QP Customer Pricing Engine Request

Description

Categories: Enginatics
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.

In Summary Mode, the report will display a summary of Applied Price Lists, List Price, Adjustment Amount, Final Unit Selling Price, Accrual Amount, Charge Amount, Item Cost, and Margin per Customer, requested Price List and Item. Summary Records can be identified in the report with Record Type=Summary

In Detail Mode, in additional to the summary information, the report will include the following record types:
- Detail Record Type which provides a breakdown of the Price List and Modifier Lines the pricing engine has applied in the calculation of the final unit selling price
- Pricing Attribute Record Type provides details of the Pricing Attributes considered by the Pricing Engine in selecting a specified Price List or Modifier
- Qualifier Attribute Record Type provides details of any Qualifier Attributes considered by the Pricing Engine in selecting a specific Price List or Modifier

Detail Record Types follow the Summary Record Type they apply to
Pricing Attribute and Qualifier Attribute Record Types follow the Detail Record Type they apply to.

Additionally, the report supports the following options:

- Use Secondary Price Lists (Yes/No)
When No, the report will restrict to displaying Prices on the requested Price Lists only
When Yes, then the report will check for the price on any secondary price lists defined for the requested Price List

- Expand Price Breaks (Yes/No)
When No, the report will display the pricing based on the requested Pricing Only
When Yes, the report will display one line per Price Break to provide an indication of the unit selling price will vary across the Price Breaks and the impact that may have on margins

- Display Promotional Goods (Buy One Get One free type Promotions))
When No, the report will not display any Promotional Goods the Customer maybe eligible to receive
When Yes, the report will display any Promotional Goods the Customer maybe eligible to receive

- Display Site Pricing Differences
When No, the report will only check the Pricing Based on the specified Customer Accounts
When Yes, the report will also check the Pricing based on the specified Ship To/Bill To Locations.

If the pricing at the Customer Site is the same as at the Customer Level, only the Customer Level pricing is displayed in the report.
If the pricing at the Customer Site differs to the pricing at the Customer Level, then both the Customer Pricing and Site Pricing is included in the report.
If a Ship To or Bill To location is not specified, and the Display Site Pricing Differences is set to Yes, the report will check all active ship to sites for the Customer within the specified Operating Units.

It is also possible to specify some additional Order Qualifiers to be considered by the Pricing Engine. These are:
- Organization - this is used as the Shipping Warehouse, It also determines from which Items can be selected and also determines the Organization from which the Item Costs are derived.
- Sales Agreement
- Order Source
- Order Type

Lastly the user can specify
- Pricing Request Date
- Pricing Request Quantity
- Currency Code

Unit Selling Price, Item Costs, and extended amounts are all displayed in the specified currency. The user can only select a currency that is supported by the selected Price Lists.

Each payment term column reports a single source: Customer Payment Term from the account profile, Ship To and Bill To from the site use Order Management terms, Price List from the price list, and Pricing from the request itself.

