ONT Orders on Credit Check Hold

Description
Categories: BI Publisher, Sales
Application: Order Management
Source: Orders on Credit Check Hold Report (XML)
Short Name: OEXOECCH_XML
DB package: ONT_OEXOECCH_XMLP_PKG
select  /*MOAC_SQL_CHANGE*/ --added for bug 20359138
	ACCT_SITE.CUST_ACCOUNT_ID CUSTOMER_ID,
	PARTY.PARTY_NAME CUSTOMER_NAME,
	CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER,
	null                                    Address1,
	null                                    City,
	null                                    State,
	h.transactional_curr_code                         Currency1,
	ccr.name                                Entry_Credit_Check_Rule,
	nvl(ot.entry_credit_check_rule_id,-1)           Entry_Rule_Id,
	ccr.open_ar_balance_flag                Entry_Open_Ar_Flag,
	ccr.open_ar_days                        Entry_Open_Ar_Days,
	ccr.uninvoiced_orders_flag              Entry_Uninvoiced_Flag,
	ccr.orders_on_hold_flag                 Entry_On_Hold_Flag,
	nvl(ccr.shipping_interval,-1)           Entry_Shipping_Interval,
	ccr2.name                               Ship_Credit_Check_Rule,
	nvl(ot.shipping_credit_check_rule_id ,-1)       Ship_Rule_Id,
	ccr2.open_ar_balance_flag               Ship_Open_Ar_Flag,
	ccr2.open_ar_days                       Ship_Open_Ar_Days,
	ccr2.uninvoiced_orders_flag             Ship_Uninvoiced_Flag,
	ccr2.orders_on_hold_flag                Ship_On_Hold_Flag,
	nvl(ccr2.shipping_interval,-1)          Ship_Shipping_Interval,
	ccr3.name                               Pick_Credit_Check_Rule,
	nvl(ot.picking_credit_check_rule_id ,-1)       Pick_Rule_Id,
	ccr3.open_ar_balance_flag               Pick_Open_Ar_Flag,
	ccr3.open_ar_days                       Pick_Open_Ar_Days,
	ccr3.uninvoiced_orders_flag             Pick_Uninvoiced_Flag,
	ccr3.orders_on_hold_flag                Pick_On_Hold_Flag,
	nvl(ccr3.shipping_interval,-1)          Pick_Shipping_Interval,
	ccr4.name                               Pack_Credit_Check_Rule,
	nvl(ot.packing_credit_check_rule_id ,-1)       Pack_Rule_Id,
	ccr4.open_ar_balance_flag               Pack_Open_Ar_Flag,
	ccr4.open_ar_days                       Pack_Open_Ar_Days,
	ccr4.uninvoiced_orders_flag             Pack_Uninvoiced_Flag,
	ccr4.orders_on_hold_flag                Pack_On_Hold_Flag,
	nvl(ccr4.shipping_interval,-1)          Pack_Shipping_Interval,
	0                                       Site_Use_Id,
	h.order_number                          Order_Number,
	h.ordered_date                          Order_Date,
	sum(nvl(l.ordered_quantity,0)*
		nvl(l.unit_selling_price,0))         Order_Amount,
	oh.creation_date                        Date_Placed_On_Hold,
	 round(sysdate - oh.creation_date)              Days_On_Hold, 
	ONT_OEXOECCH_XMLP_PKG.c_addressformula(null, null, null) C_ADDRESS, 
	ONT_OEXOECCH_XMLP_PKG.c_data_not_foundformula(PARTY.PARTY_NAME) C_DATA_NOT_FOUND, 
	--ONT_OEXOECCH_XMLP_PKG.c_days_on_hold_cuformula(:S_DAYS_ON_HOLD_CU) C_DAYS_ON_HOLD_CU, 
	ONT_OEXOECCH_XMLP_PKG.c_compute_amountsformula(0, h.transactional_curr_code, ACCT_SITE.CUST_ACCOUNT_ID, ccr.open_ar_days, ccr.open_ar_balance_flag, ccr2.open_ar_days, ccr2.open_ar_balance_flag, ccr.uninvoiced_orders_flag, ccr.orders_on_hold_flag, ccr2.uninvoiced_orders_flag, ccr2.orders_on_hold_flag, nvl ( ccr.shipping_interval , - 1 ), nvl ( ccr2.shipping_interval , - 1 ), nvl ( ot.entry_credit_check_rule_id , - 1 ), nvl ( ot.shipping_credit_check_rule_id , - 1 ), ccr3.open_ar_days, ccr3.open_ar_balance_flag, ccr3.uninvoiced_orders_flag, ccr3.orders_on_hold_flag, nvl ( ccr3.shipping_interval , - 1 ), nvl ( ot.picking_credit_check_rule_id , - 1 ), ccr4.open_ar_days, ccr4.open_ar_balance_flag, ccr4.uninvoiced_orders_flag, ccr4.orders_on_hold_flag, nvl ( ccr4.shipping_interval , - 1 ), nvl ( ot.packing_credit_check_rule_id , - 1 )) C_COMPUTE_AMOUNTS, 
	--ONT_OEXOECCH_XMLP_PKG.c_days_on_hold_crformula(:S_DAYS_ON_HOLD_CR) C_DAYS_ON_HOLD_CR,
	ONT_OEXOECCH_XMLP_PKG.ORDER_AMOUNT_DSPFORMULA(h.transactional_curr_code,sum(nvl(l.ordered_quantity,0)*
		nvl(l.unit_selling_price,0))) Order_Amount_Dsp,
	ONT_OEXOECCH_XMLP_PKG.S_ORDER_AMOUNT_CR_DSPFORMULA(h.transactional_curr_code,sum(nvl(l.ordered_quantity,0)*
		nvl(l.unit_selling_price,0))),
	ONT_OEXOECCH_XMLP_PKG.c_uninv_ord_ship_p c_uninv_ord_ship,
	ONT_OEXOECCH_XMLP_PKG.c_uninv_ord_credit_p c_uninv_ord_credit,
	ONT_OEXOECCH_XMLP_PKG.c_rec_bal_ship_p c_rec_bal_ship,
	ONT_OEXOECCH_XMLP_PKG.c_rec_bal_credit_p c_rec_bal_credit,
	ONT_OEXOECCH_XMLP_PKG.c_tot_order_limit_p c_tot_order_limit,
	ONT_OEXOECCH_XMLP_PKG.c_order_limit_p c_order_limit,
	ONT_OEXOECCH_XMLP_PKG.c_rec_bal_pick_p c_rec_bal_pick,
	ONT_OEXOECCH_XMLP_PKG.c_rec_bal_pack_p c_rec_bal_pack,
	ONT_OEXOECCH_XMLP_PKG.c_uninv_ord_pick_p c_uninv_ord_pick,
	ONT_OEXOECCH_XMLP_PKG.c_uninv_ord_pack_p c_uninv_ord_pack
