AR Receipt Analysis - Days Late

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Receipt Analysis - Days Late (XML)
Short Name: ARXCPH_XML
DB package: AR_ARXCPH_XMLP_PKG
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))
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
Order By
 
LOV Oracle
Ledger ID
 
LOV Oracle
Collector High
 
LOV Oracle
Collector Low
 
LOV Oracle
Payment Term High
 
LOV Oracle
Payment Term Low
 
LOV Oracle
Transaction Date High
 
Date
Transaction Date Low
 
Date
Invoice Number High
 
LOV Oracle
Invoice Number Low
 
LOV Oracle
Customer Number High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
Customer Name Low
 
LOV Oracle
Ask a question