1 PACKAGE BODY BOM_CALC_CYNP AS
2 /* $Header: bomcynpb.pls 120.3.12000000.2 2007/04/13 12:33:57 deegupta ship $ */
3
4 -- Declare functions/procedures
5 FUNCTION on_primary_path(
6 p_str_op_seq_id IN NUMBER
7 ) RETURN BOOLEAN;
8
9 PROCEDURE collect_ops_between_rework (
10 start_op_ptr IN NUMBER
11 ,end_op_ptr IN NUMBER
12 ,dummy_pct IN NUMBER
13 );
14 procedure collect_total_rework_prob(
15 start_op_ptr IN NUMBER
16 ,end_op_ptr IN NUMBER
17 ,dummy_pct IN NUMBER
18 );
19 PROCEDURE calc_npp_sanity_when_rework;
20
21 PROCEDURE calc_net_planning_pct_rework (
22 dummy_plan_percent IN NUMBER
23 );
24
25 FUNCTION find_op (
26 p_op_seq_id IN NUMBER
27 ) RETURN NUMBER;
28
29 FUNCTION get_fed_rework_pct(
30 to_ptr IN NUMBER
31 )RETURN BOOLEAN;
32
33 PROCEDURE calc_net_planning_pct (
34 from_ptr IN NUMBER,
35 to_ptr IN NUMBER,
36 rwk_pln_pct IN NUMBER
37 );
38
39 PROCEDURE calc_cum_yld (
40 op_ptr IN NUMBER
41 );
42
43 PROCEDURE calc_rev_cum_yld (
44 op_ptr IN NUMBER
45 );
46
47 FUNCTION is_dummy(
48 p_index IN NUMBER
49 ) RETURN BOOLEAN;
50
51 FUNCTION calc_dummy_net_planning_pct (
52 op_id IN NUMBER
53 ) RETURN NUMBER;
54
55 PROCEDURE calc_net_plan_pct(sanity_counter IN NUMBER);
56 PROCEDURE calc_net_plan_pct_sanity ( op_seq_id IN NUMBER);
57
58 PROCEDURE calc_primary_network (
59 p_routing_sequence_id IN NUMBER,
60 p_operation_type IN VARCHAR2,
61 p_update_events IN NUMBER
62 );
63
64 PROCEDURE calc_feeder_network (
65 p_routing_sequence_id IN NUMBER,
66 p_operation_type IN VARCHAR2,
67 p_update_events IN NUMBER,
68 p_ind IN NUMBER
69 );
70
71 FUNCTION find_in_main_tbl(
72 p_op_seq_id IN NUMBER
73 ) RETURN NUMBER;
74
75 FUNCTION isCyclical(
76 p_op_seq_id IN NUMBER,
77 p_ind IN NUMBER
78 ) RETURN BOOLEAN;
79
80 g_Debug_File UTL_FILE.FILE_TYPE;
81
82
83 -- Declare variables
84 v_tab_size NUMBER;
85 Rework_Effect_index NUMBER;
86
87 -- Declare PL/SQL records
88 TYPE Op_Rec_Type IS RECORD
89 ( operation_seq_id NUMBER,
90 operation_seq_num NUMBER,
91 yield NUMBER);
92
93 TYPE rework_effect_rec IS RECORD
94 ( operation_seq_id NUMBER,
95 operation_seq_num NUMBER
96 );
97 TYPE Op_Detail_Rec_Type IS RECORD
98 ( operation_seq_id NUMBER,
99 operation_seq_num NUMBER,
100 net_planning_pct NUMBER,
101 yield_nppct NUMBER,
102 rework_loop_flag NUMBER,
103 rework_effect_end_flag NUMBER,
104 cumulative_rwk_pct NUMBER,
105 cumulative_yield NUMBER,
106 is_dummy NUMBER,
107 mark_for_rework_feed NUMBER,
108 rev_cumulative_yield NUMBER );
109
110 -- Declare PL/SQL tables
111 TYPE numTabTyp IS TABLE OF NUMBER
112 INDEX BY BINARY_INTEGER;
113
114 TYPE Op_Tbl_Type IS TABLE OF Op_Rec_Type
115 INDEX BY BINARY_INTEGER;
116
117 TYPE Op_Detail_Tbl_Type IS TABLE OF Op_Detail_Rec_Type
118 INDEX BY BINARY_INTEGER;
119
120 TYPE Op_Rework_Effect_Type IS TABLE OF rework_effect_rec
121 INDEX BY BINARY_INTEGER;
122
123 op_tab numTabTyp;
124 yld_tab numTabTyp;
125 pln_tab numTabTyp;
126 odf_tab numTabTyp;
127 cumyld_tab numTabTyp;
128 revcumyld_tab numTabTyp;
129 netpln_tab numTabTyp;
130
131 start_tbl Op_Tbl_Type;
132 temp_tbl Op_Tbl_Type;
133 ntwk_op_tbl Op_Detail_Tbl_Type;
134 prim_path_tbl numTabTyp;
135 temp_op_detail_rec Op_Detail_Rec_Type;
136
137 main_ntwk_op_tbl Op_Detail_Tbl_Type;
138 main_cnt NUMBER := 1;
139
140 rework_effect_tbl Op_Rework_Effect_Type;
141 g_total_rework_prob NUMBER := 0;
142 reworks_found boolean := FALSE;
143
144 temp_op_tbl numTabTyp;
145 temp_tbl_cnt number;
146
147 visited numTabTyp;
148 g_rtg_seq_id NUMBER;
149 g_op_type NUMBER;
150 -- g_cfm_flag NUMBER; -- added for bug 2739224 to differentiate Flow and OSFM routings
151 rwrk_found BOOLEAN := FALSE;
152 token NUMBER;
153 --err_msg VARCHAR2(2000);
154
155 --Declare exceptions
156 MULTIPLE_JUNCTION_OP EXCEPTION;
157 PRAGMA exception_init(MULTIPLE_JUNCTION_OP, -20001);
158
159 CYCLICAL_EXCEPTION EXCEPTION;
160 PRAGMA exception_init(CYCLICAL_EXCEPTION, -20002);
161
162 REWORK_FORWARD EXCEPTION;
163 PRAGMA exception_init(REWORK_FORWARD, -20003);
164
165 NO_START_OP EXCEPTION;
166 PRAGMA exception_init(NO_START_OP, -20004);
167
168 MULTIPLE_ENTRY_DUMMY EXCEPTION;
169 PRAGMA exception_init(MULTIPLE_ENTRY_DUMMY, -20005);
170
171 NO_PRIMARY_LINK EXCEPTION;
172 PRAGMA exception_init(NO_PRIMARY_LINK, -20006);
173
174 PLANNING_PCT_SUM_ERROR EXCEPTION;
175 PRAGMA exception_init(PLANNING_PCT_SUM_ERROR, -20009);
176
177 NO_NWK_DEFINED_ERROR EXCEPTION; -- BUG 4348554
178 PRAGMA exception_init(NO_NWK_DEFINED_ERROR, -20010);
179
180 PROCEDURE Open_Debug_Session
181 IS
182 l_found NUMBER := 0;
183 l_utl_file_dir VARCHAR2(2000);
184 p_output_dir VARCHAR2(80) := '/sqlcom/log/tst115rw' ;
185 p_debug_filename VARCHAR2(30) := 'bom_cynpp.log' ;
186
187 BEGIN
188
189 select value
190 INTO l_utl_file_dir
191 FROM v$parameter
192 WHERE name = 'utl_file_dir';
193
194 l_found := INSTR(l_utl_file_dir, p_output_dir);
195
196
197 IF l_found = 0
198 THEN
199 RETURN;
200 END IF;
201
202 g_Debug_File := utl_file.fopen( p_output_dir
203 , p_debug_filename
204 , 'w');
205
206 END ;
207
208 -- Close Debug_Session
209 PROCEDURE Close_Debug_Session
210 IS
211 BEGIN
212 utl_file.fclose(g_Debug_File);
213 END Close_Debug_Session;
214
215
216 -- Test Debug
217 PROCEDURE Write_Debug
218 ( p_debug_message IN VARCHAR2 )
219 IS
220 BEGIN
221
222 utl_file.put_line(g_Debug_File, p_debug_message);
223
224 END Write_Debug;
225
226 PROCEDURE copy_ntwk_op_tbl
227 IS
228 cnt NUMBER;
229 BEGIN
230 FOR cnt IN 1..ntwk_op_tbl.COUNT LOOP
231 main_ntwk_op_tbl(main_cnt).operation_seq_id := ntwk_op_tbl(cnt).operation_seq_id;
232 main_ntwk_op_tbl(main_cnt).operation_seq_num := ntwk_op_tbl(cnt).operation_seq_num;
233 main_ntwk_op_tbl(main_cnt).net_planning_pct := ntwk_op_tbl(cnt).net_planning_pct;
234 main_ntwk_op_tbl(main_cnt).yield_nppct := ntwk_op_tbl(cnt).yield_nppct;
235 main_ntwk_op_tbl(main_cnt).rework_loop_flag := ntwk_op_tbl(cnt).rework_loop_flag;
236 main_ntwk_op_tbl(main_cnt).rework_effect_end_flag := ntwk_op_tbl(cnt).rework_effect_end_flag;
237 main_ntwk_op_tbl(main_cnt).cumulative_rwk_pct := ntwk_op_tbl(cnt).cumulative_rwk_pct;
238 main_ntwk_op_tbl(main_cnt).cumulative_yield := ntwk_op_tbl(cnt).cumulative_yield;
239 main_ntwk_op_tbl(main_cnt).is_dummy := ntwk_op_tbl(cnt).is_dummy;
240 main_ntwk_op_tbl(main_cnt).mark_for_rework_feed := ntwk_op_tbl(cnt).mark_for_rework_feed;
241 main_ntwk_op_tbl(main_cnt).rev_cumulative_yield := ntwk_op_tbl(cnt).rev_cumulative_yield;
242
243 main_cnt := main_cnt + 1;
244 END LOOP;
245 END copy_ntwk_op_tbl;
246
247 -- Added for RBO support for NPP. This procedure is exactly similar to the calc_cynp procedure
248 -- These two procedures need to be always in sync and should both be tested together and always.
249 -- Bug 2689249
250 PROCEDURE calc_cynp_rbo (
251 p_routing_sequence_id IN NUMBER,
252 p_operation_type IN VARCHAR2,
253 p_update_events IN NUMBER,
254 x_token_tbl OUT NOCOPY Error_Handler.Token_Tbl_Type,
255 x_err_msg OUT NOCOPY VARCHAR2,
256 x_return_status OUT NOCOPY VARCHAR2
257 ) IS
258 -- Select all the operations in the routing that do NOT have a FROM
259 -- operation. These are valid starting points for multiple paths (i.e.
260 -- it includes feeder lines)
261 /* CURSOR start_ops_cur IS
262 SELECT DISTINCT from_op_seq_id start_op_seq_id,
263 from_seq_num start_op_seq_num,
264 nvl(yield, 1) start_op_yield
265 FROM bom_operation_networks_v bonv,
266 bom_operation_sequences bos
267 WHERE bonv.routing_sequence_id = p_routing_sequence_id
268 AND bonv.operation_type = p_operation_type
269 AND bonv.transition_type <> 3
270 AND bonv.from_op_seq_id = bos.operation_sequence_id
271 AND NOT EXISTS (SELECT NULL
272 FROM bom_operation_networks net
273 WHERE net.to_op_seq_id = bonv.from_op_seq_id
274 AND net.transition_type <> 3)
275 ORDER BY from_seq_num;*/
276 -- BUG 4506235
277 CURSOR start_ops_cur IS
278 SELECT DISTINCT from_op_seq_id start_op_seq_id,
279 from_seq_num start_op_seq_num,
280 DECODE(borv.cfm_routing_flag, 3, (DECODE(bos.operation_yield_enabled, 1, NVL(bos.yield, 1), 1)), NVL(bos.yield, 1)) start_op_yield
281 FROM bom_operation_networks_v bonv,
282 bom_operation_sequences bos,
283 bom_operational_routings_v borv
284 WHERE bonv.routing_sequence_id = borv.routing_Sequence_id
285 AND bonv.routing_sequence_id = p_routing_sequence_id
286 AND bonv.operation_type = p_operation_type
287 AND bonv.transition_type <> 3
288 AND bonv.from_op_seq_id = bos.operation_sequence_id
289 AND NOT EXISTS (SELECT NULL
290 FROM bom_operation_networks net
291 WHERE net.to_op_seq_id = bonv.from_op_seq_id
292 AND net.transition_type <> 3)
293 ORDER BY from_seq_num;
294
295 CURSOR check_nwk_links_cur IS
296 SELECT null FROM DUAL
297 WHERE exists
298 ( SELECT null FROM bom_operation_networks_v bonv
299 WHERE bonv.routing_sequence_id = p_routing_sequence_id
300 AND bonv.operation_type = p_operation_type
301 AND bonv.transition_type <> 3 );
302 /****
303 CURSOR set_cfm_cur IS
304 SELECT cfm_routing_flag
305 FROM BOM_OPERATIONAL_ROUTINGS bor
306 WHERE bor.routing_sequence_id = p_routing_sequence_id;
307 ****/
308
309 CURSOR check_nwk_exists_cur( p_rtg_seq_id NUMBER ) IS -- BUG 4348554
310 SELECT COUNT(*) FROM bom_operation_networks
311 WHERE from_op_seq_id IN
312 ( SELECT operation_sequence_id FROM bom_operation_sequences
313 WHERE routing_sequence_id = p_rtg_seq_id )
314 OR to_op_SEQ_ID IN
315 ( SELECT operation_sequence_id FROM bom_operation_sequences
316 WHERE routing_sequence_id = p_rtg_seq_id );
317
318 CURSOR get_cfm_flag_cur( p_rtg_seq_id NUMBER ) IS -- BUG 4348554
319 SELECT nvl(cfm_routing_flag, 2)
320 FROM bom_operational_routings
321 WHERE routing_sequence_id = p_rtg_seq_id;
322
323 i NUMBER;
324 l_yield NUMBER;
325 succ_start BOOLEAN := FALSE;
326 -- l_token_tbl Error_Handler.Token_Tbl_Type
327 -- Primary path - traverse the network to select all the 'to' operations
328 -- until the end using the primary path
329 l_chk_new_exists NUMBER;
330 l_cfm_flag NUMBER;
331
332 BEGIN
333 --Open_Debug_Session;
334 start_tbl.DELETE;
335 ntwk_op_tbl.DELETE;
336 main_ntwk_op_tbl.DELETE;
337 prim_path_tbl.DELETE;
338 rework_effect_tbl.DELETE;
339 main_cnt := 1;
340 /****
341 FOR set_cfm_rec IN set_cfm_cur LOOP -- Added for bug 2739224
342 g_cfm_flag := nvl(set_cfm_rec.cfm_routing_flag, 2);
343 END LOOP;
344 ****/
345
346 OPEN get_cfm_flag_cur( p_routing_sequence_id ); -- BUG 4348554
347 FETCH get_cfm_flag_cur INTO l_cfm_flag;
348 CLOSE get_cfm_flag_cur;
349 IF l_cfm_flag = 3 THEN
350 OPEN check_nwk_exists_cur( p_routing_sequence_id );
351 FETCH check_nwk_exists_cur INTO l_chk_new_exists;
352 CLOSE check_nwk_exists_cur;
353 IF l_chk_new_exists = 0 THEN
354 RAISE NO_NWK_DEFINED_ERROR;
355 END IF;
356 /*
357 ELSIF l_cfm_flag = 1 THEN
358 OPEN check_nwk_exists_cur( p_routing_sequence_id );
359 FETCH check_nwk_exists_cur INTO l_chk_new_exists;
360 CLOSE check_nwk_exists_cur;
361 IF l_chk_new_exists = 0 THEN
362 RAISE NO_NWK_DEFINED_ERROR;
363 END IF;
364 */
365 END IF;
366
367 Rework_Effect_index := 0;--resetting the global index for rework collections
368 -- Fetch all the starting opns, save the min op seq num as the
369 -- starting point for the main line
370 i := 1;
371 FOR start_ops_rec IN start_ops_cur LOOP
372 start_tbl(i).operation_seq_id := start_ops_rec.start_op_seq_id;
373 start_tbl(i).operation_seq_num := start_ops_rec.start_op_seq_num;
374 start_tbl(i).yield := start_ops_rec.start_op_yield;
375 i := i + 1;
376 END LOOP;
377
378 IF start_tbl.COUNT = 0 THEN
379 FOR C1 IN check_nwk_links_cur LOOP
380 raise NO_START_OP;
381 END LOOP;
382 RETURN;
383 END IF;
384 succ_start := TRUE;
385 calc_primary_network(p_routing_sequence_id,p_operation_type,p_update_events);
386 copy_ntwk_op_tbl();
387 FOR i in 2..start_tbl.count
388 LOOP
389 ntwk_op_tbl.DELETE;
390 prim_path_tbl.DELETE;
391 rework_effect_tbl.DELETE;
392 Rework_Effect_index := 0;
393 reworks_found := false;
394 calc_feeder_network(p_routing_sequence_id,p_operation_type,p_update_events, i);
395 copy_ntwk_op_tbl();
396 END LOOP;
397
398 -- might need to truncate tables for re-use
399 start_tbl.DELETE;
400 ntwk_op_tbl.DELETE;
401 main_ntwk_op_tbl.DELETE;
402 prim_path_tbl.DELETE;
403 rework_effect_tbl.DELETE;
404
405 --Close_Debug_session;
406 EXCEPTION
407 WHEN MULTIPLE_JUNCTION_OP THEN
408 x_token_tbl(1).token_name := 'SEQ_NUM';
409 x_token_tbl(1).token_value := token;
410 x_err_msg := 'BOM_MULT_JUNC_OP';
411 x_return_status := 'E';
412 WHEN CYCLICAL_EXCEPTION THEN
413 x_token_tbl(1).token_name := 'SEQ_NUM';
414 x_token_tbl(1).token_value := token;
415 x_err_msg := 'BOM_LOOP_FOUND';
416 x_return_status := 'E';
417 WHEN REWORK_FORWARD THEN
418 x_token_tbl(1).token_name := 'SEQ_NUM';
419 x_token_tbl(1).token_value := token;
420 x_err_msg := 'BOM_RWRK_LOOP_FOUND';
421 x_return_status := 'E';
422 WHEN NO_START_OP THEN
423 x_err_msg := 'BOM_NO_START_OP';
424 x_return_status := 'E';
425 WHEN MULTIPLE_ENTRY_DUMMY THEN
426 x_token_tbl(1).token_name := 'SEQ_NUM';
427 x_token_tbl(1).token_value := token;
428 x_err_msg := 'BOM_MULT_ENTRY_DUMMY';
429 x_return_status := 'E';
430 WHEN NO_PRIMARY_LINK THEN
431 x_token_tbl(1).token_name := 'SEQ_NUM';
432 x_token_tbl(1).token_value := token;
433 x_err_msg := 'BOM_NO_PRIMARY_LINK';
434 x_return_status := 'E';
435 WHEN PLANNING_PCT_SUM_ERROR THEN
439 x_return_status := 'E';
436 x_token_tbl(1).token_name := 'SEQ_NUM';
437 x_token_tbl(1).token_value := token;
438 x_err_msg := 'BOM_PLANNING_PCT_SUM_ERROR';
440 WHEN NO_NWK_DEFINED_ERROR THEN -- BUG 4348554
441 x_err_msg := 'BOM_NO_RTG_NWK_DEF_ERROR';
442 x_return_status := 'E';
443 WHEN OTHERS THEN
444 IF succ_start THEN
445 x_err_msg := 'BOM_UNKNOWN_ERROR';
446 x_return_status := 'E';
447 ELSE
448 x_token_tbl(1).token_name := 'SEQ_NUM';
449 x_token_tbl(1).token_value := start_tbl(start_tbl.COUNT).operation_seq_num;
450 x_err_msg := 'BOM_LOOP_FOUND';
451 x_return_status := 'E';
452 END IF;
453
454 END calc_cynp_rbo;
455
456 PROCEDURE calc_cynp (
457 p_routing_sequence_id IN NUMBER,
458 p_operation_type IN VARCHAR2,
459 p_update_events IN NUMBER
460 ) IS
461 -- Select all the operations in the routing that do NOT have a FROM
462 -- operation. These are valid starting points for multiple paths (i.e.
463 -- it includes feeder lines)
464 /* CURSOR start_ops_cur IS
465 SELECT DISTINCT from_op_seq_id start_op_seq_id,
466 from_seq_num start_op_seq_num,
467 nvl(yield, 1) start_op_yield
468 FROM bom_operation_networks_v bonv,
469 bom_operation_sequences bos
470 WHERE bonv.routing_sequence_id = p_routing_sequence_id
471 AND bonv.operation_type = p_operation_type
472 AND bonv.transition_type <> 3
473 AND bonv.from_op_seq_id = bos.operation_sequence_id
474 AND NOT EXISTS (SELECT NULL
475 FROM bom_operation_networks net
476 WHERE net.to_op_seq_id = bonv.from_op_seq_id
477 AND net.transition_type <> 3)
478 ORDER BY from_seq_num;*/
479 -- BUG 4506235
480 CURSOR start_ops_cur IS
481 SELECT DISTINCT from_op_seq_id start_op_seq_id,
482 from_seq_num start_op_seq_num,
483 DECODE(borv.cfm_routing_flag, 3, (DECODE(bos.operation_yield_enabled, 1, NVL(bos.yield, 1), 1)), NVL(bos.yield, 1)) start_op_yield
484 FROM bom_operation_networks_v bonv,
485 bom_operation_sequences bos,
486 bom_operational_routings_v borv
487 WHERE bonv.routing_sequence_id = borv.routing_Sequence_id
488 AND bonv.routing_sequence_id = p_routing_sequence_id
489 AND bonv.operation_type = p_operation_type
490 AND bonv.transition_type <> 3
491 AND bonv.from_op_seq_id = bos.operation_sequence_id
492 AND NOT EXISTS (SELECT NULL
493 FROM bom_operation_networks net
494 WHERE net.to_op_seq_id = bonv.from_op_seq_id
495 AND net.transition_type <> 3)
496 ORDER BY from_seq_num;
497
498 CURSOR check_nwk_links_cur IS
499 SELECT null FROM DUAL
500 WHERE exists
501 ( SELECT null FROM bom_operation_networks_v bonv
502 WHERE bonv.routing_sequence_id = p_routing_sequence_id
503 AND bonv.operation_type = p_operation_type
504 AND bonv.transition_type <> 3 );
505
506 CURSOR check_nwk_exists_cur( p_rtg_seq_id NUMBER ) IS -- BUG 4348554
507 SELECT COUNT(*) FROM bom_operation_networks
508 WHERE from_op_seq_id IN
509 ( SELECT operation_sequence_id FROM bom_operation_sequences
510 WHERE routing_sequence_id = p_rtg_seq_id )
511 OR to_op_SEQ_ID IN
512 ( SELECT operation_sequence_id FROM bom_operation_sequences
513 WHERE routing_sequence_id = p_rtg_seq_id );
514
515 CURSOR get_cfm_flag_cur( p_rtg_seq_id NUMBER ) IS -- BUG 4348554
516 SELECT nvl(cfm_routing_flag, 2)
517 FROM bom_operational_routings
518 WHERE routing_sequence_id = p_rtg_seq_id;
519
520
521 /****
522 CURSOR set_cfm_cur IS
523 SELECT cfm_routing_flag
524 FROM BOM_OPERATIONAL_ROUTINGS bor
525 WHERE bor.routing_sequence_id = p_routing_sequence_id;
526 ****/
527 i NUMBER;
528 l_yield NUMBER;
529 succ_start BOOLEAN := FALSE;
530 -- Primary path - traverse the network to select all the 'to' operations
531 -- until the end using the primary path
532 l_chk_new_exists NUMBER;
533 l_cfm_flag NUMBER;
534
535 BEGIN
536 --Open_Debug_Session;
537 start_tbl.DELETE;
538 ntwk_op_tbl.DELETE;
539 main_ntwk_op_tbl.DELETE;
540 prim_path_tbl.DELETE;
541 rework_effect_tbl.DELETE;
542 main_cnt := 1;
543
544 /***
545 To determine whether a routing is flow or OSFM can be decided using the operation_type
546 Operation type is 2 for process ops and 3 for line ops in flow routings
547 Operation type is 1 in OSFM routings
548 ***
549 FOR set_cfm_rec IN set_cfm_cur LOOP -- Added for bug 2739224
550 g_cfm_flag := nvl(set_cfm_rec.cfm_routing_flag, 2);
551 END LOOP;
552 ***/
553
554 OPEN get_cfm_flag_cur( p_routing_sequence_id ); -- BUG 4348554
555 FETCH get_cfm_flag_cur INTO l_cfm_flag;
556 CLOSE get_cfm_flag_cur;
557 IF l_cfm_flag = 3 THEN
558 OPEN check_nwk_exists_cur( p_routing_sequence_id );
559 FETCH check_nwk_exists_cur INTO l_chk_new_exists;
560 CLOSE check_nwk_exists_cur;
561 IF l_chk_new_exists = 0 THEN
562 RAISE NO_NWK_DEFINED_ERROR;
563 END IF;
564 /*
565 ELSIF l_cfm_flag = 1 THEN
566 OPEN check_nwk_exists_cur( p_routing_sequence_id );
570 RAISE NO_NWK_DEFINED_ERROR;
567 FETCH check_nwk_exists_cur INTO l_chk_new_exists;
568 CLOSE check_nwk_exists_cur;
569 IF l_chk_new_exists = 0 THEN
571 END IF;*/
572 END IF;
573
574 Rework_Effect_index := 0;--resetting the global index for rework collections
575 -- Fetch all the starting opns, save the min op seq num as the
576 -- starting point for the main line
577 i := 1;
578 FOR start_ops_rec IN start_ops_cur LOOP
579 start_tbl(i).operation_seq_id := start_ops_rec.start_op_seq_id;
580 start_tbl(i).operation_seq_num := start_ops_rec.start_op_seq_num;
581 start_tbl(i).yield := start_ops_rec.start_op_yield;
582 i := i + 1;
583 END LOOP;
584
585 IF start_tbl.COUNT = 0 THEN
586 FOR C1 IN check_nwk_links_cur LOOP
587 raise NO_START_OP;
588 END LOOP;
589 RETURN;
590 END IF;
591 succ_start := TRUE;
592
593 calc_primary_network(p_routing_sequence_id,p_operation_type,p_update_events);
594 copy_ntwk_op_tbl();
595 FOR i in 2..start_tbl.count
596 LOOP
597 ntwk_op_tbl.DELETE;
598 prim_path_tbl.DELETE;
599 rework_effect_tbl.DELETE;
600 Rework_Effect_index := 0;
601 reworks_found := false;
602 calc_feeder_network(p_routing_sequence_id,p_operation_type,p_update_events, i);
603 copy_ntwk_op_tbl();
604 END LOOP;
605
606 -- might need to truncate tables for re-use
607 start_tbl.DELETE;
608 ntwk_op_tbl.DELETE;
609 main_ntwk_op_tbl.DELETE;
610 prim_path_tbl.DELETE;
611 rework_effect_tbl.DELETE;
612
613 --Close_Debug_session;
614 EXCEPTION
615 WHEN MULTIPLE_JUNCTION_OP THEN
616 fnd_message.set_name('BOM','BOM_MULT_JUNC_OP');
617 fnd_message.set_token('SEQ_NUM',to_char(token));
618 fnd_message.raise_error;
619 -- err_msg := fnd_message.get;
620 -- err_msg := 'Feeder subnetwork starting with operation sequence number '||to_char(token)||' joins another sub-network in more than one place. Please correct this and try again.';
621 -- raise_application_error(-20001,err_msg,FALSE);
622 -- dbms_output.put_line('Feeder subnetwork starting with operation sequence number '||to_char(token)||' joins another sub-network in more than one place. Please correct this and try again.');
623 -- null;
624 WHEN CYCLICAL_EXCEPTION THEN
625 fnd_message.set_name('BOM','BOM_LOOP_FOUND');
626 fnd_message.set_token('SEQ_NUM',to_char(token));
627 fnd_message.raise_error;
628 -- err_msg := fnd_message.get;
629 -- raise_application_error(-20002,err_msg,FALSE);
630 -- dbms_output.put_line('All primary and alternate paths should go forward. Path terminating in operation with operation sequence number '||to_char(token)||' traverses backward. Please correct this and try again.');
631 -- null;
632 WHEN REWORK_FORWARD THEN
633 fnd_message.set_name('BOM','BOM_RWRK_LOOP_FOUND');
634 fnd_message.set_token('SEQ_NUM',to_char(token));
635 fnd_message.raise_error;
636 -- err_msg := fnd_message.get;
637 -- raise_application_error(-20003,err_msg,FALSE);
638 -- dbms_output.put_line('Some reworks are going forward in sub-network starting with operation sequence number '||to_char(token)||'. Please correct this and try again.');
639 -- null;
640 WHEN NO_START_OP THEN
641 fnd_message.set_name('BOM','BOM_NO_START_OP');
642 fnd_message.raise_error;
643 -- err_msg := fnd_message.get;
644 -- raise_application_error(-20004,err_msg,FALSE);
645 -- dbms_output.put_line('There is no start operation defined in the network');
646 -- null;
647 WHEN MULTIPLE_ENTRY_DUMMY THEN
648 fnd_message.set_name('BOM','BOM_MULT_ENTRY_DUMMY');
649 fnd_message.set_token('SEQ_NUM',to_char(token));
650 fnd_message.raise_error;
651 -- err_msg := fnd_message.get;
652 -- raise_application_error(-20005,err_msg,FALSE);
653 -- dbms_output.put_line('Two or more rework links are coming into the dummy operation with operation sequence number '||to_char(token)||'. Please correct this and try again.');
654 -- null;
655 WHEN NO_PRIMARY_LINK THEN
656 fnd_message.set_name('BOM','BOM_NO_PRIMARY_LINK');
657 fnd_message.set_token('SEQ_NUM',to_char(token));
658 fnd_message.raise_error;
659 -- err_msg := fnd_message.get;
660 -- raise_application_error(-20006,err_msg,FALSE);
661 -- dbms_output.put_line('The Operation '||to_char(token)||' must have a primary link going out of it, if it has alternate link going out of it. Please correct this and try again.');
662 -- null;
663 WHEN PLANNING_PCT_SUM_ERROR THEN
664 fnd_message.set_name('BOM','BOM_PLANNING_PCT_SUM_ERROR');
665 fnd_message.set_token('SEQ_NUM',to_char(token));
666 fnd_message.raise_error;
667 -- err_msg := fnd_message.get;
668 -- raise_application_error(-20009,err_msg,FALSE);
669 -- dbms_output.put_line('Sum of percentages of all out going primary and alternate links for operation '||to_char(token)||' must be 100. Please correct this and try again.');
670 -- null;
671 -- WHEN NO_DATA_FOUND THEN
672 -- raise_application_error(-06512,'No data found error');
673 -- dbms_output.put_line('----main no data found----');
674 -- NULL;
675 WHEN NO_NWK_DEFINED_ERROR THEN -- BUG 4348554
676 fnd_message.set_name('BOM','BOM_NO_RTG_NWK_DEF_ERROR');
677 fnd_message.raise_error;
678 WHEN OTHERS THEN
679 IF succ_start THEN
680 fnd_message.set_name('BOM','BOM_UNKNOWN_ERROR');
684 fnd_message.set_token('SEQ_NUM',to_char(start_tbl(start_tbl.COUNT).operation_seq_num));
681 fnd_message.raise_error;
682 ELSE
683 fnd_message.set_name('BOM','BOM_LOOP_FOUND');
685 fnd_message.raise_error;
686 END IF;
687 -- err_msg := fnd_message.get;
688 -- raise_application_error(-20020,err_msg,FALSE);
689 -- dbms_output.put_line('An unidentified error has occurred in the planning percent calculations. Please contact Oracle Support.');
690 END calc_cynp;
691
692 PROCEDURE updt_db(
693 p_routing_sequence_id IN NUMBER,
694 p_operation_type IN NUMBER,
695 p_update_events IN NUMBER
696 )
697 IS
698 i NUMBER;
699 BEGIN
700 for i in 1..ntwk_op_tbl.COUNT loop
701 update bom_operation_sequences
702 set cumulative_yield = ntwk_op_tbl(i).cumulative_yield,
703 reverse_cumulative_yield = ntwk_op_tbl(i).rev_cumulative_yield,
704 net_planning_percent = ntwk_op_tbl(i).net_planning_pct
705 where operation_sequence_id = ntwk_op_tbl(i).operation_seq_id;
706
707 -- Update child events based on the parameter
708 if p_update_events = 1 then
709 if p_operation_type = 2 then
710 update bom_operation_sequences
711 set cumulative_yield = ntwk_op_tbl(i).cumulative_yield,
712 reverse_cumulative_yield = ntwk_op_tbl(i).rev_cumulative_yield,
713 net_planning_percent = ntwk_op_tbl(i).net_planning_pct
714 where routing_sequence_id = p_routing_sequence_id
715 and process_op_seq_id = ntwk_op_tbl(i).operation_seq_id;
716 elsif p_operation_type = 3 then
717 update bom_operation_sequences
718 set cumulative_yield = ntwk_op_tbl(i).cumulative_yield,
719 reverse_cumulative_yield = ntwk_op_tbl(i).rev_cumulative_yield,
720 net_planning_percent = ntwk_op_tbl(i).net_planning_pct
721 where routing_sequence_id = p_routing_sequence_id
722 and line_op_seq_id = ntwk_op_tbl(i).operation_seq_id;
723 end if;
724 end if;
725 end loop;
726 END updt_db;
727
728 PROCEDURE swap_ops(i NUMBER) IS
729 l_count NUMBER;
730 BEGIN
731 temp_op_detail_rec.operation_seq_id := ntwk_op_tbl(i).operation_seq_id;
732 temp_op_detail_rec.operation_seq_num := ntwk_op_tbl(i).operation_seq_num;
733 temp_op_detail_rec.net_planning_pct := ntwk_op_tbl(i).net_planning_pct;
734 temp_op_detail_rec.yield_nppct := ntwk_op_tbl(i).yield_nppct;
735 temp_op_detail_rec.rework_loop_flag := ntwk_op_tbl(i).rework_loop_flag;
736 temp_op_detail_rec.rework_effect_end_flag := ntwk_op_tbl(i).rework_effect_end_flag;
737 temp_op_detail_rec.cumulative_rwk_pct := ntwk_op_tbl(i).cumulative_rwk_pct;
738 temp_op_detail_rec.cumulative_yield := ntwk_op_tbl(i).cumulative_yield;
739 temp_op_detail_rec.is_dummy := ntwk_op_tbl(i).is_dummy;
740 temp_op_detail_rec.mark_for_rework_feed := ntwk_op_tbl(i).mark_for_rework_feed;
741 temp_op_detail_rec.rev_cumulative_yield := ntwk_op_tbl(i).rev_cumulative_yield;
742
743 l_count := ntwk_op_tbl.COUNT;
744
745 ntwk_op_tbl(i).operation_seq_id := ntwk_op_tbl(l_count).operation_seq_id;
746 ntwk_op_tbl(i).operation_seq_num := ntwk_op_tbl(l_count).operation_seq_num;
747 ntwk_op_tbl(i).net_planning_pct := ntwk_op_tbl(l_count).net_planning_pct;
748 ntwk_op_tbl(i).yield_nppct := ntwk_op_tbl(l_count).yield_nppct;
749 ntwk_op_tbl(i).rework_loop_flag := ntwk_op_tbl(l_count).rework_loop_flag;
750 ntwk_op_tbl(i).rework_effect_end_flag := ntwk_op_tbl(l_count).rework_effect_end_flag;
751 ntwk_op_tbl(i).cumulative_rwk_pct := ntwk_op_tbl(l_count).cumulative_rwk_pct;
752 ntwk_op_tbl(i).cumulative_yield := ntwk_op_tbl(l_count).cumulative_yield;
753 ntwk_op_tbl(i).is_dummy := ntwk_op_tbl(l_count).is_dummy;
754 ntwk_op_tbl(i).mark_for_rework_feed := ntwk_op_tbl(l_count).mark_for_rework_feed;
755 ntwk_op_tbl(i).rev_cumulative_yield := ntwk_op_tbl(l_count).rev_cumulative_yield;
756
757 ntwk_op_tbl(l_count).operation_seq_id := temp_op_detail_rec.operation_seq_id;
758 ntwk_op_tbl(l_count).operation_seq_num := temp_op_detail_rec.operation_seq_num;
759 ntwk_op_tbl(l_count).net_planning_pct := temp_op_detail_rec.net_planning_pct;
760 ntwk_op_tbl(l_count).yield_nppct := temp_op_detail_rec.yield_nppct;
761 ntwk_op_tbl(l_count).rework_loop_flag := temp_op_detail_rec.rework_loop_flag;
762 ntwk_op_tbl(l_count).rework_effect_end_flag := temp_op_detail_rec.rework_effect_end_flag;
763 ntwk_op_tbl(l_count).cumulative_rwk_pct := temp_op_detail_rec.cumulative_rwk_pct;
764 ntwk_op_tbl(l_count).cumulative_yield := temp_op_detail_rec.cumulative_yield;
765 ntwk_op_tbl(l_count).is_dummy := temp_op_detail_rec.is_dummy;
766 ntwk_op_tbl(l_count).mark_for_rework_feed := temp_op_detail_rec.mark_for_rework_feed;
767 ntwk_op_tbl(l_count).rev_cumulative_yield := temp_op_detail_rec.rev_cumulative_yield;
768 END swap_ops;
769
770 PROCEDURE check_loops IS
771 i number;
772 BEGIN
773 --- To find if rework loops are going forward...
774 rwrk_found := FALSE;
775 FOR i IN 1..ntwk_op_tbl.COUNT LOOP
776 visited(i) := 0;
777 END LOOP;
778 FOR i IN 1..ntwk_op_tbl.COUNT LOOP
779 IF visited(i) = 0 THEN
780 IF isCyclical(ntwk_op_tbl(i).operation_seq_id, i) THEN
781 IF rwrk_found THEN
782 token := ntwk_op_tbl(1).operation_seq_num;
783 raise REWORK_FORWARD;
784 ELSE
788 END IF;
785 raise CYCLICAL_EXCEPTION;
786 END IF;
787 END IF;
789 END LOOP;
790 END;
791
792 FUNCTION isCyclical(p_op_seq_id NUMBER, p_ind NUMBER)
793 RETURN BOOLEAN IS
794 CURSOR next_ops_cur (cv_start_op_seq_id number) IS
795 SELECT to_op_seq_id next_op_seq_id,
796 to_seq_num next_op_seq_num,
797 transition_type
798 FROM bom_operation_networks_v bonv
799 WHERE routing_sequence_id = g_rtg_seq_id
800 AND from_op_seq_id = cv_start_op_seq_id
801 AND operation_type = g_op_type
802 -- AND transition_type <> 3
803 ORDER BY transition_type -- the order is important - primary, alternate and then rework loop shud be considered
804 , next_op_seq_num;
805 j NUMBER;
806 l_flag BOOLEAN := FALSE;
807 BEGIN
808 visited(p_ind) := 1;
809 FOR C1 in next_ops_cur(p_op_seq_id) LOOP
810 j := find_op(C1.next_op_seq_id);
811 IF C1.transition_type = 3 THEN
812 rwrk_found := TRUE; --- set the rework flag indicating that a rework loop is being checked for its end
813 END IF;
814 IF j <> -1 AND NOT l_flag THEN --- If not junction operation and no cycle is found till now
815 IF visited(j) = 1 THEN --- This operation has already been 'visited' in this traversal
816 IF NOT is_dummy(j) AND rwrk_found AND NOT l_flag THEN
817 rwrk_found := false; --- unset the flag indicating that the rework loop ends in a correct preceding operation
818 ELSE
819 token := ntwk_op_tbl(j).operation_seq_num;
820 return TRUE;
821 END IF;
822 ELSIF visited(j) = 0 THEN ---- Not necessary to check for visited(j) = 2 because that has already been 'expanded' completely
823 l_flag := isCyclical(C1.next_op_seq_id, j);
824 END IF;
825 END IF;
826 END LOOP;
827 IF NOT l_flag THEN --- Say explored only when NOT returning from a rework loop
828 visited(p_ind) := 2; --- Completely explored
829 END IF;
830 IF l_flag OR rwrk_found THEN ---- If there is an unended rework loop or if the rework goes to a subsequent operation
831 RETURN TRUE;
832 ELSE
833 RETURN FALSE;
834 END IF;
835 END isCyclical;
836
837 PROCEDURE calc_dummy_rev_cum_yld (
838 op_ptr IN NUMBER
839 ) IS
840
841 CURSOR prev_opns_cur (cv_to_seq_id number) IS
842 SELECT from_op_seq_id prev_op, planning_pct
843 FROM bom_operation_networks
844 WHERE to_op_seq_id = cv_to_seq_id
845 AND transition_type = 3;
846 j NUMBER := 0;
847 BEGIN
848
849 FOR prev_opns_rec IN prev_opns_cur(ntwk_op_tbl(op_ptr).operation_seq_id) LOOP
850 IF j <> 0 THEN
851 token := ntwk_op_tbl(j).operation_seq_num;
852 raise MULTIPLE_ENTRY_DUMMY;
853 END IF;
854 j := find_op(prev_opns_rec.prev_op);
855 -- IF j <> -1 AND ntwk_op_tbl(j).rev_cumulative_yield is NULL THEN --- no need to check j <> -1 as any previous operation cannot be a junction operation
856 IF ntwk_op_tbl(j).rev_cumulative_yield is NULL THEN
857 IF NOT(nvl(ntwk_op_tbl(j).is_dummy,0) <> 1) THEN
858 calc_dummy_rev_cum_yld(j);
859 END IF;
860 END IF;
861 END LOOP;
862 ntwk_op_tbl(op_ptr).rev_cumulative_yield := ntwk_op_tbl(j).rev_cumulative_yield;
863 END calc_dummy_rev_cum_yld;
864
865 PROCEDURE validate_operation(i NUMBER) is
866 CURSOR next_op_cur(cv_start_op_seq_id NUMBER) is
867 SELECT bonv.transition_type, bonv.planning_pct
868 FROM bom_operation_networks_v bonv
869 WHERE routing_sequence_id = g_rtg_seq_id
870 AND bonv.from_op_seq_id = cv_start_op_seq_id
871 AND bonv.operation_type = g_op_type
872 AND bonv.transition_type <> 3;
873
874 prim_found BOOLEAN := FALSE;
875 next_op_found BOOLEAN := FALSE;
876 tot_pct NUMBER := 0;
877 BEGIN
878 FOR next_op_rec IN next_op_cur(ntwk_op_tbl(i).operation_seq_id) LOOP
879 next_op_found := TRUE;
880 IF next_op_rec.transition_type = 1 THEN
881 prim_found := TRUE;
882 END IF;
883 tot_pct := tot_pct + nvl(next_op_rec.planning_pct, 0);
884 END LOOP;
885 IF next_op_found THEN
886 -- IF NOT prim_found AND g_cfm_flag = 1 THEN -- added cfm flag check for bug 2739224
887 IF NOT prim_found AND g_op_type in (2,3) THEN -- This check is only for flow routings - for bug 2739224
888 token := ntwk_op_tbl(i).operation_seq_num;
889 raise NO_PRIMARY_LINK;
890 END IF;
891 IF tot_pct <> 100 THEN
892 token := ntwk_op_tbl(i).operation_seq_num;
893 raise PLANNING_PCT_SUM_ERROR;
894 END IF;
895 END IF;
896 END validate_operation;
897
898 PROCEDURE calc_primary_network (
899 p_routing_sequence_id IN NUMBER,
900 p_operation_type IN VARCHAR2,
901 p_update_events IN NUMBER
902 ) IS
903
904 CURSOR end_op_cur IS
905 SELECT max(to_op_seq_id) end_op_seq_id
906 FROM bom_operation_networks_v bonv
907 WHERE bonv.routing_sequence_id = p_routing_sequence_id
908 AND bonv.operation_type = p_operation_type
909 AND bonv.transition_type <> 3
910 AND NOT EXISTS (SELECT NULL
911 FROM bom_operation_networks net
912 WHERE net.from_op_seq_id = bonv.to_op_seq_id
913 AND net.transition_type <> 3);
914
918 CURSOR next_ops_cur (cv_start_op_seq_id number) IS
915 -- For cv_start_op_seq_id, traverse the network to select all the
916 -- adjacent 'to' operations (first the primary and then alternates
917 -- except rework)
919 SELECT to_op_seq_id next_op_seq_id,
920 to_seq_num next_op_seq_num,
921 transition_type
922 FROM bom_operation_networks_v bonv
923 WHERE routing_sequence_id = p_routing_sequence_id
924 AND from_op_seq_id = cv_start_op_seq_id
925 AND operation_type = p_operation_type
926 AND transition_type <> 3
927 ORDER BY transition_type desc, next_op_seq_num;
928
929 CURSOR next_alt_ops_cur (cv_op_seq_id number) IS
930 SELECT to_op_seq_id next_op_seq_id,
931 to_seq_num next_op_seq_num,
932 transition_type
933 FROM bom_operation_networks_v bonv
934 WHERE routing_sequence_id = p_routing_sequence_id
935 AND from_op_seq_id = cv_op_seq_id
936 AND operation_type = p_operation_type
937 AND transition_type = 2
938 ORDER BY transition_type desc, next_op_seq_num;
939
940 -- For cv_start_op_seq_id, traverse the network to select all the
941 -- adjacent REWORK 'to' operations
942 CURSOR rework_ops_cur (cv_start_op_seq_id number) IS
943 SELECT to_op_seq_id, to_seq_num,
944 transition_type, nvl(planning_pct, 0) planning_pct,
945 from_op_seq_id, from_seq_num
946 FROM bom_operation_networks_v bonv
947 WHERE routing_sequence_id = p_routing_sequence_id
948 AND from_op_seq_id = cv_start_op_seq_id
949 AND operation_type = p_operation_type
950 AND transition_type = 3
951 ORDER BY to_seq_num;
952
953 CURSOR rework_ops_cur1 (cv_start_op_seq_id number) IS
954 SELECT to_op_seq_id, to_seq_num,
955 transition_type, nvl(planning_pct, 0) planning_pct,
956 from_op_seq_id, from_seq_num
957 FROM bom_operation_networks_v bonv
958 WHERE routing_sequence_id = p_routing_sequence_id
959 AND from_op_seq_id = cv_start_op_seq_id
960 AND operation_type = p_operation_type
961 AND transition_type = 3
962 ORDER BY to_seq_num;
963
964 cursor prim_path_cur (cv_start_op_seq_id number) is
965 select bon.to_op_seq_id prim_op_seq_id
966 from bom_operation_networks bon
967 connect by prior to_op_seq_id = from_op_seq_id
968 and
969 nvl(bon.transition_type, 0) not in (2, 3)
970 start with from_op_seq_id = cv_start_op_seq_id
971 and
972 nvl(bon.transition_type, 0) not in (2, 3);
973 i number;
974 k number;
975 ii number;
976 j number;
977 v_ptr number;
978 v_local_ptr number;--used for temp loop and always
979 -- takse its value from v_ptr at the start of the loop
980 v_alt_ptr number;
981 v_to_op_seq_id number;
982 v_to_seq_num number;
983 v_planning_pct number;
984 v_cnt number;
985 v_yield number;
986 v_cumyld number;
987 v_on_alternate boolean;
988
989 v_start_op_seq_id number;
990 v_start_operation_seq_num number;
991 v_netpln number;
992 v_odf number; -- Option dependent flag
993 v_rework boolean := false;
994 l_yield number := 0;
995 dummy_plan_pct number;
996 next_op_pln_pct number;
997 next_op_ptr number;
998 low_ptr number;
999 high_ptr number;
1000 dummy_loop boolean;
1001 v_temp number;
1002
1003 BEGIN
1004 -- Create the primary path table
1005 k := 1;
1006 FOR prim_path_rec IN prim_path_cur(start_tbl(1).operation_seq_id) LOOP
1007 prim_path_tbl(k):= prim_path_rec.prim_op_seq_id;
1008 k := k + 1;
1009 END LOOP;
1010 --print_primary;
1011 -- Save the STARTing op in the Ntwk_Op table
1012 -- Default np% to 100 since the starting op cannot have a % assigned
1013 -- cum_yld of the start opn is equal to its yield
1014 ntwk_op_tbl(1).operation_seq_id := start_tbl(1).operation_seq_id;
1015 ntwk_op_tbl(1).operation_seq_num := start_tbl(1).operation_seq_num;
1016 ntwk_op_tbl(1).net_planning_pct := 100;
1017 ntwk_op_tbl(1).yield_nppct := 100;
1018 ntwk_op_tbl(1).rework_loop_flag := 0;
1019 ntwk_op_tbl(1).cumulative_rwk_pct := 0;
1020 ntwk_op_tbl(1).cumulative_yield := start_tbl(1).yield;
1021 ntwk_op_tbl(1).is_dummy := 0;
1022
1023 v_ptr := 1;
1024 v_local_ptr := 1;
1025 -- Fetch all the next TO operations from the starting operation
1026 i := 1;
1027 WHILE (v_ptr <= i) LOOP
1028 v_local_ptr := v_ptr;
1029 IF nvl(ntwk_op_tbl(v_ptr).is_dummy,0) <> 1 THEN -- if not dummy
1030 FOR rework_ops_rec IN rework_ops_cur(ntwk_op_tbl(v_local_ptr).operation_seq_id) LOOP
1031 reworks_found := TRUE;
1032 ntwk_op_tbl(v_local_ptr).rework_loop_flag := 1;
1033 IF ( find_op(rework_ops_rec.to_op_seq_id) = -1 ) THEN /**** Add the operation to the table ****/
1034 i := i + 1;
1035 v_ptr := v_ptr + 1;
1036 ntwk_op_tbl(i).operation_seq_id := rework_ops_rec.to_op_seq_id;
1037 ntwk_op_tbl(i).operation_seq_num := rework_ops_rec.to_seq_num;
1038 ntwk_op_tbl(i).rework_loop_flag := 1;
1039 END IF;
1040
1041 IF( is_dummy(find_op(ntwk_op_tbl(i).operation_seq_id))) THEN
1045 -- Now, try and find where this rework meets the original path
1042 ntwk_op_tbl(v_local_ptr).mark_for_rework_feed := 1;
1043 ntwk_op_tbl(i).is_dummy := 1;
1044 dummy_plan_pct := calc_dummy_net_planning_pct(ntwk_op_tbl(i).operation_seq_id);
1046 dummy_loop := TRUE;
1047 WHILE dummy_loop LOOP
1048 FOR rework_ops_rec1 IN rework_ops_cur1(ntwk_op_tbl(i).operation_seq_id)
1049 LOOP
1050 reworks_found := TRUE;
1051
1052 next_op_pln_pct := rework_ops_rec1.planning_pct;
1053 next_op_ptr := find_op(rework_ops_rec1.to_op_seq_id);
1054 -- IF find_op(rework_ops_rec1.to_op_seq_id ) = -1 THEN
1055 IF next_op_ptr = -1 THEN
1056 i := i + 1;
1057 v_ptr := v_ptr + 1;
1058 ntwk_op_tbl(i).operation_seq_id := rework_ops_rec1.to_op_seq_id;
1059 ntwk_op_tbl(i).operation_seq_num:= rework_ops_rec1.to_seq_num;
1060 ntwk_op_tbl(i).rework_loop_flag := 1;
1061 ELSE
1062 -- ntwk_op_tbl(find_op(rework_ops_rec1.to_op_seq_id)).rework_loop_flag := 1;
1063 ntwk_op_tbl(next_op_ptr).rework_loop_flag := 1;
1064 END IF;
1065 IF is_dummy(find_op(rework_ops_rec1.to_op_seq_id)) THEN
1066 ntwk_op_tbl(i).is_dummy := 1;
1067 dummy_plan_pct := calc_dummy_net_planning_pct(ntwk_op_tbl(i).operation_seq_id);
1068 ELSE
1069 dummy_loop := FALSE;
1070 END IF;
1071 END LOOP;
1072 END LOOP; -- END OF dummy_loop
1073 END IF;
1074 END LOOP; -- end of REWORK LOOP
1075 FOR next_ops_rec IN next_ops_cur (ntwk_op_tbl(v_local_ptr).operation_seq_id) LOOP
1076 -- if not already in table
1077 --bug 1030309
1078 --and if on the alternate path, should not be on the primary also
1079 IF find_op(next_ops_rec.next_op_seq_id) = -1 AND
1080 (next_ops_rec.transition_type = 1 or
1081 (next_ops_rec.transition_type = 2
1082 and NOT on_primary_path(next_ops_rec.next_op_seq_id))) THEN
1083 -- add node into the ntwk_op_tbl and calc np%
1084 i := i + 1;
1085 ntwk_op_tbl(i).operation_seq_id := next_ops_rec.next_op_seq_id;
1086 ntwk_op_tbl(i).operation_seq_num := next_ops_rec.next_op_seq_num;
1087 -- calc_net_planning_pct(find_op(ntwk_op_tbl(i).operation_seq_id),
1088 -- find_op(ntwk_op_tbl(i).operation_seq_id), -1);
1089 calc_net_planning_pct(i,i, -1);
1090 -- calc_cum_yld(find_op(ntwk_op_tbl(i).operation_seq_id));
1091 calc_cum_yld(i);
1092 END IF;
1093 END LOOP; -- End loop for next_ops_cur
1094 -- move pointer to the next row in table
1095 END IF;--only execute if not dummy
1096 v_ptr := v_ptr + 1;
1097 END LOOP; -- while loop
1098
1099 --print_op();
1100 FOR ii in 1..ntwk_op_tbl.COUNT LOOP
1101 FOR end_op_rec in end_op_cur LOOP
1102 IF end_op_rec.end_op_seq_id = ntwk_op_tbl(ii).operation_seq_id THEN
1103 swap_ops(ii);
1104 END IF; -- If they match
1105 END LOOP;
1106 END LOOP;
1107
1108 visited.DELETE;
1109 g_rtg_seq_id := p_routing_sequence_id;
1110 g_op_type := p_operation_type;
1111 FOR ii in 1..ntwk_op_tbl.COUNT LOOP
1112 IF NOT is_dummy(ii) THEN
1113 validate_operation(ii);
1114 END IF;
1115 END LOOP;
1116 check_loops();
1117
1118 temp_op_tbl.DELETE;
1119 temp_tbl_cnt := 1;
1120
1121 IF NOT reworks_found THEN
1122 calc_net_plan_pct_sanity(ntwk_op_tbl(ntwk_op_tbl.COUNT).operation_seq_id);
1123 ELSE
1124 calc_net_plan_pct_sanity(ntwk_op_tbl(ntwk_op_tbl.COUNT).operation_seq_id);
1125 calc_npp_sanity_when_rework();
1126 END IF;
1127
1128 -- Calculate the REV CUM YIELD for all the operations
1129 FOR i in REVERSE 1..ntwk_op_tbl.COUNT LOOP
1130 if i = ntwk_op_tbl.COUNT then/*
1131 select nvl(yield, 1)
1132 into l_yield
1133 from bom_operation_sequences
1134 where operation_sequence_id = ntwk_op_tbl(i).operation_seq_id;*/
1135 -- BUG 4506235
1136 select DECODE(bor.cfm_routing_flag, 3, (DECODE(bos.operation_yield_enabled, 1, NVL(bos.yield, 1), 1)), NVL(bos.yield, 1))
1137 into l_yield
1138 from bom_operational_routings bor, bom_operation_sequences bos
1139 where bor.routing_sequence_id = bos.routing_sequence_id
1140 and bos.operation_sequence_id = ntwk_op_tbl(i).operation_seq_id;
1141 ntwk_op_tbl(i).rev_cumulative_yield := l_yield;
1142 else
1143 temp_op_tbl.DELETE;
1144 temp_tbl_cnt := 1;
1145
1149 calc_rev_cum_yld(i);
1146 -- IF NOT is_dummy(find_op(ntwk_op_tbl(i).operation_seq_id)) THEN
1147 IF NOT is_dummy(i) THEN
1148 -- calc_rev_cum_yld(find_op(ntwk_op_tbl(i).operation_seq_id));
1150 END IF;
1151 end if;
1152 END LOOP;
1153
1154 FOR i in REVERSE 1..ntwk_op_tbl.COUNT LOOP
1155 -- IF is_dummy(find_op(ntwk_op_tbl(i).operation_seq_id)) THEN
1156 IF is_dummy(i) THEN
1157 IF ntwk_op_tbl(i).rev_cumulative_yield is NULL THEN
1158 -- calc_dummy_rev_cum_yld(find_op(ntwk_op_tbl(i).operation_seq_id));
1159 calc_dummy_rev_cum_yld(i);
1160 END IF;
1161 END IF;
1162 END LOOP;
1163 /*
1164 FOR i IN 1..ntwk_op_tbl.COUNT LOOP
1165 dbms_output.put_line('row: '||to_char(i)||
1166 ' op: '||to_char(ntwk_op_tbl(i).operation_seq_num)||
1167 ' ID: '||to_char(ntwk_op_tbl(i).operation_seq_id)||
1168 ' cyld '||to_char(ntwk_op_tbl(i).cumulative_yield)||
1169 ' rcyld '||to_char(ntwk_op_tbl(i).rev_cumulative_yield)||
1170 ' np% '||to_char(ntwk_op_tbl(i).net_planning_pct));
1171 END LOOP;
1172 */
1173 -- Update the database fields for cum yield/rev cum yield/net planning
1174 updt_db(p_routing_sequence_id, p_operation_type, p_update_events);
1175 END calc_primary_network;
1176
1177 FUNCTION bld_ntwk_op_tbl(
1178 p_op_seq_id IN NUMBER,
1179 p_routing_sequence_id IN NUMBER,
1180 p_operation_type IN NUMBER
1181 ) RETURN NUMBER IS
1182
1183 CURSOR end_op_cur IS
1184 SELECT max(to_op_seq_id) end_op_seq_id
1185 FROM bom_operation_networks_v bonv
1186 WHERE bonv.routing_sequence_id = p_routing_sequence_id
1187 AND bonv.operation_type = p_operation_type
1188 AND bonv.transition_type <> 3
1189 AND NOT EXISTS (SELECT NULL
1190 FROM bom_operation_networks net
1191 WHERE net.from_op_seq_id = bonv.to_op_seq_id
1192 AND net.transition_type <> 3);
1193
1194 -- For cv_start_op_seq_id, traverse the network to select all the
1195 -- adjacent 'to' operations (first the primary and then alternates
1196 -- except rework)
1197 CURSOR next_ops_cur (cv_start_op_seq_id number) IS
1198 SELECT to_op_seq_id next_op_seq_id,
1199 to_seq_num next_op_seq_num,
1200 transition_type
1201 FROM bom_operation_networks_v bonv
1202 WHERE routing_sequence_id = p_routing_sequence_id
1203 AND from_op_seq_id = cv_start_op_seq_id
1204 AND operation_type = p_operation_type
1205 AND transition_type <> 3
1206 ORDER BY transition_type desc, next_op_seq_num;
1207
1208 CURSOR next_alt_ops_cur (cv_op_seq_id number) IS
1209 SELECT to_op_seq_id next_op_seq_id,
1210 to_seq_num next_op_seq_num,
1211 transition_type
1212 FROM bom_operation_networks_v bonv
1213 WHERE routing_sequence_id = p_routing_sequence_id
1214 AND from_op_seq_id = cv_op_seq_id
1215 AND operation_type = p_operation_type
1216 AND transition_type = 2
1217 ORDER BY transition_type desc, next_op_seq_num;
1218
1219 -- For cv_start_op_seq_id, traverse the network to select all the
1220 -- adjacent REWORK 'to' operations
1221 CURSOR rework_ops_cur (cv_start_op_seq_id number) IS
1222 SELECT to_op_seq_id, to_seq_num,
1223 transition_type, nvl(planning_pct, 0) planning_pct,
1224 from_op_seq_id, from_seq_num
1225 FROM bom_operation_networks_v bonv
1226 WHERE routing_sequence_id = p_routing_sequence_id
1227 AND from_op_seq_id = cv_start_op_seq_id
1228 AND operation_type = p_operation_type
1229 AND transition_type = 3
1230 ORDER BY to_seq_num;
1231 CURSOR rework_ops_cur1 (cv_start_op_seq_id number) IS
1232 SELECT to_op_seq_id, to_seq_num,
1233 transition_type, nvl(planning_pct, 0) planning_pct,
1234 from_op_seq_id, from_seq_num
1235 FROM bom_operation_networks_v bonv
1236 WHERE routing_sequence_id = p_routing_sequence_id
1237 AND from_op_seq_id = cv_start_op_seq_id
1238 AND operation_type = p_operation_type
1239 AND transition_type = 3
1240 ORDER BY to_seq_num;
1241
1242 i number;
1243 k number;
1244 ii number;
1245 j number;
1246 v_ptr number;
1247 v_local_ptr number;--used for temp loop and always
1248 -- takse its value from v_ptr at the start of the loop
1249 v_alt_ptr number;
1250 v_to_op_seq_id number;
1251 v_to_seq_num number;
1252 v_planning_pct number;
1253 v_cnt number;
1254 v_yield number;
1255 v_cumyld number;
1256 v_on_alternate boolean;
1257
1258 v_start_op_seq_id number;
1259 v_start_operation_seq_num number;
1260 v_netpln number;
1261 v_odf number; -- Option dependent flag
1262 v_rework boolean := false;
1263 next_op_pln_pct number;
1264 next_op_ptr number;
1265 low_ptr number;
1266 high_ptr number;
1267 dummy_loop boolean;
1268 v_temp number;
1269 l_jop number;
1270 jop number;
1271 jop_found boolean;
1272
1273 BEGIN
1274 v_ptr := 1;
1275 v_local_ptr := 1;
1276 -- Fetch all the next TO operations from the starting operation
1277 j := 1;
1278 jop_found := FALSE;
1279 WHILE (v_ptr <= j) LOOP
1280 v_local_ptr := v_ptr;
1284 ntwk_op_tbl(v_local_ptr).rework_loop_flag := 1;
1281 IF nvl(ntwk_op_tbl(v_ptr).is_dummy,0) <> 1 THEN
1282 FOR rework_ops_rec IN rework_ops_cur(ntwk_op_tbl(v_local_ptr).operation_seq_id) LOOP
1283 reworks_found := TRUE;
1285 IF find_op(rework_ops_rec.to_op_seq_id) = -1 AND find_in_main_tbl(rework_ops_rec.to_op_seq_id) = -1 THEN
1286 ----- Added another check so that wrong operations are not added to ntwk_op_tbl when the network is wrongly created.
1287 j := j + 1;
1288 -- v_ptr := v_ptr + 1; --- commented so that no operation is missed, the operation going into the last operation, if having a rework, will fail if this increment happens
1289 ntwk_op_tbl(j).operation_seq_id := rework_ops_rec.to_op_seq_id;
1290 ntwk_op_tbl(j).operation_seq_num := rework_ops_rec.to_seq_num;
1291 ntwk_op_tbl(j).rework_loop_flag := 1;
1292 END IF;
1293
1294 -- IF( is_dummy(find_op(ntwk_op_tbl(j).operation_seq_id))) THEN
1295 IF( is_dummy(j)) THEN
1296 ntwk_op_tbl(v_local_ptr).mark_for_rework_feed := 1;
1297 ntwk_op_tbl(j).is_dummy := 1;
1298 -- Now, try and find where this rework meets the original path
1299 dummy_loop := TRUE;
1300 WHILE dummy_loop LOOP
1301 FOR rework_ops_rec1 IN rework_ops_cur1(ntwk_op_tbl(j).operation_seq_id)
1302 LOOP
1303 reworks_found := TRUE;
1304
1305 next_op_pln_pct := rework_ops_rec1.planning_pct;
1306 next_op_ptr := find_op(rework_ops_rec1.to_op_seq_id);
1307 -- IF find_op(rework_ops_rec1.to_op_seq_id ) = -1 THEN
1308 IF next_op_ptr = -1 THEN
1309 j := j + 1;
1310 -- v_ptr := v_ptr + 1; --- may be valid here, need to investigate further, commented for the time being
1311 ntwk_op_tbl(j).operation_seq_id := rework_ops_rec1.to_op_seq_id;
1312 ntwk_op_tbl(j).operation_seq_num:= rework_ops_rec1.to_seq_num;
1313 ntwk_op_tbl(j).rework_loop_flag := 1;
1314 ELSE
1315 -- ntwk_op_tbl(find_op(rework_ops_rec1.to_op_seq_id)).rework_loop_flag := 1;
1316 ntwk_op_tbl(next_op_ptr).rework_loop_flag := 1;
1317 END IF;
1318 -- IF is_dummy(find_op(rework_ops_rec1.to_op_seq_id)) THEN
1319 IF is_dummy(next_op_ptr) THEN
1320 ntwk_op_tbl(j).is_dummy := 1;
1321 ELSE
1322 dummy_loop := FALSE;
1323 END IF;
1324 END LOOP;
1325 END LOOP; -- END OF dummy_loop
1326 END IF; -- end of IF dummy
1327 END LOOP;-- end of REWORK LOOP
1328 FOR next_ops_rec IN next_ops_cur (ntwk_op_tbl(v_local_ptr).operation_seq_id) LOOP
1329 /*********** if the operation found is on the parent network, exit (junction is found) ***********/
1330 l_jop := find_in_main_tbl(next_ops_rec.next_op_seq_id);
1331 if l_jop <> -1 THEN
1332
1333 if jop_found THEN
1334 token := ntwk_op_tbl(1).operation_seq_num;
1335 raise MULTIPLE_JUNCTION_OP;
1336 end if;
1337
1338 jop := l_jop;
1339 jop_found := TRUE;
1340 v_temp := v_local_ptr;
1341 END IF;
1342
1343 -- if not already in table
1344 --bug 1030309
1345 --and if on the alternate path, should not be on the primary also
1346 IF (find_op(next_ops_rec.next_op_seq_id) = -1) AND
1347 (next_ops_rec.transition_type = 1 or
1348 (next_ops_rec.transition_type = 2
1349 and (NOT on_primary_path(next_ops_rec.next_op_seq_id))))
1350 AND (NOT jop_found OR l_jop = -1) THEN
1351 -- add node into the ntwk_op_tbl and calc np%
1352 j := j + 1;
1353 ntwk_op_tbl(j).operation_seq_id := next_ops_rec.next_op_seq_id;
1354 ntwk_op_tbl(j).operation_seq_num := next_ops_rec.next_op_seq_num;
1355 END IF;
1356 END LOOP; -- End loop for next_ops_cur
1357 -- move pointer to the next row in table
1358 END IF;--only execute if not dummy
1359 v_ptr := v_ptr + 1;
1360 END LOOP; -- while loop
1361 i := v_temp;
1362 IF v_temp <> ntwk_op_tbl.COUNT AND v_temp <> 1 THEN
1363 swap_ops(i);
1364 END IF; -- If they match
1365 return jop;
1366 END bld_ntwk_op_tbl;
1367
1368
1369 PROCEDURE calc_feeder_network (
1370 p_routing_sequence_id IN NUMBER,
1371 p_operation_type IN VARCHAR2,
1372 p_update_events IN NUMBER,
1373 p_ind IN NUMBER
1374 )
1375 IS
1376 cursor prim_path_fdr_cur (cv_start_op_seq_id number) is
1377 select bon.to_op_seq_id prim_op_seq_id
1378 from bom_operation_networks bon
1379 connect by prior to_op_seq_id = from_op_seq_id
1380 and
1381 nvl(bon.transition_type, 0) not in (2, 3)
1382 start with from_op_seq_id = cv_start_op_seq_id
1383 and
1384 nvl(bon.transition_type, 0) not in (2, 3);
1385
1386 i number;
1387 k number;
1388 l_jop number;
1389 dummy_plan_pct number;
1390 l_yield number := 0;
1391
1392 BEGIN
1393 -- Create the primary path table for the feeder
1394 k := 1;
1395 FOR prim_path_fdr_rec IN prim_path_fdr_cur(start_tbl(p_ind).operation_seq_id) LOOP
1396 prim_path_tbl(k):= prim_path_fdr_rec.prim_op_seq_id;
1397 k := k + 1;
1398 END LOOP;
1399 -- Save the STARTing op in the Ntwk_Op table
1400 -- Default np% to 100 since the starting op cannot have a % assigned
1401 -- cum_yld of the start opn is equal to its yield
1402
1403 ntwk_op_tbl(1).operation_seq_id := start_tbl(p_ind).operation_seq_id;
1407 ntwk_op_tbl(1).rework_loop_flag := 0;
1404 ntwk_op_tbl(1).operation_seq_num := start_tbl(p_ind).operation_seq_num;
1405 ntwk_op_tbl(1).net_planning_pct := 100;
1406 ntwk_op_tbl(1).yield_nppct := 100;
1408 ntwk_op_tbl(1).cumulative_rwk_pct := 0;
1409 ntwk_op_tbl(1).cumulative_yield := start_tbl(p_ind).yield;
1410 ntwk_op_tbl(1).is_dummy := 0; -- starting operation cannot be dummy
1411
1412 l_jop := bld_ntwk_op_tbl(start_tbl(p_ind).operation_seq_id, p_routing_sequence_id, p_operation_type);
1413
1414 visited.DELETE;
1415 g_rtg_seq_id := p_routing_sequence_id;
1416 g_op_type := p_operation_type;
1417 FOR i in 1..ntwk_op_tbl.COUNT LOOP
1418 IF NOT is_dummy(i) THEN
1419 validate_operation(i);
1420 END IF;
1421 END LOOP;
1422 check_loops();
1423
1424 FOR i IN 2..ntwk_op_tbl.COUNT LOOP
1425 calc_net_planning_pct(i, i, -1);
1426 END LOOP;
1427
1428 FOR i IN 2..ntwk_op_tbl.COUNT LOOP
1429 IF nvl(ntwk_op_tbl(i).is_dummy,0) = 1 THEN
1430 dummy_plan_pct := calc_dummy_net_planning_pct(ntwk_op_tbl(i).operation_seq_id);
1431 END IF;
1432 END LOOP;
1433
1434
1435
1436 /********* Check for operations that are alternate and not on primary path?? *************/
1437
1438 temp_op_tbl.DELETE;
1439 temp_tbl_cnt := 1;
1440
1441 IF NOT reworks_found THEN
1442 calc_net_plan_pct_sanity(main_ntwk_op_tbl(l_jop).operation_seq_id);
1443 ELSE
1444 calc_net_plan_pct_sanity(main_ntwk_op_tbl(l_jop).operation_seq_id);
1445 calc_npp_sanity_when_rework();
1446 END IF;
1447
1448
1449 /************* Multiply the NPP of the junction operation as the factor to NPP of all operations ********/
1450 FOR i IN 1..ntwk_op_tbl.COUNT LOOP
1451 ntwk_op_tbl(i).net_planning_pct := ntwk_op_tbl(i).net_planning_pct * nvl(main_ntwk_op_tbl(l_jop).net_planning_pct,100)/100;
1452 END LOOP;
1453
1454 -- Calculate the REV CUM YIELD for all the operations
1455 FOR i in REVERSE 1..ntwk_op_tbl.COUNT LOOP
1456 if i = ntwk_op_tbl.COUNT then/*
1457 select nvl(yield, 1)
1458 into l_yield
1459 from bom_operation_sequences
1460 where operation_sequence_id = ntwk_op_tbl(i).operation_seq_id;*/
1461 -- BUG 4506235
1462 select DECODE(bor.cfm_routing_flag, 3, (DECODE(bos.operation_yield_enabled, 1, NVL(bos.yield, 1), 1)), NVL(bos.yield, 1))
1463 into l_yield
1464 from bom_operational_routings bor, bom_operation_sequences bos
1465 where bor.routing_sequence_id = bos.routing_sequence_id
1466 and bos.operation_sequence_id = ntwk_op_tbl(i).operation_seq_id;
1467 ntwk_op_tbl(i).rev_cumulative_yield := l_yield;
1468 else
1469 temp_op_tbl.DELETE;
1470 temp_tbl_cnt := 1;
1471 -- IF NOT is_dummy(find_op(ntwk_op_tbl(i).operation_seq_id)) THEN
1472 IF NOT is_dummy(i) THEN
1473 -- calc_rev_cum_yld(find_op(ntwk_op_tbl(i).operation_seq_id));
1474 calc_rev_cum_yld(i);
1475 END IF;
1476 end if;
1477 END LOOP;
1478
1479 FOR i in REVERSE 1..ntwk_op_tbl.COUNT LOOP
1480 -- IF is_dummy(find_op(ntwk_op_tbl(i).operation_seq_id)) THEN
1481 IF is_dummy(i) THEN
1482 IF ntwk_op_tbl(i).rev_cumulative_yield is NULL THEN
1483 -- calc_dummy_rev_cum_yld(find_op(ntwk_op_tbl(i).operation_seq_id));
1484 calc_dummy_rev_cum_yld(i);
1485 END IF;
1486 END IF;
1487 END LOOP;
1488 /*
1489 dbms_output.put_line('*********feeder************');
1490 FOR i IN 1..ntwk_op_tbl.COUNT LOOP
1491 dbms_output.put_line('row: '||to_char(i)||
1492 ' op: '||to_char(ntwk_op_tbl(i).operation_seq_num)||
1493 ' ID: '||to_char(ntwk_op_tbl(i).operation_seq_id)||
1494 ' cyld '||to_char(ntwk_op_tbl(i).cumulative_yield)||
1495 ' rcyld '||to_char(ntwk_op_tbl(i).rev_cumulative_yield)||
1496 ' np% '||to_char(ntwk_op_tbl(i).net_planning_pct));
1497 END LOOP;
1498 */
1499 -- Update the database fields for cum yield/rev cum yield/net planning --- changes may be required
1500 updt_db(p_routing_sequence_id, p_operation_type, p_update_events);
1501 END calc_feeder_network;
1502
1503 FUNCTION find_op (
1504 p_op_seq_id IN NUMBER
1505 ) RETURN NUMBER IS
1506 BEGIN
1507 FOR i IN 1..ntwk_op_tbl.COUNT LOOP
1508 IF (ntwk_op_tbl(i).operation_seq_id = p_op_seq_id) THEN
1509 RETURN (i);
1510 END IF;
1511 END LOOP;
1512 RETURN (-1);
1513 END find_op;
1514
1515 FUNCTION find_temp_op (
1516 p_op_seq_id IN NUMBER
1517 ) RETURN NUMBER IS
1518 BEGIN
1519 FOR i IN 1..temp_op_tbl.COUNT LOOP
1520 IF (temp_op_tbl(i) = p_op_seq_id) THEN
1521 RETURN (i);
1522 END IF;
1523 END LOOP;
1524 RETURN (-1);
1525 END find_temp_op;
1526
1527 FUNCTION find_in_main_tbl(
1528 p_op_seq_id IN NUMBER
1529 ) RETURN NUMBER IS
1530 BEGIN
1531 FOR i IN 1..main_ntwk_op_tbl.COUNT LOOP
1532 IF (main_ntwk_op_tbl(i).operation_seq_id = p_op_seq_id) THEN
1533 RETURN (i);
1534 END IF;
1535 END LOOP;
1536 RETURN (-1);
1537 END find_in_main_tbl;
1538
1539 PROCEDURE calc_net_planning_pct (
1540 from_ptr IN NUMBER,
1541 to_ptr IN NUMBER,
1542 rwk_pln_pct IN NUMBER
1543 ) IS
1544
1545 CURSOR prev_links_cur (cv_to_seq_id number) IS
1546 SELECT from_op_seq_id prev_op, planning_pct
1547 --FROM bom_operation_networks_v
1548 FROM bom_operation_networks
1549 WHERE to_op_seq_id = cv_to_seq_id
1553 k NUMBER := 0;
1550 AND transition_type <> 3;
1551
1552 j NUMBER;
1554 l_net_planning_pct NUMBER := 0;
1555 l_yield_nppct NUMBER := 0;
1556
1557 BEGIN
1558 -- if REWORK Calc
1559 IF (rwk_pln_pct <> -1) THEN
1560 k := 1;
1561 ELSE k := 0;
1562 END IF;
1563 FOR ii IN from_ptr+k..to_ptr LOOP
1564 IF NOT ( nvl(ntwk_op_tbl(ii).is_dummy,0) = 1) THEN
1565 l_yield_nppct := 0;
1566 FOR prev_links_rec IN prev_links_cur(ntwk_op_tbl(ii).operation_seq_id) LOOP
1567 j := find_op(prev_links_rec.prev_op);
1568 IF (j <> -1) THEN
1569 l_yield_nppct := nvl(l_yield_nppct, 0)
1570 + (ntwk_op_tbl(j).yield_nppct
1571 * prev_links_rec.planning_pct)/100;
1572 END IF;
1573 END LOOP;
1574 ntwk_op_tbl(ii).yield_nppct := l_yield_nppct;
1575 END IF;
1576 END LOOP;
1577
1578 FOR ii IN from_ptr..to_ptr LOOP
1579 IF NOT ( nvl(ntwk_op_tbl(ii).is_dummy,0)=1) THEN
1580 IF (ii = 1) THEN
1581 l_net_planning_pct := 100;
1582 ELSE
1583 l_net_planning_pct := 0;
1584 END IF;
1585 FOR prev_links_rec IN prev_links_cur(ntwk_op_tbl(ii).operation_seq_id) LOOP
1586 j := find_op(prev_links_rec.prev_op);
1587 IF (j <> -1) THEN
1588 l_net_planning_pct := nvl(l_net_planning_pct, 0)
1589 +(ntwk_op_tbl(j).yield_nppct * prev_links_rec.planning_pct)/100;
1590 END IF;
1591 END LOOP;
1592 IF (ntwk_op_tbl(ii).rework_loop_flag = 1) THEN
1593 l_net_planning_pct :=
1594 l_net_planning_pct + nvl(ntwk_op_tbl(ii).cumulative_rwk_pct,0);
1595 END IF;
1596 ntwk_op_tbl(ii).net_planning_pct := l_net_planning_pct;
1597 END IF;
1598 END LOOP;
1599 END calc_net_planning_pct;
1600
1601 PROCEDURE calc_cum_yld (
1602 op_ptr IN NUMBER
1603 ) IS
1604
1605 CURSOR prev_opns_cur (cv_to_seq_id number) IS
1606 SELECT from_op_seq_id prev_op, planning_pct
1607 FROM bom_operation_networks
1608 WHERE to_op_seq_id = cv_to_seq_id
1609 AND transition_type <> 3;
1610
1611 j NUMBER;
1612 l_yield NUMBER := 0;
1613 l_cum_yld NUMBER := 0;
1614
1615 BEGIN
1616 /*
1617 select nvl(yield, 1)
1618 into l_yield
1619 from bom_operation_sequences
1620 where operation_sequence_id = ntwk_op_tbl(op_ptr).operation_seq_id;*/
1621 -- BUG 4506235
1622 select DECODE(bor.cfm_routing_flag, 3, (DECODE(bos.operation_yield_enabled, 1, NVL(bos.yield, 1), 1)), NVL(bos.yield, 1))
1623 into l_yield
1624 from bom_operational_routings bor, bom_operation_sequences bos
1625 where bor.routing_sequence_id = bos.routing_sequence_id
1626 and bos.operation_sequence_id = ntwk_op_tbl(op_ptr).operation_seq_id;
1627
1628 FOR prev_opns_rec IN prev_opns_cur(ntwk_op_tbl(op_ptr).operation_seq_id) LOOP
1629 j := find_op(prev_opns_rec.prev_op);
1630 IF (j <> -1) THEN
1631 IF(ntwk_op_tbl(op_ptr).yield_nppct = 0) THEN
1632 l_cum_yld := nvl(l_cum_yld, 0)
1633 + (ntwk_op_tbl(j).cumulative_yield
1634 * (prev_opns_rec.planning_pct * ntwk_op_tbl(j).yield_nppct
1635 /(100*100)));
1636 ELSE
1637 l_cum_yld := nvl(l_cum_yld, 0)
1638 + (ntwk_op_tbl(j).cumulative_yield
1639 * (prev_opns_rec.planning_pct * ntwk_op_tbl(j).yield_nppct
1640 /(nvl(ntwk_op_tbl(op_ptr).yield_nppct,100)*100)));
1641 END IF;
1642 END IF;
1643 END LOOP;
1644 ntwk_op_tbl(op_ptr).cumulative_yield := l_cum_yld * l_yield;
1645 END calc_cum_yld;
1646
1647 PROCEDURE calc_rev_cum_yld (
1648 op_ptr IN NUMBER
1649 ) IS
1650
1651 CURSOR next_opns_cur (cv_to_seq_id number) IS
1652 SELECT to_op_seq_id next_op, planning_pct
1653 FROM bom_operation_networks
1654 WHERE from_op_seq_id = cv_to_seq_id
1655 AND transition_type <> 3;
1656
1657 j NUMBER;
1658 l_yield NUMBER := 0;
1659 l_rev_cum_yld NUMBER := 0;
1660
1661 BEGIN/*
1662 select nvl(yield, 1)
1663 into l_yield
1664 from bom_operation_sequences
1665 where operation_sequence_id = ntwk_op_tbl(op_ptr).operation_seq_id;*/
1666 -- BUG 4506235
1667 select DECODE(bor.cfm_routing_flag, 3, (DECODE(bos.operation_yield_enabled, 1, NVL(bos.yield, 1), 1)), NVL(bos.yield, 1))
1668 into l_yield
1669 from bom_operational_routings bor, bom_operation_sequences bos
1670 where bor.routing_sequence_id = bos.routing_sequence_id
1671 and bos.operation_sequence_id = ntwk_op_tbl(op_ptr).operation_seq_id;
1672
1673 FOR next_opns_rec IN next_opns_cur(ntwk_op_tbl(op_ptr).operation_seq_id) LOOP
1674 j := find_op(next_opns_rec.next_op);
1675 IF (j <> -1) THEN
1676 IF (ntwk_op_tbl(j).rev_cumulative_yield IS NULL) AND find_temp_op(next_opns_rec.next_op) = -1 THEN
1677 temp_op_tbl(temp_tbl_cnt) := next_opns_rec.next_op;
1678 temp_tbl_cnt := temp_tbl_cnt + 1;
1679 calc_rev_cum_yld(j);
1680 END IF;
1681 ELSE
1682 RETURN;
1683 END IF;
1684 l_rev_cum_yld := nvl(l_rev_cum_yld, 0)
1685 + (nvl(ntwk_op_tbl(j).rev_cumulative_yield,1) * next_opns_rec.planning_pct/100);
1686 END LOOP;
1687 ntwk_op_tbl(op_ptr).rev_cumulative_yield := l_rev_cum_yld * l_yield;
1688 END calc_rev_cum_yld;
1689
1693 SELECT from_op_seq_id prev_op_seq_id,
1690 FUNCTION is_dummy( p_index NUMBER )
1691 RETURN BOOLEAN IS
1692 CURSOR prev_ops_cur (cv_op_seq_id number) IS
1694 transition_type
1695 FROM bom_operation_networks bonv
1696 --WHERE routing_sequence_id = p_routing_sequence_id
1697 WHERE to_op_seq_id = cv_op_seq_id;
1698 --AND operation_type = p_operation_type;
1699
1700 CURSOR next_ops_cur (cv_op_seq_id number) IS
1701 SELECT to_op_seq_id next_op_seq_id,
1702 transition_type
1703 FROM bom_operation_networks bonv
1704 --WHERE routing_sequence_id = p_routing_sequence_id
1705 WHERE from_op_seq_id = cv_op_seq_id;
1706 --AND operation_type = p_operation_type;
1707 rework_in_found BOOLEAN := FALSE;
1708 rework_out_found BOOLEAN := FALSE;
1709 is_dummy BOOLEAN := TRUE;
1710 BEGIN
1711 FOR prev_ops_rec IN prev_ops_cur(ntwk_op_tbl(p_index).operation_seq_id) LOOP
1712 IF prev_ops_rec.transition_type = 3 THEN
1713 rework_in_found := TRUE;
1714 END IF;
1715 IF prev_ops_rec.transition_type <> 3 THEN
1716 is_dummy := FALSE;
1717 END IF;
1718 END LOOP;
1719 IF is_dummy = FALSE THEN
1720 return is_dummy;
1721 END IF;
1722 FOR next_ops_rec IN next_ops_cur(ntwk_op_tbl(p_index).operation_seq_id) LOOP
1723 IF next_ops_rec.transition_type = 3 THEN
1724 rework_out_found := TRUE;
1725 END IF;
1726 IF next_ops_rec.transition_type <> 3 THEN
1727 is_dummy := FALSE;
1728 END IF;
1729 END LOOP;
1730 IF is_dummy AND rework_in_found AND rework_out_found THEN
1731 return TRUE;
1732 ELSE
1733 return FALSE;
1734 END IF;
1735 END;
1736
1737 FUNCTION calc_dummy_net_planning_pct (
1738 op_id IN NUMBER
1739 ) RETURN NUMBER IS
1740
1741 CURSOR prev_links_cur (cv_to_seq_id number) IS
1742 SELECT from_op_seq_id prev_op, planning_pct
1743 FROM bom_operation_networks
1744 WHERE to_op_seq_id = cv_to_seq_id
1745 AND transition_type = 3;
1746
1747 j NUMBER;
1748 l_yield_nppct NUMBER;
1749 l_nppct NUMBER;
1750 BEGIN
1751 FOR prev_ops_rec IN prev_links_cur(op_id) LOOP
1752 l_yield_nppct := nvl(l_yield_nppct,0) +
1753 ntwk_op_tbl(find_op(prev_ops_rec.prev_op)).yield_nppct *
1754 prev_ops_rec.planning_pct / 100;
1755 l_nppct := nvl(l_nppct,0) +
1756 ntwk_op_tbl(find_op(prev_ops_rec.prev_op)).net_planning_pct *
1757 prev_ops_rec.planning_pct / 100;
1758 END LOOP;
1759 ntwk_op_tbl(find_op(op_id)).yield_nppct := l_yield_nppct;
1760 ntwk_op_tbl(find_op(op_id)).net_planning_pct := l_nppct;
1761 return l_nppct;
1762 END calc_dummy_net_planning_pct;
1763
1764 FUNCTION get_fed_rework_pct(
1765 to_ptr IN NUMBER
1766 ) RETURN BOOLEAN IS
1767 CURSOR feeding_links_cur (cv_to_seq_id number) IS
1768 SELECT from_op_seq_id prev_op, planning_pct
1769 FROM bom_operation_networks
1770 WHERE to_op_seq_id = cv_to_seq_id
1771 AND transition_type <> 3;
1772 CURSOR fed_sum (cv_from_seq_id number) IS
1773 SELECT SUM(planning_pct) pct_sum
1774 FROM bom_operation_networks
1775 WHERE from_op_seq_id = cv_from_seq_id
1776 AND transition_type <> 3;
1777 rework_accum NUMBER := 0;
1778 grab_rework BOOLEAN := FALSE;
1779 prev_op NUMBER;
1780 rework_sum_out NUMBER;
1781 BEGIN
1782 FOR feed_rec IN feeding_links_cur(ntwk_op_tbl(to_ptr).operation_seq_id) LOOP
1783 prev_op := find_op(feed_rec.prev_op);
1784 IF prev_op <> -1 THEN
1785 FOR fed_sum_cur in fed_sum(ntwk_op_tbl(prev_op).operation_seq_id) LOOP
1786 rework_sum_out := fed_sum_cur.pct_sum;
1787 END LOOP;
1788 rework_accum := rework_accum +ntwk_op_tbl(prev_op).cumulative_rwk_pct *
1789 feed_rec.planning_pct / rework_sum_out;
1790 IF ntwk_op_tbl(prev_op).mark_for_rework_feed = 1 THEN
1791 grab_rework := TRUE;
1792 END IF;
1793 END IF;
1794 END LOOP;
1795 if grab_rework THEN
1796 ntwk_op_tbl(to_ptr).cumulative_rwk_pct := rework_accum;
1797 return grab_rework;
1798 else
1799 return false;
1800 end if;
1801 END get_fed_rework_pct;
1802
1803 procedure collect_total_rework_prob(
1804 start_op_ptr IN NUMBER
1805 ,end_op_ptr IN NUMBER
1806 ,dummy_pct IN NUMBER
1807 ) IS
1808 CURSOR forward_links_cur (cv_from_seq_id number) IS
1809 SELECT to_op_seq_id next_op
1810 FROM bom_operation_networks
1811 WHERE from_op_seq_id = cv_from_seq_id
1812 AND transition_type <> 3;
1813 CURSOR link_percent_cur (cv_from_seq_id number,cv_to_seq_id number) IS
1814 SELECT PLANNING_PCT npp
1815 FROM bom_operation_networks
1816 WHERE from_op_seq_id = cv_from_seq_id
1817 AND to_op_seq_id = cv_to_seq_id
1818 AND transition_type <> 3;
1819
1820 l_branch_rework_percent_dstr number:=1;
1821 rows_exist number:=0;
1822 j NUMBER;
1823 BEGIN
1824 IF start_op_ptr = end_op_ptr THEN -- When the rework starts and ends in the same operation
1825 g_total_rework_prob := 1;
1826 ELSE
1827 FOR forward_link in forward_links_cur(ntwk_op_tbl(start_op_ptr).operation_seq_id ) LOOP
1828 Rework_Effect_index := Rework_Effect_index + 1;
1832 FOR link_percent_rec IN
1829 rework_effect_tbl(Rework_Effect_index).operation_seq_id := forward_link.next_op;
1830 IF (forward_link.next_op = ntwk_op_tbl(end_op_ptr).operation_seq_id ) THEN
1831 FOR ii IN 1..(Rework_Effect_index-1) LOOP
1833 link_percent_cur(rework_effect_tbl(ii).operation_seq_id,
1834 rework_effect_tbl(ii+1).operation_seq_id) LOOP
1835 l_branch_rework_percent_dstr :=
1836 (l_branch_rework_percent_dstr * nvl(link_percent_rec.npp,100))/100;
1837 END LOOP;
1838 END LOOP;
1839 g_total_rework_prob := g_total_rework_prob + l_branch_rework_percent_dstr;
1840 Rework_Effect_index := Rework_Effect_index - 1;
1841 ELSE
1842 j := find_op(forward_link.next_op);
1843 IF j <> -1 THEN
1844 collect_total_rework_prob(j , end_op_ptr, dummy_pct);
1845 Rework_Effect_index := Rework_Effect_index - 1;
1846 END IF;
1847 END IF;
1848 END LOOP;
1849 END IF;
1850 END collect_total_rework_prob;
1851
1852 procedure collect_ops_between_rework (
1853 start_op_ptr IN NUMBER
1854 ,end_op_ptr IN NUMBER
1855 ,dummy_pct IN NUMBER
1856 ) IS
1857 CURSOR forward_links_cur (cv_from_seq_id number) IS
1858 SELECT to_op_seq_id next_op
1859 FROM bom_operation_networks
1860 WHERE from_op_seq_id = cv_from_seq_id
1861 AND transition_type <> 3;
1862 rows_exist number:=0;
1863 j NUMBER;
1864 BEGIN
1865 IF start_op_ptr = end_op_ptr THEN -- When the rework starts and ends in the same operation
1866 calc_net_planning_pct_rework(dummy_pct);
1867 ELSE
1868 FOR forward_link in forward_links_cur(ntwk_op_tbl(start_op_ptr).operation_seq_id ) LOOP
1869 Rework_Effect_index := Rework_Effect_index + 1;
1870 rework_effect_tbl(Rework_Effect_index).operation_seq_id := forward_link.next_op;
1871
1872 IF (forward_link.next_op = ntwk_op_tbl(end_op_ptr).operation_seq_id ) THEN
1873 calc_net_planning_pct_rework(dummy_pct);
1874 Rework_Effect_index := Rework_Effect_index - 1;
1875 ELSE
1876 j := find_op(forward_link.next_op);
1877 IF j <> -1 THEN
1878 collect_ops_between_rework(j , end_op_ptr, dummy_pct);
1879 Rework_Effect_index := Rework_Effect_index - 1;
1880 END IF;
1881 END IF;
1882 END LOOP;
1883 END IF;
1884 END collect_ops_between_rework;
1885
1886 PROCEDURE calc_net_planning_pct_rework (
1887 --from_ptr IN NUMBER
1888 --,to_ptr IN NUMBER,
1889 dummy_plan_percent IN NUMBER
1890 ) IS
1891 CURSOR prev_links_cur (cv_to_seq_id number) IS
1892 SELECT from_op_seq_id prev_op, planning_pct
1893 FROM bom_operation_networks
1894 WHERE to_op_seq_id = cv_to_seq_id
1895 AND transition_type <> 3;
1896
1897 CURSOR forward_links_cur (cv_from_seq_id number,cv_to_seq_id number) IS
1898 SELECT PLANNING_PCT npp
1899 FROM bom_operation_networks
1900 WHERE from_op_seq_id = cv_from_seq_id
1901 AND to_op_seq_id = cv_to_seq_id
1902 AND transition_type <> 3;
1903
1904 l_yield_nppct NUMBER;
1905 l_net_planning_pct NUMBER;
1906 j NUMBER;
1907 temp NUMBER;
1908 l_dummy_percent_dstr NUMBER := 1;
1909 BEGIN
1910 --print_op();
1911 FOR ii IN 1..(Rework_Effect_index-1) LOOP
1912 FOR forward_link_rec IN
1913 forward_links_cur(rework_effect_tbl(ii).operation_seq_id,
1914 rework_effect_tbl(ii+1).operation_seq_id) LOOP
1915 l_dummy_percent_dstr := (l_dummy_percent_dstr * nvl(forward_link_rec.npp,100))/100;
1916 END LOOP;
1917 END LOOP;
1918 l_dummy_percent_dstr := (dummy_plan_percent * nvl(l_dummy_percent_dstr,1))/nvl(g_total_rework_prob,1);
1919 FOR ii IN 1..Rework_Effect_index LOOP
1920 temp := find_op(rework_effect_tbl(ii).operation_seq_id);
1921 ntwk_op_tbl(temp).rework_loop_flag := 1;
1922 IF (nvl(ntwk_op_tbl(temp).is_dummy,0) <> 1) THEN
1923 ntwk_op_tbl(temp).cumulative_rwk_pct :=
1924 nvl(ntwk_op_tbl(temp).cumulative_rwk_pct,0) +
1925 l_dummy_percent_dstr; -- * ntwk_op_tbl(temp).yield_nppct/100;
1926 END IF;
1927 END LOOP;
1928 FOR ii IN 1..Rework_Effect_index LOOP
1929 temp := find_op(rework_effect_tbl(ii).operation_seq_id);
1930 IF (nvl(ntwk_op_tbl(temp).is_dummy,0) <> 1) THEN
1931 IF (temp = 1) THEN
1932 l_net_planning_pct := 100;
1933 ELSE
1934 l_net_planning_pct := 0;
1935 END IF;
1936 FOR prev_links_rec IN prev_links_cur(ntwk_op_tbl(temp).operation_seq_id) LOOP
1937 j := find_op(prev_links_rec.prev_op);
1938 IF (j <> -1) THEN
1939 l_net_planning_pct := nvl(l_net_planning_pct, 0)
1940 +(ntwk_op_tbl(j).yield_nppct * prev_links_rec.planning_pct)/100;
1941 END IF;
1942 END LOOP;
1943 IF (ntwk_op_tbl(temp).rework_loop_flag = 1) THEN
1944 l_net_planning_pct :=
1945 l_net_planning_pct + ntwk_op_tbl(temp).cumulative_rwk_pct;
1946 END IF;
1947 ntwk_op_tbl(temp).net_planning_pct := l_net_planning_pct;
1951
1948 END IF;
1949 END LOOP;
1950 END calc_net_planning_pct_rework;
1952 FUNCTION on_primary_path (
1953 p_str_op_seq_id IN NUMBER
1954 ) RETURN BOOLEAN IS
1955
1956 BEGIN
1957 FOR j IN 1..prim_path_tbl.COUNT LOOP
1958 if prim_path_tbl(j) = p_str_op_seq_id then
1959 return(TRUE);
1960 end if;
1961 END LOOP;
1962 return(FALSE);
1963
1964 END on_primary_path;
1965
1966 PROCEDURE calc_net_plan_pct(
1967 sanity_counter IN NUMBER
1968 ) IS
1969 l_npp number;
1970 j number;
1971 l_yield_nppct number;
1972 CURSOR prev_links_cur (cv_to_seq_id number) IS
1973 SELECT from_op_seq_id prev_op, planning_pct
1974 FROM bom_operation_networks
1975 WHERE to_op_seq_id = cv_to_seq_id
1976 AND transition_type <> 3;
1977
1978 BEGIN
1979 IF NOT ( nvl(ntwk_op_tbl(sanity_counter).is_dummy,0) = 1) THEN
1980 l_yield_nppct := 0;
1981 FOR prev_links_rec IN
1982 prev_links_cur(ntwk_op_tbl(sanity_counter).operation_seq_id) LOOP
1983 j := find_op(prev_links_rec.prev_op);
1984 IF (j <> -1) THEN
1985 l_yield_nppct := nvl(l_yield_nppct, 0)
1986 + (ntwk_op_tbl(j).yield_nppct *
1987 prev_links_rec.planning_pct)/100;
1988 END IF;
1989 END LOOP;
1990 ntwk_op_tbl(sanity_counter).yield_nppct := l_yield_nppct;
1991 END IF;
1992
1993 l_npp := 0;
1994 j := 0;
1995 FOR prev_links_rec IN
1996 prev_links_cur(ntwk_op_tbl(sanity_counter).operation_seq_id) LOOP
1997 j := find_op(prev_links_rec.prev_op);
1998 IF (j <> -1) THEN
1999 l_npp := nvl(l_npp, 0)
2000 + (ntwk_op_tbl(j).net_planning_pct * prev_links_rec.planning_pct)/100;
2001 END IF;
2002 END LOOP;
2003 ntwk_op_tbl(sanity_counter).net_planning_pct := l_npp;
2004 END;
2005
2006 PROCEDURE calc_net_plan_pct_sanity (
2007 op_seq_id IN NUMBER
2008 ) IS
2009 sanity_counter number;
2010 j number;
2011 l_count NUMBER := 0;
2012
2013 CURSOR prev_links_count_cur (cv_to_seq_id number) IS
2014 SELECT count(*) count
2015 FROM bom_operation_networks
2016 WHERE to_op_seq_id = cv_to_seq_id
2017 AND transition_type <> 3;
2018
2019 CURSOR prev_links_cur (cv_to_seq_id number) IS
2020 SELECT from_op_seq_id prev_op, planning_pct
2021 FROM bom_operation_networks
2022 WHERE to_op_seq_id = cv_to_seq_id
2023 AND transition_type <> 3;
2024 BEGIN
2025 FOR count_rec in prev_links_count_cur(op_seq_id) LOOP
2026 l_count := count_rec.count;
2027 END LOOP;
2028 IF l_count <> 0 THEN
2029 FOR prev_op_rec IN prev_links_cur(op_seq_id) LOOP
2030 IF find_temp_op(prev_op_rec.prev_op) = -1 THEN
2031 temp_op_tbl(temp_tbl_cnt) := prev_op_rec.prev_op;
2032 temp_tbl_cnt := temp_tbl_cnt + 1;
2033 calc_net_plan_pct_sanity(prev_op_rec.prev_op);
2034 END IF;
2035 /*
2036 IF find_op(op_seq_id) <> -1 THEN
2037 calc_net_plan_pct(find_op(op_seq_id));
2038 calc_cum_yld(find_op(op_seq_id));
2039 END IF;
2040 */
2041 j := find_op(op_seq_id);
2042 IF j <> -1 THEN
2043 calc_net_plan_pct(j);
2044 calc_cum_yld(j);
2045 END IF;
2046 END LOOP;
2047 END IF;
2048 END calc_net_plan_pct_sanity;
2049
2050 PROCEDURE calc_npp_sanity_when_rework IS
2051 sanity_counter number;
2052 l_npp number;
2053 j number;
2054 CURSOR rework_out_cur (cv_from_seq_id number) IS
2055 SELECT to_op_seq_id next_op, planning_pct
2056 FROM bom_operation_networks
2057 WHERE from_op_seq_id = cv_from_seq_id
2058 AND transition_type = 3;
2059
2060 CURSOR next_rework_cur (cv_op_seq_id number) IS
2061 SELECT to_op_seq_id next_op, planning_pct
2062 FROM bom_operation_networks
2063 WHERE from_op_seq_id = cv_op_seq_id
2064 AND transition_type = 3;
2065
2066 CURSOR next_op_cur (cv_op_seq_id number) IS
2067 SELECT to_op_seq_id next_op, planning_pct
2068 FROM bom_operation_networks
2069 WHERE from_op_seq_id = cv_op_seq_id
2070 AND transition_type <> 3;
2071
2072 CURSOR next_op_cur1 (cv_op_seq_id number) IS
2073 SELECT to_op_seq_id next_op, planning_pct
2074 FROM bom_operation_networks
2075 WHERE from_op_seq_id = cv_op_seq_id
2076 AND transition_type <> 3;
2077
2078 sanity_rework_pct NUMBER ;
2079 i NUMBER := 0;
2080 rework_loop_ends_op NUMBER := 0;
2081 rework_effect_limit_op NUMBER := 0;
2082 dummy_loop BOOLEAN ;
2083 non_primary_loop BOOLEAN ;
2084 TABLE_ENDED EXCEPTION;
2085 l_dummy NUMBER;
2086 BEGIN
2087 FOR sanity_counter in 1..ntwk_op_tbl.COUNT LOOP
2088 ntwk_op_tbl(sanity_counter).cumulative_rwk_pct :=0;
2089 END LOOP;
2090 FOR sanity_counter in 2..ntwk_op_tbl.COUNT LOOP
2091 rework_effect_tbl.DELETE;
2092 Rework_Effect_index := 0;
2093 IF NOT is_dummy(sanity_counter) THEN
2094 rework_loop_ends_op := 0;
2095 rework_effect_limit_op := 0;
2096 FOR rework_out_rec IN rework_out_cur(ntwk_op_tbl(sanity_counter).operation_seq_id) LOOP
2097 rework_loop_ends_op := 0;
2098 rework_effect_limit_op := 0;
2099 sanity_rework_pct := rework_out_rec.planning_pct *
2100 (ntwk_op_tbl(sanity_counter).yield_nppct/100);
2101 IF (find_op(rework_out_rec.next_op) <> -1) THEN
2102 IF is_dummy(find_op(rework_out_rec.next_op)) THEN
2103 dummy_loop := TRUE;
2104 i := find_op(rework_out_rec.next_op);
2105 WHILE dummy_loop LOOP
2106 FOR next_rework_rec IN next_rework_cur(ntwk_op_tbl(i).operation_seq_id) LOOP
2107 IF is_dummy(find_op(next_rework_rec.next_op)) THEN
2108 i := find_op(next_rework_rec.next_op);
2109 ELSE
2110 dummy_loop := FALSE;
2111 rework_loop_ends_op := find_op(next_rework_rec.next_op);
2112 END IF;
2113 END LOOP;
2114 END LOOP; -- END OF dummy_loop
2115 ELSE
2116 rework_loop_ends_op := find_op(rework_out_rec.next_op);
2117 END IF;
2118 END IF;
2119 /* At this point rework_loop_ends has start of rework */
2120 /* Lets go now for the search of rework_effect_limit_op*/
2121 i := 0;
2122 IF(rework_loop_ends_op >0 ) THEN
2123 IF NOT on_primary_path(ntwk_op_tbl(sanity_counter).operation_seq_id) THEN
2124 FOR next_op_rec IN next_op_cur(ntwk_op_tbl(sanity_counter).operation_seq_id) LOOP
2125 IF NOT on_primary_path(next_op_rec.next_op) THEN
2126 non_primary_loop := TRUE;
2127 WHILE non_primary_loop LOOP
2128 IF(i = 0) THEN
2129 i := find_op(next_op_rec.next_op);
2130 END IF;
2131 IF ( i >= ntwk_op_tbl.COUNT) THEN
2132 raise TABLE_ENDED;
2133 END IF;
2134 FOR next_op_rec1 IN
2135 next_op_cur1(ntwk_op_tbl(i).operation_seq_id) LOOP
2136 IF NOT on_primary_path(next_op_rec1.next_op) THEN
2137 i := find_op(next_op_rec1.next_op);
2138 ELSE
2139 non_primary_loop := FALSE;
2140 rework_effect_limit_op := find_op(next_op_rec1.next_op);
2141 END IF;
2142 END LOOP;
2143 END LOOP; -- END OF non_primary_loop
2144 ELSE
2145 rework_effect_limit_op := find_op(next_op_rec.next_op);
2146 END IF;
2147 END LOOP;--END OF rework_effect_limit_op find LOOP
2148 ELSE
2149 rework_effect_limit_op := sanity_counter;
2150 END IF ; -- See if the operation from which the rework generates
2151 -- itself is on primary.
2152
2153 Rework_Effect_index := 1;
2154 rework_effect_tbl.DELETE;
2155 g_total_rework_prob := 0;
2156
2157 IF(rework_loop_ends_op < rework_effect_limit_op) THEN
2158 rework_effect_tbl(Rework_Effect_index).operation_seq_id :=
2159 ntwk_op_tbl(rework_loop_ends_op).operation_seq_id;
2160 collect_total_rework_prob(rework_loop_ends_op,rework_effect_limit_op,sanity_rework_pct);
2161 Rework_Effect_index := 1;
2162 collect_ops_between_rework(rework_loop_ends_op,rework_effect_limit_op,sanity_rework_pct);
2163 ELSE
2164 rework_effect_tbl(Rework_Effect_index).operation_seq_id :=
2165 ntwk_op_tbl(rework_effect_limit_op).operation_seq_id;
2166 collect_total_rework_prob(rework_loop_ends_op,rework_effect_limit_op,sanity_rework_pct);
2167 Rework_Effect_index := 1;
2168 collect_ops_between_rework(rework_loop_ends_op,rework_effect_limit_op,sanity_rework_pct);
2169 END IF;
2170 END IF;-- IF rework_loop_ends_op >0
2171 END LOOP;
2172 END IF;-- ONLY IF NO DUMMY
2173 END LOOP;
2174 END calc_npp_sanity_when_rework;
2175
2176 END BOM_CALC_CYNP;
[Home] [Help]