[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select flx.id_flex_num
from fnd_id_flex_structures_tl flx
where flx.id_flex_code = 'PEA' --
and flx.application_id = 800 --
and flx.id_flex_structure_name = p_structure_name
and flx.language = 'US';
select pea.segment2 segment2,
pea.segment3 segment3,
pan.date_from date_from
from per_analysis_criteria pea,
per_person_analyses pan
where pan.person_id = p_person_id
and decode(pan.id_flex_num,l_id_flex_num,1,2) = 1
and pea.analysis_criteria_id = pan.analysis_criteria_id
and add_months(p_effective_date,-48) < ghr_general.return_rif_date(pea.segment3)
order by 3 desc ;
delete from ghr_rif_registers
where session_id = p_session_id;
SELECT gdf.segment1 pay_plan
,gdf.segment2 grade_or_level
FROM per_grade_definitions gdf
,per_grades grd
WHERE grd.grade_id = p_grade_id
AND grd.grade_definition_id = gdf.grade_definition_id;
select effective_start_date,
gdf.segment2 grade
from per_assignments_f asg,
per_grades grd,
per_grade_definitions gdf
where grd.grade_id (+) = asg.grade_id
and asg.assignment_id = p_asg_id
and asg.assignment_type <> 'B'
and grd.grade_definition_id = gdf.grade_definition_id (+)
and gdf.segment2 is not null
and trunc(asg.effective_start_date) <= trunc(p_start_date)
order by asg.effective_start_date desc,
gdf.segment2;
SELECT loc.meaning,
loc.description
FROM hr_lookups loc
WHERE loc.lookup_type = p_lookup_type
AND loc.lookup_code = p_lookup_code;
select per.person_id PERSON_ID,
per.first_name FIRST_NAME,
per.last_name LAST_NAME,
per.full_name FULL_NAME,
per.middle_names MIDDLE_NAMES,
per.date_of_birth DATE_OF_BIRTH,
per.national_identifier NATIONAL_IDENTIFIER,
asg.position_id POSITION_ID,
asg.assignment_id ASSIGNMENT_ID,
asg.grade_id GRADE_ID,
asg.job_id JOB_ID,
asg.business_group_id BUSINESS_GROUP_ID,
asg.organization_id ORGANIZATION_ID,
asg.effective_start_date EFFECTIVE_START_DATE
from per_assignments_f asg,
per_people_f per,
per_person_types ppt
where per.person_id = asg.person_id
and asg.primary_flag = 'Y'
and asg.assignment_type <> 'B'
and p_effective_date between asg.effective_start_date
and asg.effective_end_date
and per.person_type_id = ppt.person_type_id
and ppt.system_person_type IN ('EMP','EMP_APL')
and p_effective_date between per.effective_start_date
and per.effective_end_date
and asg.position_id is not null;
select rif.comp_area comp_area,
rif.comp_level comp_level,
rif.effective_date,
rif.organization_id,
rif.org_structure_id,
rif.office_symbol,
rif.agency_code_subelement
from ghr_rif_criteria rif
where rif.rif_criteria_id = p_rif_criteria_id;
select jobs.name
from per_jobs jobs
where jobs.job_id = p_job_id;
select name
from hr_positions_f pos
where pos.position_id = p_position_id
and p_effective_date between pos.effective_start_date
and pos.effective_end_date;
Select ghr_rif_registers_s.nextval from dual;
DELETE FROM ghr_rif_registers reg
WHERE reg.session_id = userenv('SESSIONID')
and rif_criteria_id = p_rif_criteria_id;
hr_utility.set_location('Inserting into ghr_rif_registers',13);
INSERT INTO ghr_rif_registers
(rif_register_id
,session_id
,rif_criteria_id
,effective_date
,person_id
,position_id
,full_name
,last_name
,first_name
,middle_names
,national_identifier
,tenure
,tenure_desc
,tenure_group
,tenure_group_desc
,tenure_group_order
,veterans_pref_for_rif
,veterans_preference
,veterans_preference_desc
,veterans_pref_sub_group
,veterans_pref_sub_group_order
,veterans_pref_sub_group_desc
,service_comp_date_rif
,performance_score
,adjusted_service_comp_date
,occ_code
,occ_code_desc
,job_name
,pay_plan
,grade_or_level
,wgi_due_date
,step_or_rate
,service_comp_date_civilian
,rating_of_record1
,rating_of_record1_desc
,rating_of_record1_date
,rating_of_record2
,rating_of_record2_desc
,rating_of_record2_date
,rating_of_record3
,rating_of_record3_desc
,rating_of_record3_date
,position_occupied
,position_occupied_desc
,position_title
,position_name
,obligated_posn_type
,obligated_posn_type_desc
,organization_id
,organization_name
,org_structure_id
,office_symbol
,agency_code_subelement
,agency_code_subelement_desc
,entered_grade_date
,obligated_expiration_date
,comp_area
,comp_level
)
VALUES
(l_rif_reg.rif_register_id
,userenv('SESSIONID')
,p_rif_criteria_id
,l_effective_date
,per_rec.person_id
,per_rec.position_id
,per_rec.full_name
,per_rec.last_name
,per_rec.first_name
,per_rec.middle_names
,per_rec.national_identifier
,l_rif_reg.tenure
,l_rif_reg.tenure_desc
,l_rif_reg.tenure_group
,l_rif_reg.tenure_group_desc
,l_rif_reg.tenure_group_order
,l_rif_reg.veterans_pref_for_rif
,l_rif_reg.veterans_preference
,l_rif_reg.veterans_preference_desc
,l_rif_reg.veterans_pref_sub_group
,l_rif_reg.veterans_pref_sub_group_order
,l_rif_reg.veterans_pref_sub_group_desc
,l_rif_reg.service_comp_date_rif
,l_rif_reg.performance_score
,l_rif_reg.adjusted_service_comp_date
,l_rif_reg.occ_code
,l_rif_reg.occ_code_desc
,l_rif_reg.job_name
,l_rif_reg.pay_plan
,l_rif_reg.grade_or_level
,l_rif_reg.wgi_due_date
,l_rif_reg.step_or_rate
,l_rif_reg.service_comp_date_civilian
,l_rif_reg.rating_of_record1
,l_rif_reg.rating_of_record1_desc
,l_rif_reg.rating_of_record1_date
,l_rif_reg.rating_of_record2
,l_rif_reg.rating_of_record2_desc
,l_rif_reg.rating_of_record2_date
,l_rif_reg.rating_of_record3
,l_rif_reg.rating_of_record3_desc
,l_rif_reg.rating_of_record3_date
,l_rif_reg.position_occupied
,l_rif_reg.position_occupied_desc
,l_rif_reg.position_title
,l_rif_reg.position_name
,l_rif_reg.obligated_posn_type
,l_rif_reg.obligated_posn_type_desc
,per_rec.organization_id
,l_rif_reg.organization_name
,l_rif_reg.org_structure_id
,l_rif_reg.office_symbol
,l_rif_reg.agency_code_subelement
,l_rif_reg.agency_code_subelement_desc
,l_rif_reg.entered_grade_date
,l_rif_reg.obligated_expiration_date
,l_comp_area
,l_comp_level
);
DELETE
FROM ghr_rif_registers reg
WHERE reg.session_id = USERENV('SESSIONID');
SELECT 1
FROM ghr_rif_criteria rif
WHERE rif.name = p_name
AND rif.rif_criteria_id <> NVL(p_rif_criteria_id,-1);
select name
from per_organization_units porg
where porg.organization_id = p_organization_id;
select position_id,organization_id,business_group_id
from hr_positions_f pos
where not exists
( select 1
from per_assignments_f asg
where asg.position_id = pos.position_id
and asg.assignment_type <> 'B'
and p_effective_date between asg.effective_start_date
and asg.effective_end_date )
and organization_id = nvl(p_organization_id,organization_id)
and p_effective_date between pos.effective_start_date
and pos.effective_end_date;