Oracle Fusion Finance SQL Guide: 18 Practical Queries for GL, AP, AR, and SLA Reporting

Oracle Fusion Finance reporting crosses several data models: General Ledger (GL), Payables (AP), Receivables (AR), Subledger Accounting (SLA), Cash Management, Procurement, and Trading Community Architecture (TCA). The difficult part is rarely writing a SELECT. It is choosing the correct transaction grain, join path, currency, ledger, business unit, and accounting layer.

This guide contains 18 practical SQL patterns built around current Oracle Fusion table and column names. The examples are deliberately explicit about installment-level AP/AR balances, supplier joins through POZ_SUPPLIERS, and the correct AP → SLA → GL drillback path.

Core Oracle Fusion Finance Tables

Finance Reporting Architecture: Ledger, Currency, and Security Context

A ledger defines a chart of accounts, accounting calendar, currency, and accounting configuration. General Ledger Data Access Sets are an important application security mechanism for ledger and primary-balancing-segment access, but that does not mean an arbitrary BI Publisher physical SQL statement against a base table is automatically filtered by the report user's Data Access Set.

For the SQL patterns below, the safe approach is explicit scoping: require :p_ledger_id in GL queries, use the appropriate ORG_ID / business-unit scope in AP and AR queries, and treat OTBI/application security separately from raw physical SQL security. See the Oracle Fusion SQL Security guide for the detailed distinction.

General Ledger SQL Queries

Query 1 — Posted Journal Lines for a Period

Returns posted journal lines for one ledger and period. Accounted amounts are in ledger currency; entered amounts are in the journal transaction currency.

SELECT gjh.je_header_id,
       gjh.name                    AS journal_name,
       gjh.je_source,
       gjh.je_category,
       gjh.period_name,
       gjh.currency_code,
       gjh.posted_date,
       gjl.je_line_num,
       gjl.effective_date,
       gcc.concatenated_segments   AS account_combination,
       gjl.entered_dr,
       gjl.entered_cr,
       gjl.accounted_dr,
       gjl.accounted_cr,
       gjl.description
FROM   gl_je_headers gjh
JOIN   gl_je_lines gjl
       ON gjl.je_header_id = gjh.je_header_id
JOIN   gl_code_combinations gcc
       ON gcc.code_combination_id = gjl.code_combination_id
WHERE  gjh.status      = 'P'
AND    gjh.ledger_id   = :p_ledger_id
AND    gjh.period_name = :p_period_name
ORDER BY gjh.posted_date,
         gjh.je_header_id,
         gjl.je_line_num

Query 2 — GL Account Balances for a Period

Uses GL_BALANCES instead of re-aggregating journal lines. Currency is explicit and TEMPLATE_ID IS NULL restricts the result to detail-account balance rows.

SELECT gb.period_name,
       gb.currency_code,
       gcc.concatenated_segments AS account,
       gb.begin_balance_dr,
       gb.begin_balance_cr,
       gb.period_net_dr,
       gb.period_net_cr,
       ( NVL(gb.begin_balance_dr, 0)
       - NVL(gb.begin_balance_cr, 0)
       + NVL(gb.period_net_dr, 0)
       - NVL(gb.period_net_cr, 0) ) AS ending_balance
FROM   gl_balances gb
JOIN   gl_code_combinations gcc
       ON gcc.code_combination_id = gb.code_combination_id
WHERE  gb.actual_flag    = 'A'
AND    gb.ledger_id      = :p_ledger_id
AND    gb.period_name    = :p_period_name
AND    gb.currency_code  = :p_currency_code
AND    gb.template_id IS NULL
ORDER BY gcc.concatenated_segments

Query 3 — Trial Balance at the End of a Selected Period

A trial-balance ending value should use the selected period's balance row. Do not sum beginning balances across every period in the year; that double-counts carried balances.

SELECT gcc.concatenated_segments AS account,
       gb.currency_code,
       NVL(gb.begin_balance_dr, 0)
         - NVL(gb.begin_balance_cr, 0) AS opening_balance,
       NVL(gb.period_net_dr, 0)
         - NVL(gb.period_net_cr, 0) AS period_activity,
       NVL(gb.begin_balance_dr, 0)
         - NVL(gb.begin_balance_cr, 0)
         + NVL(gb.period_net_dr, 0)
         - NVL(gb.period_net_cr, 0) AS ending_balance
