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.
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
Revaluation Currency
 
LOV
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
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
Ledger
gl.name=:ledger
LOV
Account Type
gcc.account_type=xxen_util.lookup_code(:account_type,'ACCOUNT_TYPE',0)
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
Batch
gjb.name=:batch
LOV
Journal
gjh.name=:journal
LOV
Status
gjh.status=xxen_util.lookup_code(:status,'BATCH_STATUS',101)
LOV
GL_SEGMENT1
gcc.segment1=:segment1
LOV
GL_SEGMENT1 From
gcc.segment1>=:segment1_from
LOV
GL_SEGMENT1 To
gcc.segment1<=:segment1_to
LOV
GL_SEGMENT10
gcc.segment10=:segment10
LOV
GL_SEGMENT10 From
gcc.segment10>=:segment10_from
LOV
GL_SEGMENT10 To
gcc.segment10<=:segment10_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
Posted Date From
gjh.posted_date>=:posted_date_from
Date
Posted Date To
gjh.posted_date<:posted_date_to+1
Date
Revaluation Conversion Type
 
LOV
Ledger
select distinct
'gcc.'||lower(fifsv.application_column_name)||' "'||fifsv.form_left_prompt||'",'||chr(10)||
'xxen_util.segment_description(gcc.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', gcc.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' desc",' text,
min(fifsv.id_flex_num) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) min_id_flex_num,
min(fifsv.segment_num) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) 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 xxen_util.contains(:ledger,gl.name)='Y')
order by
min_id_flex_num,
min_segment_num
Status
gjh.status not in ('P','U')
Revaluation Currency
nvl2(xal.gl_sl_link_id,xal.entered_dr,gjl.entered_dr)*decode(nvl2(xal.gl_sl_link_id,xal.currency_code,gjh.currency_code),:revaluation_currency,1,gdr.conversion_rate) <parameter_value>_dr,
nvl2(xal.gl_sl_link_id,xal.entered_cr,gjl.entered_cr)*decode(nvl2(xal.gl_sl_link_id,xal.currency_code,gjh.currency_code),:revaluation_currency,1,gdr.conversion_rate) <parameter_value>_cr,
(nvl(nvl2(xal.gl_sl_link_id,xal.entered_dr,gjl.entered_dr),0)-nvl(nvl2(xal.gl_sl_link_id,xal.entered_cr,gjl.entered_cr),0))*decode(nvl2(xal.gl_sl_link_id,xal.currency_code,gjh.currency_code),:revaluation_currency,1,gdr.conversion_rate) <parameter_value>_amount,
:revaluation_currency <parameter_value>_currency,