This FA GL Journal Adjustments query displays adjustment details to fixed assets that result in General Ledger journal entries, helping users to trace asset-level financial impacts across accounting periods.
#sqlquery
SELECT DISTINCT
--SQL4Fusion (An Orbit Analytics Project)
-- This query details the list of adjustments w.r.t Assets.
-- Asset Info
FAB.ASSET_NUMBER,
FAT.DESCRIPTION ASSET_DESCRIPTION,
FAB.ASSET_TYPE ASSET_TYPE,
-- Book Info
FAB.TAG_NUMBER,
FBC.BOOK_CLASS ,
FA.BOOK_TYPE_CODE BOOK_TYPE,
FBC.DISTRIBUTION_SOURCE_BOOK BOOK_CORPORATE_BOOK,
FA.ADJUSTMENT_TYPE,
-- Transaction Info
FTH.TRANSACTION_TYPE_CODE TRANSACTION_TYPE,
FTH.TRANSACTION_DATE_ENTERED TRANSACTION_DATE,
FTH.DATE_EFFECTIVE TRANSACTION_EFFECTIVE_DATE,
-- JE Info
GJB.NAME JOURNAL_BATCH_NAME,
GJH.NAME JOURNAL_HEADER_NAME,
GJB.DESCRIPTION JOURNAL_HEADER_DESCRIPTION,
GJB.STATUS JOURNAL_HEADER_STATUS,
--Periods
FDP1.PERIOD_NAME PERIOD_CREATED,
FDP2.PERIOD_NAME PERIOD_ADJUSTED,
GJH.PERIOD_NAME GL_PERIOD,
GL.CURRENCY_CODE CURRENCY,
-- Cost info
SUM(NVL(FA.ANNUALIZED_ADJUSTMENT,0)) ANNUALIZED_ADJUSTMENT,
SUM(NVL(FA.ADJUSTMENT_AMOUNT,0)) ADJUSTMENT_AMOUNT
FROM
FA_ADJUSTMENTS FA,
FA_ADDITIONS_B FAB,
FA_ADDITIONS_TL FAT,
FA_ASSET_KEYWORDS FAK,
FA_BOOK_CONTROLS FBC,
FA_CATEGORIES_B FCB,
FA_DEPRN_PERIODS FDP1,
FA_DEPRN_PERIODS FDP2,
FA_TRANSACTION_HEADERS FTH,
GL_LEDGERS GL,
GL_CODE_COMBINATIONS GCC,
XLA_DISTRIBUTION_LINKS XDL,
GL_JE_HEADERS GJH,
GL_JE_BATCHES GJB
WHERE 1=1
AND FAB.ASSET_ID = FA.ASSET_ID
AND FAB.ASSET_ID = FAT.ASSET_ID
AND FAT.LANGUAGE = USERENV('LANG')
AND FAB.ASSET_KEY_CCID = FAK.CODE_COMBINATION_ID(+)
AND FBC.BOOK_TYPE_CODE = FA.BOOK_TYPE_CODE
AND FAB.ASSET_CATEGORY_ID = FCB.CATEGORY_ID
AND FDP1.BOOK_TYPE_CODE = FA.BOOK_TYPE_CODE
AND FDP1.PERIOD_COUNTER = FA.PERIOD_COUNTER_CREATED
AND FDP2.BOOK_TYPE_CODE = FA.BOOK_TYPE_CODE
AND FDP2.PERIOD_COUNTER = FA.PERIOD_COUNTER_ADJUSTED
AND FTH.TRANSACTION_HEADER_ID = FA.TRANSACTION_HEADER_ID
AND FBC.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND FA.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID(+)
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = FA.ADJUSTMENT_LINE_ID
AND FA.ADJUSTMENT_TYPE = 'COST'
AND FBC.BOOK_CLASS = 'CORPORATE'
AND XDL.EVENT_TYPE_CODE LIKE '%ADJUSTMENTS'
AND XDL.AE_HEADER_ID = GJH.JE_HEADER_ID(+)
AND GJB.JE_BATCH_ID(+) = GJH.JE_BATCH_ID
GROUP BY
FAB.ASSET_NUMBER,
FAT.DESCRIPTION,
FAB.ASSET_TYPE,
FAB.TAG_NUMBER,
FBC.BOOK_CLASS ,
FA.BOOK_TYPE_CODE,
FBC.DISTRIBUTION_SOURCE_BOOK,
FA.ADJUSTMENT_TYPE,
FTH.TRANSACTION_TYPE_CODE,
FTH.TRANSACTION_DATE_ENTERED,
FTH.DATE_EFFECTIVE,
GJB.NAME,
GJH.NAME,
GJB.STATUS,
GJB.DESCRIPTION,
FDP1.PERIOD_NAME,
FDP2.PERIOD_NAME,
GJH.PERIOD_NAME,
GL.CURRENCY_CODE