[Home] [Help]
PACKAGE BODY: APPS.GHR_WF_PKG
Source
1 PACKAGE BODY ghr_wf_pkg AS
2 /* $Header: ghwfpkg.pkb 120.6.12020000.3 2012/11/09 09:24:20 vmididho ship $ */
3 --
4 -- Procedure
5 -- StartSF52Process
6 --
7 -- Description
8 -- Start the SF-52 workflow process for the given p_pa_request_id
9 --
10 PROCEDURE StartSF52Process
11 ( p_pa_request_id in number,
12 p_forward_to_name in varchar2,
13 p_error_msg in varchar2 default null
14 ) is
15 --
16 l_ItemType varchar2(30) := 'GHR_SF52';
17 l_ItemKey varchar2(30) := p_pa_request_id;
18 l_forward_from_display_name varchar2(100);
19 l_load_form varchar2(200); --Bug# 6923642 modifier length 100 to 200
20 l_load_prh varchar2(100);
21 l_subject varchar2(500);
22 l_line1 varchar2(500);
23 l_line2 varchar2(500);
24 l_line3 varchar2(500);
25 l_line4 varchar2(500);
26 l_line5 varchar2(500);
27 l_line5a varchar2(500);
28 l_line6 varchar2(500);
29 l_line7 varchar2(500);
30 l_line8 varchar2(500);
31 l_line9 varchar2(500);
32 --
33 begin
34
35 -- Creates a new runtime process for an application item (SF-52)
36 --
37 hr_utility.set_location('l_proc',1);
38 wf_engine.createProcess( ItemType => l_ItemType,
39 ItemKey => l_ItemKey,
40 process => 'SF52_APPROVAL_PROCESS' );
41 --
42 --
43 hr_utility.set_location('l_proc',2);
44 wf_engine.SetItemAttrNumber ( itemtype => l_ItemType,
45 itemkey => l_Itemkey,
46 aname => 'PA_REQUEST_ID',
47 avalue => p_pa_request_id );
48 hr_utility.set_location('l_proc',3);
49 wf_engine.SetItemAttrText( itemtype => l_itemtype,
50 itemkey => l_itemkey,
51 aname => 'FORWARD_TO_NAME',
52 avalue => p_forward_to_name );
53
54 hr_utility.set_location('l_proc',4);
55 hr_utility.set_location('l_proc',5);
56 wf_engine.SetItemAttrText( itemtype => l_itemtype,
57 itemkey => l_itemkey,
58 aname => 'FORWARD_FROM_DISPLAY_NAME',
59 avalue => FND_GLOBAL.USER_NAME() );
60 -- Bug # 8597583 modified parameters to pass with out double quotes
61
62 hr_utility.set_location('l_proc',6);
63 l_load_form := 'GHRWS52L:p_pa_request_id=' || l_Itemkey
64 || ' p_inbox_query_only=NO' || ' WORKFLOW_NAME=GHR_US_PA_REQUEST'
65 || ' p_wf_notification_id=&#NID';--Bug# 6923642
66 wf_engine.SetItemAttrText( itemtype => l_itemtype,
67 itemkey => l_itemkey,
68 aname => 'LOAD_SF52',
69 avalue => l_load_form
70 );
71 l_load_prh := 'GHRWSPRH:p_pa_request_id=' || l_Itemkey;
72 wf_engine.SetItemAttrText( itemtype => l_itemtype,
73 itemkey => l_itemkey,
74 aname => 'LOAD_PRH',
75 avalue => l_load_prh
76 );
77 --
78 hr_utility.set_location('l_proc',7);
79 ghr_wf_pkg.SetDestinationDetails ( p_pa_request_id => l_itemkey,
80 p_subject => l_subject,
81 p_line1 => l_line1,
82 p_line2 => l_line2,
83 p_line3 => l_line3,
84 p_line4 => l_line4,
85 p_line5 => l_line5,
86 p_line6 => l_line6,
87 p_line7 => l_line7,
88 p_line8 => l_line8,
89 p_line9 => l_line9
90 );
91 --
92 --
93 --
94
95
96 wf_engine.SetItemAttrText( itemtype => l_itemtype,
97 itemkey => l_itemkey,
98 aname => 'SUBJECT_HDR',
99 avalue => l_subject
100 );
101 wf_engine.SetItemAttrText( itemtype => l_itemtype,
102 itemkey => l_itemkey,
103 aname => 'LINE1',
104 avalue => l_line1
105 );
106 wf_engine.SetItemAttrText( itemtype => l_itemtype,
107 itemkey => l_itemkey,
108 aname => 'LINE2',
109 avalue => l_line2
110 );
111 /*
112 wf_engine.SetItemAttrText( itemtype => l_itemtype,
113 itemkey => l_itemkey,
114 aname => 'PROPOSED_EFF_DATE',
115 avalue => l_line2a
116 );
117 */
118 wf_engine.SetItemAttrText( itemtype => l_itemtype,
119 itemkey => l_itemkey,
120 aname => 'LINE3',
121 avalue => l_line3
122 );
123 wf_engine.SetItemAttrText( itemtype => l_itemtype,
124 itemkey => l_itemkey,
125 aname => 'LINE4',
126 avalue => l_line4
127 );
128 wf_engine.SetItemAttrText( itemtype => l_itemtype,
129 itemkey => l_itemkey,
130 aname => 'LINE5',
131 avalue => l_line5
132 );
133 /*
134 wf_engine.SetItemAttrText( itemtype => l_itemtype,
135 itemkey => l_itemkey,
136 aname => 'DATE_INITIATED',
137 avalue => l_line5a
138 );
139 */
140 wf_engine.SetItemAttrText( itemtype => l_itemtype,
141 itemkey => l_itemkey,
142 aname => 'LINE6',
143 avalue => l_line6
144 );
145 wf_engine.SetItemAttrText( itemtype => l_itemtype,
146 itemkey => l_itemkey,
147 aname => 'LINE7',
148 avalue => l_line7
149 );
150 wf_engine.SetItemAttrText( itemtype => l_itemtype,
151 itemkey => l_itemkey,
152 aname => 'LINE8',
153 avalue => l_line8
154 );
155 if ( CheckItemAttribute ( p_name => 'LINE9',
156 p_itemtype => l_itemtype,
157 p_itemkey => l_itemkey ) ) then
158 wf_engine.SetItemAttrText( itemtype => l_itemtype,
159 itemkey => l_itemkey,
160 aname => 'LINE9',
161 avalue => l_line9
162 );
163 end if;
164 -- Added for Future Action process
165 if p_error_msg Is Not Null then
166 wf_engine.SetItemAttrText(
167 itemtype => l_itemtype,
168 itemkey => l_itemkey,
169 aname => 'LINE_ERROR',
170 avalue => 'Update HR Error : '
171 || substr(p_error_msg,1,1000)
172 );
173 end if;
174 -- Start the SF-52 workflow process for SF52_APPROVAL_PROCESS
175 --
176 wf_engine.StartProcess ( ItemType => l_ItemType,
177 ItemKey => l_ItemKey );
178 --
179 --
180 hr_utility.set_location('l_proc',10);
181 end StartSF52Process;
182 --
183 --
184 PROCEDURE UpdateRHistoryProcess( itemtype in varchar2,
185 itemkey in varchar2,
186 actid in number,
187 funcmode in varchar2,
188 result in out nocopy varchar2) is
189 --
190 --
191 --
192 l_result varchar2(4000);
193 begin
194 l_result := result;
195 --
196 --
197
198 if funcmode = 'RUN' then
199 ghr_prh_api.upd_date_notif_sent (p_pa_request_id => itemkey,
200 p_date_notification_sent => sysdate);
201 result := ' ';
202 end if;
203
204
205 --
206 --
207 -- Other execution modes may be created in the future.
208 -- Activity indicates that it does not implement a mode
209 -- by returning null
210 --
211 result := '';
212 return;
213 --
214 exception
215 when others then
216 -- The line below records this function call in the error system
217 -- in the case of an exception.
218 wf_core.context('GHR_SF52', 'ghr_wf_pkg.UpdateRHistoryProcess',itemtype, itemkey, to_char(actid), funcmode);
219 --
220 -- Reset IN OUT parameters and set OUT parameters
221 --
222 result := l_result;
223
224 raise;
225 --
226 end UpdateRHistoryProcess;
227 --
228 --
229 --
230 --
231 PROCEDURE UpdateFinalFYIWFUsers ( itemtype in varchar2,
232 itemkey in varchar2,
233 actid in number,
234 funcmode in varchar2,
235 result in out nocopy varchar2) is
236 --
237 --
238 --
239 cursor csr_get_approver_name is
240 SELECT user_name from ghr_pa_routing_history
241 where pa_request_id = itemkey
242 and approval_status = 'APPROVE'
243 order by pa_routing_history_id desc;
244 --
245 cursor csr_get_upd_hr_user_name is
246 SELECT user_name from ghr_pa_routing_history
247 where pa_request_id = itemkey
248 and action_taken in ('UPDATE_HR_COMPLETE','ENDED')
249 order by pa_routing_history_id desc;
250 --
251 l_approver_name ghr_pa_routing_history.user_name%TYPE;
252 l_upd_hr_user_name ghr_pa_routing_history.user_name%TYPE;
253 l_line3 varchar2(500);
254 l_load_form varchar2(200); --Bug# 7312949
255 l_result varchar2(4000);
256 --
257 --
258 begin
259 --
260 l_result := result;
261 --
262 if funcmode = 'RUN' then
263 open csr_get_approver_name;
264 fetch csr_get_approver_name into l_approver_name;
265 if csr_get_approver_name%notfound then
266 null;
267 -- ?? Check with ****
268 -- hr_utility.set_message(8301,'GHR_38211_NOA_FAMILY_NOTFOUND');
269 -- hr_utility.raise_error;
270 end if;
271 close csr_get_approver_name;
272 --
273 --
274 open csr_get_upd_hr_user_name;
275 fetch csr_get_upd_hr_user_name into l_upd_hr_user_name;
276 if csr_get_upd_hr_user_name%notfound then
277 null;
278 -- ?? Check with ****
279 -- hr_utility.set_message(8301,'GHR_38211_NOA_FAMILY_NOTFOUND');
280 -- hr_utility.raise_error;
281 end if;
282 close csr_get_upd_hr_user_name;
283 --
284 if l_approver_name Is Null then
285 l_approver_name := l_upd_hr_user_name;
286 end if;
287 if l_upd_hr_user_name Is Null then
288 l_upd_hr_user_name := l_approver_name;
289 end if;
290 --
291 if ( CheckItemAttribute ( p_name => 'APPROVER_NAME',
292 p_itemtype => itemtype,
293 p_itemkey => itemkey ) and (l_approver_name Is Not Null) ) then
294 wf_engine.SetItemAttrText( itemtype => itemtype,
295 itemkey => itemkey,
296 aname => 'APPROVER_NAME',
297 avalue => l_approver_name
298 );
299 end if;
300 if ( CheckItemAttribute ( p_name => 'PERSON_UPDATE_HR',
301 p_itemtype => itemtype,
302 p_itemkey => itemkey ) and (l_upd_hr_user_name Is Not Null) ) then
303 wf_engine.SetItemAttrText( itemtype => itemtype,
304 itemkey => itemkey,
305 aname => 'PERSON_UPDATE_HR',
306 avalue => l_upd_hr_user_name
307 );
308 end if;
309 -- Third line of message
310 -- l_line3 := 'Current Status : ' || 'UPDATE_HR_COMPLETE';
311 -- wf_engine.SetItemAttrText( itemtype => itemtype,
312 -- itemkey => itemkey,
313 -- aname => 'LINE3',
314 -- avalue => l_line3
315 -- );
316 end if;
317
318 if ( CheckItemAttribute ( p_name => 'PA_REQUEST_RO',
319 p_itemtype => itemtype,
320 p_itemkey => itemkey ) ) then
321 -- Bug # 8597583 modified parameters to pass with out double quotes
322
323 l_load_form := 'GHRWS52L:p_pa_request_id=' || Itemkey
324 || ' p_inbox_query_only=YES' || ' WORKFLOW_NAME=GHR_US_PA_REQUEST'
325 || ' p_wf_notification_id=&#NID' ;--Bug# 6923642
326 wf_engine.SetItemAttrText( itemtype => itemtype,
327 itemkey => itemkey,
328 aname => 'PA_REQUEST_RO',
329 avalue => l_load_form
330 );
331 end if;
332 --
333 --
334 -- Other execution modes may be created in the future.
335 -- Activity indicates that it does not implement a mode
336 -- by returning null
337 --
338 result := '';
339 return;
340 --
341 exception
342 when others then
343 -- The line below records this function call in the error system
344 -- in the case of an exception.
345 wf_core.context('GHR_SF52', 'ghr_wf_pkg.UpdateFinalFYIWFUsers',itemtype, itemkey, to_char(actid), funcmode);
346 --
347 -- Reset IN OUT parameters and set OUT parameters
348 --
349 result := l_result;
350 raise;
351 --
352 end UpdateFinalFYIWFUsers ;
353 --
354 --
355 --
356 --
357 PROCEDURE CheckIFSameFYIUsers( itemtype in varchar2,
358 itemkey in varchar2,
359 actid in number,
360 funcmode in varchar2,
361 result in out nocopy varchar2) is
362 --
363 --
364 l_approver_name ghr_pa_routing_history.user_name%TYPE;
365 l_upd_hr_user_name ghr_pa_routing_history.user_name%TYPE;
366 --
367 l_result varchar2(4000);
368 begin
369 l_result := result;
370 --
371 --
372 if funcmode = 'RUN' then
373 if ( CheckItemAttribute ( p_name => 'APPROVER_NAME',
374 p_itemtype => itemtype,
375 p_itemkey => itemkey ) ) then
376 --
377 l_approver_name := wf_engine.GetItemAttrText
378 (itemtype => itemtype,
379 itemkey => itemkey,
380 aname => 'APPROVER_NAME'
381 );
382 end if;
383 --
384 if ( CheckItemAttribute ( p_name => 'PERSON_UPDATE_HR',
385 p_itemtype => itemtype,
386 p_itemkey => itemkey ) ) then
387 --
388 l_upd_hr_user_name := wf_engine.GetItemAttrText
389 (itemtype => itemtype,
390 itemkey => itemkey,
391 aname => 'PERSON_UPDATE_HR'
392 );
393 end if;
394
395 if l_approver_name = l_upd_hr_user_name then
396 result := 'COMPLETE:YES';
397 return;
398 else
399 result := 'COMPLETE:NO';
400 return;
401 end if;
402 --
403 end if;
404 --
405 --
406 -- Other execution modes may be created in the future.
407 -- Activity indicates that it does not implement a mode
408 -- by returning null
409 --
410 result := '';
411 return;
412 --
413 exception
414 when others then
415 -- The line below records this function call in the error system
416 -- in the case of an exception.
417 wf_core.context('GHR_SF52', 'ghr_wf_pkg.CheckIFSameFYIUsers',itemtype, itemkey, to_char(actid), funcmode);
418 --
419 -- Reset IN OUT parameters and set OUT parameters
420 --
421 result := l_result;
422 raise;
423 --
424 end CheckIFSameFYIUsers;
425 --
426 --
427 --
428 --
429 procedure FindDestination( itemtype in varchar2,
430 itemkey in varchar2,
431 actid in number,
432 funcmode in varchar2,
433 result in out nocopy varchar2 ) is
434 --
435 --
436 l_user_name ghr_pa_routing_history.user_name%TYPE;
437 l_action_taken ghr_pa_routing_history.action_taken%TYPE;
438 l_groupbox_name ghr_groupboxes.name%TYPE;
439 l_subject varchar2(500);
440 l_line1 varchar2(500);
441 l_line2 varchar2(500);
442 l_line3 varchar2(500);
443 l_line4 varchar2(500);
444 l_line5 varchar2(500);
445 l_line6 varchar2(500);
446 l_line7 varchar2(500);
447 l_line8 varchar2(500);
448 l_line9 varchar2(500);
449 --
450 --
451 l_result varchar2(4000);
452 begin
453 l_result := result;
454 --
455 if funcmode = 'RUN' then
456 --
457 ghr_wf_pkg.SetDestinationDetails ( p_pa_request_id => itemkey,
458 p_subject => l_subject,
459 p_line1 => l_line1,
460 p_line2 => l_line2,
461 p_line3 => l_line3,
462 p_line4 => l_line4,
463 p_line5 => l_line5,
464 p_line6 => l_line6,
465 p_line7 => l_line7,
466 p_line8 => l_line8,
467 p_line9 => l_line9
468 );
469 wf_engine.SetItemAttrText( itemtype => itemtype,
470 itemkey => itemkey,
471 aname => 'SUBJECT_HDR',
472 avalue => l_subject
473 );
474 wf_engine.SetItemAttrText( itemtype => itemtype,
475 itemkey => itemkey,
476 aname => 'LINE1',
477 avalue => l_line1
478 );
479 wf_engine.SetItemAttrText( itemtype => itemtype,
480 itemkey => itemkey,
481 aname => 'LINE2',
482 avalue => l_line2
483 );
484 /*
485 wf_engine.SetItemAttrText( itemtype => itemtype,
486 itemkey => itemkey,
487 aname => 'PROPOSED_EFF_DATE',
488 avalue => l_line2a
489 );
490 */
491 wf_engine.SetItemAttrText( itemtype => itemtype,
492 itemkey => itemkey,
493 aname => 'LINE3',
494 avalue => l_line3
495 );
496 wf_engine.SetItemAttrText( itemtype => itemtype,
497 itemkey => itemkey,
498 aname => 'LINE4',
499 avalue => l_line4
500 );
501 wf_engine.SetItemAttrText( itemtype => itemtype,
502 itemkey => itemkey,
503 aname => 'LINE5',
504 avalue => l_line5
505 );
506 /*
507 wf_engine.SetItemAttrText( itemtype => itemtype,
508 itemkey => itemkey,
509 aname => 'DATE_INITIATED',
510 avalue => l_line5a
511 );
512 */
513 wf_engine.SetItemAttrText( itemtype => itemtype,
514 itemkey => itemkey,
515 aname => 'LINE6',
516 avalue => l_line6
517 );
518 wf_engine.SetItemAttrText( itemtype => itemtype,
519 itemkey => itemkey,
520 aname => 'LINE7',
521 avalue => l_line7
522 );
523 wf_engine.SetItemAttrText( itemtype => itemtype,
524 itemkey => itemkey,
525 aname => 'LINE8',
526 avalue => l_line8
527 );
528 if ( CheckItemAttribute ( p_name => 'LINE9',
529 p_itemtype => itemtype,
530 p_itemkey => itemkey ) ) then
531 wf_engine.SetItemAttrText( itemtype => itemtype,
532 itemkey => itemkey,
533 aname => 'LINE9',
534 avalue => l_line9
535 );
536 end if;
537 --
538 --
539 ghr_wf_pkg.GetDestinationDetails ( p_pa_request_id => itemkey,
540 p_action_taken => l_action_taken,
541 p_user_name => l_user_name,
542 p_groupbox_name => l_groupbox_name
543 );
544 if l_action_taken in ('CANCELED') then
545 result := 'COMPLETE:CANCELED';
546 return;
547 elsif l_action_taken in ('UPDATE_HR_COMPLETE','ENDED') then
548 result := 'COMPLETE:UPDATE_HR_COMPLETE';
549 return;
550 elsif l_action_taken in ('FUTURE_ACTION') then
551 result := 'COMPLETE:FUTURE_ACTION';
552 return;
553 else
554 --
555 if l_user_name Is Not Null then
556 --
557 wf_engine.SetItemAttrText( itemtype => Itemtype,
558 itemkey => Itemkey,
559 aname => 'FORWARD_TO_NAME',
560 avalue => l_user_name );
561 result := 'COMPLETE:CONTINUE';
562 return;
563 else
564
565 wf_engine.SetItemAttrText( itemtype => Itemtype,
566 itemkey => Itemkey,
567 aname => 'FORWARD_TO_NAME',
568 avalue => l_groupbox_name );
569 result := 'COMPLETE:CONTINUE';
570 return;
571 end if;
572 --
573 end if;
574 --
575 --
576
577 end if;
578 --
579 --
580 -- Other execution modes may be created in the future.
581 -- Activity indicates that it does not implement a mode
582 -- by returning null
583 --
584 result := '';
585 return;
586 --
587 --
588 exception
589 when others then
590 -- The line below records this function call in the error system
591 -- in the case of an exception.
592 wf_core.context('GHR_SF52', 'ghr_wf_pkg.FindDestination',itemtype, itemkey, to_char(actid), funcmode);
593 --
594 -- Reset IN OUT parameters and set OUT parameters
595 --
596 result := l_result;
597 raise;
598 --
599 --
600 end FindDestination;
601 --
602 --
603 PROCEDURE GetDestinationDetails (
604 p_pa_request_id in NUMBER,
605 p_action_taken OUT nocopy varchar2,
606 p_user_name OUT nocopy varchar2,
607 p_groupbox_name OUT nocopy varchar2
608 ) IS
609
610 -- Local variables
611 l_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%TYPE;
612 l_user_name ghr_pa_routing_history.user_name%TYPE;
613 l_groupbox_id ghr_pa_routing_history.groupbox_id%TYPE;
614 l_action_taken ghr_pa_routing_history.action_taken%TYPE;
615 l_groupbox_name ghr_groupboxes.name%TYPE;
616 --
617 --
618 cursor csr_pa_routing_history is
619 SELECT max(pa_routing_history_id)
620 FROM ghr_pa_routing_history
621 WHERE pa_request_id = p_pa_request_id;
622 --
623 cursor csr_pah_details is
624 SELECT action_taken, user_name, groupbox_id
625 FROM ghr_pa_routing_history
626 WHERE pa_routing_history_id = l_pa_routing_history_id;
627 --
628 cursor csr_gbx_details is
629 SELECT name
630 FROM GHR_GROUPBOXES
631 WHERE GROUPBOX_ID = l_groupbox_id;
632 --
633 --
634 begin
635 -- This function will select from routing history table based User/ Groupbox Name which happens.
636 -- to be the next destination.
637 --
638 --
639 -- Get the last Routing History record
640 --
641 open csr_pa_routing_history;
642 fetch csr_pa_routing_history into l_pa_routing_history_id;
643 if csr_pa_routing_history%notfound then
644 null;
645 -- ?? Check with ****
646 -- hr_utility.set_message(8301,'GHR_38211_NOA_FAMILY_NOTFOUND');
647 -- hr_utility.raise_error;
648 end if;
649 close csr_pa_routing_history;
650 --
651 -- Get the routing history details
652 --
653 open csr_pah_details;
654 fetch csr_pah_details into l_action_taken, l_user_name, l_groupbox_id;
655 if csr_pah_details%notfound then
656 null;
657 -- ?? Check with ****
658 -- hr_utility.set_message(8301,'GHR_38211_NOA_FAMILY_NOTFOUND');
659 -- hr_utility.raise_error;
660 end if;
661 close csr_pah_details;
662 --
663 --
664 if l_action_taken not in ('CANCELED','UPDATE_HR_COMPLETE','FUTURE_ACTION','ENDED') or l_action_taken is Null then
665 if l_user_name is not null then
666 p_user_name := l_user_name;
667 else
668 --
669 open csr_gbx_details;
670 fetch csr_gbx_details into l_groupbox_name;
671 if csr_gbx_details%notfound then
672 null;
673 -- ?? Check with ****
674 -- hr_utility.set_message(8301,'GHR_38211_NOA_FAMILY_NOTFOUND');
675 -- hr_utility.raise_error;
676 else
677 p_groupbox_name := l_groupbox_name;
678 end if;
679 close csr_gbx_details;
680 --
681 end if;
682 elsif l_action_taken in ('CANCELED','FUTURE_ACTION','UPDATE_HR_COMPLETE','ENDED') then
683 p_action_taken := l_action_taken;
684 else
685 p_action_taken := null;
686 end if;
687 --
688 Exception when others then
689 --
690 -- Reset IN OUT parameters and set OUT parameters
691 --
692 p_action_taken := null;
693 p_user_name := null;
694 p_groupbox_name := null;
695 raise;
696 END GetDestinationDetails;
697
698 -- Sets the Message lines
699 PROCEDURE SetDestinationDetails (
700 p_pa_request_id in NUMBER,
701 p_subject OUT nocopy varchar2,
702 p_line1 OUT nocopy varchar2,
703 p_line2 OUT nocopy varchar2,
704 p_line3 OUT nocopy varchar2,
705 p_line4 OUT nocopy varchar2,
706 p_line5 OUT nocopy varchar2,
707 p_line6 OUT nocopy varchar2,
708 p_line7 OUT nocopy varchar2,
709 p_line8 OUT nocopy varchar2,
710 p_line9 OUT nocopy varchar2
711 ) is
712
713 -- Local variables
714 l_subject varchar2(500);
715 l_line1 varchar2(500);
716 l_line2 varchar2(500);
717 l_line3 varchar2(500);
718 l_line4 varchar2(500);
719 l_line5 varchar2(500);
720 l_line6 varchar2(500);
721 l_line7 varchar2(500);
722 l_line8 varchar2(500);
723 l_line9 varchar2(500);
724 l_request_number ghr_pa_requests.request_number%TYPE;
725 l_noa_family_code ghr_pa_requests.noa_family_code%TYPE;
726 l_employee_first_name ghr_pa_requests.employee_first_name%TYPE;
727 l_employee_last_name ghr_pa_requests.employee_last_name%TYPE;
728 l_employee_middle_names ghr_pa_requests.employee_middle_names%TYPE;
729 l_first_noa_desc ghr_pa_requests.first_noa_desc%TYPE;
730 l_second_noa_desc ghr_pa_requests.first_noa_desc%TYPE;
731 l_proposed_effective_date ghr_pa_requests.proposed_effective_date%TYPE;
732 l_effective_date ghr_pa_requests.effective_date%TYPE;
733 l_requested_by_person_id ghr_pa_requests.requested_by_person_id%TYPE;
734 l_routing_group_id ghr_pa_requests.routing_group_id%TYPE;
735 l_first_noa_code ghr_pa_requests.first_noa_code%TYPE;
736 l_second_noa_code ghr_pa_requests.second_noa_code%TYPE;
737 l_routing_group_name ghr_routing_groups.name%TYPE;
738 l_description ghr_routing_groups.description%TYPE;
739 l_to_organization_id ghr_pa_requests.to_organization_id%TYPE;
740 l_to_organization_name hr_organization_units.name%TYPE;
741 l_from_organization_name hr_organization_units.name%TYPE;
742 l_noa_fam_desc ghr_families.name%TYPE;
743 l_error_msg varchar2(1200);
744 l_action_taken1 ghr_pa_routing_history.action_taken%TYPE;
745 l_creation_date ghr_pa_routing_history.creation_date%TYPE;
746 l_date_notification_sent ghr_pa_routing_history.date_notification_sent%TYPE;
747 l_from_position_id ghr_pa_requests.from_position_id%TYPE;
748 l_to_position_id ghr_pa_requests.to_position_id%TYPE;
749 --
750 l_personnel_office_id ghr_pa_requests.personnel_office_id%TYPE;
751 l_status ghr_pa_requests.status%TYPE;
752 l_pos_ei_data per_position_extra_info%rowtype;
753 --
754 cursor csr_par_details is
755 SELECT noa_family_code, request_number,
756 employee_first_name, employee_last_name, employee_middle_names,
757 proposed_effective_date, effective_date, requested_by_person_id,
758 routing_group_id, to_organization_id, first_noa_desc, second_noa_desc,
759 first_noa_code, second_noa_code, from_position_id, personnel_office_id,
760 status, to_position_id
761 FROM ghr_pa_requests
762 WHERE pa_request_id = p_pa_request_id;
763 --
764 cursor csr_ghr_families is
765 SELECT name
766 FROM ghr_families
767 WHERE noa_family_code = l_noa_family_code;
768 --
769 cursor csr_routing_groups is
770 SELECT name, description
771 FROM ghr_routing_groups
772 WHERE routing_group_id = l_routing_group_id;
773 --
774 cursor csr_org_details is
775 SELECT name
776 FROM hr_organization_units
777 WHERE organization_id = l_to_organization_id ;
778 --
779 cursor csr_get_routing_details is
780 SELECT action_taken from ghr_pa_routing_history
781 where pa_request_id = p_pa_request_id
782 order by pa_routing_history_id desc;
783 --
784 cursor csr_get_initiated_date is
785 SELECT date_notification_sent, creation_date FROM ghr_pa_routing_history
786 WHERE pa_request_id = p_pa_request_id
787 order by 1 asc;
788 --
789 cursor csr_from_org_details is
790 SELECT hru.name
791 FROM hr_organization_units hru,
792 hr_all_positions_f hpf
793 WHERE hpf.position_id = nvl(l_from_position_id,-9999)
794 and nvl(l_effective_date,sysdate)
795 between hpf.effective_start_date
796 and hpf.effective_end_date
797 and hpf.organization_id = hru.organization_id;
798 --
799 --
800 begin
801 -- This function will set the Workflow notification message attributes at each hop
802 --
803 -- Get Error message
804 l_error_msg := wf_engine.GetItemAttrText
805 (itemtype => 'GHR_SF52',
806 itemkey => p_pa_request_id,
807 aname => 'LINE_ERROR'
808 );
809 -- Get from the PA request the NOA CODE
810 open csr_par_details;
811 fetch csr_par_details into l_noa_family_code, l_request_number,
812 l_employee_first_name,l_employee_last_name,
813 l_employee_middle_names, l_proposed_effective_date, l_effective_date,
814 l_requested_by_person_id,l_routing_group_id,
815 l_to_organization_id, l_first_noa_desc, l_second_noa_desc,
816 l_first_noa_code, l_second_noa_code, l_from_position_id,
817 l_personnel_office_id, l_status, l_to_position_id;
818 if csr_par_details%notfound then
819 null;
820 -- ?? Check with ****
821 -- hr_utility.set_message(8301,'GHR_38211_NOA_FAMILY_NOTFOUND');
822 -- hr_utility.raise_error;
823 end if;
824 close csr_par_details;
825 --
826 -- Fetch POI from history
827 if l_to_position_id Is Not Null then
828 ghr_history_fetch.fetch_positionei
829 ( p_position_id => l_to_position_id
830 ,p_date_effective => nvl(l_effective_date,trunc(sysdate
831 ))
832 ,p_information_type => 'GHR_US_POS_GRP1'
833 ,p_pos_ei_data => l_pos_ei_data
834 );
835 l_personnel_office_id := l_pos_ei_data.poei_information3;
836 l_pos_ei_data := null;
837 elsif l_from_position_id Is Not Null then
838 ghr_history_fetch.fetch_positionei
839 ( p_position_id => l_to_position_id
840 ,p_date_effective => nvl(l_effective_date,trunc(sysdate
841 ))
842 ,p_information_type => 'GHR_US_POS_GRP1'
843 ,p_pos_ei_data => l_pos_ei_data
844 );
845 l_personnel_office_id := l_pos_ei_data.poei_information3;
846 end if;
847 --
848 --
849 open csr_ghr_families;
850 fetch csr_ghr_families into l_noa_fam_desc;
851 if csr_ghr_families%notfound then
852 null;
853 -- ?? Check with ****
854 -- hr_utility.set_message(8301,'GHR_38211_NOA_FAMILY_NOTFOUND');
855 -- hr_utility.raise_error;
856 end if;
857 close csr_ghr_families;
858 if l_noa_fam_desc is Null then
859 l_noa_fam_desc := l_noa_family_code;
860 end if;
861 --
862 if l_first_noa_desc Is Null then
863 if l_noa_fam_desc is Null then
864 l_first_noa_desc := l_noa_family_code;
865 else
866 l_first_noa_desc := l_noa_fam_desc;
867 end if;
868 end if;
869 --
870 --
871 -- Get routing group name and description
872 if l_routing_group_id Is Not Null then
873 open csr_routing_groups;
874 fetch csr_routing_groups into l_routing_group_name, l_description;
875 if csr_routing_groups%notfound then
876 null;
877 -- ?? Check with ****
878 -- hr_utility.set_message(8301,'GHR_38211_NOA_FAMILY_NOTFOUND');
879 -- hr_utility.raise_error;
880 end if;
881 close csr_routing_groups;
882 else
883 l_routing_group_name := ' ';
884 end if;
885 -- Get Action taken
886 open csr_get_routing_details;
887 fetch csr_get_routing_details into l_action_taken1;
888 if csr_get_routing_details%notfound then
889 null;
890 -- hr_utility.set_message(8301,'GHR_38211_NOA_FAMILY_NOTFOUND');
891 -- hr_utility.raise_error;
892 end if;
893 close csr_get_routing_details;
894 --
895 open csr_get_initiated_date;
896 fetch csr_get_initiated_date into l_date_notification_sent, l_creation_date;
897 if csr_get_initiated_date%notfound then
898 null;
899 -- hr_utility.set_message(8301,'GHR_38211_NOA_FAMILY_NOTFOUND');
900 -- hr_utility.raise_error;
901 end if;
902 close csr_get_initiated_date;
903 --
904 if l_request_number is Null then
905 l_request_number := ' ';
906 end if;
907 --
908 if l_employee_last_name is Null then
909 l_employee_last_name := ' ';
910 end if;
911 --
912 if l_employee_first_name is Null then
913 l_employee_first_name := ' ';
914 end if;
915 --
916 if l_employee_middle_names is Null then
917 l_employee_middle_names := ' ';
918 end if;
919 --
920 if l_to_organization_id is Null then
921 -- Get FRom Org details if to Org ID is Null
922 l_to_organization_name := ' ';
923 open csr_from_org_details;
924 fetch csr_from_org_details into l_from_organization_name;
925 if csr_from_org_details%notfound then
926 null;
927 -- ?? Check with ****
928 -- hr_utility.set_message(8301,'GHR_38211_NOA_FAMILY_NOTFOUND');
929 -- hr_utility.raise_error;
930 end if;
931 close csr_from_org_details;
932 else
933 open csr_org_details;
934 fetch csr_org_details into l_to_organization_name;
935 if csr_org_details%notfound then
936 null;
937 -- ?? Check with ****
938 -- hr_utility.set_message(8301,'GHR_38211_NOA_FAMILY_NOTFOUND');
939 -- hr_utility.raise_error;
940 end if;
941 close csr_org_details;
942 end if;
943 --
944 -- Subject line of message
945 if l_error_msg is Not Null then
946 l_subject := 'Personnel Action : Error :' || l_first_noa_desc || ' : Req# ' || l_request_number;
947 elsif l_action_taken1 in ('ENDED') then
948 l_subject := 'Personnel Action : FYI: Ended : ' || l_first_noa_desc || ' : Req# ' || l_request_number;
949 elsif l_action_taken1 in ('UPDATE_HR_COMPLETE') then
950 l_subject := 'Personnel Action : Update HR Complete : ' || l_first_noa_desc || ' : Req# ' || l_request_number;
951 else
952 l_subject := 'Personnel Action : ' || l_first_noa_desc || ' : Req# ' || l_request_number;
953 end if;
954 p_subject := l_subject;
955 --
956 --
957 -- First line of the message body
958 if l_employee_last_name = ' ' and l_employee_first_name = ' ' and l_personnel_office_id Is Null then
959 -- l_line1 := 'Name / POI : ' ;
960 null;
961 elsif l_personnel_office_id Is Null then
962 l_line1 := l_employee_last_name || ', ' || l_employee_first_name || ' ' ||
963 l_employee_middle_names;
964 else
965 l_line1 :=
966 l_employee_last_name || ', ' || l_employee_first_name || ' ' ||
967 l_employee_middle_names || ' / ' || l_personnel_office_id;
968 end if;
969 p_line1 := l_line1;
970 -- Second line of message
971 IF l_proposed_effective_date is null then
972 l_line2 := fnd_date.date_to_displaydate(l_effective_date) || ' / ASAP';
973 ELSE
974 l_line2 := fnd_date.date_to_displaydate(l_effective_date) || ' / '|| fnd_date.date_to_displaydate(l_proposed_effective_date);
975 END IF;
976 p_line2 := l_line2;
977 -- Third line of message
978 l_line3 := l_status;
979 p_line3 := l_line3;
980 -- 4th line of message
981 l_line4 := l_routing_group_name || ' - ' || l_description;
982 p_line4 := l_line4;
983 -- 5th line of message
984 if l_date_notification_sent Is Null then
985 l_line5 := fnd_date.date_to_displaydate(sysdate) || ' / ' || fnd_date.date_to_displaydate(l_creation_date);
986 else
987 l_line5 := fnd_date.date_to_displaydate(sysdate) || ' / ' || fnd_date.date_to_displaydate(l_date_notification_sent);
988 end if;
989 p_line5 := l_line5;
990 -- 6th of message
991 if l_to_organization_name Is Not Null then
992 l_line6 := l_to_organization_name;
993 else
994 l_line6 := l_from_organization_name;
995 end if;
996 p_line6 := l_line6;
997 -- 7th line of message
998 l_line7 := l_noa_fam_desc;
999 p_line7 := l_line7;
1000 -- 8th line of message
1001 l_line8 := l_first_noa_code || ' - ' || l_first_noa_desc;
1002 p_line8 := l_line8;
1003 -- 9th line of message
1004 if l_second_noa_desc Is Not Null then
1005 l_line9 := l_line9 || l_second_noa_code || ' - ' || l_second_noa_desc;
1006 end if;
1007 p_line9 := l_line9;
1008 --
1009 --
1010 Exception when others then
1011 --
1012 -- Reset IN OUT parameters and set OUT parameters
1013 --
1014 p_subject := null;
1015 p_line1 := null;
1016 p_line2 := null;
1017 p_line3 := null;
1018 p_line4 := null;
1019 p_line5 := null;
1020 p_line6 := null;
1021 p_line7 := null;
1022 p_line8 := null;
1023 p_line9 := null;
1024 raise;
1025
1026 END SetDestinationDetails;
1027 --
1028 --
1029 procedure CompleteBlockingOfPArequest ( p_pa_request_id in Number,
1030 p_error_msg in varchar2 default null
1031 ) is
1032 l_forward_from_display_name varchar2(100);
1033 l_forward_to_display_name varchar2(100);
1034
1035 begin
1036 -- Added for Future Action process
1037 if p_error_msg Is Null then
1038 wf_engine.SetItemAttrText(
1039 itemtype => 'GHR_SF52',
1040 itemkey => p_pa_request_id,
1041 aname => 'LINE_ERROR',
1042 avalue => ''
1043 );
1044 else
1045 wf_engine.SetItemAttrText(
1046 itemtype => 'GHR_SF52',
1047 itemkey => p_pa_request_id,
1048 aname => 'LINE_ERROR',
1049 avalue => 'Update HR Error : '
1050 || substr(p_error_msg,1,1000)
1051 );
1052 end if;
1053
1054 l_forward_from_display_name := wf_engine.GetItemAttrText(itemtype => 'GHR_SF52',
1055 itemkey => p_pa_request_id,
1056 aname => 'FORWARD_FROM_DISPLAY_NAME'
1057 );
1058
1059 IF l_forward_from_display_name <> FND_GLOBAL.USER_NAME() THEN
1060 wf_engine.SetItemAttrText(itemtype => 'GHR_SF52',
1061 itemkey => p_pa_request_id,
1062 aname => 'FORWARD_FROM_DISPLAY_NAME',
1063 avalue => FND_GLOBAL.USER_NAME() );
1064 END IF;
1065
1066 wf_engine.CompleteActivity('GHR_SF52', p_pa_request_id, 'GH_NOTIFY_SF52','COMPLETE');
1067 end;
1068 --
1069 --
1070 --
1071 --
1072 procedure CompleteBlockingOfFutureAction ( p_pa_request_id in Number,
1073 p_action_taken in varchar2,
1074 p_error_msg in varchar2 default null
1075 ) is
1076 begin
1077 if p_error_msg Is Null then
1078 wf_engine.SetItemAttrText(
1079 itemtype => 'GHR_SF52',
1080 itemkey => p_pa_request_id,
1081 aname => 'LINE_ERROR',
1082 avalue => ''
1083 );
1084 else
1085 wf_engine.SetItemAttrText(
1086 itemtype => 'GHR_SF52',
1087 itemkey => p_pa_request_id,
1088 aname => 'LINE_ERROR',
1089 avalue => 'Update HR Error : '
1090 || substr(p_error_msg,1,1000)
1091 );
1092 end if;
1093 --
1094 if p_action_taken = 'UPDATE_HR_COMPLETE' then
1095 wf_engine.CompleteActivity('GHR_SF52', p_pa_request_id, 'BLOCK_FUTURE_ACTION','UPDATE_HR_COMPLETE');
1096 else
1097 wf_engine.CompleteActivity('GHR_SF52', p_pa_request_id, 'BLOCK_FUTURE_ACTION','CONTINUE');
1098 end if;
1099 --
1100 --
1101 end CompleteBlockingOfFutureAction ;
1102 --
1103 --
1104 function CheckItemAttribute
1105 (p_name in wf_item_attribute_values.name%TYPE,
1106 p_itemtype in varchar2,
1107 p_itemkey in varchar2
1108 ) return boolean IS
1109 --
1110 l_name wf_item_attribute_values.name%TYPE;
1111 --
1112 cursor csr_get_item_attr is
1113 select name
1114 from wf_item_attribute_values
1115 where item_type = upper(p_itemtype)
1116 and item_key = nvl(p_itemkey,'-9999')
1117 and name = nvl(p_name,'-9999');
1118 begin
1119 open csr_get_item_attr;
1120 fetch csr_get_item_attr into l_name;
1121 if csr_get_item_attr%notfound then
1122 close csr_get_item_attr;
1123 return false;
1124 else
1125 close csr_get_item_attr;
1126 return true;
1127 end if;
1128 end CheckItemAttribute;
1129 --
1130 PROCEDURE CheckIfPARWfEnd ( itemtype in varchar2,
1131 itemkey in varchar2,
1132 actid in number,
1133 funcmode in varchar2,
1134 result in out nocopy varchar2) is
1135 --
1136 l_action_taken ghr_pa_routing_history.action_taken%TYPE;
1137 l_load_form varchar2(200);--Bug# 7312949
1138 --
1139 cursor csr_parh is
1140 SELECT action_taken
1141 FROM ghr_pa_routing_history
1142 WHERE pa_request_id = itemkey
1143 order by pa_routing_history_id desc;
1144 --
1145
1146 l_result varchar2(4000);
1147 begin
1148 l_result := result;
1149 if funcmode = 'RUN' then
1150 open csr_parh;
1151 fetch csr_parh into l_action_taken;
1152 if csr_parh%notfound then
1153 hr_utility.set_message(8301,'GHR_38154_INVALID_PRIMARY_KEY');
1154 hr_utility.raise_error;
1155 end if;
1156 close csr_parh;
1157 --
1158 if l_action_taken in ('ENDED','UPDATE_HR_COMPLETE') then
1159 if ( CheckItemAttribute ( p_name => 'PA_REQUEST_RO',
1160 p_itemtype => itemtype,
1161 p_itemkey => itemkey ) ) then
1162 -- Bug # 8597583 modified parameters to pass with out double quotes
1163
1164 l_load_form := 'GHRWS52L:p_pa_request_id=' || Itemkey
1165 || ' p_inbox_query_only=YES' || ' WORKFLOW_NAME=GHR_US_PA_REQUEST'
1166 || ' p_wf_notification_id=&#NID';--Bug# 6923642
1167 wf_engine.SetItemAttrText( itemtype => itemtype,
1168 itemkey => itemkey,
1169 aname => 'PA_REQUEST_RO',
1170 avalue => l_load_form
1171 );
1172 end if;
1173 result := 'COMPLETE:YES';
1174 return;
1175 else
1176 result := 'COMPLETE:NO';
1177 return;
1178 end if;
1179 end if;
1180 --
1181 -- Other execution modes may be created in the future.
1182 -- Activity indicates that it does not implement a mode
1183 -- by returning null
1184 --
1185 result := '';
1186 return;
1187 --
1188 exception
1189 when others then
1190 -- The line below records this function call in the error system
1191 -- in the case of an exception.
1192 wf_core.context('GHR_SF52', 'ghr_wf_pkg.CheckIfPARWfEnd',itemtype, itemkey, to_char(actid), funcmode);
1193 --
1194 -- Reset IN OUT parameters and set OUT parameters
1195 --
1196 result := l_result;
1197 raise;
1198 --
1199 end CheckIfPARWfEnd ;
1200 --
1201 --
1202 procedure VerifyIfNtfyUpdHRUsr( itemtype in varchar2,
1203 itemkey in varchar2,
1204 actid in number,
1205 funcmode in varchar2,
1206 result in out nocopy varchar2 ) is
1207 --
1208 l_text varchar2(30);
1209 --
1210 --
1211 --
1212 l_result varchar2(4000);
1213 begin
1214 l_result := result;
1215 --
1216 if funcmode = 'RUN' then
1217 l_text := wf_engine.GetItemAttrText( itemtype => Itemtype,
1218 itemkey => Itemkey,
1219 aname => 'NTFY_UPD_HR_YES_NO');
1220 if l_text = 'NO' then
1221 result := 'COMPLETE:NO';
1222 return;
1223 else
1224 result := 'COMPLETE:YES';
1225 return;
1226 end if;
1227 end if;
1228 --
1229 result := '';
1230 return;
1231 --
1232 --
1233 exception
1234 when others then
1235 -- The line below records this function call in the error system
1236 -- in the case of an exception.
1237 wf_core.context('WGI', 'ghr_wf_wgi_pkg.VerifyIfNtfyUpdHRUsr',itemtype, itemkey, to_char(actid), funcmode);
1238 --
1239 -- Reset IN OUT parameters and set OUT parameters
1240 --
1241 result := l_result;
1242 raise;
1243 --
1244 --
1245 end VerifyIfNtfyUpdHRUsr;
1246 --
1247 --
1248 procedure CheckIfNtfyUpdHRUsr( itemtype in varchar2,
1249 itemkey in varchar2,
1250 actid in number,
1251 funcmode in varchar2,
1252 result in out nocopy varchar2 ) is
1253 --
1254 l_text varchar2(30);
1255 --
1256 --
1257 --
1258 l_result varchar2(4000);
1259 begin
1260 l_result := result;
1261 --
1262 if funcmode = 'RUN' then
1263 l_text := wf_engine.GetItemAttrText( itemtype => Itemtype,
1264 itemkey => Itemkey,
1265 aname => 'USE_UPD_HR_ONLY');
1266 if l_text = 'NO' then
1267 result := 'COMPLETE:NO';
1268 return;
1269 else
1270 result := 'COMPLETE:YES';
1271 return;
1272 end if;
1273 end if;
1274 --
1275 result := '';
1276 return;
1277 --
1278 --
1279 exception
1280 when others then
1281 -- The line below records this function call in the error system
1282 -- in the case of an exception.
1283 wf_core.context('WGI', 'ghr_wf_wgi_pkg.CheckIfNtfyUpdHRUsr',itemtype, itemkey, to_char(actid), funcmode);
1284 --
1285 -- Reset IN OUT parameters and set OUT parameters
1286 --
1287 result := l_result;
1288 raise;
1289 --
1290 --
1291 end CheckIfNtfyUpdHRUsr;
1292 --
1293 --
1294 PROCEDURE EndSF52Process( itemtype in varchar2,
1295 itemkey in varchar2,
1296 actid in number,
1297 funcmode in varchar2,
1298 result in out nocopy varchar2) is
1299 l_result varchar2(4000);
1300 begin
1301 l_result := result;
1302 if funcmode = 'RUN' then
1303 result := 'COMPLETE:COMPLETED';
1304 return;
1305 end if;
1306 --
1307 -- Other execution modes may be created in the future.
1308 -- Activity indicates that it does not implement a mode
1309 -- by returning null
1310 --
1311 result := '';
1312 return;
1313 --
1314 exception
1315 when others then
1316 -- The line below records this function call in the error system
1317 -- in the case of an exception.
1318 wf_core.context('GHR_SF52', 'ghr_wf_pkg.EndSF52Process',itemtype, itemkey, to_char(actid), funcmode);
1319 --
1320 -- Reset IN OUT parameters and set OUT parameters
1321 --
1322 result := l_result;
1323 raise;
1324 end EndSF52Process;
1325 --
1326 PROCEDURE norout( itemtype in varchar2,
1327 itemkey in varchar2,
1328 actid in number,
1329 funcmode in varchar2,
1330 result in out nocopy varchar2) is
1331 --
1332 --
1333 --BUG #13655849 increased the size of user_name
1334 l_forward_to_name ghr_pa_routing_history.user_name%TYPE;
1335 l_user_name ghr_pa_routing_history.user_name%TYPE;
1336 l_prh_rec ghr_pa_routing_history%rowtype := NULL;
1337 l_routing_group_id ghr_routing_groups.routing_group_id%type;
1338 l_effective_date ghr_pa_requests.effective_date%type;
1339 l_first_noa_id ghr_pa_requests.first_noa_id%type;
1340 l_second_noa_id ghr_pa_requests.second_noa_id%type;
1341 l_noa_family_code ghr_pa_requests.noa_family_code%type;
1342 l_gbx_id ghr_groupboxes.groupbox_id%type;
1343 --
1344 CURSOR chk_groupbox is
1345 select name,groupbox_id,display_name from ghr_groupboxes
1346 where routing_group_id = l_routing_group_id
1347 and name = l_forward_to_name;
1348
1349 CURSOR chk_groupbox_users is
1350 select groupbox_user_id,groupbox_id,user_name,
1351 INITIATOR_FLAG,
1352 REQUESTER_FLAG,
1353 AUTHORIZER_FLAG,
1354 PERSONNELIST_FLAG,
1355 APPROVER_FLAG,
1356 REVIEWER_FLAG
1357 from ghr_groupbox_users
1358 where groupbox_id = ( select groupbox_id from ghr_groupboxes
1359 where routing_group_id = l_routing_group_id )
1360 and user_name = l_forward_to_name;
1361
1362
1363 CURSOR chk_pei_wf_grp(p_user_name in varchar2) IS
1364
1365 -- Routing Group details
1366 SELECT pei.pei_information3 routing_group_id
1367 ,pei.pei_information4 initiator_flag
1368 ,pei.pei_information5 requester_flag
1369 ,pei.pei_information6 authorizer_flag
1370 ,pei.pei_information7 personnelist_flag
1371 ,pei.pei_information8 approver_flag
1372 ,pei.pei_information9 reviewer_flag
1373 ,pei.person_id person_id
1374 FROM per_people_extra_info pei
1375 ,fnd_user use
1376 WHERE use.user_name = p_user_name
1377 AND pei.person_id = use.employee_id
1378 AND pei.information_type = 'GHR_US_PER_WF_ROUTING_GROUPS'
1379 AND pei.pei_information3 = ( SELECT routing_group_id from
1380 GHR_PA_REQUESTS
1381 where pa_request_id = itemkey);
1382 --
1383 --
1384 /* Cursor c_user_emp_names(p_user_name in varchar2) is
1385 select usr.employee_id,
1386 per.first_name,
1387 per.last_name,
1388 per.middle_names
1389 from per_people_f per,
1390 fnd_user usr
1391 where upper(usr.user_name) = upper(p_user_name)
1392 and per.person_id = usr.employee_id
1393 and l_effective_date
1394 between effective_start_date
1395 and effective_end_date; */
1396 -- Bug 4863608 - Removing upper from the column name
1397 CURSOR c_user_emp_names(p_user_name in varchar2) is
1398 SELECT usr.employee_id,
1399 per.first_name,
1400 per.last_name,
1401 per.middle_names
1402 FROM per_people_f per,
1403 fnd_user usr
1404 WHERE usr.user_name = upper(p_user_name)
1405 AND per.person_id = usr.employee_id
1406 AND l_effective_date
1407 BETWEEN effective_start_date
1408 AND effective_end_date;
1409
1410 Cursor C_routing_history_id is
1411 select prh.pa_routing_history_id,
1412 prh.object_version_number
1413 from ghr_pa_routing_history prh
1414 where prh.pa_request_id = itemkey
1415 order by prh.pa_routing_history_id desc;
1416
1417 Cursor get_par
1418 is
1419 SELECT routing_group_id,nvl(effective_date,sysdate) effective_date,
1420 first_noa_id,second_noa_id,noa_family_code
1421 from ghr_pa_requests
1422 where pa_request_id = itemkey;
1423
1424 l_valid_user varchar2(1) := 'N';
1425 l_gbx_user_id ghr_groupbox_users.groupbox_user_id%type;
1426 l_initiator_flag ghr_pa_routing_history.initiator_flag%TYPE := NULL;
1427 l_requester_flag ghr_pa_routing_history.requester_flag%TYPE;
1428 l_reviewer_flag ghr_pa_routing_history.reviewer_flag%TYPE;
1429 l_authorizer_flag ghr_pa_routing_history.authorizer_flag%TYPE;
1430 l_approver_flag ghr_pa_routing_history.approver_flag%TYPE;
1431 l_approved_flag ghr_pa_routing_history.approved_flag%TYPE;
1432 l_personnelist_flag ghr_pa_routing_history.personnelist_flag%TYPE;
1433 l_user_name_employee_id per_people_f.person_id%TYPE;
1434 l_user_name_emp_first_name per_people_f.first_name%TYPE;
1435 l_user_name_emp_last_name per_people_f.last_name%TYPE;
1436 l_user_name_emp_middle_names per_people_f.middle_names%TYPE;
1437 l_current_user_name fnd_user.user_name%type;
1438 l_u_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%TYPE;
1439 l_u_prh_object_version_number ghr_pa_routing_history.object_version_number%TYPE;
1440
1441 l_result varchar2(4000);
1442 l_exp number;
1443 BEGIN
1444 l_exp := 0;
1445 l_result := result;
1446 --
1447 --
1448 IF itemtype = 'GHR_SF52' THEN
1449 IF (funcmode in ('FORWARD','TRANSFER')) then
1450 -- Get the current user name
1451 l_current_user_name := FND_GLOBAL.USER_NAME();
1452 -- Get the Forward To Information
1453 l_forward_to_name := WF_ENGINE.context_text;
1454 -- Validate the username
1455 -- a) Get the routing group id
1456 FOR rg_rec in get_par LOOP
1457 l_routing_group_id := rg_rec.routing_group_id;
1458 l_effective_date := rg_rec.effective_date;
1459 l_first_noa_id := rg_rec.first_noa_id;
1460 l_second_noa_id := rg_rec.second_noa_id;
1461 l_noa_family_code := rg_rec.noa_family_code;
1462 exit;
1463 END LOOP;
1464 l_gbx_id := NULL;
1465 l_valid_user := NULL;
1466 -- b) Check against the list of groupboxes,users under the routing group
1467 FOR gb_rec IN chk_groupbox LOOP
1468 l_gbx_id := gb_rec.groupbox_id;
1469 l_user_name := NULL;
1470 l_valid_user := 'Y';
1471 END LOOP;
1472 IF l_gbx_id is NULL THEN
1473 FOR pei_rec IN chk_pei_wf_grp(l_forward_to_name) LOOP
1474 l_initiator_flag := pei_rec.initiator_flag;
1475 l_requester_flag := pei_rec.requester_flag;
1476 l_reviewer_flag := pei_rec.reviewer_flag;
1477 l_authorizer_flag := pei_rec.authorizer_flag;
1478 l_approver_flag := pei_rec.approver_flag;
1479 l_personnelist_flag := pei_rec.personnelist_flag;
1480 l_user_name := l_forward_to_name;
1481 l_valid_user := 'Y';
1482 END LOOP;
1483 END IF;
1484 -- Create Routing History Information for the Reassignment
1485 IF l_valid_user = 'Y' THEN
1486 -- Update the current routing history record
1487 -- a) Get the current routing history details
1488 for cur_routing_history_id in C_routing_history_id loop
1489 l_u_pa_routing_history_id := cur_routing_history_id.pa_routing_history_id;
1490 l_u_prh_object_version_number := cur_routing_history_id.object_version_number;
1491 exit;
1492 end loop;
1493 -- b) Get the current user details
1494 for user_emp_names in c_user_emp_names(l_current_user_name) loop
1495 l_user_name_employee_id := user_emp_names.employee_id;
1496 l_user_name_emp_first_name := user_emp_names.first_name;
1497 l_user_name_emp_last_name := user_emp_names.last_name;
1498 l_user_name_emp_middle_names := user_emp_names.middle_names;
1499 exit;
1500 end loop;
1501 -- c) Get the current user privileges
1502 FOR pei_rec IN chk_pei_wf_grp(l_current_user_name) LOOP
1503 l_initiator_flag := pei_rec.initiator_flag;
1504 l_requester_flag := pei_rec.requester_flag;
1505 l_reviewer_flag := pei_rec.reviewer_flag;
1506 l_authorizer_flag := pei_rec.authorizer_flag;
1507 l_approver_flag := pei_rec.approver_flag;
1508 l_personnelist_flag := pei_rec.personnelist_flag;
1509 END LOOP;
1510 -- d) Call the row handler
1511 ghr_prh_upd.upd
1512 (
1513 p_pa_routing_history_id => l_u_pa_routing_history_id,
1514 p_attachment_modified_flag => 'N',
1515 p_initiator_flag => nvl(l_initiator_flag,'N'),
1516 p_approver_flag => nvl(l_approver_flag,'N'),
1517 p_reviewer_flag => nvl(l_reviewer_flag,'N'),
1518 p_requester_flag => nvl(l_requester_flag,'N'),
1519 p_authorizer_flag => nvl(l_authorizer_flag,'N'),
1520 p_personnelist_flag => nvl(l_personnelist_flag,'N'),
1521 p_approved_flag => 'N',
1522 p_user_name => l_current_user_name,
1523 p_user_name_employee_id => l_user_name_employee_id,
1524 p_user_name_emp_first_name => l_user_name_emp_first_name,
1525 p_user_name_emp_last_name => l_user_name_emp_last_name,
1526 p_user_name_emp_middle_names => l_user_name_emp_middle_names,
1527 p_action_taken => 'REASSIGNED',
1528 p_noa_family_code => l_noa_family_code,
1529 p_nature_of_action_id => l_first_noa_id,
1530 p_second_nature_of_action_id => l_second_noa_id,
1531 p_object_version_number => l_u_prh_object_version_number
1532 );
1533
1534 -- Create new record
1535 -- a) Get the user details
1536 l_user_name_employee_id := NULL;
1537 l_user_name_emp_first_name := NULL;
1538 l_user_name_emp_last_name := NULL;
1539 l_user_name_emp_middle_names := NULL;
1540 -- b) Call the row handler to create a new routing history record
1541 ghr_prh_ins.ins
1542 (
1543 p_pa_routing_history_id => l_prh_rec.pa_routing_history_id,
1544 p_pa_request_id => itemkey,
1545 p_attachment_modified_flag => nvl(l_prh_rec.attachment_modified_flag,'N') ,
1546 p_initiator_flag => 'N',
1547 p_approver_flag => 'N',
1548 p_reviewer_flag => 'N',
1549 p_requester_flag => 'N',
1550 p_authorizer_flag => 'N',
1551 p_personnelist_flag => 'N',
1552 p_approved_flag => 'N',
1553 p_user_name => l_user_name,
1554 p_user_name_employee_id => l_user_name_employee_id,
1555 p_user_name_emp_first_name => l_user_name_emp_first_name,
1556 p_user_name_emp_last_name => l_user_name_emp_last_name ,
1557 p_user_name_emp_middle_names=> l_user_name_emp_middle_names,
1558 p_groupbox_id => l_gbx_id,
1559 p_routing_seq_number => l_prh_rec.routing_seq_number,
1560 p_routing_list_id => l_prh_rec.routing_list_id,
1561 p_notepad => l_prh_rec.notepad,
1562 p_nature_of_action_id => l_first_noa_id,
1563 p_second_nature_of_action_id=> l_second_noa_id,
1564 p_noa_family_code => l_noa_family_code,
1565 p_object_version_number => l_prh_rec.object_version_number
1566 );
1567 result := 'COMPLETE:YES';
1568 ELSE
1569 l_exp := 1;
1570 app_exception.raise_exception;
1571 END IF;
1572 ELSE
1573 result := null;
1574 END IF;
1575 ELSIF (itemtype = 'OF8') THEN
1576 IF funcmode in ('FORWARD','TRANSFER') THEN
1577 l_exp := 2;
1578 app_exception.raise_exception;
1579 END IF;
1580 result := null;
1581 END IF;
1582 return;
1583 EXCEPTION WHEN OTHERS THEN
1584 --
1585 -- Reset IN OUT parameters and set OUT parameters
1586 --
1587 result := l_result;
1588 IF l_exp = 1 then
1589 result := wf_engine.eng_completed||':'||wf_engine.eng_null;
1590 fnd_message.set_name('GHR', 'GHR_38815_WF_INVALID_USER');
1591 app_exception.raise_exception;
1592 ELSIF l_exp = 2 then
1593 result := wf_engine.eng_completed||':'||wf_engine.eng_null;
1594 fnd_message.set_name('GHR', 'GHR_38674_NO_REASSIGN');
1595 app_exception.raise_exception;
1596 END IF;
1597 END norout;
1598 --
1599 END ghr_wf_pkg;