FA Asset Addition - Transfer in CIP Cost Adjustments (Oracle Fusion SQL)

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

This FA Asset Addition Report - Transfer In CIP Cost Adjustments query displays detailed CIP cost adjustments for newly added assets during open depreciation periods, helping users monitor asset capitalization accuracy.

#sqlquery

SELECT 
--SQL4Fusion (An Orbit Analytics Project)
--This query displays CIP cost adjustments for newly added assets during open depreciation periods.
  -- Asset Details
  FAB.ASSET_NUMBER,
  FAB.ASSET_TYPE,
  FDH.BOOK_TYPE_CODE BOOK_TYPE,
  --Adjustment Info
  FADJ.ADJUSTMENT_TYPE,
  FADJ.SOURCE_TYPE_CODE,
  -- GL Details
  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 Info
  NVL(FADJ.ADJUSTMENT_AMOUNT,0) ADJUSTMENT_AMOUNT,
  NVL(FADJ.ANNUALIZED_ADJUSTMENT,0) ANNUALIZED_ADJUSTMENT,
  DECODE(FADJ.DEBIT_CREDIT_FLAG, 'CR', 1, -1) * NVL(FADJ.ADJUSTMENT_AMOUNT, 0) COST
FROM
  FA_ASSET_HISTORY FAH,
  FA_DEPRN_PERIODS FDP,
  FA_CATEGORY_BOOKS FCB,
  FA_TRANSACTION_HEADERS FTH,
  FA_ADDITIONS_B FAB,
  FA_BOOKS FB,
  FA_ADJUSTMENTS FADJ,
  FA_DISTRIBUTION_HISTORY FDH,
  GL_CODE_COMBINATIONS GCC
WHERE
  1 = 1
  AND FDP.PERIOD_COUNTER = FADJ.PERIOD_COUNTER_CREATED
  AND FDP.BOOK_TYPE_CODE = FTH.BOOK_TYPE_CODE
  AND FCB.CATEGORY_ID = FAH.CATEGORY_ID
  AND FAH.BOOK_TYPE_CODE = FCB.BOOK_TYPE_CODE
  AND FTH.TRANSACTION_HEADER_ID = FAH.TRANSACTION_HEADER_ID_IN
  AND FAB.ASSET_ID = FAH.ASSET_ID
  AND FB.BOOK_TYPE_CODE = FAH.BOOK_TYPE_CODE
  AND FB.ASSET_ID = FAH.ASSET_ID
  AND FB.DATE_INEFFECTIVE IS NULL
  AND FADJ.TRANSACTION_HEADER_ID = FTH.TRANSACTION_HEADER_ID
  AND FADJ.BOOK_TYPE_CODE = FDH.BOOK_TYPE_CODE
  AND FADJ.ADJUSTMENT_TYPE = 'CIP COST'
  AND FTH.TRANSACTION_TYPE_CODE IN ('CIP ADDITION','TRANSFER IN')
  AND FADJ.BOOK_TYPE_CODE = FTH.BOOK_TYPE_CODE
  AND FADJ.DISTRIBUTION_ID = FDH.DISTRIBUTION_ID
  AND FDH.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
  AND FTH.DATE_EFFECTIVE >= FDP.PERIOD_OPEN_DATE
  AND FTH.DATE_EFFECTIVE < NVL(FDP.PERIOD_CLOSE_DATE, FTH.DATE_EFFECTIVE + 1)