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

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