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

Always pass ledger_id as a bind variable or explicit filter in every GL query. GL_JE_HEADERS, GL_BALANCES, and GL_PERIOD_STATUSES are multi-ledger tables - omitting the ledger filter causes queries to aggregate across all ledgers, producing incorrect totals.

SELECT
    gjh.je_header_id,
    gjh.name                          AS journal_name,
    gjh.je_source,
    gjh.je_category,
    gjh.period_name,
    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,
    gjl.je_line_num;

Notes

• status = 'P' limits to posted journals only - always include this filter
• Always filter by ledger_id - GL tables are multi-ledger
• accounted_dr/cr are ledger currency amounts; entered_dr/cr are transaction currency amounts

SELECT
    gb.period_name,
    gcc.concatenated_segments          AS account,
    gb.begin_balance_dr,
    gb.begin_balance_cr,
    gb.period_net_dr,
    gb.period_net_cr,
    (gb.begin_balance_dr - gb.begin_balance_cr
     + gb.period_net_dr  - gb.period_net_cr)  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
ORDER BY
    gcc.concatenated_segments;

Notes

• actual_flag = 'A' = actual balances  |  'B' = budget  |  'E' = encumbrance
• Far more performant than aggregating journal lines for period balance reports

SELECT
    gcc.concatenated_segments  AS account,
    SUM(gb.period_net_dr)      AS total_debits,
    SUM(gb.period_net_cr)      AS total_credits,
    SUM(gb.begin_balance_dr - gb.begin_balance_cr
        + gb.period_net_dr  - gb.period_net_cr)  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_year = :p_year
    AND gb.period_num <= :p_period_num
GROUP BY
    gcc.concatenated_segments
ORDER BY
    gcc.concatenated_segments;

Notes

• Filter by period_year + period_num <= :p_period_num to accumulate YTD balances
• Add a currency_code filter if your ledger contains multiple currencies
• Double-count risk: Depending on your ledger configuration, YTD balances may already be reflected in begin_balance values. Validate the accumulation logic against your specific ledger setup - in some configurations, querying multiple periods with this formula overstates the ending balance

SELECT
    gjh.je_source,
    gjh.je_category,
    COUNT(*)                       AS journal_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;

SELECT
    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;

SELECT
    gp.period_name,
    gp.start_date,
    gp.end_date,
    gp.adjustment_period_flag,
    gp.closing_status
FROM
    gl_period_statuses gp
WHERE
    gp.ledger_id       = :p_ledger_id
    AND gp.application_id  = 101
ORDER BY
    gp.start_date DESC;

Note on AP_SUPPLIERS: Supplier master data in Oracle Fusion is built on top of the TCA (Trading Community Architecture) model. AP_SUPPLIERS remains the most common reporting entry point, but supplier attributes such as party names, classifications, and addresses may also need to be sourced from HZ_* tables depending on your reporting requirements. Depending on the Oracle Fusion release and security configuration, supplier information may also be exposed through POZ_SUPPLIERS views in some environments.

SELECT
    ai.invoice_num,
    ai.invoice_date,
    ai.invoice_amount,
    NVL(ai.amount_paid, 0)                       AS amount_paid,
    ai.invoice_amount - NVL(ai.amount_paid, 0)   AS outstanding_amount,
    ai.payment_status_flag,
    ai.due_date,
    s.vendor_name,
    s.segment1  AS supplier_number
FROM
    ap_invoices_all ai
    JOIN ap_suppliers s ON s.vendor_id = ai.vendor_id
WHERE
    ai.cancelled_date IS NULL
    AND ai.payment_status_flag IN ('N', 'P')
ORDER BY
    ai.due_date;

Notes

• payment_status_flag: N = not paid  |  P = partially paid  |  Y = fully paid
• Add AND ai.org_id = :p_org_id in multi-org environments

SELECT
    s.vendor_name,
    SUM(CASE WHEN TRUNC(SYSDATE) - ai.due_date <= 0
             THEN ai.invoice_amount - NVL(ai.amount_paid, 0) ELSE 0 END)  AS current_bucket,
    SUM(CASE WHEN TRUNC(SYSDATE) - ai.due_date BETWEEN 1  AND 30
             THEN ai.invoice_amount - NVL(ai.amount_paid, 0) ELSE 0 END)  AS bucket_1_30,
    SUM(CASE WHEN TRUNC(SYSDATE) - ai.due_date BETWEEN 31 AND 60
             THEN ai.invoice_amount - NVL(ai.amount_paid, 0) ELSE 0 END)  AS bucket_31_60,
    SUM(CASE WHEN TRUNC(SYSDATE) - ai.due_date > 60
             THEN ai.invoice_amount - NVL(ai.amount_paid, 0) ELSE 0 END)  AS over_60
FROM
    ap_invoices_all ai
    JOIN ap_suppliers s ON s.vendor_id = ai.vendor_id
WHERE
    ai.cancelled_date IS NULL
GROUP BY
    s.vendor_name
ORDER BY
    over_60 DESC;

SELECT
    s.vendor_name,
    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 ap_suppliers s ON s.vendor_id = ai.vendor_id
