ERPstuff - Oracle EBS Community

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