AR Credit Hold

SELECT 
ps.invoice_currency_code           Currency_Main,
nvl(status.meaning,'')    Account_Status, 
decode (upper(:p_order_by),'CUSTOMER', substrb(party.party_name,1,50), NULL)  Dummy_Cust, 
decode (upper(:p_order_by),'CUSTOMER NUMBER',cust.account_number,NULL) Dummy_Cust_no, 
collect.name Dummy_Collect,
substrb(party.party_name,1,50)                 Customer_Name, 
cust.account_number               Customer_Number, 
cust.cust_account_id                   Customer_Id, 
trunc(sysdate - decode(hist.creation_date,NULL,max(cust.creation_date),hist.creation_date))    Days_On_Credit_Hold,  
sum(round(ps.amount_due_remaining, 2)) Balance_Due, 
sum(decode(sign(trunc(sysdate) - ps.due_date),
           1, round(ps.amount_due_remaining, 2),
           0))   Balance_Past_Due ,
acct_site.cust_acct_site_id                    Address_Id, 
loc.address1                      Address1,  
loc.address2                      Address2, 
loc.address3                      Address3,
loc.address4                      Address4,
substrb(loc.city,1,22-nvl(lengthb(loc.state || loc.postal_code),0))
	|| ', ' || loc.state || ' ' || loc.postal_code City_State_Zip,  
yesno.meaning,  
decode(upper(:p_order_by),
       'CUSTOMER', substrb(party.party_name,1,50),
       'CUSTOMER NUMBER',cust.account_number,
        'COLLECTOR', collect.name,
        substrb(party.party_name,1,50)) order_by, 
	AR_ARXCHR_XMLP_PKG.c_no_data_foundformula(ps.invoice_currency_code) C_NO_DATA_FOUND, 
	--&Currency_Main c_currency_label,
	ps.invoice_currency_code c_currency_label,
	--&account_status c_status_label, 
	nvl(status.meaning,'') c_status_label,
	AR_ARXCHR_XMLP_PKG.c_get_phone_numberformula(acct_site.cust_acct_site_id, cust.cust_account_id) C_GET_PHONE_NUMBER, 
	AR_ARXCHR_XMLP_PKG.c_primary_contactformula(acct_site.cust_acct_site_id) C_PRIMARY_CONTACT,
	AR_ARXCHR_XMLP_PKG.c_phone_number_p c_phone_number,
	AR_ARXCHR_XMLP_PKG.c_contact_id_p c_contact_id,
	AR_ARXCHR_XMLP_PKG.c_contact_name_p c_contact_name
FROM 
hz_cust_accounts cust,
hz_parties party,
hz_customer_profiles profile,
ar_credit_histories hist,
ar_collectors collect,
ar_payment_schedules_all ps,
hz_cust_site_uses_all site,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
ar_lookups status,
ar_lookups yesno
WHERE 
profile.cust_account_id = cust.cust_account_id
and cust.party_id = party.party_id
and profile.site_use_id is null  
and profile.credit_hold = 'Y'    
and hist.customer_id(+) = cust.cust_account_id 
and hist.on_hold(+) = 'N'
and hist.site_use_id(+) is null
and not exists
(select 'newer on_hold record exists'
 from ar_credit_histories hist2
 where hist2.customer_id = hist.customer_id
 and hist2.site_use_id is null
 and hist2.on_hold = 'N'
 and (hist2.creation_date > hist.creation_date
      or
      (hist2.creation_date = hist.creation_date
       and hist2.credit_history_id > hist.credit_history_id)))