FROM   gl_balances gb
JOIN   gl_code_combinations gcc
       ON gcc.code_combination_id = gb.code_combination_id
WHERE  gb.ledger_id      = :p_ledger_id
AND    gb.actual_flag    = 'A'
AND    gb.period_name    = :p_period_name
AND    gb.currency_code  = :p_currency_code
AND    gb.template_id IS NULL
ORDER BY gcc.concatenated_segments

Query 4 — Posted Journals by Source and Category

Counts distinct journal headers separately from journal lines, while summing accounted debit and credit values.

SELECT gjh.je_source,
       gjh.je_category,
       COUNT(DISTINCT gjh.je_header_id) AS journal_count,
       COUNT(*)                         AS journal_line_count,
       SUM(NVL(gjl.accounted_dr, 0))    AS total_dr,
       SUM(NVL(gjl.accounted_cr, 0))    AS total_cr
FROM   gl_je_headers gjh
JOIN   gl_je_lines gjl
       ON gjl.je_header_id = gjh.je_header_id
WHERE  gjh.status      = 'P'
AND    gjh.ledger_id   = :p_ledger_id
AND    gjh.period_name = :p_period_name
GROUP BY gjh.je_source,
         gjh.je_category
ORDER BY total_dr DESC

Query 5 — Journal Headers Not in Posted Status

Oracle uses P for posted and U for unposted, while several other status values represent validation or posting errors. Therefore status <> 'P' is a broad operational exception list, not simply “draft journals.”

SELECT gjh.je_header_id,
       gjh.name,
       gjh.je_source,
       gjh.je_category,
       gjh.period_name,
       gjh.creation_date,
       gjh.status
FROM   gl_je_headers gjh
WHERE  gjh.ledger_id = :p_ledger_id
AND    gjh.status <> 'P'
ORDER BY gjh.creation_date DESC

Query 6 — General Ledger Period Statuses

SELECT gps.period_name,
       gps.start_date,
       gps.end_date,
       gps.adjustment_period_flag,
       gps.closing_status,
       DECODE(gps.closing_status,
              'O', 'Open',
              'F', 'Future Enterable',
              'C', 'Closed',
              'P', 'Permanently Closed',
              'N', 'Never Opened',
              gps.closing_status) AS closing_status_name
FROM   gl_period_statuses gps
WHERE  gps.ledger_id      = :p_ledger_id
AND    gps.application_id = 101
ORDER BY gps.start_date DESC

Accounts Payable SQL Queries

Payables is where reporting grain matters most. AP_INVOICES_ALL is the invoice header, but due dates and current outstanding amounts are installment-level values in AP_PAYMENT_SCHEDULES_ALL. Supplier identity is POZ_SUPPLIERS plus HZ_PARTIES.

Query 7 — Open Supplier Invoice Installments

SELECT ai.invoice_id,
       ai.invoice_num,
       ai.invoice_date,
       ai.invoice_currency_code,
       ps.segment1                 AS supplier_number,
       hp.party_name               AS supplier_name,
       aps.payment_num,
       aps.due_date,
       aps.gross_amount,
       aps.amount_remaining,
       aps.payment_status_flag
FROM   ap_invoices_all ai
JOIN   ap_payment_schedules_all aps
       ON aps.invoice_id = ai.invoice_id
JOIN   poz_suppliers ps
       ON ps.vendor_id = ai.vendor_id
JOIN   hz_parties hp
       ON hp.party_id = ps.party_id
WHERE  ai.cancelled_date IS NULL
AND    aps.payment_status_flag IN ('N', 'P')
AND    ai.org_id = :p_org_id
ORDER BY aps.due_date,
         hp.party_name,
         ai.invoice_num,
         aps.payment_num

Query 8 — Current AP Aging by Supplier

This is a current open-installment aging query. Historical “as of” aging requires payment/application history; simply changing SYSDATE to an old date does not reconstruct a historical amount remaining.