from 	oe_hold_sources_all hs,
	oe_order_holds_all oh,
	oe_order_headers_all h,
	oe_order_lines_all l,
	oe_order_types_v ot, 
	oe_credit_check_rules ccr,
	oe_credit_check_rules ccr2,
                oe_credit_check_rules ccr3, 
	oe_credit_check_rules ccr4, 
	HZ_CUST_SITE_USES_ALL  SU,
	HZ_PARTY_SITES PARTY_SITE, 
	--HZ_LOC_ASSIGNMENTS LOC_ASSIGN, --comment out by bug 20359138
	HZ_LOCATIONS LOC, 
	HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
	HZ_PARTIES PARTY,
	HZ_CUST_ACCOUNTS CUST_ACCT,
 HZ_CUSTOMER_PROFILES CP
where hs.hold_id = 1
  and hs.hold_entity_code = 'O'
  and hs.released_flag = 'N'
  and oh.hold_source_id = hs.hold_source_id
  and oh.line_id is null
  and oh.hold_release_id is null
  and h.header_id = oh.header_id
  and h.header_id = l.header_id(+)
  and h.order_type_id = ot.order_type_id
  and NVL(h.cancelled_flag, 'N') = 'N'
  and ot.entry_credit_check_rule_id = ccr.credit_check_rule_id(+)
  and ot.shipping_credit_check_rule_id = ccr2.credit_check_rule_id(+)
  and ot.picking_credit_check_rule_id = ccr3.credit_check_rule_id(+)  
  and ot.packing_credit_check_rule_id = ccr4.credit_check_rule_id(+)  
  and h.invoice_to_org_id = su.site_use_id(+)
  AND SU.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID(+) 
  AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID 
  AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID 
  --AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID --comment out for bug 20359138
  --AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99) --comment out for bug 20359138
  AND ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID(+)
  AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
  and h.invoice_to_org_id = cp.site_use_id(+)
  &lp_customer_name
  &lp_customer_number
  &lp_currency_code
  &lp_order_number
  &lp_order_type
  &lp_date_hold_applied
