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