← All articles
Oracle Cloud SQL Studio, Technical Tutorials

Oracle Fusion Payables SQL queries for invoices, payments and holds

Query Oracle Fusion Payables invoices, unpaid installments, payments and holds with focused SQL examples. Check joins and export results with Fusion Toolkit.

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. The header query below retrieves its identifiers and amounts. Replace the invoice number and business-unit identifier with approved values. An invoice number alone can match different suppliers.

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;

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, use the resolved invoice ID from the header result. Replace the synthetic invoice ID and business-unit identifier below. Each row represents an installment. Oracle's payment schedule reference explains the due date, remaining amount and payment status fields.

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;

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.

SELECT p.invoice_payment_id,
       p.invoice_id,
       p.payment_num,
       p.accounting_date,
       p.amount,
       i.invoice_currency_code,
       c.check_id,
       c.check_number,
       c.check_date,
       c.currency_code AS payment_currency_code,
       c.status_lookup_code
FROM ap_invoice_payments_all p
JOIN ap_invoices_all i
  ON i.invoice_id = p.invoice_id
LEFT JOIN ap_checks_all c
  ON c.check_id = p.check_id
WHERE i.invoice_id = 300100000000042
  AND i.org_id = 300100000000001
ORDER BY p.accounting_date, p.invoice_payment_id;

Keep payment transactions separate from invoice and installment balances. These queries express the documented Payables checks above. They are separate from the CLHAPI approval-history adaptation below.

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.

SELECT h.hold_id,
       h.invoice_id,
       h.line_location_id,
       h.hold_lookup_code,
       h.hold_date,
       h.hold_reason
FROM ap_holds_all h
JOIN ap_invoices_all i
  ON i.invoice_id = h.invoice_id
WHERE i.invoice_id = 300100000000042
  AND i.org_id = 300100000000001
  AND h.release_lookup_code IS NULL
ORDER BY h.hold_date, h.hold_id;

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.

This CLHAPI SQL adaptation retrieves approval history for the same synthetic invoice and business unit. It replaces the original workflow-name filters with invoice scope. Oracle's approval-history reference defines the response and action date. Approval history does not establish payment eligibility.

SELECT h.approval_history_id,
       h.invoice_id,
       h.iteration,
       h.approval_step,
       h.rule_name,
       h.history_type,
       h.approver_id,
       h.response,
       h.action_date
FROM ap_inv_aprvl_hist_all h
WHERE h.invoice_id = 300100000000042
  AND h.org_id = 300100000000001
ORDER BY h.iteration, h.action_date, h.approval_history_id;

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.

Continue reading