AR Unapplied Receipts 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 Unapplied Receipts SQL

Post by admin »

SELECT e.name cust_collector,
b.customer_number,
SUBSTR (b.customer_name, 1, 35) customer_name,
a.receipt_number,
a.receipt_date,
a.amount receipt_amount,
a.amount - SUM (NVL (d.amount_applied, 0)) unapplied_amount
FROM ar_cash_receipts_all a,
ra_hcustomers b,
hz_customer_profiles c,
ar_receivable_applications_all d,
ar_collectors e
WHERE d.cash_receipt_id(+) = a.cash_receipt_id
AND d.status(+) = 'APP'
AND d.display(+) = 'Y'
AND a.pay_from_customer = b.customer_id
AND a.org_id = 81
AND b.customer_id = c.cust_account_id
AND c.site_use_id IS NULL
AND c.collector_id = e.collector_id(+)
AND a.status = 'UNAPP'
GROUP BY e.name,
b.customer_number,
b.customer_name,
a.cash_receipt_id,
a.receipt_number,
a.receipt_date,
a.amount,
a.TYPE,
a.status
HAVING a.amount - SUM (NVL (d.amount_applied, 0)) > 0
ORDER BY e.name, b.customer_number, a.receipt_number
Post Reply

Who is online

Users browsing this forum: Ahrefs [Bot] and 2 guests