ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 1B7EF52ED2F74515B1E94381B96608E7Y select x.ledger, x.ledger_id, x.concatenated_segments code_combination, x.code_combination_id, &segment_columns sum(x.begin_balance)*nvl(:multiplier,1) opening_balance, sum(x.period_dr)*nvl(:multiplier,1) period_dr, sum(x.period_cr)*nvl(:multiplier,1) period_cr, sum(x.period_net)*nvl(:multiplier,1) period_net, sum(x.end_balance)*nvl(:multiplier,1) closing_balance, case when nvl(fnd_profile.value('XXEN_FSG_DRILLDOWN_TO_SAME_WORKBOOK'),'Y')='N' then '=dd' else '=dds' end||'("GJ","'||x.ledger_id||','||:period_name||','||:balance_type||','||:currency_code||','||:actual_flag||','||nvl(:budget_name,'')||','||nvl(:encumbrance_type,'')||','||nvl(:journal_source,'')||','||nvl(:journal_category,'')||','||x.code_combination_id||'")' drill_to_journal from ( -- GL_BALANCES path (standard) select gl.name ledger, gl.ledger_id, gcck.concatenated_segments, gcck.code_combination_id, &segment_columns_inner &balance_columns from gl_ledgers gl, gl_code_combinations_kfv gcck, gl_balances gb where nvl(:journal_source,'%')='%' and nvl(:journal_category,'%')='%' and instr(:balance_type,'JED')=0 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 ((:balance_type='YTD' and 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)))) or (:balance_type='FYS' and gb.period_name=xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id))) or (:balance_type='FYE' and gb.period_name=xxen_fsg.last_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id))) or (:balance_type not in ('YTD','FYS','FYE') and gb.period_name=:period_name)) and ((:translated_flag in ('T','S') and gb.translated_flag is null and gb.currency_code=case when :translated_flag='T' and :currency_code<>'STAT' then gl.currency_code when :translated_flag='S' then 'STAT' else :currency_code end) or (:translated_flag not in ('T','S') and nvl(gb.translated_flag,decode(gb.currency_code,gl.currency_code,'R','X'))='R' and gb.currency_code=:currency_code)) and (:budget_name is null or :actual_flag<>'B' or gb.budget_version_id in (select gbv.budget_version_id from gl_budget_versions gbv where gbv.budget_name=:budget_name)) and (:encumbrance_type is null or :actual_flag<>'E' or gb.encumbrance_type_id in (select get.encumbrance_type_id from gl_encumbrance_types get where get.encumbrance_type=:encumbrance_type)) and (xxen_util.has_flex_value_security<>'Y' or gl_security_pkg.validate_access(null,gcck.code_combination_id)='TRUE') and (:restrict_ccids is null or gcck.code_combination_id in (select xrti.id1 from xxen_report_temp_ids xrti)) union all -- GL_JE_LINES path (journal source/category filter or JED balance type) select gl.name ledger, gl.ledger_id, gcck.concatenated_segments, gcck.code_combination_id, &segment_columns_inner2 0 begin_balance, nvl(gjl.accounted_dr,0) period_dr, nvl(gjl.accounted_cr,0) period_cr, nvl(gjl.accounted_dr,0)-nvl(gjl.accounted_cr,0) period_net, nvl(gjl.accounted_dr,0)-nvl(gjl.accounted_cr,0) end_balance from gl_ledgers gl, gl_je_headers gjh, gl_je_lines gjl, gl_code_combinations_kfv gcck where (nvl(:journal_source,'%')<>'%' or nvl(:journal_category,'%')<>'%' or instr(:balance_type,'JED')>0) and gl.ledger_id=:ledger_id and gjh.ledger_id=gl.ledger_id and gjl.ledger_id=gl.ledger_id and gjh.je_header_id=gjl.je_header_id and gjl.code_combination_id=gcck.code_combination_id and gcck.chart_of_accounts_id=gl.chart_of_accounts_id and gcck.summary_flag='N' and gjh.actual_flag=:actual_flag and gjh.status=case when :balance_type='JEDU' then 'U' else 'P' end and gjh.currency_code=:currency_code and ((instr(:balance_type,'JED')>0 and gjl.effective_date between (select gp.start_date from gl_periods gp, gl_ledgers gl3 where gl3.ledger_id=:ledger_id and gp.period_set_name=gl3.period_set_name and gp.period_type=gl3.accounted_period_type and gp.period_name=:period_name) and (select gp.end_date from gl_periods gp, gl_ledgers gl3 where gl3.ledger_id=:ledger_id and gp.period_set_name=gl3.period_set_name and gp.period_type=gl3.accounted_period_type and gp.period_name=:period_name)) or (:balance_type='FYS' and gjh.period_name=xxen_fsg.first_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id))) or (:balance_type='FYE' and gjh.period_name=xxen_fsg.last_period_name(:period_name,(select gl2.name from gl_ledgers gl2 where gl2.ledger_id=:ledger_id))) or (:balance_type not in ('FYS','FYE') and instr(:balance_type,'JED')=0 and gjh.period_name=:period_name)) and (nvl(:journal_source,'%')='%' or gjh.je_source in (select gjsv.je_source_name from gl_je_sources_vl gjsv where gjsv.user_je_source_name=:journal_source)) and (nvl(:journal_category,'%')='%' or gjh.je_category in (select gjcv.je_category_name from gl_je_categories_vl gjcv where gjcv.user_je_category_name=:journal_category)) and (:budget_name is null or :actual_flag<>'B' or gjh.budget_version_id in (select gbv.budget_version_id from gl_budget_versions gbv where gbv.budget_name=:budget_name)) and (:encumbrance_type is null or :actual_flag<>'E' or gjh.encumbrance_type_id in (select get.encumbrance_type_id from gl_encumbrance_types get where get.encumbrance_type=:encumbrance_type)) and (xxen_util.has_flex_value_security<>'Y' or gl_security_pkg.validate_access(null,gcck.code_combination_id)='TRUE') and (:restrict_ccids is null or gcck.code_combination_id in (select xrti.id1 from xxen_report_temp_ids xrti)) ) x where x.begin_balance<>0 or x.period_dr<>0 or x.period_cr<>0 or x.end_balance<>0 group by x.ledger, x.ledger_id, x.concatenated_segments, x.code_combination_id &segment_group_by order by x.concatenated_segments #,##0.00;[Red]-#,##0.00 US GL Balance Drilldown Shows GL balance details by code combination. Uses gl_balances for standard queries, gl_je_lines when filtering by journal source/category or using JED balance types. Enginatics &balance_columns &segment_columns &segment_columns_inner &segment_columns_inner2 &segment_group_by 1 -104 &balance_columns select case when :balance_type in ('PTD','CTD','FYS','FYE') then q'[ 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 begin_balance, 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 period_dr, 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 period_cr, 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 period_net, 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 end_balance]' when :balance_type='QTD' then q'[ 0 begin_balance, 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 period_dr, 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 period_cr, 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 period_net, 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 end_balance]' when :balance_type='PJTD' then q'[ 0 begin_balance, 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 period_dr, 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 period_cr, 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 period_net, 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_balance]' when :balance_type='YTD' then q'[ 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 begin_balance, 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 period_dr, 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 period_cr, 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 period_net, 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 end_balance]' end from dual Char x US Balance Columns 2 -103 &segment_group_by select distinct ',x.'||lower(fifsv.application_column_name)||',x.'||lower(fifsv.application_column_name)||'_desc' text, min(fifsv.id_flex_num) over (partition by fifsv.application_column_name) min_id_flex_num, min(fifsv.segment_num) over (partition by fifsv.application_column_name) min_segment_num from fnd_id_flex_segments_vl 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 Char x US Segment Group By 3 -102 &segment_columns_inner2 select distinct 'gcck.'||lower(fifsv.application_column_name)||',xxen_util.segment_description(gcck.'||lower(fifsv.application_column_name)||','''||fifsv.application_column_name||''',gcck.chart_of_accounts_id) '||lower(fifsv.application_column_name)||'_desc,' text, min(fifsv.id_flex_num) over (partition by fifsv.application_column_name) min_id_flex_num, min(fifsv.segment_num) over (partition by fifsv.application_column_name) min_segment_num from fnd_id_flex_segments_vl 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 Char x US Segment Columns Inner 2 4 -101 &segment_columns_inner select distinct 'gcck.'||lower(fifsv.application_column_name)||',xxen_util.segment_description(gcck.'||lower(fifsv.application_column_name)||','''||fifsv.application_column_name||''',gcck.chart_of_accounts_id) '||lower(fifsv.application_column_name)||'_desc,' text, min(fifsv.id_flex_num) over (partition by fifsv.application_column_name) min_id_flex_num, min(fifsv.segment_num) over (partition by fifsv.application_column_name) min_segment_num from fnd_id_flex_segments_vl 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 Char x US Segment Columns Inner 5 -100 &segment_columns select distinct 'x.'||lower(fifsv.application_column_name)||',x.'||lower(fifsv.application_column_name)||'_desc,' text, min(fifsv.id_flex_num) over (partition by fifsv.application_column_name) min_id_flex_num, min(fifsv.segment_num) over (partition by fifsv.application_column_name) min_segment_num from fnd_id_flex_segments_vl 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 Char x US Segment Columns 6 -50 :restrict_ccids Char US Restrict CCIDs through GTT 7 10 :ledger_id Number US Ledger ID 8 20 :period_name Char US Period Name 9 30 :balance_type Char PTD US Balance Type 10 40 :currency_code Char US Currency Code 11 50 :translated_flag Char T US Translated Flag 12 60 :actual_flag Char A US Actual Flag 13 70 :budget_name Char US Budget Name 14 80 :encumbrance_type Char US Encumbrance Type 15 90 :journal_source Char US Journal Source 16 100 :journal_category Char US Journal Category 17 110 :multiplier Number 1 US Multiplier 7DEBBBC02A2C1E6ADAF732D355E3A13C GL Balance Drilldown MRUGESH.POOJARY 6 CLOSING_BALANCE 1 CODE_COMBINATION 7 DRILL_TO_JOURNAL 2 OPENING_BALANCE 4 PERIOD_CR 3 PERIOD_DR 5 PERIOD_NET S Site MRUGESH.POOJARY 7DEBBBC02A2C1E6ADAF732D355E3A13C