SELECT ps.vendor_id,
       ps.segment1 AS supplier_number,
       hp.party_name AS supplier_name,
       ai.invoice_currency_code,
       SUM(CASE WHEN aps.due_date >= TRUNC(SYSDATE)
                THEN NVL(aps.amount_remaining, 0) ELSE 0 END) AS current_bucket,
       SUM(CASE WHEN TRUNC(SYSDATE) - aps.due_date BETWEEN 1 AND 30
                THEN NVL(aps.amount_remaining, 0) ELSE 0 END) AS bucket_1_30,
       SUM(CASE WHEN TRUNC(SYSDATE) - aps.due_date BETWEEN 31 AND 60
                THEN NVL(aps.amount_remaining, 0) ELSE 0 END) AS bucket_31_60,
       SUM(CASE WHEN TRUNC(SYSDATE) - aps.due_date BETWEEN 61 AND 90
                THEN NVL(aps.amount_remaining, 0) ELSE 0 END) AS bucket_61_90,
       SUM(CASE WHEN TRUNC(SYSDATE) - aps.due_date > 90
                THEN NVL(aps.amount_remaining, 0) ELSE 0 END) AS over_90
FROM   ap_invoices_all ai
JOIN   ap_payment_schedules_all aps
       ON aps.invoice_id = ai.invoice_id
JOIN   poz_suppliers ps
       ON ps.vendor_id = ai.vendor_id
JOIN   hz_parties hp
       ON hp.party_id = ps.party_id
WHERE  ai.cancelled_date IS NULL
AND    aps.payment_status_flag IN ('N', 'P')
AND    ai.org_id = :p_org_id
GROUP BY ps.vendor_id,
         ps.segment1,
         hp.party_name,
         ai.invoice_currency_code
ORDER BY over_90 DESC

Query 9 — Supplier Invoice Value, Rolling 12 Months

This is an operational invoice-value analysis, not a GL expense report. Invoice header totals can include tax, freight, credit memos, prepayments, and other items that do not equal expense postings.

SELECT ps.vendor_id,
       ps.segment1 AS supplier_number,
       hp.party_name AS supplier_name,
       ai.invoice_currency_code,
       COUNT(ai.invoice_id)         AS invoice_count,
       SUM(ai.invoice_amount)       AS total_invoiced,
       SUM(NVL(ai.amount_paid, 0))  AS total_paid
FROM   ap_invoices_all ai
JOIN   poz_suppliers ps
       ON ps.vendor_id = ai.vendor_id
JOIN   hz_parties hp
       ON hp.party_id = ps.party_id
WHERE  ai.invoice_date >= ADD_MONTHS(TRUNC(SYSDATE), -12)
AND    ai.cancelled_date IS NULL
AND    ai.org_id = :p_org_id
GROUP BY ps.vendor_id,
         ps.segment1,
         hp.party_name,
         ai.invoice_currency_code
ORDER BY total_invoiced DESC

Query 10 — PO-Matched AP Invoices

Uses invoice-line PO references. DISTINCT is intentional because one invoice can contain multiple lines matched to the same purchase order.

SELECT DISTINCT
       ai.invoice_id,
       ai.invoice_num,
       ai.invoice_date,
       ai.invoice_amount,
       ph.po_header_id,
       ph.segment1 AS po_number,
       ps.segment1 AS supplier_number,
       hp.party_name AS supplier_name
FROM   ap_invoices_all ai
JOIN   ap_invoice_lines_all ail
       ON ail.invoice_id = ai.invoice_id
JOIN   po_headers_all ph
       ON ph.po_header_id = ail.po_header_id
JOIN   poz_suppliers ps
       ON ps.vendor_id = ai.vendor_id
JOIN   hz_parties hp
       ON hp.party_id = ps.party_id
WHERE  ail.po_header_id IS NOT NULL
AND    ai.cancelled_date IS NULL
AND    ai.org_id = :p_org_id
ORDER BY ai.invoice_date DESC,
         ai.invoice_num

Query 11 — AP Invoice Distribution Charge Accounts

Shows the account stored on each AP distribution. This is useful for distribution analysis, but it should not be presented as the final posted accounting entry; use SLA/GL for accounting reconciliation.

