[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select noa.code, noa.description
from ghr_nature_of_actions noa
where noa.nature_of_action_id = p_noa_id
and noa.enabled_flag = 'Y'
and nvl(p_effective_date,trunc(sysdate))
between noa.date_from
and nvl(noa.date_to,nvl(p_effective_date,trunc(sysdate))) ;
select ppf.business_group_id
from per_people_f ppf
where ppf.person_id = p_person_id
and trunc(p_effective_date)
between nvl(trunc(ppf.effective_start_date),trunc(sysdate))
and nvl(trunc(ppf.effective_end_date),trunc(sysdate-1));
SELECT nature_of_action_id
FROM ghr_mass_awards
WHERE mass_award_id = p_mass_award_id;
p_mass_action_sel_flag out nocopy ghr_pa_requests.mass_action_select_flag%TYPE,
p_mass_action_comments out nocopy ghr_pa_requests.mass_action_comments%TYPE,
p_object_version_number out nocopy ghr_pa_requests.object_version_number%TYPE)
IS
--
-- local variables
--
l_proc varchar2(72) := g_package || 'get_pa_request_id_ovn';
SELECT pa_request_id, object_version_number,
pa_notification_id,rpa_type, mass_action_select_flag,
mass_action_comments
FROM ghr_pa_requests
WHERE mass_action_id = p_mass_award_id
AND effective_date = trunc(p_effective_date)
AND person_id = p_person_id;
p_mass_action_sel_flag := cur_rpa_rec.mass_action_select_flag;
hr_utility.set_location('select flag value ' || p_mass_action_sel_flag,15);
SELECT pa_request_id,
award_amount,
award_uom,
award_percentage
FROM ghr_pa_requests
WHERE mass_action_id = p_mass_award_id
AND rpa_type = p_rpa_type
AND person_id is null;
SELECT pa_request_id,
award_amount,
award_uom,
award_percentage
FROM ghr_pa_requests
WHERE mass_action_id = p_mass_award_id
AND rpa_type = p_rpa_type
AND person_id = p_person_id;
SELECT rei_information3 award_agency,
rei_information4 award_type,
rei_information6 group_award,
rei_information7 tangible_benefit_dollars,
rei_information9 date_award_earned,
rei_information10 appropriation_code
FROM ghr_pa_request_extra_info
WHERE pa_request_id = c_pa_request_id
AND information_type = l_information_type;
SELECT first_action_la_code1,
first_action_la_desc1,
first_lac1_information1,
first_lac1_information2,
first_lac1_information3,
first_lac1_information4,
first_lac1_information5,
first_action_la_code2,
first_action_la_desc2,
first_lac2_information1,
first_lac2_information2,
first_lac2_information3,
first_lac2_information4,
first_lac2_information5
FROM ghr_pa_requests
WHERE mass_action_id = p_mass_award_id
AND rpa_type = p_rpa_type
AND person_id is null;
SELECT
award_amount,
award_uom,
award_percentage
FROM ghr_pa_request_shadow
WHERE pa_request_id = p_pa_request_id;
SELECT rei_information3 award_agency,
rei_information4 award_type,
rei_information6 group_award,
rei_information7 tangible_benefit_dollars,
rei_information9 date_award_earned,
rei_information10 appropriation_code
FROM ghr_pa_request_ei_shadow
WHERE pa_request_id = p_pa_request_id
AND information_type = l_information_type;
SELECT trunc(effective_date) effective_date
FROM ghr_mass_awards
WHERE mass_award_id = p_mass_award_id;
SELECT to_char(ghr_process_log_s.nextval)
FROM sys.dual;
upd_elig_flag_bef_selection
( p_mass_award_id => p_mass_award_id,
p_rpa_type => l_rpa_type,
p_effective_date => l_effective_date);
del_elig_flag_aft_selection
( p_mass_award_id => p_mass_award_id,
p_rpa_type => l_rpa_type,
p_effective_date => l_effective_date);
update ghr_mass_awards set status_flag = l_status_flag
where mass_award_id = p_mass_award_id;
PROCEDURE upd_elig_flag_bef_selection
(
p_mass_award_id in ghr_mass_awards.mass_award_id%TYPE,
p_rpa_type in ghr_pa_requests.rpa_type%TYPE,
p_effective_date in date
)
is
l_proc varchar2(72) := g_package || 'upd_elig_flag_bef_selection';
select count(*) into l_cntr
from ghr_pa_requests
where mass_action_id = p_mass_award_id
and rpa_type = p_rpa_type
and pa_notification_id is null;
update ghr_pa_requests set
mass_action_eligible_flag = 'N'
where mass_action_id = p_mass_award_id
and rpa_type = p_rpa_type
and pa_notification_id is null;
PROCEDURE del_elig_flag_aft_selection
(
p_mass_award_id in ghr_mass_awards.mass_award_id%TYPE,
p_rpa_type in ghr_pa_requests.rpa_type%TYPE,
p_effective_date in date
)
IS
l_proc varchar2(72) := g_package || 'del_elig_flag_aft_selection';
SELECT pa_request_id
FROM ghr_pa_requests
WHERE mass_action_id = p_mass_award_id
AND rpa_type = p_rpa_type
AND mass_action_eligible_flag = 'N'
AND pa_notification_id is null;
delete from ghr_pa_request_ei_shadow
where pa_request_id = l_pa_request_id;
delete from ghr_pa_request_shadow
where pa_request_id = l_pa_request_id;
delete from ghr_pa_request_extra_info
where pa_request_id = l_pa_request_id;
delete from ghr_pa_routing_history
where pa_request_id = l_pa_request_id;
delete from ghr_pa_requests
where pa_request_id = l_pa_request_id;
END del_elig_flag_aft_selection;
l_mass_action_select_flag ghr_pa_requests.mass_action_select_flag%TYPE;
SELECT pa_request_extra_info_id,
object_version_number,
rei_information9
FROM ghr_pa_request_extra_info
WHERE information_type = l_information_type
AND pa_request_id = p_pa_request_id;
SELECT pa_request_extra_info_id
FROM ghr_pa_request_ei_shadow
WHERE information_type = l_information_type
AND pa_request_id = p_pa_request_id;
p_mass_action_sel_flag => l_mass_action_select_flag,
--Bug#3804067 Added mass action comments
p_mass_action_comments => l_mass_action_comments,
p_object_version_number => l_object_version_number);
AND NVL(l_mass_action_select_flag,'Y') <> 'N' ) THEN
l_mass_action_select_flag := 'N';
--p_mass_action_select_flag => 'Y'
p_mass_action_select_flag => NVL(l_mass_action_select_flag,'Y')
,p_approving_official_full_name => l_pa_request_rec.approving_official_full_name
,p_approval_date => l_approval_date
,p_approving_official_work_titl => l_pa_request_rec.approving_official_work_title
,p_1_approval_status => l_1_approval_status
-- Bug 3376761
,p_mass_action_comments => l_pa_request_rec.mass_action_comments
-- End of Bug 3376761
);
ghr_par_extra_info_api.update_pa_request_extra_info
(p_validate => false,
p_rei_information3 => null,
p_rei_information4 => null,
p_rei_information6 => null,
p_rei_information7 => null,
p_rei_information9 => null,
p_rei_information10 => null,
p_pa_request_extra_info_id => l_ei_pa_request_extra_id,
p_object_version_number => l_ei_ovn );
update ghr_pa_request_ei_shadow
set rei_information3 = null,
rei_information4 = null,
rei_information6 = null,
rei_information7 = null,
rei_information9 = null,
rei_information10 = null
where pa_request_extra_info_id = l_ei_shadow_id;
AND NVL(l_mass_action_select_flag,'Y') <> 'N' ) THEN
l_mass_action_select_flag := 'N';
ghr_sf52_api.update_sf52
(
p_pa_request_id => l_pa_request_id,
-- p_pa_notification_id => l_pa_request_rec.pa_notification_id,
p_noa_family_code => l_pa_request_rec.noa_family_code,
p_routing_group_id => l_pa_request_rec.routing_group_id,
p_par_object_version_number => l_object_version_number,
p_proposed_effective_asap_flag => l_pa_request_rec.proposed_effective_asap_flag,
p_academic_discipline => l_pa_request_rec.academic_discipline,
p_additional_info_person_id => l_pa_request_rec.additional_info_person_id,
p_additional_info_tel_number => l_pa_request_rec.additional_info_tel_number ,
--p_altered_pa_request_id => l_pa_request_rec.altered_pa_request_id,
p_annuitant_indicator => l_pa_request_rec.annuitant_indicator,
p_annuitant_indicator_desc => l_pa_request_rec.annuitant_indicator_desc,
p_appropriation_code1 => l_pa_request_rec.appropriation_code1,
p_appropriation_code2 => l_pa_request_rec.appropriation_code2,
p_approval_date => l_approval_date , --l_pa_request_rec.approval_date,
p_approving_official_full_name => l_pa_request_rec.approving_official_full_name,
p_approving_official_work_titl => l_pa_request_rec.approving_official_work_title,
p_authorized_by_person_id => l_pa_request_rec.authorized_by_person_id ,
p_authorized_by_title => l_pa_request_rec.authorized_by_title,
p_award_amount => l_pa_request_rec.award_amount,
p_award_uom => l_pa_request_rec.award_uom,
p_bargaining_unit_status => l_pa_request_rec.bargaining_unit_status,
p_citizenship => l_pa_request_rec.citizenship,
p_concurrence_date => l_pa_request_rec.concurrence_date,
p_custom_pay_calc_flag => l_pa_request_rec.custom_pay_calc_flag,
p_duty_station_code => l_pa_request_rec.duty_station_code,
p_duty_station_desc => l_pa_request_rec.duty_station_desc,
p_duty_station_id => l_pa_request_rec.duty_station_id,
p_duty_station_location_id => l_pa_request_rec.duty_station_location_id,
p_education_level => l_pa_request_rec.education_level,
p_effective_date => l_pa_request_rec.effective_date,
p_employee_assignment_id => l_pa_request_rec.employee_assignment_id,
p_employee_date_of_birth => l_pa_request_rec.employee_date_of_birth,
p_employee_first_name => l_pa_request_rec.employee_first_name,
p_employee_last_name => l_pa_request_rec.employee_last_name,
p_employee_middle_names => l_pa_request_rec.employee_middle_names,
p_employee_national_identifier => l_pa_request_rec.employee_national_identifier,
p_fegli => l_pa_request_rec.fegli,
p_fegli_desc => l_pa_request_rec.fegli_desc,
p_first_action_la_code1 => l_pa_request_rec.first_action_la_code1,
p_first_action_la_code2 => l_pa_request_rec.first_action_la_code2,
p_first_action_la_desc1 => l_pa_request_rec.first_action_la_desc1,
p_first_action_la_desc2 => l_pa_request_rec.first_action_la_desc2,
p_first_noa_cancel_or_correct => l_pa_request_rec.first_noa_cancel_or_correct,
p_first_noa_code => l_pa_request_rec.first_noa_code,
p_first_noa_desc => l_pa_request_rec.first_noa_desc,
p_first_noa_id => l_pa_request_rec.first_noa_id,
p_first_noa_pa_request_id => l_pa_request_rec.first_noa_pa_request_id,
p_flsa_category => l_pa_request_rec.flsa_category,
p_forwarding_address_line1 => l_pa_request_rec.forwarding_address_line1,
p_forwarding_address_line2 => l_pa_request_rec.forwarding_address_line2,
p_forwarding_address_line3 => l_pa_request_rec.forwarding_address_line3,
p_forwarding_country => l_pa_request_rec.forwarding_country,
p_forwarding_country_short_nam => l_pa_request_rec.forwarding_country_short_name,
p_forwarding_postal_code => l_pa_request_rec.forwarding_postal_code,
p_forwarding_region_2 => l_pa_request_rec.forwarding_region_2,
p_forwarding_town_or_city => l_pa_request_rec.forwarding_town_or_city,
p_from_adj_basic_pay => l_pa_request_rec.from_adj_basic_pay,
p_from_basic_pay => l_pa_request_rec.from_basic_pay,
p_from_grade_or_level => l_pa_request_rec.from_grade_or_level,
p_from_locality_adj => l_pa_request_rec.from_locality_adj,
p_from_occ_code => l_pa_request_rec.from_occ_code,
p_from_other_pay_amount => l_pa_request_rec.from_other_pay_amount,
p_from_pay_basis => l_pa_request_rec.from_pay_basis,
p_from_pay_plan => l_pa_request_rec.from_pay_plan,
-- FWFA Changes Bug#4444609
p_input_pay_rate_determinant => l_pa_request_rec.input_pay_rate_determinant,
p_from_pay_table_identifier => l_pa_request_rec.from_pay_table_identifier,
-- FWFA Changes
p_from_position_id => l_pa_request_rec.from_position_id,
p_from_position_org_line1 => l_pa_request_rec.from_position_org_line1,
p_from_position_org_line2 => l_pa_request_rec.from_position_org_line2,
p_from_position_org_line3 => l_pa_request_rec.from_position_org_line3,
p_from_position_org_line4 => l_pa_request_rec.from_position_org_line4,
p_from_position_org_line5 => l_pa_request_rec.from_position_org_line5,
p_from_position_org_line6 => l_pa_request_rec.from_position_org_line6,
p_from_position_number => l_pa_request_rec.from_position_number,
p_from_position_seq_no => l_pa_request_rec.from_position_seq_no,
p_from_position_title => l_pa_request_rec.from_position_title,
p_from_step_or_rate => l_pa_request_rec.from_step_or_rate,
p_from_total_salary => l_pa_request_rec.from_total_salary,
p_functional_class => l_pa_request_rec.functional_class,
p_notepad => l_pa_request_rec.notepad,
p_part_time_hours => l_pa_request_rec.part_time_hours,
-- FWFA Changes Bug#4444609
p_pay_rate_determinant => l_pa_request_rec.pay_rate_determinant,
p_person_id => l_pa_request_rec.person_id,
p_position_occupied => l_pa_request_rec.position_occupied,
p_proposed_effective_date => l_pa_request_rec.proposed_effective_date,
p_requested_by_person_id => l_pa_request_rec.requested_by_person_id,
p_requested_by_title => l_pa_request_rec.requested_by_title,
p_requested_date => l_pa_request_rec.requested_date,
p_requesting_office_remarks_de => l_pa_request_rec.requesting_office_remarks_desc,
p_requesting_office_remarks_fl => l_pa_request_rec.requesting_office_remarks_flag,
p_request_number => l_pa_request_rec.request_number,
p_resign_and_retire_reason_des => l_pa_request_rec.resign_and_retire_reason_desc,
p_retirement_plan => l_pa_request_rec.retirement_plan,
p_retirement_plan_desc => l_pa_request_rec.retirement_plan_desc,
p_second_action_la_code1 => l_pa_request_rec.second_action_la_code1,
p_second_action_la_code2 => l_pa_request_rec.second_action_la_code2,
p_second_action_la_desc1 => l_pa_request_rec.second_action_la_desc1,
p_second_action_la_desc2 => l_pa_request_rec.second_action_la_desc2,
p_second_noa_cancel_or_correct => l_pa_request_rec.second_noa_cancel_or_correct,
p_second_noa_code => l_pa_request_rec.second_noa_code,
p_second_noa_desc => l_pa_request_rec.second_noa_desc,
p_second_noa_id => l_pa_request_rec.second_noa_id,
p_second_noa_pa_request_id => l_pa_request_rec.second_noa_pa_request_id,
p_service_comp_date => l_pa_request_rec.service_comp_date,
p_supervisory_status => l_pa_request_rec.supervisory_status,
p_tenure => l_pa_request_rec.tenure,
p_to_adj_basic_pay => l_pa_request_rec.to_adj_basic_pay,
p_to_basic_pay => l_pa_request_rec.to_basic_pay,
p_to_grade_id => l_pa_request_rec.to_grade_id,
p_to_grade_or_level => l_pa_request_rec.to_grade_or_level,
p_to_job_id => l_pa_request_rec.to_job_id,
p_to_locality_adj => l_pa_request_rec.to_locality_adj,
p_to_occ_code => l_pa_request_rec.to_occ_code,
p_to_organization_id => l_pa_request_rec.to_organization_id,
p_to_other_pay_amount => l_pa_request_rec.to_other_pay_amount,
p_to_au_overtime => l_pa_request_rec.to_au_overtime,
p_to_auo_premium_pay_indicator => l_pa_request_rec.to_auo_premium_pay_indicator,
p_to_availability_pay => l_pa_request_rec.to_availability_pay,
p_to_ap_premium_pay_indicator => l_pa_request_rec.to_ap_premium_pay_indicator,
p_to_retention_allowance => l_pa_request_rec.to_retention_allowance,
p_to_supervisory_differential => l_pa_request_rec.to_supervisory_differential,
p_to_staffing_differential => l_pa_request_rec.to_staffing_differential,
p_to_pay_basis => l_pa_request_rec.to_pay_basis,
p_to_pay_plan => l_pa_request_rec.to_pay_plan,
-- FWFA Changes
p_to_pay_table_identifier => l_pa_request_rec.to_pay_table_identifier,
-- FWFA Changes
p_to_position_id => l_pa_request_rec.to_position_id,
p_to_position_org_line1 => l_pa_request_rec.to_position_org_line1,
p_to_position_org_line2 => l_pa_request_rec.to_position_org_line2,
p_to_position_org_line3 => l_pa_request_rec.to_position_org_line3,
p_to_position_org_line4 => l_pa_request_rec.to_position_org_line4,
p_to_position_org_line5 => l_pa_request_rec.to_position_org_line5,
p_to_position_org_line6 => l_pa_request_rec.to_position_org_line6,
p_to_position_number => l_pa_request_rec.to_position_number,
p_to_position_seq_no => l_pa_request_rec.to_position_seq_no,
p_to_position_title => l_pa_request_rec.to_position_title,
p_to_step_or_rate => l_pa_request_rec.to_step_or_rate,
p_to_total_salary => l_pa_request_rec.to_total_salary,
p_veterans_preference => l_pa_request_rec.veterans_preference,
p_veterans_pref_for_rif => l_pa_request_rec.veterans_pref_for_rif,
p_veterans_status => l_pa_request_rec.veterans_status,
p_work_schedule => l_pa_request_rec.work_schedule,
p_work_schedule_desc => l_pa_request_rec.work_schedule_desc,
p_year_degree_attained => l_pa_request_rec.year_degree_attained,
p_first_noa_information1 => l_pa_request_rec.first_noa_information1,
p_first_noa_information2 => l_pa_request_rec.first_noa_information2,
p_first_noa_information3 => l_pa_request_rec.first_noa_information3,
p_first_noa_information4 => l_pa_request_rec.first_noa_information4,
p_first_noa_information5 => l_pa_request_rec.first_noa_information5,
p_second_lac1_information1 => l_pa_request_rec.second_lac1_information1,
p_second_lac1_information2 => l_pa_request_rec.second_lac1_information2,
p_second_lac1_information3 => l_pa_request_rec.second_lac1_information3,
p_second_lac1_information4 => l_pa_request_rec.second_lac1_information4,
p_second_lac1_information5 => l_pa_request_rec.second_lac1_information5,
p_second_lac2_information1 => l_pa_request_rec.second_lac2_information1,
p_second_lac2_information2 => l_pa_request_rec.second_lac2_information2,
p_second_lac2_information3 => l_pa_request_rec.second_lac2_information3,
p_second_lac2_information4 => l_pa_request_rec.second_lac2_information4,
p_second_lac2_information5 => l_pa_request_rec.second_lac2_information5,
p_second_noa_information1 => l_pa_request_rec.second_noa_information1,
p_second_noa_information2 => l_pa_request_rec.second_noa_information2,
p_second_noa_information3 => l_pa_request_rec.second_noa_information3,
p_second_noa_information4 => l_pa_request_rec.second_noa_information4,
p_second_noa_information5 => l_pa_request_rec.second_noa_information5,
p_first_lac1_information1 => l_pa_request_rec.first_lac1_information1,
p_first_lac1_information2 => l_pa_request_rec.first_lac1_information2,
p_first_lac1_information3 => l_pa_request_rec.first_lac1_information3,
p_first_lac1_information4 => l_pa_request_rec.first_lac1_information4,
p_first_lac1_information5 => l_pa_request_rec.first_lac1_information5,
p_first_lac2_information1 => l_pa_request_rec.first_lac2_information1,
p_first_lac2_information2 => l_pa_request_rec.first_lac2_information2,
p_first_lac2_information3 => l_pa_request_rec.first_lac2_information3,
p_first_lac2_information4 => l_pa_request_rec.first_lac2_information4,
p_first_lac2_information5 => l_pa_request_rec.first_lac2_information5,
----- p_print_sf50_flag
----- p_printer_name
----- p_u_attachment_modified_flag => l_pa_request_rec.u_attachment_modified_flag,
----- p_u_approved_flag => l_pa_request_rec.u_approved_flag,
----- p_u_user_name_acted_on => l_pa_request_rec.u_user_name_acted_on,
p_u_action_taken => 'NOT_ROUTED',
p_u_approval_status => l_1_approval_status, --l_pa_request_rec.u_approval_status,
----- p_i_user_name_routed_to => l_pa_request_rec.i_user_name_routed_to,
----- p_i_groupbox_id => l_pa_request_rec.i_groupbox_id,
----- p_i_routing_list_id => l_pa_request_rec.i_routing_list_id,
----- p_i_routing_seq_number => l_pa_request_rec.i_routing_seq_number,
p_to_retention_allow_percentag => l_pa_request_rec.to_retention_allow_percentage,
p_to_supervisory_diff_percenta => l_pa_request_rec.to_supervisory_diff_percentage,
p_to_staffing_diff_percentage => l_pa_request_rec.to_staffing_diff_percentage,
p_award_percentage => l_pa_request_rec.award_percentage,
p_rpa_type => p_rpa_type,
p_mass_action_id => p_mass_award_id,
p_mass_action_eligible_flag => 'Y',
p_u_prh_object_version_number => l_u_prh_object_version_number ,
p_i_pa_routing_history_id => l_i_pa_routing_history_id,
p_i_prh_object_version_number => l_i_prh_object_version_number
-- Bug 3376761
,p_mass_action_comments => l_pa_request_rec.mass_action_comments
-- End of Bug 3376761
-- Bug#4486823 RRR Changes
,p_award_salary => l_pa_request_rec.award_salary
-- Bug#4486823 RRR Changes
,p_mass_action_select_flag => NVL(l_mass_action_select_flag,'Y')
);
update_shadow_row (p_rpa_data => l_pa_request_rec,
p_result => l_result);
hr_utility.set_location('Shadow updated ..'|| l_proc,158);
END IF; ------ Creation of SF52 and Update of SF52
l_mass_action_select_flag ghr_pa_requests.mass_action_select_flag%TYPE;
SELECT prh.pa_routing_history_id,
prh.object_version_number
FROM ghr_pa_routing_history prh
WHERE prh.pa_request_id = l_pa_request_rec.pa_request_id
ORDER by 1 desc;
SELECT name
FROM ghr_mass_awards
WHERE mass_award_id = p_mass_award_id;
p_mass_action_sel_flag => l_mass_action_select_flag,
--Bug#3804067 Added mass action comments
p_mass_action_comments => l_mass_action_comments,
p_object_version_number => l_object_version_number);
IF l_pa_notification_id is null and l_rpa_type = 'A' and l_mass_action_select_flag <> 'N' THEN
if l_1_pa_routing_history_id is null then
l_1_pa_routing_history_id := l_i_pa_routing_history_id;
AND NVL(l_mass_action_select_flag,'Y') <> 'N' ) THEN
l_mass_action_select_flag := 'N';
ghr_mass_actions_pkg.replace_insertion_values
(p_desc => l_pa_request_rec.first_noa_desc,
p_information1 => l_pa_request_rec.first_noa_information1,
-- Bug#3648118 Passed the local variable as OUT parameter
-- because passing l_pa_request_rec.first_noa_desc as OUT parameter
-- is making the variable as NULL.
p_desc_out => l_first_noa_desc
);
l_rpa_action := 'UPDATE_HR';
ghr_sf52_api.update_sf52
(
p_pa_request_id => l_pa_request_rec.pa_request_id,
p_par_object_version_number => l_pa_request_rec.object_version_number,
----Bug 2348413
p_effective_date => l_pa_request_rec.effective_date,
p_employee_assignment_id => l_pa_request_rec.employee_assignment_id,
p_noa_family_code => l_pa_request_rec.noa_family_code,
----Bug 2348413
p_routing_group_id => l_routing_group_id,
p_u_action_taken => l_rpa_action,
p_u_prh_object_version_number => l_u_prh_object_version_number ,
p_first_noa_id => l_pa_request_rec.first_noa_id, -- Bug#2740882
p_i_pa_routing_history_id => l_i_pa_routing_history_id,
p_i_prh_object_version_number => l_i_prh_object_version_number);
hr_utility.set_location('Update sf52 final Failure ' || l_proc,190);
l_log_text := 'Error in Update sf52 final ' ||' Sql error : '||sqlerrm(sqlcode);
ELSIF l_mass_action_select_flag = 'N'
and l_status = 'DESELECTED PRG:' THEN
hr_utility.set_location('Deselected by programatically' || l_proc,200);
p_errbuf := 'DESELECTED PRG:';
ELSIF l_mass_action_select_flag = 'N' then
hr_utility.set_location('Deselected by user ' || l_proc,200);
p_status := 'DESELECTED';
p_mass_action_sel_flag => l_mass_action_select_flag,
--Bug#3804067 Added mass action comments
p_mass_action_comments => l_mass_action_comments,
p_object_version_number => l_object_version_number);
p_mass_action_select_flag => 'N',
p_mass_action_comments => 'PRG: Programatically Deselected',
p_object_version_number => l_object_version_number);
l_log_text := substr(l_log_text || ' , Failed in Deselecting, ' ||' Sql error : '||sqlerrm(sqlcode),1,2000);
SELECT pa_request_extra_info_id,
object_version_number
FROM ghr_pa_request_extra_info
WHERE information_type = l_information_type
AND pa_request_id = p_pa_request_id;
SELECT pa_request_extra_info_id
FROM ghr_pa_request_ei_shadow
WHERE information_type = l_information_type
AND pa_request_id = p_pa_request_id;
ghr_par_extra_info_api.update_pa_request_extra_info
(p_validate => false,
p_rei_information3 => l_d_award_agency,
p_rei_information4 => l_d_award_type,
p_rei_information6 => l_d_group_award,
p_rei_information7 => l_d_tbd,
p_rei_information9 => l_d_date_award_earn,
p_rei_information10 => l_d_appropriation_code,
p_pa_request_extra_info_id => l_pa_request_extra_info_id,
p_object_version_number => l_pa_object_version_number);
insert into ghr_pa_request_ei_shadow
(pa_request_extra_info_id,
pa_request_id,
information_type,
rei_information3,
rei_information4,
rei_information6,
rei_information7,
rei_information9,
rei_information10)
values (ghr_pa_request_extra_info_s.nextval,
p_pa_request_id,
l_information_type,
l_s_award_agency,
l_s_award_type,
l_s_group_award,
l_s_tbd,
l_s_date_award_earn,
l_s_appropriation_code);
ELSE update ghr_pa_request_ei_shadow
set rei_information3 = l_s_award_agency,
rei_information4 = l_s_award_type,
rei_information6 = l_s_group_award,
rei_information7 = l_s_tbd,
rei_information9 = l_s_date_award_earn,
rei_information10 = l_s_appropriation_code
where pa_request_extra_info_id = l_pa_request_extra_info_sh_id;
insert into ghr_pa_request_shadow(
pa_request_id
,academic_discipline
,annuitant_indicator
,appropriation_code1
,appropriation_code2
,bargaining_unit_status
,citizenship
,duty_station_id
,duty_station_location_id
,education_level
,employee_date_of_birth
,employee_first_name
,employee_last_name
,employee_middle_names
,employee_national_identifier
,fegli
,flsa_category
,forwarding_address_line1
,forwarding_address_line2
,forwarding_address_line3
,forwarding_country_short_name
,forwarding_postal_code
,forwarding_region_2
,forwarding_town_or_city
,functional_class
,part_time_hours
,pay_rate_determinant
,position_occupied
,retirement_plan
,service_comp_date
,supervisory_status
,tenure
,to_ap_premium_pay_indicator
,to_auo_premium_pay_indicator
,to_occ_code
,to_position_id
,to_retention_allowance
,to_staffing_differential
,to_step_or_ratE
,to_supervisory_differential
,veterans_preference
,veterans_pref_for_riF
,veterans_status
,work_schedule
,year_degree_attained
,to_retention_allow_percentage
,to_supervisory_diff_percentage
,to_staffing_diff_percentage
,award_amount
,award_uom
,award_percentage )
values (
p_rpa_data.pa_request_id
,p_rpa_data.academic_discipline
,p_rpa_data.annuitant_indicator
,p_rpa_data.appropriation_code1
,p_rpa_data.appropriation_code2
,p_rpa_data.bargaining_unit_status
,p_rpa_data.citizenship
,p_rpa_data.duty_station_id
,p_rpa_data.duty_station_location_id
,p_rpa_data.education_level
,p_rpa_data.employee_date_of_birth
,p_rpa_data.employee_first_name
,p_rpa_data.employee_last_name
,p_rpa_data.employee_middle_names
,p_rpa_data.employee_national_identifier
,p_rpa_data.fegli
,p_rpa_data.flsa_category
,p_rpa_data.forwarding_address_line1
,p_rpa_data.forwarding_address_line2
,p_rpa_data.forwarding_address_line3
,p_rpa_data.forwarding_country_short_name
,p_rpa_data.forwarding_postal_code
,p_rpa_data.forwarding_region_2
,p_rpa_data.forwarding_town_or_city
,p_rpa_data.functional_class
,p_rpa_data.part_time_hours
,p_rpa_data.pay_rate_determinant
,p_rpa_data.position_occupied
,p_rpa_data.retirement_plan
,p_rpa_data.service_comp_date
,p_rpa_data.supervisory_status
,p_rpa_data.tenure
,p_rpa_data.to_ap_premium_pay_indicator
,p_rpa_data.to_auo_premium_pay_indicator
,p_rpa_data.to_occ_code
,p_rpa_data.to_position_id
,p_rpa_data.to_retention_allowance
,p_rpa_data.to_staffing_differential
,p_rpa_data.to_step_or_ratE
,p_rpa_data.to_supervisory_differential
,p_rpa_data.veterans_preference
,p_rpa_data.veterans_pref_for_riF
,p_rpa_data.veterans_status
,p_rpa_data.work_schedule
,p_rpa_data.year_degree_attained
,p_rpa_data.to_retention_allow_percentage
,p_rpa_data.to_supervisory_diff_percentage
,p_rpa_data.to_staffing_diff_percentage
,p_rpa_data.award_amount
,p_rpa_data.award_uom
,p_rpa_data.award_percentage );
Procedure update_shadow_row ( p_rpa_data in ghr_pa_requests%rowtype,
p_result out nocopy Boolean ) is
Begin
update ghr_pa_request_shadow
set
academic_discipline = p_rpa_data.academic_discipline
,annuitant_indicator = p_rpa_data.annuitant_indicator
,appropriation_code1 = p_rpa_data.appropriation_code1
,appropriation_code2 = p_rpa_data.appropriation_code2
,bargaining_unit_status = p_rpa_data.bargaining_unit_status
,citizenship = p_rpa_data.citizenship
,duty_station_id = p_rpa_data.duty_station_id
,duty_station_location_id = p_rpa_data.duty_station_location_id
,education_level = p_rpa_data.education_level
,employee_date_of_birth = p_rpa_data.employee_date_of_birth
,employee_first_name = p_rpa_data.employee_first_name
,employee_last_name = p_rpa_data.employee_last_name
,employee_middle_names = p_rpa_data.employee_middle_names
,employee_national_identifier = p_rpa_data.employee_national_identifier
,fegli = p_rpa_data.fegli
,flsa_category = p_rpa_data.flsa_category
,forwarding_address_line1 = p_rpa_data.forwarding_address_line1
,forwarding_address_line2 = p_rpa_data.forwarding_address_line2
,forwarding_address_line3 = p_rpa_data.forwarding_address_line3
,forwarding_country_short_name = p_rpa_data.forwarding_country_short_name
,forwarding_postal_code = p_rpa_data.forwarding_postal_code
,forwarding_region_2 = p_rpa_data.forwarding_region_2
,forwarding_town_or_city = p_rpa_data.forwarding_town_or_city
,functional_class = p_rpa_data.functional_class
,part_time_hours = p_rpa_data.part_time_hours
,pay_rate_determinant = p_rpa_data.pay_rate_determinant
,position_occupied = p_rpa_data.position_occupied
,retirement_plan = p_rpa_data.retirement_plan
,service_comp_date = p_rpa_data.service_comp_date
,supervisory_status = p_rpa_data.supervisory_status
,tenure = p_rpa_data.tenure
,to_ap_premium_pay_indicator = p_rpa_data.to_ap_premium_pay_indicator
,to_auo_premium_pay_indicator = p_rpa_data.to_auo_premium_pay_indicator
,to_occ_code = p_rpa_data.to_occ_code
,to_position_id = p_rpa_data.to_position_id
,to_retention_allowance = p_rpa_data.to_retention_allowance
,to_staffing_differential = p_rpa_data.to_staffing_differential
,to_step_or_ratE = p_rpa_data.to_step_or_ratE
,to_supervisory_differential = p_rpa_data.to_supervisory_differential
,veterans_preference = p_rpa_data.veterans_preference
,veterans_pref_for_riF = p_rpa_data.veterans_pref_for_riF
,veterans_status = p_rpa_data.veterans_status
,work_schedule = p_rpa_data.work_schedule
,year_degree_attained = p_rpa_data.year_degree_attained
,to_retention_allow_percentage = p_rpa_data.to_retention_allow_percentage
,to_supervisory_diff_percentage = p_rpa_data.to_supervisory_diff_percentage
,to_staffing_diff_percentage = p_rpa_data.to_staffing_diff_percentage
,award_amount = p_rpa_data.award_amount
,award_uom = p_rpa_data.award_uom
,award_percentage = p_rpa_data.award_percentage
where pa_request_id = p_rpa_data.pa_request_id;
end update_shadow_row;
select pa_remark_id,
remark_id,
description,
remark_code_information1,
remark_code_information2,
remark_code_information3,
remark_code_information4,
remark_code_information5
from ghr_pa_remarks
where pa_request_id =
(select pa_request_id from ghr_pa_requests
where mass_action_id = p_mass_award_id
and rpa_type = p_rpa_type
and person_id is null)
order by pa_remark_id;
select pa_remark_id,
object_version_number
from ghr_pa_remarks
where pa_request_id = p_pa_request_id
and remark_id = l_remark_id
and description = l_description
order by pa_remark_id;
ghr_pa_remarks_api.update_pa_remarks
(p_pa_remark_id => l_p_pa_remark_id
,p_object_version_number => l_p_object_version_number
,p_remark_code_information1 => l_remark_code_information1
,p_remark_code_information2 => l_remark_code_information2
,p_remark_code_information3 => l_remark_code_information3
,p_remark_code_information4 => l_remark_code_information4
,p_remark_code_information5 => l_remark_code_information5
,p_description => l_description
);
SELECT object_version_number,
person_id,
employee_last_name,
employee_first_name,
employee_middle_names,
employee_national_identifier
FROM ghr_pa_requests
WHERE pa_request_id = l_pa_request_id;
p_mass_action_select_flag => 'N',
p_mass_action_comments =>
'PRG: Prgramatically Deselected, Failed at Call Work flow',
p_object_version_number => l_object_version_number);