and collect.collector_id = profile.collector_id
and profile.account_status = status.lookup_code(+)
and status.lookup_type(+) = 'ACCOUNT_STATUS'
and acct_site.cust_account_id = cust.cust_account_id
and acct_site.party_site_id = party_site.party_site_id
and loc.location_id = party_site.location_id
and acct_site.cust_acct_site_id = site.cust_acct_site_id
and site.site_use_code = 'BILL_TO'
and ps.customer_site_use_id(+) = site.site_use_id
and ps.status(+) = 'OP'
and site.primary_flag = yesno.lookup_code
and yesno.lookup_type = 'YES/NO'
&lp_status_low
&lp_status_high
&lp_customer_name_low
&lp_customer_name_high
&lp_customer_number_low
&lp_customer_number_high
&lp_collector_low
&lp_collector_high
&lp_currency_code
&p_org_where_addr
GROUP BY
ps.invoice_currency_code,
status.meaning,
party.party_name,
cust.account_number,
cust.cust_account_id,
hist.creation_date,
ps.customer_site_use_id,
acct_site.cust_acct_site_id,
loc.address1,
loc.address2,
loc.address3,
loc.address4,
loc.city,
loc.state,
loc.postal_code,
yesno.meaning,
collect.name
UNION
SELECT 
ps.invoice_currency_code,
nvl(status.meaning,''),  
decode (upper(:p_order_by),'CUSTOMER',substrb(party.party_name,1,50), NULL)  Dummy_Cust, 
decode (upper(:p_order_by),'CUSTOMER NUMBER',cust.account_number,NULL) Dummy_Cust_no, 
collect.name Dummy_Collect,
substrb(party.party_name,1,50) customer_name,  
cust.account_number customer_number,  
cust.cust_account_id customer_id,  
trunc(sysdate - decode(hist.creation_date,NULL,max(cust.creation_date),hist.creation_date)), 
sum(round(ps.amount_due_remaining, 2)),  
sum(decode(sign(trunc(sysdate) - ps.due_date),
           1, round(ps.amount_due_remaining, 2),
           0)),  
acct_site.cust_acct_site_id address_id,  
loc.address1,    
loc.address2,    
loc.address3,    
loc.address4,    
substrb(loc.city,1,22-nvl(lengthb(loc.state || loc.postal_code),0))
	|| ', ' || loc.state || ' ' || loc.postal_code,  
yesno.meaning,  
decode(upper(:p_order_by),
       'CUSTOMER', substrb(party.party_name,1,50),
       'CUSTOMER NUMBER',cust.account_number,
        'COLLECTOR', collect.name,
       substrb(party.party_name,1,50)), 
	AR_ARXCHR_XMLP_PKG.c_no_data_foundformula(ps.invoice_currency_code) C_NO_DATA_FOUND, 
	--&Currency_Main c_currency_label, 
	ps.invoice_currency_code c_currency_label,
	--&account_status c_status_label, 
	nvl(status.meaning,'') c_currency_label,
	AR_ARXCHR_XMLP_PKG.c_get_phone_numberformula(acct_site.cust_acct_site_id, cust.cust_account_id) C_GET_PHONE_NUMBER, 
	AR_ARXCHR_XMLP_PKG.c_primary_contactformula(acct_site.cust_acct_site_id) C_PRIMARY_CONTACT,
	AR_ARXCHR_XMLP_PKG.c_phone_number_p c_phone_number,
	AR_ARXCHR_XMLP_PKG.c_contact_id_p c_contact_id,
	AR_ARXCHR_XMLP_PKG.c_contact_name_p c_contact_name
FROM 
hz_cust_accounts cust,
hz_parties party,
hz_customer_profiles profile,
ar_credit_histories hist,
ar_collectors collect,
ar_payment_schedules_all ps,
hz_cust_site_uses_all site,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
ar_lookups status,
ar_lookups yesno
WHERE 
profile.cust_account_id = cust.cust_account_id
and cust.party_id = party.party_id
and profile.site_use_id is not null  
and profile.credit_hold = 'Y'    
and not exists
(select 'customer level hold exists'
 from hz_customer_profiles p
 where p.cust_account_id = cust.cust_account_id
 and p.site_use_id is null
 and p.credit_hold = 'Y')
and hist.customer_id(+) = profile.cust_account_id
and hist.on_hold(+) = 'N'
and hist.site_use_id(+) = profile.site_use_id
and not exists
(select 'newer on_hold record exists'
 from ar_credit_histories hist2
 where hist2.customer_id = hist.customer_id
 and hist2.site_use_id = hist.site_use_id
 and hist2.on_hold = 'N'
 and (hist2.creation_date > hist.creation_date
      or
      (hist2.creation_date = hist.creation_date
       and hist2.credit_history_id > hist.credit_history_id)))
