ONT Unbooked Orders Detail

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