ENGINATICS
SYSTEM_ADMINISTRATOR
SYSADMIN
41D704687ED1CF8FE06362FB0905D782
select
gl.name ledger,
gl.ledger_id,
gcck.concatenated_segments,
gcck.code_combination_id,
&segment_columns
-- Balance amounts based on balance type and translated flag
sum(
case when :balance_type in ('PTD','CTD','FYS','FYE') then
case when :translated_flag in ('T','S') then
nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0)
when :translated_flag='E' then
case when gb.actual_flag='A' then
decode(gb.translated_flag,'R', nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0),
nvl(gb.begin_balance_dr_beq,0) - nvl(gb.begin_balance_cr_beq,0))
else nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0) end
when :translated_flag='C' then
case when gb.actual_flag='A' then nvl(gb.begin_balance_dr_beq,0) - nvl(gb.begin_balance_cr_beq,0)
else nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0) end
end
when :balance_type='YTD' then
case when :translated_flag in ('T','S') then
decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0), 0)
when :translated_flag='E' then
case when gb.actual_flag='A' then
decode(gb.translated_flag, 'R',
decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0), 0),
decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_dr_beq,0) - nvl(gb.begin_balance_cr_beq,0), 0))
else decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0), 0) end
when :translated_flag='C' then
case when gb.actual_flag='A' then
decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_dr_beq,0) - nvl(gb.begin_balance_cr_beq,0), 0)
else decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0), 0) end
end
when :balance_type in ('QTD','PJTD') then 0
end
) * nvl(:multiplier,1) opening_balance,
-- Period DR
sum(
case when :balance_type in ('PTD','CTD','FYS','FYE') then
case when :translated_flag in ('T','S') then nvl(gb.period_net_dr,0)
when :translated_flag='E' then
case when gb.actual_flag='A' then decode(gb.translated_flag, 'R', nvl(gb.period_net_dr,0), nvl(gb.period_net_dr_beq,0))
else nvl(gb.period_net_dr,0) end
when :translated_flag='C' then
case when gb.actual_flag='A' then nvl(gb.period_net_dr_beq,0) else nvl(gb.period_net_dr,0) end
end
when :balance_type='YTD' then
case when :translated_flag in ('T','S') then
decode(gb.period_name, :period_name, nvl(gb.period_net_dr,0) + nvl(gb.begin_balance_dr,0), 0) -
decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_dr,0), 0)
when :translated_flag='E' then
case when gb.actual_flag='A' then
decode(gb.translated_flag, 'R',
decode(gb.period_name, :period_name, nvl(gb.period_net_dr,0) + nvl(gb.begin_balance_dr,0), 0) - decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_dr,0), 0),
decode(gb.period_name, :period_name, nvl(gb.period_net_dr_beq,0) + nvl(gb.begin_balance_dr_beq,0), 0) - decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_dr_beq,0), 0))
else decode(gb.period_name, :period_name, nvl(gb.period_net_dr,0) + nvl(gb.begin_balance_dr,0), 0) - decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_dr,0), 0) end
when :translated_flag='C' then
case when gb.actual_flag='A' then
decode(gb.period_name, :period_name, nvl(gb.period_net_dr_beq,0) + nvl(gb.begin_balance_dr_beq,0), 0) - decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_dr_beq,0), 0)
else decode(gb.period_name, :period_name, nvl(gb.period_net_dr,0) + nvl(gb.begin_balance_dr,0), 0) - decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_dr,0), 0) end
end
when :balance_type='QTD' then
case when :translated_flag in ('T','S') then nvl(gb.quarter_to_date_dr,0) + nvl(gb.period_net_dr,0)
when :translated_flag='E' then
case when gb.actual_flag='A' then decode(gb.translated_flag, 'R', nvl(gb.quarter_to_date_dr,0) + nvl(gb.period_net_dr,0), nvl(gb.quarter_to_date_dr_beq,0) + nvl(gb.period_net_dr_beq,0))
else nvl(gb.quarter_to_date_dr,0) + nvl(gb.period_net_dr,0) end
when :translated_flag='C' then
case when gb.actual_flag='A' then nvl(gb.quarter_to_date_dr_beq,0) + nvl(gb.period_net_dr_beq,0)
else nvl(gb.quarter_to_date_dr,0) + nvl(gb.period_net_dr,0) end
end
when :balance_type='PJTD' then
case when :translated_flag in ('T','S') then nvl(gb.project_to_date_dr,0) + nvl(gb.period_net_dr,0)
when :translated_flag='E' then
case when gb.actual_flag='A' then decode(gb.translated_flag, 'R', nvl(gb.project_to_date_dr,0) + nvl(gb.period_net_dr,0), nvl(gb.project_to_date_dr_beq,0) + nvl(gb.period_net_dr_beq,0))
else nvl(gb.project_to_date_dr,0) + nvl(gb.period_net_dr,0) end
when :translated_flag='C' then
case when gb.actual_flag='A' then nvl(gb.project_to_date_dr_beq,0) + nvl(gb.period_net_dr_beq,0)
else nvl(gb.project_to_date_dr,0) + nvl(gb.period_net_dr,0) end
end
end
) * nvl(:multiplier,1) period_dr,
-- Period CR
sum(
case when :balance_type in ('PTD','CTD','FYS','FYE') then
case when :translated_flag in ('T','S') then nvl(gb.period_net_cr,0)
when :translated_flag='E' then
case when gb.actual_flag='A' then decode(gb.translated_flag, 'R', nvl(gb.period_net_cr,0), nvl(gb.period_net_cr_beq,0))
else nvl(gb.period_net_cr,0) end
when :translated_flag='C' then
case when gb.actual_flag='A' then nvl(gb.period_net_cr_beq,0) else nvl(gb.period_net_cr,0) end
end
when :balance_type='YTD' then
case when :translated_flag in ('T','S') then
decode(gb.period_name, :period_name, nvl(gb.period_net_cr,0) + nvl(gb.begin_balance_cr,0), 0) -
decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_cr,0), 0)
when :translated_flag='E' then
case when gb.actual_flag='A' then
decode(gb.translated_flag, 'R',
decode(gb.period_name, :period_name, nvl(gb.period_net_cr,0) + nvl(gb.begin_balance_cr,0), 0) - decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_cr,0), 0),
decode(gb.period_name, :period_name, nvl(gb.period_net_cr_beq,0) + nvl(gb.begin_balance_cr_beq,0), 0) - decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_cr_beq,0), 0))
else decode(gb.period_name, :period_name, nvl(gb.period_net_cr,0) + nvl(gb.begin_balance_cr,0), 0) - decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_cr,0), 0) end
when :translated_flag='C' then
case when gb.actual_flag='A' then
decode(gb.period_name, :period_name, nvl(gb.period_net_cr_beq,0) + nvl(gb.begin_balance_cr_beq,0), 0) - decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_cr_beq,0), 0)
else decode(gb.period_name, :period_name, nvl(gb.period_net_cr,0) + nvl(gb.begin_balance_cr,0), 0) - decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_cr,0), 0) end
end
when :balance_type='QTD' then
case when :translated_flag in ('T','S') then nvl(gb.quarter_to_date_cr,0) + nvl(gb.period_net_cr,0)
when :translated_flag='E' then
case when gb.actual_flag='A' then decode(gb.translated_flag, 'R', nvl(gb.quarter_to_date_cr,0) + nvl(gb.period_net_cr,0), nvl(gb.quarter_to_date_cr_beq,0) + nvl(gb.period_net_cr_beq,0))
else nvl(gb.quarter_to_date_cr,0) + nvl(gb.period_net_cr,0) end
when :translated_flag='C' then
case when gb.actual_flag='A' then nvl(gb.quarter_to_date_cr_beq,0) + nvl(gb.period_net_cr_beq,0)
else nvl(gb.quarter_to_date_cr,0) + nvl(gb.period_net_cr,0) end
end
when :balance_type='PJTD' then
case when :translated_flag in ('T','S') then nvl(gb.project_to_date_cr,0) + nvl(gb.period_net_cr,0)
when :translated_flag='E' then
case when gb.actual_flag='A' then decode(gb.translated_flag, 'R', nvl(gb.project_to_date_cr,0) + nvl(gb.period_net_cr,0), nvl(gb.project_to_date_cr_beq,0) + nvl(gb.period_net_cr_beq,0))
else nvl(gb.project_to_date_cr,0) + nvl(gb.period_net_cr,0) end
when :translated_flag='C' then
case when gb.actual_flag='A' then nvl(gb.project_to_date_cr_beq,0) + nvl(gb.period_net_cr_beq,0)
else nvl(gb.project_to_date_cr,0) + nvl(gb.period_net_cr,0) end
end
end
) * nvl(:multiplier,1) period_cr,
-- Period Net (calculated as Dr - Cr for simplicity, can also use direct period_net columns)
sum(
case when :balance_type in ('PTD','CTD','FYS','FYE') then
case when :translated_flag in ('T','S') then nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0)
when :translated_flag='E' then
case when gb.actual_flag='A' then decode(gb.translated_flag, 'R', nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0), nvl(gb.period_net_dr_beq,0) - nvl(gb.period_net_cr_beq,0))
else nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0) end
when :translated_flag='C' then
case when gb.actual_flag='A' then nvl(gb.period_net_dr_beq,0) - nvl(gb.period_net_cr_beq,0)
else nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0) end
end
when :balance_type='YTD' then
case when :translated_flag in ('T','S') then
decode(gb.period_name, :period_name, nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0) + nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0), 0) -
decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0), 0)
when :translated_flag='E' then
case when gb.actual_flag='A' then
decode(gb.translated_flag, 'R',
decode(gb.period_name, :period_name, nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0) + nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0), 0) - decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0), 0),
decode(gb.period_name, :period_name, nvl(gb.period_net_dr_beq,0) - nvl(gb.period_net_cr_beq,0) + nvl(gb.begin_balance_dr_beq,0) - nvl(gb.begin_balance_cr_beq,0), 0) - decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_dr_beq,0) - nvl(gb.begin_balance_cr_beq,0), 0))
else decode(gb.period_name, :period_name, nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0) + nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0), 0) - decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0), 0) end
when :translated_flag='C' then
case when gb.actual_flag='A' then
decode(gb.period_name, :period_name, nvl(gb.period_net_dr_beq,0) - nvl(gb.period_net_cr_beq,0) + nvl(gb.begin_balance_dr_beq,0) - nvl(gb.begin_balance_cr_beq,0), 0) - decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_dr_beq,0) - nvl(gb.begin_balance_cr_beq,0), 0)
else decode(gb.period_name, :period_name, nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0) + nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0), 0) - decode(gb.period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)), nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0), 0) end
end
when :balance_type='QTD' then
case when :translated_flag in ('T','S') then nvl(gb.quarter_to_date_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.quarter_to_date_cr,0) - nvl(gb.period_net_cr,0)
when :translated_flag='E' then
case when gb.actual_flag='A' then decode(gb.translated_flag, 'R', nvl(gb.quarter_to_date_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.quarter_to_date_cr,0) - nvl(gb.period_net_cr,0), nvl(gb.quarter_to_date_dr_beq,0) + nvl(gb.period_net_dr_beq,0) - nvl(gb.quarter_to_date_cr_beq,0) - nvl(gb.period_net_cr_beq,0))
else nvl(gb.quarter_to_date_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.quarter_to_date_cr,0) - nvl(gb.period_net_cr,0) end
when :translated_flag='C' then
case when gb.actual_flag='A' then nvl(gb.quarter_to_date_dr_beq,0) + nvl(gb.period_net_dr_beq,0) - nvl(gb.quarter_to_date_cr_beq,0) - nvl(gb.period_net_cr_beq,0)
else nvl(gb.quarter_to_date_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.quarter_to_date_cr,0) - nvl(gb.period_net_cr,0) end
end
when :balance_type='PJTD' then
case when :translated_flag in ('T','S') then nvl(gb.project_to_date_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.project_to_date_cr,0) - nvl(gb.period_net_cr,0)
when :translated_flag='E' then
case when gb.actual_flag='A' then decode(gb.translated_flag, 'R', nvl(gb.project_to_date_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.project_to_date_cr,0) - nvl(gb.period_net_cr,0), nvl(gb.project_to_date_dr_beq,0) + nvl(gb.period_net_dr_beq,0) - nvl(gb.project_to_date_cr_beq,0) - nvl(gb.period_net_cr_beq,0))
else nvl(gb.project_to_date_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.project_to_date_cr,0) - nvl(gb.period_net_cr,0) end
when :translated_flag='C' then
case when gb.actual_flag='A' then nvl(gb.project_to_date_dr_beq,0) + nvl(gb.period_net_dr_beq,0) - nvl(gb.project_to_date_cr_beq,0) - nvl(gb.period_net_cr_beq,0)
else nvl(gb.project_to_date_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.project_to_date_cr,0) - nvl(gb.period_net_cr,0) end
end
end
) * nvl(:multiplier,1) period_net,
-- Closing Balance (Opening + Period Net)
sum(
case when :balance_type in ('PTD','CTD','FYS','FYE') then
case when :translated_flag in ('T','S') then
nvl(gb.begin_balance_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.begin_balance_cr,0) - nvl(gb.period_net_cr,0)
when :translated_flag='E' then
case when gb.actual_flag='A' then
decode(gb.translated_flag,'R', nvl(gb.begin_balance_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.begin_balance_cr,0) - nvl(gb.period_net_cr,0),
nvl(gb.begin_balance_dr_beq,0) + nvl(gb.period_net_dr_beq,0) - nvl(gb.begin_balance_cr_beq,0) - nvl(gb.period_net_cr_beq,0))
else nvl(gb.begin_balance_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.begin_balance_cr,0) - nvl(gb.period_net_cr,0) end
when :translated_flag='C' then
case when gb.actual_flag='A' then nvl(gb.begin_balance_dr_beq,0) + nvl(gb.period_net_dr_beq,0) - nvl(gb.begin_balance_cr_beq,0) - nvl(gb.period_net_cr_beq,0)
else nvl(gb.begin_balance_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.begin_balance_cr,0) - nvl(gb.period_net_cr,0) end
end
when :balance_type='YTD' then
case when :translated_flag in ('T','S') then
decode(gb.period_name, :period_name, nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0) + nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0), 0)
when :translated_flag='E' then
decode(gb.translated_flag, 'R',
decode(gb.period_name, :period_name, nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0) + nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0), 0),
decode(gb.period_name, :period_name, nvl(gb.period_net_dr_beq,0) - nvl(gb.period_net_cr_beq,0) + nvl(gb.begin_balance_dr_beq,0) - nvl(gb.begin_balance_cr_beq,0), 0))
when :translated_flag='C' then
case when gb.actual_flag='A' then
decode(gb.period_name, :period_name, nvl(gb.period_net_dr_beq,0) - nvl(gb.period_net_cr_beq,0) + nvl(gb.begin_balance_dr_beq,0) - nvl(gb.begin_balance_cr_beq,0), 0)
else decode(gb.period_name, :period_name, nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0) + nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0), 0) end
end
when :balance_type='QTD' then
case when :translated_flag in ('T','S') then nvl(gb.quarter_to_date_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.quarter_to_date_cr,0) - nvl(gb.period_net_cr,0)
when :translated_flag='E' then
case when gb.actual_flag='A' then decode(gb.translated_flag, 'R', nvl(gb.quarter_to_date_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.quarter_to_date_cr,0) - nvl(gb.period_net_cr,0), nvl(gb.quarter_to_date_dr_beq,0) + nvl(gb.period_net_dr_beq,0) - nvl(gb.quarter_to_date_cr_beq,0) - nvl(gb.period_net_cr_beq,0))
else nvl(gb.quarter_to_date_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.quarter_to_date_cr,0) - nvl(gb.period_net_cr,0) end
when :translated_flag='C' then
case when gb.actual_flag='A' then nvl(gb.quarter_to_date_dr_beq,0) + nvl(gb.period_net_dr_beq,0) - nvl(gb.quarter_to_date_cr_beq,0) - nvl(gb.period_net_cr_beq,0)
else nvl(gb.quarter_to_date_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.quarter_to_date_cr,0) - nvl(gb.period_net_cr,0) end
end
when :balance_type='PJTD' then
case when :translated_flag in ('T','S') then nvl(gb.project_to_date_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.project_to_date_cr,0) - nvl(gb.period_net_cr,0)
when :translated_flag='E' then
case when gb.actual_flag='A' then decode(gb.translated_flag, 'R', nvl(gb.project_to_date_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.project_to_date_cr,0) - nvl(gb.period_net_cr,0), nvl(gb.project_to_date_dr_beq,0) + nvl(gb.period_net_dr_beq,0) - nvl(gb.project_to_date_cr_beq,0) - nvl(gb.period_net_cr_beq,0))
else nvl(gb.project_to_date_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.project_to_date_cr,0) - nvl(gb.period_net_cr,0) end
when :translated_flag='C' then
case when gb.actual_flag='A' then nvl(gb.project_to_date_dr_beq,0) + nvl(gb.period_net_dr_beq,0) - nvl(gb.project_to_date_cr_beq,0) - nvl(gb.period_net_cr_beq,0)
else nvl(gb.project_to_date_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.project_to_date_cr,0) - nvl(gb.period_net_cr,0) end
end
end
) * nvl(:multiplier,1) closing_balance,
-- Drill to Journal
case when nvl(fnd_profile.value('XXEN_FSG_DRILLDOWN_TO_SAME_WORKBOOK'), 'Y')='N' then '=dd' else '=dds' end
||'("GJ","'||gl.ledger_id||','||:period_name||','||:balance_type||','||:currency_code||','||:actual_flag||','||:budget_name||','||:encumbrance_type||','||:journal_source||','||:journal_category||','||gcck.code_combination_id||'")' drill_to_journal
from
gl_ledgers gl,
gl_code_combinations_kfv gcck,
gl_balances gb
where
1=1 and
gl.ledger_id=:ledger_id and
gcck.chart_of_accounts_id=gl.chart_of_accounts_id and
gcck.summary_flag='N' and
gb.code_combination_id=gcck.code_combination_id and
gb.ledger_id=gl.ledger_id and
gb.template_id is null and
gb.actual_flag=:actual_flag and
&gl_flex_value_security
&period_filter
¤cy_filter
&budget_filter
&encumbrance_filter
having (
sum(case when :balance_type in ('PTD','CTD','FYS','FYE') then nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0) else 0 end) <> 0 or
sum(case when :balance_type in ('PTD','CTD','FYS','FYE') then nvl(gb.period_net_dr,0) else 0 end) <> 0 or
sum(case when :balance_type in ('PTD','CTD','FYS','FYE') then nvl(gb.period_net_cr,0) else 0 end) <> 0 or
sum(case when :balance_type in ('PTD','CTD','FYS','FYE') then nvl(gb.begin_balance_dr,0) + nvl(gb.period_net_dr,0) - nvl(gb.begin_balance_cr,0) - nvl(gb.period_net_cr,0) else 0 end) <> 0 or
1=1
)
group by
gl.name,
gl.ledger_id,
gcck.concatenated_segments,
gcck.code_combination_id
&segment_group_by
order by
gcck.concatenated_segments
Y
#,##0.00;[Red]-#,##0.00
US
GL Balance (Drilldown) -CAI
** This report is used by the GL Financial Statement and Drilldown report to show Balance details by Code Combination. **
Shows opening balance, period Dr/Cr/Net, and closing balance for each code combination. Supports drill to journals for further analysis.
Please enter Ledger ID, Period, and Balance Type parameters
Enginatics
&budget_filter
¤cy_filter
&encumbrance_filter
&gl_flex_value_security
&period_filter
&segment_columns
&segment_group_by
1=1
1
-50
1=1
gb.code_combination_id in (select xrti.id1 from xxen_report_temp_ids xrti)
Char
Y
US
Restrict CCIDs through GTT
2
-40
&gl_flex_value_security
select 'gl_security_pkg.validate_access(null,gcck.code_combination_id)=''TRUE'' and' from dual where xxen_util.has_flex_value_security='Y'
Char
x
US
GL Flex Value Security
3
10
1=1
gl.ledger_id=:ledger_id
Number
US
Ledger ID
4
&segment_columns
select distinct
'gcck.'||lower(fifsv.application_column_name)||' "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||'",'||chr(10)||
'xxen_util.segment_description(gcck.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', gcck.chart_of_accounts_id'||
(
select
', gcck.'||lower(fifs.application_column_name)
from
fnd_id_flex_segments fifs
where
fifsv.application_id=fifs.application_id and
fifsv.id_flex_code=fifs.id_flex_code and
fifsv.id_flex_num=fifs.id_flex_num and
fifsv.enabled_flag=fifs.enabled_flag and
fifs.flex_value_set_id=(select ffvs.parent_flex_value_set_id from fnd_flex_value_sets ffvs where fifsv.flex_value_set_id=ffvs.flex_value_set_id)
)||')'||' "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length-5)||' desc",' text,
min(fifsv.id_flex_num) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) min_id_flex_num,
min(fifsv.segment_num) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) min_segment_num
from
(select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv
where
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.ledger_id=:ledger_id)
order by
min_id_flex_num,
min_segment_num
US
Ledger ID
5
&segment_group_by
select distinct
',gcck.'||lower(fifsv.application_column_name)||','||chr(10)||
'xxen_util.segment_description(gcck.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', gcck.chart_of_accounts_id'||
(
select
', gcck.'||lower(fifs.application_column_name)
from
fnd_id_flex_segments fifs
where
fifsv.application_id=fifs.application_id and
fifsv.id_flex_code=fifs.id_flex_code and
fifsv.id_flex_num=fifs.id_flex_num and
fifsv.enabled_flag=fifs.enabled_flag and
fifs.flex_value_set_id=(select ffvs.parent_flex_value_set_id from fnd_flex_value_sets ffvs where fifsv.flex_value_set_id=ffvs.flex_value_set_id)
)||')' text,
min(fifsv.id_flex_num) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) min_id_flex_num,
min(fifsv.segment_num) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) min_segment_num
from
(select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv
where
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.ledger_id=:ledger_id)
order by
min_id_flex_num,
min_segment_num
US
Ledger ID
6
20
:period_name
Char
US
Period Name
7
30
&period_filter
select
case when :balance_type = 'YTD' then 'gb.period_name in (:period_name, xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id))) and'
when :balance_type = 'FYS' then 'gb.period_name = xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)) and'
when :balance_type = 'FYE' then 'gb.period_name = xxen_fsg.last_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id)) and'
when :balance_type in ('PTD','QTD','PJTD') then 'gb.period_name = :period_name and'
else ''
end
from dual
LOV custom
select 'PTD' value, 'Period to Date' description from dual union all
select 'YTD' value, 'Year to Date' description from dual union all
select 'QTD' value, 'Quarter to Date' description from dual union all
select 'PJTD' value, 'Project to Date' description from dual union all
select 'FYS' value, 'Fiscal Year Start' description from dual union all
select 'FYE' value, 'Fiscal Year End' description from dual
PTD
US
Balance Type
8
40
:translated_flag
Char
T
US
Translated Flag
T=Translated, S=STAT, E=Entered, C=Converted
9
50
¤cy_filter
select
case when :translated_flag in ('T','S') then 'gb.translated_flag is null and gb.currency_code = '''||
case when :translated_flag='T' and :currency_code<>'STAT' then (select gl.currency_code from gl_ledgers gl where gl.ledger_id=:ledger_id)
when :translated_flag='S' then 'STAT'
else :currency_code end||''' and'
when :translated_flag in ('E','C') then 'nvl(gb.translated_flag,decode(gb.currency_code,'''||(select gl.currency_code from gl_ledgers gl where gl.ledger_id=:ledger_id)||''',''R'',''X''))=''R'' and gb.currency_code = :currency_code and'
else ''
end
from dual
Char
US
Currency Code
10
60
:actual_flag
LOV custom
select 'A' value, 'Actual' description from dual union all
select 'B' value, 'Budget' description from dual union all
select 'E' value, 'Encumbrance' description from dual
A
US
Actual Flag
A=Actual, B=Budget, E=Encumbrance
11
70
&budget_filter
select
case when :budget_name is not null and :actual_flag='B' then
'gb.budget_version_id in (select gbv.budget_version_id from gl_budget_versions gbv where gbv.budget_name='''||:budget_name||''') and'
else ''
end
from dual
Char
US
Budget Name
12
80
&encumbrance_filter
select
case when :encumbrance_type is not null and :actual_flag='E' then
'gb.encumbrance_type_id in (select get.encumbrance_type_id from gl_encumbrance_types get where get.encumbrance_type='''||:encumbrance_type||''') and'
else ''
end
from dual
Char
US
Encumbrance Type
13
90
:journal_source
Char
US
Journal Source
14
100
:journal_category
Char
US
Journal Category
15
110
:multiplier
Number
1
US
Multiplier
GL Balance Drilldown
MRUGESH.POOJARY
6
CLOSING_BALANCE
CODE_COMBINATION_ID
1
CONCATENATED_SEGMENTS
7
DRILL_TO_JOURNAL
LEDGER
LEDGER_ID
2
OPENING_BALANCE
4
PERIOD_CR
3
PERIOD_DR
5
PERIOD_NET
S
Site
MRUGESH.POOJARY
GL Balance Drilldown