select * from (
select code_combination_id, PERIOD_NUM, nvl(a3.ENTERED_YEAR_TO_DATE,0) - nvl(a4.ENTERED_YEAR_TO_DATE,0) amount from (
SELECT bal.code_combination_id,
( bal.begin_balance_dr_beq
- bal.begin_balance_cr_beq
+ bal.period_net_dr_beq
- bal.period_net_cr_beq)
ENTERED_YEAR_TO_DATE
,PERIOD_NUM
,apps.gl_flexfields_pkg.get_concat_description
(cc.chart_of_accounts_id,
bal.code_combination_id
)code_comb_desc,
(SELECT kfv.concatenated_segments
FROM gl_code_combinations_kfv kfv
WHERE code_combination_id = bal.code_combination_id)segments
FROM GL_CODE_COMBINATIONS cc, GL_BALANCES bal, GL_LEDGERS gl
WHERE (cc.SEGMENT1 >= :P_From_Sec OR :P_From_Sec IS NULL)
AND (cc.SEGMENT1 <= :P_To_Sec OR :P_To_Sec IS NULL)
AND (cc.SEGMENT2 >= :P_From_Dep OR :P_From_Dep IS NULL)
AND (cc.SEGMENT2 <= :P_To_Dep OR :P_To_Dep IS NULL)
AND (cc.SEGMENT3 >= :P_From_Acc1 OR :P_From_Acc1 IS NULL)
AND (cc.SEGMENT3 <= :P_To_Acc1 OR :P_To_Acc1 IS NULL)
AND (cc.SEGMENT4 >= :P_From_Sub_Acc OR :P_From_Sub_Acc IS NULL)
AND (cc.SEGMENT4 <= :P_To_Sub_Acc OR :P_To_Sub_Acc IS NULL)
AND bal.ACTUAL_FLAG = NVL ( :P_ACTUAL_FLAG, 'A')
AND bal.CODE_COMBINATION_ID = cc.CODE_COMBINATION_ID
AND bal.LEDGER_ID = gl.LEDGER_ID
AND cc.CHART_OF_ACCOUNTS_ID = 50320
AND bal.LEDGER_ID = 2091
AND cc.TEMPLATE_ID IS NULL
AND :P_YTD_PTD = 'YTD'
AND PERIOD_NAME IN (SELECT XXI.PERIOD_NAME FROM XX_I_GL_PERIODS_I XXI WHERE PERIOD_ID BETWEEN :P_FROM_PERIOD AND :P_TO_PERIOD)
GROUP BY
PERIOD_NUM,
( bal.begin_balance_dr_beq
- bal.begin_balance_cr_beq
+ bal.period_net_dr_beq
- bal.period_net_cr_beq)
,apps.gl_flexfields_pkg.get_concat_description
(cc.chart_of_accounts_id,
bal.code_combination_id
),bal.code_combination_id
) A3
full join (
SELECT bal.code_combination_id,
( bal.begin_balance_dr_beq
- bal.begin_balance_cr_beq
+ bal.period_net_dr_beq
- bal.period_net_cr_beq)
ENTERED_YEAR_TO_DATE
,PERIOD_NUM
,apps.gl_flexfields_pkg.get_concat_description
(cc.chart_of_accounts_id,
bal.code_combination_id
)code_comb_desc,
(SELECT kfv.concatenated_segments
FROM gl_code_combinations_kfv kfv
WHERE code_combination_id = bal.code_combination_id)segments
FROM GL_CODE_COMBINATIONS cc, GL_BALANCES bal, GL_LEDGERS gl
WHERE (cc.SEGMENT1 >= :P_From_Sec OR :P_From_Sec IS NULL)
AND (cc.SEGMENT1 <= :P_To_Sec OR :P_To_Sec IS NULL)
AND (cc.SEGMENT2 >= :P_From_Dep OR :P_From_Dep IS NULL)
AND (cc.SEGMENT2 <= :P_To_Dep OR :P_To_Dep IS NULL)
AND (cc.SEGMENT3 >= :P_From_Acc1 OR :P_From_Acc1 IS NULL)
AND (cc.SEGMENT3 <= :P_To_Acc1 OR :P_To_Acc1 IS NULL)
AND (cc.SEGMENT4 >= :P_From_Sub_Acc OR :P_From_Sub_Acc IS NULL)
AND (cc.SEGMENT4 <= :P_To_Sub_Acc OR :P_To_Sub_Acc IS NULL)
AND bal.ACTUAL_FLAG = NVL ( :P_ACTUAL_FLAG, 'A')
AND bal.CODE_COMBINATION_ID = cc.CODE_COMBINATION_ID
AND bal.LEDGER_ID = gl.LEDGER_ID
AND cc.CHART_OF_ACCOUNTS_ID = 50320
AND bal.LEDGER_ID = 2091
AND cc.TEMPLATE_ID IS NULL
AND :P_YTD_PTD = 'YTD'
AND PERIOD_NAME IN (SELECT XXI.PERIOD_NAME FROM XX_I_GL_PERIODS_I XXI WHERE PERIOD_ID BETWEEN :P_FROM_PERIOD_2 AND :P_TO_PERIOD_2)
GROUP BY
PERIOD_NUM,
( bal.begin_balance_dr_beq
- bal.begin_balance_cr_beq
+ bal.period_net_dr_beq
- bal.period_net_cr_beq)
,apps.gl_flexfields_pkg.get_concat_description
(cc.chart_of_accounts_id,
bal.code_combination_id
),bal.code_combination_id
) A4
using(code_combination_id, PERIOD_NUM)
)
pivot (
SUM(AMOUNT) as YTD_DIFF
for PERIOD_NUM in (
'1' as Jan, '2' as Feb, '3' as Mar,
'4' as Apr, '5' as May, '6' as Jun,
'7' as Jul, '8' as Aug, '9' as Sep,
'10' as Oct, '11' as Nov, '12' as Dec, '13' as Adj
)
);