and collect.collector_id = profile.collector_id
and profile.account_status = status.lookup_code(+)
and status.lookup_type(+) = 'ACCOUNT_STATUS'
and site.site_use_id = profile.site_use_id
and site.site_use_code = 'BILL_TO'
and acct_site.cust_acct_site_id = 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 ps.customer_site_use_id(+) = site.site_use_id
and ps.status(+) = 'OP'
and site.primary_flag = yesno.lookup_code
and yesno.lookup_type = 'YES/NO'
&lp_status_low
&lp_status_high
&lp_customer_name_low
&lp_customer_name_high
&lp_customer_number_low
&lp_customer_number_high
&lp_collector_low
&lp_collector_high
&lp_currency_code
&p_org_where_site
GROUP BY
ps.invoice_currency_code,
status.meaning,
party.party_name,
cust.account_number,
cust.cust_account_id,
hist.creation_date,
ps.customer_site_use_id,
acct_site.cust_acct_site_id,
loc.address1,
loc.address2,
loc.address3,
loc.address4,
loc.city,
loc.state,
loc.postal_code,
yesno.meaning,
collect.name
UNION
SELECT 
ps.invoice_currency_code,
nvl(status.meaning,''),  
decode (upper(:p_order_by),'CUSTOMER', substrb(party.party_name,1,50), NULL)  Dummy_Cust, 
decode (upper(:p_order_by),'CUSTOMER NUMBER',cust.account_number,NULL) Dummy_Cust_no, 
collect.name Dummy_Collect,
substrb(party.party_name,1,50) customer_name,  
cust.account_number customer_number,  
cust.cust_account_id customer_id,  
0,  
sum(round(nvl(ps.amount_due_remaining,0), 2)),  
sum(decode(sign(trunc(sysdate) - ps.due_date),
           1, round(nvl(ps.amount_due_remaining,0), 2),
           0)),  
to_number(null),  
null,    
null,    
null,    
null,    
null,  
null,  
decode(upper(:p_order_by),
       'CUSTOMER',substrb(party.party_name,1,50),
       'CUSTOMER NUMBER',cust.account_number,
        'COLLECTOR', collect.name,
       substrb(party.party_name,1,50)), 
	AR_ARXCHR_XMLP_PKG.c_no_data_foundformula(ps.invoice_currency_code) C_NO_DATA_FOUND, 
	--&Currency_Main c_currency_label, 
	ps.invoice_currency_code c_currency_label,
	--&account_status c_status_label, 
	nvl(status.meaning,'') c_status_label, 
	AR_ARXCHR_XMLP_PKG.c_get_phone_numberformula(to_number(null), cust.cust_account_id) C_GET_PHONE_NUMBER, 
	AR_ARXCHR_XMLP_PKG.c_primary_contactformula(to_number(null)) C_PRIMARY_CONTACT,
	AR_ARXCHR_XMLP_PKG.c_phone_number_p c_phone_number,
	AR_ARXCHR_XMLP_PKG.c_contact_id_p c_contact_id,
	AR_ARXCHR_XMLP_PKG.c_contact_name_p c_contact_name
FROM 
hz_cust_accounts cust,
hz_parties party,
hz_customer_profiles profile,
hz_cust_acct_sites_all   acct_site,
ar_collectors collect,
ar_lookups status,
ar_payment_schedules_all ps
WHERE 
profile.cust_account_id = cust.cust_account_id
and cust.party_id = party.party_id
and acct_site.cust_account_id = cust.cust_account_id and profile.site_use_id is  null
and profile.credit_hold = 'Y'    
and collect.collector_id = profile.collector_id
and ps.customer_id (+) = cust.cust_account_id
and ps.customer_site_use_id is null
and ps.status (+) = 'OP'
and profile.account_status = status.lookup_code(+)
and status.lookup_type(+) = 'ACCOUNT_STATUS'
and NVL(ps.ORG_ID, :p_reporting_entity_id) = 
DECODE(:p_reporting_level,3000,acct_site.ORG_ID,:p_reporting_entity_id ) 
&lp_status_low
&lp_status_high
&lp_customer_name_low
&lp_customer_name_high
&lp_customer_number_low
&lp_customer_number_high
&lp_collector_low
&lp_collector_high
&lp_currency_code
&p_org_where_addr
GROUP BY
ps.invoice_currency_code,
status.meaning,
party.party_name,
cust.account_number,
cust.cust_account_id, 
collect.name 
ORDER BY 
1,
2,
19,   
 10