8: ,p_new_location_id IN hr_locations.location_id%TYPE)
9: IS
10:
11:
12: l_old_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
13: l_new_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
14: l_old_locality_pay_area_id ghr_duty_stations_f.locality_pay_area_id%TYPE;
15: l_new_locality_pay_area_id ghr_duty_stations_f.locality_pay_area_id%TYPE;
16: l_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
9: IS
10:
11:
12: l_old_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
13: l_new_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
14: l_old_locality_pay_area_id ghr_duty_stations_f.locality_pay_area_id%TYPE;
15: l_new_locality_pay_area_id ghr_duty_stations_f.locality_pay_area_id%TYPE;
16: l_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
17: l_person_id per_people_f.person_id%TYPE;
10:
11:
12: l_old_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
13: l_new_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
14: l_old_locality_pay_area_id ghr_duty_stations_f.locality_pay_area_id%TYPE;
15: l_new_locality_pay_area_id ghr_duty_stations_f.locality_pay_area_id%TYPE;
16: l_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
17: l_person_id per_people_f.person_id%TYPE;
18: l_effective_start_date per_assignments_f.effective_start_date%TYPE;
11:
12: l_old_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
13: l_new_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
14: l_old_locality_pay_area_id ghr_duty_stations_f.locality_pay_area_id%TYPE;
15: l_new_locality_pay_area_id ghr_duty_stations_f.locality_pay_area_id%TYPE;
16: l_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
17: l_person_id per_people_f.person_id%TYPE;
18: l_effective_start_date per_assignments_f.effective_start_date%TYPE;
19: l_full_name per_people_f.full_name%TYPE;
12: l_old_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
13: l_new_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
14: l_old_locality_pay_area_id ghr_duty_stations_f.locality_pay_area_id%TYPE;
15: l_new_locality_pay_area_id ghr_duty_stations_f.locality_pay_area_id%TYPE;
16: l_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
17: l_person_id per_people_f.person_id%TYPE;
18: l_effective_start_date per_assignments_f.effective_start_date%TYPE;
19: l_full_name per_people_f.full_name%TYPE;
20: l_national_identifier per_people_f.national_identifier%TYPE;
67:
68: cursor cur_old_ds is
69: select b.duty_station_code old_duty_station_code
70: from hr_location_extra_info a,
71: ghr_duty_stations_f b
72: where information_type = 'GHR_US_LOC_INFORMATION'
73: and a.lei_information3 = b.duty_station_id
74: and b.duty_station_code in
75: ('040355019', '060920071', '181788003', '181789003', '195549095',
86:
87: cursor cur_new_ds is
88: select b.duty_station_code new_duty_station_code
89: from hr_location_extra_info a,
90: ghr_duty_stations_f b
91: where information_type = 'GHR_US_LOC_INFORMATION'
92: and a.lei_information3 = b.duty_station_id
93: and b.duty_station_code in
94: ('040335019', '062922071', '181850003', '181850003', '195548095', '204840131',
103: and a.location_id = p_new_location_id;
104:
105: cursor cur_old_locality_id is
106: select nvl(locality_pay_area_id,0) locality_pay_area_id
107: from ghr_duty_stations_f
108: where duty_station_code = l_old_duty_station_code
109: and nvl((l_effective_date - 1),sysdate)
110: between effective_start_date and effective_end_date;
111:
110: between effective_start_date and effective_end_date;
111:
112: cursor cur_new_locality_id is
113: select nvl(locality_pay_area_id,0) locality_pay_area_id
114: from ghr_duty_stations_f
115: where duty_station_code = l_new_duty_station_code
116: and nvl(l_effective_date,sysdate)
117: between effective_start_date and effective_end_date;
118: