Query for Customer Receipt Details

SELECT acra.cash_receipt_id,
DECODE (acra.TYPE,
'cash', 'cash receipt receipt',
'misc', 'miscellaneous',
acra.TYPE
) receipt_type,
acra.currency_code, acra.doc_sequence_value receipt_number,
acra.receipt_number reference_number,
TRUNC (acra.receipt_date) receipt_date, hp.party_name received_from,
acra.misc_payment_source, hca.account_number customer_no,
NVL (acra.amount, 0) entered_amount,
NVL (acra.amount, 0) * NVL (acra.exchange_rate, 1) functional_amount,
arm.NAME payment_method, abaa.bank_account_num bank_acc_num,
abb.bank_name, abb.bank_branch_name, acra.comments description
FROM ar_cash_receipts_all acra,
ar_receipt_methods arm,
ap_bank_accounts_all abaa,
ap_bank_branches abb,
hz_cust_accounts hca,
hz_parties hp
WHERE acra.pay_from_customer = hca.cust_account_id(+)
AND acra.org_id = abaa.org_id(+)
AND hca.party_id = hp.party_id(+)
AND acra.receipt_method_id = arm.receipt_method_id
AND acra.remittance_bank_account_id = abaa.bank_account_id
AND abaa.bank_branch_id = abb.bank_branch_id
ORDER BY TRUNC (acra.receipt_date), acra.doc_sequence_value;

Read More