SELECT
*
FROM
(
SELECT
business_unit,
cash_receipt_id,
customer_name,
customer_number,
party_site_number,
receipt_number,
receipt_date,
currency_code,
status,
application_type,
SUM(onacc_amt) onacc_amt,
SUM(unapp_amt) unapp_amt,
nvl((SUM(onacc_amt) + SUM(unapp_amt)), 0) zero_check
FROM
(
SELECT
hou.name business_unit,
acra.cash_receipt_id,
rc.customer_name,
rc.customer_number,
rc.party_site_number,
acra.receipt_number,
acra.receipt_date,
acra.currency_code,
-- ( araa.amount_applied ) amount,
CASE
WHEN araa.status = 'ACC' THEN
araa.amount_applied
ELSE
0
END onacc_amt,
CASE
WHEN araa.status = 'UNAPP' THEN
araa.amount_applied
ELSE
0
END unapp_amt,
araa.status,
araa.application_type
-- , araa.gl_date
FROM
apps.ar_cash_receipts_all acra,
apps.ar_receivable_applications_all araa,
apps.hr_operating_units hou,
zacuser.bu_ra_customers_v rc
WHERE
acra.cash_receipt_id = araa.cash_receipt_id
AND araa.status IN (
'ACC',
'UNAPP'
)
AND hou.organization_id = acra.org_id
AND acra.pay_from_customer = rc.customer_id
AND rc.bill_to_site_use_id = acra.customer_site_use_id
AND acra.org_id IN (
1022,
1099,
1100
)
)
GROUP BY
business_unit,
cash_receipt_id,
customer_name,
customer_number,
party_site_number,
receipt_number,
receipt_date,
currency_code,
status,
application_type
)
WHERE
-- (ONACC_AMT+UNAPP_AMT) <> 0
zero_check <> 0
ORDER BY
business_unit,
customer_name,
receipt_number;