1 package body WF_ENGINE_BULK as
2 /* $Header: wfengblkb.pls 120.5 2006/02/10 01:13:02 anachatt noship $ */
3
4
5 --
6 -- Exception
7 --
8 no_savepoint exception;
9 bad_format exception;
10
11 pragma EXCEPTION_INIT(no_savepoint, -1086);
12 pragma EXCEPTION_INIT(bad_format, -6502);
13
14 resource_busy exception;
15 pragma exception_init(resource_busy, -00054);
16
17 -- private variable
18 schema varchar2(30);
19 g_execCount number:=0;
20
21
22 --
23 -- ConsolidateKeys (PRIVATE)
24 --Condense the sparse itemkey list into a dense one
25 -- IN OUT
26 -- itemkeys
27 -- userkeys
28 -- ownerroles
29
30 procedure ConsolidateKeys(itemkeys in out NOCOPY WF_ENGINE_BULK.Itemkeytabtype,
31 user_keys in out NOCOPY WF_ENGINE_BULK.UserKeyTabType ,
32 owner_roles in out NOCOPY WF_ENGINE_BULK.OwnerRoleTabType )
33 is
34
35 begin
36
37 if itemkeys is not null and itemkeys.count>0 then
38 for arrIndex in itemkeys.first..itemkeys.last loop
39 if not itemkeys.exists(arrIndex) then
40 --fill in the gap with the last list element
41 itemkeys(arrIndex):=itemkeys(itemkeys.last);
42
43 --consolidate user_keys if it is not null
44 if user_keys is not null and user_keys.count>0 then
45 user_keys(arrIndex):=user_keys(user_keys.last);
46 user_keys.delete(user_keys.last);
47 end if;
48 --consolidate owner_roles if it is not null
49 if owner_roles is not null and owner_roles.count > 0 then
50 owner_roles(arrIndex):=owner_roles(owner_roles.last);
51 owner_roles.delete(owner_roles.last);
52 end if;
53 itemkeys.delete(itemkeys.last);
54 end if;
55 end loop;
56 end if;
57
58 exception
59 when others then
60 raise;
61 end ConsolidateKeys;
62
63
64 --
65 -- Current_Schema (PRIVATE)
66 -- Return the current schema
67 --
68 function Current_Schema
69 return varchar2
70 is
71 begin
72 if (wf_engine_bulk.schema is null) then
73 select sys_context('USERENV','CURRENT_SCHEMA')
74 into wf_engine_bulk.schema
75 from sys.dual;
76 end if;
77 return wf_engine_bulk.schema;
78 exception
79 when OTHERS then
80 Wf_Core.Context('Wf_Engine_Bulk', 'Current_Schema');
81 raise;
82 end Current_Schema;
83
84 --
85 -- BulkCreateItems (PRIVATE)
86 -- Create rows in bulk in the WF_ITEMS table with the given item type
87 -- , item key list
88 -- and the root process name.
89 -- IN
90 -- itemtype - item type
91 -- wflow - root process name for this item.
92 -- actdate - active date of item
93 -- user
94 --
95 procedure BulkCreateItems(
96 itemtype in varchar2,
97 wflow in varchar2,
98 actdate in date,
99 user_keys in out NOCOPY wf_engine_bulk.userkeytabtype,
100 owner_roles in out NOCOPY wf_engine_bulk.ownerroletabtype,
101 parent_itemtype in varchar2,
102 parent_itemkey in varchar2,
103 parent_context in varchar2)
104 is
105
106 rootversion number;
107 itemkeys wf_engine_bulk.itemkeytabtype;
108
109 begin
110
111 rootversion := Wf_Activity.Version(itemtype, wflow, actdate);
112 --initialize the table of itemkeys from the list of successful items.
113 if g_SuccessItems.COUNT=0 then
114 return;
115 else
116 itemkeys:=g_SuccessItems;
117 end if;
118
119
120 begin
121 --insert depending on whether user_keys and owner_roles are null or not
122 if user_keys.count > 0 and owner_roles.count > 0 then
123 forall arrInd in itemkeys.FIRST..itemkeys.LAST SAVE EXCEPTIONS
124 -- NORMAL: Insert new item and attributes directly in the db
125 insert into WF_ITEMS (
126 ITEM_TYPE,
127 ITEM_KEY,
128 ROOT_ACTIVITY,
129 ROOT_ACTIVITY_VERSION,
130 OWNER_ROLE,
131 PARENT_ITEM_TYPE,
132 PARENT_ITEM_KEY,
133 PARENT_CONTEXT,
134 BEGIN_DATE,
135 END_DATE,
136 USER_KEY
137 ) values (
138 itemtype,
139 itemkeys(arrInd),
140 wflow,
141 rootversion,
142 owner_roles(arrInd),
143 parent_itemtype,
144 parent_itemkey,
145 parent_context,
146 actdate,
147 to_date(NULL),
148 user_keys(arrInd)
149 );
150 elsif user_keys.count>0 and owner_roles.count=0 then
151 forall arrInd in itemkeys.FIRST..itemkeys.LAST SAVE EXCEPTIONS
152 insert into WF_ITEMS (
153 ITEM_TYPE,
154 ITEM_KEY,
155 ROOT_ACTIVITY,
156 ROOT_ACTIVITY_VERSION,
157 PARENT_ITEM_TYPE,
158 PARENT_ITEM_KEY,
159 PARENT_CONTEXT,
160 BEGIN_DATE,
161 END_DATE,
162 USER_KEY
163 ) values (
164 itemtype,
165 itemkeys(arrInd),
166 wflow,
167 rootversion,
168 parent_itemtype,
169 parent_itemkey,
170 parent_context,
171 actdate,
172 to_date(NULL),
173 user_keys(arrInd)
174 );
175 elsif owner_roles.count>0 and user_keys.count=0 then
176 forall arrInd in itemkeys.FIRST..itemkeys.LAST SAVE EXCEPTIONS
177 insert into WF_ITEMS (
178 ITEM_TYPE,
179 ITEM_KEY,
180 ROOT_ACTIVITY,
181 ROOT_ACTIVITY_VERSION,
182 OWNER_ROLE,
183 PARENT_ITEM_TYPE,
184 PARENT_ITEM_KEY,
185 PARENT_CONTEXT,
186 BEGIN_DATE,
187 END_DATE
188 ) values (
189 itemtype,
190 itemkeys(arrInd),
191 wflow,
192 rootversion,
193 owner_roles(arrInd),
194 parent_itemtype,
195 parent_itemkey,
196 parent_context,
197 actdate,
198 to_date(NULL)
199 );
200 elsif owner_roles.count=0 and user_keys.count=0 then
201 forall arrInd in itemkeys.FIRST..itemkeys.LAST SAVE EXCEPTIONS
202 insert into WF_ITEMS (
203 ITEM_TYPE,
204 ITEM_KEY,
205 ROOT_ACTIVITY,
206 ROOT_ACTIVITY_VERSION,
207 PARENT_ITEM_TYPE,
208 PARENT_ITEM_KEY,
209 PARENT_CONTEXT,
210 BEGIN_DATE,
211 END_DATE
212 ) values (
213 itemtype,
214 itemkeys(arrInd),
215 wflow,
216 rootversion,
217 parent_itemtype,
218 parent_itemkey,
219 parent_context,
220 actdate,
221 to_date(NULL)
222 );
223 end if;
224 exception
225 when others then
226
227 if SQL%BULK_EXCEPTIONS.COUNT>0 then
228 for ExceptionInd in 1..SQL%BULK_EXCEPTIONS.COUNT loop
229
230 g_failedItems(g_failedItems.COUNT+1):=
231 itemKeys(SQL%BULK_EXCEPTIONS(ExceptionInd).ERROR_INDEX);
232 itemkeys.DELETE(SQL%BULK_EXCEPTIONS(ExceptionInd).ERROR_INDEX);
233 if user_keys is not null and user_keys.count>0 then
234 user_keys.DELETE(SQL%BULK_EXCEPTIONS(ExceptionInd).ERROR_INDEX);
235 end if;
236 if owner_roles is not null and owner_roles.count>0 then
237 owner_roles.DELETE(SQL%BULK_EXCEPTIONS(ExceptionInd).ERROR_INDEX);
238 end if;
239 end loop;
240 -- condense the PL/SQL tables to eliminate sparse indices
241 ConsolidateKeys(ItemKeys,User_Keys,Owner_Roles);
242 end if;
243 end;
244
245 if itemkeys.count > 0 AND (not wf_item.Attribute_On_Demand(itemtype, itemkeys(itemkeys.FIRST))) then
246 begin
247 -- Initialize item attributes in bulk
248
249 --if its on demand, we are not going to do this
250 forall arrInd in itemkeys.FIRST..itemkeys.LAST save exceptions
251
252 -- NORMAL: store attributes in table
253 insert into WF_ITEM_ATTRIBUTE_VALUES (
254 ITEM_TYPE,
255 ITEM_KEY,
256 NAME,
257 TEXT_VALUE,
258 NUMBER_VALUE,
259 DATE_VALUE
260 ) select
261 itemtype,
262 itemkeys(arrInd),
263 WIA.NAME,
264 WIA.TEXT_DEFAULT,
265 WIA.NUMBER_DEFAULT,
266 WIA.DATE_DEFAULT
267 from WF_ITEM_ATTRIBUTES WIA
268 where WIA.ITEM_TYPE = itemtype;
269
270 exception
271 when others then
272 if SQL%BULK_EXCEPTIONS.COUNT>0 then
273 for ExceptionInd in 1..SQL%BULK_EXCEPTIONS.COUNT loop
274 g_failedItems(g_failedItems.COUNT+1):=
275 itemKeys(SQL%BULK_EXCEPTIONS(ExceptionInd).ERROR_INDEX);
276 itemkeys.DELETE(SQL%BULK_EXCEPTIONS(ExceptionInd).ERROR_INDEX);
277 user_keys.DELETE(SQL%BULK_EXCEPTIONS(ExceptionInd).ERROR_INDEX);
278 owner_roles.DELETE(SQL%BULK_EXCEPTIONS(ExceptionInd).ERROR_INDEX);
279 end loop;
280 ConsolidateKeys(ItemKeys,User_Keys,Owner_Roles);
281 end if;
282 end;
283 end if;
284 -- reset the SuccessItems table to reflect the successfully created items.
285 g_SuccessItems:=itemkeys;
286
287 end BulkCreateItems;
288
289
290 --
291 -- BulkAddItemAttr (PRIVATE)
292 -- Add a new unvalidated run-time item attribute for a list of itemkeys.
293 -- IN:
294 -- itemtype - item type
295
296 -- aname - attribute name
297 -- text_value - add text value to it if provided.
298 -- number_value - add number value to it if provided.
299 -- date_value - add date value to it if provided.
300 -- NOTE:
301 -- The new attribute has no type associated. Get/set usages of the
302 -- attribute must insure type consistency.
303 --
304 procedure BulkAddItemAttr(itemtype in varchar2,
305 aname in varchar2,
306 text_values in wf_engine.textTabTyp)
307 is
308 bulkException boolean := FALSE;
309 l_itemkeys WF_ENGINE_BULK.itemkeytabtype;
310 l_ukeys WF_ENGINE_BULK.UserKeyTabType;
311 l_rkeys WF_ENGINE_BULK.OwnerRoleTabType;
312 begin
313 -- Check Arguments
314 if ((itemtype is null) or
315 (aname is null)) then
316 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
317 Wf_Core.Token('ANAME', nvl(aname, 'NULL'));
318 Wf_Core.Raise('WFSQL_ARGS');
319
320 end if;
321 If g_SuccessItems.count=0 then
322 return;
323 end if;
324 l_itemkeys:=g_SuccessItems;
325
326 --bulk insert the new attribute for all the itemkeys in the list
327 begin
328
329 forall arrInd in l_itemkeys.FIRST..l_itemkeys.LAST SAVE EXCEPTIONS
330 insert into WF_ITEM_ATTRIBUTE_VALUES (
331 ITEM_TYPE,
332 ITEM_KEY,
333 NAME,
334 TEXT_VALUE
335 ) values (
336 itemtype,
337 l_itemkeys(arrInd),
338 aname,
339 BulkAddItemAttr.text_values(arrInd)
340 );
341
342 exception
343 when others then
344 if SQL%BULK_EXCEPTIONS.COUNT>0 then
345 bulkException:= true;
346 -- load the failedItems table with the list of errored itemkeys,
347 -- and remove them from the itemkeys table
348 for failIndex in 1..SQL%BULK_EXCEPTIONS.COUNT loop
349 g_FailedItems(g_FailedItems.COUNT+1):=
350 l_itemkeys(SQL%BULK_EXCEPTIONS(failIndex).ERROR_INDEX);
351 l_itemkeys.DELETE(SQL%BULK_EXCEPTIONS(failIndex).ERROR_INDEX);
352 end loop;
353 end if;
354 end;
355 -- load successfully added itemkeys to the SuccessItems Table.
356 if ( bulkException) then
357 ConsolidateKeys(l_itemkeys,l_ukeys,l_rkeys);
358 end if;
359 g_SuccessItems:=l_itemkeys;
360 exception
361 when others then
362 raise;
363 end BulkAddItemAttr;
364
365
366
367
368 --
369 -- SetItemAttrText (PUBLIC)
370 -- Set the values of an array of text item attribute.
371 -- Unlike SetItemAttrText(), it stores the values directly.
372 -- IN:
373 -- itemtype - Item type
374 -- itemkey - Item key
375 -- aname - Array of Names
376 -- avalue - Array of New values for attribute
377 --
378 procedure SetItemAttrText (
379 itemtype in varchar2,
380 itemkeys in Wf_Engine_Bulk.ItemKeyTabType,
381 anames in Wf_Engine.NameTabTyp,
382 avalues in Wf_Engine.TextTabTyp)
383 is
384 status pls_integer;
385 arrayIndex pls_integer;
386 j pls_integer;
387 wiavIND number;
388 success_cnt number;
389 match boolean;
390 succAttrUpdates Wf_Engine.NameTabTyp;
391 succItemUpdates Wf_Engine_Bulk.ItemKeyTabType;
392
393 begin
394 -- Check Arguments
395 if (itemtype is null) then
396 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
397 Wf_Core.Raise('WFSQL_ARGS');
398 end if;
399
400 if (anames.COUNT = 0 or avalues.COUNT = 0) then
401 -- Do not do anything if index table is empty.
402 return;
403
404 elsif ((anames.LAST <> avalues.LAST or anames.COUNT <> avalues.COUNT)
405 or (anames.LAST<> itemkeys.LAST or anames.COUNT<> itemkeys.COUNT)) then
406 -- Raise an error if the two index tables do not end at the same index
407 -- or do not have the same number of elements.
408 g_failedItems:=itemkeys;
409 g_failedAttributes:=anames;
410 Wf_Core.Raise('WFENG_BLK_ITM_ATTRARR_MISMATCH');
411 end if;
412
413 --If itemtype is on demand, we will insert default values if not exists already
414 if wf_item.Attribute_On_Demand(itemtype, itemkeys(itemkeys.FIRST)) then
415 begin
416 forall arrayIndex in itemkeys.FIRST..itemkeys.LAST save exceptions
417 insert into WF_ITEM_ATTRIBUTE_VALUES
418 (ITEM_TYPE, ITEM_KEY, NAME, TEXT_VALUE)
419 select itemtype, itemkeys(arrayIndex), anames(arrayIndex), text_default
420 from WF_ITEM_ATTRIBUTES WIA
421 where
422 WIA.ITEM_TYPE = itemtype
423 and
424 WIA.NAME = anames(arrayIndex)
425 and not exists (select 1 from WF_ITEM_ATTRIBUTE_VALUES WIAV
426 where WIAV.item_type=itemtype
427 and WIAV.item_key=itemkeys(arrayIndex)
428 and WIAV.NAME=anames(arrayIndex));
429 exception
430 when others then
431 null; -- ignore failures here
432 end;
433 end if;
434
435 -- Reset the table of Failed Items and Failed Attributes
436 g_FailedItems.DELETE;
437 g_FailedAttributes.DELETE;
438 -- Set the text value.
439 begin
440 forall arrInd in itemkeys.FIRST..itemkeys.LAST save exceptions
441 update WF_ITEM_ATTRIBUTE_VALUES set
442 TEXT_VALUE = avalues(arrInd)
443 where ITEM_TYPE = itemtype
444 and ITEM_KEY = itemkeys(arrInd)
445 and NAME = anames(arrInd)
446 returning item_key,name bulk collect into succItemUpdates, succAttrUpdates;
447
448 exception
449 when others then
450 if sql%bulk_exceptions.count>0 then
451 for ErrorIndex in 1..sql%bulk_exceptions.count loop
452 g_failedItems(g_FailedItems.COUNT+1):=
453 itemkeys(sql%bulk_exceptions(ErrorIndex).error_index);
454 g_failedAttributes(g_FailedAttributes.COUNT+1):=
455 anames(sql%bulk_exceptions(ErrorIndex).error_index);
456 end loop;
457 end if;
458 end;
459
460 if (succItemUpdates.count <> itemkeys.COUNT) then
461 -- determine the failed item/atrributes
462 if succItemUpdates.count=0 then
463 g_failedItems:=itemkeys;
464 g_failedAttributes:=anames;
465 else
466
467 for i in itemkeys.first..itemkeys.last loop
468
469 if ((itemkeys.count - i + 1) = succItemUpdates.count) then
470 -- we are done, the rest are all successful
471 exit;
472 end if;
473 match := false;
474 j:=succItemUpdates.first;
475 while j <= succItemUpdates.last loop
476 if anames(i) = succAttrUpdates(j)
477 and itemkeys(i) = succItemUpdates(j) then
478
479 -- remove the item/attr from the success table
480 succItemUpdates.delete(j);
481 succAttrUpdates.delete(j);
482 match := true;
483 exit;
484 end if;
485
486 j:=succItemUpdates.next(j);
487
488 end loop;
489
490 if not(match) then
491 -- item has failed.Insert into list of failed items
492 g_failedItems(g_FailedItems.COUNT+1):=itemkeys(i);
493 g_failedAttributes(g_FailedAttributes.COUNT+1):=anames(i);
494
495 end if;
496 end loop;
497 end if; --successUpdates count=0
498 end if; --sucessUpdates doesnot match item count
499
500
501 if g_failedItems.COUNT >0 then
502 WF_CORE.TOKEN('ITEMTYPE',itemtype);
503 WF_CORE.TOKEN('TOTAL',to_char(itemkeys.count));
504 WF_CORE.TOKEN('FAILED',to_char(g_FailedItems.COUNT));
505 WF_CORE.Raise('WFENG_BULK_SETATTR');
506 end if;
507
508 exception
509 when others then
510 Wf_Core.Context('Wf_Engine_Bulk', 'SetItemAttrText', itemtype);
511 raise;
512 end SetItemAttrText;
513
514
515 --
516 -- SetItemAttrNumber (PUBLIC)
517 -- Set the value of an array of number item attribute.
518 -- Attribute must be a NUMBER-type attribute.
519 -- IN:
520 -- itemtype - Item type
521 -- itemkey - Item key
522 -- aname - Array of Names
523 -- avalue - Array of new value for attribute
524 --
525 procedure SetItemAttrNumber(
526 itemtype in varchar2,
527 itemkeys in Wf_Engine_Bulk.ItemKeyTabType,
528 anames in Wf_Engine.NameTabTyp,
529 avalues in Wf_Engine.NumTabTyp)
530 is
531 arrayIndex pls_integer;
532 status pls_integer;
533 wiavIND number;
534 success_cnt number;
535 j pls_integer;
536 match boolean;
537 succAttrUpdates Wf_Engine.NameTabTyp;
538 succItemUpdates Wf_Engine_Bulk.ItemKeyTabType;
539
540 begin
541 -- Check Arguments
542 if (itemtype is null) then
543 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
544 Wf_Core.Raise('WFSQL_ARGS');
545
546 elsif (anames.COUNT = 0 or avalues.COUNT = 0) then
547 -- Do not do anything if index table is empty.
548 return;
549
550 elsif ((anames.LAST <> avalues.LAST or anames.COUNT <> avalues.COUNT)
551 or (anames.LAST<> itemkeys.LAST or anames.COUNT<> itemkeys.COUNT)) then
552 -- Raise an error if the two index tables do not end at the same index
553 -- or do not have the same number of elements.
554 g_failedItems:=itemkeys;
555 g_failedAttributes:=anames;
556 Wf_Core.Raise('WFENG_BLK_ITM_ATTRARR_MISMATCH');
557 end if;
558 --If itemtype is on demand, we will insert default values if not exists already
559 if wf_item.Attribute_On_Demand(itemtype, itemkeys(itemkeys.FIRST)) then
560 begin
561 forall arrayIndex in itemkeys.FIRST..itemkeys.LAST save exceptions
562 insert into WF_ITEM_ATTRIBUTE_VALUES
563 (ITEM_TYPE, ITEM_KEY, NAME, NUMBER_VALUE)
564 select itemtype, itemkeys(arrayIndex), anames(arrayIndex), number_default
565 from WF_ITEM_ATTRIBUTES WIA
566 where
567 WIA.ITEM_TYPE = itemtype
568 and
569 WIA.NAME = anames(arrayIndex)
570 and not exists (select 1 from WF_ITEM_ATTRIBUTE_VALUES WIAV
571 where WIAV.item_type=itemtype
572 and WIAV.item_key=itemkeys(arrayIndex)
573 and WIAV.NAME=anames(arrayIndex));
574 exception
575 when others then
576 null; -- ignore failures here and let update to handle the logic
577 end;
578 end if;
579
580 -- Reset the table of Failed Items and Failed Attributes
581 g_FailedItems.DELETE;
582 g_failedAttributes.DELETE;
583
584 -- Set the number value.
585 begin
586 forall arrInd in itemkeys.FIRST..itemkeys.LAST save exceptions
587 update WF_ITEM_ATTRIBUTE_VALUES set
588 NUMBER_VALUE = avalues(arrInd)
589 where ITEM_TYPE = itemtype
590 and ITEM_KEY = itemkeys(arrInd)
591 and NAME = anames(arrInd)
592 returning item_key,name bulk collect into succItemUpdates, succAttrUpdates;
593
594 exception
595 when others then
596 if sql%bulk_exceptions.count>0 then
597 for ErrorIndex in 1..sql%bulk_exceptions.count loop
598 g_failedItems(g_FailedItems.COUNT+1):=
599 itemkeys(sql%bulk_exceptions(ErrorIndex).error_index);
600 g_failedAttributes(g_FailedAttributes.COUNT+1):=
601 anames(sql%bulk_exceptions(ErrorIndex).error_index);
602 end loop;
603 end if;
604 end;
605
606 if (succItemUpdates.count <> itemkeys.COUNT) then
607 -- determine the failed item/atrributes
608 if succItemUpdates.count=0 then
609 g_failedItems:=itemkeys;
610 g_failedAttributes:=anames;
611 else
612
613 for i in itemkeys.first..itemkeys.last loop
614
615 if ((itemkeys.count - i + 1) = succItemUpdates.count) then
616 -- we are done, the rest are all successful
617 exit;
618 end if;
619 match := false;
620 j:=succItemUpdates.first;
621 while j <= succItemUpdates.last loop
622 if anames(i) = succAttrUpdates(j)
623 and itemkeys(i) = succItemUpdates(j) then
624
625 -- remove the item/attr from the success table
626 succItemUpdates.delete(j);
627 succAttrUpdates.delete(j);
628 match := true;
629 exit;
630 end if;
631
632 j:=succItemUpdates.next(j);
633
634 end loop;
635
636 if not(match) then
637 -- item has failed.Insert into list of failed items
638 g_failedItems(g_FailedItems.COUNT+1):=itemkeys(i);
639 g_failedAttributes(g_FailedAttributes.COUNT+1):=anames(i);
640 end if;
641 end loop;
642 end if; --successUpdates count=0
643 end if; --sucessUpdates doesnot match item count
644
645 if g_failedItems.COUNT >0 then
646
647 WF_CORE.TOKEN('ITEMTYPE',itemtype);
648 WF_CORE.TOKEN('TOTAL',to_char(itemkeys.COUNT));
649 WF_CORE.TOKEN('FAILED',to_char(g_FailedItems.COUNT));
650 WF_CORE.Raise('WFENG_BULK_SETATTR');
651 end if;
652
653 exception
654 when others then
655 Wf_Core.Context('Wf_Engine_Bulk', 'SetItemAttrNumber', itemtype);
656 raise;
657 end SetItemAttrNumber;
658
659 --
660 -- SetItemAttrDate (PUBLIC)
661 -- Set the value of an array of date item attribute.
662 -- Attribute must be a DATE-type attribute.
663 -- IN:
664 -- itemtype - Item type
665 -- itemkey - Item key List
666 -- aname - Array of Name
667 -- avalue - Array of new value for attribute
668 --
669 procedure SetItemAttrDate(
670 itemtype in varchar2,
671 itemkeys in Wf_Engine_Bulk.ItemKeyTabType,
672 anames in Wf_Engine.NameTabTyp,
673 avalues in Wf_Engine.DateTabTyp)
674 is
675 status pls_integer;
676
677 wiavIND number;
678 success_cnt number;
679
680 j pls_integer;
681 match boolean;
682 succAttrUpdates Wf_Engine.NameTabTyp;
683 succItemUpdates Wf_Engine_Bulk.ItemKeyTabType;
684
685 begin
686
687 -- Check Arguments
688 if (itemtype is null) then
689 Wf_Core.Token('ITEMTYPE', nvl(itemtype, 'NULL'));
690 Wf_Core.Raise('WFSQL_ARGS');
691
692 elsif (anames.COUNT = 0 or avalues.COUNT = 0) then
693 -- Do not do anything if index table is empty.
694 return;
695
696 elsif ((anames.LAST <> avalues.LAST or anames.COUNT <> avalues.COUNT)
697 or (anames.LAST<> itemkeys.LAST or anames.COUNT<> itemkeys.COUNT)) then
698 -- Raise an error if the two index tables do not end at the same index
699 -- or do not have the same number of elements.
700 g_FailedItems:=itemkeys;
701 g_FailedAttributes:=anames;
702 Wf_Core.Raise('WFENG_BLK_ITM_ATTRARR_MISMATCH');
703 end if;
704 -- Reset the table of Failed Items and Failed Attributes
705 g_FailedItems.DELETE;
706 g_FailedAttributes.DELETE;
707
708 --If itemtype is on demand, we will insert default values if not exists already
709 if wf_item.Attribute_On_Demand(itemtype, itemkeys(itemkeys.FIRST)) then
710 begin
711 forall arrayIndex in itemkeys.FIRST..itemkeys.LAST save exceptions
712 insert into WF_ITEM_ATTRIBUTE_VALUES
713 (ITEM_TYPE, ITEM_KEY, NAME, DATE_VALUE)
714 select itemtype, itemkeys(arrayIndex), anames(arrayIndex), Date_default
715 from WF_ITEM_ATTRIBUTES WIA
716 where
717 WIA.ITEM_TYPE = itemtype
718 and
719 WIA.NAME = anames(arrayIndex)
720 and not exists (select 1 from WF_ITEM_ATTRIBUTE_VALUES WIAV
721 where WIAV.item_type=itemtype
722 and WIAV.item_key=itemkeys(arrayIndex)
723 and WIAV.NAME=anames(arrayIndex));
724 exception
725 when others then
726 null; -- ignore failures here
727 end;
728 end if;
729 -- Set the date value.
730 begin
731 forall arrInd in itemkeys.FIRST..itemkeys.LAST save exceptions
732 update WF_ITEM_ATTRIBUTE_VALUES set
733 DATE_VALUE = avalues(arrInd)
734 where ITEM_TYPE = itemtype
735 and ITEM_KEY = itemkeys(arrInd)
736 and NAME = anames(arrInd)
737 returning item_key,name bulk collect into succItemUpdates, succAttrUpdates;
738
739 exception
740 when others then
741 if sql%bulk_exceptions.count>0 then
742 for ErrorIndex in 1..sql%bulk_exceptions.count loop
743 g_failedItems(g_FailedItems.COUNT+1):=
744 itemkeys(sql%bulk_exceptions(ErrorIndex).error_index);
745 g_failedAttributes(g_FailedAttributes.COUNT+1):=
746 anames(sql%bulk_exceptions(ErrorIndex).error_index);
747 end loop;
748 end if;
749 end;
750
751 if (succItemUpdates.count <> itemkeys.COUNT) then
752 -- determine the failed item/atrributes
753 if succItemUpdates.count=0 then
754 g_failedItems:=itemkeys;
755 g_failedAttributes:=anames;
756 else
757
758 for i in itemkeys.first..itemkeys.last loop
759
760 if ((itemkeys.count - i + 1) = succItemUpdates.count) then
761 -- we are done, the rest are all successful
762 exit;
763 end if;
764 match := false;
765 j:=succItemUpdates.first;
766 while j <= succItemUpdates.last loop
767 if anames(i) = succAttrUpdates(j)
768 and itemkeys(i) = succItemUpdates(j) then
769
770 -- remove the item/attr from the success table
771 succItemUpdates.delete(j);
772 succAttrUpdates.delete(j);
773 match := true;
774 exit;
775 end if;
776
777 j:=succItemUpdates.next(j);
778
779 end loop;
780
781 if not(match) then
782 -- item has failed.Insert into list of failed items
783 g_failedItems(g_FailedItems.COUNT+1):=itemkeys(i);
784 g_failedAttributes(g_FailedAttributes.COUNT+1):=anames(i);
785 end if;
786 end loop;
787 end if; --successUpdates count=0
788 end if; --sucessUpdates doesnot match item count
789
790 if g_failedItems.COUNT >0 then
791 WF_CORE.TOKEN('ITEMTYPE',itemtype);
792 WF_CORE.TOKEN('TOTAL',to_char(itemkeys.COUNT));
793 WF_CORE.TOKEN('FAILED',to_char(g_FailedItems.COUNT));
794 WF_CORE.Raise('WFENG_BULK_SETATTR');
795 end if;
796 exception
797 when others then
798 Wf_Core.Context('Wf_Engine_Bulk', 'SetItemAttrDate', itemtype);
799 raise;
800 end SetItemAttrDate;
801
802 -- BulkCreateProcess (PUBLIC)
803 -- Create a new runtime process for a given list of itemkeys
804 -- (for an application itemtype).
805 -- IN
806 -- itemtype - A valid item type
807 -- itemkeys - A list of itemkeys generated from the application
808 -- object's primary key.
809 -- process - A valid root process for this item type
810 -- (or null to use the item's selector function)
811 -- user_keys - A list of userkeys bearing one to one
812 -- correspondence with the item ley list
813 -- owner_roles - A list of ownerroles bearing one-to-one
814 -- correspondence with the item key list
815 procedure CreateProcess(itemtype in varchar2,
816 itemkeys in wf_engine_bulk.itemkeytabtype,
817 process in varchar2,
818 user_keys in wf_engine_bulk.userkeytabtype,
819 owner_roles in wf_engine_bulk.ownerroletabtype,
820 parent_itemtype in varchar2,
821 parent_itemkey in varchar2,
822 parent_context in varchar2,
823 masterdetail in boolean )
824 is
825 root varchar2(30);
826 version number;
827 actdate date;
828 typ varchar2(8);
829 rootid pls_integer;
830 status varchar2(8);
831
832 foundDuplicate boolean :=FALSE;
833
834
835 l_itemkeys wf_engine_bulk.itemkeytabtype;
836 l_user_keys wf_engine_bulk.userkeytabtype;
837 l_owner_roles wf_engine_bulk.ownerroletabtype;
838 mon_random wf_engine.textTabTyp;
839 acc_random wf_engine.textTabTyp;
840 schemaAttribute varchar2(30);
841 l_rkeys wf_engine_bulk.ownerroleTabType;
842 l_ukeys wf_engine_bulk.userkeyTabType;
843 l_count number;
844
845 begin
846 -- Argument validation
847 if (itemtype is null) then
848 Wf_Core.Token('ITEMTYPE', itemtype);
849 Wf_Core.Token('PROCESS', process);
850 Wf_Core.Raise('WFSQL_ARGS');
851 end if;
852 l_itemkeys:= itemkeys;
853 l_user_keys:= user_keys;
854 l_owner_roles:= owner_roles;
855
856 -- check whether the user_keys and owner_roles arrays have been passed
857 -- or not. If not , we would need to intialize them to match the itemkey list
858 -- this needs to be done so that Bulk Inserts later donot fail.
859
860
861
862 --<rwunderl:4198524>
863 if (WF_CACHE.MetaRefreshed) then
864 null;
865 end if;
866 -- Reset the table of failed items and successful items
867 g_FailedItems.DELETE;
868 g_SuccessItems.DELETE;
869
870 if (process is null) then
871 -- Call the selector function to get the process. The process
872 -- retrieved for the first itemkey in the list, is assumed to
873 -- hold for all the itemkeys in the list.
874
875 root := Wf_Engine_Util.Get_Root_Process(itemtype, l_itemkeys(l_itemkeys.FIRST));
876 if (root is null) then
877
878 g_FailedItems:=l_itemkeys;
879
880 Wf_Core.Token('TYPE', itemtype);
881 Wf_Core.Token('KEY', l_itemkeys.FIRST);
882 Wf_Core.Raise('WFENG_ITEM_ROOT_SELECTOR');
883 end if;
884 else
885 root := process;
886 end if;
887
888 -- Check that the root argument is a valid process.
889 -- NOTE: The check that the process exists must be done BEFORE
890 -- calling create_item to avoid foreign key problems during the insert.
891 -- The check that the process is runnable can't be done until AFTER
892 -- create_item so the date has been established.
893 actdate := sysdate;
894 typ := Wf_Activity.Type(itemtype, root, actdate);
895 if ((typ is null) or (typ <> wf_engine.eng_process)) then
896
897 g_FailedItems:=l_itemkeys;
898
899 Wf_Core.Token('TYPE', itemtype);
900 Wf_Core.Token('NAME', root);
901 Wf_Core.Raise('WFENG_PROCESS_NAME');
902 end if;
903
904 -- Validate the root argument is runnable
905
906 begin
907 select INSTANCE_ID
908 into rootid
909 from WF_PROCESS_ACTIVITIES PA, WF_ACTIVITIES A
910 where A.ITEM_TYPE = itemtype
911 and A.NAME = 'ROOT'
912 and actdate >= A.BEGIN_DATE
913 and actdate < NVL(A.END_DATE, actdate+1)
914 and PA.PROCESS_NAME = 'ROOT'
915 and PA.PROCESS_ITEM_TYPE = itemtype
916 and PA.PROCESS_VERSION = A.VERSION
917 and PA.INSTANCE_LABEL = root;
918
919 exception
920 when no_data_found then
921 g_failedItems:=l_itemkeys;
922
923 Wf_Core.Token('TYPE', itemtype);
924 Wf_Core.Token('NAME', root);
925 Wf_Core.Raise('WFENG_PROCESS_RUNNABLE');
926 end ;
927 -- Check for the length of the parent_context if it is provided
928 if (parent_context is NOT null and (length(parent_context) > 25)) then
929 g_failedItems:=l_itemkeys;
930 WF_CORE.Token('LABEL', parent_context);
931 WF_CORE.Token('LENGTH', '25');
932 WF_CORE.Raise('WFENG_LABEL_TOO_LARGE');
933 end if;
934 -- Check for duplicate item. Not needed as BulkCreateItems would validate this
935
936 -- Clear plsql cache first, just in case previous
937 -- item was purged/rolled back, then check for duplicate.
938 /* Wf_Item.ClearCache;
939 -- loop through all itemkeys in the list to check for duplicate
940 for arrInd in itemkeys.FIRST..itemkeys.LAST loop
941 if (Wf_Item.Item_Exist(itemtype, itemkeys(arrInd))) then
942 foundDuplicate:=TRUE;
943 g_FailedItems(g_FailedItems.COUNT+1):=itemkeys(arrInd);
944 itemkeys.delete(arrInd);
945 user_keys.delete(arrInd);
946 owner_roles.delete(arrInd);
947 end if;
948 end loop;
949 if foundDuplicate then -- we need to condense the itemkeys and the userkeys,ownerroles etc
950 ConsolidateKeys(ItemKeys,User_Keys,Owner_Roles);
951 end if;*/
952
953 g_SuccessItems:=l_ItemKeys;
954 -- Bulk Insert rows in items table
955 Wf_Engine_Bulk.BulkCreateItems(itemtype, root, actdate,l_user_keys,
956 l_owner_roles, parent_itemtype, parent_itemkey,parent_context);
957
958
959 -- Build the array of random numbers for monitor and access key attributes
960 schemaAttribute:=Wf_Engine_Bulk.Current_Schema;
961 if g_successItems.count>0 then
962 for arrInd in g_SuccessItems.first..g_SuccessItems.last loop
963 mon_random(arrInd) := Wf_Core.Random;
964 acc_random(arrInd) := Wf_Core.Random;
965
966 end loop;
967 end if;
968 -- Create monitor access key attributes
969 Wf_Engine_Bulk.BulkAddItemAttr(itemtype, wf_engine.wfmon_mon_key,
970 mon_random);
971 Wf_Engine_Bulk.BulkAddItemAttr(itemtype, wf_engine.wfmon_acc_key,
972 acc_random);
973
974 l_itemkeys:=g_SuccessItems;
975 -- Create a schema attribute across the itemkeys
976 if l_itemkeys.count>0 then
977 begin
978 forall arrInd in l_itemkeys.FIRST..l_itemkeys.LAST SAVE EXCEPTIONS
979 insert into WF_ITEM_ATTRIBUTE_VALUES (
980 ITEM_TYPE,
981 ITEM_KEY,
982 NAME,
983 TEXT_VALUE
984 ) values (
985 itemtype,
986 l_itemkeys(arrInd),
987 wf_engine.eng_schema,
988 schemaAttribute
989 );
990
991 exception
992 when others then
993 if SQL%BULK_EXCEPTIONS.COUNT>0 then
994
995 -- load the failedItems table with the list of errored itemkeys,
996 -- and remove them from the itemkeys table
997 for failIndex in 1..SQL%BULK_EXCEPTIONS.COUNT loop
998 g_FailedItems(g_FailedItems.COUNT+1):=
999 l_itemkeys(SQL%BULK_EXCEPTIONS(failIndex).ERROR_INDEX);
1000 l_itemkeys.DELETE(SQL%BULK_EXCEPTIONS(failIndex).ERROR_INDEX);
1001 end loop;
1002 ConsolidateKeys(l_itemkeys,l_ukeys,l_rkeys);
1003 end if;
1004 end;
1005 end if;
1006 g_SuccessItems:=l_itemkeys;
1007
1008 if (l_itemkeys.count > 0 )
1009 and (parent_itemtype is not null )
1010 and (parent_itemkey is not null) then
1011 --Setting the parent information
1012 if (masterdetail) then
1013 --Increment #WAITFORDETAIL master counter if it exists.
1014 if (WF_ENGINE.AddToItemAttrNumber(parent_itemtype, parent_itemkey,
1015 '#WAITFORDETAIL', l_itemkeys.count) is NOT NULL) then
1016 if (parent_context is NOT null) then
1017 --Increment/Create label counter.
1018 if (WF_ENGINE.AddToItemAttrNumber(parent_itemType, parent_itemKey,
1019 '#CNT_'||parent_context, l_itemkeys.count)
1020 is NULL) then
1021 WF_ENGINE.AddItemAttr(itemType=>parent_itemType,
1022 itemKey=>parent_itemKey,
1023 aname=>'#CNT_'||parent_context,
1024 number_value=>l_itemkeys.count);
1025 end if; --Label Counter exists
1026 begin
1027 forall arrInd in l_itemkeys.FIRST..l_itemkeys.LAST SAVE EXCEPTIONS
1028 insert into WF_ITEM_ATTRIBUTE_VALUES (
1029 ITEM_TYPE,
1030 ITEM_KEY,
1031 NAME,
1032 TEXT_VALUE
1033 ) values (
1034 itemtype,
1035 l_itemkeys(arrInd),
1036 '#LBL_'||parent_context,
1037 parent_context
1038 );
1039
1040 exception
1041 when others then
1042 if SQL%BULK_EXCEPTIONS.COUNT>0 then
1043
1044 -- load the failedItems table with the list of errored itemkeys,
1045 -- and remove them from the itemkeys table
1046 for failIndex in 1..SQL%BULK_EXCEPTIONS.COUNT loop
1047 g_FailedItems(g_FailedItems.COUNT+1):=
1048 l_itemkeys(SQL%BULK_EXCEPTIONS(failIndex).ERROR_INDEX);
1049 l_itemkeys.DELETE(SQL%BULK_EXCEPTIONS(failIndex).ERROR_INDEX);
1050 end loop;
1051 ConsolidateKeys(l_itemkeys,l_ukeys,l_rkeys);
1052 end if;
1053 end;
1054 else
1055 -- Parent context is null
1056 -- increase all known #CNT counter by the number of itemkeys
1057 l_count:= l_itemkeys.count;
1058 update WF_ITEM_ATTRIBUTE_VALUES
1059 set NUMBER_VALUE = NUMBER_VALUE + l_count
1060 where NAME like '#CNT_%'
1061 and NUMBER_VALUE is not null
1062 and ITEM_TYPE = parent_itemType
1063 and ITEM_KEY = parent_itemKey;
1064 end if; --Parent context is not null
1065 end if; --#WAITFORDETAIL exists
1066 end if; --Caller is signalling that this "should" be a coordinated flow.
1067 end if;
1068
1069 g_successItems:=l_itemKeys;
1070 --finally raise exception if the FailedItems table is non-empty
1071 if g_FailedItems.COUNT>0 then
1072 WF_CORE.Token('TYPE',itemtype);
1073 WF_CORE.Token('FAILED',to_char(g_FailedItems.count));
1074 WF_CORE.TOKEN('TOTAL', to_char(itemkeys.COUNT));
1075 WF_CORE.RAISE('WFENG_BULK_OPER');
1076 end if;
1077
1078 exception
1079 when others then
1080 Wf_Core.Context('Wf_Engine_Bulk', 'CreateProcess', itemtype, process);
1081 raise;
1082 end CreateProcess;
1083
1084 ----------------------------------------------------------------
1085
1086 --
1087 -- BulkStartProcess (PUBLIC)
1088 -- Begins execution of the process.It identifies the start activities
1089 -- for the run-time process and launches them in bulk for all the item keys
1090 -- in the list, under the given itemtype.
1091 -- IN
1092 -- itemtype - A valid item type
1093 -- itemkeys - A list of itemkeys generated from the application object's
1094 -- primary key.
1095 --
1096 procedure StartProcess(itemtype in varchar2,
1097 itemkeys in wf_engine_bulk.itemkeytabtype)
1098 is
1099
1100
1101 -- Select all the start activities in this parent process with
1102 -- no in-transitions.
1103 cursor starter_children (itemtype in varchar2,
1104 process in varchar2,
1105 version in number) is
1106 SELECT PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION,
1107 ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, INSTANCE_ID,
1108 INSTANCE_LABEL, PERFORM_ROLE, PERFORM_ROLE_TYPE,
1109 START_END, DEFAULT_RESULT
1110 FROM WF_PROCESS_ACTIVITIES WPA
1111 WHERE WPA.PROCESS_ITEM_TYPE = itemtype
1112 AND WPA.PROCESS_NAME = process
1113 AND WPA.PROCESS_VERSION = version
1114 AND WPA.START_END = wf_engine.eng_start
1115 AND NOT EXISTS (
1116 SELECT NULL
1117 FROM WF_ACTIVITY_TRANSITIONS WAT
1118 WHERE WAT.TO_PROCESS_ACTIVITY = WPA.INSTANCE_ID);
1119
1120 TYPE DateTabType is table of DATE index by binary_integer;
1121 TYPE NumTabType is table of NUMBER index by binary_integer;
1122 TYPE RawTabType is table of RAW(16) index by binary_integer;
1123 type InstanceArrayTyp is table of pls_integer index by binary_integer;
1124
1125 childarr InstanceArrayTyp; -- Place holder for all the instance id
1126 -- selected from starter_children cursor
1127 i pls_integer := 0; -- Counter for the for loop
1128 process varchar2(30) := ''; -- root process activity name
1129 version pls_integer; -- root process activity version
1130 processid pls_integer;
1131 actdate date;
1132 rerun varchar2(8); -- Activity rerun flag
1133 acttype varchar2(8); -- Activity type
1134 cost number; -- Activity cost
1135 ftype varchar2(30); -- Activity function type
1136 defer_mode boolean := FALSE;
1137
1138 TransitionCount pls_integer := 0;
1139 l_baseLnk NUMBER;
1140 l_prevLnk NUMBER;
1141 psaIND NUMBER;
1142 l_linkCollision BOOLEAN;
1143 status PLS_INTEGER;
1144
1145 root varchar2(30); -- Root process of activity
1146
1147 rootid pls_integer; -- Id of root process
1148 act_fname varchar2(240);
1149 act_ftype varchar2(30);
1150 delay number; -- dont use pls_integer or numeric overflow can occur.
1151 msg_id raw(16):=null;
1152 l_result number;
1153
1154 -- Timeout processing stuff
1155 duedate date;
1156 timeout number;
1157 msg varchar2(30);
1158 msgtype varchar2(8);
1159 expand_role varchar2(8);
1160
1161 duedateTab DateTabType;
1162 execCountTab numTabType;
1163 msgIdTab RawTabType;
1164 l_itemkeys wf_engine_bulk.itemkeytabtype;
1165 l_rkeys wf_engine_bulk.ownerroleTabType;
1166 l_ukeys wf_engine_bulk.userkeyTabType;
1167 begin
1168 -- Check if the item exists and also get back the root process name
1169 -- and version. We assume that the process and version are identical
1170 -- across the itemkeys in the list so that they need to be retieved
1171 -- only for the first itemkey in the list.
1172
1173 l_itemkeys:=itemkeys;
1174
1175 --reset the tables of successful and failed items
1176 g_failedItems.DELETE;
1177 g_successItems.DELETE;
1178 begin
1179 select WI.ROOT_ACTIVITY, WI.ROOT_ACTIVITY_VERSION
1180 into process,version
1181 from WF_ITEMS WI
1182 where WI.ITEM_TYPE = itemtype
1183 and WI.ITEM_KEY = l_itemkeys(1);
1184 exception
1185 when no_Data_found then
1186 g_FailedItems:=l_itemkeys;
1187 Wf_Core.Token('TYPE', itemtype);
1188 Wf_Core.Token('KEY', l_itemkeys.FIRST);
1189 Wf_Core.Raise('WFENG_ITEM');
1190
1191 end;
1192
1193 -- Get the id of the process root.
1194 processid := Wf_Process_Activity.RootInstanceId(itemtype, l_itemkeys(l_itemkeys.FIRST),
1195 process);
1196 if (processid is null) then
1197
1198 g_FailedItems:=itemkeys;
1199
1200 Wf_Core.Token('TYPE', itemtype);
1201 Wf_Core.Token('NAME', process);
1202 Wf_Core.Raise('WFENG_PROCESS_RUNNABLE');
1203 end if;
1204
1205
1206 --increment the execution time counter value
1207 g_execCount:=g_execCount+1;
1208
1209 -- bulk insert into WF_ITEM_ACTIVITY_STATUSES
1210 begin
1211 forall arrInd in l_itemkeys.FIRST..l_itemkeys.LAST save exceptions
1212 insert
1213 into WF_ITEM_ACTIVITY_STATUSES (
1214 ITEM_TYPE,
1215 ITEM_KEY,
1216 PROCESS_ACTIVITY,
1217 ACTIVITY_STATUS,
1218 ACTIVITY_RESULT_CODE,
1219 ASSIGNED_USER,
1220 NOTIFICATION_ID,
1221 BEGIN_DATE,
1222 END_DATE,
1223 DUE_DATE,
1224 EXECUTION_TIME,
1225 OUTBOUND_QUEUE_ID
1226 ) values (
1227 itemtype,
1228 l_itemkeys(arrInd),
1229 processid,
1230 wf_engine.eng_active,
1231 wf_engine.eng_null,
1232 null,
1233 null,
1234 SYSDATE,
1235 null,
1236 null,
1237 g_execCount,
1238 null
1239 );
1240 exception
1241 when others then
1242 if sql%bulk_exceptions.count > 0 then
1243 for arrIndex in 1.. sql%bulk_exceptions.count loop
1244
1245 g_failedItems(g_failedItems.COUNT+1) := l_itemkeys(sql%bulk_exceptions
1246 (arrIndex).ERROR_INDEX);
1247 l_itemkeys.DELETE(sql%bulk_exceptions(arrIndex).ERROR_INDEX);
1248 end loop;
1249 end if;
1250 end;
1251
1252 if g_failedItems.COUNT>0 then
1253 ConsolidateKeys(l_itemkeys,l_ukeys,l_rkeys);
1254 end if;
1255
1256
1257 -- Retrieve the starting activities from cache.
1258 WF_CACHE.GetProcessStartActivities(itemType=>itemtype,
1259 name=>process,
1260 version=>version,
1261 status=>status,
1262 psaIND=>psaIND);
1263
1264 if (status <> WF_CACHE.task_SUCCESS) then
1265 -- Starting activities are not in cache, so we will store them using a for
1266 -- loop to get all the next transition activities.
1267 -- Then we will access the list from cache to avoid maximum open cursor
1268 -- problem. First we need to retain the base index to be used later.
1269 l_baseLnk := psaIND;
1270 l_linkCollision := FALSE;
1271 for child in starter_children(itemtype, process, version) loop
1272 if (TransitionCount > 0) then --Second and succeeding iterations
1273 --We will locally store the record index from the last loop iteration.
1274 l_prevLnk := psaIND;
1275 --We will now generate an index for the start activity from the
1276 --itemType, name, version, and the current INSTANCE_ID
1277 psaIND := WF_CACHE.HashKey(itemType||':'||process||':'||version||
1278 ':'||WF_CACHE.ProcessStartActivities(psaIND).INSTANCE_ID);
1279
1280 --Check to make sure a record is not already here.
1281 if (WF_CACHE.ProcessStartActivities.EXISTS(psaIND)) then
1282 l_linkCollision := TRUE; --There should be no record here, so this
1283 --is a hash collision. We will continue
1284 --populating this linked list, but after
1285 --we use it, we will clear the pl/sql table
1286 end if;
1287
1288 --Now the PL/SQL table index has moved to the next link, so we will
1289 --populate the prev_lnk with our locally stored index. This feature,
1290 --not yet used, allows us to traverse backwards through the link list
1291 --if needed. Since it is not yet used, it is commented out.
1292 --WF_CACHE.ProcessStartActivities(psaIND).PREV_LNK := l_prevLnk;
1293
1294 --l_prevLnk represents the index of the previous record, and we need
1295 --to update its NEXT_LNK field with the current index.
1296 WF_CACHE.ProcessStartActivities(l_prevLnk).NEXT_LNK := psaIND;
1297 --else
1298 -- WF_CACHE.ProcessStartActivities(psaIND).PREV_LNK := -1;
1299
1300 end if;
1301
1302 WF_CACHE.ProcessStartActivities(psaIND).PROCESS_ITEM_TYPE :=
1303 child.PROCESS_ITEM_TYPE;
1304
1305 WF_CACHE.ProcessStartActivities(psaIND).PROCESS_NAME :=
1306 child.PROCESS_NAME;
1307
1308 WF_CACHE.ProcessStartActivities(psaIND).PROCESS_VERSION :=
1309 child.PROCESS_VERSION;
1310
1311 WF_CACHE.ProcessStartActivities(psaIND).INSTANCE_ID := child.INSTANCE_ID;
1312
1313 --While we are here, we can populate the ProcessActivities cache hoping
1314 --that a later request of any of these process activities will save us
1315 --another trip to the DB.
1316 WF_CACHE.ProcessActivities(child.INSTANCE_ID).PROCESS_ITEM_TYPE :=
1317 child.PROCESS_ITEM_TYPE;
1318 WF_CACHE.ProcessActivities(child.INSTANCE_ID).PROCESS_NAME :=
1319 child.PROCESS_NAME;
1320 WF_CACHE.ProcessActivities(child.INSTANCE_ID).PROCESS_VERSION :=
1321 child.PROCESS_VERSION;
1322 WF_CACHE.ProcessActivities(child.INSTANCE_ID).ACTIVITY_ITEM_TYPE :=
1323 child.ACTIVITY_ITEM_TYPE;
1324 WF_CACHE.ProcessActivities(child.INSTANCE_ID).ACTIVITY_NAME :=
1325 child.ACTIVITY_NAME;
1326 WF_CACHE.ProcessActivities(child.INSTANCE_ID).INSTANCE_ID :=
1327 child.INSTANCE_ID;
1328 WF_CACHE.ProcessActivities(child.INSTANCE_ID).INSTANCE_LABEL :=
1329 child.INSTANCE_LABEL;
1330 WF_CACHE.ProcessActivities(child.INSTANCE_ID).PERFORM_ROLE :=
1331 child.PERFORM_ROLE;
1332 WF_CACHE.ProcessActivities(child.INSTANCE_ID).PERFORM_ROLE_TYPE :=
1333 child.PERFORM_ROLE_TYPE;
1334 WF_CACHE.ProcessActivities(child.INSTANCE_ID).START_END :=
1335 child.START_END;
1336 WF_CACHE.ProcessActivities(child.INSTANCE_ID).DEFAULT_RESULT :=
1337 child.DEFAULT_RESULT;
1338
1339 TransitionCount := TransitionCount+1;
1340 end loop;
1341 WF_CACHE.ProcessStartActivities(psaIND).NEXT_LNK := -1;
1342 psaIND := l_baseLnk; --Reset the index back to the beginning.
1343 status := WF_CACHE.task_SUCCESS; --We now have the records successfully
1344 --in cache.
1345
1346 end if;
1347
1348 -- Load a local InstanceArrayTyp, we do this because of the recursion that
1349 -- occurs. Since the ProcessStartActivities Cache is global, any
1350 -- hashCollision would clear the cache and could cause problems as we
1351 -- process activities in recursive calls.
1352 while (psaIND <> -1) loop
1353 childarr(i) := WF_CACHE.ProcessStartActivities(psaIND).INSTANCE_ID;
1354 i := i+1;
1355 psaIND := WF_CACHE.ProcessStartActivities(psaIND).NEXT_LNK;
1356 end loop;
1357 childarr(i) := '';
1358
1359 if (l_linkCollision) then
1360 --When populating the linked list, we discovered that a hash collision
1361 --caused us to overwrite a link belonging to another list. This would
1362 --cause the other list to be incorrect. We will clear the table so the
1363 --lists will be rebuilt after this transaction.
1364 WF_CACHE.ProcessStartActivities.DELETE;
1365
1366 end if;
1367
1368
1369 i:=0;
1370
1371 -- for each child activity, do a bulk defer of all the itemkeys in the list
1372
1373 while (childarr(i) is not null) loop
1374
1375 begin
1376
1377
1378 -- calculate the activity function name and type
1379 act_fname:= Wf_Activity.activity_function(itemtype,l_itemkeys(l_itemkeys.FIRST),childarr(i));
1380 act_ftype:= Wf_Activity.activity_function_type(itemtype,l_itemkeys(l_itemkeys.FIRST),childarr(i));
1381
1382
1383 --depending on the activity function, enqueue on the proper queue.
1384
1385 if l_itemkeys.COUNT <=0 then
1386 WF_CORE.Token('TYPE',itemtype);
1387 WF_CORE.Token('FAILED',to_char(g_FailedItems.count));
1388 WF_CORE.TOKEN('TOTAL',to_char(itemkeys.COUNT));
1389 WF_CORE.RAISE('WFENG_BULK_OPER');
1390 exit;
1391 end if;
1392
1393 --reset the duedate, msgid tables
1394
1395 dueDateTab.delete;
1396
1397 msgIdTab.delete;
1398 -- increment the execution time counter
1399 g_execCount:=g_execCount+1;
1400
1401 for arrInd in l_itemkeys.FIRST..l_itemkeys.LAST loop
1402 begin
1403 -- 1. Look first for a '#TIMEOUT' NUMBER attribute
1404 timeout := Wf_Engine.GetActivityAttrNumber(itemtype, l_itemkeys(arrInd),childarr(i),
1405 wf_engine.eng_timeout_attr,
1406 ignore_notfound=>TRUE);
1407
1408 if (nvl(timeout, 0) <> 0) then
1409 -- Figure duedate as offset from begin time.
1410 -- NOTE: Default timeout is in units of minutes, not days like
1411 -- all other 'date as number' values, thus the 1440 fudge factor.
1412 duedate:= SYSDATE + (timeout / 1440);
1413 else
1414 -- 2. Look for a '#TIMEOUT' DATE attribute
1415 duedate := Wf_Engine.GetActivityAttrDate(itemtype, l_itemkeys(arrInd),
1416 childarr(i), wf_engine.eng_timeout_attr,
1417 ignore_notfound=>TRUE);
1418 end if;
1419 exception
1420 when others then
1421 if (wf_core.error_name = 'WFENG_ACTIVITY_ATTR') then
1422 -- No #TIMEOUT attr means no timeout
1423 wf_core.clear;
1424 duedate:= null;
1425 end if;
1426 end;
1427
1428 -- depending on the activity function type enqueue on the appropriate queue
1429
1430 begin
1431
1432 delay:=0;
1433
1434 if act_ftype = 'PL/SQL' then
1435
1436 wf_queue.enqueue_event
1437 (queuename=>wf_queue.DeferredQueue,
1438 itemtype=> itemtype,
1439 itemkey=>l_itemkeys(arrInd),
1440 actid=>childarr(i),
1441 delay=>delay,
1442 message_handle=>msg_id);
1443 -- even when internal, keep message for cross reference.
1444 -- msg_id :=null;
1445
1446
1447
1448 elsif act_ftype = 'EXTERNAL' then
1449 -- this is a callout so write to OUTBOUND queue
1450 -- do not set the correlation here for compatibility reason
1451
1452
1453 wf_queue.enqueue_event
1454 (queuename=>wf_queue.OutboundQueue,
1455 itemtype=>itemtype,
1456 itemkey=>l_itemkeys(arrInd),
1457 actid=>childarr(i),
1458 funcname=>act_fname,
1459 paramlist=>wf_queue.get_param_list
1460 (itemtype,l_itemkeys(arrInd),childarr(i)),
1461 message_handle=>msg_id);
1462
1463 else
1464 -- this is a callout so write to OUTBOUND queue for other type
1465
1466 wf_queue.enqueue_event
1467 (queuename=>wf_queue.OutboundQueue,
1468 itemtype=>itemtype,
1469 itemkey=>l_itemkeys(arrInd),
1470 actid=>childarr(i),
1471 correlation=>act_ftype,
1472 funcname=>act_fname,
1473 paramlist=>wf_queue.get_param_list
1474 (itemtype,l_itemkeys(arrInd),childarr(i)),
1475 message_handle=>msg_id);
1476
1477 end if;
1478 duedateTab(duedateTab.count+1):=duedate;
1479 msgIdTab(msgidtab.count+1):=msg_id;
1480 exception
1481 when others then
1482
1483 g_faileditems(g_faileditems.count+1):=l_itemkeys(arrInd);
1484 l_itemkeys.DELETE(arrInd);
1485 end;
1486 end loop;
1487 if g_failedItems.COUNT>0 then
1488 for arrInd in l_itemkeys.first..l_itemkeys.last loop
1489 if not l_itemkeys.exists(arrInd) then
1490
1491 --fill in the gap with the last list element
1492 l_itemkeys(arrInd):=l_itemkeys(l_itemkeys.last);
1493
1494 -- accordingly re-arrange the duedate,and msgid arrays
1495 -- so as not to lose one-to-one correspondence
1496 duedateTab(arrInd):=duedateTab(duedateTab.last);
1497 msgIdTab(arrInd):=msgIdTab(msgIdTab.last);
1498 l_itemkeys.delete(l_itemkeys.last);
1499 msgIdTab.delete(msgIdTab.last);
1500 duedateTab.delete(duedateTab.last);
1501 end if;
1502 end loop;
1503 end if;
1504
1505
1506 -- finally bulk insert the activity, with status as 'DEFERRED'
1507 -- for all the itemkeys.
1508 if l_itemkeys.count>0 then
1509 begin
1510 forall arrInd in l_itemkeys.FIRST..l_itemkeys.LAST save exceptions
1511 insert
1512 into WF_ITEM_ACTIVITY_STATUSES (
1513 ITEM_TYPE,
1514 ITEM_KEY,
1515 PROCESS_ACTIVITY,
1516 ACTIVITY_STATUS,
1517 ACTIVITY_RESULT_CODE,
1518 ASSIGNED_USER,
1519 NOTIFICATION_ID,
1520 BEGIN_DATE,
1521 END_DATE,
1522 DUE_DATE,
1523 EXECUTION_TIME,
1524 OUTBOUND_QUEUE_ID
1525 ) values (
1526 itemtype,
1527 l_itemkeys(arrInd),
1528 childarr(i),
1529 'DEFERRED',
1530 null,
1531 null,
1532 null,
1533 SYSDATE,
1534 null,
1535 duedateTab(arrInd),
1536 g_execCount,
1537 msgIdTab(arrInd)
1538 );
1539 exception
1540 when others then
1541 if sql%bulk_exceptions.count>0 then
1542 for failindex in 1.. sql%bulk_exceptions.count loop
1543
1544 g_failedItems(g_faileditems.count+1):=
1545 l_itemkeys( sql%bulk_exceptions(failindex).error_index);
1546 l_itemkeys.DELETE(sql%bulk_exceptions(failindex).error_index);
1547 end loop;
1548 ConsolidateKeys(l_itemkeys,l_ukeys,l_rkeys);
1549 end if;
1550 end;
1551 end if;
1552 end;
1553 i:= i+1;
1554 end loop;--child activities
1555
1556
1557 -- Report an error if no start activities can be found.
1558 if (i = 0) then
1559
1560 g_FailedItems:=itemkeys;
1561 Wf_Core.Token('PROCESS', process);
1562 Wf_Core.Raise('WFENG_NO_START');
1563 end if;
1564 g_successItems:=l_ItemKeys;
1565 -- Report Error if Failure has occured for some item keys
1566 if g_failedItems.COUNT>0 then
1567 WF_CORE.Token('TYPE',itemtype);
1568 WF_CORE.Token('FAILED',to_char(g_FailedItems.count));
1569 WF_CORE.TOKEN('TOTAL',to_char(g_successItems.COUNT));
1570 WF_CORE.RAISE('WFENG_BULK_OPER');
1571 end if;
1572 exception
1573 when others then
1574 Wf_Core.Context('Wf_Engine_Bulk','StartProcess',
1575 itemtype);
1576 raise;
1577 end StartProcess;
1578
1579 -- FastForward (PUBLIC)
1580 --
1581 --This API starts a specific activity for a list of items. This activity
1582 --must be marked as start, but does not need to be an activity without
1583 --any in transition.This API would fast forward the launch process by
1584 --bulk-creating the items, bulk initializing item attributes and bulk
1585 --starting a specified startactivity within the process across all the
1586 --itemkeys.The activity must be a direct child of the root process specified.
1587
1588 -- IN
1589 -- itemtype - A valid item type
1590 -- itemkeys - A list of itemkeys generated from the application object's
1591 -- primary key.
1592 -- process - The process to be started
1593 -- activity - The label of the specific activity within the process to be started.
1594 -- activityStatus - The status of the activity.This should be restricted to 'NOTIFIED'
1595 -- and 'DEFERRED' only.
1596
1597 procedure FastForward(itemtype in varchar2,
1598 itemkeys in wf_engine_bulk.itemkeytabtype,
1599 process in varchar2,
1600 activity in varchar2,
1601 activityStatus in varchar2,
1602 parent_itemtype in varchar2,
1603 parent_itemkey in varchar2,
1604 parent_context in varchar2,
1605 masterdetail in boolean)
1606 is
1607
1608
1609
1610 TYPE DateTabType is table of DATE index by binary_integer;
1611 TYPE NumTabType is table of NUMBER index by binary_integer;
1612 TYPE RawTabType is table of RAW(16) index by binary_integer;
1613
1614 i pls_integer := 0; -- Counter for the for loop
1615 version pls_integer; -- root process activity version
1616 processid pls_integer;
1617 actdate date;
1618 rerun varchar2(8); -- Activity rerun flag
1619 acttype varchar2(8); -- Activity type
1620 cost number; -- Activity cost
1621 ftype varchar2(30); -- Activity function type
1622 defer_mode boolean := FALSE;
1623 activity_date date;
1624
1625
1626 root varchar2(30); -- Root process of activity
1627 rootid pls_integer; -- Id of root process
1628 actid pls_integer; --Id of the activity to be fast-forwarded
1629 act_fname varchar2(240);
1630 act_ftype varchar2(30);
1631 delay number; -- dont use pls_integer or numeric overflow can occur.
1632 msg_id raw(16):=null;
1633 l_result number;
1634
1635 -- Timeout processing stuff
1636 duedate date;
1637 timeout number;
1638 msg varchar2(30);
1639 msgtype varchar2(8);
1640 expand_role varchar2(8);
1641
1642 duedateTab DateTabType;
1643 execCountTab numTabType;
1644 msgIdTab RawTabType;
1645 l_itemkeys wf_engine_bulk.itemKeyTabType;
1646 l_ukeys wf_engine_bulk.userkeyTabType;
1647 l_rkeys wf_engine_bulk.ownerroleTabType;
1648
1649 begin
1650
1651 -- Validate that the activityStatus is not other than 'DEFERRED' or 'NOTIFIED'
1652 if activityStatus is not null then
1653 if activityStatus <> 'DEFERRED' and activityStatus <> 'NOTIFIED' then
1654 --raise error
1655 g_failedItems:=itemkeys;
1656 WF_CORE.RAISE('WFENG_INVALID_ACT_STATUS');
1657 end if;
1658 end if;
1659
1660 -- initialize the itemkeys list
1661 l_itemkeys:=itemkeys;
1662 -- Call CreateProcess to Create the Process across all itemkeys
1663 begin
1664 WF_ENGINE_BULK.CreateProcess(itemtype,l_itemkeys,process,l_ukeys,l_rkeys,
1665 parent_itemtype,parent_itemkey,parent_context,masterdetail);
1666 exception
1667 when others then
1668
1669 if g_failedItems.COUNT>0 then
1670
1671 wf_core.clear;
1672 l_itemkeys:=g_SuccessItems;
1673 end if;
1674 end;
1675 if l_itemkeys.count<=0 then
1676 WF_CORE.Token('TYPE',itemtype);
1677 WF_CORE.Token('FAILED',to_char(itemkeys.count));
1678 WF_CORE.TOKEN('TOTAL', to_char(itemkeys.count));
1679 WF_CORE.RAISE('WFENG_BULK_OPER');
1680 end if;
1681 --clear the successful items table
1682 g_SuccessItems.DELETE;
1683
1684 -- Check if the item exists and also get back the root
1685 -- version. We assume that the process and version are identical
1686 -- across the itemkeys in the list so that they need to be retrieved
1687 -- only for the first itemkey in the list.
1688 begin
1689 select WI.ROOT_ACTIVITY_VERSION
1690 into version
1691 from WF_ITEMS WI
1692 where WI.ITEM_TYPE = itemtype
1693 and WI.ITEM_KEY = l_itemkeys(1)
1694 and WI.ROOT_ACTIVITY=process;
1695 exception
1696 when no_Data_found then
1697
1698 g_FailedItems:=l_itemkeys;
1699
1700 Wf_Core.Token('TYPE', itemtype);
1701 Wf_Core.Token('KEY', l_itemkeys.FIRST);
1702 Wf_Core.Raise('WFENG_ITEM');
1703 end;
1704
1705
1706 -- Get the id of the process root.
1707 processid := Wf_Process_Activity.RootInstanceId(itemtype, l_itemkeys(l_itemkeys.FIRST),
1708 process);
1709
1710 if (processid is null) then
1711
1712 g_FailedItems:=itemkeys;
1713
1714 Wf_Core.Token('TYPE', itemtype);
1715 Wf_Core.Token('NAME', process);
1716 Wf_Core.Raise('WFENG_PROCESS_RUNNABLE');
1717 end if;
1718
1719
1720
1721 -- determine the instance_id of the activity
1722 -- raise error if the activity is not found.
1723 begin
1724
1725 SELECT WPA.INSTANCE_ID
1726 into actid
1727 FROM WF_PROCESS_ACTIVITIES WPA
1728 WHERE WPA.PROCESS_ITEM_TYPE = itemtype
1729 AND WPA.PROCESS_NAME = process
1730 AND WPA.PROCESS_VERSION = version
1731 AND WPA.START_END = wf_engine.eng_start
1732 AND WPA.INSTANCE_LABEL=activity;
1733 Exception
1734 When no_data_found then
1735
1736 g_FailedItems:=itemkeys;
1737
1738 WF_CORE.Token('ACTIVITY',activity);
1739 WF_CORE.Token('ITEMTYPE',itemtype);
1740 WF_CORE.Token('PROCESS',process);
1741 WF_CORE.Raise('ACTIVITY_NOT_FOUND');
1742 End;
1743
1744 -- insert the process into the item activity statuses table
1745
1746 -- increment the execution time counter by 1.
1747 g_execCount:=g_execCount+1;
1748 -- bulk insert into WF_ITEM_ACTIVITY_STATUSES
1749 begin
1750 forall arrInd in l_itemkeys.FIRST..l_itemkeys.LAST save exceptions
1751 insert
1752 into WF_ITEM_ACTIVITY_STATUSES (
1753 ITEM_TYPE,
1754 ITEM_KEY,
1755 PROCESS_ACTIVITY,
1756 ACTIVITY_STATUS,
1757 ACTIVITY_RESULT_CODE,
1758 ASSIGNED_USER,
1759 NOTIFICATION_ID,
1760 BEGIN_DATE,
1761 END_DATE,
1762 DUE_DATE,
1763 EXECUTION_TIME,
1764 OUTBOUND_QUEUE_ID
1765 ) values (
1766 itemtype,
1767 l_itemkeys(arrInd),
1768 processid,
1769 wf_engine.eng_active,
1770 wf_engine.eng_null,
1771 null,
1772 null,
1773 SYSDATE,
1774 null,
1775 null,
1776 g_execCount,
1777 null
1778 );
1779 exception
1780 when others then
1781 if sql%bulk_exceptions.count > 0 then
1782 for arrIndex in 1.. sql%bulk_exceptions.count loop
1783
1784 g_failedItems(g_failedItems.COUNT+1) :=
1785 l_itemkeys(sql%bulk_exceptions(arrIndex).ERROR_INDEX);
1786 l_itemkeys.DELETE(sql%bulk_exceptions(arrIndex).ERROR_INDEX);
1787 end loop;
1788 end if;
1789 end;
1790 -- in case failed itemkeys have been removed
1791 -- condense the sparsely populated itemkey list
1792 if g_failedItems.COUNT>0 then
1793 ConsolidateKeys(l_itemkeys,l_ukeys,l_rkeys);
1794 end if;
1795
1796 -- defer and enqueue the given activity across all itemkeys
1797 begin
1798
1799 -- calculate the activity function name and type
1800 act_fname:= Wf_Activity.activity_function(itemtype,l_itemkeys(l_itemkeys.first),actid);
1801 act_ftype:= Wf_Activity.activity_function_type(itemtype,l_itemkeys(l_itemkeys.first),actid);
1802
1803
1804
1805 if l_itemkeys.COUNT <=0 then
1806
1807 g_FailedItems:=itemkeys;
1808
1809 WF_CORE.Token('TYPE',itemtype);
1810 WF_CORE.Token('FAILED', to_char(g_FailedItems.count));
1811 WF_CORE.TOKEN('TOTAL', to_char(itemkeys.count));
1812 WF_CORE.RAISE('WFENG_BULK_OPER');
1813 return;
1814 end if;
1815
1816 --reset the duedate, msgid tables
1817
1818 dueDateTab.delete;
1819
1820 msgIdTab.delete;
1821 -- increment the execution time counter
1822 g_execCount:=g_execCount +1;
1823
1824 for arrInd in l_itemkeys.FIRST..l_itemkeys.LAST loop
1825 begin
1826 -- 1. Look first for a '#TIMEOUT' NUMBER attribute
1827 timeout := Wf_Engine.GetActivityAttrNumber(itemtype,
1828 l_itemkeys(arrInd),actid,
1829 wf_engine.eng_timeout_attr,
1830 ignore_notfound=>TRUE);
1831
1832 if (nvl(timeout, 0) <> 0) then
1833 -- Figure duedate as offset from begin time.
1834 -- NOTE: Default timeout is in units of minutes, not days like
1835 -- all other 'date as number' values, thus the 1440 fudge factor.
1836 duedate:= SYSDATE + (timeout / 1440);
1837 else
1838 -- 2. Look for a '#TIMEOUT' DATE attribute
1839 duedate := Wf_Engine.GetActivityAttrDate
1840 (itemtype, l_itemkeys(arrInd),actid,
1841 wf_engine.eng_timeout_attr,
1842 ignore_notfound=>TRUE);
1843 end if;
1844 exception
1845 when others then
1846 if (wf_core.error_name = 'WFENG_ACTIVITY_ATTR') then
1847 -- No #TIMEOUT attr means no timeout
1848 wf_core.clear;
1849 duedate:= null;
1850 end if;
1851 end;
1852
1853 -- we enqueue only if the activityStatus is 'DEFERRED' or null
1854
1855 delay:=0;
1856 msg_id :=null;
1857 begin
1858 if activityStatus is null or activityStatus='DEFERRED' then
1859 if act_ftype = 'PL/SQL' then
1860 wf_queue.enqueue_event
1861 (queuename=>wf_queue.DeferredQueue,
1862 itemtype=> itemtype,
1863 itemkey=>l_itemkeys(arrInd),
1864 actid=>actid,
1865 delay=>delay,
1866 message_handle=>msg_id);
1867 -- even when internal, keep message for cross reference.
1868 -- msg_id :=null;
1869
1870
1871
1872 elsif act_ftype = 'EXTERNAL' then
1873 -- this is a callout so write to OUTBOUND queue
1874 -- do not set the correlation here for compatibility reason
1875
1876
1877 wf_queue.enqueue_event
1878 (queuename=>wf_queue.OutboundQueue,
1879 itemtype=>itemtype,
1880 itemkey=>l_itemkeys(arrInd),
1881 actid=>actid,
1882 funcname=>act_fname,
1883 paramlist=>wf_queue.get_param_list
1884 (itemtype,l_itemkeys(arrInd),actid),
1885 message_handle=>msg_id);
1886
1887 else
1888 -- this is a callout so write to OUTBOUND queue for other type
1889
1890 wf_queue.enqueue_event
1891 (queuename=>wf_queue.OutboundQueue,
1892 itemtype=>itemtype,
1893 itemkey=>l_itemkeys(arrInd),
1894 actid=>actid,
1895 correlation=>act_ftype,
1896 funcname=>act_fname,
1897 paramlist=>wf_queue.get_param_list
1898 (itemtype,l_itemkeys(arrInd),actid),
1899 message_handle=>msg_id);
1900
1901 end if;
1902 end if;
1903 duedateTab(duedateTab.count+1):=duedate;
1904
1905 msgIdTab(msgidtab.count+1):=msg_id;
1906 exception
1907 when others then
1908
1909 g_faileditems(g_faileditems.count+1):=l_itemkeys(arrInd);
1910 l_itemkeys.DELETE(arrInd);
1911 end;
1912
1913 end loop;
1914
1915 if g_failedItems.COUNT>0 then
1916 for arrInd in l_itemkeys.first..l_itemkeys.last loop
1917 if not l_itemkeys.exists(arrInd) then
1918
1919 --fill in the gap with the last list element
1920 l_itemkeys(arrInd):=l_itemkeys(l_itemkeys.last);
1921
1922 -- accordingly re-arrange the duedate,and msgid arrays
1923 -- so as not to lose one-to-one correspondence
1924 duedateTab(arrInd):=duedateTab(duedateTab.last);
1925 msgIdTab(arrInd):=msgIdTab(msgIdTab.last);
1926 l_itemkeys.delete(l_itemkeys.last);
1927 msgIdTab.delete(msgIdTab.last);
1928 duedateTab.delete(duedateTab.last);
1929 end if;
1930 end loop;
1931 end if;
1932
1933
1934 -- finally bulk insert the activity, with status as 'DEFERRED'
1935 -- for all the itemkeys.
1936 begin
1937 forall arrInd in l_itemkeys.FIRST..l_itemkeys.LAST save exceptions
1938 insert
1939 into WF_ITEM_ACTIVITY_STATUSES (
1940 ITEM_TYPE,
1941 ITEM_KEY,
1942 PROCESS_ACTIVITY,
1943 ACTIVITY_STATUS,
1944 ACTIVITY_RESULT_CODE,
1945 ASSIGNED_USER,
1946 NOTIFICATION_ID,
1947 BEGIN_DATE,
1948 END_DATE,
1949 DUE_DATE,
1950 EXECUTION_TIME,
1951 OUTBOUND_QUEUE_ID
1952 ) values (
1953 itemtype,
1954 l_itemkeys(arrInd),
1955 actid,
1956 nvl(activityStatus,'DEFERRED'),
1957 null,
1958 null,
1959 null,
1960 SYSDATE,
1961 null,
1962 duedateTab(arrInd),
1963 g_execCount,
1964 msgIdTab(arrInd)
1965 );
1966 exception
1967 when others then
1968 if sql%bulk_exceptions.count>0 then
1969 for failindex in 1.. sql%bulk_exceptions.count loop
1970 g_failedItems(g_faileditems.count+1):=
1971 l_itemkeys( sql%bulk_exceptions(failindex).error_index);
1972 l_itemkeys.DELETE(sql%bulk_exceptions(failindex).error_index);
1973 end loop;
1974 ConsolidateKeys(l_itemkeys,l_ukeys,l_rkeys);
1975 end if;
1976 end;
1977 end;
1978 g_SuccessItems:=l_itemkeys;
1979
1980 -- Report Error if Failure has occured for some item key
1981 if g_failedItems.COUNT>0 then
1982 WF_CORE.Token('TYPE',itemtype);
1983 WF_CORE.Token('FAILED',to_char(g_FailedItems.count));
1984 WF_CORE.TOKEN('TOTAL', to_char(itemkeys.COUNT));
1985 WF_CORE.RAISE('WFENG_BULK_OPER');
1986 end if;
1987 exception
1988 when others then
1989 Wf_Core.Context('Wf_Engine_Bulk','FastForward',
1990 Itemtype,process,activity);
1991 raise;
1992
1993
1994 end FastForward;
1995
1996
1997 END WF_ENGINE_BULK;
[Home] [Help]