GL Trial Balance - Detail

Description

Categories: Enginatics, R12 only
Repository: Github

Imported from Concurrent Program
Description: Detail Trial Balance (XML)
Application: General Ledger
Source: Trial Balance - Detail (XML)
Short Name: GLTRBALD
DB package:

Copy

select
u.ledger_name,
u.currency_code,
&lp_pivot_segment_name pivot_segment,
&pivot_and_account_segment
u.concatenated_segments account,
&parent_acct_desc
u.begin_balance_dr,
u.begin_balance_cr,
u.begin_balance,
u.period_dr,
u.period_cr,
u.period_net,
u.end_balance_dr,
u.end_balance_cr,
u.end_balance,
&segment_columns2
&dff_cols2
xxen_util.meaning(u.gl_account_type,'ACCOUNT_TYPE',0) account_type
from
(
select
max(glr.target_ledger_name) ledger_name,
gb.currency_code,
max(gcck.gl_account_type) gl_account_type,
&parent_acct_display max(gcck.concatenated_segments) concatenated_segments,
&segment_columns
decode(:p_type,'PTD', sum(decode(:p_currency_type,'T', nvl(begin_balance_dr,0),'S', nvl(begin_balance_dr,0),'E',decode(gb.translated_flag,'R', nvl(begin_balance_dr,0), nvl(begin_balance_dr_beq,0)),'C', nvl(begin_balance_dr_beq,0))),'PJTD', decode(:p_currency_type,'T', 0,'S', 0,'E', 0,'C',0),'YTD', decode(:p_currency_type,'T', sum(decode(gb.period_name,:p_first_period_name,(nvl(begin_balance_dr,0)),0)),'S', sum(decode(gb.period_name,:p_first_period_name,(nvl(begin_balance_dr,0)),0)),'E', sum(decode(gb.translated_flag,'R', decode(gb.period_name,:p_first_period_name, nvl(begin_balance_dr,0),0), decode(gb.period_name,:p_first_period_name, nvl(begin_balance_dr_beq,0),0))),'C', sum(decode(gb.period_name,:p_first_period_name,nvl(begin_balance_dr_beq,0),0)))) begin_balance_dr,
decode(:p_type,'PTD', sum(decode(:p_currency_type,'T', nvl(begin_balance_cr,0),'S', nvl(begin_balance_cr,0),'E',decode(gb.translated_flag,'R', nvl(begin_balance_cr,0), nvl(begin_balance_cr_beq,0)),'C', nvl(begin_balance_cr_beq,0))),'PJTD', decode(:p_currency_type,'T', 0,'S', 0,'E', 0,'C',0),'YTD', decode(:p_currency_type,'T', sum(decode(gb.period_name,:p_first_period_name,(nvl(begin_balance_cr,0)),0)),'S', sum(decode(gb.period_name,:p_first_period_name,(nvl(begin_balance_cr,0)),0)),'E', sum(decode(gb.translated_flag,'R', decode(gb.period_name,:p_first_period_name, nvl(begin_balance_cr,0),0), decode(gb.period_name,:p_first_period_name, nvl(begin_balance_cr_beq,0),0))),'C', sum(decode(gb.period_name,:p_first_period_name,nvl(begin_balance_cr_beq,0),0)))) begin_balance_cr,
decode(:p_type,'PTD', sum(decode(:p_currency_type,'T', nvl(begin_balance_dr,0)-nvl(begin_balance_cr,0),'S',nvl(begin_balance_dr,0)-nvl(begin_balance_cr,0),'E',decode(gb.translated_flag,'R', nvl(begin_balance_dr,0)-nvl(begin_balance_cr,0), nvl(begin_balance_dr_beq,0)- nvl(begin_balance_cr_beq,0)),'C', nvl(begin_balance_dr_beq,0)- nvl(begin_balance_cr_beq,0))),'PJTD', decode(:p_currency_type,'T', 0,'S', 0,'E', 0,'C',0),'YTD', decode(:p_currency_type,'T',sum(decode(gb.period_name, :p_first_period_name,(nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0)),0)),'S', sum(decode(gb.period_name, :p_first_period_name,(nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0)),0)),'E', sum(decode(gb.translated_flag,'R', decode(gb.period_name, :p_first_period_name, nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0),0), decode(gb.period_name, :p_first_period_name, nvl(begin_balance_dr_beq,0)- nvl(begin_balance_cr_beq,0),0))),'C', sum(decode(gb.period_name,:p_first_period_name,(nvl(begin_balance_dr_beq,0)- nvl(begin_balance_cr_beq,0)),0)))) begin_balance,
decode(:p_type,'PTD', sum(decode(:p_currency_type,'T', nvl(period_net_dr,0),'S', nvl(period_net_dr,0),'E', decode(gb.translated_flag,'R', nvl(period_net_dr,0),nvl(period_net_dr_beq,0)),'C', nvl(period_net_dr_beq,0))),'PJTD', sum(decode(:p_currency_type,'T', nvl(project_to_date_dr,0) + nvl(period_net_dr,0),'S', nvl(project_to_date_dr,0) + nvl(period_net_dr,0),'E', decode(gb.translated_flag,'R', nvl(project_to_date_dr,0) + nvl(period_net_dr,0), nvl(project_to_date_dr_beq,0) + nvl(period_net_dr_beq,0)),'C', nvl(project_to_date_dr_beq,0) + nvl(period_net_dr_beq,0))),'YTD', decode(:p_currency_type,'T',sum(decode(gb.period_name, :p_period_name, nvl(period_net_dr,0) + nvl(begin_balance_dr,0),0) - decode(gb.period_name, :p_first_period_name, nvl(begin_balance_dr,0),0)),'S', sum(decode(gb.period_name, :p_period_name, nvl(period_net_dr,0) + nvl(begin_balance_dr,0),0) -decode(gb.period_name, :p_first_period_name, nvl(begin_balance_dr,0),0)),'E', sum(decode(gb.translated_flag,'R', decode(gb.period_name, :p_period_name, nvl(period_net_dr,0)+ nvl(begin_balance_dr,0),0) -decode(gb.period_name, :p_first_period_name, nvl(begin_balance_dr,0),0), decode(gb.period_name, :p_period_name, nvl(period_net_dr_beq,0)+nvl(begin_balance_dr_beq,0),0) - decode(gb.period_name, :p_first_period_name,nvl(begin_balance_dr_beq,0),0))),'C', sum(decode(gb.period_name, :p_period_name, nvl(period_net_dr_beq,0) + nvl(begin_balance_dr_beq,0),0) - decode(gb.period_name, :p_first_period_name, nvl(begin_balance_dr_beq,0),0)))) period_dr,
decode(:p_type,'PTD', sum(decode(:p_currency_type,'T', nvl(period_net_cr,0),'S', nvl(period_net_cr,0),'E', decode(gb.translated_flag,'R', nvl(period_net_cr,0),nvl(period_net_cr_beq,0)),'C', nvl(period_net_cr_beq,0))),'PJTD', sum(decode(:p_currency_type,'T', nvl(project_to_date_cr,0) + nvl(period_net_cr,0),'S', nvl(project_to_date_cr,0) + nvl(period_net_cr,0),'E', decode(gb.translated_flag,'R', nvl(project_to_date_cr,0) + nvl(period_net_cr,0), nvl(project_to_date_cr_beq,0) + nvl(period_net_cr_beq,0)),'C', nvl(project_to_date_cr_beq,0) + nvl(period_net_cr_beq,0))),'YTD', decode(:p_currency_type,'T',sum(decode(gb.period_name, :p_period_name, nvl(period_net_cr,0) + nvl(begin_balance_cr,0),0) - decode(gb.period_name, :p_first_period_name, nvl(begin_balance_cr,0),0)),'S', sum(decode(gb.period_name, :p_period_name, nvl(period_net_cr,0) + nvl(begin_balance_cr,0),0) -decode(gb.period_name, :p_first_period_name, nvl(begin_balance_cr,0),0)),'E', sum(decode(gb.translated_flag,'R', decode(gb.period_name, :p_period_name, nvl(period_net_cr,0)+ nvl(begin_balance_cr,0),0) -decode(gb.period_name, :p_first_period_name, nvl(begin_balance_cr,0),0), decode(gb.period_name, :p_period_name, nvl(period_net_cr_beq,0)+nvl(begin_balance_cr_beq,0),0) - decode(gb.period_name, :p_first_period_name,nvl(begin_balance_cr_beq,0),0))),'C', sum(decode(gb.period_name, :p_period_name, nvl(period_net_cr_beq,0) + nvl(begin_balance_cr_beq,0),0) - decode(gb.period_name, :p_first_period_name, nvl(begin_balance_cr_beq,0),0)))) period_cr,
decode(:p_type,'PTD', sum(decode(:p_currency_type,'T', nvl(period_net_dr,0)-nvl(period_net_cr,0),'S', nvl(period_net_dr,0)- nvl(period_net_cr,0),'E', decode(gb.translated_flag,'R', nvl(period_net_dr,0)- nvl(period_net_cr,0), nvl(period_net_dr_beq,0)- nvl(period_net_cr_beq,0)),'C', nvl(period_net_dr_beq,0)- nvl(period_net_cr_beq,0))),'PJTD', sum(decode(:p_currency_type,'T', nvl(project_to_date_dr,0)+ nvl(period_net_dr,0) - nvl(project_to_date_cr,0)- nvl(period_net_cr,0),'S', nvl(project_to_date_dr,0)+ nvl(period_net_dr,0) - nvl(project_to_date_cr,0)- nvl(period_net_cr,0),'E', decode(gb.translated_flag,'R', nvl(project_to_date_dr,0)+ nvl(period_net_dr,0) - nvl(project_to_date_cr,0)- nvl(period_net_cr,0), nvl(project_to_date_dr_beq,0)+ nvl(period_net_dr_beq,0) - nvl(project_to_date_cr_beq,0)- nvl(period_net_cr_beq,0)),'C', nvl(project_to_date_dr_beq,0)+ nvl(period_net_dr_beq,0)- nvl(project_to_date_cr_beq,0) - nvl(period_net_cr_beq,0))),'YTD', decode(:p_currency_type,'T', sum( decode(gb.period_name,:p_period_name, nvl(period_net_dr,0)- nvl(period_net_cr,0)+ nvl(begin_balance_dr,0)- nvl(begin_balance_cr,0),0) - decode(gb.period_name,:p_first_period_name, nvl(begin_balance_dr,0)- nvl(begin_balance_cr,0),0)),'S', sum(decode(gb.period_name,:p_period_name, nvl(period_net_dr,0)-nvl(period_net_cr,0)+nvl(begin_balance_dr,0)-nvl(begin_balance_cr,0),0) - decode(gb.period_name,:p_first_period_name, nvl(begin_balance_dr,0)- nvl(begin_balance_cr,0),0)),'E', sum(decode(gb.translated_flag,'R', decode(gb.period_name,:p_period_name, nvl(period_net_dr,0) - nvl(period_net_cr,0)+ nvl(begin_balance_dr,0)- nvl(begin_balance_cr,0),0) - decode(gb.period_name,:p_first_period_name, nvl(begin_balance_dr,0)- nvl(begin_balance_cr,0),0), decode(gb.period_name,:p_period_name, nvl(period_net_dr_beq,0)- nvl(period_net_cr_beq,0) + nvl(begin_balance_dr_beq,0) - nvl(begin_balance_cr_beq,0),0) - decode(gb.period_name,:p_first_period_name, nvl(begin_balance_dr_beq,0)- nvl(begin_balance_cr_beq,0 ),0))),'C', sum( decode(gb.period_name,:p_period_name, nvl(period_net_dr_beq,0)- nvl(period_net_cr_beq,0)+ nvl(begin_balance_dr_beq,0) - nvl(begin_balance_cr_beq,0),0)- decode(gb.period_name,:p_first_period_name, nvl(begin_balance_dr_beq,0)-nvl(begin_balance_cr_beq,0),0)))) period_net,
decode(:p_type,'PTD', sum(decode(:p_currency_type,'T', nvl(begin_balance_dr,0)+ nvl(period_net_dr,0),'S', nvl(begin_balance_dr,0) + nvl(period_net_dr,0),'E',decode(gb.translated_flag,'R', nvl(begin_balance_dr,0)+ nvl(period_net_dr,0),nvl(begin_balance_dr_beq,0) + nvl(period_net_dr_beq,0)),'C', nvl(begin_balance_dr_beq,0) + nvl(period_net_dr_beq,0))),'PJTD', sum(decode(:p_currency_type,'T', nvl(project_to_date_dr,0) + nvl(period_net_dr,0),'S', nvl(project_to_date_dr,0) + nvl(period_net_dr,0),'E', decode(gb.translated_flag,'R', nvl(project_to_date_dr,0)+ nvl(period_net_dr,0) ,nvl(project_to_date_dr_beq,0) + nvl(period_net_dr_beq,0)),'C', nvl(project_to_date_dr_beq,0) + nvl(period_net_dr_beq,0))),'YTD', decode(:p_currency_type,'T',sum(decode(gb.period_name, :p_period_name, nvl(period_net_dr,0)+nvl(begin_balance_dr,0),0)),'S', sum(decode(gb.period_name, :p_period_name, nvl(period_net_dr,0)+ nvl(begin_balance_dr,0),0)),'E', sum(decode(gb.translated_flag,'R', decode(gb.period_name, :p_period_name, nvl(period_net_dr,0)+ nvl(begin_balance_dr,0),0),decode(gb.period_name, :p_period_name, nvl(period_net_dr_beq,0)+ nvl(begin_balance_dr_beq,0),0))),'C', sum(decode(gb.period_name, :p_period_name, nvl(period_net_dr_beq,0) + nvl(begin_balance_dr_beq,0),0)))) end_balance_dr,
decode(:p_type,'PTD', sum(decode(:p_currency_type,'T', nvl(begin_balance_cr,0)+ nvl(period_net_cr,0),'S', nvl(begin_balance_cr,0) + nvl(period_net_cr,0),'E',decode(gb.translated_flag,'R', nvl(begin_balance_cr,0)+ nvl(period_net_cr,0),nvl(begin_balance_cr_beq,0) + nvl(period_net_cr_beq,0)),'C', nvl(begin_balance_cr_beq,0) + nvl(period_net_cr_beq,0))),'PJTD', sum(decode(:p_currency_type,'T', nvl(project_to_date_cr,0) + nvl(period_net_cr,0),'S', nvl(project_to_date_cr,0) + nvl(period_net_cr,0),'E', decode(gb.translated_flag,'R', nvl(project_to_date_cr,0)+ nvl(period_net_cr,0) ,nvl(project_to_date_cr_beq,0) + nvl(period_net_cr_beq,0)),'C', nvl(project_to_date_cr_beq,0) + nvl(period_net_cr_beq,0))),'YTD', decode(:p_currency_type,'T',sum(decode(gb.period_name, :p_period_name, nvl(period_net_cr,0)+nvl(begin_balance_cr,0),0)),'S', sum(decode(gb.period_name, :p_period_name, nvl(period_net_cr,0)+ nvl(begin_balance_cr,0),0)),'E', sum(decode(gb.translated_flag,'R', decode(gb.period_name, :p_period_name, nvl(period_net_cr,0)+ nvl(begin_balance_cr,0),0),decode(gb.period_name, :p_period_name, nvl(period_net_cr_beq,0)+ nvl(begin_balance_cr_beq,0),0))),'C', sum(decode(gb.period_name, :p_period_name, nvl(period_net_cr_beq,0) + nvl(begin_balance_cr_beq,0),0)))) end_balance_cr,
decode(:p_type,'PTD', sum(decode(:p_currency_type,'T', nvl(begin_balance_dr,0)+ nvl(period_net_dr,0)- nvl(begin_balance_cr,0) - nvl(period_net_cr,0),'S', nvl(begin_balance_dr,0)+ nvl(period_net_dr,0) - nvl(begin_balance_cr,0) - nvl(period_net_cr,0),'E', decode(gb.translated_flag,'R', nvl(begin_balance_dr,0) + nvl(period_net_dr,0)- nvl(begin_balance_cr,0) - nvl(period_net_cr,0),nvl(begin_balance_dr_beq,0) + nvl(period_net_dr_beq,0)- nvl(begin_balance_cr_beq,0) - nvl(period_net_cr_beq,0) ),'C', nvl(begin_balance_dr_beq,0)+ nvl(period_net_dr_beq,0)- nvl(begin_balance_cr_beq,0) - nvl(period_net_cr_beq,0))),'PJTD', sum(decode(:p_currency_type,'T', nvl(project_to_date_dr,0)+ nvl(period_net_dr,0) - nvl(project_to_date_cr,0) - nvl(period_net_cr,0),'S', nvl(project_to_date_dr,0) + nvl(period_net_dr,0) - nvl(project_to_date_cr,0) - nvl(period_net_cr,0),'E', decode(gb.translated_flag,'R', nvl(project_to_date_dr,0) + nvl(period_net_dr,0)- nvl(project_to_date_cr,0) - nvl(period_net_cr,0), nvl(project_to_date_dr_beq,0) + nvl(period_net_dr_beq,0) - nvl(project_to_date_cr_beq,0) - nvl(period_net_cr_beq,0) ),'C', nvl(project_to_date_dr_beq,0) + nvl(period_net_dr_beq,0) - nvl(project_to_date_cr_beq,0) - nvl(period_net_cr_beq,0)) ),'YTD', decode(:p_currency_type,'T', sum(decode(gb.period_name, :p_period_name, nvl(period_net_dr,0 ) - nvl(period_net_cr,0) + nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0),0)),'S', sum(decode(gb.period_name, :p_period_name, nvl(period_net_dr,0)- nvl(period_net_cr,0) + nvl(begin_balance_dr,0)- nvl(begin_balance_cr,0),0)),'E', sum(decode(gb.translated_flag,'R', decode(gb.period_name, :p_period_name, nvl(period_net_dr,0)- nvl(period_net_cr,0)+ nvl(begin_balance_dr,0) - nvl(begin_balance_cr, 0 ), 0 ), decode(gb.period_name, :p_period_name, nvl(period_net_dr_beq, 0 ) - nvl(period_net_cr_beq,0)+ nvl(begin_balance_dr_beq,0)- nvl(begin_balance_cr_beq,0),0) ) ),'C', sum(decode(gb.period_name,:p_period_name, nvl(period_net_dr_beq,0) - nvl(period_net_cr_beq,0) + nvl(begin_balance_dr_beq,0) - nvl(begin_balance_cr_beq,0),0)))) end_balance,
&dff_cols
max(gcck.chart_of_accounts_id) chart_of_accounts_id,
&parent_id_col gcck.code_combination_id
from
gl_ledgers gl,
gl_ledger_set_assignments glsa,
gl_ledger_relationships glr,
gl_balances gb,
gl_code_combinations_kfv gcck
&parent_hierarchy_from
where
1=1 and
gl.ledger_id=:p_ledger_id and
gl.ledger_id=glsa.ledger_set_id(+) and
glr.target_currency_code=:p_ledger_currency and
gl.ledger_id=glr.source_ledger_id and
glr.source_ledger_id=glr.target_ledger_id and
nvl(glsa.ledger_id,gl.ledger_id)=gb.ledger_id and
gb.code_combination_id=gcck.code_combination_id and
nvl(:p_entered_currency,'?')=nvl(:p_entered_currency,'?') and
gb.period_name in (:p_period_name, decode(:p_type,'PTD', :p_period_name,'PJTD', :p_period_name,'YTD', :p_first_period_name)) and
gb.actual_flag='A' and
gcck.chart_of_accounts_id=:p_chart_of_accounts_id and
gcck.summary_flag='N' and
gcck.template_id is null
&parent_hierarchy_where
group by
gb.ledger_id,
gb.currency_code,
&dff_cols_group
&parent_pivot_group
&parent_group_by gcck.code_combination_id
) u
where
nvl(u.begin_balance,0)!=0 or nvl(u.period_net,0)!=0 or nvl(u.end_balance,0)!=0
order by
u.ledger_name,
u.currency_code,
pivot_segment_value,
account_segment,
&segment_columns3
u.concatenated_segments

