ONT Orders on Credit Check Hold
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Orders on Credit Check Hold Report
Application: Order Management
Source: Orders on Credit Check Hold Report (XML)
Short Name: OEXOECCH_XML
DB package: ONT_OEXOECCH_XMLP_PKG
Description: Orders on Credit Check Hold Report
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 | |
|---|---|---|---|
| Operating Unit | LOV | ||
| Customer Name | LOV Oracle | ||
| Customer Number | LOV Oracle | ||
| Order Type | LOV Oracle | ||
| Order Number | Number | ||
| Hold Applied Date (From) | Date | ||
| Hold Applied Date (To) | Date | ||
| Currency Code | LOV Oracle |