1 package body WF_ENGINE as
2 /* $Header: wfengb.pls 120.38.12020000.3 2012/11/13 19:42:45 alsosa ship $ */
3
4 type InstanceArrayTyp is table of pls_integer
5 index by binary_integer;
6
7 type RowidArrayTyp is table of rowid
8 index by binary_integer;
9
10 --
11 -- Exception
12 --
13 no_savepoint exception;
14 bad_format exception; --<rwunderl:2307104/>
15
16 pragma EXCEPTION_INIT(no_savepoint, -1086);
17 pragma EXCEPTION_INIT(bad_format, -6502); --<rwunderl:2307104/>
18
19 -- Bug 2607770
20 resource_busy exception;
21 pragma exception_init(resource_busy, -00054);
22
23 -- private variable
24 schema varchar2(30);
25
26 -- Bug 3824367
27 -- Optimizing the code using a single cursor with binds
28 cursor curs_activityattr (c_actid NUMBER, c_aname VARCHAR2) is
29 select WAAV.PROCESS_ACTIVITY_ID, WAAV.NAME, WAAV.VALUE_TYPE,
30 WAAV.TEXT_VALUE, WAAV.NUMBER_VALUE, WAAV.DATE_VALUE
31 from WF_ACTIVITY_ATTR_VALUES WAAV
32 where WAAV.PROCESS_ACTIVITY_ID = c_actid
33 and WAAV.NAME = c_aname;
34
35 --
36 -- Current_Schema (PRIVATE)
37 -- Return the current schema
38 --
39 function Current_Schema
40 return varchar2
41 is
42 begin
43 if (wf_engine.schema is null) then
44 select sys_context('USERENV','CURRENT_SCHEMA')
45 into wf_engine.schema
46 from sys.dual;
47 end if;
48 return wf_engine.schema;
49 exception
50 when OTHERS then
51 Wf_Core.Context('Wf_Engine', 'Current_Schema');
52 raise;
53 end Current_Schema;
54
55 --
56 -- AddItemAttr (PUBLIC)
57 -- Add a new unvalidated run-time item attribute.
58 -- IN:
59 -- itemtype - item type
60 -- itemkey - item key
61 -- aname - attribute name
62 -- text_value - add text value to it if provided.
63 -- number_value - add number value to it if provided.
64 -- date_value - add date value to it if provided.
65 -- NOTE:
66 -- The new attribute has no type associated. Get/set usages of the
67 -- attribute must insure type consistency.
68 --
69 procedure AddItemAttr(itemtype in varchar2,
70 itemkey in varchar2,
71 aname in varchar2,
72 text_value in varchar2,
73 number_value in number,
74 date_value in date)
75 is
76
77 wiavIND NUMBER;
78 iStatus PLS_INTEGER;
79
80 begin
81 -- Check Arguments
82 if ((itemtype is null) or
83 (itemkey is null) or
84 (aname is null)) then
85 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
86 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
87 Wf_Core.Token('ANAME', nvl(aname, 'NULL'));
88 Wf_Core.Raise('WFSQL_ARGS');
89
90 -- Insure this is a valid item
91 elsif (not Wf_Item.Item_Exist(itemtype, itemkey)) then
92 Wf_Core.Token('TYPE', itemtype);
93 Wf_Core.Token('KEY', itemkey);
94 Wf_Core.Raise('WFENG_ITEM');
95
96 end if;
97
98 if (itemkey = wf_engine.eng_synch) then
99 --ItemAttrValues are indexed on the hash value of the name.
100 --ItemKey is not used here because we are in #SYNCH mode.
101 WF_CACHE.GetItemAttrValue(itemtype, itemKey, aname, iStatus, wiavIND);
102
103 WF_CACHE.ItemAttrValues(wiavIND).ITEM_TYPE := itemtype;
104 WF_CACHE.ItemAttrValues(wiavIND).ITEM_KEY := itemKey;
105 WF_CACHE.ItemAttrValues(wiavIND).NAME := aname;
106 WF_CACHE.ItemAttrValues(wiavIND).TEXT_VALUE := text_value;
107 WF_CACHE.ItemAttrValues(wiavIND).NUMBER_VALUE := number_value;
108 WF_CACHE.ItemAttrValues(wiavIND).DATE_VALUE := date_value;
109
110 else
111 insert into WF_ITEM_ATTRIBUTE_VALUES (
112 ITEM_TYPE,
113 ITEM_KEY,
114 NAME,
115 TEXT_VALUE,
116 NUMBER_VALUE,
117 DATE_VALUE
118 ) values (
119 itemtype,
120 itemkey,
121 aname,
122 AddItemAttr.text_value,
123 AddItemAttr.number_value,
124 AddItemAttr.date_value
125 );
126 end if;
127
128 exception
129 when dup_val_on_index then
130 Wf_Core.Context('Wf_Engine', 'AddItemAttr', itemtype, itemkey, aname);
131 Wf_Core.Token('TYPE', itemtype);
132 Wf_Core.Token('KEY', itemkey);
133 Wf_Core.Token('ATTRIBUTE', aname);
134 Wf_Core.Raise('WFENG_ITEM_ATTR_UNIQUE');
135 when others then
136 Wf_Core.Context('Wf_Engine', 'AddItemAttr', itemtype, itemkey, aname);
137 raise;
138 end AddItemAttr;
139
140 --
141 -- AddItemAttrTextArray (PUBLIC)
142 -- Add an array of new unvalidated run-time item attributes of type text.
143 -- IN:
144 -- itemtype - item type
145 -- itemkey - item key
146 -- aname - Array of Names
147 -- avalue - Array of New values for attribute
148 -- NOTE:
149 -- The new attributes have no type associated. Get/set usages of these
150 -- attributes must insure type consistency.
151 --
152 procedure AddItemAttrTextArray(
153 itemtype in varchar2,
154 itemkey in varchar2,
155 aname in Wf_Engine.NameTabTyp,
156 avalue in Wf_Engine.TextTabTyp)
157 is
158 iStatus pls_integer;
159 wiavIND pls_integer;
160 arrayIndex pls_integer;
161
162 success_cnt pls_integer;
163
164 begin
165 -- Check Arguments
166 if ((itemtype is null) or
167 (itemkey is null)) then
168 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
169 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
170 Wf_Core.Raise('WFSQL_ARGS');
171
172 -- Insure this is a valid item and validate that the text array
173 -- tables passed in are in proper order.
174
175 elsif (not Wf_Item.Item_Exist(itemtype, itemkey)) then
176 Wf_Core.Token('TYPE', itemtype);
177 Wf_Core.Token('KEY', itemkey);
178 Wf_Core.Raise('WFENG_ITEM');
179
180 elsif (aname.COUNT = 0 or avalue.COUNT = 0) then
181 -- Do not do anything if index table is empty.
182 return;
183
184 elsif (aname.LAST <> avalue.LAST or aname.COUNT <> avalue.COUNT) then
185 -- Raise an error if the two index tables do not end at the same index
186 -- or do not have the same number of elements.
187 Wf_Core.Raise('WFENG_ITEM_ATTR_ARRAY_MISMATCH');
188
189 end if;
190
191 -- Check to see if we are in synch mode and use WF_CACHE.
192
193 success_cnt := 0;
194 if (itemkey = wf_engine.eng_synch) then
195 -- Use WF_CACHE.ItemAttrValues for #SYNCH mode.
196 for arrayIndex in aname.FIRST..aname.LAST loop
197 -- first check duplicate attribute name
198 WF_CACHE.GetItemAttrValue( itemType, itemKey, aname(arrayIndex), iStatus,
199 wiavIND);
200
201 if (iStatus = WF_CACHE.task_SUCCESS) then
202 null; --There is already an attribute in cache, so we will try to
203 --load the rest, then raise a dup_val_on_index after we
204 --complete.
205 else
206 WF_CACHE.ItemAttrValues(wiavIND).ITEM_TYPE := itemtype;
207 WF_CACHE.ItemAttrValues(wiavIND).ITEM_KEY := itemKey;
208 WF_CACHE.ItemAttrValues(wiavIND).NAME := aname(arrayIndex);
209 WF_CACHE.ItemAttrValues(wiavIND).TEXT_VALUE := avalue(arrayIndex);
210
211 success_cnt := success_cnt + 1;
212
213 end if;
214
215 end loop;
216 else
217 forall arrayIndex in aname.FIRST..aname.LAST
218 insert into WF_ITEM_ATTRIBUTE_VALUES (
219 ITEM_TYPE,
220 ITEM_KEY,
221 NAME,
222 TEXT_VALUE
223 ) values (
224 itemtype,
225 itemkey,
226 aname(arrayIndex),
227 avalue(arrayIndex)
228 );
229
230 success_cnt := SQL%ROWCOUNT;
231 if (success_cnt <> aname.COUNT) then
232 raise dup_val_on_index;
233 end if;
234 end if;
235
236 exception
237 when dup_val_on_index then
238 Wf_Core.Context('Wf_Engine', 'AddItemAttrTextArray', itemtype, itemkey);
239 Wf_Core.Token('TYPE', itemtype);
240 Wf_Core.Token('KEY', itemkey);
241 Wf_Core.Token('TOTAL', to_char(aname.COUNT));
242 Wf_Core.Token('SUCCESS', to_char(success_cnt));
243 Wf_Core.Raise('WFENG_ITEM_ATTR_ARRAY');
244 when others then
245 Wf_Core.Context('Wf_Engine', 'AddItemAttrTextArray', itemtype, itemkey);
246 raise;
247 end AddItemAttrTextArray;
248
249 --
250 -- AddItemAttrNumberArray (PUBLIC)
251 -- Add an array of new unvalidated run-time item attributes of type number.
252 -- IN:
253 -- itemtype - item type
254 -- itemkey - item key
255 -- aname - Array of Names
256 -- avalue - Array of New values for attribute
257 -- NOTE:
258 -- The new attributes have no type associated. Get/set usages of these
259 -- attributes must insure type consistency.
260 --
261 procedure AddItemAttrNumberArray(
262 itemtype in varchar2,
263 itemkey in varchar2,
264 aname in Wf_Engine.NameTabTyp,
265 avalue in Wf_Engine.NumTabTyp)
266 is
267 arrayIndex pls_integer;
268 iStatus pls_integer;
269 wiavIND NUMBER;
270 success_cnt number;
271 begin
272 -- Check Arguments
273 if ((itemtype is null) or
274 (itemkey is null)) then
275 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
276 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
277 Wf_Core.Raise('WFSQL_ARGS');
278
279 -- Insure this is a valid item, and that the attribute arrays are
280 -- matching and in the proper form.
281 elsif (not Wf_Item.Item_Exist(itemtype, itemkey)) then
282 Wf_Core.Token('TYPE', itemtype);
283 Wf_Core.Token('KEY', itemkey);
284 Wf_Core.Raise('WFENG_ITEM');
285
286 elsif (aname.COUNT = 0 or avalue.COUNT = 0) then
287 -- Do not do anything if index table is empty.
288 return;
289
290 elsif (aname.LAST <> avalue.LAST or aname.COUNT <> avalue.COUNT) then
291 -- Raise an error if the two index tables do not end at the same index
292 -- or do not have the same number of elements.
293 Wf_Core.Raise('WFENG_ITEM_ATTR_ARRAY_MISMATCH');
294
295 end if;
296
297 success_cnt := 0;
298 --If we are in #SYNCH mode, we will go ahead and use WF_CACHE to
299 --Store the attributes.
300 if (itemkey = wf_engine.eng_synch) then
301 for arrayIndex in aname.FIRST..aname.LAST loop
302 -- first check duplicate attribute name
303 WF_CACHE.GetItemAttrValue(itemtype, itemKey, aname(arrayIndex), iStatus,
304 wiavIND);
305
306 if (iStatus = WF_CACHE.task_SUCCESS) then
307 null; --Proceed and attempt to add the rest before raising
308 --dup_val_on_index.
309 else
310 WF_CACHE.ItemAttrValues(wiavIND).ITEM_TYPE := itemtype;
311 WF_CACHE.ItemAttrValues(wiavIND).ITEM_KEY := itemKey;
312 WF_CACHE.ItemAttrValues(wiavIND).NAME := aname(arrayIndex);
313 WF_CACHE.ItemAttrValues(wiavIND).NUMBER_VALUE := avalue(arrayIndex);
314 success_cnt := success_cnt + 1;
315 end if;
316 end loop;
317 else
318 forall arrayIndex in aname.FIRST..aname.LAST
319 insert into WF_ITEM_ATTRIBUTE_VALUES (
320 ITEM_TYPE,
321 ITEM_KEY,
322 NAME,
323 NUMBER_VALUE
324 ) values (
325 itemtype,
326 itemkey,
327 aname(arrayIndex),
328 avalue(arrayIndex)
329 );
330
331 success_cnt := SQL%ROWCOUNT;
332 if (success_cnt <> aname.COUNT) then
333 raise dup_val_on_index;
334 end if;
335 end if;
336
337 exception
338 when dup_val_on_index then
339 Wf_Core.Context('Wf_Engine', 'AddItemAttrNumberArray', itemtype, itemkey);
340 Wf_Core.Token('TYPE', itemtype);
341 Wf_Core.Token('KEY', itemkey);
342 Wf_Core.Token('TOTAL', to_char(aname.COUNT));
343 Wf_Core.Token('SUCCESS', to_char(success_cnt));
344 Wf_Core.Raise('WFENG_ITEM_ATTR_ARRAY');
345 when others then
346 Wf_Core.Context('Wf_Engine', 'AddItemAttrNumberArray', itemtype, itemkey);
347 raise;
348 end AddItemAttrNumberArray;
349
350 --
351 -- AddItemAttrDateArray (PUBLIC)
352 -- Add an array of new unvalidated run-time item attributes of type date.
353 -- IN:
354 -- itemtype - item type
355 -- itemkey - item key
356 -- aname - Array of Names
357 -- avalue - Array of New values for attribute
358 -- NOTE:
359 -- The new attributes have no type associated. Get/set usages of these
360 -- attributes must insure type consistency.
361 --
362 procedure AddItemAttrDateArray(
363 itemtype in varchar2,
364 itemkey in varchar2,
365 aname in Wf_Engine.NameTabTyp,
366 avalue in Wf_Engine.DateTabTyp)
367 is
368 iStatus pls_integer;
369 wiavIND number;
370 success_cnt number;
371
372 begin
373 -- Check Arguments
374 if ((itemtype is null) or
375 (itemkey is null)) then
376 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
377 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
378 Wf_Core.Raise('WFSQL_ARGS');
379 end if;
380
381 -- Insure this is a valid item, and the array tables match and are
382 -- in proper form.
383
384 if (not Wf_Item.Item_Exist(itemtype, itemkey)) then
385 Wf_Core.Token('TYPE', itemtype);
386 Wf_Core.Token('KEY', itemkey);
387 Wf_Core.Raise('WFENG_ITEM');
388
389 elsif (aname.COUNT = 0 or avalue.COUNT = 0) then
390 -- Do not do anything if index table is empty.
391 return;
392
393 elsif (aname.LAST <> avalue.LAST or aname.COUNT <> avalue.COUNT) then
394 -- Raise an error if the two index tables do not end at the same index
395 -- or do not have the same number of elements.
396 Wf_Core.Raise('WFENG_ITEM_ATTR_ARRAY_MISMATCH');
397 end if;
398
399 success_cnt := 0;
400 -- If in #SYNCH mode, we will use WF_CACHE to store the attributes.
401 if (itemkey = wf_engine.eng_synch) then
402 for arrayIndex in aname.FIRST..aname.LAST loop
403 WF_CACHE.GetItemAttrValue(itemtype, itemKey, aname(arrayIndex), iStatus,
404 wiavIND);
405
406 if (iStatus = WF_CACHE.task_SUCCESS) then
407 null; --Attempt to add the rest before raising the dup_val_on_index
408 else
409 WF_CACHE.ItemAttrValues(wiavIND).ITEM_TYPE := itemtype;
410 WF_CACHE.ItemAttrValues(wiavIND).ITEM_KEY := itemKey;
411 WF_CACHE.ItemAttrValues(wiavIND).NAME := aname(arrayIndex);
412 WF_CACHE.ItemAttrValues(wiavIND).DATE_VALUE := avalue(arrayIndex);
413 success_cnt := success_cnt + 1;
414 end if;
415 end loop;
416 else
417 forall arrayIndex in aname.FIRST..aname.LAST
418 insert into WF_ITEM_ATTRIBUTE_VALUES (
419 ITEM_TYPE,
420 ITEM_KEY,
421 NAME,
422 DATE_VALUE
423 ) values (
424 itemtype,
425 itemkey,
426 aname(arrayIndex),
427 avalue(arrayIndex)
428 );
429
430 success_cnt := SQL%ROWCOUNT;
431 if (success_cnt <> aname.COUNT) then
432 raise dup_val_on_index;
433 end if;
434 end if;
435
436 exception
437 when dup_val_on_index then
438 Wf_Core.Context('Wf_Engine', 'AddItemAttrDateArray', itemtype, itemkey);
439 Wf_Core.Token('TYPE', itemtype);
440 Wf_Core.Token('KEY', itemkey);
441 Wf_Core.Token('TOTAL', to_char(aname.COUNT));
442 Wf_Core.Token('SUCCESS', to_char(success_cnt));
443 Wf_Core.Raise('WFENG_ITEM_ATTR_ARRAY');
444 when others then
445 Wf_Core.Context('Wf_Engine', 'AddItemAttrDateArray', itemtype, itemkey);
446 raise;
447 end AddItemAttrDateArray;
448
449 --
450 -- SetItemAttrText (PUBLIC)
451 -- Set the value of a text item attribute.
452 -- If the attribute is a NUMBER or DATE type, then translate the
453 -- text-string value to a number/date using attribute format.
454 -- For all other types, store the value directly.
455 -- IN:
456 -- itemtype - Item type
457 -- itemkey - Item key
458 -- aname - Attribute Name
459 -- avalue - New value for attribute
460 --
461 procedure SetItemAttrText(itemtype in varchar2,
462 itemkey in varchar2,
463 aname in varchar2,
464 avalue in varchar2)
465 is
466
467 tvalue varchar2(4000);
468 nvalue number;
469 dvalue date;
470 i pls_integer;
471
472 status PLS_INTEGER;
473 wiaIND NUMBER;
474 wiavIND NUMBER;
475
476 role_info_tbl wf_directory.wf_local_roles_tbl_type;
477
478 begin
479 -- Check Arguments
480 if ((itemtype is null) or
481 (itemkey is null) or
482 (aname is null)) then
483 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
484 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
485 Wf_Core.Token('ANAME', nvl(aname, 'NULL'));
486 Wf_Core.Raise('WFSQL_ARGS');
487 end if;
488
489 -- Get type and format of attr.
490 -- This is used for translating number/date strings.
491
492 WF_CACHE.GetItemAttribute(itemtype, aname, status, wiaIND);
493
494 if (status <> WF_CACHE.task_SUCCESS) then
495 begin
496 select WIA.ITEM_TYPE, WIA.NAME, WIA.TYPE, WIA.SUBTYPE, WIA.FORMAT,
497 WIA.TEXT_DEFAULT, WIA.NUMBER_DEFAULT, WIA.DATE_DEFAULT
498 into WF_CACHE.ItemAttributes(wiaIND)
499 from WF_ITEM_ATTRIBUTES WIA
500 where WIA.ITEM_TYPE = itemtype
501 and WIA.NAME = aname;
502
503 exception
504 when no_data_found then
505 -- This is an unvalidated runtime attr.
506 -- Treat it as a varchar2.
507 WF_CACHE.ItemAttributes(wiaIND).ITEM_TYPE := itemtype;
508 WF_CACHE.ItemAttributes(wiaIND).NAME := aname;
509 WF_CACHE.ItemAttributes(wiaIND).TYPE := 'VARCHAR2';
510 WF_CACHE.ItemAttributes(wiaIND).SUBTYPE := '';
511 WF_CACHE.ItemAttributes(wiaIND).FORMAT := '';
512
513 end;
514 end if;
515
516 -- Update attribute value in appropriate type column.
517 if (WF_CACHE.ItemAttributes(wiaIND).TYPE = 'NUMBER') then
518
519 if (WF_CACHE.ItemAttributes(wiaIND).FORMAT is null) then
520 nvalue := to_number(avalue);
521 else
522 nvalue := to_number(avalue, WF_CACHE.ItemAttributes(wiaIND).FORMAT);
523 end if;
524 Wf_Engine.SetItemAttrNumber(itemtype, itemkey, aname, nvalue);
525
526 elsif (WF_CACHE.ItemAttributes(wiaIND).TYPE = 'DATE') then
527
528 if (WF_CACHE.ItemAttributes(wiaIND).FORMAT is null) then
529 dvalue := to_date(avalue,SYS_CONTEXT('USERENV','NLS_DATE_FORMAT'));
530 else
531 dvalue := to_date(avalue, WF_CACHE.ItemAttributes(wiaIND).FORMAT);
532 end if;
533 Wf_Engine.SetItemAttrDate(itemtype, itemkey, aname, dvalue);
534
535 else -- One of the text values
536
537 if (WF_CACHE.ItemAttributes(wiaIND).TYPE = 'VARCHAR2') then
538 -- VARCHAR2 type. Truncate value as necessary
539 if (WF_CACHE.ItemAttributes(wiaIND).FORMAT is null) then
540 tvalue := avalue;
541 else
542 tvalue := substr(avalue, 1,
543 to_number(WF_CACHE.ItemAttributes(wiaIND).FORMAT));
544 end if;
545 elsif (WF_CACHE.ItemAttributes(wiaIND).TYPE = 'ROLE') then
546 -- ROLE type. Decode to internal name.
547 if (avalue is null) then
548 -- Null role values are ok
549 tvalue := '';
550 else
551 -- First check if value is internal name
552 Wf_Directory.GetRoleInfo2(avalue,role_info_tbl);
553 tvalue := role_info_tbl(1).name;
554 -- If not internal name, check for display_name
555 if (tvalue is null) then
556 begin
557 SELECT name
558 INTO tvalue
559 FROM wf_role_lov_vl
560 WHERE upper(display_name) = upper(avalue)
561 AND rownum = 1;
562 exception
563 when no_data_found then
564 -- Not displayed or internal role name, error
565 wf_core.token('ROLE', avalue);
566 wf_core.raise('WFNTF_ROLE');
567 end;
568 end if;
569 end if;
570 else
571
572 -- LOOKUP, FORM, URL, DOCUMENT, misc type.
573 -- Use value directly.
574 tvalue := avalue;
575 end if;
576
577 -- Set the text value.
578 if (itemkey = wf_engine.eng_synch) then
579 -- Use WF_CACHE in synch mode
580 WF_CACHE.GetItemAttrValue(itemtype, itemKey, aname, status, wiavIND);
581
582 if (status <> WF_CACHE.task_SUCCESS) then
583 raise no_data_found;
584
585 else
586 WF_CACHE.ItemAttrValues(wiavIND).TEXT_VALUE := tvalue;
587
588 end if;
589
590 else
591 update WF_ITEM_ATTRIBUTE_VALUES set
592 TEXT_VALUE = tvalue
593 where ITEM_TYPE = itemtype
594 and ITEM_KEY = itemkey
595 and NAME = aname;
596
597 if (SQL%NOTFOUND) then
598 -- ondemand logic.
599 if wf_item.Attribute_ON_Demand(itemtype, itemkey) then
600 --we need to check if the item attribute is defined or not
601 --if attribute value is not defined at design time, we raise an exception
602 insert into WF_ITEM_ATTRIBUTE_VALUES (ITEM_TYPE, ITEM_KEY, NAME, TEXT_VALUE)
603 select
604 SetItemAttrText.itemtype,
605 SetItemAttrText.itemkey,
606 SetItemAttrText.aname,
607 SetItemAttrText.avalue
608 from WF_ITEM_ATTRIBUTES WIA
609 where WIA.ITEM_TYPE = SetItemAttrText.itemtype
610 and WIA.NAME = SetItemAttrText.aname;
611
612 if SQL%NOTFOUND then
613 raise no_data_found;
614 end if;
615 else
616 raise no_data_found;
617 end if;
618 end if;
619 end if;
620 end if;
621
622 exception
623 when no_data_found then
624 Wf_Core.Context('Wf_Engine', 'SetItemAttrText', itemtype, itemkey,
625 aname, avalue);
626 Wf_Core.Token('TYPE', itemtype);
627 Wf_Core.Token('KEY', itemkey);
628 Wf_Core.Token('ATTRIBUTE', aname);
629 Wf_Core.Raise('WFENG_ITEM_ATTR');
630
631 when bad_format then --<rwunderl:2307104/>
632 Wf_Core.Context('Wf_Engine', 'SetItemAttrText', itemtype, itemkey,
633 aname, avalue);
634 Wf_Core.Token('VALUE', avalue);
635 Wf_Core.Token('TYPE', WF_CACHE.ItemAttributes(wiaIND).TYPE);
636
637 if (WF_CACHE.ItemAttributes(wiaIND).FORMAT is not null) then
638 WF_CORE.Token('FORMAT', '('||WF_CACHE.ItemAttributes(wiaIND).FORMAT||')');
639
640 else
641 WF_CORE.Token('FORMAT', '');
642
643 end if;
644
645 Wf_Core.Raise('WFENG_BAD_FORMAT');
646
647 when others then
648 Wf_Core.Context('Wf_Engine', 'SetItemAttrText', itemtype, itemkey,
649 aname, avalue);
650 raise;
651 end SetItemAttrText;
652
653 --
654 -- SetItemAttrText2 (PRIVATE)
655 -- Set the value of a text item attribute.
656 -- USE ONLY WITH VARCHAR2 VALUES.
657 --
658 -- IN:
659 -- p_itemtype - Item type
660 -- p_itemkey - Item key
661 -- p_aname - Attribute Name
662 -- p_avalue - New value for attribute
663 -- RETURNS:
664 -- boolean
665 --
666 function SetItemAttrText2(p_itemtype in varchar2,
667 p_itemkey in varchar2,
668 p_aname in varchar2,
669 p_avalue in varchar2) return boolean
670 is
671 status PLS_INTEGER;
672 wiavIND NUMBER;
673 nvalue NUMBER;
674 begin
675 -- Check Arguments
676 if ((p_itemtype is null) or
677 (p_itemkey is null) or
678 (p_aname is null)) then
679 Wf_Core.Token('p_itemtype', nvl(p_itemtype, 'NULL'));
680 Wf_Core.Token('p_itemkey', nvl(p_itemkey, 'NULL'));
681 Wf_Core.Token('p_aname', nvl(p_aname, 'NULL'));
682 Wf_Core.Raise('WFSQL_ARGS');
683 end if;
684
685 -- Set the text value.
686 if (p_itemkey = wf_engine.eng_synch) then
687 -- Use WF_CACHE in synch mode
688 WF_CACHE.GetItemAttrValue(p_itemtype, p_itemkey, p_aname, status, wiavIND);
689
690 if (status <> WF_CACHE.task_SUCCESS) then
691 return FALSE;
692
693 else
694 WF_CACHE.ItemAttrValues(wiavIND).TEXT_VALUE := p_avalue;
695 return TRUE;
696 end if;
697
698 else
699 update WF_ITEM_ATTRIBUTE_VALUES set
700 TEXT_VALUE = p_avalue
701 where ITEM_TYPE = p_itemtype
702 and ITEM_KEY = p_itemkey
703 and NAME = p_aname;
704
705 if (SQL%NOTFOUND) then
706 -- ondemand logic
707 if wf_item.Attribute_ON_Demand(p_itemtype, p_itemkey) then
708 --
709 --we need to check if the item attribute is defined or not
710 --if attribute value is not defined at design time, we raise an exception
711 --
712 insert into WF_ITEM_ATTRIBUTE_VALUES (ITEM_TYPE, ITEM_KEY, NAME, TEXT_VALUE)
713 select
714 SetItemAttrText2.p_itemtype,
715 SetItemAttrText2.p_itemkey,
716 SetItemAttrText2.p_aname,
717 SetItemAttrText2.p_avalue
718 from WF_ITEM_ATTRIBUTES WIA
719 where WIA.ITEM_TYPE = SetItemAttrText2.p_itemtype
720 and WIA.NAME = SetItemAttrText2.p_aname;
721
722 if SQL%NOTFOUND then
723 raise no_data_found;
724 end if;
725 return TRUE;
726 else
727 return FALSE;
728 end if;
729 else
730 return TRUE;
731 end if;
732 end if;
733
734 exception
735 when no_data_found then
736 return FALSE;
737
738 when others then
739 Wf_Core.Context('Wf_Engine', 'SetItemAttrText2', p_itemtype, p_itemkey,
740 p_aname, p_avalue);
741 raise;
742 end SetItemAttrText2;
743
744 --
745 -- SetEventItemAttr (PRIVATE)
746 -- Set the value of an event item attribute.
747 -- If the attribute is a NUMBER or DATE type, then translate the
748 -- text-string value to a number/date using attribute format.
749 -- For all other types, store the value directly.
750 -- IN:
751 -- itemtype - Item type
752 -- itemkey - Item key
753 -- aname - Attribute Name
754 -- avalue - New value for attribute
755 --
756 procedure SetEventItemAttr(itemtype in varchar2,
757 itemkey in varchar2,
758 aname in varchar2,
759 avalue in varchar2)
760 is
761
762 nvalue number;
763 dvalue date;
764
765 wiaIND NUMBER;
766 status PLS_INTEGER;
767
768 begin
769 -- Get type and format of attr.
770 -- This is used for translating number/date strings.
771 WF_CACHE.GetItemAttribute(itemtype, aname, status, wiaIND);
772
773 if (status <> WF_CACHE.task_SUCCESS) then
774 begin
775 select WIA.ITEM_TYPE, WIA.NAME, WIA.TYPE, WIA.SUBTYPE, WIA.FORMAT,
776 WIA.TEXT_DEFAULT, WIA.NUMBER_DEFAULT, WIA.DATE_DEFAULT
777 into WF_CACHE.ItemAttributes(wiaIND)
778 from WF_ITEM_ATTRIBUTES WIA
779 where WIA.ITEM_TYPE = itemtype
780 and WIA.NAME = aname;
781
782 exception
783 when no_data_found then
784 -- This is an unvalidated runtime attr.
785 -- Treat it as a varchar2.
786 WF_CACHE.ItemAttributes(wiaIND).ITEM_TYPE := itemtype;
787 WF_CACHE.ItemAttributes(wiaIND).NAME := aname;
788 WF_CACHE.ItemAttributes(wiaIND).TYPE := 'VARCHAR2';
789 WF_CACHE.ItemAttributes(wiaIND).SUBTYPE := '';
790 WF_CACHE.ItemAttributes(wiaIND).FORMAT := '';
791
792 end;
793 end if;
794
795 -- Update attribute value in appropriate type column.
796 if (WF_CACHE.ItemAttributes(wiaIND).TYPE = 'NUMBER') then
797
798 if (WF_CACHE.ItemAttributes(wiaIND).FORMAT is null) then
799 nvalue := to_number(avalue, wf_core.canonical_number_mask);
800 else
801 nvalue := to_number(avalue, WF_CACHE.ItemAttributes(wiaIND).FORMAT);
802 end if;
803 Wf_Engine.SetItemAttrNumber(itemtype, itemkey, aname, nvalue);
804
805 elsif (WF_CACHE.ItemAttributes(wiaIND).TYPE = 'DATE') then
806
807 if (WF_CACHE.ItemAttributes(wiaIND).FORMAT is null) then
808 dvalue := to_date(avalue, wf_core.canonical_date_mask);
809 else
810 dvalue := to_date(avalue, WF_CACHE.ItemAttributes(wiaIND).FORMAT);
811 end if;
812 Wf_Engine.SetItemAttrDate(itemtype, itemkey, aname, dvalue);
813
814 else -- One of the text values
815
816 Wf_Engine.SetItemAttrText(itemtype, itemkey, aname, avalue);
817
818 end if;
819
820 exception
821 when no_data_found then
822 Wf_Core.Context('Wf_Engine', 'SetEventItemAttr', itemtype, itemkey,
823 aname, avalue);
824 Wf_Core.Token('TYPE', itemtype);
825 Wf_Core.Token('KEY', itemkey);
826 Wf_Core.Token('ATTRIBUTE', aname);
827 Wf_Core.Raise('WFENG_ITEM_ATTR');
828
829 when others then
830 Wf_Core.Context('Wf_Engine', 'SetEventItemAttr', itemtype, itemkey,
831 aname, avalue);
832 raise;
833 end SetEventItemAttr;
834
835 --
836 -- SetItemAttrNumber (PUBLIC)
837 -- Set the value of a number item attribute.
838 -- Attribute must be a NUMBER-type attribute.
839 -- IN:
840 -- itemtype - Item type
841 -- itemkey - Item key
842 -- aname - Attribute Name
843 -- avalue - New value for attribute
844 --
845 procedure SetItemAttrNumber(itemtype in varchar2,
846 itemkey in varchar2,
847 aname in varchar2,
848 avalue in number)
849 is
850 iStatus PLS_INTEGER;
851 wiavIND NUMBER;
852 nvalue NUMBER;
853 begin
854 -- Check Arguments
855 if ((itemtype is null) or
856 (itemkey is null) or
857 (aname is null)) then
858 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
859 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
860 Wf_Core.Token('ANAME', nvl(aname, 'NULL'));
861 Wf_Core.Raise('WFSQL_ARGS');
862 end if;
863
864 if (itemkey = wf_engine.eng_synch) then
865 WF_CACHE.GetItemAttrValue(itemtype, itemKey, aname, iStatus, wiavIND);
866
867 if (iStatus <> WF_CACHE.task_SUCCESS) then
868 raise no_data_found;
869
870 else
871 WF_CACHE.ItemAttrValues(wiavIND).NUMBER_VALUE := avalue;
872
873 end if;
874
875 else
876 update WF_ITEM_ATTRIBUTE_VALUES set
877 NUMBER_VALUE = avalue
878 where ITEM_TYPE = itemtype
879 and ITEM_KEY = itemkey
880 and NAME = aname;
881
882 if (SQL%NOTFOUND) then
883 --ondemand logic.
884 if wf_item.Attribute_ON_Demand(itemtype, itemkey) then
885 --we need to check if the item attribute is defined or not
886 --if attribute value is not defined, we raise an exception
887 insert into WF_ITEM_ATTRIBUTE_VALUES (ITEM_TYPE, ITEM_KEY, NAME, NUMBER_VALUE)
888 select
889 SetItemAttrNumber.itemtype,
890 SetItemAttrNumber.itemkey,
891 SetItemAttrNumber.aname,
892 SetItemAttrNumber.avalue
893 from WF_ITEM_ATTRIBUTES WIA
894 where WIA.ITEM_TYPE = SetItemAttrNumber.itemtype
895 and WIA.NAME = SetItemAttrNumber.aname;
896
897 if SQL%NOTFOUND then
898 raise no_data_found;
899 end if;
900 else
901 raise no_data_found;
902 end if;
903 end if;
904 end if;
905
906 exception
907 when no_data_found then
908 Wf_Core.Context('Wf_Engine', 'SetItemAttrNumber', itemtype, itemkey,
909 aname, to_char(avalue));
910 Wf_Core.Token('TYPE', itemtype);
911 Wf_Core.Token('KEY', itemkey);
912 Wf_Core.Token('ATTRIBUTE', aname);
913 Wf_Core.Raise('WFENG_ITEM_ATTR');
914
915 when others then
916 Wf_Core.Context('Wf_Engine', 'SetItemAttrNumber', itemtype, itemkey,
917 aname, to_char(avalue));
918 raise;
919 end SetItemAttrNumber;
920
921 --
922 -- SetItemAttrDate (PUBLIC)
923 -- Set the value of a date item attribute.
924 -- Attribute must be a DATE-type attribute.
925 -- IN:
926 -- itemtype - Item type
927 -- itemkey - Item key
928 -- aname - Attribute Name
929 -- avalue - New value for attribute
930 --
931 procedure SetItemAttrDate(itemtype in varchar2,
932 itemkey in varchar2,
933 aname in varchar2,
934 avalue in date)
935 is
936 iStatus PLS_INTEGER;
937 wiavIND NUMBER;
938 nvalue NUMBER;
939
940 begin
941 -- Check Arguments
942 if ((itemtype is null) or
943 (itemkey is null) or
944 (aname is null)) then
945 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
946 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
947 Wf_Core.Token('ANAME', nvl(aname, 'NULL'));
948 Wf_Core.Raise('WFSQL_ARGS');
949 end if;
950
951 if (itemkey = wf_engine.eng_synch) then
952 WF_CACHE.GetItemAttrValue(itemtype, itemKey, aname, iStatus, wiavIND);
953
954 if (iStatus <> WF_CACHE.task_SUCCESS) then
955 raise no_data_found;
956
957 else
958 WF_CACHE.ItemAttrValues(wiavIND).DATE_VALUE := avalue;
959
960 end if;
961
962 else
963 update WF_ITEM_ATTRIBUTE_VALUES set
964 DATE_VALUE = avalue
965 where ITEM_TYPE = itemtype
966 and ITEM_KEY = itemkey
967 and NAME = aname;
968
969 if (SQL%NOTFOUND) then
970 --ondemand logic.
971 if wf_item.Attribute_ON_Demand(itemtype, itemkey) then
972 --we need to check if the item attribute is defined or not
973 --if attribute value is not defined at design time, we raise an exception
974 insert into WF_ITEM_ATTRIBUTE_VALUES (ITEM_TYPE, ITEM_KEY, NAME, DATE_VALUE)
975 select
976 SetItemAttrDate.itemtype,
977 SetItemAttrDate.itemkey,
978 SetItemAttrDate.aname,
979 SetItemAttrDate.avalue
980 from WF_ITEM_ATTRIBUTES WIA
981 where WIA.ITEM_TYPE = SetItemAttrDate.itemtype
982 and WIA.NAME = SetItemAttrDate.aname;
983
984 if SQL%NOTFOUND then
985 raise no_data_found;
986 end if;
987 else
988 raise no_data_found;
989 end if;
990 end if;
991 end if;
992
993 exception
994 when no_data_found then
995 Wf_Core.Context('Wf_Engine', 'SetItemAttrDate', itemtype, itemkey,
996 aname, to_char(avalue));
997 Wf_Core.Token('TYPE', itemtype);
998 Wf_Core.Token('KEY', itemkey);
999 Wf_Core.Token('ATTRIBUTE', aname);
1000 Wf_Core.Raise('WFENG_ITEM_ATTR');
1001
1002 when bad_format then --<rwunderl:2307104/>
1003 Wf_Core.Context('Wf_Engine', 'SetItemAttr', itemtype, itemkey,
1004 aname, avalue);
1005 Wf_Core.Token('VALUE', avalue);
1006 Wf_Core.Token('FORMAT', 'DATE');
1007 Wf_Core.Raise('WFENG_BAD_FORMAT');
1008
1009 when others then
1010 Wf_Core.Context('Wf_Engine', 'SetItemAttrDate', itemtype, itemkey,
1011 aname, to_char(avalue));
1012 raise;
1013 end SetItemAttrDate;
1014
1015 --
1016 -- SetItemAttrDocument (PUBLIC)
1017 -- Set the value of a document item attribute.
1018 -- IN:
1019 -- itemtype - Item type
1020 -- itemkey - Item key
1021 -- aname - Attribute Name
1022 -- documentid - Document Identifier - full concatenated document attribute
1023 -- strings:
1024 -- nodeid:libraryid:documentid:version:document_name
1025 --
1026 --
1027 procedure SetItemAttrDocument(itemtype in varchar2,
1028 itemkey in varchar2,
1029 aname in varchar2,
1030 documentid in varchar2)
1031 is
1032 begin
1033 -- Check Arguments
1034 if ((itemtype is null) or
1035 (itemkey is null) or
1036 (aname is null)) then
1037 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
1038 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
1039 Wf_Core.Token('ANAME', nvl(aname, 'NULL'));
1040 Wf_Core.Raise('WFSQL_ARGS');
1041 end if;
1042
1043 Wf_Engine.SetItemAttrText(itemtype, itemkey, aname, documentid);
1044 exception
1045 when others then
1046 Wf_Core.Context('Wf_Engine', 'SetItemAttrDocument', itemtype, itemkey,
1047 aname);
1048 raise;
1049 end SetItemAttrDocument;
1050
1051 -- SetItemAttrEvent
1052 -- Set event-type item attribute
1053 -- IN
1054 -- itemtype - process item type
1055 -- itemkey - process item key
1056 -- name - attribute name
1057 -- event - attribute value
1058 --
1059 procedure SetItemAttrEvent(
1060 itemtype in varchar2,
1061 itemkey in varchar2,
1062 name in varchar2,
1063 event in wf_event_t)
1064 is
1065 nvalue Number;
1066 begin
1067 -- Check Arguments
1068 if ((itemtype is null) or
1069 (itemkey is null) or
1070 (name is null)) then
1071 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
1072 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
1073 Wf_Core.Token('NAME', nvl(name, 'NULL'));
1074 Wf_Core.Raise('WFSQL_ARGS');
1075 end if;
1076
1077 -- Not allowed in synch mode
1078 if (itemkey = wf_engine.eng_synch) then
1079 wf_core.token('OPERATION', 'Wf_Engine.SetItemAttrEvent');
1080 wf_core.raise('WFENG_SYNCH_DISABLED');
1081 end if;
1082
1083 update WF_ITEM_ATTRIBUTE_VALUES set
1084 EVENT_VALUE = SetItemAttrEvent.event
1085 where ITEM_TYPE = SetItemAttrEvent.itemtype
1086 and ITEM_KEY = SetItemAttrEvent.itemkey
1087 and NAME = SetItemAttrEvent.name;
1088
1089 if (SQL%NOTFOUND) then
1090 --ondemand logic.
1091 if wf_item.Attribute_ON_Demand(SetItemAttrEvent.itemtype, SetItemAttrEvent.itemkey) then
1092 --we need to check if the item attribute is defined or not
1093 --if attribute value is not defined at design time, we raise an exception
1094
1095 insert into WF_ITEM_ATTRIBUTE_VALUES (ITEM_TYPE, ITEM_KEY, NAME, EVENT_VALUE)
1096 select
1097 SetItemAttrEvent.itemtype,
1098 SetItemAttrEvent.itemkey,
1099 SetItemAttrEvent.name,
1100 SetItemAttrEvent.event
1101 from WF_ITEM_ATTRIBUTES WIA
1102 where WIA.ITEM_TYPE = SetItemAttrEvent.itemtype
1103 and WIA.NAME = SetItemAttrEvent.name;
1104
1105 if SQL%NOTFOUND then
1106 raise no_data_found;
1107 end if;
1108 else
1109 raise no_data_found;
1110 end if;
1111 end if;
1112 exception
1113 when no_data_found then
1114 Wf_Core.Context('Wf_Engine', 'SetItemAttrEvent', itemtype, itemkey,
1115 name);
1116 Wf_Core.Token('TYPE', itemtype);
1117 Wf_Core.Token('KEY', itemkey);
1118 Wf_Core.Token('ATTRIBUTE', name);
1119 Wf_Core.Raise('WFENG_ITEM_ATTR');
1120 when others then
1121 Wf_Core.Context('Wf_Engine', 'SetItemAttrEvent', itemtype, itemkey,
1122 name);
1123 raise;
1124 end SetItemAttrEvent;
1125
1126 --
1127 -- SetItemAttrTextArray (PUBLIC)
1128 -- Set the values of an array of text item attribute.
1129 -- Unlike SetItemAttrText(), it stores the values directly.
1130 -- IN:
1131 -- itemtype - Item type
1132 -- itemkey - Item key
1133 -- aname - Array of Names
1134 -- avalue - Array of New values for attribute
1135 --
1136 procedure SetItemAttrTextArray(
1137 itemtype in varchar2,
1138 itemkey in varchar2,
1139 aname in Wf_Engine.NameTabTyp,
1140 avalue in Wf_Engine.TextTabTyp)
1141 is
1142 status pls_integer;
1143 arrayIndex pls_integer;
1144 wiavIND number;
1145 success_cnt number;
1146 success_ins_cnt number;
1147 begin
1148 -- Check Arguments
1149 if ((itemtype is null) or
1150 (itemkey is null)) then
1151 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
1152 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
1153 Wf_Core.Raise('WFSQL_ARGS');
1154 end if;
1155
1156 if (aname.COUNT = 0 or avalue.COUNT = 0) then
1157 -- Do not do anything if index table is empty.
1158 return;
1159
1160 elsif (aname.LAST <> avalue.LAST or aname.COUNT <> avalue.COUNT) then
1161 -- Raise an error if the two index tables do not end at the same index
1162 -- or do not have the same number of elements.
1163 Wf_Core.Raise('WFENG_ITEM_ATTR_ARRAY_MISMATCH');
1164 end if;
1165
1166 -- Set the text value.
1167 if (itemkey = wf_engine.eng_synch) then
1168 -- Use WF_CACHE in synch mode
1169 success_cnt := 0;
1170
1171 for arrayIndex in aname.FIRST..aname.LAST loop
1172 WF_CACHE.GetItemAttrValue(itemtype, itemKey, aname(arrayIndex), status,
1173 wiavIND);
1174
1175 if (status <> WF_CACHE.task_SUCCESS) then
1176 null; --The attribute is not in cache to be set. We will proceed to
1177 --try to set the remainder and then raise a no_data_found after
1178 --we complete
1179
1180 else
1181 WF_CACHE.ItemAttrValues(wiavIND).TEXT_VALUE := avalue(arrayIndex);
1182 success_cnt := success_cnt + 1;
1183
1184 end if;
1185 end loop;
1186
1187 else
1188 forall arrayIndex in aname.FIRST..aname.LAST
1189 update WF_ITEM_ATTRIBUTE_VALUES set
1190 TEXT_VALUE = avalue(arrayIndex)
1191 where ITEM_TYPE = itemtype
1192 and ITEM_KEY = itemkey
1193 and NAME = aname(arrayIndex);
1194
1195 success_cnt := SQL%ROWCOUNT;
1196 if (success_cnt <> aname.COUNT) then
1197 --ondemand logic
1198 if wf_item.Attribute_ON_Demand(itemtype, itemkey) then
1199 forall arrayIndex in aname.FIRST..aname.LAST
1200 insert into WF_ITEM_ATTRIBUTE_VALUES
1201 (ITEM_TYPE, ITEM_KEY, NAME, TEXT_VALUE)
1202 select itemtype, itemkey, aname(arrayIndex), avalue(arrayIndex)
1203 from WF_ITEM_ATTRIBUTES WIA
1204 where
1205 WIA.ITEM_TYPE = itemtype
1206 and
1207 WIA.NAME = aname(arrayIndex)
1208 and not exists (select 1 from WF_ITEM_ATTRIBUTE_VALUES WIAV
1209 where WIAV.item_type=itemtype
1210 and WIAV.item_key=itemkey
1211 and WIAV.NAME=aname(arrayIndex));
1212
1213 success_cnt := success_cnt + SQL%ROWCOUNT;
1214 if success_cnt <> aname.COUNT then
1215 raise no_data_found;
1216 end if;
1217 else
1218 raise no_data_found;
1219 end if;
1220 end if;
1221 end if;
1222
1223 exception
1224 when no_data_found then
1225 Wf_Core.Context('Wf_Engine', 'SetItemAttrTextArray', itemtype, itemkey);
1226 Wf_Core.Token('TYPE', itemtype);
1227 Wf_Core.Token('KEY', itemkey);
1228 Wf_Core.Token('TOTAL', to_char(aname.COUNT));
1229 Wf_Core.Token('SUCCESS', to_char(success_cnt));
1230 Wf_Core.Raise('WFENG_ITEM_ATTR_ARRAY');
1231
1232 when others then
1233 Wf_Core.Context('Wf_Engine', 'SetItemAttrTextArray', itemtype, itemkey);
1234 raise;
1235 end SetItemAttrTextArray;
1236
1237
1238 --
1239 -- SetItemAttrNumberArray (PUBLIC)
1240 -- Set the value of an array of number item attribute.
1241 -- Attribute must be a NUMBER-type attribute.
1242 -- IN:
1243 -- itemtype - Item type
1244 -- itemkey - Item key
1245 -- aname - Array of Names
1246 -- avalue - Array of new value for attribute
1247 --
1248 procedure SetItemAttrNumberArray(
1249 itemtype in varchar2,
1250 itemkey in varchar2,
1251 aname in Wf_Engine.NameTabTyp,
1252 avalue in Wf_Engine.NumTabTyp)
1253 is
1254 arrayIndex pls_integer;
1255 status pls_integer;
1256 wiavIND number;
1257 success_cnt number;
1258
1259 begin
1260 -- Check Arguments
1261 if ((itemtype is null) or
1262 (itemkey is null)) then
1263 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
1264 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
1265 Wf_Core.Raise('WFSQL_ARGS');
1266
1267 elsif (aname.COUNT = 0 or avalue.COUNT = 0) then
1268 -- Do not do anything if index table is empty.
1269 return;
1270
1271 elsif (aname.LAST <> avalue.LAST or aname.COUNT <> avalue.COUNT) then
1272 -- Raise an error if the two index tables do not end at the same index
1273 -- or do not have the same number of elements.
1274 Wf_Core.Raise('WFENG_ITEM_ATTR_ARRAY_MISMATCH');
1275
1276 end if;
1277
1278 -- Set the number value.
1279 if (itemkey = wf_engine.eng_synch) then
1280 -- Use WF_CACHE in synch mode
1281 success_cnt := 0;
1282
1283 for arrayIndex in aname.FIRST..aname.LAST loop
1284 WF_CACHE.GetItemAttrValue(itemtype, itemKey, aname(arrayIndex), status,
1285 wiavIND);
1286
1287 if (status <> WF_CACHE.task_SUCCESS) then
1288 null; --The attribute is not in cache to be set. We will proceed to
1289 --try to set the remainder and then raise a no_data_found after
1290 --we complete.
1291
1292 else
1293 WF_CACHE.ItemAttrValues(wiavIND).NUMBER_VALUE := avalue(arrayIndex);
1294 success_cnt := success_cnt + 1;
1295
1296 end if;
1297
1298 end loop;
1299
1300 else
1301 forall arrayIndex in aname.FIRST..aname.LAST
1302 update WF_ITEM_ATTRIBUTE_VALUES set
1303 NUMBER_VALUE = avalue(arrayIndex)
1304 where ITEM_TYPE = itemtype
1305 and ITEM_KEY = itemkey
1306 and NAME = aname(arrayIndex);
1307
1308 success_cnt := SQL%ROWCOUNT;
1309 if (success_cnt <> aname.COUNT) then
1310 --ondemand logic
1311 if wf_item.Attribute_ON_Demand(itemtype, itemkey) then
1312 forall arrayIndex in aname.FIRST..aname.LAST
1313 insert into WF_ITEM_ATTRIBUTE_VALUES
1314 (ITEM_TYPE, ITEM_KEY, NAME, NUMBER_VALUE)
1315 select itemtype, itemkey, aname(arrayIndex), avalue(arrayIndex)
1316 from WF_ITEM_ATTRIBUTES WIA
1317 where
1318 WIA.ITEM_TYPE = itemtype
1319 and
1320 WIA.NAME = aname(arrayIndex)
1321 and not exists (select 1 from WF_ITEM_ATTRIBUTE_VALUES WIAV
1322 where WIAV.item_type=itemtype
1323 and WIAV.item_key=itemkey
1324 and WIAV.NAME=aname(arrayIndex));
1325
1326 success_cnt := success_cnt + SQL%ROWCOUNT;
1327 if success_cnt <> aname.COUNT then
1328 raise no_data_found;
1329 end if;
1330 else
1331 raise no_data_found;
1332 end if;
1333 end if;
1334 end if;
1335
1336 exception
1337 when no_data_found then
1338 Wf_Core.Context('Wf_Engine', 'SetItemAttrNumberArray', itemtype, itemkey);
1339 Wf_Core.Token('TYPE', itemtype);
1340 Wf_Core.Token('KEY', itemkey);
1341 Wf_Core.Token('TOTAL', to_char(aname.COUNT));
1342 Wf_Core.Token('SUCCESS', to_char(success_cnt));
1343 Wf_Core.Raise('WFENG_ITEM_ATTR_ARRAY');
1344
1345 when others then
1346 Wf_Core.Context('Wf_Engine', 'SetItemAttrNumberArray', itemtype, itemkey);
1347 raise;
1348 end SetItemAttrNumberArray;
1349
1350 --
1351 -- SetItemAttrDateArray (PUBLIC)
1352 -- Set the value of an array of date item attribute.
1353 -- Attribute must be a DATE-type attribute.
1354 -- IN:
1355 -- itemtype - Item type
1356 -- itemkey - Item key
1357 -- aname - Array of Name
1358 -- avalue - Array of new value for attribute
1359 --
1360 procedure SetItemAttrDateArray(
1361 itemtype in varchar2,
1362 itemkey in varchar2,
1363 aname in Wf_Engine.NameTabTyp,
1364 avalue in Wf_Engine.DateTabTyp)
1365 is
1366 status pls_integer;
1367 arrayIndex pls_integer;
1368 wiavIND number;
1369 success_cnt number;
1370
1371 begin
1372 -- Check Arguments
1373 if ((itemtype is null) or
1374 (itemkey is null)) then
1375 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
1376 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
1377 Wf_Core.Raise('WFSQL_ARGS');
1378
1379 elsif (aname.COUNT = 0 or avalue.COUNT = 0) then
1380 -- Do not do anything if index table is empty.
1381 return;
1382
1383 elsif (aname.LAST <> avalue.LAST or aname.COUNT <> avalue.COUNT) then
1384 -- Raise an error if the two index tables do not end at the same index
1385 -- or do not have the same number of elements.
1386 Wf_Core.Raise('WFENG_ITEM_ATTR_ARRAY_MISMATCH');
1387
1388 end if;
1389
1390 success_cnt := 0;
1391 -- Set the date value.
1392 if (itemkey = wf_engine.eng_synch) then
1393 -- Use WF_CACHE in synch mode
1394 for arrayIndex in aname.FIRST..aname.LAST loop
1395 WF_CACHE.GetItemAttrValue(itemtype, itemKey, aname(arrayIndex), status,
1396 wiavIND);
1397
1398 if (status <> WF_CACHE.task_SUCCESS) then
1399 null; --The attribute is not in cache to be set. We will proceed to
1400 --try to set the remainder and then raise a no_data_found after
1401 --we complete.
1402
1403 else
1404 WF_CACHE.ItemAttrValues(wiavIND).DATE_VALUE := avalue(arrayIndex);
1405 success_cnt := success_cnt + 1;
1406
1407 end if;
1408
1409 end loop;
1410
1411 else
1412 forall arrayIndex in aname.FIRST..aname.LAST
1413 update WF_ITEM_ATTRIBUTE_VALUES set
1414 DATE_VALUE = avalue(arrayIndex)
1415 where ITEM_TYPE = itemtype
1416 and ITEM_KEY = itemkey
1417 and NAME = aname(arrayIndex);
1418
1419 success_cnt := SQL%ROWCOUNT;
1420 if (success_cnt <> aname.COUNT) then
1421 --ondemand logic
1422 if wf_item.Attribute_ON_Demand(itemtype, itemkey) then
1423 forall arrayIndex in aname.FIRST..aname.LAST
1424 insert into WF_ITEM_ATTRIBUTE_VALUES
1425 (ITEM_TYPE, ITEM_KEY, NAME, DATE_VALUE)
1426 select itemtype, itemkey, aname(arrayIndex), avalue(arrayIndex)
1427 from WF_ITEM_ATTRIBUTES WIA
1428 where
1429 WIA.ITEM_TYPE = itemtype
1430 and
1431 WIA.NAME = aname(arrayIndex)
1432 and not exists (select 1 from WF_ITEM_ATTRIBUTE_VALUES WIAV
1433 where WIAV.item_type=itemtype
1434 and WIAV.item_key=itemkey
1435 and WIAV.NAME=aname(arrayIndex));
1436
1437 success_cnt := success_cnt + SQL%ROWCOUNT;
1438 if success_cnt <> aname.COUNT then
1439 raise no_data_found;
1440 end if;
1441 else
1442 raise no_data_found;
1443 end if;
1444 end if;
1445 end if;
1446
1447 exception
1448 when no_data_found then
1449 Wf_Core.Context('Wf_Engine', 'SetItemAttrDateArray', itemtype, itemkey);
1450 Wf_Core.Token('TYPE', itemtype);
1451 Wf_Core.Token('KEY', itemkey);
1452 Wf_Core.Token('TOTAL', to_char(aname.COUNT));
1453 Wf_Core.Token('SUCCESS', to_char(success_cnt));
1454 Wf_Core.Raise('WFENG_ITEM_ATTR_ARRAY');
1455
1456 when others then
1457 Wf_Core.Context('Wf_Engine', 'SetItemAttrDateArray', itemtype, itemkey);
1458 raise;
1459 end SetItemAttrDateArray;
1460
1461 --
1462 -- GetItemAttrInfo (PUBLIC)
1463 -- Get type information about a item attribute.
1464 -- IN:
1465 -- itemtype - Item type
1466 -- aname - Attribute name
1467 -- OUT:
1468 -- atype - Attribute type
1469 -- subtype - 'SEND' or 'RESPOND'
1470 -- format - Attribute format
1471 --
1472 procedure GetItemAttrInfo(itemtype in varchar2,
1473 aname in varchar2,
1474 atype out NOCOPY varchar2,
1475 subtype out NOCOPY varchar2,
1476 format out NOCOPY varchar2)
1477 is
1478
1479 wiaIND NUMBER;
1480 status PLS_INTEGER;
1481
1482 begin
1483 -- Check Arguments
1484 if ((itemtype is null) or
1485 (aname is null)) then
1486 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
1487 Wf_Core.Token('ANAME', nvl(aname, 'NULL'));
1488 Wf_Core.Raise('WFSQL_ARGS');
1489 end if;
1490
1491 WF_CACHE.GetItemAttribute(itemtype, aname, status, wiaIND);
1492
1493 if (status <> WF_CACHE.task_SUCCESS) then
1494 select WIA.ITEM_TYPE, WIA.NAME, WIA.TYPE, WIA.SUBTYPE, WIA.FORMAT,
1495 WIA.TEXT_DEFAULT, WIA.NUMBER_DEFAULT, WIA.DATE_DEFAULT
1496 into WF_CACHE.ItemAttributes(wiaIND)
1497 from WF_ITEM_ATTRIBUTES WIA
1498 where WIA.ITEM_TYPE = itemtype
1499 and WIA.NAME = aname;
1500
1501 end if;
1502
1503 atype := WF_CACHE.ItemAttributes(wiaIND).TYPE;
1504 subtype := WF_CACHE.ItemAttributes(wiaIND).SUBTYPE;
1505 format := WF_CACHE.ItemAttributes(wiaIND).FORMAT;
1506
1507 exception
1508 when no_data_found then
1509 Wf_Core.Context('Wf_Engine', 'GetItemAttrInfo', itemtype, aname);
1510 Wf_Core.Token('TYPE', itemtype);
1511 Wf_Core.Token('KEY', NULL);
1512 Wf_Core.Token('ATTRIBUTE', aname);
1513 Wf_Core.Raise('WFENG_ITEM_ATTR');
1514
1515 when others then
1516 Wf_Core.Context('Wf_Engine', 'GetItemAttrInfo', itemtype, aname);
1517 raise;
1518 end GetItemAttrInfo;
1519
1520 --
1521 -- GetItemAttrText (PUBLIC)
1522 -- Get the value of a text item attribute.
1523 -- If the attribute is a NUMBER or DATE type, then translate the
1524 -- number/date value to a text-string representation using attrbute format.
1525 -- For all other types, get the value directly.
1526 -- IN:
1527 -- itemtype - Item type
1528 -- itemkey - Item key
1529 -- aname - Attribute Name
1530 -- RETURNS:
1531 -- Attribute value
1532 --
1533 function GetItemAttrText(itemtype in varchar2,
1534 itemkey in varchar2,
1535 aname in varchar2,
1536 ignore_notfound in boolean)
1537 return varchar2 is
1538 lvalue varchar2(4000);
1539 nvalue number;
1540 dvalue date;
1541 i pls_integer;
1542
1543 wiaIND NUMBER;
1544 wiavIND NUMBER;
1545 status PLS_INTEGER;
1546
1547 begin
1548 -- Check Arguments
1549 if ((itemtype is null) or
1550 (itemkey is null) or
1551 (aname is null)) then
1552 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
1553 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
1554 Wf_Core.Token('ANAME', nvl(aname, 'NULL'));
1555 Wf_Core.Raise('WFSQL_ARGS');
1556 end if;
1557
1558 -- Get type and format of attr.
1559 -- This is used for translating number/date strings.
1560 WF_CACHE.GetItemAttribute(itemtype, aname, status, wiaIND);
1561
1562 if (status <> WF_CACHE.task_SUCCESS) then
1563 begin
1564 select WIA.ITEM_TYPE, WIA.NAME, WIA.TYPE, WIA.SUBTYPE, WIA.FORMAT,
1565 WIA.TEXT_DEFAULT, WIA.NUMBER_DEFAULT, WIA.DATE_DEFAULT
1566 into WF_CACHE.ItemAttributes(wiaIND)
1567 from WF_ITEM_ATTRIBUTES WIA
1568 where WIA.ITEM_TYPE = itemtype
1569 and WIA.NAME = aname;
1570
1571 exception
1572 when no_data_found then
1573 -- This is an unvalidated runtime attr.
1574 -- Treat it as a varchar2.
1575 WF_CACHE.ItemAttributes(wiaIND).ITEM_TYPE := itemtype;
1576 WF_CACHE.ItemAttributes(wiaIND).NAME := aname;
1577 WF_CACHE.ItemAttributes(wiaIND).TYPE := 'VARCHAR2';
1578 WF_CACHE.ItemAttributes(wiaIND).SUBTYPE := '';
1579 WF_CACHE.ItemAttributes(wiaIND).FORMAT := '';
1580
1581 end;
1582 end if;
1583
1584 -- Select value from appropriate type column.
1585 if (WF_CACHE.ItemAttributes(wiaIND).TYPE = 'NUMBER') then
1586 nvalue := Wf_Engine.GetItemAttrNumber(itemtype, itemkey, aname);
1587 if (WF_CACHE.ItemAttributes(wiaIND).FORMAT is null) then
1588 lvalue := to_char(nvalue);
1589 else
1590 lvalue := to_char(nvalue, WF_CACHE.ItemAttributes(wiaIND).FORMAT);
1591 end if;
1592 elsif (WF_CACHE.ItemAttributes(wiaIND).TYPE = 'DATE') then
1593 dvalue := Wf_Engine.GetItemAttrDate(itemtype, itemkey, aname);
1594 if (WF_CACHE.ItemAttributes(wiaIND).FORMAT is null) then
1595 lvalue := to_char(dvalue);
1596 else
1597 lvalue := to_char(dvalue, WF_CACHE.ItemAttributes(wiaIND).FORMAT);
1598 end if;
1599 else
1600 -- VARCHAR2, LOOKUP, FORM, URL, DOCUMENT.
1601 -- Get the text value directly with no translation.
1602 if (itemkey = wf_engine.eng_synch) then
1603 -- Use WF_CACHE in synch mode
1604 WF_CACHE.GetItemAttrValue(itemtype, itemKey, aname, status, wiavIND);
1605
1606 if (status <> WF_CACHE.task_SUCCESS) then
1607 raise no_data_found;
1608
1609 else
1610 return(WF_CACHE.ItemAttrValues(wiavIND).TEXT_VALUE);
1611
1612 end if;
1613
1614 else
1615 --we are going to wrap this into a scope, when value not found, we will
1616 --check ondemand flag is true, we will look at cache for design time
1617 --data else will propagate the exception
1618 begin
1619 select TEXT_VALUE
1620 into lvalue
1621 from WF_ITEM_ATTRIBUTE_VALUES
1622 where ITEM_TYPE = itemtype
1623 and ITEM_KEY = itemkey
1624 and NAME = aname;
1625 exception
1626 when no_data_found then
1627 -- ondemand logic
1628 if wf_item.Attribute_On_Demand(itemtype, itemkey) then
1629 select TEXT_DEFAULT
1630 into lvalue
1631 from WF_ITEM_ATTRIBUTES
1632 where ITEM_TYPE = itemtype
1633 and NAME = aname;
1634 else
1635 raise no_data_found;
1636 end if;
1637 end;
1638 end if;
1639 end if;
1640
1641 return(lvalue);
1642
1643 exception
1644 when no_data_found then
1645 if (ignore_notfound) then
1646
1647 return(null);
1648
1649 else
1650
1651 Wf_Core.Context('Wf_Engine', 'GetItemAttrText', itemtype, itemkey,
1652 aname);
1653 Wf_Core.Token('TYPE', itemtype);
1654 Wf_Core.Token('KEY', itemkey);
1655 Wf_Core.Token('ATTRIBUTE', aname);
1656 Wf_Core.Raise('WFENG_ITEM_ATTR');
1657
1658 end if;
1659
1660 when others then
1661 Wf_Core.Context('Wf_Engine', 'GetItemAttrText', itemtype, itemkey,
1662 aname);
1663 raise;
1664
1665 end GetItemAttrText;
1666
1667 --
1668 -- GetItemAttrNumber (PUBLIC)
1669 -- Get the value of a number item attribute.
1670 -- Attribute must be a NUMBER-type attribute.
1671 -- IN:
1672 -- itemtype - Item type
1673 -- itemkey - Item key
1674 -- aname - Attribute Name
1675 -- RETURNS:
1676 -- Attribute value
1677 --
1678 function GetItemAttrNumber(itemtype in varchar2,
1679 itemkey in varchar2,
1680 aname in varchar2,
1681 ignore_notfound in boolean)
1682
1683 return number is
1684 wiavIND number;
1685 status pls_integer;
1686 lvalue number;
1687
1688 begin
1689 -- Check Arguments
1690 if ((itemtype is null) or
1691 (itemkey is null) or
1692 (aname is null)) then
1693 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
1694 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
1695 Wf_Core.Token('ANAME', nvl(aname, 'NULL'));
1696 Wf_Core.Raise('WFSQL_ARGS');
1697 end if;
1698
1699 if (itemkey = wf_engine.eng_synch) then
1700 -- Use WF_CACHE in synch mode
1701 WF_CACHE.GetItemAttrValue(itemtype, itemKey, aname, status, wiavIND);
1702
1703 if (status <> WF_CACHE.task_SUCCESS) then
1704 raise no_data_found;
1705
1706 else
1707 return(WF_CACHE.ItemAttrValues(wiavIND).NUMBER_VALUE);
1708
1709 end if;
1710
1711 else
1712 begin
1713 select NUMBER_VALUE
1714 into lvalue
1715 from WF_ITEM_ATTRIBUTE_VALUES
1716 where ITEM_TYPE = itemtype
1717 and ITEM_KEY = itemkey
1718 and NAME = aname;
1719 exception
1720 when no_data_found then
1721 -- ondemand logic
1722 if wf_item.Attribute_On_Demand(itemtype, itemkey) then
1723 select NUMBER_DEFAULT
1724 into lvalue
1725 from WF_ITEM_ATTRIBUTES
1726 where ITEM_TYPE = itemtype
1727 and NAME = aname;
1728 else
1729 raise no_data_found;
1730 end if;
1731 end;
1732 end if;
1733
1734 return(lvalue);
1735
1736 exception
1737 when no_data_found then
1738 if (ignore_notfound) then
1739
1740 return(null);
1741
1742 else
1743
1744 Wf_Core.Context('Wf_Engine', 'GetItemAttrNumber', itemtype, itemkey,
1745 aname);
1746 Wf_Core.Token('TYPE', itemtype);
1747 Wf_Core.Token('KEY', itemkey);
1748 Wf_Core.Token('ATTRIBUTE', aname);
1749 Wf_Core.Raise('WFENG_ITEM_ATTR');
1750
1751 end if;
1752
1753 when others then
1754 Wf_Core.Context('Wf_Engine', 'GetItemAttrNumber', itemtype, itemkey,
1755 aname);
1756 raise;
1757 end GetItemAttrNumber;
1758
1759 --
1760 -- GetItemAttrDate (PUBLIC)
1761 -- Get the value of a date item attribute.
1762 -- Attribute must be a DATE-type attribute.
1763 -- IN:
1764 -- itemtype - Item type
1765 -- itemkey - Item key
1766 -- aname - Attribute Name
1767 -- RETURNS:
1768 -- Attribute value
1769 --
1770 function GetItemAttrDate (itemtype in varchar2,
1771 itemkey in varchar2,
1772 aname in varchar2,
1773 ignore_notfound in boolean)
1774 return date is
1775 lvalue date;
1776 wiavIND number;
1777 status pls_integer;
1778
1779 begin
1780 -- Check Arguments
1781 if ((itemtype is null) or
1782 (itemkey is null) or
1783 (aname is null)) then
1784 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
1785 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
1786 Wf_Core.Token('ANAME', nvl(aname, 'NULL'));
1787 Wf_Core.Raise('WFSQL_ARGS');
1788 end if;
1789
1790 if (itemkey = wf_engine.eng_synch) then
1791 -- Use WF_CACHE in synch mode
1792 WF_CACHE.GetItemAttrValue(itemtype, itemKey, aname, status, wiavIND);
1793
1794 if (status <> WF_CACHE.task_SUCCESS) then
1795 raise no_data_found;
1796
1797 else
1798 return(WF_CACHE.ItemAttrValues(wiavIND).DATE_VALUE);
1799
1800 end if;
1801
1802 else
1803 begin
1804 select DATE_VALUE
1805 into lvalue
1806 from WF_ITEM_ATTRIBUTE_VALUES
1807 where ITEM_TYPE = itemtype
1808 and ITEM_KEY = itemkey
1809 and NAME = aname;
1810 exception
1811 when no_data_found then
1812 if wf_item.Attribute_On_Demand(itemtype, itemkey) then
1813 select DATE_DEFAULT
1814 into lvalue
1815 from WF_ITEM_ATTRIBUTES
1816 where ITEM_TYPE = itemtype
1817 and NAME = aname;
1818 else
1819 raise no_data_found;
1820 end if;
1821 end;
1822
1823 end if;
1824
1825 return(lvalue);
1826
1827 exception
1828 when no_data_found then
1829 if (ignore_notfound) then
1830
1831 return(null);
1832
1833 else
1834
1835 Wf_Core.Context('Wf_Engine', 'GetItemAttrDate', itemtype, itemkey,
1836 aname);
1837 Wf_Core.Token('TYPE', itemtype);
1838 Wf_Core.Token('KEY', itemkey);
1839 Wf_Core.Token('ATTRIBUTE', aname);
1840 Wf_Core.Raise('WFENG_ITEM_ATTR');
1841
1842 end if;
1843 when others then
1844 Wf_Core.Context('Wf_Engine', 'GetItemAttrDate', itemtype, itemkey,
1845 aname);
1846 raise;
1847 end GetItemAttrDate;
1848
1849 --
1850 -- GetItemAttrDocument (PUBLIC)
1851 -- Get the value of a document item attribute.
1852 -- IN:
1853 -- itemtype - Item type
1854 -- itemkey - Item key
1855 -- aname - Attribute Name
1856 -- RETURNS:
1857 -- documentid - Document Identifier - full concatenated document attribute
1858 -- strings:
1859 -- nodeid:libraryid:documentid:version:document_name
1860 --
1861 function GetItemAttrDocument(itemtype in varchar2,
1862 itemkey in varchar2,
1863 aname in varchar2,
1864 ignore_notfound in boolean)
1865
1866 return varchar2
1867 is
1868 begin
1869 -- Check Arguments
1870 if ((itemtype is null) or
1871 (itemkey is null) or
1872 (aname is null)) then
1873 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
1874 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
1875 Wf_Core.Token('ANAME', nvl(aname, 'NULL'));
1876 Wf_Core.Raise('WFSQL_ARGS');
1877 end if;
1878
1879 return(Wf_Engine.GetItemAttrText(itemtype, itemkey, aname, ignore_notfound));
1880 exception
1881 when others then
1882 Wf_Core.Context('Wf_Engine', 'GetItemAttrDocument', itemtype, itemkey,
1883 aname);
1884 raise;
1885 end GetItemAttrDocument;
1886
1887 --
1888 -- GetItemAttrClob (PUBLIC)
1889 -- Get display contents of item attribute as a clob
1890 -- NOTE
1891 -- Returns expanded content of attribute.
1892 -- For DOCUMENT-type attributes, this will be the actual document
1893 -- generated. For all other types, this will be the displayed
1894 -- value of the attribute.
1895 -- Use GetItemAttrText to retrieve internal key.
1896 -- IN
1897 -- itemtype - item type
1898 -- itemkey - item key
1899 -- aname - item attribute name
1900 -- RETURNS
1901 -- Expanded content of item attribute as a clob
1902 --
1903 function GetItemAttrClob(
1904 itemtype in varchar2,
1905 itemkey in varchar2,
1906 aname in varchar2)
1907 return clob
1908 is
1909 tempclob clob;
1910 value varchar2(32000);
1911
1912 wiaIND NUMBER;
1913 status PLS_INTEGER;
1914
1915 begin
1916 -- Check Arguments
1917 if ((itemtype is null) or
1918 (itemkey is null) or
1919 (aname is null)) then
1920 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
1921 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
1922 Wf_Core.Token('ANAME', nvl(aname, 'NULL'));
1923 Wf_Core.Raise('WFSQL_ARGS');
1924 end if;
1925
1926 -- ### Needs to be integrated with document support in wf_notifications!
1927
1928 -- Get attribute type info
1929 WF_CACHE.GetItemAttribute(itemtype, aname, status, wiaIND);
1930
1931 if (status <> WF_CACHE.task_SUCCESS) then
1932 begin
1933 select WIA.ITEM_TYPE, WIA.NAME, WIA.TYPE, WIA.SUBTYPE, WIA.FORMAT,
1934 WIA.TEXT_DEFAULT, WIA.NUMBER_DEFAULT, WIA.DATE_DEFAULT
1935 into WF_CACHE.ItemAttributes(wiaIND)
1936 from WF_ITEM_ATTRIBUTES WIA
1937 where WIA.ITEM_TYPE = GetItemAttrClob.itemtype
1938 and WIA.NAME = GetItemAttrClob.aname;
1939
1940 exception
1941 when no_data_found then
1942 -- This is an unvalidated runtime attr.
1943 -- Treat it as a varchar2.
1944 WF_CACHE.ItemAttributes(wiaIND).ITEM_TYPE := itemtype;
1945 WF_CACHE.ItemAttributes(wiaIND).NAME := aname;
1946 WF_CACHE.ItemAttributes(wiaIND).TYPE := 'VARCHAR2';
1947 WF_CACHE.ItemAttributes(wiaIND).SUBTYPE := '';
1948 WF_CACHE.ItemAttributes(wiaIND).FORMAT := '';
1949
1950 end;
1951 end if;
1952
1953 -- Build clob with contents based on attr type
1954 if (WF_CACHE.ItemAttributes(wiaIND).TYPE = '###NOTDONE') then
1955 -- Parse out document subtypes
1956 null;
1957 else
1958 -- All others just use text value
1959 value := WF_Engine.GetItemAttrText(itemtype, itemkey, aname);
1960 end if;
1961
1962 -- Write value to fake clob and return
1963 if (value is null) then
1964 -- Dbms_lob raises error if value is null...
1965 return(null);
1966 else
1967 dbms_lob.createtemporary(tempclob, TRUE, dbms_lob.session);
1968 dbms_lob.write(tempclob, lengthb(value), 1, value);
1969 return(tempclob);
1970 end if;
1971
1972 exception
1973 when others then
1974 Wf_Core.Context('Wf_Engine', 'GetItemAttrClob', itemtype,
1975 itemkey, aname);
1976 raise;
1977 end GetItemAttrClob;
1978
1979 --
1980 -- GetItemAttrEvent
1981 -- Get event-type item attribute
1982 -- IN
1983 -- itemtype - process item type
1984 -- itemkey - process item key
1985 -- name - attribute name
1986 -- RETURNS
1987 -- Attribute value
1988 --
1989 function GetItemAttrEvent(
1990 itemtype in varchar2,
1991 itemkey in varchar2,
1992 name in varchar2)
1993 return wf_event_t
1994 is
1995 lvalue wf_event_t;
1996 l_value number;
1997 begin
1998 -- Check Arguments
1999 if ((itemtype is null) or
2000 (itemkey is null) or
2001 (name is null)) then
2002 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
2003 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
2004 Wf_Core.Token('NAME', nvl(name, 'NULL'));
2005 Wf_Core.Raise('WFSQL_ARGS');
2006 end if;
2007
2008 -- Not allowed in synch mode
2009 if (itemkey = wf_engine.eng_synch) then
2010 wf_core.token('OPERATION', 'Wf_Engine.GetItemAttrEvent');
2011 wf_core.raise('WFENG_SYNCH_DISABLED');
2012 end if;
2013
2014 begin
2015 select EVENT_VALUE
2016 into lvalue
2017 from WF_ITEM_ATTRIBUTE_VALUES
2018 where ITEM_TYPE = GetItemAttrEvent.itemtype
2019 and ITEM_KEY = GetItemAttrEvent.itemkey
2020 and NAME = GetItemAttrEvent.name;
2021
2022 --Initialization done only if event_value is null
2023 if lvalue is null then
2024 Wf_Event_T.Initialize(lvalue);
2025 end if;
2026 return(lvalue);
2027
2028 exception
2029 when no_data_found then
2030 --
2031 --For ondemand item attribute, if not found in wiav, we need to check
2032 -- if it is defined in wia table. If yes, we simply return a empty event
2033 -- to be backward compatible, otherwise no_data_found is thrown.
2034 --
2035 --Note: we do not insert value in wiav at this point. Event value
2036 -- will only be inserted into the table when setItemAttrEvent is called.
2037 --
2038 if wf_item.Attribute_On_Demand(itemtype, itemkey) then
2039 select null into l_value
2040 from WF_ITEM_ATTRIBUTES WIA
2041 where WIA.ITEM_TYPE = GetItemAttrEvent.itemtype
2042 and WIA.NAME = GetItemAttrEvent.name;
2043
2044 Wf_Event_T.Initialize(lvalue);
2045
2046 return(lvalue);
2047 else
2048 raise no_data_found;
2049 end if;
2050 end;
2051 exception
2052 when no_data_found then
2053 Wf_Core.Context('Wf_Engine', 'GetItemAttrEvent', itemtype, itemkey,
2054 name);
2055 Wf_Core.Token('TYPE', itemtype);
2056 Wf_Core.Token('KEY', itemkey);
2057 Wf_Core.Token('ATTRIBUTE', name);
2058 Wf_Core.Raise('WFENG_ITEM_ATTR');
2059 when others then
2060 Wf_Core.Context('Wf_Engine', 'GetItemAttrEvent', itemtype,
2061 itemkey, name);
2062 raise;
2063 end GetItemAttrEvent;
2064
2065 --
2066 -- GetActivityAttrInfo (PUBLIC)
2067 -- Get type information about an activity attribute.
2068 -- IN:
2069 -- itemtype - Item type
2070 -- itemkey - Item key
2071 -- actid - Process activity id
2072 -- aname - Attribute name
2073 -- OUT:
2074 -- atype - Attribute type
2075 -- subtype - 'SEND' or 'RESPOND'
2076 -- format - Attribute format
2077 --
2078 procedure GetActivityAttrInfo(itemtype in varchar2,
2079 itemkey in varchar2,
2080 actid in number,
2081 aname in varchar2,
2082 atype out NOCOPY varchar2,
2083 subtype out NOCOPY varchar2,
2084 format out NOCOPY varchar2)
2085 is
2086 actdate date;
2087
2088 waIND NUMBER;
2089 waaIND NUMBER;
2090 status PLS_INTEGER;
2091
2092 begin
2093 -- Check Arguments
2094 if ((itemtype is null) or
2095 (itemkey is null) or
2096 (actid is null) or
2097 (aname is null)) then
2098 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
2099 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
2100 Wf_Core.Token('ACTID', nvl(actid, 'NULL'));
2101 Wf_Core.Token('ANAME', nvl(aname, 'NULL'));
2102 Wf_Core.Raise('WFSQL_ARGS');
2103
2104 end if;
2105
2106 actdate := Wf_Item.Active_Date(itemtype, itemkey);
2107
2108 WF_CACHE.GetActivityAttr( itemtype, aname, actid, actdate, status, waIND,
2109 waaIND );
2110
2111 if (status <> WF_CACHE.task_SUCCESS) then
2112
2113 waIND := 0; --If the Get failed, we presume we did not get proper
2114 waaIND := 0; --hash values for the indexes. So we default to 0.
2115
2116 select WA.ITEM_TYPE, WA.NAME, WA.VERSION, WA.TYPE, WA.RERUN,
2117 WA.EXPAND_ROLE, WA.COST, WA.ERROR_ITEM_TYPE, WA.ERROR_PROCESS,
2118 WA.FUNCTION, WA.FUNCTION_TYPE,
2119 WA.EVENT_NAME, WA.MESSAGE, WA.BEGIN_DATE,
2120 WA.END_DATE, WA.DIRECTION, WAA.ACTIVITY_ITEM_TYPE,
2121 WAA.ACTIVITY_NAME, WAA.ACTIVITY_VERSION, WAA.NAME, WAA.TYPE,
2122 WAA.SUBTYPE, WAA.FORMAT, WPA.PROCESS_ITEM_TYPE, WPA.PROCESS_NAME,
2123 WPA.PROCESS_VERSION, WPA.ACTIVITY_ITEM_TYPE, WPA.ACTIVITY_NAME,
2124 WPA.INSTANCE_ID, WPA.INSTANCE_LABEL, WPA.PERFORM_ROLE,
2125 WPA.PERFORM_ROLE_TYPE, WPA.START_END, WPA.DEFAULT_RESULT
2126
2127 into WF_CACHE.Activities(waIND).ITEM_TYPE,
2128 WF_CACHE.Activities(waIND).NAME,
2129 WF_CACHE.Activities(waIND).VERSION,
2130 WF_CACHE.Activities(waIND).TYPE,
2131 WF_CACHE.Activities(waIND).RERUN,
2132 WF_CACHE.Activities(waIND).EXPAND_ROLE,
2133 WF_CACHE.Activities(waIND).COST,
2134 WF_CACHE.Activities(waIND).ERROR_ITEM_TYPE,
2135 WF_CACHE.Activities(waIND).ERROR_PROCESS,
2136 WF_CACHE.Activities(waIND).FUNCTION,
2137 WF_CACHE.Activities(waIND).FUNCTION_TYPE,
2138 WF_CACHE.Activities(waIND).EVENT_NAME,
2139 WF_CACHE.Activities(waIND).MESSAGE,
2140 WF_CACHE.Activities(waIND).BEGIN_DATE,
2141 WF_CACHE.Activities(waIND).END_DATE,
2142 WF_CACHE.Activities(waIND).DIRECTION,
2143 WF_CACHE.ActivityAttributes(waaIND).ACTIVITY_ITEM_TYPE,
2144 WF_CACHE.ActivityAttributes(waaIND).ACTIVITY_NAME,
2145 WF_CACHE.ActivityAttributes(waaIND).ACTIVITY_VERSION,
2146 WF_CACHE.ActivityAttributes(waaIND).NAME,
2147 WF_CACHE.ActivityAttributes(waaIND).TYPE,
2148 WF_CACHE.ActivityAttributes(waaIND).SUBTYPE,
2149 WF_CACHE.ActivityAttributes(waaIND).FORMAT,
2150 WF_CACHE.ProcessActivities(actid).PROCESS_ITEM_TYPE,
2151 WF_CACHE.ProcessActivities(actid).PROCESS_NAME,
2152 WF_CACHE.ProcessActivities(actid).PROCESS_VERSION,
2153 WF_CACHE.ProcessActivities(actid).ACTIVITY_ITEM_TYPE,
2154 WF_CACHE.ProcessActivities(actid).ACTIVITY_NAME,
2155 WF_CACHE.ProcessActivities(actid).INSTANCE_ID,
2156 WF_CACHE.ProcessActivities(actid).INSTANCE_LABEL,
2157 WF_CACHE.ProcessActivities(actid).PERFORM_ROLE,
2158 WF_CACHE.ProcessActivities(actid).PERFORM_ROLE_TYPE,
2159 WF_CACHE.ProcessActivities(actid).START_END,
2160 WF_CACHE.ProcessActivities(actid).DEFAULT_RESULT
2161
2162 from WF_ACTIVITY_ATTRIBUTES WAA, WF_PROCESS_ACTIVITIES WPA,
2163 WF_ACTIVITIES WA
2164
2165 where WPA.INSTANCE_ID = actid
2166 and WA.ITEM_TYPE = WPA.ACTIVITY_ITEM_TYPE
2167 and WA.NAME = WPA.ACTIVITY_NAME
2168 and actdate >= WA.BEGIN_DATE
2169 and actdate < NVL(WA.END_DATE, actdate+1)
2170 and WAA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE
2171 and WAA.ACTIVITY_NAME = WA.NAME
2172 and WAA.ACTIVITY_VERSION = WA.VERSION
2173 and WAA.NAME = aname;
2174
2175 --Get the proper hash key and copy the temporary records into the
2176 --proper locations.
2177 waIND := WF_CACHE.HashKey(itemType ||
2178 WF_CACHE.ProcessActivities(actid).ACTIVITY_NAME);
2179
2180 WF_CACHE.Activities(waIND) := WF_CACHE.Activities(0);
2181
2182 waaIND := WF_CACHE.HashKey(itemType || aname ||
2183 WF_CACHE.ProcessActivities(actid).ACTIVITY_NAME);
2184
2185 WF_CACHE.ActivityAttributes(waaIND) :=
2186 WF_CACHE.ActivityAttributes(0);
2187
2188 end if;
2189
2190 atype := WF_CACHE.ActivityAttributes(waaIND).TYPE;
2191 subtype := WF_CACHE.ActivityAttributes(waaIND).SUBTYPE;
2192 format := WF_CACHE.ActivityAttributes(waaIND).FORMAT;
2193
2194 exception
2195 when no_data_found then
2196 Wf_Core.Context('Wf_Engine', 'GetActivityAttrInfo', itemtype, itemkey,
2197 to_char(actid), aname);
2198 Wf_Core.Token('TYPE', itemtype);
2199 Wf_Core.Token('KEY', itemkey);
2200 Wf_Core.Token('ACTIVITY', to_char(actid));
2201 Wf_Core.Token('ATTRIBUTE', aname);
2202 Wf_Core.Raise('WFENG_ACTIVITY_ATTR');
2203 when others then
2204 Wf_Core.Context('Wf_Engine', 'GetActivityAttrInfo', itemtype, itemkey,
2205 to_char(actid), aname);
2206 raise;
2207 end GetActivityAttrInfo;
2208
2209
2210 --
2211 -- GetActivityAttrText (PUBLIC)
2212 -- Get the value of a text item attribute.
2213 -- If the attribute is a NUMBER or DATE type, then translate the
2214 -- number/date value to a text-string representation using attrbute format.
2215 -- For all other types, get the value directly.
2216 -- IN:
2217 -- itemtype - Item type
2218 -- itemkey - Item key
2219 -- actid - Process activity id
2220 -- aname - Attribute Name
2221 -- RETURNS:
2222 -- Attribute value
2223 --
2224 function GetActivityAttrText(itemtype in varchar2,
2225 itemkey in varchar2,
2226 actid in number,
2227 aname in varchar2,
2228 ignore_notfound in boolean)
2229 return varchar2 is
2230
2231 actdate date;
2232
2233 status PLS_INTEGER;
2234 waavIND NUMBER;
2235 waaIND NUMBER;
2236 waIND NUMBER;
2237
2238 begin
2239 -- Check Arguments
2240 if ((itemtype is null) or
2241 (itemkey is null) or
2242 (actid is null) or
2243 (aname is null)) then
2244 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
2245 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
2246 Wf_Core.Token('ACTID', nvl(actid, 'NULL'));
2247 Wf_Core.Token('ANAME', nvl(aname, 'NULL'));
2248 Wf_Core.Raise('WFSQL_ARGS');
2249 end if;
2250
2251 -- First check value_type flag for possible item_attribute ref.
2252 -- Checking to see if the Attribute Value is in cache.
2253 WF_CACHE.GetActivityAttrValue(actid, aname, status, waavIND);
2254
2255 if (status <> WF_CACHE.task_SUCCESS) then
2256 open curs_activityattr (actid, aname);
2257 fetch curs_activityattr into WF_CACHE.ActivityAttrValues(waavIND);
2258 close curs_activityattr;
2259 end if;
2260
2261 -- If it is a reference, return value of item_attr instead of
2262 -- contents of WAAV.
2263 if (WF_CACHE.ActivityAttrValues(waavIND).VALUE_TYPE = 'ITEMATTR') then
2264 if (WF_CACHE.ActivityAttrValues(waavIND).TEXT_VALUE is null) then
2265 return(null); -- Null itemattr means null value, not an error
2266 end if;
2267
2268 return(GetItemAttrText(itemtype, itemkey,
2269 substrb(WF_CACHE.ActivityAttrValues(waavIND).TEXT_VALUE, 1, 30)));
2270
2271 end if;
2272
2273 -- This is NOT an itemattr reference, get value directly from WAAV.
2274 -- Get type and format of attr for translating number/date strings.
2275 begin
2276 actdate := Wf_Item.Active_Date(itemtype, itemkey);
2277
2278 WF_CACHE.GetActivityAttr( itemtype, aname, actid, actdate, status, waIND,
2279 waaIND );
2280
2281 if (status <> WF_CACHE.task_SUCCESS) then
2282 waIND := 0;
2283 waaIND := 0;
2284
2285 select WA.ITEM_TYPE, WA.NAME, WA.VERSION, WA.TYPE, WA.RERUN,
2286 WA.EXPAND_ROLE, WA.COST, WA.ERROR_ITEM_TYPE, WA.ERROR_PROCESS,
2287 WA.FUNCTION, WA.FUNCTION_TYPE,
2288 WA.EVENT_NAME, WA.MESSAGE, WA.BEGIN_DATE,
2289 WA.END_DATE, WA.DIRECTION, WAA.ACTIVITY_ITEM_TYPE,
2290 WAA.ACTIVITY_NAME, WAA.ACTIVITY_VERSION, WAA.NAME, WAA.TYPE,
2291 WAA.SUBTYPE, WAA.FORMAT, WPA.PROCESS_ITEM_TYPE, WPA.PROCESS_NAME,
2292 WPA.PROCESS_VERSION, WPA.ACTIVITY_ITEM_TYPE, WPA.ACTIVITY_NAME,
2293 WPA.INSTANCE_ID, WPA.INSTANCE_LABEL, WPA.PERFORM_ROLE,
2294 WPA.PERFORM_ROLE_TYPE, WPA.START_END, WPA.DEFAULT_RESULT
2295
2296 into WF_CACHE.Activities(waIND).ITEM_TYPE,
2297 WF_CACHE.Activities(waIND).NAME,
2298 WF_CACHE.Activities(waIND).VERSION,
2299 WF_CACHE.Activities(waIND).TYPE,
2300 WF_CACHE.Activities(waIND).RERUN,
2301 WF_CACHE.Activities(waIND).EXPAND_ROLE,
2302 WF_CACHE.Activities(waIND).COST,
2303 WF_CACHE.Activities(waIND).ERROR_ITEM_TYPE,
2304 WF_CACHE.Activities(waIND).ERROR_PROCESS,
2305 WF_CACHE.Activities(waIND).FUNCTION,
2306 WF_CACHE.Activities(waIND).FUNCTION_TYPE,
2307 WF_CACHE.Activities(waIND).EVENT_NAME,
2308 WF_CACHE.Activities(waIND).MESSAGE,
2309 WF_CACHE.Activities(waIND).BEGIN_DATE,
2310 WF_CACHE.Activities(waIND).END_DATE,
2311 WF_CACHE.Activities(waIND).DIRECTION,
2312 WF_CACHE.ActivityAttributes(waaIND).ACTIVITY_ITEM_TYPE,
2313 WF_CACHE.ActivityAttributes(waaIND).ACTIVITY_NAME,
2314 WF_CACHE.ActivityAttributes(waaIND).ACTIVITY_VERSION,
2315 WF_CACHE.ActivityAttributes(waaIND).NAME,
2316 WF_CACHE.ActivityAttributes(waaIND).TYPE,
2317 WF_CACHE.ActivityAttributes(waaIND).SUBTYPE,
2318 WF_CACHE.ActivityAttributes(waaIND).FORMAT,
2319 WF_CACHE.ProcessActivities(actid).PROCESS_ITEM_TYPE,
2320 WF_CACHE.ProcessActivities(actid).PROCESS_NAME,
2321 WF_CACHE.ProcessActivities(actid).PROCESS_VERSION,
2322 WF_CACHE.ProcessActivities(actid).ACTIVITY_ITEM_TYPE,
2323 WF_CACHE.ProcessActivities(actid).ACTIVITY_NAME,
2324 WF_CACHE.ProcessActivities(actid).INSTANCE_ID,
2325 WF_CACHE.ProcessActivities(actid).INSTANCE_LABEL,
2326 WF_CACHE.ProcessActivities(actid).PERFORM_ROLE,
2327 WF_CACHE.ProcessActivities(actid).PERFORM_ROLE_TYPE,
2328 WF_CACHE.ProcessActivities(actid).START_END,
2329 WF_CACHE.ProcessActivities(actid).DEFAULT_RESULT
2330
2331 from WF_ACTIVITY_ATTRIBUTES WAA, WF_PROCESS_ACTIVITIES WPA,
2332 WF_ACTIVITIES WA
2333
2334 where WPA.INSTANCE_ID = actid
2335 and WA.ITEM_TYPE = WPA.ACTIVITY_ITEM_TYPE
2336 and WA.NAME = WPA.ACTIVITY_NAME
2337 and actdate >= WA.BEGIN_DATE
2338 and actdate < NVL(WA.END_DATE, actdate+1)
2339 and WAA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE
2340 and WAA.ACTIVITY_NAME = WA.NAME
2341 and WAA.ACTIVITY_VERSION = WA.VERSION
2342 and WAA.NAME = aname;
2343
2344 --Get the proper hash key and copy the temporary records into the
2345 --proper locations.
2346 waIND := WF_CACHE.HashKey(itemType ||
2347 WF_CACHE.ProcessActivities(actid).ACTIVITY_NAME);
2348
2349 WF_CACHE.Activities(waIND) := WF_CACHE.Activities(0);
2350
2351 waaIND := WF_CACHE.HashKey(itemType || aname ||
2352 WF_CACHE.ProcessActivities(actid).ACTIVITY_NAME);
2353
2354 WF_CACHE.ActivityAttributes(waaIND) :=
2355 WF_CACHE.ActivityAttributes(0);
2356
2357 end if;
2358
2359 exception
2360 when no_data_found then
2361 -- This is an unvalidated runtime attr.
2362 -- Treat it as a varchar2.
2363 -- We know that the activity and process activity should be retrievable.
2364 -- We will build a unvalidated runtime attr in cache. First we need to
2365 -- validate that we have the correct activity and process activity cached
2366
2367 WF_CACHE.GetProcessActivityInfo(actid, actdate, status, waIND);
2368
2369 if (status <> WF_CACHE.task_SUCCESS) then
2370 waIND := 0;
2371
2372 select WA.ITEM_TYPE, WA.NAME, WA.VERSION, WA.TYPE, WA.RERUN,
2373 WA.EXPAND_ROLE, WA.COST, WA.ERROR_ITEM_TYPE, WA.ERROR_PROCESS,
2374 WA.FUNCTION, WA.FUNCTION_TYPE, WA.MESSAGE, WA.BEGIN_DATE,
2375 WA.END_DATE, WA.DIRECTION, WPA.PROCESS_ITEM_TYPE,
2376 WPA.PROCESS_NAME, WPA.PROCESS_VERSION, WPA.ACTIVITY_ITEM_TYPE,
2377 WPA.ACTIVITY_NAME, WPA.INSTANCE_ID, WPA.INSTANCE_LABEL,
2378 WPA.PERFORM_ROLE, WPA.PERFORM_ROLE_TYPE, WPA.START_END,
2379 WPA.DEFAULT_RESULT
2380
2381 into WF_CACHE.Activities(waIND).ITEM_TYPE,
2382 WF_CACHE.Activities(waIND).NAME,
2383 WF_CACHE.Activities(waIND).VERSION,
2384 WF_CACHE.Activities(waIND).TYPE,
2385 WF_CACHE.Activities(waIND).RERUN,
2386 WF_CACHE.Activities(waIND).EXPAND_ROLE,
2387 WF_CACHE.Activities(waIND).COST,
2388 WF_CACHE.Activities(waIND).ERROR_ITEM_TYPE,
2389 WF_CACHE.Activities(waIND).ERROR_PROCESS,
2390 WF_CACHE.Activities(waIND).FUNCTION,
2391 WF_CACHE.Activities(waIND).FUNCTION_TYPE,
2392 WF_CACHE.Activities(waIND).MESSAGE,
2393 WF_CACHE.Activities(waIND).BEGIN_DATE,
2394 WF_CACHE.Activities(waIND).END_DATE,
2395 WF_CACHE.Activities(waIND).DIRECTION,
2396 WF_CACHE.ProcessActivities(actid).PROCESS_ITEM_TYPE,
2397 WF_CACHE.ProcessActivities(actid).PROCESS_NAME,
2398 WF_CACHE.ProcessActivities(actid).PROCESS_VERSION,
2399 WF_CACHE.ProcessActivities(actid).ACTIVITY_ITEM_TYPE,
2400 WF_CACHE.ProcessActivities(actid).ACTIVITY_NAME,
2401 WF_CACHE.ProcessActivities(actid).INSTANCE_ID,
2402 WF_CACHE.ProcessActivities(actid).INSTANCE_LABEL,
2403 WF_CACHE.ProcessActivities(actid).PERFORM_ROLE,
2404 WF_CACHE.ProcessActivities(actid).PERFORM_ROLE_TYPE,
2405 WF_CACHE.ProcessActivities(actid).START_END,
2406 WF_CACHE.ProcessActivities(actid).DEFAULT_RESULT
2407
2408 from WF_PROCESS_ACTIVITIES WPA, WF_ACTIVITIES WA
2409
2410 where WPA.INSTANCE_ID = actid
2411 and WA.ITEM_TYPE = WPA.ACTIVITY_ITEM_TYPE
2412 and WA.NAME = WPA.ACTIVITY_NAME
2413 and actdate >= WA.BEGIN_DATE
2414 and actdate < NVL(WA.END_DATE, actdate+1);
2415
2416 end if;
2417
2418 waIND := WF_CACHE.HashKey(itemType ||
2419 WF_CACHE.ProcessActivities(actid).ACTIVITY_NAME);
2420
2421 waaIND := WF_CACHE.HashKey(itemType || aname ||
2422 WF_CACHE.ProcessActivities(actid).ACTIVITY_NAME);
2423
2424 WF_CACHE.Activities(waIND) := WF_CACHE.Activities(0);
2425
2426 WF_CACHE.ActivityAttributes(waaIND).ACTIVITY_ITEM_TYPE :=
2427 WF_CACHE.Activities(waIND).ITEM_TYPE;
2428 WF_CACHE.ActivityAttributes(waaIND).ACTIVITY_NAME :=
2429 WF_CACHE.Activities(waIND).NAME;
2430 WF_CACHE.ActivityAttributes(waaIND).ACTIVITY_VERSION :=
2431 WF_CACHE.Activities(waIND).VERSION;
2432 WF_CACHE.ActivityAttributes(waaIND).NAME := aname;
2433 WF_CACHE.ActivityAttributes(waaIND).TYPE := 'VARCHAR2';
2434 WF_CACHE.ActivityAttributes(waaIND).SUBTYPE := '';
2435 WF_CACHE.ActivityAttributes(waaIND).FORMAT := '';
2436
2437 end;
2438
2439 -- Format return value as needed for text/number/date type.
2440 if (WF_CACHE.ActivityAttributes(waaIND).TYPE = 'NUMBER') then
2441 if (WF_CACHE.ActivityAttributes(waaIND).FORMAT <> '') then
2442 return(to_char(WF_CACHE.ActivityAttrValues(waavIND).NUMBER_VALUE,
2443 WF_CACHE.ActivityAttributes(waaIND).FORMAT));
2444
2445 else
2446 return(to_char(WF_CACHE.ActivityAttrValues(waavIND).NUMBER_VALUE));
2447
2448 end if;
2449
2450 elsif (WF_CACHE.ActivityAttributes(waaIND).TYPE = 'DATE') then
2451 if (WF_CACHE.ActivityAttributes(waaIND).FORMAT <> '') then
2452 return(to_char(WF_CACHE.ActivityAttrValues(waavIND).DATE_VALUE,
2453 WF_CACHE.ActivityAttributes(waaIND).FORMAT));
2454
2455 else
2456 return(to_char(WF_CACHE.ActivityAttrValues(waavIND).DATE_VALUE));
2457
2458 end if;
2459
2460 else
2461 -- VARCHAR2, LOOKUP, FORM, URL, DOCUMENT.
2462 -- Set the text value directly with no translation.
2463 return(WF_CACHE.ActivityAttrValues(waavIND).TEXT_VALUE);
2464
2465 end if;
2466
2467 exception
2468 when no_data_found then
2469 --Check to ensure that cursor is not open
2470 if (curs_activityattr%ISOPEN) then
2471 CLOSE curs_activityattr;
2472 end if;
2473
2474 if (ignore_notfound) then
2475
2476 return(null);
2477
2478 else
2479
2480 Wf_Core.Context('Wf_Engine', 'GetActivityAttrText', itemtype, itemkey,
2481 to_char(actid), aname);
2482 Wf_Core.Token('TYPE', itemtype);
2483 Wf_Core.Token('KEY', itemkey);
2484 Wf_Core.Token('ACTIVITY', to_char(actid));
2485 Wf_Core.Token('ATTRIBUTE', aname);
2486 Wf_Core.Raise('WFENG_ACTIVITY_ATTR');
2487
2488 end if;
2489
2490 when others then
2491 --Check to ensure that cursor is not open
2492 if (curs_activityattr%ISOPEN) then
2493 CLOSE curs_activityattr;
2494 end if;
2495
2496 Wf_Core.Context('Wf_Engine', 'GetActivityAttrText', itemtype, itemkey,
2497 to_char(actid), aname);
2498 raise;
2499 end GetActivityAttrText;
2500
2501 --
2502 -- GetActivityAttrNumber (PUBLIC)
2503 -- Get the value of a number item attribute.
2504 -- Attribute must be a NUMBER-type attribute.
2505 -- IN:
2506 -- itemtype - Item type
2507 -- itemkey - Item key
2508 -- actid - Process activity id
2509 -- aname - Attribute Name
2510 -- RETURNS:
2511 -- Attribute value
2512 --
2513 function GetActivityAttrNumber(itemtype in varchar2,
2514 itemkey in varchar2,
2515 actid in number,
2516 aname in varchar2,
2517 ignore_notfound in boolean)
2518
2519 return number is
2520
2521 waavIND NUMBER;
2522 status PLS_INTEGER;
2523
2524 begin
2525 -- Check Arguments
2526 if ((itemtype is null) or
2527 (itemkey is null) or
2528 (actid is null) or
2529 (aname is null)) then
2530 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
2531 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
2532 Wf_Core.Token('ACTID', nvl(actid, 'NULL'));
2533 Wf_Core.Token('ANAME', nvl(aname, 'NULL'));
2534 Wf_Core.Raise('WFSQL_ARGS');
2535 end if;
2536
2537 WF_CACHE.GetActivityAttrValue(actid, aname, status, waavIND);
2538
2539 if (status <> WF_CACHE.task_SUCCESS) then
2540 open curs_activityattr (actid, aname);
2541 fetch curs_activityattr into WF_CACHE.ActivityAttrValues(waavIND);
2542 close curs_activityattr;
2543 end if;
2544
2545 -- If it is a reference, replace lvalue with value of itemattr.
2546 if (WF_CACHE.ActivityAttrValues(waavIND).VALUE_TYPE = 'ITEMATTR') then
2547 return(GetItemAttrNumber(itemtype, itemkey,
2548 substrb(WF_CACHE.ActivityAttrValues(waavIND).TEXT_VALUE, 1, 30)));
2549
2550 else
2551 return(WF_CACHE.ActivityAttrValues(waavIND).NUMBER_VALUE);
2552
2553 end if;
2554
2555 exception
2556 when no_data_found then
2557 --Check to ensure that cursor is not open
2558 if (curs_activityattr%ISOPEN) then
2559 CLOSE curs_activityattr;
2560 end if;
2561
2562 if (ignore_notfound) then
2563 WF_CACHE.ActivityAttrValues(waavIND).PROCESS_ACTIVITY_ID := actid;
2564 WF_CACHE.ActivityAttrValues(waavIND).NAME := aname;
2565 WF_CACHE.ActivityAttrValues(waavIND).VALUE_TYPE := 'CONSTANT';
2566 WF_CACHE.ActivityAttrValues(waavIND).TEXT_VALUE := '';
2567 WF_CACHE.ActivityAttrValues(waavIND).NUMBER_VALUE := '';
2568 WF_CACHE.ActivityAttrValues(waavIND).DATE_VALUE := to_date(NULL);
2569
2570 return null;
2571
2572 else
2573
2574 Wf_Core.Context('Wf_Engine', 'GetActivityAttrNumber', itemtype, itemkey,
2575 to_char(actid), aname);
2576 Wf_Core.Token('TYPE', itemtype);
2577 Wf_Core.Token('KEY', itemkey);
2578 Wf_Core.Token('ACTIVITY', to_char(actid));
2579 Wf_Core.Token('ATTRIBUTE', aname);
2580 Wf_Core.Raise('WFENG_ACTIVITY_ATTR');
2581
2582 end if;
2583
2584 when others then
2585 --Check to ensure that cursor is not open
2586 if (curs_activityattr%ISOPEN) then
2587 CLOSE curs_activityattr;
2588 end if;
2589
2590 Wf_Core.Context('Wf_Engine', 'GetActivityAttrNumber', itemtype, itemkey,
2591 to_char(actid), aname);
2592 raise;
2593 end GetActivityAttrNumber;
2594
2595 --
2596 -- GetActivityAttrDate (PUBLIC)
2597 -- Get the value of a date item attribute.
2598 -- Attribute must be a DATE-type attribute.
2599 -- IN:
2600 -- itemtype - Item type
2601 -- itemkey - Item key
2602 -- actid - Process activity id
2603 -- aname - Attribute Name
2604 -- RETURNS:
2605 -- Attribute value
2606 --
2607 function GetActivityAttrDate(itemtype in varchar2,
2608 itemkey in varchar2,
2609 actid in number,
2610 aname in varchar2,
2611 ignore_notfound in boolean)
2612 return date is
2613
2614 waavIND NUMBER;
2615 status PLS_INTEGER;
2616
2617 begin
2618 -- Check Arguments
2619 if ((itemtype is null) or
2620 (itemkey is null) or
2621 (actid is null) or
2622 (aname is null)) then
2623 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
2624 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
2625 Wf_Core.Token('ACTID', nvl(actid, 'NULL'));
2626 Wf_Core.Token('ANAME', nvl(aname, 'NULL'));
2627 Wf_Core.Raise('WFSQL_ARGS');
2628 end if;
2629
2630 -- First check value_type flag for possible item_attribute ref.
2631 WF_CACHE.GetActivityAttrValue(actid, aname, status, waavIND);
2632
2633 if (status <> WF_CACHE.task_SUCCESS) then
2634 open curs_activityattr (actid, aname);
2635 fetch curs_activityattr into WF_CACHE.ActivityAttrValues(waavIND);
2636 close curs_activityattr;
2637 end if;
2638
2639 -- If it is a reference, get the item attribute and return it.
2640 if (WF_CACHE.ActivityAttrValues(waavIND).VALUE_TYPE = 'ITEMATTR') then
2641 return(GetItemAttrDate(itemtype, itemkey,
2642 substrb(WF_CACHE.ActivityAttrValues(waavIND).TEXT_VALUE, 1, 30)));
2643
2644 else
2645 return(WF_CACHE.ActivityAttrValues(waavIND).DATE_VALUE);
2646
2647 end if;
2648
2649 exception
2650 when no_data_found then
2651 --Check to ensure that cursor is not open
2652 if (curs_activityattr%ISOPEN) then
2653 CLOSE curs_activityattr;
2654 end if;
2655
2656 if (ignore_notfound) then
2657 WF_CACHE.ActivityAttrValues(waavIND).PROCESS_ACTIVITY_ID := actid;
2658 WF_CACHE.ActivityAttrValues(waavIND).NAME := aname;
2659 WF_CACHE.ActivityAttrValues(waavIND).VALUE_TYPE := 'CONSTANT';
2660 WF_CACHE.ActivityAttrValues(waavIND).TEXT_VALUE := '';
2661 WF_CACHE.ActivityAttrValues(waavIND).NUMBER_VALUE := '';
2662 WF_CACHE.ActivityAttrValues(waavIND).DATE_VALUE := to_date(NULL);
2663 return(null);
2664
2665 else
2666
2667 Wf_Core.Context('Wf_Engine', 'GetActivityAttrDate', itemtype, itemkey,
2668 to_char(actid), aname);
2669 Wf_Core.Token('TYPE', itemtype);
2670 Wf_Core.Token('KEY', itemkey);
2671 Wf_Core.Token('ACTIVITY', to_char(actid));
2672 Wf_Core.Token('ATTRIBUTE', aname);
2673 Wf_Core.Raise('WFENG_ACTIVITY_ATTR');
2674
2675 end if;
2676
2677 when others then
2678 --Check to ensure that cursor is not open
2679 if (curs_activityattr%ISOPEN) then
2680 CLOSE curs_activityattr;
2681 end if;
2682
2683 Wf_Core.Context('Wf_Engine', 'GetActivityAttrDate', itemtype, itemkey,
2684 to_char(actid), aname);
2685 raise;
2686 end GetActivityAttrDate;
2687
2688 --
2689 -- GetActivityAttrClob (PUBLIC)
2690 -- Get display contents of activity attribute as a clob
2691 -- NOTE
2692 -- Returns expanded content of attribute.
2693 -- For DOCUMENT-type attributes, this will be the actual document
2694 -- generated. For all other types, this will be the displayed
2695 -- value of the attribute.
2696 -- Use GetActivityAttrText to retrieve internal key.
2697 -- IN
2698 -- itemtype - item type
2699 -- itemkey - item key
2700 -- aname - activity attribute name
2701 -- RETURNS
2702 -- Expanded content of activity attribute as a clob
2703 --
2704 function GetActivityAttrClob(
2705 itemtype in varchar2,
2706 itemkey in varchar2,
2707 actid in number,
2708 aname in varchar2)
2709 return clob
2710 is
2711 atype varchar2(8);
2712 format varchar2(240);
2713 value varchar2(32000);
2714 actdate date;
2715 tempclob clob;
2716
2717 waavIND NUMBER;
2718 status PLS_INTEGER;
2719
2720 begin
2721 -- Check Arguments
2722 if ((itemtype is null) or
2723 (itemkey is null) or
2724 (actid is null) or
2725 (aname is null)) then
2726 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
2727 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
2728 Wf_Core.Token('ACTID', nvl(actid, 'NULL'));
2729 Wf_Core.Token('ANAME', nvl(aname, 'NULL'));
2730 Wf_Core.Raise('WFSQL_ARGS');
2731 end if;
2732
2733 -- First check value_type flag for possible item_attribute ref.
2734 WF_CACHE.GetActivityAttrValue(actid, aname, status, waavIND);
2735
2736 if (status <> WF_CACHE.task_SUCCESS) then
2737 open curs_activityattr (actid, aname);
2738 fetch curs_activityattr into WF_CACHE.ActivityAttrValues(waavIND);
2739 close curs_activityattr;
2740 end if;
2741
2742 -- If it is a reference, return value of item_attr instead of
2743 -- contents of WAAV.
2744 if (WF_CACHE.ActivityAttrValues(waavIND).VALUE_TYPE = 'ITEMATTR') then
2745 if (WF_CACHE.ActivityAttrValues(waavIND).TEXT_VALUE is null) then
2746 return(null); -- Null itemattr means null value, not an error
2747
2748 else
2749 return(GetItemAttrClob(itemtype, itemkey,
2750 substrb(WF_CACHE.ActivityAttrValues(waavIND).TEXT_VALUE, 1, 30)));
2751
2752 end if;
2753 end if;
2754
2755 -- Make fake clob to hold result
2756 dbms_lob.createtemporary(tempclob, TRUE, dbms_lob.session);
2757
2758 -- Build clob with contents based on attr type
2759 if (atype = '###NOTDONE') then
2760 -- Parse out document subtypes
2761 null;
2762 else
2763 -- All others just use text value
2764 value := WF_Engine.GetActivityAttrText(itemtype, itemkey, actid, aname);
2765 end if;
2766
2767 -- Write value to fake clob and return
2768 dbms_lob.write(tempclob, lengthb(value), 1, value);
2769 return(tempclob);
2770
2771 exception
2772 when others then
2773 --Check to ensure that cursor is not open
2774 if (curs_activityattr%ISOPEN) then
2775 CLOSE curs_activityattr;
2776 end if;
2777
2778 Wf_Core.Context('Wf_Engine', 'GetActivityAttrClob', itemtype,
2779 itemkey, to_char(actid), aname);
2780 raise;
2781 end GetActivityAttrClob;
2782
2783 --
2784 -- GetActivityAttrEvent
2785 -- Get event-type activity attribute
2786 -- IN
2787 -- itemtype - process item type
2788 -- itemkey - process item key
2789 -- actid - current activity id
2790 -- name - attribute name
2791 -- RETURNS
2792 -- Attribute value
2793 --
2794 function GetActivityAttrEvent(
2795 itemtype in varchar2,
2796 itemkey in varchar2,
2797 actid in number,
2798 name in varchar2)
2799 return wf_event_t
2800 is
2801 waavIND NUMBER;
2802 status PLS_INTEGER;
2803
2804 begin
2805 -- Check Arguments
2806 if ((itemtype is null) or
2807 (itemkey is null) or
2808 (actid is null) or
2809 (name is null)) then
2810 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
2811 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
2812 Wf_Core.Token('ACTID', nvl(actid, 'NULL'));
2813 Wf_Core.Token('NAME', nvl(name, 'NULL'));
2814 Wf_Core.Raise('WFSQL_ARGS');
2815 end if;
2816
2817 -- First check value_type flag for possible item_attribute ref.
2818
2819 -- First check value_type flag for possible item_attribute ref.
2820 WF_CACHE.GetActivityAttrValue(actid, name, status, waavIND);
2821
2822 if (status <> WF_CACHE.task_SUCCESS) then
2823 open curs_activityattr (actid, GetActivityAttrEvent.name);
2824 fetch curs_activityattr into WF_CACHE.ActivityAttrValues(waavIND);
2825 close curs_activityattr;
2826 end if;
2827
2828 -- If it is a reference, replace lvalue with value of itemattr.
2829 if (WF_CACHE.ActivityAttrValues(waavIND).VALUE_TYPE = 'ITEMATTR') then
2830 return(GetItemAttrEvent(itemtype, itemkey,
2831 substrb(WF_CACHE.ActivityAttrValues(waavIND).TEXT_VALUE, 1, 30)));
2832
2833 else
2834 -- Only itemattr-type activity event attrs are supported
2835 return NULL;
2836
2837 end if;
2838
2839 exception
2840 when no_data_found then
2841 --Check to ensure that cursor is not open
2842 if (curs_activityattr%ISOPEN) then
2843 CLOSE curs_activityattr;
2844 end if;
2845
2846 Wf_Core.Context('Wf_Engine', 'GetActivityAttrEvent', itemtype, itemkey,
2847 to_char(actid), name);
2848 Wf_Core.Token('TYPE', itemtype);
2849 Wf_Core.Token('KEY', itemkey);
2850 Wf_Core.Token('ACTIVITY', to_char(actid));
2851 Wf_Core.Token('ATTRIBUTE', name);
2852 Wf_Core.Raise('WFENG_ACTIVITY_ATTR');
2853
2854 when others then
2855 --Check to ensure that cursor is not open
2856 if (curs_activityattr%ISOPEN) then
2857 CLOSE curs_activityattr;
2858 end if;
2859
2860 Wf_Core.Context('Wf_Engine', 'GetActivityAttrEvent', itemtype,
2861 itemkey, to_char(actid), name);
2862 raise;
2863 end GetActivityAttrEvent;
2864
2865 --
2866 -- Set_Item_Parent (PUBLIC)
2867 -- *** OBSOLETE - Use SetItemParent instead ***
2868 --
2869 procedure Set_Item_Parent(itemtype in varchar2,
2870 itemkey in varchar2,
2871 parent_itemtype in varchar2,
2872 parent_itemkey in varchar2,
2873 parent_context in varchar2)
2874 is
2875 begin
2876 -- Not allowed in synch mode
2877 if (itemkey = wf_engine.eng_synch) then
2878 wf_core.token('OPERATION', 'Wf_Engine.Set_Item_Parent');
2879 wf_core.raise('WFENG_SYNCH_DISABLED');
2880 end if;
2881
2882 Wf_Item.Set_Item_Parent(itemtype, itemkey, parent_itemtype, parent_itemkey,
2883 parent_context);
2884 exception
2885 when others then
2886 Wf_Core.Context('Wf_Engine', 'Set_Item_Parent', itemtype, itemkey,
2887 parent_itemtype, parent_itemkey, parent_context);
2888 raise;
2889 end Set_Item_Parent;
2890
2891 --
2892 -- SetItemParent (PUBLIC)
2893 -- Set the parent info of an item
2894 -- IN
2895 -- itemtype - Item type
2896 -- itemkey - Item key
2897 -- parent_itemtype - Itemtype of parent
2898 -- parent_itemkey - Itemkey of parent
2899 -- parent_context - Context info about parent
2900 -- masterdetail - Signal if the two flows are coordinated.
2901 --
2902 procedure SetItemParent(itemtype in varchar2,
2903 itemkey in varchar2,
2904 parent_itemtype in varchar2,
2905 parent_itemkey in varchar2,
2906 parent_context in varchar2,
2907 masterdetail in boolean)
2908 is
2909 begin
2910 -- Check Arguments
2911 if ((itemtype is null) or
2912 (itemkey is null) or
2913 (parent_itemtype is null) or
2914 (parent_itemkey is null)) then
2915 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
2916 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
2917 Wf_Core.Token('PARENT_ITEMTYPE', nvl(parent_itemtype, 'NULL'));
2918 Wf_Core.Token('PARENT_ITEMKEY', nvl(parent_itemkey, 'NULL'));
2919 Wf_Core.Raise('WFSQL_ARGS');
2920
2921 end if;
2922
2923 -- Not allowed in synch mode
2924 if (itemkey = wf_engine.eng_synch) then
2925 wf_core.token('OPERATION', 'Wf_Engine.SetItemParent');
2926 wf_core.raise('WFENG_SYNCH_DISABLED');
2927 end if;
2928
2929 Wf_Item.Set_Item_Parent(itemtype, itemkey, parent_itemtype,
2930 parent_itemkey, parent_context, masterdetail);
2931 exception
2932 when others then
2933 Wf_Core.Context('Wf_Engine', 'SetItemParent', itemtype, itemkey,
2934 parent_itemtype, parent_itemkey, parent_context);
2935 raise;
2936 end SetItemParent;
2937
2938 --
2939 -- SetItemOwner (PUBLIC)
2940 -- Set the owner of an item
2941 -- IN
2942 -- itemtype - Item type
2943 -- itemkey - Item key
2944 -- owner - Role designated as owner of the item
2945 --
2946 procedure SetItemOwner(
2947 itemtype in varchar2,
2948 itemkey in varchar2,
2949 owner in varchar2)
2950 is
2951 begin
2952 -- Check Arguments
2953 if ((itemtype is null) or
2954 (itemkey is null)) then
2955 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
2956 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
2957 Wf_Core.Raise('WFSQL_ARGS');
2958
2959 end if;
2960
2961 -- Not allowed in synch mode
2962 if (itemkey = wf_engine.eng_synch) then
2963 wf_core.token('OPERATION', 'Wf_Engine.SetItemOwner');
2964 wf_core.raise('WFENG_SYNCH_DISABLED');
2965 end if;
2966
2967 Wf_Item.SetItemOwner(itemtype, itemkey, owner);
2968 exception
2969 when others then
2970 Wf_Core.Context('Wf_Engine', 'SetItemOwner', itemtype, itemkey,
2971 owner);
2972 raise;
2973 end SetItemOwner;
2974
2975 --
2976 -- GetItemUserKey (PUBLIC)
2977 -- Get the user key of an item
2978 -- IN
2979 -- itemtype - Item type
2980 -- itemkey - Item key
2981 -- RETURNS
2982 -- User key of the item
2983 --
2984 function GetItemUserKey(
2985 itemtype in varchar2,
2986 itemkey in varchar2)
2987 return varchar2
2988 is
2989 begin
2990 -- Check Arguments
2991 if ((itemtype is null) or
2992 (itemkey is null)) then
2993 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
2994 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
2995 Wf_Core.Raise('WFSQL_ARGS');
2996 end if;
2997
2998 return(Wf_Item.GetItemUserKey(itemtype, itemkey));
2999 exception
3000 when others then
3001 Wf_Core.Context('Wf_Engine', 'GetItemUserKey', itemtype, itemkey);
3002 raise;
3003 end GetItemUserKey;
3004
3005 --
3006 -- SetItemUserKey (PUBLIC)
3007 -- Set the user key of an item
3008 -- IN
3009 -- itemtype - Item type
3010 -- itemkey - Item key
3011 -- userkey - User key to be set
3012 --
3013 procedure SetItemUserKey(
3014 itemtype in varchar2,
3015 itemkey in varchar2,
3016 userkey in varchar2)
3017 is
3018 begin
3019 -- Check Arguments
3020 if ((itemtype is null) or
3021 (itemkey is null)) then
3022 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
3023 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
3024 Wf_Core.Raise('WFSQL_ARGS');
3025
3026 end if;
3027
3028 -- Not allowed in synch mode
3029 if (itemkey = wf_engine.eng_synch) then
3030 wf_core.token('OPERATION', 'Wf_Engine.SetItemUserKey');
3031 wf_core.raise('WFENG_SYNCH_DISABLED');
3032 end if;
3033
3034 Wf_Item.SetItemUserKey(itemtype, itemkey, userkey);
3035 exception
3036 when others then
3037 Wf_Core.Context('Wf_Engine', 'SetItemUserKey', itemtype, itemkey,
3038 userkey);
3039 raise;
3040 end SetItemUserKey;
3041
3042 --
3043 -- GetActivityLabel (PUBLIC)
3044 -- Get activity instance label given id, in a format
3045 -- suitable for passing to other wf_engine apis.
3046 -- IN
3047 -- actid - activity instance id
3048 -- RETURNS
3049 -- <process_name>||':'||<instance_label>
3050 --
3051 function GetActivityLabel(
3052 actid in number)
3053 return varchar2
3054 is
3055
3056 status PLS_INTEGER;
3057
3058 begin
3059 -- Check Arguments
3060 if (actid is null) then
3061 Wf_Core.Token('ACTID', nvl(actid, 'NULL'));
3062 Wf_Core.Raise('WFSQL_ARGS');
3063 end if;
3064
3065 WF_CACHE.GetProcessActivity(actid, status);
3066
3067 if (status <> WF_CACHE.task_SUCCESS) then
3068
3069 select WPA.PROCESS_ITEM_TYPE, WPA.PROCESS_NAME, WPA.PROCESS_VERSION,
3070 WPA.ACTIVITY_ITEM_TYPE, WPA.ACTIVITY_NAME, WPA.INSTANCE_ID,
3071 WPA.INSTANCE_LABEL, WPA.PERFORM_ROLE, WPA.PERFORM_ROLE_TYPE,
3072 WPA.START_END, WPA.DEFAULT_RESULT
3073 into WF_CACHE.ProcessActivities(actid)
3074 from WF_PROCESS_ACTIVITIES WPA
3075 where WPA.INSTANCE_ID = GetActivityLabel.actid;
3076
3077 end if;
3078
3079 return(WF_CACHE.ProcessActivities(actid).PROCESS_NAME || ':' ||
3080 WF_CACHE.ProcessActivities(actid).INSTANCE_LABEL);
3081
3082 exception
3083 when no_data_found then
3084 Wf_Core.Context('Wf_Engine', 'GetActivityLabel', to_char(actid));
3085 Wf_Core.Token('ACTID', to_char(actid));
3086 Wf_Core.Raise('WFENG_ACTID');
3087 when others then
3088 Wf_Core.Context('Wf_Engine', 'GetActivityLabel', to_char(actid));
3089 raise;
3090 end GetActivityLabel;
3091
3092 -- Bug 2376033
3093 -- Overloads the previous API with an additional event type parmeter
3094 --
3095 -- CB (PUBLIC)
3096 -- This is the callback function used by the notification system to
3097 -- get and set process attributes, and mark a process complete.
3098 --
3099 -- The command may be one of:
3100 -- GET - Get the value of an attribute
3101 -- SET - Set the value of an attribute
3102 -- COMPLETE - Mark the activity as complete
3103 -- ERROR - Mark the activity as error status
3104 -- TESTCTX - Test current context via selector function
3105 -- FORWARD - Execute notification function for FORWARD
3106 -- TRANSFER - Execute notification function for TRANSFER
3107 -- RESPOND - Execute notification function for RESPOND
3108 --
3109 -- The context is in the format <itemtype>:<itemkey>:<activityid>.
3110 --
3111 -- The text_value/number_value/date_value fields are mutually exclusive.
3112 -- It is assumed that only one will be used, depending on the value of
3113 -- the attr_type argument ('VARCHAR2', 'NUMBER', or 'DATE').
3114 --
3115 -- IN:
3116 -- command - Action requested. Must be one of 'GET', 'SET', or 'COMPLETE'.
3117 -- context - Context data in the form '<item_type>:<item_key>:<activity>'
3118 -- attr_name - Attribute name to set/get for 'GET' or 'SET'
3119 -- attr_type - Attribute type for 'SET'
3120 -- text_value - Text Attribute value for 'SET'
3121 -- number_value - Number Attribute value for 'SET'
3122 -- date_value - Date Attribute value for 'SET'
3123 -- OUT:
3124 -- text_value - Text Attribute value for 'GET'
3125 -- number_value - Number Attribute value for 'GET'
3126 -- date_value - Date Attribute value for 'GET'
3127 -- event_value - Event Attribute value for 'GET'
3128 --
3129 --No locking logic right now
3130 --Locking at the item level is implemented in all cases
3131 --where there is chances that status o fthe activity is being
3132 --changed and there may be simultaneous access.
3133
3134 procedure CB(command in varchar2,
3135 context in varchar2,
3136 attr_name in varchar2,
3137 attr_type in varchar2,
3138 text_value in out NOCOPY varchar2,
3139 number_value in out NOCOPY number,
3140 date_value in out NOCOPY date,
3141 event_value in out nocopy wf_event_t)
3142 is
3143 firstcolon pls_integer;
3144 secondcolon pls_integer;
3145
3146 itemtype varchar2(8);
3147 itemkey varchar2(240);
3148 actid pls_integer;
3149 status varchar2(8);
3150 result varchar2(2000);
3151
3152 message varchar2(30);
3153 msgtype varchar2(8);
3154 expand_role varchar2(1);
3155
3156 wf_invalid_command exception;
3157 wf_invalid_argument exception;
3158
3159 trig_savepoint exception;
3160 pragma exception_init(trig_savepoint, -04092);
3161 dist_savepoint exception;
3162 pragma exception_init(dist_savepoint, -02074);
3163
3164 begin
3165 --
3166 -- Argument validation
3167 --
3168 if (command is null) then
3169 raise wf_invalid_command;
3170 end if;
3171 if (context is null) then
3172 raise wf_invalid_argument;
3173 end if;
3174
3175 --
3176 -- Take the context apart and extract item_type and
3177 -- item_key from it.
3178 --
3179 firstcolon := instr(context, ':', 1,1);
3180 secondcolon := instr(context, ':', -1,1);
3181
3182 if (firstcolon = 0 or secondcolon = 0) then
3183 raise wf_invalid_argument;
3184 end if;
3185
3186 itemtype := substr(context, 1, firstcolon - 1);
3187 itemkey := substr(context, firstcolon + 1, secondcolon - firstcolon - 1);
3188 actid := to_number(substr(context, secondcolon+1,
3189 length(context) - secondcolon));
3190
3191 -- Not allowed in synch mode
3192 if (itemkey = wf_engine.eng_synch) then
3193 wf_core.token('OPERATION', 'Wf_Engine.CB');
3194 wf_core.raise('WFENG_SYNCH_DISABLED');
3195 end if;
3196
3197 --
3198 -- Handle the command now ... Get value and type Return Null
3199 -- If the specified item not found ...
3200 -- Bug 2376033 Get event attribute value for Event attribute type
3201 --
3202 if (upper(command) = 'GET') then
3203 if (attr_type = 'NUMBER') then
3204 number_value := GetItemAttrNumber(itemtype, itemkey, attr_name);
3205 elsif (attr_type = 'DATE') then
3206 date_value := GetItemAttrDate(itemtype, itemkey, attr_name);
3207 elsif (attr_type = 'EVENT') then
3208 event_value := GetItemAttrEvent(itemtype, itemkey, attr_name);
3209 else
3210 text_value := GetItemAttrText(itemtype, itemkey, attr_name);
3211 end if;
3212 elsif (upper(command) = 'SET') then
3213 begin
3214 if (attr_type = 'NUMBER') then
3215 SetItemAttrNumber(itemtype, itemkey, attr_name, number_value);
3216 elsif (attr_type = 'DATE') then
3217 SetItemAttrDate(itemtype, itemkey, attr_name, date_value);
3218 elsif (attr_type = 'EVENT') then
3219 SetItemAttrEvent(itemtype, itemkey, attr_name, event_value);
3220 else
3221 SetItemAttrText(itemtype, itemkey, attr_name, text_value);
3222 end if;
3223 exception
3224 when OTHERS then
3225 -- If attr is not already defined, add a runtime attribute
3226 -- with this name, then try the set again.
3227 if (wf_core.error_name = 'WFENG_ITEM_ATTR') then
3228 if (attr_type = 'EVENT') then
3229 raise;
3230 end if;
3231 wf_core.clear;
3232 if (attr_type = 'NUMBER') then
3233 AddItemAttr(itemtype=>itemtype,
3234 itemkey=>itemkey,
3235 aname=>attr_name,
3236 number_value=>number_value);
3237 elsif (attr_type = 'DATE') then
3238 AddItemAttr(itemtype=>itemtype,
3239 itemkey=>itemkey,
3240 aname=>attr_name,
3241 date_value=>date_value);
3242 else
3243 AddItemAttr(itemtype=>itemtype,
3244 itemkey=>itemkey,
3245 aname=>attr_name,
3246 text_value=>text_value);
3247 end if;
3248 else
3249 raise;
3250 end if;
3251 end;
3252 elsif (upper(command) = wf_engine.eng_completed) then
3253 -- CB is signalling that a notification has completed.
3254 -- If the activity originating this notification still has ACTIVE
3255 -- status, then a routing rule (or some other kind of automatic
3256 -- processing) has completed the notification before the activity
3257 -- itself has finished. In this case, do NOT actually complete
3258 -- the activity and continue processing. Exit silently and let
3259 -- execute_activity() pick up the execution when the activity
3260 -- owning this notification is actually completed.
3261 Wf_Item_Activity_Status.Status(itemtype, itemkey,
3262 actid, status);
3263 if (status = wf_engine.eng_active) then
3264 -- Do nothing!!!
3265 return;
3266 end if;
3267
3268 -- ### DL: Trap rollback error for savepoint
3269 -- ### We do not trap the cases where we have trigger or distributed
3270 -- ### savepoint at this time, but we can. More testing is needed.
3271 -- ### Mainly we do not want to initiate error processing when those
3272 -- ### exceptions are caught.
3273
3274 -- Use the text_value passed in as the result code for the activity.
3275 result := text_value;
3276 begin
3277 savepoint wf_savepoint;
3278 if (WF_CACHE.MetaRefreshed) then
3279 NULL;
3280
3281 end if;
3282
3283 Wf_Engine_Util.Complete_Activity(itemtype, itemkey, actid, result);
3284 exception
3285 when trig_savepoint or dist_savepoint then
3286 -- Savepoint violation.
3287 -- Try without fancy error processing.
3288 Wf_Engine_Util.Complete_Activity(itemtype, itemkey, actid, result);
3289 when others then
3290 -- If anything in this process raises an exception:
3291 -- 1. rollback any work in this process thread
3292 -- 2. set this activity to error status
3293 -- 3. execute the error process (if any)
3294 -- 4. clear the error to continue with next activity
3295 rollback to wf_savepoint;
3296 --The rollback will be done in the when others block
3297 Wf_Core.Context('Wf_Engine', 'CB', command, context, attr_name,
3298 attr_type, ':'||text_value||':'||to_char(number_value)||':'||
3299 to_char(date_value)||':');
3300 Wf_Item_Activity_Status.Set_Error(itemtype,
3301 itemkey, actid, wf_engine.eng_exception, FALSE);
3302 Wf_Engine_Util.Execute_Error_Process(itemtype,
3303 itemkey, actid, wf_engine.eng_exception);
3304 Wf_Core.Clear;
3305 end;
3306 elsif (upper(command) = wf_engine.eng_error) then
3307
3308 -- Set the error status
3309 Wf_Item_Activity_Status.Set_Error(itemtype, itemkey, actid,
3310 wf_engine.eng_mail, FALSE);
3311 -- Run any error process for the activity
3312 Wf_Engine_Util.Execute_Error_Process(itemtype, itemkey, actid,
3313 wf_engine.eng_mail);
3314 elsif (upper(command) = 'TESTCTX') then
3315 -- Call selector function in test mode
3316 -- Return true if result is either true or null (means context
3317 -- test not implemented)
3318 result := Wf_Engine_Util.Execute_Selector_Function(itemtype,
3319 itemkey, wf_engine.eng_testctx);
3320 text_value := nvl(result, 'TRUE');
3321 elsif (upper(command) = 'SETCTX') then
3322 -- Call selector function in set mode
3323 result := Wf_Engine_Util.Execute_Selector_Function(itemtype,
3324 itemkey, wf_engine.eng_setctx);
3325 elsif (upper(command) in ('FORWARD', 'TRANSFER', 'RESPOND',
3326 'ANSWER', 'QUESTION', 'VALIDATE')) then
3327 -- FORWARD/TRANSFER/RESPOND/ANSWER/QUESTION/VALIDATE
3328 -- Look for a notification callback function to execute.
3329 -- NOTES:
3330 -- 1. For these modes, the value buffers must pass in the expected
3331 -- expected values:
3332 -- text_value = recipient_role (null for RESPOND)
3333 -- number_value = notification_id
3334 -- 2. The callback function will raise an exception if the
3335 -- operation isn't allowed. If so, allow the exception to raise
3336 -- up to the calling function.
3337
3338 Wf_Engine_Util.Execute_Notification_Callback(command, itemtype,
3339 itemkey, actid, number_value, text_value);
3340
3341 -- For TRANSFER mode only, reset the assigned user, but only
3342 -- if not a voting activity
3343 if (command = 'TRANSFER') then
3344 Wf_Activity.Notification_Info(itemtype, itemkey, actid,
3345 message, msgtype, expand_role);
3346 if (expand_role = 'N') then
3347 Wf_Item_Activity_Status.Update_Notification(itemtype, itemkey, actid,
3348 number_value, text_value);
3349 end if;
3350 end if;
3351 else
3352 raise wf_invalid_command;
3353 end if;
3354
3355 exception
3356 when wf_invalid_command then
3357 Wf_Core.Context('Wf_Engine', 'CB', command, context, attr_name, attr_type,
3358 ':'||text_value||':'||to_char(number_value)||':'||
3359 to_char(date_value)||':');
3360 Wf_Core.Token('COMMAND', command);
3361 Wf_Core.Raise('WFSQL_COMMAND');
3362
3363 when wf_invalid_argument then
3364 Wf_Core.Context('Wf_Engine', 'CB', command, context, attr_name, attr_type,
3365 ':'||text_value||':'||to_char(number_value)||':'||
3366 to_char(date_value)||':');
3367 Wf_Core.Token('CONTEXT', context);
3368 Wf_Core.Raise('WFSQL_ARGS');
3369
3370 when OTHERS then
3371 Wf_Core.Context('Wf_Engine', 'CB', command, context, attr_name, attr_type,
3372 ':'||text_value||':'||to_char(number_value)||':'||
3373 to_char(date_value)||':');
3374 raise;
3375 end CB;
3376
3377 -- Bug 2376033
3378 -- Transferred the logic to the overloaded CB with additional event attribute
3379 -- parameter. This calls the new CB with event paramter as null.
3380 -- CB (PUBLIC)
3381 -- This is the callback function used by the notification system to
3382 -- get and set process attributes, and mark a process complete.
3383 --
3384 -- The command may be one of:
3385 -- GET - Get the value of an attribute
3386 -- SET - Set the value of an attribute
3387 -- COMPLETE - Mark the activity as complete
3388 -- ERROR - Mark the activity as error status
3389 -- TESTCTX - Test current context via selector function
3390 -- FORWARD - Execute notification function for FORWARD
3391 -- TRANSFER - Execute notification function for TRANSFER
3392 -- RESPOND - Execute notification function for RESPOND
3393 --
3394 -- The context is in the format <itemtype>:<itemkey>:<activityid>.
3395 --
3396 -- The text_value/number_value/date_value fields are mutually exclusive.
3397 -- It is assumed that only one will be used, depending on the value of
3398 -- the attr_type argument ('VARCHAR2', 'NUMBER', or 'DATE').
3399 --
3400 -- IN:
3401 -- command - Action requested. Must be one of 'GET', 'SET', or 'COMPLETE'.
3402 -- context - Context data in the form '<item_type>:<item_key>:<activity>'
3403 -- attr_name - Attribute name to set/get for 'GET' or 'SET'
3404 -- attr_type - Attribute type for 'SET'
3405 -- text_value - Text Attribute value for 'SET'
3406 -- number_value - Number Attribute value for 'SET'
3407 -- date_value - Date Attribute value for 'SET'
3408 -- OUT:
3409 -- text_value - Text Attribute value for 'GET'
3410 -- number_value - Number Attribute value for 'GET'
3411 -- date_value - Date Attribute value for 'GET'
3412 --
3413
3414 procedure CB(command in varchar2,
3415 context in varchar2,
3416 attr_name in varchar2,
3417 attr_type in varchar2,
3418 text_value in out NOCOPY varchar2,
3419 number_value in out NOCOPY number,
3420 date_value in out NOCOPY date)
3421 is
3422 event_value wf_event_t;
3423 begin
3424
3425 Wf_Engine.CB(command, context, attr_name, attr_type, text_value, number_value, date_value, event_value);
3426
3427 exception
3428 when OTHERS then
3429 Wf_Core.Context('Wf_Engine', 'oldCB', command, context, attr_name, attr_type,
3430 ':'||text_value||':'||to_char(number_value)||':'||
3431 to_char(date_value)||':');
3432 raise;
3433
3434 end CB;
3435
3436 --
3437 -- ProcessDeferred (PUBLIC)
3438 -- Process all deferred activities
3439 -- IN
3440 -- itemtype - Item type to process. If null process all item types.
3441 -- minthreshold - Minimum cost activity to process. No minimum if null.
3442 -- maxthreshold - Maximum cost activity to process. No maximum if null.
3443 --
3444 procedure ProcessDeferred(itemtype in varchar2,
3445 minthreshold in number,
3446 maxthreshold in number) is
3447
3448
3449 begin
3450 wf_queue.ProcessDeferredQueue(itemtype, minthreshold, maxthreshold);
3451 exception
3452 when others then
3453 Wf_Core.Context('Wf_Engine', 'ProcessDeferred',itemtype,
3454 to_char(minthreshold), to_char(maxthreshold));
3455 raise;
3456 end ProcessDeferred;
3457
3458 --
3459 -- ProcessTimeout (PUBLIC)
3460 -- Pick up one timed out activity and execute timeout transition.
3461 -- IN
3462 -- itemtype - Item type to process. If null process all item types.
3463 --
3464 procedure ProcessTimeOut(itemtype in varchar2)
3465 is
3466 resource_busy exception;
3467 pragma exception_init(resource_busy, -00054);
3468
3469 l_itemtype varchar2(8);
3470 l_itemkey varchar2(240);
3471 l_actid pls_integer;
3472 pntfstatus varchar2(8);
3473 pntfresult varchar2(30);
3474
3475 -- Select one timeout activity that matches itemtype
3476 -- NOTE: Two separate cursors are used for itemtype and no-itemtype
3477 -- cases to get better execution plans.
3478
3479
3480 -- select everything but completed and error.
3481 -- avoid "not in" which disables index in RBO
3482 cursor curs_itype is
3483 select
3484 S.ROWID ROW_ID
3485 from WF_ITEM_ACTIVITY_STATUSES S
3486 where S.DUE_DATE < SYSDATE
3487 and S.ACTIVITY_STATUS in ('ACTIVE','WAITING','NOTIFIED',
3488 'SUSPEND','DEFERRED')
3489 and S.ITEM_TYPE = itemtype;
3490
3491 cursor curs_noitype is
3492 select
3493 S.ROWID ROW_ID
3494 from WF_ITEM_ACTIVITY_STATUSES S
3495 where S.DUE_DATE < SYSDATE
3496 and S.ACTIVITY_STATUS in ('ACTIVE','WAITING','NOTIFIED',
3497 'SUSPEND','DEFERRED');
3498
3499 idarr RowidArrayTyp;
3500 arrsize pls_integer;
3501 eligible boolean;
3502 schema varchar2(30);
3503
3504 begin
3505 -- Fetch eligible rows into array
3506 arrsize := 0;
3507 if (itemtype is not null) then
3508 -- Fetch by itemtype
3509 for id in curs_itype loop
3510 arrsize := arrsize + 1;
3511 idarr(arrsize) := id.row_id;
3512 end loop;
3513 else
3514 -- Fetch all itemtypes
3515 for id in curs_noitype loop
3516 arrsize := arrsize + 1;
3517 idarr(arrsize) := id.row_id;
3518 end loop;
3519 end if;
3520
3521 -- Process all eligible rows found
3522 for i in 1 .. arrsize loop
3523 -- Lock row, and check if still eligible for execution
3524 -- To check eligibility, do original select only add rowid condition.
3525 -- Note ok to use no-itemtype variant since itemtype can't change
3526 -- and was already filtered for in original select.
3527 -- select everything but completed and error. avoid "not in" which
3528 -- disables index in RBO.
3529 begin
3530 select
3531 S.ITEM_TYPE, S.ITEM_KEY, S.PROCESS_ACTIVITY
3532 into l_itemtype, l_itemkey, l_actid
3533 from WF_ITEM_ACTIVITY_STATUSES S , WF_ITEMS WI
3534 where S.DUE_DATE < SYSDATE
3535 and S.ACTIVITY_STATUS in ('WAITING','NOTIFIED','SUSPEND',
3536 'DEFERRED','ACTIVE')
3537 and S.ROWID = idarr(i)
3538 and WI.item_type = S.ITEM_TYPE
3539 and WI.item_key = S.ITEM_KEY
3540 for update of S.ACTIVITY_STATUS, WI.item_type , wi.item_key NOWAIT;
3541
3542 -- check if schema matched
3543 schema := Wf_Engine.GetItemAttrText(l_itemtype,l_itemkey,
3544 wf_engine.eng_schema, ignore_notfound=>TRUE);
3545
3546 if (schema is null or
3547 schema = Wf_Engine.Current_Schema) then
3548 eligible := TRUE;
3549 else
3550 eligible := FALSE;
3551 end if;
3552 exception
3553 when resource_busy or no_data_found then
3554 -- If row already locked, or no longer eligible to run,
3555 -- continue on to next item in list.
3556 eligible := FALSE;
3557 end;
3558
3559 if (eligible) then
3560 -- Set the status to COMPLETE:#TIMEOUT.
3561 Wf_Item_Activity_Status.Create_Status(l_itemtype, l_itemkey, l_actid,
3562 wf_engine.eng_completed, wf_engine.eng_timedout);
3563
3564 begin
3565 begin
3566 begin
3567 savepoint wf_savepoint;
3568 -- If there is a function attached, call it in timeout mode to
3569 -- give the function one last chance to complete and override
3570 -- the timeout.
3571 Wf_Engine_Util.Execute_Post_NTF_Function(l_itemtype, l_itemkey,
3572 l_actid, wf_engine.eng_timeout, pntfstatus, pntfresult);
3573 if (pntfstatus = wf_engine.eng_completed) then
3574 -- Post-notification function found and returned a completed
3575 -- status.
3576 -- Complete activity with result of post-notification function.
3577 Wf_Engine_Util.Complete_Activity(l_itemtype, l_itemkey, l_actid,
3578 pntfresult, FALSE);
3579 else
3580 -- Either had no post-notification function, or result was still
3581 -- not complete.
3582 -- In either case, complete activity with #TIMEOUT.
3583 Wf_Engine_Util.Complete_Activity(l_itemtype, l_itemkey, l_actid,
3584 wf_engine.eng_timedout);
3585 end if;
3586 exception
3587 when others then
3588 -- If anything in this process raises an exception:
3589 -- 1. rollback any work in this process thread
3590 -- Raise an exception for the next exception handler to finish
3591 -- remaining steps.
3592 rollback to wf_savepoint;
3593 raise;
3594 end;
3595 exception
3596 when NO_SAVEPOINT then
3597 -- Catch any savepoint error in case of a commit happened.
3598 Wf_Core.Token('ACTIVITY', Wf_Engine.GetActivityLabel(l_actid));
3599 Wf_Core.Raise('WFENG_COMMIT_IN_COMPLETE');
3600 end;
3601 exception
3602 when OTHERS then
3603 -- Remaining steps for completing activity raises an exception:
3604 -- 2. set this activity to error status
3605 -- 3. execute the error process (if any)
3606 -- 4. clear the error to continue with next activity
3607 Wf_Core.Context('Wf_Engine', 'ProcessTimeout', l_itemkey, l_itemtype,
3608 to_char(l_actid));
3609 Wf_Item_Activity_Status.Set_Error(l_itemtype, l_itemkey, l_actid,
3610 wf_engine.eng_exception, FALSE);
3611 Wf_Engine_Util.Execute_Error_Process(l_itemtype, l_itemkey,
3612 l_actid, wf_engine.eng_exception);
3613 Wf_Core.Clear;
3614 end;
3615 end if;
3616
3617 -- bug 7828862 - Resynch apps context from cached values if it changed
3618 wfa_sec.Restore_Ctx();
3619
3620 -- For eligible row: Commit work to insure this activity
3621 -- thread doesn't interfere with others.
3622 -- For non-eligible row: Commit to release the lock.
3623 commit;
3624 Fnd_Concurrent.Set_Preferred_RBS;
3625
3626 end loop;
3627
3628 exception
3629 when others then
3630 Wf_Core.Context('Wf_Engine', 'ProcessTimeout', l_itemkey, l_itemtype,
3631 to_char(l_actid));
3632 raise;
3633 end ProcessTimeOut;
3634
3635 --
3636 -- ProcessStuckProcess (PUBLIC)
3637 -- Pick up one stuck process, mark error status, and execute error process.
3638 -- IN
3639 -- itemtype - Item type to process. If null process all item types.
3640 --
3641 procedure ProcessStuckProcess(itemtype in varchar2)
3642 is
3643 resource_busy exception;
3644 pragma exception_init(resource_busy, -00054);
3645
3646 l_itemtype varchar2(8);
3647 l_itemkey varchar2(240);
3648 l_actid pls_integer;
3649
3650 -- Select all activities from WIAS where:
3651 -- 1. Activity is a PROCESS activity
3652 -- 2. Activity has ACTIVE status
3653 -- 3. Activity has no direct child activities which have a status of:
3654 -- (ACTIVE, NOTIFIED, DEFERRED, SUSPENDED, ERROR)
3655 -- 4. Item has requested itemtype (first curs only)
3656 -- NOTE: Two separate cursors are used for itemtype and no-itemtype
3657 -- cases to get better execution plans.
3658
3659 cursor curs_itype is
3660 select /*+ ORDERED USE_NL (WIASP WI WPAP WAP)
3661 INDEX (WIASP WF_ITEM_ACTIVITY_STATUSES_N1) */
3662 WIASP.ROWID ROW_ID
3663 from WF_ITEM_ACTIVITY_STATUSES WIASP,
3664 WF_ITEMS WI,
3665 WF_PROCESS_ACTIVITIES WPAP,
3666 WF_ACTIVITIES WAP
3667 where WIASP.ITEM_TYPE = itemtype
3668 and WIASP.PROCESS_ACTIVITY = WPAP.INSTANCE_ID
3669 and WPAP.ACTIVITY_ITEM_TYPE = WAP.ITEM_TYPE
3670 and WPAP.ACTIVITY_NAME = WAP.NAME
3671 and WIASP.ITEM_TYPE = WI.ITEM_TYPE
3672 and WIASP.ITEM_KEY = WI.ITEM_KEY
3673 and WI.BEGIN_DATE >= WAP.BEGIN_DATE
3674 and WI.BEGIN_DATE < nvl(WAP.END_DATE, WI.BEGIN_DATE+1)
3675 and WAP.TYPE = wf_engine.eng_process
3676 and WIASP.ACTIVITY_STATUS = 'ACTIVE' --use literal to force index
3677 and not exists
3678 (select null
3679 from WF_ITEM_ACTIVITY_STATUSES WIASC,
3680 WF_PROCESS_ACTIVITIES WPAC
3681 where WAP.ITEM_TYPE = WPAC.PROCESS_ITEM_TYPE
3682 and WAP.NAME = WPAC.PROCESS_NAME
3683 and WAP.VERSION = WPAC.PROCESS_VERSION
3684 and WPAC.INSTANCE_ID = WIASC.PROCESS_ACTIVITY
3685 and WIASC.ITEM_TYPE = WI.ITEM_TYPE
3686 and WIASC.ITEM_KEY = WI.ITEM_KEY
3687 and WIASC.ACTIVITY_STATUS in ('ACTIVE','NOTIFIED','SUSPEND',
3688 'DEFERRED','ERROR'));
3689
3690 cursor curs_noitype is
3691 select /*+ ORDERED USE_NL (WIASP WI WPAP WAP)
3692 INDEX (WIASP WF_ITEM_ACTIVITY_STATUSES_N1) */
3693 WIASP.ROWID ROW_ID
3694 from WF_ITEM_ACTIVITY_STATUSES WIASP,
3695 WF_ITEMS WI,
3696 WF_PROCESS_ACTIVITIES WPAP,
3697 WF_ACTIVITIES WAP
3698 where WIASP.PROCESS_ACTIVITY = WPAP.INSTANCE_ID
3699 and WPAP.ACTIVITY_ITEM_TYPE = WAP.ITEM_TYPE
3700 and WPAP.ACTIVITY_NAME = WAP.NAME
3701 and WIASP.ITEM_TYPE = WI.ITEM_TYPE
3702 and WIASP.ITEM_KEY = WI.ITEM_KEY
3703 and WI.BEGIN_DATE >= WAP.BEGIN_DATE
3704 and WI.BEGIN_DATE < nvl(WAP.END_DATE, WI.BEGIN_DATE+1)
3705 and WAP.TYPE = 'PROCESS'
3706 and WIASP.ACTIVITY_STATUS = 'ACTIVE' --use literal to force index
3707 and not exists
3708 (select null
3709 from WF_ITEM_ACTIVITY_STATUSES WIASC,
3710 WF_PROCESS_ACTIVITIES WPAC
3711 where WAP.ITEM_TYPE = WPAC.PROCESS_ITEM_TYPE
3712 and WAP.NAME = WPAC.PROCESS_NAME
3713 and WAP.VERSION = WPAC.PROCESS_VERSION
3714 and WPAC.INSTANCE_ID = WIASC.PROCESS_ACTIVITY
3715 and WIASC.ITEM_TYPE = decode(wap.direction,
3716 wap.direction, WI.ITEM_TYPE,
3717 wi.item_type)
3718 and WIASC.ITEM_KEY = WI.ITEM_KEY
3719 and WIASC.ACTIVITY_STATUS in ('ACTIVE', 'NOTIFIED', 'SUSPEND',
3720 'DEFERRED', 'ERROR'));
3721
3722
3723 idarr RowidArrayTyp;
3724 arrsize pls_integer;
3725 eligible boolean;
3726
3727 begin
3728
3729 -- Fetch eligible rows into array
3730 arrsize := 0;
3731 if (itemtype is not null) then
3732 -- Fetch by itemtype
3733 for id in curs_itype loop
3734 arrsize := arrsize + 1;
3735 idarr(arrsize) := id.row_id;
3736 end loop;
3737 else
3738 -- Fetch all itemtypes
3739 for id in curs_noitype loop
3740 arrsize := arrsize + 1;
3741 idarr(arrsize) := id.row_id;
3742 end loop;
3743 end if;
3744
3745 -- Process all eligible rows found
3746 for i in 1 .. arrsize loop
3747 -- Lock row, and check if still eligible for execution
3748 -- To check for eligibility, check that:
3749 -- 1. Activity is a PROCESS activity
3750 -- 2. Activity has ACTIVE status
3751 -- 3. Activity has no direct child activities which have a status of:
3752 -- (ACTIVE, NOTIFIED, DEFERRED, SUSPENDED, ERROR)
3753 -- 4. Item has requested itemtype (first curs only)
3754 begin
3755 select
3756 WIASP.ITEM_TYPE, WIASP.ITEM_KEY, WIASP.PROCESS_ACTIVITY
3757 into l_itemtype, l_itemkey, l_actid
3758 from WF_ITEM_ACTIVITY_STATUSES WIASP,
3759 WF_PROCESS_ACTIVITIES WPAP,
3760 WF_ACTIVITIES WAP,
3761 WF_ITEMS WI
3762 where WIASP.PROCESS_ACTIVITY = WPAP.INSTANCE_ID
3763 and WPAP.ACTIVITY_ITEM_TYPE = WAP.ITEM_TYPE
3764 and WPAP.ACTIVITY_NAME = WAP.NAME
3765 and WIASP.ITEM_TYPE = WI.ITEM_TYPE
3766 and WIASP.ITEM_KEY = WI.ITEM_KEY
3767 and WI.BEGIN_DATE >= WAP.BEGIN_DATE
3768 and WI.BEGIN_DATE < nvl(WAP.END_DATE, WI.BEGIN_DATE+1)
3769 and WAP.TYPE = wf_engine.eng_process
3770 and WIASP.ACTIVITY_STATUS = 'ACTIVE' --use literal to force index
3771 and not exists
3772 (select null
3773 from WF_ITEM_ACTIVITY_STATUSES WIASC,
3774 WF_PROCESS_ACTIVITIES WPAC
3775 where WAP.ITEM_TYPE = WPAC.PROCESS_ITEM_TYPE
3776 and WAP.NAME = WPAC.PROCESS_NAME
3777 and WAP.VERSION = WPAC.PROCESS_VERSION
3778 and WPAC.INSTANCE_ID = WIASC.PROCESS_ACTIVITY
3779 and WIASC.ITEM_TYPE = WI.ITEM_TYPE
3780 and WIASC.ITEM_KEY = WI.ITEM_KEY
3781 and WIASC.ACTIVITY_STATUS in ('ACTIVE','NOTIFIED','SUSPEND',
3782 'DEFERRED','ERROR'))
3783 and WIASP.ROWID = idarr(i)
3784 for update of WIASP.ACTIVITY_STATUS, WI.ITEM_TYPE ,WI.ITEM_KEY NOWAIT;
3785
3786 eligible := TRUE;
3787 exception
3788 when resource_busy or no_data_found then
3789 -- If row already locked, or no longer eligible to run,
3790 -- continue on to next item in list.
3791 eligible := FALSE;
3792 end;
3793
3794 if (eligible) then
3795 -- Set the status to ERROR:#STUCK
3796 Wf_Item_Activity_Status.Create_Status(l_itemtype, l_itemkey, l_actid,
3797 wf_engine.eng_error, wf_engine.eng_stuck);
3798
3799 -- Execute the error process for stuck process
3800 begin
3801 begin
3802 begin
3803 savepoint wf_savepoint;
3804 Wf_Engine_Util.Execute_Error_Process(l_itemtype, l_itemkey, l_actid,
3805 wf_engine.eng_stuck);
3806 exception
3807 when others then
3808 -- If anything in this process raises an exception:
3809 -- 1. rollback any work in this process thread
3810 -- Raise an exception for the next exception handler to finish
3811 -- remaining steps.
3812 rollback to wf_savepoint;
3813 raise;
3814 end;
3815 exception
3816 when NO_SAVEPOINT then
3817 -- Catch any savepoint error in case of a commit happened.
3818 Wf_Core.Token('ACTIVITY', Wf_Engine.GetActivityLabel(l_actid));
3819 Wf_Core.Raise('WFENG_COMMIT_IN_ERRPROC');
3820 end;
3821 exception
3822 when OTHERS then
3823 -- Remaining steps for completing activity raises an exception:
3824 -- 2. set this activity to error status
3825 -- 3. execute the error process (if any)
3826 -- 4. clear the error to continue with next activity
3827 Wf_Core.Context('Wf_Engine', 'ProcessStuckProcess', l_itemkey,
3828 l_itemtype, to_char(l_actid));
3829 Wf_Item_Activity_Status.Set_Error(l_itemtype, l_itemkey, l_actid,
3830 wf_engine.eng_exception, FALSE);
3831 Wf_Engine_Util.Execute_Error_Process(l_itemtype, l_itemkey,
3832 l_actid, wf_engine.eng_exception);
3833 Wf_Core.Clear;
3834 end;
3835
3836 -- Commit work to insure this activity thread doesn't interfere
3837 -- with others.
3838 commit;
3839
3840 Fnd_Concurrent.Set_Preferred_RBS;
3841
3842 end if;
3843 end loop;
3844
3845 exception
3846 when others then
3847 Wf_Core.Context('Wf_Engine', 'ProcessStuckProcess', l_itemkey, l_itemtype,
3848 to_char(l_actid));
3849 raise;
3850 end ProcessStuckProcess;
3851
3852 --
3853 -- Background (PUBLIC)
3854 -- Process all current deferred and/or timeout activities within
3855 -- threshold limits.
3856 -- IN
3857 -- itemtype - Item type to process. If null process all item types.
3858 -- minthreshold - Minimum cost activity to process. No minimum if null.
3859 -- maxthreshold - Maximum cost activity to process. No maximum if null.
3860 -- process_deferred - Run deferred or waiting processes
3861 -- process_timeout - Handle timeout errors
3862 -- process_stuck - Handle stuck process errors
3863 --
3864 procedure Background (itemtype in varchar2,
3865 minthreshold in number,
3866 maxthreshold in number,
3867 process_deferred in boolean,
3868 process_timeout in boolean,
3869 process_stuck in boolean)
3870 is
3871 l_aq_tm_processes varchar2(512);
3872 begin
3873 WF_CORE.TAG_DB_SESSION(WF_CORE.CONN_TAG_WF, Background.itemtype);
3874 if (WF_CACHE.MetaRefreshed) then
3875 null;
3876
3877 end if;
3878
3879 --Bug 3945469: Add check on db major version and cache value for aq_tm_processes
3880 if wf_core.g_oracle_major_version is null then
3881 wf_core.InitCache;
3882 end if;
3883
3884 --Check the value of aq_tm_processes if oracle version is not 10g
3885 if (wf_core.g_oracle_major_version < 10 ) then
3886 if (wf_core.g_aq_tm_processes ='0') then
3887 --If the value aq_tm_processes is 0 then raise error
3888 wf_core.raise('WFENG_AQ_TM_PROCESSES_ERROR');
3889 end if;
3890 end if;
3891
3892 --Bug 2307428
3893 --Enable the deferred and inbound queues.
3894 wf_queue.Enablebackgroundqueues;
3895
3896 -- Do not need to preserve context
3897 wf_engine.preserved_context := FALSE;
3898
3899 -- bug 7828862 Cache Apps context before starting to process any type
3900 wfa_sec.Cache_Ctx();
3901
3902 -- Process deferred activities
3903 if (process_deferred) then
3904 -- process the inbound queue first - it may place events on the deferred Q
3905 wf_queue.ProcessInboundQueue(itemtype);
3906 wf_engine.ProcessDeferred(itemtype, minthreshold, maxthreshold);
3907 end if;
3908
3909 -- Process timeout activities
3910 if (process_timeout) then
3911 wf_engine.ProcessTimeout(itemtype);
3912 end if;
3913
3914 -- Process stuck activities
3915 if (process_stuck) then
3916 wf_engine.ProcessStuckProcess(itemtype);
3917 end if;
3918
3919 exception
3920 when others then
3921 Wf_Core.Context('Wf_Engine', 'Background', itemtype,
3922 to_char(minthreshold), to_char(maxthreshold));
3923 -- Restore Apps Context
3924 wfa_sec.Restore_Ctx();
3925 raise;
3926 end Background;
3927
3928 --
3929 -- BackgroundConcurrent (PUBLIC)
3930 -- Run background process for deferred and/or timeout activities
3931 -- from Concurrent Manager.
3932 -- This is a cover of Background() with different argument types to
3933 -- be used by the Concurrent Manager.
3934 -- IN
3935 -- errbuf - CPM error message
3936 -- retcode - CPM return code (0 = success, 1 = warning, 2 = error)
3937 -- itemtype - Item type to process. If null process all item types.
3938 -- minthreshold - Minimum cost activity to process. No minimum if null.
3939 -- maxthreshold - Maximum cost activity to process. No maximum if null.
3940 -- process_deferred - Run deferred or waiting processes
3941 -- process_timeout - Handle timeout errors
3942 -- process_stuck - Handle stuck process errors
3943 --
3944 procedure BackgroundConcurrent (
3945 errbuf out NOCOPY varchar2,
3946 retcode out NOCOPY varchar2,
3947 itemtype in varchar2,
3948 minthreshold in varchar2,
3949 maxthreshold in varchar2,
3950 process_deferred in varchar2,
3951 process_timeout in varchar2,
3952 process_stuck in varchar2,
3953 instance_number in number)
3954 is
3955 minthreshold_num number;
3956 maxthreshold_num number;
3957 process_deferred_bool boolean;
3958 process_timeout_bool boolean;
3959 process_stuck_bool boolean;
3960
3961 errname varchar2(30);
3962 errmsg varchar2(2000);
3963 errstack varchar2(4000);
3964 begin
3965 -- Convert arguments from varchar2 to real type.
3966 minthreshold_num := to_number(minthreshold);
3967 maxthreshold_num := to_number(maxthreshold);
3968
3969 if (upper(substr(process_deferred, 1, 1)) = 'Y') then
3970 process_deferred_bool := TRUE;
3971 else
3972 process_deferred_bool := FALSE;
3973 end if;
3974
3975 if (upper(substr(process_timeout, 1, 1)) = 'Y') then
3976 process_timeout_bool := TRUE;
3977 else
3978 process_timeout_bool := FALSE;
3979 end if;
3980
3981 if (upper(substr(process_stuck, 1, 1)) = 'Y') then
3982 process_stuck_bool := TRUE;
3983 else
3984 process_stuck_bool := FALSE;
3985 end if;
3986
3987 --Bug 9527839. RAC enabled workflows
3988 if instance_number > 0 then
3989 WF_ENGINE_RAC.Set_Context('INST_ID', instance_number);
3990 end if;
3991
3992 -- Call background engine with new args
3993 Wf_Engine.Background(
3994 itemtype,
3995 minthreshold_num,
3996 maxthreshold_num,
3997 process_deferred_bool,
3998 process_timeout_bool,
3999 process_stuck_bool);
4000
4001 -- Return 0 for successful completion.
4002 errbuf := '';
4003 retcode := '0';
4004
4005 exception
4006 when others then
4007 -- Retrieve error message into errbuf
4008 wf_core.get_error(errname, errmsg, errstack);
4009 if (errmsg is not null) then
4010 errbuf := errmsg;
4011 else
4012 errbuf := sqlerrm;
4013 end if;
4014
4015 -- Return 2 for error.
4016 retcode := '2';
4017 end BackgroundConcurrent;
4018
4019 --
4020 -- CreateProcess (PUBLIC)
4021 -- Create a new runtime process (for an application item).
4022 -- IN
4023 -- itemtype - A valid item type
4024 -- itemkey - A string generated from the application object's primary key.
4025 -- process - A valid root process for this item type
4026 -- (or null to use the item's selector function)
4027 --
4028 procedure CreateProcess(itemtype in varchar2,
4029 itemkey in varchar2,
4030 process in varchar2,
4031 user_key in varchar2,
4032 owner_role in varchar2)
4033 is
4034 root varchar2(30);
4035 version number;
4036 actdate date;
4037 typ varchar2(8);
4038 rootid pls_integer;
4039 status varchar2(8);
4040 l_event wf_event_t; -- Buffer for initing event itemattrs
4041 l_pkg_name varchar2(20) := 'WF_ENGINE';
4042 l_prc_name varchar2(20) := '.CreateProcess';
4043
4044 -- All event item attrs to be initialized
4045 -- Initialization is now deferred until GetItemAttrEvent
4046 /* cursor evtcurs is
4047 select WIA.NAME
4048 from WF_ITEM_ATTRIBUTES WIA
4049 where WIA.ITEM_TYPE = CreateProcess.itemtype
4050 and WIA.TYPE = 'EVENT';*/
4051
4052 begin
4053 -- Argument validation
4054 if ((itemtype is null) or (itemkey is null)) then
4055 Wf_Core.Token('ITEMTYPE', itemtype);
4056 Wf_Core.Token('ITEMKEY', itemkey);
4057 Wf_Core.Token('PROCESS', process);
4058 Wf_Core.Raise('WFSQL_ARGS');
4059 end if;
4060
4061 --<rwunderl:4198524>
4062 if (WF_CACHE.MetaRefreshed) then
4063 null;
4064 end if;
4065
4066 --Bug 9527839. RAC enabled workflows
4067 if WF_ENGINE_RAC.Process_Is_RAC_Enabled(itemtype, process) then
4068 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
4069 wf_log_pkg.string(wf_log_pkg.level_statement, l_pkg_name||l_prc_name,
4070 itemtype||' is RAC enabled');
4071 end if;
4072 --If item type is the same as the previous one do not set the context again
4073 if CreateProcess.itemtype<>setctx_itemtype or setctx_itemtype is null then
4074 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
4075 wf_log_pkg.string(wf_log_pkg.level_statement, l_pkg_name||l_prc_name,
4076 'switching type from '||nvl(setctx_itemtype,'null')||
4077 ' to '||nvl(itemtype, 'null'));
4078 end if;
4079 WF_ENGINE_RAC.Set_Context('INST_ID',nvl(sys_context('WF_RAC_CTX','INST_ID'),
4080 sys_context('userenv', 'instance')));
4081 end if;
4082 else --If not RAC-enabled we'll make sure the context is reset if previously set.
4083 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
4084 wf_log_pkg.string(wf_log_pkg.level_statement, l_pkg_name||l_prc_name,
4085 itemtype||' is not RAC enabled');
4086 end if;
4087 if sys_context('WF_RAC_CTX', 'INST_ID') is not null then
4088 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
4089 wf_log_pkg.string(wf_log_pkg.level_statement, l_pkg_name||l_prc_name,
4090 'resetting WF_RAC_CTX.INST_ID to null');
4091 end if;
4092 WF_ENGINE_RAC.Set_Context('INST_ID', null);
4093 end if;
4094 end if;
4095
4096 -- Check for duplicate item
4097 if (itemkey = wf_engine.eng_synch) then
4098 if (Wf_Item.Item_Exist(itemtype, itemkey)) then
4099 -- SYNCHMODE: If duplicate is a synch process, check the status
4100 -- of the root process of the existing item.
4101 -- If the cached item is already complete, then it is ok to
4102 -- toss out the old item and create a new one.
4103 begin
4104 Wf_Item.Root_Process(itemtype, itemkey, root, version);
4105 rootid := Wf_Process_Activity.RootInstanceId(itemtype,
4106 itemkey, root);
4107 Wf_Item_Activity_Status.Status(itemtype, itemkey, rootid, status);
4108 exception
4109 when others then
4110 status := 'x'; -- Treat errors like incomplete process
4111 end;
4112 if (nvl(status, 'x') <> wf_engine.eng_completed) then
4113 Wf_Core.Token('ITEMTYPE', itemtype);
4114 Wf_Core.Token('ITEMKEY', itemkey);
4115 Wf_Core.Raise('WFENG_SYNCH_ITEM');
4116 end if;
4117 end if;
4118 else
4119 -- Not synchmode. Clear plsql cache first, just in case previous
4120 -- item was purged/rolled back, then check for duplicate.
4121 Wf_Item.ClearCache;
4122 if (Wf_Item.Item_Exist(itemtype, itemkey)) then
4123 Wf_Core.Token('TYPE', itemtype);
4124 Wf_Core.Token('KEY', itemkey);
4125 Wf_Core.Raise('WFENG_ITEM_UNIQUE');
4126 end if;
4127 end if;
4128
4129 if (process is null) then
4130 -- Call the selector function to get the process
4131 root := Wf_Engine_Util.Get_Root_Process(itemtype, itemkey);
4132 if (root is null) then
4133 Wf_Core.Token('TYPE', itemtype);
4134 Wf_Core.Token('KEY', itemkey);
4135 Wf_Core.Raise('WFENG_ITEM_ROOT_SELECTOR');
4136 end if;
4137 else
4138 root := process;
4139 end if;
4140
4141 -- Check that the root argument is a valid process.
4142 -- NOTE: The check that the process exists must be done BEFORE
4143 -- calling create_item to avoid foreign key problems during the insert.
4144 -- The check that the process is runnable can't be done until AFTER
4145 -- create_item so the date has been established.
4146 actdate := sysdate;
4147 typ := Wf_Activity.Type(itemtype, root, actdate);
4148 if ((typ is null) or (typ <> wf_engine.eng_process)) then
4149 Wf_Core.Token('TYPE', itemtype);
4150 Wf_Core.Token('NAME', root);
4151 Wf_Core.Raise('WFENG_PROCESS_NAME');
4152 end if;
4153
4154 -- Insert row in items table
4155 Wf_Item.Create_Item(itemtype, itemkey, root, actdate, createprocess.user_key,
4156 createprocess.owner_role);
4157
4158 -- Validate the root argument is runnable
4159 rootid := Wf_Process_Activity.RootInstanceId(itemtype, itemkey,
4160 root);
4161 if (rootid is null) then
4162 Wf_Core.Token('TYPE', itemtype);
4163 Wf_Core.Token('NAME', root);
4164 Wf_Core.Raise('WFENG_PROCESS_RUNNABLE');
4165 end if;
4166
4167 if (itemkey <> WF_ENGINE.eng_synch) then
4168 -- Create monitor access key attributes
4169 Wf_Engine.AddItemAttr(itemtype, itemkey, wf_engine.wfmon_mon_key,
4170 Wf_Core.Random);
4171 Wf_Engine.AddItemAttr(itemtype, itemkey, wf_engine.wfmon_acc_key,
4172 Wf_Core.Random);
4173 end if;
4174
4175 -- Create a schema attribute
4176 Wf_Engine.AddItemAttr(itemtype, itemkey, wf_engine.eng_schema,
4177 Wf_Engine.Current_Schema);
4178
4179 -- Initialize all EVENT-type item attributes
4180 -- Not done here, it is deferred until GetItemAttrEvent
4181 /* for evtattr in evtcurs loop
4182 Wf_Event_T.Initialize(l_event);
4183 Wf_Engine.SetItemAttrEvent(
4184 itemtype => itemtype,
4185 itemkey => itemkey,
4186 name => evtattr.name,
4187 event => l_event);
4188 end loop;*/
4189
4190 exception
4191 when others then
4192 -- Bug 4117740
4193 -- Call clearcache() when #SYNCH flow is in error
4194 if ((itemkey = WF_ENGINE.eng_synch) and
4195 (wf_core.error_name is null or wf_core.error_name <> 'WFENG_SYNCH_ITEM') and
4196 (not WF_ENGINE.debug)) then
4197 Wf_Item.ClearCache;
4198 end if;
4199
4200 Wf_Core.Context('Wf_Engine', 'CreateProcess', itemtype, itemkey, process);
4201 raise;
4202 end CreateProcess;
4203
4204 --
4205 -- StartProcess (PUBLIC)
4206 -- Begins execution of the process. The process will be identified by the
4207 -- itemtype and itemkey. The engine locates the starting activities
4208 -- of the root process and executes them.
4209 -- IN
4210 -- itemtype - A valid item type
4211 -- itemkey - A string generated from the application object's primary key.
4212 --
4213 procedure StartProcess(itemtype in varchar2,
4214 itemkey in varchar2)
4215 is
4216 begin
4217 WF_CORE.TAG_DB_SESSION(WF_CORE.CONN_TAG_WF, StartProcess.itemtype);
4218
4219 if (WF_CACHE.MetaRefreshed) then
4220 null;
4221 end if;
4222 --Bug 2259039
4223 Wf_Engine_Util.Start_Process_Internal(
4224 itemtype=> itemtype,
4225 itemkey => itemkey,
4226 runmode => 'START');
4227 exception
4228 when others then
4229 Wf_Core.Context('Wf_Engine', 'StartProcess', itemtype, itemkey);
4230 raise;
4231 end StartProcess;
4232
4233 --
4234 -- LaunchProcess (PUBLIC)
4235 -- Launch a process both creates and starts it.
4236 -- This is a wrapper for friendlier UI
4237 -- IN
4238 -- itemtype - A valid item type
4239 -- itemkey - A string generated from the application object's primary key.
4240 -- process - A valid root process for this item type
4241 -- (or null to use the item's selector function)
4242 -- userkey - User key to be set
4243 -- owner - Role designated as owner of the item
4244 --
4245 procedure LaunchProcess(itemtype in varchar2,
4246 itemkey in varchar2,
4247 process in varchar2,
4248 userkey in varchar2,
4249 owner in varchar2) is
4250
4251 begin
4252 -- Check Arguments
4253 if ((itemtype is null) or
4254 (itemkey is null)) then
4255 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
4256 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
4257 Wf_Core.Raise('WFSQL_ARGS');
4258
4259 end if;
4260
4261 wf_engine.CreateProcess (itemtype,itemkey,process);
4262
4263 if userkey is not null then
4264 wf_engine.SetItemUserKey(itemtype,itemkey,userkey);
4265 end if;
4266
4267 if owner is not null then
4268 wf_engine.SetItemOwner(itemtype,itemkey,owner);
4269 end if;
4270
4271 wf_engine.StartProcess (itemtype,itemkey);
4272 exception
4273 when others then
4274 Wf_Core.Context('Wf_Engine', 'LaunchProcess', itemtype, itemkey,
4275 process, userkey, owner);
4276 raise;
4277 end LaunchProcess;
4278
4279 --
4280 -- SuspendProcess (PUBLIC)
4281 -- Suspends process execution, meaning no new transitions will occur.
4282 -- Outstanding notifications will be allowed to complete, but they will not
4283 -- cause activity transitions. If the process argument is null, the root
4284 -- process for the item is suspended, otherwise the named process is
4285 -- suspended.
4286 -- IN
4287 -- itemtype - A valid item type
4288 -- itemkey - A string generated from the application object's primary key.
4289 -- process - Process to suspend, specified in the form
4290 -- [<parent process_name>:]<process instance_label>
4291 -- If null suspend the root process.
4292 --
4293 procedure SuspendProcess(itemtype in varchar2,
4294 itemkey in varchar2,
4295 process in varchar2) is
4296
4297 root varchar2(30); -- The root process for this item key
4298 version pls_integer; -- Root process version
4299 rootid pls_integer; -- Instance id of root process
4300 actdate date; -- Active date of item
4301 proc varchar2(61); -- The process name that is going to be suspended
4302 procid pls_integer; -- The process id that is going to be suspended
4303 status varchar2(8); -- The status of the process
4304
4305 -- Cursor to select deferred activities to remove from background queue
4306 cursor defact is
4307 select PROCESS_ACTIVITY, BEGIN_DATE
4308 from WF_ITEM_ACTIVITY_STATUSES
4309 where ITEM_TYPE = itemtype
4310 and ITEM_KEY = itemkey
4311 and ACTIVITY_STATUS = wf_engine.eng_deferred;
4312
4313 begin
4314 -- Check Arguments
4315 if (itemtype is null) then
4316 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
4317 Wf_Core.Raise('WFSQL_ARGS');
4318
4319 -- Not allowed in synch mode
4320 elsif (itemkey = wf_engine.eng_synch) then
4321 wf_core.token('OPERATION', 'Wf_Engine.SuspendProcess');
4322 wf_core.raise('WFENG_SYNCH_DISABLED');
4323
4324 elsif (itemkey is null) then
4325 WF_ENGINE.SuspendAll(itemtype, process); --</rwunderl:1833759>
4326 return;
4327
4328 end if;
4329
4330 -- Get the root process for this key and also validate the item
4331 Wf_Item.Root_Process(itemtype, itemkey, root, version);
4332 if (root is null) then
4333 Wf_Core.Token('TYPE', itemtype);
4334 Wf_Core.Token('KEY', itemkey);
4335 Wf_Core.Raise('WFENG_ITEM');
4336 end if;
4337
4338 -- Get the process instance id.
4339 -- Search the process beginnning at the root process of the item for the
4340 -- activity matching process.
4341 actdate := Wf_Item.Active_Date(itemtype, itemkey);
4342 rootid := Wf_Process_Activity.RootInstanceId(itemtype, itemkey, root);
4343 if (rootid is null) then
4344 Wf_Core.Token('TYPE', itemtype);
4345 Wf_Core.Token('KEY', itemkey);
4346 Wf_Core.Token('NAME', root);
4347 Wf_Core.Raise('WFENG_ITEM_ROOT');
4348 end if;
4349
4350 if (process is null) then
4351 -- Suspend the root process
4352 proc := root;
4353 procid := rootid;
4354 else
4355 -- Suspend the given process
4356 proc := process;
4357 procid := Wf_Process_Activity.FindActivity(rootid, proc, actdate);
4358 if (procid is null) then
4359 Wf_Core.Token('TYPE', itemtype);
4360 Wf_Core.Token('KEY', itemkey);
4361 Wf_Core.Token('NAME', proc);
4362 Wf_Core.Token('VERSION', to_char(version));
4363 Wf_Core.Raise('WFENG_ITEM_PROCESS');
4364 end if;
4365
4366 -- Check that activity is a PROCESS-type.
4367 -- Only PROCESS activities may be suspended.
4368 if (Wf_Activity.Instance_Type(procid, actdate) <>
4369 wf_engine.eng_process) then
4370 Wf_Core.Token('NAME', proc);
4371 Wf_Core.Token('TYPE', itemtype);
4372 Wf_Core.Raise('WFENG_PROCESS_NAME');
4373 end if;
4374 end if;
4375
4376 -- Always clear the cache first
4377 -- AbortProcess, SuspendProcess and ResumeProcess should be rarely called
4378 -- from the background engine, so it should be safe to force reading from
4379 -- the database.
4380 Wf_Item_Activity_Status.ClearCache;
4381
4382 -- Check if the process is active
4383 Wf_Item_Activity_Status.Status(itemtype, itemkey, procid, status);
4384
4385 if (status is null) then
4386 -- This process has not been run yet. Create a pre-suspended
4387 -- status row so engine does not run process later
4388 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, procid,
4389 wf_engine.eng_suspended, wf_engine.eng_null, null, null,
4390 newStatus=>TRUE);
4391 elsif (status = wf_engine.eng_deferred) then
4392 -- Change status from 'deferred' to 'suspended'
4393 -- Doing this prevents the background processor from picking it up.
4394 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, procid,
4395 wf_engine.eng_suspended, null,
4396 null, null);
4397 elsif (status = wf_engine.eng_active) then
4398 -- Mark process as 'suspended', 'null' in WIAS table
4399 -- Doing this stops the engine from going through the rest of the flow
4400 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, procid,
4401 wf_engine.eng_suspended, null,
4402 null, null);
4403
4404 -- Suspend all the children processes
4405 Wf_Engine_Util.Suspend_Child_Processes(itemtype, itemkey, procid);
4406 else
4407 Wf_Core.Token('TYPE', itemtype);
4408 Wf_Core.Token('KEY', itemkey);
4409 Wf_Core.Token('NAME', proc);
4410 Wf_Core.Raise('WFENG_ITEM_PROCESS_ACTIVE');
4411 end if;
4412
4413 exception
4414 when others then
4415 Wf_Core.Context('Wf_Engine', 'SuspendProcess', itemtype, itemkey, process);
4416 raise;
4417 end SuspendProcess;
4418
4419 --
4420 -- AbortProcess (PUBLIC)
4421 -- Abort process execution. Outstanding notifications are canceled. The
4422 -- process is then considered complete, with a status specified by the
4423 -- result argument.
4424 -- IN
4425 -- itemtype - A valid item type
4426 -- itemkey - A string generated from the application object's primary key.
4427 -- process - Process to abort, specified in the form
4428 -- [<parent process_name>:]<process instance_label>
4429 -- If null abort the root process.
4430 -- result - Result to complete process with
4431 -- verify_lock - This boolean param determines whether we should lock
4432 -- the item before processing or not . This would control
4433 -- concurrent execution contention.
4434 -- cascade - This boolean param determines if the process should be
4435 -- aborted in cascade or not, ie kill all child processes
4436 -- to this process.
4437 --
4438 procedure AbortProcess(itemtype in varchar2,
4439 itemkey in varchar2,
4440 process in varchar2,
4441 result in varchar2,
4442 verify_lock in boolean,
4443 cascade in boolean) is
4444
4445 root varchar2(30); -- The root process for this item key
4446 version pls_integer; -- Root process version
4447 rootid pls_integer; -- Instance id of root process
4448 actdate date; -- Active date of item
4449 proc varchar2(61); -- Process name
4450 procid pls_integer; -- The process id that is going to be suspended
4451 status varchar2(8); -- The status of the process
4452 dummy pls_integer; -- Added for bug 1893606 - JWSMITH
4453
4454 --Bug 1166527
4455 l_parameterlist wf_parameter_list_t := wf_parameter_list_t();
4456
4457 l_lock boolean;
4458 cursor openNotifications is -- <7513983>
4459 SELECT wn.notification_id
4460 FROM wf_notifications wn, WF_ITEM_ACTIVITY_STATUSES ias
4461 WHERE ias.item_type = itemtype
4462 AND ias.item_key = itemkey
4463 AND ias.notification_id is not null
4464 AND ias.notification_id = wn.group_id
4465 AND wn.status = 'OPEN'
4466 UNION
4467 SELECT wn.notification_id
4468 FROM wf_notifications wn, WF_ITEM_ACTIVITY_STATUSES_H iash
4469 WHERE iash.item_type = itemtype
4470 AND iash.item_key = itemkey
4471 AND iash.notification_id is not null
4472 AND iash.notification_id = wn.notification_id
4473 AND wn.status = 'OPEN'; -- </7513983>
4474
4475 begin
4476 -- Check Arguments
4477 if (itemtype is null) then
4478 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
4479 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
4480 Wf_Core.Raise('WFSQL_ARGS');
4481
4482 elsif (itemkey = wf_engine.eng_synch) then -- Not allowed in synch mode
4483 wf_core.token('OPERATION', 'Wf_Engine.AbortProcess');
4484 wf_core.raise('WFENG_SYNCH_DISABLED');
4485 end if;
4486
4487 --Do the check for lock ONLY if there is an explicit
4488 --request for the same.
4489 if verify_lock then
4490 --Check if we can acquire lock for the
4491 --the item type/key here
4492 l_lock := wf_item.acquire_lock(itemtype,itemkey,true) ;
4493 end if;
4494
4495 -- Get the root process for this key and also validate the item
4496 Wf_Item.Root_Process(itemtype, itemkey, root, version);
4497 if (root is null) then
4498 Wf_Core.Context('Wf_Engine', 'AbortProcess', itemtype, itemkey, process);
4499 Wf_Core.Token('TYPE', itemtype);
4500 Wf_Core.Token('KEY', itemkey);
4501 Wf_Core.Raise('WFENG_ITEM');
4502 end if;
4503
4504 -- Get the process instance id.
4505 -- Search the process beginnning at the root process of the item for the
4506 -- activity matching process.
4507 actdate := Wf_Item.Active_Date(itemtype, itemkey);
4508 rootid := Wf_Process_Activity.RootInstanceId(itemtype, itemkey, root);
4509 if (rootid is null) then
4510 Wf_Core.Token('TYPE', itemtype);
4511 Wf_Core.Token('KEY', itemkey);
4512 Wf_Core.Token('NAME', root);
4513 Wf_Core.Raise('WFENG_ITEM_ROOT');
4514 end if;
4515
4516 if (process is null) then
4517 -- Abort the root process
4518 proc := root;
4519 procid := rootid;
4520 else
4521 -- Abort the given process
4522 proc := process;
4523 procid := Wf_Process_Activity.FindActivity(rootid, process, actdate);
4524 if (procid is null) then
4525 Wf_Core.Token('TYPE', itemtype);
4526 Wf_Core.Token('KEY', itemkey);
4527 Wf_Core.Token('NAME', process);
4528 Wf_Core.Token('VERSION', to_char(version));
4529 Wf_Core.Raise('WFENG_ITEM_PROCESS');
4530 end if;
4531
4532 -- Check that activity is a PROCESS-type.
4533 -- Only PROCESS activities may be aborted.
4534 if (Wf_Activity.Instance_Type(procid, actdate) <>
4535 wf_engine.eng_process) then
4536 Wf_Core.Token('NAME', proc);
4537 Wf_Core.Token('TYPE', itemtype);
4538 Wf_Core.Raise('WFENG_PROCESS_NAME');
4539 end if;
4540 end if;
4541
4542 -- Always clear the cache first
4543 Wf_Item_Activity_Status.ClearCache;
4544
4545 -- Check the process is not already complete
4546 Wf_Item_Activity_Status.Status(itemtype, itemkey, procid, status);
4547
4548 if (status is null) then
4549 if (WF_ITEM.SetEndDate(itemtype, itemkey) = 1) then
4550 Wf_Core.Token('TYPE', itemtype);
4551 Wf_core.Token('KEY', itemkey);
4552 Wf_core.Token('NAME', proc);
4553 Wf_Core.Raise('WFENG_ITEM_PROCESS_RUNNING');
4554 end if;
4555
4556 elsif (status = wf_engine.eng_completed) then
4557 Wf_Core.Token('TYPE', itemtype);
4558 Wf_Core.Token('KEY', itemkey);
4559 Wf_Core.Token('NAME', proc);
4560 Wf_Core.Raise('WFENG_ITEM_PROCESS_ACTIVE');
4561 else
4562 -- Mark process as 'COMPLETE', 'result' in WIAS table
4563 -- Doing this stops the engine from going through the rest of the flow
4564 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, procid,
4565 wf_engine.eng_completed, result,
4566 null, SYSDATE);
4567
4568 -- Kill child activities recursively
4569 Wf_Engine_Util.Process_Kill_Children(itemtype, itemkey, procid);
4570 --If cascade option is set to true abort all child
4571 --processes aswell
4572 if cascade then
4573 Wf_Engine_Util.Process_Kill_ChildProcess(itemtype, itemkey);
4574 end if;
4575 end if;
4576
4577 --Cancel any OPEN FYI notifications
4578 for nid in openNotifications
4579 loop
4580 wf_notification.cancel(nid.notification_id,' ');
4581 end loop;
4582
4583 --Include the information of the aborted process in the events
4584 --parameter list.
4585 wf_event.AddParameterToList('ITMETYPE', itemtype, l_parameterlist);
4586 wf_event.AddParameterToList('ITEMKEY', itemkey, l_parameterlist);
4587 wf_event.AddParameterToList('PROCESS', process, l_parameterlist);
4588 wf_event.AddParameterToList('RESULT', result, l_parameterlist);
4589
4590 -- Raise the event
4591 wf_event.Raise(p_event_name => 'oracle.apps.wf.engine.abort',
4592 p_event_key => itemkey,
4593 p_parameters => l_parameterlist);
4594
4595 exception
4596 when resource_busy then
4597 wf_core.token('TYPE',itemtype);
4598 wf_core.token('KEY',itemkey);
4599 wf_core.raise('WFENG_RESOURCE_BUSY');
4600
4601 when others then
4602 Wf_Core.Context('Wf_Engine', 'AbortProcess', itemtype, itemkey,
4603 process, result);
4604 raise;
4605 end AbortProcess;
4606
4607 --
4608 -- ResumeProcess (PUBLIC)
4609 -- Returns a process to normal execution status. Any transitions which
4610 -- were deferred by SuspendProcess() will now be processed.
4611 -- IN
4612 -- itemtype - A valid item type
4613 -- itemkey - A string generated from the application object's primary key.
4614 -- process - Process to resume, specified in the form
4615 -- [<parent process_name>:]<process instance_label>
4616 -- If null resume the root process.
4617 --
4618 procedure ResumeProcess(itemtype in varchar2,
4619 itemkey in varchar2,
4620 process in varchar2)
4621 is
4622 root varchar2(30); -- The root process for this item key
4623 version pls_integer; -- Root process version
4624 rootid pls_integer; -- Instance id of root process
4625 actdate date; -- Active date of item
4626 proc varchar2(61); -- The process name that is going to be suspended
4627 procid pls_integer; -- The process id that is going to be suspended
4628 status varchar2(8); -- The status of the process
4629
4630 -- Cursor to select deferred activities to restart.
4631 cursor defact is
4632 select
4633 PROCESS_ACTIVITY, BEGIN_DATE
4634 from WF_ITEM_ACTIVITY_STATUSES
4635 where ITEM_TYPE = itemtype
4636 and ITEM_KEY = itemkey
4637 and ACTIVITY_STATUS = wf_engine.eng_deferred;
4638
4639 actidarr InstanceArrayTyp; -- Deferred activities array
4640 i pls_integer := 0; -- Counter for the for loop
4641
4642 trig_savepoint exception;
4643 pragma exception_init(trig_savepoint, -04092);
4644 dist_savepoint exception;
4645 pragma exception_init(dist_savepoint, -02074);
4646 --Bug 2484201
4647 --Array to select the begin_date for the deferred activities
4648 type InstanceDateArray is table of date index by binary_integer;
4649 act_begin_date InstanceDateArray;
4650 begin
4651 WF_CORE.TAG_DB_SESSION(WF_CORE.CONN_TAG_WF, ResumeProcess.itemtype);
4652
4653 -- Check Arguments
4654 if (itemtype is null) then
4655 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
4656 Wf_Core.Raise('WFSQL_ARGS');
4657
4658 elsif (itemkey = wf_engine.eng_synch) then -- Not allowed in synch mode
4659 wf_core.token('OPERATION', 'Wf_Engine.ResumeProcess');
4660 wf_core.raise('WFENG_SYNCH_DISABLED');
4661
4662 elsif (itemkey is NULL) then
4663 WF_ENGINE.ResumeAll(itemtype, process); --</rwunderl:1833759>
4664 return;
4665
4666 end if;
4667
4668 -- Get the root process for this key
4669 Wf_Item.Root_Process(itemtype, itemkey, root, version);
4670 if (root is null) then
4671 Wf_Core.Token('TYPE', itemtype);
4672 Wf_Core.Token('KEY', itemkey);
4673 Wf_Core.Raise('WFENG_ITEM');
4674 end if;
4675
4676 -- Get the process instance id.
4677 -- Search the process beginnning at the root process of the item for the
4678 -- activity matching process.
4679 actdate := Wf_Item.Active_Date(itemtype, itemkey);
4680 rootid := Wf_Process_Activity.RootInstanceId(itemtype, itemkey, root);
4681 if (rootid is null) then
4682 Wf_Core.Token('TYPE', itemtype);
4683 Wf_Core.Token('KEY', itemkey);
4684 Wf_Core.Token('NAME', root);
4685 Wf_Core.Raise('WFENG_ITEM_ROOT');
4686 end if;
4687
4688 if (process is null) then
4689 -- Resume the root process
4690 proc := root;
4691 procid := rootid;
4692 else
4693 -- Resume the given process
4694 proc := process;
4695 procid := Wf_Process_Activity.FindActivity(rootid, process, actdate);
4696 if (procid is null) then
4697 Wf_Core.Token('TYPE', itemtype);
4698 Wf_Core.Token('KEY', itemkey);
4699 Wf_Core.Token('NAME', process);
4700 Wf_Core.Token('VERSION', to_char(version));
4701 Wf_Core.Raise('WFENG_ITEM_PROCESS');
4702 end if;
4703
4704 -- Check that activity is a PROCESS-type.
4705 -- Only PROCESS activities may be resumed.
4706 if (Wf_Activity.Instance_Type(procid, actdate) <>
4707 wf_engine.eng_process) then
4708 Wf_Core.Token('NAME', proc);
4709 Wf_Core.Token('TYPE', itemtype);
4710 Wf_Core.Raise('WFENG_PROCESS_NAME');
4711 end if;
4712 end if;
4713
4714 -- Always clear the cache first
4715 Wf_Item_Activity_Status.ClearCache;
4716
4717 -- Check if the process is suspended
4718 Wf_Item_Activity_Status.Status(itemtype, itemkey, procid, status);
4719 if (status is null) then
4720 Wf_Core.Token('TYPE', itemtype);
4721 Wf_Core.Token('KEY', itemkey);
4722 Wf_Core.Token('NAME', proc);
4723 Wf_Core.Raise('WFENG_ITEM_PROCESS_RUNNING');
4724 elsif (status <> wf_engine.eng_suspended) then
4725 Wf_Core.Token('TYPE', itemtype);
4726 Wf_Core.Token('KEY', itemkey);
4727 Wf_Core.Token('NAME', proc);
4728 Wf_Core.Raise('WFENG_ITEM_PROCESS_SUSPENDED');
4729 else
4730 -- If we came here, that means the process is currently suspended.
4731 -- Mark process as eng_active 'active', 'null' in WIAS table
4732 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, procid,
4733 wf_engine.eng_active, null, null, null);
4734
4735 -- Mark any sub-processes as active again
4736 Wf_Engine_Util.Resume_Child_Processes(itemtype, itemkey, procid);
4737
4738 -- Restart any activities that were deferred because completion
4739 -- came in while process was suspended.
4740 --
4741 -- Note that cursor will select all deferred activities, even if they
4742 -- were deferred for other reasons than suspended process, but this is
4743 -- OK because:
4744 -- 1. Activities deferred because cost is higher than threshold will
4745 -- be immediately re-deferred by process_activity()
4746 -- 2. Deferred activities that are not in the sub-process just resumed
4747 -- will still have a suspended parent, and will also be immediately
4748 -- re-deferred by process_activity().
4749 -- This causes a little extra processing in rare cases, but is easier
4750 -- than figuring out the cause for each deferral here.
4751 for actid in defact loop
4752 actidarr(i) := actid.process_activity;
4753 act_begin_date(i) := actid.begin_date;
4754 i := i + 1;
4755 end loop;
4756 actidarr(i) := '';
4757
4758 i := 0;
4759 while (actidarr(i) is not null) loop
4760 --Bug 2484201
4761 --Set the begin date in call to Create_status as the begin_date
4762 --of the activity or to sysdate if begin_date is null
4763 --Also set the status to active only if begin_date <= sysdate
4764
4765 if (nvl(act_begin_date(i),sysdate) <= sysdate) then
4766 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, actidarr(i),
4767 wf_engine.eng_active, null, sysdate, null);
4768 begin
4769 savepoint wf_savepoint;
4770 Wf_Engine_Util.Process_Activity(itemtype, itemkey, actidarr(i),
4771 Wf_Engine.Threshold, TRUE);
4772 exception
4773 when trig_savepoint or dist_savepoint then
4774 -- Can't restart process here, re-defer for the
4775 -- background process to pick up.
4776 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey,
4777 actidarr(i),wf_engine.eng_deferred, null, sysdate, null);
4778 when others then
4779 -- If anything in this process raises an exception:
4780 -- 1. rollback any work in this process thread
4781 -- 2. set this activity to error status
4782 -- 3. execute the error process (if any)
4783 -- 4. clear the error to continue with next activity
4784 rollback to wf_savepoint;
4785 Wf_Core.Context('Wf_Engine', 'ResumeProcess', itemtype, itemkey,
4786 process);
4787 Wf_Item_Activity_Status.Set_Error(itemtype, itemkey, actidarr(i),
4788 wf_engine.eng_exception, FALSE);
4789 Wf_Engine_Util.Execute_Error_Process(itemtype, itemkey, actidarr(i),
4790 wf_engine.eng_exception);
4791
4792 Wf_Core.Clear;
4793 end;
4794 --else case status is same as right now that is deferred.
4795 end if;
4796
4797 i := i + 1;
4798 end loop;
4799 end if;
4800
4801 exception
4802 when others then
4803 Wf_Core.Context('Wf_Engine', 'ResumeProcess', itemtype, itemkey, process);
4804 raise;
4805 end ResumeProcess;
4806
4807
4808 --
4809 -- SuspendAll (PUBLIC)) --</rwunderl:1833759>
4810 -- Suspends all processes for a given itemType.
4811 -- IN
4812 -- itemtype - A valid itemType
4813 --
4814
4815 Procedure SuspendAll (p_itemType in varchar2,
4816 p_process in varchar2) is
4817
4818 cursor Open_Items(p_itemType in varchar2) is
4819 SELECT item_key
4820 FROM wf_items
4821 WHERE item_type = p_itemType
4822 AND end_date is NULL;
4823
4824 cursor All_Open_Items is
4825 SELECT item_type, item_key
4826 FROM wf_items
4827 WHERE end_date is NULL;
4828
4829 begin
4830
4831 if (p_itemType is NULL) then
4832 for c in All_Open_items loop
4833 begin
4834 WF_ENGINE.SuspendProcess(c.item_type, c.item_key, p_process);
4835
4836 exception
4837 when others then
4838 if ( wf_core.error_name = 'WFENG_ITEM_PROCESS_ACTIVE' ) then
4839 wf_core.clear;
4840
4841 else
4842 raise;
4843
4844 end if;
4845
4846 end;
4847
4848 end loop;
4849
4850 else
4851 for c in Open_Items(p_itemType) loop
4852 begin
4853 WF_ENGINE.SuspendProcess(p_itemType, c.item_key, p_process);
4854
4855 exception
4856 when others then
4857 if ( wf_core.error_name = 'WFENG_ITEM_PROCESS_ACTIVE' ) then
4858 wf_core.clear;
4859
4860 else
4861 raise;
4862
4863 end if;
4864
4865 end;
4866 end loop;
4867
4868 end if;
4869
4870 exception
4871 when others then
4872 Wf_Core.Context('Wf_Engine', 'SuspendAll', p_itemType, p_process);
4873 raise;
4874
4875 end SuspendAll;
4876
4877 --
4878 -- ResumeAll (PUBLIC) --</rwunderl:1833759>
4879 -- Resumes all processes for a given itemType.
4880 -- IN
4881 -- itemtype - A valid itemType
4882 --
4883 Procedure ResumeAll (p_itemType in varchar2,
4884 p_process in varchar2) is
4885
4886 cursor suspended_items(p_itemType in varchar2) is
4887 SELECT distinct wias.item_key
4888 FROM wf_item_activity_statuses wias
4889 WHERE wias.item_type = p_itemType
4890 AND wias.activity_status = wf_engine.eng_suspended;
4891
4892 cursor all_suspended_items is
4893 SELECT distinct wias.item_type, wias.item_key
4894 FROM wf_item_activity_statuses wias
4895 WHERE wias.activity_status = wf_engine.eng_suspended;
4896
4897 begin
4898
4899 if (p_itemType is NULL) then
4900 for c in all_suspended_items loop
4901 begin
4902 WF_ENGINE.ResumeProcess(c.item_type, c.item_key, p_process);
4903
4904 exception
4905 when others then
4906 null;
4907
4908 end;
4909
4910 end loop;
4911
4912 else
4913 for c in suspended_items(p_itemType) loop
4914 begin
4915 WF_ENGINE.ResumeProcess(p_itemType, c.item_key, p_process);
4916
4917 exception
4918 when others then
4919 null;
4920
4921 end;
4922
4923 end loop;
4924
4925 end if;
4926
4927 end ResumeAll;
4928
4929
4930
4931 Procedure CreateForkProcess (
4932 copy_itemtype in varchar2,
4933 copy_itemkey in varchar2,
4934 new_itemkey in varchar2,
4935 same_version in boolean,
4936 masterdetail in boolean) is
4937
4938 root_process varchar2(30);
4939 root_process_version number;
4940 dummy varchar2(30);
4941 dummyNum number;
4942 status varchar2(50);
4943 result varchar2(50);
4944 l_parent_itemType varchar2(8);
4945 l_parent_itemKey varchar2(240);
4946 l_parent_context varchar2(2000);
4947
4948 ValTooLarge EXCEPTION;
4949 pragma exception_init(ValTooLarge, -01401);
4950 ValTooLargeNew EXCEPTION;
4951 pragma exception_init(ValTooLargeNew, -12899);
4952
4953 begin
4954
4955 -- Argument validation
4956 if (copy_itemtype is null)
4957 or (copy_itemkey is null)
4958 or (new_itemkey is null) then
4959 Wf_Core.Token('COPY_ITEMTYPE', copy_itemtype);
4960 Wf_Core.Token('COPY_ITEMKEY', copy_itemkey);
4961 Wf_Core.Token('NEW_ITEMKEY', new_itemkey);
4962 Wf_Core.Raise('WFSQL_ARGS');
4963 end if;
4964
4965 -- Not allowed in synch mode
4966 if (new_itemkey = wf_engine.eng_synch)
4967 or (copy_itemkey = wf_engine.eng_synch) then
4968 wf_core.token('OPERATION', 'Wf_Engine.SuspendProcess');
4969 wf_core.raise('WFENG_SYNCH_DISABLED');
4970 end if;
4971
4972 -- Check status
4973 Wf_engine.ItemStatus(copy_itemtype, copy_itemkey, status, result);
4974 if (status = wf_engine.eng_error) then
4975 Wf_Core.Raise('WFENG_NOFORK_ONERROR');
4976 end if;
4977
4978 -- Check for duplicate item
4979 if (Wf_Item.Item_Exist(copy_itemtype, new_itemkey)) then
4980 Wf_Core.Token('TYPE', copy_itemtype);
4981 Wf_Core.Token('KEY', new_itemkey);
4982 Wf_Core.Raise('WFENG_ITEM_UNIQUE');
4983 end if;
4984
4985 --Place row-lock on this item and retrieve parent process info:
4986 select parent_item_type, parent_item_key, parent_context
4987 into l_parent_itemType, l_parent_itemKey, l_parent_context
4988 from wf_items
4989 where item_type = copy_itemtype
4990 and item_key = copy_itemkey
4991 for update of item_type;
4992
4993 --Create the process
4994 if same_version then
4995 insert into wf_items(
4996 ITEM_TYPE, ITEM_KEY,
4997 ROOT_ACTIVITY, ROOT_ACTIVITY_VERSION,
4998 OWNER_ROLE, USER_KEY,
4999 PARENT_ITEM_TYPE, PARENT_ITEM_KEY, PARENT_CONTEXT,
5000 BEGIN_DATE, END_DATE)
5001 select
5002 ITEM_TYPE, NEW_ITEMKEY,
5003 ROOT_ACTIVITY, ROOT_ACTIVITY_VERSION,
5004 OWNER_ROLE, USER_KEY,
5005 PARENT_ITEM_TYPE, PARENT_ITEM_KEY, PARENT_CONTEXT,
5006 BEGIN_DATE, null
5007 from wf_items
5008 where item_type = copy_itemtype
5009 and item_key = copy_itemkey;
5010 else
5011
5012 --lookup the root process
5013 wf_item.Root_Process(itemtype => copy_itemtype,
5014 itemkey => copy_itemkey,
5015 wflow => root_process,
5016 version =>root_process_version);
5017
5018 wf_engine.CreateProcess(copy_itemtype,new_itemkey,root_process);
5019
5020 --delete any defaulted attributes because we will copy the existing ones.
5021 delete from wf_item_attribute_values
5022 where item_type = copy_itemtype
5023 and item_key = new_itemkey;
5024
5025
5026 end if;
5027
5028 -- copy all item attributes including runtime attributes. Also, copy
5029 -- those item attributes that were added after the item was forked
5030 insert into wf_item_attribute_values
5031 (ITEM_TYPE, ITEM_KEY, NAME,
5032 TEXT_VALUE, NUMBER_VALUE, DATE_VALUE)
5033 select ITEM_TYPE, NEW_ITEMKEY, NAME,
5034 TEXT_VALUE, NUMBER_VALUE, DATE_VALUE
5035 from wf_item_attribute_values
5036 where item_type = copy_itemtype
5037 and item_key = copy_itemkey
5038 and name not like '#LBL_'
5039 and name not like '#CNT_'
5040 union all
5041 select ITEM_TYPE, new_itemkey, NAME,
5042 TEXT_DEFAULT, NUMBER_DEFAULT, DATE_DEFAULT
5043 from WF_ITEM_ATTRIBUTES
5044 where ITEM_TYPE = copy_itemtype
5045 and NAME not in
5046 (select name
5047 from wf_item_attribute_values
5048 where item_type = copy_itemtype
5049 and item_key = copy_itemkey
5050 and name not like '#LBL_'
5051 and name not like '#CNT_');
5052
5053
5054 -- reset the access_keys to make them unique
5055 Wf_Engine.SetItemAttrText(copy_itemtype, new_itemkey,
5056 wf_engine.wfmon_mon_key, Wf_Core.Random);
5057 Wf_Engine.SetItemAttrText(copy_itemtype, new_itemkey,
5058 wf_engine.wfmon_acc_key, Wf_Core.Random);
5059
5060
5061 -- reset the schema, just in case, if the #SCHEMA attribute does not exist
5062 -- it will be added. The CreateProcess api now adds the #SCHEMA.
5063 -- Only items created before WF_ENGINE was upgraded will encounter the
5064 -- exception to be handled, so this is for backward compatibility.
5065
5066 begin
5067 Wf_Engine.SetItemAttrText(copy_itemtype, new_itemkey,
5068 wf_engine.eng_schema, Wf_Engine.Current_Schema);
5069
5070 exception
5071 when others then
5072 if (wf_core.error_name = 'WFENG_ITEM_ATTR') then
5073 wf_core.clear;
5074 WF_ENGINE.AddItemAttr(copy_itemtype, new_itemkey,
5075 wf_engine.eng_schema,
5076 Wf_Engine.Current_Schema);
5077
5078 else
5079
5080 raise;
5081
5082 end if;
5083
5084 end;
5085
5086 -- Finally set an itemkey to record what this originated from
5087 begin
5088 Wf_Engine.AddItemAttr(copy_itemtype, new_itemkey, '#FORKED_FROM',
5089 copy_itemkey);
5090 exception
5091 when others then
5092 --
5093 -- If item attribute already exists then ignore the error
5094 --
5095 if ( wf_core.error_name = 'WFENG_ITEM_ATTR_UNIQUE' ) then
5096 wf_core.clear;
5097 Wf_Engine.SetItemAttrText(copy_itemtype, new_itemkey,
5098 '#FORKED_FROM', copy_itemkey);
5099 else
5100 raise;
5101 end if;
5102 end;
5103
5104 if (masterdetail) then
5105 --The caller has signaled that this is a master/detail process
5106 --We first will attempt to zero out any #WAITFORDETAIL attribute that may be
5107 --on this forked process (it is a master itself).
5108 dummyNum := WF_ENGINE.AddToItemAttrNumber(copy_itemType, new_itemKey,
5109 '#WAITFORDETAIL',
5110 to_number(NULL));
5111
5112 if ((l_parent_itemType is NOT null) and (l_parent_itemKey is NOT null)) then
5113 --There is a parent item to this forked item, so we will validate and
5114 --increment the parent's #WAITFORDETAIL counter.
5115 if (WF_ENGINE.AddToItemAttrNumber(l_parent_itemType, l_parent_itemKey,
5116 '#WAITFORDETAIL', 1) is NOT null) then
5117 --The parent has a #WAITFORDETAIL, so we can proceed on to check for
5118 --parent context.
5119 if (l_parent_context is NOT null) then
5120 --There is a parent context, so we will add the #LBL_ attribute to
5121 --the child flow, and will increment the corresponding #CNT_ attribute
5122 --in the parent flow.
5123 begin
5124 WF_ENGINE.AddItemAttr(itemtype=>copy_itemType, itemkey=>new_itemkey,
5125 aname=>'#LBL_'||l_parent_context,
5126 text_value=>l_parent_context);
5127
5128 --Since there was a parent context in the forked_from flow, we know
5129 --The parent has a counter for this label, so we can just increment.
5130 dummyNum := WF_ENGINE.AddToItemAttrNumber(l_parent_itemType,
5131 l_parent_itemKey,
5132 '#CNT_'||l_parent_context,
5133 1);
5134 exception
5135 when ValTooLarge OR ValTooLargeNew then
5136 Wf_Core.Context('WF_ENGINE', 'CreateForkProcess', copy_itemtype,
5137 copy_itemkey, new_itemkey, l_parent_itemtype,
5138 l_parent_itemkey, l_parent_context, 'TRUE');
5139 WF_CORE.Token('LABEL', l_parent_context);
5140 WF_CORE.Token('LENGTH', 30);
5141 WF_CORE.Raise('WFENG_LABEL_TOO_LARGE');
5142 end;
5143 else
5144 -- PARENT_CONTEXT is null
5145 -- increase all known #CNT counter by 1
5146 update WF_ITEM_ATTRIBUTE_VALUES
5147 set NUMBER_VALUE = NUMBER_VALUE + 1
5148 where NAME like '#CNT_%'
5149 and NUMBER_VALUE is not null
5150 and ITEM_TYPE = l_parent_itemType
5151 and ITEM_KEY = l_parent_itemKey;
5152 end if; --PARENT_CONTEXT is not null
5153 end if; --#WAITFORDETAIL exists in the parent item.
5154 end if; --There is a parent item to this forked process.
5155 end if; --The caller signalled that this is a master/detail process.
5156 exception
5157 when others then
5158 Wf_Core.Context('Wf_Engine', 'CreateForkProcess');
5159 raise;
5160 end CreateForkProcess;
5161
5162
5163
5164 --
5165 -- StartForkProcess (PUBLIC)
5166 -- Start a process that has been forked. Depending on the way this was
5167 -- forked, this will execute startprocess if its to start with the latest
5168 -- version or it copies the forked process activty by activity.
5169 -- IN
5170 -- itemtype - Item type
5171 -- itemkey - item key to start
5172 --
5173 procedure StartForkProcess(
5174 itemtype in varchar2,
5175 itemkey in varchar2) as
5176
5177 copy_itemkey varchar2(30);
5178
5179 cursor all_activities is
5180 select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY,
5181 ACTIVITY_STATUS, ACTIVITY_RESULT_CODE,
5182 ASSIGNED_USER, NOTIFICATION_ID,
5183 BEGIN_DATE, END_DATE, EXECUTION_TIME,
5184 ERROR_NAME, ERROR_MESSAGE, ERROR_STACK,
5185 OUTBOUND_QUEUE_ID, DUE_DATE
5186 from wf_item_activity_statuses
5187 where item_type = itemtype
5188 and item_key = copy_itemkey;
5189
5190 cursor all_activities_hist is
5191 select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY,
5192 ACTIVITY_STATUS, ACTIVITY_RESULT_CODE,
5193 ASSIGNED_USER, NOTIFICATION_ID,
5194 BEGIN_DATE, END_DATE, EXECUTION_TIME,
5195 ERROR_NAME, ERROR_MESSAGE, ERROR_STACK,
5196 OUTBOUND_QUEUE_ID, DUE_DATE
5197 from wf_item_activity_statuses_h
5198 where item_type = itemtype
5199 and item_key = copy_itemkey;
5200
5201
5202 -- order by nid so that we re-execute in chronological order
5203 cursor ntf_open is
5204 select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY,
5205 ACTIVITY_STATUS, ACTIVITY_RESULT_CODE,
5206 ASSIGNED_USER, NOTIFICATION_ID,
5207 BEGIN_DATE, END_DATE, EXECUTION_TIME,
5208 ERROR_NAME, ERROR_MESSAGE, ERROR_STACK,
5209 OUTBOUND_QUEUE_ID, DUE_DATE
5210 from wf_item_activity_statuses
5211 where item_type = itemtype
5212 and item_key = copy_itemkey
5213 and notification_id is not null
5214 and activity_status = 'NOTIFIED'
5215 order by notification_id;
5216
5217
5218 nid number;
5219
5220 act_fname varchar2(240);
5221 act_ftype varchar2(30);
5222 delay number; -- dont use pls_integer or numeric overflow can occur.
5223 msg_id raw(16):=null;
5224
5225 copy_root_process varchar2(30);
5226 copy_process_version pls_integer;
5227 copy_active_date date;
5228
5229 new_root_process varchar2(30);
5230 new_process_version pls_integer;
5231 new_active_date date;
5232
5233
5234
5235 begin
5236 WF_CORE.TAG_DB_SESSION(WF_CORE.CONN_TAG_WF, StartForkProcess.itemtype);
5237 -- Argument validation
5238 if (itemtype is null)
5239 or (itemkey is null) then
5240 Wf_Core.Token('COPY_ITEMTYPE', itemtype);
5241 Wf_Core.Token('COPY_ITEMKEY', copy_itemkey);
5242 Wf_Core.Token('NEW_ITEMKEY', itemkey);
5243 Wf_Core.Raise('WFSQL_ARGS');
5244 end if;
5245
5246
5247 -- get the forked_from attribute: if it doesnt exist then this cannot be
5248 -- a forked item
5249 begin
5250 copy_itemkey := Wf_Engine.GetItemAttrText(itemtype, itemkey,'#FORKED_FROM');
5251 exception when others then
5252 Wf_Core.Raise('WF_NOFORK');
5253 end;
5254
5255
5256 -- Not allowed in synch mode
5257 if (itemkey = wf_engine.eng_synch)
5258 or (copy_itemkey = wf_engine.eng_synch) then
5259 wf_core.token('OPERATION', 'Wf_Engine.SuspendProcess');
5260 wf_core.raise('WFENG_SYNCH_DISABLED');
5261 end if;
5262
5263
5264 -- Check item exists and store attributes while cached
5265 if not (Wf_Item.Item_Exist(itemtype, copy_itemkey)) then
5266 Wf_Core.Token('TYPE', itemtype);
5267 Wf_Core.Token('KEY', copy_itemkey);
5268 Wf_Core.Raise('WFENG_ITEM');
5269 end if;
5270
5271 wf_item.Root_Process(itemtype => itemtype,
5272 itemkey => copy_itemkey,
5273 wflow => copy_root_process,
5274 version =>copy_process_version);
5275
5276 copy_active_date:= wf_item.Active_Date(itemtype => itemtype,
5277 itemkey => copy_itemkey);
5278
5279
5280 --check status of item to copy is active or complete
5281 --
5282
5283
5284 -- Check item exists
5285 if not (Wf_Item.Item_Exist(itemtype, itemkey)) then
5286 Wf_Core.Token('TYPE', itemtype);
5287 Wf_Core.Token('KEY', itemkey);
5288 Wf_Core.Raise('WFENG_ITEM');
5289 end if;
5290
5291 wf_item.Root_Process(itemtype => itemtype,
5292 itemkey => itemkey,
5293 wflow => new_root_process,
5294 version =>new_process_version);
5295
5296 new_active_date:= wf_item.Active_Date(itemtype => itemtype,
5297 itemkey => itemkey);
5298
5299
5300
5301
5302 -- validate both copy and new items have same process and start dates.
5303 -- if not, this isnt a true fork: we are simply starting a process that
5304 -- uses the latest version so use startprocess
5305 if copy_root_process <> new_root_process
5306 or copy_process_version <> new_process_version
5307 or copy_active_date <> new_active_date then
5308 begin
5309 wf_engine.startprocess(itemtype,itemkey);
5310 exception when others then
5311 Wf_Core.raise('WF_CANNOT_FORK');
5312 end;
5313 return;
5314 end if;
5315
5316 -- copy all activities except open notifications
5317 -- leave these to last because routing rule may complete the thread
5318 for act in all_activities loop
5319
5320 msg_id :=null;
5321 nid := null;
5322
5323 if act.notification_id is not null then
5324
5325 --if complete then copy else ignore (we re-execute later)
5326 if act.activity_status = wf_engine.eng_completed then
5327 wf_engine_util.notification_copy (act.notification_id,
5328 act.item_key, itemkey, nid);
5329 end if;
5330
5331 elsif act.activity_status = wf_engine.eng_deferred then
5332
5333 --process defered activity
5334 act_fname:= Wf_Activity.activity_function
5335 (act.item_type,act.item_key,act.process_activity);
5336 act_ftype:= Wf_Activity.activity_function_type
5337 (act.item_type,act.item_key,act.process_activity);
5338
5339 if act_ftype = 'PL/SQL' then
5340
5341 if act.begin_date <= sysdate then
5342 delay :=0;
5343 else
5344 delay := round((act.begin_date - sysdate)*24*60*60 + 0.5);
5345 end if;
5346 wf_queue.enqueue_event
5347 (queuename=>wf_queue.DeferredQueue,
5348 itemtype=> act.item_type,
5349 itemkey=>itemkey,
5350 actid=>act.process_activity,
5351 delay=>delay,
5352 message_handle=>msg_id);
5353
5354 --even if internal, keep message handle for easy access.
5355 --msg_id :=null;
5356 elsif act_ftype = 'EXTERNAL' then
5357 -- this is a callout so write to OUTBOUND queue
5358 -- do not set the correlation here for compatibility reason
5359 wf_queue.enqueue_event
5360 (queuename=>wf_queue.OutboundQueue,
5361 itemtype=> act.item_type,
5362 itemkey=>itemkey,
5363 actid=>act.process_activity,
5364 funcname=>act_fname,
5365 paramlist=>wf_queue.get_param_list(act.item_type,itemkey,
5366 act.process_activity),
5367 message_handle=>msg_id);
5368 else
5369 -- this is a callout so write to OUTBOUND queue for other type
5370 wf_queue.enqueue_event
5371 (queuename=>wf_queue.OutboundQueue,
5372 itemtype=> act.item_type,
5373 itemkey=>itemkey,
5374 actid=>act.process_activity,
5375 correlation=>act_ftype,
5376 funcname=>act_fname,
5377 paramlist=>wf_queue.get_param_list(act.item_type,itemkey,
5378 act.process_activity),
5379 message_handle=>msg_id);
5380 end if;
5381
5382 --else
5383 --must be a function activity
5384 --in this case we dont have to set any values, but just copy
5385
5386 end if;
5387
5388 -- now insert the status
5389 insert into wf_item_activity_statuses
5390 (ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY,
5391 ACTIVITY_STATUS, ACTIVITY_RESULT_CODE,
5392 ASSIGNED_USER, NOTIFICATION_ID,
5393 BEGIN_DATE, END_DATE, EXECUTION_TIME,
5394 ERROR_NAME, ERROR_MESSAGE, ERROR_STACK,
5395 OUTBOUND_QUEUE_ID, DUE_DATE)
5396 values(act.item_type, itemkey, act.process_activity,
5397 act.activity_status, act.activity_result_code,
5398 act.assigned_user, nid,
5399 act.begin_date, act.end_date, act.execution_time,
5400 act.error_name, act.error_message, act.error_stack,
5401 msg_id, act.due_date);
5402
5403
5404 end loop; --end defered status
5405
5406 -- repeat for all history
5407 for hist in all_activities_hist loop
5408
5409 nid := null;
5410 if hist.notification_id is not null then
5411 wf_engine_util.notification_copy (hist.notification_id,
5412 hist.item_key, itemkey, nid);
5413 end if;
5414
5415 -- now insert the status
5416 insert into wf_item_activity_statuses_h
5417 (ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY,
5418 ACTIVITY_STATUS, ACTIVITY_RESULT_CODE,
5419 ASSIGNED_USER, NOTIFICATION_ID,
5420 BEGIN_DATE, END_DATE, EXECUTION_TIME,
5421 ERROR_NAME, ERROR_MESSAGE, ERROR_STACK,
5422 OUTBOUND_QUEUE_ID, DUE_DATE)
5423 values(hist.item_type, itemkey, hist.process_activity,
5424 hist.activity_status, hist.activity_result_code,
5425 hist.assigned_user, nid,
5426 hist.begin_date, hist.end_date, hist.execution_time,
5427 hist.error_name, hist.error_message, hist.error_stack,
5428 null, hist.due_date);
5429
5430 end loop;
5431
5432 -- update any active functions to notified state
5433 begin
5434 update wf_item_activity_statuses ias
5435 set activity_status = wf_engine.eng_notified
5436 where item_type = itemtype
5437 and item_key = itemkey
5438 and activity_status = 'ACTIVE'
5439 and activity_status = wf_engine.eng_active
5440 and exists (select 'its a function, not subprocess'
5441 from wf_process_activities pa,
5442 wf_activities ac
5443 where pa.activity_name = ac.name
5444 and pa.activity_item_type = ac.item_type
5445 and pa.activity_item_type = ias.item_type
5446 and pa.instance_id = ias.process_activity
5447 and type='FUNCTION');
5448 end;
5449
5450
5451 -- update item attributes on all copied notifications
5452 wf_engine_util.notification_refresh(itemtype,itemkey);
5453
5454
5455 -- as last step, launch all notifications still open
5456 -- keep this as last step because routing rules may allow
5457 -- continuation of thread.
5458
5459 for ntf in ntf_open loop
5460 Wf_Engine_Util.Process_Activity(itemtype, itemkey,
5461 ntf.process_activity,wf_engine.threshold);
5462 end loop;
5463
5464
5465 exception
5466 when others then
5467 Wf_Core.Context('Wf_Engine', 'StartForkProcess');
5468 raise;
5469 end StartForkProcess;
5470
5471
5472 --
5473 --
5474 -- BeginActivity (PUBLIC)
5475 -- Determines if the specified activity may currently be performed on the
5476 -- work item. This is a test that the performer may proactively determine
5477 -- that their intent to perform an activity on an item is, in fact, allowed.
5478 -- IN
5479 -- itemtype - A valid item type
5480 -- itemkey - A string generated from the application object's primary key.
5481 -- activity - Completed activity, specified in the form
5482 -- [<parent process_name>:]<process instance_label>
5483 --
5484 procedure BeginActivity(itemtype in varchar2,
5485 itemkey in varchar2,
5486 activity in varchar2)
5487 is
5488 root varchar2(30); -- The name of the root process for this key
5489 version pls_integer; -- Root process version
5490 actdate date; -- Active date of item
5491 actid pls_integer; -- activity instance id
5492 begin
5493 -- Not allowed in synch mode
5494 if (itemkey = wf_engine.eng_synch) then
5495 wf_core.token('OPERATION', 'Wf_Engine.BeginActivity');
5496 wf_core.raise('WFENG_SYNCH_DISABLED');
5497 end if;
5498
5499 -- Argument validation
5500 if ((itemtype is null) or (itemkey is null) or (activity is null)) then
5501 Wf_Core.Token('ITEMTYPE', itemtype);
5502 Wf_Core.Token('ITEMKEY', itemkey);
5503 Wf_Core.Token('ACTIVITY', activity);
5504 Wf_Core.Raise('WFSQL_ARGS');
5505 end if;
5506
5507 -- Validate the activity and get the actid.
5508 -- One of these conditions must hold:
5509 -- 1. The item does not exist
5510 -- --> The process is being implicitly started for the first time
5511 -- by completing a START activity.
5512 -- 2. The item and root process exist, and activity is NOTIFIED
5513 -- --> Activity just completed in a running process.
5514
5515 -- Check if item exists and get root process
5516 Wf_Item.Root_Process(itemtype, itemkey, root, version);
5517 if (root is null) then
5518 -- Item does not exist. Must be case (1).
5519
5520 -- Use selector to get the root process
5521 -- Note must do this here, instead of relying on CreateProcess
5522 -- to call the selector, because CreateProcess can't take the
5523 -- start activity as an argument to implicitly choose a root
5524 -- process when no selector function is defined.
5525 root := Wf_Engine_Util.Get_Root_Process(itemtype, itemkey, activity);
5526 if (root is null) then
5527 Wf_Core.Token('TYPE', itemtype);
5528 Wf_Core.Token('KEY', itemkey);
5529 Wf_Core.Raise('WFENG_ITEM_ROOT_SELECTOR');
5530 end if;
5531
5532 else
5533 -- Item exists. Must be case (2).
5534 -- Check that the activity is currently notified.
5535 actid := Wf_Process_Activity.ActiveInstanceId(itemtype, itemkey,
5536 activity, wf_engine.eng_notified);
5537
5538 -- Any other status, or no status at all, is an error.
5539 if (actid is null) then
5540 Wf_Core.Token('TYPE', itemtype);
5541 Wf_Core.Token('KEY', itemkey);
5542 Wf_Core.Token('NAME', activity);
5543 Wf_Core.Raise('WFENG_NOT_NOTIFIED');
5544 end if;
5545
5546 end if;
5547 exception
5548 when others then
5549 Wf_Core.Context('Wf_Engine', 'BeginActivity', itemtype, itemkey, activity);
5550 raise;
5551 end BeginActivity;
5552
5553 --
5554 -- CompleteActivity (PUBLIC)
5555 -- Notifies the workflow engine that an activity has been completed for a
5556 -- particular process(item). This procedure can have one or more of the
5557 -- following effects:
5558 -- o Creates a new item. If the completed activity is the start of a process,
5559 -- then a new item can be created by this call. If the completed activity
5560 -- is not the start of a process, it would be an invalid activity error.
5561 -- o Complete an activity with an optional result. This signals the
5562 -- workflow engine that an asynchronous activity has been completed.
5563 -- An optional activity completion result can also be passed.
5564 -- IN
5565 -- itemtype - A valid item type
5566 -- itemkey - A string generated from the application object's primary key.
5567 -- activity - Completed activity, specified in the form
5568 -- [<parent process_name>:]<process instance_label>
5569 -- <result> - An optional result.
5570 --
5571 procedure CompleteActivity(itemtype in varchar2,
5572 itemkey in varchar2,
5573 activity in varchar2,
5574 result in varchar2,
5575 raise_engine_exception in boolean default FALSE)
5576 is
5577 root varchar2(30); -- The name of the root process for this key
5578 version pls_integer; -- Root process version
5579 rootid pls_integer; -- Root process actid
5580 actid pls_integer; -- activity instance id
5581 notid pls_integer; -- Notification group id
5582 user varchar2(320); -- Notification assigned user
5583
5584 trig_savepoint exception;
5585 pragma exception_init(trig_savepoint, -04092);
5586 dist_savepoint exception;
5587 pragma exception_init(dist_savepoint, -02074);
5588
5589 --Bug 2607770
5590 l_lock boolean;
5591 begin
5592 WF_CORE.TAG_DB_SESSION(WF_CORE.CONN_TAG_WF, CompleteActivity.itemtype);
5593 -- Argument validation
5594 if ((itemtype is null) or (itemkey is null) or (activity is null)) then
5595 Wf_Core.Token('ITEMTYPE', itemtype);
5596 Wf_Core.Token('ITEMKEY', itemkey);
5597 Wf_Core.Token('ACTIVITY', activity);
5598 Wf_Core.Raise('WFSQL_ARGS');
5599 end if;
5600
5601 if (WF_CACHE.MetaRefreshed) then
5602 null;
5603
5604 end if;
5605
5606 -- Validate the activity and get the actid.
5607 -- One of these conditions must hold:
5608 -- 1. The item does not exist
5609 -- --> The process is being implicitly started for the first time
5610 -- by completing a START activity.
5611 -- 2. The item and root process exist, and activity is NOTIFIED
5612 -- --> Activity just completed in a running process.
5613
5614 -- Check if item exists and get root process
5615 Wf_Item.Root_Process(itemtype, itemkey, root, version);
5616 if (root is null) then
5617 -- Item does not exist. Must be case (1).
5618
5619 -- Use selector to get the root process
5620 -- Note must do this here, instead of relying on CreateProcess
5621 -- to call the selector, because CreateProcess can't take the
5622 -- start activity as an argument to implicitly choose a root
5623 -- process when no selector function is defined.
5624 root := Wf_Engine_Util.Get_Root_Process(itemtype, itemkey, activity);
5625 if (root is null) then
5626 Wf_Core.Token('TYPE', itemtype);
5627 Wf_Core.Token('KEY', itemkey);
5628 Wf_Core.Raise('WFENG_ITEM_ROOT_SELECTOR');
5629 end if;
5630
5631 -- Create new process
5632 Wf_Engine.CreateProcess(itemtype, itemkey, root);
5633
5634 --Bug 2259039
5635 -- Start the process for this activity.
5636 -- The activity to be completed will be left in NOTIFIED status
5637 -- as a side-effect of this call.
5638 Wf_Engine_Util.Start_Process_Internal(
5639 itemtype => itemtype,
5640 itemkey => itemkey,
5641 runmode => 'ACTIVITY');
5642
5643 -- Get root process for the item
5644 Wf_Item.Root_Process(itemtype, itemkey, root, version);
5645
5646 -- Look for the starting activity in the root process.
5647 actid := Wf_Process_Activity.StartInstanceId(itemtype, root, version,
5648 activity);
5649
5650 -- Create a status row for new activity
5651 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, actid,
5652 wf_engine.eng_active, wf_engine.eng_null, sysdate, null, newStatus=>TRUE);
5653 else
5654
5655 --Bug 2607770
5656 --Its only in the else condition that you need to get
5657 --a lock over the existing item to make sure noone else is
5658 --processing it.
5659
5660 -- Item exists. Must be case (2).
5661 -- Check that the activity is currently notified.
5662 actid := Wf_Process_Activity.ActiveInstanceId(itemtype, itemkey,
5663 activity, wf_engine.eng_notified);
5664
5665 -- Any other status, or no status at all, is an error.
5666 if (actid is null) then
5667 Wf_Core.Token('TYPE', itemtype);
5668 Wf_Core.Token('KEY', itemkey);
5669 Wf_Core.Token('NAME', activity);
5670 Wf_Core.Raise('WFENG_NOT_NOTIFIED');
5671 end if;
5672
5673 --If acquire lock returns true we will continue
5674 --If it returns false we raise exception to the user
5675 --Any other exception we let the caller decide what to do
5676 if (itemkey <> wf_engine.eng_synch) then
5677 --If its an async process and you cannot acquire a lock
5678 --raise the exception to the user
5679 l_lock := wf_item.acquire_lock(itemtype,itemkey,true);
5680 end if;
5681
5682 -- Get notification id
5683 Wf_Item_Activity_Status.Notification_Status(itemtype, itemkey, actid,
5684 notid, user);
5685
5686 -- Close any open notifications associated with this activity.
5687 -- Note: if notifications are not closed here, they will be cancelled
5688 -- anyway by complete_activity. They are only closed here so that the
5689 -- status is closed and not cancelled when going through the external
5690 -- CompleteActivity interface.
5691 -- Bug2811737 CTILLEY - added update to end_date
5692 if (notid is not null) then
5693 update WF_NOTIFICATIONS WN set
5694 status = 'CLOSED',
5695 end_date = sysdate
5696 where WN.GROUP_ID = CompleteActivity.notid
5697 and WN.STATUS = 'OPEN';
5698 end if;
5699 end if;
5700
5701 -- Finally, complete our lovely new activity.
5702 if (itemkey = wf_engine.eng_synch) then
5703 -- SYNCHMODE: No error trapping in synchmode.
5704 Wf_Engine_Util.Complete_Activity(itemtype, itemkey, actid, result);
5705 else
5706 begin
5707 savepoint wf_savepoint;
5708 Wf_Engine_Util.Complete_Activity(itemtype, itemkey, actid, result);
5709 exception
5710 when trig_savepoint or dist_savepoint then
5711 -- You must be in a restricted environment,
5712 -- no fancy error processing for you!
5713 -- NOTE: Must go ahead and complete the activity instead of
5714 -- deferring directly, because the activity must be marked as
5715 -- complete. Any following activities started by completing
5716 -- this activity will be caught and deferred in another
5717 -- savepoint trap in process_activity.
5718 Wf_Engine_Util.Complete_Activity(itemtype, itemkey, actid, result);
5719 when others then
5720 -- If anything in this process raises an exception:
5721 -- 1. rollback any work in this process thread
5722 -- 2. set this activity to error status
5723 -- 3. execute the error process (if any)
5724 -- 4. clear the error to continue with next activity
5725 rollback to wf_savepoint;
5726 --Bug 14602624: parameter raise_engine_exception tells whether the
5727 -- callign application wants the engine to trap any unhandled
5728 -- errors. If FALSE default behavior goes on
5729 if raise_engine_exception then
5730 raise;
5731 else
5732 Wf_Core.Context('Wf_Engine', 'CompleteActivity', itemtype, itemkey,
5733 activity, result);
5734 Wf_Item_Activity_Status.Set_Error(itemtype, itemkey, actid,
5735 wf_engine.eng_exception, FALSE);
5736 Wf_Engine_Util.Execute_Error_Process(itemtype, itemkey, actid,
5737 wf_engine.eng_exception);
5738 Wf_Core.Clear;
5739 end if;
5740 end;
5741 end if;
5742
5743 exception
5744 when resource_busy then
5745 wf_core.token('TYPE',itemtype);
5746 wf_core.token('KEY',itemkey);
5747 wf_core.raise('WFENG_RESOURCE_BUSY');
5748 when others then
5749 Wf_Core.Context('Wf_Engine', 'CompleteActivity', itemtype, itemkey,
5750 activity, result);
5751 raise;
5752 end CompleteActivity;
5753
5754 --
5755 -- CompleteActivityInternalName (PUBLIC)
5756 -- Identical to CompleteActivity, except that the internal name of
5757 -- completed activity is passed instead of the activity instance label.
5758 -- NOTES:
5759 -- 1. There must be exactly ONE instance of this activity with NOTIFIED
5760 -- status.
5761 -- 2. Using this api to start a new process is not supported.
5762 -- 3. Synchronous processes are not supported in this api.
5763 -- 4. This should only be used if for some reason the instance label is
5764 -- not known. CompleteActivity should be used if the instance
5765 -- label is known.
5766 -- IN
5767 -- itemtype - A valid item type
5768 -- itemkey - A string generated from the application object's primary key.
5769 -- activity - Internal name of completed activity, in the format
5770 -- [<parent process_name>:]<process activity_name>
5771 -- <result> - An optional result.
5772 --
5773 procedure CompleteActivityInternalName(
5774 itemtype in varchar2,
5775 itemkey in varchar2,
5776 activity in varchar2,
5777 result in varchar2,
5778 raise_engine_exception in boolean default FALSE)
5779 is
5780 colon pls_integer;
5781 process varchar2(30);
5782 actname varchar2(30);
5783 label varchar2(30);
5784 begin
5785 WF_CORE.TAG_DB_SESSION(WF_CORE.CONN_TAG_WF, CompleteActivityInternalName.itemtype);
5786 -- Not allowed in synch mode
5787 if (itemkey = wf_engine.eng_synch) then
5788 wf_core.token('OPERATION', 'Wf_Engine.CompleteActivityInternalName');
5789 wf_core.raise('WFENG_SYNCH_DISABLED');
5790 end if;
5791
5792 -- Argument validation
5793 if ((itemtype is null) or (itemkey is null) or (activity is null)) then
5794 Wf_Core.Token('ITEMTYPE', itemtype);
5795 Wf_Core.Token('ITEMKEY', itemkey);
5796 Wf_Core.Token('ACTIVITY', activity);
5797 Wf_Core.Raise('WFSQL_ARGS');
5798 end if;
5799
5800 -- Parse activity arg into <process_name> and <activity_name> components.
5801 colon := instr(activity, ':');
5802 if (colon <> 0) then
5803 -- Activity arg is <process name>:<activity name>
5804 process := substr(activity, 1, colon-1);
5805 actname := substr(activity, colon+1);
5806 else
5807 -- Activity arg is just activity name
5808 process := '';
5809 actname := activity;
5810 end if;
5811
5812 -- Look up activity instance label
5813 begin
5814 select WPA.PROCESS_NAME, WPA.INSTANCE_LABEL
5815 into process, label
5816 from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_PROCESS_ACTIVITIES WPA
5817 where WIAS.ITEM_TYPE = itemtype
5818 and WIAS.ITEM_KEY = itemkey
5819 and WIAS.ACTIVITY_STATUS = wf_engine.eng_notified
5820 and WIAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID
5821 and WPA.ACTIVITY_NAME = actname
5822 and WPA.PROCESS_NAME = nvl(process, WPA.PROCESS_NAME);
5823 exception
5824 when no_data_found then
5825 Wf_Core.Token('TYPE', itemtype);
5826 Wf_Core.Token('KEY', itemkey);
5827 Wf_Core.Token('NAME', activity);
5828 Wf_Core.Raise('WFENG_NOT_NOTIFIED');
5829 end;
5830
5831 -- Complete activity with the correct arguments
5832 Wf_Engine.CompleteActivity(itemtype, itemkey, process||':'||label,
5833 result, CompleteActivityInternalName.raise_engine_exception);
5834
5835 exception
5836 when others then
5837 Wf_Core.Context('Wf_Engine', 'CompleteActivityInternalName',
5838 itemtype, itemkey, activity, result);
5839 raise;
5840 end CompleteActivityInternalName;
5841
5842 --
5843 -- AssignActivity (PUBLIC)
5844 -- Assigns or re-assigns the user who will perform an activity. It may be
5845 -- called before the activity has been enabled(transitioned to). If a user
5846 -- is assigned to an activity that already has an outstanding notification,
5847 -- that notification will be forwarded to the new user.
5848 -- IN
5849 -- itemtype - A valid item type
5850 -- itemkey - A string generated from the application object's primary key.
5851 -- activity - Activity to assign, specified in the form
5852 -- [<parent process_name>:]<process instance_label>
5853 -- performer - User who will perform this activity.
5854 -- reassignType - DELEGATE, TRANSFER or null
5855 -- ntfComments - Comments while reassigning
5856 -- 16-DEC-03 shanjgik bug 2722369 new parameters added
5857 procedure AssignActivity(itemtype in varchar2,
5858 itemkey in varchar2,
5859 activity in varchar2,
5860 performer in varchar2,
5861 reassignType in varchar2,
5862 ntfComments in varchar2) is
5863 root varchar2(30);
5864 version pls_integer;
5865 rootid pls_integer;
5866 actid pls_integer;
5867 status varchar2(8);
5868 notid pls_integer;
5869 user varchar2(320);
5870 acttype varchar2(8);
5871 actdate date;
5872 msg varchar2(30);
5873 msgtype varchar2(8);
5874 expand_role varchar2(1);
5875 begin
5876 -- Not allowed in synch mode
5877 if (itemkey = wf_engine.eng_synch) then
5878 wf_core.token('OPERATION', 'Wf_Engine.AssignActivity');
5879 wf_core.raise('WFENG_SYNCH_DISABLED');
5880 end if;
5881
5882 -- Argument validation
5883 if ((itemtype is null) or (itemkey is null) or (activity is null) or
5884 (performer is null)) then
5885 Wf_Core.Token('ITEMTYPE', itemtype);
5886 Wf_Core.Token('ITEMKEY', itemkey);
5887 Wf_Core.Token('ACTIVITY', activity);
5888 Wf_Core.Token('PERFORMER', performer);
5889 Wf_Core.Raise('WFSQL_ARGS');
5890 end if;
5891
5892 -- Get the root process for this key, and check that the item
5893 -- has been created.
5894 Wf_Item.Root_Process(itemtype, itemkey, root, version);
5895 if (root is null) then
5896 Wf_Core.Token('TYPE', itemtype);
5897 Wf_Core.Token('KEY', itemkey);
5898 Wf_Core.Raise('WFENG_ITEM');
5899 end if;
5900
5901 -- Get the root process actid.
5902 rootid := Wf_Process_Activity.RootInstanceId(itemtype, itemkey, root);
5903 if (rootid is null) then
5904 Wf_Core.Token('TYPE', itemtype);
5905 Wf_Core.Token('KEY', itemkey);
5906 Wf_Core.Token('NAME', root);
5907 Wf_Core.Raise('WFENG_ITEM_ROOT');
5908 end if;
5909
5910 -- Get the actid and check that this is a valid activity in the
5911 -- root process
5912 actdate := Wf_Item.Active_Date(itemtype, itemkey);
5913 actid := Wf_Process_Activity.FindActivity(rootid, activity, actdate);
5914 if (actid is null) then
5915 Wf_Core.Token('TYPE', itemtype);
5916 Wf_Core.Token('KEY', itemkey);
5917 Wf_Core.Token('NAME', activity);
5918 Wf_Core.Raise('WFENG_ITEM_ACTIVITY');
5919 end if;
5920
5921 -- Check if this activity is a notification type of activity
5922 acttype := Wf_Activity.Type(itemtype, activity, actdate);
5923 if (acttype <> wf_engine.eng_notification) then
5924 Wf_Core.Token('NAME', activity);
5925 Wf_Core.Raise('WFENG_NOTIFICATION_NAME');
5926 end if;
5927
5928 -- Check if the activity is active
5929 Wf_Item_Activity_Status.Status(itemtype, itemkey, actid, status);
5930
5931 if (status is null) then
5932 -- Insert one row with the performer
5933 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, actid,
5934 wf_engine.eng_waiting, '', null, null, newStatus=>TRUE);
5935 Wf_Item_Activity_Status.Update_Notification(itemtype, itemkey, actid,
5936 '', performer);
5937 elsif (status = wf_engine.eng_waiting) then
5938 Wf_Item_Activity_Status.Update_Notification(itemtype, itemkey, actid,
5939 '', performer);
5940 elsif (status in (wf_engine.eng_notified, wf_engine.eng_error)) then
5941 -- Check this is not a voting activity.
5942 -- Voting activities cannot be re-assigned.
5943 Wf_Activity.Notification_Info(itemtype, itemkey, actid, msg, msgtype,
5944 expand_role);
5945 if (expand_role = 'Y') then
5946 Wf_Core.Token('NAME', activity);
5947 Wf_Core.Raise('WFENG_VOTE_REASSIGN');
5948 end if;
5949
5950 -- Get notification id
5951 Wf_Item_Activity_Status.Notification_Status(itemtype, itemkey, actid,
5952 notid, user);
5953 -- Update the assigned user column in WIAS
5954 Wf_Item_Activity_Status.Update_Notification(itemtype, itemkey, actid,
5955 notid, performer);
5956
5957 if (notid is not null) then
5958 -- 16-DEC-03 shanjgik bug fix 2722369 check for reassignType added
5959 if (reassignType = Wf_Engine.eng_delegate) then
5960 -- delegate the notification
5961 Wf_Notification.Forward(notid, performer, ntfComments);
5962 else -- case reassignType is TRANSFER or null
5963 -- Call Wf_Notification.Transfer(notid, performer) to transfer
5964 -- ownership of the notification to the new performer.
5965 Wf_Notification.Transfer(notid, performer, ntfComments);
5966 end if;
5967 end if;
5968 else
5969 -- Activity must be complete (all other statuses are not valid
5970 -- for a notification).
5971 Wf_Core.Token('ACTIVITY', activity);
5972 Wf_Core.Token('TYPE', itemtype);
5973 Wf_Core.Token('KEY', itemkey);
5974 Wf_Core.Raise('WFENG_ITEM_ACTIVITY_COMPLETE');
5975 end if;
5976
5977 EXCEPTION
5978 when OTHERS then
5979 Wf_Core.Context('Wf_Engine', 'AssignActivity', itemtype, itemkey,
5980 activity, performer);
5981 raise;
5982 end AssignActivity;
5983
5984 --
5985 -- HandleErrorInternal (PRIVATE)
5986 -- Reset the process thread to the given activity and begin execution
5987 -- again from that point. If command is:
5988 -- SKIP - mark the activity complete with given result and continue
5989 -- RETRY - re-execute the activity before continuing
5990 -- IN
5991 -- itemtype - A valid item type.
5992 -- itemkey - The item key of the process.
5993 -- root - Root acitivity label
5994 -- rootid - Root acitivty id
5995 -- activity - Activity label
5996 -- actid - Activity id to reset
5997 -- actdate - Active Date
5998 -- command - SKIP or RETRY.
5999 -- <result> - Activity result for the 'SKIP' command.
6000 --
6001 procedure HandleErrorInternal(itemtype in varchar2,
6002 itemkey in varchar2,
6003 root in varchar2,
6004 rootid in number,
6005 activity in varchar2,
6006 actid in number,
6007 actdate in date,
6008 command in varchar2,
6009 result in varchar2 default '')
6010 is
6011 version pls_integer;
6012 funcname varchar2(240);
6013 resultout varchar2(240);
6014
6015 trig_savepoint exception;
6016 pragma exception_init(trig_savepoint, -04092);
6017 dist_savepoint exception;
6018 pragma exception_init(dist_savepoint, -02074);
6019
6020 --Bug 1166527
6021 event_name VARCHAR2(240);
6022 l_parameterlist wf_parameter_list_t := wf_parameter_list_t();
6023 begin
6024 WF_CORE.TAG_DB_SESSION(WF_CORE.CONN_TAG_WF, HandleErrorInternal.itemtype);
6025 -- Not allowed in synch mode
6026 -- Validate this before calling this function
6027
6028 -- No Argument validation
6029 -- Validate this before calling this function
6030
6031 -- Make sure item is valid
6032 -- Validate this before calling this function
6033
6034 -- Reset the process starting from the goal activity.
6035 -- This reset behaves similar to loop reset, cancelling activities,
6036 -- moving rows to history, etc. It then resets the activity status
6037 -- to active, AND resets or creates status rows for any parent process
6038 -- to active if necessary.
6039 if (not Wf_Engine_Util.Reset_Tree(itemtype, itemkey, rootid,
6040 actid, actdate)) then
6041 Wf_Core.Token('TYPE', itemtype);
6042 Wf_Core.Token('KEY', itemkey);
6043 Wf_Core.Token('NAME', activity);
6044 Wf_Core.Raise('WFENG_ITEM_ACTIVITY');
6045 end if;
6046
6047 if (command = wf_engine.eng_skip) then
6048 -- *** SKIP ***
6049 -- Mark activity complete with given result
6050 begin
6051 savepoint wf_savepoint;
6052
6053 -- execute the activity function with SKIP command (bug 2425229)
6054 funcname := Wf_Activity.Activity_Function(itemtype, itemkey, actid);
6055
6056 if (funcname is not null) then -- <6636968>
6057
6058 Wf_Engine_Util.Function_Call(funcname, itemtype, itemkey, actid, wf_engine.eng_skip,
6059 resultout);
6060
6061 -- Check if skip is allowed on this activity
6062 if (resultout = wf_engine.eng_noskip) then
6063 Wf_Core.Token('LABEL', Wf_Engine.GetActivityLabel(actid));
6064 Wf_Core.Raise('WFENG_NOSKIP');
6065 end if;
6066 end if;-- </6636968>
6067
6068 Wf_Engine_Util.Complete_Activity(itemtype, itemkey, actid, result, FALSE);
6069
6070 exception
6071 when trig_savepoint or dist_savepoint then
6072 -- You must be in a restricted environment,
6073 -- no fancy error processing for you! Try running directly.
6074 Wf_Engine_Util.Complete_Activity(itemtype, itemkey, actid,
6075 result, FALSE);
6076 when others then
6077 if (Wf_Core.Error_Name = 'WFENG_NOSKIP') then
6078 -- No processing. Raise to the caller that the activity cannot be skipped.
6079 raise;
6080 else
6081 -- If anything in this process raises an exception:
6082 -- 1. rollback any work in this process thread
6083 -- 2. set this activity to error status
6084 -- 3. execute the error process (if any)
6085 -- 4. clear the error to continue with next activity
6086 rollback to wf_savepoint;
6087 Wf_Core.Context('Wf_Engine', 'HandleErrorInternal', itemtype, itemkey,
6088 activity, command, result);
6089 Wf_Item_Activity_Status.Set_Error(itemtype, itemkey, actid,
6090 wf_engine.eng_exception, FALSE);
6091 Wf_Engine_Util.Execute_Error_Process(itemtype, itemkey, actid,
6092 wf_engine.eng_exception);
6093 Wf_Core.Clear;
6094 end if;
6095 end;
6096 --We will raise the skip event here .
6097 event_name := 'oracle.apps.wf.engine.skip';
6098 else
6099 -- *** RETRY ***
6100 if (actid = rootid) then
6101 -- Restart root process from beginnning
6102 Wf_Engine.StartProcess(itemtype, itemkey);
6103 else
6104 -- Start at given activity
6105 begin
6106 savepoint wf_savepoint;
6107 Wf_Engine_Util.Process_Activity(itemtype, itemkey, actid,
6108 Wf_Engine.Threshold, TRUE);
6109 exception
6110 when trig_savepoint or dist_savepoint then
6111 -- You must be in a restricted environment,
6112 -- no fancy error processing for you!
6113 -- Immediately defer activity to background engine.
6114 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey,
6115 actid, wf_engine.eng_deferred, wf_engine.eng_null,
6116 SYSDATE, null);
6117 when others then
6118 -- If anything in this process raises an exception:
6119 -- 1. rollback any work in this process thread
6120 -- 2. set this activity to error status
6121 -- 3. execute the error process (if any)
6122 -- 4. clear the error to continue with next activity
6123 rollback to wf_savepoint;
6124 Wf_Core.Context('Wf_Engine', 'HandleErrorInternal',itemtype,itemkey,
6125 activity, command, result);
6126 Wf_Item_Activity_Status.Set_Error(itemtype, itemkey, actid,
6127 wf_engine.eng_exception, FALSE);
6128 Wf_Engine_Util.Execute_Error_Process(itemtype, itemkey, actid,
6129 wf_engine.eng_exception);
6130 Wf_Core.Clear;
6131 end;
6132 end if;
6133 event_name := 'oracle.apps.wf.engine.retry';
6134 end if;
6135
6136 -- Store the info for Audit (Bug 5903106 - moved from HandleError to here)
6137 Wf_Item_Activity_Status.Audit(itemtype, itemkey, actid, upper(command), null);
6138
6139 --Pass the signature of the handle error API in the
6140 --parameter list
6141 wf_event.AddParameterToList('ITMETYPE', itemtype, l_parameterlist);
6142 wf_event.AddParameterToList('ITEMKEY', itemkey, l_parameterlist);
6143 wf_event.AddParameterToList('ACTIVITY', activity, l_parameterlist);
6144 if (result is NOT NULL) then
6145 wf_event.AddParameterToList('RESULT', result, l_parameterlist);
6146 end if;
6147
6148 -- Raise the event
6149 wf_event.Raise(p_event_name => event_name,
6150 p_event_key => itemkey,
6151 p_parameters => l_parameterlist);
6152
6153 exception
6154 when others then
6155 Wf_Core.Context('Wf_Engine', 'HandleErrorInternal', itemtype, itemkey,
6156 activity, command, result);
6157 raise;
6158 end HandleErrorInternal;
6159
6160 --
6161 -- HandleError (PUBLIC)
6162 -- Reset the process thread to the given activity and begin execution
6163 -- again from that point. If command is:
6164 -- SKIP - mark the activity complete with given result and continue
6165 -- RETRY - re-execute the activity before continuing
6166 -- IN
6167 -- itemtype - A valid item type.
6168 -- itemkey - The item key of the process.
6169 -- activity - Activity to reset, specified in the form
6170 -- [<parent process_name>:]<process instance_label>
6171 -- command - SKIP or RETRY.
6172 -- <result> - Activity result for the 'SKIP' command.
6173 --
6174 procedure HandleError(itemtype in varchar2,
6175 itemkey in varchar2,
6176 activity in varchar2,
6177 command in varchar2,
6178 result in varchar2)
6179 is
6180 root varchar2(30);
6181 version pls_integer;
6182 rootid pls_integer;
6183 actid pls_integer;
6184 actdate date;
6185
6186 trig_savepoint exception;
6187 pragma exception_init(trig_savepoint, -04092);
6188 dist_savepoint exception;
6189 pragma exception_init(dist_savepoint, -02074);
6190 begin
6191 WF_CORE.TAG_DB_SESSION(WF_CORE.CONN_TAG_WF, HandleError.itemtype);
6192 -- Not allowed in synch mode
6193 if (itemkey = wf_engine.eng_synch) then
6194 wf_core.token('OPERATION', 'Wf_Engine.HandleError');
6195 wf_core.raise('WFENG_SYNCH_DISABLED');
6196 end if;
6197
6198 -- Argument validation
6199 if ((itemtype is null) or (itemkey is null) or (activity is null) or
6200 (upper(command) not in (wf_engine.eng_skip, wf_engine.eng_retry))) then
6201 Wf_Core.Token('ITEMTYPE', itemtype);
6202 Wf_Core.Token('ITEMKEY', itemkey);
6203 Wf_Core.Token('ACTIVITY', activity);
6204 Wf_Core.Token('COMMAND', command);
6205 Wf_Core.Raise('WFSQL_ARGS');
6206 end if;
6207
6208
6209 -- If we are in a different Fwk session, need to clear Workflow PLSQL state
6210 if (not Wfa_Sec.CheckSession) then
6211 Wf_Global.Init;
6212 end if;
6213
6214 -- Make sure item is valid
6215 Wf_Item.Root_Process(itemtype, itemkey, root, version);
6216 if (root is null) then
6217 Wf_Core.Token('TYPE', itemtype);
6218 Wf_Core.Token('KEY', itemkey);
6219 Wf_Core.Raise('WFENG_ITEM');
6220 end if;
6221 rootid := Wf_Process_Activity.RootInstanceId(itemtype, itemkey, root);
6222 if (rootid is null) then
6223 Wf_Core.Token('TYPE', itemtype);
6224 Wf_Core.Token('KEY', itemkey);
6225 Wf_Core.Token('NAME', root);
6226 Wf_Core.Raise('WFENG_ITEM_ROOT');
6227 end if;
6228
6229 -- Look for the activity instance for this item
6230 actdate := Wf_Item.Active_Date(itemtype, itemkey);
6231 actid := Wf_Process_Activity.FindActivity(rootid, activity, actdate);
6232
6233 if (actid is null) then
6234 Wf_Core.Token('TYPE', itemtype);
6235 Wf_Core.Token('PROCESS', root);
6236 Wf_Core.Token('NAME', activity);
6237 Wf_Core.Raise('WFENG_ACTIVITY_EXIST');
6238 end if;
6239
6240 if (WF_CACHE.MetaRefreshed) then
6241 null;
6242
6243 end if;
6244
6245 -- Call the internal function to do the real job
6246 HandleErrorInternal(itemtype, itemkey, root, rootid, activity, actid,
6247 actdate, upper(command), result);
6248
6249 exception
6250 when others then
6251 Wf_Core.Context('Wf_Engine', 'HandleError', itemtype, itemkey, activity,
6252 command, result);
6253 raise;
6254 end HandleError;
6255
6256 --
6257 -- HandleErrorAll (PUBLIC)
6258 -- Reset the process thread to the given item type and/or item key and/or
6259 -- activity.
6260 -- IN
6261 -- itemtype - A valid item type.
6262 -- itemkey - The item key of the process.
6263 -- activity - Activity to reset, specified in the form
6264 -- [<parent process_name>:]<process instance_label>
6265 -- command - SKIP or RETRY.
6266 -- <result> - Activity result for the "SKIP" command.
6267 -- docommit - True if you want a commit for every n iterations.
6268 -- n is defined as wf_engine.commit_frequency
6269 --
6270 procedure HandleErrorAll(itemtype in varchar2,
6271 itemkey in varchar2,
6272 activity in varchar2,
6273 command in varchar2,
6274 result in varchar2,
6275 docommit in boolean)
6276 is
6277 root varchar2(30);
6278 version number;
6279 rootid number;
6280 actdate date;
6281
6282 c_item_key varchar2(240);
6283 c_activity varchar2(30);
6284 c_actid number;
6285
6286 cursor actc(x_itemtype varchar2, x_itemkey varchar2, x_activity varchar2) is
6287 select ias.ITEM_KEY,
6288 pa.INSTANCE_LABEL activity,
6289 pa.INSTANCE_ID actid
6290 from WF_ITEM_ACTIVITY_STATUSES ias,
6291 WF_PROCESS_ACTIVITIES pa
6292 where ias.ITEM_TYPE = x_itemtype
6293 and (x_itemkey is null or ias.ITEM_KEY = x_itemkey)
6294 and (x_activity is null or pa.INSTANCE_LABEL = x_activity)
6295 and ias.PROCESS_ACTIVITY = pa.INSTANCE_ID
6296 and ias.ACTIVITY_STATUS = 'ERROR';
6297
6298 begin
6299 --Check arguments.
6300 if (itemtype is null) then
6301 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
6302 Wf_Core.Raise('WFSQL_ARGS');
6303 end if;
6304
6305 if (WF_CACHE.MetaRefreshed) then
6306 null;
6307
6308 end if;
6309
6310 -- outer loop
6311 <<outer_handle>>
6312 loop
6313
6314 open actc(itemtype, itemkey, activity);
6315
6316 -- inner loop
6317 <<handle_loop>>
6318 loop
6319
6320 fetch actc into c_item_key, c_activity, c_actid;
6321 if (actc%notfound) then
6322 exit outer_handle;
6323 end if;
6324
6325 -- Not allowed in synch mode
6326 if (c_item_key = wf_engine.eng_synch) then
6327 wf_core.token('OPERATION', 'Wf_Engine.HandleErrorAll');
6328 wf_core.raise('WFENG_SYNCH_DISABLED');
6329 end if;
6330
6331 -- Argument validation
6332 if ((itemtype is null) or (c_item_key is null) or (c_activity is null) or
6333 (upper(command) not in (wf_engine.eng_skip, wf_engine.eng_retry)))
6334 then
6335 Wf_Core.Token('ITEMTYPE', itemtype);
6336 Wf_Core.Token('ITEMKEY', c_item_key);
6337 Wf_Core.Token('ACTIVITY', c_activity);
6338 Wf_Core.Token('COMMAND', command);
6339 Wf_Core.Raise('WFSQL_ARGS');
6340 end if;
6341
6342 -- Make sure item is valid
6343 Wf_Item.Root_Process(itemtype, c_item_key, root, version);
6344 if (root is null) then
6345 Wf_Core.Token('TYPE', itemtype);
6346 Wf_Core.Token('KEY', c_item_key);
6347 Wf_Core.Raise('WFENG_ITEM');
6348 end if;
6349 rootid := Wf_Process_Activity.RootInstanceId(itemtype, c_item_key, root);
6350 if (rootid is null) then
6351 Wf_Core.Token('TYPE', itemtype);
6352 Wf_Core.Token('KEY', c_item_key);
6353 Wf_Core.Token('NAME', root);
6354 Wf_Core.Raise('WFENG_ITEM_ROOT');
6355 end if;
6356
6357 -- Look for the activity instance for this item
6358 actdate := Wf_Item.Active_Date(itemtype, c_item_key);
6359
6360 -- Call the internal function to do the real job
6361 HandleErrorInternal(itemtype, c_item_key, root, rootid, c_activity,
6362 c_actid, actdate, upper(command), result);
6363
6364 exit handle_loop when
6365 (docommit and (actc%rowcount = wf_engine.commit_frequency));
6366
6367 end loop handle_loop;
6368
6369 if (actc%ISOPEN) then
6370 close actc;
6371 end if;
6372
6373 if (docommit) then
6374 commit;
6375 Fnd_Concurrent.Set_Preferred_RBS;
6376 end if;
6377 end loop outer_handle;
6378
6379 if (docommit) then
6380 commit;
6381 Fnd_Concurrent.Set_Preferred_RBS;
6382 end if;
6383
6384 if (actc%ISOPEN) then
6385 close actc;
6386 end if;
6387
6388 exception
6389 when others then
6390 Wf_Core.Context('Wf_Engine', 'HandleErrorAll', itemtype, itemkey);
6391 raise;
6392 end HandleErrorAll;
6393
6394 --
6395 -- ItemStatus (Public)
6396 -- This is a public cover for WF_ITEM_ACTIVITY_STATUS.ROOT_STATUS
6397 -- Returns the status and result for the root process of this item.
6398 -- If the item does not exist an exception will be raised.
6399 -- IN
6400 -- itemtype - Activity item type.
6401 -- itemkey - The item key.
6402 -- OUT
6403 -- status - Activity status for root process of this item
6404 -- result - Result code for root process of this item
6405 --
6406 procedure ItemStatus(itemtype in varchar2,
6407 itemkey in varchar2,
6408 status out NOCOPY varchar2,
6409 result out NOCOPY varchar2) is
6410 begin
6411 --Check arguments.
6412 if ((itemtype is null) or
6413 (itemkey is null)) then
6414 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
6415 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
6416 Wf_Core.Raise('WFSQL_ARGS');
6417 end if;
6418
6419 wf_item_activity_status.root_status(itemtype,itemkey,status,result);
6420 exception
6421 when others then
6422 Wf_Core.Context('Wf_Engine', 'ItemStatus', itemtype, itemkey);
6423 raise;
6424 end ItemStatus;
6425
6426 -- API to reterive more granular information from the
6427 -- item
6428 -- If the item is active and
6429 -- If there is an errored activity then status is set to ERROR
6430 -- the errname , errmsg and errstack info is given
6431 -- activity , error stack etc are provided
6432 -- If the first activity is deferred then the actid of the same
6433 -- is provided and the item status is given as 'DEFERRED'
6434 -- If an activity is in notified status then we get the
6435 -- actid of the same.
6436 procedure ItemInfo(itemtype in varchar2,
6437 itemkey in varchar2,
6438 status out NOCOPY varchar2,
6439 result out NOCOPY varchar2,
6440 actid out NOCOPY number,
6441 errname out NOCOPY varchar2,
6442 errmsg out NOCOPY varchar2,
6443 errstack out NOCOPY varchar2)
6444 is
6445 l_status varchar2(8);
6446 l_result varchar2(30);
6447 l_instance_id number;
6448
6449 --Order all activities for this itemtype ,key
6450 --giving priority to ERROR , NOTIFIED , DEFERRED (--> in that order)
6451 --and execution time
6452 /*
6453 Lets do a single select for rownum < 1 this
6454 should suffice
6455
6456 cursor act_curs (p_itemtype varchar2, p_itemkey varchar2) is
6457 select pa.instance_label,pa.instance_id
6458 ias.activity_status,
6459 ias.activity_result_code ,
6460 ias.assigned_user,
6461 ias.notification_id NID,
6462 ntf.status,
6463 ias.performed_by
6464 from wf_item_activity_statuses ias,
6465 wf_process_activities pa,
6466 wf_activities ac,
6467 wf_activities ap,
6468 wf_items i,
6469 wf_notifications ntf
6470 where ias.item_type = p_itemtype
6471 and ias.item_key = p_itemkey
6472 and ias.activity_status = wf_engine.eng_completed
6473 and ias.process_activity = pa.instance_id
6474 and pa.activity_name = ac.name
6475 and pa.activity_item_type = ac.item_type
6476 and pa.process_name = ap.name
6477 and pa.process_item_type = ap.item_type
6478 and pa.process_version = ap.version
6479 and i.item_type = '&item_type'
6480 and i.item_key = ias.item_key
6481 and i.begin_date >= ac.begin_date
6482 and i.begin_date < nvl(ac.end_date, i.begin_date+1)
6483 and ntf.notification_id(+) = ias.notification_id
6484 order by decode(ias.activity_status,'ERROR',1,'NOTIFIED',2,'DEFERRED',3,'SUSPEND',4,'WAITING',5,'ACTIVE',6,'COMPLETE',7) asc , ias.execution_time desc
6485 */
6486
6487 begin
6488 --Get the item status
6489 --Use the API above for the same
6490 wf_engine.ItemStatus(itemtype ,itemkey ,l_status,l_result);
6491
6492 --Now check the status if root has completed
6493 --we do not want to go further lower
6494 --Else if the root is still active , lets find
6495 --where the execution is stuck at.
6496
6497 if l_status= 'ACTIVE' then
6498 --Get last executed activities result and status
6499 select process_activity,
6500 activity_status,
6501 activity_result_code
6502 into l_instance_id,
6503 l_status,
6504 l_result
6505 from
6506 (
6507 select process_activity,
6508 activity_status,
6509 activity_result_code
6510 from wf_item_activity_statuses
6511 where item_type = itemtype
6512 and item_key = itemkey
6513 and activity_status <> wf_engine.eng_completed
6514 order by decode(activity_status, 'ERROR',1, 'NOTIFIED',2, 'DEFERRED',3,
6515 'SUSPEND',4, 'WAITING',5, 'ACTIVE',6, 7) asc,
6516 begin_date desc, execution_time desc
6517 )
6518 where rownum < 2;
6519
6520 --Now lets start getting all details out of the last activity
6521 if l_status = 'ERROR' then
6522 --Populate the error stack
6523 wf_item_activity_status.Error_Info(itemtype,itemkey,l_instance_id,errname,errmsg,errstack);
6524 end if;
6525
6526 status := l_status;
6527 result := l_result;
6528 actid := l_instance_id;
6529 --U can get it using the actid using Notification_Status API
6530 --nid := l_notification_id;
6531
6532
6533 else
6534 --If the root is not active return whatever is its status
6535 --and result
6536 status := l_status ;
6537 result := l_result ;
6538 end if;
6539 exception
6540 when others then
6541 Wf_Core.Context('Wf_Engine', 'ItemInfo', itemtype, itemkey);
6542 raise;
6543 end ItemInfo;
6544
6545
6546
6547
6548 --
6549 -- Activity_Exist_In_Process (Public)
6550 -- Check if an activity exist in a process
6551 -- ### OBSOLETE - Use FindActivity instead ###
6552 -- ### DO NOT REMOVE, refer to bug 1869241 ###
6553 -- IN
6554 -- p_item_type
6555 -- p_item_key
6556 -- p_activity_item_type
6557 -- p_activity_name
6558 -- RET
6559 -- TRUE if activity exist, FALSE otherwise
6560 --
6561 function Activity_Exist_In_Process (
6562 p_item_type in varchar2,
6563 p_item_key in varchar2,
6564 p_activity_item_type in varchar2,
6565 p_activity_name in varchar2)
6566 return boolean
6567 is
6568 rootactivity varchar2(30);
6569 active_date date;
6570 begin
6571 begin
6572 select ROOT_ACTIVITY, BEGIN_DATE
6573 into rootactivity, active_date
6574 from WF_ITEMS
6575 where ITEM_TYPE = p_item_type
6576 and ITEM_KEY = p_item_key;
6577 exception
6578 -- if itemtype/itemkey combination not exists, treats it as not exists
6579 when NO_DATA_FOUND then
6580 return FALSE;
6581
6582 when OTHERS then
6583 raise;
6584 end;
6585
6586 return(Wf_Engine.Activity_Exist(
6587 p_process_item_type=>p_item_type,
6588 p_process_name=>rootactivity,
6589 p_activity_item_type=>p_activity_item_type,
6590 p_activity_name=>p_activity_name,
6591 active_date=>active_date));
6592
6593 exception
6594 when others then
6595 Wf_Core.Context('Wf_Engine', 'Activity_Exist_In_Process',
6596 p_item_type, p_item_key,
6597 nvl(p_activity_item_type, p_item_type),
6598 p_activity_name);
6599 raise;
6600 end Activity_Exist_In_Process;
6601
6602 --
6603 -- Activity_Exist
6604 -- Check if an activity exist in a process
6605 -- ### OBSOLETE - Use FindActivity instead. ###
6606 -- ### DO NOT REMOVE, refer to bug 1869241 ###
6607 -- IN
6608 -- p_process_item_type
6609 -- p_process_name
6610 -- p_activity_item_type
6611 -- p_anctivity_name
6612 -- active_date
6613 -- iteration - maximum 8 level deep (0-7)
6614 -- RET
6615 -- TRUE if activity exist, FALSE otherwise
6616 --
6617 function Activity_Exist (
6618 p_process_item_type in varchar2,
6619 p_process_name in varchar2,
6620 p_activity_item_type in varchar2 default null,
6621 p_activity_name in varchar2,
6622 active_date in date default sysdate,
6623 iteration in number default 0)
6624 return boolean
6625 is
6626 m_version number;
6627 n number;
6628
6629 cursor actcur(ver number) is
6630 select WPA.ACTIVITY_ITEM_TYPE, WPA.ACTIVITY_NAME
6631 from WF_PROCESS_ACTIVITIES WPA,
6632 WF_ACTIVITIES WA
6633 where WPA.PROCESS_ITEM_TYPE = p_process_item_type
6634 and WPA.PROCESS_NAME = p_process_name
6635 and WPA.PROCESS_VERSION = ver
6636 and WPA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE
6637 and WPA.ACTIVITY_NAME = WA.NAME
6638 and WA.TYPE = 'PROCESS'
6639 and active_date >= WA.BEGIN_DATE
6640 and active_date < nvl(WA.END_DATE, active_date+1);
6641
6642 begin
6643 -- first check the iteration to avoid infinite loop
6644 if (iteration > 7) then
6645 return FALSE;
6646 end if;
6647
6648 -- then get the active version
6649 begin
6650 select VERSION into m_version
6651 from WF_ACTIVITIES
6652 where ITEM_TYPE = p_process_item_type
6653 and NAME = p_process_name
6654 and active_date >= BEGIN_DATE
6655 and active_date < nvl(END_DATE, active_date + 1);
6656 exception
6657 -- no active version exist
6658 when NO_DATA_FOUND then
6659 return FALSE;
6660
6661 when OTHERS then
6662 raise;
6663 end;
6664
6665 -- then check to see if such activity exist
6666 select count(1) into n
6667 from WF_PROCESS_ACTIVITIES
6668 where PROCESS_ITEM_TYPE = p_process_item_type
6669 and PROCESS_NAME = p_process_name
6670 and PROCESS_VERSION = m_version
6671 and ACTIVITY_ITEM_TYPE = nvl(p_activity_item_type, p_process_item_type)
6672 and ACTIVITY_NAME = p_activity_name;
6673
6674 if (n = 0) then
6675 -- recursively check subprocesses
6676 for actr in actcur(m_version) loop
6677 if (Wf_Engine.Activity_Exist(
6678 actr.activity_item_type,
6679 actr.activity_name,
6680 nvl(p_activity_item_type, p_process_item_type),
6681 p_activity_name,
6682 active_date,
6683 iteration+1)
6684 ) then
6685 return TRUE;
6686 end if;
6687 end loop;
6688
6689 return FALSE;
6690 else
6691 return TRUE;
6692 end if;
6693
6694 exception
6695 when OTHERS then
6696 Wf_Core.Context('Wf_Engine', 'Activity_Exist',
6697 p_process_item_type, p_process_name,
6698 nvl(p_activity_item_type, p_process_item_type),
6699 p_activity_name);
6700 raise;
6701 end Activity_Exist;
6702
6703 --
6704 -- Event
6705 -- Signal event to workflow process
6706 -- IN
6707 -- itemtype - Item type of process
6708 -- itemkey - Item key of process
6709 -- process_name - Process to start (only if process not already running)
6710 -- event_message - Event message payload
6711 --
6712 procedure Event(
6713 itemtype in varchar2,
6714 itemkey in varchar2,
6715 process_name in varchar2,
6716 event_message in wf_event_t)
6717 is
6718 event_name varchar2(240);
6719 actdate date; -- Active date of item
6720 root varchar2(30); -- Root process name
6721 version pls_integer; -- Root process version
6722 rootid pls_integer; -- Root process instance id
6723 aname varchar2(30); -- Item attr name
6724 avalue varchar2(2000); -- Item attr value
6725 plist wf_parameter_list_t; -- Event message parameter list
6726
6727 -- Bug 2255002
6728 parent_itemtype varchar2(8); -- parent item type
6729 parent_itemkey varchar2(240); -- parent item key
6730
6731 -- Blocked activities waiting for event (if existing process)
6732 cursor evtacts is
6733 SELECT WIAS.PROCESS_ACTIVITY actid
6734 FROM WF_ITEM_ACTIVITY_STATUSES WIAS, WF_PROCESS_ACTIVITIES WPA,
6735 WF_ACTIVITIES WA
6736 WHERE WIAS.ITEM_TYPE = event.itemtype
6737 AND WIAS.ITEM_KEY = event.itemkey
6738 AND WIAS.ACTIVITY_STATUS = 'NOTIFIED'
6739 AND WIAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID
6740 AND WPA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE
6741 AND WPA.ACTIVITY_NAME = WA.NAME
6742 AND actdate >= WA.BEGIN_DATE
6743 AND actdate < NVL(WA.END_DATE, actdate+1)
6744 AND WA.TYPE = 'EVENT'
6745 AND WA.DIRECTION = 'RECEIVE'
6746 AND (WA.EVENT_NAME is null
6747 OR WA.EVENT_NAME in
6748 (SELECT WE.NAME -- Single events
6749 FROM WF_EVENTS WE
6750 WHERE WE.TYPE = 'EVENT'
6751 AND WE.NAME = event.event_name
6752 UNION ALL
6753 SELECT GRP.NAME -- Groups containing event
6754 FROM WF_EVENTS GRP, WF_EVENT_GROUPS WEG, WF_EVENTS MBR
6755 WHERE GRP.TYPE = 'GROUP'
6756 AND GRP.GUID = WEG.GROUP_GUID
6757 AND WEG.MEMBER_GUID = MBR.GUID
6758 AND MBR.NAME = event.event_name));
6759
6760 actarr InstanceArrayTyp; -- Event activities to execute
6761 i pls_integer := 0; -- Loop counter
6762
6763 l_lock boolean;
6764
6765 begin
6766 WF_CORE.TAG_DB_SESSION(WF_CORE.CONN_TAG_WF, Event.itemtype);
6767 -- Check args
6768 if ((itemtype is null) or
6769 (itemkey is null) or
6770 (event_message is null)) then
6771 Wf_Core.Token('ITEMTYPE', itemtype);
6772 Wf_Core.Token('ITEMKEY', itemkey);
6773 Wf_Core.Token('EVENT_MESSAGE', '');
6774 Wf_Core.Raise('WFSQL_ARGS');
6775 end if;
6776
6777 -- Not allowed in synch mode
6778 if (itemkey = wf_engine.eng_synch) then
6779 Wf_Core.Token('OPERATION', 'Wf_Engine.Set_Item_Parent');
6780 Wf_Core.Raise('WFENG_SYNCH_DISABLED');
6781 end if;
6782
6783 -- Retrieve event name from message
6784 event_name := event_message.GetEventName;
6785 if (event_name is null) then
6786 Wf_Core.Token('EVENT_MESSAGE.EVENT_NAME', '');
6787 Wf_Core.Raise('WFSQL_ARGS');
6788 end if;
6789
6790 if (WF_CACHE.MetaRefreshed) then
6791 null;
6792
6793 end if;
6794
6795 -- Check if item exists
6796 if (Wf_Item.Item_Exist(itemtype, itemkey)) then
6797
6798 -- Process is already running.
6799 --Acquire lock here so that no other session
6800 --will work on it.
6801 --Acquire lock here by opening the cursor
6802 l_lock := wf_item.acquire_lock(itemtype, itemkey,true);
6803
6804 -- Find all activities waiting for this event.
6805 actdate := WF_Item.Active_Date(itemtype, itemkey);
6806 for act in evtacts loop
6807 actarr(i) := act.actid;
6808 i := i + 1;
6809 end loop;
6810 actarr(i) := '';
6811
6812 else
6813 -- Process not running yet, create it.
6814 -- If process_name is null then will use selector function.
6815 Wf_Engine.CreateProcess(itemtype, itemkey, process_name);
6816 actdate := WF_Item.Active_Date(itemtype, itemkey);
6817
6818 -- Bug 2259039
6819 -- Start the new process
6820 Wf_Engine_Util.Start_Process_Internal(
6821 itemtype => itemtype,
6822 itemkey => itemkey,
6823 runmode => 'EVENT');
6824
6825 --Select the activities waiting to receive this event
6826 actdate := WF_Item.Active_Date(itemtype, itemkey);
6827 for act in evtacts loop
6828 actarr(i) := act.actid;
6829 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, act.actid,
6830 wf_engine.eng_notified, wf_engine.eng_null, sysdate, null);
6831 i := i + 1;
6832 end loop;
6833 actarr(i) := '';
6834 end if;
6835
6836 -- Check at least one matching event activity found
6837 if (i = 0) then
6838 Wf_Core.Token('TYPE', itemtype);
6839 Wf_Core.Token('KEY', itemkey);
6840 Wf_Core.Token('EVENT', event_name);
6841 Wf_Core.Raise('WFENG_EVENT_NOTFOUND');
6842 end if;
6843
6844 -- Set item attributes for all parameters contained in the event
6845 -- message body.
6846 -- NOTE: Must be done here AFTER the process has been created
6847 -- and BEFORE any activities are executed.
6848 plist := event_message.GetParameterList;
6849 if (plist is not null) then
6850 for i in plist.first .. plist.last loop
6851 aname := plist(i).GetName;
6852 avalue := plist(i).GetValue;
6853 begin
6854 if aname = '#CONTEXT' then
6855 -- Bug 2255002 - if the parent item type and parent item key
6856 -- already exist do nothing
6857 SELECT parent_item_type, parent_item_key
6858 INTO parent_itemtype, parent_itemkey
6859 FROM wf_items
6860 WHERE item_type = itemtype
6861 AND item_key = itemkey;
6862
6863 if (parent_itemtype is null and parent_itemkey is null ) then
6864 Wf_Engine.SetItemParent(itemtype => itemtype,
6865 itemkey => itemkey,
6866 parent_itemtype =>
6867 substr(avalue,1,
6868 instr(avalue,':')-1),
6869 parent_itemkey =>
6870 substr(avalue,
6871 instr(avalue,':')+1),
6872 parent_context => null);
6873 end if;
6874 elsif aname = '#OWNER_ROLE' then
6875 --Bug 2388634
6876 --This is for the applications to set their item owner
6877 --by including a #OWNER_ROLE parameter for the event
6878 wf_engine.SetItemowner(itemtype,itemkey,avalue);
6879
6880 else
6881 -- event item attributes may use canonical masks.
6882 Wf_Engine.SetEventItemAttr(itemtype, itemkey, aname, avalue);
6883 end if;
6884 exception
6885 when others then
6886 if (wf_core.error_name = 'WFENG_ITEM_ATTR') then
6887 -- If attr doesn't exist create runtime itemattr
6888 Wf_Core.Clear;
6889 Wf_Engine.AddItemAttr(itemtype, itemkey, aname, avalue);
6890 else
6891 raise; -- All other errors are raised up.
6892 end if;
6893 end;
6894 end loop;
6895 end if;
6896
6897 -- Complete matching event activities
6898 i := 0;
6899 while (actarr(i) is not null) loop
6900 begin
6901 savepoint wf_savepoint;
6902 -- Save event data to itemattrs requested by this activity.
6903 -- #EVENTNAME
6904 aname := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actarr(i),
6905 wf_engine.eng_eventname);
6906 if (aname is not null) then
6907 Wf_Engine.SetItemAttrText(itemtype, itemkey, aname, event_name);
6908 end if;
6909 -- #EVENTKEY
6910 aname := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actarr(i),
6911 wf_engine.eng_eventkey);
6912 if (aname is not null) then
6913 Wf_Engine.SetItemAttrText(itemtype, itemkey, aname,
6914 event_message.GetEventKey);
6915 end if;
6916 -- #EVENTMESSAGE
6917 aname := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actarr(i),
6918 wf_engine.eng_eventmessage);
6919 if (aname is not null) then
6920 Wf_Engine.SetItemAttrEvent(itemtype, itemkey, aname, event_message);
6921 end if;
6922
6923 -- Execute our lovely event activity (result is always null).
6924 Wf_Engine_Util.Complete_Activity(itemtype, itemkey, actarr(i),
6925 wf_engine.eng_null);
6926 exception
6927 when others then
6928 -- If anything in this process raises an exception:
6929 -- 1. rollback any work in this process thread
6930 -- 2. set this activity to error status
6931 -- 3. execute the error process (if any)
6932 -- 4. clear the error to continue with next activity
6933 rollback to wf_savepoint;
6934 Wf_Core.Context('Wf_Engine', 'Event', itemtype, itemkey,
6935 process_name, event_name);
6936 Wf_Item_Activity_Status.Set_Error(itemtype, itemkey, actarr(i),
6937 wf_engine.eng_exception, FALSE);
6938 Wf_Engine_Util.Execute_Error_Process(itemtype, itemkey, actarr(i),
6939 wf_engine.eng_exception);
6940 Wf_Core.Clear;
6941 end;
6942 i := i + 1;
6943 end loop;
6944
6945 exception
6946 when others then
6947 Wf_Core.Context('Wf_Engine', 'Event', itemtype, itemkey,
6948 process_name, event_name);
6949 raise;
6950 end Event;
6951
6952 --
6953 -- Event2
6954 -- Signal event to workflow process
6955 -- IN
6956 -- event_message - Event message payload
6957 --
6958 procedure Event2(
6959 event_message in wf_event_t)
6960 is
6961 event_name varchar2(240);
6962 actdate date; -- Active date of item
6963 root varchar2(30); -- Root process name
6964 version pls_integer; -- Root process version
6965 rootid pls_integer; -- Root process instance id
6966 aname varchar2(30); -- Item attr name
6967 avalue varchar2(2000); -- Item attr value
6968 plist wf_parameter_list_t; -- Event message parameter list
6969 businesskey varchar2(240);
6970
6971 -- Blocked activities waiting for event (if existing process)
6972 cursor evtacts is
6973 SELECT /*+ LEADING(WA) */ WIAS.ITEM_TYPE, WIAS.ITEM_KEY, WIAS.PROCESS_ACTIVITY ACTID
6974 FROM WF_ITEM_ACTIVITY_STATUSES WIAS,
6975 WF_PROCESS_ACTIVITIES WPA,
6976 (
6977 SELECT /*+ NO_MERGE */ WA.*
6978 FROM WF_ACTIVITIES WA
6979 where WA.TYPE = 'EVENT'
6980 AND WA.DIRECTION = 'RECEIVE'
6981 AND ( WA.EVENT_NAME IS NULL OR
6982 WA.EVENT_NAME = event2.event_name OR
6983 EXISTS
6984 (
6985 SELECT null -- Groups containing event
6986 FROM WF_EVENTS GRP, WF_EVENT_GROUPS WEG, WF_EVENTS MBR
6987 WHERE GRP.TYPE = 'GROUP'
6988 AND GRP.GUID = WEG.GROUP_GUID
6989 AND WEG.MEMBER_GUID = MBR.GUID
6990 AND MBR.NAME = event2.event_name
6991 AND GRP.NAME = WA.EVENT_NAME
6992 )
6993 )
6994 ) WA,
6995 WF_ITEMS WI
6996 WHERE WIAS.ACTIVITY_STATUS = 'NOTIFIED'
6997 AND WIAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID
6998 AND WIAS.ITEM_TYPE = WPA.PROCESS_ITEM_TYPE
6999 AND WPA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE
7000 AND WPA.ACTIVITY_NAME = WA.NAME
7001 AND EXISTS
7002 ( SELECT 1 FROM WF_ACTIVITY_ATTR_VALUES WAAV,
7003 WF_ITEM_ATTRIBUTE_VALUES WIAV
7004 WHERE WAAV.PROCESS_ACTIVITY_ID = WIAS.PROCESS_ACTIVITY
7005 AND WAAV.NAME = '#BUSINESS_KEY'
7006 AND WAAV.VALUE_TYPE = 'ITEMATTR'
7007 AND WIAV.ITEM_TYPE = WIAS.ITEM_TYPE
7008 AND WIAV.ITEM_KEY = WIAS.ITEM_KEY
7009 AND WAAV.TEXT_VALUE = WIAV.NAME
7010 AND WIAV.TEXT_VALUE = event2.businesskey)
7011 AND WI.ITEM_TYPE = WIAS.ITEM_TYPE
7012 AND WI.ITEM_KEY = WIAS.ITEM_KEY
7013 FOR UPDATE OF WI.ITEM_TYPE,WI.item_key NOWAIT;
7014
7015 ectacts_rec evtacts%ROWTYPE;
7016
7017 litemtype varchar2(8);
7018 litemkey varchar2(240);
7019 lactid number;
7020
7021 i pls_integer := 0; -- Loop counter
7022
7023 begin
7024
7025 -- Check args
7026 if ((event_message is null)) then
7027 Wf_Core.Token('EVENT_MESSAGE', '');
7028 Wf_Core.Raise('WFSQL_ARGS');
7029 end if;
7030
7031 -- Retrieve event name from message
7032 event_name := event_message.GetEventName;
7033 businesskey := event_message.GetEventKey;
7034
7035 if (event_name is null) then
7036 Wf_Core.Token('EVENT_MESSAGE.EVENT_NAME', '');
7037 Wf_Core.Raise('WFSQL_ARGS');
7038 end if;
7039
7040 --Here before opening the cursor we will set the savepoint
7041 --This is so that we do not have to depend on the cursor behaviour itself
7042 --but once the cursor fails to acquire lock we expliciltly rollback
7043 --But having the for update statement in the cursor eliminates the need
7044 --for explicitly locking the workitems .
7045
7046 savepoint wf_savepoint_event2;
7047 -- Find all activities waiting for this event.
7048 for evtacts_rec in evtacts loop
7049
7050 -- Set item attributes for all parameters contained in the event
7051 -- message body.
7052 -- NOTE: Must be done here AFTER the process has been created
7053 -- and BEFORE any activities are executed.
7054 plist := event_message.GetParameterList;
7055
7056 if ((plist is not null) and (plist.count > 0)) then
7057 for i in plist.first .. plist.last loop
7058 aname := plist(i).GetName;
7059 avalue := plist(i).GetValue;
7060 begin
7061 if aname = '#CONTEXT' then
7062 Wf_Engine.SetItemParent(itemtype => evtacts_rec.item_type,
7063 itemkey => evtacts_rec.item_key,
7064 parent_itemtype =>substr(avalue,1,instr(avalue,':')-1),
7065 parent_itemkey =>substr(avalue,instr(avalue,':')+1),
7066 parent_context => null);
7067 else
7068 -- event item attributes may use canonical masks.
7069 Wf_Engine.SetEventItemAttr(evtacts_rec.item_type,
7070 evtacts_rec.item_key, aname, avalue);
7071 end if;
7072 exception
7073 when others then
7074 if (wf_core.error_name = 'WFENG_ITEM_ATTR') then
7075 -- If attr doesn't exist create runtime itemattr
7076 Wf_Core.Clear;
7077
7078 Wf_Engine.AddItemAttr(evtacts_rec.item_type,
7079 evtacts_rec.item_key,
7080 aname, avalue);
7081 else
7082 raise; -- All other errors are raised up.
7083 end if;
7084 end;
7085 end loop;
7086 end if;
7087
7088 begin
7089 savepoint wf_savepoint;
7090 -- Save event data to itemattrs requested by this activity.
7091 -- #EVENTNAME
7092 aname := Wf_Engine.GetActivityAttrText(evtacts_rec.item_type,
7093 evtacts_rec.item_key,
7094 evtacts_rec.actid,
7095 wf_engine.eng_eventname);
7096 if (aname is not null) then
7097 Wf_Engine.SetItemAttrText(evtacts_rec.item_type,
7098 evtacts_rec.item_key,
7099 aname,
7100 event_name);
7101 end if;
7102 -- #EVENTKEY
7103 aname := Wf_Engine.GetActivityAttrText(evtacts_rec.item_type,
7104 evtacts_rec.item_key,
7105 evtacts_rec.actid,
7106 wf_engine.eng_eventkey);
7107 if (aname is not null) then
7108 Wf_Engine.SetItemAttrText(evtacts_rec.item_type,
7109 evtacts_rec.item_key, aname,
7110 event_message.GetEventKey);
7111 end if;
7112 -- #EVENTMESSAGE
7113 aname := Wf_Engine.GetActivityAttrText(evtacts_rec.item_type,
7114 evtacts_rec.item_key,
7115 evtacts_rec.actid,
7116 wf_engine.eng_eventmessage);
7117 if (aname is not null) then
7118 Wf_Engine.SetItemAttrEvent(evtacts_rec.item_type,
7119 evtacts_rec.item_key,
7120 aname,
7121 event_message);
7122 end if;
7123
7124 -- Execute our lovely event activity (result is always null).
7125 Wf_Engine_Util.Complete_Activity(evtacts_rec.item_type,
7126 evtacts_rec.item_key, evtacts_rec.actid,
7127 wf_engine.eng_null);
7128 exception
7129 when others then
7130 -- If anything in this process raises an exception:
7131 -- 1. rollback any work in this process thread
7132 -- 2. set this activity to error status
7133 -- 3. execute the error process (if any)
7134 -- 4. clear the error to continue with next activity
7135 rollback to wf_savepoint;
7136 Wf_Core.Context('Wf_Engine', 'Event2', evtacts_rec.item_type,
7137 evtacts_rec.item_key, event_name);
7138 Wf_Item_Activity_Status.Set_Error(evtacts_rec.item_type,
7139 evtacts_rec.item_key,
7140 evtacts_rec.actid,
7141 wf_engine.eng_exception, FALSE);
7142 Wf_Engine_Util.Execute_Error_Process(evtacts_rec.item_type,
7143 evtacts_rec.item_key,
7144 evtacts_rec.actid,
7145 wf_engine.eng_exception);
7146 Wf_Core.Clear;
7147 end;
7148
7149 i := i + 1;
7150 end loop;
7151
7152 -- Check at least one matching event activity found
7153 if (i = 0) then
7154 Wf_Core.Token('EVENT2', event_name);
7155 Wf_Core.Raise('WFENG_EVENT_NOTFOUND');
7156 end if;
7157
7158 exception
7159 when resource_busy then
7160 --Rollback to ensure that we aren't locking anything here
7161 rollback to wf_savepoint_event2;
7162 raise;
7163 when others then
7164 Wf_Core.Context('Wf_Engine', 'Event2', businesskey, event_name);
7165 raise;
7166 end Event2;
7167
7168 --
7169 -- AddToItemAttrNumber
7170 -- Increments (or decrements) an numeric item attribute and returns the
7171 -- new value. If the item attribute does not exist, it returns null.
7172 -- IN
7173 -- p_itemtype - process item type
7174 -- p_itemkey - process item key
7175 -- p_aname - Item Attribute Name
7176 -- p_name - attribute name
7177 -- p_addend - Numeric value to be added to the item attribute. If p_addend
7178 -- is set to null, it will set the ItemAttrNumber to 0.
7179 --
7180 -- RETURNS
7181 -- Attribute value (NUMBER) or NULL if attribute does not exist.
7182 --
7183 function AddToItemAttrNumber(
7184 p_itemtype in varchar2,
7185 p_itemkey in varchar2,
7186 p_aname in varchar2,
7187 p_addend in number)
7188 return number is
7189 iStatus PLS_INTEGER;
7190 wiavIND NUMBER;
7191 l_avalue NUMBER;
7192 begin
7193 -- Check Arguments
7194 if ((p_itemtype is null) or
7195 (p_itemkey is null) or
7196 (p_aname is null)) then
7197 Wf_Core.Token('P_ITEMTYPE', nvl(p_itemtype, 'NULL'));
7198 Wf_Core.Token('P_ITEMKEY', nvl(p_itemkey, 'NULL'));
7199 Wf_Core.Token('P_ANAME', nvl(p_aname, 'NULL'));
7200 Wf_Core.Raise('WFSQL_ARGS');
7201 end if;
7202
7203 if (p_itemkey = wf_engine.eng_synch) then
7204 WF_CACHE.GetItemAttrValue(p_itemtype, p_itemKey, p_aname, iStatus,
7205 wiavIND);
7206
7207 if (iStatus <> WF_CACHE.task_SUCCESS) then
7208 return null;
7209
7210 else
7211 if (p_addend is NOT null) then
7212 WF_CACHE.ItemAttrValues(wiavIND).NUMBER_VALUE :=
7213 (WF_CACHE.ItemAttrValues(wiavIND).NUMBER_VALUE + p_addend);
7214 else
7215 WF_CACHE.ItemAttrValues(wiavIND).NUMBER_VALUE := 0;
7216 end if;
7217
7218 return WF_CACHE.ItemAttrValues(wiavIND).NUMBER_VALUE;
7219
7220 end if;
7221
7222 else
7223 if (p_addend is NOT null) then
7224 update WF_ITEM_ATTRIBUTE_VALUES wiav
7225 set wiav.NUMBER_VALUE = (wiav.NUMBER_VALUE+p_addend)
7226 where wiav.ITEM_TYPE = p_itemtype
7227 and wiav.ITEM_KEY = p_itemkey
7228 and wiav.NAME = p_aname
7229 returning wiav.NUMBER_VALUE into l_avalue;
7230 else
7231 update WF_ITEM_ATTRIBUTE_VALUES wiav
7232 set wiav.NUMBER_VALUE = 0
7233 where wiav.ITEM_TYPE = p_itemtype
7234 and wiav.ITEM_KEY = p_itemkey
7235 and wiav.NAME = p_aname
7236 returning wiav.NUMBER_VALUE into l_avalue;
7237 end if;
7238
7239 if (SQL%NOTFOUND) then
7240 return null;
7241 end if;
7242 return l_avalue;
7243
7244 end if;
7245
7246 exception
7247 when no_data_found then
7248 return NULL;
7249
7250 when others then
7251 Wf_Core.Context('Wf_Engine', 'AddToItemAttrNumber', p_itemtype, p_itemkey,
7252 p_aname, to_char(p_addend));
7253 raise;
7254 end AddToItemAttrNumber;
7255
7256 -- Bug 5903106
7257 -- HandleErrorConcurrent
7258 -- Concurrent Program API to handle any process activity that has
7259 -- encountered an error. This Concurrent Program API is a wrapper
7260 -- to HandleError and HandleErrorAll based on the parameter values
7261 -- supplied.
7262 -- IN
7263 -- p_errbuf
7264 -- p_retcode
7265 -- p_itemtype - Workflow Itemtype
7266 -- p_itemkey - Itemkey of the process
7267 -- p_activity - Workflow process activity label
7268 -- p_start_date - Errored On or After date
7269 -- p_end_date - Errored On or Before date
7270 -- p_max_retry - Maximum retries allowed on an activity
7271 -- p_docommit - Y (Yes) if you want a commit for every n iterations.
7272 -- n is defined as wf_engine.commit_frequency
7273 --
7274 procedure HandleErrorConcurrent(p_errbuf out nocopy varchar2,
7275 p_retcode out nocopy varchar2,
7276 p_itemtype in varchar2,
7277 p_itemkey in varchar2,
7278 p_process in varchar2,
7279 p_activity in varchar2,
7280 p_start_date in varchar2,
7281 p_end_date in varchar2,
7282 p_max_retry in varchar2,
7283 p_docommit in varchar2)
7284 is
7285
7286 l_start_date date;
7287 l_end_date date;
7288 l_max_retry number;
7289 l_docommit boolean;
7290 l_count number;
7291
7292 l_errname varchar2(30);
7293 l_errmsg varchar2(2000);
7294 l_stack varchar2(32000);
7295
7296 CURSOR c_err_acts (x_item_type varchar2,
7297 x_item_key varchar2,
7298 x_process varchar2,
7299 x_activity varchar2,
7300 x_start_date date,
7301 x_end_date date,
7302 x_max_retry number)
7303 IS
7304 SELECT wias.item_key,
7305 wpa.process_name,
7306 wpa.instance_label activity
7307 FROM wf_item_activity_statuses wias,
7308 wf_process_activities wpa
7309 WHERE wias.item_type = x_item_type
7310 AND (x_item_key IS NULL OR wias.item_key = x_item_key)
7311 AND (x_process IS NULL OR wpa.process_name = x_process)
7312 AND (x_activity IS NULL OR wpa.instance_label = x_activity)
7313 AND (x_start_date IS NULL OR wias.begin_date >= x_start_date)
7314 AND (x_end_date IS NULL OR wias.begin_date <= x_end_date)
7315 AND wias.process_activity = wpa.instance_id
7316 AND wias.activity_status = 'ERROR'
7317 AND x_max_retry >=
7318 (SELECT count(1)
7319 FROM wf_item_activity_statuses_h wiash
7320 WHERE wiash.item_type = wias.item_type
7321 AND wiash.item_key = wias.item_key
7322 AND wiash.process_activity = wias.process_activity
7323 AND wiash.action = 'RETRY');
7324
7325 begin
7326
7327 l_start_date := to_date(null);
7328 l_end_date := to_date(null);
7329
7330 -- Date value from CP is in fnd_flex_val_util.g_date_format_19 - 'RRRR/MM/DD HH24:MI:SS'
7331 -- This is same as wf_core.canonical_date_mask.
7332 if (p_start_date is not null) then
7333 l_start_date := to_date(p_start_date, wf_core.canonical_date_mask);
7334 end if;
7335
7336 if (p_end_date is not null) then
7337 l_end_date := to_date(p_end_date, wf_core.canonical_date_mask);
7338 end if;
7339
7340 if (nvl(p_docommit, 'Y') = 'Y') then
7341 l_docommit := TRUE;
7342 else
7343 l_docommit := FALSE;
7344 end if;
7345
7346 l_max_retry := to_number(nvl(p_max_retry, '5'));
7347
7348 -- Write parameters to log file
7349 Fnd_File.Put_Line(Fnd_File.Log, 'Wf_Engine.HandleErrorConcurrent');
7350 Fnd_File.Put_Line(Fnd_File.Log, 'p_itemtype - '||p_itemtype);
7351 Fnd_File.Put_Line(Fnd_File.Log, 'p_itemkey - '||p_itemkey);
7352 Fnd_File.Put_Line(Fnd_File.Log, 'p_process - '||p_process);
7353 Fnd_File.Put_Line(Fnd_File.Log, 'p_activity - '||p_activity);
7354 Fnd_File.Put_Line(Fnd_File.Log, 'l_start_date - '||to_char(l_start_date));
7355 Fnd_File.Put_Line(Fnd_File.Log, 'l_end_date - '||to_char(l_end_date));
7356 Fnd_File.Put_Line(Fnd_File.Log, 'l_max_retry - '||to_char(l_max_retry));
7357 Fnd_File.Put_Line(Fnd_File.Log, 'p_docommit - '||p_docommit);
7358
7359 -- Check Arguments
7360 if (p_itemtype is null) then
7361 Wf_Core.Token('ITEMTYPE', nvl(p_itemtype, 'NULL'));
7362 Wf_Core.Raise('WFSQL_ARGS');
7363 end if;
7364
7365 if (WF_CACHE.MetaRefreshed) then
7366 null;
7367 end if;
7368
7369 -- Retry all activities in ERROR for given parameters
7370 for l_rec in c_err_acts(p_itemtype,
7371 p_itemkey,
7372 p_process,
7373 p_activity,
7374 l_start_date,
7375 l_end_date,
7376 l_max_retry)
7377 loop
7378
7379 Wf_Engine.HandleError(itemtype => p_itemtype,
7380 itemkey => l_rec.item_key,
7381 activity => l_rec.process_name||':'||l_rec.activity,
7382 command => wf_engine.eng_retry,
7383 result => '');
7384
7385 if (l_docommit and c_err_acts%rowcount = wf_engine.commit_frequency) then
7386 commit;
7387 Fnd_Concurrent.Set_Preferred_RBS;
7388 end if;
7389 l_count := c_err_acts%rowcount;
7390 end loop;
7391
7392 Fnd_File.Put_Line(Fnd_File.Log, 'Items Processed - '||l_count);
7393
7394 if (l_docommit) then
7395 commit;
7396 Fnd_Concurrent.Set_Preferred_RBS;
7397 end if;
7398
7399 -- Successful completion
7400 p_errbuf := '';
7401 p_retcode := 0;
7402
7403 exception
7404 when others then
7405 Wf_Core.Get_error(l_errname, l_errmsg, l_stack);
7406
7407 -- Completed with Error
7408 p_errbuf := nvl(l_errmsg, sqlerrm);
7409 p_retcode := '2';
7410
7411 end HandleErrorConcurrent;
7412 -- bug 6161171
7413 procedure AbortProcess2(itemtype in varchar2,
7414 itemkey in varchar2,
7415 process in varchar2 default '',
7416 result in varchar2 default wf_engine.eng_force,
7417 verify_lock in binary_integer default 0,
7418 cascade in binary_integer default 0)
7419 is
7420 l_verify_lock boolean;
7421 l_cascade boolean;
7422 begin
7423 l_verify_lock := false;
7424 l_cascade := false;
7425
7426 if (verify_lock <> 0) then
7427 l_verify_lock := true;
7428 end if;
7429 if (cascade <> 0) then
7430 l_cascade := true;
7431 end if;
7432
7433 wf_engine.AbortProcess(itemtype, itemkey, process, result, l_verify_lock, l_cascade);
7434
7435 end AbortProcess2;
7436
7437 end Wf_Engine;