ONT Orders by Item

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Orders by Item Report
Application: Order Management
Source: Orders by Item Report (XML)
Short Name: OEXOEITR_XML
DB package: ONT_OEXOEITR_XMLP_PKG
Run ONT Orders by Item and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Operating Unit
 
LOV
Customer Name (From)
 
LOV Oracle
Customer Name (To)
 
LOV Oracle
Order Number (From)
 
Number
Order Number (To)
 
Number
Order Date (From)
 
Date
Order Date (To)
 
Date
Item (From)
 
Char
Item (To)
 
Char
Order Category
 
LOV Oracle
Line Category
 
LOV Oracle
Item Display
 
LOV Oracle
Show Open Orders Only
 
LOV Oracle
Order Type (From)
 
LOV Oracle
Order Type (To)
 
LOV Oracle