FA Asset Additions - Non-CIP Additions Cost (Oracle Fusion SQL)

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

#oracle fusion sql

FA Asset Additions & Non-CIP Additions Cost — Oracle Fusion SQL Query

This post shares an SQL query tailored for Oracle Fusion Fixed Assets (FA), designed to extract asset additions and non-CIP (non-in-progress) addition cost details from your Fusion FA setup. Use this query to get a comprehensive view of all asset additions that are completed (i.e. not still in CIP) and understand their costs.

What this query returns

  • Asset identifier (asset number / tag), asset description
  • Addition date, addition type (asset addition vs. non-CIP addition)
  • Cost breakdown: acquisition cost, capitalized cost, cost currency (functional and/or transactional)
  • Organization or ledger context (if applicable)
  • Asset book, depreciation book, book status (active, pending, etc.)
  • Supplier/vendor (if applicable), purchase order or invoice reference (if recorded)
  • Any assignment/asset location details (e.g. cost centre, department) as available

Use cases

  • Generating fixed-asset reports for accounting and audit purposes
  • Verifying all non-CIP additions have been correctly capitalized and costed
  • Getting a cost-center wise breakdown of asset additions
  • Preparing data for depreciation scheduling or asset register reconciliation
  • Supporting period-end closing and fixed asset reconciliations

Using & Customizing the Query

  • Copy and run the SQL against your Fusion FA schema — adjust table aliases based on your database structure
  • Add or remove columns depending on what details you need (e.g. include vendor name, PO number, invoice number)
  • If your Fusion setup uses multiple ledgers or organizations, filter or group by ledger/org for clarity
  • Feel free to post here if you need help adding more fields (e.g. depreciation book info, historical cost changes, disposal info)

#sqlquery

SELECT
 --SQL4Fusion (An Orbit Analytics Project)
--This query retrieves cost and asset details for non-CIP asset additions.
  -- Asset Details
  FAB.ASSET_NUMBER,
  FAB.ASSET_TYPE,
  -- Lease Info
  FAB.OWNED_LEASED,
  FAH.LEASE_TYPE_CODE,
  FB.BOOK_TYPE_CODE BOOK_TYPE,
  -- Transaction Info
  FTH.TRANSACTION_NAME,
  FTH.TRANSACTION_HEADER_ID,
  FTH.TRANSACTION_TYPE_CODE,
  FTH.TRANSACTION_SUBTYPE,
  FDH.TRANSACTION_UNITS,
  -- GL Info
  GCC.SEGMENT1 COMP_CODE,
  DECODE (
    FAH.ASSET_TYPE,
    'CIP',
    FCB.WIP_COST_ACCOUNT_CCID,
    FCB.ASSET_COST_ACCOUNT_CCID
  ) GL_ACCOUNT,
  GCC.SEGMENT3 DEPARTMENT,
  FND_FLEX_EXT.GET_SEGS(
    'GL',
    'GL#',
    GCC.CHART_OF_ACCOUNTS_ID,
    GCC.CODE_COMBINATION_ID
  ) EXPENSE_ACCOUNT,
  --Amount Details
    SUM (
    DECODE (
      FA.SOURCE_TYPE_CODE,
      'CIP ADJUSTMENT',
      DECODE (
        FTH.TRANSACTION_TYPE_CODE,
        '',
        0,
        DECODE (
          FA.DEBIT_CREDIT_FLAG,
          'DR', 1,
          -1
        ) * NVL(FA.ADJUSTMENT_AMOUNT, 0)
      ),
      DECODE (FA.DEBIT_CREDIT_FLAG, 'DR', 1, -1) * NVL(FA.ADJUSTMENT_AMOUNT, 0)
    )
  ) COST
FROM
  FA_ADDITIONS_B FAB,
  FA_CATEGORY_BOOKS FCB,
  GL_CODE_COMBINATIONS GCC,
  FA_TRANSACTION_HEADERS FTHA,
  FA_ASSET_HISTORY FAH,
  FA_BOOKS FB,
  FA_DISTRIBUTION_HISTORY FDH,
  FA_DEPRN_PERIODS FDP,
  FA_ADJUSTMENTS FA,
  FA_TRANSACTION_HEADERS FTH
WHERE
  FDP.PERIOD_COUNTER = FA.PERIOD_COUNTER_CREATED
  AND FB.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
  AND FB.ASSET_ID = FAB.ASSET_ID
  AND FB.DATE_INEFFECTIVE IS NULL
  AND FAH.ASSET_ID = FAB.ASSET_ID
  AND FCB.CATEGORY_ID = FAH.CATEGORY_ID
  AND FCB.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
  AND FTH.BOOK_TYPE_CODE(+) = FB.BOOK_TYPE_CODE
  AND FTH.ASSET_ID(+) = FB.ASSET_ID
  AND FTH.TRANSACTION_TYPE_CODE = 'ADDITION'
  AND FA.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
  AND FA.ASSET_ID = FB.ASSET_ID
  AND FA.ADJUSTMENT_TYPE <> 'CIP COST'
  AND FA.SOURCE_TYPE_CODE <> 'CIP ADDITION'
  AND FTHA.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
  AND FTHA.ASSET_ID = FB.ASSET_ID
  AND FA.DISTRIBUTION_ID = FDH.DISTRIBUTION_ID
  AND FDH.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
GROUP BY
  FAB.ASSET_NUMBER,
  FAB.ASSET_TYPE,
  FAB.OWNED_LEASED,
  FAH.LEASE_TYPE_CODE,
  FB.BOOK_TYPE_CODE,
  FTH.TRANSACTION_NAME,
  FTH.TRANSACTION_HEADER_ID,
  FTH.TRANSACTION_TYPE_CODE,
  FTH.TRANSACTION_SUBTYPE,
  FDH.TRANSACTION_UNITS,
  GCC.SEGMENT1,
  DECODE (
    FAH.ASSET_TYPE,
    'CIP',
    FCB.WIP_COST_ACCOUNT_CCID,
    FCB.ASSET_COST_ACCOUNT_CCID
  ) ,
  GCC.SEGMENT3 ,
  FND_FLEX_EXT.GET_SEGS(
    'GL',
    'GL#',
    GCC.CHART_OF_ACCOUNTS_ID,
    GCC.CODE_COMBINATION_ID
  )