ONT Orders on Credit Check Hold

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