[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
110.9 08-FEB-99 LTAYLOR Changed and updated MLS and Date
formats for release 11.5
110.10 09-FEB-99 LSIGRIST Updated function get_element_name
with MLS changes.
115.4 11-MAY-99 CCARTER 886635 Replaced chr() function calls with
calls to fnd_global.local_chr();
SELECT name
INTO l_budget_name
FROM per_budgets
WHERE budget_id = p_budget_id;
SELECT version_number
INTO l_budget_version_number
FROM per_budget_versions
WHERE budget_id = p_budget_id
AND budget_version_id = p_budget_version_id;
select orgtl.name,
hrl.meaning
into p_org_name,
p_org_type
from hr_all_organization_units_tl orgtl,
hr_all_organization_units org,
hr_lookups hrl
where org.organization_id = p_organization_id
and org.organization_id = orgtl.organization_id
and hrl.lookup_type (+) = 'ORG_TYPE'
and hrl.lookup_code (+) = org.type
and orgtl.LANGUAGE = userenv('LANG');
SELECT name
INTO l_job_name
FROM per_jobs_vl
WHERE job_id = p_job_id;
SELECT name
INTO l_position_name
FROM hr_positions
WHERE position_id = p_position_id;
SELECT name
INTO l_position_name
FROM hr_positions_f paf
WHERE paf.position_id = p_position_id
AND p_effective_date between paf.effective_start_date and paf.effective_end_date;
SELECT name
INTO l_grade_name
FROM per_grades_vl
WHERE grade_id = p_grade_id;
SELECT nvl(btl.user_status,atl.user_status)
INTO l_user_status
from per_assignment_status_types_tl atl
, per_assignment_status_types a
, per_ass_status_type_amends_tl btl
, per_ass_status_type_amends b
where b.assignment_status_type_id(+) = a.assignment_status_type_id
and a.assignment_status_type_id = atl.assignment_status_type_id
and b.ass_status_type_amend_id = btl.ass_status_type_amend_id(+)
and a.assignment_status_type_id = P_ASSIGNMENT_STATUS_TYPE_ID
and b.business_group_id(+) + 0 = P_BUSINESS_GROUP_ID
and nvl(a.business_group_id,P_BUSINESS_GROUP_ID) =
P_BUSINESS_GROUP_ID
and nvl(a.legislation_code, P_LEGISLATION_CODE) =
P_LEGISLATION_CODE
and nvl(b.active_flag,a.active_flag) = 'Y'
and nvl(b.default_flag,a.active_flag) = 'Y'
and decode(btl.ass_status_type_amend_id, NULL, '1', btl.language)
= decode(btl.ass_status_type_amend_id, NULL, '1', userenv('LANG'))
and atl.language = userenv('LANG');
SELECT name
INTO l_abs_name
FROM per_abs_attendance_types_vl
WHERE absence_attendance_type_id = p_abs_att_type_id;
SELECT period_name
, start_date
, end_date
INTO p_period_name
, p_start_date
, p_end_date
FROM per_time_periods
WHERE time_period_id = p_time_period_id;
select etl.element_name
into v_element_name
from pay_element_types_f_tl etl,
pay_element_types_f e
where e.element_type_id = p_element_type_id
and p_session_date between
e.effective_start_date and
e.effective_end_date
and e.element_type_id = etl.element_type_id
and etl.LANGUAGE = userenv('LANG');
select p.payroll_name
into v_payroll_name
from pay_payrolls_f p
where payroll_id = p_payroll_id
and p_session_date between
p.effective_start_date and
p.effective_end_date;
select org.name
into v_business_group_name
from hr_all_organization_units_tl org
-- where org.organization_id + 0 = p_business_group_id
-- Changed for Performance Fix: Bug 4328224
where org.organization_id = p_business_group_id
and org.language(+) = userenv('LANG');
select nvl(count(*),0)
into v_subordinate_count
from per_org_structure_elements ose
connect by ose.organization_id_parent = prior ose.organization_id_child
and ose.org_structure_version_id = p_org_structure_version_id
start with ose.organization_id_parent = p_parent_organization_id
and ose.org_structure_version_id = p_org_structure_version_id;
select nvl(count(*),0)
into v_subordinate_count
from per_pos_structure_elements pse
connect by pse.parent_position_id = prior pse.subordinate_position_id
and pse.pos_structure_version_id = p_pos_structure_version_id
start with pse.parent_position_id = p_parent_position_id
and pse.pos_structure_version_id = p_pos_structure_version_id;
select ost.name
into p_org_structure_name
from per_organization_structures ost
where ost.organization_structure_id = p_organization_structure_id;
select ost.name,
osv.version_number,
osv.date_from,
osv.date_to
into p_org_structure_name,
p_org_version,
p_version_start_date,
p_version_end_date
from per_organization_structures ost,
per_org_structure_versions osv
where osv.org_structure_version_id = p_org_structure_version_id
and ost.organization_structure_id = osv.organization_structure_id;
select pst.name
into p_pos_structure_name
from per_position_structures pst
where pst.position_structure_id = p_position_structure_id;
select pst.name,
psv.version_number,
psv.date_from,
psv.date_to
into p_pos_structure_name,
p_pos_version,
p_version_start_date,
p_version_end_date
from per_position_structures pst,
per_pos_structure_versions psv
where psv.pos_structure_version_id = p_pos_structure_version_id
and pst.position_structure_id = psv.position_structure_id;
select hrl.meaning
into v_meaning
from hr_lookups hrl
where hrl.lookup_type = p_lookup_type
and hrl.lookup_code = p_lookup_code;
select sign(count(*))
into l_number_matching
from per_person_analyses p
where p.person_id = P_PERSON_ID
and exists
(select null
from per_job_requirements j
, per_analysis_criteria ja
, per_analysis_criteria pa
where ((P_JOB_POSITION_TYPE = 'J' and
j.job_id = P_JOB_POSITION_ID)
or (P_JOB_POSITION_TYPE = 'P' and
j.position_id = P_JOB_POSITION_ID))
and j.essential = P_ESSENTIAL_FLAG
and j.analysis_criteria_id = ja.analysis_criteria_id
and p.analysis_criteria_id = pa.analysis_criteria_id
and p.analysis_criteria_id = ja.analysis_criteria_id
and ja.id_flex_num = pa.id_flex_num
and ((ja.segment1 is null or
(ja.segment1 is not null and
ja.segment1 = pa.segment1))
or (ja.segment2 is null or
(ja.segment2 is not null and
ja.segment2 = pa.segment2))
or (ja.segment3 is null or
(ja.segment3 is not null and
ja.segment3 = pa.segment3))
or (ja.segment4 is null or
(ja.segment4 is not null and
ja.segment4 = pa.segment4))
or (ja.segment5 is null or
(ja.segment5 is not null and
ja.segment5 = pa.segment5))
or (ja.segment6 is null or
(ja.segment6 is not null and
ja.segment6 = pa.segment6))
or (ja.segment7 is null or
(ja.segment7 is not null and
ja.segment7 = pa.segment7))
or (ja.segment8 is null or
(ja.segment8 is not null and
ja.segment8 = pa.segment8))
or (ja.segment9 is null or
(ja.segment9 is not null and
ja.segment9 = pa.segment9))
or (ja.segment10 is null or
(ja.segment10 is not null and
ja.segment10 = pa.segment10))
or (ja.segment11 is null or
(ja.segment11 is not null and
ja.segment11 = pa.segment11))
or (ja.segment12 is null or
(ja.segment12 is not null and
ja.segment12 = pa.segment12))
or (ja.segment13 is null or
(ja.segment13 is not null and
ja.segment13 = pa.segment13))
or (ja.segment14 is null or
(ja.segment14 is not null and
ja.segment14 = pa.segment14))
or (ja.segment15 is null or
(ja.segment15 is not null and
ja.segment15 = pa.segment15))
or (ja.segment16 is null or
(ja.segment16 is not null and
ja.segment16 = pa.segment16))
or (ja.segment17 is null or
(ja.segment17 is not null and
ja.segment17 = pa.segment17))
or (ja.segment18 is null or
(ja.segment18 is not null and
ja.segment18 = pa.segment18))
or (ja.segment19 is null or
(ja.segment19 is not null and
ja.segment19 = pa.segment19))
or (ja.segment20 is null or
(ja.segment20 is not null and
ja.segment20 = pa.segment20))
or (ja.segment21 is null or
(ja.segment21 is not null and
ja.segment21 = pa.segment21))
or (ja.segment22 is null or
(ja.segment22 is not null and
ja.segment22 = pa.segment22))
or (ja.segment23 is null or
(ja.segment23 is not null and
ja.segment23 = pa.segment23))
or (ja.segment24 is null or
(ja.segment24 is not null and
ja.segment24 = pa.segment24))
or (ja.segment25 is null or
(ja.segment25 is not null and
ja.segment25 = pa.segment25))
or (ja.segment26 is null or
(ja.segment26 is not null and
ja.segment26 = pa.segment26))
or (ja.segment27 is null or
(ja.segment27 is not null and
ja.segment27 = pa.segment27))
or (ja.segment28 is null or
(ja.segment28 is not null and
ja.segment28 = pa.segment28))
or (ja.segment29 is null or
(ja.segment29 is not null and
ja.segment29 = pa.segment29))
or (ja.segment30 is null or
(ja.segment30 is not null and
ja.segment30 = pa.segment30)))
)
having count(*) >= P_NUMBER_REQUIRED;
select DISTINCT CONCATENATED_SEGMENT_DELIMITER
into l_seg_sep
from fnd_id_flex_structures_vl
where ID_FLEX_NUM = P_ID_FLEX_NUM
and ID_FLEX_CODE=NVL(p_id_flex_code,ID_FLEX_CODE)
and APPLICATION_ID = nvl(p_application_id,APPLICATION_ID);
select application_column_name
from fnd_id_flex_segments_vl
where id_flex_num = p_id_flex_num
and id_flex_code = nvl(p_id_flex_code,id_flex_code)
and enabled_flag = 'Y'
order by segment_num;
select CONCATENATED_SEGMENT_DELIMITER
into l_seg_sep
from FND_DESCRIPTIVE_FLEXS_VL
where DESCRIPTIVE_FLEXFIELD_NAME = P_TITLE;
select 1 order_col,
dfcu.column_seq_num order_col2,
dfcu.application_column_name column_name,
replace(dfcu.form_left_prompt,'''','''''') label
from fnd_descr_flex_contexts dfc,
fnd_descr_flex_col_usage_vl dfcu
where dfc.descriptive_flexfield_name = p_desc_flex_name
and dfc.application_id = p_application_id
and dfc.global_flag = 'Y'
and dfc.enabled_flag = 'Y'
and dfcu.descriptive_flex_context_code = dfc.descriptive_flex_context_code
and dfcu.descriptive_flexfield_name = p_desc_flex_name
and dfcu.application_id = p_application_id
and dfcu.enabled_flag = 'Y'
UNION
select distinct
2 order_col,
1 order_col2,
dfcu.application_column_name column_name,
replace(dfcu.form_left_prompt,'''','''''') label
from fnd_descr_flex_contexts dfc,
fnd_descr_flex_col_usage_vl dfcu
where dfc.descriptive_flexfield_name = p_desc_flex_name
and dfc.application_id = p_application_id
and dfc.global_flag = 'N'
and dfc.enabled_flag = 'Y'
and dfcu.descriptive_flex_context_code = dfc.descriptive_flex_context_code
and dfcu.descriptive_flexfield_name = p_desc_flex_name
and dfcu.application_id = p_application_id
and dfcu.enabled_flag = 'Y'
order by 1,2,3;
select app.application_id
into v_application_id
from fnd_application app
where upper(app.application_short_name) = upper(p_appl_short_name);
select df.concatenated_segment_delimiter,
df.title
into v_delimiter,
v_title
from fnd_descriptive_flexs_vl df
where df.descriptive_flexfield_name = p_desc_flex_name
and df.application_id = v_application_id;
select 1 order_col,
dfcu.column_seq_num order_col2,
dfcu.application_column_name column_name,
replace(dfcu.form_left_prompt,'''','''''') label,
'Y' global_flag,
dfc.descriptive_flex_context_code context
from fnd_descr_flex_contexts dfc,
fnd_descr_flex_col_usage_vl dfcu
where dfc.descriptive_flexfield_name = p_desc_flex_name
and dfc.application_id = p_application_id
and dfc.global_flag = 'Y'
and dfc.enabled_flag = 'Y'
and dfcu.descriptive_flex_context_code = dfc.descriptive_flex_context_code
and dfcu.descriptive_flexfield_name = p_desc_flex_name
and dfcu.application_id = p_application_id
and dfcu.enabled_flag = 'Y'
and dfcu.display_flag = NVL(p_display,dfcu.display_flag)
UNION
select distinct
2 order_col,
1 order_col2,
dfcu.application_column_name column_name,
replace(dfcu.form_left_prompt,'''','''''') label,
'N' global_flag,
dfc.descriptive_flex_context_code context
from fnd_descr_flex_contexts dfc,
fnd_descr_flex_col_usage_vl dfcu
where dfc.descriptive_flexfield_name = p_desc_flex_name
and dfc.application_id = p_application_id
and dfc.global_flag = 'N'
and dfc.enabled_flag = 'Y'
and dfcu.descriptive_flex_context_code = dfc.descriptive_flex_context_code
and dfcu.descriptive_flexfield_name = p_desc_flex_name
and dfcu.application_id = p_application_id
and dfcu.enabled_flag = 'Y'
and dfcu.display_flag = NVL(p_display,dfcu.display_flag)
order by 1,6,2;
select app.application_id
into v_application_id
from fnd_application app
where upper(app.application_short_name) = upper(p_appl_short_name);
select df.concatenated_segment_delimiter,
df.title
into v_delimiter,
v_title
from fnd_descriptive_flexs_vl df
where df.descriptive_flexfield_name = p_desc_flex_name
and df.application_id = v_application_id;
select 1 order_col,
dfcu.column_seq_num order_col2,
dfcu.application_column_name column_name,
replace(dfcu.form_left_prompt,'''','''''') label
from fnd_descr_flex_contexts dfc,
fnd_descr_flex_col_usage_vl dfcu
where dfc.descriptive_flexfield_name = p_desc_flex_name
and dfc.application_id = p_application_id
and dfc.global_flag = 'Y'
and dfc.enabled_flag = 'Y'
and dfcu.descriptive_flex_context_code = dfc.descriptive_flex_context_code
and dfcu.descriptive_flexfield_name = p_desc_flex_name
and dfcu.application_id = p_application_id
and dfcu.enabled_flag = 'Y'
UNION
select distinct
2 order_col,
dfcu.column_seq_num order_col2,
dfcu.application_column_name column_name,
replace(dfcu.form_left_prompt,'''','''''') label
from fnd_descr_flex_contexts dfc,
fnd_descr_flex_col_usage_vl dfcu
where dfc.descriptive_flexfield_name = p_desc_flex_name
and dfc.application_id = p_application_id
and dfc.descriptive_flex_context_code = p_desc_flex_context
and dfc.global_flag = 'N'
and dfc.enabled_flag = 'Y'
and dfcu.descriptive_flex_context_code = dfc.descriptive_flex_context_code
and dfcu.descriptive_flexfield_name = p_desc_flex_name
and dfcu.application_id = p_application_id
and dfcu.enabled_flag = 'Y'
order by 1,2;
select app.application_id
into v_application_id
from fnd_application app
where upper(app.application_short_name) = upper(p_appl_short_name);
select df.concatenated_segment_delimiter,
df.title
into v_delimiter,
v_title
from fnd_descriptive_flexs_vl df
where df.descriptive_flexfield_name = p_desc_flex_name
and df.application_id = v_application_id;
select p.full_name
into v_person_name
from per_all_people_f p
where p.person_id = p_person_id
and p_session_date between p.effective_start_date
and p.effective_end_date;
select party_number
into l_party_number
from hz_parties
where party_id = p_party_id;