WHERE
    ai.invoice_date >= ADD_MONTHS(TRUNC(SYSDATE), -12)
    AND ai.cancelled_date IS NULL
GROUP BY
    s.vendor_name
ORDER BY
    total_invoiced DESC;

SELECT
    ai.invoice_num,
    ai.invoice_date,
    ai.invoice_amount,
    ph.segment1   AS po_number,
    s.vendor_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 ap_suppliers     s   ON s.vendor_id      = ai.vendor_id
WHERE
    ail.po_header_id IS NOT NULL
    AND ai.cancelled_date IS NULL
ORDER BY
    ai.invoice_date DESC;

SELECT
    ai.invoice_num,
    aid.line_type_lookup_code,
    aid.amount,
    gcc.concatenated_segments  AS 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
ORDER BY
    ai.invoice_num,
    aid.distribution_line_number;

SELECT
    ac.check_number,
    ac.check_date,
    ac.amount,
    ac.currency_code,
    ac.status_lookup_code,
    s.vendor_name
FROM
    ap_checks_all  ac
    JOIN ap_suppliers s ON s.vendor_id = ac.vendor_id
WHERE
    ac.check_date >= ADD_MONTHS(TRUNC(SYSDATE), -6)
ORDER BY
    ac.check_date DESC;

SELECT
    hp.party_name,
    hca.account_number,
    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'
GROUP BY
    hp.party_name,
    hca.account_number
ORDER BY
    outstanding_balance DESC;

SELECT
    hp.party_name,
    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 > 60
             THEN aps.amount_due_remaining ELSE 0 END)  AS over_60
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'
GROUP BY
    hp.party_name
ORDER BY
    over_60 DESC;

SELECT
    hp.party_name,
    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.trx_date >= ADD_MONTHS(TRUNC(SYSDATE), -12)
ORDER BY
    rcta.trx_date DESC;

Important - SLA data model varies significantly by environment and release: The examples below illustrate the conceptual join path from AP through SLA into GL. Production implementations often require joining via XLA_TRANSACTION_ENTITIES and filtering by APPLICATION_ID and ENTITY_CODE. The direct entity_id = invoice_id join shown below may not resolve correctly in all environments. Always validate the join path in your specific Oracle Fusion instance before using in production reports.

SELECT
    ai.invoice_num,
    xah.ae_header_id,
    xal.ae_line_num,
    xal.accounted_dr,
    xal.accounted_cr,
    gcc.concatenated_segments  AS account
FROM
    ap_invoices_all   ai
    JOIN xla_ae_headers  xah ON xah.entity_id     = ai.invoice_id
    JOIN xla_ae_lines    xal ON xal.ae_header_id  = xah.ae_header_id
    JOIN gl_code_combinations gcc
                             ON gcc.code_combination_id = xal.code_combination_id
WHERE
    ai.cancelled_date IS NULL
ORDER BY
    ai.invoice_num,
    xal.ae_line_num;

SELECT
    ai.invoice_num,
    gjh.name                   AS journal_name,
    gjh.period_name,
    gjl.je_line_num,
    gcc.concatenated_segments  AS account,
    gjl.accounted_dr,
    gjl.accounted_cr
FROM
    ap_invoices_all    ai
    JOIN xla_ae_headers   xah ON xah.entity_id      = ai.invoice_id
    JOIN xla_ae_lines     xal ON xal.ae_header_id   = xah.ae_header_id
    JOIN gl_import_references gir
                              ON gir.gl_sl_link_id  = xal.gl_sl_link_id
    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_num = :p_invoice_num;

SELECT
    cba.bank_account_name,
    cba.bank_account_num,
    cba.currency_code,
    cbau.account_type,
    cbau.primary_acct_use_flag,
    cba.start_date,
    cba.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.end_date IS NULL OR cba.end_date >= TRUNC(SYSDATE))
ORDER BY
    cba.bank_account_name,
    cbau.account_type;

Security Note: Query results across these Finance tables may vary depending on the executing user's Oracle Fusion Data Roles, Data Access Sets, Business Unit assignments, and Ledger security configuration. Two Finance users running the same GL query may see different journals depending on their assigned Data Access Set and Ledger scope. For a detailed overview of how Oracle Fusion security affects SQL query results across GL, AP, and AR, see Oracle Fusion SQL Security Explained.

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    = NVL(:p_ledger_id,   gjh.ledger_id)
    AND gjh.period_name  = NVL(:p_period_name, gjh.period_name)
    AND gjh.je_source    = NVL(:p_je_source,   gjh.je_source)
    AND (:p_account_seg IS NULL
         OR gcc.concatenated_segments LIKE :p_account_seg || '%')
ORDER BY
    gjh.period_name,
    gjh.name,
    gjl.je_line_num;

Bind variable pattern: pass NULL to any parameter to return all values for that dimension. :p_ledger_id should always be populated in production to avoid cross-ledger aggregation. For account segment filtering, pass a prefix such as 6 to return all accounts starting with 6 (expense accounts in many chart-of-accounts configurations).

Accelerate Finance SQL Development with FusionLens SQL