Parameter Name |
SQL text |
Validation |
Ledger |
|
LOV |
Ledger Category |
gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'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(+)) |
gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'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(+))
|
LOV |
Period |
|
LOV |
Show Full Year |
select distinct
decode(gps.adjustment_period_flag,'Y','adj'||decode(gps.row_number,1,null,gps.row_number)||'_')||to_char(gps.start_date+8,'mon_YYYY','nls_date_language=american')||',' pivot_text,
gps.effective_period_num
from
gl_ledgers gl,
(select row_number() over (partition by gps.application_id, gps.ledger_id, gps.period_year, gps.adjustment_period_flag order by gps.period_num) row_number, gps.* from gl_period_statuses gps) gps
where
(gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') or xxen_util.contains(:ledger,gl.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
gps.period_name=:period_name and
gl.ledger_id=gps.ledger_id and
gps.application_id=101 |
select distinct
decode(gps.adjustment_period_flag,'Y','adj'||decode(gps.row_number,1,null,gps.row_number)||'_')||to_char(gps.start_date+8,'mon_YYYY','nls_date_language=american')||',' pivot_text,
gps.effective_period_num
from
gl_ledgers gl,
(select row_number() over (partition by gps.application_id, gps.ledger_id, gps.period_year, gps.adjustment_period_flag order by gps.period_num) row_number, gps.* from gl_period_statuses gps) gps
where
(gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') or xxen_util.contains(:ledger,gl.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
gps.period_name=:period_name and
gl.ledger_id=gps.ledger_id and
gps.application_id=101
|
LOV Oracle |
Show Company |
select distinct
lower(fsav.application_column_name)||',' text
from
fnd_segment_attribute_values fsav
where
:dummy=:dummy and
fsav.application_id=101 and
fsav.id_flex_code='GL#' and
fsav.segment_attribute_type='GL_BALANCING' and
fsav.attribute_value='Y' and
fsav.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where (gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') or xxen_util.contains(:ledger,gl.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(+))) |
select distinct
lower(fsav.application_column_name)||',' text
from
fnd_segment_attribute_values fsav
where
:dummy=:dummy and
fsav.application_id=101 and
fsav.id_flex_code='GL#' and
fsav.segment_attribute_type='GL_BALANCING' and
fsav.attribute_value='Y' and
fsav.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where (gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') or xxen_util.contains(:ledger,gl.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(+)))
|
LOV |
Show Account |
select distinct
lower(fsav.application_column_name)||',' text
from
fnd_segment_attribute_values fsav
where
:dummy=:dummy and
fsav.application_id=101 and
fsav.id_flex_code='GL#' and
fsav.segment_attribute_type='GL_ACCOUNT' and
fsav.attribute_value='Y' and
fsav.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where (gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') or xxen_util.contains(:ledger,gl.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(+))) |
select distinct
lower(fsav.application_column_name)||',' text
from
fnd_segment_attribute_values fsav
where
:dummy=:dummy and
fsav.application_id=101 and
fsav.id_flex_code='GL#' and
fsav.segment_attribute_type='GL_ACCOUNT' and
fsav.attribute_value='Y' and
fsav.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where (gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') or xxen_util.contains(:ledger,gl.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(+)))
|
LOV |
Show Cost Center |
select distinct
lower(fsav.application_column_name)||',' text
from
fnd_segment_attribute_values fsav
where
:dummy=:dummy and
fsav.application_id=101 and
fsav.id_flex_code='GL#' and
fsav.segment_attribute_type='FA_COST_CTR' and
fsav.attribute_value='Y' and
fsav.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where (gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') or xxen_util.contains(:ledger,gl.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(+))) |
select distinct
lower(fsav.application_column_name)||',' text
from
fnd_segment_attribute_values fsav
where
:dummy=:dummy and
fsav.application_id=101 and
fsav.id_flex_code='GL#' and
fsav.segment_attribute_type='FA_COST_CTR' and
fsav.attribute_value='Y' and
fsav.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where (gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') or xxen_util.contains(:ledger,gl.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(+)))
|
LOV |
Show Intercompany |
select distinct
lower(fsav.application_column_name)||',' text
from
fnd_segment_attribute_values fsav
where
:dummy=:dummy and
fsav.application_id=101 and
fsav.id_flex_code='GL#' and
fsav.segment_attribute_type='GL_INTERCOMPANY' and
fsav.attribute_value='Y' and
fsav.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where (gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') or xxen_util.contains(:ledger,gl.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(+))) |
select distinct
lower(fsav.application_column_name)||',' text
from
fnd_segment_attribute_values fsav
where
:dummy=:dummy and
fsav.application_id=101 and
fsav.id_flex_code='GL#' and
fsav.segment_attribute_type='GL_INTERCOMPANY' and
fsav.attribute_value='Y' and
fsav.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where (gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') or xxen_util.contains(:ledger,gl.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(+)))
|
LOV |
Show All Segments |
select 'concatenated_segments,' text from dual union all
select x.* from (
select
lower(fifsv.application_column_name)||',' text
from
fnd_id_flex_segments_vl fifsv
where
:dummy=:dummy and
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where (gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') or xxen_util.contains(:ledger,gl.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
rownum=1)
order by
fifsv.segment_num
) x |
select 'concatenated_segments,' text from dual union all
select x.* from (
select
lower(fifsv.application_column_name)||',' text
from
fnd_id_flex_segments_vl fifsv
where
:dummy=:dummy and
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where (gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') or xxen_util.contains(:ledger,gl.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
rownum=1)
order by
fifsv.segment_num
) x
|
LOV |
Summary Template |
gb.template_id=(select gst.template_id from gl_summary_templates gst, gl_ledgers gl where gst.ledger_id = gl.ledger_id and gst.template_name=:summary_template and gl.name=:ledger) |
gb.template_id=(select gst.template_id from gl_summary_templates gst, gl_ledgers gl where gst.ledger_id = gl.ledger_id and gst.template_name=:summary_template and gl.name=:ledger)
|
LOV |
Show Account Type |
|
LOV |
Account Type |
gcck.gl_account_type=xxen_util.lookup_code(:account_type,'ACCOUNT_TYPE',0) |
gcck.gl_account_type=xxen_util.lookup_code(:account_type,'ACCOUNT_TYPE',0)
|
LOV |
Concatenated Segments |
gcck.concatenated_segments=:concatenated_segments |
gcck.concatenated_segments=:concatenated_segments
|
Char |
GL_SEGMENT1 |
|
LOV |
GL_SEGMENT1 From |
gcck.segment1>=:segment1_from |
gcck.segment1>=:segment1_from
|
LOV |
GL_SEGMENT1 To |
gcck.segment1<=:segment1_to |
gcck.segment1<=:segment1_to
|
LOV |
GL_SEGMENT2 |
|
LOV |
GL_SEGMENT2 From |
gcck.segment2>=:segment2_from |
gcck.segment2>=:segment2_from
|
LOV |
GL_SEGMENT2 To |
gcck.segment2<=:segment2_to |
gcck.segment2<=:segment2_to
|
LOV |
GL_SEGMENT3 |
|
LOV |
GL_SEGMENT3 From |
gcck.segment3>=:segment3_from |
gcck.segment3>=:segment3_from
|
LOV |
GL_SEGMENT3 To |
gcck.segment3<=:segment3_to |
gcck.segment3<=:segment3_to
|
LOV |
GL_SEGMENT4 |
|
LOV |
GL_SEGMENT4 From |
gcck.segment4>=:segment4_from |
gcck.segment4>=:segment4_from
|
LOV |
GL_SEGMENT4 To |
gcck.segment4<=:segment4_to |
gcck.segment4<=:segment4_to
|
LOV |
GL_SEGMENT5 |
|
LOV |
GL_SEGMENT5 From |
gcck.segment5>=:segment5_from |
gcck.segment5>=:segment5_from
|
LOV |
GL_SEGMENT5 To |
gcck.segment5<=:segment5_to |
gcck.segment5<=:segment5_to
|
LOV |
GL_SEGMENT6 |
|
LOV |
GL_SEGMENT6 From |
gcck.segment6>=:segment6_from |
gcck.segment6>=:segment6_from
|
LOV |
GL_SEGMENT6 To |
gcck.segment6<=:segment6_to |
gcck.segment6<=:segment6_to
|
LOV |
GL_SEGMENT7 |
|
LOV |
GL_SEGMENT7 From |
gcck.segment7>=:segment7_from |
gcck.segment7>=:segment7_from
|
LOV |
GL_SEGMENT7 To |
gcck.segment7<=:segment7_to |
gcck.segment7<=:segment7_to
|
LOV |
GL_SEGMENT8 |
|
LOV |
GL_SEGMENT8 From |
gcck.segment8>=:segment8_from |
gcck.segment8>=:segment8_from
|
LOV |
GL_SEGMENT8 To |
gcck.segment8<=:segment8_to |
gcck.segment8<=:segment8_to
|
LOV |
GL_SEGMENT9 |
|
LOV |
GL_SEGMENT9 From |
gcck.segment9>=:segment9_from |
gcck.segment9>=:segment9_from
|
LOV |
GL_SEGMENT9 To |
gcck.segment9<=:segment9_to |
gcck.segment9<=:segment9_to
|
LOV |
GL_SEGMENT10 |
gcck.segment10=:segment10 |
gcck.segment10=:segment10
|
LOV |
GL_SEGMENT10 From |
gcck.segment10>=:segment10_from |
gcck.segment10>=:segment10_from
|
LOV |
GL_SEGMENT10 To |
gcck.segment10<=:segment10_to |
gcck.segment10<=:segment10_to
|
LOV |
Revaluation Currency |
|
LOV |
Revaluation Conversion Type |
|
LOV |
Balance Type |
|
LOV |
Budget Name |
gb.budget_version_id in (select gbv.budget_version_id from gl_budget_versions gbv where gbv.budget_name = :budget_name) |
gb.budget_version_id in (select gbv.budget_version_id from gl_budget_versions gbv where gbv.budget_name = :budget_name)
|
LOV |
Encumbrance Type |
gb.encumbrance_type_id in (select get.encumbrance_type_id from gl_encumbrance_types get where get.encumbrance_type = :encumbrance_type) |
gb.encumbrance_type_id in (select get.encumbrance_type_id from gl_encumbrance_types get where get.encumbrance_type = :encumbrance_type)
|
LOV |
Exclude Inactive |
(y.start_balance<>0 or y.abs_total>0) |
(y.start_balance<>0 or y.abs_total>0)
|
LOV |
Batch |
gcck.code_combination_id in
(select
gjl.code_combination_id
from
gl_je_lines gjl,
gl_je_headers gjh,
gl_je_batches gjb
where
gjl.je_header_id = gjh.je_header_id and
gjh.je_batch_id = gjb.je_batch_id and
gjb.name = :batch_name
) |
gcck.code_combination_id in
(select
gjl.code_combination_id
from
gl_je_lines gjl,
gl_je_headers gjh,
gl_je_batches gjb
where
gjl.je_header_id = gjh.je_header_id and
gjh.je_batch_id = gjb.je_batch_id and
gjb.name = :batch_name
)
|
LOV |
Journal |
gcck.code_combination_id in
(select
gjl.code_combination_id
from
gl_je_lines gjl,
gl_je_headers gjh
where
gjl.je_header_id = gjh.je_header_id and
gjh.name = :journal_name
) |
gcck.code_combination_id in
(select
gjl.code_combination_id
from
gl_je_lines gjl,
gl_je_headers gjh
where
gjl.je_header_id = gjh.je_header_id and
gjh.name = :journal_name
)
|
LOV |