FA Asset Additions - Cost Adjustments for Additions (Oracle Fusion SQL)

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

This FA Asset Addition Report - Cost Adjustments for Additions query displays detailed cost and accounting information for newly added assets, including CIP and expense adjustments, helping users validate asset capitalization and cost accuracy.

#sqlquery

SELECT 
--SQL4Fusion (An Orbit Analytics Project)
--This query displays cost and accounting details for newly added assets including CIP adjustments.
  -- Asset Details
  FAB.ASSET_NUMBER,
  FAB.ASSET_TYPE,
  FB.BOOK_TYPE_CODE BOOK_TYPE,
  --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,
  DECODE(
    FAH.ASSET_TYPE,
    'CIP',
    NULL,
    FCB.DEPRN_EXPENSE_ACCOUNT_CCID
  ) DEPRECIATION_EXPENSE_ACCOUNT,
  -- Amount Details
  NVL(FDS.BONUS_RATE, 0) BONUS_RATE,
  NVL(FDD.COST,0) TOTAL_ASSET_COST,
  NVL(FDD.DEPRN_AMOUNT,0) DEPRN_AMOUNT,
  NVL(FDD.DEPRN_ADJUSTMENT_AMOUNT,0) DEPRN_ADJUSTMENT_AMOUNT,
  (FDD.COST) -(DECODE(FADJ.DEBIT_CREDIT_FLAG, 'DR', 1, -1) * NVL(FADJ.ADJUSTMENT_AMOUNT, 0)) COST
  FROM
  FA_ASSET_HISTORY FAH,
  FA_DEPRN_DETAIL FDD,
  FA_DEPRN_PERIODS FDP,
  FA_CATEGORY_BOOKS FCB,
  FA_TRANSACTION_HEADERS FTH,
  FA_DEPRN_SUMMARY FDS,
  FA_ADDITIONS_B FAB,
  FA_BOOKS FB,
  FA_DISTRIBUTION_HISTORY FDH,
  GL_CODE_COMBINATIONS GCC,
  FA_ADJUSTMENTS FADJ
WHERE 1=1
  AND FDD.ASSET_ID = FAH.ASSET_ID
  AND FDP.PERIOD_COUNTER = FDD.PERIOD_COUNTER
  AND FDP.BOOK_TYPE_CODE = FDD.BOOK_TYPE_CODE
  AND FDD.BOOK_TYPE_CODE = FAH.BOOK_TYPE_CODE
  AND FAH.DATE_EFFECTIVE <= NVL (FDP.PERIOD_CLOSE_DATE, SYSDATE)
  AND NVL (FAH.DATE_INEFFECTIVE, SYSDATE + 1) > NVL (FDP.PERIOD_CLOSE_DATE, SYSDATE)
  AND FTH.TRANSACTION_TYPE_CODE IN ('ADDITION','CIP ADDITION','ADJUSTMENT','CIP ADJUSTMENT')
  AND FCB.CATEGORY_ID = FAH.CATEGORY_ID
  AND FCB.BOOK_TYPE_CODE = FDD.BOOK_TYPE_CODE
  AND FTH.TRANSACTION_HEADER_ID = FAH.TRANSACTION_HEADER_ID_IN
  AND FDS.ASSET_ID = FDD.ASSET_ID
  AND FDD.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE
  AND FDS.PERIOD_COUNTER = (
    SELECT
      NVL (MIN (FDS_SUB.PERIOD_COUNTER), FDD.PERIOD_COUNTER)
    FROM
      FA_DEPRN_SUMMARY FDS_SUB
    WHERE
      FDS_SUB.BOOK_TYPE_CODE = FDD.BOOK_TYPE_CODE
      AND FDS_SUB.ASSET_ID = FDD.ASSET_ID
      AND FDS_SUB.DEPRN_SOURCE_CODE = 'DEPRN'
  )
  AND FAB.ASSET_ID = FDD.ASSET_ID
  AND FB.BOOK_TYPE_CODE = FDD.BOOK_TYPE_CODE
    AND FB.ASSET_ID = FAB.ASSET_ID
    AND FB.DATE_INEFFECTIVE IS NULL
  AND FDD.DISTRIBUTION_ID = FDH.DISTRIBUTION_ID
  AND FDD.ASSET_ID = FDH.ASSET_ID
  AND FDD.BOOK_TYPE_CODE = FDH.BOOK_TYPE_CODE
  AND GCC.CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID
  AND FADJ.BOOK_TYPE_CODE = FDD.BOOK_TYPE_CODE
  AND FADJ.ASSET_ID = FDD.ASSET_ID 
  AND FADJ.ADJUSTMENT_TYPE = 'COST'
  AND FADJ.PERIOD_COUNTER_CREATED = FDD.PERIOD_COUNTER
  AND FADJ.DISTRIBUTION_ID = FDD.DISTRIBUTION_ID
  AND FTH.TRANSACTION_HEADER_ID = FADJ.TRANSACTION_HEADER_ID(+)
  ORDER BY  FAB.ASSET_NUMBER