An Oracle Fusion Payables SQL query needs a defined reporting level. An invoice, a payment installment and a payment transaction are different records. Decide which one each output row represents before joining tables or adding amounts.
Start with one synthetic invoice in a test environment. SELECT invoice_id, invoice_num, org_id, invoice_currency_code, invoice_amount, amount_paid FROM ap_invoices_all WHERE invoice_num = 'DEMO-INV-1042' AND org_id = 300100000000001 ORDER BY invoice_id FETCH FIRST 20 ROWS ONLY retrieves header details. Replace the invoice number and business-unit identifier with approved values. An invoice number alone can match different suppliers.
Oracle documents AP_INVOICES_ALL as the invoice header table. Its INVOICE_ID identifies the invoice. Keep that identifier in diagnostic exports so subsequent checks use the same record. A remaining header balance does not by itself establish that an invoice is eligible for payment.
For unpaid installments, query SELECT i.invoice_num, s.payment_num, s.due_date, s.amount_remaining FROM ap_invoices_all i JOIN ap_payment_schedules_all s ON s.invoice_id = i.invoice_id WHERE i.invoice_id = 300100000000042 AND i.org_id = 300100000000001 AND s.amount_remaining <> 0 ORDER BY s.due_date, s.payment_num FETCH FIRST 50 ROWS ONLY. Replace the synthetic invoice ID with the resolved header ID. Each row represents an installment. Oracle's payment schedule reference explains the due date, remaining amount and payment status fields.
Payment history needs AP_INVOICE_PAYMENTS_ALL. Join it to the invoice through INVOICE_ID, then to AP_CHECKS_ALL through CHECK_ID when payment-document details are required. Preserve reversal rows. Voiding a payment can create an offsetting transaction, so discarding negative amounts changes the answer.
Invoice holds require a separate check against AP_HOLDS_ALL. Scope by the resolved invoice ID and examine unreleased holds using RELEASE_LOOKUP_CODE IS NULL. Use an existence condition when the question is whether a hold exists. Joining every hold and every payment to one invoice can multiply rows and inflate totals. The invoice-hold investigation guide covers the business evidence needed before any release.
Reconcile installment balances independently from payment history. Keep invoice currency with every amount, and compare one invoice with the Payables application before widening the date range. A query's visibility depends on the reporting privileges and security design used in your environment.
Save the reviewed SQL in unpaid.sql and run fusion-toolkit --profile ap-test sql-query -q unpaid.sql -o unpaid.csv. The native launcher uses the selected encrypted profile. Inspect the output delimiter before opening the file in another tool. SQL Studio provides an interactive editor for the same investigation.
The examples use synthetic identifiers and were reviewed on 8 November 2026. Validate table access and the selected business unit in your test environment before scheduling an export.
You can also use an LLM with the Fusion Toolkit AI Administrator skill and ask, "Which installments remain unpaid for invoice DEMO-INV-1042 in our test business unit?" It can retrieve the relevant documented Oracle data without requiring you to write the query. Start with one invoice and check the returned scope.