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",
substrb(hp.party_name,1,50) "Customer Name",
hca.account_number "Customer Number",
apsa.trx_number "Invoice Number",
substr(arpt_sql_func_util.get_trx_type_details(rcta.cust_trx_type_id,'NAME'),1,4) "Type",
rcta.purchase_order "Purchase Order",
apsa.trx_date "Invoice Date",
apsa.due_date "Due Date",
apsa.amount_due_original "Invoice Amount",
nvl(apsa.tax_original,0.00) "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) "Past Days Due",
ac.name "Collector",
rs.name "Salesperson/Agent",
--:lp_query_show_bill cons_bill_number,
ar_arxpdi_xmlp_pkg.c_data_foundformula(apsa.invoice_currency_code) c_data_found,
ar_arxpdi_xmlp_pkg.c_temp_salformula(apsa.invoice_currency_code,decode(upper(:p_order_by),upper(:p_salesperson),rs.name, null)) c_temp_sal,
ar_arxpdi_xmlp_pkg.c_custom_checkformula(apsa.invoice_currency_code, hca.cust_account_id) c_custom_check,
ar_arxpdi_xmlp_pkg.rp_old_curr_p rp_old_curr,
ar_arxpdi_xmlp_pkg.rp_curr_check_p rp_curr_check,
ar_arxpdi_xmlp_pkg.rp_old_customer_p rp_old_customer,
ar_arxpdi_xmlp_pkg.rp_cust_check_p rp_cust_check,
ar_arxpdi_xmlp_pkg.invoice_number_consformula(apsa.trx_number, rcta.trx_number) invoice_number_cons,
decode(upper(:p_order_by),upper(:p_salesperson),rs.name, null) salesrep,
decode(upper(:p_order_by),upper(:p_balance_due),arpt_sql_func_util.get_balance_due_as_of_date(payment_schedule_id,:p_as_of_date,class),0) balance_due_customer,
rcta.customer_trx_id ,
hca.cust_account_id cust_id
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 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
and 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
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