GROUP BY 
     	ACCT_SITE.CUST_ACCOUNT_ID ,
	PARTY.PARTY_NAME ,
	CUST_ACCT.ACCOUNT_NUMBER ,
	h.transactional_curr_code,
	ccr.name,
	nvl(ot.entry_credit_check_rule_id,-1),
	ccr.open_ar_balance_flag,
	ccr.open_ar_days,
	ccr.uninvoiced_orders_flag,
	ccr.orders_on_hold_flag,
	nvl(ccr.shipping_interval,-1),
	ccr2.name,
	nvl(ot.shipping_credit_check_rule_id,-1),
	ccr2.open_ar_balance_flag,
	ccr2.open_ar_days,
	ccr2.uninvoiced_orders_flag,
	ccr2.orders_on_hold_flag,
	nvl(ccr2.shipping_interval,-1),
	ccr3.name,
	nvl(ot.picking_credit_check_rule_id,-1),
	ccr3.open_ar_balance_flag,
	ccr3.open_ar_days,
	ccr3.uninvoiced_orders_flag,
	ccr3.orders_on_hold_flag,
	nvl(ccr3.shipping_interval,-1),
	ccr4.name,
	nvl(ot.packing_credit_check_rule_id,-1),
	ccr4.open_ar_balance_flag,
	ccr4.open_ar_days,
	ccr4.uninvoiced_orders_flag,
	ccr4.orders_on_hold_flag,
	nvl(ccr4.shipping_interval,-1),
	0,
	h.order_number,
	h.ordered_date,
	oh.creation_date,
	sysdate - oh.creation_date
having
                  sum(nvl(l.ordered_quantity,0)) > 0
