1 package body WF_ITEM_ACTIVITY_STATUS as
2 /* $Header: wfengb.pls 120.38.12020000.3 2012/11/13 19:42:45 alsosa ship $ */
3
4 -- Runtime cache
5 c_itemtype varchar2(8);
6 c_itemkey varchar2(240);
7 c_actid number;
8 c_status varchar2(8);
9 c_result varchar2(30);
10 c_assigned_user varchar2(320);
11 c_notification_id number;
12 c_begindate date;
13 c_enddate date;
14 c_duedate date;
15 c_errname varchar2(30);
16 c_errmsg varchar2(2000);
17 c_errstack varchar2(4000);
18
19 -- Global Execution Counter (PRIVATE)
20 g_ExecCounter number := 0;
21
22 --
23 -- ClearCache
24 -- Clear runtime cache
25 --
26 procedure ClearCache
27 is
28 begin
29 wf_item_activity_status.c_itemtype := '';
30 wf_item_activity_status.c_itemkey := '';
31 wf_item_activity_status.c_actid := '';
32 wf_item_activity_status.c_status := '';
33 wf_item_activity_status.c_result := '';
34 wf_item_activity_status.c_assigned_user := '';
35 wf_item_activity_status.c_notification_id := '';
36 wf_item_activity_status.c_begindate := to_date(NULL);
37 wf_item_activity_status.c_enddate := to_date(NULL);
38 wf_item_activity_status.c_duedate := to_date(NULL);
39 wf_item_activity_status.c_errname := '';
40 wf_item_activity_status.c_errmsg := '';
41 wf_item_activity_status.c_errstack := '';
42 exception
43 when others then
44 Wf_Core.Context('Wf_Item_Activity_Status', 'ClearCache');
45 raise;
46 end ClearCache;
47
48 --
49 -- InitCache
50 -- Initialize runtime cache
51 --
52 procedure InitCache(
53 itemtype in varchar2,
54 itemkey in varchar2,
55 actid in number,
56 ignore_notfound in boolean default FALSE)
57
58 is
59 begin
60 -- Check for refresh
61 if ((itemtype = wf_item_activity_status.c_itemtype) and
62 (itemkey = wf_item_activity_status.c_itemkey) and
63 (actid = wf_item_activity_status.c_actid)) then
64 return;
65 end if;
66
67 -- SYNCHMODE: Error if this is not current activity.
68 if (itemkey = wf_engine.eng_synch) then
69 Wf_Core.Token('ITEMTYPE', itemtype);
70 Wf_Core.Token('ITEMKEY', itemkey);
71 Wf_Core.Token('ACTID', to_char(actid));
72 Wf_Core.Raise('WFSQL_INTERNAL');
73 end if;
74
75 -- Query new values
76 select WIAS.ACTIVITY_STATUS, WIAS.ACTIVITY_RESULT_CODE,
77 WIAS.ASSIGNED_USER,
78 WIAS.NOTIFICATION_ID,
79 WIAS.BEGIN_DATE, WIAS.END_DATE,
80 WIAS.DUE_DATE,
81 WIAS.ERROR_NAME, WIAS.ERROR_MESSAGE,
82 WIAS.ERROR_STACK
83 into wf_item_activity_status.c_status, wf_item_activity_status.c_result,
84 wf_item_activity_status.c_assigned_user,
85 wf_item_activity_status.c_notification_id,
86 wf_item_activity_status.c_begindate, wf_item_activity_status.c_enddate,
87 wf_item_activity_status.c_duedate,
88 wf_item_activity_status.c_errname, wf_item_activity_status.c_errmsg,
89 wf_item_activity_status.c_errstack
90 from WF_ITEM_ACTIVITY_STATUSES WIAS
91 where WIAS.ITEM_TYPE = itemtype
92 and WIAS.ITEM_KEY = itemkey
93 and WIAS.PROCESS_ACTIVITY = actid;
94
95 -- Save cache key values
96 wf_item_activity_status.c_itemtype := itemtype;
97 wf_item_activity_status.c_itemkey := itemkey;
98 wf_item_activity_status.c_actid := actid;
99
100 exception
101
102 when NO_DATA_FOUND then
103 if (ignore_notfound) then
104 WF_ITEM_ACTIVITY_STATUS.ClearCache;
105
106 else
107
108 Wf_Core.Context('Wf_Item_Activity_Status', 'InitCache',
109 itemtype, itemkey, to_char(actid));
110 raise;
111
112 end if;
113
114 when others then
115 Wf_Core.Context('Wf_Item_Activity_Status', 'InitCache',
116 itemtype, itemkey, to_char(actid));
117 raise;
118 end InitCache;
119
120 --
121 -- Update_Notification (PRIVATE)
122 -- Update the notification id and assigned user in WF_ITEM_ACTIVITY_STATUSES
123 -- table for a given item activity.
124 -- IN
125 -- itemtype - Activity item type.
126 -- itemkey - The item key.
127 -- actid - Process activity (instance id).
128 -- notid - Notification id for this notification activity.
129 -- user - The perform user for this notification activity.
130 --
131 procedure Update_Notification(itemtype in varchar2,
132 itemkey in varchar2,
133 actid in number,
134 notid in number,
135 user in varchar2)
136 is
137 begin
138 update
139 WF_ITEM_ACTIVITY_STATUSES set
140 NOTIFICATION_ID = notid,
141 ASSIGNED_USER = user
142 where ITEM_TYPE = itemtype
143 and ITEM_KEY = itemkey
144 and PROCESS_ACTIVITY = actid;
145
146 -- Update runtime cache if this is the current row
147 if ((wf_item_activity_status.c_itemtype = itemtype) and
148 (wf_item_activity_status.c_itemkey = itemkey) and
149 (wf_item_activity_status.c_actid = actid)) then
150 wf_item_activity_status.c_assigned_user := user;
151 wf_item_activity_status.c_notification_id := notid;
152 end if;
153
154 if (Wf_Engine.Debug) then
155 commit;
156 end if;
157 exception
158 when OTHERS then
159 Wf_Core.Context('Wf_Item_Activity_Status', 'Update_Notification', itemtype,
160 itemkey, to_char(actid), to_char(notid), user);
161 raise;
162 end Update_Notification;
163
164 --
165 -- Root_Status (PRIVATE)
166 -- Returns the status and result for the root process of this item.
167 -- If the process is not yet active, the status and result will be null.
168 -- IN
169 -- itemtype - Activity item type.
170 -- itemkey - The item key.
171 -- OUT
172 -- status - Activity status for root process of this item
173 -- result - Result code for root process of this item
174 --
175 procedure Root_Status(itemtype in varchar2,
176 itemkey in varchar2,
177 status out NOCOPY varchar2,
178 result out NOCOPY varchar2)
179 is
180 root varchar2(30);
181 version pls_integer;
182 rootid pls_integer;
183 begin
184 -- Get root process
185 Wf_Item.root_process(itemtype, itemkey, root, version);
186 if (root is null) then
187 Wf_Core.Token('TYPE', itemtype);
188 Wf_Core.Token('KEY', itemkey);
189 Wf_Core.Raise('WFENG_ITEM');
190 end if;
191
192 -- Get root process actid
193 rootid := Wf_Process_Activity.RootInstanceId(itemtype, itemkey, root);
194 if (rootid is null) then
195 Wf_Core.Token('TYPE', itemtype);
196 Wf_Core.Token('NAME', root);
197 Wf_Core.Raise('WFENG_PROCESS_RUNNABLE');
198 end if;
199
200 -- Get status
201 Wf_Item_Activity_Status.Result(itemtype, itemkey, rootid, status, result);
202
203 exception
204 when others then
205 Wf_Core.Context('Wf_Item_Activity_Status', 'Root_Status',
206 itemtype, itemkey);
207 raise;
208 end Root_Status;
209
210 --
211 -- LastResult
212 -- Get the instid and status of current row in cache.
213 -- Only used in SYNCHMODE.
214 -- IN
215 -- itemtype - itemtype of item
216 -- itemkey - itemkey of item
217 -- OUT
218 -- actid - instance id of current activity in cache
219 -- status - status of current activity in cache
220 -- result - result of current activity in cache
221 -- NOTE: ### Used by Flex - inform them of any api changes.
222 --
223 procedure LastResult(
224 itemtype in varchar2,
225 itemkey in varchar2,
226 actid out NOCOPY number,
227 status out NOCOPY varchar2,
228 result out NOCOPY varchar2)
229 is
230 begin
231 -- Check that the item matches one in the cache
232 if ((itemtype <> nvl(wf_item_activity_status.c_itemtype, 'x')) or
233 (itemkey <> nvl(wf_item_activity_status.c_itemkey, 'x'))) then
234 Wf_Core.Token('ITEMTYPE', itemtype);
235 Wf_Core.Token('ITEMKEY', itemkey);
236 Wf_Core.Raise('WFENG_SYNCH_ITEM');
237 end if;
238
239 actid := wf_item_activity_status.c_actid;
240 status := wf_item_activity_status.c_status;
241 result := wf_item_activity_status.c_result;
242 exception
243 when others then
244 Wf_Core.Context('Wf_Item_Activity_Status', 'LastResult',
245 itemtype, itemkey);
246 raise;
247 end LastResult;
248
249 --
250 -- Status (PRIVATE)
251 -- Returns the status for this item activity. If there is no row in
252 -- the WF_ITEM_ACTIVITY_STATUSES table, the status out variable will be null.
253 -- IN
254 -- itemtype - Activity item type.
255 -- itemkey - The item key.
256 -- actid - Process activity (instance id).
257 -- OUT
258 -- status - Activity status for this item activity.
259 --
260 procedure Status(itemtype in varchar2,
261 itemkey in varchar2,
262 actid in number,
263 status out NOCOPY varchar2)
264 is
265 begin
266
267 Wf_Item_Activity_Status.InitCache(itemtype, itemkey, actid,
268 ignore_notfound=>TRUE);
269 status := wf_item_activity_status.c_status;
270 return;
271
272 exception
273 when OTHERS then
274 Wf_Core.Context('Wf_Item_Activity_Status', 'Status', itemtype,
275 itemkey, to_char(actid));
276 raise;
277 end Status;
278
279 --
280 -- Result (PRIVATE)
281 -- Returns the status and result for this item activity. If there is no
282 -- row in the WF_ITEM_ACTIVITY_STATUSES table, both status and result
283 -- out variables will be null.
284 -- IN
285 -- itemtype - Activity item type.
286 -- itemkey - The item key.
287 -- actid - Process activity (instance id).
288 -- OUT
289 -- status - Activity status for this item activity.
290 -- result - Activity result for this item activity.
291 --
292 procedure Result(itemtype in varchar2,
293 itemkey in varchar2,
294 actid in number,
295 status out NOCOPY varchar2,
296 result out NOCOPY varchar2) is
297
298 begin
299
300 Wf_Item_Activity_Status.InitCache(itemtype, itemkey, actid,
301 ignore_notfound=>TRUE);
302
303 status := wf_item_activity_status.c_status;
304 result := wf_item_activity_status.c_result;
305 return;
306
307 exception
308 when NO_DATA_FOUND then
309 status := '';
310 result := '';
311 when OTHERS then
312 Wf_Core.Context('Wf_Item_Activity_Status', 'Result', itemtype,
313 itemkey, to_char(actid));
314 raise;
315 end Result;
316
317 --
318 -- Due_Date (PRIVATE)
319 -- Returns the duedate of an activity that will timeout
320 -- IN
321 -- itemtype - Activity item type.
322 -- itemkey - The item key.
323 -- actid - Process activity (instance id).
324 -- RETURNS
325 -- duedate - Date activity will timeout
326 --
327 function Due_Date(
328 itemtype in varchar2,
329 itemkey in varchar2,
330 actid in number)
331 return date
332 is
333 begin
334
335 Wf_Item_Activity_Status.InitCache(itemtype, itemkey, actid);
336 return(wf_item_activity_status.c_duedate);
337
338 exception
339 when others then
340 Wf_Core.Context('Wf_Item_Activity_Status', 'Due_Date', itemtype,
341 itemkey, to_char(actid));
342 raise;
343 end Due_Date;
344
345 --
346 -- Notification_Status (PRIVATE)
347 -- Returns the notification id and assigned user for this item activity.
348 -- If there is no row in the WF_ITEM_ACTIVITY_STATUSES table, the notid
349 -- and user out variables will contain null.
350 -- IN
351 -- itemtype - Activity item type.
352 -- itemkey - The item key.
353 -- actid - Process activity (instance id).
354 -- OUT
355 -- notid - The notification id for this notification activity.
356 -- user - The assigned user for this notification activity.
357 --
358 procedure Notification_Status(itemtype in varchar2,
359 itemkey in varchar2,
360 actid in number,
361 notid out NOCOPY number,
362 user out NOCOPY varchar2)
363 is
364 begin
365
366 Wf_Item_Activity_Status.InitCache(itemtype, itemkey, actid,
367 ignore_notfound=>TRUE);
368 notid := wf_item_activity_status.c_notification_id;
369 user := wf_item_activity_status.c_assigned_user;
370 return;
371
372 exception
373 when OTHERS then
374 Wf_Core.Context('Wf_Item_Activity_Status', 'Notification_Status', itemtype,
375 itemkey, to_char(actid));
376 raise;
377 end Notification_Status;
378
379 --
380 -- Error_Info (PRIVATE)
381 -- Returns all error info for an activity.
382 -- IN
383 -- itemtype - Activity item type.
384 -- itemkey - The item key.
385 -- actid - Process activity (instance id).
386 -- OUT
387 -- errname - Error name
388 -- errmsg - Error message
389 -- errstack - Error stack
390 --
391 procedure Error_Info(itemtype in varchar2,
392 itemkey in varchar2,
393 actid in number,
394 errname out NOCOPY varchar2,
395 errmsg out NOCOPY varchar2,
396 errstack out NOCOPY varchar2)
397 is
398 begin
399
400 Wf_Item_Activity_Status.InitCache(itemtype, itemkey, actid);
401 errname := wf_item_activity_status.c_errname;
402 errmsg := wf_item_activity_status.c_errmsg;
403 errstack := wf_item_activity_status.c_errstack;
404 return;
405
406 exception
407 when NO_DATA_FOUND then
408 Wf_Core.Context('Wf_Item_Activity_Status', 'Error_Info', itemtype,
409 itemkey, to_char(actid));
410 Wf_Core.Token('TYPE', itemtype);
411 Wf_Core.Token('KEY', itemkey);
412 Wf_Core.Token('ACTID', to_char(actid));
413 Wf_Core.Raise('WFENG_ITEM_ACTIVITY_STATUS');
414 when OTHERS then
415 Wf_Core.Context('Wf_Item_Activity_Status', 'Error_Info', itemtype,
416 itemkey, to_char(actid));
417 raise;
418 end Error_Info;
419
420 --
421 -- Set_Error (PRIVATE)
422 -- Set error status and save current error info for this item activity.
423 -- To be called when a client activity function has raised an
424 -- unhandled exception.
425 -- IN
426 -- itemtype - Activity item type
427 -- itemkey - Item Key
428 -- actid - Process activity id
429 -- errcode - Result code of activity
430 -- error_process - Flag if this error is in the error process,
431 -- not in original activity
432 --
433 procedure Set_Error(itemtype in varchar2,
434 itemkey in varchar2,
435 actid in number,
436 errcode in varchar2,
437 error_process in boolean default FALSE)
438 is
439 errname varchar2(30);
440 errmsg varchar2(2000);
441 errstack varchar2(4000);
442 l_errname varchar2(30);
443 l_errmsg varchar2(2000);
444 l_errstack varchar2(4000);
445 prefix varchar2(80);
446 l_exist number(1);
447 begin
448 -- First look for a standard WF_CORE exception.
449 Wf_Core.Get_Error(errname, errmsg, errstack);
450
451 if (errname is null) then
452 -- If no WF_CORE exception, look for an Oracle error.
453 errname := to_char(sqlcode);
454 errmsg := sqlerrm;
455 end if;
456
457 if (error_process) then
458 -- For an error in the error process, append the error info,
459 -- but do NOT change the status or result. Those come from the
460 -- original error.
461
462 -- SYNCHMODE: This should NEVER happen in synchmode, since
463 -- error processes are not allowed.
464 if (itemkey = wf_engine.eng_synch) then
465 wf_core.token('OPERATION',
466 'Wf_Item_Activity_Status.Set_Error(error_process)');
467 wf_core.raise('WFENG_SYNCH_DISABLED');
468 end if;
469
470 prefix := substrb(' ['||Wf_Core.Translate('WFENG_ERR_PROC_ERROR')||': ',
471 1, 80);
472
473 select
474 error_name,error_message,error_stack
475 into l_errname,l_errmsg,l_errstack
476 from WF_ITEM_ACTIVITY_STATUSES
477 where ITEM_TYPE = itemtype
478 and ITEM_KEY = itemkey
479 and PROCESS_ACTIVITY = actid;
480
481 l_errname:= substrb(l_errname||prefix||errname||']', 1, 30);
482 l_errmsg:=substrb(l_errmsg||prefix||errmsg||']', 1, 2000);
483 l_errstack:=substrb(l_errstack||prefix||errstack||']', 1, 4000);
484 update
485 WF_ITEM_ACTIVITY_STATUSES set
486 ERROR_NAME=l_errname,
487 ERROR_MESSAGE = l_errmsg,
488 ERROR_STACK = l_errstack
489 where ITEM_TYPE = itemtype
490 and ITEM_KEY = itemkey
491 and PROCESS_ACTIVITY = actid;
492
493
494 -- Update runtime cache
495 if ((wf_item_activity_status.c_itemtype = itemtype) and
496 (wf_item_activity_status.c_itemkey = itemkey) and
497 (wf_item_activity_status.c_actid = actid)) then
498
499 wf_item_activity_status.c_errname :=
500 substrb(wf_item_activity_status.c_errname||prefix||errname||']',
501 1, 30);
502 wf_item_activity_status.c_errmsg :=
503 substrb(wf_item_activity_status.c_errmsg||prefix||errmsg||']',
504 1, 2000);
505 wf_item_activity_status.c_errstack :=
506 substrb(wf_item_activity_status.c_errstack||prefix||errstack||']',
507 1, 4000);
508
509 end if;
510
511 else
512 -- Update runtime cache
513 if ((wf_item_activity_status.c_itemtype = itemtype) and
514 (wf_item_activity_status.c_itemkey = itemkey) and
515 (wf_item_activity_status.c_actid = actid)) then
516
517 wf_item_activity_status.c_errname := errname;
518 wf_item_activity_status.c_errmsg := errmsg;
519 wf_item_activity_status.c_errstack := errstack;
520 wf_item_activity_status.c_status := wf_engine.eng_error;
521 wf_item_activity_status.c_result := errcode;
522
523 end if;
524
525 -- SYNCHMODE: In synch mode stop after updating internal cache.
526 -- Note the ONLY place this should be called in synchmode
527 -- is function_call or execute_activity if an activity raises
528 -- an unhandled exception.
529 if (itemkey = wf_engine.eng_synch) then
530 return;
531 end if;
532
533 -- Store error info and set status/result
534
535 update
536 WF_ITEM_ACTIVITY_STATUSES set
537 ACTIVITY_STATUS = wf_engine.eng_error,
538 ACTIVITY_RESULT_CODE = errcode,
539 ERROR_NAME = errname,
540 ERROR_MESSAGE = errmsg,
541 ERROR_STACK = errstack
542 where ITEM_TYPE = itemtype
543 and ITEM_KEY = itemkey
544 and PROCESS_ACTIVITY = actid;
545
546
547 end if;
548
549 -- Bug 3960517
550 -- No record was found in WF_ITEM_ACTIVITY_STATUSES. We will check
551 -- the history table. If record exists in WF_ITEM_ACTIVITY_STATUSES_H,
552 -- we may have re-entered a node causing engine to prepare for looping.
553 -- But instead of going through all the activities along the loop, it has
554 -- progressed through a different path. Since there maybe multiple records
555 -- in the history table with the same item type, item key and actid, we may
556 -- not be able to mark the error accordingly, so we just simply ignore the error.
557 if (SQL%NOTFOUND) then
558 select 1 into l_exist
559 from WF_ITEM_ACTIVITY_STATUSES_H
560 where ITEM_TYPE = itemtype
561 and ITEM_KEY= itemkey
562 and process_activity = actid
563 and rownum < 2;
564 --raise NO_DATA_FOUND;
565 end if;
566
567 if (Wf_Engine.Debug) then
568 commit;
569 end if;
570 exception
571 when NO_DATA_FOUND then
572 Wf_Core.Context('Wf_Item_Activity_Status', 'Set_Error',
573 itemtype, itemkey, to_char(actid), errcode);
574 Wf_Core.Token('TYPE', itemtype);
575 Wf_Core.Token('KEY', itemkey);
576 Wf_Core.Token('ACTID', to_char(actid));
577 Wf_Core.Raise('WFENG_ITEM_ACTIVITY_STATUS');
578 when OTHERS then
579 Wf_Core.Context('Wf_Item_Activity_Status', 'Set_Error',
580 itemtype, itemkey, to_char(actid), errcode);
581
582 raise;
583 end Set_Error;
584
585 --
586 -- Delete_Status (PRIVATE)
587 -- Deletes the row for this item activity from WF_ITEM_ACTIVITY_STATUSES.
588 -- IN
589 -- itemtype - Activity item type.
590 -- itemkey - The item key.
591 -- actid - Process activity (instance id).
592 --
593 procedure Delete_Status(itemtype in varchar2,
594 itemkey in varchar2,
595 actid in number)
596 is
597 begin
598 delete
599 from WF_ITEM_ACTIVITY_STATUSES
600 where ITEM_TYPE = itemtype
601 and ITEM_KEY = itemkey
602 and PROCESS_ACTIVITY = actid;
603
604 -- Clear runtime cache if needed
605 if ((wf_item_activity_status.c_itemtype = itemtype) and
606 (wf_item_activity_status.c_itemkey = itemkey) and
607 (wf_item_activity_status.c_actid = actid)) then
608 wf_item_activity_status.c_itemtype := '';
609 wf_item_activity_status.c_itemkey := '';
610 wf_item_activity_status.c_actid := '';
611 end if;
612
613 if (Wf_Engine.Debug) then
614 commit;
615 end if;
616 exception
617 when OTHERS then
618 Wf_Core.Context('Wf_Item_Activity_Status', 'Delete_Status', itemtype,
619 itemkey, to_char(actid));
620 raise;
621 end Delete_Status;
622
623 --
624 -- Create_Status (PRIVATE)
625 -- If this item activity status already exists, then update its
626 -- status, result, begin date and end date.
627 -- Otherwise, create the activity status by inserting a new row into WIAS
628 -- table with the supplied status and result.
629 -- IN
630 -- itemtype - Activity item type.
631 -- itemkey - The item key.
632 -- actid - Process activity (instance id).
633 -- status - Activity status for this item activity.
634 -- result - Activity result for this item activity.
635 -- beginning - Activity begin_date, or null to leave unchanged
636 -- ending - Activity end_date, or null to leave unchanged
637 -- callout - determines if activity is a call outside the database
638 -- suspended - flag only ever set when called from Process_Activity
639 -- when parent is suspended
640 procedure Create_Status(itemtype in varchar2,
641 itemkey in varchar2,
642 actid in number,
643 status in varchar2,
644 result in varchar2,
645 beginning in date,
646 ending in date,
647 suspended in boolean,
648 newStatus in boolean)
649 is
650 root varchar2(30); -- Root process of activity
651 version pls_integer; -- Root process version
652 rootid pls_integer; -- Id of root process
653 act_fname varchar2(240);
654 act_ftype varchar2(30);
655 delay number; -- dont use pls_integer or numeric overflow can occur.
656 msg_id raw(16):=null;
657 l_result number;
658
659 -- Timeout processing stuff
660 duedate date;
661 timeout number;
662 msg varchar2(30);
663 msgtype varchar2(8);
664 expand_role varchar2(8);
665
666 -- status flags
667 l_newStatus boolean default FALSE;
668
669 begin
670 if (itemkey = wf_engine.eng_synch) then
671
672 -- SYNCHMODE: Only update runtime cache.
673 if ((wf_item_activity_status.c_itemtype = itemtype) and
674 (wf_item_activity_status.c_itemkey = itemkey) and
675 (wf_item_activity_status.c_actid = actid)) then
676 -- Existing row. Only update relevant parts
677 wf_item_activity_status.c_status := status;
678 if (result is not null) then
679 wf_item_activity_status.c_result := result;
680 end if;
681 if (beginning is not null) then
682 wf_item_activity_status.c_begindate := beginning;
683 end if;
684 if (ending is not null) then
685 wf_item_activity_status.c_enddate := ending;
686 end if;
687 else
688 -- Fresh new row. Re-initialize everything.
689 wf_item_activity_status.c_itemtype := itemtype;
690 wf_item_activity_status.c_itemkey := itemkey;
691 wf_item_activity_status.c_actid := actid;
692 wf_item_activity_status.c_status := status;
693 wf_item_activity_status.c_result := result;
694 wf_item_activity_status.c_begindate := beginning;
695 wf_item_activity_status.c_enddate := ending;
696 wf_item_activity_status.c_duedate := to_date(NULL);
697 wf_item_activity_status.c_assigned_user := '';
698 wf_item_activity_status.c_notification_id := '';
699 wf_item_activity_status.c_errname := '';
700 wf_item_activity_status.c_errmsg := '';
701 wf_item_activity_status.c_errstack := '';
702 end if;
703
704 else
705 -- NORMAL mode:
706
707 -- TIMEOUT PROCESSING
708 -- Calculate new timeout date if begin_date is being changed.
709 if (beginning is not null) then
710 begin
711 -- 1. Look first for a '#TIMEOUT' NUMBER attribute
712 timeout := Wf_Engine.GetActivityAttrNumber(itemtype, itemkey,
713 actid, wf_engine.eng_timeout_attr,
714 ignore_notfound=>TRUE);
715
716 if (nvl(timeout, 0) <> 0) then
717 -- Figure duedate as offset from begin time.
718 -- NOTE: Default timeout is in units of minutes, not days like
719 -- all other 'date as number' values, thus the 1440 fudge factor.
720 duedate := beginning + (timeout / 1440);
721 else
722 -- 2. Look for a '#TIMEOUT' DATE attribute
723 duedate := Wf_Engine.GetActivityAttrDate(itemtype, itemkey,
724 actid, wf_engine.eng_timeout_attr,
725 ignore_notfound=>TRUE);
726 end if;
727 exception
728 when others then
729 if (wf_core.error_name = 'WFENG_ACTIVITY_ATTR') then
730 -- No #TIMEOUT attr means no timeout
731 wf_core.clear;
732 duedate := null;
733 else
734 raise;
735 end if;
736 end;
737 end if;
738
739 -- DEFERRED QUEUE PROCESSING
740 -- if deferred, insert into the deferred queue
741 -- but not if parent is SUSPENDED or we get infinite loop in queue
742 if create_status.status = wf_engine.eng_deferred
743 and (not create_status.suspended )then
744
745 act_fname:= Wf_Activity.activity_function(itemtype,itemkey,actid);
746 act_ftype:= Wf_Activity.activity_function_type(itemtype,itemkey,actid);
747
748 -- If enqueue fails, only the activity should error and not the root
749 begin
750 if act_ftype = 'PL/SQL' then
751
752 if beginning is null then
753 delay :=0;
754 else
755 delay := round((beginning - sysdate)*24*60*60 + 0.5);
756 end if;
757 wf_queue.enqueue_event
758 (queuename=>wf_queue.DeferredQueue,
759 itemtype=> itemtype,
760 itemkey=>create_status.itemkey,
761 actid=>create_status.actid,
762 delay=>delay,
763 message_handle=>msg_id);
764 -- even when internal, keep message for cross reference.
765 -- msg_id :=null;
766 elsif act_ftype = 'EXTERNAL' then
767 -- this is a callout so write to OUTBOUND queue
768 -- do not set the correlation here for compatibility reason
769 wf_queue.enqueue_event
770 (queuename=>wf_queue.OutboundQueue,
771 itemtype=> create_status.itemtype,
772 itemkey=>create_status.itemkey,
773 actid=>create_status.actid,
774 funcname=>act_fname,
775 paramlist=>wf_queue.get_param_list(itemtype,itemkey,actid),
776 message_handle=>msg_id);
777 else
778 -- this is a callout so write to OUTBOUND queue for other type
779 wf_queue.enqueue_event
780 (queuename=>wf_queue.OutboundQueue,
781 itemtype=> create_status.itemtype,
782 itemkey=>create_status.itemkey,
783 actid=>create_status.actid,
784 correlation=>act_ftype,
785 funcname=>act_fname,
786 paramlist=>wf_queue.get_param_list(itemtype,itemkey,actid),
787 message_handle=>msg_id);
788 end if;
789 exception
790 when others then
791 -- If any error while enqueing, set the activity status to ERROR
792 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, actid,
793 wf_engine.eng_error, wf_engine.eng_exception,
794 sysdate, null, newStatus=>TRUE);
795 Wf_Item_Activity_Status.Set_Error(itemtype, itemkey, actid,
796 wf_engine.eng_exception, FALSE);
797 Wf_Engine_Util.Execute_Error_Process(itemtype, itemkey, actid,
798 wf_engine.eng_exception);
799 return;
800 end;
801 end if;
802
803 -- Increment Counter
804 g_ExecCounter := (g_ExecCounter + 1);
805
806 -- Update the status in db. The execution_time is also reset if:
807 -- 1. Changing status to active
808 -- 2. Changing status to complete/error AND the execution_time is
809 -- not yet set (activity has been aborted without being executed).
810
811 if not (newStatus) then
812 update
813 WF_ITEM_ACTIVITY_STATUSES set
814 ACTIVITY_STATUS = create_status.status,
815 ACTIVITY_RESULT_CODE = nvl(create_status.result, ACTIVITY_RESULT_CODE),
816 BEGIN_DATE = nvl(create_status.beginning, BEGIN_DATE),
817 END_DATE = nvl(create_status.ending, END_DATE),
818 DUE_DATE = decode(create_status.beginning,
819 to_date(NULL), DUE_DATE,
820 create_status.duedate),
821 OUTBOUND_QUEUE_ID = msg_id,
822 EXECUTION_TIME =
823 decode(create_status.status,
824 wf_engine.eng_active, g_ExecCounter,
825 wf_engine.eng_completed, nvl(EXECUTION_TIME, g_ExecCounter),
826 wf_engine.eng_error, nvl(EXECUTION_TIME, g_ExecCounter),
827 EXECUTION_TIME)
828 where ITEM_TYPE = create_status.itemtype
829 and ITEM_KEY = create_status.itemkey
830 and PROCESS_ACTIVITY = create_status.actid;
831 end if;
832
833 -- Create the status if not found
834 if ((newStatus) or (SQL%ROWCOUNT = 0)) then
835 begin
836 insert
837 into WF_ITEM_ACTIVITY_STATUSES (
838 ITEM_TYPE,
839 ITEM_KEY,
840 PROCESS_ACTIVITY,
841 ACTIVITY_STATUS,
842 ACTIVITY_RESULT_CODE,
843 ASSIGNED_USER,
844 NOTIFICATION_ID,
845 BEGIN_DATE,
846 END_DATE,
847 DUE_DATE,
848 EXECUTION_TIME,
849 OUTBOUND_QUEUE_ID
850 ) values (
851 create_status.itemtype,
852 create_status.itemkey,
853 create_status.actid,
854 create_status.status,
855 create_status.result,
856 null,
857 null,
858 create_status.beginning,
859 create_status.ending,
860 create_status.duedate,
861 decode(create_status.status,
862 wf_engine.eng_active, g_ExecCounter,
863 wf_engine.eng_completed, g_ExecCounter,
864 wf_engine.eng_error, g_ExecCounter,
865 null),
866 create_status.msg_id
867 );
868
869 -- Initialize runtime cache with new row
870 wf_item_activity_status.c_itemtype := itemtype;
871 wf_item_activity_status.c_itemkey := itemkey;
872 wf_item_activity_status.c_actid := actid;
873 wf_item_activity_status.c_status := status;
874 wf_item_activity_status.c_result := result;
875 wf_item_activity_status.c_begindate := beginning;
876 wf_item_activity_status.c_enddate := ending;
877 wf_item_activity_status.c_duedate := duedate;
878 wf_item_activity_status.c_assigned_user := '';
879 wf_item_activity_status.c_notification_id := '';
880 wf_item_activity_status.c_errname := '';
881 wf_item_activity_status.c_errmsg := '';
882 wf_item_activity_status.c_errstack := '';
883
884 l_newStatus := TRUE;
885
886 exception
887 when DUP_VAL_ON_INDEX then
888 -- If we are attempting to insert but the record exists, we will then
889 -- automatically update to ensure fault tolerance.
890 l_newStatus := FALSE;
891
892 update
893 WF_ITEM_ACTIVITY_STATUSES set
894 ACTIVITY_STATUS = create_status.status,
895 ACTIVITY_RESULT_CODE = nvl(create_status.result,
896 ACTIVITY_RESULT_CODE),
897 BEGIN_DATE = nvl(create_status.beginning, BEGIN_DATE),
898 END_DATE = nvl(create_status.ending, END_DATE),
899 DUE_DATE = decode(create_status.beginning,
900 to_date(NULL), DUE_DATE,
901 create_status.duedate),
902 OUTBOUND_QUEUE_ID = msg_id,
903 EXECUTION_TIME = decode(create_status.status,
904 wf_engine.eng_active, g_ExecCounter,
905 wf_engine.eng_completed, nvl(EXECUTION_TIME,
906 g_ExecCounter),
907 wf_engine.eng_error, nvl(EXECUTION_TIME,
908 g_ExecCounter),
909 EXECUTION_TIME)
910 where ITEM_TYPE = create_status.itemtype
911 and ITEM_KEY = create_status.itemkey
912 and PROCESS_ACTIVITY = create_status.actid;
913 end;
914 end if;
915
916
917 if (not l_newStatus) then
918 -- Update runtime cache with new data, if current row
919 if ((wf_item_activity_status.c_itemtype = itemtype) and
920 (wf_item_activity_status.c_itemkey = itemkey) and
921 (wf_item_activity_status.c_actid = actid)) then
922 wf_item_activity_status.c_status := status;
923 if (result is not null) then
924 wf_item_activity_status.c_result := result;
925 end if;
926 if (beginning is not null) then
927 wf_item_activity_status.c_begindate := beginning;
928 wf_item_activity_status.c_duedate := duedate;
929 end if;
930 if (ending is not null) then
931 wf_item_activity_status.c_enddate := ending;
932 end if;
933 end if;
934 end if;
935
936 -- If the root process is being marked completed or active,
937 -- then also update the end_date of the item.
938 Wf_Item.Root_Process(itemtype, itemkey, root, version);
939 rootid := Wf_Process_Activity.RootInstanceId(itemtype, itemkey, root);
940 if (actid = rootid) then
941 if (status = wf_engine.eng_completed) then
942 l_result := WF_ITEM.SetEndDate(itemtype, itemkey);
943 elsif (status = wf_engine.eng_active) then
944 UPDATE WF_ITEMS SET
945 END_DATE = to_date(NULL)
946 WHERE ITEM_TYPE = itemtype
947 AND ITEM_KEY = itemkey;
948 end if;
949 end if;
950 end if;
951
952 -- High availability support. creates dependency on datamodel changes
953 -- and WF_HA_MIGRATION package (WFHAMIG[S|B].pls)
954 --if (WF_HA_MIGRATION.GET_CACHED_HA_MAINT_MODE = 'MAINT') then
955 -- WF_HA_MIGRATION.SET_HA_FLAG(itemtype, itemkey);
956 --end if;
957
958 if (Wf_Engine.Debug) then
959 commit;
960 end if;
961 exception
962 when OTHERS then
963 Wf_Core.Context('Wf_Item_Activity_Status', 'Create_Status', itemtype,
964 itemkey, to_char(actid), status, result);
965 raise;
966 end Create_Status;
967
968 --
969 -- Audit (PRIVATE)
970 -- Procedure to update the item activity status record with Audit
971 -- information if the activity was expedited from Status Monitor
972 -- IN
973 -- itemtype - Item Type
974 -- itemkey - Item Key
975 -- actid - Activity Id
976 -- action - Action performed on the activity
977 -- performer - User performed the action
978 procedure Audit(itemtype in varchar2,
979 itemkey in varchar2,
980 actid in number,
981 action in varchar2,
982 performer in varchar2)
983 is
984 l_username varchar2(320);
985 begin
986 -- if the performer is not provided get the current session user
987 if (performer is null) then
988 l_username := wfa_sec.GetUser();
989 else
990 l_username := performer;
991 end if;
992
993 -- update the status record with the action and performer
994 UPDATE wf_item_activity_statuses
995 SET action = Audit.action,
996 performed_by = l_username
997 WHERE item_type = Audit.itemtype
998 AND item_key = Audit.itemkey
999 AND process_activity = Audit.actid;
1000
1001 --Bug 3361746
1002 --The existing handleerror API does not mandatorily require an entry in
1003 --wf_item_activity_statuses table, hence we should not throw an
1004 --exception if no row is found for the activity.
1005
1006 exception
1007 when others then
1008 Wf_Core.Context('Wf_Item_Activity_Status', 'Audit', itemtype, itemkey, to_char(actid));
1009 raise;
1010 end Audit;
1011
1012 -- 3966635 Workflow Provisioning Project
1013 -- The following is added in order not to loose the changes required.
1014 -- --
1015 -- -- Update_Prov_Request (PRIVATE)
1016 -- -- Procedure to update the item activity status record with Provision Request Id
1017 -- -- IN
1018 -- -- itemtype - Item Type
1019 -- -- itemkey - Item Key
1020 -- -- actid - Activity Id
1021 -- -- prov_request_id - Provision request id
1022 -- procedure Update_Prov_Request(itemtype in varchar2,
1023 -- itemkey in varchar2,
1024 -- actid in number,
1025 -- prov_request_id in number)
1026 -- is
1027 -- begin
1028 --
1029 -- UPDATE wf_item_activity_statuses
1030 -- SET prov_request_id = Update_Prov_Request.prov_request_id
1031 -- WHERE item_type = Update_Prov_Request.itemtype
1032 -- AND item_key = Update_Prov_Request.itemkey
1033 -- AND process_activity = Update_Prov_Request.actid;
1034 --
1035 -- exception
1036 -- when others then
1037 -- Wf_Core.Context('Wf_Item_Activity_Status', 'Update_Prov_Request', itemtype, itemkey, to_char(actid));
1038 -- raise;
1039 -- end Update_Prov_Request;
1040 --
1041 end WF_ITEM_ACTIVITY_STATUS;