[Home] [Help]
PACKAGE BODY: APPS.GHR_MASS_AWARDS_PKG
Source
1 PACKAGE BODY GHR_MASS_AWARDS_PKG AS
2 /* $Header: ghrmarpa.pkb 120.13 2010/04/09 05:25:19 vmididho ship $ */
3
4
5 g_package varchar2(32) := 'GHR_MASS_AWARDS_PKG.';
6 l_information_type varchar2(40) := 'GHR_US_PAR_AWARDS_BONUS';
7 g_log_name varchar2(30);
8 g_log_text varchar2(2000);
9
10
11 PROCEDURE get_noa_code_desc
12 (
13 p_noa_id in ghr_nature_of_actions.nature_of_action_id%type,
14 p_effective_date in date default trunc(sysdate),
15 p_noa_code out nocopy ghr_nature_of_actions.code%type,
16 p_noa_desc out nocopy ghr_nature_of_actions.description%type
17 )
18 IS
19
20 --
21 -- local variables
22 --
23
24 l_proc varchar2(72) := g_package || 'get_noa_code_desc';
25
26 cursor c_noa is
27 select noa.code, noa.description
28 from ghr_nature_of_actions noa
29 where noa.nature_of_action_id = p_noa_id
30 and noa.enabled_flag = 'Y'
31 and nvl(p_effective_date,trunc(sysdate))
32 between noa.date_from
33 and nvl(noa.date_to,nvl(p_effective_date,trunc(sysdate))) ;
34 --
35 BEGIN
36 --
37 hr_utility.set_location('Entering ' || l_proc,5);
38 p_noa_code := Null;
39 p_noa_desc := Null;
40
41 for noa_code_desc in c_noa loop
42 hr_utility.set_location( l_proc,10);
43 p_noa_code := noa_code_desc.code;
44 p_noa_desc := noa_code_desc.description;
45 end loop;
46 hr_utility.set_location('Leaving ' || l_proc,15);
47
48 EXCEPTION
49 WHEN others THEN
50 -- Reset IN OUT parameters and set OUT parameters
51 p_noa_code := NULL;
52 p_noa_desc := NULL;
53 RAISE;
54
55 END get_noa_code_desc;
56
57 PROCEDURE get_business_group(p_person_id in number,
58 p_effective_date in date,
59 p_business_group_id in out nocopy number) is
60
61 l_business_group_id number;
62 -- -------------------------------------------------------------------------
63 -- Cursor to derive business group from person based on person_id.
64 -- -------------------------------------------------------------------------
65 cursor c_business_group is
66 select ppf.business_group_id
67 from per_people_f ppf
68 where ppf.person_id = p_person_id
69 and trunc(p_effective_date)
70 between nvl(trunc(ppf.effective_start_date),trunc(sysdate))
71 and nvl(trunc(ppf.effective_end_date),trunc(sysdate-1));
72 --
73 BEGIN
74
75 l_business_group_id := p_business_group_id ; --NOCOPY Changes
76
77 open c_business_group;
78 fetch c_business_group into p_business_group_id;
79 close c_business_group;
80
81 EXCEPTION
82 WHEN others THEN
83 -- Reset IN OUT parameters and set OUT parameters
84 p_business_group_id := l_business_group_id;
85 RAISE;
86
87 END get_business_group;
88
89
90 FUNCTION get_noa_id (
91 p_mass_award_id in ghr_mass_awards.mass_award_id%type)
92 RETURN NUMBER IS
93 --
94 CURSOR cur_noa_id IS
95 SELECT nature_of_action_id
96 FROM ghr_mass_awards
97 WHERE mass_award_id = p_mass_award_id;
98
99 l_ret_val NUMBER(15);
100
101 BEGIN
102 l_ret_val := null;
103 FOR cur_noa_id_rec IN cur_noa_id LOOP
104 l_ret_val := cur_noa_id_rec.nature_of_action_id;
105 END LOOP;
106
107 RETURN(l_ret_val);
108
109 END get_noa_id;
110
111 --Bug#3804067 Added new parameter p_mass_action_comments
112 PROCEDURE get_pa_request_id_ovn (
113 p_mass_award_id in ghr_mass_awards.mass_award_id%TYPE,
114 p_effective_date in date,
115 p_person_id in per_people_f.person_id%TYPE,
116 p_pa_request_id out nocopy ghr_pa_requests.pa_request_id%TYPE,
117 p_pa_notification_id out nocopy ghr_pa_requests.pa_notification_id%TYPE,
118 p_rpa_type out nocopy ghr_pa_requests.rpa_type%TYPE,
119 p_mass_action_sel_flag out nocopy ghr_pa_requests.mass_action_select_flag%TYPE,
120 p_mass_action_comments out nocopy ghr_pa_requests.mass_action_comments%TYPE,
121 p_object_version_number out nocopy ghr_pa_requests.object_version_number%TYPE)
122
123 IS
124
125 --
126 -- local variables
127 --
128
129 l_proc varchar2(72) := g_package || 'get_pa_request_id_ovn';
130 --Bug#3804067 Added mass_action_comments
131 CURSOR cur_rpa IS
132 SELECT pa_request_id, object_version_number,
133 pa_notification_id,rpa_type, mass_action_select_flag,
134 mass_action_comments
135 FROM ghr_pa_requests
136 WHERE mass_action_id = p_mass_award_id
137 AND effective_date = trunc(p_effective_date)
138 AND person_id = p_person_id;
139
140 BEGIN
141
142 hr_utility.set_location('Entering ' || l_proc,5);
143 p_pa_request_id := Null;
144 p_object_version_number := Null;
145 p_pa_notification_id := Null;
146 p_rpa_type := Null;
147 p_mass_action_sel_flag := Null;
148 p_mass_action_comments := Null;
149
150 for cur_rpa_rec in cur_rpa loop
151 hr_utility.set_location( l_proc,10);
152 p_pa_request_id := cur_rpa_rec.pa_request_id;
153 p_object_version_number := cur_rpa_rec.object_version_number;
154 p_pa_notification_id := cur_rpa_rec.pa_notification_id;
155 p_rpa_type := cur_rpa_rec.rpa_type;
156 p_mass_action_sel_flag := cur_rpa_rec.mass_action_select_flag;
157 --Bug#3804067 Added code for comments.
158 p_mass_action_comments := cur_rpa_rec.mass_action_comments;
159 end loop;
160 hr_utility.set_location('pa_requst_id value ' || to_char(p_pa_request_id) ,15);
161 hr_utility.set_location('pa_notifn_id value ' || to_char(p_pa_notification_id),15);
162 hr_utility.set_location('pa_rpa_type value ' || p_rpa_type,15);
163 hr_utility.set_location('pa_ovn value ' || to_char(p_object_version_number),15);
164 hr_utility.set_location('select flag value ' || p_mass_action_sel_flag,15);
165 hr_utility.set_location('Leaving ' || l_proc,15);
166
167 EXCEPTION
168 WHEN others THEN
169 -- Reset IN OUT parameters and set OUT parameters
170
171 p_pa_request_id :=NULL;
172 p_pa_notification_id :=NULL;
173 p_rpa_type :=NULL;
174 p_mass_action_sel_flag :=NULL;
175 --Bug#3804067 Added mass_action_comments
176 p_mass_action_comments :=NULL;
177 p_object_version_number :=NULL;
178 hr_utility.set_location('Leaving ' || l_proc,20);
179 RAISE;
180
181 END get_pa_request_id_ovn;
182
183 PROCEDURE get_award_details
184 (
185 p_mass_award_id in ghr_mass_awards.mass_award_id%TYPE,
186 p_rpa_type in ghr_pa_requests.rpa_type%TYPE,
187 p_effective_date in date,
188 p_person_id in ghr_pa_requests.person_id%TYPE,
189 p_award_amount out nocopy ghr_pa_requests.award_amount%TYPE,
190 p_award_uom out nocopy ghr_pa_requests.award_uom%TYPE,
191 p_award_percentage out nocopy ghr_pa_requests.award_percentage%TYPE,
192 p_award_agency out nocopy ghr_pa_request_extra_info.rei_information3%type,
193 p_award_type out nocopy ghr_pa_request_extra_info.rei_information4%type,
194 p_group_award out nocopy ghr_pa_request_extra_info.rei_information6%type,
195 p_tangible_benefit_dollars out nocopy ghr_pa_request_extra_info.rei_information7%type,
196 p_date_award_earned out nocopy ghr_pa_request_extra_info.rei_information9%type,
197 p_appropriation_code out nocopy ghr_pa_request_extra_info.rei_information10%type
198 )
199 ---- Fetch Remarks and Legal Authority codes.
200 IS
201
202 --
203 -- local variables
204 --
205
206 l_proc varchar2(72) := g_package || 'get_award_details';
207 l_pa_request_id ghr_pa_requests.pa_request_id%TYPE;
208
209 CURSOR cur_rpa_tmp IS
210 SELECT pa_request_id,
211 award_amount,
212 award_uom,
213 award_percentage
214 FROM ghr_pa_requests
215 WHERE mass_action_id = p_mass_award_id
216 AND rpa_type = p_rpa_type
217 AND person_id is null;
218
219 CURSOR cur_rpa_per IS
220 SELECT pa_request_id,
221 award_amount,
222 award_uom,
223 award_percentage
224 FROM ghr_pa_requests
225 WHERE mass_action_id = p_mass_award_id
226 AND rpa_type = p_rpa_type
227 AND person_id = p_person_id;
228
229 CURSOR cur_award_ei(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
230 SELECT rei_information3 award_agency,
231 rei_information4 award_type,
232 rei_information6 group_award,
233 rei_information7 tangible_benefit_dollars,
234 rei_information9 date_award_earned,
235 rei_information10 appropriation_code
236 FROM ghr_pa_request_extra_info
237 WHERE pa_request_id = c_pa_request_id
238 AND information_type = l_information_type;
239
240 BEGIN
241
242 hr_utility.set_location('Entering ' || l_proc,5);
243 p_award_amount := Null;
244 p_award_uom := Null;
245 p_award_percentage := Null;
246 p_award_agency := Null;
247 p_award_type := Null;
248 p_group_award := Null;
249 p_tangible_benefit_dollars := Null;
250 p_date_award_earned := Null;
251 p_appropriation_code := Null;
252
253 IF p_rpa_type = 'TA' then
254 for cur_rpa_tmp_rec in cur_rpa_tmp loop
255 hr_utility.set_location( l_proc,10);
256 l_pa_request_id := cur_rpa_tmp_rec.pa_request_id;
257 p_award_amount := cur_rpa_tmp_rec.award_amount;
258 p_award_uom := cur_rpa_tmp_rec.award_uom;
259 p_award_percentage := cur_rpa_tmp_rec.award_percentage;
260 hr_utility.set_location(' Rpa type = ' || p_rpa_type ,15);
261 hr_utility.set_location(' award amt = ' || to_char(p_award_amount) ,15);
262 hr_utility.set_location(' award uom = ' || p_award_uom ,15);
263 hr_utility.set_location(' award per = ' || to_char(p_award_percentage) ,15);
264 end loop;
265 else
266 for cur_rpa_per_rec in cur_rpa_per loop
267 hr_utility.set_location( l_proc,10);
268 l_pa_request_id := cur_rpa_per_rec.pa_request_id;
269 p_award_amount := cur_rpa_per_rec.award_amount;
270 p_award_uom := cur_rpa_per_rec.award_uom;
271 p_award_percentage := cur_rpa_per_rec.award_percentage;
272 hr_utility.set_location(' Rpa type = ' || p_rpa_type ,15);
273 hr_utility.set_location(' Person_id = ' || to_char(p_person_id) ,15);
274 hr_utility.set_location(' award amt = ' || to_char(p_award_amount) ,15);
275 hr_utility.set_location(' award uom = ' || p_award_uom ,15);
276 hr_utility.set_location(' award per = ' || to_char(p_award_percentage) ,15);
277 end loop;
278 END IF;
279
280 hr_utility.set_location(' Rpa ID = ' ||l_pa_request_id ,15);
281
282 for cur_award_ei_rec in cur_award_ei(l_pa_request_id) loop
283 hr_utility.set_location( l_proc,20);
284 p_award_agency := cur_award_ei_rec.award_agency;
285 p_award_type := cur_award_ei_rec.award_type;
286 p_group_award := cur_award_ei_rec.group_award;
287 p_tangible_benefit_dollars := cur_award_ei_rec.tangible_benefit_dollars;
288 p_date_award_earned := cur_award_ei_rec.date_award_earned;
289 p_appropriation_code := cur_award_ei_rec.appropriation_code;
290 hr_utility.set_location(' award agency = ' || p_award_agency ,15);
291 hr_utility.set_location(' award type = ' || p_award_type ,15);
292 hr_utility.set_location(' group award = ' || p_group_award ,15);
293 hr_utility.set_location(' tbd = ' || p_tangible_benefit_dollars ,15);
294 hr_utility.set_location(' date award = ' || p_date_award_earned ,15);
295 hr_utility.set_location(' appr Code = ' || p_appropriation_code ,15);
296 end loop;
297
298 hr_utility.set_location('Leaving ' || l_proc,35);
299
300 EXCEPTION
301 WHEN others THEN
302 -- Reset IN OUT parameters and set OUT parameters
303
304 p_award_amount := Null;
305 p_award_uom := Null;
306 p_award_percentage := Null;
307 p_award_agency := Null;
308 p_award_type := Null;
309 p_group_award := Null;
310 p_tangible_benefit_dollars := Null;
311 p_date_award_earned := Null;
312 p_appropriation_code := Null;
313
314 hr_utility.set_location('Leaving ' || l_proc,40);
315 RAISE;
316
317 END get_award_details;
318
319 PROCEDURE get_award_lac
320 (
321 p_mass_award_id in ghr_mass_awards.mass_award_id%TYPE,
322 p_rpa_type in ghr_pa_requests.rpa_type%TYPE,
323 p_effective_date in date,
324 p_first_lac1_record out nocopy first_lac1_record,
325 p_first_lac2_record out nocopy first_lac2_record
326 )
327
328 IS
329
330 --
331 -- local variables
332 --
333
334 l_proc varchar2(72) := g_package || 'get_award_lac';
335 l_pa_request_id ghr_pa_requests.pa_request_id%TYPE;
336
337
338 CURSOR cur_rpa_lac IS
339 SELECT first_action_la_code1,
340 first_action_la_desc1,
341 first_lac1_information1,
342 first_lac1_information2,
343 first_lac1_information3,
344 first_lac1_information4,
345 first_lac1_information5,
346 first_action_la_code2,
347 first_action_la_desc2,
348 first_lac2_information1,
349 first_lac2_information2,
350 first_lac2_information3,
351 first_lac2_information4,
352 first_lac2_information5
353 FROM ghr_pa_requests
354 WHERE mass_action_id = p_mass_award_id
355 AND rpa_type = p_rpa_type
356 AND person_id is null;
357
358 BEGIN
359
360 hr_utility.set_location('Entering ' || l_proc,5);
361 p_first_lac1_record := Null;
362 p_first_lac2_record := Null;
363
364 for cur_rpa_lac_rec in cur_rpa_lac loop
365 hr_utility.set_location( l_proc,10);
366 p_first_lac1_record.first_action_la_code1 := cur_rpa_lac_rec.first_action_la_code1;
367 p_first_lac1_record.first_action_la_desc1 := cur_rpa_lac_rec.first_action_la_desc1;
368 p_first_lac1_record.first_lac1_information1 := cur_rpa_lac_rec.first_lac1_information1;
369 p_first_lac1_record.first_lac1_information2 := cur_rpa_lac_rec.first_lac1_information2;
370 p_first_lac1_record.first_lac1_information3 := cur_rpa_lac_rec.first_lac1_information3;
371 p_first_lac1_record.first_lac1_information4 := cur_rpa_lac_rec.first_lac1_information4;
372 p_first_lac1_record.first_lac1_information5 := cur_rpa_lac_rec.first_lac1_information5;
373
374 p_first_lac2_record.first_action_la_code2 := cur_rpa_lac_rec.first_action_la_code2;
375 p_first_lac2_record.first_action_la_desc2 := cur_rpa_lac_rec.first_action_la_desc2;
376 p_first_lac2_record.first_lac2_information1 := cur_rpa_lac_rec.first_lac2_information1;
377 p_first_lac2_record.first_lac2_information2 := cur_rpa_lac_rec.first_lac2_information2;
378 p_first_lac2_record.first_lac2_information3 := cur_rpa_lac_rec.first_lac2_information3;
379 p_first_lac2_record.first_lac2_information4 := cur_rpa_lac_rec.first_lac2_information4;
380 p_first_lac2_record.first_lac2_information5 := cur_rpa_lac_rec.first_lac2_information5;
381 end loop;
382
383 hr_utility.set_location('Leaving ' || l_proc,20);
384
385 EXCEPTION
386 WHEN others THEN
387 -- Reset IN OUT parameters and set OUT parameters
388
389 p_first_lac1_record := Null;
390 p_first_lac2_record := Null;
391
392 hr_utility.set_location('Leaving ' || l_proc,25);
393 RAISE;
394
395 END get_award_lac;
396
397 PROCEDURE get_award_details_shadow
398 (
399 p_pa_request_id in ghr_pa_request_ei_shadow.pa_request_id%type,
400 p_award_amount out nocopy ghr_pa_requests.award_amount%TYPE,
401 p_award_uom out nocopy ghr_pa_requests.award_uom%TYPE,
402 p_award_percentage out nocopy ghr_pa_requests.award_percentage%TYPE,
403 p_award_agency out nocopy ghr_pa_request_extra_info.rei_information3%type,
404 p_award_type out nocopy ghr_pa_request_extra_info.rei_information4%type,
405 p_group_award out nocopy ghr_pa_request_extra_info.rei_information6%type,
406 p_tangible_benefit_dollars out nocopy ghr_pa_request_extra_info.rei_information7%type,
407 p_date_award_earned out nocopy ghr_pa_request_extra_info.rei_information9%type,
408 p_appropriation_code out nocopy ghr_pa_request_extra_info.rei_information10%type
409 )
410
411 IS
412
413 --
414 -- local variables
415 --
416
417 l_proc varchar2(72) := g_package || 'get_award_details_shadow';
418 l_pa_request_id ghr_pa_requests.pa_request_id%TYPE;
419
420 CURSOR cur_rpa_shadow IS
421 SELECT
422 award_amount,
423 award_uom,
424 award_percentage
425 FROM ghr_pa_request_shadow
426 WHERE pa_request_id = p_pa_request_id;
427
428 CURSOR cur_award_ei_shadow IS
429 SELECT rei_information3 award_agency,
430 rei_information4 award_type,
431 rei_information6 group_award,
432 rei_information7 tangible_benefit_dollars,
433 rei_information9 date_award_earned,
434 rei_information10 appropriation_code
435 FROM ghr_pa_request_ei_shadow
436 WHERE pa_request_id = p_pa_request_id
437 AND information_type = l_information_type;
438
439 BEGIN
440
441 hr_utility.set_location('Entering ' || l_proc,5);
442 p_award_amount := Null;
443 p_award_uom := Null;
444 p_award_percentage := Null;
445 p_award_agency := Null;
446 p_award_type := Null;
447 p_group_award := Null;
448 p_tangible_benefit_dollars := Null;
449 p_date_award_earned := Null;
450 p_appropriation_code := Null;
451
452 for cur_rpa_shadow_rec in cur_rpa_shadow loop
453 hr_utility.set_location( l_proc,10);
454 p_award_amount := cur_rpa_shadow_rec.award_amount;
455 p_award_uom := cur_rpa_shadow_rec.award_uom;
456 p_award_percentage := cur_rpa_shadow_rec.award_percentage;
457 hr_utility.set_location(' pa req id = ' || to_char(p_pa_request_id) ,10);
458 hr_utility.set_location(' award amt = ' || to_char(p_award_amount) ,10);
459 hr_utility.set_location(' award uom = ' || p_award_uom ,10);
460 hr_utility.set_location(' award per = ' || to_char(p_award_percentage) ,10);
461 end loop;
462
463 for cur_award_ei_shadow_rec in cur_award_ei_shadow loop
464 hr_utility.set_location( l_proc,20);
465 p_award_agency := cur_award_ei_shadow_rec.award_agency;
466 p_award_type := cur_award_ei_shadow_rec.award_type;
467 p_group_award := cur_award_ei_shadow_rec.group_award;
468 p_tangible_benefit_dollars := cur_award_ei_shadow_rec.tangible_benefit_dollars;
469 p_date_award_earned := cur_award_ei_shadow_rec.date_award_earned;
470 p_appropriation_code := cur_award_ei_shadow_rec.appropriation_code;
471 hr_utility.set_location(' award agency = ' || p_award_agency ,20);
472 hr_utility.set_location(' award type = ' || p_award_type ,20);
473 hr_utility.set_location(' group award = ' || p_group_award ,20);
474 hr_utility.set_location(' tbd = ' || p_tangible_benefit_dollars ,20);
475 hr_utility.set_location(' date award = ' || p_date_award_earned ,20);
476 hr_utility.set_location(' Appr Code = ' || p_appropriation_code ,20);
477 end loop;
478
479 hr_utility.set_location('Leaving ' || l_proc,35);
480
481 EXCEPTION
482 WHEN others THEN
483 -- Reset IN OUT parameters and set OUT parameters
484
485 p_award_amount := Null;
486 p_award_uom := Null;
487 p_award_percentage := Null;
488 p_award_agency := Null;
489 p_award_type := Null;
490 p_group_award := Null;
491 p_tangible_benefit_dollars := Null;
492 p_date_award_earned := Null;
493 p_appropriation_code := Null;
494
495 hr_utility.set_location('Leaving ' || l_proc,40);
496 RAISE;
497
498 END get_award_details_shadow;
499
500
501 PROCEDURE main_awards
502 (
503 p_mass_award_id in ghr_mass_awards.mass_award_id%TYPE,
504 p_action_type in varchar2,
505 p_errbuf out nocopy varchar2, --\___ error log
506 p_status out nocopy varchar2, --||
507 p_retcode out nocopy number, --/ in conc. manager.
508 p_maxcheck out nocopy number
509 )
510
511
512 IS
513
514 --
515 -- local variables
516 --
517 l_errbuf varchar2(2000) := NULL;
518 l_status varchar2(10) := NULL;
519 l_retcode number := NULL;
520 l_status_flag varchar2(1) := NULL;
521
522 l_proc varchar2(72) := g_package || 'main_awards';
523 l_effective_date date;
524 l_rpa_type varchar2(30) := 'A';
525 l_prog_name varchar2(30);
526
527 CURSOR cur_ma IS
528 SELECT trunc(effective_date) effective_date
529 FROM ghr_mass_awards
530 WHERE mass_award_id = p_mass_award_id;
531
532 CURSOR cur_get_seq is
533 SELECT to_char(ghr_process_log_s.nextval)
534 FROM sys.dual;
535
536
537 BEGIN
538
539 hr_utility.set_location('Entering ' || l_proc,5);
540 l_effective_date := Null;
541
542 for cur_ma_rec in cur_ma loop
543 hr_utility.set_location( l_proc,10);
544 l_effective_date := cur_ma_rec.effective_date;
545 end loop;
546
547 open cur_get_seq;
548 fetch cur_get_seq into l_prog_name;
549 close cur_get_seq;
550
551 l_prog_name := 'GHR_MAW_PKG-' || l_prog_name;
552 g_log_name := l_prog_name;
553
554 hr_utility.set_location( l_proc,20);
555 upd_elig_flag_bef_selection
556 ( p_mass_award_id => p_mass_award_id,
557 p_rpa_type => l_rpa_type,
558 p_effective_date => l_effective_date);
559
560
561 hr_utility.set_location( l_proc,30);
562 ghr_mass_awards_elig.get_eligible_employees
563 ( p_mass_award_id => p_mass_award_id,
564 p_action_type => p_action_type,
565 p_errbuf => l_errbuf,
566 p_status => l_status,
567 p_retcode => l_retcode,
568 p_maxcheck => p_maxcheck);
569
570 p_errbuf := l_errbuf;
571 p_status := l_status;
572 p_retcode := l_retcode;
573
574 if l_retcode = 0 then
575 l_status_flag := 'P';
576 else
577 l_status_flag := 'E';
578 end if;
579
580 hr_utility.set_location( l_proc,40);
581 del_elig_flag_aft_selection
582 ( p_mass_award_id => p_mass_award_id,
583 p_rpa_type => l_rpa_type,
584 p_effective_date => l_effective_date);
585
586 hr_utility.set_location( 'Updating ghr_mass_awards table ...' ||l_proc,50);
587
588 IF p_action_type = 'FINAL' THEN
589 update ghr_mass_awards set status_flag = l_status_flag
590 where mass_award_id = p_mass_award_id;
591 END IF;
592
593 hr_utility.set_location('Leaving ' || l_proc,60);
594
595 EXCEPTION
596 WHEN others THEN
597 -- Reset IN OUT parameters and set OUT parameters
598
599 p_errbuf := NULL;
600 p_status := NULL;
601 p_retcode := NULL;
602 p_maxcheck := NULL;
603 hr_utility.set_location('Leaving ' || l_proc,65);
604 RAISE;
605
606 END main_awards;
607
608 PROCEDURE upd_elig_flag_bef_selection
609 (
610 p_mass_award_id in ghr_mass_awards.mass_award_id%TYPE,
611 p_rpa_type in ghr_pa_requests.rpa_type%TYPE,
612 p_effective_date in date
613 )
614
615 is
616
617 l_proc varchar2(72) := g_package || 'upd_elig_flag_bef_selection';
618 l_cntr number;
619
620 begin
621
622 hr_utility.set_location('Entering ' || l_proc,5);
623
624 select count(*) into l_cntr
625 from ghr_pa_requests
626 where mass_action_id = p_mass_award_id
627 and rpa_type = p_rpa_type
628 and pa_notification_id is null;
629
630 hr_utility.set_location('No of rows :' || to_char(l_cntr) || l_proc,10);
631
632 update ghr_pa_requests set
633 mass_action_eligible_flag = 'N'
634 where mass_action_id = p_mass_award_id
635 and rpa_type = p_rpa_type
636 and pa_notification_id is null;
637
638 --- commit; --- Form will commit
639
640 hr_utility.set_location('Leaving ' || l_proc,15);
641
642 end;
643
644 PROCEDURE del_elig_flag_aft_selection
645 (
646 p_mass_award_id in ghr_mass_awards.mass_award_id%TYPE,
647 p_rpa_type in ghr_pa_requests.rpa_type%TYPE,
648 p_effective_date in date
649 )
650
651 IS
652
653 l_proc varchar2(72) := g_package || 'del_elig_flag_aft_selection';
654 l_pa_request_id ghr_pa_requests.pa_request_id%type;
655
656 CURSOR cur_rpa_del is
657 SELECT pa_request_id
658 FROM ghr_pa_requests
659 WHERE mass_action_id = p_mass_award_id
660 AND rpa_type = p_rpa_type
661 AND mass_action_eligible_flag = 'N'
662 AND pa_notification_id is null;
663
664 BEGIN
665 hr_utility.set_location('Entering ' || l_proc,5);
666 FOR cur_rpa_del_rec in cur_rpa_del
667 LOOP
668 hr_utility.set_location( l_proc,10);
669 l_pa_request_id := cur_rpa_del_rec.pa_request_id;
670
671 -- Delete the shadow extra information
672 hr_utility.set_location( l_proc,15);
673 delete from ghr_pa_request_ei_shadow
674 where pa_request_id = l_pa_request_id;
675
676 -- Delete the shadow information
677 hr_utility.set_location( l_proc,20);
678 delete from ghr_pa_request_shadow
679 where pa_request_id = l_pa_request_id;
680
681 -- Delete the database extra information for the given person_id
682 hr_utility.set_location( l_proc,25);
683 delete from ghr_pa_request_extra_info
684 where pa_request_id = l_pa_request_id;
685
686 -- Delete the database RPA award routing history record for the given person_id
687 hr_utility.set_location( l_proc,35);
688 delete from ghr_pa_routing_history
689 where pa_request_id = l_pa_request_id;
690
691 -- Delete the database RPA award record for the given person_id
692 hr_utility.set_location( l_proc,35);
693 delete from ghr_pa_requests
694 where pa_request_id = l_pa_request_id;
695 END LOOP;
696 END del_elig_flag_aft_selection;
697
698 Procedure marpa_process
699 (
700 p_mass_award_id in ghr_mass_awards.mass_award_id%TYPE,
701 p_action_type in VARCHAR2,
702 p_rpa_type in ghr_pa_requests.rpa_type%TYPE,
703 p_effective_date in date,
704 p_person_id in per_people_f.person_id%TYPE,
705 p_pa_request_rec in out nocopy ghr_pa_requests%rowtype ,
706 p_log_text out nocopy varchar2,
707 p_maxcheck out nocopy number
708 )
709
710 IS
711
712 l_proc varchar2(72) := g_package || 'marpa_process';
713 l_pa_request_rec ghr_pa_requests%rowtype;
714 l_log_text varchar2(2000);
715 l_result boolean;
716
717 l_dummy varchar2(30);
718 l_dummy_number number;
719 l_pa_request_id ghr_pa_requests.pa_request_id%TYPE;
720 l_pa_notification_id ghr_pa_requests.pa_notification_id%TYPE;
721 l_rpa_type ghr_pa_requests.rpa_type%TYPE;
722 l_mass_action_select_flag ghr_pa_requests.mass_action_select_flag%TYPE;
723 --Bug#3804067 Added l_mass_aciton_comments variable
724 l_mass_action_comments ghr_pa_requests.mass_action_comments%TYPE;
725 l_object_version_number ghr_pa_requests.object_version_number%type;
726
727 l_1_prh_object_version_number ghr_pa_requests.object_version_number%type;
728 l_1_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
729 l_2_prh_object_version_number ghr_pa_requests.object_version_number%type;
730 l_2_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
731
732 l_approval_date date;
733 l_approving_off_work_title ghr_pa_requests.APPROVING_OFFICIAL_WORK_TITLE%type;
734 l_1_approval_status varchar2(10);
735
736 l_bt_award_amount ghr_pa_requests.award_amount%type;
737 l_bt_award_percentage ghr_pa_requests.award_percentage%type;
738 l_bs_award_amount ghr_pa_requests.award_amount%type;
739 l_bs_award_percentage ghr_pa_requests.award_percentage%type;
740 l_bd_award_amount ghr_pa_requests.award_amount%type;
741 l_bd_award_percentage ghr_pa_requests.award_percentage%type;
742
743 l_t_award_amount ghr_pa_requests.award_amount%type;
744 l_t_award_uom ghr_pa_requests.award_uom%type;
745 l_t_award_percentage ghr_pa_requests.award_percentage%type;
746
747 l_d_award_amount ghr_pa_requests.award_amount%type;
748 l_d_award_uom ghr_pa_requests.award_uom%type;
749 l_d_award_percentage ghr_pa_requests.award_percentage%type;
750
751 l_s_award_amount ghr_pa_requests.award_amount%type;
752 l_s_award_uom ghr_pa_requests.award_uom%type;
753 l_s_award_percentage ghr_pa_requests.award_percentage%type;
754
755 l_u_prh_object_version_number number;
756 l_i_pa_routing_history_id number;
757 l_i_prh_object_version_number number;
758
759 ma_rpaerror exception;
760 --Begin Bug # 4748927
761 l_asg_ei_data per_assignment_extra_info%rowtype;
762 --End Bug # 4748927
763
764 CURSOR cur_rpa_ei (p_pa_request_id number) is
765 SELECT pa_request_extra_info_id,
766 object_version_number,
767 rei_information9
768 FROM ghr_pa_request_extra_info
769 WHERE information_type = l_information_type
770 AND pa_request_id = p_pa_request_id;
771
772 l_ei_pa_request_extra_id ghr_pa_request_extra_info.pa_request_extra_info_id%type;
773 l_ei_ovn ghr_pa_request_extra_info.object_version_number%type;
774 l_ei_dae ghr_pa_request_extra_info.rei_information9%type;
775
776 CURSOR cur_rpa_ei_shadow (p_pa_request_id number) is
777 SELECT pa_request_extra_info_id
778 FROM ghr_pa_request_ei_shadow
779 WHERE information_type = l_information_type
780 AND pa_request_id = p_pa_request_id;
781
782 l_ei_shadow_id ghr_pa_request_ei_shadow.pa_request_extra_info_id%type;
783 L_CNT number(10);
784 l_error_flag BOOLEAN;
785
786 BEGIN
787 l_pa_request_rec := p_pa_request_rec;
788
789 begin
790
791 if p_action_type = 'FINAL' THEN
792 l_approval_date := sysdate;
793 l_1_approval_status := 'APPROVE';
794 end if;
795
796 hr_utility.set_location('Entering ...' || l_proc,120);
797 hr_utility.set_location('Noa_family_code value ' || l_pa_request_rec.noa_family_code,120);
798 get_pa_request_id_ovn
799 ( p_mass_award_id => p_mass_award_id,
800 p_effective_date => p_effective_date,
801 p_person_id => p_person_id,
802 p_pa_request_id => l_pa_request_id,
803 p_pa_notification_id => l_pa_notification_id,
804 p_rpa_type => l_rpa_type,
805 p_mass_action_sel_flag => l_mass_action_select_flag,
806 --Bug#3804067 Added mass action comments
807 p_mass_action_comments => l_mass_action_comments,
808 p_object_version_number => l_object_version_number);
809
810 -- Bug#3804067 assigning comments to l_pa_request_rec.
811 l_pa_request_rec.mass_action_comments := l_mass_action_comments;
812
813 if l_rpa_type is null then l_rpa_type := 'A'; end if;
814
815 IF l_pa_notification_id is null and l_rpa_type = 'A' then
816 IF l_pa_request_id is null THEN
817 -- Create SF52
818 hr_utility.set_location(l_proc,130);
819 begin
820 l_log_text := p_action_type || '-Error while creating the PA Request Rec. ';
821 g_log_text := p_action_type || '-Error while creating the PA Request Rec. ';
822 hr_utility.set_location('In MAR PA Bef Crt'||l_pa_request_rec.award_amount ,36);
823 -- Bug 3376761
824 -- check to see if award amount is within 25% of annual basic pay
825 -- other wise pass an error message in comments column.
826 -- Begin Bug# 4748927
827 /*l_award_salary :=
828 ghr_pay_calc.convert_amount(l_pa_request_rec.from_basic_pay
829 ,l_pa_request_rec.from_pay_basis,'PA');*/
830
831 ghr_history_fetch.fetch_asgei(
832 p_assignment_id => l_pa_request_rec.employee_assignment_id,
833 p_information_type => 'GHR_US_ASG_SF52',
834 p_date_effective => p_effective_date,
835 p_asg_ei_data => l_asg_ei_data);
836 ghr_pay_calc.award_amount_calc (
837 p_position_id => l_pa_request_rec.to_position_id
838 ,p_pay_plan => l_pa_request_rec.from_pay_plan
839 ,p_award_percentage => l_pa_request_rec.award_percentage
840 ,p_user_table_id => l_pa_request_rec.from_pay_table_identifier
841 ,p_grade_or_level => l_pa_request_rec.from_grade_or_level
842 ,p_effective_date => l_pa_request_rec.effective_date
843 ,p_basic_pay => l_pa_request_rec.from_basic_pay
844 ,p_adj_basic_pay => l_pa_request_rec.from_adj_basic_pay
845 ,p_duty_station_id => l_pa_request_rec.duty_station_id
846 ,p_prd => l_asg_ei_data.aei_information6
847 ,p_pay_basis => l_pa_request_rec.from_pay_basis
848 ,p_person_id => l_pa_request_rec.person_id
849 -- ,p_award_amount => l_pa_request_rec.award_amount Bug 5041967
850 ,p_award_amount => l_dummy
851 ,p_award_salary => l_pa_request_rec.award_salary
852 );
853
854 IF l_pa_request_rec.award_percentage IS NOT NULL THEN
855 l_pa_request_rec.award_amount := l_dummy;
856 END IF;
857 hr_utility.set_location('l_pa_request_rec.award_amount' || l_pa_request_rec.award_amount,111);
858 hr_utility.set_location('l_pa_request_rec.award_salary' || l_pa_request_rec.award_salary,111);
859
860 -- End Bug# 4748927
861 /*
862 IF l_pa_request_rec.award_amount > ROUND(l_award_salary * 0.25 , 0) THEN
863 l_cnt := INSTR(l_pa_request_rec.mass_action_comments, '; Award percentage cannot be greater than 25% of the basic annual pay.',1);
864 IF l_cnt =0 then
865 l_pa_request_rec.mass_action_comments := l_pa_request_rec.mass_action_comments || 'Award percentage cannot be greater than 25% of the basic annual pay.' ;
866 END IF;
867 END IF;
868 */
869 --Pradeep Commented the above and added the below for bug 3934195
870 l_error_flag := FALSE;
871 check_award_amount (p_noa_code => l_pa_request_rec.first_noa_code,
872 p_effective_date => l_pa_request_rec.effective_date,
873 p_award_amount => l_pa_request_rec.award_amount,
874 p_from_pay_plan => l_pa_request_rec.from_pay_plan,
875 p_from_basic_pay_pa => l_pa_request_rec.award_salary,
876 p_to_position_id => l_pa_request_rec.to_position_id,
877 p_comments => l_pa_request_rec.mass_action_comments,
878 p_error_flg => l_error_flag
879 );
880 IF l_error_flag THEN
881 p_maxcheck := 1;
882 END IF;
883 IF ( l_error_flag
884 AND NVL(l_mass_action_select_flag,'Y') <> 'N' ) THEN
885 l_mass_action_select_flag := 'N';
886 p_maxcheck := 1;
887 END IF;
888
889 --END OF Bug 3376761 .
890
891 ghr_sf52_api.create_sf52
892 ( p_noa_family_code => l_pa_request_rec.noa_family_code,
893 -- p_routing_group_id => l_pa_request_rec.routing_group_id,
894 -- This would be updated after creation.
895 p_proposed_effective_asap_flag => l_pa_request_rec.proposed_effective_asap_flag,
896 p_academic_discipline => l_pa_request_rec.academic_discipline,
897 p_additional_info_person_id => l_pa_request_rec.additional_info_person_id,
898 p_additional_info_tel_number => l_pa_request_rec.additional_info_tel_number,
899 -- p_altered_pa_request_id => l_pa_request_rec.altered_pa_request_id,
900 p_annuitant_indicator => l_pa_request_rec.annuitant_indicator,
901 p_annuitant_indicator_desc => l_pa_request_rec.annuitant_indicator_desc,
902 p_appropriation_code1 => l_pa_request_rec.appropriation_code1,
903 p_appropriation_code2 => l_pa_request_rec.appropriation_code2,
904 p_authorized_by_person_id => l_pa_request_rec.authorized_by_person_id,
905 p_authorized_by_title => l_pa_request_rec.authorized_by_title,
906 p_award_amount => l_pa_request_rec.award_amount,
907 p_award_uom => l_pa_request_rec.award_uom,
908 p_bargaining_unit_status => l_pa_request_rec.bargaining_unit_status,
909 p_citizenship => l_pa_request_rec.citizenship,
910 p_concurrence_date => l_pa_request_rec.concurrence_date,
911 p_custom_pay_calc_flag => l_pa_request_rec.custom_pay_calc_flag, -- Expecxt
912 p_duty_station_code => l_pa_request_rec.duty_station_code,
913 p_duty_station_desc => l_pa_request_rec.duty_station_desc,
914 p_duty_station_id => l_pa_request_rec.duty_station_id,
915 p_duty_station_location_id => l_pa_request_rec.duty_station_location_id,
916 p_education_level => l_pa_request_rec.education_level,
917 p_effective_date => l_pa_request_rec.effective_date,
918 p_employee_assignment_id => l_pa_request_rec.employee_assignment_id,
919 p_employee_date_of_birth => l_pa_request_rec.employee_date_of_birth,
920 p_employee_first_name => l_pa_request_rec.employee_first_name,
921 p_employee_last_name => l_pa_request_rec.employee_last_name,
922 p_employee_middle_names => l_pa_request_rec.employee_middle_names,
923 p_employee_national_identifier => l_pa_request_rec.employee_national_identifier,
924 p_fegli => l_pa_request_rec.fegli,
925 p_fegli_desc => l_pa_request_rec.fegli_desc,
926 p_first_action_la_code1 => l_pa_request_rec.first_action_la_code1,
927 p_first_action_la_code2 => l_pa_request_rec.first_action_la_code2,
928 p_first_action_la_desc1 => l_pa_request_rec.first_action_la_desc1,
929 p_first_action_la_desc2 => l_pa_request_rec.first_action_la_desc2,
930 -- p_first_noa_cancel_or_correct => l_pa_request_rec.first_noa_cancel_or_correct,
931 p_first_noa_code => l_pa_request_rec.first_noa_code,
932 p_first_noa_desc => l_pa_request_rec.first_noa_desc,
933 p_first_noa_id => l_pa_request_rec.first_noa_id,
934 p_first_noa_information1 => l_pa_request_rec.first_noa_information1,
935 p_first_noa_pa_request_id => l_pa_request_rec.first_noa_pa_request_id,
936 p_flsa_category => l_pa_request_rec.flsa_category,
937 p_from_adj_basic_pay => l_pa_request_rec.from_adj_basic_pay,
938 p_from_basic_pay => l_pa_request_rec.from_basic_pay,
939 p_from_grade_or_level => l_pa_request_rec.from_grade_or_level,
940 p_from_locality_adj => l_pa_request_rec.from_locality_adj,
941 p_from_occ_code => l_pa_request_rec.from_occ_code,
942 p_from_other_pay_amount => l_pa_request_rec.from_other_pay_amount,
943 p_from_pay_basis => l_pa_request_rec.from_pay_basis,
944 p_from_pay_plan => l_pa_request_rec.from_pay_plan,
945 -- FWFA Changes Bug#4444609
946 p_input_pay_rate_determinant => l_pa_request_rec.input_pay_rate_determinant,
947 p_from_pay_table_identifier => l_pa_request_rec.from_pay_table_identifier,
948 -- FWFA Changes
949 p_from_position_id => l_pa_request_rec.from_position_id,
950 p_from_position_org_line1 => l_pa_request_rec.from_position_org_line1,
951 p_from_position_org_line2 => l_pa_request_rec.from_position_org_line2,
952 p_from_position_org_line3 => l_pa_request_rec.from_position_org_line3,
953 p_from_position_org_line4 => l_pa_request_rec.from_position_org_line4,
954 p_from_position_org_line5 => l_pa_request_rec.from_position_org_line5,
955 p_from_position_org_line6 => l_pa_request_rec.from_position_org_line6,
956 p_from_position_number => l_pa_request_rec.from_position_number,
957 p_from_position_seq_no => l_pa_request_rec.from_position_seq_no,
958 p_from_position_title => l_pa_request_rec.from_position_title,
959 p_from_step_or_rate => l_pa_request_rec.from_step_or_rate,
960 p_from_total_salary => l_pa_request_rec.from_total_salary,
961 p_functional_class => l_pa_request_rec.functional_class,
962 p_notepad => l_pa_request_rec.notepad,
963 p_part_time_hours => l_pa_request_rec.part_time_hours,
964 -- FWFA Changes Bug#4444609
965 p_pay_rate_determinant => l_pa_request_rec.pay_rate_determinant,
966 -- FWFA Changes
967 p_person_id => l_pa_request_rec.person_id,
968 p_position_occupied => l_pa_request_rec.position_occupied,
969 p_proposed_effective_date => l_pa_request_rec.proposed_effective_date,
970 p_requested_by_person_id => l_pa_request_rec.requested_by_person_id,
971 p_requested_by_title => l_pa_request_rec.requested_by_title,
972 p_requested_date => l_pa_request_rec.requested_date,
973 p_requesting_office_remarks_de => l_pa_request_rec.requesting_office_remarks_desc,
974 p_requesting_office_remarks_fl => l_pa_request_rec.requesting_office_remarks_flag,
975 -- p_request_number => l_pa_request_rec.request_number,
976 p_resign_and_retire_reason_des => l_pa_request_rec.resign_and_retire_reason_desc,
977 p_retirement_plan => l_pa_request_rec.retirement_plan,
978 p_retirement_plan_desc => l_pa_request_rec.retirement_plan_desc,
979 -- p_second_action_la_code1 => l_pa_request_rec.second_action_la_code1,
980 -- p_second_action_la_code2 => l_pa_request_rec.second_action_la_code2,
981 -- p_second_action_la_desc1 => l_pa_request_rec.second_action_la_desc1,
982 -- p_second_action_la_desc2 => l_pa_request_rec.second_action_la_desc2,
983 -- p_second_noa_code => l_pa_request_rec.second_noa_code,
984 -- p_second_noa_desc => l_pa_request_rec.second_noa_desc,
985 -- p_second_noa_id => l_pa_request_rec.second_noa_id,
986 -- p_second_noa_pa_request_id => l_pa_request_rec.
987 p_service_comp_date => l_pa_request_rec.service_comp_date,
988 p_supervisory_status => l_pa_request_rec.supervisory_status,
989 p_tenure => l_pa_request_rec.tenure,
990 p_to_adj_basic_pay => l_pa_request_rec.to_adj_basic_pay,
991 p_to_basic_pay => l_pa_request_rec.to_basic_pay,
992 p_to_grade_id => l_pa_request_rec.to_grade_id,
993 p_to_grade_or_level => l_pa_request_rec.to_grade_or_level,
994 p_to_job_id => l_pa_request_rec.to_job_id,
995 p_to_locality_adj => l_pa_request_rec.to_locality_adj,
996 p_to_occ_code => l_pa_request_rec.to_occ_code,
997 p_to_organization_id => l_pa_request_rec.to_organization_id,
998 p_to_other_pay_amount => l_pa_request_rec.to_other_pay_amount,
999 p_to_au_overtime => l_pa_request_rec.to_au_overtime,
1000 p_to_auo_premium_pay_indicator => l_pa_request_rec.to_auo_premium_pay_indicator,
1001 p_to_availability_pay => l_pa_request_rec.to_availability_pay,
1002 p_to_ap_premium_pay_indicator => l_pa_request_rec.to_ap_premium_pay_indicator,
1003 p_to_retention_allowance => l_pa_request_rec.to_retention_allowance,
1004 p_to_supervisory_differential => l_pa_request_rec.to_supervisory_differential,
1005 p_to_staffing_differential => l_pa_request_rec.to_staffing_differential,
1006 p_to_pay_basis => l_pa_request_rec.to_pay_basis,
1007 p_to_pay_plan => l_pa_request_rec.to_pay_plan,
1008 p_to_position_id => l_pa_request_rec.to_position_id,
1009 p_to_position_org_line1 => l_pa_request_rec.to_position_org_line1,
1010 p_to_position_org_line2 => l_pa_request_rec.to_position_org_line2,
1011 p_to_position_org_line3 => l_pa_request_rec.to_position_org_line3,
1012 p_to_position_org_line4 => l_pa_request_rec.to_position_org_line4,
1013 p_to_position_org_line5 => l_pa_request_rec.to_position_org_line5,
1014 p_to_position_org_line6 => l_pa_request_rec.to_position_org_line6,
1015 p_to_position_number => l_pa_request_rec.to_position_number,
1016 p_to_position_seq_no => l_pa_request_rec.to_position_seq_no,
1017 p_to_position_title => l_pa_request_rec.to_position_title,
1018 p_to_step_or_rate => l_pa_request_rec.to_step_or_rate,
1019 p_to_total_salary => l_pa_request_rec.to_total_salary,
1020 p_veterans_preference => l_pa_request_rec.veterans_preference,
1021 p_veterans_pref_for_rif => l_pa_request_rec.veterans_pref_for_rif,
1022 p_veterans_status => l_pa_request_rec.veterans_status,
1023 p_work_schedule => l_pa_request_rec.work_schedule,
1024 p_work_schedule_desc => l_pa_request_rec.work_schedule_desc,
1025 p_year_degree_attained => l_pa_request_rec.year_degree_attained,
1026 p_first_lac1_information1 => l_pa_request_rec.first_lac1_information1,
1027 p_first_lac1_information2 => l_pa_request_rec.first_lac1_information2,
1028 p_first_lac1_information3 => l_pa_request_rec.first_lac1_information3,
1029 p_first_lac1_information4 => l_pa_request_rec.first_lac1_information4,
1030 p_first_lac1_information5 => l_pa_request_rec.first_lac1_information5,
1031 p_first_lac2_information1 => l_pa_request_rec.first_lac2_information1,
1032 p_first_lac2_information2 => l_pa_request_rec.first_lac2_information2,
1033 p_first_lac2_information3 => l_pa_request_rec.first_lac2_information3,
1034 p_first_lac2_information4 => l_pa_request_rec.first_lac2_information4,
1035 p_first_lac2_information5 => l_pa_request_rec.first_lac2_information5,
1036 p_second_lac1_information1 => l_pa_request_rec.second_lac1_information1,
1037 p_second_lac1_information2 => l_pa_request_rec.second_lac1_information1,
1038 p_second_lac1_information3 => l_pa_request_rec.second_lac1_information1,
1039 p_second_lac1_information4 => l_pa_request_rec.second_lac1_information1,
1040 p_second_lac1_information5 => l_pa_request_rec.second_lac1_information1,
1041 p_print_sf50_flag => 'N', -- true for all ??
1042 p_printer_name => Null,
1043 p_1_attachment_modified_flag => 'N',
1044 p_1_approved_flag => 'N',
1045 p_1_user_name_acted_on => Null,
1046 p_1_action_taken => 'NOT_ROUTED',
1047 p_2_user_name_routed_to => Null,
1048 p_2_groupbox_id => Null,
1049 p_2_routing_list_id => Null,
1050 p_2_routing_seq_number => Null,
1051 p_to_retention_allow_percentag => l_pa_request_rec.to_retention_allow_percentage,
1052 p_to_supervisory_diff_percenta => l_pa_request_rec.to_supervisory_diff_percentage,
1053 p_to_staffing_diff_percentage => l_pa_request_rec.to_staffing_diff_percentage ,
1054 p_award_percentage => l_pa_request_rec.award_percentage,
1055 p_pa_request_id => l_pa_request_rec.pa_request_id,
1056 p_par_object_version_number => l_pa_request_rec.object_version_number,
1057 p_1_pa_routing_history_id => l_1_pa_routing_history_id,
1058 p_1_prh_object_version_number => l_1_prh_object_version_number,
1059 p_2_pa_routing_history_id => l_2_pa_routing_history_id,
1060 -- Bug#4486823 RRR Changes
1061 p_award_salary => l_pa_request_rec.award_salary,
1062 -- Bug#4486823 RRR Changes
1063 p_2_prh_object_version_number => l_2_prh_object_version_number,
1064 p_rpa_type => p_rpa_type,
1065 p_mass_action_id => p_mass_award_id,
1066 p_mass_action_eligible_flag => 'Y',
1067
1068 --p_mass_action_select_flag => 'Y'
1069 p_mass_action_select_flag => NVL(l_mass_action_select_flag,'Y')
1070
1071 ,p_approving_official_full_name => l_pa_request_rec.approving_official_full_name
1072 ,p_approval_date => l_approval_date
1073 ,p_approving_official_work_titl => l_pa_request_rec.approving_official_work_title
1074 ,p_1_approval_status => l_1_approval_status
1075 -- Bug 3376761
1076 ,p_mass_action_comments => l_pa_request_rec.mass_action_comments
1077 -- End of Bug 3376761
1078 );
1079 exception
1080 WHEN OTHERS THEN
1081 hr_utility.set_location( ' Sql error : '||sqlerrm(sqlcode) ,132);
1082 hr_utility.set_location(l_proc,132);
1083 l_log_text := substr(l_log_text || ' Sql error : '|| sqlerrm(sqlcode),1,2000);
1084 g_log_text := substr(l_log_text || ' Sql error : '|| sqlerrm(sqlcode),1,2000);
1085 raise ma_rpaerror;
1086 end;
1087
1088 hr_utility.set_location(l_proc,135);
1089 begin
1090 l_log_text := p_action_type || '-Error After creating the RPA before upd ';
1091 g_log_text := p_action_type || '-Error After creating the RPA before upd ';
1092 ghr_par_upd.upd(
1093 p_pa_request_id => l_pa_request_rec.pa_request_id,
1094 p_object_version_number => l_pa_request_rec.object_version_number,
1095 p_agency_code => l_pa_request_rec.agency_code,
1096 p_employee_dept_or_agency => l_pa_request_rec.employee_dept_or_agency,
1097 p_personnel_office_id => l_pa_request_rec.personnel_office_id,
1098 p_from_office_symbol => l_pa_request_rec.from_office_symbol);
1099 exception
1100 WHEN OTHERS THEN
1101 hr_utility.set_location(l_proc,136);
1102 l_log_text := l_log_text || ' Sql error : '||sqlerrm(sqlcode);
1103 g_log_text := l_log_text || ' Sql error : '||sqlerrm(sqlcode);
1104 raise ma_rpaerror;
1105 end;
1106
1107 hr_utility.set_location(l_proc,138);
1108 create_shadow_row (p_rpa_data => l_pa_request_rec);
1109 hr_utility.set_location(' Shadow created ...'|| l_proc,138);
1110
1111 ----Initialize the RPA extra information
1112 FOR cur_rpa_ei_rec in cur_rpa_ei(l_pa_request_rec.pa_request_id)
1113 LOOP
1114 hr_utility.set_location( l_proc,10);
1115 l_ei_pa_request_extra_id := cur_rpa_ei_rec.pa_request_extra_info_id;
1116 l_ei_ovn := cur_rpa_ei_rec.object_version_number;
1117 l_ei_dae := cur_rpa_ei_rec.rei_information9;
1118 exit;
1119 END LOOP;
1120
1121 IF l_ei_dae is not null then
1122 IF l_ei_pa_request_extra_id is not null then
1123 ghr_par_extra_info_api.update_pa_request_extra_info
1124 (p_validate => false,
1125 p_rei_information3 => null,
1126 p_rei_information4 => null,
1127 p_rei_information6 => null,
1128 p_rei_information7 => null,
1129 p_rei_information9 => null,
1130 p_rei_information10 => null,
1131 p_pa_request_extra_info_id => l_ei_pa_request_extra_id,
1132 p_object_version_number => l_ei_ovn );
1133 END IF;
1134 END IF;
1135
1136 ----Initialize the RPA extra information Shadow.
1137 FOR cur_rpa_ei_shadow_rec in cur_rpa_ei_shadow(l_pa_request_rec.pa_request_id)
1138 LOOP
1139 hr_utility.set_location( l_proc,10);
1140 l_ei_shadow_id := cur_rpa_ei_shadow_rec.pa_request_extra_info_id;
1141 exit;
1142 END LOOP;
1143
1144 IF l_ei_shadow_id is not null Then
1145 update ghr_pa_request_ei_shadow
1146 set rei_information3 = null,
1147 rei_information4 = null,
1148 rei_information6 = null,
1149 rei_information7 = null,
1150 rei_information9 = null,
1151 rei_information10 = null
1152 where pa_request_extra_info_id = l_ei_shadow_id;
1153 END IF;
1154
1155 ----End Initialize.
1156
1157 hr_utility.set_location(l_proc,140);
1158 refresh_award_details
1159 (
1160 p_mass_award_id => p_mass_award_id,
1161 p_rpa_type => p_rpa_type,
1162 p_effective_date => p_effective_date,
1163 p_person_id => p_person_id,
1164 p_pa_request_id => l_pa_request_rec.pa_request_id );
1165
1166 ELSE
1167
1168 --- Update SF52
1169 l_pa_request_rec.award_amount := Null;
1170 l_pa_request_rec.award_uom := Null;
1171 l_pa_request_rec.award_percentage := Null;
1172
1173 hr_utility.set_location(l_proc,141);
1174 ----- Get Award details from the template and also extra information
1175 get_award_details
1176 ( p_mass_award_id => p_mass_award_id,
1177 p_rpa_type => 'TA',
1178 p_effective_date => p_effective_date,
1179 p_person_id => null,
1180 p_award_amount => l_t_award_amount,
1181 p_award_uom => l_t_award_uom,
1182 p_award_percentage => l_t_award_percentage,
1183 p_award_agency => l_dummy,
1184 p_award_type => l_dummy,
1185 p_group_award => l_dummy,
1186 p_tangible_benefit_dollars => l_dummy,
1187 p_date_award_earned => l_dummy,
1188 p_appropriation_code => l_dummy);
1189
1190 l_bt_award_amount := l_t_award_amount;
1191 l_bt_award_percentage := l_t_award_percentage;
1192 hr_utility.set_location('l_bt_award_amount' || l_bt_award_amount,142);
1193 hr_utility.set_location(l_proc,142);
1194 ----- Get Award details from the database
1195 get_award_details
1196 ( p_mass_award_id => p_mass_award_id,
1197 p_rpa_type => p_rpa_type,
1198 p_effective_date => p_effective_date,
1199 p_person_id => p_person_id,
1200 p_award_amount => l_d_award_amount,
1201 p_award_uom => l_d_award_uom,
1202 p_award_percentage => l_d_award_percentage,
1203 p_award_agency => l_dummy,
1204 p_award_type => l_dummy,
1205 p_group_award => l_dummy,
1206 p_tangible_benefit_dollars => l_dummy,
1207 p_date_award_earned => l_dummy,
1208 p_appropriation_code => l_dummy);
1209
1210 l_bd_award_amount := l_d_award_amount;
1211 l_bd_award_percentage := l_d_award_percentage;
1212 hr_utility.set_location('l_bd_award_amount' || l_bd_award_amount,142);
1213 hr_utility.set_location(l_proc,143);
1214 ----- Get Award details from the shadow
1215 get_award_details_shadow
1216 ( p_pa_request_id => l_pa_request_id,
1217 p_award_amount => l_s_award_amount,
1218 p_award_uom => l_s_award_uom,
1219 p_award_percentage => l_s_award_percentage,
1220 p_award_agency => l_dummy,
1221 p_award_type => l_dummy,
1222 p_group_award => l_dummy,
1223 p_tangible_benefit_dollars => l_dummy,
1224 p_date_award_earned => l_dummy,
1225 p_appropriation_code => l_dummy );
1226
1227 l_bs_award_amount := l_s_award_amount;
1228 l_bs_award_percentage := l_s_award_percentage;
1229 hr_utility.set_location('l_bs_award_amount' || l_bs_award_amount,142);
1230
1231 hr_utility.set_location(l_proc,144);
1232 set_ei
1233 (p_shadow => l_s_award_amount,
1234 p_template => l_t_award_amount,
1235 p_person => l_d_award_amount);
1236
1237 set_ei
1238 (p_shadow => l_s_award_uom,
1239 p_template => l_t_award_uom,
1240 p_person => l_d_award_uom);
1241
1242 set_ei
1243 (p_shadow => l_s_award_percentage,
1244 p_template => l_t_award_percentage,
1245 p_person => l_d_award_percentage);
1246 -- Begin Bug# 4748927
1247 /*
1248 l_award_salary :=
1249 ghr_pay_calc.convert_amount(l_pa_request_rec.from_basic_pay
1250 ,l_pa_request_rec.from_pay_basis,'PA');*/
1251
1252 if l_d_award_percentage is not null then
1253 l_d_award_amount :=
1254 ROUND(l_pa_request_rec.award_salary * NVL(l_d_award_percentage,0) / 100 , 0);
1255 end if;
1256 if l_s_award_percentage is not null then
1257 l_s_award_amount :=
1258 ROUND(l_pa_request_rec.award_salary * NVL(l_s_award_percentage,0) / 100 , 0);
1259 end if;
1260
1261 -- End Bug# 4748927
1262
1263 l_pa_request_rec.award_amount := l_d_award_amount;
1264 l_pa_request_rec.award_uom := l_d_award_uom;
1265 l_pa_request_rec.award_percentage := l_d_award_percentage;
1266
1267 hr_utility.set_location('l_pa_request_rec.award_amount' || l_pa_request_rec.award_amount,111);
1268 hr_utility.set_location('l_pa_request_rec.award_salary' || l_pa_request_rec.award_salary,111);
1269 -- End Sundar Test
1270
1271 -- Bug 3376761
1272 -- check to see if the award amount is within 25% of basic pay
1273 -- other write error message in comments column.
1274 /*
1275 IF l_pa_request_rec.award_amount > ROUND(l_award_salary * 0.25 , 0) THEN
1276 l_cnt := INSTR(l_pa_request_rec.mass_action_comments, 'Award percentage cannot be greater than 25% of the basic annual pay.',1);
1277 IF l_cnt =0 then
1278 l_pa_request_rec.mass_action_comments := l_pa_request_rec.mass_action_comments || '; Award percentage cannot be greater than 25% of the basic annual pay.' ;
1279 END IF;
1280 END IF;
1281 */
1282 hr_utility.set_location('In MAR PA Bef Upd'||l_pa_request_rec.award_amount ,35);
1283 --Pradeep Commented the above and added the below for bug 3934195
1284 l_error_flag := FALSE;
1285 check_award_amount (p_noa_code => l_pa_request_rec.first_noa_code,
1286 p_effective_date => l_pa_request_rec.effective_date,
1287 p_award_amount => l_pa_request_rec.award_amount,
1288 p_from_pay_plan => l_pa_request_rec.from_pay_plan,
1289 p_from_basic_pay_pa => l_pa_request_rec.award_salary,
1290 p_to_position_id => l_pa_request_rec.to_position_id,
1291 p_comments => l_pa_request_rec.mass_action_comments,
1292 p_error_flg => l_error_flag
1293 );
1294 IF l_error_flag THEN
1295 p_maxcheck := 1;
1296 END IF;
1297
1298 IF ( l_error_flag
1299 AND NVL(l_mass_action_select_flag,'Y') <> 'N' ) THEN
1300 l_mass_action_select_flag := 'N';
1301 p_maxcheck := 1;
1302 END IF;
1303
1304 --END OF Bug 3376761 .
1305
1306 hr_utility.set_location(l_proc || 'pa_request_id ' || to_char(l_pa_request_id),150);
1307 hr_utility.set_location(l_proc || 'Object version ' || to_char(l_object_version_number),151);
1308
1309 begin
1310 l_log_text := 'Error while Updating the PA Request Rec. ';
1311 g_log_text := 'Error while Updating the PA Request Rec. ';
1312 ghr_sf52_api.update_sf52
1313 (
1314 p_pa_request_id => l_pa_request_id,
1315 -- p_pa_notification_id => l_pa_request_rec.pa_notification_id,
1316 p_noa_family_code => l_pa_request_rec.noa_family_code,
1317 p_routing_group_id => l_pa_request_rec.routing_group_id,
1318 p_par_object_version_number => l_object_version_number,
1319 p_proposed_effective_asap_flag => l_pa_request_rec.proposed_effective_asap_flag,
1320 p_academic_discipline => l_pa_request_rec.academic_discipline,
1321 p_additional_info_person_id => l_pa_request_rec.additional_info_person_id,
1322 p_additional_info_tel_number => l_pa_request_rec.additional_info_tel_number ,
1323 --p_altered_pa_request_id => l_pa_request_rec.altered_pa_request_id,
1324 p_annuitant_indicator => l_pa_request_rec.annuitant_indicator,
1325 p_annuitant_indicator_desc => l_pa_request_rec.annuitant_indicator_desc,
1326 p_appropriation_code1 => l_pa_request_rec.appropriation_code1,
1327 p_appropriation_code2 => l_pa_request_rec.appropriation_code2,
1328 p_approval_date => l_approval_date , --l_pa_request_rec.approval_date,
1329 p_approving_official_full_name => l_pa_request_rec.approving_official_full_name,
1330 p_approving_official_work_titl => l_pa_request_rec.approving_official_work_title,
1331 p_authorized_by_person_id => l_pa_request_rec.authorized_by_person_id ,
1332 p_authorized_by_title => l_pa_request_rec.authorized_by_title,
1333 p_award_amount => l_pa_request_rec.award_amount,
1334 p_award_uom => l_pa_request_rec.award_uom,
1335 p_bargaining_unit_status => l_pa_request_rec.bargaining_unit_status,
1336 p_citizenship => l_pa_request_rec.citizenship,
1337 p_concurrence_date => l_pa_request_rec.concurrence_date,
1338 p_custom_pay_calc_flag => l_pa_request_rec.custom_pay_calc_flag,
1339 p_duty_station_code => l_pa_request_rec.duty_station_code,
1340 p_duty_station_desc => l_pa_request_rec.duty_station_desc,
1341 p_duty_station_id => l_pa_request_rec.duty_station_id,
1342 p_duty_station_location_id => l_pa_request_rec.duty_station_location_id,
1343 p_education_level => l_pa_request_rec.education_level,
1344 p_effective_date => l_pa_request_rec.effective_date,
1345 p_employee_assignment_id => l_pa_request_rec.employee_assignment_id,
1346 p_employee_date_of_birth => l_pa_request_rec.employee_date_of_birth,
1347 p_employee_first_name => l_pa_request_rec.employee_first_name,
1348 p_employee_last_name => l_pa_request_rec.employee_last_name,
1349 p_employee_middle_names => l_pa_request_rec.employee_middle_names,
1350 p_employee_national_identifier => l_pa_request_rec.employee_national_identifier,
1351 p_fegli => l_pa_request_rec.fegli,
1352 p_fegli_desc => l_pa_request_rec.fegli_desc,
1353 p_first_action_la_code1 => l_pa_request_rec.first_action_la_code1,
1354 p_first_action_la_code2 => l_pa_request_rec.first_action_la_code2,
1355 p_first_action_la_desc1 => l_pa_request_rec.first_action_la_desc1,
1356 p_first_action_la_desc2 => l_pa_request_rec.first_action_la_desc2,
1357 p_first_noa_cancel_or_correct => l_pa_request_rec.first_noa_cancel_or_correct,
1358 p_first_noa_code => l_pa_request_rec.first_noa_code,
1359 p_first_noa_desc => l_pa_request_rec.first_noa_desc,
1360 p_first_noa_id => l_pa_request_rec.first_noa_id,
1361 p_first_noa_pa_request_id => l_pa_request_rec.first_noa_pa_request_id,
1362 p_flsa_category => l_pa_request_rec.flsa_category,
1363 p_forwarding_address_line1 => l_pa_request_rec.forwarding_address_line1,
1364 p_forwarding_address_line2 => l_pa_request_rec.forwarding_address_line2,
1365 p_forwarding_address_line3 => l_pa_request_rec.forwarding_address_line3,
1366 p_forwarding_country => l_pa_request_rec.forwarding_country,
1367 p_forwarding_country_short_nam => l_pa_request_rec.forwarding_country_short_name,
1368 p_forwarding_postal_code => l_pa_request_rec.forwarding_postal_code,
1369 p_forwarding_region_2 => l_pa_request_rec.forwarding_region_2,
1370 p_forwarding_town_or_city => l_pa_request_rec.forwarding_town_or_city,
1371 p_from_adj_basic_pay => l_pa_request_rec.from_adj_basic_pay,
1372 p_from_basic_pay => l_pa_request_rec.from_basic_pay,
1373 p_from_grade_or_level => l_pa_request_rec.from_grade_or_level,
1374 p_from_locality_adj => l_pa_request_rec.from_locality_adj,
1375 p_from_occ_code => l_pa_request_rec.from_occ_code,
1376 p_from_other_pay_amount => l_pa_request_rec.from_other_pay_amount,
1377 p_from_pay_basis => l_pa_request_rec.from_pay_basis,
1378 p_from_pay_plan => l_pa_request_rec.from_pay_plan,
1379 -- FWFA Changes Bug#4444609
1380 p_input_pay_rate_determinant => l_pa_request_rec.input_pay_rate_determinant,
1381 p_from_pay_table_identifier => l_pa_request_rec.from_pay_table_identifier,
1382 -- FWFA Changes
1383 p_from_position_id => l_pa_request_rec.from_position_id,
1384 p_from_position_org_line1 => l_pa_request_rec.from_position_org_line1,
1385 p_from_position_org_line2 => l_pa_request_rec.from_position_org_line2,
1386 p_from_position_org_line3 => l_pa_request_rec.from_position_org_line3,
1387 p_from_position_org_line4 => l_pa_request_rec.from_position_org_line4,
1388 p_from_position_org_line5 => l_pa_request_rec.from_position_org_line5,
1389 p_from_position_org_line6 => l_pa_request_rec.from_position_org_line6,
1390 p_from_position_number => l_pa_request_rec.from_position_number,
1391 p_from_position_seq_no => l_pa_request_rec.from_position_seq_no,
1392 p_from_position_title => l_pa_request_rec.from_position_title,
1393 p_from_step_or_rate => l_pa_request_rec.from_step_or_rate,
1394 p_from_total_salary => l_pa_request_rec.from_total_salary,
1395 p_functional_class => l_pa_request_rec.functional_class,
1396 p_notepad => l_pa_request_rec.notepad,
1397 p_part_time_hours => l_pa_request_rec.part_time_hours,
1398 -- FWFA Changes Bug#4444609
1399 p_pay_rate_determinant => l_pa_request_rec.pay_rate_determinant,
1400 p_person_id => l_pa_request_rec.person_id,
1401 p_position_occupied => l_pa_request_rec.position_occupied,
1402 p_proposed_effective_date => l_pa_request_rec.proposed_effective_date,
1403 p_requested_by_person_id => l_pa_request_rec.requested_by_person_id,
1404 p_requested_by_title => l_pa_request_rec.requested_by_title,
1405 p_requested_date => l_pa_request_rec.requested_date,
1406 p_requesting_office_remarks_de => l_pa_request_rec.requesting_office_remarks_desc,
1407 p_requesting_office_remarks_fl => l_pa_request_rec.requesting_office_remarks_flag,
1408 p_request_number => l_pa_request_rec.request_number,
1409 p_resign_and_retire_reason_des => l_pa_request_rec.resign_and_retire_reason_desc,
1410 p_retirement_plan => l_pa_request_rec.retirement_plan,
1411 p_retirement_plan_desc => l_pa_request_rec.retirement_plan_desc,
1412 p_second_action_la_code1 => l_pa_request_rec.second_action_la_code1,
1413 p_second_action_la_code2 => l_pa_request_rec.second_action_la_code2,
1414 p_second_action_la_desc1 => l_pa_request_rec.second_action_la_desc1,
1415 p_second_action_la_desc2 => l_pa_request_rec.second_action_la_desc2,
1416 p_second_noa_cancel_or_correct => l_pa_request_rec.second_noa_cancel_or_correct,
1417 p_second_noa_code => l_pa_request_rec.second_noa_code,
1418 p_second_noa_desc => l_pa_request_rec.second_noa_desc,
1419 p_second_noa_id => l_pa_request_rec.second_noa_id,
1420 p_second_noa_pa_request_id => l_pa_request_rec.second_noa_pa_request_id,
1421 p_service_comp_date => l_pa_request_rec.service_comp_date,
1422 p_supervisory_status => l_pa_request_rec.supervisory_status,
1423 p_tenure => l_pa_request_rec.tenure,
1424 p_to_adj_basic_pay => l_pa_request_rec.to_adj_basic_pay,
1425 p_to_basic_pay => l_pa_request_rec.to_basic_pay,
1426 p_to_grade_id => l_pa_request_rec.to_grade_id,
1427 p_to_grade_or_level => l_pa_request_rec.to_grade_or_level,
1428 p_to_job_id => l_pa_request_rec.to_job_id,
1429 p_to_locality_adj => l_pa_request_rec.to_locality_adj,
1430 p_to_occ_code => l_pa_request_rec.to_occ_code,
1431 p_to_organization_id => l_pa_request_rec.to_organization_id,
1432 p_to_other_pay_amount => l_pa_request_rec.to_other_pay_amount,
1433 p_to_au_overtime => l_pa_request_rec.to_au_overtime,
1434 p_to_auo_premium_pay_indicator => l_pa_request_rec.to_auo_premium_pay_indicator,
1435 p_to_availability_pay => l_pa_request_rec.to_availability_pay,
1436 p_to_ap_premium_pay_indicator => l_pa_request_rec.to_ap_premium_pay_indicator,
1437 p_to_retention_allowance => l_pa_request_rec.to_retention_allowance,
1438 p_to_supervisory_differential => l_pa_request_rec.to_supervisory_differential,
1439 p_to_staffing_differential => l_pa_request_rec.to_staffing_differential,
1440 p_to_pay_basis => l_pa_request_rec.to_pay_basis,
1441 p_to_pay_plan => l_pa_request_rec.to_pay_plan,
1442 -- FWFA Changes
1443 p_to_pay_table_identifier => l_pa_request_rec.to_pay_table_identifier,
1444 -- FWFA Changes
1445 p_to_position_id => l_pa_request_rec.to_position_id,
1446 p_to_position_org_line1 => l_pa_request_rec.to_position_org_line1,
1447 p_to_position_org_line2 => l_pa_request_rec.to_position_org_line2,
1448 p_to_position_org_line3 => l_pa_request_rec.to_position_org_line3,
1449 p_to_position_org_line4 => l_pa_request_rec.to_position_org_line4,
1450 p_to_position_org_line5 => l_pa_request_rec.to_position_org_line5,
1451 p_to_position_org_line6 => l_pa_request_rec.to_position_org_line6,
1452 p_to_position_number => l_pa_request_rec.to_position_number,
1453 p_to_position_seq_no => l_pa_request_rec.to_position_seq_no,
1454 p_to_position_title => l_pa_request_rec.to_position_title,
1455 p_to_step_or_rate => l_pa_request_rec.to_step_or_rate,
1456 p_to_total_salary => l_pa_request_rec.to_total_salary,
1457 p_veterans_preference => l_pa_request_rec.veterans_preference,
1458 p_veterans_pref_for_rif => l_pa_request_rec.veterans_pref_for_rif,
1459 p_veterans_status => l_pa_request_rec.veterans_status,
1460 p_work_schedule => l_pa_request_rec.work_schedule,
1461 p_work_schedule_desc => l_pa_request_rec.work_schedule_desc,
1462 p_year_degree_attained => l_pa_request_rec.year_degree_attained,
1463 p_first_noa_information1 => l_pa_request_rec.first_noa_information1,
1464 p_first_noa_information2 => l_pa_request_rec.first_noa_information2,
1465 p_first_noa_information3 => l_pa_request_rec.first_noa_information3,
1466 p_first_noa_information4 => l_pa_request_rec.first_noa_information4,
1467 p_first_noa_information5 => l_pa_request_rec.first_noa_information5,
1468 p_second_lac1_information1 => l_pa_request_rec.second_lac1_information1,
1469 p_second_lac1_information2 => l_pa_request_rec.second_lac1_information2,
1470 p_second_lac1_information3 => l_pa_request_rec.second_lac1_information3,
1471 p_second_lac1_information4 => l_pa_request_rec.second_lac1_information4,
1472 p_second_lac1_information5 => l_pa_request_rec.second_lac1_information5,
1473 p_second_lac2_information1 => l_pa_request_rec.second_lac2_information1,
1474 p_second_lac2_information2 => l_pa_request_rec.second_lac2_information2,
1475 p_second_lac2_information3 => l_pa_request_rec.second_lac2_information3,
1476 p_second_lac2_information4 => l_pa_request_rec.second_lac2_information4,
1477 p_second_lac2_information5 => l_pa_request_rec.second_lac2_information5,
1478 p_second_noa_information1 => l_pa_request_rec.second_noa_information1,
1479 p_second_noa_information2 => l_pa_request_rec.second_noa_information2,
1480 p_second_noa_information3 => l_pa_request_rec.second_noa_information3,
1481 p_second_noa_information4 => l_pa_request_rec.second_noa_information4,
1482 p_second_noa_information5 => l_pa_request_rec.second_noa_information5,
1483 p_first_lac1_information1 => l_pa_request_rec.first_lac1_information1,
1484 p_first_lac1_information2 => l_pa_request_rec.first_lac1_information2,
1485 p_first_lac1_information3 => l_pa_request_rec.first_lac1_information3,
1486 p_first_lac1_information4 => l_pa_request_rec.first_lac1_information4,
1487 p_first_lac1_information5 => l_pa_request_rec.first_lac1_information5,
1488 p_first_lac2_information1 => l_pa_request_rec.first_lac2_information1,
1489 p_first_lac2_information2 => l_pa_request_rec.first_lac2_information2,
1490 p_first_lac2_information3 => l_pa_request_rec.first_lac2_information3,
1491 p_first_lac2_information4 => l_pa_request_rec.first_lac2_information4,
1492 p_first_lac2_information5 => l_pa_request_rec.first_lac2_information5,
1493 ----- p_print_sf50_flag
1494 ----- p_printer_name
1495 ----- p_u_attachment_modified_flag => l_pa_request_rec.u_attachment_modified_flag,
1496 ----- p_u_approved_flag => l_pa_request_rec.u_approved_flag,
1497 ----- p_u_user_name_acted_on => l_pa_request_rec.u_user_name_acted_on,
1498 p_u_action_taken => 'NOT_ROUTED',
1499 p_u_approval_status => l_1_approval_status, --l_pa_request_rec.u_approval_status,
1500 ----- p_i_user_name_routed_to => l_pa_request_rec.i_user_name_routed_to,
1501 ----- p_i_groupbox_id => l_pa_request_rec.i_groupbox_id,
1502 ----- p_i_routing_list_id => l_pa_request_rec.i_routing_list_id,
1503 ----- p_i_routing_seq_number => l_pa_request_rec.i_routing_seq_number,
1504
1505 p_to_retention_allow_percentag => l_pa_request_rec.to_retention_allow_percentage,
1506 p_to_supervisory_diff_percenta => l_pa_request_rec.to_supervisory_diff_percentage,
1507 p_to_staffing_diff_percentage => l_pa_request_rec.to_staffing_diff_percentage,
1508 p_award_percentage => l_pa_request_rec.award_percentage,
1509 p_rpa_type => p_rpa_type,
1510 p_mass_action_id => p_mass_award_id,
1511 p_mass_action_eligible_flag => 'Y',
1512 p_u_prh_object_version_number => l_u_prh_object_version_number ,
1513 p_i_pa_routing_history_id => l_i_pa_routing_history_id,
1514 p_i_prh_object_version_number => l_i_prh_object_version_number
1515
1516 -- Bug 3376761
1517 ,p_mass_action_comments => l_pa_request_rec.mass_action_comments
1518 -- End of Bug 3376761
1519 -- Bug#4486823 RRR Changes
1520 ,p_award_salary => l_pa_request_rec.award_salary
1521 -- Bug#4486823 RRR Changes
1522 ,p_mass_action_select_flag => NVL(l_mass_action_select_flag,'Y')
1523 );
1524 exception
1525 WHEN OTHERS THEN
1526 hr_utility.set_location(l_proc,153);
1527 l_log_text := l_log_text || ' Sql error : '||sqlerrm(sqlcode);
1528 g_log_text := l_log_text || ' Sql error : '||sqlerrm(sqlcode);
1529 raise ma_rpaerror;
1530 end;
1531
1532 hr_utility.set_location('u ovn ' || to_char(l_u_prh_object_version_number),154);
1533 hr_utility.set_location('prh id ' || to_char(l_i_pa_routing_history_id),154);
1534 hr_utility.set_location('prh ovn ' || to_char(l_i_prh_object_version_number),154);
1535 hr_utility.set_location('par upd ' || to_char(l_object_version_number),154);
1536 hr_utility.set_location('par upd ' || to_char(l_pa_request_id),155);
1537 hr_utility.set_location(l_proc,156);
1538 begin
1539 l_log_text := p_action_type || '-Error After updating the RPA before upd ';
1540 g_log_text := p_action_type || '-Error After updating the RPA before upd ';
1541 ghr_par_upd.upd(
1542 p_pa_request_id => l_pa_request_id,
1543 p_object_version_number => l_object_version_number,
1544 p_agency_code => l_pa_request_rec.agency_code,
1545 p_employee_dept_or_agency => l_pa_request_rec.employee_dept_or_agency,
1546 p_personnel_office_id => l_pa_request_rec.personnel_office_id,
1547 p_from_office_symbol => l_pa_request_rec.from_office_symbol);
1548 exception
1549 WHEN OTHERS THEN
1550 hr_utility.set_location(l_proc,153);
1551 l_log_text := l_log_text || ' Sql error : '||sqlerrm(sqlcode);
1552 g_log_text := l_log_text || ' Sql error : '||sqlerrm(sqlcode);
1553 raise ma_rpaerror;
1554 end;
1555
1556 hr_utility.set_location(l_proc,158);
1557 l_pa_request_rec.award_amount := l_s_award_amount;
1558 l_pa_request_rec.award_uom := l_s_award_uom;
1559 l_pa_request_rec.award_percentage := l_s_award_percentage;
1560
1561 l_pa_request_rec.pa_request_id := l_pa_request_id;
1562 l_pa_request_rec.object_version_number := l_object_version_number;
1563 update_shadow_row (p_rpa_data => l_pa_request_rec,
1564 p_result => l_result);
1565 if l_result then
1566 hr_utility.set_location('Shadow updated ..'|| l_proc,158);
1567 else
1568 hr_utility.set_location('Shadow not found..'|| l_proc,158);
1569 end if;
1570
1571 hr_utility.set_location(l_proc,160);
1572 refresh_award_details
1573 (
1574 p_mass_award_id => p_mass_award_id,
1575 p_rpa_type => p_rpa_type,
1576 p_effective_date => p_effective_date,
1577 p_person_id => p_person_id,
1578 p_pa_request_id => l_pa_request_id );
1579
1580 p_pa_request_rec := l_pa_request_rec;
1581 p_log_text := l_log_text;
1582
1583 hr_utility.set_location('Leaving ...' || l_proc,161);
1584
1585 END IF; ------ Creation of SF52 and Update of SF52
1586 END IF;
1587 exception when ma_rpaerror then
1588 p_pa_request_rec := l_pa_request_rec;
1589 p_log_text := l_log_text;
1590 raise;
1591 when others then
1592 p_pa_request_rec := l_pa_request_rec;
1593 p_log_text := l_log_text;
1594 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),162);
1595 l_log_text := p_action_type || '-Error raised in marpa process others ';
1596 l_log_text := l_log_text || ' Sql error : '||sqlerrm(sqlcode);
1597 raise ma_rpaerror;
1598 end;
1599 p_pa_request_rec := l_pa_request_rec;
1600 p_log_text := l_log_text;
1601 END marpa_process;
1602
1603 Procedure build_rpa_for_mass_awards
1604 (
1605 p_mass_award_id in ghr_mass_awards.mass_award_id%TYPE,
1606 p_action_type in VARCHAR2,
1607 p_rpa_type in ghr_pa_requests.rpa_type%TYPE,
1608 p_effective_date in date,
1609 p_person_id in per_people_f.person_id%TYPE,
1610 p_assignment_id in per_assignments_f.assignment_id%TYPE,
1611 p_position_id in hr_positions_f.position_id%TYPE,
1612 p_grade_id in number,
1613 p_location_id in hr_locations.location_id%TYPE,
1614 p_job_id in number,
1615 p_errbuf out nocopy varchar2, --\ error log
1616 p_status out nocopy varchar2, --||
1617 p_retcode out nocopy number,
1618 p_maxcheck out nocopy number--/ in conc. manager.
1619 )
1620
1621 IS
1622
1623 l_proc varchar2(72) := g_package || 'build_rpa_for_mass_awards';
1624 l_dummy varchar2(30);
1625 l_dummy_number number;
1626 l_business_group_id number;
1627
1628 l_pa_request_rec ghr_pa_requests%rowtype;
1629
1630 l_personnel_officer_name per_people_f.full_name%type;
1631 l_approval_date date;
1632 l_approving_off_work_title ghr_pa_requests.APPROVING_OFFICIAL_WORK_TITLE%type;
1633 l_1_approval_status varchar2(10);
1634
1635 l_1_prh_object_version_number ghr_pa_requests.object_version_number%type;
1636 l_1_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
1637 l_2_prh_object_version_number ghr_pa_requests.object_version_number%type;
1638 l_2_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
1639
1640 l_multiple_error_flag boolean;
1641 l_result boolean;
1642
1643 l_scd_leave varchar2(30);
1644 l_pa_remark_id ghr_pa_remarks.pa_remark_id%type;
1645 l_pa_request_id ghr_pa_requests.pa_request_id%TYPE;
1646 l_pa_notification_id ghr_pa_requests.pa_notification_id%TYPE;
1647 l_rpa_type ghr_pa_requests.rpa_type%TYPE;
1648 l_mass_action_select_flag ghr_pa_requests.mass_action_select_flag%TYPE;
1649 -- Bug#3804067 Added variable l_mass_action_comments
1650 l_mass_action_comments ghr_pa_requests.mass_action_comments%TYPE;
1651 l_object_version_number ghr_pa_requests.object_version_number%type;
1652 l_log_text varchar2(2000);
1653 l_routing_group_id ghr_pa_requests.routing_group_id%type;
1654 l_groupbox_id ghr_groupboxes.groupbox_id%type;
1655 l_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
1656 l_prh_object_version_number ghr_pa_routing_history.object_version_number%type;
1657
1658 l_duty_station_location_id hr_locations.location_id%TYPE;
1659 l_duty_station_id ghr_duty_stations_v.duty_station_id%TYPE;
1660 l_duty_station_code ghr_duty_stations_v.duty_station_code%TYPE;
1661 l_duty_station_desc ghr_duty_stations_v.duty_station_desc%TYPE;
1662 l_appropriation_code1 varchar2(30);
1663 l_appropriation_code2 varchar2(30);
1664
1665 l_bt_award_amount ghr_pa_requests.award_amount%type;
1666 l_bt_award_percentage ghr_pa_requests.award_percentage%type;
1667 l_bs_award_amount ghr_pa_requests.award_amount%type;
1668 l_bs_award_percentage ghr_pa_requests.award_percentage%type;
1669 l_bd_award_amount ghr_pa_requests.award_amount%type;
1670 l_bd_award_percentage ghr_pa_requests.award_percentage%type;
1671
1672 l_t_award_amount ghr_pa_requests.award_amount%type;
1673 l_t_award_uom ghr_pa_requests.award_uom%type;
1674 l_t_award_percentage ghr_pa_requests.award_percentage%type;
1675
1676 l_d_award_amount ghr_pa_requests.award_amount%type;
1677 l_d_award_uom ghr_pa_requests.award_uom%type;
1678 l_d_award_percentage ghr_pa_requests.award_percentage%type;
1679
1680 l_s_award_amount ghr_pa_requests.award_amount%type;
1681 l_s_award_uom ghr_pa_requests.award_uom%type;
1682 l_s_award_percentage ghr_pa_requests.award_percentage%type;
1683
1684 l_u_prh_object_version_number number;
1685 l_i_pa_routing_history_id number;
1686 l_i_prh_object_version_number number;
1687
1688 l_pa_request_extra_info_id number;
1689 l_pa_object_version_number number;
1690 l_lac1 first_lac1_record;
1691 l_lac2 first_lac2_record;
1692 l_rpa_action varchar2(40);
1693
1694 -- Bug#3648118 Added the following variable to handle NOCOPY behaviour
1695 l_first_noa_desc ghr_pa_requests.first_noa_desc%type;
1696
1697 l_pa_request_num_prefix varchar2(10) := 'MAW';
1698 l_savepoint varchar2(1);
1699
1700 groupboxerr exception;
1701 rpaerror exception;
1702 ma_rpaerror exception;
1703 ma_awdpererr exception; -- Bug 3376761 Anil
1704
1705 CURSOR c_routing_history is
1706 SELECT prh.pa_routing_history_id,
1707 prh.object_version_number
1708 FROM ghr_pa_routing_history prh
1709 WHERE prh.pa_request_id = l_pa_request_rec.pa_request_id
1710 ORDER by 1 desc;
1711
1712 CURSOR cur_maw_name IS
1713 SELECT name
1714 FROM ghr_mass_awards
1715 WHERE mass_award_id = p_mass_award_id;
1716
1717 l_maw_name ghr_mass_awards.name%type;
1718
1719 l_from_basic_pay_pa number;
1720 l_comments varchar2(2000);
1721 l_error_flag boolean;
1722 l_status varchar2(2000);
1723 l_asg_ei_data per_assignment_extra_info%rowtype;
1724 --Bug # 9329643
1725 l_employee_number varchar2(30);
1726 Begin
1727 p_errbuf := Null;
1728 p_status := Null;
1729 p_retcode := Null;
1730
1731 for cur_maw_name_rec in cur_maw_name
1732 loop
1733 l_maw_name := cur_maw_name_rec.name;
1734 end loop;
1735
1736 hr_utility.set_location('Entering ' || l_proc,5);
1737 l_savepoint := 'P';
1738 savepoint build_rpa_save_preview;
1739 hr_utility.set_location('savepoint build_rpa_save_preview ' || l_proc,5);
1740 -- The following would be null while creating rpa.
1741
1742 l_pa_request_rec.additional_info_person_id := NULL;
1743 l_pa_request_rec.additional_info_tel_number := NULL;
1744 l_pa_request_rec.Proposed_Effective_Date := NULL;
1745 l_pa_request_rec.Proposed_Effective_ASAP_flag := 'N';
1746 l_pa_request_rec.requested_by_person_id := NULL;
1747 l_pa_request_rec.requested_by_title := NULL;
1748 l_pa_request_rec.requested_date := NULL;
1749 l_pa_request_rec.authorized_by_person_id := NULL;
1750 l_pa_request_rec.authorized_by_title := NULL;
1751 l_pa_request_rec.concurrence_Date := NULL;
1752 --
1753 l_pa_request_rec.to_step_or_rate := NULL;
1754 l_pa_request_rec.to_adj_basic_pay := NULL;
1755 l_pa_request_rec.to_basic_pay := NULL;
1756 l_pa_request_rec.to_total_salary := NULL;
1757 l_pa_request_rec.to_other_pay_amount := NULL;
1758 l_pa_request_rec.to_au_overtime := NULL;
1759 l_pa_request_rec.to_auo_premium_pay_indicator := NULL;
1760 l_pa_request_rec.to_availability_pay := NULL;
1761 l_pa_request_rec.to_ap_premium_pay_indicator := NULL;
1762 l_pa_request_rec.to_retention_allowance := NULL;
1763 l_pa_request_rec.to_supervisory_differential := NULL;
1764 l_pa_request_rec.to_staffing_differential := NULL;
1765 l_pa_request_rec.to_locality_adj := NULL;
1766 l_pa_request_rec.to_retention_allow_percentage := NULL;
1767 l_pa_request_rec.to_supervisory_diff_percentage := NULL;
1768 l_pa_request_rec.to_staffing_diff_percentage := NULL;
1769
1770
1771 ---l_pa_request_rec.to_adj_basic_pay := 0;
1772 ---l_pa_request_rec.to_basic_pay := 0; --APP-37206 CPDF Edit #370.25.2
1773 ---l_pa_request_rec.to_total_salary := 0;
1774
1775 hr_utility.set_location(l_proc,10);
1776 l_pa_request_rec.mass_action_id := p_mass_award_id;
1777 l_pa_request_rec.rpa_type := p_rpa_type;
1778 l_pa_request_rec.effective_date := p_effective_date;
1779 l_pa_request_rec.person_id := p_person_id;
1780 l_pa_request_rec.employee_assignment_id := p_assignment_id;
1781 l_pa_request_rec.from_position_id := p_position_id;
1782 l_pa_request_rec.to_grade_id := p_grade_id;
1783 l_pa_request_rec.duty_station_location_id := p_location_id;
1784 l_pa_request_rec.to_job_id := p_job_id;
1785
1786 l_pa_request_rec.first_noa_id := get_noa_id(p_mass_award_id);
1787 l_pa_request_rec.noa_family_code :=
1788 ghr_pa_requests_pkg.get_noa_pm_family
1789 (p_nature_of_action_id => l_pa_request_rec.first_noa_id);
1790
1791 hr_utility.set_location(l_proc,15);
1792 get_noa_code_desc
1793 ( p_noa_id => l_pa_request_rec.first_noa_id,
1794 p_effective_date => p_effective_date,
1795 p_noa_code => l_pa_request_rec.first_noa_code,
1796 p_noa_desc => l_pa_request_rec.first_noa_desc );
1797
1798 hr_utility.set_location(l_proc,20);
1799 ghr_pa_requests_pkg.get_person_details
1800 (p_person_id => p_person_id
1801 ,p_effective_date => p_effective_date
1802 ,p_national_identifier => l_pa_request_rec.employee_national_identifier
1803 ,p_date_of_birth => l_pa_request_rec.employee_date_of_birth
1804 ,p_last_name => l_pa_request_rec.employee_last_name
1805 ,p_first_name => l_pa_request_rec.employee_first_name
1806 ,p_middle_names => l_pa_request_rec.employee_middle_names
1807 );
1808
1809 --Bug # 9329643
1810
1811 If p_person_id is not null then
1812 l_employee_number := ghr_pa_requests_pkg2.get_employee_number
1813 (p_person_id => p_person_id,
1814 p_effective_date => p_effective_date);
1815 End if;
1816
1817 hr_utility.set_location(l_proc,25);
1818 ghr_api.sf52_from_data_elements
1819 (p_person_id => p_person_id
1820 ,p_assignment_id => l_pa_request_rec.employee_assignment_id
1821 ,p_effective_date => p_effective_date
1822 ,p_altered_pa_request_id => null
1823 ,p_noa_id_corrected => null
1824 ,p_pa_history_id => null
1825 ,p_position_title => l_pa_request_rec.from_position_title
1826 ,p_position_number => l_pa_request_rec.from_position_number
1827 ,p_position_seq_no => l_pa_request_rec.from_position_seq_no
1828 ,p_pay_plan => l_pa_request_rec.from_pay_plan
1829 ,p_job_id => l_pa_request_rec.to_job_id
1830 ,p_occ_code => l_pa_request_rec.from_occ_code
1831 ,p_grade_id => l_pa_request_rec.to_grade_id
1832 ,p_grade_or_level => l_pa_request_rec.from_grade_or_level
1833 ,p_step_or_rate => l_pa_request_rec.from_step_or_rate
1834 ,p_total_salary => l_pa_request_rec.from_total_salary
1835 ,p_pay_basis => l_pa_request_rec.from_pay_basis
1836 -- FWFA Changes Bug#4444609
1837 ,p_pay_table_identifier => l_pa_request_rec.from_pay_table_identifier
1838 -- FWFA Changes
1839 ,p_basic_pay => l_pa_request_rec.from_basic_pay
1840 ,p_locality_adj => l_pa_request_rec.from_locality_adj
1841 ,p_adj_basic_pay => l_pa_request_rec.from_adj_basic_pay
1842 ,p_other_pay => l_pa_request_rec.from_other_pay_amount
1843 ----Bug 2348413
1844 ,p_au_overtime => l_dummy_number
1845 ,p_auo_premium_pay_indicator => l_dummy
1846 ,p_availability_pay => l_dummy_number
1847 ,p_ap_premium_pay_indicator => l_dummy
1848 ,p_retention_allowance => l_dummy_number
1849 ,p_retention_allow_percentage => l_dummy_number
1850 ,p_supervisory_differential => l_dummy_number
1851 ,p_supervisory_diff_percentage => l_dummy_number
1852 ,p_staffing_differential => l_dummy_number
1853 ,p_staffing_diff_percentage => l_dummy_number
1854 ----Bug 2348413
1855 ,p_organization_id => l_pa_request_rec.to_organization_id
1856 ,p_position_org_line1 => l_pa_request_rec.from_position_org_line1
1857 ,p_position_org_line2 => l_pa_request_rec.from_position_org_line2
1858 ,p_position_org_line3 => l_pa_request_rec.from_position_org_line3
1859 ,p_position_org_line4 => l_pa_request_rec.from_position_org_line4
1860 ,p_position_org_line5 => l_pa_request_rec.from_position_org_line5
1861 ,p_position_org_line6 => l_pa_request_rec.from_position_org_line6
1862 ,p_position_id => l_pa_request_rec.from_position_id
1863 ,p_duty_station_location_id => l_duty_station_location_id
1864 -- FWFA Changes Bug#4444609
1865 ,p_pay_rate_determinant => l_dummy
1866 -- FWFA Changes
1867 ,p_work_schedule => l_pa_request_rec.work_schedule
1868 );
1869
1870 hr_utility.set_location(l_proc,30);
1871 hr_utility.set_location('Bef Get Aw Det'||l_pa_request_rec.award_amount ,31);
1872 ----- Get Award details from the template and also extra information
1873 get_award_details
1874 ( p_mass_award_id => p_mass_award_id,
1875 p_rpa_type => 'TA',
1876 p_effective_date => p_effective_date,
1877 p_person_id => null,
1878 p_award_amount => l_t_award_amount,
1879 p_award_uom => l_t_award_uom,
1880 p_award_percentage => l_t_award_percentage,
1881 p_award_agency => l_dummy,
1882 p_award_type => l_dummy,
1883 p_group_award => l_dummy,
1884 p_tangible_benefit_dollars => l_dummy,
1885 p_date_award_earned => l_dummy,
1886 p_appropriation_code => l_dummy);
1887
1888 l_pa_request_rec.award_amount := l_t_award_amount;
1889 l_pa_request_rec.award_uom := l_t_award_uom;
1890 l_pa_request_rec.award_percentage := l_t_award_percentage;
1891 l_bt_award_amount := l_t_award_amount;
1892 l_bt_award_percentage := l_t_award_percentage;
1893 hr_utility.set_location('Aft Get Aw Det'||l_pa_request_rec.award_amount ,31);
1894 -- Begin Bug# 4748927
1895 /* l_from_basic_pay_pa :=
1896 ghr_pay_calc.convert_amount(l_pa_request_rec.from_basic_pay
1897 ,l_pa_request_rec.from_pay_basis,'PA');*/
1898 --End Bug# 4748927
1899 hr_utility.set_location(l_proc,35);
1900 l_pa_request_rec.duty_station_location_id := l_duty_station_location_id;
1901
1902 ghr_pa_requests_pkg.get_SF52_loc_ddf_details
1903 (p_location_id => l_duty_station_location_id
1904 ,p_duty_station_id => l_pa_request_rec.duty_station_id);
1905
1906 ghr_pa_requests_pkg.get_duty_station_details
1907 (p_duty_station_id => l_pa_request_rec.duty_station_id
1908 ,p_effective_date => l_pa_request_rec.effective_date
1909 ,p_duty_station_code => l_pa_request_rec.duty_station_code
1910 ,p_duty_station_desc => l_pa_request_rec.duty_station_desc);
1911 -- Begin Bug# 4748927
1912 if l_pa_request_rec.award_percentage is not null then
1913 l_pa_request_rec.award_amount :=
1914 ROUND(l_pa_request_rec.award_salary *
1915 NVL(l_pa_request_rec.award_percentage,0) / 100 , 0);
1916
1917 -- Bug# 4748927 End
1918 end if;
1919
1920 l_pa_request_rec.to_position_id := l_pa_request_rec.from_position_id;
1921 l_pa_request_rec.to_position_title := l_pa_request_rec.from_position_title;
1922 l_pa_request_rec.to_position_number := l_pa_request_rec.from_position_number;
1923 l_pa_request_rec.to_position_seq_no := l_pa_request_rec.from_position_seq_no;
1924 l_pa_request_rec.to_position_org_line1 := l_pa_request_rec.from_position_org_line1;
1925 l_pa_request_rec.to_position_org_line2 := l_pa_request_rec.from_position_org_line2;
1926 l_pa_request_rec.to_position_org_line3 := l_pa_request_rec.from_position_org_line3;
1927 l_pa_request_rec.to_position_org_line4 := l_pa_request_rec.from_position_org_line4;
1928 l_pa_request_rec.to_position_org_line5 := l_pa_request_rec.from_position_org_line5;
1929 l_pa_request_rec.to_position_org_line6 := l_pa_request_rec.from_position_org_line6;
1930
1931 hr_utility.set_location(l_proc,40);
1932 ghr_pa_requests_pkg.get_sf52_pos_ddf_details
1933 (p_position_id => p_position_id
1934 ,p_date_Effective => l_pa_request_rec.effective_date
1935 ,p_flsa_category => l_pa_request_rec.flsa_category
1936 ,p_bargaining_unit_status => l_pa_request_rec.bargaining_unit_status
1937 ,p_work_schedule => l_dummy
1938 ,p_functional_class => l_pa_request_rec.functional_class
1939 ,p_supervisory_status => l_pa_request_rec.supervisory_status
1940 ,p_position_occupied => l_pa_request_rec.position_occupied
1941 ,p_appropriation_code1 => l_pa_request_rec.appropriation_code1
1942 ,p_appropriation_code2 => l_pa_request_rec.appropriation_code2
1943 ,p_personnel_office_id => l_pa_request_rec.personnel_office_id
1944 ,p_office_symbol => l_pa_request_rec.from_office_symbol
1945 ,p_part_time_hours => l_dummy
1946 );
1947
1948 hr_utility.set_location('POI ID ' || l_pa_request_rec.personnel_office_id ,42);
1949 hr_utility.set_location('From Pos ID ' || to_char(l_pa_request_rec.from_position_id) ,42);
1950 hr_utility.set_location('To Pos ID ' || to_char(l_pa_request_rec.to_position_id) ,42);
1951 hr_utility.set_location('Office Symbol ' || l_pa_request_rec.from_office_symbol ,42);
1952
1953 hr_utility.set_location(l_proc,45);
1954 get_business_group
1955 (p_person_id => p_person_id
1956 ,p_effective_date => p_effective_date
1957 ,p_business_group_id => l_business_group_id
1958 );
1959
1960 hr_utility.set_location(l_proc,50);
1961 l_pa_request_rec.agency_code := ghr_api.get_position_agency_code_pos
1962 (p_position_id => p_position_id
1963 ,p_business_group_id => l_business_group_id
1964 ,p_effective_date => p_effective_date
1965 );
1966 hr_utility.set_location('Agency Code ' || l_pa_request_rec.agency_code ,52);
1967
1968 l_pa_request_rec.employee_dept_or_agency := ghr_pa_requests_pkg.get_lookup_meaning
1969 (800
1970 ,'GHR_US_AGENCY_CODE'
1971 ,l_pa_request_rec.agency_code
1972 );
1973
1974 hr_utility.set_location('employee_dept_or_agency ' || l_pa_request_rec.employee_dept_or_agency ,52);
1975 hr_utility.set_location('Noa_family_code value ' || l_pa_request_rec.noa_family_code,52);
1976 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,52);
1977 hr_utility.set_location('PA OVN ' || to_char(l_pa_request_rec.object_version_number),52);
1978 hr_utility.set_location('l_pa_request_rec.award_amount '||l_pa_request_rec.award_amount ,52);
1979
1980 -- Bug 5041967 Sundar
1981 ghr_history_fetch.fetch_asgei(
1982 p_assignment_id => l_pa_request_rec.employee_assignment_id,
1983 p_information_type => 'GHR_US_ASG_SF52',
1984 p_date_effective => p_effective_date,
1985 p_asg_ei_data => l_asg_ei_data);
1986 ghr_pay_calc.award_amount_calc (
1987 p_position_id => l_pa_request_rec.to_position_id
1988 ,p_pay_plan => l_pa_request_rec.from_pay_plan
1989 ,p_award_percentage => l_pa_request_rec.award_percentage
1990 ,p_user_table_id => l_pa_request_rec.from_pay_table_identifier
1991 ,p_grade_or_level => l_pa_request_rec.from_grade_or_level
1992 ,p_effective_date => l_pa_request_rec.effective_date
1993 ,p_basic_pay => l_pa_request_rec.from_basic_pay
1994 ,p_adj_basic_pay => l_pa_request_rec.from_adj_basic_pay
1995 ,p_duty_station_id => l_pa_request_rec.duty_station_id
1996 ,p_prd => l_asg_ei_data.aei_information6
1997 ,p_pay_basis => l_pa_request_rec.from_pay_basis
1998 ,p_person_id => l_pa_request_rec.person_id
1999 -- ,p_award_amount => l_pa_request_rec.award_amount Bug 5041967
2000 ,p_award_amount => l_dummy
2001 ,p_award_salary => l_pa_request_rec.award_salary
2002 );
2003 -- End Bug 5041967 Sundar
2004
2005 -----------Created marpa_process procedure Call
2006 begin
2007 hr_utility.set_location('Bef MAR PA '||l_pa_request_rec.award_amount ,32);
2008 marpa_process
2009 (
2010 p_mass_award_id => p_mass_award_id
2011 ,p_action_type => p_action_type
2012 ,p_rpa_type => p_rpa_type
2013 ,p_effective_date => p_effective_date
2014 ,p_person_id => p_person_id
2015 ,p_pa_request_rec => l_pa_request_rec
2016 ,p_log_text => l_log_text
2017 ,p_maxcheck => p_maxcheck
2018 );
2019
2020 hr_utility.set_location('After MAR PA '||l_pa_request_rec.award_amount ,33);
2021 commit;
2022 hr_utility.set_location('commit to have eligibility build_rpa_save_preview ' ,162);
2023 l_savepoint := 'P';
2024 savepoint build_rpa_save_preview;
2025 hr_utility.set_location('savepoint build_rpa_save_preview - After Preview ' ,162);
2026 exception when ma_rpaerror then raise;
2027 when others then raise ma_rpaerror;
2028 end;
2029
2030 -------commit;
2031 ---------------------Starting Final
2032 ---Recent Modification - Group box id must be commited in order to get call work flow
2033
2034 hr_utility.set_location(l_proc,163);
2035 -- Employee Data
2036
2037 get_pa_request_id_ovn
2038 ( p_mass_award_id => p_mass_award_id,
2039 p_effective_date => p_effective_date,
2040 p_person_id => p_person_id,
2041 p_pa_request_id => l_pa_request_id,
2042 p_pa_notification_id => l_pa_notification_id,
2043 p_rpa_type => l_rpa_type,
2044 p_mass_action_sel_flag => l_mass_action_select_flag,
2045 --Bug#3804067 Added mass action comments
2046 p_mass_action_comments => l_mass_action_comments,
2047 p_object_version_number => l_object_version_number);
2048
2049 ------ Modified in 115.9
2050 l_pa_request_rec.mass_action_comments := l_mass_action_comments;
2051 l_pa_request_rec.object_version_number := l_object_version_number;
2052 l_pa_request_rec.pa_request_id := l_pa_request_id;
2053 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,165);
2054 hr_utility.set_location('PA OVN ' || to_char(l_pa_request_rec.object_version_number),165);
2055
2056 -------
2057 if l_rpa_type is null then l_rpa_type := 'A'; end if;
2058
2059 IF p_action_type = 'FINAL' then
2060 IF l_pa_notification_id is null and l_rpa_type = 'A' and l_mass_action_select_flag <> 'N' THEN
2061
2062 if l_1_pa_routing_history_id is null then
2063 l_1_pa_routing_history_id := l_i_pa_routing_history_id;
2064 end if;
2065 if l_1_prh_object_version_number is null then
2066 l_1_prh_object_version_number := l_i_prh_object_version_number;
2067 end if;
2068
2069 begin
2070 hr_utility.set_location('Getting Group Box ' || l_proc,170);
2071
2072 ghr_mass_actions_pkg.get_personnel_off_groupbox
2073 (p_position_id => p_position_id
2074 ,p_effective_date => p_effective_date
2075 ,p_groupbox_id => l_groupbox_id
2076 ,p_routing_group_id => l_routing_group_id );
2077 hr_utility.set_location('Getting Group Box Success ' || l_proc,170);
2078 hr_utility.set_location('Routing Group Id ' || to_char(l_routing_group_id),170);
2079 hr_utility.set_location('Group Box Id' || to_char(l_groupbox_id),170);
2080
2081 exception
2082 WHEN OTHERS THEN
2083 hr_utility.set_location('Getting Group Box Failure ' || l_proc,170);
2084 l_log_text := 'Error in POI groupbox ' ||' Sql error : '||sqlerrm(sqlcode);
2085
2086 -- Call ghr_par_upd.upd here
2087
2088 raise groupboxerr;
2089 end;
2090
2091 -- Call ghr_prh_upd.upd here
2092 for routing_history_id in c_routing_history loop
2093 l_pa_routing_history_id := routing_history_id.pa_routing_history_id;
2094 l_prh_object_version_number := routing_history_id.object_version_number;
2095 exit;
2096 end loop;
2097
2098 hr_utility.set_location('Updating prh ' || l_proc,175);
2099 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,175);
2100 hr_utility.set_location('PA OVN ' || to_char(l_pa_request_rec.object_version_number),175);
2101 hr_utility.set_location('RHVN ' || to_char(l_prh_object_version_number),176);
2102 hr_utility.set_location('RHID ' || to_char(l_pa_routing_history_id),177);
2103
2104 ghr_prh_upd.upd(
2105 p_pa_routing_history_id => l_pa_routing_history_id,
2106 p_groupbox_id => l_groupbox_id,
2107 p_object_version_number => l_prh_object_version_number);
2108
2109 hr_utility.set_location('Updating par ' || l_proc,180);
2110
2111 -- Call ghr_par_upd.upd here
2112 ghr_par_upd.upd(
2113 p_pa_request_id => l_pa_request_rec.pa_request_id,
2114 p_routing_group_id => l_routing_group_id,
2115 p_object_version_number => l_pa_request_rec.object_version_number);
2116
2117 -- Bug 3376761
2118 -- check to see if the award amount is within 25% of basic pay
2119 -- other wise raise award percentage exception.
2120
2121 l_from_basic_pay_pa :=
2122 ghr_pay_calc.convert_amount(l_pa_request_rec.from_basic_pay
2123 ,l_pa_request_rec.from_pay_basis,'PA');
2124
2125
2126 commit;
2127 hr_utility.set_location('commit(GB) build_rpa_save_preview ' || l_proc,52);
2128 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,52);
2129 hr_utility.set_location('PA OVN ' || to_char(l_pa_request_rec.object_version_number),52);
2130
2131 ---- Recent Modification End;
2132
2133 l_savepoint := 'F';
2134 savepoint build_rpa_save_final;
2135 hr_utility.set_location('savepoint build_rpa_save_final ' || l_proc,52);
2136 hr_utility.set_location(l_proc,52);
2137 hr_utility.set_location('Bef Check Award Amt'||l_pa_request_rec.award_amount ,34);
2138 /*
2139 IF l_pa_request_rec.award_amount > ROUND(l_from_basic_pay_pa * 0.25 , 0) THEN
2140 RAISE ma_awdpererr;
2141 END IF ;
2142 */
2143 --Pradeep Commented the above and added the below for bug 3934195
2144 get_award_details
2145 ( p_mass_award_id => p_mass_award_id,
2146 p_rpa_type => l_rpa_type,
2147 p_effective_date => p_effective_date,
2148 p_person_id => null,
2149 p_award_amount => l_d_award_amount,
2150 p_award_uom => l_d_award_uom,
2151 p_award_percentage => l_d_award_percentage,
2152 p_award_agency => l_dummy,
2153 p_award_type => l_dummy,
2154 p_group_award => l_dummy,
2155 p_tangible_benefit_dollars => l_dummy,
2156 p_date_award_earned => l_dummy,
2157 p_appropriation_code => l_dummy);
2158
2159 l_error_flag := FALSE;
2160 check_award_amount (p_noa_code => l_pa_request_rec.first_noa_code,
2161 p_effective_date => l_pa_request_rec.effective_date,
2162 p_award_amount => l_d_award_amount,
2163 p_from_pay_plan => l_pa_request_rec.from_pay_plan,
2164 p_from_basic_pay_pa => l_from_basic_pay_pa,
2165 p_to_position_id => l_pa_request_rec.to_position_id,
2166 p_comments => l_comments,
2167 p_error_flg => l_error_flag
2168 );
2169 IF l_error_flag THEN
2170 p_maxcheck := 1;
2171 END IF;
2172
2173 IF ( l_error_flag
2174 AND NVL(l_mass_action_select_flag,'Y') <> 'N' ) THEN
2175 l_mass_action_select_flag := 'N';
2176 RAISE ma_awdpererr;
2177 END IF ;
2178
2179 -- End of Bug 3376761
2180
2181 get_award_lac
2182 (p_mass_award_id => p_mass_award_id,
2183 p_rpa_type => 'TA',
2184 p_effective_date => p_effective_date,
2185 p_first_lac1_record => l_lac1,
2186 p_first_lac2_record => l_lac2
2187 );
2188 l_pa_request_rec.first_action_la_code1 := l_lac1.first_action_la_code1;
2189 l_pa_request_rec.first_action_la_desc1 := l_lac1.first_action_la_desc1;
2190 l_pa_request_rec.first_lac1_information1 := l_lac1.first_lac1_information1;
2191 l_pa_request_rec.first_lac1_information2 := l_lac1.first_lac1_information2;
2192 l_pa_request_rec.first_lac1_information3 := l_lac1.first_lac1_information3;
2193 l_pa_request_rec.first_lac1_information4 := l_lac1.first_lac1_information4;
2194 l_pa_request_rec.first_lac1_information5 := l_lac1.first_lac1_information5;
2195
2196 l_pa_request_rec.first_action_la_code2 := l_lac2.first_action_la_code2;
2197 l_pa_request_rec.first_action_la_desc2 := l_lac2.first_action_la_desc2;
2198 l_pa_request_rec.first_lac2_information1 := l_lac2.first_lac2_information1;
2199 l_pa_request_rec.first_lac2_information2 := l_lac2.first_lac2_information2;
2200 l_pa_request_rec.first_lac2_information3 := l_lac2.first_lac2_information3;
2201 l_pa_request_rec.first_lac2_information4 := l_lac2.first_lac2_information4;
2202 l_pa_request_rec.first_lac2_information5 := l_lac2.first_lac2_information5;
2203
2204 -- First NOA Code has insertion values. (For only 879 and 885 NOAC's)
2205
2206 ghr_mass_actions_pkg.replace_insertion_values
2207 (p_desc => l_pa_request_rec.first_noa_desc,
2208 p_information1 => l_pa_request_rec.first_noa_information1,
2209 -- Bug#3648118 Passed the local variable as OUT parameter
2210 -- because passing l_pa_request_rec.first_noa_desc as OUT parameter
2211 -- is making the variable as NULL.
2212 p_desc_out => l_first_noa_desc
2213 );
2214
2215 l_pa_request_rec.first_noa_desc := l_first_noa_desc;
2216 -- Bug#3648118 Changes completed.
2217 hr_utility.set_location(l_proc,55);
2218 ghr_pa_requests_pkg.get_SF52_person_ddf_details
2219 (p_person_id => l_pa_request_rec.person_id,
2220 p_date_effective => l_pa_request_rec.effective_date,
2221 p_citizenship => l_pa_request_rec.citizenship,
2222 p_veterans_preference => l_pa_request_rec.veterans_preference,
2223 p_veterans_pref_for_rif => l_pa_request_rec.veterans_pref_for_rif,
2224 p_veterans_status => l_pa_request_rec.veterans_status,
2225 p_scd_leave => l_scd_leave
2226 );
2227
2228 -- populate service comp date
2229 hr_utility.set_location(l_proc,60);
2230 l_pa_request_rec.service_comp_date := fnd_date.canonical_to_date(l_scd_leave);
2231
2232 -- get education details
2233 hr_utility.set_location(l_proc,70);
2234 ghr_api.return_education_Details
2235 (p_person_id => l_pa_request_rec.person_id,
2236 p_effective_date => l_pa_request_rec.effective_date,
2237 p_education_level => l_pa_request_rec.education_level,
2238 p_academic_discipline => l_pa_request_rec.academic_discipline,
2239 p_year_degree_attained => l_pa_request_rec.year_degree_attained
2240 );
2241
2242 hr_utility.set_location(l_proc,80);
2243 ghr_pa_requests_pkg.get_SF52_asg_ddf_details
2244 (p_assignment_id => l_pa_request_rec.employee_assignment_id
2245 ,p_date_effective => l_pa_request_rec.effective_date
2246 ,p_tenure => l_pa_request_rec.tenure
2247 ,p_annuitant_indicator => l_pa_request_rec.annuitant_indicator
2248 ,p_pay_rate_determinant => l_dummy
2249 ,p_work_schedule => l_pa_request_rec.work_schedule
2250 ,p_part_time_hours => l_pa_request_rec.part_time_hours
2251 );
2252
2253 -- Annuitant_indicator
2254 l_pa_request_rec.annuitant_indicator_desc := ghr_pa_requests_pkg.get_lookup_meaning
2255 (800
2256 ,'GHR_US_ANNUITANT_INDICATOR'
2257 ,l_pa_request_rec.annuitant_indicator
2258 );
2259
2260 --WORK_SCHEDULE
2261 l_pa_request_rec.work_schedule_desc := ghr_pa_requests_pkg.get_lookup_meaning
2262 (800
2263 ,'GHR_US_WORK_SCHEDULE'
2264 ,l_pa_request_rec.work_schedule
2265 );
2266
2267 -- get fegli,retirement_plan
2268
2269 hr_utility.set_location(l_proc,90);
2270 ghr_api.retrieve_element_entry_value
2271 (p_element_name => 'FEGLI'
2272 ,p_input_value_name => 'FEGLI'
2273 ,p_assignment_id => l_pa_request_rec.employee_assignment_id
2274 ,p_effective_date => l_pa_request_rec.effective_date
2275 ,p_value => l_pa_request_rec.fegli
2276 ,p_multiple_error_flag => l_multiple_error_flag
2277 );
2278
2279 l_pa_request_rec.fegli_desc := ghr_pa_requests_pkg.get_lookup_meaning
2280 (800
2281 ,'GHR_US_FEGLI'
2282 ,l_pa_request_rec.fegli
2283 );
2284
2285
2286 --retirement_plan
2287 hr_utility.set_location(l_proc,100);
2288 ghr_api.retrieve_element_entry_value
2289 (p_element_name => 'Retirement Plan'
2290 ,p_input_value_name => 'Plan'
2291 ,p_assignment_id => l_pa_request_rec.employee_assignment_id
2292 ,p_effective_date => l_pa_request_rec.effective_date
2293 ,p_value => l_pa_request_rec.retirement_plan
2294 ,p_multiple_error_flag => l_multiple_error_flag
2295 );
2296
2297 l_pa_request_rec.retirement_plan_desc := ghr_pa_requests_pkg.get_lookup_meaning
2298 (800
2299 ,'GHR_US_RETIREMENT_PLAN'
2300 ,l_pa_request_rec.retirement_plan
2301 );
2302
2303 -- Descriptions for the codes passed in
2304 hr_utility.set_location(l_proc,110);
2305 ghr_mass_actions_pkg.get_personnel_officer_name
2306 (p_personnel_office_id => l_pa_request_rec.personnel_office_id,
2307 p_person_full_name => l_personnel_officer_name,
2308 p_approving_off_work_title => l_approving_off_work_title);
2309
2310 l_pa_request_rec.approving_official_full_name := l_personnel_officer_name;
2311 l_pa_request_rec.approving_official_work_title := l_approving_off_work_title;
2312
2313 l_approval_date := sysdate;
2314 l_1_approval_status := 'APPROVE';
2315 l_pa_request_rec.request_number := l_pa_request_num_prefix ||
2316 to_char(l_pa_request_rec.pa_request_id);
2317
2318
2319 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,110);
2320 hr_utility.set_location('PA OVN ' || to_char(l_pa_request_rec.object_version_number),110);
2321 -----------Created marpa_process procedure Call
2322 ---- To print NPA correctly ..bug..2356373.
2323
2324 l_pa_request_rec.sf50_approving_ofcl_full_name := l_personnel_officer_name;
2325 l_pa_request_rec.sf50_approval_date := sysdate;
2326 l_pa_request_rec.sf50_approving_ofcl_work_title := l_approving_off_work_title;
2327
2328 begin
2329 marpa_process
2330 (
2331 p_mass_award_id => p_mass_award_id
2332 ,p_action_type => p_action_type
2333 ,p_rpa_type => p_rpa_type
2334 ,p_effective_date => p_effective_date
2335 ,p_person_id => p_person_id
2336 ,p_pa_request_rec => l_pa_request_rec
2337 ,p_log_text => l_log_text
2338 ,p_maxcheck => p_maxcheck
2339 );
2340
2341 exception when ma_rpaerror then raise;
2342 when others then raise ma_rpaerror;
2343 end;
2344
2345 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,165);
2346 hr_utility.set_location('PA OVN ' || to_char(l_pa_request_rec.object_version_number),165);
2347
2348 hr_utility.set_location('Creating Remarks ' || l_proc,165);
2349 create_remarks
2350 (p_mass_award_id => p_mass_award_id
2351 ,p_rpa_type => 'TA'
2352 ,p_effective_date => p_effective_date
2353 ,p_pa_request_id => l_pa_request_rec.pa_request_id
2354 );
2355 hr_utility.set_location('Creating Remarks Over ' || l_proc,165);
2356
2357
2358 if p_effective_date > sysdate then
2359 l_rpa_action := 'FUTURE_ACTION';
2360 else
2361 l_rpa_action := 'UPDATE_HR';
2362 end if;
2363
2364 hr_utility.set_location('Action before last ghr_par_upd.upd ' || l_rpa_action || ' ' || l_proc,175);
2365
2366 ---Bug 2356373
2367 ghr_par_upd.upd
2368 (p_pa_request_id => l_pa_request_rec.pa_request_id,
2369 p_object_version_number => l_pa_request_rec.object_version_number,
2370 p_personnel_office_id => l_pa_request_rec.personnel_office_id,
2371 p_employee_dept_or_agency => l_pa_request_rec.employee_dept_or_agency,
2372 p_to_office_symbol => l_pa_request_rec.to_office_symbol,
2373 p_sf50_approving_ofcl_full_nam => l_personnel_officer_name,
2374 p_sf50_approval_date => sysdate ,
2375 p_sf50_approving_ofcl_work_tit => l_approving_off_work_title
2376 );
2377 ---Bug 2356373 End
2378
2379 hr_utility.set_location('Action after last ghr_par_upd.upd ' || l_rpa_action || ' ' || l_proc,175);
2380
2381 hr_utility.set_location('Action before ' || l_rpa_action || ' ' || l_proc,185);
2382 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,185);
2383 hr_utility.set_location('PA OVN ' || to_char(l_pa_request_rec.object_version_number),185);
2384
2385 hr_utility.set_location('employee_dept_or_agency ' || l_pa_request_rec.employee_dept_or_agency ,185);
2386 hr_utility.set_location('POI ID ' || l_pa_request_rec.personnel_office_id ,185);
2387 ------
2388
2389 ----Update Sf52 for Personnel Action
2390 begin
2391 ghr_sf52_api.update_sf52
2392 (
2393 p_pa_request_id => l_pa_request_rec.pa_request_id,
2394 p_par_object_version_number => l_pa_request_rec.object_version_number,
2395 ----Bug 2348413
2396 p_effective_date => l_pa_request_rec.effective_date,
2397 p_employee_assignment_id => l_pa_request_rec.employee_assignment_id,
2398 p_noa_family_code => l_pa_request_rec.noa_family_code,
2399 ----Bug 2348413
2400 p_routing_group_id => l_routing_group_id,
2401 p_u_action_taken => l_rpa_action,
2402 p_u_prh_object_version_number => l_u_prh_object_version_number ,
2403 p_first_noa_id => l_pa_request_rec.first_noa_id, -- Bug#2740882
2404 p_i_pa_routing_history_id => l_i_pa_routing_history_id,
2405 p_i_prh_object_version_number => l_i_prh_object_version_number);
2406 exception
2407 WHEN OTHERS THEN
2408 hr_utility.set_location(sqlerrm(sqlcode),1);
2409 hr_utility.set_location('Update sf52 final Failure ' || l_proc,190);
2410 l_log_text := 'Error in Update sf52 final ' ||' Sql error : '||sqlerrm(sqlcode);
2411 raise rpaerror;
2412 end;
2413
2414 hr_utility.set_location('Action after ' || l_rpa_action || ' ' || l_proc,200);
2415 p_status := 'SUCCESS';
2416 p_retcode := 0;
2417 l_log_text := 'Last Name: '|| l_pa_request_rec.employee_last_name ||
2418 'Emp No: '|| l_employee_number ||
2419 'Mass Award:'|| to_char(p_mass_award_id)||
2420 'SF52 Successfully completed';
2421 p_errbuf := l_log_text;
2422
2423 ghr_wgi_pkg.create_ghr_errorlog(
2424 p_program_name => g_log_name,
2425 p_message_name => substr(l_maw_name || '-' || 'Success',1,30),
2426 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' || l_log_text,1,2000),
2427 p_log_date => sysdate);
2428
2429
2430
2431 ELSE
2432 IF l_rpa_type = 'M' then
2433 p_status := 'MANUAL';
2434 p_retcode := 0;
2435 hr_utility.set_location('Manual rpa_type ' || l_proc,200);
2436 ELSIF l_mass_action_select_flag = 'N'
2437 and l_status = 'DESELECTED PRG:' THEN
2438 hr_utility.set_location('Deselected by programatically' || l_proc,200);
2439 p_errbuf := 'DESELECTED PRG:';
2440 p_retcode := 0;
2441 ELSIF l_mass_action_select_flag = 'N' then
2442 hr_utility.set_location('Deselected by user ' || l_proc,200);
2443 p_status := 'DESELECTED';
2444 p_retcode := 0;
2445 ELSE
2446 hr_utility.set_location('Already Processed ' || l_proc,200);
2447 p_status := 'PROCESSED';
2448 p_retcode := 0;
2449 END IF;
2450 END IF; ----- For l_pa_notification_id is null and rpa_type = 'A'
2451 END IF; ---- For p_action = 'FINAL'
2452
2453 Exception
2454 When groupboxerr then
2455 rollback to build_rpa_save_preview;
2456 hr_utility.set_location('rollback grp build_rpa_save_preview ' || l_proc,1);
2457
2458 -- Call ghr_par_upd.upd here
2459 begin
2460
2461 get_pa_request_id_ovn
2462 ( p_mass_award_id => p_mass_award_id,
2463 p_effective_date => p_effective_date,
2464 p_person_id => p_person_id,
2465 p_pa_request_id => l_pa_request_id,
2466 p_pa_notification_id => l_pa_notification_id,
2467 p_rpa_type => l_rpa_type,
2468 p_mass_action_sel_flag => l_mass_action_select_flag,
2469 --Bug#3804067 Added mass action comments
2470 p_mass_action_comments => l_mass_action_comments,
2471 p_object_version_number => l_object_version_number);
2472
2473 ghr_par_upd.upd(
2474 p_pa_request_id => l_pa_request_id,
2475 p_mass_action_eligible_flag => 'Y',
2476 p_mass_action_select_flag => 'N',
2477 p_mass_action_comments => 'PRG: Programatically Deselected',
2478 p_object_version_number => l_object_version_number);
2479 exception when others then
2480 l_log_text := substr(l_log_text || ' , Failed in Deselecting, ' ||' Sql error : '||sqlerrm(sqlcode),1,2000);
2481 end;
2482
2483 hr_utility.set_location('Error occured in group box ' || l_proc , 1);
2484 IF l_log_text is NULL THEN
2485 l_log_text := 'Error while creating / Updating the PA Request Rec.';
2486 END IF;
2487
2488 p_retcode := 1;
2489 p_status := 'GROUPBOX';
2490 p_errbuf := substr(l_log_text || 'Details in GHR_PROCESS_LOG',1,2000);
2491
2492 hr_utility.set_location('before creating entry in log file',2);
2493 -- Bug#3718167 Added SSN in the log text below
2494 --Bug # 9329643 Modified SSN to Emp No
2495 l_log_text := substr(',( ' || l_pa_request_rec.employee_last_name || ', ' ||
2496 l_pa_request_rec.employee_first_name ||' '||l_pa_request_rec.employee_middle_names||
2497 '; Emp No: '||l_employee_number||
2498 ') ' || l_log_text,1,2000);
2499
2500 ghr_wgi_pkg.create_ghr_errorlog(
2501 p_program_name => g_log_name,
2502 p_message_name => substr(l_maw_name || '-' || 'GB Err',1,30),
2503 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' ||l_log_text,1,2000),
2504 p_log_date => sysdate);
2505
2506 hr_utility.set_location('Groupbox Error ',2);
2507 hr_utility.set_location('created entry in log file',2);
2508 commit;
2509
2510 When rpaerror then
2511
2512 if l_savepoint = 'F' THEN
2513 rollback to build_rpa_save_final;
2514 hr_utility.set_location('rollback rpa build_rpa_save_final ' || l_proc,1);
2515 else
2516 rollback to build_rpa_save_preview;
2517 hr_utility.set_location('rollback rpa build_rpa_save_preview ' || l_proc,1);
2518 end if;
2519
2520 hr_utility.set_location('Error occured Final Sf52 ' || l_proc , 1);
2521 IF l_log_text is NULL THEN
2522 l_log_text := 'Error while creating / Updating the PA Request Rec.';
2523 END IF;
2524
2525 p_retcode := 1;
2526 p_status := 'FAILURE';
2527 p_errbuf := substr(l_log_text || 'Details in GHR_PROCESS_LOG',1,2000);
2528
2529 hr_utility.set_location('before creating entry in log file',2);
2530 -- Bug#3718167 Added SSN in the following log text
2531 --Bug # 9329643 Modified SSN to Emp No
2532 l_log_text := substr(',( ' || l_pa_request_rec.employee_last_name || ', ' ||
2533 l_pa_request_rec.employee_first_name ||' '||l_pa_request_rec.employee_middle_names||
2534 '; Emp No: '||l_employee_number||
2535 ') ' || l_log_text ,1,2000);
2536 ghr_wgi_pkg.create_ghr_errorlog(
2537 p_program_name => g_log_name,
2538 p_message_name => substr(l_maw_name || '-' || 'RPA Err',1,30),
2539 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' ||l_log_text,1,2000),
2540 p_log_date => sysdate);
2541
2542 hr_utility.set_location('FINAL SF52 Error ',2);
2543 hr_utility.set_location('created entry in log file',2);
2544
2545 hr_utility.set_location('Calling workflow begin entry ',3);
2546 mass_awards_error_handling
2547 ( p_pa_request_id => l_pa_request_rec.pa_request_id,
2548 p_object_version_number => l_pa_request_rec.object_version_number,
2549 p_error => l_log_text,
2550 p_result => l_result);
2551 if l_result then
2552 l_log_text := 'Error while routing to group box in call workflow ';
2553 p_status := 'OTHER';
2554 p_retcode := 1;
2555 hr_utility.set_location('before creating entry in log file',4);
2556 -- Bug#3718167 Added SSN in the following log text
2557 --Bug # 9329643 Modified SSN to Emp No
2558 l_log_text := substr( ',( ' || l_pa_request_rec.employee_last_name || ', ' ||
2559 l_pa_request_rec.employee_first_name ||' '||l_pa_request_rec.employee_middle_names||
2560 '; Emp No: '||l_employee_number||
2561 ') ' || l_log_text,1,2000);
2562 ghr_wgi_pkg.create_ghr_errorlog(
2563 p_program_name => g_log_name,
2564 p_message_name => substr(l_maw_name || '-' || 'RPA WF',1,30),
2565 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' || l_log_text,1,2000),
2566 p_log_date => sysdate);
2567
2568 hr_utility.set_location('FINAL SF52 Error - Call Work flow ',4);
2569 hr_utility.set_location('created entry in log file',4);
2570 end if;
2571 hr_utility.set_location('Calling workflow end entry ',3);
2572
2573 commit;
2574
2575 When ma_rpaerror then
2576
2577 if l_savepoint = 'F' THEN
2578 rollback to build_rpa_save_final;
2579 hr_utility.set_location('rollback marpa build_rpa_save_final ' || l_proc,1);
2580 else
2581 rollback to build_rpa_save_preview;
2582 hr_utility.set_location('rollback marpa build_rpa_save_preview ' || l_proc,1);
2583 end if;
2584
2585 hr_utility.set_location('Error occured in ' || l_proc , 1);
2586 IF g_log_text is NOT NULL then
2587 l_log_text := g_log_text;
2588 END IF;
2589 IF l_log_text is NULL THEN
2590 l_log_text := 'Error while creating / Updating the PA Request Rec.';
2591 END IF;
2592
2593 p_retcode := 1;
2594 p_status := 'FAILURE';
2595 p_errbuf := substr(l_log_text || 'Details in GHR_PROCESS_LOG',1,2000);
2596
2597 hr_utility.set_location('before creating entry in log file',2);
2598 -- Bug#3718167 Added SSN in the following log text
2599 --Bug # 9329643 Modified SSN to Emp No
2600 l_log_text := substr( ',( ' || l_pa_request_rec.employee_last_name || ', ' ||
2601 l_pa_request_rec.employee_first_name ||' '||l_pa_request_rec.employee_middle_names||
2602 '; Emp No: '||l_employee_number||
2603 ') ' || l_log_text,1,2000);
2604 ghr_wgi_pkg.create_ghr_errorlog(
2605 p_program_name => g_log_name,
2606 p_message_name => substr(l_maw_name || '-' || 'RPA MA',1,30) ,
2607 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' || l_log_text,1,2000),
2608 p_log_date => sysdate);
2609
2610
2611 hr_utility.set_location('Error Others ',2);
2612 hr_utility.set_location('created entry in log file',2);
2613
2614 commit;
2615
2616 -- Bug 3376761
2617 -- If Award amount exceeds 25% of annual basic pay
2618
2619 When ma_awdpererr then
2620
2621
2622 rollback to build_rpa_save_final;
2623
2624 hr_utility.set_location('rollback marpa build_rpa_save_final ' || l_proc,1);
2625
2626 hr_utility.set_location('Error occured in ' || l_proc , 1);
2627
2628 --l_log_text := 'APP-GHR-38611 - Award Percentage cannot be greater than 25% of total annual basic pay.';
2629 l_log_text := l_comments;
2630
2631 p_retcode := 1;
2632 p_status := 'FAILURE';
2633 p_errbuf := substr(l_log_text || 'Details in GHR_PROCESS_LOG',1,2000);
2634
2635 hr_utility.set_location('before creating entry in log file',2);
2636 -- Bug#3718167 Added SSN in the following log text
2637 --Bug # 9329643 Modified SSN to Emp No
2638 l_log_text := substr(',( ' || l_pa_request_rec.employee_last_name || ', ' ||
2639 l_pa_request_rec.employee_first_name ||' '||l_pa_request_rec.employee_middle_names||
2640 '; Emp No: '||l_employee_number||
2641 ') ' || l_log_text ,1,2000);
2642
2643 ghr_wgi_pkg.create_ghr_errorlog(
2644 p_program_name => g_log_name,
2645 p_message_name => substr(l_maw_name || '-' || 'RPA MA',1,60),
2646 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' ||l_log_text,1,2000),
2647 p_log_date => sysdate);
2648
2649 hr_utility.set_location('Award percentage Error ',2);
2650 hr_utility.set_location('created entry in log file',2);
2651
2652 hr_utility.set_location('Calling workflow begin entry ',3);
2653 mass_awards_error_handling
2654 ( p_pa_request_id => l_pa_request_rec.pa_request_id,
2655 p_object_version_number => l_pa_request_rec.object_version_number,
2656 p_error => l_log_text,
2657 p_result => l_result);
2658 if l_result then
2659 l_log_text := 'Error while routing to group box in call workflow ';
2660 p_status := 'OTHER';
2661 p_retcode := 1;
2662 hr_utility.set_location('before creating entry in log file',4);
2663 -- Bug#3718167 Added SSN in the following log text
2664 --Bug # 9329643 Modified SSN to Emp No
2665 l_log_text := substr( ',( ' || l_pa_request_rec.employee_last_name || ', ' ||
2666 l_pa_request_rec.employee_first_name || ' '||l_pa_request_rec.employee_middle_names||
2667 '; Emp No: '||l_employee_number||
2668 ') ' || l_log_text,1,2000);
2669 ghr_wgi_pkg.create_ghr_errorlog(
2670 p_program_name => g_log_name,
2671 p_message_name => substr(l_maw_name || '-' || 'RPA WF',1,30),
2672 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' || l_log_text,1,2000),
2673 p_log_date => sysdate);
2674
2675 hr_utility.set_location('Award percentage Error - Call Work flow ',4);
2676 hr_utility.set_location('created entry in log file',4);
2677 end if;
2678 hr_utility.set_location('Calling workflow end entry ',3);
2679
2680 commit;
2681
2682 -- End of Bug 3376761
2683
2684 When others then
2685
2686 if l_savepoint = 'F' THEN
2687 rollback to build_rpa_save_final;
2688 hr_utility.set_location('rollback others build_rpa_save_final ' || l_proc,1);
2689 else
2690 rollback to build_rpa_save_preview;
2691 hr_utility.set_location('rollback others build_rpa_save_preview ' || l_proc,1);
2692 end if;
2693
2694 hr_utility.set_location('Error occured in ' || l_proc , 1);
2695 IF l_log_text is NULL THEN
2696 l_log_text := 'Error while creating / Updating the PA Request Rec.';
2697 END IF;
2698
2699 p_retcode := 1;
2700 p_status := 'FAILURE';
2701 p_errbuf := substr(l_log_text || 'Details in GHR_PROCESS_LOG',1,2000);
2702
2703 hr_utility.set_location('before creating entry in log file',2);
2704 -- Bug#3718167 Added SSN to the following log text
2705 --Bug # 9329643 Modified SSN to Emp No
2706 l_log_text := substr( ',( ' || l_pa_request_rec.employee_last_name || ', ' ||
2707 l_pa_request_rec.employee_first_name ||' '||l_pa_request_rec.employee_middle_names||
2708 '; Emp No: '||l_employee_number||
2709 ') ' || l_log_text,1,2000);
2710 ghr_wgi_pkg.create_ghr_errorlog(
2711 p_program_name => g_log_name,
2712 p_message_name => substr(l_maw_name || '-' || 'RPA Oth',1,30) ,
2713 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' || l_log_text,1,2000),
2714 p_log_date => sysdate);
2715
2716
2717 hr_utility.set_location('Error Others ',2);
2718 hr_utility.set_location('created entry in log file',2);
2719
2720 commit;
2721
2722 end build_rpa_for_mass_awards;
2723
2724
2725 PROCEDURE refresh_award_details
2726 (
2727 p_mass_award_id in ghr_mass_awards.mass_award_id%TYPE,
2728 p_rpa_type in ghr_pa_requests.rpa_type%TYPE,
2729 p_effective_date in date,
2730 p_person_id in per_people_f.person_id%TYPE,
2731 p_pa_request_id in ghr_pa_requests.pa_request_id%type
2732 )
2733
2734 IS
2735
2736 l_proc varchar2(72) := g_package || 'refresh_award_details';
2737
2738 l_pa_request_extra_info_id number;
2739 l_pa_request_extra_info_sh_id number;
2740 l_pa_object_version_number number;
2741 l_dummy varchar2(30);
2742 --l_information_type varchar2(40) := 'GHR_US_PAR_AWARDS_BONUS';
2743
2744
2745 l_t_award_amount ghr_pa_requests.award_amount%type;
2746 l_t_award_uom ghr_pa_requests.award_uom%type;
2747 l_t_award_percentage ghr_pa_requests.award_percentage%type;
2748 l_t_award_agency varchar2(150);
2749 l_t_award_type varchar2(150);
2750 l_t_group_award varchar2(150);
2751 l_t_tbd varchar2(150);
2752 l_t_date_award_earn varchar2(150);
2753 l_t_appropriation_code varchar2(150);
2754
2755 l_d_award_amount ghr_pa_requests.award_amount%type;
2756 l_d_award_uom ghr_pa_requests.award_uom%type;
2757 l_d_award_percentage ghr_pa_requests.award_percentage%type;
2758 l_d_award_agency varchar2(150);
2759 l_d_award_type varchar2(150);
2760 l_d_group_award varchar2(150);
2761 l_d_tbd varchar2(150);
2762 l_d_date_award_earn varchar2(150);
2763 l_d_appropriation_code varchar2(150);
2764
2765 l_s_award_amount ghr_pa_requests.award_amount%type;
2766 l_s_award_uom ghr_pa_requests.award_uom%type;
2767 l_s_award_percentage ghr_pa_requests.award_percentage%type;
2768 l_s_award_agency varchar2(150);
2769 l_s_award_type varchar2(150);
2770 l_s_group_award varchar2(150);
2771 l_s_tbd varchar2(150);
2772 l_s_date_award_earn varchar2(150);
2773 l_s_appropriation_code varchar2(150);
2774
2775 CURSOR cur_rpa_ei (p_pa_request_id number) is
2776 SELECT pa_request_extra_info_id,
2777 object_version_number
2778 FROM ghr_pa_request_extra_info
2779 WHERE information_type = l_information_type
2780 AND pa_request_id = p_pa_request_id;
2781
2782 CURSOR cur_rpa_ei_shadow (p_pa_request_id number) is
2783 SELECT pa_request_extra_info_id
2784 FROM ghr_pa_request_ei_shadow
2785 WHERE information_type = l_information_type
2786 AND pa_request_id = p_pa_request_id;
2787
2788 BEGIN
2789
2790 hr_utility.set_location('Entering ' || l_proc,5);
2791 FOR cur_rpa_ei_rec in cur_rpa_ei(p_pa_request_id)
2792 LOOP
2793 hr_utility.set_location( l_proc,10);
2794 l_pa_request_extra_info_id := cur_rpa_ei_rec.pa_request_extra_info_id;
2795 l_pa_object_version_number := cur_rpa_ei_rec.object_version_number;
2796 exit;
2797 END LOOP;
2798
2799 FOR cur_rpa_ei_shadow_rec in cur_rpa_ei_shadow(p_pa_request_id)
2800 LOOP
2801 hr_utility.set_location( l_proc,10);
2802 l_pa_request_extra_info_sh_id := cur_rpa_ei_shadow_rec.pa_request_extra_info_id;
2803 exit;
2804 END LOOP;
2805
2806 hr_utility.set_location(l_proc,15);
2807 ----- Get Award details from the template and also extra information
2808 get_award_details
2809 ( p_mass_award_id => p_mass_award_id,
2810 p_rpa_type => 'TA',
2811 p_effective_date => p_effective_date,
2812 p_person_id => null,
2813 p_award_amount => l_dummy,
2814 p_award_uom => l_dummy,
2815 p_award_percentage => l_dummy,
2816 p_award_agency => l_t_award_agency,
2817 p_award_type => l_t_award_type,
2818 p_group_award => l_t_group_award,
2819 p_tangible_benefit_dollars => l_t_tbd,
2820 p_date_award_earned => l_t_date_award_earn,
2821 p_appropriation_code => l_t_appropriation_code);
2822
2823 hr_utility.set_location('p_award_amount - l_dummy' || l_dummy,20);
2824 hr_utility.set_location(l_proc,20);
2825 ----- Get Award details for the employee existing in the database
2826 get_award_details
2827 ( p_mass_award_id => p_mass_award_id,
2828 p_rpa_type => p_rpa_type,
2829 p_effective_date => p_effective_date,
2830 p_person_id => p_person_id,
2831 p_award_amount => l_dummy,
2832 p_award_uom => l_dummy,
2833 p_award_percentage => l_dummy,
2834 p_award_agency => l_d_award_agency,
2835 p_award_type => l_d_award_type,
2836 p_group_award => l_d_group_award,
2837 p_tangible_benefit_dollars => l_d_tbd,
2838 p_date_award_earned => l_d_date_award_earn,
2839 p_appropriation_code => l_d_appropriation_code);
2840
2841 hr_utility.set_location('p_award_amountD - l_dummy' || l_dummy,20);
2842
2843 hr_utility.set_location(l_proc,25);
2844 ----- Get Award details from the shadow
2845 get_award_details_shadow
2846 ( p_pa_request_id => p_pa_request_id,
2847 p_award_amount => l_dummy,
2848 p_award_uom => l_dummy,
2849 p_award_percentage => l_dummy,
2850 p_award_agency => l_s_award_agency,
2851 p_award_type => l_s_award_type,
2852 p_group_award => l_s_group_award,
2853 p_tangible_benefit_dollars => l_s_tbd,
2854 p_date_award_earned => l_s_date_award_earn,
2855 p_appropriation_code => l_s_appropriation_code);
2856 hr_utility.set_location('p_award_amountS - l_dummy' || l_dummy,20);
2857 ------------
2858 hr_utility.set_location(l_proc,30);
2859 set_ei
2860 (p_shadow => l_s_award_agency,
2861 p_template => l_t_award_agency,
2862 p_person => l_d_award_agency);
2863
2864 set_ei
2865 (p_shadow => l_s_award_type,
2866 p_template => l_t_award_type,
2867 p_person => l_d_award_type);
2868
2869 set_ei
2870 (p_shadow => l_s_group_award,
2871 p_template => l_t_group_award,
2872 p_person => l_d_group_award);
2873
2874 set_ei
2875 (p_shadow => l_s_tbd,
2876 p_template => l_t_tbd,
2877 p_person => l_d_tbd);
2878
2879 set_ei
2880 (p_shadow => l_s_date_award_earn,
2881 p_template => l_t_date_award_earn,
2882 p_person => l_d_date_award_earn);
2883
2884 set_ei
2885 (p_shadow => l_s_appropriation_code,
2886 p_template => l_t_appropriation_code,
2887 p_person => l_d_appropriation_code);
2888
2889 hr_utility.set_location(l_proc,40);
2890 IF l_pa_request_extra_info_id is null then
2891 hr_utility.set_location(l_proc,45);
2892 ghr_par_extra_info_api.create_pa_request_extra_info
2893 (p_validate => false,
2894 p_pa_request_id => p_pa_request_id,
2895 p_information_type => l_information_type,
2896 p_rei_information_category => l_information_type,
2897 p_rei_information3 => l_t_award_agency,
2898 p_rei_information4 => l_t_award_type,
2899 p_rei_information6 => l_t_group_award,
2900 p_rei_information7 => l_t_tbd,
2901 p_rei_information9 => l_t_date_award_earn,
2902 p_rei_information10 => l_t_appropriation_code,
2903 p_pa_request_extra_info_id => l_dummy,
2904 p_object_version_number => l_dummy
2905 );
2906 ELSE
2907 hr_utility.set_location(l_proc,50);
2908
2909 ghr_par_extra_info_api.update_pa_request_extra_info
2910 (p_validate => false,
2911 p_rei_information3 => l_d_award_agency,
2912 p_rei_information4 => l_d_award_type,
2913 p_rei_information6 => l_d_group_award,
2914 p_rei_information7 => l_d_tbd,
2915 p_rei_information9 => l_d_date_award_earn,
2916 p_rei_information10 => l_d_appropriation_code,
2917 p_pa_request_extra_info_id => l_pa_request_extra_info_id,
2918 p_object_version_number => l_pa_object_version_number);
2919 END IF;
2920 IF l_pa_request_extra_info_sh_id is null then
2921 insert into ghr_pa_request_ei_shadow
2922 (pa_request_extra_info_id,
2923 pa_request_id,
2924 information_type,
2925 rei_information3,
2926 rei_information4,
2927 rei_information6,
2928 rei_information7,
2929 rei_information9,
2930 rei_information10)
2931 values (ghr_pa_request_extra_info_s.nextval,
2932 p_pa_request_id,
2933 l_information_type,
2934 l_s_award_agency,
2935 l_s_award_type,
2936 l_s_group_award,
2937 l_s_tbd,
2938 l_s_date_award_earn,
2939 l_s_appropriation_code);
2940 ELSE update ghr_pa_request_ei_shadow
2941 set rei_information3 = l_s_award_agency,
2942 rei_information4 = l_s_award_type,
2943 rei_information6 = l_s_group_award,
2944 rei_information7 = l_s_tbd,
2945 rei_information9 = l_s_date_award_earn,
2946 rei_information10 = l_s_appropriation_code
2947 where pa_request_extra_info_id = l_pa_request_extra_info_sh_id;
2948 END IF;
2949
2950 EXCEPTION
2951
2952 when others then
2953 hr_utility.set_location('Error in ghr_par_extra info.create pa req'||
2954 ' Sql Err is '|| sqlerrm(sqlcode) || l_proc, 60);
2955 --raise mass_awarderror;
2956
2957 END refresh_award_details;
2958
2959 Procedure set_ei
2960 (p_shadow in out nocopy varchar2,
2961 p_template in varchar2,
2962 p_person in out nocopy varchar2,
2963 p_refresh_flag in varchar2 default 'Y')
2964 is
2965
2966 l_shadow varchar2(240);
2967 l_person varchar2(240);
2968 begin
2969
2970 l_shadow := p_shadow ;/*NOCOPY CHANGES*/
2971 l_person := p_person ;
2972
2973 If p_refresh_flag = 'Y' then
2974 hr_utility.set_location('in set ei - Y ',5);
2975 If nvl(p_person,hr_api.g_varchar2) <> nvl(p_template,hr_api.g_varchar2) and
2976 nvl(p_person,hr_api.g_varchar2) = nvl(p_shadow,hr_api.g_varchar2) then
2977 p_person := p_template;
2978 End if;
2979 Else
2980 hr_utility.set_location('in set ei - N ',6);
2981 p_person := p_template;
2982 End if;
2983 p_shadow := p_template;
2984 EXCEPTION
2985 WHEN others THEN
2986 -- Reset IN OUT parameters and set OUT parameters
2987
2988 p_shadow := l_shadow ;
2989 p_person := l_person ;
2990 RAISE;
2991
2992 End set_ei;
2993
2994 Procedure create_shadow_row ( p_rpa_data in ghr_pa_requests%rowtype) is
2995 Begin
2996 insert into ghr_pa_request_shadow(
2997 pa_request_id
2998 ,academic_discipline
2999 ,annuitant_indicator
3000 ,appropriation_code1
3001 ,appropriation_code2
3002 ,bargaining_unit_status
3003 ,citizenship
3004 ,duty_station_id
3005 ,duty_station_location_id
3006 ,education_level
3007 ,employee_date_of_birth
3008 ,employee_first_name
3009 ,employee_last_name
3010 ,employee_middle_names
3011 ,employee_national_identifier
3012 ,fegli
3013 ,flsa_category
3014 ,forwarding_address_line1
3015 ,forwarding_address_line2
3016 ,forwarding_address_line3
3017 ,forwarding_country_short_name
3018 ,forwarding_postal_code
3019 ,forwarding_region_2
3020 ,forwarding_town_or_city
3021 ,functional_class
3022 ,part_time_hours
3023 ,pay_rate_determinant
3024 ,position_occupied
3025 ,retirement_plan
3026 ,service_comp_date
3027 ,supervisory_status
3028 ,tenure
3029 ,to_ap_premium_pay_indicator
3030 ,to_auo_premium_pay_indicator
3031 ,to_occ_code
3032 ,to_position_id
3033 ,to_retention_allowance
3034 ,to_staffing_differential
3035 ,to_step_or_ratE
3036 ,to_supervisory_differential
3037 ,veterans_preference
3038 ,veterans_pref_for_riF
3039 ,veterans_status
3040 ,work_schedule
3041 ,year_degree_attained
3042 ,to_retention_allow_percentage
3043 ,to_supervisory_diff_percentage
3044 ,to_staffing_diff_percentage
3045 ,award_amount
3046 ,award_uom
3047 ,award_percentage )
3048 values (
3049 p_rpa_data.pa_request_id
3050 ,p_rpa_data.academic_discipline
3051 ,p_rpa_data.annuitant_indicator
3052 ,p_rpa_data.appropriation_code1
3053 ,p_rpa_data.appropriation_code2
3054 ,p_rpa_data.bargaining_unit_status
3055 ,p_rpa_data.citizenship
3056 ,p_rpa_data.duty_station_id
3057 ,p_rpa_data.duty_station_location_id
3058 ,p_rpa_data.education_level
3059 ,p_rpa_data.employee_date_of_birth
3060 ,p_rpa_data.employee_first_name
3061 ,p_rpa_data.employee_last_name
3062 ,p_rpa_data.employee_middle_names
3063 ,p_rpa_data.employee_national_identifier
3064 ,p_rpa_data.fegli
3065 ,p_rpa_data.flsa_category
3066 ,p_rpa_data.forwarding_address_line1
3067 ,p_rpa_data.forwarding_address_line2
3068 ,p_rpa_data.forwarding_address_line3
3069 ,p_rpa_data.forwarding_country_short_name
3070 ,p_rpa_data.forwarding_postal_code
3071 ,p_rpa_data.forwarding_region_2
3072 ,p_rpa_data.forwarding_town_or_city
3073 ,p_rpa_data.functional_class
3074 ,p_rpa_data.part_time_hours
3075 ,p_rpa_data.pay_rate_determinant
3076 ,p_rpa_data.position_occupied
3077 ,p_rpa_data.retirement_plan
3078 ,p_rpa_data.service_comp_date
3079 ,p_rpa_data.supervisory_status
3080 ,p_rpa_data.tenure
3081 ,p_rpa_data.to_ap_premium_pay_indicator
3082 ,p_rpa_data.to_auo_premium_pay_indicator
3083 ,p_rpa_data.to_occ_code
3084 ,p_rpa_data.to_position_id
3085 ,p_rpa_data.to_retention_allowance
3086 ,p_rpa_data.to_staffing_differential
3087 ,p_rpa_data.to_step_or_ratE
3088 ,p_rpa_data.to_supervisory_differential
3089 ,p_rpa_data.veterans_preference
3090 ,p_rpa_data.veterans_pref_for_riF
3091 ,p_rpa_data.veterans_status
3092 ,p_rpa_data.work_schedule
3093 ,p_rpa_data.year_degree_attained
3094 ,p_rpa_data.to_retention_allow_percentage
3095 ,p_rpa_data.to_supervisory_diff_percentage
3096 ,p_rpa_data.to_staffing_diff_percentage
3097 ,p_rpa_data.award_amount
3098 ,p_rpa_data.award_uom
3099 ,p_rpa_data.award_percentage );
3100 end create_shadow_row;
3101
3102 Procedure update_shadow_row ( p_rpa_data in ghr_pa_requests%rowtype,
3103 p_result out nocopy Boolean ) is
3104 Begin
3105 update ghr_pa_request_shadow
3106 set
3107 academic_discipline = p_rpa_data.academic_discipline
3108 ,annuitant_indicator = p_rpa_data.annuitant_indicator
3109 ,appropriation_code1 = p_rpa_data.appropriation_code1
3110 ,appropriation_code2 = p_rpa_data.appropriation_code2
3111 ,bargaining_unit_status = p_rpa_data.bargaining_unit_status
3112 ,citizenship = p_rpa_data.citizenship
3113 ,duty_station_id = p_rpa_data.duty_station_id
3114 ,duty_station_location_id = p_rpa_data.duty_station_location_id
3115 ,education_level = p_rpa_data.education_level
3116 ,employee_date_of_birth = p_rpa_data.employee_date_of_birth
3117 ,employee_first_name = p_rpa_data.employee_first_name
3118 ,employee_last_name = p_rpa_data.employee_last_name
3119 ,employee_middle_names = p_rpa_data.employee_middle_names
3120 ,employee_national_identifier = p_rpa_data.employee_national_identifier
3121 ,fegli = p_rpa_data.fegli
3122 ,flsa_category = p_rpa_data.flsa_category
3123 ,forwarding_address_line1 = p_rpa_data.forwarding_address_line1
3124 ,forwarding_address_line2 = p_rpa_data.forwarding_address_line2
3125 ,forwarding_address_line3 = p_rpa_data.forwarding_address_line3
3126 ,forwarding_country_short_name = p_rpa_data.forwarding_country_short_name
3127 ,forwarding_postal_code = p_rpa_data.forwarding_postal_code
3128 ,forwarding_region_2 = p_rpa_data.forwarding_region_2
3129 ,forwarding_town_or_city = p_rpa_data.forwarding_town_or_city
3130 ,functional_class = p_rpa_data.functional_class
3131 ,part_time_hours = p_rpa_data.part_time_hours
3132 ,pay_rate_determinant = p_rpa_data.pay_rate_determinant
3133 ,position_occupied = p_rpa_data.position_occupied
3134 ,retirement_plan = p_rpa_data.retirement_plan
3135 ,service_comp_date = p_rpa_data.service_comp_date
3136 ,supervisory_status = p_rpa_data.supervisory_status
3137 ,tenure = p_rpa_data.tenure
3138 ,to_ap_premium_pay_indicator = p_rpa_data.to_ap_premium_pay_indicator
3139 ,to_auo_premium_pay_indicator = p_rpa_data.to_auo_premium_pay_indicator
3140 ,to_occ_code = p_rpa_data.to_occ_code
3141 ,to_position_id = p_rpa_data.to_position_id
3142 ,to_retention_allowance = p_rpa_data.to_retention_allowance
3143 ,to_staffing_differential = p_rpa_data.to_staffing_differential
3144 ,to_step_or_ratE = p_rpa_data.to_step_or_ratE
3145 ,to_supervisory_differential = p_rpa_data.to_supervisory_differential
3146 ,veterans_preference = p_rpa_data.veterans_preference
3147 ,veterans_pref_for_riF = p_rpa_data.veterans_pref_for_riF
3148 ,veterans_status = p_rpa_data.veterans_status
3149 ,work_schedule = p_rpa_data.work_schedule
3150 ,year_degree_attained = p_rpa_data.year_degree_attained
3151 ,to_retention_allow_percentage = p_rpa_data.to_retention_allow_percentage
3152 ,to_supervisory_diff_percentage = p_rpa_data.to_supervisory_diff_percentage
3153 ,to_staffing_diff_percentage = p_rpa_data.to_staffing_diff_percentage
3154 ,award_amount = p_rpa_data.award_amount
3155 ,award_uom = p_rpa_data.award_uom
3156 ,award_percentage = p_rpa_data.award_percentage
3157 where pa_request_id = p_rpa_data.pa_request_id;
3158
3159 if sql%notfound then
3160 p_result := FALSE;
3161 else
3162 p_result := TRUE;
3163 end if;
3164
3165 EXCEPTION
3166 WHEN others THEN
3167 -- Reset IN OUT parameters and set OUT parameters
3168
3169 p_result := NULL;
3170 RAISE;
3171
3172
3173 end update_shadow_row;
3174
3175 Procedure create_remarks
3176 (p_mass_award_id in ghr_mass_awards.mass_award_id%TYPE,
3177 p_rpa_type in ghr_pa_requests.rpa_type%TYPE,
3178 p_effective_date in date,
3179 p_pa_request_id in ghr_pa_requests.pa_request_id%type
3180 )
3181
3182 is
3183
3184 l_proc varchar2(72) := g_package || 'create_remarks';
3185 l_pa_remark_id ghr_pa_remarks.pa_remark_id%type;
3186 l_remark_id ghr_pa_remarks.remark_id%type;
3187 l_description ghr_pa_remarks.description%type;
3188 l_remark_code_information1 ghr_pa_remarks.remark_code_information1%type;
3189 l_remark_code_information2 ghr_pa_remarks.remark_code_information2%type;
3190 l_remark_code_information3 ghr_pa_remarks.remark_code_information3%type;
3191 l_remark_code_information4 ghr_pa_remarks.remark_code_information4%type;
3192 l_remark_code_information5 ghr_pa_remarks.remark_code_information5%type;
3193 l_p_pa_remark_id ghr_pa_remarks.remark_id%type;
3194 l_p_object_version_number ghr_pa_remarks.object_version_number%type;
3195
3196 cursor cur_rem_tmp is
3197 select pa_remark_id,
3198 remark_id,
3199 description,
3200 remark_code_information1,
3201 remark_code_information2,
3202 remark_code_information3,
3203 remark_code_information4,
3204 remark_code_information5
3205 from ghr_pa_remarks
3206 where pa_request_id =
3207 (select pa_request_id from ghr_pa_requests
3208 where mass_action_id = p_mass_award_id
3209 and rpa_type = p_rpa_type
3210 and person_id is null)
3211 order by pa_remark_id;
3212
3213 cursor cur_rem is
3214 select pa_remark_id,
3215 object_version_number
3216 from ghr_pa_remarks
3217 where pa_request_id = p_pa_request_id
3218 and remark_id = l_remark_id
3219 and description = l_description
3220 order by pa_remark_id;
3221
3222 begin
3223
3224 -- get template remarks and descriptions
3225 hr_utility.set_location('Entering ' || l_proc,5);
3226
3227 for cur_rem_tmp_rec in cur_rem_tmp loop
3228 l_pa_remark_id := cur_rem_tmp_rec.pa_remark_id;
3229 l_remark_id := cur_rem_tmp_rec.remark_id;
3230 l_description := cur_rem_tmp_rec.description;
3231 l_remark_code_information1 := cur_rem_tmp_rec.remark_code_information1;
3232 l_remark_code_information2 := cur_rem_tmp_rec.remark_code_information2;
3233 l_remark_code_information3 := cur_rem_tmp_rec.remark_code_information3;
3234 l_remark_code_information4 := cur_rem_tmp_rec.remark_code_information4;
3235 l_remark_code_information5 := cur_rem_tmp_rec.remark_code_information5;
3236
3237 l_p_pa_remark_id := null;
3238 l_p_object_version_number := null;
3239
3240 for cur_rem_rec in cur_rem loop
3241 l_p_pa_remark_id := cur_rem_rec.pa_remark_id;
3242 l_p_object_version_number := cur_rem_rec.object_version_number;
3243 end loop;
3244
3245 if l_p_pa_remark_id is null then
3246 ghr_pa_remarks_api.create_pa_remarks
3247 (
3248 p_pa_request_id => p_pa_request_id
3249 ,p_remark_id => l_remark_id
3250 ,p_description => l_description
3251 ,p_remark_code_information1 => l_remark_code_information1
3252 ,p_remark_code_information2 => l_remark_code_information2
3253 ,p_remark_code_information3 => l_remark_code_information3
3254 ,p_remark_code_information4 => l_remark_code_information4
3255 ,p_remark_code_information5 => l_remark_code_information5
3256 ,p_pa_remark_id => l_p_pa_remark_id
3257 ,p_object_version_number => l_p_object_version_number
3258 );
3259 else
3260 ghr_pa_remarks_api.update_pa_remarks
3261 (p_pa_remark_id => l_p_pa_remark_id
3262 ,p_object_version_number => l_p_object_version_number
3263 ,p_remark_code_information1 => l_remark_code_information1
3264 ,p_remark_code_information2 => l_remark_code_information2
3265 ,p_remark_code_information3 => l_remark_code_information3
3266 ,p_remark_code_information4 => l_remark_code_information4
3267 ,p_remark_code_information5 => l_remark_code_information5
3268 ,p_description => l_description
3269 );
3270 end if;
3271 end loop;
3272
3273 end create_remarks;
3274
3275 Procedure mass_awards_error_handling
3276 ( p_pa_request_id in ghr_pa_requests.pa_request_id%type,
3277 p_object_version_number in ghr_pa_requests.object_version_number%type,
3278 p_error in varchar2 default null,
3279 p_result out nocopy boolean
3280 )
3281
3282 is
3283
3284 ---
3285 --- Local variables
3286 ---
3287 l_pa_request_id ghr_pa_requests.pa_request_id%type;
3288 l_object_version_number ghr_pa_requests.object_version_number%type;
3289 l_person_id ghr_pa_requests.person_id%type;
3290 l_employee_first_name ghr_pa_requests.employee_first_name%type;
3291 l_employee_last_name ghr_pa_requests.employee_last_name%type;
3292 l_employee_middle_names ghr_pa_requests.employee_middle_names%type;
3293 l_employee_national_identifier ghr_pa_requests.employee_national_identifier%type;
3294 l_error varchar2(2000);
3295 l_log_text varchar2(2000);
3296 l_proc varchar2(72) := 'mass_awards_error_handling';
3297
3298 CURSOR cur_rpa IS
3299 SELECT object_version_number,
3300 person_id,
3301 employee_last_name,
3302 employee_first_name,
3303 employee_middle_names,
3304 employee_national_identifier,
3305 effective_date
3306 FROM ghr_pa_requests
3307 WHERE pa_request_id = l_pa_request_id;
3308
3309 --Bug # 9329643
3310 l_employee_number varchar2(30);
3311 l_effective_date date;
3312
3313
3314 begin
3315
3316 hr_utility.set_location('Entering ..' || l_proc,5);
3317
3318 l_pa_request_id := p_pa_request_id;
3319 l_object_version_number := p_object_version_number;
3320 l_error := p_error;
3321
3322 hr_utility.set_location('pa_request_id = ' ||to_char( l_pa_request_id),5);
3323 hr_utility.set_location('object_version_number = ' ||to_char(l_object_version_number),5);
3324
3325 ghr_api.call_workflow (
3326 p_pa_request_id => l_pa_request_id,
3327 p_action_taken => 'CONTINUE',
3328 p_error => l_error);
3329
3330 for cur_rpa_rec in cur_rpa loop
3331 hr_utility.set_location( l_proc,10);
3332 l_object_version_number := cur_rpa_rec.object_version_number;
3333 l_person_id := cur_rpa_rec.person_id;
3334 l_employee_last_name := cur_rpa_rec.employee_last_name;
3335 l_employee_first_name := cur_rpa_rec.employee_first_name;
3336 l_employee_middle_names := cur_rpa_rec.employee_middle_names;
3337 l_employee_national_identifier := cur_rpa_rec.employee_national_identifier;
3338 l_effective_date := cur_rpa_rec.effective_date;
3339 hr_utility.set_location('object_version_number = ' ||to_char( l_object_version_number),10);
3340 end loop;
3341
3342
3343 --Bug # 9329643
3344 If l_person_id is not null then
3345 l_employee_number := ghr_pa_requests_pkg2.get_employee_number
3346 (p_person_id => l_person_id,
3347 p_effective_date => l_effective_date);
3348 End if;
3349
3350 -- Call ghr_par_upd.upd here
3351 ghr_par_upd.upd(
3352 p_pa_request_id => l_pa_request_id,
3353 p_rpa_type => 'M',
3354 p_object_version_number => l_object_version_number);
3355
3356 hr_utility.set_location( 'Made Manual RPA' || to_char(l_pa_request_id) ,20);
3357 hr_utility.set_location( 'Leaving ' || l_proc ,25);
3358 -- Bug#3718167 Added SSN to the following log text
3359 --Bug # 9329643 Modified SSN to Emp No
3360 l_log_text := ' ,' || l_employee_last_name || ', ' || l_employee_first_name||' '||l_employee_middle_names
3361 ||'Emp No: '|| l_employee_number;
3362
3363 ghr_wgi_pkg.create_ghr_errorlog(
3364 p_program_name => g_log_name,
3365 p_message_name => substr('Manual RPA',1,30),
3366 p_log_text => substr('Person id : ' || to_char(l_person_id) || ' ' || l_log_text,1,2000),
3367 p_log_date => sysdate);
3368
3369 p_result := FALSE;
3370
3371 exception when others then
3372 p_result := TRUE;
3373 begin
3374 ghr_par_upd.upd(
3375 p_pa_request_id => l_pa_request_id,
3376 p_mass_action_eligible_flag => 'Y',
3377 p_mass_action_select_flag => 'N',
3378 p_mass_action_comments =>
3379 'PRG: Prgramatically Deselected, Failed at Call Work flow',
3380 p_object_version_number => l_object_version_number);
3381 exception when others then
3382 null;
3383 end;
3384
3385 end mass_awards_error_handling;
3386
3387 --Pradeep added this procedure for bug 3934195
3388 /* Logic is first set the message name and tokens as usual and then use
3389 the fnd_message.get to get the message that we have assigned.
3390 This is to remove the hard coding and using only the messages.
3391 */
3392
3393 PROCEDURE check_award_amount (p_noa_code ghr_pa_requests.first_noa_code%TYPE,
3394 p_effective_date ghr_pa_requests.effective_date%TYPE,
3395 p_award_amount NUMBER,
3396 p_from_pay_plan ghr_pa_requests.from_pay_plan%TYPE,
3397 p_from_basic_pay_pa ghr_pa_requests.from_basic_pay%TYPE,
3398 p_to_position_id ghr_pa_requests.to_position_id%TYPE,
3399 p_comments OUT NOCOPY varchar2,
3400 p_error_flg OUT NOCOPY BOOLEAN) IS
3401 l_temp NUMBER;
3402 l_max_allowed_amount NUMBER;
3403 l_min_allowed_amount NUMBER;
3404 --BUG 5482191
3405 l_psi VARCHAR2(10);
3406 BEGIN
3407 --bug 5482191
3408
3409 l_psi := ghr_pa_requests_pkg.get_personnel_system_indicator(p_to_position_id,p_effective_date);
3410
3411
3412 p_error_flg := FALSE;
3413 --Check for Maximum Amount.
3414 fnd_message.set_name ('GHR', 'GHR_38904_AWARD_AMT_TOO_BIG5');
3415
3416 IF p_noa_code = '844' THEN
3417 l_max_allowed_amount := 5 * p_from_basic_pay_pa / 100;
3418 fnd_message.set_token ('ALLOWED', '5%');
3419 ELSIF p_noa_code IN ('840', '841', '879') THEN
3420 l_max_allowed_amount := 25 * p_from_basic_pay_pa / 100;
3421 fnd_message.set_token ('ALLOWED', '25%');
3422 ELSIF p_noa_code IN ('878') THEN
3423 l_max_allowed_amount := 35 * p_from_basic_pay_pa / 100;
3424 fnd_message.set_token ('ALLOWED', '35%');
3425 --bug 5482191
3426 ELSIF (p_noa_code IN ('849') and l_psi = '00' ) THEN
3427 l_max_allowed_amount := 35 * p_from_basic_pay_pa / 100;
3428 fnd_message.set_token ('ALLOWED', '35%');
3429
3430 ELSIF p_noa_code IN ('825', '842', '843', '848') THEN
3431 l_max_allowed_amount := 25000;
3432 fnd_message.set_name ('GHR', 'GHR_38905_AWARD_AMT_TOO_BIG6');
3433 fnd_message.set_token ('ALLOWED', '$25000');
3434 END IF;
3435
3436 /*
3437 Existing Handling of 816 and 815 is not changed make sure
3438 that 815 and 816 are not included in above list.
3439 */
3440 IF p_noa_code = '816' THEN
3441 IF p_from_pay_plan = 'EE' THEN
3442 IF (50 * p_from_basic_pay_pa / 100) > 50000 THEN
3443 l_max_allowed_amount := 50000;
3444 ELSE
3445 l_max_allowed_amount := 50 * p_from_basic_pay_pa / 100;
3446 END IF;
3447
3448 IF p_award_amount > l_max_allowed_amount THEN
3449 fnd_message.set_name ('GHR', 'GHR_38898_AWARD_AMT_TOO_BIG3');
3450 p_comments := p_comments||fnd_message.get;
3451 p_error_flg := TRUE;
3452 fnd_message.clear;
3453 END IF;
3454 ELSIF ( ghr_pay_calc.leo_position (
3455 p_prd => l_temp,
3456 p_position_id=> p_to_position_id,
3457 p_retained_user_table_id=> l_temp,
3458 p_duty_station_id=> l_temp,
3459 p_effective_date=> p_effective_date
3460 )
3461 ) THEN
3462
3463 l_max_allowed_amount := 25 * p_from_basic_pay_pa / 100;
3464 IF l_max_allowed_amount < 15000 THEN
3465 l_max_allowed_amount := 15000;
3466 END IF;
3467 IF p_award_amount > l_max_allowed_amount THEN
3468 fnd_message.set_name ('GHR', 'GHR_38896_AWARD_AMT_TOO_BIG2');
3469 p_comments := p_comments||fnd_message.get;
3470 p_error_flg := TRUE;
3471 fnd_message.clear;
3472 END IF;
3473 ELSIF ( NOT ghr_pay_calc.leo_position (
3474 p_prd => l_temp,
3475 p_position_id=> p_to_position_id,
3476 p_retained_user_table_id=> l_temp,
3477 p_duty_station_id=> l_temp,
3478 p_effective_date=> p_effective_date
3479 )
3480 ) THEN
3481 l_max_allowed_amount := 25 * p_from_basic_pay_pa / 100;
3482
3483 IF p_award_amount > l_max_allowed_amount THEN
3484 fnd_message.set_name ('GHR', 'GHR_AWARD_AMT_TOO_BIG');
3485 p_comments := p_comments||fnd_message.get;
3486 p_error_flg := TRUE;
3487 fnd_message.clear;
3488 END IF;
3489 END IF;
3490 ELSIF p_noa_code = '815' THEN
3491 IF p_from_pay_plan = 'EE' THEN
3492 IF (50 * p_from_basic_pay_pa / 100) > 50000 THEN
3493 l_max_allowed_amount := 50000;
3494 ELSE
3495 l_max_allowed_amount := 50 * p_from_basic_pay_pa / 100;
3496 END IF;
3497
3498 IF p_award_amount > l_max_allowed_amount THEN
3499 fnd_message.set_name ('GHR', 'GHR_38898_AWARD_AMT_TOO_BIG3');
3500 p_comments := p_comments||fnd_message.get;
3501 p_error_flg := TRUE;
3502 fnd_message.clear;
3503 END IF;
3504 ELSE
3505 l_max_allowed_amount := 25 * p_from_basic_pay_pa / 100;
3506 fnd_message.set_token ('ALLOWED', '25%');
3507 p_comments := p_comments||fnd_message.get;
3508 p_error_flg := TRUE;
3509 fnd_message.clear;
3510 END IF;
3511 END IF;
3512
3513 -- Raise an Error if Award Amount is Greater than the Maximum Allowed Amount.
3514 --IF p_award_amount > round(l_max_allowed_amount) THEN
3515
3516 IF p_award_amount > floor(l_max_allowed_amount) THEN
3517 p_comments := fnd_message.get;
3518 p_error_flg := TRUE;
3519 fnd_message.CLEAR;
3520 ELSE
3521 fnd_message.CLEAR;
3522 END IF;
3523
3524 --Check for Minimum Amount.
3525 fnd_message.set_name ('GHR', 'GHR_38903_AWARD_AMT_TOO_LESS');
3526
3527 --Getting the Minimum Allowed Amount.
3528 IF p_noa_code = '879' THEN
3529 l_min_allowed_amount := 5 * p_from_basic_pay_pa / 100;
3530 fnd_message.set_token ('ALLOWED', '5%');
3531 END IF;
3532
3533 -- Raise an Error if Award Amount is Less than the Minimum Allowed Amount.
3534 IF p_award_amount < trunc(l_min_allowed_amount) THEN
3535 p_comments := fnd_message.get;
3536 p_error_flg := TRUE;
3537 fnd_message.clear;
3538 ELSE
3539 fnd_message.clear;
3540 END IF;
3541
3542 END check_award_amount;
3543
3544 END GHR_MASS_AWARDS_PKG;