QP Customer Pricing Engine Request
Description
Categories: Enginatics
Repository: Github
Repository: Github
QP Customer Pricing Engine Request
==============================
This report requests Item Selling Price information by Customer across Price Lists from the Pricing Engine.
The report also displays the Items Costs for the specified Organization and based on the Unit Selling Price and Item Cost, displays a Margin Analysis.
The report can be run in Summary or Detail Mode.
more
==============================
This report requests Item Selling Price information by Customer across Price Lists from the Pricing Engine.
The report also displays the Items Costs for the specified Organization and based on the Unit Selling Price and Item Cost, displays a Margin Analysis.
The report can be run in Summary or Detail Mode.
more
Run
QP Customer Pricing Engine Request and other Oracle EBS reports with Blitz Report™ on our demo environment
with xxen_qp_preq_data as (select xml_req.*, xml_res.* from xmltable ( 'REPORT/PRICING_DATA/REQUEST' passing xmltype(xxen_qp_preq.get_report_xml_clob) columns line_number number path 'LNUM', customer_or_site varchar2(10) path 'CSFL', cust_account_id integer path 'CAID', ship_to_site_use_id integer path 'STID', bill_to_site_use_id integer path 'BTID', item_id integer path 'ITMID', -- optional order qualifiers order_type_id integer path 'OTID', ship_from_org_id integer path 'SFOID', payment_term_id integer path 'PTID', -- Pricing Request price_list_id integer path 'PLID', price_list_line_id number path 'PLLID', pricing_qty number path 'QTY', pricing_qty_uom_code varchar2(3) path 'UOM', results_xml xmltype path 'RESULTS' ) xml_req, xmltable ( '/RESULTS/ROW' passing xml_req.results_xml columns line_detail_seq number path 'LDSEQ', applied_price_list_id number path 'APLID', secondary_price_list_flag varchar2(1) path 'SPLFL', promotional_goods_flag varchar2(1) path 'PRGFL', promotional_goods_item_id number path 'PRGID', record_type varchar2(30) path 'RT', created_from_list_header_id number path 'LSTID', created_from_list_line_id number path 'LLNID', line_qty_or_amt number path 'LQTY', priced_qty number path 'PQTY', priced_uom varchar2(3) path 'PUOM', unit_price number path 'PUP', adjustment_amount number path 'PADJ', adjusted_unit_price number path 'PAUP', extended_amount number path 'PEAMT', charges_amount number path 'PCAMT', accrual_amount number path 'PAAMT', cogs_amount number path 'PCOGS', benefit varchar2(80) path 'BENFT', benefit_method varchar2(4000) path 'BENMT', benefit_value varchar2(4000) path 'BENVL', benefit_item varchar2(360) path 'BENIT', benefit_qty number path 'BENQT', operand varchar2(80) path 'OPR', operand_value number path 'OPRV', price_formula varchar2(250) path 'PFRM', currency varchar2(3) path 'CUR', base_currency varchar2(3) path 'BCUR', multi_currency_flag varchar2(1) path 'MCFL', exchange_rate_type varchar2(30) path 'XRTT', exchange_rate number path 'XRT', product_precedence number path 'PPREC', automatic_flag varchar2(1) path 'ATMFL', override_flag varchar2(1) path 'OVRFL', accrual_flag varchar2(1) path 'ACCFL', attribute_level varchar2(10) path 'ALVL', attribute_grouping_num number path 'AGPN', attribute_context varchar2(80) path 'ACTX', attribute varchar2(80) path 'ANME', attribute_operator varchar2(30) path 'AOPR', attribute_value_from varchar2(240) path 'AVLFR', attribute_value_to varchar2(240) path 'AVTO', attribute_precedence number path 'APREC' ) xml_res ) -- -- Main Query Starts Here -- select x.* from ( select -- customer xxqpd.customer_or_site, hp.party_name customer_name, hca.account_number account_number, hp.party_name || ' - ' || hca.account_number customer_name_and_account, nvl(xxen_util.meaning(hp.category_code,'CUSTOMER_CATEGORY',222),hp.category_code) customer_category, nvl(xxen_util.meaning(hca.customer_class_code,'CUSTOMER CLASS',222),hca.customer_class_code) customer_class, nvl(xxen_util.meaning(hca.sales_channel_code,'SALES_CHANNEL',660),hca.sales_channel_code) sales_channel, hcpc.name customer_profile, nvl(hcp.discount_terms,'N') customer_allow_discount, (select rtv.name from ra_terms_vl rtv where rtv.term_id = nvl(hca.payment_term_id,hcp.standard_terms) ) customer_payment_term, (select qslhv.name from qp_secu_list_headers_vl qslhv where qslhv.list_header_id = hca.price_list_id ) customer_price_list, -- ship_to/bill to OU nvl(haouv1.name,haouv2.name) operating_unit, -- ship_to site hcsua1.location ship_to_location_code, hcsua1.location || ' ' || hl1.address1 || ' ' || hl1.address2 || ' ' || hl1.address3 ship_to_location, hcsua1.primary_flag ship_to_primary_flag, hcpc1.name ship_to_profile, (select mp.organization_code from mtl_parameters mp where mp.organization_id = hcsua1.warehouse_id ) ship_to_ship_from_warehouse, (select ottt.name from oe_transaction_types_tl ottt where ottt.transaction_type_id = hcsua1.order_type_id and ottt.language = userenv('lang') ) ship_to_order_type, (select rtv.name from ra_terms_vl rtv where rtv.term_id = hcsua1.payment_term_id ) ship_to_payment_term, (select qslhv.name from qp_secu_list_headers_vl qslhv where qslhv.list_header_id = hcsua1.price_list_id ) ship_to_price_list, -- bill_to site hcsua2.location bill_to_location_code, hcsua2.location || ' ' || hl2.address1 || ' ' || hl2.address2 || ' ' || hl2.address3 bill_to_location, hcsua2.primary_flag bill_to_primary_flag, hcpc2.name bill_to_profile, (select mp.organization_code from mtl_parameters mp where mp.organization_id = hcsua2.warehouse_id ) bill_to_ship_from_warehouse, (select ottt.name from oe_transaction_types_tl ottt where ottt.transaction_type_id = hcsua2.order_type_id and ottt.language = userenv('lang') ) bill_to_order_type, (select rtv.name from ra_terms_vl rtv where rtv.term_id = hcsua2.payment_term_id ) bill_to_payment_term, (select qslhv.name from qp_secu_list_headers_vl qslhv where qslhv.list_header_id = hcsua2.price_list_id ) bill_to_price_list, -- hp.party_name || ' - ' || hca.account_number || nvl2(nvl(hcsua1.location,hcsua2.location), ' - ' || nvl(hcsua1.location || ' ' || hl1.address1 || ' ' || hl1.address2 || ' ' || hl1.address3, hcsua2.location || ' ' || hl2.address1 || ' ' || hl2.address2 || ' ' || hl2.address3) || ' - ' || '(' || nvl(haouv1.name,haouv2.name) || ')', '' ) customer_and_site_org_desc, -- Price List qslhv1.name price_list, qslhv1.description price_list_description, xxen_util.meaning(qslhv1.list_type_code,'LIST_TYPE_CODE',661) price_list_type, qslhv1.currency_code price_list_currency, nvl(qslhv1.global_flag,'N') price_list_global_flag, haouv3.name price_list_operating_unit, (select rtv.name from ra_terms_vl rtv where rtv.term_id = qslhv1.terms_id ) price_list_payment_term, -- pricing request -- item msik.concatenated_segments pricing_item_code, msik.description pricing_item_description, msik.concatenated_segments || ' - ' || msik.description pricing_item_code_and_descr, xxen_qp_preq.get_pricing_date pricing_date, xxqpd.pricing_qty, xxqpd.pricing_qty_uom_code, decode(qll1.list_line_type_code,'PBH','Y') price_break_flag, xxen_qp_preq.get_pricing_currency pricing_currency_code, -- order qualifiers xxen_qp_preq.get_agreement pricing_agreement_name, xxen_qp_preq.get_order_source pricing_order_source, (select ottt.name from oe_transaction_types_tl ottt where ottt.transaction_type_id = xxqpd.order_type_id and ottt.language = userenv('lang') ) pricing_order_type, (select mp.organization_code from mtl_parameters mp where mp.organization_id = xxqpd.ship_from_org_id ) pricing_warehouse, (select rtv.name from ra_terms_vl rtv where rtv.term_id = xxqpd.payment_term_id ) pricing_payment_term, -- -- Pricing Engine Results -- qslhv2.name applied_price_list, xxqpd.secondary_price_list_flag, xxqpd.promotional_goods_flag, msik2.concatenated_segments priced_item, msik2.description priced_item_descscription, nvl2(xxqpd.promotional_goods_flag,msik.concatenated_segments || ' (P) ','') || msik2.concatenated_segments || ' - ' || msik2.description priced_item_code_and_descr, msik2.primary_uom_code priced_item_primary_uom_code, xxen_util.meaning(msik.bom_item_type,'BOM_ITEM_TYPE',700) bom_item_type, xxen_qp_preq.get_category_set_name category_set, &category_set_columns cst_cost_api.get_item_cost(1,msik2.inventory_item_id,xxen_qp_preq.get_cost_organization_id,null,xxen_qp_preq.get_cost_type_id) priced_item_cost, xxen_qp_preq.get_cost_organization_code item_cost_organization, case xxqpd.record_type when 'S' then 'Summary' when 'D' then 'Detail' when 'QA' then 'Qualifier Attribute' when 'PA' then 'Pricing Attribute' else xxqpd.record_type end record_type, qslhv3.name list_name, nvl(qll3.list_line_no,xxqpd.created_from_list_line_id) list_line_num, xxen_util.meaning(qslhv3.list_type_code,'LIST_TYPE_CODE',661) list_type, xxen_util.meaning(qll3.list_line_type_code,'LIST_LINE_TYPE_CODE',661) line_type, xxqpd.line_qty_or_amt, xxqpd.priced_qty, xxqpd.priced_uom, xxqpd.unit_price, xxqpd.adjustment_amount, xxqpd.adjusted_unit_price, xxqpd.extended_amount, xxqpd.charges_amount, xxqpd.accrual_amount, xxqpd.cogs_amount, case when nvl(xxqpd.cogs_amount,0) = 0 then to_number(null) else (xxqpd.extended_amount - xxqpd.cogs_amount) end margin_pre_accrual, case when nvl(xxqpd.cogs_amount,0) = 0 then to_number(null) else round((xxqpd.extended_amount - xxqpd.cogs_amount) / xxqpd.cogs_amount * 100,2) end margin_pre_accrual_pct, case when nvl(xxqpd.cogs_amount,0) = 0 then to_number(null) else (xxqpd.extended_amount + nvl(xxqpd.accrual_amount,0) - xxqpd.cogs_amount) end margin_post_accrual, case when nvl(xxqpd.cogs_amount,0) = 0 then to_number(null) else round((xxqpd.extended_amount + nvl(xxqpd.accrual_amount,0) - xxqpd.cogs_amount) / xxqpd.cogs_amount * 100,2) end margin_post_accrual_pct, xxen_util.meaning(qpbv3.price_break_type_code,'PRICE_BREAK_TYPE_CODE',661) pb_type, qp_qp_form_pricing_attr.get_attribute('QP_ATTR_DEFNS_PRICING', qpbv3.pricing_attribute_context, qpbv3.pricing_attribute) pb_attribute, xxen_util.meaning(qpbv3.comparison_operator_code,'COMPARISON_OPERATOR',661) pb_operator, qpbv3.pricing_attr_value_from pb_from, qpbv3.pricing_attr_value_to pb_to, xxqpd.benefit, xxqpd.benefit_method, xxqpd.benefit_value, xxqpd.benefit_item, xxqpd.benefit_qty, xxqpd.operand, xxqpd.operand_value, xxqpd.price_formula, xxqpd.currency, xxqpd.base_currency, xxqpd.multi_currency_flag, xxqpd.exchange_rate_type, xxqpd.exchange_rate, xxqpd.product_precedence, xxqpd.automatic_flag, xxqpd.override_flag, xxqpd.accrual_flag, xxqpd.attribute_level, xxqpd.attribute_grouping_num, xxqpd. |