UNION
select /*MOAC_SQL_CHANGE*/  --add for bug 20359138
	ACCT_SITE.CUST_ACCOUNT_ID INVOICE_TO_CUST_ID,
	PARTY.PARTY_NAME CUSTOMER_NAME,
	CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER,
	LOC.ADDRESS1 ADDRESS1,
	LOC.CITY CITY,
	LOC.STATE STATE,
	h.transactional_curr_code,
	ccr.name entry_credit_check_rule,
	nvl(ot.entry_credit_check_rule_id,-1) entry_rule_id,
	ccr.open_ar_balance_flag entry_open_ar_flag,
	ccr.open_ar_days entry_open_ar_days,
	ccr.uninvoiced_orders_flag entry_uninvoiced_flag,
	ccr.orders_on_hold_flag entry_on_hold_flag,
	nvl(ccr.shipping_interval,-1) entry_shipping_interval,
	ccr2.name ship_credit_check_rule,
	nvl(ot.shipping_credit_check_rule_id,-1) ship_rule_id,
	ccr2.open_ar_balance_flag ship_open_ar_flag,
	ccr2.open_ar_days ship_open_ar_days,
	ccr2.uninvoiced_orders_flag ship_uninvoiced_flag,
	ccr2.orders_on_hold_flag ship_on_hold_flag,
	nvl(ccr2.shipping_interval,-1) ship_shipping_interval,
	ccr3.name pick_credit_check_rule,
	nvl(ot.picking_credit_check_rule_id,-1) pick_rule_id,
	ccr3.open_ar_balance_flag pick_open_ar_flag,
	ccr3.open_ar_days pick_open_ar_days,
	ccr3.uninvoiced_orders_flag pick_uninvoiced_flag,
	ccr3.orders_on_hold_flag pick_on_hold_flag,
	nvl(ccr3.shipping_interval,-1) pick_shipping_interval,
	ccr4.name pack_credit_check_rule,
	nvl(ot.packing_credit_check_rule_id,-1) pack_rule_id,
	ccr4.open_ar_balance_flag pack_open_ar_flag,
	ccr4.open_ar_days pack_open_ar_days,
	ccr4.uninvoiced_orders_flag pack_uninvoiced_flag,
	ccr4.orders_on_hold_flag pack_on_hold_flag,
	nvl(ccr4.shipping_interval,-1) pack_shipping_interval,
        	h.invoice_to_org_id site_use_id,
	h.order_number,
	h.ordered_date,
	sum(nvl(l.ordered_quantity,0)*
		nvl(l.unit_selling_price,0)) order_amount,
	oh.creation_date,
	 round (sysdate - oh.creation_date)  days_on_hold, 
	ONT_OEXOECCH_XMLP_PKG.c_addressformula(null, null, null) C_ADDRESS, 
	ONT_OEXOECCH_XMLP_PKG.c_data_not_foundformula(PARTY.PARTY_NAME) C_DATA_NOT_FOUND, 
	--ONT_OEXOECCH_XMLP_PKG.c_days_on_hold_cuformula(:S_DAYS_ON_HOLD_CU) C_DAYS_ON_HOLD_CU, 
	--Bug 19910063
	ONT_OEXOECCH_XMLP_PKG.c_compute_amountsformula(h.invoice_to_org_id, h.transactional_curr_code, ACCT_SITE.CUST_ACCOUNT_ID, ccr.open_ar_days, ccr.open_ar_balance_flag, ccr2.open_ar_days, ccr2.open_ar_balance_flag, ccr.uninvoiced_orders_flag, ccr.orders_on_hold_flag, ccr2.uninvoiced_orders_flag, ccr2.orders_on_hold_flag, nvl ( ccr.shipping_interval , - 1 ), nvl ( ccr2.shipping_interval , - 1 ), nvl ( ot.entry_credit_check_rule_id , - 1 ), nvl ( ot.shipping_credit_check_rule_id , - 1 ), ccr3.open_ar_days, ccr3.open_ar_balance_flag, ccr3.uninvoiced_orders_flag, ccr3.orders_on_hold_flag, nvl ( ccr3.shipping_interval , - 1 ), nvl ( ot.picking_credit_check_rule_id , - 1 ), ccr4.open_ar_days, ccr4.open_ar_balance_flag, ccr4.uninvoiced_orders_flag, ccr4.orders_on_hold_flag, nvl ( ccr4.shipping_interval , - 1 ), nvl ( ot.packing_credit_check_rule_id , - 1 )) C_COMPUTE_AMOUNTS, 
	--ONT_OEXOECCH_XMLP_PKG.c_days_on_hold_crformula(:S_DAYS_ON_HOLD_CR) C_DAYS_ON_HOLD_CR,
	ONT_OEXOECCH_XMLP_PKG.ORDER_AMOUNT_DSPFORMULA( h.transactional_curr_code,sum(nvl(l.ordered_quantity,0)*
		nvl(l.unit_selling_price,0))) Order_Amount_Dsp,
	ONT_OEXOECCH_XMLP_PKG.S_ORDER_AMOUNT_CR_DSPFORMULA(h.transactional_curr_code,sum(nvl(l.ordered_quantity,0)*
		nvl(l.unit_selling_price,0))),
	ONT_OEXOECCH_XMLP_PKG.c_uninv_ord_ship_p c_uninv_ord_ship,
	ONT_OEXOECCH_XMLP_PKG.c_uninv_ord_credit_p c_uninv_ord_credit,
	ONT_OEXOECCH_XMLP_PKG.c_rec_bal_ship_p c_rec_bal_ship,
	ONT_OEXOECCH_XMLP_PKG.c_rec_bal_credit_p c_rec_bal_credit,
	ONT_OEXOECCH_XMLP_PKG.c_tot_order_limit_p c_tot_order_limit,
	ONT_OEXOECCH_XMLP_PKG.c_order_limit_p c_order_limit,
	ONT_OEXOECCH_XMLP_PKG.c_rec_bal_pick_p c_rec_bal_pick,
	ONT_OEXOECCH_XMLP_PKG.c_rec_bal_pack_p c_rec_bal_pack,
	ONT_OEXOECCH_XMLP_PKG.c_uninv_ord_pick_p c_uninv_ord_pick,
	ONT_OEXOECCH_XMLP_PKG.c_uninv_ord_pack_p c_uninv_ord_pack
