1 package body WF_ITEM as
2 /* $Header: wfengb.pls 120.38.12020000.3 2012/11/13 19:42:45 alsosa ship $ */
3
4 c_itemtype varchar2(8);
5 c_itemkey varchar2(240);
6
7 c_ondemand boolean;
8
9 c_root_activity varchar2(30);
10 c_root_activity_version pls_integer;
11 c_begin_date date;
12 c_userkey varchar2(240);
13
14 --
15 -- ClearCache
16 -- Clear runtime cache
17 --
18 procedure ClearCache
19 is
20 begin
21 wf_item.c_itemtype := '';
22 wf_item.c_itemkey := '';
23 wf_item.c_root_activity := '';
24 wf_item.c_root_activity_version := '';
25 wf_item.c_begin_date := to_date(NULL);
26 wf_item.c_userkey := '';
27
28 --clear ondemand flag
29 wf_item.c_ondemand := null;
30 -- Clear the synch attribute cache too
31 wf_engine.synch_attr_count := 0;
32
33 exception
34 when others then
35 Wf_Core.Context('Wf_Item', 'ClearCache');
36 raise;
37 end ClearCache;
38
39 --
40 -- InitCache (PRIVATE)
41 -- Initialize package cache
42 -- IN
43 -- itemtype - Item type
44 -- itemkey - Item key
45 --
46 procedure InitCache(
47 itemtype in varchar2,
48 itemkey in varchar2,
49 ignore_notfound in boolean default FALSE)
50
51 is
52 rootid number;
53 status varchar2(8);
54 onDemandFlag wf_activity_attributes.text_default%type;
55 begin
56 -- Check for refresh
57 if ((itemtype = wf_item.c_itemtype) and
58 (itemkey = wf_item.c_itemkey)) then
59 return;
60 end if;
61
62 -- SYNCHMODE: If
63 -- 1. Asking for an item other than the cached one AND
64 -- 2. The cached item is a synch process AND
65 -- 3. The cached item has not yet completed
66 -- then raise an error. Other items cannot be accessed until synch
67 -- item completes, because it can't be restarted from db
68 if (wf_item.c_itemkey = wf_engine.eng_synch) then
69 -- Get status of root process of cached item
70 -- Note: If process completed successfully, the last thing in the
71 -- WIAS runtime cache should be the root process, which is the
72 -- only reason this will work.
73 begin
74 rootid := Wf_Process_Activity.RootInstanceId(c_itemtype, c_itemkey,
75 c_root_activity);
76 Wf_Item_Activity_Status.Status(c_itemtype, c_itemkey, rootid, status);
77 exception
78 when others then
79 status := 'x'; -- Treat errors like incomplete process
80 end;
81 if (nvl(status, 'x') <> wf_engine.eng_completed) then
82 Wf_Core.Token('ITEMTYPE', itemtype);
83 Wf_Core.Token('ITEMKEY', itemkey);
84 Wf_Core.Raise('WFENG_SYNCH_ITEM');
85 end if;
86 end if;
87
88 -- Query new values
89 select WI.ROOT_ACTIVITY, WI.ROOT_ACTIVITY_VERSION, WI.BEGIN_DATE,
90 WI.USER_KEY
91 into wf_item.c_root_activity, wf_item.c_root_activity_version,
92 wf_item.c_begin_date, wf_item.c_userkey
93 from WF_ITEMS WI
94 where WI.ITEM_TYPE = InitCache.itemtype
95 and WI.ITEM_KEY = InitCache.itemkey;
96
97 -- Save cache key values
98 wf_item.c_itemtype := itemtype;
99 wf_item.c_itemkey := itemkey;
100
101 --cache ondemand flag
102 -- this could potentially have some impact on the performance as this
103 -- routine is called many times while we are dealing with design time data.
104 -- ondemand is true if the name process #ONDEMANDATTR attribute exist,
105 -- otherwise, it is false.
106 --
107 begin
108 select text_default into onDemandFlag from wf_activity_attributes
109 where activity_item_type = c_itemtype
110 and activity_name = c_root_activity
111 and activity_version=c_root_activity_version
112 and name = '#ONDEMANDATTR';
113
114 wf_item.c_ondemand := true;
115 exception
116 when no_data_found then
117 wf_item.c_ondemand := false;
118 end;
119
120
121
122 exception
123 when NO_DATA_FOUND then
124 if (ignore_notfound) then
125 WF_ITEM.ClearCache;
126
127 else
128
129 Wf_Core.Context('Wf_Item', 'InitCache', itemtype, itemkey);
130 raise;
131
132 end if;
133
134 when others then
135 Wf_Core.Context('Wf_Item', 'InitCache', itemtype, itemkey);
136 raise;
137 end InitCache;
138
139 --
140 -- Attribute_On_Demand
141 -- Set parent ids of an item
142 -- IN
143 -- itemtype - Item type
144 -- itemkey - Item key
145 -- Initialize cache if not already done then return c_ondemand;
146 --
147
148 function Attribute_On_Demand(
149 itemtype in varchar2,
150 itemkey in varchar2
151 ) return boolean
152 as
153 begin
154 --
155 --we need to call in case itemtype and itemkey are not in synch with
156 --cached value.
157 --
158 Wf_Item.InitCache(itemtype, itemkey, ignore_notfound=>TRUE);
159 return c_ondemand;
160 end Attribute_On_Demand;
161
162 --
163 -- Set_Item_Parent
164 -- Set parent ids of an item
165 -- IN
166 -- itemtype - Item type
167 -- itemkey - Item key
168 -- parent_itemtype - Itemtype of parent
169 -- parent_itemkey - Itemkey of parent
170 -- parent_context - Context info about parent
171 --
172 procedure Set_Item_Parent(itemtype in varchar2,
173 itemkey in varchar2,
174 parent_itemtype in varchar2,
175 parent_itemkey in varchar2,
176 parent_context in varchar2,
177 masterdetail in boolean)
178 is
179 ValTooLarge EXCEPTION;
180 pragma exception_init(ValTooLarge, -01401);
181 ValTooLargeNew EXCEPTION;
182 pragma exception_init(ValTooLargeNew, -12899);
183 connect_by_loop exception;
184 pragma exception_init(connect_by_loop, -01436);
185 l_count number;
186 begin
187 begin
188 savepoint wf_loop_savepoint;
189 update WF_ITEMS set
190 PARENT_ITEM_TYPE = Set_Item_Parent.parent_itemtype,
191 PARENT_ITEM_KEY = Set_Item_Parent.parent_itemkey,
192 PARENT_CONTEXT = Set_Item_Parent.parent_context
193 where ITEM_TYPE = Set_Item_Parent.itemtype
194 and ITEM_KEY = Set_Item_Parent.itemkey;
195
196 if (sql%notfound) then
197 raise no_data_found;
198 end if;
199
200 -- bug 12850046: preventing a loop condition from being created
201 select count(1) into l_count
202 from WF_ITEMS
203 CONNECT BY PRIOR PARENT_ITEM_TYPE = ITEM_TYPE AND
204 PRIOR PARENT_ITEM_KEY = ITEM_KEY
205 start with ITEM_TYPE=Set_Item_Parent.itemtype AND
206 ITEM_KEY=Set_Item_Parent.itemkey;
207 exception
208 when connect_by_loop then
209 rollback to wf_loop_savepoint;
210 Wf_Core.Context('Wf_Item', 'Set_Item_Parent', itemtype, itemkey,
211 parent_itemtype, parent_itemkey, parent_context);
212 Wf_Core.Token('PARENT_ITEM_TYPE', parent_itemtype);
213 Wf_Core.Token('PARENT_ITEM_KEY', parent_itemkey);
214 Wf_Core.Token('ITEM_TYPE', itemtype);
215 Wf_Core.Token('ITEM_KEY', itemkey);
216 Wf_Core.Raise('WFENG_ITEM_LOOP');
217 end;
218 if (masterdetail) then
219 --Increment #WAITFORDETAIL master counter if it exists.
220 if (WF_ENGINE.AddToItemAttrNumber(parent_itemType, parent_itemKey,
221 '#WAITFORDETAIL', 1) is NOT NULL) then
222 if (parent_context is NOT null) then
223 --Increment/Create label counter.
224 if (length(parent_context) > 30) then
225 WF_CORE.Token('LABEL', parent_context);
226 WF_CORE.Token('LENGTH', '30');
227 WF_CORE.Raise('WFENG_LABEL_TOO_LARGE');
228
229 elsif (WF_ENGINE.AddToItemAttrNumber(parent_itemType, parent_itemKey,
230 '#CNT_'||parent_context, 1)
231 is NULL) then
232 WF_ENGINE.AddItemAttr(itemType=>parent_itemType,
233 itemKey=>parent_itemKey,
234 aname=>'#CNT_'||parent_context,
235 number_value=>1);
236 end if; --Label Counter exists
237
238 WF_ENGINE.AddItemAttr(itemType=>itemType, itemKey=>itemKey,
239 aname=>'#LBL_'||parent_context,
240 text_value=>parent_context);
241
242 else
243 -- Parent context is null
244 -- increase all known #CNT counter by 1
245 update WF_ITEM_ATTRIBUTE_VALUES
246 set NUMBER_VALUE = NUMBER_VALUE + 1
247 where NAME like '#CNT_%'
248 and NUMBER_VALUE is not null
249 and ITEM_TYPE = parent_itemType
250 and ITEM_KEY = parent_itemKey;
251 end if; --Parent context is not null
252 end if; --#WAITFORDETAIL exists
253 end if; --Caller is signalling that this "should" be a coordinated flow.
254
255 exception
256 when no_data_found then
257 Wf_Core.Context('Wf_Item', 'Set_Item_Parent', itemtype, itemkey,
258 parent_itemtype, parent_itemkey, parent_context);
259 Wf_Core.Token('TYPE', itemtype);
260 Wf_Core.Token('KEY', itemkey);
261 Wf_Core.Raise('WFENG_ITEM');
262
263 when ValTooLarge or ValTooLargeNew then
264 Wf_Core.Context('Wf_Item', 'Set_Item_Parent', itemtype, itemkey,
265 parent_itemtype, parent_itemkey, parent_context, 'TRUE');
266 WF_CORE.Token('LABEL', parent_context);
267 WF_CORE.Token('LENGTH', 30);
268 WF_CORE.Raise('WFENG_LABEL_TOO_LARGE');
269
270 when others then
271 Wf_Core.Context('Wf_Item', 'Set_Item_Parent', itemtype, itemkey,
272 parent_itemtype, parent_itemkey, parent_context);
273 raise;
274 end Set_Item_Parent;
275
276 --
277 -- SetItemOwner
278 -- Set the owner of an item
279 -- IN
280 -- itemtype - Item type
281 -- itemkey - Item key
282 -- owner - Role designated as owner of the item
283 --
284 procedure SetItemOwner(
285 itemtype in varchar2,
286 itemkey in varchar2,
287 owner in varchar2)
288 is
289 begin
290
291 -- Update owner column
292 update WF_ITEMS WI set
293 OWNER_ROLE = SetItemOwner.owner
294 where WI.ITEM_TYPE = SetItemOwner.itemtype
295 and WI.ITEM_KEY = SetItemOwner.itemkey;
296
297 if (sql%notfound) then
298 raise no_data_found;
299 end if;
300 exception
301 when no_data_found then
302 Wf_Core.Context('Wf_Item', 'SetItemOwner', itemtype, itemkey,
303 owner);
304 Wf_Core.Token('TYPE', itemtype);
305 Wf_Core.Token('KEY', itemkey);
306 Wf_Core.Raise('WFENG_ITEM');
307 when others then
308 Wf_Core.Context('Wf_Item', 'SetItemOwner', itemtype, itemkey,
309 owner);
310 raise;
311 end SetItemOwner;
312
313 --
314 -- SetItemUserKey
315 -- Set the user key of an item
316 -- IN
317 -- itemtype - Item type
318 -- itemkey - Item key
319 -- userkey - User key to be set
320 --
321 procedure SetItemUserKey(
322 itemtype in varchar2,
323 itemkey in varchar2,
324 userkey in varchar2)
325 is
326 begin
327 update WF_ITEMS WI set
328 USER_KEY = SetItemUserKey.userkey
329 where WI.ITEM_TYPE = SetItemUserKey.itemtype
330 and WI.ITEM_KEY = SetItemUserKey.itemkey;
331
332 if (sql%notfound) then
333 raise no_data_found;
334 end if;
335
336 -- Set value in the local cache the right item
337 if ((itemtype = wf_item.c_itemtype) and
338 (itemkey = wf_item.c_itemkey)) then
339 wf_item.c_userkey := userkey;
340 end if;
341 exception
342 when no_data_found then
343 Wf_Core.Context('Wf_Item', 'SetItemUserKey', itemtype, itemkey,
344 userkey);
345 Wf_Core.Token('TYPE', itemtype);
346 Wf_Core.Token('KEY', itemkey);
347 Wf_Core.Raise('WFENG_ITEM');
348 when others then
349 Wf_Core.Context('Wf_Item', 'SetItemUserKey', itemtype, itemkey,
350 userkey);
351 raise;
352 end SetItemUserKey;
353
354 --
355 -- GetItemUserKey
356 -- Get the user key of an item
357 -- IN
358 -- itemtype - Item type
359 -- itemkey - Item key
360 -- RETURNS
361 -- User key of the item
362 --
363 function GetItemUserKey(
364 itemtype in varchar2,
365 itemkey in varchar2)
366 return varchar2
367 is
368 buf varchar2(240);
369 begin
370 -- Check first for cached value
371 if ((itemtype = wf_item.c_itemtype) and
372 (itemkey = wf_item.c_itemkey)) then
373 return(wf_item.c_userkey);
374 end if;
375
376 -- No cached value, go directly to the source
377 select USER_KEY
378 into buf
379 from WF_ITEMS WI
380 where WI.ITEM_TYPE = GetItemUserKey.itemtype
381 and WI.ITEM_KEY = GetItemUserKey.itemkey;
382
383 return(buf);
384 exception
385 when no_data_found then
386 Wf_Core.Context('Wf_Item', 'GetItemUserKey', itemtype, itemkey);
387 Wf_Core.Token('TYPE', itemtype);
388 Wf_Core.Token('KEY', itemkey);
389 Wf_Core.Raise('WFENG_ITEM');
390 when others then
391 Wf_Core.Context('Wf_Item', 'GetItemUserKey', itemtype, itemkey);
392 raise;
393 end GetItemUserKey;
394
395 --
396 -- Item_Exist (PRIVATE)
397 -- Returns TRUE if this is an existing item. Otherwise return FALSE.
398 -- IN
399 -- itemtype - item type
400 -- itemkey - item key
401 --
402 function Item_Exist(itemtype in varchar2,
403 itemkey in varchar2)
404 return boolean
405 is
406 begin
407
408 Wf_Item.InitCache(itemtype, itemkey, ignore_notfound=>TRUE);
409
410 if (wf_item.c_itemtype is not null) then
411
412 return(TRUE);
413
414 else
415
416 return(FALSE);
417
418 end if;
419
420 exception
421 when OTHERS then
422 Wf_Core.Context('Wf_Item', 'Item_Exist', itemtype, itemkey);
423 raise;
424 end Item_Exist;
425
426 --
427 -- Root_Process (PRIVATE)
428 -- If the item exists, wflow out variable will contain the root process
429 -- name for this item key. Otherwise the wflow out variable will be null.
430 -- IN
431 -- itemtype - item type
432 -- itemkey - item key
433 -- OUT
434 -- wflow - root process
435 -- version - root process version
436 --
437 procedure Root_Process(itemtype in varchar2,
438 itemkey in varchar2,
439 wflow out NOCOPY varchar2,
440 version out NOCOPY number)
441 is
442 begin
443
444 Wf_Item.InitCache(itemtype, itemkey);
445 wflow := wf_item.c_root_activity;
446 version := wf_item.c_root_activity_version;
447
448 exception
449 when NO_DATA_FOUND then
450 wflow := '';
451 version := -1;
452 when OTHERS then
453 Wf_Core.Context('Wf_Item', 'Root_Process', itemtype, itemkey);
454 raise;
455 end Root_Process;
456
457 --
458 -- Create_Item (PRIVATE)
459 -- Create one row in the WF_ITEMS table with the given item type, item key
460 -- and the root process name.
461 -- IN
462 -- itemtype - item type
463 -- itemkey - item key
464 -- wflow - root process name for this item key.
465 -- actdate - active date of item
466 --
467 procedure Create_Item(
468 itemtype in varchar2,
469 itemkey in varchar2,
470 wflow in varchar2,
471 actdate in date,
472 user_key in varchar2,
473 owner_role in varchar2)
474 is
475
476 rootversion number;
477 rootid number;
478
479 onDemandFlag wf_activity_attributes.text_default%type;
480 --<rwunderl:2412940>
481 status PLS_INTEGER;
482 wiaIND NUMBER;
483 wiavIND NUMBER;
484
485 cursor attrcurs(itype in varchar2) is
486 select WIA.ITEM_TYPE, WIA.NAME, WIA.TYPE, WIA.SUBTYPE, WIA.FORMAT,
487 WIA.TEXT_DEFAULT, WIA.NUMBER_DEFAULT, WIA.DATE_DEFAULT
488 from WF_ITEM_ATTRIBUTES WIA
489 where WIA.ITEM_TYPE = itype;
490
491 begin
492
493 rootversion := Wf_Activity.Version(itemtype, wflow, actdate);
494
495 if (itemkey <> wf_engine.eng_synch) then
496 -- NORMAL: Insert new item and attributes directly in the db
497 insert into WF_ITEMS (
498 ITEM_TYPE,
499 ITEM_KEY,
500 ROOT_ACTIVITY,
501 ROOT_ACTIVITY_VERSION,
502 OWNER_ROLE,
503 PARENT_ITEM_TYPE,
504 PARENT_ITEM_KEY,
505 BEGIN_DATE,
506 END_DATE,
507 USER_KEY
508 ) values (
509 itemtype,
510 itemkey,
511 wflow,
512 rootversion,
513 Create_Item.owner_role,
514 '',
515 '',
516 actdate,
517 to_date(NULL),
518 Create_item.user_key
519 );
520 end if;
521
522 -- Initialize runtime cache (used in both NORMAL and SYNCHMODE).
523 wf_item.c_itemtype := itemtype;
524 wf_item.c_itemkey := itemkey;
525 wf_item.c_root_activity := wflow;
526 wf_item.c_root_activity_version := rootversion;
527 wf_item.c_begin_date := actdate;
528 wf_item.c_userkey := Create_item.user_key;
529
530 --
531 --ondemand flag
532 --
533 begin
534 select text_default into onDemandFlag from wf_activity_attributes
535 where activity_item_type = c_itemtype
536 and activity_name = c_root_activity
537 and activity_version=c_root_activity_version
538 and name = '#ONDEMANDATTR';
539 wf_item.c_ondemand := true;
540 exception
541 when no_data_found then
542 wf_item.c_ondemand := false;
543 end;
544
545 -- Initialize item attributes
546 if (itemkey <> wf_engine.eng_synch) then
547 -- NORMAL: store attributes in table
548 if(not wf_item.c_ondemand) then
549 -- only popluate when the flag is false
550 insert into WF_ITEM_ATTRIBUTE_VALUES (
551 ITEM_TYPE,
552 ITEM_KEY,
553 NAME,
554 TEXT_VALUE,
555 NUMBER_VALUE,
556 DATE_VALUE
557 ) select
558 itemtype,
559 itemkey,
560 WIA.NAME,
561 WIA.TEXT_DEFAULT,
562 WIA.NUMBER_DEFAULT,
563 WIA.DATE_DEFAULT
564 from WF_ITEM_ATTRIBUTES WIA
565 where WIA.ITEM_TYPE = itemtype;
566 end if;
567 else
568 -- SYNCHMODE: store attributes in plsql only
569 for curs in attrcurs(itemtype) loop
570 --Getting the index for the item attribute.
571 WF_CACHE.GetItemAttribute(itemtype, curs.name, status, wiaIND);
572
573 --Getting the index for the item attribute value
574 WF_CACHE.GetItemAttrValue(itemtype, itemkey, curs.name, status, wiavIND);
575
576 --Loading the item attribute into cache for synch mode.
577 WF_CACHE.ItemAttributes(wiaIND).ITEM_TYPE := itemType;
578 WF_CACHE.ItemAttributes(wiaIND).NAME := curs.name;
579 WF_CACHE.ItemAttributes(wiaIND).TYPE := curs.type;
580 WF_CACHE.ItemAttributes(wiaIND).SUBTYPE := curs.subtype;
581 WF_CACHE.ItemAttributes(wiaIND).FORMAT := curs.format;
582 WF_CACHE.ItemAttributes(wiaIND).TEXT_DEFAULT := curs.text_default;
583 WF_CACHE.ItemAttributes(wiaIND).NUMBER_DEFAULT := curs.number_default;
584 WF_CACHE.ItemAttributes(wiaIND).DATE_DEFAULT := curs.date_default;
585
586 --Loading the item attribute value into cache for use by synch processes
587 --only until we introduce the item locking feature.
588 WF_CACHE.ItemAttrValues(wiavIND).ITEM_TYPE := itemType;
589 WF_CACHE.ItemAttrValues(wiavIND).ITEM_KEY := itemKey;
590 WF_CACHE.ItemAttrValues(wiavIND).NAME := curs.name;
591 WF_CACHE.ItemAttrValues(wiavIND).TEXT_VALUE := curs.text_default;
592 WF_CACHE.ItemAttrValues(wiavIND).NUMBER_VALUE := curs.number_default;
593 WF_CACHE.ItemAttrValues(wiavIND).DATE_VALUE := curs.date_default;
594
595 end loop;
596 end if;
597
598 exception
599 when DUP_VAL_ON_INDEX then
600 Wf_Core.Context('Wf_Item', 'Create_Item', itemtype, itemkey, wflow);
601 Wf_Core.Token('TYPE', itemtype);
602 Wf_Core.Token('KEY', itemkey);
603 Wf_Core.Raise('WFENG_ITEM_UNIQUE');
604 when OTHERS then
605 Wf_Core.Context('Wf_Item', 'Create_Item', itemtype, itemkey, wflow);
606 raise;
607 end Create_Item;
608
609 --
610 -- Active_Date (PRIVATE)
611 -- Return the begin date of an item
612 -- IN
613 -- itemtype
614 -- itemkey
615 -- RETURN
616 -- Begin date of item
617 --
618 function Active_Date(itemtype in varchar2,
619 itemkey in varchar2)
620 return date
621 is
622 begin
623 Wf_Item.InitCache(itemtype, itemkey);
624 return(wf_item.c_begin_date);
625 exception
626 when NO_DATA_FOUND then
627 Wf_Core.Context('Wf_Item', 'Active_Date', itemtype, itemkey);
628 Wf_Core.Token('TYPE', itemtype);
629 Wf_Core.Token('KEY', itemkey);
630 Wf_Core.Raise('WFENG_ITEM');
631 when OTHERS then
632 Wf_Core.Context('Wf_Item', 'Active_Date', itemtype, itemkey);
633 end Active_Date;
634
635 --Function Acquire_lock (PRIVATE)
636 --This function tries to lock the particular item (for the give
637 --itemtype/itemkey ) in the wf_items table. It returns true if the lock
638 --acquired else returns false.
639
640 --Here we will not do any error handling but return true/false
641 --for the case of lock_acquired or not . This leaves the caller
642 --the decision of what to do when a resource busy error occurs
643 --(ie FALSE) . For eg : Background engine will ignore it and move
644 --on , WF Engine will raise exception etc.
645
646 function acquire_lock(itemtype in varchar2,
647 itemkey in varchar2,
648 raise_exception in boolean)
649 return boolean
650 is
651 --Bug 2607770
652 --Cursor for acquiring lock
653 cursor itemlock (itemtype varchar2, itemkey varchar2) is
654 select '1'
655 from wf_items
656 where item_type = itemtype
657 and item_key = itemkey
658 for update nowait;
659
660 --Define an exception to capture the resource_busy error
661 resource_busy exception;
662 pragma EXCEPTION_INIT(resource_busy,-00054);
663
664 begin
665 --Acquire lock here by opening the cursor
666 OPEN itemlock (itemtype,itemkey);
667 --Close the cursor once the lock has been acquired
668 CLOSE itemlock;
669 return TRUE;
670 exception
671 --Capture the exception on resource-busy error
672 when resource_busy then
673 --Lets double check that the cursor is not open
674 if (itemlock%ISOPEN) then
675 CLOSE itemlock;
676 end if;
677 --check the if_raise flag . If its true then raise
678 --the exception
679 --else return false and let the caller decide what to do.
680 if raise_exception then
681 raise;
682 --If not able to acquire lock return FALSE
683 else
684 return FALSE;
685 end if;
686 when others then
687 --Lets double check that the cursor is not open
688 if (itemlock%ISOPEN) then
689 CLOSE itemlock;
690 end if;
691 --In this case we do not want a TRUE/FALSE return
692 --we just raise the error.
693 Wf_Core.Context('Wf_Item', 'Acquire_lock', itemtype, itemkey);
694 raise;
695 end;
696
697 --
698 -- SetEndDate (Private)
699 -- Sets end_date and completes any coordinated counter processing.
700 -- IN
701 -- p_itemtype - process item type
702 -- p_itemkey - process item key
703 -- RETURNS
704 -- number
705 -- NOTE:
706 -- This function will return a status of one of the following:
707 -- 0 - Item was found, active, and the end_date was set.
708 -- 1 - The item was not found. (ERROR)
709 function SetEndDate(p_itemtype in varchar2,
710 p_itemkey in varchar2) return number
711 is
712 l_parent_itemType VARCHAR2(8);
713 l_parent_itemKey VARCHAR2(240);
714 l_parent_context VARCHAR2(2000);
715
716 TYPE nameTAB is TABLE of VARCHAR2(30) index by binary_integer;
717 attrNames nameTAB;
718
719 l_result NUMBER;
720 i NUMBER;
721 --Bug 14784055. Need to validate the activity LABEL is not too large
722 ValTooLarge EXCEPTION;
723 pragma exception_init(ValTooLarge, -01401);
724 ValTooLargeNew EXCEPTION;
725 pragma exception_init(ValTooLargeNew, -12899);
726 l_module varchar2(20) := 'SetEndDate';
727 l_context_label varchar2(40);
728 begin
729 UPDATE wf_items
730 SET end_date = sysdate
731 WHERE item_type = p_itemType
732 AND item_key = p_itemKey
733 AND end_date is NULL
734 RETURNING parent_item_type, parent_item_key, parent_context
735 INTO l_parent_itemtype, l_parent_itemkey, l_parent_context;
736
737 if (sql%notfound) then
738 return 1;
739 end if;
740
741 --We need to perform some counter processing if they exist.
742 if ((l_parent_itemType is NOT null) and
743 (l_parent_itemKey is NOT null)) then
744 --No counter Processing to be done for error process
745
746 if l_parent_itemType=Wf_Engine.GetItemAttrText(p_itemtype, p_itemkey,
747 'ERROR_ITEM_TYPE',TRUE) and l_parent_itemkey=
748 Wf_Engine.GetItemAttrText(p_itemtype, p_itemkey,
749 'ERROR_ITEM_KEY',TRUE) then
750 return 0;
751 end if;
752
753 if (WF_ENGINE.AddToItemAttrNumber(l_parent_itemType, l_parent_itemKey,
754 '#WAITFORDETAIL', -1) is NOT null) then
755 if ((l_parent_context is NOT null) and
756 (WF_ENGINE.GetItemAttrText(p_itemType, p_itemKey,
757 '#LBL_'||l_parent_context, TRUE) is NOT NULL)) then
758 l_context_label := '#LBL_'||l_parent_context;
759 if (WF_ENGINE.SetItemAttrText2(p_itemType, p_itemKey,
760 l_context_label, NULL)) then
761 l_context_label := '#CNT_'||l_parent_context;
762 l_result := WF_ENGINE.AddToItemAttrNumber(l_parent_itemType,
763 l_parent_itemKey,
764 l_context_label,
765 -1);
766 end if;
767 else
768 SELECT TEXT_VALUE
769 bulk collect into attrNames
770 FROM WF_ITEM_ATTRIBUTE_VALUES
771 WHERE ITEM_TYPE = p_itemType
772 AND ITEM_KEY = p_itemKey
773 AND NAME like ('#LBL_%')
774 AND TEXT_VALUE is NOT null;
775
776 if (attrNames.COUNT > 0) then
777 for i in attrNames.FIRST..attrNames.LAST loop
778 l_context_label := '#LBL_'||attrNames(i);
779 if (WF_ENGINE.SetItemAttrText2(p_itemType, p_itemKey,
780 l_context_label, NULL)) then
781 l_context_label := '#CNT_'||attrNames(i);
782 l_result := WF_ENGINE.AddToItemAttrNumber(l_parent_itemtype,
783 l_parent_itemkey,
784 l_context_label,
785 -1);
786 end if; --#LBL_ exists as expected.
787 end loop;
788 end if; --There are non-null #LBL_ attributes.
789 --<rwunderl:4271715> We need to decrement any #CNT_ attributes
790 --in the parent which the child may not have created the corresponding
791 --#LBL_ (did not arrive) such as in the case of an AbortProcess()
792 SELECT wiav.NAME
793 bulk collect into attrNames
794 FROM WF_ITEM_ATTRIBUTE_VALUES wiav
795 WHERE wiav.ITEM_TYPE = l_parent_itemType
796 AND wiav.ITEM_KEY = l_parent_itemKey
797 and wiav.NAME like ('#CNT_%')
798 AND NOT EXISTS (select null
799 from wf_item_attribute_values wiav2
800 where wiav2.item_type = p_itemType
801 and wiav2.item_key = p_itemKey
802 and wiav2.name = REPLACE(wiav.name,'#CNT_','#LBL_'));
803 if (attrNames.COUNT > 0) then
804 for i in attrNames.FIRST..attrNames.LAST loop
805 l_result := WF_ENGINE.AddToItemAttrNumber(l_parent_itemtype,
806 l_parent_itemkey,
807 attrNames(i), -1);
808 end loop;
809 end if; --There were unvisited ContinueFlow() activites in the child.
810 end if; --Parent Context
811 end if; --We were able to decrement the #WAITFORDETAIL
812 end if; --This item has a parent.
813
814 -- Bug 10126650
815 -- Find active error children for this completing item and abort them
816 for c_abort in (select item_type, item_key
817 from wf_items wi
818 where wi.parent_item_type = p_itemtype
819 and wi.parent_item_key = p_itemkey
820 and wi.parent_item_type = (select wiav.text_value
821 from wf_item_attribute_values wiav
822 where wiav.item_type = wi.item_type
823 and wiav.item_key = wi.item_key
824 and wiav.name = 'ERROR_ITEM_TYPE')
825 and wi.parent_item_key = (select wiav.text_value
826 from wf_item_attribute_values wiav
827 where wiav.item_type = wi.item_type
828 and wiav.item_key = wi.item_key
829 and wiav.name = 'ERROR_ITEM_KEY')
830 and exists (select null
831 from wf_item_attribute_values wiav
832 where wiav.item_type = wi.item_type
833 and wiav.item_key = wi.item_key
834 and wiav.name = 'ERROR_ACTIVITY_LABEL')
835 and end_date is null)
836 loop
837 WF_ENGINE.AbortProcess(c_abort.item_type, c_abort.item_key);
838 end loop;
839
840 return 0;
841 exception
842 when ValTooLarge OR ValTooLargeNew then
843 Wf_Core.Context('WF_ENGINE', l_module, p_itemtype, p_itemkey,
844 l_parent_itemType, l_parent_itemKey);
845 WF_CORE.Token('LABEL', l_context_label);
846 WF_CORE.Token('LENGTH', 30);
847 WF_CORE.Raise('WFENG_LABEL_TOO_LARGE');
848 when OTHERS then
849 WF_CORE.Context('WF_ITEM', 'SetEndDate', p_itemType, p_itemKey);
850 raise;
851
852 end SetEndDate;
853
854 end WF_ITEM;