Thursday, September 24, 2009

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;

No comments:

Post a Comment