Query : GL to SLA to FA

🔍 GL → SLA → FA Oracle Cloud ERP Trace Query

This SQL traces journal entries from General Ledger (GL) through Subledger Accounting (SLA/XLA) down to Fixed Assets (FA) tables. Perfect for debugging depreciation postings or asset-related journal flows.

SELECT
    --===============================
    -- BATCH INFORMATION (GL_JE_BATCHES)
    --===============================
    batch.je_batch_id,
    batch.name                     AS "Batch Name",
    batch.status                   AS "Batch Status",
    glk2.meaning                   AS "Batch Status Meaning", 
    batch.description              AS "Batch Description",
    batch.control_total            AS "Control Total",
    batch.creation_date            AS "Batch Creation Date",
    batch.created_by               AS "Batch Created By",
    batch.last_update_date         AS "Batch Last Update Date",
    batch.last_updated_by          AS "Batch Last Updated By",
    batch.posted_date              AS "Batch Posted Date",
    batch.approver_employee_id,
    batch.approval_status_code     AS "Batch Approval Status Code",
    glk.meaning                    AS "Approval Status Meaning",
    batch.parent_je_batch_id,

    --===============================
    -- HEADER INFORMATION (GL_JE_HEADERS)
    --===============================
    hdr.je_header_id,
    hdr.name                       AS "Header Name",
    hdr.description                AS "Header Description",
    src.user_je_source_name        AS "JE Source",
    cat.user_je_category_name      AS "JE Category", 
    hdr.accrual_rev_change_sign_flag,
    hdr.date_created,
    hdr.default_effective_date,
    hdr.status,
    hdr.currency_code,
    hdr.external_reference         AS "External Reference",
    hdr.accrual_rev_flag,
    hdr.accrual_rev_period_name,
    hdr.reversed_je_header_id,

    --===============================
    -- LINE INFORMATION (GL_JE_LINES)
    --===============================
    line.je_line_num,
    line.description               AS "Line Description",
    line.entered_dr,
    line.entered_cr,
    line.accounted_dr,
    line.accounted_cr,
    line.currency_conversion_rate,
    line.currency_conversion_type,
    line.currency_conversion_date,
    
    -- CODE COMBINATION SEGMENTS
    gcc.segment1                   AS "Segment 1 - LE",
    gcc.segment2                   AS "Segment 2 - Account", 
    gcc.segment3                   AS "Segment 3 - Employee",
    gcc.segment4                   AS "Segment 4 - Department",
    gcc.segment5                   AS "Segment 5 - Project",
    gcc.segment6                   AS "Segment 6 - IC",
    gcc.segment7                   AS "Segment 7 - Vendor",
    gcc.segment8                   AS "Segment 8 - Future 1",
    gcc.segment9                   AS "Segment 9 - Future 2",
    gcc.segment10,
    
    ledger.name                    AS ledgername,
    gp.period_name,

    --===============================
    -- XLA (SUBLEDGER ACCOUNTING)
    --===============================
    xal.ae_header_id,
    xe.event_id                    AS XE_EVENT_ID,
    xah.event_id                   AS XAH_EVENT_ID,
    gir.gl_sl_link_id,
    xal.description                AS "XLA Line Description",

    --===============================
    -- FIXED ASSETS
    --===============================
    fds.asset_id,
    fab.asset_number

FROM
    gl_je_headers        hdr,
    gl_je_lines          line,
    gl_code_combinations gcc,
    gl_ledgers           ledger,
    gl_je_sources        src,
    gl_je_categories     cat,
    gl_je_batches        batch,
    gl_periods           gp,
    gl_lookups           glk,
    gl_lookups           glk2,
    xla_ae_headers       xah,
    xla_ae_lines         xal,
    xla_distribution_links xdl,
    gl_import_references gir,
    xla_events           xe,
    fa_deprn_summary     fds,
    fa_additions_b       fab

WHERE 1 = 1
    -- GL JOINS
    AND hdr.je_header_id         = line.je_header_id
    AND line.code_combination_id = gcc.code_combination_id
    AND hdr.ledger_id            = ledger.ledger_id
    AND line.ledger_id           = ledger.ledger_id
    AND hdr.je_source            = src.je_source_name
    AND hdr.je_category          = cat.je_category_name
    AND hdr.je_batch_id          = batch.je_batch_id
    AND hdr.period_name          = gp.period_name
    AND ledger.period_set_name   = gp.period_set_name
    AND batch.approval_status_code = glk.lookup_code
    AND glk.lookup_type          = 'JE_BATCH_APPROVAL_STATUS'
    AND batch.status             = glk2.lookup_code
    AND glk2.lookup_type         = 'MJE_BATCH_STATUS'

    -- BUSINESS FILTERS
    AND gp.period_name   LIKE '10%2024'
    AND hdr.je_source    = 'Assets'
    AND hdr.je_category  = 'Depreciation' 
    AND ledger.name      = 'GAAP Ledger'

    -- SLA & FA LINKS (Critical Trace Path)
    AND xah.ae_header_id      = xal.ae_header_id
    AND xal.gl_sl_link_id     = gir.gl_sl_link_id
    AND xal.gl_sl_link_table  = gir.gl_sl_link_table
    AND xdl.ae_header_id      = xah.ae_header_id
    AND xdl.ae_line_num       = xal.ae_line_num
    AND xdl.application_id    = xah.application_id
    AND gir.je_header_id      = hdr.je_header_id
    AND gir.je_line_num       = line.je_line_num
    AND xe.event_id           = xah.event_id
    AND fds.event_id          = xe.event_id
    AND fds.asset_id          = fab.asset_id

    -- SPECIFIC RECORD FILTERS
    AND batch.je_batch_id = '3013241'
    AND hdr.je_header_id  = '3134824'
    AND line.je_line_num  = '2'
    AND NVL(hdr.currency_code, 0) NOT IN ('STAT')

ORDER BY je_batch_id, je_header_id, je_line_num;

💡 Pro Tip: Remove the last 4 specific ID filters to trace all Assets/Depreciation journals for Oct 2024 in GAAP Ledger.