This FA Assets As Per Category query displays categorized asset details with financial and accounting data grouped by asset category, helping users analyze asset costs and classifications for better financial reporting.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Categorized asset financial summary.
FAB.ASSET_NUMBER || ' - ' || FATL.DESCRIPTION ASSET_DESCRIPTION,
FAB.SERIAL_NUMBER,
FAB.TAG_NUMBER,
FAB.ASSET_TYPE,
FAB.ASSET_ID,
SUM(FB.COST) COST,
FB.DATE_PLACED_IN_SERVICE DATE_PLACED_IN_SERVICE,
FB.BOOK_TYPE_CODE,
FCB.SEGMENT1 MAJOR_CATEGORY,
FCB.SEGMENT2 MINOR_CATEGORY,
FCB.CATEGORY_TYPE,
FCB.CATEGORY_ID,
FND_FLEX_EXT.GET_SEGS(
'GL',
'GL#',
GCC.CHART_OF_ACCOUNTS_ID,
GCC.CODE_COMBINATION_ID
) ACCOUNT,
NVL(GCC.SEGMENT1, 'None') COMPANY_CODE,
GCC.CODE_COMBINATION_ID,
FMA.ADJUSTMENT_TYPE
FROM
FA_ADDITIONS_TL FATL,
FA_ADDITIONS_B FAB,
FA_ASSET_HISTORY FAH,
FA_BOOKS FB,
FA_CATEGORIES_B FCB,
FA_MC_ADJUSTMENTS FMA,
FA_DISTRIBUTION_HISTORY FDH,
GL_CODE_COMBINATIONS GCC
WHERE
1 = 1
AND FAB.ASSET_ID = FATL.ASSET_ID
AND FAB.ASSET_ID = FAH.ASSET_ID
AND FB.DATE_EFFECTIVE >= FAH.DATE_EFFECTIVE
AND FB.DATE_EFFECTIVE < NVL(FAH.DATE_INEFFECTIVE, SYSDATE)
AND FB.ASSET_ID = FAH.ASSET_ID
AND FB.ASSET_ID = FAB.ASSET_ID
AND FB.PERIOD_COUNTER_FULLY_RETIRED IS NULL
AND FB.DATE_INEFFECTIVE IS NULL
AND FAB.ASSET_CATEGORY_ID = FCB.CATEGORY_ID
AND FMA.ASSET_ID(+) = FDH.ASSET_ID
AND FDH.ASSET_ID(+) = FAB.ASSET_ID
AND FDH.DATE_INEFFECTIVE IS NULL
AND FDH.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID(+)
AND FATL.LANGUAGE = USERENV('LANG')
GROUP BY
FND_FLEX_EXT.GET_SEGS(
'GL',
'GL#',
GCC.CHART_OF_ACCOUNTS_ID,
GCC.CODE_COMBINATION_ID
),
FCB.SEGMENT1,
FCB.SEGMENT2,
FAB.ASSET_NUMBER,
FATL.DESCRIPTION,
FAB.SERIAL_NUMBER,
FAB.TAG_NUMBER,
FB.DATE_PLACED_IN_SERVICE,
FMA.ADJUSTMENT_TYPE,
FB.BOOK_TYPE_CODE,
FCB.CATEGORY_TYPE,
FAB.ASSET_TYPE,
NVL(GCC.SEGMENT1, 'None'),
GCC.CODE_COMBINATION_ID,
FCB.CATEGORY_ID,
FAB.ASSET_ID