SELECT ai.invoice_num,
       aid.invoice_distribution_id,
       aid.invoice_line_number,
       aid.distribution_line_number,
       aid.line_type_lookup_code,
       aid.accounting_date,
       aid.amount,
       aid.base_amount,
       gcc.concatenated_segments AS distribution_account
FROM   ap_invoices_all ai
JOIN   ap_invoice_distributions_all aid
       ON aid.invoice_id = ai.invoice_id
JOIN   gl_code_combinations gcc
       ON gcc.code_combination_id = aid.dist_code_combination_id
WHERE  ai.cancelled_date IS NULL
AND    ai.org_id = :p_org_id
ORDER BY ai.invoice_num,
         aid.invoice_line_number,
         aid.distribution_line_number

Query 12 — Supplier Payment Documents, Last Six Months

Returns payment-document history. If you need the invoices paid by each payment, join through AP_INVOICE_PAYMENTS_ALL using CHECK_ID and INVOICE_ID.

SELECT ac.check_id,
       ac.check_number,
       ac.check_date,
       ac.amount,
       ac.currency_code,
       ac.status_lookup_code,
       ac.payment_method_code,
       ps.segment1   AS supplier_number,
       hp.party_name AS supplier_name
FROM   ap_checks_all ac
JOIN   poz_suppliers ps
       ON ps.vendor_id = ac.vendor_id
JOIN   hz_parties hp
       ON hp.party_id = ps.party_id
WHERE  ac.check_date >= ADD_MONTHS(TRUNC(SYSDATE), -6)
ORDER BY ac.check_date DESC,
         ac.check_id DESC

AP_INVOICES_ALL.INVOICE_ID
        ↓
AP_INVOICE_PAYMENTS_ALL.INVOICE_ID
AP_INVOICE_PAYMENTS_ALL.CHECK_ID
        ↓
AP_CHECKS_ALL.CHECK_ID

Accounts Receivable SQL Queries

AR_PAYMENT_SCHEDULES_ALL stores invoices, debit memos, credit memos, chargebacks, receipts, and other receivable schedules. Therefore an AR balance or aging query must decide whether it wants debit items only or a net customer balance including credits and receipts.

Query 13 — Open AR Debit Items by Customer

Uses the documented CUSTOMER_ID → HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID relationship and restricts the result to open debit-item classes.

SELECT hca.cust_account_id,
       hca.account_number,
       hp.party_name,
       aps.invoice_currency_code,
       SUM(aps.amount_due_remaining) AS outstanding_balance
FROM   ar_payment_schedules_all aps
JOIN   hz_cust_accounts hca
       ON hca.cust_account_id = aps.customer_id
JOIN   hz_parties hp
       ON hp.party_id = hca.party_id
WHERE  aps.status = 'OP'
AND    aps.class IN ('INV', 'DM', 'CB')
AND    aps.org_id = :p_org_id
GROUP BY hca.cust_account_id,
         hca.account_number,
         hp.party_name,
         aps.invoice_currency_code
ORDER BY outstanding_balance DESC

Query 14 — Current AR Aging by Customer

SELECT hca.cust_account_id,
       hca.account_number,
       hp.party_name,
       aps.invoice_currency_code,
       SUM(CASE WHEN aps.due_date >= TRUNC(SYSDATE)
                THEN aps.amount_due_remaining ELSE 0 END) AS current_bucket,
       SUM(CASE WHEN TRUNC(SYSDATE) - aps.due_date BETWEEN 1 AND 30
                THEN aps.amount_due_remaining ELSE 0 END) AS bucket_1_30,
       SUM(CASE WHEN TRUNC(SYSDATE) - aps.due_date BETWEEN 31 AND 60
                THEN aps.amount_due_remaining ELSE 0 END) AS bucket_31_60,
       SUM(CASE WHEN TRUNC(SYSDATE) - aps.due_date BETWEEN 61 AND 90
                THEN aps.amount_due_remaining ELSE 0 END) AS bucket_61_90,
       SUM(CASE WHEN TRUNC(SYSDATE) - aps.due_date > 90
                THEN aps.amount_due_remaining ELSE 0 END) AS over_90
