select
'Code Combinations'||'|'||'Opening Balance'||'|'||'Dr'||'|'||'Cr'||'|'||'Net'||'|'||'Closing Balance'||'|Company|Company desc|Department|Department desc|Account|Account desc|Sub-Account|Sub-Account desc|Product|Product desc|Segment6|Segment6 desc|Segment7|Segment7 desc'
from dual
union all
select * from(
select /*+ no_merge(x)*/
x.concatenated_segments||'|'||
sum(x.begin_balance)||'|'||
sum(x.period_dr)||'|'||
sum(x.period_cr)||'|'||
sum(x.period_net)||'|'||
sum(x.end_balance)||'|'||x.segment1||'|'||
replace(xxen_util.segment_description(x.segment1,'SEGMENT1',x.chart_of_accounts_id),'|','`^')||'|'||x.segment2||'|'||
replace(xxen_util.segment_description(x.segment2,'SEGMENT2',x.chart_of_accounts_id),'|','`^')||'|'||x.segment3||'|'||
replace(xxen_util.segment_description(x.segment3,'SEGMENT3',x.chart_of_accounts_id),'|','`^')||'|'||x.segment4||'|'||
replace(xxen_util.segment_description(x.segment4,'SEGMENT4',x.chart_of_accounts_id),'|','`^')||'|'||x.segment5||'|'||
replace(xxen_util.segment_description(x.segment5,'SEGMENT5',x.chart_of_accounts_id),'|','`^')||'|'||x.segment6||'|'||
replace(xxen_util.segment_description(x.segment6,'SEGMENT6',x.chart_of_accounts_id),'|','`^')||'|'||x.segment7||'|'||
replace(xxen_util.segment_description(x.segment7,'SEGMENT7',x.chart_of_accounts_id),'|','`^')
from
(
select /*+ leading(xwsv gcck) index(gb gl_balances_n1)*/
gcck.concatenated_segments,gcck.chart_of_accounts_id,
gcck.segment1,gcck.segment2,gcck.segment3,gcck.segment4,gcck.segment5,gcck.segment6,gcck.segment7,
nvl(begin_balance_dr, 0)- nvl(begin_balance_cr,0) begin_balance,
nvl(period_net_dr,0) period_dr,
nvl(period_net_cr,0) period_cr,
nvl(period_net_dr, 0)- nvl(period_net_cr, 0) period_net,
nvl(begin_balance_dr, 0)+ nvl(period_net_dr, 0) - nvl(begin_balance_cr, 0) - nvl(period_net_cr, 0) end_balance
from
gl_code_combinations_kfv gcck,
gl_balances gb,
gl_ledgers gl
where
1=1 and
gcck.chart_of_accounts_id=gl.chart_of_accounts_id and
gcck.summary_flag='N' and
gb.template_id is null and
gb.ledger_id=gl.ledger_id and
gcck.code_combination_id=gb.code_combination_id and
gb.translated_flag is null
) x
where
x.period_net<>0 and
3=3
group by
x.segment1,
xxen_util.segment_description(x.segment1,'SEGMENT1',x.chart_of_accounts_id),x.segment2,
xxen_util.segment_description(x.segment2,'SEGMENT2',x.chart_of_accounts_id),x.segment3,
xxen_util.segment_description(x.segment3,'SEGMENT3',x.chart_of_accounts_id),x.segment4,
xxen_util.segment_description(x.segment4,'SEGMENT4',x.chart_of_accounts_id),x.segment5,
xxen_util.segment_description(x.segment5,'SEGMENT5',x.chart_of_accounts_id),x.segment6,
xxen_util.segment_description(x.segment6,'SEGMENT6',x.chart_of_accounts_id),x.segment7,
xxen_util.segment_description(x.segment7,'SEGMENT7',x.chart_of_accounts_id),
x.concatenated_segments
order by
x.concatenated_segments
) |
