Thursday, October 20, 2022
Query to get basic AP Invoice and Payment information from Oracle Apps R12
In this post, we have given the query which gives you the joins between AP invoice tables and relevant Payment tables.
Tested instance: R12.2.4
Tables Involved:
AP_INVOICE_PAYMENTS_ALL
AP_CHECKS_ALL
AP_LOOKUP_CODES
IBY_PAYMENT_METHODS_VL
AP_INVOICES_ALL
AP_SUPPLIERS
Query:
WITH ap_invoice_payments AS ( SELECT aip.amount amount, DECODE(aip.invoice_payment_type, 'PREPAY', alc1.displayed_field, iby.payment_method_name ) payment_method, ac.check_number document_number, ac.check_date check_date, decode(ac.status_lookup_code, 'VOIDED', 'Y', 'N') void, aip.invoice_id, aip.creation_date FROM ap_invoice_payments_all aip, ap_checks_all ac, ap_lookup_codes alc1, iby_payment_methods_vl iby WHERE aip.check_id = ac.check_id AND alc1.lookup_type (+) = 'NLS TRANSLATION' AND alc1.lookup_code (+) = 'PREPAY' AND iby.payment_method_code (+) = ac.payment_method_code ) SELECT aia.invoice_id, aia.org_id org_id, aia.invoice_num , aia.invoice_amount, (SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'INVOICE TYPE' AND lookup_code = aia.invoice_type_lookup_code) invoice_type, TRUNC(aia.creation_date) inv_creation_date, supp.vendor_name, (SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'VENDOR TYPE' AND lookup_code = supp.vendor_type_lookup_code) vendor_type, aia.cancelled_date, aia.doc_sequence_value, (SELECT aprhis.approver_name FROM apps.ap_inv_aprvl_hist_all aprhis WHERE aprhis.approval_history_id = (SELECT MIN(hist.approval_history_id) FROM apps.ap_inv_aprvl_hist_all hist WHERE hist.invoice_id = aprhis.invoice_id AND hist.response <> 'STOPPED') and approver_name is not null and aprhis.invoice_id = aia.invoice_id) first_approver, aia.payment_currency_code CURRENCY, aia.amount_paid amount_paid, pay.creation_date paid_date, TO_CHAR(aia.invoice_received_date, 'DD-MON-YYYY') invoice_received_date, pay.amount payment_amount, pay.check_date check_date, pay.payment_method, pay.document_number, FROM ap_invoices_all aia, ap_invoice_payments pay, ap_suppliers supp WHERE aia.cancelled_by IS NULL AND aia.invoice_id = pay.invoice_id(+) AND supp.vendor_id = aia.vendor_id AND NVL(pay.void, 'N') = 'N' AND NVL(TRUNC(supp.end_date_active), TRUNC(SYSDATE + 1)) >= TRUNC(SYSDATE) AND supp.employee_id is null AND aia.wfapproval_status in ('MANUALLY APPROVED', 'NOT REQUIRED', 'WFAPPROVED' ) ORDER BY aia.invoice_num
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
Disclaimer
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.
0 Responses to “Query to get basic AP Invoice and Payment information from Oracle Apps R12”
Post a Comment