[Home] [Help]
PACKAGE BODY: APPS.BOM_VALIDATE_RTG_HEADER
Source
1 PACKAGE BODY BOM_Validate_Rtg_Header AS
2 /* $Header: BOMLRTGB.pls 120.1.12000000.3 2007/04/24 07:43:00 kjonnala ship $*/
3 /****************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMLRTGB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_Validate_Rtg_Header
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 07-AUG-00 Biao Zhang Initial Creation
21 --
22 ****************************************************************************/
23 G_PKG_NAME VARCHAR2(30) := 'BOM_Validate_Rtg_Header';
24 g_token_tbl Error_Handler.Token_Tbl_Type;
25
26 l_sub_locator_control NUMBER;
27 l_locator_control NUMBER;
28 l_org_locator_control NUMBER;
29 l_item_locator_control NUMBER;
30 l_item_loc_restricted NUMBER; -- 1,Locator is Restricted,else 2
31
32
33 /*******************************************************************
34 * Procedure : Check_Existence
35 * Returns : None
36 * Parameters IN : Rtg Header Exposed Record
37 * Rtg Header Unexposed Record
38 * Parameters out: Old Rtg Header exposed Record
39 * Old Rtg Header Unexposed Record
40 * Mesg Token Table
41 * Return Status
42 * Purpose : Procedure will query the routing header
43 * record and return it in old record variables. If the
44 * Transaction Type is Create and the record already
45 * exists the return status would be error or if the
46 * transaction type is Update or Delete and the record
47 * does not exist then the return status would be an
48 * error as well. Mesg_Token_Table will carry the
49 * error messsage and the tokens associated with the
50 * message.
51 *********************************************************************/
52 PROCEDURE Check_Existence
53 ( p_rtg_header_rec IN Bom_Rtg_Pub.rtg_header_Rec_Type
54 , p_rtg_header_unexp_rec IN Bom_Rtg_Pub.rtg_header_Unexposed_Rec_Type
55 , x_old_rtg_header_rec IN OUT NOCOPY Bom_Rtg_Pub.rtg_header_Rec_Type
56 , x_old_rtg_header_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.rtg_header_Unexposed_Rec_Type
57 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
58 , x_return_status IN OUT NOCOPY VARCHAR2
59 )
60 IS
61 l_token_tbl Error_Handler.Token_Tbl_Type;
62 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
63 l_return_status VARCHAR2(1);
64 l_err_text VARCHAR2(2000);
65
66 BEGIN
67
68 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
69 Error_Handler.Write_Debug('Quering Routing . . . ') ;
70 Error_Handler.Write_Debug('Assembly item : ' || to_char(p_rtg_header_unexp_rec.assembly_item_id));
71 Error_Handler.Write_Debug('Org Id : ' || to_char(p_rtg_header_unexp_rec.organization_id ));
72 Error_Handler.Write_Debug('Alternate : ' || p_rtg_header_rec.alternate_routing_code );
73 END IF;
74
75
76 Bom_Rtg_Header_Util.Query_Row
77 ( p_assembly_item_id =>
78 p_rtg_header_unexp_rec.assembly_item_id
79 , p_alternate_routing_code =>
80 p_rtg_header_rec.alternate_routing_code
81 , p_organization_id =>
82 p_rtg_header_unexp_rec.organization_id
83 , x_rtg_header_rec => x_old_rtg_header_rec
84 , x_rtg_header_unexp_rec => x_old_rtg_header_unexp_rec
85 , x_return_status => l_return_status
86 );
87
88 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
89 Error_Handler.Write_Debug('Query Row Returned with : ' || l_return_status);
90 END IF;
91
92 IF l_return_status = BOM_Rtg_Globals.G_RECORD_FOUND AND
93 p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
94 THEN
95 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
96 l_token_tbl(1).token_value :=
97 p_rtg_header_rec.assembly_item_name;
98 Error_Handler.Add_Error_Token
99 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
100 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
101 , p_message_name => 'BOM_RTG_ALREADY_EXISTS'
102 , p_token_tbl => l_token_tbl
103 );
104 l_return_status := FND_API.G_RET_STS_ERROR;
105
106 ELSIF l_return_status = BOM_Rtg_Globals.G_RECORD_NOT_FOUND AND
107 p_rtg_header_rec.transaction_type IN
108 (BOM_Rtg_Globals.G_OPR_UPDATE, BOM_Rtg_Globals.G_OPR_DELETE)
109 THEN
110 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
111 l_token_tbl(1).token_value :=
112 p_rtg_header_rec.assembly_item_name;
113 Error_Handler.Add_Error_Token
114 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
115 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
116 , p_message_name => 'BOM_RTG_DOESNOT_EXISTS'
117 , p_token_tbl => l_token_tbl
118 );
119 l_return_status := FND_API.G_RET_STS_ERROR;
120 ELSIF l_Return_status = FND_API.G_RET_STS_UNEXP_ERROR
121 THEN
122 Error_Handler.Add_Error_Token
123 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
124 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
125 , p_message_name => NULL
126 , p_message_text =>
127 'Unexpected error while existence verification of ' ||
128 'Assembly item '||
129 p_rtg_header_rec.assembly_item_name
130 , p_token_tbl => l_token_tbl
131 );
132 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
133
134 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS
135 THEN
136 l_return_status := FND_API.G_RET_STS_SUCCESS;
137 END IF;
138
139 x_return_status := l_return_status;
140 x_mesg_token_tbl := l_mesg_token_tbl;
141
142
143 EXCEPTION
144 WHEN OTHERS THEN
145
146 l_err_text := G_PKG_NAME || ' Validation (Check Existnece) '
147 || substrb(SQLERRM,1,200);
148
149 Error_Handler.Add_Error_Token
150 ( p_message_name => NULL
151 , p_message_text => l_err_text
152 , p_mesg_token_tbl => l_mesg_token_tbl
153 , x_mesg_token_tbl => l_mesg_token_tbl
154 ) ;
155
156 -- Return the status and message table.
157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
158 x_mesg_token_tbl := l_mesg_token_tbl ;
159
160
161 END Check_Existence;
162
163
164 /*******************************************************************
165 * Procedure : Check_Access
166 * Returns : None
167 * Parameters IN : Assembly_Item_Id
168 * Organization_Id
169 * Alternate_rtg_Designator
170 * Parameters out: Return Status
171 * Message Token Table
172 * Purpose : This procedure will check if the user has access
173 * to the Assembly Item's BOM Item Type.
174 * If not then an appropriate message and a error status
175 * will be returned back.
176 *********************************************************************/
177 PROCEDURE Check_Access
178 ( p_assembly_item_name IN VARCHAR2
179 , p_assembly_item_id IN NUMBER
180 , p_alternate_rtg_code IN VARCHAR2
181 , p_organization_id IN NUMBER
182 , p_mesg_token_tbl IN Error_Handler.Mesg_Token_Tbl_Type
183 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
184 , x_return_status IN OUT NOCOPY VARCHAR2
185 )
186 IS
187 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
188 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type :=
189 p_mesg_token_tbl;
190 l_err_text VARCHAR2(2000);
191
192 l_bom_item_type NUMBER;
193 l_assembly_type NUMBER;
194 l_eam_item_type NUMBER;
195 l_token_tbl Error_Handler.Token_Tbl_Type;
196
197 BEGIN
198
199 --
200 -- If user is trying to update an Engineering Item from RTG
201 -- Business Object, the user should not be allowed.
202 --
203
204 -- Added eam_item_type for eAM enhancement
205 -- by MK 04/20/2001
206 SELECT bom_item_type
207 , decode(eng_item_flag, 'N', 1, 2)
208 , NVL(eam_item_type, 0 )
209 INTO l_bom_item_type
210 , l_assembly_type
211 , l_eam_item_type
212 FROM mtl_system_items
213 WHERE inventory_item_id = p_assembly_item_id
214 AND organization_id = p_organization_id;
215
216 /* Commented for bug 3277905
217 IF l_assembly_type = 2 Engineering Item
218 THEN
219 Error_Handler.Add_Error_Token
220 ( p_Message_name => 'BOM_ASSEMBLY_TYPE_ENG'
221 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
222 , x_mesg_token_tbl => l_mesg_token_tbl
223 );
224 l_return_status := FND_API.G_RET_STS_ERROR;
225 END IF; */
226
227 IF BOM_Rtg_Globals.Get_STD_Item_Access IS NULL AND
228 BOM_Rtg_Globals.Get_PLN_Item_Access IS NULL AND
229 BOM_Rtg_Globals.Get_MDL_Item_Access IS NULL AND
230 BOM_Rtg_Globals.Get_OC_Item_Access IS NULL
231 THEN
232 --
233 -- Get respective profile values
234 --
235 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
236 Error_Handler.Write_Debug('Checking item type access . . . ');
237 END IF;
238
239 IF fnd_profile.value('BOM:STANDARD_ITEM_ACCESS') = '1'
240 THEN
241 BOM_Rtg_Globals.Set_STD_Item_Access
242 ( p_std_item_access => 4);
243 ELSE
244
245 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
246 Error_Handler.Write_Debug('No access to Std Items');
247 END IF;
248 BOM_Rtg_Globals.Set_STD_Item_Access
249 (p_std_item_access => NULL);
250 END IF;
251
252 IF fnd_profile.value('BOM:MODEL_ITEM_ACCESS') = '1'
253 THEN
254 BOM_Rtg_Globals.Set_MDL_Item_Access
255 ( p_mdl_item_access => 1);
256 BOM_Rtg_Globals.Set_OC_Item_Access
257 ( p_oc_item_access => 2);
258
259 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
260 Error_Handler.Write_Debug('Model/OC items are accessible');
261 END IF;
262
263 ELSE
264 BOM_Rtg_Globals.Set_MDL_Item_Access
265 ( p_mdl_item_access => NULL);
266 BOM_Rtg_Globals.Set_OC_Item_Access
267 ( p_oc_item_access => NULL);
268
269 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
270 Error_Handler.Write_Debug('No access to Model/OC items ');
271 END IF;
272 END IF;
273
274 IF fnd_profile.value('BOM:PLANNING_ITEM_ACCESS') = '1'
275 THEN
276 BOM_Rtg_Globals.Set_PLN_Item_Access
277 ( p_pln_item_access => 3);
278
279 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
280 Error_Handler.Write_Debug('Planning item accessible');
281 END IF;
282 ELSE
283 BOM_Rtg_Globals.Set_PLN_Item_Access
284 ( p_pln_item_access => NULL);
285
286 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
287 Error_Handler.Write_Debug('No access to Planning items ');
288 END IF;
289
290 END IF;
291 END IF;
292
293 --
294 -- Use BOM Item Type of the Assembly Item that is queried above
295 -- to check if user has access to it.
296 --
297 IF l_Bom_Item_Type NOT IN
298 ( NVL(BOM_Rtg_Globals.Get_STD_Item_Access, 0),
299 NVL(BOM_Rtg_Globals.Get_PLN_Item_Access, 0),
300 NVL(BOM_Rtg_Globals.Get_OC_Item_Access, 0) ,
301 NVL(BOM_Rtg_Globals.Get_MDL_Item_Access, 0)
302 )
303 AND l_Bom_Item_Type <> 5
304 THEN
305 l_Token_Tbl(1).Token_Name := 'BOM_ITEM_TYPE';
306 l_Token_Tbl(1).Translate := TRUE;
307 IF l_Bom_Item_Type = 1
308 THEN
309 l_Token_Tbl(1).Token_Value := 'BOM_MODEL';
310 ELSIF l_Bom_Item_Type = 2
311 THEN
312 l_Token_Tbl(1).Token_Value:='BOM_OPTION_CLASS';
313 ELSIF l_Bom_Item_Type = 3
314 THEN
315 l_Token_Tbl(1).Token_Value := 'BOM_PLANNING';
316 ELSIF l_Bom_Item_Type = 4
317 THEN
318 l_Token_Tbl(1).Token_Value := 'BOM_STANDARD';
319 END IF;
320
321 Error_Handler.Add_Error_Token
322 ( p_Message_Name => 'BOM_RTG_AITEM_ACCESS_DENIED'
323 , p_Mesg_Token_Tbl => l_mesg_token_tbl
324 , x_Mesg_Token_Tbl => l_mesg_token_tbl
325 , p_Token_Tbl => l_token_tbl
326 );
327 l_return_status := FND_API.G_RET_STS_ERROR ;
328
329 ELSIF l_Bom_Item_Type = 3
330 THEN
331 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
332 l_token_tbl(1).token_value := p_assembly_item_name ;
333
334
335 Error_Handler.Add_Error_Token
336 ( p_Message_name => 'BOM_RTG_AITEM_PLANNING_ITEM'
337 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
338 , x_mesg_token_tbl => l_mesg_token_tbl
339 , p_Token_Tbl => l_token_tbl
340 );
341 l_return_status := FND_API.G_RET_STS_ERROR;
342
343
344 END IF;
345
346 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
347 Error_Handler.Write_Debug('Check if this item is Asset Item for Enterprise Asset Management. . .');
348 END IF;
349 -- for eAM enhancement,
350 -- If eam item type is 1 : Asset Group or 3 : Rebuildable Component
351 -- User cannnot create any type of routings
352
353 /* IF l_eam_item_type IN ( BOM_Rtg_Globals.G_ASSET_GROUP ,
354 BOM_Rtg_Globals.G_REBUILDABLE )
355 THEN
356 */
357 /* Fix for bug 5903026 - Allow routings to be created for items with eam_item_type=3 (i.e.)Rebuildable
358 Commented the earlier IF condition. Now we disallow only for G_ASSET_GROUP
359 */
360 IF l_eam_item_type = BOM_Rtg_Globals.G_ASSET_GROUP
361 THEN
362 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
363 l_token_tbl(1).token_value := p_assembly_item_name ;
364
365
366 Error_Handler.Add_Error_Token
367 ( p_Message_name => 'BOM_EAM_ITEM_TYPE_INVALID'
368 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
369 , x_mesg_token_tbl => l_mesg_token_tbl
370 , p_Token_Tbl => l_token_tbl
371 );
372 l_return_status := FND_API.G_RET_STS_ERROR;
373
374 END IF ;
375
376 -- Set Eam Item Type to System Info Record.
377 BOM_Rtg_Globals.Set_Eam_Item_Type(p_eam_item_type => l_eam_item_type) ;
378
379 x_return_status := l_return_status;
380 x_mesg_token_tbl := l_mesg_token_tbl;
381
382
383 EXCEPTION
384 WHEN OTHERS THEN
385
386 l_err_text := G_PKG_NAME || ' Validation (Check Access) '
387 || substrb(SQLERRM,1,200);
388
389 Error_Handler.Add_Error_Token
390 ( p_message_name => NULL
391 , p_message_text => l_err_text
392 , p_mesg_token_tbl => l_mesg_token_tbl
393 , x_mesg_token_tbl => l_mesg_token_tbl
394 ) ;
395
396 -- Return the status and message table.
397 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
398 x_mesg_token_tbl := l_mesg_token_tbl ;
399
400
401 END Check_Access;
402
403 /*******************************************************************
404 * Procedure : Check_Flow_Routing_Operability
405 * Returns : None
406 * Parameters IN : cfm_routing_flag
407 * Parameters out: Return Status
408 * Message Token Table
409 * Purpose : This procedure will check
410 * If not then an appropriate message and a error status
411 * will be returned back.
412 *********************************************************************/
413 PROCEDURE Check_Flow_Routing_Operability
414 ( p_assembly_item_name IN VARCHAR2
415 , p_cfm_routing_flag IN NUMBER
416 , p_organization_code IN VARCHAR2
417 , p_organization_id IN NUMBER
418 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
419 , x_return_status IN OUT NOCOPY VARCHAR2
420 )
421 IS
422
423 l_errorNumber NUMBER;
424 l_OrgIsWsmEnabled NUMBER;
425 l_err_text VARCHAR2(200) := NULL;
426 l_errorMessage VARCHAR2(200) := NULL;
427 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
428 l_Token_Tbl Error_Handler.Token_Tbl_Type;
429 x_install_cfm BOOLEAN;
430 x_status VARCHAR2(50);
431 x_industry VARCHAR2(50);
432 x_schema VARCHAR2(50);
433
434 BEGIN
435
436 x_return_status := FND_API.G_RET_STS_SUCCESS;
437
438 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug(
439 'Within Routing Header Check flow routing operability . . . ');
440 END IF;
441
442 IF NVL(p_cfm_routing_flag ,2) NOT IN ( 1, 2 ,3)
443 AND p_cfm_routing_flag <> FND_API.G_MISS_NUM
444 THEN
445 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
446 l_token_tbl(1).token_value :=
447 p_assembly_item_name;
448 l_token_tbl(2).token_name := 'CFM_ROUTING_FLAG';
449 l_token_tbl(2).token_value :=
450 NVL(p_cfm_routing_flag ,2) ;
451 Error_Handler.Add_Error_Token
452 ( p_message_name => 'BOM_RTG_CFM_FLAG_INVALID'
453 , p_token_tbl => l_token_tbl
454 , p_mesg_token_tbl => l_mesg_token_tbl
455 , x_mesg_token_tbl => l_mesg_token_tbl
456 );
457
458 x_return_status := FND_API.G_RET_STS_ERROR;
459
460
461 ELSIF NVL(p_cfm_routing_flag , 2) = 1
462 THEN
463 x_install_cfm :=
464 Fnd_Installation.Get_App_Info
465 (application_short_name => 'FLM',
466 status => x_status,
467 industry => x_industry,
468 oracle_schema => x_schema);
469
470 IF (x_status <> 'I' or x_status is NULL)
471 THEN
472 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
473 l_token_tbl(1).token_value :=
474 p_assembly_item_name;
475 Error_Handler.Add_Error_Token
476 ( p_message_name => 'BOM_RTG_FLOW_RTG_INVALID'
477 , p_token_tbl => l_token_tbl
478 , p_mesg_token_tbl => l_mesg_token_tbl
479 , x_mesg_token_tbl => l_mesg_token_tbl
480 );
481
482 x_return_status := FND_API.G_RET_STS_ERROR;
483
484 END IF;
485
486 ELSIF NVL(p_cfm_routing_flag , 2) = 3
487 THEN
488 --- WSM (OSFM) Enhancement
489 --- For Lot Based Routings, need to check if the Org is WSM Enabled.
490
491 l_OrgIsWsmEnabled := WSMPUTIL.CHECK_WSM_ORG(
492 p_organization_id => p_organization_id,
493 x_err_code => l_errorNumber,
494 x_err_msg => l_errorMessage);
495
496 IF (l_OrgIsWsmEnabled = 0)
497 THEN l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
498 l_token_tbl(1).token_value :=
499 p_assembly_item_name;
500 l_token_tbl(2).token_name := 'ORGANIZATION_CODE';
501 l_token_tbl(2).token_value := p_organization_code ;
502
503 Error_Handler.Add_Error_Token
504 ( p_message_name => 'BOM_RTG_WSM_ORG_INVALID'
505 , p_token_tbl => l_token_tbl
506 , p_mesg_token_tbl => l_mesg_token_tbl
507 , x_mesg_token_tbl => l_mesg_token_tbl
508 );
509
510 x_return_status := FND_API.G_RET_STS_ERROR;
511
512 END IF;
513
514 END IF;
515
516 -- for eAM enhancement,
517 -- If eam item type is 1 : Asset Group and 2 : Asset Activity
518 -- Cfm Routing Flag should be 2 : Standard
519 -- then Check if org is eam enabled and eam has been installed.
520 -- (this validation might not be necessary because
521 -- user cannot enter eam item type if eAM is not available. )
522 -- If eam item type is null,
523 -- Cfm Routing Flag is 1,2 or 3
524
525 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
526 Error_Handler.Write_Debug('Check maintenance routings operability for eAM . . .');
527 END IF;
528 IF BOM_Rtg_Globals.Get_Eam_Item_Type = BOM_Rtg_Globals.G_ASSET_ACTIVITY
529 THEN
530 -- Check cfm routig flag.
531 -- the value should be Null or 2
532 IF NVL(p_cfm_routing_flag , 2) <> 2
533 THEN
534
535 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
536 l_token_tbl(1).token_value :=
537 p_assembly_item_name;
538 l_token_tbl(2).token_name := 'CFM_ROUTING_FLAG';
539 l_token_tbl(2).token_value :=
540 NVL(p_cfm_routing_flag ,2) ;
541 Error_Handler.Add_Error_Token
542 ( p_message_name => 'BOM_EAM_CFM_FLAG_INVALID'
543 , p_token_tbl => l_token_tbl
544 , p_mesg_token_tbl => l_mesg_token_tbl
545 , x_mesg_token_tbl => l_mesg_token_tbl
546 );
547
548 x_return_status := FND_API.G_RET_STS_ERROR;
549
550 ELSE -- CFM Routing Flag is OK
551
552 -- Check if eAM has been installed
553 --
554 IF Bom_Eamutil.Enabled <> 'Y'
555 THEN
556
557 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
558 l_token_tbl(1).token_value :=
559 p_assembly_item_name;
560 Error_Handler.Add_Error_Token
561 ( p_message_name => 'BOM_EAM_INVALID'
562 , p_token_tbl => l_token_tbl
563 , p_mesg_token_tbl => l_mesg_token_tbl
564 , x_mesg_token_tbl => l_mesg_token_tbl
565 );
566
567 x_return_status := FND_API.G_RET_STS_ERROR;
568
569 END IF ;
570
571 --- For Lot Based Routings, need to check if the Org is EAM Enabled.
572 IF BOM_EAMUTIL.OrgIsEamEnabled(p_org_id => p_organization_id)
573 <> 'Y'
574 THEN
575
576 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
577 l_token_tbl(1).token_value :=
578 p_assembly_item_name;
579 l_token_tbl(2).token_name := 'ORGANIZATION_CODE';
580 l_token_tbl(2).token_value := p_organization_code ;
581
582 Error_Handler.Add_Error_Token
583 ( p_message_name => 'BOM_EAM_ORG_INVALID'
584 , p_token_tbl => l_token_tbl
585 , p_mesg_token_tbl => l_mesg_token_tbl
586 , x_mesg_token_tbl => l_mesg_token_tbl
587 );
588
589 x_return_status := FND_API.G_RET_STS_ERROR;
590 END IF ;
591
592 END IF ;
593
594 END IF ;
595
596 -- Set Cfm Routing Flag to System Info Record.
597 BOM_Rtg_Globals.Set_CFM_Rtg_Flag(p_cfm_rtg_type =>
598 NVL(p_cfm_routing_flag, 2) ) ;
599
600 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
601
602 EXCEPTION
603 WHEN OTHERS THEN
604
605 l_err_text := G_PKG_NAME || ' Validation (Check Flow Routing Operability) '
606 || substrb(SQLERRM,1,200);
607
608 Error_Handler.Add_Error_Token
609 ( p_message_name => NULL
610 , p_message_text => l_err_text
611 , p_mesg_token_tbl => l_mesg_token_tbl
612 , x_mesg_token_tbl => l_mesg_token_tbl
613 ) ;
614
615 -- Return the status and message table.
616 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
617 x_mesg_token_tbl := l_mesg_token_tbl ;
618
619 END Check_Flow_Routing_Operability ;
620
621 /********************************************************************
622 * Procedure : Check_Attributes
623 * Parameters IN : Revised Item Exposed Column record
624 * Revised Item Unexposed Column record
625 * Old Revised Item Exposed Column record
626 * Old Revised Item unexposed column record
627 * Parameters out: Return Status
628 * Mesg Token Table
629 * Purpose : Check_Attrbibutes procedure will validate every
630 * revised item attrbiute in its entirety.
631 **********************************************************************/
632 PROCEDURE Check_Attributes
633 ( x_return_status IN OUT NOCOPY VARCHAR2
634 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
635 , p_rtg_header_Rec IN Bom_Rtg_Pub.rtg_header_Rec_Type
636 , p_rtg_header_unexp_rec IN Bom_Rtg_Pub.rtg_header_Unexposed_Rec_Type
637 , p_old_rtg_header_rec IN Bom_Rtg_Pub.rtg_header_Rec_Type
638 , p_old_rtg_header_unexp_rec IN Bom_Rtg_Pub.rtg_header_Unexposed_Rec_Type
639 )
640 IS
641 l_err_text VARCHAR2(2000) := NULL;
642 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
643 l_Token_Tbl Error_Handler.Token_Tbl_Type;
644
645
646 BEGIN
647
648 x_return_status := FND_API.G_RET_STS_SUCCESS;
649
650 IF BOM_Rtg_Globals.Get_Debug = 'Y'THEN
651 Error_Handler.Write_Debug('Within Rtg Header Check Attributes . . . ');
652 END IF;
653
654 IF p_rtg_header_rec.eng_routing_flag IS NOT NULL AND
655 p_rtg_header_rec.eng_routing_flag <> FND_API.G_MISS_NUM AND
656 p_rtg_header_rec.eng_routing_flag NOT IN (1,2)
657 THEN
658 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
659 l_token_tbl(1).token_value :=
660 p_rtg_header_rec.assembly_item_name;
661 Error_Handler.Add_Error_Token
662 ( p_message_name => 'BOM_RTG_ENG_RTG_TYPE_INVALID'
663 , p_token_tbl => l_token_tbl
664 , p_mesg_token_tbl => l_mesg_token_tbl
665 , x_mesg_token_tbl => l_mesg_token_tbl
666 );
667 x_return_status := FND_API.G_RET_STS_ERROR;
668 END IF;
669
670 IF p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
671 THEN
672 IF p_rtg_header_rec.eng_routing_flag = FND_API.G_MISS_NUM
673 THEN
674 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
675 l_token_tbl(1).token_value :=
676 p_rtg_header_rec.assembly_item_name;
677 Error_Handler.Add_Error_Token
678 ( p_message_name => 'BOM_RTG_ENG_RTG_TYPE_MISSING'
679 , p_token_tbl => l_token_tbl
680 , p_mesg_token_tbl => l_mesg_token_tbl
681 , x_mesg_token_tbl => l_mesg_token_tbl
682 );
683 x_return_status := FND_API.G_RET_STS_ERROR;
684 END IF ;
685 END IF;
686
687
688 IF p_rtg_header_rec.mixed_model_map_flag IS NOT NULL AND
689 p_rtg_header_rec.mixed_model_map_flag
690 <> FND_API.G_MISS_NUM AND
691 p_rtg_header_rec.mixed_model_map_flag NOT IN (1,2)
692 THEN
693 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
694 l_token_tbl(1).token_value :=
695 p_rtg_header_rec.assembly_item_name;
696 Error_Handler.Add_Error_Token
697 ( p_message_name =>'BOM_FLM_RTG_MXDMDL_MAP_INVALID'
698 , p_token_tbl => l_token_tbl
699 , p_mesg_token_tbl => l_mesg_token_tbl
700 , x_mesg_token_tbl => l_mesg_token_tbl
701 );
702 x_return_status := FND_API.G_RET_STS_ERROR;
703 END IF;
704
705 IF p_rtg_header_rec.ctp_flag IS NOT NULL AND
706 p_rtg_header_rec.ctp_flag <> FND_API.G_MISS_NUM AND
707 p_rtg_header_rec.ctp_flag NOT IN (1,2)
708 THEN
709 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
710 l_token_tbl(1).token_value :=
711 p_rtg_header_rec.assembly_item_name;
712 Error_Handler.Add_Error_Token
713 ( p_message_name => 'BOM_RTG_CTP_INVALID'
714 , p_token_tbl => l_token_tbl
715 , p_mesg_token_tbl => l_mesg_token_tbl
716 , x_mesg_token_tbl => l_mesg_token_tbl
717 );
718 x_return_status := FND_API.G_RET_STS_ERROR;
719 END IF;
720
721
722
723 IF p_rtg_header_rec.cfm_routing_flag IS NOT NULL AND
724 p_rtg_header_rec.cfm_routing_flag <> FND_API.G_MISS_NUM AND
725 p_old_rtg_header_rec.cfm_routing_flag <> FND_API.G_MISS_NUM AND
726 p_rtg_header_rec.cfm_routing_flag <>
727 p_old_rtg_header_rec.cfm_routing_flag
728 THEN
729
730 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
731 l_token_tbl(1).token_value :=
732 p_rtg_header_rec.assembly_item_name;
733 Error_Handler.Add_Error_Token
734 ( p_message_name => 'BOM_RTG_CFM_NOT_UPDATABLE'
735 , p_token_tbl => l_token_tbl
736 , p_mesg_token_tbl => l_mesg_token_tbl
737 , x_mesg_token_tbl => l_mesg_token_tbl
738 );
739 x_return_status := FND_API.G_RET_STS_ERROR;
740 END IF;
741
742
743 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
744
745 EXCEPTION
746 WHEN OTHERS THEN
747
748 l_err_text := G_PKG_NAME || ' Validation (Check Attributes) '
749 || substrb(SQLERRM,1,200);
750
751 Error_Handler.Add_Error_Token
752 ( p_message_name => NULL
753 , p_message_text => l_err_text
754 , p_mesg_token_tbl => l_mesg_token_tbl
755 , x_mesg_token_tbl => l_mesg_token_tbl
756 ) ;
757
758 -- Return the status and message table.
759 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
760 x_mesg_token_tbl := l_mesg_token_tbl ;
761
762 END Check_Attributes;
763
764 /*********************************************************************
765 * Procedure : Check_Required
766 * Parameters IN : Rtg Header Exposed column record
767 * Parameters out: Mesg Token Table
768 * Return_Status
769 * Purpose :
770 **********************************************************************/
771 PROCEDURE Check_Required
772 ( x_return_status IN OUT NOCOPY VARCHAR2
773 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
774 , p_rtg_header_rec IN Bom_Rtg_Pub.rtg_header_Rec_Type
775 , p_rtg_header_unexp_rec IN Bom_Rtg_Pub.rtg_header_Unexposed_Rec_Type
776 )
777 IS
778 l_err_text VARCHAR2(2000);
779 l_mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
780 l_Token_Tbl Error_Handler.Token_Tbl_Type;
781 BEGIN
782 x_return_status := FND_API.G_RET_STS_SUCCESS;
783
784 IF ( p_rtg_header_unexp_rec.common_routing_sequence_id
785 IS NULL
786 OR
787 p_rtg_header_unexp_rec.common_routing_sequence_id <>
788 FND_API.G_MISS_NUM
789 ) AND
790 ( p_rtg_header_rec.common_assembly_item_name IS NOT NULL
791 AND p_rtg_header_rec.common_assembly_item_name =
792 FND_API.G_MISS_CHAR
793 )
794 THEN
795 --
796 -- If common assembly name is given,
797 -- the common routing sequence is required.
798 --
799 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
800 l_token_tbl(1).token_value :=
801 p_rtg_header_rec.assembly_item_name;
802
803 Error_Handler.Add_Error_Token
804 ( p_message_name => 'BOM_RTG_COMMON_RTG_REQUIRED'
805 , p_token_tbl => l_Token_tbl
806 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
807 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
808 );
809
810 x_return_status := FND_API.G_RET_STS_ERROR;
811 END IF;
812
813 IF ( p_rtg_header_rec.cfm_routing_flag = 1) AND
814 ( p_rtg_header_unexp_rec.line_id IS NULL OR
815 p_rtg_header_unexp_rec.line_id = FND_API.G_MISS_NUM
816 )
817 THEN
818 --
819 -- If the routing is flow routing the line id required.
820 --
821 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
822 l_token_tbl(1).token_value :=
823 p_rtg_header_rec.assembly_item_name;
824
825 Error_Handler.Add_Error_Token
826 ( p_message_name => 'BOM_FLM_RTG_LINE_ID_REQUIRED'
827 , p_token_tbl => l_Token_tbl
828 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
829 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
830 );
831
832 x_return_status := FND_API.G_RET_STS_ERROR;
833 END IF;
834
835 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
836
837
838 EXCEPTION
839 WHEN OTHERS THEN
840
841 l_err_text := G_PKG_NAME || ' Validation (Check Required) '
842 || substrb(SQLERRM,1,200);
843
844 Error_Handler.Add_Error_Token
845 ( p_message_name => NULL
846 , p_message_text => l_err_text
847 , p_mesg_token_tbl => l_mesg_token_tbl
848 , x_mesg_token_tbl => l_mesg_token_tbl
849 ) ;
850
851 -- Return the status and message table.
852 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
853 x_mesg_token_tbl := l_mesg_token_tbl ;
854
855
856 END Check_Required;
857
858 /*****************************************************************
859 * Function : Control (Local function)
860 * Parameter IN : Org Level Control
861 * Subinventory Level Control
862 * Item Level Control
863 * Returns : Number
864 * Purpose : Control procedure will take the various level control
865 * values and decide if the Locator is controlled at the
866 * org,subinventory or item level. It will also decide
867 * if the locator is pre-specified or dynamic.
868 *******************************************************************/
869 FUNCTION CONTROL(org_control IN number,
870 sub_control IN number,
871 item_control IN number )
872 RETURN NUMBER
873 IS
874 locator_control number;
875 BEGIN
876
877 IF (org_control = 1) then
878 locator_control := 1;
879 ELSIF (org_control = 2) then
880 locator_control := 2;
881 ELSIF (org_control = 3) then
882 locator_control := 3;
883 ELSIF (org_control = 4) then
884 IF (sub_control = 1) then
885 locator_control := 1;
886 ELSIF (sub_control = 2) then
887 locator_control := 2;
888 ELSIF (sub_control = 3) then
889 locator_control := 3;
890 ELSIF (sub_control = 5) then
891 IF (item_control = 1) then
892 locator_control := 1;
893 ELSIF (item_control = 2) then
894 locator_control := 2;
895 ELSIF (item_control = 3) then
896 locator_control := 3;
897 ELSIF (item_control IS NULL) then
898 locator_control := sub_control;
899 END IF;
900 END IF;
901 END IF;
902
903 RETURN locator_control;
904
905 END CONTROL;
906
907
908 /*********************************************************************
909 -- Check if Subinventory Exists
910 *********************************************************************/
911 FUNCTION Check_SubInv_Exists( p_organization_id IN NUMBER
912 , p_subinventory IN VARCHAR2 )
913 RETURN BOOLEAN
914 IS
915
916 -- cursor for checking subinventory exsiting
917 CURSOR l_subinv_csr ( p_organization_id NUMBER
918 , p_subinventory VARCHAR2)
919 IS
920 SELECT 'SubInv exists'
921 FROM SYS.DUAL
922 WHERE NOT EXISTS ( SELECT null
923 FROM mtl_secondary_inventories
924 WHERE organization_id = p_organization_id
925 AND secondary_inventory_name = p_subinventory
926 );
927
928
929 l_ret_status BOOLEAN := TRUE ;
930
931 BEGIN
932
933 FOR l_subinv_rec IN l_subinv_csr ( p_organization_id
934 , p_subinventory )
935 LOOP
936 l_ret_status := FALSE ;
937 END LOOP ;
938 RETURN l_ret_status ;
939
940 END Check_SubInv_Exists ;
941
942
943
944 /*********************************************************************
945 -- Get Restrict Subinventory Flag and Inventory Asset Flag for the Item
946 *********************************************************************/
947 PROCEDURE Get_SubInv_Flag ( p_assembly_item_id IN NUMBER
948 , p_organization_id IN NUMBER
949 , x_rest_subinv_code IN OUT NOCOPY VARCHAR2
950 , x_inv_asset_flag IN OUT NOCOPY VARCHAR2 )
951 IS
952
953 -- cursor for checking subinventory exsiting
954 CURSOR l_subinv_flag_csr ( p_organization_id NUMBER
955 , p_assembly_item_id NUMBER)
956 IS
957 SELECT DECODE(restrict_subinventories_code, 1, 'Y', 'N')
958 restrict_code
959 , inventory_asset_flag
960 FROM MTL_SYSTEM_ITEMS
961 WHERE inventory_item_id = p_assembly_item_id
962 AND organization_id = p_organization_id ;
963
964
965 BEGIN
966
967 FOR l_subinv_flag_rec IN l_subinv_flag_csr ( p_organization_id
968 , p_assembly_item_id )
969 LOOP
970 x_rest_subinv_code := l_subinv_flag_rec.restrict_code ;
971 x_inv_asset_flag := l_subinv_flag_rec.inventory_asset_flag ;
972 END LOOP ;
973
974 END Get_SubInv_Flag ;
975
976
977 /*********************************************************************
978 -- Check Locator
979 *********************************************************************/
980 -- Local function to verify locators
981 FUNCTION Check_Locators ( p_organization_id IN NUMBER
982 , p_assembly_item_id IN NUMBEr
983 , p_locator_id IN NUMBER
984 , p_subinventory IN VARCHAR2 )
985 RETURN BOOLEAN
986 IS
987 Cursor CheckDuplicate is
988 SELECT 'checking for duplicates' dummy
989 FROM sys.dual
990 WHERE EXISTS (
991 SELECT null
992 FROM mtl_item_locations
993 WHERE organization_id = p_organization_id
994 AND inventory_location_id = p_locator_id
995 AND subinventory_code <> p_subinventory
996 );
997
998 x_control NUMBER;
999 l_success BOOLEAN;
1000 l_dummy VARCHAR2(20) ;
1001
1002 BEGIN
1003
1004 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN
1005 Error_Handler.Write_Debug('Check Locators. . .Locator Id is ' || to_char(p_locator_id));
1006 END IF;
1007
1008 l_org_locator_control := 0 ;
1009 l_item_locator_control := 0;
1010
1011
1012 -- Get Value of Org_Locator and item_Locator.
1013 SELECT stock_locator_control_code
1014 INTO l_org_locator_control
1015 FROM mtl_parameters
1016 WHERE organization_id = p_organization_id;
1017
1018 -- Get Value of Item Locator
1019 SELECT location_control_code
1020 INTO l_item_locator_control
1021 FROM mtl_system_items
1022 WHERE organization_id = p_organization_id
1023 AND inventory_item_id = p_assembly_item_id ;
1024
1025 -- Get if locator is restricted or unrestricted
1026 SELECT RESTRICT_LOCATORS_CODE
1027 INTO l_item_loc_restricted
1028 FROM mtl_system_items
1029 WHERE organization_id = p_organization_id
1030 AND inventory_item_id = p_assembly_item_id ;
1031
1032
1033 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN
1034 Error_Handler.Write_Debug('Org - Stock Locator Control : '|| to_char(l_org_locator_control) );
1035 Error_Handler.Write_Debug('Item - Location Control : '|| to_char(l_item_locator_control) );
1036 Error_Handler.Write_Debug('Item - Restrict Locator : '|| to_char(l_item_loc_restricted) );
1037 END IF;
1038
1039 /**************************************
1040 -- Locator_Control_Code
1041 -- 1 : No Locator Control
1042 -- 2 : Prespecified Locator Control
1043 -- 3 : Dynamic Entiry Locator Control
1044 -- 4 : Determined by Sub Inv Level
1045 -- 5 : Determined at Item Level
1046 ***************************************/
1047 /* Commented this for BUG 3872490
1048 --
1049 -- Locator cannot be NULL is if locator restricted
1050 --
1051 IF p_locator_id IS NULL
1052 AND l_item_loc_restricted = 1
1053 THEN
1054 l_locator_control := 4;
1055 RETURN FALSE;
1056 ELSIF p_locator_id IS NULL
1057 AND l_item_loc_restricted = 2
1058 THEN
1059 RETURN TRUE;
1060 END IF;
1061 */
1062 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN
1063 Error_Handler.Write_Debug('Sub Inv - Loc Control : '|| to_char(l_sub_locator_control) );
1064 END IF;
1065
1066
1067
1068 IF l_org_locator_control is not null AND
1069 l_sub_locator_control is not null AND
1070 l_item_locator_control is not null
1071 THEN
1072
1073 x_control := BOM_Validate_Rtg_Header.Control
1074 ( Org_Control => l_org_locator_control,
1075 Sub_Control => l_sub_locator_control,
1076 Item_Control => l_item_locator_control
1077 );
1078
1079 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN
1080 Error_Handler.Write_Debug('Calling BOM_Validate_Rtg_Header.Control. Loc Control '||
1081 to_char(x_control) );
1082 END IF;
1083 l_locator_control := x_control;
1084 -- Variable to identify if the dynamic loc.
1085 -- Message must be logged.
1086
1087 IF x_Control = 1 THEN -- No Locator Control
1088 -- Added following If for BUG 3872490
1089 If p_locator_id is NOT NULL
1090 AND p_subinventory is NOT NULL
1091 Then
1092 RETURN FALSE; -- No Locator and Locator Id is
1093 -- supplied then raise Error
1094 Else
1095 Return TRUE;
1096 End If; -- End of BUG 3872490
1097 ELSIF x_Control = 2 -- PRESPECIFIED
1098 OR x_Control = 3 -- DYNAMIC ENTRY -- bug 3761854
1099 THEN
1100 BEGIN
1101
1102 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN
1103 Error_Handler.Write_Debug ('Checking when x_control returned 2 and ' ||
1104 ' item locator is ' ||
1105 to_char(l_item_locator_control));
1106 END IF;
1107
1108 --BUG 3872490
1109 -- Locator cannot be NULL is if locator control is prespecified
1110 IF p_locator_id IS NULL
1111 AND p_subinventory is NOT NULL
1112 THEN
1113 l_locator_control := 4;
1114 RETURN FALSE;
1115 END IF;
1116 -- If restrict locators is Y then check in
1117 -- mtl_secondary_locators if the item is
1118 -- assigned to the subinventory/location
1119 -- combination If restrict locators is N then
1120 -- check that the locator exists
1121 -- and is assigned to the subinventory and this
1122 -- combination is found in mtl_item_locations.
1123
1124 IF l_item_loc_restricted = 1 -- Restrict Locators = YES
1125 THEN
1126 -- Check for restrict Locators YES
1127 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN
1128 Error_Handler.Write_Debug ('Before Checking for restrict Locators Yes. ' );
1129 END IF;
1130 SELECT 'Valid'
1131 INTO l_dummy
1132 FROM mtl_item_locations mil,
1133 mtl_secondary_locators msl
1134 WHERE msl.inventory_item_id = p_assembly_item_id
1135 AND msl.organization_id = p_organization_id
1136 AND msl.subinventory_code = p_subinventory
1137 AND msl.secondary_locator = p_locator_id
1138 AND mil.inventory_location_id = msl.secondary_locator
1139 AND mil.organization_id = msl.organization_id
1140 AND NVL(mil.disable_date, SYSDATE+1) > SYSDATE ;
1141
1142 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN
1143 Error_Handler.Write_Debug ('Restrict locators is Y . ' ||
1144 'Sub Inv : ' || p_subinventory || 'Comp Loc : ' || to_char(p_locator_id )
1145 || ' are valid.' );
1146 END IF;
1147
1148 -- If no exception is raised then the
1149 -- Locator is Valid
1150 RETURN TRUE;
1151
1152 ELSE
1153 -- Check for restrict Locators NO
1154
1155 SELECT 'Valid'
1156 INTO l_dummy
1157 FROM mtl_item_locations mil
1158 WHERE mil.subinventory_code = p_subinventory
1159 AND mil.inventory_location_id = p_locator_id
1160 AND mil.organization_id = p_organization_id
1161 AND NVL(mil.DISABLE_DATE, SYSDATE+1) > SYSDATE;
1162
1163 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN
1164 Error_Handler.Write_Debug ('Restrict locators is No . ' ||
1165 'Sub Inv : ' || p_subinventory || 'Comp Loc : ' || to_char(p_locator_id )
1166 || ' are valid.' );
1167 END IF;
1168
1169 -- If no exception is raised then the
1170 -- Locator is Valid
1171 RETURN TRUE;
1172
1173 END IF;
1174
1175 EXCEPTION
1176 WHEN NO_DATA_FOUND THEN
1177
1178 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN
1179 Error_Handler.Write_Debug ('Locator is invlaid . ' );
1180 END IF ;
1181
1182 RETURN FALSE;
1183 END; -- x_control=2 Ends
1184
1185 /* ELSIF x_Control = 3 THEN
1186 -- DYNAMIC LOCATORS ARE NOT ALLOWED IN OI.
1187 -- Dynamic locators are not allowed in open
1188 -- interface, so raise an error if the locator
1189 -- control is dynamic.
1190 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN
1191 Error_Handler.Write_Debug ('Dynamic Locator Control. ' ) ;
1192 END IF ;
1193 l_locator_control := 3;
1194
1195
1196 RETURN FALSE;
1197 */ ELSE
1198 -- dbms_output.put_line
1199 -- ('Finally returing a true value . . .');
1200 RETURN TRUE;
1201
1202 END IF; -- X_control Checking Ends
1203
1204 ELSE
1205 RETURN TRUE;
1206 END IF; -- If Locator Control check Ends.
1207
1208 END Check_Locators;
1209
1210
1211 /********************************************************************
1212 * Procedure : Check_Entity
1213 * Parameters IN : Rtg Header Exposed column record
1214 * Rtg Header Unexposed column record
1215 * Old Rtg Header exposed column record
1216 * Old Rtg Header unexposed column record
1217 * Parameters out: Message Token Table
1218 * Return Status
1219 * Purpose : This procedure will perform the business logic
1220 * validation for the BOM Header Entity. It will perform
1221 * any cross entity validations and make sure that the
1222 * user is not entering values which may disturb the
1223 * integrity of the data.
1224 *********************************************************************/
1225 PROCEDURE Check_Entity
1226 ( p_rtg_header_rec IN OUT NOCOPY Bom_Rtg_Pub.rtg_header_Rec_Type
1227 , p_rtg_header_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.rtg_header_Unexposed_Rec_Type
1228 , p_old_rtg_header_rec IN Bom_Rtg_Pub.rtg_header_Rec_Type
1229 , p_old_rtg_header_unexp_rec IN Bom_Rtg_Pub.rtg_header_Unexposed_Rec_Type
1230 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1231 , x_return_status IN OUT NOCOPY VARCHAR2
1232 )
1233 IS
1234 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1235 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
1236 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1237 l_dummy VARCHAR2(1);
1238 l_HasOps BOOLEAN := FALSE;
1239
1240 l_bom_item_type NUMBER;
1241 l_pto_flag VARCHAR2(1);
1242 l_eng_item_flag VARCHAR2(1);
1243 l_bom_enabled_flag VARCHAR2(1);
1244 l_ser_num_control_code MTL_SYSTEM_ITEMS.SERIAL_NUMBER_CONTROL_CODE%TYPE;
1245
1246 l_sub_inv_exists BOOLEAN := FALSE;
1247 l_allow_expense_to_asset VARCHAR2(10);
1248 l_rest_subinv_code VARCHAR2(1);
1249 l_inv_asset_flag VARCHAR2(1);
1250
1251
1252 l_err_text VARCHAR2(2000);
1253
1254 -- cursor for selecting assembly item attributes
1255 CURSOR c_item(
1256 p_org_id NUMBER,
1257 p_item_id NUMBER
1258 )
1259 IS
1260 SELECT bom_item_type
1261 , pick_components_flag
1262 , bom_enabled_flag
1263 , eng_item_flag
1264 , serial_number_control_code
1265 FROM MTL_SYSTEM_ITEMS
1266 WHERE organization_id = p_org_id
1267 AND inventory_item_id = p_item_id;
1268
1269 -- cursor for checking common assembly chain
1270 CURSOR c_CheckCommon(
1271 P_assembly_item_id NUMBER,
1272 P_org_id NUMBER,
1273 P_alt_routing_code VARCHAR2
1274 )
1275 IS
1276 SELECT NVL(common_routing_sequence_id, routing_sequence_id)
1277 common_routing, routing_sequence_id
1278 FROM bom_operational_routings
1279 WHERE assembly_item_id = P_assembly_item_id
1280 AND organization_id = P_org_id
1281 AND NVL(alternate_routing_designator,'XXXX') =
1282 NVL(P_alt_routing_code, 'XXXX');
1283
1284 -- cursor for verifying common routing attributes
1285 CURSOR c_VerifyCommonRtg(
1286 P_cmn_rtg_id NUMBER,
1287 P_rtg_type NUMBER,
1288 P_item_id NUMBER,
1289 P_org_id NUMBER,
1290 p_cfm_rtg_flag NUMBER,
1291 P_alt_desg VARCHAR2) is
1292 SELECT 1 dummy
1293 FROM sys.dual
1294 WHERE not exists (
1295 SELECT NULL
1296 FROM bom_operational_routings bor
1297 WHERE bor.routing_sequence_id = P_cmn_rtg_id
1298 AND NVL(bor.alternate_routing_designator,
1299 'Primary Alternate') = NVL(P_alt_desg, 'Primary Alternate')
1300 AND bor.common_routing_sequence_id =
1301 bor.routing_sequence_id
1302 AND bor.assembly_item_id <> P_item_id
1303 AND bor.organization_id = P_org_id
1304 AND nvl(bor.cfm_routing_flag, 2) = p_cfm_rtg_flag
1305 AND bor.routing_type =
1306 decode(P_rtg_type, 1, 1, bor.routing_type));
1307
1308 -- cursor for checking operaitons exist for the current routing.
1309 CURSOR c_check_Ops(
1310 p_routing_sequence_id NUMBER)
1311 IS
1312 SELECT 'Y' has_ops
1313 FROM sys.dual
1314 WHERE exists ( Select null
1315 FROM Bom_Operation_Sequences
1316 WHERE routing_sequence_id =
1317 p_routing_sequence_id );
1318
1319 -- CTP flag check for routing type
1320 CURSOR c_check_ctp_rtg(
1321 p_assembly_item_id NUMBER,
1322 p_organization_id NUMBER,
1323 p_common_routing_sequence_id NUMBER
1324 )
1325 IS
1326 SELECT 'Y'
1327 FROM sys.dual
1328 WHERE exists ( Select null
1329 FROM bom_operational_routings
1330 WHERE common_routing_sequence_id <>
1331 p_common_routing_sequence_id
1332 AND CTP_flag = 1
1333 AND organization_id = p_organization_id
1334 AND assembly_item_id = p_assembly_item_id ) ;
1335
1336
1337 -- cursor for checking if Flow Routing and referring
1338 -- common routing has active operation sequences
1339 CURSOR c_check_active_ops(
1340 p_routing_sequence_id NUMBER
1341 )
1342 IS
1343 SELECT 'Y'
1344 FROM sys.dual
1345 WHERE exists ( Select null
1346 FROM Bom_Operation_Sequences
1347 WHERE routing_sequence_id =p_routing_sequence_id
1348 AND NVL(disable_date, trunc(sysdate) + 1)
1349 > trunc(sysdate)
1350 );
1351
1352
1353 -- cursor for verifying if Routing for Same Item
1354 -- with active Mixed Model Map Flag does not exist
1355 CURSOR c_check_active_mixed(
1356 P_assembly_item_id NUMBER,
1357 P_organization_id NUMBER,
1358 p_line_id NUMBER,
1359 p_common_routing_sequence_id NUMBER
1360 )
1361 IS
1362 SELECT 'Y'
1363 FROM sys.dual
1364 WHERE exists ( Select null
1365 FROM Bom_Operational_Routings
1366 WHERE organization_id = P_organization_id
1367 AND assembly_item_id = P_assembly_item_id
1368 AND mixed_model_map_flag = 1
1369 AND line_id = p_line_id
1370 AND common_routing_sequence_id
1371 <> p_common_routing_sequence_id );
1372
1373 --cursor for check the priority for standard routing
1374 CURSOR c_check_priority(
1375 p_assembly_item_id NUMBER,
1376 p_organization_id NUMBER,
1377 p_priority NUMBER,
1378 p_common_routing_sequence_id NUMBER
1379 )
1380 IS
1381 SELECT 'Y'
1382 FROM sys.dual
1383 WHERE exists ( Select null
1384 FROM Bom_Operational_Routings
1385 WHERE organization_id = p_organization_id
1386 AND Assembly_Item_Id = p_assembly_item_id
1387 AND priority = p_priority
1388 AND common_routing_sequence_id <>
1389 p_common_routing_sequence_id
1390 );
1391
1392
1393 -- cursors for completion_subinventory check
1394 CURSOR c_Restrict_SubInv_Asset IS
1395 SELECT locator_type
1396 FROM mtl_item_sub_ast_trk_val_v
1397 WHERE inventory_item_id =p_rtg_header_unexp_rec.assembly_item_id
1398 AND organization_id = p_rtg_header_unexp_rec.organization_id
1399 AND secondary_inventory_name =
1400 p_rtg_header_rec.completion_subinventory;
1401
1402 CURSOR c_Restrict_SubInv_Trk IS
1403 SELECT locator_type
1404 FROM mtl_item_sub_trk_val_v
1405 WHERE inventory_item_id = p_rtg_header_unexp_rec.assembly_item_id
1406 AND organization_id = p_rtg_header_unexp_rec.organization_id
1407 AND secondary_inventory_name =
1408 p_rtg_header_rec.completion_subinventory;
1409 CURSOR c_SubInventory_Asset IS
1410 SELECT locator_type
1411 FROM mtl_sub_ast_trk_val_v
1412 WHERE organization_id = p_rtg_header_unexp_rec.organization_id
1413 AND secondary_inventory_name =
1414 p_rtg_header_rec.completion_subinventory;
1415
1416 CURSOR c_Subinventory_Tracked IS
1417 SELECT locator_type
1418 FROM mtl_subinventories_trk_val_v
1419 WHERE organization_id = p_rtg_header_unexp_rec.organization_id
1420 AND secondary_inventory_name =
1421 p_rtg_header_rec.completion_subinventory;
1422
1423 CURSOR c_Check_BOM IS
1424 SELECT NULL from dual
1425 WHERE exists
1426 (SELECT 1 /* Checking for the BOM components operation seq. num. for alternate */
1427 FROM BOM_BILL_OF_MATERIALS BOM, BOM_COMPONENT_OPERATIONS BCO
1428 WHERE BOM.ORGANIZATION_ID = p_rtg_header_unexp_rec.organization_id
1429 AND BOM.ASSEMBLY_ITEM_ID = p_rtg_header_unexp_rec.assembly_item_id
1430 AND BOM.ALTERNATE_BOM_DESIGNATOR = p_rtg_header_rec.alternate_routing_code
1431 AND BOM.BILL_SEQUENCE_ID = BCO.BILL_SEQUENCE_ID)
1432 OR exists
1433 (SELECT 1 /* Checking for the BOM components operation seq. num. for alternate */
1434 FROM BOM_BILL_OF_MATERIALS BOM, BOM_INVENTORY_COMPONENTS BIC
1435 WHERE BOM.ORGANIZATION_ID = p_rtg_header_unexp_rec.organization_id
1436 AND BOM.ASSEMBLY_ITEM_ID = p_rtg_header_unexp_rec.assembly_item_id
1437 AND BOM.ALTERNATE_BOM_DESIGNATOR = p_rtg_header_rec.alternate_routing_code
1438 AND BOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
1439 AND BIC.OPERATION_SEQ_NUM > 1)
1440 ;
1441 BEGIN
1442
1443 --
1444 -- Performing Entity Validation in routing header
1445 --
1446 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1447 ('Performing Entitity Validation for Rtg header. . .') ;
1448 END IF;
1449
1450
1451 --
1452 -- Check Assembly item attributes.
1453 --
1454 FOR l_item_rec IN c_item(
1455 p_org_id => p_rtg_header_unexp_rec.organization_id
1456 , p_item_id => p_rtg_header_unexp_rec.assembly_item_id
1457 )
1458 LOOP
1459 l_bom_item_type := l_item_rec.bom_item_type ;
1460 l_pto_flag := l_item_rec.pick_components_flag ;
1461 l_eng_item_flag := l_item_rec.eng_item_flag ;
1462 l_bom_enabled_flag := l_item_rec.bom_enabled_flag ;
1463 l_ser_num_control_code := l_item_rec.serial_number_control_code;
1464 END LOOP ;
1465
1466 --bug:5235647 Allow routing creation for items having
1467 --serial control as 'None' or 'Pre-Defined' only.
1468 /* Fix for bug 5962485 - Network routings are allowed only for
1469 lot controlled items having serial control as 'None' or 'Pre-Defined' only.
1470 Lot control check is done in BOMRPVTB.pls, serial control check is done here.
1471 Modified If condition to check for Network Routings only (i.e.)cfm_routing_flag = 3 */
1472
1473 IF ( p_rtg_header_rec.cfm_routing_flag = 3 AND
1474 ( ( l_ser_num_control_code <> 1 ) AND ( l_ser_num_control_code <> 2 ) ) )
1475 THEN
1476 Error_Handler.Add_Error_Token
1477 ( p_message_name => 'BOM_ASSEMBLY_NOT_SERIAL'
1478 , p_mesg_token_tbl => l_Mesg_Token_Tbl
1479 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1480 , p_token_tbl => g_token_tbl
1481 );
1482
1483 l_return_status := FND_API.G_RET_STS_ERROR;
1484 END IF;
1485
1486 --
1487 -- Item Attribute: BOM Allowed must be set Yes for the item
1488 -- you are creating routing for.
1489 --
1490 IF l_bom_enabled_flag <> 'Y'
1491 THEN
1492 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
1493 g_token_tbl(1).token_value :=
1494 p_rtg_header_rec.assembly_item_name;
1495
1496 Error_Handler.Add_Error_Token
1497 ( p_message_name => 'BOM_RTG_AITEM_BOM_NOT_ALLOWED'
1498 , p_mesg_token_tbl => l_Mesg_Token_Tbl
1499 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1500 , p_token_tbl => g_token_tbl
1501 );
1502
1503 l_return_status := FND_API.G_RET_STS_ERROR;
1504
1505 END IF ;
1506
1507 --
1508 -- User must not create routing for pick to order items.
1509 -- pick_components_flag must be Yes.
1510 --
1511 IF l_pto_flag <> 'N'
1512 THEN
1513 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
1514 g_token_tbl(1).token_value :=
1515 p_rtg_header_rec.assembly_item_name;
1516
1517 Error_Handler.Add_Error_Token
1518 ( p_message_name => 'BOM_RTG_AITEM_PTO_ITEM'
1519 , p_mesg_token_tbl => l_Mesg_Token_Tbl
1520 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1521 , p_token_tbl => g_token_tbl
1522 );
1523
1524 l_return_status := FND_API.G_RET_STS_ERROR;
1525 END IF ;
1526
1527 /*************************************************
1528 -- Current Release, User cannot create Engineering
1529 -- Routing throught Routing BO.
1530 --
1531 -- If eng_routing_flag 2 (routing_type = 1 : Mfg),
1532 -- then Item Attribute : Eng_Item_Flag must be No.
1533 -- If eng_routing_flag 1 (routing_type 2 : Eng)
1534 -- then Eng_Item_Flag must be Yes or No.
1535 --
1536 IF p_rtg_header_unexp_rec.routing_type = 1 -- Mfg Routing
1537 AND l_eng_item_flag <> 'N'
1538 THEN
1539 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
1540 g_token_tbl(1).token_value :=
1541 p_rtg_header_rec.assembly_item_name;
1542
1543 Error_Handler.Add_Error_Token
1544 ( p_message_name => 'BOM_RTG_AITEMORRTG_TYP_INVALID'
1545 , p_mesg_token_tbl => l_Mesg_Token_Tbl
1546 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1547 , p_token_tbl => g_token_tbl
1548 );
1549 l_return_status := FND_API.G_RET_STS_ERROR;
1550 END IF;
1551
1552 IF p_rtg_header_unexp_rec.routing_type = 2 -- Eng Routing
1553 AND l_eng_item_flag not in ( 'N', 'Y')
1554 THEN
1555 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
1556 g_token_tbl(1).token_value :=
1557 p_rtg_header_rec.assembly_item_name;
1558
1559 Error_Handler.Add_Error_Token
1560 ( p_message_name => 'BOM_RTG_AITEMORRTG_TYP_INVALID'
1561 , p_mesg_token_tbl => l_Mesg_Token_Tbl
1562 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1563 , p_token_tbl => g_token_tbl
1564 );
1565
1566 l_return_status := FND_API.G_RET_STS_ERROR;
1567 END IF;
1568 *************************************************/
1569
1570 -- User is not allowed to create Eng Routing throgh
1571 -- Routing BO in this release.
1572 -- Hence Engineering Item is errored out in Check Access.
1573 -- Also missmach between routing type and Eng Itme Flag
1574 -- should be errored-out here.
1575
1576 -- Bug 4240258 Now enabling the creation of Eng Routing for
1577 -- Mfg Item.So commenting the OR part.
1578
1579 IF (p_rtg_header_unexp_rec.routing_type = 1 -- Mfg Routing
1580 AND l_eng_item_flag <> 'N') -- Eng Item
1581 --OR (p_rtg_header_unexp_rec.routing_type = 2 -- Eng Routing
1582 -- AND l_eng_item_flag <> 'Y' ) -- Mfg Item
1583 THEN
1584 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
1585 g_token_tbl(1).token_value :=
1586 p_rtg_header_rec.assembly_item_name;
1587
1588 Error_Handler.Add_Error_Token
1589 ( p_message_name => 'BOM_RTG_AITRTG_TYPE_MISSMATCH'
1590 , p_mesg_token_tbl => l_Mesg_Token_Tbl
1591 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1592 , p_token_tbl => g_token_tbl
1593 );
1594
1595 l_return_status := FND_API.G_RET_STS_ERROR;
1596 END IF;
1597
1598
1599 --
1600 -- User must not create routing for planning item
1601 -- Bom_Item_Type = 3.
1602 IF l_bom_item_type = 3
1603 THEN
1604 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
1605 g_token_tbl(1).token_value :=
1606 p_rtg_header_rec.assembly_item_name;
1607
1608 Error_Handler.Add_Error_Token
1609 ( p_message_name => 'BOM_RTG_AITEM_PLANING_ITEM'
1610 , p_mesg_token_tbl => l_Mesg_Token_Tbl
1611 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1612 , p_token_tbl => g_token_tbl
1613 );
1614
1615 l_return_status := FND_API.G_RET_STS_ERROR;
1616 END IF ;
1617
1618
1619 --
1620 -- If alternate routing code is NOT NULL, then Primary routing
1621 -- must exist if the user is trying to create an Alternate
1622 --
1623 IF p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
1624 AND p_rtg_header_rec.alternate_routing_code IS NOT NULL
1625 AND p_rtg_header_rec.alternate_routing_code <>
1626 FND_API.G_MISS_CHAR
1627 THEN
1628 BEGIN
1629 SELECT '1'
1630 INTO l_dummy
1631 FROM bom_operational_routings
1632 WHERE alternate_routing_designator IS NULL
1633 AND assembly_item_id =
1634 p_rtg_header_unexp_rec.assembly_item_id
1635 AND organization_id =
1636 p_rtg_header_unexp_rec.organization_id;
1637
1638 EXCEPTION
1639 WHEN NO_DATA_FOUND THEN
1640 l_return_status :=
1641 FND_API.G_RET_STS_ERROR;
1642 l_token_tbl(1).token_name :=
1643 'ASSEMBLY_ITEM_NAME';
1644 l_token_tbl(1).token_value :=
1645 p_rtg_header_rec.assembly_item_name;
1646 Error_Handler.Add_Error_Token
1647 ( p_message_name =>
1648 'BOM_RTG_CANNOT_ADD_ALTERNATE'
1649 , p_token_tbl => l_token_tbl
1650 , p_mesg_token_tbl => l_mesg_token_tbl
1651 , x_mesg_token_tbl => l_mesg_token_tbl
1652 );
1653 END;
1654
1655 -- Added for switch routing project for patchset I
1656 -- When creating alternate routings, check if there are BOMs
1657 -- having references to the primary routing of the assembly
1658 -- which may become invalid once an alternate is created
1659
1660 FOR rec1 IN c_Check_BOM LOOP
1661 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1662 THEN
1663 Error_Handler.Add_Error_Token
1664 (p_message_name => 'BOM_ALT_RTG_REF_BOM'
1665 , p_token_tbl => l_token_tbl
1666 , p_mesg_token_tbl => l_mesg_token_tbl
1667 , x_mesg_token_tbl => l_mesg_token_tbl
1668 , p_message_type => 'W'
1669 );
1670 END IF;
1671 END LOOP;
1672 END IF;
1673
1674 --Bug:4293475 Checking for the disable date before creating an
1675 --alternate for a routing.If the alternate is disabled then
1676 --throwing an error.
1677
1678 IF p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
1679 AND p_rtg_header_rec.alternate_routing_code IS NOT NULL
1680 AND p_rtg_header_rec.alternate_routing_code <> FND_API.G_MISS_CHAR
1681 THEN
1682 BEGIN
1683 SELECT '1'
1684 INTO l_dummy
1685 FROM bom_alternate_designators
1686 WHERE
1687 alternate_designator_code = p_rtg_header_rec.alternate_routing_code
1688 AND organization_id = p_rtg_header_unexp_rec.organization_id
1689 AND disable_date is not null
1690 AND disable_date <= sysdate;
1691
1692 l_return_status := FND_API.G_RET_STS_ERROR;
1693 l_token_tbl.delete;
1694 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
1695 l_token_tbl(1).token_value := p_rtg_header_rec.assembly_item_name;
1696 l_token_tbl(2).token_name := 'ALTERNATE_ROUTING_CODE';
1697 l_token_tbl(2).token_value := p_rtg_header_rec.alternate_routing_code;
1698
1699 Error_Handler.Add_Error_Token
1700 ( p_message_name => 'BOM_RTG_ALTERNATE_DISABLED'
1701 , p_token_tbl => l_token_tbl
1702 , p_mesg_token_tbl => l_mesg_token_tbl
1703 , x_mesg_token_tbl => l_mesg_token_tbl
1704 );
1705 EXCEPTION
1706 WHEN NO_DATA_FOUND THEN
1707 NULL; -- No Error
1708 END;
1709 END IF;
1710 --Bug:4293475 ends
1711
1712 -- Common Routing Validation
1713 IF p_rtg_header_unexp_rec.common_routing_sequence_id <>
1714 p_rtg_header_unexp_rec.routing_sequence_id
1715 THEN
1716
1717 -- Common routing's alt must be same as current routing's alt
1718 -- Common routing cannot have same assembly_item_id as current routing
1719 -- Common routing must have the same org id as current routing
1720 -- Common routing must be mfg routing if current routing is a mfg routing
1721 -- Common routing must have same CFM type as current routing
1722
1723 FOR l_Common_rec in c_VerifyCommonRtg
1724 ( p_cmn_rtg_id => p_rtg_header_unexp_rec.common_routing_sequence_id,
1725 p_rtg_type => p_rtg_header_unexp_rec.routing_type,
1726 p_item_id => p_rtg_header_unexp_rec.assembly_item_id,
1727 P_org_id => p_rtg_header_unexp_rec.organization_id,
1728 p_cfm_rtg_flag => p_rtg_header_rec.cfm_routing_flag,
1729 p_alt_desg => p_rtg_header_rec.alternate_routing_code)
1730 LOOP
1731 l_token_tbl.delete;
1732 l_token_tbl(1).token_name :='COMMON_ASSEMBLY_ITEM_NAME';
1733 l_token_tbl(1).token_value :=
1734 p_rtg_header_rec.common_assembly_item_name;
1735 l_token_tbl(2).token_name := 'ASSEMBLY_ITEM_NAME';
1736 l_token_tbl(2).token_value :=
1737 p_rtg_header_rec.assembly_item_name;
1738
1739 Error_Handler.Add_Error_Token
1740 ( p_message_name =>
1741 'BOM_RTG_ASSY_COMMON_RRG_SEQ'
1742 , p_token_tbl => l_token_tbl
1743 , p_mesg_token_tbl => l_mesg_token_tbl
1744 , x_mesg_token_tbl => l_mesg_token_tbl
1745 );
1746 l_return_status := FND_API.G_RET_STS_ERROR;
1747 END LOOP ;-- validate common
1748
1749
1750 --
1751 -- If the user is assigning a common assembly to the current
1752 -- routing then the common assembly must not already have a
1753 -- common assembly. i.e User cannot create a chain of common
1754 -- routing
1755 BEGIN
1756 SELECT '1'
1757 INTO l_dummy
1758 FROM bom_operational_routings
1759 WHERE routing_sequence_id =
1760 p_rtg_header_unexp_rec.common_routing_sequence_id
1761 AND NVL(common_routing_sequence_id,
1762 routing_sequence_id) <> routing_sequence_id;
1763
1764 l_token_tbl.delete;
1765 l_token_tbl(1).token_name :='COMMON_ASSEMBLY_ITEM_NAME';
1766 l_token_tbl(1).token_value :=
1767 p_rtg_header_rec.common_assembly_item_name;
1768 l_token_tbl(2).token_name := 'ASSEMBLY_ITEM_NAME';
1769 l_token_tbl(2).token_value :=
1770 p_rtg_header_rec.assembly_item_name;
1771 Error_Handler.Add_Error_Token
1772 ( p_message_name =>
1773 'BOM_RTG_ASSY_COMMON_OTHER_ASSY'
1774 , p_token_tbl => l_token_tbl
1775 , p_mesg_token_tbl => l_mesg_token_tbl
1776 , x_mesg_token_tbl => l_mesg_token_tbl
1777 );
1778 l_return_status := FND_API.G_RET_STS_ERROR;
1779
1780 EXCEPTION
1781 WHEN NO_DATA_FOUND THEN
1782 NULL;
1783
1784 END;
1785
1786
1787 --
1788 -- Common_assembly_item_id check
1789 -- If Routing already has its child operation sequences, User
1790 -- cannnot assign Common_Assembly_Item_Id.
1791 --
1792 IF p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
1793 THEN
1794
1795 FOR l_Operation in c_check_ops(
1796 p_routing_sequence_id =>
1797 p_rtg_header_unexp_rec.routing_sequence_id)
1798 LOOP
1799
1800 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
1801 l_token_tbl(1).token_value :=
1802 p_rtg_header_rec.assembly_item_name;
1803 Error_Handler.Add_Error_Token
1804 ( p_message_name =>
1805 'BOM_RTG_COMMON_RTG_NOUPDATABLE'
1806 , p_token_tbl => l_token_tbl
1807 , p_mesg_token_tbl => l_mesg_token_tbl
1808 , x_mesg_token_tbl => l_mesg_token_tbl
1809 );
1810 l_return_status := FND_API.G_RET_STS_ERROR;
1811
1812 END LOOP;
1813 END IF ;
1814
1815 END IF ; -- End of Common Routing Valdiation
1816
1817 --
1818 -- If the user is trying to perform an update, and the routing
1819 -- is referencing another routing as common, then this routing
1820 -- is not updateable.
1821 --
1822 /* This check is not required as we should be able to update the routing details even for
1823 a routing referencing another routing as common -- bug 2923716
1824 IF p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
1825 THEN
1826
1827 FOR l_checkCommon IN c_CheckCommon
1828 ( P_assembly_item_id => p_rtg_header_unexp_rec.assembly_item_id
1829 , P_org_id => p_rtg_header_unexp_rec.organization_id
1830 , P_alt_routing_code=>p_rtg_header_rec.alternate_routing_code)
1831 LOOP
1832 IF l_CheckCommon.common_routing <>
1833 l_CheckCommon.routing_sequence_id
1834 THEN
1835 l_token_tbl.delete;
1836 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
1837 l_token_tbl(1).token_value :=
1838 p_rtg_header_rec.assembly_item_name;
1839 Error_Handler.Add_Error_Token
1840 ( p_message_name =>
1841 'BOM_RTG_ASSY_COMMON_REF_COMMON'
1842 , p_token_tbl => l_token_tbl
1843 , p_mesg_token_tbl => l_mesg_token_tbl
1844 , x_mesg_token_tbl => l_mesg_token_tbl
1845 );
1846 l_return_status := FND_API.G_RET_STS_ERROR;
1847 END IF;
1848
1849 END LOOP;
1850 END IF ;
1851 */
1852 --
1853 -- CTP flag check
1854 -- If the CTP Flag is 1:Yes, Verify if Routing for
1855 -- Same Item with active CTP Flag does not exist.
1856
1857 IF p_rtg_header_rec.ctp_flag = 1
1858 AND ( p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
1859 OR ( p_rtg_header_rec.transaction_type
1860 = BOM_Rtg_Globals.G_OPR_UPDATE
1861 AND p_rtg_header_rec.ctp_flag <>
1862 p_old_rtg_header_rec.ctp_flag )
1863 )
1864 THEN
1865
1866 -- for flow routing type, CFM routing flag = 1;
1867 FOR CTP_Rtg_rec in C_check_CTP_Rtg
1868 ( p_assembly_item_id =>
1869 p_rtg_header_unexp_rec.assembly_item_id
1870 , p_organization_id =>
1871 p_rtg_header_unexp_rec.organization_id
1872 , p_common_routing_sequence_id =>
1873 p_rtg_header_unexp_rec.common_routing_sequence_id
1874 )
1875 LOOP
1876 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
1877 l_token_tbl(1).token_value :=
1878 p_rtg_header_rec.assembly_item_name;
1879 Error_Handler.Add_Error_Token
1880 ( p_message_name =>
1881 'BOM_RTG_CTP_ALREADY_EXISTS'
1882 , p_token_tbl => l_token_tbl
1883 , p_mesg_token_tbl => l_mesg_token_tbl
1884 , x_mesg_token_tbl => l_mesg_token_tbl
1885 );
1886 l_return_status := FND_API.G_RET_STS_ERROR;
1887 END LOOP ;
1888
1889 END IF;
1890
1891 --
1892 -- For UPDATEs, conditionally non updatable Column Check.If Flow
1893 -- Routing and referring common routing has active operation
1894 -- sequences, user cannot update line code.
1895 --
1896 IF p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
1897 AND p_rtg_header_rec.cfm_routing_flag = 1
1898 AND p_rtg_header_unexp_rec.line_id
1899 <> p_old_rtg_header_unexp_rec.line_id
1900 THEN
1901
1902 FOR l_active_ops_rec in c_check_active_ops(
1903 p_routing_sequence_id =>
1904 p_rtg_header_unexp_rec.common_routing_sequence_id)
1905 LOOP
1906
1907 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
1908 l_token_tbl(1).token_value :=
1909 p_rtg_header_rec.assembly_item_name;
1910 Error_Handler.Add_Error_Token
1911 ( p_message_name =>
1912 'BOM_FLM_RTG_LINED_NO_UPDATABLE'
1913 , p_token_tbl => l_token_tbl
1914 , p_mesg_token_tbl => l_mesg_token_tbl
1915 , x_mesg_token_tbl => l_mesg_token_tbl
1916 );
1917 l_return_status := FND_API.G_RET_STS_ERROR;
1918
1919 END LOOP;
1920 END IF;
1921
1922 --
1923 -- If Mixed Model Map Flag is 1: Yes, verify if Routing for Same
1924 -- item with active Mixed Model Map Flag does not exist.
1925 --
1926 IF p_rtg_header_rec.mixed_model_map_flag = 1
1927 AND ( p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
1928 OR ( p_rtg_header_rec.transaction_type
1929 = BOM_Rtg_Globals.G_OPR_UPDATE
1930 AND p_rtg_header_rec.mixed_model_map_flag <>
1931 p_old_rtg_header_rec.mixed_model_map_flag )
1932 )
1933 THEN
1934
1935 FOR l_active_mixed_rec in c_check_active_mixed(
1936 P_assembly_item_id =>
1937 p_rtg_header_unexp_rec.assembly_item_id
1938 , P_organization_id =>
1939 p_rtg_header_unexp_rec.organization_id
1940 , p_line_id =>
1941 p_rtg_header_unexp_rec.line_id
1942 , p_common_routing_sequence_id
1943 =>
1944 p_rtg_header_unexp_rec.common_routing_sequence_id
1945 )
1946 LOOP
1947
1948 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
1949 l_token_tbl(1).token_value :=
1950 p_rtg_header_rec.assembly_item_name;
1951 Error_Handler.Add_Error_Token
1952 ( p_message_name =>
1953 'BOM_FLM_RTG_MMMF_ALRDY_EXISTS'
1954 , p_token_tbl => l_token_tbl
1955 , p_mesg_token_tbl => l_mesg_token_tbl
1956 , x_mesg_token_tbl => l_mesg_token_tbl
1957 );
1958 l_return_status := FND_API.G_RET_STS_ERROR;
1959
1960 END LOOP;
1961 END IF;
1962
1963 -- Unique priority check.
1964 IF p_rtg_header_rec.priority IS NOT NULL
1965 AND p_rtg_header_rec.priority <> FND_API.G_MISS_NUM
1966 AND ( p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
1967 OR ( p_rtg_header_rec.transaction_type
1968 = BOM_Rtg_Globals.G_OPR_UPDATE
1969 AND p_rtg_header_rec.priority <>
1970 NVL(p_old_rtg_header_rec.priority
1971 , FND_API.G_MISS_NUM ) )
1972 )
1973 THEN
1974 FOR l_priority_rec in c_check_priority(
1975 p_assembly_item_id =>
1976 p_rtg_header_unexp_rec.assembly_item_id
1977 , p_organization_id =>
1978 p_rtg_header_unexp_rec.organization_id
1979 , p_priority =>
1980 p_rtg_header_rec.priority
1981 , p_common_routing_sequence_id
1982 =>
1983 p_rtg_header_unexp_rec.common_routing_sequence_id
1984 )
1985 LOOP
1986 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
1987 l_token_tbl(1).token_value :=
1988 p_rtg_header_rec.assembly_item_name;
1989 Error_Handler.Add_Error_Token
1990 ( p_message_name =>
1991 'BOM_RTG_PRIORITY_DUPLICATE'
1992 , p_token_tbl => l_token_tbl
1993 , p_mesg_token_tbl => l_mesg_token_tbl
1994 , x_mesg_token_tbl => l_mesg_token_tbl
1995 );
1996 l_return_status := FND_API.G_RET_STS_ERROR;
1997
1998 END LOOP;
1999
2000 END IF ;
2001
2002
2003 IF p_rtg_header_rec.completion_subinventory IS NULL
2004 OR p_rtg_header_rec.completion_subinventory = FND_API.G_MISS_CHAR
2005 THEN
2006 IF p_rtg_header_unexp_rec.completion_locator_id IS NOT NULL
2007 AND p_rtg_header_unexp_rec.completion_locator_id
2008 <> FND_API.G_MISS_NUM
2009
2010 THEN
2011 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME' ;
2012 l_token_tbl(1).token_value :=
2013 p_rtg_header_rec.assembly_item_name;
2014
2015 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2016 THEN
2017
2018 Error_Handler.Add_Error_Token
2019 ( p_message_name =>
2020 'BOM_RTG_LOCATOR_MUST_BE_NULL'
2021 , p_token_tbl => l_token_tbl
2022 , p_mesg_token_tbl => l_mesg_token_tbl
2023 , x_mesg_token_tbl => l_mesg_token_tbl
2024 );
2025 END IF ;
2026
2027 l_return_status := FND_API.G_RET_STS_ERROR;
2028 END IF;
2029
2030 END IF;
2031
2032
2033 IF p_rtg_header_rec.Completion_Subinventory IS NOT NULL
2034 AND p_rtg_header_rec.Completion_Subinventory
2035 <> FND_API.G_MISS_CHAR
2036 AND ( p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
2037 OR ( p_rtg_header_rec.transaction_type
2038 = BOM_Rtg_Globals.G_OPR_UPDATE
2039 AND p_rtg_header_rec.Completion_Subinventory <>
2040 NVL( p_old_rtg_header_rec.Completion_Subinventory
2041 ,FND_API.G_MISS_CHAR )
2042 )
2043 )
2044 THEN
2045 IF NOT Check_SubInv_Exists
2046 ( p_organization_id =>
2047 p_rtg_header_unexp_rec.organization_id
2048 , p_subinventory
2049 =>p_rtg_header_rec.Completion_Subinventory
2050 )
2051 THEN
2052 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
2053 l_token_tbl(1).token_value :=
2054 p_rtg_header_rec.assembly_item_name;
2055 l_token_tbl(2).token_name := 'COMPLETION_SUBINVENTORY';
2056 l_token_tbl(2).token_value :=
2057 p_rtg_header_rec.completion_subinventory;
2058 Error_Handler.Add_Error_Token
2059 ( p_message_name =>
2060 'BOM_RTG_SUBINV_NAME_INVALID'
2061 , p_token_tbl => l_token_tbl
2062 , p_mesg_token_tbl => l_mesg_token_tbl
2063 , x_mesg_token_tbl => l_mesg_token_tbl
2064 );
2065 l_return_status := FND_API.G_RET_STS_ERROR;
2066 ELSE
2067 l_sub_inv_exists := TRUE ;
2068
2069 END IF ;
2070
2071 END IF;
2072
2073
2074 IF l_sub_inv_exists THEN
2075
2076 -- check completeion subinventory
2077 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2078 ('Performing completeion subinventory check. . .') ;
2079 END IF;
2080 l_allow_expense_to_asset := fnd_profile.value
2081 ('INV:EXPENSE_TO_ASSET_TRANSFER');
2082
2083
2084 Get_SubInv_Flag
2085 ( p_assembly_item_id => p_rtg_header_unexp_rec.assembly_item_id
2086 , p_organization_id => p_rtg_header_unexp_rec.organization_id
2087 , x_rest_subinv_code => l_rest_subinv_code
2088 , x_inv_asset_flag => l_inv_asset_flag ) ;
2089
2090
2091 IF BOM_Rtg_Globals.get_debug = 'Y' THEN
2092 error_handler.write_debug('Get Sub Inv Flag . . . ');
2093 error_handler.write_debug('Expense to asset transfer : '|| l_allow_expense_to_asset );
2094 error_handler.write_debug('Restrict Sub Inv Code : ' || l_rest_subinv_code );
2095 error_handler.write_debug('Inv Asset Flag : '|| l_inv_asset_flag );
2096
2097 END IF;
2098
2099 IF l_rest_subinv_code = 'Y' THEN
2100 IF l_allow_expense_to_asset = '1' THEN
2101
2102 IF BOM_Rtg_Globals.get_debug = 'Y'
2103 THEN
2104 error_handler.write_debug('Before OPEN c_Restrict_SubInv_Trk');
2105 END IF;
2106
2107 OPEN c_Restrict_SubInv_Trk;
2108 FETCH c_Restrict_SubInv_Trk INTO
2109 l_Sub_Locator_Control;
2110
2111 IF c_Restrict_SubInv_Trk%NOTFOUND THEN
2112 CLOSE c_Restrict_SubInv_Trk;
2113 l_token_tbl(1).token_name :=
2114 'ASSEMBLY_ITEM_NAME';
2115 l_token_tbl(1).token_value :=
2116 p_rtg_header_rec.assembly_item_name;
2117 l_token_tbl(2).token_name :=
2118 'COMPLETION_SUBINVENTORY';
2119 l_token_tbl(2).token_value :=
2120 p_rtg_header_rec.completion_subinventory;
2121
2122 Error_Handler.Add_Error_Token
2123 ( p_message_name =>
2124 'BOM_RTG_SINV_RSTRCT_EXPASST'
2125 , p_token_tbl => l_token_tbl
2126 , p_mesg_token_tbl => l_mesg_token_tbl
2127 , x_mesg_token_tbl => l_mesg_token_tbl
2128 );
2129 l_return_status := FND_API.G_RET_STS_ERROR;
2130 ELSE
2131 CLOSE c_Restrict_SubInv_Trk;
2132
2133 END IF;
2134
2135 ELSE -- l_allow_expense_to_asset <> '1'
2136 IF l_inv_asset_flag = 'Y' THEN
2137
2138 OPEN c_Restrict_SubInv_Asset;
2139 FETCH c_Restrict_SubInv_Asset INTO
2140 l_Sub_Locator_Control;
2141 IF c_Restrict_SubInv_Asset%NOTFOUND THEN
2142 CLOSE c_Restrict_SubInv_Asset;
2143
2144 l_token_tbl(1).token_name :=
2145 'ASSEMBLY_ITEM_NAME';
2146 l_token_tbl(1).token_value :=
2147 p_rtg_header_rec.assembly_item_name;
2148 l_token_tbl(2).token_name :=
2149 'COMPLETION_SUBINVENTORY';
2150 l_token_tbl(2).token_value :=
2151 p_rtg_header_rec.completion_subinventory;
2152 Error_Handler.Add_Error_Token
2153 ( p_message_name =>
2154 'BOM_RTG_SINV_RSTRCT_INVASST'
2155 , p_token_tbl => l_token_tbl
2156 , p_mesg_token_tbl => l_mesg_token_tbl
2157 , x_mesg_token_tbl => l_mesg_token_tbl
2158 );
2159 l_return_status :=
2160 FND_API.G_RET_STS_ERROR;
2161 ELSE
2162 CLOSE c_Restrict_SubInv_Asset ;
2163 END IF;
2164
2165 ELSE -- l_inv_asset_flag <> 'Y'
2166
2167 OPEN c_Restrict_SubInv_Trk;
2168 FETCH c_Restrict_SubInv_Trk INTO
2169 l_Sub_Locator_Control;
2170 IF c_Restrict_SubInv_Trk%NOTFOUND THEN
2171 CLOSE c_Restrict_SubInv_Trk;
2172 l_token_tbl(1).token_name :=
2173 'ASSEMBLY_ITEM_NAME';
2174 l_token_tbl(1).token_value :=
2175 p_rtg_header_rec.assembly_item_name;
2176 l_token_tbl(2).token_name :=
2177 'COMPLETION_SUBINVENTORY';
2178 l_token_tbl(2).token_value :=
2179 p_rtg_header_rec.completion_subinventory;
2180 Error_Handler.Add_Error_Token
2181 ( p_message_name =>
2182 'BOM_RTG_SINV_RSTRCT_NOASST'
2183 , p_token_tbl => l_token_tbl
2184 , p_mesg_token_tbl => l_mesg_token_tbl
2185 , x_mesg_token_tbl => l_mesg_token_tbl
2186 );
2187 l_return_status :=
2188 FND_API.G_RET_STS_ERROR;
2189
2190 ELSE
2191 CLOSE c_Restrict_SubInv_Trk;
2192 END IF;
2193 END IF; -- End of l_inv_asset_flag
2194 END IF; -- End of l_allow_expense_to_asset
2195
2196
2197 ELSE -- l_rest_subinv_code <> 'Y'
2198 IF l_allow_expense_to_asset = '1' THEN
2199
2200 OPEN c_SubInventory_Tracked;
2201 FETCH c_SubInventory_Tracked INTO
2202 l_Sub_Locator_Control;
2203 IF c_SubInventory_Tracked%NOTFOUND THEN
2204 CLOSE c_SubInventory_Tracked;
2205 l_token_tbl(1).token_name :=
2206 'ASSEMBLY_ITEM_NAME';
2207 l_token_tbl(1).token_value :=
2208 p_rtg_header_rec.assembly_item_name;
2209 l_token_tbl(2).token_name :=
2210 'COMPLETION_SUBINVENTORY';
2211 l_token_tbl(2).token_value :=
2212 p_rtg_header_rec.completion_subinventory;
2213 Error_Handler.Add_Error_Token
2214 ( p_message_name =>
2215 'BOM_RTG_SINV_NOTRSTRCT_EXPASST'
2216 , p_token_tbl => l_token_tbl
2217 , p_mesg_token_tbl => l_mesg_token_tbl
2218 , x_mesg_token_tbl => l_mesg_token_tbl
2219 );
2220 l_return_status :=
2221 FND_API.G_RET_STS_ERROR;
2222 ELSE
2223 CLOSE c_SubInventory_Tracked;
2224 END IF;
2225
2226 ELSE -- l_allow_expense_to_asset <> '1'
2227 IF l_inv_asset_flag = 'Y' THEN
2228
2229 OPEN c_SubInventory_Asset;
2230 FETCH c_SubInventory_Asset INTO
2231 l_Sub_Locator_Control;
2232 IF c_SubInventory_Asset%NOTFOUND THEN
2233 CLOSE c_SubInventory_Asset;
2234 l_token_tbl(1).token_name :=
2235 'ASSEMBLY_ITEM_NAME';
2236 l_token_tbl(1).token_value :=
2237 p_rtg_header_rec.assembly_item_name;
2238 l_token_tbl(2).token_name :=
2239 'COMPLETION_SUBINVENTORY';
2240 l_token_tbl(2).token_value :=
2241 p_rtg_header_rec.completion_subinventory;
2242 Error_Handler.Add_Error_Token
2243 ( p_message_name =>
2244 'BOM_RTG_SINV_NOTRSTRCT_ASST'
2245 , p_token_tbl => l_token_tbl
2246 , p_mesg_token_tbl => l_mesg_token_tbl
2247 , x_mesg_token_tbl => l_mesg_token_tbl
2248 );
2249 l_return_status :=
2250 FND_API.G_RET_STS_ERROR;
2251 ELSE
2252 CLOSE c_SubInventory_Asset;
2253 END IF;
2254
2255 ELSE -- l_inv_asset_flag <> 'Y'
2256
2257 OPEN c_Subinventory_Tracked;
2258 FETCH c_Subinventory_Tracked INTO
2259 l_Sub_Locator_Control;
2260 IF c_SubInventory_Tracked%NOTFOUND THEN
2261 CLOSE c_Subinventory_Tracked;
2262 l_token_tbl(1).token_name :=
2263 'ASSEMBLY_ITEM_NAME';
2264 l_token_tbl(1).token_value :=
2265 p_rtg_header_rec.assembly_item_name;
2266 l_token_tbl(2).token_name :=
2267 'COMPLETION_SUBINVENTORY';
2268 l_token_tbl(2).token_value :=
2269 p_rtg_header_rec.completion_subinventory;
2270 Error_Handler.Add_Error_Token
2271 ( p_message_name =>
2272 'BOM_RTG_SINV_NOTRSTRCT_NOASST'
2273 , p_token_tbl => l_token_tbl
2274 , p_mesg_token_tbl => l_mesg_token_tbl
2275 , x_mesg_token_tbl => l_mesg_token_tbl
2276 );
2277 l_return_status :=
2278 FND_API.G_RET_STS_ERROR;
2279 ELSE
2280 CLOSE c_Subinventory_Tracked;
2281 END IF;
2282 END IF ; -- End of l_inv_asset_flag = 'Y'
2283 END IF; -- End of l_allow_expense_to_asset
2284 END IF; -- End of -- l_rest_subinv_code = 'Y'
2285 END IF ;
2286
2287 /********************************************************************
2288 --
2289 -- Check Locators
2290 --
2291 ********************************************************************/
2292 -- check completion locator
2293
2294 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2295 ('Performing completion locator. . .') ;
2296 END IF;
2297
2298 IF (( p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
2299 AND NVL(p_rtg_header_unexp_rec.completion_locator_id , 0) <>
2300 NVL(p_rtg_header_unexp_rec.completion_locator_id , 0)
2301 )
2302 OR (p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
2303 AND p_rtg_header_rec.completion_subinventory is not null --BUG 3872490
2304 AND p_rtg_header_rec.completion_subinventory <> FND_API.G_MISS_CHAR) --BUG 3872490
2305 )
2306 AND NOT Check_Locators( p_organization_id => p_rtg_header_unexp_rec.organization_id
2307 , p_assembly_item_id=> p_rtg_header_unexp_rec.assembly_item_id
2308 , p_locator_id => p_rtg_header_unexp_rec.completion_locator_id
2309 , p_subinventory => p_rtg_header_rec.completion_subinventory )
2310 THEN
2311
2312
2313 IF l_locator_control = 4 THEN
2314 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2315 THEN
2316 l_token_tbl(1).token_name :='ASSEMBLY_ITEM_NAME';
2317 l_token_tbl(1).token_value :=
2318 p_rtg_header_rec.assembly_item_name;
2319 Error_Handler.Add_Error_Token
2320 ( p_message_name => 'BOM_RTG_LOCATOR_REQUIRED'
2321 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2322 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2323 , p_Token_Tbl => g_Token_Tbl
2324 );
2325 END IF;
2326
2327 ELSIF l_locator_control = 3 THEN
2328 -- Log the Dynamic locator control message.
2329 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2330 THEN
2331 l_token_tbl(1).token_name :='ASSEMBLY_ITEM_NAME';
2332 l_token_tbl(1).token_value :=
2333 p_rtg_header_rec.assembly_item_name;
2334 Error_Handler.Add_Error_Token
2335 ( p_message_name => 'BOM_RTG_LOC_CANNOT_BE_DYNAMIC'
2336 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2337 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2338 , p_Token_Tbl => g_Token_Tbl
2339 );
2340 END IF;
2341
2342 ELSIF l_locator_control = 2 THEN
2343 IF l_item_loc_restricted = 1 THEN
2344 -- if error occured when item_locator_control was
2345 -- restrcited
2346 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2347 THEN
2348 l_token_tbl(1).token_name :='ASSEMBLY_ITEM_NAME';
2349 l_token_tbl(1).token_value :=
2350 p_rtg_header_rec.assembly_item_name;
2351 l_token_tbl(2).token_name :=
2352 'COMPLETION_SUBINVENTORY';
2353 l_token_tbl(2).token_value :=
2354 p_rtg_header_rec.completion_subinventory;
2355
2356
2357 Error_Handler.Add_Error_Token
2358 ( p_message_name =>
2359 'BOM_RTG_ITEM_LOC_RESTRICTED'
2360 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2361 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2362 , p_Token_Tbl => g_Token_Tbl
2363 );
2364 END IF;
2365 ELSE
2366 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2367 THEN
2368 l_token_tbl(1).token_name :='ASSEMBLY_ITEM_NAME';
2369 l_token_tbl(1).token_value :=
2370 p_rtg_header_rec.assembly_item_name;
2371 Error_Handler.Add_Error_Token
2372 ( p_message_name => 'BOM_RTG_LOCATOR_NOT_IN_SUBINV'
2373 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2374 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2375 , p_Token_Tbl => g_Token_Tbl
2376 );
2377 END IF;
2378 END IF ;
2379
2380 ELSIF l_locator_control = 1 THEN
2381 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2382 THEN
2383 Error_Handler.Add_Error_Token
2384 ( p_message_name =>
2385 'BOM_RTG_ITEM_NO_LOC_CONTROL'
2386 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2387 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2388 , p_Token_Tbl => g_Token_Tbl
2389 );
2390 END IF;
2391
2392 END IF;
2393
2394 l_return_status := FND_API.G_RET_STS_ERROR;
2395 END IF; ---end of locator check
2396
2397 IF p_rtg_header_rec.transaction_type IN (BOM_Rtg_Globals.G_OPR_UPDATE) AND -- Added for SSOS (bug 2689249)
2398 p_rtg_header_rec.ser_start_op_seq IS NOT NULL AND
2399 l_bom_item_type IN (1,2) THEN -- If the item is a model/option class item routing
2400 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2401 THEN
2402 Error_Handler.Add_Error_Token
2403 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
2404 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2405 , p_message_name => 'BOM_SER_OP_CONFIG_RTG_EXISTS'
2406 , p_token_tbl => l_token_tbl
2407 , p_message_type => 'W'
2408 );
2409 END IF ;
2410 END IF;
2411
2412 x_return_status := l_return_status;
2413 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2414
2415 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN
2416 Error_Handler.Write_Debug('Routing header : Entity Validation done . . . Return Status is ' || l_return_status);
2417 END IF ;
2418
2419 EXCEPTION
2420
2421 WHEN FND_API.G_EXC_ERROR THEN
2422
2423 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Expected Error in routing header Entity Validation . . .'); END IF;
2424
2425 x_return_status := FND_API.G_RET_STS_ERROR;
2426 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2427
2428 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2429
2430 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Unexpected Error in routing header Entity Validation . . .'); END IF;
2431
2432 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2433 IF FND_MSG_PUB.Check_Msg_Level
2434 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2435 THEN
2436 l_err_text := G_PKG_NAME ||
2437 ' : (Entity Validation) ' ||
2438 substrb(SQLERRM,1,200);
2439 Error_Handler.Add_Error_Token
2440 ( p_Message_Text => l_err_text
2441 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2442 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2443 );
2444 END IF;
2445 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2446
2447 WHEN OTHERS THEN
2448 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug(SQLERRM || ' ' || TO_CHAR(SQLCODE)); END IF;
2449 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2450 IF FND_MSG_PUB.Check_Msg_Level
2451 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2452 THEN
2453 l_err_text := G_PKG_NAME ||
2454 ' : (Entity Validation) ' ||
2455 substrb(SQLERRM,1,200);
2456 Error_Handler.Add_Error_Token
2457 ( p_Message_Text => l_err_text
2458 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2459 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2460 );
2461 END IF;
2462 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2463
2464 END Check_Entity;
2465
2466
2467 PROCEDURE Check_Entity_Delete
2468 ( x_return_status IN OUT NOCOPY VARCHAR2
2469 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2470 , p_rtg_header_rec IN Bom_Rtg_Pub.rtg_header_Rec_Type
2471 , p_rtg_header_Unexp_Rec IN Bom_Rtg_Pub.rtg_header_Unexposed_Rec_Type
2472 , x_rtg_header_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.rtg_header_Unexposed_Rec_Type
2473 )
2474 IS
2475 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
2476 l_rtg_header_unexp_rec Bom_Rtg_Pub.rtg_header_Unexposed_Rec_Type
2477 := p_rtg_header_Unexp_Rec;
2478 l_err_text VARCHAR2(2000);
2479
2480 Cursor CheckGroup is
2481 SELECT description,
2482 delete_group_sequence_id,
2483 delete_type
2484 FROM bom_delete_groups
2485 WHERE delete_group_name = p_rtg_header_rec.Delete_Group_Name
2486 AND organization_id = p_rtg_header_Unexp_Rec.organization_id;
2487
2488 BEGIN
2489 x_return_status := FND_API.G_RET_STS_SUCCESS;
2490 l_rtg_header_unexp_rec := p_rtg_header_unexp_rec;
2491
2492 IF p_rtg_header_rec.Delete_Group_Name IS NULL OR
2493 p_rtg_header_rec.Delete_Group_Name = FND_API.G_MISS_CHAR
2494 THEN
2495 Error_Handler.Add_Error_Token
2496 ( p_message_name => 'BOM_DG_NAME_MISSING'
2497 , p_mesg_token_tbl => l_mesg_token_tbl
2498 , x_mesg_token_tbl => x_mesg_token_tbl
2499 );
2500 x_return_status := FND_API.G_RET_STS_ERROR;
2501 RETURN;
2502 END IF;
2503
2504 For c_CheckGroup in CheckGroup
2505 LOOP
2506 If c_CheckGroup.delete_type <> 3 /* Bill */ then
2507 Error_Handler.Add_Error_Token
2508 ( p_message_name => 'BOM_DUPLICATE_DELETE_GROUP'
2509 , p_mesg_token_tbl=>l_mesg_token_tbl
2510 , x_mesg_token_tbl=>x_mesg_token_tbl
2511 );
2512 x_return_status := FND_API.G_RET_STS_ERROR;
2513 RETURN;
2514 End if;
2515
2516
2517 l_rtg_header_unexp_rec.DG_description :=
2518 c_Checkgroup.description;
2519 l_rtg_header_unexp_rec.DG_sequence_id :=
2520 c_Checkgroup.delete_group_sequence_id;
2521
2522 -- RETURN;
2523
2524 END LOOP;
2525 /* -- not necessary (bug 2774997)
2526 IF l_rtg_header_unexp_rec.DG_sequence_id IS NULL
2527 THEN
2528 Error_Handler.Add_Error_Token
2529 ( p_message_name => 'NEW_DELETE_GROUP'
2530 , p_message_type => 'W'
2531 , p_mesg_token_tbl => l_mesg_token_tbl
2532 , x_mesg_token_tbl => x_mesg_token_tbl
2533 );
2534
2535 l_rtg_header_unexp_rec.DG_new := TRUE;
2536 l_rtg_header_unexp_rec.DG_description :=
2537 p_rtg_header_rec.DG_description;
2538 END IF;
2539 */
2540
2541 IF l_rtg_header_unexp_rec.Routing_Type IS NULL -- Added for bug 2774997
2542 THEN
2543 SELECT DECODE(p_rtg_header_rec.eng_routing_flag, 1, 2, 1)
2544 INTO l_rtg_header_unexp_rec.routing_type
2545 FROM SYS.DUAL ;
2546 END IF;
2547 -- Return the unexposed record
2548 x_rtg_header_unexp_rec := l_rtg_header_unexp_rec;
2549
2550 EXCEPTION
2551 WHEN OTHERS THEN
2552
2553 l_err_text := G_PKG_NAME || ' Validation (Check Entity Delete) '
2554 || substrb(SQLERRM,1,200);
2555
2556 Error_Handler.Add_Error_Token
2557 ( p_message_name => NULL
2558 , p_message_text => l_err_text
2559 , p_mesg_token_tbl => l_mesg_token_tbl
2560 , x_mesg_token_tbl => l_mesg_token_tbl
2561 ) ;
2562
2563 -- Return the status and message table.
2564 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2565 x_mesg_token_tbl := l_mesg_token_tbl ;
2566
2567 x_rtg_header_unexp_rec := l_rtg_header_unexp_rec;
2568
2569 END Check_Entity_Delete;
2570
2571 PROCEDURE Check_SSOS -- Added for SSOS (bug 2689249)
2572 ( p_rtg_header_rec IN Bom_Rtg_Pub.rtg_header_Rec_Type
2573 , p_rtg_header_unexp_rec IN Bom_Rtg_Pub.rtg_header_Unexposed_Rec_Type
2574 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2575 , x_return_status IN OUT NOCOPY VARCHAR2
2576 ) IS
2577 Cursor check_ssos_cur IS
2578 Select 'IsNotValid'
2579 from dual
2580 where p_rtg_header_rec.ser_start_op_seq NOT IN
2581 (select bos.OPERATION_SEQ_NUM
2582 from bom_operation_sequences bos
2583 where bos.ROUTING_SEQUENCE_ID = p_rtg_header_unexp_rec.routing_sequence_id
2584 and nvl(bos.OPERATION_TYPE,1) = 1
2585 and nvl(bos.EFFECTIVITY_DATE, sysdate-1) <= sysdate
2586 and nvl(bos.disable_date , sysdate + 1) >= sysdate
2587 and bos.OPTION_DEPENDENT_FLAG = 2
2588 and bos.count_point_type = 1)
2589 ;
2590
2591 BEGIN
2592 FOR check_ssos_rec in check_ssos_cur LOOP
2593 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2594 THEN
2595 Error_Handler.Add_Error_Token
2596 ( p_message_name => 'BOM_SSOS_INVALID'
2597 , p_mesg_token_tbl => x_mesg_token_tbl
2598 , x_mesg_token_tbl => x_mesg_token_tbl
2599 );
2600 END IF;
2601 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('SSOS is invalid....'); END IF;
2602 x_return_status := FND_API.G_RET_STS_ERROR;
2603 END LOOP;
2604 END Check_SSOS;
2605
2606
2607 PROCEDURE Check_lot_controlled_item --for bug 3132425
2608 ( p_assembly_item_id IN NUMBER
2609 , p_organization_id IN NUMBER
2610 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2611 , x_return_status IN OUT NOCOPY VARCHAR2
2612 )
2613 IS
2614
2615 CURSOR lot_check is
2616 select lot_control_code
2617 from mtl_system_items m
2618 where m.organization_id = p_organization_id
2619 and m.inventory_item_id = p_assembly_item_id;
2620
2621 BEGIN
2622 x_return_status := FND_API.G_RET_STS_SUCCESS;
2623
2624 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug(
2625 'Within Routing Header Check lot controlled item . . . ');
2626 END IF;
2627
2628 if BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_Lot_Rtg then
2629 FOR cur_count IN lot_check LOOP
2630 if cur_count.lot_control_code = 1
2631 then x_return_status := FND_API.G_RET_STS_ERROR;
2632 end if;
2633 END LOOP;
2634 end if;
2635
2636 EXCEPTION
2637 WHEN OTHERS THEN
2638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2639
2640 END Check_lot_controlled_item;
2641
2642 PROCEDURE Validate_SSOS
2643 ( p_routing_sequence_id IN NUMBER
2644 , p_ser_start_op_seq IN NUMBER
2645 , p_validate_from_table IN BOOLEAN
2646 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2647 , x_return_status IN OUT NOCOPY VARCHAR2
2648 )
2649 IS
2650
2651 l_cfm_routing_flag BOM_OPERATIONAL_ROUTINGS.CFM_ROUTING_FLAG%TYPE;
2652 l_ser_num_control_code MTL_SYSTEM_ITEMS_B.SERIAL_NUMBER_CONTROL_CODE%TYPE;
2653 l_ser_start_op_seq BOM_OPERATIONAL_ROUTINGS.SERIALIZATION_START_OP%TYPE;
2654 l_nw_opern_count NUMBER;
2655 l_retval INTEGER;
2656 l_start_op_seq_id NUMBER;
2657 l_errcode NUMBER;
2658 l_errmsg VARCHAR2(2000);
2659
2660 BEGIN
2661 x_return_status := FND_API.G_RET_STS_SUCCESS;
2662
2663 SELECT COUNT(1)
2664 INTO l_nw_opern_count
2665 FROM BOM_OPERATION_SEQUENCES bos,
2666 BOM_OPERATION_NETWORKS bon
2667 WHERE
2668 bon.FROM_OP_SEQ_ID = bos.OPERATION_SEQUENCE_ID
2669 AND bos.ROUTING_SEQUENCE_ID = p_routing_sequence_id;
2670
2671 -- validate SSOS only when network exists
2672 IF ( l_nw_opern_count > 0 ) THEN
2673
2674 SELECT bor.CFM_ROUTING_FLAG,
2675 bor.SERIALIZATION_START_OP,
2676 msib.SERIAL_NUMBER_CONTROL_CODE
2677 INTO l_cfm_routing_flag,
2678 l_ser_start_op_seq,
2679 l_ser_num_control_code
2680 FROM BOM_OPERATIONAL_ROUTINGS bor, MTL_SYSTEM_ITEMS_B msib
2681 WHERE
2682 bor.ASSEMBLY_ITEM_ID = msib.INVENTORY_ITEM_ID
2683 AND bor.ORGANIZATION_ID = msib.ORGANIZATION_ID
2684 AND bor.ROUTING_SEQUENCE_ID = p_routing_sequence_id;
2685
2686 IF ( ( l_ser_num_control_code = 2 ) AND ( NVL(l_cfm_routing_flag, 2) IN (2, 3) ) )
2687 THEN
2688
2689 IF ( p_validate_from_table = FALSE ) THEN
2690 l_ser_start_op_seq := p_ser_start_op_seq;
2691 END IF;
2692
2693 -- SSOS is required for standard/network routing of serial controlled item.
2694 IF ( ( l_ser_start_op_seq IS NULL ) OR ( l_ser_start_op_seq = FND_API.G_MISS_NUM ) ) THEN
2695
2696 IF ( FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) )
2697 THEN
2698 Error_Handler.Add_Error_Token
2699 ( p_message_name => 'WSM_NTWK_SERIAL_START_OP'
2700 , p_mesg_token_tbl => x_mesg_token_tbl
2701 , x_mesg_token_tbl => x_mesg_token_tbl
2702 );
2703 END IF;
2704 x_return_status := FND_API.G_RET_STS_ERROR;
2705
2706 ELSE
2707 -- SSOS should be on primary path of network.
2708 l_retval := WSMPUTIL.PRIMARY_PATH_IS_EFFECTIVE_TILL
2709 ( p_routing_sequence_id => p_routing_sequence_id,
2710 p_routing_rev_date => SYSDATE,
2711 p_start_op_seq_id => l_start_op_seq_id,
2712 p_op_seq_num => l_ser_start_op_seq,
2713 x_err_code => l_errcode,
2714 x_err_msg => l_errmsg
2715 );
2716
2717 IF (l_retval = 0) THEN
2718 IF ( FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) )
2719 THEN
2720 Error_Handler.Add_Error_Token
2721 ( p_message_name => 'WSM_NTWK_SERIAL_START_OP'
2722 , p_mesg_token_tbl => x_mesg_token_tbl
2723 , x_mesg_token_tbl => x_mesg_token_tbl
2724 );
2725 END IF;
2726 x_return_status := FND_API.G_RET_STS_ERROR;
2727 END IF; -- end if (l_retval = 0)
2728
2729 END IF; -- end if ( l_ser_start_op_seq IS NULL )
2730 END IF; -- end if ( ( l_ser_num_control_code = 2 ) AND ( NVL(l_cfm_routing_flag, 2) IN (2, 3) ) )
2731
2732 END IF; -- ( l_nw_opern_count > 0 )
2733
2734 EXCEPTION
2735 WHEN OTHERS THEN
2736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2737
2738 END Validate_SSOS;
2739
2740 END BOM_Validate_Rtg_Header;