[Home] [Help]
PACKAGE BODY: APPS.ZPB_WF_NTF
Source
1 PACKAGE BODY ZPB_WF_NTF AS
2 /* $Header: zpbwfntf.plb 120.6 2007/12/04 16:23:24 mbhat ship $ */
3
4 PROCEDURE SetRole (AdHocRole in varchar2, ExpDays in number, RoleDisplay in varchar2 default NULL)
5 IS
6 -- May replace with AdHocRole
7 roleDisplayName varchar2(320) := AdHocRole;
8 roleName varchar2(320) :=AdHocRole;
9 addDays number :=ExpDays;
10
11 BEGIN
12
13 if RoleDisplay is not NULL then
14 roleDisplayName := RoleDisplay;
15 end if;
16
17 wf_directory.CreateAdHocRole(role_name => roleName,
18 role_display_name => roleDisplayName,
19 language => NULL,
20 territory => NULL,
21 role_description => NULL,
22 notification_preference => 'MAILHTML',
23 role_users => NULL,
24 email_address => NULL,
25 fax => NULL,
26 status => 'ACTIVE',
27 expiration_date => sysdate+addDays);
28 end SetRole;
29 --
30 --
31
32 procedure VALIDATE_BUS_AREA (itemtype in varchar2,
33 itemkey in varchar2,
34 actid in number,
35 funcmode in varchar2,
36 resultout out nocopy varchar2)
37
38 IS
39
40 TaskID number;
41 l_business_area_id number;
42 l_version_id number;
43
44
45
46 CURSOR c_val_msgs is
47 select error_type, message
48 from ZPB_BUSAREA_VALIDATIONS;
49
50 l_cur_rec c_val_msgs%ROWTYPE;
51
52 errorlist varchar2(4000) := null;
53 warninglist varchar2(4000) := null;
54 l_chr_newline VARCHAR2(8);
55
56 BEGIN
57 l_chr_newline := fnd_global.newline;
58 IF (funcmode = 'RUN') THEN
59 TaskID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
60 Itemkey => ItemKey,
61 aname => 'TASKID');
62
63 select business_area_id into l_business_area_id
64 from zpb_analysis_cycles
65 where analysis_cycle_id = (select analysis_cycle_id
66 from zpb_analysis_cycle_tasks where task_id = TaskId);
67
68 wf_engine.SetItemAttrText(Itemtype => ItemType,
69 Itemkey => ItemKey,
70 aname => 'BUSINESSAREAID',
71 avalue => l_business_area_id);
72
73 select VERSION_ID into l_version_id
74 from ZPB_BUSAREA_VERSIONS
75 where VERSION_TYPE = 'P' and BUSINESS_AREA_ID = l_business_area_id;
76
77 wf_engine.SetItemAttrText(Itemtype => ItemType,
78 Itemkey => ItemKey,
79 aname => 'VERSIONID',
80 avalue => l_version_id);
81
82 ZPB_BUSAREA_VAL.VAL_AGAINST_EPB(l_version_id);
83 ZPB_BUSAREA_VAL.VAL_AGAINST_EPF(l_version_id);
84 ZPB_BUSAREA_VAL.VAL_DEFINITION(l_version_id);
85
86 for l_cur_rec in c_val_msgs loop
87 if l_cur_rec.error_type = 'E' then
88 errorlist := errorlist || l_cur_rec.message || l_chr_newline;
89 elsif l_cur_rec.error_type = 'W' then
90 warninglist := warninglist || l_cur_rec.message || l_chr_newline;
91 end if;
92 end loop;
93
94 if errorlist is not null then
95 resultout:= 'E';
96 elsif warninglist is not null then
97 resultout := 'W';
98 else
99 resultout := 'N';
100 end if ;
101 wf_engine.SetItemAttrText(Itemtype => ItemType,
102 Itemkey => ItemKey,
103 aname => 'VALIDATIONERROR',
104 avalue => ERRORLIST);
105
106 wf_engine.SetItemAttrText(Itemtype => ItemType,
107 Itemkey => ItemKey,
108 aname => 'VALIDATIONWARNING',
109 avalue => warninglist);
110
111 END IF;
112
113 return;
114
115 exception
116 when NO_DATA_FOUND then
117 Null;
118
119 when others then
120 WF_CORE.CONTEXT('ZPB_WF_NTF.VALIDATE_BUS_AREA', itemtype, itemkey, to_char(actid), funcmode);
121 raise;
122
123 end VALIDATE_BUS_AREA;
124
125
126 PROCEDURE RemUser (AdHocRole in varchar2,
127 UserList in varchar2)
128 IS
129 roleName varchar2(320) :=AdHocRole;
130 BEGIN
131 wf_directory.RemoveUsersFromAdHocRole(roleName, UserList);
132 end RemUser;
133
134
135 --
136 --
137 -- RemALL cleans up wf_local_roles. It is called from OES by ntf.purgerole
138 -- ntf.purgerole also calls wf_purge.notificatons and wf_purgeItem
139 -- along with this so all expired notifications are cleaned.
140 -- These are called by expiration_date.
141 --
142 PROCEDURE RemALL (AdHocRole in varchar2)
143 IS
144 roleName varchar2(320) :=AdHocRole;
145 BEGIN
146 wf_directory.RemoveUsersFromAdHocRole(roleName);
147
148 delete wf_local_roles
149 where name = roleName;
150
151 commit;
152
153 exception
154 when others then
155 raise;
156
157 end RemALL;
158
159
160 function MakeRoleName (ACID in Number, TaskID in Number, UserID in Number default NULL) return varchar2
161
162 AS
163
164 charDate varchar2(20);
165 rolename varchar2(320);
166 lcount number;
167
168 BEGIN
169
170 lcount := 1;
171
172 while lcount > 0
173 loop
174
175 charDate := to_char(sysdate, 'J-SSSSS');
176 if UserID is not NULL then
177 rolename := 'ZPB'|| to_char( TaskID) || '-' || charDate || '-' || UserID;
178 else
179 rolename := 'ZPB'|| to_char( TaskID) || '-' || charDate;
180 end if;
181
182 select count(name)
183 into lcount
184 from wf_roles
185 where name = rolename;
186
187 if lcount > 0 then
188 dbms_lock.sleep(1);
189 end if;
190
191 end loop;
192
193
194 return rolename;
195
196 exception
197 when others then
198 raise;
199
200 END;
201
202
203 function GetFNDResp (RespKey in Varchar2) return varchar2
204
205 AS
206
207 rolename varchar2(320);
208 respID number;
209 appID number;
210
211 BEGIN
212
213 select APPLICATION_ID
214 into appID
215 from FND_APPLICATION
216 where APPLICATION_SHORT_NAME = 'ZPB';
217
218
219 if RespKey = 'ZPB' then
220 rolename := 'FND_RESP'|| appID;
221 return rolename;
222 else
223
224 select RESPONSIBILITY_ID
225 into respID
226 from fnd_responsibility_vl
227 where APPLICATION_ID = appID and RESPONSIBILITY_KEY = RespKey;
228
229 rolename := 'FND_RESP'|| appID || ':' || respID;
230 return rolename;
231
232 end if;
233
234 exception
235 when NO_DATA_FOUND then
236 return 'NOT_FOUND';
237
238 when others then
239 raise;
240
241 END;
242
243 procedure SET_ATTRIBUTES (itemtype in varchar2,
244 itemkey in varchar2,
245 actid in number,
246 funcmode in varchar2,
247 resultout out nocopy varchar2)
248 IS
249
250 ACNAME varchar2(300);
251 ACID number;
252 errMsg varchar2(320);
253 ActEntry varchar2(30);
254 TaskID number;
255 RoleName varchar2(320);
256 InstDesc varchar2(300);
257 InstanceID number;
258 Deadline varchar2(30);
259 DeadDate date;
260 Relative number := 0;
261 DType varchar(24);
262 WDeadline varchar2(30);
263 WType varchar(24);
264 workflowprocess varchar2(30);
265 TASKPARAMNAME varchar2(100);
266 UserList varchar(4000);
267 Subject varchar(4000);
268 Message varchar(4000);
269 UserToNotifyP varchar2(1);
270 authorID number;
271 l_authorIDT varchar2(4000);
272 thisOwnerID number;
273 thisOwner varchar2(150);
274 l_business_area_id number;
275 l_deadDate varchar2(100);
276
277 CURSOR c_tparams is
278 select NAME, value
279 from ZPB_TASK_PARAMETERS
280 where TASK_ID = TaskID;
281
282 v_tparams c_tparams%ROWTYPE;
283
284
285 CURSOR c_baControllers is
286 select C.USER_NAME
287 from FND_RESPONSIBILITY A,
288 ZPB_ACCOUNT_STATES B,
289 FND_USER C
290 where B.BUSINESS_AREA_ID = l_business_area_id AND
291 A.RESPONSIBILITY_ID = B.RESP_ID AND
292 A.RESPONSIBILITY_KEY = 'ZPB_SUPER_CONTROLLER_RESP' AND
293 C.USER_ID = B.USER_ID ;
294
295 v_baControllers c_baControllers%ROWTYPE;
296
297 CURSOR c_bpadmin is
298 select C.USER_NAME
299 from FND_RESPONSIBILITY A,
300 ZPB_ACCOUNT_STATES B,
301 FND_USER C
302 where A.RESPONSIBILITY_ID = B.RESP_ID AND
303 A.RESPONSIBILITY_KEY = 'ZPB_CONTROLLER_RESP' AND
304 C.USER_ID = B.USER_ID ;
305
306 v_bpadmin c_bpadmin%ROWTYPE;
307
308 -- B4951035 - ACID was hard coded to 8891.
309 CURSOR c_bpowner is
310 select C.USER_NAME
311 from zpb_analysis_cycles A,
312 FND_USER C
313 where analysis_cycle_id = ACID AND
314 C.USER_ID = A.OWNER_ID ;
315
316 v_bpowner c_bpowner%ROWTYPE;
317
318 BEGIN
319
320 IF (funcmode = 'RUN') THEN
321 resultout :='COMPLETE:B';
322
323
324 SELECT ACTIVITY_NAME, PROCESS_NAME INTO ActEntry, workflowprocess
325 FROM WF_PROCESS_ACTIVITIES
326 WHERE INSTANCE_ID=actid;
327
328
329 -- B 4951035 - ERROR IN CODE FOR NOTIFICATIONS TO BPO
330 ACID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
331 Itemkey => ItemKey,
332 aname => 'ACID');
333
334 ACNAME := wf_engine.GetItemAttrText(Itemtype => ItemType,
335 Itemkey => ItemKey,
336 aname => 'ACNAME');
337
338
339 TaskID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
340 Itemkey => ItemKey,
341 aname => 'TASKID');
342
343
344 InstanceID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
345 Itemkey => ItemKey,
346 aname => 'INSTANCEID');
347
348
349 thisOwnerID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
350 Itemkey => ItemKey,
351 aname => 'OWNERID');
352 thisOwner := zpb_wf_ntf.ID_to_FNDUser(thisOwnerID);
353
354
355 select business_area_id into l_business_area_id from
356 zpb_analysis_cycles where analysis_cycle_id = (select analysis_cycle_id
357 from zpb_analysis_cycle_tasks where task_id = TaskId) ;
358
359 select INSTANCE_DESCRIPTION
360 into InstDesc
361 from ZPB_ANALYSIS_CYCLE_INSTANCES
362 where INSTANCE_AC_ID = InstanceID;
363
364 -- set descripton
365 wf_engine.SetItemAttrText(Itemtype => ItemType,
366 Itemkey => ItemKey,
367 aname => 'INSTANCEDESC',
368 avalue => InstDesc);
369
370
371 -- set up last_updated_by as authorID and who
372 -- the notification is from.
373 -- if workflowprocess = 'NOTIFY' then
374 -- bug 3482485
375
376 select value into l_authorIDT
377 from zpb_task_parameters
378 where task_id = TaskID and name = 'OWNER_ID';
379
380 authorID := to_number(l_authorIDT);
381
382 wf_engine.SetItemAttrText(Itemtype => ItemType,
383 Itemkey => ItemKey,
384 aname => '#FROM_ROLE',
385 avalue => ZPB_WF_NTF.ID_to_FNDUser(authorID));
386
387 -- end if;
388
389 -- read parameters from ZPB_TASK_PARAMETERS using task ID.
390
391 UserList := 'NONE';
392 Dtype := 'NONE';
393 UserToNotifyP := 'N';
394
395 for v_tparams in c_tparams loop
396
397 taskParamName := v_tparams.name;
398 if taskParamName = 'NOTIFY_SUBJECT' then
399 Subject := v_tparams.value;
400
401 wf_engine.SetItemAttrText(Itemtype => ItemType,
402 Itemkey => ItemKey,
403 aname => 'SUBJECT',
404 avalue => Subject);
405
406 elsif taskParamName = 'NOTIFY_CONTENT' then
407 Message := v_tparams.value;
408
409 wf_engine.SetItemAttrText(Itemtype => ItemType,
410 Itemkey => ItemKey,
411 aname => 'ISSUEMSG',
412 avalue => message);
413
414 elsif taskParamName = 'DURATION_TYPE' then
415 DType := v_tparams.value;
416 elsif taskParamName = 'DURATION_VALUE' then
417 Deadline := v_tparams.value;
418
419 elsif taskParamName = 'USERS_TO_NOTIFY' then
420 UserList := v_tparams.value;
421 UserToNotifyP := 'Y';
422
423 elsif taskParamName = 'WAIT_TYPE' then
424 DType := v_tparams.value;
425 elsif taskParamName = 'WAIT_VALUE' then
426 Deadline := v_tparams.value;
427
428
429
430 elsif taskParamName = 'NOTIFY_BACONTROLLERS' then
431
432 for c_rec in c_baControllers
433 loop
434 UserList := UserList ||', '||c_rec.user_name;
435 end loop;
436 UserToNotifyP := 'Y';
437
438 elsif taskParamName = 'NOTIFY_BPADMIN' then
439
440 for c_rec in c_bpadmin
441 loop
442 UserList := UserList ||', '||c_rec.user_name;
443 end loop;
444 UserToNotifyP := 'Y';
445
446 -- B4951035 future issue
447 -- NOTE none of the NOTIFY_B param names are in fnd_lookups
448 -- this looks like it was not fully implementd and
449 -- can not ever execute with typeO in NOTIFY_BPOWNER
450 -- I do not know if it should execute. I currently will not change
451 -- this.
452
453 elsif taskParamName = 'NOTIFY_BPOWNERN' then
454
455 for c_rec in c_bpowner
456 loop
457 UserList := UserList ||', '||c_rec.user_name;
458 end loop;
459 UserToNotifyP := 'Y';
460
461 else
462 errMsg := v_tparams.value;
463 end if;
464
465 end loop;
466
467
468 if Dtype <> 'NONE' then
469
470 if Dtype = 'DATE' then
471 DeadDate := to_Date(Deadline,'YYYY/MM/DD-HH24:MI:SS');
472 Relative := (DeadDate - sysdate) * 1440;
473 elsif Dtype = 'DAY' then
474 Relative := to_number(Deadline)* 1440;
475 DeadDate := sysdate + to_number(Deadline);
476 elsif Dtype = 'WEEK' then
477 Relative := to_number(Deadline)* 7 * 1440;
478 DeadDate := sysdate + (to_number(Deadline) * 7);
479 elsif Dtype = 'MONTH' then
480 DeadDate := add_months(sysdate, to_number(Deadline));
481 Relative := (DeadDate - sysdate) * 1440;
482 end if;
483
484 l_deadDate := to_char(DeadDate,'DD-MON-YY HH24:MI:SS');
485
486 -- relative default is 0 not delay!
487 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
488 Itemkey => ItemKey,
489 aname => 'WAITDEADLINE',
490 avalue => Relative);
491
492 -- relative default is 0 not delay!
493 wf_engine.SetItemAttrText(Itemtype => ItemType,
494 Itemkey => ItemKey,
495 aname => 'DEADLINEDISP',
496 avalue => l_deadDate);
497 end if;
498
499 -- Create AdHoc Roles when needed and set user lists for WF Directory
500 -- sets wf_local_roles, wf_local_user_roles for views wf_users, wf_roles
501 -- and wf_user_roles.
502
503 if UserToNotifyP = 'Y' then
504
505 -- test fix 11/18/2003
506 if UserList = 'NONE' then
507 resultout := 'COMPLETE:B';
508 return;
509 end if;
510
511 -- this is support for * OLD STYLE * user_to_notify
512 if UserList is not NULL OR UserList <> 'NONE' then
513 -- B4951035 prevent relative from being passed as zero, that is now bad for WF
514 RoleName := zpb_wf_ntf.OLD_STYLE_USERS(instanceID, taskID, thisOwner, thisOwnerID, relative+7, UserList);
515
516 if RoleName <> '#NOROLE#' then
517 wf_engine.SetItemAttrText(Itemtype => ItemType,
518 Itemkey => ItemKey,
519 aname => 'EPBPERFORMER',
520 avalue => RoleName);
521 end if;
522 -- this is set to A becuase at startup EPBPERFORMER is defaulted to BPcycle owner/publisher.
523 resultout := 'COMPLETE:A';
524 else
525 -- UserList is NONE if none you can go around the notification.
526 resultout := 'COMPLETE:B';
527 end if;
528
529 else -- users_to_notify_param=N
530
531 -- B4951035 prevent relative from being passed as zero, that is now bad for WF
532 UserToNotifyP := zpb_wf_ntf.set_users_to_notify(taskID, ItemKey, workflowprocess, relative+7, thisOwner, thisOwnerID);
533
534 if UserToNotifyP = 'B' then
535 resultout := 'COMPLETE:B';
536 else
537 resultout := 'COMPLETE:A';
538 end if;
539
540 end if;
541
542
543 -- b 4948928
544 -- if Expired WF users have been detected then send list to BPO or its proxy
545 -- otherwise do nothing.
546 zpb_wf_ntf.SendExpiredUserMsg(thisOwnerID, TaskID, itemType);
547
548
549 END IF;
550 return;
551
552 exception
553 when NO_DATA_FOUND then
554 Null;
555
556 when others then
557 WF_CORE.CONTEXT('ZPB_WF.SET_ATTRIBUTES', itemtype, itemkey, to_char(actid), funcmode);
558 raise;
559
560 end SET_ATTRIBUTES;
561
562 procedure SET_PAUSE (itemtype in varchar2,
563 itemkey in varchar2,
564 actid in number,
565 funcmode in varchar2,
566 resultout out NOCOPY varchar2)
567 IS
568
569 -- NOTE: all values are for the WF that is ending need new values set for the
570 -- process to be started.
571
572 ACID number;
573 ACNAME varchar2(300);
574 TaskID number;
575 InstanceID number;
576 charDate varchar2(30);
577 DeadDate date;
578 workflowprocess varchar2(30);
579 UserList varchar(4000);
580
581 BEGIN
582
583 IF (funcmode = 'RUN') THEN
584
585 ACID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
586 Itemkey => ItemKey,
587 aname => 'ACID');
588
589 ACNAME := wf_engine.GetItemAttrText(Itemtype => ItemType,
590 Itemkey => ItemKey,
591 aname => 'ACNAME');
592
593 TaskID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
594 Itemkey => ItemKey,
595 aname => 'TASKID');
596
597 InstanceID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
598 Itemkey => ItemKey,
599 aname => 'INSTANCEID');
600
601
602 -- owner := wf_engine.GetItemAttrText(Itemtype => ItemType,
603 -- Itemkey => ItemKey,
604 -- aname => 'FNDUSERNAM');
605
606 -- first save the current status code in previous status code field
607 update ZPB_ANALYSIS_CYCLES
608 set prev_status_code = status_code
609 where ANALYSIS_CYCLE_ID = InstanceID;
610
611 -- then update status_code to PAUSED
612 update ZPB_ANALYSIS_CYCLES
613 set status_code = 'PAUSED'
614 where ANALYSIS_CYCLE_ID = InstanceID;
615
616 -- Set the ReviewBP task whose notification action caused the
617 -- instance to be paused to complete
618 -- This is normally done in RunNextTask procedure, but this task
619 -- never calls that procedure
620 update ZPB_ANALYSIS_CYCLE_TASKS
621 set status_code = 'COMPLETE'
622 where TASK_ID = TaskID;
623
624 resultout :='COMPLETE';
625
626 END IF;
627 return;
628
629 exception
630 when NO_DATA_FOUND then
631 Null;
632
633 when others then
634 WF_CORE.CONTEXT('ZPB_WF.RunNextTask', itemtype, itemkey, to_char(actid), funcmode);
635 raise;
636
637 end SET_PAUSE;
638
639 function Get_EPB_Users (RespKey in Varchar2) return clob
640 AS
641
642 -- UserList varchar2(4000);
643 UserList clob;
644 userhold varchar2(30);
645 fndResp varchar2(30);
646
647 CURSOR c_users is
648 select user_name
649 from wf_user_roles
650 where role_name = fndResp;
651
652 v_users c_users%ROWTYPE;
653
654
655 CURSOR c_zpbusers is
656 select distinct user_name
657 from wf_user_roles
658 where ROLE_ORIG_SYSTEM = fndResp;
659
660 v_zpbusers c_zpbusers%ROWTYPE;
661
662 BEGIN
663
664 fndResp := ZPB_WF_NTF.GetFNDResp(RespKey);
665
666 if RespKEY = 'ZPB' then
667 -- by application
668 for v_zpbusers in c_zpbusers loop
669 if c_zpbusers%ROWCOUNT = 1 then
670 userlist := v_zpbusers.user_name;
671 else
672 userhold := v_zpbusers.user_name;
673 userlist := userlist ||',' || userhold;
674 end if;
675 end loop;
676
677 else
678 -- by responsibility
679 for v_users in c_users loop
680 if c_users%ROWCOUNT = 1 then
681 userlist := v_users.user_name;
682 else
683 userhold := v_users.user_name;
684 userlist := userlist ||',' || userhold;
685 end if;
686 end loop;
687
688 end if;
689
690 return userlist;
691
692 exception
693 when others then
694 raise;
695
696 END;
697
698 function NotifyForTask (TaskID in Number) return varchar2
699
700 AS
701
702 itemtype varchar2(8) := 'EPBCYCLE';
703 workflowProcess varchar2(30) := 'NOEVENTNTF';
704 itemkey varchar2(240);
705 UserName varchar2(30);
706 ACName varchar2(300);
707 ACIDEvent number;
708 TaskName varchar2(256);
709 charDate varchar2(30);
710 owner varchar2(30) := fnd_global.user_name;
711 ownerID number := fnd_global.USER_ID;
712 respID number := fnd_global.RESP_ID;
713 respAppID number := fnd_global.RESP_APPL_ID;
714
715
716 CURSOR c_eventACID is
717 select distinct v.analysis_cycle_id thisACID, v.name thisACName,
718 u.user_name thisUser
719 from zpb_all_cycles_v v, zpb_process_details_v pro, fnd_user u
720 where v.analysis_cycle_id in (select pa.analysis_cycle_id from zpb_ac_param_values pa
721 where pa.param_id = 20 and pa.value in(select d.value from ZPB_PROCESS_DETAILS_V d
722 where d.name = 'CREATE_EVENT_IDENTIFIER' and d.task_id = TaskID))
723 and v.last_updated_by = u.user_id;
724
725 -- v_eventACID c_eventACID%ROWTYPE;
726
727 BEGIN
728
729 select task_name into TaskName
730 from zpb_process_details_v
731 where task_id = TaskID and name = 'CREATE_EVENT_IDENTIFIER';
732
733 ACIDEvent := NULL;
734 FOR each in c_eventACID loop
735 ACIDEvent := each.thisACID;
736 ACName := each.thisACName;
737 UserName := each.thisUser;
738
739 -- create itemkey for workflow
740 charDate := to_char(sysdate, 'MM/DD/YYYY-HH24:MI:SS');
741 itemkey := rtrim(substr(ACName, 1, 50), ' ') || '-' || to_char(ACIDEvent) || '-0-' || workflowprocess || '-' || charDate ;
742
743 -- Create WF start process instance
744 wf_engine.CreateProcess(ItemType => ItemType,
745 itemKey => ItemKey,
746 process => WorkflowProcess);
747
748
749 -- set Cycle ID!
750 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
751 Itemkey => ItemKey,
752 aname => 'ACID',
753 avalue => ACIDEvent);
754 -- set cycle Name!
755 wf_engine.SetItemAttrText(Itemtype => ItemType,
756 Itemkey => ItemKey,
757 aname => 'ACNAME',
758 avalue => ACNAME);
759
760 -- set task Name!
761 wf_engine.SetItemAttrText(Itemtype => ItemType,
762 Itemkey => ItemKey,
763 aname => 'TASKNAME',
764 avalue => TaskName);
765
766 -- This should be the EPB controller user.
767 wf_engine.SetItemOwner(ItemType => ItemType,
768 ItemKey => ItemKey,
769 owner => owner);
770
771 -- set EPBPerformer to owner name for notifications!
772 wf_engine.SetItemAttrText(Itemtype => ItemType,
773 Itemkey => ItemKey,
774 aname => 'EPBPERFORMER',
775 avalue => UserName);
776
777 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
778 Itemkey => ItemKey,
779 aname => 'OWNERID',
780 avalue => ownerID);
781
782 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
783 Itemkey => ItemKey,
784 aname => 'RESPID',
785 avalue => respID);
786
787 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
788 Itemkey => ItemKey,
789 aname => 'RESPAPPID',
790 avalue => respAppID);
791
792 wf_engine.SetItemAttrText(Itemtype => ItemType,
793 Itemkey => ItemKey,
794 aname => '#FROM_ROLE',
795 avalue => owner);
796
797 -- Now that all is created and set: START the PROCESS!
798
799 wf_engine.StartProcess(ItemType => ItemType,
800 ItemKey => ItemKey);
801 commit;
802
803 end loop;
804
805 if ACIDEvent is Null then
806 return 'NONE_FOUND';
807 else
808 return 'NOTIFIED';
809 end if;
810
811 exception
812 when others then
813 raise;
814
815 END;
816
817 procedure NOTIFY_ON_DELETE (numericID in number,
818 IDType in Varchar2 default 'TASK')
819
820 IS
821
822 retval varchar2(30);
823 taskID number;
824
825 CURSOR c_acid is
826 select TASK_ID, TASK_NAME
827 from ZPB_PROCESS_DETAILS_V
828 where ANALYSIS_CYCLE_ID = numericID AND
829 name = 'CREATE_EVENT_IDENTIFIER';
830
831 v_acid c_acid%ROWTYPE;
832
833 BEGIN
834
835 if IDType = 'TASK' then
836 retval := NotifyForTask(numericID);
837 else
838 for v_acid in c_acid loop
839 if c_acid%ROWCOUNT >= 1 then
840 taskID :=v_acid.task_id;
841 retval := NotifyForTask(taskID);
842 end if;
843 end loop;
844 end if;
845
846 exception
847 when others then
848 raise;
849
850 end NOTIFY_ON_DELETE;
851
852
853
854 Function SET_USERS_TO_NOTIFY (taskID in number,
855 itemkey in varchar2,
856 workflowprocess in varchar2,
857 relative in number,
858 thisOwner in varchar2,
859 thisOwnerID in number) return varchar2
860
861 IS
862
863 errMsg varchar2(320);
864 RoleName varchar2(320);
865 TASKPARAMNAME varchar2(100) := NULL;
866 UserList varchar2(2000);
867 UserToNotifyP varchar2(1);
868 Rtype varchar2(4000) := NULL;
869 thisRecipient varchar2(100);
870 thisUserID number;
871 cCount number;
872 InstanceID number;
873 itemtype varchar2(30) := 'EPBCYCLE';
874 l_label varchar2(50);
875 NewDispName varchar2(360);
876
877
878 CURSOR c_recipient is
879 select NAME, value
880 from ZPB_TASK_PARAMETERS
881 where TASK_ID = TaskID and name = 'SPECIFIED_NOTIFICATION_RECIPIENT';
882
883 v_recipient c_recipient%ROWTYPE;
884
885 CURSOR c_type is
886 select name, value
887 from ZPB_TASK_PARAMETERS
888 where TASK_ID = TaskID and name = 'NOTIFICATION_RECIPIENT_TYPE';
889
890 v_type c_type%ROWTYPE;
891
892
893 BEGIN
894 for v_type in c_type loop
895 TASKPARAMNAME := v_type.name;
896 Rtype := v_type.value;
897 end loop;
898
899 if TASKPARAMNAME is NULL then
900 if workflowprocess = 'WAIT_TASK' then
901 return 'A';
902 else
903 return 'B';
904 end if;
905 end if;
906
907 select count(*)
908 into cCount
909 from ZPB_TASK_PARAMETERS
910 where TASK_ID = TaskID and name = 'SPECIFIED_NOTIFICATION_RECIPIENT';
911
912
913 InstanceID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
914 Itemkey => ItemKey,
915 aname => 'INSTANCEID');
916 if Rtype = 'SPECIFIED' then
917
918 for v_recipient in c_recipient loop
919 thisRecipient := v_recipient.value;
920
921 if c_recipient%ROWCOUNT = 1 then
922 if cCount > 1 then
923
924 -- THIS SHOULD NOT BE FOR REVIEW FRAMEWORK - FYI style
925 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, TaskID);
926 zpb_wf_ntf.SetRole(rolename, relative+7);
927 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisRecipient);
928 thisUserID := FNDUser_to_ID(thisRecipient);
929 zpb_wf_ntf.add_Shadow(rolename, thisUserID);
930
931 else
932
933 -- THIS CAN BE FOR REVIEW FRAMEWORK - response style
934 -- only one user
935 thisUserID := FNDUser_to_ID(thisRecipient);
936 -- make the Ad Hoc role to hold both the dataowner and shadow
937 if zpb_wf_ntf.has_Shadow(thisUserID) = 'Y' then
938 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, TaskID);
939 select distinct display_name
940 into NewDispName
941 from wf_users
942 where name = thisRecipient;
943 -- add (And Shadows) display to role dispaly name
944 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_ANDSHADOWS');
945 l_label := FND_MESSAGE.GET;
946 NewDispName := NewDispName || l_label;
947
948 zpb_wf_ntf.SetRole(rolename, relative+7, NewDispName);
949 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisRecipient);
950 zpb_wf_ntf.add_Shadow(rolename, thisUserID);
951 else
952 rolename := thisRecipient;
953 end if;
954 end if;
955 else
956 -- not for Review Framework
957 -- b 4948928 added test as part of this bug premptively
958 if zpb_wf_ntf.user_in_role(rolename, thisRecipient) = 'N' then
959 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisRecipient);
960 end if;
961
962 thisUserID := FNDUser_to_ID(thisRecipient);
963 zpb_wf_ntf.add_Shadow(rolename, thisUserID);
964 end if;
965
966 end loop;
967 elsif Rtype = 'ZPB_CONTROLLER_RESP' then
968 -- transform to FND resp.
969 roleName := zpb_wf_ntf.GetFNDResp('ZPB_CONTROLLER_RESP');
970 elsif Rtype = 'ZPB_ALL_USERS' then
971
972 -- transform to all ZPB users.
973 -- not setting shadows for all users
974 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, TaskID);
975 zpb_wf_ntf.SetRole(rolename, relative+7);
976 zpb_wf_ntf.Set_EPB_Users(rolename, 'ZPB');
977
978 elsif Rtype = 'OWNER_OF_AC' then
979 -- uses thisOwner
980 -- make the Ad Hoc role to hold both the dataowner and shadow
981 if zpb_wf_ntf.has_Shadow(thisOwnerID) = 'Y' then
982 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, TaskID);
983 select distinct display_name
984 into NewDispName
985 from wf_users
986 where name = thisOwner;
987
988 -- add (And Shadows) display to role dispaly name
989 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_ANDSHADOWS');
990 l_label := FND_MESSAGE.GET;
991 NewDispName := NewDispName || l_label;
992 zpb_wf_ntf.SetRole(rolename, relative, NewDispName);
993 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisOwner);
994 zpb_wf_ntf.add_Shadow(rolename, thisOwnerID);
995 else
996 rolename := thisOwner;
997 end if;
998 elsif Rtype = 'NONE' or Rtype is NULL then
999 return 'B';
1000 end if;
1001
1002 wf_engine.SetItemAttrText(Itemtype => ItemType,
1003 Itemkey => ItemKey,
1004 aname => 'EPBPERFORMER',
1005 avalue => RoleName);
1006
1007 return 'A';
1008
1009 exception
1010
1011 when NO_DATA_FOUND then
1012 return 'NO_DATA';
1013
1014 when others then
1015 raise;
1016
1017 end;
1018
1019
1020 -- This finds all EPB users and sets them to a WF role for
1021 -- a notification to all users.
1022 Procedure Set_EPB_Users (rolename in Varchar2, RespKey in Varchar2)
1023 AS
1024
1025 UserList varchar2(2000);
1026 userhold varchar2(100);
1027 fndResp varchar2(30);
1028 cntr number :=1;
1029
1030 CURSOR c_zpbusers is
1031 select distinct user_name
1032 from wf_user_roles
1033 where ROLE_ORIG_SYSTEM = fndResp;
1034
1035 v_zpbusers c_zpbusers%ROWTYPE;
1036
1037 BEGIN
1038
1039 fndResp := ZPB_WF_NTF.GetFNDResp(RespKey);
1040
1041 -- by application
1042 for v_zpbusers in c_zpbusers loop
1043 if cntr = 1 then
1044 userlist := v_zpbusers.user_name;
1045 cntr := cntr+1;
1046 else
1047 userhold := v_zpbusers.user_name;
1048 userlist := userlist ||',' || userhold;
1049 cntr := cntr+1;
1050 if cntr = 20 then
1051 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, UserList);
1052 UserList := NULL;
1053 cntr := 1;
1054 end if;
1055 end if;
1056 end loop;
1057
1058 if UserList is not NULL then
1059 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, UserList);
1060 end if;
1061
1062 return;
1063
1064 exception
1065 when others then
1066 raise;
1067
1068 END Set_EPB_Users;
1069
1070 /*=========================================================================+
1071 | FUNCTION update_Role_with_Shadows
1072 |
1073 | DESCRIPTION
1074 | Updates the role with shadow users(if any) of the present user.
1075 | and returns the same.
1076 |
1077 +=========================================================================*/
1078 function update_Role_with_Shadows (roleName varchar2, thisUser in varchar2 ) return varchar2
1079 AS
1080 thisUserID number;
1081 BEGIN
1082 thisUserID := FNDUser_to_ID(thisUser);
1083 if has_Shadow(thisUserID) = 'Y' then
1084 zpb_wf_ntf.add_Shadow(roleName, thisUserID);
1085 end if;
1086
1087 -- abudnik 01JAN20 bUG 4641877
1088 if zpb_wf_ntf.user_in_role(rolename, thisUser) = 'N' then
1089 ZPB_UTIL_PVT.AddUsersToAdHocRole(roleName, thisUser);
1090 end if;
1091
1092
1093 return roleName;
1094
1095 exception
1096
1097 when others then
1098 raise;
1099
1100 END;
1101
1102
1103 function ID_to_FNDUser (userID in number) return varchar2
1104 AS
1105
1106 fndUser varchar2(150);
1107 respID number;
1108 appID number;
1109
1110 BEGIN
1111
1112
1113 select user_name into fndUser
1114 from fnd_user
1115 where user_id = userID;
1116
1117 return fndUser;
1118
1119 exception
1120 when NO_DATA_FOUND then
1121 return 'NOT_FOUND';
1122
1123 when others then
1124 raise;
1125
1126 END;
1127
1128 function FNDUser_to_ID (fndUser in varchar2) return number
1129 AS
1130
1131 userID number;
1132 respID number;
1133 appID number;
1134
1135 BEGIN
1136
1137 select user_id into userID
1138 from fnd_user
1139 where user_name = fndUser;
1140
1141 return userID;
1142
1143 exception
1144 when NO_DATA_FOUND then
1145 return 'NOT_FOUND';
1146
1147 when others then
1148 raise;
1149
1150 END;
1151
1152 procedure ADD_SHADOW (rolename in varchar2, UserId in Number)
1153
1154 AS
1155
1156 thisID number;
1157 thisRecipient varchar2(100);
1158
1159 CURSOR c_shadow is
1160 select shadow_id
1161 from zpb_shadow_users
1162 where user_id = UserId and privilege_lookup in ('FULLACCESS', 'NOTIFICATIONSONLY');
1163
1164 v_shadow c_shadow%ROWTYPE;
1165
1166 BEGIN
1167
1168
1169 for v_shadow in c_shadow loop
1170 thisID := v_shadow.shadow_id;
1171 thisRecipient := ID_to_FNDUser(thisID);
1172 if zpb_wf_ntf.user_in_role(rolename, thisRecipient) = 'N' then
1173 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisRecipient);
1174 end if;
1175 end loop;
1176
1177 return;
1178
1179 exception
1180
1181 when NO_DATA_FOUND then
1182 Null;
1183
1184 when others then
1185 raise;
1186
1187 end ADD_SHADOW;
1188
1189 function USER_IN_ROLE (rolename in varchar2, UserName in varchar2) return varchar2
1190
1191 AS
1192
1193 thisUser varchar2(100) := NULL;
1194
1195 CURSOR c_wfrole is
1196 select user_name
1197 from wf_user_roles
1198 where role_name = rolename and user_name = UserName;
1199
1200 v_wfrole c_wfrole%ROWTYPE;
1201
1202 BEGIN
1203
1204 for v_wfrole in c_wfrole loop
1205 thisUser := v_wfrole.USER_NAME;
1206 end loop;
1207
1208 if thisUser is NULL then
1209 return 'N';
1210 else
1211 return 'EXISTS';
1212 end if;
1213
1214
1215 exception
1216
1217 when NO_DATA_FOUND then
1218 Null;
1219
1220 when others then
1221 raise;
1222
1223 END;
1224
1225 Function HAS_SHADOW (userId in Number) return varchar2
1226
1227 AS
1228
1229 thisID number;
1230 thisRecipient varchar2(100);
1231 l_status varchar2(4) := 'N';
1232
1233 CURSOR c_shadow is
1234 select shadow_id
1235 from zpb_shadow_users
1236 where user_id = UserId and privilege_lookup in ('FULLACCESS', 'NOTIFICATIONSONLY');
1237
1238 v_shadow c_shadow%ROWTYPE;
1239
1240 BEGIN
1241
1242 for v_shadow in c_shadow loop
1243 thisID := v_shadow.shadow_id;
1244 l_status := 'Y';
1245 end loop;
1246
1247 return l_status;
1248
1249 exception
1250
1251 when NO_DATA_FOUND then
1252 Null;
1253
1254 when others then
1255 raise;
1256
1257 END;
1258
1259
1260 FUNCTION OLD_STYLE_USERS(instanceID in number, taskID in number, thisOwner in varchar2, thisOwnerID in number, relative in number DEFAULT 0, UserList in varchar2 DEFAULT NULL) return varchar2
1261
1262 IS
1263
1264 l_type varchar2(12);
1265 l_label varchar2(50);
1266 newDispName varchar2(360);
1267 rolename varchar2(320);
1268 curUser varchar2(150);
1269 curUserId number;
1270 l_UserList varchar2(2000);
1271
1272
1273 BEGIN
1274
1275 rolename := '#NOROLE#';
1276
1277 -- Owner of Buisness Process
1278 if UserList = 'OWNER_OF_AC' then
1279 curUser := thisOwner;
1280 curUserID := thisOwnerID;
1281 l_type := 'SET_THE_ROLE';
1282
1283 elsif UserList = 'ZPB_CONTROLLER_RESP' then
1284
1285 -- transform to FND resp.
1286 roleName := zpb_wf_ntf.GetFNDResp('ZPB_CONTROLLER_RESP');
1287 l_type := 'ALREADY_SET';
1288
1289 elsif UserList = 'ZPB_ALL_USERS' then
1290 -- transform to all ZPB users. so no need to add shadows.
1291 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, TaskID);
1292 zpb_wf_ntf.SetRole(rolename, relative+7);
1293 l_UserList := zpb_wf_ntf.Get_EPB_Users('ZPB');
1294 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, l_UserList);
1295 l_type := 'ALREADY_SET';
1296
1297 elsif instr(UserList, ',') > 0 then
1298 -- FND user names list so build Ad Hoc role
1299 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, TaskID);
1300 zpb_wf_ntf.SetRole(rolename, relative+7);
1301 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, UserList);
1302 l_type := 'ALREADY_SET';
1303
1304 -- a single user selected
1305 elsif instr(UserList, ',') = 0 then
1306 curUser := UserList;
1307 curUserID := zpb_wf_ntf.FNDUser_to_ID(UserList);
1308 l_type := 'SET_THE_ROLE';
1309
1310 end if;
1311
1312 if l_type = 'SET_THE_ROLE' then
1313 -- make the Ad Hoc role to hold both the dataowner and shadow
1314 if zpb_wf_ntf.has_Shadow(curUserID) = 'Y' then
1315 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, TaskID);
1316 select distinct display_name
1317 into NewDispName
1318 from wf_users
1319 where name = curUser;
1320
1321 -- add (And Shadows) display to role dispaly name
1322 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_ANDSHADOWS');
1323 l_label := FND_MESSAGE.GET;
1324 NewDispName := NewDispName || l_label;
1325
1326 zpb_wf_ntf.SetRole(rolename, relative, NewDispName);
1327 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, curUser);
1328 zpb_wf_ntf.add_Shadow(rolename, curUserID);
1329 else
1330 rolename := curUser;
1331 end if;
1332 end if;
1333
1334
1335 return rolename;
1336
1337 exception
1338
1339 when NO_DATA_FOUND then
1340 Null;
1341
1342 when others then
1343 raise;
1344
1345 END;
1346
1347
1348 procedure SENDMSG(p_userid in number,
1349 p_subject in varchar2,
1350 p_message in varchar2)
1351 /*
1352
1353 This procedure assumes application context is set!
1354
1355 p_userID is any user ID which corresponds to a valid wf_roles.name
1356 which may be a vaild fnd_user.user_name.
1357 p_subject - the notification subject up to 150 chars
1358 p_message - the notification message up to 2000 chars
1359
1360 May be called from any procedure if context is set.
1361
1362 */
1363
1364 IS
1365
1366 l_nid number;
1367 l_username varchar2(150);
1368 l_subject varchar2(150);
1369 l_message varchar2(2000);
1370 l_item_type varchar2(8);
1371 l_msg_name varchar2(16);
1372 l_send_comment varchar2(30); -- future use
1373
1374
1375 begin
1376
1377 l_item_type := 'EPBCYCLE';
1378 -- FYI - this is the same FYI message defined and used by the
1379 -- Notify Task.
1380 l_msg_name := 'FYIMSG';
1381
1382 l_username := ID_to_FNDUser(p_userid);
1383
1384 l_nid := wf_notification.send(ROLE => l_username,
1385 MSG_TYPE => l_item_type,
1386 MSG_NAME => l_msg_name,
1387 DUE_DATE => NULL,
1388 CALLBACK => NULL,
1389 CONTEXT => NULL,
1390 SEND_COMMENT => l_send_comment,
1391 PRIORITY => NULL);
1392
1393
1394 --DBMS_OUTPUT.PUT_LINE(l_nid);
1395 wf_notification.SETATTRTEXT(l_nid, 'SUBJECT', p_subject);
1396 wf_notification.SETATTRTEXT(l_nid, 'ISSUEMSG', p_message);
1397
1398
1399 return;
1400
1401 exception
1402 when others then
1403 raise;
1404
1405 end SENDMSG;
1406
1407 -- added for b 5251227
1408 /*=========================================================================+
1409 | FUNCTION update_Role_with_Shadows
1410 |
1411 | DESCRIPTION
1412 | To be used when the EPBPrerformer Attrubute is a single user and not
1413 | a list of users or if BP owner ID is passed in. When called this will
1414 | set any shadow users the EPBPerformer may have so notifications will be
1415 | sent to shadows also.
1416 |
1417 | Parameters: itemtype - usually EPBCYCLE will work for other ITEMTYPES that have
1418 | WF attributes TASKID, INSTANCEID OWNERID and EPBPERFORMER.
1419 | itemkey - for the currently running WF EPB process
1420 | actid - 0 if not called directly by WF
1421 | functmode - RUN when called directly by WF or
1422 | EPBPERFORMER or EPB_BPOWNERID if called from procedure
1423 | and not from WF directly.
1424 +=========================================================================*/
1425 procedure SHADOWS_FOR_EPBPERFORMER (itemtype in varchar2,
1426 itemkey in varchar2,
1427 actid in number,
1428 funcmode in varchar2,
1429 resultout out nocopy varchar2)
1430
1431 IS
1432
1433 l_thisUserID NUMBER;
1434 l_RoleName varchar2(320);
1435 l_thisRecipient varchar2(100);
1436 l_label varchar2(50);
1437 l_NewDispName varchar2(360);
1438 l_TASKID NUMBER;
1439 l_INSTANCEID NUMBER;
1440 l_retbool BOOLEAN;
1441
1442 BEGIN
1443
1444 IF (funcmode = 'RUN') or (instr(funcmode, 'EPB') = 1) THEN
1445
1446
1447 if (funcmode = 'EPB_BPOWNERID') then
1448
1449 -- explicit request to use owner ID as recipient
1450 l_thisUserID := WF_ENGINE.GetItemAttrNumber(
1451 itemtype => ItemType,
1452 itemkey => ItemKey,
1453 aname => 'OWNERID');
1454 l_thisRecipient := ID_to_FNDuser(l_thisUserID);
1455
1456 wf_engine.SetItemAttrText(Itemtype => ItemType,
1457 Itemkey => ItemKey,
1458 aname => 'EPBPERFORMER',
1459 avalue => l_thisRecipient);
1460
1461 else
1462 -- value is EPBPERFORMER or RUN
1463 -- in either case vlue set in WF attr EPBPERFORMER is basis for target recipient
1464 l_thisRecipient := wf_engine.GetItemAttrText(Itemtype => ItemType,
1465 Itemkey => ItemKey,
1466 aname => 'EPBPERFORMER');
1467 l_thisUserID := FNDUser_to_ID(l_thisRecipient);
1468
1469 end if;
1470
1471 -- just tells us this is a vaid single user not group role
1472 -- so look for and set shadows
1473 l_retbool := wf_directory.useractive(l_thisRecipient);
1474
1475 if l_retbool = TRUE then
1476
1477
1478
1479 --DBMS_OUTPUT.PUT_LINE('in true');
1480
1481 -- make the Ad Hoc role to hold both the dataowner and shadow
1482
1483 --DBMS_OUTPUT.PUT_LINE(l_thisRecipient);
1484 if zpb_wf_ntf.has_Shadow(l_thisUserID) = 'Y' then
1485
1486
1487 l_InstanceID := WF_ENGINE.GetItemAttrNumber(
1488 itemtype => ItemType,
1489 itemkey => ItemKey,
1490 aname => 'INSTANCEID');
1491
1492 l_TaskID := WF_ENGINE.GetItemAttrNumber(
1493 itemtype => ItemType,
1494 itemkey => ItemKey,
1495 aname => 'TASKID');
1496
1497 --DBMS_OUTPUT.PUT_LINE('has shadow');
1498 l_RoleName := zpb_wf_ntf.MakeRoleName(l_InstanceID, l_TaskID);
1499 select distinct display_name
1500 into l_NewDispName
1501 from wf_users
1502 where name = l_thisRecipient;
1503 -- add (And Shadows) display to role dispaly name
1504 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_ANDSHADOWS');
1505 l_label := FND_MESSAGE.GET;
1506
1507 l_NewDispName := l_NewDispName || l_label;
1508 zpb_wf_ntf.SetRole(l_RoleName, 7, l_NewDispName);
1509 -- b4948928
1510 if zpb_wf_ntf.user_in_role(l_rolename, l_thisRecipient) = 'N' then
1511 ZPB_UTIL_PVT.AddUsersToAdHocRole(l_RoleName, l_thisRecipient);
1512 end if;
1513 zpb_wf_ntf.add_Shadow(l_RoleName, l_thisUserID);
1514
1515 wf_engine.SetItemAttrText(Itemtype => ItemType,
1516 Itemkey => ItemKey,
1517 aname => 'EPBPERFORMER',
1518 avalue => l_RoleName);
1519
1520 --DBMS_OUTPUT.PUT_LINE('performer set');
1521
1522 end if;
1523
1524 end if; -- TRUE
1525
1526 END IF;
1527
1528 resultout :='COMPLETE';
1529 return;
1530
1531
1532 exception
1533 when others then
1534
1535 if (funcmode = 'EPBPERFORMER') or (funcmode = 'EPB_BPOWNERID') then
1536 raise;
1537 else
1538
1539 Wf_Core.Context('ZPB_WF_NTF', 'SHADOWS_FOR_EPBPERFORMER', itemtype,
1540 itemkey, to_char(actid), funcmode);
1541 raise;
1542 end if;
1543
1544 END SHADOWS_FOR_EPBPERFORMER;
1545
1546 procedure SendExpiredUserMsg (p_BPOwnerID in number,
1547 p_taskID in number,
1548 p_itemtype in varchar2)
1549
1550 is
1551
1552
1553 l_nid number;
1554 l_BPownerOrProxy varchar2(150);
1555 l_item_type varchar2(8);
1556 l_msg_name varchar2(16);
1557 l_BPOwnerID number;
1558 l_BP_runID number;
1559 l_BP_runName varchar2(100);
1560 l_TaskID number;
1561 l_taskName varchar2(60);
1562 l_BAID number;
1563 l_BA_Name varchar2(100);
1564 l_count number;
1565
1566
1567 begin
1568
1569 --DBMS_OUTPUT.PUT_LINE('begin sendmsgexpired');
1570
1571 SELECT count(user_name) into l_count
1572 FROM ZPB_WF_INACTIVE_USERS_GT;
1573
1574 --DBMS_OUTPUT.PUT_LINE('COUNT from GT: ' || l_count);
1575
1576 if (l_count > 0) then
1577
1578
1579 l_BPOwnerID := p_BPOwnerID;
1580 l_taskID := p_taskID;
1581
1582 select t.task_name, ac.business_area_id, v.name, i.INSTANCE_DESCRIPTION, t.ANALYSIS_CYCLE_ID
1583 into l_taskName, l_BAID, l_BA_name, l_BP_runName, l_BP_runID
1584 from zpb_analysis_cycles ac, zpb_analysis_cycle_tasks t,
1585 ZPB_BUSINESS_AREAS_VL v, zpb_analysis_cycle_instances i
1586 where t.task_id = l_taskID
1587 and t.ANALYSIS_CYCLE_ID = ac.ANALYSIS_CYCLE_ID
1588 and ac.ANALYSIS_CYCLE_ID = i.INSTANCE_AC_ID
1589 and ac.business_area_id = v.business_area_id;
1590
1591 l_BPownerOrProxy := zpb_wf_ntf.Get_Active_User(l_BPOwnerID, l_BAID);
1592
1593 --DBMS_OUTPUT.PUT_LINE('after getactive owner proxy' || l_bpownerorproxy);
1594 --DBMS_OUTPUT.PUT_LINE('l_taskname ' || l_taskname);
1595
1596
1597 -- need fndmessage
1598 if p_ItemType is NULL then
1599 l_item_type := 'EPBCYCLE';
1600 else
1601 l_item_type := p_itemtype;
1602 end if;
1603 l_msg_name := 'INACTIVEMSG';
1604
1605
1606 l_nid := wf_notification.send(ROLE => l_BPownerOrProxy,
1607 MSG_TYPE => l_item_type,
1608 MSG_NAME => l_msg_name,
1609 DUE_DATE => NULL,
1610 CALLBACK => NULL,
1611 CONTEXT => NULL,
1612 SEND_COMMENT => NULL,
1613 PRIORITY => NULL);
1614
1615 -- DBMS_OUTPUT.PUT_LINE('nid ' || l_nid);
1616
1617
1618 wf_notification.SETATTRTEXT(l_nid, '#HDR_TASKNAME', l_taskName);
1619 wf_notification.SETATTRTEXT(l_nid, '#HDR_BUS_AREA_NAME', l_BA_name);
1620 wf_notification.SETATTRTEXT(l_nid, '#HDR_INSTANCEDESC', l_BP_runName);
1621 wf_notification.SETATTRTEXT(l_nid, '#HDR_BUS_AREA_ID', l_BAID);
1622 wf_notification.SETATTRTEXT(l_nid, '#HDR_TASKID', l_taskID);
1623
1624
1625
1626 --DBMS_OUTPUT.PUT_LINE('before build expired');
1627 -- Build user list for message body
1628 zpb_wf_ntf.Build_ExpiredUser_list (l_nid);
1629
1630 end if; -- if > 0
1631
1632
1633 return;
1634
1635 exception
1636 when others then
1637 raise;
1638
1639 end SendExpiredUserMsg;
1640
1641 function Get_Active_User (p_BPOwnerID in number, p_BAID in number) return varchar2
1642
1643 as
1644
1645 l_RoleName varchar2(320);
1646 l_thisRecipient varchar2(150);
1647 l_label varchar2(50);
1648 l_NewDispName varchar2(360);
1649 l_TASKID NUMBER;
1650 l_INSTANCEID NUMBER;
1651 l_respID number;
1652 L_RESPDISPLAY varchar2(100);
1653
1654 begin
1655
1656 l_thisRecipient := zpb_wf_ntf.ID_to_FNDUser(p_BPOwnerID);
1657
1658 -- Make a role name may not use it if just one user.
1659 if zpb_wf_ntf.has_Shadow(p_BPOwnerID) = 'Y' then
1660
1661 l_RoleName := zpb_wf_ntf.MakeRoleName(l_InstanceID, l_TaskID);
1662
1663 select distinct display_name
1664 into l_NewDispName
1665 from wf_users
1666 where name = l_thisRecipient;
1667 -- add (And Shadows) display to role dispaly name
1668 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_ANDSHADOWS');
1669 l_label := FND_MESSAGE.GET;
1670 l_NewDispName := l_NewDispName || l_label;
1671
1672
1673 zpb_wf_ntf.SetRole(l_RoleName, 7, l_NewDispName);
1674
1675 if wf_directory.useractive(l_thisRecipient) = TRUE then
1676 if zpb_wf_ntf.user_in_role(l_rolename, l_thisRecipient) = 'N' then
1677 ZPB_UTIL_PVT.AddUsersToAdHocRole(l_RoleName, l_thisRecipient);
1678 end if;
1679 end if;
1680
1681 zpb_wf_ntf.add_Shadow(l_RoleName, p_BPOwnerID);
1682
1683 else
1684
1685 if wf_directory.useractive(l_thisRecipient) = TRUE then
1686 l_RoleName := l_thisRecipient;
1687 else
1688
1689 -- there are no shadows and the owner is expired so
1690 -- try to find secrity admins and send the notes to them
1691 l_RoleName := zpb_wf_ntf.MakeRoleName(l_InstanceID, l_TaskID);
1692 -- add Responibility name as role dispaly name
1693 -- first get RESPONSIBILITY_ID
1694 select RESPONSIBILITY_ID, RESPONSIBILITY_NAME
1695 into l_respID, l_respDisplay
1696 from fnd_responsibility_vl
1697 where application_id = 210 and
1698 RESPONSIBILITY_KEY = 'ZPB_MANAGER_RESP';
1699
1700 zpb_wf_ntf.SetRole(l_RoleName, 7, l_respDisplay);
1701
1702 l_RoleName := zpb_wf_ntf.FindSecurityAdmin (p_BAID, l_RoleName, l_respID);
1703 -- final check if no zpb users yet then go to sysadmin
1704 if l_RoleName = 'NO_ZPB_USERS' then
1705 l_RoleName := 'SYSADMIN';
1706 end if;
1707
1708 end if;
1709
1710 end if; -- shadow check
1711
1712 return l_RoleName;
1713
1714 exception
1715 when others then
1716 raise;
1717
1718 end Get_Active_User;
1719
1720 function FindSecurityAdmin (p_BAID in number,
1721 p_roleName in varchar2,
1722 p_respID in number) return varchar2
1723
1724 as
1725
1726 l_status varchar2(12);
1727 l_thisUserID number;
1728 l_thisRecipient varchar2(320);
1729
1730
1731 CURSOR c_SecAdmins is
1732 select USER_ID from zpb_account_states
1733 where RESP_ID = p_respID AND ACCOUNT_STATUS = 0 AND BUSINESS_AREA_ID = p_BAID;
1734
1735 v_SecAdmin c_SecAdmins%ROWTYPE;
1736
1737 begin
1738
1739 l_status := 'N0_ZPB_USERS';
1740
1741 for v_SecAdmin in c_SecAdmins loop
1742 l_thisUserID := v_SecAdmin.user_id;
1743 l_thisRecipient := ID_to_FNDuser(l_thisUserID);
1744
1745 if wf_directory.useractive(l_thisRecipient) = TRUE then
1746 if zpb_wf_ntf.user_in_role(p_RoleName, l_thisRecipient) = 'N' then
1747 ZPB_UTIL_PVT.AddUsersToAdHocRole(p_RoleName, l_thisRecipient);
1748 l_status := 'Y';
1749 end if;
1750 end if;
1751
1752 end loop;
1753
1754 return l_status;
1755
1756 end FindSecurityAdmin;
1757
1758
1759
1760 Procedure Build_ExpiredUser_list (p_nid in number)
1761 is
1762
1763 l_length number;
1764 l_register number;
1765 l_userlist varchar2(4000);
1766 l_fullLength number;
1767 l_bypass varchar2(1);
1768 l_session_id number;
1769
1770 CURSOR c_inactive_users is
1771 select distinct(user_name) from ZPB_WF_INACTIVE_USERS_GT;
1772
1773 v_inactive_user c_inactive_users%ROWTYPE;
1774
1775 begin
1776
1777 SELECT SUM(LENGTH(user_name)) into l_fulllength
1778 FROM ZPB_WF_INACTIVE_USERS_GT;
1779
1780 if l_fulllength > 0 then -- do nothing if length is 0 or negative
1781
1782 if l_fulllength < 3990 then
1783 -- just one register to needed
1784 for v_inactive_user in c_inactive_users loop
1785
1786 if c_inactive_users%ROWCOUNT = 1 then
1787 l_userlist := v_inactive_user.user_name;
1788 else
1789 l_userlist := l_userlist || ', ' || v_inactive_user.user_name;
1790 end if;
1791
1792 end loop;
1793
1794 wf_notification.SETATTRTEXT(p_nid, 'REGISTER1', l_userlist);
1795
1796
1797 -- loop over cursor
1798
1799 else
1800 l_register := 0;
1801
1802 open c_inactive_users;
1803 loop
1804 fetch c_inactive_users into v_inactive_user;
1805
1806 if c_inactive_users%ROWCOUNT = 1 then
1807 l_userlist := v_inactive_user.user_name;
1808 l_bypass := 'Y';
1809 else -- manyrows
1810
1811 l_userlist := l_userlist || ', ' || v_inactive_user.user_name;
1812 l_length := length(l_userlist);
1813
1814 if l_length > 3660 then
1815 l_register := l_register+1;
1816 l_bypass := 'N';
1817 l_length := 0;
1818 else
1819 l_bypass := 'Y';
1820 end if;
1821
1822 if c_inactive_users%NOTFOUND then
1823 l_bypass := 'LAST';
1824 end if;
1825
1826
1827
1828 if l_bypass = 'N' or l_bypass = 'LAST' then --BYPASS
1829
1830 case l_register
1831
1832 when 1 then
1833 wf_notification.SETATTRTEXT(p_nid, 'REGISTER1', l_userlist);
1834 l_userlist := NULL;
1835 when 2 then
1836 wf_notification.SETATTRTEXT(p_nid, 'REGISTER2', l_userlist);
1837 l_userlist := NULL;
1838
1839 when 3 then
1840 wf_notification.SETATTRTEXT(p_nid, 'REGISTER3', l_userlist);
1841 l_userlist := NULL;
1842
1843 when 4 then
1844 wf_notification.SETATTRTEXT(p_nid, 'REGISTER4', l_userlist);
1845 l_userlist := NULL;
1846
1847 when 5 then
1848 wf_notification.SETATTRTEXT(p_nid, 'REGISTER5', l_userlist);
1849 l_userlist := NULL;
1850
1851 when 6 then
1852 wf_notification.SETATTRTEXT(p_nid, 'REGISTER6', l_userlist);
1853 l_userlist := NULL;
1854
1855 end case;
1856
1857 end if; -- manyrows
1858
1859 end if; --BYPASS
1860
1861
1862 if c_inactive_users%NOTFOUND and l_bypass = 'LAST' then
1863 EXIT;
1864 end if;
1865
1866
1867 end loop;
1868 close c_inactive_users;
1869
1870
1871 end if; -- fullLength test
1872
1873 end if; -- less than 0
1874
1875 return;
1876
1877 exception
1878 when others then
1879 raise;
1880
1881 end Build_ExpiredUser_list;
1882
1883
1884
1885
1886 end ZPB_WF_NTF;