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.attribute_context,
xxqpd.attribute,
xxqpd.attribute_operator,
xxqpd.attribute_value_from,
xxqpd.attribute_value_to,
xxqpd.attribute_precedence,
-- record sequencing columns
xxqpd.line_number,
xxqpd.line_detail_seq
from
xxen_qp_preq_data xxqpd,
-- customer
hz_cust_accounts hca,
hz_parties hp,
hz_customer_profiles hcp,
hz_cust_profile_classes hcpc,
-- ship to site
hz_cust_site_uses_all hcsua1,
hz_cust_acct_sites_all hcasa1,
hz_party_sites hps1,
hz_locations hl1,
hr_all_organization_units_vl haouv1,
hz_customer_profiles hcp1,
hz_cust_profile_classes hcpc1,
-- bill to site
hz_cust_site_uses_all hcsua2,
hz_cust_acct_sites_all hcasa2,
hz_party_sites hps2,
hz_locations hl2,
hr_all_organization_units_vl haouv2,
hz_customer_profiles hcp2,
hz_cust_profile_classes hcpc2,
-- items
mtl_system_items_b_kfv msik,
mtl_system_items_b_kfv msik2,
-- requested price list
qp_secu_list_headers_vl qslhv1,
hr_all_organization_units_vl haouv3,
qp_list_lines qll1,
-- applied Price List
qp_secu_list_headers_vl qslhv2,
-- PE PLL/Modifier List headers/list lines
qp_secu_list_headers_vl qslhv3,
qp_list_lines qll3,
qp_price_breaks_v qpbv3
where
-- customer
hca.cust_account_id = xxqpd.cust_account_id
and hp.party_id = hca.party_id
and hcp.cust_account_id (+) = hca.cust_account_id
and nvl(hcp.site_use_id,-1) = -1
and hcpc.profile_class_id (+) = hcp.profile_class_id
-- ship to site
and hcsua1.site_use_id (+) = xxqpd.ship_to_site_use_id
and hcasa1.cust_acct_site_id (+) = hcsua1.cust_acct_site_id
and hps1.party_site_id (+) = hcasa1.party_site_id
and hl1.location_id (+) = hps1.location_id
and haouv1.organization_id (+) = hcsua1.org_id
and hcp1.site_use_id (+) = hcsua1.site_use_id
and hcpc1.profile_class_id (+) = hcp1.profile_class_id
-- bill to site
and hcsua2.site_use_id (+) = xxqpd.bill_to_site_use_id
and hcasa2.cust_acct_site_id (+) = hcsua2.cust_acct_site_id
and hps2.party_site_id (+) = hcasa2.party_site_id
and hl2.location_id (+) = hps2.location_id
and haouv2.organization_id (+) = hcsua2.org_id
and hcp2.site_use_id (+) = hcsua2.site_use_id
and hcpc2.profile_class_id (+) = hcp2.profile_class_id
-- item msik = requested item, msik2 = priced item
and msik.inventory_item_id = xxqpd.item_id
and msik.organization_id = xxen_qp_preq.get_item_organization_id
and msik2.inventory_item_id = nvl(xxqpd.promotional_goods_item_id,xxqpd.item_id)
and msik2.organization_id = xxen_qp_preq.get_item_organization_id
-- requested price list
and qslhv1.list_header_id = xxqpd.price_list_id
and haouv3.organization_id (+) = qslhv1.orig_org_id
and qll1.list_line_id = xxqpd.price_list_line_id
-- applied price list
and qslhv2.list_header_id (+) = xxqpd.applied_price_list_id
-- PE PLL/Modifier List headers/list lines
and qslhv3.list_header_id (+) = xxqpd.created_from_list_header_id
and qll3.list_header_id (+) = xxqpd.created_from_list_header_id
and qll3.list_line_id (+) = xxqpd.created_from_list_line_id
and qpbv3.list_header_id (+) = xxqpd.created_from_list_header_id
and qpbv3.list_line_id (+) = xxqpd.created_from_list_line_id
and ( hcsua1.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
or hcsua2.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
or (hcsua1.org_id is null and hcsua2.org_id is null)
)
) x
where
1=1
order by
x.customer_name,
x.account_number,
x.pricing_item_code,
x.price_list,
x.customer_or_site,
x.operating_unit,
x.ship_to_location,
x.bill_to_location,
x.line_number,
x.line_detail_seq |
