[Home] [Help]
PACKAGE BODY: APPS.GHR_WF_WGI_PKG
Source
1 PACKAGE BODY ghr_wf_wgi_pkg AS
2 /* $Header: ghwfwgi.pkb 120.3 2006/05/05 04:12:20 vnarasim noship $ */
3 --
4 -- Procedure
5 -- StartWGIProcess
6 --
7 -- Description
8 -- Start the WGI workflow process for the given p_pa_request_id
9 --
10 PROCEDURE StartWGIProcess
11 ( p_pa_request_id in number,
12 p_full_name in varchar2
13 ) is
14 --
15 l_ItemType varchar2(30) := 'WGI';
16 l_ItemKey varchar2(30) := p_pa_request_id;
17 --
18 begin
19 -- Added for testing
20 --
21 -- Creates a new runtime process for an application item (SF-52 for WGI)
22 --
23 wf_engine.createProcess( ItemType => l_ItemType,
24 ItemKey => l_ItemKey,
25 process => 'AWGI' );
26 --
27 --
28 wf_engine.SetItemAttrText ( itemtype => l_ItemType,
29 itemkey => l_Itemkey,
30 aname => 'PA_REQUEST_ID',
31 avalue => p_pa_request_id );
32 wf_engine.SetItemAttrText ( itemtype => l_ItemType,
33 itemkey => l_Itemkey,
34 aname => 'EMP_FULL_NAME',
35 avalue => p_full_name );
36 --
37 --
38 -- Start the WGI process for the SF52 created
39 --
40 wf_engine.StartProcess ( ItemType => l_ItemType,
41 ItemKey => l_ItemKey );
42 --
43 --
44 end StartWGIProcess;
45 --
46 --
47 procedure FindDestination( itemtype in varchar2,
48 itemkey in varchar2,
49 actid in number,
50 funcmode in varchar2,
51 result in out nocopy varchar2 ) is
52 --
53 --
54 --
55 l_user_name ghr_pa_routing_history.user_name%TYPE;
56 l_person_id ghr_pa_requests.person_id%TYPE;
57 l_position_id ghr_pa_requests.from_position_id%TYPE;
58 l_effective_date ghr_pa_requests.effective_date%TYPE;
59 l_routing_group_id ghr_pa_requests.routing_group_id%TYPE;
60 l_assignment_id ghr_pa_requests.employee_assignment_id%TYPE;
61 l_full_name per_people_f.full_name%TYPE;
62 l_routing_group_name ghr_routing_groups.name%TYPE;
63 l_routing_group_desc ghr_routing_groups.description%TYPE;
64 l_supervisor_name ghr_pa_routing_history.user_name%TYPE;
65 l_groupbox_id ghr_groupboxes.groupbox_id%TYPE;
66 l_groupbox_name ghr_groupboxes.name%TYPE;
67 l_groupbox_desc ghr_groupboxes.description%TYPE;
68 l_office_symbol_name hr_lookups.meaning%TYPE;
69 l_wgi_due_date date;
70 l_rating varchar2(30);
71 l_multi_error_flag boolean;
72 l_valid_user boolean;
73 l_valid_grpbox boolean;
74 l_line1 varchar2(500);
75 l_line2 varchar2(500);
76 l_line3 varchar2(500);
77 l_line4 varchar2(500);
78 l_line5 varchar2(500);
79 l_line6 varchar2(500);
80 l_line7 varchar2(500);
81 l_wgi_error_note varchar2(500);
82 l_personnel_office_id ghr_pa_requests.personnel_office_id%TYPE;
83 l_gbx_user_id ghr_pois.person_id%TYPE;
84 l_routing_group varchar2(500);
85 -- NOCOPY Changes
86 l_result varchar2(250);
87 --
88 --
89 Cursor c_user_full_name is
90 Select ppf.full_name
91 from per_people_f ppf
92 where ppf.person_id =
93 (select employee_id
94 from fnd_user
95 where user_name = l_user_name
96 )
97 and l_effective_date
98 between ppf.effective_start_date and ppf.effective_end_date;
99 begin
100 -- NOCOPY Changes
101 l_result := result;
102 --
103 if funcmode = 'RUN' then
104 wf_engine.SetItemAttrText (itemtype => ItemType,
105 itemkey => Itemkey,
106 aname => 'ERROR_MSG',
107 avalue => '' );
108 --
109 -- Get Person ID and effective date from PA requests table
110 get_par_details (
111 p_pa_request_id => itemkey
112 ,p_person_id => l_person_id
113 ,p_effective_date => l_effective_date
114 ,p_position_id => l_position_id
115 );
116 --Find supervisor name of WF user
117 l_user_name := get_next_approver (
118 p_person_id => l_person_id
119 ,p_effective_date => l_effective_date
120 );
121 -- Set item attributes
122 if l_user_name Is Not Null then
123 -- Get Full Name of the user
124 for full_name_rec in c_user_full_name loop
125 l_full_name := full_name_rec.full_name;
126 end loop;
127
128 wf_engine.SetItemAttrText ( itemtype => Itemtype,
129 itemkey => Itemkey,
130 aname => 'FWD_SUP_NAME',
131 avalue => l_user_name || ' - ' || l_full_name
132 );
133 else
134 wf_engine.SetItemAttrText ( itemtype => ItemType,
135 itemkey => Itemkey,
136 aname => 'ERROR_MSG',
137 avalue => 'Supervisor does not exist or is invalid for this Employee. ');
138 end if;
139 -- Get employees personnel groupbox
140 if l_position_id Is not Null then
141 Get_emp_personnel_groupbox ( p_position_id => l_position_id
142 ,p_effective_date => l_effective_date
143 ,p_groupbox_name => l_groupbox_name
144 ,p_personnel_office_id => l_personnel_office_id
145 ,p_gbx_user_id => l_gbx_user_id
146 );
147 wf_engine.SetItemAttrText ( itemtype => ItemType,
148 itemkey => Itemkey,
149 aname => 'POI',
150 avalue => l_personnel_office_id);
151 else
152 wf_engine.SetItemAttrText ( itemtype => ItemType,
153 itemkey => Itemkey,
154 aname => 'ERROR_MSG',
155 avalue => 'Position ID does not exist for this Employee. '
156 );
157 result := 'COMPLETE:NO';
158 return;
159 end if;
160 -- Verify whether valid workflow user
161 l_valid_user := VerifyValidWFUser (
162 p_user_name => l_user_name
163 );
164 -- Verify whether valid groupbox
165 l_valid_grpbox := VerifyValidWFUser (
166 p_user_name => l_groupbox_name
167 );
168 if l_groupbox_name Is Not Null then
169 wf_engine.SetItemAttrText (
170 itemtype => Itemtype,
171 itemkey => Itemkey,
172 aname => 'PERSONNEL_OFFICE_GBX',
173 avalue => l_groupbox_name
174 );
175 l_wgi_error_note := 'Please process the WGI Request for Personnel Action manually
176 which has been routed to your Personnel office Groupbox :' || l_groupbox_name;
177 end if;
178 if l_valid_grpbox then
179 GetRoutingGroupDetails (
180 p_groupbox_name => l_groupbox_name
181 ,p_groupbox_id => l_groupbox_id
182 ,p_routing_group_id => l_routing_group_id
183 ,p_groupbox_desc => l_groupbox_desc
184 ,p_routing_group_name => l_routing_group_name
185 ,p_routing_group_desc => l_routing_group_desc
186 );
187 else
188 wf_engine.SetItemAttrText( itemtype => ItemType,
189 itemkey => Itemkey,
190 aname => 'ERROR_MSG',
191 avalue => 'Groupbox is not valid or invalid for this Employee. ' );
192 end if;
193 if l_routing_group_id is not null then
194 update_sf52_action_taken(p_pa_request_id => itemkey,
195 p_routing_group_id => l_routing_group_id,
196 p_groupbox_id => l_groupbox_id,
197 p_action_taken => 'NOT_ROUTED',
198 p_gbx_user_id => l_gbx_user_id);
199 else
200 wf_engine.SetItemAttrText ( itemtype => ItemType,
201 itemkey => Itemkey,
202 aname => 'ERROR_MSG',
203 avalue => 'Routing group does not exist for this Employee. ' );
204 end if;
205 SetDestination( p_request_id => itemkey
206 ,p_person_id => l_person_id
207 ,p_position_id => l_position_id
208 ,p_effective_date => l_effective_date
209 ,p_office_symbol_name => l_office_symbol_name
210 ,p_line1 => l_line1
211 ,p_line2 => l_line2
212 ,p_line3 => l_line3
213 ,p_line4 => l_line4
214 ,p_line5 => l_line5
215 ,p_line6 => l_line6
216 ,p_line7 => l_line7
217 ,p_routing_group => l_routing_group
218 );
219 wf_engine.SetItemAttrText (
220 itemtype => Itemtype,
221 itemkey => Itemkey,
222 aname => 'LINE1',
223 avalue => l_line1
224 );
225 wf_engine.SetItemAttrText (
226 itemtype => Itemtype,
227 itemkey => Itemkey,
228 aname => 'LINE2',
229 avalue => l_line2
230 );
231 wf_engine.SetItemAttrText (
232 itemtype => Itemtype,
233 itemkey => Itemkey,
234 aname => 'LINE3',
235 avalue => l_line3
236 );
237 wf_engine.SetItemAttrText (
238 itemtype => Itemtype,
239 itemkey => Itemkey,
240 aname => 'LINE4',
241 avalue => l_line4
242 );
243 wf_engine.SetItemAttrText (
244 itemtype => Itemtype,
245 itemkey => Itemkey,
246 aname => 'LINE5',
247 avalue => l_line5
248 );
249 wf_engine.SetItemAttrText (
250 itemtype => Itemtype,
251 itemkey => Itemkey,
252 aname => 'LINE6',
253 avalue => l_line6
254 );
255 wf_engine.SetItemAttrText (
256 itemtype => Itemtype,
257 itemkey => Itemkey,
258 aname => 'LINE7',
259 avalue => l_line7
260 );
261
262 wf_engine.SetItemAttrText (
263 itemtype => Itemtype,
264 itemkey => Itemkey,
265 aname => 'WGI_ERROR_NOTE',
266 avalue => l_wgi_error_note
267 );
268 wf_engine.SetItemAttrText( itemtype => Itemtype,
269 itemkey => Itemkey,
270 aname => 'FROM_NAME',
271 avalue => FND_GLOBAL.USER_NAME() );
272 wf_engine.SetItemAttrText (
273 itemtype => Itemtype,
274 itemkey => Itemkey,
275 aname => 'OFFICE_SYMBOL',
276 avalue => l_office_symbol_name
277 );
278 wf_engine.SetItemAttrText (
279 itemtype => Itemtype,
280 itemkey => Itemkey,
281 aname => 'ROUTING_GROUP',
282 avalue => l_routing_group
283 );
284 if ((l_valid_user ) and (l_valid_grpbox)) then
285 result := 'COMPLETE:YES';
286 return;
287 elsif ((NOT l_valid_user) and (NOT l_valid_grpbox)) then
288 wf_engine.SetItemAttrText ( itemtype => Itemtype,
289 itemkey => Itemkey,
290 aname => 'ERROR_MSG',
291 avalue => 'Supervisor and Groupbox for the employee are invalid. ');
292 result := 'COMPLETE:NO';
293 return;
294 else
295 result := 'COMPLETE:NO';
296 return;
297 end if;
298 --
299 --
300 --
301 elsif ( funcmode = 'CANCEL' ) then
302 result := 'COMPLETE:NO';
303 return;
304 --
305 --
306 end if;
307 --
308 result := '';
309 return;
310 --
311 --
312 exception
313 when others then
314 -- NOCOPY Changes
315 result := l_result;
316 -- The line below records this function call in the error system
317 -- in the case of an exception.
318 wf_core.context('WGI', 'ghr_wf_wgi_pkg.FindDestination',itemtype, itemkey, to_char(actid), funcmode);
319 raise;
320 --
321 end FindDestination;
322 --
323 --
324 procedure approval_required( itemtype in varchar2,
325 itemkey in varchar2,
326 actid in number,
327 funcmode in varchar2,
328 result in out nocopy varchar2 ) is
329 --
330 l_text varchar2(30);
331 l_result varchar2(250);
332 --
333 --
334 begin
335 -- NOCOPY Changes
336 l_result := result;
337 --
338 if funcmode = 'RUN' then
339 l_text := wf_engine.GetItemAttrText( itemtype => Itemtype,
340 itemkey => Itemkey,
341 aname => 'APPROVAL_REQUIRED');
342 if l_text = 'NO' then
343 result := 'COMPLETE:NO';
344 return;
345 else
346 result := 'COMPLETE:YES';
347 return;
348 end if;
349 end if;
350 --
351 result := '';
352 return;
353 --
354 --
355 exception
356 when others then
357 -- NOCOPY Changes
358 result := l_result;
359 -- The line below records this function call in the error system
360 -- in the case of an exception.
361 wf_core.context('WGI', 'ghr_wf_wgi_pkg.approval_required',itemtype, itemkey, to_char(actid), funcmode);
362 raise;
363 --
364 --
365 end approval_required;
366 --
367 --
368 --
369 PROCEDURE GetRoutingGroupDetails (
370 p_groupbox_name IN varchar2,
371 p_groupbox_id out nocopy number,
372 p_routing_group_id out nocopy number,
373 p_groupbox_desc out nocopy varchar2,
374 p_routing_group_name out nocopy varchar2,
375 p_routing_group_desc out nocopy varchar2
376 ) IS
377 -- Local variables
378 l_groupbox_id ghr_groupboxes.groupbox_id%TYPE;
379 l_groupbox_name ghr_groupboxes.name%TYPE;
380 l_groupbox_desc ghr_groupboxes.name%TYPE;
381 l_routing_group_id ghr_groupboxes.routing_group_id%TYPE;
382 l_routing_group_name ghr_routing_groups.name%TYPE;
383 l_routing_group_desc ghr_routing_groups.description%TYPE;
384 --
385 --
386 cursor csr_gbx is
387 select gbx.groupbox_id, gbx.routing_group_id, gbx.name, rgp.name , rgp.description
388 from ghr_groupboxes gbx, ghr_routing_groups rgp
389 where gbx.name = l_groupbox_name
390 and gbx.routing_group_id = rgp.routing_group_id;
391 --
392 begin
393 --
394 --
395 l_groupbox_name := p_groupbox_name;
396 open csr_gbx;
397 -- fetch the candidate details
398 fetch csr_gbx into l_groupbox_id, l_routing_group_id, l_groupbox_name, l_routing_group_name, l_routing_group_desc;
399 if csr_gbx%notfound then
400 -- if the cursor does not return a row then we must set the out
401 -- parameter to null
402 p_routing_group_id := null;
403 p_routing_group_name := null;
404 p_routing_group_desc := null;
405 p_groupbox_desc := null;
406 p_groupbox_id := null;
407 else
408 p_routing_group_id := l_routing_group_id;
409 p_routing_group_name := l_routing_group_name;
410 p_routing_group_desc := l_routing_group_desc;
411 p_groupbox_desc := l_groupbox_desc;
412 p_groupbox_id := l_groupbox_id;
413 end if;
414 -- close the cursor
415 close csr_gbx;
416 exception
417 when others then
418 p_routing_group_id := null;
419 p_routing_group_name := null;
420 p_routing_group_desc := null;
421 p_groupbox_desc := null;
422 p_groupbox_id := null;
423 raise;
424 end GetRoutingGroupDetails;
425 --
426 --
427 --
428 -- ----------------------------------------------------------------------------
429 -- |-----------------------------< get_next_approver >------------------------|
430 -- ----------------------------------------------------------------------------
431 function get_next_approver
432 (p_person_id in per_people_f.person_id%type,
433 p_effective_date in ghr_pa_requests.effective_date%TYPE
434 )
435 return ghr_pa_routing_history.user_name%TYPE is
436 --
437 l_in_person_id ghr_pa_requests.person_id%TYPE;
438 l_effective_date ghr_pa_requests.effective_date%TYPE;
439 l_out_person_name ghr_pa_routing_history.user_name%TYPE;
440 --
441 cursor csr_pa is
442 select substr(usr.user_name,1,30) user_name
443 from per_assignments_f paf
444 ,per_people_f ppf
445 ,fnd_user usr
446 where paf.person_id = l_in_person_id
447 and paf.primary_flag = 'Y'
448 and p_effective_date
449 between paf.effective_start_date
450 and paf.effective_end_date
451 and ppf.person_id = paf.supervisor_id
452 and ppf.current_employee_flag = 'Y'
453 and p_effective_date
454 between ppf.effective_start_date
455 and ppf.effective_end_date
456 and ppf.person_id = usr.employee_id;
457 --
458 --
459 --
460 begin
461 -- [CUSTOMIZE]
462 -- open the candidate select cursor
463 l_in_person_id := p_person_id;
464 l_effective_date := p_effective_date;
465 open csr_pa;
466 -- fetch the candidate details
467 fetch csr_pa into l_out_person_name;
468 if csr_pa%notfound then
469 -- if the cursor does not return a row then we must set the out
470 -- parameter to null
471 l_out_person_name := null;
472 end if;
473 -- close the cursor
474 close csr_pa;
475 return(l_out_person_name);
476 end get_next_approver;
477 --
478 --
479 PROCEDURE Get_emp_personnel_groupbox (
480 p_position_id IN number,
481 p_effective_date IN date,
482 p_groupbox_name out nocopy varchar2,
483 p_personnel_office_id out nocopy ghr_pa_requests.personnel_office_id%TYPE,
484 p_gbx_user_id out nocopy ghr_pois.person_id%TYPE
485 ) IS
486 --
487 -- Local variables
488 --
489 l_groupbox_name ghr_groupboxes.name%TYPE;
490 --
491 l_flsa_category ghr_pa_requests.flsa_category%TYPE;
492 l_bargaining_unit_status ghr_pa_requests.bargaining_unit_status%TYPE;
493 l_work_schedule ghr_pa_requests.work_schedule%TYPE;
494 l_functional_class ghr_pa_requests.functional_class%TYPE;
495 l_supervisory_status ghr_pa_requests.supervisory_status%TYPE;
496 l_position_occupied ghr_pa_requests.position_occupied%TYPE;
497 l_appropriation_code1 ghr_pa_requests.appropriation_code1%TYPE;
498 l_appropriation_code2 ghr_pa_requests.appropriation_code2%TYPE;
499 l_personnel_office_id ghr_pa_requests.personnel_office_id%TYPE;
500 l_from_office_symbol ghr_pa_requests.from_office_symbol%TYPE;
501 l_part_time_hours ghr_pa_requests.part_time_hours%TYPE;
502 l_gbx_user_id ghr_pois.person_id%TYPE;
503 --
504 --
505 cursor csr_poi_code is
506 SELECT gbx.name ,poi.person_id
507 from ghr_groupboxes gbx, ghr_pois poi
508 WHERE gbx.groupbox_id (+) = nvl(poi.groupbox_id,-9999999)
509 AND poi.personnel_office_id = nvl(l_personnel_office_id,-99999999);
510 --
511 begin
512 --
513 ghr_pa_requests_pkg.get_SF52_pos_ddf_details
514 (p_position_id => p_position_id
515 ,p_date_effective => trunc(p_effective_date)
516 ,p_flsa_category => l_flsa_category
517 ,p_bargaining_unit_status => l_bargaining_unit_status
518 ,p_work_schedule => l_work_schedule
519 ,p_functional_class => l_functional_class
520 ,p_supervisory_status => l_supervisory_status
521 ,p_position_occupied => l_position_occupied
522 ,p_appropriation_code1 => l_appropriation_code1
523 ,p_appropriation_code2 => l_appropriation_code2
524 ,p_personnel_office_id => l_personnel_office_id
525 ,p_office_symbol => l_from_office_symbol
526 ,p_part_time_hours => l_part_time_hours);
527 --
528 if l_personnel_office_id Is Null then
529 p_groupbox_name := null;
530 p_personnel_office_id := null;
531 else
532 open csr_poi_code;
533 fetch csr_poi_code into l_groupbox_name, l_gbx_user_id;
534 if csr_poi_code%notfound then
535 p_groupbox_name := null;
536 else
537 p_groupbox_name := l_groupbox_name;
538 end if;
539 p_personnel_office_id := l_personnel_office_id;
540 p_gbx_user_id := l_gbx_user_id;
541 close csr_poi_code;
542 end if;
543 --
544 exception
545 WHEN OTHERS THEN
546 p_groupbox_name := null;
547 p_personnel_office_id := null;
548 p_gbx_user_id := null;
549 raise;
550 end Get_emp_personnel_groupbox;
551 --
552 --
553 --
554 PROCEDURE Get_par_details (
555 p_pa_request_id in number
556 ,p_person_id out nocopy number
557 ,p_effective_date out nocopy date
558 ,p_position_id out nocopy number
559 ) IS
560 --
561 -- Local variables
562 --
563 l_pa_request_id ghr_pa_requests.pa_request_id%TYPE;
564 l_person_id ghr_pa_requests.person_id%TYPE;
565 l_effective_date ghr_pa_requests.effective_date%TYPE;
566 l_position_id ghr_pa_requests.from_position_id%TYPE;
567 --
568 cursor csr_par is
569 select person_id, effective_date, from_position_id
570 from ghr_pa_requests par
571 where par.pa_request_id = l_pa_request_id;
572 --
573 begin
574 --
575 --
576 l_pa_request_id := p_pa_request_id;
577 open csr_par;
578 -- fetch the position extra info details
579 fetch csr_par into l_person_id , l_effective_date, l_position_id;
580 if csr_par%notfound then
581 -- if the cursor does not return a row then we must set the out
582 -- parameter to null
583 p_person_id := null;
584 p_effective_date := null;
585 p_position_id := null;
586 else
587 p_person_id := l_person_id;
588 p_effective_date := l_effective_date;
589 p_position_id := l_position_id;
590 end if;
591 close csr_par;
592 -- NOCOPY Changes. Added exception.
593 EXCEPTION
594 WHEN OTHERS THEN
595 p_person_id := NULL;
596 p_effective_date := NULL;
597 p_position_id := NULL;
598 raise;
599 end Get_par_details;
600 --
601 --
602 PROCEDURE PersonnelGrpBoxExists (itemtype in varchar2,
603 itemkey in varchar2,
604 actid in number,
605 funcmode in varchar2,
606 result in out nocopy varchar2
607 ) is
608 l_text varchar2(80);
609 l_result VARCHAR2(250);
610 Begin
611 -- NOCOPY Changes
612 l_result := result;
613 if funcmode = 'RUN' then
614 l_text := wf_engine.GetItemAttrText( itemtype => Itemtype,
615 itemkey => Itemkey,
616 aname => 'PERSONNEL_OFFICE_GBX');
617 if l_text is NULL then
618 ghr_wgi_pkg.create_ghr_errorlog(
619 p_program_name => 'PersonnelGrpBoxExists',
620 p_log_text => 'Itemtype: '||itemtype||' Itemkey: '||itemkey||'Group Box Id does not exist',
621 p_message_name => null,
622 p_log_date => sysdate );
623 result := 'COMPLETE:NO';
624 return;
625 else
626 result := 'COMPLETE:YES';
627 return;
628 end if;
629 end if;
630 --
631 -- Other execution modes may be created in the future.
632 -- Activity indicates that it does not implement a mode
633 -- by returning null
634 --
635 result := '';
636 return;
637 --
638 exception
639 when others then
640 -- NOCOPY Changes
641 result := l_result;
642 -- The line below records this function call in the error system
643 -- in the case of an exception.
644 wf_core.context('WGI', 'ghr_wf_pd_pkg.PersonnelGrpBoxExists',itemtype, itemkey, to_char(actid), funcmode);
645 raise;
646 --
647 --
648 End PersonnelGrpBoxExists;
649 --
650 --
651 Procedure StartSF52Process( itemtype in varchar2,
652 itemkey in varchar2,
653 actid in number,
654 funcmode in varchar2,
655 result in out nocopy varchar2
656 ) is
657 l_grpbox_name ghr_pa_routing_history.user_name%TYPE;
658 l_error_msg varchar2(1000);
659 l_result VARCHAR2(250);
660 Begin
661 l_result := result;
662 if funcmode = 'RUN' then
663 l_error_msg := wf_engine.GetItemAttrText (
664 itemtype => Itemtype,
665 itemkey => Itemkey,
666 aname => 'ERROR_MSG'
667 );
668 ghr_api.call_workflow (
669 P_PA_REQUEST_ID => itemkey,
670 P_ACTION_TAKEN => 'CONTINUE',
671 P_OLD_ACTION_TAKEN => NULL,
672 P_ERROR => l_error_msg
673 );
674 result := 'COMPLETE:YES';
675 return;
676 end if;
677 --
678 -- Other execution modes may be created in the future.
679 -- Activity indicates that it does not implement a mode
680 -- by returning null
681 --
682 result := '';
683 return;
684 --
685 exception
686 when others then
687 -- NOCOPY Changes
688 result := l_result;
689 -- The line below records this function call in the error system
690 -- in the case of an exception.
691 wf_core.context('WGI', 'ghr_wf_pd_pkg.StartSF52Process',itemtype, itemkey, to_char(actid), funcmode);
692 raise;
693 --
694 End StartSF52Process;
695 --
696 --
697 Procedure CancelSF52Process( itemtype in varchar2,
698 itemkey in varchar2,
699 actid in number,
700 funcmode in varchar2,
701 result in out nocopy varchar2
702 ) is
703 cursor c1 is
704 select object_version_number
705 from ghr_pa_requests
706 where pa_request_id = itemkey;
707 l_ovn ghr_pa_requests.object_version_number%type;
708 l_result VARCHAR2(250);
709
710 Begin
711 l_result := result;
712 if funcmode = 'RUN' then
713 open c1;
714 fetch c1 into l_ovn;
715 close c1;
716 ghr_sf52_api.end_sf52 ( p_pa_request_id => itemkey,
717 p_action_taken => 'CANCELED',
718 p_par_object_version_number => l_ovn
719 );
720 result := 'COMPLETE:YES';
721 end if;
722 --
723 --
724 -- Other execution modes may be created in the future.
725 -- Activity indicates that it does not implement a mode
726 -- by returning null
727 --
728 result := '';
729 return;
730 --
731 exception
732 when others then
733 -- NOCOPY Changes
734 result := l_result;
735 -- The line below records this function call in the error system
736 -- in the case of an exception.
737 wf_core.context('WGI', 'ghr_wf_pd_pkg.CancelSF52Process',itemtype, itemkey, to_char(actid), funcmode);
738 raise;
739 --
740 End CancelSF52Process;
741 --
742 --
743 Function VerifyValidWFUser (p_user_name in varchar2)
744 return boolean is
745 l_temp char;
746 cursor c1(p_user_name varchar2) is
747 select 'X'
748 from wf_roles
749 where name = p_user_name;
750 begin
751 open c1(p_user_name);
752 fetch c1 into l_temp;
753 if c1%NOTFOUND then
754 close c1;
755 return(FALSE);
756 else
757 close c1;
758 return(TRUE);
759 end if;
760 end VerifyValidWFUser;
761 --
762 --
763 PROCEDURE SetDestination( p_request_id in varchar2
764 ,p_person_id in varchar2
765 ,p_position_id in varchar2
766 ,p_effective_date in date
767 ,p_office_symbol_name out nocopy varchar2
768 ,p_line1 out nocopy varchar2
769 ,p_line2 out nocopy varchar2
770 ,p_line3 out nocopy varchar2
771 ,p_line4 out nocopy varchar2
772 ,p_line5 out nocopy varchar2
773 ,p_line6 out nocopy varchar2
774 ,p_line7 out nocopy varchar2
775 ,p_routing_group out nocopy varchar2
776 ) is
777
778 l_line1 varchar2(500);
779 l_line2 varchar2(500);
780 l_line3 varchar2(500);
781 l_line4 varchar2(500);
782 l_line5 varchar2(500);
783 l_line6 varchar2(500);
784 l_line7 varchar2(500);
785 l_office_symbol_id varchar2(30);
786 l_office_symbol_name varchar2(80);
787 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
788 l_ret_grade ghr_pa_requests.from_grade_or_level%type;
789 l_cur_grade ghr_pa_requests.from_grade_or_level%type;
790 l_ret_pay_plan varchar2(30);
791 l_cur_pay_plan varchar2(30);
792 l_cur_step_rate ghr_pa_requests.from_step_or_rate%type;
793 l_ret_step_rate ghr_pa_requests.from_step_or_rate%type;
794 l_rating_id per_analysis_criteria.segment2%type;
795 l_rating ghr_pa_request_extra_info.rei_information3%type;
796 l_rating_date varchar2(30);
797 l_wgi_date varchar2(30);
798 l_new_step ghr_pa_requests.to_step_or_rate%type;
799 l_new_sal ghr_pa_requests.to_total_salary%type;
800 l_positionei_rec per_position_extra_info%rowtype;
801 l_special_info ghr_api.special_information_type;
802 l_routing_group_id ghr_pa_requests.routing_group_id%TYPE;
803 l_routing_group_name ghr_routing_groups.name%TYPE;
804 l_rgp_description ghr_routing_groups.description%TYPE;
805 --
806 --
807 cursor csr_lkp_code( l_type in varchar2,
808 l_code in varchar2) is
809 select fcl.meaning
810 from hr_lookups fcl
811 where fcl.lookup_type = l_type
812 and fcl.lookup_code = l_code
813 and fcl.enabled_flag = 'Y'
814 and nvl(p_effective_date,trunc(sysdate))
815 between nvl(fcl.start_date_active,nvl(p_effective_date,trunc(sysdate)))
816 and nvl(fcl.end_date_active, nvl(p_effective_date,trunc(sysdate)));
817 --
818 cursor cur_pay_plan is
819 select from_pay_plan,
820 from_grade_or_level,
821 from_step_or_rate,
822 to_step_or_rate,
823 to_total_salary
824 from ghr_pa_requests
825 where pa_request_id = p_request_id;
826 --
827 cursor cur_par_extra is
828 select rei_information3,
829 rei_information9
830 from ghr_pa_request_extra_info
831 where pa_request_id = p_request_id
832 and information_type = 'GHR_US_PAR_PERF_APPRAISAL';
833 --
834 --
835 cursor csr_rgp is
836 SELECT rgp.name, rgp.description
837 FROM ghr_routing_groups rgp, ghr_pa_requests par
838 WHERE par.pa_request_id = p_request_id
839 and par.routing_group_id = rgp.routing_group_id;
840 --
841 Begin
842 ghr_history_fetch.fetch_positionei( p_position_id => p_position_id,
843 p_information_type => 'GHR_US_POS_GRP1',
844 p_date_effective => p_effective_date,
845 p_pos_ei_data => l_positionei_rec
846 );
847 l_office_symbol_id := l_positionei_rec.poei_information3;
848
849 if l_office_symbol_id is not null then
850 open csr_lkp_code('GHR_US_OFFICE_SYMBOL', l_office_symbol_id);
851 fetch csr_lkp_code into l_office_symbol_name;
852 if csr_lkp_code%FOUND then
853 p_office_symbol_name := 'Office Symbol : ' || l_office_symbol_name;
854 end if;
855 close csr_lkp_code;
856 end if;
857 --
858 -- get retained details
859 --
860 begin
861 l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details
862 (p_person_id => p_person_id
863 ,p_effective_date => p_effective_date);
864 exception
865 When ghr_pay_calc.pay_calc_message then
866 null;
867 end;
868 --
869 -- Get Performance ratings
870 --
871 -- Get it from GHR_US_PAR_PERF_APPRAISAL
872 open cur_par_extra;
873 fetch cur_par_extra into l_rating_id,l_rating_date;
874 close cur_par_extra;
875 --
876 if l_rating_id is not null then
877 open csr_lkp_code('GHR_US_RATING_OF_RECORD', l_rating_id);
878 fetch csr_lkp_code into l_rating;
879 close csr_lkp_code;
880 end if;
881 --
882 open csr_rgp;
883 fetch csr_rgp into
884 l_routing_group_name,
885 l_rgp_description;
886 close csr_rgp;
887 --
888
889 open cur_pay_plan;
890 fetch cur_pay_plan into
891 l_cur_pay_plan,
892 l_cur_grade,
893 l_cur_step_rate,
894 l_new_step,
895 l_new_sal;
896 close cur_pay_plan;
897
898 l_wgi_date := to_char(p_effective_date, 'YYYY/MM/DD');
899 /*
900 l_cur_pay_plan := rpad(nvl(l_cur_pay_plan,' '), 5, ' ');
901 l_cur_grade := rpad(nvl(l_cur_grade, ' '), 5, ' ');
902 l_cur_step_rate := rpad(nvl(l_cur_step_rate,' '), 5, ' ');
903 */
904 -- WF Changes ADDED THE FOLLOWING IF CONDITION.
905 IF l_retained_grade_rec.pay_plan IS NULL AND
906 l_retained_grade_rec.grade_or_level IS NULL AND
907 l_retained_grade_rec.pay_plan IS NULL AND
908 l_retained_grade_rec.grade_or_level IS NULL AND
909 l_retained_grade_rec.step_or_rate IS NULL AND
910 l_retained_grade_rec.pay_basis IS NULL AND
911 l_retained_grade_rec.user_table_id IS NULL AND
912 l_retained_grade_rec.locality_percent IS NULL THEN
913
914
915 l_line1 := l_cur_pay_plan || '-' || l_cur_grade;-- WF Changes
916 l_line7 := l_new_step;
917 ELSE
918 l_line1 := l_retained_grade_rec.pay_plan || '-' || l_retained_grade_rec.grade_or_level;
919 IF l_retained_grade_rec.temp_step IS NOT NULL THEN
920 l_line7 := l_retained_grade_rec.temp_step;
921 ELSE
922 l_line7 := l_retained_grade_rec.step_or_RATE;
923 END IF;
924 END IF;
925
926 -- l_line1 := l_cur_pay_plan || '-' || l_cur_grade || '-' || l_cur_step_rate;-- WF Changes
927 l_line2 := l_retained_grade_rec.pay_plan || '-' || l_retained_grade_rec.grade_or_level || '-' || l_retained_grade_rec.step_or_rate ;
928 l_line3 := l_rating; -- Performance Rating
929 l_line4 := fnd_date.date_to_displaydate(fnd_date.canonical_to_date(l_rating_date)); -- Date Effective
930 l_line5 := fnd_date.date_to_displaydate(fnd_date.canonical_to_date(l_wgi_date)); -- WGI Pay Date
931 l_line6 := l_new_sal; -- New Salary
932 --WF changes Change this line7 to pick temp step/reteained step/step in position.
933 -- l_line7 := l_new_step; -- New Step
934
935 p_line1 := l_line1;
936 p_line2 := l_line2;
937 p_line3 := l_line3;
938 p_line4 := l_line4;
939 p_line5 := l_line5;
940 p_line6 := l_line6;
941 p_line7 := l_line7;
942 p_routing_group := l_routing_group_name || ' - ' || l_rgp_description;
943 --
944 Exception
945 -- NOCOPY Changes.
946 WHEN OTHERS THEN
947 p_office_symbol_name := NULL;
948 p_line1 := NULL;
949 p_line2 := NULL;
950 p_line3 := NULL;
951 p_line4 := NULL;
952 p_line5 := NULL;
953 p_line6 := NULL;
954 p_line7 := NULL;
955 p_routing_group := NULL;
956 raise;
957 End SetDestination;
958 --
959 --
960 --
961 PROCEDURE UpdateRoutingHistory( itemtype in varchar2,
962 itemkey in varchar2,
963 actid in number,
964 funcmode in varchar2,
965 result in out nocopy varchar2) is
966 Begin
967 NULL;
968 End UpdateRoutingHistory;
969 --
970 --
971 --
972 PROCEDURE CallUpdateToHR( itemtype in varchar2,
973 itemkey in varchar2,
974 actid in number,
975 funcmode in varchar2,
976 result in out nocopy varchar2) is
977 -- NOCOPY Changes
978 l_result VARCHAR2(250);
979 begin
980 -- NOCOPY Changes
981 l_result := result;
982 if funcmode = 'RUN' then
983 --Submit to Update to HR
984 update_sf52_action_taken(p_pa_request_id => itemkey,
985 p_routing_group_id => '',
986 p_groupbox_id => '',
987 p_action_taken => 'UPDATE_HR',
988 p_gbx_user_id => '');
989 result := 'COMPLETE:YES';
990 return;
991 end if;
992 --
993 result := '';
994 return;
995 --
996 exception
997 when others then
998 -- NOCOPY Changes
999 result := l_result;
1000 wf_engine.SetItemAttrText ( itemtype => ItemType,
1001 itemkey => Itemkey,
1002 aname => 'ERROR_MSG',
1003 avalue => 'Update to HR failed. ' || substr(sqlerrm,1,1000) );
1004 result := 'COMPLETE:NO';
1005 --
1006 end CallUpdateToHR;
1007 --
1008 --
1009 PROCEDURE EndWGIProcess( itemtype in varchar2,
1010 itemkey in varchar2,
1011 actid in number,
1012 funcmode in varchar2,
1013 result in out nocopy varchar2) is
1014 -- NOCOPY Changes
1015 l_result VARCHAR2(250);
1016 begin
1017 -- NOCOPY Changes
1018 l_result := result;
1019 if funcmode = 'RUN' then
1020 result := 'COMPLETE:COMPLETED';
1021 return;
1022 end if;
1023 --
1024 --
1025 -- Other execution modes may be created in the future.
1026 -- Activity indicates that it does not implement a mode
1027 -- by returning null
1028 --
1029 result := '';
1030 return;
1031 --
1032 exception
1033 when others then
1034 -- NOCOPY Changes
1035 result := l_result;
1036 -- The line below records this function call in the error system
1037 -- in the case of an exception.
1038 wf_core.context('WGI', 'ghr_wf_pd_pkg.EndWGIProcess',itemtype, itemkey, to_char(actid), funcmode);
1039 raise;
1040 --
1041 end EndWGIProcess;
1042 --
1043 --
1044 procedure update_sf52_action_taken(p_pa_request_id in ghr_pa_requests.pa_request_id%TYPE
1045 ,p_routing_group_id in ghr_pa_requests.routing_group_id%type
1046 ,p_groupbox_id in ghr_pa_routing_history.groupbox_id%type
1047 ,p_action_taken in ghr_pa_routing_history.action_taken%TYPE
1048 ,p_gbx_user_id in ghr_pois.person_id%TYPE)
1049 is
1050 --
1051 Cursor c_fnd_sessions is
1052 Select 1
1053 From fnd_sessions
1054 where session_id = userenv('sessionid');
1055 -- Local variables
1056 --
1057 l_exists boolean := false;
1058 l_validate boolean := false;
1059 l_noa_family_code ghr_pa_requests.noa_family_code%TYPE;
1060 l_routing_group_id ghr_pa_requests.routing_group_id%TYPE;
1061 l_par_object_version_number ghr_pa_requests.object_version_number%TYPE;
1062 l_proposed_effective_asap_flag ghr_pa_requests.proposed_effective_asap_flag%TYPE;
1063 l_academic_discipline ghr_pa_requests.academic_discipline%TYPE;
1064 l_additional_info_person_id ghr_pa_requests.additional_info_person_id%TYPE;
1065 l_additional_info_tel_number ghr_pa_requests.additional_info_tel_number%TYPE;
1066 l_altered_pa_request_id ghr_pa_requests.altered_pa_request_id%TYPE;
1067 l_annuitant_indicator ghr_pa_requests.annuitant_indicator%TYPE;
1068 l_annuitant_indicator_desc ghr_pa_requests.annuitant_indicator_desc%TYPE;
1069 l_appropriation_code1 ghr_pa_requests.appropriation_code1%TYPE;
1070 l_appropriation_code2 ghr_pa_requests.appropriation_code2%TYPE;
1071 l_authorized_by_person_id ghr_pa_requests.authorized_by_person_id%TYPE;
1072 l_authorized_by_title ghr_pa_requests.authorized_by_title%TYPE;
1073 l_award_amount ghr_pa_requests.award_amount%TYPE;
1074 l_award_uom ghr_pa_requests.award_uom%TYPE;
1075 l_bargaining_unit_status ghr_pa_requests.bargaining_unit_status%TYPE;
1076 l_citizenship ghr_pa_requests.citizenship%TYPE;
1077 l_concurrence_date ghr_pa_requests.concurrence_date%TYPE;
1078 l_custom_pay_calc_flag ghr_pa_requests.custom_pay_calc_flag%TYPE;
1079 l_duty_station_code ghr_pa_requests.duty_station_code%TYPE;
1080 l_duty_station_desc ghr_pa_requests.duty_station_desc%TYPE;
1081 l_duty_station_id ghr_pa_requests.duty_station_id%TYPE;
1082 l_duty_station_location_id ghr_pa_requests.duty_station_location_id%TYPE;
1083 l_education_level ghr_pa_requests.education_level%TYPE;
1084 l_effective_date ghr_pa_requests.effective_date%TYPE;
1085 l_employee_assignment_id ghr_pa_requests.employee_assignment_id%TYPE;
1086 l_employee_date_of_birth ghr_pa_requests.employee_date_of_birth%TYPE;
1087 l_employee_first_name ghr_pa_requests.employee_first_name%TYPE;
1088 l_employee_last_name ghr_pa_requests.employee_last_name%TYPE;
1089 l_employee_middle_names ghr_pa_requests.employee_middle_names%TYPE;
1090 l_employee_national_identifier ghr_pa_requests.employee_national_identifier%TYPE;
1091 l_fegli ghr_pa_requests.fegli%TYPE;
1092 l_fegli_desc ghr_pa_requests.fegli_desc%TYPE;
1093 l_first_action_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
1094 l_first_action_la_code2 ghr_pa_requests.first_action_la_code2%TYPE;
1095 l_first_action_la_desc1 ghr_pa_requests.first_action_la_desc1%TYPE;
1096 l_first_action_la_desc2 ghr_pa_requests.first_action_la_desc2%TYPE;
1097 l_first_noa_cancel_or_correct ghr_pa_requests.first_noa_cancel_or_correct%TYPE;
1098 l_first_noa_code ghr_pa_requests.first_noa_code%TYPE;
1099 l_first_noa_desc ghr_pa_requests.first_noa_desc%TYPE;
1100 l_first_noa_id ghr_pa_requests.first_noa_id%TYPE;
1101 l_first_noa_pa_request_id ghr_pa_requests.first_noa_pa_request_id%TYPE;
1102 l_flsa_category ghr_pa_requests.flsa_category%TYPE;
1103 l_forwarding_address_line1 ghr_pa_requests.forwarding_address_line1%TYPE;
1104 l_forwarding_address_line2 ghr_pa_requests.forwarding_address_line2%TYPE;
1105 l_forwarding_address_line3 ghr_pa_requests.forwarding_address_line3%TYPE;
1106 l_forwarding_country ghr_pa_requests.forwarding_country%TYPE;
1107 l_forwarding_postal_code ghr_pa_requests.forwarding_postal_code%TYPE;
1108 l_forwarding_region_2 ghr_pa_requests.forwarding_region_2%TYPE;
1109 l_forwarding_town_or_city ghr_pa_requests.forwarding_town_or_city%TYPE;
1110 l_from_adj_basic_pay ghr_pa_requests.from_adj_basic_pay%TYPE;
1111 l_from_basic_pay ghr_pa_requests.from_basic_pay%TYPE;
1112 l_from_grade_or_level ghr_pa_requests.from_grade_or_level%TYPE;
1113 l_from_locality_adj ghr_pa_requests.from_locality_adj%TYPE;
1114 l_from_occ_code ghr_pa_requests.from_occ_code%TYPE;
1115 l_from_other_pay_amount ghr_pa_requests.from_other_pay_amount%TYPE;
1116 l_from_pay_basis ghr_pa_requests.from_pay_basis%TYPE;
1117 l_from_pay_plan ghr_pa_requests.from_pay_plan%TYPE;
1118 -- FWFA Changes Bug#4444609
1119 l_input_pay_rate_determinant ghr_pa_requests.input_pay_rate_determinant%TYPE;
1120 l_from_pay_table_identifier ghr_pa_requests.from_pay_table_identifier%TYPE;
1121 -- FWFA Changes
1122 l_from_position_id ghr_pa_requests.from_position_id%TYPE;
1123 l_from_position_org_line1 ghr_pa_requests.from_position_org_line1%TYPE;
1124 l_from_position_org_line2 ghr_pa_requests.from_position_org_line2%TYPE;
1125 l_from_position_org_line3 ghr_pa_requests.from_position_org_line3%TYPE;
1126 l_from_position_org_line4 ghr_pa_requests.from_position_org_line4%TYPE;
1127 l_from_position_org_line5 ghr_pa_requests.from_position_org_line5%TYPE;
1128 l_from_position_org_line6 ghr_pa_requests.from_position_org_line6%TYPE;
1129 l_from_position_number ghr_pa_requests.from_position_number%TYPE;
1130 l_from_position_seq_no ghr_pa_requests.from_position_seq_no%TYPE;
1131 l_from_position_title ghr_pa_requests.from_position_title%TYPE;
1132 l_from_step_or_rate ghr_pa_requests.from_step_or_rate%TYPE;
1133 l_from_total_salary ghr_pa_requests.from_total_salary%TYPE;
1134 l_functional_class ghr_pa_requests.functional_class%TYPE;
1135 l_notepad ghr_pa_requests.notepad%TYPE;
1136 l_part_time_hours ghr_pa_requests.part_time_hours%TYPE;
1137 l_pay_rate_determinant ghr_pa_requests.pay_rate_determinant%TYPE;
1138 l_person_id ghr_pa_requests.person_id%TYPE;
1139 l_position_occupied ghr_pa_requests.position_occupied%TYPE;
1140 l_proposed_effective_date ghr_pa_requests.proposed_effective_date%TYPE;
1141 l_requested_by_person_id ghr_pa_requests.requested_by_person_id%TYPE;
1142 l_requested_by_title ghr_pa_requests.requested_by_title%TYPE;
1143 l_requested_date ghr_pa_requests.requested_date%TYPE;
1144 l_requesting_office_remarks_de ghr_pa_requests.requesting_office_remarks_desc%TYPE;
1145 l_requesting_office_remarks_fl ghr_pa_requests.requesting_office_remarks_flag%TYPE;
1146 l_request_number ghr_pa_requests.request_number%TYPE;
1147 l_resign_and_retire_reason_des ghr_pa_requests.resign_and_retire_reason_desc%TYPE;
1148 l_retirement_plan ghr_pa_requests.retirement_plan%TYPE;
1149 l_retirement_plan_desc ghr_pa_requests.retirement_plan_desc%TYPE;
1150 l_second_action_la_code1 ghr_pa_requests.second_action_la_code1%TYPE;
1151 l_second_action_la_code2 ghr_pa_requests.second_action_la_code2%TYPE;
1152 l_second_action_la_desc1 ghr_pa_requests.second_action_la_desc1%TYPE;
1153 l_second_action_la_desc2 ghr_pa_requests.second_action_la_desc2%TYPE;
1154 l_second_noa_cancel_or_correct ghr_pa_requests.second_noa_cancel_or_correct%TYPE;
1155 l_second_noa_code ghr_pa_requests.second_noa_code%TYPE;
1156 l_second_noa_desc ghr_pa_requests.second_noa_desc%TYPE;
1157 l_second_noa_id ghr_pa_requests.second_noa_id%TYPE;
1158 l_second_noa_pa_request_id ghr_pa_requests.second_noa_pa_request_id%TYPE;
1159 l_service_comp_date ghr_pa_requests.service_comp_date%TYPE;
1160 l_supervisory_status ghr_pa_requests.supervisory_status%TYPE;
1161 l_tenure ghr_pa_requests.tenure%TYPE;
1162 l_to_adj_basic_pay ghr_pa_requests.to_adj_basic_pay%TYPE;
1163 l_to_basic_pay ghr_pa_requests.to_basic_pay%TYPE;
1164 l_to_grade_id ghr_pa_requests.to_grade_id%TYPE;
1165 l_to_grade_or_level ghr_pa_requests.to_grade_or_level%TYPE;
1166 l_to_job_id ghr_pa_requests.to_job_id%TYPE;
1167 l_to_locality_adj ghr_pa_requests.to_locality_adj%TYPE;
1168 l_to_occ_code ghr_pa_requests.to_occ_code%TYPE;
1169 l_to_organization_id ghr_pa_requests.to_organization_id%TYPE;
1170 l_to_other_pay_amount ghr_pa_requests.to_other_pay_amount%TYPE;
1171 l_to_au_overtime ghr_pa_requests.to_au_overtime%TYPE;
1172 l_to_auo_premium_pay_indicator ghr_pa_requests.to_auo_premium_pay_indicator%TYPE;
1173 l_to_availability_pay ghr_pa_requests.to_availability_pay%TYPE;
1174 l_to_ap_premium_pay_indicator ghr_pa_requests.to_ap_premium_pay_indicator%TYPE;
1175 l_to_retention_allowance ghr_pa_requests.to_retention_allowance%TYPE;
1176 l_to_supervisory_differential ghr_pa_requests.to_supervisory_differential%TYPE;
1177 l_to_staffing_differential ghr_pa_requests.to_staffing_differential%TYPE;
1178 l_to_pay_basis ghr_pa_requests.to_pay_basis%TYPE;
1179 l_to_pay_plan ghr_pa_requests.to_pay_plan%TYPE;
1180 -- FWFA Changes Bug 4444609
1181 l_to_pay_table_identifier ghr_pa_requests.to_pay_table_identifier%TYPE;
1182 -- FWFA Changes
1183 l_to_position_id ghr_pa_requests.to_position_id%TYPE;
1184 l_to_position_org_line1 ghr_pa_requests.to_position_org_line1%TYPE;
1185 l_to_position_org_line2 ghr_pa_requests.to_position_org_line2%TYPE;
1186 l_to_position_org_line3 ghr_pa_requests.to_position_org_line3%TYPE;
1187 l_to_position_org_line4 ghr_pa_requests.to_position_org_line4%TYPE;
1188 l_to_position_org_line5 ghr_pa_requests.to_position_org_line5%TYPE;
1189 l_to_position_org_line6 ghr_pa_requests.to_position_org_line6%TYPE;
1190 l_to_position_number ghr_pa_requests.to_position_number%TYPE;
1191 l_to_position_seq_no ghr_pa_requests.to_position_seq_no%TYPE;
1192 l_to_position_title ghr_pa_requests.to_position_title%TYPE;
1193 l_to_step_or_rate ghr_pa_requests.to_step_or_rate%TYPE;
1194 l_to_total_salary ghr_pa_requests.to_total_salary%TYPE;
1195 l_veterans_preference ghr_pa_requests.veterans_preference%TYPE;
1196 l_veterans_pref_for_rif ghr_pa_requests.veterans_pref_for_rif%TYPE;
1197 l_veterans_status ghr_pa_requests.veterans_status%TYPE;
1198 l_work_schedule ghr_pa_requests.work_schedule%TYPE;
1199 l_work_schedule_desc ghr_pa_requests.work_schedule_desc%TYPE;
1200 l_year_degree_attained ghr_pa_requests.year_degree_attained%TYPE;
1201 l_first_noa_information1 ghr_pa_requests.first_noa_information1%TYPE;
1202 l_first_noa_information2 ghr_pa_requests.first_noa_information2%TYPE;
1203 l_first_noa_information3 ghr_pa_requests.first_noa_information3%TYPE;
1204 l_first_noa_information4 ghr_pa_requests.first_noa_information4%TYPE;
1205 l_first_noa_information5 ghr_pa_requests.first_noa_information5%TYPE;
1206 l_second_lac1_information1 ghr_pa_requests.second_lac1_information1%TYPE;
1207 l_second_lac1_information2 ghr_pa_requests.second_lac1_information2%TYPE;
1208 l_second_lac1_information3 ghr_pa_requests.second_lac1_information3%TYPE;
1209 l_second_lac1_information4 ghr_pa_requests.second_lac1_information4%TYPE;
1210 l_second_lac1_information5 ghr_pa_requests.second_lac1_information5%TYPE;
1211 l_second_lac2_information1 ghr_pa_requests.second_lac2_information1%TYPE;
1212 l_second_lac2_information2 ghr_pa_requests.second_lac2_information2%TYPE;
1213 l_second_lac2_information3 ghr_pa_requests.second_lac2_information3%TYPE;
1214 l_second_lac2_information4 ghr_pa_requests.second_lac2_information4%TYPE;
1215 l_second_lac2_information5 ghr_pa_requests.second_lac2_information5%TYPE;
1216 l_second_noa_information1 ghr_pa_requests.second_noa_information1%TYPE;
1217 l_second_noa_information2 ghr_pa_requests.second_noa_information2%TYPE;
1218 l_second_noa_information3 ghr_pa_requests.second_noa_information3%TYPE;
1219 l_second_noa_information4 ghr_pa_requests.second_noa_information4%TYPE;
1220 l_second_noa_information5 ghr_pa_requests.second_noa_information5%TYPE;
1221 l_first_lac1_information1 ghr_pa_requests.first_lac1_information1%TYPE;
1222 l_first_lac1_information2 ghr_pa_requests.first_lac1_information2%TYPE;
1223 l_first_lac1_information3 ghr_pa_requests.first_lac1_information3%TYPE;
1224 l_first_lac1_information4 ghr_pa_requests.first_lac1_information4%TYPE;
1225 l_first_lac1_information5 ghr_pa_requests.first_lac1_information5%TYPE;
1226 l_first_lac2_information1 ghr_pa_requests.first_lac2_information1%TYPE;
1227 l_first_lac2_information2 ghr_pa_requests.first_lac2_information2%TYPE;
1228 l_first_lac2_information3 ghr_pa_requests.first_lac2_information3%TYPE;
1229 l_first_lac2_information4 ghr_pa_requests.first_lac2_information4%TYPE;
1230 l_first_lac2_information5 ghr_pa_requests.first_lac2_information5%TYPE;
1231 l_u_attachment_modified_flag varchar2(30);
1232 l_u_approved_flag varchar2(30);
1233 l_u_user_name_acted_on ghr_pa_routing_history.user_name%TYPE;
1234 l_i_user_name_routed_to ghr_pa_routing_history.user_name%TYPE;
1235 l_i_groupbox_id ghr_pa_routing_history.groupbox_id%TYPE;
1236 l_i_routing_list_id number;
1237 l_i_routing_seq_number ghr_pa_routing_history.routing_seq_number%TYPE;
1238 l_u_prh_object_version_number number;
1239 l_i_pa_routing_history_id number;
1240 l_i_prh_object_version_number number;
1241 l_u_approval_status ghr_pa_routing_history.approval_status%TYPE;
1242 l_approving_official_full_name ghr_pa_requests.approving_official_full_name%TYPE;
1243 l_approving_official_work_titl ghr_pa_requests.approving_official_work_title%TYPE;
1244 l_approval_date ghr_pa_requests.approval_date%TYPE;
1245 --
1246 l_to_retention_allow_percentag ghr_pa_requests.to_retention_allow_percentage%TYPE;
1247 l_to_supervisory_diff_percenta ghr_pa_requests.to_supervisory_diff_percentage%TYPE;
1248 l_to_staffing_diff_percentage ghr_pa_requests.to_staffing_diff_percentage%TYPE;
1249 l_award_percentage ghr_pa_requests.award_percentage%TYPE;
1250 l_commit number;
1251
1252 --
1253 -- Added cursor to select object version number and routing history id
1254 cursor csr_get_parh_info is
1255 select pa_routing_history_id, object_version_number
1256 from ghr_pa_routing_history
1257 where pa_request_id = p_pa_request_id;
1258 --
1259 cursor csr_par_details is
1260 select
1261 noa_family_code,
1262 routing_group_id,
1263 object_version_number,
1264 proposed_effective_asap_flag,
1265 academic_discipline,
1266 additional_info_person_id,
1267 additional_info_tel_number,
1268 altered_pa_request_id,
1269 annuitant_indicator,
1270 annuitant_indicator_desc,
1271 appropriation_code1,
1272 appropriation_code2,
1273 authorized_by_person_id,
1274 authorized_by_title,
1275 award_amount,
1276 award_uom,
1277 bargaining_unit_status,
1278 citizenship,
1279 concurrence_date,
1280 custom_pay_calc_flag,
1281 duty_station_code,
1282 duty_station_desc,
1283 duty_station_id,
1284 duty_station_location_id,
1285 education_level,
1286 effective_date,
1287 employee_assignment_id,
1288 employee_date_of_birth,
1289 employee_first_name,
1290 employee_last_name,
1291 employee_middle_names,
1292 employee_national_identifier,
1293 fegli,
1294 fegli_desc,
1295 first_action_la_code1,
1296 first_action_la_code2,
1297 first_action_la_desc1,
1298 first_action_la_desc2,
1299 first_noa_cancel_or_correct,
1300 first_noa_code,
1301 first_noa_desc,
1302 first_noa_id,
1303 first_noa_pa_request_id,
1304 flsa_category,
1305 forwarding_address_line1,
1306 forwarding_address_line2,
1307 forwarding_address_line3,
1308 forwarding_country,
1309 forwarding_postal_code,
1310 forwarding_region_2,
1311 forwarding_town_or_city,
1312 from_adj_basic_pay,
1313 from_basic_pay,
1314 from_grade_or_level,
1315 from_locality_adj,
1316 from_occ_code,
1317 from_other_pay_amount,
1318 from_pay_basis,
1319 from_pay_plan,
1320 -- FWFA Changes Bug#4444609
1321 input_pay_rate_determinant,
1322 from_pay_table_identifier,
1323 -- FWFA Changes
1324 from_position_id,
1325 from_position_org_line1,
1326 from_position_org_line2,
1327 from_position_org_line3,
1328 from_position_org_line4,
1329 from_position_org_line5,
1330 from_position_org_line6,
1331 from_position_number,
1332 from_position_seq_no,
1333 from_position_title,
1334 from_step_or_rate,
1335 from_total_salary,
1336 functional_class,
1337 notepad,
1338 part_time_hours,
1339 pay_rate_determinant,
1340 person_id,
1341 position_occupied,
1342 proposed_effective_date,
1343 requested_by_person_id,
1344 requested_by_title,
1345 requested_date,
1346 requesting_office_remarks_desc,
1347 requesting_office_remarks_flag,
1348 request_number,
1349 resign_and_retire_reason_desc,
1350 retirement_plan,
1351 retirement_plan_desc,
1352 second_action_la_code1,
1353 second_action_la_code2,
1354 second_action_la_desc1,
1355 second_action_la_desc2,
1356 second_noa_cancel_or_correct,
1357 second_noa_code,
1358 second_noa_desc,
1359 second_noa_id,
1360 second_noa_pa_request_id,
1361 service_comp_date,
1362 supervisory_status,
1363 tenure,
1364 to_adj_basic_pay,
1365 to_basic_pay,
1366 to_grade_id,
1367 to_grade_or_level,
1368 to_job_id,
1369 to_locality_adj,
1370 to_occ_code,
1371 to_organization_id,
1372 to_other_pay_amount,
1373 to_au_overtime,
1374 to_auo_premium_pay_indicator,
1375 to_availability_pay,
1376 to_ap_premium_pay_indicator,
1377 to_retention_allowance,
1378 to_supervisory_differential,
1379 to_staffing_differential,
1380 to_pay_basis,
1381 to_pay_plan,
1382 -- FWFA Changes Bug#4444609
1383 to_pay_table_identifier,
1384 -- FWFA Changes
1385 to_position_id,
1386 to_position_org_line1,
1387 to_position_org_line2,
1388 to_position_org_line3,
1389 to_position_org_line4,
1390 to_position_org_line5,
1391 to_position_org_line6,
1392 to_position_number,
1393 to_position_seq_no,
1394 to_position_title,
1395 to_step_or_rate,
1396 to_total_salary,
1397 veterans_preference,
1398 veterans_pref_for_rif,
1399 veterans_status,
1400 work_schedule,
1401 work_schedule_desc,
1402 year_degree_attained,
1403 first_noa_information1,
1404 first_noa_information2,
1405 first_noa_information3,
1406 first_noa_information4,
1407 first_noa_information5,
1408 second_lac1_information1,
1409 second_lac1_information2,
1410 second_lac1_information3,
1411 second_lac1_information4,
1412 second_lac1_information5,
1413 second_lac2_information1,
1414 second_lac2_information2,
1415 second_lac2_information3,
1416 second_lac2_information4,
1417 second_lac2_information5,
1418 second_noa_information1,
1419 second_noa_information2,
1420 second_noa_information3,
1421 second_noa_information4,
1422 second_noa_information5,
1423 first_lac1_information1,
1424 first_lac1_information2,
1425 first_lac1_information3,
1426 first_lac1_information4,
1427 first_lac1_information5,
1428 first_lac2_information1,
1429 first_lac2_information2,
1430 first_lac2_information3,
1431 first_lac2_information4,
1432 first_lac2_information5,
1433 approval_date,
1434 approving_official_full_name,
1435 approving_official_work_title,
1436 to_retention_allow_percentage,
1437 to_supervisory_diff_percentage,
1438 to_staffing_diff_percentage,
1439 award_percentage
1440 -- u_attachment_modified_flag,
1441 -- u_approved_flag,
1442 -- u_user_name_acted_on,
1443 -- u_user_name_acted_on,
1444 -- u_action_taken,
1445 -- i_user_name_routed_to,
1446 -- i_groupbox_id
1447 -- i_routing_list_id,
1448 -- i_routing_seq_number
1449 from ghr_pa_requests
1450 where pa_request_id = p_pa_request_id;
1451 --
1452 begin
1453 -- Replacing the insert dml with a call to the dt_fndate.change_ses_date procedure .
1454
1455 dt_fndate.change_ses_date(p_ses_date => trunc(sysdate),
1456 p_commit => l_commit
1457 );
1458 -- The previous code did not perform a commit after writing to fnd_sessions
1459 -- Hence not issuing a Commit based on the value of the l_commit out variable
1460
1461
1462
1463
1464 open csr_par_details ;
1465 fetch csr_par_details into
1466 l_noa_family_code,
1467 l_routing_group_id,
1468 l_par_object_version_number,
1469 l_proposed_effective_asap_flag,
1470 l_academic_discipline,
1471 l_additional_info_person_id,
1472 l_additional_info_tel_number,
1473 l_altered_pa_request_id,
1474 l_annuitant_indicator,
1475 l_annuitant_indicator_desc,
1476 l_appropriation_code1,
1477 l_appropriation_code2,
1478 l_authorized_by_person_id,
1479 l_authorized_by_title,
1480 l_award_amount,
1481 l_award_uom,
1482 l_bargaining_unit_status,
1483 l_citizenship,
1484 l_concurrence_date,
1485 l_custom_pay_calc_flag,
1486 l_duty_station_code,
1487 l_duty_station_desc,
1488 l_duty_station_id,
1489 l_duty_station_location_id,
1490 l_education_level,
1491 l_effective_date,
1492 l_employee_assignment_id,
1493 l_employee_date_of_birth,
1494 l_employee_first_name,
1495 l_employee_last_name,
1496 l_employee_middle_names,
1497 l_employee_national_identifier,
1498 l_fegli,
1499 l_fegli_desc,
1500 l_first_action_la_code1,
1501 l_first_action_la_code2,
1502 l_first_action_la_desc1,
1503 l_first_action_la_desc2,
1504 l_first_noa_cancel_or_correct,
1505 l_first_noa_code,
1506 l_first_noa_desc,
1507 l_first_noa_id,
1508 l_first_noa_pa_request_id,
1509 l_flsa_category,
1510 l_forwarding_address_line1,
1511 l_forwarding_address_line2,
1512 l_forwarding_address_line3,
1513 l_forwarding_country,
1514 l_forwarding_postal_code,
1515 l_forwarding_region_2,
1516 l_forwarding_town_or_city,
1517 l_from_adj_basic_pay,
1518 l_from_basic_pay,
1519 l_from_grade_or_level,
1520 l_from_locality_adj,
1521 l_from_occ_code,
1522 l_from_other_pay_amount,
1523 l_from_pay_basis,
1524 l_from_pay_plan,
1525 -- FWFA Changes Bug#4444609
1526 l_input_pay_rate_determinant,
1527 l_from_pay_table_identifier,
1528 -- FWFA Changes
1529 l_from_position_id,
1530 l_from_position_org_line1,
1531 l_from_position_org_line2,
1532 l_from_position_org_line3,
1533 l_from_position_org_line4,
1534 l_from_position_org_line5,
1535 l_from_position_org_line6,
1536 l_from_position_number,
1537 l_from_position_seq_no,
1538 l_from_position_title,
1539 l_from_step_or_rate,
1540 l_from_total_salary,
1541 l_functional_class,
1542 l_notepad,
1543 l_part_time_hours,
1544 l_pay_rate_determinant,
1545 l_person_id,
1546 l_position_occupied,
1547 l_proposed_effective_date,
1548 l_requested_by_person_id,
1549 l_requested_by_title,
1550 l_requested_date,
1551 l_requesting_office_remarks_de,
1552 l_requesting_office_remarks_fl ,
1553 l_request_number,
1554 l_resign_and_retire_reason_des,
1555 l_retirement_plan,
1556 l_retirement_plan_desc,
1557 l_second_action_la_code1,
1558 l_second_action_la_code2,
1559 l_second_action_la_desc1,
1560 l_second_action_la_desc2,
1561 l_second_noa_cancel_or_correct,
1562 l_second_noa_code,
1563 l_second_noa_desc,
1564 l_second_noa_id,
1565 l_second_noa_pa_request_id,
1566 l_service_comp_date,
1567 l_supervisory_status,
1568 l_tenure,
1569 l_to_adj_basic_pay,
1570 l_to_basic_pay,
1571 l_to_grade_id,
1572 l_to_grade_or_level,
1573 l_to_job_id,
1574 l_to_locality_adj,
1575 l_to_occ_code,
1576 l_to_organization_id,
1577 l_to_other_pay_amount,
1578 l_to_au_overtime,
1579 l_to_auo_premium_pay_indicator,
1580 l_to_availability_pay,
1581 l_to_ap_premium_pay_indicator,
1582 l_to_retention_allowance,
1583 l_to_supervisory_differential,
1584 l_to_staffing_differential,
1585 l_to_pay_basis,
1586 l_to_pay_plan,
1587 -- FWFA Changes Bug#4444609
1588 l_to_pay_Table_identifier,
1589 -- FWFA Changes
1590 l_to_position_id,
1591 l_to_position_org_line1,
1592 l_to_position_org_line2,
1593 l_to_position_org_line3,
1594 l_to_position_org_line4,
1595 l_to_position_org_line5,
1596 l_to_position_org_line6,
1597 l_to_position_number,
1598 l_to_position_seq_no,
1599 l_to_position_title,
1600 l_to_step_or_rate,
1601 l_to_total_salary,
1602 l_veterans_preference,
1603 l_veterans_pref_for_rif,
1604 l_veterans_status,
1605 l_work_schedule,
1606 l_work_schedule_desc,
1607 l_year_degree_attained,
1608 l_first_noa_information1,
1609 l_first_noa_information2,
1610 l_first_noa_information3,
1611 l_first_noa_information4,
1612 l_first_noa_information5,
1613 l_second_lac1_information1,
1614 l_second_lac1_information2,
1615 l_second_lac1_information3,
1616 l_second_lac1_information4,
1617 l_second_lac1_information5,
1618 l_second_lac2_information1,
1619 l_second_lac2_information2,
1620 l_second_lac2_information3,
1621 l_second_lac2_information4,
1622 l_second_lac2_information5,
1623 l_second_noa_information1,
1624 l_second_noa_information2,
1625 l_second_noa_information3,
1626 l_second_noa_information4,
1627 l_second_noa_information5,
1628 l_first_lac1_information1,
1629 l_first_lac1_information2,
1630 l_first_lac1_information3,
1631 l_first_lac1_information4,
1632 l_first_lac1_information5,
1633 l_first_lac2_information1,
1634 l_first_lac2_information2,
1635 l_first_lac2_information3,
1636 l_first_lac2_information4,
1637 l_first_lac2_information5,
1638 l_approval_date,
1639 l_approving_official_full_name,
1640 l_approving_official_work_titl,
1641 l_to_retention_allow_percentag,
1642 l_to_supervisory_diff_percenta,
1643 l_to_staffing_diff_percentage,
1644 l_award_percentage;
1645 -- l_u_attachment_modified_flag,
1646 -- l_u_approved_flag,
1647 -- l_u_user_name_acted_on,
1648 -- l_u_action_taken,
1649 -- l_i_user_name_routed_to,
1650 -- l_i_groupbox_id
1651 -- l_i_routing_list_id,
1652 -- l_i_routing_seq_number
1653 if csr_par_details%notfound then
1654 -- if the cursor does not return a row then we must set the out
1655 -- parameter to null
1656 null;
1657 end if;
1658 -- close the cursor
1659 close csr_par_details;
1660 if p_routing_group_id is not NULL then
1661 l_routing_group_id := p_routing_group_id;
1662 end if;
1663 l_u_approval_status := '';
1664 if p_groupbox_id is not NULL then
1665 l_i_groupbox_id := p_groupbox_id;
1666 -- Get the Electronic Auth. values
1667 l_u_approval_status := 'APPROVE';
1668 l_approval_date := sysdate;
1669 l_approving_official_full_name := ghr_pa_requests_pkg.get_full_name_fml (p_gbx_user_id);
1670 l_approving_official_work_titl :=
1671 ghr_pa_requests_pkg.get_position_work_title (p_person_id => TO_CHAR(p_gbx_user_id));
1672 end if;
1673 -- Set Request Number
1674 l_request_number := 'WGI:' || p_pa_request_id;
1675 ghr_sf52_api.update_sf52(
1676 p_validate => l_validate,
1677 p_pa_request_id => p_pa_request_id,
1678 p_noa_family_code => l_noa_family_code,
1679 p_routing_group_id => l_routing_group_id,
1680 p_par_object_version_number => l_par_object_version_number,
1681 p_proposed_effective_asap_flag => l_proposed_effective_asap_flag,
1682 p_academic_discipline => l_academic_discipline,
1683 p_additional_info_person_id => l_additional_info_person_id,
1684 p_additional_info_tel_number => l_additional_info_tel_number,
1685 p_altered_pa_request_id => l_altered_pa_request_id,
1686 p_annuitant_indicator => l_annuitant_indicator,
1687 p_annuitant_indicator_desc => l_annuitant_indicator_desc,
1688 p_appropriation_code1 => l_appropriation_code1,
1689 p_appropriation_code2 => l_appropriation_code2,
1690 p_authorized_by_person_id => l_authorized_by_person_id,
1691 p_authorized_by_title => l_authorized_by_title,
1692 p_award_amount => l_award_amount,
1693 p_award_uom => l_award_uom,
1694 p_bargaining_unit_status => l_bargaining_unit_status,
1695 p_citizenship => l_citizenship,
1696 p_concurrence_date => l_concurrence_date,
1697 p_custom_pay_calc_flag => l_custom_pay_calc_flag,
1698 p_duty_station_code => l_duty_station_code,
1699 p_duty_station_desc => l_duty_station_desc,
1700 p_duty_station_id => l_duty_station_id,
1701 p_duty_station_location_id => l_duty_station_location_id,
1702 p_education_level => l_education_level,
1703 p_effective_date => l_effective_date,
1704 p_employee_assignment_id => l_employee_assignment_id,
1705 p_employee_date_of_birth => l_employee_date_of_birth,
1706 p_employee_first_name => l_employee_first_name,
1707 p_employee_last_name => l_employee_last_name,
1708 p_employee_middle_names => l_employee_middle_names,
1709 p_employee_national_identifier => l_employee_national_identifier,
1710 p_fegli => l_fegli,
1711 p_fegli_desc => l_fegli_desc,
1712 p_first_action_la_code1 => l_first_action_la_code1,
1713 p_first_action_la_code2 => l_first_action_la_code2,
1714 p_first_action_la_desc1 => l_first_action_la_desc1,
1715 p_first_action_la_desc2 => l_first_action_la_desc2,
1716 p_first_noa_cancel_or_correct => l_first_noa_cancel_or_correct,
1717 p_first_noa_code => l_first_noa_code,
1718 p_first_noa_desc => l_first_noa_desc,
1719 p_first_noa_id => l_first_noa_id,
1720 p_first_noa_pa_request_id => l_first_noa_pa_request_id,
1721 p_flsa_category => l_flsa_category,
1722 p_forwarding_address_line1 => l_forwarding_address_line1,
1723 p_forwarding_address_line2 => l_forwarding_address_line2,
1724 p_forwarding_address_line3 => l_forwarding_address_line3,
1725 p_forwarding_country => l_forwarding_country,
1726 p_forwarding_postal_code => l_forwarding_postal_code,
1727 p_forwarding_region_2 => l_forwarding_region_2,
1728 p_forwarding_town_or_city => l_forwarding_town_or_city,
1729 p_from_adj_basic_pay => l_from_adj_basic_pay,
1730 p_from_basic_pay => l_from_basic_pay,
1731 p_from_grade_or_level => l_from_grade_or_level,
1732 p_from_locality_adj => l_from_locality_adj,
1733 p_from_occ_code => l_from_occ_code,
1734 p_from_other_pay_amount => l_from_other_pay_amount,
1735 p_from_pay_basis => l_from_pay_basis,
1736 p_from_pay_plan => l_from_pay_plan,
1737 -- FWFA Changes Bug#4444609
1738 p_input_pay_rate_determinant => l_input_pay_rate_determinant,
1739 p_from_pay_table_identifier => l_from_pay_table_identifier,
1740 -- FWFA Changes
1741 p_from_position_id => l_from_position_id,
1742 p_from_position_org_line1 => l_from_position_org_line1,
1743 p_from_position_org_line2 => l_from_position_org_line2,
1744 p_from_position_org_line3 => l_from_position_org_line3,
1745 p_from_position_org_line4 => l_from_position_org_line4,
1746 p_from_position_org_line5 => l_from_position_org_line5,
1747 p_from_position_org_line6 => l_from_position_org_line6,
1748 p_from_position_number => l_from_position_number,
1749 p_from_position_seq_no => l_from_position_seq_no,
1750 p_from_position_title => l_from_position_title,
1751 p_from_step_or_rate => l_from_step_or_rate,
1752 p_from_total_salary => l_from_total_salary,
1753 p_functional_class => l_functional_class,
1754 p_notepad => l_notepad,
1755 p_part_time_hours => l_part_time_hours,
1756 p_pay_rate_determinant => l_pay_rate_determinant,
1757 p_person_id => l_person_id,
1758 p_position_occupied => l_position_occupied,
1759 p_proposed_effective_date => l_proposed_effective_date,
1760 p_requested_by_person_id => l_requested_by_person_id,
1761 p_requested_by_title => l_requested_by_title,
1762 p_requested_date => l_requested_date,
1763 p_requesting_office_remarks_de => l_requesting_office_remarks_de,
1764 p_requesting_office_remarks_fl => l_requesting_office_remarks_fl,
1765 p_request_number => l_request_number,
1766 p_resign_and_retire_reason_des => l_resign_and_retire_reason_des,
1767 p_retirement_plan => l_retirement_plan,
1768 p_retirement_plan_desc => l_retirement_plan_desc,
1769 p_second_action_la_code1 => l_second_action_la_code1,
1770 p_second_action_la_code2 => l_second_action_la_code2,
1771 p_second_action_la_desc1 => l_second_action_la_desc1,
1772 p_second_action_la_desc2 => l_second_action_la_desc2,
1773 p_second_noa_cancel_or_correct => l_second_noa_cancel_or_correct,
1774 p_second_noa_code => l_second_noa_code,
1775 p_second_noa_desc => l_second_noa_desc,
1776 p_second_noa_id => l_second_noa_id,
1777 p_second_noa_pa_request_id => l_second_noa_pa_request_id,
1778 p_service_comp_date => l_service_comp_date,
1779 p_supervisory_status => l_supervisory_status,
1780 p_tenure => l_tenure,
1781 p_to_adj_basic_pay => l_to_adj_basic_pay,
1782 p_to_basic_pay => l_to_basic_pay,
1783 p_to_grade_id => l_to_grade_id,
1784 p_to_grade_or_level => l_to_grade_or_level,
1785 p_to_job_id => l_to_job_id,
1786 p_to_locality_adj => l_to_locality_adj,
1787 p_to_occ_code => l_to_occ_code,
1788 p_to_organization_id => l_to_organization_id,
1789 p_to_other_pay_amount => l_to_other_pay_amount,
1790 p_to_au_overtime => l_to_au_overtime,
1791 p_to_auo_premium_pay_indicator => l_to_auo_premium_pay_indicator,
1792 p_to_availability_pay => l_to_availability_pay,
1793 p_to_ap_premium_pay_indicator => l_to_ap_premium_pay_indicator,
1794 p_to_retention_allowance => l_to_retention_allowance,
1795 p_to_supervisory_differential => l_to_supervisory_differential,
1796 p_to_staffing_differential => l_to_staffing_differential,
1797 p_to_pay_basis => l_to_pay_basis,
1798 p_to_pay_plan => l_to_pay_plan,
1799 -- FWFA Changes Bug#4444609
1800 p_to_pay_table_identifier => l_to_pay_table_identifier,
1801 -- FWFA Changes
1802 p_to_position_id => l_to_position_id,
1803 p_to_position_org_line1 => l_to_position_org_line1,
1804 p_to_position_org_line2 => l_to_position_org_line2,
1805 p_to_position_org_line3 => l_to_position_org_line3,
1806 p_to_position_org_line4 => l_to_position_org_line4,
1807 p_to_position_org_line5 => l_to_position_org_line5,
1808 p_to_position_org_line6 => l_to_position_org_line6,
1809 p_to_position_number => l_to_position_number,
1810 p_to_position_seq_no => l_to_position_seq_no,
1811 p_to_position_title => l_to_position_title,
1812 p_to_step_or_rate => l_to_step_or_rate,
1813 p_to_total_salary => l_to_total_salary,
1814 p_veterans_preference => l_veterans_preference,
1815 p_veterans_pref_for_rif => l_veterans_pref_for_rif,
1816 p_veterans_status => l_veterans_status,
1817 p_work_schedule => l_work_schedule,
1818 p_work_schedule_desc => l_work_schedule_desc,
1819 p_year_degree_attained => l_year_degree_attained,
1820 p_first_noa_information1 => l_first_noa_information1,
1821 p_first_noa_information2 => l_first_noa_information2,
1822 p_first_noa_information3 => l_first_noa_information3,
1823 p_first_noa_information4 => l_first_noa_information4,
1824 p_first_noa_information5 => l_first_noa_information5,
1825 p_second_lac1_information1 => l_second_lac1_information1,
1826 p_second_lac1_information2 => l_second_lac1_information2,
1827 p_second_lac1_information3 => l_second_lac1_information3,
1828 p_second_lac1_information4 => l_second_lac1_information4,
1829 p_second_lac1_information5 => l_second_lac1_information5,
1830 p_second_lac2_information1 => l_second_lac2_information1,
1831 p_second_lac2_information2 => l_second_lac2_information2,
1832 p_second_lac2_information3 => l_second_lac2_information3,
1833 p_second_lac2_information4 => l_second_lac2_information4,
1834 p_second_lac2_information5 => l_second_lac2_information5,
1835 p_second_noa_information1 => l_second_noa_information1,
1836 p_second_noa_information2 => l_second_noa_information2,
1837 p_second_noa_information3 => l_second_noa_information3,
1838 p_second_noa_information4 => l_second_noa_information4,
1839 p_second_noa_information5 => l_second_noa_information5,
1840 p_first_lac1_information1 => l_first_lac1_information1,
1841 p_first_lac1_information2 => l_first_lac1_information2,
1842 p_first_lac1_information3 => l_first_lac1_information3,
1843 p_first_lac1_information4 => l_first_lac1_information4,
1844 p_first_lac1_information5 => l_first_lac1_information5,
1845 p_first_lac2_information1 => l_first_lac2_information1,
1846 p_first_lac2_information2 => l_first_lac2_information2,
1847 p_first_lac2_information3 => l_first_lac2_information3,
1848 p_first_lac2_information4 => l_first_lac2_information4,
1849 p_first_lac2_information5 => l_first_lac2_information5,
1850 -- Added for sf52_from_data_elements
1851 p_to_retention_allow_percentag => l_to_retention_allow_percentag,
1852 p_to_supervisory_diff_percenta => l_to_supervisory_diff_percenta,
1853 p_to_staffing_diff_percentage => l_to_staffing_diff_percentage,
1854 p_award_percentage => l_award_percentage,
1855 -- Added for Elect Auth.
1856 p_u_approval_status => l_u_approval_status,
1857 p_approval_date => l_approval_date,
1858 p_approving_official_full_name => l_approving_official_full_name,
1859 p_approving_official_work_titl => l_approving_official_work_titl,
1860 -- p_u_attachment_modified_flag => l_u_attachment_modified_flag,
1861 -- p_u_approved_flag => l_u_approved_flag,
1862 -- p_u_user_name_acted_on => l_u_user_name_acted_on,
1863 p_u_action_taken => p_action_taken,
1864 -- p_i_user_name_routed_to => l_i_user_name_routed_to,
1865 -- p_i_groupbox_id => l_i_groupbox_id,
1866 -- p_i_routing_list_id => l_i_routing_list_id,
1867 -- p_i_routing_seq_number => l_i_routing_seq_number,
1868 p_u_prh_object_version_number => l_u_prh_object_version_number,
1869 p_i_pa_routing_history_id => l_i_pa_routing_history_id,
1870 p_i_prh_object_version_number => l_i_prh_object_version_number
1871 );
1872 -- Added to update Groupbox ID using the row handler
1873 --
1874 -- open cursor
1875 open csr_get_parh_info;
1876 fetch csr_get_parh_info into l_i_pa_routing_history_id, l_i_prh_object_version_number;
1877 if csr_get_parh_info%notfound then
1878 l_i_pa_routing_history_id := null;
1879 l_i_prh_object_version_number := null;
1880 end if;
1881 close csr_get_parh_info ;
1882 --
1883 if l_i_groupbox_id Is Not Null then
1884 ghr_prh_upd.upd
1885 (
1886 p_object_version_number => l_i_prh_object_version_number,
1887 p_pa_routing_history_id => l_i_pa_routing_history_id,
1888 p_groupbox_id => l_i_groupbox_id
1889 );
1890 end if;
1891 --
1892 --
1893 end update_sf52_action_taken;
1894 --
1895 --
1896 procedure perofc_approval_required ( itemtype in varchar2,
1897 itemkey in varchar2,
1898 actid in number,
1899 funcmode in varchar2,
1900 result in out nocopy varchar2 ) is
1901 --
1902 l_text varchar2(30);
1903 l_result VARCHAR2(250);
1904 --
1905 --
1906 begin
1907 -- NOCOPY Changes
1908 l_result := result;
1909 --
1910 if funcmode = 'RUN' then
1911 l_text := wf_engine.GetItemAttrText( itemtype => Itemtype,
1912 itemkey => Itemkey,
1913 aname => 'PEROFC_APRVL_REQD');
1914 if l_text = 'YES' then
1915 result := 'COMPLETE:YES';
1916 return;
1917 else
1918 result := 'COMPLETE:NO';
1919 return;
1920 end if;
1921 end if;
1922 --
1923 result := '';
1924 return;
1925 --
1926 --
1927 exception
1928 when others then
1929 -- NOCOPY Changes
1930 result := l_result;
1931 -- The line below records this function call in the error system
1932 -- in the case of an exception.
1933 wf_core.context('WGI', 'ghr_wf_wgi_pkg.perofc_approval_required',itemtype, itemkey, to_char(actid), funcmode);
1934 raise;
1935 --
1936 --
1937 end perofc_approval_required;
1938 --
1939 --
1940 procedure use_perofc_only ( itemtype in varchar2,
1941 itemkey in varchar2,
1942 actid in number,
1943 funcmode in varchar2,
1944 result in out nocopy varchar2 ) is
1945 --
1946 l_text varchar2(30);
1947 l_result VARCHAR2(250);
1948 --
1949 --
1950 begin
1951 -- NOCOPY Changes
1952 l_result := result;
1953 --
1954 if funcmode = 'RUN' then
1955 l_text := wf_engine.GetItemAttrText( itemtype => Itemtype,
1956 itemkey => Itemkey,
1957 aname => 'USE_PEROFC');
1958 if l_text = 'NO' then
1959 result := 'COMPLETE:NO';
1960 return;
1961 else
1962 result := 'COMPLETE:YES';
1963 return;
1964 end if;
1965 end if;
1966 --
1967 result := '';
1968 return;
1969 --
1970 --
1971 exception
1972 when others then
1973 -- NOCOPY Changes
1974 result := l_result;
1975 -- The line below records this function call in the error system
1976 -- in the case of an exception.
1977 wf_core.context('WGI', 'ghr_wf_wgi_pkg.use_perofc_only',itemtype, itemkey, to_char(actid), funcmode);
1978 raise;
1979 --
1980 --
1981 end use_perofc_only;
1982 --
1983 --
1984 procedure FindDestPerOfficeGbx( itemtype in varchar2,
1985 itemkey in varchar2,
1986 actid in number,
1987 funcmode in varchar2,
1988 result in out nocopy varchar2 ) is
1989 --
1990 --
1991 --
1992 l_user_name ghr_pa_routing_history.user_name%TYPE;
1993 l_person_id ghr_pa_requests.person_id%TYPE;
1994 l_position_id ghr_pa_requests.from_position_id%TYPE;
1995 l_effective_date ghr_pa_requests.effective_date%TYPE;
1996 l_routing_group_id ghr_pa_requests.routing_group_id%TYPE;
1997 l_assignment_id ghr_pa_requests.employee_assignment_id%TYPE;
1998 l_full_name per_people_f.full_name%TYPE;
1999 l_routing_group_name ghr_routing_groups.name%TYPE;
2000 l_routing_group_desc ghr_routing_groups.description%TYPE;
2001 l_supervisor_name ghr_pa_routing_history.user_name%TYPE;
2002 l_groupbox_id ghr_groupboxes.groupbox_id%TYPE;
2003 l_groupbox_name ghr_groupboxes.name%TYPE;
2004 l_groupbox_desc ghr_groupboxes.description%TYPE;
2005 l_office_symbol_name hr_lookups.meaning%TYPE;
2006 l_wgi_due_date date;
2007 l_rating varchar2(30);
2008 l_multi_error_flag boolean;
2009 l_valid_user boolean;
2010 l_valid_grpbox boolean;
2011 l_line1 varchar2(500);
2012 l_line2 varchar2(500);
2013 l_line3 varchar2(500);
2014 l_line4 varchar2(500);
2015 l_line5 varchar2(500);
2016 l_line6 varchar2(500);
2017 l_line7 varchar2(500);
2018 l_personnel_office_id ghr_pa_requests.personnel_office_id%TYPE;
2019 l_gbx_user_id ghr_pois.person_id%TYPE;
2020 l_routing_group varchar2(500);
2021 l_wgi_error_note varchar2(500);
2022 -- NOCOPY Changes
2023 l_result varchar2(250);
2024 --
2025 --
2026 begin
2027 --
2028 -- NOCOPY Changes
2029 l_result := result;
2030
2031 if funcmode = 'RUN' then
2032 wf_engine.SetItemAttrText (itemtype => ItemType,
2033 itemkey => Itemkey,
2034 aname => 'ERROR_MSG',
2035 avalue => '' );
2036 --
2037 -- Get Person ID and effective date from PA requests table
2038 get_par_details (
2039 p_pa_request_id => itemkey
2040 ,p_person_id => l_person_id
2041 ,p_effective_date => l_effective_date
2042 ,p_position_id => l_position_id
2043 );
2044 -- Get employees personnel groupbox
2045 if l_position_id Is not Null then
2046 Get_emp_personnel_groupbox ( p_position_id => l_position_id
2047 ,p_effective_date => l_effective_date
2048 ,p_groupbox_name => l_groupbox_name
2049 ,p_personnel_office_id => l_personnel_office_id
2050 ,p_gbx_user_id => l_gbx_user_id
2051 );
2052 wf_engine.SetItemAttrText ( itemtype => ItemType,
2053 itemkey => Itemkey,
2054 aname => 'POI',
2055 avalue => l_personnel_office_id);
2056 else
2057 wf_engine.SetItemAttrText ( itemtype => ItemType,
2058 itemkey => Itemkey,
2059 aname => 'ERROR_MSG',
2060 avalue => 'Position ID does not exist for this Employee. '
2061 );
2062 result := 'COMPLETE:NO';
2063 return;
2064 end if;
2065 -- Verify whether valid groupbox
2066 l_valid_grpbox := VerifyValidWFUser (
2067 p_user_name => l_groupbox_name
2068 );
2069 if l_groupbox_name Is Not Null then
2070 wf_engine.SetItemAttrText (
2071 itemtype => Itemtype,
2072 itemkey => Itemkey,
2073 aname => 'PERSONNEL_OFFICE_GBX',
2074 avalue => l_groupbox_name
2075 );
2076 l_wgi_error_note := 'Please process the WGI Request for Personnel Action manually
2077 which has been routed to your Personnel office Groupbox :' || l_groupbox_name;
2078 end if;
2079 if l_valid_grpbox then
2080 GetRoutingGroupDetails (
2081 p_groupbox_name => l_groupbox_name
2082 ,p_groupbox_id => l_groupbox_id
2083 ,p_routing_group_id => l_routing_group_id
2084 ,p_groupbox_desc => l_groupbox_desc
2085 ,p_routing_group_name => l_routing_group_name
2086 ,p_routing_group_desc => l_routing_group_desc
2087 );
2088 else
2089 wf_engine.SetItemAttrText( itemtype => ItemType,
2090 itemkey => Itemkey,
2091 aname => 'ERROR_MSG',
2092 avalue => 'Groupbox is not valid or invalid for this Employee. ' );
2093 end if;
2094 if l_routing_group_id is not null then
2095 update_sf52_action_taken(p_pa_request_id => itemkey,
2096 p_routing_group_id => l_routing_group_id,
2097 p_groupbox_id => l_groupbox_id,
2098 p_action_taken => 'NOT_ROUTED',
2099 p_gbx_user_id => l_gbx_user_id);
2100 else
2101 wf_engine.SetItemAttrText ( itemtype => ItemType,
2102 itemkey => Itemkey,
2103 aname => 'ERROR_MSG',
2104 avalue => 'Routing group does not exist for this Employee. ' );
2105 end if;
2106 --
2107 SetDestination( p_request_id => itemkey
2108 ,p_person_id => l_person_id
2109 ,p_position_id => l_position_id
2110 ,p_effective_date => l_effective_date
2111 ,p_office_symbol_name => l_office_symbol_name
2112 ,p_line1 => l_line1
2113 ,p_line2 => l_line2
2114 ,p_line3 => l_line3
2115 ,p_line4 => l_line4
2116 ,p_line5 => l_line5
2117 ,p_line6 => l_line6
2118 ,p_line7 => l_line7
2119 ,p_routing_group => l_routing_group
2120 );
2121 wf_engine.SetItemAttrText (
2122 itemtype => Itemtype,
2123 itemkey => Itemkey,
2124 aname => 'LINE1',
2125 avalue => l_line1
2126 );
2127 wf_engine.SetItemAttrText (
2128 itemtype => Itemtype,
2129 itemkey => Itemkey,
2130 aname => 'LINE2',
2131 avalue => l_line2
2132 );
2133 wf_engine.SetItemAttrText (
2134 itemtype => Itemtype,
2135 itemkey => Itemkey,
2136 aname => 'LINE3',
2137 avalue => l_line3
2138 );
2139 wf_engine.SetItemAttrText (
2140 itemtype => Itemtype,
2141 itemkey => Itemkey,
2142 aname => 'LINE4',
2143 avalue => l_line4
2144 );
2145 wf_engine.SetItemAttrText (
2146 itemtype => Itemtype,
2147 itemkey => Itemkey,
2148 aname => 'LINE5',
2149 avalue => l_line5
2150 );
2151 wf_engine.SetItemAttrText (
2152 itemtype => Itemtype,
2153 itemkey => Itemkey,
2154 aname => 'LINE6',
2155 avalue => l_line6
2156 );
2157 wf_engine.SetItemAttrText (
2158 itemtype => Itemtype,
2159 itemkey => Itemkey,
2160 aname => 'LINE7',
2161 avalue => l_line7
2162 );
2163 wf_engine.SetItemAttrText (
2164 itemtype => Itemtype,
2165 itemkey => Itemkey,
2166 aname => 'WGI_ERROR_NOTE',
2167 avalue => l_wgi_error_note
2168 );
2169 wf_engine.SetItemAttrText( itemtype => Itemtype,
2170 itemkey => Itemkey,
2171 aname => 'FROM_NAME',
2172 avalue => FND_GLOBAL.USER_NAME() );
2173 wf_engine.SetItemAttrText (
2174 itemtype => Itemtype,
2175 itemkey => Itemkey,
2176 aname => 'OFFICE_SYMBOL',
2177 avalue => l_office_symbol_name
2178 );
2179 wf_engine.SetItemAttrText (
2180 itemtype => Itemtype,
2181 itemkey => Itemkey,
2182 aname => 'ROUTING_GROUP',
2183 avalue => l_routing_group
2184 );
2185 if l_valid_grpbox then
2186 result := 'COMPLETE:YES';
2187 return;
2188 else
2189 wf_engine.SetItemAttrText (
2190 itemtype => Itemtype,
2191 itemkey => Itemkey,
2192 aname => 'ERROR_MSG',
2193 avalue => 'Personnel Groupbox of the employee is invalid. '
2194 );
2195 result := 'COMPLETE:NO';
2196 return;
2197 end if;
2198 --
2199 end if;
2200 --
2201 result := '';
2202 return;
2203 --
2204 --
2205 exception
2206 when others then
2207 -- NOCOPY Changes
2208 result := l_result;
2209 -- The line below records this function call in the error system
2210 -- in the case of an exception.
2211 wf_core.context('WGI', 'ghr_wf_wgi_pkg.FindDestPerOfficeGbx',itemtype, itemkey, to_char(actid), funcmode);
2212 raise;
2213 --
2214 end FindDestPerOfficeGbx;
2215 --
2216 --
2217 --
2218 --
2219 procedure update_sf52_for_wgi_denial ( itemtype in varchar2,
2220 itemkey in varchar2,
2221 actid in number,
2222 funcmode in varchar2,
2223 result in out nocopy varchar2) is
2224 --
2225 --
2226 l_u_prh_object_version_number number;
2227 l_i_pa_routing_history_id number;
2228 l_i_prh_object_version_number number;
2229 l_par_object_version_number ghr_pa_requests.object_version_number%TYPE;
2230 l_person_id ghr_pa_requests.person_id%TYPE;
2231 l_effective_date ghr_pa_requests.effective_date%TYPE;
2232 l_pay_rate_determinant ghr_pa_requests.pay_rate_determinant%TYPE;
2233 l_first_noa_code ghr_pa_requests.first_noa_code%TYPE;
2234 l_first_noa_id ghr_pa_requests.first_noa_id%TYPE;
2235 l_first_noa_desc ghr_pa_requests.first_noa_desc%TYPE;
2236 l_first_action_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
2237 l_first_action_la_desc1 ghr_pa_requests.first_action_la_desc1%TYPE;
2238 l_first_action_la_code2 ghr_pa_requests.first_action_la_code2%TYPE;
2239 l_first_action_la_desc2 ghr_pa_requests.first_action_la_desc2%TYPE;
2240 l_validate boolean := false;
2241 l_from_adj_basic_pay ghr_pa_requests.from_adj_basic_pay%TYPE;
2242 l_from_basic_pay ghr_pa_requests.from_basic_pay%TYPE;
2243 l_from_grade_or_level ghr_pa_requests.from_grade_or_level%TYPE;
2244 l_from_locality_adj ghr_pa_requests.from_locality_adj%TYPE;
2245 l_from_occ_code ghr_pa_requests.from_occ_code%TYPE;
2246 l_from_other_pay_amount ghr_pa_requests.from_other_pay_amount%TYPE;
2247 l_from_pay_basis ghr_pa_requests.from_pay_basis%TYPE;
2248 l_from_pay_plan ghr_pa_requests.from_pay_plan%TYPE;
2249 l_from_position_id ghr_pa_requests.from_position_id%TYPE;
2250 l_from_position_org_line1 ghr_pa_requests.from_position_org_line1%TYPE;
2251 l_from_position_org_line2 ghr_pa_requests.from_position_org_line2%TYPE;
2252 l_from_position_org_line3 ghr_pa_requests.from_position_org_line3%TYPE;
2253 l_from_position_org_line4 ghr_pa_requests.from_position_org_line4%TYPE;
2254 l_from_position_org_line5 ghr_pa_requests.from_position_org_line5%TYPE;
2255 l_from_position_org_line6 ghr_pa_requests.from_position_org_line6%TYPE;
2256 l_from_position_number ghr_pa_requests.from_position_number%TYPE;
2257 l_from_position_seq_no ghr_pa_requests.from_position_seq_no%TYPE;
2258 l_from_position_title ghr_pa_requests.from_position_title%TYPE;
2259 l_from_step_or_rate ghr_pa_requests.from_step_or_rate%TYPE;
2260 l_from_total_salary ghr_pa_requests.from_total_salary%TYPE;
2261 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
2262 -- Remarks
2263 l_pa_remark_id ghr_pa_remarks.pa_remark_id%TYPE;
2264 l_pre_object_version_number ghr_pa_remarks.object_version_number%TYPE;
2265 l_remark_id1 ghr_pa_remarks.remark_id%TYPE := Null;
2266 l_remark_desc1 ghr_pa_remarks.description%type := Null;
2267 l_remark1_info1 ghr_pa_remarks.remark_code_information1%TYPE := Null;
2268 l_remark1_info2 ghr_pa_remarks.remark_code_information2%TYPE := Null;
2269 l_remark1_info3 ghr_pa_remarks.remark_code_information3%TYPE := Null;
2270 l_remark_id2 ghr_pa_remarks.remark_id%TYPE := Null;
2271 l_remark_desc2 ghr_pa_remarks.description%type := Null;
2272 l_remark2_info1 ghr_pa_remarks.remark_code_information1%TYPE := Null;
2273 l_remark2_info2 ghr_pa_remarks.remark_code_information2%TYPE := Null;
2274 l_remark2_info3 ghr_pa_remarks.remark_code_information3%TYPE := Null;
2275 --NOCOPY Changes
2276 l_result VARCHAR2(250);
2277 --
2278 -- Fetch PA request details
2279 cursor csr_get_par_info is
2280 select
2281 object_version_number,
2282 person_id,
2283 effective_date,
2284 pay_rate_determinant,
2285 first_action_la_code1,
2286 first_action_la_desc1,
2287 first_noa_code,
2288 first_noa_desc,
2289 first_noa_id,
2290 from_adj_basic_pay,
2291 from_basic_pay,
2292 from_grade_or_level,
2293 from_locality_adj,
2294 from_occ_code,
2295 from_other_pay_amount,
2296 from_pay_basis,
2297 from_pay_plan,
2298 from_position_id,
2299 from_position_org_line1,
2300 from_position_org_line2,
2301 from_position_org_line3,
2302 from_position_org_line4,
2303 from_position_org_line5,
2304 from_position_org_line6,
2305 from_position_number,
2306 from_position_seq_no,
2307 from_position_title,
2308 from_step_or_rate,
2309 from_total_salary
2310 from ghr_pa_requests
2311 where pa_request_id = to_number(itemkey);
2312 --
2313 --
2314 begin
2315 -- NOCOPY Changes
2316 l_result := result;
2317 if funcmode = 'RUN' then
2318 -- Open cursor csr_get_par_info
2319 open csr_get_par_info;
2320 fetch csr_get_par_info into
2321 l_par_object_version_number,l_person_id,l_effective_date, l_pay_rate_determinant,
2322 l_first_action_la_code1, l_first_action_la_desc1, l_first_noa_code,
2323 l_first_noa_desc, l_first_noa_id, l_from_adj_basic_pay,
2324 l_from_basic_pay, l_from_grade_or_level, l_from_locality_adj,
2325 l_from_occ_code, l_from_other_pay_amount, l_from_pay_basis,
2326 l_from_pay_plan, l_from_position_id,l_from_position_org_line1,
2327 l_from_position_org_line2, l_from_position_org_line3, l_from_position_org_line4,
2328 l_from_position_org_line5, l_from_position_org_line6, l_from_position_number,
2329 l_from_position_seq_no, l_from_position_title, l_from_step_or_rate,
2330 l_from_total_salary;
2331 if csr_get_par_info%notfound then
2332 -- if the cursor does not return a row then we must set the out
2333 -- parameter to null
2334 null;
2335 end if;
2336 -- close the cursor
2337 close csr_get_par_info;
2338 -- Set values of the to side
2339 --
2340 l_first_noa_code := 888;
2341 --
2342 ghr_wgi_pkg.get_noa_code_desc ( p_code => l_first_noa_code,
2343 p_effective_date => l_effective_date,
2344 p_nature_of_action_id => l_first_noa_id,
2345 p_description => l_first_noa_desc
2346 );
2347 begin
2348 l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details
2349 (p_person_id => l_person_id
2350 ,p_effective_date => l_effective_date);
2351 exception
2352 When ghr_pay_calc.pay_calc_message then
2353 null;
2354 end;
2355
2356 --
2357 -- Get Legal Authority Description
2358 --
2359 ghr_wgi_pkg.derive_legal_auth_cd_remarks (
2360 p_first_noa_code => l_first_noa_code,
2361 p_pay_rate_determinant => l_pay_rate_determinant,
2362 p_from_pay_plan => l_from_pay_plan,
2363 p_grade_or_level => l_from_grade_or_level,
2364 p_step_or_rate => l_from_step_or_rate,
2365 p_retained_pay_plan => l_retained_grade_rec.pay_plan,
2366 p_retained_grade_or_level => l_retained_grade_rec.grade_or_level,
2367 p_retained_step_or_rate => l_retained_grade_rec.step_or_rate,
2368 -- Bug#5204589 Added p_temp_step parameter.
2369 p_temp_step => l_retained_grade_rec.temp_step,
2370 p_effective_date => l_effective_date,
2371 p_first_action_la_code1 => l_first_action_la_code1,
2372 p_first_action_la_desc1 => l_first_action_la_desc1,
2373 p_first_action_la_code2 => l_first_action_la_code2,
2374 p_first_action_la_desc2 => l_first_action_la_desc2,
2375 p_remark_id1 => l_remark_id1,
2376 p_remark_desc1 => l_remark_desc1,
2377 p_remark1_info1 => l_remark1_info1,
2378 p_remark1_info2 => l_remark1_info2,
2379 p_remark1_info3 => l_remark1_info3,
2380 p_remark_id2 => l_remark_id2,
2381 p_remark_desc2 => l_remark_desc2,
2382 p_remark2_info1 => l_remark2_info1,
2383 p_remark2_info2 => l_remark2_info2,
2384 p_remark2_info3 => l_remark2_info3
2385 );
2386
2387 --
2388 --
2389 ghr_sf52_api.update_sf52(
2390 p_validate => l_validate,
2391 p_pa_request_id => to_number(itemkey),
2392 p_par_object_version_number => l_par_object_version_number,
2393 p_first_action_la_code1 => l_first_action_la_code1,
2394 p_first_action_la_desc1 => l_first_action_la_desc1,
2395 p_first_action_la_code2 => l_first_action_la_code2,
2396 p_first_action_la_desc2 => l_first_action_la_desc2,
2397 p_first_noa_code => l_first_noa_code,
2398 p_first_noa_desc => l_first_noa_desc,
2399 p_first_noa_id => l_first_noa_id,
2400 p_to_grade_id => null,
2401 p_to_job_id => null,
2402 p_to_au_overtime => null,
2403 p_to_auo_premium_pay_indicator => null,
2404 p_to_availability_pay => null,
2405 p_to_ap_premium_pay_indicator => null,
2406 p_to_retention_allowance => null,
2407 p_to_supervisory_differential => null,
2408 p_to_staffing_differential => null,
2409 p_to_adj_basic_pay => l_from_adj_basic_pay,
2410 p_to_basic_pay => l_from_basic_pay,
2411 p_to_grade_or_level => l_from_grade_or_level,
2412 p_to_locality_adj => l_from_locality_adj,
2413 p_to_occ_code => l_from_occ_code,
2414 p_to_other_pay_amount => l_from_other_pay_amount,
2415 p_to_pay_basis => l_from_pay_basis,
2416 p_to_pay_plan => l_from_pay_plan,
2417 p_to_position_id => l_from_position_id,
2418 p_to_position_org_line1 => l_from_position_org_line1,
2419 p_to_position_org_line2 => l_from_position_org_line2,
2420 p_to_position_org_line3 => l_from_position_org_line3,
2421 p_to_position_org_line4 => l_from_position_org_line4,
2422 p_to_position_org_line5 => l_from_position_org_line5,
2423 p_to_position_org_line6 => l_from_position_org_line6,
2424 p_to_position_number => l_from_position_number,
2425 p_to_position_seq_no => l_from_position_seq_no,
2426 p_to_position_title => l_from_position_title,
2427 p_to_step_or_rate => l_from_step_or_rate,
2428 p_to_total_salary => l_from_total_salary,
2429 p_u_action_taken => 'NOT_ROUTED',
2430 p_u_prh_object_version_number => l_u_prh_object_version_number,
2431 p_i_pa_routing_history_id => l_i_pa_routing_history_id,
2432 p_i_prh_object_version_number => l_i_prh_object_version_number
2433 );
2434
2435 -- Create remarks if any, derived from above .
2436 If l_remark_id1 is not null then
2437 ghr_pa_remarks_api.create_pa_remarks
2438 (p_pa_request_id => to_number(itemkey),
2439 p_remark_id => l_remark_id1,
2440 p_description => l_remark_desc1,
2441 p_remark_code_information1 => l_remark1_info1,
2442 p_remark_code_information2 => l_remark1_info2,
2443 p_remark_code_information3 => l_remark1_info3,
2444 p_pa_remark_id => l_pa_remark_id,
2445 p_object_version_number => l_pre_object_version_number
2446 );
2447 End if;
2448 If l_remark_id2 is not null then
2449 ghr_pa_remarks_api.create_pa_remarks
2450 (p_pa_request_id => to_number(itemkey),
2451 p_remark_id => l_remark_id2,
2452 p_description => l_remark_desc2,
2453 p_remark_code_information1 => l_remark2_info1,
2454 p_remark_code_information2 => l_remark2_info2,
2455 p_remark_code_information3 => l_remark2_info3,
2456 p_pa_remark_id => l_pa_remark_id,
2457 p_object_version_number => l_pre_object_version_number
2458 );
2459 End if;
2460
2461 result := 'COMPLETE:';
2462 return;
2463 end if;
2464 --
2465 result := '';
2466 return;
2467 --
2468 exception
2469 when others then
2470 -- NOCOPY Changes
2471 result := l_result;
2472 -- The line below records this function call in the error system
2473 -- in the case of an exception.
2474 wf_core.context('WGI', 'ghr_wf_wgi_pkg.update_sf52_for_wgi_denial',itemtype, itemkey, to_char(actid), funcmode);
2475 raise;
2476 --
2477 end update_sf52_for_wgi_denial;
2478 --
2479 --
2480 --
2481 --
2482 procedure CheckNtfyPOI ( itemtype in varchar2,
2483 itemkey in varchar2,
2484 actid in number,
2485 funcmode in varchar2,
2486 result in out nocopy varchar2 ) is
2487 --
2488 l_text varchar2(30);
2489 l_result VARCHAR2(250);
2490 --
2491 --
2492 begin
2493 --
2494 --
2495 -- NOCOPY Changes
2496 l_result := result;
2497 if funcmode = 'RUN' then
2498 l_text := wf_engine.GetItemAttrText( itemtype => Itemtype,
2499 itemkey => Itemkey,
2500 aname => 'NTFY_PEROFC_OF_APRVL');
2501 if l_text = 'NO' then
2502 result := 'COMPLETE:NO';
2503 return;
2504 else
2505 result := 'COMPLETE:YES';
2506 return;
2507 end if;
2508 end if;
2509 --
2510 result := '';
2511 return;
2512 --
2513 --
2514 exception
2515 when others then
2516 -- NOCOPY Changes
2517 result := l_result;
2518 -- The line below records this function call in the error system
2519 -- in the case of an exception.
2520 wf_core.context('WGI', 'ghr_wf_wgi_pkg.CheckNtfyPOI',itemtype, itemkey, to_char(actid), funcmode);
2521 raise;
2522 --
2523 --
2524 end CheckNtfyPOI;
2525 --
2526 --
2527 procedure populate_shadow( itemtype in varchar2,
2528 itemkey in varchar2,
2529 actid in number,
2530 funcmode in varchar2,
2531 result in out nocopy varchar2) is
2532 --
2533 --
2534 -- Fetch PA request details
2535 cursor csr_par_info is
2536 select *
2537 from ghr_pa_requests
2538 where pa_request_id = to_number(itemkey);
2539 cursor csr_check_par is
2540 select count(*)
2541 from ghr_pa_requests
2542 where pa_request_id = to_number(itemkey);
2543 --
2544 l_sf52_data_in_rec ghr_pa_requests%rowtype;
2545 l_count number;
2546 l_result VARCHAR2(250);
2547 --
2548 --
2549 begin
2550 -- NOCOPY Changes
2551 l_result := result;
2552 if funcmode = 'RUN' then
2553 -- Open cursor csr_get_par_info
2554 open csr_par_info;
2555 fetch csr_par_info into l_sf52_data_in_rec;
2556 if csr_par_info%notfound then
2557 -- if the cursor does not return a row then we must set the out
2558 -- parameter to null
2559 null;
2560 else
2561 -- Check if PA Request ID exists
2562 open csr_check_par;
2563 fetch csr_check_par into l_count;
2564 if l_count = 1 then
2565 GHR_PROCESS_SF52.create_shadow_row ( p_sf52_data => l_sf52_data_in_rec);
2566 else
2567 -- if the cursor does not return a row then we must set the out
2568 -- parameter to null
2569 null;
2570 end if;
2571 close csr_check_par;
2572 end if;
2573 -- close the cursor
2574 close csr_par_info;
2575
2576 result := 'COMPLETE:';
2577 return;
2578 end if;
2579 --
2580 result := '';
2581 return;
2582 --
2583 exception
2584 when others then
2585 -- NOCOPY Changes
2586 result := l_result;
2587 -- The line below records this function call in the error system
2588 -- in the case of an exception.
2589 wf_core.context('WGI', 'ghr_wf_wgi_pkg.populate_shadow',itemtype, itemkey, to_char(actid), funcmode);
2590 raise;
2591 --
2592 end populate_shadow;
2593 --
2594 --
2595 end ghr_wf_wgi_pkg;