FA Cost Adjustments Audit Info (Oracle Fusion SQL)

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

This FA Cost Adjustments Audit query displays cost adjustment details for fixed assets across accounting periods, helping users to track financial changes and support audit readiness.

#sqlquery

SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- This query tracks Fixed Assets cost adjustments.
   FDP1.BOOK_TYPE_CODE,
   FDP1.PERIOD_NAME,
   -- Asset Details
   FAB.ASSET_NUMBER,
   FAB.TAG_NUMBER,
   FAH.UNITS ACTUAL_UNITS,
   FAB.SERIAL_NUMBER,
   FAB.MODEL_NUMBER,
   FAB.MANUFACTURER_NAME,
   FAB.PROPERTY_TYPE_CODE,
   FAB.IN_USE_FLAG,
   FAB.OWNED_LEASED,
   FAB.NEW_USED,
   FB1.DATE_PLACED_IN_SERVICE,
   FDH.UNITS_ASSIGNED,
   -- GL Details
   FCBK.SEGMENT1 || '-' || FCBK.SEGMENT2 CATEGORY,
   FL.SEGMENT3 CITY,
   FL.SEGMENT2 STATE,
   FL.SEGMENT1 COUNTRY,
   FND_FLEX_EXT.GET_SEGS(
     'GL',
     'GL#',
     GCC.CHART_OF_ACCOUNTS_ID,
     GCC.CODE_COMBINATION_ID
   ) ACCOUNT,
   GCC.SEGMENT1 COMPANY,
   -- Cost Details
   SUM(FB1.COST) OLD_COST,
   SUM(FB2.COST) NEW_COST,
   SUM(ABS (FB2.COST - FB1.COST)) NET_CHANGE
FROM
  FA_TRANSACTION_HEADERS FTH,   
  FA_ADDITIONS_B FAB,
  FA_DEPRN_PERIODS FDP1,
  FA_DISTRIBUTION_HISTORY FDH,
  FA_BOOKS FB1,
  FA_BOOKS FB2,
  FA_BOOK_CONTROLS FBC,
  GL_LEDGERS GL,
  FA_ASSET_HISTORY FAH,
  FA_CATEGORY_BOOKS FCB,
  FA_CATEGORIES_B FCBK,
  FA_LOCATIONS FL,
  GL_CODE_COMBINATIONS GCC
WHERE 1=1
  AND FTH.TRANSACTION_TYPE_CODE IN('ADJUSTMENT', 'CIP ADJUSTMENT')
  AND FAB.ASSET_ID = FTH.ASSET_ID
  AND FTH.BOOK_TYPE_CODE = FDP1.BOOK_TYPE_CODE
  AND FTH.DATE_EFFECTIVE BETWEEN FDP1.PERIOD_OPEN_DATE
  AND NVL (FDP1.PERIOD_CLOSE_DATE, SYSDATE)
  AND FTH.ASSET_ID = FDH.ASSET_ID(+)
  AND FTH.BOOK_TYPE_CODE = FDH.BOOK_TYPE_CODE(+)
  AND FBC.DISTRIBUTION_SOURCE_BOOK(+) = FDH.BOOK_TYPE_CODE
  AND FB1.TRANSACTION_HEADER_ID_OUT = FTH.TRANSACTION_HEADER_ID
  AND FB1.BOOK_TYPE_CODE = FTH.BOOK_TYPE_CODE
  AND FB2.TRANSACTION_HEADER_ID_IN = FTH.TRANSACTION_HEADER_ID
  AND FB2.BOOK_TYPE_CODE = FTH.BOOK_TYPE_CODE
  AND FB1.BOOK_TYPE_CODE = FBC.BOOK_TYPE_CODE
  AND FBC.SET_OF_BOOKS_ID = GL.LEDGER_ID
  AND FAH.ASSET_ID = FAB.ASSET_ID
  AND FAH.BOOK_TYPE_CODE = FBC.BOOK_TYPE_CODE
  AND FCB.CATEGORY_ID = FAH.CATEGORY_ID
  AND FCB.BOOK_TYPE_CODE = FAH.BOOK_TYPE_CODE
  AND FCBK.CATEGORY_ID = FCB.CATEGORY_ID
  AND FL.LOCATION_ID = FDH.LOCATION_ID
  AND FDH.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
GROUP BY
   FAB.ASSET_NUMBER,
   FAB.TAG_NUMBER,
   FAH.UNITS,
   FAB.SERIAL_NUMBER,
   FAB.MODEL_NUMBER,
   FAB.MANUFACTURER_NAME,
   FAB.PROPERTY_TYPE_CODE,
   FAB.IN_USE_FLAG,
   FAB.OWNED_LEASED,
   FAB.NEW_USED,
   FB1.DATE_PLACED_IN_SERVICE,
   FDP1.BOOK_TYPE_CODE,
   FDP1.PERIOD_NAME,
   FDH.UNITS_ASSIGNED,
   FCBK.SEGMENT1 || '-' || FCBK.SEGMENT2 ,
   FL.SEGMENT3 ,
   FL.SEGMENT2 ,
   FL.SEGMENT1 ,
   FND_FLEX_EXT.GET_SEGS(
     'GL',
     'GL#',
     GCC.CHART_OF_ACCOUNTS_ID,
     GCC.CODE_COMBINATION_ID
   ) ,
   GCC.SEGMENT1