SELECT acr.receipt_number RECEIPT_NUMBER,
receipt_date RECEIPT_DATE,
hca.account_number CUSTOMER_NUMBER,
hp.party_name PARTY_NAME,
acr.currency_code CURRENCY_CODE,
acr.amount RECEIPT_AMT,
decode(acr.status, 'UNAPP', ABS(aps.amount_due_remaining), 0) UNAPP_AMT,
decode(acr.status, 'UNID', ABS(aps.amount_due_remaining), 0) UNID_AMT,
acr.work_item_assignment_date WORK_ITEM_ASSIGNMENT_DATE,
fu.user_name USER_NAME,
lu.meaning WORK_ITEM_STATUS
FROM ar_cash_receipts_all acr,
ar_payment_schedules_all aps,
fnd_user fu,
hz_cust_accounts_all hca,
hz_parties hp,
ar_lookups lu
WHERE 'Y' = :p_detail
AND fu.user_id = acr.cash_appln_owner_id
AND acr.cash_receipt_id = aps.cash_receipt_id
AND hca.party_id = hp.party_id (+)
AND acr.pay_from_customer = hca.cust_account_id (+)
AND acr.work_item_status_code = lu.lookup_code
AND lu.lookup_type = 'WORK_ITEM_STATUS'
&gc_org_id
&gc_cash_appln_owner
&gc_recpt_date
&gc_cust
&gc_work_item_status
&gc_assgn_date
ORDER BY RECEIPT_NUMBER |
