This GL Code Combination Descriptions query displays account segment details for each chart of accounts, providing a complete view of the GL structure for financial analysis.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Fetches full chart of accounts with segment descriptions from Oracle Fusion GL.
GL.NAME LEDGER,
FIFST.ID_FLEX_STRUCTURE_NAME FLEX_STRUCTURE_NAME,
FIFST.DESCRIPTION FLEX_STRUCTURE_DESCRIPTION,
-- SEGMENTS 1 to 30 concatenated
DECODE(GCC.SEGMENT1, NULL, NULL, GCC.SEGMENT1 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT1)) ||
DECODE(GCC.SEGMENT2, NULL, NULL, GCC.SEGMENT2 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT2)) ||
DECODE(GCC.SEGMENT3, NULL, NULL, GCC.SEGMENT3 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT3)) ||
DECODE(GCC.SEGMENT4, NULL, NULL, GCC.SEGMENT4 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT4)) ||
DECODE(GCC.SEGMENT5, NULL, NULL, GCC.SEGMENT5 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT5)) ||
DECODE(GCC.SEGMENT6, NULL, NULL, GCC.SEGMENT6 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT6)) ||
DECODE(GCC.SEGMENT7, NULL, NULL, GCC.SEGMENT7 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT7)) ||
DECODE(GCC.SEGMENT8, NULL, NULL, GCC.SEGMENT8 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT8)) ||
DECODE(GCC.SEGMENT9, NULL, NULL, GCC.SEGMENT9 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT9)) ||
DECODE(GCC.SEGMENT10, NULL, NULL, GCC.SEGMENT10 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT10)) ||
DECODE(GCC.SEGMENT11, NULL, NULL, GCC.SEGMENT11 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT11)) ||
DECODE(GCC.SEGMENT12, NULL, NULL, GCC.SEGMENT12 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT12)) ||
DECODE(GCC.SEGMENT13, NULL, NULL, GCC.SEGMENT13 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT13)) ||
DECODE(GCC.SEGMENT14, NULL, NULL, GCC.SEGMENT14 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT14)) ||
DECODE(GCC.SEGMENT15, NULL, NULL, GCC.SEGMENT15 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT15)) ||
DECODE(GCC.SEGMENT16, NULL, NULL, GCC.SEGMENT16 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT16)) ||
DECODE(GCC.SEGMENT17, NULL, NULL, GCC.SEGMENT17 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT17)) ||
DECODE(GCC.SEGMENT18, NULL, NULL, GCC.SEGMENT18 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT18)) ||
DECODE(GCC.SEGMENT19, NULL, NULL, GCC.SEGMENT19 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT19)) ||
DECODE(GCC.SEGMENT20, NULL, NULL, GCC.SEGMENT20 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT20)) ||
DECODE(GCC.SEGMENT21, NULL, NULL, GCC.SEGMENT21 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT21)) ||
DECODE(GCC.SEGMENT22, NULL, NULL, GCC.SEGMENT22 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT22)) ||
DECODE(GCC.SEGMENT23, NULL, NULL, GCC.SEGMENT23 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT23)) ||
DECODE(GCC.SEGMENT24, NULL, NULL, GCC.SEGMENT24 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT24)) ||
DECODE(GCC.SEGMENT25, NULL, NULL, GCC.SEGMENT25 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT25)) ||
DECODE(GCC.SEGMENT26, NULL, NULL, GCC.SEGMENT26 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT26)) ||
DECODE(GCC.SEGMENT27, NULL, NULL, GCC.SEGMENT27 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT27)) ||
DECODE(GCC.SEGMENT28, NULL, NULL, GCC.SEGMENT28 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT28)) ||
DECODE(GCC.SEGMENT29, NULL, NULL, GCC.SEGMENT29 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT29)) ||
DECODE(GCC.SEGMENT30, NULL, NULL, GCC.SEGMENT30 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT30)) ACCOUNT,
DECODE(GCC.SEGMENT1, NULL, NULL, GCC.SEGMENT30 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT1)) SEGMENT1,
DECODE(GCC.SEGMENT2, NULL, NULL, GCC.SEGMENT2 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT2)) SEGMENT2,
DECODE(GCC.SEGMENT3, NULL, NULL, GCC.SEGMENT3 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT3)) SEGMENT3,
DECODE(GCC.SEGMENT4, NULL, NULL, GCC.SEGMENT4 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT4)) SEGMENT4,
DECODE(GCC.SEGMENT5, NULL, NULL, GCC.SEGMENT5 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT5)) SEGMENT5,
DECODE(GCC.SEGMENT6, NULL, NULL, GCC.SEGMENT6 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT6)) SEGMENT6,
DECODE(GCC.SEGMENT7, NULL, NULL, GCC.SEGMENT7 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT7)) SEGMENT7,
DECODE(GCC.SEGMENT8, NULL, NULL, GCC.SEGMENT8 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT8)) SEGMENT8,
DECODE(GCC.SEGMENT9, NULL, NULL, GCC.SEGMENT9 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT9)) SEGMENT9,
DECODE(GCC.SEGMENT10, NULL, NULL, GCC.SEGMENT10 || ' - ' || GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 1, GCC.SEGMENT10)) SEGMENT10,
GCC.ACCOUNT_TYPE,
GCC.START_DATE_ACTIVE,
GCC.END_DATE_ACTIVE,
DECODE(GCC.ENABLED_FLAG, 'N', 'No', 'Y', 'Yes', NULL) ENABLED_FLAG --Indicates if this account combination is enabled.
FROM
GL_CODE_COMBINATIONS GCC,
FND_ID_FLEX_STRUCTURES FIFS,
FND_ID_FLEX_STRUCTURES_TL FIFST,
GL_LEDGERS GL
WHERE
1 = 1
AND GL.CHART_OF_ACCOUNTS_ID = GCC.CHART_OF_ACCOUNTS_ID
AND FIFS.ID_FLEX_CODE = 'GL#' --pulling the structure metadata for the Accounting Flexfield
AND FIFS.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID
AND FIFS.APPLICATION_ID = FIFST.APPLICATION_ID
AND FIFS.ID_FLEX_CODE = FIFST.ID_FLEX_CODE
AND FIFS.ID_FLEX_NUM = FIFST.ID_FLEX_NUM
AND FIFST.LANGUAGE = USERENV('LANG')
AND GCC.ENABLED_FLAG = 'Y';