ONT Orders by Item

Description
Categories: BI Publisher, Sales
Application: Order Management
Source: Orders by Item Report (XML)
Short Name: OEXOEITR_XML
DB package: ONT_OEXOEITR_XMLP_PKG
SELECT
        party.party_name cust ,
        cust_acct.account_number custno,
        h.order_number ordernum,
        ot.name order_type,
        h.cust_po_number purchase,
        h.ordered_date   order_date,
        decode(l.line_category_code,'ORDER',nvl(sum(l.ordered_quantity),0),0) ordqty,
        decode(l.line_category_code,'ORDER', nvl(sum(l.shipped_quantity),0),0) shipqty,
        decode(l.line_category_code,'ORDER',decode(l.shippable_flag,'Y',
        nvl(sum(l.ordered_quantity),0) - nvl(sum(l.shipped_quantity),0),
        nvl(sum(l.ordered_quantity),0) - nvl(sum(l.fulfilled_quantity),0)),0) ostandqty,
     decode(l.line_category_code,'ORDER',NVL(SUM(l.ordered_quantity2),0),0) ordqty2,
     decode(l.line_category_code,'ORDER',NVL(SUM(l.shipped_quantity2),0),0) shipqty2,
 decode(l.line_category_code,'ORDER',decode(l.shippable_flag,'Y',
        NVL(SUM(l.ordered_quantity2),0) - NVL(SUM(l.shipped_quantity2),0),
        nvl(sum(l.ordered_quantity2),0) - nvl(sum(decode(l.fulfilled_quantity2,9.99E125,NULL,l.fulfilled_quantity2)),0)),0) ostandqty2,
     min( l.item_identifier_type) item_identifier_type,
&rp_item_flex_all_seg item_flex,
min(l.inventory_item_id) inventory_item_id,
min(l.ordered_item_id) ordered_item_id,
min(l.ordered_item) ordered_item,
    decode(l.line_category_code,'RETURN',nvl(sum(l.ordered_quantity),0),0) retqty,
    decode(l.line_category_code,'RETURN',NVL(SUM(l.ordered_quantity2),0),0) retqty2,
    l.order_quantity_uom Unit,
    l.ordered_quantity_uom2 unit2,
    MIN(l.preferred_grade) grade,
    l.charge_periodicity_code,
 ONT_OEXOEITR_XMLP_PKG.cf_charge_periodicityformula(l.charge_periodicity_code) CF_charge_periodicity,
 --&item_dsp item_dsp,
 ONT_OEXOEITR_XMLP_PKG.cf_unit2formula(l.ordered_quantity_uom2) CF_unit2,
 ONT_OEXOEITR_XMLP_PKG.cf_unit1formula(l.order_quantity_uom) CF_unit1,
 ONT_OEXOEITR_XMLP_PKG.Item_dspFormula(min( l.item_identifier_type),:c_master_org,min(l.inventory_item_id),min(l.ordered_item_id),min(l.ordered_item),SI.ORGANIZATION_ID, SI.INVENTORY_ITEM_ID) item_dsp
FROM    oe_order_headers_all h,
           hz_parties party ,
           hz_cust_accounts cust_acct ,
           oe_order_lines_all l,
           oe_transaction_types_tl ot ,
           mtl_system_items_b_kfv si ,
           mtl_system_items_tl st
WHERE   cust_acct.cust_account_id = h.sold_to_org_id
AND     cust_acct.party_id=party.party_id
AND     h.header_id = l.header_id
AND     h.order_type_id = ot.transaction_type_id
AND     ot.language = userenv('LANG')
AND     si.organization_id = :c_master_org
AND     si.inventory_item_id = st.inventory_item_id
AND     si.organization_id  = st.organization_id
AND     st.language         = userenv('LANG')
AND     l.inventory_item_id = si.inventory_item_id
AND     nvl(l.org_id,0)  = nvl(:p_org_id,0)
AND     nvl(h.org_id,0) = nvl(:p_org_id,0)
&lp_customer_name
&lp_order_date
&lp_order_num
&lp_order_type
&lp_openonly
and &lp_item
&lp_order_category
&lp_line_category
GROUP BY
  &RP_ITEM_FLEX_ALL_SEG,
   SI.description,
   party.party_name,
   cust_acct.account_number,
   h.order_number,
   ot.name,
   h.cust_po_number,
   h.ordered_date,
   l.line_category_code,
   l.order_quantity_uom,
   l.ordered_quantity_uom2,
  l.charge_periodicity_code,
  l.shippable_flag,
  --&item_dsp,
  SI.ORGANIZATION_ID, SI.INVENTORY_ITEM_ID
--ORDER BY
--   party.party_name
   ORDER BY item_dsp,23 ASC,20 ASC,21 ASC,1 ASC , party.party_name
Parameter Name SQL text Validation
Item Flex Code
 
Ledger Id
 
Number
Order Type (To)
 
LOV Oracle
Order Type (From)
 
LOV Oracle
Show Open Orders Only
 
LOV Oracle
Item Display
 
LOV Oracle
Line Category
 
LOV Oracle
Order Category
 
LOV Oracle
Item (To)
 
Item (From)
 
Order Date (To)
 
Date
Order Date (From)
 
Date
Order Number (To)
 
Number
Order Number (From)
 
Number
Customer Name (To)
 
LOV Oracle
Customer Name (From)
 
LOV Oracle