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