1 PACKAGE BODY ghr_ds_jan99_pkg AS
2 /* $Header: ghdsconv.pkb 120.0.12010000.2 2009/05/26 10:34:23 utokachi noship $ */
3
4 PROCEDURE do_conversion(
5 p_errbuf out NOCOPY varchar2
6 ,p_retcode out NOCOPY number
7 ,p_old_location_id IN hr_locations.location_id%TYPE
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;
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;
21 l_assignment_id per_assignments_f.assignment_id%TYPE;
22 l_location_id per_assignments_f.location_id%TYPE;
23 l_position_id per_assignments_f.position_id%TYPE;
24 l_pos_name per_positions.name%TYPE;
25 l_organization_id per_assignments_f.organization_id%TYPE;
26 l_effective_date date;
27 l_assign_effective_date date;
28
29
30 l_count number := 0;
31
32 l_log_text varchar2(2000);
33
34 l_datetrack_update_mode varchar2(30);
35 l_object_version_number number;
36 l_special_ceiling_step_id number;
37 l_start_date date;
38 l_end_date date;
39 l_people_group_id number;
40 l_group_name varchar2(2000);
41 l_org_now_no_manager_warning boolean;
42 l_other_manager_warning boolean;
43 l_spp_delete_warning boolean;
44 l_entries_changed_warning varchar2(2000);
45 l_tax_district_changed_warning boolean;
46
47 l_position_definition_id number;
48 l_name varchar2(2000);
49 l_valid_grades_changed_warning boolean;
50
51 l_entered_by hr_locations.entered_by%TYPE;
52 l_location_code hr_locations.location_code%TYPE;
53
54 l_eed date;
55 l_esd date;
56 l_exists boolean := false;
57 l_out_eed date;
58 l_out_esd date;
59
60 same_loc_err exception;
61 ds422760045 exception;
62 old_not_in_opm exception;
63 new_not_in_opm exception;
64 old_new_not_in_opm exception;
65 pay_area_id_err exception;
66
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',
76 '204891103', '211257115', '211758081', '211758187', '213397003',
77 '220376047', '222431059', '240414031', '240931047', '241371003',
78 '265260085', '296675179', '330043017', '343478025', '398961099',
79 '421172125', '424275109', '424676109', '471348157',
80 '484208013', '484209153', '485936303', '511566069', '530171061',
81 '530533025', '541475079', '542325035', '542334035', '542857045',
82 'UV0000000', 'CF0000000', 'CG0000000', 'PS0000000', 'TC0000000',
83 'TC1000000', 'TC1030000', 'TC1040000', 'TC1050000', 'TC1200000',
84 'TC1300000', 'TC1500000', 'WS0000000', '422760045')
85 and a.location_id = p_old_location_id;
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',
95 '211256115', '211757081', '211757187', '210019003', '221920121', '221130059',
96 '240411031', '240110047', '241366003', '265260075', '296654179', '330029017',
97 '343475025', '399230099', '421170125', '420000109', '420000109', '471338157',
98 '480000013', '482400153', '484140303', '511565041', '530170061', '530529025',
99 '541474079', '541348035', '541348035', '541541045',
100 'UV0000000', 'CF0000000', 'CG0000000', 'PS0000000', 'AE0000000',
101 'AE1000000', 'AE1030000', 'AE1040000', 'AE1050000', 'AE1200000',
102 'AE1300000', 'AE1500000', 'WS0000000')
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
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
119 cursor cur_people is
120 select paf.person_id person_id,
121 paf.effective_start_date effective_start_date,
122 paf.assignment_id assignment_id,
123 paf.object_version_number object_version_number
124 from per_assignments_f paf,
125 per_assignment_status_types ast
126 where l_effective_date between paf.effective_start_date
127 and paf.effective_end_date
128 and paf.location_id = p_old_location_id
129 and ast.assignment_status_type_id = paf.assignment_status_type_id
130 and ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN','TERM_ASSIGN')
131 union
132 select paf1.person_id person_id,
133 paf1.effective_start_date effective_start_date,
134 paf1.assignment_id assignment_id,
135 paf1.object_version_number object_version_number
136 from per_assignments_f paf1,
137 per_assignment_status_types ast1
138 where l_effective_date <= paf1.effective_start_date
139 and paf1.location_id = p_old_location_id
140 and ast1.assignment_status_type_id = paf1.assignment_status_type_id
141 and ast1.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN','TERM_ASSIGN')
142 order by 3,2;
143
144
145 Cursor c_full_name is
146 select ppf.full_name
147 from per_people_f ppf
148 where person_id = l_person_id
149 and l_effective_date
150 between ppf.effective_start_date and ppf.effective_end_date;
151
152 cursor c_update_mode_a is
153 select asg.effective_start_date ,
154 asg.effective_end_date
155 from per_assignments_f asg
156 where asg.assignment_id = l_assignment_id
157 and l_assign_effective_date
158 between asg.effective_start_date
159 and asg.effective_end_date;
160
161 cursor c_update_mode_a1 is
162 select asg.effective_start_date ,
163 asg.effective_end_date
164 from per_assignments_f asg
165 where asg.assignment_id = l_assignment_id
166 and l_assign_effective_date < asg.effective_start_date;
167
168 cursor cur_position is
169 select pos1.position_id position_id,
170 pos1.name name,
171 pos1.effective_start_date effective_start_date,
172 pos1.object_version_number object_version_number
173 from hr_all_positions_f pos1
174 where l_effective_date between pos1.effective_start_date
175 and pos1.effective_end_date
176 and pos1.location_id = p_old_location_id
177 union
178 select pos2.position_id position_id,
179 pos2.name name,
180 pos2.effective_start_date effective_start_date,
181 pos2.object_version_number object_version_number
182 from hr_all_positions_f pos2
183 where l_effective_date <= pos2.effective_start_date
184 and pos2.location_id = p_old_location_id
185 order by 1,3;
186
187 cursor c_pos_update_mode_a is
188 select pos.effective_start_date ,
189 pos.effective_end_date
190 from hr_all_positions_f pos
191 where pos.position_id = l_position_id
192 and l_assign_effective_date
193 between pos.effective_start_date
194 and pos.effective_end_date;
195
196 cursor c_pos_update_mode_a1 is
197 select pos.effective_start_date ,
198 pos.effective_end_date
199 from hr_all_positions_f pos
200 where pos.position_id = l_position_id
201 and l_assign_effective_date < pos.effective_start_date;
202
203 cursor cur_organizations is
204 select organization_id,name
205 from hr_organization_units
206 where location_id = p_old_location_id
207 for update of location_id;
208
209 ---cursor cur_loc is
210 ---select entered_by,location_code,object_version_number
211 ---from hr_locations
212 ---where location_id = p_old_location_id
213 ---for update of inactive_date;
214
215 BEGIN
216 p_retcode := 0;
217 p_errbuf := NULL;
218
219 -- Set the effective_date as
220 l_effective_date := to_date('1999/01/01','YYYY/MM/DD');
221 l_old_duty_station_code := null;
222 l_new_duty_station_code := null;
223
224 ghr_mto_int.set_log_program_name('GHR_LOC_CONV_PKG');
225
226 -- Check Location id are same
227 if p_old_location_id = p_new_location_id then raise same_loc_err; end if;
228
229 -- Fetch the Location Extra info old duty station code and validate
230 -- otherwise write in the log.
231
232 for cur_old_ds_rec in cur_old_ds
233 loop
234 l_old_duty_station_code := cur_old_ds_rec.old_duty_station_code;
235 end loop;
236
237 -- Check old location id Extra info duty station is not pertaining to OPM Change
238 -- otherwise fetched old_location_id is null
239 if l_old_duty_station_code is null then raise old_not_in_opm; end if;
240
241 -- Check old Location id Extra information is ds422760045
242 if l_old_duty_station_code = '422760045' then raise ds422760045; end if;
243
244 -- Fetch the Location Extra info new duty station code and validate
245 -- otherwise write in the log.
246
247 for cur_new_ds_rec in cur_new_ds
248 loop
249 l_new_duty_station_code := cur_new_ds_rec.new_duty_station_code;
250 end loop;
251
252 -- Check new location id Extra info duty station is not pertaining to OPM Change
253 -- otherwise fetched new_location_id is null
254 if l_new_duty_station_code is null then raise new_not_in_opm; end if;
255
256 if l_old_duty_station_code = '040355019' then l_duty_station_code := '040335019'; end if;
257 if l_old_duty_station_code = '060920071' then l_duty_station_code := '062922071'; end if;
258 if l_old_duty_station_code = '181788003' then l_duty_station_code := '181850003'; end if;
259 if l_old_duty_station_code = '181789003' then l_duty_station_code := '181850003'; end if;
260 if l_old_duty_station_code = '195549095' then l_duty_station_code := '195548095'; end if;
261 if l_old_duty_station_code = '204891103' then l_duty_station_code := '204840131'; end if;
262 if l_old_duty_station_code = '211257115' then l_duty_station_code := '211256115'; end if;
263 if l_old_duty_station_code = '211758081' then l_duty_station_code := '211757081'; end if;
264 if l_old_duty_station_code = '211758187' then l_duty_station_code := '211757187'; end if;
265 if l_old_duty_station_code = '213397003' then l_duty_station_code := '210019003'; end if;
266 if l_old_duty_station_code = '220376047' then l_duty_station_code := '221920121'; end if;
267 if l_old_duty_station_code = '222431059' then l_duty_station_code := '221130059'; end if;
268 if l_old_duty_station_code = '240414031' then l_duty_station_code := '240411031'; end if;
269 if l_old_duty_station_code = '240931047' then l_duty_station_code := '240110047'; end if;
270 if l_old_duty_station_code = '241371003' then l_duty_station_code := '241366003'; end if;
271 if l_old_duty_station_code = '265260085' then l_duty_station_code := '265260075'; end if;
272 if l_old_duty_station_code = '296675179' then l_duty_station_code := '296654179'; end if;
273 if l_old_duty_station_code = '330043017' then l_duty_station_code := '330029017'; end if;
274 if l_old_duty_station_code = '343478025' then l_duty_station_code := '343475025'; end if;
275 if l_old_duty_station_code = '398961099' then l_duty_station_code := '399230099'; end if;
276 if l_old_duty_station_code = '421172125' then l_duty_station_code := '421170125'; end if;
277 if l_old_duty_station_code = '424275109' then l_duty_station_code := '420000109'; end if;
278 if l_old_duty_station_code = '424676109' then l_duty_station_code := '420000109'; end if;
279 if l_old_duty_station_code = '471348157' then l_duty_station_code := '471338157'; end if;
280 if l_old_duty_station_code = '484208013' then l_duty_station_code := '480000013'; end if;
281 if l_old_duty_station_code = '484209153' then l_duty_station_code := '482400153'; end if;
282 if l_old_duty_station_code = '485936303' then l_duty_station_code := '484140303'; end if;
283 if l_old_duty_station_code = '511566069' then l_duty_station_code := '511565041'; end if;
284 if l_old_duty_station_code = '530171061' then l_duty_station_code := '530170061'; end if;
285 if l_old_duty_station_code = '530533025' then l_duty_station_code := '530529025'; end if;
286 if l_old_duty_station_code = '541475079' then l_duty_station_code := '541474079'; end if;
287 if l_old_duty_station_code = '542325035' then l_duty_station_code := '541348035'; end if;
288 if l_old_duty_station_code = '542334035' then l_duty_station_code := '541348035'; end if;
289 if l_old_duty_station_code = '542857045' then l_duty_station_code := '541541045'; end if;
290
291 if l_old_duty_station_code = 'UV0000000' then l_duty_station_code := 'UV0000000' ; end if;
292 if l_old_duty_station_code = 'CF0000000' then l_duty_station_code := 'CF0000000' ; end if;
293 if l_old_duty_station_code = 'CG0000000' then l_duty_station_code := 'CG0000000' ; end if;
294 if l_old_duty_station_code = 'PS0000000' then l_duty_station_code := 'PS0000000' ; end if;
295 if l_old_duty_station_code = 'TC0000000' then l_duty_station_code := 'AE0000000' ; end if;
296 if l_old_duty_station_code = 'TC1000000' then l_duty_station_code := 'AE1000000' ; end if;
297 if l_old_duty_station_code = 'TC1030000' then l_duty_station_code := 'AE1030000' ; end if;
298 if l_old_duty_station_code = 'TC1040000' then l_duty_station_code := 'AE1040000' ; end if;
299 if l_old_duty_station_code = 'TC1050000' then l_duty_station_code := 'AE1050000' ; end if;
300 if l_old_duty_station_code = 'TC1200000' then l_duty_station_code := 'AE1200000' ; end if;
301 if l_old_duty_station_code = 'TC1300000' then l_duty_station_code := 'AE1300000' ; end if;
302 if l_old_duty_station_code = 'TC1500000' then l_duty_station_code := 'AE1500000' ; end if;
303 if l_old_duty_station_code = 'WS0000000' then l_duty_station_code := 'WS0000000' ; end if;
304
305 -- Check old duty_station and new duty station combination as per OPM
306 if l_duty_station_code <> l_new_duty_station_code then
307 raise old_new_not_in_opm; end if;
308
309 -- Fetch Locality pay areas id and then compare
310 for cur_old_locality_id_rec in cur_old_locality_id
311 loop
312 l_old_locality_pay_area_id := cur_old_locality_id_rec.locality_pay_area_id;
313 end loop;
314 for cur_new_locality_id_rec in cur_new_locality_id
315 loop
316 l_new_locality_pay_area_id := cur_new_locality_id_rec.locality_pay_area_id;
317 end loop;
318
319 if nvl(l_new_locality_pay_area_id,0) <> nvl(l_old_locality_pay_area_id,0) then
320 raise pay_area_id_err; end if;
321
322 -- Fetch all the employees and Update the location_id in Assignment, Position,
323 -- Organization.
324 begin
325 for cur_people_rec in cur_people
326 loop
327 l_person_id := cur_people_rec.person_id;
328 l_effective_start_date := cur_people_rec.effective_start_date;
329 l_assignment_id := cur_people_rec.assignment_id;
330 l_object_version_number := cur_people_rec.object_version_number;
331
332 If l_effective_start_date >= l_effective_date then
333 -- l_datetrack_update_mode := 'CORRECTION';
334 l_assign_effective_date := l_effective_start_date;
335 else
336 --l_datetrack_update_mode := 'UPDATE';
337 l_assign_effective_date := l_effective_date;
338 end if;
339
340
341 for update_mode in c_update_mode_a loop
342 l_esd := update_mode.effective_start_date;
343 l_eed := update_mode.effective_end_date;
344 end loop;
345 If l_esd = l_assign_effective_date then
346 l_datetrack_update_mode := 'CORRECTION';
347 Elsif l_esd < l_assign_effective_date and
348 to_char(l_eed,'YYYY/MM/DD') = '4712/12/31' then
349 l_datetrack_update_mode := 'UPDATE';
350 -- to end date a row and then create a new row
351 Elsif l_esd < l_assign_effective_date then
352 for update_mode1 in c_update_mode_a1 loop
353 l_exists := true;
354 exit;
355 end loop;
356 If l_exists then
357 l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
358 l_exists := false;
359 Else
360 l_datetrack_update_mode := 'CORRECTION';
361 End if;
362 End if;
363 --End if;
364 hr_utility.set_location('UPDATE_MODE : ' || l_datetrack_update_mode,2);
365 for c_full_name_rec in c_full_name loop
366 l_full_name := c_full_name_rec.full_name;
367 end loop;
368 l_log_text := 'In Assignments - Employee Name : ' || l_full_name || ' : ' ;
369 l_log_text := l_log_text || ' Assignment Id : ' || to_char(l_assignment_id) || ' - ';
370
371 hr_utility.set_location('asg id ' || l_assignment_id,1);
372 hr_utility.set_location('ed ' || l_effective_date,1);
373 hr_utility.set_location('ead ' || l_assign_effective_date,1);
374 hr_utility.set_location('OVN ' || l_object_version_number,1);
375
376 ---- Update Assignment - New Location in Date track as of 01-JAN-1999
377 ghr_session.set_session_var_for_core
378 (p_effective_date => l_assign_effective_date );
379 begin
380 hr_assignment_api.update_emp_asg_criteria
381 (p_effective_date => l_assign_effective_date
382 ,p_datetrack_update_mode => l_datetrack_update_mode
383 ,p_assignment_id => l_assignment_id
384 ,p_object_version_number => l_object_version_number
385 ,p_location_id => p_new_location_id
386 ,p_special_ceiling_step_id => l_special_ceiling_step_id
387 ,p_effective_start_date => l_start_date
388 ,p_effective_end_date => l_end_date
389 ,p_people_group_id => l_people_group_id
390 ,p_group_name => l_group_name
391 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
392 ,p_other_manager_warning => l_other_manager_warning
393 ,p_spp_delete_warning => l_spp_delete_warning
394 ,p_entries_changed_warning => l_entries_changed_warning
395 ,p_tax_district_changed_warning => l_tax_district_changed_warning
396 );
397 l_count := l_count + 1;
398 exception when others then
399 ghr_mto_int.log_message
400 (p_procedure => 'Error in Assignments'
401 ,p_message => l_log_text || ' Sql error : '|| sqlerrm(sqlcode)
402 );
403 end;
404 begin
405 ghr_history_api.post_update_process;
406 exception when others then
407 ghr_mto_int.log_message
408 (p_procedure => 'Error in Assignments(History)'
409 ,p_message => l_log_text || ' Sql error : '|| sqlerrm(sqlcode)
410 );
411 end;
412 end loop;
413
414 if l_count <> 0 then
415 ghr_mto_int.log_message
416 (p_procedure => 'Success Completion Asg'
417 ,p_message => 'All Employees New Location changed in ' || to_char(l_count) || ' Assignments'
418 );
419 else
420 ghr_mto_int.log_message
421 (p_procedure => 'Success Completion Asg'
422 ,p_message => 'But No Employees for the given Old Location'
423 );
424 end if;
425 end;
426
427 -- Initialize the Counter for positions.
428 l_count := 0;
429 begin
430 for cur_position_rec in cur_position
431 loop
432 l_pos_name := cur_position_rec.name;
433 l_position_id := cur_position_rec.position_id;
434 l_effective_start_date := cur_position_rec.effective_start_date;
435 l_object_version_number := cur_position_rec.object_version_number;
436
437 l_log_text := 'In Positions - Position Name ' || l_pos_name;
438
439 -- Update Position - New Location
440 -----
441 If l_effective_start_date >= l_effective_date then
442 -- l_datetrack_update_mode := 'CORRECTION';
443 l_assign_effective_date := l_effective_start_date;
444 else
445 --l_datetrack_update_mode := 'UPDATE';
446 l_assign_effective_date := l_effective_date;
447 end if;
448
449
450 for pos_update_mode in c_pos_update_mode_a loop
451 l_esd := pos_update_mode.effective_start_date;
452 l_eed := pos_update_mode.effective_end_date;
453 end loop;
454 If l_esd = l_assign_effective_date then
455 l_datetrack_update_mode := 'CORRECTION';
456 Elsif l_esd < l_assign_effective_date and
457 to_char(l_eed,'YYYY/MM/DD') = '4712/12/31' then
458 l_datetrack_update_mode := 'UPDATE';
459 -- to end date a row and then create a new row
460 Elsif l_esd < l_assign_effective_date then
461 for pos_update_mode1 in c_pos_update_mode_a1 loop
462 l_exists := true;
463 exit;
464 end loop;
465 If l_exists then
466 l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
467 l_exists := false;
468 Else
469 l_datetrack_update_mode := 'CORRECTION';
470 End if;
471 End if;
472 --End if;
473 hr_utility.set_location('UPDATE_MODE Position : ' || l_datetrack_update_mode,2);
474 ----
475 hr_utility.set_location('pos id ' || l_position_id,1);
476 hr_utility.set_location('ed ' || l_effective_date,1);
477 hr_utility.set_location('esd ' || l_effective_start_date,1);
478 hr_utility.set_location('ead ' || l_assign_effective_date,1);
479 hr_utility.set_location('OVN ' || l_object_version_number,1);
480
481 begin
482 ghr_session.set_session_var_for_core
483 (p_effective_date => l_effective_date );
484
485 savepoint hrposupd;
486
487 hr_position_api.update_position
488 (p_position_id => l_position_id
489 ,p_effective_start_date => l_out_esd
490 ,p_effective_end_date => l_out_eed
491 ,p_object_version_number => l_object_version_number
492 ,p_location_id => p_new_location_id
493 ,p_position_definition_id => l_position_definition_id
494 ,p_name => l_name
495 ,p_valid_grades_changed_warning => l_valid_grades_changed_warning
496 ,p_effective_date => l_assign_effective_date
497 ,p_datetrack_mode => l_datetrack_update_mode
498 );
499
500 ghr_history_api.post_update_process;
501 l_count := l_count + 1;
502 exception when others then
503 rollback to hrposupd;
504 ghr_mto_int.log_message
505 (p_procedure => 'Error in Positions'
506 ,p_message => l_log_text || ' Sql error : '|| sqlerrm(sqlcode)
507 );
508 end;
509
510 end loop;
511
512 if l_count <> 0 then
513 ghr_mto_int.log_message
514 (p_procedure => 'Success Completion Pos'
515 ,p_message => 'New Location changed for ' || to_char(l_count) || ' of Positions'
516 );
517 else
518 ghr_mto_int.log_message
519 (p_procedure => 'Success Completion Pos'
520 ,p_message => 'But No Positions for the given Old Location'
521 );
522 end if;
523 end;
524
525 -- Initialize the Counter for Organizations.
526 l_count := 0;
527 begin
528 for cur_organizations_rec in cur_organizations
529 loop
530 l_organization_id := cur_organizations_rec.organization_id;
531
532 ---- Update Organization - New Location
533 update hr_organization_units set location_id = p_new_location_id
534 where current of cur_organizations;
535
536 l_count := l_count + 1;
537
538 end loop;
539
540 if l_count <> 0 then
541 ghr_mto_int.log_message
542 (p_procedure => 'Success Completion Org'
543 ,p_message => 'New Location changed for ' || to_char(l_count) || ' of Organizations'
544 );
545 else
546 ghr_mto_int.log_message
547 (p_procedure => 'Success Completion Org'
548 ,p_message => 'But No Organizations for the given Old Location'
549 );
550 end if;
551 end;
552
553 --- Commented because do not inactivate the location Bug # 896345
554 --- begin
555 --- for cur_loc_rec in cur_loc
556 --- loop
557 --- l_entered_by := cur_loc_rec.entered_by;
558 --- l_location_code := cur_loc_rec.location_code;
559 --- l_object_version_number := cur_loc_rec.object_version_number;
560 ---
561 ---- Update Location as inactive as on 31-DEC-1998
562 --- update hr_locations set inactive_date = (l_effective_date - 1)
563 --- where current of cur_loc;
564 ---
565 --- end loop;
566 --- end;
567
568 commit;
569
570 EXCEPTION
571 when same_loc_err then
572 l_log_text := 'The New Location chosen is the same as the Old Location. ';
573 l_log_text := l_log_text || 'Please verify that the New Location is correct.';
574 l_log_text := l_log_text || ' Only OPM mandated changes can be run through the Duty Station Conversion. ';
575 ghr_mto_int.log_message
576 (p_procedure => 'Same Old and New Location Name'
577 ,p_message => l_log_text
578 );
579 commit;
580
581 when ds422760045 then
582 l_log_text := ' An error has occurred while attempting to change Duty Station 422760045,';
583 l_log_text := l_log_text || ' Fairfield/Delaware/Pennsylvania. You must complete a Request for ';
584 l_log_text := l_log_text || 'Personnel action to move all affected employees from this Duty Station ';
585 l_log_text := l_log_text || 'to a different Duty Station. You may obtain a listing of all employees ';
586 l_log_text := l_log_text || 'in this Duty Station by running the Location Occupancy Report.';
587 ghr_mto_int.log_message
588 (p_procedure => 'DS 422760045'
589 ,p_message => l_log_text
590 );
591 commit;
592
593 when old_not_in_opm then
594 l_log_text := 'The Old Location chosen has an associated Duty Station that is not one of the OPM mandated Duty Stations. ';
595 l_log_text := l_log_text || 'Please verify that the Old Location is correct. ';
596 l_log_text := l_log_text || 'Only OPM mandated changes can be run through the Duty Station Conversion. ';
597 ghr_mto_int.log_message
598 (p_procedure => 'Old Location'
599 ,p_message => l_log_text
600 );
601 commit;
602
603 when new_not_in_opm then
604 l_log_text := 'The New Location chosen has an associated Duty Station that is not one of the OPM mandated Duty Stations. ';
605 l_log_text := l_log_text || 'Please verify that the New Location is correct. ';
606 l_log_text := l_log_text || 'Only OPM mandated changes can be run through the Duty Station Conversion. ';
607 ghr_mto_int.log_message
608 (p_procedure => 'New Location'
609 ,p_message => l_log_text
610 );
611 commit;
612
613 when old_new_not_in_opm then
614 ghr_mto_int.log_message
615 (p_procedure => 'Old to New Combination'
616 ,p_message => 'Old Location Duty station is mapped to a wrong New Location Duty Station'
617 );
618 commit;
619
620 when pay_area_id_err then
621 l_log_text := 'This change in Locations results in a Change in Locality Pay Areas. ';
622 l_log_text := l_log_text || 'Therefore a Request for Personnel Action (RPA) must be completed for each ';
623 l_log_text := l_log_text || 'employee involved in this move. You may obtain a listing of all employees';
624 l_log_text := l_log_text || ' in this Location by running the Location Occupancy Report.';
625 l_log_text := l_log_text || ' Process a NOAC 895 to change the Locality Adjustment and Employee Location. ';
626 l_log_text := l_log_text || 'Use the Start Date the employee was first assigned to this location';
627 l_log_text := l_log_text || ', as of the effective date of the RPA. ';
628
629 ghr_mto_int.log_message
630 (p_procedure => 'Locality Adjustment Different'
631 ,p_message => l_log_text
632 );
633 commit;
634 when others then
635 rollback;
636 ghr_mto_int.log_message
637 (p_procedure => 'Conversion Failure'
638 ,p_message => l_log_text || ' Sql error : '|| sqlerrm(sqlcode)
639 );
640 commit;
641 END do_conversion;
642 END ghr_ds_jan99_pkg;
[Home] [Help]