[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Generic_Update_Extra_Info
(P_PA_REQUEST_REC IN GHR_PA_REQUESTS%ROWTYPE
,p_l_information_type IN varchar2
,p_extra_info_id IN number
,p_l_object_version_number IN out nocopy number
,p_information1 IN varchar2 default hr_api.g_varchar2
,p_information2 IN varchar2 default hr_api.g_varchar2
,p_information3 IN varchar2 default hr_api.g_varchar2
,p_information4 IN varchar2 default hr_api.g_varchar2
,p_information5 IN varchar2 default hr_api.g_varchar2
,p_information6 IN varchar2 default hr_api.g_varchar2
,p_Information7 IN varchar2 default hr_api.g_varchar2
,p_information8 IN varchar2 default hr_api.g_varchar2
,p_information9 IN varchar2 default hr_api.g_varchar2
,p_information10 IN varchar2 default hr_api.g_varchar2
,p_information11 IN varchar2 default hr_api.g_varchar2
,p_information12 IN varchar2 default hr_api.g_varchar2
,p_information13 IN varchar2 default hr_api.g_varchar2
,p_information14 IN varchar2 default hr_api.g_varchar2
,p_information15 IN varchar2 default hr_api.g_varchar2
,p_information16 IN varchar2 default hr_api.g_varchar2
,p_information17 IN varchar2 default hr_api.g_varchar2
,p_information18 IN varchar2 default hr_api.g_varchar2
,p_information19 IN varchar2 default hr_api.g_varchar2
,p_information20 IN varchar2 default hr_api.g_varchar2
,p_information21 IN varchar2 default hr_api.g_varchar2
,p_information22 IN varchar2 default hr_api.g_varchar2
,p_information23 IN varchar2 default hr_api.g_varchar2
,p_information24 IN varchar2 default hr_api.g_varchar2
,p_information25 IN varchar2 default hr_api.g_varchar2
,p_information26 IN varchar2 default hr_api.g_varchar2
,p_information27 IN varchar2 default hr_api.g_varchar2
,p_information28 IN varchar2 default hr_api.g_varchar2
,p_information29 IN varchar2 default hr_api.g_varchar2
,p_information30 IN varchar2 default hr_api.g_varchar2) is
--
l_proc varchar2(70) := 'Generic Update Extra Info';
select assignment_extra_info_id,
object_version_number
from per_assignment_extra_info
where assignment_id = l_id
and information_type = p_l_information_type;
select person_extra_info_id,
object_version_number
from per_people_extra_info
where person_id = l_id
and information_type = p_l_information_type;
select position_extra_info_id,
object_version_number
from per_position_extra_info
where position_id = l_id
and information_type = p_l_information_type;
hr_assignment_extra_info_api.update_assignment_extra_info
( p_assignment_extra_info_id => l_Extra_Info_Id
, p_object_version_number => l_ovn
, p_aei_information1 => p_information1
, p_aei_information2 => p_information2
, p_aei_information3 => p_information3
, p_aei_information4 => p_information4
, p_aei_information5 => p_information5
, p_aei_information6 => p_information6
, p_aei_information7 => p_information7
, p_aei_information8 => p_information8
, p_aei_information9 => p_information9
, p_aei_information10 => p_information10
, p_aei_information11 => p_information11
, p_aei_information12 => p_information12
, p_aei_information13 => p_information13
, p_aei_information14 => p_information14
, p_aei_information15 => p_information15
, p_aei_information16 => p_information16
, p_aei_information17 => p_information17
, p_aei_information18 => p_information18
, p_aei_information19 => p_information19
, p_aei_information20 => p_information20
, p_aei_information21 => p_information21
, p_aei_information22 => p_information22
, p_aei_information23 => p_information23
, p_aei_information24 => p_information24
, p_aei_information25 => p_information25
, p_aei_information26 => p_information26
, p_aei_information27 => p_information27
, p_aei_information28 => p_information28
, p_aei_information29 => p_information29
, p_aei_information30 => p_information30);
hr_person_extra_info_api.update_person_extra_info
( p_person_extra_info_id => l_Extra_Info_Id
, p_object_version_number => l_ovn
, p_pei_information1 => p_information1
, p_pei_information2 => p_information2
, p_pei_information3 => p_information3
, p_pei_information4 => p_information4
, p_pei_information5 => p_information5
, p_pei_information6 => p_information6
, p_pei_information7 => p_information7
, p_pei_information8 => p_information8
, p_pei_information9 => p_information9
, p_pei_information10 => p_information10
, p_pei_information11 => p_information11
, p_pei_information12 => p_information12
, p_pei_information13 => p_information13
, p_pei_information14 => p_information14
, p_pei_information15 => p_information15
, p_pei_information16 => p_information16
, p_pei_information17 => p_information17
, p_pei_information18 => p_information18
, p_pei_information19 => p_information19
, p_pei_information20 => p_information20
, p_pei_information21 => p_information21
, p_pei_information22 => p_information22
, p_pei_information23 => p_information23
, p_pei_information24 => p_information24
, p_pei_information25 => p_information25
, p_pei_information26 => p_information26
, p_pei_information27 => p_information27
, p_pei_information28 => p_information28
, p_pei_information29 => p_information29
, p_pei_information30 => p_information30);
hr_position_extra_info_api.update_position_extra_info
( p_position_extra_info_id => l_Extra_Info_Id
, p_object_version_number => l_ovn
, p_poei_information1 => p_information1
, p_poei_information2 => p_information2
, p_poei_information3 => p_information3
, p_poei_information4 => p_information4
, p_poei_information5 => p_information5
, p_poei_information6 => p_information6
, p_poei_information7 => p_information7
, p_poei_information8 => p_information8
, p_poei_information9 => p_information9
, p_poei_information10 => p_information10
, p_poei_information11 => p_information11
, p_poei_information12 => p_information12
, p_poei_information13 => p_information13
, p_poei_information14 => p_information14
, p_poei_information15 => p_information15
, p_poei_information16 => p_information16
, p_poei_information17 => p_information17
, p_poei_information18 => p_information18
, p_poei_information19 => p_information19
, p_poei_information20 => p_information20
, p_poei_information21 => p_information21
, p_poei_information22 => p_information22
, p_poei_information23 => p_information23
, p_poei_information24 => p_information24
, p_poei_information25 => p_information25
, p_poei_information26 => p_information26
, p_poei_information27 => p_information27
, p_poei_information28 => p_information28
, p_poei_information29 => p_information29
, p_poei_information30 => p_information30
);
End Generic_Update_Extra_Info;
Procedure Update_Retained_Grade
(P_PA_REQUEST_REC IN GHR_PA_REQUESTS%ROWTYPE ,
P_Per_retained_grade IN OUT NOCOPY GHR_API.Per_retained_grade_TYPE) IS
--
--
cursor c_702_rec is
select rei_information3,pei_information1,
pei.object_version_number ovn
from ghr_pa_request_extra_info rei,
per_people_extra_info pei
where pa_request_id = p_pa_request_rec.pa_request_id
and pei.person_extra_info_id = rei.rei_information3
and pei.information_type = 'GHR_US_RETAINED_GRADE'
and rei.information_type = 'GHR_US_PAR_TERM_RG_PROMO'
and nvl(rei.rei_information30,hr_api.g_varchar2) <> 'Original RPA';
select rei_information3,pei_information1,
pei.object_version_number ovn
from ghr_pa_request_extra_info rei,
per_people_extra_info pei
where pa_request_id = p_pa_request_rec.pa_request_id
and pei.person_extra_info_id = rei.rei_information3
and pei.information_type = 'GHR_US_RETAINED_GRADE'
and rei.information_type = 'GHR_US_PAR_TERM_RG_POSN_CHG'
and (rei.rei_information5 is null or rei.rei_information5 = 'Y')
and nvl(rei.rei_information30,hr_api.g_varchar2) <> 'Original RPA';
SELECT rei_information3 temp_step
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_rec.pa_request_id
AND information_type = 'GHR_US_PAR_RG_TEMP_PROMO';
l_proc varchar2(70) := 'Update_Retained_Grade';
select object_version_number,
pei_information1,
pei_information2,
pei_information3,
pei_information4,
pei_information5,
pei_information6,
pei_information7,
pei_information8,
pei_information9
from per_people_extra_info
where person_extra_info_id = l_retained_grade_rec.person_extra_info_id;
Generic_Update_Extra_Info
(p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_RETAINED_GRADE'
,p_extra_info_id => rg_rec.rei_information3
,p_l_object_version_number => rg_rec.ovn
,p_information2 => fnd_date.date_to_canonical(p_pa_request_rec.effective_date - 1)
);
Generic_Update_Extra_Info
(p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_RETAINED_GRADE'
,p_extra_info_id => rg_rec.rei_information3
,p_l_object_version_number => rg_rec.ovn
,p_information2 => fnd_date.date_to_canonical(p_pa_request_rec.effective_date - 1)
);
Generic_Update_Extra_Info
(p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_RETAINED_GRADE'
,p_extra_info_id => p_per_retained_grade.person_extra_info_id
,p_l_object_version_number => p_per_retained_grade.object_version_number
,p_information2 => fnd_date.date_to_canonical(l_effective_date)
);
hr_person_extra_info_api.update_person_extra_info
(p_person_extra_info_id => l_retained_grade_rec.person_extra_info_id,
p_object_version_number => l_ret_object_version_number,
p_pei_information2 => fnd_date.date_to_canonical(p_pa_request_rec.effective_date )
);
hr_person_extra_info_api.update_person_extra_info
(p_person_extra_info_id => l_retained_grade_rec.person_extra_info_id,
p_object_version_number => l_ret_object_version_number,
p_pei_information9 => l_new_temp_step
);
hr_person_extra_info_api.update_person_extra_info
(p_person_extra_info_id => l_retained_grade_rec.person_extra_info_id,
p_object_version_number => l_ret_object_version_number,
p_pei_information4 => P_Per_retained_grade.retain_step_or_rate
);
hr_person_extra_info_api.update_person_extra_info
(p_person_extra_info_id => l_retained_grade_rec.person_extra_info_id,
p_object_version_number => l_ret_object_version_number,
p_pei_information9 => P_Per_retained_grade.temp_step
);
Generic_Update_Extra_Info
(p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_RETAINED_GRADE'
,p_extra_info_id => l_retained_grade_rec.person_extra_info_id
,p_l_object_version_number => l_ret_object_version_number
,p_information1 => l_cur_date_from
,p_information2 => l_new_date_to
,p_information3 => l_new_grade_or_level
,p_information4 => l_new_step_or_rate
,p_information5 => l_new_pay_plan
,p_information6 => l_new_pay_table
,p_Information7 => l_new_loc_percent
,p_information8 => l_new_pay_basis
,p_information9 => l_new_temp_step
);
Generic_Update_Extra_Info
(p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_RETAINED_GRADE'
,p_extra_info_id => l_retained_grade_rec.person_extra_info_id
,p_l_object_version_number => l_ret_object_version_number
,p_information2 => fnd_date.date_to_canonical(p_pa_request_rec.effective_date - 1)
);
END Update_Retained_Grade;
Procedure update_edu_sit
(p_pa_request_rec in ghr_pa_requests%rowtype
) is
l_proc varchar2(72) := 'Generic Sit';
select id_flex_num
from fnd_id_flex_structures_tl
where id_flex_structure_name = 'US Fed Education'
and language = 'US';
select analysis_criteria_id ,
person_analysis_id ,
object_version_number
from per_person_analyses
where person_id = p_pa_request_rec.person_id
and id_flex_num = l_id_flex_num;
select pea.analysis_criteria_id,
pea.segment1 education_level,
pea.segment2 academic_discipline,
pea.segment3 year_degree_attained,
pan.person_analysis_id,
pan.object_version_number
from per_analysis_criteria pea,
per_person_analyses pan
where pan.person_id = p_pa_request_rec.person_id
and pan.id_flex_num = l_id_flex_num
and pea.id_flex_num = pan.id_flex_num
and p_pa_request_rec.effective_date
between nvl(pan.date_from,p_pa_request_rec.effective_date)
and nvl(pan.date_to,p_pa_request_rec.effective_date)
and p_pa_request_rec.effective_date
between nvl(pea.start_date_active,p_pa_request_rec.effective_date)
and nvl(pea.end_date_active,p_pa_request_rec.effective_date)
and pan.analysis_criteria_id = pea.analysis_criteria_id
order by pea.segment1 desc;
select segment1 education_level,
segment2 academic_discipline,
segment3 year_degree_attained
from per_analysis_criteria
where analysis_criteria_id = l_analysis_criteria_id
and id_flex_num = l_id_flex_num;
select business_group_id
from per_all_people_f
where person_id = p_pa_request_rec.person_id
and p_pa_request_rec.effective_date
between effective_start_date and effective_end_date;
select multiple_occurrences_flag
from per_special_info_types sit
where business_group_id = l_business_group_id
and id_flex_num = l_id_flex_num;
hr_sit_api.update_sit
(p_person_analysis_id => l_personal_analysis_id,
p_pea_object_version_number => l_object_version_number,
p_date_from => p_pa_request_rec.effective_date, ---??
p_segment1 => p_pa_request_rec.education_level,
p_segment2 => p_pa_request_rec.academic_discipline,
p_segment3 => p_pa_request_rec.year_degree_attained,
p_analysis_criteria_id => l_analysis_criteria_id
);
End update_edu_sit;
select fpm.process_method_code
from ghr_noa_families nof
,ghr_families fam
,ghr_noa_fam_proc_methods fpm
,ghr_pa_data_fields pdf
where nof.nature_of_action_id = p_pa_request_rec.first_noa_id
and nof.noa_family_code = fam.noa_family_code
and nof.enabled_flag = 'Y'
and p_pa_request_rec.effective_date between nvl(nof.start_date_active,p_pa_request_rec.effective_date)
and nvl(nof.end_date_active,p_pa_request_rec.effective_date )
and fam.proc_method_flag = 'Y'
and fam.enabled_flag = 'Y'
and p_pa_request_rec.effective_date between nvl(fam.start_date_active,p_pa_request_rec.effective_date)
and nvl(fam.end_date_active,p_pa_request_rec.effective_date)
and fam.noa_family_code = fpm.noa_family_code
and fpm.pa_data_field_id = pdf.pa_data_field_id
and fpm.enabled_flag = 'Y'
and p_pa_request_rec.effective_date between nvl(fpm.start_date_active,p_pa_request_rec.effective_date)
and nvl(fpm.end_date_active,p_pa_request_rec.effective_date)
and pdf.form_field_name = l_form_field_name
and pdf.enabled_flag = 'Y'
and p_pa_request_rec.effective_date between nvl(pdf.date_from,p_pa_request_rec.effective_date)
and nvl(pdf.date_to,p_pa_request_rec.effective_date );
select poei_information5 l_org_structure_id
from per_position_extra_info
where information_type='GHR_US_POS_GRP1' and position_id=p_position_id;
select second_action_la_code1,second_action_la_code2,first_noa_code,second_noa_code,
second_action_la_desc1,second_action_la_desc2 --Bug# 4941984(AFHR2)
from ghr_pa_requests
where pa_request_id=p_pa_request_id and first_noa_code='002';
select noa_family_code from ghr_noa_families
where nature_of_action_id=p_second_noa_id and noa_family_code='APP'
AND
nature_of_action_id not in (select nature_of_action_id from ghr_noa_families
where noa_family_code='APPT_TRANS');
select par.first_noa_code
from
ghr_pa_requests par,
per_Assignments_f asg
where asg.assignment_id=par.employee_assignment_id
and par.employee_national_identifier=p_ssn
and noa_family_code not in
('NON_PAY_DUTY_STATUS','RETURN_TO_DUTY','CANCEL','CORRECT')
and par.effective_date=asg.effective_start_date
Order by effective_date asc;
select first_noa_information1 NTE_Dates
from ghr_pa_requests
where first_noa_code=p_noa_code
and noa_family_code not in
('NON_PAY_DUTY_STATUS','RETURN_TO_DUTY','CANCEL','CORRECT')
and employee_national_identifier=p_ssn;
SELECT
MAX(par.effective_date) eff_date
FROM
ghr_pa_requests par
WHERE
par.person_id= c_person_id AND
par.noa_family_code = 'NON_PAY_DUTY_STATUS' AND
par.pa_notification_id IS NOT NULL AND
NVL(par.first_noa_cancel_or_correct,'NULL') <> 'CANCEL' AND
par.effective_date < c_eff_date;
SELECT
par.first_noa_information1 nte_date
FROM
ghr_pa_requests par
WHERE
par.person_id= c_person_id AND
par.effective_date BETWEEN c_eff_date AND c_rtd_date AND
par.pa_notification_id IS NOT NULL AND
NVL(par.first_noa_cancel_or_correct, 'NULL') <> 'CANCEL' AND
par.first_noa_code IN ('508','515','517','522','548','549','553','554','571','590','750','760','761','762','765','769','770');
SELECT
first_noa_information1 NTE_Dates
FROM
ghr_pa_requests par,
per_Assignments_f asg
WHERE
asg.assignment_id= par.employee_assignment_id AND
asg.effective_start_date = par.effective_date AND
par.person_id= c_person_id AND
par.effective_date < c_eff_date AND
par.noa_family_code NOT IN ('NON_PAY_DUTY_STATUS','RETURN_TO_DUTY','CANCEL','CORRECT') AND
par.pa_notification_id IS NOT NULL AND
NVL(par.first_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
ORDER BY effective_date ASC;
SELECT rei_information5
FROM ghr_pa_request_extra_info
WHERE pa_request_id=p_par_id
AND information_type='GHR_US_PAR_REALIGNMENT';
SELECT gdf.segment1 pay_plan,
gdf.segment2 grade_or_level
FROM per_grade_definitions gdf,
per_grades grd
WHERE grd.grade_id = p_pos_valid_grade.target_grade
AND grd.grade_definition_id = gdf.grade_definition_id
AND grd.business_group_id = FND_PROFILE.value('PER_BUSINESS_GROUP_ID');
SELECT grd.grade_id
FROM per_grade_definitions gdf,
per_grades grd
WHERE grd.grade_definition_id = gdf.grade_definition_id
and gdf.segment1 = l_pay_plan
and gdf.segment2 = l_grade_or_level
and grd.business_group_id = FND_PROFILE.value('PER_BUSINESS_GROUP_ID');
Generic_Update_Extra_Info
(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_ASG_SF52'
,p_extra_info_id => p_asg_sf52.assignment_extra_info_id
,p_l_object_version_number => p_asg_sf52.object_version_number
,p_information3 => p_asg_sf52.step_or_rate
,p_information4 => p_asg_sf52.tenure
,p_information5 => p_asg_sf52.annuitant_indicator
,p_information6 => nvl(ghr_process_sf52.g_prd,p_asg_sf52.pay_rate_determinant)
,p_information7 => p_asg_sf52.work_schedule
,p_information8 => p_asg_sf52.part_time_hours
-- FWFA Changes Bug#4444609. Added NVL condition to handle the families that doesn't trigger pay calc.
,p_information9 => P_Asg_Sf52.calc_pay_table
-- FWFA Changes
);
Generic_Update_Extra_Info
(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_ASG_NON_SF52'
,p_extra_info_id => p_asg_non_sf52.assignment_extra_info_id
,p_l_object_version_number => p_asg_non_sf52.object_version_number
,p_information3 => p_asg_non_sf52.date_arr_personnel_office
-- ,p_information4 => p_asg_non_sf52.duty_status
,p_information5 => p_asg_non_sf52.key_emer_essential_empl
,p_information6 => p_asg_non_sf52.non_disc_agmt_status
-- ,p_information7 => p_asg_non_sf52.date_wtop_exemp_expires
,p_information8 => p_asg_non_sf52.parttime_indicator
,p_information9 => p_asg_non_sf52.qualification_standard_waiver
-- ,p_information10 => p_asg_non_sf52.trainee_promotion_id
-- ,p_information11 => p_asg_non_sf52.date_trainee_promotion_expt
);
Generic_Update_Extra_Info
(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_ASG_NTE_DATES'
,p_extra_info_id => p_asg_nte_dates.assignment_extra_info_id
,p_l_object_version_number => p_asg_nte_dates.object_version_number
,p_information3 => p_asg_nte_dates.asg_nte_start_date
,p_information4 => p_asg_nte_dates.assignment_nte
,p_information5 => p_asg_nte_dates.lwop_nte_start_date
,p_information6 => p_asg_nte_dates.lwop_nte
,p_information7 => p_asg_nte_dates.suspension_nte_start_date
,p_information8 => p_asg_nte_dates.suspension_nte
,p_information9 => p_asg_nte_dates.furlough_nte_start_date
,p_information10 => p_asg_nte_dates.furlough_nte
,p_information11 => p_asg_nte_dates.lwp_nte_start_date
,p_information12 => p_asg_nte_dates.lwp_nte
,p_information13 => p_asg_nte_dates.sabatical_nte_start_date
,p_information14 => p_asg_nte_dates.sabatical_nte
-- ,p_information15 => p_asg_nte_dates.assignment_number
-- ,p_information16 => p_asg_nte_dates.position_nte
);
Generic_Update_Extra_Info
(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_PER_SF52'
,p_extra_info_id => p_per_sf52.person_extra_info_id
,p_l_object_version_number => p_per_sf52.object_version_number
,p_information3 => p_per_sf52.citizenship
,p_information4 => p_per_sf52.veterans_preference
,p_information5 => p_per_sf52.veterans_preference_for_rif
,p_information6 => p_per_sf52.veterans_status
);
Generic_Update_Extra_Info
(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_PER_GROUP1'
,p_extra_info_id => p_per_group1.person_extra_info_id
,p_l_object_version_number => p_per_group1.object_version_number
,p_information3 => p_per_group1.appointment_type
,p_information4 => p_per_group1.type_of_employment
,p_information5 => p_per_group1.race_national_origin
--,p_information6 => p_per_group1.date_last_promotion
,p_information7 => p_per_group1.agency_code_transfer_from
,p_information8 => l_Cur_Appt_Auth_1
,p_information22 => l_Cur_Appt_Auth_desc1--Bug# 4941984(AFHR2)
,p_information9 => l_Cur_Appt_Auth_2
,p_information23 => l_Cur_Appt_Auth_desc2--Bug# 4941984(AFHR2)
--,p_information10 => p_per_group1.country_world_citizenship
,p_information11 => p_per_group1.handicap_code
--,p_information12 => p_per_group1.consent_id
--,p_information13 => p_per_group1.date_fehb_eligibility_expires
--,p_information14 => p_per_group1.date_temp_eligibility_fehb
--,p_information15 => p_per_group1.date_febh_dependent_cert_exp
--,p_information16 => p_per_group1.family_member_emp_pref
--,p_information17 => p_per_group1.family_member_status
,p_information21 => p_per_group1.retention_inc_review_date
);
/* Note : Since none of this data is currently being updated, why call the generic_update at all ??? */
/*If p_per_group2.per_group2_flag = 'Y' then
--
-- included date_stat_return_rights_expire for p_information7 and named the others correctly.
hr_utility.set_location(l_proc,40);
Generic_Update_Extra_Info
(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_PER_GROUP2'
,p_extra_info_id => p_per_group2.person_extra_info_id
,p_l_object_version_number => p_per_group2.object_version_number
,p_information3 => p_per_group2.obligated_position_number
,p_information4 => p_per_group2.obligated_position_type
,p_information5 => p_per_group2.date_overseas_tour_expires
,p_information6 => p_per_group2.date_return_rights_expires
,p_information7 => p_per_group2.date_stat_return_rights_expir
,p_information8 => p_per_group2.civilian_duty_stat_contigency
,p_information9 => p_per_group2.date_travel_agmt_pcs_expires
,p_information10 => p_per_group2.draw_down_action_id
);
Generic_Update_Extra_Info
(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_PER_SCD_INFORMATION'
,p_extra_info_id => p_per_scd_info.person_extra_info_id
,p_l_object_version_number => p_per_scd_info.object_version_number
,p_information3 => p_per_scd_info.scd_leave
,p_information4 => p_per_scd_info.scd_civilian
,p_information5 => p_per_scd_info.scd_rif
,p_information6 => p_per_scd_info.scd_tsp
,p_information7 => p_per_scd_info.scd_retirement
-- Bug 4164083 eHRI New Attribution Changes
,p_information8 => p_per_scd_info.scd_ses
,p_information9 => p_per_scd_info.scd_spl_retirement
-- End eHRI New Attribution Changes
--bug 4443968
,p_information12 => p_per_scd_info.scd_creditable_svc_annl_leave
);
Generic_Update_Extra_Info
(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_PER_PROBATIONS'
,p_extra_info_id => p_per_probations.person_extra_info_id
,p_l_object_version_number => p_per_probations.object_version_number
,p_information3 => p_per_probations.date_prob_trial_period_begin
,p_information4 => p_per_probations.date_prob_trial_period_ends
--,p_information8 => p_per_probations.date_spvr_mgr_prob_begins --Bug 4588575
,p_information5 => p_per_probations.date_spvr_mgr_prob_ends
,p_information6 => p_per_probations.spvr_mgr_prob_completion
,p_information7 => p_per_probations.date_ses_prob_expires
);
Generic_Update_Extra_Info
(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_PER_SEPARATE_RETIRE'
,p_extra_info_id => p_per_sep_retire.person_extra_info_id
,p_l_object_version_number => p_per_sep_retire.object_version_number
,p_information3 => p_per_sep_retire.fers_coverage
,p_information4 => p_per_sep_retire.prev_retirement_coverage
,p_information5 => p_per_sep_retire.frozen_service
,p_information6 => p_per_sep_retire.naf_retirement_indicator
,p_information7 => p_per_sep_retire.reason_for_separation
,p_information8 => l_agency_code_transfer_to
--,p_information9 => p_per_sep_retire.date_projected_retirement
--,p_information10 => p_per_sep_retire.mandatory_retirement_date
,p_information11 => p_per_sep_retire.separate_pkg_status_indicator -- Bug 1359482
--,p_information12 => p_per_sep_retire.separate_pkg_register_number
--,p_information13 => p_per_sep_retire.separate_pkg_pay_office_id
--,p_information14 => p_per_sep_retire.date_ret_appl_received
--,p_information15 => p_per_sep_retire.date_ret_pkg_sent_to_payroll
--,p_information16 => p_per_sep_retire.date_ret_pkg_recv_payroll
--,p_information17 => p_per_sep_retire.date_ret_pkg_to_opm
);
/* Note : Since none of this data is currently being updated, why call the generic_update at all ??? */
/*If p_per_security.per_security_flag = 'Y' then
--
hr_utility.set_location(l_proc,60);
Generic_Update_Extra_Info(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_PER_SECURITY'
,p_extra_info_id => p_per_security.person_extra_info_id
,p_l_object_version_number => p_per_security.object_version_number
,p_information3 => p_per_security.sec_investigation_basis
,p_information4 => p_per_security.type_of_sec_investigation
,p_information5 => p_per_security.date_sec_invest_required
,p_information6 => p_per_security.date_sec_invest_completed
,p_information7 => p_per_security.personnel_sec_clearance
,p_information8 => p_per_security.sec_clearance_eligilb_date
,p_information9 => p_per_security.prp_sci_status_employment
);
Generic_Update_Extra_Info(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_PER_SERVICE_OBLIGATION'
,p_extra_info_id => p_per_service_oblig.person_extra_info_id
,p_l_object_version_number => p_per_service_oblig.object_version_number
,p_information3 => p_per_service_oblig.service_oblig_type_code
,p_information4 => p_per_service_oblig.service_oblig_end_date
,p_information5 => p_per_service_oblig.service_oblig_start_date
);
Generic_Update_Extra_Info
(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_PER_CONVERSIONS'
,p_extra_info_id => p_per_conversions.person_extra_info_id
,p_l_object_version_number => p_per_conversions.object_version_number
,p_information3 => p_per_conversions.date_conv_career_begins
,p_information4 => p_per_conversions.date_conv_career_due
,p_information5 => p_per_conversions.date_recmd_conv_begins
,p_information7 => p_per_conversions.date_recmd_conv_due
,p_information6 => p_per_conversions.date_vra_conv_due
);
Generic_Update_Extra_Info
(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_PER_BENEFIT_INFO'
,p_extra_info_id => p_per_benefit_info.person_extra_info_id
,p_l_object_version_number => p_per_benefit_info.object_version_number
,p_information3 => p_per_benefit_info.FEGLI_Date_Eligibility_Expires
,p_information4 => p_per_benefit_info.FEHB_Date_Eligibility_expires
,p_information5 => p_per_benefit_info.FEHB_Date_temp_eligibility
,p_information6 => p_per_benefit_info.FEHB_Date_dependent_cert_expir
,p_information7 => p_per_benefit_info.FEHB_LWOP_contingency_st_date
,p_information8 => p_per_benefit_info.FEHB_LWOP_contingency_end_date
,p_information10 => p_per_benefit_info.FEHB_Child_equiry_court_date
,p_information11 => p_per_benefit_info.FERS_Date_eligibility_expires
,p_information12 => p_per_benefit_info.FERS_Election_Date
,p_information13 => p_per_benefit_info.FERS_Election_Indicator
,p_information14 => p_per_benefit_info.TSP_Agncy_Contrib_Elig_date
,p_information15 => p_per_benefit_info.TSP_Emp_Contrib_Elig_date
-- 6312144 Added the following RPA -- EIT Benefits segments
,p_information16 => p_per_benefit_info.FEGLI_Assignment_Ind
,p_information17 => p_per_benefit_info.FEGLI_Post_Elec_Basic_Ins_Amt
,p_information18 => p_per_benefit_info.FEGLI_Court_Order_Ind
,p_information19 => p_per_benefit_info.Desg_FEGLI_Benf_Ind
,p_information20 => p_per_benefit_info.FEHB_Event_Code
);
Generic_Update_Extra_Info
(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_PER_ETHNICITY_RACE'
,p_extra_info_id => p_per_race_ethnic_info.person_extra_info_id
,p_l_object_version_number => p_per_race_ethnic_info.object_version_number
,p_information3 => p_per_race_ethnic_info.p_hispanic
,p_information4 => p_per_race_ethnic_info.p_american_indian
,p_information5 => p_per_race_ethnic_info.p_asian
,p_information6 => p_per_race_ethnic_info.p_black_afr_american
,p_information7 => p_per_race_ethnic_info.p_hawaiian_pacific
,p_information8 => p_per_race_ethnic_info.p_white
);
Generic_Update_Extra_Info
(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_PER_UNIFORMED_SERVICES'
,p_extra_info_id => p_per_uniformed_services.person_extra_info_id
,p_l_object_version_number => p_per_uniformed_services.object_version_number
--,p_information3 => p_per_uniformed_services.reserve_category
--,p_information4 => p_per_uniformed_services.military_recall_status
,p_information5 => p_per_uniformed_services.creditable_military_service
--,p_information6 => p_per_uniformed_services.date_retired_uniform_service
--,p_information7 => p_per_uniformed_services.uniform_service_component
--,p_information8 => p_per_uniformed_services.uniform_service_designation
--,p_information9 => p_per_uniformed_services.retirement_grade
--,p_information10 => p_per_uniformed_services.military_retire_waiver_ind
--,p_information11 => p_per_uniformed_services.exception_retire_pay_ind
);
Generic_Update_Extra_Info
(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_POS_VALID_GRADE'
,p_extra_info_id => p_pos_valid_grade.position_extra_info_id
,p_l_object_version_number => p_pos_valid_grade.object_version_number
,p_information3 => p_pos_valid_grade.valid_grade
,p_information4 => p_pos_valid_grade.target_grade
,p_information5 => p_pos_valid_grade.pay_table_id
,p_information6 => p_pos_valid_grade.pay_basis
,p_information7 => p_pos_valid_grade.employment_category_group
);
ghr_mlc_pkg.position_history_update (p_position_id => P_PA_REQUEST_REC.to_position_id,
p_effective_date => P_PA_REQUEST_REC.effective_date,
p_table_id => P_PA_REQUEST_REC.from_pay_table_identifier,
p_upd_tableid => p_pos_valid_grade.pay_table_id);
hr_utility.set_location('Posn Update With WS/PTH' || l_posn_title_pm ,81);
Generic_Update_Extra_Info
(p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_POS_GRP1'
,p_extra_info_id => p_pos_grp1.position_extra_info_id
,p_l_object_version_number => p_pos_grp1.object_version_number
,p_information3 => l_personnel_office_id
,p_information4 => p_pos_grp1.office_symbol
,p_information5 => l_Organ_Component
-- Bug#3816651 Uncommented p_information6.
,p_information6 => p_pos_grp1.occupation_category_code -- This is actually the occ_series on the DDf and not occ_code
,p_information7 => p_pos_grp1.flsa_category
,p_information8 => p_pos_grp1.bargaining_unit_status
--Bug #6356058
-- ,p_information9 => p_pos_grp1.competitive_level
,p_information10 => p_pos_grp1.work_schedule
,p_information11 => p_pos_grp1.functional_class
,p_information12 => p_pos_grp1.position_working_title
--,p_information13 => p_pos_grp1.position_sensitivity
--,p_information14 => p_pos_grp1.security_access
--,p_information15 => p_pos_grp1.prp_sci
,p_information16 => p_pos_grp1.supervisory_status
--,p_information17 => p_pos_grp1.type_employee_supervised
,p_information18 => p_pos_grp1.payroll_office_id
--,p_information19 => p_pos_grp1.timekeeper
--,p_information20 => p_pos_grp1.competitive_area
,p_information21 => p_pos_grp1.positions_organization
,p_information23 => p_pos_grp1.part_time_hours
);
hr_utility.set_location('Posn Update Without WS/PTH' || l_posn_title_pm ,81);
Generic_Update_Extra_Info
(p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_POS_GRP1'
,p_extra_info_id => p_pos_grp1.position_extra_info_id
,p_l_object_version_number => p_pos_grp1.object_version_number
,p_information3 => p_pos_grp1.personnel_office_id
,p_information4 => p_pos_grp1.office_symbol
,p_information5 => p_pos_grp1.organization_structure_id
-- Bug#3816651 Uncommented p_information6
,p_information6 => p_pos_grp1.occupation_category_code -- This is actually the occ_series on the DDf and not occ_code
,p_information7 => p_pos_grp1.flsa_category
,p_information8 => p_pos_grp1.bargaining_unit_status
--Bug #6356058
-- ,p_information9 => p_pos_grp1.competitive_level
--,p_information10 => p_pos_grp1.work_schedule
,p_information11 => p_pos_grp1.functional_class
,p_information12 => p_pos_grp1.position_working_title
--,p_information13 => p_pos_grp1.position_sensitivity
--,p_information14 => p_pos_grp1.security_access
--,p_information15 => p_pos_grp1.prp_sci
,p_information16 => p_pos_grp1.supervisory_status
--,p_information17 => p_pos_grp1.type_employee_supervised
,p_information18 => p_pos_grp1.payroll_office_id
--,p_information19 => p_pos_grp1.timekeeper
--,p_information20 => p_pos_grp1.competitive_area
,p_information21 => p_pos_grp1.positions_organization
--,p_information23 => p_pos_grp1.part_time_hours
);
Generic_Update_Extra_Info
(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_POS_GRP2'
,p_extra_info_id => p_pos_grp2.position_extra_info_id
,p_l_object_version_number => p_pos_grp2.object_version_number
,p_information3 => p_pos_grp2.position_occupied
,p_information4 => p_pos_grp2.organization_function_code
--,p_information5 => p_pos_grp2.date_position_classified
--,p_information6 => p_pos_grp2.date_last_position_audit
--,p_information7 => p_pos_grp2.classification_official
--,p_information8 => p_pos_grp2.language_required
--,p_information9 => p_pos_grp2.drug_test
--,p_information10 => p_pos_grp2.financial_statement
--,p_information11 => p_pos_grp2.training_program_id
--,p_information12 => p_pos_grp2.key_emergency_essential
,p_information13 => p_pos_grp2.appropriation_code1
,p_information14 => p_pos_grp2.appropriation_code2
--,p_information15 => p_pos_grp2.intelligence_position_ind
--,p_information16 => p_pos_grp2.leo_position_indicator
);
/* Note : Since none of this data is currently being updated, why call the generic_update at all ??? */
/*
If p_pos_oblig.pos_oblig_flag = 'Y' then
--
hr_utility.set_location(l_proc,90);
Generic_Update_Extra_Info
(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_POS_OBLIG'
,p_extra_info_id => p_pos_oblig.position_extra_info_id
,p_l_object_version_number => p_pos_oblig.object_version_number
,p_information3 => p_pos_oblig.expiration_date
,p_information4 => p_pos_oblig.obligation_type
,p_information5 => p_pos_oblig.employee_ssn
);
/* Note : Since none of this data is currently being updated, why call the generic_update at all ??? */
/*
If p_pos_car_prog.pos_car_prog_flag = 'Y' then
--
hr_utility.set_location(l_proc,95);
Generic_Update_Extra_Info(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_POS_CAR_PROG'
,p_extra_info_id => p_pos_car_prog.position_extra_info_id
,p_l_object_version_number => p_pos_car_prog.object_version_number
,p_information3 => p_pos_car_prog.career_program_id
,p_information4 => p_pos_car_prog.career_program_type
,p_information5 => p_pos_car_prog.change_reasons
,p_information6 => p_pos_car_prog.career_field_id
,p_information7 => p_pos_car_prog.career_program_code
,p_information8 => p_pos_car_prog.acteds_key_position);
Generic_Update_Extra_Info
(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_PER_BENEFITS_CONT'
,p_extra_info_id => p_ipa_benefits_cont.person_extra_info_id
,p_l_object_version_number => p_ipa_benefits_cont.object_version_number
,p_information1 => p_ipa_benefits_cont.FEGLI_Indicator
,p_information2 => p_ipa_benefits_cont.FEGLI_Election_Date
,p_information3 => p_ipa_benefits_cont.FEGLI_Elec_Not_Date
,p_information4 => p_ipa_benefits_cont.FEHB_Indicator
,p_information5 => p_ipa_benefits_cont.FEHB_Election_Date
,p_information6 => p_ipa_benefits_cont.FEHB_Elec_Notf_Date
,p_information7 => p_ipa_benefits_cont.Retirement_Indicator
,p_information12 => p_ipa_benefits_cont.Retirement_Elec_Date
,p_information8 => p_ipa_benefits_cont.Retirement_Elec_Notf_Date
,p_information9 => p_ipa_benefits_cont.Cont_Term_Insuff_Pay_Elec_Date
,p_information10 => p_ipa_benefits_cont.Cont_Term_Insuff_Pay_Notf_Date
,p_information11 => p_ipa_benefits_cont.Cont_Term_Insuff_Pmt_Type_Code);
Generic_Update_Extra_Info
(
p_pa_request_rec => P_PA_REQUEST_REC
,p_l_information_type => 'GHR_US_PER_RETIRMENT_SYS_INFO'
,p_extra_info_id => p_retirement_info.person_extra_info_id
,p_l_object_version_number => p_retirement_info.object_version_number
,p_information1 => p_retirement_info.special_population_code
,p_information2 => p_retirement_info.App_Exc_CSRS_Ind
,p_information3 => p_retirement_info.App_Exc_FERS_Ind
,p_information4 => p_retirement_info.FICA_Coverage_Ind1
,p_information5 => p_retirement_info.FICA_Coverage_Ind2);
update_edu_sit(p_pa_request_rec => p_pa_request_rec);
generic_update_sit
(p_segment_rec => l_segment_rec,
p_special_information_type => 'US Fed Perf Appraisal',
p_pa_request_rec => p_pa_request_rec
);
generic_update_sit
(p_pa_request_rec => p_pa_request_rec,
p_special_information_type => 'US Fed Conduct Perf',
p_segment_rec => l_segment_rec
);
update_retained_grade
(p_pa_request_rec => p_pa_request_rec,
p_per_retained_grade => p_per_retained_grade );
select ast.assignment_status_type_id,
ast.active_flag
from per_assignment_status_types ast
where ast.per_system_status = l_system_status
and ast.user_status like '%' || l_user_status || '%'
and nvl(ast.business_group_id,hr_api.g_number) = hr_api.g_number
and ast.legislation_code = 'US';
select ast.assignment_status_type_id,
ast.active_flag
from per_assignment_status_types ast
where ast.per_system_status = l_system_status
and ast.user_status like '%' || l_user_status || '%'
and instr(ast.user_status,'NTE') = 0
and nvl(ast.business_group_id,hr_api.g_number) = hr_api.g_number
and ast.legislation_code = 'US';
select ast.user_status,asg.effective_start_date
from per_assignment_status_types ast,
per_all_assignments_f asg
where ast.assignment_status_type_id = asg.assignment_status_type_id
and asg.assignment_id = p_assignment_id
and asg.primary_flag = 'Y'
order by asg.effective_start_date;
select ast.user_status
from per_assignment_status_types ast,
per_all_assignments_f asg
where ast.assignment_status_type_id = asg.assignment_status_type_id
and asg.assignment_id = p_assignment_id
and asg.primary_flag = 'Y'
and user_status='Active Appointment';
Function return_update_mode
(p_id in per_people_f.person_id%type,
p_effective_date in date,
p_table_name in varchar2
) return varchar2 is
l_proc varchar2(72) := 'return_update_mode';
cursor c_update_mode_p is
select per.effective_start_date ,
per.effective_end_date
from per_all_people_f per
where per.person_id = p_id
and p_effective_date
between per.effective_start_date
and per.effective_end_date;
cursor c_update_mode_p1 is
select per.effective_start_date ,
per.effective_end_date
from per_all_people_f per
where per.person_id = p_id
and p_effective_date < per.effective_start_date;
cursor c_update_mode_a is
select asg.effective_start_date ,
asg.effective_end_date
from per_all_assignments_f asg
where asg.assignment_id = p_id
and p_effective_date
between asg.effective_start_date
and asg.effective_end_date;
cursor c_update_mode_a1 is
select asg.effective_start_date ,
asg.effective_end_date
from per_all_assignments_f asg
where asg.assignment_id = p_id
and p_effective_date < asg.effective_start_date;
cursor c_update_mode_pos is
select pos.effective_start_date ,
pos.effective_end_date
from hr_all_positions_f pos
where pos.position_id = p_id
and p_effective_date
between pos.effective_start_date
and pos.effective_end_date;
cursor c_update_mode_pos1 is
select pos.effective_start_date ,
pos.effective_end_date
from hr_all_positions_f pos
where pos.position_id = p_id
and p_effective_date < pos.effective_start_date;
for update_mode in c_update_mode_p loop
hr_utility.set_location(l_proc,15);
l_esd := update_mode.effective_start_date;
l_eed := update_mode.effective_end_date;
l_mode := 'UPDATE';
for update_mode1 in c_update_mode_p1 loop
hr_utility.set_location(l_proc,40);
l_mode := 'UPDATE_CHANGE_INSERT';
for update_mode in c_update_mode_a loop
hr_utility.set_location(l_proc,65);
l_esd := update_mode.effective_start_date;
l_eed := update_mode.effective_end_date;
l_mode := 'UPDATE'; -- to end date a row and then create a new row
for update_mode1 in c_update_mode_a1 loop
hr_utility.set_location(l_proc,85);
l_mode := 'UPDATE_CHANGE_INSERT'; -- to insert a row between 2 existing rows
hr_utility.set_location('UPDATE_MODE : ' || l_mode,2);
for update_mode in c_update_mode_pos loop
hr_utility.set_location(l_proc,115);
l_esd := update_mode.effective_start_date;
l_eed := update_mode.effective_end_date;
l_mode := 'UPDATE'; -- to end date a row and then create a new row
for update_mode1 in c_update_mode_pos1 loop
hr_utility.set_location(l_proc,135);
l_mode := 'UPDATE_CHANGE_INSERT'; -- to insert a row between 2 existing rows
hr_utility.set_location('UPDATE_MODE : ' || l_mode,2);
End return_update_mode;
l_hr_person_api_update varchar2(1) := 'N';
l_employee_api_update_criteria varchar2(1) := 'N';
l_update_address varchar2(1) := 'N';
l_update_person varchar2(1) := 'N';
l_asg_upd_spp_delete_warning boolean;
l_update_mode varchar2(30) := 'UPDATE';
l_employee_update_flag varchar2(1) := 'N';
l_update_gre varchar2(1) := 'N';
l_position_data_rec_type ghr_sf52_pos_update.position_data_rec_type;
Select fam.noa_family_code
from ghr_noa_families nfa,
ghr_families fam
where nfa.nature_of_action_id = p_pa_request_rec.first_noa_id
and nfa.noa_family_code = fam.noa_family_code
and fam.update_hr_flag = 'Y';
select per.business_group_id
from per_all_people_f per
where per.person_id = p_pa_request_rec.person_id
and g_effective_date between
per.effective_start_date
and per.effective_end_date;
select pds.period_of_service_id,
pds.object_version_number
from per_periods_of_service pds
where pds.person_id = p_pa_request_rec.person_id
and pds.date_start <= g_effective_date
and pds.actual_termination_date is null
order by 1 asc;
Select ppt.system_person_type,
ppf.person_type_id
from per_person_types ppt,
per_all_people_f ppf
where ppf.person_id = P_pa_request_rec.person_id
and ppt.person_type_id = ppf.person_type_id
and g_effective_date between ppf.effective_start_date
and ppf.effective_end_date;
select asg.assignment_id,
asg.object_version_number
from per_all_assignments_f asg
where asg.person_id = p_pa_request_rec.person_id
and asg.assignment_id <> p_pa_request_rec.employee_assignment_id;
select ppt.person_type_id
from per_person_types ppt
where ppt.business_group_id = l_business_group_id
and ppt.system_person_type = 'EX_EMP'
-- and ppt.user_person_type = 'Ex-employee'
and ppt.active_flag = 'Y'
order by ppt.person_type_id asc;
select
pad.address_id,
pad.object_version_number,
pad.address_line1,
pad.address_line2,
pad.address_line3,
pad.town_or_city,
pad.region_2,
pad.postal_code,
pad.country
from
per_addresses pad
where
pad.person_id = p_pa_request_rec.person_id
and g_effective_date + 1
between pad.date_from and nvl(pad.date_to,g_effective_date + 1)
and pad.primary_flag = 'Y';
select
pad.address_id,
pad.object_version_number,
pad.address_line1 ,
pad.address_line2 ,
pad.address_line3 ,
pad.town_or_city ,
pad.region_2 ,
pad.region_1 ,
pad.postal_code ,
pad.country ,
pad.address_type,
pad.primary_flag
from
per_addresses pad
where
pad.person_id = p_pa_request_rec.person_id
and g_effective_date
between pad.date_from and nvl(pad.date_to,g_effective_date)
and pad.primary_flag <> 'Y';
SELECT pah.information1
FROM ghr_pa_history pah
WHERE pah.table_name = 'PER_ADDRESSES'
AND pah.information1 = p_address_id
AND pah.pa_request_id IN
(
SELECT pa_request_id
FROM ghr_pa_requests
CONNECT BY prior pa_request_id = altered_pa_request_id
START WITH pa_request_id = p_pa_request_id
);
select c.county_name
from pay_us_counties c ,
pay_us_city_names t ,
pay_us_states s,
pay_us_zip_codes z
where s.state_abbrev = p_pa_request_rec.forwarding_region_2
and t.city_name = p_pa_request_rec.forwarding_town_or_city
and t.state_code = s.state_code
and t.state_code = c.state_code
and t.county_code = c.county_code
and z.city_code = t.city_code
and substr(p_pa_request_rec.forwarding_postal_code,1,5)
between z.zip_start and z.zip_end
and z.state_code = t.state_code
and z.county_code = t.county_code;
select paf.object_version_number,
paf.business_group_id,
paf.normal_hours,
paf.location_id,
paf.payroll_id
from per_all_assignments_f paf
where paf.assignment_id = p_assignment_id
and g_effective_date
between paf.effective_start_date
and paf.effective_end_date;
select ppf.object_version_number,
ppf.business_group_id ,
ppf.employee_number,
ppf.national_identifier,
ppf.date_of_birth,
ppf.first_name,
ppf.last_name,
ppf.middle_names
from per_all_people_f ppf
where ppf.person_id = P_pa_request_rec.person_id
and g_effective_date between ppf.effective_start_date
and ppf.effective_end_date;
select rei_information3 payroll_id
from ghr_pa_request_extra_info
where pa_request_id = p_pa_request_rec.pa_request_id
and information_type = 'GHR_US_PAR_PAYROLL_TYPE';
select payroll_id
from pay_payrolls_f
where period_type = 'Bi-Week'
and payroll_name = l_payroll_name
and business_group_id = l_business_group_id
and p_pa_request_rec.effective_date
between effective_start_date and effective_end_date;
select pay.payroll_id
from pay_payrolls_f pay
where pay.payroll_name = 'Biweekly Payroll'
and g_effective_date
between pay.effective_start_date and pay.effective_end_date
and business_group_id = l_business_group_id;
select pos.object_version_number
from hr_all_positions_f pos -- Venkat
where pos.position_id = p_pa_request_rec.from_position_id
and p_pa_request_rec.effective_date between
pos.effective_start_date and pos.effective_end_date;
select tax.tax_unit_id
from hr_tax_units_v tax
where tax_unit_id = p_pa_request_rec.to_organization_id;
select tax.tax_unit_id
from hr_tax_units_v tax
where tax_unit_id = l_business_group_id;
select pos.organization_id
from hr_all_positions_f pos
where pos.position_id = p_pa_request_rec.from_position_id
and p_pa_request_rec.effective_date between
pos.effective_start_date and pos.effective_end_date;
select pos.organization_id
from hr_all_positions_f pos
where pos.position_id = p_pa_request_rec.to_position_id
and p_pa_request_rec.effective_date between
pos.effective_start_date and pos.effective_end_date;
select ast.user_status,
ast.per_system_status,
asg.effective_start_date
from
per_assignment_status_types ast,
per_all_assignments_f asg
where asg.assignment_id = l_assignment_id
and ast.assignment_status_type_id = asg.assignment_status_type_id
and g_effective_date
between asg.effective_start_date
and asg.effective_end_date;
Select asg.assignment_status_type_id
from per_assignments_f asg
where asg.assignment_id = l_assignment_id
and l_old_effective_start_date between asg.effective_start_date
and asg.effective_end_date;
Select past.per_system_status ,asg.assignment_status_type_id
from per_assignments_f asg,per_assignment_status_types past
where asg.assignment_id = l_assignment_id
and asg.assignment_status_type_id = past.assignment_status_type_id
order by asg.effective_start_date desc;
select fpm.process_method_code
from ghr_noa_families nof
,ghr_families fam
,ghr_noa_fam_proc_methods fpm
,ghr_pa_data_fields pdf
where nof.nature_of_action_id = p_pa_request_rec.first_noa_id
and nof.noa_family_code = fam.noa_family_code
and nof.enabled_flag = 'Y'
and p_pa_request_rec.effective_date between nvl(nof.start_date_active,p_pa_request_rec.effective_date)
and nvl(nof.end_date_active,p_pa_request_rec.effective_date)
and fam.proc_method_flag = 'Y'
and fam.enabled_flag = 'Y'
and p_pa_request_rec.effective_date between nvl(fam.start_date_active,p_pa_request_rec.effective_date)
and nvl(fam.end_date_active,p_pa_request_rec.effective_date)
and fam.noa_family_code = fpm.noa_family_code
and fpm.pa_data_field_id = pdf.pa_data_field_id
and fpm.enabled_flag = 'Y'
and p_pa_request_rec.effective_date between nvl(fpm.start_date_active,p_pa_request_rec.effective_date)
and nvl(fpm.end_date_active,p_pa_request_rec.effective_date)
and pdf.form_field_name = l_form_field_name
and pdf.enabled_flag = 'Y'
and p_pa_request_rec.effective_date between nvl(pdf.date_from,p_pa_request_rec.effective_date)
and nvl(pdf.date_to,p_pa_request_rec.effective_date);
SELECT effective_date
FROM ghr_pa_Requests
WHERE effective_date <= p_eff_Date
and person_id = p_pa_request_rec.person_id
--and employee_assignment_id = p_Asg_id
-- for performance reasons
and pa_notification_id is not null
and noa_family_code = 'NON_PAY_DUTY_STATUS'
ORDER BY pa_request_id desc;
SELECT pos.job_id
FROM hr_all_positions_f pos
WHERE pos.position_id = c_position_id
AND c_effective_date BETWEEN pos.effective_start_date and pos.effective_end_date;
SELECT ORG_INFORMATION5
FROM HR_ORGANIZATION_INFORMATION
WHERE ORG_INFORMATION_CONTEXT = 'GHR_US_ORG_INFORMATION'
AND ORGANIZATION_ID = c_business_group_id;
SELECT DECODE(c_segment,'SEGMENT1',SEGMENT1,
'SEGMENT2',SEGMENT2,
'SEGMENT3',SEGMENT3,
'SEGMENT4',SEGMENT4,
'SEGMENT5',SEGMENT5,
'SEGMENT6',SEGMENT6,
'SEGMENT7',SEGMENT7,
'SEGMENT8',SEGMENT8,
'SEGMENT9',SEGMENT9,
'SEGMENT10',SEGMENT10,
'SEGMENT11',SEGMENT11,
'SEGMENT12',SEGMENT12,
'SEGMENT13',SEGMENT13,
'SEGMENT14',SEGMENT14,
'SEGMENT15',SEGMENT15,
'SEGMENT16',SEGMENT16,
'SEGMENT17',SEGMENT17,
'SEGMENT18',SEGMENT18,
'SEGMENT19',SEGMENT19,
'SEGMENT20',SEGMENT20,
'SEGMENT21',SEGMENT21,
'SEGMENT22',SEGMENT22,
'SEGMENT23',SEGMENT23,
'SEGMENT24',SEGMENT24,
'SEGMENT25',SEGMENT25,
'SEGMENT26',SEGMENT26,
'SEGMENT27',SEGMENT27,
'SEGMENT28',SEGMENT28,
'SEGMENT29',SEGMENT29,
'SEGMENT30',SEGMENT30) agency_code
FROM per_position_definitions ppd, hr_all_positions_f pos
WHERE pos.position_definition_id = ppd.position_definition_id
AND pos.position_id = c_position_id
AND c_effective_date BETWEEN pos.effective_start_date AND pos.effective_end_date;
l_update_occ_code VARCHAR2(1) := 'N';
SELECT position_id
FROM per_all_assignments_f asg
WHERE asg.assignment_id = c_assignment_id
AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
SELECT rei_information12 nte_date, rei_information11 amount
FROM ghr_pa_request_extra_info
WHERE rei_information_category='GHR_US_PAR_MD_DDS_PAY'
AND pa_request_id = p_pa_request_rec.pa_request_id;
SELECT remark_id
FROM ghr_pa_remarks
WHERE pa_request_id = p_pa_request_rec.pa_request_id
AND remark_id =
(select remark_id from ghr_remarks
where code = 'BBB');
select rem.code
from ghr_remarks rem
where rem.remark_id = p_remark_id
and rem.enabled_flag = 'Y'
and nvl(p_pa_request_rec.effective_date,sysdate)
between rem.date_from and nvl(rem.date_to,nvl(p_pa_request_rec.effective_date, trunc(sysdate)));
ghr_sf52_pos_update.update_position_info
(p_pos_data_rec => l_position_data_rec_type
);
/* -- SEt flag to update assignment with the targer org.
l_employee_api_update_criteria := 'Y';
--while terminating the MDDDS_SPECIAL_PAY element it should delete the remarks also.
IF ( l_mddds_total_special_pay <> 0 )
and ( l_mddds_total_special_pay IS NOT NULL ) THEN
ghr_pa_remarks_api.create_pa_remarks
(
p_PA_REQUEST_ID => p_pa_request_rec.pa_request_id,
p_REMARK_ID => l_remark_id,
p_DESCRIPTION => l_remark_description,
P_PA_REMARK_ID => l_pa_remark_id,
p_OBJECT_VERSION_NUMBER => l_rem_ovn,
p_remark_code_information1 => l_mddds_special_pay_nte_date
);
--while terminating the MDDDS_SPECIAL_PAY element it should delete the remarks also.
IF ( l_mddds_total_special_pay = 0 )
OR ( l_mddds_total_special_pay IS NULL ) THEN
hr_utility.set_location('Inside else'||l_rpa_remark_id ,10);
-- Need to update Position only if OCC_CODE is different in RPA form than the Position.
-- Commented for testing
IF (p_pa_request_rec.to_job_id <> l_pos_job_id) OR (p_agency_code <> l_pos_agency_code) THEN
l_position_data_rec_type.position_id := p_pa_request_rec.to_position_id;
l_position_data_rec_type.datetrack_mode := return_update_mode
(p_id => p_pa_request_rec.to_position_id,
p_effective_date => p_pa_request_rec.effective_date,
p_table_name => 'HR_ALL_POSITIONS_F'
);
ghr_sf52_pos_update.update_position_info
(p_pos_data_rec => l_position_data_rec_type
);
l_update_occ_code := 'Y';
l_update_mode := 'CORRECTION';
l_employee_api_update_criteria := 'Y';
l_update_gre := 'Y';
l_employee_api_update_criteria := 'Y';
l_update_gre := 'Y'; -- Added Venkat Bug # 1239688
l_employee_api_update_criteria := 'Y';
l_update_gre := 'Y';
l_update_mode := 'UPDATE'; -- If action is Correction,then update_mode = 'CORRECTION'
-- should be updated
FOR l_get_asg_position IN c_get_asg_position(p_pa_request_rec.employee_assignment_id,
p_pa_request_rec.effective_date) LOOP
l_asg_position := l_get_asg_position.position_id;
l_employee_api_update_criteria := 'Y';
l_employee_api_update_criteria := 'Y';
l_update_gre := 'Y';
l_employee_api_update_criteria := 'Y';
l_employee_api_update_criteria := 'Y';
IF (l_update_occ_code = 'Y') THEN -- Sundar 3215139 Need to update assignment only if position is updated
l_employee_api_update_criteria := 'Y';
l_employee_api_update_criteria := 'Y';
l_update_person := 'Y';
l_update_address := 'Y';
hr_utility.set_location('Correction -- Update Address ' || l_proc,68);
l_update_person = 'Y' then
l_per_object_version_number := null;
delete from per_person_list_changes
where person_id = p_pa_request_rec.person_id
and nvl(termination_flag,hr_api.g_varchar2) = 'Y';
l_employee_api_update_criteria := 'Y';
l_employee_api_update_criteria := 'Y';
l_employee_api_update_criteria := 'Y';
If l_employee_api_update_criteria = 'Y' or l_update_gre = 'Y' then
-- Function to determine update_mode
l_update_mode := return_update_mode
(p_id => p_pa_request_rec.employee_assignment_id,
p_effective_date => p_pa_request_rec.effective_date,
p_table_name => 'PER_ASSIGNMENTS_F'
);
hr_utility.set_location('l_update_gre is '||l_update_gre,91);
If l_update_gre = 'Y' then
-- get the GRE . Passed into the foll. api as the p_tax_unit
for tax_unit in c_tax_unit_org loop
l_tax_unit_id := tax_unit.tax_unit_id;
savepoint update_emp_asg;
hr_assignment_api.update_us_emp_asg
(p_assignment_id => p_pa_request_rec.employee_assignment_id,
p_object_version_number => l_asg_object_version_number,
p_effective_date => g_effective_date,
p_datetrack_update_mode => l_update_mode,
p_comment_id => l_comment_id,
p_tax_unit => l_tax_unit_id, -- gre
p_soft_coding_keyflex_id => l_soft_coding_keyflex_id,
p_effective_start_date => l_asg_upd_effective_start_date,
p_effective_end_date => l_asg_upd_effective_end_date,
p_concatenated_segments => l_concatenated_segments,
p_no_managers_warning => l_asg_upd_org_now_man_warn,
p_other_manager_warning => l_asg_upd_other_manager_warn
);
l_update_mode := 'CORRECTION';
rollback to update_emp_asg;
hr_utility.set_location('After update_us_emp_asg '||l_proc,93);
If l_employee_api_update_criteria = 'Y' then
hr_utility.set_location('Before update_emp_asg_criteria ',94);
hr_utility.set_location('checking how correction updates the record',500);
hr_assignment_api.update_emp_asg_criteria
(p_effective_date => g_effective_date
,p_datetrack_update_mode => l_update_mode
,p_assignment_id => l_assignment_id
,p_object_version_number => l_asg_object_version_number
,P_PAYROLL_ID => l_payroll_id
,p_position_id => nvl(P_pa_request_rec.to_position_id,l_position_id) --nvl added for bug 4542437
,p_job_id => nvl(P_pa_request_rec.to_job_id,l_job_id) --nvl added for bug 4542437
,p_location_id => P_pa_request_rec.duty_station_location_id
,p_organization_id => nvl(P_pa_request_rec.to_organization_id,l_org_id)--nvl added for bug 4542437
,p_grade_id => nvl(P_pa_request_rec.to_grade_id,l_grade_id) --nvl added for bug 4542437
,p_effective_start_date => l_asg_upd_effective_start_date
,p_effective_end_date => l_asg_upd_effective_end_date
,p_special_ceiling_step_id => l_asg_upd_special_ceil_step_id
,p_people_group_id => l_asg_upd_people_group_id
,p_group_name => l_asg_upd_group_name
,p_org_now_no_manager_warning => l_asg_upd_org_now_man_warn
,p_other_manager_warning => l_asg_upd_other_manager_warn
,p_spp_delete_warning => l_asg_upd_spp_delete_warning
,p_entries_changed_warning => l_asg_upd_entries_chan_warn
,p_tax_district_changed_warning => l_asg_upd_tax_dist_chan_warn
);
hr_utility.set_location('After update_emp_asg_criteria '||l_proc,95);
l_update_mode := return_update_mode
(p_id => p_pa_request_rec.employee_assignment_id,
p_effective_date => p_pa_request_rec.effective_date,
p_table_name => 'PER_ASSIGNMENTS_F'
);
,p_datetrack_update_mode => l_update_mode
,p_assignment_id => l_assignment_id
,p_assignment_status_type_id => l_asg_status_type_id
,p_object_version_number => l_asg_object_version_number
,p_effective_start_date => l_asg_act_eff_start_date
,p_effective_end_date => l_asg_act_eff_end_date
);
l_update_mode := return_update_mode
(p_id => p_pa_request_rec.employee_assignment_id,
p_effective_date => p_pa_request_rec.effective_date,
p_table_name => 'PER_ASSIGNMENTS_F'
);
,p_datetrack_update_mode => l_update_mode
,p_assignment_id => l_assignment_id
,p_object_version_number => l_asg_object_version_number
,p_assignment_status_type_id => l_asg_status_type_id
,p_effective_start_date => l_asg_sus_eff_start_date
,p_effective_end_date => l_asg_sus_eff_end_date
);
insert into per_person_list_changes
(person_id
,security_profile_id
,include_flag
,termination_flag)
select l.person_id
,l.security_profile_id
,'Y'
,'Y'
from per_person_list l
where l.person_id = p_pa_request_rec.person_id
and not exists
(Select 1
From per_person_list_changes pplc
Where pplc.person_id = p_pa_request_rec.person_id
And pplc.security_profile_id = l.security_profile_id
);
l_position_data_rec_type.datetrack_mode := return_update_mode
(p_id => p_pa_request_rec.to_position_id,
p_effective_date => p_pa_request_rec.effective_date,
p_table_name => 'HR_ALL_POSITIONS_F'
);
ghr_sf52_pos_update.update_position_info
(p_pos_data_rec => l_position_data_rec_type);
If l_update_person = 'Y' then
hr_utility.set_location(l_proc,138);
l_update_mode := return_update_mode
(p_id => p_pa_request_rec.person_id,
p_effective_date => p_pa_request_rec.effective_date,
p_table_name => 'PER_PEOPLE_F'
);
hr_person_api.update_person
(p_effective_date => g_effective_date
,p_datetrack_update_mode => l_update_mode
,p_person_id => p_pa_request_rec.person_id
,p_object_version_number => l_per_object_version_number
,p_employee_number => l_per_upd_employee_number
,p_last_name => p_pa_request_rec.employee_last_name
,p_first_name => p_pa_request_rec.employee_first_name
,p_middle_names => p_pa_request_rec.employee_middle_names
,p_national_identifier => p_pa_request_rec.employee_national_identifier
,p_date_of_birth => p_pa_request_rec.employee_date_of_birth
,p_effective_start_date => l_per_upd_effective_start_date
,p_effective_end_date => l_per_upd_effective_end_date
,p_full_name => l_per_upd_full_name
,p_comment_id => l_per_upd_comment_id
,p_name_combination_warning => l_per_upd_name_comb_warn
,p_assign_payroll_warning => l_per_upd_assgn_payroll_warn
,p_orig_hire_warning => l_orig_hire_warning
);
If l_create_address = 'Y' or l_update_address = 'Y' then
hr_utility.set_location('in upd addresses',1000);
hr_person_address_api.update_us_person_address
(p_effective_date => g_effective_date
,p_date_to => g_effective_date
,p_address_id => sec_address.address_id
,p_object_version_number => sec_address.object_version_number
);
hr_person_address_api.update_us_person_address
(p_effective_date => g_effective_date
,p_date_to => g_effective_date
,p_address_id => l_address_id
,p_object_version_number => l_add_object_version_number
);
If l_update_address = 'Y' then
hr_utility.set_location(l_proc,150);
hr_person_address_api.update_us_person_address
(p_address_id => l_address_id
,p_object_version_number => l_add_object_version_number
,p_effective_date => g_effective_date
,p_address_line1 => p_pa_request_rec.forwarding_address_line1
,p_address_line2 => p_pa_request_rec.forwarding_address_line2
,p_address_line3 => p_pa_request_rec.forwarding_address_line3
,p_city => p_pa_request_rec.forwarding_town_or_city
,p_state => p_pa_request_rec.forwarding_region_2
,p_county => l_county_name
,p_zip_code => p_pa_request_rec.forwarding_postal_code
,p_country => p_pa_request_rec.forwarding_country
);
If l_create_address = 'Y' or l_update_address = 'Y' then
for county in county_name loop
l_county_name := county.county_name;
hr_person_address_api.update_person_address
(p_effective_date => g_effective_date
,p_date_to => g_effective_date
,p_address_id => sec_address.address_id
,p_object_version_number => sec_address.object_version_number
);
hr_person_address_api.update_person_address
(p_effective_date => g_effective_date
,p_date_to => g_effective_date
,p_address_id => l_address_id
,p_object_version_number => l_add_object_version_number
);
If l_update_address = 'Y' then
hr_utility.set_location(l_proc,150);
hr_person_address_api.update_person_address
(p_address_id => l_address_id
,p_object_version_number => l_add_object_version_number
,p_effective_date => g_effective_date
,p_address_line1 => p_pa_request_rec.forwarding_address_line1
,p_address_line2 => p_pa_request_rec.forwarding_address_line2
,p_address_line3 => p_pa_request_rec.forwarding_address_line3
,p_town_or_city => p_pa_request_rec.forwarding_town_or_city
,p_region_2 => p_pa_request_rec.forwarding_region_2
,p_region_1 => l_county_name
,p_postal_code => p_pa_request_rec.forwarding_postal_code
,p_country => p_pa_request_rec.forwarding_country
);
l_update_mode := return_update_mode
(p_id => p_pa_request_rec.person_id,
p_effective_date => p_pa_request_rec.effective_date,
p_table_name => 'PER_PEOPLE_F'
);
hr_person_api.update_person
(p_effective_date => g_effective_date
,p_datetrack_update_mode => l_update_mode
,p_person_id => p_pa_request_rec.person_id
,p_object_version_number => l_per_object_version_number
,p_employee_number => l_per_upd_employee_number
,p_last_name => p_pa_request_rec.employee_last_name
,p_first_name => p_pa_request_rec.employee_first_name
,p_middle_names => p_pa_request_rec.employee_middle_names
,p_national_identifier => p_pa_request_rec.employee_national_identifier
,p_date_of_birth => p_pa_request_rec.employee_date_of_birth
,p_effective_start_date => l_per_upd_effective_start_date
,p_effective_end_date => l_per_upd_effective_end_date
,p_full_name => l_per_upd_full_name
,p_comment_id => l_per_upd_comment_id
,p_name_combination_warning => l_per_upd_name_comb_warn
,p_assign_payroll_warning => l_per_upd_assgn_payroll_warn
,p_orig_hire_warning => l_orig_hire_warning
);
hr_position_api.update_position
(p_position_id => p_pa_request_rec.from_position_id,
p_date_end => p_pa_request_rec.effective_date,
p_object_version_number => l_pos_object_version_number,
p_position_definition_id => l_pos_definition_id,
p_name => l_name,
p_valid_grades_changed_warning => l_val_grd_chg_wng
);
hr_utility.set_location('Update Positions Location ' || l_proc,151);
ghr_sf52_pos_update.update_positions_location(
p_position_id => p_pa_request_rec.to_position_id,
p_location_id => p_pa_request_rec.duty_station_location_id,
p_effective_date => p_pa_request_rec.effective_date);
select eev.screen_entry_value screen_entry_value,
ele.element_entry_id
from pay_element_types_f elt,
pay_input_values_f ipv,
pay_element_entries_f ele,
pay_element_entry_values_f eev
where trunc(eff_date)
between elt.effective_start_date and elt.effective_end_date
and trunc(eff_date)
between ipv.effective_start_date and ipv.effective_end_date
and trunc(eff_date)
between ele.effective_start_date and ele.effective_end_date
and trunc(eff_date)
between eev.effective_start_date and eev.effective_end_date
and elt.element_type_id = ipv.element_type_id
and upper(elt.element_name) = upper(ele_name)
and ipv.input_value_id = eev.input_value_id
and ele.assignment_id = p_pa_request_rec.employee_assignment_id
and ele.element_entry_id + 0 = eev.element_entry_id
and upper(ipv.name) = upper( ipv_name)
-- and NVL(elt.business_group_id,0) = NVL(ipv.business_group_id,0) -- modified by Ashley
and (elt.business_group_id is null or elt.business_group_id = bg_id);
select object_version_number,
pei_information2,
pei_information3,
pei_information4,
pei_information5,
pei_information6,
pei_information7,
pei_information8,
pei_information9
from per_people_extra_info
where person_extra_info_id = l_retained_grade_rec.person_extra_info_id;
SELECT rei_information3 temp_step
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_rec.pa_request_id
AND information_type = 'GHR_US_PAR_RG_TEMP_PROMO';
Select distinct business_group_id bg
from per_assignments_f
where assignment_id = p_assignment_id
and p_eff_date between effective_start_date
and effective_end_date;
Select pb.pay_basis_id
From pay_element_types_f ele,
pay_input_values_f inp,
per_pay_bases pb
where ele.business_group_id=p_bg_id
and upper(element_name)=upper(p_ele_name)
and ele.business_group_id=inp.business_group_id
and ele.element_type_id=inp.element_type_id
and inp.input_value_id=pb.input_value_id;
select object_version_number ovn,
people_group_id ppl_grp_id,
special_ceiling_step_id spcl_clng_stp_id,
soft_coding_keyflex_id scl_kff_id,
effective_start_date start_date,
effective_end_date end_date,
payroll_id
from per_assignments_f
where assignment_id=p_asg_id
-- and position_id = p_pa_request_rec.to_position_id
and p_eff_date
between effective_start_date and effective_end_date;
l_spp_delete_warning boolean;
Select ppp.pay_proposal_id proposal_id,
ppp.object_version_number ovn
from per_pay_proposals ppp
where ppp.assignment_id = p_assignment_id
and change_date = p_eff_date;
select 1
from per_person_types pet,
per_people_f per
where pet.person_type_id = per.person_type_id
and per.person_id = p_person_id
and p_effective_date
between per.effective_start_date and per.effective_end_date
and pet.system_person_type = 'EX_EMP';
SELECT rei_information4 wgi_due
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_rec.pa_request_id
AND information_type = 'GHR_US_PAR_SALARY_CHG';
SELECT par_orig.from_step_or_rate step1, par_corr.from_step_or_rate step2
FROM ghr_pa_requests par_orig , ghr_pa_requests par_corr
where par_orig.pa_request_id = par_corr.altered_pa_request_id
and par_corr.pa_request_id = c_pa_request_id;
SELECT 1
FROM ghr_pay_plans gpp
WHERE gpp.pay_plan = c_pay_plan
AND gpp.wgi_enabled_flag = 'Y';
SELECT to_pay_plan
FROM ghr_pa_requests par
WHERE par.pa_request_id = c_pa_request_id;
select EQUIVALENT_PAY_PLAN
from ghr_pay_plans
where pay_plan = c_pay_plan;
SELECT 1
FROM pay_element_entries_f pee, pay_element_types_f pet
WHERE pee.element_type_id = pet.element_type_id
AND c_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND pet.element_name = c_element_name
AND pee.assignment_id = c_assignment_id;
hr_utility.trace('Before call to Delete Salary Proposal :'||l_dt_mode);
hr_maintain_proposal_api.delete_salary_proposal
(
p_pay_proposal_id => l_pay_proposal_id ,
p_business_group_id => l_bg_id ,
p_object_version_number => l_sal_admin_ovn ,
p_validate => FALSE ,
p_salary_warning => l_sal_warn
);
l_dt_mode := return_update_mode
(p_id => p_pa_request_rec.employee_assignment_id,
p_effective_date => l_effective_date,
p_table_name => 'PER_ASSIGNMENTS_F'
);
hr_assignment_api.update_emp_asg_criteria
(p_effective_date => l_effective_date
,p_datetrack_update_mode => l_dt_mode
,p_assignment_id => p_pa_request_rec.employee_assignment_id
,p_object_version_number => l_SB_ovn
,P_PAYROLL_ID => l_payroll_id
,p_pay_basis_id => l_sal_basis_id
,p_position_id => P_pa_request_rec.to_position_id
,p_job_id => P_pa_request_rec.to_job_id
,p_location_id => P_pa_request_rec.duty_station_location_id
,p_organization_id => P_pa_request_rec.to_organization_id
,p_grade_id => P_pa_request_rec.to_grade_id
,p_effective_start_date => l_eff_start_date
,p_effective_end_date => l_eff_end_date
,p_special_ceiling_step_id => l_spcl_clng_stp_id
,p_people_group_id => l_ppl_grp_id
,p_group_name => l_group_name
,p_org_now_no_manager_warning => l_org_now_man_warn
,p_other_manager_warning => l_other_manager_warn
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_chan_warn
,p_tax_district_changed_warning => l_tax_dist_chan_warn
);
hr_utility.trace('After Update Person record under gh52doup.pkb');
hr_maintain_proposal_api.insert_salary_proposal
(
p_pay_proposal_id => l_pay_proposal_id
,p_assignment_id => p_pa_request_rec.employee_assignment_id
,p_business_group_id => l_bg_id
,p_change_date => l_effective_date
,p_proposed_salary_n => p_pa_request_rec.to_basic_pay
,p_object_version_number => l_sal_admin_ovn
,p_element_entry_id => ll_element_entry_id
,p_inv_next_sal_date_warning => l_date_warn
,p_proposed_salary_warning => l_sal_warn
,p_approved_warning => l_approve_warn
,p_payroll_warning => l_payroll_warn
,p_multiple_components => 'N'
,p_approved => 'Y'
);
hr_maintain_proposal_api.delete_salary_proposal(
p_pay_proposal_id => l_pay_proposal_id
,p_business_group_id => l_bg_id
,p_object_version_number => l_sal_admin_ovn
,p_validate => FALSE
,p_salary_warning => l_sal_warn);
hr_maintain_proposal_api.insert_salary_proposal
(
p_pay_proposal_id => l_pay_proposal_id
,p_assignment_id => p_pa_request_rec.employee_assignment_id
,p_business_group_id => l_bg_id
,p_change_date => l_effective_date
,p_proposed_salary_n => p_pa_request_rec.to_basic_pay
,p_object_version_number => l_sal_admin_ovn
,p_element_entry_id => ll_element_entry_id
,p_inv_next_sal_date_warning => l_date_warn
,p_proposed_salary_warning => l_sal_warn
,p_approved_warning => l_approve_warn
,p_payroll_warning => l_payroll_warn
,p_multiple_components => 'N'
,p_approved => 'Y'
);
/* To be included after Martin Reid's element api handles the create and update warning
if l_adj_basic_pay_warn = FALSE then
hr_utility.set_message(8301,'GHR_38136_FAIL_TO_UPD_SALARY');
-- If Pay plan is not eligible, update with NULL
IF l_is_wgi_eligible = FALSE THEN
-- Check if WGI element is present or not. If present only we need to update
-- Get Element Name
l_wgi_new_name := pqp_fedhr_uspay_int_utils.return_new_element_name(
p_fedhr_element_name =>'Within Grade Increase',
p_business_group_id => l_bg_id,
p_effective_date => p_pa_request_rec.effective_date);
ghr_sf52_do_update.get_wgi_dates
(p_pa_request_rec => p_pa_request_rec,
p_wgi_due_date => l_wgi_due_date,
p_wgi_pay_date => l_wgi_pay_date,
p_retained_grade_rec => l_retained_grade_rec,
p_dlei => NULL
);
-- it comes here. Need to update NULL in that case. .
ELSIF nvl(p_pa_request_rec.noa_family_code,hr_api.g_varchar2) IN ('APP','CONV_APP')
AND p_wgi.p_last_equi_incr IS NOT NULL AND l_wgi_cleared = FALSE THEN
hr_utility.set_location('Inside elsif loop',511);
ghr_sf52_do_update.get_wgi_dates
(p_pa_request_rec => p_pa_request_rec,
p_wgi_due_date => l_wgi_due_date,
p_wgi_pay_date => l_wgi_pay_date,
p_retained_grade_rec => l_retained_grade_rec,
p_dlei => fnd_date.canonical_to_date(p_wgi.p_last_equi_incr)
);
-- Bug 3617295 DLEI should be updated with RPA effective date
IF p_pa_request_rec.first_noa_code = '855' OR p_pa_request_rec.second_noa_code = '855' THEN
p_wgi.p_last_equi_incr := fnd_date.date_to_canonical(p_pa_request_rec.effective_date); -- Bug 3991240
hr_utility.set_location('update retained grade info',1);
hr_person_extra_info_api.update_person_extra_info
(p_person_extra_info_id => l_retained_grade_rec.person_extra_info_id,
p_object_version_number => l_ret_object_version_number,
p_pei_information2 => fnd_date.date_to_canonical(p_pa_request_rec.effective_date - 1)
);
hr_person_extra_info_api.update_person_extra_info
(p_person_extra_info_id => l_retained_grade_rec.person_extra_info_id,
p_object_version_number => l_ret_object_version_number,
p_pei_information2 => fnd_date.date_to_canonical(p_pa_request_rec.effective_date - 1)
);
hr_person_extra_info_api.update_person_extra_info
(p_person_extra_info_id => l_retained_grade_rec.person_extra_info_id,
p_object_version_number => l_ret_object_version_number,
p_pei_information4 => l_retained_grade_rec.step_or_rate
);
hr_person_extra_info_api.update_person_extra_info
(p_person_extra_info_id => l_retained_grade_rec.person_extra_info_id,
p_object_version_number => l_ret_object_version_number,
p_pei_information2 => fnd_date.date_to_canonical(p_pa_request_rec.effective_date - 1)
);
hr_person_extra_info_api.update_person_extra_info
(p_person_extra_info_id => l_retained_grade_rec.person_extra_info_id,
p_object_version_number => l_ret_object_version_number,
p_pei_information9 => l_new_temp_step
);
hr_person_extra_info_api.update_person_extra_info
(p_person_extra_info_id => l_retained_grade_rec.person_extra_info_id,
p_object_version_number => l_ret_object_version_number,
p_pei_information2 => fnd_date.date_to_canonical(p_pa_request_rec.effective_date - 1)
);
ghr_mass_actions_pkg.replace_insertion_values
(p_desc => l_remark_desc,
p_information1 => l_remark_information1,
p_information2 => l_remark_information2,
p_information3 => l_remark_information3,
p_information4 => l_remark_information4,
p_information5 => l_remark_information5,
p_desc_out => l_remark_desc_out
);
SELECT pa_incentive_category_amount amount,
pa_incentive_category_pmnt_dt payment_date
FROM ghr_pa_incentives
where pa_request_id = l_pa_request_id
order by pa_incentive_category_pmnt_dt;
select ele.element_entry_id,
ipv.name,
ipv.input_value_id,
ipv.uom,
eev.screen_entry_value screen_entry_value,
ele.object_version_number
from pay_element_types_f elt,
pay_input_values_f ipv,
pay_element_entries_f ele,
pay_element_entry_values_f eev
where trunc(eff_date) between elt.effective_start_date
and elt.effective_end_date
and trunc(eff_date) between ipv.effective_start_date
and ipv.effective_end_date
and trunc(eff_date) between ele.effective_start_date
and ele.effective_end_date
and trunc(eff_date) between eev.effective_start_date
and eev.effective_end_date
and elt.element_type_id = ipv.element_type_id
and upper(elt.element_name) = upper(ele_name)
and ipv.input_value_id = eev.input_value_id
and ele.assignment_id = asg_id
and ele.element_entry_id + 0 = eev.element_entry_id
and (elt.business_group_id is null or elt.business_group_id = bg_id)
order by ele.element_entry_id,ipv.input_value_id;
select asg.business_group_id
from per_all_assignments_f asg
where asg.assignment_id = asg_id
and eff_date between asg.effective_start_date
and asg.effective_end_date;
l_update_mode VARCHAR2(25);
l_update_warning boolean;
l_update_mode := 'CORRECTION';
py_element_entry_api.update_element_entry
(p_datetrack_update_mode => l_update_mode
,p_effective_date => p_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_input_value_id1 => l_input_value_id1
,p_entry_value1 => l_value1
,p_input_value_id2 => l_input_value_id2
,p_entry_value2 => l_value2
,p_input_value_id3 => l_input_value_id3
,p_entry_value3 => l_value3
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => l_update_warning);
SELECT pa_incentive_category,
pa_incentive_category_amount,
pa_incentive_category_percent,
pa_incentive_category_pmnt_dt,
pa_incentive_category_end_date
FROM ghr_pa_incentives
where pa_request_id = l_pa_request_id
order by pa_incentive_category_pmnt_dt;
/* To be included after Martin Reid's element api handles the create and update warning
if l_fegli_warn = FALSE then
hr_utility.set_message(8301,'GHR_38141_FAIL_TO_UPD_FEGLI');
select rei_information3 payroll_type
from ghr_pa_request_extra_info
where pa_request_id = p_pa_request_rec.pa_request_id
and information_type = 'GHR_US_PAR_PAYROLL_TYPE';
select asg.payroll_id
from per_all_assignments_f asg
where asg.assignment_id = p_pa_request_rec.employee_assignment_id;
select gpp.equivalent_pay_plan,gpp.maximum_Step
from ghr_pay_plans gpp
where gpp.pay_plan = l_pay_plan
and gpp.wgi_enabled_flag = 'Y'; -- **also check for WGI enabled flag
select gpw.to_step -- l_from_step
from ghr_pay_plan_waiting_periods gpw
where gpw.from_Step = l_step_or_rate
and gpw.pay_plan = l_eq_pay_plan; -- pay plan -????
select gpw.waiting_period -- l_wait_period
from ghr_pay_plan_waiting_periods gpw
where gpw.pay_plan = l_eq_pay_plan
and gpw.from_step = l_from_step; -- p_pa_request_rec.to_step_or_rate
select ptp.start_Date
from per_time_periods ptp
where ptp.payroll_id = l_payroll_id -- in case of p_wgi_due_date not null also get the payroll id
and ptp.start_date >= p_wgi_due_date
order by ptp.start_date asc ;
Procedure generic_update_sit
(p_pa_request_rec in ghr_pa_requests%rowtype,
p_special_information_type in fnd_id_flex_structures_tl.id_flex_structure_name%type,
p_segment_rec in ghr_api.special_information_type
)
is
l_proc varchar2(72) := 'Generic Update SIT';
select business_group_id
from per_all_people_f
where person_id = p_pa_request_rec.person_id;
select id_flex_num
from fnd_id_flex_structures_tl
where id_flex_structure_name = p_special_information_type
and id_flex_code = 'PEA' --??
and application_id = 800
and language = 'US'; --??
select multiple_occurrences_flag
from per_special_info_types sit
where business_group_id = l_business_group_id
and id_flex_num = l_id_flex_num;
Select date_from
from per_person_analyses
where person_analysis_id = p_segment_rec.person_analysis_id;
hr_sit_api.update_sit
(p_person_analysis_id => p_segment_rec.person_analysis_id,
p_date_from => l_date_from,
p_segment1 => p_segment_rec.segment1,
p_segment2 => p_segment_rec.segment2,
p_segment3 => p_segment_rec.segment3,
p_segment4 => p_segment_rec.segment4,
p_segment5 => p_segment_rec.segment5,
p_segment6 => p_segment_rec.segment6,
p_segment7 => p_segment_rec.segment7,
p_segment8 => p_segment_rec.segment8,
p_segment9 => p_segment_rec.segment9,
p_segment10 => p_segment_rec.segment10,
p_segment11 => p_segment_rec.segment11,
p_segment12 => p_segment_rec.segment12,
p_segment13 => p_segment_rec.segment13,
p_segment14 => p_segment_rec.segment14,
p_segment15 => p_segment_rec.segment15,
p_segment16 => p_segment_rec.segment16,
p_segment17 => p_segment_rec.segment17,
p_segment18 => p_segment_rec.segment18,
p_segment19 => p_segment_rec.segment19,
p_segment20 => p_segment_rec.segment20,
p_segment21 => p_segment_rec.segment21,
p_segment22 => p_segment_rec.segment22,
p_segment23 => p_segment_rec.segment23,
p_segment24 => p_segment_rec.segment24,
p_segment25 => p_segment_rec.segment25,
p_segment26 => p_segment_rec.segment26,
p_segment27 => p_segment_rec.segment27,
p_segment28 => p_segment_rec.segment28,
p_segment29 => p_segment_rec.segment29,
p_segment30 => p_segment_rec.segment30,
p_analysis_criteria_id => l_analysis_criteria_id,
p_pea_object_version_number => l_object_version_number
);
end; -- END OF generic_update_sit