FROM   ar_payment_schedules_all aps
JOIN   hz_cust_accounts hca
       ON hca.cust_account_id = aps.customer_id
JOIN   hz_parties hp
       ON hp.party_id = hca.party_id
WHERE  aps.status = 'OP'
AND    aps.class IN ('INV', 'DM', 'CB')
AND    aps.org_id = :p_org_id
GROUP BY hca.cust_account_id,
         hca.account_number,
         hp.party_name,
         aps.invoice_currency_code
ORDER BY over_90 DESC

Like the AP example, this is current aging using today's remaining balance. A historical as-of aging requires transaction/application history rather than today's AMOUNT_DUE_REMAINING.

Query 15 — Completed Customer Transaction Headers

SELECT rcta.customer_trx_id,
       hp.party_name,
       hca.account_number,
       rcta.trx_number,
       rcta.trx_date,
       rcta.invoice_currency_code,
       rcta.complete_flag
FROM   ra_customer_trx_all rcta
JOIN   hz_cust_accounts hca
       ON hca.cust_account_id = rcta.bill_to_customer_id
JOIN   hz_parties hp
       ON hp.party_id = hca.party_id
WHERE  rcta.complete_flag = 'Y'
AND    rcta.org_id = :p_org_id
AND    rcta.trx_date >= ADD_MONTHS(TRUNC(SYSDATE), -12)
ORDER BY rcta.trx_date DESC,
         rcta.trx_number

This is transaction-header activity. If the requirement is specifically “invoices only,” add the appropriate transaction-type/class logic for your Receivables setup rather than assuming every row in RA_CUSTOMER_TRX_ALL is an invoice.

Subledger Accounting (SLA) Queries

The key point in an AP-to-SLA trace is that XLA_AE_HEADERS.ENTITY_ID is an SLA entity identifier. It is not the AP invoice ID. The source transaction is resolved through XLA_TRANSACTION_ENTITIES.

Query 16 — AP Invoice to SLA Accounting

SELECT ai.invoice_id,
       ai.invoice_num,
       xte.entity_id,
       xah.ae_header_id,
       xah.accounting_date,
       xah.gl_transfer_status_code,
       xal.ae_line_num,
       xal.accounting_class_code,
       xal.accounted_dr,
       xal.accounted_cr,
       gcc.concatenated_segments AS accounting_account
FROM   ap_invoices_all ai
JOIN   xla_transaction_entities xte
       ON xte.application_id = 200
      AND xte.entity_code = 'AP_INVOICES'
      AND xte.source_id_int_1 = ai.invoice_id
JOIN   xla_ae_headers xah
       ON xah.entity_id      = xte.entity_id
      AND xah.application_id = xte.application_id
JOIN   xla_ae_lines xal
       ON xal.ae_header_id   = xah.ae_header_id
      AND xal.application_id = xah.application_id
JOIN   gl_code_combinations gcc
       ON gcc.code_combination_id = xal.code_combination_id
WHERE  ai.invoice_id = :p_invoice_id
ORDER BY xah.ae_header_id,
         xal.ae_line_num

Query 17 — Trace an AP Invoice Through SLA into GL

Matches both GL_SL_LINK_ID and GL_SL_LINK_TABLE. GL_IMPORT_REFERENCES is populated when the journal source is configured to maintain import references; summarized transfer can also affect the drillback grain.

SELECT ai.invoice_id,
       ai.invoice_num,
       xah.ae_header_id,
       xal.ae_line_num,
       xal.accounting_class_code,
       xal.accounted_dr AS sla_accounted_dr,
       xal.accounted_cr AS sla_accounted_cr,
       gjh.je_header_id,
       gjh.name          AS journal_name,
       gjh.period_name,
       gjl.je_line_num,
       gjl.accounted_dr  AS gl_accounted_dr,
       gjl.accounted_cr  AS gl_accounted_cr,
       gcc.concatenated_segments AS gl_account
FROM   ap_invoices_all ai
JOIN   xla_transaction_entities xte
       ON xte.application_id = 200
      AND xte.entity_code = 'AP_INVOICES'
      AND xte.source_id_int_1 = ai.invoice_id
