[Home] [Help]
PACKAGE BODY: APPS.OE_UPGRADE_WF2
Source
1 PACKAGE BODY OE_UPGRADE_WF2 as
2 /* $Header: OEXIUWUB.pls 120.0 2005/06/01 01:02:04 appldev noship $ */
3
4 PROCEDURE Get_Pre_Activity
5 (
6 p_action_id IN NUMBER,
7 p_sequence_id IN NUMBER,
8 v_pre_activity OUT NOCOPY VARCHAR2,
9
10 v_pre_result OUT NOCOPY VARCHAR2
11
12 )
13
14 IS
15
16 BEGIN
17 IF (p_sequence_id > 1) THEN
18
19 SELECT activity_name,activity_result
20 INTO v_pre_activity,
21 v_pre_result
22 FROM oe_upgrade_wf_act_map
23 WHERE activity_seq = p_sequence_id - 1
24 AND action_id = p_action_id;
25 END IF;
26
27 END Get_Pre_Activity;
28
29
30 FUNCTION Get_Post_Activity
31 (
32 p_action_id IN NUMBER,
33 p_sequence_id IN NUMBER
34 )
35 RETURN VARCHAR2
36
37 IS
38 r_post_activity_name VARCHAR2(30) := NULL;
39
40 BEGIN
41 SELECT activity_name
42 INTO r_post_activity_name
43 FROM oe_upgrade_wf_act_map
44 WHERE activity_seq = p_sequence_id + 1
45 AND action_id = p_action_id;
46
47 RETURN (r_post_activity_name);
48 END;
49
50 FUNCTION get_instance_id
51 (
52 p_process_name IN VARCHAR2,
53 p_activity_name IN VARCHAR2,
54 p_instance_label IN VARCHAR2
55 )
56 RETURN number
57
58 IS
59
60 r_instance_id NUMBER;
61
62 BEGIN
63 SELECT nvl(instance_id, -1)
64 INTO r_instance_id
65 FROM wf_process_activities
66 WHERE process_name = p_process_name
67 AND activity_name = p_activity_name
68 AND instance_label = p_instance_label
69 AND process_version = (SELECT max(process_version)
70 FROM wf_process_activities
71 WHERE process_name = p_process_name
72 AND activity_name = p_activity_name
73 AND instance_label = p_instance_label);
74
75 RETURN (r_instance_id);
76
77 END;
78
79 PROCEDURE Insert_Into_Wf_Table
80 (
81 p_from_instance_id IN NUMBER,
82 p_to_instance_id IN NUMBER,
83 p_result_code IN VARCHAR2,
84 p_level_error OUT NOCOPY NUMBER
85
86 )
87
88 IS
89
90 BEGIN
91 wf_core.session_level := 20;
92
93 /* Insert into table wf_activity_transitions */
94 BEGIN
95
96 SELECT 'x'
97 INTO v_dummy
98 FROM wf_activity_transitions
99 WHERE from_process_activity = p_from_instance_id
100 AND result_code = p_result_code
101 AND to_process_activity = p_to_instance_id;
102
103 EXCEPTION
104 WHEN NO_DATA_FOUND THEN
105 wf_load.upload_activity_transition (
106 x_from_process_activity => p_from_instance_id,
107 x_result_code => p_result_code,
108 x_to_process_activity => p_to_instance_id,
109 x_protect_level => 20,
110 x_custom_level => 20,
111 x_arrow_geometry => '1;0;0;0;0.30000;0,0:0,0:',
112 x_level_error => p_level_error
113 );
114 END;
115 END Insert_Into_Wf_Table;
116
117 PROCEDURE Get_Icon_X_value
118 (
119 p_icon_geometry IN VARCHAR2,
120 p_x_value OUT NOCOPY NUMBER
121
122 )
123
124 IS
125
126 flength NUMBER := 0;
127 cnt NUMBER := 0;
128 j NUMBER := 0;
129 rtn NUMBER := 0;
130 cont VARCHAR2(1);
131
132 BEGIN
133
134 cont := 'Y';
135 flength := length(p_icon_geometry);
136
137 WHILE cont = 'Y' AND cnt < flength + 1 LOOP
138
139 IF substr(p_icon_geometry, cnt, 1) = ',' THEN
140 j := cnt - 1;
141 cont := 'N';
142 END IF;
143
144 cnt := cnt + 1;
145 END LOOP;
146
147 IF cont = 'Y' THEN
148 j := flength;
149 END IF;
150
151 rtn := to_number(substr(p_icon_geometry, 1, j));
152 p_x_value := rtn;
153
154 EXCEPTION
155 WHEN others THEN
156 p_x_value := 0;
157 End Get_Icon_X_value;
158
159 PROCEDURE Create_Process_Name
160 (
161 p_item_type IN VARCHAR2,
162 p_line_type IN VARCHAR2,
163 p_cycle_id IN NUMBER
164 )
165
166 IS
167 v_version NUMBER :=1;
168 v_display_name VARCHAR2(80);
169 v_process_name VARCHAR2(80);
170
171 BEGIN
172
173 wf_core.session_level := 20;
174
175 BEGIN
176
177 SELECT 'x' INTO v_dummy
178 FROM wf_activities
179 WHERE item_type = p_item_type
180 AND version = 1
181 AND name = 'UPG_PN_'||p_item_type||'_'||p_line_type||'_'||to_char(p_cycle_id);
182
183 EXCEPTION
184 WHEN NO_DATA_FOUND THEN
185
186 v_error_level := 2001;
187
188 SELECT
189 'UPG_PN_'||p_item_type||'_'||p_line_type||'_'||to_char(cycle_id),
190 'UPG_PN_'||p_item_type||'_'||p_line_type||'_'||rtrim(name)
191 INTO v_process_name,
192 v_display_name
193 FROM
194 so_cycles
195 WHERE cycle_id = p_cycle_id
196 AND p_item_type in ('OEOH', 'OEOL');
197
198
199 /* check if already inserted */
200
201 begin
202 select 'x' into v_dummy
203 from wf_activities
204 where item_Type = P_ITEM_TYPE
205 AND NAME = v_process_name
206 and version = v_version;
207 exception
208 when no_data_found then
209 /* Insert data into WF_ACTIVITIES */
210 wf_load.upload_activity (
211 x_item_type => p_item_type,
212 x_name => v_process_name,
213 x_display_name => v_display_name,
214 x_description => NULL,
215 x_type => 'PROCESS',
216 x_rerun => 'RESET',
217 x_protect_level => 20,
218 x_custom_level => 20,
219 x_effective_date => sysdate - 1,
220 x_function => null,
221 x_function_type => null,
222 x_result_type => '*',
223 x_cost => 0,
224 x_read_role => null,
225 x_write_role => null,
226 x_execute_role => null,
227 x_icon_name => 'PROCESS.ICO',
228 x_message => null,
229 x_error_process => 'RETRY_ONLY',
230 x_expand_role => 'N',
231 x_error_item_type => 'WFERROR',
232 x_runnable_flag => 'Y',
233 x_version => v_version,
234 x_level_error => v_api_error_code
235 );
236 end;
237 END;
238
239 V_ERROR_FLAG := 'N';
240 EXCEPTION
241 WHEN OTHERS THEN
242 v_error_flag := 'Y';
243 v_error_code := sqlcode;
244 v_error_message := 'Error occured in cycle: ' ||to_char(p_cycle_id)
245 ||', Type:'||p_item_type
246 ||' during creation of Process Name...'
247 ||' Oracle error:'||to_char(v_error_code);
248
249 END Create_Process_Name;
250
251 /* Description: Inserts Lookup types using the WF API. One lookup type is
252 created per custom action.
253 */
254
255 PROCEDURE Create_Lookup_Type
256 (
257 p_item_type IN VARCHAR2
258 )
259 IS
260
261 CURSOR c1 IS
262 SELECT
263 'UPG_RT_'||to_char(M.action_id) lookup_type,
264 ltrim(rtrim('UPG_RT_'||substr(M.name,1,26))) display_name,
265 decode(M.result_table,'SO_HEADERS','OEOH','SO_LINES','OEOL','ERROR') item_type,
266 20 protect_level,
267 20 custom_level,
268 M.description description
269 FROM
270 so_actions M,
271 oe_upgrade_wf_act_map U
272 WHERE decode(M.result_table,'SO_HEADERS','OEOH',
273 'SO_LINES','OEOL','ERROR') = p_item_type
274 AND M.action_id not in
275 ( SELECT name from oe_upgrade_wf_obs_codes
276 WHERE type = 'ACTION' )
277 AND M.action_id = U.action_id(+)
278 AND U.action_id is null;
279
280 v_lookup_type VARCHAR2(80);
281
282 BEGIN
283 -- dbms_output.enable(999999999999);
284 wf_core.session_level := 20;
285
286 FOR c2 IN c1 LOOP
287 v_api_error_code := 0;
288
289 BEGIN
290 SELECT 'x' INTO v_dummy
291 FROM wf_lookup_types
292 WHERE lookup_type = c2.lookup_type
293 OR display_name = c2.display_name;
294
295 EXCEPTION
296 WHEN NO_DATA_FOUND THEN
297
298 v_error_level := 2011;
299
300 /* Insert data into WF_LOOKUP_TYPES_TL */
301 wf_load.upload_lookup_type (
302 x_lookup_type => c2.lookup_type,
303 x_display_name => c2.display_name,
304 x_description => c2.description,
305 x_protect_level => c2.protect_level,
306 x_custom_level => c2.custom_level,
307 x_item_type => c2.item_type,
308 x_level_error => v_api_error_code
309 );
310 END;
311
312 v_lookup_type := c2.lookup_type;
313 -- dbms_output.put_line('Completed.. '||v_lookup_type
314 -- ||' Error : '||to_char(v_api_error_code));
315 END LOOP;
316
317 V_ERROR_FLAG := 'N';
318 EXCEPTION
319 WHEN OTHERS THEN
320
321 v_error_flag := 'Y';
322 v_error_code := sqlcode;
323 v_error_message := 'Error occured in creation of Lookup Type:'||v_lookup_type
324 ||'... Oracle error:'||to_char(v_error_code);
325
326 END Create_Lookup_Type;
327
328
329
330 /* Create (Result) Lookup codes for Custom Actions
331 ontupg24.sql
332 */
333
334 PROCEDURE Create_Lookup_Code
335 (
336 p_item_type IN VARCHAR2
337 )
338 IS
339 CURSOR c1 IS
340 SELECT
341 'UPG_RT_'||to_char(SA.action_id) lookup_type,
342 'UPG_RC_'||to_char(SR.result_id) lookup_code,
343 max(SR.name) meaning,
344 max(20) protect_level,
345 max(20) custom_level,
346 max(SR.Description) description
347 FROM
348 so_actions SA,
349 so_action_results SAR,
350 so_results SR,
351 oe_upgrade_wf_act_map U
352 WHERE decode(SA.result_table,'SO_HEADERS','OEOH',
353 'SO_LINES','OEOL','ERROR') = p_item_type
354 AND SA.action_id = SAR.action_id
355 AND SAR.result_id = SR.result_id
356 AND SR.result_id not in
357 ( SELECT name from oe_upgrade_wf_obs_codes
358 WHERE type = 'RESULT' )
359 AND SA.action_id = U.action_id (+)
360 AND U.action_id is null
361 GROUP BY
362 'UPG_RT_'||to_char(SA.action_id),
363 'UPG_RC_'||to_char(SR.result_id);
364
365 v_lookup_type VARCHAR2(80);
366 v_lookup_code VARCHAR2(80);
367
368 BEGIN
369 -- dbms_output.enable(999999999999);
370 v_api_error_code := 0;
371 wf_core.session_level := 20;
372
373 -- dbms_output.put_line('Just entered...');
374 FOR c2 IN c1 LOOP
375 -- dbms_output.put_line('Entering loop..');
376 v_lookup_type := c2.lookup_type;
377 v_lookup_code := c2.lookup_code;
378
379 BEGIN
380 SELECT 'x' INTO v_dummy
381 FROM wf_lookups
382 WHERE ( lookup_type = c2.lookup_type AND
383 lookup_code = c2.lookup_code)
384 OR ( lookup_type = c2.lookup_type AND
385 meaning = c2.meaning);
386
387 EXCEPTION
388 WHEN NO_DATA_FOUND THEN
389
390 v_error_level := 2021;
391
392 /* Insert data into WF_LOOKUPS_TL */
393 wf_load.upload_lookup
394 (
395 x_lookup_type => c2.lookup_type,
396 x_lookup_code => c2.lookup_code,
397 x_meaning => c2.meaning,
398 x_description => c2.description,
399 x_protect_level => c2.protect_level,
400 x_custom_level => c2.custom_level,
401 x_level_error => v_api_error_code
402 );
403 END;
404
405 -- dbms_output.put_line('Completed .. '||v_lookup_type||' -- '
406 -- ||v_lookup_code||'.. Error:'||to_char(v_api_error_code));
407 END LOOP;
408
409 V_ERROR_FLAG := 'N';
410 EXCEPTION
411 WHEN OTHERS THEN
412
413 v_error_flag := 'Y';
414 v_error_code := sqlcode;
415 v_error_message := 'Error occured in creation of Lookup Type: '||v_lookup_code
416 ||'... Oracle error:'||to_char(v_error_code);
417
418 END Create_Lookup_Code;
419
420
421 /*===================================================================*/
422 -- for non seeded (custom) actions
423 /*===================================================================*/
424
425 PROCEDURE Create_Activity_Name
426 (
427 p_item_type IN VARCHAR2
428 )
429
430 IS
431 CURSOR c1 IS
432 SELECT
433 decode(result_table,'SO_HEADERS','OEOH',
434 'SO_LINES','OEOL','ERROR') item_type,
435 'UPG_AN_'||to_Char(SA.action_id) activity_name,
436 SA.Name display_name,
437 1 version,
438 decode(sa.action_approval,'Y','NOTICE','FUNCTION') type,
439 'LOOP' rerun,
440 'N' expand_role,
441 sysdate - 1 BEGIN_date,
442 null end_date,
443 decode(sa.action_approval,'Y','',
447 null read_role,
444 'OE_WF_UPGRADE_UTIL.UPGRADE_CUSTOM_ACTIVITY_BLOCK') function,
445 'UPG_RT_'||to_Char(SA.action_id) result_Type,
446 decode(sa.action_approval,'Y', 60, null) cost,
448 null write_role,
449 null execute_role,
450 decode(sa.action_approval,'Y','NOTIFY.ICO','FUNCTION.ICO') icon_name,
451 decode(sa.action_approval,'Y',
452 'UPG_AN_'||to_char(sa.action_id), NULL) message,
453 'RETRY_ONLY' error_process,
454 'WFERROR' error_item_type,
455 'N' runnable_flag,
456 null function_type,
457 result_column result_column,
458 sa.action_id action_id
459 FROM
460 so_actions SA,
461 oe_upgrade_wf_act_map U
462 WHERE SA.action_id = U.action_id(+)
463 AND U.action_id is null
464 AND SA.action_id not in
465 ( SELECT name from oe_upgrade_wf_obs_codes
466 WHERE type = 'ACTION' )
467 AND decode(result_table,'SO_HEADERS','OEOH',
468 'SO_LINES','OEOL','ERROR') = p_item_type
469 AND sa.action_id in (select action_id from so_cycle_actions);
470
471 v_message varchar2(80);
472 v_name VARCHAR2(80);
473 v_version NUMBER;
474 v_short_name varchar2(80);
475 v_fyi_flag varchar2(1);
476 v_result_type varchar2(30);
477
478 BEGIN
479 -- dbms_output.enable('999999999');
480 v_api_error_code := 0;
481 wf_core.session_level := 20;
482
483 FOR c2 IN c1 LOOP
484
485 v_error_level := 2031;
486
487 v_fyi_flag := 'N';
488 begin
489 select 'N' into v_fyi_flag from so_action_results
490 where action_id = c2.action_id
491 and rownum = 1;
492 exception
493 when no_data_found then
494 v_fyi_flag := 'Y';
495 end;
496
497 v_name := c2.activity_name;
498
499 if c2.item_type = 'OEOH' then
500 v_short_name := 'HDR_SHORT_DESCRIPTOR';
501 elsif c2.item_type = 'OEOL' then
502 v_short_name := 'LIN_SHORT_DESCRIPTOR';
503 end if;
504
505 -- dbms_output.put_line('Starting : '||v_name);
506
507 BEGIN
508 SELECT 'x' INTO v_dummy
509 FROM wf_activities
510 WHERE item_type = p_item_type
511 AND version = 1
512 AND name = c2.activity_name;
513
514 EXCEPTION
515 WHEN NO_DATA_FOUND THEN
516
517 if v_fyi_flag = 'Y' then
518 v_result_type := '*';
519 else
520 v_result_type := c2.result_Type;
521 end if;
522
523 v_error_level := 2032;
524 /* Insert data into WF_ACITVITIES */
525 WF_LOAD.UPLOAD_ACTIVITY (
526 x_item_type => c2.item_type,
527 x_name => c2.activity_name,
528 x_display_name => c2.display_name,
529 x_description => c2.activity_name,
530 x_type => c2.type,
531 x_rerun => c2.rerun,
532 x_protect_level => 20,
533 x_custom_level => 20,
534 x_effective_date => c2.BEGIN_date,
535 x_function => c2.function,
536 x_function_type => c2.function_type,
537 x_result_type => v_result_type,
538 x_cost => c2.cost,
539 x_read_role => c2.read_role,
540 x_write_role => c2.write_role,
541 x_execute_role => c2.execute_role,
542 x_icon_name => c2.icon_name,
543 x_message => c2.message,
544 x_error_process => c2.error_process,
545 x_expand_role => c2.expand_role,
546 x_error_item_type => c2.error_item_type,
547 x_runnable_flag => c2.runnable_flag,
548 x_version => v_version,
549 x_level_error => v_api_error_code
550 );
551 --sam
552 if c2.type <> 'NOTICE' then
553 WF_LOAD.UPLOAD_ACTIVITY_ATTRIBUTE (
554 x_activity_item_type => c2.item_type,
555 x_activity_name => c2.activity_name,
556 x_activity_version => v_version,
557 x_name => 'S_COLUMN',
558 x_display_name => c2.display_name||'-S_COLUMN',
559 x_description => c2.display_name||'-S_COLUMN',
560 x_sequence => 0,
564 x_subtype => 'SEND',
561 x_type => 'VARCHAR2',
562 x_protect_level => 20,
563 x_custom_level => 20,
565 x_format => '',
566 x_default => c2.activity_name,
567 x_value_type => 'CONSTANT',
568 x_level_error => v_api_error_code
569 ) ;
570 else
571 Wf_Load.UPLOAD_MESSAGE (
572 x_type=> c2.item_type,
573 x_name=> c2.activity_name,
574 x_display_name=> v_name,
575 x_description=> v_name,
576 x_subject=> c2.display_name,
577 x_body=> '&'||v_short_name,
578 x_html_body=> null,
579 x_protect_level=> 20,
580 x_custom_level=> 20,
581 x_default_priority=> 50,
582 x_read_role=> null,
583 x_write_role=> null,
584 x_level_error=> v_api_error_code
585 );
586
587 Wf_Load.UPLOAD_MESSAGE_ATTRIBUTE(
588 x_message_type=> c2.item_Type,
589 x_message_name=> c2.activity_name,
590 x_name=> v_short_name,
591 x_display_name=> v_short_name,
592 x_description=> v_short_name,
593 x_sequence=> 0,
594 x_type=> 'DOCUMENT',
595 x_subtype=> 'SEND',
596 x_protect_level=> 20,
597 x_custom_level=> 20,
598 x_format=> '_top',
599 x_default=> v_short_name,
600 x_value_type=> 'ITEMATTR',
601 x_attach=> 'N',
602 x_level_error=> v_api_error_code
603 );
604
605 if v_fyi_flag = 'N' then
606 Wf_Load.UPLOAD_MESSAGE_ATTRIBUTE(
607 x_message_type=> c2.item_Type,
608 x_message_name=> c2.activity_name,
609 x_name=> 'RESULT',
610 x_display_name=> 'RESULT',
611 x_description=> 'RESULT',
612 x_sequence=> 1,
613 x_type=> 'LOOKUP',
614 x_subtype=> 'RESPOND',
615 x_protect_level=> 20,
616 x_custom_level=> 20,
617 x_format=> c2.result_Type,
618 x_default=> NULL,
619 x_value_type=> 'CONSTANT',
620 x_attach=> 'N',
621 x_level_error=> v_api_error_code
622 );
623 end if;
624 end if;
625 END;
626
627 -- dbms_output.put_line('Completed : '||v_name||' Error : '
628 -- ||to_char(v_api_error_code)||'---'
629 -- ||to_char(wf_core.session_level));
630 END LOOP;
631
632 V_ERROR_FLAG := 'N';
633 EXCEPTION
634 WHEN OTHERS THEN
635
636 v_error_flag := 'Y';
637 v_error_code := sqlcode;
638 v_error_message := 'Error occured in creation of Activity Name:'||v_name
639 ||'... Oracle error:'||to_char(v_error_code);
640
641 END Create_Activity_Name;
642
643
644 /*===================================================================*/
645 -- Create processes
646 -- for both seeded and custom activities
647 /*===================================================================*/
648
649 PROCEDURE Create_Process_Activity
650 (
651 p_item_type IN VARCHAR2,
652 p_cycle_id IN NUMBER,
653 p_line_type IN VARCHAR2
654 )
655 IS
656 CURSOR c1 IS
657 SELECT
658 p_item_type process_item_type,
659 'UPG_PN_'||p_item_type||'_'
660 ||p_line_type||'_'||to_char(p_cycle_id) process_name,
661 1 process_version,
662 nvl(oemap.activity_item_type,p_item_type) activity_item_type,
663 nvl(oemap.activity_name,
664 'UPG_AN_'||to_char(soac.action_id)) activity_name,
665 wf_process_activities_s.nextval instance_id,
666 nvl(oemap.activity_name,
670 'UPG_AN_'||to_char(soac.action_id)),
667 'UPG_ILN_'||to_char(sca.cycle_action_id)) instance_label,
668
669 decode(nvl(oemap.activity_name,
671 'START','START','END','END',NULL) start_end,
672 decode(soac.action_approval,'Y','ITEMATTR','CONSTANT') perform_role_type,
673 decode(soac.action_approval,'Y','NOTIFICATION_APPROVER','') perform_role,
674 soac.result_column result_column,
675 soac.action_approval approval
676 FROM
677 so_cycle_actions sca,
678 so_actions soac,
679 oe_upgrade_wf_act_map oemap
680 WHERE sca.cycle_id = p_cycle_id
681 AND sca.action_id = soac.action_id
682 AND sca.action_id = oemap.action_id (+)
683 AND ( (soac.action_id in (SELECT action_id FROM oe_upgrade_wf_act_map
684 WHERE line_type in ( p_line_type , 'BOTH')) )
685 OR (soac.action_id not in (SELECT action_id FROM oe_upgrade_wf_act_map )))
686 AND SOAC.action_id not in
687 ( SELECT name FROM oe_upgrade_wf_obs_codes
688 WHERE type = 'ACTION' )
689 AND ( sca.action_id in (SELECT action_id from so_action_pre_reqs)
690 OR sca.cycle_action_id in (SELECT cycle_action_id from so_action_pre_reqs))
691 AND decode(soac.result_table,'SO_HEADERS','OEOH',
692 'SO_LINES','OEOL','ERROR') = p_item_type;
693
694 v_process_name VARCHAR2(80);
695 v_activity_name VARCHAR2(80);
696 v_check_duplicate_flag VARCHAR2(1);
697 v_instance_label VARCHAR2(80);
698 v_instance_id NUMBER;
699 v_booked_count NUMBER;
700 BEGIN
701 -- dbms_output.enable('999999999');
702 wf_core.session_level := 20;
703 FOR c2 in c1 LOOP
704 /* We want to add UPG_BOOK_PROCESS_ASYNCH only if the original cycle has
705 an action with a pre-req of Enter -Booked (1-1) */
706 IF c2.activity_name = 'UPG_BOOK_PROCESS_ASYNCH' THEN
707 SELECT count(1)
708 INTO v_booked_count
709 FROM so_action_pre_reqs
710 WHERE action_id = 1
711 AND cycle_action_id in (SELECT cycle_action_id
712 FROM so_cycle_actions
713 WHERE cycle_id = p_cycle_id)
714 AND result_id = 1;
715
716 IF v_booked_count = 0 THEN
717 GOTO end_of_loop;
718 END IF;
719 END IF;
720 /* end of fix */
721
722
723 v_error_level := 2041;
724 v_api_error_code := 0;
725 v_process_name := c2.process_name;
726 v_activity_name := c2.activity_name;
727 -- dbms_output.put_line('Starting ..'||v_process_name ||'---'||v_activity_name);
728
729 BEGIN
730 v_check_duplicate_flag := 'N';
731
732 SELECT 'Y' INTO v_check_duplicate_flag
733 FROM wf_process_activities
734 WHERE process_item_type = c2.process_item_Type
735 AND process_name = c2.process_name
736 AND process_version = c2.process_version
737 AND instance_label = c2.instance_label;
738 EXCEPTION
739 WHEN TOO_MANY_ROWS THEN
740 v_check_duplicate_flag := 'Y';
741 WHEN NO_DATA_FOUND THEN
742 v_check_duplicate_flag := 'N';
743 END;
744
745 IF v_check_duplicate_flag = 'N' THEN
746
747 v_error_level := 2042;
748 -- dbms_output.put_line('attempting to insert:= '||c2.process_name||'**'||c2.activity_name);
749 wf_load.upload_process_activity
750 (
751 x_process_item_type => c2.process_item_type,
752 x_process_name => c2.process_name,
753 x_process_version => c2.process_version,
754 x_activity_item_type => c2.activity_item_type,
755 x_activity_name => c2.activity_name,
756 x_instance_id => c2.instance_id,
757 x_instance_label => c2.instance_label,
758 x_protect_level => 20,
759 x_custom_level => 20,
760 x_start_end => c2.start_end,
761 x_default_result => NULL,
762 x_icon_geometry => '0,0',
763 x_perform_role => c2.perform_role,
764 x_perform_role_type => c2.perform_role_type,
765 x_user_comment => NULL,
766 x_level_error => v_api_error_code
767 );
768 -- dbms_output.put_line('inserted := '||c2.process_name||'**'||c2.activity_name);
769
770
771 if nvl(c2.approval,'-') <> 'Y' then
772 WF_LOAD.UPLOAD_ACTIVITY_ATTR_VALUE (
773 x_process_activity_id => c2.instance_id,
774 x_name => 'S_COLUMN',
775 x_protect_level => 20,
779 x_effective_date => sysdate - 1,
776 x_custom_level => 20,
777 x_value => c2.result_column,
778 x_value_type => 'CONSTANT',
780 x_level_error => v_api_error_code
781 );
782 end if;
783
784 ELSE
785 -- dbms_output.put_line('Spared(duplicate)..'||v_process_name ||'---'||v_activity_name);
786 NULL;
787 END IF;
788
789 -- dbms_output.put_line('Complted ..'||v_process_name
790 -- ||'---'||v_activity_name
791 -- ||' Error code:'||to_Char(v_api_error_code));
792 <<end_of_loop>>
793 null;
794 END LOOP;
795
796 IF (p_item_type = 'OEOL') THEN
797 BEGIN
798 SELECT 'x' INTO v_dummy
799 FROM wf_process_activities
800 WHERE process_name = 'UPG_PN_OEOL_'||p_line_type||'_'||to_char(p_cycle_id)
801 AND activity_name = 'START';
802 EXCEPTION
803 WHEN TOO_MANY_ROWS THEN
804 null;
805 WHEN NO_DATA_FOUND THEN
806 SELECT wf_process_activities_s.nextval
807 INTO v_instance_id
808 FROM dual;
809
810 -- dbms_output.put_line('will be loading the Pr.Act. ');
811 v_error_level := 2142;
812 WF_LOAD.UPLOAD_PROCESS_ACTIVITY (
813 x_process_item_type => p_item_type,
814 x_process_name => 'UPG_PN_OEOL_'||p_line_type||'_'||to_char(p_cycle_id),
815 x_process_version => 1,
816 x_activity_item_type => 'WFSTD',
817 x_activity_name => 'START',
818 x_instance_id => v_instance_id,
819 x_instance_label => 'START',
820 x_protect_level => 20,
821 x_custom_level => 20,
822 x_start_end => 'START',
823 x_default_result => null,
824 x_icon_geometry => '0,0',
825 x_perform_role => null,
826 x_perform_role_type => 'CONSTANT',
827 x_user_comment => null,
828 x_level_error => v_api_error_code);
829
830 -- dbms_output.put_line('loaded the Pr.Act. ');
831 END;
832 END IF;
833
834 V_ERROR_FLAG := 'N';
835 EXCEPTION
836 WHEN OTHERS THEN
837
838 v_error_flag := 'Y';
839 v_error_code := sqlcode;
840 v_error_message := 'Error occured in cycle: ' ||to_char(p_cycle_id)
841 ||', Type:'||p_item_type
842 ||' during creation of Process Activities ...'
843 ||' Oracle error:'||to_char(v_error_code);
844
845 End Create_Process_Activity;
846
847
848 /*
849 To populate the local table oe_action_pre_reqs from so_action_pre_reqs
850 This takes care of the putting an AND activitiy in the appropriate place.
851 */
852
853 PROCEDURE Create_Activity_And
854 (
855 p_item_type IN VARCHAR2,
856 p_line_type IN VARCHAR2,
857 p_cycle_id IN NUMBER
858 )
859 IS
860 CURSOR c1 IS
861 SELECT
862 spr.cycle_action_id,
863 spr.action_id,
864 spr.result_id,
865 spr.group_number
866 FROM so_action_pre_reqs spr,
867 so_cycle_actions sca,
868 so_actions sa,
869 so_actions sa2
870 WHERE spr.cycle_action_id = sca.cycle_action_id
871 AND sca.cycle_id = p_cycle_id
872 AND spr.action_id = sa.action_id
873 AND sca.action_id = sa2.action_id
874 AND decode(sa2.result_table,'SO_HEADERS','OEOH','SO_LINES','OEOL','ERROR')=p_item_type
875 AND spr.action_id IN
876 (SELECT action_id
877 FROM so_cycle_actions
878 WHERE cycle_id = p_cycle_id)
879 AND spr.cycle_action_id IN
880 (SELECT cycle_action_id
881 FROM so_Cycle_actions
882 WHERE cycle_id = p_cycle_id)
883 AND decode(sa.result_table,'SO_HEADERS','OEOH','SO_LINES','OEOL','ERROR')=p_item_type
884 AND spr.result_id NOT IN
885 ( SELECT result_id FROM so_results, oe_upgrade_wf_obs_codes
886 WHERE type ='RESULT'
887 AND so_results.result_id = oe_upgrade_wf_obs_codes.name )
888 AND spr.action_id NOT IN
889 ( SELECT action_id FROM so_actions a, oe_upgrade_wf_obs_codes b
890 WHERE type = 'ACTION'
891 AND a.action_id = b.name )
892 /* Transition from Pick Release and Backorder Release obsoleted */
893 AND spr.action_id NOT IN (2,4)
894 AND (sa.action_id <> 3
895 /* Transition from Ship confirm to Inv. Intfce. obsoleted */
896 OR (sa.action_id = 3 and sa2.action_id <> 11))
897 AND ( SPR.ACTION_ID NOT IN (2,3,4,11,16) or
898 (spr.action_id in (2,3,4,11,16) and sca.action_id not in (2,3,4,11,16)) )
899 AND ( (sa.action_id in (SELECT action_id FROM oe_upgrade_wf_act_map
903 WHERE line_type in ( p_line_type , 'BOTH')) )
900 WHERE line_type in ( p_line_type , 'BOTH')) )
901 OR (sa.action_id not in (SELECT action_id FROM oe_upgrade_wf_act_map )))
902 AND ( (sa2.action_id in (SELECT action_id FROM oe_upgrade_wf_act_map
904 OR (sa2.action_id not in (SELECT action_id FROM oe_upgrade_wf_act_map )))
905 ORDER BY spr.cycle_action_id, spr.group_number;
906
907 v_cycle_action_id NUMBER;
908 v_cycle_id NUMBER;
909 v_action_id NUMBER;
910 v_group_number NUMBER;
911 v_last_OR_inst_id NUMBER;
912 v_cyc_act_inst_id NUMBER;
913 v_act_inst_id NUMBER;
914 v_last_instance_id NUMBER;
915 v_process_name VARCHAR2(30);
916 v_instance_label VARCHAR2(30);
917 v_mul_rec_grp_flag VARCHAR2(1);
918 v_api_error_level NUMBER;
919
920 BEGIN
921 -- dbms_output.enable('999999999');
922 -- dbms_output.put_line('Entered program');
923 wf_core.session_level := 20;
924 v_cycle_action_id := 0;
925
926 FOR c2 IN c1 LOOP
927
928 v_error_level := 2051;
929 -- dbms_output.put_line('In the CURSOR..fetched grp #:'||to_char(c2.group_number));
930 IF c2.cycle_action_id <> v_cycle_action_id THEN
931 v_cycle_action_id := c2.cycle_action_id;
932
933 SELECT
934 cycle_id,
935 action_id
936 INTO
937 v_cycle_id,
938 v_action_id
939 FROM so_cycle_actions
940 WHERE cycle_action_id = v_cycle_Action_id;
941
942 v_process_name := 'UPG_PN_'||p_item_type||'_'
943 ||p_line_type||'_'||to_char(v_cycle_id);
944 v_group_number := null;
945
946 -- dbms_output.put_line('In the stage1..process_name: '||v_process_name);
947 END IF;
948
949 BEGIN
950 -- dbms_output.put_line('In the stage 2..');
951
952 v_mul_rec_grp_flag := null;
953
954 SELECT 'Y' INTO v_mul_rec_grp_flag
955 FROM oe_upgrade_wf_mulgrp_v
956 WHERE cycle_action_id = c2.cycle_action_id
957 AND action_id = c2.action_id;
958 EXCEPTION
959 WHEN no_data_found THEN
960 -- dbms_output.put_line('In the stage 3..');
961 v_mul_rec_grp_flag := 'N';
962 WHEN too_many_rows THEN
963 -- dbms_output.put_line('In the stage 3.1..');
964 v_mul_rec_grp_flag := 'Y';
965 END;
966
967 IF v_mul_rec_grp_flag = 'N' THEN
968
969 v_error_level := 2052;
970 -- dbms_output.put_line('In the stage 4..');
971 INSERT INTO oe_action_pre_reqs
972 (
973 cycle_action_id,
974 action_id,
975 result_id,
976 group_number,
977 cycle_id,
978 type,
979 line_type,
980 instance_label,
981 instance_id
982 )
983 VALUES
984 (
985 c2.cycle_action_id,
986 c2.action_id,
987 c2.result_id,
988 c2.group_number,
989 v_cycle_id,
990 p_item_type,
991 p_line_type,
992 null,
993 null
994 );
995 ELSE
996
997 v_error_level := 2053;
998 -- dbms_output.put_line('In the stage 5..');
999 IF c2.group_number = v_group_number THEN
1000
1001 v_error_level := 2054;
1002 -- dbms_output.put_line('In the stage 6..');
1003 INSERT INTO oe_action_pre_reqs
1004 (
1005 cycle_action_id,
1006 action_id,
1007 result_id,
1008 group_number,
1009 cycle_id,
1010 type,
1011 line_type,
1012 instance_label,
1013 instance_id
1014 )
1015 VALUES
1016 (
1017 null,
1018 c2.action_id,
1019 c2.result_id,
1020 c2.group_number,
1021 v_cycle_id,
1022 p_item_type,
1023 p_line_type,
1024 v_instance_label,
1025 v_last_instance_id
1026 );
1027 ELSE
1028 v_error_level := 2055;
1029 -- dbms_output.put_line('In the stage 7..');
1030 v_group_number := c2.group_number;
1034 SELECT wf_process_activities_s.nextval
1031 v_instance_label := 'AND_'||to_Char(v_cycle_action_id)||'_'||
1032 to_Char(v_group_number);
1033
1035 INTO v_last_instance_id
1036 FROM dual;
1037
1038 -- dbms_output.put_line('In the stage 7.1..');
1039 -- dbms_output.put_line(v_process_name||'- 1 '||v_instance_label
1040 -- ||'- '||to_char(v_last_instance_id));
1041
1042 /* Insert data into WF_PROCESS_ACTIVITIES */
1043 WF_LOAD.UPLOAD_PROCESS_ACTIVITY (
1044 x_process_item_type => p_item_type,
1045 x_process_name => v_process_name,
1046 x_process_version => 1,
1047 x_activity_item_type => 'WFSTD',
1048 x_activity_name => 'AND',
1049 x_instance_id => v_last_instance_id,
1050 x_instance_label => v_instance_label,
1051 x_protect_level => 20,
1052 x_custom_level => 20,
1053 x_start_end => null,
1054 x_default_result => null,
1055 x_icon_geometry => '0,0',
1056 x_perform_role => null,
1057 x_perform_role_type => 'CONSTANT',
1058 x_user_comment => null,
1059 x_level_error => v_api_error_level
1060 );
1061
1062 -- dbms_output.put_line('In the stage 7.2 error:'
1063 -- ||to_char(v_api_error_level));
1064
1065 v_error_level := 2056;
1066 INSERT INTO oe_action_pre_reqs
1067 (
1068 cycle_action_id,
1069 action_id,
1070 result_id,
1071 group_number,
1072 cycle_id,
1073 type,
1074 line_type,
1075 instance_label,
1076 instance_id
1077 )
1078 values
1079 (
1080 c2.cycle_action_id,
1081 null,
1082 null,
1083 c2.group_number,
1084 v_cycle_id,
1085 p_item_type,
1086 p_line_type,
1087 v_instance_label,
1088 v_last_instance_id
1089 );
1090
1091 v_error_level := 2057;
1092 INSERT INTO oe_action_pre_reqs
1093 (
1094 cycle_action_id,
1095 action_id,
1096 result_id,
1097 group_number,
1098 cycle_id,
1099 type,
1100 line_type,
1101 instance_label,
1102 instance_id
1103 )
1104 VALUES
1105 (
1106 null,
1107 c2.action_id,
1108 c2.result_id,
1109 c2.group_number,
1110 v_cycle_id,
1111 p_item_type,
1112 p_line_type,
1113 v_instance_label,
1114 v_last_instance_id
1115 );
1116 END IF;
1117 END IF;
1118 -- dbms_output.put_line('In the stage 8..');
1119 END LOOP;
1120 -- dbms_output.put_line('In the end..');
1121
1122 V_ERROR_FLAG := 'N';
1123 EXCEPTION
1124 WHEN OTHERS THEN
1125
1126 v_error_flag := 'Y';
1127 v_error_code := sqlcode;
1128 v_error_message := 'Error occured in cycle: ' ||to_char(p_cycle_id)
1129 ||', Type:'||p_item_type
1130 ||' during creation of Activity AND...'
1131 ||' Oracle error:'||to_char(v_error_code);
1132
1133 END Create_Activity_And;
1134
1135
1136 /*============================================================================
1137 to create CONTINUEFLOW and WAITFORFLOW for inter type dependencies
1138 ============================================================================*/
1139
1140 PROCEDURE Create_Header_Line_Dependency
1141 (
1142 p_cycle_id IN NUMBER,
1143 p_line_type IN VARCHAR2
1144 )
1145
1146 IS
1147 v_result_table VARCHAR2(30);
1148 v_action_id NUMBER;
1149 v_cycle_id NUMBER;
1150 v_cycle_action_id NUMBER;
1151 v_hdr_process_name VARCHAR2(80);
1152 v_lin_process_name VARCHAR2(80);
1153 v_hdr_activity_name VARCHAR2(80);
1157 v_instance_id NUMBER;
1154 v_lin_activity_name VARCHAR2(80);
1155 v_api_error_code NUMBER;
1156 v_check_duplicate_flag VARCHAR2(1);
1158 v_hdr_act_instance_id NUMBER;
1159 v_lin_act_instance_id NUMBER;
1160 v_version NUMBER;
1161 v_book_flag VARCHAR2(1);
1162 v_result_id NUMBER;
1163 v_start_instance_id NUMBER;
1164
1165 CURSOR c1 IS
1166 SELECT
1167 b.cycle_id,
1168 b.action_id lin_action_id,
1169 a.cycle_action_id,
1170 a.result_id,
1171 a.action_id hdr_action_id,
1172 a.group_number
1173 FROM so_action_pre_reqs a, so_Cycle_actions b
1174 WHERE b.cycle_id = p_cycle_id
1175 AND ((a.action_id IN (SELECT action_id
1176 FROM oe_upgrade_wf_act_map
1177 WHERE line_type IN ( p_line_type , 'BOTH')) )
1178 OR (a.action_id NOT IN (SELECT action_id
1179 FROM oe_upgrade_wf_act_map )))
1180 AND ((b.action_id IN (SELECT action_id
1181 FROM oe_upgrade_wf_act_map
1182 WHERE line_type IN ( p_line_type , 'BOTH')) )
1183 OR (b.action_id NOT IN (SELECT action_id
1184 FROM oe_upgrade_wf_act_map )))
1185 AND a.action_id IN
1186 (SELECT action_id
1187 FROM so_actions
1188 WHERE result_table = 'SO_HEADERS'
1189 AND (action_id = 1 or action_id not in (select action_id from oe_upgrade_wf_act_map)))
1190 AND a.cycle_action_id IN
1191 (SELECT cycle_action_id
1192 FROM so_cycle_actions
1193 WHERE action_id in
1194 (SELECT action_id
1195 FROM so_actions
1196 WHERE result_table = 'SO_LINES'))
1197 AND a.cycle_action_id = b.cycle_action_id
1198 AND a.cycle_action_id IN
1199 (SELECT cycle_action_id
1200 FROM so_Cycle_actions
1201 WHERE cycle_id = p_cycle_id);
1202
1203 CURSOR c3 IS
1204 SELECT
1205 pr.instance_id,
1206 pr.instance_label,
1207 pr.rowid
1208 FROM oe_action_pre_reqs pr
1209 WHERE pr.cycle_id = v_cycle_id
1210 AND pr.action_id = v_action_id
1211 and pr.line_type = p_line_type -- included 3/24/00
1212 FOR UPDATE;
1213
1214 CURSOR c5 IS
1215 SELECT
1216 pr.instance_id,
1217 pr.rowid
1218 FROM oe_action_pre_reqs pr
1219 WHERE pr.cycle_id = v_cycle_id
1220 AND pr.cycle_action_id = v_cycle_action_id
1221 AND pr.line_type = p_line_type -- included 3/24/00
1222 FOR UPDATE;
1223
1224 BEGIN
1225 -- dbms_output.enable('9999999999');
1226 -- dbms_output.put_line('Starting Program');
1227 wf_core.session_level := 20;
1228 v_book_flag := 'N';
1229 FOR c2 IN c1 LOOP
1230 v_error_level := 2061;
1231 -- dbms_output.put_line('Entering INTO C2 loop');
1232 v_hdr_process_name := 'UPG_PN_OEOH_REG_' || to_Char(c2.cycle_id);
1233 v_lin_process_name := 'UPG_PN_OEOL_'||p_line_type||'_'||to_Char(c2.cycle_id);
1234 v_cycle_id := c2.cycle_id;
1235 v_cycle_action_id := c2.cycle_action_id;
1236
1237 -- dbms_output.put_line('Stage D1');
1238
1239 IF c2.hdr_action_id = 1 AND c2.result_id = 1 THEN
1240 v_hdr_activity_name := 'BOOK_CONT_L';
1241 v_lin_activity_name := 'BOOK_WAIT_FOR_H';
1242 v_book_flag := 'Y';
1243 -- dbms_output.put_line('Stage D2');
1244 ELSE
1245 v_hdr_activity_name := 'UPG_AN_OEOH_'||to_Char(c2.hdr_action_id)
1246 ||'_CONT_L';
1247 v_lin_activity_name := 'UPG_AN_OEOL_'||to_Char(c2.hdr_action_id)
1248 ||'_WAIT_FOR_H';
1249 v_book_flag := 'N';
1250 -- dbms_output.put_line('Stage D3');
1251 END IF;
1252
1253 if c2.hdr_action_id <> 1 then /* for custom actions only */
1254 --dbms_output.put_line('Cycle: ' || to_char(p_cycle_id));
1255 --dbms_output.put_line('Stage D4 - hdr action_id ' || to_char(c2.hdr_action_id));
1256 /* For incoporating a new activity at Header Flow */
1257
1258 IF v_book_flag = 'N' THEN
1259 BEGIN
1260 SELECT 0 INTO v_error_level
1261 FROM WF_ACTIVITIES
1262 WHERE NAME = v_hdr_activity_name
1263 AND ITEM_TYPE = 'OEOH'
1264 AND VERSION = 1;
1265 EXCEPTION
1266 WHEN NO_DATA_FOUND THEN
1267 v_error_level := 2062;
1268 -- dbms_output.put_line('Stage D5');
1269 wf_load.upload_activity (
1270 x_item_type => 'OEOH',
1271 x_name => v_hdr_activity_name,
1272 x_display_name => v_hdr_activity_name,
1273 x_description => NULL,
1277 x_custom_level => 20,
1274 x_type => 'FUNCTION',
1275 x_rerun => 'RESET',
1276 x_protect_level => 20,
1278 x_effective_date => sysdate - 1,
1279 x_function => 'WF_STANDARD.CONTINUEFLOW',
1280 x_function_type => null,
1281 x_result_type => '*',
1282 x_cost => 0,
1283 x_read_role => null,
1284 x_write_role => null,
1285 x_execute_role => null,
1286 x_icon_name => 'FUNCTION.ICO',
1287 x_message => null,
1288 x_error_process => 'RETRY_ONLY',
1289 x_expand_role => 'N',
1290 x_error_item_type => 'WFERROR',
1291 x_runnable_flag => 'N',
1292 x_version => v_version,
1293 x_level_error => v_api_error_code
1294 );
1295
1296 -- dbms_output.put_line('Stage D6');
1297
1298 -- dbms_output.put_line('Activity inserted--'||v_hdr_activity_name);
1299
1300 v_error_level := 2063;
1301 WF_LOAD.UPLOAD_ACTIVITY_ATTRIBUTE (
1302 x_activity_item_type => 'OEOH',
1303 x_activity_name => v_hdr_activity_name,
1304 x_activity_version => 1,
1305 x_name => 'WAITING_ACTIVITY',
1306 x_display_name => v_hdr_activity_name||'-WAITING_ACTIVITY',
1307 x_description => v_hdr_activity_name||'-WAITING_ACTIVITY',
1308 x_sequence => 0,
1309 x_type => 'VARCHAR2',
1310 x_protect_level => 20,
1311 x_custom_level => 20,
1312 x_subtype => 'SEND',
1313 x_format => '',
1314 x_default => v_lin_activity_name,
1315 x_value_type => 'CONSTANT',
1316 x_level_error => v_api_error_code
1317 ) ;
1318
1319 -- dbms_output.put_line('Stage D7');
1320 -- dbms_output.put_line('api error attr: ' || to_char(v_api_error_code));
1321 -- dbms_output.put_line('Attribute inserted-1-'||v_hdr_activity_name||'-WAITING_ACTIVITY');
1322 -- dbms_output.put_line('attempting insert-'||v_hdr_activity_name||'-WAITING_FLOW');
1323
1324 WF_LOAD.UPLOAD_ACTIVITY_ATTRIBUTE (
1325 x_activity_item_type => 'OEOH',
1326 x_activity_name => v_hdr_activity_name,
1327 x_activity_version => 1,
1328 x_name => 'WAITING_FLOW',
1329 x_display_name => v_hdr_activity_name||'-WAITING_FLOW',
1330 x_description => v_hdr_activity_name||'-WAITING_FLOW',
1331 x_sequence => 1,
1332 x_type => 'LOOKUP',
1333 x_protect_level => 20,
1334 x_custom_level => 20,
1335 x_subtype => 'SEND',
1336 x_format => 'WFSTD_MASTER_DETAIL',
1337 x_default => 'DETAIL',
1338 x_value_type => 'CONSTANT',
1339 x_level_error => v_api_error_code
1340 ) ;
1341 -- dbms_output.put_line('api error2: ' || to_char(v_api_error_code));
1342
1343 -- dbms_output.put_line('Stage D9');
1344 -- dbms_output.put_line('Attribute inserted-2-'||v_hdr_activity_name);
1345 END;
1346 END IF; /* for v_book_flag */
1347
1348 BEGIN
1349 v_error_level := 2065;
1350 -- dbms_output.put_line('Stage D10');
1351 v_check_duplicate_flag := 'N';
1352
1353 SELECT 'Y',instance_id
1354 INTO
1355 v_check_duplicate_flag,
1356 v_instance_id
1357 FROM wf_process_activities
1358 WHERE process_item_type = 'OEOH'
1359 AND process_name = v_hdr_process_name
1360 AND process_version = 1
1361 AND activity_item_type= 'OEOH'
1362 AND activity_name = v_hdr_activity_name;
1363
1364 -- dbms_output.put_line('Flag = Y');
1365 -- dbms_output.put_line('Stage D11');
1366 EXCEPTION
1367 WHEN TOO_MANY_ROWS THEN
1368 -- dbms_output.put_line('Stage D12');
1372 -- dbms_output.put_line('Stage D13');
1369 v_check_duplicate_flag := 'Y';
1370 WHEN NO_DATA_FOUND THEN
1371 v_check_duplicate_flag := 'N';
1373 END;
1374
1375 IF v_check_duplicate_flag = 'N' THEN
1376
1377 v_error_level := 2066;
1378 -- dbms_output.put_line('Stage D15 Flag = N');
1379 SELECT
1380 wf_process_activities_s.nextval
1381 INTO
1382 v_instance_id
1383 FROM dual;
1384
1385 -- dbms_output.put_line('seq okay');
1386
1387 wf_load.upload_process_activity
1388 (
1389 x_process_item_type => 'OEOH',
1390 x_process_name => v_hdr_process_name,
1391 x_process_version => 1,
1392 x_activity_item_type => 'OEOH',
1393 x_activity_name => v_hdr_activity_name,
1394 x_instance_id => v_instance_id,
1395 x_instance_label => v_hdr_activity_name,
1396 x_protect_level => 20,
1397 x_custom_level => 20,
1398 x_start_end => null,
1399 x_default_result => NULL,
1400 x_icon_geometry => '0,0',
1401 x_perform_role => NULL,
1402 x_perform_role_type => 'CONSTANT',
1403 x_user_comment => NULL,
1404 x_level_error => v_api_error_code
1405 );
1406 -- dbms_output.put_line('Stage D15 -- Process Activity inserted--'
1407 -- ||v_hdr_activity_name);
1408 ELSE
1409 -- dbms_output.put_line('Spared(duplicate)..'
1410 -- ||v_hdr_process_name ||'---'||v_hdr_activity_name);
1411 NULL;
1412 END IF;
1413
1414 -- dbms_output.put_line('Stage D17');
1415
1416 v_error_level := 2067;
1417 WF_LOAD.UPLOAD_ACTIVITY_ATTR_VALUE (
1418 x_process_activity_id => v_instance_id,
1419 x_name => 'WAITING_ACTIVITY',
1420 x_protect_level => 20,
1421 x_custom_level => 20,
1422 x_value => v_lin_activity_name,
1423 x_value_type => 'CONSTANT',
1424 x_effective_date => sysdate - 1,
1425 x_level_error => v_api_error_code
1426 );
1427 -- dbms_output.put_line('Activity Attr Value inserted-1-'
1428 -- ||to_char(v_instance_id));
1429 -- dbms_output.put_line('api error value1: ' || to_char(v_api_error_code));
1430
1431 v_error_level := 2068;
1432 WF_LOAD.UPLOAD_ACTIVITY_ATTR_VALUE (
1433 x_process_activity_id => v_instance_id,
1434 x_name => 'WAITING_FLOW',
1435 x_protect_level => 20,
1436 x_custom_level => 20,
1437 x_value => 'DETAIL',
1438 x_value_type => 'CONSTANT',
1439 x_effective_date => sysdate - 1,
1440 x_level_error => v_api_error_code
1441 );
1442
1443 -- dbms_output.put_line('api error value2: ' || to_char(v_api_error_code));
1444 -- dbms_output.put_line('Activity Attr Value inserted-2-'||to_char(v_instance_id));
1445 -- dbms_output.put_line('Action Pre Req Stage 1 ');
1446
1447 v_action_id := c2.hdr_action_id;
1448 v_result_table := 'SO_HEADERS';
1449
1450 FOR c4 IN c3 LOOP
1451 v_error_level := 2069;
1452 -- dbms_output.put_line('Stage D18');
1453
1454 IF c4.instance_id IS null THEN
1455 -- dbms_output.put_line('Stage D19');
1456 UPDATE oe_action_pre_reqs
1457 SET
1458 action_id = null,
1459 result_id = null,
1460 instance_label = 'Interdependecy-Hdr',
1461 instance_id = v_instance_id
1462 WHERE rowid = c4.rowid;
1463 ELSE
1464 -- dbms_output.put_line('Stage D20');
1465 UPDATE oe_action_pre_reqs
1466 SET
1467 instance_id2 = c4.instance_id,
1468 instance_label2 = c4.instance_label,
1469 instance_id= v_instance_id,
1473
1470 instance_label = 'Interdependecy-Hdr'
1471 WHERE rowid = c4.rowid;
1472 END IF;
1474 -- dbms_output.put_line('Action Pre Req Stage 2 ');
1475
1476 END LOOP;
1477
1478 -- dbms_output.put_line('Stage D21');
1479
1480 v_error_level := 2070;
1481 INSERT INTO oe_action_pre_reqs
1482 (
1483 cycle_action_id,
1484 action_id,
1485 result_id,
1486 group_number,
1487 cycle_id,
1488 type,
1489 line_type,
1490 instance_label,
1491 instance_id
1492 )
1493 VALUES
1494 (
1495 null,
1496 c2.hdr_action_id,
1497 c2.result_id,
1498 c2.group_number,
1499 c2.cycle_id,
1500 'OEOH',
1501 'REG',
1502 'Interdependecy-Hdr',
1503 v_instance_id
1504 );
1505
1506 -- dbms_output.put_line('Stage D22');
1507
1508 /* For incoporating a new activity at Line Flow */
1509
1510 IF v_book_flag = 'N' THEN
1511 BEGIN
1512 SELECT 0 INTO v_error_level
1513 FROM WF_ACTIVITIES
1514 WHERE NAME = v_lin_activity_name
1515 AND ITEM_TYPE = 'OEOL'
1516 AND VERSION = 1;
1517 EXCEPTION
1518 WHEN NO_DATA_FOUND THEN
1519 v_error_level := 2071;
1520 -- dbms_output.put_line('Stage D23');
1521 wf_load.upload_activity (
1522 x_item_type => 'OEOL',
1523 x_name => v_lin_activity_name,
1524 x_display_name => v_lin_activity_name,
1525 x_description => NULL,
1526 x_type => 'FUNCTION',
1527 x_rerun => 'RESET',
1528 x_protect_level => 20,
1529 x_custom_level => 20,
1530 x_effective_date => sysdate - 1,
1531 x_function => 'WF_STANDARD.WAITFORFLOW',
1532 x_function_type => null,
1533 x_result_type => '*',
1534 x_cost => 0,
1535 x_read_role => null,
1536 x_write_role => null,
1537 x_execute_role => null,
1538 x_icon_name => 'FUNCTION.ICO',
1539 x_message => null,
1540 x_error_process => 'RETRY_ONLY',
1541 x_expand_role => 'N',
1542 x_error_item_type => 'WFERROR',
1543 x_runnable_flag => 'N',
1544 x_version => v_version,
1545 x_level_error => v_api_error_code
1546 );
1547
1548 -- dbms_output.put_line('Activity Inserted --'||v_lin_activity_name);
1549
1550 v_error_level := 2072;
1551 WF_LOAD.UPLOAD_ACTIVITY_ATTRIBUTE (
1552 x_activity_item_type => 'OEOL',
1553 x_activity_name => v_lin_activity_name,
1554 x_activity_version => 1,
1555 x_name => 'CONTINUATION_ACTIVITY',
1556 x_display_name => v_lin_activity_name||'-CONTINUATION_ACTIVITY',
1557 x_description => v_lin_activity_name||'-CONTINUATION_ACTIVITY',
1558 x_sequence => 0,
1559 x_type => 'VARCHAR2',
1560 x_protect_level => 20,
1561 x_custom_level => 20,
1562 x_subtype => 'SEND',
1563 x_format => '',
1564 x_default => v_hdr_activity_name,
1565 x_value_type => 'CONSTANT',
1566 x_level_error => v_api_error_code
1567 ) ;
1568
1569 -- dbms_output.put_line('Activity Attr Inserted -1-'||v_lin_activity_name);
1570
1571 v_error_level := 2073;
1572 WF_LOAD.UPLOAD_ACTIVITY_ATTRIBUTE (
1573 x_activity_item_type => 'OEOL',
1574 x_activity_name => v_lin_activity_name,
1575 x_activity_version => 1,
1576 x_name => 'CONTINUATION_FLOW',
1577 x_display_name => v_lin_activity_name||'-CONTINUATION_FLOW',
1581 x_protect_level => 20,
1578 x_description => v_lin_activity_name||'-CONTINUATION_FLOW',
1579 x_sequence => 1,
1580 x_type => 'LOOKUP',
1582 x_custom_level => 20,
1583 x_subtype => 'SEND',
1584 x_format => 'WFSTD_MASTER_DETAIL',
1585 x_default => 'MASTER',
1586 x_value_type => 'CONSTANT',
1587 x_level_error => v_api_error_code) ;
1588
1589 -- dbms_output.put_line('Activity Attr Inserted -2-'||v_lin_activity_name);
1590 END; -- sam
1591 end IF;
1592
1593 BEGIN
1594 v_error_level := 2074;
1595 -- dbms_output.put_line('Check duplicate - 002');
1596 v_check_duplicate_flag := 'N';
1597
1598 SELECT 'Y',instance_id INTO v_check_duplicate_flag, v_instance_id
1599 FROM wf_process_activities
1600 WHERE process_item_type = 'OEOL'
1601 AND process_name = v_lin_process_name
1602 AND process_version = 1
1603 AND activity_item_type= 'OEOL'
1604 AND activity_name = v_lin_activity_name;
1605 EXCEPTION
1606 WHEN too_many_rows THEN
1607 v_check_duplicate_flag := 'Y';
1608 WHEN no_data_found THEN
1609 v_check_duplicate_flag := 'N';
1610 END;
1611
1612 IF v_check_duplicate_flag = 'N' THEN
1613 v_error_level := 2075;
1614 -- dbms_output.put_line('duplicate - 002');
1615
1616 SELECT wf_process_activities_s.nextval INTO v_instance_id FROM dual;
1617
1618 -- dbms_output.put_line('before insertion of activity:='
1619 -- ||v_lin_process_name||'=='||v_lin_activity_name);
1620
1621 wf_load.upload_process_activity
1622 (
1623 x_process_item_type => 'OEOL',
1624 x_process_name => v_lin_process_name,
1625 x_process_version => 1,
1626 x_activity_item_type => 'OEOL',
1627 x_activity_name => v_lin_activity_name,
1628 x_instance_id => v_instance_id,
1629 x_instance_label => v_lin_activity_name,
1630 x_protect_level => 20,
1631 x_custom_level => 20,
1632 x_start_end => null,
1633 x_default_result => NULL,
1634 x_icon_geometry => '0,0',
1635 x_perform_role => NULL,
1636 x_perform_role_type => 'CONSTANT',
1637 x_user_comment => NULL,
1638 x_level_error => v_api_error_code
1639 );
1640
1641 -- dbms_output.put_line('Process Activity Inserted -'||to_char(v_instance_id));
1642 ELSE
1643 -- dbms_output.put_line('Spared(duplicate)..'||v_lin_process_name
1644 -- ||'---'||v_lin_activity_name);
1645 NULL;
1646 END IF;
1647
1648 v_error_level := 2076;
1649 WF_LOAD.UPLOAD_ACTIVITY_ATTR_VALUE (
1650 x_process_activity_id => v_instance_id,
1651 x_name => 'CONTINUATION_ACTIVITY',
1652 x_protect_level => 20,
1653 x_custom_level => 20,
1654 x_value => v_hdr_activity_name,
1655 x_value_type => 'CONSTANT',
1656 x_effective_date => sysdate - 1,
1657 x_level_error => v_api_error_code
1658 );
1659
1660 -- dbms_output.put_line('Activity Attr value Inserted -1-'||v_lin_activity_name);
1661
1662 v_error_level := 2077;
1663 WF_LOAD.UPLOAD_ACTIVITY_ATTR_VALUE (
1664 x_process_activity_id => v_instance_id,
1665 x_name => 'CONTINUATION_FLOW',
1666 x_protect_level => 20,
1667 x_custom_level => 20,
1668 x_value => 'MASTER',
1669 x_value_type => 'CONSTANT',
1670 x_effective_date => sysdate - 1,
1671 x_level_error => v_api_error_code
1672 );
1673
1674 -- dbms_output.put_line('Activity Attr value Inserted -2-'||v_lin_activity_name);
1675
1676 -- dbms_output.put_line('Pre Req stage 5 ');
1677
1678 v_action_id := c2.lin_action_id;
1679 v_result_table := 'SO_LINES';
1680
1681 INSERT INTO oe_action_pre_reqs
1682 (
1686 line_type,
1683 group_number,
1684 cycle_id,
1685 type,
1687 action_id,
1688 instance_label,
1689 instance_id
1690 )
1691 VALUES
1692 (
1693 c2.group_number,
1694 c2.cycle_id,
1695 'OEOL',
1696 p_line_type,
1697 '-3',
1698 'Interdependecy-Line',
1699 v_instance_id
1700 );
1701
1702
1703 INSERT INTO oe_action_pre_reqs
1704 (
1705 cycle_action_id,
1706 action_id,
1707 result_id,
1708 group_number,
1709 cycle_id,
1710 type,
1711 line_type,
1712 instance_label,
1713 instance_id
1714 )
1715 VALUES
1716 (
1717 c2.cycle_action_id,
1718 '',
1719 '',
1720 c2.group_number,
1721 c2.cycle_id,
1722 'OEOL',
1723 p_line_type,
1724 'Interdependecy-Line',
1725 v_instance_id
1726 );
1727
1728 -- dbms_output.put_line('Stage D28');
1729
1730 else
1731 -- dbms_output.put_line('Handling Enter: ' || p_line_type);
1732 -- Create Enter activity in the line level
1733 -- The c2.cycle_action_ids are the Line actions, for which we need to create ENTER
1734 -- in the line level, and create transitions from ENTER to those line actions.
1735 v_result_id := c2.result_id;
1736 v_action_id := c2.lin_action_id;
1737 v_cycle_action_id := c2.cycle_action_id;
1738 IF v_result_id = 1 THEN
1739 begin
1740 select instance_id into v_instance_id
1741 from wf_process_activities
1742 where process_item_type = 'OEOL'
1743 and process_version =1
1744 and process_name = v_lin_process_name
1745 and instance_label = 'ENTER';
1746 exception
1747 when no_data_found then
1748 SELECT wf_process_activities_s.nextval INTO v_instance_id FROM dual;
1749 wf_load.upload_process_activity
1750 (
1751 x_process_item_type => 'OEOL',
1752 x_process_name => v_lin_process_name,
1753 x_process_version => 1,
1754 x_activity_item_type => 'OEOL',
1755 x_activity_name => 'ENTER',
1756 x_instance_id => v_instance_id,
1757 x_instance_label => 'ENTER',
1758 x_protect_level => 20,
1759 x_custom_level => 20,
1760 x_start_end => null,
1761 x_default_result => NULL,
1762 x_icon_geometry => '0,0',
1763 x_perform_role => NULL,
1764 x_perform_role_type => 'CONSTANT',
1765 x_user_comment => NULL,
1766 x_level_error => v_api_error_code
1767 );
1768 when others then
1769 null;
1770 end;
1771 /*
1772 FOR c6 in c5 LOOP
1773 v_error_level := 2078;
1774 -- dbms_output.put_line('Stage N24');
1775
1776 IF c6.instance_id IS null THEN
1777 -- dbms_output.put_line('Stage N25');
1778 UPDATE oe_action_pre_reqs
1779 SET
1780 cycle_action_id = null,
1781 instance_label = 'ENTER',
1782 instance_id = v_instance_id
1783 WHERE rowid = c6.rowid;
1784 ELSE
1785 -- dbms_output.put_line('Stage N26');
1786 UPDATE oe_action_pre_reqs
1787 SET
1788 cycle_action_id = null,
1789 instance_id2 = v_instance_id,
1790 instance_label2 = 'ENTER'
1791 WHERE rowid = c6.rowid;
1792 END IF;
1793
1794 -- dbms_output.put_line('Pre Req stage 6 (Enter)');
1795
1796 END LOOP;
1797
1798 -- dbms_output.put_line('Stage N21');
1799
1800 v_error_level := 3070;
1801
1802 INSERT INTO oe_action_pre_reqs
1803 (
1804 cycle_action_id,
1805 action_id,
1809 type,
1806 result_id,
1807 group_number,
1808 cycle_id,
1810 line_type,
1811 instance_label,
1812 instance_id
1813 )
1814 VALUES
1815 (
1816 c2.cycle_action_id,
1817 '',
1818 '',
1819 c2.group_number,
1820 c2.cycle_id,
1821 'OEOL',
1822 p_line_Type,
1823 'ENTER',
1824 v_instance_id
1825 );
1826 */
1827 --sam
1828 INSERT INTO oe_action_pre_reqs
1829 (
1830 cycle_action_id,
1831 action_id,
1832 result_id,
1833 group_number,
1834 cycle_id,
1835 type,
1836 line_type,
1837 instance_label,
1838 instance_id
1839 )
1840 VALUES
1841 (
1842 c2.cycle_action_id,
1843 '',
1844 '',
1845 c2.group_number,
1846 c2.cycle_id,
1847 'OEOL',
1848 p_line_Type,
1849 'ENTER',
1850 v_instance_id
1851 );
1852
1853
1854 END IF;
1855 end if;
1856 END LOOP; /* main c2 loop*/
1857 V_ERROR_FLAG := 'N';
1858 EXCEPTION
1859 WHEN OTHERS THEN
1860
1861 v_error_flag := 'Y';
1862 v_error_code := sqlcode;
1863 v_error_message := 'Error occured in cycle: ' ||to_char(p_cycle_id)
1864 ||' during creation of Header Line Dependency...'
1865 ||' Oracle error:'||to_char(v_error_code);
1866
1867 END Create_Header_Line_Dependency;
1868
1869
1870 /* To incorporate ORs in the pre-requisites */
1871
1872 PROCEDURE Create_Activity_Or
1873 (
1874 p_item_type IN VARCHAR2,
1875 p_line_type IN VARCHAR2,
1876 p_cycle_id IN NUMBER
1877 )
1878
1879 IS
1880 CURSOR c1 IS
1881 SELECT cycle_action_id, action_id, result_id, group_number, rowid
1882 FROM oe_action_pre_reqs
1883 WHERE cycle_action_id IS NOT null
1884 AND type = p_item_type
1885 AND line_type = p_line_type
1886 AND cycle_action_id in
1887 (SELECT cycle_action_id
1888 FROM oe_action_pre_reqs
1889 WHERE cycle_action_id is not null
1890 AND cycle_id = p_cycle_id
1891 GROUP BY cycle_action_id
1892 HAVING count(*) > 1)
1893 ORDER BY cycle_action_id
1894 FOR UPDATE;
1895
1896 v_cycle_action_id NUMBER;
1897 v_api_error_level NUMBER;
1898 v_process_name VARCHAR2(30);
1899 v_last_instance_id NUMBER;
1900 v_instance_label VARCHAR2(30);
1901 v_cycle_id NUMBER;
1902 v_count NUMBER;
1903 v_activity VARCHAR2(30);
1904
1905 BEGIN
1906 wf_core.session_level := 20;
1907 v_cycle_action_id := 0;
1908
1909 FOR c2 IN c1 LOOP
1910 v_error_level := 2081;
1911 -- dbms_output.put_line('Stage 1');
1912 BEGIN
1913 SELECT count(distinct group_number)
1914 INTO v_count
1915 FROM oe_action_pre_reqs
1916 WHERE cycle_action_id = c2.cycle_action_id;
1917 EXCEPTION
1918 WHEN OTHERS THEN
1919 null;
1920 END;
1921
1922 IF c2.cycle_action_id <> v_cycle_action_id THEN /* cycle_action_id break */
1923
1924 -- dbms_output.put_line('Stage 1');
1925 v_cycle_action_id := c2.cycle_action_id;
1926
1927 SELECT
1928 'UPG_PN_'||p_item_type||'_'||p_line_type||'_'||to_char(cycle_id) ,
1929 decode(v_count, 1, 'AND_'||to_char(c2.cycle_action_id), 'OR_'||to_char(c2.cycle_action_id)),
1930 cycle_id
1931 INTO
1932 v_process_name,
1933 v_instance_label,
1934 v_cycle_id
1935 FROM so_cycle_actions
1936 WHERE cycle_action_id = c2.cycle_action_id;
1937
1938 SELECT wf_process_activities_s.nextval
1939 INTO v_last_instance_id
1940 FROM dual;
1941
1942 v_api_error_level := 0;
1943
1944 -- dbms_output.put_line('Stage 2');
1945 IF v_count > 1 THEN
1946 v_activity := 'OR';
1947 ELSE
1948 v_activity := 'AND';
1949 END IF;
1950
1951 v_error_level := 2082;
1952 WF_LOAD.UPLOAD_PROCESS_ACTIVITY (
1953 x_process_item_type => p_item_type,
1954 x_process_name => v_process_name,
1955 x_process_version => 1,
1956 x_activity_item_type => 'WFSTD',
1960 x_protect_level => 20,
1957 x_activity_name => v_activity,
1958 x_instance_id => v_last_instance_id,
1959 x_instance_label => v_instance_label,
1961 x_custom_level => 20,
1962 x_start_end => null,
1963 x_default_result => null,
1964 x_icon_geometry => '0,0',
1965 x_perform_role => null,
1966 x_perform_role_type => 'CONSTANT',
1967 x_user_comment => null,
1968 x_level_error => v_api_error_level
1969 );
1970
1971 -- dbms_output.put_line('Stage 3 error: '||to_char(v_api_error_level));
1972
1973 v_error_level := 2083;
1974 INSERT INTO oe_action_pre_reqs
1975 (
1976 cycle_action_id,
1977 action_id,
1978 result_id,
1979 group_number,
1980 cycle_id,
1981 type,
1982 line_type,
1983 instance_label,
1984 instance_id,
1985 instance_label2,
1986 instance_id2
1987 )
1988 VALUES
1989 (
1990 c2.cycle_action_id,
1991 null,
1992 null,
1993 c2.group_number,
1994 v_cycle_id,
1995 p_item_type,
1996 p_line_type,
1997 v_instance_label,
1998 v_last_instance_id,
1999 null,
2000 null
2001 );
2002
2003 END IF;
2004
2005 UPDATE oe_action_pre_reqs
2006 SET cycle_action_id = null,
2007 instance_label2 = v_instance_label,
2008 instance_id2 = v_last_instance_id
2009 WHERE rowid = c2.rowid;
2010 -- dbms_output.put_line('Stage 4');
2011 END LOOP;
2012 -- dbms_output.put_line('Stage End');
2013
2014 V_ERROR_FLAG := 'N';
2015 EXCEPTION
2016 WHEN OTHERS THEN
2017
2018 v_error_flag := 'Y';
2019 v_error_code := sqlcode;
2020 v_error_message := 'Error occured in cycle: ' ||to_char(p_cycle_id)
2021 ||', Type:'||p_item_type
2022 ||' during creation of Activity OR...'
2023 ||' Oracle error:'||to_char(v_error_code);
2024
2025 END Create_Activity_Or;
2026
2027
2028 /*=============================================================
2029 -- Based on table oe_action_pre_reqs, link all the activities
2030 -- into wf_activity_transition table
2031 ==============================================================*/
2032
2033 PROCEDURE Create_Activity_Transition
2034 (
2035 p_item_type IN VARCHAR2,
2036 p_cycle_id IN NUMBER,
2037 p_line_type IN VARCHAR2
2038 )
2039
2040 IS
2041 CURSOR c1 IS
2042 SELECT
2043 cycle_action_id,
2044 action_id,
2045 decode(result_id,null,'*','UPG_RC_'||to_char(result_id)) result_code,
2046 cycle_id,
2047 instance_id,
2048 instance_id2
2049 FROM oe_action_pre_reqs
2050 WHERE type = p_item_type
2051 AND cycle_id = p_cycle_id
2052 AND line_type = p_line_type;
2053
2054 v_from_instance_id NUMBER;
2055 v_to_instance_id NUMBER;
2056 v_result_code VARCHAR2(30);
2057 v_pre_result VARCHAR2(30);
2058 v_pre_activity VARCHAR2(30);
2059 v_post_activity VARCHAR2(30);
2060 v_max_seq NUMBER := 0;
2061 v_level_error NUMBER := 0;
2062
2063 BEGIN
2064 -- dbms_output.enable('999999999999');
2065 FOR c2 in c1 LOOP
2066 -- dbms_output.put_line('Just entered INTO the C2 loop');
2067 -- dbms_output.put_line('c2.cycle_action_id : '||to_char(c2.cycle_action_id));
2068 DECLARE
2069 CURSOR c3 IS
2070 SELECT
2071 a.action_id action_id_c3,
2072 nvl(m.activity_name,'UPG_AN_'
2073 ||to_char(a.action_id)) act_name_t,
2074 'UPG_PN_'||p_item_type||'_'
2075 ||p_line_type||'_'||to_char(p_cycle_id) proc_name,
2076 nvl(m.activity_name,
2077 'UPG_ILN_'||to_char(ca.cycle_action_id)) instance_label,
2078 nvl(m.activity_seq, 0) act_seq
2079 FROM so_actions a,
2080 so_cycle_actions ca,
2081 oe_upgrade_wf_act_map m
2082 WHERE ca.cycle_id = p_cycle_id
2083 AND ca.action_id = a.action_id
2084 AND ca.cycle_action_id = c2.cycle_action_id
2085 AND a.action_id = m.action_id(+)
2086 ORDER BY act_seq;
2087
2088 CURSOR c5 IS
2089 SELECT a.action_id action_id_c5,
2090 nvl(m.activity_name,'UPG_AN_'
2094 'UPG_ILN_'||to_char(ca.cycle_action_id)) instance_label,
2091 ||to_char(a.action_id)) act_name_f,
2092 nvl(m.activity_seq, 0) act_seq,
2093 nvl(m.activity_name,
2095 nvl(m.activity_result,c2.result_code) act_result
2096 FROM so_actions a,
2097 so_cycle_actions ca,
2098 oe_upgrade_wf_act_map m
2099 WHERE a.action_id = c2.action_id
2100 AND a.action_id = ca.action_id
2101 AND ca.cycle_id = p_cycle_id
2102 AND a.action_id = m.action_id(+)
2103 ORDER BY act_seq desc;
2104
2105 BEGIN
2106 v_error_level := 2091;
2107 -- dbms_output.put_line('======================='||to_char(v_error_level));
2108 IF (c2.cycle_action_id is null) AND (c2.action_id is null) THEN
2109
2110 v_from_instance_id := c2.instance_id;
2111 v_to_instance_id := c2.instance_id2;
2112 v_result_code := '*';
2113
2114 v_error_level := 2092;
2115 -- dbms_output.put_line('======================='||to_char(v_error_level));
2116 /* Insert INTO table wf_activity_transitions */
2117 oe_upgrade_wf2.insert_into_wf_table
2118 ( v_from_instance_id,
2119 v_to_instance_id,
2120 v_result_code,
2121 v_level_error
2122 );
2123 END IF;
2124
2125 v_error_level := 2093;
2126
2127 -- dbms_output.put_line('======================='||to_char(v_error_level));
2128 IF (c2.cycle_action_id is not null) AND (c2.action_id is null) THEN
2129 FOR c4 in c3 LOOP
2130 v_error_level := 2093;
2131 -- dbms_output.put_line('======================='||to_char(v_error_level));
2132 -- dbms_output.put_line('#######################'||c4.act_name_t);
2133 v_to_instance_id := oe_upgrade_wf2.get_instance_id
2134 ('UPG_PN_'||p_item_type||'_'||p_line_type
2135 ||'_'||to_char(c2.cycle_id),
2136 c4.act_name_t,
2137 c4.instance_label);
2138
2139 /* this activity is the first one in 'to' side on mapping table */
2140 IF (c4.act_seq = 0 OR c4.act_seq =1) THEN
2141 v_from_instance_id := c2.instance_id;
2142 v_result_code := '*';
2143 v_error_level := 2094;
2144 -- dbms_output.put_line('======================='||to_char(v_error_level));
2145 ELSE
2146 v_error_level := 2095;
2147 -- dbms_output.put_line('======================='||to_char(v_error_level));
2148 -- dbms_output.put_line('#######################'||c4.act_seq);
2149 oe_upgrade_wf2.get_pre_activity(c4.action_id_c3,c4.act_seq,
2150 v_pre_activity,v_pre_result);
2151
2152 v_error_level := 2096;
2153 -- dbms_output.put_line('======================='||to_char(v_error_level));
2154 -- dbms_output.put_line('#######################'||v_pre_activity);
2155 v_from_instance_id := oe_upgrade_wf2.get_instance_id
2156 ('UPG_PN_'||p_item_type||'_'||p_line_type||'_'||to_char(c2.cycle_id),
2157 v_pre_activity,
2158 v_pre_activity);
2159 v_result_code := v_pre_result;
2160 END IF; /* get 'from_instance_id' */
2161
2162
2163 /* Insert into table wf_activity_transitions */
2164 v_error_level := 2097;
2165 -- dbms_output.put_line('======================='||to_char(v_error_level));
2166 -- dbms_output.put_line('************* 1 **************');
2167 oe_upgrade_wf2.insert_into_wf_table
2168 (
2169 v_from_instance_id,
2170 v_to_instance_id,
2171 v_result_code,
2172 v_level_error
2173 );
2174 END LOOP;
2175 END IF; /* cycle_action_id is null and action_id is not null */
2176
2177 v_error_level := 2098;
2178 -- dbms_output.put_line('======================='||to_char(v_error_level));
2179 IF (c2.cycle_action_id is null) AND (c2.action_id is not null) THEN
2180 v_error_level := 2099;
2181 --dbms_output.put_line('======================='||to_char(v_error_level));
2182 FOR c6 IN c5 LOOP
2183 v_error_level := 2100;
2184 -- dbms_output.put_line('======================='||to_char(v_error_level));
2185 IF c6.act_seq <> 0 THEN
2186 SELECT max(activity_seq)
2187 INTO v_max_seq
2188 FROM oe_upgrade_wf_act_map
2189 WHERE action_id = c6.action_id_c5;
2190 END IF;
2191
2192 v_error_level := 2101;
2193 -- dbms_output.put_line('======================='||to_char(v_error_level));
2197 ||to_char(c2.cycle_id),
2194 -- dbms_output.put_line('*************** = '||c2.cycle_id||'__'||c6.act_name_f || 'label:'||c6.instance_label || ' ' || p_item_type || p_line_type || to_char(c2.cycle_id));
2195 v_from_instance_id := oe_upgrade_wf2.get_instance_id
2196 ( 'UPG_PN_'||p_item_type||'_'||p_line_type||'_'
2198 c6.act_name_f,
2199 c6.instance_label
2200 );
2201
2202 /* this activity is the last one in 'from' side on mapping table */
2203 IF (c6.act_seq = 0 or c6.act_seq = v_max_seq) THEN
2204 v_error_level := 2102;
2205 --dbms_output.put_line('======================='||to_char(v_error_level));
2206 v_to_instance_id := nvl(c2.instance_id, c2.instance_id2);
2207 ELSE
2208 v_error_level := 2103;
2209 -- dbms_output.put_line('======================='||to_char(v_error_level));
2210 -- dbms_output.put_line('*************** = '||c6.act_seq);
2211 v_post_activity := oe_upgrade_wf2.get_post_activity
2212 (
2213 c6.action_id_c5,
2214 c6.act_seq
2215 );
2216
2217 v_error_level := 2104;
2218 -- dbms_output.put_line('======================='||to_char(v_error_level));
2219 -- dbms_output.put_line('*************** = '||v_post_activity);
2220 v_to_instance_id := oe_upgrade_wf2.get_instance_id
2221 (
2222 'UPG_PN_'||p_item_type||'_'
2223 ||p_line_type||'_'
2224 ||to_char(c2.cycle_id),
2225 v_post_activity,
2226 v_post_activity
2227 );
2228 END IF;
2229
2230 /* Insert into table wf_activity_transitions */
2231 -- dbms_output.put_line('************* 2 **************');
2232
2233 v_error_level := 2105;
2234 -- dbms_output.put_line('======================='||to_char(v_error_level));
2235 v_result_code := c6.act_result;
2236 IF (c6.act_result = c2.result_code) AND
2237 (c6.act_result <> '*') THEN
2238 BEGIN
2239 SELECT 'x' INTO v_dummy
2240 FROM wf_lookups
2241 WHERE lookup_type = 'UPG_RT_'||to_char(c6.action_id_c5)
2242 AND lookup_code = c6.act_result;
2243 EXCEPTION
2244 WHEN NO_DATA_FOUND THEN
2245 v_result_code := '*';
2246 END;
2247 END IF;
2248
2249 oe_upgrade_wf2.insert_into_wf_table
2250 (
2251 v_from_instance_id,
2252 v_to_instance_id,
2253 v_result_code,
2254 v_level_error
2255 );
2256 END LOOP; /* for c6 in c5 ... */
2257 END IF; /* cycle_action_id is null and action_id is not null */
2258
2259 IF (c2.cycle_action_id is not null) AND (c2.action_id is not null) THEN
2260 v_error_level := 2106;
2261 -- dbms_output.put_line('======================='||to_char(v_error_level));
2262 FOR c4 IN c3 LOOP
2263 v_error_level := 2107;
2264 -- dbms_output.put_line('======================='||to_char(v_error_level));
2265 -- dbms_output.put_line('************* ????? **************'||c4.act_seq);
2266 /* it's the first activity on 'to' side OR it is the custom activity */
2267 IF (c4.act_seq = 0 OR c4.act_seq =1) THEN
2268 v_error_level := 2108;
2269 -- dbms_output.put_line('======================='||to_char(v_error_level));
2270 FOR c6 IN c5 LOOP
2271 IF c6.act_seq <> 0 THEN
2272 SELECT max(activity_seq)
2273 INTO v_max_seq
2274 FROM oe_upgrade_wf_act_map
2275 WHERE action_id = c6.action_id_c5;
2276 END IF;
2277
2278 v_error_level := 2109;
2279 -- dbms_output.put_line('======================='||to_char(v_error_level));
2280 -- dbms_output.put_line('======================='||c6.act_name_f);
2281 v_from_instance_id := oe_upgrade_wf2.get_instance_id
2282 (
2283 c4.proc_name,
2284 c6.act_name_f,
2288 -- dbms_output.put_line('************* ????? *****'||v_from_instance_id
2285 c6.instance_label
2286 );
2287
2289 -- ||' '||c6.act_seq);
2290
2291 v_error_level := 2110;
2292 -- dbms_output.put_line('======================='||to_char(v_error_level));
2293 /* it's the last activity on 'from' side */
2294 IF (c6.act_seq = 0 OR c6.act_seq = v_max_seq) THEN
2295 v_error_level := 2111;
2296 -- dbms_output.put_line('======================='||to_char(v_error_level));
2297 -- dbms_output.put_line('======================='||c4.act_name_t);
2298 v_to_instance_id := oe_upgrade_wf2.get_instance_id
2299 (
2300 c4.proc_name,
2301 c4.act_name_t,
2302 c4.instance_label
2303 );
2304 -- dbms_output.put_line('********** last one*'||v_to_instance_id);
2305 ELSE
2306 --dbms_output.put_line('c6.act_seq:' || to_char(c6.act_seq) || to_char(v_max_seq));
2307 --dbms_output.put_line('xxxx ' || c6.action_id_c5 || 'and c4 ' || c4.act_name_t);
2308 v_error_level := 2111;
2309 --dbms_output.put_line('======================='||to_char(v_error_level));
2310 v_post_activity := oe_upgrade_wf2.get_post_activity
2311 (
2312 c6.action_id_c5,
2313 c6.act_seq
2314 );
2315
2316 v_error_level := 2112;
2317 --dbms_output.put_line('======================='||to_char(v_error_level));
2318 v_to_instance_id := oe_upgrade_wf2.get_instance_id
2319 (
2320 c4.proc_name,
2321 v_post_activity,
2322 v_post_activity
2323 );
2324
2325 END IF;
2326
2327 /* Insert into table wf_activity_transitions */
2328 -- dbms_output.put_line('************* 9 **************'|| to_char(c2.action_id));
2329
2330 v_error_level := 2113;
2331 v_result_code := c6.act_result;
2332 --dbms_output.put_line('sam: c6 result: ' || c6.act_result || ' c2 result: ' || c2.result_code);
2333
2334 IF (c6.act_result = c2.result_code) AND (c6.act_result <> '*') THEN
2335 BEGIN
2336 SELECT 'x' INTO v_dummy
2337 FROM wf_lookups
2338 WHERE lookup_type = 'UPG_RT_'||to_char(c6.action_id_c5)
2339 AND lookup_code = c6.act_result;
2340 EXCEPTION
2341 WHEN NO_DATA_FOUND THEN
2342 v_result_code := '*';
2343 END;
2344 END IF;
2345 -- dbms_output.put_line('from: ' || v_from_instance_id || 'to: ' || v_to_instance_id || ' result: ' || v_result_code);
2346 oe_upgrade_wf2.insert_into_wf_table
2347 (
2348 v_from_instance_id,
2349 v_to_instance_id,
2350 v_result_code,
2351 v_level_error
2352 );
2353 --dbms_output.put_line('sam: inserted ' || to_char(v_from_instance_id) || ' to ' || v_to_instance_id || ' result ' || v_result_code);
2354 --dbms_output.put_line('------- level_error'||v_level_error);
2355 END LOOP; /* for c6 in c5 ... */
2356 IF (c2.action_id = -1) THEN
2357 v_from_instance_id := oe_upgrade_wf2.get_instance_id
2358 ( 'UPG_PN_'||p_item_type||'_'
2359 || p_line_type||'_'
2360 || to_char(c2.cycle_id),
2361 'START',
2362 'START'
2363 );
2364 v_to_instance_id := oe_upgrade_wf2.get_instance_id
2365 (
2369 );
2366 c4.proc_name,
2367 c4.act_name_t,
2368 c4.instance_label
2370 /* Insert into table wf_activity_transitions */
2371 -- dbms_output.put_line('************* 5 **************');
2372 v_level_error := 21133;
2373 oe_upgrade_wf2.insert_into_wf_table
2374 (
2375 v_from_instance_id,
2376 v_to_instance_id,
2377 '*',
2378 v_level_error
2379 );
2380 END IF;
2381 ELSE
2382 v_error_level := 2114;
2383 /* it's not first activity on 'to' side */
2384 oe_upgrade_wf2.get_pre_activity
2385 (
2386 c4.action_id_c3,
2387 c4.act_seq,
2388 v_pre_activity,
2389 v_pre_result
2390 );
2391
2392 v_error_level := 2115;
2393 v_from_instance_id := oe_upgrade_wf2.get_instance_id
2394 (
2395 'UPG_PN_'||p_item_type||'_'
2396 ||p_line_type||'_'
2397 ||to_char(c2.cycle_id),
2398 v_pre_activity,
2399 v_pre_activity
2400 );
2401
2402 v_error_level := 2116;
2403 v_result_code := v_pre_result;
2404 v_to_instance_id := oe_upgrade_wf2.get_instance_id
2405 (
2406 c4.proc_name,
2407 c4.act_name_t,
2408 c4.instance_label
2409 );
2410
2411 v_error_level := 2117;
2412
2413 /* Insert into table wf_activity_transitions */
2414 -- dbms_output.put_line('************* 5 **************');
2415 oe_upgrade_wf2.insert_into_wf_table
2416 (
2417 v_from_instance_id,
2418 v_to_instance_id,
2419 v_result_code,
2420 v_level_error
2421 );
2422
2423
2424 END IF;
2425 END LOOP; /* for c4 in c3 ... */
2426 END IF;
2427 END;
2428 END LOOP; /* for c2 in c1 .... */
2429
2430 V_ERROR_FLAG := 'N';
2431 --dbms_output.put_line('sam: leaving transitions');
2432 EXCEPTION
2433 WHEN OTHERS THEN
2434 v_error_flag := 'Y';
2435 v_error_code := sqlcode;
2436 v_error_message := 'Error occured in cycle: ' ||to_char(p_cycle_id)
2437 ||', Type:'||p_item_type
2438 ||' during creation of Activity Transition...'
2439 ||' Oracle error:'||to_char(v_error_code);
2440 END Create_Activity_Transition;
2441
2442
2443 /* ***********************************************************************
2444 -- If the SHIP_CONFIRM
2445 -- If the SHIP CONFIRM(action_id = 3) exists in SO cycles,
2446 -- The Receivables Interface(action_id = 7) will transform to
2447 -- WF ==> UPG_FULFILLMENT_SUB and UPG_LINE_INVOICE_INTERFACE_SUB
2448 -- Otherwise the Receivables Interface will only become
2449 -- WF ==> UPG_LINE_INVOICE_INTERFACE_SUB
2450 *************************************************************************/
2451
2452 PROCEDURE Ship_Confirm_Adjusting
2453 (
2454 p_cycle_id IN NUMBER,
2455 p_line_type IN VARCHAR2
2456 )
2457 IS
2458
2459 v_instance_id number := 0;
2460 v_from_instance_id number;
2461 v_result_code VARCHAR2(30);
2462 v_to_instance_id number;
2463 v_api_error_code number := 0;
2464 v_level_error number := 0;
2465
2466 cursor c1 is
2467 select cycle_id from so_cycles
2468 where cycle_id in (select cycle_id from so_cycle_actions
2469 where action_id = 7)
2470 and cycle_id not in (select cycle_id from so_cycle_actions
2471 where action_id in (3, 13))
2472 and cycle_id = p_cycle_id;
2473
2474 cursor c3 is
2475 select from_process_activity, result_code
2476 from wf_activity_transitions
2477 where to_process_activity = v_instance_id;
2478
2479 BEGIN
2480 wf_core.session_level := 20;
2484 select instance_id into v_instance_id
2481 for c2 in c1 loop
2482
2483 -- find out the instance_id for activity "UPG_FULFILLMENT_SUB"
2485 from wf_process_activities
2486 where process_name =
2487 'UPG_PN_OEOL_'||p_line_type||'_'||to_char(p_cycle_id)
2488 and activity_name = 'UPG_FULFILLMENT_SUB'
2489 and rownum = 1
2490 order by process_version desc;
2491
2492
2493 -- find out the post activity instance_id
2494 -- we know there is only 1 transition coming out of fulfillment
2495 -- fulfillment is a seeded upgrade activity
2496 select to_process_activity into v_to_instance_id
2497 from wf_activity_transitions
2498 where from_process_activity = v_instance_id
2499 and rownum = 1;
2500
2501
2502 -- For the previous activity(s) , add the new transition
2503 For c4 in c3 loop
2504
2505 oe_upgrade_wf2.insert_into_wf_table
2506 (
2507 c4.from_process_activity,
2508 v_to_instance_id,
2509 c4.result_code,
2510 v_level_error
2511 );
2512 End loop;
2513
2514 -- delete from wf_process_activities
2515 -- the api will also delete from wf_activity_transition
2516 WF_LOAD.Delete_Process_Activity
2517 (
2518 p_step => v_instance_id
2519 );
2520
2521 end loop;
2522 V_ERROR_FLAG := 'N';
2523 EXCEPTION
2524 WHEN NO_DATA_FOUND THEN
2525 --dbms_output.put_line('no data found at ship confirm adjust');
2526 null;
2527 WHEN OTHERS THEN
2528 --dbms_output.put_line('others exception ship confirm');
2529 v_error_flag := 'Y';
2530 v_error_code := sqlcode;
2531 v_error_message := 'Error occured in cycle: ' ||to_char(p_cycle_id)
2532 ||' during SHIP CONFIRM adjusting ....'
2533 ||' Oracle error:'||to_char(v_error_code);
2534 END Ship_Confirm_Adjusting;
2535
2536
2537 PROCEDURE ATO_Adjusting
2538 (
2539 p_cycle_id IN NUMBER
2540 )
2541 IS
2542
2543 v_instance_id number := 0;
2544 v_from_instance_id number;
2545 v_api_error_code number := 0;
2546 v_level_error number := 0;
2547 v_new_instance_id number;
2548 v_mfg_shipping_instance_id number;
2549 v_cfg_shipping_instance_id number;
2550 v_mfg_instance_id number;
2551
2552 cursor c1 is
2553 select cycle_id from so_cycles
2554 where cycle_id in (select cycle_id from so_cycle_actions
2555 where action_id = 15);
2556 cursor c3 is
2557 select to_process_activity
2558 from wf_activity_transitions
2559 where from_process_activity = v_instance_id;
2560
2561 cursor c5 is
2562 select from_process_activity, result_code
2563 from wf_activity_transitions
2564 where to_process_activity = v_instance_id;
2565
2566
2567 BEGIN
2568 wf_core.session_level := 20;
2569 for c2 in c1 loop
2570
2571 -- find out the instance_id for activity "UPG_MODEL_MFG_RELEASE"
2572 select instance_id into v_mfg_instance_id
2573 from wf_process_activities
2574 where process_name = 'UPG_PN_OEOL_REG_'||to_char(p_cycle_id)
2575 and activity_name = 'UPG_MODEL_MFG_RELEASE'
2576 and rownum = 1
2577 order by process_version desc;
2578
2579 -- find out the instance_id for activity "UPG_MODEL_MFG_RELEASE"
2580 select instance_id into v_instance_id
2581 from wf_process_activities
2582 where process_name = 'UPG_PN_OEOL_CFG_'||to_char(p_cycle_id)
2583 and activity_name = 'UPG_MODEL_MFG_RELEASE'
2584 and rownum = 1
2585 order by process_version desc;
2586
2587 -- find out the previous activity instance_id
2588
2589
2590 -- find instance_id for shipping activity
2591 SELECT instance_id into v_cfg_shipping_instance_id
2592 from wf_process_activities
2593 where process_name = 'UPG_PN_OEOL_CFG_'||to_char(p_cycle_id)
2594 and activity_name = 'UPG_SHIPPING_SUB'
2595 and rownum = 1
2596 order by process_version desc;
2597
2598 -- find instance_id for shipping activity
2599 SELECT instance_id into v_mfg_shipping_instance_id
2600 from wf_process_activities
2601 where process_name = 'UPG_PN_OEOL_REG_'||to_char(p_cycle_id)
2602 and activity_name = 'UPG_SHIPPING_SUB'
2603 and rownum = 1
2604 order by process_version desc;
2605
2606 -- upload the configuration_line subprocess
2607
2608 select wf_process_activities_s.nextval
2609 into v_new_instance_id
2610 from dual;
2611
2612 WF_LOAD.UPLOAD_PROCESS_ACTIVITY ( x_process_item_type => 'OEOL',
2613 x_process_name => 'UPG_PN_OEOL_CFG_'||to_char(p_cycle_id),
2614 x_process_version => 1,
2615 x_activity_item_type => 'OEOL',
2616 x_activity_name => 'UPG_CONFIGURATION_LINE',
2620 x_custom_level => 20,
2617 x_instance_id => v_new_instance_id,
2618 x_instance_label => 'UPG_CONFIGURATION_LINE',
2619 x_protect_level => 20,
2621 x_start_end => null,
2622 x_default_result => null,
2623 x_icon_geometry => '0,0',
2624 x_perform_role => null,
2625 x_perform_role_type => 'CONSTANT',
2626 x_user_comment => null,
2627 x_level_error => v_api_error_code
2628 );
2629
2630 for c6 in c5 loop
2631 -- insert new transition to wf_activity_transition table
2632 oe_upgrade_wf2.insert_into_wf_table
2633 (
2634 c6.from_process_activity,
2635 v_new_instance_id,
2636 c6.result_code,
2637 v_level_error
2638 );
2639 end loop;
2640
2641 -- insert transition from shipping to mfg release
2642 oe_upgrade_wf2.insert_into_wf_table
2643 (
2644 v_mfg_shipping_instance_id,
2645 v_mfg_instance_id,
2646 'UNRESERVE',
2647 v_level_error
2648 );
2649
2650 -- insert transition from shipping to config line activity
2651 oe_upgrade_wf2.insert_into_wf_table
2652 (
2653 v_cfg_shipping_instance_id,
2654 v_new_instance_id,
2655 'UNRESERVE',
2656 v_level_error
2657 );
2658
2659
2660 -- it may go out to multiple destinations while it should only come from 1
2661 -- place, as there are AND's and OR's for the inbound transition
2662 for c4 in c3 loop
2663 oe_upgrade_wf2.insert_into_wf_table
2664 (
2665 v_new_instance_id,
2666 c4.to_process_activity,
2667 '*',
2668 v_level_error
2669 );
2670 end loop;
2671
2672 -- delete from wf_process_activities
2673 -- the api will also delete from wf_activity_transition
2674 WF_LOAD.Delete_Process_Activity
2675 (
2676 p_step => v_instance_id
2677 );
2678
2679 end loop;
2680
2681 V_ERROR_FLAG := 'N';
2682 EXCEPTION
2683 WHEN NO_DATA_FOUND THEN
2684 null;
2685 WHEN OTHERS THEN
2686 v_error_flag := 'Y';
2687 v_error_code := sqlcode;
2688 v_error_message := 'Error occured in cycle: ' ||to_char(p_cycle_id)
2689 ||' during SHIP CONFIRM adjusting ....'
2690 ||' Oracle error:'||to_char(v_error_code);
2691 END ATO_Adjusting;
2692
2693
2694 /* Generic Flow Adjusting */
2695
2696 PROCEDURE Generic_Flow_Adjusting
2697 (
2698 p_item_type IN VARCHAR2,
2699 p_cycle_id IN NUMBER,
2700 p_line_type IN VARCHAR2
2701 )
2702 IS
2703 v_shipping_instance_id NUMBER;
2704 v_rma_instance_id NUMBER;
2705 v_and_instance_id1 NUMBER;
2706 v_and_instance_id2 NUMBER;
2707 v_api_error_level NUMBER:=0;
2708 v_get_category_instance_id NUMBER;
2709 v_start_instance_id NUMBER;
2710 v_from_instance_id NUMBER;
2711 v_level_error NUMBER:=0;
2712
2713
2714
2715 Cursor c1 is
2716 select cycle_id from so_cycles
2717 where cycle_id in (select cycle_id from so_cycle_actions
2718 where action_id in (2,3,4,11,16))
2719 and cycle_id in (select cycle_id from so_cycle_actions
2720 where action_id = 13)
2721 and cycle_id = p_cycle_id;
2722
2723 cursor c3 is
2724 -- find out the previous activity instance_id
2725 select from_process_activity, result_code
2726 from wf_activity_transitions
2727 where to_process_activity = v_shipping_instance_id;
2728
2729 -- find out the previous activity instance_id
2730 cursor c5 is
2731 select from_process_activity, result_code
2732 from wf_activity_transitions
2733 where to_process_activity = v_rma_instance_id;
2734
2735 BEGIN
2736 wf_core.session_level := 20;
2737 for c2 in c1 loop
2738
2739 -- find out the instance_id for activity "UPG_SHIPPING_SUB"
2740 select instance_id into v_shipping_instance_id
2741 from wf_process_activities
2742 where process_name =
2743 'UPG_PN_OEOL_'||p_line_type||'_'||to_char(p_cycle_id)
2744 and activity_name = 'UPG_SHIPPING_SUB'
2745 and rownum = 1
2746 order by process_version desc;
2747
2748
2749
2750 SELECT wf_process_activities_s.nextval
2751 INTO v_and_instance_id1
2752 FROM dual;
2753
2754 WF_LOAD.UPLOAD_PROCESS_ACTIVITY (
2755 x_process_item_type => 'OEOL',
2756 x_process_name => 'UPG_PN_'||p_item_type||'_'||p_line_type ||'_'||to_char(p_cycle_id),
2757 x_process_version => 1,
2758 x_activity_item_type => 'WFSTD',
2759 x_activity_name => 'AND',
2763 x_custom_level => 20,
2760 x_instance_id => v_and_instance_id1,
2761 x_instance_label => 'AND-1',
2762 x_protect_level => 20,
2764 x_start_end => '',
2765 x_default_result => null,
2766 x_icon_geometry => '0,0',
2767 x_perform_role => null,
2768 x_perform_role_type => 'CONSTANT',
2769 x_user_comment => null,
2770 x_level_error => v_api_error_level
2771 );
2772
2773 for c4 in c3 loop
2774 -- transit from previous act to AND
2775 oe_upgrade_wf2.insert_into_wf_table
2776 ( c4.from_process_activity,
2777 v_and_instance_id1,
2778 c4.result_code,
2779 v_level_error
2780 );
2781 delete from wf_activity_transitions
2782 where from_process_activity = c4.from_process_activity
2783 and to_process_activity = v_shipping_instance_id;
2784
2785 end loop;
2786
2787 -- transit from AND to UPG_SHIPPING_SUB
2788 oe_upgrade_wf2.insert_into_wf_table
2789 ( v_and_instance_id1,
2790 v_shipping_instance_id,
2791 '*',
2792 v_level_error
2793 );
2794
2795
2796 -- now for the UPG_RMA_RECEIVING_SUB handling
2797
2798 -- find out the instance_id for activity "UPG_RMA_RECEIVING_SUB"
2799 select instance_id into v_rma_instance_id
2800 from wf_process_activities
2801 where process_name =
2802 'UPG_PN_OEOL_'||p_line_type||'_'||to_char(p_cycle_id)
2803 and activity_name = 'UPG_RMA_RECEIVING_SUB'
2804 and rownum = 1
2805 order by process_version desc;
2806
2807
2808
2809 SELECT wf_process_activities_s.nextval
2810 INTO v_and_instance_id2
2811 FROM dual;
2812
2813 WF_LOAD.UPLOAD_PROCESS_ACTIVITY (
2814 x_process_item_type => 'OEOL',
2815 x_process_name => 'UPG_PN_'||p_item_type||'_'||p_line_type ||'_'||to_char(p_cycle_id),
2816 x_process_version => 1,
2817 x_activity_item_type => 'WFSTD',
2818 x_activity_name => 'AND',
2819 x_instance_id => v_and_instance_id2,
2820 x_instance_label => 'AND-2',
2821 x_protect_level => 20,
2822 x_custom_level => 20,
2823 x_start_end => '',
2824 x_default_result => null,
2825 x_icon_geometry => '0,0',
2826 x_perform_role => null,
2827 x_perform_role_type => 'CONSTANT',
2828 x_user_comment => null,
2829 x_level_error => v_api_error_level
2830 );
2831 for c6 in c5 loop
2832 -- transit from previous act to AND
2833 oe_upgrade_wf2.insert_into_wf_table
2834 ( c6.from_process_activity,
2835 v_and_instance_id2,
2836 c6.result_code,
2837 v_level_error
2838 );
2839 delete from wf_activity_transitions
2840 where from_process_activity =c6.from_process_activity
2841 and to_process_activity = v_rma_instance_id;
2842 end loop;
2843
2844 -- transit from AND to UPG_RMA_RECEIVING_SUB
2845 oe_upgrade_wf2.insert_into_wf_table
2846 ( v_and_instance_id2,
2847 v_rma_instance_id,
2848 '*',
2849 v_level_error
2850 );
2851
2852
2853 -- now for the util_get_line_cateogry check activity
2854 -- find out the instance_id for the START activity
2855 select instance_id into v_start_instance_id
2856 from wf_process_activities
2857 where process_name = 'UPG_PN_OEOL_'||p_line_type||'_'||
2858 to_char(p_cycle_id)
2859 and activity_name = 'START'
2860 and rownum = 1
2861 order by process_version desc;
2862
2863 SELECT wf_process_activities_s.nextval
2864 INTO v_get_category_instance_id
2865 FROM dual;
2866
2867 WF_LOAD.UPLOAD_PROCESS_ACTIVITY (
2868 x_process_item_type => 'OEOL',
2869 x_process_name => 'UPG_PN_'||p_item_type||'_'||p_line_type ||'_'||to_char(p_cycle_id),
2870 x_process_version => 1,
2871 x_activity_item_type => 'OEOL',
2872 x_activity_name => 'UTIL_GET_LINE_CATEGORY',
2873 x_instance_id => v_get_category_instance_id,
2874 x_instance_label => 'UTIL_GET_LINE_CATEGORY',
2875 x_protect_level => 20,
2876 x_custom_level => 20,
2877 x_start_end => '',
2881 x_perform_role_type => 'CONSTANT',
2878 x_default_result => null,
2879 x_icon_geometry => '0,0',
2880 x_perform_role => null,
2882 x_user_comment => null,
2883 x_level_error => v_api_error_level
2884 );
2885 -- transit from START to new activity
2886 oe_upgrade_wf2.insert_into_wf_table
2887 ( v_start_instance_id,
2888 v_get_category_instance_id,
2889 '*',
2890 v_level_error
2891 );
2892 -- transit from new activity to the 2 ANDs created earlier in procedure
2893 oe_upgrade_wf2.insert_into_wf_table
2894 ( v_get_category_instance_id,
2895 v_and_instance_id1,
2896 'ORDER',
2897 v_level_error
2898 );
2899 oe_upgrade_wf2.insert_into_wf_table
2900 ( v_get_category_instance_id,
2901 v_and_instance_id2,
2902 'RETURN',
2903 v_level_error
2904 );
2905 end Loop;
2906 EXCEPTION
2907 WHEN NO_DATA_FOUND THEN
2908 null;
2909 WHEN OTHERS THEN
2910 v_error_flag := 'Y';
2911 v_error_code := sqlcode;
2912 v_error_message := 'Error occured in cycle: '
2913 ||', Type:'||p_item_type
2914 ||' during adjusting of generic flows'
2915 ||' Oracle error:'||to_char(v_error_code);
2916 END Generic_Flow_Adjusting;
2917
2918
2919 /* Create default transition to itself if there is result code that has no transition - for Custom activity */
2920
2921 PROCEDURE Create_Default_Transition
2922 (
2923 p_item_type IN VARCHAR2,
2924 p_line_type IN VARCHAR2,
2925 p_cycle_id IN NUMBER
2926 )
2927
2928 IS
2929 -- cursor c1 selects those activities do not have a default transition
2930 CURSOR c1 IS
2931 SELECT b.process_item_type,b.process_name,b.activity_name, t.from_process_activity
2932 FROM wf_activity_transitions t, wf_process_activities b
2933 WHERE b.process_name = 'UPG_PN_'||p_item_type||'_'||p_line_type||'_'||to_char(p_cycle_id)
2934 AND t.from_process_activity = b.instance_id
2935 AND b.activity_name like 'UPG_AN%'
2936 AND b.activity_name not like 'UPG_AN%WAIT_FOR_H'
2937 AND b.activity_name not like 'UPG_AN%CONT_L'
2938 AND b.process_version = (select max(c.process_version) from wf_process_activities c
2939 where c.process_name = b.process_name
2940 and c.activity_name = b.activity_name )
2941 AND t.from_process_activity not in
2942 (
2943 SELECT from_process_activity
2944 FROM wf_activity_transitions
2945 WHERE result_code = '*'
2946 )
2947 GROUP BY b.process_item_type,b.process_name, b.activity_name,t.from_process_activity;
2948
2949 v_lookup_count NUMBER;
2950 v_transitions NUMBER;
2951 v_level_error NUMBER := 0;
2952 v_and_upg_close_instance_id NUMBER;
2953 v_api_error_code NUMBER;
2954 v_instance_id NUMBER;
2955
2956 BEGIN
2957 wf_core.session_level := 20;
2958 FOR c2 in c1 LOOP
2959 v_error_level := 2131;
2960 /* Insert into transtion table */
2961 SELECT count(distinct result_code)
2962 INTO v_transitions
2963 FROM wf_activity_transitions
2964 WHERE from_process_activity = c2.from_process_activity;
2965
2966 IF v_transitions = 0 THEN -- no transitions exists, take it to the 'AND' b4 close
2967 SELECT t.from_process_activity
2968 INTO v_and_upg_close_instance_id
2969 FROM wf_activity_transitions t, wf_process_activities b
2970 WHERE b.process_name = 'UPG_PN_'||p_item_type||'_'||p_line_type||'_'
2971 ||to_char(p_cycle_id)
2972 AND b.activity_name = 'UPG_CLOSE_' || decode(p_item_type, 'OEOH',
2973 'HEADER', 'OEOL', 'LINE', '') || '_PROCESS'
2974 AND b.process_version = (select max(c.process_version)
2975 from wf_process_activities c
2976 where c.process_name = b.process_name
2977 and c.activity_name = b.activity_name )
2978 AND t.to_process_activity = b.instance_id;
2979
2980
2981 oe_upgrade_wf2.insert_into_wf_table
2982 (
2983 c2.from_process_activity,
2984 v_and_upg_close_instance_id,
2985 '*',
2986 v_level_error
2987 );
2988 ELSE
2989 SELECT count(*)
2990 INTO v_lookup_count
2991 FROM wf_lookups
2992 WHERE lookup_type = (SELECT result_type
2993 FROM wf_activities
2997
2994 WHERE name = c2.activity_name
2995 AND item_type = p_item_type
2996 AND rownum = 1);
2998
2999 IF v_transitions < v_lookup_count THEN -- some transitions are missing
3000 SELECT wf_process_activities_s.nextval
3001 INTO v_instance_id
3002 FROM dual;
3003
3004 wf_load.upload_process_activity
3005 (
3006 x_process_item_type => p_item_type,
3007 x_process_name => 'UPG_PN_' || p_item_type || '_' || p_line_type || '_' || to_char(p_cycle_id),
3008 x_process_version => 1,
3009 x_activity_item_type => 'WFSTD',
3010 x_activity_name => 'WAIT',
3011 x_instance_id => v_instance_id,
3012 x_instance_label => 'WAIT_'||to_char(v_instance_id),
3013 x_protect_level => 20,
3014 x_custom_level => 20,
3015 x_start_end => NULL,
3016 x_default_result => NULL,
3017 x_icon_geometry => '0,0',
3018 x_perform_role => NULL,
3019 x_perform_role_type => 'CONSTANT',
3020 x_user_comment => NULL,
3021 x_level_error => v_api_error_code
3022 );
3023 wf_load.upload_activity_attr_value
3024 (
3025 x_process_activity_id => v_instance_id,
3026 x_name => 'WAIT_MODE',
3027 x_protect_level => 20,
3028 x_custom_level => 20,
3029 x_value => 'RELATIVE',
3030 x_value_type => 'CONSTANT',
3031 x_effective_date => sysdate - 1,
3032 x_level_error => v_api_error_code
3033 );
3034 wf_load.upload_activity_attr_value
3035 (
3036 x_process_activity_id => v_instance_id,
3037 x_name => 'WAIT_RELATIVE_TIME',
3038 x_protect_level => 20,
3039 x_custom_level => 20,
3040 x_value => 1,
3041 x_value_type => 'CONSTANT',
3042 x_effective_date => sysdate - 1,
3043 x_level_error => v_api_error_code
3044 );
3045
3046 oe_upgrade_wf2.insert_into_wf_table
3047 (
3048 c2.from_process_activity,
3049 v_instance_id,
3050 '*',
3051 v_level_error
3052 );
3053 oe_upgrade_wf2.insert_into_wf_table
3054 (
3055 v_instance_id,
3056 c2.from_process_activity,
3057 '*',
3058 v_level_error
3059 );
3060 END IF;
3061 END IF;
3062 END LOOP;
3063
3064 V_ERROR_FLAG := 'N';
3065 EXCEPTION
3066 WHEN NO_DATA_FOUND THEN
3067 null;
3068 WHEN OTHERS THEN
3069
3070 v_error_flag := 'Y';
3071 v_error_code := sqlcode;
3072 v_error_message := 'Error occured in cycle: ' ||to_char(p_cycle_id)
3073 ||', Type:'||p_item_type
3074 ||' during creation of Default Transition...'
3075 ||' Oracle error:'||to_char(v_error_code);
3076 END Create_Default_Transition;
3077
3078
3079 /* connect 'START' to all 'open start' in line level only*/
3080
3081 PROCEDURE Create_Line_Start
3082 (
3083 p_cycle_id IN NUMBER,
3084 p_line_type IN VARCHAR2
3085 )
3086
3087 IS
3088 CURSOR c1 IS
3089 SELECT distinct from_process_activity
3090 FROM wf_activity_transitions
3091 WHERE from_process_activity IN
3092 (
3093 SELECT instance_id
3094 FROM wf_process_activities
3095 WHERE process_name = 'UPG_PN_OEOL_'||p_line_type
3096 ||'_'||to_char(p_cycle_id)
3097 AND activity_name <> 'START'
3098 )
3099 AND from_process_activity NOT IN
3100 (
3101 SELECT to_process_activity
3102 FROM wf_activity_transitions a,
3103 wf_process_activities b
3104 WHERE a.to_process_activity = b.instance_id
3108 );
3105 AND b.process_name = 'UPG_PN_OEOL_'||p_line_type
3106 ||'_'||to_char(p_cycle_id)
3107 AND a.from_process_activity <> a.to_process_activity
3109
3110
3111 v_instance_id NUMBER := 0;
3112 v_level_error NUMBER := 0;
3113
3114 BEGIN
3115 -- dbms_output.enable('9999999999');
3116
3117 wf_core.session_level := 20;
3118 -- dbms_output.put_line('Entered program');
3119
3120 v_instance_id := oe_upgrade_wf2.get_instance_id
3121 ('UPG_PN_OEOL'||'_'||p_line_type
3122 ||'_'||to_char(p_cycle_id),
3123 'START',
3124 'START');
3125 FOR c2 in c1 LOOP
3126
3127 v_error_level := 2143;
3128 /* Insert into transtion table */
3129 oe_upgrade_wf2.insert_into_wf_table
3130 (
3131 v_instance_id,
3132 c2.from_process_activity,
3133 '*',
3134 v_level_error
3135 );
3136
3137 -- dbms_output.put_line('inserted trans');
3138 END LOOP;
3139
3140 V_ERROR_FLAG := 'N';
3141 EXCEPTION
3142 WHEN OTHERS THEN
3143
3144 v_error_flag := 'Y';
3145 v_error_code := sqlcode;
3146 v_error_message := 'Error occured in cycle: ' ||to_char(p_cycle_id)
3147 ||' during creation of Open Line Start...'
3148 ||' Oracle error:'||to_char(v_error_code);
3149 END Create_Line_Start;
3150
3151 PROCEDURE Wait_Flow_Adjusting
3152 (
3153 p_item_type IN VARCHAR2,
3154 p_cycle_id IN NUMBER,
3155 p_line_type IN VARCHAR2
3156 )
3157 IS
3158
3159 v_delete_wait_instance NUMBER;
3160 v_continue_line_flow_label VARCHAR2(30);
3161 v_cont_act_instance_id NUMBER;
3162 v_tmp NUMBER;
3163
3164 Cursor c1 is
3165 SELECT a.instance_id, a.instance_label, attr_value1.text_value
3166 FROM wf_process_activities a, wf_process_activities b,
3167 wf_activity_attr_values attr_value1, wf_activity_attr_values attr_value2
3168 WHERE a.process_name = 'UPG_PN_'||p_item_type||'_'||p_line_type||'_'||to_char(p_cycle_id)
3169 AND a.process_name = b.process_name
3170 AND a.activity_name like 'UPG_AN_OEOL_%WAIT_FOR_H'
3171 AND b.activity_name like 'UPG_AN_OEOL_%WAIT_FOR_H'
3172 AND a.instance_id <> b.instance_id
3173 AND attr_value1.name = 'CONTINUATION_ACTIVITY'
3174 AND attr_value1.process_activity_id = a.instance_id
3175 AND attr_value2.name = 'CONTINUATION_ACTIVITY'
3176 AND attr_value2.process_activity_id = b.instance_id
3177 AND attr_value1.text_value = attr_value2.text_value;
3178
3179 Cursor c3 is
3180 SELECT from_process_activity, result_code
3181 FROM wf_activity_transitions
3182 WHERE to_process_activity = v_delete_wait_instance;
3183
3184 Cursor c5 is
3185 SELECT to_process_activity, result_code
3186 FROM wf_activity_transitions
3187 WHERE from_process_activity = v_delete_wait_instance;
3188
3189
3190
3191 BEGIN
3192 for c2 in c1 loop
3193 -- dbms_output.put_line('In wait_flow_adjusting: ' || c2.text_value);
3194 SELECT attr_value.text_value
3195 INTO v_continue_line_flow_label
3196 FROM wf_process_activities a, wf_activity_attr_values attr_value
3197 WHERE a.instance_label = c2.text_value
3198 AND a.process_name = 'UPG_PN_OEOH_REG_'||to_char(p_cycle_id)
3199 AND attr_value.process_activity_id = a.instance_id
3200 AND attr_value.name = 'WAITING_ACTIVITY';
3201
3202
3203 IF c2.instance_label <> v_continue_line_flow_label THEN -- adjust
3204 SELECT instance_id
3205 INTO v_cont_act_instance_id
3206 FROM wf_process_activities
3207 WHERE instance_label=v_continue_line_flow_label
3208 AND process_name = 'UPG_PN_'||p_item_type||'_'||p_line_type||'_'||to_char(p_cycle_id);
3209
3210 v_delete_wait_instance := c2.instance_id;
3211 -- adjust all incoming transitions to point to the AND before the central WAIT activity
3212 for c4 in c3 loop
3213 BEGIN
3214 SELECT 1
3215 INTO v_tmp
3216 FROM wf_activity_transitions
3217 WHERE from_process_activity = c4.from_process_activity
3218 AND to_process_activity = v_cont_act_instance_id
3219 AND result_code = c4.result_code;
3220 EXCEPTION
3221 WHEN NO_DATA_FOUND THEN
3222 update wf_activity_transitions
3223 set to_process_activity = v_cont_act_instance_id
3224 where from_process_activity = c4.from_process_activity
3225 and to_process_activity = v_delete_wait_instance;
3226 END;
3227 end loop;
3228 -- adjust all the outgoing transitions to originate from the central WAIT activity
3229 -- dbms_output.put_line('in wait_flow_adjusting3');
3230 for c6 in c5 loop
3231 BEGIN
3232 SELECT 1
3236 AND to_process_activity = c6.to_process_activity
3233 INTO v_tmp
3234 FROM wf_activity_transitions
3235 WHERE from_process_activity = v_cont_act_instance_id
3237 AND result_code = c6.result_code;
3238 EXCEPTION
3239 WHEN NO_DATA_FOUND THEN
3240 update wf_activity_transitions
3241 set from_process_activity = v_cont_act_instance_id
3242 where from_process_activity = v_delete_wait_instance
3243 and to_process_activity = c6.to_process_activity;
3244 END;
3245 end loop;
3246 -- delete the extra WAIT
3247 WF_LOAD.Delete_Process_Activity(p_step=> v_delete_wait_instance);
3248 END IF;
3249 end loop;
3250 END Wait_Flow_Adjusting;
3251
3252
3253 /* Fix Geometry coordinates */
3254
3255 PROCEDURE Adjust_Arrow_Geometry
3256 (
3257 p_item_type IN VARCHAR2,
3258 p_line_type IN VARCHAR2,
3259 p_cycle_id IN NUMBER
3260 )
3261
3262 IS
3263
3264 CURSOR c1 IS
3265 SELECT max(wpa2.icon_geometry) icon,
3266 wpa.instance_id id
3267 FROM wf_activity_transitions wat,
3268 wf_process_activities wpa,
3269 wf_process_activities wpa2
3270 WHERE wat.to_process_activity = wpa.instance_id
3271 AND wpa.process_name = 'UPG_PN_'||p_item_type||'_'
3272 ||p_line_type||'_'||to_char(p_cycle_id)
3273 AND wpa.icon_geometry = '0,0'
3274 AND wat.from_process_activity = wpa2.instance_id
3275 AND wpa2.icon_geometry <> '0,0'
3276 GROUP BY wpa.instance_id;
3277
3278 CURSOR c3 IS
3279 SELECT icon_geometry icon
3280 FROM wf_process_activities
3281 WHERE process_name = 'UPG_PN_'||p_item_type||'_'
3282 ||p_line_type||'_'||to_char(p_cycle_id)
3283 GROUP BY icon_geometry
3284 HAVING count(icon_geometry) > 1;
3285
3286 CURSOR c7 IS
3287 SELECT a.icon_geometry icon_from,
3288 a.instance_id from_id,
3289 b.icon_geometry icon_to,
3290 b.instance_id to_id
3291 FROM wf_process_activities a,
3292 wf_process_activities b,
3293 wf_activity_transitions c
3294 WHERE a.instance_id = c.from_process_activity
3295 AND b.instance_id = c.to_process_activity
3296 AND a.process_name = 'UPG_PN_'||p_item_type||'_'
3297 ||p_line_type||'_'||to_char(p_cycle_id);
3298
3299 v_icon_row NUMBER;
3300 v_icon_row_char VARCHAR2(80);
3301 v_flag VARCHAR2(1) := 'Y';
3302 v_icon_x_value NUMBER;
3303
3304
3305 BEGIN
3306 -- dbms_output.enable('999999999');
3307
3308 UPDATE wf_process_activities
3309 SET icon_geometry = '-312,0'
3310 WHERE activity_name = 'START'
3311 and process_name like 'UPG_PN%'
3312 and icon_geometry = '0,0';
3313
3314 BEGIN
3315 wf_core.session_level := 20;
3316 -- dbms_output.put_line('Entered into c1');
3317
3318 v_error_level := 2151;
3319 WHILE v_flag = 'Y' LOOP
3320 v_flag := 'N';
3321 -- dbms_output.put_line('Before going into c2 loop..');
3322
3323 FOR c2 IN c1 LOOP
3324 v_error_level := 2152;
3325 v_flag := 'Y';
3326 oe_upgrade_wf2.get_icon_x_value
3327 (
3328 c2.icon,
3329 v_icon_row
3330 );
3331
3332 -- dbms_output.put_line('v_icon_row = '||to_char(v_icon_row));
3333
3334 v_error_level := 2153;
3335 UPDATE wf_process_activities
3336 SET icon_geometry = to_char(v_icon_row+110)||',0'
3337 WHERE instance_id = c2.id;
3338 END LOOP;
3339 END LOOP;
3340
3341 FOR c4 IN c3 LOOP
3342 v_error_level := 2154;
3343 -- dbms_output.put_line('In c4 loop..');
3344 DECLARE
3345 CURSOR c5 IS
3346 SELECT instance_id id
3347 FROM wf_process_activities
3348 WHERE process_name = 'UPG_PN_'||p_item_type||'_'
3349 ||p_line_type||'_'||to_char(p_cycle_id)
3350 AND icon_geometry = c4.icon;
3351
3352 v_icon_col NUMBER;
3353 BEGIN
3354 v_error_level := 2155;
3355 v_icon_col := 0;
3356 FOR c6 in c5 LOOP
3357 v_error_level := 2156;
3358 v_icon_x_value := 0;
3359 oe_upgrade_wf2.get_icon_x_value
3360 (
3361 c4.icon,
3362 v_icon_x_value
3363 );
3364
3365 v_error_level := 2157;
3366 UPDATE wf_process_activities
3367 SET icon_geometry = to_char(v_icon_x_value)||','
3368 ||to_char(v_icon_col)
3369 WHERE instance_id = c6.id;
3370
3371 v_icon_col := v_icon_col + 100;
3372 END LOOP;
3373 END;
3374 END LOOP;
3375 -- dbms_output.put_line('Out of c1 loop..');
3376 END;
3380 UPDATE wf_activity_transitions
3377
3378 FOR c8 in c7 LOOP
3379 v_error_level := 2157;
3381 SET arrow_geometry = '1;0;0;0;0.30000;'||c8.icon_from||':'||c8.icon_to||':'
3382 WHERE from_process_activity = c8.from_id
3383 AND to_process_activity = c8.to_id;
3384 END LOOP;
3385
3386 V_ERROR_FLAG := 'N';
3387 EXCEPTION
3388 WHEN OTHERS THEN
3389
3390 v_error_flag := 'Y';
3391 v_error_code := sqlcode;
3392 v_error_message := 'Error occured in cycle: ' ||to_char(p_cycle_id)
3393 ||', Type:'||p_item_type
3394 ||' during Adjusting Arrow Geometry ...'
3395 ||' Oracle error:'||to_char(v_error_code);
3396
3397 END Adjust_Arrow_Geometry;
3398
3399
3400 /*=======================================================================
3401 To close the open end flows and take them to AND before CLOSE
3402 To be run once, intended for both OEOH and OEOH (Headers and Lines)
3403 Is rerunnable.
3404 ========================================================================*/
3405
3406 PROCEDURE Close_Open_End
3407 (
3408 p_cycle_id IN NUMBER,
3409 p_line_type IN VARCHAR2,
3410 p_item_type IN VARCHAR2
3411 )
3412
3413 IS
3414 v_end_found_flag VARCHAR2(1);
3415 v_end_activity_id NUMBER;
3416 v_and_activity_id NUMBER;
3417 v_api_error_level NUMBER;
3418
3419 CURSOR c1 IS
3420 SELECT
3421 to_process_activity ,
3422 process_name,
3423 process_item_type
3424 FROM
3425 wf_activity_transitions WAT,
3426 wf_process_activities
3427 WHERE
3428 process_name = 'UPG_PN_'||p_item_type||'_'
3429 ||p_line_type||'_'||to_char(p_cycle_id)
3430 AND wat.to_process_activity NOT IN
3431 (
3432 SELECT from_process_activity
3433 FROM wf_activity_transitions
3434 )
3435 AND instance_id = to_process_activity
3436 AND to_process_activity IN
3437 (
3438 SELECT instance_id
3439 FROM wf_process_activities wpa2
3440 WHERE wpa2.activity_name <> 'END'
3441 AND wpa2.process_name = 'UPG_PN_'||p_item_type||'_'||p_line_type
3442 ||'_'||to_char(p_cycle_id));
3443 BEGIN
3444 wf_core.session_level := 20;
3445 FOR c2 in c1 LOOP
3446 v_error_level := 2161;
3447 v_end_found_flag := 'N';
3448 BEGIN
3449 SELECT
3450 'Y',
3451 instance_id
3452 INTO
3453 v_end_found_flag,
3454 v_end_activity_id
3455 FROM wf_process_activities
3456 WHERE process_name = c2.process_name
3457 AND instance_label = 'AND'
3458 AND activity_name = 'AND';
3459 EXCEPTION
3460 WHEN NO_DATA_FOUND THEN
3461 v_end_found_flag := 'N';
3462 when TOO_MANY_ROWS THEN
3463 v_end_found_flag := 'Y';
3464 END;
3465
3466 IF v_end_found_flag = 'N' THEN
3467 v_error_level := 2162;
3468 SELECT wf_process_activities_s.nextval
3469 INTO v_and_activity_id
3470 FROM dual;
3471
3472 v_error_level := 2163;
3473 WF_LOAD.UPLOAD_PROCESS_ACTIVITY (
3474 x_process_item_type => c2.process_item_type,
3475 x_process_name => c2.process_name,
3476 x_process_version => 1,
3477 x_activity_item_type => 'WFSTD',
3478 x_activity_name => 'AND',
3479 x_instance_id => v_and_activity_id,
3480 x_instance_label => 'AND',
3481 x_protect_level => 20,
3482 x_custom_level => 20,
3483 x_start_end => '',
3484 x_default_result => null,
3485 x_icon_geometry => '0,0',
3486 x_perform_role => null,
3487 x_perform_role_type => 'CONSTANT',
3488 x_user_comment => null,
3489 x_level_error => v_api_error_level
3490 );
3491
3492 /* Create END activity */
3493 WF_LOAD.UPLOAD_PROCESS_ACTIVITY (
3494 x_process_item_type => c2.process_item_type,
3495 x_process_name => c2.process_name,
3496 x_process_version => 1,
3497 x_activity_item_type => 'WFSTD',
3498 x_activity_name => 'END',
3499 x_instance_id => v_end_activity_id,
3500 x_instance_label => c2.process_name||'_END',
3501 x_protect_level => 20,
3502 x_custom_level => 20,
3503 x_start_end => 'END',
3504 x_default_result => null,
3505 x_icon_geometry => '0,0',
3509 x_level_error => v_api_error_level
3506 x_perform_role => null,
3507 x_perform_role_type => 'CONSTANT',
3508 x_user_comment => null,
3510 );
3511
3512 /* Establish the relation between 'AND' and 'END'*/
3513 WF_LOAD.UPLOAD_ACTIVITY_TRANSITION (
3514 x_from_process_activity => v_and_activity_id,
3515 x_result_code => '*',
3516 x_to_process_activity => v_end_activity_id,
3517 x_protect_level => 20,
3518 x_custom_level => 20,
3519 x_arrow_geometry => '1;0;0;0;0.30000;0,0:0,0:',
3520 x_level_error => v_api_error_level
3521 );
3522
3523 END IF;
3524
3525 v_error_level := 2164;
3526 WF_LOAD.UPLOAD_ACTIVITY_TRANSITION (
3527 x_from_process_activity => c2.to_process_activity,
3528 x_result_code => '*',
3529 x_to_process_activity => v_end_activity_id,
3530 x_protect_level => 20,
3531 x_custom_level => 20,
3532 x_arrow_geometry => '1;0;0;0;0.30000;0,0:0,0:',
3533 x_level_error => v_api_error_level
3534 );
3535 END LOOP;
3536
3537 V_ERROR_FLAG := 'N';
3538 EXCEPTION
3539 WHEN OTHERS THEN
3540
3541 v_error_flag := 'Y';
3542 v_error_code := sqlcode;
3543 v_error_message := 'Error occured in cycle: ' ||to_char(p_cycle_id)
3544 ||', Type:'||p_item_type
3545 ||' during creation of Close Open End...'
3546 ||' Oracle error:'||to_char(v_error_code);
3547 END Close_Open_END;
3548
3549 PROCEDURE Upgrade_Workflow
3550
3551 IS
3552
3553 /* CURSOR c1 will filter out nocopy the unsupported order cycles
3554
3555 including (in order):
3556 1. Filter out nocopy Cycles that have NO Ship Confirm but have Inventory Interface
3557
3558 2. Filter out nocopy cycles have both RMA interface and Purchase release
3559
3560 3. Filter out nocopy cycles already processed
3561
3562 4. Filter out nocopy cycles that have no line actions in them
3563
3564 5. Filter out nocopy Cycles that have no header actions in them
3565
3566 6.Filter out nocopy cycles that have cancel_order(5) in them
3567
3568 7.Filter out nocopy cycles that have cancel_line (6)in them
3569
3570 THIS IS NOW DONE IN THE NEW SCRIPT
3571 */
3572
3573 CURSOR c1 IS
3574 SELECT sc.cycle_id
3575 FROM so_cycles sc
3576 WHERE sc.cycle_id NOT IN
3577 (select cycle_id
3578 from oe_upgrade_log
3579 where (module='NU'
3580 and cycle_id is not null)
3581 or (module is null
3582 and cycle_id is not null))
3583 AND (exists (select 1
3584 from so_headers_all sh
3585 where sh.cycle_id = sc.cycle_id
3586 and open_flag = 'Y')
3587 OR exists (select 1
3588 from so_lines_all sl
3589 where sl.cycle_id = sc.cycle_id
3590 and open_flag = 'Y'));
3591
3592 cursor c3 is
3593 select cycle_id from so_cycles;
3594
3595 v_error_code NUMBER;
3596 v_cfg_item VARCHAR2(1) := 'N';
3597 BEGIN
3598 V_ERROR_FLAG := 'N';
3599 --dbms_output.enable('999999999999');
3600
3601 -- dbms_output.put_line('************* FLAG1 = '||v_error_flag);
3602 IF V_ERROR_FLAG = 'N' THEN
3603 OE_UPGRADE_WF2.Create_Lookup_Type('OEOH');
3604 COMMIT;
3605 END IF;
3606
3607 -- dbms_output.put_line('************* FLAG2 = '||v_error_flag);
3608 IF V_ERROR_FLAG = 'N' THEN
3609 OE_UPGRADE_WF2.Create_Lookup_Type('OEOL');
3610 COMMIT;
3611 END IF;
3612
3613 -- dbms_output.put_line('************* FLAG3 = '||v_error_flag);
3614 IF V_ERROR_FLAG = 'N' THEN
3615 OE_UPGRADE_WF2.Create_Lookup_Code('OEOH');
3616 COMMIT;
3617 END IF;
3618
3619 -- dbms_output.put_line('************* FLAG4 = '||v_error_flag);
3620 IF V_ERROR_FLAG = 'N' THEN
3621 OE_UPGRADE_WF2.Create_Lookup_Code('OEOL');
3622 COMMIT;
3623 END IF;
3624
3625 -- dbms_output.put_line('************* FLAG5 = '||v_error_flag);
3626 IF V_ERROR_FLAG = 'N' THEN
3627 OE_UPGRADE_WF2.Create_activity_name('OEOH');
3628 COMMIT;
3629 END IF;
3630
3631 -- dbms_output.put_line('************* FLAG6 = '||v_error_flag);
3632 IF V_ERROR_FLAG = 'N' THEN
3633 OE_UPGRADE_WF2.Create_activity_name('OEOL');
3634 COMMIT;
3635 END IF;
3636
3637 for c4 in c3 loop
3638 --dbms_output.put_line('************* FLAG10 = '||v_error_flag);
3639 IF V_ERROR_FLAG = 'N' THEN
3640 OE_UPGRADE_WF2.Create_process_name ('OEOH','REG',c4.cycle_id);
3641 END IF;
3642
3643 --dbms_output.put_line('************* FLAG11 = '||v_error_flag);
3644 IF V_ERROR_FLAG = 'N' THEN
3645 OE_UPGRADE_WF2.Create_process_name ('OEOL','REG',c4.cycle_id);
3646 END IF;
3650 SELECT 'Y'
3647 --create an additional CFG flow for ATO config item
3648 --action 15 is manufacturing release
3649 BEGIN
3651 INTO v_cfg_item
3652 FROM so_cycle_actions
3653 WHERE action_id = 15
3654 AND cycle_id = c4.cycle_id;
3655 EXCEPTION
3656 WHEN NO_DATA_FOUND THEN
3657 v_cfg_item := 'N';
3658 WHEN OTHERS THEN
3659 v_error_flag :='Y';
3660 v_error_code := sqlcode;
3661 v_error_message := 'Error occured in cycle: ' ||to_char(c4.cycle_id)
3662 ||' Checking for Config line. Oracle error:'||to_char(v_error_code);
3663 END;
3664 IF v_cfg_item = 'Y' THEN
3665 OE_UPGRADE_WF2.Create_process_name ('OEOL','CFG',c4.cycle_id);
3666 END IF;
3667 v_cfg_item := 'N';
3668
3669 end loop;
3670
3671 -- MAIN LOOP STARTS
3672
3673 FOR c2 IN c1 LOOP
3674 V_ERROR_FLAG := 'N';
3675 -- dbms_output.put_line('************* FLAG11 = '||v_error_flag);
3676 IF V_ERROR_FLAG = 'N' THEN
3677 OE_UPGRADE_WF2.Create_process_activity ('OEOH',c2.cycle_id,'REG');
3678 END IF;
3679
3680 -- dbms_output.put_line('************* FLAG12 = '||v_error_flag);
3681 IF V_ERROR_FLAG = 'N' THEN
3682 OE_UPGRADE_WF2.Create_activity_and ('OEOH','REG',c2.cycle_id);
3683 END IF;
3684
3685 BEGIN
3686 SELECT 'Y'
3687 INTO v_cfg_item
3688 FROM so_cycle_actions
3689 WHERE action_id = 15
3690 AND cycle_id = c2.cycle_id;
3691 EXCEPTION
3692 WHEN NO_DATA_FOUND THEN
3693 v_cfg_item := 'N';
3694 WHEN OTHERS THEN
3695 v_error_flag :='Y';
3696 v_error_code := sqlcode;
3697 v_error_message := 'Error occured in cycle: ' ||to_char(c2.cycle_id)
3698 ||' Checking for Config line. Oracle error:'||to_char(v_error_code);
3699 END;
3700 -- dbms_output.put_line('************* FLAG14 = '||v_error_flag);
3701 IF V_ERROR_FLAG = 'N' THEN
3702 OE_UPGRADE_WF2.Create_process_activity ('OEOL',c2.cycle_id,'REG');
3703 IF v_cfg_item = 'Y' THEN
3704 -- dbms_output.put_line('create process activity for cfg');
3705 OE_UPGRADE_WF2.Create_process_activity ('OEOL',c2.cycle_id,'CFG');
3706 END IF;
3707
3708 END IF;
3709
3710 -- dbms_output.put_line('************* FLAG15 = '||v_error_flag);
3711 IF V_ERROR_FLAG = 'N' THEN
3712 OE_UPGRADE_WF2.Create_activity_and ('OEOL','REG',c2.cycle_id);
3713 IF v_cfg_item = 'Y' THEN
3714 -- dbms_output.put_line('create activity and for cfg ' || to_char(c2.cycle_id));
3715 OE_UPGRADE_WF2.Create_activity_and ('OEOL','CFG',c2.cycle_id);
3716 END IF;
3717 END IF;
3718
3719 -- -2 is for ENTERED/PARTIAL for the line action
3720 update oe_action_pre_reqs
3721 set action_id = -2
3722 where action_id = 1
3723 and type = 'OEOL'
3724 and result_id in (5, 15);
3725
3726 -- dbms_output.put_line('************* FLAG16 = '||v_error_flag);
3727 IF V_ERROR_FLAG = 'N' THEN
3728 -- dbms_output.put_line('going in dependency');
3729 OE_UPGRADE_WF2.Create_header_line_dependency (c2.cycle_id,'REG');
3730 IF v_cfg_item = 'Y' THEN
3731 -- dbms_output.put_line('create dependency for cfg ' || to_char(c2.cycle_id));
3732 OE_UPGRADE_WF2.Create_header_line_dependency (c2.cycle_id,'CFG');
3733 END IF;
3734 END IF;
3735
3736 -- dbms_output.put_line('************* FLAG17 = '||v_error_flag);
3737 IF V_ERROR_FLAG = 'N' THEN
3738 OE_UPGRADE_WF2.Create_activity_or ('OEOH','REG',c2.cycle_id);
3739 END IF;
3740
3741 -- dbms_output.put_line('************* FLAG18 = '||v_error_flag);
3742 IF V_ERROR_FLAG = 'N' THEN
3743 -- -1 is for ENTERED/PARTIAL for the header action
3744 update oe_action_pre_reqs
3745 set action_id = -1
3746 where action_id = 1
3747 and type = 'OEOH'
3748 and result_id in (5, 15);
3749
3750 OE_UPGRADE_WF2.Create_activity_transition ('OEOH',c2.cycle_id,'REG');
3751 END IF;
3752
3753 IF V_ERROR_FLAG = 'N' THEN
3754 OE_UPGRADE_WF2.Create_notification (c2.cycle_id,'REG','OEOH');
3755 END IF;
3756
3757 -- dbms_output.put_line('************* FLAG19 = '||v_error_flag);
3758 IF V_ERROR_FLAG = 'N' THEN
3759 OE_UPGRADE_WF2.Create_default_transition ('OEOH','REG',c2.cycle_id);
3760 END IF;
3761
3762 -- dbms_output.put_line('************* FLAG20 = '||v_error_flag);
3763 IF V_ERROR_FLAG = 'N' THEN
3764 OE_UPGRADE_WF2.Close_Open_End (c2.cycle_id,'REG','OEOH');
3765 END IF;
3766
3767 -- dbms_output.put_line('************* FLAG21 = '||v_error_flag);
3768 IF V_ERROR_FLAG = 'N' THEN
3772 -- dbms_output.put_line('************* FLAG22 = '||v_error_flag);
3769 OE_UPGRADE_WF2.Adjust_arrow_geometry ('OEOH','REG',c2.cycle_id);
3770 END IF;
3771
3773 IF V_ERROR_FLAG = 'N' THEN
3774 OE_UPGRADE_WF2.Create_activity_or ('OEOL','REG',c2.cycle_id);
3775 IF v_cfg_item = 'Y' THEN
3776 --dbms_output.put_line('create activity or for cfg');
3777 OE_UPGRADE_WF2.Create_activity_or ('OEOL','CFG',c2.cycle_id);
3778 END IF;
3779 END IF;
3780
3781
3782
3783 -- dbms_output.put_line('************* FLAG23 = '||v_error_flag);
3784 IF V_ERROR_FLAG = 'N' THEN
3785 OE_UPGRADE_WF2.Create_activity_transition ('OEOL',c2.cycle_id,'REG');
3786 IF v_cfg_item = 'Y' THEN
3787 -- dbms_output.put_line('create activity transition for cfg');
3788 OE_UPGRADE_WF2.Create_activity_transition ('OEOL',c2.cycle_id,'CFG');
3789 END IF;
3790 END IF;
3791
3792
3793
3794 --dbms_output.put_line('************* FLAG23+ = '||v_error_flag);
3795 IF V_ERROR_FLAG = 'N' THEN
3796 OE_UPGRADE_WF2.Create_notification (c2.cycle_id,'REG','OEOL');
3797 IF v_cfg_item = 'Y' THEN
3798 --dbms_output.put_line('create notification for cfg');
3799 OE_UPGRADE_WF2.Create_notification (c2.cycle_id,'CFG','OEOL');
3800 END IF;
3801 END IF;
3802
3803 -- dbms_output.put_line('************* FLAG24 = '||v_error_flag|| ' cycle_id:' || to_char(c2.cycle_id));
3804 IF V_ERROR_FLAG = 'N' THEN
3805 OE_UPGRADE_WF2.Create_default_transition ('OEOL','REG',c2.cycle_id);
3806 IF v_cfg_item = 'Y' THEN
3807 -- dbms_output.put_line('create default end for cfg');
3808 OE_UPGRADE_WF2.Create_default_transition ('OEOL','CFG',c2.cycle_id);
3809 END IF;
3810 END IF;
3811
3812 --dbms_output.put_line('************* FLAG25 = '||v_error_flag);
3813 IF V_ERROR_FLAG = 'N' THEN
3814 OE_UPGRADE_WF2.Create_line_start (c2.cycle_id,'REG');
3815 IF v_cfg_item = 'Y' THEN
3816 OE_UPGRADE_WF2.Create_line_start (c2.cycle_id,'CFG');
3817 END IF;
3818 END IF;
3819
3820 -- dbms_output.put_line('************* FLAG26 = '||v_error_flag);
3821 IF V_ERROR_FLAG = 'N' THEN
3822 OE_UPGRADE_WF2.Close_Open_End (c2.cycle_id,'REG','OEOL');
3823 IF v_cfg_item = 'Y' THEN
3824 OE_UPGRADE_WF2.Close_Open_End (c2.cycle_id,'CFG','OEOL');
3825 END IF;
3826 END IF;
3827
3828 -- dbms_output.put_line('************* FLAG26+ = '||v_error_flag);
3829 IF V_ERROR_FLAG = 'N' THEN
3830 OE_UPGRADE_WF2.Ship_Confirm_Adjusting (c2.cycle_id,'REG');
3831 IF v_cfg_item = 'Y' THEN
3832 -- dbms_output.put_line('adjust ship confirm for cfg');
3833 OE_UPGRADE_WF2.Ship_Confirm_Adjusting (c2.cycle_id,'CFG');
3834 END IF;
3835 END IF;
3836
3837 -- dbms_output.put_line('************* FLAG26++ = '||v_error_flag);
3838 IF V_ERROR_FLAG = 'N' THEN
3839 IF v_cfg_item = 'Y' THEN
3840 --dbms_output.put_line('adjust ato for cfg');
3841 OE_UPGRADE_WF2.ATO_Adjusting (c2.cycle_id);
3842 END IF;
3843 END IF;
3844
3845 -- dbms_output.put_line('************* FLAG27 = '||v_error_flag);
3846 IF V_ERROR_FLAG = 'N' THEN
3847 OE_UPGRADE_WF2.Generic_Flow_Adjusting ('OEOL', c2.cycle_id,'REG');
3848 IF v_cfg_item = 'Y' THEN
3849 -- dbms_output.put_line('adjust ato for cfg');
3850 OE_UPGRADE_WF2.Generic_Flow_Adjusting ('OEOL', c2.cycle_id,'CFG');
3851 END IF;
3852 END IF;
3853
3854 -- dbms_output.put_line('************* FLAG28 = '||v_error_flag);
3855 -- adjust for multiple waits
3856 IF V_ERROR_FLAG = 'N' THEN
3857 OE_UPGRADE_WF2.Wait_Flow_Adjusting ('OEOL', c2.cycle_id,'REG');
3858 IF v_cfg_item = 'Y' THEN
3859 --dbms_output.put_line('adjust ato for cfg');
3860 OE_UPGRADE_WF2.Wait_Flow_Adjusting ('OEOL', c2.cycle_id,'CFG');
3861 END IF;
3862 END IF;
3863
3864 -- dbms_output.put_line('************* FLAG29 = '||v_error_flag);
3865 IF V_ERROR_FLAG = 'N' THEN
3866 OE_UPGRADE_WF2.Adjust_arrow_geometry ('OEOL','REG',c2.cycle_id);
3867 IF v_cfg_item = 'Y' THEN
3868 OE_UPGRADE_WF2.Adjust_arrow_geometry ('OEOL','CFG',c2.cycle_id);
3869 END IF;
3870 END IF;
3871
3872
3873
3874 IF V_ERROR_FLAG = 'Y' THEN
3875 -- dbms_output.put_line('************* FLAG30 = '||v_error_flag);
3876 ROLLBACK;
3877 INSERT INTO oe_upgrade_errors
3878 (module,error_level,comments,creation_date)
3879 VALUES
3880 ('WF',v_error_level,v_error_message,sysdate - 1);
3881 COMMIT;
3882 ELSE
3883 INSERT INTO oe_upgrade_log ( creation_date,cycle_id)
3887 v_cfg_item :='N';
3884 VALUES (sysdate,c2.cycle_id);
3885 COMMIT;
3886 END IF;
3888 END LOOP;
3889
3890 END Upgrade_Workflow;
3891
3892 Procedure Create_Notification
3893 (
3894 P_cycle_id IN Number,
3895 P_line_type IN varchar2,
3896 P_item_type IN varchar2
3897 )
3898 IS
3899 cursor c1 is
3900 select
3901 instance_id,
3902 wa.name,
3903 wa.result_type
3904 from
3905 wf_process_activities wpa,
3906 wf_activities wa
3907 where wpa.activity_name = wa.name
3908 and wpa.process_name = 'UPG_PN_'||p_item_type||'_'||p_line_type||'_'||to_char(p_cycle_id)
3909 and wa.type = 'NOTICE';
3910
3911 v_notn_instance_id number;
3912
3913 cursor c3 is
3914 select to_process_activity, result_code
3915 from wf_activity_transitions
3916 where from_process_activity = v_notn_instance_id;
3917
3918 cursor c5 is
3919 select from_process_activity, result_code
3920 from wf_activity_transitions
3921 where to_process_activity = v_notn_instance_id;
3922
3923 v_activity_name varchar2(50);
3924 v_process_name varchar2(50);
3925 v_version number;
3926 v_api_error_code number;
3927 v_lookup_type varchar2(30);
3928 v_instance_id number;
3929 v_from_instance_id number;
3930 v_to_instance_id number;
3931 v_or_instance_id2 number;
3932 v_or_instance_id number;
3933 v_result_code varchar2(30);
3934 v_level_error NUMBER := 0;
3935 v_result_column varchar2(30);
3936 v_fyi_flag varchar2(1);
3937 v_action_id number;
3938 v_result_code2 varchar2(30);
3939 v_result_type varchar2(30);
3940
3941 Begin
3942
3943 -- dbms_output.enable('99999999999');
3944 v_error_flag := 'N';
3945 v_process_name := 'UPG_PN_'||p_item_type||'_'||p_line_type||'_'||to_char(p_cycle_id);
3946 -- dbms_output.put_line('..1');
3947 for c2 in c1 loop
3948 -- dbms_output.put_line('..2');
3949 v_notn_instance_id := c2.instance_id;
3950
3951 v_activity_name := 'UPG_AN_PNOT_'||to_char(c2.instance_id);
3952
3953 begin
3954 select
3955 result_column,
3956 action_id,
3957 'UPG_RT_'||to_char(sa.action_id)
3958 into
3959 v_result_column,
3960 v_action_id,
3961 v_result_Type
3962 from
3963 so_actions sa
3964 where sa.action_id = to_number(substr(c2.name,8,10))
3965 and substr(c2.name,8,10) between '0000000000' and '9999999999';
3966 exception
3967 when others then
3968 v_result_column := NULL;
3969 v_action_id := NULL;
3970 end;
3971
3972 begin
3973 select 'N' into v_fyi_Flag from so_action_results
3974 where action_id = v_action_id
3975 and rownum = 1;
3976 exception
3977 when no_data_found then
3978 v_fyi_flag := 'Y';
3979 end;
3980
3981 -- dbms_output.put_line('..3');
3982
3983 wf_load.upload_activity (
3984 x_item_type => P_item_type,
3985 x_name => v_activity_name,
3986 x_display_name => v_activity_name,
3987 x_description => NULL,
3988 x_type => 'FUNCTION',
3989 x_rerun => 'RESET',
3990 x_protect_level => 20,
3991 x_custom_level => 20,
3992 x_effective_date => sysdate,
3993 x_function => 'OE_WF_UPGRADE_UTIL.UPGRADE_PRE_APPROVAL',
3994 x_function_type => null,
3995 x_result_type => v_result_type,
3996 x_cost => 0,
3997 x_read_role => null,
3998 x_write_role => null,
3999 x_execute_role => null,
4000 x_icon_name => 'FUNCTION.ICO',
4001 x_message => null,
4002 x_error_process => 'RETRY_ONLY',
4003 x_expand_role => 'N',
4004 x_error_item_type => 'WFERROR',
4005 x_runnable_flag => 'N',
4006 x_version => v_version,
4007 x_level_error => v_api_error_code
4008 );
4009
4010 -- dbms_output.put_line('013');
4011 WF_LOAD.UPLOAD_ACTIVITY_ATTRIBUTE (
4012 x_activity_item_type => P_item_type,
4016 x_display_name => v_activity_name,
4013 x_activity_name => v_activity_name,
4014 x_activity_version => 1,
4015 x_name => 'S_COLUMN',
4017 x_description => v_activity_name,
4018 x_sequence => 0,
4019 x_type => 'VARCHAR2',
4020 x_protect_level => 20,
4021 x_custom_level => 20,
4022 x_subtype => 'SEND',
4023 x_format => '',
4024 x_default => v_result_column,
4025 x_value_type => 'CONSTANT',
4026 x_level_error => v_api_error_code
4027 ) ;
4028
4029 -- dbms_output.put_line('023');
4030 wf_load.upload_lookup
4031 (
4032 x_lookup_type => v_result_type,
4033 x_lookup_code => 'NOT_PROCESSED',
4034 x_meaning => 'NOT_PROCESSED_NOTN_'||c2.name,
4035 x_description => 'Not processed - for Notification',
4036 x_protect_level => 20,
4037 x_custom_level => 20,
4038 x_level_error => v_api_error_code
4039 );
4040
4041 -- dbms_output.put_line('033');
4042 select
4043 wf_process_activities_s.nextval into v_instance_id
4044 from dual;
4045
4046 -- dbms_output.put_line('043');
4047 v_api_error_code := 0;
4048
4049 -- dbms_output.put_line('Stage 2');
4050
4051 WF_LOAD.UPLOAD_PROCESS_ACTIVITY (
4052 x_process_item_type => p_item_type,
4053 x_process_name => v_process_name,
4054 x_process_version => 1,
4055 x_activity_item_type => p_item_type,
4056 x_activity_name => v_activity_name,
4057 x_instance_id => v_instance_id,
4058 x_instance_label => v_activity_name,
4059 x_protect_level => 20,
4060 x_custom_level => 20,
4061 x_start_end => null,
4062 x_default_result => null,
4063 x_icon_geometry => '0,0',
4064 x_perform_role => null,
4065 x_perform_role_type => 'CONSTANT',
4066 x_user_comment => null,
4067 x_level_error => v_api_error_code
4068 );
4069
4070
4071 -- dbms_output.put_line('053');
4072 WF_LOAD.UPLOAD_ACTIVITY_ATTR_VALUE (
4073 x_process_activity_id => v_instance_id,
4074 x_name => 'S_COLUMN',
4075 x_protect_level => 20,
4076 x_custom_level => 20,
4077 x_value => v_result_column,
4078 x_value_type => 'CONSTANT',
4079 x_effective_date => sysdate,
4080 x_level_error => v_api_error_code
4081 );
4082
4083
4084 -- dbms_output.put_line('063');
4085 /* Insert process activity OR - */
4086
4087 select
4088 wf_process_activities_s.nextval into v_or_instance_id
4089 from dual;
4090
4091 -- dbms_output.put_line('073');
4092 v_activity_name := 'OR';
4093
4094 -- dbms_output.put_line('Stage 2');
4095
4096 WF_LOAD.UPLOAD_PROCESS_ACTIVITY (
4097 x_process_item_type => p_item_type,
4098 x_process_name => v_process_name,
4099 x_process_version => 1,
4100 x_activity_item_type => 'WFSTD',
4101 x_activity_name => v_activity_name,
4102 x_instance_id => v_or_instance_id,
4103 x_instance_label => v_activity_name||'_'||to_char(v_or_instance_id),
4104 x_protect_level => 20,
4105 x_custom_level => 20,
4106 x_start_end => null,
4107 x_default_result => null,
4108 x_icon_geometry => '0,0',
4109 x_perform_role => null,
4110 x_perform_role_type => 'CONSTANT',
4111 x_user_comment => null,
4112 x_level_error => v_api_error_code
4113 );
4114
4115
4116
4117 -- dbms_output.put_line('083');
4118 /* Bring from instance id, Result code from transitions */
4119 /* where to = notn.instance id */
4120
4121 for c6 in c5 loop
4122 -- dbms_output.put_line('093');
4126 c6.from_process_activity,
4123 /* Create transition between A to PreNotfn. */
4124 oe_upgrade_wf2.insert_into_wf_table
4125 (
4127 v_instance_id,
4128 c6.result_code,
4129 v_level_error
4130 );
4131
4132 /* Delete the transition between A and Notfn. */
4133
4134 wf_load.delete_transition (
4135 p_previous_step => c6.from_process_activity,
4136 p_next_step => c2.instance_id,
4137 p_result_code => v_result_code
4138 );
4139 end loop;
4140
4141
4142 for c4 in c3 loop
4143 /* for the same result_code from the PreNot and the Not, go to the OR-n */
4144 select wf_process_activities_s.nextval into v_or_instance_id2
4145 from dual;
4146
4147 v_activity_name := 'OR';
4148
4149 WF_LOAD.UPLOAD_PROCESS_ACTIVITY (
4150 x_process_item_type => p_item_type,
4151 x_process_name => v_process_name,
4152 x_process_version => 1,
4153 x_activity_item_type => 'WFSTD',
4154 x_activity_name => v_activity_name,
4155 x_instance_id => v_or_instance_id2,
4156 x_instance_label => v_activity_name||'_'||to_char(v_or_instance_id2),
4157 x_protect_level => 20,
4158 x_custom_level => 20,
4159 x_start_end => null,
4160 x_default_result => null,
4161 x_icon_geometry => '0,0',
4162 x_perform_role => null,
4163 x_perform_role_type => 'CONSTANT',
4164 x_user_comment => null,
4165 x_level_error => v_api_error_code
4166 );
4167 /* update notification to point to the OR just created */
4168 update wf_activity_transitions
4169 set to_process_activity = v_or_instance_id2
4170 where from_process_activity = v_notn_instance_id
4171 and to_process_activity = c4.to_process_activity;
4172
4173 /* point the PreNotification to this Or as well */
4174 oe_upgrade_wf2.insert_into_wf_table
4175 (
4176 v_instance_id,
4177 v_or_instance_id2,
4178 c4.result_code,
4179 v_level_error
4180 );
4181
4182 /* From Or-2 to B(s) */
4183 oe_upgrade_wf2.insert_into_wf_table
4184 (
4185 v_or_instance_id2,
4186 c4.to_process_activity,
4187 '*',
4188 v_level_error
4189 );
4190
4191 -- dbms_output.put_line('163');
4192 end loop;
4193 -- dbms_output.put_line('113');
4194 /* Create transition between Pre Notfn. to Or */
4195
4196 oe_upgrade_wf2.insert_into_wf_table
4197 (
4198 v_instance_id,
4199 v_or_instance_id,
4200 '*',
4201 v_level_error
4202 );
4203
4204
4205 -- dbms_output.put_line('123');
4206 /* Create transition between OR to Notification */
4207
4208 oe_upgrade_wf2.insert_into_wf_table
4209 (
4210 v_or_instance_id,
4211 c2.instance_id,
4212 '*',
4213 v_level_error
4214 );
4215
4216
4217 -- dbms_output.put_line('133');
4218 /* Create transition between Notification to OR for Not-processed code */
4219
4220 if v_fyi_Flag = 'N' then
4221 oe_upgrade_wf2.insert_into_wf_table
4222 (
4223 c2.instance_id,
4224 v_or_instance_id,
4225 'NOT_PROCESSED',
4226 v_level_error
4227 );
4228 end if;
4229
4230
4231 -- dbms_output.put_line('143');
4232 /* Bring TO instance id from transitions where From = Notification instance id */
4233 /* For each record, Create transition from Pre-Notfn. to TO brought */
4234
4235 end loop;
4236
4237 -- dbms_output.put_line('173');
4238 Exception
4239 when others then
4240 --dbms_output.put_line('create_notification exception');
4241 v_error_flag := 'Y';
4242 End Create_Notification;
4243
4244 END OE_UPGRADE_WF2;