from 	oe_hold_sources_all hs,
	oe_order_holds_all oh,
	oe_order_headers_all h,
	oe_order_lines_all l,
	oe_order_types_v ot,
	oe_credit_check_rules ccr,
	oe_credit_check_rules ccr2,
                oe_credit_check_rules ccr3, 
	oe_credit_check_rules ccr4, 
	HZ_CUST_SITE_USES_ALL   SU,
	HZ_PARTY_SITES PARTY_SITE, 
	--HZ_LOC_ASSIGNMENTS LOC_ASSIGN, --comment out by bug 20359138
	HZ_LOCATIONS LOC, 
	HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
	HZ_PARTIES PARTY,
	HZ_CUST_ACCOUNTS CUST_ACCT,
 	HZ_CUSTOMER_PROFILES CP,
	HZ_CUST_PROFILE_AMTS cpa
where h.header_id = oh.header_id
  and oh.line_id is null
  and oh.hold_release_id is null
  and oh.hold_source_id = hs.hold_source_id
  and hs.hold_id = 1
  and hs.hold_entity_code = 'O'
  and hs.released_flag = 'N'
  and h.header_id = l.header_id(+)
  and NVL(h.cancelled_flag, 'N')  = 'N'
  and h.order_type_id = ot.order_type_id
  and ot.entry_credit_check_rule_id = ccr.credit_check_rule_id(+)
  and ot.shipping_credit_check_rule_id = ccr2.credit_check_rule_id(+)
  and ot.picking_credit_check_rule_id = ccr3.credit_check_rule_id(+) 
  and ot.packing_credit_check_rule_id = ccr4.credit_check_rule_id(+) 
  and h.invoice_to_org_id = su.site_use_id(+)
 AND SU.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID(+) 
  AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID 
  AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID 
 -- AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID --comment out by bug 20359138
 -- AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99) --comment out by bug 20359138
  AND ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID(+)
  AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
  and h.invoice_to_org_id = cp.site_use_id --(+)--comment out by bug 20359138 
  and cp.credit_checking = 'Y' --and cp.credit_checking(+) = 'Y' --remove(+) by bug 20359138
  and cp.CUST_ACCOUNT_PROFILE_ID = cpa.CUST_ACCOUNT_PROFILE_ID
  and cpa.currency_code = h.transactional_curr_code
  &lp_customer_name
  &lp_customer_number
  &lp_currency_code
  &lp_order_number
  &lp_order_type
  &lp_date_hold_applied
