[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
IF p_mode = hr_suit_match_utility_web.g_select_people_work_mode THEN
fnd_message.set_name('PER', 'HR_WEB_SM_MENU_1');
SELECT meaning
FROM hr_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;
SELECT meaning
FROM fnd_common_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;
'SELECT max(count(step_value))'
||' FROM per_competence_levels_v'
||' WHERE competence_id IN ('||l_ids||')'
||' GROUP BY competence_id';
'SELECT distinct ppf.person_id';
PROCEDURE keyflex_select_where_clause
(p_business_group_id in number
,p_keyflex_code in varchar2
,p_filter_clause in varchar2 default null
,p_select_clause out nocopy varchar2
,p_where_clause out nocopy varchar2) IS
l_mapped_col_names hr_util_misc_web.g_varchar2_tab_type;
IF p_select_clause IS null THEN
p_select_clause := l_table_short_name
||l_segment_separator
||l_mapped_col_names(i);
p_select_clause := p_select_clause
||'||'
||l_table_short_name
||l_segment_separator
||l_mapped_col_names(i);
IF p_select_clause IS NOT null THEN
p_select_clause := p_select_clause ||' name';
p_select_clause := substr(l_table_short_name,1,2)||'.name name';
END keyflex_select_where_clause;
SELECT step_value
FROM per_competence_levels_v
WHERE rating_level_id = p_rating_level_id;
SELECT name
FROM per_competences_tl
WHERE competence_id = p_competence_id
AND language = userenv('LANG') ;
SELECT organization_id, job_id
FROM hr_positions_f
WHERE position_id = p_pos_id
AND TRUNC(SYSDATE) BETWEEN effective_start_date
AND effective_end_date;
l_flex_select_clause varchar2(2000);
keyflex_select_where_clause
(p_business_group_id => l_business_group
,p_keyflex_code => 'GRD'
,p_select_clause => l_flex_select_clause
,p_where_clause => l_flex_where_clause);
l_dynamic_sql := 'SELECT distinct(pvg.valid_grade_id),'
||l_flex_select_clause
||' FROM per_valid_grades pvg,'
||' per_grades pg, per_grade_definitions pgd'
||' WHERE pvg.job_id = '||p_id
||' AND pvg.grade_id = pg.grade_id'
||' AND pg.grade_definition_id = pgd.grade_definition_id';
l_dynamic_sql := 'SELECT distinct(pvg.valid_grade_id),'
||l_flex_select_clause
||' FROM per_valid_grades pvg,'
||' per_grades pg, per_grade_definitions pgd'
||' WHERE pvg.position_id = '||p_id
||' AND pvg.grade_id = pg.grade_id'
||' AND pg.grade_definition_id = pgd.grade_definition_id';
l_flex_select_clause varchar2(2000);
l_dynamic_sql := 'SELECT location_id, location_code'
||' FROM hr_locations_all'
||' WHERE location_id IN ('
||p_ids
||')';
l_dynamic_sql := 'SELECT organization_id, name'
||' FROM hr_organization_units'
||' WHERE organization_id IN ('
||p_ids
||')';
keyflex_select_where_clause
(p_business_group_id => l_business_group
,p_keyflex_code => 'JOB'
,p_select_clause => l_flex_select_clause
,p_where_clause => l_flex_where_clause);
l_dynamic_sql := 'SELECT pj.job_id,'
||l_flex_select_clause
||' FROM per_jobs_vl pj, per_job_definitions pjd'
||' WHERE pj.job_definition_id = pjd.job_definition_id'
||' AND pj.job_id IN ('
||p_ids
||')';
keyflex_select_where_clause
(p_business_group_id => l_business_group
,p_keyflex_code => 'POS'
,p_select_clause => l_flex_select_clause
,p_where_clause => l_flex_where_clause);
l_dynamic_sql := 'SELECT pp.position_id,'
||l_flex_select_clause
||' FROM hr_positions_f pp, per_position_definitions ppd'
||' WHERE TRUNC(SYSDATE) BETWEEN pp.effective_start_date'
||' AND pp.effective_end_date'
||' AND pp.position_definition_id = ppd.position_definition_id'
||' AND pp.position_id IN ('
||p_ids
||')';
l_dynamic_sql := 'SELECT pv.vacancy_id'
||',pv.name || '' (''||pr.name||'')'' name'
||' FROM per_vacancies pv, per_requisitions pr'
||' WHERE pv.vacancy_id IN ('
||p_ids
||')'
||' AND pv.requisition_id = pr.requisition_id';
keyflex_select_where_clause
(p_business_group_id => l_business_group
,p_keyflex_code => 'GRD'
,p_select_clause => l_flex_select_clause
,p_where_clause => l_flex_where_clause);
l_dynamic_sql := 'SELECT pg.grade_id,'
||l_flex_select_clause
||' FROM per_grades pg, per_grade_definitions pgd'
||' WHERE pg.grade_definition_id = pgd.grade_definition_id'
||' AND pg.grade_id IN ('
||p_ids
||')';
l_dynamic_sql := 'SELECT activity_version_id, version_name'
||' FROM ota_activity_versions'
||' WHERE activity_version_id IN ('
||p_ids
||')';
l_dynamic_sql := 'SELECT ppf.person_id';
SELECT system_person_type
FROM per_person_types
WHERE person_type_id = p_person_type_id;
SELECT person_type_id
FROM per_person_types
WHERE (system_person_type = 'EMP' )
AND business_group_id = p_business_group_id;
SELECT person_type_id
FROM per_person_types
WHERE system_person_type = 'APL'
AND business_group_id = p_business_group_id;
l_job_flex_select_clause varchar2(2000);
l_pos_flex_select_clause varchar2(2000);
l_grd_flex_select_clause varchar2(2000);
keyflex_select_where_clause
(p_business_group_id => l_business_group
,p_keyflex_code => 'JOB'
,p_filter_clause => l_filter_clause
,p_select_clause => l_job_flex_select_clause
,p_where_clause => l_job_flex_where_clause);
keyflex_select_where_clause
(p_business_group_id => l_business_group
,p_keyflex_code => 'POS'
,p_filter_clause => l_filter_clause
,p_select_clause => l_pos_flex_select_clause
,p_where_clause => l_pos_flex_where_clause);
keyflex_select_where_clause
(p_business_group_id => l_business_group
,p_keyflex_code => 'GRD'
,p_filter_clause => l_filter_clause
,p_select_clause => l_grd_flex_select_clause
,p_where_clause => l_grd_flex_where_clause);
l_dynamic_sql := 'SELECT DISTINCT(paf.location_id) id'
||' ,hl.location_code name'
||' FROM per_assignments_f paf'
||' ,per_people_f ppf'
||' ,per_person_type_usages_f ptu'
||' ,hr_locations_all hl'
||' WHERE paf.business_group_id = '
||l_business_group
||' AND paf.assignment_type = '||l_assignment_type
||' AND ppf.person_id = ptu.person_id '
||' AND ppf.person_type_id IN '||l_person_type_ids
||' AND TRUNC(sysdate) BETWEEN ptu.effective_start_date '
||' AND ptu.effective_end_date'
||' AND TRUNC(sysdate) BETWEEN ppf.effective_start_date'
||' AND ppf.effective_end_date'
||' AND ppf.person_id = paf.person_id'
||' AND TRUNC(sysdate) BETWEEN paf.effective_start_date'
||' AND paf.effective_end_date'
||' AND paf.location_id = hl.location_id'
||' AND UPPER(hl.location_code) '
|| l_filter_clause;
l_dynamic_sql := 'SELECT DISTINCT(paf.organization_id) id'
||' ,hou.name name'
||' FROM per_assignments_f paf'
||' ,per_people_f ppf'
||' ,per_person_type_usages_f ptu'
||' ,hr_organization_units hou'
||' WHERE paf.business_group_id = '
||l_business_group
||' AND paf.assignment_type = '||l_assignment_type
||' AND ppf.person_id = ptu.person_id '
||' AND ppf.person_type_id IN '||l_person_type_ids
||' AND TRUNC(sysdate) between ptu.effective_start_date'
||' AND ptu.effective_end_date'
||' AND TRUNC(sysdate) BETWEEN paf.effective_start_date'
||' AND paf.effective_end_date'
||' AND ppf.person_id = paf.person_id'
||' AND TRUNC(sysdate) BETWEEN ppf.effective_start_date'
||' AND ppf.effective_end_date'
||' AND paf.organization_id = hou.organization_id'
||' AND UPPER(hou.name) '
|| l_filter_clause;
l_dynamic_sql := 'SELECT DISTINCT(paf.job_id) id'
||','|| l_job_flex_select_clause
||' FROM per_assignments_f paf'
||' ,per_people_f ppf'
||' ,per_person_type_usages_f ptu'
||' ,per_jobs_vl pj'
||' ,per_job_definitions pjd'
||' WHERE paf.business_group_id = '
||l_business_group
||' AND paf.assignment_type = '||l_assignment_type
||' AND ppf.person_id = ptu.person_id '
||' AND ppf.person_type_id IN '||l_person_type_ids
||' AND TRUNC(sysdate) between ptu.effective_start_date'
||' AND ptu.effective_end_date'
||' AND TRUNC(sysdate) BETWEEN paf.effective_start_date'
||' AND paf.effective_end_date'
||' AND ppf.person_id = paf.person_id'
||' AND TRUNC(sysdate) BETWEEN ppf.effective_start_date'
||' AND ppf.effective_end_date'
||' AND paf.job_id = pj.job_id'
||' AND pj.job_definition_id = pjd.job_definition_id'
||' AND '
||l_job_flex_where_clause;
l_dynamic_sql := 'SELECT DISTINCT(paf.position_id) id'
||','|| l_pos_flex_select_clause
||' FROM per_assignments_f paf'
||' ,per_people_f ppf'
||' ,per_person_type_usages_f ptu'
||' ,hr_positions_f pp'
||' ,per_position_definitions ppd'
||' WHERE paf.business_group_id = '
||l_business_group
||' AND paf.assignment_type = '||l_assignment_type
||' AND ppf.person_id = ptu.person_id '
||' AND ppf.person_type_id IN '||l_person_type_ids
||' AND TRUNC(sysdate) between ptu.effective_start_date'
||' AND ptu.effective_end_date'
||' AND TRUNC(sysdate) BETWEEN paf.effective_start_date'
||' AND paf.effective_end_date'
||' AND ppf.person_id = paf.person_id'
||' AND TRUNC(sysdate) BETWEEN ppf.effective_start_date'
||' AND ppf.effective_end_date'
||' AND paf.position_id = pp.position_id'
||' AND TRUNC(SYSDATE) BETWEEN pp.effective_start_date'
||' AND pp.effective_end_date'
||' AND pp.position_definition_id = ppd.position_definition_id'
||' AND (pp.status is null OR pp.status <> ''INVALID'')'
||' AND '
||l_pos_flex_where_clause;
l_dynamic_sql := 'SELECT DISTINCT(paf.vacancy_id) id'
||',pv.name || '' (''||pr.name||'')'' name'
||' FROM per_assignments_f paf'
||' ,per_people_f ppf'
||' ,per_person_type_usages_f ptu'
||' ,per_vacancies pv'
||' ,per_requisitions pr'
||' WHERE paf.business_group_id = '
||l_business_group
||' AND paf.assignment_type = '||l_assignment_type
||' AND ppf.person_id = ptu.person_id '
||' AND ppf.person_type_id IN '||l_person_type_ids
||' AND TRUNC(sysdate) between ptu.effective_start_date'
||' AND ptu.effective_end_date'
||' AND TRUNC(sysdate) BETWEEN paf.effective_start_date'
||' AND paf.effective_end_date'
||' AND ppf.person_id = paf.person_id'
||' AND TRUNC(sysdate) BETWEEN ppf.effective_start_date'
||' AND ppf.effective_end_date'
||' AND paf.vacancy_id = pv.vacancy_id'
||' AND pv.requisition_id = pr.requisition_id'
||' AND UPPER(pv.name) '
|| l_filter_clause;
l_dynamic_sql := 'SELECT DISTINCT(paf.grade_id) id'
||','|| l_grd_flex_select_clause
||' FROM per_assignments_f paf'
||' ,per_people_f ppf'
||' ,per_person_type_usages_f ptu'
||' ,per_grades pg'
||' ,per_grade_definitions pgd'
||' WHERE paf.business_group_id = '
||l_business_group
||' AND paf.assignment_type = '||l_assignment_type
||' AND ppf.person_id = ptu.person_id '
||' AND ppf.person_type_id IN '||l_person_type_ids
||' AND TRUNC(sysdate) between ptu.effective_start_date'
||' AND ptu.effective_end_date'
||' AND TRUNC(sysdate) BETWEEN paf.effective_start_date'
||' AND paf.effective_end_date'
||' AND ppf.person_id = paf.person_id'
||' AND TRUNC(sysdate) BETWEEN ppf.effective_start_date'
||' AND ppf.effective_end_date'
||' AND paf.grade_id = pg.grade_id'
||' AND pg.grade_definition_id = pgd.grade_definition_id'
||' AND '
||l_grd_flex_where_clause;
l_dynamic_sql := 'SELECT location_id, location_code'
||' FROM hr_locations_all'
||' WHERE UPPER(location_code) '
|| l_filter_clause;
l_dynamic_sql := 'SELECT distinct(hou.organization_id), hou.name'
||' FROM hr_organization_units hou,'
||' hr_organization_information hoi'
||' WHERE hou.business_group_id = '
||l_business_group
||' AND TRUNC(sysdate) BETWEEN hou.date_from AND'
||' NVL(hou.date_to, TRUNC(sysdate))'
||' AND hou.organization_id = hoi.organization_id'
||' AND hoi.org_information_context = ''CLASS'''
||' AND hoi.org_information1 IN (''HR_BG'',''HR_ORG'')'
||' AND UPPER(hou.name) '
|| l_filter_clause;
l_dynamic_sql := 'SELECT job_id, name'
||' FROM per_jobs_vl'
||' WHERE business_group_id = '
||l_business_group
||' AND TRUNC(sysdate) BETWEEN date_from AND'
||' NVL(date_to,TRUNC(sysdate))'
||' AND UPPER(name) '
|| l_filter_clause;
l_dynamic_sql := 'SELECT position_id, name'
||' FROM hr_positions_f'
||' WHERE business_group_id = '
||l_business_group
||' AND TRUNC(sysdate) BETWEEN effective_start_date'
||' AND effective_end_date'
||' AND TRUNC(sysdate) BETWEEN date_effective AND'
||' NVL(date_end, TRUNC(sysdate))'
||' AND (status is null OR status <> ''INVALID'')'
||' AND UPPER(name) '
|| l_filter_clause;
l_dynamic_sql := 'SELECT pv.vacancy_id'
||',pv.name || '' (''||pr.name||'')'' name'
||' FROM per_vacancies pv, per_requisitions pr'
||' WHERE pv.business_group_id = '
||l_business_group
||' AND TRUNC(sysdate) BETWEEN pv.date_from AND'
||' NVL(pv.date_to, TRUNC(sysdate))'
||' AND UPPER(pv.name) '
|| l_filter_clause
||' AND pv.requisition_id = pr.requisition_id';
SELECT hou.name, hl.location_code
FROM hr_organization_units hou,
hr_locations_all hl
WHERE hou.organization_id = p_id
AND hou.location_id = hl.location_id(+);
SELECT hou.name, hl.location_code
FROM hr_positions_f pp,
hr_organization_units hou,
hr_locations_all hl
WHERE pp.position_id = p_id
AND TRUNC(SYSDATE) BETWEEN pp.effective_start_date
AND pp.effective_end_date
AND pp.organization_id = hou.organization_id
AND hou.location_id = hl.location_id(+);
SELECT pv.name || ' (' || pr.name ||')'
,hou.name
,hl.location_code
FROM per_vacancies pv,
per_requisitions pr,
hr_organization_units hou,
hr_locations_all hl
WHERE pv.vacancy_id = p_id
AND pv.requisition_id = pr.requisition_id
AND pv.organization_id = hou.organization_id(+)
AND hou.location_id = hl.location_id(+);
SELECT business_group_id
,organization_id
,job_id
,position_id
,grade_id
FROM per_vacancies
WHERE vacancy_id = p_vacancy_id;
SELECT pce.competence_element_id,
pce.effective_date_from,
pce.proficiency_level_id,
pce.high_proficiency_level_id,
pce.mandatory,
cpl.competence_id,
cpl.name,
r1.step_value low_step_value,
r2.step_value high_step_value,
rtx1.name low_step_name,
rtx2.name high_step_name
FROM per_competence_elements pce,
per_competences_tl cpl,
per_rating_levels r1,
per_rating_levels r2,
per_rating_levels_tl rtx1,
per_rating_levels_tl rtx2
WHERE pce.ENTERPRISE_ID = p_core_id
AND pce.type = 'REQUIREMENT'
AND pce.organization_id IS null
AND pce.job_id IS null
AND pce.position_id IS null
AND p_effective_date BETWEEN pce.effective_date_from AND
NVL(pce.effective_date_to, p_effective_date)
AND pce.competence_id = cpl.competence_id
AND cpl.language = userenv('LANG')
AND pce.proficiency_level_id = r1.rating_level_id(+)
AND pce.high_proficiency_level_id = r2.rating_level_id(+)
AND pce.proficiency_level_id = rtx1.rating_level_id(+)
AND pce.high_proficiency_level_id = rtx2.rating_level_id(+)
AND rtx1.language(+) = userenv('LANG')
AND rtx2.language(+) = userenv('LANG')
ORDER BY cpl.name;
SELECT pce.competence_element_id,
pce.effective_date_from,
pce.proficiency_level_id,
pce.high_proficiency_level_id,
pce.mandatory,
cpl.competence_id,
cpl.name,
r1.step_value low_step_value,
rtx1.name low_step_name,
r2.step_value high_step_value,
rtx2.name high_step_name
FROM per_competence_elements pce,
per_competences_tl cpl,
per_rating_levels r1,
per_rating_levels r2,
per_rating_levels_tl rtx1,
per_rating_levels_tl rtx2
WHERE pce.organization_id = p_org_id
AND pce.type = 'REQUIREMENT'
AND p_effective_date BETWEEN pce.effective_date_from AND
NVL(pce.effective_date_to, p_effective_date)
AND pce.competence_id = cpl.competence_id
AND cpl.language = userenv('LANG')
AND pce.proficiency_level_id = r1.rating_level_id(+)
AND pce.high_proficiency_level_id = r2.rating_level_id(+)
AND pce.proficiency_level_id = rtx1.rating_level_id(+)
AND pce.high_proficiency_level_id = rtx2.rating_level_id(+)
AND rtx1.language(+) = userenv('LANG')
AND rtx2.language(+) = userenv('LANG')
ORDER BY cpl.name;
SELECT pce.competence_element_id,
pce.effective_date_from,
pce.proficiency_level_id,
pce.high_proficiency_level_id,
pce.mandatory,
cpl.competence_id,
cpl.name,
r1.step_value low_step_value,
rtx1.name low_step_name,
r2.step_value high_step_value,
rtx2.name high_step_name
FROM per_competence_elements pce,
per_competences_tl cpl,
per_rating_levels r1,
per_rating_levels r2,
per_rating_levels_tl rtx1,
per_rating_levels_tl rtx2
WHERE pce.job_id = p_job_id
AND (pce.valid_grade_id IS null
-- OR pce.valid_grade_id = p_grade_id)
OR pce.valid_grade_id in (select pvg.valid_grade_id
from per_valid_grades pvg
where pvg.grade_id = p_grade_id
and trunc(sysdate) between pvg.DATE_FROM and
nvl(pvg.DATE_TO, trunc(sysdate))))
AND pce.type = 'REQUIREMENT'
AND p_effective_date BETWEEN pce.effective_date_from AND
NVL(pce.effective_date_to, p_effective_date)
AND pce.competence_id = cpl.competence_id
AND cpl.language = userenv('LANG')
AND pce.proficiency_level_id = r1.rating_level_id(+)
AND pce.high_proficiency_level_id = r2.rating_level_id(+)
AND pce.proficiency_level_id = rtx1.rating_level_id(+)
AND pce.high_proficiency_level_id = rtx2.rating_level_id(+)
AND rtx1.language(+) = userenv('LANG')
AND rtx2.language(+) = userenv('LANG')
ORDER BY cpl.name;
SELECT pce.competence_element_id,
pce.effective_date_from,
pce.proficiency_level_id,
pce.high_proficiency_level_id,
pce.mandatory,
cpl.competence_id,
cpl.name,
r1.step_value low_step_value,
rtx1.name low_step_name,
r2.step_value high_step_value,
rtx2.name high_step_name
FROM per_competence_elements pce,
per_competences_tl cpl,
per_rating_levels r1,
per_rating_levels r2,
per_rating_levels_tl rtx1,
per_rating_levels_tl rtx2
WHERE pce.position_id = p_pos_id
AND (pce.valid_grade_id IS null
-- OR pce.valid_grade_id = p_grade_id)
OR pce.valid_grade_id in (select pvg.valid_grade_id
from per_valid_grades pvg
where pvg.grade_id = p_grade_id
and trunc(sysdate) between pvg.DATE_FROM and
nvl(pvg.DATE_TO, trunc(sysdate))))
AND pce.type = 'REQUIREMENT'
AND p_effective_date BETWEEN pce.effective_date_from AND
NVL(pce.effective_date_to, p_effective_date)
AND pce.competence_id = cpl.competence_id
AND cpl.language = userenv('LANG')
AND pce.proficiency_level_id = r1.rating_level_id(+)
AND pce.high_proficiency_level_id = r2.rating_level_id(+)
AND pce.proficiency_level_id = rtx1.rating_level_id(+)
AND pce.high_proficiency_level_id = rtx2.rating_level_id(+)
AND rtx1.language(+) = userenv('LANG')
AND rtx2.language(+) = userenv('LANG')
ORDER BY cpl.name;
SELECT cpl.competence_id,
pce.competence_element_id,
pce.effective_date_from,
pce.proficiency_level_id,
pce.high_proficiency_level_id,
pce.mandatory,
cpl.name,
r1.step_value low_step_value,
rtx1.name low_step_name,
r2.step_value high_step_value,
rtx2.name high_step_name
FROM per_competence_elements pce,
per_competences_tl cpl,
per_rating_levels r1,
per_rating_levels r2,
per_rating_levels_tl rtx1,
per_rating_levels_tl rtx2
WHERE pce.person_id = p_person_id
AND pce.type = 'PERSONAL'
AND p_effective_date BETWEEN pce.effective_date_from AND
NVL(pce.effective_date_to, p_effective_date)
AND pce.competence_id = cpl.competence_id
AND cpl.language = userenv('LANG')
AND pce.proficiency_level_id = r1.rating_level_id(+)
AND pce.high_proficiency_level_id = r2.rating_level_id(+)
AND pce.proficiency_level_id = rtx1.rating_level_id(+)
AND pce.high_proficiency_level_id = rtx2.rating_level_id(+)
AND rtx1.language(+) = userenv('LANG')
AND rtx2.language(+) = userenv('LANG')
ORDER BY cpl.name;
SELECT distinct(paf.person_id) person_id
,ppf.full_name name
,hr_person_type_usage_info.get_user_person_type
(p_effective_date, paf.person_id) type
,ppf.order_name
FROM per_assignments_f paf
,per_people_f ppf
WHERE paf.vacancy_id = p_vacancy_id
AND p_effective_date BETWEEN paf.effective_start_date
AND NVL(paf.effective_end_date, p_effective_date)
AND paf.person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date
AND NVL(ppf.effective_end_date, p_effective_date)
ORDER BY NVL(ppf.order_name,ppf.full_name);
SELECT distinct(paf.person_id) person_id
,ppf.full_name name
,hr_person_type_usage_info.get_user_person_type
(p_effective_date, paf.person_id) type
,ppf.last_name
,ppf.first_name
FROM per_assignments_f paf
,per_people_f ppf
WHERE paf.vacancy_id = p_vacancy_id
AND p_effective_date BETWEEN paf.effective_start_date
AND NVL(paf.effective_end_date, p_effective_date)
AND paf.person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date
AND NVL(ppf.effective_end_date, p_effective_date)
ORDER BY ppf.last_name, ppf.first_name;
SELECT pasf.vacancy_id
,pv.name ||' ('||pr.name||')' name
FROM per_assignments_f pasf,
per_assignment_status_types past,
per_vacancies pv,
per_requisitions pr
WHERE pasf.person_id = p_person_id
AND p_effective_date BETWEEN pasf.effective_start_date
AND pasf.effective_end_date
AND pasf.vacancy_id = pv.vacancy_id
AND pv.requisition_id = pr.requisition_id
AND pasf.assignment_status_type_id = past.assignment_status_type_id
AND pasf.assignment_type = 'A'
AND past.per_system_status in ('ACTIVE_APL');
SELECT * FROM (
SELECT psp.position_id, pp.name
FROM per_succession_planning psp,
hr_positions_f pp
WHERE psp.person_id = p_person_id
AND p_effective_date <= NVL(psp.end_date, p_effective_date)
AND psp.position_id = pp.position_id
AND TRUNC(p_effective_date) BETWEEN pp.effective_start_date
AND pp.effective_end_date
UNION
SELECT pp.position_id, pp.name
FROM hr_positions_f pp
WHERE pp.position_id = (SELECT pp2.successor_position_id
FROM hr_positions_f pp2,
per_assignments_f paf
WHERE paf.person_id = p_person_id
AND p_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.position_id = pp2.position_id
AND p_effective_date BETWEEN pp2.effective_start_date
AND pp2.effective_end_date)
AND TRUNC(p_effective_date) BETWEEN pp.effective_start_date
AND pp.effective_end_date)
ORDER BY name;
SELECT pasf.assignment_id, pasf.position_id, pasf.grade_id, pp.name
FROM per_assignments_f pasf,
per_assignment_status_types past,
hr_positions_f pp
WHERE pasf.person_id = p_person_id
AND p_effective_date BETWEEN pasf.effective_start_date
AND pasf.effective_end_date
AND pasf.position_id = pp.position_id
AND TRUNC(SYSDATE) BETWEEN pp.effective_start_date
AND pp.effective_end_date
--AND pasf.vacancy_id is null
AND pasf.assignment_status_type_id = past.assignment_status_type_id
AND (pasf.assignment_type = 'E' AND
past.per_system_status in ('ACTIVE_ASSIGN')
OR pasf.assignment_type = 'A' AND
past.per_system_status in ('ACCEPTED', 'OFFER', 'ACTIVE_APL'));
SELECT pasf.assignment_id, pasf.job_id, pasf.grade_id, pj.name
FROM per_assignments_f pasf,
per_assignment_status_types past,
per_jobs_vl pj
WHERE pasf.person_id = p_person_id
AND p_effective_date BETWEEN pasf.effective_start_date
AND pasf.effective_end_date
AND pasf.job_id = pj.job_id
AND pasf.position_id is null
-- AND pasf.vacancy_id is null
AND pasf.assignment_status_type_id = past.assignment_status_type_id
AND (pasf.assignment_type = 'E' AND
past.per_system_status in ('ACTIVE_ASSIGN')
OR pasf.assignment_type = 'A' AND
past.per_system_status in ('ACCEPTED', 'OFFER', 'ACTIVE_APL'));
SELECT pasf.assignment_id, pasf.organization_id, hou.name
FROM per_assignments_f pasf,
per_assignment_status_types past,
hr_organization_units hou
WHERE pasf.person_id = p_person_id
AND p_effective_date BETWEEN pasf.effective_start_date
AND pasf.effective_end_date
AND pasf.organization_id = hou.organization_id
AND pasf.position_id is null
AND pasf.job_id is null
-- AND pasf.vacancy_id is null
AND pasf.assignment_status_type_id = past.assignment_status_type_id
AND (pasf.assignment_type = 'E' AND
past.per_system_status in ('ACTIVE_ASSIGN')
OR pasf.assignment_type = 'A' AND
past.per_system_status in ('ACCEPTED', 'OFFER', 'ACTIVE_APL'));
SELECT pasf.assignment_id
,pasf.vacancy_id
,pv.name||' ('||pr.name||')' name
FROM per_assignments_f pasf,
per_assignment_status_types past,
per_vacancies pv,
per_requisitions pr
WHERE pasf.person_id = p_person_id
AND p_effective_date BETWEEN pasf.effective_start_date
AND pasf.effective_end_date
AND pasf.vacancy_id = pv.vacancy_id
AND pv.requisition_id = pr.requisition_id
AND pasf.assignment_status_type_id = past.assignment_status_type_id
AND pasf.assignment_type = 'A'
AND past.per_system_status in ('ACCEPTED', 'OFFER', 'ACTIVE_APL');
SELECT * FROM (
SELECT distinct(psp.person_id) person_id
,ppf.full_name name
,hr_person_type_usage_info.get_user_person_type
(p_effective_date, ppf.person_id) type
,ppf.order_name
FROM per_succession_planning psp
,per_people_f ppf
WHERE psp.position_id = p_pos_id
AND p_effective_date <=
NVL(psp.end_date, p_effective_date)
AND psp.person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
UNION
select paf.person_id
,ppf.full_name name
,hr_person_type_usage_info.get_user_person_type
(p_effective_date, ppf.person_id) type
,ppf.order_name
from hr_positions_f ps,
per_assignments_f paf
,per_people_f ppf
where ps.successor_position_id = p_pos_id
AND p_effective_date BETWEEN ps.effective_start_date
AND ps.effective_end_date
and paf.position_id = ps.position_id
AND paf.person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND p_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date)
ORDER BY NVL(order_name,name);
SELECT * FROM (
SELECT distinct(psp.person_id) person_id
,ppf.full_name name
,hr_person_type_usage_info.get_user_person_type
(p_effective_date, ppf.person_id) type
,ppf.last_name
,ppf.first_name
FROM per_succession_planning psp
,per_people_f ppf
WHERE psp.position_id = p_pos_id
AND p_effective_date <=
NVL(psp.end_date, p_effective_date)
AND psp.person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date
AND NVL(ppf.effective_end_date, p_effective_date)
UNION
select paf.person_id
,ppf.full_name name
,hr_person_type_usage_info.get_user_person_type
(p_effective_date, ppf.person_id) type
,ppf.last_name
,ppf.first_name
from hr_positions_f ps,
per_assignments_f paf
,per_people_f ppf
where ps.successor_position_id = p_pos_id
AND p_effective_date BETWEEN ps.effective_start_date
AND ps.effective_end_date
and paf.position_id = ps.position_id
AND paf.person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND p_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date)
ORDER BY last_name, first_name;
'SELECT distinct(paf.person_id) person_id';
||' (SELECT pvg.grade_id'
||' FROM per_valid_grades pvg'
||' WHERE pvg.valid_grade_id = '
||p_grade_id||')';
||' (SELECT pvg.grade_id'
||' FROM per_valid_grades pvg'
||' WHERE pvg.valid_grade_id = '
||p_grade_id||')';
||' (SELECT pvg.grade_id'
||' FROM per_valid_grades pvg'
||' WHERE pvg.valid_grade_id = '
||p_grade_id||')';
||' (SELECT pvg.grade_id'
||' FROM per_valid_grades pvg'
||' WHERE pvg.valid_grade_id = '
||p_grade_id||')';
SELECT distinct(paf.person_id) person_id
,ppf.full_name name
,hr_person_type_usage_info.get_user_person_type
(p_effective_date, ppf.person_id) type
,ppf.order_name
FROM per_assignments_f paf
,per_people_f ppf
WHERE paf.organization_id = p_org_id
AND paf.assignment_type = 'E'
AND p_effective_date BETWEEN paf.effective_start_date
AND NVL(paf.effective_end_date, p_effective_date)
AND paf.person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date
AND NVL(ppf.effective_end_date, p_effective_date)
ORDER BY NVL(ppf.order_name,ppf.full_name);
SELECT distinct(paf.person_id) person_id
,ppf.full_name name
,hr_person_type_usage_info.get_user_person_type
(p_effective_date,ppf.person_id) type
,ppf.last_name
,ppf.first_name
FROM per_assignments_f paf
,per_people_f ppf
WHERE paf.organization_id = p_org_id
AND paf.assignment_type = 'E'
AND p_effective_date BETWEEN paf.effective_start_date
AND NVL(paf.effective_end_date, p_effective_date)
AND paf.person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date
AND NVL(ppf.effective_end_date, p_effective_date)
ORDER BY ppf.last_name, ppf.first_name;
SELECT distinct(paf.person_id) person_id
,ppf.full_name name
,hr_person_type_usage_info.get_user_person_type
(p_effective_date,ppf.person_id) type
,ppf.order_name
FROM per_assignments_f paf
,per_people_f ppf
WHERE paf.job_id = p_job_id
AND paf.assignment_type = 'E'
AND (p_grade_id is null or paf.grade_id = p_grade_id)
AND p_effective_date BETWEEN paf.effective_start_date
AND NVL(paf.effective_end_date, p_effective_date)
AND paf.person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date
AND NVL(ppf.effective_end_date, p_effective_date)
ORDER BY NVL(ppf.order_name,ppf.full_name);
SELECT distinct(paf.person_id) person_id
,ppf.full_name name
,hr_person_type_usage_info.get_user_person_type
(p_effective_date,ppf.person_id) type
,ppf.last_name
,ppf.first_name
FROM per_assignments_f paf
,per_people_f ppf
WHERE paf.job_id = p_job_id
AND paf.assignment_type = 'E'
AND (p_grade_id is null or paf.grade_id = p_grade_id)
AND p_effective_date BETWEEN paf.effective_start_date
AND NVL(paf.effective_end_date, p_effective_date)
AND paf.person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date
AND NVL(ppf.effective_end_date, p_effective_date)
ORDER BY ppf.last_name, ppf.first_name;
SELECT distinct(paf.person_id) person_id
,ppf.full_name name
,hr_person_type_usage_info.get_user_person_type
(p_effective_date,ppf.person_id) type
,ppf.order_name
FROM per_assignments_f paf
,per_people_f ppf
WHERE paf.position_id = p_pos_id
AND paf.assignment_type = 'E'
AND (p_grade_id is null or paf.grade_id = p_grade_id)
AND p_effective_date BETWEEN paf.effective_start_date
AND NVL(paf.effective_end_date, p_effective_date)
AND paf.person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date
AND NVL(ppf.effective_end_date, p_effective_date)
ORDER BY NVL(ppf.order_name,ppf.full_name);
SELECT distinct(paf.person_id) person_id
,ppf.full_name name
,hr_person_type_usage_info.get_user_person_type
(p_effective_date,ppf.person_id) type
,ppf.last_name
,ppf.first_name
FROM per_assignments_f paf
,per_people_f ppf
WHERE paf.position_id = p_pos_id
AND paf.assignment_type = 'E'
AND (p_grade_id is null or paf.grade_id = p_grade_id)
AND p_effective_date BETWEEN paf.effective_start_date
AND NVL(paf.effective_end_date, p_effective_date)
AND paf.person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date
AND NVL(ppf.effective_end_date, p_effective_date)
ORDER BY ppf.last_name, ppf.first_name;
SELECT distinct(oe.activity_version_id) activity_version_id
,oav.version_name
FROM ota_events oe
,ota_delegate_bookings odb
,ota_activity_versions oav
WHERE odb.delegate_person_id = p_person_id
AND odb.event_id = oe.event_id
AND oe.activity_version_id = oav.activity_version_id;
SELECT odb.delegate_person_id person_id
FROM ota_delegate_bookings odb
WHERE odb.event_id IN
(SELECT oe.event_id
FROM ota_events oe
WHERE oe.activity_version_id = p_activity_version_id);
SELECT prl.rating_level_id, prl.step_value, rtx.name
FROM per_rating_levels prl
,per_competences pc
,per_rating_levels_tl rtx
WHERE pc.competence_id = p_competence_id
AND pc.rating_scale_id = prl.rating_scale_id
AND prl.rating_level_id = rtx.rating_level_id
AND rtx.language = userenv('LANG')
ORDER BY step_value;
SELECT prl.rating_level_id, prl.step_value, rtx.name
FROM per_rating_levels prl
,per_competences pc
,per_rating_levels_tl rtx
WHERE pc.competence_id = p_competence_id
AND pc.competence_id = prl.competence_id
AND prl.rating_level_id = rtx.rating_level_id
AND rtx.language = userenv('LANG')
ORDER BY step_value;