SELECT customer_number, customer_name, paymt, day_030, day_031_060,
day_061_090, day_091_120, day_121_150, day_151_180, day_181, no_oftrx
FROM (SELECT pay.org_id, pay.customer_id
-- ,pay.trx_number, pay.trx_date
-- ,pay.AMOUNT_DUE_ORIGINAL, pay.AMOUNT_DUE_REMAINING,to_Date ('asofdate') - trunc(pay.trx_date)
,
SUM
((CASE
WHEN (TO_DATE (:p_as_of_date) - TRUNC (pay.trx_date)) <=
30
THEN AMOUNT_DUE_ORIGINAL
END
)
) day_030,
SUM
((CASE
WHEN (TO_DATE (:p_as_of_date) - TRUNC (pay.trx_date))
BETWEEN 31
AND 60
THEN AMOUNT_DUE_ORIGINAL
END
)
) day_031_060,
SUM
((CASE
WHEN (TO_DATE (:p_as_of_date) - TRUNC (pay.trx_date))
BETWEEN 61
AND 90
THEN AMOUNT_DUE_ORIGINAL
END
)
) day_061_090,
SUM
((CASE
WHEN (TO_DATE (:p_as_of_date) - TRUNC (pay.trx_date))
BETWEEN 91
AND 120
THEN AMOUNT_DUE_ORIGINAL
END
)
) day_091_120,
SUM
((CASE
WHEN (TO_DATE (:p_as_of_date) - TRUNC (pay.trx_date))
BETWEEN 121
AND 150
THEN AMOUNT_DUE_ORIGINAL
END
)
) day_121_150,
SUM
((CASE
WHEN (TO_DATE (:p_as_of_date) - TRUNC (pay.trx_date))
BETWEEN 151
AND 180
THEN AMOUNT_DUE_ORIGINAL
END
)
) day_151_180,
SUM
((CASE
WHEN (TO_DATE (:p_as_of_date) - TRUNC (pay.trx_date)) >=
181
THEN AMOUNT_DUE_ORIGINAL
END
)
) day_181,
COUNT (*) no_oftrx, ''
FROM apps.ar_payment_schedules_all pay
WHERE pay.CLASS IN ('INV','DM','CM','PMT')
AND pay.org_id = :p_org_id
AND pay.gl_date <= TO_DATE (:p_as_of_date)
AND pay.gl_date_closed > TO_DATE (:p_as_of_date)
AND EXISTS (
SELECT 'x'
FROM apps.gl_code_combinations gl,
apps.ra_cust_trx_line_gl_dist_all distgl
WHERE ( TRIM ((:p_acct_cd)) = '0'
OR gl.segment4 IN (:p_acct_cd)
) --gl.segment4 in (:P_ACCT_CD)
AND gl.code_combination_id = distgl.code_combination_id
AND distgl.account_class = 'REC'
AND distgl.customer_trx_id = pay.customer_trx_id)
GROUP BY pay.org_id, pay.customer_id
-- ,pay.AMOUNT_DUE_ORIGINAL, pay.AMOUNT_DUE_REMAINING ,pay.trx_number, pay.trx_date
) invag,
(SELECT pay.org_id, pay.customer_id,
SUM (receiptl.amount_applied) paymt
FROM apps.ar_payment_schedules_all pay,
ar_receivable_applications_all receiptl
WHERE pay.CLASS = 'PMT'
AND receiptl.status IN ('ACC', 'UNAPP')
AND pay.org_id = :p_org_id
AND pay.payment_schedule_id = receiptl.payment_schedule_id
AND pay.gl_date <= TO_DATE (:p_as_of_date)
AND pay.gl_date_closed > TO_DATE (:p_as_of_date)
GROUP BY pay.org_id, pay.customer_id) payment,
apps.ra_customers cust
WHERE cust.customer_id = invag.customer_id(+)
AND cust.customer_id = payment.customer_id(+)
AND (invag.org_id IS NOT NULL OR payment.org_id IS NOT NULL)