4 /*-------------------------------------------------------------------------
1 PACKAGE BODY BOM_RTG_NETWORK_VALIDATE_API AS
2 /* $Header: BOMRNWVB.pls 115.2 99/08/05 13:21:58 porting ship $ */
3
5 Name
6 get_routing_sequence_id
7 Description
8 Function to get the routing_sequence_id when the alternate key
9 IDs are specified.
10 Returns
11 routing_sequence_id
12 +--------------------------------------------------------------------------*/
13 FUNCTION get_routing_sequence_id (
14 p_assy_item_id IN NUMBER,
15 p_org_id IN NUMBER,
16 p_alt_rtg_desig IN VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
17
18 l_rtg_seq_id NUMBER := NULL;
19
20 BEGIN
21 SELECT routing_sequence_id
22 INTO l_rtg_seq_id
23 FROM bom_operational_routings
24 WHERE assembly_item_id = p_assy_item_id
25 AND organization_id = p_org_id
26 AND NVL(alternate_routing_designator, 'NONE') =
27 NVL(p_alt_rtg_desig, 'NONE');
28
29 RETURN (l_rtg_seq_id);
30
31 EXCEPTION WHEN NO_DATA_FOUND THEN
32 RETURN NULL;
33 END;
34
35 /*-------------------------------------------------------------------------
36 Name
37 check_network_exists
38 Description
39 Function to get the routing_sequence_id when the alternate key
40 IDs are specified.
41 Returns
42 TRUE if a network of p_operation_type exists for p_rtg_sequence_id;
43 FALSE otherwise.
44 +--------------------------------------------------------------------------*/
45 FUNCTION check_network_exists (
46 p_rtg_sequence_id IN NUMBER,
47 p_operation_type IN NUMBER) RETURN BOOLEAN IS
48 net_exists NUMBER;
49 BEGIN
50 SELECT 1
51 INTO net_exists
52 FROM dual
53 WHERE EXISTS (SELECT null
54 FROM bom_operation_networks_v
55 WHERE routing_sequence_id = p_rtg_sequence_id
56 AND operation_type = p_operation_type);
57
58 RETURN (TRUE);
59 EXCEPTION WHEN NO_DATA_FOUND THEN
60 RETURN (FALSE);
61 END check_network_exists;
62
63 /*-------------------------------------------------------------------------
64 Name
65 ref_pos
66 Description
67 Function to get the reference position which is the first row from
68 the top of the table with flag = 'C'.
69 Returns
70 Reference, if there exists rows with flag = 'C';
71 -1, otherwise.
72 +--------------------------------------------------------------------------*/
73 FUNCTION ref_pos (
74 p_lnk_tbl IN Lnk_Tbl_Type) RETURN NUMBER IS
75 i INTEGER;
76 BEGIN
77 FOR i IN 1..p_lnk_tbl.COUNT LOOP
78 IF p_lnk_tbl(i).flag = 'C' THEN
79 RETURN (i);
80 END IF;
81 END LOOP;
82 RETURN (-1);
83 END ref_pos;
84
85 /*-------------------------------------------------------------------------
86 Name
87 ref_rev_pos
88 Description
89 Function to get the reference position which is the first row from
90 the bottom of the table with flag = 'C'.
91 Returns
92 Reference, if there exists rows with flag = 'C';
93 -1, otherwise.
94 +--------------------------------------------------------------------------*/
95 FUNCTION ref_rev_pos (
96 p_lnk_tbl IN Lnk_Tbl_Type) RETURN NUMBER IS
97 i INTEGER;
98 BEGIN
99 FOR i IN REVERSE 1..p_lnk_tbl.COUNT LOOP
100 IF p_lnk_tbl(i).flag = 'C' THEN
101 RETURN (i);
102 END IF;
103 END LOOP;
104 RETURN (-1);
105 END ref_rev_pos;
106
107 /*-------------------------------------------------------------------------
108 Name
109 find_op
110 Description
111 Function to search the table for a particular operation seq.
112 Returns
113 TRUE, if p_op_seq_num in p_op_tbl;
114 FALSE, otherwise.
115 +--------------------------------------------------------------------------*/
116 FUNCTION find_op (
120 BEGIN
117 p_op_seq_num IN NUMBER,
118 p_op_tbl IN Op_Tbl_Type) RETURN BOOLEAN IS
119 i INTEGER;
121 FOR i IN 1..p_op_tbl.COUNT LOOP
122 IF p_op_tbl(i).operation_seq_num = p_op_seq_num THEN
123 RETURN (TRUE);
124 END IF;
125 END LOOP;
126 RETURN (FALSE);
127 END find_op;
128
129 /*-------------------------------------------------------------------------
130 Name
131 is_connected
132 Description
133 Function to to see if there are any rows common in the two tables
134 i.e. if the two tables are connected.
135 Returns
136 TRUE, if p_con_tbl is connected to p_mst_tbl;
137 FALSE, otherwise.
138 +--------------------------------------------------------------------------*/
139 FUNCTION is_connected (
140 p_con_tbl IN Op_Tbl_Type,
141 p_mst_tbl IN Op_Tbl_Type) RETURN BOOLEAN IS
142 i INTEGER;
143 j INTEGER;
144 BEGIN
145 --dbms_output.put_line('check if connected');
146 FOR i IN 1..p_mst_tbl.COUNT LOOP
147 FOR j IN 1..p_con_tbl.COUNT LOOP
148 IF p_mst_tbl(i).operation_seq_num
149 = p_con_tbl(j).operation_seq_num THEN
150 --dbms_output.put_line('TRUE');
151 RETURN (TRUE);
152 END IF;
153 END LOOP;
154 END LOOP;
155 --dbms_output.put_line('FALSE');
156 RETURN (FALSE);
157 END is_connected;
158
159 /*-------------------------------------------------------------------------
160 Name
161 get_all_links
162 Description
163 This PROCEDURE gets all the operation links on the network for
164 the routing.
165 Returns
166 A PL/SQL table of Lnk_Tbl_Type type as OUT parameter that includes
167 a list of all operation links for the routing network of
168 p_operation_type.
169 +--------------------------------------------------------------------------*/
170 PROCEDURE get_all_links (
171 p_rtg_sequence_id IN NUMBER,
172 p_operation_type IN NUMBER,
173 x_Lnk_Tbl OUT Lnk_Tbl_Type ) IS
174
175 i INTEGER := 1;
176 CURSOR all_links IS
177 SELECT from_op_seq_id,from_seq_num,
178 to_op_seq_id
179 FROM bom_operation_networks_v
180 WHERE routing_sequence_id = p_rtg_sequence_id
181 AND operation_type = p_operation_type
182 AND transition_type <> 3
183 ORDER BY from_seq_num;
184
185 BEGIN
186 FOR all_links_rec IN all_links LOOP
187 x_Lnk_Tbl(i).from_op_seq_id := all_links_rec.from_op_seq_id;
188 x_Lnk_Tbl(i).to_op_seq_id := all_links_rec.to_op_seq_id;
189 x_Lnk_Tbl(i).flag := 'C';
190 --dbms_output.put_line('Row #'||to_char(i)||' '||
191 -- to_char(x_Lnk_Tbl(i).from_op_seq_id)||' '||
192 -- to_char(x_Lnk_Tbl(i).to_op_seq_id)||' '||
193 -- x_Lnk_Tbl(i).flag);
194 i := i + 1;
195 END LOOP;
196 END get_all_links;
197
198 /*-------------------------------------------------------------------------
199 Name
200 get_all_start_nodes
201 Description
202 This PROCEDURE gets all the starting nodes of the operation links on
203 the network for the routing.
204 Returns
205 A PL/SQL table of Op_Tbl_Type type as OUT parameter that includes
206 a list of all starting nodes for the routing network of type
207 p_operation_type.
208 +--------------------------------------------------------------------------*/
209 PROCEDURE get_all_start_nodes (
210 p_rtg_sequence_id IN NUMBER,
211 p_operation_type IN NUMBER,
212 x_Op_Tbl OUT Op_Tbl_Type ) IS
213
214 i INTEGER := 1;
215 CURSOR all_start_nodes IS
216 SELECT DISTINCT from_op_seq_id, from_seq_num
217 FROM bom_operation_networks_v bonv
218 WHERE routing_sequence_id = p_rtg_sequence_id
219 AND operation_type = p_operation_type
220 AND transition_type <> 3
221 AND NOT EXISTS (SELECT NULL
222 FROM bom_operation_networks_v net
223 WHERE net.to_op_seq_id = bonv.from_op_seq_id)
224 ORDER BY from_seq_num;
225
226 BEGIN
227 FOR all_start_nodes_rec IN all_start_nodes LOOP
228 x_Op_Tbl(i).operation_seq_id := all_start_nodes_rec.from_op_seq_id;
229 x_Op_Tbl(i).operation_seq_num := all_start_nodes_rec.from_seq_num;
230 --dbms_output.put_line('Row# '||to_char(i)||' '||
231 -- to_char(x_Op_Tbl(i).operation_seq_id)||' '||
232 -- to_char(x_Op_Tbl(i).operation_seq_num));
233 i := i + 1;
234 END LOOP;
235 END get_all_start_nodes;
236
237 /*-------------------------------------------------------------------------
238 Name
239 create_con_op_list
240 Description
241 This PROCEDURE gets all the connected nodes on the network for the
242 routing starting from the p_str_op_num node.
243 Returns
244 A PL/SQL table of Op_Tbl_Type type as OUT parameter that includes
245 a list of all connected nodes for the routing network of type
246 p_operation_type.
247 +--------------------------------------------------------------------------*/
248 PROCEDURE create_con_op_list (
249 p_str_op_id IN NUMBER,
250 p_str_op_num IN NUMBER,
251 x_Op_Tbl OUT Op_Tbl_Type ) IS
252
253 i INTEGER := 2;
254 l_op_seq_num NUMBER;
255
256 CURSOR all_con_ops (c_str_op_id NUMBER) IS
257 SELECT DISTINCT to_op_seq_id operation_sequence_id
258 FROM bom_operation_networks
259 CONNECT BY PRIOR to_op_seq_id = from_op_seq_id
260 AND transition_type <> 3
261 START WITH from_op_seq_id = c_str_op_id
262 AND transition_type <> 3;
263
264 BEGIN
268 x_Op_Tbl(1).operation_seq_id := p_str_op_id;
265 --dbms_output.put_line('Connected lst----');
266 -- create a list of connected nodes starting from the start node
267 -- add the start node
269 x_Op_Tbl(1).operation_seq_num := p_str_op_num;
270 --dbms_output.put_line('Row# 1'||' '||to_char(x_Op_Tbl(1).operation_seq_id)||' '||
271 -- to_char(x_Op_Tbl(1).operation_seq_num));
272 FOR all_con_op_rec IN all_con_ops(p_str_op_id) LOOP
273 SELECT operation_seq_num
274 INTO l_op_seq_num
275 FROM bom_operation_sequences
276 WHERE operation_sequence_id =
277 all_con_op_rec.operation_sequence_id;
278 x_Op_Tbl(i).operation_seq_id :=
279 all_con_op_rec.operation_sequence_id;
280 x_Op_Tbl(i).operation_seq_num := l_op_seq_num;
281 --dbms_output.put_line('Row# '||to_char(i)||' '||to_char(x_Op_Tbl(i).operation_seq_id)||' '||
282 -- to_char(x_Op_Tbl(i).operation_seq_num));
283 i := i + 1;
284 END LOOP;
285 EXCEPTION WHEN NO_DATA_FOUND THEN
286 NULL;
287
288 END create_con_op_list;
289
290 /*-------------------------------------------------------------------------
291 Name
292 append_to_mst_lst
293 Description
294 This PROCEDURE appends p_Con_Op_Tbl to the master list x_Mst_Op_Tbl
295 making sure that there is no redundancy.
296 Returns
297 A PL/SQL table of Op_Tbl_Type type as IN OUT parameter that is
298 the master list of the connected nodes in the network.
299 +--------------------------------------------------------------------------*/
300 PROCEDURE append_to_mst_lst (
301 p_Con_Op_Tbl IN Op_Tbl_Type,
302 x_Mst_Op_Tbl IN OUT Op_Tbl_Type) IS
303
304 i INTEGER;
305 mst_ctr INTEGER;
306 BEGIN
307 mst_ctr := x_Mst_Op_Tbl.COUNT + 1;
308 --dbms_output.put_line('Append to Mst lst');
309 FOR i IN 1..p_Con_Op_Tbl.COUNT LOOP
310 IF NOT find_op(p_Con_Op_Tbl(i).operation_seq_num, x_Mst_Op_Tbl) THEN
311 x_Mst_Op_Tbl(mst_ctr).operation_seq_id :=
312 p_Con_Op_Tbl(i).operation_seq_id;
313 x_Mst_Op_Tbl(mst_ctr).operation_seq_num :=
314 p_Con_Op_Tbl(i).operation_seq_num;
315 --dbms_output.put_line('Mst: row# '||to_char(mst_ctr)||' '||
316 -- to_char(x_Mst_Op_Tbl(mst_ctr).operation_seq_id)||' '||
317 -- to_char(x_Mst_Op_Tbl(mst_ctr).operation_seq_num));
318 mst_ctr := mst_ctr + 1;
319 END IF;
320 END LOOP;
321
322 END append_to_mst_lst;
323
324
325 /*-------------------------------------------------------------------------
326 Name
327 validate_routing_network
328 Description
329 This PROCEDURE is the main procedure thats called from the form,
330 java applet code, or the Routing Open Interface code to validate
331 the Routing Network.
332 Returns
333 x_status -- Result of the validation.
334 x_message -- Message to include the LOOP or Broken Link it they exist.
335 +--------------------------------------------------------------------------*/
336 PROCEDURE validate_routing_network(
337 p_rtg_sequence_id IN NUMBER,
338 p_assy_item_id IN NUMBER,
339 p_org_id IN NUMBER,
340 p_alt_rtg_desig IN VARCHAR2 DEFAULT NULL,
341 p_operation_type IN NUMBER,
342 x_status OUT VARCHAR2,
343 x_message OUT VARCHAR2) IS
344
345 l_rtg_seq_id NUMBER := NULL;
346 l_Lnk_Tbl Lnk_Tbl_Type;
347 l_connected INTEGER;
348 l_top_ptr INTEGER;
349 l_bot_ptr INTEGER;
350 l_flag_changed INTEGER := 1;
354
351 l_Str_Op_Tbl Op_Tbl_Type;
352 l_Mtr_Op_Tbl Op_Tbl_Type;
353 l_Con_Op_Tbl Op_Tbl_Type;
355 BEGIN
356 IF p_rtg_sequence_id is NOT NULL THEN
357 --dbms_output.put_line('rtg seq specified');
358 l_rtg_seq_id := p_rtg_sequence_id;
359 ELSE
360 --dbms_output.put_line('rtg seq retreived');
361 l_rtg_seq_id := get_routing_sequence_id (
362 p_assy_item_id,
363 p_org_id,
364 p_alt_rtg_desig);
365 END IF;
366
367 IF check_network_exists(l_rtg_seq_id, p_operation_type) THEN
368 --dbms_output.put_line('network EXISTS');
369
370 -- Check For LOOPS
371 -- Create the PL/SQL table
372 get_all_links (l_rtg_seq_id,
373 p_operation_type,
374 l_Lnk_Tbl);
375
376 --dbms_output.put_line('START check for loops');
377 -- get the top and bottom reference pointers
378 l_top_ptr := ref_pos(l_Lnk_Tbl);
379 l_bot_ptr := ref_rev_pos(l_Lnk_Tbl);
380 --dbms_output.put_line('top_ptr:'|| to_char(l_top_ptr));
381 --dbms_output.put_line('bot_ptr:'|| to_char(l_bot_ptr));
382
383 -- While there are rows with flag='C'
384 WHILE (l_top_ptr <> -1 and l_bot_ptr <> -1 and l_flag_changed = 1) LOOP
385 l_flag_changed := 0;
386 FOR t_counter IN l_top_ptr..l_bot_ptr LOOP
387 IF l_Lnk_Tbl(t_counter).flag = 'C' THEN
388 l_connected := 0;
389 --dbms_output.put_line('in loop1 :'||to_char(t_counter)||' time');
390 FOR j IN l_top_ptr..l_bot_ptr LOOP
391 IF (l_Lnk_Tbl(t_counter).from_op_seq_id = l_Lnk_Tbl(j).to_op_seq_id
392 AND l_Lnk_Tbl(j).flag = 'C') THEN
393 l_connected := 1;
394 END IF;
395 END LOOP;
396 IF (l_connected = 0) THEN
397 l_Lnk_Tbl(t_counter).flag := 'D';
398 l_flag_changed := 1;
399 --dbms_output.put_line('changing row# '||to_char(t_counter)
400 -- ||' '||'flag to D');
401 END IF;
402 END IF;
403 END LOOP;
404
405 l_top_ptr := ref_pos(l_Lnk_Tbl);
406 l_bot_ptr := ref_rev_pos(l_Lnk_Tbl);
407 --dbms_output.put_line('top_ptr:'|| to_char(l_top_ptr));
408 --dbms_output.put_line('bot_ptr:'|| to_char(l_bot_ptr));
409
410 l_flag_changed := 0;
411 IF (l_top_ptr <> -1 and l_bot_ptr <> -1) THEN
412 FOR b_counter IN REVERSE l_top_ptr..l_bot_ptr LOOP
413 IF l_Lnk_Tbl(b_counter).flag = 'C' THEN
414 l_connected := 0;
415 --dbms_output.put_line('in loop2 :'||to_char(b_counter)||' time');
416 FOR j IN REVERSE l_top_ptr..l_bot_ptr LOOP
417 IF (l_Lnk_Tbl(b_counter).to_op_seq_id = l_Lnk_Tbl(j).from_op_seq_id
418 and l_Lnk_Tbl(j).flag = 'C') THEN
419 l_connected := 1;
420 END IF;
421 END LOOP;
422 IF (l_connected = 0) THEN
423 l_Lnk_Tbl(b_counter).flag := 'D';
424 l_flag_changed := 1;
425 --dbms_output.put_line('changing row# '||to_char(b_counter)||' '||
426 -- 'flag to D');
427 END IF;
428 END IF;
429 END LOOP;
430 END IF;
431 l_top_ptr := ref_pos(l_Lnk_Tbl);
432 l_bot_ptr := ref_rev_pos(l_Lnk_Tbl);
433 --dbms_output.put_line('top_ptr:'|| to_char(l_top_ptr));
434 --dbms_output.put_line('bot_ptr:'|| to_char(l_bot_ptr));
435 END LOOP; -- while
436
437 IF (l_top_ptr <> -1 or l_bot_ptr <> -1) THEN
438 FND_MESSAGE.SET_NAME('BOM','BOM_RTG_NTWK_LOOP_EXISTS');
439 x_status := 'F'; -- LOOP exists
440 x_message:= FND_MESSAGE.GET;
441 RETURN;
442 END IF;
443 --dbms_output.put_line('END check for loops');
444
445 -- Check For BROKEN LINKS
446 --dbms_output.put_line('START check for BROKEN links');
447
448 get_all_start_nodes(l_rtg_seq_id,
449 p_operation_type,
450 l_Str_Op_Tbl);
451 -- add the first start node into the master list
452 l_Mtr_Op_Tbl(1).operation_seq_id := l_Str_Op_Tbl(1).operation_seq_id;
453 l_Mtr_Op_Tbl(1).operation_seq_num := l_Str_Op_Tbl(1).operation_seq_num;
454 --dbms_output.put_line('Mst: row# 1 '||to_char(l_Mtr_Op_Tbl(1).operation_seq_id)||' '||
455 -- to_char(l_Mtr_Op_Tbl(1).operation_seq_num));
456 -- get each start node, get the nodes it is connected to, compare with
457 -- the master list to see if there is any common node i.e. connected.
458 -- if there is, add the nodes to the master list. if there is none,
459 -- this is the broken link; print the broken link.
460
461 FOR i IN 1..l_Str_Op_Tbl.COUNT LOOP
462 create_con_op_list(l_Str_Op_Tbl(i).operation_seq_id,
463 l_Str_Op_Tbl(i).operation_seq_num,
464 l_Con_Op_Tbl);
465 IF is_connected(l_Con_Op_Tbl, l_Mtr_Op_Tbl) THEN
466 -- add the nodes to the master list
467 append_to_mst_lst(l_Con_Op_Tbl, l_Mtr_Op_Tbl);
468 -- add the starting node into the Master list
469 IF NOT find_op (l_Str_Op_Tbl(i).operation_seq_num, l_Mtr_Op_Tbl) THEN
470 l_Mtr_Op_Tbl(l_Mtr_Op_Tbl.COUNT+1).operation_seq_id :=
471 l_Str_Op_Tbl(i).operation_seq_id;
472 l_Mtr_Op_Tbl(l_Mtr_Op_Tbl.COUNT+1).operation_seq_num :=
473 l_Str_Op_Tbl(i).operation_seq_num;
474 END IF;
475 -- delete the connected list for re-use
476 l_Con_Op_Tbl.DELETE;
477 ELSE
478 --dbms_output.put_line('BROKEN link exists');
479 FND_MESSAGE.SET_NAME('BOM','BOM_RTG_NTWK_BROKEN_LINK_EXIST');
480 x_status := 'F'; -- BROKEN Link exists
481 x_message:= FND_MESSAGE.GET;
482 RETURN;
483 END IF;
484 END LOOP;
485
486 FND_MESSAGE.SET_NAME('BOM','BOM_RTG_NTWK_VALID');
487 x_status := 'S'; -- NO LOOPS or BROKEN Link exists
488 x_message:= FND_MESSAGE.GET;
489
490 ELSE
491 -- nothing done when NO network exists
492 x_status := 'F'; -- NO Network
493 x_message:= '';
494 END IF;
495
496 END validate_routing_network;
497
498
499 END BOM_RTG_NETWORK_VALIDATE_API;
[Home] [Help]