select DISTINCT
ACCT_SITE.CUST_ACCOUNT_ID CUSTOMER_ID,
SUBSTRB(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME,
CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER,
loc.address1 Address1,
loc.city City,
loc.state 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,
0 Site_Use_Id,
h.order_number Order_Number,
h.ordered_date Order_Date,
sum (decode(l.line_category_code,'ORDER',
(nvl(l.ordered_quantity,0)* nvl(l.unit_selling_price,0)),
-1 * (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,
loc.address2,
loc.address3,
loc.postal_code,
h.cust_po_number,h.header_id
,cols.name collector_name,
oh.order_hold_id,
oh.attribute1 notification_date
from oe_hold_sources hs,
oe_order_holds oh,
oe_order_headers h,
oe_order_lines l,
oe_order_types_v ot,
oe_credit_check_rules ccr,
oe_credit_check_rules ccr2,
RA_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
HZ_PARTIES PARTY,
HZ_CUST_ACCOUNTS CUST_ACCT,
ar_customer_profiles cp,
hz_customer_profiles prof,
ar_collectors cols
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 h.invoice_to_org_id = su.site_use_id(+)
AND SU.ADDRESS_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
AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99)
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(+)
and cust_acct.cust_account_id = prof.cust_account_id
and prof.site_use_id is null
and prof.collector_id = cols.collector_id
and CUST_ACCT.ACCOUNT_NUMBER = :p_customer_number
GROUP BY
ACCT_SITE.CUST_ACCOUNT_ID ,
SUBSTRB(PARTY.PARTY_NAME,1,50) ,
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_or_hold_slag,
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),
0,
h.order_number,
h.ordered_date,
oh.creation_date,
sysdate - oh.creation_date,loc.address2,
loc.address3,
loc.postal_code,
h.cust_po_number,h.header_id
,cols.name,
oh.order_hold_id,
oh.attribute1
having
sum(nvl(l.ordered_quantity,0)) > 0