GROUP BY 
	ACCT_SITE.CUST_ACCOUNT_ID ,
	PARTY.PARTY_NAME ,
	CUST_ACCT.ACCOUNT_NUMBER ,
	LOC.ADDRESS1 ,
	LOC.CITY ,
	LOC.STATE ,
	h.transactional_curr_code,
	ccr.name,
	nvl(ot.entry_credit_check_rule_id,-1),
	ccr.open_ar_balance_flag,
	ccr.open_ar_days,
	ccr.uninvoiced_orders_flag,
	ccr.orders_on_hold_flag,
	nvl(ccr.shipping_interval,-1),
	ccr2.name,
	nvl(ot.shipping_credit_check_rule_id,-1),
	ccr2.open_ar_balance_flag,
	ccr2.open_ar_days,
	ccr2.uninvoiced_orders_flag,
	ccr2.orders_on_hold_flag,
	nvl(ccr2.shipping_interval,-1),
	ccr3.name,
	nvl(ot.picking_credit_check_rule_id,-1),
	ccr3.open_ar_balance_flag,
	ccr3.open_ar_days,
	ccr3.uninvoiced_orders_flag,
	ccr3.orders_on_hold_flag,
	nvl(ccr3.shipping_interval,-1),
	ccr4.name,
	nvl(ot.packing_credit_check_rule_id,-1),
	ccr4.open_ar_balance_flag,
	ccr4.open_ar_days,
	ccr4.uninvoiced_orders_flag,
	ccr4.orders_on_hold_flag,
	nvl(ccr4.shipping_interval,-1),
	h.invoice_to_org_id,
	h.order_number,
	h.ordered_date,
	oh.creation_date,
	sysdate - oh.creation_date
having
                  sum(nvl(l.ordered_quantity,0)) > 0
