[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_WF_NTF
Source
1 PACKAGE BODY BEN_CWB_WF_NTF AS
2 /* $Header: bencwbfy.pkb 120.1 2011/02/11 12:04:27 sgnanama ship $ */
3 g_package VARCHAR2 (60) := 'BEN_CWB_WF_NTF';
4 g_itemtype VARCHAR2 (60) := 'BENCWBFY';
5 g_wfprocess VARCHAR2 (60) := 'CWBFYINTF';
6
7 FUNCTION get_issue_bdgt_ntf_comment(p_transaction_id NUMBER)
8 RETURN VARCHAR2
9 IS
10 l_comment VARCHAR2(2000);
11 l_transaction_id VARCHAR2(200);
12
13 CURSOR c1(v_transaction_id IN VARCHAR2) IS
14 SELECT attribute40
15 FROM ben_transaction
16 WHERE transaction_type = 'BDGTNTF'
17 AND transaction_id = to_number(v_transaction_id);
18 BEGIN
19 l_transaction_id := wf_engine.getitemattrtext (itemtype => 'BENCWBFY',
20 itemkey => p_transaction_id,
21 aname => 'TRANSACTION_ID'
22 );
23 OPEN c1(l_transaction_id);
24 FETCH c1 INTO l_comment;
25 CLOSE c1;
26 RETURN l_comment;
27 EXCEPTION
28 WHEN OTHERS
29 THEN
30 wf_core.CONTEXT (g_package,
31 'get_issue_bdgt_ntf_comment',
32 l_transaction_id,
33 'Error occured in while getting the isssue budget comments'
34 );
35 RAISE;
36 END;
37
38 FUNCTION get_approve_ntf_comment(p_transaction_id NUMBER)
39 RETURN VARCHAR2
40 IS
41 l_comment VARCHAR2(2000);
42 l_transaction_id VARCHAR2(200);
43 CURSOR c1(v_transaction_id IN VARCHAR2) IS
44 SELECT attribute40
45 FROM ben_transaction
46 WHERE transaction_type = 'APPRNTF'
47 AND transaction_id = to_number(v_transaction_id);
48 BEGIN
49 l_transaction_id := wf_engine.getitemattrtext (itemtype => 'BENCWBFY',
50 itemkey => p_transaction_id,
51 aname => 'TRANSACTION_ID'
52 );
53 OPEN c1(l_transaction_id);
54 FETCH c1 INTO l_comment;
55 CLOSE c1;
56 RETURN l_comment;
57 EXCEPTION
58 WHEN OTHERS
59 THEN
60 wf_core.CONTEXT (g_package,
61 'get_issue_approve_ntf_comment',
62 l_transaction_id,
63 'Error occured in while getting the approval comments'
64 );
65 RAISE;
66 END;
67
68 FUNCTION get_access_ntf_comment(p_transaction_id NUMBER)
69 RETURN VARCHAR2
70 IS
71 l_comment VARCHAR2(2000);
72 l_transaction_id VARCHAR2(200);
73 CURSOR c1(v_transaction_id IN VARCHAR2) IS
74 SELECT attribute40
75 FROM ben_transaction
76 WHERE transaction_id = to_number(v_transaction_id);
77 BEGIN
78 l_transaction_id := wf_engine.getitemattrtext (itemtype => 'BENCWBFY',
79 itemkey => p_transaction_id,
80 aname => 'TRANSACTION_ID'
81 );
82 OPEN c1(l_transaction_id);
83 FETCH c1 INTO l_comment;
84 CLOSE c1;
85 RETURN l_comment;
86 EXCEPTION
87 WHEN OTHERS
88 THEN
89 wf_core.CONTEXT (g_package,
90 'get_access_ntf_comment',
91 l_transaction_id,
92 'Error occured in while getting the access comments'
93 );
94 RAISE;
95 END;
96
97 FUNCTION get_profile_value (NAME VARCHAR2)
98 RETURN VARCHAR2
99 IS
100 l_profile_value VARCHAR2 (2000);
101 BEGIN
102 fnd_profile.get (NAME, l_profile_value);
103 RETURN l_profile_value;
104 EXCEPTION
105 WHEN OTHERS
106 THEN
107 wf_core.CONTEXT (g_package,
108 'get_profile_value',
109 NAME,
110 'Error occured in while getting the profile value'
111 );
112 RAISE;
113 END;
114
115 FUNCTION get_plan_name (p_group_per_in_ler_id IN NUMBER)
116 RETURN VARCHAR2
117 IS
118 CURSOR c1
119 IS
120 SELECT dsgn.NAME
121 FROM ben_per_in_ler pil, ben_cwb_pl_dsgn dsgn
122 WHERE pil.per_in_ler_id = p_group_per_in_ler_id
123 AND pil.group_pl_id = dsgn.pl_id
124 AND pil.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
125 AND dsgn.group_pl_id = dsgn.pl_id
126 AND dsgn.group_oipl_id = -1;
127
128 l_pl c1%ROWTYPE;
129 BEGIN
130 OPEN c1;
131
132 FETCH c1
133 INTO l_pl;
134
135 CLOSE c1;
136
137 RETURN l_pl.NAME;
138 END;
139
140 FUNCTION get_worksheet_manager_name (p_group_per_in_ler_id IN NUMBER)
141 RETURN VARCHAR2
142 IS
143 CURSOR c1
144 IS
145 SELECT per.full_name
146 FROM ben_per_in_ler pil,
147 per_all_people_f per
148 WHERE pil.per_in_ler_id = p_group_per_in_ler_id
149 AND pil.ws_mgr_id = per.person_id
150 AND sysdate between per.effective_start_date and per.effective_end_date;
151
152 l_mgr c1%ROWTYPE;
153 BEGIN
154 OPEN c1;
155
156 FETCH c1
157 INTO l_mgr;
158 CLOSE c1;
159 RETURN l_mgr.full_name;
160 END;
161
162 FUNCTION get_person_name (p_group_per_in_ler_id IN NUMBER)
163 RETURN VARCHAR2
164 IS
165 CURSOR c1
166 IS
167 SELECT info.full_name, info.brief_name
168 FROM ben_cwb_person_info info
169 WHERE info.group_per_in_ler_id = p_group_per_in_ler_id;
170
171 l_info c1%ROWTYPE;
172 l_name_profile VARCHAR (10);
173 BEGIN
174 OPEN c1;
175
176 FETCH c1
177 INTO l_info;
178
179 CLOSE c1;
180
181 l_name_profile := get_profile_value ('BEN_DISPLAY_EMPLOYEE_NAME');
182
183 IF (l_name_profile = 'BN')
184 THEN
185 RETURN l_info.brief_name;
186 ELSE
187 RETURN l_info.full_name;
188 END IF;
189 END;
190
191 FUNCTION get_for_period(p_group_per_in_ler_id IN NUMBER)
192 RETURN VARCHAR2
193 IS
194 CURSOR c11
195 IS
196 SELECT nvl(dsgn.wthn_yr_start_dt,dsgn.yr_perd_start_dt)||' - '||
197 nvl(dsgn.wthn_yr_end_dt,dsgn.yr_perd_end_dt) forPeriod
198 FROM ben_per_in_ler pil,
199 ben_cwb_pl_dsgn dsgn
200 WHERE pil.per_in_ler_id = p_group_per_in_ler_id
201 AND pil.group_pl_id = dsgn.group_pl_id
202 AND pil.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
203 AND dsgn.oipl_id = -1
204 AND dsgn.group_pl_id = dsgn.pl_id
205 AND dsgn.group_oipl_id = dsgn.oipl_id;
206 l_info c11%ROWTYPE;
207 BEGIN
208 OPEN c11;
209 FETCH c11 INTO l_info;
210 CLOSE c11;
211
212 RETURN l_info.forPeriod;
213 END;
214
215
216 FUNCTION get_ntf_conf_value (p_message_type IN VARCHAR2)
217 RETURN VARCHAR2
218 IS
219 l_name VARCHAR2 (30) := NULL;
220 ret_val VARCHAR2 (30) := NULL;
221 attr wf_item_attributes_vl_pub.wf_item_attributes_vl_tbl_type;
222 BEGIN
223 IF (p_message_type = 'BUDGET_ISSUED')
224 THEN
225 l_name := 'BDGT_ISS_NTF_CONF';
226 END IF;
227
228 IF (p_message_type = 'WS_REJECTED')
229 THEN
230 l_name := 'WS_REJ_NTF_CONF';
231 END IF;
232
233 IF (p_message_type = 'ACCESS')
234 THEN
235 l_name := 'ACCESS_NTF_CONF';
236 END IF;
237
238 IF (p_message_type = 'WS_SUBMITTED')
239 THEN
240 l_name := 'WS_SUB_NTF_CONF';
241 END IF;
242
243 IF l_name IS NOT NULL
244 THEN
245 wf_item_attributes_vl_pub.fetch_item_attributes
246 (p_item_type => g_itemtype,
247 p_name => l_name,
248 p_wf_item_attributes_vl_tbl => attr
249 );
250 ret_val := attr (1).text_default;
251 END IF;
252
253 RETURN ret_val;
254 END;
255
256 FUNCTION get_fnd_user_name (p_person_id IN NUMBER)
257 RETURN VARCHAR2
258 IS
259 l_fnd_user_name VARCHAR2 (2000);
260 l_proc VARCHAR2 (100) := 'get_fnd_user_name';
261
262 CURSOR c2
263 IS
264 SELECT user_name
265 FROM fnd_user
266 WHERE employee_id = p_person_id;
267 BEGIN
268 hr_utility.set_location ('Entering ' || g_package || ' : ' || l_proc,
269 3000
270 );
271 hr_utility.TRACE ('p_person_id : ' || p_person_id);
272
273 OPEN c2;
274
275 FETCH c2
276 INTO l_fnd_user_name;
277
278 IF c2%NOTFOUND
279 THEN
280 --10649674
281 if(p_person_id = -99) then
282 l_fnd_user_name := 'SYSADMIN';
283 else
284 hr_utility.TRACE ('fnd person does not exist ' || p_person_id);
285 wf_core.RAISE ('fnd person does not exist ' || p_person_id);
286 end if;
287 END IF;
288
289 CLOSE c2;
290
291 hr_utility.TRACE ('l_fnd_user_name : ' || l_fnd_user_name);
292 hr_utility.set_location ('Exiting ' || g_package || ' : ' || l_proc,
293 3000
294 );
295 RETURN l_fnd_user_name;
296 EXCEPTION
297 WHEN OTHERS
298 THEN
299 /* wf_core.CONTEXT (g_package,
300 'get_fnd_user_name',
301 p_person_id,
302 'Error occured in while getting the fnd_user_name'
303 );
304 RAISE;*/
305
306 return null;
307 END;
308
309
310 --
311 -- get_curren_notification_id
312 -- This function gives the notification id for a paticular user.
313 -- IN
314 -- p_item_key -- the item key
315 -- p_fnd_user_name -- fnd user name to whom the notification was sent.
316 -- RETURN
317 -- notification id
318 --
319 FUNCTION get_current_notification_id (
320 p_item_key IN VARCHAR2
321 , p_fnd_user_name IN VARCHAR2
322 )
323 RETURN NUMBER IS
324 l_notification_id NUMBER;
325 l_proc VARCHAR2 (100) := 'get_current_notification_id';
326
327 CURSOR c1 IS
328 SELECT notification_id
329 FROM wf_item_activity_statuses
330 WHERE item_key = p_item_key
331 AND item_type = g_itemtype
332 AND assigned_user = p_fnd_user_name;
333 BEGIN
334 hr_utility.set_location ('Entering ' || g_package || ' : ' || l_proc, 2000);
335 hr_utility.TRACE ('p_item_key : ' || p_item_key);
336 hr_utility.TRACE ('p_fnd_user_name : ' || p_fnd_user_name);
337
338 IF p_fnd_user_name IS NULL THEN
339 hr_utility.TRACE ('p_fnd_user_name is null');
340 wf_core.RAISE ('p_fnd_user_name is null');
341 END IF;
342
343 OPEN c1;
344 FETCH c1 INTO l_notification_id;
345
346 IF c1%NOTFOUND THEN
347 hr_utility.TRACE ( 'Notification id not present for the user '
348 || p_fnd_user_name
349 || 'for the item key'
350 || p_item_key);
351 wf_core.RAISE ( 'Notification id is not present for the user '
352 || p_fnd_user_name
353 || ' for the item key'
354 || p_item_key);
355 END IF;
356
357 CLOSE c1;
358 hr_utility.TRACE ('l_notification_id : ' || l_notification_id);
359 hr_utility.set_location ('Exiting ' || g_package || ' : ' || l_proc, 2000);
360 RETURN l_notification_id;
361 EXCEPTION
362 WHEN OTHERS THEN
363 wf_core.CONTEXT (g_package
364 , 'get_current_notification_id'
365 , p_item_key
366 , p_fnd_user_name
367 , 'Error occured in while getting the get_current_notification_id'
368 );
369 RAISE;
370 END;
371
372 PROCEDURE close_ntf (
373 itemtype IN VARCHAR2,
374 itemkey IN VARCHAR2,
375 actid IN NUMBER,
376 funcmode IN VARCHAR2,
377 RESULT OUT NOCOPY VARCHAR2
378 )
379 IS
380 l_from_fnd_user_name VARCHAR2 (2000);
381 l_notification_id VARCHAR2 (2000);
382 l_status VARCHAR2 (60);
383 BEGIN
384 l_from_fnd_user_name :=
385 wf_engine.getitemattrtext (itemtype => 'BENCWBFY',
386 itemkey => itemkey,
387 aname => 'RCVR_USER_NAME'
388 );
389 l_notification_id :=
390 get_current_notification_id (p_item_key => itemkey,
391 p_fnd_user_name => l_from_fnd_user_name
392 );
393
394 BEGIN
395 SELECT n.status
396 INTO l_status
397 FROM wf_notifications n
398 WHERE n.notification_id = l_notification_id;
399 EXCEPTION
400 WHEN NO_DATA_FOUND
401 THEN
402 wf_core.token ('NID',
403 wf_notification.getsubject (l_notification_id)
404 );
405 wf_core.RAISE ('WFNTF_NID');
406 END;
407
408 IF (l_status = 'OPEN')
409 THEN
410 wf_notification.CLOSE (nid => l_notification_id,
411 responder => l_from_fnd_user_name
412 );
413 END IF;
414 EXCEPTION
415 WHEN OTHERS
416 THEN
417 wf_core.CONTEXT (g_package,
418 'ntf_closed',
419 itemtype,
420 itemkey,
421 TO_CHAR (actid),
422 funcmode
423 );
424 RESULT := '';
425 RAISE;
426 END;
427
428 PROCEDURE set_common_item_attributes (
429 p_message_type IN VARCHAR2,
433 p_group_per_in_ler_id IN NUMBER,
430 p_item_key IN NUMBER,
431 p_rcvr_person_id IN NUMBER,
432 p_from_person_id IN NUMBER,
434 p_transaction_id IN NUMBER
435 )
436 IS
437 l_rcvr_fnd_user_name VARCHAR2 (2000);
438 l_from_fnd_user_name VARCHAR2 (2000);
439 l_err_name VARCHAR2 (30);
440 l_err_msg VARCHAR2 (2000);
441 l_err_stack VARCHAR2 (32000);
442 l_for_period VARCHAR2(30);
443 l_proc VARCHAR2 (100) := 'set_common_item_attributes';
444 BEGIN
445 hr_utility.set_location ('Entering ' || g_package || ':' || l_proc,
446 100);
447 l_rcvr_fnd_user_name := get_fnd_user_name (p_rcvr_person_id);
448 hr_utility.TRACE ('l_rcvr_fnd_user_name ' || l_rcvr_fnd_user_name);
449
450 l_for_period := get_for_period (p_group_per_in_ler_id);
451 hr_utility.TRACE ('l_for_period ' || l_for_period);
452
453 l_from_fnd_user_name := get_fnd_user_name (p_from_person_id);
454 hr_utility.TRACE ('l_from_fnd_user_name ' || l_from_fnd_user_name);
455
456 wf_engine.setitemattrtext (itemtype => g_itemtype,
457 itemkey => p_item_key,
458 aname => 'MESSAGE_TYPE',
459 avalue => p_message_type
460 );
461 hr_utility.TRACE ( ' item attribute MESSAGE_TYPE is set to '
462 || p_message_type
463 );
464 wf_engine.setitemattrtext (itemtype => g_itemtype,
465 itemkey => p_item_key,
466 aname => 'RCVR_USER_NAME',
467 avalue => l_rcvr_fnd_user_name
468 );
469 hr_utility.TRACE ( ' item attribute RCVR_USER_NAME is set to '
470 || l_rcvr_fnd_user_name
471 );
472 wf_engine.setitemattrtext (itemtype => g_itemtype,
473 itemkey => p_item_key,
474 aname => 'RCVR_PERSON_NAME',
475 avalue => get_person_name
476 (p_group_per_in_ler_id)
477 );
478 hr_utility.TRACE ( ' item attribute RCVR_PERSON_NAME is set to '
479 || l_rcvr_fnd_user_name
480 );
481 wf_engine.setitemattrtext
482 (itemtype => 'BENCWBFY',
483 itemkey => p_item_key,
484 aname => 'MANAGER_NAME',
485 avalue => get_worksheet_manager_name
486 (p_group_per_in_ler_id)
487 );
488 wf_engine.setitemattrtext
489 (itemtype => 'BENCWBFY',
490 itemkey => p_item_key,
491 aname => 'PLAN_NAME',
492 avalue => get_plan_name
493 (p_group_per_in_ler_id)
494 );
495 wf_engine.setitemattrtext (itemtype => 'BENCWBFY',
496 itemkey => p_item_key,
497 aname => 'FOR_PERIOD',
498 avalue => l_for_period
499 );
500
501 hr_utility.TRACE ( ' item attribute l_for_period is set to '
502 || l_for_period
503 );
504
505 hr_utility.TRACE (' item attribute PLAN_NAME is set ');
506 wf_engine.setitemattrtext (itemtype => 'BENCWBFY',
507 itemkey => p_item_key,
508 aname => 'GROUP_PER_IN_LER_ID',
509 avalue => p_group_per_in_ler_id
510 );
511 hr_utility.TRACE ( ' item attribute GROUP_PER_IN_LER_ID is set to '
512 || p_group_per_in_ler_id
513 );
514 wf_engine.setitemattrtext (itemtype => 'BENCWBFY',
515 itemkey => p_item_key,
516 aname => 'TRANSACTION_ID',
517 avalue => p_transaction_id
518 );
519 hr_utility.TRACE ( ' item attribute TRANSACTION_ID is set to '
520 || p_transaction_id
521 );
522 wf_engine.setitemattrtext (itemtype => g_itemtype,
523 itemkey => p_item_key,
524 aname => 'FROM_ROLE',
525 avalue => l_from_fnd_user_name
526 );
527 hr_utility.TRACE ( ' item attribute FROM_ROLE is set to '
528 || l_from_fnd_user_name
529 );
530 hr_utility.set_location ('Exiting ' || g_package || ':' || l_proc, 100);
531 END;
532
533 PROCEDURE set_access_due_item_attributes (p_item_key IN NUMBER)
534 IS
535 BEGIN
536 hr_utility.TRACE ('p_item_key ' || p_item_key);
537
538 wf_engine.setitemattrtext (itemtype => g_itemtype,
539 itemkey => p_item_key,
540 aname => 'FYI_COMMENTS',
544
541 avalue => get_access_ntf_comment(p_item_key)
542 );
543 END;
545 PROCEDURE set_appr_ntf_item_attribute (p_item_key IN NUMBER)
546 IS
547 BEGIN
548 hr_utility.TRACE ('p_item_key ' || p_item_key);
549
550 wf_engine.setitemattrtext (itemtype => g_itemtype,
551 itemkey => p_item_key,
552 aname => 'FYI_COMMENTS',
553 avalue => get_approve_ntf_comment(p_item_key)
554 );
555 END;
556
557 PROCEDURE set_iss_budgt_item_attributes (p_item_key IN NUMBER)
558 IS
559 BEGIN
560 hr_utility.TRACE ('p_item_key ' || p_item_key);
561
562 wf_engine.setitemattrtext (itemtype => g_itemtype,
563 itemkey => p_item_key,
564 aname => 'FYI_COMMENTS',
565 avalue => get_issue_bdgt_ntf_comment(p_item_key)
566 );
567 END;
568
569 PROCEDURE cwb_fyi_ntf_api (
570 p_transaction_id IN NUMBER,
571 p_message_type IN VARCHAR2,
572 p_rcvr_person_id IN NUMBER,
573 p_from_person_id IN NUMBER,
574 p_group_per_in_ler_id IN NUMBER
575 )
576 IS
577 l_proc VARCHAR2 (61) := 'cwb_fyi_ntf_api';
578 l_itemkey NUMBER;
579 l_rcvr_user_name VARCHAR2 (60);
580 l_err_name VARCHAR2 (30);
581 l_err_msg VARCHAR2 (2000);
582 l_err_stack VARCHAR2 (32000);
583 BEGIN
584
585 hr_utility.set_location ('Entering ' || g_package || ':' || l_proc, 10);
586
587 --
588 -- A unique itemkey is generated for every workflow transaction
589 --
590 SELECT ben_cwb_wf_ntf_s.NEXTVAL
591 INTO l_itemkey
592 FROM DUAL;
593
594 hr_utility.TRACE ('l_itemkey : ' || l_itemkey);
595
596 IF l_itemkey IS NULL
597 THEN
598 hr_utility.TRACE ('l_itemkey is null');
599 fnd_message.set_name ('BEN', 'BEN_93399_CWB_NTF_ITEM_KEY_ERR');
600 fnd_message.raise_error;
601 END IF;
602
603 IF p_rcvr_person_id IS NULL
604 THEN
605 hr_utility.TRACE ('p_rcvr_person_id is null');
606 fnd_message.set_name ('BEN', 'BEN_93400_CWB_NTF_NO_PERSON_ID');
607 fnd_message.raise_error;
608 END IF;
609
610 hr_utility.TRACE ('p_rcvr_person_id : ' || p_rcvr_person_id);
611
612 BEGIN
613 wf_engine.createprocess (itemtype => g_itemtype,
614 itemkey => l_itemkey,
615 process => g_wfprocess
616 );
617 hr_utility.TRACE ( 'Workflow process '
618 || g_wfprocess
619 || ' for itemtype '
620 || g_itemtype
621 || ' itemkey '
622 || l_itemkey
623 || ' is created'
624 );
625 EXCEPTION
626 WHEN OTHERS
627 THEN
628 wf_core.get_error (l_err_name, l_err_msg, l_err_stack);
629
630 IF (l_err_name IS NOT NULL)
631 THEN
632 hr_utility.TRACE ( 'Following Workflow error has occured'
633 || l_err_msg
634 || 'Error stack for this is '
635 || l_err_stack
636 );
637 fnd_message.set_name ('BEN', 'BEN_93401_CWB_NTF_WF_ERR');
638 fnd_message.set_token ('ERR_NAME', l_err_name, FALSE);
639 fnd_message.set_token ('ERR_STACK',
640 SUBSTRB (l_err_stack, 25000),
641 FALSE
642 );
643 wf_core.CLEAR;
644 fnd_message.raise_error;
645 ELSE
646 hr_utility.TRACE
647 ( 'A general error has occured while creating the workflow process '
648 || 'and it is not a worflow error'
649 );
650 fnd_message.set_name ('BEN', 'BEN_93402_CWB_NTF_CRE_PROC_ERR');
651 fnd_message.raise_error;
652 END IF;
653 END;
654
655 --
656 -- setting the common item attributes.
657 --
658 set_common_item_attributes
659 (p_message_type => p_message_type,
660 p_item_key => l_itemkey,
661 p_rcvr_person_id => p_rcvr_person_id,
662 p_from_person_id => p_from_person_id,
663 p_group_per_in_ler_id => p_group_per_in_ler_id,
664 p_transaction_id => p_transaction_id
665 );
666 hr_utility.TRACE ('common item attributes are set');
667
668 --
669 -- setting issued budget item attributes.
670 --
671 IF ( p_message_type = 'BUDGET_ISSUED'
672 OR p_message_type = 'BUDGET_ISSUED_LLM'
673 )
674 THEN
675 set_iss_budgt_item_attributes (p_item_key => l_itemkey);
676 hr_utility.TRACE
677 ('issued budget item attributes are set successfully');
678 END IF;
679
683 IF (p_message_type = 'WS_REJECTED')
680 --
681 -- setting approval item attributes.
682 --
684 THEN
685 set_appr_ntf_item_attribute (p_item_key => l_itemkey);
686 hr_utility.TRACE ('approval item attributes are set successfully');
687 END IF;
688
689 --
690
691 --
692 IF (p_message_type = 'WS_RECALLED')
693 THEN
694 set_appr_ntf_item_attribute (p_item_key => l_itemkey);
695 hr_utility.TRACE ('RECALLED item attributes are set successfully');
696 END IF;
697
698 --
699 -- setting access and due dates item attributes.
700 --
701 IF (p_message_type = 'ACCESS')
702 THEN
703 set_access_due_item_attributes (p_item_key => l_itemkey);
704 hr_utility.TRACE
705 ('access and due dates item attributes are set successfully');
706 END IF;
707
708 IF (p_message_type = 'WS_SUBMITTED')
709 THEN
710 set_appr_ntf_item_attribute (p_item_key => l_itemkey);
711 hr_utility.TRACE
712 ('ws submitted item attributes are set successfully');
713 END IF;
714
715 BEGIN
716 wf_engine.startprocess (itemtype => g_itemtype,
717 itemkey => l_itemkey);
718 hr_utility.TRACE ( 'Workflow process '
719 || g_wfprocess
720 || ' for itemtype '
721 || g_itemtype
722 || ' itemkey '
723 || l_itemkey
724 || ' is started'
725 );
726 EXCEPTION
727 WHEN OTHERS
728 THEN
729 wf_core.get_error (l_err_name, l_err_msg, l_err_stack);
730
731 IF (l_err_name IS NOT NULL)
732 THEN
733 hr_utility.TRACE ( 'Following Workflow error has occured'
734 || l_err_msg
735 || 'Follwing is the error stack '
736 || l_err_stack
737 );
738 fnd_message.set_name ('BEN', 'BEN_93401_CWB_NTF_WF_ERR');
739 fnd_message.set_token ('ERR_NAME', l_err_name, FALSE);
740 fnd_message.set_token ('ERR_STACK',
741 SUBSTRB (l_err_stack, 25000),
742 FALSE
743 );
744 wf_core.CLEAR;
745 fnd_message.raise_error;
746 ELSE
747 hr_utility.TRACE
748 ( 'A general error has occured while starting the workflow process '
749 || 'and it is not a worflow error'
750 );
751 fnd_message.set_name ('BEN', 'BEN_93404_CWB_NTF_STRT_PRC_ERR');
752 fnd_message.set_token ('ITEM_KEY', l_itemkey);
753 fnd_message.raise_error;
754 END IF;
755 END;
756
757 hr_utility.set_location ('Exiting ' || g_package || ':' || l_proc, 10);
758 EXCEPTION
759 WHEN OTHERS
760 THEN
761 RAISE;
762 END;
763
764 --
765 -- cwb_plan_comp_ntf_api
766 -- Need to design this notification
767 --
768 PROCEDURE cwb_plan_comp_ntf_api (
769 p_transaction_id IN NUMBER,
770 p_message_type IN VARCHAR2,
771 p_from_person_id IN NUMBER,
772 p_group_per_in_ler_id IN NUMBER
773 )
774 IS
775 CURSOR c1
776 IS
777 SELECT role_id, role_name
778 FROM pqh_roles
779 WHERE role_type_cd = 'CWB';
780
781 CURSOR c2 (l_role_id NUMBER)
782 IS
783 SELECT pei.person_id person_id, ppf.full_name person_name,
784 usr.user_name user_name, usr.user_id user_id
785 FROM per_people_extra_info pei,
786 per_all_people_f ppf,
787 fnd_user usr,
788 pqh_roles rls
789 WHERE information_type = 'PQH_ROLE_USERS'
790 AND pei.person_id = ppf.person_id
791 AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
792 AND ppf.effective_end_date
793 AND usr.employee_id = ppf.person_id
794 AND rls.role_id = TO_NUMBER (pei.pei_information3)
795 AND NVL (pei.pei_information5, 'Y') = 'Y'
796 AND rls.role_id = l_role_id;
797
798 l_proc VARCHAR2 (61) := g_package || ':' || 'plan_comp_ntf';
799 l_itemkey NUMBER;
800 BEGIN
801 hr_utility.set_location (l_proc || ' Entering ', 10);
802
803 FOR i IN c1
804 LOOP
805 hr_utility.set_location ('checking people for role ' || i.role_name,
806 20
807 );
808
809 FOR j IN c2 (i.role_id)
810 LOOP
811 hr_utility.set_location ( 'person '
812 || j.person_name
813 || ' has this role ',
814 20
815 );
816 hr_utility.set_location ('user ' || j.user_name
817 || ' has this role ',
818 20
819 );
820 cwb_fyi_ntf_api (p_transaction_id => p_transaction_id,
821 p_message_type => p_message_type,
825 );
822 p_rcvr_person_id => j.person_id,
823 p_from_person_id => p_from_person_id,
824 p_group_per_in_ler_id => p_group_per_in_ler_id
826 END LOOP;
827 END LOOP;
828
829 hr_utility.set_location (l_proc || ' Exiting ', 100);
830 END;
831
832 PROCEDURE which_message (
833 itemtype IN VARCHAR2,
834 itemkey IN VARCHAR2,
835 actid IN NUMBER,
836 funcmode IN VARCHAR2,
837 RESULT OUT NOCOPY VARCHAR2
838 )
839 IS
840 l_message_type VARCHAR2 (60);
841 l_proc VARCHAR2 (100) := 'which_message';
842 BEGIN
843 hr_utility.set_location ('Entering ' || g_package || ' : ' || l_proc,
844 10000
845 );
846 hr_utility.TRACE ('itemtype : ' || itemtype);
847 hr_utility.TRACE ('itemkey : ' || itemkey);
848 hr_utility.TRACE ('actid : ' || actid);
849 hr_utility.TRACE ('funcmode : ' || funcmode);
850
851 IF (funcmode = 'RUN')
852 THEN
853 l_message_type :=
854 wf_engine.getitemattrtext (itemtype => itemtype,
855 itemkey => itemkey,
856 aname => 'MESSAGE_TYPE'
857 );
858
859 IF ( l_message_type = 'ACCESS'
860 OR l_message_type = 'BUDGET_ISSUED'
861 OR l_message_type = 'WS_SUBMITTED'
862 OR l_message_type = 'WS_REJECTED'
863 OR l_message_type = 'WS_RECALLED'
864 )
865 THEN
866 RESULT := 'COMPLETE:' || l_message_type;
867 RETURN;
868 END IF;
869 END IF;
870
871 IF (funcmode = 'CANCEL')
872 THEN
873 RESULT := 'COMPLETE';
874 RETURN;
875 END IF;
876
877 hr_utility.TRACE ('l_message_type : ' || l_message_type);
878 RESULT := '';
879 hr_utility.set_location ('Exiting ' || g_package || ' : ' || l_proc,
880 10000
881 );
882 RETURN;
883 EXCEPTION
884 WHEN OTHERS
885 THEN
886 wf_core.CONTEXT (g_package,
887 'which_message',
888 itemtype,
889 itemkey,
890 TO_CHAR (actid),
891 funcmode
892 );
893 RESULT := '';
894 RAISE;
895 END;
896
897 PROCEDURE is_notification_sent (
898 itemtype IN VARCHAR2,
899 itemkey IN VARCHAR2,
900 actid IN NUMBER,
901 funcmode IN VARCHAR2,
902 RESULT OUT NOCOPY VARCHAR2
903 )
904 IS
905 l_message_type VARCHAR2 (60);
906 l_proc VARCHAR2 (100) := 'is_notification_sent';
907 BEGIN
908 l_message_type :=
909 wf_engine.getitemattrtext (itemtype => itemtype,
910 itemkey => itemkey,
911 aname => 'MESSAGE_TYPE'
912 );
913 RESULT := 'COMPLETE:' || get_ntf_conf_value (l_message_type);
914 RETURN;
915 EXCEPTION
916 WHEN OTHERS
917 THEN
918 wf_core.CONTEXT (g_package,
919 'is_notification_sent',
920 itemtype,
921 itemkey,
922 TO_CHAR (actid),
923 funcmode
924 );
925 RESULT := '';
926 RAISE;
927 END;
928 END;