[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT increased_index
FROM pqh_fr_global_indices_f
WHERE gross_index = p_gross_index
AND type_of_record = 'IND' -- for indices
AND p_effective_date BETWEEN effective_start_date and effective_end_date;
SELECT TO_NUMBER(pgi_information3)
FROM ben_pgm_extra_info
WHERE information_type = 'PQH_FR_CORP_INFO'
AND pgm_id = p_ben_pgm_id;
SELECT pgi_information2
FROM ben_pgm_extra_info
WHERE information_type = 'PQH_FR_CORP_INFO'
AND pgm_id = p_ben_pgm_id;
SELECT appraisal_id
FROM per_appraisals
WHERE appraisee_person_id = p_person_id
AND type = p_appraisal_type
AND appraisal_system_status = p_appraisal_status
AND appraisal_date >= NVL(p_appraisal_start_date,HR_GENERAL.start_of_time)
AND appraisal_date <= NVL(p_appraisal_end_date,HR_GENERAL.end_of_time)
AND appraisal_date <= p_effective_date
ORDER BY appraisal_date DESC;
SELECT assessment_id
FROM per_assessments
WHERE appraisal_id = p_appraisal_id;
SELECT COUNT(asg.person_id)
FROM per_all_assignments_f asg
,per_all_people_f ppl
WHERE asg.grade_ladder_pgm_id = p_ben_pgm_id
AND asg.grade_id = p_grade_id
AND asg.primary_flag = 'Y'
AND ppl.person_id = asg.person_id
AND ppl.per_information15 = '01' --Fonctionaires only
AND asg.assignment_status_type_id IN (SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN'))
AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND p_effective_date BETWEEN ppl.effective_start_date AND ppl.effective_end_date;
SELECT COUNT(placement_id)
FROM per_spinal_point_placements_f
WHERE assignment_id IN (SELECT asg.assignment_id
FROM per_all_assignments_f asg
,per_all_people_f ppl
WHERE asg.grade_ladder_pgm_id = p_ben_pgm_id
AND asg.grade_id = p_grade_id
AND asg.primary_flag = 'Y'
AND ppl.person_id = asg.person_id
AND ppl.per_information15 = '01' --Fonctionaires only
AND asg.assignment_status_type_id IN (SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN'))
AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND p_effective_date BETWEEN ppl.effective_start_date AND ppl.effective_end_date)
AND information3 = p_speed
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT NVL(cei.information6,-1) --MAX Speed Quota
,NVL(cei.information7,-1) --AVG Speed Quota
FROM pqh_corps_definitions corps
,pqh_corps_extra_info cei
WHERE corps.ben_pgm_id = p_ben_pgm_id
AND cei.corps_definition_id = corps.corps_definition_id
AND cei.information3 = TO_CHAR(p_grade_id)
AND cei.information_type = 'GRADE';
PROCEDURE update_speed(p_place_id IN NUMBER
,p_speed IN VARCHAR2
,p_eff_dt IN DATE
,p_ovn IN OUT NOCOPY NUMBER
,p_eff_st_dt OUT NOCOPY DATE
,p_eff_end_dt OUT NOCOPY DATE) IS
--
--Variable Declarations.
l_ovn NUMBER;
l_proc VARCHAR2(72) := g_package||'update_speed';
HR_UTILITY.set_location('OVN before update: '||l_ovn,20);
HR_SP_PLACEMENT_API.update_spp(p_effective_date => p_eff_dt
,p_datetrack_mode => l_datetrack_mode
,p_placement_id => p_place_id
,p_information3 => p_speed
,p_object_version_number => l_ovn
,p_effective_start_date => p_eff_st_dt
,p_effective_end_date => p_eff_end_dt);
HR_UTILITY.set_location('OVN after update: '||l_ovn,40);
END update_speed;
SELECT PQH_WORKFLOW_ITEM_KEY_S.nextval INTO l_itemKey FROM dual;
SELECT asg.grade_id
,spp.step_id
,spp.information3
,MONTHS_BETWEEN(p_effective_date,spp.effective_start_date)
,p_effective_date-spp.effective_start_date
,spp.effective_start_date
FROM per_all_assignments_f asg
,per_spinal_point_placements_f spp
WHERE asg.assignment_id = p_assignment_id
AND spp.assignment_id(+) = asg.assignment_id
AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND p_effective_date BETWEEN spp.effective_start_date(+) AND spp.effective_end_date(+);
SELECT DECODE(p_speed_cd
,'MIN',psp.information4
,'AVG',psp.information5
,'MAX',psp.information3
,-1) speed_len
,NVL(pps.information2,'CM') speed_unit
FROM per_grade_spines_f gsp
,per_spinal_point_steps_f sps
,per_spinal_points psp
,per_parent_spines pps
WHERE gsp.grade_id = p_grade_id
AND sps.step_id = p_step_id
AND sps.grade_spine_id = gsp.grade_spine_id
AND sps.business_group_id = gsp.business_group_id
AND psp.parent_spine_id = gsp.parent_spine_id
AND psp.spinal_point_id = sps.spinal_point_id
AND psp.business_group_id = gsp.business_group_id
AND psp.information_category = 'FR_PQH'
AND pps.information_category = psp.information_category
AND pps.business_group_id = psp.business_group_id
AND pps.parent_spine_id = psp.parent_spine_id
AND p_effective_date BETWEEN gsp.effective_start_date AND gsp.effective_end_date
AND p_effective_date BETWEEN sps.effective_start_date AND sps.effective_end_date;
SELECT user_name
FROM fnd_user
WHERE employee_id = p_mgr_id
AND p_effective_date BETWEEN NVL(start_date,HR_GENERAL.start_of_time) AND NVL(end_date,HR_GENERAL.end_of_time);