GL Journals

Description
Categories: Enginatics
Repository: Github
GL batches and journals report, including amounts and attachments
select
gjh.period_name,
gjh.default_effective_date effective_date,
gjh.posted_date,
gl.name ledger,
xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101) ledger_category,
xxen_util.meaning(gjb.status,'MJE_BATCH_STATUS',101) batch_status,
(select gjsv.user_je_source_name from gl_je_sources_vl gjsv where gjh.je_source=gjsv.je_source_name) source_name,
(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,
gjh.name journal_name,
gjh.description journal_description,
gjh.running_total_dr entered_dr,
gjh.running_total_cr entered_cr,
nvl(gjh.running_total_dr,0)-nvl(gjh.running_total_cr,0) entered_amount,
gjh.currency_code currency,
gjh.running_total_accounted_dr accounted_dr,
gjh.running_total_accounted_cr accounted_cr,
nvl(gjh.running_total_accounted_dr,0)-nvl(gjh.running_total_accounted_cr,0) accounted_amount,
gl.currency_code ledger_currency,
xxen_util.meaning(gjh.actual_flag,'XLA_BALANCE_TYPE',602) balance_type,
gjh.currency_conversion_date conversion_date,
gjh.currency_conversion_type conversion_type,
gjh.currency_conversion_rate conversion_rate,
gjh.external_reference,
(select gbv.budget_name from gl_budget_versions gbv where gjh.budget_version_id=gbv.budget_version_id) budget_name,
fad1.count batch_attachment_count,
fad2.count journal_attachment_count,
&attachment_columns
gjh.status journal_status
from
gl_ledgers gl,
gl_periods gp,
gl_je_batches gjb,
gl_je_headers gjh,
(select distinct fad.pk1_value,&fad_document_id count(*) over (partition by fad.pk1_value) count from fnd_attached_documents fad where fad.entity_name='GL_JE_BATCHES') fad1,
(select distinct fad.pk2_value,&fad_document_id count(*) over (partition by fad.pk2_value) count from fnd_attached_documents fad where fad.entity_name='GL_JE_HEADERS') fad2,
fnd_documents fd1,
fnd_documents fd2,
fnd_documents_tl fdt1,
fnd_documents_tl fdt2,
fnd_document_datatypes fdd1,
fnd_document_datatypes fdd2,
fnd_document_categories_vl fdcv1,
fnd_document_categories_vl fdcv2,
fnd_lobs fl1,
fnd_lobs fl2,
fnd_documents_short_text fdst1,
fnd_documents_short_text fdst2,
fnd_documents_long_text fdlt1,
fnd_documents_long_text fdlt2
where
1=1 and
gl.period_set_name=gp.period_set_name and
gp.period_name=gjh.period_name and
gl.ledger_id=gjh.ledger_id and
gjb.je_batch_id=gjh.je_batch_id and
to_char(gjb.je_batch_id)=fad1.pk1_value(+) and
to_char(gjh.je_header_id)=fad2.pk2_value(+) and
fad1.document_id=fd1.document_id(+) and
fad2.document_id=fd2.document_id(+) and
fd1.document_id=fdt1.document_id(+) and
fd2.document_id=fdt2.document_id(+) and
fdt1.language(+)=userenv('lang') and
fdt2.language(+)=userenv('lang') and
fd1.datatype_id=fdd1.datatype_id(+) and
fd2.datatype_id=fdd2.datatype_id(+) and
fdd1.language(+)=userenv('lang') and
fdd2.language(+)=userenv('lang') and
fd1.category_id=fdcv1.category_id(+) and
fd2.category_id=fdcv2.category_id(+) and
fd1.media_id=fl1.file_id(+) and
fd2.media_id=fl2.file_id(+) and
decode(fd1.datatype_id,1,fd1.media_id)=fdst1.media_id(+) and
decode(fd2.datatype_id,1,fd2.media_id)=fdst2.media_id(+) and
decode(fd1.datatype_id,2,fd1.media_id)=fdlt1.media_id(+) and
decode(fd2.datatype_id,2,fd2.media_id)=fdlt2.media_id(+)
order by
gp.period_year desc,
gp.period_num desc,
gjh.default_effective_date,
gjb.name,
gjh.name,
gl.name,
fad1.seq_num,
fad2.seq_num
Parameter Name SQL text Validation
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=xxen_util.lookup_code(:balance_type,'XLA_BALANCE_TYPE',602)
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
Ledger
gl.name=:ledger
LOV
Batch
gjb.name=:batch
LOV
Journal
gjh.name=:journal
LOV
Status
gjh.status=xxen_util.lookup_code(:status,'BATCH_STATUS',101)
LOV
Ledger Category
gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101)
LOV
Posted Date From
gjh.posted_date>=:posted_date_from
Date
Posted Date To
gjh.posted_date<:posted_date_to+1
Date
Show Attachment Details
fad1.seq_num batch_attch_sequence,
fdcv1.user_name batch_attch_category,
fdt1.title batch_attch_title,
fdd1.user_name batch_attch_data_type,
decode(fd1.datatype_id,5,fd1.url,nvl(fl1.file_name,fd1.file_name)) batch_attch_name,
decode(fd1.datatype_id,
5,'=HYPERLINK("'||fd1.url||'","'||fd1.url||'")',
nvl2(fd1.media_id,'=HYPERLINK("'||fnd_gfm.construct_download_url(fnd_web_config.gfm_agent,fd1.media_id)||'","'||nvl(fl1.file_name,fd1.file_name)||'")',null)
) batch_attch_url,
decode(fd1.datatype_id,1,to_clob(fdst1.short_text),2,xxen_util.long_to_clob('FND_DOCUMENTS_LONG_TEXT','LONG_TEXT',fdlt1.rowid)) batch_attch_text,
fad2.seq_num journal_attch_sequence,
fdcv2.user_name journal_attch_category,
fdt2.title journal_attch_title,
fdd2.user_name journal_attch_data_type,
decode(fd2.datatype_id,5,fd2.url,nvl(fl2.file_name,fd2.file_name)) journal_attch_name,
decode(fd2.datatype_id,
5,'=HYPERLINK("'||fd2.url||'","'||fd2.url||'")',
nvl2(fd2.media_id,'=HYPERLINK("'||fnd_gfm.construct_download_url(fnd_web_config.gfm_agent,fd2.media_id)||'","'||nvl(fl2.file_name,fd2.file_name)||'")',null)
) journal_attch_url,
decode(fd2.datatype_id,1,to_clob(fdst2.short_text),2,xxen_util.long_to_clob('FND_DOCUMENTS_LONG_TEXT','LONG_TEXT',fdlt2.rowid)) journal_attch_text,
LOV Oracle
Status
gjh.status not in ('P','U')
Show Attachment Details
 null seq_num, null document_id,
Show Attachment Details
 fad.seq_num, fad.document_id,