AR Receipt Analysis - Days Late

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