FA Assets Posting to General Ledger (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Fixed Assets (FA-Fusion-SQL's)
1

#oracle fusion sql

FA Assets — Posting to General Ledger (Oracle Fusion SQL)

Welcome to the FA Assets Posting to General Ledger page — your go-to reference for SQL queries that help you extract and review asset-related general ledger (GL) postings generated by Oracle Fusion Fixed Assets (FA).

What you get from this SQL query

  • GL journal entries created from asset transactions (asset additions, capitalizations, retirements, revaluations, depreciation, adjustments)
  • Journal header and line information: ledger/ledger-ID, accounting date, journal batch or reference number, GL account code, natural account, cost centre / department / flex-field segments (if configured)
  • Asset metadata: asset ID/number or tag, asset category or class, asset location (if your setup uses location flex-fields)
  • Transaction type (addition, depreciation, retirement, revaluation, adjustment, etc.), debit/credit indicator, amount (transactional or functional currency), date of posting, and user or process that created the entry

Why this is useful

  • To reconcile your fixed-asset subledger with the GL — ensuring that all FA additions/adjustments/depreciations are correctly reflected in the general ledger.
  • To audit asset accounting history — track which assets were capitalized, depreciated, revalued or retired, and when those journal entries went to the GL.
  • To support financial reporting and compliance — by exporting or analyzing GL entries related to fixed assets (cost centers, natural accounts, depreciation expense, accumulated depreciation, asset write-offs).
  • To prepare data for downstream reporting or analytics — for ERP-wide reports, financial dashboards, asset-based cost analysis, or migrations to warehouses/BI.

How to use & customize the query

  • Run the SQL against your Fusion FA/GL schema. If you have configured custom Accounting Flexfields or Asset flex-fields, join related tables to retrieve segment values (cost centre, location, category, etc.).
  • Filter by transaction type or account code to isolate e.g. only additions, only depreciation, or only retirements.
  • Include both header and line-level details (ledger, journal batch, accounting date, GL account, debit/credit, amount) and asset-level metadata (asset number, category, location) to enable full traceability.
  • Optionally, join with asset-history tables if you also want to see historical changes (e.g. reclassifications, unit adjustments, location changes).

#sqlquery

SELECT
 --SQL4Fusion (An Orbit Analytics Project)
 --Asset financial transactions summary.
  XAH.PERIOD_NAME,
  GJB.NAME JOURNAL_BATCH_NAME,
  GJH.NAME JOURNAL_ENTRY_NAME,
  GJH.DESCRIPTION JOURNAL_DESCRIPTION,
  GJH.JE_SOURCE,
  FND_FLEX_EXT.GET_SEGS(
    'GL',
    'GL#',
    GLCC.CHART_OF_ACCOUNTS_ID,
    GLCC.CODE_COMBINATION_ID
  ) GL_ACCOUNT,
  GLCC.SEGMENT1,
  GLCC.SEGMENT2,
  GLCC.SEGMENT3,
  GLCC.SEGMENT4,
  GLCC.SEGMENT5,
  FA_ADJ.SOURCE_TYPE_CODE,
  FA_ADJ.ADJUSTMENT_TYPE,
  FA_ADJ.BOOK_TYPE_CODE,
  FA_ADJ.ASSET_ID,
  FAB.ASSET_NUMBER,
  FAB.CURRENT_UNITS,
  FAB.ASSET_TYPE,
    SUM(XAL.ENTERED_DR) ENTERED_DR,
  SUM(XAL.ENTERED_CR) ENTERED_CR,
  SUM(XAL.ACCOUNTED_DR) ACCOUNTED_DR,
  SUM(XAL.ACCOUNTED_CR) ACCOUNTED_CR
FROM
  XLA_AE_HEADERS XAH,
  XLA_AE_LINES XAL,
  XLA_TRANSACTION_ENTITIES XTE,
  XLA_DISTRIBUTION_LINKS XDL,
  GL_IMPORT_REFERENCES GIR,
  GL_JE_BATCHES GJB,
  GL_JE_HEADERS GJH,
  GL_JE_LINES GJL,
  GL_CODE_COMBINATIONS GLCC,
  FA_ADJUSTMENTS FA_ADJ,
  FA_ADDITIONS_B FAB
WHERE
  1=1
  AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
  AND XAH.APPLICATION_ID = XAL.APPLICATION_ID
  AND GJH.JE_SOURCE = 'Assets'
  AND FA_ADJ.ASSET_ID = FAB.ASSET_ID(+)
  AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = FA_ADJ.ADJUSTMENT_LINE_ID(+)
  AND GJL.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID
  AND GIR.JE_HEADER_ID = GJL.JE_HEADER_ID
  AND GIR.JE_LINE_NUM = GJL.JE_LINE_NUM
  AND GIR.JE_BATCH_ID = GJB.JE_BATCH_ID
  AND GIR.JE_HEADER_ID = GJH.JE_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 XAH.AE_HEADER_ID = XDL.AE_HEADER_ID
  AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
  AND XAL.APPLICATION_ID = XTE.APPLICATION_ID
  AND XAH.EVENT_ID = XDL.EVENT_ID
  AND XAH.ENTITY_ID = XTE.ENTITY_ID
GROUP BY
  XAH.PERIOD_NAME,
  GJB.NAME,
  GJH.NAME,
  GJH.DESCRIPTION,
  GJH.JE_SOURCE,
  FND_FLEX_EXT.GET_SEGS(
    'GL',
    'GL#',
    GLCC.CHART_OF_ACCOUNTS_ID,
    GLCC.CODE_COMBINATION_ID
  ),
  GLCC.SEGMENT1,
  GLCC.SEGMENT2,
  GLCC.SEGMENT3,
  GLCC.SEGMENT4,
  GLCC.SEGMENT5,
  FA_ADJ.SOURCE_TYPE_CODE,
  FA_ADJ.ADJUSTMENT_TYPE,
  FA_ADJ.BOOK_TYPE_CODE,
  FA_ADJ.ASSET_ID,
  FAB.ASSET_NUMBER,
  FAB.CURRENT_UNITS,
  FAB.ASSET_TYPE