AR Collections Aging SQL

This forum is to discuss different features/issues of Oracle Financials modules ( GL - General Ledger, AP - Accounts Payable, AR - Accounts Receivable, FA - Fixed Assets & CM - Cash Management ).
Post Reply
admin
Posts: 2062
Joined: Fri Mar 31, 2006 12:59 am
Location: Pakistan
Contact:

AR Collections Aging SQL

Post by admin »

Your auditors might ask collections aging data/report so this can be used with changes as per your requirements. Few fields are commented but if required can be enabled.

-- collections aging

DECLARE
v_user_id NUMBER DEFAULT 9999; -- update as per your data
v_resp_id NUMBER DEFAULT 99999; -- update as per your data
v_org_id NUMBER DEFAULT 999; -- update as per your data
BEGIN
FND_GLOBAL.apps_initialize (v_user_id, v_resp_id, 222);
MO_GLOBAL.init ('AR');
END;

SELECT -- ar_payment_schedules.customer_id customer_id,
-- decode (ar_receivable_applications.amount_applied_from, NULL, NULL, '*') cc_flag,
PARTY.PARTY_NAME
customer,
CUST.ACCOUNT_NUMBER
customer_number,
ra_cust_trx_types.name
cust_trx_type,
-- ar_payment_schedules.customer_trx_id customer_trx_id,
ar_payment_schedules.trx_number
invoice_number,
ra_customer_trx.TRX_DATE,
(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,
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_cash_receipts.RECEIPT_DATE,
ar_payment_schedules.payment_schedule_id
sched_id,
CASE
WHEN ( ar_receivable_applications.apply_date
- ar_payment_schedules.due_date BETWEEN 1
AND 30)
THEN
ar_receivable_applications.amount_applied
ELSE
0
END
"1 to 30",
CASE
WHEN ( ar_receivable_applications.apply_date
- ar_payment_schedules.due_date BETWEEN 31
AND 60)
THEN
ar_receivable_applications.amount_applied
ELSE
0
END
"31 to 60",
CASE
WHEN ( ar_receivable_applications.apply_date
- ar_payment_schedules.due_date BETWEEN 61
AND 90)
THEN
ar_receivable_applications.amount_applied
ELSE
0
END
"61 to 90",
CASE
WHEN ( ar_receivable_applications.apply_date
- ar_payment_schedules.due_date BETWEEN 91
AND 180)
THEN
ar_receivable_applications.amount_applied
ELSE
0
END
"91 to 180",
CASE
WHEN ( ar_receivable_applications.apply_date
- ar_payment_schedules.due_date BETWEEN 181
AND 360)
THEN
ar_receivable_applications.amount_applied
ELSE
0
END
"181 to 360",
CASE
WHEN ( ar_receivable_applications.apply_date
- ar_payment_schedules.due_date BETWEEN 361
AND 1000000)
THEN
ar_receivable_applications.amount_applied
ELSE
0
END
"361 to ..."
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
AND ra_customer_trx.TRX_DATE BETWEEN '01-JAN-2017' AND '31-DEC-2017'
-- and ar_payment_schedules.trx_number = '440016420'
-- and ar_receivable_applications.apply_date >= '01-JAN-2014' --:p_in_trx_date_low
-- and ar_receivable_applications.apply_date <= '31-DEC-2017' --:p_in_trx_date_high
-- and CUST.ACCOUNT_NUMBER = '9495 - FC'
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;
Post Reply

Who is online

Users browsing this forum: Google Adsense [Bot] and 1 guest