ENGINATICSSYSTEM_ADMINISTRATORSYSADMIN3A3828F2E3DEAEFCE06362FB0905A1D1Yselect
null action_,
null status_,
null message_,
null request_id_,
null modified_columns_,
null row_id,
to_number(null) group_id,
'Vision Operations (USA)' ledger_name,
'Operations' organization,
'CORPORATE 2000' budget,
'Spreadsheet' source,
'Adjustment' category,
'Dummy' batch_name,
'Dummy' batch_description,
'Dummy' journal_name,
'Dummy' journal_description,
'Dummy' journal_reference,
'GBP' currency,
sysdate accounting_date,
'Jan-99' period,
to_char(null) account_alias,
to_char(null) concatenated_segments,
to_char(null) gl_segment1,
to_char(null) gl_segment2,
to_char(null) gl_segment3,
to_char(null) gl_segment4,
to_char(null) gl_segment5,
to_char(null) gl_segment6,
to_char(null) gl_segment7,
to_char(null) gl_segment8,
to_char(null) gl_segment9,
to_char(null) gl_segment10,
to_char(null) gl_segment11,
to_char(null) gl_segment12,
to_char(null) gl_segment13,
to_char(null) gl_segment14,
to_char(null) gl_segment15,
0 debit,
0 credit,
'Yes' reverse_journal,
'Jan-99' reversal_period,
'User' conversion_type,
sysdate conversion_date,
1 conversion_rate,
'Dummy' line_description,
'A' actual_flag,
'INV' encumbrance_type,
null attach_upload_file,
null status,
sysdate date_created,
to_number(null) created_by,
to_number(null) user_id,
null summary_flag,
null post_to_suspense_flag,
null submit_approval,
null submit_posting,
--
null line_dff_context,
null gl_je_line_attribute1,
null gl_je_line_attribute2,
null gl_je_line_attribute3,
null gl_je_line_attribute4,
null gl_je_line_attribute5,
null gl_je_line_attribute6,
null gl_je_line_attribute7,
null gl_je_line_attribute8,
null gl_je_line_attribute9,
null gl_je_line_attribute10,
--
null attachment_category_,
null attachment_title_,
null attachment_description_,
null attachment_type_,
null attachment_content_,
null attachment_file_id_
from
dual
where
1=1 and
1=0 and
:p_actual_flag=:p_actual_flag and
nvl(:p_foreign_currency_flag,'No')=nvl(:p_foreign_currency_flag,'No') and
:p_create_summary_jnl=:p_create_summary_jnl and
:p_post_to_suspense_flag=:p_post_to_suspense_flag
¬_use_first_block
&report_table_select &report_table_name &report_table_where_clause &success_records
&processed_runUSQLGLGLLEZLSRSF197EA704D3C076AE0530100007F65F9AYxxen_gl_upload.insert_gl_interfacexxen_gl_upload.submit_gl_importxxen_gl_upload.validate_journal_line(
p_ledger=>:$column$.ledger_name,
p_segment1=>:$column$.gl_segment1,
p_segment2=>:$column$.gl_segment2,
p_segment3=>:$column$.gl_segment3,
p_segment4=>:$column$.gl_segment4,
p_segment5=>:$column$.gl_segment5,
p_segment6=>:$column$.gl_segment6,
p_segment7=>:$column$.gl_segment7,
p_segment8=>:$column$.gl_segment8,
p_segment9=>:$column$.gl_segment9,
p_segment10=>:$column$.gl_segment10,
p_segment11=>:$column$.gl_segment11,
p_segment12=>:$column$.gl_segment12,
p_segment13=>:$column$.gl_segment13,
p_segment14=>:$column$.gl_segment14,
p_segment15=>:$column$.gl_segment15,
p_context=>:$column$.line_dff_context,
p_attribute1=>:$column$.gl_je_line_attribute1,
p_attribute2=>:$column$.gl_je_line_attribute2,
p_attribute3=>:$column$.gl_je_line_attribute3,
p_attribute4=>:$column$.gl_je_line_attribute4,
p_attribute5=>:$column$.gl_je_line_attribute5,
p_attribute6=>:$column$.gl_je_line_attribute6,
p_attribute7=>:$column$.gl_je_line_attribute7,
p_attribute8=>:$column$.gl_je_line_attribute8,
p_attribute9=>:$column$.gl_je_line_attribute9,
p_attribute10=>:$column$.gl_je_line_attribute10
)USAshish: GL Journal UploadGL Journal Upload
Templates are provided for the following Journal Types
- Functional Actuals
- Foreign Actuals
- Budget Journals
- Encumbrance Journals
Submit for Approval
---------------------------
Imported Journals requiring approval can be submitted for approval after import if they are eligble.
Submit Journal Post
---------------------------
Imported Journals can be submitted for immediate posting after import if they are eligible.
In order to submit journals for posting, the current responsibility must have access to post journals.Upload¬_use_first_block&processed_run&report_table_name&report_table_select&report_table_where_clause&success_records1=1:p_actual_flag:p_create_summary_jnl:p_foreign_currency_flag:p_post_to_suspense_flag1-40:p_create_summary_jnlLOV OracleYes_NoYselect
lookup_code id,
meaning value,
null description
from
fnd_lookups
where fnd_lookups.lookup_type='YES_NO'
order by value,descriptionNYUSCreate Summary Journal2-30:p_post_to_suspense_flagLOV OracleYes_NoYselect
lookup_code id,
meaning value,
null description
from
fnd_lookups
where fnd_lookups.lookup_type='YES_NO'
order by value,descriptionNYUSPost Errors to Suspense3-51=1:ledger=:ledgerCharxxen_util.default_ledgerUSLedger410:p_actual_flagLOV customYselect
flvv.meaning value,
flvv.lookup_code description
from
fnd_lookup_values_vl flvv
where
flvv.lookup_type='XLA_BALANCE_TYPE' and
flvv.view_application_id=602 and
flvv.security_group_id=0
order by
flvv.meaningxxen_util.meaning('A','XLA_BALANCE_TYPE',602)YUSJournal Type520:p_foreign_currency_flagLOV customYselect
xxen_util.meaning('Y','YES_NO',0) value,
null description
from
dual
where
(select flvv.lookup_code from fnd_lookup_values_vl flvv where flvv.lookup_type = 'XLA_BALANCE_TYPE' and flvv.view_application_id = 602 and flvv.description = :$flex$.Journal_Type) != 'E'USAllow Foreign Currency6301=1:attach_upload_file_to_journal=:attach_upload_file_to_journalLOV OracleYes_NoYselect
lookup_code id,
meaning value,
null description
from
fnd_lookups
where fnd_lookups.lookup_type='YES_NO'
order by value,descriptionNYUSAttach Upload file to Journal7401=1:submit_for_approval=:submit_for_approvalLOV OracleYes_NoYselect
lookup_code id,
meaning value,
null description
from
fnd_lookups
where fnd_lookups.lookup_type='YES_NO'
order by value,descriptionNYUSSubmit for Approval8501=1:submit_posting=:submit_postingLOV customYselect
lookup_code id,
meaning value,
null description
from
fnd_lookups
where
fnd_lookups.lookup_type='YES_NO' and
(lookup_code = 'N' or
xxen_gl_upload.responsibility_can_post = 'Y'
)
order by value,descriptionNYUSSubmit Journal Post1ACTION_Charvarchar22STATUS_Charvarchar23MESSAGE_Charvarchar24REQUEST_ID_Charvarchar2YY5MODIFIED_COLUMNS_Charvarchar2Y6ROW_IDCharvarchar2YY7GROUP_IDNumbernumberYY8LEDGER_NAMELOV customvarchar2Yselect
gl.name value,
fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description
from
gl_ledgers gl,
fnd_id_flex_structures_vl fifsv
where
(:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and
(:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and
gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) and
gl.chart_of_accounts_id=fifsv.id_flex_num and
fifsv.id_flex_code='GL#' and
fifsv.application_id=101
order by
fifsv.id_flex_structure_name,
decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4),
gl.nameselect
gl.ledger_id id,
gl.name value
from
gl_ledgers glY9ORGANIZATIONLOV customvarchar2Yselect
gbecv.budget_entity_id id,
gbecv.name value,
gbecv.description description
from
gl_ledgers gl,
gl_budget_entities_current_v gbecv
where
gl.name = :$column$.ledger_name and
gbecv.ledger_id = gl.ledger_id and
gbecv.use_privilege = 'Y'
order by
value10BUDGETLOV customvarchar2Yselect
gbv.budget_version_id id,
gbv.budget_name value,
gbv.description
from
gl_budgets_v gbv
where
gbv.ledger_name = :$column$.ledger_name and
gbv.status in ('C','O') and
gbv.latest_opened_year is not null and
( :$column$.organization is null or
exists
(select
null
from
gl_budget_entities_current_v gbecv,
gl_entity_budgets geb
where
gbecv.name = :$column$.organization and
gbecv.budget_entity_id = geb.budget_entity_id and
geb.budget_version_id = gbv.budget_version_id
)
)
order by
value
select
gbv.budget_version_id id,
gbv.budget_name value
from
gl_budgets_v gbv11SOURCELOV customvarchar2Yselect
gjsv.user_je_source_name value,
gjsv.description
from
gl_je_sources_v gjsvY12CATEGORYLOV customvarchar2Yselect
gjcv.user_je_category_name value,
gjcv.description
from
gl_je_categories_v gjcvY13BATCH_NAMECharvarchar214BATCH_DESCRIPTIONCharvarchar215JOURNAL_NAMECharvarchar216JOURNAL_DESCRIPTIONCharvarchar217JOURNAL_REFERENCECharvarchar218CURRENCYLOV customvarchar2Yselect
fcv.currency_code id,
fcv.currency_code value,
fcv.description
from
fnd_currencies_vl fcv
where
fcv.enabled_flag='Y' and
( ( (:$flex$.allow_foreign_currency = xxen_util.meaning('Y','YES_NO',0) or fcv.currency_code = 'STAT') and
xxen_util.lookup_code(:$flex$.journal_type,'XLA_BALANCE_TYPE',602) != 'E'
) or
fcv.currency_code = (select gl.currency_code from gl_ledgers gl where gl.name = :$column$.ledger_name)
)
order by
fcv.currency_codeY19ACCOUNTING_DATEDatedateY20PERIODLOV customvarchar2Yselect
gps.period_name id,
gps.period_name value,
xxen_util.meaning(gps.closing_status,'CLOSING_STATUS',101)||': '||fnd_date.date_to_displaydate(gps.start_date)||' - '||fnd_date.date_to_displaydate(gps.end_date) description
from
gl_ledgers gl,
gl_period_statuses gps
where
gl.name = :$column$.ledger_name and
gps.ledger_id = gl.ledger_id and
gps.application_id = 101 and
gps.closing_status='O' and
:$column$.accounting_date between gps.start_date and gps.end_date
order by
gps.start_date descselect
gps.period_name
from
gl_ledgers gl,
gl_period_statuses gps
where
gl.name = :$column$.ledger_name and
gps.ledger_id = gl.ledger_id and
gps.application_id = 101 and
gps.closing_status='O' and
:$column$.accounting_date between gps.start_date and gps.end_date
Y21ACCOUNT_ALIASLOV customvarchar2Yselect
fsfa.alias_name id,
fsfa.alias_name value,
fsfa.concatenated_segments || ' (' || flvv.description || ')' description
from
gl_ledgers gl,
fnd_shorthand_flex_aliases fsfa,
gl_code_combinations_kfv gcck,
fnd_lookup_values_vl flvv
where
gl.name=:$column$.ledger_name and
fsfa.application_id = 101 and
fsfa.id_flex_code = 'GL#' and
fsfa.id_flex_num = gl.chart_of_accounts_id and
fsfa.enabled_flag = 'Y' and
trunc(sysdate) between nvl(fsfa.start_date_active,trunc(sysdate)) and nvl(fsfa.end_date_active,trunc(sysdate)) and
gcck.chart_of_accounts_id = gl.chart_of_accounts_id and
gcck.concatenated_segments = fsfa.concatenated_segments and
gcck.enabled_flag = 'Y' and
gcck.summary_flag = 'N' and
trunc(sysdate) between nvl(gcck.start_date_active,trunc(sysdate)) and nvl(gcck.end_date_active,trunc(sysdate)) and
decode(xxen_util.lookup_code(:$flex$.journal_type,'XLA_BALANCE_TYPE',602),'B',gcck.detail_budgeting_allowed,gcck.detail_posting_allowed) = 'Y' and
flvv.lookup_type = 'ACCOUNT TYPE' and
flvv.view_application_id = 101 and
flvv.lookup_code = gcck.gl_account_type
order by value
22CONCATENATED_SEGMENTSLOV customvarchar2YYselect
gcck.code_combination_id id,
gcck.concatenated_segments value,
xxen_util.segments_description(gcck.code_combination_id) || ' (' || flvv.description || ')' description
from
gl_code_combinations_kfv gcck,
gl_ledgers gl,
fnd_lookup_values_vl flvv
where
gcck.chart_of_accounts_id=gl.chart_of_accounts_id and
gl.name=:$column$.ledger_name and
gcck.enabled_flag = 'Y' and
gcck.summary_flag = 'N' and
trunc(sysdate) between nvl(gcck.start_date_active,trunc(sysdate)) and nvl(gcck.end_date_active,trunc(sysdate)) and
decode(xxen_util.lookup_code(:$flex$.journal_type,'XLA_BALANCE_TYPE',602),'B',gcck.detail_budgeting_allowed,gcck.detail_posting_allowed) = 'Y' and
flvv.lookup_type = 'ACCOUNT TYPE' and
flvv.view_application_id = 101 and
flvv.lookup_code = gcck.gl_account_type and
(:$column$.account_alias is null or
gcck.concatenated_segments =
(select
fsfa.concatenated_segments
from
fnd_shorthand_flex_aliases fsfa
where
fsfa.application_id = 101 and
fsfa.id_flex_code = 'GL#' and
fsfa.id_flex_num = gl.chart_of_accounts_id and
fsfa.enabled_flag = 'Y' and
trunc(sysdate) between nvl(fsfa.start_date_active,trunc(sysdate)) and nvl(fsfa.end_date_active,trunc(sysdate)) and
fsfa.alias_name = :$column$.account_alias
)
)
order by
valueselect
gcck.concatenated_segments value
from
gl_code_combinations_kfv gcck,
gl_ledgers gl,
fnd_lookup_values_vl flvv
where
gcck.chart_of_accounts_id=gl.chart_of_accounts_id and
gl.name=:$column$.ledger_name and
gcck.enabled_flag = 'Y' and
gcck.summary_flag = 'N' and
trunc(sysdate) between nvl(gcck.start_date_active,trunc(sysdate)) and nvl(gcck.end_date_active,trunc(sysdate)) and
decode(xxen_util.lookup_code(:$flex$.journal_type,'XLA_BALANCE_TYPE',602),'B',gcck.detail_budgeting_allowed,gcck.detail_posting_allowed) = 'Y' and
flvv.lookup_type = 'ACCOUNT TYPE' and
flvv.view_application_id = 101 and
flvv.lookup_code = gcck.gl_account_type and
gcck.concatenated_segments =
(select
fsfa.concatenated_segments
from
fnd_shorthand_flex_aliases fsfa
where
fsfa.application_id = 101 and
fsfa.id_flex_code = 'GL#' and
fsfa.id_flex_num = gl.chart_of_accounts_id and
fsfa.enabled_flag = 'Y' and
trunc(sysdate) between nvl(fsfa.start_date_active,trunc(sysdate)) and nvl(fsfa.end_date_active,trunc(sysdate)) and
fsfa.alias_name = :$column$.account_alias
)23GL_SEGMENT1LOV customvarchar2Yselect distinct
ffvv.flex_value id,
ffvv.flex_value value,
ffvv.description
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.enabled_flag='Y' and
fifsgv.application_column_name='SEGMENT1' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
ffvv.enabled_flag='Y' and
sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment1 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)
order by
ffvv.flex_valueselect distinct
ffvv.flex_value value
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.application_column_name='SEGMENT1' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment1 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)24GL_SEGMENT2LOV customvarchar2Yselect distinct
ffvv.flex_value id,
ffvv.flex_value value,
ffvv.description
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.enabled_flag='Y' and
fifsgv.application_column_name='SEGMENT2' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
ffvv.enabled_flag='Y' and
sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment2 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)
order by
ffvv.flex_valueselect distinct
ffvv.flex_value value
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.application_column_name='SEGMENT2' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment2 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)25GL_SEGMENT3LOV customvarchar2Yselect distinct
ffvv.flex_value id,
ffvv.flex_value value,
ffvv.description
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.enabled_flag='Y' and
fifsgv.application_column_name='SEGMENT3' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
ffvv.enabled_flag='Y' and
sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment3 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)
order by
ffvv.flex_valueselect distinct
ffvv.flex_value value
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.application_column_name='SEGMENT3' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment3 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)26GL_SEGMENT4LOV customvarchar2Yselect distinct
ffvv.flex_value id,
ffvv.flex_value value,
ffvv.description
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.enabled_flag='Y' and
fifsgv.application_column_name='SEGMENT4' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
ffvv.enabled_flag='Y' and
sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment4 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)
order by
ffvv.flex_valueselect distinct
ffvv.flex_value value
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.application_column_name='SEGMENT4' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment4 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)27GL_SEGMENT5LOV customvarchar2Yselect distinct
ffvv.flex_value id,
ffvv.flex_value value,
ffvv.description
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.enabled_flag='Y' and
fifsgv.application_column_name='SEGMENT5' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
ffvv.enabled_flag='Y' and
sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment5 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)
order by
ffvv.flex_valueselect distinct
ffvv.flex_value value
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.application_column_name='SEGMENT5' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment5 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)28GL_SEGMENT6LOV customvarchar2Yselect distinct
ffvv.flex_value id,
ffvv.flex_value value,
ffvv.description
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.enabled_flag='Y' and
fifsgv.application_column_name='SEGMENT6' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
ffvv.enabled_flag='Y' and
sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment6 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)
order by
ffvv.flex_valueselect distinct
ffvv.flex_value value
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.application_column_name='SEGMENT6' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment6 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)29GL_SEGMENT7LOV customvarchar2Yselect distinct
ffvv.flex_value id,
ffvv.flex_value value,
ffvv.description
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.enabled_flag='Y' and
fifsgv.application_column_name='SEGMENT7' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
ffvv.enabled_flag='Y' and
sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment7 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)
order by
ffvv.flex_valueselect distinct
ffvv.flex_value value
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.application_column_name='SEGMENT7' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment7 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)30GL_SEGMENT8LOV customvarchar2Yselect distinct
ffvv.flex_value id,
ffvv.flex_value value,
ffvv.description
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.enabled_flag='Y' and
fifsgv.application_column_name='SEGMENT8' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
ffvv.enabled_flag='Y' and
sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment8 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)
order by
ffvv.flex_valueselect distinct
ffvv.flex_value value
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.application_column_name='SEGMENT8' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment8 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)31GL_SEGMENT9LOV customvarchar2Yselect distinct
ffvv.flex_value id,
ffvv.flex_value value,
ffvv.description
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.enabled_flag='Y' and
fifsgv.application_column_name='SEGMENT9' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
ffvv.enabled_flag='Y' and
sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment9 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)
order by
ffvv.flex_valueselect distinct
ffvv.flex_value value
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.application_column_name='SEGMENT9' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment9 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)32GL_SEGMENT10LOV customvarchar2Yselect distinct
ffvv.flex_value id,
ffvv.flex_value value,
ffvv.description
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.enabled_flag='Y' and
fifsgv.application_column_name='SEGMENT10' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
ffvv.enabled_flag='Y' and
sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment10 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)
order by
ffvv.flex_valueselect distinct
ffvv.flex_value value
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.application_column_name='SEGMENT10' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment10 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)33GL_SEGMENT11LOV customvarchar2Yselect distinct
ffvv.flex_value id,
ffvv.flex_value value,
ffvv.description
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.enabled_flag='Y' and
fifsgv.application_column_name='SEGMENT11' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
ffvv.enabled_flag='Y' and
sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment11 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)
order by
ffvv.flex_valueselect distinct
ffvv.flex_value value
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.application_column_name='SEGMENT11' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment11 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)34GL_SEGMENT12LOV customvarchar2Yselect distinct
ffvv.flex_value id,
ffvv.flex_value value,
ffvv.description
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.enabled_flag='Y' and
fifsgv.application_column_name='SEGMENT12' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
ffvv.enabled_flag='Y' and
sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment12 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)
order by
ffvv.flex_valueselect distinct
ffvv.flex_value value
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.application_column_name='SEGMENT12' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment12 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)35GL_SEGMENT13LOV customvarchar2Yselect distinct
ffvv.flex_value id,
ffvv.flex_value value,
ffvv.description
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.enabled_flag='Y' and
fifsgv.application_column_name='SEGMENT13' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
ffvv.enabled_flag='Y' and
sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment13 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)
order by
ffvv.flex_valueselect distinct
ffvv.flex_value value
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.application_column_name='SEGMENT13' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment13 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)36GL_SEGMENT14LOV customvarchar2Yselect distinct
ffvv.flex_value id,
ffvv.flex_value value,
ffvv.description
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.enabled_flag='Y' and
fifsgv.application_column_name='SEGMENT14' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
ffvv.enabled_flag='Y' and
sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment14 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)
order by
ffvv.flex_valueselect distinct
ffvv.flex_value value
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.application_column_name='SEGMENT14' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment14 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)37GL_SEGMENT15LOV customvarchar2Yselect distinct
ffvv.flex_value id,
ffvv.flex_value value,
ffvv.description
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.enabled_flag='Y' and
fifsgv.application_column_name='SEGMENT15' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
ffvv.enabled_flag='Y' and
sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment15 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)
order by
ffvv.flex_valueselect distinct
ffvv.flex_value value
from
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and
fifsgv.application_id=101 and
fifsgv.id_flex_code='GL#' and
fifsgv.application_column_name='SEGMENT15' and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
fifsgv.id_flex_num in (
select /*+ no_unnest*/ gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$column$.ledger_name)
and
(
:$column$.concatenated_segments is null or
ffvv.flex_value =
(select
gcck.segment15 from
gl_code_combinations_kfv gcck
where
gcck.concatenated_segments=:$column$.concatenated_segments and
gcck.chart_of_accounts_id=fifsgv.id_flex_num
)
)38DEBITNumbernumber39CREDITNumbernumber40REVERSE_JOURNALLOV Oraclevarchar2Yes_NoYselect
lookup_code id,
meaning value,
null description
from
fnd_lookups
where fnd_lookups.lookup_type='YES_NO'
order by value,description41REVERSAL_PERIODLOV customvarchar2Yselect
gps.period_name value,
xxen_util.meaning(gps.closing_status,'CLOSING_STATUS',101)||': '||fnd_date.date_to_displaydate(gps.start_date)||' - '||fnd_date.date_to_displaydate(gps.end_date) description
from
gl_ledgers gl,
gl_period_statuses gps
where
gl.name = :$column$.ledger_name and
gps.ledger_id = gl.ledger_id and
gps.application_id = 101 and
:$column$.accounting_date < gps.start_date and
:$column$.reverse_journal = xxen_util.meaning('Y','YES_NO',0)
order by
gps.start_date desc42CONVERSION_TYPELOV customvarchar2Yselect
gdct.user_conversion_type value,
gdct.description
from
gl_daily_conversion_types gdct
order by 143CONVERSION_DATEDatedate44CONVERSION_RATELOV customnumberselect
gdr.conversion_rate id,
gdr.conversion_rate value,
gdct.user_conversion_type || ' - ' || to_char(gdr.conversion_date,'DD-Mon-YYYY') description
from
gl_ledgers gl,
gl_daily_conversion_types gdct,
gl_daily_rates gdr
where
gl.name = :$column$.ledger_name and
gdct.user_conversion_type = :$column$.conversion_type and
gdr.conversion_type = gdct.conversion_type and
gdr.from_currency = :$column$.currency and
gdr.to_currency = gl.currency_code and
gdr.conversion_date = :$column$.conversion_date
45LINE_DESCRIPTIONCharvarchar246ACTUAL_FLAGLOV customvarchar2Yselect 'A' value,'Actuals' description from dual
union all
select 'B' value,'Budget' description from dual
union all
select 'E' value,'Encumbrance' description from dualxxen_util.lookup_code(:$flex$.journal_type,'XLA_BALANCE_TYPE',602)Y47ENCUMBRANCE_TYPELOV customvarchar2Yselect
gctv.encumbrance_type_id id,
gctv.encumbrance_type value,
gctv.description
from
gl_encumbrance_types_v gctv
order by
valueselect
gct.encumbrance_type_id id,
gct.encumbrance_type value
from
gl_encumbrance_types gct48ATTACH_UPLOAD_FILECharvarchar2:$flex$.attach_upload_file_to_journalY49STATUSCharvarchar2NEWY50DATE_CREATEDDateTimedatesysdateY51CREATED_BYNumbernumberfnd_global.user_idY52USER_IDNumbernumberfnd_global.user_idY53SUMMARY_FLAGCharvarchar2:$flex$.create_summary_journalY54POST_TO_SUSPENSE_FLAGCharvarchar2:$flex$.post_errors_to_suspenseY55SUBMIT_APPROVALCharvarchar2:$flex$.Submit_for_ApprovalY56SUBMIT_POSTINGCharvarchar2:$flex$.Submit_Journal_PostY57LINE_DFF_CONTEXTLOV customvarchar2Yselect distinct
fdfcv.descriptive_flex_context_name value,
fdfcv.description
from
fnd_descriptive_flexs_vl fdfv,
fnd_descr_flex_contexts_vl fdfcv
where
fdfcv.application_id = fdfv.application_id and
fdfcv.descriptive_flexfield_name = fdfv.descriptive_flexfield_name and
fdfcv.enabled_flag = 'Y' and
fdfv.application_id = 101 and
fdfv.descriptive_flexfield_name = 'GL_JE_LINES' and
fdfcv.descriptive_flex_context_code != 'Global Data Elements'
order by
fdfcv.descriptive_flex_context_name58GL_JE_LINE_ATTRIBUTE1LOV customvarchar2Yselect
substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value,
substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description
from
table(
xxen_util.dff_attribute_lov
(p_application_id => 101,
p_descriptive_flexfield_name => 'GL_JE_LINES',
p_context_name => :$column$.line_dff_context,
p_column_name => 'ATTRIBUTE1',
p_attribute1 => :$column$.gl_je_line_attribute1,
p_attribute2 => :$column$.gl_je_line_attribute2,
p_attribute3 => :$column$.gl_je_line_attribute3,
p_attribute4 => :$column$.gl_je_line_attribute4,
p_attribute5 => :$column$.gl_je_line_attribute5,
p_attribute6 => :$column$.gl_je_line_attribute6,
p_attribute7 => :$column$.gl_je_line_attribute7,
p_attribute8 => :$column$.gl_je_line_attribute8,
p_attribute9 => :$column$.gl_je_line_attribute9,
p_attribute10 => :$column$.gl_je_line_attribute10
)
) lov
order by
valueselect distinct
'(Context) ' || initcap(fdfcuv.application_column_name) || ': ' ||
listagg(decode(fdfc.descriptive_flex_context_code,'Global Data Elements','(Global)','(' || fdfc.descriptive_flex_context_code || ')') || ' ' || fdfcuv.form_left_prompt,', ') within group (order by decode(fdfc.descriptive_flex_context_code,'Global Data Elements',1,2),fdfc.descriptive_flex_context_code) over (partition by fdfcuv.application_column_name)
from
fnd_descriptive_flexs_vl fdfv,
fnd_descr_flex_contexts fdfc,
fnd_descr_flex_col_usage_vl fdfcuv
where
fdfc.application_id = fdfv.application_id and
fdfc.descriptive_flexfield_name = fdfv.descriptive_flexfield_name and
fdfc.enabled_flag = 'Y' and
fdfcuv.application_id = fdfc.application_id and
fdfcuv.descriptive_flexfield_name = fdfc.descriptive_flexfield_name and
fdfcuv.descriptive_flex_context_code = fdfc.descriptive_flex_context_code and
fdfcuv.enabled_flag = 'Y' and
fdfv.application_id = 101 and
fdfv.descriptive_flexfield_name = 'GL_JE_LINES' and
fdfcuv.application_column_name = 'ATTRIBUTE1'59GL_JE_LINE_ATTRIBUTE2LOV customvarchar2Yselect
substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value,
substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description
from
table(
xxen_util.dff_attribute_lov
(p_application_id => 101,
p_descriptive_flexfield_name => 'GL_JE_LINES',
p_context_name => :$column$.line_dff_context,
p_column_name => 'ATTRIBUTE2',
p_attribute1 => :$column$.gl_je_line_attribute1,
p_attribute2 => :$column$.gl_je_line_attribute2,
p_attribute3 => :$column$.gl_je_line_attribute3,
p_attribute4 => :$column$.gl_je_line_attribute4,
p_attribute5 => :$column$.gl_je_line_attribute5,
p_attribute6 => :$column$.gl_je_line_attribute6,
p_attribute7 => :$column$.gl_je_line_attribute7,
p_attribute8 => :$column$.gl_je_line_attribute8,
p_attribute9 => :$column$.gl_je_line_attribute9,
p_attribute10 => :$column$.gl_je_line_attribute10
)
) lov
order by
valueselect distinct
'(Context) ' || initcap(fdfcuv.application_column_name) || ': ' ||
listagg(decode(fdfc.descriptive_flex_context_code,'Global Data Elements','(Global)','(' || fdfc.descriptive_flex_context_code || ')') || ' ' || fdfcuv.form_left_prompt,', ') within group (order by decode(fdfc.descriptive_flex_context_code,'Global Data Elements',1,2),fdfc.descriptive_flex_context_code) over (partition by fdfcuv.application_column_name)
from
fnd_descriptive_flexs_vl fdfv,
fnd_descr_flex_contexts fdfc,
fnd_descr_flex_col_usage_vl fdfcuv
where
fdfc.application_id = fdfv.application_id and
fdfc.descriptive_flexfield_name = fdfv.descriptive_flexfield_name and
fdfc.enabled_flag = 'Y' and
fdfcuv.application_id = fdfc.application_id and
fdfcuv.descriptive_flexfield_name = fdfc.descriptive_flexfield_name and
fdfcuv.descriptive_flex_context_code = fdfc.descriptive_flex_context_code and
fdfcuv.enabled_flag = 'Y' and
fdfv.application_id = 101 and
fdfv.descriptive_flexfield_name = 'GL_JE_LINES' and
fdfcuv.application_column_name = 'ATTRIBUTE2'60GL_JE_LINE_ATTRIBUTE3LOV customvarchar2Yselect
substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value,
substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description
from
table(
xxen_util.dff_attribute_lov
(p_application_id => 101,
p_descriptive_flexfield_name => 'GL_JE_LINES',
p_context_name => :$column$.line_dff_context,
p_column_name => 'ATTRIBUTE3',
p_attribute1 => :$column$.gl_je_line_attribute1,
p_attribute2 => :$column$.gl_je_line_attribute2,
p_attribute3 => :$column$.gl_je_line_attribute3,
p_attribute4 => :$column$.gl_je_line_attribute4,
p_attribute5 => :$column$.gl_je_line_attribute5,
p_attribute6 => :$column$.gl_je_line_attribute6,
p_attribute7 => :$column$.gl_je_line_attribute7,
p_attribute8 => :$column$.gl_je_line_attribute8,
p_attribute9 => :$column$.gl_je_line_attribute9,
p_attribute10 => :$column$.gl_je_line_attribute10
)
) lov
order by
valueselect distinct
'(Context) ' || initcap(fdfcuv.application_column_name) || ': ' ||
listagg(decode(fdfc.descriptive_flex_context_code,'Global Data Elements','(Global)','(' || fdfc.descriptive_flex_context_code || ')') || ' ' || fdfcuv.form_left_prompt,', ') within group (order by decode(fdfc.descriptive_flex_context_code,'Global Data Elements',1,2),fdfc.descriptive_flex_context_code) over (partition by fdfcuv.application_column_name)
from
fnd_descriptive_flexs_vl fdfv,
fnd_descr_flex_contexts fdfc,
fnd_descr_flex_col_usage_vl fdfcuv
where
fdfc.application_id = fdfv.application_id and
fdfc.descriptive_flexfield_name = fdfv.descriptive_flexfield_name and
fdfc.enabled_flag = 'Y' and
fdfcuv.application_id = fdfc.application_id and
fdfcuv.descriptive_flexfield_name = fdfc.descriptive_flexfield_name and
fdfcuv.descriptive_flex_context_code = fdfc.descriptive_flex_context_code and
fdfcuv.enabled_flag = 'Y' and
fdfv.application_id = 101 and
fdfv.descriptive_flexfield_name = 'GL_JE_LINES' and
fdfcuv.application_column_name = 'ATTRIBUTE3'61GL_JE_LINE_ATTRIBUTE4LOV customvarchar2Yselect
substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value,
substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description
from
table(
xxen_util.dff_attribute_lov
(p_application_id => 101,
p_descriptive_flexfield_name => 'GL_JE_LINES',
p_context_name => :$column$.line_dff_context,
p_column_name => 'ATTRIBUTE4',
p_attribute1 => :$column$.gl_je_line_attribute1,
p_attribute2 => :$column$.gl_je_line_attribute2,
p_attribute3 => :$column$.gl_je_line_attribute3,
p_attribute4 => :$column$.gl_je_line_attribute4,
p_attribute5 => :$column$.gl_je_line_attribute5,
p_attribute6 => :$column$.gl_je_line_attribute6,
p_attribute7 => :$column$.gl_je_line_attribute7,
p_attribute8 => :$column$.gl_je_line_attribute8,
p_attribute9 => :$column$.gl_je_line_attribute9,
p_attribute10 => :$column$.gl_je_line_attribute10
)
) lov
order by
valueselect distinct
'(Context) ' || initcap(fdfcuv.application_column_name) || ': ' ||
listagg(decode(fdfc.descriptive_flex_context_code,'Global Data Elements','(Global)','(' || fdfc.descriptive_flex_context_code || ')') || ' ' || fdfcuv.form_left_prompt,', ') within group (order by decode(fdfc.descriptive_flex_context_code,'Global Data Elements',1,2),fdfc.descriptive_flex_context_code) over (partition by fdfcuv.application_column_name)
from
fnd_descriptive_flexs_vl fdfv,
fnd_descr_flex_contexts fdfc,
fnd_descr_flex_col_usage_vl fdfcuv
where
fdfc.application_id = fdfv.application_id and
fdfc.descriptive_flexfield_name = fdfv.descriptive_flexfield_name and
fdfc.enabled_flag = 'Y' and
fdfcuv.application_id = fdfc.application_id and
fdfcuv.descriptive_flexfield_name = fdfc.descriptive_flexfield_name and
fdfcuv.descriptive_flex_context_code = fdfc.descriptive_flex_context_code and
fdfcuv.enabled_flag = 'Y' and
fdfv.application_id = 101 and
fdfv.descriptive_flexfield_name = 'GL_JE_LINES' and
fdfcuv.application_column_name = 'ATTRIBUTE4'62GL_JE_LINE_ATTRIBUTE5LOV customvarchar2Yselect
substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value,
substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description
from
table(
xxen_util.dff_attribute_lov
(p_application_id => 101,
p_descriptive_flexfield_name => 'GL_JE_LINES',
p_context_name => :$column$.line_dff_context,
p_column_name => 'ATTRIBUTE5',
p_attribute1 => :$column$.gl_je_line_attribute1,
p_attribute2 => :$column$.gl_je_line_attribute2,
p_attribute3 => :$column$.gl_je_line_attribute3,
p_attribute4 => :$column$.gl_je_line_attribute4,
p_attribute5 => :$column$.gl_je_line_attribute5,
p_attribute6 => :$column$.gl_je_line_attribute6,
p_attribute7 => :$column$.gl_je_line_attribute7,
p_attribute8 => :$column$.gl_je_line_attribute8,
p_attribute9 => :$column$.gl_je_line_attribute9,
p_attribute10 => :$column$.gl_je_line_attribute10
)
) lov
order by
valueselect distinct
'(Context) ' || initcap(fdfcuv.application_column_name) || ': ' ||
listagg(decode(fdfc.descriptive_flex_context_code,'Global Data Elements','(Global)','(' || fdfc.descriptive_flex_context_code || ')') || ' ' || fdfcuv.form_left_prompt,', ') within group (order by decode(fdfc.descriptive_flex_context_code,'Global Data Elements',1,2),fdfc.descriptive_flex_context_code) over (partition by fdfcuv.application_column_name)
from
fnd_descriptive_flexs_vl fdfv,
fnd_descr_flex_contexts fdfc,
fnd_descr_flex_col_usage_vl fdfcuv
where
fdfc.application_id = fdfv.application_id and
fdfc.descriptive_flexfield_name = fdfv.descriptive_flexfield_name and
fdfc.enabled_flag = 'Y' and
fdfcuv.application_id = fdfc.application_id and
fdfcuv.descriptive_flexfield_name = fdfc.descriptive_flexfield_name and
fdfcuv.descriptive_flex_context_code = fdfc.descriptive_flex_context_code and
fdfcuv.enabled_flag = 'Y' and
fdfv.application_id = 101 and
fdfv.descriptive_flexfield_name = 'GL_JE_LINES' and
fdfcuv.application_column_name = 'ATTRIBUTE5'63GL_JE_LINE_ATTRIBUTE6LOV customvarchar2Yselect
substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value,
substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description
from
table(
xxen_util.dff_attribute_lov
(p_application_id => 101,
p_descriptive_flexfield_name => 'GL_JE_LINES',
p_context_name => :$column$.line_dff_context,
p_column_name => 'ATTRIBUTE6',
p_attribute1 => :$column$.gl_je_line_attribute1,
p_attribute2 => :$column$.gl_je_line_attribute2,
p_attribute3 => :$column$.gl_je_line_attribute3,
p_attribute4 => :$column$.gl_je_line_attribute4,
p_attribute5 => :$column$.gl_je_line_attribute5,
p_attribute6 => :$column$.gl_je_line_attribute6,
p_attribute7 => :$column$.gl_je_line_attribute7,
p_attribute8 => :$column$.gl_je_line_attribute8,
p_attribute9 => :$column$.gl_je_line_attribute9,
p_attribute10 => :$column$.gl_je_line_attribute10
)
) lov
order by
valueselect distinct
'(Context) ' || initcap(fdfcuv.application_column_name) || ': ' ||
listagg(decode(fdfc.descriptive_flex_context_code,'Global Data Elements','(Global)','(' || fdfc.descriptive_flex_context_code || ')') || ' ' || fdfcuv.form_left_prompt,', ') within group (order by decode(fdfc.descriptive_flex_context_code,'Global Data Elements',1,2),fdfc.descriptive_flex_context_code) over (partition by fdfcuv.application_column_name)
from
fnd_descriptive_flexs_vl fdfv,
fnd_descr_flex_contexts fdfc,
fnd_descr_flex_col_usage_vl fdfcuv
where
fdfc.application_id = fdfv.application_id and
fdfc.descriptive_flexfield_name = fdfv.descriptive_flexfield_name and
fdfc.enabled_flag = 'Y' and
fdfcuv.application_id = fdfc.application_id and
fdfcuv.descriptive_flexfield_name = fdfc.descriptive_flexfield_name and
fdfcuv.descriptive_flex_context_code = fdfc.descriptive_flex_context_code and
fdfcuv.enabled_flag = 'Y' and
fdfv.application_id = 101 and
fdfv.descriptive_flexfield_name = 'GL_JE_LINES' and
fdfcuv.application_column_name = 'ATTRIBUTE6'64GL_JE_LINE_ATTRIBUTE7LOV customvarchar2Yselect
substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value,
substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description
from
table(
xxen_util.dff_attribute_lov
(p_application_id => 101,
p_descriptive_flexfield_name => 'GL_JE_LINES',
p_context_name => :$column$.line_dff_context,
p_column_name => 'ATTRIBUTE7',
p_attribute1 => :$column$.gl_je_line_attribute1,
p_attribute2 => :$column$.gl_je_line_attribute2,
p_attribute3 => :$column$.gl_je_line_attribute3,
p_attribute4 => :$column$.gl_je_line_attribute4,
p_attribute5 => :$column$.gl_je_line_attribute5,
p_attribute6 => :$column$.gl_je_line_attribute6,
p_attribute7 => :$column$.gl_je_line_attribute7,
p_attribute8 => :$column$.gl_je_line_attribute8,
p_attribute9 => :$column$.gl_je_line_attribute9,
p_attribute10 => :$column$.gl_je_line_attribute10
)
) lov
order by
valueselect distinct
'(Context) ' || initcap(fdfcuv.application_column_name) || ': ' ||
listagg(decode(fdfc.descriptive_flex_context_code,'Global Data Elements','(Global)','(' || fdfc.descriptive_flex_context_code || ')') || ' ' || fdfcuv.form_left_prompt,', ') within group (order by decode(fdfc.descriptive_flex_context_code,'Global Data Elements',1,2),fdfc.descriptive_flex_context_code) over (partition by fdfcuv.application_column_name)
from
fnd_descriptive_flexs_vl fdfv,
fnd_descr_flex_contexts fdfc,
fnd_descr_flex_col_usage_vl fdfcuv
where
fdfc.application_id = fdfv.application_id and
fdfc.descriptive_flexfield_name = fdfv.descriptive_flexfield_name and
fdfc.enabled_flag = 'Y' and
fdfcuv.application_id = fdfc.application_id and
fdfcuv.descriptive_flexfield_name = fdfc.descriptive_flexfield_name and
fdfcuv.descriptive_flex_context_code = fdfc.descriptive_flex_context_code and
fdfcuv.enabled_flag = 'Y' and
fdfv.application_id = 101 and
fdfv.descriptive_flexfield_name = 'GL_JE_LINES' and
fdfcuv.application_column_name = 'ATTRIBUTE7'65GL_JE_LINE_ATTRIBUTE8LOV customvarchar2Yselect
substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value,
substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description
from
table(
xxen_util.dff_attribute_lov
(p_application_id => 101,
p_descriptive_flexfield_name => 'GL_JE_LINES',
p_context_name => :$column$.line_dff_context,
p_column_name => 'ATTRIBUTE8',
p_attribute1 => :$column$.gl_je_line_attribute1,
p_attribute2 => :$column$.gl_je_line_attribute2,
p_attribute3 => :$column$.gl_je_line_attribute3,
p_attribute4 => :$column$.gl_je_line_attribute4,
p_attribute5 => :$column$.gl_je_line_attribute5,
p_attribute6 => :$column$.gl_je_line_attribute6,
p_attribute7 => :$column$.gl_je_line_attribute7,
p_attribute8 => :$column$.gl_je_line_attribute8,
p_attribute9 => :$column$.gl_je_line_attribute9,
p_attribute10 => :$column$.gl_je_line_attribute10
)
) lov
order by
valueselect distinct
'(Context) ' || initcap(fdfcuv.application_column_name) || ': ' ||
listagg(decode(fdfc.descriptive_flex_context_code,'Global Data Elements','(Global)','(' || fdfc.descriptive_flex_context_code || ')') || ' ' || fdfcuv.form_left_prompt,', ') within group (order by decode(fdfc.descriptive_flex_context_code,'Global Data Elements',1,2),fdfc.descriptive_flex_context_code) over (partition by fdfcuv.application_column_name)
from
fnd_descriptive_flexs_vl fdfv,
fnd_descr_flex_contexts fdfc,
fnd_descr_flex_col_usage_vl fdfcuv
where
fdfc.application_id = fdfv.application_id and
fdfc.descriptive_flexfield_name = fdfv.descriptive_flexfield_name and
fdfc.enabled_flag = 'Y' and
fdfcuv.application_id = fdfc.application_id and
fdfcuv.descriptive_flexfield_name = fdfc.descriptive_flexfield_name and
fdfcuv.descriptive_flex_context_code = fdfc.descriptive_flex_context_code and
fdfcuv.enabled_flag = 'Y' and
fdfv.application_id = 101 and
fdfv.descriptive_flexfield_name = 'GL_JE_LINES' and
fdfcuv.application_column_name = 'ATTRIBUTE8'66GL_JE_LINE_ATTRIBUTE9LOV customvarchar2Yselect
substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value,
substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description
from
table(
xxen_util.dff_attribute_lov
(p_application_id => 101,
p_descriptive_flexfield_name => 'GL_JE_LINES',
p_context_name => :$column$.line_dff_context,
p_column_name => 'ATTRIBUTE9',
p_attribute1 => :$column$.gl_je_line_attribute1,
p_attribute2 => :$column$.gl_je_line_attribute2,
p_attribute3 => :$column$.gl_je_line_attribute3,
p_attribute4 => :$column$.gl_je_line_attribute4,
p_attribute5 => :$column$.gl_je_line_attribute5,
p_attribute6 => :$column$.gl_je_line_attribute6,
p_attribute7 => :$column$.gl_je_line_attribute7,
p_attribute8 => :$column$.gl_je_line_attribute8,
p_attribute9 => :$column$.gl_je_line_attribute9,
p_attribute10 => :$column$.gl_je_line_attribute10
)
) lov
order by
valueselect distinct
'(Context) ' || initcap(fdfcuv.application_column_name) || ': ' ||
listagg(decode(fdfc.descriptive_flex_context_code,'Global Data Elements','(Global)','(' || fdfc.descriptive_flex_context_code || ')') || ' ' || fdfcuv.form_left_prompt,', ') within group (order by decode(fdfc.descriptive_flex_context_code,'Global Data Elements',1,2),fdfc.descriptive_flex_context_code) over (partition by fdfcuv.application_column_name)
from
fnd_descriptive_flexs_vl fdfv,
fnd_descr_flex_contexts fdfc,
fnd_descr_flex_col_usage_vl fdfcuv
where
fdfc.application_id = fdfv.application_id and
fdfc.descriptive_flexfield_name = fdfv.descriptive_flexfield_name and
fdfc.enabled_flag = 'Y' and
fdfcuv.application_id = fdfc.application_id and
fdfcuv.descriptive_flexfield_name = fdfc.descriptive_flexfield_name and
fdfcuv.descriptive_flex_context_code = fdfc.descriptive_flex_context_code and
fdfcuv.enabled_flag = 'Y' and
fdfv.application_id = 101 and
fdfv.descriptive_flexfield_name = 'GL_JE_LINES' and
fdfcuv.application_column_name = 'ATTRIBUTE9'67GL_JE_LINE_ATTRIBUTE10LOV customvarchar2Yselect
substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value,
substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description
from
table(
xxen_util.dff_attribute_lov
(p_application_id => 101,
p_descriptive_flexfield_name => 'GL_JE_LINES',
p_context_name => :$column$.line_dff_context,
p_column_name => 'ATTRIBUTE10',
p_attribute1 => :$column$.gl_je_line_attribute1,
p_attribute2 => :$column$.gl_je_line_attribute2,
p_attribute3 => :$column$.gl_je_line_attribute3,
p_attribute4 => :$column$.gl_je_line_attribute4,
p_attribute5 => :$column$.gl_je_line_attribute5,
p_attribute6 => :$column$.gl_je_line_attribute6,
p_attribute7 => :$column$.gl_je_line_attribute7,
p_attribute8 => :$column$.gl_je_line_attribute8,
p_attribute9 => :$column$.gl_je_line_attribute9,
p_attribute10 => :$column$.gl_je_line_attribute10
)
) lov
order by
valueselect distinct
'(Context) ' || initcap(fdfcuv.application_column_name) || ': ' ||
listagg(decode(fdfc.descriptive_flex_context_code,'Global Data Elements','(Global)','(' || fdfc.descriptive_flex_context_code || ')') || ' ' || fdfcuv.form_left_prompt,', ') within group (order by decode(fdfc.descriptive_flex_context_code,'Global Data Elements',1,2),fdfc.descriptive_flex_context_code) over (partition by fdfcuv.application_column_name)
from
fnd_descriptive_flexs_vl fdfv,
fnd_descr_flex_contexts fdfc,
fnd_descr_flex_col_usage_vl fdfcuv
where
fdfc.application_id = fdfv.application_id and
fdfc.descriptive_flexfield_name = fdfv.descriptive_flexfield_name and
fdfc.enabled_flag = 'Y' and
fdfcuv.application_id = fdfc.application_id and
fdfcuv.descriptive_flexfield_name = fdfc.descriptive_flexfield_name and
fdfcuv.descriptive_flex_context_code = fdfc.descriptive_flex_context_code and
fdfcuv.enabled_flag = 'Y' and
fdfv.application_id = 101 and
fdfv.descriptive_flexfield_name = 'GL_JE_LINES' and
fdfcuv.application_column_name = 'ATTRIBUTE10'68ATTACHMENT_CATEGORY_LOV customvarchar2Yselect
fdcuv.user_name value,
null description
from
fnd_doc_category_usages_vl fdcuv,
fnd_attachment_functions af,
fnd_document_categories fdc
where
fdcuv.attachment_function_id=af.attachment_function_id and
af.function_name='GLXJEENT' and
fdc.category_id=fdcuv.category_id and
nvl(fdc.start_date_active,sysdate-1)<=sysdate and
nvl(fdc.start_date_active,sysdate+1)>sysdate
order by value69ATTACHMENT_TITLE_Charvarchar270ATTACHMENT_DESCRIPTION_Charvarchar271ATTACHMENT_TYPE_LOV customvarchar2Yselect
fddv.user_name value,
null description
from
fnd_document_datatypes_vl fddv
where
fddv.name in ('SHORT_TEXT','FILE','WEB_PAGE')
order by value72ATTACHMENT_CONTENT_Charvarchar273ATTACHMENT_FILE_ID_Charvarchar2U1p_statusinCharSTATUSU2p_ledger_idinNumberLEDGER_NAMEU3p_accounting_dateinDateACCOUNTING_DATEU4p_currency_codeinCharCURRENCYU5p_date_createdinDateDATE_CREATEDU6p_created_byinNumberCREATED_BYU7p_check_if_balancedinCharYU8p_actual_flaginCharACTUAL_FLAGU9p_user_je_category_nameinCharCATEGORYU10p_user_je_source_nameinCharSOURCEU11p_currency_conversion_dateinDateCONVERSION_DATEU12p_encumbrance_type_idinNumberENCUMBRANCE_TYPEU13p_budget_version_idinNumberBUDGETU14p_user_currency_conv_typeinCharCONVERSION_TYPEU15p_currency_conversion_rateinNumberCONVERSION_RATEU16p_segment1inCharGL_SEGMENT1U17p_segment2inCharGL_SEGMENT2U18p_segment3inCharGL_SEGMENT3U19p_segment4inCharGL_SEGMENT4U20p_segment5inCharGL_SEGMENT5U21p_segment6inCharGL_SEGMENT6U22p_segment7inCharGL_SEGMENT7U23p_segment8inCharGL_SEGMENT8U24p_segment9inCharGL_SEGMENT9U25p_segment10inCharGL_SEGMENT10U26p_segment11inCharGL_SEGMENT11U27p_segment12inCharGL_SEGMENT12U28p_segment13inCharGL_SEGMENT13U29p_segment14inCharGL_SEGMENT14U30p_segment15inCharGL_SEGMENT15U31p_segment16inCharU32p_segment17inCharU33p_segment18inCharU34p_segment19inCharU35p_segment20inCharU36p_segment21inCharU37p_segment22inCharU38p_segment23inCharU39p_segment24inCharU40p_segment25inCharU41p_segment26inCharU42p_segment27inCharU43p_segment28inCharU44p_segment29inCharU45p_segment30inCharU46p_entered_drinNumberDEBITU47p_entered_crinNumberCREDITU48p_accounted_drinNumberU49p_accounted_crinNumberU50p_transaction_dateinDateU51p_reference1inCharBATCH_NAMEU52p_reference2inCharBATCH_DESCRIPTIONU53p_reference3inCharU54p_reference4inCharJOURNAL_NAMEU55p_reference5inCharJOURNAL_DESCRIPTIONU56p_reference6inCharJOURNAL_REFERENCEU57p_reference7inCharREVERSE_JOURNALU58p_reference8inCharREVERSAL_PERIODU59p_reference9inCharU60p_reference10inCharLINE_DESCRIPTIONU61p_reference11inCharU62p_reference12inCharU63p_reference13inCharU64p_reference14inCharU65p_reference15inCharU66p_reference16inCharU67p_reference17inCharU68p_reference18inCharU69p_reference19inCharU70p_reference20inCharU71p_reference21inCharU72p_reference22inCharU73p_reference23inCharU74p_reference24inCharU75p_reference25inCharU76p_reference26inCharU77p_reference27inCharU78p_reference28inCharU79p_reference29inCharU80p_reference30inCharU81p_je_batch_idinNumberU82p_period_nameinCharPERIODU83p_je_header_idinNumberU84p_je_line_numinNumberU85p_chart_of_accounts_idinNumberU86p_functional_currency_codeinCharU87p_code_combination_idinNumberU88p_date_created_in_glinDateU89p_warning_codeinCharU90p_status_descriptioninCharROW_IDU91p_stat_amountinNumberU92p_group_idinNumberU93p_request_idinNumberU94p_subledger_doc_sequence_idinNumberU95p_subledger_doc_sequence_valueinNumberU96p_attribute1inCharGL_JE_LINE_ATTRIBUTE1U97p_attribute2inCharGL_JE_LINE_ATTRIBUTE2U98p_attribute3inCharGL_JE_LINE_ATTRIBUTE3U99p_attribute4inCharGL_JE_LINE_ATTRIBUTE4U100p_attribute5inCharGL_JE_LINE_ATTRIBUTE5U101p_attribute6inCharGL_JE_LINE_ATTRIBUTE6U102p_attribute7inCharGL_JE_LINE_ATTRIBUTE7U103p_attribute8inCharGL_JE_LINE_ATTRIBUTE8U104p_attribute9inCharGL_JE_LINE_ATTRIBUTE9U105p_attribute10inCharGL_JE_LINE_ATTRIBUTE10U106p_attribute11inCharU107p_attribute12inCharU108p_attribute13inCharU109p_attribute14inCharU110p_attribute15inCharU111p_attribute16inCharU112p_attribute17inCharU113p_attribute18inCharU114p_attribute19inCharU115p_attribute20inCharU116p_contextinCharLINE_DFF_CONTEXTU117p_context2inCharU118p_invoice_dateinDateU119p_tax_codeinCharU120p_invoice_identifierinCharU121p_invoice_amountinNumberU122p_context3inCharU123p_ussgl_transaction_codeinCharU124p_descr_flex_error_messageinCharU125p_jgzz_recon_refinCharU126p_average_journal_flaginCharU127p_originating_bal_seg_valueinCharU128p_gl_sl_link_idinNumberU129p_gl_sl_link_tableinCharU130p_reference_dateinDateU131p_balancing_segment_valueinCharU132p_management_segment_valueinCharU133p_code_combination_id_interiminNumberU134p_funds_reserved_flaginCharU135p_ledger_nameinCharYLEDGER_NAMEvalueU136p_account_segmentsinCharYCONCATENATED_SEGMENTSvalueU137p_row_idin/outCharROW_IDU138request_id_outNumberREQUEST_ID_U139status_outCharSTATUS_CODE_U140message_outCharMESSAGE_P1p_je_source_nameinCharSOURCEP2p_user_idinNumberUSER_IDP3p_ledger_idinNumberLEDGER_NAMEP4p_summary_flaginCharYSUMMARY_FLAGP5p_post_to_suspense_flaginCharYPOST_TO_SUSPENSE_FLAGP6p_ledger_user_key1inCharYP7p_ledger_user_key2inCharYP8p_attach_upload_fileinCharYATTACH_UPLOAD_FILEP9p_submit_approvalinCharYSUBMIT_APPROVALP10p_submit_postinginCharYSUBMIT_POSTINGP11x_group_idoutNumberGROUP_IDP12request_id_outNumberREQUEST_ID_P13status_outCharSTATUS_CODE_P14message_outCharMESSAGE_Error SQLselect
xm.action_,
xxen_upload.status_meaning(case when gi.status='PROCESSED' then xxen_upload.status_success
else xxen_upload.status_error end) status_,
nvl(
(
select
listagg(flv.meaning,',') within group (order by instr(','|| gi.status ||',',',' || flv.lookup_code || ',',1,1)) error_code
from
(select
substr(fnm.message_text,1,4) lookup_code,
ltrim(substr(fnm.message_text,7,2000)) meaning
from
fnd_new_messages fnm
where
fnm.application_id = 101 and
fnm.language_code = userenv('lang') and
fnm.message_name like 'R_LEZL%' and
fnm.message_text like 'E___ %'
) flv
where
instr(','|| gi.status ||',',',' || flv.lookup_code || ',',1,1) > 0
),xm.message_) message_,
null request_id_,
xm.modified_columns_,
gi.rowid row_id,
xm.group_id,
xm.ledger_name,
xm.organization,
xm.budget,
nvl(gi.user_je_source_name,xm.source) source,
nvl(gi.user_je_category_name,xm.category) category,
xm.batch_name,
xm.batch_description,
xm.journal_name,
xm.journal_description,
xm.journal_reference,
nvl(gi.currency_code,xm.currency) currency,
nvl(gi.accounting_date,xm.accounting_date) accounting_date,
xm.period,
xm.account_alias,
xm.concatenated_segments,
xm.gl_segment1,
xm.gl_segment2,
xm.gl_segment3,
xm.gl_segment4,
xm.gl_segment5,
xm.gl_segment6,
xm.gl_segment7,
xm.gl_segment8,
xm.gl_segment9,
xm.gl_segment10,
xm.gl_segment11,
xm.gl_segment12,
xm.gl_segment13,
xm.gl_segment14,
xm.gl_segment15,
nvl(gi.entered_dr,to_number(xm.debit)) debit,
nvl(gi.entered_cr,to_number(xm.credit)) credit,
nvl(xxen_util.meaning(gi.reference7,'YES_NO',0),xm.reverse_journal) reverse_journal,
nvl(gi.reference8,xm.reversal_period) reversal_period,
nvl(gi.user_currency_conversion_type,xm.conversion_type) conversion_type,
nvl(gi.currency_conversion_date,xm.conversion_date) conversion_date,
nvl(to_number(gi.currency_conversion_rate),to_number(xm.conversion_rate)) conversion_rate,
xm.line_description,
nvl(gi.actual_flag,xm.actual_flag) actual_flag,
xm.encumbrance_type,
xm.attach_upload_file,
xm.status,
xm.date_created,
to_number(xm.created_by),
to_number(xm.user_id),
xm.summary_flag,
xm.post_to_suspense_flag,
xm.submit_approval,
xm.submit_posting,
--
xm.line_dff_context,
xm.gl_je_line_attribute1,
xm.gl_je_line_attribute2,
xm.gl_je_line_attribute3,
xm.gl_je_line_attribute4,
xm.gl_je_line_attribute5,
xm.gl_je_line_attribute6,
xm.gl_je_line_attribute7,
xm.gl_je_line_attribute8,
xm.gl_je_line_attribute9,
xm.gl_je_line_attribute10,
--
xm.attachment_category_,
xm.attachment_title_,
xm.attachment_description_,
xm.attachment_type_,
xm.attachment_content_,
xm.attachment_file_id_
from
gl_interface gi,
xxen_gl_journal_upload_9275_u xm
where rowidtochar(gi.rowid (+)) = xm.row_id
and (xm.status_code_ != xxen_upload.status_success or nvl(gi.status,'PROCESSED')<>'PROCESSED')Success SQLselect
xxen_upload.action_meaning(xxen_upload.action_create) action_,
xxen_upload.status_meaning(xxen_upload.status_success) status_,
nvl2(xm.message_,xm.message_ || chr(10),null) || 'Journal Name: '||gjh.name||', Batch Name: '||gjb.name message_,
null request_id_,
null modified_columns_,
null row_id,
to_number(null) group_id,
gl.name ledger,
null organization,
gbv.budget_name,
gjsv.user_je_source_name source,
gjcv.user_je_category_name category,
gjb.name batch_name,
gjb.description batch_description,
gjh.name journal_name,
gjh.description journal_description,
gjh.external_reference journal_reference,
gjh.currency_code currency,
gjl.effective_date accounting_date,
gjh.period_name period,
(select
fsfa.alias_name
from
fnd_shorthand_flex_aliases fsfa
where
fsfa.application_id = 101 and
fsfa.id_flex_code = 'GL#' and
fsfa.id_flex_num = gcck.chart_of_accounts_id and
fsfa.concatenated_segments = gcck.concatenated_segments and
fsfa.enabled_flag = 'Y' and
trunc(sysdate) between nvl(fsfa.start_date_active,trunc(sysdate)) and nvl(fsfa.end_date_active,trunc(sysdate)) and
rownum <= 1
) account_alias,
gcck.concatenated_segments account,
gcck.segment1,
gcck.segment2,
gcck.segment3,
gcck.segment4,
gcck.segment5,
gcck.segment6,
gcck.segment7,
gcck.segment8,
gcck.segment9,
gcck.segment10,
gcck.segment11,
gcck.segment12,
gcck.segment13,
gcck.segment14,
gcck.segment15,
gjl.entered_dr,
gjl.entered_cr,
xxen_util.meaning(gjh.accrual_rev_flag,'YES_NO',0) reverse_journal,
gjh.accrual_rev_period_name reversal_period,
gd.user_conversion_type,
gjh.currency_conversion_date conversion_date,
gjh.currency_conversion_rate,
gjl.description journal_line_description,
gjh.actual_flag,
gct.encumbrance_type,
null attach_upload_file,
null status,
to_date(null) date_created,
to_number(null) created_by,
to_number(null) user_id,
null summary_flag,
null post_to_suspense_flag,
null submit_approval,
null submit_posting,
--
xxen_util.display_flexfield_context(101,'GL_JE_LINES',gjl.context) line_dff_context,
xxen_util.display_flexfield_value(101,'GL_JE_LINES',gjl.context,'ATTRIBUTE1',gjl.rowid,gjl.attribute1) gl_je_line_attribute1,
xxen_util.display_flexfield_value(101,'GL_JE_LINES',gjl.context,'ATTRIBUTE2',gjl.rowid,gjl.attribute2) gl_je_line_attribute2,
xxen_util.display_flexfield_value(101,'GL_JE_LINES',gjl.context,'ATTRIBUTE3',gjl.rowid,gjl.attribute3) gl_je_line_attribute3,
xxen_util.display_flexfield_value(101,'GL_JE_LINES',gjl.context,'ATTRIBUTE4',gjl.rowid,gjl.attribute4) gl_je_line_attribute4,
xxen_util.display_flexfield_value(101,'GL_JE_LINES',gjl.context,'ATTRIBUTE5',gjl.rowid,gjl.attribute5) gl_je_line_attribute5,
xxen_util.display_flexfield_value(101,'GL_JE_LINES',gjl.context,'ATTRIBUTE6',gjl.rowid,gjl.attribute6) gl_je_line_attribute6,
xxen_util.display_flexfield_value(101,'GL_JE_LINES',gjl.context,'ATTRIBUTE7',gjl.rowid,gjl.attribute7) gl_je_line_attribute7,
xxen_util.display_flexfield_value(101,'GL_JE_LINES',gjl.context,'ATTRIBUTE8',gjl.rowid,gjl.attribute8) gl_je_line_attribute8,
xxen_util.display_flexfield_value(101,'GL_JE_LINES',gjl.context,'ATTRIBUTE9',gjl.rowid,gjl.attribute9) gl_je_line_attribute9,
xxen_util.display_flexfield_value(101,'GL_JE_LINES',gjl.context,'ATTRIBUTE10',gjl.rowid,gjl.attribute10) gl_je_line_attribute10,
--
xm.attachment_category_,
xm.attachment_title_,
xm.attachment_description_,
xm.attachment_type_,
xxen_upload.check_and_add_attachment(
p_function_name=>'GLXJEENT',
p_function_type=>'O',
p_category_name=>xm.attachment_category_,
p_entity_name=>'GL_JE_HEADERS',
p_data_type=>xm.attachment_type_,
p_title=>xm.attachment_title_,
p_description=>xm.attachment_description_,
p_text=>xm.attachment_content_,
p_file_id=>xm.attachment_file_id_,
p_pk1_value=>gjb.je_batch_id,
p_pk2_value=>gjh.je_header_id
) attachment_content_,
xm.attachment_file_id_
from
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_ledgers gl,
gl_je_sources_v gjsv,
gl_je_categories_v gjcv,
gl_code_combinations_kfv gcck,
gl_encumbrance_types gct,
gl_budgets_v gbv,
gl_daily_conversion_types gd,
fnd_user fu,
(
select distinct
xm.group_id,
xm.ledger_name,
xm.source,
xm.category,
xm.currency,
xm.period,
max(xm.message_) keep (dense_rank first order by xm.upload_row) over (partition by xm.ledger_name,xm.source,xm.category,xm.currency,xm.period) message_,
max(xm.attachment_category_) keep (dense_rank first order by xm.upload_row) over (partition by xm.ledger_name,xm.source,xm.category,xm.currency,xm.period) attachment_category_,
max(xm.attachment_title_) keep (dense_rank first order by xm.upload_row) over (partition by xm.ledger_name,xm.source,xm.category,xm.currency,xm.period) attachment_title_,
max(xm.attachment_description_) keep (dense_rank first order by xm.upload_row) over (partition by xm.ledger_name,xm.source,xm.category,xm.currency,xm.period) attachment_description_,
max(xm.attachment_type_) keep (dense_rank first order by xm.upload_row) over (partition by xm.ledger_name,xm.source,xm.category,xm.currency,xm.period) attachment_type_,
max(xm.attachment_content_) keep (dense_rank first order by xm.upload_row) over (partition by xm.ledger_name,xm.source,xm.category,xm.currency,xm.period) attachment_content_,
max(xm.attachment_file_id_) keep (dense_rank first order by xm.upload_row) over (partition by xm.ledger_name,xm.source,xm.category,xm.currency,xm.period) attachment_file_id_
from
xxen_gl_journal_upload_9275_u xm
where status_<>xxen_upload.status_error
and ( not exists ( select 1 from gl_interface where rowidtochar(rowid) = xm.row_id ) or exists ( select 1 from gl_interface where rowidtochar(rowid) = xm.row_id and status='PROCESSED'))
) xm
where gjb.group_id = xm.group_id
and gjb.je_batch_id = gjh.je_batch_id
and gjh.je_header_id = gjl.je_header_id
and gjh.je_source = gjsv.je_source_name
and gjh.je_category = gjcv.je_category_name
and gjh.encumbrance_type_id = gct.encumbrance_type_id(+)
and gjh.budget_version_id = gbv.budget_version_id(+)
and gl.ledger_id = gjh.ledger_id
and fu.user_id = gjl.created_by
and nvl(gjh.currency_conversion_type,'!')=gd.conversion_type(+)
and gcck.code_combination_id = gjl.code_combination_id
and gjsv.user_je_source_name=xm.source
and gjcv.user_je_category_name=xm.category
and gjh.currency_code=xm.currency
and gjh.period_name=xm.period
and gl.name=xm.ledger_name