Oracle Fusion - GL Trail Balance Query with Cost Center & Natural Account

 select

COST_CENTER,

ACCOUNT,

COST_CENTER_DESC,

ACCOUNT_DESC,

SUM(OPENING_BAL) OPENING_BAL_USD,

SUM(PERIOD_NET_DR) NET_DR_USD,

SUM(PERIOD_NET_CR) NET_CR_USD,

SUM(CLOSING_BAL) CLOSING_BAL_USD

FROM

(

SELECT

CC.SEGMENT3 COST_CENTER,

CC.SEGMENT4 ACCOUNT,

(SELECT

VAL3.DESCRIPTION

FROM

FND_FLEX_VALUES_VL VAL3,

FND_ID_FLEX_SEGMENTS SEG3

WHERE    

SEG3.ID_FLEX_NUM = CC.CHART_OF_ACCOUNTS_ID

       AND VAL3.FLEX_VALUE_SET_ID = SEG3.FLEX_VALUE_SET_ID

       AND SEG3.APPLICATION_ID = 101

       AND SEG3.ID_FLEX_CODE = 'GL#'

       AND SEG3.ENABLED_FLAG = 'Y'

       AND SEG3.APPLICATION_COLUMN_NAME = 'SEGMENT3'

       AND VAL3.FLEX_VALUE = CC.SEGMENT3

) COST_CENTER_DESC,    

(

SELECT

VAL3.DESCRIPTION

FROM

FND_FLEX_VALUES_VL VAL3,

FND_ID_FLEX_SEGMENTS SEG3

WHERE    

SEG3.ID_FLEX_NUM = CC.CHART_OF_ACCOUNTS_ID

       AND VAL3.FLEX_VALUE_SET_ID = SEG3.FLEX_VALUE_SET_ID

       AND SEG3.APPLICATION_ID = 101

       AND SEG3.ID_FLEX_CODE = 'GL#'

       AND SEG3.ENABLED_FLAG = 'Y'

       AND SEG3.APPLICATION_COLUMN_NAME = 'SEGMENT4'

       AND VAL3.FLEX_VALUE = CC.SEGMENT4

) ACCOUNT_DESC,

(CASE WHEN GP.PERIOD_YEAR || LPAD (GP.PERIOD_NUM, 2, 0)=:P_FROM_PERIOD THEN NVL(GLB.BEGIN_BALANCE_DR, 0) - NVL(GLB.BEGIN_BALANCE_CR, 0)

END) OPENING_BAL,

-- NVL(GLB.BEGIN_BALANCE_DR, 0) - NVL(GLB.BEGIN_BALANCE_CR, 0) BEGIN_BALANCE,

GLB.PERIOD_NET_DR,

GLB.PERIOD_NET_CR,

-- (NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0) - NVL(BEGIN_BALANCE_CR, 0) - NVL(PERIOD_NET_CR, 0)) END_BALANCE,

(CASE WHEN GP.PERIOD_YEAR || LPAD (GP.PERIOD_NUM, 2, 0)=:P_TO_PERIOD THEN (NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0) - NVL(BEGIN_BALANCE_CR, 0) - NVL(PERIOD_NET_CR, 0)) 

END) CLOSING_BAL,

GP.PERIOD_YEAR || LPAD (GP.PERIOD_NUM, 2, 0) PERIOD

FROM

    GL_BALANCES      GLB,

    GL_CODE_COMBINATIONS   CC,

    GL_LEDGERS    GLL,

GL_PERIODS GP

WHERE

 GLB.ACTUAL_FLAG = 'A'

AND GLB.CURRENCY_CODE = 'USD'

-- AND GLB.PERIOD_NAME IN

AND GLB.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID

AND CC.SUMMARY_FLAG = 'N'

AND CC.TEMPLATE_ID IS NULL

AND GLL.LEDGER_ID = :P_LEDGER_ID --Ledger Parameter

AND GLL.PERIOD_SET_NAME = GP.PERIOD_SET_NAME

AND GP.ADJUSTMENT_PERIOD_FLAG = 'N'

AND GLB.PERIOD_NAME=GP.PERIOD_NAME

AND GP.PERIOD_YEAR || LPAD (GP.PERIOD_NUM, 2, 0) BETWEEN :P_FROM_PERIOD AND :P_TO_PERIOD    -- From and Period Parameter

AND cc.segment3 BETWEEN NVL (:p_segment3_low, cc.segment3) AND NVL (:p_segment3_high, cc.segment3) -- Cost Center Parameter

AND cc.segment4 BETWEEN NVL (:p_segment4_low, cc.segment4) AND NVL (:p_segment4_high, cc.segment4) -- Account Paramter

)

GROUP BY 

COST_CENTER,

ACCOUNT,

COST_CENTER_DESC,

ACCOUNT_DESC

ORDER BY COST_CENTER,ACCOUNT