oracle EBS PL/sql 查看销售订单的价目表Price List

EBS 价目表: oracle-EBS-PL/sql 查看销售订单的价目表Price List,设定好价目表:

主要用到的表:

qp_list_headers
qp_list_lines
qp_pricing_attributes

后台查看的sql 如下:

select 
    '  ' BATCH,
    hraou.name ORG_NAME,  
    substr(hraou.name,1,4) ORG_ID,
    qplh.name PRICE_LIST_NAME,
    qplh.description PRICE_LIST_DESCRIPTION,
    qplh.list_header_id PRICE_LIST_ID,
    qplh.attribute15 PRICE_LIST_HDR_REF,
    qplh.currency_code CURRENCY,
    qplh.start_date_active H_START_DATE_ACTIVE,
    qplh.end_date_active H_END_DATE_ACTIVE,
    qpll.attribute15 PRICE_LIST_LINE_REF,
    QP_Price_List_Line_Util.Get_Product_Value (
             'QP_ATTR_DEFNS_PRICING',
             qppa.product_attribute_context,
             qppa.product_attribute,
             qppa.product_attr_value) ITEM_CODE,
    qppa.product_attr_value ITEM_ID,
    msi.DESCRIPTION,
    qppa.product_uom_code UOM,
    qpll.operand LIST_PRICE,
    qpll.start_date_active L_START_DATE_ACTIVE,
    qpll.end_date_active L_END_DATE_ACTIVE
from qp_list_headers qplh
    left join HR_ORGANIZATION_INFORMATION hroi on hroi.ORGANIZATION_ID = qplh.orig_org_id and hroi.ORG_INFORMATION1 = 'OPERATING_UNIT'
    left join HR_ALL_ORGANIZATION_UNITS hraou on hroi.ORGANIZATION_ID = hraou.ORGANIZATION_ID,
    qp_list_lines qpll,
    QP_PRICING_ATTRIBUTES qppa
        left join mtl_system_items msi on qppa.product_attr_value = msi.INVENTORY_ITEM_ID 
    and msi.ORGANIZATION_ID =101 
where qplh.list_type_code = 'PRL'
and qpll.list_line_type_code = 'PLL'
and qpll.list_header_id = qplh.list_header_id
and qpll.list_line_id = qppa.list_line_id
and qplh.attribute15 is not null
and qpll.attribute15 is not null   and  (qpll.end_date_active>'01-1月-19' or  qpll.end_date_active is null)
and UPPER(qplh.name) like '%%BASE PRICE%' 
order by qpll.attribute15;

 

----欢迎转载----
THE END
分享
二维码
< <上一篇
下一篇>>