[Home] [Help]
PACKAGE BODY: APPS.ZPB_EXCEPTION_ALERT
Source
1 PACKAGE BODY ZPB_EXCEPTION_ALERT AS
2 /* $Header: zpbwfexc.plb 120.4 2007/12/04 16:23:03 mbhat ship $ */
3
4 Owner varchar2(30);
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'zpb_exception_alert';
6
7 -- Directly called by WF function Activity
8 procedure EVALUATE_RESULTS (itemtype in varchar2,
9 itemkey in varchar2,
10 actid in number,
11 funcmode in varchar2,
12 resultout out nocopy varchar2)
13 IS
14
15 TaskID number;
16 ExplainReq varchar2(3);
17 countEx number;
18
19 BEGIN
20
21 IF (funcmode = 'RUN') then
22
23 resultout :='COMPLETE:NOEXCEPT';
24
25 TaskID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
26 Itemkey => ItemKey,
27 aname => 'TASKID');
28
29 select count(*) INTO countEx
30 from ZPB_EXCP_RESULTS
31 where TASK_ID = TaskID;
32
33 if countEx > 0 then
34 select value into ExplainReq
35 from ZPB_TASK_PARAMETERS
36 where TASK_ID = TaskID and NAME = 'EXPLANATION_REQUIRED_FLAG';
37
38 -- type of exception
39 if ExplainReq = 'N' then
40 resultout :='COMPLETE:FYI';
41 else
42 resultout :='COMPLETE:ACTIONREQ_DUE';
43 end if;
44 else
45 -- if not results no exception
46 resultout :='COMPLETE:NOEXCEPT';
47 end if;
48
49 END IF;
50 return;
51
52 exception
53
54 when others then
55 WF_CORE.CONTEXT('ZPB_WF.EVALUATE_RESULTS', itemtype, itemkey, to_char(actid), funcmode);
56 raise;
57
58 end EVALUATE_RESULTS;
59
60 procedure SET_ATTRIBUTES (itemtype in varchar2,
61 newitemkey in varchar2,
62 taskID in number)
63
64 IS
65
66 value Varchar2(4000);
67 ExpReq Varchar2(4000);
68 DType Varchar2(4000);
69 Deadline varchar2(4000);
70 DeadDate date;
71 TASKPARAMNAME varchar2(100);
72 errmsg varchar2(255);
73 relative number;
74 l_authorIDT varchar2(4000);
75 authorID number;
76 l_label varchar2(4000);
77
78 CURSOR c_tskparams is
79 select NAME, VALUE
80 from ZPB_TASK_PARAMETERS
81 where TASK_ID = TaskID and NAME <> 'SPECIFIED_TARGET_NAME';
82
83 v_tskparams c_tskparams%ROWTYPE;
84
85
86 BEGIN
87
88 for v_tskparams in c_tskparams loop
89
90 taskParamName := v_tskparams.name;
91
92 if taskParamName = 'NOTIFY_SUBJECT' then
93 value := v_tskparams.value;
94 wf_engine.SetItemAttrText(Itemtype => ItemType,
95 Itemkey => NEWItemKey,
96 aname => 'SUBJECT',
97 avalue => value);
98 elsif taskParamName = 'NOTIFY_CONTENT' then
99 value := v_tskparams.value;
100 wf_engine.SetItemAttrText(Itemtype => ItemType,
101 Itemkey => NEWItemKey,
102 aname => 'ISSUEMSG',
103 avalue => value);
104 elsif taskParamName = 'EXCEPTION_DIMENSION_NAME' then
105 value := v_tskparams.value;
106 wf_engine.SetItemAttrText(Itemtype => ItemType,
107 Itemkey => NEWItemKey,
108 aname => 'EXCEPDIM',
109 avalue => value);
110 elsif taskParamName = 'SAVED_SELECTION_NAME' then
111 value := v_tskparams.value;
112
113 if value is not NULL then
114 -- Selection label
115 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_SELECT_LABEL');
116 l_label := FND_MESSAGE.GET;
117 value := l_label || value;
118
119 wf_engine.SetItemAttrText(Itemtype => ItemType,
120 Itemkey => NEWItemKey,
121 aname => 'EXCEPSELECTION',
122 avalue => value);
123 end if;
124
125
126 elsif taskParamName = 'EXPLANATION_REQUIRED_FLAG' then
127 ExpReq := v_tskparams.value;
128 elsif taskParamName = 'WAIT_TYPE' then
129 DType := v_tskparams.value;
130 elsif taskParamName = 'WAIT_VALUE' then
131 Deadline := v_tskparams.value;
132
133 else
134 errMsg := v_tskparams.value;
135 end if;
136
137 end loop;
138
139 if ExpReq = 'Y' then
140
141 if Dtype = 'DAY' then
142 Relative := to_number(Deadline)* 1440;
143 DeadDate := sysdate + to_number(Deadline);
144 elsif Dtype = 'WEEK' then
145 Relative := to_number(Deadline)* 7 * 1440;
146 DeadDate := sysdate + (to_number(Deadline) * 7);
147 elsif Dtype = 'MONTH' then
148 DeadDate := add_months(sysdate, to_number(Deadline));
149 Relative := (DeadDate - sysdate) * 1440;
150 end if;
151
152 -- DEADLINE for child explanation processes
153 -- relative subtract 1 minute from child timeout to get them to timeout first.
154 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
155 Itemkey => NEWItemKey,
156 aname => 'EXPDEADLINE',
157 avalue => Relative-1);
158
159 end if;
160
161 -- bug 3482485
162 select value into l_authorIDT
163 from zpb_task_parameters
164 where task_id = TaskID and name = 'OWNER_ID';
165
166 authorID := to_number(l_authorIDT);
167
168 wf_engine.SetItemAttrText(Itemtype => ItemType,
169 Itemkey => NEWItemKey,
170 aname => '#FROM_ROLE',
171 avalue => ZPB_WF_NTF.ID_to_FNDUser(authorID));
172
173 return;
174
175 exception
176
177 when others then
178 raise;
179
180 end SET_ATTRIBUTES;
181
182 procedure SET_SPECIFIED_USERS (ExcType in varchar2,
183 AuthorID in number,
184 itemtype in varchar2,
185 newitemkey in varchar2,
186 taskID in number,
187 InstanceID in number)
188
189 IS
190
191 rolename varchar2(100);
192 relative number := 7;
193 thisRecipient varchar2(100);
194 thisUserID number;
195 errmsg varchar2(100);
196
197 CURSOR c_recipient is
198 select NAME, value
199 from ZPB_TASK_PARAMETERS
200 where TASK_ID = TaskID and name = 'SPECIFIED_NOTIFICATION_RECIPIENT';
201
202 v_recipient c_recipient%ROWTYPE;
203
204 BEGIN
205
206 for v_recipient in c_recipient loop
207
208 thisRecipient := v_recipient.value;
209
210 if c_recipient%ROWCOUNT = 1 then
211 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, TaskID);
212 zpb_wf_ntf.SetRole(rolename, relative);
213 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisRecipient);
214 else
215 if zpb_wf_ntf.user_in_role(rolename, thisRecipient) = 'N' then
216 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisRecipient);
217 end if;
218 end if;
219
220 -- add in shadow if there is one
221 thisUserID := zpb_wf_ntf.fnduser_to_ID(thisRecipient);
222 zpb_wf_ntf.add_Shadow(rolename, thisUserId);
223
224 end loop;
225
226 -- add in author as recipient if EXCEPTION_TYPE => A
227 if ExcType = 'A' then
228 thisRecipient := zpb_wf_ntf.ID_to_FNDUser(AuthorID);
229 if zpb_wf_ntf.user_in_role(rolename, thisRecipient) = 'N' then
230 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisRecipient);
231 zpb_wf_ntf.add_Shadow(rolename, AuthorID);
232 end if;
233 end if;
234
235 wf_engine.SetItemAttrText(Itemtype => ItemType,
236 Itemkey => NEWItemKey,
237 aname => 'EPBPERFORMER',
238 avalue => RoleName);
239
240 return;
241
242 exception
243 when others then
244 raise;
245
246 end SET_SPECIFIED_USERS;
247
248 procedure FYI_NOTIFICATIONS (ntfTarget in varchar2,
249 itemtype in varchar2,
250 itemkey in varchar2,
251 taskID in number)
252 IS
253
254 ACNAME varchar2(300);
255 ACID number;
256 owner varchar2(30);
257 ownerID number;
258 AuthorID number;
259 thisUser varchar2(100);
260 relative number := 7;
261 ExcType varchar2(100);
262 rolename varchar2(100);
263 InstanceID number;
264 InstanceDesc varchar2(300);
265 TaskName varchar2(256);
266 charDate varchar2(30);
267 NEWItemKey varchar2(240);
268 workflowprocess varchar2(30) := 'NOTIFYEXCEPT';
269 value varchar2(4000);
270 errMsg varchar2(2000);
271 l_authorIDT varchar2(4000);
272
273 BEGIN
274 -- GET current task information.
275 ACID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
276 Itemkey => ItemKey,
277 aname => 'ACID');
278 ACNAME := wf_engine.GetItemAttrText(Itemtype => ItemType,
279 Itemkey => ItemKey,
280 aname => 'ACNAME');
281 ownerID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
282 Itemkey => ItemKey,
283 aname => 'OWNERID');
284 owner := zpb_wf_ntf.ID_to_FNDUser(ownerID);
285
286 InstanceID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
287 Itemkey => ItemKey,
288 aname => 'INSTANCEID');
289
290 -- set up defaults for author and type
291 select TASK_NAME into TaskName
292 from zpb_analysis_cycle_tasks
293 where ANALYSIS_CYCLE_ID = InstanceID and task_id = taskid;
294
295
296 -- bug 3482485
297 select value into l_authorIDT
298 from zpb_task_parameters
299 where task_id = TaskID and name = 'OWNER_ID';
300
301 AuthorID := to_number(l_authorIDT);
302
303
304 select VALUE
305 into ExcType
306 from ZPB_TASK_PARAMETERS
307 where TASK_ID = TaskID and name = 'EXCEPTION_TYPE';
308
309 -- create NEWItemKey for FYI workflow
310 charDate := to_char(sysdate, 'MM/DD/YYYY-HH24:MI:SS');
311 NEWItemKey := rtrim(substr(ACName, 1, 50), ' ') || '-' || to_char(ACID) || '-0-' || workflowprocess || '-' || charDate ;
312
313 -- SET UP FYI PROCESS for this NEWItemKey!
314 -- Create WF start process instance
315 wf_engine.CreateProcess(ItemType => ItemType,
316 itemKey => NEWItemKey,
317 process => WorkflowProcess);
318
319 -- This should be the EPB controller.
320 wf_engine.SetItemOwner(ItemType => ItemType,
321 ItemKey => NEWItemKey,
322 owner => owner);
323
324 -- NOTE: for this NEWitemkey!
325 wf_engine.SetItemAttrText(itemtype => itemtype,
326 itemkey => NEWItemKey,
327 aname => 'EXCEPLIST',
328 avalue =>
329 'PLSQL:ZPB_EXCEPTION_ALERT.EXCEPTION_LIST/'||
330 TO_CHAR(taskID)||':'||NEWItemKey);
331
332 if ntfTarget = 'SPECIFIED' then
333 -- this will populate wf attrs and WF Ad Hoc role with users.
334 ZPB_EXCEPTION_ALERT.SET_SPECIFIED_USERS(ExcType, AuthorID, itemtype, NEWItemkey, taskID, InstanceID);
335
336 elsif ntfTarget = 'OWNER_OF_AC' then
337
338 -- BUSINESS PROCESS OWNER => OWNER_OF_AC
339 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, TaskID);
340 zpb_wf_ntf.SetRole(rolename, relative);
341 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, owner);
342 zpb_wf_ntf.add_Shadow(rolename, ownerID);
343
344 -- add in author as recipient if EXCEPTION_TYPE => A
345 if ExcType = 'A' then
346 thisUser := zpb_wf_ntf.ID_to_FNDUser(AuthorID);
347 if zpb_wf_ntf.user_in_role(rolename, thisUser) = 'N' then
348 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisUser);
349 zpb_wf_ntf.add_Shadow(rolename, AuthorID);
350 end if;
351 end if;
352
353 wf_engine.SetItemAttrText(Itemtype => ItemType,
354 Itemkey => NEWItemKey,
355 aname => 'EPBPERFORMER',
356 avalue => RoleName);
357
358 elsif ntfTarget = 'JUST_AUTHOR' then
359 -- Just the Author
360 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, TaskID);
361 zpb_wf_ntf.SetRole(rolename, relative);
362 thisUser := zpb_wf_ntf.ID_to_FNDUser(AuthorID);
363 if zpb_wf_ntf.user_in_role(rolename, thisUser) = 'N' then
364 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisUser);
365 -- zpb_wf_ntf.add_Shadow(rolename, AuthorID);
366 end if;
367 wf_engine.SetItemAttrText(Itemtype => ItemType,
368 Itemkey => NEWItemKey,
369 aname => 'EPBPERFORMER',
370 avalue => RoleName);
371 end if;
372
373 -- reads parameters and sets attributes for this process
374 ZPB_EXCEPTION_ALERT.SET_ATTRIBUTES(itemtype, NEWItemkey, taskID);
375
376 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
377 Itemkey => NEWItemKey,
378 aname => 'ACID',
379 avalue => ACID);
380
381 -- set workflow with Instance Cycle ID!
382 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
383 Itemkey => NEWItemKey,
384 aname => 'INSTANCEID',
385 avalue => InstanceID);
386
387 -- set cycle Name!
388 wf_engine.SetItemAttrText(Itemtype => ItemType,
389 Itemkey => NEWItemKey,
390 aname => 'ACNAME',
391 avalue => ACNAME);
392 -- set Task ID!
393 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
394 Itemkey => NEWItemKey,
395 aname => 'TASKID',
396 avalue => TaskID);
397
398 -- set Task Name!
399 select TASK_NAME
400 into TaskName
401 from zpb_analysis_cycle_tasks
402 where ANALYSIS_CYCLE_ID = InstanceID and task_id = taskid;
403
404 wf_engine.SetItemAttrText(Itemtype => ItemType,
405 Itemkey => NEWItemKey,
406 aname => 'TASKNAME',
407 avalue => TaskName);
408
409 select INSTANCE_DESCRIPTION
410 into InstanceDesc
411 from ZPB_ANALYSIS_CYCLE_INSTANCES
412 where INSTANCE_AC_ID = InstanceID;
413
414 -- set descripton
415 wf_engine.SetItemAttrText(Itemtype => ItemType,
416 Itemkey => NEWItemKey,
417 aname => 'INSTANCEDESC',
418 avalue => InstanceDesc);
419
420
421
422 -- START IT!
423 -- Now that all is created and set START the PROCESS!
424 wf_engine.StartProcess(ItemType => ItemType,
425 ItemKey => NEWItemKey);
426
427 return;
428
429 exception
430 when others then
431 -- WF_CORE.CONTEXT('ZPB_EXCEPTION_ALERT.FYI_SPECIFIED', itemtype, itemkey, to_char(actid), funcmode);
432 raise;
433
434 end FYI_NOTIFICATIONS;
435
436
437 procedure SEND_NOTIFICATIONS (itemtype in varchar2,
438 itemkey in varchar2,
439 actid in number,
440 funcmode in varchar2,
441 resultout out nocopy varchar2)
445 ACNAME varchar2(300);
442 IS
443
444 InstItemType varchar2(8) := 'EPBCYCLE';
446 TaskID number;
447 owner varchar2(30);
448 newitemkey varchar2(240);
449 workflowprocess varchar2(30);
450 InstanceID number;
451 NtfType varchar2(4000);
452 ExcType varchar2(4000);
453 ntfTarget varchar2(4000);
454 DType Varchar2(6) := 'NONE';
455 DeadDate date;
456 Deadline varchar2(30);
457 TASKPARAMNAME varchar2(100);
458 errmsg varchar2(100);
459 relative number;
460 authorID number;
461 l_authorIDT varchar2(4000);
462 thisOwnerID number; -- b 4948928
463
464 CURSOR c_deadline is
465 select NAME, VALUE
466 from ZPB_TASK_PARAMETERS
467 where TASK_ID = TaskID and ( NAME = 'WAIT_TYPE' or NAME = 'WAIT_VALUE' );
468
469 v_deadline c_deadline%ROWTYPE;
470
471
472 BEGIN
473
474 IF (funcmode = 'RUN') then
475
476 resultout := 'ERROR';
477
478 TaskID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
479 Itemkey => ItemKey,
480 aname => 'TASKID');
481
482 -- bug 3482485
483 select value into l_authorIDT
484 from zpb_task_parameters
485 where task_id = TaskID and name = 'OWNER_ID';
486
487 authorID := to_number(l_authorIDT);
488
489 wf_engine.SetItemAttrText(Itemtype => ItemType,
490 Itemkey => ItemKey,
491 aname => '#FROM_ROLE',
492 avalue => ZPB_WF_NTF.ID_to_FNDUser(authorID));
493
494 select value into ntfType
495 from ZPB_TASK_PARAMETERS
496 where TASK_ID = TaskID and NAME = 'EXPLANATION_REQUIRED_FLAG';
497
498 select value into ntfTarget
499 from ZPB_TASK_PARAMETERS
500 where TASK_ID = TaskID and NAME = 'NOTIFICATION_RECIPIENT_TYPE';
501
502 select value into ExcType
503 from ZPB_TASK_PARAMETERS
504 where TASK_ID = TaskID and NAME = 'EXCEPTION_TYPE';
505
506 if NtfType = 'N' then
507
508 if ntfTarget = 'DATA_OWNERS' then
509 -- this will populate wf attrs and start the NTF Workflow to send FYI for data owners.
510 ZPB_EXCEPTION_ALERT.FYI_BY_OWNER(itemtype, itemkey, taskID);
511 --after all dataowner get their ntfs send one to author if type = analyst.
512
513 if ExcType = 'A' then
514 -- overloaded ntfTarget type to included JUST_AUTHOR
515 ZPB_EXCEPTION_ALERT.FYI_NOTIFICATIONS('JUST_AUTHOR', itemtype, itemkey, taskID);
516 end if;
517 resultout := 'COMPLETE';
518 else
519 -- this will populate wf attrs and start the NTF Workflow which send the NTF.
520 ZPB_EXCEPTION_ALERT.FYI_NOTIFICATIONS(ntfTarget, itemtype, itemkey, taskID);
521 resultout := 'COMPLETE';
522 end if;
523
524 elsif NtfType = 'Y' then
525
526 -- set timeout/deadline for Explanation parent
527 for v_deadline in c_deadline loop
528 taskParamName := v_deadline.name;
529 if taskParamName = 'WAIT_TYPE' then
530 DType := v_deadline.value;
531 elsif taskParamName = 'WAIT_VALUE' then
532 Deadline := v_deadline.value;
533 else
534 errMsg := v_deadline.value;
535 end if;
536 end loop;
537
538 if Dtype = 'DAY' then
539 Relative := to_number(Deadline)* 1440;
540 DeadDate := sysdate + to_number(Deadline);
541 elsif Dtype = 'WEEK' then
542 Relative := to_number(Deadline)* 7 * 1440;
543 DeadDate := sysdate + (to_number(Deadline) * 7);
544 elsif Dtype = 'MONTH' then
545 DeadDate := add_months(sysdate, to_number(Deadline));
546 Relative := (DeadDate - sysdate) * 1440;
547 end if;
548
549 if Dtype <> 'NONE' then
550 -- relative add 1 minutes to deadline to let children time out
551 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
552 Itemkey => ItemKey,
553 aname => 'WAITDEADLINE',
554 avalue => Relative+1);
555
556 end if;
557 -- end of timeout/deadline code
558
559
560 if ntfTarget = 'DATA_OWNERS' then
561 -- this will populate wf attrs and start the NTF Workflow to send Exp NTFs.
562 -- Note: itemkey is ParentItemkey
563 ZPB_EXCEPTION_ALERT.EXPLANATION_BY_OWNER(itemtype, itemkey, taskID);
564 resultout := 'COMPLETE';
565 elsif ntfTarget = 'SPECIFIED' then
566 ZPB_EXCEPTION_ALERT.EXPLANATION_BY_SPECIFIED(itemtype, itemkey, taskID);
567 resultout := 'COMPLETE';
568 elsif ntfTarget = 'OWNER_OF_AC' then
569 ZPB_EXCEPTION_ALERT.EXPL_BY_ACOWNER(itemtype, itemkey, taskID);
570 resultout := 'COMPLETE';
571 end if;
572
573
577
574 else
575 resultout := 'ERROR';
576 end if;
578 -- b 4948928
579 -- if Expired WF users have been detected then send list to BPO or its proxy
580 -- otherwise do nothing.
581
582 thisOwnerID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
583 Itemkey => ItemKey,
584 aname => 'OWNERID');
585
586 zpb_wf_ntf.SendExpiredUserMsg(thisOwnerID, taskID, itemType);
587 -- end b 4948928
588
589 END IF;
590
591
592 return;
593
594 exception
595
596 when others then
597
598 WF_CORE.CONTEXT('ZPB_EXPCEPTION_ALERT.SEND_NOTIFICATIONS', itemtype, itemkey, to_char(actid), funcmode);
599 raise;
600
601 end SEND_NOTIFICATIONS;
602
603
604 procedure WF_RUN_EXCEPTION (errbuf out nocopy varchar2,
605 retcode out nocopy varchar2,
606 taskID in Number,
607 UserID in Number)
608 IS
609
610 x_return_status varchar2(100);
611 x_msg_count number;
612 x_msg_data varchar2(4000);
613
614 BEGIN
615
616
617 /*
618
619 -- zpb_excp_pvt.run_exception(UserID, taskID, 1.0, , , 1, x_return_status, x_msg_count, x_msg_data);
620
621 */
622
623 -- Begining run of exception request
624
625 ZPB_LOG.WRITE_EVENT_TR ('ZPB_EXCEPTION_ALERT.WF_RUN_EXCEPTION', 'ZPB_WF_BEGEXCPRUN');
626
627 zpb_excp_pvt.run_exception(p_api_version => 1.0,
628 p_init_msg_list => FND_API.G_FALSE,
629 p_commit => FND_API.G_TRUE,
630 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
631 x_return_status => x_return_status,
632 x_msg_count => x_msg_count,
633 x_msg_data => x_msg_data,
634 p_task_id => taskID,
635 p_user_id => UserID);
636
637 -- Successfully completed run of exception request
638 ZPB_LOG.WRITE_EVENT_TR ('ZPB_EXCEPTION_ALERT.WF_RUN_EXCEPTION', 'ZPB_WF_COMPEXCPRUN');
639
640 retcode :='0';
641 return;
642
643 exception
644
645 when others then
646 -- Encountered error when running exception request
647 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_WF_ERREXCPRUN');
648 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
649
650 retcode :='2';
651 errbuf:=substr(sqlerrm, 1, 255);
652
653 end WF_RUN_EXCEPTION;
654
655
656 procedure EXCEPTION_LIST (document_id in varchar2,
657 display_type in varchar2,
658 document in out nocopy varchar2,
659 document_type in out nocopy varchar2)
660
661 IS
662 ItemType varchar2(30) := 'EPBCYCLE';
663 ItemKey varchar2(240);
664 taskID number;
665 NID number;
666 ResultList varchar2(4000);
667 thisMember varchar2(240);
668 thisValue varchar2(2000);
669 thisVFlag number;
670 l_label varchar2(200);
671 l_number number;
672 l_dimname varchar2(100);
673
674 CURSOR c_results is
675 select *
676 from ZPB_EXCP_RESULTS
677 where TASK_ID = TaskID;
678
679 v_results c_results%ROWTYPE;
680
681 BEGIN
682
683 TaskID := to_number(substr(document_id, 1, instr(document_id,':')-1));
684 itemkey := substr(document_id, instr(document_id,':')+1);
685
686
687 -- Exceptionable member[s]:
688
689 select value into l_dimname from ZPB_TASK_PARAMETERS
690 where TASK_ID = TaskID and NAME = 'EXCEPTION_DIMENSION_NAME';
691
692 -- Following ZPB_WF_DIMNAME members have an alert
693
694 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_DIMMEMBERS');
695 FND_MESSAGE.SET_TOKEN ('ZPB_WF_DIMNAME', l_dimname);
696 l_label := FND_MESSAGE.GET;
697
698
699 -- put label in WF resource?
700 document := document|| htf.tableOpen(cattributes=>'width=100%');
701 document := document|| htf.tableRowOpen;
702 document := document|| htf.tableData(cvalue=>htf.bold(l_label));
703 document := document|| htf.tableData(htf.br);
704 document := document|| htf.tableRowClose;
705
706
707 for v_results in c_results loop
708 /*
709 thisMember := v_results.member_display;
710
711 thisVFlag := v_results.VALUE_FLAG;
712 if thisVFlag = 0 then
713 thisValue := v_results.VALUE_CHAR;
714 elsif thisVFlag = 1 then
715 l_number := v_results.VALUE_NUMBER;
716 thisValue := to_char(l_number);
717 elsif thisVFlag = 2 then
718 thisValue := to_char(v_results.VALUE_DATE);
719 end if;
720
721 resultList := thisMember || ' - ' || thisVAlue;
722 */
723 resultList := v_results.member_display;
724 -- Exception results
725 document := document|| htf.tableRowOpen;
726 document := document|| htf.tableData(cvalue=>ResultList);
727 document := document|| htf.tableData(htf.br);
728 document := document|| htf.tableRowClose;
729
733
730 end loop;
731
732 document_type := 'text/html';
734 return;
735
736 exception
737 when others then
738 wf_core.context('ZPB_EXCEPTION_ALERT','EXCEPTION_LIST',ItemType, ItemKey);
739 raise;
740
741
742 END EXCEPTION_LIST;
743
744
745 procedure SET_OWNER_USERS (itemtype in varchar2,
746 newitemkey in varchar2,
747 taskID in number,
748 InstanceID in number)
749
750 IS
751
752 UserList varchar2(600);
753 rolename varchar2(100);
754 relative number := 7;
755 thisOWNER varchar2(100);
756
757 CURSOR c_thisowner is
758 select distinct OWNER
759 from ZPB_EXCP_RESULTS
760 where TASK_ID = TaskID
761 order by OWNER;
762
763 v_thisowner c_thisowner%ROWTYPE;
764
765
766 BEGIN
767
768 UserList := ' ';
769
770 for v_thisowner in c_thisowner loop
771 thisOwner := v_thisowner.owner;
772 if UserList = ' ' then
773 UserList := thisOwner;
774 else
775 UserList := UserList || ',' ||thisOwner;
776 end if;
777 end loop;
778
779
780 if instr(UserList, ',') > 0 then
781 -- FND user names list so build Ad Hoc role
782 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, taskID);
783 zpb_wf_ntf.SetRole(rolename, relative);
784 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, UserList);
785 elsif length(UserList) > 0 then
786 rolename := UserList;
787 end if;
788
789 wf_engine.SetItemAttrText(Itemtype => ItemType,
790 Itemkey => NEWItemKey,
791 aname => 'EPBPERFORMER',
792 avalue => RoleName);
793
794 return;
795
796 exception
797
798 when others then
799 raise;
800
801 end SET_OWNER_USERS;
802
803
804 procedure EXCEPTION_BY_OWNER (document_id in varchar2,
805 display_type in varchar2,
806 document in out nocopy varchar2,
807 document_type in out nocopy varchar2)
808
809 IS
810 ItemType varchar2(30) := 'EPBCYCLE';
811 ItemKey varchar2(240);
812 taskID number;
813 nid number;
814 role varchar2(35);
815 DDate date;
816 Dtext varchar2(35);
817 ResultList varchar2(4000);
818 thisMember varchar2(240);
819 thisValue varchar2(1000);
820 thisVFlag number;
821
822 CURSOR c_byowner is
823 select *
824 from ZPB_EXCP_RESULTS
825 where TASK_ID = TaskID and OWNER = role;
826
827 v_byowner c_byowner%ROWTYPE;
828
829 BEGIN
830
831 TaskID := to_number(substr(document_id, 1, instr(document_id,':')-1));
832 nid := to_number(substr(document_id, instr(document_id,':')+1));
833 wf_notification.GetInfo (nid, role, Dtext, Dtext, Dtext, Ddate, Dtext);
834
835 for v_byowner in c_byowner loop
836
837 thisMember := v_byowner.member_display;
838
839 thisVFlag := v_byowner.VALUE_FLAG;
840 if thisVFlag = 0 then
841 thisValue := v_byowner.VALUE_CHAR;
842 elsif thisVFlag = 1 then
843 thisValue := to_char(v_byowner.VALUE_NUMBER);
844 elsif thisVFlag = 2 then
845 thisValue := to_char(v_byowner.VALUE_DATE);
846 end if;
847
848 resultList := thisMember || ' - ' || thisVAlue;
849 -- Exception results
850 document := document|| htf.tableRowOpen;
851 document := document|| htf.tableData(cvalue=>htf.bold(ResultList));
852 document := document|| htf.tableRowClose;
853
854 end loop;
855
856 document_type := 'text/html';
857
858 return;
859
860 exception
861 when others then
862 wf_core.context('ZPB_EXCEPTION_ALERT','EXCEPTION_BY_OWNER',ItemType, ItemKey);
863 raise;
864
865 END EXCEPTION_BY_OWNER;
866
867
868 -- This launches the process EXPLAINCHILD which requires a response.
869 procedure EXPLANATION_BY_OWNER (itemtype in varchar2,
870 ParentItemkey in varchar2,
871 taskID in number)
872 IS
873
874 ACNAME varchar2(300);
875 ACID number;
876 ProcOwner varchar2(30);
877 ProcOwnerID number;
878 rolename varchar2(30);
879 relative number :=7;
880 InstanceID number;
881 InstanceDesc varchar2(300);
882 TaskName varchar2(256);
883 NEWItemKey varchar2(240);
884 workflowprocess varchar2(30) := 'EXPLAINCHILD';
885 thisItemKey varchar2(240);
886 thisOWNER varchar2(100);
887 thisOwnerID number;
888 thisApprover varchar2(100);
889 thisApproverID number;
890 ShadowStatus varchar2(24);
891 errMsg varchar2(2000);
892 NewDispName varchar2(360);
893 l_label varchar2(50);
894
895 l_htmlagent varchar2(1000); --B 4106621 URL
896 l_URLexplain varchar2(1000); --B 4106621 URL
897 l_newURL varchar2(2000); --B 4106621 URL
898
899 CURSOR c_thisowner is
903 order by OWNER;
900 select distinct OWNER_ID, OWNER, APPROVER_ID, APPROVER
901 from ZPB_EXCP_RESULTS
902 where TASK_ID = TaskID
904
905 v_thisowner c_thisowner%ROWTYPE;
906
907
908 CURSOR c_children is
909 select ITEM_KEY
910 from WF_ITEMS_V
911 where PARENT_ITEM_KEY = ParentItemKey;
912
913 v_child c_children%ROWTYPE;
914
915
916
917 BEGIN
918 -- GET current task information.
919 ACID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
920 Itemkey => ParentItemKey,
921 aname => 'ACID');
922 ACNAME := wf_engine.GetItemAttrText(Itemtype => ItemType,
923 Itemkey => ParentItemKey,
924 aname => 'ACNAME');
925 ProcOwnerID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
926 Itemkey => ParentItemKey,
927 aname => 'OWNERID');
928 ProcOwner := zpb_wf_ntf.ID_to_FNDUser(ProcOwnerID);
929
930 InstanceID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
931 Itemkey => ParentItemKey,
932 aname => 'INSTANCEID');
933
934 -- B 4106621 URL
935 -- l_htmlAgent := FND_PROFILE.VALUE_SPECIFIC('APPS_FRAMEWORK_AGENT', ProcOwnerID);
936
937 l_URLexplain := wf_engine.GetItemAttrText(Itemtype => ItemType,
938 Itemkey => ParentItemKey,
939 aname => 'URLEXPLAIN');
940 -- l_newURL := l_htmlAgent || '/OA_HTML/' || l_URLexplain;
941
942 -- set Task Name!
943 select TASK_NAME
944 into TaskName
945 from zpb_analysis_cycle_tasks
946 where ANALYSIS_CYCLE_ID = InstanceID and task_id = taskid;
947
948 select INSTANCE_DESCRIPTION
949 into InstanceDesc
950 from ZPB_ANALYSIS_CYCLE_INSTANCES
951 where INSTANCE_AC_ID = InstanceID;
952
953 -- FOR EACH OWNER start a explanation required process
954 for v_thisowner in c_thisowner loop
955
956 thisOwnerID := v_thisowner.owner_id;
957 thisOwner := v_thisowner.owner;
958 thisApproverID := v_thisowner.approver_id;
959 thisApprover := v_thisowner.approver;
960
961 -- create NEWItemKey for FYI workflow
962 NEWItemKey := rtrim(substr(ACName, 1, 50), ' ') || '-' || to_char(InstanceID) || '-' || to_char(taskID) || '-' || thisOwner || '-' || workflowprocess;
963
964 -- SET UP PROCESS for this NEWItemKey!
965 -- Create WF start process instance
966 wf_engine.CreateProcess(ItemType => ItemType,
967 itemKey => NEWItemKey,
968 process => WorkflowProcess);
969
970 -- SETITEMPARENT! NewItemKey is the CHILD!
971 wf_engine.SetItemParent(ItemType => ItemType,
972 itemKey => NEWItemKey,
973 PARENT_ITEMTYPE => ItemType,
974 PARENT_ITEMKEY => ParentItemKey,
975 PARENT_CONTEXT => NULL);
976
977 -- This should be the EPB controller.
978 wf_engine.SetItemOwner(ItemType => ItemType,
979 ItemKey => NEWItemKey,
980 owner => ProcOwner);
981
982 -- owner ID
983 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
984 Itemkey => NEWItemKey,
985 aname => 'OWNERID',
986 avalue => ProcOwnerID);
987
988 --=============================================================================
989 -- b5179198 URL profile should be set for the ntf target user
990 l_htmlAgent := FND_PROFILE.VALUE_SPECIFIC('APPS_FRAMEWORK_AGENT', thisOwnerID);
991 l_newURL := l_htmlAgent || '/OA_HTML/' || l_URLexplain;
992 --==============================================================================
993
994 -- B 4106621 URL
995 wf_engine.SetItemAttrText(Itemtype => ItemType,
996 Itemkey => NEWItemKey,
997 aname => 'URLEXPLAIN',
998 avalue => l_newURL);
999
1000 -- make the Ad Hoc role to hold both the dataowner and shadow
1001 if zpb_wf_ntf.has_Shadow(thisOwnerID) = 'Y' then
1002 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, TaskID, thisOwnerID);
1003 select distinct display_name
1004 into NewDispName
1005 from wf_users
1006 where name = thisOwner;
1007
1008 -- add (And Shadows) display to role dispaly name
1009 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_ANDSHADOWS');
1010 l_label := FND_MESSAGE.GET;
1011 NewDispName := NewDispName || l_label;
1012
1013 zpb_wf_ntf.SetRole(rolename, relative, NewDispName);
1014 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisOwner);
1015 zpb_wf_ntf.add_Shadow(rolename, thisOwnerID);
1016 else
1017 rolename := thisOwner;
1018 end if;
1019
1020 -- explanation recipient rolename with owner and shadow
1021 wf_engine.SetItemAttrText(Itemtype => ItemType,
1022 Itemkey => NEWItemKey,
1023 aname => 'EPBPERFORMER',
1024 avalue => rolename);
1025
1026 -- **********************************************************
1027 -- make the Ad Hoc role to hold both the APPROVERS and shadow
1028
1029 dbms_lock.sleep(1);
1030
1031 if zpb_wf_ntf.has_Shadow(thisApproverID) = 'Y' then
1035 from wf_users
1032 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, TaskID, thisApproverID);
1033 select distinct display_name
1034 into NewDispName
1036 where name = thisApprover;
1037
1038 -- add (And Shadows) display to role dispaly name
1039 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_ANDSHADOWS');
1040 l_label := FND_MESSAGE.GET;
1041 NewDispName := NewDispName || l_label;
1042
1043 zpb_wf_ntf.SetRole(rolename, relative, NewDispName);
1044 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisApprover);
1045 zpb_wf_ntf.add_Shadow(rolename, thisApproverID);
1046 else
1047 rolename := thisApprover;
1048 end if;
1049
1050 -- approver
1051 wf_engine.SetItemAttrText(Itemtype => ItemType,
1052 Itemkey => NEWItemKey,
1053 aname => 'FNDUSERNAM',
1054 avalue => rolename);
1055
1056 -- plsql document procedure
1057 wf_engine.SetItemAttrText(itemtype => itemtype,
1058 itemkey => NEWItemKey,
1059 aname => 'EXCEPLIST',
1060 avalue => 'PLSQL:ZPB_EXCEPTION_ALERT.EXP_EXCEP_BY_OWNER/' || TO_CHAR(taskID) || ':' || thisOwner);
1061
1062 -- plsql document procedure
1063 wf_engine.SetItemAttrText(itemtype => itemtype,
1064 itemkey => NEWItemKey,
1065 aname => 'RESPNOTE',
1066 avalue => 'PLSQL:ZPB_EXCEPTION_ALERT.SHOW_RESP/' || NEWitemkey );
1067
1068
1069 -- reads parameters and sets attributes for this process
1070 ZPB_EXCEPTION_ALERT.SET_ATTRIBUTES(itemtype, NEWItemkey, taskID);
1071
1072 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
1073 Itemkey => NEWItemKey,
1074 aname => 'ACID',
1075 avalue => ACID);
1076
1077 -- set workflow with Instance Cycle ID!
1078 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
1079 Itemkey => NEWItemKey,
1080 aname => 'INSTANCEID',
1081 avalue => InstanceID);
1082
1083 -- set cycle Name!
1084 wf_engine.SetItemAttrText(Itemtype => ItemType,
1085 Itemkey => NEWItemKey,
1086 aname => 'ACNAME',
1087 avalue => ACNAME);
1088 -- set Task ID!
1089 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
1090 Itemkey => NEWItemKey,
1091 aname => 'TASKID',
1092 avalue => TaskID);
1093
1094 wf_engine.SetItemAttrText(Itemtype => ItemType,
1095 Itemkey => NEWItemKey,
1096 aname => 'TASKNAME',
1097 avalue => TaskName);
1098
1099
1100 -- set descripton
1101 wf_engine.SetItemAttrText(Itemtype => ItemType,
1102 Itemkey => NEWItemKey,
1103 aname => 'INSTANCEDESC',
1104 avalue => InstanceDesc);
1105
1106
1107 wf_engine.SetItemAttrText(Itemtype => ItemType,
1108 Itemkey => NEWItemKey,
1109 aname => 'REGISTER1',
1110 avalue => NULL);
1111
1112 wf_engine.SetItemAttrText(Itemtype => ItemType,
1113 Itemkey => NEWItemKey,
1114 aname => 'REGISTER2',
1115 avalue => NULL);
1116
1117 wf_engine.SetItemAttrText(Itemtype => ItemType,
1118 Itemkey => NEWItemKey,
1119 aname => 'REGISTER3',
1120 avalue => NULL);
1121
1122 wf_engine.SetItemAttrText(Itemtype => ItemType,
1123 Itemkey => NEWItemKey,
1124 aname => 'REGISTER4',
1125 avalue => NULL);
1126
1127 wf_engine.SetItemAttrText(Itemtype => ItemType,
1128 Itemkey => NEWItemKey,
1129 aname => 'REGISTER5',
1130 avalue => NULL);
1131
1132 wf_engine.SetItemAttrText(Itemtype => ItemType,
1133 Itemkey => NEWItemKey,
1134 aname => 'REGISTER6',
1135 avalue => NULL);
1136
1137 end loop;
1138
1139
1140
1141 -- START IT!
1142 for v_child in c_children loop
1143
1144 thisItemKEY := v_child.ITEM_KEY;
1145 -- Now that all is created and set START each CHILD PROCESS!
1146 wf_engine.StartProcess(ItemType => ItemType,
1147 ItemKey => thisItemKey);
1148 end loop;
1149
1150 return;
1151
1152 exception
1153 when others then
1154 -- WF_CORE.CONTEXT('ZPB_EXCEPTION_ALERT.FYI_SPECIFIED', itemtype, itemkey, to_char(actid), funcmode);
1155 raise;
1156
1157 end EXPLANATION_BY_OWNER;
1158
1159
1160
1161 procedure EXP_EXCEP_BY_OWNER (document_id in varchar2,
1162 display_type in varchar2,
1163 document in out nocopy varchar2,
1164 document_type in out nocopy varchar2)
1165
1166 IS
1167 ItemType varchar2(30) := 'EPBCYCLE';
1168 ItemKey varchar2(240);
1169 taskID number;
1170 nid number;
1171 DataOwner varchar2(100);
1172 l_label varchar2(200);
1173 DDate date;
1174 Dtext varchar2(35);
1178 thisVFlag number;
1175 ResultList varchar2(4000);
1176 thisMember varchar2(240);
1177 thisValue varchar2(1000);
1179 l_dimname varchar2(100);
1180
1181 CURSOR c_byowner is
1182 select *
1183 from ZPB_EXCP_RESULTS
1184 where TASK_ID = TaskID and OWNER = DataOwner;
1185
1186 v_byowner c_byowner%ROWTYPE;
1187
1188 BEGIN
1189
1190 TaskID := to_number(substr(document_id, 1, instr(document_id,':')-1));
1191 DataOwner := substr(document_id, instr(document_id,':')+1);
1192
1193
1194 -- Exceptionable member[s]:
1195
1196 select value into l_dimname
1197 from ZPB_TASK_PARAMETERS
1198 where TASK_ID = TaskID and NAME = 'EXCEPTION_DIMENSION_NAME';
1199
1200 -- Following ZPB_WF_DIMNAME members have an alert
1201 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_DIMMEMBERS');
1202 FND_MESSAGE.SET_TOKEN ('ZPB_WF_DIMNAME', l_dimname);
1203 l_label := FND_MESSAGE.GET;
1204
1205
1206 -- put label in WF resource?
1207 document := document|| htf.tableOpen(cattributes=>'width=100%');
1208 document := document|| htf.tableRowOpen;
1209 document := document|| htf.tableData(cvalue=>htf.bold(l_label));
1210 document := document|| htf.tableData(htf.br);
1211 document := document|| htf.tableRowClose;
1212
1213 for v_byowner in c_byowner loop
1214 /*
1215 thisMember := v_byowner.member_display;
1216
1217 thisVFlag := v_byowner.VALUE_FLAG;
1218 if thisVFlag = 0 then
1219 thisValue := v_byowner.VALUE_CHAR;
1220 elsif thisVFlag = 1 then
1221 thisValue := to_char(v_byowner.VALUE_NUMBER);
1222 elsif thisVFlag = 2 then
1223 thisValue := to_char(v_byowner.VALUE_DATE);
1224 end if;
1225
1226 resultList := thisMember || ' - ' || thisVAlue;
1227 */
1228 resultList := v_byowner.member_display;
1229 -- Exception results
1230 document := document|| htf.tableRowOpen;
1231 document := document|| htf.tableData(cvalue=>ResultList);
1232 document := document|| htf.tableData(htf.br);
1233 document := document|| htf.tableRowClose;
1234
1235 end loop;
1236
1237 document_type := 'text/html';
1238
1239 return;
1240
1241 exception
1242 when others then
1243 wf_core.context('ZPB_EXCEPTION_ALERT','EXP_EXCEP_BY_OWNER',ItemType, ItemKey);
1244 raise;
1245
1246 END EXP_EXCEP_BY_OWNER;
1247
1248 procedure MANAGE_RESPONSE(itemtype in varchar2,
1249 itemkey in varchar2,
1250 actid in number,
1251 funcmode in varchar2,
1252 resultout out NOCOPY varchar2)
1253 IS
1254
1255 thisNID Number;
1256 userDispName varchar2(360);
1257 Ttext varchar2(360);
1258 thisRole varchar2(360);
1259 result varchar2(24);
1260 resp varchar2(1000) := NULL;
1261 reg1 varchar2(1000) := NULL;
1262 reg2 varchar2(1000) := NULL;
1263 reg3 varchar2(1000) := NULL;
1264 reg4 varchar2(1000) := NULL;
1265 reg5 varchar2(1000) := NULL;
1266 reg6 varchar2(1000) := NULL;
1267
1268
1269 BEGIN
1270
1271
1272 if (funcmode = 'RUN') then
1273
1274 result := wf_engine.GetItemAttrText(Itemtype => ItemType,
1275 Itemkey => ItemKey,
1276 aname => 'RESULT');
1277
1278
1279
1280 if result = 'REJECTED' or result = 'EXPLANATION' then
1281
1282 thisNID := wf_engine.context_nid;
1283
1284 if result = 'REJECTED' then
1285
1286 thisRole := wf_engine.GetItemAttrText(Itemtype => ItemType,
1287 Itemkey => ItemKey,
1288 aname => 'FNDUSERNAM');
1289 else
1290 thisRole := wf_engine.GetItemAttrText(Itemtype => ItemType,
1291 Itemkey => ItemKey,
1292 aname => 'EPBPERFORMER');
1293
1294 end if;
1295
1296
1297 wf_engine.SetItemAttrText(Itemtype => ItemType,
1298 Itemkey => ItemKey,
1299 aname => '#FROM_ROLE',
1300 avalue => thisRole);
1301
1302
1303 select distinct display_name
1304 into UserDispName
1305 from wf_roles
1306 where name = thisRole;
1307
1308 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
1309 Itemkey => ItemKey,
1310 aname => 'THISNID',
1311 avalue => thisNID);
1312
1313
1314 resp := wf_engine.GetItemAttrText(Itemtype => ItemType,
1315 Itemkey => ItemKey,
1316 aname => 'WF_NOTE');
1317
1318 if resp is not NULL then
1319 resp := userDispName || ' - ' || sysdate ||': ' || resp ;
1320 end if;
1321
1322 reg1 := wf_engine.GetItemAttrText(Itemtype => ItemType,
1323 Itemkey => ItemKey,
1324 aname => 'REGISTER1');
1325
1326 reg2 := wf_engine.GetItemAttrText(Itemtype => ItemType,
1330 reg3 := wf_engine.GetItemAttrText(Itemtype => ItemType,
1327 Itemkey => ItemKey,
1328 aname => 'REGISTER2');
1329
1331 Itemkey => ItemKey,
1332 aname => 'REGISTER3');
1333
1334 reg4 := wf_engine.GetItemAttrText(Itemtype => ItemType,
1335 Itemkey => ItemKey,
1336 aname => 'REGISTER4');
1337
1338 reg5 := wf_engine.GetItemAttrText(Itemtype => ItemType,
1339 Itemkey => ItemKey,
1340 aname => 'REGISTER5');
1341
1342 reg6 := wf_engine.GetItemAttrText(Itemtype => ItemType,
1343 Itemkey => ItemKey,
1344 aname => 'REGISTER6');
1345
1346
1347 if (resp is not null) then
1348 reg6 := reg5;
1349 reg5 := reg4;
1350 reg4 := reg3;
1351 reg3 := reg2;
1352 reg2 := reg1;
1353 reg1 := resp;
1354
1355 wf_engine.SetItemAttrText(Itemtype => ItemType,
1356 Itemkey => ItemKey,
1357 aname => 'REGISTER1',
1358 avalue => reg1);
1359
1360 wf_engine.SetItemAttrText(Itemtype => ItemType,
1361 Itemkey => ItemKey,
1362 aname => 'REGISTER2',
1363 avalue => reg2);
1364
1365 wf_engine.SetItemAttrText(Itemtype => ItemType,
1366 Itemkey => ItemKey,
1367 aname => 'REGISTER3',
1368 avalue => reg3);
1369
1370 wf_engine.SetItemAttrText(Itemtype => ItemType,
1371 Itemkey => ItemKey,
1372 aname => 'REGISTER4',
1373 avalue => reg4);
1374
1375 wf_engine.SetItemAttrText(Itemtype => ItemType,
1376 Itemkey => ItemKey,
1377 aname => 'REGISTER5',
1378 avalue => reg5);
1379
1380 wf_engine.SetItemAttrText(Itemtype => ItemType,
1381 Itemkey => ItemKey,
1382 aname => 'REGISTER6',
1383 avalue => reg6);
1384
1385 wf_engine.SetItemAttrText(Itemtype => ItemType,
1386 Itemkey => ItemKey,
1387 aname => 'WF_NOTE',
1388 avalue => NULL);
1389
1390
1391 end if;
1392
1393 end if;
1394
1395 end if;
1396
1397 if (funcmode = 'TIMEOUT') then
1398 resultout := wf_engine.eng_timedout;
1399 end if;
1400
1401 return;
1402
1403 exception
1404 when others then
1405 WF_CORE.CONTEXT('ZPB_EXCEPTION_ALERT.MANAGE_RESPONSE', itemtype, itemkey, to_char(actid), funcmode);
1406 raise;
1407
1408 end MANAGE_RESPONSE;
1409
1410
1411 procedure SHOW_RESP(document_id in varchar2,
1412 display_type in varchar2,
1413 document in out nocopy varchar2,
1414 document_type in out nocopy varchar2)
1415
1416 IS
1417 ItemType varchar2(30) := 'EPBCYCLE';
1418 ItemKey varchar2(240);
1419 nid number;
1420 l_label varchar2(100);
1421 reg1 varchar2(1000) := NULL;
1422 reg2 varchar2(1000) := NULL;
1423 reg3 varchar2(1000) := NULL;
1424 reg4 varchar2(1000) := NULL;
1425 reg5 varchar2(1000) := NULL;
1426 reg6 varchar2(1000) := NULL;
1427 thisValue varchar2(360);
1428
1429 BEGIN
1430
1431 ItemKey := document_id;
1432
1433 reg1 := wf_engine.GetItemAttrText(Itemtype => ItemType,
1434 Itemkey => ItemKey,
1435 aname => 'REGISTER1');
1436
1437 reg2 := wf_engine.GetItemAttrText(Itemtype => ItemType,
1438 Itemkey => ItemKey,
1439 aname => 'REGISTER2');
1440
1441 reg3 := wf_engine.GetItemAttrText(Itemtype => ItemType,
1442 Itemkey => ItemKey,
1443 aname => 'REGISTER3');
1444
1445 reg4 := wf_engine.GetItemAttrText(Itemtype => ItemType,
1446 Itemkey => ItemKey,
1447 aname => 'REGISTER4');
1448
1449 reg5 := wf_engine.GetItemAttrText(Itemtype => ItemType,
1450 Itemkey => ItemKey,
1451 aname => 'REGISTER5');
1452
1453 reg6 := wf_engine.GetItemAttrText(Itemtype => ItemType,
1454 Itemkey => ItemKey,
1455 aname => 'REGISTER6');
1456
1457
1458 -- Notes:
1459 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_NOTES');
1460 l_label := FND_MESSAGE.GET;
1461
1462 -- Exception results
1463 if reg1 is not NULL then
1464 document := document|| htf.tableRowOpen;
1465 document := document|| htf.tableData(cvalue=>htf.bold(l_label));
1466 document := document|| htf.tableData(htf.br);
1470 document := document|| htf.tableData(htf.br);
1467 document := document|| htf.tableRowClose;
1468 document := document|| htf.tableRowOpen;
1469 document := document|| htf.tableData(cvalue=>reg1);
1471 document := document|| htf.tableRowClose;
1472 end if;
1473
1474 if reg2 is not NULL then
1475 document := document|| htf.tableRowOpen;
1476 document := document|| htf.tableData(cvalue=>reg2);
1477 document := document|| htf.tableData(htf.br);
1478 document := document|| htf.tableRowClose;
1479 end if;
1480
1481 if reg3 is not NULL then
1482 document := document|| htf.tableRowOpen;
1483 document := document|| htf.tableData(cvalue=>reg3);
1484 document := document|| htf.tableData(htf.br);
1485 document := document|| htf.tableRowClose;
1486 end if;
1487
1488 if reg4 is not NULL then
1489 document := document|| htf.tableRowOpen;
1490 document := document|| htf.tableData(cvalue=>reg4);
1491 document := document|| htf.tableData(htf.br);
1492 document := document|| htf.tableRowClose;
1493 end if;
1494
1495 if reg5 is not NULL then
1496 document := document|| htf.tableRowOpen;
1497 document := document|| htf.tableData(cvalue=>reg5);
1498 document := document|| htf.tableData(htf.br);
1499 document := document|| htf.tableRowClose;
1500 end if;
1501
1502 if reg6 is not NULL then
1503 document := document|| htf.tableRowOpen;
1504 document := document|| htf.tableData(cvalue=>reg6);
1505 document := document|| htf.tableData(htf.br);
1506 document := document|| htf.tableRowClose;
1507 end if;
1508
1509 document_type := 'text/html';
1510
1511 return;
1512
1513 exception
1514 when others then
1515 wf_core.context('ZPB_EXCEPTION_ALERT','SHOW_RESP',ItemType, ItemKey);
1516 raise;
1517
1518 END SHOW_RESP;
1519
1520
1521 procedure CLEAN_RESULTS_TABLE (errbuf out nocopy varchar2,
1522 retcode out nocopy varchar2,
1523 taskID in Number)
1524 IS
1525
1526 BEGIN
1527
1528 -- delete ZPB_EXCP_RESULTS
1529 -- where TASK_ID = taskID;
1530
1531 -- delete ZPB_EXCP_EXPLANATIONS
1532 -- where TASK_ID = taskID;
1533
1534 -- commit;
1535
1536 retcode :='0';
1537 return;
1538
1539 exception
1540
1541 when others then
1542 rollback;
1543 retcode :='2';
1544 errbuf:=substr(sqlerrm, 1, 255);
1545
1546 end CLEAN_RESULTS_TABLE;
1547
1548 -- This launches the process EXPLAINCHILD which requires a response.
1549 procedure EXPLANATION_BY_SPECIFIED(itemtype in varchar2,
1550 ParentItemkey in varchar2,
1551 taskID in number)
1552 IS
1553
1554 ACNAME varchar2(300);
1555 ACID number;
1556 owner varchar2(30);
1557 InstanceID number;
1558 InstanceDesc varchar2(300);
1559 TaskName varchar2(256);
1560 NEWItemKey varchar2(240);
1561 workflowprocess varchar2(30) := 'EXPLAINCHILD';
1562 thisItemKey varchar2(240);
1563 thisRecipient varchar2(100);
1564 thisApprover varchar2(100);
1565 thisRecipientID number;
1566 thisApproverID number;
1567 errMsg varchar2(2000);
1568 rolename varchar2(320);
1569 NewDispName varchar2(360);
1570 l_label varchar2(30);
1571 relative number;
1572 ApproverTYPE varchar2(4000);
1573
1574 l_htmlagent varchar2(2000); -- B 4106621 URL agent override
1575 ProcOwnerID number; -- B 4106621 URL agent override
1576 l_URLexplain varchar2(1000); --B 4106621 URL
1577 l_newURL varchar2(2000); --B 4106621 URL
1578
1579
1580 CURSOR c_children is
1581 select ITEM_KEY
1582 from WF_ITEMS_V
1583 where PARENT_ITEM_KEY = ParentItemKey;
1584
1585 v_child c_children%ROWTYPE;
1586
1587 CURSOR c_recipient is
1588 select value
1589 from ZPB_TASK_PARAMETERS
1590 where TASK_ID = TaskID and name = 'SPECIFIED_NOTIFICATION_RECIPIENT';
1591
1592 v_recipient c_recipient%ROWTYPE;
1593
1594 BEGIN
1595 -- GET current task information.
1596 ACID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
1597 Itemkey => ParentItemKey,
1598 aname => 'ACID');
1599 ACNAME := wf_engine.GetItemAttrText(Itemtype => ItemType,
1600 Itemkey => ParentItemKey,
1601 aname => 'ACNAME');
1602 owner := wf_engine.GetItemAttrText(Itemtype => ItemType,
1603 Itemkey => ParentItemKey,
1604 aname => 'FNDUSERNAM');
1605 InstanceID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
1606 Itemkey => ParentItemKey,
1607 aname => 'INSTANCEID');
1608
1609
1610 -- B 4106621 URL agent override
1611 --ProcOwnerID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
1612 -- Itemkey => ParentItemKey,
1613 -- aname => 'OWNERID');
1614
1615 --l_htmlAgent := FND_PROFILE.VALUE_SPECIFIC('APPS_FRAMEWORK_AGENT', ProcOwnerID);
1616
1620
1617 l_URLexplain := wf_engine.GetItemAttrText(Itemtype => ItemType,
1618 Itemkey => ParentItemKey,
1619 aname => 'URLEXPLAIN');
1621 --l_newURL := l_htmlAgent || '/OA_HTML/' || l_URLexplain;
1622
1623 -- set Task Name!
1624 select TASK_NAME, LAST_UPDATED_BY
1625 into TaskName, thisApproverID
1626 from zpb_analysis_cycle_tasks
1627 where ANALYSIS_CYCLE_ID = InstanceID and task_id = taskid;
1628
1629 select INSTANCE_DESCRIPTION
1630 into InstanceDesc
1631 from ZPB_ANALYSIS_CYCLE_INSTANCES
1632 where INSTANCE_AC_ID = InstanceID;
1633
1634 select value
1635 into ApproverTYPE
1636 from ZPB_TASK_PARAMETERS
1637 where TASK_ID = TaskID and name = 'EXPLANATION_APPROVER';
1638
1639 if ApproverTYPE = 'AUTHOR_OF_EXCEPTION' then
1640 thisApprover := zpb_wf_ntf.ID_to_fnduser(thisApproverID);
1641 end if;
1642
1643 -- FOR EACH OWNER start a explanation required process
1644 for v_recipient in c_recipient loop
1645 dbms_lock.sleep(1);
1646 thisRecipient := v_recipient.value;
1647 -- create NEWItemKey for FYI workflow
1648 NEWItemKey := rtrim(substr(ACName, 1, 50), ' ') || '-' || to_char(InstanceID) || '-' || to_char(taskID) || '-' || thisRecipient || '-' || workflowprocess;
1649
1650 -- SET UP PROCESS for this NEWItemKey!
1651 -- Create WF start process instance
1652 wf_engine.CreateProcess(ItemType => ItemType,
1653 itemKey => NEWItemKey,
1654 process => WorkflowProcess);
1655
1656 -- SETITEMPARENT! NewItemKey is the CHILD!
1657 wf_engine.SetItemParent(ItemType => ItemType,
1658 itemKey => NEWItemKey,
1659 PARENT_ITEMTYPE => ItemType,
1660 PARENT_ITEMKEY => ParentItemKey,
1661 PARENT_CONTEXT => NULL);
1662
1663 -- This should be the EPB controller.
1664 wf_engine.SetItemOwner(ItemType => ItemType,
1665 ItemKey => NEWItemKey,
1666 owner => owner);
1667
1668
1669 -- explanation recipient
1670 -- make the Ad Hoc role to hold both the dataowner and shadow
1671
1672 thisRecipientID := zpb_wf_ntf.fnduser_to_ID(thisRecipient);
1673
1674 --=============================================================================
1675 -- b5179198 URL profile should be set for the ntf target user
1676 l_htmlAgent := FND_PROFILE.VALUE_SPECIFIC('APPS_FRAMEWORK_AGENT', thisRecipientID);
1677 l_newURL := l_htmlAgent || '/OA_HTML/' || l_URLexplain;
1678 --==============================================================================
1679
1680 if zpb_wf_ntf.has_Shadow(thisRecipientID) = 'Y' then
1681 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, TaskID, thisRecipientID);
1682 select distinct display_name
1683 into NewDispName
1684 from wf_users
1685 where name = thisRecipient;
1686
1687 -- add (And Shadows) display to role dispaly name
1688 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_ANDSHADOWS');
1689 l_label := FND_MESSAGE.GET;
1690 NewDispName := NewDispName || l_label;
1691
1692 zpb_wf_ntf.SetRole(rolename, relative, NewDispName);
1693 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisRecipient);
1694 zpb_wf_ntf.add_Shadow(rolename, thisRecipientID);
1695 else
1696 rolename := thisRecipient;
1697 end if;
1698
1699 -- explanation recipient rolename with owner and shadow
1700 wf_engine.SetItemAttrText(Itemtype => ItemType,
1701 Itemkey => NEWItemKey,
1702 aname => 'EPBPERFORMER',
1703 avalue => rolename);
1704
1705
1706 -- **********************************************************
1707 -- make the Ad Hoc role to hold both the APPROVERS and shadow
1708
1709 if zpb_wf_ntf.has_Shadow(thisApproverID) = 'Y' then
1710 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, TaskID, thisApproverID);
1711 select distinct display_name
1712 into NewDispName
1713 from wf_users
1714 where name = thisApprover;
1715
1716 -- add (And Shadows) display to role dispaly name
1717 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_ANDSHADOWS');
1718 l_label := FND_MESSAGE.GET;
1719 NewDispName := NewDispName || l_label;
1720
1721 zpb_wf_ntf.SetRole(rolename, relative, NewDispName);
1722 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisApprover);
1723 zpb_wf_ntf.add_Shadow(rolename, thisApproverID);
1724 else
1725 rolename := thisApprover;
1726 end if;
1727
1728 -- approver
1729 wf_engine.SetItemAttrText(Itemtype => ItemType,
1730 Itemkey => NEWItemKey,
1731 aname => 'FNDUSERNAM',
1732 avalue => rolename);
1733
1734
1735 -- plsql document procedure
1736 wf_engine.SetItemAttrText(itemtype => itemtype,
1737 itemkey => NEWItemKey,
1738 aname => 'EXCEPLIST',
1739 avalue => 'PLSQL:ZPB_EXCEPTION_ALERT.EXCEPTION_LIST/'|| TO_CHAR(taskID)||':'||NEWItemKey);
1740
1741 -- plsql document procedure
1742 wf_engine.SetItemAttrText(itemtype => itemtype,
1743 itemkey => NEWItemKey,
1747
1744 aname => 'RESPNOTE',
1745 avalue => 'PLSQL:ZPB_EXCEPTION_ALERT.SHOW_RESP/' || NEWitemkey );
1746
1748 -- reads parameters and sets attributes for this process
1749 ZPB_EXCEPTION_ALERT.SET_ATTRIBUTES(itemtype, NEWItemkey, taskID);
1750
1751 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
1752 Itemkey => NEWItemKey,
1753 aname => 'ACID',
1754 avalue => ACID);
1755
1756 -- set workflow with Instance Cycle ID!
1757 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
1758 Itemkey => NEWItemKey,
1759 aname => 'INSTANCEID',
1760 avalue => InstanceID);
1761
1762 -- set cycle Name!
1763 wf_engine.SetItemAttrText(Itemtype => ItemType,
1764 Itemkey => NEWItemKey,
1765 aname => 'ACNAME',
1766 avalue => ACNAME);
1767 -- set Task ID!
1768 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
1769 Itemkey => NEWItemKey,
1770 aname => 'TASKID',
1771 avalue => TaskID);
1772
1773 wf_engine.SetItemAttrText(Itemtype => ItemType,
1774 Itemkey => NEWItemKey,
1775 aname => 'TASKNAME',
1776 avalue => TaskName);
1777
1778 -- set descripton
1779 wf_engine.SetItemAttrText(Itemtype => ItemType,
1780 Itemkey => NEWItemKey,
1781 aname => 'INSTANCEDESC',
1782 avalue => InstanceDesc);
1783
1784 -- B 4106621 URL
1785 wf_engine.SetItemAttrText(Itemtype => ItemType,
1786 Itemkey => NEWItemKey,
1787 aname => 'URLEXPLAIN',
1788 avalue => l_newURL);
1789
1790 wf_engine.SetItemAttrText(Itemtype => ItemType,
1791 Itemkey => NEWItemKey,
1792 aname => 'REGISTER1',
1793 avalue => NULL);
1794
1795 wf_engine.SetItemAttrText(Itemtype => ItemType,
1796 Itemkey => NEWItemKey,
1797 aname => 'REGISTER2',
1798 avalue => NULL);
1799
1800 wf_engine.SetItemAttrText(Itemtype => ItemType,
1801 Itemkey => NEWItemKey,
1802 aname => 'REGISTER3',
1803 avalue => NULL);
1804
1805 wf_engine.SetItemAttrText(Itemtype => ItemType,
1806 Itemkey => NEWItemKey,
1807 aname => 'REGISTER4',
1808 avalue => NULL);
1809
1810 wf_engine.SetItemAttrText(Itemtype => ItemType,
1811 Itemkey => NEWItemKey,
1812 aname => 'REGISTER5',
1813 avalue => NULL);
1814
1815 wf_engine.SetItemAttrText(Itemtype => ItemType,
1816 Itemkey => NEWItemKey,
1817 aname => 'REGISTER6',
1818 avalue => NULL);
1819
1820 end loop;
1821
1822
1823
1824 -- START IT!
1825 for v_child in c_children loop
1826
1827 thisItemKEY := v_child.ITEM_KEY;
1828 -- Now that all is created and set START each CHILD PROCESS!
1829 wf_engine.StartProcess(ItemType => ItemType,
1830 ItemKey => thisItemKey);
1831 end loop;
1832
1833 return;
1834
1835 exception
1836 when others then
1837 -- WF_CORE.CONTEXT('ZPB_EXCEPTION_ALERT.EXPLANATION_BY_SPECIFIED', itemtype, itemkey, to_char(actid), funcmode);
1838 raise;
1839
1840 end EXPLANATION_BY_SPECIFIED;
1841
1842 procedure BUILD_DEADLINE_NTF (itemtype in varchar2,
1843 itemkey in varchar2,
1844 actid in number,
1845 funcmode in varchar2,
1846 resultout out nocopy varchar2)
1847 IS
1848
1849
1850 errMsg varchar2(320);
1851 TaskID number;
1852 InstDesc varchar2(300);
1853 value varchar2(4000);
1854 InstanceID number;
1855 workflowprocess varchar2(30);
1856 taskParamName varchar2(100);
1857 thisCount number := 0;
1858 taskname varchar2(150);
1859 l_label varchar2(4000);
1860
1861 CURSOR c_tparams is
1862 select NAME, value
1863 from ZPB_TASK_PARAMETERS
1864 where TASK_ID = TaskID;
1865
1866 v_tparams c_tparams%ROWTYPE;
1867
1868 BEGIN
1869
1870
1871 IF (funcmode = 'RUN') THEN
1872
1873 resultout :='COMPLETE:N';
1874
1875 -- b5102962 WF_ITEM_ACTIVITY_STATUSES_V is non performant so
1876 -- it is replaced by the table WF_ITEM_ACTIVITY_STATUSES and
1877 -- ACTIVITY_STATUS_CODE is replaced by ACTIVITY_STATUS
1878 select count(*) into thisCount
1879 from WF_ITEM_ACTIVITY_STATUSES
1880 where ITEM_TYPE = 'EPBCYCLE' and
1881 ITEM_KEY in (select ITEM_KEY from WF_ITEMS_V where PARENT_ITEM_KEY = ItemKey)
1882 and (ACTIVITY_STATUS = 'NOTIFIED' or ACTIVITY_RESULT_CODE = '#TIMEOUT');
1883
1884 if thisCount > 0 then
1885
1886 TaskID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
1887 Itemkey => ItemKey,
1888 aname => 'TASKID');
1889
1893
1890 InstanceID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
1891 Itemkey => ItemKey,
1892 aname => 'INSTANCEID');
1894 select INSTANCE_DESCRIPTION
1895 into InstDesc
1896 from ZPB_ANALYSIS_CYCLE_INSTANCES
1897 where INSTANCE_AC_ID = InstanceID;
1898
1899 -- set descripton
1900 wf_engine.SetItemAttrText(Itemtype => ItemType,
1901 Itemkey => ItemKey,
1902 aname => 'INSTANCEDESC',
1903 avalue => InstDesc);
1904
1905 for v_tparams in c_tparams loop
1906
1907 taskParamName := v_tparams.name;
1908
1909 if taskParamName = 'EXCEPTION_DIMENSION_NAME' then
1910 value := v_tparams.value;
1911 wf_engine.SetItemAttrText(Itemtype => ItemType,
1912 Itemkey => ItemKey,
1913 aname => 'EXCEPDIM',
1914 avalue => value);
1915 elsif taskParamName = 'SAVED_SELECTION_NAME' then
1916 value := v_tparams.value;
1917
1918
1919 if value is not NULL then
1920 -- Selection label
1921 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_SELECT_LABEL');
1922 l_label := FND_MESSAGE.GET;
1923 value := l_label || value;
1924
1925 wf_engine.SetItemAttrText(Itemtype => ItemType,
1926 Itemkey => ItemKey,
1927 aname => 'EXCEPSELECTION',
1928 avalue => value);
1929 end if;
1930
1931 end if;
1932 end loop;
1933 resultout :='COMPLETE:Y';
1934 end if;
1935
1936 END IF;
1937
1938 return;
1939
1940 exception
1941
1942 when others then
1943 WF_CORE.CONTEXT('ZPB_WF.BUILD_DEADLINE_NTF', itemtype, itemkey, to_char(actid), funcmode);
1944 raise;
1945 end BUILD_DEADLINE_NTF;
1946
1947 -- b5102962 reorganized this procedure removed WF view from cursor - performance issue.
1948 procedure NON_RESPONDERS (document_id in varchar2,
1949 display_type in varchar2,
1950 document in out nocopy varchar2,
1951 document_type in out nocopy varchar2)
1952
1953 IS
1954 ItemType varchar2(30);
1955 ItemKey varchar2(240);
1956 thisUser varchar2(320);
1957 childItemKey varchar2(240);
1958 l_label varchar2(150);
1959 l_ctr number;
1960 l_activity_id number;
1961
1962 -- b5102962 WF_ITEM_ACTIVITY_STATUSES_V is non performant so
1963 -- it is replaced by the table WF_ITEM_ACTIVITY_STATUSES and
1964 -- ACTIVITY_STATUS_CODE is replaced by ACTIVITY_STATUS and
1965 -- ASSIGNED_USER_DISPLAY_NAME to ASSIGNED_USER.
1966 -- Done for both cursors below.
1967
1968 CURSOR c_exceptionPool is
1969 SELECT ITEM_KEY, ASSIGNED_USER
1970 FROM WF_ITEM_ACTIVITY_STATUSES
1971 WHERE ITEM_TYPE = 'EPBCYCLE' and
1972 ITEM_KEY in (select ITEM_KEY from WF_ITEMS_V where PARENT_ITEM_KEY = ItemKey)
1973 and (ACTIVITY_STATUS = 'NOTIFIED' or ACTIVITY_RESULT_CODE = '#TIMEOUT');
1974 v_exChild c_exceptionPool%ROWTYPE;
1975
1976 BEGIN
1977
1978 ItemType := 'EPBCYCLE';
1979 ItemKey := document_id;
1980 -- Explanations not received from:
1981 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_EXPLAINERS');
1982 l_label := FND_MESSAGE.GET;
1983
1984 --pool1
1985 l_ctr := 0;
1986 for v_exChild in c_exceptionPool loop
1987
1988 -- b5102962
1989 l_activity_id := NULL;
1990 childItemKey := v_exChild.Item_key;
1991
1992 begin
1993
1994 select activity_id into l_activity_id
1995 from WF_ITEM_ACTIVITY_STATUSES_V
1996 WHERE ITEM_TYPE = 'EPBCYCLE'
1997 AND ITEM_KEY = childItemKey
1998 AND activity_name = 'EXPREQNTF';
1999
2000 exception
2001 when NO_DATA_FOUND then
2002 l_activity_id := null;
2003 end;
2004
2005 -- if there is a hit then process this
2006 if l_activity_id is not NULL then
2007 l_ctr := l_ctr+1;
2008 thisUser := substr(wf_directory.getroledisplayname(v_exChild.ASSIGNED_USER), 1, 320);
2009 -- thisUser := v_explainer.ASSIGNED_USER_DISPLAY_NAME;
2010 -- put label in WF resource?
2011 if l_ctr = 1 then
2012 document := document|| htf.tableOpen(cattributes=>'width=100%');
2013 document := document|| htf.tableRowOpen;
2014 document := document|| htf.tableData(cvalue=>htf.bold(l_label));
2015 document := document|| htf.tableData(htf.br);
2016 document := document|| htf.tableRowClose;
2017 end if;
2018 -- Exception results
2019 document := document|| htf.tableRowOpen;
2020 document := document|| htf.tableData(cvalue=>thisUser);
2021 document := document|| htf.tableData(htf.br);
2022 document := document|| htf.tableRowClose;
2023 end if;
2024 end loop;
2025 --pool1
2026
2027
2028 --pool2
2029 -- Approvals not received from:
2033 l_ctr := 0;
2030 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_APPROVERS');
2031 l_label := FND_MESSAGE.GET;
2032
2034 for v_exChild in c_exceptionPool loop
2035 -- b5102962
2036 l_activity_id := NULL;
2037 childItemKey := v_exChild.Item_key;
2038
2039 begin
2040
2041 select activity_id into l_activity_id
2042 from WF_ITEM_ACTIVITY_STATUSES_V
2043 WHERE ITEM_TYPE = 'EPBCYCLE'
2044 AND ITEM_KEY = childItemKey
2045 and activity_name = 'EXPAPPROVAL';
2046
2047 exception
2048 when NO_DATA_FOUND then
2049 l_activity_id := null;
2050 end;
2051 -- if there is a hit then process this
2052 if l_activity_id is NOT NULL then
2053 l_ctr := l_ctr+1;
2054 -- b5102962
2055 thisUser := substr(wf_directory.getroledisplayname(v_exChild.ASSIGNED_USER), 1, 320);
2056 -- thisUser := v_approver.ASSIGNED_USER_DISPLAY_NAME;
2057 -- put label in WF resource?
2058
2059 if l_ctr = 1 then
2060 document := document|| htf.tableOpen(cattributes=>'width=100%');
2061 document := document|| htf.tableRowOpen;
2062 document := document|| htf.tableData(cvalue=>htf.bold(l_label));
2063 document := document|| htf.tableData(htf.br);
2064 document := document|| htf.tableRowClose;
2065 end if;
2066
2067 -- Exception results
2068 document := document|| htf.tableRowOpen;
2069 document := document|| htf.tableData(cvalue=>thisUser);
2070 document := document|| htf.tableData(htf.br);
2071 document := document|| htf.tableRowClose;
2072 end if;
2073
2074 end loop;
2075 -- pool2
2076
2077 document_type := 'text/html';
2078
2079 return;
2080
2081 exception
2082
2083 when others then
2084 wf_core.context('ZPB_EXCEPTION_ALERT','NON_RESPONDERS',ItemType, ItemKey);
2085 raise;
2086
2087 end NON_RESPONDERS;
2088
2089 -- 5179198 agent by user
2090 procedure REQUEST_EXPLANATIONS (taskID in NUMBER,
2091 NID in NUMBER,
2092 AddMsg in varchar2 default NULL,
2093 Dtype in varchar2 default NULL,
2094 Dvalue in number default NULL,
2095 p_api_version IN NUMBER,
2096 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2097 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2098 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2099 x_return_status OUT nocopy varchar2,
2100 x_msg_count OUT nocopy number,
2101 x_msg_data OUT nocopy varchar2)
2102
2103
2104
2105 IS
2106
2107 l_api_name CONSTANT VARCHAR2(30) := 'REQUEST_EXPLANATIONS';
2108 l_api_version CONSTANT NUMBER := 1.0;
2109
2110 ItemKey varchar2(240);
2111 ItemType varchar2(30) := 'EPBCYCLE';
2112 InstanceID number;
2113 ACID number;
2114 ACNAME varchar2(256);
2115 InstDesc varchar2(256);
2116 Subject varchar2(4000);
2117 Message varchar2(4000);
2118 ExcepDim varchar2(4000);
2119 ExcepSel varchar2(4000);
2120 TaskName varchar2(256);
2121 DeadDate date;
2122 Deadline varchar2(30);
2123 relative number;
2124 NEWItemKey varchar2(240);
2125 workflowprocess varchar2(30) := 'EXPLAIN_MORE';
2126 rolename varchar2(100);
2127 thisOwner varchar2(100);
2128 thisOwnerID number;
2129 thisApprover varchar2(100);
2130 thisApproverID number;
2131 errMsg varchar2(2000);
2132 NewDispName varchar2(360);
2133 l_label varchar2(50);
2134
2135 ProcOwnerID number; -- B 4106621 URL agent override
2136 l_htmlagent varchar2(2000); -- B 4106621 URL agent override
2137 l_URLexplain varchar2(1000); --B 4106621 URL
2138 l_newURL varchar2(2000); --B 4106621 URL
2139
2140
2141 CURSOR c_thisowner is
2142 select distinct OWNER, OWNER_ID, APPROVER, APPROVER_ID
2143 from ZPB_EXCP_EXPLANATIONS
2144 where TASK_ID = TaskID and Notification_id = NID and STATUS = 2
2145 order by OWNER;
2146
2147 v_thisowner c_thisowner%ROWTYPE;
2148
2149
2150 BEGIN
2151
2152 -- Standard Start of API savepoint
2153 SAVEPOINT zpb_request_explanation;
2154 -- Standard call to check for call compatibility.
2155 IF NOT FND_API.Compatible_API_Call( l_api_version,
2156 p_api_version,
2157 l_api_name,
2158 G_PKG_NAME)
2159 THEN
2160 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2161 END IF;
2162 -- Initialize message list if p_init_msg_list is set to TRUE.
2163 IF FND_API.to_Boolean(p_init_msg_list) THEN
2164 FND_MSG_PUB.initialize;
2165 END IF;
2166 -- Initialize API return status to success
2167 x_return_status := FND_API.G_RET_STS_SUCCESS;
2168
2169
2173 where item_type = 'EPBCYCLE' and NOTIFICATION_ID = NID;
2170 -- Begin REQUEST_EXPLANATIONS code
2171 select ITEM_KEY into ItemKey
2172 from WF_ITEM_ACTIVITY_STATUSES_V
2174
2175
2176 ACID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
2177 Itemkey => ItemKey,
2178 aname => 'ACID');
2179 InstanceID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
2180 Itemkey => ItemKey,
2181 aname => 'INSTANCEID');
2182 TaskName := wf_engine.GetItemAttrText(Itemtype => ItemType,
2183 Itemkey => ItemKey,
2184 aname => 'TASKNAME');
2185 Subject := wf_engine.GetItemAttrText(Itemtype => ItemType,
2186 Itemkey => ItemKey,
2187 aname => 'SUBJECT');
2188 Message := wf_engine.GetItemAttrText(Itemtype => ItemType,
2189 Itemkey => ItemKey,
2190 aname => 'ISSUEMSG');
2191 ExcepDim := wf_engine.GetItemAttrText(Itemtype => ItemType,
2192 Itemkey => ItemKey,
2193 aname => 'EXCEPDIM');
2194 ExcepSel := wf_engine.GetItemAttrText(Itemtype => ItemType,
2195 Itemkey => ItemKey,
2196 aname => 'EXCEPSELECTION');
2197 InstDesc := wf_engine.GetItemAttrText(Itemtype => ItemType,
2198 Itemkey => ItemKey,
2199 aname => 'INSTANCEDESC');
2200
2201 -- B 4106621 URL agent override
2202 --l_htmlAgent := FND_PROFILE.VALUE_SPECIFIC('APPS_FRAMEWORK_AGENT', ProcOwnerID);
2203 --
2204 l_URLexplain := wf_engine.GetItemAttrText(Itemtype => ItemType,
2205 Itemkey => ItemKey,
2206 aname => 'URLEXPLAIN');
2207 --
2208 --l_newURL := l_htmlAgent || '/OA_HTML/' || l_URLexplain;
2209
2210
2211 if Dtype is not NULL then
2212
2213 if Dtype = 'DAY' then
2214 Relative := Dvalue * 1440;
2215 DeadDate := sysdate + DValue;
2216 elsif Dtype = 'WEEK' then
2217 Relative := Dvalue * 7 * 1440;
2218 DeadDate := sysdate + (DValue * 7);
2219 elsif Dtype = 'MONTH' then
2220 DeadDate := add_months(sysdate, Dvalue);
2221 Relative := (DeadDate - sysdate) * 1440;
2222 end if;
2223 else
2224 Relative := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
2225 Itemkey => ItemKey,
2226 aname => 'EXPDEADLINE');
2227 end if;
2228
2229
2230 -- NOTE: need to add owner in.
2231
2232
2233 -- THE BIG LOOP
2234 -- FOR EACH OWNER start a explanation required process
2235
2236 for v_thisowner in c_thisowner loop
2237
2238 thisOwner := v_thisowner.owner;
2239 thisOwnerID := v_thisowner.owner_id;
2240 thisApprover := v_thisowner.approver;
2241 thisApproverID := v_thisowner.approver_id;
2242
2243 -- create NEWItemKey for FYI workflow
2244 NEWItemKey := rtrim(substr(InstDesc, 1, 25), ' ') || '-' || to_char(InstanceID) || '-' || to_char(taskID) || '-' || thisOwner || '-' || to_char(sysdate, 'MM/DD/YYYY-HH24:MI:SS') || workflowprocess;
2245
2246 -- SET UP PROCESS for this NEWItemKey!
2247 -- Create WF start process instance
2248 wf_engine.CreateProcess(ItemType => ItemType,
2249 itemKey => NEWItemKey,
2250 process => WorkflowProcess);
2251
2252 -- This should be the EPB controller.
2253 wf_engine.SetItemOwner(ItemType => ItemType,
2254 ItemKey => NEWItemKey,
2255 owner => thisOwner);
2256
2257
2258 --=============================================================================
2259 -- b5179198 URL profile should be set for the ntf target user
2260 l_htmlAgent := FND_PROFILE.VALUE_SPECIFIC('APPS_FRAMEWORK_AGENT', thisOwnerID);
2261 l_newURL := l_htmlAgent || '/OA_HTML/' || l_URLexplain;
2262 --==============================================================================
2263
2264 -- B 4106621 URL agent override
2265 wf_engine.SetItemAttrText(Itemtype => ItemType,
2266 Itemkey => NEWItemKey,
2267 aname => 'URLEXPLAIN',
2268 avalue => l_newURL);
2269
2270 -- **********************************************************
2271 -- make the Ad Hoc role to hold both the dataowner and shadow
2272 dbms_lock.sleep(1);
2273 if zpb_wf_ntf.has_Shadow(thisOwnerID) = 'Y' then
2274 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, TaskID, thisOwnerID);
2275 select distinct display_name
2276 into NewDispName
2277 from wf_users
2278 where name = thisOwner;
2279
2280 -- add (And Shadows) display to role dispaly name
2281 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_ANDSHADOWS');
2282 l_label := FND_MESSAGE.GET;
2283 NewDispName := NewDispName || l_label;
2284
2285 zpb_wf_ntf.SetRole(rolename, relative, NewDispName);
2286 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisOwner);
2287 zpb_wf_ntf.add_Shadow(rolename, thisOwnerID);
2288 else
2289 rolename := thisOwner;
2290 end if;
2291
2292 -- explanation recipient
2296 avalue => rolename);
2293 wf_engine.SetItemAttrText(Itemtype => ItemType,
2294 Itemkey => NEWItemKey,
2295 aname => 'EPBPERFORMER',
2297
2298 -- **********************************************************
2299 -- make the Ad Hoc role to hold both the APPROVERS and shadow
2300 if zpb_wf_ntf.has_Shadow(thisApproverID) = 'Y' then
2301 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, TaskID, thisApproverID);
2302 select distinct display_name
2303 into NewDispName
2304 from wf_users
2305 where name = thisApprover;
2306
2307 -- add (And Shadows) display to role dispaly name
2308 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_ANDSHADOWS');
2309 l_label := FND_MESSAGE.GET;
2310 NewDispName := NewDispName || l_label;
2311
2312 zpb_wf_ntf.SetRole(rolename, relative, NewDispName);
2313 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisApprover);
2314 zpb_wf_ntf.add_Shadow(rolename, thisApproverID);
2315 else
2316 rolename := thisApprover;
2317 end if;
2318
2319 -- approver
2320 wf_engine.SetItemAttrText(Itemtype => ItemType,
2321 Itemkey => NEWItemKey,
2322 aname => 'FNDUSERNAM',
2323 avalue => rolename);
2324
2325
2326 -- plsql document procedure
2327 wf_engine.SetItemAttrText(itemtype => itemtype,
2328 itemkey => NEWItemKey,
2329 aname => 'EXCEPLIST',
2330 avalue => 'PLSQL:ZPB_EXCEPTION_ALERT.EXCEP_NTF_LIST/' || TO_CHAR(taskID) || ':' || NID || ':' || thisOwner);
2331
2332 -- plsql document procedure
2333 wf_engine.SetItemAttrText(itemtype => itemtype,
2334 itemkey => NEWItemKey,
2335 aname => 'RESPNOTE',
2336 avalue => 'PLSQL:ZPB_EXCEPTION_ALERT.SHOW_RESP/' || NEWitemkey );
2337
2338 -- reads parameters and sets attributes for this process
2339 ZPB_EXCEPTION_ALERT.SET_ATTRIBUTES(itemtype, NEWItemkey, taskID);
2340
2341 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
2342 Itemkey => NEWItemKey,
2343 aname => 'ACID',
2344 avalue => ACID);
2345
2346 -- set workflow with Instance Cycle ID!
2347 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
2348 Itemkey => NEWItemKey,
2349 aname => 'INSTANCEID',
2350 avalue => InstanceID);
2351
2352 -- set Task ID!
2353 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
2354 Itemkey => NEWItemKey,
2355 aname => 'TASKID',
2356 avalue => TaskID);
2357
2358 wf_engine.SetItemAttrText(Itemtype => ItemType,
2359 Itemkey => NEWItemKey,
2360 aname => 'TASKNAME',
2361 avalue => TaskName);
2362
2363 wf_engine.SetItemAttrText(Itemtype => ItemType,
2364 Itemkey => NEWItemKey,
2365 aname => 'SUBJECT',
2366 avalue => Subject);
2367
2368 if AddMsg is not NULL then
2369 Message := Message || ' Additional Note: ' || AddMsg;
2370 end if;
2371
2372 wf_engine.SetItemAttrText(Itemtype => ItemType,
2373 Itemkey => NEWItemKey,
2374 aname => 'ISSUEMSG',
2375 avalue => Message);
2376
2377 wf_engine.SetItemAttrText(Itemtype => ItemType,
2378 Itemkey => NEWItemKey,
2379 aname => 'EXCEPDIM',
2380 avalue => ExcepDim);
2381
2382 wf_engine.SetItemAttrText(Itemtype => ItemType,
2383 Itemkey => NEWItemKey,
2384 aname => 'EXCEPSELECTION',
2385 avalue => ExcepSel);
2386
2387 wf_engine.SetItemAttrText(Itemtype => ItemType,
2388 Itemkey => NEWItemKey,
2389 aname => 'INSTANCEDESC',
2390 avalue => InstDesc);
2391
2392 -- relative default is Task Deadline!
2393 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
2394 Itemkey => NEWItemKey,
2395 aname => 'EXPDEADLINE',
2396 avalue => Relative);
2397
2398 -- Initialize corespondance regesters
2399 wf_engine.SetItemAttrText(Itemtype => ItemType,
2400 Itemkey => NEWItemKey,
2401 aname => 'REGISTER1',
2402 avalue => NULL);
2403 wf_engine.SetItemAttrText(Itemtype => ItemType,
2404 Itemkey => NEWItemKey,
2405 aname => 'REGISTER2',
2406 avalue => NULL);
2407 wf_engine.SetItemAttrText(Itemtype => ItemType,
2408 Itemkey => NEWItemKey,
2409 aname => 'REGISTER3',
2410 avalue => NULL);
2411 wf_engine.SetItemAttrText(Itemtype => ItemType,
2412 Itemkey => NEWItemKey,
2413 aname => 'REGISTER4',
2414 avalue => NULL);
2415 wf_engine.SetItemAttrText(Itemtype => ItemType,
2416 Itemkey => NEWItemKey,
2417 aname => 'REGISTER5',
2418 avalue => NULL);
2419 wf_engine.SetItemAttrText(Itemtype => ItemType,
2423
2420 Itemkey => NEWItemKey,
2421 aname => 'REGISTER6',
2422 avalue => NULL);
2424 -- Now that all is created and set START each independent PROCESS!
2425 wf_engine.StartProcess(ItemType => ItemType,
2426 ItemKey => NewItemKey);
2427 end loop;
2428
2429 -- b 4948928
2430 -- if Expired WF users have been detected then send list to BPO or its proxy
2431 -- otherwise do nothing.
2432
2433 ProcOwnerID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
2434 Itemkey => ItemKey,
2435 aname => 'OWNERID');
2436
2437 zpb_wf_ntf.SendExpiredUserMsg(ProcOwnerID, taskID, itemType);
2438 -- end b 4948928
2439
2440
2441 /*
2442 commit;
2443 return;
2444 */
2445
2446 -- Standard check of p_commit.
2447 IF FND_API.To_Boolean( p_commit ) THEN
2448 COMMIT WORK;
2449 END IF;
2450
2451 -- Standard call to get message count and if count is 1, get message info.
2452 FND_MSG_PUB.Count_And_Get(
2453 p_count => x_msg_count,
2454 p_data => x_msg_data
2455 );
2456
2457
2458 EXCEPTION
2459
2460 /*
2461 WHEN OTHERS THEN
2462 raise;
2463 */
2464
2465
2466 WHEN FND_API.G_EXC_ERROR THEN
2467 ROLLBACK TO zpb_request_explanation;
2468 x_return_status := FND_API.G_RET_STS_ERROR;
2469 FND_MSG_PUB.Count_And_Get(
2470 p_count => x_msg_count,
2471 p_data => x_msg_data
2472 );
2473 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2474 ROLLBACK TO zpb_request_explanation;
2475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2476 FND_MSG_PUB.Count_And_Get(
2477 p_count => x_msg_count,
2478 p_data => x_msg_data
2479 );
2480 WHEN OTHERS THEN
2481 ROLLBACK TO zpb_request_explanation;
2482 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2483 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2484 FND_MSG_PUB.Add_Exc_Msg(
2485 G_PKG_NAME,
2486 l_api_name
2487 );
2488 END IF;
2489 FND_MSG_PUB.Count_And_Get(
2490 p_count => x_msg_count,
2491 p_data => x_msg_data
2492 );
2493
2494 end REQUEST_EXPLANATIONS;
2495
2496 procedure EXCEP_NTF_LIST (document_id in varchar2,
2497 display_type in varchar2,
2498 document in out nocopy varchar2,
2499 document_type in out nocopy varchar2)
2500
2501 IS
2502 ItemType varchar2(30) := 'EPBCYCLE';
2503 ItemKey varchar2(240);
2504 taskID number;
2505 nid number;
2506 startPos1 number;
2507 startPos2 number;
2508 DataOwner varchar2(100);
2509 l_label varchar2(200);
2510 DDate date;
2511 Dtext varchar2(35);
2512 ResultList varchar2(4000);
2513 thisMember varchar2(240);
2514 thisValue varchar2(1000);
2515 thisVFlag number;
2516 l_dimname varchar2(100);
2517
2518 CURSOR c_byowner is
2519 select *
2520 from ZPB_EXCP_EXPLANATIONS
2521 where TASK_ID = TaskID and
2522 NOTIFICATION_ID = nid and
2523 OWNER = DataOwner and
2524 status = 2;
2525
2526 v_byowner c_byowner%ROWTYPE;
2527
2528 BEGIN
2529
2530 startPos1 := instr(document_id,':')+1;
2531 startPos2 := instr(document_id, ':', startPos1);
2532 TaskID := to_number(substr(document_id, 1, instr(document_id,':')-1));
2533 -- nid := to_number(substr(document_id, startPos1, instr(document_id, ':')-1));
2534 nid := to_number(substr(document_id, startPos1, startPos2 - startPos1));
2535 DataOwner := substr(document_id, startPos2+1);
2536
2537
2538 -- Exceptionable member[s]:
2539
2540 select value into l_dimname
2541 from ZPB_TASK_PARAMETERS
2542 where TASK_ID = TaskID and NAME = 'EXCEPTION_DIMENSION_NAME';
2543
2544 -- Following ZPB_WF_DIMNAME members have an alert
2545 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_DIMMEMBERS');
2546 FND_MESSAGE.SET_TOKEN ('ZPB_WF_DIMNAME', l_dimname);
2547 l_label := FND_MESSAGE.GET;
2548
2549 -- put label in WF resource?
2550 document := document|| htf.tableOpen(cattributes=>'width=100%');
2551 document := document|| htf.tableRowOpen;
2552 document := document|| htf.tableData(cvalue=>htf.bold(l_label));
2553 document := document|| htf.tableData(htf.br);
2554 document := document|| htf.tableRowClose;
2555
2556 for v_byowner in c_byowner loop
2557 /*
2558 thisMember := v_byowner.member_display;
2559
2560 thisVFlag := v_byowner.VALUE_FLAG;
2561 if thisVFlag = 0 then
2562 thisValue := v_byowner.VALUE_CHAR;
2563 elsif thisVFlag = 1 then
2564 thisValue := to_char(v_byowner.VALUE_NUMBER);
2565 elsif thisVFlag = 2 then
2566 thisValue := to_char(v_byowner.VALUE_DATE);
2567 end if;
2568 */
2569 resultList := v_byowner.member_display;
2570 -- Exception results
2571 document := document|| htf.tableRowOpen;
2575
2572 document := document|| htf.tableData(cvalue=>ResultList);
2573 document := document|| htf.tableData(htf.br);
2574 document := document|| htf.tableRowClose;
2576 end loop;
2577
2578 document_type := 'text/html';
2579
2580 return;
2581
2582 exception
2583 when others then
2584 wf_core.context('ZPB_EXCEPTION_ALERT','EXCEP_NTF_LIST',ItemType, ItemKey);
2585 raise;
2586
2587 END EXCEP_NTF_LIST;
2588
2589
2590 -- This launches the process
2591 procedure FYI_BY_OWNER (itemtype in varchar2,
2592 ParentItemkey in varchar2,
2593 taskID in number)
2594 IS
2595
2596 ACNAME varchar2(300);
2597 ACID number;
2598 ProcOwner varchar2(30);
2599 ProcOwnerID number;
2600 AuthorID number;
2601 SentToAuthor varchar2(1) := 'N';
2602 ExcType varchar2(4000);
2603 rolename varchar2(30);
2604 relative number :=7;
2605 InstanceID number;
2606 InstanceDesc varchar2(300);
2607 TaskName varchar2(256);
2608 NEWItemKey varchar2(240);
2609 workflowprocess varchar2(30) := 'NOTIFYEXCEPT';
2610 thisItemKey varchar2(240);
2611 thisOWNER varchar2(100);
2612 thisOwnerID number;
2613 errMsg varchar2(2000);
2614 NewDispName varchar2(360);
2615 l_label varchar2(50);
2616 l_authorIDT varchar2(4000);
2617
2618
2619 CURSOR c_thisowner is
2620 select distinct OWNER_ID, OWNER
2621 from ZPB_EXCP_RESULTS
2622 where TASK_ID = TaskID
2623 order by OWNER;
2624
2625 v_thisowner c_thisowner%ROWTYPE;
2626
2627
2628 CURSOR c_children is
2629 select ITEM_KEY
2630 from WF_ITEMS_V
2631 where PARENT_ITEM_KEY = ParentItemKey;
2632
2633 v_child c_children%ROWTYPE;
2634
2635
2636 BEGIN
2637 -- GET current task information.
2638 ACID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
2639 Itemkey => ParentItemKey,
2640 aname => 'ACID');
2641 ACNAME := wf_engine.GetItemAttrText(Itemtype => ItemType,
2642 Itemkey => ParentItemKey,
2643 aname => 'ACNAME');
2644 ProcOwnerID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
2645 Itemkey => ParentItemKey,
2646 aname => 'OWNERID');
2647 ProcOwner := zpb_wf_ntf.ID_to_FNDUser(ProcOwnerID);
2648
2649 InstanceID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
2650 Itemkey => ParentItemKey,
2651 aname => 'INSTANCEID');
2652
2653 -- set Task Name!
2654 select TASK_NAME into TaskName
2655 from zpb_analysis_cycle_tasks
2656 where ANALYSIS_CYCLE_ID = InstanceID and task_id = taskid;
2657
2658 -- bug 3482485
2659 select value into l_authorIDT
2660 from zpb_task_parameters
2661 where task_id = TaskID and name = 'OWNER_ID';
2662
2663 authorID := to_number(l_authorIDT);
2664
2665
2666 select INSTANCE_DESCRIPTION
2667 into InstanceDesc
2668 from ZPB_ANALYSIS_CYCLE_INSTANCES
2669 where INSTANCE_AC_ID = InstanceID;
2670
2671 select VALUE
2672 into ExcType
2673 from ZPB_TASK_PARAMETERS
2674 where TASK_ID = TaskID and name = 'EXCEPTION_TYPE';
2675
2676 -- FOR EACH OWNER start a explanation required process
2677 for v_thisowner in c_thisowner loop
2678
2679 thisOwner := v_thisowner.owner;
2680 thisOwnerID := v_thisowner.owner_id;
2681
2682 -- create NEWItemKey for FYI workflow
2683 NEWItemKey := rtrim(substr(ACName, 1, 25), ' ') || '-' || to_char(InstanceID) || '-' || to_char(taskID) || '-' || thisOwner || '-' || to_char(sysdate, 'MM/DD/YYYY-HH24:MI:SS') || workflowprocess;
2684
2685 -- SET UP PROCESS for this NEWItemKey!
2686 -- Create WF start process instance
2687 wf_engine.CreateProcess(ItemType => ItemType,
2688 itemKey => NEWItemKey,
2689 process => WorkflowProcess);
2690
2691 -- This should be the EPB controller.
2692 wf_engine.SetItemOwner(ItemType => ItemType,
2693 ItemKey => NEWItemKey,
2694 owner => ProcOwner);
2695
2696 -- owner ID
2697 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
2698 Itemkey => NEWItemKey,
2699 aname => 'OWNERID',
2700 avalue => ProcOwnerID);
2701
2702
2703 -- **********************************************************
2704 -- make the Ad Hoc role to hold both the dataowner and shadow
2705 if zpb_wf_ntf.has_Shadow(thisOwnerID) = 'Y' then
2706 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, TaskID);
2707 select distinct display_name
2708 into NewDispName
2709 from wf_users
2710 where name = thisOwner;
2711
2712 -- add (And Shadows) display to role dispaly name
2713 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_ANDSHADOWS');
2714 l_label := FND_MESSAGE.GET;
2715 NewDispName := NewDispName || l_label;
2716
2717 zpb_wf_ntf.SetRole(rolename, relative, NewDispName);
2721 rolename := thisOwner;
2718 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisOwner);
2719 zpb_wf_ntf.add_Shadow(rolename, thisOwnerID);
2720 else
2722 end if;
2723
2724 -- explanation recipient rolename with owner and shadow
2725 wf_engine.SetItemAttrText(Itemtype => ItemType,
2726 Itemkey => NEWItemKey,
2727 aname => 'EPBPERFORMER',
2728 avalue => rolename);
2729
2730
2731 -- plsql document procedure
2732 wf_engine.SetItemAttrText(itemtype => itemtype,
2733 itemkey => NEWItemKey,
2734 aname => 'EXCEPLIST',
2735 avalue => 'PLSQL:ZPB_EXCEPTION_ALERT.EXP_EXCEP_BY_OWNER/' || TO_CHAR(taskID) || ':' || thisOwner);
2736
2737
2738 -- reads parameters and sets attributes for this process
2739 ZPB_EXCEPTION_ALERT.SET_ATTRIBUTES(itemtype, NEWItemkey, taskID);
2740
2741 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
2742 Itemkey => NEWItemKey,
2743 aname => 'ACID',
2744 avalue => ACID);
2745
2746 -- set workflow with Instance Cycle ID!
2747 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
2748 Itemkey => NEWItemKey,
2749 aname => 'INSTANCEID',
2750 avalue => InstanceID);
2751
2752 -- set cycle Name!
2753 wf_engine.SetItemAttrText(Itemtype => ItemType,
2754 Itemkey => NEWItemKey,
2755 aname => 'ACNAME',
2756 avalue => ACNAME);
2757 -- set Task ID!
2758 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
2759 Itemkey => NEWItemKey,
2760 aname => 'TASKID',
2761 avalue => TaskID);
2762
2763 wf_engine.SetItemAttrText(Itemtype => ItemType,
2764 Itemkey => NEWItemKey,
2765 aname => 'TASKNAME',
2766 avalue => TaskName);
2767
2768 -- set descripton
2769 wf_engine.SetItemAttrText(Itemtype => ItemType,
2770 Itemkey => NEWItemKey,
2771 aname => 'INSTANCEDESC',
2772 avalue => InstanceDesc);
2773
2774
2775 -- Now that all is created and set START each independent PROCESS!
2776 wf_engine.StartProcess(ItemType => ItemType,
2777 ItemKey => NEWItemKey);
2778
2779 end loop;
2780
2781
2782 return;
2783
2784 exception
2785 when others then
2786 -- WF_CORE.CONTEXT('ZPB_EXCEPTION_ALERT.FYI_BY_OWNER', itemtype, itemkey, to_char(actid), funcmode);
2787 raise;
2788
2789 end FYI_BY_OWNER;
2790
2791
2792 procedure EXPL_BY_ACOWNER(itemtype in varchar2,
2793 ParentItemkey in varchar2,
2794 taskID in number)
2795 IS
2796
2797 ACNAME varchar2(300);
2798 ACID number;
2799 owner varchar2(30);
2800 InstanceID number;
2801 InstanceDesc varchar2(300);
2802 TaskName varchar2(256);
2803 NEWItemKey varchar2(240);
2804 workflowprocess varchar2(30) := 'EXPLAINCHILD';
2805 thisItemKey varchar2(240);
2806 thisRecipient varchar2(100);
2807 thisApprover varchar2(100);
2808 thisRecipientID number;
2809 thisApproverID number;
2810 errMsg varchar2(2000);
2811 rolename varchar2(320);
2812 NewDispName varchar2(360);
2813 l_label varchar2(30);
2814 relative number;
2815 ApproverTYPE varchar2(4000);
2816 l_htmlagent varchar2(2000); -- B 4106621 URL agent override
2817 ProcOwnerID number; -- B 4106621 URL agent override
2818 l_URLexplain varchar2(1000); --B 4106621 URL
2819 l_newURL varchar2(2000); --B 4106621 URL
2820
2821
2822 CURSOR c_children is
2823 select ITEM_KEY
2824 from WF_ITEMS_V
2825 where PARENT_ITEM_KEY = ParentItemKey;
2826
2827 v_child c_children%ROWTYPE;
2828
2829 BEGIN
2830 -- GET current task information.
2831 ACID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
2832 Itemkey => ParentItemKey,
2833 aname => 'ACID');
2834 ACNAME := wf_engine.GetItemAttrText(Itemtype => ItemType,
2835 Itemkey => ParentItemKey,
2836 aname => 'ACNAME');
2837
2838 -- should be GOOD owner
2839 owner := wf_engine.GetItemAttrText(Itemtype => ItemType,
2840 Itemkey => ParentItemKey,
2841 aname => 'FNDUSERNAM');
2842
2843 thisRecipient := owner;
2844
2845 InstanceID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
2846 Itemkey => ParentItemKey,
2847 aname => 'INSTANCEID');
2848
2849
2850 -- B 4106621 URL agent override
2851 ProcOwnerID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
2852 Itemkey => ParentItemKey,
2853 aname => 'OWNERID');
2854
2855 l_htmlAgent := FND_PROFILE.VALUE_SPECIFIC('APPS_FRAMEWORK_AGENT', ProcOwnerID);
2856 l_URLexplain := wf_engine.GetItemAttrText(Itemtype => ItemType,
2857 Itemkey => ParentItemKey,
2858 aname => 'URLEXPLAIN');
2859 l_newURL := l_htmlAgent || '/OA_HTML/' || l_URLexplain;
2860
2861 -- set Task Name!
2862 select TASK_NAME, LAST_UPDATED_BY
2863 into TaskName, thisApproverID
2864 from zpb_analysis_cycle_tasks
2865 where ANALYSIS_CYCLE_ID = InstanceID and task_id = taskid;
2869 from ZPB_ANALYSIS_CYCLE_INSTANCES
2866
2867 select INSTANCE_DESCRIPTION
2868 into InstanceDesc
2870 where INSTANCE_AC_ID = InstanceID;
2871
2872 select value
2873 into ApproverTYPE
2874 from ZPB_TASK_PARAMETERS
2875 where TASK_ID = TaskID and name = 'EXPLANATION_APPROVER';
2876
2877 if ApproverTYPE = 'AUTHOR_OF_EXCEPTION' then
2878 thisApprover := zpb_wf_ntf.ID_to_fnduser(thisApproverID);
2879 end if;
2880
2881
2882 -- create NEWItemKey for FYI workflow
2883 NEWItemKey := rtrim(substr(ACName, 1, 50), ' ') || '-' || to_char(InstanceID) || '-' || to_char(taskID) || '-' || thisRecipient || '-' || workflowprocess;
2884
2885 -- SET UP PROCESS for this NEWItemKey!
2886 -- Create WF start process instance
2887 wf_engine.CreateProcess(ItemType => ItemType,
2888 itemKey => NEWItemKey,
2889 process => WorkflowProcess);
2890
2891 -- SETITEMPARENT! NewItemKey is the CHILD!
2892 wf_engine.SetItemParent(ItemType => ItemType,
2893 itemKey => NEWItemKey,
2894 PARENT_ITEMTYPE => ItemType,
2895 PARENT_ITEMKEY => ParentItemKey,
2896 PARENT_CONTEXT => NULL);
2897
2898 -- This should be the EPB publisher controller.
2899 wf_engine.SetItemOwner(ItemType => ItemType,
2900 ItemKey => NEWItemKey,
2901 owner => owner);
2902
2903
2904 -- B 4106621 URL agent override
2905 wf_engine.SetItemAttrText(Itemtype => ItemType,
2906 Itemkey => NEWItemKey,
2907 aname => 'URLEXPLAIN',
2908 avalue => l_newURL);
2909
2910 -- explanation recipient
2911 -- make the Ad Hoc role to hold both the dataowner and shadow
2912
2913
2914 thisRecipientID := zpb_wf_ntf.fnduser_to_ID(thisRecipient);
2915 if zpb_wf_ntf.has_Shadow(thisRecipientID) = 'Y' then
2916 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, TaskID, thisRecipientID);
2917 select distinct display_name
2918 into NewDispName
2919 from wf_users
2920 where name = thisRecipient;
2921
2922 -- add (And Shadows) display to role dispaly name
2923 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_ANDSHADOWS');
2924 l_label := FND_MESSAGE.GET;
2925 NewDispName := NewDispName || l_label;
2926
2927 zpb_wf_ntf.SetRole(rolename, relative, NewDispName);
2928 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisRecipient);
2929 zpb_wf_ntf.add_Shadow(rolename, thisRecipientID);
2930 else
2931 rolename := thisRecipient;
2932 end if;
2933
2934 -- explanation recipient rolename with owner and shadow
2935 wf_engine.SetItemAttrText(Itemtype => ItemType,
2936 Itemkey => NEWItemKey,
2937 aname => 'EPBPERFORMER',
2938 avalue => rolename);
2939
2940
2941 -- **********************************************************
2942 -- make the Ad Hoc role to hold both the APPROVERS and shadow
2943
2944 if zpb_wf_ntf.has_Shadow(thisApproverID) = 'Y' then
2945 rolename := zpb_wf_ntf.MakeRoleName(InstanceID, TaskID, thisApproverID);
2946 select distinct display_name
2947 into NewDispName
2948 from wf_users
2949 where name = thisApprover;
2950
2951 -- add (And Shadows) display to role dispaly name
2952 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_ANDSHADOWS');
2953 l_label := FND_MESSAGE.GET;
2954 NewDispName := NewDispName || l_label;
2955
2956 zpb_wf_ntf.SetRole(rolename, relative, NewDispName);
2957 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisApprover);
2958 zpb_wf_ntf.add_Shadow(rolename, thisApproverID);
2959 else
2960 rolename := thisApprover;
2961 end if;
2962
2963 -- approver
2964 wf_engine.SetItemAttrText(Itemtype => ItemType,
2965 Itemkey => NEWItemKey,
2966 aname => 'FNDUSERNAM',
2967 avalue => rolename);
2968
2969
2973 aname => 'EXCEPLIST',
2970 -- plsql document procedure
2971 wf_engine.SetItemAttrText(itemtype => itemtype,
2972 itemkey => NEWItemKey,
2974 avalue => 'PLSQL:ZPB_EXCEPTION_ALERT.EXCEPTION_LIST/'|| TO_CHAR(taskID)||':'||NEWItemKey);
2975
2976 -- plsql document procedure
2977 wf_engine.SetItemAttrText(itemtype => itemtype,
2978 itemkey => NEWItemKey,
2979 aname => 'RESPNOTE',
2980 avalue => 'PLSQL:ZPB_EXCEPTION_ALERT.SHOW_RESP/' || NEWitemkey );
2981
2982
2983 -- reads parameters and sets attributes for this process
2984 ZPB_EXCEPTION_ALERT.SET_ATTRIBUTES(itemtype, NEWItemkey, taskID);
2985
2986 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
2987 Itemkey => NEWItemKey,
2988 aname => 'ACID',
2989 avalue => ACID);
2990
2991 -- set workflow with Instance Cycle ID!
2992 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
2993 Itemkey => NEWItemKey,
2994 aname => 'INSTANCEID',
2995 avalue => InstanceID);
2996
2997 -- set cycle Name!
2998 wf_engine.SetItemAttrText(Itemtype => ItemType,
2999 Itemkey => NEWItemKey,
3000 aname => 'ACNAME',
3001 avalue => ACNAME);
3002 -- set Task ID!
3003 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
3004 Itemkey => NEWItemKey,
3005 aname => 'TASKID',
3006 avalue => TaskID);
3007
3008 wf_engine.SetItemAttrText(Itemtype => ItemType,
3009 Itemkey => NEWItemKey,
3010 aname => 'TASKNAME',
3011 avalue => TaskName);
3012
3013 -- set descripton
3014 wf_engine.SetItemAttrText(Itemtype => ItemType,
3015 Itemkey => NEWItemKey,
3016 aname => 'INSTANCEDESC',
3017 avalue => InstanceDesc);
3018
3019
3020 wf_engine.SetItemAttrText(Itemtype => ItemType,
3021 Itemkey => NEWItemKey,
3022 aname => 'REGISTER1',
3023 avalue => NULL);
3024
3025 wf_engine.SetItemAttrText(Itemtype => ItemType,
3026 Itemkey => NEWItemKey,
3027 aname => 'REGISTER2',
3028 avalue => NULL);
3029
3030 wf_engine.SetItemAttrText(Itemtype => ItemType,
3031 Itemkey => NEWItemKey,
3032 aname => 'REGISTER3',
3033 avalue => NULL);
3034
3035 wf_engine.SetItemAttrText(Itemtype => ItemType,
3036 Itemkey => NEWItemKey,
3037 aname => 'REGISTER4',
3038 avalue => NULL);
3039
3040 wf_engine.SetItemAttrText(Itemtype => ItemType,
3041 Itemkey => NEWItemKey,
3042 aname => 'REGISTER5',
3043 avalue => NULL);
3044
3045 wf_engine.SetItemAttrText(Itemtype => ItemType,
3046 Itemkey => NEWItemKey,
3047 aname => 'REGISTER6',
3048 avalue => NULL);
3049
3050
3051
3052 -- START IT!
3053 for v_child in c_children loop
3054
3055 thisItemKEY := v_child.ITEM_KEY;
3056 -- Now that all is created and set START each CHILD PROCESS!
3057 wf_engine.StartProcess(ItemType => ItemType,
3058 ItemKey => thisItemKey);
3059 end loop;
3060
3061 return;
3062
3063 exception
3064 when others then
3065 -- WF_CORE.CONTEXT('ZPB_EXCEPTION_ALERT.EXPL_BY_ACOWNER', itemtype, itemkey, to_char(actid), funcmode);
3066 raise;
3067
3068 end EXPL_BY_ACOWNER;
3069
3070
3071 end ZPB_EXCEPTION_ALERT;