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