ONT Unbooked Orders Detail

Description
Categories: BI Publisher, Sales
Application: Order Management
Source: Unbooked Orders Detail Report (XML)
Short Name: OEXOEUBD_XML
DB package: ONT_OEXOEUBD_XMLP_PKG
select
h.order_number,
            h.ordered_date,
            ot.name order_type,
            UPPER(REPLACE(SUBSTR(ppf_mgr.email_address, 1,
                      DECODE(INSTR(ppf_mgr.email_address, '.'),
	        0, LENGTH(ppf_mgr.email_address),
	       INSTR(ppf_mgr.email_address, '.') -1)), '@', '_')) manager,
            fu.user_name,
            l.item_identifier_type,
            decode (l.item_identifier_type, 'INT', &rp_item_flex_all_seg, 'CUST', ci.customer_item_number, l.ordered_item)
Item,
            msi.description Internal_Item_Desc,
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,
            h.conversion_rate        Conversion_rate ,
            h.conversion_type_code Conversion_Type_Code ,
	    substrb(bill_to_PARTY.PARTY_NAME,1,50) bill_to,
	    substrb(sold_to_PARTY.PARTY_NAME,1,50) sold_to,
            h.cust_po_number,
            l.charge_periodicity_code,
	ONT_OEXOEUBD_XMLP_PKG.c_precisionformula(h.transactional_curr_code) C_precision,
	ONT_OEXOEUBD_XMLP_PKG.c_recur_chargesformula(l.charge_periodicity_code) C_recur_charge_periodicity,
	ONT_OEXOEUBD_XMLP_PKG.c_line_amtformula(h.transactional_curr_code, 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 ) ), h.conversion_type_code, h.ordered_date, h.conversion_rate) C_line_amt
	--,	&Item_dsp Item_Dsp
from        fnd_user fu,
            mtl_system_items_vl msi,
            oe_order_lines_all l,
            oe_order_headers_all h,
            oe_order_types_v ot,
            mtl_customer_items ci,
	   hz_cust_site_uses_all  bill_site,
	   hz_cust_acct_sites_all acct_site,
	   hz_party_sites party_site,
           -- hz_loc_assignments loc_assign, --bug 10050232
           hz_locations loc,
	   hz_parties bill_to_party,
           hz_cust_accounts bill_to_cust_acct ,
	   hz_parties sold_to_party,
           hz_cust_accounts sold_to_cust_acct ,
            per_all_people_f ppf_mgr,
            per_all_assignments_f paf,
            per_all_people_f ppf
where     h.header_id = l.header_id
   AND bill_to_CUST_ACCT.PARTY_ID = bill_to_PARTY.PARTY_ID (+)
   AND sold_to_CUST_ACCT.PARTY_ID = sold_to_PARTY.PARTY_ID (+)
   AND acct_site.party_site_id = party_site.party_site_id(+)
   AND party_site.location_id  = loc.location_id (+)
   -- AND loc.location_id = loc_assign.location_id  (+) --bug 10050232 
   -- AND NVL (acct_site.org_id, -99) = NVL (loc_assign.org_id, -99) --bug 10050232 
and       l.inventory_item_id = msi.inventory_item_id
and       h.order_type_id(+) = ot.order_type_id
and       h.created_by = fu.user_id
AND 	  SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE(+) , SYSDATE) AND
                          NVL(PPF.EFFECTIVE_END_DATE(+) , SYSDATE+1)
and       fu.employee_id = ppf.person_id(+)
and       ppf.person_id = paf.person_id(+)
AND 	  SYSDATE BETWEEN NVL(PAF.EFFECTIVE_START_DATE(+) , SYSDATE) AND
                          NVL(PAF.EFFECTIVE_END_DATE(+) , SYSDATE+1)
and       paf.supervisor_id = ppf_mgr.person_id(+)
AND 	  SYSDATE BETWEEN NVL(PPF_MGR.EFFECTIVE_START_DATE(+) , SYSDATE) AND
                          NVL(PPF_MGR.EFFECTIVE_END_DATE(+) , SYSDATE+1)
