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

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

This FA Asset Addition Report - CIP Adjustments Cost query displays cost adjustments and account details for CIP assets during the current period, helping users validate capitalization and accounting accuracy.

#sqlquery

SELECT
   --SQL4Fusion (An Orbit Analytics Project)
   --Retrieves CIP asset cost adjustments and accounting details.
  FB.BOOK_TYPE_CODE BOOK_TYPE,
  GCC.SEGMENT1,
  DECODE (
    FAH.ASSET_TYPE,
    'CIP',
    FCB.WIP_COST_ACCOUNT_CCID,
    FCB.ASSET_COST_ACCOUNT_CCID
  ) ASSET_ACCOUNT,
  FTHA.TRANSACTION_HEADER_ID TRANSACTION_HEADER_ID,
  FAB.ASSET_NUMBER ASSET_NUMBER,
  GCC.SEGMENT3 DEPARTMENT,
  FND_FLEX_EXT.GET_SEGS(
    'GL',
    'GL#',
    GCC.CHART_OF_ACCOUNTS_ID,
    GCC.CODE_COMBINATION_ID
  ) ACCOUNT,
  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 LIKE '%COST'
  AND FA.SOURCE_TYPE_CODE LIKE 'CIP AD%'
  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
  FB.BOOK_TYPE_CODE,
  GCC.SEGMENT1,
  DECODE (
    FAH.ASSET_TYPE,
    'CIP',
    FCB.WIP_COST_ACCOUNT_CCID,
    FCB.ASSET_COST_ACCOUNT_CCID
  ),
  FTHA.TRANSACTION_HEADER_ID,
  FAB.ASSET_NUMBER,
  GCC.SEGMENT3,
  FND_FLEX_EXT.GET_SEGS(
    'GL',
    'GL#',
    GCC.CHART_OF_ACCOUNTS_ID,
    GCC.CODE_COMBINATION_ID
  )