JOIN   xla_ae_headers xah
       ON xah.entity_id      = xte.entity_id
      AND xah.application_id = xte.application_id
JOIN   xla_ae_lines xal
       ON xal.ae_header_id   = xah.ae_header_id
      AND xal.application_id = xah.application_id
JOIN   gl_import_references gir
       ON gir.gl_sl_link_id    = xal.gl_sl_link_id
      AND gir.gl_sl_link_table = xal.gl_sl_link_table
JOIN   gl_je_lines gjl
       ON gjl.je_header_id = gir.je_header_id
      AND gjl.je_line_num  = gir.je_line_num
JOIN   gl_je_headers gjh
       ON gjh.je_header_id = gjl.je_header_id
JOIN   gl_code_combinations gcc
       ON gcc.code_combination_id = gjl.code_combination_id
WHERE  ai.invoice_id = :p_invoice_id
ORDER BY gjh.je_header_id,
         gjl.je_line_num,
         xah.ae_header_id,
         xal.ae_line_num

Cash Management Query

Query 18 — Active Internal Bank Account Uses

Uses columns that actually belong to the bank-account and bank-account-use objects. Account type comes from CE_BANK_ACCOUNTS.BANK_ACCOUNT_TYPE; the use table carries business-unit and product-enable flags.

SELECT cba.bank_account_id,
       cba.bank_account_name,
       cba.bank_account_num,
       cba.currency_code,
       cba.bank_account_type,
       cbau.bank_acct_use_id,
       cbau.org_id,
       cbau.legal_entity_id,
       cbau.ap_use_enable_flag,
       cbau.ar_use_enable_flag,
       cbau.xtr_use_enable_flag,
       cbau.pay_use_enable_flag,
       cba.start_date,
       cba.end_date,
       cbau.end_date AS use_end_date
FROM   ce_bank_accounts cba
JOIN   ce_bank_acct_uses_all cbau
       ON cbau.bank_account_id = cba.bank_account_id
WHERE  cba.start_date <= TRUNC(SYSDATE)
AND    (cba.end_date IS NULL OR cba.end_date >= TRUNC(SYSDATE))
AND    (cbau.end_date IS NULL OR cbau.end_date >= TRUNC(SYSDATE))
ORDER BY cba.bank_account_name,
         cbau.org_id

Common Oracle Fusion Finance SQL Mistakes

Bonus — BI Publisher Parameterized GL Journal Report

Keep the ledger mandatory. Make secondary dimensions optional with explicit :p IS NULL OR column = :p predicates. The generic account-prefix example below filters the whole concatenated account combination; if you need the natural-account segment specifically, filter the correct SEGMENTn for your chart of accounts.

SELECT gjh.name AS journal_name,
       gjh.je_source,
       gjh.je_category,
       gjh.period_name,
       gjh.currency_code,
       gjl.je_line_num,
       gcc.concatenated_segments AS account,
       gjl.accounted_dr,
       gjl.accounted_cr,
       gjl.description
FROM   gl_je_headers gjh
JOIN   gl_je_lines gjl
       ON gjl.je_header_id = gjh.je_header_id
JOIN   gl_code_combinations gcc
       ON gcc.code_combination_id = gjl.code_combination_id
WHERE  gjh.status    = 'P'
AND    gjh.ledger_id = :p_ledger_id
AND    (:p_period_name IS NULL OR gjh.period_name = :p_period_name)
AND    (:p_je_source   IS NULL OR gjh.je_source   = :p_je_source)
AND    (:p_account_prefix IS NULL
        OR gcc.concatenated_segments LIKE :p_account_prefix || '%')
ORDER BY gjh.period_name,
         gjh.name,
         gjl.je_line_num

Final Thoughts

Reliable Oracle Fusion Finance SQL is mostly about respecting the accounting model. Header totals are not accounting entries, invoices are not payment schedules, XLA entity IDs are not source transaction IDs, and an open AR schedule can represent more than an invoice.

Use these queries as verified starting patterns, then add the ledger, organization, currency, transaction-class, security, and reconciliation rules required by your own implementation.