AR - Average Days Late
Posted: 19 November 2007 11:47 PM
Newbie
Rank
Total Posts:  2
Joined  2007-11-15

Iam trying to print the Average Days Late data for Customers in our custom report.
As Per Metalink Note:1059874.6 , I used the formula
sum(apply_date - due_date) * amount_applied) / sum(amount_applied) ..But still iam not able to Match it with the Avg. Days Late field in Customer Accounts Screen.
(Navigation : Receivable Manager—> Collection—> Customer Accounts “Avg. Days Late”)

Has anyone faced this situation ? If so , can i get the SQL used to derive this Data?

Thanks
KK

Profile
 
Posted: 21 November 2007 10:06 AM   [ # 1 ]
Newbie
Rank
Total Posts:  2
Joined  2007-11-15

Figured it out finally ....

SELECT CUSTOMER_NAME,CUSTOMER_NUMBER,ROUND(NVL(SUM((RA.APPLY_DATE - PS.DUE_DATE ) * (RA.AMOUNT_APPLIED) ),0)
/ DECODE(SUM(RA.AMOUNT_APPLIED),0,1, NULL ,1,SUM(RA.AMOUNT_APPLIED)) ,2)
FROM
APPS.RA_CUSTOMERS C,
AR_PAYMENT_SCHEDULES PS,
AR_RECEIVABLE_APPLICATIONS RA
WHERE c.status = ‘A’
AND PS.CUSTOMER_ID=C.CUSTOMER_ID
AND RA.APPLIED_PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND RA.STATUS = ‘APP’
AND RA.DISPLAY = ‘Y’
AND NVL(PS.RECEIPT_CONFIRMED_FLAG,‘Y’) = ‘Y’
—AND PS.INVOICE_CURRENCY_CODE = NVL(‘USD’,PS.INVOICE_CURRENCY_CODE)
GROUP BY CUSTOMER_NAME,CUSTOMER_NUMBER

Profile