[Home] [Help]
PACKAGE BODY: APPS.BOM_VALIDATE_OP_NETWORK
Source
1 PACKAGE BODY BOM_Validate_Op_Network AS
2 /* $Header: BOMLONWB.pls 115.23 2004/03/19 12:37:16 earumuga ship $*/
3 /****************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMLONWB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_Validate_Op_Network
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 07-AUG-00 Biao Zhang Initial Creation
21 --
22 ****************************************************************************/
23
24 G_Pkg_Name VARCHAR2(30) := 'BOM_Validate_Op_Network';
25
26
27
28 /*******************************************************************
29 * Procedure : Check_Eam_Rtg_Network
30 * Parameters IN : Operation Network Exposed Record
31 * Operation Network Unexposed Record
32 * Old Operation Network exposed Record
33 * Old Operation Network Unexposed Record
34 * Mesg Token Table
35 * Parameters OUT: Mesg Token Table
36 * Return Status
37 * Purpose : Procedure will validate for eAM Rtg Network.
38 * This procedure is called internally by Check_Entity2.
39 *
40 *********************************************************************/
41 PROCEDURE Check_Eam_Rtg_Network
42 ( p_op_network_rec IN Bom_Rtg_Pub.Op_Network_Rec_Type
43 , p_op_network_unexp_rec IN Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type
44 , p_old_op_network_rec IN Bom_Rtg_Pub.Op_Network_Rec_Type
45 , p_old_op_network_unexp_rec
46 IN Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type
47 , p_mesg_token_tbl IN Error_Handler.Mesg_Token_Tbl_Type
48 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
49 , x_return_status IN OUT NOCOPY VARCHAR2
50 )
51 IS
52
53 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
54 l_return_status VARCHAR2(1);
55 l_err_msg VARCHAR2(2000);
56 l_common_routing_sequence_id NUMBER;
57
58
59 BEGIN
60
61 l_return_status := FND_API.G_RET_STS_SUCCESS;
62 x_return_status := FND_API.G_RET_STS_SUCCESS;
63 l_mesg_token_tbl := p_mesg_token_tbl;
64
65 IF BOM_Rtg_Globals.Get_Debug = 'Y'
66 THEN Error_Handler.Write_Debug
67 ('Within Operation Network Check Eam Rtg Network. . . ');
68 END IF;
69
70
71 -- Get Common Routing Seq Id from System Info Rec.
72 -- If the value is Null, set Common Routing Seq Id.
73 l_common_routing_sequence_id := BOM_Rtg_Globals.Get_Common_Rtg_Seq_id ;
74
75 IF l_common_routing_sequence_id IS NULL OR
76 l_common_routing_sequence_id = FND_API.G_MISS_NUM
77 THEN
78 BEGIN
79 SELECT common_routing_sequence_id
80 INTO l_common_routing_sequence_id
81 FROM bom_operational_routings
82 WHERE routing_sequence_id =
83 p_op_network_unexp_rec.routing_sequence_id ;
84 END ;
85
86 BOM_Rtg_Globals.Set_Common_Rtg_Seq_id
87 ( p_common_rtg_seq_id => l_common_routing_sequence_id );
88
89 END IF;
90
91 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
92 Error_Handler.Write_Debug('Call eAM Rtg Network validation API. . . ');
93 END IF;
94
95 -- Call Routing eAM API for eAM Rtg Network validation
96 Bom_Rtg_Eam_Util.Check_Eam_Rtg_Network
97 ( p_routing_sequence_id => p_op_network_unexp_rec.routing_sequence_id
98 , x_err_msg => l_err_msg
99 , x_return_status => l_return_status
100 ) ;
101
102 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
103
104 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
105 THEN
106 Error_Handler.Add_Error_Token
107 ( p_message_name => NULL
108 , p_message_text => l_err_msg
109 , p_mesg_token_tbl => l_mesg_token_tbl
110 , x_mesg_token_tbl => x_mesg_token_tbl
111 ) ;
112
113 END IF;
114 x_return_status := FND_API.G_RET_STS_ERROR ;
115 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS AND
116 l_err_msg IS NOT NULL THEN
117 Error_Handler.Add_Error_Token
118 (
119 p_message_name => NULL,
120 p_message_text => l_err_msg,
121 p_mesg_token_tbl => l_mesg_token_tbl,
122 x_mesg_token_tbl => x_mesg_token_tbl
123 );
124
125 END IF ;
126
127 END Check_Eam_Rtg_Network ;
128
129
130 /*******************************************************************
131 * Procedure : Check_Existence
132 * Returns : None
133 * Parameters IN : Operation Network Exposed Record
134 * Operation Network Unexposed Record
135 * Parameters OUT: Old Operation Network exposed Record
136 * Old Operation Network Unexposed Record
137 * Mesg Token Table
138 * Return Status
139 * Purpose : Procedure will query the routing revision
140 * record and return it in old record variables. If the
141 * Transaction Type is Create and the record already
142 * exists the return status would be error or if the
143 * transaction type is Update or Delete and the record
144 * does not exist then the return status would be an
145 * error as well. Mesg_Token_Table will carry the
146 * error messsage and the tokens associated with the
147 * message.
148 *********************************************************************/
149 PROCEDURE Check_Existence
150 ( p_op_network_rec IN Bom_Rtg_Pub.Op_Network_Rec_Type
151 , p_op_network_unexp_rec IN Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type
152 , x_old_op_network_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Network_Rec_Type
153 , x_old_op_network_unexp_rec
154 IN OUT NOCOPY Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type
155 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
156 , x_return_status IN OUT NOCOPY VARCHAR2
157 )
158 IS
159 l_token_tbl Error_Handler.Token_Tbl_Type;
160 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
161 l_return_status VARCHAR2(1);
162 cfm_flag NUMBER := NULL ;
163 BEGIN
164
165 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
166 Error_Handler.Write_Debug('Quering Op Network . . .' ) ;
167 END IF;
168
169 Bom_Op_Network_Util.Query_Row
170 ( p_from_op_seq_id =>
171 p_op_network_unexp_rec.from_op_seq_id
172 , p_to_op_seq_id =>
173 p_op_network_unexp_rec.to_op_seq_id
174 , x_op_network_rec => x_old_op_network_rec
175 , x_op_network_unexp_rec => x_old_op_network_unexp_rec
176 , x_return_status => l_return_status
177 );
178
179 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
180 Error_Handler.Write_Debug('Query Row Returned with : ' || l_return_status);
181 END IF;
182
183 IF l_return_status = BOM_Rtg_Globals.G_RECORD_FOUND AND
184 p_op_network_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
185 THEN
186 l_token_tbl(1).token_name := 'FROM_OP_SEQ_NUMBER';
187 l_token_tbl(1).token_value :=
188 p_op_network_rec.from_op_seq_number;
189 l_token_tbl(2).token_name := 'TO_OP_SEQ_NUMBER';
190 l_token_tbl(2).token_value :=
191 p_op_network_rec.to_op_seq_number;
192 Error_Handler.Add_Error_Token
193 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
194 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
195 , p_message_name => 'BOM_OP_NWK_ALREADY_EXISTS'
196 , p_token_tbl => l_token_tbl
197 );
198 l_return_status := FND_API.G_RET_STS_ERROR;
199 ELSIF l_return_status = BOM_Rtg_Globals.G_RECORD_NOT_FOUND AND
200 p_op_network_rec.transaction_type IN
201 ( BOM_Rtg_Globals.G_OPR_UPDATE, BOM_Rtg_Globals.G_OPR_DELETE)
202 THEN
203 l_token_tbl(1).token_name := 'FROM_OP_SEQ_NUMBER';
204 l_token_tbl(1).token_value :=
205 p_op_network_rec.from_op_seq_number;
206 l_token_tbl(2).token_name := 'TO_OP_SEQ_NUMBER';
207 l_token_tbl(2).token_value :=
208 p_op_network_rec.to_op_seq_number;
209 Error_Handler.Add_Error_Token
210 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
211 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
212 , p_message_name => 'BOM_OP_NWK_DOESNOT_EXISTS'
213 , p_token_tbl => l_token_tbl
214 );
215 l_return_status := FND_API.G_RET_STS_ERROR;
216 ELSIF l_Return_status = FND_API.G_RET_STS_UNEXP_ERROR
217 THEN
218 l_token_tbl(1).token_name := 'FROM_OP_SEQ_NUMBER';
219 l_token_tbl(1).token_value :=
220 p_op_network_rec.from_op_seq_number;
221 l_token_tbl(2).token_name := 'TO_OP_SEQ_NUMBER';
222 l_token_tbl(2).token_value :=
223 p_op_network_rec.to_op_seq_number;
224 Error_Handler.Add_Error_Token
225 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
226 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
227 , p_message_name => NULL
228 , p_message_text =>
229 'Unexpected error while existence verification of ' ||
230 'operation network'||
231 p_op_network_rec.assembly_item_name
232 , p_token_tbl => l_token_tbl
233 );
234 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
235 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS
236 THEN
237 l_return_status := FND_API.G_RET_STS_SUCCESS;
238 END IF;
239
240 x_return_status := l_return_status;
241 x_mesg_token_tbl := l_mesg_token_tbl;
242
243
244 END Check_Existence;
245
246
247
248 /**********************************************************************
249 * Procedure : Check_Acces
250 * Returns : None
251 * Parameters IN : Operation Network Exposed Record
252 * Operation Network Unexposed Record
253 * Parameters OUT: Old Operation Network exposed Record
254 * Old Operation Network Unexposed Record
255 * Mesg Token Table
256 * Return Status
257 * Purpose : This procedure will check if the user has access to
258 * the operations for Op Network.
259 **********************************************************************/
260 PROCEDURE Check_Access
261 ( p_op_network_rec IN Bom_Rtg_Pub.Op_Network_Rec_Type
262 , p_op_network_unexp_rec IN Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type
263 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
264 , x_return_status IN OUT NOCOPY VARCHAR2
265 )
266 IS
267 l_Token_Tbl Error_Handler.Token_Tbl_Type;
268 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
269 l_return_status VARCHAR2(1);
270 l_err_text VARCHAR2(2000);
271 l_cfm_flag NUMBER ;
272
273 -- Check if Department is valid
274 CURSOR l_nwkop_csr ( p_op_seq_id NUMBER
275 )
276 IS
277 SELECT standard_operation_id
278 ,disable_date
279 FROM BOM_OPERATION_SEQUENCES bos
280 WHERE bos.operation_sequence_id = p_op_seq_id ;
281
282 BEGIN
283
284 l_return_status := FND_API.G_RET_STS_SUCCESS;
285
286 IF BOM_Rtg_Globals.Get_Debug = 'Y'
287 THEN Error_Handler.Write_Debug
288 ('Within Operation Network Check Access. . . ');
289 END IF;
290
291 -- Set Token Value
292 l_token_tbl(1).token_name := 'FROM_OP_SEQ_NUMBER';
293 l_token_tbl(1).token_value :=
294 NVL( p_op_network_rec.new_from_op_seq_number
295 , p_op_network_rec.from_op_seq_number) ;
296 l_token_tbl(2).token_name := 'TO_OP_SEQ_NUMBER';
297 l_token_tbl(2).token_value :=
298 NVL( p_op_network_rec.new_to_op_seq_number
299 , p_op_network_rec.to_op_seq_number) ;
300
301
302
303 -- Check if operation is valid on current date when the current operation
304 -- is for Lot Based routing.
305 l_cfm_flag := BOM_Rtg_Globals.Get_CFM_Rtg_Flag ;
306
307
308 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
309 ('Cfm Routing Flag is ' || to_char(l_cfm_flag) ||
310 '. . . Eam Item Type is ' || to_char(BOM_Rtg_Globals.Get_Eam_Item_Type) ) ;
311 END IF ;
312
313 IF l_cfm_flag IS NULL OR
314 l_cfm_flag = FND_API.G_MISS_NUM
315 THEN
316 l_cfm_flag := Bom_Rtg_Validate.Get_Flow_Routing_Flag
317 (p_op_network_unexp_rec.routing_sequence_id) ;
318 BOM_Rtg_Globals.Set_CFM_Rtg_Flag(p_cfm_rtg_type => l_cfm_flag) ;
319 END IF;
320
321 IF l_cfm_flag = BOM_Rtg_Globals.G_FLOW_RTG
322 THEN
323
324 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
325 ('Check flow routing network . . . ' ) ;
326 END IF ;
327
328 -- In current release, validations for Check Access
329 -- in Flow Rtg's Op Network do not exist.
330 NULL ;
331
332 ELSIF l_cfm_flag = BOM_Rtg_Globals.G_LOT_RTG
333 THEN
334
335 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
336 ('Check WSM routing network . . . ' ) ;
337 END IF ;
338 if BOM_Rtg_GLobals.Get_CFM_Rtg_Flag <> BOM_Rtg_Globals.G_Lot_Rtg then --for bug 3132411
339 FOR l_nwkop_rec IN l_nwkop_csr
340 ( p_op_seq_id => NVL(p_op_network_unexp_rec.new_from_op_seq_id,
341 p_op_network_unexp_rec.from_op_seq_id )
342 )
343 LOOP
344 IF l_nwkop_rec.standard_operation_id IS NULL THEN
345 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
346 THEN
347 Error_Handler.Add_Error_Token
348 ( p_message_name => 'BOM_OP_NWK_STDOP_REQUIRED'
349 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
350 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
351 , p_Token_Tbl => l_Token_Tbl
352 ) ;
353 END IF ;
354 l_return_status := FND_API.G_RET_STS_ERROR ;
355 END IF ;
356
357 IF NVL(l_nwkop_rec.disable_date , TRUNC(sysdate)+1 )
358 <= TRUNC(sysdate)
359 THEN
360 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
361 THEN
362 Error_Handler.Add_Error_Token
363 ( p_message_name => 'BOM_OP_NWK_ALREADY_DISABLED'
364 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
365 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
366 , p_Token_Tbl => l_Token_Tbl
367 ) ;
368 END IF ;
369 l_return_status := FND_API.G_RET_STS_ERROR ;
370 END IF ;
371
372 END LOOP ;
373
374 IF l_return_status <> FND_API.G_RET_STS_ERROR
375 THEN
376 FOR l_nwkop_rec IN l_nwkop_csr
377 ( p_op_seq_id => NVL( p_op_network_unexp_rec.new_to_op_seq_id
378 , p_op_network_unexp_rec.to_op_seq_id )
379 )
380 LOOP
381 IF l_nwkop_rec.standard_operation_id IS NULL THEN
382 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
383 THEN
384 Error_Handler.Add_Error_Token
385 ( p_message_name => 'BOM_OP_NWK_STDOP_REQUIRED'
386 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
387 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
388 , p_Token_Tbl => l_Token_Tbl
389 ) ;
390 END IF ;
391 l_return_status := FND_API.G_RET_STS_ERROR ;
392 END IF ;
393
394 IF NVL(l_nwkop_rec.disable_date , TRUNC(sysdate) + 1)
395 <= TRUNC(sysdate)
396 THEN
397 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
398 THEN
399 Error_Handler.Add_Error_Token
400 ( p_message_name => 'BOM_OP_NWK_ALREADY_DISABLED'
401 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
402 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
403 , p_Token_Tbl => l_Token_Tbl
404 ) ;
405 END IF ;
406 l_return_status := FND_API.G_RET_STS_ERROR ;
407 END IF ;
408
409 END LOOP ;
410
411 END IF ;
412 end if; --for bug 3132411
413
414 -- For eAM enhancement
415 -- Check access for network of Maintenace Routings
416 ELSIF l_cfm_flag = BOM_Rtg_Globals.G_STD_RTG
417 AND BOM_Rtg_Globals.Get_Eam_Item_Type = BOM_Rtg_Globals.G_ASSET_ACTIVITY
418 THEN
419
420 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
421 ('Check maintenance routing network . . . Eam Item Type is ' || to_char(BOM_Rtg_Globals.Get_Eam_Item_Type) ) ;
422 END IF ;
423
424 -- Check if the from operation has been disabled
425 FOR l_nwkop_rec IN l_nwkop_csr
426 ( p_op_seq_id => NVL( p_op_network_unexp_rec.new_from_op_seq_id
427 , p_op_network_unexp_rec.from_op_seq_id )
428 )
429 LOOP
430
431 IF NVL(l_nwkop_rec.disable_date , TRUNC(sysdate)+1 )
432 <= TRUNC(sysdate)
433 THEN
434 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
435 THEN
436 Error_Handler.Add_Error_Token
437 ( p_message_name => 'BOM_OP_NWK_ALREADY_DISABLED'
438 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
439 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
440 , p_Token_Tbl => l_Token_Tbl
441 ) ;
442 END IF ;
443 l_return_status := FND_API.G_RET_STS_ERROR ;
444 END IF ;
445
446 END LOOP ;
447
448
449 IF l_return_status <> FND_API.G_RET_STS_ERROR
450 THEN
451 -- Check if the to operation has been disabled
452 FOR l_nwkop_rec IN l_nwkop_csr
453 ( p_op_seq_id => NVL( p_op_network_unexp_rec.new_to_op_seq_id
454 , p_op_network_unexp_rec.to_op_seq_id)
455 )
456 LOOP
457
458 IF NVL(l_nwkop_rec.disable_date , TRUNC(sysdate) + 1)
459 <= TRUNC(sysdate)
460 THEN
461 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
462 THEN
463 Error_Handler.Add_Error_Token
464 ( p_message_name => 'BOM_OP_NWK_ALREADY_DISABLED'
465 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
466 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
467 , p_Token_Tbl => l_Token_Tbl
468 ) ;
469 END IF ;
470 l_return_status := FND_API.G_RET_STS_ERROR ;
471 END IF ;
472
473 END LOOP ;
474
475 END IF ;
476
477
478 -- For eAM enhancement, following cfm routing flag validation
479 -- is moved from BOM_RTG_Val_To_Id.OP_Network_UUI_To_UI procedure
480 -- and added condition for maintenance routing
481 ELSIF l_cfm_flag <> BOM_Rtg_Globals.G_FLOW_RTG
482 AND l_cfm_flag <> BOM_Rtg_Globals.G_LOT_RTG
483 AND BOM_Rtg_Globals.Get_Eam_Item_Type <> BOM_Rtg_Globals.G_ASSET_ACTIVITY
484 THEN
485
486 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
487 ('Check if parenet routing is a standard routing. ' || l_return_status) ;
488 END IF ;
489 l_token_tbl.delete ;
490 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
491 l_token_tbl(1).token_value :=
492 p_op_network_rec.assembly_item_name;
493 Error_Handler.Add_Error_Token
494 ( p_Message_Name => 'BOM_OP_NWK_RTG_INVALID'
495 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
496 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
497 , p_Token_Tbl => l_Token_Tbl
498 );
499 l_return_status := FND_API.G_RET_STS_ERROR ;
500
501 END IF ;
502
503 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
504 ('Check if operations for network are valid. ' || l_return_status) ;
505 END IF ;
506 -- Return Status and Message Token
507 x_return_status := l_return_status;
508 x_mesg_token_tbl := l_mesg_token_tbl;
509
510 EXCEPTION
511 WHEN OTHERS THEN
512 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
513 ('Some unknown error in Check Access. . .' || SQLERRM );
514 END IF ;
515
516
517 l_err_text := G_PKG_NAME || ' Validation (Check Access) '
518 || substrb(SQLERRM,1,200);
519
520 Error_Handler.Add_Error_Token
521 ( p_message_name => NULL
522 , p_message_text => l_err_text
523 , p_mesg_token_tbl => l_mesg_token_tbl
524 , x_mesg_token_tbl => l_mesg_token_tbl
525 ) ;
526
527 -- Return the status and message table.
528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
529 x_mesg_token_tbl := l_mesg_token_tbl ;
530
531
532 END Check_Access ;
533
534
535 /********************************************************************
536 * Procedure : Check_Attributes
537 * Parameters IN : Operation Network Exposed Column record
538 * Operation Network Unexposed Column record
539 * Old Operation Network Exposed Column record
540 * Old Operation Network unexposed column record
541 * Parameters OUT: Return Status
542 * Mesg Token Table
543 * Purpose : Check_Attrbibutes procedure will validate every
544 * Operation Network attrbiute in its entirety.
545 **********************************************************************/
546 PROCEDURE Check_Attributes
547 ( x_return_status IN OUT NOCOPY VARCHAR2
548 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
549 --, p_op_network_tbl IN Bom_Rtg_Pub.Op_Network_Tbl_Type
550 , p_op_network_Rec IN Bom_Rtg_Pub.Op_Network_Rec_Type
551 , p_op_network_unexp_rec IN Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type
552 , p_old_op_network_rec IN Bom_Rtg_Pub.Op_Network_Rec_Type
553 , p_old_op_network_unexp_rec IN Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type
554 )
555 IS
556 l_err_text VARCHAR2(2000) := NULL;
557 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
558 l_Token_Tbl Error_Handler.Token_Tbl_Type;
559 l_cfm_flag NUMBER := NULL ;
560
561 BEGIN
562
563 x_return_status := FND_API.G_RET_STS_SUCCESS;
564
565 IF BOM_Rtg_Globals.Get_Debug = 'Y'
566 THEN Error_Handler.Write_Debug
567 ('Within Operation Network Check Attributes . . . ');
568 END IF;
569
570 -- Set Token Value
571 l_token_tbl(1).token_name := 'FROM_OP_SEQ_NUMBER';
572 l_token_tbl(1).token_value :=
573 p_op_network_rec.from_op_seq_number;
574 l_token_tbl(2).token_name := 'TO_OP_SEQ_NUMBER';
575 l_token_tbl(2).token_value :=
576 p_op_network_rec.to_op_seq_number;
577
578
579 --
580 -- Check if the user is trying to update a record with
581 -- missing value when the column value is required.
582 --
583
584 IF p_op_network_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
585 THEN
586
587
588 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
589 ('Operation Attr Validation: Missing Value. . . ' || x_return_status) ;
590 END IF;
591
592 -- Connection(Transition) Type
593 IF p_op_network_rec.connection_type = FND_API.G_MISS_NUM
594 THEN
595 Error_Handler.Add_Error_Token
596 ( p_Message_Name => 'BOM_OP_NWK_CNTTYPE_MISSING'
597 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
598 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
599 , p_Token_Tbl => l_Token_Tbl
600 );
601 x_return_status := FND_API.G_RET_STS_ERROR;
602 END IF ;
603
604
605 -- Planning Percent
606 IF p_op_network_rec.planning_percent = FND_API.G_MISS_NUM
607 THEN
608 Error_Handler.Add_Error_Token
609 ( p_Message_Name => 'BOM_OP_NWK_PLNPCT_MISSING'
610 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
611 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
612 , p_Token_Tbl => l_Token_Tbl
613 );
614 x_return_status := FND_API.G_RET_STS_ERROR;
615 END IF;
616 END IF ;
617
618 IF ( p_op_network_rec.From_X_Coordinate <> FND_API.G_MISS_NUM
619 AND p_op_network_rec.From_X_Coordinate < 0 )
620 OR ( p_op_network_rec.From_Y_Coordinate <> FND_API.G_MISS_NUM
621 AND p_op_network_rec.From_Y_Coordinate < 0 )
622 OR ( p_op_network_rec.To_X_Coordinate <> FND_API.G_MISS_NUM
623 AND p_op_network_rec.To_X_Coordinate < 0 )
624 OR ( p_op_network_rec.To_Y_Coordinate <> FND_API.G_MISS_NUM
625 AND p_op_network_rec.To_Y_Coordinate < 0 ) THEN
626 Error_Handler.Add_Error_Token
627 ( p_message_name => 'BOM_OP_NWK_COORD_NEGATIVE'
628 , p_token_tbl => l_token_tbl
629 , p_mesg_token_tbl => l_mesg_token_tbl
630 , x_mesg_token_tbl => l_mesg_token_tbl
631 );
632
633 x_return_status := FND_API.G_RET_STS_ERROR;
634
635 END IF;
636 /*
637 FOR I in 1..p_op_network_tbl.COUNT LOOP
638 IF ((p_op_network_tbl(I).from_op_seq_number =
639 p_op_network_rec.from_op_seq_number) AND
640 ((p_op_network_tbl(I).From_X_Coordinate <>
641 p_op_network_rec.From_X_Coordinate) OR
642 (p_op_network_tbl(I).From_Y_Coordinate <>
643 p_op_network_rec.From_Y_Coordinate))) OR
644 ((p_op_network_tbl(I).from_op_seq_number =
645 p_op_network_rec.to_op_seq_number) AND
646 ((p_op_network_tbl(I).From_X_Coordinate <>
647 p_op_network_rec.To_X_Coordinate) OR
648 (p_op_network_tbl(I).From_Y_Coordinate <>
649 p_op_network_rec.To_Y_Coordinate))) OR
650 ((p_op_network_tbl(I).To_op_seq_number =
651 p_op_network_rec.from_op_seq_number) AND
652 ((p_op_network_tbl(I).To_X_Coordinate <>
653 p_op_network_rec.From_X_Coordinate) OR
654 (p_op_network_tbl(I).To_Y_Coordinate <>
655 p_op_network_rec.From_Y_Coordinate))) OR
656 ((p_op_network_tbl(I).To_op_seq_number =
657 p_op_network_rec.To_op_seq_number) AND
658 ((p_op_network_tbl(I).To_X_Coordinate <>
659 p_op_network_rec.To_X_Coordinate) OR
660 (p_op_network_tbl(I).To_Y_Coordinate <>
661 p_op_network_rec.To_Y_Coordinate))) THEN
662
663 Error_Handler.Add_Error_Token
664 ( p_message_name => 'BOM_OP_NWK_COORD_MISMATCH'
665 , p_token_tbl => l_token_tbl
666 , p_mesg_token_tbl => l_mesg_token_tbl
667 , x_mesg_token_tbl => l_mesg_token_tbl
668 );
669
670 x_return_status := FND_API.G_RET_STS_ERROR;
671
672 END IF;
673
674 END LOOP;
675 */
676 l_cfm_flag := BOM_Rtg_Globals.Get_CFM_Rtg_Flag ;
677
678
679 IF l_cfm_flag IS NULL OR
680 l_cfm_flag = FND_API.G_MISS_NUM
681 THEN
682 l_cfm_flag := Bom_Rtg_Validate.Get_Flow_Routing_Flag
683 (p_op_network_unexp_rec.routing_sequence_id) ;
684 BOM_Rtg_Globals.Set_CFM_Rtg_Flag(p_cfm_rtg_type => l_cfm_flag) ;
685 END IF;
686
687 IF p_op_network_rec.connection_type NOT IN (1, 2)
688 AND p_op_network_rec.connection_type is NOT NULL
689 AND p_op_network_rec.connection_type <> FND_API.G_MISS_NUM
690 AND l_cfm_flag = BOM_Rtg_Globals.G_LOT_RTG
691 THEN
692 Error_Handler.Add_Error_Token
693 ( p_message_name => 'BOM_OP_NWK_CNTYPE_INVALID'
694 , p_token_tbl => l_token_tbl
695 , p_mesg_token_tbl => l_mesg_token_tbl
696 , x_mesg_token_tbl => l_mesg_token_tbl
697 );
698
699 x_return_status := FND_API.G_RET_STS_ERROR;
700
701 -- Flow Routing's Network can have connetion type : 3 - Rework
702 ELSIF p_op_network_rec.connection_type NOT IN (1, 2, 3)
703 AND p_op_network_rec.connection_type is NOT NULL
704 AND p_op_network_rec.connection_type <> FND_API.G_MISS_NUM
705 AND l_cfm_flag = BOM_Rtg_Globals.G_FLOW_RTG
706 THEN
707
708 Error_Handler.Add_Error_Token
709 ( p_message_name => 'BOM_OP_NWK_CNTYPE_INVALID'
710 , p_token_tbl => l_token_tbl
711 , p_mesg_token_tbl => l_mesg_token_tbl
712 , x_mesg_token_tbl => l_mesg_token_tbl
713 );
714
715 x_return_status := FND_API.G_RET_STS_ERROR;
716
717 END IF;
718
719 IF (p_op_network_rec.planning_percent > 100
720 OR p_op_network_rec.planning_percent < 0)
721 AND p_op_network_rec.planning_percent IS NOT NULL
722 AND p_op_network_rec.planning_percent <> FND_API.G_MISS_NUM
723 THEN
724
725 Error_Handler.Add_Error_Token
726 ( p_message_name => 'BOM_OP_NWK_PLNPCT_INVALID'
727 , p_token_tbl => l_token_tbl
728 , p_mesg_token_tbl => l_mesg_token_tbl
729 , x_mesg_token_tbl => l_mesg_token_tbl
730 );
731 x_return_status := FND_API.G_RET_STS_ERROR;
732 END IF;
733
734 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
735
736 EXCEPTION
737 WHEN OTHERS THEN
738 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
739 ('Some unknown error in Attribute Validation . . .' || SQLERRM );
740 END IF ;
741
742
743 l_err_text := G_PKG_NAME || ' Validation (Attr. Validation) '
744 || substrb(SQLERRM,1,200);
745
746 Error_Handler.Add_Error_Token
747 ( p_message_name => NULL
748 , p_message_text => l_err_text
749 , p_mesg_token_tbl => l_mesg_token_tbl
750 , x_mesg_token_tbl => l_mesg_token_tbl
751 ) ;
752
753 -- Return the status and message table.
754 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
755 x_mesg_token_tbl := l_mesg_token_tbl ;
756
757
758 END Check_Attributes;
759
760 /********************************************************************
761 * Procedure : Check_Entity1
762 * Parameters IN : operation network Exposed column record
763 * operation network Unexposed column record
764 * Old operation network exposed column record
765 * Old operation network unexposed column record
766 * Parameters OUT: Message Token Table
767 * Return Status
768 * Purpose : This procedure will perform the business logic
769 * validation for the operation network Entity.It will
770 * perform any cross entity validations and make sure
771 * that the user is not entering values which may
772 * disturb the integrity of the data.
773 *********************************************************************/
774 PROCEDURE Check_Entity1
775 ( p_op_network_rec IN Bom_Rtg_Pub.Op_Network_Rec_Type
776 , p_op_network_unexp_rec IN Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type
777 , p_old_op_network_rec IN Bom_Rtg_Pub.Op_Network_Rec_Type
778 , p_old_op_network_unexp_rec IN Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type
779 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
780 , x_return_status IN OUT NOCOPY VARCHAR2
781 )
782 IS
783
784 Cursor c_op_network
785 ( P_From_Op_Seq_Id number
786 , P_To_Op_Seq_Id number
787 )
788 IS
789 SELECT 'x' dummy
790 FROM DUAL
791 WHERE EXISTS
792 ( SELECT NULL
793 FROM bom_operation_networks a
794 WHERE a.from_op_seq_id = P_From_Op_Seq_Id
795 AND a.to_op_seq_id <> P_To_Op_Seq_Id
796 AND a.transition_type = 1
797 );
798
799
800 CURSOR check_unique_network_csr ( p_from_op_seq_id NUMBER
801 , p_to_op_seq_id NUMBER )
802 IS
803 SELECT 'Not Unique'
804 FROM SYS.DUAL
805 WHERE EXISTS ( SELECT NULL
806 FROM bom_operation_networks a
807 WHERE a.from_op_seq_id = p_from_op_seq_id
808 AND a.to_op_seq_id = p_to_op_seq_id
809 );
810
811
812 l_total_planning_pct NUMBER :=0;
813 l_planning_pct NUMBER :=0;
814 l_token_tbl Error_Handler.Token_Tbl_Type;
815 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
816 l_dummy NUMBER;
817 l_err_text VARCHAR2(2000) ;
818 l_return_status VARCHAR2(1);
819 l_err_code NUMBER;
820
821 PASS_CHECK_ENTITY1_FOR_EAM EXCEPTION ;
822
823
824 BEGIN
825
826
827 x_return_status := FND_API.G_RET_STS_SUCCESS;
828
829 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
830 Error_Handler.Write_Debug ('Within Operation Network Check Entity1 . . . ');
831 END IF;
832
833 -- Set Token Value
834 l_token_tbl(1).token_name := 'FROM_OP_SEQ_NUMBER';
835 l_token_tbl(1).token_value :=
836 NVL( p_op_network_rec.new_from_op_seq_number
837 , p_op_network_rec.from_op_seq_number ) ;
838 l_token_tbl(2).token_name := 'TO_OP_SEQ_NUMBER';
839 l_token_tbl(2).token_value :=
840 NVL( p_op_network_rec.new_to_op_seq_number
841 , p_op_network_rec.to_op_seq_number ) ;
842
843 IF p_op_network_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
844 AND ( p_op_network_unexp_rec.new_from_op_seq_id IS NOT NULL OR
845 p_op_network_unexp_rec.new_to_op_seq_id IS NOT NULL )
846 THEN
847
848 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
849 Error_Handler.Write_Debug('Check op network uniqueness for UPDATE . . . ');
850 END IF;
851
852 FOR l_uniqe_rec in check_unique_network_csr
853 ( P_From_Op_Seq_Id => NVL(p_op_network_unexp_rec.new_from_op_seq_id,
854 p_op_network_unexp_rec.from_op_seq_id) ,
855 P_To_Op_Seq_Id => NVL(p_op_network_unexp_rec.new_to_op_seq_id,
856 p_op_network_unexp_rec.to_op_seq_id)
857 )
858 LOOP
859
860 Error_Handler.Add_Error_Token
861 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
862 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
863 , p_message_name => 'BOM_OP_NWK_ALREADY_EXISTS'
864 , p_token_tbl => l_token_tbl
865 );
866 x_return_status := FND_API.G_RET_STS_ERROR;
867
868 END LOOP ;
869 END IF ;
870
871
872 -- For eAM enhancement.
873 IF BOM_Rtg_Globals.Get_Eam_Item_Type = BOM_Rtg_Globals.G_ASSET_ACTIVITY
874 THEN
875 NULL ;
876
877
878 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
879 Error_Handler.Write_Debug('Call eAM Rtg Network validation API. . . ');
880 END IF;
881 /*
882 -- This validation no longer used
883 -- Call Routing eAM API for eAM Rtg Network validation
884 Bom_Rtg_Eam_Util.Check_Eam_Nwk_FromOp
885 ( p_from_op_seq_num => p_op_network_rec.from_op_seq_number
886 , p_from_op_seq_id => p_op_network_unexp_rec.from_op_seq_id
887 , p_to_op_seq_num => p_op_network_rec.to_op_seq_number
888 , p_to_op_seq_id => p_op_network_unexp_rec.to_op_seq_id
889 , x_err_msg => l_err_text
890 , x_return_status => l_return_status
891 ) ;
892
893 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
894
895 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
896 THEN
897 Error_Handler.Add_Error_Token
898 ( p_message_name => NULL
899 , p_message_text => l_err_text
900 , p_mesg_token_tbl => l_mesg_token_tbl
901 , x_mesg_token_tbl => l_mesg_token_tbl
902 ) ;
903
904 END IF;
905 x_return_status := FND_API.G_RET_STS_ERROR ;
906 END IF ;
907
908 -- If operation network is for maintenance routing,
909 -- pass through followings validations.
910
911 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
912 Error_Handler.Write_Debug ('Pass Operation Network Check Entity1 in maintenance routings . . . ');
913 END IF;
914 */
915
916 RAISE PASS_CHECK_ENTITY1_FOR_EAM ;
917
918
919 END IF ;
920
921 --
922 -- Merge step 12 into this procedure
923 -- Check conditionally required attributes
924 --
925 IF p_op_network_rec.planning_percent IS NULL
926 OR p_op_network_rec.planning_percent = FND_API.G_MISS_NUM
927 THEN
928
929 Error_Handler.Add_Error_Token
930 ( p_message_name => 'BOM_OP_NWK_PLNPCT_REQUIRED'
931 , p_token_tbl => l_token_tbl
932 , p_mesg_token_tbl => l_mesg_token_tbl
933 , x_mesg_token_tbl => l_mesg_token_tbl
934 );
935 x_return_status := FND_API.G_RET_STS_ERROR;
936 END IF;
937
938 --
939 -- If connection type =1, there must be only one primary from operation. --
940 --
941 IF p_op_network_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE AND
942 p_op_network_rec.connection_type = 1
943 THEN
944 FOR l_opnet_rec in c_op_network
945 ( P_From_Op_Seq_Id => NVL(p_op_network_unexp_rec.new_from_op_seq_id,
946 p_op_network_unexp_rec.from_op_seq_id) ,
947 P_To_Op_Seq_Id => p_op_network_unexp_rec.to_op_seq_id
948 )
949 LOOP
950
951 l_token_tbl.DELETE(2) ;
952
953 IF p_op_network_rec.operation_type = 1
954 THEN
955
956 Error_Handler.Add_Error_Token
957 ( p_message_name => 'BOM_OP_NWK_PMOP_NOTUNIQUE'
958 , p_token_tbl => l_token_tbl
959 , p_mesg_token_tbl => l_mesg_token_tbl
960 , x_mesg_token_tbl => l_mesg_token_tbl
961 );
962 x_return_status := FND_API.G_RET_STS_ERROR;
963
964 ELSIF p_op_network_rec.operation_type = 2
965 THEN
966
967 Error_Handler.Add_Error_Token
968 ( p_message_name => 'BOM_OP_NWK_PMPRCS_NOTUNIQUE'
969 , p_token_tbl => l_token_tbl
970 , p_mesg_token_tbl => l_mesg_token_tbl
971 , x_mesg_token_tbl => l_mesg_token_tbl
972 );
973 x_return_status := FND_API.G_RET_STS_ERROR;
974 ELSIF p_op_network_rec.operation_type = 3
975 THEN
976
977 Error_Handler.Add_Error_Token
978 ( p_message_name => 'BOM_OP_NWK_PMLO_NOTUNIQUE'
979 , p_token_tbl => l_token_tbl
980 , p_mesg_token_tbl => l_mesg_token_tbl
981 , x_mesg_token_tbl => l_mesg_token_tbl
982 );
983 x_return_status := FND_API.G_RET_STS_ERROR;
984 END IF;
985
986 END LOOP;
987 END IF;
988
989
990 --
991 -- Check planning percentage.
992 -- For update or create, total of Planning Percent for Operation
993 -- Network must be smaller than 100%.
994 --
995
996 -- Changed the condition to support updating from op and to op id
997 -- IF p_op_network_rec.transaction_type IN (BOM_Rtg_Globals.G_OPR_CREATE ,
998 -- BOM_Rtg_Globals.G_OPR_UPDATE)
999 -- Added similar validation in Check_Entity2 for updating.
1000
1001 IF p_op_network_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
1002 AND p_op_network_rec.connection_type IN (1, 2)
1003 THEN
1004 SELECT NVL(SUM(planning_pct), 0)
1005 INTO l_total_planning_pct
1006 FROM bom_operation_networks
1007 WHERE from_op_seq_id = p_op_network_unexp_rec.from_op_seq_id
1008 AND to_op_seq_id <> p_op_network_unexp_rec.to_op_seq_id
1009 AND transition_type IN (1, 2);
1010
1011 -- select planning percent for update process
1012 IF p_op_network_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
1013 THEN
1014 SELECT NVL(planning_pct,0)
1015 INTO l_planning_pct
1016 FROM bom_operation_networks
1017 WHERE from_op_seq_id = p_op_network_unexp_rec.from_op_seq_id
1018 AND to_op_seq_id = p_op_network_unexp_rec.to_op_seq_id
1019 AND transition_type IN (1, 2);
1020 END IF;
1021
1022
1023 l_total_planning_pct := l_total_planning_pct
1024 + p_op_network_rec.planning_percent ;
1025
1026
1027 /*
1028 l_total_planning_pct := l_total_planning_pct
1029 + p_op_network_rec.planning_percent
1030 - l_planning_pct ;
1031 */
1032
1033
1034 IF (l_total_planning_pct > 100)
1035 THEN
1036
1037 l_token_tbl.DELETE(2) ;
1038
1039 IF p_op_network_rec.operation_type IN (1,2)
1040 THEN
1041 Error_Handler.Add_Error_Token
1042 ( p_message_name => 'BOM_OP_NWK_PRCS_PLNPCT_INVALID'
1043 , p_token_tbl => l_token_tbl
1044 , p_mesg_token_tbl => l_mesg_token_tbl
1045 , x_mesg_token_tbl => l_mesg_token_tbl
1046 );
1047 x_return_status := FND_API.G_RET_STS_ERROR;
1048 ELSIF p_op_network_rec.operation_type = 3
1049 THEN
1050 Error_Handler.Add_Error_Token
1051 ( p_message_name => 'BOM_OP_NWK_LO_PLNLPCT_INVALID'
1052 , p_token_tbl => l_token_tbl
1053 , p_mesg_token_tbl=> l_mesg_token_tbl
1054 , x_mesg_token_tbl=> l_mesg_token_tbl
1055 );
1056 x_return_status := FND_API.G_RET_STS_ERROR;
1057 END IF;
1058
1059 END IF;
1060 END IF;
1061
1062 --For Delete Link OSFM constraint
1063
1064 IF p_op_network_rec.transaction_type = BOM_Rtg_Globals.G_OPR_DELETE
1065 AND BOM_RTG_Globals.Is_Osfm_NW_Calc_Flag
1066 AND
1067 WSMPUTIL.JOBS_WITH_QTY_AT_FROM_OP (x_err_code => l_err_code,
1068 x_err_msg => l_err_text,
1069 p_routing_sequence_id => p_op_network_unexp_rec.Routing_Sequence_Id,
1070 p_operation_seq_num => p_op_network_rec.From_Op_Seq_Number)
1071 THEN
1072 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
1073 l_token_tbl(1).token_value := p_op_network_rec.From_Op_Seq_Number;
1074 Error_Handler.Add_Error_Token(p_message_name => 'BOM_WSM_FROM_OP_ACTIVE_JOB',
1075 p_mesg_token_tbl => l_mesg_token_tbl,
1076 p_token_tbl => l_token_tbl,
1077 x_mesg_token_tbl => l_mesg_token_tbl);
1078 x_return_status := Error_Handler.G_Status_Error;
1079 END IF;
1080 --End of Delete Link OSFM Constraint
1081
1082 x_mesg_token_tbl := l_mesg_token_tbl;
1083
1084 EXCEPTION
1085 WHEN PASS_CHECK_ENTITY1_FOR_EAM THEN
1086 x_mesg_token_tbl := l_mesg_token_tbl;
1087
1088 WHEN OTHERS THEN
1089 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1090 ('Some unknown error in Entity Validation1. . .' || SQLERRM );
1091 END IF ;
1092
1093
1094 l_err_text := G_PKG_NAME || ' Validation (Entity Validation1) '
1095 || substrb(SQLERRM,1,200);
1096
1097 Error_Handler.Add_Error_Token
1098 ( p_message_name => NULL
1099 , p_message_text => l_err_text
1100 , p_mesg_token_tbl => l_mesg_token_tbl
1101 , x_mesg_token_tbl => l_mesg_token_tbl
1102 ) ;
1103
1104 -- Return the status and message table.
1105 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1106 x_mesg_token_tbl := l_mesg_token_tbl ;
1107
1108
1109 END Check_Entity1;
1110
1111
1112 /********************************************************************
1113 * Procedure : Check_Entity2
1114 * Parameters IN : Operation Network Exposed column record
1115 * Operation Network Unexposed column record
1116 * Old operation network exposed column record
1117 * Old operation network unexposed column record
1118 * Parameters OUT: Message Token Table
1119 * Return Status
1120 * Purpose : This procedure will call network validation API
1121 * to check the routing network created with processes
1122 * or line-operations for existing loops. Check the
1123 * routing network to see if there exist any broken
1124 *links in the network that was created
1125 *********************************************************************/
1126 PROCEDURE Check_Entity2
1127 ( p_op_network_rec IN Bom_Rtg_Pub.Op_Network_Rec_Type
1128 , p_op_network_unexp_rec IN Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type
1129 , p_old_op_network_rec IN Bom_Rtg_Pub.Op_Network_Rec_Type
1130 , p_old_op_network_unexp_rec IN Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type
1131 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1132 , x_return_status IN OUT NOCOPY VARCHAR2
1133 )
1134 IS
1135 x_status VARCHAR2(1);
1136 x_message VARCHAR2(255);
1137
1138 l_total_planning_pct NUMBER :=0;
1139 l_err_text VARCHAR2(2000) ;
1140 l_token_tbl Error_Handler.Token_Tbl_Type;
1141 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1142 l_dummy NUMBER;
1143
1144 BEGIN
1145
1146
1147 x_return_status := FND_API.G_RET_STS_SUCCESS;
1148
1149 IF BOM_Rtg_Globals.Get_Debug = 'Y'
1150 THEN Error_Handler.Write_Debug
1151 ('Within operation network entity level check2 . . . ');
1152 END IF;
1153
1154 -- Set Token Value
1155 l_token_tbl(1).token_name := 'FROM_OP_SEQ_NUMBER';
1156 l_token_tbl(1).token_value :=
1157 NVL( p_op_network_rec.new_from_op_seq_number
1158 , p_op_network_rec.from_op_seq_number ) ;
1159 l_token_tbl(2).token_name := 'TO_OP_SEQ_NUMBER';
1160 l_token_tbl(2).token_value :=
1161 NVL( p_op_network_rec.new_to_op_seq_number
1162 , p_op_network_rec.to_op_seq_number ) ;
1163
1164 -- Operation Network regarding eAM enhancement
1165 -- The routing networks can be defined for flow manufacturing routings
1166 -- and lot based (defined in WSM) routings.
1167 -- Currently, the network defined for lot based routings cannot have
1168 -- multiple start nodes. It should be a single network with only one start node.
1169 -- But the maintenance routing defined for an asset activity can have
1170 -- multiple small network of operations
1171 -- ( i.e the routings can have more than one start node).
1172 -- Both lot based and EAM routings does not allow looping within the network.
1173 -- In both the cases all the operations in a routing may not be totally connected.
1174 -- There can be some operations not included in the network.
1175
1176
1177 --
1178 -- Check planning percentage.
1179 -- For update, total of Planning Percent for Operation
1180 -- Network must be smaller than 100%.
1181 -- Added to support updating from op and to op id
1182
1183 IF p_op_network_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
1184 AND p_op_network_rec.connection_type IN (1, 2)
1185 AND BOM_Rtg_Globals.Get_Eam_Item_Type <> BOM_Rtg_Globals.G_ASSET_ACTIVITY
1186 THEN
1187
1188 SELECT NVL(SUM(planning_pct), 0)
1189 INTO l_total_planning_pct
1190 FROM bom_operation_networks
1191 WHERE from_op_seq_id = NVL( p_op_network_unexp_rec.new_from_op_seq_id
1192 , p_op_network_unexp_rec.from_op_seq_id)
1193 AND to_op_seq_id <> NVL( p_op_network_unexp_rec.new_to_op_seq_id
1194 , p_op_network_unexp_rec.to_op_seq_id)
1195 AND transition_type IN (1, 2);
1196
1197 --l_total_planning_pct := l_total_planning_pct;
1198 -- + p_op_network_rec.planning_percent ;
1199
1200
1201 IF BOM_Rtg_Globals.Get_Debug = 'Y'
1202 THEN Error_Handler.Write_Debug
1203 ('Check planning percentage '|| to_char(l_total_planning_pct) );
1204 END IF;
1205
1206 IF (l_total_planning_pct > 100)
1207 THEN
1208
1209 l_token_tbl.DELETE(2) ;
1210
1211 IF p_op_network_rec.operation_type IN (1,2)
1212 THEN
1213 Error_Handler.Add_Error_Token
1214 ( p_message_name => 'BOM_OP_NWK_PRCS_PLNPCT_INVALID'
1215 , p_token_tbl => l_token_tbl
1216 , p_mesg_token_tbl => l_mesg_token_tbl
1217 , x_mesg_token_tbl => l_mesg_token_tbl
1218 );
1219 x_return_status := FND_API.G_RET_STS_ERROR;
1220 ELSIF p_op_network_rec.operation_type = 3
1221 THEN
1222 Error_Handler.Add_Error_Token
1223 ( p_message_name => 'BOM_OP_NWK_LO_PLNLPCT_INVALID'
1224 , p_token_tbl => l_token_tbl
1225 , p_mesg_token_tbl=> l_mesg_token_tbl
1226 , x_mesg_token_tbl=> l_mesg_token_tbl
1227 );
1228 x_return_status := FND_API.G_RET_STS_ERROR;
1229 END IF;
1230
1231 l_token_tbl(2).token_name := 'TO_OP_SEQ_NUMBER';
1232 l_token_tbl(2).token_value :=
1233 NVL( p_op_network_rec.new_to_op_seq_number
1234 , p_op_network_rec.to_op_seq_number ) ;
1235
1236 END IF;
1237 END IF;
1238
1239 /* Following code needs to be called when the whole network has been created and
1240 not at the creation/modification of each link. Although the code checks a link
1241 attributes , it should run after the entire netowrk has been created/modified
1242 -- Added condition for eAM enhancement
1243 IF p_op_network_rec.connection_type <> 3 -- Not Rework
1244 AND BOM_Rtg_Globals.Get_Eam_Item_Type <> BOM_Rtg_Globals.G_ASSET_ACTIVITY
1245 THEN
1246
1247 bom_rtg_network_validate_api.validate_routing_network
1248 ( p_rtg_sequence_id => p_op_network_unexp_rec.routing_sequence_id
1249 , p_assy_item_id => p_op_network_unexp_rec.assembly_item_id
1250 , p_org_id => p_op_network_unexp_rec.organization_id
1251 , p_alt_rtg_desig => p_op_network_rec.alternate_routing_code
1252 , p_operation_type => p_op_network_rec.operation_type
1253 , x_status => x_status
1254 , x_message => x_message
1255 ) ;
1256
1257 IF BOM_Rtg_Globals.Get_Debug = 'Y'
1258 THEN Error_Handler.Write_Debug
1259 ('After calling Rtg Network Validate API. Retrun status is '|| x_status );
1260 END IF;
1261
1262 END IF ;
1263
1264
1265
1266 IF x_status = 'F' AND x_message IS NOT NULL
1267 THEN
1268
1269 IF UPPER( RTRIM(x_message) ) =
1270 UPPER('A loop has been detected in this Routing Network.')
1271 THEN
1272 Error_Handler.Add_Error_Token
1273 ( p_message_name => 'BOM_OP_NWK_LOOP_EXIT'
1274 , p_token_tbl => l_token_tbl
1275 , p_mesg_token_tbl => l_mesg_token_tbl
1276 , x_mesg_token_tbl => l_mesg_token_tbl
1277 );
1278 x_return_status := FND_API.G_RET_STS_ERROR;
1279 ELSIF UPPER( RTRIM(x_message) ) =
1280 UPPER('A broken link exists in this routing Network.')
1281 THEN
1282
1283 Error_Handler.Add_Error_Token
1284 ( p_message_name => 'BOM_OP_NWK_BROEKN_LINK_EXIT'
1285 , p_token_tbl => l_token_tbl
1286 , p_mesg_token_tbl => l_mesg_token_tbl
1287 , x_mesg_token_tbl => l_mesg_token_tbl
1288 );
1289
1290 x_return_status := FND_API.G_RET_STS_ERROR;
1291 ELSE
1292
1293 Error_Handler.Add_Error_Token
1294 ( p_message_name => 'BOM_OP_NWK_VLDN_ERROR'
1295 , p_token_tbl => l_token_tbl
1296 , p_mesg_token_tbl => l_mesg_token_tbl
1297 , x_mesg_token_tbl => l_mesg_token_tbl
1298 );
1299
1300 x_return_status := FND_API.G_RET_STS_ERROR;
1301 END IF;
1302 END IF;
1303 */
1304 -- For eAM enhancement
1305 -- Maintenance Routing Network Validation
1306 IF p_op_network_rec.operation_type = 1
1307 AND BOM_Rtg_Globals.Get_Eam_Item_Type = BOM_Rtg_Globals.G_ASSET_ACTIVITY
1308 THEN
1309
1310 Check_Eam_Rtg_Network
1311 ( p_op_network_rec => p_op_network_rec
1312 , p_op_network_unexp_rec => p_op_network_unexp_rec
1313 , p_old_op_network_rec => p_old_op_network_rec
1314 , p_old_op_network_unexp_rec => p_old_op_network_unexp_rec
1315 , p_mesg_token_tbl => l_mesg_token_tbl
1316 , x_mesg_token_tbl => l_mesg_token_tbl
1317 , x_return_status => x_status
1318 ) ;
1319
1320 IF x_status = FND_API.G_RET_STS_ERROR THEN
1321 x_return_status := FND_API.G_RET_STS_ERROR;
1322 END IF ;
1323
1324
1325 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
1326 Error_Handler.Write_Debug
1327 ('Validation for eAM Op Network is completed with status '|| x_return_status);
1328 END IF;
1329
1330
1331 END IF ; -- eAM Operation Network Validation
1332
1333
1334 x_mesg_token_tbl := l_mesg_token_tbl;
1335
1336 EXCEPTION
1337 WHEN OTHERS THEN
1338 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1339 ('Some unknown error in Entity Validation2. . .' || SQLERRM );
1340 END IF ;
1341
1342
1343 l_err_text := G_PKG_NAME || ' Validation (Entity Validation2) '
1344 || substrb(SQLERRM,1,200);
1345
1346 Error_Handler.Add_Error_Token
1347 ( p_message_name => NULL
1348 , p_message_text => l_err_text
1349 , p_mesg_token_tbl => l_mesg_token_tbl
1350 , x_mesg_token_tbl => l_mesg_token_tbl
1351 ) ;
1352
1353 -- Return the status and message table.
1354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1355 x_mesg_token_tbl := l_mesg_token_tbl ;
1356
1357
1358 END Check_Entity2;
1359 /*********************************************************************
1360 * Procedure : Check_WSM_Netowrk_Attribs
1361 * Parameters IN : Assembly item id
1362 * Organization Id
1363 * Alternate_Rtg_Code
1364 * previous start id as found before the whole update
1365 * previous end id as found before the whole update
1366 * Parameters OUT:
1367 * Mesg token Table
1368 * Return Status
1369 * Purpose : Procedure will varify that the routing start and
1370 * end are unchanged
1371 ***********************************************************************/
1372 PROCEDURE Check_WSM_Netowrk_Attribs
1373 ( p_routing_sequence_id IN NUMBER
1374 , p_prev_start_id IN NUMBER
1375 , p_prev_end_id IN NUMBER
1376 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1377 , x_Return_status IN OUT NOCOPY VARCHAR2
1378 )
1379 IS
1380 CURSOR cur_op_seq_num( p_routing_id NUMBER,
1381 p_operation_seq_id NUMBER) IS
1382 select operation_seq_num from bom_operation_sequences
1383 WHERE routing_sequence_id = p_routing_id
1384 AND operation_sequence_id = p_operation_seq_id;
1385
1386 l_routing_sequence_id NUMBER :=0;
1387 l_common_routing_sequence_id NUMBER :=0;
1388 l_cfm_routing_flag NUMBER :=0;
1389 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type ;
1390 l_post_start_id NUMBER :=0;
1391 l_post_end_id NUMBER :=0;
1392 err_code NUMBER:=0;
1393 err_msg VARCHAR2(2000);
1394 l_token_tbl Error_Handler.Token_Tbl_Type;
1395 x_success NUMBER:=0;
1396 BEGIN
1397 x_return_status := FND_API.G_RET_STS_SUCCESS;
1398
1399 select routing_sequence_id, cfm_routing_flag
1400 into l_routing_sequence_id,l_cfm_routing_flag
1401 from bom_operational_routings where
1402 routing_sequence_id = p_routing_sequence_id;
1403
1404 -- Get Common Routing Seq Id from System Info Rec.
1405 -- If the value is Null, set Common Routing Seq Id.
1406 l_common_routing_sequence_id := BOM_Rtg_Globals.Get_Common_Rtg_Seq_id ;
1407 IF l_common_routing_sequence_id IS NULL OR
1408 l_common_routing_sequence_id = FND_API.G_MISS_NUM THEN
1409 BEGIN
1410 SELECT common_routing_sequence_id
1411 INTO l_common_routing_sequence_id
1412 FROM bom_operational_routings
1413 WHERE routing_sequence_id = l_routing_sequence_id;
1414 END ;
1415 BOM_Rtg_Globals.Set_Common_Rtg_Seq_id
1416 ( p_common_rtg_seq_id => l_common_routing_sequence_id );
1417 END IF;
1418 IF nvl(l_cfm_routing_flag,2) = 3 THEN
1419 --IF ( p_prev_start_id IS NOT NULL AND p_prev_start_id <> 0 ) THEN -- commented for bug3134027
1420 WSMPUTIL.FIND_ROUTING_START(l_common_routing_sequence_id,
1421 l_post_start_id,
1422 err_code,
1423 err_msg );
1424
1425 IF err_code = -1 THEN --for OSFM routings
1426 RETURN;
1427 END IF;
1428
1429 IF err_msg IS NOT NULL THEN
1430 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1431 Error_Handler.Add_Error_Token
1432 ( p_message_name => NULL
1433 , p_message_text => err_msg
1434 , p_mesg_token_tbl => l_mesg_token_tbl
1435 , x_mesg_token_tbl => l_mesg_token_tbl
1436 ) ;
1437
1438 END IF;
1439 x_return_status := FND_API.G_RET_STS_ERROR ;
1440 x_mesg_token_tbl := l_mesg_token_tbl ; --for bug 3134027
1441
1442 RETURN;
1443 END IF;
1444
1445 IF p_prev_start_id is not null and p_prev_start_id <> 0 then
1446 IF (l_post_start_id <> p_prev_start_id) THEN
1447 -- Set Token Value
1448
1449 l_token_tbl(1).token_name := 'START_OP_SEQ_NUM';
1450 FOR rec_op_seq_num in cur_op_seq_num(l_routing_sequence_id,
1451 l_post_start_id) LOOP
1452 l_token_tbl(1).token_value := rec_op_seq_num.operation_seq_num;
1453 END LOOP;
1454 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1455 Error_Handler.Add_Error_Token
1456 ( p_message_name => 'WSM_START_CANNOT_BE_CHANGED'
1457 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1458 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
1459 , p_token_tbl => l_token_tbl
1460 ) ;
1461 END IF ;
1462 x_return_status := FND_API.G_RET_STS_ERROR ;
1463 x_mesg_token_tbl := l_mesg_token_tbl ; --for bug 3134027
1464
1465 RETURN;
1466 END IF;
1467 end if; -- for bug3134027
1468
1469
1470 -- IF ( p_prev_end_id IS NOT NULL AND p_prev_end_id <> 0 ) THEN -- commented for bug3134027
1471 WSMPUTIL.FIND_ROUTING_END(l_common_routing_sequence_id,
1472 l_post_end_id,
1473 err_code,
1474 err_msg );
1475
1476 IF err_code = -1 THEN --for OSFM routings
1477 RETURN;
1478 END IF;
1479
1480 IF err_msg IS NOT NULL THEN
1481
1482 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1483 Error_Handler.Add_Error_Token
1484 ( p_message_name => NULL
1485 , p_message_text => err_msg
1486 , p_mesg_token_tbl => l_mesg_token_tbl
1487 , x_mesg_token_tbl => l_mesg_token_tbl
1488 ) ;
1489
1490 END IF;
1491 x_return_status := FND_API.G_RET_STS_ERROR ;
1492 x_mesg_token_tbl := l_mesg_token_tbl ; --for bug 3134027
1493
1494 RETURN;
1495 END IF;
1496 IF p_prev_end_id is not null and p_prev_end_id <> 0 then
1497 if (l_post_end_id <> p_prev_end_id) THEN
1498 -- Set Token Value
1499 l_token_tbl(1).token_name := 'END_OP_SEQ_NUM';
1500 FOR rec_op_seq_num in cur_op_seq_num(l_routing_sequence_id,
1501 l_post_end_id) LOOP
1502 l_token_tbl(1).token_value := rec_op_seq_num.operation_seq_num;
1503 END LOOP;
1504
1505 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1506 Error_Handler.Add_Error_Token
1507 ( p_message_name => 'WSM_END_CANNOT_BE_CHANGED'
1508 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1509 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
1510 , p_token_tbl => l_token_tbl
1511 ) ;
1512 END IF ;
1513 x_return_status := FND_API.G_RET_STS_ERROR ;
1514 x_mesg_token_tbl := l_mesg_token_tbl ; --for bug 3134027
1515
1516 RETURN;
1517 END IF;
1518
1519 END IF;-- If prev end has some value -- commented for bug3134027
1520
1521 err_msg := NULL;
1522 err_code := 0;
1523 x_success := WSMPUTIL.PRIMARY_LOOP_TEST
1524 (l_common_routing_sequence_id,
1525 l_post_start_id,
1526 l_post_end_id,
1527 err_code,
1528 err_msg );
1529
1530 IF err_msg IS NOT NULL THEN
1531 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1532 THEN
1533
1534 Error_Handler.Add_Error_Token
1535 ( p_message_name => NULL
1536 , p_message_text => err_msg
1537 , p_mesg_token_tbl => l_mesg_token_tbl
1538 , x_mesg_token_tbl => l_mesg_token_tbl
1539 ) ;
1540 END IF;
1541 x_return_status := FND_API.G_RET_STS_ERROR ;
1542 x_mesg_token_tbl := l_mesg_token_tbl ; --for bug 3134027
1543
1544 RETURN ;
1545
1546 END IF;
1547
1548 err_msg := NULL;
1549 err_code := 0;
1550 x_success:= WSMPUTIL.CHECK_100_PERCENT (
1551 l_common_routing_sequence_id,
1552 err_code,
1553 err_msg );
1554
1555 IF err_msg IS NOT NULL THEN
1556 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1557 THEN
1558 Error_Handler.Add_Error_Token
1559 ( p_message_name => NULL
1560 , p_message_text => err_msg
1561 , p_mesg_token_tbl => l_mesg_token_tbl
1562 , x_mesg_token_tbl => l_mesg_token_tbl
1563 ) ;
1564 END IF;
1565 x_return_status := FND_API.G_RET_STS_ERROR ;
1566 x_mesg_token_tbl := l_mesg_token_tbl ; --for bug 3134027
1567
1568 RETURN ;
1569 END IF;
1570 END IF;-- ONLY if CFM_ROUTING_FLAG=3(OSFM ROUTING)
1571
1572
1573 -- Return messages
1574 x_mesg_token_tbl := l_mesg_token_tbl ;
1575
1576
1577 END Check_WSM_Netowrk_Attribs;
1578
1579
1580 END BOM_Validate_Op_Network;