SQL 388 lines

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,
  xxen_util.yes(hcp.discount_terms) customer_allow_discount,
  (select rtv.name
   from   ra_terms_vl rtv
   where  rtv.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,
  xxen_util.yes(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,
  xxen_util.yes(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,
  xxen_util.yes(qslhv1.global_flag) 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
  msiv.concatenated_segments pricing_item_code,
  msiv.description pricing_item_description,
  msiv.concatenated_segments || ' - ' || msiv.description pricing_item_code_and_descr,
  (select min(mck.concatenated_segments) keep (dense_rank first order by mic.category_id)
   from   mtl_item_categories mic,
          mtl_categories_kfv mck
   where  mic.category_set_id = xxen_qp_preq.get_category_set_id
   and    msiv.organization_id = mic.organization_id
   and    msiv.inventory_item_id = mic.inventory_item_id
   and    mic.category_id = mck.category_id
  ) pricing_category_segments,
  xxen_qp_preq.get_pricing_date pricing_date,
  xxqpd.pricing_qty,
  xxqpd.pricing_qty_uom_code,
  xxen_util.yes(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,
  xxen_util.yes(xxqpd.secondary_price_list_flag) secondary_price_list_flag,
  xxen_util.yes(xxqpd.promotional_goods_flag) promotional_goods_flag,
  msiv2.concatenated_segments  priced_item,
  msiv2.description priced_item_description,
  nvl2(xxqpd.promotional_goods_flag,msiv.concatenated_segments || ' (P) ','') || msiv2.concatenated_segments || ' - ' || msiv2.description priced_item_code_and_descr,
  msiv2.primary_uom_code priced_item_primary_uom_code,
  xxen_util.meaning(msiv.bom_item_type,'BOM_ITEM_TYPE',700) bom_item_type,
  xxen_qp_preq.get_category_set_name category_set,
  (select min(mck.concatenated_segments) keep (dense_rank first order by mic.category_id)
   from   mtl_item_categories mic,
          mtl_categories_kfv mck
   where  mic.category_set_id = xxen_qp_preq.get_category_set_id
   and    msiv2.organization_id = mic.organization_id
   and    msiv2.inventory_item_id = mic.inventory_item_id
   and    mic.category_id = mck.category_id
  ) category_segments,
  &category_set_columns
  cst_cost_api.get_item_cost(1,msiv2.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,
  xxen_util.yes(xxqpd.multi_currency_flag) multi_currency_flag,
  xxqpd.exchange_rate_type,
  xxqpd.exchange_rate,
  xxqpd.product_precedence,
  xxen_util.yes(xxqpd.automatic_flag) automatic_flag,
  xxen_util.yes(xxqpd.override_flag) override_flag,
  xxen_util.yes(xxqpd.accrual_flag) 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_vl            msiv,
  mtl_system_items_vl            msiv2,
  -- 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 msiv = requested item, msiv2 = priced item
and msiv.inventory_item_id        = xxqpd.item_id
and msiv.organization_id          = xxen_qp_preq.get_item_organization_id
and msiv2.inventory_item_id       = nvl(xxqpd.promotional_goods_item_id,xxqpd.item_id)
and msiv2.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 xroa.id from xxen_report_org_access xroa where xroa.access_type='OU')
     or hcsua2.org_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU')
     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

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, xxen_util.yes(hcp.discount_terms) customer_allow_discount, (select rtv.name from ra_terms_vl rtv where rtv.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, xxen_util.yes(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, xxen_util.yes(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, xxen_util.yes(qslhv1.global_flag) 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 msiv.concatenated_segments pricing_item_code, msiv.description pricing_item_description, msiv.concatenated_segments || ' - ' || msiv.description pricing_item_code_and_descr, (select min(mck.concatenated_segments) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_kfv mck where mic.category_set_id = xxen_qp_preq.get_category_set_id and msiv.organization_id = mic.organization_id and msiv.inventory_item_id = mic.inventory_item_id and mic.category_id = mck.category_id ) pricing_category_segments, xxen_qp_preq.get_pricing_date pricing_date, xxqpd.pricing_qty, xxqpd.pricing_qty_uom_code, xxen_util.yes(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, xxen_util.yes(xxqpd.secondary_price_list_flag) secondary_price_list_flag, xxen_util.yes(xxqpd.promotional_goods_flag) promotional_goods_flag, msiv2.concatenated_segments priced_item, msiv2.description priced_item_description, nvl2(xxqpd.promotional_goods_flag,msiv.concatenated_segments || ' (P) ','') || msiv2.concatenated_segments || ' - ' || msiv2.description priced_item_code_and_descr, msiv2.primary_uom_code priced_item_primary_uom_code, xxen_util.meaning(msiv.bom_item_type,'BOM_ITEM_TYPE',700) bom_item_type, xxen_qp_preq.get_category_set_name category_set, (select min(mck.concatenated_segments) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_kfv mck where mic.category_set_id = xxen_qp_preq.get_category_set_id and msiv2.organization_id = mic.organization_id and msiv2.inventory_item_id = mic.inventory_item_id and mic.category_id = mck.category_id ) category_segments, &category_set_columns cst_cost_api.get_item_cost(1,msiv2.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, xxen_util.yes(xxqpd.multi_currency_flag) multi_currency_flag, xxqpd.exchange_rate_type, xxqpd.exchange_rate, xxqpd.product_precedence, xxen_util.yes(xxqpd.automatic_flag) automatic_flag, xxen_util.yes(xxqpd.override_flag) override_flag, xxen_util.yes(xxqpd.accrual_flag) 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_vl msiv, mtl_system_items_vl msiv2, -- 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 msiv = requested item, msiv2 = priced item and msiv.inventory_item_id = xxqpd.item_id and msiv.organization_id = xxen_qp_preq.get_item_organization_id and msiv2.inventory_item_id = nvl(xxqpd.promotional_goods_item_id,xxqpd.item_id) and msiv2.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 xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') or hcsua2.org_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') 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

Parameters 24

Parameter

Type

List of values SQL

Report Level

LOV

Operating Unit

LOV

(x.operating_unit is null or x.operating_unit=:p_operating_unit)

Customer Name

LOV

x.customer_name=:p_customer_name

Account Number

LOV

x.account_number=:p_account_number

Customer Ship To Location

LOV

(x.ship_to_location_code  is null or x.ship_to_location_code=:p_ship_to_location)

Customer Bill To Location

LOV

(x.bill_to_location_code is null or x.bill_to_location_code=:p_bill_to_location)

Price List

LOV

x.price_list=:p_price_list

Currency Code

LOV

Organization Code

LOV

Category Set

LOV

Description

SQL 388 lines

Parameters 24

Find out more

Features

Download

FAQ