SQL Query to drill down FA Assets to GL

Posted by

The below given query provides the drill down from the FA Assets mapping to GL with XLA Tables

SELECT
FTH.ASSET_ID, FAB.ASSET_NUMBER,
FTH.TRANSACTION_TYPE_CODE,
FTH.DATE_EFFECTIVE,
FAB.ASSET_TYPE,
GJH.JE_HEADER_ID,
GJH.PERIOD_NAME,
XE.EVENT_ID,
GJH.NAME HEADER_NAME,
GCC.SEGMENT1 BC,
GCC.SEGMENT3 DEPT,
GCC.SEGMENT4 ACCT
FROM
GL_JE_HEADERS GJH,
GL.GL_JE_LINES GJL,
GL.GL_CODE_COMBINATIONS GCC,
GL.GL_PERIODS GLP,
GL_IMPORT_REFERENCES IMP,
XLA_AE_LINES  XAL ,
XLA.XLA_AE_HEADERS XAH,
XLA.XLA_EVENTS XE,
XLA_TRANSACTION_ENTITIES XTE,
FA_TRANSACTION_HEADERS  FTH,
FA_ADDITIONS_B FAB
WHERE 1=1
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJH.JE_SOURCE                    = 'Assets'
and GJH.JE_CATEGORY                  = 'Addition'
AND GJL.JE_HEADER_ID                 = IMP.JE_HEADER_ID
AND GJL.JE_LINE_NUM                  = IMP.JE_LINE_NUM
AND IMP.GL_SL_LINK_ID                = GJH.JE_HEADER_ID
AND IMP.GL_SL_LINK_TABLE             = XAL.GL_SL_LINK_TABLE
AND XAL.APPLICATION_ID               = XAH.APPLICATION_ID
AND XAL.AE_HEADER_ID                 = XAH.AE_HEADER_ID
AND XAH.APPLICATION_ID               = XE.APPLICATION_ID
AND XAH.EVENT_ID                     = XE.EVENT_ID
AND XE.APPLICATION_ID                = XTE.APPLICATION_ID
AND XTE.APPLICATION_ID               = 20004
AND XE.ENTITY_ID                     = XTE.ENTITY_ID
--AND XTE.ENTITY_CODE                  IN ( 'TRANSACTIONS', 'ADJUSTMENTS')
AND XAL.CODE_COMBINATION_ID          = GCC.CODE_COMBINATION_ID
AND XTE.ENTITY_ID                    = XE.ENTITY_ID
AND XTE.SOURCE_ID_INT_1              = FTH.TRANSACTION_HEADER_ID
AND FTH.ASSET_ID                     = FAB.ASSET_ID
--AND FAB.ASSET_NUMBER                 = '1474840'