[Home] [Help]
PACKAGE BODY: APPS.BOM_VALIDATE_BOM_HEADER
Source
1 PACKAGE BODY BOM_Validate_Bom_Header AS
2 /* $Header: BOMLBOMB.pls 120.13.12010000.2 2008/11/10 09:33:08 gliang ship $ */
3 /****************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMLBOMB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_Validate_Bom_Header
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 01-JUL-99 Rahul Chitko Initial Creation
21 --
22 -- 08-MAY-2001 Refai Farook EAM related changes
23 --
24 -- 06-May-05 Abhishek Rudresh Common BOM Attr Updates
25
26 -- 20-Jun-05 Vani Hymavathi Validations for to OPM convergence project
27 -- 13-JUL-06 Bhavnesh Patel Added support for Structure Type
28 ****************************************************************************/
29 G_Pkg_Name VARCHAR2(30) := 'BOM_Validate_Bom_Header';
30 g_token_tbl Error_Handler.Token_Tbl_Type;
31
32
33 /*******************************************************************
34 * Procedure : Check_Existence
35 * Returns : None
36 * Parameters IN : Bom Header Exposed Record
37 * Bom Header Unexposed Record
38 * Parameters OUT: Old BOM Header exposed Record
39 * Old BOM Header Unexposed Record
40 * Mesg Token Table
41 * Return Status
42 * Purpose : Procedure will query the old bill of materials 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_bom_header_rec IN Bom_Bo_Pub.Bom_Head_Rec_Type
54 , p_bom_head_unexp_rec IN Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
55 , x_old_bom_header_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Head_Rec_Type
56 , x_old_bom_head_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Head_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 BEGIN
65
66 If Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Quering Assembly item ' || to_char(p_bom_head_unexp_rec.assembly_item_id)); END IF;
67 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug(' Org: ' || to_char(p_bom_head_unexp_rec.organization_id) || ' Alt: ' || p_bom_header_rec.alternate_bom_code ); END IF;
68
69 /* bug 4133037, For creates we need to check for existance in bom_structures_b */
70
71 If(p_bom_header_rec.transaction_type = BOM_Globals.G_OPR_CREATE) then
72 Bom_Bom_Header_Util.Query_Table_Row
73 ( p_assembly_item_id =>
74 p_bom_head_unexp_rec.assembly_item_id
75 , p_alternate_bom_code =>
76 p_bom_header_rec.alternate_bom_code
77 , p_organization_id =>
78 p_bom_head_unexp_rec.organization_id
79 , x_bom_header_rec => x_old_bom_header_rec
80 , x_bom_head_unexp_rec => x_old_bom_head_unexp_rec
81 , x_return_status => l_return_status
82 );
83 else
84 Bom_Bom_Header_Util.Query_Row
85 ( p_assembly_item_id =>
86 p_bom_head_unexp_rec.assembly_item_id
87 , p_alternate_bom_code =>
88 p_bom_header_rec.alternate_bom_code
89 , p_organization_id =>
90 p_bom_head_unexp_rec.organization_id
91 , x_bom_header_rec => x_old_bom_header_rec
92 , x_bom_head_unexp_rec => x_old_bom_head_unexp_rec
93 , x_return_status => l_return_status
94 );
95 end if;
96
97 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Query Row Returned with : ' || l_return_status); END IF;
98
99 IF l_return_status = BOM_Globals.G_RECORD_FOUND AND
100 p_bom_header_rec.transaction_type = BOM_Globals.G_OPR_CREATE
101 THEN
102 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
103 l_token_tbl(1).token_value :=
104 p_bom_header_rec.assembly_item_name;
105 Error_Handler.Add_Error_Token
106 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
107 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
108 , p_message_name => 'BOM_ASSY_ITEM_ALREADY_EXISTS'
109 , p_token_tbl => l_token_tbl
110 );
111 l_return_status := FND_API.G_RET_STS_ERROR;
112 ELSIF l_return_status = BOM_Globals.G_RECORD_NOT_FOUND AND
113 p_bom_header_rec.transaction_type IN
114 (BOM_Globals.G_OPR_UPDATE, BOM_Globals.G_OPR_DELETE)
115 THEN
116 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
117 l_token_tbl(1).token_value :=
118 p_bom_header_rec.assembly_item_name;
119 Error_Handler.Add_Error_Token
120 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
121 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
122 , p_message_name => 'BOM_ASSY_ITEM_DOESNOT_EXISTS'
123 , p_token_tbl => l_token_tbl
124 );
125 l_return_status := FND_API.G_RET_STS_ERROR;
126 ELSIF l_Return_status = FND_API.G_RET_STS_UNEXP_ERROR
127 THEN
128 Error_Handler.Add_Error_Token
129 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
130 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
131 , p_message_name => NULL
132 , p_message_text =>
133 'Unexpected error while existence verification of ' ||
134 'Assembly item '||
135 p_bom_header_rec.assembly_item_name
136 , p_token_tbl => l_token_tbl
137 );
138 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
139 ELSE
140
141 /* Assign the relevant transaction type for SYNC operations */
142
143 IF p_bom_header_rec.transaction_type = 'SYNC' THEN
144 IF l_return_status = Bom_Globals.G_RECORD_FOUND THEN
145 x_old_bom_header_rec.transaction_type :=
146 Bom_Globals.G_OPR_UPDATE;
147 ELSE
148 x_old_bom_header_rec.transaction_type :=
149 Bom_Globals.G_OPR_CREATE;
150 END IF;
151 END IF;
152 l_return_status := FND_API.G_RET_STS_SUCCESS;
153
154 END IF;
155
156 x_return_status := l_return_status;
157 x_mesg_token_tbl := l_mesg_token_tbl;
158 END Check_Existence;
159
160
161 /*******************************************************************
162 * Procedure : Check_Access
163 * Returns : None
164 * Parameters IN : Assembly_Item_Id
165 * Organization_Id
166 * Alternate_Bom_Designator
167 * Parameters OUT: Return Status
168 * Message Token Table
169 * Purpose : This procedure will check if the user has access
170 * to the Assembly Item's BOM Item Type.
171 * If not then an appropriate message and a error status
172 * will be returned back.
173 *********************************************************************/
174 PROCEDURE Check_Access
175 ( p_assembly_item_id IN NUMBER
176 , p_alternate_bom_code IN VARCHAR2
177 , p_organization_id IN NUMBER
178 , p_mesg_token_tbl IN Error_Handler.Mesg_Token_Tbl_Type
179 := Error_Handler.G_MISS_MESG_TOKEN_TBL
180 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
181 , x_return_status IN OUT NOCOPY VARCHAR2
182 )
183 IS
184 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
185 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type :=
186 p_mesg_token_tbl;
187 l_bom_item_type NUMBER;
188 l_assembly_type NUMBER;
189 l_tracking_qty_ind VARCHAR2(30);
190 l_OPM_org VARCHAR2(1);
191 l_token_tbl Error_Handler.Token_Tbl_Type;
192
193 BEGIN
194
195 SELECT bom_item_type, decode(eng_item_flag, 'N', 1, 2)
196 ,tracking_quantity_ind
197 INTO l_bom_item_type, l_assembly_type,l_tracking_qty_ind
198 FROM mtl_system_items
199 WHERE inventory_item_id = p_assembly_item_id
200 AND organization_id = p_organization_id;
201
202 SELECT process_enabled_flag
203 INTO l_OPM_org
204 FROM mtl_parameters
205 WHERE organization_id = p_organization_id;
206
207 --
208 -- If user is trying to update an Engineering Item from BOM
209 -- Business Object, the user should not be allowed.
210 --
211
212 /*IF l_assembly_type = 2 -- Engineering Item
213 THEN
214 Error_Handler.Add_Error_Token
215 ( p_Message_name => 'BOM_ASSEMBLY_TYPE_ENG'
216 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
217 , x_mesg_token_tbl => l_mesg_token_tbl
218 );
219 l_return_status := FND_API.G_RET_STS_ERROR;
220 END IF;*/
221
222 /* Validations for OPM Convergence Project
223 Model/Option class bills are not allowed in OPM organizations*/
224
225 IF (l_OPM_org='Y' and l_bom_item_type in (1,2))THEN
226 Error_Handler.Add_Error_Token
227 ( p_Message_name => 'BOM_OPM_ORG_MODEL_OC'
228 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
229 , x_mesg_token_tbl => l_mesg_token_tbl
230 );
231 l_return_status := FND_API.G_RET_STS_ERROR;
232 END IF;
233
234 /* Validations for OPM Convergence Project
235 Dual UOM controlled items should not be allowed*/
236
237 IF (l_tracking_qty_ind<>'P' )THEN
238 Error_Handler.Add_Error_Token
239 ( p_Message_name => 'BOM_DUAL_UOM_ITEMS'
240 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
241 , x_mesg_token_tbl => l_mesg_token_tbl
242 );
243 l_return_status := FND_API.G_RET_STS_ERROR;
244 END IF;
245
246
247 IF BOM_Globals.Get_STD_Item_Access IS NULL AND
248 BOM_Globals.Get_PLN_Item_Access IS NULL AND
249 BOM_Globals.Get_MDL_Item_Access IS NULL AND
250 BOM_Globals.Get_OC_Item_Access IS NULL
251 THEN
252 --
253 -- Get respective profile values
254 --
255 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Checking item type access . . . '); END IF;
256
257 IF fnd_profile.value('BOM:STANDARD_ITEM_ACCESS') = '1'
258 THEN
259 BOM_Globals.Set_STD_Item_Access
260 ( p_std_item_access => 4);
261 ELSE
262 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('no access to standard items'); END IF;
263 BOM_Globals.Set_STD_Item_Access
264 (p_std_item_access => NULL);
265 END IF;
266
267 IF fnd_profile.value('BOM:MODEL_ITEM_ACCESS') = '1'
268 THEN
269 BOM_Globals.Set_MDL_Item_Access
270 ( p_mdl_item_access => 1);
271 BOM_Globals.Set_OC_Item_Access
272 ( p_oc_item_access => 2);
273 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Model/OC items are accessible. . . '); END IF;
274 ELSE
275 BOM_Globals.Set_MDL_Item_Access
276 ( p_mdl_item_access => NULL);
277 BOM_Globals.Set_OC_Item_Access
278 ( p_oc_item_access => NULL);
279 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug(' Model/OC item access denied . . . '); END IF;
280 END IF;
281
282 IF fnd_profile.value('BOM:PLANNING_ITEM_ACCESS') = '1'
283 THEN
284 BOM_Globals.Set_PLN_Item_Access
285 ( p_pln_item_access => 3);
286 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Planning item accessible. . . '); END IF;
287 ELSE
288 BOM_Globals.Set_PLN_Item_Access
289 ( p_pln_item_access => NULL);
290 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Planning item access denied. . . '); END IF;
291 END IF;
292 END IF;
293
294 --
295 -- Use BOM Item Type of the Assembly Item that is queried above
296 -- to check if user has access to it.
297 --
298 IF l_Bom_Item_Type NOT IN
299 ( NVL(BOM_Globals.Get_STD_Item_Access, 0),
300 NVL(BOM_Globals.Get_PLN_Item_Access, 0),
301 NVL(BOM_Globals.Get_OC_Item_Access, 0) ,
302 NVL(BOM_Globals.Get_MDL_Item_Access, 0),
303 BOM_Globals.G_PRODUCT_FAMILY
304 )
305 THEN
306 l_Token_Tbl(1).Token_Name := 'BOM_ITEM_TYPE';
307 l_Token_Tbl(1).Translate := TRUE;
308 IF l_Bom_Item_Type = 1
309 THEN
310 l_Token_Tbl(1).Token_Value := 'BOM_MODEL';
311 ELSIF l_Bom_Item_Type = 2
312 THEN
313 l_Token_Tbl(1).Token_Value:='BOM_OPTION_CLASS';
314 ELSIF l_Bom_Item_Type = 3
315 THEN
316 l_Token_Tbl(1).Token_Value := 'BOM_PLANNING';
317 ELSIF l_Bom_Item_Type = 4
318 THEN
319 l_Token_Tbl(1).Token_Value := 'BOM_STANDARD';
320 END IF;
321
322 Error_Handler.Add_Error_Token
323 ( p_Message_Name => 'BOM_ASSY_ITEM_ACCESS_DENIED'
324 , p_Mesg_Token_Tbl => l_mesg_token_tbl
325 , x_Mesg_Token_Tbl => l_mesg_token_tbl
326 , p_Token_Tbl => l_token_tbl
327 );
328 l_return_status := FND_API.G_RET_STS_ERROR;
329 END IF;
330
331 x_return_status := l_return_status;
332 x_mesg_token_tbl := l_mesg_token_tbl;
333
334 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Check Access returning with ' || l_return_status ); END IF;
335
336 END Check_Access;
337
338
339 /********************************************************************
340 * Procedure : Check_Attributes
341 * Parameters IN : Revised Item Exposed Column record
342 * Revised Item Unexposed Column record
343 * Old Revised Item Exposed Column record
344 * Old Revised Item unexposed column record
345 * Parameters OUT: Return Status
346 * Mesg Token Table
347 * Purpose : Check_Attrbibutes procedure will validate every
348 * revised item attrbiute in its entirety.
349 **********************************************************************/
350 PROCEDURE Check_Attributes
351 ( x_return_status IN OUT NOCOPY VARCHAR2
352 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
353 , p_bom_header_Rec IN Bom_Bo_Pub.Bom_Head_Rec_Type
354 , p_bom_head_unexp_rec IN Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
355 , p_old_bom_header_rec IN Bom_Bo_Pub.Bom_Head_Rec_Type
356 , p_old_bom_head_unexp_rec IN Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
357 )
358 IS
359 l_err_text VARCHAR2(2000) := NULL;
360 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
361 l_Token_Tbl Error_Handler.Token_Tbl_Type;
362
363 BEGIN
364
365 x_return_status := FND_API.G_RET_STS_SUCCESS;
366
367 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Within Bom Header Check Attributes . . . '); END IF;
368
369 IF p_bom_header_rec.alternate_bom_code IS NOT NULL AND
370 p_bom_header_rec.alternate_bom_code <> FND_API.G_MISS_CHAR
371 AND
372 ( p_bom_header_rec.alternate_bom_code <>
373 p_old_bom_header_rec.alternate_bom_code OR
374 p_old_bom_header_rec.alternate_bom_code IS NULL
375 )
376 THEN
377 IF NOT BOM_Validate.Alternate_Designator
378 ( p_alternate_bom_code =>
379 p_bom_header_rec.alternate_bom_code
380 , p_organization_id =>
381 p_bom_head_unexp_rec.organization_id
382 )
383 THEN
384 l_token_tbl(1).token_name :=
385 'ALTERNATE_BOM_CODE';
386 l_token_tbl(1).token_value :=
387 p_bom_header_rec.alternate_bom_code;
388 Error_Handler.Add_Error_Token
389 ( p_token_tbl => l_token_tbl
390 , p_message_name =>
391 'BOM_ALTERNATE_DESG_INVALID'
392 , p_mesg_token_tbl => l_mesg_token_tbl
393 , x_mesg_token_tbl => l_mesg_token_tbl
394 );
395 x_return_status := FND_API.G_RET_STS_ERROR;
396 END IF;
397 END IF;
398
399 IF p_bom_header_rec.assembly_type IS NOT NULL AND
400 p_bom_header_rec.assembly_type <> FND_API.G_MISS_NUM AND
401 p_bom_header_rec.assembly_type NOT IN (1,2)
402 THEN
403 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
404 l_token_tbl(1).token_value :=
405 p_bom_header_rec.assembly_item_name;
406 l_token_tbl(2).token_name := 'ASSEMBLY_TYPE';
407 l_token_tbl(2).token_value :=
408 p_bom_header_rec.assembly_type;
409 Error_Handler.Add_Error_Token
410 ( p_message_name => 'BOM_ASSEMBLY_TYPE_INVALID'
411 , p_token_tbl => l_token_tbl
412 , p_mesg_token_tbl => l_mesg_token_tbl
413 , x_mesg_token_tbl => l_mesg_token_tbl
414 );
415 x_return_status := FND_API.G_RET_STS_ERROR;
416 END IF;
417
418 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
419
420 END Check_Attributes;
421
422 /*********************************************************************
423 * Procedure : Check_Required
424 * Parameters IN : BOM Header Exposed column record
425 * Parameters OUT: Mesg Token Table
426 * Return_Status
427 * Purpose :
428 **********************************************************************/
429 PROCEDURE Check_Required
430 ( x_return_status IN OUT NOCOPY VARCHAR2
431 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
432 , p_bom_header_Rec IN Bom_Bo_Pub.Bom_Head_Rec_Type
433 )
434 IS
435 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
436 l_Token_Tbl Error_Handler.Token_Tbl_Type;
437 BEGIN
438 x_return_status := FND_API.G_RET_STS_SUCCESS;
439
440 IF ( p_bom_header_rec.common_organization_code IS NOT NULL AND
441 p_bom_header_rec.common_organization_code <>
442 FND_API.G_MISS_CHAR
443 ) AND
444 ( p_bom_header_rec.common_assembly_item_name IS NULL OR
445 p_bom_header_rec.common_assembly_item_name =
446 FND_API.G_MISS_CHAR
447 )
448 THEN
449 --
450 -- If the common org code is given the common assembly
451 -- name is required.
452 --
453 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
454 l_token_tbl(1).token_value :=
455 p_bom_header_rec.assembly_item_name;
456
457 Error_Handler.Add_Error_Token
458 ( p_message_name => 'BOM_COMMON_ASSY_REQUIRED'
459 , p_token_tbl => l_Token_tbl
460 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
461 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
462 );
463
464 x_return_status := FND_API.G_RET_STS_ERROR;
465 END IF;
466
467 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
468
469 END Check_Required;
470
471
472 /********************************************************************
473 * Procedure : Check_Entity
474 * Parameters IN : Bom Header Exposed column record
475 * Bom Header Unexposed column record
476 * Old Bom Header exposed column record
477 * Old Bom Header unexposed column record
478 * Parameters OUT: Message Token Table
479 * Return Status
480 * Purpose : This procedure will perform the business logic
481 * validation for the BOM Header Entity. It will perform
482 * any cross entity validations and make sure that the
483 * user is not entering values which may disturb the
484 * integrity of the data.
485 *********************************************************************/
486 PROCEDURE Check_Entity
487 ( p_bom_header_rec IN Bom_Bo_Pub.Bom_Head_Rec_Type
488 , p_bom_head_unexp_rec IN Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
489 , p_old_bom_head_rec IN Bom_Bo_Pub.Bom_Head_Rec_Type
490 , p_old_bom_head_unexp_rec IN Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
491 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
492 , x_return_status IN OUT NOCOPY VARCHAR2
493 )
494 IS
495 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
496 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
497 l_Token_Tbl Error_Handler.Token_Tbl_Type;
498 l_dummy VARCHAR2(1);
499 l_valid_op_seq VARCHAR2(1);
500 bit NUMBER;
501 base_id NUMBER;
502 struct_type_id NUMBER;
503 ato VARCHAR2(1);
504 pto VARCHAR2(1);
505 assmtype NUMBER;
506 l_count NUMBER;
507 bom_enabled VARCHAR2(1);
508 l_mater_org_id NUMBER;
509 l_comp_rev_status VARCHAR2(1); --Bug 7526867
510
511 CURSOR c_CheckCommon IS
512 SELECT NVL(common_bill_sequence_id,bill_sequence_id) common_bill_seq,
513 bill_sequence_id
514 FROM bom_bill_of_materials
515 WHERE assembly_item_id = p_bom_head_unexp_rec.assembly_item_id
516 AND organization_id = p_bom_head_unexp_rec.organization_id
517 AND NVL(alternate_bom_designator, 'XXXX') =
518 NVL(p_bom_header_rec.alternate_bom_code, 'XXXX');
519
520 l_existing_str_type BOM_STRUCTURE_TYPES_B.STRUCTURE_TYPE_NAME%TYPE;
521
522 BEGIN
523
524 --
525 -- Verify that the Parent has BOM Enabled
526 --
527 select bom_enabled_flag into bom_enabled from mtl_system_items
528 where inventory_item_id = p_bom_head_unexp_rec.assembly_item_id
529 AND organization_id = p_bom_head_unexp_rec.organization_id;
530
531 IF bom_enabled <> 'Y'
532 THEN
533 g_token_tbl(1).token_name := 'REVISED_ITEM_NAME';
534 g_token_tbl(1).token_value :=
535 p_bom_header_rec.assembly_item_name;
536
537 Error_Handler.Add_Error_Token
538 ( p_message_name => 'BOM_REV_ITEM_BOM_NOT_ENABLED'
539 , p_mesg_token_tbl => l_Mesg_Token_Tbl
540 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
541 , p_token_tbl => g_token_tbl
542 );
543
544 l_return_status := FND_API.G_RET_STS_ERROR;
545
546 END IF;
547
548 -- PackBom Related validations ..
549
550 SELECT structure_type_id INTO struct_type_id FROM bom_structure_types_b
551 WHERE structure_type_name = 'Packaging Hierarchy';
552
553 IF p_bom_head_unexp_rec.structure_type_id = struct_type_id
554 THEN
555
556 --
557 -- Verify If PIM_PDS profiles are enabled to create a Packaging Hierarchy
558 IF Bom_Globals.IS_PIM_PDS_ENABLED = 'N'
559 THEN
560 Error_Handler.Add_Error_Token
561 ( p_message_name => 'BOM_CREATE_PACK_HIER_NOT_ALLOW'
562 , p_mesg_token_tbl => l_Mesg_Token_Tbl
563 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
564 );
565 l_return_status := FND_API.G_RET_STS_ERROR;
566 END IF;
567
568 ---
569 -- Packaging BOM creation is allowed only with the prefered structure name for the
570 -- structure type 'Packaging Hierarchy'
571 ---
572 SELECT Count(1) INTO l_count FROM bom_alternate_designators
573 WHERE structure_type_id = struct_type_id and alternate_designator_code = p_bom_header_rec.alternate_bom_code
574 AND organization_id = p_bom_head_unexp_rec.organization_id AND is_preferred = 'Y';
575
576 IF l_count < 1 THEN
577 l_token_tbl(1).token_name := 'STRUCTURE_NAME';
578 l_token_tbl(1).token_value := p_bom_header_rec.alternate_bom_code;
579 l_token_tbl(2).token_name := 'STRUCTURE_TYPE';
580 l_token_tbl(2).token_value := p_bom_header_rec.Structure_Type_Name;
581
582 Error_Handler.Add_Error_Token
583 ( p_Message_Name => 'BOM_STRUCTNAMEANDTYPE_INVALID'
584 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
585 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
586 , p_Token_Tbl => l_token_tbl
587 );
588 l_return_status := FND_API.G_RET_STS_ERROR;
589 END IF;
590
591 ---
592 -- Do not allow packaging hierarchies to be created in child orgs.
593 -- Pkg Hiers cannonly be commoned from the master org.
594 ---
595 IF p_bom_header_rec.transaction_type = Bom_Globals.G_OPR_CREATE AND
596 p_bom_head_unexp_rec.source_bill_sequence_id IS NULL
597 THEN
598 SELECT master_organization_id INTO l_mater_org_id FROM mtl_parameters
599 WHERE organization_id = p_bom_head_unexp_rec.organization_id;
600 IF (p_bom_head_unexp_rec.organization_id <> l_mater_org_id) THEN
601 Error_Handler.Add_Error_Token
602 ( p_message_name => 'BOM_PKG_HIERARCHY_IN_CHILD_ORG'
603 , p_mesg_token_tbl => l_Mesg_Token_Tbl
604 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
605 );
606 l_return_status := FND_API.G_RET_STS_ERROR;
607 END IF;
608 END IF;
609 END IF;
610
611
612 --
613 -- If alternate designator is NOT NULL, then Primary Bill
614 -- must exist if the user is trying to create an Alternate
615 --
616 IF p_bom_header_rec.alternate_bom_code IS NOT NULL AND
617 p_bom_header_rec.alternate_bom_code <> FND_API.G_MISS_CHAR
618 THEN
619 BEGIN
620 SELECT '1'
621 INTO l_dummy
622 FROM bom_bill_of_materials
623 WHERE alternate_bom_designator IS NULL
624 AND assembly_item_id =
625 p_bom_head_unexp_rec.assembly_item_id
626 AND organization_id =
627 p_bom_head_unexp_rec.organization_id
628 AND ((p_bom_header_rec.assembly_type= 2)
629 OR
630 (p_bom_header_rec.assembly_type =1
631 and assembly_type = 1));
632
633 EXCEPTION
634 WHEN NO_DATA_FOUND THEN
635 l_return_status :=
636 FND_API.G_RET_STS_ERROR;
637 l_token_tbl(1).token_name :=
638 'ASSEMBLY_ITEM_NAME';
639 l_token_tbl(1).token_value :=
640 p_bom_header_rec.assembly_item_name;
641 Error_Handler.Add_Error_Token
642 ( p_message_name =>
643 'BOM_CANNOT_ADD_ALTERNATE'
644 , p_token_tbl => l_token_tbl
645 , p_mesg_token_tbl => l_mesg_token_tbl
646 , x_mesg_token_tbl => l_mesg_token_tbl
647 );
648 END;
649 END IF;
650
651 --validate structure type
652 --The value of structure type in the case of null or FND_API.MISS_CHAR
653 --will be ignored.
654
655 IF ( p_bom_header_rec.alternate_bom_code IS NOT NULL
656 AND p_bom_header_rec.alternate_bom_code <> FND_API.G_MISS_CHAR
657 AND p_bom_head_unexp_rec.structure_type_id IS NOT NULL
658 AND p_bom_head_unexp_rec.structure_type_id <> FND_API.G_MISS_NUM )
659 THEN
660 SELECT COUNT(1)
661 INTO l_count
662 FROM BOM_ALTERNATE_DESIGNATORS BAD
663 WHERE
664 BAD.ORGANIZATION_ID = p_bom_head_unexp_rec.organization_id
665 AND BAD.ALTERNATE_DESIGNATOR_CODE = p_bom_header_rec.alternate_bom_code
666 AND BAD.STRUCTURE_TYPE_ID IN
667 ( SELECT BST.STRUCTURE_TYPE_ID
668 FROM BOM_STRUCTURE_TYPES_B BST
669 START WITH BST.STRUCTURE_TYPE_ID = p_bom_head_unexp_rec.structure_type_id
670 CONNECT BY PRIOR BST.PARENT_STRUCTURE_TYPE_ID = BST.STRUCTURE_TYPE_ID
671 );
672
673 IF ( l_count = 0 ) THEN
674 l_return_status := FND_API.G_RET_STS_ERROR;
675
676 l_token_tbl(1).token_name := 'STRUCTURE_NAME';
677 l_token_tbl(1).token_value := p_bom_header_rec.alternate_bom_code;
678 l_token_tbl(2).token_name := 'STRUCTURE_TYPE';
679 l_token_tbl(2).token_value := p_bom_header_rec.Structure_Type_Name;
680
681 Error_Handler.Add_Error_Token
682 ( p_Message_Name => 'BOM_STRUCTNAMEANDTYPE_INVALID'
683 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
684 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
685 , p_Token_Tbl => l_token_tbl
686 );
687 END IF; -- end if l_count = 0
688 END IF; -- end if p_bom_header_rec.alternate_bom_code IS NOT NULL
689
690 -- In update case, the new structure type must be a child of the existing one
691 IF ( p_bom_header_rec.transaction_type = Bom_Globals.G_OPR_UPDATE
692 AND p_bom_head_unexp_rec.structure_type_id IS NOT NULL
693 AND p_bom_head_unexp_rec.structure_type_id <> FND_API.G_MISS_NUM
694 AND p_bom_head_unexp_rec.structure_type_id <> p_old_bom_head_unexp_rec.structure_type_id )
695 THEN
696 SELECT COUNT(1)
697 INTO l_count
698 FROM BOM_STRUCTURE_TYPES_B BST
699 WHERE BST.STRUCTURE_TYPE_ID = p_bom_head_unexp_rec.structure_type_id
700 START WITH BST.STRUCTURE_TYPE_ID = p_old_bom_head_unexp_rec.structure_type_id
701 CONNECT BY PRIOR BST.STRUCTURE_TYPE_ID = BST.PARENT_STRUCTURE_TYPE_ID;
702
703 IF ( l_count = 0 ) THEN
704 l_return_status := FND_API.G_RET_STS_ERROR;
705
706 --existing structure type is not populated in the BO record
707 l_token_tbl(1).token_name := 'OLD_STRTYPE';
708 BEGIN
709 SELECT STRUCTURE_TYPE_NAME
710 INTO l_existing_str_type
711 FROM BOM_STRUCTURE_TYPES_B
712 WHERE STRUCTURE_TYPE_ID = p_old_bom_head_unexp_rec.structure_type_id;
713
714 l_token_tbl(1).token_value := l_existing_str_type;
715 EXCEPTION
716 WHEN NO_DATA_FOUND THEN
717 l_token_tbl(1).token_value := '';
718 END;
719
720 l_token_tbl(2).token_name := 'NEW_STRTYPE';
721 l_token_tbl(2).token_value := p_bom_header_rec.structure_type_name;
722
723 Error_Handler.Add_Error_Token
724 ( p_Message_Name => 'BOM_UPDATE_STRTYPE_INVALID'
725 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
726 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
727 , p_Token_Tbl => l_token_tbl
728 );
729 END IF; -- end if l_count = 0
730 END IF; -- end if p_bom_header_rec.transaction_type = Bom_Globals.G_OPR_UPDATE
731
732
733 /** -------------------------------------------------------------
734 ** When commoning a bill, either creating or updating an existing
735 ** one, the following validations are performed
736 ** 1. Common_bill_Sequence_Id is non-updateable. So once a common
737 ** bom is created user can only delete it and cannot simply
738 ** update it to piont to another bom as common
739 ** 2. Manufactuing BOM's cannot refer to an Engineering BOM as common
740 ** 3. If the BOM being updated already has components, then it cannot
741 ** refer to another BOM as common
742 ** 4. If a BOM is referencing another bill as common, then only the
743 ** parent BOM is updateable
744 ** 5. If a BOM is already referencing another bill as common, then
745 ** this BOM cannot be used as common for another BOM. i.e it is
746 ** not permitted to create a chain of common BOM's
747 ** 6. The current BOM and the bill being referenced as common must have the
748 ** same master org
749 ** 7. If a BOM in one org is referening a BOM in another org as common, then
750 ** make sure that then all the components that exist under the parent org
751 ** must exist both the orgs
752 ** 8. If a BOM in one org is referencing a BOM in another org as common, then
753 ** any substitute components under the components must also exist in both
754 ** orgs
755 ** 9. When referencing another bom as common, the items must have the same
756 ** bom_item_type, pick_components_flag, replenish_to_order_flag and
757 ** bom_enabled_flag
758 ** --------------------------------------------------------------------**/
759
760 --
761 -- If the user is performing an update operation, then the user
762 -- must not enter the value for common organization code and
763 -- common assembly item name. Providing these values would mean that
764 -- the user is attempting to update these non-updateable columns
765 --
766 IF p_bom_header_rec.transaction_type = Bom_Globals.G_OPR_UPDATE
767 AND
768 ( ( p_bom_header_rec.common_organization_code IS NOT NULL
769 AND p_bom_header_rec.common_organization_code <>
770 FND_API.G_MISS_CHAR
771 AND NVL(p_bom_header_rec.common_organization_code, 'XXX') <>
772 NVL(p_old_bom_head_rec.common_organization_code,'XXX')
773 )
774 OR
775 ( p_bom_header_rec.common_assembly_item_name IS NOT NULL
776 AND p_bom_header_rec.common_assembly_item_name <>
777 FND_API.G_MISS_CHAR
778 AND NVL(p_bom_header_rec.common_assembly_item_name, 'NONE') <>
779 NVL(p_old_bom_head_rec.common_assembly_item_name, 'NONE')
780 )
781 )
782 THEN
783
784 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
785 l_token_tbl(1).token_value :=
786 p_bom_header_rec.assembly_item_name;
787 Error_Handler.Add_Error_Token
788 ( p_message_name =>
789 'BOM_COMMON_ORG_ASSY_NONUPD'
790 , p_token_tbl => l_token_tbl
791 , p_mesg_token_tbl => l_mesg_token_tbl
792 , x_mesg_token_tbl => l_mesg_token_tbl
793 );
794 l_return_status := FND_API.G_RET_STS_ERROR;
795 END IF;
796
797 --
798 -- If the user is trying to perform an update, and the bill is
799 -- referencing another bill as common, then this bill is not
800 -- updateable. Only the parent bill is
801 --
802 FOR CheckCommon IN c_CheckCommon
803 LOOP
804 IF CheckCommon.common_bill_seq <>
805 CheckCommon.bill_sequence_id
806 THEN
807 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
808 l_token_tbl(1).token_value :=
809 p_bom_header_rec.assembly_item_name;
810 Error_Handler.Add_Error_Token
811 ( p_message_name =>
812 'BOM_ASSY_COMMON_REF_COMMON'
813 , p_token_tbl => l_token_tbl
814 , p_mesg_token_tbl => l_mesg_token_tbl
815 , x_mesg_token_tbl => l_mesg_token_tbl
816 );
817 l_return_status := FND_API.G_RET_STS_ERROR;
818 END IF;
819
820 END LOOP;
821 --
822 --
823 -- If the user is assigning a common assembly to the current
824 -- bill then the common assembly must already have a common
825 -- assembly. i.e User cannot create a chain of common bills
826 --
827 -- Using the common bill sequence_id check if the record for the
828 -- common bill has a common bill sequence id.
829 IF p_bom_head_unexp_rec.source_bill_sequence_id IS NOT NULL AND
830 p_bom_head_unexp_rec.source_bill_sequence_id <>
831 FND_API.G_MISS_NUM
832 THEN
833 BEGIN
834 SELECT '1'
835 INTO l_dummy
836 FROM bom_bill_of_materials
837 WHERE bill_sequence_id =
838 p_bom_head_unexp_rec.source_bill_sequence_id
839 AND NVL(source_bill_sequence_id, bill_sequence_id) <>
840 bill_sequence_id;
841
842 l_token_tbl.delete;
843 l_token_tbl(1).token_name := 'COMMON_ASSEMBLY_ITEM_NAME';
844 l_token_tbl(1).token_value :=
845 p_bom_header_rec.common_assembly_item_name;
846 l_token_tbl(2).token_name := 'ASSEMBLY_ITEM_NAME';
847 l_token_tbl(2).token_value :=
848 p_bom_header_rec.assembly_item_name;
849 Error_Handler.Add_Error_Token
850 ( p_message_name =>
851 'BOM_ASSY_COMMON_OTHER_ASSY'
852 , p_token_tbl => l_token_tbl
853 , p_mesg_token_tbl => l_mesg_token_tbl
854 , x_mesg_token_tbl => l_mesg_token_tbl
855 );
856 l_return_status := FND_API.G_RET_STS_ERROR;
857
858 EXCEPTION
859 WHEN NO_DATA_FOUND THEN
860 NULL;
861
862 END;
863 --
864 -- If the current bill is a manufacturing bill then the
865 -- common bill must also be a manufactuing bill
866 --
867 BEGIN
868 SELECT '1'
869 INTO l_dummy
870 FROM mtl_system_items assy,
871 mtl_system_items common
872 WHERE assy.inventory_item_id =
873 p_bom_head_unexp_rec.assembly_item_id
874 AND assy.organization_id =
875 p_bom_head_unexp_rec.organization_id
876 AND common.inventory_item_id =
877 p_bom_head_unexp_rec.common_assembly_item_id
878 AND common.organization_id =
879 p_bom_head_unexp_rec.common_organization_id
880 AND ((common.eng_item_flag = 'N' and
881 assy.eng_item_flag = common.eng_item_flag)
882 OR
883 common.eng_item_flag <> 'N');
884
885 EXCEPTION
886 WHEN NO_DATA_FOUND THEN
887 l_token_tbl(1).token_name :=
888 'ASSEMBLY_ITEM_NAME';
889 l_token_tbl(1).token_value :=
890 p_bom_header_rec.assembly_item_name;
891 l_token_tbl(2).token_name :=
892 'COMMON_ASSEMBLY_ITEM_NAME';
893 l_token_tbl(2).token_value :=
894 p_bom_header_rec.common_assembly_item_name;
895 Error_Handler.Add_Error_Token
896 ( p_message_name =>
897 'BOM_COMMON_ASSY_TYPE_MISMATCH'
898 , p_token_tbl => l_token_tbl
899 , p_mesg_token_tbl => l_mesg_token_tbl
900 , x_mesg_token_tbl => l_mesg_token_tbl
901 );
902 l_return_status := FND_API.G_RET_STS_ERROR;
903 END;
904 END IF;
905
906 IF p_bom_header_Rec.ENABLE_ATTRS_UPDATE = 'Y'
907 THEN
908 -- Add operation sequence number validation here
909 --call bompcmbm.validate_operation_sequence_id
910 BOMPCMBM.Validate_Operation_Sequence_Id(p_src_bill_sequence_id => p_bom_head_unexp_rec.source_bill_sequence_id
911 , p_assembly_item_id => p_bom_head_unexp_rec.assembly_item_id
912 , p_organization_id => p_bom_head_unexp_rec.organization_id
913 , p_alt_desg => p_bom_header_rec.alternate_bom_code
914 , x_Return_Status => l_valid_op_seq);
915 IF l_valid_op_seq = FND_API.G_RET_STS_ERROR
916 THEN
917 --BOM_COMMON_OPN_INVALID
918 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
919 l_token_tbl(1).token_value := p_bom_header_rec.assembly_item_name;
920 l_token_tbl(2).token_name := 'COMMON_ASSY_ITEM_NAME';
921 l_token_tbl(2).token_value := p_bom_header_rec.common_assembly_item_name;
922 l_token_tbl(3).token_name := 'SOURCE_ORG';
923 l_token_tbl(3).token_value := p_bom_header_rec.common_organization_code;
924 l_token_tbl(4).token_name := 'DEST_ORG';
925 l_token_tbl(4).token_value := p_bom_header_rec.organization_code;
926 l_token_tbl(5).token_name := 'ASSEMBLY_ITEM_NAME1';
927 l_token_tbl(5).token_value := p_bom_header_rec.assembly_item_name;
928
929 Error_Handler.Add_Error_Token
930 ( p_message_name =>
931 'BOM_COMMON_OPN_INVALID'
932 , p_token_tbl => l_token_tbl
933 , p_mesg_token_tbl => l_mesg_token_tbl
934 , x_mesg_token_tbl => l_mesg_token_tbl
935 );
936 l_return_status := FND_API.G_RET_STS_ERROR;
937 END IF;
938 END IF;
939
940
941
942 --
943 -- Validation for inter-org common
944 --
945 IF ( p_bom_header_rec.transaction_type = Bom_Globals.G_OPR_UPDATE AND
946 p_bom_head_unexp_rec.source_bill_sequence_id IS NOT NULL AND
947 p_bom_head_unexp_rec.source_bill_sequence_id <> FND_API.G_MISS_NUM AND
948 p_old_bom_head_unexp_rec.source_bill_sequence_id IS NULL AND
949 p_old_bom_head_unexp_rec.organization_id <>
950 p_bom_head_unexp_rec.common_organization_id
951 )
952 OR
953 ( p_bom_header_rec.transaction_type = Bom_Globals.G_OPR_CREATE AND
954 p_bom_head_unexp_rec.source_bill_sequence_id IS NOT NULL AND
955 p_bom_head_unexp_rec.source_bill_sequence_id <> FND_API.G_MISS_NUM AND
956 p_bom_head_unexp_rec.common_organization_id <>
957 p_bom_head_unexp_rec.organization_id
958 )
959 THEN
960 BEGIN
961 SELECT '1'
962 INTO l_dummy
963 FROM mtl_parameters mp1, mtl_parameters mp2
964 WHERE mp1.organization_id = p_bom_head_unexp_rec.organization_id
965 AND mp2.organization_id =
966 DECODE(p_bom_header_rec.transaction_type, Bom_Globals.G_OPR_CREATE,
967 p_bom_head_unexp_rec.common_organization_id,
968 Bom_Globals.G_OPR_UPDATE,
969 p_old_bom_head_unexp_rec.common_organization_id
970 )
971 AND mp1.master_organization_id = mp2.master_organization_id;
972
973 EXCEPTION
974 WHEN no_data_found THEN
975 l_token_tbl(1).token_name :=
976 'ASSEMBLY_ITEM_NAME';
977 l_token_tbl(1).token_value :=
978 p_bom_header_rec.assembly_item_name;
979 l_token_tbl(2).token_name :=
980 'COMMON_ASSEMBLY_ITEM_NAME';
981 l_token_tbl(2).token_value :=
982 p_bom_header_rec.common_assembly_item_name;
983 l_token_tbl(3).token_name := 'ORG_CODE';
984 l_token_tbl(3).token_value :=
985 p_bom_header_rec.organization_code;
986 l_token_tbl(4).token_name := 'COMMON_ORG_CODE';
987 l_token_tbl(4).token_value :=
988 p_bom_header_rec.common_organization_code;
989
990 Error_Handler.Add_Error_Token
991 ( p_message_name =>
992 'BOM_COMMON_MASTER_ORG_MISMATCH'
993 , p_token_tbl => l_token_tbl
994 , p_mesg_token_tbl => l_mesg_token_tbl
995 , x_mesg_token_tbl => l_mesg_token_tbl
996 );
997
998 l_return_status := FND_API.G_RET_STS_ERROR;
999 END; -- if master org same ends
1000
1001 -- Add operation sequence number validation here
1002 --call bompcmbm.validate_operation_sequence_id
1003 /*BOMPCMBM.Validate_Operation_Sequence_Id(p_src_bill_sequence_id => p_bom_head_unexp_rec.source_bill_sequence_id
1004 , p_assembly_item_id => p_bom_head_unexp_rec.assembly_item_id
1005 , p_organization_id => p_bom_head_unexp_rec.organization_id
1006 , p_alt_desg => p_bom_header_rec.alternate_bom_code
1007 , x_Return_Status => l_valid_op_seq);
1008 IF l_valid_op_seq = FND_API.G_RET_STS_UNEXP_ERROR
1009 THEN
1010 --BOM_COMMON_OPN_INVALID
1011 l_token_tbl(1).token_name := 'ALT_DESG';
1012 l_token_tbl(1).token_value := p_bom_header_rec.alternate_bom_code;
1013 l_token_tbl(2).token_name := 'COMMON_ASSY_ITEM_NAME';
1014 l_token_tbl(2).token_value := p_bom_header_rec.common_assembly_item_name;
1015 l_token_tbl(3).token_name := 'SOURCE_ORG';
1016 l_token_tbl(3).token_value := p_bom_header_rec.common_organization_code;
1017 l_token_tbl(4).token_name := 'DEST_ORG';
1018 l_token_tbl(4).token_value := p_bom_header_rec.organization_code;
1019
1020 Error_Handler.Add_Error_Token
1021 ( p_message_name =>
1022 'BOM_COMMON_OPN_INVALID'
1023 , p_token_tbl => l_token_tbl
1024 , p_mesg_token_tbl => l_mesg_token_tbl
1025 , x_mesg_token_tbl => l_mesg_token_tbl
1026 );
1027 l_return_status := FND_API.G_RET_STS_ERROR;
1028 END IF;*/
1029
1030 -- If the current bom and the bom being referenced as common are not in the
1031 -- same org, then make sure that the components of the parent BOM exist in
1032 -- both the organizations
1033 BEGIN
1034 SELECT bom_item_type, base_item_id, replenish_to_order_flag,
1035 pick_components_flag--, DECODE(eng_item_flag, 'Y', 2, 1)
1036 INTO bit, base_id, ato, pto--, assmtype
1037 FROM mtl_system_items
1038 WHERE inventory_item_id = p_bom_head_unexp_rec.assembly_item_id
1039 AND organization_id = p_bom_head_unexp_rec.organization_id;
1040
1041 SELECT assembly_type
1042 INTO assmtype
1043 FROM bom_structures_b
1044 WHERE bill_sequence_id = p_bom_head_unexp_rec.source_bill_sequence_id;
1045
1046 SELECT count(*)
1047 INTO l_count
1048 FROM bom_inventory_components bic
1049 WHERE bic.bill_sequence_id = p_bom_head_unexp_rec.source_bill_sequence_id
1050 AND nvl(bic.disable_date, sysdate + 1) >= sysdate --- Bug: 3448641
1051 AND not exists
1052 (SELECT 'x'
1053 FROM mtl_system_items s
1054 WHERE s.organization_id = p_bom_head_unexp_rec.organization_id
1055 AND s.inventory_item_id = bic.component_item_id
1056 AND ((assmtype = 1 AND s.eng_item_flag = 'N')
1057 OR (assmtype = 2)
1058 )
1059 /* Commented the following for Bug2984763 */
1060 AND s.bom_enabled_flag = 'Y' /* Uncommented for bug 5925020 */
1061 AND s.inventory_item_id <> p_bom_head_unexp_rec.assembly_item_id
1062 AND ((bit = 1 AND s.bom_item_type <> 3)
1063 OR (bit = 2 AND s.bom_item_type <> 3)
1064 OR (bit = 3)
1065 OR (bit = 4
1066 AND (s.bom_item_type = 4
1067 OR
1068 ( s.bom_item_type IN (2, 1)
1069 AND s.replenish_to_order_flag = 'Y'
1070 AND base_id IS NOT NULL
1071 AND ato = 'Y'
1072 )
1073 )
1074 )
1075 )
1076 AND (bit = 3
1077 OR
1078 pto = 'Y'
1079 OR
1080 s.pick_components_flag = 'N'
1081 )
1082 AND (bit = 3
1083 OR
1084 NVL(s.bom_item_type, 4) <> 2
1085 OR
1086 (s.bom_item_type = 2
1087 AND (( pto = 'Y'
1088 AND s.pick_components_flag = 'Y'
1089 )
1090 OR ( ato = 'Y'
1091 AND s.replenish_to_order_flag = 'Y'
1092 )
1093 )
1094 )
1095 )
1096 AND not( bit = 4
1097 AND pto = 'Y'
1098 AND s.bom_item_type = 4
1099 AND s.replenish_to_order_flag = 'Y'
1100 )
1101 );
1102
1103
1104
1105 IF l_Count > 0
1106 THEN
1107 l_token_tbl.DELETE;
1108 l_token_tbl(1).token_name :=
1109 'ASSEMBLY_ITEM_NAME';
1110 l_token_tbl(1).token_value :=
1111 p_bom_header_rec.assembly_item_name;
1112 l_token_tbl(2).token_name := 'ORG_CODE';
1113 l_token_tbl(2).token_value :=
1114 p_bom_header_rec.organization_code;
1115 l_token_tbl(3).token_name := 'COMMON_ORG_CODE';
1116 l_token_tbl(3).token_value :=
1117 p_bom_header_rec.common_organization_code;
1118
1119 Error_Handler.Add_Error_Token
1120 ( p_message_name => 'BOM_COMMON_COMP_PROP_MISMATCH'
1121 , p_token_tbl => l_token_tbl
1122 , p_mesg_token_tbl => l_mesg_token_tbl
1123 , x_mesg_token_tbl => l_mesg_token_tbl
1124 );
1125
1126 l_return_status := FND_API.G_RET_STS_ERROR;
1127 END IF;
1128 END;
1129
1130 --If the source bom comtains fixed rev components, make sure the same revisions exist in the
1131 --destination org.
1132 BOMPCMBM.check_comp_rev_in_local_org(p_src_bill_seq_id => p_bom_head_unexp_rec.source_bill_sequence_id,
1133 p_org_id => p_bom_head_unexp_rec.organization_id,
1134 x_return_status => l_comp_rev_status); --Bug 7526867
1135
1136 IF l_comp_rev_status <> FND_API.G_RET_STS_SUCCESS --Bug 7526867
1137 THEN
1138
1139 l_token_tbl.DELETE;
1140 l_token_tbl(1).token_name := 'ASSY_ITEM';
1141 l_token_tbl(1).token_value := p_bom_header_rec.assembly_item_name;
1142 l_token_tbl(2).token_name := 'ORG_CODE';
1143 l_token_tbl(2).token_value := p_bom_header_rec.organization_code;
1144
1145 Error_Handler.Add_Error_Token
1146 ( p_message_name => 'BOM_SRC_COMP_FIXED_REV'
1147 , p_token_tbl => l_token_tbl
1148 , p_mesg_token_tbl => l_mesg_token_tbl
1149 , x_mesg_token_tbl => l_mesg_token_tbl
1150 );
1151
1152 l_return_status := FND_API.G_RET_STS_ERROR; --Bug 7526867
1153
1154 END IF;
1155
1156 --
1157 -- Make sure that the substitute components exist in both the organizations
1158 --
1159 BEGIN
1160 SELECT count(*)
1161 INTO l_count
1162 FROM bom_inventory_components bic,
1163 bom_substitute_components bsc
1164 WHERE bic.bill_sequence_id =p_bom_head_unexp_rec.source_bill_sequence_id
1165 AND bic.component_sequence_id = bsc.component_sequence_id
1166 AND bsc.substitute_component_id not in
1167 (select msi1.inventory_item_id
1168 from mtl_system_items msi1, mtl_system_items msi2
1169 where msi1.organization_id = p_bom_head_unexp_rec.organization_id
1170 and msi1.inventory_item_id = bsc.substitute_component_id
1171 and msi1.bom_enabled_flag = 'Y'
1172 and msi2.organization_id = p_bom_head_unexp_rec.common_organization_id
1173 and msi2.inventory_item_id = msi1.inventory_item_id
1174 AND ((assmtype = 1 AND msi1.eng_item_flag = 'N')
1175 OR (assmtype = 2)
1176 )
1177 AND msi1.inventory_item_id <> p_bom_head_unexp_rec.assembly_item_id
1178 AND ((bit = 1 AND msi1.bom_item_type <> 3)
1179 OR (bit = 2 AND msi1.bom_item_type <> 3)
1180 OR (bit = 3)
1181 OR (bit = 4
1182 AND (msi1.bom_item_type = 4
1183 OR
1184 ( msi1.bom_item_type IN (2, 1)
1185 AND msi1.replenish_to_order_flag = 'Y'
1186 AND base_id IS NOT NULL
1187 AND ato = 'Y'
1188 )
1189 )
1190 )
1191 )
1192 AND (bit = 3
1193 OR
1194 pto = 'Y'
1195 OR
1196 msi1.pick_components_flag = 'N'
1197 )
1198 AND (bit = 3
1199 OR
1200 NVL(msi1.bom_item_type, 4) <> 2
1201 OR
1202 (msi1.bom_item_type = 2
1203 AND (( pto = 'Y'
1204 AND msi1.pick_components_flag = 'Y'
1205 )
1206 OR ( ato = 'Y'
1207 AND msi1.replenish_to_order_flag = 'Y'
1208 )
1209 )
1210 )
1211 )
1212 AND not( bit = 4
1213 AND pto = 'Y'
1214 AND msi1.bom_item_type = 4
1215 AND msi1.replenish_to_order_flag = 'Y'
1216 )
1217 );
1218 IF (l_count > 0) THEN
1219 l_token_tbl.DELETE;
1220 l_token_tbl(1).token_name :=
1221 'COMPONENT_ITEM_NAME';
1222 l_token_tbl(1).token_value :=
1223 p_bom_header_rec.assembly_item_name;
1224 l_token_tbl(2).token_name := 'ASSEMBLY_ITEM_NAME';
1225 l_token_tbl(2).token_value :=
1226 p_bom_header_rec.organization_code;
1227 l_token_tbl(3).token_name := 'COMMON_ORG_CODE';
1228 l_token_tbl(3).token_value :=
1229 p_bom_header_rec.common_organization_code;
1230
1231 Error_Handler.Add_Error_Token
1232 ( p_message_name => 'BOM_COMMON_SCOMP_NOTINALLORG'
1233 , p_token_tbl => l_token_tbl
1234 , p_mesg_token_tbl => l_mesg_token_tbl
1235 , x_mesg_token_tbl => l_mesg_token_tbl
1236 );
1237
1238 l_return_status := FND_API.G_RET_STS_ERROR;
1239
1240 END IF;
1241 END; -- Check if all the substitutes exist ends
1242 END IF; -- User creating a common or updating the common info Ends
1243 -- check to see if bill item and common item have same bom_item_type,
1244 -- pick_components_flag and replenish_to_order_flag
1245 -- Common item must have bom_enabled_flag = 'Y'
1246 --
1247 IF ( p_bom_header_rec.transaction_type = Bom_Globals.G_OPR_UPDATE AND
1248 p_bom_head_unexp_rec.source_bill_sequence_id IS NOT NULL AND
1249 p_bom_head_unexp_rec.source_bill_sequence_id <> FND_API.G_MISS_NUM AND
1250 p_old_bom_head_unexp_rec.source_bill_sequence_id IS NULL
1251 )
1252 OR
1253 ( p_bom_header_rec.transaction_type = Bom_Globals.G_OPR_CREATE AND
1254 p_bom_head_unexp_rec.source_bill_sequence_id IS NOT NULL AND
1255 p_bom_head_unexp_rec.source_bill_sequence_id <> FND_API.G_MISS_NUM
1256 )
1257 THEN
1258 BEGIN
1259 SELECT 1
1260 INTO l_count
1261 FROM mtl_system_items msi1, mtl_system_items msi2
1262 WHERE
1263 --Bug 2217522 msi1.organization_id = p_bom_head_unexp_rec.common_organization_id
1264 msi1.organization_id = p_bom_head_unexp_rec.organization_id --Bug 2217522
1265 AND msi1.inventory_item_id = p_bom_head_unexp_rec.assembly_item_id
1266 AND msi2.organization_id = p_bom_head_unexp_rec.common_organization_id
1267 AND msi2.inventory_item_id=p_bom_head_unexp_rec.common_assembly_item_id
1268 AND msi2.bom_enabled_flag = 'Y'
1269 AND msi1.bom_item_type = msi2.bom_item_type
1270 AND msi1.pick_components_flag = msi2.pick_components_flag
1271 AND msi1.replenish_to_order_flag = msi2.replenish_to_order_flag
1272 AND msi1.effectivity_control = msi2.effectivity_control;
1273 --Commoning should happen within the eff ctrl.
1274 EXCEPTION
1275 WHEN no_data_found THEN
1276 l_return_status := FND_API.G_RET_STS_ERROR;
1277 l_token_tbl.DELETE;
1278 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
1279 l_token_tbl(1).token_value := p_bom_header_rec.assembly_item_name;
1280 l_token_tbl(2).token_name := 'COMMON_ORG_CODE';
1281 l_token_tbl(2).token_value :=
1282 p_bom_header_rec.common_organization_code;
1283 l_token_tbl(3).token_name := 'ORG_CODE';
1284 l_token_tbl(3).token_value := p_bom_header_rec.organization_code;
1285
1286 Error_Handler.Add_Error_Token
1287 ( p_message_name => 'BOM_COMMON_ATOPTO_MISMATCH'
1288 , p_token_tbl => l_token_tbl
1289 , p_mesg_token_tbl => l_mesg_token_tbl
1290 , x_mesg_token_tbl => l_mesg_token_tbl
1291 );
1292 END;
1293 END IF;
1294
1295
1296 x_return_status := l_return_status;
1297 x_mesg_token_tbl := l_mesg_token_tbl;
1298
1299 END Check_Entity;
1300
1301
1302 PROCEDURE Check_Entity_Delete
1303 ( x_return_status IN OUT NOCOPY VARCHAR2
1304 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1305 , p_bom_header_rec IN Bom_Bo_Pub.Bom_Head_Rec_Type
1306 , p_bom_head_Unexp_Rec IN Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
1307 , x_bom_head_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
1308 )
1309 IS
1310 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
1311 l_bom_head_unexp_rec Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
1312 := p_bom_head_Unexp_Rec;
1313 Cursor CheckGroup is
1314 SELECT description,
1315 delete_group_sequence_id,
1316 delete_type
1317 FROM bom_delete_groups
1318 WHERE delete_group_name = p_bom_header_rec.Delete_Group_Name
1319 AND organization_id = p_bom_head_Unexp_Rec.organization_id;
1320
1321 BEGIN
1322 x_return_status := FND_API.G_RET_STS_SUCCESS;
1323 x_bom_head_unexp_rec := p_bom_head_unexp_rec;
1324
1325 IF p_bom_header_rec.Delete_Group_Name IS NULL OR
1326 p_bom_header_rec.Delete_Group_Name = FND_API.G_MISS_CHAR
1327 THEN
1328 Error_Handler.Add_Error_Token
1329 ( p_message_name => 'BOM_DG_NAME_MISSING'
1330 , p_mesg_token_tbl => l_mesg_token_tbl
1331 , x_mesg_token_tbl => x_mesg_token_tbl
1332 );
1333 x_return_status := FND_API.G_RET_STS_ERROR;
1334 RETURN;
1335 END IF;
1336
1337 For c_CheckGroup in CheckGroup
1338 LOOP
1339 If c_CheckGroup.delete_type <> 2 /* Bill */ then
1340 Error_Handler.Add_Error_Token
1341 ( p_message_name => 'BOM_DUPLICATE_DELETE_GROUP'
1342 , p_mesg_token_tbl=>l_mesg_token_tbl
1343 , x_mesg_token_tbl=>x_mesg_token_tbl
1344 );
1345 x_return_status := FND_API.G_RET_STS_ERROR;
1346 RETURN;
1347 End if;
1348
1349
1350 l_bom_head_unexp_rec.DG_description :=
1351 c_Checkgroup.description;
1352 l_bom_head_unexp_rec.DG_sequence_id :=
1353 c_Checkgroup.delete_group_sequence_id;
1354
1355 RETURN;
1356
1357 END LOOP;
1358
1359 IF l_bom_head_unexp_rec.DG_sequence_id IS NULL
1360 THEN
1361 Error_Handler.Add_Error_Token
1362 ( p_message_name => 'NEW_DELETE_GROUP'
1363 , p_message_type => 'W'
1364 , p_mesg_token_tbl => l_mesg_token_tbl
1365 , x_mesg_token_tbl => x_mesg_token_tbl
1366 );
1367
1368 l_bom_head_unexp_rec.DG_new := TRUE;
1369 l_bom_head_unexp_rec.DG_description :=
1370 p_bom_header_rec.DG_description;
1371 END IF;
1372
1373
1374 -- Return the unexposed record
1375 x_bom_head_unexp_rec := l_bom_head_unexp_rec;
1376
1377 END Check_Entity_Delete;
1378
1379
1380 END Bom_Validate_Bom_Header;