GL Journals

Description
Categories: Enginatics
Repository: Github Columns: Period Name, Effective Date, Posted Date, Ledger, Ledger Category, Batch Status, Source Name, Cat Name, Batch Name, Journal Name ...
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.doc_sequence_value document_number,
xxen_util.meaning(gjh.tax_status_code,'TAX_STATUS',101) tax_status_code,
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.description(gjh.actual_flag,'BATCH_TYPE',101) balance_type,
gjh.currency_conversion_date conversion_date,
(select gdct.user_conversion_type from gl_daily_conversion_types gdct where gjh.currency_conversion_type=gdct.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
xxen_util.meaning(gjh.status,'BATCH_STATUS',101) 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=(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
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=:ledger_category
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
select
'fad1.seq_num batch_attch_sequence,
fdcv1.user_name batch_attch_category,' ||
decode(fnd_release.major_version,'11',null,'fdt1.title batch_attch_title,') || '
fdd1.user_name batch_attch_data_type,' ||
decode(fnd_release.major_version,'11','nvl(fl1.file_name,fd1.file_name) batch_attch_name,','decode(fd1.datatype_id,5,fd1.url,nvl(fl1.file_name,fd1.file_name)) batch_attch_name,') || chr(10) ||
decode(fnd_release.major_version,'11'
,'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,
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,' ||
decode(fnd_release.major_version,'11',null,'fdt2.title journal_attch_title,') || '
fdd2.user_name journal_attch_data_type,' ||
decode(fnd_release.major_version,'11','nvl(fl2.file_name,fd2.file_name) journal_attch_name,','decode(fd2.datatype_id,5,fd2.url,nvl(fl2.file_name,fd2.file_name)) journal_attch_name,') || chr(10) ||
decode(fnd_release.major_version,'11'
,'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,
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,'
from dual
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,