UNION
select 	/*MOAC_SQL_CHANGE*/ --add for bug 20359138
	ACCT_SITE.CUST_ACCOUNT_ID INVOICE_TO_CUST_ID,
	PARTY.PARTY_NAME CUSTOMER_NAME,
	CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER,
	null address1,
	null city,
	null state,
	h.transactional_curr_code,
	ccr.name entry_credit_check_rule,
	nvl(ot.entry_credit_check_rule_id,-1) entry_rule_id,
	ccr.open_ar_balance_flag entry_open_ar_flag,
	ccr.open_ar_days entry_open_ar_days,
	ccr.uninvoiced_orders_flag entry_uninvoiced_flag,
	ccr.orders_on_hold_flag entry_on_hold_flag,
	nvl(ccr.shipping_interval,-1) entry_shipping_interval,
	ccr2.name ship_credit_check_rule,
	nvl(ot.shipping_credit_check_rule_id,-1) ship_rule_id,
	ccr2.open_ar_balance_flag ship_open_ar_flag,
	ccr2.open_ar_days ship_open_ar_days,
	ccr2.uninvoiced_orders_flag ship_uninvoiced_flag,
	ccr2.orders_on_hold_flag ship_on_hold_flag,
	nvl(ccr2.shipping_interval,-1) ship_shipping_interval,
	ccr3.name pick_credit_check_rule,
	nvl(ot.picking_credit_check_rule_id,-1) pick_rule_id,
	ccr3.open_ar_balance_flag pick_open_ar_flag,
	ccr3.open_ar_days pick_open_ar_days,
	ccr3.uninvoiced_orders_flag pick_uninvoiced_flag,
	ccr3.orders_on_hold_flag pick_on_hold_flag,
	nvl(ccr3.shipping_interval,-1) pick_shipping_interval,
	ccr4.name pack_credit_check_rule,
	nvl(ot.packing_credit_check_rule_id,-1) pack_rule_id,
	ccr4.open_ar_balance_flag pack_open_ar_flag,
	ccr4.open_ar_days pack_open_ar_days,
	ccr4.uninvoiced_orders_flag pack_uninvoiced_flag,
	ccr4.orders_on_hold_flag pack_on_hold_flag,
	nvl(ccr4.shipping_interval,-1) pack_shipping_interval,
	0 site_use_id,
	h.order_number,
	h.ordered_date,
	sum(nvl(l.ordered_quantity,0)*
		nvl(l.unit_selling_price,0)) order_amount,
	oh.creation_date,
	 round (sysdate - oh.creation_date) days_on_hold, 
	ONT_OEXOECCH_XMLP_PKG.c_addressformula(null, null, null) C_ADDRESS, 
	ONT_OEXOECCH_XMLP_PKG.c_data_not_foundformula(PARTY.PARTY_NAME) C_DATA_NOT_FOUND, 
	--ONT_OEXOECCH_XMLP_PKG.c_days_on_hold_cuformula(:S_DAYS_ON_HOLD_CU) C_DAYS_ON_HOLD_CU, 
	ONT_OEXOECCH_XMLP_PKG.c_compute_amountsformula(0, h.transactional_curr_code, ACCT_SITE.CUST_ACCOUNT_ID, ccr.open_ar_days, ccr.open_ar_balance_flag, ccr2.open_ar_days, ccr2.open_ar_balance_flag, ccr.uninvoiced_orders_flag, ccr.orders_on_hold_flag, ccr2.uninvoiced_orders_flag, ccr2.orders_on_hold_flag, nvl ( ccr.shipping_interval , - 1 ), nvl ( ccr2.shipping_interval , - 1 ), nvl ( ot.entry_credit_check_rule_id , - 1 ), nvl ( ot.shipping_credit_check_rule_id , - 1 ), ccr3.open_ar_days, ccr3.open_ar_balance_flag, ccr3.uninvoiced_orders_flag, ccr3.orders_on_hold_flag, nvl ( ccr3.shipping_interval , - 1 ), nvl ( ot.picking_credit_check_rule_id , - 1 ), ccr4.open_ar_days, ccr4.open_ar_balance_flag, ccr4.uninvoiced_orders_flag, ccr4.orders_on_hold_flag, nvl ( ccr4.shipping_interval , - 1 ), nvl ( ot.packing_credit_check_rule_id , - 1 )) C_COMPUTE_AMOUNTS, 
	--ONT_OEXOECCH_XMLP_PKG.c_days_on_hold_crformula(:S_DAYS_ON_HOLD_CR) C_DAYS_ON_HOLD_CR,
	ONT_OEXOECCH_XMLP_PKG.ORDER_AMOUNT_DSPFORMULA( h.transactional_curr_code,sum(nvl(l.ordered_quantity,0)*
		nvl(l.unit_selling_price,0))) Order_Amount_Dsp,
	ONT_OEXOECCH_XMLP_PKG.S_ORDER_AMOUNT_CR_DSPFORMULA(h.transactional_curr_code,sum(nvl(l.ordered_quantity,0)*
		nvl(l.unit_selling_price,0))),
	ONT_OEXOECCH_XMLP_PKG.c_uninv_ord_ship_p c_uninv_ord_ship,
	ONT_OEXOECCH_XMLP_PKG.c_uninv_ord_credit_p c_uninv_ord_credit,
	ONT_OEXOECCH_XMLP_PKG.c_rec_bal_ship_p c_rec_bal_ship,
	ONT_OEXOECCH_XMLP_PKG.c_rec_bal_credit_p c_rec_bal_credit,
	ONT_OEXOECCH_XMLP_PKG.c_tot_order_limit_p c_tot_order_limit,
	ONT_OEXOECCH_XMLP_PKG.c_order_limit_p c_order_limit,
	ONT_OEXOECCH_XMLP_PKG.c_rec_bal_pick_p c_rec_bal_pick,
	ONT_OEXOECCH_XMLP_PKG.c_rec_bal_pack_p c_rec_bal_pack,
	ONT_OEXOECCH_XMLP_PKG.c_uninv_ord_pick_p c_uninv_ord_pick,
	ONT_OEXOECCH_XMLP_PKG.c_uninv_ord_pack_p c_uninv_ord_pack
from 	oe_hold_sources_all hs,
	oe_order_holds_all oh,
	oe_order_headers_all h,
	oe_order_lines_all l,
	oe_order_types_v ot, 
	oe_credit_check_rules ccr,
	oe_credit_check_rules ccr2,
	oe_credit_check_rules ccr3,  
	oe_credit_check_rules ccr4,  
	HZ_CUST_SITE_USES_ALL SU,
	HZ_PARTY_SITES PARTY_SITE, 
	--HZ_LOC_ASSIGNMENTS LOC_ASSIGN, --comment out for bug 20359138
	HZ_LOCATIONS LOC, 
	HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
	HZ_PARTIES PARTY,
	HZ_CUST_ACCOUNTS CUST_ACCT,
 	HZ_CUSTOMER_PROFILES cp,
	HZ_CUST_PROFILE_AMTS cpa
