Overview: This query is use to get AP modules payment details with following columns such as invoice id, invoice number, voucher number, payment method, payment reference, payment date, amount, payment status, bank name in Oracle R12 Application.
SELECT aia.invoice_id, aia.invoice_num, aia.DOC_SEQUENCE_VALUE Voucher_num, aca.payment_method_code, aca.check_number payment_reference, aca.check_date payment_date, aca.amount, aca.status_lookup_code payment_status, cbv.bank_name, aip.attribute1 UTR_NO FROM ap_invoices_all aia, ap_invoice_payments_all aip, ap_checks_all aca, ce_bank_acct_uses_all cbau, ce_bank_accounts cba, ce_banks_v cbv WHERE aia.invoice_id = aip.invoice_id AND aca.check_id = aip.check_id AND cbau.bank_acct_use_id = aca.ce_bank_acct_use_id AND cbau.bank_account_id = cba.bank_account_id AND cba.bank_id = cbv.bank_party_id