[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_mass_action_select_flag in varchar2 default null,
p_mass_action_comments in varchar2 default null,
-- Bug#4486823 RRR Changes
p_payment_option in varchar2 default null,
p_award_salary in number default null,
-- Bug#4486823 RRR Changes
p_par_object_version_number out nocopy number,
p_1_pa_routing_history_id out nocopy number,
p_1_prh_object_version_number out nocopy number,
p_2_pa_routing_history_id out nocopy number,
p_2_prh_object_version_number out nocopy number
)is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'create_sf52';
select usr.employee_id,
per.first_name,
per.last_name,
per.middle_names
from per_people_f per,
fnd_user usr
where upper(usr.user_name) = upper(p_1_user_name_acted_on)
and per.person_id = usr.employee_id
and l_effective_date
between effective_start_date
and effective_end_date; */
SELECT usr.employee_id,
per.first_name,
per.last_name,
per.middle_names
FROM per_people_f per,
fnd_user usr
WHERE usr.user_name = upper(p_1_user_name_acted_on)
AND per.person_id = usr.employee_id
AND trunc(sysdate)
BETWEEN effective_start_date
AND effective_end_date;
SELECT rlm.seq_number,
rlm.groupbox_id,
rlm.user_name
FROM ghr_routing_list_members rlm
WHERE rlm.routing_list_id = p_2_routing_list_id
ORDER BY rlm.seq_number ASC;
SELECT 1
FROM ghr_pa_routing_history prh
WHERE prh.pa_request_id = l_pa_request_id;
SELECT gbx.name
FROM ghr_groupboxes gbx
WHERE gbx.groupbox_id = l_2_groupbox_id;
SELECT par.object_version_number
FROM ghr_pa_requests par
WHERE par.pa_request_id = l_pa_request_id;
select fams.noa_family_code
from ghr_noa_families noafam,
ghr_families fams
where noafam.nature_of_action_id = p_second_noa_id
and noafam.enabled_flag = 'Y'
and fams.noa_family_code = noafam.noa_family_code
and fams.enabled_flag = 'Y'
and fams.update_hr_flag = 'Y'
and l_effective_date between nvl(noafam.start_date_active,to_date('1951/01/01','RRRR/MM/DD'))
and nvl(noafam.end_date_active,to_date('4712/12/31','RRRR/MM/DD'));
p_mass_action_select_flag => p_mass_action_select_flag,
p_mass_action_comments => p_mass_action_comments
);
If nvl(p_1_action_taken,hr_api.g_varchar2) in ('UPDATE_HR','FUTURE_ACTION') then
If p_approval_date is not null then
l_sf50_approval_date := p_approval_date;
p_mass_action_select_flag => p_mass_action_select_flag,
p_mass_action_comments => p_mass_action_comments ,
-- Bug#4486823 RRR Changes
p_payment_option => p_payment_option,
p_award_salary => p_award_salary
-- Bug#4486823 RRR Changes
);
insert into ghr_pa_remarks
(pa_remark_id
,pa_request_id
,remark_id
,description
,object_version_number
)
select ghr_pa_remarks_s.nextval
,l_pa_request_id
,rem.remark_id
,rem.description
,1
from ghr_remarks rem,
ghr_noac_remarks nre
where nre.nature_of_action_id = p_first_noa_id
and nre.required_flag = 'Y'
and l_effective_date
between nre.date_from
and nvl(nre.date_to,l_effective_date)
and nre.remark_id = rem.remark_id;
insert into ghr_pa_remarks
(pa_remark_id
,pa_request_id
,remark_id
,description
,object_version_number
)
select ghr_pa_remarks_s.nextval
,l_pa_request_id
,rem.remark_id
,rem.description
,1
from ghr_remarks rem,
ghr_noac_remarks nre
where nre.nature_of_action_id = p_second_noa_id
and nre.required_flag = 'Y'
and l_effective_date
between nre.date_from
and nvl(nre.date_to,l_effective_date)
and nre.remark_id = rem.remark_id;
'NO_ACTION','REVIEWED','CANCELED','UPDATE_HR','UPDATE_HR_COMPLETE', 'END_ROUTING')
then
hr_utility.set_message(8301,'GHR_38110_INVALID_ACTION_TAKEN');
if l_action_taken not in ('CANCELED','UPDATE_HR','UPDATE_HR_COMPLETE','NOT_ROUTED','END_ROUTING') then
if p_2_user_name_routed_to is null and
p_2_groupbox_id is null and
p_2_routing_list_id is null then
hr_utility.set_message(8301,'GHR_38115_ROUT_INFO_REQD');
('CANCELED','UPDATE_HR_COMPLETE') then
----If (p_mass_action_id is not null and nvl(p_rpa_type,hr_api.g_varchar2) <> 'TA') or (p_mass_action_id is null) then
-- Comment above line because irrespective of p_mass_action_id value the if condn should be true (AVR)
If nvl(p_rpa_type,hr_api.g_varchar2) <> 'TA' then
if l_action_taken = 'UPDATE_HR' and trunc(p_effective_date) > trunc(sysdate) then
l_action_taken := 'FUTURE_ACTION';
if nvl(l_action_taken,hr_api.g_varchar2) not in ('NOT_ROUTED','UPDATE_HR','FUTURE_ACTION','END_ROUTING') then
ghr_prh_ins.ins
(p_pa_routing_history_id => p_2_pa_routing_history_id,
p_pa_request_id => l_pa_request_id,
p_attachment_modified_flag => 'N',
p_initiator_flag => 'N',
p_approver_flag => 'N',
p_reviewer_flag => 'N',
p_requester_flag => 'N',
p_authorizer_flag => 'N',
p_personnelist_flag => 'N',
p_approved_flag => 'N',
p_user_name => l_2_user_name,
p_groupbox_id => l_2_groupbox_id,
p_routing_list_id => p_2_routing_list_id,
p_routing_seq_number => l_2_routing_seq_number,
p_noa_family_code => p_noa_family_code,
p_nature_of_action_id => p_first_noa_id,
p_second_nature_of_action_id => p_second_noa_id,
p_object_version_number => p_2_prh_object_version_number
-- p_validate => false
);
if l_action_taken in ('UPDATE_HR','FUTURE_ACTION','END_ROUTING') then
-- call update_hr with the p_pa_request_rec data
l_rec.pa_request_id := l_pa_request_id;
l_rec.mass_action_select_flag := p_mass_action_select_flag;
ghr_sf52_api.update_detail_remarks( p_effective_date => l_effective_date,
p_pa_request_id => l_pa_request_id);
ghr_sf52_post_update.get_notification_details
(p_pa_request_id => l_pa_request_id,
p_effective_date => p_effective_date,
-- p_object_version_number => p_imm_pa_request_rec.object_version_number,
p_from_position_id => l_rec.from_position_id,
p_to_position_id => l_rec.to_position_id,
p_agency_code => l_rec.agency_code,
p_from_agency_code => l_rec.from_agency_code,
p_from_agency_desc => l_rec.from_agency_desc,
p_from_office_symbol => l_rec.from_office_symbol,
p_personnel_office_id => l_rec.personnel_office_id,
p_employee_dept_or_agency => l_rec.employee_dept_or_agency,
p_to_office_symbol => l_rec.to_office_symbol
);
ghr_agency_update.ghr_agency_upd(
p_pa_request_rec => l_rec,
p_asg_sf52 => l_asg_sf52,
p_asg_non_sf52 => l_asg_non_sf52,
p_asg_nte_dates => l_asg_nte_dates,
p_per_sf52 => l_per_sf52,
p_per_group1 => l_per_group1,
p_per_group2 => l_per_group2,
p_per_scd_info => l_per_scd_info,
p_per_retained_grade => l_per_retained_grade,
p_per_probations => l_per_probations,
p_per_sep_retire => l_per_sep_retire,
p_per_security => l_per_security,
p_per_conversions => l_per_conversions,
p_per_uniformed_services=> l_per_uniformed_services,
p_pos_oblig => l_pos_oblig,
p_pos_grp2 => l_pos_grp2,
p_pos_grp1 => l_pos_grp1,
p_pos_valid_grade => l_pos_valid_grade,
p_pos_car_prog => l_pos_car_prog,
p_loc_info => l_loc_info,
p_wgi => l_wgi,
p_recruitment_bonus => l_recruitment_bonus,
p_relocation_bonus => l_relocation_bonus,
p_sf52_from_data => l_sf52_from_data,
p_personal_info => l_personal_info,
p_gov_awards_type => l_gov_awards_type,
p_perf_appraisal_type => l_perf_appraisal_type,
p_payroll_type => l_payroll_type,
p_conduct_perf_type => l_conduct_perf_type,
p_agency_sf52 => l_agency_sf52,
p_agency_code => l_agency_code,
p_entitlement => l_imm_entitlement,
p_foreign_lang_prof_pay => l_imm_foreign_lang_prof_pay,
p_edp_pay => l_imm_edp_pay,
p_hazard_pay => l_imm_hazard_pay,
p_health_benefits => l_imm_health_benefits,
p_danger_pay => l_imm_danger_pay,
p_imminent_danger_pay => l_imm_imminent_danger_pay,
p_living_quarters_allow => l_imm_living_quarters_allow,
p_post_diff_amt => l_imm_post_diff_amt,
p_post_diff_percent => l_imm_post_diff_percent,
p_sep_maintenance_allow => l_imm_sep_maintenance_allow,
p_supplemental_post_allow => l_imm_supplemental_post_allow,
p_temp_lodge_allow => l_imm_temp_lodge_allow,
p_premium_pay => l_imm_premium_pay,
p_retirement_annuity => l_imm_retirement_annuity,
p_severance_pay => l_imm_severance_pay,
p_thrift_saving_plan => l_imm_thrift_saving_plan,
p_retention_allow_review => l_imm_retention_allow_review,
p_health_ben_pre_tax => l_imm_health_ben_pre_tax,
p_per_benefit_info => l_imm_per_benefit_info,
p_imm_retirement_info => l_imm_retirement_info, --Bug# 7131104
--Begin Bug# 10181661
p_asg_detail_info => l_imm_asg_detail_info,
p_ipa_detail_ben_cont_info => l_imm_ipa_benefits_cont
--End Bug# 10181661
);
if (l_action_taken not in ('UPDATE_HR','END_ROUTING')) then
-- call workflow
ghr_api.call_workflow
(p_pa_request_id => l_pa_request_id
,p_action_taken => l_action_taken
);
If l_action_taken <> 'UPDATE_HR' then
hr_utility.set_message(8301,'GHR_38399_52_NOT_PROCESSED');
p_mass_action_select_flag => p_mass_action_select_flag,
p_mass_action_comments => p_mass_action_comments
);
procedure update_sf52
(p_validate in boolean default false,
p_pa_request_id in number,
p_noa_family_code in varchar2 default hr_api.g_varchar2,
p_routing_group_id in number default hr_api.g_number,
p_par_object_version_number in out nocopy number,
p_proposed_effective_asap_flag in varchar2 default hr_api.g_varchar2,
p_academic_discipline in varchar2 default hr_api.g_varchar2,
p_additional_info_person_id in number default hr_api.g_number,
p_additional_info_tel_number in varchar2 default hr_api.g_varchar2,
p_altered_pa_request_id in number default hr_api.g_number,
p_annuitant_indicator in varchar2 default hr_api.g_varchar2,
p_annuitant_indicator_desc in varchar2 default hr_api.g_varchar2,
p_appropriation_code1 in varchar2 default hr_api.g_varchar2,
p_appropriation_code2 in varchar2 default hr_api.g_varchar2,
p_approval_date in date default hr_api.g_date,
p_approving_official_full_name in varchar2 default hr_api.g_varchar2,
p_approving_official_work_titl in varchar2 default hr_api.g_varchar2,
p_authorized_by_person_id in number default hr_api.g_number,
p_authorized_by_title in varchar2 default hr_api.g_varchar2,
p_award_amount in number default hr_api.g_number,
p_award_uom in varchar2 default hr_api.g_varchar2,
p_bargaining_unit_status in varchar2 default hr_api.g_varchar2,
p_citizenship in varchar2 default hr_api.g_varchar2,
p_concurrence_date in date default hr_api.g_date,
p_custom_pay_calc_flag in varchar2 default hr_api.g_varchar2,
p_duty_station_code in varchar2 default hr_api.g_varchar2,
p_duty_station_desc in varchar2 default hr_api.g_varchar2,
p_duty_station_id in number default hr_api.g_number,
p_duty_station_location_id in number default hr_api.g_number,
p_education_level in varchar2 default hr_api.g_varchar2,
p_effective_date in date default hr_api.g_date,
p_employee_assignment_id in number default hr_api.g_number,
p_employee_date_of_birth in date default hr_api.g_date,
p_employee_first_name in varchar2 default hr_api.g_varchar2,
p_employee_last_name in varchar2 default hr_api.g_varchar2,
p_employee_middle_names in varchar2 default hr_api.g_varchar2,
p_employee_national_identifier in varchar2 default hr_api.g_varchar2,
p_fegli in varchar2 default hr_api.g_varchar2,
p_fegli_desc in varchar2 default hr_api.g_varchar2,
p_first_action_la_code1 in varchar2 default hr_api.g_varchar2,
p_first_action_la_code2 in varchar2 default hr_api.g_varchar2,
p_first_action_la_desc1 in varchar2 default hr_api.g_varchar2,
p_first_action_la_desc2 in varchar2 default hr_api.g_varchar2,
p_first_noa_cancel_or_correct in varchar2 default hr_api.g_varchar2,
p_first_noa_code in varchar2 default hr_api.g_varchar2,
p_first_noa_desc in varchar2 default hr_api.g_varchar2,
p_first_noa_id in number default hr_api.g_number,
p_first_noa_pa_request_id in number default hr_api.g_number,
p_flsa_category in varchar2 default hr_api.g_varchar2,
p_forwarding_address_line1 in varchar2 default hr_api.g_varchar2,
p_forwarding_address_line2 in varchar2 default hr_api.g_varchar2,
p_forwarding_address_line3 in varchar2 default hr_api.g_varchar2,
p_forwarding_country in varchar2 default hr_api.g_varchar2,
p_forwarding_country_short_nam in varchar2 default hr_api.g_varchar2,
p_forwarding_postal_code in varchar2 default hr_api.g_varchar2,
p_forwarding_region_2 in varchar2 default hr_api.g_varchar2,
p_forwarding_town_or_city in varchar2 default hr_api.g_varchar2,
p_from_adj_basic_pay in number default hr_api.g_number,
p_from_basic_pay in number default hr_api.g_number,
p_from_grade_or_level in varchar2 default hr_api.g_varchar2,
p_from_locality_adj in number default hr_api.g_number,
p_from_occ_code in varchar2 default hr_api.g_varchar2,
p_from_other_pay_amount in number default hr_api.g_number,
p_from_pay_basis in varchar2 default hr_api.g_varchar2,
p_from_pay_plan in varchar2 default hr_api.g_varchar2,
-- FWFA Changes Bug#4444609
p_input_pay_rate_determinant in varchar2 default hr_api.g_varchar2,
p_from_pay_table_identifier in number default hr_api.g_number,
-- FWFA Changes
p_from_position_id in number default hr_api.g_number,
p_from_position_org_line1 in varchar2 default hr_api.g_varchar2,
p_from_position_org_line2 in varchar2 default hr_api.g_varchar2,
p_from_position_org_line3 in varchar2 default hr_api.g_varchar2,
p_from_position_org_line4 in varchar2 default hr_api.g_varchar2,
p_from_position_org_line5 in varchar2 default hr_api.g_varchar2,
p_from_position_org_line6 in varchar2 default hr_api.g_varchar2,
p_from_position_number in varchar2 default hr_api.g_varchar2,
p_from_position_seq_no in number default hr_api.g_number,
p_from_position_title in varchar2 default hr_api.g_varchar2,
p_from_step_or_rate in varchar2 default hr_api.g_varchar2,
p_from_total_salary in number default hr_api.g_number,
p_functional_class in varchar2 default hr_api.g_varchar2,
p_notepad in varchar2 default hr_api.g_varchar2,
p_part_time_hours in number default hr_api.g_number,
p_pay_rate_determinant in varchar2 default hr_api.g_varchar2,
p_person_id in number default hr_api.g_number,
p_position_occupied in varchar2 default hr_api.g_varchar2,
p_proposed_effective_date in date default hr_api.g_date,
p_requested_by_person_id in number default hr_api.g_number,
p_requested_by_title in varchar2 default hr_api.g_varchar2,
p_requested_date in date default hr_api.g_date,
p_requesting_office_remarks_de in varchar2 default hr_api.g_varchar2,
p_requesting_office_remarks_fl in varchar2 default hr_api.g_varchar2,
p_request_number in varchar2 default hr_api.g_varchar2,
p_resign_and_retire_reason_des in varchar2 default hr_api.g_varchar2,
p_retirement_plan in varchar2 default hr_api.g_varchar2,
p_retirement_plan_desc in varchar2 default hr_api.g_varchar2,
p_second_action_la_code1 in varchar2 default hr_api.g_varchar2,
p_second_action_la_code2 in varchar2 default hr_api.g_varchar2,
p_second_action_la_desc1 in varchar2 default hr_api.g_varchar2,
p_second_action_la_desc2 in varchar2 default hr_api.g_varchar2,
p_second_noa_cancel_or_correct in varchar2 default hr_api.g_varchar2,
p_second_noa_code in varchar2 default hr_api.g_varchar2,
p_second_noa_desc in varchar2 default hr_api.g_varchar2,
p_second_noa_id in number default hr_api.g_number,
p_second_noa_pa_request_id in number default hr_api.g_number,
p_service_comp_date in date default hr_api.g_date,
p_supervisory_status in varchar2 default hr_api.g_varchar2,
p_tenure in varchar2 default hr_api.g_varchar2,
p_to_adj_basic_pay in number default hr_api.g_number,
p_to_basic_pay in number default hr_api.g_number,
p_to_grade_id in number default hr_api.g_number,
p_to_grade_or_level in varchar2 default hr_api.g_varchar2,
p_to_job_id in number default hr_api.g_number,
p_to_locality_adj in number default hr_api.g_number,
p_to_occ_code in varchar2 default hr_api.g_varchar2,
p_to_organization_id in number default hr_api.g_number,
p_to_other_pay_amount in number default hr_api.g_number,
p_to_au_overtime in number default hr_api.g_number,
p_to_auo_premium_pay_indicator in varchar2 default hr_api.g_varchar2,
p_to_availability_pay in number default hr_api.g_number,
p_to_ap_premium_pay_indicator in varchar2 default hr_api.g_varchar2,
p_to_retention_allowance in number default hr_api.g_number,
p_to_supervisory_differential in number default hr_api.g_number,
p_to_staffing_differential in number default hr_api.g_number,
p_to_pay_basis in varchar2 default hr_api.g_varchar2,
p_to_pay_plan in varchar2 default hr_api.g_varchar2,
-- FWFA Changes Bug#4444609
p_to_pay_table_identifier in number default hr_api.g_number,
-- FWFA Changes
p_to_position_id in number default hr_api.g_number,
p_to_position_org_line1 in varchar2 default hr_api.g_varchar2,
p_to_position_org_line2 in varchar2 default hr_api.g_varchar2,
p_to_position_org_line3 in varchar2 default hr_api.g_varchar2,
p_to_position_org_line4 in varchar2 default hr_api.g_varchar2,
p_to_position_org_line5 in varchar2 default hr_api.g_varchar2,
p_to_position_org_line6 in varchar2 default hr_api.g_varchar2,
p_to_position_number in varchar2 default hr_api.g_varchar2,
p_to_position_seq_no in number default hr_api.g_number,
p_to_position_title in varchar2 default hr_api.g_varchar2,
p_to_step_or_rate in varchar2 default hr_api.g_varchar2,
p_to_total_salary in number default hr_api.g_number,
p_veterans_preference in varchar2 default hr_api.g_varchar2,
p_veterans_pref_for_rif in varchar2 default hr_api.g_varchar2,
p_veterans_status in varchar2 default hr_api.g_varchar2,
p_work_schedule in varchar2 default hr_api.g_varchar2,
p_work_schedule_desc in varchar2 default hr_api.g_varchar2,
p_year_degree_attained in number default hr_api.g_number,
p_first_noa_information1 in varchar2 default hr_api.g_varchar2,
p_first_noa_information2 in varchar2 default hr_api.g_varchar2,
p_first_noa_information3 in varchar2 default hr_api.g_varchar2,
p_first_noa_information4 in varchar2 default hr_api.g_varchar2,
p_first_noa_information5 in varchar2 default hr_api.g_varchar2,
p_second_lac1_information1 in varchar2 default hr_api.g_varchar2,
p_second_lac1_information2 in varchar2 default hr_api.g_varchar2,
p_second_lac1_information3 in varchar2 default hr_api.g_varchar2,
p_second_lac1_information4 in varchar2 default hr_api.g_varchar2,
p_second_lac1_information5 in varchar2 default hr_api.g_varchar2,
p_second_lac2_information1 in varchar2 default hr_api.g_varchar2,
p_second_lac2_information2 in varchar2 default hr_api.g_varchar2,
p_second_lac2_information3 in varchar2 default hr_api.g_varchar2,
p_second_lac2_information4 in varchar2 default hr_api.g_varchar2,
p_second_lac2_information5 in varchar2 default hr_api.g_varchar2,
p_second_noa_information1 in varchar2 default hr_api.g_varchar2,
p_second_noa_information2 in varchar2 default hr_api.g_varchar2,
p_second_noa_information3 in varchar2 default hr_api.g_varchar2,
p_second_noa_information4 in varchar2 default hr_api.g_varchar2,
p_second_noa_information5 in varchar2 default hr_api.g_varchar2,
p_first_lac1_information1 in varchar2 default hr_api.g_varchar2,
p_first_lac1_information2 in varchar2 default hr_api.g_varchar2,
p_first_lac1_information3 in varchar2 default hr_api.g_varchar2,
p_first_lac1_information4 in varchar2 default hr_api.g_varchar2,
p_first_lac1_information5 in varchar2 default hr_api.g_varchar2,
p_first_lac2_information1 in varchar2 default hr_api.g_varchar2,
p_first_lac2_information2 in varchar2 default hr_api.g_varchar2,
p_first_lac2_information3 in varchar2 default hr_api.g_varchar2,
p_first_lac2_information4 in varchar2 default hr_api.g_varchar2,
p_first_lac2_information5 in varchar2 default hr_api.g_varchar2,
p_attribute_category in varchar2 default hr_api.g_varchar2,
p_attribute1 in varchar2 default hr_api.g_varchar2,
p_attribute2 in varchar2 default hr_api.g_varchar2,
p_attribute3 in varchar2 default hr_api.g_varchar2,
p_attribute4 in varchar2 default hr_api.g_varchar2,
p_attribute5 in varchar2 default hr_api.g_varchar2,
p_attribute6 in varchar2 default hr_api.g_varchar2,
p_attribute7 in varchar2 default hr_api.g_varchar2,
p_attribute8 in varchar2 default hr_api.g_varchar2,
p_attribute9 in varchar2 default hr_api.g_varchar2,
p_attribute10 in varchar2 default hr_api.g_varchar2,
p_attribute11 in varchar2 default hr_api.g_varchar2,
p_attribute12 in varchar2 default hr_api.g_varchar2,
p_attribute13 in varchar2 default hr_api.g_varchar2,
p_attribute14 in varchar2 default hr_api.g_varchar2,
p_attribute15 in varchar2 default hr_api.g_varchar2,
p_attribute16 in varchar2 default hr_api.g_varchar2,
p_attribute17 in varchar2 default hr_api.g_varchar2,
p_attribute18 in varchar2 default hr_api.g_varchar2,
p_attribute19 in varchar2 default hr_api.g_varchar2,
p_attribute20 in varchar2 default hr_api.g_varchar2,
p_print_sf50_flag in varchar2 default 'N',
p_printer_name in varchar2 default null,
p_print_back_page in varchar2 default 'Y',
p_u_attachment_modified_flag in varchar2 default hr_api.g_varchar2,
p_u_approved_flag in varchar2 default hr_api.g_varchar2,
p_u_user_name_acted_on in varchar2 default hr_api.g_varchar2,
p_u_action_taken in varchar2 default null,
p_u_approval_status in varchar2 default hr_api.g_varchar2,
p_i_user_name_routed_to in varchar2 default null,
p_i_groupbox_id in number default null,
p_i_routing_list_id in number default null,
p_i_routing_seq_number in number default null,
p_capped_other_pay in number default null,
p_to_retention_allow_percentag in number default hr_api.g_number,
p_to_supervisory_diff_percenta in number default hr_api.g_number,
p_to_staffing_diff_percentage in number default hr_api.g_number,
p_award_percentage in number default hr_api.g_number,
p_rpa_type in varchar2 default hr_api.g_varchar2,
p_mass_action_id in number default hr_api.g_number,
p_mass_action_eligible_flag in varchar2 default hr_api.g_varchar2,
p_mass_action_select_flag in varchar2 default hr_api.g_varchar2,
p_mass_action_comments in varchar2 default hr_api.g_varchar2,
-- Bug#4486823 RRR Changes
p_payment_option in varchar2 default null,
p_award_salary in number default hr_api.g_number,
-- Bug#4486823 RRR Changes
p_u_prh_object_version_number out nocopy number,
p_i_pa_routing_history_id out nocopy number,
p_i_prh_object_version_number out nocopy number
)
is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'update_sf52';
l_last_update_date ghr_pa_requests.last_update_date%type;
l_last_updated_by ghr_pa_requests.last_updated_by%type;
l_last_update_login ghr_pa_requests.last_update_login%type;
SELECT count(*) cnt
FROM ghr_pa_routing_history prh
WHERE prh.pa_request_id = p_pa_request_id;
SELECT prh.pa_routing_history_id,
prh.object_version_number
FROM ghr_pa_routing_history prh
WHERE prh.pa_request_id = p_pa_request_id
ORDER by prh.pa_routing_history_id desc;
SELECT par.routing_group_id
FROM ghr_pa_requests par
WHERE par.pa_request_id = p_pa_request_id;
select usr.employee_id,
per.first_name,
per.last_name,
per.middle_names
from fnd_user usr,
per_people_f per
where upper(p_u_user_name_acted_on) = upper(usr.user_name)
and per.person_id = usr.employee_id
and l_effective_date
between effective_start_date
and effective_end_date; */
SELECT usr.employee_id,
per.first_name,
per.last_name,
per.middle_names
FROM fnd_user usr,
per_people_f per
WHERE usr.user_name = UPPER(p_u_user_name_acted_on)
AND per.person_id = usr.employee_id
AND trunc(sysdate)
BETWEEN effective_start_date
AND effective_end_date;
SELECT prh.routing_seq_number
FROM ghr_pa_routing_history prh
WHERE prh.pa_request_id = p_pa_request_id
AND prh.routing_list_id = p_i_routing_list_id
ORDER BY prh.pa_routing_history_id desc;
SELECT rlm.seq_number,
rlm.groupbox_id,
rlm.user_name
FROM ghr_routing_list_members rlm
WHERE rlm.routing_list_id = p_i_routing_list_id
AND rlm.seq_number > l_cur_seq_numb
ORDER BY rlm.seq_number asc;
SELECT action_taken
FROM ghr_pa_routing_history prh
WHERE prh.pa_request_id = p_pa_request_id;
SELECT gbx.name
FROM ghr_groupboxes gbx
WHERE gbx.groupbox_id = l_next_groupbox_id;
SELECT par.noa_family_code,
par.last_update_date,
par.last_updated_by,
par.last_update_login,
par.created_by,
par.creation_date
FROM ghr_pa_requests par
WHERE pa_request_id = p_pa_request_id;
SELECT pa_request_extra_info_id,
object_version_number
FROM ghr_pa_request_extra_info rei
WHERE rei.pa_request_id = p_pa_request_id
AND rei.information_type = l_information_type;
SELECT par.object_version_number
FROM ghr_pa_requests par
WHERE par.pa_request_id = p_pa_request_id;
SELECT par.employee_assignment_id,par.effective_date
FROM ghr_pa_requests par
WHERE par.pa_request_id = p_pa_request_id;
select pa_request_id
from ghr_pa_requests
where pa_request_id in (select mass_action_id
from ghr_pa_requests
where pa_request_id = p_pa_request_id
and first_noa_code = '001'
and mass_action_id is not null
and rpa_type = 'DUAL')
and pa_notification_id is not null
and first_noa_code = '001';
select 1
from ghr_pa_requests
where pa_request_id = p_pa_request_id
and mass_action_id < pa_request_id
and rpa_type = 'DUAL'
and second_noa_code = (select second_noa_code
from ghr_pa_requests
where pa_request_id = (select min(pa_request_id)
from ghr_pa_requests
where pa_notification_id is not null
connect by pa_request_id = prior altered_pa_request_id
start with pa_request_id = p_pa_request_id));
select 1
from ghr_pa_requests
where pa_request_id = p_first_pa_req_id
and pa_notification_id is not null;
SELECT to_position_id FROM ghr_pa_requests
WHERE pa_request_id = p_pa_request_id;
select fams.noa_family_code
from ghr_noa_families noafam,
ghr_families fams
where noafam.nature_of_action_id = p_second_noa_id
and noafam.enabled_flag = 'Y'
and fams.noa_family_code = noafam.noa_family_code
and fams.enabled_flag = 'Y'
and fams.update_hr_flag = 'Y'
and l_effective_date between nvl(noafam.start_date_active,to_date('1951/01/01','RRRR/MM/DD'))
and nvl(noafam.end_date_active,to_date('4712/12/31','RRRR/MM/DD'));
savepoint update_sf52;
ghr_sf52_bk2.update_sf52_b (
p_noa_family_code => p_noa_family_code,
p_pa_request_id => p_pa_request_id,
p_routing_group_id => p_routing_group_id,
p_proposed_effective_asap_flag => p_proposed_effective_asap_flag,
p_academic_discipline => p_academic_discipline,
p_additional_info_person_id => p_additional_info_person_id,
p_additional_info_tel_number => p_additional_info_tel_number,
p_altered_pa_request_id => p_altered_pa_request_id,
p_annuitant_indicator => p_annuitant_indicator,
p_annuitant_indicator_desc => p_annuitant_indicator_desc,
p_appropriation_code1 => p_appropriation_code1,
p_appropriation_code2 => p_appropriation_code2,
p_authorized_by_person_id => p_authorized_by_person_id,
p_authorized_by_title => p_authorized_by_title,
p_award_amount => p_award_amount,
p_award_uom => p_award_uom,
p_bargaining_unit_status => p_bargaining_unit_status,
p_citizenship => p_citizenship,
p_concurrence_date => p_concurrence_date,
p_custom_pay_calc_flag => p_custom_pay_calc_flag,
p_duty_station_code => p_duty_station_code,
p_duty_station_desc => p_duty_station_desc,
p_duty_station_location_id => p_duty_station_location_id,
p_duty_station_id => p_duty_station_id,
p_education_level => p_education_level,
p_effective_date => p_effective_date,
p_employee_assignment_id => p_employee_assignment_id,
p_employee_date_of_birth => p_employee_date_of_birth,
p_employee_first_name => p_employee_first_name,
p_employee_last_name => p_employee_last_name,
p_employee_middle_names => p_employee_middle_names,
p_employee_national_identifier => p_employee_national_identifier,
p_fegli => p_fegli,
p_fegli_desc => p_fegli_desc,
p_first_action_la_code1 => p_first_action_la_code1,
p_first_action_la_code2 => p_first_action_la_code2,
p_first_action_la_desc1 => p_first_action_la_desc1,
p_first_action_la_desc2 => p_first_action_la_desc2,
p_first_noa_cancel_or_correct => p_first_noa_cancel_or_correct,
p_first_noa_id => p_first_noa_id,
p_first_noa_code => p_first_noa_code,
p_first_noa_desc => p_first_noa_desc,
p_first_noa_pa_request_id => p_first_noa_pa_request_id,
p_flsa_category => p_flsa_category,
p_forwarding_address_line1 => p_forwarding_address_line1,
p_forwarding_address_line2 => p_forwarding_address_line2,
p_forwarding_address_line3 => p_forwarding_address_line3,
p_forwarding_country => p_forwarding_country,
p_forwarding_country_short_nam => p_forwarding_country_short_nam,
p_forwarding_postal_code => p_forwarding_postal_code,
p_forwarding_region_2 => p_forwarding_region_2,
p_forwarding_town_or_city => p_forwarding_town_or_city ,
p_from_adj_basic_pay => p_from_adj_basic_pay,
p_from_basic_pay => p_from_basic_pay,
p_from_grade_or_level => p_from_grade_or_level,
p_from_locality_adj => p_from_locality_adj,
p_from_occ_code => p_from_occ_code,
p_from_other_pay_amount => p_from_other_pay_amount,
p_from_pay_basis => p_from_pay_basis,
p_from_pay_plan => p_from_pay_plan,
--FWFA Changes Bug#4444609
-- p_input_pay_rate_determinant => p_input_pay_rate_determinant,
-- p_from_pay_table_identifier => p_from_pay_table_identifier,
-- FWFA Changes
p_from_position_id => p_from_position_id,
p_from_position_org_line1 => p_from_position_org_line1,
p_from_position_org_line2 => p_from_position_org_line2,
p_from_position_org_line3 => p_from_position_org_line3,
p_from_position_org_line4 => p_from_position_org_line4,
p_from_position_org_line5 => p_from_position_org_line5,
p_from_position_org_line6 => p_from_position_org_line6,
p_from_position_number => p_from_position_number,
p_from_position_seq_no => p_from_position_seq_no,
p_from_position_title => p_from_position_title,
p_from_step_or_rate => p_from_step_or_rate,
p_from_total_salary => p_from_total_salary,
p_functional_class => p_functional_class,
p_notepad => p_notepad,
p_part_time_hours => p_part_time_hours,
p_pay_rate_determinant => p_pay_rate_determinant,
p_person_id => p_person_id,
p_position_occupied => p_position_occupied,
p_proposed_effective_date => p_proposed_effective_date,
p_requested_by_person_id => p_requested_by_person_id,
p_requested_by_title => p_requested_by_title,
p_requested_date => p_requested_date,
p_requesting_office_remarks_de => p_requesting_office_remarks_de,
p_requesting_office_remarks_fl => p_requesting_office_remarks_fl,
p_request_number => p_request_number,
p_resign_and_retire_reason_des => p_resign_and_retire_reason_des,
p_retirement_plan => p_retirement_plan,
p_retirement_plan_desc => p_retirement_plan_desc,
p_second_action_la_code1 => p_second_action_la_code1,
p_second_action_la_code2 => p_second_action_la_code2,
p_second_action_la_desc1 => p_second_action_la_desc1,
p_second_action_la_desc2 => p_second_action_la_desc2,
p_second_noa_cancel_or_correct => p_second_noa_cancel_or_correct,
p_second_noa_code => p_second_noa_code,
p_second_noa_desc => p_second_noa_desc,
p_second_noa_id => p_second_noa_id,
p_second_noa_pa_request_id => p_second_noa_pa_request_id,
p_service_comp_date => p_service_comp_date,
p_supervisory_status => p_supervisory_status,
p_tenure => p_tenure,
p_to_adj_basic_pay => p_to_adj_basic_pay,
p_to_basic_pay => p_to_basic_pay,
p_to_grade_id => p_to_grade_id,
p_to_grade_or_level => p_to_grade_or_level,
p_to_job_id => p_to_job_id,
p_to_locality_adj => p_to_locality_adj,
p_to_occ_code => p_to_occ_code,
p_to_organization_id => p_to_organization_id,
p_to_other_pay_amount => p_to_other_pay_amount,
p_to_au_overtime => p_to_au_overtime,
p_to_auo_premium_pay_indicator => p_to_auo_premium_pay_indicator,
p_to_availability_pay => p_to_availability_pay,
p_to_ap_premium_pay_indicator => p_to_ap_premium_pay_indicator,
p_to_retention_allowance => p_to_retention_allowance,
p_to_supervisory_differential => p_to_supervisory_differential,
p_to_staffing_differential => p_to_staffing_differential,
p_to_pay_basis => p_to_pay_basis,
p_to_pay_plan => p_to_pay_plan,
-- FWFA Changes Bug#4444609
-- p_to_pay_table_identifier => p_to_pay_table_identifier,
-- FWFA Changes
p_to_position_id => p_to_position_id,
p_to_position_org_line1 => p_to_position_org_line1,
p_to_position_org_line2 => p_to_position_org_line2,
p_to_position_org_line3 => p_to_position_org_line3,
p_to_position_org_line4 => p_to_position_org_line4,
p_to_position_org_line5 => p_to_position_org_line5,
p_to_position_org_line6 => p_to_position_org_line6,
p_to_position_number => p_to_position_number,
p_to_position_seq_no => p_to_position_seq_no,
p_to_position_title => p_to_position_title,
p_to_step_or_rate => p_to_step_or_rate,
p_to_total_salary => p_to_total_salary,
p_veterans_pref_for_rif => p_veterans_pref_for_rif,
p_veterans_preference => p_veterans_preference,
p_veterans_status => p_veterans_status,
p_work_schedule => p_work_schedule,
p_work_schedule_desc => p_work_schedule_desc,
p_year_degree_attained => p_year_degree_attained,
p_first_noa_information1 => p_first_noa_information1,
p_first_noa_information2 => p_first_noa_information2,
p_first_noa_information3 => p_first_noa_information3,
p_first_noa_information4 => p_first_noa_information4,
p_first_noa_information5 => p_first_noa_information5,
p_second_lac1_information1 => p_second_lac1_information1,
p_second_lac1_information2 => p_second_lac1_information2,
p_second_lac1_information3 => p_second_lac1_information3,
p_second_lac1_information4 => p_second_lac1_information4,
p_second_lac1_information5 => p_second_lac1_information5,
p_second_lac2_information1 => p_second_lac2_information1,
p_second_lac2_information2 => p_second_lac2_information2,
p_second_lac2_information3 => p_second_lac2_information3,
p_second_lac2_information4 => p_second_lac2_information4,
p_second_lac2_information5 => p_second_lac2_information5,
p_second_noa_information1 => p_second_noa_information1,
p_second_noa_information2 => p_second_noa_information2,
p_second_noa_information3 => p_second_noa_information3,
p_second_noa_information4 => p_second_noa_information4,
p_second_noa_information5 => p_second_noa_information5,
p_first_lac1_information1 => p_first_lac1_information1,
p_first_lac1_information2 => p_first_lac1_information2,
p_first_lac1_information3 => p_first_lac1_information3,
p_first_lac1_information4 => p_first_lac1_information4,
p_first_lac1_information5 => p_first_lac1_information5,
p_first_lac2_information1 => p_first_lac2_information1,
p_first_lac2_information2 => p_first_lac2_information2,
p_first_lac2_information3 => p_first_lac2_information3,
p_first_lac2_information4 => p_first_lac2_information4,
p_first_lac2_information5 => p_first_lac2_information5,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_print_sf50_flag => p_print_sf50_flag,
p_printer_name => p_printer_name,
p_u_attachment_modified_flag => p_u_attachment_modified_flag,
p_u_approved_flag => p_u_approved_flag,
p_u_user_name_acted_on => p_u_user_name_acted_on,
p_u_action_taken => p_u_action_taken,
p_u_approval_status => p_u_approval_status,
p_i_user_name_routed_to => p_i_user_name_routed_to,
p_i_groupbox_id => p_i_groupbox_id,
p_i_routing_list_id => p_i_routing_list_id,
p_i_routing_seq_number => p_i_routing_seq_number,
p_capped_other_pay => p_capped_other_pay,
p_par_object_version_number => p_par_object_version_number,
p_to_retention_allow_percentag => p_to_retention_allow_percentag,
p_to_supervisory_diff_percenta => p_to_supervisory_diff_percenta,
p_to_staffing_diff_percentage => p_to_staffing_diff_percentage,
p_award_percentage => p_award_percentage,
p_rpa_type => p_rpa_type,
p_mass_action_id => p_mass_action_id,
p_mass_action_eligible_flag => p_mass_action_eligible_flag,
p_mass_action_select_flag => p_mass_action_select_flag,
p_mass_action_comments => p_mass_action_comments
);
(p_module_name => 'update_sf52',
p_hook_type => 'BP'
);
If nvl(p_u_action_taken,hr_api.g_varchar2) in ('UPDATE_HR','FUTURE_ACTION') then
If p_approval_date is not null and p_approval_date <> hr_api.g_date then
l_sf50_approval_date := p_approval_date;
p_mass_action_select_flag => p_mass_action_select_flag,
p_mass_action_comments => p_mass_action_comments,
p_payment_option => p_payment_option,
p_award_salary => p_award_salary
);
delete from ghr_pa_remarks pre
where pre.pa_request_id = p_pa_request_id
and pre.remark_id in
(select remark_id
from ghr_noac_remarks
where nature_of_action_id = ghr_par_shd.g_old_rec.first_noa_id);
insert into ghr_pa_remarks
(pa_remark_id
,pa_request_id
,remark_id
,description
,object_version_number
)
select ghr_pa_remarks_s.nextval
,p_pa_request_id
,rem.remark_id
,rem.description
,1
from ghr_remarks rem
,ghr_noac_remarks nre
where nre.nature_of_action_id = p_first_noa_id
and nre.required_flag = 'Y'
and l_effective_date
between nre.date_from
and nvl(nre.date_to,l_effective_date)
and nre.remark_id = rem.remark_id;
delete from ghr_pa_remarks pre
where pre.pa_request_id = p_pa_request_id
and pre.remark_id in
(select remark_id
from ghr_noac_remarks
where nature_of_action_id = ghr_par_shd.g_old_rec.second_noa_id);
insert into ghr_pa_remarks
(pa_remark_id
,pa_request_id
,remark_id
,description
,object_version_number
)
select ghr_pa_remarks_s.nextval
,p_pa_request_id
,rem.remark_id
,rem.description
,1
from ghr_remarks rem
,ghr_noac_remarks nre
where nre.nature_of_action_id = p_second_noa_id
and nre.required_flag = 'Y'
and l_effective_date
between nre.date_from
and nvl(nre.date_to,l_effective_date)
and nre.remark_id = rem.remark_id;
hr_utility.set_location('update/ delete extra info',1);
'NO_ACTION','REVIEWED','CANCELED','UPDATE_HR','UPDATE_HR_COMPLETE','NONE')
then
hr_utility.set_message(8301,'GHR_38110_INVALID_ACTION_TAKEN');
if l_action_taken not in ('CANCELED','UPDATE_HR','UPDATE_HR_COMPLETE','NOT_ROUTED','NONE','ENDED','END_ROUTING') then
if p_i_user_name_routed_to is null and
p_i_groupbox_id is null and
p_i_routing_list_id is null then
hr_utility.set_message(8301,'GHR_38115_ROUT_INFO_REQD');
if nvl(l_action_taken,hr_api.g_varchar2) not in ('CANCELED','UPDATE_HR_COMPLETE','NONE','ENDED')then
hr_utility.set_location('check ' ||l_action_taken,1);
hr_utility.set_location('in update sf52 api , user acted on is ' || p_u_user_name_acted_on,1);
If l_action_taken = 'UPDATE_HR' and
trunc(p_effective_date) > sysdate then
l_action_taken := 'FUTURE_ACTION';
if nvl(l_action_taken,hr_api.g_varchar2) not in ('NOT_ROUTED','UPDATE_HR','FUTURE_ACTION','NONE','END_ROUTING') then
ghr_prh_ins.ins
(p_pa_routing_history_id => l_i_pa_routing_history_id,
p_pa_request_id => p_pa_request_id,
p_attachment_modified_flag => 'N',
p_initiator_flag => 'N',
p_approver_flag => 'N',
p_reviewer_flag => 'N',
p_requester_flag => 'N',
p_authorizer_flag => 'N',
p_personnelist_flag => nvl(l_personnelist_flag,'N'),
p_approved_flag => 'N',
p_user_name => l_next_user_name,
p_groupbox_id => l_next_groupbox_id,
p_routing_list_id => p_i_routing_list_id,
p_routing_seq_number => l_next_seq_numb,
p_noa_family_code => p_noa_family_code,
p_nature_of_action_id => p_first_noa_id,
p_second_nature_of_action_id => p_second_noa_id,
p_object_version_number => l_i_prh_object_version_number
-- p_validate => p_validate
);
if l_action_taken in ('UPDATE_HR','FUTURE_ACTION','END_ROUTING') then
hr_utility.set_location(l_proc || p_award_amount,1);
If p_mass_action_select_flag = hr_api.g_varchar2 then
l_rec.mass_action_select_flag := ghr_par_shd.g_old_rec.mass_action_select_flag;
ghr_sf52_api.update_detail_remarks( p_effective_date => l_effective_date,
p_pa_request_id => p_pa_request_id);
ghr_sf52_post_update.get_notification_details
(p_pa_request_id => p_pa_request_id,
p_effective_date => p_effective_date,
-- p_object_version_number => p_imm_pa_request_rec.object_version_number,
p_from_position_id => l_rec.from_position_id,
p_to_position_id => NVL(l_rec.to_position_id,l_to_position_id), --Bug# 8510411
p_agency_code => l_rec.agency_code,
p_from_agency_code => l_rec.from_agency_code,
p_from_agency_desc => l_rec.from_agency_desc,
p_from_office_symbol => l_rec.from_office_symbol,
p_personnel_office_id => l_rec.personnel_office_id,
p_employee_dept_or_agency => l_rec.employee_dept_or_agency,
p_to_office_symbol => l_rec.to_office_symbol
);
ghr_agency_update.ghr_agency_upd(
p_pa_request_rec => l_rec,
p_asg_sf52 => l_asg_sf52,
p_asg_non_sf52 => l_asg_non_sf52,
p_asg_nte_dates => l_asg_nte_dates,
p_per_sf52 => l_per_sf52,
p_per_group1 => l_per_group1,
p_per_group2 => l_per_group2,
p_per_scd_info => l_per_scd_info,
p_per_retained_grade => l_per_retained_grade,
p_per_probations => l_per_probations,
p_per_sep_retire => l_per_sep_retire,
p_per_security => l_per_security,
p_per_conversions => l_per_conversions,
p_per_uniformed_services => l_per_uniformed_services,
p_pos_oblig => l_pos_oblig,
p_pos_grp2 => l_pos_grp2,
p_pos_grp1 => l_pos_grp1,
p_pos_valid_grade => l_pos_valid_grade,
p_pos_car_prog => l_pos_car_prog,
p_loc_info => l_loc_info,
p_wgi => l_wgi,
p_recruitment_bonus => l_recruitment_bonus,
p_relocation_bonus => l_relocation_bonus,
p_sf52_from_data => l_sf52_from_data,
p_personal_info => l_personal_info,
p_gov_awards_type => l_gov_awards_type,
p_perf_appraisal_type => l_perf_appraisal_type,
p_payroll_type => l_payroll_type,
p_conduct_perf_type => l_conduct_perf_type,
p_agency_sf52 => l_agency_sf52,
p_agency_code => l_agency_code,
p_entitlement => l_imm_entitlement,
p_foreign_lang_prof_pay => l_imm_foreign_lang_prof_pay,
p_edp_pay => l_imm_edp_pay,
p_hazard_pay => l_imm_hazard_pay,
p_health_benefits => l_imm_health_benefits,
p_danger_pay => l_imm_danger_pay,
p_imminent_danger_pay => l_imm_imminent_danger_pay,
p_living_quarters_allow => l_imm_living_quarters_allow,
p_post_diff_amt => l_imm_post_diff_amt,
p_post_diff_percent => l_imm_post_diff_percent,
p_sep_maintenance_allow => l_imm_sep_maintenance_allow,
p_supplemental_post_allow => l_imm_supplemental_post_allow,
p_temp_lodge_allow => l_imm_temp_lodge_allow,
p_premium_pay => l_imm_premium_pay,
p_retirement_annuity => l_imm_retirement_annuity,
p_severance_pay => l_imm_severance_pay,
p_thrift_saving_plan => l_imm_thrift_saving_plan,
p_retention_allow_review => l_imm_retention_allow_review,
p_health_ben_pre_tax => l_imm_health_ben_pre_tax,
p_per_benefit_info => l_imm_per_benefit_info,
p_imm_retirement_info => l_imm_retirement_info, --Bug# 7131104
--Begin Bug# 10181661
p_asg_detail_info => l_imm_asg_detail_info,
p_ipa_detail_ben_cont_info => l_imm_ipa_benefits_cont
--End Bug# 10181661
);
if (l_action_taken not in ('UPDATE_HR','END_ROUTING')) then
ghr_api.call_workflow
(p_pa_request_id => p_pa_request_id,
p_action_taken => l_action_taken
);
If l_action_taken <> 'UPDATE_HR' then
hr_utility.set_message(8301,'GHR_38399_52_NOT_PROCESSED');
-- Modified as First NPA need to be printed only if First NPA generation is selected
for chk_first_corr_npa in c_first_corr_npa(p_first_pa_req_id => p_mass_action_id)
loop
submit_request_to_print_50
(p_printer_name => p_printer_name,
p_pa_request_id => p_mass_action_id,
p_effective_date => p_effective_date,
p_user_name => p_u_user_name_acted_on,
p_back_page => p_print_back_page
);
ghr_sf52_bk2.update_sf52_a (
p_noa_family_code => p_noa_family_code,
p_pa_request_id => p_pa_request_id,
p_routing_group_id => p_routing_group_id,
p_proposed_effective_asap_flag => p_proposed_effective_asap_flag,
p_academic_discipline => p_academic_discipline,
p_additional_info_person_id => p_additional_info_person_id,
p_additional_info_tel_number => p_additional_info_tel_number,
p_altered_pa_request_id => p_altered_pa_request_id,
p_annuitant_indicator => p_annuitant_indicator,
p_annuitant_indicator_desc => p_annuitant_indicator_desc,
p_appropriation_code1 => p_appropriation_code1,
p_appropriation_code2 => p_appropriation_code2,
p_authorized_by_person_id => p_authorized_by_person_id,
p_authorized_by_title => p_authorized_by_title,
p_award_amount => p_award_amount,
p_award_uom => p_award_uom,
p_bargaining_unit_status => p_bargaining_unit_status,
p_citizenship => p_citizenship,
p_concurrence_date => p_concurrence_date,
p_custom_pay_calc_flag => p_custom_pay_calc_flag,
p_duty_station_code => p_duty_station_code,
p_duty_station_desc => p_duty_station_desc,
p_duty_station_location_id => p_duty_station_location_id,
p_duty_station_id => p_duty_station_id,
p_education_level => p_education_level,
p_effective_date => p_effective_date,
p_employee_assignment_id => p_employee_assignment_id,
p_employee_date_of_birth => p_employee_date_of_birth,
p_employee_first_name => p_employee_first_name,
p_employee_last_name => p_employee_last_name,
p_employee_middle_names => p_employee_middle_names,
p_employee_national_identifier => p_employee_national_identifier,
p_fegli => p_fegli,
p_fegli_desc => p_fegli_desc,
p_first_action_la_code1 => p_first_action_la_code1,
p_first_action_la_code2 => p_first_action_la_code2,
p_first_action_la_desc1 => p_first_action_la_desc1,
p_first_action_la_desc2 => p_first_action_la_desc2,
p_first_noa_cancel_or_correct => p_first_noa_cancel_or_correct,
p_first_noa_id => p_first_noa_id,
p_first_noa_code => p_first_noa_code,
p_first_noa_desc => p_first_noa_desc,
p_first_noa_pa_request_id => p_first_noa_pa_request_id,
p_flsa_category => p_flsa_category,
p_forwarding_address_line1 => p_forwarding_address_line1,
p_forwarding_address_line2 => p_forwarding_address_line2,
p_forwarding_address_line3 => p_forwarding_address_line3,
p_forwarding_country => p_forwarding_country,
p_forwarding_country_short_nam => p_forwarding_country_short_nam,
p_forwarding_postal_code => p_forwarding_postal_code,
p_forwarding_region_2 => p_forwarding_region_2,
p_forwarding_town_or_city => p_forwarding_town_or_city ,
p_from_adj_basic_pay => p_from_adj_basic_pay,
p_from_basic_pay => p_from_basic_pay,
p_from_grade_or_level => p_from_grade_or_level,
p_from_locality_adj => p_from_locality_adj,
p_from_occ_code => p_from_occ_code,
p_from_other_pay_amount => p_from_other_pay_amount,
p_from_pay_basis => p_from_pay_basis,
p_from_pay_plan => p_from_pay_plan,
-- FWFA Changes Bug#4444609
-- p_input_pay_rate_determinant => p_input_pay_rate_determinant,
-- p_from_pay_table_identifier => p_from_pay_table_identifier,
-- FWFA Changes
p_from_position_id => p_from_position_id,
p_from_position_org_line1 => p_from_position_org_line1,
p_from_position_org_line2 => p_from_position_org_line2,
p_from_position_org_line3 => p_from_position_org_line3,
p_from_position_org_line4 => p_from_position_org_line4,
p_from_position_org_line5 => p_from_position_org_line5,
p_from_position_org_line6 => p_from_position_org_line6,
p_from_position_number => p_from_position_number,
p_from_position_seq_no => p_from_position_seq_no,
p_from_position_title => p_from_position_title,
p_from_step_or_rate => p_from_step_or_rate,
p_from_total_salary => p_from_total_salary,
p_functional_class => p_functional_class,
p_notepad => p_notepad,
p_part_time_hours => p_part_time_hours,
p_pay_rate_determinant => p_pay_rate_determinant,
p_person_id => p_person_id,
p_position_occupied => p_position_occupied,
p_proposed_effective_date => p_proposed_effective_date,
p_requested_by_person_id => p_requested_by_person_id,
p_requested_by_title => p_requested_by_title,
p_requested_date => p_requested_date,
p_requesting_office_remarks_de => p_requesting_office_remarks_de,
p_requesting_office_remarks_fl => p_requesting_office_remarks_fl,
p_request_number => p_request_number,
p_resign_and_retire_reason_des => p_resign_and_retire_reason_des,
p_retirement_plan => p_retirement_plan,
p_retirement_plan_desc => p_retirement_plan_desc,
p_second_action_la_code1 => p_second_action_la_code1,
p_second_action_la_code2 => p_second_action_la_code2,
p_second_action_la_desc1 => p_second_action_la_desc1,
p_second_action_la_desc2 => p_second_action_la_desc2,
p_second_noa_cancel_or_correct => p_second_noa_cancel_or_correct,
p_second_noa_code => p_second_noa_code,
p_second_noa_desc => p_second_noa_desc,
p_second_noa_id => p_second_noa_id,
p_second_noa_pa_request_id => p_second_noa_pa_request_id,
p_service_comp_date => p_service_comp_date,
p_supervisory_status => p_supervisory_status,
p_tenure => p_tenure,
p_to_adj_basic_pay => p_to_adj_basic_pay,
p_to_basic_pay => p_to_basic_pay,
p_to_grade_id => p_to_grade_id,
p_to_grade_or_level => p_to_grade_or_level,
p_to_job_id => p_to_job_id,
p_to_locality_adj => p_to_locality_adj,
p_to_occ_code => p_to_occ_code,
p_to_organization_id => p_to_organization_id,
p_to_other_pay_amount => p_to_other_pay_amount,
p_to_au_overtime => p_to_au_overtime,
p_to_auo_premium_pay_indicator => p_to_auo_premium_pay_indicator,
p_to_availability_pay => p_to_availability_pay,
p_to_ap_premium_pay_indicator => p_to_ap_premium_pay_indicator,
p_to_retention_allowance => p_to_retention_allowance,
p_to_supervisory_differential => p_to_supervisory_differential,
p_to_staffing_differential => p_to_staffing_differential,
p_to_pay_basis => p_to_pay_basis,
p_to_pay_plan => p_to_pay_plan,
-- FWFA Changes Bug#4444609
-- p_to_pay_table_identifier => p_to_pay_table_identifier,
-- FWFA Changes
p_to_position_id => p_to_position_id,
p_to_position_org_line1 => p_to_position_org_line1,
p_to_position_org_line2 => p_to_position_org_line2,
p_to_position_org_line3 => p_to_position_org_line3,
p_to_position_org_line4 => p_to_position_org_line4,
p_to_position_org_line5 => p_to_position_org_line5,
p_to_position_org_line6 => p_to_position_org_line6,
p_to_position_number => p_to_position_number,
p_to_position_seq_no => p_to_position_seq_no,
p_to_position_title => p_to_position_title,
p_to_step_or_rate => p_to_step_or_rate,
p_to_total_salary => p_to_total_salary,
p_veterans_pref_for_rif => p_veterans_pref_for_rif,
p_veterans_preference => p_veterans_preference,
p_veterans_status => p_veterans_status,
p_work_schedule => p_work_schedule,
p_work_schedule_desc => p_work_schedule_desc,
p_year_degree_attained => p_year_degree_attained,
p_first_noa_information1 => p_first_noa_information1,
p_first_noa_information2 => p_first_noa_information2,
p_first_noa_information3 => p_first_noa_information3,
p_first_noa_information4 => p_first_noa_information4,
p_first_noa_information5 => p_first_noa_information5,
p_second_lac1_information1 => p_second_lac1_information1,
p_second_lac1_information2 => p_second_lac1_information2,
p_second_lac1_information3 => p_second_lac1_information3,
p_second_lac1_information4 => p_second_lac1_information4,
p_second_lac1_information5 => p_second_lac1_information5,
p_second_lac2_information1 => p_second_lac2_information1,
p_second_lac2_information2 => p_second_lac2_information2,
p_second_lac2_information3 => p_second_lac2_information3,
p_second_lac2_information4 => p_second_lac2_information4,
p_second_lac2_information5 => p_second_lac2_information5,
p_second_noa_information1 => p_second_noa_information1,
p_second_noa_information2 => p_second_noa_information2,
p_second_noa_information3 => p_second_noa_information3,
p_second_noa_information4 => p_second_noa_information4,
p_second_noa_information5 => p_second_noa_information5,
p_first_lac1_information1 => p_first_lac1_information1,
p_first_lac1_information2 => p_first_lac1_information2,
p_first_lac1_information3 => p_first_lac1_information3,
p_first_lac1_information4 => p_first_lac1_information4,
p_first_lac1_information5 => p_first_lac1_information5,
p_first_lac2_information1 => p_first_lac2_information1,
p_first_lac2_information2 => p_first_lac2_information2,
p_first_lac2_information3 => p_first_lac2_information3,
p_first_lac2_information4 => p_first_lac2_information4,
p_first_lac2_information5 => p_first_lac2_information5,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_print_sf50_flag => p_print_sf50_flag,
p_printer_name => p_printer_name,
p_u_attachment_modified_flag => p_u_attachment_modified_flag,
p_u_approved_flag => p_u_approved_flag,
p_u_user_name_acted_on => p_u_user_name_acted_on,
p_u_action_taken => p_u_action_taken,
p_u_approval_status => p_u_approval_status,
p_i_user_name_routed_to => p_i_user_name_routed_to,
p_i_groupbox_id => p_i_groupbox_id,
p_i_routing_list_id => p_i_routing_list_id,
p_i_routing_seq_number => p_i_routing_seq_number,
p_capped_other_pay => p_capped_other_pay,
p_i_pa_routing_history_id => l_i_pa_routing_history_id,
p_i_prh_object_version_number => l_i_prh_object_version_number,
p_par_object_version_number => l_par_object_version_number,
p_to_retention_allow_percentag => p_to_retention_allow_percentag,
p_to_supervisory_diff_percenta => p_to_supervisory_diff_percenta,
p_to_staffing_diff_percentage => p_to_staffing_diff_percentage,
p_award_percentage => p_award_percentage,
p_rpa_type => p_rpa_type,
p_mass_action_id => p_mass_action_id,
p_mass_action_eligible_flag => p_mass_action_eligible_flag,
p_mass_action_select_flag => p_mass_action_select_flag,
p_mass_action_comments => p_mass_action_comments
);
(p_module_name => 'update_sf52',
p_hook_type => 'AP'
);
ROLLBACK TO update_sf52;
Rollback to update_sf52;
end update_sf52;
select count(*) cnt
from ghr_pa_routing_history prh
where prh.pa_request_id = p_pa_request_id;
select prh.pa_routing_history_id,
prh.pa_request_id,
-- ATTACHMENT_MODIFIED_FLAG
prh.initiator_flag,
prh.requester_flag,
prh.authorizer_flag,
prh.personnelist_flag,
prh.approver_flag,
prh.reviewer_flag,
-- prh.approved_flag,
prh.user_name,
prh.user_name_employee_id,
prh.user_name_emp_first_name,
prh.user_name_emp_last_name,
prh.user_name_emp_middle_names,
prh.notepad,
prh.action_taken,
prh.groupbox_id,
prh.routing_list_id,
prh.routing_seq_number,
prh.nature_of_action_id,
prh.noa_family_code,
prh.second_nature_of_action_id,
prh.object_version_number
-- prh.approval_status
from ghr_pa_routing_history prh
where prh.pa_request_id = p_pa_request_id
Order by prh.pa_routing_history_id desc;
select par.effective_date,
par.routing_group_id
from ghr_pa_requests par
where par.pa_request_id = p_pa_request_id;
select usr.employee_id,
per.first_name,
per.last_name,
per.middle_names
from fnd_user usr,
per_people_f per
where upper(l_user_name) = upper(usr.user_name)
and per.person_id = usr.employee_id
and l_effective_date
between effective_start_date
and effective_end_date; */
SELECT usr.employee_id,
per.first_name,
per.last_name,
per.middle_names
FROM fnd_user usr,
per_people_f per
WHERE usr.user_name = UPPER(l_user_name)
AND per.person_id = usr.employee_id
AND trunc(sysdate)
BETWEEN effective_start_date
AND effective_end_date;
if nvl(p_action_taken,hr_api.g_varchar2) in ('CANCELED','UPDATE_HR_COMPLETE','ENDED') then
for request_details in c_request_details loop
l_effective_date := trunc(request_details.effective_date);
If p_action_taken IN ('UPDATE_HR_COMPLETE','ENDED') then
for cur_routing_history in C_routing_history_id loop
l_pa_routing_history_id := cur_routing_history.pa_routing_history_id;
select rowid row_id,
first_noa_code,
second_noa_code,
object_version_number
from ghr_pa_requests
where pa_request_id = cp_pa_request_id
for update;
select user_id
from fnd_user
where user_name = p_user_name;
If nvl(p_action_taken,hr_api.g_varchar2) = 'CANCELED' or nvl(p_action_taken,hr_api.g_varchar2) = 'UPDATE_HR_COMPLETE'
or nvl(p_action_taken,hr_api.g_varchar2) = 'UPDATE_HR' then
p_status := p_action_taken;
If nvl(p_action_taken,hr_api.g_varchar2) = 'UPDATE_HR' and trunc(nvl(p_effective_date,hr_api.g_date)) > sysdate then
p_status := 'FUTURE_ACTION';
select 1
from ghr_event_history
where table_name = 'GHR_PA_REQUESTS'
and record_id = p_pa_request_id
and start_date is not null
and end_date is null;
( nvl(p_action_taken,hr_api.g_varchar2) = 'UPDATE_HR' or
nvl(p_action_taken,hr_api.g_varchar2) = 'FUTURE_ACTION'
--**dk
or nvl(p_action_taken,hr_api.g_varchar2) = 'END_ROUTING'
) then
-- BUG # 3420126 As per requirements in the bug need not be fired during routing
/*or
( p_user_name_routed_to is not null and
nvl(p_user_name_acted_on,hr_api.g_varchar2) <> p_user_name_routed_to
) or
(p_groupbox_routed_to is not null )*/
hr_utility.set_location(l_proc,10);
Procedure update_detail_remarks
(p_effective_date in date,
p_pa_request_id in number
)
is
l_proc varchar2(72) := g_package || 'update_detail_remarks';
SELECT rei_information13 Organization,
rei_information5 position_id,
rei_information6 grade_id,
rei_information7 job_id,
rei_information12 location_id
FROM ghr_pa_request_extra_info
WHERE rei_information_category='GHR_US_PAR_DET_INFO'
AND pa_request_id = p_pa_request_id;
SELECT gdf.segment1 pay_plan
,gdf.segment2 grade_or_level
FROM per_grade_definitions gdf
,per_grades grd
WHERE grd.grade_id = l_grade_id
AND grd.grade_definition_id = gdf.grade_definition_id
AND grd.business_group_id = FND_PROFILE.value('PER_BUSINESS_GROUP_ID');
select pos.name pos_name
from hr_all_positions_f pos
where pos.position_id = l_pos_id
and p_effective_date between pos.effective_start_date and pos.effective_end_date;
select description,pa_remark_id,
object_version_number from ghr_pa_remarks
where remark_id =l_remark_id
and pa_request_id = p_pa_request_id;
delete from ghr_pa_remarks
where pa_remark_id = l_pa_remark_id;
ghr_pa_remarks_api.update_pa_remarks
(p_pa_remark_id => l_pa_remark_id
,p_object_version_number => l_rem_ovn
,p_remark_code_information1 => l_detail_org--Bug# 12683854
,p_remark_code_information2 => NULL
,p_remark_code_information3 => NULL
,p_remark_code_information4 => NULL
,p_remark_code_information5 => NULL
,p_description => l_remark_description
);
delete from ghr_pa_remarks
where pa_remark_id = l_pa_remark_id;
ghr_pa_remarks_api.update_pa_remarks
(p_pa_remark_id => l_pa_remark_id
,p_object_version_number => l_rem_ovn
,p_remark_code_information1 => l_detail_position_name --Bug# 12683854
,p_remark_code_information2 => NULL
,p_remark_code_information3 => NULL
,p_remark_code_information4 => NULL
,p_remark_code_information5 => NULL
,p_description => l_remark_description
);
delete from ghr_pa_remarks
where pa_remark_id = l_pa_remark_id;
ghr_pa_remarks_api.update_pa_remarks
(p_pa_remark_id => l_pa_remark_id
,p_object_version_number => l_rem_ovn
,p_remark_code_information1 => l_detail_pay_plan--Bug# 12683854
,p_remark_code_information2 => NULL
,p_remark_code_information3 => NULL
,p_remark_code_information4 => NULL
,p_remark_code_information5 => NULL
,p_description => l_remark_description
);
delete from ghr_pa_remarks
where pa_remark_id = l_pa_remark_id;
ghr_pa_remarks_api.update_pa_remarks
(p_pa_remark_id => l_pa_remark_id
,p_object_version_number => l_rem_ovn
,p_remark_code_information1 => l_detail_grade--Bug# 12683854
,p_remark_code_information2 => NULL
,p_remark_code_information3 => NULL
,p_remark_code_information4 => NULL
,p_remark_code_information5 => NULL
,p_description => l_remark_description
);
delete from ghr_pa_remarks
where pa_remark_id = l_pa_remark_id;
ghr_pa_remarks_api.update_pa_remarks
(p_pa_remark_id => l_pa_remark_id
,p_object_version_number => l_rem_ovn
,p_remark_code_information1 => l_detail_job --Bug# 12683854
,p_remark_code_information2 => NULL
,p_remark_code_information3 => NULL
,p_remark_code_information4 => NULL
,p_remark_code_information5 => NULL
,p_description => l_remark_description
);
delete from ghr_pa_remarks
where pa_remark_id = l_pa_remark_id;
ghr_pa_remarks_api.update_pa_remarks
(p_pa_remark_id => l_pa_remark_id
,p_object_version_number => l_rem_ovn
,p_remark_code_information1 => l_detail_location --Bug# 12683854
,p_remark_code_information2 => NULL
,p_remark_code_information3 => NULL
,p_remark_code_information4 => NULL
,p_remark_code_information5 => NULL
,p_description => l_remark_description
);
END update_detail_remarks;