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.

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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,
(select get.encumbrance_type from gl_encumbrance_types get where get.encumbrance_type_id = gjh.encumbrance_type_id) encumbrance_type,
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
(
'Journal Line'='&account_matching_level' and
1=1
or
'Journal Header'='&account_matching_level' and
gjh.rowid in
(
select
gjh.rowid
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
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
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
gl_security_pkg.validate_access(null,gjl.code_combination_id)='TRUE' 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 Matching Level
<parameter_value>
LOV
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 in (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
Budget Name
(gjh.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))
LOV
Encumbrance Type
(gjh.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))
LOV