Select sum (invoice_amount) , sum(pending_amt) pay, sum (on_account) advanc ,
(sum(pending_amt) - sum (on_account)) bal
From (
SELECT distinct ac.customer_name party_name, ac.customer_number,
TO_CHAR (rct.trx_date) gl_date,
TO_CHAR (ars.due_date) dutrm_date,
TO_NUMBER (rct.trx_date - TO_DATE (NVL (:p_to_date, SYSDATE))
) due_date,
(ars.amount_due_original) invoice_amount,
(ars.amount_due_remaining * NVL (ars.exchange_rate, 1)
) pending_amt,
TO_NUMBER ('') on_account,
rct.trx_number, rtl.TYPE
FROM ra_customer_trx_all rct,
ra_cust_trx_types_all rtl,
ar_payment_schedules_all ars,
ar_customers ac
WHERE rct.cust_trx_type_id = rtl.cust_trx_type_id
AND ars.customer_trx_id = rct.customer_trx_id
AND rct.customer_trx_id = ars.customer_trx_id
AND ars.amount_due_remaining <> 0
AND rct.bill_to_customer_id = ac.customer_id
AND ac.customer_number = NVL (:cust_id, ac.customer_number)
AND NVL(ars.gl_date,'31-DEC-2999') <=:p_end_date
UNION ALL
SELECT ac.customer_name party_name, ac.customer_number,
TO_CHAR (acr.receipt_date), TO_CHAR ('') dutrm_date,
TO_NUMBER ('') due_date, TO_NUMBER ('') invoice_amount,TO_NUMBER ('') pending_amt,
( NVL (acr.exchange_rate, 1)
* ( acr.amount
- nvl((SELECT SUM (NVL (amount_applied, 0))
FROM ar_receivable_applications_all ara
WHERE ara.cash_receipt_id = acr.cash_receipt_id and org_id=81
AND ara.gl_date <= :p_end_date
AND display = 'Y'
AND applied_customer_trx_id IS NOT NULL),0)
)
) "ON_ACCOUNT",
acr.receipt_number, '' TYPE
FROM ar_cash_receipts_all acr,
ar_customers ac,
hz_cust_accounts_all hca
WHERE acr.status = 'UNAPP'
AND acr.org_id=81
AND hca.cust_account_id = acr.pay_from_customer
AND hca.account_number = ac.customer_number
AND NVL(acr.receipt_date,'31-DEC-2999') <=:p_end_date
--AND ac.customer_number = NVL (:cust_id, ac.customer_number)
UNION ALL
SELECT ac.customer_name party_name, ac.customer_number,
TO_CHAR (acr.receipt_date),
TO_CHAR (ars.trx_number) invoice_num,
TO_NUMBER ('') dutrm_date, TO_NUMBER ('') due_date,
acr.amount invoice_amount,
(SUM (ara.amount_applied * NVL (acr.exchange_rate, 1))
) "ON_ACCOUNT",
acr.receipt_number, TO_CHAR ('') TYPE
FROM ar_cash_receipts_all acr,
ar_customers ac,
hz_cust_accounts_all hca,
ar_receivable_applications_all ara,
ar_payment_schedules_all ars
WHERE acr.status = 'APP'
AND ara.status = 'ACC'
AND ara.display = 'Y'
AND hca.cust_account_id = acr.pay_from_customer
AND ara.cash_receipt_id = acr.cash_receipt_id
AND hca.account_number = ac.customer_number
AND ac.customer_number = NVL (:cust_id, ac.customer_number)
AND ara.applied_payment_schedule_id = ars.payment_schedule_id
AND NVL(ara.gl_date,'31-DEC-2999') <=:p_end_date
--AND
GROUP BY ac.customer_name,
acr.receipt_date,
acr.receipt_number,
acr.amount,
trx_number,
ars.amount_due_remaining,
ars.amount_due_original,
ac.customer_number
) inv
GROUP BY inv.party_name, inv.customer_number
ORDER BY 1