Oracle EBS AP Payments Query

I need a clean, well-documented SQL statement that pulls detailed payment information from Oracle E-Business Suite Financials—specifically the Accounts Payable module’s Payments side. The objective is to see every payment issued: status, method, check or electronic reference, bank account, invoice linkage, supplier, date, currency, and amount. I am on an Oracle E-Business Suite 12.x environment and will run the code through SQL*Plus, so a standalone SQL script is all that’s required; no BI Publisher, OTBI, or custom objects. Please base the logic on the standard AP tables (AP_INVOICE_PAYMENTS_ALL, AP_CHECKS_ALL, AP_PAYMENT_SCHEDULES_ALL, AP_SUPPLIERS, and any essential GL or ZX references) and include helpful comments explaining joins, date filters, and parameters I can adjust easily. Performance matters: the query should return one line per payment, be free of Cartesian joins, and complete in under 30 seconds when pulling a year’s worth of data from a five-million-row AP_INVOICE_PAYMENTS_ALL table. If useful, feel free to add sensible indexing hints or analytic functions. Deliverable • A single .sql file containing the query, fully commented and ready to execute. Acceptance criteria • Runs without errors on EBS 12.2 • Results match on-screen totals in AP Payment history • Meets the stated performance goal I may ask for additional work on invoices or supplier data later, but for now the focus is this Payments query. If it sounds straightforward, let’s get started.

Project ID: 40401255