GL S401 Balance Drilldown Report
SELECT
period_name period,
period_year || substr(effective_period_num, - 2) period_num,
ledger_name ledger_name,
/*rpt_type,
fs_cat_1,
fs_cat_2,
fs_cat_3,*/
ent,
loc,
gac,
lac,
dept,
ico,
prog,
pal,
fut,
account_code_combination acct_cd_comb,
currency_code cy_cd,
gac_description gac_desc,
lac_description lac_desc,
dept_description dept_desc,
prog_description prog_desc,
pal_description pal_desc,
SUM(begin_bal) bb_ent_cy_amt,
SUM(ptd_dr) ptd_ent_dr_cy_amt,
SUM(ptd_cr) ptd_ent_cr_cy_amt,
SUM(ptd) ptd_ent_cy_amt,
SUM(ending_bal) eb_ent_cy_amt,
SUM(begin_bal_func) bb_book_cy_amt,
SUM(ptd_func_dr) ptd_book_dr_cy_amt,
SUM(ptd_func_cr) ptd_book_cr_cy_amt,
SUM(ptd_func) ptd_book_cy_amt,
SUM(ending_bal_func) eb_book_cy_amt,
-- controller,
ccid,
sysdate timestamp
FROM
(SELECT
gb.period_name,
gps.effective_period_num,
gps.period_year,
gl.currency_code,
gl.name ledger_name,
Xglcc.Segment1 ent,
Xglcc.Segment2 Loc,
Xglcc.Segment3 Gac,
Xglcc.Segment4 Lac,
Xglcc.Segment5 dept,
Xglcc.Segment7 ico,
Xglcc.Segment8 prog,
Xglcc.Segment6 pal,
Xglcc.Segment9 fut,
xglcc.concatenated_segments account_code_combination,
(
SELECT
Ffv.Description
FROM
Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE
Ffvs.Flex_Value_Set_Name = 'XXON_GLS3_GAC'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Xglcc.Segment3
) Gac_Description,
(
SELECT
Ffv.Description
FROM
Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE
Ffvs.Flex_Value_Set_Name = 'XXON_GLS4_LAC'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Xglcc.Segment4
) Lac_Description,
(
SELECT
Ffv.Description
FROM
Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE
Ffvs.Flex_Value_Set_Name = 'XXON_GLS5_DEPT'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Xglcc.Segment5
) Dept_Description,
(
SELECT
Ffv.Description
FROM
Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE
Ffvs.Flex_Value_Set_Name = 'XXON_GLS8_PROG'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Xglcc.Segment8
) Prog_Description,
(
SELECT
Ffv.Description
FROM
Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE
Ffvs.Flex_Value_Set_Name = 'XXON_GLS6_PAL'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Xglcc.Segment6
) PAL_Description,
(
CASE
WHEN Gb.Translated_Flag = 'R' THEN SUM(
Nvl(Gb.Begin_Balance_Dr_Beq, 0) - Nvl(Gb.Begin_Balance_Cr_Beq, 0)
) * -1
ELSE SUM(
Nvl(Gb.Begin_Balance_Dr, 0) - Nvl(Gb.Begin_Balance_Cr, 0)
)
END
) Begin_Bal,
(
CASE
WHEN Gb.Translated_Flag = 'R' THEN SUM(Nvl(Gb.Period_Net_Dr_Beq, 0)) * -1
ELSE SUM(Nvl(Gb.Period_Net_Dr, 0))
END
) Ptd_Dr,
(
CASE
WHEN Gb.Translated_Flag = 'R' THEN SUM(Nvl(Gb.Period_Net_Cr_Beq, 0)) * -1
ELSE SUM(Nvl(Gb.Period_Net_Cr, 0))
END
) Ptd_Cr,
(
CASE
WHEN Gb.Translated_Flag = 'R' THEN SUM(
Nvl(Gb.Period_Net_Dr_Beq, 0) - Nvl(Gb.Period_Net_Cr_Beq, 0)
) * -1
ELSE SUM(
Nvl(Gb.Period_Net_Dr, 0) - Nvl(Gb.Period_Net_Cr, 0)
)
END
) Ptd,
(
CASE
WHEN Gb.Translated_Flag = 'R' THEN SUM(
Nvl(Gb.Begin_Balance_Dr_Beq, 0) - Nvl(Gb.Begin_Balance_Cr_Beq, 0) + Nvl(Gb.Period_Net_Dr_Beq, 0) - Nvl(Gb.Period_Net_Cr_Beq, 0)
) * -1
ELSE SUM(
Nvl(Gb.Begin_Balance_Dr, 0) - Nvl(Gb.Begin_Balance_Cr, 0) + Nvl(Gb.Period_Net_Dr, 0) - Nvl(Gb.Period_Net_Cr, 0)
)
END
) Ending_Bal,
(
CASE
WHEN Gb.Translated_Flag = 'R' THEN SUM(
Nvl(Gb.Begin_Balance_Dr_Beq, 0) - Nvl(Gb.Begin_Balance_Cr_Beq, 0)
) * -1
ELSE SUM(
Nvl(Gb.Begin_Balance_Dr, 0) - Nvl(Gb.Begin_Balance_Cr, 0)
)
END
) Begin_Bal_Func,
(
CASE
WHEN Gb.Translated_Flag = 'R' THEN SUM(Nvl(Gb.Period_Net_Dr_Beq, 0)) * -1
ELSE SUM(Nvl(Gb.Period_Net_Dr, 0))
END
) Ptd_Func_Dr,
(
CASE
WHEN Gb.Translated_Flag = 'R' THEN SUM(Nvl(Gb.Period_Net_Cr_Beq, 0)) * -1
ELSE SUM(Nvl(Gb.Period_Net_Cr, 0))
END
) Ptd_Func_Cr,
(
CASE
WHEN Gb.Translated_Flag = 'R' THEN SUM(
Nvl(Gb.Period_Net_Dr_Beq, 0) - Nvl(Gb.Period_Net_Cr_Beq, 0)
) * -1
ELSE SUM(
Nvl(Gb.Period_Net_Dr, 0) - Nvl(Gb.Period_Net_Cr, 0)
)
END
) Ptd_Func,
(
CASE
WHEN Gb.Translated_Flag = 'R' THEN SUM(
Nvl(Gb.Begin_Balance_Dr_Beq, 0) - Nvl(Gb.Begin_Balance_Cr_Beq, 0) + Nvl(Gb.Period_Net_Dr_Beq, 0) - Nvl(Gb.Period_Net_Cr_Beq, 0)
) * -1
ELSE SUM(
Nvl(Gb.Begin_Balance_Dr, 0) - Nvl(Gb.Begin_Balance_Cr, 0) + Nvl(Gb.Period_Net_Dr, 0) - Nvl(Gb.Period_Net_Cr, 0)
)
END
) Ending_Bal_Func,
xglcc.code_combination_id ccid
FROM
gl_period_statuses gps,
gl_code_combinations xglcc,
gl_ledgers gl,
gl_balances gb
WHERE
gps.application_id = 101
AND gps.ledger_id = gl.ledger_id
AND gps.period_name = gb.period_name
AND gl.ledger_id = gb.ledger_id
AND gb.currency_code <> 'STAT'
AND gb.actual_flag = 'A'
AND (
gb.translated_flag = 'R'
OR gb.translated_flag IS NULL
)
AND gb.code_combination_id = xglcc.code_combination_id
AND GB.Period_Name = NVL(:p_period,GB.Period_Name)
AND (gl.name in (:p_ledger) OR 'ALL' IN (:p_ledger||'ALL'))
AND (Xglcc.Segment1 in (:p_company) OR 'ALL' IN (:p_company||'ALL'))
AND (Xglcc.Segment3 in (:p_gac) OR 'ALL' IN (:p_gac||'ALL'))
AND (Xglcc.Segment4 in (:p_lac) OR 'ALL' IN (:p_lac||'ALL'))
AND (Xglcc.Segment5 in (:p_dept) OR 'ALL' IN (:p_dept||'ALL'))
AND (Xglcc.Segment7 in (:p_ico) OR 'ALL' IN (:p_ico||'ALL'))
AND (Xglcc.Segment8 in (:p_prog) OR 'ALL' IN (:p_prog||'ALL'))
AND (Xglcc.Segment2 in (:p_loc) OR 'ALL' IN (:p_loc||'ALL'))
AND (Xglcc.Segment9 in (:p_fut) OR 'ALL' IN (:p_fut||'ALL'))
AND (Xglcc.Segment6 in (:p_bu) OR 'ALL' IN (:p_bu||'ALL'))
AND (Xglcc.Concatenated_Segments in (:p_acct_cd_comb) OR 'ALL' IN (:p_acct_cd_comb||'ALL'))
AND (Gb.Currency_Code in (:p_cy_cd) OR 'ALL' IN (:p_cy_cd||'ALL'))
---AND xglcc.segment1 = 612
--AND xglcc.segment3 = '11142'
GROUP BY
gb.period_name,
gps.effective_period_num,
gps.period_year,
gl.currency_code,
gl.name,
xglcc.segment1,
xglcc.segment2,
xglcc.segment3,
xglcc.segment4,
xglcc.segment5,
xglcc.segment7,
xglcc.segment8,
xglcc.segment6,
xglcc.segment9,
xglcc.concatenated_segments,
gb.translated_flag,
xglcc.code_combination_id
UNION ALL /*foreign currency balances*/
SELECT
gb.period_name,
gps.effective_period_num,
gps.period_year,
gb.currency_code,
gl.name ledger_name,
/*pxglcc.rpt_type,
pxglcc.fs_cat_1,
pxglcc.fs_cat_2,
pxglcc.fs_cat_3,*/
Xglcc.Segment1 ent,
Xglcc.Segment2 Loc,
Xglcc.Segment3 Gac,
Xglcc.Segment4 Lac,
Xglcc.Segment5 Dept,
Xglcc.Segment7 ico,
Xglcc.Segment8 prog,
Xglcc.Segment6 pal,
Xglcc.Segment9 Fut,
xglcc.concatenated_segments account_code_combination,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS3_GAC'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Xglcc.Segment3) Gac_Description,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS4_LAC'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Xglcc.Segment4) Lac_Description,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS5_DEPT'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Xglcc.Segment5) Dept_Description,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS8_PROG'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Xglcc.Segment8) Prog_Description,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS6_PAL'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Xglcc.Segment6) PAL_Description,
SUM(nvl(begin_balance_dr, 0) - nvl(begin_balance_cr, 0)) begin_bal,
SUM(nvl(gb.period_net_dr, 0)) ptd_dr,
SUM(nvl(gb.period_net_cr, 0)) ptd_cr,
SUM(nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0)) ptd,
SUM((nvl(begin_balance_dr, 0) - nvl(begin_balance_cr, 0)) +(nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0))) ending_bal,
SUM(nvl(begin_balance_dr_beq, 0) - nvl(begin_balance_cr_beq, 0)) begin_bal_func,
SUM(nvl(gb.period_net_dr_beq, 0)) ptd_func_dr,
SUM(nvl(gb.period_net_cr_beq, 0)) ptd_func_cr,
SUM(nvl(gb.period_net_dr_beq, 0) - nvl(gb.period_net_cr_beq, 0)) ptd_func,
SUM((nvl(begin_balance_dr_beq, 0) - nvl(begin_balance_cr_beq, 0)) +(nvl(gb.period_net_dr_beq, 0) - nvl(gb.period_net_cr_beq,
0))) ending_bal_func,
xglcc.code_combination_id ccid
FROM
gl_ledgers gl,
gl_period_statuses gps,
gl_code_combinations xglcc,
/* (
SELECT
'BSHEET' rpt_type,
bxglcc.fs_cat_1,
bxglcc.fs_cat_2,
bxglcc.fs_cat_3,
bxglcc.controller,
bxglcc.code_combination_id
FROM
xxon_gl_fs_glcc_attr_bsheet bxglcc
WHERE
bxglcc.gl_account_type IN ( 'A', 'L', 'O' )
UNION ALL
SELECT
'PLOPR' rpt_type,
pxglcc.fs_cat_1,
pxglcc.fs_cat_2,
pxglcc.fs_cat_3,
pxglcc.controller,
pxglcc.code_combination_id
FROM
xxon_gl_fs_glcc_attr_plopr pxglcc
WHERE
pxglcc.gl_account_type IN ( 'E', 'R' )
) pxglcc,*/
gl_balances gb
WHERE 1=1
/*EXISTS (
SELECT
1
FROM
gl_consolidation sgc
WHERE
EXISTS (
SELECT
1
FROM
gl_cons_map_set_v s,
gl_cons_set_assign_v a
WHERE
s.consolidation_set_id = a.consolidation_set_id
AND a.consolidation_id = sgc.consolidation_id
)
AND sgc.from_ledger_id = gl.ledger_id
AND sgc.from_currency_code = 'USD'
AND sgc.name NOT LIKE 'ZZZ%'
AND nvl('', 'N') = 'Y'
UNION ALL
SELECT
1
FROM
dual
WHERE
nvl('', 'N') = 'N'
)*/
AND gps.application_id = 101
AND gps.ledger_id = gl.ledger_id
AND gps.period_name = gb.period_name
AND gb.code_combination_id = xglcc.code_combination_id
-- AND xglcc.code_combination_id = pxglcc.code_combination_id (+)
AND gl.ledger_id = gb.ledger_id
AND gb.currency_code <> 'STAT'
AND gb.actual_flag = 'A'
AND gb.translated_flag = 'R'
AND GB.Period_Name = NVL(:p_period,GB.Period_Name)
AND (gl.name in (:p_ledger) OR 'ALL' IN (:p_ledger||'ALL'))
AND (Xglcc.Segment1 in (:p_company) OR 'ALL' IN (:p_company||'ALL'))
AND (Xglcc.Segment3 in (:p_gac) OR 'ALL' IN (:p_gac||'ALL'))
AND (Xglcc.Segment4 in (:p_lac) OR 'ALL' IN (:p_lac||'ALL'))
AND (Xglcc.Segment5 in (:p_dept) OR 'ALL' IN (:p_dept||'ALL'))
AND (Xglcc.Segment7 in (:p_ico) OR 'ALL' IN (:p_ico||'ALL'))
AND (Xglcc.Segment8 in (:p_prog) OR 'ALL' IN (:p_prog||'ALL'))
AND (Xglcc.Segment2 in (:p_loc) OR 'ALL' IN (:p_loc||'ALL'))
AND (Xglcc.Segment9 in (:p_fut) OR 'ALL' IN (:p_fut||'ALL'))
AND (Xglcc.Segment6 in (:p_bu) OR 'ALL' IN (:p_bu||'ALL'))
AND (Xglcc.Concatenated_Segments in (:p_acct_cd_comb) OR 'ALL' IN (:p_acct_cd_comb||'ALL'))
AND (Gb.Currency_Code in (:p_cy_cd) OR 'ALL' IN (:p_cy_cd||'ALL'))
--AND xglcc.segment1=612
--AND xglcc.segment3='11142'
GROUP BY
gb.period_name,
gps.effective_period_num,
gps.period_year,
gl.name,
/* pxglcc.rpt_type,
pxglcc.fs_cat_1,
pxglcc.fs_cat_2,
pxglcc.fs_cat_3,*/
xglcc.segment1,
xglcc.segment2,
xglcc.segment3,
xglcc.segment4,
xglcc.segment5,
xglcc.segment7,
xglcc.segment8,
xglcc.segment6,
xglcc.segment9,
xglcc.concatenated_segments,
gb.currency_code,
-- pxglcc.controller,
xglcc.code_combination_id
) t
WHERE
1 = 1
GROUP BY
period_name,
effective_period_num,
period_year,
ledger_name,
/*rpt_type,
fs_cat_1,
fs_cat_2,
fs_cat_3,*/
ent,
loc,
gac,
lac,
dept,
ico,
prog,
pal,
fut,
account_code_combination,
currency_code,
gac_description,
lac_description,
dept_description,
prog_description,
pal_description,
-- controller,
ccid
HAVING ( SUM(begin_bal_func) <> 0
OR SUM(ending_bal_func) <> 0
OR SUM(begin_bal) <> 0
OR SUM(ending_bal) <> 0 )
ORDER BY
ent
**SELECT Period_Name Period,
Period_Year || Substr(Effective_Period_Num, -2) Period_Num,
NAME Ledger_Name,
Segment1 Ent,
Segment2 Loc,
Segment3 Gac,
Segment4 Lac,
Segment5 Dept,
Segment7 Ico,
Segment8 Prog,
Segment6 PAL,
Segment9 Fut,
Concatenated_Segments Acct_Cd_Comb,
Currency_Code Cy_Cd,
Gac_Description Gac_Desc,
Lac_Description Lac_Desc,
Dept_Description Dept_Desc,
Prog_Description Proj_Desc,
PAL_Description PAL_Desc,
Bb_Book_Cy_Cd Bb_Book_Cy_Cd,
Ptd_Book_Dr,
Ptd_Book_Cr,
Ptd_Book_Cy_Cd Ptd_Book_Cy_Cd,
Eb_Book_Cy_Cd Eb_Book_Cy_Cd,
Code_Combination_Id Ccid,
sysdate timestamp
FROM (SELECT Gb.Period_Name,
Gps.Effective_Period_Num,
Gps.Period_Year,
-- Gb.Ledger_Name ledger_NAME,
Gl.Name,
Xglcc.Segment1,
Xglcc.Segment2,
Xglcc.Segment3,
Xglcc.Segment4,
Xglcc.Segment5,
Xglcc.Segment7,
Xglcc.Segment8,
Xglcc.Segment6,
Xglcc.Segment9,
Xglcc.Concatenated_Segments,
Gb.Currency_Code,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS3_GAC'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Xglcc.Segment3) Gac_Description,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS4_LAC'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Xglcc.Segment4) Lac_Description,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS5_DEPT'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Xglcc.Segment5) Dept_Description,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS8_PROG'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Xglcc.Segment8) Prog_Description,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS6_PAL'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Xglcc.Segment6) PAL_Description,
SUM(Nvl(Gb.Begin_Balance_Dr, 0) - Nvl(Gb.Begin_Balance_Cr, 0)) Bb_Book_Cy_Cd,
SUM(Nvl(Gb.Period_Net_Dr, 0)) Ptd_Book_Dr,
SUM(Nvl(Gb.Period_Net_Cr, 0)) Ptd_Book_Cr,
SUM(Nvl(Gb.Period_Net_Dr, 0) - Nvl(Gb.Period_Net_Cr, 0)) Ptd_Book_Cy_Cd,
SUM((Nvl(Gb.Begin_Balance_Dr, 0) - Nvl(Gb.Begin_Balance_Cr, 0)) +
(Nvl(Gb.Period_Net_Dr, 0) - Nvl(Gb.Period_Net_Cr, 0))) Eb_Book_Cy_Cd,
-- Pxglcc.Controller,
Xglcc.Code_Combination_Id
FROM
Gl_Period_Statuses Gps,
Gl_Code_Combinations Xglcc,
Gl_Ledgers Gl,
Gl_Balances Gb
WHERE Gps.Application_Id = 101
AND Gl.Ledger_Id = Gb.Ledger_Id
AND Gps.Period_Name = Gb.Period_Name
AND Gps.ledger_id = gl.ledger_id
AND Gb.Code_Combination_Id = Xglcc.Code_Combination_Id
AND gb.period_name= NVL(:p_period,GB.Period_Name)
AND (gl.name in (:p_ledger) OR 'ALL' IN (:p_ledger||'ALL'))
AND (Xglcc.Segment1 in (:p_company) OR 'ALL' IN (:p_company||'ALL'))
AND (Xglcc.Segment3 in (:p_gac) OR 'ALL' IN (:p_gac||'ALL'))
AND (Xglcc.Segment4 in (:p_lac) OR 'ALL' IN (:p_lac||'ALL'))
AND (Xglcc.Segment5 in (:p_dept) OR 'ALL' IN (:p_dept||'ALL'))
AND (Xglcc.Segment7 in (:p_ico) OR 'ALL' IN (:p_ico||'ALL'))
AND (Xglcc.Segment8 in (:p_prog) OR 'ALL' IN (:p_prog||'ALL'))
AND (Xglcc.Segment6 in (:p_bu) OR 'ALL' IN (:p_bu||'ALL'))
AND (Xglcc.Segment2 in (:p_loc) OR 'ALL' IN (:p_loc||'ALL'))
AND (Xglcc.Segment9 in (:p_fut) OR 'ALL' IN (:p_fut||'ALL'))
AND (Xglcc.Concatenated_Segments in (:p_acct_cd_comb) OR 'ALL' IN (:p_acct_cd_comb||'ALL'))
AND (Gb.Currency_Code in (:p_cy_cd) OR 'ALL' IN (:p_cy_cd||'ALL'))
-- AND xglcc.segment1='612'
-- AND xglcc.segment3='61212'*/
AND Gb.Actual_Flag = 'A'
AND Gb.translated_flag IS NULL
GROUP BY Gb.Period_Name,
Gl.Name,
Gps.Effective_Period_Num,
Gps.Period_Year,
Xglcc.Segment1,
Xglcc.Segment2,
Xglcc.Segment3,
Xglcc.Segment4,
Xglcc.Segment5,
Xglcc.Segment7,
Xglcc.Segment8,
Xglcc.Segment6,
Xglcc.Segment9,
Xglcc.Concatenated_Segments,
Gb.Currency_Code,
-- Pxglcc.Controller,
Xglcc.Code_Combination_Id
)
WHERE 1=1
-- %IF ' amp;PARM18' = 'Y' %THENDO;
AND (Bb_Book_Cy_Cd <> 0
OR Ptd_Book_Dr <> 0
OR Ptd_Book_Cr <> 0
OR Ptd_Book_Cy_Cd <> 0
OR Eb_Book_Cy_Cd <> 0)
--%ELSE;
-- AND (Bb_Book_Cy_Cd lt; gt; 0
-- OR Eb_Book_Cy_Cd lt; gt; 0)
--%ENDIF;
No comments:
Post a Comment