[Home] [Help]
PACKAGE BODY: APPS.GHR_MASS_AWARDS_PKG
Source
1 PACKAGE BODY GHR_MASS_AWARDS_PKG AS
2 /* $Header: ghrmarpa.pkb 120.12 2006/11/08 16:59:43 ygnanapr noship $ */
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
358 BEGIN
355 AND rpa_type = p_rpa_type
356 AND person_id is null;
357
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;
473 hr_utility.set_location(' group award = ' || p_group_award ,20);
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);
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
624 select count(*) into l_cntr
621
622 hr_utility.set_location('Entering ' || l_proc,5);
623
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
754
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;
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
868 */
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;
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,
954 p_from_position_org_line5 => l_pa_request_rec.from_position_org_line5,
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,
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,
1029 p_first_lac1_information4 => l_pa_request_rec.first_lac1_information4,
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,
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
1126 p_rei_information4 => null,
1123 ghr_par_extra_info_api.update_pa_request_extra_info
1124 (p_validate => false,
1125 p_rei_information3 => 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,
1247 /*
1244 p_template => l_t_award_percentage,
1245 p_person => l_d_award_percentage);
1246 -- Begin Bug# 4748927
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,
1349 p_employee_middle_names => l_pa_request_rec.employee_middle_names,
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,
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,
1426 p_to_grade_id => l_pa_request_rec.to_grade_id,
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,
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,
1504
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,
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,
1618 p_maxcheck out nocopy number--/ in conc. manager.
1615 p_errbuf out nocopy varchar2, --\ error log
1616 p_status out nocopy varchar2, --||
1617 p_retcode out nocopy number,
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 Begin
1725 p_errbuf := Null;
1726 p_status := Null;
1727 p_retcode := Null;
1728
1729 for cur_maw_name_rec in cur_maw_name
1730 loop
1731 l_maw_name := cur_maw_name_rec.name;
1732 end loop;
1733
1734 hr_utility.set_location('Entering ' || l_proc,5);
1735 l_savepoint := 'P';
1736 savepoint build_rpa_save_preview;
1740 l_pa_request_rec.additional_info_person_id := NULL;
1737 hr_utility.set_location('savepoint build_rpa_save_preview ' || l_proc,5);
1738 -- The following would be null while creating rpa.
1739
1741 l_pa_request_rec.additional_info_tel_number := NULL;
1742 l_pa_request_rec.Proposed_Effective_Date := NULL;
1743 l_pa_request_rec.Proposed_Effective_ASAP_flag := 'N';
1744 l_pa_request_rec.requested_by_person_id := NULL;
1745 l_pa_request_rec.requested_by_title := NULL;
1746 l_pa_request_rec.requested_date := NULL;
1747 l_pa_request_rec.authorized_by_person_id := NULL;
1748 l_pa_request_rec.authorized_by_title := NULL;
1749 l_pa_request_rec.concurrence_Date := NULL;
1750 --
1751 l_pa_request_rec.to_step_or_rate := NULL;
1752 l_pa_request_rec.to_adj_basic_pay := NULL;
1753 l_pa_request_rec.to_basic_pay := NULL;
1754 l_pa_request_rec.to_total_salary := NULL;
1755 l_pa_request_rec.to_other_pay_amount := NULL;
1756 l_pa_request_rec.to_au_overtime := NULL;
1757 l_pa_request_rec.to_auo_premium_pay_indicator := NULL;
1758 l_pa_request_rec.to_availability_pay := NULL;
1759 l_pa_request_rec.to_ap_premium_pay_indicator := NULL;
1760 l_pa_request_rec.to_retention_allowance := NULL;
1761 l_pa_request_rec.to_supervisory_differential := NULL;
1762 l_pa_request_rec.to_staffing_differential := NULL;
1763 l_pa_request_rec.to_locality_adj := NULL;
1764 l_pa_request_rec.to_retention_allow_percentage := NULL;
1765 l_pa_request_rec.to_supervisory_diff_percentage := NULL;
1766 l_pa_request_rec.to_staffing_diff_percentage := NULL;
1767
1768
1769 ---l_pa_request_rec.to_adj_basic_pay := 0;
1770 ---l_pa_request_rec.to_basic_pay := 0; --APP-37206 CPDF Edit #370.25.2
1771 ---l_pa_request_rec.to_total_salary := 0;
1772
1773 hr_utility.set_location(l_proc,10);
1774 l_pa_request_rec.mass_action_id := p_mass_award_id;
1775 l_pa_request_rec.rpa_type := p_rpa_type;
1776 l_pa_request_rec.effective_date := p_effective_date;
1777 l_pa_request_rec.person_id := p_person_id;
1778 l_pa_request_rec.employee_assignment_id := p_assignment_id;
1779 l_pa_request_rec.from_position_id := p_position_id;
1780 l_pa_request_rec.to_grade_id := p_grade_id;
1781 l_pa_request_rec.duty_station_location_id := p_location_id;
1782 l_pa_request_rec.to_job_id := p_job_id;
1783
1784 l_pa_request_rec.first_noa_id := get_noa_id(p_mass_award_id);
1785 l_pa_request_rec.noa_family_code :=
1786 ghr_pa_requests_pkg.get_noa_pm_family
1787 (p_nature_of_action_id => l_pa_request_rec.first_noa_id);
1788
1789 hr_utility.set_location(l_proc,15);
1790 get_noa_code_desc
1791 ( p_noa_id => l_pa_request_rec.first_noa_id,
1792 p_effective_date => p_effective_date,
1793 p_noa_code => l_pa_request_rec.first_noa_code,
1794 p_noa_desc => l_pa_request_rec.first_noa_desc );
1795
1796 hr_utility.set_location(l_proc,20);
1797 ghr_pa_requests_pkg.get_person_details
1798 (p_person_id => p_person_id
1799 ,p_effective_date => p_effective_date
1800 ,p_national_identifier => l_pa_request_rec.employee_national_identifier
1801 ,p_date_of_birth => l_pa_request_rec.employee_date_of_birth
1802 ,p_last_name => l_pa_request_rec.employee_last_name
1803 ,p_first_name => l_pa_request_rec.employee_first_name
1804 ,p_middle_names => l_pa_request_rec.employee_middle_names
1805 );
1806
1807 hr_utility.set_location(l_proc,25);
1808 ghr_api.sf52_from_data_elements
1809 (p_person_id => p_person_id
1810 ,p_assignment_id => l_pa_request_rec.employee_assignment_id
1811 ,p_effective_date => p_effective_date
1812 ,p_altered_pa_request_id => null
1813 ,p_noa_id_corrected => null
1814 ,p_pa_history_id => null
1815 ,p_position_title => l_pa_request_rec.from_position_title
1816 ,p_position_number => l_pa_request_rec.from_position_number
1817 ,p_position_seq_no => l_pa_request_rec.from_position_seq_no
1818 ,p_pay_plan => l_pa_request_rec.from_pay_plan
1819 ,p_job_id => l_pa_request_rec.to_job_id
1820 ,p_occ_code => l_pa_request_rec.from_occ_code
1821 ,p_grade_id => l_pa_request_rec.to_grade_id
1822 ,p_grade_or_level => l_pa_request_rec.from_grade_or_level
1823 ,p_step_or_rate => l_pa_request_rec.from_step_or_rate
1824 ,p_total_salary => l_pa_request_rec.from_total_salary
1825 ,p_pay_basis => l_pa_request_rec.from_pay_basis
1826 -- FWFA Changes Bug#4444609
1827 ,p_pay_table_identifier => l_pa_request_rec.from_pay_table_identifier
1828 -- FWFA Changes
1829 ,p_basic_pay => l_pa_request_rec.from_basic_pay
1833 ----Bug 2348413
1830 ,p_locality_adj => l_pa_request_rec.from_locality_adj
1831 ,p_adj_basic_pay => l_pa_request_rec.from_adj_basic_pay
1832 ,p_other_pay => l_pa_request_rec.from_other_pay_amount
1834 ,p_au_overtime => l_dummy_number
1835 ,p_auo_premium_pay_indicator => l_dummy
1836 ,p_availability_pay => l_dummy_number
1837 ,p_ap_premium_pay_indicator => l_dummy
1838 ,p_retention_allowance => l_dummy_number
1839 ,p_retention_allow_percentage => l_dummy_number
1840 ,p_supervisory_differential => l_dummy_number
1841 ,p_supervisory_diff_percentage => l_dummy_number
1842 ,p_staffing_differential => l_dummy_number
1843 ,p_staffing_diff_percentage => l_dummy_number
1844 ----Bug 2348413
1845 ,p_organization_id => l_pa_request_rec.to_organization_id
1846 ,p_position_org_line1 => l_pa_request_rec.from_position_org_line1
1847 ,p_position_org_line2 => l_pa_request_rec.from_position_org_line2
1848 ,p_position_org_line3 => l_pa_request_rec.from_position_org_line3
1849 ,p_position_org_line4 => l_pa_request_rec.from_position_org_line4
1850 ,p_position_org_line5 => l_pa_request_rec.from_position_org_line5
1851 ,p_position_org_line6 => l_pa_request_rec.from_position_org_line6
1852 ,p_position_id => l_pa_request_rec.from_position_id
1853 ,p_duty_station_location_id => l_duty_station_location_id
1854 -- FWFA Changes Bug#4444609
1855 ,p_pay_rate_determinant => l_dummy
1856 -- FWFA Changes
1857 ,p_work_schedule => l_pa_request_rec.work_schedule
1858 );
1859
1860 hr_utility.set_location(l_proc,30);
1861 hr_utility.set_location('Bef Get Aw Det'||l_pa_request_rec.award_amount ,31);
1862 ----- Get Award details from the template and also extra information
1863 get_award_details
1864 ( p_mass_award_id => p_mass_award_id,
1865 p_rpa_type => 'TA',
1866 p_effective_date => p_effective_date,
1867 p_person_id => null,
1868 p_award_amount => l_t_award_amount,
1869 p_award_uom => l_t_award_uom,
1870 p_award_percentage => l_t_award_percentage,
1871 p_award_agency => l_dummy,
1872 p_award_type => l_dummy,
1873 p_group_award => l_dummy,
1874 p_tangible_benefit_dollars => l_dummy,
1875 p_date_award_earned => l_dummy,
1876 p_appropriation_code => l_dummy);
1877
1878 l_pa_request_rec.award_amount := l_t_award_amount;
1879 l_pa_request_rec.award_uom := l_t_award_uom;
1880 l_pa_request_rec.award_percentage := l_t_award_percentage;
1881 l_bt_award_amount := l_t_award_amount;
1882 l_bt_award_percentage := l_t_award_percentage;
1883 hr_utility.set_location('Aft Get Aw Det'||l_pa_request_rec.award_amount ,31);
1884 -- Begin Bug# 4748927
1885 /* l_from_basic_pay_pa :=
1886 ghr_pay_calc.convert_amount(l_pa_request_rec.from_basic_pay
1887 ,l_pa_request_rec.from_pay_basis,'PA');*/
1888 --End Bug# 4748927
1889 hr_utility.set_location(l_proc,35);
1890 l_pa_request_rec.duty_station_location_id := l_duty_station_location_id;
1891
1892 ghr_pa_requests_pkg.get_SF52_loc_ddf_details
1893 (p_location_id => l_duty_station_location_id
1894 ,p_duty_station_id => l_pa_request_rec.duty_station_id);
1895
1896 ghr_pa_requests_pkg.get_duty_station_details
1897 (p_duty_station_id => l_pa_request_rec.duty_station_id
1898 ,p_effective_date => l_pa_request_rec.effective_date
1899 ,p_duty_station_code => l_pa_request_rec.duty_station_code
1900 ,p_duty_station_desc => l_pa_request_rec.duty_station_desc);
1901 -- Begin Bug# 4748927
1902 if l_pa_request_rec.award_percentage is not null then
1903 l_pa_request_rec.award_amount :=
1904 ROUND(l_pa_request_rec.award_salary *
1905 NVL(l_pa_request_rec.award_percentage,0) / 100 , 0);
1906
1907 -- Bug# 4748927 End
1908 end if;
1909
1910 l_pa_request_rec.to_position_id := l_pa_request_rec.from_position_id;
1911 l_pa_request_rec.to_position_title := l_pa_request_rec.from_position_title;
1912 l_pa_request_rec.to_position_number := l_pa_request_rec.from_position_number;
1913 l_pa_request_rec.to_position_seq_no := l_pa_request_rec.from_position_seq_no;
1914 l_pa_request_rec.to_position_org_line1 := l_pa_request_rec.from_position_org_line1;
1915 l_pa_request_rec.to_position_org_line2 := l_pa_request_rec.from_position_org_line2;
1916 l_pa_request_rec.to_position_org_line3 := l_pa_request_rec.from_position_org_line3;
1917 l_pa_request_rec.to_position_org_line4 := l_pa_request_rec.from_position_org_line4;
1918 l_pa_request_rec.to_position_org_line5 := l_pa_request_rec.from_position_org_line5;
1919 l_pa_request_rec.to_position_org_line6 := l_pa_request_rec.from_position_org_line6;
1920
1921 hr_utility.set_location(l_proc,40);
1922 ghr_pa_requests_pkg.get_sf52_pos_ddf_details
1923 (p_position_id => p_position_id
1924 ,p_date_Effective => l_pa_request_rec.effective_date
1925 ,p_flsa_category => l_pa_request_rec.flsa_category
1926 ,p_bargaining_unit_status => l_pa_request_rec.bargaining_unit_status
1927 ,p_work_schedule => l_dummy
1931 ,p_appropriation_code1 => l_pa_request_rec.appropriation_code1
1928 ,p_functional_class => l_pa_request_rec.functional_class
1929 ,p_supervisory_status => l_pa_request_rec.supervisory_status
1930 ,p_position_occupied => l_pa_request_rec.position_occupied
1932 ,p_appropriation_code2 => l_pa_request_rec.appropriation_code2
1933 ,p_personnel_office_id => l_pa_request_rec.personnel_office_id
1934 ,p_office_symbol => l_pa_request_rec.from_office_symbol
1935 ,p_part_time_hours => l_dummy
1936 );
1937
1938 hr_utility.set_location('POI ID ' || l_pa_request_rec.personnel_office_id ,42);
1939 hr_utility.set_location('From Pos ID ' || to_char(l_pa_request_rec.from_position_id) ,42);
1940 hr_utility.set_location('To Pos ID ' || to_char(l_pa_request_rec.to_position_id) ,42);
1941 hr_utility.set_location('Office Symbol ' || l_pa_request_rec.from_office_symbol ,42);
1942
1943 hr_utility.set_location(l_proc,45);
1944 get_business_group
1945 (p_person_id => p_person_id
1946 ,p_effective_date => p_effective_date
1947 ,p_business_group_id => l_business_group_id
1948 );
1949
1950 hr_utility.set_location(l_proc,50);
1951 l_pa_request_rec.agency_code := ghr_api.get_position_agency_code_pos
1952 (p_position_id => p_position_id
1953 ,p_business_group_id => l_business_group_id
1954 ,p_effective_date => p_effective_date
1955 );
1956 hr_utility.set_location('Agency Code ' || l_pa_request_rec.agency_code ,52);
1957
1958 l_pa_request_rec.employee_dept_or_agency := ghr_pa_requests_pkg.get_lookup_meaning
1959 (800
1960 ,'GHR_US_AGENCY_CODE'
1961 ,l_pa_request_rec.agency_code
1962 );
1963
1964 hr_utility.set_location('employee_dept_or_agency ' || l_pa_request_rec.employee_dept_or_agency ,52);
1965 hr_utility.set_location('Noa_family_code value ' || l_pa_request_rec.noa_family_code,52);
1966 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,52);
1967 hr_utility.set_location('PA OVN ' || to_char(l_pa_request_rec.object_version_number),52);
1968 hr_utility.set_location('l_pa_request_rec.award_amount '||l_pa_request_rec.award_amount ,52);
1969
1970 -- Bug 5041967 Sundar
1971 ghr_history_fetch.fetch_asgei(
1972 p_assignment_id => l_pa_request_rec.employee_assignment_id,
1973 p_information_type => 'GHR_US_ASG_SF52',
1974 p_date_effective => p_effective_date,
1975 p_asg_ei_data => l_asg_ei_data);
1976 ghr_pay_calc.award_amount_calc (
1977 p_position_id => l_pa_request_rec.to_position_id
1978 ,p_pay_plan => l_pa_request_rec.from_pay_plan
1979 ,p_award_percentage => l_pa_request_rec.award_percentage
1980 ,p_user_table_id => l_pa_request_rec.from_pay_table_identifier
1981 ,p_grade_or_level => l_pa_request_rec.from_grade_or_level
1982 ,p_effective_date => l_pa_request_rec.effective_date
1983 ,p_basic_pay => l_pa_request_rec.from_basic_pay
1984 ,p_adj_basic_pay => l_pa_request_rec.from_adj_basic_pay
1985 ,p_duty_station_id => l_pa_request_rec.duty_station_id
1986 ,p_prd => l_asg_ei_data.aei_information6
1987 ,p_pay_basis => l_pa_request_rec.from_pay_basis
1988 ,p_person_id => l_pa_request_rec.person_id
1989 -- ,p_award_amount => l_pa_request_rec.award_amount Bug 5041967
1990 ,p_award_amount => l_dummy
1991 ,p_award_salary => l_pa_request_rec.award_salary
1992 );
1993 -- End Bug 5041967 Sundar
1994
1995 -----------Created marpa_process procedure Call
1996 begin
1997 hr_utility.set_location('Bef MAR PA '||l_pa_request_rec.award_amount ,32);
1998 marpa_process
1999 (
2000 p_mass_award_id => p_mass_award_id
2001 ,p_action_type => p_action_type
2002 ,p_rpa_type => p_rpa_type
2003 ,p_effective_date => p_effective_date
2004 ,p_person_id => p_person_id
2005 ,p_pa_request_rec => l_pa_request_rec
2006 ,p_log_text => l_log_text
2007 ,p_maxcheck => p_maxcheck
2008 );
2009
2010 hr_utility.set_location('After MAR PA '||l_pa_request_rec.award_amount ,33);
2011 commit;
2012 hr_utility.set_location('commit to have eligibility build_rpa_save_preview ' ,162);
2013 l_savepoint := 'P';
2014 savepoint build_rpa_save_preview;
2015 hr_utility.set_location('savepoint build_rpa_save_preview - After Preview ' ,162);
2016 exception when ma_rpaerror then raise;
2017 when others then raise ma_rpaerror;
2018 end;
2019
2020 -------commit;
2021 ---------------------Starting Final
2022 ---Recent Modification - Group box id must be commited in order to get call work flow
2023
2024 hr_utility.set_location(l_proc,163);
2025 -- Employee Data
2026
2027 get_pa_request_id_ovn
2028 ( p_mass_award_id => p_mass_award_id,
2029 p_effective_date => p_effective_date,
2030 p_person_id => p_person_id,
2031 p_pa_request_id => l_pa_request_id,
2032 p_pa_notification_id => l_pa_notification_id,
2033 p_rpa_type => l_rpa_type,
2034 p_mass_action_sel_flag => l_mass_action_select_flag,
2035 --Bug#3804067 Added mass action comments
2036 p_mass_action_comments => l_mass_action_comments,
2037 p_object_version_number => l_object_version_number);
2038
2039 ------ Modified in 115.9
2043 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,165);
2040 l_pa_request_rec.mass_action_comments := l_mass_action_comments;
2041 l_pa_request_rec.object_version_number := l_object_version_number;
2042 l_pa_request_rec.pa_request_id := l_pa_request_id;
2044 hr_utility.set_location('PA OVN ' || to_char(l_pa_request_rec.object_version_number),165);
2045
2046 -------
2047 if l_rpa_type is null then l_rpa_type := 'A'; end if;
2048
2049 IF p_action_type = 'FINAL' then
2050 IF l_pa_notification_id is null and l_rpa_type = 'A' and l_mass_action_select_flag <> 'N' THEN
2051
2052 if l_1_pa_routing_history_id is null then
2053 l_1_pa_routing_history_id := l_i_pa_routing_history_id;
2054 end if;
2055 if l_1_prh_object_version_number is null then
2056 l_1_prh_object_version_number := l_i_prh_object_version_number;
2057 end if;
2058
2059 begin
2060 hr_utility.set_location('Getting Group Box ' || l_proc,170);
2061
2062 ghr_mass_actions_pkg.get_personnel_off_groupbox
2063 (p_position_id => p_position_id
2064 ,p_effective_date => p_effective_date
2065 ,p_groupbox_id => l_groupbox_id
2066 ,p_routing_group_id => l_routing_group_id );
2067 hr_utility.set_location('Getting Group Box Success ' || l_proc,170);
2068 hr_utility.set_location('Routing Group Id ' || to_char(l_routing_group_id),170);
2069 hr_utility.set_location('Group Box Id' || to_char(l_groupbox_id),170);
2070
2071 exception
2072 WHEN OTHERS THEN
2073 hr_utility.set_location('Getting Group Box Failure ' || l_proc,170);
2074 l_log_text := 'Error in POI groupbox ' ||' Sql error : '||sqlerrm(sqlcode);
2075
2076 -- Call ghr_par_upd.upd here
2077
2078 raise groupboxerr;
2079 end;
2080
2081 -- Call ghr_prh_upd.upd here
2082 for routing_history_id in c_routing_history loop
2083 l_pa_routing_history_id := routing_history_id.pa_routing_history_id;
2084 l_prh_object_version_number := routing_history_id.object_version_number;
2085 exit;
2086 end loop;
2087
2088 hr_utility.set_location('Updating prh ' || l_proc,175);
2089 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,175);
2090 hr_utility.set_location('PA OVN ' || to_char(l_pa_request_rec.object_version_number),175);
2091 hr_utility.set_location('RHVN ' || to_char(l_prh_object_version_number),176);
2092 hr_utility.set_location('RHID ' || to_char(l_pa_routing_history_id),177);
2093
2094 ghr_prh_upd.upd(
2095 p_pa_routing_history_id => l_pa_routing_history_id,
2096 p_groupbox_id => l_groupbox_id,
2097 p_object_version_number => l_prh_object_version_number);
2098
2099 hr_utility.set_location('Updating par ' || l_proc,180);
2100
2101 -- Call ghr_par_upd.upd here
2102 ghr_par_upd.upd(
2103 p_pa_request_id => l_pa_request_rec.pa_request_id,
2104 p_routing_group_id => l_routing_group_id,
2105 p_object_version_number => l_pa_request_rec.object_version_number);
2106
2107 -- Bug 3376761
2108 -- check to see if the award amount is within 25% of basic pay
2109 -- other wise raise award percentage exception.
2110
2111 l_from_basic_pay_pa :=
2112 ghr_pay_calc.convert_amount(l_pa_request_rec.from_basic_pay
2113 ,l_pa_request_rec.from_pay_basis,'PA');
2114
2115
2116 commit;
2117 hr_utility.set_location('commit(GB) build_rpa_save_preview ' || l_proc,52);
2118 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,52);
2119 hr_utility.set_location('PA OVN ' || to_char(l_pa_request_rec.object_version_number),52);
2120
2121 ---- Recent Modification End;
2122
2123 l_savepoint := 'F';
2124 savepoint build_rpa_save_final;
2125 hr_utility.set_location('savepoint build_rpa_save_final ' || l_proc,52);
2126 hr_utility.set_location(l_proc,52);
2127 hr_utility.set_location('Bef Check Award Amt'||l_pa_request_rec.award_amount ,34);
2128 /*
2129 IF l_pa_request_rec.award_amount > ROUND(l_from_basic_pay_pa * 0.25 , 0) THEN
2130 RAISE ma_awdpererr;
2131 END IF ;
2132 */
2133 --Pradeep Commented the above and added the below for bug 3934195
2134 get_award_details
2135 ( p_mass_award_id => p_mass_award_id,
2136 p_rpa_type => l_rpa_type,
2137 p_effective_date => p_effective_date,
2138 p_person_id => null,
2139 p_award_amount => l_d_award_amount,
2140 p_award_uom => l_d_award_uom,
2141 p_award_percentage => l_d_award_percentage,
2142 p_award_agency => l_dummy,
2143 p_award_type => l_dummy,
2144 p_group_award => l_dummy,
2145 p_tangible_benefit_dollars => l_dummy,
2146 p_date_award_earned => l_dummy,
2147 p_appropriation_code => l_dummy);
2148
2149 l_error_flag := FALSE;
2150 check_award_amount (p_noa_code => l_pa_request_rec.first_noa_code,
2151 p_effective_date => l_pa_request_rec.effective_date,
2152 p_award_amount => l_d_award_amount,
2153 p_from_pay_plan => l_pa_request_rec.from_pay_plan,
2154 p_from_basic_pay_pa => l_from_basic_pay_pa,
2155 p_to_position_id => l_pa_request_rec.to_position_id,
2156 p_comments => l_comments,
2157 p_error_flg => l_error_flag
2158 );
2162
2159 IF l_error_flag THEN
2160 p_maxcheck := 1;
2161 END IF;
2163 IF ( l_error_flag
2164 AND NVL(l_mass_action_select_flag,'Y') <> 'N' ) THEN
2165 l_mass_action_select_flag := 'N';
2166 RAISE ma_awdpererr;
2167 END IF ;
2168
2169 -- End of Bug 3376761
2170
2171 get_award_lac
2172 (p_mass_award_id => p_mass_award_id,
2173 p_rpa_type => 'TA',
2174 p_effective_date => p_effective_date,
2175 p_first_lac1_record => l_lac1,
2176 p_first_lac2_record => l_lac2
2177 );
2178 l_pa_request_rec.first_action_la_code1 := l_lac1.first_action_la_code1;
2179 l_pa_request_rec.first_action_la_desc1 := l_lac1.first_action_la_desc1;
2180 l_pa_request_rec.first_lac1_information1 := l_lac1.first_lac1_information1;
2181 l_pa_request_rec.first_lac1_information2 := l_lac1.first_lac1_information2;
2182 l_pa_request_rec.first_lac1_information3 := l_lac1.first_lac1_information3;
2183 l_pa_request_rec.first_lac1_information4 := l_lac1.first_lac1_information4;
2184 l_pa_request_rec.first_lac1_information5 := l_lac1.first_lac1_information5;
2185
2186 l_pa_request_rec.first_action_la_code2 := l_lac2.first_action_la_code2;
2187 l_pa_request_rec.first_action_la_desc2 := l_lac2.first_action_la_desc2;
2188 l_pa_request_rec.first_lac2_information1 := l_lac2.first_lac2_information1;
2189 l_pa_request_rec.first_lac2_information2 := l_lac2.first_lac2_information2;
2190 l_pa_request_rec.first_lac2_information3 := l_lac2.first_lac2_information3;
2191 l_pa_request_rec.first_lac2_information4 := l_lac2.first_lac2_information4;
2192 l_pa_request_rec.first_lac2_information5 := l_lac2.first_lac2_information5;
2193
2194 -- First NOA Code has insertion values. (For only 879 and 885 NOAC's)
2195
2196 ghr_mass_actions_pkg.replace_insertion_values
2197 (p_desc => l_pa_request_rec.first_noa_desc,
2198 p_information1 => l_pa_request_rec.first_noa_information1,
2199 -- Bug#3648118 Passed the local variable as OUT parameter
2200 -- because passing l_pa_request_rec.first_noa_desc as OUT parameter
2201 -- is making the variable as NULL.
2202 p_desc_out => l_first_noa_desc
2203 );
2204
2205 l_pa_request_rec.first_noa_desc := l_first_noa_desc;
2206 -- Bug#3648118 Changes completed.
2207 hr_utility.set_location(l_proc,55);
2208 ghr_pa_requests_pkg.get_SF52_person_ddf_details
2209 (p_person_id => l_pa_request_rec.person_id,
2210 p_date_effective => l_pa_request_rec.effective_date,
2211 p_citizenship => l_pa_request_rec.citizenship,
2212 p_veterans_preference => l_pa_request_rec.veterans_preference,
2213 p_veterans_pref_for_rif => l_pa_request_rec.veterans_pref_for_rif,
2214 p_veterans_status => l_pa_request_rec.veterans_status,
2215 p_scd_leave => l_scd_leave
2216 );
2217
2218 -- populate service comp date
2219 hr_utility.set_location(l_proc,60);
2220 l_pa_request_rec.service_comp_date := fnd_date.canonical_to_date(l_scd_leave);
2221
2222 -- get education details
2223 hr_utility.set_location(l_proc,70);
2224 ghr_api.return_education_Details
2225 (p_person_id => l_pa_request_rec.person_id,
2226 p_effective_date => l_pa_request_rec.effective_date,
2227 p_education_level => l_pa_request_rec.education_level,
2228 p_academic_discipline => l_pa_request_rec.academic_discipline,
2229 p_year_degree_attained => l_pa_request_rec.year_degree_attained
2230 );
2231
2232 hr_utility.set_location(l_proc,80);
2233 ghr_pa_requests_pkg.get_SF52_asg_ddf_details
2234 (p_assignment_id => l_pa_request_rec.employee_assignment_id
2235 ,p_date_effective => l_pa_request_rec.effective_date
2236 ,p_tenure => l_pa_request_rec.tenure
2237 ,p_annuitant_indicator => l_pa_request_rec.annuitant_indicator
2238 ,p_pay_rate_determinant => l_dummy
2239 ,p_work_schedule => l_pa_request_rec.work_schedule
2240 ,p_part_time_hours => l_pa_request_rec.part_time_hours
2241 );
2242
2243 -- Annuitant_indicator
2244 l_pa_request_rec.annuitant_indicator_desc := ghr_pa_requests_pkg.get_lookup_meaning
2245 (800
2246 ,'GHR_US_ANNUITANT_INDICATOR'
2247 ,l_pa_request_rec.annuitant_indicator
2248 );
2249
2250 --WORK_SCHEDULE
2251 l_pa_request_rec.work_schedule_desc := ghr_pa_requests_pkg.get_lookup_meaning
2252 (800
2253 ,'GHR_US_WORK_SCHEDULE'
2254 ,l_pa_request_rec.work_schedule
2255 );
2256
2257 -- get fegli,retirement_plan
2258
2259 hr_utility.set_location(l_proc,90);
2260 ghr_api.retrieve_element_entry_value
2261 (p_element_name => 'FEGLI'
2262 ,p_input_value_name => 'FEGLI'
2263 ,p_assignment_id => l_pa_request_rec.employee_assignment_id
2264 ,p_effective_date => l_pa_request_rec.effective_date
2265 ,p_value => l_pa_request_rec.fegli
2266 ,p_multiple_error_flag => l_multiple_error_flag
2267 );
2268
2269 l_pa_request_rec.fegli_desc := ghr_pa_requests_pkg.get_lookup_meaning
2270 (800
2274
2271 ,'GHR_US_FEGLI'
2272 ,l_pa_request_rec.fegli
2273 );
2275
2276 --retirement_plan
2277 hr_utility.set_location(l_proc,100);
2278 ghr_api.retrieve_element_entry_value
2279 (p_element_name => 'Retirement Plan'
2280 ,p_input_value_name => 'Plan'
2281 ,p_assignment_id => l_pa_request_rec.employee_assignment_id
2282 ,p_effective_date => l_pa_request_rec.effective_date
2283 ,p_value => l_pa_request_rec.retirement_plan
2284 ,p_multiple_error_flag => l_multiple_error_flag
2285 );
2286
2287 l_pa_request_rec.retirement_plan_desc := ghr_pa_requests_pkg.get_lookup_meaning
2288 (800
2289 ,'GHR_US_RETIREMENT_PLAN'
2290 ,l_pa_request_rec.retirement_plan
2291 );
2292
2293 -- Descriptions for the codes passed in
2294 hr_utility.set_location(l_proc,110);
2295 ghr_mass_actions_pkg.get_personnel_officer_name
2296 (p_personnel_office_id => l_pa_request_rec.personnel_office_id,
2297 p_person_full_name => l_personnel_officer_name,
2298 p_approving_off_work_title => l_approving_off_work_title);
2299
2300 l_pa_request_rec.approving_official_full_name := l_personnel_officer_name;
2301 l_pa_request_rec.approving_official_work_title := l_approving_off_work_title;
2302
2303 l_approval_date := sysdate;
2304 l_1_approval_status := 'APPROVE';
2305 l_pa_request_rec.request_number := l_pa_request_num_prefix ||
2306 to_char(l_pa_request_rec.pa_request_id);
2307
2308
2309 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,110);
2310 hr_utility.set_location('PA OVN ' || to_char(l_pa_request_rec.object_version_number),110);
2311 -----------Created marpa_process procedure Call
2312 ---- To print NPA correctly ..bug..2356373.
2313
2314 l_pa_request_rec.sf50_approving_ofcl_full_name := l_personnel_officer_name;
2315 l_pa_request_rec.sf50_approval_date := sysdate;
2316 l_pa_request_rec.sf50_approving_ofcl_work_title := l_approving_off_work_title;
2317
2318 begin
2319 marpa_process
2320 (
2321 p_mass_award_id => p_mass_award_id
2322 ,p_action_type => p_action_type
2323 ,p_rpa_type => p_rpa_type
2324 ,p_effective_date => p_effective_date
2325 ,p_person_id => p_person_id
2326 ,p_pa_request_rec => l_pa_request_rec
2327 ,p_log_text => l_log_text
2328 ,p_maxcheck => p_maxcheck
2329 );
2330
2331 exception when ma_rpaerror then raise;
2332 when others then raise ma_rpaerror;
2333 end;
2334
2335 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,165);
2336 hr_utility.set_location('PA OVN ' || to_char(l_pa_request_rec.object_version_number),165);
2337
2338 hr_utility.set_location('Creating Remarks ' || l_proc,165);
2339 create_remarks
2340 (p_mass_award_id => p_mass_award_id
2341 ,p_rpa_type => 'TA'
2342 ,p_effective_date => p_effective_date
2343 ,p_pa_request_id => l_pa_request_rec.pa_request_id
2344 );
2345 hr_utility.set_location('Creating Remarks Over ' || l_proc,165);
2346
2347
2348 if p_effective_date > sysdate then
2349 l_rpa_action := 'FUTURE_ACTION';
2350 else
2351 l_rpa_action := 'UPDATE_HR';
2352 end if;
2353
2354 hr_utility.set_location('Action before last ghr_par_upd.upd ' || l_rpa_action || ' ' || l_proc,175);
2355
2356 ---Bug 2356373
2357 ghr_par_upd.upd
2358 (p_pa_request_id => l_pa_request_rec.pa_request_id,
2359 p_object_version_number => l_pa_request_rec.object_version_number,
2360 p_personnel_office_id => l_pa_request_rec.personnel_office_id,
2361 p_employee_dept_or_agency => l_pa_request_rec.employee_dept_or_agency,
2362 p_to_office_symbol => l_pa_request_rec.to_office_symbol,
2363 p_sf50_approving_ofcl_full_nam => l_personnel_officer_name,
2364 p_sf50_approval_date => sysdate ,
2365 p_sf50_approving_ofcl_work_tit => l_approving_off_work_title
2366 );
2367 ---Bug 2356373 End
2368
2369 hr_utility.set_location('Action after last ghr_par_upd.upd ' || l_rpa_action || ' ' || l_proc,175);
2370
2371 hr_utility.set_location('Action before ' || l_rpa_action || ' ' || l_proc,185);
2372 hr_utility.set_location('PA REQ ID ' || to_char(l_pa_request_rec.pa_request_id) ,185);
2373 hr_utility.set_location('PA OVN ' || to_char(l_pa_request_rec.object_version_number),185);
2374
2375 hr_utility.set_location('employee_dept_or_agency ' || l_pa_request_rec.employee_dept_or_agency ,185);
2376 hr_utility.set_location('POI ID ' || l_pa_request_rec.personnel_office_id ,185);
2377 ------
2378
2379 ----Update Sf52 for Personnel Action
2380 begin
2381 ghr_sf52_api.update_sf52
2382 (
2383 p_pa_request_id => l_pa_request_rec.pa_request_id,
2384 p_par_object_version_number => l_pa_request_rec.object_version_number,
2385 ----Bug 2348413
2386 p_effective_date => l_pa_request_rec.effective_date,
2387 p_employee_assignment_id => l_pa_request_rec.employee_assignment_id,
2391 p_u_action_taken => l_rpa_action,
2388 p_noa_family_code => l_pa_request_rec.noa_family_code,
2389 ----Bug 2348413
2390 p_routing_group_id => l_routing_group_id,
2392 p_u_prh_object_version_number => l_u_prh_object_version_number ,
2393 p_first_noa_id => l_pa_request_rec.first_noa_id, -- Bug#2740882
2394 p_i_pa_routing_history_id => l_i_pa_routing_history_id,
2395 p_i_prh_object_version_number => l_i_prh_object_version_number);
2396 exception
2397 WHEN OTHERS THEN
2398 hr_utility.set_location(sqlerrm(sqlcode),1);
2399 hr_utility.set_location('Update sf52 final Failure ' || l_proc,190);
2400 l_log_text := 'Error in Update sf52 final ' ||' Sql error : '||sqlerrm(sqlcode);
2401 raise rpaerror;
2402 end;
2403
2404 hr_utility.set_location('Action after ' || l_rpa_action || ' ' || l_proc,200);
2405 p_status := 'SUCCESS';
2406 p_retcode := 0;
2407 l_log_text := 'Last Name: '|| l_pa_request_rec.employee_last_name ||
2408 'SSN: ' || l_pa_request_rec.employee_national_identifier ||
2409 'Mass Award:'|| to_char(p_mass_award_id)||
2410 'SF52 Successfully completed';
2411 p_errbuf := l_log_text;
2412
2413 ghr_wgi_pkg.create_ghr_errorlog(
2414 p_program_name => g_log_name,
2415 p_message_name => substr(l_maw_name || '-' || 'Success',1,30),
2416 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' || l_log_text,1,2000),
2417 p_log_date => sysdate);
2418
2419
2420
2421 ELSE
2422 IF l_rpa_type = 'M' then
2423 p_status := 'MANUAL';
2424 p_retcode := 0;
2425 hr_utility.set_location('Manual rpa_type ' || l_proc,200);
2426 ELSIF l_mass_action_select_flag = 'N'
2427 and l_status = 'DESELECTED PRG:' THEN
2428 hr_utility.set_location('Deselected by programatically' || l_proc,200);
2429 p_errbuf := 'DESELECTED PRG:';
2430 p_retcode := 0;
2431 ELSIF l_mass_action_select_flag = 'N' then
2432 hr_utility.set_location('Deselected by user ' || l_proc,200);
2433 p_status := 'DESELECTED';
2434 p_retcode := 0;
2435 ELSE
2436 hr_utility.set_location('Already Processed ' || l_proc,200);
2437 p_status := 'PROCESSED';
2438 p_retcode := 0;
2439 END IF;
2440 END IF; ----- For l_pa_notification_id is null and rpa_type = 'A'
2441 END IF; ---- For p_action = 'FINAL'
2442
2443 Exception
2444 When groupboxerr then
2445 rollback to build_rpa_save_preview;
2446 hr_utility.set_location('rollback grp build_rpa_save_preview ' || l_proc,1);
2447
2448 -- Call ghr_par_upd.upd here
2449 begin
2450
2451 get_pa_request_id_ovn
2452 ( p_mass_award_id => p_mass_award_id,
2453 p_effective_date => p_effective_date,
2454 p_person_id => p_person_id,
2455 p_pa_request_id => l_pa_request_id,
2456 p_pa_notification_id => l_pa_notification_id,
2457 p_rpa_type => l_rpa_type,
2458 p_mass_action_sel_flag => l_mass_action_select_flag,
2459 --Bug#3804067 Added mass action comments
2460 p_mass_action_comments => l_mass_action_comments,
2461 p_object_version_number => l_object_version_number);
2462
2463 ghr_par_upd.upd(
2464 p_pa_request_id => l_pa_request_id,
2465 p_mass_action_eligible_flag => 'Y',
2466 p_mass_action_select_flag => 'N',
2467 p_mass_action_comments => 'PRG: Programatically Deselected',
2468 p_object_version_number => l_object_version_number);
2469 exception when others then
2470 l_log_text := substr(l_log_text || ' , Failed in Deselecting, ' ||' Sql error : '||sqlerrm(sqlcode),1,2000);
2471 end;
2472
2473 hr_utility.set_location('Error occured in group box ' || l_proc , 1);
2474 IF l_log_text is NULL THEN
2475 l_log_text := 'Error while creating / Updating the PA Request Rec.';
2476 END IF;
2477
2478 p_retcode := 1;
2479 p_status := 'GROUPBOX';
2480 p_errbuf := substr(l_log_text || 'Details in GHR_PROCESS_LOG',1,2000);
2481
2482 hr_utility.set_location('before creating entry in log file',2);
2483 -- Bug#3718167 Added SSN in the log text below
2484 l_log_text := substr(',( ' || l_pa_request_rec.employee_last_name || ', ' ||
2485 l_pa_request_rec.employee_first_name ||' '||l_pa_request_rec.employee_middle_names||
2486 '; SSN: '||l_pa_request_rec.employee_national_identifier||
2487 ') ' || l_log_text,1,2000);
2488
2489 ghr_wgi_pkg.create_ghr_errorlog(
2490 p_program_name => g_log_name,
2491 p_message_name => substr(l_maw_name || '-' || 'GB Err',1,30),
2492 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' ||l_log_text,1,2000),
2493 p_log_date => sysdate);
2494
2495 hr_utility.set_location('Groupbox Error ',2);
2496 hr_utility.set_location('created entry in log file',2);
2497 commit;
2498
2499 When rpaerror then
2500
2501 if l_savepoint = 'F' THEN
2502 rollback to build_rpa_save_final;
2503 hr_utility.set_location('rollback rpa build_rpa_save_final ' || l_proc,1);
2504 else
2505 rollback to build_rpa_save_preview;
2506 hr_utility.set_location('rollback rpa build_rpa_save_preview ' || l_proc,1);
2507 end if;
2508
2509 hr_utility.set_location('Error occured Final Sf52 ' || l_proc , 1);
2510 IF l_log_text is NULL THEN
2514 p_retcode := 1;
2511 l_log_text := 'Error while creating / Updating the PA Request Rec.';
2512 END IF;
2513
2515 p_status := 'FAILURE';
2516 p_errbuf := substr(l_log_text || 'Details in GHR_PROCESS_LOG',1,2000);
2517
2518 hr_utility.set_location('before creating entry in log file',2);
2519 -- Bug#3718167 Added SSN in the following log text
2520 l_log_text := substr(',( ' || l_pa_request_rec.employee_last_name || ', ' ||
2521 l_pa_request_rec.employee_first_name ||' '||l_pa_request_rec.employee_middle_names||
2522 '; SSN: '||l_pa_request_rec.employee_national_identifier||
2523 ') ' || l_log_text ,1,2000);
2524 ghr_wgi_pkg.create_ghr_errorlog(
2525 p_program_name => g_log_name,
2526 p_message_name => substr(l_maw_name || '-' || 'RPA Err',1,30),
2527 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' ||l_log_text,1,2000),
2528 p_log_date => sysdate);
2529
2530 hr_utility.set_location('FINAL SF52 Error ',2);
2531 hr_utility.set_location('created entry in log file',2);
2532
2533 hr_utility.set_location('Calling workflow begin entry ',3);
2534 mass_awards_error_handling
2535 ( p_pa_request_id => l_pa_request_rec.pa_request_id,
2536 p_object_version_number => l_pa_request_rec.object_version_number,
2537 p_error => l_log_text,
2538 p_result => l_result);
2539 if l_result then
2540 l_log_text := 'Error while routing to group box in call workflow ';
2541 p_status := 'OTHER';
2542 p_retcode := 1;
2543 hr_utility.set_location('before creating entry in log file',4);
2544 -- Bug#3718167 Added SSN in the following log text
2545 l_log_text := substr( ',( ' || l_pa_request_rec.employee_last_name || ', ' ||
2546 l_pa_request_rec.employee_first_name ||' '||l_pa_request_rec.employee_middle_names||
2547 '; SSN: '||l_pa_request_rec.employee_national_identifier||
2548 ') ' || l_log_text,1,2000);
2549 ghr_wgi_pkg.create_ghr_errorlog(
2550 p_program_name => g_log_name,
2551 p_message_name => substr(l_maw_name || '-' || 'RPA WF',1,30),
2552 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' || l_log_text,1,2000),
2553 p_log_date => sysdate);
2554
2555 hr_utility.set_location('FINAL SF52 Error - Call Work flow ',4);
2556 hr_utility.set_location('created entry in log file',4);
2557 end if;
2558 hr_utility.set_location('Calling workflow end entry ',3);
2559
2560 commit;
2561
2562 When ma_rpaerror then
2563
2564 if l_savepoint = 'F' THEN
2565 rollback to build_rpa_save_final;
2566 hr_utility.set_location('rollback marpa build_rpa_save_final ' || l_proc,1);
2567 else
2568 rollback to build_rpa_save_preview;
2569 hr_utility.set_location('rollback marpa build_rpa_save_preview ' || l_proc,1);
2570 end if;
2571
2572 hr_utility.set_location('Error occured in ' || l_proc , 1);
2573 IF g_log_text is NOT NULL then
2574 l_log_text := g_log_text;
2575 END IF;
2576 IF l_log_text is NULL THEN
2577 l_log_text := 'Error while creating / Updating the PA Request Rec.';
2578 END IF;
2579
2580 p_retcode := 1;
2581 p_status := 'FAILURE';
2582 p_errbuf := substr(l_log_text || 'Details in GHR_PROCESS_LOG',1,2000);
2583
2584 hr_utility.set_location('before creating entry in log file',2);
2585 -- Bug#3718167 Added SSN in the following log text
2586 l_log_text := substr( ',( ' || l_pa_request_rec.employee_last_name || ', ' ||
2587 l_pa_request_rec.employee_first_name ||' '||l_pa_request_rec.employee_middle_names||
2588 '; SSN: '||l_pa_request_rec.employee_national_identifier||
2589 ') ' || l_log_text,1,2000);
2590 ghr_wgi_pkg.create_ghr_errorlog(
2591 p_program_name => g_log_name,
2592 p_message_name => substr(l_maw_name || '-' || 'RPA MA',1,30) ,
2593 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' || l_log_text,1,2000),
2594 p_log_date => sysdate);
2595
2596
2597 hr_utility.set_location('Error Others ',2);
2598 hr_utility.set_location('created entry in log file',2);
2599
2600 commit;
2601
2602 -- Bug 3376761
2603 -- If Award amount exceeds 25% of annual basic pay
2604
2605 When ma_awdpererr then
2606
2607
2608 rollback to build_rpa_save_final;
2609
2610 hr_utility.set_location('rollback marpa build_rpa_save_final ' || l_proc,1);
2611
2612 hr_utility.set_location('Error occured in ' || l_proc , 1);
2613
2614 --l_log_text := 'APP-GHR-38611 - Award Percentage cannot be greater than 25% of total annual basic pay.';
2615 l_log_text := l_comments;
2616
2617 p_retcode := 1;
2618 p_status := 'FAILURE';
2619 p_errbuf := substr(l_log_text || 'Details in GHR_PROCESS_LOG',1,2000);
2620
2621 hr_utility.set_location('before creating entry in log file',2);
2622 -- Bug#3718167 Added SSN in the following log text
2623 l_log_text := substr(',( ' || l_pa_request_rec.employee_last_name || ', ' ||
2624 l_pa_request_rec.employee_first_name ||' '||l_pa_request_rec.employee_middle_names||
2625 '; SSN: '||l_pa_request_rec.employee_national_identifier||
2626 ') ' || l_log_text ,1,2000);
2627
2628 ghr_wgi_pkg.create_ghr_errorlog(
2629 p_program_name => g_log_name,
2633
2630 p_message_name => substr(l_maw_name || '-' || 'RPA MA',1,60),
2631 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' ||l_log_text,1,2000),
2632 p_log_date => sysdate);
2634 hr_utility.set_location('Award percentage Error ',2);
2635 hr_utility.set_location('created entry in log file',2);
2636
2637 hr_utility.set_location('Calling workflow begin entry ',3);
2638 mass_awards_error_handling
2639 ( p_pa_request_id => l_pa_request_rec.pa_request_id,
2640 p_object_version_number => l_pa_request_rec.object_version_number,
2641 p_error => l_log_text,
2642 p_result => l_result);
2643 if l_result then
2644 l_log_text := 'Error while routing to group box in call workflow ';
2645 p_status := 'OTHER';
2646 p_retcode := 1;
2647 hr_utility.set_location('before creating entry in log file',4);
2648 -- Bug#3718167 Added SSN in the following log text
2649 l_log_text := substr( ',( ' || l_pa_request_rec.employee_last_name || ', ' ||
2650 l_pa_request_rec.employee_first_name || ' '||l_pa_request_rec.employee_middle_names||
2651 '; SSN: '||l_pa_request_rec.employee_national_identifier||
2652 ') ' || l_log_text,1,2000);
2653 ghr_wgi_pkg.create_ghr_errorlog(
2654 p_program_name => g_log_name,
2655 p_message_name => substr(l_maw_name || '-' || 'RPA WF',1,30),
2656 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' || l_log_text,1,2000),
2657 p_log_date => sysdate);
2658
2659 hr_utility.set_location('Award percentage Error - Call Work flow ',4);
2660 hr_utility.set_location('created entry in log file',4);
2661 end if;
2662 hr_utility.set_location('Calling workflow end entry ',3);
2663
2664 commit;
2665
2666 -- End of Bug 3376761
2667
2668 When others then
2669
2670 if l_savepoint = 'F' THEN
2671 rollback to build_rpa_save_final;
2672 hr_utility.set_location('rollback others build_rpa_save_final ' || l_proc,1);
2673 else
2674 rollback to build_rpa_save_preview;
2675 hr_utility.set_location('rollback others build_rpa_save_preview ' || l_proc,1);
2676 end if;
2677
2678 hr_utility.set_location('Error occured in ' || l_proc , 1);
2679 IF l_log_text is NULL THEN
2680 l_log_text := 'Error while creating / Updating the PA Request Rec.';
2681 END IF;
2682
2683 p_retcode := 1;
2684 p_status := 'FAILURE';
2685 p_errbuf := substr(l_log_text || 'Details in GHR_PROCESS_LOG',1,2000);
2686
2687 hr_utility.set_location('before creating entry in log file',2);
2688 -- Bug#3718167 Added SSN to the following log text
2689 l_log_text := substr( ',( ' || l_pa_request_rec.employee_last_name || ', ' ||
2690 l_pa_request_rec.employee_first_name ||' '||l_pa_request_rec.employee_middle_names||
2691 '; SSN: '||l_pa_request_rec.employee_national_identifier||
2692 ') ' || l_log_text,1,2000);
2693 ghr_wgi_pkg.create_ghr_errorlog(
2694 p_program_name => g_log_name,
2695 p_message_name => substr(l_maw_name || '-' || 'RPA Oth',1,30) ,
2696 p_log_text => substr('Person id : ' || to_char(p_person_id) || ' ' || l_log_text,1,2000),
2697 p_log_date => sysdate);
2698
2699
2700 hr_utility.set_location('Error Others ',2);
2701 hr_utility.set_location('created entry in log file',2);
2702
2703 commit;
2704
2705 end build_rpa_for_mass_awards;
2706
2707
2708 PROCEDURE refresh_award_details
2709 (
2710 p_mass_award_id in ghr_mass_awards.mass_award_id%TYPE,
2711 p_rpa_type in ghr_pa_requests.rpa_type%TYPE,
2712 p_effective_date in date,
2713 p_person_id in per_people_f.person_id%TYPE,
2714 p_pa_request_id in ghr_pa_requests.pa_request_id%type
2715 )
2716
2717 IS
2718
2719 l_proc varchar2(72) := g_package || 'refresh_award_details';
2720
2721 l_pa_request_extra_info_id number;
2722 l_pa_request_extra_info_sh_id number;
2723 l_pa_object_version_number number;
2724 l_dummy varchar2(30);
2725 --l_information_type varchar2(40) := 'GHR_US_PAR_AWARDS_BONUS';
2726
2727
2728 l_t_award_amount ghr_pa_requests.award_amount%type;
2729 l_t_award_uom ghr_pa_requests.award_uom%type;
2730 l_t_award_percentage ghr_pa_requests.award_percentage%type;
2731 l_t_award_agency varchar2(150);
2732 l_t_award_type varchar2(150);
2733 l_t_group_award varchar2(150);
2734 l_t_tbd varchar2(150);
2735 l_t_date_award_earn varchar2(150);
2736 l_t_appropriation_code varchar2(150);
2737
2738 l_d_award_amount ghr_pa_requests.award_amount%type;
2739 l_d_award_uom ghr_pa_requests.award_uom%type;
2740 l_d_award_percentage ghr_pa_requests.award_percentage%type;
2741 l_d_award_agency varchar2(150);
2742 l_d_award_type varchar2(150);
2743 l_d_group_award varchar2(150);
2744 l_d_tbd varchar2(150);
2745 l_d_date_award_earn varchar2(150);
2746 l_d_appropriation_code varchar2(150);
2747
2748 l_s_award_amount ghr_pa_requests.award_amount%type;
2749 l_s_award_uom ghr_pa_requests.award_uom%type;
2753 l_s_group_award varchar2(150);
2750 l_s_award_percentage ghr_pa_requests.award_percentage%type;
2751 l_s_award_agency varchar2(150);
2752 l_s_award_type varchar2(150);
2754 l_s_tbd varchar2(150);
2755 l_s_date_award_earn varchar2(150);
2756 l_s_appropriation_code varchar2(150);
2757
2758 CURSOR cur_rpa_ei (p_pa_request_id number) is
2759 SELECT pa_request_extra_info_id,
2760 object_version_number
2761 FROM ghr_pa_request_extra_info
2762 WHERE information_type = l_information_type
2763 AND pa_request_id = p_pa_request_id;
2764
2765 CURSOR cur_rpa_ei_shadow (p_pa_request_id number) is
2766 SELECT pa_request_extra_info_id
2767 FROM ghr_pa_request_ei_shadow
2768 WHERE information_type = l_information_type
2769 AND pa_request_id = p_pa_request_id;
2770
2771 BEGIN
2772
2773 hr_utility.set_location('Entering ' || l_proc,5);
2774 FOR cur_rpa_ei_rec in cur_rpa_ei(p_pa_request_id)
2775 LOOP
2776 hr_utility.set_location( l_proc,10);
2777 l_pa_request_extra_info_id := cur_rpa_ei_rec.pa_request_extra_info_id;
2778 l_pa_object_version_number := cur_rpa_ei_rec.object_version_number;
2779 exit;
2780 END LOOP;
2781
2782 FOR cur_rpa_ei_shadow_rec in cur_rpa_ei_shadow(p_pa_request_id)
2783 LOOP
2784 hr_utility.set_location( l_proc,10);
2785 l_pa_request_extra_info_sh_id := cur_rpa_ei_shadow_rec.pa_request_extra_info_id;
2786 exit;
2787 END LOOP;
2788
2789 hr_utility.set_location(l_proc,15);
2790 ----- Get Award details from the template and also extra information
2791 get_award_details
2792 ( p_mass_award_id => p_mass_award_id,
2793 p_rpa_type => 'TA',
2794 p_effective_date => p_effective_date,
2795 p_person_id => null,
2796 p_award_amount => l_dummy,
2797 p_award_uom => l_dummy,
2798 p_award_percentage => l_dummy,
2799 p_award_agency => l_t_award_agency,
2800 p_award_type => l_t_award_type,
2801 p_group_award => l_t_group_award,
2802 p_tangible_benefit_dollars => l_t_tbd,
2803 p_date_award_earned => l_t_date_award_earn,
2804 p_appropriation_code => l_t_appropriation_code);
2805
2806 hr_utility.set_location('p_award_amount - l_dummy' || l_dummy,20);
2807 hr_utility.set_location(l_proc,20);
2808 ----- Get Award details for the employee existing in the database
2809 get_award_details
2810 ( p_mass_award_id => p_mass_award_id,
2811 p_rpa_type => p_rpa_type,
2812 p_effective_date => p_effective_date,
2813 p_person_id => p_person_id,
2814 p_award_amount => l_dummy,
2815 p_award_uom => l_dummy,
2816 p_award_percentage => l_dummy,
2817 p_award_agency => l_d_award_agency,
2818 p_award_type => l_d_award_type,
2819 p_group_award => l_d_group_award,
2820 p_tangible_benefit_dollars => l_d_tbd,
2821 p_date_award_earned => l_d_date_award_earn,
2822 p_appropriation_code => l_d_appropriation_code);
2823
2824 hr_utility.set_location('p_award_amountD - l_dummy' || l_dummy,20);
2825
2826 hr_utility.set_location(l_proc,25);
2827 ----- Get Award details from the shadow
2828 get_award_details_shadow
2829 ( p_pa_request_id => p_pa_request_id,
2830 p_award_amount => l_dummy,
2831 p_award_uom => l_dummy,
2832 p_award_percentage => l_dummy,
2833 p_award_agency => l_s_award_agency,
2834 p_award_type => l_s_award_type,
2835 p_group_award => l_s_group_award,
2836 p_tangible_benefit_dollars => l_s_tbd,
2837 p_date_award_earned => l_s_date_award_earn,
2838 p_appropriation_code => l_s_appropriation_code);
2839 hr_utility.set_location('p_award_amountS - l_dummy' || l_dummy,20);
2840 ------------
2841 hr_utility.set_location(l_proc,30);
2842 set_ei
2843 (p_shadow => l_s_award_agency,
2844 p_template => l_t_award_agency,
2845 p_person => l_d_award_agency);
2846
2847 set_ei
2848 (p_shadow => l_s_award_type,
2849 p_template => l_t_award_type,
2850 p_person => l_d_award_type);
2851
2852 set_ei
2853 (p_shadow => l_s_group_award,
2854 p_template => l_t_group_award,
2855 p_person => l_d_group_award);
2856
2857 set_ei
2858 (p_shadow => l_s_tbd,
2859 p_template => l_t_tbd,
2860 p_person => l_d_tbd);
2861
2862 set_ei
2863 (p_shadow => l_s_date_award_earn,
2864 p_template => l_t_date_award_earn,
2865 p_person => l_d_date_award_earn);
2866
2867 set_ei
2868 (p_shadow => l_s_appropriation_code,
2869 p_template => l_t_appropriation_code,
2870 p_person => l_d_appropriation_code);
2871
2872 hr_utility.set_location(l_proc,40);
2876 (p_validate => false,
2873 IF l_pa_request_extra_info_id is null then
2874 hr_utility.set_location(l_proc,45);
2875 ghr_par_extra_info_api.create_pa_request_extra_info
2877 p_pa_request_id => p_pa_request_id,
2878 p_information_type => l_information_type,
2879 p_rei_information_category => l_information_type,
2880 p_rei_information3 => l_t_award_agency,
2881 p_rei_information4 => l_t_award_type,
2882 p_rei_information6 => l_t_group_award,
2883 p_rei_information7 => l_t_tbd,
2884 p_rei_information9 => l_t_date_award_earn,
2885 p_rei_information10 => l_t_appropriation_code,
2886 p_pa_request_extra_info_id => l_dummy,
2887 p_object_version_number => l_dummy
2888 );
2889 ELSE
2890 hr_utility.set_location(l_proc,50);
2891
2892 ghr_par_extra_info_api.update_pa_request_extra_info
2893 (p_validate => false,
2894 p_rei_information3 => l_d_award_agency,
2895 p_rei_information4 => l_d_award_type,
2896 p_rei_information6 => l_d_group_award,
2897 p_rei_information7 => l_d_tbd,
2898 p_rei_information9 => l_d_date_award_earn,
2899 p_rei_information10 => l_d_appropriation_code,
2900 p_pa_request_extra_info_id => l_pa_request_extra_info_id,
2901 p_object_version_number => l_pa_object_version_number);
2902 END IF;
2903 IF l_pa_request_extra_info_sh_id is null then
2904 insert into ghr_pa_request_ei_shadow
2905 (pa_request_extra_info_id,
2906 pa_request_id,
2907 information_type,
2908 rei_information3,
2909 rei_information4,
2910 rei_information6,
2911 rei_information7,
2912 rei_information9,
2913 rei_information10)
2914 values (ghr_pa_request_extra_info_s.nextval,
2915 p_pa_request_id,
2916 l_information_type,
2917 l_s_award_agency,
2918 l_s_award_type,
2919 l_s_group_award,
2920 l_s_tbd,
2921 l_s_date_award_earn,
2922 l_s_appropriation_code);
2923 ELSE update ghr_pa_request_ei_shadow
2924 set rei_information3 = l_s_award_agency,
2925 rei_information4 = l_s_award_type,
2926 rei_information6 = l_s_group_award,
2927 rei_information7 = l_s_tbd,
2928 rei_information9 = l_s_date_award_earn,
2929 rei_information10 = l_s_appropriation_code
2930 where pa_request_extra_info_id = l_pa_request_extra_info_sh_id;
2931 END IF;
2932
2933 EXCEPTION
2934
2935 when others then
2936 hr_utility.set_location('Error in ghr_par_extra info.create pa req'||
2937 ' Sql Err is '|| sqlerrm(sqlcode) || l_proc, 60);
2938 --raise mass_awarderror;
2939
2940 END refresh_award_details;
2941
2942 Procedure set_ei
2943 (p_shadow in out nocopy varchar2,
2944 p_template in varchar2,
2945 p_person in out nocopy varchar2,
2946 p_refresh_flag in varchar2 default 'Y')
2947 is
2948
2949 l_shadow varchar2(240);
2950 l_person varchar2(240);
2951 begin
2952
2953 l_shadow := p_shadow ;/*NOCOPY CHANGES*/
2954 l_person := p_person ;
2955
2956 If p_refresh_flag = 'Y' then
2957 hr_utility.set_location('in set ei - Y ',5);
2958 If nvl(p_person,hr_api.g_varchar2) <> nvl(p_template,hr_api.g_varchar2) and
2959 nvl(p_person,hr_api.g_varchar2) = nvl(p_shadow,hr_api.g_varchar2) then
2960 p_person := p_template;
2961 End if;
2962 Else
2963 hr_utility.set_location('in set ei - N ',6);
2964 p_person := p_template;
2965 End if;
2966 p_shadow := p_template;
2967 EXCEPTION
2968 WHEN others THEN
2969 -- Reset IN OUT parameters and set OUT parameters
2970
2971 p_shadow := l_shadow ;
2972 p_person := l_person ;
2973 RAISE;
2974
2975 End set_ei;
2976
2977 Procedure create_shadow_row ( p_rpa_data in ghr_pa_requests%rowtype) is
2978 Begin
2979 insert into ghr_pa_request_shadow(
2980 pa_request_id
2981 ,academic_discipline
2982 ,annuitant_indicator
2983 ,appropriation_code1
2984 ,appropriation_code2
2985 ,bargaining_unit_status
2986 ,citizenship
2987 ,duty_station_id
2988 ,duty_station_location_id
2989 ,education_level
2990 ,employee_date_of_birth
2991 ,employee_first_name
2992 ,employee_last_name
2993 ,employee_middle_names
2994 ,employee_national_identifier
2995 ,fegli
2996 ,flsa_category
3000 ,forwarding_country_short_name
2997 ,forwarding_address_line1
2998 ,forwarding_address_line2
2999 ,forwarding_address_line3
3001 ,forwarding_postal_code
3002 ,forwarding_region_2
3003 ,forwarding_town_or_city
3004 ,functional_class
3005 ,part_time_hours
3006 ,pay_rate_determinant
3007 ,position_occupied
3008 ,retirement_plan
3009 ,service_comp_date
3010 ,supervisory_status
3011 ,tenure
3012 ,to_ap_premium_pay_indicator
3013 ,to_auo_premium_pay_indicator
3014 ,to_occ_code
3015 ,to_position_id
3016 ,to_retention_allowance
3017 ,to_staffing_differential
3018 ,to_step_or_ratE
3019 ,to_supervisory_differential
3020 ,veterans_preference
3021 ,veterans_pref_for_riF
3022 ,veterans_status
3023 ,work_schedule
3024 ,year_degree_attained
3025 ,to_retention_allow_percentage
3026 ,to_supervisory_diff_percentage
3027 ,to_staffing_diff_percentage
3028 ,award_amount
3029 ,award_uom
3030 ,award_percentage )
3031 values (
3032 p_rpa_data.pa_request_id
3033 ,p_rpa_data.academic_discipline
3034 ,p_rpa_data.annuitant_indicator
3035 ,p_rpa_data.appropriation_code1
3036 ,p_rpa_data.appropriation_code2
3037 ,p_rpa_data.bargaining_unit_status
3038 ,p_rpa_data.citizenship
3039 ,p_rpa_data.duty_station_id
3040 ,p_rpa_data.duty_station_location_id
3041 ,p_rpa_data.education_level
3042 ,p_rpa_data.employee_date_of_birth
3043 ,p_rpa_data.employee_first_name
3044 ,p_rpa_data.employee_last_name
3045 ,p_rpa_data.employee_middle_names
3046 ,p_rpa_data.employee_national_identifier
3047 ,p_rpa_data.fegli
3048 ,p_rpa_data.flsa_category
3049 ,p_rpa_data.forwarding_address_line1
3050 ,p_rpa_data.forwarding_address_line2
3051 ,p_rpa_data.forwarding_address_line3
3052 ,p_rpa_data.forwarding_country_short_name
3053 ,p_rpa_data.forwarding_postal_code
3054 ,p_rpa_data.forwarding_region_2
3055 ,p_rpa_data.forwarding_town_or_city
3056 ,p_rpa_data.functional_class
3057 ,p_rpa_data.part_time_hours
3058 ,p_rpa_data.pay_rate_determinant
3059 ,p_rpa_data.position_occupied
3060 ,p_rpa_data.retirement_plan
3061 ,p_rpa_data.service_comp_date
3062 ,p_rpa_data.supervisory_status
3063 ,p_rpa_data.tenure
3064 ,p_rpa_data.to_ap_premium_pay_indicator
3065 ,p_rpa_data.to_auo_premium_pay_indicator
3066 ,p_rpa_data.to_occ_code
3067 ,p_rpa_data.to_position_id
3068 ,p_rpa_data.to_retention_allowance
3069 ,p_rpa_data.to_staffing_differential
3070 ,p_rpa_data.to_step_or_ratE
3071 ,p_rpa_data.to_supervisory_differential
3072 ,p_rpa_data.veterans_preference
3073 ,p_rpa_data.veterans_pref_for_riF
3074 ,p_rpa_data.veterans_status
3075 ,p_rpa_data.work_schedule
3076 ,p_rpa_data.year_degree_attained
3077 ,p_rpa_data.to_retention_allow_percentage
3078 ,p_rpa_data.to_supervisory_diff_percentage
3079 ,p_rpa_data.to_staffing_diff_percentage
3080 ,p_rpa_data.award_amount
3081 ,p_rpa_data.award_uom
3082 ,p_rpa_data.award_percentage );
3083 end create_shadow_row;
3084
3085 Procedure update_shadow_row ( p_rpa_data in ghr_pa_requests%rowtype,
3086 p_result out nocopy Boolean ) is
3087 Begin
3088 update ghr_pa_request_shadow
3089 set
3090 academic_discipline = p_rpa_data.academic_discipline
3091 ,annuitant_indicator = p_rpa_data.annuitant_indicator
3092 ,appropriation_code1 = p_rpa_data.appropriation_code1
3093 ,appropriation_code2 = p_rpa_data.appropriation_code2
3094 ,bargaining_unit_status = p_rpa_data.bargaining_unit_status
3095 ,citizenship = p_rpa_data.citizenship
3096 ,duty_station_id = p_rpa_data.duty_station_id
3097 ,duty_station_location_id = p_rpa_data.duty_station_location_id
3098 ,education_level = p_rpa_data.education_level
3099 ,employee_date_of_birth = p_rpa_data.employee_date_of_birth
3100 ,employee_first_name = p_rpa_data.employee_first_name
3101 ,employee_last_name = p_rpa_data.employee_last_name
3102 ,employee_middle_names = p_rpa_data.employee_middle_names
3103 ,employee_national_identifier = p_rpa_data.employee_national_identifier
3104 ,fegli = p_rpa_data.fegli
3105 ,flsa_category = p_rpa_data.flsa_category
3106 ,forwarding_address_line1 = p_rpa_data.forwarding_address_line1
3107 ,forwarding_address_line2 = p_rpa_data.forwarding_address_line2
3108 ,forwarding_address_line3 = p_rpa_data.forwarding_address_line3
3109 ,forwarding_country_short_name = p_rpa_data.forwarding_country_short_name
3110 ,forwarding_postal_code = p_rpa_data.forwarding_postal_code
3111 ,forwarding_region_2 = p_rpa_data.forwarding_region_2
3112 ,forwarding_town_or_city = p_rpa_data.forwarding_town_or_city
3116 ,position_occupied = p_rpa_data.position_occupied
3113 ,functional_class = p_rpa_data.functional_class
3114 ,part_time_hours = p_rpa_data.part_time_hours
3115 ,pay_rate_determinant = p_rpa_data.pay_rate_determinant
3117 ,retirement_plan = p_rpa_data.retirement_plan
3118 ,service_comp_date = p_rpa_data.service_comp_date
3119 ,supervisory_status = p_rpa_data.supervisory_status
3120 ,tenure = p_rpa_data.tenure
3121 ,to_ap_premium_pay_indicator = p_rpa_data.to_ap_premium_pay_indicator
3122 ,to_auo_premium_pay_indicator = p_rpa_data.to_auo_premium_pay_indicator
3123 ,to_occ_code = p_rpa_data.to_occ_code
3124 ,to_position_id = p_rpa_data.to_position_id
3125 ,to_retention_allowance = p_rpa_data.to_retention_allowance
3126 ,to_staffing_differential = p_rpa_data.to_staffing_differential
3127 ,to_step_or_ratE = p_rpa_data.to_step_or_ratE
3128 ,to_supervisory_differential = p_rpa_data.to_supervisory_differential
3129 ,veterans_preference = p_rpa_data.veterans_preference
3130 ,veterans_pref_for_riF = p_rpa_data.veterans_pref_for_riF
3131 ,veterans_status = p_rpa_data.veterans_status
3132 ,work_schedule = p_rpa_data.work_schedule
3133 ,year_degree_attained = p_rpa_data.year_degree_attained
3134 ,to_retention_allow_percentage = p_rpa_data.to_retention_allow_percentage
3135 ,to_supervisory_diff_percentage = p_rpa_data.to_supervisory_diff_percentage
3136 ,to_staffing_diff_percentage = p_rpa_data.to_staffing_diff_percentage
3137 ,award_amount = p_rpa_data.award_amount
3138 ,award_uom = p_rpa_data.award_uom
3139 ,award_percentage = p_rpa_data.award_percentage
3140 where pa_request_id = p_rpa_data.pa_request_id;
3141
3142 if sql%notfound then
3143 p_result := FALSE;
3144 else
3145 p_result := TRUE;
3146 end if;
3147
3148 EXCEPTION
3149 WHEN others THEN
3150 -- Reset IN OUT parameters and set OUT parameters
3151
3152 p_result := NULL;
3153 RAISE;
3154
3155
3156 end update_shadow_row;
3157
3158 Procedure create_remarks
3159 (p_mass_award_id in ghr_mass_awards.mass_award_id%TYPE,
3160 p_rpa_type in ghr_pa_requests.rpa_type%TYPE,
3161 p_effective_date in date,
3162 p_pa_request_id in ghr_pa_requests.pa_request_id%type
3163 )
3164
3165 is
3166
3167 l_proc varchar2(72) := g_package || 'create_remarks';
3168 l_pa_remark_id ghr_pa_remarks.pa_remark_id%type;
3169 l_remark_id ghr_pa_remarks.remark_id%type;
3170 l_description ghr_pa_remarks.description%type;
3171 l_remark_code_information1 ghr_pa_remarks.remark_code_information1%type;
3172 l_remark_code_information2 ghr_pa_remarks.remark_code_information2%type;
3173 l_remark_code_information3 ghr_pa_remarks.remark_code_information3%type;
3174 l_remark_code_information4 ghr_pa_remarks.remark_code_information4%type;
3175 l_remark_code_information5 ghr_pa_remarks.remark_code_information5%type;
3176 l_p_pa_remark_id ghr_pa_remarks.remark_id%type;
3177 l_p_object_version_number ghr_pa_remarks.object_version_number%type;
3178
3179 cursor cur_rem_tmp is
3180 select pa_remark_id,
3181 remark_id,
3182 description,
3183 remark_code_information1,
3184 remark_code_information2,
3185 remark_code_information3,
3186 remark_code_information4,
3187 remark_code_information5
3188 from ghr_pa_remarks
3189 where pa_request_id =
3190 (select pa_request_id from ghr_pa_requests
3191 where mass_action_id = p_mass_award_id
3192 and rpa_type = p_rpa_type
3193 and person_id is null)
3194 order by pa_remark_id;
3195
3196 cursor cur_rem is
3197 select pa_remark_id,
3198 object_version_number
3199 from ghr_pa_remarks
3200 where pa_request_id = p_pa_request_id
3201 and remark_id = l_remark_id
3202 and description = l_description
3203 order by pa_remark_id;
3204
3205 begin
3206
3207 -- get template remarks and descriptions
3208 hr_utility.set_location('Entering ' || l_proc,5);
3209
3210 for cur_rem_tmp_rec in cur_rem_tmp loop
3211 l_pa_remark_id := cur_rem_tmp_rec.pa_remark_id;
3212 l_remark_id := cur_rem_tmp_rec.remark_id;
3213 l_description := cur_rem_tmp_rec.description;
3214 l_remark_code_information1 := cur_rem_tmp_rec.remark_code_information1;
3215 l_remark_code_information2 := cur_rem_tmp_rec.remark_code_information2;
3216 l_remark_code_information3 := cur_rem_tmp_rec.remark_code_information3;
3217 l_remark_code_information4 := cur_rem_tmp_rec.remark_code_information4;
3218 l_remark_code_information5 := cur_rem_tmp_rec.remark_code_information5;
3219
3220 l_p_pa_remark_id := null;
3221 l_p_object_version_number := null;
3222
3223 for cur_rem_rec in cur_rem loop
3224 l_p_pa_remark_id := cur_rem_rec.pa_remark_id;
3225 l_p_object_version_number := cur_rem_rec.object_version_number;
3226 end loop;
3227
3228 if l_p_pa_remark_id is null then
3229 ghr_pa_remarks_api.create_pa_remarks
3230 (
3231 p_pa_request_id => p_pa_request_id
3232 ,p_remark_id => l_remark_id
3233 ,p_description => l_description
3234 ,p_remark_code_information1 => l_remark_code_information1
3235 ,p_remark_code_information2 => l_remark_code_information2
3236 ,p_remark_code_information3 => l_remark_code_information3
3237 ,p_remark_code_information4 => l_remark_code_information4
3238 ,p_remark_code_information5 => l_remark_code_information5
3239 ,p_pa_remark_id => l_p_pa_remark_id
3240 ,p_object_version_number => l_p_object_version_number
3241 );
3242 else
3243 ghr_pa_remarks_api.update_pa_remarks
3244 (p_pa_remark_id => l_p_pa_remark_id
3245 ,p_object_version_number => l_p_object_version_number
3246 ,p_remark_code_information1 => l_remark_code_information1
3247 ,p_remark_code_information2 => l_remark_code_information2
3248 ,p_remark_code_information3 => l_remark_code_information3
3249 ,p_remark_code_information4 => l_remark_code_information4
3250 ,p_remark_code_information5 => l_remark_code_information5
3251 ,p_description => l_description
3252 );
3253 end if;
3254 end loop;
3255
3256 end create_remarks;
3257
3258 Procedure mass_awards_error_handling
3259 ( p_pa_request_id in ghr_pa_requests.pa_request_id%type,
3260 p_object_version_number in ghr_pa_requests.object_version_number%type,
3261 p_error in varchar2 default null,
3262 p_result out nocopy boolean
3263 )
3264
3265 is
3266
3267 ---
3268 --- Local variables
3269 ---
3270 l_pa_request_id ghr_pa_requests.pa_request_id%type;
3271 l_object_version_number ghr_pa_requests.object_version_number%type;
3272 l_person_id ghr_pa_requests.person_id%type;
3273 l_employee_first_name ghr_pa_requests.employee_first_name%type;
3274 l_employee_last_name ghr_pa_requests.employee_last_name%type;
3275 l_employee_middle_names ghr_pa_requests.employee_middle_names%type;
3276 l_employee_national_identifier ghr_pa_requests.employee_national_identifier%type;
3277 l_error varchar2(2000);
3278 l_log_text varchar2(2000);
3279 l_proc varchar2(72) := 'mass_awards_error_handling';
3280
3281 CURSOR cur_rpa IS
3282 SELECT object_version_number,
3283 person_id,
3284 employee_last_name,
3285 employee_first_name,
3286 employee_middle_names,
3287 employee_national_identifier
3288 FROM ghr_pa_requests
3289 WHERE pa_request_id = l_pa_request_id;
3290
3291
3292 begin
3293
3294 hr_utility.set_location('Entering ..' || l_proc,5);
3295
3296 l_pa_request_id := p_pa_request_id;
3297 l_object_version_number := p_object_version_number;
3298 l_error := p_error;
3299
3300 hr_utility.set_location('pa_request_id = ' ||to_char( l_pa_request_id),5);
3301 hr_utility.set_location('object_version_number = ' ||to_char(l_object_version_number),5);
3302
3303 ghr_api.call_workflow (
3304 p_pa_request_id => l_pa_request_id,
3305 p_action_taken => 'CONTINUE',
3306 p_error => l_error);
3307
3308 for cur_rpa_rec in cur_rpa loop
3309 hr_utility.set_location( l_proc,10);
3310 l_object_version_number := cur_rpa_rec.object_version_number;
3311 l_person_id := cur_rpa_rec.person_id;
3312 l_employee_last_name := cur_rpa_rec.employee_last_name;
3313 l_employee_first_name := cur_rpa_rec.employee_first_name;
3314 l_employee_middle_names := cur_rpa_rec.employee_middle_names;
3315 l_employee_national_identifier := cur_rpa_rec.employee_national_identifier;
3316 hr_utility.set_location('object_version_number = ' ||to_char( l_object_version_number),10);
3317 end loop;
3318
3319 -- Call ghr_par_upd.upd here
3320 ghr_par_upd.upd(
3321 p_pa_request_id => l_pa_request_id,
3322 p_rpa_type => 'M',
3323 p_object_version_number => l_object_version_number);
3324
3325 hr_utility.set_location( 'Made Manual RPA' || to_char(l_pa_request_id) ,20);
3326 hr_utility.set_location( 'Leaving ' || l_proc ,25);
3327 -- Bug#3718167 Added SSN to the following log text
3328 l_log_text := ' ,' || l_employee_last_name || ', ' || l_employee_first_name||' '||l_employee_middle_names
3329 ||'SSN: '|| l_employee_national_identifier;
3330
3331 ghr_wgi_pkg.create_ghr_errorlog(
3332 p_program_name => g_log_name,
3333 p_message_name => substr('Manual RPA',1,30),
3334 p_log_text => substr('Person id : ' || to_char(l_person_id) || ' ' || l_log_text,1,2000),
3335 p_log_date => sysdate);
3336
3337 p_result := FALSE;
3338
3339 exception when others then
3340 p_result := TRUE;
3341 begin
3342 ghr_par_upd.upd(
3343 p_pa_request_id => l_pa_request_id,
3344 p_mass_action_eligible_flag => 'Y',
3345 p_mass_action_select_flag => 'N',
3346 p_mass_action_comments =>
3347 'PRG: Prgramatically Deselected, Failed at Call Work flow',
3348 p_object_version_number => l_object_version_number);
3349 exception when others then
3350 null;
3351 end;
3352
3353 end mass_awards_error_handling;
3354
3355 --Pradeep added this procedure for bug 3934195
3356 /* Logic is first set the message name and tokens as usual and then use
3357 the fnd_message.get to get the message that we have assigned.
3358 This is to remove the hard coding and using only the messages.
3359 */
3360
3361 PROCEDURE check_award_amount (p_noa_code ghr_pa_requests.first_noa_code%TYPE,
3362 p_effective_date ghr_pa_requests.effective_date%TYPE,
3363 p_award_amount NUMBER,
3364 p_from_pay_plan ghr_pa_requests.from_pay_plan%TYPE,
3365 p_from_basic_pay_pa ghr_pa_requests.from_basic_pay%TYPE,
3366 p_to_position_id ghr_pa_requests.to_position_id%TYPE,
3367 p_comments OUT NOCOPY varchar2,
3368 p_error_flg OUT NOCOPY BOOLEAN) IS
3369 l_temp NUMBER;
3370 l_max_allowed_amount NUMBER;
3371 l_min_allowed_amount NUMBER;
3372 --BUG 5482191
3373 l_psi VARCHAR2(10);
3374 BEGIN
3375 --bug 5482191
3376
3377 l_psi := ghr_pa_requests_pkg.get_personnel_system_indicator(p_to_position_id,p_effective_date);
3378
3379
3380 p_error_flg := FALSE;
3381 --Check for Maximum Amount.
3382 fnd_message.set_name ('GHR', 'GHR_38904_AWARD_AMT_TOO_BIG5');
3383
3384 IF p_noa_code = '844' THEN
3385 l_max_allowed_amount := 5 * p_from_basic_pay_pa / 100;
3386 fnd_message.set_token ('ALLOWED', '5%');
3387 ELSIF p_noa_code IN ('840', '841', '879') THEN
3388 l_max_allowed_amount := 25 * p_from_basic_pay_pa / 100;
3389 fnd_message.set_token ('ALLOWED', '25%');
3390 ELSIF p_noa_code IN ('878') THEN
3391 l_max_allowed_amount := 35 * p_from_basic_pay_pa / 100;
3392 fnd_message.set_token ('ALLOWED', '35%');
3393 --bug 5482191
3394 ELSIF (p_noa_code IN ('849') and l_psi = '00' ) THEN
3395 l_max_allowed_amount := 35 * p_from_basic_pay_pa / 100;
3396 fnd_message.set_token ('ALLOWED', '35%');
3397
3398 ELSIF p_noa_code IN ('825', '842', '843', '848') THEN
3399 l_max_allowed_amount := 25000;
3400 fnd_message.set_name ('GHR', 'GHR_38905_AWARD_AMT_TOO_BIG6');
3401 fnd_message.set_token ('ALLOWED', '$25000');
3402 END IF;
3403
3404 /*
3408 IF p_noa_code = '816' THEN
3405 Existing Handling of 816 and 815 is not changed make sure
3406 that 815 and 816 are not included in above list.
3407 */
3409 IF p_from_pay_plan = 'EE' THEN
3410 IF (50 * p_from_basic_pay_pa / 100) > 50000 THEN
3411 l_max_allowed_amount := 50000;
3412 ELSE
3413 l_max_allowed_amount := 50 * p_from_basic_pay_pa / 100;
3414 END IF;
3415
3416 IF p_award_amount > l_max_allowed_amount THEN
3417 fnd_message.set_name ('GHR', 'GHR_38898_AWARD_AMT_TOO_BIG3');
3418 p_comments := p_comments||fnd_message.get;
3419 p_error_flg := TRUE;
3420 fnd_message.clear;
3421 END IF;
3422 ELSIF ( ghr_pay_calc.leo_position (
3423 p_prd => l_temp,
3424 p_position_id=> p_to_position_id,
3425 p_retained_user_table_id=> l_temp,
3426 p_duty_station_id=> l_temp,
3427 p_effective_date=> p_effective_date
3428 )
3429 ) THEN
3430
3431 l_max_allowed_amount := 25 * p_from_basic_pay_pa / 100;
3432 IF l_max_allowed_amount < 15000 THEN
3433 l_max_allowed_amount := 15000;
3434 END IF;
3435 IF p_award_amount > l_max_allowed_amount THEN
3436 fnd_message.set_name ('GHR', 'GHR_38896_AWARD_AMT_TOO_BIG2');
3437 p_comments := p_comments||fnd_message.get;
3438 p_error_flg := TRUE;
3439 fnd_message.clear;
3440 END IF;
3441 ELSIF ( NOT ghr_pay_calc.leo_position (
3442 p_prd => l_temp,
3443 p_position_id=> p_to_position_id,
3444 p_retained_user_table_id=> l_temp,
3445 p_duty_station_id=> l_temp,
3446 p_effective_date=> p_effective_date
3447 )
3448 ) THEN
3449 l_max_allowed_amount := 25 * p_from_basic_pay_pa / 100;
3450
3451 IF p_award_amount > l_max_allowed_amount THEN
3452 fnd_message.set_name ('GHR', 'GHR_AWARD_AMT_TOO_BIG');
3453 p_comments := p_comments||fnd_message.get;
3454 p_error_flg := TRUE;
3455 fnd_message.clear;
3456 END IF;
3457 END IF;
3458 ELSIF p_noa_code = '815' THEN
3459 IF p_from_pay_plan = 'EE' THEN
3460 IF (50 * p_from_basic_pay_pa / 100) > 50000 THEN
3461 l_max_allowed_amount := 50000;
3462 ELSE
3463 l_max_allowed_amount := 50 * p_from_basic_pay_pa / 100;
3464 END IF;
3465
3466 IF p_award_amount > l_max_allowed_amount THEN
3467 fnd_message.set_name ('GHR', 'GHR_38898_AWARD_AMT_TOO_BIG3');
3468 p_comments := p_comments||fnd_message.get;
3469 p_error_flg := TRUE;
3470 fnd_message.clear;
3471 END IF;
3472 ELSE
3473 l_max_allowed_amount := 25 * p_from_basic_pay_pa / 100;
3474 fnd_message.set_token ('ALLOWED', '25%');
3475 p_comments := p_comments||fnd_message.get;
3476 p_error_flg := TRUE;
3477 fnd_message.clear;
3478 END IF;
3479 END IF;
3480
3481 -- Raise an Error if Award Amount is Greater than the Maximum Allowed Amount.
3482 --IF p_award_amount > round(l_max_allowed_amount) THEN
3483
3484 IF p_award_amount > floor(l_max_allowed_amount) THEN
3485 p_comments := fnd_message.get;
3486 p_error_flg := TRUE;
3487 fnd_message.CLEAR;
3488 ELSE
3489 fnd_message.CLEAR;
3490 END IF;
3491
3492 --Check for Minimum Amount.
3493 fnd_message.set_name ('GHR', 'GHR_38903_AWARD_AMT_TOO_LESS');
3494
3495 --Getting the Minimum Allowed Amount.
3496 IF p_noa_code = '879' THEN
3497 l_min_allowed_amount := 5 * p_from_basic_pay_pa / 100;
3498 fnd_message.set_token ('ALLOWED', '5%');
3499 END IF;
3500
3501 -- Raise an Error if Award Amount is Less than the Minimum Allowed Amount.
3502 IF p_award_amount < trunc(l_min_allowed_amount) THEN
3503 p_comments := fnd_message.get;
3504 p_error_flg := TRUE;
3505 fnd_message.clear;
3506 ELSE
3507 fnd_message.clear;
3508 END IF;
3509
3510 END check_award_amount;
3511
3512 END GHR_MASS_AWARDS_PKG;