1 package body WF_ENGINE as
2 /* $Header: wfengb.pls 120.25.12010000.3 2008/10/28 15:19:48 alepe 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 -- For eligible row: Commit work to insure this activity
3618 -- thread doesn't interfere with others.
3619 -- For non-eligible row: Commit to release the lock.
3620 commit;
3621 Fnd_Concurrent.Set_Preferred_RBS;
3622
3623 end loop;
3624
3625 exception
3626 when others then
3627 Wf_Core.Context('Wf_Engine', 'ProcessTimeout', l_itemkey, l_itemtype,
3628 to_char(l_actid));
3629 raise;
3630 end ProcessTimeOut;
3631
3632 --
3633 -- ProcessStuckProcess (PUBLIC)
3634 -- Pick up one stuck process, mark error status, and execute error process.
3635 -- IN
3636 -- itemtype - Item type to process. If null process all item types.
3637 --
3638 procedure ProcessStuckProcess(itemtype in varchar2)
3639 is
3640 resource_busy exception;
3641 pragma exception_init(resource_busy, -00054);
3642
3643 l_itemtype varchar2(8);
3644 l_itemkey varchar2(240);
3645 l_actid pls_integer;
3646
3647 -- Select all activities from WIAS where:
3648 -- 1. Activity is a PROCESS activity
3649 -- 2. Activity has ACTIVE status
3650 -- 3. Activity has no direct child activities which have a status of:
3651 -- (ACTIVE, NOTIFIED, DEFERRED, SUSPENDED, ERROR)
3652 -- 4. Item has requested itemtype (first curs only)
3653 -- NOTE: Two separate cursors are used for itemtype and no-itemtype
3654 -- cases to get better execution plans.
3655
3656 cursor curs_itype is
3657 select /*+ ORDERED USE_NL (WIASP WI WPAP WAP)
3658 INDEX (WIASP WF_ITEM_ACTIVITY_STATUSES_N1) */
3659 WIASP.ROWID ROW_ID
3660 from WF_ITEM_ACTIVITY_STATUSES WIASP,
3661 WF_ITEMS WI,
3662 WF_PROCESS_ACTIVITIES WPAP,
3663 WF_ACTIVITIES WAP
3664 where WIASP.ITEM_TYPE = itemtype
3665 and WIASP.PROCESS_ACTIVITY = WPAP.INSTANCE_ID
3666 and WPAP.ACTIVITY_ITEM_TYPE = WAP.ITEM_TYPE
3667 and WPAP.ACTIVITY_NAME = WAP.NAME
3668 and WIASP.ITEM_TYPE = WI.ITEM_TYPE
3669 and WIASP.ITEM_KEY = WI.ITEM_KEY
3670 and WI.BEGIN_DATE >= WAP.BEGIN_DATE
3671 and WI.BEGIN_DATE < nvl(WAP.END_DATE, WI.BEGIN_DATE+1)
3672 and WAP.TYPE = wf_engine.eng_process
3673 and WIASP.ACTIVITY_STATUS = 'ACTIVE' --use literal to force index
3674 and not exists
3675 (select null
3676 from WF_ITEM_ACTIVITY_STATUSES WIASC,
3677 WF_PROCESS_ACTIVITIES WPAC
3678 where WAP.ITEM_TYPE = WPAC.PROCESS_ITEM_TYPE
3679 and WAP.NAME = WPAC.PROCESS_NAME
3680 and WAP.VERSION = WPAC.PROCESS_VERSION
3681 and WPAC.INSTANCE_ID = WIASC.PROCESS_ACTIVITY
3682 and WIASC.ITEM_TYPE = WI.ITEM_TYPE
3683 and WIASC.ITEM_KEY = WI.ITEM_KEY
3684 and WIASC.ACTIVITY_STATUS in ('ACTIVE','NOTIFIED','SUSPEND',
3685 'DEFERRED','ERROR'));
3686
3687 cursor curs_noitype is
3688 select /*+ ORDERED USE_NL (WIASP WI WPAP WAP)
3689 INDEX (WIASP WF_ITEM_ACTIVITY_STATUSES_N1) */
3690 WIASP.ROWID ROW_ID
3691 from WF_ITEM_ACTIVITY_STATUSES WIASP,
3692 WF_ITEMS WI,
3693 WF_PROCESS_ACTIVITIES WPAP,
3694 WF_ACTIVITIES WAP
3695 where WIASP.PROCESS_ACTIVITY = WPAP.INSTANCE_ID
3696 and WPAP.ACTIVITY_ITEM_TYPE = WAP.ITEM_TYPE
3697 and WPAP.ACTIVITY_NAME = WAP.NAME
3698 and WIASP.ITEM_TYPE = WI.ITEM_TYPE
3699 and WIASP.ITEM_KEY = WI.ITEM_KEY
3700 and WI.BEGIN_DATE >= WAP.BEGIN_DATE
3701 and WI.BEGIN_DATE < nvl(WAP.END_DATE, WI.BEGIN_DATE+1)
3702 and WAP.TYPE = 'PROCESS'
3703 and WIASP.ACTIVITY_STATUS = 'ACTIVE' --use literal to force index
3704 and not exists
3705 (select null
3706 from WF_ITEM_ACTIVITY_STATUSES WIASC,
3707 WF_PROCESS_ACTIVITIES WPAC
3708 where WAP.ITEM_TYPE = WPAC.PROCESS_ITEM_TYPE
3709 and WAP.NAME = WPAC.PROCESS_NAME
3710 and WAP.VERSION = WPAC.PROCESS_VERSION
3711 and WPAC.INSTANCE_ID = WIASC.PROCESS_ACTIVITY
3712 and WIASC.ITEM_TYPE = decode(wap.direction,
3713 wap.direction, WI.ITEM_TYPE,
3714 wi.item_type)
3715 and WIASC.ITEM_KEY = WI.ITEM_KEY
3716 and WIASC.ACTIVITY_STATUS in ('ACTIVE', 'NOTIFIED', 'SUSPEND',
3717 'DEFERRED', 'ERROR'));
3718
3719
3720 idarr RowidArrayTyp;
3721 arrsize pls_integer;
3722 eligible boolean;
3723
3724 begin
3725
3726 -- Fetch eligible rows into array
3727 arrsize := 0;
3728 if (itemtype is not null) then
3729 -- Fetch by itemtype
3730 for id in curs_itype loop
3731 arrsize := arrsize + 1;
3732 idarr(arrsize) := id.row_id;
3733 end loop;
3734 else
3735 -- Fetch all itemtypes
3736 for id in curs_noitype loop
3737 arrsize := arrsize + 1;
3738 idarr(arrsize) := id.row_id;
3739 end loop;
3740 end if;
3741
3742 -- Process all eligible rows found
3743 for i in 1 .. arrsize loop
3744 -- Lock row, and check if still eligible for execution
3745 -- To check for eligibility, check that:
3746 -- 1. Activity is a PROCESS activity
3747 -- 2. Activity has ACTIVE status
3748 -- 3. Activity has no direct child activities which have a status of:
3749 -- (ACTIVE, NOTIFIED, DEFERRED, SUSPENDED, ERROR)
3750 -- 4. Item has requested itemtype (first curs only)
3751 begin
3752 select
3753 WIASP.ITEM_TYPE, WIASP.ITEM_KEY, WIASP.PROCESS_ACTIVITY
3754 into l_itemtype, l_itemkey, l_actid
3755 from WF_ITEM_ACTIVITY_STATUSES WIASP,
3756 WF_PROCESS_ACTIVITIES WPAP,
3757 WF_ACTIVITIES WAP,
3758 WF_ITEMS WI
3759 where WIASP.PROCESS_ACTIVITY = WPAP.INSTANCE_ID
3760 and WPAP.ACTIVITY_ITEM_TYPE = WAP.ITEM_TYPE
3761 and WPAP.ACTIVITY_NAME = WAP.NAME
3762 and WIASP.ITEM_TYPE = WI.ITEM_TYPE
3763 and WIASP.ITEM_KEY = WI.ITEM_KEY
3764 and WI.BEGIN_DATE >= WAP.BEGIN_DATE
3765 and WI.BEGIN_DATE < nvl(WAP.END_DATE, WI.BEGIN_DATE+1)
3766 and WAP.TYPE = wf_engine.eng_process
3767 and WIASP.ACTIVITY_STATUS = 'ACTIVE' --use literal to force index
3768 and not exists
3769 (select null
3770 from WF_ITEM_ACTIVITY_STATUSES WIASC,
3771 WF_PROCESS_ACTIVITIES WPAC
3772 where WAP.ITEM_TYPE = WPAC.PROCESS_ITEM_TYPE
3773 and WAP.NAME = WPAC.PROCESS_NAME
3774 and WAP.VERSION = WPAC.PROCESS_VERSION
3775 and WPAC.INSTANCE_ID = WIASC.PROCESS_ACTIVITY
3776 and WIASC.ITEM_TYPE = WI.ITEM_TYPE
3777 and WIASC.ITEM_KEY = WI.ITEM_KEY
3778 and WIASC.ACTIVITY_STATUS in ('ACTIVE','NOTIFIED','SUSPEND',
3779 'DEFERRED','ERROR'))
3780 and WIASP.ROWID = idarr(i)
3781 for update of WIASP.ACTIVITY_STATUS, WI.ITEM_TYPE ,WI.ITEM_KEY NOWAIT;
3782
3783 eligible := TRUE;
3784 exception
3785 when resource_busy or no_data_found then
3786 -- If row already locked, or no longer eligible to run,
3787 -- continue on to next item in list.
3788 eligible := FALSE;
3789 end;
3790
3791 if (eligible) then
3792 -- Set the status to ERROR:#STUCK
3793 Wf_Item_Activity_Status.Create_Status(l_itemtype, l_itemkey, l_actid,
3794 wf_engine.eng_error, wf_engine.eng_stuck);
3795
3796 -- Execute the error process for stuck process
3797 begin
3798 begin
3799 begin
3800 savepoint wf_savepoint;
3801 Wf_Engine_Util.Execute_Error_Process(l_itemtype, l_itemkey, l_actid,
3802 wf_engine.eng_stuck);
3803 exception
3804 when others then
3805 -- If anything in this process raises an exception:
3806 -- 1. rollback any work in this process thread
3807 -- Raise an exception for the next exception handler to finish
3808 -- remaining steps.
3809 rollback to wf_savepoint;
3810 raise;
3811 end;
3812 exception
3813 when NO_SAVEPOINT then
3814 -- Catch any savepoint error in case of a commit happened.
3815 Wf_Core.Token('ACTIVITY', Wf_Engine.GetActivityLabel(l_actid));
3816 Wf_Core.Raise('WFENG_COMMIT_IN_ERRPROC');
3817 end;
3818 exception
3819 when OTHERS then
3820 -- Remaining steps for completing activity raises an exception:
3821 -- 2. set this activity to error status
3822 -- 3. execute the error process (if any)
3823 -- 4. clear the error to continue with next activity
3824 Wf_Core.Context('Wf_Engine', 'ProcessStuckProcess', l_itemkey,
3825 l_itemtype, to_char(l_actid));
3826 Wf_Item_Activity_Status.Set_Error(l_itemtype, l_itemkey, l_actid,
3827 wf_engine.eng_exception, FALSE);
3828 Wf_Engine_Util.Execute_Error_Process(l_itemtype, l_itemkey,
3829 l_actid, wf_engine.eng_exception);
3830 Wf_Core.Clear;
3831 end;
3832
3833 -- Commit work to insure this activity thread doesn't interfere
3834 -- with others.
3835 commit;
3836
3837 Fnd_Concurrent.Set_Preferred_RBS;
3838
3839 end if;
3840 end loop;
3841
3842 exception
3843 when others then
3844 Wf_Core.Context('Wf_Engine', 'ProcessStuckProcess', l_itemkey, l_itemtype,
3845 to_char(l_actid));
3846 raise;
3847 end ProcessStuckProcess;
3848
3849 --
3850 -- Background (PUBLIC)
3851 -- Process all current deferred and/or timeout activities within
3852 -- threshold limits.
3853 -- IN
3854 -- itemtype - Item type to process. If null process all item types.
3855 -- minthreshold - Minimum cost activity to process. No minimum if null.
3856 -- maxthreshold - Maximum cost activity to process. No maximum if null.
3857 -- process_deferred - Run deferred or waiting processes
3858 -- process_timeout - Handle timeout errors
3859 -- process_stuck - Handle stuck process errors
3860 --
3861 procedure Background (itemtype in varchar2,
3862 minthreshold in number,
3863 maxthreshold in number,
3864 process_deferred in boolean,
3865 process_timeout in boolean,
3866 process_stuck in boolean)
3867 is
3868 l_aq_tm_processes varchar2(512);
3869 begin
3870 if (WF_CACHE.MetaRefreshed) then
3871 null;
3872
3873 end if;
3874
3875 --Bug 3945469: Add check on db major version and cache value for aq_tm_processes
3876 if wf_core.g_oracle_major_version is null then
3877 wf_core.InitCache;
3878 end if;
3879
3880 --Check the value of aq_tm_processes if oracle version is not 10g
3881 if (wf_core.g_oracle_major_version < 10 ) then
3882 if (wf_core.g_aq_tm_processes ='0') then
3883 --If the value aq_tm_processes is 0 then raise error
3884 wf_core.raise('WFENG_AQ_TM_PROCESSES_ERROR');
3885 end if;
3886 end if;
3887
3888 --Bug 2307428
3889 --Enable the deferred and inbound queues.
3890 wf_queue.Enablebackgroundqueues;
3891
3892 -- Do not need to preserve context
3893 wf_engine.preserved_context := FALSE;
3894
3895 -- Process deferred activities
3896 if (process_deferred) then
3897 -- process the inbound queue first - it may place events on the deferred Q
3898 wf_queue.ProcessInboundQueue(itemtype);
3899 wf_engine.ProcessDeferred(itemtype, minthreshold, maxthreshold);
3900 end if;
3901
3902 -- Process timeout activities
3903 if (process_timeout) then
3904 wf_engine.ProcessTimeout(itemtype);
3905 end if;
3906
3907 -- Process stuck activities
3908 if (process_stuck) then
3909 wf_engine.ProcessStuckProcess(itemtype);
3910 end if;
3911
3912 exception
3913 when others then
3914 Wf_Core.Context('Wf_Engine', 'Background', itemtype,
3915 to_char(minthreshold), to_char(maxthreshold));
3916 raise;
3917 end Background;
3918
3919 --
3920 -- BackgroundConcurrent (PUBLIC)
3921 -- Run background process for deferred and/or timeout activities
3922 -- from Concurrent Manager.
3923 -- This is a cover of Background() with different argument types to
3924 -- be used by the Concurrent Manager.
3925 -- IN
3926 -- errbuf - CPM error message
3927 -- retcode - CPM return code (0 = success, 1 = warning, 2 = error)
3928 -- itemtype - Item type to process. If null process all item types.
3929 -- minthreshold - Minimum cost activity to process. No minimum if null.
3930 -- maxthreshold - Maximum cost activity to process. No maximum if null.
3931 -- process_deferred - Run deferred or waiting processes
3932 -- process_timeout - Handle timeout errors
3933 -- process_stuck - Handle stuck process errors
3934 --
3935 procedure BackgroundConcurrent (
3936 errbuf out NOCOPY varchar2,
3937 retcode out NOCOPY varchar2,
3938 itemtype in varchar2,
3939 minthreshold in varchar2,
3940 maxthreshold in varchar2,
3941 process_deferred in varchar2,
3942 process_timeout in varchar2,
3943 process_stuck in varchar2)
3944 is
3945 minthreshold_num number;
3946 maxthreshold_num number;
3947 process_deferred_bool boolean;
3948 process_timeout_bool boolean;
3949 process_stuck_bool boolean;
3950
3951 errname varchar2(30);
3952 errmsg varchar2(2000);
3953 errstack varchar2(4000);
3954 begin
3955 -- Convert arguments from varchar2 to real type.
3956 minthreshold_num := to_number(minthreshold);
3957 maxthreshold_num := to_number(maxthreshold);
3958
3959 if (upper(substr(process_deferred, 1, 1)) = 'Y') then
3960 process_deferred_bool := TRUE;
3961 else
3962 process_deferred_bool := FALSE;
3963 end if;
3964
3965 if (upper(substr(process_timeout, 1, 1)) = 'Y') then
3966 process_timeout_bool := TRUE;
3967 else
3968 process_timeout_bool := FALSE;
3969 end if;
3970
3971 if (upper(substr(process_stuck, 1, 1)) = 'Y') then
3972 process_stuck_bool := TRUE;
3973 else
3974 process_stuck_bool := FALSE;
3975 end if;
3976
3977 -- Call background engine with new args
3978 Wf_Engine.Background(
3979 itemtype,
3980 minthreshold_num,
3981 maxthreshold_num,
3982 process_deferred_bool,
3983 process_timeout_bool,
3984 process_stuck_bool);
3985
3986 -- Return 0 for successful completion.
3987 errbuf := '';
3988 retcode := '0';
3989
3990 exception
3991 when others then
3992 -- Retrieve error message into errbuf
3993 wf_core.get_error(errname, errmsg, errstack);
3994 if (errmsg is not null) then
3995 errbuf := errmsg;
3996 else
3997 errbuf := sqlerrm;
3998 end if;
3999
4000 -- Return 2 for error.
4001 retcode := '2';
4002 end BackgroundConcurrent;
4003
4004 --
4005 -- CreateProcess (PUBLIC)
4006 -- Create a new runtime process (for an application item).
4007 -- IN
4008 -- itemtype - A valid item type
4009 -- itemkey - A string generated from the application object's primary key.
4010 -- process - A valid root process for this item type
4011 -- (or null to use the item's selector function)
4012 --
4013 procedure CreateProcess(itemtype in varchar2,
4014 itemkey in varchar2,
4015 process in varchar2,
4016 user_key in varchar2,
4017 owner_role in varchar2)
4018 is
4019 root varchar2(30);
4020 version number;
4021 actdate date;
4022 typ varchar2(8);
4023 rootid pls_integer;
4024 status varchar2(8);
4025 l_event wf_event_t; -- Buffer for initing event itemattrs
4026
4027 -- All event item attrs to be initialized
4028 -- Initialization is now deferred until GetItemAttrEvent
4029 /* cursor evtcurs is
4030 select WIA.NAME
4031 from WF_ITEM_ATTRIBUTES WIA
4032 where WIA.ITEM_TYPE = CreateProcess.itemtype
4033 and WIA.TYPE = 'EVENT';*/
4034
4035 begin
4036 -- Argument validation
4037 if ((itemtype is null) or (itemkey is null)) then
4038 Wf_Core.Token('ITEMTYPE', itemtype);
4039 Wf_Core.Token('ITEMKEY', itemkey);
4040 Wf_Core.Token('PROCESS', process);
4041 Wf_Core.Raise('WFSQL_ARGS');
4042 end if;
4043
4044 --<rwunderl:4198524>
4045 if (WF_CACHE.MetaRefreshed) then
4046 null;
4047 end if;
4048
4049 -- Check for duplicate item
4050 if (itemkey = wf_engine.eng_synch) then
4051 if (Wf_Item.Item_Exist(itemtype, itemkey)) then
4052 -- SYNCHMODE: If duplicate is a synch process, check the status
4053 -- of the root process of the existing item.
4054 -- If the cached item is already complete, then it is ok to
4055 -- toss out the old item and create a new one.
4056 begin
4057 Wf_Item.Root_Process(itemtype, itemkey, root, version);
4058 rootid := Wf_Process_Activity.RootInstanceId(itemtype,
4059 itemkey, root);
4060 Wf_Item_Activity_Status.Status(itemtype, itemkey, rootid, status);
4061 exception
4062 when others then
4063 status := 'x'; -- Treat errors like incomplete process
4064 end;
4065 if (nvl(status, 'x') <> wf_engine.eng_completed) then
4066 Wf_Core.Token('ITEMTYPE', itemtype);
4067 Wf_Core.Token('ITEMKEY', itemkey);
4068 Wf_Core.Raise('WFENG_SYNCH_ITEM');
4069 end if;
4070 end if;
4071 else
4072 -- Not synchmode. Clear plsql cache first, just in case previous
4073 -- item was purged/rolled back, then check for duplicate.
4074 Wf_Item.ClearCache;
4075 if (Wf_Item.Item_Exist(itemtype, itemkey)) then
4076 Wf_Core.Token('TYPE', itemtype);
4077 Wf_Core.Token('KEY', itemkey);
4078 Wf_Core.Raise('WFENG_ITEM_UNIQUE');
4079 end if;
4080 end if;
4081
4082 if (process is null) then
4083 -- Call the selector function to get the process
4084 root := Wf_Engine_Util.Get_Root_Process(itemtype, itemkey);
4085 if (root is null) then
4086 Wf_Core.Token('TYPE', itemtype);
4087 Wf_Core.Token('KEY', itemkey);
4088 Wf_Core.Raise('WFENG_ITEM_ROOT_SELECTOR');
4089 end if;
4090 else
4091 root := process;
4092 end if;
4093
4094 -- Check that the root argument is a valid process.
4095 -- NOTE: The check that the process exists must be done BEFORE
4096 -- calling create_item to avoid foreign key problems during the insert.
4097 -- The check that the process is runnable can't be done until AFTER
4098 -- create_item so the date has been established.
4099 actdate := sysdate;
4100 typ := Wf_Activity.Type(itemtype, root, actdate);
4101 if ((typ is null) or (typ <> wf_engine.eng_process)) then
4102 Wf_Core.Token('TYPE', itemtype);
4103 Wf_Core.Token('NAME', root);
4104 Wf_Core.Raise('WFENG_PROCESS_NAME');
4105 end if;
4106
4107 -- Insert row in items table
4108 Wf_Item.Create_Item(itemtype, itemkey, root, actdate, createprocess.user_key,
4109 createprocess.owner_role);
4110
4111 -- Validate the root argument is runnable
4112 rootid := Wf_Process_Activity.RootInstanceId(itemtype, itemkey,
4113 root);
4114 if (rootid is null) then
4115 Wf_Core.Token('TYPE', itemtype);
4116 Wf_Core.Token('NAME', root);
4117 Wf_Core.Raise('WFENG_PROCESS_RUNNABLE');
4118 end if;
4119
4120 if (itemkey <> WF_ENGINE.eng_synch) then
4121 -- Create monitor access key attributes
4122 Wf_Engine.AddItemAttr(itemtype, itemkey, wf_engine.wfmon_mon_key,
4123 Wf_Core.Random);
4124 Wf_Engine.AddItemAttr(itemtype, itemkey, wf_engine.wfmon_acc_key,
4125 Wf_Core.Random);
4126 end if;
4127
4128 -- Create a schema attribute
4129 Wf_Engine.AddItemAttr(itemtype, itemkey, wf_engine.eng_schema,
4130 Wf_Engine.Current_Schema);
4131
4132 -- Initialize all EVENT-type item attributes
4133 -- Not done here, it is deferred until GetItemAttrEvent
4134 /* for evtattr in evtcurs loop
4135 Wf_Event_T.Initialize(l_event);
4136 Wf_Engine.SetItemAttrEvent(
4137 itemtype => itemtype,
4138 itemkey => itemkey,
4139 name => evtattr.name,
4140 event => l_event);
4141 end loop;*/
4142
4143 exception
4144 when others then
4145 -- Bug 4117740
4146 -- Call clearcache() when #SYNCH flow is in error
4147 if ((itemkey = WF_ENGINE.eng_synch) and
4148 (wf_core.error_name is null or wf_core.error_name <> 'WFENG_SYNCH_ITEM') and
4149 (not WF_ENGINE.debug)) then
4150 Wf_Item.ClearCache;
4151 end if;
4152
4153 Wf_Core.Context('Wf_Engine', 'CreateProcess', itemtype, itemkey, process);
4154 raise;
4155 end CreateProcess;
4156
4157 --
4158 -- StartProcess (PUBLIC)
4159 -- Begins execution of the process. The process will be identified by the
4160 -- itemtype and itemkey. The engine locates the starting activities
4161 -- of the root process and executes them.
4162 -- IN
4163 -- itemtype - A valid item type
4164 -- itemkey - A string generated from the application object's primary key.
4165 --
4166 procedure StartProcess(itemtype in varchar2,
4167 itemkey in varchar2)
4168 is
4169 begin
4170 if (WF_CACHE.MetaRefreshed) then
4171 null;
4172 end if;
4173 --Bug 2259039
4174 Wf_Engine_Util.Start_Process_Internal(
4175 itemtype=> itemtype,
4176 itemkey => itemkey,
4177 runmode => 'START');
4178 exception
4179 when others then
4180 Wf_Core.Context('Wf_Engine', 'StartProcess', itemtype, itemkey);
4181 raise;
4182 end StartProcess;
4183
4184 --
4185 -- LaunchProcess (PUBLIC)
4186 -- Launch a process both creates and starts it.
4187 -- This is a wrapper for friendlier UI
4188 -- IN
4189 -- itemtype - A valid item type
4190 -- itemkey - A string generated from the application object's primary key.
4191 -- process - A valid root process for this item type
4192 -- (or null to use the item's selector function)
4193 -- userkey - User key to be set
4194 -- owner - Role designated as owner of the item
4195 --
4196 procedure LaunchProcess(itemtype in varchar2,
4197 itemkey in varchar2,
4198 process in varchar2,
4199 userkey in varchar2,
4200 owner in varchar2) is
4201
4202 begin
4203 -- Check Arguments
4204 if ((itemtype is null) or
4205 (itemkey is null)) then
4206 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
4207 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
4208 Wf_Core.Raise('WFSQL_ARGS');
4209
4210 end if;
4211
4212 wf_engine.CreateProcess (itemtype,itemkey,process);
4213
4214 if userkey is not null then
4215 wf_engine.SetItemUserKey(itemtype,itemkey,userkey);
4216 end if;
4217
4218 if owner is not null then
4219 wf_engine.SetItemOwner(itemtype,itemkey,owner);
4220 end if;
4221
4222 wf_engine.StartProcess (itemtype,itemkey);
4223 exception
4224 when others then
4225 Wf_Core.Context('Wf_Engine', 'LaunchProcess', itemtype, itemkey,
4226 process, userkey, owner);
4227 raise;
4228 end LaunchProcess;
4229
4230 --
4231 -- SuspendProcess (PUBLIC)
4232 -- Suspends process execution, meaning no new transitions will occur.
4233 -- Outstanding notifications will be allowed to complete, but they will not
4234 -- cause activity transitions. If the process argument is null, the root
4235 -- process for the item is suspended, otherwise the named process is
4236 -- suspended.
4237 -- IN
4238 -- itemtype - A valid item type
4239 -- itemkey - A string generated from the application object's primary key.
4240 -- process - Process to suspend, specified in the form
4241 -- [<parent process_name>:]<process instance_label>
4242 -- If null suspend the root process.
4243 --
4244 procedure SuspendProcess(itemtype in varchar2,
4245 itemkey in varchar2,
4246 process in varchar2) is
4247
4248 root varchar2(30); -- The root process for this item key
4249 version pls_integer; -- Root process version
4250 rootid pls_integer; -- Instance id of root process
4251 actdate date; -- Active date of item
4252 proc varchar2(61); -- The process name that is going to be suspended
4253 procid pls_integer; -- The process id that is going to be suspended
4254 status varchar2(8); -- The status of the process
4255
4256 -- Cursor to select deferred activities to remove from background queue
4257 cursor defact is
4258 select PROCESS_ACTIVITY, BEGIN_DATE
4259 from WF_ITEM_ACTIVITY_STATUSES
4260 where ITEM_TYPE = itemtype
4261 and ITEM_KEY = itemkey
4262 and ACTIVITY_STATUS = wf_engine.eng_deferred;
4263
4264 begin
4265 -- Check Arguments
4266 if (itemtype is null) then
4267 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
4268 Wf_Core.Raise('WFSQL_ARGS');
4269
4270 -- Not allowed in synch mode
4271 elsif (itemkey = wf_engine.eng_synch) then
4272 wf_core.token('OPERATION', 'Wf_Engine.SuspendProcess');
4273 wf_core.raise('WFENG_SYNCH_DISABLED');
4274
4275 elsif (itemkey is null) then
4276 WF_ENGINE.SuspendAll(itemtype, process); --</rwunderl:1833759>
4277 return;
4278
4279 end if;
4280
4281 -- Get the root process for this key and also validate the item
4282 Wf_Item.Root_Process(itemtype, itemkey, root, version);
4283 if (root is null) then
4284 Wf_Core.Token('TYPE', itemtype);
4285 Wf_Core.Token('KEY', itemkey);
4286 Wf_Core.Raise('WFENG_ITEM');
4287 end if;
4288
4289 -- Get the process instance id.
4290 -- Search the process beginnning at the root process of the item for the
4291 -- activity matching process.
4292 actdate := Wf_Item.Active_Date(itemtype, itemkey);
4293 rootid := Wf_Process_Activity.RootInstanceId(itemtype, itemkey, root);
4294 if (rootid is null) then
4295 Wf_Core.Token('TYPE', itemtype);
4296 Wf_Core.Token('KEY', itemkey);
4297 Wf_Core.Token('NAME', root);
4298 Wf_Core.Raise('WFENG_ITEM_ROOT');
4299 end if;
4300
4301 if (process is null) then
4302 -- Suspend the root process
4303 proc := root;
4304 procid := rootid;
4305 else
4306 -- Suspend the given process
4307 proc := process;
4308 procid := Wf_Process_Activity.FindActivity(rootid, proc, actdate);
4309 if (procid is null) then
4310 Wf_Core.Token('TYPE', itemtype);
4311 Wf_Core.Token('KEY', itemkey);
4312 Wf_Core.Token('NAME', proc);
4313 Wf_Core.Token('VERSION', to_char(version));
4314 Wf_Core.Raise('WFENG_ITEM_PROCESS');
4315 end if;
4316
4317 -- Check that activity is a PROCESS-type.
4318 -- Only PROCESS activities may be suspended.
4319 if (Wf_Activity.Instance_Type(procid, actdate) <>
4320 wf_engine.eng_process) then
4321 Wf_Core.Token('NAME', proc);
4322 Wf_Core.Token('TYPE', itemtype);
4323 Wf_Core.Raise('WFENG_PROCESS_NAME');
4324 end if;
4325 end if;
4326
4327 -- Always clear the cache first
4328 -- AbortProcess, SuspendProcess and ResumeProcess should be rarely called
4329 -- from the background engine, so it should be safe to force reading from
4330 -- the database.
4331 Wf_Item_Activity_Status.ClearCache;
4332
4333 -- Check if the process is active
4334 Wf_Item_Activity_Status.Status(itemtype, itemkey, procid, status);
4335
4336 if (status is null) then
4337 -- This process has not been run yet. Create a pre-suspended
4338 -- status row so engine does not run process later
4339 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, procid,
4340 wf_engine.eng_suspended, wf_engine.eng_null, null, null,
4341 newStatus=>TRUE);
4342 elsif (status = wf_engine.eng_deferred) then
4343 -- Change status from 'deferred' to 'suspended'
4344 -- Doing this prevents the background processor from picking it up.
4345 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, procid,
4346 wf_engine.eng_suspended, null,
4347 null, null);
4348 elsif (status = wf_engine.eng_active) then
4349 -- Mark process as 'suspended', 'null' in WIAS table
4350 -- Doing this stops the engine from going through the rest of the flow
4351 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, procid,
4352 wf_engine.eng_suspended, null,
4353 null, null);
4354
4355 -- Suspend all the children processes
4356 Wf_Engine_Util.Suspend_Child_Processes(itemtype, itemkey, procid);
4357 else
4358 Wf_Core.Token('TYPE', itemtype);
4359 Wf_Core.Token('KEY', itemkey);
4360 Wf_Core.Token('NAME', proc);
4361 Wf_Core.Raise('WFENG_ITEM_PROCESS_ACTIVE');
4362 end if;
4363
4364 exception
4365 when others then
4366 Wf_Core.Context('Wf_Engine', 'SuspendProcess', itemtype, itemkey, process);
4367 raise;
4368 end SuspendProcess;
4369
4370 --
4371 -- AbortProcess (PUBLIC)
4372 -- Abort process execution. Outstanding notifications are canceled. The
4373 -- process is then considered complete, with a status specified by the
4374 -- result argument.
4375 -- IN
4376 -- itemtype - A valid item type
4377 -- itemkey - A string generated from the application object's primary key.
4378 -- process - Process to abort, specified in the form
4379 -- [<parent process_name>:]<process instance_label>
4380 -- If null abort the root process.
4381 -- result - Result to complete process with
4382 -- verify_lock - This boolean param determines whether we should lock
4383 -- the item before processing or not . This would control
4384 -- concurrent execution contention.
4385 -- cascade - This boolean param determines if the process should be
4386 -- aborted in cascade or not, ie kill all child processes
4387 -- to this process.
4388 --
4389 procedure AbortProcess(itemtype in varchar2,
4390 itemkey in varchar2,
4391 process in varchar2,
4392 result in varchar2,
4393 verify_lock in boolean,
4394 cascade in boolean) is
4395
4396 root varchar2(30); -- The root process for this item key
4397 version pls_integer; -- Root process version
4398 rootid pls_integer; -- Instance id of root process
4399 actdate date; -- Active date of item
4400 proc varchar2(61); -- Process name
4401 procid pls_integer; -- The process id that is going to be suspended
4402 status varchar2(8); -- The status of the process
4403 dummy pls_integer; -- Added for bug 1893606 - JWSMITH
4404
4405 --Bug 1166527
4406 l_parameterlist wf_parameter_list_t := wf_parameter_list_t();
4407
4408 l_lock boolean;
4409 cursor openNotifications is -- <7513983>
4410 SELECT wn.notification_id
4411 FROM wf_notifications wn, WF_ITEM_ACTIVITY_STATUSES ias
4412 WHERE ias.item_type = itemtype
4413 AND ias.item_key = itemkey
4414 AND ias.notification_id is not null
4415 AND ias.notification_id = wn.group_id
4416 AND wn.status = 'OPEN'
4417 UNION
4418 SELECT wn.notification_id
4419 FROM wf_notifications wn, WF_ITEM_ACTIVITY_STATUSES_H iash
4420 WHERE iash.item_type = itemtype
4421 AND iash.item_key = itemkey
4422 AND iash.notification_id is not null
4423 AND iash.notification_id = wn.notification_id
4424 AND wn.status = 'OPEN'; -- </7513983>
4425
4426 begin
4427 -- Check Arguments
4428 if (itemtype is null) then
4429 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
4430 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
4431 Wf_Core.Raise('WFSQL_ARGS');
4432
4433 elsif (itemkey = wf_engine.eng_synch) then -- Not allowed in synch mode
4434 wf_core.token('OPERATION', 'Wf_Engine.AbortProcess');
4435 wf_core.raise('WFENG_SYNCH_DISABLED');
4436 end if;
4437
4438 --Do the check for lock ONLY if there is an explicit
4439 --request for the same.
4440 if verify_lock then
4441 --Check if we can acquire lock for the
4442 --the item type/key here
4443 l_lock := wf_item.acquire_lock(itemtype,itemkey,true) ;
4444 end if;
4445
4446 -- Get the root process for this key and also validate the item
4447 Wf_Item.Root_Process(itemtype, itemkey, root, version);
4448 if (root is null) then
4449 Wf_Core.Context('Wf_Engine', 'AbortProcess', itemtype, itemkey, process);
4450 Wf_Core.Token('TYPE', itemtype);
4451 Wf_Core.Token('KEY', itemkey);
4452 Wf_Core.Raise('WFENG_ITEM');
4453 end if;
4454
4455 -- Get the process instance id.
4456 -- Search the process beginnning at the root process of the item for the
4457 -- activity matching process.
4458 actdate := Wf_Item.Active_Date(itemtype, itemkey);
4459 rootid := Wf_Process_Activity.RootInstanceId(itemtype, itemkey, root);
4460 if (rootid is null) then
4461 Wf_Core.Token('TYPE', itemtype);
4462 Wf_Core.Token('KEY', itemkey);
4463 Wf_Core.Token('NAME', root);
4464 Wf_Core.Raise('WFENG_ITEM_ROOT');
4465 end if;
4466
4467 if (process is null) then
4468 -- Abort the root process
4469 proc := root;
4470 procid := rootid;
4471 else
4472 -- Abort the given process
4473 proc := process;
4474 procid := Wf_Process_Activity.FindActivity(rootid, process, actdate);
4475 if (procid is null) then
4476 Wf_Core.Token('TYPE', itemtype);
4477 Wf_Core.Token('KEY', itemkey);
4478 Wf_Core.Token('NAME', process);
4479 Wf_Core.Token('VERSION', to_char(version));
4480 Wf_Core.Raise('WFENG_ITEM_PROCESS');
4481 end if;
4482
4483 -- Check that activity is a PROCESS-type.
4484 -- Only PROCESS activities may be aborted.
4485 if (Wf_Activity.Instance_Type(procid, actdate) <>
4486 wf_engine.eng_process) then
4487 Wf_Core.Token('NAME', proc);
4488 Wf_Core.Token('TYPE', itemtype);
4489 Wf_Core.Raise('WFENG_PROCESS_NAME');
4490 end if;
4491 end if;
4492
4493 -- Always clear the cache first
4494 Wf_Item_Activity_Status.ClearCache;
4495
4496 -- Check the process is not already complete
4497 Wf_Item_Activity_Status.Status(itemtype, itemkey, procid, status);
4498
4499 if (status is null) then
4500 if (WF_ITEM.SetEndDate(itemtype, itemkey) = 1) then
4501 Wf_Core.Token('TYPE', itemtype);
4502 Wf_core.Token('KEY', itemkey);
4503 Wf_core.Token('NAME', proc);
4504 Wf_Core.Raise('WFENG_ITEM_PROCESS_RUNNING');
4505 end if;
4506
4507 elsif (status = wf_engine.eng_completed) then
4508 Wf_Core.Token('TYPE', itemtype);
4509 Wf_Core.Token('KEY', itemkey);
4510 Wf_Core.Token('NAME', proc);
4511 Wf_Core.Raise('WFENG_ITEM_PROCESS_ACTIVE');
4512 else
4513 -- Mark process as 'COMPLETE', 'result' in WIAS table
4514 -- Doing this stops the engine from going through the rest of the flow
4515 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, procid,
4516 wf_engine.eng_completed, result,
4517 null, SYSDATE);
4518
4519 -- Kill child activities recursively
4520 Wf_Engine_Util.Process_Kill_Children(itemtype, itemkey, procid);
4521 --If cascade option is set to true abort all child
4522 --processes aswell
4523 if cascade then
4524 Wf_Engine_Util.Process_Kill_ChildProcess(itemtype, itemkey);
4525 end if;
4526 end if;
4527
4528 --Cancel any OPEN FYI notifications
4529 for nid in openNotifications
4530 loop
4531 wf_notification.cancel(nid.notification_id,' ');
4532 end loop;
4533
4534 --Include the information of the aborted process in the events
4535 --parameter list.
4536 wf_event.AddParameterToList('ITMETYPE', itemtype, l_parameterlist);
4537 wf_event.AddParameterToList('ITEMKEY', itemkey, l_parameterlist);
4538 wf_event.AddParameterToList('PROCESS', process, l_parameterlist);
4539 wf_event.AddParameterToList('RESULT', result, l_parameterlist);
4540
4541 -- Raise the event
4542 wf_event.Raise(p_event_name => 'oracle.apps.wf.engine.abort',
4543 p_event_key => itemkey,
4544 p_parameters => l_parameterlist);
4545
4546 exception
4547 when resource_busy then
4548 wf_core.token('TYPE',itemtype);
4549 wf_core.token('KEY',itemkey);
4550 wf_core.raise('WFENG_RESOURCE_BUSY');
4551
4552 when others then
4553 Wf_Core.Context('Wf_Engine', 'AbortProcess', itemtype, itemkey,
4554 process, result);
4555 raise;
4556 end AbortProcess;
4557
4558 --
4559 -- ResumeProcess (PUBLIC)
4560 -- Returns a process to normal execution status. Any transitions which
4561 -- were deferred by SuspendProcess() will now be processed.
4562 -- IN
4563 -- itemtype - A valid item type
4564 -- itemkey - A string generated from the application object's primary key.
4565 -- process - Process to resume, specified in the form
4566 -- [<parent process_name>:]<process instance_label>
4567 -- If null resume the root process.
4568 --
4569 procedure ResumeProcess(itemtype in varchar2,
4570 itemkey in varchar2,
4571 process in varchar2)
4572 is
4573 root varchar2(30); -- The root process for this item key
4574 version pls_integer; -- Root process version
4575 rootid pls_integer; -- Instance id of root process
4576 actdate date; -- Active date of item
4577 proc varchar2(61); -- The process name that is going to be suspended
4578 procid pls_integer; -- The process id that is going to be suspended
4579 status varchar2(8); -- The status of the process
4580
4581 -- Cursor to select deferred activities to restart.
4582 cursor defact is
4583 select
4584 PROCESS_ACTIVITY, BEGIN_DATE
4585 from WF_ITEM_ACTIVITY_STATUSES
4586 where ITEM_TYPE = itemtype
4587 and ITEM_KEY = itemkey
4588 and ACTIVITY_STATUS = wf_engine.eng_deferred;
4589
4590 actidarr InstanceArrayTyp; -- Deferred activities array
4591 i pls_integer := 0; -- Counter for the for loop
4592
4593 trig_savepoint exception;
4594 pragma exception_init(trig_savepoint, -04092);
4595 dist_savepoint exception;
4596 pragma exception_init(dist_savepoint, -02074);
4597 --Bug 2484201
4598 --Array to select the begin_date for the deferred activities
4599 type InstanceDateArray is table of date index by binary_integer;
4600 act_begin_date InstanceDateArray;
4601 begin
4602 -- Check Arguments
4603 if (itemtype is null) then
4604 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
4605 Wf_Core.Raise('WFSQL_ARGS');
4606
4607 elsif (itemkey = wf_engine.eng_synch) then -- Not allowed in synch mode
4608 wf_core.token('OPERATION', 'Wf_Engine.ResumeProcess');
4609 wf_core.raise('WFENG_SYNCH_DISABLED');
4610
4611 elsif (itemkey is NULL) then
4612 WF_ENGINE.ResumeAll(itemtype, process); --</rwunderl:1833759>
4613 return;
4614
4615 end if;
4616
4617 -- Get the root process for this key
4618 Wf_Item.Root_Process(itemtype, itemkey, root, version);
4619 if (root is null) then
4620 Wf_Core.Token('TYPE', itemtype);
4621 Wf_Core.Token('KEY', itemkey);
4622 Wf_Core.Raise('WFENG_ITEM');
4623 end if;
4624
4625 -- Get the process instance id.
4626 -- Search the process beginnning at the root process of the item for the
4627 -- activity matching process.
4628 actdate := Wf_Item.Active_Date(itemtype, itemkey);
4629 rootid := Wf_Process_Activity.RootInstanceId(itemtype, itemkey, root);
4630 if (rootid is null) then
4631 Wf_Core.Token('TYPE', itemtype);
4632 Wf_Core.Token('KEY', itemkey);
4633 Wf_Core.Token('NAME', root);
4634 Wf_Core.Raise('WFENG_ITEM_ROOT');
4635 end if;
4636
4637 if (process is null) then
4638 -- Resume the root process
4639 proc := root;
4640 procid := rootid;
4641 else
4642 -- Resume the given process
4643 proc := process;
4644 procid := Wf_Process_Activity.FindActivity(rootid, process, actdate);
4645 if (procid is null) then
4646 Wf_Core.Token('TYPE', itemtype);
4647 Wf_Core.Token('KEY', itemkey);
4648 Wf_Core.Token('NAME', process);
4649 Wf_Core.Token('VERSION', to_char(version));
4650 Wf_Core.Raise('WFENG_ITEM_PROCESS');
4651 end if;
4652
4653 -- Check that activity is a PROCESS-type.
4654 -- Only PROCESS activities may be resumed.
4655 if (Wf_Activity.Instance_Type(procid, actdate) <>
4656 wf_engine.eng_process) then
4657 Wf_Core.Token('NAME', proc);
4658 Wf_Core.Token('TYPE', itemtype);
4659 Wf_Core.Raise('WFENG_PROCESS_NAME');
4660 end if;
4661 end if;
4662
4663 -- Always clear the cache first
4664 Wf_Item_Activity_Status.ClearCache;
4665
4666 -- Check if the process is suspended
4667 Wf_Item_Activity_Status.Status(itemtype, itemkey, procid, status);
4668 if (status is null) then
4669 Wf_Core.Token('TYPE', itemtype);
4670 Wf_Core.Token('KEY', itemkey);
4671 Wf_Core.Token('NAME', proc);
4672 Wf_Core.Raise('WFENG_ITEM_PROCESS_RUNNING');
4673 elsif (status <> wf_engine.eng_suspended) then
4674 Wf_Core.Token('TYPE', itemtype);
4675 Wf_Core.Token('KEY', itemkey);
4676 Wf_Core.Token('NAME', proc);
4677 Wf_Core.Raise('WFENG_ITEM_PROCESS_SUSPENDED');
4678 else
4679 -- If we came here, that means the process is currently suspended.
4680 -- Mark process as eng_active 'active', 'null' in WIAS table
4681 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, procid,
4682 wf_engine.eng_active, null, null, null);
4683
4684 -- Mark any sub-processes as active again
4685 Wf_Engine_Util.Resume_Child_Processes(itemtype, itemkey, procid);
4686
4687 -- Restart any activities that were deferred because completion
4688 -- came in while process was suspended.
4689 --
4690 -- Note that cursor will select all deferred activities, even if they
4691 -- were deferred for other reasons than suspended process, but this is
4692 -- OK because:
4693 -- 1. Activities deferred because cost is higher than threshold will
4694 -- be immediately re-deferred by process_activity()
4695 -- 2. Deferred activities that are not in the sub-process just resumed
4696 -- will still have a suspended parent, and will also be immediately
4697 -- re-deferred by process_activity().
4698 -- This causes a little extra processing in rare cases, but is easier
4699 -- than figuring out the cause for each deferral here.
4700 for actid in defact loop
4701 actidarr(i) := actid.process_activity;
4702 act_begin_date(i) := actid.begin_date;
4703 i := i + 1;
4704 end loop;
4705 actidarr(i) := '';
4706
4707 i := 0;
4708 while (actidarr(i) is not null) loop
4709 --Bug 2484201
4710 --Set the begin date in call to Create_status as the begin_date
4711 --of the activity or to sysdate if begin_date is null
4712 --Also set the status to active only if begin_date <= sysdate
4713
4714 if (nvl(act_begin_date(i),sysdate) <= sysdate) then
4715 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, actidarr(i),
4716 wf_engine.eng_active, null, sysdate, null);
4717 begin
4718 savepoint wf_savepoint;
4719 Wf_Engine_Util.Process_Activity(itemtype, itemkey, actidarr(i),
4720 Wf_Engine.Threshold, TRUE);
4721 exception
4722 when trig_savepoint or dist_savepoint then
4723 -- Can't restart process here, re-defer for the
4724 -- background process to pick up.
4725 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey,
4726 actidarr(i),wf_engine.eng_deferred, null, sysdate, null);
4727 when others then
4728 -- If anything in this process raises an exception:
4729 -- 1. rollback any work in this process thread
4730 -- 2. set this activity to error status
4731 -- 3. execute the error process (if any)
4732 -- 4. clear the error to continue with next activity
4733 rollback to wf_savepoint;
4734 Wf_Core.Context('Wf_Engine', 'ResumeProcess', itemtype, itemkey,
4735 process);
4736 Wf_Item_Activity_Status.Set_Error(itemtype, itemkey, actidarr(i),
4737 wf_engine.eng_exception, FALSE);
4738 Wf_Engine_Util.Execute_Error_Process(itemtype, itemkey, actidarr(i),
4739 wf_engine.eng_exception);
4740
4741 Wf_Core.Clear;
4742 end;
4743 --else case status is same as right now that is deferred.
4744 end if;
4745
4746 i := i + 1;
4747 end loop;
4748 end if;
4749
4750 exception
4751 when others then
4752 Wf_Core.Context('Wf_Engine', 'ResumeProcess', itemtype, itemkey, process);
4753 raise;
4754 end ResumeProcess;
4755
4756
4757 --
4758 -- SuspendAll (PUBLIC)) --</rwunderl:1833759>
4759 -- Suspends all processes for a given itemType.
4760 -- IN
4761 -- itemtype - A valid itemType
4762 --
4763
4764 Procedure SuspendAll (p_itemType in varchar2,
4765 p_process in varchar2) is
4766
4767 cursor Open_Items(p_itemType in varchar2) is
4768 SELECT item_key
4769 FROM wf_items
4770 WHERE item_type = p_itemType
4771 AND end_date is NULL;
4772
4773 cursor All_Open_Items is
4774 SELECT item_type, item_key
4775 FROM wf_items
4776 WHERE end_date is NULL;
4777
4778 begin
4779
4780 if (p_itemType is NULL) then
4781 for c in All_Open_items loop
4782 begin
4783 WF_ENGINE.SuspendProcess(c.item_type, c.item_key, p_process);
4784
4785 exception
4786 when others then
4787 if ( wf_core.error_name = 'WFENG_ITEM_PROCESS_ACTIVE' ) then
4788 wf_core.clear;
4789
4790 else
4791 raise;
4792
4793 end if;
4794
4795 end;
4796
4797 end loop;
4798
4799 else
4800 for c in Open_Items(p_itemType) loop
4801 begin
4802 WF_ENGINE.SuspendProcess(p_itemType, c.item_key, p_process);
4803
4804 exception
4805 when others then
4806 if ( wf_core.error_name = 'WFENG_ITEM_PROCESS_ACTIVE' ) then
4807 wf_core.clear;
4808
4809 else
4810 raise;
4811
4812 end if;
4813
4814 end;
4815 end loop;
4816
4817 end if;
4818
4819 exception
4820 when others then
4821 Wf_Core.Context('Wf_Engine', 'SuspendAll', p_itemType, p_process);
4822 raise;
4823
4824 end SuspendAll;
4825
4826 --
4827 -- ResumeAll (PUBLIC) --</rwunderl:1833759>
4828 -- Resumes all processes for a given itemType.
4829 -- IN
4830 -- itemtype - A valid itemType
4831 --
4832 Procedure ResumeAll (p_itemType in varchar2,
4833 p_process in varchar2) is
4834
4835 cursor suspended_items(p_itemType in varchar2) is
4836 SELECT distinct wias.item_key
4837 FROM wf_item_activity_statuses wias
4838 WHERE wias.item_type = p_itemType
4839 AND wias.activity_status = wf_engine.eng_suspended;
4840
4841 cursor all_suspended_items is
4842 SELECT distinct wias.item_type, wias.item_key
4843 FROM wf_item_activity_statuses wias
4844 WHERE wias.activity_status = wf_engine.eng_suspended;
4845
4846 begin
4847
4848 if (p_itemType is NULL) then
4849 for c in all_suspended_items loop
4850 begin
4851 WF_ENGINE.ResumeProcess(c.item_type, c.item_key, p_process);
4852
4853 exception
4854 when others then
4855 null;
4856
4857 end;
4858
4859 end loop;
4860
4861 else
4862 for c in suspended_items(p_itemType) loop
4863 begin
4864 WF_ENGINE.ResumeProcess(p_itemType, c.item_key, p_process);
4865
4866 exception
4867 when others then
4868 null;
4869
4870 end;
4871
4872 end loop;
4873
4874 end if;
4875
4876 end ResumeAll;
4877
4878
4879
4880 Procedure CreateForkProcess (
4881 copy_itemtype in varchar2,
4882 copy_itemkey in varchar2,
4883 new_itemkey in varchar2,
4884 same_version in boolean,
4885 masterdetail in boolean) is
4886
4887 root_process varchar2(30);
4888 root_process_version number;
4889 dummy varchar2(30);
4890 dummyNum number;
4891 status varchar2(50);
4892 result varchar2(50);
4893 l_parent_itemType varchar2(8);
4894 l_parent_itemKey varchar2(240);
4895 l_parent_context varchar2(2000);
4896
4897 ValTooLarge EXCEPTION;
4898 pragma exception_init(ValTooLarge, -01401);
4899 begin
4900
4901 -- Argument validation
4902 if (copy_itemtype is null)
4903 or (copy_itemkey is null)
4904 or (new_itemkey is null) then
4905 Wf_Core.Token('COPY_ITEMTYPE', copy_itemtype);
4906 Wf_Core.Token('COPY_ITEMKEY', copy_itemkey);
4907 Wf_Core.Token('NEW_ITEMKEY', new_itemkey);
4908 Wf_Core.Raise('WFSQL_ARGS');
4909 end if;
4910
4911 -- Not allowed in synch mode
4912 if (new_itemkey = wf_engine.eng_synch)
4913 or (copy_itemkey = wf_engine.eng_synch) then
4914 wf_core.token('OPERATION', 'Wf_Engine.SuspendProcess');
4915 wf_core.raise('WFENG_SYNCH_DISABLED');
4916 end if;
4917
4918 -- Check status
4919 Wf_engine.ItemStatus(copy_itemtype, copy_itemkey, status, result);
4920 if (status = wf_engine.eng_error) then
4921 Wf_Core.Raise('WFENG_NOFORK_ONERROR');
4922 end if;
4923
4924 -- Check for duplicate item
4925 if (Wf_Item.Item_Exist(copy_itemtype, new_itemkey)) then
4926 Wf_Core.Token('TYPE', copy_itemtype);
4927 Wf_Core.Token('KEY', new_itemkey);
4928 Wf_Core.Raise('WFENG_ITEM_UNIQUE');
4929 end if;
4930
4931 --Place row-lock on this item and retrieve parent process info:
4932 select parent_item_type, parent_item_key, parent_context
4933 into l_parent_itemType, l_parent_itemKey, l_parent_context
4934 from wf_items
4935 where item_type = copy_itemtype
4936 and item_key = copy_itemkey
4937 for update of item_type;
4938
4939 --Create the process
4940 if same_version then
4941 insert into wf_items(
4942 ITEM_TYPE, ITEM_KEY,
4943 ROOT_ACTIVITY, ROOT_ACTIVITY_VERSION,
4944 OWNER_ROLE, USER_KEY,
4945 PARENT_ITEM_TYPE, PARENT_ITEM_KEY, PARENT_CONTEXT,
4946 BEGIN_DATE, END_DATE)
4947 select
4948 ITEM_TYPE, NEW_ITEMKEY,
4949 ROOT_ACTIVITY, ROOT_ACTIVITY_VERSION,
4950 OWNER_ROLE, USER_KEY,
4951 PARENT_ITEM_TYPE, PARENT_ITEM_KEY, PARENT_CONTEXT,
4952 BEGIN_DATE, null
4953 from wf_items
4954 where item_type = copy_itemtype
4955 and item_key = copy_itemkey;
4956 else
4957
4958 --lookup the root process
4959 wf_item.Root_Process(itemtype => copy_itemtype,
4960 itemkey => copy_itemkey,
4961 wflow => root_process,
4962 version =>root_process_version);
4963
4964 wf_engine.CreateProcess(copy_itemtype,new_itemkey,root_process);
4965
4966 --delete any defaulted attributes because we will copy the existing ones.
4967 delete from wf_item_attribute_values
4968 where item_type = copy_itemtype
4969 and item_key = new_itemkey;
4970
4971
4972 end if;
4973
4974 -- copy all item attributes including runtime attributes. Also, copy
4975 -- those item attributes that were added after the item was forked
4976 insert into wf_item_attribute_values
4977 (ITEM_TYPE, ITEM_KEY, NAME,
4978 TEXT_VALUE, NUMBER_VALUE, DATE_VALUE)
4979 select ITEM_TYPE, NEW_ITEMKEY, NAME,
4980 TEXT_VALUE, NUMBER_VALUE, DATE_VALUE
4981 from wf_item_attribute_values
4982 where item_type = copy_itemtype
4983 and item_key = copy_itemkey
4984 and name not like '#LBL_'
4985 and name not like '#CNT_'
4986 union all
4987 select ITEM_TYPE, new_itemkey, NAME,
4988 TEXT_DEFAULT, NUMBER_DEFAULT, DATE_DEFAULT
4989 from WF_ITEM_ATTRIBUTES
4990 where ITEM_TYPE = copy_itemtype
4991 and NAME not in
4992 (select name
4993 from wf_item_attribute_values
4994 where item_type = copy_itemtype
4995 and item_key = copy_itemkey
4996 and name not like '#LBL_'
4997 and name not like '#CNT_');
4998
4999
5000 -- reset the access_keys to make them unique
5001 Wf_Engine.SetItemAttrText(copy_itemtype, new_itemkey,
5002 wf_engine.wfmon_mon_key, Wf_Core.Random);
5003 Wf_Engine.SetItemAttrText(copy_itemtype, new_itemkey,
5004 wf_engine.wfmon_acc_key, Wf_Core.Random);
5005
5006
5007 -- reset the schema, just in case, if the #SCHEMA attribute does not exist
5008 -- it will be added. The CreateProcess api now adds the #SCHEMA.
5009 -- Only items created before WF_ENGINE was upgraded will encounter the
5010 -- exception to be handled, so this is for backward compatibility.
5011
5012 begin
5013 Wf_Engine.SetItemAttrText(copy_itemtype, new_itemkey,
5014 wf_engine.eng_schema, Wf_Engine.Current_Schema);
5015
5016 exception
5017 when others then
5018 if (wf_core.error_name = 'WFENG_ITEM_ATTR') then
5019 wf_core.clear;
5020 WF_ENGINE.AddItemAttr(copy_itemtype, new_itemkey,
5021 wf_engine.eng_schema,
5022 Wf_Engine.Current_Schema);
5023
5024 else
5025
5026 raise;
5027
5028 end if;
5029
5030 end;
5031
5032 -- Finally set an itemkey to record what this originated from
5033 begin
5034 Wf_Engine.AddItemAttr(copy_itemtype, new_itemkey, '#FORKED_FROM',
5035 copy_itemkey);
5036 exception
5037 when others then
5038 --
5039 -- If item attribute already exists then ignore the error
5040 --
5041 if ( wf_core.error_name = 'WFENG_ITEM_ATTR_UNIQUE' ) then
5042 wf_core.clear;
5043 Wf_Engine.SetItemAttrText(copy_itemtype, new_itemkey,
5044 '#FORKED_FROM', copy_itemkey);
5045 else
5046 raise;
5047 end if;
5048 end;
5049
5050 if (masterdetail) then
5051 --The caller has signaled that this is a master/detail process
5052 --We first will attempt to zero out any #WAITFORDETAIL attribute that may be
5053 --on this forked process (it is a master itself).
5054 dummyNum := WF_ENGINE.AddToItemAttrNumber(copy_itemType, new_itemKey,
5055 '#WAITFORDETAIL',
5056 to_number(NULL));
5057
5058 if ((l_parent_itemType is NOT null) and (l_parent_itemKey is NOT null)) then
5059 --There is a parent item to this forked item, so we will validate and
5060 --increment the parent's #WAITFORDETAIL counter.
5061 if (WF_ENGINE.AddToItemAttrNumber(l_parent_itemType, l_parent_itemKey,
5062 '#WAITFORDETAIL', 1) is NOT null) then
5063 --The parent has a #WAITFORDETAIL, so we can proceed on to check for
5064 --parent context.
5065 if (l_parent_context is NOT null) then
5066 --There is a parent context, so we will add the #LBL_ attribute to
5067 --the child flow, and will increment the corresponding #CNT_ attribute
5068 --in the parent flow.
5069 begin
5070 WF_ENGINE.AddItemAttr(itemtype=>copy_itemType, itemkey=>new_itemkey,
5071 aname=>'#LBL_'||l_parent_context,
5072 text_value=>l_parent_context);
5073
5074 --Since there was a parent context in the forked_from flow, we know
5075 --The parent has a counter for this label, so we can just increment.
5076 dummyNum := WF_ENGINE.AddToItemAttrNumber(l_parent_itemType,
5077 l_parent_itemKey,
5078 '#CNT_'||l_parent_context,
5079 1);
5080 exception
5081 when ValTooLarge then
5082 Wf_Core.Context('WF_ENGINE', 'CreateForkProcess', copy_itemtype,
5083 copy_itemkey, new_itemkey, l_parent_itemtype,
5084 l_parent_itemkey, l_parent_context, 'TRUE');
5085 WF_CORE.Token('LABEL', l_parent_context);
5086 WF_CORE.Token('LENGTH', 25);
5087 WF_CORE.Raise('WFENG_LABEL_TOO_LARGE');
5088 end;
5089 else
5090 -- PARENT_CONTEXT is null
5091 -- increase all known #CNT counter by 1
5092 update WF_ITEM_ATTRIBUTE_VALUES
5093 set NUMBER_VALUE = NUMBER_VALUE + 1
5094 where NAME like '#CNT_%'
5095 and NUMBER_VALUE is not null
5096 and ITEM_TYPE = l_parent_itemType
5097 and ITEM_KEY = l_parent_itemKey;
5098 end if; --PARENT_CONTEXT is not null
5099 end if; --#WAITFORDETAIL exists in the parent item.
5100 end if; --There is a parent item to this forked process.
5101 end if; --The caller signalled that this is a master/detail process.
5102 exception
5103 when others then
5104 Wf_Core.Context('Wf_Engine', 'CreateForkProcess');
5105 raise;
5106 end CreateForkProcess;
5107
5108
5109
5110 --
5111 -- StartForkProcess (PUBLIC)
5112 -- Start a process that has been forked. Depending on the way this was
5113 -- forked, this will execute startprocess if its to start with the latest
5114 -- version or it copies the forked process activty by activity.
5115 -- IN
5116 -- itemtype - Item type
5117 -- itemkey - item key to start
5118 --
5119 procedure StartForkProcess(
5120 itemtype in varchar2,
5121 itemkey in varchar2) as
5122
5123 copy_itemkey varchar2(30);
5124
5125 cursor all_activities is
5126 select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY,
5127 ACTIVITY_STATUS, ACTIVITY_RESULT_CODE,
5128 ASSIGNED_USER, NOTIFICATION_ID,
5129 BEGIN_DATE, END_DATE, EXECUTION_TIME,
5130 ERROR_NAME, ERROR_MESSAGE, ERROR_STACK,
5131 OUTBOUND_QUEUE_ID, DUE_DATE
5132 from wf_item_activity_statuses
5133 where item_type = itemtype
5134 and item_key = copy_itemkey;
5135
5136 cursor all_activities_hist is
5137 select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY,
5138 ACTIVITY_STATUS, ACTIVITY_RESULT_CODE,
5139 ASSIGNED_USER, NOTIFICATION_ID,
5140 BEGIN_DATE, END_DATE, EXECUTION_TIME,
5141 ERROR_NAME, ERROR_MESSAGE, ERROR_STACK,
5142 OUTBOUND_QUEUE_ID, DUE_DATE
5143 from wf_item_activity_statuses_h
5144 where item_type = itemtype
5145 and item_key = copy_itemkey;
5146
5147
5148 -- order by nid so that we re-execute in chronological order
5149 cursor ntf_open is
5150 select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY,
5151 ACTIVITY_STATUS, ACTIVITY_RESULT_CODE,
5152 ASSIGNED_USER, NOTIFICATION_ID,
5153 BEGIN_DATE, END_DATE, EXECUTION_TIME,
5154 ERROR_NAME, ERROR_MESSAGE, ERROR_STACK,
5155 OUTBOUND_QUEUE_ID, DUE_DATE
5156 from wf_item_activity_statuses
5157 where item_type = itemtype
5158 and item_key = copy_itemkey
5159 and notification_id is not null
5160 and activity_status = 'NOTIFIED'
5161 order by notification_id;
5162
5163
5164 nid number;
5165
5166 act_fname varchar2(240);
5167 act_ftype varchar2(30);
5168 delay number; -- dont use pls_integer or numeric overflow can occur.
5169 msg_id raw(16):=null;
5170
5171 copy_root_process varchar2(30);
5172 copy_process_version pls_integer;
5173 copy_active_date date;
5174
5175 new_root_process varchar2(30);
5176 new_process_version pls_integer;
5177 new_active_date date;
5178
5179
5180
5181 begin
5182
5183 -- Argument validation
5184 if (itemtype is null)
5185 or (itemkey is null) then
5186 Wf_Core.Token('COPY_ITEMTYPE', itemtype);
5187 Wf_Core.Token('COPY_ITEMKEY', copy_itemkey);
5188 Wf_Core.Token('NEW_ITEMKEY', itemkey);
5189 Wf_Core.Raise('WFSQL_ARGS');
5190 end if;
5191
5192
5193 -- get the forked_from attribute: if it doesnt exist then this cannot be
5194 -- a forked item
5195 begin
5196 copy_itemkey := Wf_Engine.GetItemAttrText(itemtype, itemkey,'#FORKED_FROM');
5197 exception when others then
5198 Wf_Core.Raise('WF_NOFORK');
5199 end;
5200
5201
5202 -- Not allowed in synch mode
5203 if (itemkey = wf_engine.eng_synch)
5204 or (copy_itemkey = wf_engine.eng_synch) then
5205 wf_core.token('OPERATION', 'Wf_Engine.SuspendProcess');
5206 wf_core.raise('WFENG_SYNCH_DISABLED');
5207 end if;
5208
5209
5210 -- Check item exists and store attributes while cached
5211 if not (Wf_Item.Item_Exist(itemtype, copy_itemkey)) then
5212 Wf_Core.Token('TYPE', itemtype);
5213 Wf_Core.Token('KEY', copy_itemkey);
5214 Wf_Core.Raise('WFENG_ITEM');
5215 end if;
5216
5217 wf_item.Root_Process(itemtype => itemtype,
5218 itemkey => copy_itemkey,
5219 wflow => copy_root_process,
5220 version =>copy_process_version);
5221
5222 copy_active_date:= wf_item.Active_Date(itemtype => itemtype,
5223 itemkey => copy_itemkey);
5224
5225
5226 --check status of item to copy is active or complete
5227 --
5228
5229
5230 -- Check item exists
5231 if not (Wf_Item.Item_Exist(itemtype, itemkey)) then
5232 Wf_Core.Token('TYPE', itemtype);
5233 Wf_Core.Token('KEY', itemkey);
5234 Wf_Core.Raise('WFENG_ITEM');
5235 end if;
5236
5237 wf_item.Root_Process(itemtype => itemtype,
5238 itemkey => itemkey,
5239 wflow => new_root_process,
5240 version =>new_process_version);
5241
5242 new_active_date:= wf_item.Active_Date(itemtype => itemtype,
5243 itemkey => itemkey);
5244
5245
5246
5247
5248 -- validate both copy and new items have same process and start dates.
5249 -- if not, this isnt a true fork: we are simply starting a process that
5250 -- uses the latest version so use startprocess
5251 if copy_root_process <> new_root_process
5252 or copy_process_version <> new_process_version
5253 or copy_active_date <> new_active_date then
5254 begin
5255 wf_engine.startprocess(itemtype,itemkey);
5256 exception when others then
5257 Wf_Core.raise('WF_CANNOT_FORK');
5258 end;
5259 return;
5260 end if;
5261
5262 -- copy all activities except open notifications
5263 -- leave these to last because routing rule may complete the thread
5264 for act in all_activities loop
5265
5266 msg_id :=null;
5267 nid := null;
5268
5269 if act.notification_id is not null then
5270
5271 --if complete then copy else ignore (we re-execute later)
5272 if act.activity_status = wf_engine.eng_completed then
5273 wf_engine_util.notification_copy (act.notification_id,
5274 act.item_key, itemkey, nid);
5275 end if;
5276
5277 elsif act.activity_status = wf_engine.eng_deferred then
5278
5279 --process defered activity
5280 act_fname:= Wf_Activity.activity_function
5281 (act.item_type,act.item_key,act.process_activity);
5282 act_ftype:= Wf_Activity.activity_function_type
5283 (act.item_type,act.item_key,act.process_activity);
5284
5285 if act_ftype = 'PL/SQL' then
5286
5287 if act.begin_date <= sysdate then
5288 delay :=0;
5289 else
5290 delay := round((act.begin_date - sysdate)*24*60*60 + 0.5);
5291 end if;
5292 wf_queue.enqueue_event
5293 (queuename=>wf_queue.DeferredQueue,
5294 itemtype=> act.item_type,
5295 itemkey=>itemkey,
5296 actid=>act.process_activity,
5297 delay=>delay,
5298 message_handle=>msg_id);
5299
5300 --even if internal, keep message handle for easy access.
5301 --msg_id :=null;
5302 elsif act_ftype = 'EXTERNAL' then
5303 -- this is a callout so write to OUTBOUND queue
5304 -- do not set the correlation here for compatibility reason
5305 wf_queue.enqueue_event
5306 (queuename=>wf_queue.OutboundQueue,
5307 itemtype=> act.item_type,
5308 itemkey=>itemkey,
5309 actid=>act.process_activity,
5310 funcname=>act_fname,
5311 paramlist=>wf_queue.get_param_list(act.item_type,itemkey,
5312 act.process_activity),
5313 message_handle=>msg_id);
5314 else
5315 -- this is a callout so write to OUTBOUND queue for other type
5316 wf_queue.enqueue_event
5317 (queuename=>wf_queue.OutboundQueue,
5318 itemtype=> act.item_type,
5319 itemkey=>itemkey,
5320 actid=>act.process_activity,
5321 correlation=>act_ftype,
5322 funcname=>act_fname,
5323 paramlist=>wf_queue.get_param_list(act.item_type,itemkey,
5324 act.process_activity),
5325 message_handle=>msg_id);
5326 end if;
5327
5328 --else
5329 --must be a function activity
5330 --in this case we dont have to set any values, but just copy
5331
5332 end if;
5333
5334 -- now insert the status
5335 insert into wf_item_activity_statuses
5336 (ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY,
5337 ACTIVITY_STATUS, ACTIVITY_RESULT_CODE,
5338 ASSIGNED_USER, NOTIFICATION_ID,
5339 BEGIN_DATE, END_DATE, EXECUTION_TIME,
5340 ERROR_NAME, ERROR_MESSAGE, ERROR_STACK,
5341 OUTBOUND_QUEUE_ID, DUE_DATE)
5342 values(act.item_type, itemkey, act.process_activity,
5343 act.activity_status, act.activity_result_code,
5344 act.assigned_user, nid,
5345 act.begin_date, act.end_date, act.execution_time,
5346 act.error_name, act.error_message, act.error_stack,
5347 msg_id, act.due_date);
5348
5349
5350 end loop; --end defered status
5351
5352 -- repeat for all history
5353 for hist in all_activities_hist loop
5354
5355 nid := null;
5356 if hist.notification_id is not null then
5357 wf_engine_util.notification_copy (hist.notification_id,
5358 hist.item_key, itemkey, nid);
5359 end if;
5360
5361 -- now insert the status
5362 insert into wf_item_activity_statuses_h
5363 (ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY,
5364 ACTIVITY_STATUS, ACTIVITY_RESULT_CODE,
5365 ASSIGNED_USER, NOTIFICATION_ID,
5366 BEGIN_DATE, END_DATE, EXECUTION_TIME,
5367 ERROR_NAME, ERROR_MESSAGE, ERROR_STACK,
5368 OUTBOUND_QUEUE_ID, DUE_DATE)
5369 values(hist.item_type, itemkey, hist.process_activity,
5370 hist.activity_status, hist.activity_result_code,
5371 hist.assigned_user, nid,
5372 hist.begin_date, hist.end_date, hist.execution_time,
5373 hist.error_name, hist.error_message, hist.error_stack,
5374 null, hist.due_date);
5375
5376 end loop;
5377
5378 -- update any active functions to notified state
5379 begin
5380 update wf_item_activity_statuses ias
5381 set activity_status = wf_engine.eng_notified
5382 where item_type = itemtype
5383 and item_key = itemkey
5384 and activity_status = 'ACTIVE'
5385 and activity_status = wf_engine.eng_active
5386 and exists (select 'its a function, not subprocess'
5387 from wf_process_activities pa,
5388 wf_activities ac
5389 where pa.activity_name = ac.name
5390 and pa.activity_item_type = ac.item_type
5391 and pa.activity_item_type = ias.item_type
5392 and pa.instance_id = ias.process_activity
5393 and type='FUNCTION');
5394 end;
5395
5396
5397 -- update item attributes on all copied notifications
5398 wf_engine_util.notification_refresh(itemtype,itemkey);
5399
5400
5401 -- as last step, launch all notifications still open
5402 -- keep this as last step because routing rules may allow
5403 -- continuation of thread.
5404
5405 for ntf in ntf_open loop
5406 Wf_Engine_Util.Process_Activity(itemtype, itemkey,
5407 ntf.process_activity,wf_engine.threshold);
5408 end loop;
5409
5410
5411 exception
5412 when others then
5413 Wf_Core.Context('Wf_Engine', 'StartForkProcess');
5414 raise;
5415 end StartForkProcess;
5416
5417
5418 --
5419 --
5420 -- BeginActivity (PUBLIC)
5421 -- Determines if the specified activity may currently be performed on the
5422 -- work item. This is a test that the performer may proactively determine
5423 -- that their intent to perform an activity on an item is, in fact, allowed.
5424 -- IN
5425 -- itemtype - A valid item type
5426 -- itemkey - A string generated from the application object's primary key.
5427 -- activity - Completed activity, specified in the form
5428 -- [<parent process_name>:]<process instance_label>
5429 --
5430 procedure BeginActivity(itemtype in varchar2,
5431 itemkey in varchar2,
5432 activity in varchar2)
5433 is
5434 root varchar2(30); -- The name of the root process for this key
5435 version pls_integer; -- Root process version
5436 actdate date; -- Active date of item
5437 actid pls_integer; -- activity instance id
5438 begin
5439 -- Not allowed in synch mode
5440 if (itemkey = wf_engine.eng_synch) then
5441 wf_core.token('OPERATION', 'Wf_Engine.BeginActivity');
5442 wf_core.raise('WFENG_SYNCH_DISABLED');
5443 end if;
5444
5445 -- Argument validation
5446 if ((itemtype is null) or (itemkey is null) or (activity is null)) then
5447 Wf_Core.Token('ITEMTYPE', itemtype);
5448 Wf_Core.Token('ITEMKEY', itemkey);
5449 Wf_Core.Token('ACTIVITY', activity);
5450 Wf_Core.Raise('WFSQL_ARGS');
5451 end if;
5452
5453 -- Validate the activity and get the actid.
5454 -- One of these conditions must hold:
5455 -- 1. The item does not exist
5456 -- --> The process is being implicitly started for the first time
5457 -- by completing a START activity.
5458 -- 2. The item and root process exist, and activity is NOTIFIED
5459 -- --> Activity just completed in a running process.
5460
5461 -- Check if item exists and get root process
5462 Wf_Item.Root_Process(itemtype, itemkey, root, version);
5463 if (root is null) then
5464 -- Item does not exist. Must be case (1).
5465
5466 -- Use selector to get the root process
5467 -- Note must do this here, instead of relying on CreateProcess
5468 -- to call the selector, because CreateProcess can't take the
5469 -- start activity as an argument to implicitly choose a root
5470 -- process when no selector function is defined.
5471 root := Wf_Engine_Util.Get_Root_Process(itemtype, itemkey, activity);
5472 if (root is null) then
5473 Wf_Core.Token('TYPE', itemtype);
5474 Wf_Core.Token('KEY', itemkey);
5475 Wf_Core.Raise('WFENG_ITEM_ROOT_SELECTOR');
5476 end if;
5477
5478 else
5479 -- Item exists. Must be case (2).
5480 -- Check that the activity is currently notified.
5481 actid := Wf_Process_Activity.ActiveInstanceId(itemtype, itemkey,
5482 activity, wf_engine.eng_notified);
5483
5484 -- Any other status, or no status at all, is an error.
5485 if (actid is null) then
5486 Wf_Core.Token('TYPE', itemtype);
5487 Wf_Core.Token('KEY', itemkey);
5488 Wf_Core.Token('NAME', activity);
5489 Wf_Core.Raise('WFENG_NOT_NOTIFIED');
5490 end if;
5491
5492 end if;
5493 exception
5494 when others then
5495 Wf_Core.Context('Wf_Engine', 'BeginActivity', itemtype, itemkey, activity);
5496 raise;
5497 end BeginActivity;
5498
5499 --
5500 -- CompleteActivity (PUBLIC)
5501 -- Notifies the workflow engine that an activity has been completed for a
5502 -- particular process(item). This procedure can have one or more of the
5503 -- following effects:
5504 -- o Creates a new item. If the completed activity is the start of a process,
5505 -- then a new item can be created by this call. If the completed activity
5506 -- is not the start of a process, it would be an invalid activity error.
5507 -- o Complete an activity with an optional result. This signals the
5508 -- workflow engine that an asynchronous activity has been completed.
5509 -- An optional activity completion result can also be passed.
5510 -- IN
5511 -- itemtype - A valid item type
5512 -- itemkey - A string generated from the application object's primary key.
5513 -- activity - Completed activity, specified in the form
5514 -- [<parent process_name>:]<process instance_label>
5515 -- <result> - An optional result.
5516 --
5517 procedure CompleteActivity(itemtype in varchar2,
5518 itemkey in varchar2,
5519 activity in varchar2,
5520 result in varchar2)
5521 is
5522 root varchar2(30); -- The name of the root process for this key
5523 version pls_integer; -- Root process version
5524 rootid pls_integer; -- Root process actid
5525 actid pls_integer; -- activity instance id
5526 notid pls_integer; -- Notification group id
5527 user varchar2(320); -- Notification assigned user
5528
5529 trig_savepoint exception;
5530 pragma exception_init(trig_savepoint, -04092);
5531 dist_savepoint exception;
5532 pragma exception_init(dist_savepoint, -02074);
5533
5534 --Bug 2607770
5535 l_lock boolean;
5536 begin
5537 -- Argument validation
5538 if ((itemtype is null) or (itemkey is null) or (activity is null)) then
5539 Wf_Core.Token('ITEMTYPE', itemtype);
5540 Wf_Core.Token('ITEMKEY', itemkey);
5541 Wf_Core.Token('ACTIVITY', activity);
5542 Wf_Core.Raise('WFSQL_ARGS');
5543 end if;
5544
5545 if (WF_CACHE.MetaRefreshed) then
5546 null;
5547
5548 end if;
5549
5550 -- Validate the activity and get the actid.
5551 -- One of these conditions must hold:
5552 -- 1. The item does not exist
5553 -- --> The process is being implicitly started for the first time
5554 -- by completing a START activity.
5555 -- 2. The item and root process exist, and activity is NOTIFIED
5556 -- --> Activity just completed in a running process.
5557
5558 -- Check if item exists and get root process
5559 Wf_Item.Root_Process(itemtype, itemkey, root, version);
5560 if (root is null) then
5561 -- Item does not exist. Must be case (1).
5562
5563 -- Use selector to get the root process
5564 -- Note must do this here, instead of relying on CreateProcess
5565 -- to call the selector, because CreateProcess can't take the
5566 -- start activity as an argument to implicitly choose a root
5567 -- process when no selector function is defined.
5568 root := Wf_Engine_Util.Get_Root_Process(itemtype, itemkey, activity);
5569 if (root is null) then
5570 Wf_Core.Token('TYPE', itemtype);
5571 Wf_Core.Token('KEY', itemkey);
5572 Wf_Core.Raise('WFENG_ITEM_ROOT_SELECTOR');
5573 end if;
5574
5575 -- Create new process
5576 Wf_Engine.CreateProcess(itemtype, itemkey, root);
5577
5578 --Bug 2259039
5579 -- Start the process for this activity.
5580 -- The activity to be completed will be left in NOTIFIED status
5581 -- as a side-effect of this call.
5582 Wf_Engine_Util.Start_Process_Internal(
5583 itemtype => itemtype,
5584 itemkey => itemkey,
5585 runmode => 'ACTIVITY');
5586
5587 -- Get root process for the item
5588 Wf_Item.Root_Process(itemtype, itemkey, root, version);
5589
5590 -- Look for the starting activity in the root process.
5591 actid := Wf_Process_Activity.StartInstanceId(itemtype, root, version,
5592 activity);
5593
5594 -- Create a status row for new activity
5595 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, actid,
5596 wf_engine.eng_active, wf_engine.eng_null, sysdate, null, newStatus=>TRUE);
5597 else
5598
5599 --Bug 2607770
5600 --Its only in the else condition that you need to get
5601 --a lock over the existing item to make sure noone else is
5602 --processing it.
5603
5604 -- Item exists. Must be case (2).
5605 -- Check that the activity is currently notified.
5606 actid := Wf_Process_Activity.ActiveInstanceId(itemtype, itemkey,
5607 activity, wf_engine.eng_notified);
5608
5609 -- Any other status, or no status at all, is an error.
5610 if (actid is null) then
5611 Wf_Core.Token('TYPE', itemtype);
5612 Wf_Core.Token('KEY', itemkey);
5613 Wf_Core.Token('NAME', activity);
5614 Wf_Core.Raise('WFENG_NOT_NOTIFIED');
5615 end if;
5616
5617 --If acquire lock returns true we will continue
5618 --If it returns false we raise exception to the user
5619 --Any other exception we let the caller decide what to do
5620 if (itemkey <> wf_engine.eng_synch) then
5621 --If its an async process and you cannot acquire a lock
5622 --raise the exception to the user
5623 l_lock := wf_item.acquire_lock(itemtype,itemkey,true);
5624 end if;
5625
5626 -- Get notification id
5627 Wf_Item_Activity_Status.Notification_Status(itemtype, itemkey, actid,
5628 notid, user);
5629
5630 -- Close any open notifications associated with this activity.
5631 -- Note: if notifications are not closed here, they will be cancelled
5632 -- anyway by complete_activity. They are only closed here so that the
5633 -- status is closed and not cancelled when going through the external
5634 -- CompleteActivity interface.
5635 -- Bug2811737 CTILLEY - added update to end_date
5636 if (notid is not null) then
5637 update WF_NOTIFICATIONS WN set
5638 status = 'CLOSED',
5639 end_date = sysdate
5640 where WN.GROUP_ID = CompleteActivity.notid
5641 and WN.STATUS = 'OPEN';
5642 end if;
5643 end if;
5644
5645 -- Finally, complete our lovely new activity.
5646 if (itemkey = wf_engine.eng_synch) then
5647 -- SYNCHMODE: No error trapping in synchmode.
5648 Wf_Engine_Util.Complete_Activity(itemtype, itemkey, actid, result);
5649 else
5650 begin
5651 savepoint wf_savepoint;
5652 Wf_Engine_Util.Complete_Activity(itemtype, itemkey, actid, result);
5653 exception
5654 when trig_savepoint or dist_savepoint then
5655 -- You must be in a restricted environment,
5656 -- no fancy error processing for you!
5657 -- NOTE: Must go ahead and complete the activity instead of
5658 -- deferring directly, because the activity must be marked as
5659 -- complete. Any following activities started by completing
5660 -- this activity will be caught and deferred in another
5661 -- savepoint trap in process_activity.
5662 Wf_Engine_Util.Complete_Activity(itemtype, itemkey, actid, result);
5663 when others then
5664 -- If anything in this process raises an exception:
5665 -- 1. rollback any work in this process thread
5666 -- 2. set this activity to error status
5667 -- 3. execute the error process (if any)
5668 -- 4. clear the error to continue with next activity
5669 rollback to wf_savepoint;
5670 Wf_Core.Context('Wf_Engine', 'CompleteActivity', itemtype, itemkey,
5671 activity, result);
5672 Wf_Item_Activity_Status.Set_Error(itemtype, itemkey, actid,
5673 wf_engine.eng_exception, FALSE);
5674 Wf_Engine_Util.Execute_Error_Process(itemtype, itemkey, actid,
5675 wf_engine.eng_exception);
5676 Wf_Core.Clear;
5677 end;
5678 end if;
5679
5680 exception
5681 when resource_busy then
5682 wf_core.token('TYPE',itemtype);
5683 wf_core.token('KEY',itemkey);
5684 wf_core.raise('WFENG_RESOURCE_BUSY');
5685 when others then
5686 Wf_Core.Context('Wf_Engine', 'CompleteActivity', itemtype, itemkey,
5687 activity, result);
5688 raise;
5689 end CompleteActivity;
5690
5691 --
5692 -- CompleteActivityInternalName (PUBLIC)
5693 -- Identical to CompleteActivity, except that the internal name of
5694 -- completed activity is passed instead of the activity instance label.
5695 -- NOTES:
5696 -- 1. There must be exactly ONE instance of this activity with NOTIFIED
5697 -- status.
5698 -- 2. Using this api to start a new process is not supported.
5699 -- 3. Synchronous processes are not supported in this api.
5700 -- 4. This should only be used if for some reason the instance label is
5701 -- not known. CompleteActivity should be used if the instance
5702 -- label is known.
5703 -- IN
5704 -- itemtype - A valid item type
5705 -- itemkey - A string generated from the application object's primary key.
5706 -- activity - Internal name of completed activity, in the format
5707 -- [<parent process_name>:]<process activity_name>
5708 -- <result> - An optional result.
5709 --
5710 procedure CompleteActivityInternalName(
5711 itemtype in varchar2,
5712 itemkey in varchar2,
5713 activity in varchar2,
5714 result in varchar2)
5715 is
5716 colon pls_integer;
5717 process varchar2(30);
5718 actname varchar2(30);
5719 label varchar2(30);
5720 begin
5721 -- Not allowed in synch mode
5722 if (itemkey = wf_engine.eng_synch) then
5723 wf_core.token('OPERATION', 'Wf_Engine.CompleteActivityInternalName');
5724 wf_core.raise('WFENG_SYNCH_DISABLED');
5725 end if;
5726
5727 -- Argument validation
5728 if ((itemtype is null) or (itemkey is null) or (activity is null)) then
5729 Wf_Core.Token('ITEMTYPE', itemtype);
5730 Wf_Core.Token('ITEMKEY', itemkey);
5731 Wf_Core.Token('ACTIVITY', activity);
5732 Wf_Core.Raise('WFSQL_ARGS');
5733 end if;
5734
5735 -- Parse activity arg into <process_name> and <activity_name> components.
5736 colon := instr(activity, ':');
5737 if (colon <> 0) then
5738 -- Activity arg is <process name>:<activity name>
5739 process := substr(activity, 1, colon-1);
5740 actname := substr(activity, colon+1);
5741 else
5742 -- Activity arg is just activity name
5743 process := '';
5744 actname := activity;
5745 end if;
5746
5747 -- Look up activity instance label
5748 begin
5749 select WPA.PROCESS_NAME, WPA.INSTANCE_LABEL
5750 into process, label
5751 from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_PROCESS_ACTIVITIES WPA
5752 where WIAS.ITEM_TYPE = itemtype
5753 and WIAS.ITEM_KEY = itemkey
5754 and WIAS.ACTIVITY_STATUS = wf_engine.eng_notified
5755 and WIAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID
5756 and WPA.ACTIVITY_NAME = actname
5757 and WPA.PROCESS_NAME = nvl(process, WPA.PROCESS_NAME);
5758 exception
5759 when no_data_found then
5760 Wf_Core.Token('TYPE', itemtype);
5761 Wf_Core.Token('KEY', itemkey);
5762 Wf_Core.Token('NAME', activity);
5763 Wf_Core.Raise('WFENG_NOT_NOTIFIED');
5764 end;
5765
5766 -- Complete activity with the correct arguments
5767 Wf_Engine.CompleteActivity(itemtype, itemkey, process||':'||label,
5768 result);
5769
5770 exception
5771 when others then
5772 Wf_Core.Context('Wf_Engine', 'CompleteActivityInternalName',
5773 itemtype, itemkey, activity, result);
5774 raise;
5775 end CompleteActivityInternalName;
5776
5777 --
5778 -- AssignActivity (PUBLIC)
5779 -- Assigns or re-assigns the user who will perform an activity. It may be
5780 -- called before the activity has been enabled(transitioned to). If a user
5781 -- is assigned to an activity that already has an outstanding notification,
5782 -- that notification will be forwarded to the new user.
5783 -- IN
5784 -- itemtype - A valid item type
5785 -- itemkey - A string generated from the application object's primary key.
5786 -- activity - Activity to assign, specified in the form
5787 -- [<parent process_name>:]<process instance_label>
5788 -- performer - User who will perform this activity.
5789 -- reassignType - DELEGATE, TRANSFER or null
5790 -- ntfComments - Comments while reassigning
5791 -- 16-DEC-03 shanjgik bug 2722369 new parameters added
5792 procedure AssignActivity(itemtype in varchar2,
5793 itemkey in varchar2,
5794 activity in varchar2,
5795 performer in varchar2,
5796 reassignType in varchar2,
5797 ntfComments in varchar2) is
5798 root varchar2(30);
5799 version pls_integer;
5800 rootid pls_integer;
5801 actid pls_integer;
5802 status varchar2(8);
5803 notid pls_integer;
5804 user varchar2(320);
5805 acttype varchar2(8);
5806 actdate date;
5807 msg varchar2(30);
5808 msgtype varchar2(8);
5809 expand_role varchar2(1);
5810 begin
5811 -- Not allowed in synch mode
5812 if (itemkey = wf_engine.eng_synch) then
5813 wf_core.token('OPERATION', 'Wf_Engine.AssignActivity');
5814 wf_core.raise('WFENG_SYNCH_DISABLED');
5815 end if;
5816
5817 -- Argument validation
5818 if ((itemtype is null) or (itemkey is null) or (activity is null) or
5819 (performer is null)) then
5820 Wf_Core.Token('ITEMTYPE', itemtype);
5821 Wf_Core.Token('ITEMKEY', itemkey);
5822 Wf_Core.Token('ACTIVITY', activity);
5823 Wf_Core.Token('PERFORMER', performer);
5824 Wf_Core.Raise('WFSQL_ARGS');
5825 end if;
5826
5827 -- Get the root process for this key, and check that the item
5828 -- has been created.
5829 Wf_Item.Root_Process(itemtype, itemkey, root, version);
5830 if (root is null) then
5831 Wf_Core.Token('TYPE', itemtype);
5832 Wf_Core.Token('KEY', itemkey);
5833 Wf_Core.Raise('WFENG_ITEM');
5834 end if;
5835
5836 -- Get the root process actid.
5837 rootid := Wf_Process_Activity.RootInstanceId(itemtype, itemkey, root);
5838 if (rootid is null) then
5839 Wf_Core.Token('TYPE', itemtype);
5840 Wf_Core.Token('KEY', itemkey);
5841 Wf_Core.Token('NAME', root);
5842 Wf_Core.Raise('WFENG_ITEM_ROOT');
5843 end if;
5844
5845 -- Get the actid and check that this is a valid activity in the
5846 -- root process
5847 actdate := Wf_Item.Active_Date(itemtype, itemkey);
5848 actid := Wf_Process_Activity.FindActivity(rootid, activity, actdate);
5849 if (actid is null) then
5850 Wf_Core.Token('TYPE', itemtype);
5851 Wf_Core.Token('KEY', itemkey);
5852 Wf_Core.Token('NAME', activity);
5853 Wf_Core.Raise('WFENG_ITEM_ACTIVITY');
5854 end if;
5855
5856 -- Check if this activity is a notification type of activity
5857 acttype := Wf_Activity.Type(itemtype, activity, actdate);
5858 if (acttype <> wf_engine.eng_notification) then
5859 Wf_Core.Token('NAME', activity);
5860 Wf_Core.Raise('WFENG_NOTIFICATION_NAME');
5861 end if;
5862
5863 -- Check if the activity is active
5864 Wf_Item_Activity_Status.Status(itemtype, itemkey, actid, status);
5865
5866 if (status is null) then
5867 -- Insert one row with the performer
5868 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, actid,
5869 wf_engine.eng_waiting, '', null, null, newStatus=>TRUE);
5870 Wf_Item_Activity_Status.Update_Notification(itemtype, itemkey, actid,
5871 '', performer);
5872 elsif (status = wf_engine.eng_waiting) then
5873 Wf_Item_Activity_Status.Update_Notification(itemtype, itemkey, actid,
5874 '', performer);
5875 elsif (status in (wf_engine.eng_notified, wf_engine.eng_error)) then
5876 -- Check this is not a voting activity.
5877 -- Voting activities cannot be re-assigned.
5878 Wf_Activity.Notification_Info(itemtype, itemkey, actid, msg, msgtype,
5879 expand_role);
5880 if (expand_role = 'Y') then
5881 Wf_Core.Token('NAME', activity);
5882 Wf_Core.Raise('WFENG_VOTE_REASSIGN');
5883 end if;
5884
5885 -- Get notification id
5886 Wf_Item_Activity_Status.Notification_Status(itemtype, itemkey, actid,
5887 notid, user);
5888 -- Update the assigned user column in WIAS
5889 Wf_Item_Activity_Status.Update_Notification(itemtype, itemkey, actid,
5890 notid, performer);
5891
5892 if (notid is not null) then
5893 -- 16-DEC-03 shanjgik bug fix 2722369 check for reassignType added
5894 if (reassignType = Wf_Engine.eng_delegate) then
5895 -- delegate the notification
5896 Wf_Notification.Forward(notid, performer, ntfComments);
5897 else -- case reassignType is TRANSFER or null
5898 -- Call Wf_Notification.Transfer(notid, performer) to transfer
5899 -- ownership of the notification to the new performer.
5900 Wf_Notification.Transfer(notid, performer, ntfComments);
5901 end if;
5902 end if;
5903 else
5904 -- Activity must be complete (all other statuses are not valid
5905 -- for a notification).
5906 Wf_Core.Token('ACTIVITY', activity);
5907 Wf_Core.Token('TYPE', itemtype);
5908 Wf_Core.Token('KEY', itemkey);
5909 Wf_Core.Raise('WFENG_ITEM_ACTIVITY_COMPLETE');
5910 end if;
5911
5912 EXCEPTION
5913 when OTHERS then
5914 Wf_Core.Context('Wf_Engine', 'AssignActivity', itemtype, itemkey,
5915 activity, performer);
5916 raise;
5917 end AssignActivity;
5918
5919 --
5920 -- HandleErrorInternal (PRIVATE)
5921 -- Reset the process thread to the given activity and begin execution
5922 -- again from that point. If command is:
5923 -- SKIP - mark the activity complete with given result and continue
5924 -- RETRY - re-execute the activity before continuing
5925 -- IN
5926 -- itemtype - A valid item type.
5927 -- itemkey - The item key of the process.
5928 -- root - Root acitivity label
5929 -- rootid - Root acitivty id
5930 -- activity - Activity label
5931 -- actid - Activity id to reset
5932 -- actdate - Active Date
5933 -- command - SKIP or RETRY.
5934 -- <result> - Activity result for the 'SKIP' command.
5935 --
5936 procedure HandleErrorInternal(itemtype in varchar2,
5937 itemkey in varchar2,
5938 root in varchar2,
5939 rootid in number,
5940 activity in varchar2,
5941 actid in number,
5942 actdate in date,
5943 command in varchar2,
5944 result in varchar2 default '')
5945 is
5946 version pls_integer;
5947 funcname varchar2(240);
5948 resultout varchar2(240);
5949
5950 trig_savepoint exception;
5951 pragma exception_init(trig_savepoint, -04092);
5952 dist_savepoint exception;
5953 pragma exception_init(dist_savepoint, -02074);
5954
5955 --Bug 1166527
5956 event_name VARCHAR2(240);
5957 l_parameterlist wf_parameter_list_t := wf_parameter_list_t();
5958 begin
5959 -- Not allowed in synch mode
5960 -- Validate this before calling this function
5961
5962 -- No Argument validation
5963 -- Validate this before calling this function
5964
5965 -- Make sure item is valid
5966 -- Validate this before calling this function
5967
5968 -- Reset the process starting from the goal activity.
5969 -- This reset behaves similar to loop reset, cancelling activities,
5970 -- moving rows to history, etc. It then resets the activity status
5971 -- to active, AND resets or creates status rows for any parent process
5972 -- to active if necessary.
5973 if (not Wf_Engine_Util.Reset_Tree(itemtype, itemkey, rootid,
5974 actid, actdate)) then
5975 Wf_Core.Token('TYPE', itemtype);
5976 Wf_Core.Token('KEY', itemkey);
5977 Wf_Core.Token('NAME', activity);
5978 Wf_Core.Raise('WFENG_ITEM_ACTIVITY');
5979 end if;
5980
5981 if (command = wf_engine.eng_skip) then
5982 -- *** SKIP ***
5983 -- Mark activity complete with given result
5984 begin
5985 savepoint wf_savepoint;
5986
5987 -- execute the activity function with SKIP command (bug 2425229)
5988 funcname := Wf_Activity.Activity_Function(itemtype, itemkey, actid);
5989
5990 if (funcname is not null) then -- <6636968>
5991
5992 Wf_Engine_Util.Function_Call(funcname, itemtype, itemkey, actid, wf_engine.eng_skip,
5993 resultout);
5994
5995 -- Check if skip is allowed on this activity
5996 if (resultout = wf_engine.eng_noskip) then
5997 Wf_Core.Token('LABEL', Wf_Engine.GetActivityLabel(actid));
5998 Wf_Core.Raise('WFENG_NOSKIP');
5999 end if;
6000 end if;-- </6636968>
6001
6002 Wf_Engine_Util.Complete_Activity(itemtype, itemkey, actid, result, FALSE);
6003
6004 exception
6005 when trig_savepoint or dist_savepoint then
6006 -- You must be in a restricted environment,
6007 -- no fancy error processing for you! Try running directly.
6008 Wf_Engine_Util.Complete_Activity(itemtype, itemkey, actid,
6009 result, FALSE);
6010 when others then
6011 if (Wf_Core.Error_Name = 'WFENG_NOSKIP') then
6012 -- No processing. Raise to the caller that the activity cannot be skipped.
6013 raise;
6014 else
6015 -- If anything in this process raises an exception:
6016 -- 1. rollback any work in this process thread
6017 -- 2. set this activity to error status
6018 -- 3. execute the error process (if any)
6019 -- 4. clear the error to continue with next activity
6020 rollback to wf_savepoint;
6021 Wf_Core.Context('Wf_Engine', 'HandleErrorInternal', itemtype, itemkey,
6022 activity, command, result);
6023 Wf_Item_Activity_Status.Set_Error(itemtype, itemkey, actid,
6024 wf_engine.eng_exception, FALSE);
6025 Wf_Engine_Util.Execute_Error_Process(itemtype, itemkey, actid,
6026 wf_engine.eng_exception);
6027 Wf_Core.Clear;
6028 end if;
6029 end;
6030 --We will raise the skip event here .
6031 event_name := 'oracle.apps.wf.engine.skip';
6032 else
6033 -- *** RETRY ***
6034 if (actid = rootid) then
6035 -- Restart root process from beginnning
6036 Wf_Engine.StartProcess(itemtype, itemkey);
6037 else
6038 -- Start at given activity
6039 begin
6040 savepoint wf_savepoint;
6041 Wf_Engine_Util.Process_Activity(itemtype, itemkey, actid,
6042 Wf_Engine.Threshold, TRUE);
6043 exception
6044 when trig_savepoint or dist_savepoint then
6045 -- You must be in a restricted environment,
6046 -- no fancy error processing for you!
6047 -- Immediately defer activity to background engine.
6048 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey,
6049 actid, wf_engine.eng_deferred, wf_engine.eng_null,
6050 SYSDATE, null);
6051 when others then
6052 -- If anything in this process raises an exception:
6053 -- 1. rollback any work in this process thread
6054 -- 2. set this activity to error status
6055 -- 3. execute the error process (if any)
6056 -- 4. clear the error to continue with next activity
6057 rollback to wf_savepoint;
6058 Wf_Core.Context('Wf_Engine', 'HandleErrorInternal',itemtype,itemkey,
6059 activity, command, result);
6060 Wf_Item_Activity_Status.Set_Error(itemtype, itemkey, actid,
6061 wf_engine.eng_exception, FALSE);
6062 Wf_Engine_Util.Execute_Error_Process(itemtype, itemkey, actid,
6063 wf_engine.eng_exception);
6064 Wf_Core.Clear;
6065 end;
6066 end if;
6067 event_name := 'oracle.apps.wf.engine.retry';
6068 end if;
6069
6070 -- Store the info for Audit (Bug 5903106 - moved from HandleError to here)
6071 Wf_Item_Activity_Status.Audit(itemtype, itemkey, actid, upper(command), null);
6072
6073 --Pass the signature of the handle error API in the
6074 --parameter list
6075 wf_event.AddParameterToList('ITMETYPE', itemtype, l_parameterlist);
6076 wf_event.AddParameterToList('ITEMKEY', itemkey, l_parameterlist);
6077 wf_event.AddParameterToList('ACTIVITY', activity, l_parameterlist);
6078 if (result is NOT NULL) then
6079 wf_event.AddParameterToList('RESULT', result, l_parameterlist);
6080 end if;
6081
6082 -- Raise the event
6083 wf_event.Raise(p_event_name => event_name,
6084 p_event_key => itemkey,
6085 p_parameters => l_parameterlist);
6086
6087 exception
6088 when others then
6089 Wf_Core.Context('Wf_Engine', 'HandleErrorInternal', itemtype, itemkey,
6090 activity, command, result);
6091 raise;
6092 end HandleErrorInternal;
6093
6094 --
6095 -- HandleError (PUBLIC)
6096 -- Reset the process thread to the given activity and begin execution
6097 -- again from that point. If command is:
6098 -- SKIP - mark the activity complete with given result and continue
6099 -- RETRY - re-execute the activity before continuing
6100 -- IN
6101 -- itemtype - A valid item type.
6102 -- itemkey - The item key of the process.
6103 -- activity - Activity to reset, specified in the form
6104 -- [<parent process_name>:]<process instance_label>
6105 -- command - SKIP or RETRY.
6106 -- <result> - Activity result for the 'SKIP' command.
6107 --
6108 procedure HandleError(itemtype in varchar2,
6109 itemkey in varchar2,
6110 activity in varchar2,
6111 command in varchar2,
6112 result in varchar2)
6113 is
6114 root varchar2(30);
6115 version pls_integer;
6116 rootid pls_integer;
6117 actid pls_integer;
6118 actdate date;
6119
6120 trig_savepoint exception;
6121 pragma exception_init(trig_savepoint, -04092);
6122 dist_savepoint exception;
6123 pragma exception_init(dist_savepoint, -02074);
6124 begin
6125 -- Not allowed in synch mode
6126 if (itemkey = wf_engine.eng_synch) then
6127 wf_core.token('OPERATION', 'Wf_Engine.HandleError');
6128 wf_core.raise('WFENG_SYNCH_DISABLED');
6129 end if;
6130
6131 -- Argument validation
6132 if ((itemtype is null) or (itemkey is null) or (activity is null) or
6133 (upper(command) not in (wf_engine.eng_skip, wf_engine.eng_retry))) then
6134 Wf_Core.Token('ITEMTYPE', itemtype);
6135 Wf_Core.Token('ITEMKEY', itemkey);
6136 Wf_Core.Token('ACTIVITY', activity);
6137 Wf_Core.Token('COMMAND', command);
6138 Wf_Core.Raise('WFSQL_ARGS');
6139 end if;
6140
6141 -- If we are in a different Fwk session, need to clear Workflow PLSQL state
6142 if (not Wfa_Sec.CheckSession) then
6143 Wf_Global.Init;
6144 end if;
6145
6146 -- Make sure item is valid
6147 Wf_Item.Root_Process(itemtype, itemkey, root, version);
6148 if (root is null) then
6149 Wf_Core.Token('TYPE', itemtype);
6150 Wf_Core.Token('KEY', itemkey);
6151 Wf_Core.Raise('WFENG_ITEM');
6152 end if;
6153 rootid := Wf_Process_Activity.RootInstanceId(itemtype, itemkey, root);
6154 if (rootid is null) then
6155 Wf_Core.Token('TYPE', itemtype);
6156 Wf_Core.Token('KEY', itemkey);
6157 Wf_Core.Token('NAME', root);
6158 Wf_Core.Raise('WFENG_ITEM_ROOT');
6159 end if;
6160
6161 -- Look for the activity instance for this item
6162 actdate := Wf_Item.Active_Date(itemtype, itemkey);
6163 actid := Wf_Process_Activity.FindActivity(rootid, activity, actdate);
6164
6165 if (actid is null) then
6166 Wf_Core.Token('TYPE', itemtype);
6167 Wf_Core.Token('PROCESS', root);
6168 Wf_Core.Token('NAME', activity);
6169 Wf_Core.Raise('WFENG_ACTIVITY_EXIST');
6170 end if;
6171
6172 if (WF_CACHE.MetaRefreshed) then
6173 null;
6174
6175 end if;
6176
6177 -- Call the internal function to do the real job
6178 HandleErrorInternal(itemtype, itemkey, root, rootid, activity, actid,
6179 actdate, upper(command), result);
6180 exception
6181 when others then
6182 Wf_Core.Context('Wf_Engine', 'HandleError', itemtype, itemkey, activity,
6183 command, result);
6184 raise;
6185 end HandleError;
6186
6187 --
6188 -- HandleErrorAll (PUBLIC)
6189 -- Reset the process thread to the given item type and/or item key and/or
6190 -- activity.
6191 -- IN
6192 -- itemtype - A valid item type.
6193 -- itemkey - The item key of the process.
6194 -- activity - Activity to reset, specified in the form
6195 -- [<parent process_name>:]<process instance_label>
6196 -- command - SKIP or RETRY.
6197 -- <result> - Activity result for the "SKIP" command.
6198 -- docommit - True if you want a commit for every n iterations.
6199 -- n is defined as wf_engine.commit_frequency
6200 --
6201 procedure HandleErrorAll(itemtype in varchar2,
6202 itemkey in varchar2,
6203 activity in varchar2,
6204 command in varchar2,
6205 result in varchar2,
6206 docommit in boolean)
6207 is
6208 root varchar2(30);
6209 version number;
6210 rootid number;
6211 actdate date;
6212
6213 c_item_key varchar2(240);
6214 c_activity varchar2(30);
6215 c_actid number;
6216
6217 cursor actc(x_itemtype varchar2, x_itemkey varchar2, x_activity varchar2) is
6218 select ias.ITEM_KEY,
6219 pa.INSTANCE_LABEL activity,
6220 pa.INSTANCE_ID actid
6221 from WF_ITEM_ACTIVITY_STATUSES ias,
6222 WF_PROCESS_ACTIVITIES pa
6223 where ias.ITEM_TYPE = x_itemtype
6224 and (x_itemkey is null or ias.ITEM_KEY = x_itemkey)
6225 and (x_activity is null or pa.INSTANCE_LABEL = x_activity)
6226 and ias.PROCESS_ACTIVITY = pa.INSTANCE_ID
6227 and ias.ACTIVITY_STATUS = 'ERROR';
6228
6229 begin
6230 --Check arguments.
6231 if (itemtype is null) then
6232 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
6233 Wf_Core.Raise('WFSQL_ARGS');
6234 end if;
6235
6236 if (WF_CACHE.MetaRefreshed) then
6237 null;
6238
6239 end if;
6240
6241 -- outer loop
6242 <<outer_handle>>
6243 loop
6244
6245 open actc(itemtype, itemkey, activity);
6246
6247 -- inner loop
6248 <<handle_loop>>
6249 loop
6250
6251 fetch actc into c_item_key, c_activity, c_actid;
6252 if (actc%notfound) then
6253 exit outer_handle;
6254 end if;
6255
6256 -- Not allowed in synch mode
6257 if (c_item_key = wf_engine.eng_synch) then
6258 wf_core.token('OPERATION', 'Wf_Engine.HandleErrorAll');
6259 wf_core.raise('WFENG_SYNCH_DISABLED');
6260 end if;
6261
6262 -- Argument validation
6263 if ((itemtype is null) or (c_item_key is null) or (c_activity is null) or
6264 (upper(command) not in (wf_engine.eng_skip, wf_engine.eng_retry)))
6265 then
6266 Wf_Core.Token('ITEMTYPE', itemtype);
6267 Wf_Core.Token('ITEMKEY', c_item_key);
6268 Wf_Core.Token('ACTIVITY', c_activity);
6269 Wf_Core.Token('COMMAND', command);
6270 Wf_Core.Raise('WFSQL_ARGS');
6271 end if;
6272
6273 -- Make sure item is valid
6274 Wf_Item.Root_Process(itemtype, c_item_key, root, version);
6275 if (root is null) then
6276 Wf_Core.Token('TYPE', itemtype);
6277 Wf_Core.Token('KEY', c_item_key);
6278 Wf_Core.Raise('WFENG_ITEM');
6279 end if;
6280 rootid := Wf_Process_Activity.RootInstanceId(itemtype, c_item_key, root);
6281 if (rootid is null) then
6282 Wf_Core.Token('TYPE', itemtype);
6283 Wf_Core.Token('KEY', c_item_key);
6284 Wf_Core.Token('NAME', root);
6285 Wf_Core.Raise('WFENG_ITEM_ROOT');
6286 end if;
6287
6288 -- Look for the activity instance for this item
6289 actdate := Wf_Item.Active_Date(itemtype, c_item_key);
6290
6291 -- Call the internal function to do the real job
6292 HandleErrorInternal(itemtype, c_item_key, root, rootid, c_activity,
6293 c_actid, actdate, upper(command), result);
6294
6295 exit handle_loop when
6296 (docommit and (actc%rowcount = wf_engine.commit_frequency));
6297
6298 end loop handle_loop;
6299
6300 if (actc%ISOPEN) then
6301 close actc;
6302 end if;
6303
6304 if (docommit) then
6305 commit;
6306 Fnd_Concurrent.Set_Preferred_RBS;
6307 end if;
6308 end loop outer_handle;
6309
6310 if (docommit) then
6311 commit;
6312 Fnd_Concurrent.Set_Preferred_RBS;
6313 end if;
6314
6315 if (actc%ISOPEN) then
6316 close actc;
6317 end if;
6318
6319 exception
6320 when others then
6321 Wf_Core.Context('Wf_Engine', 'HandleErrorAll', itemtype, itemkey);
6322 raise;
6323 end HandleErrorAll;
6324
6325 --
6326 -- ItemStatus (Public)
6327 -- This is a public cover for WF_ITEM_ACTIVITY_STATUS.ROOT_STATUS
6328 -- Returns the status and result for the root process of this item.
6329 -- If the item does not exist an exception will be raised.
6330 -- IN
6331 -- itemtype - Activity item type.
6332 -- itemkey - The item key.
6333 -- OUT
6334 -- status - Activity status for root process of this item
6335 -- result - Result code for root process of this item
6336 --
6337 procedure ItemStatus(itemtype in varchar2,
6338 itemkey in varchar2,
6339 status out NOCOPY varchar2,
6340 result out NOCOPY varchar2) is
6341 begin
6342 --Check arguments.
6343 if ((itemtype is null) or
6344 (itemkey is null)) then
6345 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
6346 Wf_Core.Token('ITEMKEY', nvl(itemkey, 'NULL'));
6347 Wf_Core.Raise('WFSQL_ARGS');
6348 end if;
6349
6350 wf_item_activity_status.root_status(itemtype,itemkey,status,result);
6351 exception
6352 when others then
6353 Wf_Core.Context('Wf_Engine', 'ItemStatus', itemtype, itemkey);
6354 raise;
6355 end ItemStatus;
6356
6357 -- API to reterive more granular information from the
6358 -- item
6359 -- If the item is active and
6360 -- If there is an errored activity then status is set to ERROR
6361 -- the errname , errmsg and errstack info is given
6362 -- activity , error stack etc are provided
6363 -- If the first activity is deferred then the actid of the same
6364 -- is provided and the item status is given as 'DEFERRED'
6365 -- If an activity is in notified status then we get the
6366 -- actid of the same.
6367 procedure ItemInfo(itemtype in varchar2,
6368 itemkey in varchar2,
6369 status out NOCOPY varchar2,
6370 result out NOCOPY varchar2,
6371 actid out NOCOPY number,
6372 errname out NOCOPY varchar2,
6373 errmsg out NOCOPY varchar2,
6374 errstack out NOCOPY varchar2)
6375 is
6376 l_status varchar2(8);
6377 l_result varchar2(30);
6378 l_instance_id number;
6379
6380 --Order all activities for this itemtype ,key
6381 --giving priority to ERROR , NOTIFIED , DEFERRED (--> in that order)
6382 --and execution time
6383 /*
6384 Lets do a single select for rownum < 1 this
6385 should suffice
6386
6387 cursor act_curs (p_itemtype varchar2, p_itemkey varchar2) is
6388 select pa.instance_label,pa.instance_id
6389 ias.activity_status,
6390 ias.activity_result_code ,
6391 ias.assigned_user,
6392 ias.notification_id NID,
6393 ntf.status,
6394 ias.performed_by
6395 from wf_item_activity_statuses ias,
6396 wf_process_activities pa,
6397 wf_activities ac,
6398 wf_activities ap,
6399 wf_items i,
6400 wf_notifications ntf
6401 where ias.item_type = p_itemtype
6402 and ias.item_key = p_itemkey
6403 and ias.activity_status = wf_engine.eng_completed
6404 and ias.process_activity = pa.instance_id
6405 and pa.activity_name = ac.name
6406 and pa.activity_item_type = ac.item_type
6407 and pa.process_name = ap.name
6408 and pa.process_item_type = ap.item_type
6409 and pa.process_version = ap.version
6410 and i.item_type = '&item_type'
6411 and i.item_key = ias.item_key
6412 and i.begin_date >= ac.begin_date
6413 and i.begin_date < nvl(ac.end_date, i.begin_date+1)
6414 and ntf.notification_id(+) = ias.notification_id
6415 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
6416 */
6417
6418 begin
6419 --Get the item status
6420 --Use the API above for the same
6421 wf_engine.ItemStatus(itemtype ,itemkey ,l_status,l_result);
6422
6423 --Now check the status if root has completed
6424 --we do not want to go further lower
6425 --Else if the root is still active , lets find
6426 --where the execution is stuck at.
6427
6428 if l_status= 'ACTIVE' then
6429 --Get last executed activities result and status
6430 select process_activity,
6431 activity_status,
6432 activity_result_code
6433 into l_instance_id,
6434 l_status,
6435 l_result
6436 from
6437 (
6438 select process_activity,
6439 activity_status,
6440 activity_result_code
6441 from wf_item_activity_statuses
6442 where item_type = itemtype
6443 and item_key = itemkey
6444 and activity_status <> wf_engine.eng_completed
6445 order by decode(activity_status, 'ERROR',1, 'NOTIFIED',2, 'DEFERRED',3,
6446 'SUSPEND',4, 'WAITING',5, 'ACTIVE',6, 7) asc,
6447 begin_date desc, execution_time desc
6448 )
6449 where rownum < 2;
6450
6451 --Now lets start getting all details out of the last activity
6452 if l_status = 'ERROR' then
6453 --Populate the error stack
6454 wf_item_activity_status.Error_Info(itemtype,itemkey,l_instance_id,errname,errmsg,errstack);
6455 end if;
6456
6457 status := l_status;
6458 result := l_result;
6459 actid := l_instance_id;
6460 --U can get it using the actid using Notification_Status API
6461 --nid := l_notification_id;
6462
6463
6464 else
6465 --If the root is not active return whatever is its status
6466 --and result
6467 status := l_status ;
6468 result := l_result ;
6469 end if;
6470 exception
6471 when others then
6472 Wf_Core.Context('Wf_Engine', 'ItemInfo', itemtype, itemkey);
6473 raise;
6474 end ItemInfo;
6475
6476
6477
6478
6479 --
6480 -- Activity_Exist_In_Process (Public)
6481 -- Check if an activity exist in a process
6482 -- ### OBSOLETE - Use FindActivity instead ###
6483 -- ### DO NOT REMOVE, refer to bug 1869241 ###
6484 -- IN
6485 -- p_item_type
6486 -- p_item_key
6487 -- p_activity_item_type
6488 -- p_activity_name
6489 -- RET
6490 -- TRUE if activity exist, FALSE otherwise
6491 --
6492 function Activity_Exist_In_Process (
6493 p_item_type in varchar2,
6494 p_item_key in varchar2,
6495 p_activity_item_type in varchar2,
6496 p_activity_name in varchar2)
6497 return boolean
6498 is
6499 rootactivity varchar2(30);
6500 active_date date;
6501 begin
6502 begin
6503 select ROOT_ACTIVITY, BEGIN_DATE
6504 into rootactivity, active_date
6505 from WF_ITEMS
6506 where ITEM_TYPE = p_item_type
6507 and ITEM_KEY = p_item_key;
6508 exception
6509 -- if itemtype/itemkey combination not exists, treats it as not exists
6510 when NO_DATA_FOUND then
6511 return FALSE;
6512
6513 when OTHERS then
6514 raise;
6515 end;
6516
6517 return(Wf_Engine.Activity_Exist(
6518 p_process_item_type=>p_item_type,
6519 p_process_name=>rootactivity,
6520 p_activity_item_type=>p_activity_item_type,
6521 p_activity_name=>p_activity_name,
6522 active_date=>active_date));
6523
6524 exception
6525 when others then
6526 Wf_Core.Context('Wf_Engine', 'Activity_Exist_In_Process',
6527 p_item_type, p_item_key,
6528 nvl(p_activity_item_type, p_item_type),
6529 p_activity_name);
6530 raise;
6531 end Activity_Exist_In_Process;
6532
6533 --
6534 -- Activity_Exist
6535 -- Check if an activity exist in a process
6536 -- ### OBSOLETE - Use FindActivity instead. ###
6537 -- ### DO NOT REMOVE, refer to bug 1869241 ###
6538 -- IN
6539 -- p_process_item_type
6540 -- p_process_name
6541 -- p_activity_item_type
6542 -- p_anctivity_name
6543 -- active_date
6544 -- iteration - maximum 8 level deep (0-7)
6545 -- RET
6546 -- TRUE if activity exist, FALSE otherwise
6547 --
6548 function Activity_Exist (
6549 p_process_item_type in varchar2,
6550 p_process_name in varchar2,
6551 p_activity_item_type in varchar2 default null,
6552 p_activity_name in varchar2,
6553 active_date in date default sysdate,
6554 iteration in number default 0)
6555 return boolean
6556 is
6557 m_version number;
6558 n number;
6559
6560 cursor actcur(ver number) is
6561 select WPA.ACTIVITY_ITEM_TYPE, WPA.ACTIVITY_NAME
6562 from WF_PROCESS_ACTIVITIES WPA,
6563 WF_ACTIVITIES WA
6564 where WPA.PROCESS_ITEM_TYPE = p_process_item_type
6565 and WPA.PROCESS_NAME = p_process_name
6566 and WPA.PROCESS_VERSION = ver
6567 and WPA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE
6568 and WPA.ACTIVITY_NAME = WA.NAME
6569 and WA.TYPE = 'PROCESS'
6570 and active_date >= WA.BEGIN_DATE
6571 and active_date < nvl(WA.END_DATE, active_date+1);
6572
6573 begin
6574 -- first check the iteration to avoid infinite loop
6575 if (iteration > 7) then
6576 return FALSE;
6577 end if;
6578
6579 -- then get the active version
6580 begin
6581 select VERSION into m_version
6582 from WF_ACTIVITIES
6583 where ITEM_TYPE = p_process_item_type
6584 and NAME = p_process_name
6585 and active_date >= BEGIN_DATE
6586 and active_date < nvl(END_DATE, active_date + 1);
6587 exception
6588 -- no active version exist
6589 when NO_DATA_FOUND then
6590 return FALSE;
6591
6592 when OTHERS then
6593 raise;
6594 end;
6595
6596 -- then check to see if such activity exist
6597 select count(1) into n
6598 from WF_PROCESS_ACTIVITIES
6599 where PROCESS_ITEM_TYPE = p_process_item_type
6600 and PROCESS_NAME = p_process_name
6601 and PROCESS_VERSION = m_version
6602 and ACTIVITY_ITEM_TYPE = nvl(p_activity_item_type, p_process_item_type)
6603 and ACTIVITY_NAME = p_activity_name;
6604
6605 if (n = 0) then
6606 -- recursively check subprocesses
6607 for actr in actcur(m_version) loop
6608 if (Wf_Engine.Activity_Exist(
6609 actr.activity_item_type,
6610 actr.activity_name,
6611 nvl(p_activity_item_type, p_process_item_type),
6612 p_activity_name,
6613 active_date,
6614 iteration+1)
6615 ) then
6616 return TRUE;
6617 end if;
6618 end loop;
6619
6620 return FALSE;
6621 else
6622 return TRUE;
6623 end if;
6624
6625 exception
6626 when OTHERS then
6627 Wf_Core.Context('Wf_Engine', 'Activity_Exist',
6628 p_process_item_type, p_process_name,
6629 nvl(p_activity_item_type, p_process_item_type),
6630 p_activity_name);
6631 raise;
6632 end Activity_Exist;
6633
6634 --
6635 -- Event
6636 -- Signal event to workflow process
6637 -- IN
6638 -- itemtype - Item type of process
6639 -- itemkey - Item key of process
6640 -- process_name - Process to start (only if process not already running)
6641 -- event_message - Event message payload
6642 --
6643 procedure Event(
6644 itemtype in varchar2,
6645 itemkey in varchar2,
6646 process_name in varchar2,
6647 event_message in wf_event_t)
6648 is
6649 event_name varchar2(240);
6650 actdate date; -- Active date of item
6651 root varchar2(30); -- Root process name
6652 version pls_integer; -- Root process version
6653 rootid pls_integer; -- Root process instance id
6654 aname varchar2(30); -- Item attr name
6655 avalue varchar2(2000); -- Item attr value
6656 plist wf_parameter_list_t; -- Event message parameter list
6657
6658 -- Bug 2255002
6659 parent_itemtype varchar2(8); -- parent item type
6660 parent_itemkey varchar2(240); -- parent item key
6661
6662 -- Blocked activities waiting for event (if existing process)
6663 cursor evtacts is
6664 SELECT WIAS.PROCESS_ACTIVITY actid
6665 FROM WF_ITEM_ACTIVITY_STATUSES WIAS, WF_PROCESS_ACTIVITIES WPA,
6666 WF_ACTIVITIES WA
6667 WHERE WIAS.ITEM_TYPE = event.itemtype
6668 AND WIAS.ITEM_KEY = event.itemkey
6669 AND WIAS.ACTIVITY_STATUS = 'NOTIFIED'
6670 AND WIAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID
6671 AND WPA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE
6672 AND WPA.ACTIVITY_NAME = WA.NAME
6673 AND actdate >= WA.BEGIN_DATE
6674 AND actdate < NVL(WA.END_DATE, actdate+1)
6675 AND WA.TYPE = 'EVENT'
6676 AND WA.DIRECTION = 'RECEIVE'
6677 AND (WA.EVENT_NAME is null
6678 OR WA.EVENT_NAME in
6679 (SELECT WE.NAME -- Single events
6680 FROM WF_EVENTS WE
6681 WHERE WE.TYPE = 'EVENT'
6682 AND WE.NAME = event.event_name
6683 UNION ALL
6684 SELECT GRP.NAME -- Groups containing event
6685 FROM WF_EVENTS GRP, WF_EVENT_GROUPS WEG, WF_EVENTS MBR
6686 WHERE GRP.TYPE = 'GROUP'
6687 AND GRP.GUID = WEG.GROUP_GUID
6688 AND WEG.MEMBER_GUID = MBR.GUID
6689 AND MBR.NAME = event.event_name));
6690
6691 actarr InstanceArrayTyp; -- Event activities to execute
6692 i pls_integer := 0; -- Loop counter
6693
6694 l_lock boolean;
6695
6696 begin
6697 -- Check args
6698 if ((itemtype is null) or
6699 (itemkey is null) or
6700 (event_message is null)) then
6701 Wf_Core.Token('ITEMTYPE', itemtype);
6702 Wf_Core.Token('ITEMKEY', itemkey);
6703 Wf_Core.Token('EVENT_MESSAGE', '');
6704 Wf_Core.Raise('WFSQL_ARGS');
6705 end if;
6706
6707 -- Not allowed in synch mode
6708 if (itemkey = wf_engine.eng_synch) then
6709 Wf_Core.Token('OPERATION', 'Wf_Engine.Set_Item_Parent');
6710 Wf_Core.Raise('WFENG_SYNCH_DISABLED');
6711 end if;
6712
6713 -- Retrieve event name from message
6714 event_name := event_message.GetEventName;
6715 if (event_name is null) then
6716 Wf_Core.Token('EVENT_MESSAGE.EVENT_NAME', '');
6717 Wf_Core.Raise('WFSQL_ARGS');
6718 end if;
6719
6720 if (WF_CACHE.MetaRefreshed) then
6721 null;
6722
6723 end if;
6724
6725 -- Check if item exists
6726 if (Wf_Item.Item_Exist(itemtype, itemkey)) then
6727
6728 -- Process is already running.
6729 --Acquire lock here so that no other session
6730 --will work on it.
6731 --Acquire lock here by opening the cursor
6732 l_lock := wf_item.acquire_lock(itemtype, itemkey,true);
6733
6734 -- Find all activities waiting for this event.
6735 actdate := WF_Item.Active_Date(itemtype, itemkey);
6736 for act in evtacts loop
6737 actarr(i) := act.actid;
6738 i := i + 1;
6739 end loop;
6740 actarr(i) := '';
6741
6742 else
6743 -- Process not running yet, create it.
6744 -- If process_name is null then will use selector function.
6745 Wf_Engine.CreateProcess(itemtype, itemkey, process_name);
6746 actdate := WF_Item.Active_Date(itemtype, itemkey);
6747
6748 -- Bug 2259039
6749 -- Start the new process
6750 Wf_Engine_Util.Start_Process_Internal(
6751 itemtype => itemtype,
6752 itemkey => itemkey,
6753 runmode => 'EVENT');
6754
6755 --Select the activities waiting to receive this event
6756 actdate := WF_Item.Active_Date(itemtype, itemkey);
6757 for act in evtacts loop
6758 actarr(i) := act.actid;
6759 Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, act.actid,
6760 wf_engine.eng_notified, wf_engine.eng_null, sysdate, null);
6761 i := i + 1;
6762 end loop;
6763 actarr(i) := '';
6764 end if;
6765
6766 -- Check at least one matching event activity found
6767 if (i = 0) then
6768 Wf_Core.Token('TYPE', itemtype);
6769 Wf_Core.Token('KEY', itemkey);
6770 Wf_Core.Token('EVENT', event_name);
6771 Wf_Core.Raise('WFENG_EVENT_NOTFOUND');
6772 end if;
6773
6774 -- Set item attributes for all parameters contained in the event
6775 -- message body.
6776 -- NOTE: Must be done here AFTER the process has been created
6777 -- and BEFORE any activities are executed.
6778 plist := event_message.GetParameterList;
6779 if (plist is not null) then
6780 for i in plist.first .. plist.last loop
6781 aname := plist(i).GetName;
6782 avalue := plist(i).GetValue;
6783 begin
6784 if aname = '#CONTEXT' then
6785 -- Bug 2255002 - if the parent item type and parent item key
6786 -- already exist do nothing
6787 SELECT parent_item_type, parent_item_key
6788 INTO parent_itemtype, parent_itemkey
6789 FROM wf_items
6790 WHERE item_type = itemtype
6791 AND item_key = itemkey;
6792
6793 if (parent_itemtype is null and parent_itemkey is null ) then
6794 Wf_Engine.SetItemParent(itemtype => itemtype,
6795 itemkey => itemkey,
6796 parent_itemtype =>
6797 substr(avalue,1,
6798 instr(avalue,':')-1),
6799 parent_itemkey =>
6800 substr(avalue,
6801 instr(avalue,':')+1),
6802 parent_context => null);
6803 end if;
6804 elsif aname = '#OWNER_ROLE' then
6805 --Bug 2388634
6806 --This is for the applications to set their item owner
6807 --by including a #OWNER_ROLE parameter for the event
6808 wf_engine.SetItemowner(itemtype,itemkey,avalue);
6809
6810 else
6811 -- event item attributes may use canonical masks.
6812 Wf_Engine.SetEventItemAttr(itemtype, itemkey, aname, avalue);
6813 end if;
6814 exception
6815 when others then
6816 if (wf_core.error_name = 'WFENG_ITEM_ATTR') then
6817 -- If attr doesn't exist create runtime itemattr
6818 Wf_Core.Clear;
6819 Wf_Engine.AddItemAttr(itemtype, itemkey, aname, avalue);
6820 else
6821 raise; -- All other errors are raised up.
6822 end if;
6823 end;
6824 end loop;
6825 end if;
6826
6827 -- Complete matching event activities
6828 i := 0;
6829 while (actarr(i) is not null) loop
6830 begin
6831 savepoint wf_savepoint;
6832 -- Save event data to itemattrs requested by this activity.
6833 -- #EVENTNAME
6834 aname := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actarr(i),
6835 wf_engine.eng_eventname);
6836 if (aname is not null) then
6837 Wf_Engine.SetItemAttrText(itemtype, itemkey, aname, event_name);
6838 end if;
6839 -- #EVENTKEY
6840 aname := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actarr(i),
6841 wf_engine.eng_eventkey);
6842 if (aname is not null) then
6843 Wf_Engine.SetItemAttrText(itemtype, itemkey, aname,
6844 event_message.GetEventKey);
6845 end if;
6846 -- #EVENTMESSAGE
6847 aname := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actarr(i),
6848 wf_engine.eng_eventmessage);
6849 if (aname is not null) then
6850 Wf_Engine.SetItemAttrEvent(itemtype, itemkey, aname, event_message);
6851 end if;
6852
6853 -- Execute our lovely event activity (result is always null).
6854 Wf_Engine_Util.Complete_Activity(itemtype, itemkey, actarr(i),
6855 wf_engine.eng_null);
6856 exception
6857 when others then
6858 -- If anything in this process raises an exception:
6859 -- 1. rollback any work in this process thread
6860 -- 2. set this activity to error status
6861 -- 3. execute the error process (if any)
6862 -- 4. clear the error to continue with next activity
6863 rollback to wf_savepoint;
6864 Wf_Core.Context('Wf_Engine', 'Event', itemtype, itemkey,
6865 process_name, event_name);
6866 Wf_Item_Activity_Status.Set_Error(itemtype, itemkey, actarr(i),
6867 wf_engine.eng_exception, FALSE);
6868 Wf_Engine_Util.Execute_Error_Process(itemtype, itemkey, actarr(i),
6869 wf_engine.eng_exception);
6870 Wf_Core.Clear;
6871 end;
6872 i := i + 1;
6873 end loop;
6874
6875 exception
6876 when others then
6877 Wf_Core.Context('Wf_Engine', 'Event', itemtype, itemkey,
6878 process_name, event_name);
6879 raise;
6880 end Event;
6881
6882 --
6883 -- Event2
6884 -- Signal event to workflow process
6885 -- IN
6886 -- event_message - Event message payload
6887 --
6888 procedure Event2(
6889 event_message in wf_event_t)
6890 is
6891 event_name varchar2(240);
6892 actdate date; -- Active date of item
6893 root varchar2(30); -- Root process name
6894 version pls_integer; -- Root process version
6895 rootid pls_integer; -- Root process instance id
6896 aname varchar2(30); -- Item attr name
6897 avalue varchar2(2000); -- Item attr value
6898 plist wf_parameter_list_t; -- Event message parameter list
6899 businesskey varchar2(240);
6900
6901 -- Blocked activities waiting for event (if existing process)
6902 cursor evtacts is
6903 SELECT /*+ LEADING(WA) */ WIAS.ITEM_TYPE, WIAS.ITEM_KEY, WIAS.PROCESS_ACTIVITY ACTID
6904 FROM WF_ITEM_ACTIVITY_STATUSES WIAS,
6905 WF_PROCESS_ACTIVITIES WPA,
6906 (
6907 SELECT /*+ NO_MERGE */ WA.*
6908 FROM WF_ACTIVITIES WA
6909 where WA.TYPE = 'EVENT'
6910 AND WA.DIRECTION = 'RECEIVE'
6911 AND ( WA.EVENT_NAME IS NULL OR
6912 WA.EVENT_NAME = event2.event_name OR
6913 EXISTS
6914 (
6915 SELECT null -- Groups containing event
6916 FROM WF_EVENTS GRP, WF_EVENT_GROUPS WEG, WF_EVENTS MBR
6917 WHERE GRP.TYPE = 'GROUP'
6918 AND GRP.GUID = WEG.GROUP_GUID
6919 AND WEG.MEMBER_GUID = MBR.GUID
6920 AND MBR.NAME = event2.event_name
6921 AND GRP.NAME = WA.EVENT_NAME
6922 )
6923 )
6924 ) WA,
6925 WF_ITEMS WI
6926 WHERE WIAS.ACTIVITY_STATUS = 'NOTIFIED'
6927 AND WIAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID
6928 AND WIAS.ITEM_TYPE = WPA.PROCESS_ITEM_TYPE
6929 AND WPA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE
6930 AND WPA.ACTIVITY_NAME = WA.NAME
6931 AND EXISTS
6932 ( SELECT 1 FROM WF_ACTIVITY_ATTR_VALUES WAAV,
6933 WF_ITEM_ATTRIBUTE_VALUES WIAV
6934 WHERE WAAV.PROCESS_ACTIVITY_ID = WIAS.PROCESS_ACTIVITY
6935 AND WAAV.NAME = '#BUSINESS_KEY'
6936 AND WAAV.VALUE_TYPE = 'ITEMATTR'
6937 AND WIAV.ITEM_TYPE = WIAS.ITEM_TYPE
6938 AND WIAV.ITEM_KEY = WIAS.ITEM_KEY
6939 AND WAAV.TEXT_VALUE = WIAV.NAME
6940 AND WIAV.TEXT_VALUE = event2.businesskey)
6941 AND WI.ITEM_TYPE = WIAS.ITEM_TYPE
6942 AND WI.ITEM_KEY = WIAS.ITEM_KEY
6943 FOR UPDATE OF WI.ITEM_TYPE,WI.item_key NOWAIT;
6944
6945 ectacts_rec evtacts%ROWTYPE;
6946
6947 litemtype varchar2(8);
6948 litemkey varchar2(240);
6949 lactid number;
6950
6951 i pls_integer := 0; -- Loop counter
6952
6953 begin
6954
6955 -- Check args
6956 if ((event_message is null)) then
6957 Wf_Core.Token('EVENT_MESSAGE', '');
6958 Wf_Core.Raise('WFSQL_ARGS');
6959 end if;
6960
6961 -- Retrieve event name from message
6962 event_name := event_message.GetEventName;
6963 businesskey := event_message.GetEventKey;
6964
6965 if (event_name is null) then
6966 Wf_Core.Token('EVENT_MESSAGE.EVENT_NAME', '');
6967 Wf_Core.Raise('WFSQL_ARGS');
6968 end if;
6969
6970 --Here before opening the cursor we will set the savepoint
6971 --This is so that we do not have to depend on the cursor behaviour itself
6972 --but once the cursor fails to acquire lock we expliciltly rollback
6973 --But having the for update statement in the cursor eliminates the need
6974 --for explicitly locking the workitems .
6975
6976 savepoint wf_savepoint_event2;
6977 -- Find all activities waiting for this event.
6978 for evtacts_rec in evtacts loop
6979
6980 -- Set item attributes for all parameters contained in the event
6981 -- message body.
6982 -- NOTE: Must be done here AFTER the process has been created
6983 -- and BEFORE any activities are executed.
6984 plist := event_message.GetParameterList;
6985
6986 if ((plist is not null) and (plist.count > 0)) then
6987 for i in plist.first .. plist.last loop
6988 aname := plist(i).GetName;
6989 avalue := plist(i).GetValue;
6990 begin
6991 if aname = '#CONTEXT' then
6992 Wf_Engine.SetItemParent(itemtype => evtacts_rec.item_type,
6993 itemkey => evtacts_rec.item_key,
6994 parent_itemtype =>substr(avalue,1,instr(avalue,':')-1),
6995 parent_itemkey =>substr(avalue,instr(avalue,':')+1),
6996 parent_context => null);
6997 else
6998 -- event item attributes may use canonical masks.
6999 Wf_Engine.SetEventItemAttr(evtacts_rec.item_type,
7000 evtacts_rec.item_key, aname, avalue);
7001 end if;
7002 exception
7003 when others then
7004 if (wf_core.error_name = 'WFENG_ITEM_ATTR') then
7005 -- If attr doesn't exist create runtime itemattr
7006 Wf_Core.Clear;
7007
7008 Wf_Engine.AddItemAttr(evtacts_rec.item_type,
7009 evtacts_rec.item_key,
7010 aname, avalue);
7011 else
7012 raise; -- All other errors are raised up.
7013 end if;
7014 end;
7015 end loop;
7016 end if;
7017
7018 begin
7019 savepoint wf_savepoint;
7020 -- Save event data to itemattrs requested by this activity.
7021 -- #EVENTNAME
7022 aname := Wf_Engine.GetActivityAttrText(evtacts_rec.item_type,
7023 evtacts_rec.item_key,
7024 evtacts_rec.actid,
7025 wf_engine.eng_eventname);
7026 if (aname is not null) then
7027 Wf_Engine.SetItemAttrText(evtacts_rec.item_type,
7028 evtacts_rec.item_key,
7029 aname,
7030 event_name);
7031 end if;
7032 -- #EVENTKEY
7033 aname := Wf_Engine.GetActivityAttrText(evtacts_rec.item_type,
7034 evtacts_rec.item_key,
7035 evtacts_rec.actid,
7036 wf_engine.eng_eventkey);
7037 if (aname is not null) then
7038 Wf_Engine.SetItemAttrText(evtacts_rec.item_type,
7039 evtacts_rec.item_key, aname,
7040 event_message.GetEventKey);
7041 end if;
7042 -- #EVENTMESSAGE
7043 aname := Wf_Engine.GetActivityAttrText(evtacts_rec.item_type,
7044 evtacts_rec.item_key,
7045 evtacts_rec.actid,
7046 wf_engine.eng_eventmessage);
7047 if (aname is not null) then
7048 Wf_Engine.SetItemAttrEvent(evtacts_rec.item_type,
7049 evtacts_rec.item_key,
7050 aname,
7051 event_message);
7052 end if;
7053
7054 -- Execute our lovely event activity (result is always null).
7055 Wf_Engine_Util.Complete_Activity(evtacts_rec.item_type,
7056 evtacts_rec.item_key, evtacts_rec.actid,
7057 wf_engine.eng_null);
7058 exception
7059 when others then
7060 -- If anything in this process raises an exception:
7061 -- 1. rollback any work in this process thread
7062 -- 2. set this activity to error status
7063 -- 3. execute the error process (if any)
7064 -- 4. clear the error to continue with next activity
7065 rollback to wf_savepoint;
7066 Wf_Core.Context('Wf_Engine', 'Event2', evtacts_rec.item_type,
7067 evtacts_rec.item_key, event_name);
7068 Wf_Item_Activity_Status.Set_Error(evtacts_rec.item_type,
7069 evtacts_rec.item_key,
7070 evtacts_rec.actid,
7071 wf_engine.eng_exception, FALSE);
7072 Wf_Engine_Util.Execute_Error_Process(evtacts_rec.item_type,
7073 evtacts_rec.item_key,
7074 evtacts_rec.actid,
7075 wf_engine.eng_exception);
7076 Wf_Core.Clear;
7077 end;
7078
7079 i := i + 1;
7080 end loop;
7081
7082 -- Check at least one matching event activity found
7083 if (i = 0) then
7084 Wf_Core.Token('EVENT2', event_name);
7085 Wf_Core.Raise('WFENG_EVENT_NOTFOUND');
7086 end if;
7087
7088 exception
7089 when resource_busy then
7090 --Rollback to ensure that we aren't locking anything here
7091 rollback to wf_savepoint_event2;
7092 raise;
7093 when others then
7094 Wf_Core.Context('Wf_Engine', 'Event2', businesskey, event_name);
7095 raise;
7096 end Event2;
7097
7098 --
7099 -- AddToItemAttrNumber
7100 -- Increments (or decrements) an numeric item attribute and returns the
7101 -- new value. If the item attribute does not exist, it returns null.
7102 -- IN
7103 -- p_itemtype - process item type
7104 -- p_itemkey - process item key
7105 -- p_aname - Item Attribute Name
7106 -- p_name - attribute name
7107 -- p_addend - Numeric value to be added to the item attribute. If p_addend
7108 -- is set to null, it will set the ItemAttrNumber to 0.
7109 --
7110 -- RETURNS
7111 -- Attribute value (NUMBER) or NULL if attribute does not exist.
7112 --
7113 function AddToItemAttrNumber(
7114 p_itemtype in varchar2,
7115 p_itemkey in varchar2,
7116 p_aname in varchar2,
7117 p_addend in number)
7118 return number is
7119 iStatus PLS_INTEGER;
7120 wiavIND NUMBER;
7121 l_avalue NUMBER;
7122 begin
7123 -- Check Arguments
7124 if ((p_itemtype is null) or
7125 (p_itemkey is null) or
7126 (p_aname is null)) then
7127 Wf_Core.Token('P_ITEMTYPE', nvl(p_itemtype, 'NULL'));
7128 Wf_Core.Token('P_ITEMKEY', nvl(p_itemkey, 'NULL'));
7129 Wf_Core.Token('P_ANAME', nvl(p_aname, 'NULL'));
7130 Wf_Core.Raise('WFSQL_ARGS');
7131 end if;
7132
7133 if (p_itemkey = wf_engine.eng_synch) then
7134 WF_CACHE.GetItemAttrValue(p_itemtype, p_itemKey, p_aname, iStatus,
7135 wiavIND);
7136
7137 if (iStatus <> WF_CACHE.task_SUCCESS) then
7138 return null;
7139
7140 else
7141 if (p_addend is NOT null) then
7142 WF_CACHE.ItemAttrValues(wiavIND).NUMBER_VALUE :=
7143 (WF_CACHE.ItemAttrValues(wiavIND).NUMBER_VALUE + p_addend);
7144 else
7145 WF_CACHE.ItemAttrValues(wiavIND).NUMBER_VALUE := 0;
7146 end if;
7147
7148 return WF_CACHE.ItemAttrValues(wiavIND).NUMBER_VALUE;
7149
7150 end if;
7151
7152 else
7153 if (p_addend is NOT null) then
7154 update WF_ITEM_ATTRIBUTE_VALUES wiav
7155 set wiav.NUMBER_VALUE = (wiav.NUMBER_VALUE+p_addend)
7156 where wiav.ITEM_TYPE = p_itemtype
7157 and wiav.ITEM_KEY = p_itemkey
7158 and wiav.NAME = p_aname
7159 returning wiav.NUMBER_VALUE into l_avalue;
7160 else
7161 update WF_ITEM_ATTRIBUTE_VALUES wiav
7162 set wiav.NUMBER_VALUE = 0
7163 where wiav.ITEM_TYPE = p_itemtype
7164 and wiav.ITEM_KEY = p_itemkey
7165 and wiav.NAME = p_aname
7166 returning wiav.NUMBER_VALUE into l_avalue;
7167 end if;
7168
7169 if (SQL%NOTFOUND) then
7170 return null;
7171 end if;
7172 return l_avalue;
7173
7174 end if;
7175
7176 exception
7177 when no_data_found then
7178 return NULL;
7179
7180 when others then
7181 Wf_Core.Context('Wf_Engine', 'AddToItemAttrNumber', p_itemtype, p_itemkey,
7182 p_aname, to_char(p_addend));
7183 raise;
7184 end AddToItemAttrNumber;
7185
7186 -- Bug 5903106
7187 -- HandleErrorConcurrent
7188 -- Concurrent Program API to handle any process activity that has
7189 -- encountered an error. This Concurrent Program API is a wrapper
7190 -- to HandleError and HandleErrorAll based on the parameter values
7191 -- supplied.
7192 -- IN
7193 -- p_errbuf
7194 -- p_retcode
7195 -- p_itemtype - Workflow Itemtype
7196 -- p_itemkey - Itemkey of the process
7197 -- p_activity - Workflow process activity label
7198 -- p_start_date - Errored On or After date
7199 -- p_end_date - Errored On or Before date
7200 -- p_max_retry - Maximum retries allowed on an activity
7201 -- p_docommit - Y (Yes) if you want a commit for every n iterations.
7202 -- n is defined as wf_engine.commit_frequency
7203 --
7204 procedure HandleErrorConcurrent(p_errbuf out nocopy varchar2,
7205 p_retcode out nocopy varchar2,
7206 p_itemtype in varchar2,
7207 p_itemkey in varchar2,
7208 p_process in varchar2,
7209 p_activity in varchar2,
7210 p_start_date in varchar2,
7211 p_end_date in varchar2,
7212 p_max_retry in varchar2,
7213 p_docommit in varchar2)
7214 is
7215
7216 l_start_date date;
7217 l_end_date date;
7218 l_max_retry number;
7219 l_docommit boolean;
7220 l_count number;
7221
7222 l_errname varchar2(30);
7223 l_errmsg varchar2(2000);
7224 l_stack varchar2(32000);
7225
7226 CURSOR c_err_acts (x_item_type varchar2,
7227 x_item_key varchar2,
7228 x_process varchar2,
7229 x_activity varchar2,
7230 x_start_date date,
7231 x_end_date date,
7232 x_max_retry number)
7233 IS
7234 SELECT wias.item_key,
7235 wpa.process_name,
7236 wpa.instance_label activity
7237 FROM wf_item_activity_statuses wias,
7238 wf_process_activities wpa
7239 WHERE wias.item_type = x_item_type
7240 AND (x_item_key IS NULL OR wias.item_key = x_item_key)
7241 AND (x_process IS NULL OR wpa.process_name = x_process)
7242 AND (x_activity IS NULL OR wpa.instance_label = x_activity)
7243 AND (x_start_date IS NULL OR wias.begin_date >= x_start_date)
7244 AND (x_end_date IS NULL OR wias.begin_date <= x_end_date)
7245 AND wias.process_activity = wpa.instance_id
7246 AND wias.activity_status = 'ERROR'
7247 AND x_max_retry >=
7248 (SELECT count(1)
7249 FROM wf_item_activity_statuses_h wiash
7250 WHERE wiash.item_type = wias.item_type
7251 AND wiash.item_key = wias.item_key
7252 AND wiash.process_activity = wias.process_activity
7253 AND wiash.action = 'RETRY');
7254
7255 begin
7256
7257 l_start_date := to_date(null);
7258 l_end_date := to_date(null);
7259
7260 -- Date value from CP is in fnd_flex_val_util.g_date_format_19 - 'RRRR/MM/DD HH24:MI:SS'
7261 -- This is same as wf_core.canonical_date_mask.
7262 if (p_start_date is not null) then
7263 l_start_date := to_date(p_start_date, wf_core.canonical_date_mask);
7264 end if;
7265
7266 if (p_end_date is not null) then
7267 l_end_date := to_date(p_end_date, wf_core.canonical_date_mask);
7268 end if;
7269
7270 if (nvl(p_docommit, 'Y') = 'Y') then
7271 l_docommit := TRUE;
7272 else
7273 l_docommit := FALSE;
7274 end if;
7275
7276 l_max_retry := to_number(nvl(p_max_retry, '5'));
7277
7278 -- Write parameters to log file
7279 Fnd_File.Put_Line(Fnd_File.Log, 'Wf_Engine.HandleErrorConcurrent');
7280 Fnd_File.Put_Line(Fnd_File.Log, 'p_itemtype - '||p_itemtype);
7281 Fnd_File.Put_Line(Fnd_File.Log, 'p_itemkey - '||p_itemkey);
7282 Fnd_File.Put_Line(Fnd_File.Log, 'p_process - '||p_process);
7283 Fnd_File.Put_Line(Fnd_File.Log, 'p_activity - '||p_activity);
7284 Fnd_File.Put_Line(Fnd_File.Log, 'l_start_date - '||to_char(l_start_date));
7285 Fnd_File.Put_Line(Fnd_File.Log, 'l_end_date - '||to_char(l_end_date));
7286 Fnd_File.Put_Line(Fnd_File.Log, 'l_max_retry - '||to_char(l_max_retry));
7287 Fnd_File.Put_Line(Fnd_File.Log, 'p_docommit - '||p_docommit);
7288
7289 -- Check Arguments
7290 if (p_itemtype is null) then
7291 Wf_Core.Token('ITEMTYPE', nvl(p_itemtype, 'NULL'));
7292 Wf_Core.Raise('WFSQL_ARGS');
7293 end if;
7294
7295 if (WF_CACHE.MetaRefreshed) then
7296 null;
7297 end if;
7298
7299 -- Retry all activities in ERROR for given parameters
7300 for l_rec in c_err_acts(p_itemtype,
7301 p_itemkey,
7302 p_process,
7303 p_activity,
7304 l_start_date,
7305 l_end_date,
7306 l_max_retry)
7307 loop
7308
7309 Wf_Engine.HandleError(itemtype => p_itemtype,
7310 itemkey => l_rec.item_key,
7311 activity => l_rec.process_name||':'||l_rec.activity,
7312 command => wf_engine.eng_retry,
7313 result => '');
7314
7315 if (l_docommit and c_err_acts%rowcount = wf_engine.commit_frequency) then
7316 commit;
7317 Fnd_Concurrent.Set_Preferred_RBS;
7318 end if;
7319 l_count := c_err_acts%rowcount;
7320 end loop;
7321
7322 Fnd_File.Put_Line(Fnd_File.Log, 'Items Processed - '||l_count);
7323
7324 if (l_docommit) then
7325 commit;
7326 Fnd_Concurrent.Set_Preferred_RBS;
7327 end if;
7328
7329 -- Successful completion
7330 p_errbuf := '';
7331 p_retcode := 0;
7332
7333 exception
7334 when others then
7335 Wf_Core.Get_error(l_errname, l_errmsg, l_stack);
7336
7337 -- Completed with Error
7338 p_errbuf := nvl(l_errmsg, sqlerrm);
7339 p_retcode := '2';
7340
7341 end HandleErrorConcurrent;
7342 -- bug 6161171
7343 procedure AbortProcess2(itemtype in varchar2,
7344 itemkey in varchar2,
7345 process in varchar2 default '',
7346 result in varchar2 default wf_engine.eng_force,
7347 verify_lock in binary_integer default 0,
7348 cascade in binary_integer default 0)
7349 is
7350 l_verify_lock boolean;
7351 l_cascade boolean;
7352 begin
7353 l_verify_lock := false;
7354 l_cascade := false;
7355
7356 if (verify_lock <> 0) then
7357 l_verify_lock := true;
7358 end if;
7359 if (cascade <> 0) then
7360 l_cascade := true;
7361 end if;
7362
7363 wf_engine.AbortProcess(itemtype, itemkey, process, result, l_verify_lock, l_cascade);
7364
7365 end AbortProcess2;
7366
7367 end Wf_Engine;