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