[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT appraisal_id,
overall_performance_level_id,
appraisal_date,
appraisal_system_status
FROM per_appraisals
WHERE appraisee_person_id = p_person_id AND appraisal_date <= TRUNC (SYSDATE)
ORDER BY appraisal_date DESC;
SELECT step_value || ' - ' || NAME "APPRAISAL_RATING"
FROM per_rating_levels
WHERE rating_level_id = l_overall_performance_level_id;
SELECT meaning
FROM hr_lookups
WHERE lookup_type = 'APPRAISAL_SYSTEM_STATUS' AND lookup_code = l_appraisal_system_status;
SELECT DISTINCT 'Y' AS if_current
FROM per_appraisal_periods
WHERE plan_id = c_plan_id
AND TRUNC (SYSDATE) BETWEEN NVL (task_start_date, SYSDATE)
AND NVL (task_end_date, SYSDATE);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 'X'
FROM per_objectives
WHERE aligned_with_objective_id = c_obj_id); -- 8789635 bug fix changes
SELECT DISTINCT 'Y' AS enabled
FROM per_objectives
WHERE objective_id = c_align_id;
SELECT supervisor_id
INTO l_personid
FROM per_perf_mgmt_plans
WHERE plan_id = p_planid;
SELECT potential
FROM (SELECT pei_information1 potential,
fnd_date.canonical_to_date (pei_information5) start_date,
fnd_date.canonical_to_date (pei_information6) end_date,
fnd_date.canonical_to_date (pei_information8) completion_date
FROM per_people_extra_info
WHERE person_id = p_person_id AND information_type = 'PER_SUCCESSION_MGMT_INFO')
WHERE (NVL (start_date, p_effective_date) <= p_effective_date)
ORDER BY NVL (completion_date, start_date) DESC;
SELECT pei_information1 potential
FROM per_people_extra_info
WHERE person_id = p_person_id AND information_type = 'PER_SUCCESSION_PLANNING';
SELECT information1 --- return from BG specific. If not defined then Global
FROM per_shared_types
WHERE lookup_type = 'READINESS_LEVEL'
AND system_type_cd = p_potential
AND business_group_id = hr_general.get_business_group_id
UNION ALL
SELECT information1
FROM per_shared_types
WHERE lookup_type = 'READINESS_LEVEL'
AND system_type_cd = p_potential
AND business_group_id IS NULL;
SELECT information1 --- return from BG specific. If not defined then Global
FROM per_shared_types
WHERE lookup_type = 'PER_SUCC_PLAN_POTENTIAL'
AND system_type_cd = p_potential
AND business_group_id = hr_general.get_business_group_id
UNION ALL
SELECT information1
FROM per_shared_types
WHERE lookup_type = 'PER_SUCC_PLAN_POTENTIAL'
AND system_type_cd = p_potential
AND business_group_id IS NULL;
SELECT performance_rating
FROM per_performance_reviews
WHERE person_id = p_person_id AND review_date <= p_effective_date
ORDER BY review_date DESC;
SELECT information1 --- return from BG specific. If not defined then
GLOBAL
FROM per_shared_types
WHERE lookup_type = 'PERFORMANCE_RATING'
AND system_type_cd = p_perf
AND NVL (business_group_id, -1) =
NVL2 (business_group_id, hr_general.get_business_group_id,
-1)
UNION ALL
SELECT information1
FROM per_shared_types
WHERE lookup_type = 'PERFORMANCE_RATING'
AND system_type_cd = p_perf
AND business_group_id IS NULL;
SELECT RETENTION
FROM (SELECT pei_information4 RETENTION,
fnd_date.canonical_to_date (pei_information5) start_date,
fnd_date.canonical_to_date (pei_information6) end_date,
fnd_date.canonical_to_date (pei_information8) completion_date
FROM per_people_extra_info
WHERE person_id = p_person_id AND information_type = 'PER_SUCCESSION_MGMT_INFO')
WHERE (NVL (start_date, p_effective_date) <= p_effective_date)
ORDER BY NVL (completion_date, start_date) DESC;
SELECT pei_information2 RETENTION
FROM per_people_extra_info
WHERE person_id = p_person_id AND information_type = 'PER_SUCCESSION_PLANNING';
SELECT information1 --- return from BG specific. If not defined then Global
FROM per_shared_types
WHERE lookup_type = 'PER_RETENTION_POTENTIAL'
AND system_type_cd = p_retention
AND business_group_id = hr_general.get_business_group_id
UNION ALL
SELECT information1
FROM per_shared_types
WHERE lookup_type = 'PER_RETENTION_POTENTIAL'
AND system_type_cd = p_retention
AND business_group_id IS NULL;
SELECT information1 --- return from BG specific. If not defined then Global
FROM per_shared_types
WHERE lookup_type = 'PER_SUCC_PLAN_RISK_LEVEL'
AND system_type_cd = p_retention
AND business_group_id = hr_general.get_business_group_id
UNION ALL
SELECT information1
FROM per_shared_types
WHERE lookup_type = 'PER_SUCC_PLAN_RISK_LEVEL'
AND system_type_cd = p_retention
AND business_group_id IS NULL;
SELECT information1 --- return from BG specific. If not defined then Global
FROM per_shared_types
WHERE lookup_type = 'READINESS_LEVEL'
AND system_type_cd = p_potential
AND business_group_id = hr_general.get_business_group_id
UNION ALL
SELECT information1
FROM per_shared_types
WHERE lookup_type = 'READINESS_LEVEL'
AND system_type_cd = p_potential
AND business_group_id IS NULL;
SELECT iol
FROM (SELECT pei_information9 iol,
fnd_date.canonical_to_date (pei_information5) start_date,
fnd_date.canonical_to_date (pei_information6) end_date,
fnd_date.canonical_to_date (pei_information8) completion_date
FROM per_people_extra_info
WHERE person_id = p_person_id AND information_type = 'PER_SUCCESSION_MGMT_INFO')
WHERE (NVL (start_date, p_effective_date) <= p_effective_date)
ORDER BY NVL (completion_date, start_date) DESC;
SELECT information1 --- return from BG specific. If not defined then Global
FROM per_shared_types
WHERE lookup_type = 'PER_SP_IMPACT_OF_LOSS'
AND system_type_cd = p_iol
AND business_group_id = hr_general.get_business_group_id
UNION ALL
SELECT information1
FROM per_shared_types
WHERE lookup_type = 'PER_SP_IMPACT_OF_LOSS'
AND system_type_cd = p_iol
AND business_group_id IS NULL;
SELECT information1 --- return from BG specific. If not defined then
GLOBAL
FROM per_shared_types
WHERE lookup_type = 'PERFORMANCE_RATING'
AND system_type_cd = p_perf
AND NVL (business_group_id, -1) =
NVL2 (business_group_id, hr_general.get_business_group_id,
-1)
UNION ALL
SELECT information1
FROM per_shared_types
WHERE lookup_type = 'PERFORMANCE_RATING'
AND system_type_cd = p_perf
AND business_group_id IS NULL;
SELECT information1 --- return from BG specific. If not defined then Global
FROM per_shared_types
WHERE lookup_type = 'PER_RETENTION_POTENTIAL'
AND system_type_cd = p_retention
AND business_group_id = hr_general.get_business_group_id
UNION ALL
SELECT information1
FROM per_shared_types
WHERE lookup_type = 'PER_RETENTION_POTENTIAL'
AND system_type_cd = p_retention
AND business_group_id IS NULL;
SELECT information1 --- return from BG specific. If not defined then Global
FROM per_shared_types
WHERE lookup_type = 'PER_SP_IMPACT_OF_LOSS'
AND system_type_cd = p_iol
AND business_group_id = hr_general.get_business_group_id
UNION ALL
SELECT information1
FROM per_shared_types
WHERE lookup_type = 'PER_SP_IMPACT_OF_LOSS'
AND system_type_cd = p_iol
AND business_group_id IS NULL;
SELECT information1 --- return from BG specific. If not defined then Global
FROM per_shared_types
WHERE lookup_type = 'PER_RETENTION_POTENTIAL'
AND system_type_cd = p_retention
AND business_group_id = hr_general.get_business_group_id
UNION ALL
SELECT information1
FROM per_shared_types
WHERE lookup_type = 'PER_RETENTION_POTENTIAL'
AND system_type_cd = p_retention
AND business_group_id IS NULL;
SELECT key_worker
FROM (SELECT pei_information3 key_worker,
fnd_date.canonical_to_date (pei_information5) start_date,
fnd_date.canonical_to_date (pei_information6) end_date,
fnd_date.canonical_to_date (pei_information8) completion_date
FROM per_people_extra_info
WHERE person_id = p_person_id AND information_type = 'PER_SUCCESSION_MGMT_INFO')
WHERE (NVL (start_date, p_effective_date) <= p_effective_date)
ORDER BY NVL (completion_date, start_date) DESC;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (
SELECT NULL
FROM per_sp_plan plans, per_sp_successor_in_plan succ
WHERE plans.successee_id = p_person_id
AND succ.status = 'A'
AND plans.status = 'A'
AND plans.plan_type = 'EMP'
AND succ.plan_id = plans.plan_id
AND p_effective_date BETWEEN TRUNC(start_date) AND NVL(end_date, p_effective_date));
SELECT 'Y'
FROM DUAL
WHERE EXISTS (
SELECT NULL
FROM per_sp_plan plans, per_sp_successor_in_plan succ
WHERE succ.successor_id = p_person_id
AND succ.status = 'A'
AND plans.status = 'A'
AND succ.plan_id = plans.plan_id
AND p_effective_date BETWEEN TRUNC(start_date) AND NVL(end_date, p_effective_date));
SELECT 'Y'
FROM SYS.DUAL
WHERE EXISTS (
SELECT NULL
FROM per_person_types typ, per_person_type_usages_f ptu
WHERE typ.system_person_type IN ('EMP', 'CWK', 'EMP_APL', 'APL'
,decode (fnd_profile.value ('PER_SP_SHOW_TERMINATED')
,'Y'
,'EX_EMP')
,decode (fnd_profile.value ('PER_SP_SHOW_TERMINATED')
,'Y'
,'EX_CWK'))
AND typ.person_type_id = ptu.person_type_id
AND TRUNC (SYSDATE) BETWEEN ptu.effective_start_date AND ptu.effective_end_date
AND ptu.person_id = p_person_id);
SELECT legislation_code,
COUNT (*)
FROM (SELECT legislation_code,
CASE
WHEN (100 - average_readiness) < 25
THEN 'L'
WHEN (100 - average_readiness) BETWEEN 25 AND 75
THEN 'M'
WHEN (100 - average_readiness) > 75
THEN 'H'
END overall_readiness
FROM (SELECT sp.plan_id,
bg.org_information9 legislation_code,
NVL (ssd.plan_readiness_rule, 'AVG') plan_readiness_rule,
DECODE (NVL (ssd.plan_readiness_rule, 'AVG'),
'AVG', NVL (AVG (readiness_pct), 0),
'MIN', NVL (MIN (readiness_pct), 0),
'MAX', NVL (MAX (readiness_pct), 0)
) average_readiness
FROM per_sp_plan sp,
per_sp_successor_in_plan ssp,
per_sp_successee_details ssd,
hr_organization_information bg,
per_people_f ppf,
per_assignments_f paf,
per_assignments_f pa1,
per_assignment_status_types pas
WHERE ppf.person_id <> fnd_global.employee_id
AND ppf.person_id = paf.person_id
AND paf.primary_flag = 'Y'
AND pa1.primary_flag = 'Y'
AND paf.assignment_type IN ('E','C')
AND pa1.assignment_type IN ('E','C','A')
AND pa1.assignment_status_type_id = pas.assignment_status_type_id
AND pas.per_system_status <> 'TERM_ASSIGN'
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pa1.effective_start_date AND pa1.effective_end_date
AND paf.business_group_id = bg.organization_id
AND bg.org_information_context = 'Business Group Information'
AND sp.plan_type = 'EMP'
AND sp.successee_id = ppf.person_id
AND sp.status = 'A'
AND ssp.status = 'A'
AND sp.plan_id = ssp.plan_id
AND ssd.successee_type(+) = 'EMP'
AND sp.successee_id = ssd.successee_id(+)
AND ssp.successor_id = pa1.person_id
AND hr_wpm_util.is_hipo_key_inplan_worker (ppf.person_id, TRUNC (SYSDATE)) IN
('Y', 'N')
GROUP BY sp.plan_id, bg.org_information9, NVL (ssd.plan_readiness_rule, 'AVG')))
WHERE legislation_code = p_legislation_code AND overall_readiness = p_mode
GROUP BY legislation_code;
SELECT DECODE (NVL (ssd.plan_readiness_rule, 'AVG'),
'AVG', NVL (AVG (readiness_pct), 0),
'MIN', NVL (MIN (readiness_pct), 0),
'MAX', NVL (MAX (readiness_pct), 0)
) average_readiness
FROM per_sp_plan sp,
per_sp_successor_in_plan ssp,
per_sp_successee_details ssd,
per_people_f ppf,
per_assignments_f paf,
per_assignments_f pa1,
per_assignment_status_types pas
WHERE sp.plan_id = p_plan_id
AND ppf.person_id <> fnd_global.employee_id
AND ppf.person_id = paf.person_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type IN ('E','C')
AND (pa1.assignment_type='A' OR (pa1.assignment_type IN ('E','C') AND pa1.primary_flag = 'Y'))
AND pa1.assignment_status_type_id = pas.assignment_status_type_id
AND pas.per_system_status <> 'TERM_ASSIGN'
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pa1.effective_start_date AND pa1.effective_end_date
-- AND sp.plan_type = 'EMP'
-- AND sp.successee_id = ppf.person_id
AND ssp.status <> 'I'
AND sp.plan_id = ssp.plan_id
AND ssd.successee_type(+) = 'EMP'
AND sp.successee_id = ssd.successee_id(+)
AND ssp.successor_id = pa1.person_id
GROUP BY sp.plan_id, NVL (ssd.plan_readiness_rule, 'AVG');
SELECT 'Y'
FROM DUAL
WHERE EXISTS (
SELECT 'x'
FROM per_personal_scorecards pps, per_assignments_f paf,
per_perf_mgmt_plans pmp
WHERE paf.supervisor_id = p_manager_person_id
AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND
paf.effective_end_date
AND paf.assignment_id = pps.assignment_id
AND pps.plan_id = p_plan_id
AND pmp.plan_id = p_plan_id
AND TRUNC (SYSDATE) BETWEEN pmp.obj_setting_start_date AND
NVL (pps.obj_setting_deadline,
pmp.obj_setting_deadline
));