Reports2017-11-18T12:27:27+00:00

AR Past Due Invoice

Description
Categories: Enginatics, Financials
Application: Receivables
Source: Past Due Invoice Report
Short Name: ARXPDI_XML

select
apsa.invoice_currency_code currency,
hp.party_name customer_name,
hca.account_number,
apsa.trx_number invoice_number,
apsa.class,
rctta.name invoice_type,
rcta.purchase_order,
apsa.trx_date invoice_date,
apsa.due_date,
apsa.amount_due_original invoice_amount,
apsa.tax_original tax_amount,
arpt_sql_func_util.get_balance_due_as_of_date(payment_schedule_id,:p_as_of_date,class) balance_due,
:p_as_of_date-trunc(apsa.due_date) days_past_due,
ac.name collector,
rs.name salesperson
from
hz_customer_profiles hcp,
hz_customer_profiles hcp1,
ar_collectors ac,
hz_cust_accounts hca,
hz_parties hp,
ra_customer_trx_all rcta,
ra_salesreps rs,
ar_payment_schedules_all apsa,
ra_cust_trx_types_all rctta
where
apsa.actual_date_closed>:p_as_of_date and
arpt_sql_func_util.get_balance_due_as_of_date(payment_schedule_id,:p_as_of_date,class)!=0 and
1=1 and
hca.cust_account_id=rcta.bill_to_customer_id and
hca.party_id=hp.party_id and
rcta.customer_trx_id=apsa.customer_trx_id and
rcta.primary_salesrep_id=rs.salesrep_id (+) and
hcp.cust_account_id=hca.cust_account_id and
hcp.site_use_id is null and
hcp1.site_use_id (+)=apsa.customer_site_use_id and
ac.collector_id=nvl(hcp1.collector_id, hcp.collector_id) and
rctta.cust_trx_type_id=rcta.cust_trx_type_id and
rctta.org_id=rcta.org_id
order by
decode(upper(:p_order_by),'SALESPERSON',rs.name, 'CUSTOMER',hp.party_name,null),
decode(upper(:p_order_by),'BALANCE DUE',arpt_sql_func_util.get_balance_due_as_of_date(payment_schedule_id,:p_as_of_date,class),0)

Parameter Name SQL text Validation
Salesperson/Agent High
rs.name<=:p_salesrep_high
LOV Oracle
Salesperson/Agent Low
rs.name>=:p_salesrep_low
LOV Oracle
Type High
rctta.name<=:p_invoice_type_high
LOV Oracle
Type Low
rctta.name>=:p_invoice_type_low
LOV Oracle
Customer Number High
hca.account_number<=:p_customer_number_high
LOV Oracle
Customer Number Low
hca.account_number>=:p_customer_number_low
LOV Oracle
Customer Name High
hp.party_name<=:p_customer_name_high
LOV Oracle
Customer Name Low
hp.party_name>=:p_customer_name_low
LOV Oracle
Collector High
ac.name<=:p_collector_high
LOV Oracle
Collector Low
ac.name>=:p_collector_low
LOV Oracle
Balance Due High
 
Number
Balance Due Low
 
Number
Days Late High
 
Number
Days Late Low
 
Number
As of Date
 
Date
Order By
 
LOV Oracle

By continuing to use the site, you agree to the use of cookies. Accept