1 PACKAGE BODY ENG_Validate_Ref_Designator
2 /* $Header: ENGLRFDB.pls 115.16 2002/12/13 00:12:46 bbontemp ship $ */
3 AS
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENG_Validate_Ref_Designator';
5 ret_code NUMBER;
6 l_dummy VARCHAR2(80);
7
8
9 /*************************************************************************
10 * Local Procedure: Calculate_both_totals
11 * Parameter IN : old_component_sequenc_id
12 * Parameters OUT : Total Quantity of Designators
13 * Purpose : Procedure calculate_both_totals will take the component
14 * sequence_id and calculate the number of designators that
15 * already exist for it and the how many exist on the same
16 * component on the ECO with an acd_type of add or disable
17 * Then by making use of the set operater it will eliminate
18 * the disable one's from the list. This is the quantity
19 * of designator that will remain on the component after
20 * implementation and is returned by the procedure as
21 * Total Quantity.
22 **************************************************************************/
23 Procedure Calculate_Both_Totals( p_old_component_sequence_id IN NUMBER,
24 x_TotalQuantity OUT NOCOPY NUMBER
25 )
26 IS
27
28 X_OldComp number;
29 X_Add constant number := 1;
30 X_Delete constant number := 3;
31 l_Implemented_Count NUMBER;
32 l_dummy varchar2(80);
33
34 Cursor GetTotalQty is
35 Select brd.component_reference_designator
36 From bom_reference_designators brd
37 Where brd.component_sequence_id = p_old_component_sequence_id
38 And nvl(brd.acd_type, X_Add) = X_Add
39 Union
40 Select brd.component_reference_designator
41 From bom_reference_designators brd,
42 bom_inventory_components bic
43 Where DECODE(bic.old_component_sequence_id, NULL,
44 bic.component_sequence_id,
45 bic.old_component_sequence_id) = p_old_component_sequence_id
46 And bic.component_sequence_id = brd.component_sequence_id
47 And bic.implementation_date is null
48 And brd.acd_type = X_Add
49 Minus
50 Select brd.component_reference_designator
51 From bom_reference_designators brd,
52 bom_inventory_components bic
53 Where DECODE(bic.old_component_sequence_id, NULL,
54 bic.component_sequence_id,
55 bic.old_component_sequence_id) = p_old_component_sequence_id
56 And bic.component_sequence_id = brd.component_sequence_id
57 And bic.implementation_date is null
58 And brd.acd_type = X_Delete;
59
60 BEGIN
61 For X_Designators in GetTotalQty loop
62 X_TotalQuantity := GetTotalQty%rowcount;
63 RETURN;
64 End loop;
65
66 -- Else return 0
67 X_TotalQuantity := 0;
68
69 END Calculate_Both_Totals;
70
71 /********************************************************************
72 *
73 * Procedure : Check_Entity
74 * Parameters IN : Reference Designator Record as given by the User
75 * Reference Designator Unexposed Record
76 * Parameters OUT: Return_Status - Indicating success or faliure
77 * Mesg_Token_Tbl - Filled with any errors or warnings
78 * Purpose : Entity validate procedure will execute the business
79 * validations for the referenced designator entity
80 * Any errors are loaded in the Mesg_Token_Tbl and
81 * a return status value is set.
82 ********************************************************************/
83
84 PROCEDURE Check_Entity
85 ( x_return_status OUT NOCOPY VARCHAR2
86 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
87 , p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
88 , p_Ref_Desg_Unexp_Rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
89 )
90 IS
91 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
92 l_ref_qty NUMBER := 0;
93 l_component_qty NUMBER;
94 l_dummy VARCHAR(80);
95 l_processed BOOLEAN;
96 l_quantity NUMBER;
97 l_change NUMBER := 0;
98 l_component_seq_id NUMBER := 0;
99 l_token_tbl Error_Handler.Token_Tbl_Type;
100 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
101 l_Err_text VARCHAR2(2000);
102
103 CURSOR c_acdtype IS
104 SELECT acd_type, old_component_sequence_id
105 FROM bom_inventory_components bic
106 WHERE bic.component_sequence_id =
107 p_Ref_Desg_Unexp_Rec.component_sequence_id;
108
109 CURSOR c_QuantityRelated IS
110 SELECT component_quantity
111 FROM bom_inventory_components
112 WHERE component_sequence_id =
113 p_Ref_Desg_Unexp_rec.component_sequence_id
114 AND quantity_related = 1;
115 BEGIN
116
117 --dbms_output.put_line('Entity Validation for Ref. Desgs begins . . .');
118
119 -- The ECO can be updated but a warning needs to be generated and
120 -- scheduled revised items need to be update to Open
121 -- and the ECO status need to be changed to Not Submitted for Approval
122
123 BOM_GLOBALS.Check_Approved_For_Process
124 ( p_change_notice => p_ref_designator_rec.Eco_Name,
125 p_organization_id => p_ref_desg_Unexp_rec.organization_id,
126 x_processed => l_processed,
127 x_err_text => l_err_text
128 );
129
130 IF l_processed = TRUE THEN
131 -- If the above process returns true then set the ECO approval.
132
133 BEGIN
134 BOM_GLOBALS.Set_Request_For_Approval
135 ( p_change_notice => p_ref_designator_rec.Eco_Name,
136 p_organization_id => p_ref_desg_Unexp_rec.organization_id,
137 x_err_text => l_err_text
138 );
139
140 EXCEPTION
141 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
142 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
143 END;
144
145 END IF;
146
147 --dbms_output.put_line('Verified if process exists . . .');
148
149 /**********************************************************************
150 *
151 * If the Transaction Type is CREATE and the ACD_Type = Disable, then
152 * the reference designator should already exist for the revised
153 * component.
154 *
155 ***********************************************************************/
156 IF p_ref_designator_rec.acd_type = 3 THEN
157 BEGIN
158 SELECT 1
159 INTO l_dummy
160 FROM bom_inventory_components
161 WHERE component_sequence_id =
162 p_ref_desg_unexp_rec.component_sequence_id
163 AND implementation_date IS NOT NULL
164 AND exists ( SELECT 1
165 FROM bom_reference_designators
166 WHERE component_sequence_id =
167 p_ref_desg_unexp_rec.component_sequence_id
168 AND component_reference_designator =
169 p_ref_designator_rec.reference_designator_name
170 AND acd_type = 1
171 );
172
173 EXCEPTION
174 WHEN NO_DATA_FOUND THEN
175 -- It means that the reference designator does not
176 -- exist on the revised component or it is probably
177 -- not implemented yet.
178
179 l_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
180 l_Token_Tbl(1).Token_Value :=
181 p_Ref_Designator_Rec.component_item_name;
182
183 l_token_tbl(2).token_name :=
184 'REFERENCE_DESIGNATOR_NAME';
185 l_token_tbl(2).token_value :=
186 p_ref_designator_rec.reference_designator_name;
187
188 Error_Handler.Add_Error_Token
189 ( x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
190 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
191 , p_message_name => 'ENG_DISABLE_DESG_NOT_FOUND'
192 , p_Token_Tbl => l_Token_Tbl
193 );
194
195 l_return_status := FND_API.G_RET_STS_ERROR;
196 END;
197
198 END IF;
199
200 /************************************************************************
201 * Check if ACD_Type of component is ADD then ref. desg is also add.
202 * no need for a exception as validity of component_sequence_id is already
203 * checked for.
204 *************************************************************************/
205
206 FOR acd IN c_acdtype LOOP
207 --
208 -- If the component has an ACD_Type of ADD then ref. Desg must also
209 -- be ADD
210 --
211 IF acd.acd_type = 1 /* ADD */ AND
212 p_ref_designator_rec.acd_type <> 1
213 THEN
214 l_return_status := FND_API.G_RET_STS_ERROR;
215
216 l_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
217 l_Token_Tbl(1).Token_Value :=
218 p_Ref_Designator_Rec.component_item_name;
219
220 Error_Handler.Add_Error_Token
221 ( x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
222 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
223 , p_message_name => 'ENG_RFD_ACD_NOT_COMPATIBLE'
224 , p_Token_Tbl => l_Token_Tbl
225 );
226 END IF;
227 END LOOP;
228
229 --dbms_output.put_line('Verified Compatible ACD Types . . .');
230
231 /************************************************************************
232 * If the Transaction Type is CREATE and the ACD_type is ADD then check the
233 * type of item to which a ref. designator is being added. Planning bills
234 * cannot have ref. desgs and also components which are not Standard cannot
235 * have ref. desgs. This OR so even if either exists Ref. Designators cannot
236 * be added.
237 *************************************************************************/
238
239 BEGIN
240 SELECT 'Non-Standard Comp'
241 INTO l_dummy
242 FROM bom_inventory_Components bic
243 WHERE bic.component_sequence_id =
244 p_Ref_Desg_Unexp_Rec.component_sequence_id
245 AND bic.bom_item_type in (1, 2, 3); /*MODEL,OPTION CLASS,PLANNING*/
246
247 -- If no exception is raised then
248 -- Generate an error saying that the component is non-standard.
249
250 l_return_status := FND_API.G_RET_STS_ERROR;
251
252 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
253 THEN
254 Error_Handler.Add_Error_Token
255 ( x_Mesg_Token_tbl => l_Mesg_Token_Tbl
256 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
257 , p_message_name => 'ENG_RFD_NON_STD_PARENT'
258 );
259 END IF;
260
261 EXCEPTION
262 WHEN NO_DATA_FOUND THEN
263 -- do nothing
264 NULL;
265 WHEN OTHERS THEN
266 --dbms_output.put_line
267 -- ('Unexpected error in Checking Planning Item ' ||
268 -- SQLERRM
269 -- );
270
271 Error_Handler.Add_Error_Token
272 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
273 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
274 , p_message_text => 'ERROR in Entity validation '
275 || SUBSTR(SQLERRM, 1, 30) ||
276 ' ' || to_char(SQLCODE)
277 , p_message_name => NULL
278 );
279
280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
281
282 END; /* End Checking for non-standard component */
283
284 BEGIN
285 SELECT 'Planning Bill'
286 INTO l_dummy
287 FROM sys.dual
288 WHERE EXISTS (SELECT 'Planning Item'
289 FROM bom_bill_of_materials bom,
290 mtl_system_items msi,
291 bom_inventory_components bic
292 WHERE msi.bom_item_type = 3 /* PLANNING */
293 AND msi.inventory_item_id = bom.assembly_item_id
294 AND msi.organization_id = bom.organization_id
295 AND bom.bill_sequence_id = bic.bill_sequence_id
296 AND bic.component_sequence_id =
297 p_Ref_Desg_Unexp_Rec.Component_sequence_id
298 );
299
300 -- If a record is found, then log an error because of the above
301 -- mentioned comment.
302 l_return_status := FND_API.G_RET_STS_ERROR;
303
304 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
305 THEN
306 Error_Handler.Add_Error_Token
307 ( x_Mesg_Token_tbl => l_Mesg_Token_Tbl
308 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
309 , p_message_name => 'ENG_RFD_PLANNING_BILL'
310 );
311 END IF;
312
313 EXCEPTION
314 WHEN NO_DATA_FOUND THEN
315 NULL; -- Do nothing
316 WHEN OTHERS THEN
317 --dbms_output.put_line
318 --('Unexpected error in Checking Planning Item ' ||
319 -- SQLERRM
320 -- );
321
322 Error_Handler.Add_Error_Token
323 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
324 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
325 , p_message_text => 'ERROR in Entity validation '
326 || SUBSTR(SQLERRM, 1, 30) ||
327 ' ' || to_char(SQLCODE)
328 , p_message_name => NULL
329 );
330
331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
332 END; /* End Checking for Planning Parent */
333
334
335 --dbms_output.put_line('Verified Component not planning . . .');
336
337 /************************************************************************
338 * If a ref. designator is being added or deleted, and if the Quantity
339 * Related is 1 then their must be a check that the number of designators
340 * is equal to the component_quantity.
341 *************************************************************************/
342 IF (p_ref_designator_rec.Transaction_Type = BOM_GLOBALS.G_OPR_CREATE) THEN
343 BEGIN
344
345 OPEN c_QuantityRelated;
346 FETCH c_QuantityRelated INTO l_Quantity;
347
348 IF c_QuantityRelated%FOUND THEN
349
350 /*****************************************************************
351 --
352 -- If no exception is raised then validate the actual quantity of
353 -- ref. designators with respect to the component quantity
354 -- If the designators are not equal, then generate a WARNING.
355 --
356 -- If the component to which the reference designator is added is a
357 -- CREATE/ADD then the reference designator must all have CREATE/ADD,
358 -- in this only the reference designators on the current component
359 -- need to be considered, therefore the old component sequence_id is
360 -- same as component sequence id. But, if the parent component is
361 -- CREATE/CHANGE, then ref. designators to the old and new
362 -- component should be considered
363 ******************************************************************/
364
365 FOR acd IN c_acdtype LOOP
366 IF acd.acd_type = 2 /* CHANGE */
367 THEN
368 Calculate_Both_Totals
369 ( p_old_component_sequence_id =>
370 acd.old_component_sequence_id
371 , x_TotalQuantity => l_ref_qty
372 );
373 ELSE
374 Calculate_Both_Totals
375 ( p_old_component_sequence_id =>
376 p_ref_desg_Unexp_rec.component_sequence_id
377 , x_TotalQuantity => l_ref_qty);
378 END IF;
379 END LOOP;
380
381 --dbms_output.put_line('Ref. Desg Qty: ' || to_char(l_ref_qty));
382 --dbms_output.put_line('Quantity: ' || to_char(l_quantity));
383
384
385 /***************************************************************
386 -- Since the Component Quantity is Mandatory is must have been
387 -- validated prior to this call or it must have been defaulted
388 -- to 1 if the user has not supplied a value.
389 ****************************************************************/
390
391 IF (p_ref_designator_rec.acd_type = 1) THEN
392 l_change := 1;
393 ELSIF (p_ref_designator_rec.acd_type = 3) THEN
394 l_change := -1;
395 END IF;
396
397 IF l_quantity <> l_ref_qty + l_change THEN
398 -- Log a warning but do not set the error status
399 l_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
400 l_token_tbl(1).token_value :=
401 p_ref_designator_rec.component_item_name;
402 Error_Handler.Add_Error_Token
403 ( x_Mesg_Token_Tbl => l_Mesg_Token_tbl
404 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
405 , p_message_name => 'ENG_QUANTITY_RELATED_INVALID'
406 , p_token_tbl => l_token_tbl
407 );
408 END IF;
409
410 END IF;
411 CLOSE c_QuantityRelated;
412 END; /* operation = CREATE ENDS */
413 END IF; /* If Operation = CREATE ENDS */
414
415 --dbms_output.put_line('Verified Quantity Related . . .');
416
417 -- Done validating entity
418
419 x_return_status := l_return_status;
420 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
421
422 EXCEPTION
423
424 WHEN FND_API.G_EXC_ERROR THEN
425
426 --dbms_output.put_line('Expected Error in Ref Desgs. Entity Validation ');
427
428 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
429 x_return_status := FND_API.G_RET_STS_ERROR;
430
431 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
432
433 --dbms_output.put_line('UNExpected Error in Ref. Desgs Entity Validation ');
434 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
436
437 WHEN OTHERS THEN
438 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
440
441 END Check_Entity;
442
443 /********************************************************************
444 *
445 * Procedure : Check_Attributes
446 * Parameters IN : Reference Designator Record as given by the User
447 * Parameters OUT: Return_Status - Indicating success or faliure
448 * Mesg_Token_Tbl - Filled with any errors or warnings
449 * Purpose : Attribute validation will validate individual attributes
450 * and any errors will be populated in the Mesg_Token_Tbl
451 * and returned with a return_status.
452 ********************************************************************/
453
454 PROCEDURE Check_Attributes
455 ( x_return_status OUT NOCOPY VARCHAR2
456 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
457 , p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
458 )
459 IS
460 l_token_tbl Error_Handler.Token_tbl_Type;
461 l_Mesg_token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
462 BEGIN
463
464 x_return_status := FND_API.G_RET_STS_SUCCESS;
465
466 -- Validate ref_designator attributes
467
468 --dbms_output.put_line('Attribute Validation Starts . . . ');
469
470 IF p_ref_designator_rec.acd_type IS NOT NULL AND
471 p_ref_designator_rec.acd_type NOT IN(1, 3)
472 THEN
473 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
474 THEN
475 l_token_tbl(1).token_name := 'REF_DESG';
476 l_token_tbl(1).token_value := 'ENG_REF_DESG_CAP';
477 l_token_tbl(1).translate := TRUE;
478
479 Error_Handler.Add_Error_Token
480 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
481 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
482 , p_message_name => 'ENG_RFD_SBC_ACD_TYPE_INVALID'
483 , p_token_tbl => l_token_tbl
484 );
485 END IF;
486 x_return_status := FND_API.G_RET_STS_ERROR;
487 END IF;
488
489 --dbms_output.put_line('After ACD_TYPE . . .');
490
491 -- Done validating attributes
492 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
493
494 EXCEPTION
495
496 WHEN FND_API.G_EXC_ERROR THEN
497
498 --dbms_output.put_line('Expected Error in Ref. Desgs Attr Validation . . .');
499 x_Mesg_token_Tbl := l_Mesg_token_Tbl;
500 x_return_status := FND_API.G_RET_STS_ERROR;
501
502 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
503
504 --dbms_output.put_line('UNExpected Error in Ref. Desgs Attr Validation . . .');
505 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
507
508 WHEN OTHERS THEN
509 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
510 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
511
512 END Check_Attributes;
513
514 /********************************************************************
515 *
516 * Procedure : Check_Entity_Delete
517 * Parameters IN : Reference Designator Record as given by the User
518 * Reference Designator Unexposed Record
519 * Parameters OUT: Return_Status - Indicating success or faliure
520 * Mesg_Token_Tbl - Filled with any errors or warnings
521 * Purpose : Entity Delete procedure will verify if the entity can
522 * can be deleted without violating any business rules.
523 * In case of errors the Mesg_token_Tbl is populated and
524 * process return with a status other than 'S'
525 * Warning will not prevent the entity from being deleted.
526 ********************************************************************/
527
528 PROCEDURE Check_Entity_Delete
529 ( x_return_status OUT NOCOPY VARCHAR2
530 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
531 , p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
532 , p_Ref_Desg_Unexp_Rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
533 )
534 IS
535 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
536 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
537
538 BEGIN
539
540 /***********************************************************************
541 -- If a Ref, Designator is being deleted and the Quantity Related for the
542 -- Component is 1 then a warning must be given if the the deletion is
543 -- going to make the number or designators <> Component Quantity.
544 ************************************************************************/
545 SELECT 'Related'
546 INTO l_dummy
547 FROM bom_inventory_components
548 WHERE quantity_related = 1
549 AND component_sequence_id =
550 p_ref_desg_Unexp_rec.component_sequence_id;
551
552
553 -- But the Quantity Related is 1 so log a warning
554 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
555 -- This ideally should be a warning
556 THEN
557 Error_Handler.Add_Error_Token
558 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
559 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
560 , p_message_name => 'ENG_QUANTITY_RELATED_INVALID'
561 );
562 END IF;
563
564 EXCEPTION
565 WHEN NO_DATA_FOUND THEN
566 NULL;
567
568 END Check_Entity_Delete;
569
570 /*****************************************************************************
571 * Procedure : Check_Existence
572 * Parameters IN : Refernce Designator exposed column record
573 * Refernce Designator unexposed column record
574 * Parameters OUT: Old Reference Designator exposed column record
575 * Old Reference Designator unexposed column record
576 * Mesg Token Table
577 * Return Status
578 * Purpose : Check_Existence will poerform a query using the primary key
579 * information and will return a success if the operation is
580 * CREATE and the record EXISTS or will return an
581 * error if the operation is UPDATE and the record DOES NOT
582 * EXIST.
583 * In case of UPDATE if the record exists then the procedure
584 * will return the old record in the old entity parameters
585 * with a success status.
586 ****************************************************************************/
587 PROCEDURE Check_Existence
588 ( p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
589 , p_ref_desg_unexp_rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
590 , x_old_ref_designator_rec IN OUT NOCOPY Bom_Bo_Pub.Ref_Designator_Rec_Type
591 , x_old_ref_desg_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
592 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
593 , x_Return_Status OUT NOCOPY VARCHAR2
594 )
595 IS
596 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
597 l_Return_Status VARCHAR2(1);
598 l_Token_Tbl Error_Handler.Token_Tbl_Type;
599 BEGIN
600 l_Token_Tbl(1).Token_Name := 'REFERENCE_DESIGNATOR_NAME';
601 l_Token_Tbl(1).Token_Value :=
602 p_ref_designator_rec.reference_designator_name;
603 l_token_tbl(2).token_name := 'REVISED_COMPONENT_NAME';
604 l_token_tbl(2).token_value := p_ref_designator_rec.component_item_name;
605
606 ENG_Ref_Designator_Util.Query_Row
607 ( p_ref_designator =>
608 p_ref_designator_rec.reference_designator_name
609 , p_component_sequence_id =>
610 p_ref_desg_unexp_rec.component_sequence_id
611 , p_acd_type => p_ref_designator_rec.acd_type
612 , x_Ref_Designator_Rec => x_old_ref_designator_rec
613 , x_Ref_Desg_Unexp_Rec => x_old_ref_desg_unexp_rec
614 , x_Return_Status => l_return_status
615 );
616
617 IF l_return_status = BOM_Globals.G_RECORD_FOUND AND
618 p_ref_designator_rec.transaction_type = BOM_Globals.G_OPR_CREATE
619 THEN
620 Error_Handler.Add_Error_Token
621 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
622 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
623 , p_message_name => 'ENG_REF_DESG_ALREADY_EXISTS'
624 , p_token_tbl => l_token_tbl
625 );
626 l_return_status := FND_API.G_RET_STS_ERROR;
627 ELSIF l_return_status = BOM_Globals.G_RECORD_NOT_FOUND AND
628 p_ref_designator_rec.transaction_type IN
629 (BOM_Globals.G_OPR_UPDATE, BOM_Globals.G_OPR_DELETE)
630 THEN
631 Error_Handler.Add_Error_Token
632 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
633 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
634 , p_message_name => 'ENG_REF_DESG_DOESNOT_EXIST'
635 , p_token_tbl => l_token_tbl
636 );
637 l_return_status := FND_API.G_RET_STS_ERROR;
638 ELSIF l_Return_status = FND_API.G_RET_STS_UNEXP_ERROR
639 THEN
640 Error_Handler.Add_Error_Token
641 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
642 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
643 , p_message_name => NULL
644 , p_message_text =>
645 'Unexpected error while existence verification of ' ||
646 'Reference Designator '||
647 p_ref_designator_rec.reference_designator_name
648 , p_token_tbl => l_token_tbl
649 );
650 ELSE
651 l_return_status := FND_API.G_RET_STS_SUCCESS;
652 END IF;
653
654 x_return_status := l_return_status;
655 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
656
657 END Check_Existence;
658
659 PROCEDURE Check_Lineage
660 ( p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
661 , p_ref_desg_unexp_rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
662 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
663 , x_Return_Status OUT NOCOPY VARCHAR2
664 )
665 IS
666 l_token_tbl Error_Handler.Token_Tbl_Type;
667 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
668
669 CURSOR c_GetComponent IS
670 SELECT revised_item_sequence_id
671 FROM bom_inventory_components
672 WHERE component_item_id= p_ref_desg_unexp_rec.component_item_id
673 AND operation_seq_num=p_ref_designator_rec.operation_sequence_number
674 AND effectivity_date = p_ref_designator_rec.start_effective_date
675 AND bill_sequence_id = p_ref_desg_unexp_rec.bill_sequence_id;
676 BEGIN
677 x_return_status := FND_API.G_RET_STS_SUCCESS;
678
679 FOR Component IN c_GetComponent LOOP
680 IF Component.revised_item_sequence_id <>
681 p_ref_desg_unexp_rec.revised_item_sequence_id
682 THEN
683 l_Token_Tbl(1).token_name :=
684 'REVISED_COMPONENT_NAME';
685 l_Token_Tbl(1).token_value :=
686 p_ref_designator_rec.component_item_name;
687 l_Token_Tbl(2).token_name :=
688 'REFERENCE_DESIGNATOR_NAME';
689 l_Token_Tbl(2).token_value :=
690 p_ref_designator_rec.reference_designator_name;
691 l_Token_Tbl(3).token_name :=
692 'REVISED_ITEM_NAME';
693 l_Token_Tbl(3).token_value :=
694 p_ref_designator_rec.revised_item_name;
695
696 Error_Handler.Add_Error_Token
697 ( p_Message_Name => 'ENG_REF_REV_ITEM_MISMATCH'
698 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
699 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
700 , p_Token_Tbl => l_Token_Tbl
701 );
702 x_return_status := FND_API.G_RET_STS_ERROR;
703 END IF;
704 END LOOP;
705
706 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
707
708 END CHECK_LINEAGE;
709
710 PROCEDURE CHECK_ACCESS
711 ( p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
712 , p_ref_desg_unexp_rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
713 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
714 , x_Return_Status OUT NOCOPY VARCHAR2
715 )
716 IS
717 l_return_status VARCHAR2(1);
718 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
719 l_dummy VARCHAR2(80);
720 l_token_tbl Error_Handler.Token_Tbl_Type;
721 BEGIN
722 l_return_status := FND_API.G_RET_STS_SUCCESS;
723
724 /************************************************************************
725 *
726 * If the parent component is having an ACD_type of Disable then cannot
727 * perform any operations on the reference designator.
728 *
729 ************************************************************************/
730 BEGIN
731 SELECT 'parent not disabled'
732 INTO l_dummy
733 FROM bom_inventory_components bic
734 WHERE bic.component_sequence_id =
735 p_Ref_Desg_Unexp_Rec.component_sequence_id
736 AND NVL(bic.acd_type, 0) <> 3;
737 EXCEPTION
738 WHEN NO_DATA_FOUND THEN
739 -- This means that the parent is disabled as
740 -- the record search was fired to get a parent
741 -- which is not disabled
742
743 l_token_Tbl(1).Token_Name := 'REF_DESG';
744 l_token_Tbl(1).Token_Value :=
745 p_Ref_Designator_Rec.Reference_Designator_Name;
746
747 Error_Handler.Add_Error_Token
748 ( x_Mesg_Token_tbl => l_Mesg_token_tbl
749 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
750 , p_message_name => 'ENG_RFD_COMP_ACD_TYPE_DISABLE'
751 , p_Token_Tbl => l_Token_Tbl
752 );
753 l_return_status := FND_API.G_RET_STS_ERROR;
754 WHEN OTHERS THEN
755 --This means that an unexpected error has occured
756 Error_Handler.Add_Error_Token
757 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
758 , p_Mesg_token_Tbl => l_Mesg_Token_Tbl
759 , p_message_name => NULL
760 , p_message_text => 'ERROR in Entity validation '
761 || SUBSTR(SQLERRM, 1, 240)
762 || ' ' || to_char(SQLCODE)
763 );
764
765 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
766 END;
767 x_return_status := l_return_status;
768 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
769
770 END CHECK_ACCESS;
771
772 END ENG_Validate_Ref_Designator;
[Home] [Help]