[Home] [Help]
PACKAGE BODY: APPS.BOM_RTG_EAM_UTIL
Source
1 PACKAGE BODY Bom_Rtg_Eam_Util AS
2 /* $Header: BOMREAMB.pls 115.6 2004/03/19 12:39:30 earumuga ship $ */
3 /****************************************************************************
4 --
5 -- Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMREAMB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package Bom_Rtg_Eam_Util : eAM utility for routing package
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 12-AUG-01 Masanori Kimizuka Initial Creation
21 --
22 ****************************************************************************/
23
24 G_Pkg_Name VARCHAR2(30) := 'Bom_Rtg_Eam_Util';
25
26 /*******************************************************************
27 * Procedure : CheckNwkExists
28 * Parameters IN : routing_sequence_id
29 * Parameters OUT: None
30 * Purpose : Procedure will return True if valid Rtg Network exists.
31 *********************************************************************/
32 FUNCTION CheckNwkExists( p_routing_sequence_id IN NUMBER )
33 RETURN BOOLEAN
34 IS
35
36 CURSOR c_Nwk_Exists IS
37 SELECT 'Valid Nwk Exists'
38 FROM BOM_OPERATION_NETWORKS bon
39 WHERE exists ( SELECT operation_sequence_id
40 FROM bom_operation_sequences bos
41 WHERE bos.operation_sequence_id = bon.from_op_seq_id
42 AND bos.routing_sequence_id = p_routing_sequence_id
43 AND bos.effectivity_date <= SYSDATE
44 AND nvl(bos.disable_date,SYSDATE+2) > SYSDATE
45 )
46 AND exists ( SELECT operation_sequence_id
47 FROM bom_operation_sequences bos2
48 WHERE bos2.operation_sequence_id = bon.to_op_seq_id
49 AND bos2.routing_sequence_id = p_routing_sequence_id
50 AND bos2.effectivity_date <= SYSDATE
51 AND nvl(bos2.disable_date,SYSDATE+2) > SYSDATE
52 )
53 AND nvl(bon.disable_date,SYSDATE+2) > SYSDATE ;
54
55 BEGIN
56
57 FOR x_Nwk IN c_Nwk_Exists
58 LOOP
59 RETURN TRUE ;
60 END LOOP ;
61
62 RETURN FALSE ;
63
64 END CheckNwkExists ;
65
66 /*******************************************************************
67 * Procedure : Get_All_Links
68 * Parameters IN : routing_sequence_id
69 * Parameters OUT: x_network_link_tbl
70 * Purpose : Procedure will return a PL/SQL table of
71 * Op_Nwk_Link_Tbl_Type type as OUT parameter
72 * that includes a list of all operation links for
73 * the routing network of eAM Maintenace Routing.
74 *********************************************************************/
75 PROCEDURE Get_All_Links
76 ( p_routing_sequence_id IN NUMBER
77 , x_network_link_tbl IN OUT NOCOPY Op_Nwk_Link_Tbl_Type
78 )
79 IS
80
81 i INTEGER := 1;
82
83 CURSOR c_All_Nwk_Links IS
84 SELECT bon.from_op_seq_id from_op_seq_id
85 , bos1.operation_seq_num from_op_seq_num
86 , bon.to_op_seq_id to_op_seq_id
87 , bos2.operation_seq_num to_op_seq_num
88 , bon.transition_type transition_type
89 , bon.planning_pct planning_pct
90 FROM BOM_OPERATION_NETWORKS bon
91 , BOM_OPERATION_SEQUENCES bos1
92 , BOM_OPERATION_SEQUENCES bos2
93 WHERE nvl(bon.disable_date,SYSDATE+2) > SYSDATE
94 AND bon.transition_type <> 3
95 AND bos2.effectivity_date <= SYSDATE
96 AND nvl(bos2.disable_date,SYSDATE+2) > SYSDATE
97 AND bos2.routing_sequence_id = p_routing_sequence_id
98 AND bos2.operation_sequence_id = bon.to_op_seq_id
99 AND bos1.effectivity_date <= SYSDATE
100 AND nvl(bos1.disable_date,SYSDATE+2) > SYSDATE
101 AND bos1.routing_sequence_id = p_routing_sequence_id
102 AND bos1.operation_sequence_id = bon.from_op_seq_id
103 ORDER BY from_op_seq_num ;
104
105
106 BEGIN
107 -- Op_Nwk_Link_Rec_Type columns
108 -- from_op_seq_id NUMBER
109 -- from_op_seq_num NUMBER
110 -- to_op_seq_Id NUMBER
111 -- transition_type NUMBER
112 -- planning_pct NUMBER
113 -- network_seq_num NUMBER
114 -- process_flag VARCHAR2
115
116 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
117 Error_Handler.Write_Debug( 'Getting All Links . . .' ) ;
118 END IF ;
119
120 FOR all_links_rec IN c_All_Nwk_Links LOOP
121 x_network_link_tbl(i).from_op_seq_id := all_links_rec.from_op_seq_id;
122 x_network_link_tbl(i).from_op_seq_num := all_links_rec.from_op_seq_num;
123 x_network_link_tbl(i).to_op_seq_id := all_links_rec.to_op_seq_id;
124 x_network_link_tbl(i).to_op_seq_num := all_links_rec.to_op_seq_num;
125 x_network_link_tbl(i).transition_type := all_links_rec.to_op_seq_id;
126 x_network_link_tbl(i).planning_pct := all_links_rec.to_op_seq_id;
127 x_network_link_tbl(i).network_seq_num := 0 ;
128 x_network_link_tbl(i).process_flag := 'C';
129
130
131
132 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
133 Error_Handler.Write_Debug( 'Row#'||to_char(i) || ' FROM : '
134 || to_char(x_network_link_tbl(i).from_op_seq_num)
135 || ' - '
136 || to_char(x_network_link_tbl(i).from_op_seq_id)
137 || ' TO : '
138 || to_char(x_network_link_tbl(i).to_op_seq_num)
139 || ' - '
140 || to_char(x_network_link_tbl(i).to_op_seq_id)
141 || ' Process : '
142 || x_network_link_tbl(i).process_flag
143 );
144
145 END IF ;
146
147 i := i + 1;
148 END LOOP;
149 END Get_All_Links ;
150
151 /*******************************************************************
152 * Procedure : Get_All_Start_Nodes
153 * Parameters IN : routing_sequence_id
154 * Parameters OUT: x_network_link_tbl
155 * Purpose : Procedure will return a PL/SQL table of
156 * Op_Nwk_Link_Tbl_Type type as OUT parameter
157 * that includes a list of all starting nodes for
158 * the routing network of eAM Maintenace Routing.
159 *********************************************************************/
160 PROCEDURE Get_All_Start_Nodes
161 ( p_routing_sequence_id IN NUMBER
162 , x_start_op_tbl IN OUT NOCOPY Op_Nwk_Link_Tbl_Type
163 )
164 IS
165
166 i INTEGER := 1;
167
168 CURSOR c_All_Start_Nodes IS
169 SELECT bon.from_op_seq_id from_op_seq_id
170 , bos1.operation_seq_num from_op_seq_num
171 , bon.to_op_seq_id to_op_seq_id
172 , bos2.operation_seq_num to_op_seq_num
173 , bon.transition_type transition_type
174 , bon.planning_pct planning_pct
175 FROM BOM_OPERATION_NETWORKS bon
176 , BOM_OPERATION_SEQUENCES bos1
177 , BOM_OPERATION_SEQUENCES bos2
178 WHERE NOT EXISTS ( SELECT NULL
179 FROM BOM_OPERATION_NETWORKS bon2
180 WHERE bon2.to_op_seq_id = bon.from_op_seq_id
181 )
182 AND nvl(bon.disable_date,SYSDATE+2) > SYSDATE
183 AND bon.transition_type <> 3
184 AND bos2.effectivity_date <= SYSDATE
185 AND nvl(bos2.disable_date,SYSDATE+2) > SYSDATE
186 AND bos2.routing_sequence_id = p_routing_sequence_id
187 AND bos2.operation_sequence_id = bon.to_op_seq_id
188 AND bos1.effectivity_date <= SYSDATE
189 AND nvl(bos1.disable_date,SYSDATE+2) > SYSDATE
190 AND bos1.routing_sequence_id = p_routing_sequence_id
191 AND bos1.operation_sequence_id = bon.from_op_seq_id
192 ORDER BY from_op_seq_num ;
193
194
195 BEGIN
196 -- Op_Nwk_Link_Rec_Type columns
197 -- from_op_seq_id NUMBER
198 -- from_op_seq_num NUMBER
199 -- to_op_seq_id NUMBER
200 -- to_op_seq_num NUMBER
201 -- transition_type NUMBER
202 -- planning_pct NUMBER
203 -- network_seq_num NUMBER
204 -- process_flag VARCHAR2
205
206 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
207 Error_Handler.Write_Debug( 'Getting All start nodes . . .' ) ;
208 END IF ;
209
210 FOR all_start_nodes_rec IN c_All_Start_Nodes LOOP
211 x_start_op_tbl(i).from_op_seq_id := all_start_nodes_rec.from_op_seq_id ;
212 x_start_op_tbl(i).from_op_seq_num := all_start_nodes_rec.from_op_seq_num ;
213 x_start_op_tbl(i).to_op_seq_id := all_start_nodes_rec.to_op_seq_id ;
214 x_start_op_tbl(i).to_op_seq_num := all_start_nodes_rec.to_op_seq_num ;
215 x_start_op_tbl(i).transition_type := all_start_nodes_rec.transition_type ;
216 x_start_op_tbl(i).planning_pct := all_start_nodes_rec.planning_pct ;
217 x_start_op_tbl(i).network_seq_num := i ;
218 x_start_op_tbl(i).process_flag := 'C';
219
220
221 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
222 Error_Handler.Write_Debug( 'Row#'||to_char(i) || ' FROM : '
223 || to_char(x_start_op_tbl(i).from_op_seq_num)
224 || ' - '
225 || to_char(x_start_op_tbl(i).from_op_seq_id)
226 || ' TO : '
227 || to_char(x_start_op_tbl(i).to_op_seq_num)
228 || ' - '
229 || to_char(x_start_op_tbl(i).to_op_seq_id)
230 || ' Process : '
231 || x_start_op_tbl(i).process_flag
232 );
233
234 END IF ;
235
236 i := i + 1;
237 END LOOP;
238 END Get_All_Start_Nodes ;
239
240
241 /*******************************************************************
242 * Procedure : Create_Connected_Op_List
243 * Parameters IN : Form Op Seq Id
244 * From Op Seq Num
245 * Parameters OUT: Connected nodes for eAM routing network
246 * Purpose : Procedure will return a PL/SQL table of
247 * Op_Nwk_Link_Tbl_Type type as OUT parameter
248 * that includes a list of all nodes for
249 * the routing network of eAM Maintenace Routing.
250 *********************************************************************/
251 FUNCTION Create_Connected_Op_List
252 ( p_from_op_seq_id IN NUMBER
253 , p_from_op_seq_hum IN NUMBER
254 , p_network_seq_num IN NUMBER
255 , x_connected_op_tbl IN OUT NOCOPY Op_Nwk_Link_Tbl_Type
256 )
257 RETURN BOOLEAN
258 IS
259
260
261 i INTEGER := 2 ;
262 l_from_op_seq_num NUMBER ;
263 l_to_op_seq_num NUMBER ;
264
265
266 CURSOR all_connected_ops IS
267 SELECT from_op_seq_id
268 , to_op_seq_id
269 , transition_type
270 , planning_pct
271 FROM BOM_OPERATION_NETWORKS
272 START WITH from_op_seq_id = p_from_op_seq_id
273 AND transition_type <> 3
274 CONNECT BY PRIOR to_op_seq_id = from_op_seq_id
275 AND transition_type <> 3;
276
277 BEGIN
278 -- create a list of connected nodes starting from the start node
279 -- add the start node
280
281
282 x_connected_op_tbl(1).from_op_seq_id := p_from_op_seq_id ;
283 x_connected_op_tbl(1).from_op_seq_num := p_from_op_seq_hum ;
284 x_connected_op_tbl(1).process_flag := 'S' ;
285
286 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
287 Error_Handler.Write_Debug('Connected op list : Start Node '
288 || to_char(x_connected_op_tbl(1).from_op_seq_num)
289 || ' - '
290 || to_char(x_connected_op_tbl(1).from_op_seq_id)
291 );
292
293 END IF ;
294
295 FOR all_connected_op_rec IN all_connected_ops LOOP
296 BEGIN
297 SELECT operation_seq_num
298 INTO l_from_op_seq_num
299 FROM bom_operation_sequences
300 WHERE operation_sequence_id =
301 all_connected_op_rec.from_op_seq_id ;
302
303 SELECT operation_seq_num
304 INTO l_to_op_seq_num
305 FROM bom_operation_sequences
306 WHERE operation_sequence_id =
307 all_connected_op_rec.to_op_seq_id ;
308
309
310 END ;
311
312 x_connected_op_tbl(i).from_op_seq_id := all_connected_op_rec.from_op_seq_id;
313 x_connected_op_tbl(i).from_op_seq_num := l_from_op_seq_num ;
314 x_connected_op_tbl(i).to_op_seq_id := all_connected_op_rec.to_op_seq_id;
315 x_connected_op_tbl(i).to_op_seq_num := l_to_op_seq_num ;
316 x_connected_op_tbl(i).transition_type := all_connected_op_rec.transition_type;
317 x_connected_op_tbl(i).planning_pct := all_connected_op_rec.planning_pct ;
318 x_connected_op_tbl(i).network_seq_num := p_network_seq_num ;
319 x_connected_op_tbl(i).process_flag := 'C' ;
320
321 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
322 Error_Handler.Write_Debug( 'Row#'||to_char(i) || ' FROM : '
323 || to_char(x_connected_op_tbl(i).from_op_seq_num)
324 || ' - '
325 || to_char(x_connected_op_tbl(i).from_op_seq_id)
326 || ' TO : '
327 || to_char(x_connected_op_tbl(i).to_op_seq_num)
328 || ' - '
329 || to_char(x_connected_op_tbl(i).to_op_seq_id)
330 );
331
332 END IF ;
333
334 i := i + 1;
335
336 END LOOP;
337
338
339 RETURN TRUE ;
340
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN
343
344 RETURN TRUE ;
345
346 WHEN OTHERS THEN
347 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
348 Error_Handler.Write_Debug
349 ( 'When creating connected op list, Some unknown error in Check Access. . .' || SQLERRM ) ;
350 END IF ;
351
352 -- RETURN FALSE ;
353 -- This error can be catched in following processes.
354
355 RETURN TRUE ;
356
357 END Create_Connected_Op_List ;
358
359
360 /*******************************************************************
361 * Fucntion : IsConnected
362 * Return : BOOLEAN
363 * Parameters IN : Connected Op Tbl
364 * Master Op Tbl
365 * Parameters OUT: None
366 * Purpose : Function to to see if there are any rows common
367 * in the two tables.
368 * i.e. if the two tables are connected.
369 * TRUE, if connected table is connected to master tble;
370 * FALSE, otherwise.
371 *********************************************************************/
372 FUNCTION IsConnected
373 ( p_connected_op_tbl IN Op_Nwk_Link_Tbl_Type
374 , p_master_op_tbl IN Op_Nwk_Link_Tbl_Type
375 )
376 RETURN BOOLEAN
377 IS
378 i NUMBER ;
379 j NUMBER ;
380
381 BEGIN
382
383 FOR i IN 1..p_master_op_tbl.COUNT LOOP
384 FOR j IN 1..p_connected_op_tbl.COUNT LOOP
388 THEN
385 IF p_master_op_tbl(i).from_op_seq_num
386 = p_connected_op_tbl(j).from_op_seq_num
387 AND p_connected_op_tbl(j).process_flag = 'C'
389 RETURN (TRUE);
390 END IF;
391 END LOOP;
392 END LOOP;
393
394 RETURN (FALSE);
395
396 END IsConnected ;
397
398
399 /*******************************************************************
400 * Fucntion : CheckOpNwkNodeExists
401 * Parameters IN :
402 * Master Op Tbl
403 * Parameters OUT: None
404 * Purpose : This procedure appends Connected Op Tbl to
405 * Master OP tbl making sure that there is no redundancy,
406 * and then return the master list of the connected nodes
407 *********************************************************************/
408 FUNCTION CheckOpNwkNodeExists
409 ( p_from_op_seq_num IN NUMBER
410 , p_to_op_seq_num IN NUMBER
411 , p_op_nwk_tbl IN Op_Nwk_Link_Tbl_Type
412 ) RETURN BOOLEAN
413 IS
414
415 i NUMBER ;
416
417 BEGIN
418 FOR i IN 1..p_op_nwk_tbl.COUNT LOOP
419 IF p_op_nwk_tbl(i).from_op_seq_num = p_from_op_seq_num
420 AND p_op_nwk_tbl(i).to_op_seq_num = p_to_op_seq_num
421 THEN
422 RETURN (TRUE);
423 END IF;
424 END LOOP;
425
426 RETURN (FALSE);
427
428 END CheckOpNwkNodeExists ;
429
430
431
432 /*******************************************************************
433 * Procedure : AppendToMasterList
434 * Parameters IN : Connected Op Tbl
435 * Master Op Tbl
436 * Parameters OUT: None
437 * Purpose : This procedure appends Connected Op Tbl to
438 * Master OP tbl making sure that there is no redundancy,
439 * and then return the master list of the connected nodes
440 *********************************************************************/
441 PROCEDURE AppendToMasterList
442 ( p_connected_op_tbl IN Op_Nwk_Link_Tbl_Type
443 , p_master_op_tbl IN Op_Nwk_Link_Tbl_Type
444 , x_master_op_tbl IN OUT NOCOPY Op_Nwk_Link_Tbl_Type
445 )
446 IS
447
448 i NUMBER ;
449 mst_c NUMBER ;
450
451 BEGIN
452 x_master_op_tbl := p_master_op_tbl ;
453 mst_c := x_master_op_tbl.COUNT + 1 ;
454
455
456 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
457 Error_Handler.Write_Debug('Appending an op link to master op list . .' );
458 END IF ;
459
460
461 FOR i IN 1..p_connected_op_tbl.COUNT LOOP
462
463 IF NOT CheckOpNwkNodeExists
464 ( p_from_op_seq_num => p_connected_op_tbl(i).from_op_seq_num
465 , p_to_op_seq_num => p_connected_op_tbl(i).to_op_seq_num
466 , p_op_nwk_tbl => x_master_op_tbl
467 )
468 AND p_connected_op_tbl(i).process_flag <> 'S'
469 THEN
470 x_master_op_tbl(mst_c).from_op_seq_id :=
471 p_connected_op_tbl(i).from_op_seq_id ;
472 x_master_op_tbl(mst_c).from_op_seq_num :=
473 p_connected_op_tbl(i).from_op_seq_num ;
474 x_master_op_tbl(mst_c).to_op_seq_id :=
475 p_connected_op_tbl(i).to_op_seq_id ;
476 x_master_op_tbl(mst_c).to_op_seq_num :=
477 p_connected_op_tbl(i).to_op_seq_num ;
478 x_master_op_tbl(mst_c).transition_type :=
479 p_connected_op_tbl(i).transition_type ;
480 x_master_op_tbl(mst_c).planning_pct :=
481 p_connected_op_tbl(i).planning_pct ;
482 x_master_op_tbl(mst_c).network_seq_num :=
483 p_connected_op_tbl(i).network_seq_num ;
484 x_master_op_tbl(mst_c).process_flag :=
485 p_connected_op_tbl(i).process_flag ;
486
487 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
488 Error_Handler.Write_Debug( 'Row#'||to_char(mst_c) || ' FROM : '
489 || to_char(x_master_op_tbl(mst_c).from_op_seq_num)
490 || ' - '
491 || to_char(x_master_op_tbl(mst_c).from_op_seq_id)
492 || ' TO : '
493 || to_char(x_master_op_tbl(mst_c).to_op_seq_num)
494 || ' - '
495 || to_char(x_master_op_tbl(mst_c).to_op_seq_id)
496 || ' Process : '
497 || x_master_op_tbl(mst_c).process_flag
498 || ' Nwk Seq : '
499 || to_char(x_master_op_tbl(mst_c).network_seq_num)
500 );
501
502 END IF ;
503
504
505 mst_c := mst_c + 1;
506 END IF;
507 END LOOP;
508
509 END AppendToMasterList ;
510
511
512
513
514 /*******************************************************************
515 * Function : GetTopRefPointer
516 * Parameters IN : Op Nwk Link tbl
517 * Parameters OUT: None
518 * Purpose : This function will get the reference position
519 * which is the first row from the top of the table
520 * with flag = 'C'.
524 FUNCTION GetTopRefPointer
521 * Then It will return reference, if there exists
522 * rows with flag = 'C'; otherwise -1.
523 *********************************************************************/
525 ( p_network_link_tbl IN Op_Nwk_Link_Tbl_Type )
526 RETURN NUMBER
527 IS
528
529 BEGIN
530 FOR i IN 1..p_network_link_tbl.COUNT LOOP
531 IF p_network_link_tbl(i).process_flag = 'C' THEN
532 RETURN (i);
533 END IF;
534 END LOOP;
535 RETURN (-1);
536 END GetTopRefPointer ;
537
538
539 /*******************************************************************
540 * Function : GetBottomRefPointer
541 * Parameters IN : Op Nwk Link tbl
542 * Parameters OUT: None
543 * Purpose : This function will get the reference position
544 * which is the first row from the bottom of the table
545 * with flag = 'C'.
546 * Then It will return reference, if there exists
547 * rows with flag = 'C'; otherwise -1.
548 *********************************************************************/
549 FUNCTION GetBottomRefPointer
550 ( p_network_link_tbl IN Op_Nwk_Link_Tbl_Type )
551 RETURN NUMBER
552 IS
553
554 BEGIN
555 FOR i IN REVERSE 1..p_network_link_tbl.COUNT LOOP
556 IF p_network_link_tbl(i).process_flag = 'C' THEN
557 RETURN (i);
558 END IF;
559 END LOOP;
560 RETURN (-1);
561 END GetBottomRefPointer ;
562
563 /*******************************************************************
564 * Procedure : CheckLoopNwk
565 * Parameters IN : Op Network Tbl Tbl
566 * Master Op Tbl
567 * Parameters OUT: None
568 * Purpose : This procedure will check if loop exists.
569 *********************************************************************/
570 FUNCTION CheckLoopNwk
571 ( p_network_link_tbl IN Op_Nwk_Link_Tbl_Type )
572 RETURN BOOLEAN
573 IS
574 l_network_link_tbl Op_Nwk_Link_Tbl_Type ;
575 l_top_ptr NUMBER ;
576 l_bot_ptr NUMBER ;
577 l_connected NUMBER ;
578 l_flag_changed NUMBER ;
579
580 BEGIN
581 l_network_link_tbl := p_network_link_tbl ;
582
583 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
584 Error_Handler.Write_Debug('Within CheckLoopAPI . . .');
585 END IF;
586
587 -- Get the top and bottom reference pointers
588 l_top_ptr := GetTopRefPointer( l_network_link_tbl ) ;
589 l_bot_ptr := GetBottomRefPointer( l_network_link_tbl ) ;
590
591
592 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
593 Error_Handler.Write_Debug('Get op network node. top_ptr: '
594 || to_char(l_top_ptr)
595 || ' - ' || 'bot_ptr: '|| to_char(l_bot_ptr));
596 END IF;
597
598 -- Initialize chang flag
599 l_flag_changed := 1 ;
600
601 -- While there are rows with flag='C'
602 WHILE (l_top_ptr <> -1 and l_bot_ptr <> -1 and l_flag_changed = 1) LOOP
603 l_flag_changed := 0;
604 FOR i IN 1..l_network_link_tbl.COUNT LOOP
605 IF l_network_link_tbl(i).process_flag = 'C' THEN
606 l_connected := 0;
607
608 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
609 Error_Handler.Write_Debug('in loop1 :'||to_char(i)||' time');
610 END IF;
611
612 FOR j IN 1..l_network_link_tbl.COUNT LOOP
613 IF l_network_link_tbl(i).from_op_seq_id
614 = l_network_link_tbl(j).to_op_seq_id
615 AND l_network_link_tbl(j).process_flag = 'C'
616 THEN
617 l_connected := 1 ;
618 END IF;
619 END LOOP;
620
621 IF (l_connected = 0) THEN
622 l_network_link_tbl(i).process_flag := 'D';
623 l_flag_changed := 1;
624
625 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
626 Error_Handler.Write_Debug('Changing row#'||to_char(i)||' '||'flag to D');
627 END IF;
628
629 END IF;
630 END IF;
631 END LOOP;
632
633 -- Get the top and bottom reference pointers
634 l_top_ptr := GetTopRefPointer( l_network_link_tbl ) ;
635 l_bot_ptr := GetBottomRefPointer( l_network_link_tbl ) ;
636
637 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
638 Error_Handler.Write_Debug('top_ptr:'|| to_char(l_top_ptr));
639 Error_Handler.Write_Debug('bot_ptr:'|| to_char(l_bot_ptr));
640 END IF;
641
642
643 l_flag_changed := 0;
644 IF (l_top_ptr <> -1 and l_bot_ptr <> -1) THEN
645 FOR i IN REVERSE l_top_ptr..l_bot_ptr LOOP
646 IF l_network_link_tbl(i).process_flag = 'C' THEN
647 l_connected := 0;
648
649 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
650 Error_Handler.Write_Debug('in loop2 :'||to_char(i)||' time');
651 END IF;
652
653
654 FOR j IN REVERSE l_top_ptr..l_bot_ptr LOOP
655 IF l_network_link_tbl(i).to_op_seq_id
656 = l_network_link_tbl(j).from_op_seq_id
657 AND l_network_link_tbl(j).process_flag = 'C'
661 END LOOP;
658 THEN
659 l_connected := 1;
660 END IF;
662
663 IF (l_connected = 0) THEN
664 l_network_link_tbl(i).process_flag := 'D';
665 l_flag_changed := 1;
666
667 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
668 Error_Handler.Write_Debug('Changing row#'||to_char(i)||' '||'flag to D');
669 END IF;
670 END IF;
671 END IF;
672 END LOOP;
673 END IF;
674
675 -- Get the top and bottom reference pointers
676 l_top_ptr := GetTopRefPointer( l_network_link_tbl ) ;
677 l_bot_ptr := GetBottomRefPointer( l_network_link_tbl ) ;
678
679 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
680 Error_Handler.Write_Debug('top_ptr:'|| to_char(l_top_ptr));
681 Error_Handler.Write_Debug('bot_ptr:'|| to_char(l_bot_ptr));
682 END IF;
683
684 END LOOP; -- while
685
686 IF (l_top_ptr <> -1 or l_bot_ptr <> -1) THEN
687
688 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
689 Error_Handler.Write_Debug('Error. A loop has been detected');
690 END IF;
691
692 RETURN FALSE ;
693 END IF;
694
695 RETURN TRUE ;
696
697 END CheckLoopNwk ;
698
699
700
701 /*******************************************************************
702 * Procedure : Check_Eam_Rtg_Network
703 * Parameters IN : Routing Sequence_id
704 * Parameters OUT: Error Code
705 * Return Status
706 * Purpose : Procedure will validate for eAM Rtg Network.
707 * This procedure is called by Routing BO and BOMFDONW form
708 *********************************************************************/
709 PROCEDURE Check_Eam_Rtg_Network
710 ( p_routing_sequence_id IN NUMBER
711 , x_err_msg IN OUT NOCOPY VARCHAR2
712 , x_return_status IN OUT NOCOPY VARCHAR2
713 )
714 IS
715 l_network_link_tbl Op_Nwk_Link_Tbl_Type ;
716 l_each_nwk_link_tbl Op_Nwk_Link_Tbl_Type ;
717 l_start_op_tbl Op_Nwk_Link_Tbl_Type ;
718 l_connected_op_tbl Op_Nwk_Link_Tbl_Type ;
719 l_master_op_tbl Op_Nwk_Link_Tbl_Type ;
720 l_LoopIndex NUMBER ;
721 nwk_c NUMBER ;
722 l_mst_c NUMBER ;
723 l_seq_num NUMBER ;
724 l_connected_flag BOOLEAN ;
725
726 BEGIN
727
728 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
729 Error_Handler.Write_Debug('Within eAM Rtg Network validation API. . . ');
730 END IF;
731
732
733 -- Return Status
734 -- Success : FND_API.G_RET_STS_SUCCESS
735 -- Error : FND_API.G_RET_STS_ERROR
736 -- Unexpected Error : G_RET_STS_UNEXP_ERROR
737
738 x_return_status := FND_API.G_RET_STS_SUCCESS ;
739
740 -- Check if valid eAM routing network exists
741 IF NOT CheckNwkExists( p_routing_sequence_id => p_routing_sequence_id )
742 THEN
743 FND_MESSAGE.SET_NAME('BOM','BOM_EAM_NO_NETWORK_EXIST');
744 x_err_msg:= FND_MESSAGE.GET;
745 -- Commented as part of bug#3460975. This is not an error. It should be treated as warning
746 -- because a routing may or may not have a link.
747 -- x_return_status := FND_API.G_RET_STS_ERROR ;
748
749 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
750 Error_Handler.Write_Debug('Check if Op Network exists. . . ' || x_return_status );
751 END IF;
752
753 -- Commented as part of bug#3460975. Since this is a warning don't return.
754 -- RETURN ;
755 END IF;
756
757
758 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
759 Error_Handler.Write_Debug('Get network info and import the data into the PL/SQL table . . . ' );
760 END IF;
761
762 Get_All_Links
763 ( p_routing_sequence_id => p_routing_sequence_id
764 , x_network_link_tbl => l_network_link_tbl
765 ) ;
766
767 Get_All_Start_Nodes
768 ( p_routing_sequence_id => p_routing_sequence_id
769 , x_start_op_tbl => l_start_op_tbl
770 ) ;
771
772
773 -- Check for BROKEN LINKS
774 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
775 Error_Handler.Write_Debug('Check for broken links. . . ' );
776 END IF;
777
778 -- In each start node, get the nodes connected to,then compare with
779 -- the master list to see if there is any common node i.e. connected.
780 -- if there is, add the nodes to the master list. if there is none,
781 -- this is the broken link; print the broken link.
782
783 FOR i IN 1..l_start_op_tbl.COUNT LOOP
784
785 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
786 Error_Handler.Write_Debug('Creating connected op list : '
787 || to_char(l_start_op_tbl(i).from_op_seq_num)
788 || ' - '
789 || to_char(l_start_op_tbl(i).from_op_seq_id)
790 );
791
792 END IF ;
793
794 -- add the starting node into the Master list
795 IF NOT CheckOpNwkNodeExists
796 ( p_from_op_seq_num => l_start_op_tbl(i).from_op_seq_num
800 THEN
797 , p_to_op_seq_num => l_start_op_tbl(i).to_op_seq_num
798 , p_op_nwk_tbl => l_master_op_tbl
799 )
801 l_mst_c := l_master_op_tbl.COUNT + 1 ;
802 l_master_op_tbl(l_mst_c).from_op_seq_id := l_start_op_tbl(i).from_op_seq_id;
803 l_master_op_tbl(l_mst_c).from_op_seq_num := l_start_op_tbl(i).from_op_seq_num;
804 l_master_op_tbl(l_mst_c).process_flag := 'S' ;
805 l_master_op_tbl(l_mst_c).network_seq_num := i ;
806
807 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
808 Error_Handler.Write_Debug('Added start node to Master Op Nwk Tbl . . . ' );
809 Error_Handler.Write_Debug('Mst : Row# ' || to_char(l_mst_c) || ' = '
810 || to_char(l_master_op_tbl(l_mst_c).from_op_seq_num)
811 || ' - ' || to_char(l_master_op_tbl(l_mst_c).from_op_seq_id)
812 );
813 END IF;
814
815
816 END IF;
817
818
819 l_connected_flag :=
820 Create_Connected_Op_List
821 ( p_from_op_seq_id => l_start_op_tbl(i).from_op_seq_id
822 , p_from_op_seq_hum => l_start_op_tbl(i).from_op_seq_num
823 , p_network_seq_num => i
824 , x_connected_op_tbl => l_connected_op_tbl
825 ) ;
826
827 IF NOT l_connected_flag THEN
828
829 FND_MESSAGE.SET_NAME('BOM','BOM_EAM_RTG_INVALID_LINK_EXIST');
830 x_err_msg:= FND_MESSAGE.GET;
831 x_return_status := FND_API.G_RET_STS_ERROR ;
832 RETURN ;
833
834 END IF ;
835
836 IF IsConnected(l_connected_op_tbl, l_master_op_tbl) THEN
837
838 -- add the nodes to the master list
839 AppendToMasterList
840 ( p_connected_op_tbl => l_connected_op_tbl
841 , p_master_op_tbl => l_master_op_tbl
842 , x_master_op_tbl => l_master_op_tbl
843 ) ;
844
845 -- delete the connected list for re-use
846 l_connected_op_tbl.DELETE;
847
848 ELSE
849 FND_MESSAGE.SET_NAME('BOM','BOM_RTG_NTWK_BROKEN_LINK_EXIST');
850 x_err_msg:= FND_MESSAGE.GET;
851 x_return_status := FND_API.G_RET_STS_ERROR ;
852 RETURN ;
853 END IF;
854 END LOOP;
855
856
857 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
858 Error_Handler.Write_Debug('Check all links in l_network_link_tbl exists in Master Op Tbl. . . ' );
859 END IF;
860
861 -- Check all links in l_network_link_tbl exists in Master Op Tbl
862 FOR i IN 1..l_network_link_tbl.COUNT
863 LOOP
864
865 IF NOT CheckOpNwkNodeExists
866 ( p_from_op_seq_num => l_network_link_tbl(i).from_op_seq_num
867 , p_to_op_seq_num => l_network_link_tbl(i).to_op_seq_num
868 , p_op_nwk_tbl => l_master_op_tbl
869 )
870 THEN
871
872 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
873 Error_Handler.Write_Debug('Error Broken Link exists on Nwk Row# '
874 || to_char(i) || ' - FROM : '
875 || to_char(l_network_link_tbl(i).from_op_seq_num)
876 || ' TO : '
877 || to_char(l_network_link_tbl(i).to_op_seq_num)
878 );
879 END IF;
880
881 FND_MESSAGE.SET_NAME('BOM','BOM_EAM_RTG_INVALID_LINK_EXIST');
882 x_err_msg:= FND_MESSAGE.GET;
883 x_return_status := FND_API.G_RET_STS_ERROR ;
884 RETURN ;
885 END IF ;
886 END LOOP ;
887
888
889 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
890 Error_Handler.Write_Debug('Check all connected links in Master Op tbl exists in all links. . . ' );
891 END IF;
892
893 -- Check all connected links in Master Op tbl exists in all links
894 FOR i IN 1..l_master_op_tbl.COUNT
895 LOOP
896
897 IF NOT CheckOpNwkNodeExists
898 ( p_from_op_seq_num => l_master_op_tbl(i).from_op_seq_num
899 , p_to_op_seq_num => l_master_op_tbl(i).to_op_seq_num
900 , p_op_nwk_tbl => l_network_link_tbl
901 )
902 AND l_master_op_tbl(i).process_flag <> 'S'
903 THEN
904
905 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
906 Error_Handler.Write_Debug('Error Broken Link exists on Master Row# '
907 || to_char(i) || ' - '
908 || to_char(l_master_op_tbl(i).from_op_seq_num)
909 );
910 END IF;
911
912 FND_MESSAGE.SET_NAME('BOM','BOM_EAM_RTG_INVALID_LINK_EXIST');
913 x_err_msg:= FND_MESSAGE.GET;
914 x_return_status := FND_API.G_RET_STS_ERROR ;
915 RETURN ;
916 END IF ;
917 END LOOP ;
918
919
920 -- Check for Loops
921 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
922 Error_Handler.Write_Debug('Check for loops. . . ' );
923 END IF;
924
925
929 l_seq_num := 1 ;
926 -- get a group of network links from master op tbl for multiple start node.
927 -- Initialize flag
928 nwk_c := 1 ;
930
931 -- Check for Loops
932 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
933 Error_Handler.Write_Debug('Get a group of network links from master op tbl for multiple start node. . . ' );
934 END IF;
935
936
937 FOR i IN 1..l_master_op_tbl.COUNT
938 LOOP
939
940
941 -- When Nwk Seq num is chanaged, check loop.
942 IF l_seq_num <> l_master_op_tbl(i).network_seq_num
943 AND l_master_op_tbl(i).process_flag <> 'S'
944 THEN
945 -- Check loop
946
947 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
948 Error_Handler.Write_Debug('When nwk seq is changed, Check Loop for Network Link Seq Num ' || to_char(l_seq_num) );
949 END IF;
950 IF CheckLoopNwk (p_network_link_tbl => l_each_nwk_link_tbl)
951 THEN
952 -- Initialize nwk_c counter and l_each_nwk_link_tbl
953 nwk_c := 1 ;
954 l_each_nwk_link_tbl.DELETE ;
955
956 ELSE
957
958
959 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
960 Error_Handler.Write_Debug('Loop exists in operation network : seq = ' || to_char(l_seq_num) );
961 END IF;
962
963 FND_MESSAGE.SET_NAME('BOM','BOM_RTG_NTWK_LOOP_EXISTS');
964 x_err_msg:= FND_MESSAGE.GET;
965 x_return_status := FND_API.G_RET_STS_ERROR ;
966 RETURN ;
967 END IF ;
968
969 END IF ;
970
971
972 IF l_master_op_tbl(i).process_flag <> 'S'
973 THEN
974
975 l_each_nwk_link_tbl(nwk_c).from_op_seq_id :=
976 l_master_op_tbl(i).from_op_seq_id ;
977 l_each_nwk_link_tbl(nwk_c).from_op_seq_num :=
978 l_master_op_tbl(i).from_op_seq_num ;
979 l_each_nwk_link_tbl(nwk_c).to_op_seq_id :=
980 l_master_op_tbl(i).to_op_seq_id ;
981 l_each_nwk_link_tbl(nwk_c).to_op_seq_num :=
982 l_master_op_tbl(i).to_op_seq_num ;
983 l_each_nwk_link_tbl(nwk_c).transition_type :=
984 l_master_op_tbl(i).transition_type ;
985 l_each_nwk_link_tbl(nwk_c).planning_pct :=
986 l_master_op_tbl(i).planning_pct ;
987 l_each_nwk_link_tbl(nwk_c).network_seq_num :=
988 l_master_op_tbl(i).network_seq_num ;
989 l_each_nwk_link_tbl(nwk_c).process_flag :=
990 l_master_op_tbl(i).process_flag ;
991
992 l_seq_num := l_master_op_tbl(i).network_seq_num ;
993 nwk_c := nwk_c + 1 ;
994 END IF ;
995
996 END LOOP ;
997
998 -- Check Loop
999 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
1000 Error_Handler.Write_Debug('At the end of loop validation, Check loops for rest of network : seq = '
1001 || to_char(l_seq_num) );
1002 END IF ;
1003
1004 IF CheckLoopNwk (p_network_link_tbl => l_each_nwk_link_tbl)
1005 THEN
1006 l_each_nwk_link_tbl.DELETE ;
1007
1008 ELSE
1009
1010 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
1011 Error_Handler.Write_Debug('Loop exists in operation network : seq = '
1012 || to_char(l_seq_num) );
1013 END IF ;
1014
1015 FND_MESSAGE.SET_NAME('BOM','BOM_RTG_NTWK_LOOP_EXISTS');
1016 x_err_msg:= FND_MESSAGE.GET;
1017 x_return_status := FND_API.G_RET_STS_ERROR ;
1018 RETURN ;
1019 END IF ;
1020
1021
1022
1023 END Check_Eam_Rtg_Network ;
1024
1025
1026 /*******************************************************************
1027 * Procedure : Check_Eam_Rtg_Network
1028 * Parameters IN : Operation Network Exposed Record
1029 * Operation Network Unexposed Record
1030 * Old Operation Network exposed Record
1031 * Old Operation Network Unexposed Record
1032 * Mesg Token Table
1033 * Parameters OUT: Mesg Token Table
1034 * Return Status
1035 * Purpose : Procedure will validate for eAM Rtg Network.
1036 * This procedure is called by Routing BO and BOMFDONW form
1037 *********************************************************************/
1038 PROCEDURE Check_Eam_Rtg_Network
1039 ( p_op_network_rec IN Bom_Rtg_Pub.Op_Network_Rec_Type
1040 , p_op_network_unexp_rec IN Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type
1041 , p_old_op_network_rec IN Bom_Rtg_Pub.Op_Network_Rec_Type
1042 , p_old_op_network_unexp_rec IN Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type
1043 , p_mesg_token_tbl IN Error_Handler.Mesg_Token_Tbl_Type
1044 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1045 , x_return_status IN OUT NOCOPY VARCHAR2
1046 )
1047 IS
1048
1049 l_token_tbl Error_Handler.Token_Tbl_Type;
1050 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
1051 l_return_status VARCHAR2(1);
1052 l_cfm_flag NUMBER ;
1053
1054 BEGIN
1055
1056 NULL ;
1057
1058 END Check_Eam_Rtg_Network ;
1059
1060
1061
1062
1066 * From Op Seq Id
1063 /*******************************************************************
1064 * Procedure : Check_Eam_Nwk_FromOp
1065 * Parameters IN : From Op Seq Num
1067 * To Op Seq Num
1068 * To Op Seq Id
1069 * Parameters OUT: Error Code
1070 * Return Status
1071 * Purpose : Procedure will validate for from operation in eAM Rtg Network.
1072 *********************************************************************/
1073 PROCEDURE Check_Eam_Nwk_FromOp
1074 ( p_from_op_seq_num IN NUMBER
1075 , p_from_op_seq_id IN NUMBER
1076 , p_to_op_seq_num IN NUMBER
1077 , p_to_op_seq_id IN NUMBER
1078 , x_err_msg IN OUT NOCOPY VARCHAR2
1079 , x_return_status IN OUT NOCOPY VARCHAR2
1080 )
1081 IS
1082
1083 Cursor c_op_network ( P_From_Op_Seq_Id number
1084 , P_To_Op_Seq_Id number)
1085 IS
1086 SELECT 'x' dummy
1087 FROM DUAL
1088 WHERE EXISTS
1089 ( SELECT NULL
1090 FROM bom_operation_networks a
1091 WHERE a.from_op_seq_id = P_From_Op_Seq_Id
1092 AND a.to_op_seq_id <> P_To_Op_Seq_Id
1093 AND a.transition_type = 1
1094 );
1095
1096 BEGIN
1097
1098 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
1099 Error_Handler.Write_Debug('Within eAM Rtg Network from operation validation API. . . ');
1100 END IF;
1101
1102
1103 -- Return Status
1104 -- Success : FND_API.G_RET_STS_SUCCESS
1105 -- Error : FND_API.G_RET_STS_ERROR
1106 -- Unexpected Error : G_RET_STS_UNEXP_ERROR
1107
1108 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1109
1110 FOR l_opnet_rec in c_op_network
1111 ( P_From_Op_Seq_Id => p_from_op_seq_id,
1112 P_To_Op_Seq_Id => p_to_op_seq_id
1113 )
1114 LOOP
1115
1116
1117 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
1118 Error_Handler.Write_Debug('from operation is duplicate in operation network : seq = '
1119 || to_char(p_from_op_seq_num) );
1120 END IF ;
1121
1122 FND_MESSAGE.SET_NAME('BOM','BOM_OP_NWK_PMOP_NOTUNIQUE');
1123 FND_MESSAGE.SET_TOKEN('FROM_OP_SEQ_NUMBER', p_from_op_seq_num) ;
1124 x_err_msg:= FND_MESSAGE.GET;
1125 x_return_status := FND_API.G_RET_STS_ERROR ;
1126
1127 END LOOP ;
1128
1129
1130
1131 END Check_Eam_Nwk_FromOp ;
1132
1133
1134 /***************************************************************************
1135 * Function : OrgIsEamEnabled
1136 * Returns : VARCHAR2
1137 * Parameters IN : p_org_id
1138 * Parameters OUT: None
1139 * Purpose : Function will return the value of 'Y' or 'N'
1140 * to check if organization is eAM enabled.
1141 *****************************************************************************/
1142 FUNCTION OrgIsEamEnabled(p_org_id NUMBER) RETURN VARCHAR2
1143 IS
1144
1145 CURSOR GetEamEnabledFlag IS
1146 SELECT NVL(eam_enabled_flag, 'N') eam_enabled_flag
1147 FROM mtl_parameters
1148 WHERE organization_id = p_org_id ;
1149
1150
1151 x_eam_enabled_flag VARCHAR2(1) ;
1152
1153 BEGIN
1154
1155 FOR X_Eam in GetEamEnabledFlag LOOP
1156 x_eam_enabled_flag := X_Eam.eam_enabled_flag ;
1157 END LOOP;
1158
1159 RETURN x_eam_enabled_flag ;
1160
1161 END OrgIsEamEnabled ;
1162
1163 /***************************************************************************
1164 * Function : CheckShutdownType
1165 * Returns : BOOLEAN
1166 * Parameters IN : p_shutdown_type
1167 * Parameters OUT: None
1168 * Purpose : Function will return the value of True or False
1169 * to check if ShutdownType is valid.
1170 *****************************************************************************/
1171 FUNCTION CheckShutdownType(p_shutdown_type IN VARCHAR2 )
1172 RETURN BOOLEAN
1173 IS
1174
1175 CURSOR CheckShutdownTypeCode IS
1176 SELECT 'Valid'
1177 FROM MFG_LOOKUPS
1178 WHERE lookup_code = TO_NUMBER(p_shutdown_type)
1179 AND lookup_type = 'BOM_EAM_SHUTDOWN_TYPE' ;
1180
1181
1182
1183 BEGIN
1184
1185 FOR X_Shutdown in CheckShutdownTypeCode LOOP
1186 RETURN TRUE ;
1187 END LOOP;
1188
1189 RETURN FALSE ;
1190
1191 END CheckShutdownType ;
1192
1193
1194 /***************************************************************************
1195 * Function : Check_UpdateDept
1196 * Returns : BOOLEAN
1197 * Parameters IN : p_op_seq_id, p_org_id, p_dept_id
1198 * Parameters OUT: None
1199 * Purpose : Function will return the value of True or False
1200 * to check if user can update the department for this operation.
1201 *****************************************************************************/
1202 FUNCTION Check_UpdateDept
1203 ( p_op_seq_id IN NUMBER
1204 , p_org_id IN NUMBER
1205 , p_dept_id IN NUMBER
1206 )
1207 RETURN BOOLEAN
1208 IS
1209
1210 CURSOR Check_DeptResource IS
1211 SELECT 'This dept is updatable'
1212 FROM BOM_DEPARTMENTS bd
1216 AND (
1213 WHERE trunc(nvl(bd.disable_date, sysdate + 1)) > trunc(sysdate)
1214 AND bd.department_id = p_dept_id
1215 AND bd.organization_id = p_org_id
1217 -- (NOT EXISTS (SELECT NULL
1218 -- FROM BOM_OPERATION_RESOURCES bor
1219 -- WHERE bor.operation_sequence_id = NVL(p_op_seq_id, -1)
1220 -- )
1221 -- ) OR
1222 ( NOT EXISTS ( SELECT 'Dept Invalid'
1223 FROM BOM_OPERATION_RESOURCES bor2
1224 WHERE bor2.operation_sequence_id = NVL(p_op_seq_id,-1)
1225 AND NOT EXISTS (SELECT 'x'
1226 FROM BOM_DEPARTMENT_RESOURCES bdr
1227 WHERE bdr.department_id = bd.department_id
1228 AND bdr.resource_id = bor2.resource_id)
1229 )
1230 )
1231 ) ;
1232
1233 BEGIN
1234
1235 FOR X_EamDept IN Check_DeptResource LOOP
1236 RETURN TRUE ;
1237 END LOOP;
1238
1239 RETURN FALSE ;
1240
1241 END Check_UpdateDept ;
1242
1243
1244 /******************************************************************
1245 * Procedure : Op_Node_Check_Existence
1246 * Parameters IN : Operation node record
1247 * Parameters OUT: Old operation node record
1248 * Return Mesg
1249 * Return Status
1250 * Purpose : Op_Node_Check_Existence will query using the primary key
1251 * information and return a success if the operation is
1252 * CREATE and the record EXISTS or will return an
1253 * error if the operation is UPDATE and record DOES NOT
1254 * EXIST.
1255 * In case of UPDATE if record exists, then the procedure
1256 * will return old record in the old entity parameters
1257 * with a success status.
1258 *********************************************************************/
1259 PROCEDURE Op_Node_Check_Existence
1260 ( p_op_node_rec IN Bom_Rtg_Eam_Util.Op_Node_Rec_Type
1261 , x_old_op_node_rec IN OUT NOCOPY Bom_Rtg_Eam_Util.Op_Node_Rec_Type
1262 , x_return_mesg IN OUT NOCOPY VARCHAR2
1263 , x_return_status IN OUT NOCOPY VARCHAR2
1264 )
1265 IS
1266 l_return_mesg VARCHAR2(2000) ;
1267 l_return_status VARCHAR2(1);
1268
1269
1270 /* Define Cursor */
1271 Cursor op_node_csr( p_operation_sequence_id NUMBER )
1272 IS
1273 SELECT X_COORDINATE
1274 , Y_COORDINATE
1275 FROM BOM_OPERATION_SEQUENCES
1276 WHERE operation_sequence_id = p_operation_sequence_id ;
1277
1278 op_node_rec op_node_csr%ROWTYPE ;
1279
1280 BEGIN
1281
1282 -- Init local table variables.
1283 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1284 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1285
1286
1287 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
1288 Error_Handler.Write_Debug
1289 ('Querying an operation sequence record : Seq ID '
1290 || to_char(p_op_node_rec.operation_sequence_id ) || '. . . ' ) ;
1291 END IF ;
1292
1293 -- Query Operation Row
1294 IF NOT op_node_csr%ISOPEN
1295 THEN
1296 OPEN op_node_csr( p_operation_sequence_id
1297 => p_op_node_rec.operation_sequence_id ) ;
1298 END IF ;
1299
1300 FETCH op_node_csr INTO op_node_rec ;
1301
1302 IF op_node_csr%FOUND
1303 THEN
1304 -- SetQueried Record to Op Node Recourd
1305 x_old_op_node_rec.X_Coordinate := op_node_rec.X_Coordinate ;
1306 x_old_op_node_rec.Y_Coordinate := op_node_rec.Y_Coordinate ;
1307
1308
1309 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
1310 Error_Handler.Write_Debug('Finished querying and assigning operation node record . . .') ;
1311 END IF ;
1312
1313 l_return_status := BOM_Rtg_Globals.G_RECORD_FOUND ;
1314
1315 ELSE
1316
1317 l_return_status := BOM_Rtg_Globals.G_RECORD_NOT_FOUND ;
1318
1319 END IF ;
1320
1321 IF op_node_csr%ISOPEN
1322 THEN
1323 CLOSE op_node_csr ;
1324 END IF ;
1325
1326
1327 IF l_return_status = BOM_Rtg_Globals.G_RECORD_FOUND AND
1328 p_op_node_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
1329 THEN
1330 l_return_mesg := 'BOM_OP_ALREADY_EXISTS' ;
1331 l_return_status := FND_API.G_RET_STS_ERROR ;
1332
1333 ELSIF l_return_status = BOM_Rtg_Globals.G_RECORD_NOT_FOUND AND
1334 p_op_node_rec.transaction_type IN
1335 (BOM_Rtg_Globals.G_OPR_UPDATE,
1336 BOM_Rtg_Globals.G_OPR_DELETE )
1337 THEN
1338 l_return_mesg := 'BOM_OP_DOESNOT_EXIST' ;
1339 l_return_status := FND_API.G_RET_STS_ERROR ;
1340
1341 ELSE
1342 l_return_status := FND_API.G_RET_STS_SUCCESS;
1343
1344 END IF ;
1345
1346
1347 x_return_status := l_return_status;
1348 x_return_mesg := l_return_mesg ;
1349
1350 EXCEPTION
1351 WHEN OTHERS THEN
1352 x_return_mesg := G_PKG_NAME || ' Operation Node Check Existence '
1356
1353 || substrb(SQLERRM,1,200);
1354
1355 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
1357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1358
1359
1360 END Op_Node_Check_Existence ;
1361
1362
1363 /*******************************************************************
1364 * Procedure : Op_Node_Populate_Null_Columns
1365 * Parameters IN : Operation Node column record
1366 * Old Operation Node column record
1367 * Parameters OUT: Operation Node column record after populating null columns
1368 * Purpose : Complete record will compare the database record with
1369 * the user given record and will complete the user
1370 * record with values from the database record, for all
1371 * columns that the user has left NULL.
1372 ********************************************************************/
1373 PROCEDURE Op_Node_Populate_Null_Columns
1374 ( p_op_node_rec IN Bom_Rtg_Eam_Util.Op_Node_Rec_Type
1375 , p_old_op_node_rec IN Bom_Rtg_Eam_Util.Op_Node_Rec_Type
1376 , x_op_node_rec IN OUT NOCOPY Bom_Rtg_Eam_Util.Op_Node_Rec_Type
1377 )
1378 IS
1379
1380
1381 BEGIN
1382
1383
1384 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1385 ('Within the Operation Node Populate null columns...') ;
1386 END IF ;
1387
1388 -- Initialize operation exp and unexp record
1389 x_op_node_rec := p_op_node_rec ;
1390
1391
1392 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1393 ('Populate null exposed columns......') ;
1394 END IF ;
1395
1396
1397 IF x_op_node_rec.X_Coordinate IS NULL
1398 THEN
1399 x_op_node_rec.X_Coordinate := p_old_op_node_rec.X_Coordinate ;
1400 END IF ;
1401
1402 IF x_op_node_rec.Y_Coordinate IS NULL
1403 THEN
1404 x_op_node_rec.Y_Coordinate := p_old_op_node_rec.Y_Coordinate ;
1405 END IF ;
1406
1407 END Op_Node_Populate_Null_Columns ;
1408
1409
1410 /********************************************************************
1411 * Procedure : Op_Node_Entity_Defaulting
1412 * Parameters IN : Operation Node column record
1413 * Parameters OUT: Operation Node column record after defaulting
1414 * Return Message
1415 * Return Status
1416 * Purpose : Entity defaulting proc. defualts columns to
1417 * appropriate values.
1418 *********************************************************************/
1419 PROCEDURE Op_Node_Entity_Defaulting
1420 ( p_op_node_rec IN Bom_Rtg_Eam_Util.Op_Node_Rec_Type
1421 , x_op_node_rec IN OUT NOCOPY Bom_Rtg_Eam_Util.Op_Node_Rec_Type
1422 , x_return_mesg IN OUT NOCOPY VARCHAR2
1423 , x_return_status IN OUT NOCOPY VARCHAR2
1424 )
1425 IS
1426
1427
1428 BEGIN
1429
1430 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1431 ('Within the Operation Node Entity Defaulting...') ;
1432 END IF ;
1433
1434 x_op_node_rec := p_op_node_rec ;
1435 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1436
1437
1438 IF x_op_node_rec.X_Coordinate = FND_API.G_MISS_NUM
1439 THEN
1440 x_op_node_rec.X_Coordinate := NULL ;
1441 END IF ;
1442
1443 IF x_op_node_rec.Y_Coordinate = FND_API.G_MISS_NUM
1444 THEN
1445 x_op_node_rec.Y_Coordinate := NULL ;
1446 END IF ;
1447
1448
1449 EXCEPTION
1450 WHEN OTHERS THEN
1451 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1452 ('Some unknown error in Op Node Entity Defaulting . . .' || SQLERRM );
1453 END IF ;
1454
1455
1456 x_return_mesg := G_PKG_NAME || ' Defaulting (Op Node Entity Defaulting) '
1457 || substrb(SQLERRM,1,200);
1458 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
1459
1460 -- Return the status and message table.
1461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1462
1463 END Op_Node_Entity_Defaulting ;
1464
1465
1466 /******************************************************************
1467 * Procedure : Op_Node_Check_Entity
1468 * Parameters IN : Operation Node column record
1469 * Old Operation Node column record
1470 * Parameters OUT: Return Message
1471 * Return Status
1472 * Purpose : Check_Entity validate the entity for the correct
1473 * business logic. It will verify the values by running
1474 * checks on inter-dependent columns.
1475 * It will also verify that changes in one column value
1476 * does not invalidate some other columns.
1477 **********************************************************************/
1478 PROCEDURE Op_Node_Check_Entity
1479 ( p_op_node_rec IN Bom_Rtg_Eam_Util.Op_Node_Rec_Type
1480 , p_old_op_node_rec IN Bom_Rtg_Eam_Util.Op_Node_Rec_Type
1481 , x_op_node_rec IN OUT NOCOPY Bom_Rtg_Eam_Util.Op_Node_Rec_Type
1482 , x_return_mesg IN OUT NOCOPY VARCHAR2
1483 , x_return_status IN OUT NOCOPY VARCHAR2
1484 )
1485 IS
1486
1487 BEGIN
1488
1492 x_op_node_rec := p_op_node_rec ;
1489 --
1490 -- Initialize Op Node Record and Status
1491 --
1493 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1494
1495
1496 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1497 ('Performing Operation Check Entitity Validation . . .') ;
1498 END IF ;
1499
1500 -- There is no validation in current release.
1501
1502
1503 EXCEPTION
1504 WHEN OTHERS THEN
1505 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1506 ('Some unknown error in Op Node Entity Validation . . .' || SQLERRM );
1507 END IF ;
1508
1509
1510 x_return_mesg := G_PKG_NAME || ' Validation (Op Node Entity Validation) '
1511 || substrb(SQLERRM,1,200);
1512
1513 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
1514
1515 -- Return the status and message table.
1516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1517
1518
1519 END Op_Node_Check_Entity ;
1520
1521 /*********************************************************************
1522 * Procedure : Op_Node_Perform_Writes
1523 * Parameters IN : Operation Node column record
1524 * Parameters OUT: Return Message
1525 * Return Status
1526 * Purpose : Perform any insert/update/deletes to the
1527 * Operation Sequences table.
1528 *********************************************************************/
1529 PROCEDURE Op_Node_Perform_Writes
1530 ( p_op_node_rec IN Bom_Rtg_Eam_Util.Op_Node_Rec_Type
1531 , x_return_mesg IN OUT NOCOPY VARCHAR2
1532 , x_return_status IN OUT NOCOPY VARCHAR2
1533 )
1534 IS
1535
1536 BEGIN
1537
1538 --
1539 -- Initialize Status
1540 --
1541 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1542
1543 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1544 ('Performing Database Writes . . .') ;
1545 END IF ;
1546
1547
1548 IF p_op_node_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE THEN
1549 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1550 ('Operation Sequence: Executing Insert Row. . . ') ;
1551 END IF;
1552
1553 NULL ;
1554
1555 ELSIF p_op_node_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
1556 THEN
1557 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1558 ('Operatin Sequence: Executing Update Row. . . ') ;
1559 END IF ;
1560
1561
1562 UPDATE BOM_OPERATION_SEQUENCES
1563 SET X_Coordinate = p_op_node_rec.x_coordinate
1564 , Y_Coordinate = p_op_node_rec.y_coordinate
1565 , last_update_date = SYSDATE /* Last Update Date */
1566 , last_updated_by = BOM_Rtg_Globals.Get_User_Id /* Last Updated By */
1567 , last_update_login = BOM_Rtg_Globals.Get_Login_Id /* Last Update Login */
1568 , program_application_id = BOM_Rtg_Globals.Get_Prog_AppId /* Application Id */
1569 , program_id = BOM_Rtg_Globals.Get_Prog_Id /* Program Id */
1570 , program_update_date = SYSDATE /* program_update_date */
1571 WHERE operation_sequence_id = p_op_node_rec.operation_sequence_id ;
1572
1573
1574 ELSIF p_op_node_rec.transaction_type = BOM_Rtg_Globals.G_OPR_DELETE
1575 THEN
1576
1577 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1578 ('Operatin Sequence: Executing Delete Row. . . ') ;
1579 END IF ;
1580
1581 NULL ;
1582
1583 END IF ;
1584
1585
1586 EXCEPTION
1587 WHEN OTHERS THEN
1588 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1589 ('Some unknown error in Op Node Perform Writes . . .' || SQLERRM );
1590 END IF ;
1591
1592 x_return_mesg := G_PKG_NAME || ' Utility (Op Node Perform Writes) '
1593 || substrb(SQLERRM,1,200);
1594
1595 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
1596
1597 -- Return the status and message table.
1598 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1599
1600 END Op_Node_Perform_Writes ;
1601
1602
1603
1604 /****************************************************************************
1605 * Procedure : Operation_Nodes
1606 * Parameters IN : Operation Node Table
1607 * Parameters OUT : Operatin Node Table and Return Status and Messages
1608 * Purpose : This procedure will process all the Operation Nodes records.
1609 *
1610 *****************************************************************************/
1611 PROCEDURE Operation_Nodes
1612 ( p_op_node_tbl IN Bom_Rtg_Eam_Util.Op_Node_Tbl_Type
1613 , x_op_node_tbl IN OUT NOCOPY Bom_Rtg_Eam_Util.Op_Node_Tbl_Type
1614 , x_return_mesg IN OUT NOCOPY VARCHAR2
1615 , x_return_status IN OUT NOCOPY VARCHAR2
1616 )
1617 IS
1618
1619 l_op_node_tbl Bom_Rtg_Eam_Util.Op_Node_Tbl_Type ;
1620 l_op_node_rec Bom_Rtg_Eam_Util.Op_Node_Rec_Type ;
1621 l_old_op_node_rec Bom_Rtg_Eam_Util.Op_Node_Rec_Type ;
1622
1623 l_return_status VARCHAR2(1) ;
1627
1624 l_return_mesg VARCHAR2(2000) ;
1625
1626 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
1628
1629 EXC_SEV_QUIT_OBJECT EXCEPTION ;
1630 EXC_UNEXP_SKIP_OBJECT EXCEPTION ;
1631
1632
1633 BEGIN
1634
1635 -- Return Status
1636 -- Success : FND_API.G_RET_STS_SUCCESS
1637 -- Error : FND_API.G_RET_STS_ERROR
1638 -- Unexpected Error : FND_API.G_RET_STS_UNEXP_ERROR
1639
1640
1641 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1642 ('Processing Operation Nodes . . .' ) ;
1643 END IF ;
1644
1645
1646
1647 -- Init local table variables.
1648 SAVEPOINT init_operation_nodes ;
1649 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1650 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1651 l_op_node_tbl := p_op_node_tbl ;
1652
1653 -- Load environment information into the SYSTEM_INFORMATION record
1654 -- (USER_ID, LOGIN_ID, PROG_APPID, PROG_ID)
1655
1656 BOM_Rtg_Globals.Init_System_Info_Rec
1657 ( x_mesg_token_tbl => l_mesg_token_tbl
1658 , x_return_status => l_return_status
1659 );
1660
1661
1662 FOR I IN 1..l_op_node_tbl.COUNT LOOP
1663 BEGIN
1664
1665 -- Load local records
1666 l_op_node_rec := l_op_node_tbl(I) ;
1667
1668 l_op_node_rec.transaction_type :=
1669 UPPER(l_op_node_rec.transaction_type) ;
1670
1671
1672 IF (l_op_node_rec.return_status IS NULL OR
1673 l_op_node_rec.return_status = FND_API.G_MISS_CHAR)
1674
1675 THEN
1676
1677 l_return_status := FND_API.G_RET_STS_SUCCESS;
1678 l_op_node_rec.return_status := FND_API.G_RET_STS_SUCCESS;
1679
1680
1681 -- Check Transaction Type
1682 -- Transaction Type should be UPDATE only.
1683
1684 IF l_op_node_rec.transaction_type <> BOM_Rtg_Globals.G_OPR_UPDATE
1685 THEN
1686 /* Error Handling */
1687 FND_MESSAGE.SET_NAME('BOM','BOM_OPNODE_TRANS_TYPE_INVALID');
1688 FND_MESSAGE.SET_TOKEN('OP_SEQ_NUMBER', l_op_node_rec.operation_sequence_number) ;
1689 l_return_mesg := FND_MESSAGE.GET ;
1690 l_return_status := FND_API.G_RET_STS_ERROR ;
1691 RAISE EXC_SEV_QUIT_OBJECT ;
1692
1693 END IF ;
1694
1695 -- Process Flow step : Check user unique index : Operation_Sequence_Id
1696 IF l_op_node_rec.operation_sequence_id is NULL OR
1697 l_op_node_rec.operation_sequence_id = FND_API.G_MISS_NUM
1698 THEN
1699
1700 /* Error Handling */
1701 FND_MESSAGE.SET_NAME('BOM','BOM_OPNODE_SEQ_ID_NULL');
1702 FND_MESSAGE.SET_TOKEN('OP_SEQ_NUMBER', l_op_node_rec.operation_sequence_number) ;
1703 l_return_mesg := FND_MESSAGE.GET ;
1704 l_return_status := FND_API.G_RET_STS_ERROR ;
1705 RAISE EXC_SEV_QUIT_OBJECT ;
1706
1707
1708 END IF;
1709
1710 -- Process Flow step : Verify Operation 's existence
1711 --
1712 Bom_Rtg_Eam_Util.Op_Node_Check_Existence
1713 ( p_op_node_rec => l_op_node_rec
1714 , x_old_op_node_rec => l_old_op_node_rec
1715 , x_return_mesg => l_return_mesg
1716 , x_return_status => l_return_status
1717 );
1718
1719 IF l_return_status = FND_API.G_RET_STS_ERROR
1720 THEN
1721
1722 /* Error Handling */
1723 RAISE EXC_SEV_QUIT_OBJECT ;
1724
1725 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1726 THEN
1727
1728 /* Error Handling */
1729 RAISE EXC_UNEXP_SKIP_OBJECT ;
1730
1731 END IF;
1732
1733 -- Process Flow step : Check required fields exist
1734 -- Nothing special
1735
1736 -- Process Flow step : Attribute Validation for CREATE and UPDATE
1737 -- Nothing special
1738
1739 IF l_op_node_rec.transaction_type IN
1740 (BOM_Rtg_Globals.G_OPR_UPDATE, BOM_Rtg_Globals.G_OPR_DELETE)
1741 THEN
1742
1743 --
1744 -- Process flow step : Populate NULL columns for Update and Delete
1745 --
1746
1747 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
1748 Error_Handler.Write_Debug('Populate NULL columns') ;
1749 END IF ;
1750
1751 Bom_Rtg_Eam_Util.Op_Node_Populate_Null_Columns
1752 ( p_op_node_rec => l_op_node_rec
1753 , p_old_op_node_rec => l_old_op_node_rec
1754 , x_op_node_rec => l_op_node_rec
1755 );
1756
1757
1758 ELSIF l_op_node_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
1759 THEN
1760 --
1761 -- Process Flow step : Default missing values for Op Nodes (CREATE)
1762
1763 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
1764 Error_Handler.Write_Debug('Attribute Defaulting') ;
1765 END IF ;
1766
1767 END IF ;
1768
1769 --
1773 , BOM_Rtg_Globals.G_OPR_UPDATE )
1770 -- Process Flow step : Entity defaulting for CREATE and UPDATE
1771 --
1772 IF l_op_node_rec.transaction_type IN ( BOM_Rtg_Globals.G_OPR_CREATE
1774 THEN
1775
1776 Bom_Rtg_Eam_Util.Op_Node_Entity_Defaulting
1777 ( p_op_node_rec => l_op_node_rec
1778 , x_op_node_rec => l_op_node_rec
1779 , x_return_mesg => l_return_mesg
1780 , x_return_status => l_return_status
1781 ) ;
1782
1783 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
1784 Error_Handler.Write_Debug ('Entity defaulting completed with return_status: ' || l_return_status) ;
1785 END IF ;
1786
1787
1788 IF l_return_status = FND_API.G_RET_STS_ERROR
1789 THEN
1790
1791 /* Error Handling */
1792 RAISE EXC_SEV_QUIT_OBJECT ;
1793
1794 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1795 THEN
1796
1797 /* Error Handling */
1798 RAISE EXC_UNEXP_SKIP_OBJECT ;
1799
1800 END IF;
1801
1802 END IF ;
1803
1804 --
1805 -- Process Flow step : Entity Level Validation
1806 -- Call Bom_Validate_Op_Res.Check_Entity
1807 --
1808 Bom_Rtg_Eam_Util.Op_Node_Check_Entity
1809 ( p_op_node_rec => l_op_node_rec
1810 , p_old_op_node_rec => l_old_op_node_rec
1811 , x_op_node_rec => l_op_node_rec
1812 , x_return_mesg => l_return_mesg
1813 , x_return_status => l_return_status
1814 ) ;
1815
1816
1817 IF l_return_status = FND_API.G_RET_STS_ERROR
1818 THEN
1819
1820 /* Error Handling */
1821 RAISE EXC_SEV_QUIT_OBJECT ;
1822
1823 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1824 THEN
1825
1826 /* Error Handling */
1827 RAISE EXC_UNEXP_SKIP_OBJECT ;
1828
1829 END IF;
1830
1831
1832
1833 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
1834 Error_Handler.Write_Debug('Entity validation completed with '
1835 || l_return_Status || ' proceeding for database writes . . . ') ;
1836 END IF;
1837
1838 --
1839 -- Process Flow step 16 : Database Writes
1840 --
1841 Bom_Rtg_Eam_Util.Op_Node_Perform_Writes
1842 ( p_op_node_rec => l_op_node_rec
1843 , x_return_mesg => l_return_mesg
1844 , x_return_status => l_return_status
1845 ) ;
1846
1847
1848 IF l_return_status = FND_API.G_RET_STS_ERROR
1849 THEN
1850
1851 /* Error Handling */
1852 RAISE EXC_SEV_QUIT_OBJECT ;
1853
1854 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1855 THEN
1856
1857 /* Error Handling */
1858 RAISE EXC_UNEXP_SKIP_OBJECT ;
1859
1860 END IF;
1861
1862
1863 END IF ; -- END IF statement that checks RETURN STATUS
1864
1865 -- Load tables.
1866 l_op_node_tbl(I) := l_op_node_rec;
1867
1868
1869 EXCEPTION
1870
1871 WHEN EXC_SEV_QUIT_OBJECT THEN
1872 l_return_status := FND_API.G_RET_STS_ERROR ;
1873
1874 WHEN EXC_UNEXP_SKIP_OBJECT THEN
1875 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1876
1877 END ; -- END block
1878
1879
1880
1881 IF l_return_status IN ( FND_API.G_RET_STS_ERROR
1882 , FND_API.G_RET_STS_UNEXP_ERROR)
1883 THEN
1884 x_return_status := l_return_status ;
1885 ROLLBACK TO init_operation_nodes ;
1886 RETURN ;
1887 END IF;
1888
1889
1890 END LOOP ; -- End of l_op_node_tbl loop
1891
1892
1893 -- Load OUT parameters
1894 IF NVL(l_return_status, FND_API.G_RET_STS_SUCCESS )
1895 <> FND_API.G_RET_STS_SUCCESS
1896 THEN
1897 x_return_status := l_return_status ;
1898 END IF;
1899
1900 x_return_mesg := l_return_mesg ;
1901
1902 END Operation_Nodes ;
1903
1904
1905 PROCEDURE Create_OpNwkTbl_From_OpLinkTbl
1906 ( p_op_link_tbl IN Bom_Rtg_Eam_Util.Op_Link_Tbl_Type
1907 , x_op_network_tbl IN OUT NOCOPY Bom_Rtg_Pub.Op_Network_Tbl_Type
1908 )
1909 IS
1910
1911 -- Retrieve operation network from RBO_NWK
1912 CURSOR GetOpNwk ( p_from_op_id NUMBER
1913 , p_to_op_id NUMBER )
1914 IS
1915 SELECT bos1.Operation_Type
1916 , bos1.operation_seq_num From_Op_Seq_Number
1917 , bos1.effectivity_date From_Start_Effective_Date
1918 , bos2.operation_seq_num To_Op_Seq_Number
1919 , bos2.effectivity_date To_Start_Effective_Date
1920 FROM BOM_OPERATION_SEQUENCES bos1
1921 , BOM_OPERATION_SEQUENCES bos2
1922 WHERE bos1.operation_sequence_id = p_from_op_id
1926
1923 AND bos2.operation_sequence_id = p_to_op_id ;
1924
1925 nwk_c NUMBER := 0; -- counter for operation network
1927 BEGIN
1928
1929
1930 FOR I IN 1..p_op_link_tbl.COUNT LOOP
1931
1932
1933 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1934 ('Getting Operation Network Record . . .');
1935 END IF ;
1936
1937 nwk_c:= nwk_c + 1 ; --counter for operation networks
1938 FOR nwk_rec IN GetOpNwk ( p_from_op_id => p_op_link_tbl(I).From_Op_Seq_Id
1939 , p_to_op_id => p_op_link_tbl(I).To_Op_Seq_Id )
1940 LOOP
1941 x_op_network_tbl(nwk_c).Operation_Type := nwk_rec.Operation_Type ;
1942 x_op_network_tbl(nwk_c).From_Op_Seq_Number := nwk_rec.From_Op_Seq_Number ;
1943 x_op_network_tbl(nwk_c).From_Start_Effective_Date := nwk_rec.From_Start_Effective_Date ;
1944 x_op_network_tbl(nwk_c).To_Op_Seq_Number := nwk_rec.To_Op_Seq_Number ;
1945 x_op_network_tbl(nwk_c).To_Start_Effective_Date := nwk_rec.To_Start_Effective_Date ;
1946
1947 END LOOP ;
1948
1949
1950 x_op_network_tbl(nwk_c).Assembly_Item_Name := p_op_link_tbl(I).Assembly_Item_Name ;
1951 x_op_network_tbl(nwk_c).Organization_Code := p_op_link_tbl(I).Organization_Code ;
1952 x_op_network_tbl(nwk_c).Alternate_Routing_Code := p_op_link_tbl(I).Alternate_Routing_Code ;
1953 x_op_network_tbl(nwk_c).New_From_Op_Seq_Number := NULL ;
1954 x_op_network_tbl(nwk_c).New_From_Start_Effective_Date := NULL ;
1955 x_op_network_tbl(nwk_c).New_To_Op_Seq_Number := NULL ;
1956 x_op_network_tbl(nwk_c).New_To_Start_Effective_Date := NULL ;
1957 x_op_network_tbl(nwk_c).Connection_Type := NULL ;
1958 x_op_network_tbl(nwk_c).Planning_Percent := NULL ;
1959 x_op_network_tbl(nwk_c).Attribute_category := NULL ;
1960 x_op_network_tbl(nwk_c).Attribute1 := NULL ;
1961 x_op_network_tbl(nwk_c).Attribute2 := NULL ;
1962 x_op_network_tbl(nwk_c).Attribute3 := NULL ;
1963 x_op_network_tbl(nwk_c).Attribute4 := NULL ;
1964 x_op_network_tbl(nwk_c).Attribute5 := NULL ;
1965 x_op_network_tbl(nwk_c).Attribute6 := NULL ;
1966 x_op_network_tbl(nwk_c).Attribute7 := NULL ;
1967 x_op_network_tbl(nwk_c).Attribute8 := NULL ;
1968 x_op_network_tbl(nwk_c).Attribute9 := NULL ;
1969 x_op_network_tbl(nwk_c).Attribute10 := NULL ;
1970 x_op_network_tbl(nwk_c).Attribute11 := NULL ;
1971 x_op_network_tbl(nwk_c).Attribute12 := NULL ;
1972 x_op_network_tbl(nwk_c).Attribute13 := NULL ;
1973 x_op_network_tbl(nwk_c).Attribute14 := NULL ;
1974 x_op_network_tbl(nwk_c).Attribute15 := NULL ;
1975 x_op_network_tbl(nwk_c).Original_System_Reference := NULL ;
1976 x_op_network_tbl(nwk_c).Transaction_Type := p_op_link_tbl(I).Transaction_Type ;
1977 x_op_network_tbl(nwk_c).Return_Status := p_op_link_tbl(I).Return_Status ;
1978 END LOOP;
1979
1980
1981 END Create_OpNwkTbl_From_OpLinkTbl ;
1982
1983
1984
1985 /****************************************************************************
1986 * Procedure : Operation_Links
1987 * Parameters IN : Operation Links Table
1988 * Parameters OUT : Operatin Links Table and Return Status and Messages
1989 * Purpose : This procedure will process all the Operation Link records
1990 * using Routing Business Objects.
1991 *****************************************************************************/
1992 PROCEDURE Operation_Links
1993 ( p_op_link_tbl IN Bom_Rtg_Eam_Util.Op_Link_Tbl_Type
1994 , x_op_link_tbl IN OUT NOCOPY Bom_Rtg_Eam_Util.Op_Link_Tbl_Type
1995 , x_message_list IN OUT NOCOPY Error_Handler.Error_Tbl_Type
1996 , x_return_status IN OUT NOCOPY VARCHAR2
1997 )
1998 IS
1999
2000 /* Record and Table declaration */
2001 l_rtg_header_rec Bom_Rtg_Pub.Rtg_Header_Rec_Type ;
2002 l_out_rtg_header_rec Bom_Rtg_Pub.Rtg_Header_Rec_Type ;
2003 l_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type ;
2004 l_out_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type ;
2005 l_operation_tbl Bom_Rtg_Pub.Operation_Tbl_Type ;
2006 l_out_operation_tbl Bom_Rtg_Pub.Operation_Tbl_Type ;
2007 l_op_resource_tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type ;
2008 l_out_op_resource_tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type ;
2009 l_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type ;
2010 l_out_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type ;
2011 l_op_network_tbl Bom_Rtg_Pub.Op_Network_Tbl_Type ;
2012 l_out_op_network_tbl Bom_Rtg_Pub.Op_Network_Tbl_Type ;
2013
2014 l_return_status VARCHAR2(1);
2015 l_unexp_error VARCHAR2(1000);
2016 l_msg_count NUMBER := 0;
2017 l_message_list Error_Handler.Error_Tbl_Type ;
2018
2019
2020
2021
2022 BEGIN
2023
2024 SAVEPOINT init_operation_link ;
2025
2026
2027 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2028 ('Create op_network_tbl from p_op_link_tbl. . . ');
2029 END IF ;
2030
2031
2032 Create_OpNwkTbl_From_OpLinkTbl
2033 ( p_op_link_tbl => p_op_link_tbl
2034 , x_op_network_tbl => l_op_network_tbl
2035 ) ;
2036
2037
2041
2038 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2039 ('Call Routing Business Object Public API . . . ');
2040 END IF ;
2042 Bom_Rtg_Pub.Process_Rtg
2043 ( p_init_msg_list => TRUE
2044 , p_rtg_header_rec => l_rtg_header_rec
2045 , p_rtg_revision_tbl => l_rtg_revision_tbl
2046 , p_operation_tbl => l_operation_tbl
2047 , p_op_resource_tbl => l_op_resource_tbl
2048 , p_sub_resource_tbl => l_sub_resource_tbl
2049 , p_op_network_tbl => l_op_network_tbl
2050 , x_rtg_header_rec => l_out_rtg_header_rec
2051 , x_rtg_revision_tbl => l_out_rtg_revision_tbl
2052 , x_operation_tbl => l_out_operation_tbl
2053 , x_op_resource_tbl => l_out_op_resource_tbl
2054 , x_sub_resource_tbl => l_out_sub_resource_tbl
2055 , x_op_network_tbl => l_out_op_network_tbl
2056 , x_return_status => l_return_status
2057 , x_msg_count => l_msg_count
2058 , p_debug => 'N'
2059 , p_debug_filename => NULL
2060 , p_output_dir => NULL
2061 ) ;
2062
2063
2064 -- Check the return status and error handling
2065 -- If error is returned re-initialize applet again.
2066 Error_Handler.get_entity_message
2067 ( p_entity_id => 'NWK'
2068 , x_message_list => l_message_list
2069 );
2070
2071 -- Renurn Mesg and Status
2072 x_message_list := l_message_list ;
2073 x_return_status := l_return_status ;
2074 x_op_link_tbl := p_op_link_tbl ;
2075
2076
2077 IF l_return_status IN ( FND_API.G_RET_STS_ERROR
2078 , FND_API.G_RET_STS_UNEXP_ERROR)
2079 THEN
2080 ROLLBACK TO init_operation_link ;
2081 END IF ;
2082
2083 END Operation_Links ;
2084
2085
2086
2087 END Bom_Rtg_Eam_Util ;