and       nvl(l.booked_flag, 'N') ='N'
and       (l.cancelled_flag ='N' or l.cancelled_flag is null)
and       nvl(msi.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
and       l.ordered_item_id = ci.customer_item_id(+)
and       l.open_flag ='Y'
--and       nvl(l.org_id,0) = nvl(:p_organization_id,0)
and       nvl(l.org_id,0) = nvl(:p_organization_id_v,0)
--and       nvl(h.org_id,0) = nvl(:p_organization_id,0)
and       nvl(h.org_id,0) = nvl(:p_organization_id_v,0)
and       l.sold_to_org_id = sold_to_CUST_ACCT.CUST_ACCOUNT_ID(+)
and       bill_site.CUST_ACCT_SITE_ID = acct_site.cust_acct_site_id(+)
and       acct_site.cust_account_id =  bill_to_CUST_ACCT.CUST_ACCOUNT_ID(+)
and       l.invoice_to_org_id= bill_site.site_use_id(+)
&lp_created_by
&lp_order_date
&lp_order_type
&lp_manager
UNION ALL
select
  h.order_number,
             h.ordered_date,
             ot.name order_type,
             UPPER(REPLACE(SUBSTR(ppf_mgr.email_address, 1,
	   DECODE(INSTR(ppf_mgr.email_address, '.'),
	   0, LENGTH(ppf_mgr.email_address),
	   INSTR(ppf_mgr.email_address, '.') -1)), '@', '_')) manager,
              fu.user_name,
              NULL  item_identifier_type,
              '    'item,
              '     'Internal_Item_Desc,
              0 line_amt,
              h.transactional_curr_code,
              h.conversion_rate        Conversion_rate ,
              h.conversion_type_code Conversion_Type_Code ,
              substrb(bill_to_PARTY.PARTY_NAME,1,50) bill_to,
              substrb(sold_to_PARTY.PARTY_NAME,1,50) sold_to,
              h.cust_po_number,
              NULL charge_periodicity_code,
	ONT_OEXOEUBD_XMLP_PKG.c_precisionformula(h.transactional_curr_code) C_precision,
	ONT_OEXOEUBD_XMLP_PKG.c_recur_chargesformula(NULL) C_recur_charge_periodicity,
	--ONT_OEXOEUBD_XMLP_PKG.c_line_amtformula(h.transactional_curr_code, 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 ) ), h.conversion_type_code, h.ordered_date, h.conversion_rate) C_line_amt
	ONT_OEXOEUBD_XMLP_PKG.c_line_amtformula(h.transactional_curr_code, 0,h.conversion_type_code,h.ordered_date,h.conversion_rate) C_line_amt
	--, &Item_dsp Item_Dsp
from      oe_order_headers_all h,
          oe_order_types_v ot,
	  hz_cust_site_uses_all bill_site,
	  hz_party_sites party_site,
          -- hz_loc_assignments loc_assign, -- bug 10050232 
          hz_locations loc,
          hz_cust_acct_sites_all acct_site,
	  hz_parties bill_to_party,
          hz_cust_accounts bill_to_cust_acct ,
	  hz_parties sold_to_party,
          hz_cust_accounts sold_to_cust_acct ,
	  per_all_people_f ppf_mgr,
          per_all_assignments_f paf,
          per_all_people_f ppf,
          fnd_user fu
where    h.created_by = fu.user_id
       AND acct_site.party_site_id = party_site.party_site_id (+)
       AND party_site.location_id = loc.location_id (+)
       -- AND loc.location_id = loc_assign.location_id (+) --bug 10050232 
       -- AND NVL (acct_site.org_id, -99) = NVL (loc_assign.org_id, -99) --bug 10050232 
       AND bill_to_cust_acct.party_id = bill_to_party.party_id (+)
       AND sold_to_cust_acct.party_id = sold_to_party.party_id (+)
and        h.booked_flag  ='N'
and        (h.cancelled_flag ='N' or h.cancelled_flag is null)
and        h.open_flag = 'Y'
and        h.order_type_id(+) = ot.order_type_id
and        fu.employee_id = ppf.person_id(+)
AND 	  SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE(+) , SYSDATE) AND
                          NVL(PPF.EFFECTIVE_END_DATE(+) , SYSDATE+1)
and        ppf.person_id = paf.person_id(+)
AND 	  SYSDATE BETWEEN NVL(PAF.EFFECTIVE_START_DATE(+) , SYSDATE) AND
                          NVL(PAF.EFFECTIVE_END_DATE(+) , SYSDATE+1)
and        paf.supervisor_id = ppf_mgr.person_id(+)
AND 	  SYSDATE BETWEEN NVL(PPF_MGR.EFFECTIVE_START_DATE(+) , SYSDATE) AND
                          NVL(PPF_MGR.EFFECTIVE_END_DATE(+) , SYSDATE+1)
and        h.sold_to_org_id = sold_to_CUST_ACCT.CUST_ACCOUNT_ID (+)
and        bill_site. CUST_ACCT_SITE_ID = acct_site.cust_acct_site_id (+)
and       acct_site.cust_account_id =  bill_to_CUST_ACCT.CUST_ACCOUNT_ID(+)
and        h.invoice_to_org_id= bill_site.site_use_id(+)
--and        nvl(h.org_id,0) = nvl(:p_organization_id,0)
and        nvl(h.org_id,0) = nvl(:p_organization_id_v,0)
and        not exists
              (select 'X' from oe_order_lines_all  b
                where  h.header_id = b.header_id
	        --and nvl(b.org_id,0) = nvl(:p_organization_id,0) )
	        and nvl(b.org_id,0) = nvl(:p_organization_id_v,0) )
&lp_created_by
&lp_order_date
&lp_order_type
&lp_manager
&lp_order_by
Parameter Name SQL text Validation
Item Flex Code
 
Ledger Id
 
Number
Use Ledger Currency
 
LOV Oracle
Manager (To)
 
LOV Oracle
Manager (From)
 
LOV Oracle
Order Type (To)
 
LOV Oracle
Order Type (From)
 
LOV Oracle
Order Date (To)
 
Date
Order Date (From)
 
Date
Created By (To)
 
LOV Oracle
Created By (From)
 
LOV Oracle
Sort By
 
LOV Oracle