GL Account Analysis 11i

Description
Categories: Enginatics
Repository: Github
Detail GL transaction report with one line per journal line including all segments, with amounts in both transaction currency and ledger currency.
Run GL Account Analysis 11i and other Oracle EBS reports with Blitz Report™ on our demo environment
select
gl.name ledger,
gjh.period_name,
(select gjsv.user_je_source_name from gl_je_sources_vl gjsv where gjh.je_source=gjsv.je_source_name) source_name,
gjh.external_reference reference,
(select gjcv.user_je_category_name from gl_je_categories_vl gjcv where gjh.je_category=gjcv.je_category_name) category_name,
gjb.name batch_name,
xxen_util.meaning(gjb.status,'MJE_BATCH_STATUS',101) batch_status,
gjh.posted_date,
gjh.name journal_name,
gjh.description journal_description,
gjh.doc_sequence_value document_number,
xxen_util.meaning(gjh.tax_status_code,'TAX_STATUS',101) tax_status_code,
gjl.description line_description,
xxen_util.meaning(gjl.tax_line_flag,'YES_NO',0) tax_line,
xxen_util.meaning(gjl.taxable_line_flag,'YES_NO',0) taxable_line,
xxen_util.meaning(gjl.amount_includes_tax_flag,'YES_NO',0) amount_includes_tax,
xxen_util.meaning(gcc.account_type,'ACCOUNT_TYPE',0) account_type,
&segment_columns gjl.entered_dr jnl_line_entered_dr,
gjl.entered_cr jnl_line_entered_cr,
nvl(gjl.entered_dr,0)-nvl(gjl.entered_cr,0) jnl_line_entered_amount,
gjh.currency_code jnl_line_currency,
gjl.accounted_dr jnl_line_accounted_dr,
gjl.accounted_cr jnl_line_accounted_cr,
nvl(gjl.accounted_dr,0)-nvl(gjl.accounted_cr,0) accounted_amount,
gl.currency_code ledger_currency,
&revaluation_columns gjh.doc_sequence_value doc_sequence_value,
xxen_util.description(gjh.actual_flag,'BATCH_TYPE',101) balance_type,
(select gbv.budget_name from gl_budget_versions gbv where gjh.budget_version_id=gbv.budget_version_id) budget_name,
gjh.currency_conversion_date conversion_date,
gjh.currency_conversion_type conversion_type,
gjh.currency_conversion_rate conversion_rate,
xxen_util.user_name(gjh.created_by) journal_created_by,
gjh.creation_date journal_creation_date,
gjb.je_batch_id,
gjl.je_header_id,
gjl.je_line_num,
gjl.context dff_context
from
gl_sets_of_books gl,
gl_periods gp,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
(select gdr.* from gl_daily_rates gdr, gl_daily_conversion_types gdct where gdr.to_currency=:revaluation_currency and gdr.conversion_type=gdct.conversion_type and gdct.user_conversion_type=:revaluation_conversion_type) gdr
where
1=1 and
gl.period_set_name=gp.period_set_name and
gp.period_name=gjh.period_name and
gl.set_of_books_id=gjh.set_of_books_id and
gjh.je_batch_id=gjb.je_batch_id and
gp.period_name=gjl.period_name and
gjh.je_header_id=gjl.je_header_id and
gjl.code_combination_id=gcc.code_combination_id and
gjh.currency_conversion_date=gdr.conversion_date(+) and
case gjh.currency_code when:revaluation_currency then null else gjh.currency_code end=gdr.from_currency(+)
order by
gl.name,
gjh.posted_date,
source_name,
category_name,
gjb.name,
gjh.name,
gjl.je_line_num
Parameter Name SQL text Validation
Ledger
gl.name=:ledger
LOV
Period
gp.period_name=:period_name
LOV
Period From
gp.period_year*10000+gp.period_num>=(select gp0.period_year*10000+gp0.period_num effective_period_num from gl_periods gp0 where gl.period_set_name=gp0.period_set_name and gp0.period_name=:period_name_from)
LOV
Period To
gp.period_year*10000+gp.period_num<=(select gp0.period_year*10000+gp0.period_num effective_period_num from gl_periods gp0 where gl.period_set_name=gp0.period_set_name and gp0.period_name=:period_name_to)
LOV
Posted Date From
gjh.posted_date>=:posted_date_from
Date
Posted Date To
gjh.posted_date<:posted_date_to+1
Date
Journal Source
gjh.je_source in (select gjsv.je_source_name from gl_je_sources_vl gjsv where gjsv.user_je_source_name=:user_je_source_name)
LOV
Journal Category
gjh.je_category in (select gjcv.je_category_name from gl_je_categories_vl gjcv where gjcv.user_je_category_name=:journal_category)
LOV
Batch
gjb.name=:batch
LOV
Journal
gjh.name=:journal
LOV
Journal Line
gjl.je_line_num=:journal_line
Number
Account Type
gcc.account_type=xxen_util.lookup_code(:account_type,'ACCOUNT_TYPE',0)
LOV
Concatenated Segments
gcc.concatenated_segments=:concatenated_segments
Char
GL_SEGMENT1
gcc.segment1=:segment1
LOV
GL_SEGMENT1 From
gcc.segment1>=:segment1_from
LOV
GL_SEGMENT1 To
gcc.segment1<=:segment1_to
LOV
GL_SEGMENT2
gcc.segment2=:segment2
LOV
GL_SEGMENT2 From
gcc.segment2>=:segment2_from
LOV
GL_SEGMENT2 To
gcc.segment2<=:segment2_to
LOV
GL_SEGMENT3
gcc.segment3=:segment3
LOV
GL_SEGMENT3 From
gcc.segment3>=:segment3_from
LOV
GL_SEGMENT3 To
gcc.segment3<=:segment3_to
LOV
GL_SEGMENT4
gcc.segment4=:segment4
LOV
GL_SEGMENT4 From
gcc.segment4>=:segment4_from
LOV
GL_SEGMENT4 To
gcc.segment4<=:segment4_to
LOV
GL_SEGMENT5
gcc.segment5=:segment5
LOV
GL_SEGMENT5 From
gcc.segment5>=:segment5_from
LOV
GL_SEGMENT5 To
gcc.segment5<=:segment5_to
LOV
GL_SEGMENT6
gcc.segment6=:segment6
LOV
GL_SEGMENT6 From
gcc.segment6>=:segment6_from
LOV
GL_SEGMENT6 To
gcc.segment6<=:segment6_to
LOV
GL_SEGMENT7
gcc.segment7=:segment7
LOV
GL_SEGMENT7 From
gcc.segment7>=:segment7_from
LOV
GL_SEGMENT7 To
gcc.segment7<=:segment7_to
LOV
GL_SEGMENT8
gcc.segment8=:segment8
LOV
GL_SEGMENT8 From
gcc.segment8>=:segment8_from
LOV
GL_SEGMENT8 To
gcc.segment8<=:segment8_to
LOV
GL_SEGMENT9
gcc.segment9=:segment9
LOV
GL_SEGMENT9 From
gcc.segment9>=:segment9_from
LOV
GL_SEGMENT9 To
gcc.segment9<=:segment9_to
LOV
GL_SEGMENT10
gcc.segment10=:segment10
LOV
GL_SEGMENT10 From
gcc.segment10>=:segment10_from
LOV
GL_SEGMENT10 To
gcc.segment10<=:segment10_to
LOV
Status
gjh.status=xxen_util.lookup_code(:status,'BATCH_STATUS',101)
LOV
Revaluation Currency
 
LOV
Revaluation Conversion Type
 
LOV
Balance Type
gjh.actual_flag=(select flvv.lookup_code from fnd_lookup_values_vl flvv where flvv.description=:balance_type and flvv.lookup_type='BATCH_TYPE' and flvv.view_application_id=101 and flvv.security_group_id=0)
LOV