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