1 package body WF_LOAD as
2 /* $Header: wfldrb.pls 120.6 2006/08/24 07:03:06 hgandiko ship $ */
3
4 --
5 -- UPLOAD_ITEM_TYPE
6 --
7 procedure UPLOAD_ITEM_TYPE (
8 x_name in varchar2,
9 x_display_name in varchar2,
10 x_description in varchar2,
11 x_protect_level in number,
12 x_custom_level in number,
13 x_wf_selector in varchar2,
14 x_read_role in varchar2,
15 x_write_role in varchar2,
16 x_execute_role in varchar2,
17 x_persistence_type in varchar2,
18 x_persistence_days in varchar2,
19 x_level_error out NOCOPY number
20 ) is
21 row_id varchar2(30);
22 protection_level number;
23 customization_level number;
24 conflict_name varchar2(8);
25 l_persistence_days number;
26 l_dname varchar2(80);
27 n_dname varchar2(80);
28 l_name varchar2(8);
29 begin
30 -- Reset any caches that might be running.
31 WF_CACHE.Reset;
32
33 n_dname := x_display_name;
34 begin
35 select NAME, DISPLAY_NAME, NAME
36 into conflict_name, l_dname, l_name
37 from WF_ITEM_TYPES_VL
38 where DISPLAY_NAME = x_display_name
39 and NAME <> x_name;
40
41 n_dname := substrb('@'||l_dname, 1, 80);
42
43 -- this loop will make sure no duplicate with n_dname
44 loop
45 begin
46 select NAME, DISPLAY_NAME
47 into conflict_name, l_dname
48 from WF_ITEM_TYPES_VL
49 where DISPLAY_NAME = n_dname
50 and NAME <> l_name;
51
52 n_dname := substrb('@'||l_dname, 1, 80);
53
54 if ( n_dname = l_dname ) then
55 Wf_Core.Token('DNAME', x_display_name);
56 Wf_Core.Token('NAME', x_name);
57 Wf_Core.Token('CONFLICT_NAME', conflict_name);
58 Wf_Core.Raise('WFSQL_UNIQUE_NAME');
59 exit;
60 end if;
61 exception
62 when no_data_found then
63 exit;
64 end;
65 end loop;
66
67 -- ### Not needed any more
68 -- update the old data with the new display name
69 -- begin
70 -- update WF_ITEM_TYPES_TL
71 -- set display_name = n_dname
72 -- where NAME = l_name
73 -- and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
74 -- exception
75 -- when others then
76 -- Wf_Core.Token('TABLE', 'ITEM_TYPES_TL');
77 -- Wf_Core.Token('VALUE', l_name);
78 -- Wf_Core.Raise('WFSQL_UPDATE_FAIL');
79 -- end;
80 exception
81 when no_data_found then
82 null;
83 end;
84
85 l_persistence_days := to_number(x_persistence_days);
86
87 -- Check protection level
88 x_level_error := 0;
89 begin
90 select PROTECT_LEVEL, CUSTOM_LEVEL
91 into protection_level, customization_level
92 from WF_ITEM_TYPES_VL
93 where NAME = x_name;
94
95 if ((wf_core.upload_mode <> 'FORCE') and
96 (protection_level < wf_core.session_level)) then
97 x_level_error := 1;
98 return;
99 end if;
100
101 if ((wf_core.upload_mode = 'UPGRADE') and
102 (customization_level > wf_core.session_level)) then
103 x_level_error := 2;
104 return;
105 end if;
106
107 -- Update existing row
108 Wf_Item_Types_Pkg.Update_Row(
109 x_name => x_name,
110 x_protect_level => x_protect_level,
111 x_custom_level => x_custom_level,
112 x_wf_selector => x_wf_selector,
113 x_read_role => x_read_role,
114 x_write_role => x_write_role,
115 x_execute_role => x_execute_role,
116 x_display_name => n_dname,
117 x_description => x_description,
118 x_persistence_type => x_persistence_type,
119 x_persistence_days => l_persistence_days
120 );
121 exception
122 when NO_DATA_FOUND then
123 -- Check protection level for new row
124 if ((wf_core.upload_mode <> 'FORCE') and
125 (x_protect_level < wf_core.session_level)) then
126 x_level_error := 4+1;
127 return;
128 end if;
129
130 if ((wf_core.upload_mode = 'UPGRADE') and
131 (x_custom_level > wf_core.session_level)) then
132 x_level_error := 4+2;
133 return;
134 end if;
135
136 -- Insert new row
137 Wf_Item_Types_Pkg.Insert_Row(
138 x_rowid => row_id,
139 x_name => x_name,
140 x_protect_level => x_protect_level,
141 x_custom_level => x_custom_level,
142 x_wf_selector => x_wf_selector,
143 x_read_role => x_read_role,
144 x_write_role => x_write_role,
145 x_execute_role => x_execute_role,
146 x_display_name => n_dname,
147 x_description => x_description,
148 x_persistence_type => x_persistence_type,
149 x_persistence_days => l_persistence_days
150 );
151 end;
152
153
154 exception
155 when OTHERS then
156 Wf_Core.Context('Wf_Load', 'Upload_Item_Type', x_name);
157 raise;
158 end UPLOAD_ITEM_TYPE;
159
160 --
161 -- Reseq_Item_Attribute (PRIVATE)
162 -- Resequence attributes in the db to match the sequence of attrs
163 -- being uploaded. This is needed to avoid unique index violations
164 -- on the sequence when uploading reordered attributes.
165 -- IN
166 -- itemtype - Item type owning attrs
167 -- oldseq - Original sequence number of attr being uploaded
168 -- newseq - New sequence number of attribute
169 --
170 procedure Reseq_Item_Attribute(
171 itemtype in varchar2,
172 oldseq in number,
173 newseq in number)
174 is
175 begin
176 -- Move attr being updated to a placeholder out of the way.
177 update WF_ITEM_ATTRIBUTES set
178 SEQUENCE = -1
179 where ITEM_TYPE = itemtype
180 and SEQUENCE = oldseq;
181
182 if (oldseq < newseq) then
183 -- Move attrs DOWN in sequence to make room at higher position
184 for i in (oldseq + 1) .. newseq loop
185 update WF_ITEM_ATTRIBUTES set
186 SEQUENCE = SEQUENCE - 1
187 where ITEM_TYPE = itemtype
188 and SEQUENCE = i;
189 end loop;
190 elsif (oldseq > newseq) then
191 -- Move attrs UP in sequence to make room at lower position
192 for i in reverse newseq .. (oldseq - 1) loop
193 update WF_ITEM_ATTRIBUTES set
194 SEQUENCE = SEQUENCE + 1
195 where ITEM_TYPE = itemtype
196 and SEQUENCE = i;
197 end loop;
198 end if;
199
200 -- Move attr being updated into new sequence position
201 update WF_ITEM_ATTRIBUTES set
202 SEQUENCE = newseq
203 where ITEM_TYPE = itemtype
204 and SEQUENCE = -1;
205
206 exception
207 when others then
208 Wf_Core.Context('Wf_Load', 'Reseq_Item_Attribute', itemtype,
209 to_char(oldseq), to_char(newseq));
210 raise;
211 end Reseq_Item_Attribute;
212
213 --
214 -- UPLOAD_ITEM_ATTRIBUTE
215 --
216 procedure UPLOAD_ITEM_ATTRIBUTE (
217 x_item_type in varchar2,
218 x_name in varchar2,
219 x_display_name in varchar2,
220 x_description in varchar2,
221 x_sequence in number,
222 x_type in varchar2,
223 x_protect_level in number,
224 x_custom_level in number,
225 x_subtype in varchar2,
226 x_format in varchar2,
227 x_default in varchar2,
228 x_level_error out NOCOPY number
229 ) is
230 row_id varchar2(30);
231 protection_level number;
232 customization_level number;
233 l_text_default varchar2(4000) := '';
234 l_number_default number := '';
235 l_date_default date := '';
236 conflict_name varchar2(40);
237 l_dname varchar2(80);
238 n_dname varchar2(80);
239 l_name varchar2(30);
240 old_sequence number;
241 begin
242 -- Reset any caches that might be running.
243 WF_CACHE.Reset;
244
245 -- Check for unique index violations
246 -- try to resolve the problem by appending '@'
247 -- to the incoming display name
248 n_dname := x_display_name;
249 begin
250 -- l_name will be the old data to update
251 select ITEM_TYPE||':'||NAME, DISPLAY_NAME, NAME
252 into conflict_name, l_dname, l_name
253 from WF_ITEM_ATTRIBUTES_VL
254 where DISPLAY_NAME = x_display_name
255 and ITEM_TYPE = x_item_type
256 and NAME <> x_name;
257
258 n_dname := substrb('@'||l_dname, 1, 80);
259
260 -- this loop will make sure no duplicate with n_dname
261 loop
262 begin
263 select ITEM_TYPE||':'||NAME, DISPLAY_NAME
264 into conflict_name, l_dname
265 from WF_ITEM_ATTRIBUTES_VL
266 where DISPLAY_NAME = n_dname
267 and ITEM_TYPE = x_item_type
268 and NAME <> l_name;
269
270 n_dname := substrb('@'||l_dname, 1, 80);
271
272 if ( n_dname = l_dname ) then
273 Wf_Core.Token('DNAME', x_display_name);
274 Wf_Core.Token('NAME', x_item_type||':'||x_name);
275 Wf_Core.Token('CONFLICT_NAME', conflict_name);
276 Wf_Core.Raise('WFSQL_UNIQUE_NAME');
277 exit;
278 end if;
279 exception
280 when no_data_found then
281 exit;
282 end;
283 end loop;
284
285 -- ### Not needed any more
286 -- update the old data with the new display name
287 -- begin
288 -- update WF_ITEM_ATTRIBUTES_TL
289 -- set display_name = n_dname
290 -- where ITEM_TYPE = x_item_type
291 -- and NAME = l_name
292 -- and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
293 -- exception
294 -- when others then
295 -- Wf_Core.Token('TABLE', 'ITEM_ATTRIBUTES_TL');
296 -- Wf_Core.Token('VALUE', l_name);
297 -- Wf_Core.Raise('WFSQL_UPDATE_FAIL');
298 -- end;
299 exception
300 when no_data_found then
301 null;
302
303 when others then
304 raise;
305 end;
306
307 -- Translate x_default to appropriate type
308 if (x_type = 'NUMBER') then
309 l_number_default := to_number(x_default);
310 elsif (x_type = 'DATE') then
311 l_date_default := to_date(x_default, 'YYYY/MM/DD HH24:MI:SS');
312 else
313 l_text_default := x_default;
314 end if;
315
316 -- Check protection level
317 x_level_error := 0;
318 begin
319 select PROTECT_LEVEL, CUSTOM_LEVEL, SEQUENCE
320 into protection_level, customization_level, old_sequence
321 from WF_ITEM_ATTRIBUTES_VL
322 where ITEM_TYPE = x_item_type
323 and NAME = x_name;
324
325 if ((wf_core.upload_mode <> 'FORCE') and
326 (protection_level < wf_core.session_level)) then
327 x_level_error := 1;
328 return;
329 end if;
330
331 if ((wf_core.upload_mode = 'UPGRADE') and
332 (customization_level > wf_core.session_level)) then
333 x_level_error := 2;
334 return;
335 end if;
336
337 -- Resequence attrs in db to match sequence being uploaded
338 if (old_sequence <> x_sequence) then
339 Wf_Load.Reseq_Item_Attribute(
340 itemtype => x_item_type,
341 oldseq => old_sequence,
342 newseq => x_sequence);
343 end if;
344
345 -- Update existing row
346 Wf_Item_Attributes_Pkg.Update_Row(
347 x_item_type => x_item_type,
348 x_name => x_name,
349 x_sequence => x_sequence,
350 x_type => x_type,
351 x_protect_level => x_protect_level,
352 x_custom_level => x_custom_level,
353 x_subtype => x_subtype,
354 x_format => x_format,
355 x_text_default => l_text_default,
356 x_number_default => l_number_default,
357 x_date_default => l_date_default,
358 x_display_name => n_dname,
359 x_description => x_description
360 );
361 exception
362 when NO_DATA_FOUND then
363 -- Check protection level for new row
364 -- ### Relax the checking on attributes, lookup_code, transitions
365 if ((wf_core.upload_mode <> 'FORCE') and
366 (x_protect_level < wf_core.session_level)) then
367 x_level_error := 1;
368 return;
369 end if;
370
371 if ((wf_core.upload_mode = 'UPGRADE') and
372 (x_custom_level > wf_core.session_level)) then
373 x_level_error := 2;
374 return;
375 end if;
376
377 -- Resequence attrs so that everything below the attr being
378 -- inserted is shoved out of the way.
379 select nvl(max(SEQUENCE), -1)+1
380 into old_sequence
381 from WF_ITEM_ATTRIBUTES
382 where ITEM_TYPE = x_item_type;
383
384 if (old_sequence <> x_sequence) then
385 Wf_Load.Reseq_Item_Attribute(
386 itemtype => x_item_type,
387 oldseq => old_sequence,
388 newseq => x_sequence);
389 end if;
390
391 -- Insert new row
392 Wf_Item_Attributes_Pkg.Insert_Row(
393 x_rowid => row_id,
394 x_item_type => x_item_type,
395 x_name => x_name,
396 x_sequence => x_sequence,
397 x_type => x_type,
398 x_protect_level => x_protect_level,
399 x_custom_level => x_custom_level,
400 x_subtype => x_subtype,
401 x_format => x_format,
402 x_text_default => l_text_default,
403 x_number_default => l_number_default,
404 x_date_default => l_date_default,
405 x_display_name => n_dname,
406 x_description => x_description
407 );
408 end;
409
410 exception
411 when OTHERS then
412 Wf_Core.Context('Wf_Load', 'Upload_Item_Attribute', x_item_type, x_name);
413 raise;
414 end UPLOAD_ITEM_ATTRIBUTE;
415
416 --
417 -- UPLOAD_LOOKUP_TYPE
418 --
419 procedure UPLOAD_LOOKUP_TYPE (
420 x_lookup_type in varchar2,
421 x_display_name in varchar2,
422 x_description in varchar2,
423 x_protect_level in number,
424 x_custom_level in number,
425 x_item_type in varchar2,
426 x_level_error out NOCOPY number
427 ) is
428 row_id varchar2(30);
429 protection_level number;
430 customization_level number;
431 conflict_name varchar2(30);
432 l_dname varchar2(80);
433 n_dname varchar2(80);
434 l_name varchar2(30);
435 begin
436 -- Reset any caches that might be running.
437 WF_CACHE.Reset;
438
439 -- Set the new display name
440 n_dname := x_display_name;
441
442 -- Check for unique index violations
443 -- try to resolve the problem by appending '@'
444 -- to the incoming display name
445 begin
446 select LOOKUP_TYPE, DISPLAY_NAME, LOOKUP_TYPE
447 into conflict_name, l_dname, l_name
448 from WF_LOOKUP_TYPES
449 where DISPLAY_NAME = x_display_name
450 and LOOKUP_TYPE <> x_lookup_type;
451
452 n_dname := substrb('@'||l_dname, 1, 80);
453
454 -- this loop will make sure no duplicate with n_dname
455 loop
456 begin
457 select LOOKUP_TYPE, DISPLAY_NAME
458 into conflict_name, l_dname
459 from WF_LOOKUP_TYPES
460 where DISPLAY_NAME = n_dname
461 and LOOKUP_TYPE <> l_name;
462
463 n_dname := substrb('@'||l_dname, 1, 80);
464
465 if ( n_dname = l_dname ) then
466 Wf_Core.Token('DNAME', x_display_name);
467 Wf_Core.Token('NAME', x_lookup_type);
468 Wf_Core.Token('CONFLICT_NAME', conflict_name);
469 Wf_Core.Raise('WFSQL_UNIQUE_NAME');
470 exit;
471 end if;
472 exception
473 when no_data_found then
474 exit;
475 end;
476 end loop;
477 -- ### No need to do this
478 -- update the old data with the new meaning
479 -- begin
480 -- update WF_LOOKUP_TYPES_TL
481 -- set DISPLAY_NAME = n_dname
482 -- where LOOKUP_TYPE = l_name
483 -- and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
484 -- exception
485 -- when others then
486 -- Wf_Core.Token('TABLE', 'LOOKUP_TYPES_TL');
487 -- Wf_Core.Token('VALUE', l_name);
488 -- Wf_Core.Raise('WFSQL_UPDATE_FAIL');
489 -- end;
490 exception
491 when no_data_found then
492 null;
493 end;
494
495 -- Check protection level
496 x_level_error := 0;
497 begin
498 select PROTECT_LEVEL, CUSTOM_LEVEL
499 into protection_level, customization_level
500 from WF_LOOKUP_TYPES
501 where LOOKUP_TYPE = x_lookup_type;
502
503 if ((wf_core.upload_mode <> 'FORCE') and
504 (protection_level < wf_core.session_level)) then
505 x_level_error := 1;
506 return;
507 end if;
508
509 if ((wf_core.upload_mode = 'UPGRADE') and
510 (customization_level > wf_core.session_level)) then
511 x_level_error := 2;
512 return;
513 end if;
514
515 -- Update existing row
516 Wf_Lookup_Types_Pkg.Update_Row(
517 x_lookup_type => x_lookup_type,
518 x_item_type => x_item_type,
519 x_protect_level => x_protect_level,
520 x_custom_level => x_custom_level,
521 x_display_name => n_dname,
522 x_description => x_description
523 );
524 exception
525 when NO_DATA_FOUND then
526 -- Check protection level for new row
527 if ((wf_core.upload_mode <> 'FORCE') and
528 (x_protect_level < wf_core.session_level)) then
529 x_level_error := 4+1;
530 return;
531 end if;
532
533 if ((wf_core.upload_mode = 'UPGRADE') and
534 (x_custom_level > wf_core.session_level)) then
535 x_level_error := 4+2;
536 return;
537 end if;
538
539 -- Insert new row
540 Wf_Lookup_Types_Pkg.Insert_Row(
541 x_rowid => row_id,
542 x_lookup_type => x_lookup_type,
543 x_item_type => x_item_type,
544 x_protect_level => x_protect_level,
545 x_custom_level => x_custom_level,
546 x_display_name => n_dname,
547 x_description => x_description
548 );
549 end;
550
551 exception
552 when OTHERS then
553 Wf_Core.Context('Wf_Load', 'Upload_Lookup_Type', x_lookup_type);
554 raise;
555 end UPLOAD_LOOKUP_TYPE;
556
557 --
558 -- UPLOAD_LOOKUP
559 --
560 procedure UPLOAD_LOOKUP (
561 x_lookup_type in varchar2,
562 x_lookup_code in varchar2,
563 x_meaning in varchar2,
564 x_description in varchar2,
565 x_protect_level in number,
566 x_custom_level in number,
567 x_level_error out NOCOPY number
568 ) is
569 row_id varchar2(30) := '';
570 protection_level number;
571 customization_level number;
572 conflict_name varchar2(80);
573 l_dname varchar2(80);
574 n_dname varchar2(80);
575 l_name varchar2(30);
576 begin
577 -- Reset any caches that might be running.
578 WF_CACHE.Reset;
579
580 -- Check for unique index violations
581 -- try to resolve the problem by appending '@'
582 -- to the incoming meaning
583 n_dname := x_meaning;
584 begin
585 -- l_name will be the old data to update
586 select LOOKUP_TYPE||':'||LOOKUP_CODE, MEANING, LOOKUP_CODE
587 into conflict_name, l_dname, l_name
588 from WF_LOOKUPS
589 where MEANING = x_meaning
590 and LOOKUP_TYPE = x_lookup_type
591 and LOOKUP_CODE <> x_lookup_code;
592
593 n_dname := substrb('@'||l_dname, 1, 80);
594
595 -- this loop will make sure no duplicate with n_dname
596 loop
597 begin
598 select LOOKUP_TYPE||':'||LOOKUP_CODE, MEANING
599 into conflict_name, l_dname
600 from WF_LOOKUPS
601 where MEANING = n_dname
602 and LOOKUP_TYPE = x_lookup_type
603 and LOOKUP_CODE <> l_name;
604
605 n_dname := substrb('@'||l_dname, 1, 80);
606
607 if ( n_dname = l_dname ) then
608 Wf_Core.Token('DNAME', x_meaning);
609 Wf_Core.Token('NAME', x_lookup_code);
610 Wf_Core.Token('CONFLICT_NAME', conflict_name);
611 Wf_Core.Raise('WFSQL_UNIQUE_NAME');
612 exit;
613 end if;
614 exception
615 when no_data_found then
616 exit;
617 end;
618 end loop;
619
620 -- ### No need to do this
621 -- update the old data with the new meaning
622 -- begin
623 -- update WF_LOOKUPS_TL
624 -- set MEANING = n_dname
625 -- where LOOKUP_TYPE = x_lookup_type
626 -- and LOOKUP_CODE = l_name
627 -- and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
628 -- exception
629 -- when others then
630 -- Wf_Core.Token('TABLE', 'LOOKUPS_TL');
631 -- Wf_Core.Token('VALUE', l_name);
632 -- Wf_Core.Raise('WFSQL_UPDATE_FAIL');
633 -- end;
634 exception
635 when no_data_found then
636 null;
637
638 when others then
639 raise;
640 end;
641
642 -- Check protection level
643 x_level_error := 0;
644 begin
645 select PROTECT_LEVEL, CUSTOM_LEVEL
646 into protection_level, customization_level
647 from WF_LOOKUPS
648 where LOOKUP_TYPE = x_lookup_type
649 and LOOKUP_CODE = x_lookup_code;
650
651 if ((wf_core.upload_mode <> 'FORCE') and
652 (protection_level < wf_core.session_level)) then
653 x_level_error := 1;
654 return;
655 end if;
656
657 if ((wf_core.upload_mode = 'UPGRADE') and
658 (customization_level > wf_core.session_level)) then
659 x_level_error := 2;
660 return;
661 end if;
662
663 -- Update existing row
664 Wf_Lookups_Pkg.Update_Row(
665 x_lookup_type => x_lookup_type,
666 x_lookup_code => x_lookup_code,
667 x_protect_level => x_protect_level,
668 x_custom_level => x_custom_level,
669 x_meaning => n_dname,
670 x_description => x_description
671 );
672 exception
673 when NO_DATA_FOUND then
674 -- Check protection level for new row
675 -- ### Relax the checking on attributes, lookup_code, transitions
676 if ((wf_core.upload_mode <> 'FORCE') and
677 (x_protect_level < wf_core.session_level)) then
678 x_level_error := 1;
679 return;
680 end if;
681
682 if ((wf_core.upload_mode = 'UPGRADE') and
683 (x_custom_level > wf_core.session_level)) then
684 x_level_error := 2;
685 return;
686 end if;
687
688 -- Insert new row
689 Wf_Lookups_Pkg.Insert_Row(
690 x_rowid => row_id,
691 x_lookup_type => x_lookup_type,
692 x_lookup_code => x_lookup_code,
693 x_protect_level => x_protect_level,
694 x_custom_level => x_custom_level,
695 x_meaning => n_dname,
696 x_description => x_description
697 );
698 end;
699
700 exception
701 when OTHERS then
702 Wf_Core.Context('Wf_Load', 'Upload_Lookup', x_lookup_type, x_lookup_code);
703 raise;
704 end UPLOAD_LOOKUP;
705
706 --
707 -- UPLOAD_MESSAGE
708 --
709 procedure UPLOAD_MESSAGE (
710 x_type in varchar2,
711 x_name in varchar2,
712 x_display_name in varchar2,
713 x_description in varchar2,
714 x_subject in varchar2,
715 x_body in varchar2,
716 x_html_body in varchar2,
717 x_protect_level in number,
718 x_custom_level in number,
719 x_default_priority in number,
720 x_read_role in varchar2,
721 x_write_role in varchar2,
722 x_level_error out NOCOPY number
723 ) is
724 row_id varchar2(30);
725 protection_level number;
726 customization_level number;
727 begin
728 -- Reset any caches that might be running.
729 WF_CACHE.Reset;
730
731 -- Check protection level
732 x_level_error := 0;
733 begin
734 select PROTECT_LEVEL, CUSTOM_LEVEL
735 into protection_level, customization_level
736 from WF_MESSAGES_VL
737 where TYPE = x_type
738 and NAME = x_name;
739
740 if ((wf_core.upload_mode <> 'FORCE') and
741 (protection_level < wf_core.session_level)) then
742 x_level_error := 1;
743 return;
744 end if;
745
746 if ((wf_core.upload_mode = 'UPGRADE') and
747 (customization_level > wf_core.session_level)) then
748 x_level_error := 2;
749 return;
750 end if;
751
752 -- Update existing row
753 Wf_Messages_Pkg.Update_Row(
754 x_type => x_type,
755 x_name => x_name,
756 x_protect_level => x_protect_level,
757 x_custom_level => x_custom_level,
758 x_default_priority => x_default_priority,
759 x_read_role => x_read_role,
760 x_write_role => x_write_role,
761 x_display_name => x_display_name,
762 x_description => x_description,
763 x_subject => x_subject,
764 x_body => x_body,
765 x_html_body => x_html_body
766 );
767 exception
768 when NO_DATA_FOUND then
769 -- Check protection level for new row
770 if ((wf_core.upload_mode <> 'FORCE') and
771 (x_protect_level < wf_core.session_level)) then
772 x_level_error := 4+1;
773 return;
774 end if;
775
776 if ((wf_core.upload_mode = 'UPGRADE') and
777 (x_custom_level > wf_core.session_level)) then
778 x_level_error := 4+2;
779 return;
780 end if;
781
782 -- Insert new row
783 Wf_Messages_Pkg.Insert_Row(
784 x_rowid => row_id,
785 x_type => x_type,
786 x_name => x_name,
787 x_protect_level => x_protect_level,
788 x_custom_level => x_custom_level,
789 x_default_priority => x_default_priority,
790 x_read_role => x_read_role,
791 x_write_role => x_write_role,
792 x_display_name => x_display_name,
793 x_description => x_description,
794 x_subject => x_subject,
795 x_body => x_body,
796 x_html_body => x_html_body
797 );
798 end;
799
800 exception
801 when OTHERS then
802 Wf_Core.Context('Wf_Load', 'Upload_Message', x_type, x_name);
803 raise;
804 end UPLOAD_MESSAGE;
805
806 --
807 -- Reseq_Message_Attribute (PRIVATE)
808 -- Resequence attributes in the db to match the sequence of attrs
809 -- being uploaded. This is needed to avoid unique index violations
810 -- on the sequence when uploading reordered attributes.
811 -- IN
812 -- msgtype - Message type of msg owning attrs
813 -- msgname - Message name of msg owning attrs
814 -- oldseq - Original sequence number of attr being uploaded
815 -- newseq - New sequence number of attribute
816 --
817 procedure Reseq_Message_Attribute(
818 msgtype in varchar2,
819 msgname in varchar2,
820 oldseq in number,
821 newseq in number)
822 is
823 begin
824 -- Move attr being updated to a placeholder out of the way.
825 update WF_MESSAGE_ATTRIBUTES set
826 SEQUENCE = -1
827 where MESSAGE_TYPE = msgtype
828 and MESSAGE_NAME = msgname
829 and SEQUENCE = oldseq;
830
831 if (oldseq < newseq) then
832 -- Move attrs DOWN in sequence to make room at higher position
833 for i in (oldseq + 1) .. newseq loop
834 update WF_MESSAGE_ATTRIBUTES set
835 SEQUENCE = SEQUENCE - 1
836 where MESSAGE_TYPE = msgtype
837 and MESSAGE_NAME = msgname
838 and SEQUENCE = i;
839 end loop;
840 elsif (oldseq > newseq) then
841 -- Move attrs UP in sequence to make room at lower position
842 for i in reverse newseq .. (oldseq - 1) loop
843 update WF_MESSAGE_ATTRIBUTES set
844 SEQUENCE = SEQUENCE + 1
845 where MESSAGE_TYPE = msgtype
846 and MESSAGE_NAME = msgname
847 and SEQUENCE = i;
848 end loop;
849 end if;
850
851 -- Move attr being updated into new sequence position
852 update WF_MESSAGE_ATTRIBUTES set
853 SEQUENCE = newseq
854 where MESSAGE_TYPE = msgtype
855 and MESSAGE_NAME = msgname
856 and SEQUENCE = -1;
857
858 exception
859 when others then
860 Wf_Core.Context('Wf_Load', 'Reseq_Message_Attribute', msgtype,
861 msgname, to_char(oldseq), to_char(newseq));
862 raise;
863 end Reseq_Message_Attribute;
864
865 --
866 -- UPLOAD_MESSAGE_ATTRIBUTE
867 --
868 procedure UPLOAD_MESSAGE_ATTRIBUTE (
869 x_message_type in varchar2,
870 x_message_name in varchar2,
871 x_name in varchar2,
872 x_display_name in varchar2,
873 x_description in varchar2,
874 x_sequence in number,
875 x_type in varchar2,
876 x_subtype in varchar2,
877 x_protect_level in number,
878 x_custom_level in number,
879 x_format in varchar2,
880 x_default in varchar2,
881 x_value_type in varchar2,
882 x_attach in varchar2,
883 x_level_error out NOCOPY number
884 ) is
885 row_id varchar2(30);
886 protection_level number;
887 customization_level number;
888 l_text_default varchar2(4000) := '';
889 l_number_default number := '';
890 l_date_default date := '';
891 conflict_name varchar2(80);
892 l_dname varchar2(80);
893 n_dname varchar2(80);
894 l_name varchar2(30);
895 old_sequence number;
896 begin
897 -- Reset any caches that might be running.
898 WF_CACHE.Reset;
899
900 -- Check for unique index violations
901 -- try to resolve the problem by appending '@'
902 -- to the incoming display name
903 n_dname := x_display_name;
904 begin
905 -- l_name will be the old data to update
906 select MESSAGE_TYPE||':'||MESSAGE_NAME||':'||NAME, DISPLAY_NAME, NAME
907 into conflict_name, l_dname, l_name
908 from WF_MESSAGE_ATTRIBUTES_VL
909 where DISPLAY_NAME = n_dname
910 and MESSAGE_TYPE = x_message_type
911 and MESSAGE_NAME = x_message_name
912 and NAME <> x_name;
913
914 n_dname := substrb('@'||l_dname, 1, 80);
915
916 -- this loop will make sure no duplicate with n_dname
917 loop
918 begin
919 select MESSAGE_TYPE||':'||MESSAGE_NAME||':'||NAME, DISPLAY_NAME
920 into conflict_name, l_dname
921 from WF_MESSAGE_ATTRIBUTES_VL
922 where DISPLAY_NAME = n_dname
923 and MESSAGE_TYPE = x_message_type
924 and MESSAGE_NAME = x_message_name
925 and NAME <> l_name;
926
927 n_dname := substrb('@'||l_dname, 1, 80);
928
929 if ( n_dname = l_dname ) then
930 Wf_Core.Token('DNAME', x_display_name);
931 Wf_Core.Token('NAME', x_message_type||':'||x_message_name||':'
932 ||x_name);
933 Wf_Core.Token('CONFLICT_NAME', conflict_name);
934 Wf_Core.Raise('WFSQL_UNIQUE_NAME');
935 exit;
936 end if;
937 exception
938 when no_data_found then
939 exit;
940 end;
941 end loop;
942
943 -- ### No need to do this
944 -- update the old data with the new display name
945 -- begin
946 -- update WF_MESSAGE_ATTRIBUTES_TL
947 -- set display_name = n_dname
948 -- where MESSAGE_TYPE = x_message_type
949 -- and MESSAGE_NAME = x_message_name
950 -- and NAME = l_name
951 -- and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
952 -- exception
953 -- when others then
954 -- Wf_Core.Token('TABLE', 'MESSAGE_ATTRIBUTES_TL');
955 -- Wf_Core.Token('VALUE', l_name);
956 -- Wf_Core.Raise('WFSQL_UPDATE_FAIL');
957 -- end;
958 exception
959 when no_data_found then
960 null;
961
962 when others then
963 raise;
964 end;
965
966 -- Translate x_default to appropriate type
967 if ((x_value_type = 'CONSTANT') and (x_type = 'NUMBER')) then
968 l_number_default := to_number(x_default);
969 elsif ((x_value_type = 'CONSTANT') and (x_type = 'DATE')) then
970 l_date_default := to_date(x_default, 'YYYY/MM/DD HH24:MI:SS');
971 else
972 l_text_default := x_default;
973 end if;
974
975 -- Check protection level
976 x_level_error := 0;
977 begin
978 select PROTECT_LEVEL, CUSTOM_LEVEL, SEQUENCE
979 into protection_level, customization_level, old_sequence
980 from WF_MESSAGE_ATTRIBUTES_VL
981 where MESSAGE_TYPE = x_message_type
982 and MESSAGE_NAME = x_message_name
983 and NAME = x_name;
984
985 if ((wf_core.upload_mode <> 'FORCE') and
986 (protection_level < wf_core.session_level)) then
987 x_level_error := 1;
988 return;
989 end if;
990
991 if ((wf_core.upload_mode = 'UPGRADE') and
992 (customization_level > wf_core.session_level)) then
993 x_level_error := 2;
994 return;
995 end if;
996
997 -- Resequence attrs in db to match sequence being uploaded
998 if (old_sequence <> x_sequence) then
999 Wf_Load.Reseq_Message_Attribute(
1000 msgtype => x_message_type,
1001 msgname => x_message_name,
1002 oldseq => old_sequence,
1003 newseq => x_sequence);
1004 end if;
1005
1006 Wf_Message_Attributes_Pkg.Update_Row(
1007 x_message_type => x_message_type,
1008 x_message_name => x_message_name,
1009 x_name => x_name,
1010 x_sequence => x_sequence,
1011 x_type => x_type,
1012 x_subtype => x_subtype,
1013 x_protect_level => x_protect_level,
1014 x_custom_level => x_custom_level,
1015 x_format => x_format,
1016 x_text_default => l_text_default,
1017 x_number_default => l_number_default,
1018 x_date_default => l_date_default,
1019 x_value_type => x_value_type,
1020 x_display_name => n_dname,
1021 x_description => x_description,
1022 x_attach => x_attach
1023 );
1024 exception
1025 when NO_DATA_FOUND then
1026 -- Resequence attrs so that everything below the attr being
1027 -- inserted is shoved out of the way.
1028 select nvl(max(SEQUENCE), -1)+1
1029 into old_sequence
1030 from WF_MESSAGE_ATTRIBUTES
1031 where MESSAGE_TYPE = x_message_type
1032 and MESSAGE_NAME = x_message_name;
1033
1034 if (old_sequence <> x_sequence) then
1035 Wf_Load.Reseq_Message_Attribute(
1036 msgtype => x_message_type,
1037 msgname => x_message_name,
1038 oldseq => old_sequence,
1039 newseq => x_sequence);
1040 end if;
1041
1042 Wf_Message_Attributes_Pkg.Insert_Row(
1043 x_rowid => row_id,
1044 x_message_type => x_message_type,
1045 x_message_name => x_message_name,
1046 x_name => x_name,
1047 x_sequence => x_sequence,
1048 x_type => x_type,
1049 x_subtype => x_subtype,
1050 x_protect_level => x_protect_level,
1051 x_custom_level => x_custom_level,
1052 x_format => x_format,
1053 x_text_default => l_text_default,
1054 x_number_default => l_number_default,
1055 x_date_default => l_date_default,
1056 x_value_type => x_value_type,
1057 x_display_name => n_dname,
1058 x_description => x_description,
1059 x_attach => x_attach
1060 );
1061 end;
1062
1063 exception
1064 when OTHERS then
1065 Wf_Core.Context('Wf_Load', 'Upload_Message_Attribute', x_message_type,
1066 x_message_name, x_name);
1067 raise;
1068 end UPLOAD_MESSAGE_ATTRIBUTE;
1069
1070 --
1071 -- UPLOAD_ACTIVITY
1072 --
1073 procedure UPLOAD_ACTIVITY (
1074 x_item_type in varchar2,
1075 x_name in varchar2,
1076 x_display_name in varchar2,
1077 x_description in varchar2,
1078 x_type in varchar2,
1079 x_rerun in varchar2,
1080 x_protect_level in number,
1081 x_custom_level in number,
1082 x_effective_date in date,
1083 x_function in varchar2,
1084 x_function_type in varchar2,
1085 x_result_type in varchar2,
1086 x_cost in number,
1087 x_read_role in varchar2,
1088 x_write_role in varchar2,
1089 x_execute_role in varchar2,
1090 x_icon_name in varchar2,
1091 x_message in varchar2,
1092 x_error_process in varchar2,
1093 x_expand_role in varchar2,
1094 x_error_item_type in varchar2,
1095 x_runnable_flag in varchar2,
1096 x_event_filter in varchar2 ,
1097 x_event_type in varchar2 ,
1098 x_log_message out NOCOPY varchar2,
1099 x_version out NOCOPY number,
1100 x_level_error out NOCOPY number
1101 ) is
1102 row_id varchar2(30);
1103 protection_level number;
1104 customization_level number;
1105 old_version number := '';
1106 old_begin_date date := '';
1107 old_end_date date := '';
1108 new_version number;
1109 dummy pls_integer;
1110 noinsert pls_integer := -1; /* always insert by default */
1111 dummy_log_message varchar2(32000);
1112 dummy_version number;
1113 dummy_level_error number;
1114 conflict_name varchar2(240);
1115 l_dname varchar2(240);
1116 n_dname varchar2(240);
1117 l_name varchar2(30);
1118 begin
1119 -- Reset any caches that might be running.
1120 WF_CACHE.Reset;
1121
1122 -- Check protection level,
1123 -- and get version number and begin/end-dates for version currently
1124 -- active for x_effective_date.
1125 x_level_error := 0;
1126 begin
1127 select PROTECT_LEVEL, CUSTOM_LEVEL, VERSION, BEGIN_DATE, END_DATE
1128 into protection_level, customization_level,
1129 old_version, old_begin_date, old_end_date
1130 from WF_ACTIVITIES_VL
1131 where ITEM_TYPE = x_item_type
1132 and NAME = x_name
1133 and x_effective_date >= BEGIN_DATE
1134 and x_effective_date < nvl(END_DATE, x_effective_date+1);
1135
1136 if (x_type <> 'FOLDER') then
1137 if ((wf_core.upload_mode <> 'FORCE') and
1138 (protection_level < wf_core.session_level)) then
1139 x_level_error := 1;
1140 return;
1141 end if;
1142
1143 if ((wf_core.upload_mode = 'UPGRADE') and
1144 (customization_level > wf_core.session_level)) then
1145 x_level_error := 2;
1146 return;
1147 end if;
1148 end if;
1149 exception
1150 when NO_DATA_FOUND then
1151 -- No version active. Check for begin_date of next version
1152 -- after this one in the timeline and use that as the end date
1153 -- of the new version.
1154 -- If this still returns null, then either this is the first version
1155 -- to be entered or a previously deleted activity is being
1156 -- recreated. OK to leave end_date as null.
1157 select min(BEGIN_DATE)
1158 into old_end_date
1159 from WF_ACTIVITIES_VL
1160 where ITEM_TYPE = x_item_type
1161 and NAME = x_name
1162 and BEGIN_DATE >= x_effective_date;
1163
1164 -- Check protection level for new row
1165 if (x_type <> 'FOLDER') then
1166 if ((wf_core.upload_mode <> 'FORCE') and
1167 (x_protect_level < wf_core.session_level)) then
1168 x_level_error := 4+1;
1169 return;
1170 end if;
1171
1172 if ((wf_core.upload_mode = 'UPGRADE') and
1173 (x_custom_level > wf_core.session_level)) then
1174 x_level_error := 4+2;
1175 return;
1176 end if;
1177 end if;
1178 end;
1179
1180
1181 -- ### When it is ROOT FOLDER:
1182 -- ### New version is always the last version, 1 if no last version found.
1183 -- ### Instead of updating the end date according to the effective date,
1184 -- ### null the end date.
1185 -- ### Don't insert a row if it already exists.
1186
1187 if (x_name = 'ROOT' and x_type = 'FOLDER') then
1188 -- Get current version number, if not exist, use 1
1189 -- If version exists, noinsert is positive, that is not to insert.
1190 select nvl(max(VERSION), 1), count(1)
1191 into new_version, noinsert
1192 from WF_ACTIVITIES
1193 where ITEM_TYPE = x_item_type
1194 and NAME = x_name;
1195
1196 else
1197 -- Get a new version number
1198 select nvl(max(VERSION), 0) + 1
1199 into new_version
1200 from WF_ACTIVITIES
1201 where ITEM_TYPE = x_item_type
1202 and NAME = x_name;
1203
1204 -- Set the end_date of the old version covering x_effective_date to
1205 -- x_effective_date.
1206 update WF_ACTIVITIES set
1207 END_DATE = x_effective_date
1208 where ITEM_TYPE = x_item_type
1209 and NAME = x_name
1210 and VERSION = old_version;
1211 end if;
1212
1213 -- Check for unique index violations
1214 -- try to resolve the problem by appending '@'
1215 -- to the incoming display name
1216 -- for activity, we must have the specific version first.
1217 n_dname := x_display_name;
1218 begin
1219 -- l_name will be the old data to update
1220 select ITEM_TYPE||':'||NAME||':'||to_char(VERSION), DISPLAY_NAME, NAME
1221 into conflict_name, l_dname, l_name
1222 from WF_ACTIVITIES_VL
1223 where DISPLAY_NAME = n_dname
1224 and ITEM_TYPE = x_item_type
1225 and x_effective_date >= BEGIN_DATE
1226 and x_effective_date < nvl(END_DATE, x_effective_date+1)
1227 and NAME <> x_name;
1228
1229 n_dname := substrb('@'||l_dname, 1, 240);
1230
1231 -- this loop will make sure no duplicate with n_dname
1232 loop
1233 begin
1234 select ITEM_TYPE||':'||NAME||':'||to_char(VERSION), DISPLAY_NAME
1235 into conflict_name, l_dname
1236 from WF_ACTIVITIES_VL
1237 where DISPLAY_NAME = n_dname
1238 and ITEM_TYPE = x_item_type
1239 and x_effective_date >= BEGIN_DATE
1240 and x_effective_date < nvl(END_DATE, x_effective_date+1)
1241 and NAME <> l_name;
1242
1243 n_dname := substrb('@'||l_dname, 1, 240);
1244
1245 if ( n_dname = l_dname ) then
1246 Wf_Core.Token('DNAME', x_display_name);
1247 Wf_Core.Token('NAME', x_item_type||':'||x_name||':'||new_version);
1248 Wf_Core.Token('CONFLICT_NAME', conflict_name);
1249 Wf_Core.Raise('WFSQL_UNIQUE_NAME');
1250 exit;
1251 end if;
1252 exception
1253 when no_data_found then
1254 exit;
1255 end;
1256 end loop;
1257
1258 -- ### Not needed any more
1259 -- update the old data with the new display name
1260 -- begin
1261 -- update WF_ACTIVITIES_TL
1262 -- set display_name = n_dname
1263 -- where NAME = l_name
1264 -- and ITEM_TYPE = x_item_type
1265 -- and VERSION = new_version
1266 -- and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1267 -- exception
1268 -- when others then
1269 -- Wf_Core.Token('TABLE', 'ACTIVITIES_TL');
1270 -- Wf_Core.Token('VALUE', l_name);
1271 -- Wf_Core.Raise('WFSQL_UPDATE_FAIL');
1272 -- end;
1273 exception
1274 when no_data_found then
1275 null;
1276
1277 when others then
1278 raise;
1279 end;
1280
1281 --
1282 -- Insert a new row for the new version, starting at x_effective_date
1283 -- and ending at the end_date of the old version covering x_effective_date.
1284 if (noinsert <= 0) then
1285 if (x_event_filter is null and x_event_type = null) then
1286
1287 Wf_Activities_Pkg.Insert_Row(
1288 x_rowid => row_id,
1289 x_item_type => x_item_type,
1290 x_name => x_name,
1291 x_version => new_version,
1292 x_type => x_type,
1293 x_rerun => x_rerun,
1294 x_expand_role => x_expand_role,
1295 x_protect_level => x_protect_level,
1296 x_custom_level => x_custom_level,
1297 x_begin_date => x_effective_date,
1298 x_end_date => old_end_date,
1299 x_function => x_function,
1300 x_function_type => x_function_type,
1301 x_result_type => x_result_type,
1302 x_cost => x_cost,
1303 x_read_role => x_read_role,
1304 x_write_role => x_write_role,
1305 x_execute_role => x_execute_role,
1306 x_icon_name => x_icon_name,
1307 x_message => x_message,
1308 x_error_process => x_error_process,
1309 x_display_name => n_dname,
1310 x_description => x_description,
1311 x_error_item_type => x_error_item_type,
1312 x_runnable_flag => x_runnable_flag
1313 );
1314
1315 else
1316
1317 Wf_Activities_Pkg.Insert_Row(
1318 x_rowid => row_id,
1319 x_item_type => x_item_type,
1320 x_name => x_name,
1321 x_version => new_version,
1322 x_type => x_type,
1323 x_rerun => x_rerun,
1324 x_expand_role => x_expand_role,
1325 x_protect_level => x_protect_level,
1326 x_custom_level => x_custom_level,
1327 x_begin_date => x_effective_date,
1328 x_end_date => old_end_date,
1329 x_function => x_function,
1330 x_function_type => x_function_type,
1331 x_result_type => x_result_type,
1332 x_cost => x_cost,
1333 x_read_role => x_read_role,
1334 x_write_role => x_write_role,
1335 x_execute_role => x_execute_role,
1336 x_icon_name => x_icon_name,
1337 x_message => x_message,
1338 x_error_process => x_error_process,
1339 x_display_name => n_dname,
1340 x_description => x_description,
1341 x_error_item_type => x_error_item_type,
1342 x_runnable_flag => x_runnable_flag,
1343 x_event_filter => x_event_filter,
1344 x_event_type => x_event_type
1345 );
1346 end if;
1347
1348 -- handle the extra log message for display name conflict
1349 x_log_message := Wf_Load.logbuf;
1350 -- if the message is not empty, there is a conflict.
1351 if (x_log_message is not null or x_log_message <> '') then
1352 x_level_error := 16;
1353 Wf_Load.logbuf := ''; -- clear the buffer now
1354 end if;
1355
1356 if (not (x_name = 'ROOT' and x_type = 'FOLDER')) then
1357 WF_LOAD.UPLOAD_ACTIVITY(
1358 x_item_type=>x_item_type,
1359 x_name=>'ROOT',
1360 x_type=>'FOLDER',
1361 x_display_name=>'ROOT',
1362 x_description=>'',
1363 x_rerun=>'RESET',
1364 x_protect_level=>x_protect_level,
1365 x_custom_level=>x_custom_level,
1366 x_effective_date=>x_effective_date,
1367 x_function=>'',
1368 x_function_type=>'',
1369 x_result_type=>'*',
1370 x_cost=>0,
1371 x_read_role=>'',
1372 x_write_role=>'',
1373 x_execute_role=>'',
1374 x_icon_name=>'ROOT',
1375 x_message=>'',
1376 x_error_process=>'',
1377 x_expand_role=>'N',
1378 x_error_item_type =>'WFERROR',
1379 x_runnable_flag =>'N',
1380 x_log_message=>dummy_log_message,
1381 x_version=>dummy_version,
1382 x_level_error=>dummy_level_error);
1383 end if;
1384 else
1385 -- If you got here, this must be a ROOT FOLDER.
1386 -- Null the end_date of the new version of ROOT FOLDER,
1387 -- in case it was set (deleted but not purged previously).
1388
1389 if (old_begin_date is null) then
1390 -- only have future definition, need to update the begin date also
1391 update WF_ACTIVITIES
1392 set BEGIN_DATE = x_effective_date,
1393 END_DATE = to_date(NULL)
1394 where ITEM_TYPE = x_item_type
1395 and NAME = x_name
1396 and VERSION = new_version;
1397
1398 -- since we move the begin date, we'd better make sure to
1399 -- delete all the previous root versions that fall into this
1400 -- date range. This is a safe guard for old data from
1401 -- pre WF 2.5 days where there maybe multiple root versions.
1402 -- ### umm... maybe we should skip this.
1403 -- ### most customer would not have such problem.
1404 -- ### in order to delete these, we may need to do a more
1405 -- ### complicated cursor query.
1406 else
1407 update WF_ACTIVITIES
1408 set END_DATE = to_date(NULL)
1409 where ITEM_TYPE = x_item_type
1410 and NAME = x_name
1411 and VERSION = new_version;
1412 end if;
1413 end if;
1414
1415 x_version := new_version;
1416
1417 /* ### Should not have a commit in this API */
1418 /* ### commit; */
1419 exception
1420 when OTHERS then
1421 Wf_Core.Context('Wf_Load', 'Upload_Activity', x_item_type, x_name);
1422 raise;
1423 end UPLOAD_ACTIVITY;
1424
1425 --
1426 -- provide the old 2.5 version of signature for forward compatibility
1427 -- this is used by other product teams
1428 --
1429 procedure UPLOAD_ACTIVITY (
1430 x_item_type in varchar2,
1431 x_name in varchar2,
1432 x_display_name in varchar2,
1433 x_description in varchar2,
1434 x_type in varchar2,
1435 x_rerun in varchar2,
1436 x_protect_level in number,
1437 x_custom_level in number,
1438 x_effective_date in date,
1439 x_function in varchar2,
1440 x_function_type in varchar2,
1441 x_result_type in varchar2,
1442 x_cost in number,
1443 x_read_role in varchar2,
1444 x_write_role in varchar2,
1445 x_execute_role in varchar2,
1446 x_icon_name in varchar2,
1447 x_message in varchar2,
1448 x_error_process in varchar2,
1449 x_expand_role in varchar2,
1450 x_error_item_type in varchar2,
1451 x_runnable_flag in varchar2,
1452 x_version out NOCOPY number,
1453 x_level_error out NOCOPY number
1454 ) is
1455 dummy_log_message varchar2(32000);
1456 begin
1457 -- call the real UPLOAD_ACTIVITY and ignore the dummy_log_message
1458 WF_LOAD.UPLOAD_ACTIVITY(
1459 x_item_type=>x_item_type,
1460 x_name=>x_name,
1461 x_type=>x_type,
1462 x_display_name=>x_display_name,
1463 x_description=>x_description,
1464 x_rerun=>x_rerun,
1465 x_protect_level=>x_protect_level,
1466 x_custom_level=>x_custom_level,
1467 x_effective_date=>x_effective_date,
1468 x_function=>x_function,
1469 x_function_type=>x_function_type,
1470 x_result_type=>x_result_type,
1471 x_cost=>x_cost,
1472 x_read_role=>x_read_role,
1473 x_write_role=>x_write_role,
1474 x_execute_role=>x_execute_role,
1475 x_icon_name=>x_icon_name,
1476 x_message=>x_message,
1477 x_error_process=>x_error_process,
1478 x_expand_role=>x_expand_role,
1479 x_error_item_type =>x_error_item_type,
1480 x_runnable_flag =>x_runnable_flag,
1481 x_event_filter => null,
1482 x_event_type => null,
1483 x_log_message=>dummy_log_message,
1484 x_version=>x_version,
1485 x_level_error=>x_level_error
1486 );
1487 end UPLOAD_ACTIVITY;
1488
1489 --
1490 -- Reseq_Activity_Attribute (PRIVATE)
1491 -- Resequence attributes in the db to match the sequence of attrs
1492 -- being uploaded. This is needed to avoid unique index violations
1493 -- on the sequence when uploading reordered attributes.
1494 -- IN
1495 -- acttype - Activity type of activity owning attrs
1496 -- actname - Activity name of activity owning attrs
1497 -- actver - Activity version of activity owning attrs
1498 -- oldseq - Original sequence number of attr being uploaded
1499 -- newseq - New sequence number of attribute
1500 -- NOTE
1501 -- This isn't technically necessary for activity attrs yet, since
1502 -- new versions are always created. This is only in case we ever
1503 -- decide to merge activity versions instead of always creating
1504 -- new ones.
1505 --
1506 --
1507 procedure Reseq_Activity_Attribute(
1508 acttype in varchar2,
1509 actname in varchar2,
1510 actver in number,
1511 oldseq in number,
1512 newseq in number)
1513 is
1514 begin
1515 -- Move attr being updated to a placeholder out of the way.
1516 update WF_ACTIVITY_ATTRIBUTES set
1517 SEQUENCE = -1
1518 where ACTIVITY_ITEM_TYPE = acttype
1519 and ACTIVITY_NAME = actname
1520 and ACTIVITY_VERSION = actver
1521 and SEQUENCE = oldseq;
1522
1523 if (oldseq < newseq) then
1524 -- Move attrs DOWN in sequence to make room at higher position
1525 for i in (oldseq + 1) .. newseq loop
1526 update WF_ACTIVITY_ATTRIBUTES set
1527 SEQUENCE = SEQUENCE - 1
1528 where ACTIVITY_ITEM_TYPE = acttype
1529 and ACTIVITY_NAME = actname
1530 and ACTIVITY_VERSION = actver
1531 and SEQUENCE = i;
1532 end loop;
1533 elsif (oldseq > newseq) then
1534 -- Move attrs UP in sequence to make room at lower position
1535 for i in reverse newseq .. (oldseq - 1) loop
1536 update WF_ACTIVITY_ATTRIBUTES set
1537 SEQUENCE = SEQUENCE + 1
1538 where ACTIVITY_ITEM_TYPE = acttype
1539 and ACTIVITY_NAME = actname
1540 and ACTIVITY_VERSION = actver
1541 and SEQUENCE = i;
1542 end loop;
1543 end if;
1544
1545 -- Move attr being updated into new sequence position
1546 update WF_ACTIVITY_ATTRIBUTES set
1547 SEQUENCE = newseq
1548 where ACTIVITY_ITEM_TYPE = acttype
1549 and ACTIVITY_NAME = actname
1550 and ACTIVITY_VERSION = actver
1551 and SEQUENCE = -1;
1552
1553 exception
1554 when others then
1555 Wf_Core.Context('Wf_Load', 'Reseq_Activity_Attribute', acttype,
1556 actname, to_char(actver), to_char(oldseq), to_char(newseq));
1557 raise;
1558 end Reseq_Activity_Attribute;
1559
1560 --
1561 -- UPLOAD_ACTIVITY_ATTRIBUTE
1562 --
1563 procedure UPLOAD_ACTIVITY_ATTRIBUTE (
1564 x_activity_item_type in varchar2,
1565 x_activity_name in varchar2,
1566 x_activity_version in number,
1567 x_name in varchar2,
1568 x_display_name in varchar2,
1569 x_description in varchar2,
1570 x_sequence in number,
1571 x_type in varchar2,
1572 x_protect_level in number,
1573 x_custom_level in number,
1574 x_subtype in varchar2,
1575 x_format in varchar2,
1576 x_default in varchar2,
1577 x_value_type in varchar2,
1578 x_level_error out NOCOPY number
1579 ) is
1580 row_id varchar2(30);
1581 protection_level number;
1582 customization_level number;
1583 l_text_default varchar2(4000) := '';
1584 l_number_default number := '';
1585 l_date_default date := '';
1586 old_sequence number;
1587 begin
1588 -- Reset any caches that might be running.
1589 WF_CACHE.Reset;
1590
1591 -- Translate x_default to appropriate type
1592 if ((x_value_type = 'CONSTANT') and (x_type = 'NUMBER')) then
1593 l_number_default := to_number(x_default);
1594 elsif ((x_value_type = 'CONSTANT') and (x_type = 'DATE')) then
1595 l_date_default := to_date(x_default, 'YYYY/MM/DD HH24:MI:SS');
1596 else
1597 l_text_default := x_default;
1598 end if;
1599
1600 -- Check protection level
1601 x_level_error := 0;
1602 begin
1603 select PROTECT_LEVEL, CUSTOM_LEVEL, SEQUENCE
1604 into protection_level, customization_level, old_sequence
1605 from WF_ACTIVITY_ATTRIBUTES_VL
1606 where ACTIVITY_ITEM_TYPE = x_activity_item_type
1607 and ACTIVITY_NAME = x_activity_name
1608 and ACTIVITY_VERSION = x_activity_version
1609 and NAME = x_name;
1610
1611 if ((wf_core.upload_mode <> 'FORCE') and
1612 (protection_level < wf_core.session_level)) then
1613 x_level_error := 1;
1614 return;
1615 end if;
1616
1617 if ((wf_core.upload_mode = 'UPGRADE') and
1618 (customization_level > wf_core.session_level)) then
1619 x_level_error := 2;
1620 return;
1621 end if;
1622
1623 -- Resequence attrs in db to match sequence being uploaded
1624 if (old_sequence <> x_sequence) then
1625 Wf_Load.Reseq_Activity_Attribute(
1626 acttype => x_activity_item_type,
1627 actname => x_activity_name,
1628 actver => x_activity_version,
1629 oldseq => old_sequence,
1630 newseq => x_sequence);
1631 end if;
1632
1633 -- Update existing row
1634 Wf_Activity_Attributes_Pkg.Update_Row(
1635 x_activity_item_type => x_activity_item_type,
1636 x_activity_name => x_activity_name,
1637 x_activity_version => x_activity_version,
1638 x_name => x_name,
1639 x_sequence => x_sequence,
1640 x_type => x_type,
1641 x_value_type => x_value_type,
1642 x_protect_level => x_protect_level,
1643 x_custom_level => x_custom_level,
1644 x_subtype => x_subtype,
1645 x_format => x_format,
1646 x_text_default => l_text_default,
1647 x_number_default => l_number_default,
1648 x_date_default => l_date_default,
1649 x_display_name => x_display_name,
1650 x_description => x_description
1651 );
1652 exception
1653 when NO_DATA_FOUND then
1654 -- Check protection level for new row
1655 -- ### Relax the checking on attributes, lookup_code, transitions
1656 if ((wf_core.upload_mode <> 'FORCE') and
1657 (x_protect_level < wf_core.session_level)) then
1658 x_level_error := 1;
1659 return;
1660 end if;
1661
1662 if ((wf_core.upload_mode = 'UPGRADE') and
1663 (x_custom_level > wf_core.session_level)) then
1664 x_level_error := 2;
1665 return;
1666 end if;
1667
1668 -- Resequence attrs so that everything below the attr being
1669 -- inserted is shoved out of the way.
1670 select nvl(max(SEQUENCE), -1)+1
1671 into old_sequence
1672 from WF_ACTIVITY_ATTRIBUTES
1673 where ACTIVITY_ITEM_TYPE = x_activity_item_type
1674 and ACTIVITY_NAME = x_activity_name
1675 and ACTIVITY_VERSION = x_activity_version;
1676
1677 if (old_sequence <> x_sequence) then
1678 Wf_Load.Reseq_Activity_Attribute(
1679 acttype => x_activity_item_type,
1680 actname => x_activity_name,
1681 actver => x_activity_version,
1682 oldseq => old_sequence,
1683 newseq => x_sequence);
1684 end if;
1685
1686 -- Insert new row
1687 Wf_Activity_Attributes_Pkg.Insert_Row(
1688 x_rowid => row_id,
1689 x_activity_item_type => x_activity_item_type,
1690 x_activity_name => x_activity_name,
1691 x_activity_version => x_activity_version,
1692 x_name => x_name,
1693 x_sequence => x_sequence,
1694 x_type => x_type,
1695 x_value_type => x_value_type,
1696 x_protect_level => x_protect_level,
1697 x_custom_level => x_custom_level,
1698 x_subtype => x_subtype,
1699 x_format => x_format,
1700 x_text_default => l_text_default,
1701 x_number_default => l_number_default,
1702 x_date_default => l_date_default,
1703 x_display_name => x_display_name,
1704 x_description => x_description
1705 );
1706 end;
1707
1708 exception
1709 when OTHERS then
1710 Wf_Core.Context('Wf_Load', 'Upload_Activity_Attribute',
1711 x_activity_item_type, x_activity_name,
1712 to_char(x_activity_version), x_name);
1713 raise;
1714 end UPLOAD_ACTIVITY_ATTRIBUTE;
1715
1716 --
1717 -- UPLOAD_PROCESS_ACTIVITY
1718 --
1719 procedure UPLOAD_PROCESS_ACTIVITY (
1720 x_process_item_type in varchar2,
1721 x_process_name in varchar2,
1722 x_process_version in number,
1723 x_activity_item_type in varchar2,
1724 x_activity_name in varchar2,
1725 x_instance_id in out NOCOPY number,
1726 x_instance_label in varchar2,
1727 x_protect_level in number,
1728 x_custom_level in number,
1729 x_start_end in varchar2,
1730 x_default_result in varchar2,
1731 x_icon_geometry in varchar2,
1732 x_perform_role in varchar2,
1733 x_perform_role_type in varchar2,
1734 x_user_comment in varchar2,
1735 x_level_error out NOCOPY number
1736 ) is
1737 protection_level number;
1738 customization_level number;
1739 new_version number;
1740 root_instance_id number := 0;
1741 noinsert pls_integer := 1;
1742 dummy_log_message varchar2(32000);
1743 dummy_version number;
1744 dummy_level_error number;
1745 -- has_performer number := 0;
1746 l_perform_role varchar2(320);
1747 l_perform_role_type varchar2(8);
1748
1749 role_info_tbl wf_directory.wf_local_roles_tbl_type;
1750 begin
1751 -- Reset any caches that might be running.
1752 WF_CACHE.Reset;
1753
1754 -- Check protection level
1755 x_level_error := 0;
1756 begin
1757 -- instance_id of zero means generate locally
1758 if (x_instance_id = 0) then
1759 select WF_PROCESS_ACTIVITIES_S.NEXTVAL
1760 into x_instance_id
1761 from sys.dual;
1762
1763 raise NO_DATA_FOUND; --jump to insert
1764 else
1765 select PROTECT_LEVEL, CUSTOM_LEVEL
1766 into protection_level, customization_level
1767 from WF_PROCESS_ACTIVITIES
1768 where INSTANCE_ID = x_instance_id;
1769
1770 if (x_process_name <> 'ROOT') then
1771 if ((wf_core.upload_mode <> 'FORCE') and
1772 (protection_level < wf_core.session_level)) then
1773 x_level_error := 1;
1774 return;
1775 end if;
1776
1777 if ((wf_core.upload_mode = 'UPGRADE') and
1778 (customization_level > wf_core.session_level)) then
1779 x_level_error := 2;
1780 return;
1781 end if;
1782 end if;
1783 end if;
1784
1785 -- Validate PERFORM_ROLE
1786 l_perform_role_type := substr(x_perform_role_type, 1, 8);
1787 l_perform_role := substr(x_perform_role, 1, 320);
1788 if (l_perform_role_type = 'DEFER') then
1789 begin
1790 select NAME into l_perform_role
1791 from WF_ROLES
1792 where DISPLAY_NAME = x_perform_role
1793 and rownum < 2;
1794 exception
1795 when NO_DATA_FOUND then
1796 null;
1797 end;
1798 l_perform_role_type := 'CONSTANT'; -- reset to CONSTANT
1799 end if;
1800
1801 -- if a performer is defined, check the validity and report error
1802 -- don't bother to make sure that tye activity type is NOTICE
1803 if (l_perform_role_type = 'CONSTANT' and l_perform_role is not null) then
1804 Wf_Directory.GetRoleInfo2(l_perform_role,role_info_tbl);
1805 if (role_info_tbl(1).name is null) then
1806 x_level_error := 8;
1807 end if;
1808 end if;
1809
1810 -- Update existing row
1811 update WF_PROCESS_ACTIVITIES set
1812 PROCESS_ITEM_TYPE = x_process_item_type,
1813 PROCESS_NAME = x_process_name,
1814 PROCESS_VERSION = x_process_version,
1815 ACTIVITY_ITEM_TYPE = x_activity_item_type,
1816 ACTIVITY_NAME = x_activity_name,
1817 INSTANCE_LABEL = x_instance_label,
1818 PROTECT_LEVEL = x_protect_level,
1819 CUSTOM_LEVEL = x_custom_level,
1820 START_END = x_start_end,
1821 DEFAULT_RESULT = x_default_result,
1822 ICON_GEOMETRY = x_icon_geometry,
1823 PERFORM_ROLE = l_perform_role,
1824 PERFORM_ROLE_TYPE = l_perform_role_type,
1825 USER_COMMENT = x_user_comment
1826 where INSTANCE_ID = x_instance_id;
1827 exception
1828 when NO_DATA_FOUND then
1829 -- Check protection level for new row
1830 if ((wf_core.upload_mode <> 'FORCE') and
1831 (x_protect_level < wf_core.session_level)) then
1832 x_level_error := 4+1;
1833 return;
1834 end if;
1835
1836 if ((wf_core.upload_mode = 'UPGRADE') and
1837 (x_custom_level > wf_core.session_level)) then
1838 x_level_error := 4+2;
1839 return;
1840 end if;
1841
1842 -- Validate PERFORM_ROLE
1843 l_perform_role_type := substr(x_perform_role_type, 1, 8);
1844 l_perform_role := substr(x_perform_role, 1, 320);
1845 if (l_perform_role_type = 'DEFER') then
1846 begin
1847 select NAME into l_perform_role
1848 from WF_ROLES
1849 where DISPLAY_NAME = x_perform_role
1850 and rownum < 2;
1851 exception
1852 when NO_DATA_FOUND then
1853 null;
1854 end;
1855 l_perform_role_type := 'CONSTANT'; -- reset to CONSTANT
1856 end if;
1857 if (l_perform_role_type = 'CONSTANT' and l_perform_role is not null) then
1858 Wf_Directory.GetRoleInfo2(l_perform_role,role_info_tbl);
1859 if (role_info_tbl(1).name is null) then
1860 x_level_error := 8;
1861 end if;
1862 end if;
1863
1864 /* ### may not needed it, but just in case */
1865 -- Create a ROOT FOLDER if it does not exist
1866 --
1867 -- If version exists, noinsert is positive, that is not to insert.
1868 select nvl(max(VERSION), 1), count(1)
1869 into new_version, noinsert
1870 from WF_ACTIVITIES
1871 where ITEM_TYPE = x_process_item_type
1872 and NAME = 'ROOT';
1873
1874 if (noinsert <= 0) then
1875 WF_LOAD.UPLOAD_ACTIVITY(
1876 x_item_type=>x_process_item_type,
1877 x_name=>'ROOT',
1878 x_type=>'FOLDER',
1879 x_display_name=>'ROOT',
1880 x_description=>'',
1881 x_rerun=>'RESET',
1882 x_protect_level=>x_protect_level,
1883 x_custom_level=>x_custom_level,
1884 x_effective_date=>sysdate,
1885 x_function=>'',
1886 x_function_type =>'',
1887 x_result_type=>'*',
1888 x_cost=>0,
1889 x_read_role=>'',
1890 x_write_role=>'',
1891 x_execute_role=>'',
1892 x_icon_name=>'ROOT',
1893 x_message=>'',
1894 x_error_process=>'',
1895 x_expand_role=>'N',
1896 x_error_item_type =>'WFERROR',
1897 x_runnable_flag =>'N',
1898 x_event_filter => '',
1899 x_event_type => '',
1900 x_log_message=>dummy_log_message,
1901 x_version=>dummy_version,
1902 x_level_error=>dummy_level_error);
1903 end if;
1904
1905 -- If noinsert is positive, that is not to insert.
1906 -- Check if ROOT process of such activity has already been inserted,
1907 -- since each process activity must be attached to ROOT.
1908 select count(1)
1909 into noinsert
1910 from WF_PROCESS_ACTIVITIES
1911 where PROCESS_NAME = 'ROOT'
1912 and ACTIVITY_ITEM_TYPE = x_process_item_type
1913 and ACTIVITY_NAME = x_activity_name
1914 and rownum=1;
1915
1916 -- no need to insert when ROOT already exists (noinsert > 0)
1917 if (x_process_name <> 'ROOT' or noinsert <= 0) then
1918 -- Insert new row
1919 insert into WF_PROCESS_ACTIVITIES (
1920 PROCESS_ITEM_TYPE,
1921 PROCESS_NAME,
1922 PROCESS_VERSION,
1923 ACTIVITY_ITEM_TYPE,
1924 ACTIVITY_NAME,
1925 INSTANCE_ID,
1926 INSTANCE_LABEL,
1927 PROTECT_LEVEL,
1928 CUSTOM_LEVEL,
1929 START_END,
1930 DEFAULT_RESULT,
1931 ICON_GEOMETRY,
1932 PERFORM_ROLE,
1933 PERFORM_ROLE_TYPE,
1934 USER_COMMENT
1935 ) values (
1936 x_process_item_type,
1937 x_process_name,
1938 x_process_version,
1939 x_activity_item_type,
1940 x_activity_name,
1941 x_instance_id,
1942 x_instance_label,
1943 x_protect_level,
1944 x_custom_level,
1945 x_start_end,
1946 x_default_result,
1947 x_icon_geometry,
1948 l_perform_role,
1949 l_perform_role_type,
1950 x_user_comment
1951 );
1952 end if;
1953
1954 --
1955 -- Create a new ROOT process if it does not exist
1956 -- if process name is ROOT, it should be inserted by the above
1957 -- statement.
1958 --
1959 if (x_process_name <> 'ROOT' and noinsert <= 0) then
1960 -- Insert a root process activity
1961 WF_LOAD.UPLOAD_PROCESS_ACTIVITY(
1962 x_process_item_type=>x_process_item_type,
1963 x_process_name=>'ROOT',
1964 x_process_version=>new_version,
1965 x_activity_item_type=>x_process_item_type,
1966 x_activity_name=>x_process_name,
1967 x_instance_id=>root_instance_id,
1968 x_instance_label=>x_process_name,
1969 x_protect_level=>x_protect_level,
1970 x_custom_level=>x_custom_level,
1971 x_start_end=>'',
1972 x_default_result=>'',
1973 x_icon_geometry=>'',
1974 x_perform_role=>'',
1975 x_perform_role_type=>'CONSTANT',
1976 x_user_comment=>'',
1977 x_level_error=>dummy_level_error
1978 );
1979 end if;
1980
1981 end;
1982
1983 exception
1984 when OTHERS then
1985 Wf_Core.Context('Wf_Load', 'Upload_Process_Activity',x_process_name,
1986 x_activity_name,
1987 to_char(x_instance_id));
1988 raise;
1989 end UPLOAD_PROCESS_ACTIVITY;
1990
1991 --
1992 -- UPLOAD_ACTIVITY_ATTR_VALUE
1993 --
1994 procedure UPLOAD_ACTIVITY_ATTR_VALUE (
1995 x_process_activity_id in number,
1996 x_name in varchar2,
1997 x_protect_level in number,
1998 x_custom_level in number,
1999 x_value in varchar2,
2000 x_value_type in varchar2,
2001 x_effective_date in date,
2002 x_level_error out NOCOPY number
2003 ) is
2004 protection_level number;
2005 customization_level number;
2006 l_type varchar2(8);
2007 l_text_value varchar2(4000) := '';
2008 l_number_value number := '';
2009 l_date_value date := '';
2010 begin
2011 -- Reset any caches that might be running.
2012 WF_CACHE.Reset;
2013
2014 -- Translate x_value to appropriate type.
2015 -- First have to get the type of this activity attr.
2016 --
2017 -- Special handles the hidden attributes first.
2018 -- add future hidden attributes here.
2019 if (x_name = '#TIMEOUT' or
2020 x_name = '#PRIORITY') then
2021 l_type := 'NUMBER';
2022 elsif (x_name = '#EVENTNAME' or x_name = '#EVENTKEY' or
2023 x_name = '#EVENTMESSAGE' or x_name = '#EVENTOUTAGENT' or
2024 x_name = '#EVENTTOAGENT') then
2025 l_type := 'TEXT';
2026 else
2027 --
2028 -- Handle regular attributes here
2029 --
2030 begin
2031 select WAA.TYPE
2032 into l_type
2033 from WF_PROCESS_ACTIVITIES WPA, WF_ACTIVITIES WA,
2034 WF_ACTIVITY_ATTRIBUTES WAA
2035 where WPA.INSTANCE_ID = x_process_activity_id
2036 and WPA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE
2037 and WPA.ACTIVITY_NAME = WA.NAME
2038 and x_effective_date >= WA.BEGIN_DATE
2039 and x_effective_date < nvl(WA.END_DATE, x_effective_date+1)
2040 and WA.ITEM_TYPE = WAA.ACTIVITY_ITEM_TYPE
2041 and WA.NAME = WAA.ACTIVITY_NAME
2042 and WA.VERSION = WAA.ACTIVITY_VERSION
2043 and WAA.NAME = x_name;
2044 exception
2045 when no_data_found then
2046 -- If not found, then activity must not have this attr defined.
2047 -- (This can happen if process and activity uploaded with inconsistent
2048 -- protection levels.)
2049 -- In this case ignore the attr value setting.
2050 return;
2051 end;
2052 end if;
2053
2054 -- Note for hidden attributes:
2055 -- When x_value_type is 'CONSTANT', l_type is always 'NUMBER'
2056 -- When x_value_type is 'ITEMATTR', l_type is always 'TEXT'
2057 --
2058 if ((x_value_type = 'CONSTANT') and (l_type = 'NUMBER')) then
2059 l_number_value := to_number(x_value);
2060 elsif ((x_value_type = 'CONSTANT') and (l_type = 'DATE')) then
2061 l_date_value := to_date(x_value, 'YYYY/MM/DD HH24:MI:SS');
2062 else
2063 l_text_value := x_value;
2064 end if;
2065
2066 -- Check protection level
2067 x_level_error := 0;
2068 begin
2069 select PROTECT_LEVEL, CUSTOM_LEVEL
2070 into protection_level, customization_level
2071 from WF_ACTIVITY_ATTR_VALUES
2072 where PROCESS_ACTIVITY_ID = x_process_activity_id
2073 and NAME = x_name;
2074
2075 if ((wf_core.upload_mode <> 'FORCE') and
2076 (protection_level < wf_core.session_level)) then
2077 x_level_error := 1;
2078 return;
2079 end if;
2080
2081 if ((wf_core.upload_mode = 'UPGRADE') and
2082 (customization_level > wf_core.session_level)) then
2083 x_level_error := 2;
2084 return;
2085 end if;
2086
2087 -- Update existing row
2088 update WF_ACTIVITY_ATTR_VALUES set
2089 PROTECT_LEVEL = x_protect_level,
2090 CUSTOM_LEVEL = x_custom_level,
2091 TEXT_VALUE = l_text_value,
2092 NUMBER_VALUE = l_number_value,
2093 DATE_VALUE = l_date_value,
2094 VALUE_TYPE = x_value_type
2095 where PROCESS_ACTIVITY_ID = x_process_activity_id
2096 and NAME = x_name;
2097 exception
2098 when NO_DATA_FOUND then
2099 -- Check protection level for new row
2100 -- ### Relax the checking on attributes, lookup_code, transitions
2101 if ((wf_core.upload_mode <> 'FORCE') and
2102 (x_protect_level < wf_core.session_level)) then
2103 x_level_error := 1;
2104 return;
2105 end if;
2106
2107 if ((wf_core.upload_mode = 'UPGRADE') and
2108 (x_custom_level > wf_core.session_level)) then
2109 x_level_error := 2;
2110 return;
2111 end if;
2112
2113 -- Insert new row
2114 insert into WF_ACTIVITY_ATTR_VALUES (
2115 PROCESS_ACTIVITY_ID,
2116 NAME,
2117 PROTECT_LEVEL,
2118 CUSTOM_LEVEL,
2119 TEXT_VALUE,
2120 NUMBER_VALUE,
2121 DATE_VALUE,
2122 VALUE_TYPE
2123 ) values (
2124 x_process_activity_id,
2125 x_name,
2126 x_protect_level,
2127 x_custom_level,
2128 l_text_value,
2129 l_number_value,
2130 l_date_value,
2131 x_value_type
2132 );
2133 end;
2134
2135 exception
2136 when OTHERS then
2137 Wf_Core.Context('Wf_Load', 'Upload_Activity_Attr_Value',
2138 to_char(x_process_activity_id), x_name);
2139 raise;
2140 end UPLOAD_ACTIVITY_ATTR_VALUE;
2141
2142 --
2143 -- UPLOAD_ACTIVITY_TRANSITION
2144 --
2145 procedure UPLOAD_ACTIVITY_TRANSITION (
2146 x_from_process_activity in number,
2147 x_result_code in varchar2,
2148 x_to_process_activity in number,
2149 x_protect_level in number,
2150 x_custom_level in number,
2151 x_arrow_geometry in varchar2,
2152 x_level_error out NOCOPY number
2153 ) is
2154 protection_level number;
2155 customization_level number;
2156 begin
2157 -- Reset any caches that might be running.
2158 WF_CACHE.Reset;
2159
2160 -- Check protection level
2161 x_level_error := 0;
2162 begin
2163 select PROTECT_LEVEL, CUSTOM_LEVEL
2164 into protection_level, customization_level
2165 from WF_ACTIVITY_TRANSITIONS
2166 where FROM_PROCESS_ACTIVITY = x_from_process_activity
2167 and RESULT_CODE = x_result_code
2168 and TO_PROCESS_ACTIVITY = x_to_process_activity;
2169
2170 if ((wf_core.upload_mode <> 'FORCE') and
2171 (protection_level < wf_core.session_level)) then
2172 x_level_error := 1;
2173 return;
2174 end if;
2175
2176 if ((wf_core.upload_mode = 'UPGRADE') and
2177 (customization_level > wf_core.session_level)) then
2178 x_level_error := 2;
2179 return;
2180 end if;
2181
2182 -- Update existing row
2183 update WF_ACTIVITY_TRANSITIONS set
2184 FROM_PROCESS_ACTIVITY = x_from_process_activity,
2185 RESULT_CODE = x_result_code,
2186 TO_PROCESS_ACTIVITY = x_to_process_activity,
2187 PROTECT_LEVEL = x_protect_level,
2188 CUSTOM_LEVEL = x_custom_level,
2189 ARROW_GEOMETRY = x_arrow_geometry
2190 where FROM_PROCESS_ACTIVITY = x_from_process_activity
2191 and RESULT_CODE = x_result_code
2192 and TO_PROCESS_ACTIVITY = x_to_process_activity;
2193 exception
2194 when NO_DATA_FOUND then
2195 -- Check protection level for new row
2196 -- ### Relax the checking on attributes, lookup_code, transitions
2197 if ((wf_core.upload_mode <> 'FORCE') and
2198 (x_protect_level < wf_core.session_level)) then
2199 x_level_error := 1;
2200 return;
2201 end if;
2202
2203 if ((wf_core.upload_mode = 'UPGRADE') and
2204 (x_custom_level > wf_core.session_level)) then
2205 x_level_error := 2;
2206 return;
2207 end if;
2208
2209 -- Insert new row
2210 insert into WF_ACTIVITY_TRANSITIONS (
2211 FROM_PROCESS_ACTIVITY,
2212 RESULT_CODE,
2213 TO_PROCESS_ACTIVITY,
2214 PROTECT_LEVEL,
2215 CUSTOM_LEVEL,
2216 ARROW_GEOMETRY
2217 ) values (
2218 x_from_process_activity,
2219 x_result_code,
2220 x_to_process_activity,
2221 x_protect_level,
2222 x_custom_level,
2223 x_arrow_geometry
2224 );
2225 end;
2226
2227 exception
2228 when OTHERS then
2229 Wf_Core.Context('Wf_Load', 'Upload_Activity_Transition',
2230 to_char(x_from_process_activity),
2231 x_result_code,
2232 to_char(x_to_process_activity));
2233 raise;
2234 end UPLOAD_ACTIVITY_TRANSITION;
2235
2236 --
2237 -- UPLOAD_RESOURCE
2238 --
2239 procedure UPLOAD_RESOURCE (
2240 x_type in varchar2,
2241 x_name in varchar2,
2242 x_protect_level in number,
2243 x_custom_level in number,
2244 x_id in number,
2245 x_text in varchar2,
2246 x_level_error out NOCOPY number
2247 ) is
2248
2249 begin
2250 WF_RESOURCE_LOAD.UPLOAD_RESOURCE(x_type,
2251 x_name,
2252 x_protect_level,
2253 x_custom_level,
2254 x_id,
2255 x_text,
2256 x_level_error);
2257
2258 exception
2259 when others then
2260 Wf_Core.Context('Wf_Load', 'Upload_Resource', x_name, x_type);
2261 raise;
2262 end UPLOAD_RESOURCE;
2263
2264 --
2265 -- DELETE_LOOKUP_TYPE
2266 --
2267 procedure DELETE_LOOKUP_TYPE(
2268 x_lookup_type in varchar2,
2269 x_level_error out NOCOPY number
2270 ) is
2271 protection_level number;
2272 customization_level number;
2273 begin
2274 -- Reset any caches that might be running.
2275 WF_CACHE.Reset;
2276
2277 -- Check protection level
2278 x_level_error := 0;
2279 select PROTECT_LEVEL, CUSTOM_LEVEL
2280 into protection_level, customization_level
2281 from WF_LOOKUP_TYPES
2282 where LOOKUP_TYPE = x_lookup_type;
2283
2284 if ((wf_core.upload_mode <> 'FORCE') and
2285 (protection_level < wf_core.session_level)) then
2286 x_level_error := 1;
2287 return;
2288 end if;
2289
2290 if ((wf_core.upload_mode = 'UPGRADE') and
2291 (customization_level > wf_core.session_level)) then
2292 x_level_error := 2;
2293 return;
2294 end if;
2295
2296 -- Delete child lookups of this type
2297 Delete_Lookups(x_lookup_type, x_level_error);
2298
2299 Wf_Lookup_Types_Pkg.Delete_Row(x_lookup_type => x_lookup_type);
2300
2301 exception
2302 when NO_DATA_FOUND then
2303 null;
2304 when OTHERS then
2305 Wf_Core.Context('Wf_Load', 'Delete_Lookup_Type', x_lookup_type);
2306 raise;
2307 end DELETE_LOOKUP_TYPE;
2308
2309 --
2310 -- DELETE_LOOKUP
2311 --
2312 procedure DELETE_LOOKUP(
2313 x_lookup_type in varchar2,
2314 x_lookup_code in varchar2,
2315 x_level_error out NOCOPY number
2316 ) is
2317 protection_level number;
2318 customization_level number;
2319 begin
2320 -- Reset any caches that might be running.
2321 WF_CACHE.Reset;
2322
2323 -- Check protection level
2324 x_level_error := 0;
2325 select PROTECT_LEVEL, CUSTOM_LEVEL
2326 into protection_level, customization_level
2327 from WF_LOOKUPS
2328 where LOOKUP_TYPE = x_lookup_type
2329 and LOOKUP_CODE = x_lookup_code;
2330
2331 if ((wf_core.upload_mode <> 'FORCE') and
2332 (protection_level < wf_core.session_level)) then
2333 x_level_error := 1;
2334 return;
2335 end if;
2336
2337 if ((wf_core.upload_mode = 'UPGRADE') and
2338 (customization_level > wf_core.session_level)) then
2339 x_level_error := 2;
2340 return;
2341 end if;
2342
2343 Wf_Lookups_Pkg.Delete_Row(
2344 x_lookup_type => x_lookup_type,
2345 x_lookup_code => x_lookup_code
2346 );
2347
2348 exception
2349 when NO_DATA_FOUND then
2350 null;
2351 when OTHERS then
2352 Wf_Core.Context('Wf_Load', 'Delete_Lookup', x_lookup_type, x_lookup_code);
2353 raise;
2354 end DELETE_LOOKUP;
2355
2356 --
2357 -- DELETE_LOOKUPS
2358 --
2359 procedure DELETE_LOOKUPS(
2360 x_lookup_type in varchar2,
2361 x_level_error out NOCOPY number
2362 ) is
2363 protection_level number;
2364 customization_level number;
2365 begin
2366 -- Reset any caches that might be running.
2367 WF_CACHE.Reset;
2368
2369 X_LEVEL_ERROR := 0;
2370 select MIN(protect_level), MAX(custom_level)
2371 into protection_level, customization_level
2372 from WF_LOOKUP_TYPES_TL
2373 where LOOKUP_TYPE = X_LOOKUP_TYPE;
2374
2375 if ((wf_core.upload_mode <> 'FORCE') and
2376 (protection_level < wf_core.session_level)) then
2377 x_level_error := 1;
2378 return;
2379 end if;
2380
2381 if ((wf_core.upload_mode = 'UPGRADE') and
2382 (customization_level > wf_core.session_level)) then
2383 x_level_error := 2;
2384 return;
2385 end if;
2386
2387 delete from WF_LOOKUPS_TL
2388 where LOOKUP_TYPE = X_LOOKUP_TYPE;
2389
2390 exception
2391 when NO_DATA_FOUND then
2392 null;
2393 when OTHERS then
2394 wf_core.context('WF_LOAD', 'DELETE_LOOKUPS');
2395 raise;
2396 end DELETE_LOOKUPS;
2397
2398 --
2399 -- DELETE_ITEM_TYPE
2400 --
2401 procedure DELETE_ITEM_TYPE(
2402 x_name in varchar2,
2403 x_level_error out NOCOPY number
2404 ) is
2405 protection_level number;
2406 customization_level number;
2407 l_persistence_type varchar2(8);
2408
2409 dummy number;
2410 fk_violation exception;
2411 pragma exception_init(fk_violation, -2292);
2412
2413 begin
2414 -- Reset any caches that might be running.
2415 WF_CACHE.Reset;
2416
2417 -- Check protection level
2418 x_level_error := 0;
2419 select PROTECT_LEVEL, CUSTOM_LEVEL, PERSISTENCE_TYPE
2420 into protection_level, customization_level, l_persistence_type
2421 from WF_ITEM_TYPES_VL
2422 where NAME = x_name;
2423
2424 if ((wf_core.upload_mode <> 'FORCE') and
2425 (protection_level < wf_core.session_level)) then
2426 x_level_error := 1;
2427 return;
2428 end if;
2429
2430 if ((wf_core.upload_mode = 'UPGRADE') and
2431 (customization_level > wf_core.session_level)) then
2432 x_level_error := 2;
2433 return;
2434 end if;
2435
2436 -- Set what persistence type to purge first
2437 Wf_Purge.persistence_type := l_persistence_type;
2438
2439 -- Purge obsolete and unused activities in this itemtype.
2440 -- This is to give some hope of being able to delete the itemtype
2441 -- if it is really no longer in use, without interference from
2442 -- obsolete activity versions.
2443 Wf_Purge.Activities(itemtype => x_name);
2444
2445 -- Delete item attributes
2446 Delete_Item_Attributes(x_name, x_level_error);
2447
2448 -- Double check for fk references before the actual delete,
2449 -- just in case constraints are missing or disabled.
2450 begin
2451 select 1
2452 into dummy
2453 from sys.dual
2454 where not exists
2455 (select 1
2456 from WF_LOOKUP_TYPES
2457 where ITEM_TYPE = x_name)
2458 and not exists
2459 (select 1
2460 from WF_ACTIVITIES
2461 where ITEM_TYPE = x_name)
2462 and not exists
2463 (select 1
2464 from WF_MESSAGES
2465 where TYPE = x_name);
2466 exception
2467 when no_data_found then
2468 -- Bad row found. Raise exception back to loader.
2469 raise fk_violation;
2470 end;
2471
2472 Wf_Item_Types_Pkg.Delete_Row(x_name=>x_name);
2473
2474 exception
2475 when NO_DATA_FOUND then
2476 null;
2477 when OTHERS then
2478 Wf_Core.Context('Wf_Load', 'Delete_Item_Type', x_name);
2479 raise;
2480 end DELETE_ITEM_TYPE;
2481
2482 --
2483 -- DELETE_ITEM_ATTRIBUTE
2484 --
2485 procedure DELETE_ITEM_ATTRIBUTE(
2486 x_item_type in varchar2,
2487 x_name in varchar2,
2488 x_level_error out NOCOPY number
2489 ) is
2490 protection_level number;
2491 customization_level number;
2492 begin
2493 -- Reset any caches that might be running.
2494 WF_CACHE.Reset;
2495
2496 -- Check protection level
2497 x_level_error := 0;
2498 select PROTECT_LEVEL, CUSTOM_LEVEL
2499 into protection_level, customization_level
2500 from WF_ITEM_ATTRIBUTES_VL
2501 where ITEM_TYPE = x_item_type
2502 and NAME = x_name;
2503
2504 if ((wf_core.upload_mode <> 'FORCE') and
2505 (protection_level < wf_core.session_level)) then
2506 x_level_error := 1;
2507 return;
2508 end if;
2509
2510 if ((wf_core.upload_mode = 'UPGRADE') and
2511 (customization_level > wf_core.session_level)) then
2512 x_level_error := 2;
2513 return;
2514 end if;
2515
2516 Wf_Item_Attributes_Pkg.Delete_Row(
2517 x_item_type => x_item_type,
2518 x_name => x_name);
2519
2520 exception
2521 when NO_DATA_FOUND then
2522 null;
2523 when OTHERS then
2524 Wf_Core.Context('Wf_Load', 'Delete_Item_Attribute', x_item_type, x_name);
2525 raise;
2526 end DELETE_ITEM_ATTRIBUTE;
2527
2528 --
2529 -- DELETE_ITEM_ATTRIBUTES
2530 --
2531 procedure DELETE_ITEM_ATTRIBUTES(
2532 x_item_type in varchar2,
2533 x_level_error out NOCOPY number
2534 ) is
2535 protection_level number;
2536 customization_level number;
2537 begin
2538 -- Reset any caches that might be running.
2539 WF_CACHE.Reset;
2540
2541 -- Check protection level
2542 x_level_error := 0;
2543 select PROTECT_LEVEL, CUSTOM_LEVEL
2544 into protection_level, customization_level
2545 from WF_ITEM_TYPES_VL
2546 where NAME = x_item_type;
2547
2548 if ((wf_core.upload_mode <> 'FORCE') and
2549 (protection_level < wf_core.session_level)) then
2550 x_level_error := 1;
2551 return;
2552 end if;
2553
2554 if ((wf_core.upload_mode = 'UPGRADE') and
2555 (customization_level > wf_core.session_level)) then
2556 x_level_error := 2;
2557 return;
2558 end if;
2559
2560 delete from WF_ITEM_ATTRIBUTES_TL
2561 where ITEM_TYPE = X_ITEM_TYPE;
2562
2563 delete from WF_ITEM_ATTRIBUTES
2564 where ITEM_TYPE = X_ITEM_TYPE;
2565 exception
2566 when NO_DATA_FOUND then
2567 null;
2568 when OTHERS then
2569 Wf_Core.Context('Wf_Load', 'Delete_Item_Attributes', x_item_type);
2570 raise;
2571 end DELETE_ITEM_ATTRIBUTES;
2572
2573 --
2574 -- DELETE_MESSAGE
2575 --
2576 procedure DELETE_MESSAGE(
2577 x_type in varchar2,
2578 x_name in varchar2,
2579 x_level_error out NOCOPY number
2580 ) is
2581 protection_level number;
2582 customization_level number;
2583
2584 dummy number;
2585 fk_violation exception;
2586 pragma exception_init(fk_violation, -2292);
2587
2588 begin
2589 -- Reset any caches that might be running.
2590 WF_CACHE.Reset;
2591
2592 -- Check protection level
2593 x_level_error := 0;
2594 select PROTECT_LEVEL, CUSTOM_LEVEL
2595 into protection_level, customization_level
2596 from WF_MESSAGES
2597 where TYPE = x_type
2598 and NAME = x_name;
2599
2600 if ((wf_core.upload_mode <> 'FORCE') and
2601 (protection_level < wf_core.session_level)) then
2602 x_level_error := 1;
2603 return;
2604 end if;
2605
2606 if ((wf_core.upload_mode = 'UPGRADE') and
2607 (customization_level > wf_core.session_level)) then
2608 x_level_error := 2;
2609 return;
2610 end if;
2611
2612 -- Delete message attributes
2613 Delete_Message_Attributes(x_type, x_name, x_level_error);
2614
2615 -- Double check for fk references before the actual delete,
2616 -- just in case constraints are missing or disabled.
2617 begin
2618 select 1
2619 into dummy
2620 from sys.dual
2621 where not exists
2622 (select 1
2623 from WF_ACTIVITIES
2624 where ITEM_TYPE = x_type
2625 and MESSAGE = x_name)
2626 and not exists
2627 (select 1
2628 from WF_NOTIFICATIONS
2629 where MESSAGE_TYPE = x_type
2630 and MESSAGE_NAME = x_name);
2631 exception
2632 when no_data_found then
2633 -- Bad row found. Raise exception back to loader.
2634 raise fk_violation;
2635 end;
2636
2637 Wf_Messages_Pkg.Delete_Row(x_type => x_type, x_name => x_name);
2638
2639 exception
2640 when NO_DATA_FOUND then
2641 null;
2642 when OTHERS then
2643 Wf_Core.Context('Wf_Load', 'Delete_Message', x_type, x_name);
2644 raise;
2645 end DELETE_MESSAGE;
2646
2647 --
2648 -- DELETE_MESSAGE_ATTRIBUTE
2649 --
2650 procedure DELETE_MESSAGE_ATTRIBUTE(
2651 x_message_type in varchar2,
2652 x_message_name in varchar2,
2653 x_name in varchar2,
2654 x_level_error out NOCOPY number
2655 ) is
2656 protection_level number;
2657 customization_level number;
2658 begin
2659
2660 -- Check protection level
2661 x_level_error := 0;
2662 select PROTECT_LEVEL, CUSTOM_LEVEL
2663 into protection_level, customization_level
2664 from WF_MESSAGE_ATTRIBUTES_VL
2665 where MESSAGE_TYPE = x_message_type
2666 and MESSAGE_NAME = x_message_name
2667 and NAME = x_name;
2668
2669 if ((wf_core.upload_mode <> 'FORCE') and
2670 (protection_level < wf_core.session_level)) then
2671 x_level_error := 1;
2672 return;
2673 end if;
2674
2675 if ((wf_core.upload_mode = 'UPGRADE') and
2676 (customization_level > wf_core.session_level)) then
2677 x_level_error := 2;
2678 return;
2679 end if;
2680
2681 Wf_Message_Attributes_Pkg.Delete_Row(
2682 x_message_type => x_message_type,
2683 x_message_name => x_message_name,
2684 x_name => x_name);
2685
2686 exception
2687 when NO_DATA_FOUND then
2688 null;
2689 when OTHERS then
2690 Wf_Core.Context('Wf_Load', 'Delete_Message_Attribute', x_message_type,
2691 x_message_name, x_name);
2692 raise;
2693 end DELETE_MESSAGE_ATTRIBUTE;
2694
2695 --
2696 -- DELETE_MESSAGE_ATTRIBUTES
2697 --
2698 procedure DELETE_MESSAGE_ATTRIBUTES(
2699 x_message_type in varchar2,
2700 x_message_name in varchar2,
2701 x_level_error out NOCOPY number
2702 ) is
2703 protection_level NUMBER;
2704 customization_level NUMBER;
2705 begin
2706 -- Reset any caches that might be running.
2707 WF_CACHE.Reset;
2708
2709 -- Check protection level
2710 x_level_error := 0;
2711 select PROTECT_LEVEL, CUSTOM_LEVEL
2712 into protection_level, customization_level
2713 from WF_MESSAGES
2714 where TYPE = x_message_type
2715 and NAME = x_message_name;
2716
2717 if ((wf_core.upload_mode <> 'FORCE') and
2718 (protection_level < wf_core.session_level)) then
2719 x_level_error := 1;
2720 return;
2721 end if;
2722
2723 if ((wf_core.upload_mode = 'UPGRADE') and
2724 (customization_level > wf_core.session_level)) then
2725 x_level_error := 2;
2726 return;
2727 end if;
2728
2729 delete from WF_MESSAGE_ATTRIBUTES_TL
2730 where MESSAGE_TYPE = x_message_type
2731 and MESSAGE_NAME = x_message_name;
2732
2733 delete from WF_MESSAGE_ATTRIBUTES
2734 where MESSAGE_TYPE = x_message_type
2735 and MESSAGE_NAME = x_message_name;
2736
2737 exception
2738 when NO_DATA_FOUND then
2739 null;
2740 when OTHERS then
2741 Wf_Core.Context('Wf_Load', 'Delete_Message_Attributes', x_message_type,
2742 x_message_name);
2743 raise;
2744 end DELETE_MESSAGE_ATTRIBUTES;
2745
2746 --
2747 -- DELETE_ACTIVITY
2748 --
2749 procedure DELETE_ACTIVITY(
2750 x_item_type in varchar2,
2751 x_name in varchar2,
2752 x_level_error out NOCOPY number
2753 ) is
2754 protection_level number;
2755 customization_level number;
2756 l_persistence_type varchar2(8);
2757 begin
2758 -- Reset any caches that might be running.
2759 WF_CACHE.Reset;
2760
2761 -- Check protection level
2762 x_level_error := 0;
2763
2764 select PROTECT_LEVEL, CUSTOM_LEVEL
2765 into protection_level, customization_level
2766 from WF_ACTIVITIES_VL
2767 where ITEM_TYPE = x_item_type
2768 and NAME = x_name
2769 and END_DATE is null;
2770
2771 if ((wf_core.upload_mode <> 'FORCE') and
2772 (protection_level < wf_core.session_level)) then
2773 x_level_error := 1;
2774 return;
2775 end if;
2776
2777 if ((wf_core.upload_mode = 'UPGRADE') and
2778 (customization_level > wf_core.session_level)) then
2779 x_level_error := 2;
2780 return;
2781 end if;
2782
2783 -- Do not delete, only set end_date
2784 update WF_ACTIVITIES set
2785 end_date = sysdate
2786 where ITEM_TYPE = x_item_type
2787 and NAME = x_name
2788 and END_DATE is null;
2789
2790 -- Find out what persistence type it belongs
2791 select PERSISTENCE_TYPE
2792 into l_persistence_type
2793 from WF_ITEM_TYPES
2794 where NAME = x_item_type;
2795
2796 Wf_Purge.persistence_type := l_persistence_type;
2797
2798 -- Purge obsolete and unused versions of the activity
2799 Wf_Purge.Activities(
2800 itemtype => x_item_type,
2801 name => x_name);
2802
2803 exception
2804 when NO_DATA_FOUND then
2805 null;
2806 when OTHERS then
2807 Wf_Core.Context('Wf_Load', 'Delete_Activity', x_item_type, x_name);
2808 raise;
2809 end DELETE_ACTIVITY;
2810
2811 --
2812 -- WebDB Integration
2813 --
2814
2815 --
2816 -- Delete_Transition
2817 -- IN
2818 -- p_previous_step - instance id of the FROM process activity
2819 -- p_next_step - instance id of the TO process activity
2820 -- P_result_code - result code of this transition
2821 -- NOTE
2822 -- It is possible to leave an invalid Workflow definition after this
2823 -- call.
2824 -- Ignores the criteria with a null arguement.
2825 -- p_previous_step and p_next_step cannot be both null.
2826 procedure Delete_Transition(
2827 p_previous_step in number ,
2828 p_next_step in number ,
2829 p_result_code in varchar2 )
2830 is
2831 begin
2832 -- Reset any caches that might be running.
2833 WF_CACHE.Reset;
2834
2835 if (p_previous_step is null and p_next_step is null) then
2836 -- do not delete anything if both are null
2837 return;
2838 end if;
2839
2840 if (p_next_step is null) then
2841 delete WF_ACTIVITY_TRANSITIONS
2842 where FROM_PROCESS_ACTIVITY = p_previous_step
2843 and RESULT_CODE = nvl(p_result_code, RESULT_CODE);
2844 else
2845 if (p_previous_step is null) then
2846 delete WF_ACTIVITY_TRANSITIONS
2847 where TO_PROCESS_ACTIVITY = p_next_step
2848 and RESULT_CODE = nvl(p_result_code, RESULT_CODE);
2849 else
2850 -- both are non-null
2851 delete WF_ACTIVITY_TRANSITIONS
2852 where FROM_PROCESS_ACTIVITY = p_previous_step
2853 and RESULT_CODE = nvl(p_result_code, RESULT_CODE)
2854 and TO_PROCESS_ACTIVITY = p_next_step;
2855 end if;
2856 end if;
2857 exception
2858 when OTHERS then
2859 Wf_Core.Context('Wf_Load', 'Delete_Transition', p_previous_step,
2860 p_next_step, p_result_code);
2861 raise;
2862 end;
2863
2864 --
2865 -- Get_Process_Activity
2866 -- IN
2867 -- p_activity_instance - instance id of a process activity
2868 -- OUT
2869 -- p_xcor - X coordinate of the icon geometry
2870 -- p_ycor - Y coordinate of the icon geometry
2871 -- p_activity_name - internal name of this process activity
2872 -- NOTE
2873 --
2874 procedure Get_Process_Activity(
2875 p_activity_instance in number,
2876 p_xcor out NOCOPY number,
2877 p_ycor out NOCOPY number,
2878 p_activity_name out NOCOPY varchar2,
2879 p_instance_label out NOCOPY varchar2)
2880 is
2881 l_icon_geometry varchar2(2000);
2882 comma_position number;
2883 begin
2884 -- Reset any caches that might be running.
2885 WF_CACHE.Reset;
2886
2887 select ICON_GEOMETRY, ACTIVITY_NAME, INSTANCE_LABEL
2888 into l_icon_geometry, p_activity_name, p_instance_label
2889 from WF_PROCESS_ACTIVITIES
2890 where instance_id = p_activity_instance;
2891
2892 comma_position := instr(l_icon_geometry, ',');
2893 p_xcor := to_number(substr(l_icon_geometry, 1, comma_position - 1));
2894 p_ycor := to_number(substr(l_icon_geometry, comma_position + 1));
2895
2896 exception
2897 when OTHERS then
2898 Wf_Core.Context('Wf_Load', 'Get_Process_Activity', p_activity_instance);
2899 raise;
2900 end;
2901
2902 --
2903 -- Update_Message
2904 -- IN
2905 -- p_type - item type of message
2906 -- p_name - message name
2907 -- p_subject - message subject
2908 -- p_body - text body
2909 -- p_html_body - html formated body
2910 -- OUT
2911 -- x_level_error - the output of error level
2912 -- NOTE
2913 -- It first selects the values related to the message
2914 -- and then calls UPLOAD_MESSAGE to update the value.
2915 --
2916 procedure UPDATE_MESSAGE (
2917 p_type in varchar2,
2918 p_name in varchar2,
2919 p_subject in varchar2,
2920 p_body in varchar2,
2921 p_html_body in varchar2,
2922 p_level_error out NOCOPY number
2923 )
2924 is
2925 l_protect_level number;
2926 l_custom_level number;
2927 l_default_priority number;
2928 l_display_name varchar2(80);
2929 l_description varchar2(240);
2930 l_subject varchar2(240);
2931 l_body varchar2(4000);
2932 l_html_body varchar2(4000);
2933 begin
2934 -- Reset any caches that might be running.
2935 WF_CACHE.Reset;
2936
2937 Wf_Load.Get_Message(p_type, p_name, l_protect_level, l_custom_level,
2938 l_default_priority, l_display_name, l_description,
2939 l_subject, l_body, l_html_body);
2940
2941 if (p_subject is not null) then
2942 l_subject := p_subject;
2943 end if;
2944
2945 if (p_body is not null) then
2946 l_body := p_body;
2947 end if;
2948
2949 if (p_html_body is not null) then
2950 l_html_body := p_html_body;
2951 end if;
2952
2953 Wf_Load.UPLOAD_MESSAGE(
2954 x_type => p_type,
2955 x_name => p_name,
2956 x_display_name => l_display_name,
2957 x_description => l_description,
2958 x_subject => l_subject,
2959 x_body => l_body,
2960 x_html_body => l_html_body,
2961 x_protect_level => l_protect_level,
2962 x_custom_level => l_custom_level,
2963 x_default_priority => l_default_priority,
2964 x_read_role => null,
2965 x_write_role => null,
2966 x_level_error => p_level_error);
2967
2968 exception
2969 when OTHERS then
2970 Wf_Core.Context('Wf_Load', 'Get_Message', p_type, p_name);
2971 raise;
2972 end;
2973
2974 --
2975 -- Get_MESSAGE
2976 -- IN
2977 -- p_type - message item type
2978 -- p_name - message name
2979 -- OUT
2980 -- p_protect_level -
2981 -- p_custom_level -
2982 -- p_default_priority -
2983 -- p_display_name - 80
2984 -- p_description - 240
2985 -- p_subject - 240
2986 -- p_body - 4000
2987 -- p_html_body - 4000
2988 --
2989 procedure GET_MESSAGE (
2990 p_type in varchar2,
2991 p_name in varchar2,
2992 p_protect_level out NOCOPY number,
2993 p_custom_level out NOCOPY number,
2994 p_default_priority out NOCOPY number,
2995 p_display_name out NOCOPY varchar2,
2996 p_description out NOCOPY varchar2,
2997 p_subject out NOCOPY varchar2,
2998 p_body out NOCOPY varchar2,
2999 p_html_body out NOCOPY varchar2
3000 )
3001 is
3002 begin
3003 select PROTECT_LEVEL,
3004 CUSTOM_LEVEL,
3005 DEFAULT_PRIORITY,
3006 DISPLAY_NAME,
3007 DESCRIPTION,
3008 SUBJECT,
3009 BODY,
3010 HTML_BODY
3011 into
3012 p_protect_level,
3013 p_custom_level,
3014 p_default_priority,
3015 p_display_name,
3016 p_description,
3017 p_subject,
3018 p_body,
3019 p_html_body
3020 from WF_MESSAGES_VL
3021 where TYPE = p_type
3022 and NAME = p_name;
3023
3024 exception
3025 when OTHERS then
3026 Wf_Core.Context('Wf_Load', 'Get_Message', p_type, p_name);
3027 raise;
3028 end;
3029
3030 --
3031 -- COPY_ITEM_TYPE
3032 -- IN
3033 -- p_item_type - item type to copy from.
3034 -- p_destination_item_type- new item type.
3035 -- p_new_suffix - suffix to use append to internal names
3036 -- of new entities.
3037 -- NOTE
3038 --
3039 procedure COPY_ITEM_TYPE(
3040 p_item_type in varchar2,
3041 p_destination_item_type in varchar2,
3042 p_new_suffix in varchar2)
3043 is
3044 type t_ittRecord is record (
3045 protect_level number,
3046 custom_level number,
3047 name varchar2(8),
3048 display_name varchar2(80),
3049 description varchar2(240),
3050 wf_selector varchar2(240),
3051 read_role varchar2(320),
3052 write_role varchar2(320),
3053 execute_role varchar2(320),
3054 persistence_type varchar2(8),
3055 persistence_days varchar2(8));
3056
3057 v_itt t_ittRecord;
3058
3059 cursor itacur(itt in varchar2) is
3060 select PROTECT_LEVEL, CUSTOM_LEVEL, NAME, DISPLAY_NAME, DESCRIPTION,
3061 TYPE, SUBTYPE, FORMAT, TEXT_DEFAULT,
3062 to_char(NUMBER_DEFAULT) NUMBER_DEFAULT,
3063 to_char(DATE_DEFAULT, 'YYYY/MM/DD HH24:MI:SS') DATE_DEFAULT,
3064 SEQUENCE
3065 from WF_ITEM_ATTRIBUTES_VL
3066 where ITEM_TYPE = itt
3067 order by SEQUENCE;
3068
3069 cursor lutcur(itt in varchar2) is
3070 select PROTECT_LEVEL, CUSTOM_LEVEL, LOOKUP_TYPE, DISPLAY_NAME,
3071 DESCRIPTION
3072 from WF_LOOKUP_TYPES
3073 where ITEM_TYPE = itt
3074 order by LOOKUP_TYPE;
3075
3076 type t_lutTable is table of WF_LOOKUP_TYPES.LOOKUP_TYPE%TYPE
3077 index by binary_integer;
3078
3079 v_lut t_lutTable;
3080
3081 cursor luccur(lut in varchar2) is
3082 select PROTECT_LEVEL, CUSTOM_LEVEL, LOOKUP_CODE, MEANING, DESCRIPTION
3083 from WF_LOOKUPS
3084 where LOOKUP_TYPE = lut
3085 order by LOOKUP_CODE;
3086
3087 cursor msgcur(itt in varchar2) is
3088 select PROTECT_LEVEL, CUSTOM_LEVEL, NAME, DISPLAY_NAME, DESCRIPTION,
3089 SUBJECT, BODY, DEFAULT_PRIORITY, READ_ROLE, WRITE_ROLE,
3090 HTML_BODY
3091 from WF_MESSAGES_VL
3092 where TYPE = itt
3093 order by TYPE, NAME;
3094
3095 type t_msgTable is table of WF_MESSAGES.NAME%TYPE
3096 index by binary_integer;
3097
3098 v_msg t_msgTable;
3099
3100 cursor msacur(itt in varchar2, msg in varchar2) is
3101 select PROTECT_LEVEL, CUSTOM_LEVEL, NAME, DISPLAY_NAME, DESCRIPTION,
3102 TYPE, SUBTYPE, FORMAT, TEXT_DEFAULT,
3103 to_char(NUMBER_DEFAULT) NUMBER_DEFAULT,
3104 to_char(DATE_DEFAULT, 'YYYY/MM/DD HH24:MI:SS') DATE_DEFAULT,
3105 VALUE_TYPE, ATTACH, SEQUENCE
3106 from WF_MESSAGE_ATTRIBUTES_VL
3107 where MESSAGE_TYPE = itt and MESSAGE_NAME = msg
3108 order by SEQUENCE;
3109
3110 cursor actcur(itt in varchar2) is
3111 select PROTECT_LEVEL, CUSTOM_LEVEL, NAME, DISPLAY_NAME, DESCRIPTION,
3112 ITEM_TYPE, VERSION, TYPE, RERUN, FUNCTION, RESULT_TYPE, COST,
3113 ICON_NAME, MESSAGE, ERROR_PROCESS, EXPAND_ROLE,
3114 READ_ROLE, WRITE_ROLE, EXECUTE_ROLE,
3115 to_char(BEGIN_DATE, 'YYYY/MM/DD HH24:MI:SS') EFFECTIVE_DATE,
3116 ERROR_ITEM_TYPE, RUNNABLE_FLAG, FUNCTION_TYPE,
3117 EVENT_NAME, DIRECTION
3118 from WF_ACTIVITIES_VL
3119 where sysdate >= BEGIN_DATE
3120 and (sysdate < END_DATE or END_DATE is null)
3121 and ITEM_TYPE = itt
3122 order by item_type, name;
3123
3124 type t_actRecord is record (
3125 name varchar2(30),
3126 version number);
3127
3128 type t_actTable is table of t_actRecord index by binary_integer;
3129
3130 v_act t_actTable;
3131
3132 cursor atacur(itt in varchar2, actname in varchar2, ver in number) is
3133 select PROTECT_LEVEL, CUSTOM_LEVEL, NAME, DISPLAY_NAME, DESCRIPTION,
3134 TYPE, SUBTYPE, FORMAT, TEXT_DEFAULT,
3135 to_char(NUMBER_DEFAULT) NUMBER_DEFAULT,
3136 to_char(DATE_DEFAULT, 'YYYY/MM/DD HH24:MI:SS') DATE_DEFAULT,
3137 VALUE_TYPE, SEQUENCE
3138 from WF_ACTIVITY_ATTRIBUTES_VL
3139 where ACTIVITY_ITEM_TYPE = itt
3140 and ACTIVITY_NAME = actname
3141 and ACTIVITY_VERSION = ver
3142 order by SEQUENCE;
3143
3144 type t_pacTable is table of WF_PROCESS_ACTIVITIES.INSTANCE_ID%TYPE
3145 index by binary_integer;
3146
3147 v_opac t_pacTable;
3148 v_npac t_pacTable;
3149
3150 cursor paccur(itt in varchar2, actname in varchar2, ver in number) is
3151 select ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, INSTANCE_ID, START_END,
3152 DEFAULT_RESULT, ICON_GEOMETRY, PERFORM_ROLE,
3153 USER_COMMENT, PERFORM_ROLE_TYPE, INSTANCE_LABEL,
3154 PROTECT_LEVEL, CUSTOM_LEVEL
3155 from WF_PROCESS_ACTIVITIES
3156 where PROCESS_ITEM_TYPE = itt
3157 and PROCESS_NAME = actname
3158 and PROCESS_VERSION = ver
3159 order by INSTANCE_ID;
3160
3161 cursor patcur(id in number) is
3162 select RESULT_CODE, TO_PROCESS_ACTIVITY, ARROW_GEOMETRY,
3163 PROTECT_LEVEL, CUSTOM_LEVEL
3164 from WF_ACTIVITY_TRANSITIONS
3165 where FROM_PROCESS_ACTIVITY = id
3166 order by RESULT_CODE;
3167
3168 cursor aavcur(id in number) is
3169 select NAME, VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL,
3170 TEXT_VALUE,
3171 to_char(NUMBER_VALUE) NUMBER_VALUE,
3172 to_char(DATE_VALUE, 'YYYY/MM/DD HH24:MI:SS') DATE_VALUE
3173 from WF_ACTIVITY_ATTR_VALUES
3174 where PROCESS_ACTIVITY_ID = id;
3175
3176 l_item_type varchar2(8);
3177 l_default varchar2(4000);
3178 l_value varchar2(4000);
3179 l_format varchar2(240);
3180 l_result_type varchar2(30);
3181 l_message varchar2(30);
3182 l_pname varchar2(30);
3183 l_name varchar2(30);
3184 l_dname varchar2(80);
3185 l_performer varchar2(30);
3186 l_version number;
3187 l_level_error number;
3188 i pls_integer;
3189 toid pls_integer;
3190 ismatch boolean := false;
3191 no_match exception;
3192 dummy_log_message varchar2(32000);
3193 begin
3194 -- Copying Item Type
3195 begin
3196 select PROTECT_LEVEL, CUSTOM_LEVEL, NAME, DISPLAY_NAME, DESCRIPTION,
3197 WF_SELECTOR, READ_ROLE, WRITE_ROLE, EXECUTE_ROLE,
3198 PERSISTENCE_TYPE,
3199 to_char(PERSISTENCE_DAYS) PERSISTENCE_DAYS
3200 into v_itt
3201 from WF_ITEM_TYPES_VL
3202 where NAME = p_item_type;
3203 exception
3204 when others then
3205 Wf_Core.Context('Wf_Load', 'Selecting ITEM_TYPE', p_item_type);
3206 raise;
3207 end;
3208
3209 begin
3210 select DISPLAY_NAME
3211 into l_dname
3212 from WF_ITEM_TYPES_VL
3213 where NAME = p_destination_item_type;
3214 exception
3215 when NO_DATA_FOUND then
3216 l_dname := substr(v_itt.display_name||p_new_suffix,1,80);
3217
3218 when OTHERS then
3219 raise;
3220 end;
3221
3222 Wf_Load.UPLOAD_ITEM_TYPE(
3223 x_name=> p_destination_item_type,
3224 x_display_name=> l_dname,
3225 x_description=> v_itt.description,
3226 x_protect_level=> v_itt.protect_level,
3227 x_custom_level=> v_itt.custom_level,
3228 x_wf_selector=> v_itt.wf_selector,
3229 x_read_role=> v_itt.read_role,
3230 x_write_role=> v_itt.write_role,
3231 x_execute_role=> v_itt.execute_role,
3232 x_persistence_type=> v_itt.persistence_type,
3233 x_persistence_days=> v_itt.persistence_days,
3234 x_level_error=> l_level_error
3235 );
3236 l_dname := null;
3237
3238 if (l_level_error <> 0) then
3239 Wf_Core.Token('ENTITY', 'ITEM_TYPE');
3240 Wf_Core.Token('TYPE', '');
3241 Wf_Core.Token('NAME', p_destination_item_type);
3242 Wf_Core.Raise('WFLDRSD_UPI');
3243 end if;
3244
3245
3246 -- Copy Lookup Types
3247 i := 1;
3248 for lutr in lutcur(p_item_type) loop
3249 v_lut(i) := lutr.LOOKUP_TYPE;
3250 Wf_Load.UPLOAD_LOOKUP_TYPE (
3251 x_lookup_type=>substr(lutr.lookup_type||p_new_suffix, 1, 30),
3252 x_display_name=>substr(lutr.display_name||p_new_suffix, 1, 80),
3253 x_description=>lutr.description,
3254 x_protect_level=>lutr.protect_level,
3255 x_custom_level=>lutr.custom_level,
3256 x_item_type=>p_destination_item_type,
3257 x_level_error=>l_level_error
3258 );
3259 if (l_level_error <> 0) then
3260 Wf_Core.Token('ENTITY', 'LOOKUP_TYPE');
3261 Wf_Core.Token('TYPE', p_destination_item_type);
3262 Wf_Core.Token('NAME', substr(lutr.lookup_type||p_new_suffix, 1, 30));
3263 Wf_Core.Raise('WFLDRSD_UPI');
3264 end if;
3265 i := i + 1;
3266 end loop;
3267
3268 -- Copy Lookup Codes
3269 for j in 1..v_lut.count loop
3270 for lucr in luccur(v_lut(j)) loop
3271 Wf_Load.UPLOAD_LOOKUP (
3272 x_lookup_type=>substr(v_lut(j)||p_new_suffix, 1, 30),
3273 x_lookup_code=>lucr.lookup_code,
3274 x_meaning=>lucr.meaning,
3275 x_description=>lucr.description,
3276 x_protect_level=>lucr.protect_level,
3277 x_custom_level=>lucr.custom_level,
3278 x_level_error=>l_level_error
3279 );
3280 if (l_level_error <> 0) then
3281 Wf_Core.Token('ENTITY', 'LOOKUP_CODE');
3282 Wf_Core.Token('TYPE', substr(v_lut(j)||p_new_suffix, 1, 30));
3283 Wf_Core.Token('NAME', lucr.lookup_code);
3284 Wf_Core.Raise('WFLDRSD_UPI');
3285 end if;
3286 end loop;
3287 end loop;
3288
3289 -- Copy Item Attributes
3290 for itar in itacur(p_item_type) loop
3291 l_format := itar.format;
3292 if (itar.type = 'LOOKUP') then
3293 for k in 1..v_lut.count loop
3294 if (itar.format = v_lut(k)) then
3295 l_format := substr(v_lut(k)||p_new_suffix, 1, 30);
3296 end if;
3297 end loop;
3298 end if;
3299 if (itar.type = 'NUMBER') then
3300 l_default := itar.number_default;
3301 elsif (itar.type = 'DATE') then
3302 l_default := itar.date_default;
3303 else
3304 l_default := itar.text_default;
3305 end if;
3306
3307 Wf_Load.UPLOAD_ITEM_ATTRIBUTE(
3308 x_item_type=>p_destination_item_type,
3309 x_name=>substr(itar.name||p_new_suffix, 1, 30),
3310 x_display_name=>substr(itar.display_name||p_new_suffix, 1, 80),
3311 x_description=>itar.description,
3312 x_sequence=>itar.sequence,
3313 x_type=>itar.type,
3314 x_protect_level=>itar.protect_level,
3315 x_custom_level=>itar.custom_level,
3316 x_subtype=>itar.subtype,
3317 x_format=>l_format,
3318 x_default=>l_default,
3319 x_level_error=>l_level_error
3320 );
3321 if (l_level_error <> 0) then
3322 Wf_Core.Token('ENTITY', 'ITEM_ATTRIBUTE');
3323 Wf_Core.Token('TYPE', p_destination_item_type);
3324 Wf_Core.Token('NAME', itar.name);
3325 Wf_Core.Raise('WFLDRSD_UPI');
3326 end if;
3327 end loop;
3328
3329 -- Copy Message
3330 i := 1;
3331 for msgr in msgcur(p_item_type) loop
3332 v_msg(i) := msgr.name;
3333 Wf_Load.UPLOAD_MESSAGE (
3334 x_type=>p_destination_item_type,
3335 x_name=>substr(msgr.name||p_new_suffix,1,30),
3336 x_display_name=>substr(msgr.display_name||p_new_suffix,1,80),
3337 x_description=>msgr.description,
3338 x_subject=>msgr.subject,
3339 x_body=>msgr.body,
3340 x_html_body=>msgr.html_body,
3341 x_protect_level=>msgr.protect_level,
3342 x_custom_level=>msgr.custom_level,
3343 x_default_priority=>msgr.default_priority,
3344 x_read_role=>msgr.read_role,
3345 x_write_role=>msgr.write_role,
3346 x_level_error=>l_level_error
3347 );
3348 if (l_level_error <> 0) then
3349 Wf_Core.Token('ENTITY', 'MESSAGE');
3350 Wf_Core.Token('TYPE', p_destination_item_type);
3351 Wf_Core.Token('NAME', msgr.name);
3352 Wf_Core.Raise('WFLDRSD_UPI');
3353 end if;
3354 i := i + 1;
3355 end loop;
3356
3357 for j in 1..v_msg.count loop
3358 for msar in msacur(p_item_type,v_msg(j)) loop
3359 l_format := msar.format;
3360 l_name := msar.name;
3361 if (msar.type = 'LOOKUP') then
3362 for k in 1..v_lut.count loop
3363 if (msar.format = v_lut(k)) then
3364 l_format := substr(v_lut(k)||p_new_suffix, 1, 30);
3365 end if;
3366 end loop;
3367 end if;
3368 if (msar.value_type = 'CONSTANT') then
3369 if (msar.type = 'NUMBER') then
3370 l_default := msar.number_default;
3371 elsif (msar.type = 'DATE') then
3372 l_default := msar.date_default;
3373 else
3374 l_default := msar.text_default;
3375 end if;
3376 else
3377 -- must be ITEMATTR
3378 l_default := substr(msar.text_default||p_new_suffix, 1, 30);
3379 -- message attribute name needs to be changed for RESPOND attribute
3380 -- that is not RESULT
3381 if (msar.subtype = 'RESPOND' and msar.name <> 'RESULT') then
3382 l_name := substr(msar.name||p_new_suffix, 1, 30);
3383 end if;
3384 end if;
3385
3386 Wf_Load.UPLOAD_MESSAGE_ATTRIBUTE(
3387 x_message_type=>p_destination_item_type,
3388 x_message_name=>substr(v_msg(j)||p_new_suffix,1,30),
3389 x_name=>l_name,
3390 x_display_name=>msar.display_name,
3391 x_description=>msar.description,
3392 x_sequence=>msar.sequence,
3393 x_type=>msar.type,
3394 x_subtype=>msar.subtype,
3395 x_protect_level=>msar.protect_level,
3396 x_custom_level=>msar.custom_level,
3397 x_format=>l_format,
3398 x_default=>l_default,
3399 x_value_type=>msar.value_type,
3400 x_attach=>msar.attach,
3401 x_level_error=>l_level_error
3402 );
3403 if (l_level_error <> 0) then
3404 Wf_Core.Token('ENTITY', 'MESSAGE_ATTRIBUTE');
3405 Wf_Core.Token('TYPE', v_msg(j));
3406 Wf_Core.Token('NAME', msar.name);
3407 Wf_Core.Raise('WFLDRSD_UPI');
3408 end if;
3409 end loop;
3410 end loop;
3411
3412 -- Copy Activity
3413 i := 1;
3414 for actr in actcur(p_item_type) loop
3415 v_act(i).name := actr.name;
3416 v_act(i).version := actr.version;
3417
3418 l_result_type := actr.result_type;
3419 for k in 1..v_lut.count loop
3420 if (actr.result_type = v_lut(k)) then
3421 l_result_type := substr(v_lut(k)||p_new_suffix, 1, 30);
3422 end if;
3423 end loop;
3424
3425 if (actr.type = 'NOTICE') then
3426 l_message := substr(actr.message||p_new_suffix,1,30);
3427 else
3428 l_message := actr.message;
3429 end if;
3430
3431 if (actr.name = 'ROOT') then
3432 l_name := actr.name;
3433 l_dname := actr.display_name;
3434 else
3435 l_name := substr(actr.name||p_new_suffix,1,30);
3436 l_dname := substr(actr.display_name||p_new_suffix,1,80);
3437 end if;
3438
3439 Wf_Load.UPLOAD_ACTIVITY (
3440 x_item_type=>p_destination_item_type,
3441 x_name=>l_name,
3442 x_display_name=>l_dname,
3443 x_description=>actr.description,
3444 x_type=>actr.type,
3445 x_rerun=>actr.rerun,
3446 x_protect_level=>actr.protect_level,
3447 x_custom_level=>actr.custom_level,
3448 x_effective_date=>sysdate,
3449 x_function=>actr.function,
3450 x_function_type=>actr.function_type,
3451 x_result_type=>l_result_type,
3452 x_cost=>actr.cost,
3453 x_read_role=>actr.read_role,
3454 x_write_role=>actr.write_role,
3455 x_execute_role=>actr.execute_role,
3456 x_icon_name=>actr.icon_name,
3457 x_message=>l_message,
3458 x_error_process=>actr.error_process,
3459 x_expand_role=>actr.expand_role,
3460 x_error_item_type=>actr.error_item_type,
3461 x_runnable_flag=>actr.runnable_flag,
3462 x_event_filter => actr.event_name,
3463 x_event_type => actr.direction,
3464 x_log_message=>dummy_log_message,
3465 x_version=>l_version,
3466 x_level_error=>l_level_error
3467 );
3468 if (l_level_error <> 0) then
3469 Wf_Core.Token('ENTITY', 'ACTIVITY');
3470 Wf_Core.Token('TYPE', p_destination_item_type);
3471 Wf_Core.Token('NAME', actr.name);
3472 Wf_Core.Raise('WFLDRSD_UPI');
3473 end if;
3474 i := i + 1;
3475 end loop;
3476
3477 for j in 1..v_act.count loop
3478 for atar in atacur(p_item_type,v_act(j).name,v_act(j).version) loop
3479 l_format := atar.format;
3480 if (atar.type = 'LOOKUP') then
3481 for k in 1..v_lut.count loop
3482 if (atar.format = v_lut(k)) then
3483 l_format := substr(v_lut(k)||p_new_suffix, 1, 30);
3484 end if;
3485 end loop;
3486 end if;
3487 if (atar.value_type = 'CONSTANT') then
3488 if (atar.type = 'NUMBER') then
3489 l_default := atar.number_default;
3490 elsif (atar.type = 'DATE') then
3491 l_default := atar.date_default;
3492 else
3493 l_default := atar.text_default;
3494 end if;
3495 else
3496 -- must be ITEMATTR
3497 l_default := substr(atar.text_default||p_new_suffix, 1, 30);
3498 end if;
3499
3500 -- version below is always 1, the first one.
3501 Wf_Load.UPLOAD_ACTIVITY_ATTRIBUTE(
3502 x_activity_item_type=>p_destination_item_type,
3503 x_activity_name=>substr(v_act(j).name||p_new_suffix,1,30),
3504 x_activity_version=>1,
3505 x_name=>atar.name,
3506 x_display_name=>atar.display_name,
3507 x_description=>atar.description,
3508 x_sequence=>atar.sequence,
3509 x_type=>atar.type,
3510 x_protect_level=>atar.protect_level,
3511 x_custom_level=>atar.custom_level,
3512 x_subtype=>atar.subtype,
3513 x_format=>l_format,
3514 x_default=>l_default,
3515 x_value_type=>atar.value_type,
3516 x_level_error=>l_level_error
3517 );
3518 if (l_level_error <> 0) then
3519 Wf_Core.Token('ENTITY', 'ACTIVITY_ATTRIBUTE');
3520 Wf_Core.Token('TYPE', substr(v_act(j).name,1,30));
3521 Wf_Core.Token('NAME', atar.name);
3522 Wf_Core.Raise('WFLDRSD_UPI');
3523 end if;
3524 end loop;
3525 end loop;
3526
3527 -- Copy Process Activity
3528 i := 1;
3529 for j in 1..v_act.count loop
3530 for pacr in paccur(p_item_type,v_act(j).name,v_act(j).version) loop
3531 v_opac(i) := pacr.instance_id;
3532 v_npac(i) := 0;
3533 -- make sure activity_item_type is consistant
3534 if (pacr.activity_item_type = p_item_type) then
3535 l_item_type := p_destination_item_type;
3536 l_name := substr(pacr.activity_name||p_new_suffix,1,30);
3537 else
3538 l_item_type := pacr.activity_item_type;
3539 l_name := pacr.activity_name;
3540 end if;
3541
3542 if (v_act(j).name = 'ROOT') then
3543 l_pname := v_act(j).name;
3544 else
3545 l_pname := substr(v_act(j).name||p_new_suffix,1,30);
3546 end if;
3547
3548 if (pacr.perform_role_type = 'ITEMATTR') then
3549 l_performer := substr(pacr.perform_role||p_new_suffix,1,30);
3550 else
3551 l_performer := pacr.perform_role;
3552 end if;
3553
3554 -- check if process activity already exists
3555 begin
3556 select INSTANCE_ID into v_npac(i)
3557 from WF_PROCESS_ACTIVITIES
3558 where INSTANCE_LABEL = pacr.instance_label
3559 and PROCESS_ITEM_TYPE = p_destination_item_type
3560 and PROCESS_NAME = l_pname
3561 and PROCESS_VERSION = 1;
3562 exception
3563 when NO_DATA_FOUND then
3564 v_npac(i) := 0;
3565
3566 Wf_Load.UPLOAD_PROCESS_ACTIVITY (
3567 x_process_item_type=>p_destination_item_type,
3568 x_process_name=>l_pname,
3569 x_process_version=>1,
3570 x_activity_item_type=>l_item_type,
3571 x_activity_name=>l_name,
3572 x_instance_id=>v_npac(i),
3573 x_instance_label=>pacr.instance_label,
3574 x_protect_level=>pacr.protect_level,
3575 x_custom_level=>pacr.custom_level,
3576 x_start_end=>pacr.start_end,
3577 x_default_result=>pacr.default_result,
3578 x_icon_geometry=>pacr.icon_geometry,
3579 x_perform_role=>l_performer,
3580 x_perform_role_type=>pacr.perform_role_type,
3581 x_user_comment=>pacr.user_comment,
3582 x_level_error=>l_level_error
3583 );
3584 if (l_level_error <> 0) then
3585 Wf_Core.Token('ENTITY', 'PROCESS_ACTIVITY');
3586 Wf_Core.Token('TYPE', v_act(j).name);
3587 Wf_Core.Token('NAME', pacr.activity_name);
3588 Wf_Core.Raise('WFLDRSD_UPI');
3589 end if;
3590 end;
3591 i := i + 1;
3592 end loop;
3593 end loop;
3594
3595 for j in 1..v_opac.count loop
3596
3597 -- Copy Activity Transitions
3598 for patr in patcur(v_opac(j)) loop
3599 ismatch := false;
3600 for k in 1..v_opac.count loop
3601 -- find the index of to_process_activity
3602 if v_opac(k) = patr.to_process_activity then
3603 toid := k;
3604 ismatch := true;
3605 exit;
3606 end if;
3607 end loop;
3608 if (not ismatch) then
3609 -- ### dbms_output.put_line('no match for transition: '||
3610 -- ### to_char(patr.to_process_activity));
3611 -- ### should raise error here.
3612 raise no_match;
3613 end if;
3614
3615 Wf_Load.UPLOAD_ACTIVITY_TRANSITION (
3616 x_from_process_activity=>v_npac(j),
3617 x_result_code=>patr.result_code,
3618 x_to_process_activity=>v_npac(toid),
3619 x_protect_level=>patr.protect_level,
3620 x_custom_level=>patr.custom_level,
3621 x_arrow_geometry=>patr.arrow_geometry,
3622 x_level_error=>l_level_error
3623 );
3624 if (l_level_error <> 0) then
3625 Wf_Core.Token('ENTITY', 'ACTIVITY_TRANSITION');
3626 Wf_Core.Token('TYPE', '');
3627 Wf_Core.Token('NAME', to_char(v_npac(j))||'-'||to_char(v_npac(toid)));
3628 Wf_Core.Raise('WFLDRSD_UPI');
3629 end if;
3630 end loop;
3631
3632 -- Copy Activity Attr Values
3633 for aavr in aavcur(v_opac(j)) loop
3634 if (aavr.value_type = 'CONSTANT') then
3635 -- just pick a non-null value
3636 l_value := nvl(aavr.date_value,
3637 nvl(aavr.number_value, aavr.text_value));
3638 else
3639 -- must be ITEMATTR
3640 l_value := substr(aavr.text_value||p_new_suffix, 1, 30);
3641 end if;
3642
3643
3644 Wf_Load.UPLOAD_ACTIVITY_ATTR_VALUE (
3645 x_process_activity_id=>v_npac(j),
3646 x_name=>aavr.name,
3647 x_protect_level=>aavr.protect_level,
3648 x_custom_level=>aavr.custom_level,
3649 x_value=>l_value,
3650 x_value_type=>aavr.value_type,
3651 x_effective_date=>sysdate,
3652 x_level_error=>l_level_error
3653 );
3654 if (l_level_error <> 0) then
3655 Wf_Core.Token('ENTITY', 'ACTIVITY_ATTR_VALUE');
3656 Wf_Core.Token('TYPE', to_char(v_npac(j)));
3657 Wf_Core.Token('NAME', aavr.name);
3658 Wf_Core.Raise('WFLDRSD_UPI');
3659 end if;
3660 end loop;
3661 end loop;
3662
3663 exception
3664 when OTHERS then
3665 if (itacur%isopen) then
3666 close itacur;
3667 end if;
3668 if (lutcur%isopen) then
3669 close itacur;
3670 end if;
3671 if (luccur%isopen) then
3672 close itacur;
3673 end if;
3674 if (msgcur%isopen) then
3675 close msgcur;
3676 end if;
3677 if (msacur%isopen) then
3678 close msacur;
3679 end if;
3680 Wf_Core.Context('Wf_Load', 'COPY_ITEM_TYPE', p_item_type,
3681 p_destination_item_type,
3682 p_new_suffix);
3683 raise;
3684 end;
3685
3686 -- Delete_Process_Activity
3687 -- IN
3688 -- p_item_type - item type of this process activity (used in making
3689 -- sure the process activity has not been run).
3690 -- p_step - instance id of the process activity
3691 -- NOTE
3692 -- It is possible to leave an invalid Workflow definition after this
3693 -- call.
3694 -- Make sure it does not exist in wf_item_activity_statuses, ie. has
3695 -- not been run.
3696 -- It needs to make sure all transitions are cleaned up first.
3697 -- It also needs to clean up all activity attribute values.
3698 procedure Delete_Process_Activity(
3699 p_step in number)
3700 is
3701 dummy number;
3702 begin
3703
3704 -- ###
3705 -- The following check causes full table scans. Since it is controlled by
3706 -- constraints already, we skipped the check here.
3707 -- begin
3708 -- select 1 into dummy
3709 -- from sys.dual
3710 -- where not exists (
3711 -- select 1
3712 -- from WF_ITEM_ACTIVITY_STATUSES_V
3713 -- where ACTIVITY_ID = p_step
3714 -- );
3715 -- exception
3716 -- when NO_DATA_FOUND then
3717 -- -- ### activity started
3718 -- raise;
3719 -- end;
3720
3721 -- Delete all transitions to and from this process activity
3722 Wf_Load.Delete_Transition(p_previous_step=>p_step);
3723 Wf_Load.Delete_Transition(p_next_step=>p_step);
3724
3725 -- Delete all related attribute values
3726 delete from WF_ACTIVITY_ATTR_VALUES where PROCESS_ACTIVITY_ID = p_step;
3727
3728 -- Delete the process activity
3729 delete from WF_PROCESS_ACTIVITIES where INSTANCE_ID = p_step;
3730
3731 exception
3732 when OTHERS then
3733 Wf_Core.Context('Wf_Load', 'Delete_Process_Activity',
3734 to_char(p_step));
3735 raise;
3736 end;
3737
3738 --
3739 -- Get_Activity_Attr_Val
3740 -- IN
3741 -- p_process_instance_id - instance id of the process activity
3742 -- p_attribute_name - name of the attribute
3743 -- OUT
3744 -- p_attribute_value_type - value type like 'CONSTANT' or 'ITEMATTR'
3745 -- p_attribute_value - value of the attribute
3746 --
3747 procedure GET_ACTIVITY_ATTR_VAL(
3748 p_process_instance_id in number,
3749 p_attribute_name in varchar2,
3750 p_attribute_value_type out NOCOPY varchar2,
3751 p_attribute_value out NOCOPY varchar2)
3752 is
3753 begin
3754 select VALUE_TYPE,
3755 nvl(nvl(TEXT_VALUE, to_char(NUMBER_VALUE)),
3756 to_char(DATE_VALUE, 'YYYY/MM/DD HH24:MI:SS'))
3757 into p_attribute_value_type,
3758 p_attribute_value
3759 from WF_ACTIVITY_ATTR_VALUES
3760 where PROCESS_ACTIVITY_ID = p_process_instance_id
3761 and NAME = p_attribute_name;
3762 exception
3763 when OTHERS then
3764 Wf_Core.Context('Wf_Load', 'Get_Activity_Attr_Val',
3765 to_char(p_process_instance_id), p_attribute_name);
3766 raise;
3767 end;
3768
3769 --
3770 -- Get_Item_Attribute
3771 -- IN
3772 -- p_item_type - item type
3773 -- p_attribute_name - name of the attribute
3774 -- OUT
3775 -- p_attribute_type - type like 'NUMBER', 'TEXT' and so on
3776 -- p_attribute_value - value of the attribute
3777 --
3778 procedure GET_ITEM_ATTRIBUTE(
3779 p_item_type in varchar2,
3780 p_attribute_name in varchar2,
3781 p_attribute_type out NOCOPY varchar2,
3782 p_attribute_value out NOCOPY varchar2)
3783 is
3784 l_text varchar2(4000);
3785 l_number varchar2(100);
3786 l_date varchar2(30);
3787 begin
3788 select TYPE,
3789 TEXT_DEFAULT,
3790 to_char(NUMBER_DEFAULT),
3791 to_char(DATE_DEFAULT, 'YYYY/MM/DD HH24:MI:SS')
3792 into p_attribute_type,
3793 p_attribute_value,
3794 l_number,
3795 l_date
3796 from WF_ITEM_ATTRIBUTES
3797 where ITEM_TYPE = p_item_type
3798 and NAME = p_attribute_name;
3799
3800 if (p_attribute_type = 'DATE') then
3801 p_attribute_value := l_date;
3802 elsif (p_attribute_type = 'NUMBER') then
3803 p_attribute_value := l_number;
3804 end if;
3805
3806 exception
3807 when OTHERS then
3808 Wf_Core.Context('Wf_Load', 'Get_Item_Attribute',
3809 p_item_type, p_attribute_name);
3810 raise;
3811 end;
3812
3813 --
3814 -- Get_Activity
3815 -- IN
3816 -- p_item_type -
3817 -- p_name -
3818 -- OUT
3819 -- p_display_name -
3820 -- p_description -
3821 -- p_type -
3822 -- p_rerun -
3823 -- p_protect_level -
3824 -- p_custom_level -
3825 -- p_begin_date -
3826 -- p_function -
3827 -- p_function_type -
3828 -- p_result_type -
3829 -- p_cost -
3830 -- p_read_role -
3831 -- p_write_role -
3832 -- p_execute_role -
3833 -- p_icon_name -
3834 -- p_message -
3835 -- p_error_process -
3836 -- p_expand_role -
3837 -- p_error_item_type -
3838 -- p_runnable_flag -
3839 -- p_version -
3840 procedure GET_ACTIVITY (
3841 p_item_type in varchar2,
3842 p_name in varchar2,
3843 p_display_name out NOCOPY varchar2,
3844 p_description out NOCOPY varchar2,
3845 p_type out NOCOPY varchar2,
3846 p_rerun out NOCOPY varchar2,
3847 p_protect_level out NOCOPY number,
3848 p_custom_level out NOCOPY number,
3849 p_begin_date out NOCOPY date,
3850 p_function out NOCOPY varchar2,
3851 p_function_type out NOCOPY varchar2,
3852 p_result_type out NOCOPY varchar2,
3853 p_cost out NOCOPY number,
3854 p_read_role out NOCOPY varchar2,
3855 p_write_role out NOCOPY varchar2,
3856 p_execute_role out NOCOPY varchar2,
3857 p_icon_name out NOCOPY varchar2,
3858 p_message out NOCOPY varchar2,
3859 p_error_process out NOCOPY varchar2,
3860 p_expand_role out NOCOPY varchar2,
3861 p_error_item_type out NOCOPY varchar2,
3862 p_runnable_flag out NOCOPY varchar2,
3863 p_version out NOCOPY number
3864 )
3865 is
3866 begin
3867 select DISPLAY_NAME,
3868 DESCRIPTION,
3869 TYPE,
3870 RERUN,
3871 PROTECT_LEVEL,
3872 CUSTOM_LEVEL,
3873 BEGIN_DATE,
3874 FUNCTION,
3875 FUNCTION_TYPE,
3876 RESULT_TYPE,
3877 COST,
3878 READ_ROLE,
3879 WRITE_ROLE,
3880 EXECUTE_ROLE,
3881 ICON_NAME,
3882 MESSAGE,
3883 ERROR_PROCESS,
3884 EXPAND_ROLE,
3885 ERROR_ITEM_TYPE,
3886 RUNNABLE_FLAG,
3887 VERSION
3888 into p_display_name,
3889 p_description,
3890 p_type,
3891 p_rerun,
3892 p_protect_level,
3893 p_custom_level,
3894 p_begin_date,
3895 p_function,
3896 p_function_type,
3897 p_result_type,
3898 p_cost,
3899 p_read_role,
3900 p_write_role,
3901 p_execute_role,
3902 p_icon_name,
3903 p_message,
3904 p_error_process,
3905 p_expand_role,
3906 p_error_item_type,
3907 p_runnable_flag,
3908 p_version
3909 from WF_ACTIVITIES_VL
3910 where ITEM_TYPE = p_item_type
3911 and NAME = p_name
3912 and END_DATE is null;
3913 exception
3914 when OTHERS then
3915 Wf_Core.Context('Wf_Load', 'Get_Activity', p_item_type, p_name);
3916 raise;
3917 end;
3918
3919 --
3920 -- Update_Activity
3921 -- IN
3922 -- p_item_type - item type of the activity
3923 -- p_name - activity name
3924 -- p_display_name - activity display name
3925 -- p_description - activity description
3926 -- p_expand_role - flag to indicate expand role or not
3927 -- OUT
3928 -- p_level_error - the output of error level
3929 -- NOTE
3930 -- Cannot use UPLOAD_ACTIVITY because activity is normally versioned.
3931 -- Update only the latest version.
3932 --
3933 procedure UPDATE_ACTIVITY (
3934 p_item_type in varchar2,
3935 p_name in varchar2,
3936 p_display_name in varchar2,
3937 p_description in varchar2 ,
3938 p_expand_role in varchar2 ,
3939 p_level_error out NOCOPY number)
3940 is
3941 conflict_name varchar2(240);
3942 l_name varchar2(30);
3943 l_dname varchar2(80);
3944 n_dname varchar2(80);
3945 l_display_name varchar2(80);
3946 l_description varchar2(240);
3947 l_type varchar2(8);
3948 l_rerun varchar2(8);
3949 l_protect_level number;
3950 l_custom_level number;
3951 l_begin_date date;
3952 l_function varchar2(240);
3953 l_function_type varchar2(30);
3954 l_result_type varchar2(30);
3955 l_cost number;
3956 l_read_role varchar2(320);
3957 l_write_role varchar2(320);
3958 l_execute_role varchar2(320);
3959 l_icon_name varchar2(30);
3960 l_message varchar2(30);
3961 l_error_process varchar2(30);
3962 l_expand_role varchar2(1);
3963 l_error_item_type varchar2(8);
3964 l_runnable_flag varchar2(1);
3965 l_version number;
3966 begin
3967 -- Reset any caches that might be running.
3968 WF_CACHE.Reset;
3969
3970 begin
3971 Wf_Load.GET_ACTIVITY (
3972 p_item_type=>p_item_type,
3973 p_name=>p_name,
3974 p_display_name=>l_display_name,
3975 p_description=>l_description,
3976 p_type=>l_type,
3977 p_rerun=>l_rerun,
3978 p_protect_level=>l_protect_level,
3979 p_custom_level=>l_custom_level,
3980 p_begin_date=>l_begin_date,
3981 p_function=>l_function,
3982 p_function_type=>l_function_type,
3983 p_result_type=>l_result_type,
3984 p_cost=>l_cost,
3985 p_read_role=>l_read_role,
3986 p_write_role=>l_write_role,
3987 p_execute_role=>l_execute_role,
3988 p_icon_name=>l_icon_name,
3989 p_message=>l_message,
3990 p_error_process=>l_error_process,
3991 p_expand_role=>l_expand_role,
3992 p_error_item_type=>l_error_item_type,
3993 p_runnable_flag=>l_runnable_flag,
3994 p_version=>l_version
3995 );
3996 -- Check protect and custom level
3997 if (l_type <> 'FOLDER') then
3998 if ((wf_core.upload_mode <> 'FORCE') and
3999 (l_protect_level < wf_core.session_level)) then
4000 p_level_error := 1;
4001 return;
4002 end if;
4003
4004 if ((wf_core.upload_mode = 'UPGRADE') and
4005 (l_custom_level > wf_core.session_level)) then
4006 p_level_error := 2;
4007 return;
4008 end if;
4009 end if;
4010 exception
4011 when OTHERS then
4012 -- Don't proceed
4013 Wf_Core.Context('Wf_Load', 'Update_Activity Get_Activity');
4014 raise;
4015 end;
4016
4017 if (p_display_name is not null) then
4018 l_display_name := p_display_name;
4019 end if;
4020 if (p_description is not null) then
4021 l_description := p_description;
4022 end if;
4023 if (p_expand_role is not null) then
4024 l_expand_role := p_expand_role;
4025 end if;
4026
4027 -- Check for unique index violations
4028 -- try to resolve the problem by appending '@'
4029 -- to the incoming display name
4030 -- for activity, we must have the specific version first.
4031 n_dname := l_display_name;
4032 begin
4033 -- l_name will be the old data to update
4034 select ITEM_TYPE||':'||NAME||':'||to_char(VERSION), DISPLAY_NAME, NAME
4035 into conflict_name, l_dname, l_name
4036 from WF_ACTIVITIES_VL
4037 where DISPLAY_NAME = n_dname
4038 and ITEM_TYPE = p_item_type
4039 and l_begin_date >= BEGIN_DATE
4040 and l_begin_date < nvl(END_DATE, l_begin_date+1)
4041 and NAME <> p_name;
4042
4043 n_dname := substrb('@'||l_dname, 1, 240);
4044
4045 -- this loop will make sure no duplicate with n_dname
4046 loop
4047 begin
4048 select ITEM_TYPE||':'||NAME||':'||to_char(VERSION), DISPLAY_NAME
4049 into conflict_name, l_dname
4050 from WF_ACTIVITIES_VL
4051 where DISPLAY_NAME = n_dname
4052 and ITEM_TYPE = p_item_type
4053 and l_begin_date >= BEGIN_DATE
4054 and l_begin_date < nvl(END_DATE, l_begin_date+1)
4055 and NAME <> l_name;
4056
4057 n_dname := substrb('@'||l_dname, 1, 80);
4058
4059 if ( n_dname = l_dname ) then
4060 Wf_Core.Token('DNAME', l_display_name);
4061 Wf_Core.Token('NAME', p_item_type||':'||p_name||':'||
4062 to_char(l_version));
4063 Wf_Core.Token('CONFLICT_NAME', conflict_name);
4064 Wf_Core.Raise('WFSQL_UNIQUE_NAME');
4065 exit;
4066 end if;
4067 exception
4068 when no_data_found then
4069 exit;
4070 end;
4071 end loop;
4072 exception
4073 when no_data_found then
4074 null;
4075
4076 when others then
4077 raise;
4078 end;
4079
4080 -- Do the Update
4081 update WF_ACTIVITIES
4082 set expand_role = l_expand_role
4083 where ITEM_TYPE = p_item_type
4084 and NAME = p_name
4085 and VERSION = l_version;
4086
4087 update WF_ACTIVITIES_TL
4088 set DISPLAY_NAME = n_dname,
4089 DESCRIPTION = l_description
4090 where ITEM_TYPE = p_item_type
4091 and NAME = p_name
4092 and VERSION = l_version;
4093
4094 exception
4095 when OTHERS then
4096 Wf_Core.Context('Wf_Load', 'Update_Activity', p_item_type, p_name,
4097 p_display_name);
4098 raise;
4099 end;
4100
4101 --
4102 -- Get_Activity_Instance
4103 -- Return the instance id for an activity based on its label of a
4104 -- given process and activity
4105 -- IN
4106 -- p_process_item_type -
4107 -- p_process_name -
4108 -- p_process_version -
4109 -- p_activity_item_type -
4110 -- p_activity_name -
4111 -- p_instance_label -
4112 function Get_Activity_Instance(
4113 p_process_item_type in varchar2,
4114 p_process_name in varchar2,
4115 p_process_version in number ,
4116 p_activity_item_type in varchar2 ,
4117 p_activity_name in varchar2 ,
4118 p_instance_label in varchar2 )
4119 return number
4120 is
4121 id number;
4122 begin
4123 -- p_activity_item_type and p_activity_name pair are non-null or
4124 -- p_instance_label is non-null. Otherwise return -1.
4125 if (p_instance_label is null and
4126 (p_activity_item_type is null or p_activity_name is null)) then
4127 return (-1);
4128 end if;
4129
4130 if (p_instance_label is not null) then
4131 select INSTANCE_ID into id
4132 from WF_PROCESS_ACTIVITIES
4133 where INSTANCE_LABEL = p_instance_label
4134 and PROCESS_NAME = p_process_name
4135 and PROCESS_ITEM_TYPE = p_process_item_type
4136 and PROCESS_VERSION = p_process_version;
4137
4138 return id;
4139 end if;
4140
4141 -- return only the first row if there are more.
4142 select INSTANCE_ID into id
4143 from WF_PROCESS_ACTIVITIES
4144 where PROCESS_NAME = p_process_name
4145 and PROCESS_ITEM_TYPE = p_process_item_type
4146 and PROCESS_VERSION = p_process_version
4147 and ACTIVITY_ITEM_TYPE = p_activity_item_type
4148 and ACTIVITY_NAME = p_activity_name
4149 and rownum = 1;
4150
4151 return id;
4152
4153 exception
4154 when OTHERS then
4155 Wf_Core.Context('Wf_Load', 'Get_Activity_Instance', p_process_item_type,
4156 p_process_name, p_process_version);
4157 return (-1);
4158 end;
4159
4160 /* ### Get_Process_Activity include this function
4161 --
4162 -- GetActNameFromInstId
4163 -- IN
4164 -- p_instance_id - instance id of an activity
4165 -- RET
4166 -- Name of the activity in varchar2
4167 --
4168 function GetActNameFromInstId (
4169 p_instance_id in number)
4170 return varchar2
4171 is
4172 l_actname varchar2(30);
4173 begin
4174 select ACTIVITY_NAME
4175 into l_actname
4176 from WF_PROCESS_ACTIVITIES
4177 where INSTANCE_ID = p_instance_id;
4178
4179 return l_actname;
4180 exception
4181 when OTHERS then
4182 Wf_Core.Context('Wf_Load', 'GetActNameFromInstId',
4183 to_char(p_instance_id));
4184 return null;
4185 end;
4186 */
4187
4188 --
4189 -- Get_Activity_Transition
4190 -- IN
4191 -- p_from_activity -
4192 -- p_to_activity -
4193 -- p_result_code -
4194 -- OUT
4195 -- p_result_codes - table of all matched result codes
4196 -- p_activities - table of all matched activity instance ids
4197 -- NOTE
4198 -- Depend on what the parameter given return the appropriate result
4199 -- p_from_activity + p_to_activity => p_result_codes
4200 -- p_from_activity + p_result_code => p_activities (of to activity)
4201 -- p_to_activity + p_result_code => p_activities (of from activity)
4202 -- p_from_activity => p_result_codes + p_activities (of to activity)
4203 -- p_to_activity => p_result_codes + p_activities (of from activity)
4204 procedure Get_Activity_Transition (
4205 p_from_activity in number ,
4206 p_to_activity in number ,
4207 p_result_code in varchar2 ,
4208 p_activities out NOCOPY t_instanceidTab,
4209 p_result_codes out NOCOPY t_resultcodeTab)
4210 is
4211 cursor rccur(from_act in number, to_act in number) is
4212 select RESULT_CODE
4213 from WF_ACTIVITY_TRANSITIONS
4214 where FROM_PROCESS_ACTIVITY = from_act
4215 and TO_PROCESS_ACTIVITY = to_act;
4216
4217 cursor tpcur(from_act in number, res_code in varchar2) is
4218 select TO_PROCESS_ACTIVITY
4219 from WF_ACTIVITY_TRANSITIONS
4220 where FROM_PROCESS_ACTIVITY = from_act
4221 and RESULT_CODE = res_code;
4222
4223 cursor tp2cur(from_act in number) is
4224 select TO_PROCESS_ACTIVITY, RESULT_CODE
4225 from WF_ACTIVITY_TRANSITIONS
4226 where FROM_PROCESS_ACTIVITY = from_act;
4227
4228 cursor fpcur(to_act in number, res_code in varchar2) is
4229 select FROM_PROCESS_ACTIVITY
4230 from WF_ACTIVITY_TRANSITIONS
4231 where TO_PROCESS_ACTIVITY = to_act
4232 and RESULT_CODE = res_code;
4233
4234 cursor fp2cur(to_act in number) is
4235 select FROM_PROCESS_ACTIVITY, RESULT_CODE
4236 from WF_ACTIVITY_TRANSITIONS
4237 where TO_PROCESS_ACTIVITY = to_act;
4238
4239 i pls_integer;
4240 begin
4241 if (p_from_activity is null and p_to_activity is null and
4242 p_result_code is null) then
4243 return;
4244 end if;
4245 if (p_from_activity is not null and p_to_activity is not null) then
4246 i := 1;
4247 for rcr in rccur(p_from_activity, p_to_activity) loop
4248 p_result_codes(i) := rcr.RESULT_CODE;
4249 i := i + 1;
4250 end loop;
4251 elsif (p_to_activity is null) then
4252 i := 1;
4253 if (p_result_code is null) then
4254 for tpr in tp2cur(p_from_activity) loop
4255 p_activities(i) := tpr.TO_PROCESS_ACTIVITY;
4256 p_result_codes(i) := tpr.RESULT_CODE;
4257 i := i + 1;
4258 end loop;
4259 else
4260 for tpr in tpcur(p_from_activity, p_result_code) loop
4261 p_activities(i) := tpr.TO_PROCESS_ACTIVITY;
4262 i := i + 1;
4263 end loop;
4264 end if;
4265 else
4266 i := 1;
4267 if (p_result_code is null) then
4268 for fpr in fp2cur(p_to_activity) loop
4269 p_activities(i) := fpr.FROM_PROCESS_ACTIVITY;
4270 p_result_codes(i) := fpr.RESULT_CODE;
4271 i := i + 1;
4272 end loop;
4273 else
4274 for fpr in fpcur(p_to_activity, p_result_code) loop
4275 p_activities(i) := fpr.FROM_PROCESS_ACTIVITY;
4276 i := i + 1;
4277 end loop;
4278 end if;
4279 end if;
4280 exception
4281 when OTHERS then
4282 Wf_Core.Context('Wf_Load', 'Get_Activity_Transition',
4283 to_char(p_from_activity),
4284 to_char(p_to_activity),
4285 p_result_code
4286 );
4287 end;
4288
4289 --
4290 -- Get_Item_Attribute_Names
4291 -- select all the item attributes that match the specified suffix
4292 -- IN
4293 -- p_item_type - item type of the item attributes
4294 -- p_suffix - suffix that the internal names of item attributes endded in
4295 -- OUT
4296 -- p_names - table of internal names that returned
4297 --
4298 procedure Get_Item_Attribute_Names(
4299 p_item_type in varchar2,
4300 p_suffix in varchar2,
4301 p_names out NOCOPY t_nameTab
4302 )is
4303 cursor itancur is
4304 select NAME
4305 from WF_ITEM_ATTRIBUTES
4306 where ITEM_TYPE = p_item_type
4307 and NAME like '%'||p_suffix;
4308
4309 i pls_integer;
4310 begin
4311 i := 1;
4312 for itanr in itancur loop
4313 p_names(i) := itanr.name;
4314 i := i + 1;
4315 end loop;
4316 exception
4317 when OTHERS then
4318 Wf_Core.Context('Wf_Load', 'Get_Attribute_Names', p_item_type, p_suffix);
4319 raise;
4320 end;
4321
4322
4323 --
4324 -- Get_Notif_Activity_Names
4325 -- select all the notification activities that match the specified suffix
4326 -- IN
4327 -- p_item_type - item type of the activities
4328 -- p_suffix - suffix that the internal names of activities endded in
4329 -- OUT
4330 -- p_names - table of internal names that returned
4331 --
4332 procedure Get_Notif_Activity_Names(
4333 p_item_type in varchar2,
4334 p_suffix in varchar2,
4335 p_names out NOCOPY t_nameTab
4336 )is
4337 cursor notfcur is
4338 select NAME
4339 from WF_ACTIVITIES
4340 where NAME like '%'||p_suffix
4341 and ITEM_TYPE = p_item_type
4342 and TYPE = 'NOTICE'
4343 and END_DATE is null;
4344
4345 i pls_integer;
4346 begin
4347 i := 1;
4348 for notfr in notfcur loop
4349 p_names(i) := notfr.name;
4350 i := i + 1;
4351 end loop;
4352 exception
4353 when OTHERS then
4354 Wf_Core.Context('Wf_Load', 'Get_Notif_Activity_Names',
4355 p_item_type, p_suffix);
4356 raise;
4357 end;
4358
4359 -- Get_Message_Names
4360 -- select all the messages that match the specified suffix
4361 -- IN
4362 -- p_item_type - item type of the messages
4363 -- p_suffix - suffix that the internal names of messages endded in
4364 -- OUT
4365 -- p_names - table of internal names that returned
4366 --
4367 procedure Get_Message_Names(
4368 p_item_type in varchar2,
4369 p_suffix in varchar2,
4370 p_names out NOCOPY t_nameTab
4371 )is
4372 cursor msgcur is
4373 select NAME
4374 from WF_MESSAGES
4375 where NAME like '%'||p_suffix
4376 and TYPE = p_item_type;
4377
4378 i pls_integer;
4379 begin
4380 i := 1;
4381 for msgr in msgcur loop
4382 p_names(i) := msgr.name;
4383 i := i + 1;
4384 end loop;
4385 exception
4386 when OTHERS then
4387 Wf_Core.Context('Wf_Load', 'Get_Message_Names', p_item_type, p_suffix);
4388 raise;
4389 end;
4390
4391 -- Get_Process_Activity_Instances
4392 -- select all the process activities of activity of type process
4393 -- IN
4394 -- p_process_item_type - item type of the process which includes these
4395 -- activities.
4396 -- p_process_name - process name
4397 -- p_process_version - process version which defaults to 1
4398 -- OUT
4399 -- p_instance_ids - table of instance ids that returned
4400 --
4401 procedure Get_Process_Activity_Instances(
4402 p_process_item_type in varchar2,
4403 p_process_name in varchar2,
4404 p_process_version in number ,
4405 p_instance_ids out NOCOPY t_instanceidTab
4406 )is
4407 cursor paccur is
4408 select INSTANCE_ID
4409 from WF_PROCESS_ACTIVITIES
4410 where PROCESS_NAME = p_process_name
4411 and PROCESS_ITEM_TYPE = p_process_item_type
4412 and PROCESS_VERSION = p_process_version;
4413
4414 i pls_integer;
4415 begin
4416 i := 1;
4417 for pacr in paccur loop
4418 p_instance_ids(i) := pacr.instance_id;
4419 i := i + 1;
4420 end loop;
4421 exception
4422 when OTHERS then
4423 Wf_Core.Context('Wf_Load', 'Get_Process_Activity_Instances',
4424 p_process_item_type, p_process_name, p_process_version);
4425 raise;
4426 end;
4427
4428 --
4429 -- GET_LOOKUP
4430 -- Get the Lookup definition
4431 -- IN
4432 -- x_lookup_type - item type of lookup
4433 -- x_lookup_code - internal name of lookup code
4434 -- OUT
4435 -- x_meaning - display name of lookup code
4436 -- x_description - description of lookup code
4437 -- x_protect_level -
4438 -- x_custom_level -
4439 --
4440 procedure Get_Lookup(
4441 x_lookup_type in varchar2,
4442 x_lookup_code in varchar2,
4443 x_meaning out NOCOPY varchar2,
4444 x_description out NOCOPY varchar2,
4445 x_protect_level out NOCOPY number,
4446 x_custom_level out NOCOPY number
4447 )
4448 is
4449 begin
4450 select MEANING, DESCRIPTION, PROTECT_LEVEL, CUSTOM_LEVEL
4451 into x_meaning, x_description, x_protect_level, x_custom_level
4452 from WF_LOOKUPS
4453 where LOOKUP_TYPE = x_lookup_type
4454 and LOOKUP_CODE = x_lookup_code;
4455 exception
4456 when OTHERS then
4457 Wf_Core.Context('Wf_Load', 'Get_Lookup', x_lookup_type, x_lookup_code);
4458 raise;
4459 end;
4460
4461 --
4462 -- UPDATE_LOOKUP
4463 -- Update the provided fields for Lookup
4464 -- IN
4465 -- x_lookup_type - item type of lookup
4466 -- x_lookup_code - internal name of lookup code
4467 -- x_meaning - display name of lookup code
4468 -- x_description - description of lookup code
4469 -- x_protect_level -
4470 -- x_custom_level -
4471 -- OUT
4472 -- x_level_error - level of error returned from UPLOAD_LOOKUP
4473 -- NOTE
4474 -- Calls GET_LOOKUP to get the default value before calling
4475 -- UPLOAD_LOOKUP.
4476 --
4477 procedure UPDATE_LOOKUP(
4478 x_lookup_type in varchar2,
4479 x_lookup_code in varchar2,
4480 x_meaning in varchar2 ,
4481 x_description in varchar2 ,
4482 x_protect_level in number ,
4483 x_custom_level in number ,
4484 x_level_error out NOCOPY number
4485 )
4486 is
4487 l_meaning varchar2(80);
4488 l_description varchar2(240);
4489 l_protect_level number;
4490 l_custom_level number;
4491 begin
4492 -- Reset any caches that might be running.
4493 WF_CACHE.Reset;
4494
4495 -- Get Lookup
4496 Wf_Load.Get_Lookup(x_lookup_type, x_lookup_code,
4497 l_meaning, l_description, l_protect_level, l_custom_level);
4498
4499 -- Upload Lookup
4500 if (x_meaning is not null) then
4501 l_meaning := x_meaning;
4502 end if;
4503 if (x_description is not null) then
4504 l_description := x_description;
4505 end if;
4506 if (x_protect_level is not null) then
4507 l_protect_level := x_protect_level;
4508 end if;
4509 if (x_custom_level is not null) then
4510 l_custom_level := x_custom_level;
4511 end if;
4512 Wf_Load.UPLOAD_LOOKUP(
4513 x_lookup_type=>x_lookup_type,
4514 x_lookup_code=>x_lookup_code,
4515 x_meaning=>l_meaning,
4516 x_description=>l_description,
4517 x_protect_level=>l_protect_level,
4518 x_custom_level=>l_custom_level,
4519 x_level_error=>x_level_error
4520 );
4521 exception
4522 when OTHERS then
4523 Wf_Core.Context('Wf_Load', 'Update_Lookup', x_lookup_type, x_lookup_code);
4524 raise;
4525 end;
4526
4527
4528 --
4529 -- GET_LOOKUP_CODES
4530 -- Get lookup codes for a lookup type
4531 -- IN
4532 -- p_lookup_type - item type of lookup
4533 -- OUT
4534 -- p_lookup_codes - table of lookup codes
4535 --
4536 procedure Get_Lookup_Codes(
4537 p_lookup_type in varchar2,
4538 p_lookup_codes out NOCOPY t_resultcodeTab)
4539 is
4540 cursor luccur is
4541 select LOOKUP_CODE
4542 from WF_LOOKUPS
4543 where LOOKUP_TYPE = p_lookup_type;
4544
4545 i pls_integer;
4546 begin
4547 i := 1;
4548 for lucr in luccur loop
4549 p_lookup_codes(i) := lucr.lookup_code;
4550 i := i + 1;
4551 end loop;
4552 exception
4553 when OTHERS then
4554 Wf_Core.Context('Wf_Load', 'Get_Lookup_Codes', p_lookup_type);
4555 raise;
4556 end;
4557
4558 --
4559 -- Activity_Exist_In_Process (Deprecated, use WF_ENGINE.Activity_Exist instead)
4560 -- Check if an activity exist in a process
4561 -- IN
4562 -- p_process_item_type
4563 -- p_process_name
4564 -- p_activity_item_type
4565 -- p_anctivity_name
4566 -- active_date
4567 -- iteration - maximum 8 level deep (0-7)
4568 -- RET
4569 -- TRUE if activity exist, FALSE otherwise
4570 --
4571 function Activity_Exist_In_Process (
4572 p_process_item_type in varchar2,
4573 p_process_name in varchar2,
4574 p_activity_item_type in varchar2 ,
4575 p_activity_name in varchar2,
4576 active_date in date ,
4577 iteration in number )
4578 return boolean
4579 is
4580 m_version number;
4581 n number;
4582
4583 cursor actcur(ver number) is
4584 select WPA.ACTIVITY_ITEM_TYPE, WPA.ACTIVITY_NAME
4585 from WF_PROCESS_ACTIVITIES WPA,
4586 WF_ACTIVITIES WA
4587 where WPA.PROCESS_ITEM_TYPE = p_process_item_type
4588 and WPA.PROCESS_NAME = p_process_name
4589 and WPA.PROCESS_VERSION = ver
4590 and WPA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE
4591 and WPA.ACTIVITY_NAME = WA.NAME
4592 and WA.TYPE = 'PROCESS'
4593 and active_date >= WA.BEGIN_DATE
4594 and active_date < nvl(WA.END_DATE, active_date+1);
4595
4596 begin
4597 -- first check the iteration to avoid infinite loop
4598 if (iteration > 7) then
4599 -- debug only
4600 -- Wf_Core.Context('Wf_Load', 'Activity_Exist_In_Process_Overflown',
4601 -- p_process_item_type, p_process_name,
4602 -- nvl(p_activity_item_type, p_process_item_type),
4603 -- p_activity_name);
4604 return FALSE;
4605 end if;
4606
4607 -- then get the active version
4608 begin
4609 select VERSION into m_version
4610 from WF_ACTIVITIES
4611 where ITEM_TYPE = p_process_item_type
4612 and NAME = p_process_name
4613 and active_date >= BEGIN_DATE
4614 and active_date < nvl(END_DATE, active_date + 1);
4615 exception
4616 -- no active version exist
4617 when NO_DATA_FOUND then
4618 return FALSE;
4619
4620 when OTHERS then
4621 raise;
4622 end;
4623
4624 -- then check to see if such activity exist
4625 select count(1) into n
4626 from WF_PROCESS_ACTIVITIES
4627 where PROCESS_ITEM_TYPE = p_process_item_type
4628 and PROCESS_NAME = p_process_name
4629 and PROCESS_VERSION = m_version
4630 and ACTIVITY_ITEM_TYPE = nvl(p_activity_item_type, p_process_item_type)
4631 and ACTIVITY_NAME = p_activity_name;
4632
4633 if (n = 0) then
4634 -- recursively check subprocesses
4635 for actr in actcur(m_version) loop
4636 if (Wf_Load.Activity_Exist_In_Process(
4637 actr.activity_item_type,
4638 actr.activity_name,
4639 nvl(p_activity_item_type, p_process_item_type),
4640 p_activity_name,
4641 active_date,
4642 iteration+1)
4643 ) then
4644 return TRUE;
4645 end if;
4646 end loop;
4647
4648 return FALSE;
4649 else
4650 return TRUE;
4651 end if;
4652
4653 exception
4654 when OTHERS then
4655 Wf_Core.Context('Wf_Load', 'Activity_Exist_In_Process',
4656 p_process_item_type, p_process_name,
4657 nvl(p_activity_item_type, p_process_item_type),
4658 p_activity_name);
4659 raise;
4660 end;
4661
4662 --
4663 -- BeginTransaction
4664 -- (PRIVATE)
4665 -- Calls WF_CACHE.BeginTransaction() to control the calls to WF_CACHE.Reset()
4666 -- so there is not unnecessary locking or update to WFCACHE_META_UPD.
4667 -- Calling this api mandates that EndTransaction is called BEFORE control is
4668 -- returned.
4669 PROCEDURE BeginTransaction
4670 is
4671 begin
4672 if (NOT WF_CACHE.BeginTransaction) then
4673 NULL; --We are ignoring a false condition but may later need to handle.
4674 end if;
4675 end;
4676
4677 --
4678 -- EndTransaction
4679 -- (PRIVATE)
4680 -- Calls WF_CACHE.EndTransaction() to signal the end of the transaction and to
4681 -- call WF_CACHE.Reset() which will update WFCACHE_META_UPD.
4682 -- WARNING: THIS API WILL ISSUE A COMMIT!
4683 PROCEDURE EndTransaction
4684 is
4685 begin
4686 if (NOT WF_CACHE.EndTransaction) then
4687 NULL; --We are ignoring a false condition but may later need to handle.
4688 end if;
4689 commit; --Commit the work.
4690 end;
4691
4692 end WF_LOAD;
[Home] [Help]