[Home] [Help]
PACKAGE BODY: APPS.GHR_MASS_SEL_NOA
Source
1 PACKAGE BODY GHR_MASS_SEL_NOA AS
2 /* $Header: ghmasnoa.pkb 120.0.12020000.25 2013/02/20 09:16:57 utokachi noship $ */
3
4 g_package varchar2(32) DEFAULT 'GHR_MASS_SEL_NOA.';
5 g_proc varchar2(72) DEFAULT NULL;
6 l_log_text varchar2(2000);
7 l_noaerrbuf varchar2(2000) DEFAULT NULL;
8 l_mcanerrbuf varchar2(2000) DEFAULT NULL;
9 cur_business_group_id per_business_groups.business_group_id%type;
10 g_effective_date date;
11 l_noa_code ghr_mass_salaries.noa_code%type;
12 l_nte_date ghr_mass_salaries.nte_date%type;
13 l_target_barg_unit_status ghr_mass_salaries.target_bargaining_unit_status%type;
14 l_target_flsa_category ghr_mass_salaries.target_flsa_category%type;
15 l_target_occ_code ghr_mass_salaries.target_occ_code%type;
16 l_target_job_id ghr_mass_salaries.target_job_id%type;
17 l_target_agency_code_subelmt ghr_mass_salaries.target_agency_code_subelement%type;
18 l_target_duty_station_id ghr_mass_salaries.target_duty_station_id%type;
19 l_target_duty_station_code ghr_mass_salaries.target_duty_station_code%type;
20 l_p_personnel_office_id ghr_pa_requests.personnel_office_id%type;
21 l_target_functional_class ghr_mass_salaries.target_functional_class%type;--Bug# 14410401
22 -- Begin Bug# 14751973
23 l_target_duty_station_desc varchar2(150);
24 l_old_barg_unit_status ghr_mass_salaries.target_bargaining_unit_status%type;
25 l_old_flsa_category ghr_mass_salaries.target_flsa_category%type;
26 l_old_occ_code ghr_mass_salaries.target_occ_code%type;
27 l_old_functional_class ghr_mass_salaries.target_functional_class%type;
28 l_dummy varchar2(80);
29 g_mcal_flag BOOLEAN DEFAULT FALSE;
30 p_can_noa_code ghr_mass_salaries.noa_code%type;
31 -- End Bug# 14751973
32
33 PROCEDURE pr
34 (msg IN varchar2
35 , par1 IN varchar2 DEFAULT NULL
36 , par2 IN varchar2 DEFAULT NULL) IS
37 BEGIN
38 ghr_mto_int.put_line (msg|| '-'|| par1|| ' -'|| par2|| '-');
39 EXCEPTION
40 WHEN others THEN
41 hr_utility.set_location ('Error in pr '|| ' Err is '|| sqlerrm (sqlcode), 20);
42 l_noaerrbuf := 'Error in pr Sql Err is '|| sqlerrm (sqlcode);
43 RAISE mnoa_error;
44 END pr;
45
46 --Begin Bug# 14805985
47 FUNCTION chk_pa_req_already_exists
48 (p_person_id IN number
49 , p_effective_date IN date
50 , p_first_noa_code IN varchar2) RETURN boolean IS
51
52 l_name per_people_f.full_name%TYPE;
53 l_pa_request_id ghr_pa_requests.pa_request_id%TYPE;
54 l_employee_number per_people_f.employee_number%TYPE;
55
56 CURSOR csr_action_taken IS
57 SELECT pr.pa_request_id
58 , max (pa_routing_history_id) pa_routing_history_id
59 FROM ghr_pa_requests pr
60 , ghr_pa_routing_history prh
61 WHERE pr.pa_request_id = prh.pa_request_id
62 AND person_id = p_person_id
63 AND first_noa_code = p_first_noa_code
64 AND effective_date = p_effective_date
65 AND nvl (pr.first_noa_cancel_or_correct, 'X') <> ghr_history_api.g_cancel
66 GROUP BY pr.pa_request_id;
67 CURSOR csr_name IS
68 SELECT substr (pr.employee_last_name
69 || ', '
70 || pr.employee_first_name, 1
71 , 240) fname
72 FROM ghr_pa_requests pr
73 WHERE pr.pa_request_id = l_pa_request_id;
74 CURSOR pa_hist_cur
75 (p_r_hist_id IN number) IS
76 SELECT nvl (action_taken, ' ') action_taken
77 FROM ghr_pa_routing_history
78 WHERE pa_routing_history_id = p_r_hist_id;
79 l_action_taken ghr_pa_routing_history.action_taken%TYPE;
80 l_proc varchar2(72) DEFAULT g_package|| '.update_sel_flg';
81 BEGIN
82 g_proc := 'chk_pa_req_already_exists';
83 hr_utility.set_location ('Entering '|| l_proc, 6);
84 FOR v_action_taken IN csr_action_taken LOOP
85 hr_utility.set_location ('Record exists '|| l_proc, 6);
86 l_pa_request_id := v_action_taken.pa_request_id;
87
88 FOR v_name IN csr_name LOOP
89 l_name := v_name.fname;
90 EXIT;
91 END LOOP;
92
93 FOR pa_hist_rec IN pa_hist_cur (v_action_taken.pa_routing_history_id) LOOP
94 l_action_taken := pa_hist_rec.action_taken;
95
96 EXIT;
97 END LOOP;
98
99 IF p_person_id IS NOT NULL THEN
100 l_employee_number := ghr_pa_requests_pkg2.get_employee_number
101 (p_person_id => p_person_id
102 , p_effective_date => p_effective_date);
103 END IF;
104
105 IF l_action_taken <> 'CANCELED' THEN
106 hr_utility.set_location ('in Canceled action '|| l_proc, 6);
107 ghr_mto_int.log_message
108 (p_procedure => 'RPA Exists Already'
109 , p_message => 'Name: '
110 || l_name
111 || ' Emp No: '
112 || l_employee_number
113 || ' - Mass NOA Selectable : '
114 || ' RPA Exists for the given effective date ');
115
116 RETURN TRUE;
117 END IF;
118 END LOOP;
119
120 RETURN FALSE;
121 END chk_pa_req_already_exists;
122 --End Bug# 14805985
123
124 PROCEDURE update_sel_flg
125 (p_person_id IN number
126 , p_effective_date IN date) IS
127
128 l_person_extra_info_id number;
129 l_object_version_number number;
130 l_per_ei_data per_people_extra_info%ROWTYPE;
131 l_proc varchar2(72) DEFAULT g_package|| '.update_sel_flg';
132 l_ind number DEFAULT 1;
133
134 BEGIN
135 g_proc := 'update_sel_flg';
136 hr_utility.set_location ('Entering '|| l_proc, 5);
137
138 pr ('Inside '|| l_proc, to_char (p_person_id));
139 l_ind := 10;
140
141 ghr_history_fetch.fetch_peopleei
142 (p_person_id => p_person_id
143 , p_information_type => 'GHR_US_PER_MASS_ACTIONS'
144 , p_date_effective => p_effective_date
145 , p_per_ei_data => l_per_ei_data);
146
147 l_ind := 20;
148
149 l_person_extra_info_id := l_per_ei_data.person_extra_info_id;
150 l_object_version_number := l_per_ei_data.object_version_number;
151
152 IF l_person_extra_info_id IS NOT NULL THEN
153 ghr_person_extra_info_api.update_person_extra_info
154 (p_person_extra_info_id => l_person_extra_info_id
155 , p_effective_date => sysdate
156 , p_object_version_number => l_object_version_number
157 , p_pei_information3 => NULL
158 , p_pei_information4 => NULL
159 , p_pei_information5 => NULL
160 , p_pei_information10 => NULL
161 , p_pei_information_category => 'GHR_US_PER_MASS_ACTIONS');
162
163 l_ind := 30;
164 hr_utility.set_location ('Exiting '|| l_proc, 10);
165 END IF;
166
167 l_ind := 50;
168
169 pr ('Exiting '|| l_proc, to_char (p_person_id));
170 EXCEPTION
171 WHEN mnoa_error THEN
172 RAISE;
173 WHEN others THEN
174 hr_utility.set_location ('Error in '|| l_proc|| ' Err is '|| sqlerrm (sqlcode), 20);
175
176 l_noaerrbuf := 'Error in '|| l_proc|| ' at '|| to_char (l_ind)|| ' Sql Err is '|| sqlerrm (sqlcode);
177 RAISE mnoa_error;
178 END update_sel_flg;
179
180 FUNCTION check_select_flg
181 (p_person_id IN number
182 , p_action IN varchar2
183 , p_effective_date IN date
184 , p_mass_salary_id IN number
185 , p_sel_flg IN OUT NOCOPY varchar2) RETURN boolean IS
186
187 l_per_ei_data per_people_extra_info%ROWTYPE;
188 l_comments varchar2(250);
189 l_sel_flag varchar2(3);
190 l_line number DEFAULT 0;
191 l_proc varchar2(72) DEFAULT g_package|| '.check_select_flg';
192 BEGIN
193 g_proc := 'check_select_flg';
194 hr_utility.set_location ('Entering '|| l_proc, 5);
195 l_line := 5;
196
197 get_extra_info_comments (p_person_id, p_effective_date
198 , l_sel_flag, l_comments
199 , p_mass_salary_id);
200
201 IF l_comments IS NOT NULL THEN
202 IF substr (nvl (l_comments, '@#%'), 1, 3) = 'MNOA' THEN
203 ins_upd_per_extra_info (p_person_id, p_effective_date
204 , l_sel_flag, NULL
205 , p_mass_salary_id);
206 END IF;
207 END IF;
208
209 l_line := 10;
210
211 IF l_sel_flag IS NULL THEN
212 p_sel_flg := 'Y';
213 ELSE
214 p_sel_flg := l_sel_flag;
215 END IF;
216
217 l_line := 15;
218
219 IF p_action IN ('SHOW', 'REPORT') THEN
220 RETURN TRUE;
221 ELSIF p_action = 'CREATE' THEN
222 IF p_sel_flg = 'Y' THEN
223 RETURN TRUE;
224 ELSE
225 RETURN FALSE;
226 END IF;
227 END IF;
228 EXCEPTION
229 WHEN mnoa_error THEN
230 RAISE;
231 WHEN others THEN
232 hr_utility.set_location ('Error in '|| l_proc|| ' Err is '|| sqlerrm (sqlcode), 20);
233
234 l_noaerrbuf := 'Error in '|| l_proc|| ' @'|| to_char (l_line)|| ' Sql Err is '|| sqlerrm (sqlcode);
235
236 RAISE mnoa_error;
237 END check_select_flg;
238
239 PROCEDURE get_extra_info_comments
240 (p_person_id IN number
241 , p_effective_date IN date
242 , p_sel_flag IN OUT NOCOPY varchar2
243 , p_comments IN OUT NOCOPY varchar2
244 , p_mass_salary_id IN number) IS
245
246 l_per_ei_data per_people_extra_info%ROWTYPE;
247 l_proc varchar2(72) DEFAULT g_package|| '.get_extra_info_comments';
248 l_eff_date date;
249 CURSOR chk_history
250 (p_person_id IN number
251 , eff_date IN date) IS
252 SELECT information9 info9
253 , information10 info10
254 , information11 info11
255 , information17 info17
256 FROM ghr_pa_history
257 WHERE person_id = p_person_id
258 AND pa_history_id IN
259 (
260 SELECT max (pa_history_id)
261 FROM ghr_pa_history
262 WHERE person_id = p_person_id
263 AND information5 = 'GHR_US_PER_MASS_ACTIONS'
264 AND table_name = 'PER_PEOPLE_EXTRA_INFO'
265 AND effective_date = eff_date
266 GROUP BY information11
267 );
268 BEGIN
269 g_proc := 'get_extra_info_comments';
270 hr_utility.set_location ('Entering ' || l_proc, 5);
271 l_eff_date := p_effective_date;
272
273 ghr_history_fetch.fetch_peopleei
274 (p_person_id => p_person_id
275 , p_information_type => 'GHR_US_PER_MASS_ACTIONS'
276 , p_date_effective => l_eff_date
277 , p_per_ei_data => l_per_ei_data);
278
279 IF nvl (to_number (l_per_ei_data.pei_information5), hr_api.g_number)
280 <> nvl (p_mass_salary_id, hr_api.g_number) THEN
281 p_sel_flag := 'Y';
282 p_comments := NULL;
283 ELSE
284 p_sel_flag := l_per_ei_data.pei_information3;
285 p_comments := l_per_ei_data.pei_information4;
286 END IF;
287
288 IF p_sel_flag IS NOT NULL
289 AND nvl (to_number (l_per_ei_data.pei_information5), hr_api.g_number)
290 <> nvl (p_mass_salary_id, hr_api.g_number) THEN
291 FOR chk_history_rec IN chk_history
292 (p_person_id => p_person_id
293 , eff_date => l_eff_date) LOOP
294 IF chk_history_rec.info11 = p_mass_salary_id THEN
295 p_sel_flag := chk_history_rec.info9;
296 p_comments := chk_history_rec.info10;
297
298 END IF;
299 END LOOP;
300 END IF;
301 EXCEPTION
302 WHEN mnoa_error THEN
303 RAISE;
304 WHEN others THEN
305 hr_utility.set_location ('Error in '|| l_proc|| ' Err is '|| sqlerrm (sqlcode), 20);
306 l_noaerrbuf := 'Error in '|| l_proc|| ' Sql Err is '|| sqlerrm (sqlcode);
307 RAISE mnoa_error;
308 END get_extra_info_comments;
309
310 PROCEDURE refresh_ei_details
311 (p_noa_action IN ghr_pa_requests.first_noa_code%TYPE
312 , p_effective_date IN date
313 , p_person_id IN per_people_f.person_id%TYPE
314 , p_work_schedule IN ghr_pa_requests.work_schedule%TYPE
315 , p_pa_request_id IN ghr_pa_requests.pa_request_id%TYPE) IS
316
317 l_proc varchar2(72);
318 l_pa_request_extra_info_id number;
319 l_pa_request_extra_info_sh_id number;
320 l_pa_object_version_number number;
321 l_dummy varchar2(30);
322 l_information_type varchar2(40);
323 l_employment_type varchar2(5);
324 l_pei_extra_info per_people_extra_info%ROWTYPE;
325 l_pa_rei_id number;
326 l_rpa_eff_date date;
327 l_rpa_nte_date date;
328 --Bug# 14456039
329 l_rpa_position_id ghr_pa_requests.from_position_id%type;
330 l_pos_agency_code ghr_pa_requests.agency_code%type;
331 l_eit_agency ghr_pa_request_extra_info.rei_information4%type;
332 l_eit_poi ghr_pa_request_extra_info.rei_information3%type;
333 l_eit_poi_dt ghr_pa_request_extra_info.rei_information5%type;
334
335 CURSOR cur_rpa_ei
336 (p_pa_request_id IN number) IS
337 SELECT pa_request_extra_info_id
338 , object_version_number
339 FROM ghr_pa_request_extra_info
340 WHERE information_type = l_information_type
341 AND pa_request_id = p_pa_request_id;
342
343 CURSOR cur_rpa_ei_shadow
344 (p_pa_request_id IN number) IS
345 SELECT pa_request_extra_info_id
346 FROM ghr_pa_request_ei_shadow
347 WHERE information_type = l_information_type
348 AND pa_request_id = p_pa_request_id;
349
350 CURSOR cur_rpa_dtl
351 (p_pa_request_id IN number) IS
352 SELECT *
353 FROM ghr_pa_requests
354 WHERE pa_request_id = p_pa_request_id;
355
356 BEGIN
357 g_proc := 'refresh_ei_details';
358 l_proc := g_proc|| 'refresh_ei_details';
359 hr_utility.set_location ('Entering '|| l_proc, 5);
360
361 IF p_noa_action IN ('280', '292') THEN
362 l_information_type := 'GHR_US_PAR_RETURN_TO_DUTY';
363 IF p_work_schedule IN ('F', 'G') THEN
364 l_employment_type := '1';
365 ELSIF p_work_schedule IN ('P', 'Q', 'S', 'T') THEN
366 l_employment_type := '2';
367 ELSIF p_work_schedule IN ('I', 'J') THEN
368 l_employment_type := '4';
369 END IF;
370 ELSIF p_noa_action IN ('430','460','471','472') THEN
371 hr_utility.set_location ('Entering '|| l_proc||p_noa_action, 51);
372 IF p_noa_action IN ('430','460') THEN
373 l_information_type := 'GHR_US_PAR_NON_PAY_DUTY_STATUS';
374 ELSIF p_noa_action IN ('471','472') THEN
375 l_information_type := 'GHR_US_PAR_LWOP_INFO';
376 END IF;
377 FOR l_cur_rpa_dtl IN cur_rpa_dtl(p_pa_request_id) LOOP
378 l_rpa_eff_date := l_cur_rpa_dtl.effective_date;
379 l_rpa_nte_date := fnd_date.canonical_to_date(l_cur_rpa_dtl.first_noa_information1);
380 END LOOP;
381 --Begin Bug# 14403219
382 IF p_noa_action IN ('471') THEN
383 ghr_history_fetch.fetch_peopleei
384 (p_person_id => p_person_id
385 , p_information_type => 'GHR_US_PER_GROUP1'
386 , p_date_effective => p_effective_date - 1
387 , p_per_ei_data => l_pei_extra_info);
388
389 l_employment_type := l_pei_extra_info.pei_information4;
390 ELSIF p_noa_action IN ('430') THEN
391 --bug# 14652133, removed if conditions.
392 l_employment_type := 'F';
393 ELSE
394 --End Bug# 14403219
395 IF (l_rpa_nte_date-l_rpa_eff_date)<15 THEN
396 l_employment_type := 'S';
397 ELSIF (l_rpa_nte_date-l_rpa_eff_date)<=30 THEN
398 l_employment_type := 'T';
399 ELSIF (l_rpa_nte_date-l_rpa_eff_date)>30 THEN
400 l_employment_type := 'F';
401 ELSE
402 ghr_history_fetch.fetch_peopleei
403 (p_person_id => p_person_id
404 , p_information_type => 'GHR_US_PER_GROUP1'
405 , p_date_effective => p_effective_date - 1
406 , p_per_ei_data => l_pei_extra_info);
407
408 l_employment_type := l_pei_extra_info.pei_information4;
409 END IF;
410 END IF;
411 ELSIF p_noa_action IN ('800') and l_target_agency_code_subelmt IS NOT NULL THEN
412 l_information_type := 'GHR_US_PAR_CHG_DATA_ELEMENT';
413 l_eit_agency := l_target_agency_code_subelmt;
414 l_eit_poi := l_p_personnel_office_id;
415 l_eit_poi_dt := fnd_date.date_to_canonical(p_effective_date);
416 --Bug# 14456039
417 FOR l_cur_rpa_dtl IN cur_rpa_dtl(p_pa_request_id) LOOP
418 l_rpa_position_id := l_cur_rpa_dtl.from_position_id;
419 END LOOP;
420 l_pos_agency_code := ghr_api.get_position_agency_code_pos
421 (p_position_id => l_rpa_position_id
422 ,p_business_group_id => fnd_profile.value ('PER_BUSINESS_GROUP_ID')
423 ,p_effective_date => p_effective_date
424 );
425
426 --End Bug# 14456039
427 END IF;
428
429 FOR cur_rpa_ei_rec IN cur_rpa_ei (p_pa_request_id) LOOP
430 hr_utility.set_location (l_proc, 10);
431 l_pa_request_extra_info_id := cur_rpa_ei_rec.pa_request_extra_info_id;
432 l_pa_object_version_number := cur_rpa_ei_rec.object_version_number;
433 EXIT;
434 END LOOP;
435
436 FOR cur_rpa_ei_shadow_rec IN cur_rpa_ei_shadow (p_pa_request_id) LOOP
437 hr_utility.set_location (l_proc, 11);
438 l_pa_request_extra_info_sh_id := cur_rpa_ei_shadow_rec.pa_request_extra_info_id;
439 EXIT;
440 END LOOP;
441
442 hr_utility.set_location (l_proc, 40);
443
444 IF l_pa_request_extra_info_id IS NULL THEN
445 hr_utility.set_location (l_proc, 45);
446
447 IF p_noa_action IN ('471','472', '460','430') THEN
448 hr_utility.set_location (l_proc|| p_noa_action, 12);
449
450 ghr_par_extra_info_api.create_pa_request_extra_info
451 (p_validate => FALSE
452 , p_pa_request_id => p_pa_request_id
453 , p_information_type => l_information_type
454 , p_rei_information_category => l_information_type
455 , p_rei_information3 => l_employment_type
456 , p_pa_request_extra_info_id => l_pa_rei_id
457 , p_object_version_number => l_dummy);
458 ELSIF p_noa_action IN ('280', '292') THEN
459 hr_utility.set_location (l_proc|| p_noa_action, 13);
460
461 ghr_par_extra_info_api.create_pa_request_extra_info
462 (p_validate => FALSE
463 , p_pa_request_id => p_pa_request_id
464 , p_information_type => l_information_type
465 , p_rei_information_category => l_information_type
466 , p_rei_information8 => l_employment_type
467 , p_pa_request_extra_info_id => l_dummy
468 , p_object_version_number => l_dummy);
469 ELSIF p_noa_action IN ('800') AND l_target_agency_code_subelmt IS NOT NULL THEN
470 hr_utility.set_location (l_proc|| p_noa_action, 122);
471
472 ghr_par_extra_info_api.create_pa_request_extra_info
473 (p_validate => FALSE
474 , p_pa_request_id => p_pa_request_id
475 , p_information_type => l_information_type
476 , p_rei_information_category => l_information_type
477 , p_rei_information3 => l_eit_poi
478 , p_rei_information4 => l_eit_agency
479 , p_rei_information5 => l_eit_poi_dt
480 , p_pa_request_extra_info_id => l_dummy
481 , p_object_version_number => l_dummy);
482 END IF;
483 ELSE
484 hr_utility.set_location (l_proc|| p_noa_action, 14);
485
486 IF p_noa_action IN ('471','472', '460','430') THEN
487 hr_utility.set_location (l_proc|| p_noa_action, 15);
488
489 ghr_par_extra_info_api.update_pa_request_extra_info
490 (p_validate => FALSE
491 , p_rei_information3 => l_employment_type
492 , p_pa_request_extra_info_id => l_pa_request_extra_info_id
493 , p_object_version_number => l_pa_object_version_number);
494 ELSIF p_noa_action IN ('280', '292') THEN
495 hr_utility.set_location (l_proc|| p_noa_action, 16);
496
497 ghr_par_extra_info_api.update_pa_request_extra_info
498 (p_validate => FALSE
499 , p_rei_information8 => l_employment_type
500 , p_pa_request_extra_info_id => l_pa_request_extra_info_id
501 , p_object_version_number => l_pa_object_version_number);
502 ELSIF p_noa_action IN ('800') AND l_target_agency_code_subelmt IS NOT NULL THEN
503 hr_utility.set_location (l_proc|| p_noa_action, 16);
504
505 ghr_par_extra_info_api.update_pa_request_extra_info
506 (p_validate => FALSE
507 , p_rei_information3 => l_eit_poi
508 , p_rei_information4 => l_eit_agency
509 , p_rei_information5 => l_eit_poi_dt
510 , p_pa_request_extra_info_id => l_pa_request_extra_info_id
511 , p_object_version_number => l_pa_object_version_number);
512 END IF;
513 END IF;
514
515 IF l_pa_request_extra_info_sh_id IS NULL THEN
516 hr_utility.set_location (l_proc, 17);
517
518 IF p_noa_action IN ('471','472', '460','430') THEN
519 INSERT
520 INTO ghr_pa_request_ei_shadow (pa_request_extra_info_id
521 , pa_request_id
522 , information_type
523 , rei_information3)
524 VALUES (ghr_pa_request_extra_info_s.nextval
525 , p_pa_request_id
526 , l_information_type
527 , l_employment_type);
528 ELSIF p_noa_action IN ('280', '292') THEN
529 INSERT
530 INTO ghr_pa_request_ei_shadow (pa_request_extra_info_id
531 , pa_request_id
532 , information_type
533 , rei_information8)
534 VALUES (ghr_pa_request_extra_info_s.nextval
535 , p_pa_request_id
536 , l_information_type
537 , l_employment_type);
538 ELSIF p_noa_action IN ('800') AND l_target_agency_code_subelmt IS NOT NULL THEN
539 INSERT
540 INTO ghr_pa_request_ei_shadow (pa_request_extra_info_id
541 , pa_request_id
542 , information_type
543 , rei_information3
544 , rei_information4
545 , rei_information5)
546 VALUES (ghr_pa_request_extra_info_s.nextval
547 , p_pa_request_id
548 , l_information_type
549 , l_eit_poi
550 ,l_pos_agency_code
551 ,l_eit_poi_dt);
552 END IF;
553 ELSE
554 hr_utility.set_location (l_proc, 18);
555
556 IF p_noa_action IN ('471','472', '460','430') THEN
557 UPDATE ghr_pa_request_ei_shadow
558 SET rei_information3 = l_employment_type
559 WHERE pa_request_extra_info_id = l_pa_request_extra_info_sh_id;
560 ELSIF p_noa_action IN ('280', '292') THEN
561 UPDATE ghr_pa_request_ei_shadow
562 SET rei_information8 = l_employment_type
563 WHERE pa_request_extra_info_id = l_pa_request_extra_info_sh_id;
564 ELSIF p_noa_action IN ('800') AND l_target_agency_code_subelmt IS NOT NULL THEN
565 UPDATE ghr_pa_request_ei_shadow
566 SET rei_information3 = l_eit_poi
567 ,rei_information4 = l_pos_agency_code
568 ,rei_information5 = l_eit_poi_dt
569 WHERE pa_request_extra_info_id = l_pa_request_extra_info_sh_id;
570 END IF;
571 END IF;
572 EXCEPTION
573 WHEN others THEN
574 hr_utility.set_location ('Error in ghr_par_extra info.create pa req'|| ' Sql Err is '|| sqlerrm (sqlcode)|| l_proc, 60);
575 RAISE mnoa_error;
576 END refresh_ei_details;
577
578 PROCEDURE fetch_and_validate_emp
579 (p_action IN varchar2
580 , p_mass_salary_id IN number
581 , p_mass_salary_name IN varchar2
582 , p_full_name IN per_people_f.full_name%TYPE
583 , p_national_identifier IN per_people_f.national_identifier%TYPE
584 , p_employee_number IN per_people_f.employee_number%TYPE
585 , p_assignment_id IN per_assignments_f.assignment_id%TYPE
586 , p_person_id IN per_assignments_f.person_id%TYPE
587 , p_position_id IN per_assignments_f.position_id%TYPE
588 , p_grade_id IN per_assignments_f.grade_id%TYPE
589 , p_business_group_id IN per_assignments_f.business_group_id%TYPE
590 , p_location_id IN per_assignments_f.location_id%TYPE
591 , p_organization_id IN per_assignments_f.organization_id%TYPE
592 , p_mnoa_organization_id IN per_assignments_f.organization_id%TYPE
593 , p_mnoa_duty_station_id IN ghr_duty_stations_f.duty_station_id%TYPE
594 , p_mnoa_personnel_office_id IN varchar2
595 , p_mnoa_agency_code_subelement IN varchar2
596 , p_mnoa_job_id IN number default NULL
597 , p_mnoa_bargaining_unit IN varchar2 default NULL
598 , p_mnoa_flsa_category IN varchar2 default NULL
599 , p_personnel_office_id OUT NOCOPY varchar2
600 , p_org_structure_id OUT NOCOPY varchar2
601 , p_position_title OUT NOCOPY varchar2
602 , p_position_number OUT NOCOPY varchar2
603 , p_position_seq_no OUT NOCOPY varchar2
604 , p_subelem_code OUT NOCOPY varchar2
605 , p_duty_station_id OUT NOCOPY ghr_duty_stations_f.duty_station_id%TYPE
606 , p_tenure OUT NOCOPY varchar2
607 , p_annuitant_indicator OUT NOCOPY varchar2
608 , p_pay_rate_determinant OUT NOCOPY varchar2
609 , p_work_schedule OUT NOCOPY varchar2
610 , p_part_time_hour OUT NOCOPY varchar2
611 , p_to_grade_id OUT NOCOPY per_assignments_f.grade_id%TYPE
612 , p_pay_plan OUT NOCOPY varchar2
613 , p_to_pay_plan OUT NOCOPY varchar2
614 , p_pay_table_id OUT NOCOPY number
615 , p_grade_or_level OUT NOCOPY varchar2
616 , p_to_grade_or_level OUT NOCOPY varchar2
617 , p_step_or_rate OUT NOCOPY varchar2
618 , p_pay_basis OUT NOCOPY varchar2
619 , p_elig_flag OUT NOCOPY boolean) IS
620
621 CURSOR mnoa_dtl_cur
622 (cur_pay_plan IN varchar2
623 , cur_prd IN varchar2) IS
624 SELECT count (*) cnt
625 FROM ghr_mass_salary_criteria
626 WHERE mass_salary_id = p_mass_salary_id
627 AND pay_plan = cur_pay_plan
628 AND pay_rate_determinant = cur_prd;
629
630 l_row_cnt number DEFAULT 0;
631 l_pos_grp1_rec per_position_extra_info%ROWTYPE;
632 l_assignment_id per_assignments_f.assignment_id%TYPE;
633 l_person_id per_assignments_f.person_id%TYPE;
634 l_position_id per_assignments_f.position_id%TYPE;
635 l_grade_id per_assignments_f.grade_id%TYPE;
636 l_business_group_id per_assignments_f.business_group_id%TYPE;
637 l_location_id per_assignments_f.location_id%TYPE;
638 l_tenure varchar2(35);
639 l_annuitant_indicator varchar2(35);
640 l_pay_rate_determinant varchar2(35);
641 l_work_schedule varchar2(35);
642 l_part_time_hour varchar2(35);
643 l_pay_table_id number;
644 l_pay_plan varchar2(30);
645 l_grade_or_level varchar2(30);
646 l_to_grade_id number;
647 l_to_pay_plan varchar2(30);
648 l_to_grade_or_level varchar2(30);
649 l_step_or_rate varchar2(30);
650 l_pay_basis varchar2(30);
651 l_duty_station_id number;
652 l_effective_date date;
653 l_personnel_office_id varchar2(300);
654 l_org_structure_id varchar2(300);
655 l_sub_element_code varchar2(300);
656 l_position_title varchar2(300);
657 l_position_number varchar2(20);
658 l_position_seq_no varchar2(20);
659 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
660 l_occ_series ghr_pa_requests.from_occ_code%type;
661 l_mnoa_occ_series ghr_pa_requests.from_occ_code%type;
662 l_bargaining_unit ghr_pa_requests.bargaining_unit_status%type;
663 l_flsa_category ghr_pa_requests.flsa_category%type;
664 l_fetch_poid_data boolean DEFAULT FALSE;
665 l_fetch_ds_data boolean DEFAULT FALSE;
666 l_fetch_agency_data boolean DEFAULT FALSE;
667 l_fetch_occ_data boolean DEFAULT FALSE;
668 l_fetch_flsa_data boolean DEFAULT FALSE;
669 l_fetch_bu_data boolean DEFAULT FALSE;
670 init_elig_flag boolean DEFAULT FALSE;
671 l_proc varchar2(72) DEFAULT g_package|| '.fetch_and_validate_emp';
672 BEGIN
673 g_proc := 'fetch_and_validate_emp';
674 hr_utility.set_location ('Entering '|| l_proc, 5);
675 l_assignment_id := p_assignment_id;
676 l_position_id := p_position_id;
677 l_grade_id := p_grade_id;
678 l_business_group_id := p_business_group_id;
679 l_location_id := p_location_id;
680 l_effective_date := g_effective_date;
681
682 IF p_mnoa_organization_id IS NOT NULL
683 OR p_mnoa_duty_station_id IS NOT NULL
684 OR p_mnoa_personnel_office_id IS NOT NULL
685 OR p_mnoa_agency_code_subelement IS NOT NULL
686 OR p_mnoa_job_id IS NOT NULL
687 OR p_mnoa_bargaining_unit IS NOT NULL
688 OR p_mnoa_flsa_category IS NOT NULL THEN
689
690 hr_utility.set_location ('The location id is:'|| l_location_id, 12345);
691 hr_utility.set_location ('mnoa Org ID:'|| p_mnoa_organization_id, 1111);
692 hr_utility.set_location ('Org ID:'|| p_organization_id, 2222);
693
694 IF nvl (p_mnoa_organization_id, p_organization_id)
695 = p_organization_id THEN
696 hr_utility.set_location ('Org ID PASS', 10);
697
698 IF p_mnoa_personnel_office_id IS NOT NULL THEN
699 hr_utility.set_location ('POID CHECK', 15);
700 ghr_msl_pkg.get_pos_grp1_ddf (l_position_id, l_effective_date
701 , l_pos_grp1_rec);
702 l_personnel_office_id := l_pos_grp1_rec.poei_information3;
703 l_org_structure_id := l_pos_grp1_rec.poei_information5;
704 l_fetch_poid_data := TRUE;
705 END IF;
706
707 IF (p_mnoa_personnel_office_id = l_personnel_office_id)
708 OR NOT (l_fetch_poid_data) THEN
709 hr_utility.set_location ('POID PASS', 20);
710 IF p_mnoa_agency_code_subelement IS NOT NULL THEN
711 hr_utility.set_location ('Agency CHECK', 25);
712 ghr_msl_pkg.get_sub_element_code_pos_title (l_position_id, p_person_id
713 , l_business_group_id, l_assignment_id
714 , l_effective_date, l_sub_element_code
715 , l_position_title, l_position_number
716 , l_position_seq_no);
717
718 l_fetch_agency_data := TRUE;
719 END IF;
720
721 IF (substr (p_mnoa_agency_code_subelement, 1, 2)
722 = substr (l_sub_element_code, 1, 2)
723 AND nvl (substr (p_mnoa_agency_code_subelement, 3, 2), substr (l_sub_element_code, 3, 2))
724 = substr (l_sub_element_code, 3, 2))
725 OR NOT (l_fetch_agency_data) THEN
726 hr_utility.set_location ('Agency PASS', 30);
727
728 IF p_mnoa_duty_station_id IS NOT NULL THEN
729 hr_utility.set_location ('DS CHECK', 35);
730 ghr_pa_requests_pkg.get_sf52_loc_ddf_details
731 (p_location_id => l_location_id
732 , p_duty_station_id => l_duty_station_id);
733 l_fetch_ds_data := TRUE;
734 END IF;
735
736 IF (p_mnoa_duty_station_id = l_duty_station_id)
737 OR NOT (l_fetch_ds_data) THEN
738 hr_utility.set_location ('DS PASS', 40);
739 IF p_mnoa_job_id IS NOT NULL
740 OR p_mnoa_bargaining_unit IS NOT NULL
741 OR p_mnoa_flsa_category IS NOT NULL THEN
742 IF p_mnoa_job_id IS NOT NULL THEN
743 hr_utility.set_location ('OCC CODE CHECK', 35);
744
745 l_mnoa_occ_series := ghr_api.get_job_occ_series_job
746 (p_job_id => p_mnoa_job_id
747 ,p_business_group_id => l_business_group_id);
748 l_occ_series := ghr_pa_requests_pkg.get_occ_series(l_position_id,l_effective_date,l_business_group_id);
749 l_fetch_occ_data := TRUE;
750 END IF;
751 IF (l_mnoa_occ_series = l_occ_series) OR NOT(l_fetch_occ_data) THEN
752 hr_utility.set_location ('OCC PASS', 35);
753 IF p_mnoa_bargaining_unit IS NOT NULL THEN
754 hr_utility.set_location ('Bargaining Unit CHECK', 35);
755 l_bargaining_unit:= ghr_pa_requests_pkg.get_bargaining_unit(p_position_id,l_effective_date);
756 l_fetch_bu_data := TRUE;
757 END IF;
758 IF (p_mnoa_bargaining_unit = l_bargaining_unit) OR NOT(l_fetch_bu_data) THEN
759 hr_utility.set_location ('BU PASS', 30);
760 IF p_mnoa_flsa_category IS NOT NULL THEN
761 hr_utility.set_location ('FLSA Category CHECK', 35);
762 l_flsa_category:= ghr_pa_requests_pkg.get_flsa_category(p_position_id,l_effective_date);
763 l_fetch_flsa_data := TRUE;
764 END IF;
765 IF (p_mnoa_flsa_category = l_flsa_category) OR NOT(l_fetch_flsa_data) THEN
766 hr_utility.set_location ('FLSA PASS', 40);
767 init_elig_flag := TRUE;
768 ELSE
769 hr_utility.set_location ('FLSA FAIL', 40);
770 init_elig_flag := FALSE;
771 END IF;
772 ELSE --IF (p_mnoa_bargaining_unit = l_bargaining_unit)
773 hr_utility.set_location ('BU FAIL', 55);
774 init_elig_flag := FALSE;
775 END IF;--IF (p_mnoa_bargaining_unit = l_bargaining_unit)
776 ELSE--IF (l_mnoa_occ_series = l_occ_series)
777 hr_utility.set_location ('OCC FAIL', 35);
778 init_elig_flag := FALSE;
779 END IF;--IF (l_mnoa_occ_series = l_occ_series)
780 ELSE --IF p_mnoa_job_id IS NOT NULL
781 hr_utility.set_location ('No Additional Selection CRITERIA', 85);
782 init_elig_flag := TRUE;
783 END IF; ---IF p_mnoa_job_id IS NOT NULL
784
785 ELSE
786 hr_utility.set_location ('DS FAIL', 45);
787 init_elig_flag := FALSE;
788 END IF;
789 ELSE --IF (substr (p_mnoa_agency_code_subelement, 1, 2)
790 hr_utility.set_location ('Agency FAIL', 55);
791 init_elig_flag := FALSE;
792 END IF; --IF (substr (p_mnoa_agency_code_subelement, 1, 2)
793 ELSE --IF (p_mnoa_personnel_office_id = l_personnel_office_id)
794 hr_utility.set_location ('POID FAIL', 65);
795 init_elig_flag := FALSE;
796 END IF; --IF (p_mnoa_personnel_office_id = l_personnel_office_id)
797 ELSE --IF nvl (p_mnoa_organization_id, p_organization_id)
798 hr_utility.set_location ('Org FAIL', 75);
799 init_elig_flag := FALSE;
800 END IF; --IF nvl (p_mnoa_organization_id, p_organization_id)
801 ELSE
802 hr_utility.set_location ('No INIT CRITERIA', 85);
803 init_elig_flag := TRUE;
804 END IF;-- IF p_mnoa_organization_id IS NOT NULL
805
806 IF init_elig_flag THEN
807 --Begin Bug# 14805985
808 IF NOT(chk_pa_req_already_exists
809 (p_person_id => p_person_id,
810 p_effective_date => l_effective_date,
811 p_first_noa_code => l_noa_code
812 )) then
813 --End Bug# 14805985
814 IF NOT l_fetch_ds_data THEN
815 ghr_pa_requests_pkg.get_sf52_loc_ddf_details
816 (p_location_id => l_location_id
817 , p_duty_station_id => l_duty_station_id);
818 END IF;
819
820 ghr_pa_requests_pkg.get_sf52_asg_ddf_details (l_assignment_id, l_effective_date
821 , l_tenure, l_annuitant_indicator
822 , l_pay_rate_determinant, l_work_schedule
823 , l_part_time_hour);
824
825 IF NOT l_fetch_agency_data THEN
826 ghr_msl_pkg.get_sub_element_code_pos_title (l_position_id, p_person_id
827 , l_business_group_id, l_assignment_id
828 , l_effective_date, l_sub_element_code
829 , l_position_title, l_position_number
830 , l_position_seq_no);
831 END IF;
832 --Begin Bug# 14726863
833 IF NOT l_fetch_poid_data THEN
834 ghr_msl_pkg.get_pos_grp1_ddf (l_position_id, l_effective_date, l_pos_grp1_rec);
835 l_personnel_office_id := l_pos_grp1_rec.poei_information3;
836 END IF;
837 --End Bug# 14726863
838 BEGIN
839 ghr_msl_pkg.get_pay_plan_and_table_id (l_pay_rate_determinant, p_person_id
840 , l_position_id, l_effective_date
841 , l_grade_id, l_to_grade_id
842 , l_assignment_id, 'SHOW'
843 , l_pay_plan, l_to_pay_plan
844 , l_pay_table_id, l_grade_or_level
845 , l_to_grade_or_level, l_step_or_rate
846 , l_pay_basis);
847 EXCEPTION
848 WHEN others THEN
849 hr_utility.set_location ('WHEN OTHERS of get_pay_plan_and_table_id ', 999);
850 END;
851
852 p_personnel_office_id := l_personnel_office_id;
853 p_org_structure_id := l_org_structure_id;
854 p_position_title := l_position_title;
855 p_position_number := l_position_number;
856 p_position_seq_no := l_position_seq_no;
857 p_subelem_code := l_sub_element_code;
858 p_duty_station_id := l_duty_station_id;
859 p_tenure := l_tenure;
860 p_annuitant_indicator := l_annuitant_indicator;
861 p_pay_rate_determinant := l_pay_rate_determinant;
862 p_work_schedule := l_work_schedule;
863 p_part_time_hour := l_part_time_hour;
864 p_to_grade_id := l_to_grade_id;
865 p_pay_plan := l_pay_plan;
866 p_to_pay_plan := l_to_pay_plan;
867 p_pay_table_id := l_pay_table_id;
868 p_grade_or_level := l_grade_or_level;
869 p_to_grade_or_level := l_to_grade_or_level;
870 p_step_or_rate := l_step_or_rate;
871 p_pay_basis := l_pay_basis;
872 p_elig_flag := init_elig_flag;
873 --Begin bug# 14805985
874 ELSE
875 p_elig_flag :=FALSE;
876 END IF;
877 --End bug# 14805985
878 END IF;--IF init_elig_flag THEN
879 hr_utility.set_location ('Leaving '|| l_proc, 184);
880 EXCEPTION
881 WHEN others THEN
882 hr_utility.set_location ('WHEN OTHERS'|| l_proc, 185);
883 RAISE;
884 END fetch_and_validate_emp;
885
886 PROCEDURE create_mass_noa_prev
887 (p_effective_date IN date
888 , p_date_of_birth IN date
889 , p_full_name IN varchar2
890 , p_national_identifier IN varchar2
891 , p_employee_number IN varchar2
892 , p_duty_station_id IN varchar2
893 , p_duty_station_code IN varchar2
894 , p_duty_station_desc IN varchar2
895 , p_personnel_office_id IN varchar2
896 , p_basic_pay IN number
897 , p_new_basic_pay IN number
898 , p_adj_basic_pay IN number
899 , p_new_adj_basic_pay IN number
900 , p_old_loc_adj IN number
901 , p_new_loc_adj IN number
902 , p_tot_old_sal IN number
903 , p_tot_new_sal IN number
904 , p_position_id IN number
905 , p_position_title IN varchar2
906 , p_position_number IN varchar2
907 , p_position_seq_no IN varchar2
908 , p_agency_sub_element_code IN varchar2
909 , p_person_id IN number
910 , p_mass_salary_id IN number
911 , p_sel_flg IN varchar2
912 , p_action IN varchar2
913 , p_assignment_id IN number
914 , p_organization_name IN varchar2
915 , p_to_agency_code IN varchar2
916 , p_to_duty_station_id IN varchar2
917 , p_to_duty_station_code IN varchar2
918 , p_to_duty_station_desc IN varchar2
919 , p_occ_code IN varchar2
920 , p_to_occ_code IN varchar2
921 , p_from_bargaining_unit_status IN varchar2
922 , p_to_bargaining_unit_status IN varchar2
923 , p_from_flsa_category IN varchar2
924 , p_to_flsa_category IN varchar2
925 , p_from_functional_class IN varchar2
926 , p_to_functional_class IN varchar2
927 , p_work_schedule IN varchar2) IS
928 l_proc varchar2(72) DEFAULT g_package
929 || '.create_mass_noa_prev';
930
931 l_cust_rec ghr_mass_act_custom.ghr_mass_custom_out_rec_type;
932 l_cust_in_rec ghr_mass_act_custom.ghr_mass_custom_in_rec_type;
933 l_poi_desc varchar2(80);
934 l_asg_extra_info_rec per_assignment_extra_info%rowtype;
935 l_pei_extra_info per_people_extra_info%ROWTYPE;
936 l_old_assignment_status varchar2(80);
937 l_new_assignment_status varchar2(80);
938 l_old_type_of_employment varchar2(30);
939 l_new_type_of_employment varchar2(30);
940 l_old_pay_userra_status varchar2(30);
941 l_new_pay_userra_status varchar2(30);
942 l_temp_noa_code ghr_nature_of_actions.code%type;
943 l_furlough_indicator varchar2(30);
944 l_old_lwop_nte_start_date date;
945 l_new_lwop_nte_start_date date;
946 l_old_lwop_nte date;
947 l_new_lwop_nte date;
948 l_old_furlough_nte_start_date date;
949 l_new_furlough_nte_start_date date;
950 l_old_furlough_nte date;
951 l_new_furlough_nte date;
952 l_new_basic_pay number;
953 l_new_adj_basic_pay number;
954 l_new_loc_adj number;
955 l_tot_new_sal number;
956
957 cursor c_user_status is
958 select ast.user_status
959 from
960 per_assignment_status_types ast,
961 per_all_assignments_f asg
962 where asg.assignment_id = p_assignment_id
963 and ast.assignment_status_type_id = asg.assignment_status_type_id
964 and p_effective_date
965 between asg.effective_start_date
966 and asg.effective_end_date;
967
968 BEGIN
969 g_proc := 'create_mass_noa_prev';
970
971 hr_utility.set_location ('Entering '
972 || l_proc, 5);
973 l_temp_noa_code := l_noa_code;
974 l_poi_desc := ghr_mre_pkg.get_poi_name (p_personnel_office_id);
975
976 BEGIN
977 l_cust_in_rec.person_id := p_person_id;
978
979 l_cust_in_rec.position_id := p_position_id;
980
981 l_cust_in_rec.assignment_id := p_assignment_id;
982
983 l_cust_in_rec.national_identifier := p_national_identifier;
984
985 l_cust_in_rec.mass_action_type := 'MNOA';
986
987 l_cust_in_rec.mass_action_id := p_mass_salary_id;
988
989 l_cust_in_rec.effective_date := p_effective_date;
990
991 ghr_mass_act_custom.pre_insert
992 (p_cust_in_rec => l_cust_in_rec
993 , p_cust_rec => l_cust_rec );
994 EXCEPTION
995 WHEN others THEN
996 hr_utility.set_location ('Error in Mass Act Custom '
997 || 'Err is '
998 || sqlerrm (sqlcode), 20);
999
1000 l_noaerrbuf := 'Error in Mass Act Custom '
1001 || 'Err is '
1002 || sqlerrm (sqlcode);
1003
1004 RAISE mnoa_error;
1005 END;
1006 IF g_mcal_flag THEN
1007 l_temp_noa_code := p_can_noa_code;
1008 ELSE
1009 ghr_history_fetch.fetch_asgei
1010 (p_assignment_id => p_assignment_id,
1011 p_information_type => 'GHR_US_ASG_NON_SF52',
1012 p_date_effective => p_effective_date,
1013 p_asg_ei_data => l_asg_extra_info_rec
1014 );
1015 l_old_pay_userra_status := l_asg_extra_info_rec.aei_information14;
1016 l_furlough_indicator := l_asg_extra_info_rec.aei_information15;
1017 l_asg_extra_info_rec := NULL;
1018
1019 IF l_noa_code IN ('280','292','471') THEN
1020 l_new_pay_userra_status := 'P';
1021 ELSIF l_noa_code IN ('430') THEN
1022 l_new_pay_userra_status := 'O';
1023 ELSIF l_noa_code IN ('460','773') THEN
1024 l_new_pay_userra_status := 'L';
1025 ELSIF l_noa_code IN ('472','772') THEN
1026 l_new_pay_userra_status := 'G';
1027 END IF;
1028
1029 IF l_noa_code IN ('280','292') THEN
1030 l_new_assignment_status := 'Active Appointment';
1031 ELSIF l_noa_code IN ('471') THEN
1032 l_new_assignment_status := 'Furlough';
1033 ELSIF l_noa_code IN ('430') THEN
1034 l_new_assignment_status := 'Non Pay';
1035 ELSIF l_noa_code IN ('460','773') THEN
1036 l_new_assignment_status := 'Leave Without Pay NTE';
1037 ELSIF l_noa_code IN ('472','772') THEN
1038 l_new_assignment_status := 'Furlough NTE';
1039 END IF;
1040
1041
1042 for asg_stat_rec in c_user_status loop
1043 l_old_assignment_status := asg_stat_rec.user_status;
1044 end loop;
1045
1046 ghr_history_fetch.fetch_peopleei
1047 (p_person_id => p_person_id
1048 , p_information_type => 'GHR_US_PER_GROUP1'
1049 , p_date_effective => p_effective_date
1050 , p_per_ei_data => l_pei_extra_info);
1051
1052 l_old_type_of_employment := l_pei_extra_info.pei_information4;
1053
1054 IF l_noa_code IN ('280', '292') THEN
1055 IF p_work_schedule IN ('F', 'G') THEN
1056 l_new_type_of_employment := '1';
1057 ELSIF p_work_schedule IN ('P', 'Q', 'S', 'T') THEN
1058 l_new_type_of_employment := '2';
1059 ELSIF p_work_schedule IN ('I', 'J') THEN
1060 l_new_type_of_employment := '4';
1061 END IF;
1062 ELSIF l_noa_code IN ('471') THEN
1063 l_new_type_of_employment := l_old_type_of_employment;
1064 ELSIF l_noa_code IN ('430') THEN
1065 l_new_type_of_employment := 'F';
1066 ELSIF l_noa_code IN ('460', '472') THEN
1067 IF (l_nte_date - p_effective_date) < 15 THEN
1068 l_new_type_of_employment := 'S';
1069 ELSIF (l_nte_date - p_effective_date) <= 30 THEN
1070 l_new_type_of_employment := 'T';
1071 ELSIF (l_nte_date - p_effective_date) > 30 THEN
1072 l_new_type_of_employment := 'F';
1073 END IF;
1074 END IF;
1075 IF l_noa_code NOT IN ('792','800') THEN
1076
1077 ghr_history_fetch.fetch_asgei
1078 (p_assignment_id => p_assignment_id,
1079 p_information_type => 'GHR_US_ASG_NTE_DATES',
1080 p_date_effective => p_effective_date,
1081 p_asg_ei_data => l_asg_extra_info_rec
1082 );
1083
1084 l_old_furlough_nte_start_date := fnd_date.canonical_to_date(l_asg_extra_info_rec.aei_information9);
1085 l_old_furlough_nte := fnd_date.canonical_to_date(l_asg_extra_info_rec.aei_information10);
1086 l_old_lwop_nte_start_date := fnd_date.canonical_to_date(l_asg_extra_info_rec.aei_information5);
1087 l_old_lwop_nte := fnd_date.canonical_to_date(l_asg_extra_info_rec.aei_information6);
1088 l_asg_extra_info_rec := NULL;
1089 IF l_noa_code = '460' THEN
1090 l_new_lwop_nte_start_date := p_effective_date;
1091 l_new_lwop_nte := l_nte_date;
1092 ELSIF l_noa_code = '472' THEN
1093 l_new_furlough_nte_start_date := p_effective_date;
1094 l_new_furlough_nte := l_nte_date;
1095 ELSIF l_noa_code = '772' THEN
1096 l_new_furlough_nte_start_date := l_old_furlough_nte_start_date;
1097 l_new_furlough_nte := l_nte_date;
1098 ELSIF l_noa_code = '773' THEN
1099 l_new_lwop_nte_start_date := l_old_lwop_nte_start_date;
1100 l_new_lwop_nte := l_nte_date;
1101 END IF;
1102 END IF;
1103 IF l_noa_code = '792' THEN
1104 IF (ghr_pay_calc.get_lpa_percentage (p_duty_station_id, p_effective_date)
1105 <> ghr_pay_calc.get_lpa_percentage (p_to_duty_station_id, p_effective_date)) THEN
1106 l_temp_noa_code := '894';
1107 ELSE
1108 l_temp_noa_code := l_noa_code;
1109 END IF;
1110 ELSE
1111 l_temp_noa_code := l_noa_code;
1112 END IF;
1113 IF l_noa_code NOT IN ('430','460','471','472') THEN
1114 IF p_new_basic_pay IS NULL THEN
1115 l_new_basic_pay := p_basic_pay;
1116 ELSE
1117 l_new_basic_pay := p_new_basic_pay;
1118 END IF;
1119 IF p_new_adj_basic_pay IS NULL THEN
1120 l_new_adj_basic_pay := p_adj_basic_pay;
1121 ELSE
1122 l_new_adj_basic_pay := p_new_adj_basic_pay;
1123 END IF;
1124 IF p_new_loc_adj IS NULL THEN
1125 l_new_loc_adj := p_old_loc_adj;
1126 ELSE
1127 l_new_loc_adj := p_new_loc_adj;
1128 END IF;
1129 IF p_tot_new_sal IS NULL THEN
1130 l_tot_new_sal := p_tot_old_sal;
1131 ELSE
1132 l_tot_new_sal := p_tot_new_sal;
1133 END IF;
1134 ELSE
1135 l_new_basic_pay := p_new_basic_pay;
1136 l_new_adj_basic_pay := p_new_adj_basic_pay;
1137 l_new_loc_adj := p_new_loc_adj;
1138 l_tot_new_sal := p_tot_new_sal;
1139 END IF;
1140 END IF; --IF g_mcal_flag THEN
1141 INSERT
1142 INTO ghr_mass_noa_preview (mass_noa_preview_id
1143 , mass_action_type
1144 , ui_type
1145 , session_id
1146 , effective_date
1147 , employee_date_of_birth
1148 , full_name
1149 , national_identifier
1150 , employee_number
1151 , duty_station_id
1152 , duty_station_code
1153 , duty_station_desc
1154 , personnel_office_id
1155 , poi_desc
1156 , from_basic_pay
1157 , to_basic_pay
1158 , from_adj_basic_pay
1159 , to_adj_basic_pay
1160 , from_locality_adj
1161 , to_locality_adj
1162 , from_total_salary
1163 , to_total_salary
1164 , position_id
1165 , position_title
1166 , position_number
1167 , position_seq_no
1168 , agency_code
1169 , person_id
1170 , select_flag
1171 , noa_code
1172 , organization_name
1173 , to_agency_code
1174 , to_duty_station_id
1175 , to_duty_station_code
1176 , to_duty_station_desc
1177 , old_assignment_status
1178 , new_assignment_status
1179 , old_type_of_employment
1180 , new_type_of_employment
1181 , old_pay_userra_status
1182 , new_pay_userra_status
1183 , occ_code
1184 , to_occ_code
1185 , from_bargaining_unit_status
1186 , to_bargaining_unit_status
1187 , from_flsa_category
1188 , to_flsa_category
1189 , from_functional_class
1190 , to_functional_class
1191 , furlough_indicator
1192 , old_lwop_nte_start_date
1193 , new_lwop_nte_start_date
1194 , old_lwop_nte
1195 , new_lwop_nte
1196 , old_furlough_nte_start_date
1197 , new_furlough_nte_start_date
1198 , old_furlough_nte
1199 , new_furlough_nte
1200 , user_attribute1
1201 , user_attribute2
1202 , user_attribute3
1203 , user_attribute4
1204 , user_attribute5
1205 , user_attribute6
1206 , user_attribute7
1207 , user_attribute8
1208 , user_attribute9
1209 , user_attribute10
1210 , user_attribute11
1211 , user_attribute12
1212 , user_attribute13
1213 , user_attribute14
1214 , user_attribute15
1215 , user_attribute16
1216 , user_attribute17
1217 , user_attribute18
1218 , user_attribute19
1219 , user_attribute20)
1220 VALUES (GHR_MASS_NOA_PREVIEW_S.nextval
1221 , 'MNOA'
1222 , 'FORM'
1223 , userenv ('SESSIONID')
1224 , p_effective_date
1225 , p_date_of_birth
1226 , p_full_name
1227 , p_national_identifier
1228 , p_employee_number
1229 , p_duty_station_id
1230 , p_duty_station_code
1231 , p_duty_station_desc
1232 , p_personnel_office_id
1233 , l_poi_desc
1234 , p_basic_pay
1235 , l_new_basic_pay
1236 , p_adj_basic_pay
1237 , l_new_adj_basic_pay
1238 , p_old_loc_adj
1239 , l_new_loc_adj
1240 , p_tot_old_sal
1241 , l_tot_new_sal
1242 , p_position_id
1243 , p_position_title
1244 , p_position_number
1245 , p_position_seq_no
1246 , p_agency_sub_element_code
1247 , p_person_id
1248 , p_sel_flg
1249 , l_temp_noa_code
1250 , p_organization_name
1251 , p_to_agency_code
1252 , p_to_duty_station_id
1253 , p_to_duty_station_code
1254 , p_to_duty_station_desc
1255 , l_old_assignment_status
1256 , l_new_assignment_status
1257 , l_old_type_of_employment
1258 , l_new_type_of_employment
1259 , l_old_pay_userra_status
1260 , l_new_pay_userra_status
1261 , p_occ_code
1262 , p_to_occ_code
1263 , p_from_bargaining_unit_status
1264 , p_to_bargaining_unit_status
1265 , p_from_flsa_category
1266 , p_to_flsa_category
1267 , p_from_functional_class
1268 , p_to_functional_class
1269 , l_furlough_indicator
1270 , l_old_lwop_nte_start_date
1271 , l_new_lwop_nte_start_date
1272 , l_old_lwop_nte
1273 , l_new_lwop_nte
1274 , l_old_furlough_nte_start_date
1275 , l_new_furlough_nte_start_date
1276 , l_old_furlough_nte
1277 , l_new_furlough_nte
1278 , l_cust_rec.user_attribute1
1279 , l_cust_rec.user_attribute2
1280 , l_cust_rec.user_attribute3
1281 , l_cust_rec.user_attribute4
1282 , l_cust_rec.user_attribute5
1283 , l_cust_rec.user_attribute6
1284 , l_cust_rec.user_attribute7
1285 , l_cust_rec.user_attribute8
1286 , l_cust_rec.user_attribute9
1287 , l_cust_rec.user_attribute10
1288 , l_cust_rec.user_attribute11
1289 , l_cust_rec.user_attribute12
1290 , l_cust_rec.user_attribute13
1291 , l_cust_rec.user_attribute14
1292 , l_cust_rec.user_attribute15
1293 , l_cust_rec.user_attribute16
1294 , l_cust_rec.user_attribute17
1295 , l_cust_rec.user_attribute18
1296 , l_cust_rec.user_attribute19
1297 , l_cust_rec.user_attribute20);
1298
1299 hr_utility.set_location ('Exiting '
1300 || l_proc, 10);
1301 EXCEPTION
1302 WHEN mnoa_error THEN
1303 RAISE;
1304 WHEN others THEN
1305 hr_utility.set_location ('Error in '
1306 || l_proc
1307 || ' Err is '
1308 || sqlerrm (sqlcode), 20);
1309
1310 l_noaerrbuf := 'Error in '
1311 || l_proc
1312 || ' Sql Err is '
1313 || sqlerrm (sqlcode);
1314
1315 RAISE mnoa_error;
1316 END create_mass_noa_prev;
1317
1318
1319 PROCEDURE ins_upd_per_extra_info
1320 (p_person_id IN number
1321 , p_effective_date IN date
1322 , p_sel_flag IN varchar2
1323 , p_comment IN varchar2
1324 , p_msl_id IN number) IS
1325
1326 l_person_extra_info_id number;
1327 l_object_version_number number;
1328 l_per_ei_data per_people_extra_info%ROWTYPE;
1329
1330 CURSOR people_ext_cur
1331 (person IN number) IS
1332 SELECT person_extra_info_id
1333 , object_version_number
1334 FROM per_people_extra_info
1335 WHERE person_id = person
1336 AND information_type = 'GHR_US_PER_MASS_ACTIONS';
1337
1338 l_proc varchar2(72) DEFAULT g_package|| '.ins_upd_per_extra_info';
1339 l_eff_date date;
1340 BEGIN
1341 g_proc := 'ins_upd_per_extra_info';
1342 hr_utility.set_location ('Entering '|| l_proc, 5);
1343
1344 IF p_effective_date > sysdate THEN
1345 l_eff_date := sysdate;
1346 ELSE
1347 l_eff_date := p_effective_date;
1348 END IF;
1349
1350 ghr_history_fetch.fetch_peopleei
1351 (p_person_id => p_person_id
1352 , p_information_type => 'GHR_US_PER_MASS_ACTIONS'
1353 , p_date_effective => l_eff_date
1354 , p_per_ei_data => l_per_ei_data);
1355
1356 l_person_extra_info_id := l_per_ei_data.person_extra_info_id;
1357 l_object_version_number := l_per_ei_data.object_version_number;
1358
1359 IF l_person_extra_info_id IS NULL THEN
1360 FOR per_ext_rec IN people_ext_cur (p_person_id) LOOP
1361 l_person_extra_info_id := per_ext_rec.person_extra_info_id;
1362 l_object_version_number := per_ext_rec.object_version_number;
1363 END LOOP;
1364 END IF;
1365
1366 IF l_person_extra_info_id IS NOT NULL THEN
1367 ghr_person_extra_info_api.update_person_extra_info
1368 (p_person_extra_info_id => l_person_extra_info_id
1369 , p_effective_date => trunc (l_eff_date)
1370 , p_object_version_number => l_object_version_number
1371 , p_pei_information3 => p_sel_flag
1372 , p_pei_information4 => p_comment
1373 , p_pei_information5 => to_char (p_msl_id)
1374 , p_pei_information_category => 'GHR_US_PER_MASS_ACTIONS');
1375 ELSE
1376 ghr_person_extra_info_api.create_person_extra_info
1377 (p_person_id => p_person_id
1378 , p_information_type => 'GHR_US_PER_MASS_ACTIONS'
1379 , p_effective_date => trunc (l_eff_date)
1380 , p_pei_information3 => p_sel_flag
1381 , p_pei_information4 => p_comment
1382 , p_pei_information5 => to_char (p_msl_id)
1383 , p_pei_information_category => 'GHR_US_PER_MASS_ACTIONS'
1384 , p_person_extra_info_id => l_person_extra_info_id
1385 , p_object_version_number => l_object_version_number);
1386 END IF;
1387
1388 hr_utility.set_location ('Exiting '|| l_proc, 10);
1389 EXCEPTION
1390 WHEN mnoa_error THEN
1391 RAISE;
1392 WHEN others THEN
1393 pr ('Error in '|| l_proc);
1394
1395 hr_utility.set_location ('Error in '|| l_proc|| ' Err is '|| sqlerrm (sqlcode), 20);
1396 l_noaerrbuf := 'Error in '|| l_proc|| ' Sql Err is '|| sqlerrm (sqlcode);
1397 RAISE mnoa_error;
1398 END ins_upd_per_extra_info;
1399
1400 PROCEDURE get_lac_dtls
1401 (p_pa_request_id IN number
1402 , p_sf52_rec OUT NOCOPY ghr_pa_requests%ROWTYPE) IS
1403
1404 l_proc varchar2(72) DEFAULT g_package|| '.get_lac_dtls';
1405 CURSOR cur_pa_req_cur IS
1406 SELECT *
1407 FROM ghr_pa_requests
1408 WHERE pa_request_id = p_pa_request_id;
1409 BEGIN
1410 g_proc := 'get_lac_dtls';
1411
1412 hr_utility.set_location ('Entering '|| l_proc, 5);
1413
1414 pr ('Entering '|| l_proc|| ' Pa req id ', to_char (p_pa_request_id));
1415
1416 IF p_pa_request_id IS NULL THEN
1417 pr ('PA request id is null.................');
1418 ELSE
1419 FOR cur_pa_rec IN cur_pa_req_cur LOOP
1420 p_sf52_rec := cur_pa_rec;
1421 EXIT;
1422 END LOOP;
1423 END IF;
1424 hr_utility.set_location ('Exiting '|| l_proc, 10);
1425 EXCEPTION
1426 WHEN others THEN
1427 hr_utility.set_location ('Error in '|| l_proc|| ' Err is '|| sqlerrm (sqlcode), 20);
1428 l_noaerrbuf := 'Error in '|| l_proc|| ' Sql Err is '|| sqlerrm (sqlcode);
1429 RAISE mnoa_error;
1430 END get_lac_dtls;
1431
1432 PROCEDURE update_position_info
1433 (p_position_data_rec IN ghr_sf52_pos_update.position_data_rec_type) IS
1434 l_proc varchar2(30) DEFAULT 'update_position_info';
1435 BEGIN
1436 hr_utility.set_location ('Entering '|| l_proc, 10);
1437 hr_utility.set_location ('Vacant Position ID '|| to_char (p_position_data_rec.position_id), 10);
1438
1439 ghr_session.set_session_var_for_core (p_position_data_rec.effective_end_date);
1440
1441 ghr_sf52_pos_update.update_position_info
1442 (p_pos_data_rec => p_position_data_rec);
1443
1444 hr_utility.set_location ('Calling post_update_process '|| l_proc, 50);
1445 ghr_history_api.post_update_process;
1446 hr_utility.set_location ('Leaving '|| l_proc, 100);
1447 END update_position_info;
1448
1449 PROCEDURE get_pos_grp1_ddf
1450 (p_position_id IN per_assignments_f.position_id%TYPE
1451 , p_effective_date IN date
1452 , p_flsa_category OUT NOCOPY varchar2
1453 , p_barg_unit_status OUT NOCOPY varchar2
1454 , p_functional_class OUT NOCOPY varchar2 -- Bug# 14410401
1455 , p_pos_ei_data OUT NOCOPY per_position_extra_info%ROWTYPE) IS
1456 l_proc varchar2(72) DEFAULT g_package|| '.get_pos_grp1_ddf';
1457 BEGIN
1458 g_proc := 'get_pos_grp1_ddf';
1459
1460 hr_utility.set_location ('Entering '|| l_proc, 5);
1461
1462 ghr_history_fetch.fetch_positionei
1463 (p_position_id => p_position_id
1464 , p_information_type => 'GHR_US_POS_GRP1'
1465 , p_date_effective => p_effective_date
1466 , p_pos_ei_data => p_pos_ei_data);
1467
1468 p_flsa_category := p_pos_ei_data.poei_information7;
1469 p_barg_unit_status := p_pos_ei_data.poei_information8;
1470 p_functional_class := p_pos_ei_data.poei_information11;
1471
1472 hr_utility.set_location ('Exiting '|| l_proc, 10);
1473 EXCEPTION
1474 WHEN mnoa_error THEN
1475 RAISE;
1476 WHEN others THEN
1477 p_barg_unit_status := NULL;
1478 p_flsa_category := NULL;
1479 p_functional_class := NULL;
1480 p_pos_ei_data := NULL;
1481
1482 pr ('Error in '|| l_proc);
1483
1484 hr_utility.set_location ('Error in '|| l_proc|| ' Err is '|| sqlerrm (sqlcode), 20);
1485
1486 l_noaerrbuf := 'Error in '|| l_proc|| ' Sql Err is '|| sqlerrm (sqlcode);
1487
1488 RAISE mnoa_error;
1489 END get_pos_grp1_ddf;
1490
1491 PROCEDURE execute_mnoa
1492 (p_errbuf OUT NOCOPY varchar2
1493 , p_retcode OUT NOCOPY number
1494 , p_mass_salary_id IN number
1495 , p_action IN varchar2) IS
1496
1497 p_mass_salary varchar2(32);
1498 l_assignment_id per_assignments_f.assignment_id%TYPE;
1499 l_position_id per_assignments_f.position_id%TYPE;
1500 l_grade_id per_assignments_f.grade_id%TYPE;
1501 l_business_group_id per_assignments_f.business_group_id%TYPE;
1502 l_position_title varchar2(300);
1503 l_position_number varchar2(20);
1504 l_position_seq_no varchar2(20);
1505 l_recs_failed number DEFAULT 0;
1506 l_tenure varchar2(35);
1507 l_annuitant_indicator varchar2(35);
1508 l_pay_rate_determinant varchar2(35);
1509 l_work_schedule varchar2(35);
1510 l_part_time_hour varchar2(35);
1511 l_pay_table_id number;
1512 l_pay_plan varchar2(30);
1513 l_grade_or_level varchar2(30);
1514 l_to_grade_id number;
1515 l_to_pay_plan varchar2(30);
1516 l_to_grade_or_level varchar2(30);
1517 l_step_or_rate varchar2(30);
1518 l_pay_basis varchar2(30);
1519 l_location_id number;
1520 l_duty_station_id number;
1521 l_duty_station_desc ghr_pa_requests.duty_station_desc%TYPE;
1522 l_duty_station_code ghr_pa_requests.duty_station_code%TYPE;
1523 l_effective_date date;
1524 l_p_locality_area_code ghr_mass_salaries.locality_pay_area_code%TYPE;
1525 l_personnel_office_id varchar2(300);
1526 l_org_structure_id varchar2(300);
1527 l_sub_element_code varchar2(300);
1528 l_old_basic_pay number;
1529 l_old_avail_pay number;
1530 l_old_loc_diff number;
1531 l_tot_old_sal number;
1532 l_old_auo_pay number;
1533 l_old_adj_basic_pay number;
1534 l_other_pay number;
1535 l_auo_premium_pay_indicator varchar2(30);
1536 l_ap_premium_pay_indicator varchar2(30);
1537 l_retention_allowance number;
1538 l_retention_allow_perc number;
1539 l_new_retention_allowance number;
1540 l_supervisory_differential number;
1541 l_supervisory_diff_perc number;
1542 l_new_supervisory_differential number;
1543 l_staffing_differential number;
1544 l_new_avail_pay number;
1545 l_new_loc_diff number;
1546 l_tot_new_sal number;
1547 l_new_auo_pay number;
1548 l_new_basic_pay number;
1549 l_new_locality_adj number;
1550 l_new_adj_basic_pay number;
1551 l_new_total_salary number;
1552 l_new_other_pay_amount number;
1553 l_new_au_overtime number;
1554 l_new_availability_pay number;
1555 l_out_step_or_rate varchar2(30);
1556 l_out_pay_rate_determinant varchar2(30);
1557 l_out_pay_plan varchar2(30);
1558 l_out_grade_id number;
1559 l_out_grade_or_level varchar2(30);
1560 l_message_set boolean;
1561 l_calculated boolean;
1562 l_mass_salary_id number;
1563 l_user_table_id number;
1564 l_submit_flag varchar2(2);
1565 l_executive_order_number ghr_mass_salaries.executive_order_number%TYPE;
1566 l_executive_order_date ghr_mass_salaries.executive_order_date%TYPE;
1567 l_opm_issuance_number ghr_mass_salaries.opm_issuance_number%TYPE;
1568 l_opm_issuance_date ghr_mass_salaries.opm_issuance_date%TYPE;
1569 l_pa_request_id number;
1570 l_rowid varchar2(30);
1571 l_p_organization_id number;
1572 l_p_duty_station_id number;
1573 l_p_job_id ghr_pa_requests.from_occ_code%type;
1574 l_p_bargaining_unit ghr_pa_requests.bargaining_unit_status%type;
1575 l_p_flsa_category ghr_pa_requests.flsa_category%type;
1576 l_row_cnt number DEFAULT 0;
1577 l_sf52_rec ghr_pa_requests%ROWTYPE;
1578 l_lac_sf52_rec ghr_pa_requests%ROWTYPE;
1579 l_errbuf varchar2(2000);
1580 l_retcode number;
1581 l_pos_ei_data per_position_extra_info%ROWTYPE;
1582 l_pay_calc_in_data ghr_pay_calc.pay_calc_in_rec_type;
1583 l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
1584 l_sel_flg varchar2(2);
1585 l_first_action_la_code1 varchar2(30);
1586 l_first_action_la_code2 varchar2(30);
1587 l_remark_code1 varchar2(30);
1588 l_remark_code2 varchar2(30);
1589 l_p_agency_code_subelement varchar2(30);
1590 l_entitled_other_pay number;
1591 l_capped_other_pay number;
1592 l_adj_basic_message boolean DEFAULT FALSE;
1593 l_pay_cap_message boolean DEFAULT FALSE;
1594 l_temp_retention_allowance number;
1595 l_open_pay_fields_caps boolean;
1596 l_message_set_caps boolean;
1597 l_total_pay_check varchar2(1);
1598 l_comment varchar2(150);
1599 l_comment_sal varchar2(150);
1600 l_old_capped_other_pay number;
1601
1602 l_elig_flag boolean DEFAULT FALSE;
1603 rec_busy EXCEPTION;
1604 PRAGMA EXCEPTION_INIT(rec_busy, -54);
1605 l_proc varchar2(72) DEFAULT g_package|| '.execute_mnoa';
1606 l_org_name hr_organization_units.name%TYPE;
1607 l_asg_extra_info_rec per_assignment_extra_info%rowtype;
1608 ---Vacant position variables
1609 l_avail_status_id NUMBER;
1610 l_position_data_rec ghr_sf52_pos_UPDATE.position_data_rec_type;
1611 l_new_pos_grp1_rec per_position_extra_info%rowtype;
1612 l_pos_valid_grade_ei_data per_position_extra_info%rowtype;
1613
1614 CURSOR cur_active_people
1615 (effective_date IN date) IS
1616 SELECT ppf.person_id person_id
1617 , ppf.first_name first_name
1618 , ppf.last_name last_name
1619 , ppf.middle_names middle_names
1620 , ppf.full_name full_name
1621 , ppf.date_of_birth date_of_birth
1622 , ppf.national_identifier national_identifier
1623 , ppf.employee_number employee_number
1624 , paf.position_id position_id
1625 , paf.assignment_id assignment_id
1626 , paf.grade_id grade_id
1627 , paf.job_id job_id
1628 , paf.location_id location_id
1629 , paf.organization_id organization_id
1630 , paf.business_group_id business_group_id
1631 , paf.assignment_status_type_id assignment_status_type_id
1632 FROM per_assignments_f paf
1633 , per_people_f ppf
1634 , per_person_types ppt
1635 , hr_organization_units hou
1636 , per_assignment_status_types past
1637 WHERE ppf.person_id = paf.person_id
1638 AND l_effective_date
1639 BETWEEN ppf.effective_start_date
1640 AND ppf.effective_end_date
1641 AND l_effective_date
1642 BETWEEN paf.effective_start_date
1643 AND paf.effective_end_date
1644 AND paf.primary_flag = 'Y'
1645 AND paf.assignment_type <> 'B'
1646 AND ppf.person_type_id = ppt.person_type_id
1647 AND paf.assignment_status_type_id = past.assignment_status_type_id
1648 AND ppt.system_person_type IN ('EMP', 'EMP_APL')
1649 AND past.per_system_status <> 'SUSP_ASSIGN' --Bug# 14658176
1650 AND upper (user_status) NOT IN ('TERMINATE ASSIGNMENT', 'ACTIVE APPLICATION', 'OFFER'
1651 , 'ACCEPTED', 'TERMINATE APPLICATION', 'END'
1652 , 'FURLOUGH', 'FURLOUGH NTE', 'TERMINATE APPOINTMENT'
1653 , 'SEPARATED')
1654 AND paf.organization_id = hou.organization_id
1655 AND paf.position_id IS NOT NULL
1656 ORDER BY ppf.person_id;
1657 CURSOR cur_292_people
1658 (effective_date IN date) IS
1659 SELECT ppf.person_id person_id
1660 , ppf.first_name first_name
1661 , ppf.last_name last_name
1662 , ppf.middle_names middle_names
1663 , ppf.full_name full_name
1664 , ppf.date_of_birth date_of_birth
1665 , ppf.national_identifier national_identifier
1666 , ppf.employee_number employee_number
1667 , paf.position_id position_id
1668 , paf.assignment_id assignment_id
1669 , paf.grade_id grade_id
1670 , paf.job_id job_id
1671 , paf.location_id location_id
1672 , paf.organization_id organization_id
1673 , paf.business_group_id business_group_id
1674 , paf.assignment_status_type_id assignment_status_type_id
1675 FROM per_assignments_f paf
1676 , per_people_f ppf
1677 , per_person_types ppt
1678 , hr_organization_units hou
1679 , per_assignment_status_types past
1680 WHERE ppf.person_id = paf.person_id
1681 AND l_effective_date
1682 BETWEEN ppf.effective_start_date
1683 AND ppf.effective_end_date
1684 AND l_effective_date
1685 BETWEEN paf.effective_start_date
1686 AND paf.effective_end_date
1687 AND paf.primary_flag = 'Y'
1688 AND paf.assignment_type <> 'B'
1689 AND ppf.person_type_id = ppt.person_type_id
1690 AND paf.assignment_status_type_id = past.assignment_status_type_id
1691 AND ppt.system_person_type IN ('EMP', 'EMP_APL')
1692 AND past.per_system_status = 'SUSP_ASSIGN'
1693 AND past.user_status NOT IN ('Non Pay') --Bug# 14414790
1694 AND paf.organization_id = hou.organization_id
1695 AND paf.position_id IS NOT NULL
1696 ORDER BY ppf.person_id;
1697 CURSOR cur_280_people
1698 (effective_date IN date) IS
1699 SELECT ppf.person_id person_id
1700 , ppf.first_name first_name
1701 , ppf.last_name last_name
1702 , ppf.middle_names middle_names
1703 , ppf.full_name full_name
1704 , ppf.date_of_birth date_of_birth
1705 , ppf.national_identifier national_identifier
1706 , ppf.employee_number employee_number
1707 , paf.position_id position_id
1708 , paf.assignment_id assignment_id
1709 , paf.grade_id grade_id
1710 , paf.job_id job_id
1711 , paf.location_id location_id
1712 , paf.organization_id organization_id
1713 , paf.business_group_id business_group_id
1714 , paf.assignment_status_type_id assignment_status_type_id
1715 FROM per_assignments_f paf
1716 , per_people_f ppf
1717 , per_person_types ppt
1718 , hr_organization_units hou
1719 , per_assignment_status_types past
1720 WHERE ppf.person_id = paf.person_id
1721 AND l_effective_date
1722 BETWEEN ppf.effective_start_date
1723 AND ppf.effective_end_date
1724 AND l_effective_date
1725 BETWEEN paf.effective_start_date
1726 AND paf.effective_end_date
1727 AND paf.primary_flag = 'Y'
1728 AND paf.assignment_type <> 'B'
1729 AND ppf.person_type_id = ppt.person_type_id
1730 AND paf.assignment_status_type_id = past.assignment_status_type_id
1731 AND ppt.system_person_type IN ('EMP', 'EMP_APL')
1732 AND past.per_system_status = 'SUSP_ASSIGN'
1733 AND past.user_status = 'Non Pay' --Bug# 14405844
1734 AND paf.organization_id = hou.organization_id
1735 AND paf.position_id IS NOT NULL
1736 ORDER BY ppf.person_id;
1737 CURSOR cur_772_people
1738 (l_eff_date IN date) IS
1739 SELECT ppf.person_id person_id
1740 , ppf.first_name first_name
1741 , ppf.last_name last_name
1742 , ppf.middle_names middle_names
1743 , ppf.full_name full_name
1744 , ppf.date_of_birth date_of_birth
1745 , ppf.national_identifier national_identifier
1746 , ppf.employee_number employee_number
1747 , paf.position_id position_id
1748 , paf.assignment_id assignment_id
1749 , paf.grade_id grade_id
1750 , paf.job_id job_id
1751 , paf.location_id location_id
1752 , paf.organization_id organization_id
1753 , paf.business_group_id business_group_id
1754 , paf.assignment_status_type_id assignment_status_type_id
1755 FROM per_assignments_f paf
1756 , per_people_f ppf
1757 , per_person_types ppt
1758 , hr_organization_units hou
1759 , per_assignment_status_types past
1760 WHERE ppf.person_id = paf.person_id
1761 AND l_eff_date
1762 BETWEEN ppf.effective_start_date
1763 AND ppf.effective_end_date
1764 AND l_eff_date
1765 BETWEEN paf.effective_start_date
1766 AND paf.effective_end_date
1767 AND paf.primary_flag = 'Y'
1768 AND paf.assignment_type <> 'B'
1769 AND ppf.person_type_id = ppt.person_type_id
1770 AND ppt.system_person_type IN ('EMP', 'EMP_APL')
1771 AND paf.assignment_status_type_id = past.assignment_status_type_id
1772 AND past.user_status IN ('Furlough NTE') --Bug# 14414790
1773 AND paf.organization_id = hou.organization_id
1774 AND paf.position_id IS NOT NULL
1775 AND paf.business_group_id = nvl (l_business_group_id, paf.business_group_id)
1776 ORDER BY ppf.person_id;
1777 CURSOR cur_773_people
1778 (effective_date IN date) IS
1779 SELECT ppf.person_id person_id
1780 , ppf.first_name first_name
1781 , ppf.last_name last_name
1782 , ppf.middle_names middle_names
1783 , ppf.full_name full_name
1784 , ppf.date_of_birth date_of_birth
1785 , ppf.national_identifier national_identifier
1786 , ppf.employee_number employee_number
1787 , paf.position_id position_id
1788 , paf.assignment_id assignment_id
1789 , paf.grade_id grade_id
1790 , paf.job_id job_id
1791 , paf.location_id location_id
1792 , paf.organization_id organization_id
1793 , paf.business_group_id business_group_id
1794 , paf.assignment_status_type_id assignment_status_type_id
1795 FROM per_assignments_f paf
1796 , per_people_f ppf
1797 , per_person_types ppt
1798 , hr_organization_units hou
1799 , per_assignment_status_types past
1800 WHERE ppf.person_id = paf.person_id
1801 AND l_effective_date
1802 BETWEEN ppf.effective_start_date
1803 AND ppf.effective_end_date
1804 AND l_effective_date
1805 BETWEEN paf.effective_start_date
1806 AND paf.effective_end_date
1807 AND paf.primary_flag = 'Y'
1808 AND paf.assignment_type <> 'B'
1809 AND ppf.person_type_id = ppt.person_type_id
1810 AND paf.assignment_status_type_id = past.assignment_status_type_id
1811 AND ppt.system_person_type IN ('EMP', 'EMP_APL')
1812 AND paf.organization_id = hou.organization_id
1813 AND paf.position_id IS NOT NULL
1814 AND past.user_status = 'Leave Without Pay NTE'
1815 ORDER BY ppf.person_id;
1816 --Begin Bug# 14414790
1817 CURSOR cur_430_people
1818 (effective_date IN date) IS
1819 SELECT ppf.person_id person_id
1820 , ppf.first_name first_name
1821 , ppf.last_name last_name
1822 , ppf.middle_names middle_names
1823 , ppf.full_name full_name
1824 , ppf.date_of_birth date_of_birth
1825 , ppf.national_identifier national_identifier
1826 , ppf.employee_number employee_number
1827 , paf.position_id position_id
1828 , paf.assignment_id assignment_id
1829 , paf.grade_id grade_id
1830 , paf.job_id job_id
1831 , paf.location_id location_id
1832 , paf.organization_id organization_id
1833 , paf.business_group_id business_group_id
1834 , paf.assignment_status_type_id assignment_status_type_id
1835 FROM per_assignments_f paf
1836 , per_people_f ppf
1837 , per_person_types ppt
1838 , hr_organization_units hou
1839 , per_assignment_status_types past
1840 WHERE ppf.person_id = paf.person_id
1841 AND l_effective_date
1842 BETWEEN ppf.effective_start_date
1843 AND ppf.effective_end_date
1844 AND l_effective_date
1845 BETWEEN paf.effective_start_date
1846 AND paf.effective_end_date
1847 AND paf.primary_flag = 'Y'
1848 AND paf.assignment_type <> 'B'
1849 AND ppf.person_type_id = ppt.person_type_id
1850 AND paf.assignment_status_type_id = past.assignment_status_type_id
1851 AND ppt.system_person_type IN ('EMP', 'EMP_APL')
1852 AND paf.organization_id = hou.organization_id
1853 AND paf.position_id IS NOT NULL
1854 AND past.per_system_status <> 'SUSP_ASSIGN' --Bug# 14658176
1855 --AND past.user_status NOT IN('Leave Without Pay NTE','Non Pay','Leave Without Pay Mil','Leave Without Pay US')
1856 ORDER BY ppf.person_id;
1857 --End Bug# 14414790
1858 CURSOR cur_792_people
1859 (effective_date IN date) IS
1860 SELECT ppf.person_id person_id
1861 , ppf.first_name first_name
1862 , ppf.last_name last_name
1863 , ppf.middle_names middle_names
1864 , ppf.full_name full_name
1865 , ppf.date_of_birth date_of_birth
1866 , ppf.national_identifier national_identifier
1867 , ppf.employee_number employee_number
1868 , paf.position_id position_id
1869 , paf.assignment_id assignment_id
1870 , paf.grade_id grade_id
1871 , paf.job_id job_id
1872 , paf.location_id location_id
1873 , paf.organization_id organization_id
1874 , paf.business_group_id business_group_id
1875 , paf.assignment_status_type_id assignment_status_type_id
1876 FROM per_assignments_f paf
1877 , per_people_f ppf
1878 , per_person_types ppt
1879 , hr_organization_units hou
1880 WHERE ppf.person_id = paf.person_id
1881 AND l_effective_date
1882 BETWEEN ppf.effective_start_date
1883 AND ppf.effective_end_date
1884 AND l_effective_date
1885 BETWEEN paf.effective_start_date
1886 AND paf.effective_end_date
1887 AND paf.primary_flag = 'Y'
1888 AND paf.assignment_type <> 'B'
1889 AND ppf.person_type_id = ppt.person_type_id
1890 AND ppt.system_person_type IN ('EMP', 'EMP_APL')
1891 AND paf.organization_id = hou.organization_id
1892 AND paf.position_id IS NOT NULL
1893 ORDER BY ppf.person_id;
1894
1895 CURSOR vacant_pos (g_business_group_id per_business_groups.business_group_id%type,
1896 effective_date date) is
1897 SELECT null PERSON_ID,
1898 'VACANT' FIRST_NAME,
1899 'VACANT' LAST_NAME,
1900 null MIDDLE_NAMES,
1901 'VACANT' FULL_NAME,
1902 null DATE_OF_BIRTH,
1903 null NATIONAL_IDENTIFIER,
1904 null EMPLOYEE_NUMBER,
1905 position_id POSITION_ID,
1906 null ASSIGNMENT_ID,
1907 to_NUMBER(null) GRADE_ID,
1908 JOB_ID,
1909 pop.LOCATION_ID,
1910 pop.ORGANIZATION_ID,
1911 pop.BUSINESS_GROUP_ID,
1912 punits.name ORGANIZATION_NAME,
1913 pop.availability_status_id
1914 from hr_positions_f pop,
1915 per_organization_units punits
1916 WHERE pop.business_group_id = g_business_group_id
1917 and trunc(effective_date) between pop.effective_start_DATE and pop.effective_END_DATE
1918 and pop.organization_id = punits.organization_id
1919 and not exists
1920 (
1921 SELECT 'X'
1922 FROM per_people_f p, per_assignments_f a
1923 WHERE trunc(effective_date) between a.effective_start_date and a.effective_end_date
1924 AND a.primary_flag = 'Y'
1925 AND a.assignment_type <> 'B'
1926 AND p.current_employee_flag = 'Y'
1927 AND a.business_group_id = g_business_group_id
1928 AND a.person_id = p.person_id
1929 AND a.position_id = pop.position_id
1930 AND trunc(effective_date) between p.effective_start_date and p.effective_end_date
1931 );
1932
1933 cursor c_grade_kff (grd_id NUMBER) is
1934 SELECT gdf.segment1
1935 ,gdf.segment2
1936 from per_grades grd, per_grade_definitions gdf
1937 WHERE grd.grade_id = grd_id
1938 and grd.grade_definition_id = gdf.grade_definition_id;
1939
1940 CURSOR ghr_msnoa
1941 (p_msnoa_id IN number) IS
1942 SELECT name
1943 , effective_date
1944 , mass_salary_id
1945 , user_table_id
1946 , submit_flag
1947 , executive_order_number
1948 , executive_order_date
1949 , rowid
1950 , pa_request_id
1951 , organization_id
1952 , duty_station_id
1953 , personnel_office_id
1954 , agency_code_subelement
1955 , opm_issuance_number
1956 , opm_issuance_date
1957 , locality_pay_area_code
1958 , noa_code
1959 , nte_date
1960 ,target_bargaining_unit_status
1961 ,target_flsa_category
1962 ,target_occ_code
1963 ,target_job_id
1964 ,target_agency_code_subelement
1965 ,target_duty_station_id
1966 ,target_duty_station_code
1967 ,target_functional_class
1968 ,bargaining_unit_status
1969 ,flsa_category
1970 ,job_id
1971 FROM ghr_mass_salaries
1972 WHERE mass_salary_id = p_msnoa_id
1973 FOR UPDATE OF user_table_id NOWAIT;
1974
1975 CURSOR get_noa_fam IS
1976 SELECT noa_family_code
1977 FROM ghr_families
1978 WHERE noa_family_code IN
1979 (
1980 SELECT noa_family_code
1981 FROM ghr_noa_families
1982 WHERE nature_of_action_id IN
1983 (
1984 SELECT nature_of_action_id
1985 FROM ghr_nature_of_actions
1986 WHERE code = ghr_msl_pkg.g_first_noa_code
1987 )
1988 )
1989 AND proc_method_flag = 'Y';
1990
1991 CURSOR get_loc_id(c_duty_station_code ghr_pa_requests.duty_station_code%type) IS
1992 SELECT lei.location_id location_id
1993 FROM hr_location_extra_info lei, ghr_duty_stations_v ds
1994 WHERE ds.duty_station_code = c_duty_station_code
1995 AND lei.information_type = 'GHR_US_LOC_INFORMATION'
1996 and to_number(lei.lei_information3) = ds.duty_station_id
1997 and l_effective_date between ds.effective_start_date
1998 and ds.effective_end_date;
1999
2000 PROCEDURE vacant_pos_process
2001 (p_assignment_id IN per_assignments_f.assignment_id%TYPE
2002 , p_person_id IN per_assignments_f.person_id%TYPE
2003 , p_position_id IN per_assignments_f.position_id%TYPE
2004 , p_grade_id IN per_assignments_f.grade_id%TYPE
2005 , p_business_group_id IN per_assignments_f.business_group_id%TYPE
2006 , p_location_id IN per_assignments_f.location_id%TYPE
2007 , p_organization_id IN per_assignments_f.organization_id%TYPE
2008 , p_date_of_birth IN date
2009 , p_first_name IN per_people_f.first_name%TYPE
2010 , p_last_name IN per_people_f.last_name%TYPE
2011 , p_full_name IN per_people_f.full_name%TYPE
2012 , p_middle_names IN per_people_f.middle_names%TYPE
2013 , p_national_identifier IN per_people_f.national_identifier%TYPE
2014 , p_employee_number IN per_people_f.employee_number%TYPE
2015 , p_personnel_office_id IN varchar2
2016 , p_org_structure_id IN varchar2
2017 , p_position_title IN varchar2
2018 , p_position_number IN varchar2
2019 , p_position_seq_no IN varchar2
2020 , p_subelem_code IN varchar2
2021 , p_duty_station_id IN ghr_duty_stations_f.duty_station_id%TYPE
2022 , p_tenure IN varchar2
2023 , p_annuitant_indicator IN varchar2
2024 , p_pay_rate_determinant IN varchar2
2025 , p_work_schedule IN varchar2
2026 , p_part_time_hour IN varchar2
2027 , p_to_grade_id IN per_assignments_f.grade_id%TYPE
2028 , p_pay_plan IN varchar2
2029 , p_to_pay_plan IN varchar2
2030 , p_pay_table_id IN number
2031 , p_grade_or_level IN varchar2
2032 , p_to_grade_or_level IN varchar2
2033 , p_step_or_rate IN varchar2
2034 , p_pay_basis IN varchar2) IS
2035 l_proc varchar2(72) DEFAULT g_package
2036 || 'vacant_pos_process';
2037 l_vac_flsa_category per_position_extra_info.poei_information7%TYPE;
2038 l_vac_barg_unit_status per_position_extra_info.poei_information8%TYPE;
2039 l_vac_duty_location_id ghr_pa_requests.duty_station_location_id%TYPE;
2040 l_vac_functional_class per_position_extra_info.poei_information11%TYPE;
2041 -- Begin Bug# 14751973
2042 l_vac_duty_station_code ghr_mass_salaries.target_duty_station_code%type;
2043 l_vac_duty_station_desc varchar2(150);
2044 l_vac_occ_code ghr_mass_salaries.target_occ_code%type;
2045 -- End Bug# 14751973
2046 BEGIN
2047 SAVEPOINT execute_noa_vacent;
2048 l_sel_flg := NULL;
2049 l_position_data_rec := NULL;
2050 l_new_pos_grp1_rec := NULL;
2051 l_position_id := p_position_id;
2052 l_position_data_rec.position_id := p_position_id;
2053 l_position_data_rec.effective_date := l_effective_date;
2054 l_position_data_rec.organization_id := p_organization_id;
2055 l_business_group_id := p_business_group_id;
2056
2057 -- Bug# 14751973, commented below if condition
2058 /*IF l_noa_code = '792'
2059 AND l_target_duty_station_id IS NOT NULL
2060 AND l_target_duty_station_id <> p_duty_station_id THEN
2061 l_duty_station_id := l_target_duty_station_id;
2062 ELSE*/
2063 l_duty_station_id := p_duty_station_id;
2064 --END IF;
2065 --Bug# 14810769
2066 l_sub_element_code := ghr_api.get_position_agency_code_pos
2067 (l_position_id,l_business_group_id);
2068
2069 l_position_title := ghr_api.get_position_title_pos
2070 (p_position_id => l_position_id
2071 , p_business_group_id => l_business_group_id);
2072
2073 l_position_number := ghr_api.get_position_desc_no_pos
2074 (p_position_id => l_position_id
2075 , p_business_group_id => l_business_group_id);
2076
2077 l_position_seq_no := ghr_api.get_position_sequence_no_pos
2078 (p_position_id => l_position_id
2079 , p_business_group_id => l_business_group_id);
2080
2081 ghr_history_fetch.fetch_positionei
2082 (p_position_id => l_position_id
2083 , p_information_type => 'GHR_US_POS_VALID_GRADE'
2084 , p_date_effective => l_effective_date
2085 , p_pos_ei_data => l_pos_valid_grade_ei_data);
2086
2087 l_grade_id := l_pos_valid_grade_ei_data.poei_information3;
2088
2089 FOR c_grade_kff_rec IN c_grade_kff (l_grade_id) LOOP
2090 l_pay_plan := c_grade_kff_rec.segment1;
2091 l_grade_or_level := c_grade_kff_rec.segment2;
2092 EXIT;
2093 END LOOP;
2094 BEGIN
2095 ghr_pa_requests_pkg.get_duty_station_details
2096 (p_duty_station_id => l_duty_station_id
2097 , p_effective_date => l_effective_date
2098 , p_duty_station_code => l_duty_station_code
2099 , p_duty_station_desc => l_duty_station_desc);
2100
2101 hr_utility.set_location ('The duty station name is:'
2102 || l_duty_station_code, 12345);
2103 EXCEPTION
2104 WHEN others THEN
2105 hr_utility.set_location ('Error in Ghr_pa_requests_pkg.get_duty_station_details'
2106 || 'Err is '
2107 || sqlerrm (sqlcode), 20);
2108
2109 l_noaerrbuf := 'Error in get_duty_station_details Sql Err is '
2110 || sqlerrm (sqlcode);
2111 RAISE mnoa_error;
2112 END;
2113 --Begin Bug# 14751973
2114 BEGIN
2115 ghr_pa_requests_pkg.get_duty_station_details
2116 (p_duty_station_id => l_target_duty_station_id
2117 , p_effective_date => l_effective_date
2118 , p_duty_station_code => l_vac_duty_station_code
2119 , p_duty_station_desc => l_vac_duty_station_desc);
2120
2121 hr_utility.set_location ('The target duty station name is:'
2122 || l_vac_duty_station_code, 12345);
2123 EXCEPTION
2124 WHEN others THEN
2125 hr_utility.set_location ('Error in Target get Ghr_pa_requests_pkg.get_duty_station_details'
2126 || 'Err is '
2127 || sqlerrm (sqlcode), 20);
2128
2129 l_noaerrbuf := 'Error in Target get duty_station_details Sql Err is '
2130 || sqlerrm (sqlcode);
2131 RAISE mnoa_error;
2132 END;
2133 -- End Bug 4751973
2134 ghr_mlc_pkg.check_select_flg_pos (l_position_id, upper (p_action)
2135 , l_effective_date, p_mass_salary_id
2136 , l_sel_flg);
2137
2138 BEGIN
2139 IF upper (p_action) IN ('SHOW') THEN
2140 -- Begin Bug# 14751973
2141 ghr_pa_requests_pkg.get_sf52_pos_ddf_details
2142 (p_position_id => l_position_id
2143 ,p_date_Effective => l_effective_date
2144 ,p_flsa_category => l_vac_flsa_category
2145 ,p_bargaining_unit_status => l_vac_barg_unit_status
2146 ,p_work_schedule => l_dummy
2147 ,p_functional_class => l_vac_functional_class
2148 ,p_supervisory_status => l_dummy
2149 ,p_position_occupied => l_dummy
2150 ,p_appropriation_code1 => l_dummy
2151 ,p_appropriation_code2 => l_dummy
2152 ,p_personnel_office_id => l_dummy
2153 ,p_office_symbol => l_dummy
2154 ,p_part_time_hours => l_dummy
2155 );
2156 l_vac_occ_code := ghr_pa_requests_pkg.get_occ_series(l_position_id,l_effective_date,l_business_group_id);
2157
2158 create_mass_noa_prev
2159 (p_effective_date => l_effective_date
2160 , p_date_of_birth => p_date_of_birth
2161 , p_full_name => p_full_name
2162 , p_national_identifier => p_national_identifier
2163 , p_employee_number => p_employee_number
2164 , p_duty_station_id => l_duty_station_id
2165 , p_duty_station_code => l_duty_station_code
2166 , p_duty_station_desc => l_duty_station_desc
2167 , p_personnel_office_id => l_personnel_office_id
2168 , p_basic_pay => NULL
2169 , p_new_basic_pay => NULL
2170 , p_adj_basic_pay => NULL
2171 , p_new_adj_basic_pay => NULL
2172 , p_old_loc_adj => NULL
2173 , p_new_loc_adj => NULL
2174 , p_tot_old_sal => NULL
2175 , p_tot_new_sal => NULL
2176 , p_position_id => l_position_id
2177 , p_position_title => l_position_title
2178 , p_position_number => l_position_number
2179 , p_position_seq_no => l_position_seq_no
2180 , p_agency_sub_element_code => l_sub_element_code
2181 , p_person_id => p_person_id
2182 , p_mass_salary_id => l_mass_salary_id
2183 , p_sel_flg => l_sel_flg
2184 , p_action => p_action
2185 , p_assignment_id => NULL
2186 , p_organization_name => l_org_name
2187 , p_to_agency_code => l_target_agency_code_subelmt
2188 , p_to_duty_station_id => l_target_duty_station_id
2189 , p_to_duty_station_code => l_vac_duty_station_code
2190 , p_to_duty_station_desc => l_vac_duty_station_desc
2191 , p_occ_code => l_vac_occ_code
2192 , p_to_occ_code => l_target_occ_code
2193 , p_from_bargaining_unit_status => l_vac_barg_unit_status
2194 , p_to_bargaining_unit_status => l_target_barg_unit_status
2195 , p_from_flsa_category => l_vac_flsa_category
2196 , p_to_flsa_category => l_target_flsa_category
2197 , p_from_functional_class => l_vac_functional_class
2198 , p_to_functional_class => l_target_functional_class
2199 , p_work_schedule => NULL);
2200 -- End Bug# 14751973
2201 END IF;
2202
2203 ghr_mlc_pkg.ins_upd_pos_extra_info (l_position_id, l_effective_date
2204 , l_sel_flg, l_comment
2205 , p_mass_salary_id);
2206 l_comment := NULL;
2207 END;
2208
2209 IF upper (p_action) = 'CREATE' THEN
2210
2211 IF l_sel_flg ='Y' THEN
2212 BEGIN
2213 get_pos_grp1_ddf (l_position_id, l_effective_date
2214 , l_vac_flsa_category, l_vac_barg_unit_status, l_vac_functional_class
2215 , l_new_pos_grp1_rec);
2216
2217 IF l_noa_code = '800' THEN
2218 hr_utility.set_location ('Assigning Position Elements for NOA: 800 '
2219 || l_proc, 139);
2220
2221 IF l_target_agency_code_subelmt IS NOT NULL THEN
2222 l_position_data_rec.agency_code_subelement := l_target_agency_code_subelmt;
2223 END IF;
2224
2225 IF l_target_job_id IS NOT NULL THEN
2226 l_position_data_rec.job_id := l_target_job_id;
2227 END IF;
2228 IF l_target_job_id IS NOT NULL THEN
2229 l_position_data_rec.job_id := l_target_job_id;
2230 END IF;
2231 --Begin Bug# 14410401
2232 IF l_target_functional_class IS NOT NULL
2233 AND l_target_functional_class <> l_vac_functional_class THEN
2234 l_vac_functional_class := l_target_functional_class;
2235 END IF;
2236 --End Bug# 14410401
2237
2238 IF l_target_flsa_category IS NOT NULL
2239 AND l_target_flsa_category <> l_vac_flsa_category THEN
2240 l_vac_flsa_category := l_target_flsa_category;
2241 END IF;
2242
2243 IF l_target_barg_unit_status IS NOT NULL
2244 AND l_target_barg_unit_status <> l_vac_barg_unit_status THEN
2245 l_vac_barg_unit_status := l_target_barg_unit_status;
2246 END IF;
2247
2248 IF l_new_pos_grp1_rec.position_extra_info_id IS NOT NULL
2249 AND (l_target_flsa_category IS NOT NULL
2250 OR l_target_barg_unit_status IS NOT NULL
2251 OR l_target_functional_class IS NOT NULL) THEN --Bug# 14410401
2252 ghr_api.g_api_dml := TRUE;
2253
2254 BEGIN
2255 ghr_position_extra_info_api.update_position_extra_info
2256 (p_position_extra_info_id => l_new_pos_grp1_rec.position_extra_info_id
2257 , p_effective_date => l_effective_date
2258 , p_object_version_number => l_new_pos_grp1_rec.object_version_number
2259 , p_poei_information7 => l_vac_flsa_category
2260 , p_poei_information8 => l_vac_barg_unit_status
2261 , p_poei_information11 => l_vac_functional_class-- Bug# 14410401
2262 , p_poei_information_category => 'GHR_US_POS_GRP1');
2263 EXCEPTION
2264 WHEN others THEN
2265 pr ('Error in UPDATE_position_extra_info is '
2266 || l_errbuf
2267 || ' '
2268 || to_char (l_retcode));
2269
2270 pr ('Err is '
2271 || sqlerrm (sqlcode));
2272
2273 l_noaerrbuf := 'Error in Updating Position extra info'
2274 || ' Sql Err is '
2275 || sqlerrm (sqlcode);
2276
2277 RAISE mnoa_error;
2278 END;
2279
2280 ghr_api.g_api_dml := FALSE;
2281 END IF;
2282 ELSIF l_noa_code = '792' THEN
2283 hr_utility.set_location ('Bargaining unit for NOA: 792 '
2284 || l_target_barg_unit_status, 139);
2285
2286 IF l_vac_barg_unit_status
2287 <> nvl (l_target_barg_unit_status, l_vac_barg_unit_status) THEN
2288 IF l_new_pos_grp1_rec.position_extra_info_id IS NOT NULL THEN
2289 ghr_api.g_api_dml := TRUE;
2290
2291 BEGIN
2292 ghr_position_extra_info_api.update_position_extra_info
2293 (p_position_extra_info_id => l_new_pos_grp1_rec.position_extra_info_id
2294 , p_effective_date => l_effective_date
2295 , p_object_version_number => l_new_pos_grp1_rec.object_version_number
2296 , p_poei_information8 => l_target_barg_unit_status
2297 , p_poei_information_category => 'GHR_US_POS_GRP1');
2298 EXCEPTION
2299 WHEN others THEN
2300 pr ('Error in UPDATE_position_extra_info is '
2301 || l_errbuf
2302 || ' '
2303 || to_char (l_retcode));
2304
2305 pr ('Err is '
2306 || sqlerrm (sqlcode));
2307
2308 l_noaerrbuf := 'Error in Updating Position extra info'
2309 || ' Sql Err is '
2310 || sqlerrm (sqlcode);
2311
2312 RAISE mnoa_error;
2313 END;
2314
2315 ghr_api.g_api_dml := FALSE;
2316 END IF;
2317 END IF;
2318
2319 IF l_target_duty_station_id IS NOT NULL THEN
2320 FOR c_get_loc_id IN get_loc_id (l_duty_station_code) LOOP
2321 l_vac_duty_location_id := c_get_loc_id.location_id;
2322 END LOOP;
2323
2324 IF p_location_id <> l_vac_duty_location_id THEN
2325 l_position_data_rec.location_id := l_vac_duty_location_id;
2326 END IF;
2327 END IF;
2328 END IF;
2329
2330 BEGIN
2331
2332 update_position_info (l_position_data_rec);
2333 ghr_validate_perwsdpo.update_posn_status(l_position_id,l_effective_date);
2334 ghr_mlc_pkg.upd_ext_info_to_null(l_position_id,l_effective_date);
2335 --Bug# 14475560
2336 ghr_mto_int.log_message( p_procedure => 'Successful Completion',
2337 p_message => 'Vacant Position : '||l_position_title ||'.'|| l_position_number
2338 ||'.'||l_position_seq_no||'.'||l_sub_element_code||
2339 ' Mass NOA Selectable : '||
2340 p_mass_salary ||' Vacant pos Successfully completed');
2341 --End Bug# 14475560
2342 EXCEPTION
2343 WHEN others THEN
2344 l_noaerrbuf := 'Error in ghr_sf52_pos_UPDATE.UPDATE_position_info'
2345 || ' Sql Err is '
2346 || sqlerrm (sqlcode);
2347
2348 RAISE mnoa_error;
2349 END;
2350 EXCEPTION
2351 WHEN mnoa_error THEN
2352 RAISE;
2353 WHEN others THEN
2354 l_noaerrbuf := 'Error in vacant_pos_process '
2355 || ' Sql Err is '
2356 || sqlerrm (sqlcode);
2357
2358 RAISE mnoa_error;
2359 END;
2360 END IF;
2361 END IF;
2362 EXCEPTION
2363 WHEN mnoa_error THEN
2364 hr_utility.set_location ('Error occurred in '
2365 || l_proc
2366 || ' Sql error '
2367 || sqlerrm (sqlcode), 10);
2368
2369 BEGIN
2370 IF upper (p_action) <> 'SHOW' THEN
2371 ROLLBACK TO execute_noa_vacent;
2372 END IF;
2373 EXCEPTION
2374 WHEN others THEN
2375 NULL;
2376 END;
2377
2378 l_log_text := 'Error in mnoa '
2379 || l_proc
2380 || ' '
2381 || ' For Mass NOA Selectable : '
2382 || p_mass_salary
2383 || ' Pos Name: '
2384 || l_position_title
2385 || ' position No: '
2386 || l_position_id
2387 || ' '
2388 || l_noaerrbuf;
2389
2390 hr_utility.set_location ('before creating entry in log file', 10);
2391
2392 l_recs_failed := l_recs_failed + 1;
2393
2394 BEGIN
2395 ghr_mto_int.log_message
2396 (p_procedure => g_proc
2397 , p_message => l_log_text);
2398 EXCEPTION
2399 WHEN others THEN
2400 hr_utility.set_message (8301, 'GHR_38475_ERROR_LOG_FAILURE');
2401
2402 hr_utility.raise_error;
2403 END;
2404 WHEN others THEN
2405 hr_utility.set_location ('Error (Others2) occurred in '
2406 || l_proc
2407 || ' Sql error '
2408 || sqlerrm (sqlcode), 30);
2409
2410 BEGIN
2411 ROLLBACK TO execute_noa_vacent;
2412 EXCEPTION
2413 WHEN others THEN
2414 NULL;
2415 END;
2416
2417 l_log_text := 'Error in '
2418 || l_proc
2419 || ' For Mass NOA Selectable : '
2420 || p_mass_salary
2421 || ' Sql Err is '
2422 || sqlerrm (sqlcode);
2423
2424 l_recs_failed := l_recs_failed + 1;
2425
2426 hr_utility.set_location ('before creating entry in log file', 30);
2427
2428 p_errbuf := 'Error in '
2429 || l_proc
2430 || ' Details in GHR_PROCESS_LOG';
2431
2432 p_retcode := 2;
2433
2434 IF upper (p_action) = 'CREATE' THEN
2435 UPDATE ghr_mass_salaries
2436 SET submit_flag = 'E'
2437 WHERE rowid = l_rowid;
2438
2439 COMMIT;
2440 END IF;
2441
2442 BEGIN
2443 ghr_mto_int.log_message
2444 (p_procedure => g_proc
2445 , p_message => l_log_text);
2446 EXCEPTION
2447 WHEN others THEN
2448 hr_utility.set_message (8301, 'Create Error Log failed');
2449
2450 hr_utility.raise_error;
2451 END;
2452 END vacant_pos_process;
2453
2454
2455 PROCEDURE mnoa_process
2456 (p_assignment_id IN per_assignments_f.assignment_id%TYPE
2457 , p_person_id IN per_assignments_f.person_id%TYPE
2458 , p_position_id IN per_assignments_f.position_id%TYPE
2459 , p_grade_id IN per_assignments_f.grade_id%TYPE
2460 , p_business_group_id IN per_assignments_f.business_group_id%TYPE
2461 , p_location_id IN per_assignments_f.location_id%TYPE
2462 , p_organization_id IN per_assignments_f.organization_id%TYPE
2463 , p_date_of_birth IN date
2464 , p_first_name IN per_people_f.first_name%TYPE
2465 , p_last_name IN per_people_f.last_name%TYPE
2466 , p_full_name IN per_people_f.full_name%TYPE
2467 , p_middle_names IN per_people_f.middle_names%TYPE
2468 , p_national_identifier IN per_people_f.national_identifier%TYPE
2469 , p_employee_number IN per_people_f.employee_number%TYPE
2470 , p_personnel_office_id IN varchar2
2471 , p_org_structure_id IN varchar2
2472 , p_position_title IN varchar2
2473 , p_position_number IN varchar2
2474 , p_position_seq_no IN varchar2
2475 , p_subelem_code IN varchar2
2476 , p_duty_station_id IN ghr_duty_stations_f.duty_station_id%TYPE
2477 , p_tenure IN varchar2
2478 , p_annuitant_indicator IN varchar2
2479 , p_pay_rate_determinant IN varchar2
2480 , p_work_schedule IN varchar2
2481 , p_part_time_hour IN varchar2
2482 , p_to_grade_id IN per_assignments_f.grade_id%TYPE
2483 , p_pay_plan IN varchar2
2484 , p_to_pay_plan IN varchar2
2485 , p_pay_table_id IN number
2486 , p_grade_or_level IN varchar2
2487 , p_to_grade_or_level IN varchar2
2488 , p_step_or_rate IN varchar2
2489 , p_pay_basis IN varchar2) IS
2490
2491 BEGIN
2492 SAVEPOINT execute_noa_sp;
2493 l_sel_flg := NULL;
2494 l_pay_calc_in_data := NULL;
2495 l_pay_calc_out_data := NULL;
2496 l_assignment_id := p_assignment_id;
2497 l_position_id := p_position_id;
2498 l_grade_id := p_grade_id;
2499 l_business_group_id := p_business_group_id;
2500 l_location_id := p_location_id;
2501 l_personnel_office_id := p_personnel_office_id;
2502 l_org_structure_id := p_org_structure_id;
2503 l_position_title := p_position_title;
2504 l_position_number := p_position_number;
2505 l_position_seq_no := p_position_seq_no;
2506 l_sub_element_code := p_subelem_code;
2507 l_duty_station_id := p_duty_station_id;
2508 l_tenure := p_tenure;
2509 l_annuitant_indicator := p_annuitant_indicator;
2510 l_pay_rate_determinant := p_pay_rate_determinant;
2511 l_work_schedule := P_work_schedule;
2512 l_part_time_hour := p_part_time_hour;
2513 l_to_grade_id := p_to_grade_id;
2514 l_pay_plan := p_pay_plan;
2515 l_to_pay_plan := p_to_pay_plan;
2516 l_pay_table_id := p_pay_table_id;
2517 l_grade_or_level := p_grade_or_level;
2518 l_to_grade_or_level := p_to_grade_or_level;
2519 l_step_or_rate := P_step_or_rate;
2520 l_pay_basis := p_pay_basis;
2521
2522 IF check_select_flg (p_person_id, upper (p_action)
2523 , l_effective_date, p_mass_salary_id
2524 , l_sel_flg) THEN
2525 BEGIN
2526 -- Bug# 14751973, commented below code
2527 /*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
2528 l_duty_station_id := l_target_duty_station_id;
2529 ELSE
2530 l_duty_station_id := l_duty_station_id;
2531 END IF;*/
2532 ghr_pa_requests_pkg.get_duty_station_details
2533 (p_duty_station_id => l_duty_station_id
2534 , p_effective_date => l_effective_date
2535 , p_duty_station_code => l_duty_station_code
2536 , p_duty_station_desc => l_duty_station_desc);
2537
2538 hr_utility.set_location ('The duty station name is:'|| l_duty_station_code, 12345);
2539
2540 hr_utility.set_location ('The duty station desc is:'|| l_duty_station_desc, 12345);
2541 EXCEPTION
2542 WHEN others THEN
2543 hr_utility.set_location ('Error in Ghr_pa_requests_pkg.get_duty_station_details'|| 'Err is '|| sqlerrm (sqlcode), 20);
2544 l_noaerrbuf := 'Error in get_duty_station_details Sql Err is '|| sqlerrm (sqlcode);
2545 RAISE mnoa_error;
2546 END;
2547 -- Begin Bug# 14751973
2548 IF l_target_duty_station_id IS NOT NULL THEN
2549 BEGIN
2550 ghr_pa_requests_pkg.get_duty_station_details
2551 (p_duty_station_id => l_target_duty_station_id
2552 , p_effective_date => l_effective_date
2553 , p_duty_station_code => l_target_duty_station_code
2554 , p_duty_station_desc => l_target_duty_station_desc);
2555
2556 hr_utility.set_location ('The Target duty station name is:'|| l_target_duty_station_code, 12346);
2557 EXCEPTION
2558 WHEN others THEN
2559 hr_utility.set_location ('Error in Target Ghr_pa_requests_pkg.get_duty_station_details'|| 'Err is '|| sqlerrm (sqlcode), 20);
2560 l_noaerrbuf := 'Error in Target get_duty_station_details Sql Err is '|| sqlerrm (sqlcode);
2561 RAISE mnoa_error;
2562 END;
2563 END IF;
2564 -- End Bug# 14751973
2565 hr_utility.set_location ('Before calling ghr_msl_pkg.get_from_sf52_data_elements :'|| l_proc, 70);
2566 ghr_msl_pkg.get_from_sf52_data_elements (l_assignment_id, l_effective_date
2567 , l_old_basic_pay, l_old_avail_pay
2568 , l_old_loc_diff, l_tot_old_sal
2569 , l_old_auo_pay, l_old_adj_basic_pay
2570 , l_other_pay, l_auo_premium_pay_indicator
2571 , l_ap_premium_pay_indicator, l_retention_allowance
2572 , l_retention_allow_perc, l_supervisory_differential
2573 , l_supervisory_diff_perc, l_staffing_differential);
2574
2575 hr_utility.set_location ('After calling ghr_msl_pkg.get_from_sf52_data_elements :'|| l_proc, 71);
2576 --Begin Bug# 14414972, for NON_PAY_DUTY_STATION family pay calc is not required, so skipping
2577 IF l_noa_code NOT IN ('430','460','471','472') THEN
2578 l_pay_calc_in_data.person_id := p_person_id;
2579 l_pay_calc_in_data.position_id := l_position_id;
2580 l_pay_calc_in_data.noa_code := l_noa_code;
2581 l_pay_calc_in_data.second_noa_code := NULL;
2582 l_pay_calc_in_data.first_action_la_code1 := l_lac_sf52_rec.first_action_la_code1;
2583 l_pay_calc_in_data.effective_date := l_effective_date;
2584 l_pay_calc_in_data.pay_rate_determinant := l_pay_rate_determinant;
2585 l_pay_calc_in_data.pay_plan := l_pay_plan;
2586 l_pay_calc_in_data.grade_or_level := l_grade_or_level;
2587 l_pay_calc_in_data.step_or_rate := l_step_or_rate;
2588 l_pay_calc_in_data.pay_basis := l_pay_basis;
2589 l_pay_calc_in_data.user_table_id := l_pay_table_id;
2590 l_pay_calc_in_data.duty_station_id := nvl(l_target_duty_station_id,l_duty_station_id);
2591 l_pay_calc_in_data.auo_premium_pay_indicator := l_auo_premium_pay_indicator;
2592 l_pay_calc_in_data.ap_premium_pay_indicator := l_ap_premium_pay_indicator;
2593 l_pay_calc_in_data.retention_allowance := l_retention_allowance;
2594 l_pay_calc_in_data.to_ret_allow_percentage := l_retention_allow_perc;
2595 l_pay_calc_in_data.supervisory_differential := l_supervisory_differential;
2596 l_pay_calc_in_data.staffing_differential := l_staffing_differential;
2597 l_pay_calc_in_data.current_basic_pay := l_old_basic_pay;
2598 l_pay_calc_in_data.current_adj_basic_pay := l_old_adj_basic_pay;
2599 l_pay_calc_in_data.current_step_or_rate := l_step_or_rate;
2600 FOR l_get_noa_fam IN get_noa_fam LOOP
2601 l_pay_calc_in_data.noa_family_code := l_get_noa_fam.noa_family_code;
2602 END LOOP;
2603
2604 l_pay_calc_in_data.pa_request_id := NULL;
2605
2606 BEGIN
2607 hr_utility.set_location ('Pay calc Calling :'|| l_pay_calc_in_data.noa_code, 123);
2608
2609 ghr_pay_calc.sql_main_pay_calc (l_pay_calc_in_data, l_pay_calc_out_data
2610 , l_message_set, l_calculated);
2611
2612 IF l_message_set THEN
2613 hr_utility.set_location (l_proc, 40);
2614 l_calculated := FALSE;
2615 l_noaerrbuf := hr_utility.get_message;
2616 END IF;
2617 EXCEPTION
2618 WHEN mnoa_error THEN
2619 g_proc := 'ghr_pay_calc';
2620 RAISE;
2621 WHEN others THEN
2622 hr_utility.set_location ('Pay Calc Exception :'|| l_pay_calc_in_data.noa_code, 124);
2623
2624 IF ghr_pay_calc.gm_unadjusted_pay_flg = 'Y' THEN
2625 l_comment := 'MNOA:Error: Unadjusted Basic Pay must be entered in Employee record.';
2626 ELSE
2627 l_comment := 'MNOA:Error: See process log for details.';
2628 END IF;
2629
2630 IF upper (p_action) IN ('SHOW') THEN
2631 -- Begin Bug# 14751973
2632 create_mass_noa_prev
2633 (p_effective_date => l_effective_date
2634 , p_date_of_birth => p_date_of_birth
2635 , p_full_name => p_full_name
2636 , p_national_identifier => p_national_identifier
2637 , p_employee_number => p_employee_number
2638 , p_duty_station_id => l_duty_station_id
2639 , p_duty_station_code => l_duty_station_code
2640 , p_duty_station_desc => l_duty_station_desc
2641 , p_personnel_office_id => l_personnel_office_id
2642 , p_basic_pay => l_old_basic_pay
2643 , p_new_basic_pay => l_new_basic_pay
2644 , p_adj_basic_pay => l_old_adj_basic_pay
2645 , p_new_adj_basic_pay => l_new_adj_basic_pay
2646 , p_old_loc_adj => l_old_loc_diff
2647 , p_new_loc_adj => l_new_locality_adj
2648 , p_tot_old_sal => l_tot_old_sal
2649 , p_tot_new_sal => l_new_total_salary
2650 , p_position_id => l_position_id
2651 , p_position_title => l_position_title
2652 , p_position_number => l_position_number
2653 , p_position_seq_no => l_position_seq_no
2654 , p_agency_sub_element_code => l_sub_element_code
2655 , p_person_id => p_person_id
2656 , p_mass_salary_id => l_mass_salary_id
2657 , p_sel_flg => l_sel_flg
2658 , p_action => p_action
2659 , p_assignment_id => l_assignment_id
2660 , p_organization_name => l_org_name
2661 , p_to_agency_code => NULL
2662 , p_to_duty_station_id => NULL
2663 , p_to_duty_station_code => NULL
2664 , p_to_duty_station_desc => NULL
2665 , p_occ_code => NULL
2666 , p_to_occ_code => NULL
2667 , p_from_bargaining_unit_status => NULL
2668 , p_to_bargaining_unit_status => NULL
2669 , p_from_flsa_category => NULL
2670 , p_to_flsa_category => NULL
2671 , p_from_functional_class => NULL
2672 , p_to_functional_class => NULL
2673 , p_work_schedule => l_work_schedule);
2674
2675 END IF;
2676
2677 ins_upd_per_extra_info (p_person_id, l_effective_date
2678 , l_sel_flg, l_comment
2679 , p_mass_salary_id);
2680 l_comment := NULL;
2681 hr_utility.set_location ('Error in Ghr_pay_calc.sql_main_pay_calc '|| 'Err is '|| sqlerrm (sqlcode), 20);
2682 l_noaerrbuf := 'Error in ghr_pay_calc Sql Err is '|| sqlerrm (sqlcode);
2683 g_proc := 'ghr_pay_calc';
2684 RAISE mnoa_error;
2685 END;
2686 --Begin Bug# 14751973, for some NOAs with step 00 to pay values are getting null
2687 l_pay_calc_out_data.basic_pay := nvl(l_pay_calc_out_data.basic_pay,l_old_basic_pay);
2688 l_pay_calc_out_data.locality_adj := nvl(l_pay_calc_out_data.locality_adj,l_old_loc_diff);
2689 l_pay_calc_out_data.adj_basic_pay := nvl(l_pay_calc_out_data.adj_basic_pay,l_old_adj_basic_pay);
2690 l_pay_calc_out_data.au_overtime := nvl(l_pay_calc_out_data.au_overtime,l_old_auo_pay);
2691 l_pay_calc_out_data.availability_pay := nvl(l_pay_calc_out_data.availability_pay,l_old_avail_pay);
2692 l_pay_calc_out_data.other_pay_amount := nvl(l_pay_calc_out_data.other_pay_amount,l_other_pay);
2693 l_pay_calc_out_data.total_salary := nvl(l_pay_calc_out_data.total_salary,l_tot_old_sal);
2694 --End Bug# 14751973
2695 l_new_basic_pay := l_pay_calc_out_data.basic_pay;
2696 l_new_locality_adj := l_pay_calc_out_data.locality_adj;
2697 l_new_adj_basic_pay := l_pay_calc_out_data.adj_basic_pay;
2698 l_new_au_overtime := l_pay_calc_out_data.au_overtime;
2699 l_new_availability_pay := l_pay_calc_out_data.availability_pay;
2700 l_out_pay_plan := l_pay_calc_out_data.out_to_pay_plan;
2701 l_out_grade_id := l_pay_calc_out_data.out_to_grade_id;
2702 l_out_grade_or_level := l_pay_calc_out_data.out_to_grade_or_level;
2703 l_out_pay_rate_determinant := l_pay_calc_out_data.out_pay_rate_determinant;
2704 l_out_step_or_rate := l_pay_calc_out_data.out_step_or_rate;
2705 l_new_retention_allowance := l_pay_calc_out_data.retention_allowance;
2706 l_new_supervisory_differential := l_supervisory_differential;
2707 l_new_other_pay_amount := l_pay_calc_out_data.other_pay_amount;
2708 l_entitled_other_pay := l_new_other_pay_amount;
2709 IF l_new_other_pay_amount = 0 THEN
2710 l_new_other_pay_amount := NULL;
2711 END IF;
2712
2713 l_new_total_salary := l_pay_calc_out_data.total_salary;
2714
2715 BEGIN
2716 l_capped_other_pay := ghr_pa_requests_pkg2.get_cop
2717 (p_assignment_id => l_assignment_id
2718 , p_effective_date => l_effective_date);
2719
2720 l_old_capped_other_pay := l_capped_other_pay;
2721 IF hr_utility.debug_enabled = TRUE THEN
2722 hr_utility.set_location ('Before Pay Cap '|| l_proc, 21);
2723 hr_utility.set_location ('l_effective_date '|| l_effective_date, 21);
2724 hr_utility.set_location ('l_out_pay_rate_determinant '|| l_out_pay_rate_determinant, 21);
2725 hr_utility.set_location ('l_pay_plan '|| l_pay_plan, 21);
2726 hr_utility.set_location ('l_position_id '|| to_char (l_position_id), 21);
2727 hr_utility.set_location ('l_pay_basis '|| l_pay_basis, 21);
2728 hr_utility.set_location ('person_id '|| to_char (p_person_id), 21);
2729 hr_utility.set_location ('l_new_basic_pay ' || to_char (l_new_basic_pay), 21);
2730 hr_utility.set_location ('l_new_locality_adj '|| to_char (l_new_locality_adj), 21);
2731 hr_utility.set_location ('l_new_adj_basic_pay '|| to_char (l_new_adj_basic_pay), 21);
2732 hr_utility.set_location ('l_new_total_salary '|| to_char (l_new_total_salary), 21);
2733 hr_utility.set_location ('l_entitled_other_pay '|| to_char (l_entitled_other_pay), 21);
2734 hr_utility.set_location ('l_capped_other_pay '|| to_char (l_capped_other_pay), 21);
2735 hr_utility.set_location ('l_new_retention_allowance '|| to_char (l_new_retention_allowance), 21);
2736 hr_utility.set_location ('l_new_supervisory_differential ' || to_char (l_new_supervisory_differential), 21);
2737 hr_utility.set_location ('l_staffing_differential '|| to_char (l_staffing_differential), 21);
2738 hr_utility.set_location ('l_new_au_overtime '|| to_char (l_new_au_overtime), 21);
2739 hr_utility.set_location ('l_new_availability_pay '|| to_char (l_new_availability_pay), 21);
2740 END IF;
2741
2742 ghr_pay_caps.do_pay_caps_main
2743 (p_pa_request_id => NULL
2744 , p_effective_date => l_effective_date
2745 , p_duty_station_id => nvl(l_target_duty_station_id,l_duty_station_id)
2746 , p_pay_rate_determinant => nvl (l_out_pay_rate_determinant, l_pay_rate_determinant)
2747 , p_pay_plan => l_pay_plan
2748 , p_to_position_id => l_position_id
2749 , p_pay_basis => l_pay_basis
2750 , p_person_id => p_person_id
2751 , p_noa_code => l_noa_code
2752 , p_basic_pay => l_new_basic_pay
2753 , p_locality_adj => l_new_locality_adj
2754 , p_adj_basic_pay => l_new_adj_basic_pay
2755 , p_total_salary => l_new_total_salary
2756 , p_other_pay_amount => l_entitled_other_pay
2757 , p_capped_other_pay => l_capped_other_pay
2758 , p_retention_allowance => l_new_retention_allowance
2759 , p_retention_allow_percentage => l_retention_allow_perc
2760 , p_supervisory_allowance => l_new_supervisory_differential
2761 , p_staffing_differential => l_staffing_differential
2762 , p_au_overtime => l_new_au_overtime
2763 , p_availability_pay => l_new_availability_pay
2764 , p_adj_basic_message => l_adj_basic_message
2765 , p_pay_cap_message => l_pay_cap_message
2766 , p_pay_cap_adj => l_temp_retention_allowance
2767 , p_open_pay_fields => l_open_pay_fields_caps
2768 , p_message_set => l_message_set_caps
2769 , p_total_pay_check => l_total_pay_check);
2770
2771 l_new_other_pay_amount := nvl (l_capped_other_pay, l_entitled_other_pay);
2772
2773 IF hr_utility.debug_enabled = TRUE THEN
2774 hr_utility.set_location ('After Pay Cap '|| l_proc, 22);
2775 hr_utility.set_location ('l_effective_date '|| l_effective_date, 22);
2776 hr_utility.set_location ('l_out_pay_rate_determinant ' || l_out_pay_rate_determinant, 22);
2777 hr_utility.set_location ('l_pay_plan ' || l_pay_plan, 22);
2778 hr_utility.set_location ('l_position_id '|| to_char (l_position_id), 22);
2779 hr_utility.set_location ('l_pay_basis '|| l_pay_basis, 22);
2780 hr_utility.set_location ('person_id '|| to_char (p_person_id), 22);
2781 hr_utility.set_location ('l_new_basic_pay '|| to_char (l_new_basic_pay), 22);
2782 hr_utility.set_location ('l_new_locality_adj '|| to_char (l_new_locality_adj), 22);
2783 hr_utility.set_location ('l_new_adj_basic_pay '|| to_char (l_new_adj_basic_pay), 22);
2784 hr_utility.set_location ('l_new_total_salary '|| to_char (l_new_total_salary), 22);
2785 hr_utility.set_location ('l_entitled_other_pay '|| to_char (l_entitled_other_pay), 22);
2786 hr_utility.set_location ('l_capped_other_pay '|| to_char (l_capped_other_pay), 22);
2787 hr_utility.set_location ('l_new_retention_allowance '|| to_char (l_new_retention_allowance), 22);
2788 hr_utility.set_location ('l_new_supervisory_differential '|| to_char (l_new_supervisory_differential), 22);
2789 hr_utility.set_location ('l_staffing_differential '|| to_char (l_staffing_differential), 22);
2790 hr_utility.set_location ('l_new_au_overtime '|| to_char (l_new_au_overtime), 22);
2791 hr_utility.set_location ('l_new_availability_pay '|| to_char (l_new_availability_pay), 22);
2792 END IF;
2793
2794 IF l_pay_cap_message THEN
2795 IF nvl (l_temp_retention_allowance, 0) > 0 THEN
2796 l_comment := 'MNOA: Exceeded Total Cap - reduce Retention Allow to '
2797 || to_char (l_temp_retention_allowance);
2798 l_sel_flg := 'N';
2799 ELSE
2800 l_comment := 'MNOA: Exceeded Total cap - pls review.';
2801 END IF;
2802 ELSIF l_adj_basic_message THEN
2803 l_comment := 'MNOA: Exceeded Adjusted Pay Cap - Locality reduced.';
2804 END IF;
2805
2806 IF (l_old_basic_pay > l_new_basic_pay) THEN
2807 l_comment_sal := 'MNOA: From Basic Pay exceeds To Basic Pay.';
2808 END IF;
2809
2810 IF l_pay_cap_message
2811 OR l_adj_basic_message THEN
2812 IF (l_comment_sal IS NOT NULL) THEN
2813 l_comment := l_comment_sal|| ' '|| l_comment;
2814 END IF;
2815
2816 hr_utility.set_location ('Before Calling ins_upd_per_extra_info p_mass_salary_id '|| p_mass_salary_id, 555);
2817
2818 ins_upd_per_extra_info (p_person_id, l_effective_date
2819 , l_sel_flg, l_comment
2820 , p_mass_salary_id);
2821
2822 l_comment := NULL;
2823 ELSIF l_comment_sal IS NOT NULL THEN
2824 hr_utility.set_location (' l_comment_sal is not null '|| p_mass_salary_id, 555);
2825
2826 ins_upd_per_extra_info (p_person_id, l_effective_date
2827 , l_sel_flg, l_comment_sal
2828 , p_mass_salary_id);
2829 END IF;
2830
2831 l_comment_sal := NULL;
2832 EXCEPTION
2833 WHEN mnoa_error THEN
2834 RAISE;
2835 WHEN others THEN
2836 hr_utility.set_location ('Error in ghr_pay_caps.do_pay_caps_main '|| 'Err is '|| sqlerrm (sqlcode), 23);
2837 l_noaerrbuf := 'Error in do_pay_caps_main Sql Err is '|| sqlerrm (sqlcode);
2838 RAISE mnoa_error;
2839 END;
2840 --Begin Bug# 14414972, for NON_PAY_DUTY_STATION family pay calc is not required
2841 ELSE
2842 BEGIN
2843 ghr_msl_pkg.get_pay_plan_and_table_id (l_pay_rate_determinant, p_person_id
2844 , l_position_id, l_effective_date
2845 , l_grade_id, l_assignment_id
2846 , 'SHOW', l_pay_plan
2847 , l_pay_table_id, l_grade_or_level
2848 , l_step_or_rate, l_pay_basis);
2849
2850 EXCEPTION
2851
2852 WHEN OTHERS THEN
2853 hr_utility.set_location ('Error in ghr_msl_pkg.get_pay_plan_and_table_id of '|| ' Err is '|| sqlerrm (sqlcode), 20);
2854 l_pay_plan := p_pay_plan;
2855 l_to_pay_plan := p_to_pay_plan;
2856 l_pay_table_id := p_pay_table_id;
2857 l_grade_or_level := p_grade_or_level;
2858 l_step_or_rate := P_step_or_rate;
2859 l_pay_basis := p_pay_basis;
2860 END;
2861 l_pay_calc_out_data.pay_table_id := l_pay_table_id;
2862 l_pay_calc_out_data.calculation_pay_table_id := l_pay_table_id;
2863
2864 END IF; -- IF l_noa_code NOT IN ('430','460','471','472')
2865 --End Bug# 14414972
2866 IF upper (p_action) IN ('SHOW', 'REPORT') THEN
2867 hr_utility.set_location ('Calling create_mass_noa_prev in p_action '|| p_action, 1331);
2868 -- Begin Bug# 14751973
2869 IF l_noa_code IN ('800','792') THEN
2870 ghr_pa_requests_pkg.get_sf52_pos_ddf_details
2871 (p_position_id => l_position_id
2872 ,p_date_Effective => l_effective_date
2873 ,p_flsa_category => l_old_flsa_category
2874 ,p_bargaining_unit_status => l_old_barg_unit_status
2875 ,p_work_schedule => l_dummy
2876 ,p_functional_class => l_old_functional_class
2877 ,p_supervisory_status => l_dummy
2878 ,p_position_occupied => l_dummy
2879 ,p_appropriation_code1 => l_dummy
2880 ,p_appropriation_code2 => l_dummy
2881 ,p_personnel_office_id => l_dummy
2882 ,p_office_symbol => l_dummy
2883 ,p_part_time_hours => l_dummy
2884 );
2885 IF l_noa_code ='800' THEN
2886 l_old_occ_code := ghr_pa_requests_pkg.get_occ_series(l_position_id,l_effective_date,l_business_group_id);
2887 ELSIF l_noa_code ='792' THEN
2888 l_old_flsa_category := NULL;
2889 l_old_functional_class := NULL;
2890 END IF;
2891 END IF;
2892 create_mass_noa_prev
2893 (p_effective_date => l_effective_date
2894 , p_date_of_birth => p_date_of_birth
2895 , p_full_name => p_full_name
2896 , p_national_identifier => p_national_identifier
2897 , p_employee_number => p_employee_number
2898 , p_duty_station_id => l_duty_station_id
2899 , p_duty_station_code => l_duty_station_code
2900 , p_duty_station_desc => l_duty_station_desc
2901 , p_personnel_office_id => l_personnel_office_id
2902 , p_basic_pay => l_old_basic_pay
2903 , p_new_basic_pay => l_new_basic_pay
2904 , p_adj_basic_pay => l_old_adj_basic_pay
2905 , p_new_adj_basic_pay => l_new_adj_basic_pay
2906 , p_old_loc_adj => l_old_loc_diff
2907 , p_new_loc_adj => l_new_locality_adj
2908 , p_tot_old_sal => l_tot_old_sal
2909 , p_tot_new_sal => l_new_total_salary
2910 , p_position_id => l_position_id
2911 , p_position_title => l_position_title
2912 , p_position_number => l_position_number
2913 , p_position_seq_no => l_position_seq_no
2914 , p_agency_sub_element_code => l_sub_element_code
2915 , p_person_id => p_person_id
2916 , p_mass_salary_id => l_mass_salary_id
2917 , p_sel_flg => l_sel_flg
2918 , p_action => p_action
2919 , p_assignment_id => l_assignment_id
2920 , p_organization_name => l_org_name
2921 , p_to_agency_code => l_target_agency_code_subelmt
2922 , p_to_duty_station_id => l_target_duty_station_id
2923 , p_to_duty_station_code => l_target_duty_station_code
2924 , p_to_duty_station_desc => l_target_duty_station_desc
2925 , p_occ_code => l_old_occ_code
2926 , p_to_occ_code => l_target_occ_code
2927 , p_from_bargaining_unit_status => l_old_barg_unit_status
2928 , p_to_bargaining_unit_status => l_target_barg_unit_status
2929 , p_from_flsa_category => l_old_flsa_category
2930 , p_to_flsa_category => l_target_flsa_category
2931 , p_from_functional_class => l_old_functional_class
2932 , p_to_functional_class => l_target_functional_class
2933 , p_work_schedule => l_work_schedule);
2934 -- End Bug# 14751973
2935 ELSIF upper (p_action) = 'CREATE' THEN
2936 BEGIN
2937 ghr_msl_pkg.get_pay_plan_and_table_id (l_pay_rate_determinant, p_person_id
2938 , l_position_id, l_effective_date
2939 , l_grade_id, l_assignment_id
2940 , 'CREATE', l_pay_plan
2941 , l_pay_table_id, l_grade_or_level
2942 , l_step_or_rate, l_pay_basis);
2943 EXCEPTION
2944 WHEN ghr_msl_pkg.msl_error THEN
2945 l_noaerrbuf := hr_utility.get_message;
2946 RAISE mnoa_error;
2947 END;
2948 hr_utility.set_location ('Callin ghr_msl_pkg.assign_to_sf52_rec ' || l_sf52_rec.first_noa_information1, 233);
2949 ghr_msl_pkg.assign_to_sf52_rec (p_person_id, p_first_name
2950 , p_last_name, p_middle_names
2951 , p_national_identifier, p_date_of_birth
2952 , l_effective_date, l_assignment_id
2953 , l_tenure, nvl (l_out_grade_id, l_to_grade_id)
2954 , nvl (l_out_pay_plan, l_to_pay_plan), nvl (l_out_grade_or_level, l_to_grade_or_level)
2955 , l_step_or_rate, l_annuitant_indicator
2956 , nvl (l_out_pay_rate_determinant, l_pay_rate_determinant), l_work_schedule
2957 , l_part_time_hour, l_pos_ei_data.poei_information7
2958 , l_pos_ei_data.poei_information8, l_pos_ei_data.poei_information11
2959 , l_pos_ei_data.poei_information16, l_new_basic_pay
2960 , l_new_locality_adj, l_new_adj_basic_pay
2961 , l_new_total_salary, l_other_pay
2962 , l_new_other_pay_amount, l_new_au_overtime
2963 , l_new_availability_pay, l_new_retention_allowance
2964 , l_retention_allow_perc, l_new_supervisory_differential
2965 , l_supervisory_diff_perc, l_staffing_differential
2966 , nvl(l_target_duty_station_id,l_duty_station_id), nvl(l_target_duty_station_code,l_duty_station_code)
2967 , nvl(l_target_duty_station_desc,l_duty_station_desc), l_pay_rate_determinant
2968 , l_pay_calc_out_data.pay_table_id, l_pay_calc_out_data.calculation_pay_table_id
2969 , l_lac_sf52_rec, l_sf52_rec);
2970
2971 l_sf52_rec.first_noa_information1 := fnd_date.date_to_canonical (l_nte_date);
2972
2973 BEGIN
2974 ghr_mass_actions_pkg.pay_calc_rec_to_sf52_rec (l_pay_calc_out_data, l_sf52_rec);
2975 EXCEPTION
2976 WHEN others THEN
2977 hr_utility.set_location ('Error in Ghr_mass_actions_pkg.pay_calc_rec_to_sf52_rec '|| 'Err is '|| sqlerrm (sqlcode), 20);
2978 l_noaerrbuf := 'Error in ghr_mass_act_pkg.pay_calc_to_sf52 Sql Err is '|| sqlerrm (sqlcode);
2979 RAISE mnoa_error;
2980 END;
2981
2982 BEGIN
2983 l_sf52_rec.mass_action_id := p_mass_salary_id;
2984 l_sf52_rec.rpa_type := 'MNOA';
2985 IF l_noa_code ='800' THEN
2986 hr_utility.set_location ('Assigning RPA Elements for NOA: 800 '|| l_proc, 139);
2987 l_sf52_rec.to_occ_code := l_target_occ_code;
2988 l_sf52_rec.to_job_id := l_target_job_id;
2989 l_sf52_rec.functional_class := l_target_functional_class; --Bug# 14410401
2990 l_sf52_rec.flsa_category := l_target_flsa_category;
2991 l_sf52_rec.bargaining_unit_status := l_target_barg_unit_status;
2992 l_sf52_rec.agency_code := l_target_agency_code_subelmt;
2993 ELSIF l_noa_code ='792' THEN
2994 hr_utility.set_location ('Assigning Duty station Details for NOA: 792 '|| l_target_duty_station_id, 139);
2995 l_sf52_rec.bargaining_unit_status := l_target_barg_unit_status;
2996 IF l_target_duty_station_id IS NOT NULL THEN
2997 FOR c_get_loc_id IN get_loc_id(l_sf52_rec.duty_station_code) LOOP
2998 l_sf52_rec.duty_station_location_id := c_get_loc_id.location_id;
2999 END LOOP;
3000 END IF;
3001 END IF;
3002
3003 g_proc := 'Create_sf52_recrod';
3004
3005 hr_utility.set_location ('Calling create_sf52_for_mass_changes '|| l_proc, 140);
3006
3007 ghr_mass_changes.create_sf52_for_mass_changes
3008 (p_mass_action_type => 'MASS_NOA_SEL'
3009 , p_pa_request_rec => l_sf52_rec
3010 , p_errbuf => l_errbuf
3011 , p_retcode => l_retcode);
3012
3013 DECLARE
3014 l_pa_request_number ghr_pa_requests.request_number%TYPE;
3015 BEGIN
3016 l_pa_request_number := l_sf52_rec.request_number|| '-'|| p_mass_salary_id;
3017 ghr_par_upd.upd
3018 (p_pa_request_id => l_sf52_rec.pa_request_id
3019 , p_object_version_number => l_sf52_rec.object_version_number
3020 , p_request_number => l_pa_request_number);
3021 END;
3022
3023 BEGIN
3024 IF l_noa_code IN ('472', '292','430', '460', '471','280','800') THEN
3025 hr_utility.set_location ('Calling refresh_ei_details '|| l_proc, 140);
3026
3027 refresh_ei_details
3028 (p_noa_action => l_noa_code
3029 , p_effective_date => l_effective_date
3030 , p_person_id => p_person_id
3031 , p_work_schedule => p_work_schedule
3032 , p_pa_request_id => l_sf52_rec.pa_request_id);
3033 END IF;
3034 END;
3035
3036 IF l_errbuf IS NULL THEN
3037 pr ('No error in create sf52 ');
3038 hr_utility.set_location ('Before commiting', 2);
3039
3040 ghr_mto_int.log_message
3041 (p_procedure => 'Successful Completion'
3042 , p_message => 'Name: '
3043 || p_full_name
3044 || ' Emp No: '
3045 || p_employee_number
3046 || ' Mass NOA Selectable : '
3047 || p_mass_salary
3048 || ' SF52 Successfully completed');
3049
3050 ghr_msl_pkg.create_lac_remarks (l_pa_request_id, l_sf52_rec.pa_request_id);
3051
3052 g_proc := 'update_SEL_FLG';
3053 update_sel_flg (p_person_id, l_effective_date);
3054 COMMIT;
3055 ELSE
3056 pr ('Error in create sf52', l_errbuf);
3057 hr_utility.set_location ('Error in '|| to_char (p_position_id), 20);
3058 RAISE mnoa_error;
3059 END IF;
3060 EXCEPTION
3061 WHEN mnoa_error THEN
3062 RAISE;
3063 WHEN others THEN
3064 l_noaerrbuf := 'Error in ghr_mass_chg.create_sf52 '|| ' Sql Err is '|| sqlerrm (sqlcode);
3065 RAISE mnoa_error;
3066 END;
3067 END IF;
3068 END IF;--IF check_select_flg
3069 l_row_cnt := l_row_cnt + 1;
3070
3071 IF upper (p_action) <> 'CREATE' THEN
3072 IF l_row_cnt > 50 THEN
3073 COMMIT;
3074 l_row_cnt := 0;
3075 END IF;
3076 END IF;
3077 EXCEPTION
3078 WHEN mnoa_error THEN
3079 hr_utility.set_location ('Error occurred in ' || l_proc || ' Sql error ' || sqlerrm (sqlcode), 10);
3080
3081 BEGIN
3082 IF upper (p_action) <> 'SHOW' THEN
3083 ROLLBACK TO execute_noa_sp;
3084 END IF;
3085 EXCEPTION
3086 WHEN others THEN
3087 NULL;
3088 END;
3089
3090 l_log_text := 'Error in mnoa '
3091 || l_proc
3092 || ' '
3093 || ' For Mass NOA Selectable : '
3094 || p_mass_salary
3095 || ' Name: '
3096 || p_full_name
3097 || ' Emp No: '
3098 || p_employee_number
3099 || ' '
3100 || l_noaerrbuf;
3101
3102 hr_utility.set_location ('before creating entry in log file', 10);
3103 l_recs_failed := l_recs_failed + 1;
3104 BEGIN
3105 ghr_mto_int.log_message
3106 (p_procedure => g_proc
3107 , p_message => l_log_text);
3108 EXCEPTION
3109 WHEN others THEN
3110 hr_utility.set_message (8301, 'GHR_38475_ERROR_LOG_FAILURE');
3111 hr_utility.raise_error;
3112 END;
3113 WHEN others THEN
3114 hr_utility.set_location ('Error (Others2) occurred in ' || l_proc || ' Sql error ' || sqlerrm (sqlcode), 30);
3115 BEGIN
3116 ROLLBACK TO execute_noa_sp;
3117 EXCEPTION
3118 WHEN others THEN
3119 NULL;
3120 END;
3121
3122 l_log_text := 'Error in '
3123 || l_proc
3124 || ' For Mass NOA Selectable : '
3125 || p_mass_salary
3126 || ' Sql Err is '
3127 || sqlerrm (sqlcode);
3128
3129 l_recs_failed := l_recs_failed + 1;
3130 hr_utility.set_location ('before creating entry in log file', 30);
3131
3132 p_errbuf := 'Error in '|| l_proc|| ' Details in GHR_PROCESS_LOG';
3133 p_retcode := 2;
3134
3135 IF upper (p_action) = 'CREATE' THEN
3136 UPDATE ghr_mass_salaries
3137 SET submit_flag = 'E'
3138 WHERE rowid = l_rowid;
3139
3140 COMMIT;
3141 END IF;
3142
3143 BEGIN
3144 ghr_mto_int.log_message
3145 (p_procedure => g_proc
3146 , p_message => l_log_text);
3147 EXCEPTION
3148 WHEN others THEN
3149 hr_utility.set_message (8301, 'Create Error Log failed');
3150 hr_utility.raise_error;
3151 END;
3152 END mnoa_process;
3153 BEGIN
3154 hr_utility.set_location ('Entering main execute_mnoa '|| l_proc, 5);
3155 p_retcode := 0;
3156
3157 BEGIN
3158 FOR msnoa IN ghr_msnoa (p_mass_salary_id) LOOP
3159 hr_utility.set_location ('Mass Criteria name '|| msnoa.name, 51);
3160 p_mass_salary := msnoa.name;
3161 l_effective_date := msnoa.effective_date;
3162 l_mass_salary_id := msnoa.mass_salary_id;
3163 l_user_table_id := msnoa.user_table_id;
3164 l_submit_flag := msnoa.submit_flag;
3165 l_executive_order_number := msnoa.executive_order_number;
3166 l_executive_order_date := msnoa.executive_order_date;
3167 l_opm_issuance_number := msnoa.opm_issuance_number;
3168 l_opm_issuance_date := msnoa.opm_issuance_date;
3169 l_pa_request_id := msnoa.pa_request_id;
3170 l_rowid := msnoa.rowid;
3171 l_p_organization_id := msnoa.organization_id;
3172 l_p_duty_station_id := msnoa.duty_station_id;
3173 l_p_personnel_office_id := msnoa.personnel_office_id;
3174 l_p_agency_code_subelement := msnoa.agency_code_subelement;
3175 l_p_locality_area_code := msnoa.locality_pay_area_code;
3176 l_noa_code := msnoa.noa_code;
3177 l_nte_date := msnoa.nte_date;
3178 l_target_barg_unit_status := msnoa.target_bargaining_unit_status;
3179 l_target_flsa_category := msnoa.target_flsa_category;
3180 l_target_occ_code := msnoa.target_occ_code;
3181 l_target_job_id := msnoa.target_job_id;
3182 l_target_agency_code_subelmt := msnoa.target_agency_code_subelement;
3183 l_target_duty_station_id := msnoa.target_duty_station_id;
3184 l_target_duty_station_code := msnoa.target_duty_station_code;
3185 l_target_functional_class := msnoa.target_functional_class;--Bug# 14410401
3186 l_p_job_id := msnoa.job_id;
3187 l_p_bargaining_unit := msnoa.bargaining_unit_status;
3188 l_p_flsa_category := msnoa.flsa_category;
3189 pr ('Pa request id is '|| to_char (l_pa_request_id));
3190 EXIT;
3191 END LOOP;
3192 EXCEPTION
3193 WHEN rec_busy THEN
3194 hr_utility.set_location ('Mass Selectable NOA is in use', 1);
3195 l_noaerrbuf := 'Error in '
3196 || l_proc
3197 || ' Sql Err is '
3198 || sqlerrm (sqlcode);
3199 hr_utility.set_message (8301, 'GHR_38477_LOCK_ON_MSL');
3200 hr_utility.raise_error;
3201 WHEN others THEN
3202 hr_utility.set_location ('Error in '|| l_proc || ' Sql err is ' || sqlerrm (sqlcode), 1);
3203 l_noaerrbuf := 'Error in '
3204 || l_proc
3205 || ' Sql Err is '
3206 || sqlerrm (sqlcode);
3207
3208 RAISE mnoa_error;
3209 END;
3210 g_effective_date := l_effective_date;
3211 IF upper (p_action) = 'CREATE' THEN
3212 ghr_mto_int.set_log_program_name ('GHR_MNOA_PKG');
3213 ELSE
3214 ghr_mto_int.set_log_program_name ('MSNOA_'|| p_mass_salary);
3215 END IF;
3216 get_lac_dtls (l_pa_request_id, l_lac_sf52_rec);
3217 ghr_msl_pkg.g_first_noa_code := l_noa_code;
3218 hr_utility.set_location ('Mass Action name '|| p_action, 52);
3219 --hr_utility.trace_on (NULL, 'raju');
3220 IF l_noa_code IN ('460') THEN
3221 FOR per IN cur_active_people (l_effective_date) LOOP
3222 BEGIN
3223 l_personnel_office_id := NULL;
3224 l_org_structure_id := NULL;
3225 l_position_title := NULL;
3226 l_position_number := NULL;
3227 l_position_seq_no := NULL;
3228 l_sub_element_code := NULL;
3229 l_duty_station_id := NULL;
3230 l_tenure := NULL;
3231 l_annuitant_indicator := NULL;
3232 l_pay_rate_determinant := NULL;
3233 l_work_schedule := NULL;
3234 l_part_time_hour := NULL;
3235 l_to_grade_id := NULL;
3236 l_pay_plan := NULL;
3237 l_to_pay_plan := NULL;
3238 l_pay_table_id := NULL;
3239 l_grade_or_level := NULL;
3240 l_to_grade_or_level := NULL;
3241 l_step_or_rate := NULL;
3242 l_pay_basis := NULL;
3243 l_elig_flag := FALSE;
3244 l_org_name := ghr_mre_pkg.get_organization_name (per.organization_id);
3245 BEGIN
3246 hr_utility.set_location ('Calling fetch_and_validate_emp '|| l_noa_code, 2234);
3247
3248 fetch_and_validate_emp
3249 (p_action => p_action
3250 , p_mass_salary_id => p_mass_salary_id
3251 , p_mass_salary_name => p_mass_salary
3252 , p_full_name => per.full_name
3253 , p_national_identifier => per.national_identifier
3254 , p_employee_number => per.employee_number
3255 , p_assignment_id => per.assignment_id
3256 , p_person_id => per.person_id
3257 , p_position_id => per.position_id
3258 , p_grade_id => per.grade_id
3259 , p_business_group_id => per.business_group_id
3260 , p_location_id => per.location_id
3261 , p_organization_id => per.organization_id
3262 , p_mnoa_organization_id => l_p_organization_id
3263 , p_mnoa_duty_station_id => l_p_duty_station_id
3264 , p_mnoa_personnel_office_id => l_p_personnel_office_id
3265 , p_mnoa_agency_code_subelement => l_p_agency_code_subelement
3266 , p_mnoa_job_id => NULL
3267 , p_mnoa_bargaining_unit => NULL
3268 , p_mnoa_flsa_category => NULL
3269 , p_personnel_office_id => l_personnel_office_id
3270 , p_org_structure_id => l_org_structure_id
3271 , p_position_title => l_position_title
3272 , p_position_number => l_position_number
3273 , p_position_seq_no => l_position_seq_no
3274 , p_subelem_code => l_sub_element_code
3275 , p_duty_station_id => l_duty_station_id
3276 , p_tenure => l_tenure
3277 , p_annuitant_indicator => l_annuitant_indicator
3278 , p_pay_rate_determinant => l_pay_rate_determinant
3279 , p_work_schedule => l_work_schedule
3280 , p_part_time_hour => l_part_time_hour
3281 , p_to_grade_id => l_to_grade_id
3282 , p_pay_plan => l_pay_plan
3283 , p_to_pay_plan => l_to_pay_plan
3284 , p_pay_table_id => l_pay_table_id
3285 , p_grade_or_level => l_grade_or_level
3286 , p_to_grade_or_level => l_to_grade_or_level
3287 , p_step_or_rate => l_step_or_rate
3288 , p_pay_basis => l_pay_basis
3289 , p_elig_flag => l_elig_flag);
3290
3291 l_to_grade_id := NULL;
3292 l_to_grade_or_level := NULL;
3293 l_to_pay_plan := NULL;
3294 EXCEPTION
3295 WHEN others THEN
3296 l_elig_flag := FALSE;
3297 END;
3298
3299 IF l_elig_flag THEN
3300 mnoa_process
3301 (p_assignment_id => per.assignment_id
3302 , p_person_id => per.person_id
3303 , p_position_id => per.position_id
3304 , p_grade_id => per.grade_id
3305 , p_business_group_id => per.business_group_id
3306 , p_location_id => per.location_id
3307 , p_organization_id => per.organization_id
3308 , p_date_of_birth => per.date_of_birth
3309 , p_first_name => per.first_name
3310 , p_last_name => per.last_name
3311 , p_full_name => per.full_name
3312 , p_middle_names => per.middle_names
3313 , p_national_identifier => per.national_identifier
3314 , p_employee_number => per.employee_number
3315 , p_personnel_office_id => l_personnel_office_id
3316 , p_org_structure_id => l_org_structure_id
3317 , p_position_title => l_position_title
3318 , p_position_number => l_position_number
3319 , p_position_seq_no => l_position_seq_no
3320 , p_subelem_code => l_sub_element_code
3321 , p_duty_station_id => l_duty_station_id
3322 , p_tenure => l_tenure
3323 , p_annuitant_indicator => l_annuitant_indicator
3324 , p_pay_rate_determinant => l_pay_rate_determinant
3325 , p_work_schedule => l_work_schedule
3326 , p_part_time_hour => l_part_time_hour
3327 , p_to_grade_id => l_to_grade_id
3328 , p_pay_plan => l_pay_plan
3329 , p_to_pay_plan => l_to_pay_plan
3330 , p_pay_table_id => l_pay_table_id
3331 , p_grade_or_level => l_grade_or_level
3332 , p_to_grade_or_level => l_to_grade_or_level
3333 , p_step_or_rate => l_step_or_rate
3334 , p_pay_basis => l_pay_basis);
3335 END IF;
3336 END;
3337 END LOOP;
3338 --Begin Bug# 14405844
3339 ELSIF l_noa_code IN ('471', '472') THEN
3340 FOR per IN cur_active_people (l_effective_date) LOOP
3341 BEGIN
3342 l_personnel_office_id := NULL;
3343 l_org_structure_id := NULL;
3344 l_position_title := NULL;
3345 l_position_number := NULL;
3346 l_position_seq_no := NULL;
3347 l_sub_element_code := NULL;
3348 l_duty_station_id := NULL;
3349 l_tenure := NULL;
3350 l_annuitant_indicator := NULL;
3351 l_pay_rate_determinant := NULL;
3352 l_work_schedule := NULL;
3353 l_part_time_hour := NULL;
3354 l_to_grade_id := NULL;
3355 l_pay_plan := NULL;
3356 l_to_pay_plan := NULL;
3357 l_pay_table_id := NULL;
3358 l_grade_or_level := NULL;
3359 l_to_grade_or_level := NULL;
3360 l_step_or_rate := NULL;
3361 l_pay_basis := NULL;
3362 l_elig_flag := FALSE;
3363 l_org_name := ghr_mre_pkg.get_organization_name (per.organization_id);
3364 l_asg_extra_info_rec := null;
3365 ghr_history_fetch.fetch_asgei
3366 (p_assignment_id => per.assignment_id,
3367 p_information_type => 'GHR_US_ASG_NON_SF52',
3368 p_date_effective => l_effective_date,
3369 p_asg_ei_data => l_asg_extra_info_rec
3370 );
3371 IF l_asg_extra_info_rec.aei_information15 IS NULL OR l_asg_extra_info_rec.aei_information15 ='F' THEN
3372 BEGIN
3373 hr_utility.set_location ('Calling fetch_and_validate_emp '|| l_noa_code, 2234);
3374
3375 fetch_and_validate_emp
3376 (p_action => p_action
3377 , p_mass_salary_id => p_mass_salary_id
3378 , p_mass_salary_name => p_mass_salary
3379 , p_full_name => per.full_name
3380 , p_national_identifier => per.national_identifier
3381 , p_employee_number => per.employee_number
3382 , p_assignment_id => per.assignment_id
3383 , p_person_id => per.person_id
3384 , p_position_id => per.position_id
3385 , p_grade_id => per.grade_id
3386 , p_business_group_id => per.business_group_id
3387 , p_location_id => per.location_id
3388 , p_organization_id => per.organization_id
3389 , p_mnoa_organization_id => l_p_organization_id
3390 , p_mnoa_duty_station_id => l_p_duty_station_id
3391 , p_mnoa_personnel_office_id => l_p_personnel_office_id
3392 , p_mnoa_agency_code_subelement => l_p_agency_code_subelement
3393 , p_mnoa_job_id => NULL
3394 , p_mnoa_bargaining_unit => NULL
3395 , p_mnoa_flsa_category => NULL
3396 , p_personnel_office_id => l_personnel_office_id
3397 , p_org_structure_id => l_org_structure_id
3398 , p_position_title => l_position_title
3399 , p_position_number => l_position_number
3400 , p_position_seq_no => l_position_seq_no
3401 , p_subelem_code => l_sub_element_code
3402 , p_duty_station_id => l_duty_station_id
3403 , p_tenure => l_tenure
3404 , p_annuitant_indicator => l_annuitant_indicator
3405 , p_pay_rate_determinant => l_pay_rate_determinant
3406 , p_work_schedule => l_work_schedule
3407 , p_part_time_hour => l_part_time_hour
3408 , p_to_grade_id => l_to_grade_id
3409 , p_pay_plan => l_pay_plan
3410 , p_to_pay_plan => l_to_pay_plan
3411 , p_pay_table_id => l_pay_table_id
3412 , p_grade_or_level => l_grade_or_level
3413 , p_to_grade_or_level => l_to_grade_or_level
3414 , p_step_or_rate => l_step_or_rate
3415 , p_pay_basis => l_pay_basis
3416 , p_elig_flag => l_elig_flag);
3417
3418 l_to_grade_id := NULL;
3419 l_to_grade_or_level := NULL;
3420 l_to_pay_plan := NULL;
3421 EXCEPTION
3422 WHEN others THEN
3423 l_elig_flag := FALSE;
3424 END;
3425
3426 IF l_elig_flag THEN
3427 mnoa_process
3428 (p_assignment_id => per.assignment_id
3429 , p_person_id => per.person_id
3430 , p_position_id => per.position_id
3431 , p_grade_id => per.grade_id
3432 , p_business_group_id => per.business_group_id
3433 , p_location_id => per.location_id
3434 , p_organization_id => per.organization_id
3435 , p_date_of_birth => per.date_of_birth
3436 , p_first_name => per.first_name
3437 , p_last_name => per.last_name
3438 , p_full_name => per.full_name
3439 , p_middle_names => per.middle_names
3440 , p_national_identifier => per.national_identifier
3441 , p_employee_number => per.employee_number
3442 , p_personnel_office_id => l_personnel_office_id
3443 , p_org_structure_id => l_org_structure_id
3444 , p_position_title => l_position_title
3445 , p_position_number => l_position_number
3446 , p_position_seq_no => l_position_seq_no
3447 , p_subelem_code => l_sub_element_code
3448 , p_duty_station_id => l_duty_station_id
3449 , p_tenure => l_tenure
3450 , p_annuitant_indicator => l_annuitant_indicator
3451 , p_pay_rate_determinant => l_pay_rate_determinant
3452 , p_work_schedule => l_work_schedule
3453 , p_part_time_hour => l_part_time_hour
3454 , p_to_grade_id => l_to_grade_id
3455 , p_pay_plan => l_pay_plan
3456 , p_to_pay_plan => l_to_pay_plan
3457 , p_pay_table_id => l_pay_table_id
3458 , p_grade_or_level => l_grade_or_level
3459 , p_to_grade_or_level => l_to_grade_or_level
3460 , p_step_or_rate => l_step_or_rate
3461 , p_pay_basis => l_pay_basis);
3462 END IF;
3463 END IF;
3464 END;
3465 END LOOP;
3466 --End Bug# 14405844
3467 ELSIF l_noa_code IN ('280') THEN
3468 FOR per IN cur_280_people (l_effective_date) LOOP
3469 BEGIN
3470 l_personnel_office_id := NULL;
3471 l_org_structure_id := NULL;
3472 l_position_title := NULL;
3473 l_position_number := NULL;
3474 l_position_seq_no := NULL;
3475 l_sub_element_code := NULL;
3476 l_duty_station_id := NULL;
3477 l_tenure := NULL;
3478 l_annuitant_indicator := NULL;
3479 l_pay_rate_determinant := NULL;
3480 l_work_schedule := NULL;
3481 l_part_time_hour := NULL;
3482 l_to_grade_id := NULL;
3483 l_pay_plan := NULL;
3484 l_to_pay_plan := NULL;
3485 l_pay_table_id := NULL;
3486 l_grade_or_level := NULL;
3487 l_to_grade_or_level := NULL;
3488 l_step_or_rate := NULL;
3489 l_pay_basis := NULL;
3490 l_elig_flag := FALSE;
3491 l_org_name := ghr_mre_pkg.get_organization_name (per.organization_id);
3492 BEGIN
3493 hr_utility.set_location ('Calling fetch_and_validate_emp '|| l_noa_code, 2235);
3494
3495 fetch_and_validate_emp
3496 (p_action => p_action
3497 , p_mass_salary_id => p_mass_salary_id
3498 , p_mass_salary_name => p_mass_salary
3499 , p_full_name => per.full_name
3500 , p_national_identifier => per.national_identifier
3501 , p_employee_number => per.employee_number
3502 , p_assignment_id => per.assignment_id
3503 , p_person_id => per.person_id
3504 , p_position_id => per.position_id
3505 , p_grade_id => per.grade_id
3506 , p_business_group_id => per.business_group_id
3507 , p_location_id => per.location_id
3508 , p_organization_id => per.organization_id
3509 , p_mnoa_organization_id => l_p_organization_id
3510 , p_mnoa_duty_station_id => l_p_duty_station_id
3511 , p_mnoa_personnel_office_id => l_p_personnel_office_id
3512 , p_mnoa_agency_code_subelement => l_p_agency_code_subelement
3513 , p_mnoa_job_id => NULL
3514 , p_mnoa_bargaining_unit => NULL
3515 , p_mnoa_flsa_category => NULL
3516 , p_personnel_office_id => l_personnel_office_id
3517 , p_org_structure_id => l_org_structure_id
3518 , p_position_title => l_position_title
3519 , p_position_number => l_position_number
3520 , p_position_seq_no => l_position_seq_no
3521 , p_subelem_code => l_sub_element_code
3522 , p_duty_station_id => l_duty_station_id
3523 , p_tenure => l_tenure
3524 , p_annuitant_indicator => l_annuitant_indicator
3525 , p_pay_rate_determinant => l_pay_rate_determinant
3526 , p_work_schedule => l_work_schedule
3527 , p_part_time_hour => l_part_time_hour
3528 , p_to_grade_id => l_to_grade_id
3529 , p_pay_plan => l_pay_plan
3530 , p_to_pay_plan => l_to_pay_plan
3531 , p_pay_table_id => l_pay_table_id
3532 , p_grade_or_level => l_grade_or_level
3533 , p_to_grade_or_level => l_to_grade_or_level
3534 , p_step_or_rate => l_step_or_rate
3535 , p_pay_basis => l_pay_basis
3536 , p_elig_flag => l_elig_flag);
3537 EXCEPTION
3538 WHEN others THEN
3539 l_elig_flag := FALSE;
3540 END;
3541
3542 IF l_elig_flag THEN
3543 mnoa_process
3544 (p_assignment_id => per.assignment_id
3545 , p_person_id => per.person_id
3546 , p_position_id => per.position_id
3547 , p_grade_id => per.grade_id
3548 , p_business_group_id => per.business_group_id
3549 , p_location_id => per.location_id
3550 , p_organization_id => per.organization_id
3551 , p_date_of_birth => per.date_of_birth
3552 , p_first_name => per.first_name
3553 , p_last_name => per.last_name
3554 , p_full_name => per.full_name
3555 , p_middle_names => per.middle_names
3556 , p_national_identifier => per.national_identifier
3557 , p_employee_number => per.employee_number
3558 , p_personnel_office_id => l_personnel_office_id
3559 , p_org_structure_id => l_org_structure_id
3560 , p_position_title => l_position_title
3561 , p_position_number => l_position_number
3562 , p_position_seq_no => l_position_seq_no
3563 , p_subelem_code => l_sub_element_code
3564 , p_duty_station_id => l_duty_station_id
3565 , p_tenure => l_tenure
3566 , p_annuitant_indicator => l_annuitant_indicator
3567 , p_pay_rate_determinant => l_pay_rate_determinant
3568 , p_work_schedule => l_work_schedule
3569 , p_part_time_hour => l_part_time_hour
3570 , p_to_grade_id => l_to_grade_id
3571 , p_pay_plan => l_pay_plan
3572 , p_to_pay_plan => l_to_pay_plan
3573 , p_pay_table_id => l_pay_table_id
3574 , p_grade_or_level => l_grade_or_level
3575 , p_to_grade_or_level => l_to_grade_or_level
3576 , p_step_or_rate => l_step_or_rate
3577 , p_pay_basis => l_pay_basis);
3578 END IF;
3579 END;
3580 END LOOP;
3581 ELSIF l_noa_code IN ('292') THEN
3582 FOR per IN cur_292_people (l_effective_date) LOOP
3583 BEGIN
3584 l_personnel_office_id := NULL;
3585 l_org_structure_id := NULL;
3586 l_position_title := NULL;
3587 l_position_number := NULL;
3588 l_position_seq_no := NULL;
3589 l_sub_element_code := NULL;
3590 l_duty_station_id := NULL;
3591 l_tenure := NULL;
3592 l_annuitant_indicator := NULL;
3593 l_pay_rate_determinant := NULL;
3594 l_work_schedule := NULL;
3595 l_part_time_hour := NULL;
3596 l_to_grade_id := NULL;
3597 l_pay_plan := NULL;
3598 l_to_pay_plan := NULL;
3599 l_pay_table_id := NULL;
3600 l_grade_or_level := NULL;
3601 l_to_grade_or_level := NULL;
3602 l_step_or_rate := NULL;
3603 l_pay_basis := NULL;
3604 l_elig_flag := FALSE;
3605 l_org_name := ghr_mre_pkg.get_organization_name (per.organization_id);
3606 BEGIN
3607 hr_utility.set_location ('Calling fetch_and_validate_emp '|| l_noa_code, 2236);
3608 fetch_and_validate_emp
3609 (p_action => p_action
3610 , p_mass_salary_id => p_mass_salary_id
3611 , p_mass_salary_name => p_mass_salary
3612 , p_full_name => per.full_name
3613 , p_national_identifier => per.national_identifier
3614 , p_employee_number => per.employee_number
3615 , p_assignment_id => per.assignment_id
3616 , p_person_id => per.person_id
3617 , p_position_id => per.position_id
3618 , p_grade_id => per.grade_id
3619 , p_business_group_id => per.business_group_id
3620 , p_location_id => per.location_id
3621 , p_organization_id => per.organization_id
3622 , p_mnoa_organization_id => l_p_organization_id
3623 , p_mnoa_duty_station_id => l_p_duty_station_id
3624 , p_mnoa_personnel_office_id => l_p_personnel_office_id
3625 , p_mnoa_agency_code_subelement => l_p_agency_code_subelement
3626 , p_mnoa_job_id => NULL
3627 , p_mnoa_bargaining_unit => NULL
3628 , p_mnoa_flsa_category => NULL
3629 , p_personnel_office_id => l_personnel_office_id
3630 , p_org_structure_id => l_org_structure_id
3631 , p_position_title => l_position_title
3632 , p_position_number => l_position_number
3633 , p_position_seq_no => l_position_seq_no
3634 , p_subelem_code => l_sub_element_code
3635 , p_duty_station_id => l_duty_station_id
3636 , p_tenure => l_tenure
3637 , p_annuitant_indicator => l_annuitant_indicator
3638 , p_pay_rate_determinant => l_pay_rate_determinant
3639 , p_work_schedule => l_work_schedule
3640 , p_part_time_hour => l_part_time_hour
3641 , p_to_grade_id => l_to_grade_id
3642 , p_pay_plan => l_pay_plan
3643 , p_to_pay_plan => l_to_pay_plan
3644 , p_pay_table_id => l_pay_table_id
3645 , p_grade_or_level => l_grade_or_level
3646 , p_to_grade_or_level => l_to_grade_or_level
3647 , p_step_or_rate => l_step_or_rate
3648 , p_pay_basis => l_pay_basis
3649 , p_elig_flag => l_elig_flag);
3650 EXCEPTION
3651 WHEN others THEN
3652 l_elig_flag := FALSE;
3653 END;
3654
3655 IF l_elig_flag THEN
3656 mnoa_process
3657 (p_assignment_id => per.assignment_id
3658 , p_person_id => per.person_id
3659 , p_position_id => per.position_id
3660 , p_grade_id => per.grade_id
3661 , p_business_group_id => per.business_group_id
3662 , p_location_id => per.location_id
3663 , p_organization_id => per.organization_id
3664 , p_date_of_birth => per.date_of_birth
3665 , p_first_name => per.first_name
3666 , p_last_name => per.last_name
3667 , p_full_name => per.full_name
3668 , p_middle_names => per.middle_names
3669 , p_national_identifier => per.national_identifier
3670 , p_employee_number => per.employee_number
3671 , p_personnel_office_id => l_personnel_office_id
3672 , p_org_structure_id => l_org_structure_id
3673 , p_position_title => l_position_title
3674 , p_position_number => l_position_number
3675 , p_position_seq_no => l_position_seq_no
3676 , p_subelem_code => l_sub_element_code
3677 , p_duty_station_id => l_duty_station_id
3678 , p_tenure => l_tenure
3679 , p_annuitant_indicator => l_annuitant_indicator
3680 , p_pay_rate_determinant => l_pay_rate_determinant
3681 , p_work_schedule => l_work_schedule
3682 , p_part_time_hour => l_part_time_hour
3683 , p_to_grade_id => l_to_grade_id
3684 , p_pay_plan => l_pay_plan
3685 , p_to_pay_plan => l_to_pay_plan
3686 , p_pay_table_id => l_pay_table_id
3687 , p_grade_or_level => l_grade_or_level
3688 , p_to_grade_or_level => l_to_grade_or_level
3689 , p_step_or_rate => l_step_or_rate
3690 , p_pay_basis => l_pay_basis);
3691 END IF;
3692 END;
3693 END LOOP;
3694 ELSIF l_noa_code IN ('430') THEN
3695 --Bug# 14414790 modified cursor
3696 FOR per IN cur_430_people (l_effective_date) LOOP
3697 BEGIN
3698 l_personnel_office_id := NULL;
3699 l_org_structure_id := NULL;
3700 l_position_title := NULL;
3701 l_position_number := NULL;
3702 l_position_seq_no := NULL;
3703 l_sub_element_code := NULL;
3704 l_duty_station_id := NULL;
3705 l_tenure := NULL;
3706 l_annuitant_indicator := NULL;
3707 l_pay_rate_determinant := NULL;
3708 l_work_schedule := NULL;
3709 l_part_time_hour := NULL;
3710 l_to_grade_id := NULL;
3711 l_pay_plan := NULL;
3712 l_to_pay_plan := NULL;
3713 l_pay_table_id := NULL;
3714 l_grade_or_level := NULL;
3715 l_to_grade_or_level := NULL;
3716 l_step_or_rate := NULL;
3717 l_pay_basis := NULL;
3718 l_elig_flag := FALSE;
3719 l_org_name := ghr_mre_pkg.get_organization_name (per.organization_id);
3720 IF ghr_pa_requests_pkg.get_work_schedule (per.position_id, l_effective_date) IN ('G', 'J', 'Q', 'T') THEN
3721 BEGIN
3722 hr_utility.set_location ('Calling fetch_and_validate_emp '|| l_noa_code, 2237);
3723
3724 fetch_and_validate_emp
3725 (p_action => p_action
3726 , p_mass_salary_id => p_mass_salary_id
3727 , p_mass_salary_name => p_mass_salary
3728 , p_full_name => per.full_name
3729 , p_national_identifier => per.national_identifier
3730 , p_employee_number => per.employee_number
3731 , p_assignment_id => per.assignment_id
3732 , p_person_id => per.person_id
3733 , p_position_id => per.position_id
3734 , p_grade_id => per.grade_id
3735 , p_business_group_id => per.business_group_id
3736 , p_location_id => per.location_id
3737 , p_organization_id => per.organization_id
3738 , p_mnoa_organization_id => l_p_organization_id
3739 , p_mnoa_duty_station_id => l_p_duty_station_id
3740 , p_mnoa_personnel_office_id => l_p_personnel_office_id
3741 , p_mnoa_agency_code_subelement => l_p_agency_code_subelement
3742 , p_mnoa_job_id => NULL
3743 , p_mnoa_bargaining_unit => NULL
3744 , p_mnoa_flsa_category => NULL
3745 , p_personnel_office_id => l_personnel_office_id
3746 , p_org_structure_id => l_org_structure_id
3747 , p_position_title => l_position_title
3748 , p_position_number => l_position_number
3749 , p_position_seq_no => l_position_seq_no
3750 , p_subelem_code => l_sub_element_code
3751 , p_duty_station_id => l_duty_station_id
3752 , p_tenure => l_tenure
3753 , p_annuitant_indicator => l_annuitant_indicator
3754 , p_pay_rate_determinant => l_pay_rate_determinant
3755 , p_work_schedule => l_work_schedule
3756 , p_part_time_hour => l_part_time_hour
3757 , p_to_grade_id => l_to_grade_id
3758 , p_pay_plan => l_pay_plan
3759 , p_to_pay_plan => l_to_pay_plan
3760 , p_pay_table_id => l_pay_table_id
3761 , p_grade_or_level => l_grade_or_level
3762 , p_to_grade_or_level => l_to_grade_or_level
3763 , p_step_or_rate => l_step_or_rate
3764 , p_pay_basis => l_pay_basis
3765 , p_elig_flag => l_elig_flag);
3766 EXCEPTION
3767 WHEN others THEN
3768 l_elig_flag := FALSE;
3769 END;
3770
3771 IF l_elig_flag THEN
3772 mnoa_process
3773 (p_assignment_id => per.assignment_id
3774 , p_person_id => per.person_id
3775 , p_position_id => per.position_id
3776 , p_grade_id => per.grade_id
3777 , p_business_group_id => per.business_group_id
3778 , p_location_id => per.location_id
3779 , p_organization_id => per.organization_id
3780 , p_date_of_birth => per.date_of_birth
3781 , p_first_name => per.first_name
3782 , p_last_name => per.last_name
3783 , p_full_name => per.full_name
3784 , p_middle_names => per.middle_names
3785 , p_national_identifier => per.national_identifier
3786 , p_employee_number => per.employee_number
3787 , p_personnel_office_id => l_personnel_office_id
3788 , p_org_structure_id => l_org_structure_id
3789 , p_position_title => l_position_title
3790 , p_position_number => l_position_number
3791 , p_position_seq_no => l_position_seq_no
3792 , p_subelem_code => l_sub_element_code
3793 , p_duty_station_id => l_duty_station_id
3794 , p_tenure => l_tenure
3795 , p_annuitant_indicator => l_annuitant_indicator
3796 , p_pay_rate_determinant => l_pay_rate_determinant
3797 , p_work_schedule => l_work_schedule
3798 , p_part_time_hour => l_part_time_hour
3799 , p_to_grade_id => l_to_grade_id
3800 , p_pay_plan => l_pay_plan
3801 , p_to_pay_plan => l_to_pay_plan
3802 , p_pay_table_id => l_pay_table_id
3803 , p_grade_or_level => l_grade_or_level
3804 , p_to_grade_or_level => l_to_grade_or_level
3805 , p_step_or_rate => l_step_or_rate
3806 , p_pay_basis => l_pay_basis);
3807 END IF;
3808 END IF;
3809 END;
3810 END LOOP;
3811 ELSIF l_noa_code IN ('772') THEN
3812 FOR per IN cur_772_people (l_effective_date) LOOP
3813 BEGIN
3814 l_personnel_office_id := NULL;
3815 l_org_structure_id := NULL;
3816 l_position_title := NULL;
3817 l_position_number := NULL;
3818 l_position_seq_no := NULL;
3819 l_sub_element_code := NULL;
3820 l_duty_station_id := NULL;
3821 l_tenure := NULL;
3822 l_annuitant_indicator := NULL;
3823 l_pay_rate_determinant := NULL;
3824 l_work_schedule := NULL;
3825 l_part_time_hour := NULL;
3826 l_to_grade_id := NULL;
3827 l_pay_plan := NULL;
3828 l_to_pay_plan := NULL;
3829 l_pay_table_id := NULL;
3830 l_grade_or_level := NULL;
3831 l_to_grade_or_level := NULL;
3832 l_step_or_rate := NULL;
3833 l_pay_basis := NULL;
3834 l_elig_flag := FALSE;
3835 l_org_name := ghr_mre_pkg.get_organization_name (per.organization_id);
3836
3837 BEGIN
3838 hr_utility.set_location ('Calling fetch_and_validate_emp '|| l_noa_code, 2238);
3839 fetch_and_validate_emp
3840 (p_action => p_action
3841 , p_mass_salary_id => p_mass_salary_id
3842 , p_mass_salary_name => p_mass_salary
3843 , p_full_name => per.full_name
3844 , p_national_identifier => per.national_identifier
3845 , p_employee_number => per.employee_number
3846 , p_assignment_id => per.assignment_id
3847 , p_person_id => per.person_id
3848 , p_position_id => per.position_id
3849 , p_grade_id => per.grade_id
3850 , p_business_group_id => per.business_group_id
3851 , p_location_id => per.location_id
3852 , p_organization_id => per.organization_id
3853 , p_mnoa_organization_id => l_p_organization_id
3854 , p_mnoa_duty_station_id => l_p_duty_station_id
3855 , p_mnoa_personnel_office_id => l_p_personnel_office_id
3856 , p_mnoa_agency_code_subelement => l_p_agency_code_subelement
3857 , p_mnoa_job_id => NULL
3858 , p_mnoa_bargaining_unit => NULL
3859 , p_mnoa_flsa_category => NULL
3860 , p_personnel_office_id => l_personnel_office_id
3861 , p_org_structure_id => l_org_structure_id
3862 , p_position_title => l_position_title
3863 , p_position_number => l_position_number
3864 , p_position_seq_no => l_position_seq_no
3865 , p_subelem_code => l_sub_element_code
3866 , p_duty_station_id => l_duty_station_id
3867 , p_tenure => l_tenure
3868 , p_annuitant_indicator => l_annuitant_indicator
3869 , p_pay_rate_determinant => l_pay_rate_determinant
3870 , p_work_schedule => l_work_schedule
3871 , p_part_time_hour => l_part_time_hour
3872 , p_to_grade_id => l_to_grade_id
3873 , p_pay_plan => l_pay_plan
3874 , p_to_pay_plan => l_to_pay_plan
3875 , p_pay_table_id => l_pay_table_id
3876 , p_grade_or_level => l_grade_or_level
3877 , p_to_grade_or_level => l_to_grade_or_level
3878 , p_step_or_rate => l_step_or_rate
3879 , p_pay_basis => l_pay_basis
3880 , p_elig_flag => l_elig_flag);
3881 EXCEPTION
3882 WHEN others THEN
3883 l_elig_flag := FALSE;
3884 END;
3885
3886 IF l_elig_flag THEN
3887 mnoa_process
3888 (p_assignment_id => per.assignment_id
3889 , p_person_id => per.person_id
3890 , p_position_id => per.position_id
3891 , p_grade_id => per.grade_id
3892 , p_business_group_id => per.business_group_id
3893 , p_location_id => per.location_id
3894 , p_organization_id => per.organization_id
3895 , p_date_of_birth => per.date_of_birth
3896 , p_first_name => per.first_name
3897 , p_last_name => per.last_name
3898 , p_full_name => per.full_name
3899 , p_middle_names => per.middle_names
3900 , p_national_identifier => per.national_identifier
3901 , p_employee_number => per.employee_number
3902 , p_personnel_office_id => l_personnel_office_id
3903 , p_org_structure_id => l_org_structure_id
3904 , p_position_title => l_position_title
3905 , p_position_number => l_position_number
3906 , p_position_seq_no => l_position_seq_no
3907 , p_subelem_code => l_sub_element_code
3908 , p_duty_station_id => l_duty_station_id
3909 , p_tenure => l_tenure
3910 , p_annuitant_indicator => l_annuitant_indicator
3911 , p_pay_rate_determinant => l_pay_rate_determinant
3912 , p_work_schedule => l_work_schedule
3913 , p_part_time_hour => l_part_time_hour
3914 , p_to_grade_id => l_to_grade_id
3915 , p_pay_plan => l_pay_plan
3916 , p_to_pay_plan => l_to_pay_plan
3917 , p_pay_table_id => l_pay_table_id
3918 , p_grade_or_level => l_grade_or_level
3919 , p_to_grade_or_level => l_to_grade_or_level
3920 , p_step_or_rate => l_step_or_rate
3921 , p_pay_basis => l_pay_basis);
3922 END IF;
3923 END;
3924 END LOOP;
3925 ELSIF l_noa_code IN ('773') THEN
3926 FOR per IN cur_773_people (l_effective_date) LOOP
3927 BEGIN
3928 l_personnel_office_id := NULL;
3929 l_org_structure_id := NULL;
3930 l_position_title := NULL;
3931 l_position_number := NULL;
3932 l_position_seq_no := NULL;
3933 l_sub_element_code := NULL;
3934 l_duty_station_id := NULL;
3935 l_tenure := NULL;
3936 l_annuitant_indicator := NULL;
3937 l_pay_rate_determinant := NULL;
3938 l_work_schedule := NULL;
3939 l_part_time_hour := NULL;
3940 l_to_grade_id := NULL;
3941 l_pay_plan := NULL;
3942 l_to_pay_plan := NULL;
3943 l_pay_table_id := NULL;
3944 l_grade_or_level := NULL;
3945 l_to_grade_or_level := NULL;
3946 l_step_or_rate := NULL;
3947 l_pay_basis := NULL;
3948 l_elig_flag := FALSE;
3949 l_org_name := ghr_mre_pkg.get_organization_name (per.organization_id);
3950
3951 BEGIN
3952 hr_utility.set_location ('Calling fetch_and_validate_emp '|| l_noa_code, 2239);
3953
3954 fetch_and_validate_emp
3955 (p_action => p_action
3956 , p_mass_salary_id => p_mass_salary_id
3957 , p_mass_salary_name => p_mass_salary
3958 , p_full_name => per.full_name
3959 , p_national_identifier => per.national_identifier
3960 , p_employee_number => per.employee_number
3961 , p_assignment_id => per.assignment_id
3962 , p_person_id => per.person_id
3963 , p_position_id => per.position_id
3964 , p_grade_id => per.grade_id
3965 , p_business_group_id => per.business_group_id
3966 , p_location_id => per.location_id
3967 , p_organization_id => per.organization_id
3968 , p_mnoa_organization_id => l_p_organization_id
3969 , p_mnoa_duty_station_id => l_p_duty_station_id
3970 , p_mnoa_personnel_office_id => l_p_personnel_office_id
3971 , p_mnoa_agency_code_subelement => l_p_agency_code_subelement
3972 , p_mnoa_job_id => NULL
3973 , p_mnoa_bargaining_unit => NULL
3974 , p_mnoa_flsa_category => NULL
3975 , p_personnel_office_id => l_personnel_office_id
3976 , p_org_structure_id => l_org_structure_id
3977 , p_position_title => l_position_title
3978 , p_position_number => l_position_number
3979 , p_position_seq_no => l_position_seq_no
3980 , p_subelem_code => l_sub_element_code
3981 , p_duty_station_id => l_duty_station_id
3982 , p_tenure => l_tenure
3983 , p_annuitant_indicator => l_annuitant_indicator
3984 , p_pay_rate_determinant => l_pay_rate_determinant
3985 , p_work_schedule => l_work_schedule
3986 , p_part_time_hour => l_part_time_hour
3987 , p_to_grade_id => l_to_grade_id
3988 , p_pay_plan => l_pay_plan
3989 , p_to_pay_plan => l_to_pay_plan
3990 , p_pay_table_id => l_pay_table_id
3991 , p_grade_or_level => l_grade_or_level
3992 , p_to_grade_or_level => l_to_grade_or_level
3993 , p_step_or_rate => l_step_or_rate
3994 , p_pay_basis => l_pay_basis
3995 , p_elig_flag => l_elig_flag);
3996 EXCEPTION
3997 WHEN others THEN
3998 l_elig_flag := FALSE;
3999 END;
4000
4001 IF l_elig_flag THEN
4002 mnoa_process
4003 (p_assignment_id => per.assignment_id
4004 , p_person_id => per.person_id
4005 , p_position_id => per.position_id
4006 , p_grade_id => per.grade_id
4007 , p_business_group_id => per.business_group_id
4008 , p_location_id => per.location_id
4009 , p_organization_id => per.organization_id
4010 , p_date_of_birth => per.date_of_birth
4011 , p_first_name => per.first_name
4012 , p_last_name => per.last_name
4013 , p_full_name => per.full_name
4014 , p_middle_names => per.middle_names
4015 , p_national_identifier => per.national_identifier
4016 , p_employee_number => per.employee_number
4017 , p_personnel_office_id => l_personnel_office_id
4018 , p_org_structure_id => l_org_structure_id
4019 , p_position_title => l_position_title
4020 , p_position_number => l_position_number
4021 , p_position_seq_no => l_position_seq_no
4022 , p_subelem_code => l_sub_element_code
4023 , p_duty_station_id => l_duty_station_id
4024 , p_tenure => l_tenure
4025 , p_annuitant_indicator => l_annuitant_indicator
4026 , p_pay_rate_determinant => l_pay_rate_determinant
4027 , p_work_schedule => l_work_schedule
4028 , p_part_time_hour => l_part_time_hour
4029 , p_to_grade_id => l_to_grade_id
4030 , p_pay_plan => l_pay_plan
4031 , p_to_pay_plan => l_to_pay_plan
4032 , p_pay_table_id => l_pay_table_id
4033 , p_grade_or_level => l_grade_or_level
4034 , p_to_grade_or_level => l_to_grade_or_level
4035 , p_step_or_rate => l_step_or_rate
4036 , p_pay_basis => l_pay_basis);
4037 END IF;
4038 END;
4039 END LOOP;
4040 ELSIF l_noa_code IN ('792', '800') THEN
4041 cur_business_group_id := fnd_profile.value ('PER_BUSINESS_GROUP_ID');
4042 FOR per IN cur_792_people (l_effective_date) LOOP
4043 BEGIN
4044 l_personnel_office_id := NULL;
4045 l_org_structure_id := NULL;
4046 l_position_title := NULL;
4047 l_position_number := NULL;
4048 l_position_seq_no := NULL;
4049 l_sub_element_code := NULL;
4050 l_duty_station_id := NULL;
4051 l_tenure := NULL;
4052 l_annuitant_indicator := NULL;
4053 l_pay_rate_determinant := NULL;
4054 l_work_schedule := NULL;
4055 l_part_time_hour := NULL;
4056 l_to_grade_id := NULL;
4057 l_pay_plan := NULL;
4058 l_to_pay_plan := NULL;
4059 l_pay_table_id := NULL;
4060 l_grade_or_level := NULL;
4061 l_to_grade_or_level := NULL;
4062 l_step_or_rate := NULL;
4063 l_pay_basis := NULL;
4064 l_elig_flag := FALSE;
4065 l_org_name := ghr_mre_pkg.get_organization_name (per.organization_id);
4066 BEGIN
4067 hr_utility.set_location ('Calling fetch_and_validate_emp '|| l_noa_code, 2240);
4068
4069 fetch_and_validate_emp
4070 (p_action => p_action
4071 , p_mass_salary_id => p_mass_salary_id
4072 , p_mass_salary_name => p_mass_salary
4073 , p_full_name => per.full_name
4074 , p_national_identifier => per.national_identifier
4075 , p_employee_number => per.employee_number
4076 , p_assignment_id => per.assignment_id
4077 , p_person_id => per.person_id
4078 , p_position_id => per.position_id
4079 , p_grade_id => per.grade_id
4080 , p_business_group_id => per.business_group_id
4081 , p_location_id => per.location_id
4082 , p_organization_id => per.organization_id
4083 , p_mnoa_organization_id => l_p_organization_id
4084 , p_mnoa_duty_station_id => l_p_duty_station_id
4085 , p_mnoa_personnel_office_id => l_p_personnel_office_id
4086 , p_mnoa_agency_code_subelement => l_p_agency_code_subelement
4087 , p_mnoa_job_id => l_p_job_id
4088 , p_mnoa_bargaining_unit => l_p_bargaining_unit
4089 , p_mnoa_flsa_category => l_p_flsa_category
4090 , p_personnel_office_id => l_personnel_office_id
4091 , p_org_structure_id => l_org_structure_id
4092 , p_position_title => l_position_title
4093 , p_position_number => l_position_number
4094 , p_position_seq_no => l_position_seq_no
4095 , p_subelem_code => l_sub_element_code
4096 , p_duty_station_id => l_duty_station_id
4097 , p_tenure => l_tenure
4098 , p_annuitant_indicator => l_annuitant_indicator
4099 , p_pay_rate_determinant => l_pay_rate_determinant
4100 , p_work_schedule => l_work_schedule
4101 , p_part_time_hour => l_part_time_hour
4102 , p_to_grade_id => l_to_grade_id
4103 , p_pay_plan => l_pay_plan
4104 , p_to_pay_plan => l_to_pay_plan
4105 , p_pay_table_id => l_pay_table_id
4106 , p_grade_or_level => l_grade_or_level
4107 , p_to_grade_or_level => l_to_grade_or_level
4108 , p_step_or_rate => l_step_or_rate
4109 , p_pay_basis => l_pay_basis
4110 , p_elig_flag => l_elig_flag);
4111 EXCEPTION
4112 WHEN others THEN
4113 l_elig_flag := FALSE;
4114 END;
4115
4116 IF l_elig_flag THEN
4117 mnoa_process
4118 (p_assignment_id => per.assignment_id
4119 , p_person_id => per.person_id
4120 , p_position_id => per.position_id
4121 , p_grade_id => per.grade_id
4122 , p_business_group_id => per.business_group_id
4123 , p_location_id => per.location_id
4124 , p_organization_id => per.organization_id
4125 , p_date_of_birth => per.date_of_birth
4126 , p_first_name => per.first_name
4127 , p_last_name => per.last_name
4128 , p_full_name => per.full_name
4129 , p_middle_names => per.middle_names
4130 , p_national_identifier => per.national_identifier
4131 , p_employee_number => per.employee_number
4132 , p_personnel_office_id => l_personnel_office_id
4133 , p_org_structure_id => l_org_structure_id
4134 , p_position_title => l_position_title
4135 , p_position_number => l_position_number
4136 , p_position_seq_no => l_position_seq_no
4137 , p_subelem_code => l_sub_element_code
4138 , p_duty_station_id => l_duty_station_id
4139 , p_tenure => l_tenure
4140 , p_annuitant_indicator => l_annuitant_indicator
4141 , p_pay_rate_determinant => l_pay_rate_determinant
4142 , p_work_schedule => l_work_schedule
4143 , p_part_time_hour => l_part_time_hour
4144 , p_to_grade_id => l_to_grade_id
4145 , p_pay_plan => l_pay_plan
4146 , p_to_pay_plan => l_to_pay_plan
4147 , p_pay_table_id => l_pay_table_id
4148 , p_grade_or_level => l_grade_or_level
4149 , p_to_grade_or_level => l_to_grade_or_level
4150 , p_step_or_rate => l_step_or_rate
4151 , p_pay_basis => l_pay_basis);
4152 END IF;
4153 END;
4154 END LOOP;
4155 FOR c_vacant_pos IN vacant_pos (cur_business_group_id, l_effective_date) LOOP
4156 l_avail_status_id := c_vacant_pos.availability_status_id;
4157
4158 IF ghr_pa_requests_pkg.get_pos_availability_status (l_avail_status_id) NOT IN ('DELETED', 'FROZEN', 'ELIMINATED') THEN
4159 BEGIN
4160 l_personnel_office_id := NULL;
4161 l_org_structure_id := NULL;
4162 l_position_title := NULL;
4163 l_position_number := NULL;
4164 l_position_seq_no := NULL;
4165 l_sub_element_code := NULL;
4166 l_duty_station_id := NULL;
4167 l_tenure := NULL;
4168 l_annuitant_indicator := NULL;
4169 l_pay_rate_determinant := NULL;
4170 l_work_schedule := NULL;
4171 l_part_time_hour := NULL;
4172 l_to_grade_id := NULL;
4173 l_pay_plan := NULL;
4174 l_to_pay_plan := NULL;
4175 l_pay_table_id := NULL;
4176 l_grade_or_level := NULL;
4177 l_to_grade_or_level := NULL;
4178 l_step_or_rate := NULL;
4179 l_pay_basis := NULL;
4180 l_elig_flag := FALSE;
4181 l_org_name := ghr_mre_pkg.get_organization_name (c_vacant_pos.organization_id);
4182
4183 BEGIN
4184 hr_utility.set_location ('Calling fetch_and_validate_emp ' || l_proc, 2240);
4185
4186 fetch_and_validate_emp
4187 (p_action => p_action
4188 , p_mass_salary_id => p_mass_salary_id
4189 , p_mass_salary_name => p_mass_salary
4190 , p_full_name => c_vacant_pos.full_name
4191 , p_national_identifier => c_vacant_pos.national_identifier
4192 , p_employee_number => c_vacant_pos.employee_number
4193 , p_assignment_id => c_vacant_pos.assignment_id
4194 , p_person_id => c_vacant_pos.person_id
4195 , p_position_id => c_vacant_pos.position_id
4196 , p_grade_id => c_vacant_pos.grade_id
4197 , p_business_group_id => c_vacant_pos.business_group_id
4198 , p_location_id => c_vacant_pos.location_id
4199 , p_organization_id => c_vacant_pos.organization_id
4200 , p_mnoa_organization_id => l_p_organization_id
4201 , p_mnoa_duty_station_id => l_p_duty_station_id
4202 , p_mnoa_personnel_office_id => l_p_personnel_office_id
4203 , p_mnoa_agency_code_subelement => l_p_agency_code_subelement
4204 , p_mnoa_job_id => l_p_job_id
4205 , p_mnoa_bargaining_unit => l_p_bargaining_unit
4206 , p_mnoa_flsa_category => l_p_flsa_category
4207 , p_personnel_office_id => l_personnel_office_id
4208 , p_org_structure_id => l_org_structure_id
4209 , p_position_title => l_position_title
4210 , p_position_number => l_position_number
4211 , p_position_seq_no => l_position_seq_no
4212 , p_subelem_code => l_sub_element_code
4213 , p_duty_station_id => l_duty_station_id
4214 , p_tenure => l_tenure
4215 , p_annuitant_indicator => l_annuitant_indicator
4216 , p_pay_rate_determinant => l_pay_rate_determinant
4217 , p_work_schedule => l_work_schedule
4218 , p_part_time_hour => l_part_time_hour
4219 , p_to_grade_id => l_to_grade_id
4220 , p_pay_plan => l_pay_plan
4221 , p_to_pay_plan => l_to_pay_plan
4222 , p_pay_table_id => l_pay_table_id
4223 , p_grade_or_level => l_grade_or_level
4224 , p_to_grade_or_level => l_to_grade_or_level
4225 , p_step_or_rate => l_step_or_rate
4226 , p_pay_basis => l_pay_basis
4227 , p_elig_flag => l_elig_flag);
4228 EXCEPTION
4229 WHEN others THEN
4230 l_elig_flag := FALSE;
4231 END;
4232
4233 IF l_elig_flag THEN
4234 hr_utility.set_location ('Calling vacant_pos_process '
4235 || l_noa_code, 2241);
4236
4237 vacant_pos_process
4238 (p_assignment_id => c_vacant_pos.assignment_id
4239 , p_person_id => c_vacant_pos.person_id
4240 , p_position_id => c_vacant_pos.position_id
4241 , p_grade_id => c_vacant_pos.grade_id
4242 , p_business_group_id => c_vacant_pos.business_group_id
4243 , p_location_id => c_vacant_pos.location_id
4244 , p_organization_id => c_vacant_pos.organization_id
4245 , p_date_of_birth => c_vacant_pos.date_of_birth
4246 , p_first_name => c_vacant_pos.first_name
4247 , p_last_name => c_vacant_pos.last_name
4248 , p_full_name => c_vacant_pos.full_name
4249 , p_middle_names => c_vacant_pos.middle_names
4250 , p_national_identifier => c_vacant_pos.national_identifier
4251 , p_employee_number => c_vacant_pos.employee_number
4252 , p_personnel_office_id => l_personnel_office_id
4253 , p_org_structure_id => l_org_structure_id
4254 , p_position_title => l_position_title
4255 , p_position_number => l_position_number
4256 , p_position_seq_no => l_position_seq_no
4257 , p_subelem_code => l_sub_element_code
4258 , p_duty_station_id => l_duty_station_id
4259 , p_tenure => l_tenure
4260 , p_annuitant_indicator => l_annuitant_indicator
4261 , p_pay_rate_determinant => l_pay_rate_determinant
4262 , p_work_schedule => l_work_schedule
4263 , p_part_time_hour => l_part_time_hour
4264 , p_to_grade_id => l_to_grade_id
4265 , p_pay_plan => l_pay_plan
4266 , p_to_pay_plan => l_to_pay_plan
4267 , p_pay_table_id => l_pay_table_id
4268 , p_grade_or_level => l_grade_or_level
4269 , p_to_grade_or_level => l_to_grade_or_level
4270 , p_step_or_rate => l_step_or_rate
4271 , p_pay_basis => l_pay_basis);
4272 END IF;
4273 END;
4274 END IF;
4275 END LOOP;
4276 END IF;
4277 --hr_utility.trace_off;
4278 pr ('After processing is over ', to_char (l_recs_failed));
4279
4280 IF (l_recs_failed = 0) THEN
4281 IF upper (p_action) = 'CREATE' THEN
4282 BEGIN
4283 UPDATE ghr_mass_salaries
4284 SET submit_flag = 'P'
4285 WHERE rowid = l_rowid;
4286 EXCEPTION
4287 WHEN others THEN
4288 hr_utility.set_location ('Error in Update ghr_msl Sql error '|| sqlerrm (sqlcode), 30);
4289 hr_utility.set_message (8301, 'GHR_38476_UPD_GHR_MSL_FAILURE');
4290 hr_utility.raise_error;
4291 END;
4292 END IF;
4293 ELSE
4294 p_errbuf := 'Error in '
4295 || l_proc
4296 || ' Details in GHR_PROCESS_LOG';
4297
4298 p_retcode := 2;
4299
4300 IF upper (p_action) = 'CREATE' THEN
4301 UPDATE ghr_mass_salaries
4302 SET submit_flag = 'E'
4303 WHERE rowid = l_rowid;
4304 END IF;
4305 END IF;
4306 pr ('Before commiting.....');
4307 COMMIT;
4308 pr ('After commiting.....', to_char (l_recs_failed));
4309 EXCEPTION
4310 WHEN others THEN
4311 hr_utility.set_location ('Error (Others2) occurred in '|| l_proc|| ' Sql error '|| sqlerrm (sqlcode), 30);
4312
4313 BEGIN
4314 ROLLBACK TO execute_noa_sp;
4315 EXCEPTION
4316 WHEN others THEN
4317 NULL;
4318 END;
4319
4320 l_log_text := 'Error in '
4321 || l_proc
4322 || ' For Mass NOA Selectable : '
4323 || p_mass_salary
4324 || ' Sql Err is '
4325 || sqlerrm (sqlcode);
4326
4327 l_recs_failed := l_recs_failed + 1;
4328 hr_utility.set_location ('before creating entry in log file', 30);
4329 p_errbuf := 'Error in '|| l_proc|| ' Details in GHR_PROCESS_LOG';
4330 p_retcode := 2;
4331
4332 IF upper (p_action) = 'CREATE' THEN
4333 UPDATE ghr_mass_salaries
4334 SET submit_flag = 'E'
4335 WHERE rowid = l_rowid;
4336
4337 COMMIT;
4338 END IF;
4339
4340 BEGIN
4341 ghr_mto_int.log_message
4342 (p_procedure => g_proc
4343 , p_message => l_log_text);
4344 EXCEPTION
4345 WHEN others THEN
4346 hr_utility.set_message (8301, 'Create Error Log failed');
4347
4348 hr_utility.raise_error;
4349 END;
4350 END execute_mnoa;
4351 --Begin Bug# 14467215
4352 PROCEDURE execute_mcancel
4353 (p_errbuf OUT NOCOPY varchar2
4354 , p_retcode OUT NOCOPY number
4355 , p_mass_salary_id IN number
4356 , p_action IN varchar2) IS
4357
4358 p_mass_salary varchar2(32);
4359 l_mass_salary_id number;
4360 l_target_mass_action_id number;
4361 l_rowid varchar2(30);
4362 l_pa_rowid varchar2(30);
4363 l_pa_request_id number;
4364 l_can_pa_request_id number;
4365 l_name per_people_f.full_name%TYPE;
4366 l_employee_number per_people_f.employee_number%TYPE;
4367 l_total_recs number DEFAULT 0;
4368 l_recs_failed number DEFAULT 0;
4369 l_pa_req_rec ghr_pa_requests%ROWTYPE;
4370 l_effective_date date;
4371 l_submit_flag varchar2(2);
4372 l_row_cnt number DEFAULT 0;
4373 l_ovn number;
4374 l_lac_sf52_rec ghr_pa_requests%ROWTYPE;
4375 l_errbuf varchar2(2000);
4376 l_retcode number;
4377 p_full_name per_people_f.full_name%TYPE;
4378 p_employee_number per_people_f.employee_number%TYPE;
4379 l_routing_group_id ghr_pa_requests.routing_group_id%TYPE;
4380 l_groupbox_id ghr_groupboxes.groupbox_id%TYPE;
4381 l_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%TYPE;
4382 l_prh_object_version_number ghr_pa_routing_history.object_version_number%TYPE;
4383 l_u_prh_object_version_number number;
4384 l_i_pa_routing_history_id number;
4385 l_i_prh_object_version_number number;
4386 l_object_version_number ghr_pa_requests.object_version_number%TYPE;
4387 l_personnel_officer_name per_people_f.full_name%TYPE;
4388 l_approving_off_work_title ghr_pa_requests.approving_official_work_title%TYPE;
4389 rec_busy EXCEPTION;
4390 PRAGMA EXCEPTION_INIT(rec_busy, -54);
4391 l_proc varchar2(72) DEFAULT 'GHR_MASS_CANCEL.'|| '.execute_mcancel';
4392 CURSOR ghr_mcancel
4393 (p_mcan_id IN number) IS
4394 SELECT name
4395 , target_mass_action_id
4396 , effective_date
4397 , mass_salary_id
4398 , submit_flag
4399 , rowid
4400 , pa_request_id
4401 FROM ghr_mass_salaries
4402 WHERE mass_salary_id = p_mcan_id
4403 FOR UPDATE OF user_table_id NOWAIT;
4404
4405 CURSOR cur_can_records
4406 (p_mass_action_id IN ghr_pa_requests.mass_action_id%TYPE
4407 , p_effective_date IN date) IS
4408 SELECT *
4409 FROM ghr_pa_requests a
4410 WHERE a.effective_date = p_effective_date
4411 AND a.mass_action_id = p_mass_action_id
4412 AND a.pa_notification_id IS NOT NULL
4413 AND NOT EXISTS
4414 (
4415 SELECT 1
4416 FROM ghr_pa_requests b
4417 WHERE b.first_noa_code IN ('001')
4418 AND b.person_id = a.person_id
4419 AND b.pa_notification_id IS NOT NULL
4420 START WITH pa_request_id = a.pa_request_id
4421 AND altered_pa_request_id IS NULL
4422 CONNECT BY altered_pa_request_id = PRIOR pa_request_id
4423 );
4424 CURSOR cur_get_dtl
4425 (p_mass_action_id IN ghr_pa_requests.mass_action_id%TYPE
4426 , p_effective_date IN date) IS
4427 SELECT substr (employee_last_name|| ', '|| employee_first_name, 1, 240) fname,person_id
4428 FROM ghr_pa_requests
4429 WHERE effective_date = p_effective_date
4430 AND mass_action_id = p_mass_action_id;
4431
4432 CURSOR cur_pa_rowid
4433 (p_pa_request_id IN ghr_pa_requests.pa_request_id%TYPE) IS
4434 SELECT rowid
4435 , object_version_number
4436 FROM ghr_pa_requests a
4437 WHERE pa_request_id = p_pa_request_id;
4438
4439 CURSOR cur_ppl_dtl
4440 (p_person_id IN number
4441 , p_effective_date IN date) IS
4442 SELECT full_name
4443 , employee_number
4444 FROM per_people_f
4445 WHERE person_id = p_person_id
4446 AND p_effective_date
4447 BETWEEN effective_start_date
4448 AND effective_end_date;
4449
4450 CURSOR c_routing_history IS
4451 SELECT prh.pa_routing_history_id
4452 , prh.object_version_number
4453 FROM ghr_pa_routing_history prh
4454 WHERE prh.pa_request_id = l_can_pa_request_id
4455 ORDER BY 1 DESC;
4456
4457 CURSOR c_req_num IS
4458 SELECT request_number
4459 , object_version_number
4460 FROM ghr_pa_requests
4461 WHERE pa_request_id = l_can_pa_request_id
4462 ORDER BY 1 DESC;
4463
4464 PROCEDURE mcancel_process
4465 (p_mass_salary_id IN number
4466 , p_target_mass_action_id IN number
4467 , p_effective_date IN date) IS
4468 l_session_id number;
4469 l_sess_date date;
4470 --Begin Bug# 16083971
4471 l_organization_id per_assignments_f.organization_id%TYPE;
4472 CURSOR c_org_id(l_per_id in per_assignments_f.person_id%TYPE) IS
4473 SELECT asg.organization_id
4474 FROM per_all_assignments_f asg
4475 WHERE asg.person_id = l_per_id
4476 AND asg.assignment_type <> 'B'
4477 AND trunc(p_effective_date) BETWEEN asg.effective_start_date AND asg.effective_end_date
4478 ORDER BY asg.assignment_id;
4479 --End Bug# 16083971
4480 l_proc varchar2(72) DEFAULT 'GHR_MASS_CANCEL.'|| '.mcancel_process'; -- Bug# 14751973
4481 CURSOR get_sess_date IS
4482 SELECT trunc (effective_date)
4483 , session_id
4484 FROM fnd_sessions
4485 WHERE session_id = userenv ('sessionid');
4486 BEGIN
4487 hr_utility.set_location ('Entering mcancel_process '|| l_proc, 100);
4488
4489 FOR l_pa_req_rec IN cur_can_records (p_target_mass_action_id, p_effective_date) LOOP
4490 BEGIN
4491 SAVEPOINT execute_mcan_sp;
4492 -- Begin Bug# 14751973
4493 p_can_noa_code := l_pa_req_rec.first_noa_code;
4494 g_mcal_flag := TRUE;
4495 -- End Bug# 14751973
4496 l_total_recs := l_total_recs+1;
4497 FOR c_ppl_dtl IN cur_ppl_dtl (l_pa_req_rec.person_id, p_effective_date) LOOP
4498 p_full_name := c_ppl_dtl.full_name;
4499 p_employee_number := c_ppl_dtl.employee_number;
4500 END LOOP;
4501 --Begin Bug# 16083971
4502 -- For some NOAs To Org will be null, so getting from assignment
4503 IF l_pa_req_rec.to_organization_id IS NULL THEN
4504 FOR cus_org_id IN c_org_id(l_pa_req_rec.person_id) LOOP
4505 l_organization_id := cus_org_id.organization_id;
4506 EXIT;
4507 END LOOP;
4508 ELSE
4509 l_organization_id := l_pa_req_rec.to_organization_id;
4510 END IF;
4511 --End Bug# 16083971
4512 IF upper (p_action) IN ('SHOW', 'REPORT') THEN
4513 hr_utility.set_location ('Calling create_mass_noa_prev in '|| l_proc, 133);
4514 -- Bug# 14751973
4515 create_mass_noa_prev
4516 (p_effective_date => l_effective_date
4517 , p_date_of_birth => l_pa_req_rec.employee_date_of_birth
4518 , p_full_name => p_full_name
4519 , p_national_identifier => l_pa_req_rec.employee_national_identifier
4520 , p_employee_number => p_employee_number
4521 , p_duty_station_id => NULL
4522 , p_duty_station_code => NULL
4523 , p_duty_station_desc => NULL
4524 , p_personnel_office_id => NULL
4525 , p_basic_pay => NULL
4526 , p_new_basic_pay => NULL
4527 , p_adj_basic_pay => NULL
4528 , p_new_adj_basic_pay => NULL
4529 , p_old_loc_adj => NULL
4530 , p_new_loc_adj => NULL
4531 , p_tot_old_sal => NULL
4532 , p_tot_new_sal => NULL
4533 , p_position_id => nvl (l_pa_req_rec.to_position_id, l_pa_req_rec.from_position_id)
4534 , p_position_title => nvl (l_pa_req_rec.to_position_title, l_pa_req_rec.from_position_title)
4535 , p_position_number => nvl (l_pa_req_rec.to_position_number, l_pa_req_rec.from_position_number)
4536 , p_position_seq_no => nvl (l_pa_req_rec.to_position_seq_no, l_pa_req_rec.from_position_seq_no)
4537 , p_agency_sub_element_code => NULL
4538 , p_person_id => l_pa_req_rec.person_id
4539 , p_mass_salary_id => p_mass_salary_id
4540 , p_sel_flg => NULL
4541 , p_action => p_action
4542 , p_assignment_id => l_pa_req_rec.employee_assignment_id
4543 , p_organization_name => ghr_mre_pkg.get_organization_name (l_organization_id)
4544 , p_to_agency_code => NULL
4545 , p_to_duty_station_id => NULL
4546 , p_to_duty_station_code => NULL
4547 , p_to_duty_station_desc => NULL
4548 , p_occ_code => NULL
4549 , p_to_occ_code => NULL
4550 , p_from_bargaining_unit_status => NULL
4551 , p_to_bargaining_unit_status => NULL
4552 , p_from_flsa_category => NULL
4553 , p_to_flsa_category => NULL
4554 , p_from_functional_class => NULL
4555 , p_to_functional_class => NULL
4556 , p_work_schedule => NULL);
4557 ELSIF upper (p_action) = 'CREATE' THEN
4558 hr_utility.set_location ('p_action= '|| p_action|| ' in '|| l_proc, 133);
4559 BEGIN
4560 l_errbuf := NULL;
4561
4562 FOR c_pa_rowid IN cur_pa_rowid (l_pa_req_rec.pa_request_id) LOOP
4563 l_pa_rowid := c_pa_rowid.rowid;
4564 l_ovn := c_pa_rowid.object_version_number;
4565 END LOOP;
4566
4567 OPEN get_sess_date;
4568
4569 FETCH get_sess_date
4570 INTO l_sess_date
4571 , l_session_id;
4572
4573 IF get_sess_date%NOTFOUND THEN
4574 hr_utility.set_location ('session id '|| userenv ('sessionid'), 100);
4575 l_session_id := userenv ('sessionid');
4576
4577 INSERT
4578 INTO fnd_sessions (session_id, effective_date)
4579 VALUES (l_session_id, sysdate);
4580 ELSIF l_sess_date IS NULL THEN
4581 UPDATE fnd_sessions
4582 SET effective_date = trunc (sysdate)
4583 WHERE session_id = l_session_id;
4584 END IF;
4585
4586 CLOSE get_sess_date;
4587
4588 BEGIN
4589 g_proc := 'Create_Mass_cancel_record ';
4590
4591 hr_utility.set_location ('Before creating cancellation RPA '|| l_proc, 134);
4592
4593 l_can_pa_request_id := ghr_approved_pa_requests.ghr_cancel_sf52
4594 (p_pa_request_id => l_pa_req_rec.pa_request_id
4595 , p_par_object_version_number => l_ovn
4596 , p_noa_id => l_pa_req_rec.first_noa_id
4597 , p_which_noa => 1
4598 , p_row_id => l_pa_rowid
4599 , p_username => fnd_profile.value ('USERNAME')
4600 , p_which_action => 'ORIGINAL'
4601 , p_cancel_legal_authority => NULL);
4602
4603 BEGIN
4604 g_proc := 'update_Mass_cancel_record';
4605
4606 DECLARE
4607 l_pa_request_number ghr_pa_requests.request_number%TYPE;
4608 BEGIN
4609 hr_utility.set_location ('Update pa_req_num '|| l_proc, 200);
4610
4611 FOR cur_req_num IN c_req_num LOOP
4612 l_pa_request_number := cur_req_num.request_number;
4613
4614 l_object_version_number := cur_req_num.object_version_number;
4615 END LOOP;
4616
4617 l_pa_request_number := 'MCAN'
4618 || l_can_pa_request_id
4619 || '-'
4620 || p_mass_salary_id;
4621
4622 BEGIN
4623 ghr_mass_actions_pkg.get_personnel_officer_name
4624 (p_personnel_office_id => l_pa_req_rec.personnel_office_id
4625 , p_person_full_name => l_personnel_officer_name
4626 , p_approving_off_work_title => l_approving_off_work_title);
4627 EXCEPTION
4628 WHEN others THEN
4629 hr_utility.set_location ('Getting personnel office Failure '
4630 || l_proc, 170);
4631
4632 l_log_text := 'Error in POI Name '
4633 || ' Sql error : '
4634 || sqlerrm (sqlcode);
4635
4636 RAISE mnoa_error;
4637 END;
4638
4639 ghr_par_upd.upd
4640 (p_pa_request_id => l_can_pa_request_id
4641 , p_object_version_number => l_object_version_number
4642 , p_first_action_la_code1 => l_lac_sf52_rec.first_action_la_code1
4643 , p_first_action_la_code2 => l_lac_sf52_rec.first_action_la_code2
4644 , p_first_action_la_desc1 => l_lac_sf52_rec.first_action_la_desc1
4645 , p_first_action_la_desc2 => l_lac_sf52_rec.first_action_la_desc2
4646 , p_request_number => l_pa_request_number
4647 , p_mass_action_id => p_mass_salary_id
4648 , p_approval_date => sysdate
4649 , p_sf50_approval_date => sysdate
4650 , p_approving_official_full_name => l_personnel_officer_name
4651 , p_approving_official_work_titl => l_approving_off_work_title
4652 , p_sf50_approving_ofcl_full_nam => l_personnel_officer_name
4653 , p_sf50_approving_ofcl_work_tit => l_approving_off_work_title
4654 , p_rpa_type => 'MCAN');
4655 EXCEPTION
4656 WHEN others THEN
4657 l_mcanerrbuf := hr_utility.get_message;
4658 l_errbuf := l_mcanerrbuf;
4659 RAISE mnoa_error;
4660 END;
4661
4662 BEGIN
4663 hr_utility.set_location ('Getting Group Box '
4664 || l_proc, 170);
4665
4666 ghr_mass_actions_pkg.get_personnel_off_groupbox
4667 (p_position_id => nvl (l_pa_req_rec.to_position_id, l_pa_req_rec.from_position_id)
4668 , p_effective_date => l_effective_date
4669 , p_groupbox_id => l_groupbox_id
4670 , p_routing_group_id => l_routing_group_id);
4671
4672 hr_utility.set_location ('Routing Group Id '
4673 || to_char (l_routing_group_id), 170);
4674
4675 hr_utility.set_location ('Group Box Id'
4676 || to_char (l_groupbox_id), 170);
4677 EXCEPTION
4678 WHEN others THEN
4679 hr_utility.set_location ('Getting Group Box Failure '
4680 || l_proc, 170);
4681
4682 l_log_text := 'Error in POI groupbox '
4683 || ' Sql error : '
4684 || sqlerrm (sqlcode);
4685
4686 RAISE mnoa_error;
4687 END;
4688
4689 FOR routing_history_id IN c_routing_history LOOP
4690 l_pa_routing_history_id := routing_history_id.pa_routing_history_id;
4691 l_prh_object_version_number := routing_history_id.object_version_number;
4692 EXIT;
4693 END LOOP;
4694
4695 hr_utility.set_location ('Updating prh '
4696 || l_proc, 175);
4697 hr_utility.set_location ('RHVN '
4698 || to_char (l_prh_object_version_number), 176);
4699 hr_utility.set_location ('RHID '
4700 || to_char (l_pa_routing_history_id), 177);
4701 ghr_prh_upd.upd
4702 (p_pa_routing_history_id => l_pa_routing_history_id
4703 , p_groupbox_id => l_groupbox_id
4704 , p_object_version_number => l_prh_object_version_number);
4705
4706 ghr_par_upd.upd
4707 (p_pa_request_id => l_can_pa_request_id
4708 , p_routing_group_id => l_routing_group_id
4709 , p_object_version_number => l_object_version_number);
4710
4711 BEGIN
4712 ghr_sf52_api.update_sf52
4713 (p_pa_request_id => l_can_pa_request_id
4714 , p_par_object_version_number => l_object_version_number
4715 , p_effective_date => l_effective_date
4716 , p_routing_group_id => l_routing_group_id
4717 , p_u_action_taken => 'UPDATE_HR'
4718 , p_u_prh_object_version_number => l_u_prh_object_version_number
4719 , p_i_pa_routing_history_id => l_i_pa_routing_history_id
4720 , p_i_prh_object_version_number => l_i_prh_object_version_number);
4721 EXCEPTION
4722 WHEN others THEN
4723 hr_utility.set_location (sqlerrm (sqlcode), 1);
4724 hr_utility.set_location ('Update sf52 final Failure '
4725 || l_proc, 190);
4726 l_log_text := 'Error in Update sf52 final '
4727 || ' Sql error : '
4728 || sqlerrm (sqlcode);
4729 RAISE mnoa_error;
4730 END;
4731 END;
4732 EXCEPTION
4733 WHEN others THEN
4734 l_mcanerrbuf := hr_utility.get_message;
4735 l_errbuf := l_mcanerrbuf;
4736 RAISE mnoa_error;
4737 END;
4738
4739 IF l_errbuf IS NULL THEN
4740 pr ('No error in create sf52 ');
4741 hr_utility.set_location ('Before commiting', 2);
4742
4743 ghr_mto_int.log_message
4744 (p_procedure => 'Successful Completion'
4745 , p_message => 'Name: '
4746 || p_full_name
4747 || ' Emp No: '
4748 || p_employee_number
4749 || ' Mass Cancellation : '
4750 || p_mass_salary
4751 || ' SF52 Successfully completed');
4752
4753 ghr_msl_pkg.create_lac_remarks (l_pa_request_id, l_can_pa_request_id);
4754
4755 COMMIT;
4756 ELSE
4757 pr ('Error in create sf52', l_errbuf);
4758
4759 hr_utility.set_location ('Error in '
4760 || to_char (l_pa_req_rec.to_position_id), 20);
4761
4762 RAISE mnoa_error;
4763 END IF;
4764 EXCEPTION
4765 WHEN mnoa_error THEN
4766 RAISE;
4767 WHEN others THEN
4768 l_mcanerrbuf := 'Error in canel_create_sf52 '
4769 || ' Sql Err is '
4770 || sqlerrm (sqlcode);
4771
4772 RAISE mnoa_error;
4773 END;
4774 END IF;
4775
4776 l_row_cnt := l_row_cnt + 1;
4777
4778 IF upper (p_action) <> 'CREATE' THEN
4779 IF l_row_cnt > 50 THEN
4780 COMMIT;
4781
4782 l_row_cnt := 0;
4783 END IF;
4784 END IF;
4785 EXCEPTION
4786 WHEN mnoa_error THEN
4787 g_mcal_flag := FALSE;-- Bug# 14751973
4788 hr_utility.set_location ('Error occurred in '
4789 || l_proc
4790 || ' Sql error '
4791 || sqlerrm (sqlcode), 10);
4792
4793 BEGIN
4794 IF upper (p_action) <> 'SHOW' THEN
4795 ROLLBACK TO execute_mcan_sp;
4796 END IF;
4797 EXCEPTION
4798 WHEN others THEN
4799 NULL;
4800 END;
4801
4802 l_log_text := 'Error in mcan '
4803 || l_proc
4804 || ' '
4805 || ' For Mass Cancellation : '
4806 || p_mass_salary
4807 || ' Name: '
4808 || p_full_name
4809 || ' Emp No: '
4810 || p_employee_number
4811 || ' '
4812 || l_mcanerrbuf;
4813
4814 hr_utility.set_location ('before creating entry in log file', 10);
4815 l_recs_failed := l_recs_failed + 1;
4816
4817 BEGIN
4818 ghr_mto_int.log_message
4819 (p_procedure => g_proc
4820 , p_message => l_log_text);
4821 EXCEPTION
4822 WHEN others THEN
4823 hr_utility.set_message (8301, 'GHR_38475_ERROR_LOG_FAILURE');
4824 hr_utility.raise_error;
4825 END;
4826 WHEN others THEN
4827 g_mcal_flag := FALSE;-- Bug# 14751973
4828 hr_utility.set_location ('Error (Others3) occurred in '
4829 || l_proc
4830 || ' Sql error '
4831 || sqlerrm (sqlcode), 30);
4832
4833 BEGIN
4834 ROLLBACK TO execute_mcan_sp;
4835 EXCEPTION
4836 WHEN others THEN
4837 NULL;
4838 END;
4839
4840 l_log_text := 'Error in '
4841 || l_proc
4842 || ' For Mass Cancellation : '
4843 || p_mass_salary
4844 || ' Sql Err is '
4845 || sqlerrm (sqlcode);
4846
4847 l_recs_failed := l_recs_failed + 1;
4848 hr_utility.set_location ('before creating entry in log file', 30);
4849 p_errbuf := 'Error in '
4850 || l_proc
4851 || ' Details in GHR_PROCESS_LOG';
4852 p_retcode := 2;
4853
4854 IF upper (p_action) = 'CREATE' THEN
4855 UPDATE ghr_mass_salaries
4856 SET submit_flag = 'E'
4857 WHERE rowid = l_rowid;
4858
4859 COMMIT;
4860 END IF;
4861
4862 BEGIN
4863 ghr_mto_int.log_message
4864 (p_procedure => g_proc
4865 , p_message => l_log_text);
4866 EXCEPTION
4867 WHEN others THEN
4868 hr_utility.set_message (8301, 'Create Error Log failed');
4869 hr_utility.raise_error;
4870 END;
4871
4872 END;
4873 END LOOP;--FOR l_pa_req_rec
4874 g_mcal_flag := FALSE;-- Bug# 14751973
4875 IF l_total_recs = 0 THEN
4876 BEGIN
4877 g_proc := 'RPA Cancelled Already';
4878 FOR c_get_dtl IN cur_get_dtl (p_target_mass_action_id, p_effective_date) LOOP
4879 l_name := c_get_dtl.fname;
4880 l_employee_number := ghr_pa_requests_pkg2.get_employee_number
4881 (p_person_id => c_get_dtl.person_id
4882 , p_effective_date => p_effective_date);
4883 hr_utility.set_location ('Mass Cancellation already done for this ' || l_proc, 1);
4884 l_mcanerrbuf := 'Name: '
4885 || l_name
4886 || '; Emp No: '
4887 || l_employee_number
4888 || '- Mass Cancellation'
4889 || ' RPA has been already cancelled for the given effective date';
4890 RAISE mnoa_error;
4891 END LOOP;
4892 END;
4893 END IF;--IF l_total_recs
4894 EXCEPTION
4895 WHEN others THEN
4896 g_mcal_flag := FALSE;-- Bug# 14751973
4897 hr_utility.set_location ('Error (Others3) occurred in '
4898 || l_proc
4899 || ' Sql error '
4900 || sqlerrm (sqlcode), 30);
4901
4902 BEGIN
4903 ROLLBACK TO execute_mcan_sp;
4904 EXCEPTION
4905 WHEN others THEN
4906 NULL;
4907 END;
4908
4909 l_log_text := 'Error in '
4910 || l_proc
4911 || ' For Mass Cancellation : '
4912 || p_mass_salary
4913 || ' Sql Err is '
4914 || sqlerrm (sqlcode);
4915
4916 l_recs_failed := l_recs_failed + 1;
4917 hr_utility.set_location ('before creating entry in log file', 30);
4918 p_errbuf := 'Error in '
4919 || l_proc
4920 || ' Details in GHR_PROCESS_LOG';
4921 p_retcode := 2;
4922
4923 IF upper (p_action) = 'CREATE' THEN
4924 UPDATE ghr_mass_salaries
4925 SET submit_flag = 'E'
4926 WHERE rowid = l_rowid;
4927
4928 COMMIT;
4929 END IF;
4930
4931 BEGIN
4932 ghr_mto_int.log_message
4933 (p_procedure => g_proc
4934 , p_message => l_log_text);
4935 EXCEPTION
4936 WHEN others THEN
4937 hr_utility.set_message (8301, 'Create Error Log failed');
4938 hr_utility.raise_error;
4939 END;
4940 END mcancel_process;
4941 BEGIN
4942 hr_utility.set_location ('Entering main execute_mcancel '
4943 || l_proc, 5);
4944 p_retcode := 0;
4945
4946 BEGIN
4947 hr_utility.set_location ('Mass id '
4948 || p_mass_salary_id, 51);
4949
4950 FOR mcancel IN ghr_mcancel (p_mass_salary_id) LOOP
4951 hr_utility.set_location ('Mass Criteria name '
4952 || mcancel.name, 51);
4953
4954 p_mass_salary := mcancel.name;
4955 l_effective_date := mcancel.effective_date;
4956 l_mass_salary_id := mcancel.mass_salary_id;
4957 l_target_mass_action_id := mcancel.target_mass_action_id;
4958 l_submit_flag := mcancel.submit_flag;
4959 l_pa_request_id := mcancel.pa_request_id;
4960 l_rowid := mcancel.rowid;
4961 pr ('Pa request id is '
4962 || to_char (l_pa_request_id));
4963
4964 EXIT;
4965 END LOOP;
4966 EXCEPTION
4967 WHEN rec_busy THEN
4968 hr_utility.set_location ('Mass Cancellation is in use'
4969 || l_proc, 1);
4970
4971 l_mcanerrbuf := 'Error in '
4972 || l_proc
4973 || ' Sql Err is '
4974 || sqlerrm (sqlcode);
4975
4976 hr_utility.set_message (8301, 'GHR_38477_LOCK_ON_MSL');
4977
4978 hr_utility.raise_error;
4979 WHEN others THEN
4980 hr_utility.set_location ('Error in '
4981 || l_proc
4982 || ' Sql err is '
4983 || sqlerrm (sqlcode), 1);
4984
4985 l_mcanerrbuf := 'Error in '
4986 || l_proc
4987 || ' Sql Err is '
4988 || sqlerrm (sqlcode);
4989
4990 RAISE mnoa_error;
4991 END;
4992
4993 g_effective_date := l_effective_date;
4994
4995 IF upper (p_action) = 'CREATE' THEN
4996 ghr_mto_int.set_log_program_name ('GHR_MCANCEL_PKG');
4997 ELSE
4998 ghr_mto_int.set_log_program_name ('MCANCEL_'
4999 || p_mass_salary);
5000 END IF;
5001
5002 get_lac_dtls (l_pa_request_id, l_lac_sf52_rec);
5003 hr_utility.set_location ('Mass Action name '
5004 || p_action, 52);
5005
5006 BEGIN
5007 mcancel_process (l_mass_salary_id, l_target_mass_action_id
5008 , l_effective_date);
5009 END;
5010
5011 pr ('After processing is over ', to_char (l_recs_failed));
5012
5013 IF (l_recs_failed = 0) THEN
5014 IF upper (p_action) = 'CREATE' THEN
5015 BEGIN
5016 UPDATE ghr_mass_salaries
5017 SET submit_flag = 'P'
5018 WHERE rowid = l_rowid;
5019 EXCEPTION
5020 WHEN others THEN
5021 hr_utility.set_location ('Error in Update ghr_msl Sql error '
5022 || sqlerrm (sqlcode), 30);
5023
5024 hr_utility.set_message (8301, 'GHR_38476_UPD_GHR_MSL_FAILURE');
5025
5026 hr_utility.raise_error;
5027 END;
5028 END IF;
5029 ELSE
5030 p_errbuf := 'Error in '
5031 || l_proc
5032 || ' Details in GHR_PROCESS_LOG';
5033
5034 p_retcode := 2;
5035
5036 IF upper (p_action) = 'CREATE' THEN
5037 UPDATE ghr_mass_salaries
5038 SET submit_flag = 'E'
5039 WHERE rowid = l_rowid;
5040 END IF;
5041 END IF;
5042
5043 pr ('Before commiting.....');
5044
5045 COMMIT;
5046
5047 pr ('After commiting.....', to_char (l_recs_failed));
5048 EXCEPTION
5049 WHEN others THEN
5050 hr_utility.set_location ('Error (Others4) occurred in '
5051 || l_proc
5052 || ' Sql error '
5053 || sqlerrm (sqlcode), 30);
5054
5055 BEGIN
5056 ROLLBACK TO execute_mcan_sp;
5057 EXCEPTION
5058 WHEN others THEN
5059 NULL;
5060 END;
5061
5062 l_log_text := 'Error in '
5063 || l_proc
5064 || ' For Mass Cancellation : '
5065 || p_mass_salary
5066 || ' Sql Err is '
5067 || sqlerrm (sqlcode);
5068
5069 l_recs_failed := l_recs_failed + 1;
5070
5071 hr_utility.set_location ('before creating entry in log file', 30);
5072
5073 p_errbuf := 'Error in '
5074 || l_proc
5075 || ' Details in GHR_PROCESS_LOG';
5076
5077 p_retcode := 2;
5078
5079 IF upper (p_action) = 'CREATE' THEN
5080 UPDATE ghr_mass_salaries
5081 SET submit_flag = 'E'
5082 WHERE rowid = l_rowid;
5083
5084 COMMIT;
5085 END IF;
5086
5087 BEGIN
5088 ghr_mto_int.log_message
5089 (p_procedure => g_proc
5090 , p_message => l_log_text);
5091 EXCEPTION
5092 WHEN others THEN
5093 hr_utility.set_message (8301, 'Create Error Log failed');
5094
5095 hr_utility.raise_error;
5096 END;
5097 END execute_mcancel;
5098 --End Bug# 14467215
5099 --Begin Bug# 14751973
5100 PROCEDURE purge_processed_noa_recs(p_session_id in number,
5101 p_err_buf out nocopy varchar2) IS
5102
5103 BEGIN
5104 p_err_buf := null;
5105 DELETE from ghr_mass_noa_preview
5106 WHERE mass_action_type = 'MNOA'
5107 AND session_id = p_session_id;
5108 COMMIT;
5109
5110 EXCEPTION
5111 WHEN others THEN
5112 p_err_buf := 'Sql err '|| sqlerrm(sqlcode);
5113 END purge_processed_noa_recs;
5114 --end Bug# 14751973
5115 END ghr_mass_sel_noa;