Parameter Name

SQL text

Validation

Ledger/Ledger Set

LOV Oracle

Ledger Currency

LOV Oracle

Currency Type

LOV Oracle

Entered Currency

LOV Oracle

Pivot Segment

select
''''||fifsv.form_left_prompt||''''
from
fnd_id_flex_segments_vl fifsv
where
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num=:p_chart_of_accounts_id and
fifsv.segment_num=:p_pagebreak_seg_num

LOV Oracle

Pivot Segment Low

select
'gcck.'||fifsv.application_column_name||'>= '''||:p_low_pb_seg||''''
from
fnd_id_flex_segments_vl fifsv
where 
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num=:p_chart_of_accounts_id and
fifsv.segment_num=:p_pagebreak_seg_num and 
fifsv.enabled_flag='Y'

LOV

Pivot Segment High

select
'gcck.'||fifsv.application_column_name||'<= '''||:p_high_pb_seg||''''
from
fnd_id_flex_segments_vl fifsv
where 
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num=:p_chart_of_accounts_id and
fifsv.segment_num=:p_pagebreak_seg_num and 
fifsv.enabled_flag='Y'

LOV

Period

LOV Oracle

Amount Type

LOV Oracle

Active Accounts Only

gcck.enabled_flag='Y' and
not exists (
select null
from
fnd_id_flex_segments fifs,
fnd_flex_values ffv
where
fifs.id_flex_code='GL#' and
fifs.application_id=101 and
fifs.id_flex_num=gcck.chart_of_accounts_id and
fifs.flex_value_set_id=ffv.flex_value_set_id and
decode(fifs.application_column_name,'SEGMENT1',gcck.segment1,'SEGMENT2',gcck.segment2,'SEGMENT3',gcck.segment3,'SEGMENT4',gcck.segment4,'SEGMENT5',gcck.segment5,'SEGMENT6',gcck.segment6,'SEGMENT7',gcck.segment7,'SEGMENT8',gcck.segment8,'SEGMENT9',gcck.segment9,'SEGMENT10',gcck.segment10,'SEGMENT11',gcck.segment11,'SEGMENT12',gcck.segment12,'SEGMENT13',gcck.segment13,'SEGMENT14',gcck.segment14,'SEGMENT15',gcck.segment15)=ffv.flex_value and
(ffv.enabled_flag='N' or ffv.start_date_active>sysdate or ffv.end_date_active<trunc(sysdate))
)

LOV

Show Parents

select 'nvl(pmap.parent_flex_value,''(No Parent)'') concatenated_segments,--' from dual

LOV

Show DFF Attributes

select xxen_util.dff_columns(p_table_name=>'gl_code_combinations',p_table_alias=>'gcck') from dual

LOV

Download

Excel XML SQL

Blitz Report™

Blitz Report™ provides multiple benefits:

Update thousands of records in seconds
Perfectly formatted Excel reports
Create new or ad-hoc reports in minutes
User configurable reports
Includes seeded reports for all core areas
Toolkits for tuning, management and support
Data governance and audit tracking
Central report repository & version control
Imports BI-Publisher and Discoverer reports
EBS forms interface for user adoption

Find out more

Features

Download

FAQ