ONT Unbooked Orders

Description
Categories: BI Publisher, Sales
Application: Order Management
Source: Unbooked Orders Report (XML)
Short Name: OEXOEUBK_XML
DB package: ONT_OEXOEUBK_XMLP_PKG
select  h.order_number, 
         h.ordered_date,
         l.line_category_code category,
        fu.user_name,
l.item_identifier_type,
l.inventory_item_id,
l.ordered_item_id,
l.ordered_item,
DECODE(l.line_category_code,'RETURN',nvl(l.unit_selling_price,0) * nvl(l.ordered_quantity,0) * -1,
               nvl(l.unit_selling_price,0) * nvl(l.ordered_quantity,0))  line_amt ,
         h.transactional_curr_code,
l.charge_periodicity_code,
&rp_item_flex_all_seg item_flex, 
	ONT_OEXOEUBK_XMLP_PKG.c_periodicity_dspformula(l.charge_periodicity_code) C_periodicity_dsp,
ONT_OEXOEUBK_XMLP_PKG.Item_dspFormula(l.inventory_item_id,l.item_identifier_type,l.ordered_item_id,MSI.ORGANIZATION_ID,l.ordered_item) Item_dsp 
from    fnd_user fu, 
        oe_order_lines_all l, 
        oe_order_headers_all h,
        mtl_system_items_vl msi 
where   h.header_id = l.header_id
and l.inventory_item_id  = msi.inventory_item_id
and msi.organization_id = nvl(l.ship_from_org_id,:c_master_org)
and     h.created_by = fu.user_id
and     l.booked_flag ='N' 
and     (l.cancelled_flag ='N' or l.cancelled_flag is null)
and     l.open_flag ='Y'   
--and nvl(h.org_id,0) = nvl(:p_organization_id,0)
and nvl(h.org_id,0) = nvl(:p_organization_id1,0)
&lp_created_by
&lp_order_date
&lp_order_category
&lp_line_category
UNION ALL
select   h.order_number,
              h.ordered_date,
              h.order_category_code category,
              fu.user_name,
              null,
              null,
              null,
              null,
              0 line_amt,
              h.transactional_curr_code,
              null,
              null, 
	ONT_OEXOEUBK_XMLP_PKG.c_periodicity_dspformula(null) C_periodicity_dsp,
	null Item_dsp
from oe_order_headers_all h,
        fnd_user fu
where  h.created_by = fu.user_id 
 and   h.booked_flag  ='N' 
 and  (h.cancelled_flag ='N' or h.cancelled_flag is null)
 and h.open_flag = 'Y' 
--and nvl(h.org_id,0) = nvl(:p_organization_id,0)
and nvl(h.org_id,0) = nvl(:p_organization_id1,0)
 and not exists (select null from oe_order_lines_all  l1  where  l1.header_id = h.header_id )
 &lp_created_by 
&lp_order_date
&lp_order_category
&lp_order_by
Parameter NameSQL textValidation
Item Flex Code
 
Ledger Id
 
Number
Item Display
 
LOV Oracle
Line Category
 
LOV Oracle
Order Category
 
LOV Oracle
Order Date (To)
 
Date
Order Date (From)
 
Date
Created By (To)
 
LOV Oracle
Created By (From)
 
LOV Oracle
Sort By
 
LOV Oracle