AR Receipt Analysis - Days Late
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Receipt Analysis - Days Late
Application: Receivables
Source: Receipt Analysis - Days Late (XML)
Short Name: ARXCPH_XML
DB package: AR_ARXCPH_XMLP_PKG
Description: Receipt Analysis - Days Late
Application: Receivables
Source: Receipt Analysis - Days Late (XML)
Short Name: ARXCPH_XML
DB package: AR_ARXCPH_XMLP_PKG
Run
AR Receipt Analysis - Days Late and other Oracle EBS reports with Blitz Report™ on our demo environment
select ar_payment_schedules.customer_id customer_id, decode (ar_receivable_applications.amount_applied_from, NULL, NULL, '*') cc_flag, &P_SORT dcolsort, SUBSTRB(PARTY.PARTY_NAME,1,50) customer, CUST.ACCOUNT_NUMBER customer_number, ra_cust_trx_types.name cust_trx_type_id, ar_payment_schedules.customer_trx_id customer_trx_id, ar_payment_schedules.trx_number invoice_number, (ar_payment_schedules.due_date) due_date, ar_payment_schedules.terms_sequence_number terms_sequence_number, ar_payment_schedules.amount_due_original invoice_amount, ACCT_SITE.CUST_ACCT_SITE_ID address_id, LOC.city city, LOC.state state, ra_terms.name terms, ar_receivable_applications.amount_applied payment, (ar_receivable_applications.apply_date-ar_payment_schedules.due_date) days_late, (ar_receivable_applications.apply_date-ar_payment_schedules.due_date)* (ar_receivable_applications.amount_applied) weighted_days_late, ar_payment_schedules.invoice_currency_code currency_code, ar_cash_receipts.receipt_number payment_number, ar_payment_schedules.payment_schedule_id sched_id, AR_ARXCPH_XMLP_PKG.wt_avg_days_lateformula(:Sum_Payment, :Sum_W_Days_Late) Wt_Avg_Days_Late, AR_ARXCPH_XMLP_PKG.average_days_lateformula(:rec_counter, :Sum_Days_Late) Average_Days_Late, -- AR_ARXCPH_XMLP_PKG.Skip_Sum_p Skip_Sum, AR_ARXCPH_XMLP_PKG.set_addr_flagformula(ACCT_SITE.CUST_ACCT_SITE_ID) Set_Addr_Flag, AR_ARXCPH_XMLP_PKG.Addr_Prn_Flag_p Addr_Prn_Flag, AR_ARXCPH_XMLP_PKG.Prev_Addr_Id_p Prev_Addr_Id, AR_ARXCPH_XMLP_PKG.skip_inv_sumformula(ar_payment_schedules.amount_due_original, ar_payment_schedules.customer_id, ar_payment_schedules.invoice_currency_code, ar_payment_schedules.customer_trx_id, ar_payment_schedules.terms_sequence_number) Skip_Inv_Sum, AR_ARXCPH_XMLP_PKG.prev_customer_trx_id_p prev_customer_trx_id, AR_ARXCPH_XMLP_PKG.prev_currency_code_p prev_currency_code, AR_ARXCPH_XMLP_PKG.prev_customer_id_p prev_customer_id, AR_ARXCPH_XMLP_PKG.prev_terms_p prev_terms, AR_ARXCPH_XMLP_PKG.D_invoice_amountFormula(SUBSTRB(PARTY.PARTY_NAME,1,50)), AR_ARXCPH_XMLP_PKG.getInvoiceAmount(ar_payment_schedules.payment_schedule_id,ar_payment_schedules.amount_due_original) Invoice_Amount_non_duplicate from ar_payment_schedules, ar_receivable_applications, HZ_CUST_ACCT_SITES ACCT_SITE, HZ_LOCATIONS LOC, HZ_PARTY_SITES PARTY_SITE, ra_terms, ra_customer_trx, ra_cust_trx_types, ar_collectors, HZ_customer_profiles cust_cp, HZ_customer_profiles site_cp, HZ_CUST_ACCOUNTS CUST, HZ_PARTIES PARTY, HZ_CUST_site_uses SITE, ar_cash_receipts where ar_payment_schedules.term_id = ra_terms.term_id(+) and ar_receivable_applications.status = 'APP' and ar_receivable_applications.display = 'Y' and nvl(ar_receivable_applications.confirmed_flag,'Y') = 'Y' and ar_receivable_applications.set_of_books_id = :p_set_of_books_id and ar_receivable_applications.applied_payment_schedule_id = ar_payment_schedules.payment_schedule_id and ar_payment_schedules.class<> 'CM' and ar_payment_schedules.customer_trx_id = ra_customer_trx.customer_trx_id (+) and ra_customer_trx.cust_trx_type_id = ra_cust_trx_types.cust_trx_type_id (+) and ar_payment_schedules.customer_id = CUST.CUST_ACCOUNT_ID AND CUST.PARTY_ID = PARTY.PARTY_ID and ar_payment_schedules.customer_site_use_id = SITE.site_use_id and nvl(site_cp.collector_id,cust_cp.collector_id) = ar_collectors.collector_id and CUST.CUST_ACCOUNT_ID = cust_cp.CUST_ACCOUNT_ID and cust_cp.site_use_id is null and ar_payment_schedules.customer_site_use_id = site_cp.site_use_id(+) and SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID &lp_invoice_num_low &lp_invoice_num_high &lp_customer_name_low &lp_customer_name_high &lp_cust_num_low &lp_cust_num_high &lp_trx_date_low &lp_trx_date_high &lp_collector_low &lp_collector_high &lp_terms_low &lp_terms_high and ar_receivable_applications.cash_receipt_id = ar_cash_receipts.cash_receipt_id /*order by PARTY.PARTY_NAME, CUST.CUST_ACCOUNT_ID, ar_payment_schedules.invoice_currency_code, ar_payment_schedules.trx_number, ar_payment_schedules.payment_schedule_id, ar_payment_schedules.due_date, ar_payment_schedules.terms_sequence_number order by 1,5,2,4,19,8,7,6,21,10,15,9,3,11,20,16,17,18*/ ORDER BY 1 ASC,5 ASC,2 ASC,4 ASC,19 ASC,8 ASC,7 ASC,6 ASC,21 ASC,10 ASC,15 ASC,9 ASC , PARTY.PARTY_NAME , CUST.CUST_ACCOUNT_ID , ar_payment_schedules.invoice_currency_code , ar_payment_schedules.trx_number , ar_payment_schedules.payment_schedule_id , ar_payment_schedules.due_date , ar_payment_schedules.terms_sequence_number |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
Customer Name Low |
|
LOV Oracle | |
Customer Name High |
|
LOV Oracle | |
Customer Number Low |
|
LOV Oracle | |
Customer Number High |
|
LOV Oracle | |
Invoice Number Low |
|
LOV Oracle | |
Invoice Number High |
|
LOV Oracle | |
Transaction Date Low |
|
Date | |
Transaction Date High |
|
Date | |
Payment Term Low |
|
LOV Oracle | |
Payment Term High |
|
LOV Oracle | |
Collector Low |
|
LOV Oracle | |
Collector High |
|
LOV Oracle |