where h.header_id = oh.header_id
  and NVL(h.cancelled_flag, 'N')  = 'N' 
  and oh.line_id is null
  and oh.hold_release_id is null
  and oh.hold_source_id = hs.hold_source_id
  and hs.hold_id = 1
  and hs.hold_entity_code = 'O'
  and hs.released_flag = 'N'
  and h.header_id = l.header_id(+)
  and h.order_type_id = ot.order_type_id
  and ot.entry_credit_check_rule_id = ccr.credit_check_rule_id(+)
  and ot.shipping_credit_check_rule_id = ccr2.credit_check_rule_id(+)
  and ot.picking_credit_check_rule_id = ccr3.credit_check_rule_id(+) 
  and ot.packing_credit_check_rule_id = ccr4.credit_check_rule_id(+) 
  and h.invoice_to_org_id = su.site_use_id(+)
     AND SU.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID(+) 
  AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID 
  AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID 
  --AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID --comment out by bug 20359138
  --AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99) --comment out by bug 20359138
  AND ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID(+)
  AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
  and h.invoice_to_org_id = cp.site_use_id --(+) --remove (+) by bug 20359138
  and cp.credit_checking = 'Y'  --and cp.credit_checking(+) = 'Y' --remove (+) by bug 20359138 
and cp.CUST_ACCOUNT_PROFILE_ID = cpa.CUST_ACCOUNT_PROFILE_ID(+)
and cpa.currency_code=h.transactional_curr_code --bug 20359138
  &lp_customer_name
  &lp_customer_number
  &lp_currency_code
  &lp_order_number
  &lp_order_type
  &lp_date_hold_applied
GROUP BY 
	ACCT_SITE.CUST_ACCOUNT_ID ,
	PARTY.PARTY_NAME ,
	CUST_ACCT.ACCOUNT_NUMBER ,
	h.transactional_curr_code,
	ccr.name,
	nvl(ot.entry_credit_check_rule_id,-1),
	ccr.open_ar_balance_flag,
	ccr.open_ar_days,
	ccr.uninvoiced_orders_flag,
	ccr.orders_on_hold_flag,
	nvl(ccr.shipping_interval,-1),
	ccr2.name,
	nvl(ot.shipping_credit_check_rule_id,-1),
	ccr2.open_ar_balance_flag,
	ccr2.open_ar_days,
	ccr2.uninvoiced_orders_flag,
	ccr2.orders_on_hold_flag,
	nvl(ccr2.shipping_interval,-1),
                ccr3.name,
	nvl(ot.picking_credit_check_rule_id,-1),
	ccr3.open_ar_balance_flag,
	ccr3.open_ar_days,
	ccr3.uninvoiced_orders_flag,
	ccr3.orders_on_hold_flag,
	nvl(ccr3.shipping_interval,-1),
	ccr4.name,
	nvl(ot.packing_credit_check_rule_id,-1),
	ccr4.open_ar_balance_flag,
	ccr4.open_ar_days,
	ccr4.uninvoiced_orders_flag,
	ccr4.orders_on_hold_flag,
	nvl(ccr4.shipping_interval,-1),
	0,
	h.order_number,
	h.ordered_date,
	oh.creation_date,
	sysdate - oh.creation_date
having
                  sum(nvl(l.ordered_quantity,0)) > 0
order by 2,7,36,8,37
Parameter Name SQL text Validation
Ledger Id
 
Number
Currency Code
 
LOV Oracle
Hold Applied Date (To)
 
Date
Hold Applied Date (From)
 
Date
Order Number
 
Number
Order Type
 
LOV Oracle
Customer Number
 
LOV Oracle
Customer Name
 
LOV Oracle