[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pr.pa_request_id
, max (pa_routing_history_id) pa_routing_history_id
FROM ghr_pa_requests pr
, ghr_pa_routing_history prh
WHERE pr.pa_request_id = prh.pa_request_id
AND person_id = p_person_id
AND first_noa_code = p_first_noa_code
AND effective_date = p_effective_date
AND nvl (pr.first_noa_cancel_or_correct, 'X') <> ghr_history_api.g_cancel
GROUP BY pr.pa_request_id;
SELECT substr (pr.employee_last_name
|| ', '
|| pr.employee_first_name, 1
, 240) fname
FROM ghr_pa_requests pr
WHERE pr.pa_request_id = l_pa_request_id;
SELECT nvl (action_taken, ' ') action_taken
FROM ghr_pa_routing_history
WHERE pa_routing_history_id = p_r_hist_id;
l_proc varchar2(72) DEFAULT g_package|| '.update_sel_flg';
|| ' - Mass NOA Selectable : '
|| ' RPA Exists for the given effective date ');
PROCEDURE update_sel_flg
(p_person_id IN number
, p_effective_date IN date) IS
l_person_extra_info_id number;
l_proc varchar2(72) DEFAULT g_package|| '.update_sel_flg';
g_proc := 'update_sel_flg';
ghr_person_extra_info_api.update_person_extra_info
(p_person_extra_info_id => l_person_extra_info_id
, p_effective_date => sysdate
, p_object_version_number => l_object_version_number
, p_pei_information3 => NULL
, p_pei_information4 => NULL
, p_pei_information5 => NULL
, p_pei_information10 => NULL
, p_pei_information_category => 'GHR_US_PER_MASS_ACTIONS');
END update_sel_flg;
FUNCTION check_select_flg
(p_person_id IN number
, p_action IN varchar2
, p_effective_date IN date
, p_mass_salary_id IN number
, p_sel_flg IN OUT NOCOPY varchar2) RETURN boolean IS
l_per_ei_data per_people_extra_info%ROWTYPE;
l_proc varchar2(72) DEFAULT g_package|| '.check_select_flg';
g_proc := 'check_select_flg';
END check_select_flg;
SELECT information9 info9
, information10 info10
, information11 info11
, information17 info17
FROM ghr_pa_history
WHERE person_id = p_person_id
AND pa_history_id IN
(
SELECT max (pa_history_id)
FROM ghr_pa_history
WHERE person_id = p_person_id
AND information5 = 'GHR_US_PER_MASS_ACTIONS'
AND table_name = 'PER_PEOPLE_EXTRA_INFO'
AND effective_date = eff_date
GROUP BY information11
);
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;
SELECT *
FROM ghr_pa_requests
WHERE pa_request_id = p_pa_request_id;
ghr_par_extra_info_api.update_pa_request_extra_info
(p_validate => FALSE
, p_rei_information3 => l_employment_type
, p_pa_request_extra_info_id => l_pa_request_extra_info_id
, p_object_version_number => l_pa_object_version_number);
ghr_par_extra_info_api.update_pa_request_extra_info
(p_validate => FALSE
, p_rei_information8 => l_employment_type
, p_pa_request_extra_info_id => l_pa_request_extra_info_id
, p_object_version_number => l_pa_object_version_number);
ghr_par_extra_info_api.update_pa_request_extra_info
(p_validate => FALSE
, p_rei_information3 => l_eit_poi
, p_rei_information4 => l_eit_agency
, p_rei_information5 => l_eit_poi_dt
, 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)
VALUES (ghr_pa_request_extra_info_s.nextval
, p_pa_request_id
, l_information_type
, l_employment_type);
INSERT
INTO ghr_pa_request_ei_shadow (pa_request_extra_info_id
, pa_request_id
, information_type
, rei_information8)
VALUES (ghr_pa_request_extra_info_s.nextval
, p_pa_request_id
, l_information_type
, l_employment_type);
INSERT
INTO ghr_pa_request_ei_shadow (pa_request_extra_info_id
, pa_request_id
, information_type
, rei_information3
, rei_information4
, rei_information5)
VALUES (ghr_pa_request_extra_info_s.nextval
, p_pa_request_id
, l_information_type
, l_eit_poi
,l_pos_agency_code
,l_eit_poi_dt);
UPDATE ghr_pa_request_ei_shadow
SET rei_information3 = l_employment_type
WHERE pa_request_extra_info_id = l_pa_request_extra_info_sh_id;
UPDATE ghr_pa_request_ei_shadow
SET rei_information8 = l_employment_type
WHERE pa_request_extra_info_id = l_pa_request_extra_info_sh_id;
UPDATE ghr_pa_request_ei_shadow
SET rei_information3 = l_eit_poi
,rei_information4 = l_pos_agency_code
,rei_information5 = l_eit_poi_dt
WHERE pa_request_extra_info_id = l_pa_request_extra_info_sh_id;
SELECT count (*) cnt
FROM ghr_mass_salary_criteria
WHERE mass_salary_id = p_mass_salary_id
AND pay_plan = cur_pay_plan
AND pay_rate_determinant = cur_prd;
hr_utility.set_location ('No Additional Selection CRITERIA', 85);
select ast.user_status
from
per_assignment_status_types ast,
per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and ast.assignment_status_type_id = asg.assignment_status_type_id
and p_effective_date
between asg.effective_start_date
and asg.effective_end_date;
ghr_mass_act_custom.pre_insert
(p_cust_in_rec => l_cust_in_rec
, p_cust_rec => l_cust_rec );
INSERT
INTO ghr_mass_noa_preview (mass_noa_preview_id
, mass_action_type
, ui_type
, session_id
, effective_date
, employee_date_of_birth
, full_name
, national_identifier
, employee_number
, duty_station_id
, duty_station_code
, duty_station_desc
, personnel_office_id
, poi_desc
, from_basic_pay
, to_basic_pay
, from_adj_basic_pay
, to_adj_basic_pay
, from_locality_adj
, to_locality_adj
, from_total_salary
, to_total_salary
, position_id
, position_title
, position_number
, position_seq_no
, agency_code
, person_id
, select_flag
, noa_code
, organization_name
, to_agency_code
, to_duty_station_id
, to_duty_station_code
, to_duty_station_desc
, old_assignment_status
, new_assignment_status
, old_type_of_employment
, new_type_of_employment
, old_pay_userra_status
, new_pay_userra_status
, occ_code
, to_occ_code
, from_bargaining_unit_status
, to_bargaining_unit_status
, from_flsa_category
, to_flsa_category
, from_functional_class
, to_functional_class
, furlough_indicator
, old_lwop_nte_start_date
, new_lwop_nte_start_date
, old_lwop_nte
, new_lwop_nte
, old_furlough_nte_start_date
, new_furlough_nte_start_date
, old_furlough_nte
, new_furlough_nte
, user_attribute1
, user_attribute2
, user_attribute3
, user_attribute4
, user_attribute5
, user_attribute6
, user_attribute7
, user_attribute8
, user_attribute9
, user_attribute10
, user_attribute11
, user_attribute12
, user_attribute13
, user_attribute14
, user_attribute15
, user_attribute16
, user_attribute17
, user_attribute18
, user_attribute19
, user_attribute20)
VALUES (GHR_MASS_NOA_PREVIEW_S.nextval
, 'MNOA'
, 'FORM'
, userenv ('SESSIONID')
, p_effective_date
, p_date_of_birth
, p_full_name
, p_national_identifier
, p_employee_number
, p_duty_station_id
, p_duty_station_code
, p_duty_station_desc
, p_personnel_office_id
, l_poi_desc
, p_basic_pay
, l_new_basic_pay
, p_adj_basic_pay
, l_new_adj_basic_pay
, p_old_loc_adj
, l_new_loc_adj
, p_tot_old_sal
, l_tot_new_sal
, p_position_id
, p_position_title
, p_position_number
, p_position_seq_no
, p_agency_sub_element_code
, p_person_id
, p_sel_flg
, l_temp_noa_code
, p_organization_name
, p_to_agency_code
, p_to_duty_station_id
, p_to_duty_station_code
, p_to_duty_station_desc
, l_old_assignment_status
, l_new_assignment_status
, l_old_type_of_employment
, l_new_type_of_employment
, l_old_pay_userra_status
, l_new_pay_userra_status
, p_occ_code
, p_to_occ_code
, p_from_bargaining_unit_status
, p_to_bargaining_unit_status
, p_from_flsa_category
, p_to_flsa_category
, p_from_functional_class
, p_to_functional_class
, l_furlough_indicator
, l_old_lwop_nte_start_date
, l_new_lwop_nte_start_date
, l_old_lwop_nte
, l_new_lwop_nte
, l_old_furlough_nte_start_date
, l_new_furlough_nte_start_date
, l_old_furlough_nte
, l_new_furlough_nte
, l_cust_rec.user_attribute1
, l_cust_rec.user_attribute2
, l_cust_rec.user_attribute3
, l_cust_rec.user_attribute4
, l_cust_rec.user_attribute5
, l_cust_rec.user_attribute6
, l_cust_rec.user_attribute7
, l_cust_rec.user_attribute8
, l_cust_rec.user_attribute9
, l_cust_rec.user_attribute10
, l_cust_rec.user_attribute11
, l_cust_rec.user_attribute12
, l_cust_rec.user_attribute13
, l_cust_rec.user_attribute14
, l_cust_rec.user_attribute15
, l_cust_rec.user_attribute16
, l_cust_rec.user_attribute17
, l_cust_rec.user_attribute18
, l_cust_rec.user_attribute19
, l_cust_rec.user_attribute20);
SELECT person_extra_info_id
, object_version_number
FROM per_people_extra_info
WHERE person_id = person
AND information_type = 'GHR_US_PER_MASS_ACTIONS';
ghr_person_extra_info_api.update_person_extra_info
(p_person_extra_info_id => l_person_extra_info_id
, p_effective_date => trunc (l_eff_date)
, p_object_version_number => l_object_version_number
, p_pei_information3 => p_sel_flag
, p_pei_information4 => p_comment
, p_pei_information5 => to_char (p_msl_id)
, p_pei_information_category => 'GHR_US_PER_MASS_ACTIONS');
SELECT *
FROM ghr_pa_requests
WHERE pa_request_id = p_pa_request_id;
PROCEDURE update_position_info
(p_position_data_rec IN ghr_sf52_pos_update.position_data_rec_type) IS
l_proc varchar2(30) DEFAULT 'update_position_info';
ghr_sf52_pos_update.update_position_info
(p_pos_data_rec => p_position_data_rec);
hr_utility.set_location ('Calling post_update_process '|| l_proc, 50);
ghr_history_api.post_update_process;
END update_position_info;
l_position_data_rec ghr_sf52_pos_UPDATE.position_data_rec_type;
SELECT ppf.person_id person_id
, ppf.first_name first_name
, ppf.last_name last_name
, ppf.middle_names middle_names
, ppf.full_name full_name
, ppf.date_of_birth date_of_birth
, ppf.national_identifier national_identifier
, ppf.employee_number employee_number
, paf.position_id position_id
, paf.assignment_id assignment_id
, paf.grade_id grade_id
, paf.job_id job_id
, paf.location_id location_id
, paf.organization_id organization_id
, paf.business_group_id business_group_id
, paf.assignment_status_type_id assignment_status_type_id
FROM per_assignments_f paf
, per_people_f ppf
, per_person_types ppt
, hr_organization_units hou
, per_assignment_status_types past
WHERE ppf.person_id = paf.person_id
AND l_effective_date
BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND l_effective_date
BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.primary_flag = 'Y'
AND paf.assignment_type <> 'B'
AND ppf.person_type_id = ppt.person_type_id
AND paf.assignment_status_type_id = past.assignment_status_type_id
AND ppt.system_person_type IN ('EMP', 'EMP_APL')
AND past.per_system_status <> 'SUSP_ASSIGN' --Bug# 14658176
AND upper (user_status) NOT IN ('TERMINATE ASSIGNMENT', 'ACTIVE APPLICATION', 'OFFER'
, 'ACCEPTED', 'TERMINATE APPLICATION', 'END'
, 'FURLOUGH', 'FURLOUGH NTE', 'TERMINATE APPOINTMENT'
, 'SEPARATED')
AND paf.organization_id = hou.organization_id
AND paf.position_id IS NOT NULL
ORDER BY ppf.person_id;
SELECT ppf.person_id person_id
, ppf.first_name first_name
, ppf.last_name last_name
, ppf.middle_names middle_names
, ppf.full_name full_name
, ppf.date_of_birth date_of_birth
, ppf.national_identifier national_identifier
, ppf.employee_number employee_number
, paf.position_id position_id
, paf.assignment_id assignment_id
, paf.grade_id grade_id
, paf.job_id job_id
, paf.location_id location_id
, paf.organization_id organization_id
, paf.business_group_id business_group_id
, paf.assignment_status_type_id assignment_status_type_id
FROM per_assignments_f paf
, per_people_f ppf
, per_person_types ppt
, hr_organization_units hou
, per_assignment_status_types past
WHERE ppf.person_id = paf.person_id
AND l_effective_date
BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND l_effective_date
BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.primary_flag = 'Y'
AND paf.assignment_type <> 'B'
AND ppf.person_type_id = ppt.person_type_id
AND paf.assignment_status_type_id = past.assignment_status_type_id
AND ppt.system_person_type IN ('EMP', 'EMP_APL')
AND past.per_system_status = 'SUSP_ASSIGN'
AND past.user_status NOT IN ('Non Pay') --Bug# 14414790
AND paf.organization_id = hou.organization_id
AND paf.position_id IS NOT NULL
ORDER BY ppf.person_id;
SELECT ppf.person_id person_id
, ppf.first_name first_name
, ppf.last_name last_name
, ppf.middle_names middle_names
, ppf.full_name full_name
, ppf.date_of_birth date_of_birth
, ppf.national_identifier national_identifier
, ppf.employee_number employee_number
, paf.position_id position_id
, paf.assignment_id assignment_id
, paf.grade_id grade_id
, paf.job_id job_id
, paf.location_id location_id
, paf.organization_id organization_id
, paf.business_group_id business_group_id
, paf.assignment_status_type_id assignment_status_type_id
FROM per_assignments_f paf
, per_people_f ppf
, per_person_types ppt
, hr_organization_units hou
, per_assignment_status_types past
WHERE ppf.person_id = paf.person_id
AND l_effective_date
BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND l_effective_date
BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.primary_flag = 'Y'
AND paf.assignment_type <> 'B'
AND ppf.person_type_id = ppt.person_type_id
AND paf.assignment_status_type_id = past.assignment_status_type_id
AND ppt.system_person_type IN ('EMP', 'EMP_APL')
AND past.per_system_status = 'SUSP_ASSIGN'
AND past.user_status = 'Non Pay' --Bug# 14405844
AND paf.organization_id = hou.organization_id
AND paf.position_id IS NOT NULL
ORDER BY ppf.person_id;
SELECT ppf.person_id person_id
, ppf.first_name first_name
, ppf.last_name last_name
, ppf.middle_names middle_names
, ppf.full_name full_name
, ppf.date_of_birth date_of_birth
, ppf.national_identifier national_identifier
, ppf.employee_number employee_number
, paf.position_id position_id
, paf.assignment_id assignment_id
, paf.grade_id grade_id
, paf.job_id job_id
, paf.location_id location_id
, paf.organization_id organization_id
, paf.business_group_id business_group_id
, paf.assignment_status_type_id assignment_status_type_id
FROM per_assignments_f paf
, per_people_f ppf
, per_person_types ppt
, hr_organization_units hou
, per_assignment_status_types past
WHERE ppf.person_id = paf.person_id
AND l_eff_date
BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND l_eff_date
BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.primary_flag = 'Y'
AND paf.assignment_type <> 'B'
AND ppf.person_type_id = ppt.person_type_id
AND ppt.system_person_type IN ('EMP', 'EMP_APL')
AND paf.assignment_status_type_id = past.assignment_status_type_id
AND past.user_status IN ('Furlough NTE') --Bug# 14414790
AND paf.organization_id = hou.organization_id
AND paf.position_id IS NOT NULL
AND paf.business_group_id = nvl (l_business_group_id, paf.business_group_id)
ORDER BY ppf.person_id;
SELECT ppf.person_id person_id
, ppf.first_name first_name
, ppf.last_name last_name
, ppf.middle_names middle_names
, ppf.full_name full_name
, ppf.date_of_birth date_of_birth
, ppf.national_identifier national_identifier
, ppf.employee_number employee_number
, paf.position_id position_id
, paf.assignment_id assignment_id
, paf.grade_id grade_id
, paf.job_id job_id
, paf.location_id location_id
, paf.organization_id organization_id
, paf.business_group_id business_group_id
, paf.assignment_status_type_id assignment_status_type_id
FROM per_assignments_f paf
, per_people_f ppf
, per_person_types ppt
, hr_organization_units hou
, per_assignment_status_types past
WHERE ppf.person_id = paf.person_id
AND l_effective_date
BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND l_effective_date
BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.primary_flag = 'Y'
AND paf.assignment_type <> 'B'
AND ppf.person_type_id = ppt.person_type_id
AND paf.assignment_status_type_id = past.assignment_status_type_id
AND ppt.system_person_type IN ('EMP', 'EMP_APL')
AND paf.organization_id = hou.organization_id
AND paf.position_id IS NOT NULL
AND past.user_status = 'Leave Without Pay NTE'
ORDER BY ppf.person_id;
SELECT ppf.person_id person_id
, ppf.first_name first_name
, ppf.last_name last_name
, ppf.middle_names middle_names
, ppf.full_name full_name
, ppf.date_of_birth date_of_birth
, ppf.national_identifier national_identifier
, ppf.employee_number employee_number
, paf.position_id position_id
, paf.assignment_id assignment_id
, paf.grade_id grade_id
, paf.job_id job_id
, paf.location_id location_id
, paf.organization_id organization_id
, paf.business_group_id business_group_id
, paf.assignment_status_type_id assignment_status_type_id
FROM per_assignments_f paf
, per_people_f ppf
, per_person_types ppt
, hr_organization_units hou
, per_assignment_status_types past
WHERE ppf.person_id = paf.person_id
AND l_effective_date
BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND l_effective_date
BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.primary_flag = 'Y'
AND paf.assignment_type <> 'B'
AND ppf.person_type_id = ppt.person_type_id
AND paf.assignment_status_type_id = past.assignment_status_type_id
AND ppt.system_person_type IN ('EMP', 'EMP_APL')
AND paf.organization_id = hou.organization_id
AND paf.position_id IS NOT NULL
AND past.per_system_status <> 'SUSP_ASSIGN' --Bug# 14658176
--AND past.user_status NOT IN('Leave Without Pay NTE','Non Pay','Leave Without Pay Mil','Leave Without Pay US')
ORDER BY ppf.person_id;
SELECT ppf.person_id person_id
, ppf.first_name first_name
, ppf.last_name last_name
, ppf.middle_names middle_names
, ppf.full_name full_name
, ppf.date_of_birth date_of_birth
, ppf.national_identifier national_identifier
, ppf.employee_number employee_number
, paf.position_id position_id
, paf.assignment_id assignment_id
, paf.grade_id grade_id
, paf.job_id job_id
, paf.location_id location_id
, paf.organization_id organization_id
, paf.business_group_id business_group_id
, paf.assignment_status_type_id assignment_status_type_id
FROM per_assignments_f paf
, per_people_f ppf
, per_person_types ppt
, hr_organization_units hou
WHERE ppf.person_id = paf.person_id
AND l_effective_date
BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND l_effective_date
BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.primary_flag = 'Y'
AND paf.assignment_type <> 'B'
AND ppf.person_type_id = ppt.person_type_id
AND ppt.system_person_type IN ('EMP', 'EMP_APL')
AND paf.organization_id = hou.organization_id
AND paf.position_id IS NOT NULL
ORDER BY ppf.person_id;
SELECT null PERSON_ID,
'VACANT' FIRST_NAME,
'VACANT' LAST_NAME,
null MIDDLE_NAMES,
'VACANT' FULL_NAME,
null DATE_OF_BIRTH,
null NATIONAL_IDENTIFIER,
null EMPLOYEE_NUMBER,
position_id POSITION_ID,
null ASSIGNMENT_ID,
to_NUMBER(null) GRADE_ID,
JOB_ID,
pop.LOCATION_ID,
pop.ORGANIZATION_ID,
pop.BUSINESS_GROUP_ID,
punits.name ORGANIZATION_NAME,
pop.availability_status_id
from hr_positions_f pop,
per_organization_units punits
WHERE pop.business_group_id = g_business_group_id
and trunc(effective_date) between pop.effective_start_DATE and pop.effective_END_DATE
and pop.organization_id = punits.organization_id
and not exists
(
SELECT 'X'
FROM per_people_f p, per_assignments_f a
WHERE trunc(effective_date) between a.effective_start_date and a.effective_end_date
AND a.primary_flag = 'Y'
AND a.assignment_type <> 'B'
AND p.current_employee_flag = 'Y'
AND a.business_group_id = g_business_group_id
AND a.person_id = p.person_id
AND a.position_id = pop.position_id
AND trunc(effective_date) between p.effective_start_date and p.effective_end_date
);
SELECT gdf.segment1
,gdf.segment2
from per_grades grd, per_grade_definitions gdf
WHERE grd.grade_id = grd_id
and grd.grade_definition_id = gdf.grade_definition_id;
SELECT name
, effective_date
, mass_salary_id
, user_table_id
, submit_flag
, executive_order_number
, executive_order_date
, rowid
, pa_request_id
, organization_id
, duty_station_id
, personnel_office_id
, agency_code_subelement
, opm_issuance_number
, opm_issuance_date
, locality_pay_area_code
, noa_code
, nte_date
,target_bargaining_unit_status
,target_flsa_category
,target_occ_code
,target_job_id
,target_agency_code_subelement
,target_duty_station_id
,target_duty_station_code
,target_functional_class
,bargaining_unit_status
,flsa_category
,job_id
FROM ghr_mass_salaries
WHERE mass_salary_id = p_msnoa_id
FOR UPDATE OF user_table_id NOWAIT;
SELECT noa_family_code
FROM ghr_families
WHERE noa_family_code IN
(
SELECT noa_family_code
FROM ghr_noa_families
WHERE nature_of_action_id IN
(
SELECT nature_of_action_id
FROM ghr_nature_of_actions
WHERE code = ghr_msl_pkg.g_first_noa_code
)
)
AND proc_method_flag = 'Y';
SELECT lei.location_id location_id
FROM hr_location_extra_info lei, ghr_duty_stations_v ds
WHERE ds.duty_station_code = c_duty_station_code
AND lei.information_type = 'GHR_US_LOC_INFORMATION'
and to_number(lei.lei_information3) = ds.duty_station_id
and l_effective_date between ds.effective_start_date
and ds.effective_end_date;
ghr_mlc_pkg.check_select_flg_pos (l_position_id, upper (p_action)
, l_effective_date, p_mass_salary_id
, l_sel_flg);
ghr_position_extra_info_api.update_position_extra_info
(p_position_extra_info_id => l_new_pos_grp1_rec.position_extra_info_id
, p_effective_date => l_effective_date
, p_object_version_number => l_new_pos_grp1_rec.object_version_number
, p_poei_information7 => l_vac_flsa_category
, p_poei_information8 => l_vac_barg_unit_status
, p_poei_information11 => l_vac_functional_class-- Bug# 14410401
, p_poei_information_category => 'GHR_US_POS_GRP1');
pr ('Error in UPDATE_position_extra_info is '
|| l_errbuf
|| ' '
|| to_char (l_retcode));
ghr_position_extra_info_api.update_position_extra_info
(p_position_extra_info_id => l_new_pos_grp1_rec.position_extra_info_id
, p_effective_date => l_effective_date
, p_object_version_number => l_new_pos_grp1_rec.object_version_number
, p_poei_information8 => l_target_barg_unit_status
, p_poei_information_category => 'GHR_US_POS_GRP1');
pr ('Error in UPDATE_position_extra_info is '
|| l_errbuf
|| ' '
|| to_char (l_retcode));
update_position_info (l_position_data_rec);
ghr_validate_perwsdpo.update_posn_status(l_position_id,l_effective_date);
' Mass NOA Selectable : '||
p_mass_salary ||' Vacant pos Successfully completed');
l_noaerrbuf := 'Error in ghr_sf52_pos_UPDATE.UPDATE_position_info'
|| ' Sql Err is '
|| sqlerrm (sqlcode);
|| ' For Mass NOA Selectable : '
|| p_mass_salary
|| ' Pos Name: '
|| l_position_title
|| ' position No: '
|| l_position_id
|| ' '
|| l_noaerrbuf;
|| ' For Mass NOA Selectable : '
|| p_mass_salary
|| ' Sql Err is '
|| sqlerrm (sqlcode);
UPDATE ghr_mass_salaries
SET submit_flag = 'E'
WHERE rowid = l_rowid;
IF check_select_flg (p_person_id, upper (p_action)
, l_effective_date, p_mass_salary_id
, l_sel_flg) THEN
BEGIN
-- Bug# 14751973, commented below code
/*IF l_noa_code ='792' AND l_target_duty_station_id IS NOT NULL AND l_target_duty_station_id <> l_duty_station_id THEN
l_duty_station_id := l_target_duty_station_id;
|| ' Mass NOA Selectable : '
|| p_mass_salary
|| ' SF52 Successfully completed');
g_proc := 'update_SEL_FLG';
update_sel_flg (p_person_id, l_effective_date);
END IF;--IF check_select_flg
|| ' For Mass NOA Selectable : '
|| p_mass_salary
|| ' Name: '
|| p_full_name
|| ' Emp No: '
|| p_employee_number
|| ' '
|| l_noaerrbuf;
|| ' For Mass NOA Selectable : '
|| p_mass_salary
|| ' Sql Err is '
|| sqlerrm (sqlcode);
UPDATE ghr_mass_salaries
SET submit_flag = 'E'
WHERE rowid = l_rowid;
hr_utility.set_location ('Mass Selectable NOA is in use', 1);
IF ghr_pa_requests_pkg.get_pos_availability_status (l_avail_status_id) NOT IN ('DELETED', 'FROZEN', 'ELIMINATED') THEN
BEGIN
l_personnel_office_id := NULL;
UPDATE ghr_mass_salaries
SET submit_flag = 'P'
WHERE rowid = l_rowid;
hr_utility.set_location ('Error in Update ghr_msl Sql error '|| sqlerrm (sqlcode), 30);
UPDATE ghr_mass_salaries
SET submit_flag = 'E'
WHERE rowid = l_rowid;
|| ' For Mass NOA Selectable : '
|| p_mass_salary
|| ' Sql Err is '
|| sqlerrm (sqlcode);
UPDATE ghr_mass_salaries
SET submit_flag = 'E'
WHERE rowid = l_rowid;
SELECT name
, target_mass_action_id
, effective_date
, mass_salary_id
, submit_flag
, rowid
, pa_request_id
FROM ghr_mass_salaries
WHERE mass_salary_id = p_mcan_id
FOR UPDATE OF user_table_id NOWAIT;
SELECT *
FROM ghr_pa_requests a
WHERE a.effective_date = p_effective_date
AND a.mass_action_id = p_mass_action_id
AND a.pa_notification_id IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM ghr_pa_requests b
WHERE b.first_noa_code IN ('001')
AND b.person_id = a.person_id
AND b.pa_notification_id IS NOT NULL
START WITH pa_request_id = a.pa_request_id
AND altered_pa_request_id IS NULL
CONNECT BY altered_pa_request_id = PRIOR pa_request_id
);
SELECT substr (employee_last_name|| ', '|| employee_first_name, 1, 240) fname,person_id
FROM ghr_pa_requests
WHERE effective_date = p_effective_date
AND mass_action_id = p_mass_action_id;
SELECT rowid
, object_version_number
FROM ghr_pa_requests a
WHERE pa_request_id = p_pa_request_id;
SELECT full_name
, employee_number
FROM per_people_f
WHERE person_id = p_person_id
AND p_effective_date
BETWEEN effective_start_date
AND effective_end_date;
SELECT prh.pa_routing_history_id
, prh.object_version_number
FROM ghr_pa_routing_history prh
WHERE prh.pa_request_id = l_can_pa_request_id
ORDER BY 1 DESC;
SELECT request_number
, object_version_number
FROM ghr_pa_requests
WHERE pa_request_id = l_can_pa_request_id
ORDER BY 1 DESC;
SELECT asg.organization_id
FROM per_all_assignments_f asg
WHERE asg.person_id = l_per_id
AND asg.assignment_type <> 'B'
AND trunc(p_effective_date) BETWEEN asg.effective_start_date AND asg.effective_end_date
ORDER BY asg.assignment_id;
SELECT trunc (effective_date)
, session_id
FROM fnd_sessions
WHERE session_id = userenv ('sessionid');
INSERT
INTO fnd_sessions (session_id, effective_date)
VALUES (l_session_id, sysdate);
UPDATE fnd_sessions
SET effective_date = trunc (sysdate)
WHERE session_id = l_session_id;
g_proc := 'update_Mass_cancel_record';
hr_utility.set_location ('Update pa_req_num '|| l_proc, 200);
ghr_sf52_api.update_sf52
(p_pa_request_id => l_can_pa_request_id
, p_par_object_version_number => l_object_version_number
, p_effective_date => l_effective_date
, p_routing_group_id => l_routing_group_id
, p_u_action_taken => 'UPDATE_HR'
, 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);
hr_utility.set_location ('Update sf52 final Failure '
|| l_proc, 190);
l_log_text := 'Error in Update sf52 final '
|| ' Sql error : '
|| sqlerrm (sqlcode);
UPDATE ghr_mass_salaries
SET submit_flag = 'E'
WHERE rowid = l_rowid;
UPDATE ghr_mass_salaries
SET submit_flag = 'E'
WHERE rowid = l_rowid;
UPDATE ghr_mass_salaries
SET submit_flag = 'P'
WHERE rowid = l_rowid;
hr_utility.set_location ('Error in Update ghr_msl Sql error '
|| sqlerrm (sqlcode), 30);
UPDATE ghr_mass_salaries
SET submit_flag = 'E'
WHERE rowid = l_rowid;
UPDATE ghr_mass_salaries
SET submit_flag = 'E'
WHERE rowid = l_rowid;
DELETE from ghr_mass_noa_preview
WHERE mass_action_type = 'MNOA'
AND session_id = p_session_id;