1 PACKAGE BODY BOM_Validate_Ref_Designator AS
2 /* $Header: BOMLRFDB.pls 120.10 2011/04/06 23:53:31 umajumde ship $ */
3 /**********************************************************************
4 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
5 -- All rights reserved.
6 --
7 -- FILENAME
8 --
9 -- BOMLRFDB.pls
10 --
11 -- DESCRIPTION
12 --
13 -- Body of package BOM_Validate_Ref_designators
14 --
15 -- NOTES
16 --
17 -- HISTORY
18 --
19 -- 19-JUL-1999 Rahul Chitko Initial Creation
20 --
21 -- 08-MAY-2001 Refai Farook EAM related changes
22 --
23 -- 05-JUL-2004 Hari Gelli Added Check_Quantity procedure
24 **************************************************************************/
25 G_PKG_NAME CONSTANT VARCHAR2(30) := 'BOM_Validate_Ref_Designator';
26 ret_code NUMBER;
27 l_dummy VARCHAR2(80);
28
29
30 /*************************************************************************
31 * Local Procedure: Calculate_both_totals
32 * Parameter IN : old_component_sequenc_id
33 * Parameters OUT : Total Quantity of Designators
34 * Purpose : Procedure calculate_both_totals will take the component
35 * sequence_id and calculate the number of designators that
36 * already exist for it and the how many exist on the same
37 * component on the ECO with an acd_type of add or disable
38 * Then by making use of the set operater it will eliminate
39 * the disable one's from the list. This is the quantity
40 * of designator that will remain on the component after
41 * implementation and is returned by the procedure as
42 * Total Quantity.
43 **************************************************************************/
44 Procedure Calculate_Both_Totals( p_old_component_sequence_id IN NUMBER,
45 x_TotalQuantity IN OUT NOCOPY NUMBER
46 )
47 IS
48
49 X_OldComp number;
50 X_Add constant number := 1;
51 X_Delete constant number := 3;
52 l_Implemented_Count NUMBER;
53 l_dummy varchar2(80);
54
55 Cursor GetTotalQty is
56 Select brd.component_reference_designator
57 From bom_reference_designators brd
58 Where brd.component_sequence_id = p_old_component_sequence_id
59 And nvl(brd.acd_type, X_Add) = X_Add
60 Union
61 Select brd.component_reference_designator
62 From bom_reference_designators brd,
63 bom_inventory_components bic
64 Where /* Bug 9346685 : Replaced decode with conditions
65 DECODE(bic.old_component_sequence_id, NULL,
66 bic.component_sequence_id,
67 bic.old_component_sequence_id) = p_old_component_sequence_id */
68 (bic.old_component_sequence_id = p_old_component_sequence_id
69 OR
70 (bic.old_component_sequence_id IS NULL
71 AND bic.component_sequence_id = p_old_component_sequence_id))
72 And bic.component_sequence_id = brd.component_sequence_id
73 And bic.implementation_date is null
74 And brd.acd_type = X_Add
75 Minus
76 Select brd.component_reference_designator
77 From bom_reference_designators brd,
78 bom_inventory_components bic
79 Where /* Bug 9346685 : Replaced decode with conditions
80 DECODE(bic.old_component_sequence_id, NULL,
81 bic.component_sequence_id,
82 bic.old_component_sequence_id) = p_old_component_sequence_id */
83 (bic.old_component_sequence_id = p_old_component_sequence_id
84 OR
85 (bic.old_component_sequence_id IS NULL
86 AND bic.component_sequence_id = p_old_component_sequence_id))
87 And bic.component_sequence_id = brd.component_sequence_id
88 And bic.implementation_date is null
89 And brd.acd_type = X_Delete;
90
91 BEGIN
92 IF (Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_BOM_BO) THEN
93 BEGIN
94 Select count(*)
95 Into X_TotalQuantity
96 From bom_reference_designators brd ,
97 bom_inventory_components bic
98 Where brd.component_sequence_id = p_old_component_sequence_id
99 And bic.component_sequence_id = brd.component_sequence_id
100 And bic.implementation_date is NOT NULL
101 And nvl(brd.acd_type, 1) = 1 ;
102
103 RETURN;
104 EXCEPTION
105 WHEN OTHERS THEN
106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
107 END;
108
109 ELSE
110 X_TotalQuantity :=0;
111 For X_Designators in GetTotalQty loop
112 X_TotalQuantity := X_TotalQuantity + 1;
113 End loop;
114 RETURN;
115 END IF;
116
117 -- Else return 0
118 X_TotalQuantity := 0;
119
120 END Calculate_Both_Totals;
121
122 /*************************************************************************
123 * Local Procedure: Check_Quantity
124 * Parameter IN : component_sequenc_id
125 * : component_item_name
126 * Parameters OUT : Error_Status
127 * Purpose : Procedure Check_Quantity will take the component
128 * sequence_id and checks if the quantity related is set to yes for
129 * the component and if it is yes then calculates the number of designators
130 * by calling the Calculate_both_totals and valiadtes the totals and
131 * send back the error status or success.
132 **************************************************************************/
133 PROCEDURE Check_Quantity
134 ( x_return_status IN OUT NOCOPY VARCHAR2
135 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
136 , p_component_sequence_id IN NUMBER
137 , p_component_item_name IN VARCHAR2
138 )
139 IS
140 l_ref_qty NUMBER := 0;
141 l_quantity NUMBER;
142 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
143
144 l_token_tbl Error_Handler.Token_Tbl_Type;
145 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
146
147 CURSOR c_QuantityRelated IS
148 SELECT component_quantity
149 FROM bom_inventory_components
150 WHERE component_sequence_id = p_component_sequence_id
151 AND quantity_related = 1;
152
153 CURSOR c_acdtype IS
154 SELECT acd_type, old_component_sequence_id
155 FROM bom_inventory_components bic
156 WHERE bic.component_sequence_id = p_component_sequence_id;
157 BEGIN
158
159 /*****************************************************************
160 --
161 -- If no exception is raised then validate the actual quantity of
162 -- ref. designators with respect to the component quantity
163 -- If the designators are not equal, then set error status.
164 ******************************************************************/
165
166 OPEN c_QuantityRelated;
167 FETCH c_QuantityRelated INTO l_Quantity;
168
169 IF c_QuantityRelated%FOUND THEN
170
171 FOR acd IN c_acdtype LOOP
172 IF acd.acd_type = 2 /* CHANGE */
173 THEN
174 Calculate_Both_Totals
175 ( p_old_component_sequence_id => acd.old_component_sequence_id
176 , x_TotalQuantity => l_ref_qty
177 );
178 ELSE
179 Calculate_Both_Totals
180 ( p_old_component_sequence_id => p_component_sequence_id
181 , x_TotalQuantity => l_ref_qty);
182 END IF;
183 END LOOP;
184
185 IF l_quantity <> l_ref_qty THEN
186 l_return_status := FND_API.G_RET_STS_ERROR;
187
188 l_token_tbl.delete ;
189 l_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
190 l_token_tbl(1).token_value := p_component_item_name;
191 Error_Handler.Add_Error_Token
192 ( x_Mesg_Token_Tbl => l_Mesg_Token_tbl
193 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
194 , p_message_name => 'BOM_QUANTITY_RELATED_INVALID'
195 , p_token_tbl => l_token_tbl
196 , p_message_type => 'W'
197 );
198 END IF;
199 END IF;
200 CLOSE c_QuantityRelated;
201
202 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
203 x_return_status := l_return_status;
204
205 END Check_Quantity;
206
207 /********************************************************************
208 *
209 * Procedure : Check_Entity
210 * Parameters IN : Reference Designator Record as given by the User
211 * Reference Designator Unexposed Record
212 * Parameters OUT: Return_Status - Indicating success or faliure
213 * Mesg_Token_Tbl - Filled with any errors or warnings
214 * Purpose : Entity validate procedure will execute the business
215 * validations for the referenced designator entity
216 * Any errors are loaded in the Mesg_Token_Tbl and
217 * a return status value is set.
218 ********************************************************************/
219
220 PROCEDURE Check_Entity
221 ( x_return_status IN OUT NOCOPY VARCHAR2
222 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
223 , p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
224 , p_Ref_Desg_Unexp_Rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
225 )
226 IS
227 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
228 l_temp_var NUMBER := 0;
229 l_ref_qty NUMBER := 0;
230 l_component_qty NUMBER;
231 l_dummy VARCHAR(80);
232 l_processed BOOLEAN;
233 l_quantity NUMBER;
234 l_change NUMBER := 0;
235 l_component_seq_id NUMBER := 0;
236 l_token_tbl Error_Handler.Token_Tbl_Type;
237 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
238 l_Err_text VARCHAR2(2000);
239 l_basis_type NUMBER;
240 l_assy_bom_enabled VARCHAR2(1);
241 l_parent_PTO_flag VARCHAR2(1);
242 l_rec_ATO_flag VARCHAR2(1);
243 l_rec_optional NUMBER;
244 l_rec_BIT NUMBER;
245 l_parent_BIT NUMBER;
246
247 CURSOR c_acdtype IS
248 SELECT acd_type, old_component_sequence_id
249 FROM bom_inventory_components bic
250 WHERE bic.component_sequence_id =
251 p_Ref_Desg_Unexp_Rec.component_sequence_id;
252
253 CURSOR c_QuantityRelated IS
254 SELECT component_quantity
255 FROM bom_inventory_components
256 WHERE component_sequence_id =
257 p_Ref_Desg_Unexp_rec.component_sequence_id
258 AND quantity_related = 1;
259 BEGIN
260
261 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Entity Validation for Ref. Desgs begins . . .'); END IF;
262 If bom_globals.get_debug = 'Y' then Error_Handler.write_debug('Ref Desg: Checking for editable common bill...'); END IF;
263 BEGIN
264 --validation for BOM ER #9946990
265 SELECT bic.bom_item_type,
266 msi.bom_item_type,
267 msi.pick_components_flag,
268 msi2.replenish_to_order_flag,
269 bic.optional
270 INTO l_rec_BIT,
271 l_parent_BIT,
272 l_parent_PTO_flag,
273 l_rec_ATO_flag,
274 l_rec_optional
275 FROM mtl_system_items msi,
276 mtl_system_items msi2,
277 bom_inventory_components bic,
278 bom_bill_of_materials bbom
279 WHERE msi.inventory_item_id = bbom.assembly_item_id
280 AND msi.organization_id = bbom.organization_id
281 AND msi2.inventory_item_id = bic.component_item_id
282 AND msi2.organization_id = bic.pk2_value
283 AND bic.component_sequence_id =
284 p_Ref_Desg_Unexp_rec.component_sequence_id
285 AND bic.bill_sequence_id = bbom.bill_sequence_id;
286 EXCEPTION
287 WHEN OTHERS THEN
288 NULL;
289 END;
290
291 --if a mandatory ATO item was added in a kit or pto model when the related profile was set, you cannot
292 --update or delete the component once the profile is unset (meaning you cannot insert, update
293 --or delete a substitute component on it
294
295 IF
296 nvl(fnd_profile.value('BOM:MANDATORY_ATO_IN_PTO'), 2) <> 1 AND
297 l_parent_BIT = Bom_Globals.G_STANDARD AND
298 l_parent_PTO_Flag = 'Y' AND
299 l_rec_ATO_Flag = 'Y' AND
300 l_rec_BIT = Bom_Globals.G_STANDARD
301 THEN
302 Error_Handler.Add_Error_Token
303 ( p_message_name => 'BOM_KIT_COMP_PRF_NOT_SET'
304 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
305 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
306 );
307 l_return_status := FND_API.G_RET_STS_ERROR;
308 END IF;
309
310
311
312
313 IF
314 nvl(fnd_profile.value('BOM:MANDATORY_ATO_IN_PTO'), 2) <> 1 AND
315 l_parent_BIT = Bom_Globals.G_MODEL AND
316 l_parent_PTO_Flag = 'Y' AND
317 l_rec_ATO_Flag = 'Y' AND
318 l_rec_BIT = Bom_Globals.G_STANDARD AND
319 l_rec_optional = 2
320 THEN
321 Error_Handler.Add_Error_Token
322 ( p_message_name => 'BOM_MODEL_COMP_PRF_NOT_SET'
323 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
324 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
325 );
326 l_return_status := FND_API.G_RET_STS_ERROR;
327 END IF;
328 --BOM ER #9946990 changes (end)
329
330 BEGIN
331 IF Bom_Globals.Get_Caller_Type <> 'MIGRATION' THEN
332 SELECT 1
333 INTO l_dummy
334 FROM bom_bill_of_materials
335 WHERE bill_sequence_id = source_bill_sequence_id
336 AND bill_sequence_id = p_ref_desg_unexp_rec.bill_sequence_id;
337 END IF;
338 EXCEPTION
339 WHEN NO_DATA_FOUND THEN
340 Error_Handler.Add_Error_Token
341 ( p_Message_Name => 'BOM_COMMON_REF_DESG'
342 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
343 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
344 , p_Token_Tbl => l_Token_Tbl
345 );
346 l_Return_Status := FND_API.G_RET_STS_ERROR;
347
348 END;
349 /* 4870173 */
350 select basis_type into l_basis_type from bom_components_b where component_sequence_id = p_Ref_Desg_Unexp_Rec.component_sequence_id;
351 If(l_Basis_type = 2) THEN
352 l_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
353 l_Token_Tbl(1).Token_Value := p_Ref_Designator_Rec.component_item_name;
354 Error_Handler.Add_Error_Token
355 ( x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
356 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
357 , p_message_name => 'BOM_LOT_BASED_RDS'
358 , p_Token_Tbl => l_Token_Tbl
359 );
360 l_return_status := FND_API.G_RET_STS_ERROR;
361 End if;
362
363 /*Check BOM enabled flag of the assembly*/
364 SELECT msi.bom_enabled_flag
365 INTO l_assy_bom_enabled
366 FROM mtl_system_items_b msi,
367 bom_bill_of_materials bbom
368 WHERE bbom.bill_sequence_id = p_Ref_Desg_Unexp_Rec.bill_sequence_id
369 AND bbom.assembly_item_id = msi.inventory_item_id
370 AND bbom.organization_id = msi.organization_id;
371
372 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Assy Bom Enabled flag : ' || l_assy_bom_enabled); END IF;
373
374 IF l_assy_bom_enabled <> 'Y'
375 THEN
376 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
377 l_token_tbl(1).token_name := 'REVISED_ITEM_NAME';
378 l_token_tbl(1).token_value :=
379 p_ref_designator_rec.Revised_Item_Name;
380 Error_Handler.Add_Error_Token
381 ( x_Mesg_Token_tbl => l_Mesg_Token_Tbl
382 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
383 , p_message_name => 'BOM_REV_ITEM_BOM_NOT_ENABLED'
384 , p_token_tbl => l_token_tbl
385 );
386 END IF;
387 l_return_status := FND_API.G_RET_STS_ERROR;
388 END IF;
389
390 -- The ECO can be updated but a warning needs to be generated and
391 -- scheduled revised items need to be update to Open
392 -- and the ECO status need to be changed to Not Submitted for Approval
393
394 IF Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_ECO_BO
395 THEN
396 Bom_GLOBALS.Check_Approved_For_Process
397 ( p_change_notice => p_ref_designator_rec.Eco_Name,
398 p_organization_id => p_ref_desg_Unexp_rec.organization_id,
399 x_processed => l_processed,
400 x_err_text => l_err_text
401 );
402
403 IF l_processed = TRUE THEN
404 -- If the above process returns true then set the ECO approval.
405
406 BEGIN
407 Bom_GLOBALS.Set_Request_For_Approval
408 ( p_change_notice => p_ref_designator_rec.Eco_Name,
409 p_organization_id => p_ref_desg_Unexp_rec.organization_id,
410 x_err_text => l_err_text
411 );
412
413 EXCEPTION
414 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
415 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
416 END;
417
418 END IF;
419 END IF;
420
421 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Verified if process exists . . .'); END IF;
422
423 /**********************************************************************
424 *
425 * If the Transaction Type is CREATE and the ACD_Type = Disable, then
426 * the reference designator should already exist for the revised
427 * component.
428 * This piece of code will not be executed by BOM BO since the acd_type is
429 * is not going to be 3 when the procedure is invoked from BOM BO.
430 ***********************************************************************/
431 IF p_ref_designator_rec.acd_type = 3 THEN
432 BEGIN
433
434 SELECT component_reference_designator
435 INTO l_dummy
436 FROM bom_reference_designators brd,
437 bom_inventory_components bic
438 WHERE bic.component_sequence_id =
439 p_ref_desg_unexp_rec.component_sequence_id
440 AND brd.component_sequence_id = bic.old_component_sequence_id
441 AND brd.component_reference_designator =
442 p_ref_designator_rec.reference_designator_name
443 AND NVL(brd.ACD_TYPE,1) <> 3; /* bug 9270000 */
444
445
446 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Ref Desigantor: ' || l_dummy); END IF;
447
448 EXCEPTION
449 WHEN NO_DATA_FOUND THEN
450 -- It means that the reference designator does not
451 -- exist on the revised component or it is probably
452 -- not implemented yet.
453
454 l_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
455 l_Token_Tbl(1).Token_Value :=
456 p_Ref_Designator_Rec.component_item_name;
457
458 l_token_tbl(2).token_name :=
459 'REFERENCE_DESIGNATOR_NAME';
460 l_token_tbl(2).token_value :=
461 p_ref_designator_rec.reference_designator_name;
462
463 Error_Handler.Add_Error_Token
464 ( x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
465 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
466 , p_message_name => 'BOM_DISABLE_DESG_NOT_FOUND'
467 , p_Token_Tbl => l_Token_Tbl
468 );
469
470 l_return_status := FND_API.G_RET_STS_ERROR;
471 END;
472
473 END IF;
474
475 /************************************************************************
476 * Check if ACD_Type of component is ADD then ref. desg is also add.
477 * no need for a exception as validity of component_sequence_id is already
478 * checked for.
479 *************************************************************************/
480
481 FOR acd IN c_acdtype LOOP
482 --
483 -- If the component has an ACD_Type of ADD then ref. Desg must also
484 -- be ADD
485 --
486 IF acd.acd_type = 1 /* ADD */ AND
487 p_ref_designator_rec.acd_type <> 1
488 THEN
489 l_return_status := FND_API.G_RET_STS_ERROR;
490
491
492 l_token_tbl.delete; -- Added by MK on 11/14/00
493 l_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
494 l_Token_Tbl(1).Token_Value :=
495 p_Ref_Designator_Rec.component_item_name;
496
497 Error_Handler.Add_Error_Token
498 ( x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
499 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
500 , p_message_name => 'BOM_RFD_ACD_NOT_COMPATIBLE'
501 , p_Token_Tbl => l_Token_Tbl
502 );
503 END IF;
504 END LOOP;
505
506 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Verified Compatible ACD Types . . .'); END IF;
507
508 /************************************************************************
509 * If the Transaction Type is CREATE and the ACD_type is ADD then check the
510 * type of item to which a ref. designator is being added. Planning bills
511 * cannot have ref. desgs and also components which are not Standard cannot
512 * have ref. desgs. This OR so even if either exists Ref. Designators cannot
513 * be added.
514 *************************************************************************/
515
516 BEGIN
517 SELECT 'Non-Standard Comp'
518 INTO l_dummy
519 FROM bom_inventory_Components bic
520 WHERE bic.component_sequence_id =
521 p_Ref_Desg_Unexp_Rec.component_sequence_id
522 AND bic.bom_item_type in (1, 2, 3); /*MODEL,OPTION CLASS,PLANNING*/
523
524 -- If no exception is raised then
525 -- Generate an error saying that the component is non-standard.
526
527 l_return_status := FND_API.G_RET_STS_ERROR;
528
529 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
530 THEN
531 Error_Handler.Add_Error_Token
532 ( x_Mesg_Token_tbl => l_Mesg_Token_Tbl
533 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
534 , p_message_name => 'BOM_RFD_NON_STD_PARENT'
535 );
536 END IF;
537
538 EXCEPTION
539 WHEN NO_DATA_FOUND THEN
540 -- do nothing
541 NULL;
542 WHEN OTHERS THEN
543 -- dbms_output.put_line
544 -- ('Unexpected error in Checking Planning Item ' ||
545 -- SQLERRM
546 -- );
547
548 Error_Handler.Add_Error_Token
549 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
550 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
551 , p_message_text => 'ERROR in Entity validation '
552 || SUBSTR(SQLERRM, 1, 30) ||
553 ' ' || to_char(SQLCODE)
554 , p_message_name => NULL
555 );
556
557 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
558
559 END; /* End Checking for non-standard component */
560
561 BEGIN
562 SELECT 'Planning Bill'
563 INTO l_dummy
564 FROM sys.dual
565 WHERE EXISTS (SELECT 'Planning Item'
566 FROM bom_bill_of_materials bom,
567 mtl_system_items msi,
568 bom_inventory_components bic
569 WHERE msi.bom_item_type = 3 /* PLANNING */
570 AND msi.inventory_item_id = bom.assembly_item_id
571 AND msi.organization_id = bom.organization_id
572 AND bom.bill_sequence_id = bic.bill_sequence_id
573 AND bic.component_sequence_id =
574 p_Ref_Desg_Unexp_Rec.Component_sequence_id
575 );
576
577 -- If a record is found, then log an error because of the above
578 -- mentioned comment.
579 l_return_status := FND_API.G_RET_STS_ERROR;
580
581 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
582 THEN
583 Error_Handler.Add_Error_Token
584 ( x_Mesg_Token_tbl => l_Mesg_Token_Tbl
585 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
586 , p_message_name => 'BOM_RFD_PLANNING_BILL'
587 );
588 END IF;
589
590 EXCEPTION
591 WHEN NO_DATA_FOUND THEN
592 NULL; -- Do nothing
593 WHEN OTHERS THEN
594 -- dbms_output.put_line
595 -- ('Unexpected error in Checking Planning Item ' ||
596 -- SQLERRM
597 -- );
598
599 Error_Handler.Add_Error_Token
600 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
601 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
602 , p_message_text => 'ERROR in Entity validation '
603 || SUBSTR(SQLERRM, 1, 30) ||
604 ' ' || to_char(SQLCODE)
605 , p_message_name => NULL
606 );
607
608 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
609 END; /* End Checking for Planning Parent */
610
611
612 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Verified Component not planning . . .'); END IF;
613
614 /************************************************************************
615 * If a ref. designator is being added or deleted, and if the Quantity
616 * Related is 1 then their must be a check that the number of designators
617 * is equal to the component_quantity.
618 *************************************************************************/
619 IF (p_ref_designator_rec.Transaction_Type = Bom_GLOBALS.G_OPR_CREATE) THEN
620 BEGIN
621
622 OPEN c_QuantityRelated;
623 FETCH c_QuantityRelated INTO l_Quantity;
624
625 --IF c_QuantityRelated%FOUND THEN
626 -- Bug No:3522842. For BOM BO Quantity Related validation will be done from Bom_Bo_Pvt.
627 IF c_QuantityRelated%FOUND AND Bom_Globals.Get_Bo_Identifier <> Bom_Globals.G_BOM_BO THEN
628
629 /*****************************************************************
630 --
631 -- If no exception is raised then validate the actual quantity of
632 -- ref. designators with respect to the component quantity
633 -- If the designators are not equal, then generate a WARNING.
634 --
635 -- If the component to which the reference designator is added is a
636 -- CREATE/ADD then the reference designator must all have CREATE/ADD,
637 -- in this only the reference designators on the current component
638 -- need to be considered, therefore the old component sequence_id is
639 -- same as component sequence id. But, if the parent component is
640 -- CREATE/CHANGE, then ref. designators to the old and new
641 -- component should be considered
642 ******************************************************************/
643
644 FOR acd IN c_acdtype LOOP
645 IF acd.acd_type = 2 /* CHANGE */
646 THEN
647 Calculate_Both_Totals
648 ( p_old_component_sequence_id =>
649 acd.old_component_sequence_id
650 , x_TotalQuantity => l_ref_qty
651 );
652 ELSE
653 Calculate_Both_Totals
654 ( p_old_component_sequence_id =>
655 p_ref_desg_Unexp_rec.component_sequence_id
656 , x_TotalQuantity => l_ref_qty);
657 END IF;
658 END LOOP;
659
660 -- dbms_output.put_line('Ref. Desg Qty: ' || to_char(l_ref_qty));
661 -- dbms_output.put_line('Quantity: ' || to_char(l_quantity));
662
663
664 /***************************************************************
665 -- Since the Component Quantity is Mandatory is must have been
666 -- validated prior to this call or it must have been defaulted
667 -- to 1 if the user has not supplied a value.
668 ****************************************************************/
669
670 IF (p_ref_designator_rec.acd_type = 1) THEN
671 l_change := 1;
672 ELSIF (p_ref_designator_rec.acd_type = 3) THEN
673 l_change := -1;
674 END IF;
675
676 IF l_quantity <> l_ref_qty + l_change THEN
677 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN --bug 12329790
678 -- Log a warning but do not set the error status
679 l_token_tbl.delete ; -- Added by MK on 11/14/00
680 l_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
681 l_token_tbl(1).token_value :=
682 p_ref_designator_rec.component_item_name;
683 Error_Handler.Add_Error_Token
684 ( x_Mesg_Token_Tbl => l_Mesg_Token_tbl
685 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
686 , p_message_name => 'BOM_QUANTITY_RELATED_INVALID'
687 , p_token_tbl => l_token_tbl
688 , p_message_type => 'W' -- Added by MK on 11/14/00
689 );
690 END IF;
691 END IF;
692 END IF;
693 CLOSE c_QuantityRelated;
694 END; /* operation = CREATE ENDS */
695 END IF; /* If Operation = CREATE ENDS */
696
697 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Verified Quantity Related . . .'); END IF;
698
699 -- Done validating entity
700
701 x_return_status := l_return_status;
702 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
703
704
705 /* When the Designator name is updated with New_designator, It should be checked that the new_desinator
706 does not exists already */
707
708 IF ( p_ref_designator_rec.new_reference_designator is not null
709 and p_ref_designator_rec.new_reference_designator <> FND_API.G_MISS_CHAR
710 and p_ref_designator_rec.transaction_type = Bom_Globals.G_OPR_UPDATE) THEN
711
712 select count(*) into l_temp_var
713 FROM BOM_REFERENCE_DESIGNATORS
714 WHERE COMPONENT_REFERENCE_DESIGNATOR = p_ref_designator_rec.new_reference_designator
715 AND COMPONENT_SEQUENCE_ID = p_Ref_Desg_Unexp_Rec.component_sequence_id
716 AND NVL(DECODE(ACD_TYPE, FND_API.G_MISS_NUM, null, acd_type), 0) =
717 NVL(DECODE(p_ref_designator_rec.acd_type, FND_API.G_MISS_NUM, null, p_ref_designator_rec.acd_type), 0) ;
718
719 IF (l_temp_var <>0) then
720
721 l_Token_Tbl(1).Token_Name := 'REFERENCE_DESIGNATOR_NAME';
722 l_Token_Tbl(1).Token_Value :=
723 p_ref_designator_rec.new_reference_designator;
724 l_token_tbl(2).token_name := 'REVISED_COMPONENT_NAME';
725 l_token_tbl(2).token_value := p_ref_designator_rec.component_item_name;
726
727 Error_Handler.Add_Error_Token
728 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
729 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
730 , p_message_name => 'BOM_REF_DESG_ALREADY_EXISTS'
731 , p_token_tbl => l_token_tbl
732 );
733 RAISE FND_API.G_EXC_ERROR;
734 END IF;
735 END IF;
736
737 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Verified New_designator ...'); END IF;
738
739 EXCEPTION
740
741 WHEN FND_API.G_EXC_ERROR THEN
742
743 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Expected Error in Ref Desgs. Entity Validation '); END IF;
744
745 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
746 x_return_status := FND_API.G_RET_STS_ERROR;
747
748 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
749
750 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('UNExpected Error in Ref. Desgs Entity Validation '); END IF;
751 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
752 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
753
754 WHEN OTHERS THEN
755 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
757
758 END Check_Entity;
759
760 /********************************************************************
761 *
762 * Procedure : Check_Attributes
763 * Parameters IN : Reference Designator Record as given by the User
764 * Parameters OUT: Return_Status - Indicating success or faliure
765 * Mesg_Token_Tbl - Filled with any errors or warnings
766 * Purpose : Attribute validation will validate individual attributes
767 * and any errors will be populated in the Mesg_Token_Tbl
768 * and returned with a return_status.
769 ********************************************************************/
770
771 PROCEDURE Check_Attributes
772 ( x_return_status IN OUT NOCOPY VARCHAR2
773 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
774 , p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
775 )
776 IS
777 l_token_tbl Error_Handler.Token_tbl_Type;
778 l_Mesg_token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
779 BEGIN
780
781 x_return_status := FND_API.G_RET_STS_SUCCESS;
782
783 -- Validate ref_designator attributes
784
785 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Attribute Validation Starts . . . '); END IF;
786
787 IF p_ref_designator_rec.acd_type IS NOT NULL AND
788 ( ( Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_ECO_BO AND
789 p_ref_designator_rec.acd_type NOT IN(1, 3)
790 ) OR
791 ( Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_BOM_BO AND
792 p_ref_designator_rec.acd_type IS NOT NULL OR
793 p_ref_designator_rec.acd_type <> FND_API.G_MISS_NUM
794 )
795 )
796 THEN
797 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
798 THEN
799 l_token_tbl(1).token_name := 'REF_DESG';
800 l_token_tbl(1).token_value := 'BOM_REF_DESG_CAP';
801 l_token_tbl(1).translate := TRUE;
802
803 Error_Handler.Add_Error_Token
804 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
805 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
806 , p_message_name => 'BOM_RFD_SBC_ACD_TYPE_INVALID'
807 , p_token_tbl => l_token_tbl
808 );
809 END IF;
810 x_return_status := FND_API.G_RET_STS_ERROR;
811 END IF;
812
813 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('After ACD_TYPE . . .'); END IF;
814
815 -- Done validating attributes
816 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
817
818 EXCEPTION
819
820 WHEN FND_API.G_EXC_ERROR THEN
821
822 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Expected Error in Ref. Desgs Attr Validation . . .'); END IF;
823 x_Mesg_token_Tbl := l_Mesg_token_Tbl;
824 x_return_status := FND_API.G_RET_STS_ERROR;
825
826 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
827
828 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('UNExpected Error in Ref. Desgs Attr Validation . . .'); END IF;
829 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
830 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
831
832 WHEN OTHERS THEN
833 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
834 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
835
836 END Check_Attributes;
837
838 /********************************************************************
839 *
840 * Procedure : Check_Entity_Delete
841 * Parameters IN : Reference Designator Record as given by the User
842 * Reference Designator Unexposed Record
843 * Parameters OUT: Return_Status - Indicating success or faliure
844 * Mesg_Token_Tbl - Filled with any errors or warnings
845 * Purpose : Entity Delete procedure will verify if the entity can
846 * can be deleted without violating any business rules.
847 * In case of errors the Mesg_token_Tbl is populated and
848 * process return with a status other than 'S'
849 * Warning will not prevent the entity from being deleted.
850 ********************************************************************/
851
852 PROCEDURE Check_Entity_Delete
853 ( x_return_status IN OUT NOCOPY VARCHAR2
854 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
855 , p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
856 , p_Ref_Desg_Unexp_Rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
857 )
858 IS
859 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
860 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
861 l_token_tbl Error_Handler.Token_Tbl_Type;
862 l_rec_BIT NUMBER;
863 l_parent_BIT NUMBER;
864 l_parent_PTO_flag VARCHAR2(1);
865 l_rec_ATO_flag VARCHAR2(1);
866 l_rec_optional NUMBER;
867 BEGIN
868
869
870 BEGIN
871 --validation for BOM ER
872 SELECT bic.bom_item_type,
873 msi.bom_item_type,
874 msi.pick_components_flag,
875 msi2.replenish_to_order_flag,
876 bic.optional
877 INTO l_rec_BIT,
878 l_parent_BIT,
879 l_parent_PTO_flag,
880 l_rec_ATO_flag,
881 l_rec_optional
882 FROM mtl_system_items msi,
883 mtl_system_items msi2,
884 bom_inventory_components bic,
885 bom_bill_of_materials bbom
886 WHERE msi.inventory_item_id = bbom.assembly_item_id
887 AND msi.organization_id = bbom.organization_id
888 AND msi2.inventory_item_id = bic.component_item_id
889 AND msi2.organization_id = bic.pk2_value
890 AND bic.component_sequence_id =
891 p_Ref_Desg_Unexp_rec.component_sequence_id
892 AND bic.bill_sequence_id = bbom.bill_sequence_id;
893 EXCEPTION
894 WHEN OTHERS THEN
895 NULL;
896 END;
897
898 IF
899 nvl(fnd_profile.value('BOM:MANDATORY_ATO_IN_PTO'), 2) <> 1 AND
900 l_parent_BIT = Bom_Globals.G_STANDARD AND
901 l_parent_PTO_Flag = 'Y' AND
902 l_rec_ATO_Flag = 'Y' AND
903 l_rec_BIT = Bom_Globals.G_STANDARD
904 THEN
905 Error_Handler.Add_Error_Token
906 ( p_message_name => 'BOM_KIT_COMP_PRF_NOT_SET'
907 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
908 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
909 );
910 l_return_status := FND_API.G_RET_STS_ERROR;
911 END IF;
912
913
914 --if a mandatory ATO item was added in a pto model when the related profile was set, you cannot
915 --update or delete the component once the profile is unset (meaning you cannot insert, update
916 --or delete a substitute component on it
917
918 IF
919 nvl(fnd_profile.value('BOM:MANDATORY_ATO_IN_PTO'), 2) <> 1 AND
920 l_parent_BIT = Bom_Globals.G_MODEL AND
921 l_parent_PTO_Flag = 'Y' AND
922 l_rec_ATO_Flag = 'Y' AND
923 l_rec_BIT = Bom_Globals.G_STANDARD AND
924 l_rec_optional = 2
925 THEN
926 Error_Handler.Add_Error_Token
927 ( p_message_name => 'BOM_MODEL_COMP_PRF_NOT_SET'
928 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
929 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
930 );
931 l_return_status := FND_API.G_RET_STS_ERROR;
932 END IF;
933 --BOM ER changes (end)
934
935
936 /***********************************************************************
937 -- If a Ref, Designator is being deleted and the Quantity Related for the
938 -- Component is 1 then a warning must be given if the the deletion is
939 -- going to make the number or designators <> Component Quantity.
940 ************************************************************************/
941 BEGIN
942 SELECT 'Related'
943 INTO l_dummy
944 FROM bom_inventory_components
945 WHERE quantity_related = 1
946 AND component_sequence_id =
947 p_ref_desg_Unexp_rec.component_sequence_id;
948
949
950 -- But the Quantity Related is 1 so log a warning
951 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
952 -- This ideally should be a warning
953 THEN
954 -- Added token by MK on 12/06/00
955 l_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
956 l_token_tbl(1).token_value :=
957 p_ref_designator_rec.component_item_name;
958
959 Error_Handler.Add_Error_Token
960 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
961 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
962 , p_message_name => 'BOM_QUANTITY_RELATED_INVALID'
963 , p_message_type => 'W'
964
965 );
966 END IF;
967
968 EXCEPTION
969 WHEN NO_DATA_FOUND THEN
970 NULL;
971 END ;
972
973 -- Added by MK on 11/14/00
974 x_return_status := l_return_status ;
975 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
976
977 END Check_Entity_Delete;
978
979 /*****************************************************************************
980 * Procedure : Check_Existence
981 * Parameters IN : Refernce Designator exposed column record
982 * Refernce Designator unexposed column record
983 * Parameters OUT: Old Reference Designator exposed column record
984 * Old Reference Designator unexposed column record
985 * Mesg Token Table
986 * Return Status
987 * Purpose : Check_Existence will poerform a query using the primary key
988 * information and will return a success if the operation is
989 * CREATE and the record EXISTS or will return an
990 * error if the operation is UPDATE and the record DOES NOT
991 * EXIST.
992 * In case of UPDATE if the record exists then the procedure
993 * will return the old record in the old entity parameters
994 * with a success status.
995 ****************************************************************************/
996 PROCEDURE Check_Existence
997 ( p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
998 , p_ref_desg_unexp_rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
999 , x_old_ref_designator_rec IN OUT NOCOPY Bom_Bo_Pub.Ref_Designator_Rec_Type
1000 , x_old_ref_desg_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
1001 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1002 , x_Return_Status IN OUT NOCOPY VARCHAR2
1003 )
1004 IS
1005 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1006 l_Return_Status VARCHAR2(1);
1007 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1008 BEGIN
1009 l_Token_Tbl(1).Token_Name := 'REFERENCE_DESIGNATOR_NAME';
1010 l_Token_Tbl(1).Token_Value :=
1011 p_ref_designator_rec.reference_designator_name;
1012 l_token_tbl(2).token_name := 'REVISED_COMPONENT_NAME';
1013 l_token_tbl(2).token_value := p_ref_designator_rec.component_item_name;
1014
1015 BOM_Ref_Designator_Util.Query_Row
1016 ( p_ref_designator =>
1017 p_ref_designator_rec.reference_designator_name
1018 , p_component_sequence_id =>
1019 p_ref_desg_unexp_rec.component_sequence_id
1020 , p_acd_type => p_ref_designator_rec.acd_type
1021 , x_Ref_Designator_Rec => x_old_ref_designator_rec
1022 , x_Ref_Desg_Unexp_Rec => x_old_ref_desg_unexp_rec
1023 , x_Return_Status => l_return_status
1024 );
1025
1026 IF l_return_status = Bom_Globals.G_RECORD_FOUND AND
1027 p_ref_designator_rec.transaction_type = Bom_Globals.G_OPR_CREATE
1028 THEN
1029 Error_Handler.Add_Error_Token
1030 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
1031 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1032 , p_message_name => 'BOM_REF_DESG_ALREADY_EXISTS'
1033 , p_token_tbl => l_token_tbl
1034 );
1035 l_return_status := FND_API.G_RET_STS_ERROR;
1036 ELSIF l_return_status = Bom_Globals.G_RECORD_NOT_FOUND AND
1037 p_ref_designator_rec.transaction_type IN
1038 (Bom_Globals.G_OPR_UPDATE, Bom_Globals.G_OPR_DELETE)
1039 THEN
1040 Error_Handler.Add_Error_Token
1041 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
1042 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1043 , p_message_name => 'BOM_REF_DESG_DOESNOT_EXIST'
1044 , p_token_tbl => l_token_tbl
1045 );
1046 l_return_status := FND_API.G_RET_STS_ERROR;
1047 ELSIF l_Return_status = FND_API.G_RET_STS_UNEXP_ERROR
1048 THEN
1049 Error_Handler.Add_Error_Token
1050 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
1051 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1052 , p_message_name => NULL
1053 , p_message_text =>
1054 'Unexpected error while existence verification of ' ||
1055 'Reference Designator '||
1056 p_ref_designator_rec.reference_designator_name
1057 , p_token_tbl => l_token_tbl
1058 );
1059 ELSE
1060
1061 /* Assign the relevant transaction type for SYNC operations */
1062
1063 IF p_ref_designator_rec.transaction_type = 'SYNC' THEN
1064 IF l_return_status = Bom_Globals.G_RECORD_FOUND THEN
1065 x_old_ref_designator_rec.transaction_type :=
1066 Bom_Globals.G_OPR_UPDATE;
1067 ELSE
1068 x_old_ref_designator_rec.transaction_type :=
1069 Bom_Globals.G_OPR_CREATE;
1070 END IF;
1071 END IF;
1072 l_return_status := FND_API.G_RET_STS_SUCCESS;
1073
1074 END IF;
1075
1076 x_return_status := l_return_status;
1077 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1078
1079 END Check_Existence;
1080
1081 PROCEDURE Check_Lineage
1082 ( p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
1083 , p_ref_desg_unexp_rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
1084 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1085 , x_Return_Status IN OUT NOCOPY VARCHAR2
1086 )
1087 IS
1088 l_token_tbl Error_Handler.Token_Tbl_Type;
1089 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1090
1091 CURSOR c_GetComponent IS
1092 SELECT revised_item_sequence_id
1093 FROM bom_inventory_components
1094 WHERE component_item_id= p_ref_desg_unexp_rec.component_item_id
1095 AND operation_seq_num=p_ref_designator_rec.operation_sequence_number
1096 AND effectivity_date = p_ref_designator_rec.start_effective_date
1097 AND bill_sequence_id = p_ref_desg_unexp_rec.bill_sequence_id;
1098 BEGIN
1099 x_return_status := FND_API.G_RET_STS_SUCCESS;
1100
1101 FOR Component IN c_GetComponent LOOP
1102 IF Component.revised_item_sequence_id <>
1103 p_ref_desg_unexp_rec.revised_item_sequence_id
1104 THEN
1105 l_Token_Tbl(1).token_name :=
1106 'REVISED_COMPONENT_NAME';
1107 l_Token_Tbl(1).token_value :=
1108 p_ref_designator_rec.component_item_name;
1109 l_Token_Tbl(2).token_name :=
1110 'REFERENCE_DESIGNATOR_NAME';
1111 l_Token_Tbl(2).token_value :=
1112 p_ref_designator_rec.reference_designator_name;
1113 l_Token_Tbl(3).token_name :=
1114 'REVISED_ITEM_NAME';
1115 l_Token_Tbl(3).token_value :=
1116 p_ref_designator_rec.revised_item_name;
1117
1118 Error_Handler.Add_Error_Token
1119 ( p_Message_Name => 'BOM_REF_REV_ITEM_MISMATCH'
1120 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1121 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1122 , p_Token_Tbl => l_Token_Tbl
1123 );
1124 x_return_status := FND_API.G_RET_STS_ERROR;
1125 END IF;
1126 END LOOP;
1127
1128 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1129
1130 END CHECK_LINEAGE;
1131
1132 PROCEDURE CHECK_ACCESS
1133 ( p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
1134 , p_ref_desg_unexp_rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
1135 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1136 , x_Return_Status IN OUT NOCOPY VARCHAR2
1137 )
1138 IS
1139 l_return_status VARCHAR2(1);
1140 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
1141 l_dummy VARCHAR2(80);
1142 l_token_tbl Error_Handler.Token_Tbl_Type;
1143 BEGIN
1144 l_return_status := FND_API.G_RET_STS_SUCCESS;
1145
1146 /************************************************************************
1147 *
1148 * If the parent component is having an ACD_type of Disable then cannot
1149 * perform any operations on the reference designator.
1150 *
1151 ************************************************************************/
1152 BEGIN
1153 SELECT 'parent not disabled'
1154 INTO l_dummy
1155 FROM bom_inventory_components bic
1156 WHERE bic.component_sequence_id =
1157 p_Ref_Desg_Unexp_Rec.component_sequence_id
1158 AND NVL(bic.acd_type, 0) <> 3;
1159 EXCEPTION
1160 WHEN NO_DATA_FOUND THEN
1161 -- This means that the parent is disabled as
1162 -- the record search was fired to get a parent
1163 -- which is not disabled
1164
1165 l_token_Tbl(1).Token_Name := 'REF_DESG';
1166 l_token_Tbl(1).Token_Value :=
1167 p_Ref_Designator_Rec.Reference_Designator_Name;
1168
1169 Error_Handler.Add_Error_Token
1170 ( x_Mesg_Token_tbl => l_Mesg_token_tbl
1171 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1172 , p_message_name => 'BOM_RFD_COMP_ACD_TYPE_DISABLE'
1173 , p_Token_Tbl => l_Token_Tbl
1174 );
1175 l_return_status := FND_API.G_RET_STS_ERROR;
1176 WHEN OTHERS THEN
1177 --This means that an unexpected error has occured
1178 Error_Handler.Add_Error_Token
1179 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
1180 , p_Mesg_token_Tbl => l_Mesg_Token_Tbl
1181 , p_message_name => NULL
1182 , p_message_text => 'ERROR in Entity validation '
1183 || SUBSTR(SQLERRM, 1, 240)
1184 || ' ' || to_char(SQLCODE)
1185 );
1186
1187 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1188 END;
1189 x_return_status := l_return_status;
1190 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1191
1192 END CHECK_ACCESS;
1193
1194 /*
1195 ** BOM Business Object procedure calls
1196 */
1197 PROCEDURE Check_Entity
1198 ( x_return_status IN OUT NOCOPY VARCHAR2
1199 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1200 , p_bom_ref_designator_rec IN Bom_Bo_Pub.Bom_Ref_Designator_Rec_Type
1201 , p_bom_Ref_Desg_Unexp_Rec IN Bom_Bo_Pub.Bom_Ref_Desg_Unexp_Rec_Type
1202 )
1203 IS
1204 l_ref_designator_rec Bom_Bo_Pub.Ref_Designator_rec_Type;
1205 l_ref_desg_unexp_rec Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type;
1206 BEGIN
1207 --Convert the BOM Record to ECO
1208
1209 Bom_Bo_Pub.Convert_Bomdesg_To_EcoDesg
1210 ( p_bom_ref_designator_rec => p_bom_ref_designator_rec
1211 , p_bom_ref_desg_unexp_rec => p_bom_ref_desg_unexp_rec
1212 , x_ref_designator_rec => l_Ref_designator_rec
1213 , x_ref_Desg_unexp_rec => l_ref_desg_unexp_rec
1214 );
1215
1216 -- Call Check Entity
1217 Bom_Validate_Ref_Designator.Check_Entity
1218 ( p_ref_designator_rec => l_ref_designator_rec
1219 , p_ref_desg_unexp_rec => l_Ref_Desg_unexp_rec
1220 , x_return_status => x_return_status
1221 , x_mesg_token_tbl => x_mesg_token_tbl
1222 );
1223
1224 END Check_Entity;
1225
1226 -- Procedure Attributes
1227
1228 PROCEDURE Check_Attributes
1229 ( x_return_status IN OUT NOCOPY VARCHAR2
1230 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1231 , p_Bom_ref_designator_rec IN Bom_Bo_Pub.Bom_Ref_Designator_Rec_Type
1232 )
1233 IS
1234 l_ref_designator_rec Bom_Bo_Pub.Ref_Designator_rec_Type;
1235 l_ref_desg_unexp_rec Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type;
1236 BEGIN
1237 --Convert the BOM Record to ECO
1238
1239 Bom_Bo_Pub.Convert_Bomdesg_To_EcoDesg
1240 ( p_bom_ref_designator_rec => p_bom_ref_designator_rec
1241 , x_ref_designator_rec => l_Ref_designator_rec
1242 , x_ref_Desg_unexp_rec => l_ref_desg_unexp_rec
1243 );
1244
1245 -- Call Check Entity
1246 Bom_Validate_Ref_Designator.Check_Attributes
1247 ( p_ref_designator_rec => l_ref_designator_rec
1248 , x_return_status => x_return_status
1249 , x_mesg_token_tbl => x_mesg_token_tbl
1250 );
1251
1252 END Check_Attributes;
1253
1254 -- Procedure Entity_Delete
1255
1256 PROCEDURE Check_Entity_Delete
1257 ( x_return_status IN OUT NOCOPY VARCHAR2
1258 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1259 , p_bom_ref_designator_rec IN Bom_Bo_Pub.Bom_Ref_Designator_Rec_Type
1260 , p_bom_Ref_Desg_Unexp_Rec IN Bom_Bo_Pub.Bom_Ref_Desg_Unexp_Rec_Type
1261 )
1262 IS
1263 l_ref_designator_rec Bom_Bo_Pub.Ref_Designator_rec_Type;
1264 l_ref_desg_unexp_rec Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type;
1265 BEGIN
1266 --Convert the BOM Record to ECO
1267
1268 Bom_Bo_Pub.Convert_Bomdesg_To_EcoDesg
1269 ( p_bom_ref_designator_rec => p_bom_ref_designator_rec
1270 , p_bom_ref_desg_unexp_rec => p_bom_ref_desg_unexp_rec
1271 , x_ref_designator_rec => l_Ref_designator_rec
1272 , x_ref_Desg_unexp_rec => l_ref_desg_unexp_rec
1273 );
1274
1275 -- Call Check Entity
1276 Bom_Validate_Ref_Designator.Check_Entity_Delete
1277 ( p_ref_designator_rec => l_ref_designator_rec
1278 , p_ref_desg_unexp_rec => l_Ref_Desg_unexp_rec
1279 , x_return_status => x_return_status
1280 , x_mesg_token_tbl => x_mesg_token_tbl
1281 );
1282
1283
1284 END Check_Entity_Delete;
1285
1286 PROCEDURE Check_Existence
1287 ( p_bom_ref_designator_rec IN Bom_Bo_Pub.Bom_Ref_Designator_Rec_Type
1288 , p_bom_ref_desg_unexp_rec IN Bom_Bo_Pub.Bom_Ref_Desg_Unexp_Rec_Type
1289 , x_old_bom_ref_designator_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Ref_Designator_Rec_Type
1290 , x_old_bom_ref_desg_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Ref_Desg_Unexp_Rec_Type
1291 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1292 , x_Return_Status IN OUT NOCOPY VARCHAR2
1293 )
1294 IS
1295 l_ref_designator_rec Bom_Bo_Pub.Ref_Designator_rec_Type;
1296 l_ref_desg_unexp_rec Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type;
1297 l_old_ref_designator_rec Bom_Bo_Pub.Ref_Designator_Rec_Type;
1298 l_old_ref_desg_unexp_rec Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type;
1299
1300 BEGIN
1301 --Convert the BOM Record to ECO
1302
1303 Bom_Bo_Pub.Convert_Bomdesg_To_EcoDesg
1304 ( p_bom_ref_designator_rec => p_bom_ref_designator_rec
1305 , p_bom_ref_desg_unexp_rec => p_bom_ref_desg_unexp_rec
1306 , x_ref_designator_rec => l_Ref_designator_rec
1307 , x_ref_Desg_unexp_rec => l_ref_desg_unexp_rec
1308 );
1309
1310 -- dbms_output.put_line('Before Check Existence . . . ');
1311 -- dbms_output.put_line('Component Sequence: ' || l_ref_desg_unexp_rec.component_sequence_id);
1312 -- dbms_output.put_line('Reference Designator ' || l_Ref_designator_rec.reference_designator_name);
1313
1314 -- Call Check Entity
1315 Bom_Validate_Ref_Designator.Check_Existence
1316 ( p_ref_designator_rec => l_ref_designator_rec
1317 , p_ref_desg_unexp_rec => l_Ref_Desg_unexp_rec
1318 , x_old_ref_designator_rec => l_old_ref_designator_rec
1319 , x_old_ref_desg_unexp_rec => l_old_ref_desg_unexp_rec
1320 , x_return_status => x_return_status
1321 , x_mesg_token_tbl => x_mesg_token_tbl
1322 );
1323
1324 -- Convert ECO Record to BOM before returning
1325 Bom_Bo_Pub.Convert_EcoDesg_To_BomDesg
1326 ( p_ref_designator_rec => l_old_ref_designator_rec
1327 , p_ref_desg_unexp_rec => l_old_ref_desg_unexp_rec
1328 , x_bom_ref_designator_rec => x_old_bom_ref_designator_rec
1329 , x_bom_ref_desg_unexp_rec => x_old_bom_ref_desg_unexp_rec
1330 );
1331
1332 END Check_Existence;
1333
1334 PROCEDURE Check_Lineage
1335 ( p_bom_ref_designator_rec IN Bom_Bo_Pub.bom_Ref_Designator_Rec_Type
1336 , p_bom_ref_desg_unexp_rec IN Bom_Bo_Pub.bom_Ref_Desg_Unexp_Rec_Type
1337 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1338 , x_Return_Status IN OUT NOCOPY VARCHAR2
1339 )
1340 IS
1341 l_ref_designator_rec Bom_Bo_Pub.Ref_Designator_rec_Type;
1342 l_ref_desg_unexp_rec Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type;
1343 BEGIN
1344 --Convert the BOM Record to ECO
1345
1346 Bom_Bo_Pub.Convert_Bomdesg_To_EcoDesg
1347 ( p_bom_ref_designator_rec => p_bom_ref_designator_rec
1348 , p_bom_ref_desg_unexp_rec => p_bom_ref_desg_unexp_rec
1349 , x_ref_designator_rec => l_Ref_designator_rec
1350 , x_ref_Desg_unexp_rec => l_ref_desg_unexp_rec
1351 );
1352
1353 -- Call Check Entity
1354 Bom_Validate_Ref_Designator.Check_Lineage
1355 ( p_ref_designator_rec => l_ref_designator_rec
1356 , p_ref_desg_unexp_rec => l_Ref_Desg_unexp_rec
1357 , x_return_status => x_return_status
1358 , x_mesg_token_tbl => x_mesg_token_tbl
1359 );
1360
1361 END Check_Lineage;
1362
1363 PROCEDURE CHECK_ACCESS
1364 ( p_bom_ref_designator_rec IN Bom_Bo_Pub.Bom_Ref_Designator_Rec_Type
1365 , p_bom_ref_desg_unexp_rec IN Bom_Bo_Pub.Bom_Ref_Desg_Unexp_Rec_Type
1366 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1367 , x_Return_Status IN OUT NOCOPY VARCHAR2
1368 )
1369 IS
1370 l_ref_designator_rec Bom_Bo_Pub.Ref_Designator_rec_Type;
1371 l_ref_desg_unexp_rec Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type;
1372 BEGIN
1373 --Convert the BOM Record to ECO
1374
1375 Bom_Bo_Pub.Convert_Bomdesg_To_EcoDesg
1376 ( p_bom_ref_designator_rec => p_bom_ref_designator_rec
1377 , p_bom_ref_desg_unexp_rec => p_bom_ref_desg_unexp_rec
1378 , x_ref_designator_rec => l_Ref_designator_rec
1379 , x_ref_Desg_unexp_rec => l_ref_desg_unexp_rec
1380 );
1381
1382 -- Call Check Access
1383 Bom_Validate_Ref_Designator.Check_Access
1384 ( p_ref_designator_rec => l_ref_designator_rec
1385 , p_ref_desg_unexp_rec => l_Ref_Desg_unexp_rec
1386 , x_return_status => x_return_status
1387 , x_mesg_token_tbl => x_mesg_token_tbl
1388 );
1389
1390 END Check_Access;
1391
1392
1393 END BOM_Validate_Ref_Designator;
[Home] [Help]