AR Credit Hold

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Credit Hold Report (XML)
Short Name: ARXCHR_XML
DB package: AR_ARXCHR_XMLP_PKG
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
Parameter Name SQL text Validation
Ledger
 
LOV Oracle
Currency
 
LOV Oracle
Customer Number High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
Customer Name Low
 
LOV Oracle
Collector High
 
LOV Oracle
Collector Low
 
LOV Oracle
Account Status High
 
LOV Oracle
Account Status
 
LOV Oracle
Order By
 
LOV Oracle
Reporting Context
 
LOV Oracle
Reporting level
 
LOV Oracle