FA Asset Additions- CIP Additions Initial Cost and Reserve (Oracle Fusion SQL)

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

This FA Asset Addition Report - CIP Additions Initial Cost and Reserve query displays cost and account details for newly added CIP assets in the current depreciation period, helping users validate asset capitalization and accounting accuracy.

#sqlquery

SELECT
 --SQL4Fusion (An Orbit Analytics Project)
 --Retrieves cost and account details for newly added CIP assets.
  FB.BOOK_TYPE_CODE BOOK_TYPE,
  (GCC.SEGMENT1) COMPANY,
  DECODE (
    FAH.ASSET_TYPE,
    'CIP',
    FCB.WIP_COST_ACCOUNT_CCID,
    FCB.ASSET_COST_ACCOUNT_CCID
  ) ASSET_ACCOUNT,
  FTH.TRANSACTION_HEADER_ID TRANSACTION_NUMBER,
  FAB.ASSET_NUMBER ASSET_NUMBER_DESCRIPTION,
  GCC.SEGMENT3 DEPARTMENT,
  SUM (FDD.DEPRN_RESERVE) INITIAL_DEPRN_RESERVE,
  FND_FLEX_EXT.GET_SEGS(
    'GL',
    'GL#',
    GCC.CHART_OF_ACCOUNTS_ID,
    GCC.CODE_COMBINATION_ID
  ) ACCOUNT,
  DECODE (
    FAH.ASSET_TYPE,
    'CIP',
    NULL,
    FCB.DEPRN_EXPENSE_ACCOUNT_CCID
  ) RESERVE_ACCOUNT,
  NVL (FDS.BONUS_RATE, 0) BONUS_RATE
FROM
  FA_DEPRN_SUMMARY FDS,
  FA_ADDITIONS_B FAB,
  GL_CODE_COMBINATIONS GCC,
  FA_DISTRIBUTION_HISTORY FDH,
  FA_CATEGORY_BOOKS FCB,
  FA_TRANSACTION_HEADERS FTH,
  FA_ASSET_HISTORY FAH,
  FA_BOOKS FB,
  FA_DEPRN_PERIODS FDP,
  FA_DEPRN_DETAIL FDD
WHERE
  1 = 1
  AND FDS.ASSET_ID = FDD.ASSET_ID
  AND FAB.ASSET_ID = FDD.ASSET_ID
  AND FDP.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE
  AND FDP.PERIOD_COUNTER = FDD.PERIOD_COUNTER + 1
  AND FDD.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE
  AND FDD.DEPRN_SOURCE_CODE = 'B'
  AND FDD.DISTRIBUTION_ID = FDH.DISTRIBUTION_ID
  AND FDH.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
  AND FB.BOOK_TYPE_CODE = FDD.BOOK_TYPE_CODE
  AND FB.ASSET_ID = FDD.ASSET_ID
  AND FB.DATE_INEFFECTIVE IS NULL
  AND FAH.ASSET_ID = FDD.ASSET_ID
  AND FAH.DATE_EFFECTIVE <= NVL (FDP.PERIOD_CLOSE_DATE, SYSDATE)
  AND NVL (FAH.DATE_INEFFECTIVE, SYSDATE + 1) > NVL (FDP.PERIOD_CLOSE_DATE, SYSDATE)
  AND FCB.CATEGORY_ID = FAH.CATEGORY_ID
  AND FCB.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE
  AND FDD.ASSET_ID NOT IN (
    SELECT
      FA.ASSET_ID
    FROM
      FA_ADJUSTMENTS FA
    WHERE
      FA.BOOK_TYPE_CODE = FDD.BOOK_TYPE_CODE
      AND FA.ASSET_ID = FDD.ASSET_ID
  )
  AND FAB.ASSET_ID = FTH.ASSET_ID
  AND FCB.BOOK_TYPE_CODE = FTH.BOOK_TYPE_CODE
  AND (
    FAH.ASSET_TYPE = 'CIP'
    AND FTH.TRANSACTION_TYPE_CODE = 'CIP ADDITION'
  )
GROUP BY
  FB.BOOK_TYPE_CODE,
  (GCC.SEGMENT1),
  DECODE (
    FAH.ASSET_TYPE,
    'CIP',
    FCB.WIP_COST_ACCOUNT_CCID,
    FCB.ASSET_COST_ACCOUNT_CCID
  ),
  FTH.TRANSACTION_HEADER_ID,
  FAB.ASSET_NUMBER,
  GCC.SEGMENT3,
  FND_FLEX_EXT.GET_SEGS(
    'GL',
    'GL#',
    GCC.CHART_OF_ACCOUNTS_ID,
    GCC.CODE_COMBINATION_ID
  ),
  DECODE (
    FAH.ASSET_TYPE,
    'CIP',
    NULL,
    FCB.DEPRN_EXPENSE_ACCOUNT_CCID
  ),
  NVL (FDS.BONUS_RATE, 0)