[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_WF_NTF
Source
1 PACKAGE BODY BEN_CWB_WF_NTF AS
2 /* $Header: bencwbfy.pkb 120.0 2005/05/28 03:58:55 appldev noship $ */
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 hr_utility.TRACE ('fnd person does not exist ' || p_person_id);
281 wf_core.RAISE ('fnd person does not exist ' || p_person_id);
282 END IF;
283
284 CLOSE c2;
285
286 hr_utility.TRACE ('l_fnd_user_name : ' || l_fnd_user_name);
287 hr_utility.set_location ('Exiting ' || g_package || ' : ' || l_proc,
288 3000
289 );
290 RETURN l_fnd_user_name;
291 EXCEPTION
292 WHEN OTHERS
293 THEN
294 /* wf_core.CONTEXT (g_package,
295 'get_fnd_user_name',
296 p_person_id,
297 'Error occured in while getting the fnd_user_name'
298 );
299 RAISE;*/
300
301 return null;
302 END;
303
304
305 --
306 -- get_curren_notification_id
307 -- This function gives the notification id for a paticular user.
308 -- IN
309 -- p_item_key -- the item key
310 -- p_fnd_user_name -- fnd user name to whom the notification was sent.
311 -- RETURN
312 -- notification id
313 --
314 FUNCTION get_current_notification_id (
315 p_item_key IN VARCHAR2
316 , p_fnd_user_name IN VARCHAR2
317 )
318 RETURN NUMBER IS
319 l_notification_id NUMBER;
320 l_proc VARCHAR2 (100) := 'get_current_notification_id';
321
322 CURSOR c1 IS
323 SELECT notification_id
324 FROM wf_item_activity_statuses
325 WHERE item_key = p_item_key
326 AND item_type = g_itemtype
327 AND assigned_user = p_fnd_user_name;
328 BEGIN
329 hr_utility.set_location ('Entering ' || g_package || ' : ' || l_proc, 2000);
330 hr_utility.TRACE ('p_item_key : ' || p_item_key);
331 hr_utility.TRACE ('p_fnd_user_name : ' || p_fnd_user_name);
332
333 IF p_fnd_user_name IS NULL THEN
334 hr_utility.TRACE ('p_fnd_user_name is null');
335 wf_core.RAISE ('p_fnd_user_name is null');
336 END IF;
337
338 OPEN c1;
339 FETCH c1 INTO l_notification_id;
340
341 IF c1%NOTFOUND THEN
342 hr_utility.TRACE ( 'Notification id not present for the user '
343 || p_fnd_user_name
344 || 'for the item key'
345 || p_item_key);
346 wf_core.RAISE ( 'Notification id is not present for the user '
347 || p_fnd_user_name
348 || ' for the item key'
349 || p_item_key);
350 END IF;
351
352 CLOSE c1;
353 hr_utility.TRACE ('l_notification_id : ' || l_notification_id);
354 hr_utility.set_location ('Exiting ' || g_package || ' : ' || l_proc, 2000);
355 RETURN l_notification_id;
356 EXCEPTION
357 WHEN OTHERS THEN
358 wf_core.CONTEXT (g_package
359 , 'get_current_notification_id'
360 , p_item_key
361 , p_fnd_user_name
362 , 'Error occured in while getting the get_current_notification_id'
363 );
364 RAISE;
365 END;
366
367 PROCEDURE close_ntf (
368 itemtype IN VARCHAR2,
369 itemkey IN VARCHAR2,
370 actid IN NUMBER,
371 funcmode IN VARCHAR2,
372 RESULT OUT NOCOPY VARCHAR2
373 )
374 IS
375 l_from_fnd_user_name VARCHAR2 (2000);
376 l_notification_id VARCHAR2 (2000);
377 l_status VARCHAR2 (60);
378 BEGIN
379 l_from_fnd_user_name :=
380 wf_engine.getitemattrtext (itemtype => 'BENCWBFY',
381 itemkey => itemkey,
382 aname => 'RCVR_USER_NAME'
383 );
384 l_notification_id :=
385 get_current_notification_id (p_item_key => itemkey,
386 p_fnd_user_name => l_from_fnd_user_name
387 );
388
389 BEGIN
390 SELECT n.status
391 INTO l_status
392 FROM wf_notifications n
393 WHERE n.notification_id = l_notification_id;
394 EXCEPTION
395 WHEN NO_DATA_FOUND
396 THEN
397 wf_core.token ('NID',
398 wf_notification.getsubject (l_notification_id)
399 );
400 wf_core.RAISE ('WFNTF_NID');
401 END;
402
403 IF (l_status = 'OPEN')
404 THEN
405 wf_notification.CLOSE (nid => l_notification_id,
406 responder => l_from_fnd_user_name
407 );
408 END IF;
409 EXCEPTION
410 WHEN OTHERS
411 THEN
412 wf_core.CONTEXT (g_package,
413 'ntf_closed',
414 itemtype,
415 itemkey,
416 TO_CHAR (actid),
417 funcmode
418 );
419 RESULT := '';
420 RAISE;
421 END;
422
423 PROCEDURE set_common_item_attributes (
424 p_message_type IN VARCHAR2,
425 p_item_key IN NUMBER,
426 p_rcvr_person_id IN NUMBER,
427 p_from_person_id IN NUMBER,
428 p_group_per_in_ler_id IN NUMBER,
429 p_transaction_id IN NUMBER
430 )
431 IS
432 l_rcvr_fnd_user_name VARCHAR2 (2000);
433 l_from_fnd_user_name VARCHAR2 (2000);
437 l_for_period VARCHAR2(30);
434 l_err_name VARCHAR2 (30);
435 l_err_msg VARCHAR2 (2000);
436 l_err_stack VARCHAR2 (32000);
438 l_proc VARCHAR2 (100) := 'set_common_item_attributes';
439 BEGIN
440 hr_utility.set_location ('Entering ' || g_package || ':' || l_proc,
441 100);
442 l_rcvr_fnd_user_name := get_fnd_user_name (p_rcvr_person_id);
443 hr_utility.TRACE ('l_rcvr_fnd_user_name ' || l_rcvr_fnd_user_name);
444
445 l_for_period := get_for_period (p_group_per_in_ler_id);
446 hr_utility.TRACE ('l_for_period ' || l_for_period);
447
448 l_from_fnd_user_name := get_fnd_user_name (p_from_person_id);
449 hr_utility.TRACE ('l_from_fnd_user_name ' || l_from_fnd_user_name);
450
451 wf_engine.setitemattrtext (itemtype => g_itemtype,
452 itemkey => p_item_key,
453 aname => 'MESSAGE_TYPE',
454 avalue => p_message_type
455 );
456 hr_utility.TRACE ( ' item attribute MESSAGE_TYPE is set to '
457 || p_message_type
458 );
459 wf_engine.setitemattrtext (itemtype => g_itemtype,
460 itemkey => p_item_key,
461 aname => 'RCVR_USER_NAME',
462 avalue => l_rcvr_fnd_user_name
463 );
464 hr_utility.TRACE ( ' item attribute RCVR_USER_NAME is set to '
465 || l_rcvr_fnd_user_name
466 );
467 wf_engine.setitemattrtext (itemtype => g_itemtype,
468 itemkey => p_item_key,
469 aname => 'RCVR_PERSON_NAME',
470 avalue => get_person_name
471 (p_group_per_in_ler_id)
472 );
473 hr_utility.TRACE ( ' item attribute RCVR_PERSON_NAME is set to '
474 || l_rcvr_fnd_user_name
475 );
476 wf_engine.setitemattrtext
477 (itemtype => 'BENCWBFY',
478 itemkey => p_item_key,
479 aname => 'MANAGER_NAME',
480 avalue => get_worksheet_manager_name
481 (p_group_per_in_ler_id)
482 );
483 wf_engine.setitemattrtext
484 (itemtype => 'BENCWBFY',
485 itemkey => p_item_key,
486 aname => 'PLAN_NAME',
487 avalue => get_plan_name
488 (p_group_per_in_ler_id)
489 );
490 wf_engine.setitemattrtext (itemtype => 'BENCWBFY',
491 itemkey => p_item_key,
492 aname => 'FOR_PERIOD',
493 avalue => l_for_period
494 );
495
496 hr_utility.TRACE ( ' item attribute l_for_period is set to '
497 || l_for_period
498 );
499
500 hr_utility.TRACE (' item attribute PLAN_NAME is set ');
501 wf_engine.setitemattrtext (itemtype => 'BENCWBFY',
502 itemkey => p_item_key,
503 aname => 'GROUP_PER_IN_LER_ID',
504 avalue => p_group_per_in_ler_id
505 );
506 hr_utility.TRACE ( ' item attribute GROUP_PER_IN_LER_ID is set to '
507 || p_group_per_in_ler_id
508 );
509 wf_engine.setitemattrtext (itemtype => 'BENCWBFY',
510 itemkey => p_item_key,
511 aname => 'TRANSACTION_ID',
512 avalue => p_transaction_id
513 );
514 hr_utility.TRACE ( ' item attribute TRANSACTION_ID is set to '
515 || p_transaction_id
516 );
517 wf_engine.setitemattrtext (itemtype => g_itemtype,
518 itemkey => p_item_key,
519 aname => 'FROM_ROLE',
520 avalue => l_from_fnd_user_name
521 );
522 hr_utility.TRACE ( ' item attribute FROM_ROLE is set to '
523 || l_from_fnd_user_name
524 );
525 hr_utility.set_location ('Exiting ' || g_package || ':' || l_proc, 100);
526 END;
527
528 PROCEDURE set_access_due_item_attributes (p_item_key IN NUMBER)
529 IS
530 BEGIN
531 hr_utility.TRACE ('p_item_key ' || p_item_key);
532
533 wf_engine.setitemattrtext (itemtype => g_itemtype,
534 itemkey => p_item_key,
538 END;
535 aname => 'FYI_COMMENTS',
536 avalue => get_access_ntf_comment(p_item_key)
537 );
539
540 PROCEDURE set_appr_ntf_item_attribute (p_item_key IN NUMBER)
541 IS
542 BEGIN
543 hr_utility.TRACE ('p_item_key ' || p_item_key);
544
545 wf_engine.setitemattrtext (itemtype => g_itemtype,
546 itemkey => p_item_key,
547 aname => 'FYI_COMMENTS',
548 avalue => get_approve_ntf_comment(p_item_key)
549 );
550 END;
551
552 PROCEDURE set_iss_budgt_item_attributes (p_item_key IN NUMBER)
553 IS
554 BEGIN
555 hr_utility.TRACE ('p_item_key ' || p_item_key);
556
557 wf_engine.setitemattrtext (itemtype => g_itemtype,
558 itemkey => p_item_key,
559 aname => 'FYI_COMMENTS',
560 avalue => get_issue_bdgt_ntf_comment(p_item_key)
561 );
562 END;
563
564 PROCEDURE cwb_fyi_ntf_api (
565 p_transaction_id IN NUMBER,
566 p_message_type IN VARCHAR2,
567 p_rcvr_person_id IN NUMBER,
568 p_from_person_id IN NUMBER,
569 p_group_per_in_ler_id IN NUMBER
570 )
571 IS
572 l_proc VARCHAR2 (61) := 'cwb_fyi_ntf_api';
573 l_itemkey NUMBER;
574 l_rcvr_user_name VARCHAR2 (60);
575 l_err_name VARCHAR2 (30);
576 l_err_msg VARCHAR2 (2000);
577 l_err_stack VARCHAR2 (32000);
578 BEGIN
579
580 hr_utility.set_location ('Entering ' || g_package || ':' || l_proc, 10);
581
582 --
583 -- A unique itemkey is generated for every workflow transaction
584 --
585 SELECT ben_cwb_wf_ntf_s.NEXTVAL
586 INTO l_itemkey
587 FROM DUAL;
588
589 hr_utility.TRACE ('l_itemkey : ' || l_itemkey);
590
591 IF l_itemkey IS NULL
592 THEN
593 hr_utility.TRACE ('l_itemkey is null');
594 fnd_message.set_name ('BEN', 'BEN_93399_CWB_NTF_ITEM_KEY_ERR');
595 fnd_message.raise_error;
596 END IF;
597
598 IF p_rcvr_person_id IS NULL
599 THEN
600 hr_utility.TRACE ('p_rcvr_person_id is null');
601 fnd_message.set_name ('BEN', 'BEN_93400_CWB_NTF_NO_PERSON_ID');
602 fnd_message.raise_error;
603 END IF;
604
605 hr_utility.TRACE ('p_rcvr_person_id : ' || p_rcvr_person_id);
606
607 BEGIN
608 wf_engine.createprocess (itemtype => g_itemtype,
609 itemkey => l_itemkey,
610 process => g_wfprocess
611 );
612 hr_utility.TRACE ( 'Workflow process '
613 || g_wfprocess
614 || ' for itemtype '
615 || g_itemtype
616 || ' itemkey '
617 || l_itemkey
618 || ' is created'
619 );
620 EXCEPTION
621 WHEN OTHERS
622 THEN
623 wf_core.get_error (l_err_name, l_err_msg, l_err_stack);
624
625 IF (l_err_name IS NOT NULL)
626 THEN
627 hr_utility.TRACE ( 'Following Workflow error has occured'
628 || l_err_msg
629 || 'Error stack for this is '
630 || l_err_stack
631 );
632 fnd_message.set_name ('BEN', 'BEN_93401_CWB_NTF_WF_ERR');
633 fnd_message.set_token ('ERR_NAME', l_err_name, FALSE);
634 fnd_message.set_token ('ERR_STACK',
635 SUBSTRB (l_err_stack, 25000),
636 FALSE
637 );
638 wf_core.CLEAR;
639 fnd_message.raise_error;
640 ELSE
641 hr_utility.TRACE
642 ( 'A general error has occured while creating the workflow process '
643 || 'and it is not a worflow error'
644 );
645 fnd_message.set_name ('BEN', 'BEN_93402_CWB_NTF_CRE_PROC_ERR');
646 fnd_message.raise_error;
647 END IF;
648 END;
649
650 --
651 -- setting the common item attributes.
652 --
653 set_common_item_attributes
654 (p_message_type => p_message_type,
655 p_item_key => l_itemkey,
656 p_rcvr_person_id => p_rcvr_person_id,
657 p_from_person_id => p_from_person_id,
658 p_group_per_in_ler_id => p_group_per_in_ler_id,
659 p_transaction_id => p_transaction_id
660 );
661 hr_utility.TRACE ('common item attributes are set');
662
663 --
667 OR p_message_type = 'BUDGET_ISSUED_LLM'
664 -- setting issued budget item attributes.
665 --
666 IF ( p_message_type = 'BUDGET_ISSUED'
668 )
669 THEN
670 set_iss_budgt_item_attributes (p_item_key => l_itemkey);
671 hr_utility.TRACE
672 ('issued budget item attributes are set successfully');
673 END IF;
674
675 --
676 -- setting approval item attributes.
677 --
678 IF (p_message_type = 'WS_REJECTED')
679 THEN
680 set_appr_ntf_item_attribute (p_item_key => l_itemkey);
681 hr_utility.TRACE ('approval item attributes are set successfully');
682 END IF;
683
684 --
685
686 --
687 IF (p_message_type = 'WS_RECALLED')
688 THEN
689 set_appr_ntf_item_attribute (p_item_key => l_itemkey);
690 hr_utility.TRACE ('RECALLED item attributes are set successfully');
691 END IF;
692
693 --
694 -- setting access and due dates item attributes.
695 --
696 IF (p_message_type = 'ACCESS')
697 THEN
698 set_access_due_item_attributes (p_item_key => l_itemkey);
699 hr_utility.TRACE
700 ('access and due dates item attributes are set successfully');
701 END IF;
702
703 IF (p_message_type = 'WS_SUBMITTED')
704 THEN
705 set_appr_ntf_item_attribute (p_item_key => l_itemkey);
706 hr_utility.TRACE
707 ('ws submitted item attributes are set successfully');
708 END IF;
709
710 BEGIN
711 wf_engine.startprocess (itemtype => g_itemtype,
712 itemkey => l_itemkey);
713 hr_utility.TRACE ( 'Workflow process '
714 || g_wfprocess
715 || ' for itemtype '
716 || g_itemtype
717 || ' itemkey '
718 || l_itemkey
719 || ' is started'
720 );
721 EXCEPTION
722 WHEN OTHERS
723 THEN
724 wf_core.get_error (l_err_name, l_err_msg, l_err_stack);
725
726 IF (l_err_name IS NOT NULL)
727 THEN
728 hr_utility.TRACE ( 'Following Workflow error has occured'
729 || l_err_msg
730 || 'Follwing is the error stack '
731 || l_err_stack
732 );
733 fnd_message.set_name ('BEN', 'BEN_93401_CWB_NTF_WF_ERR');
734 fnd_message.set_token ('ERR_NAME', l_err_name, FALSE);
735 fnd_message.set_token ('ERR_STACK',
736 SUBSTRB (l_err_stack, 25000),
737 FALSE
738 );
739 wf_core.CLEAR;
740 fnd_message.raise_error;
741 ELSE
742 hr_utility.TRACE
743 ( 'A general error has occured while starting the workflow process '
744 || 'and it is not a worflow error'
745 );
746 fnd_message.set_name ('BEN', 'BEN_93404_CWB_NTF_STRT_PRC_ERR');
747 fnd_message.set_token ('ITEM_KEY', l_itemkey);
748 fnd_message.raise_error;
749 END IF;
750 END;
751
752 hr_utility.set_location ('Exiting ' || g_package || ':' || l_proc, 10);
753 EXCEPTION
754 WHEN OTHERS
755 THEN
756 RAISE;
757 END;
758
759 --
760 -- cwb_plan_comp_ntf_api
761 -- Need to design this notification
762 --
763 PROCEDURE cwb_plan_comp_ntf_api (
764 p_transaction_id IN NUMBER,
765 p_message_type IN VARCHAR2,
766 p_from_person_id IN NUMBER,
767 p_group_per_in_ler_id IN NUMBER
768 )
769 IS
770 CURSOR c1
771 IS
772 SELECT role_id, role_name
773 FROM pqh_roles
774 WHERE role_type_cd = 'CWB';
775
776 CURSOR c2 (l_role_id NUMBER)
777 IS
778 SELECT pei.person_id person_id, ppf.full_name person_name,
779 usr.user_name user_name, usr.user_id user_id
780 FROM per_people_extra_info pei,
781 per_all_people_f ppf,
782 fnd_user usr,
783 pqh_roles rls
784 WHERE information_type = 'PQH_ROLE_USERS'
785 AND pei.person_id = ppf.person_id
786 AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
787 AND ppf.effective_end_date
788 AND usr.employee_id = ppf.person_id
789 AND rls.role_id = TO_NUMBER (pei.pei_information3)
790 AND NVL (pei.pei_information5, 'Y') = 'Y'
791 AND rls.role_id = l_role_id;
792
793 l_proc VARCHAR2 (61) := g_package || ':' || 'plan_comp_ntf';
794 l_itemkey NUMBER;
795 BEGIN
796 hr_utility.set_location (l_proc || ' Entering ', 10);
797
798 FOR i IN c1
799 LOOP
800 hr_utility.set_location ('checking people for role ' || i.role_name,
801 20
802 );
803
804 FOR j IN c2 (i.role_id)
805 LOOP
806 hr_utility.set_location ( 'person '
807 || j.person_name
808 || ' has this role ',
809 20
810 );
811 hr_utility.set_location ('user ' || j.user_name
812 || ' has this role ',
813 20
814 );
815 cwb_fyi_ntf_api (p_transaction_id => p_transaction_id,
816 p_message_type => p_message_type,
817 p_rcvr_person_id => j.person_id,
818 p_from_person_id => p_from_person_id,
819 p_group_per_in_ler_id => p_group_per_in_ler_id
820 );
821 END LOOP;
822 END LOOP;
823
824 hr_utility.set_location (l_proc || ' Exiting ', 100);
825 END;
826
827 PROCEDURE which_message (
828 itemtype IN VARCHAR2,
829 itemkey IN VARCHAR2,
830 actid IN NUMBER,
831 funcmode IN VARCHAR2,
832 RESULT OUT NOCOPY VARCHAR2
833 )
834 IS
835 l_message_type VARCHAR2 (60);
836 l_proc VARCHAR2 (100) := 'which_message';
837 BEGIN
838 hr_utility.set_location ('Entering ' || g_package || ' : ' || l_proc,
839 10000
840 );
841 hr_utility.TRACE ('itemtype : ' || itemtype);
842 hr_utility.TRACE ('itemkey : ' || itemkey);
843 hr_utility.TRACE ('actid : ' || actid);
844 hr_utility.TRACE ('funcmode : ' || funcmode);
845
846 IF (funcmode = 'RUN')
847 THEN
848 l_message_type :=
849 wf_engine.getitemattrtext (itemtype => itemtype,
850 itemkey => itemkey,
851 aname => 'MESSAGE_TYPE'
852 );
853
854 IF ( l_message_type = 'ACCESS'
855 OR l_message_type = 'BUDGET_ISSUED'
856 OR l_message_type = 'WS_SUBMITTED'
857 OR l_message_type = 'WS_REJECTED'
858 OR l_message_type = 'WS_RECALLED'
859 )
860 THEN
861 RESULT := 'COMPLETE:' || l_message_type;
862 RETURN;
863 END IF;
864 END IF;
865
866 IF (funcmode = 'CANCEL')
867 THEN
868 RESULT := 'COMPLETE';
869 RETURN;
870 END IF;
871
872 hr_utility.TRACE ('l_message_type : ' || l_message_type);
873 RESULT := '';
874 hr_utility.set_location ('Exiting ' || g_package || ' : ' || l_proc,
875 10000
876 );
877 RETURN;
878 EXCEPTION
879 WHEN OTHERS
880 THEN
881 wf_core.CONTEXT (g_package,
882 'which_message',
883 itemtype,
884 itemkey,
885 TO_CHAR (actid),
886 funcmode
887 );
888 RESULT := '';
889 RAISE;
890 END;
891
892 PROCEDURE is_notification_sent (
893 itemtype IN VARCHAR2,
894 itemkey IN VARCHAR2,
895 actid IN NUMBER,
896 funcmode IN VARCHAR2,
897 RESULT OUT NOCOPY VARCHAR2
898 )
899 IS
900 l_message_type VARCHAR2 (60);
901 l_proc VARCHAR2 (100) := 'is_notification_sent';
902 BEGIN
903 l_message_type :=
904 wf_engine.getitemattrtext (itemtype => itemtype,
905 itemkey => itemkey,
906 aname => 'MESSAGE_TYPE'
907 );
908 RESULT := 'COMPLETE:' || get_ntf_conf_value (l_message_type);
909 RETURN;
910 EXCEPTION
911 WHEN OTHERS
912 THEN
913 wf_core.CONTEXT (g_package,
914 'is_notification_sent',
915 itemtype,
916 itemkey,
917 TO_CHAR (actid),
918 funcmode
919 );
920 RESULT := '';
921 RAISE;
922 END;
923 END;