1 PACKAGE BODY ENG_Validate_Rev_Component AS
2 /* $Header: ENGLCMPB.pls 115.55 2002/12/13 00:13:19 bbontemp ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENG_Validate_Rev_Component';
7 l_dummy VARCHAR2(80);
8 l_MODEL CONSTANT NUMBER := 1;
9 l_OPTION_CLASS CONSTANT NUMBER := 2;
10 l_PLANNING CONSTANT NUMBER := 3;
11 l_STANDARD CONSTANT NUMBER := 4;
12 l_Sub_Locator_Control NUMBER;
13 l_locator_control NUMBER;
14 l_org_locator_control NUMBER;
15 l_item_locator_control NUMBER;
16 l_item_loc_restricted NUMBER; -- 1,if Locator is Restricted, else 2
17
18 g_Comp_Item_Type NUMBER; -- Bom_Item_Type of Component
19 g_Assy_Item_Type NUMBER; -- Bom_Item_Type of Assembly
20 g_Comp_ATO_flag CHAR; -- ATO flag for Component
21 g_Assy_ATO_flag CHAR; -- ATO flag for Assembly
22 g_Comp_PTO_flag CHAR; -- PTO flag for Component
23 g_Assy_PTO_flag CHAR; -- PTO flag for Assembly
24 g_Comp_Config CHAR; -- Is component a config item
25 g_Comp_ATO_Forecast_Control NUMBER; -- Component items ATO Forecast Control
26 g_Assy_Config CHAR; -- Is assembly a config item
27 g_Comp_Eng_Flag CHAR; -- Is component an Engineering Item
28 g_Assy_Eng_Flag CHAR; -- Is assembly an Engineering Item
29 g_Comp_ATP_Comp_flag CHAR; -- Components ATP Component Flag
30 g_Assy_ATP_Comp_flag CHAR; -- Assembly's ATP Component flag
31 g_Comp_ATP_Check_flag CHAR; -- Components ATP check flag
32 g_Assy_ATP_Check_flag CHAR; -- Assembly's ATP check flag
33 g_Assy_Wip_supply_Type NUMBER; -- Assembly 's wip supply type
34 g_Comp_Wip_Supply_Type NUMBER; -- Components WIP Supply Type
35 g_Assy_Bom_Enabled_flag CHAR; -- Assembly's bom_enabled_flag
36 g_Comp_Bom_Enabled_flag CHAR; -- Component's bom_enabled_flag
37
38 g_rev_component_rec Bom_bo_Pub.Rev_Component_Rec_Type;
39 g_Rev_Comp_Unexp_Rec Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type;
40 g_Token_Tbl Error_Handler.Token_Tbl_Type;
41
42
43 /*****************************************************************************
44 * Procedure : Check_Required
45 * Parameters IN : Revised Component exposed column record
46 * Paramaters OUT: Return Status
47 * Mesg Token Table
48 * Purpose : Procedure will check if the user has given all the required
49 * columns for the type of operation user is trying to perform.
50 * If the required columns are not filled in, then the record
51 * would get an error.
52 ******************************************************************************/
53 PROCEDURE Check_Required
54 ( x_return_status OUT NOCOPY VARCHAR2
55 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
56 , p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
57 )
58 IS
59 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
60
61 BEGIN
62 x_return_status := FND_API.G_RET_STS_SUCCESS;
63
64 g_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
65 g_token_tbl(1).token_value := p_rev_component_rec.component_item_name;
66
67 IF p_rev_component_rec.acd_type IS NULL OR
68 p_rev_component_rec.acd_type = FND_API.G_MISS_NUM
69 THEN
70 Error_Handler.Add_Error_Token
71 ( p_message_name => 'ENG_ACD_TYPE_MISSING'
72 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
73 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
74 , p_Token_Tbl => g_Token_Tbl
75 );
76
77 x_return_status := FND_API.G_RET_STS_ERROR;
78 END IF;
79
80 -- Return the message table.
81
82 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
83
84 END Check_Required;
85
86
87 /***************************************************************************
88 * Function : Control (Local function)
89 * Parameter IN : Org Level Control
90 * Subinventory Level Control
91 * Item Level Control
92 * Returns : Number
93 * Purpose : Control procedure will take the various level control values
94 * and decide if the Locator is controlled at the org,subinven
95 * or item level. It will also decide if the locator is
96 * pre-specified or dynamic.
97 **************************************************************************/
98 FUNCTION CONTROL(org_control IN number,
99 sub_control IN number,
100 item_control IN number default NULL)
101 RETURN NUMBER IS
102 locator_control number;
103 BEGIN
104
105 IF (org_control = 1) then
106 locator_control := 1;
107 ELSIF (org_control = 2) then
108 locator_control := 2;
109 ELSIF (org_control = 3) then
110 locator_control := 3;
111 ELSIF (org_control = 4) then
112 IF (sub_control = 1) then
113 locator_control := 1;
114 ELSIF (sub_control = 2) then
115 locator_control := 2;
116 ELSIF (sub_control = 3) then
117 locator_control := 3;
118 ELSIF (sub_control = 5) then
119 IF (item_control = 1) then
120 locator_control := 1;
121 ELSIF (item_control = 2) then
122 locator_control := 2;
123 ELSIF (item_control = 3) then
124 locator_control := 3;
125 ELSIF (item_control IS NULL) then
126 locator_control := sub_control;
127 END IF;
128 END IF;
129 END IF;
130 RETURN locator_control;
131 END CONTROL;
132
133 /***************************************************************************
134 * Function : Check_Overlap_Dates (Local function)
135 * Parameter IN: Effectivity Date
136 * Disable Date
137 * Bill Sequence Id
138 * Component Item Id
139 * Return : True if dates are overlapping else false.
140 * Purpose : The function will check if the same component is entered
141 * with overlapping dates. Components with overlapping dates
142 * will get a warning.
143 ***************************************************************************/
144 FUNCTION Check_Overlap_Dates (X_Effectivity_Date DATE,
145 X_Disable_Date DATE,
146 X_Member_Item_Id NUMBER,
147 X_Bill_Sequence_Id NUMBER,
148 X_Rowid VARCHAR2)
149 RETURN BOOLEAN
150 IS
151 X_Count NUMBER := 0;
152 CURSOR X_All_Dates IS
153 SELECT 'X' date_available FROM sys.dual
154 WHERE EXISTS (
155 SELECT 1 from BOM_Inventory_Components
156 WHERE Component_Item_Id = X_Member_Item_Id
157 AND Bill_Sequence_Id = X_Bill_Sequence_Id
158 AND (( RowId <> X_RowID ) or
159 (X_RowId IS NULL))
160 AND ( X_Disable_Date IS NULL
161 OR ( Trunc(X_Disable_Date) >
162 Trunc(Effectivity_Date)
163 )
164 )
165 AND ( Trunc(X_Effectivity_Date) <
166 Trunc(Disable_Date)
167 OR Disable_Date IS NULL
168 )
169 );
170 BEGIN
171
172 FOR X_Date IN X_All_Dates LOOP
173 X_Count := X_Count + 1;
174 END LOOP;
175
176 -- If count <> 0 that means the current date is overlapping with
177 -- some record.
178 IF X_Count <> 0 THEN
179 RETURN TRUE;
180 ELSE
181 RETURN FALSE;
182 END IF;
183
184 END Check_Overlap_Dates;
185
186 /***************************************************************************
187 * Function : Check_Overlap_Numbers (Local function)
188 * Parameter IN: from end item unit number
189 * to end item unit number
190 * Bill Sequence Id
191 * Component Item Id
192 * Return : True if unit numbers are overlapping, else false.
193 * Purpose : The function will check if the same component is entered
194 * with overlapping unit numbers. Components with overlapping
195 * unit numbers will get a warning.
196 * History : Created by AS on 07/08/99 as part of unit effectivity
197 * functionality.
198 ***************************************************************************/
199 FUNCTION Check_Overlap_Numbers(X_From_End_Item_Number VARCHAR2,
200 X_To_End_Item_Number VARCHAR2,
201 X_Member_Item_Id NUMBER,
202 X_Bill_Sequence_Id NUMBER,
203 X_Rowid VARCHAR2)
204 RETURN BOOLEAN
205 IS
206 X_Count NUMBER := 0;
207 CURSOR X_All_Numbers IS
208 SELECT 'X' unit_available FROM sys.dual
209 WHERE EXISTS (
210 SELECT 1 from BOM_Inventory_Components
211 WHERE Component_Item_Id = X_Member_Item_Id
212 AND Bill_Sequence_Id = X_Bill_Sequence_Id
213 AND (RowId <> X_RowID
214 OR X_RowId IS NULL)
215 AND (X_To_End_Item_Number IS NULL
216 OR X_To_End_Item_Number >
217 From_End_Item_Unit_Number)
218 AND (X_From_End_Item_Number <
219 To_End_Item_Unit_Number
220 OR To_End_Item_Unit_Number IS NULL
221 )
222 );
223 BEGIN
224
225 FOR X_Unit IN X_All_Numbers LOOP
226 X_Count := X_Count + 1;
227 END LOOP;
228
229 -- If count <> 0 that means the unit numbers are overlapping with
230 -- some record.
231 IF X_Count <> 0 THEN
232 RETURN TRUE;
233 ELSE
234 RETURN FALSE;
235 END IF;
236
237 END Check_Overlap_Numbers;
238
239 /****************************************************************************
240 * Function : Verify_Item_Attributes
241 * Prameters IN : Organization_Id
242 * Component Item Id
243 * Assembly Item Id
244 * Eng Bill flag for Assembly
245 * Parameters OUT: Mesg Token_Tbl
246 * Return : True if no attributes are invalid and False otherwise
247 * Purpose : The function will validate the following BOM Matrix.
248 *----------------------------------------------------------------------------
249 * Component Types
250 *----------------------------------------------------------------------------
251 *Bill PTO ATO PTO ATO ATO PTO Standard
252 *Types Model Model OC OC Planning Item Item Item
253 *------------- ------------------------------------------------------------
254 *PTO Model Yes Yes Yes No No Yes Yes Yes
255 *ATO Model No Yes No Yes No Yes No Yes
256 *PTO OC Yes Yes Yes No No Yes Yes Yes
257 *ATO OC No Yes No Yes No Yes No Yes
258 *Planning Yes Yes Yes Yes Yes Yes Yes Yes
259 *ATO Item No No No No No Yes No Yes
260 *PTO Item No No No No No No Yes Yes
261 *Standard Item No No No No No Yes No Yes
262 *Config Item No Yes No Yes No Yes No Yes
263 *
264 *****************************************************************************/
265 FUNCTION Verify_Item_Attributes
266 ( p_Mesg_token_tbl IN Error_Handler.Mesg_Token_Tbl_Type
267 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type)
268 RETURN BOOLEAN
269 IS
270 dummy NUMBER;
271 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
272
273 BEGIN
274 l_Mesg_Token_Tbl := p_Mesg_Token_Tbl;
275
276 --dbms_output.put_line('Within the Verify Item Attributes procedure . . . ');
277
278 -- Verify Eng flag for Assembly and Component
279 IF g_Assy_Eng_Flag = 'N' and -- Bill is manufacturing
280 g_Comp_Eng_Flag = 'Y' -- and component is Engineering
281 THEN
282 Error_Handler.Add_Error_Token
283 ( x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
284 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
285 , p_message_name => 'ENG_ASSY_COMP_ENG_FLG_MISMATCH'
286 , p_Token_Tbl => g_Token_Tbl
287 );
288 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
289 RETURN FALSE;
290 END IF;
291
292 /*******************************************************************
293 -- Verify ATO MODEL OR ATO OPTION CLASS Assembly(not config) Attributes
294 -- ATO Model does not allow
295 -- 1. Planning Components
296 -- 2. PTO Model or PTO Option Class
297 -- 3. PTO Standard
298 -- If the Assembly is ATO Standard, then it does not allow the
299 -- above three types and
300 -- 4. ATO Model or
301 -- 5. ATO Option Class
302 *******************************************************************/
303 IF ( ( ( g_Assy_Item_Type IN (l_MODEL, l_OPTION_CLASS, l_STANDARD) AND
304 g_Assy_ATO_flag = 'Y' AND
305 g_Assy_Config = 'N'
306 )
307 ) AND
308 ( g_Comp_Item_Type = l_PLANNING OR
309 ( g_Comp_Item_Type IN (l_MODEL, l_OPTION_CLASS, l_STANDARD) AND
310 g_Comp_PTO_Flag = 'Y'
311 )
312 )
313 ) OR
314 (
315 g_Assy_Item_Type = l_STANDARD AND
316 g_Assy_ATO_flag = 'Y' AND
317 g_Assy_Config = 'N' AND
318 ( g_Comp_Item_Type IN (l_MODEL, l_OPTION_CLASS) AND
319 g_Comp_ATO_Flag = 'Y'
320 )
321 )
322 THEN
323 IF g_Assy_Item_Type = l_MODEL
324 THEN
325 g_Token_Tbl(2).Token_Name := 'ITEM_TYPE';
326 g_Token_Tbl(2).Token_Value := 'ENG_MODEL_TYPE';
327 g_Token_Tbl(2).Translate := TRUE;
328 ELSIF g_Assy_Item_Type = l_OPTION_CLASS
329 THEN
330 g_Token_Tbl(2).Token_Name := 'ITEM_TYPE';
331 g_Token_Tbl(2).Token_Value := 'ENG_OPTION_CLASS_TYPE';
332 g_Token_Tbl(2).Translate := TRUE;
333 ELSE
334 g_Token_Tbl(2).Token_Name := 'ITEM_TYPE';
335 g_Token_Tbl(2).Token_Value := 'ENG_STANDARD_TYPE';
336 g_Token_Tbl(2).Translate := TRUE;
337 END IF;
338 g_token_tbl(3).token_name := 'REVISED_ITEM_NAME';
339 g_token_tbl(3).token_value :=
340 g_rev_component_rec.revised_item_name;
341
342 IF g_Assy_Item_Type IN ( l_MODEL, l_OPTION_CLASS)
343 THEN
344 Error_Handler.Add_Error_Token
345 ( p_message_name => 'ENG_ATO_PROP_MISMATCH'
346 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
347 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
348 , p_Token_Tbl => g_Token_Tbl
349 );
350 ELSE
351 Error_Handler.Add_Error_Token
352 ( p_message_name => 'ENG_ATO_STD_PROP_MISMATCH'
353 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
354 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
355 , p_Token_Tbl => g_Token_Tbl
356 );
357 END IF;
358 g_Token_Tbl.DELETE(2);
359 g_Token_Tbl.DELETE(3);
360
361 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
362 RETURN FALSE;
363
364 /*************************************************************
365 -- If the Assembly is a Config Item i.e. ATO with Base_Item_Id
366 -- NOT NULL then it will allow
367 -- 1. ATO Model
368 -- 2. ATO Option Class
369 -- 3. ATO Standard or
370 -- 4. Standard item
371 -- only if the assemly is Phantom is Wip_Supply_Type is 6
372 *************************************************************/
373 ELSIF g_Assy_ATO_Flag = 'Y' AND
374 g_Assy_Config = 'Y'
375 THEN
376 IF g_Assy_Wip_Supply_Type <> 6 AND
377 ( ( g_Comp_Item_Type in (l_MODEL, l_OPTION_CLASS, l_STANDARD)
378 AND
379 g_Comp_ATO_Flag = 'Y'
380 ) OR
381 g_Comp_Item_Type = l_STANDARD
382 )
383 THEN
384 g_token_tbl(2).token_name := 'REVISED_ITEM_NAME';
385 g_token_tbl(2).token_value :=
386 g_rev_component_rec.revised_item_name;
387
388 Error_Handler.Add_Error_Token
389 ( p_message_name => 'ENG_CFG_SUPPLY_NOT_PHANTOM'
390 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
391 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
392 , p_Token_Tbl => g_Token_Tbl
393 );
394 g_token_tbl.delete(2);
395
396 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
397 RETURN FALSE;
398
399 /*******************************************************
400 -- Assembly is Config item with Wip Supply of Phantom
401 -- but the component item types do not match
402 *******************************************************/
403 ELSIF g_Assy_Wip_Supply_Type = 6 AND
404 NOT
405 ( ( g_Comp_Item_Type IN
406 (l_MODEL, l_OPTION_CLASS, l_STANDARD)
407 AND
408 g_Comp_ATO_Flag = 'Y'
409 ) OR
410 g_Comp_Item_Type = l_STANDARD
411 )
412 THEN
413 g_token_tbl(2).token_name := 'REVISED_ITEM_NAME';
414 g_token_tbl(2).token_value :=
415 g_rev_component_rec.revised_item_name;
416
417 Error_Handler.Add_Error_Token
418 ( p_message_name => 'ENG_CONFIG_PROP_MISMATCH'
419 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
420 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
421 , p_Token_Tbl => g_Token_Tbl
422 );
423
424 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
425 g_token_tbl.delete(2);
426 RETURN FALSE;
427 END IF;
428
429 /*************************************************************
430 -- Verify PTO MODEL or OPTION CLASS Assembly Attributes
431 -- PTO Models do not allow
432 -- 1. ATO Option Class and
433 -- 2. Planning components
434 **************************************************************/
435 ELSIF g_Assy_Item_Type IN ( l_MODEL, l_OPTION_CLASS) AND
436 g_Assy_PTO_flag = 'Y' AND
437 ( g_Comp_Item_Type = l_PLANNING OR
438 ( g_Comp_Item_Type = l_OPTION_CLASS AND
439 g_Comp_ATO_flag = 'Y'
440 )
441 )
442 THEN
443 IF g_Assy_Item_Type = l_MODEL
444 THEN
445 g_Token_Tbl(2).Token_Name := 'ITEM_TYPE';
446 g_Token_Tbl(2).Token_Value := 'ENG_MODEL_TYPE';
447 g_Token_Tbl(2).Translate := TRUE;
448 ELSIF g_Assy_Item_Type = l_OPTION_CLASS
449 THEN
450 g_Token_Tbl(2).Token_Name := 'ITEM_TYPE';
451 g_Token_Tbl(2).Token_Value := 'ENG_OPTION_CLASS_TYPE';
452 g_Token_Tbl(2).Translate := TRUE;
453 END IF;
454
455 g_token_tbl(3).token_name := 'REVISED_ITEM_NAME';
456 g_token_tbl(3).token_value :=
457 g_rev_component_rec.revised_item_name;
458
459 Error_Handler.Add_Error_Token
460 ( p_message_name => 'ENG_MODEL_OC_PROP_MISMATCH'
461 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
462 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
463 , p_Token_Tbl => g_Token_Tbl
464 );
465
466 g_Token_Tbl.DELETE(2);
467 g_token_tbl.delete(3);
468 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
469 RETURN FALSE;
470
471 /*************************************************************
472 --
473 -- PTO STandard will only allow Standard or PTO Standard
474 --
475 *************************************************************/
476 ELSIF g_Assy_Item_Type = l_STANDARD AND
477 g_Assy_PTO_Flag = 'Y' AND
478 NOT ( ( g_Comp_Item_Type = l_STANDARD AND
479 g_Comp_PTO_flag = 'Y'
480 ) OR
481 ( g_Comp_Item_Type = l_STANDARD)
482 )
483 THEN
484 g_token_tbl(2).token_name := 'REVISED_ITEM_NAME';
485 g_token_tbl(2).token_value :=
486 g_rev_component_rec.revised_item_name;
487 Error_Handler.Add_Error_Token
488 ( p_message_name => 'ENG_PTO_STD_PROP_MISMATCH'
489 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
490 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
491 , p_Token_Tbl => g_Token_Tbl
492 );
493 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
494 g_token_tbl.delete(2);
495 RETURN FALSE;
496
497 /************************************************************
498 --
499 -- A STANDARD bill will only allow ATO Standard and Standard
500 -- items as components
501 --
502 *************************************************************/
503 ELSIF g_Assy_Item_Type = l_STANDARD AND
504 g_Assy_PTO_Flag = 'N' AND
505 g_Assy_ATO_Flag = 'N' AND
506 NOT
507 ( g_Comp_Item_Type = l_STANDARD AND
508 ( ( g_Comp_ATO_Flag = 'Y' AND
509 g_comp_PTO_Flag = 'N'
510 ) OR
511 ( g_comp_ATO_Flag = 'N' AND
512 g_comp_PTO_Flag = 'N'
513 )
514 )
515 )
516 THEN
517 g_token_tbl(2).token_name := 'REVISED_ITEM_NAME';
518 g_token_tbl(2).token_value :=
519 g_rev_component_rec.revised_item_name;
520 Error_Handler.Add_Error_Token
521 ( p_message_name => 'ENG_STANDARD_PROP_MISMATCH'
522 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
523 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
524 , p_Token_Tbl => g_Token_Tbl
525 );
526 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
527 g_token_tbl.delete(2);
528 RETURN FALSE;
529
530 END IF;
531
532
533 -- Once the matrix is verified then check the ATP Components and ATP
534 -- Check attributes
535
536 IF ( g_Assy_ATP_Comp_flag = 'N' AND
537 g_Comp_Atp_Check_Flag = 'Y'
538 )
539 THEN
540 g_token_tbl(2).token_name := 'REVISED_ITEM_NAME';
541 g_token_tbl(2).token_value :=
542 g_rev_component_rec.revised_item_name;
543
544 Error_Handler.Add_Error_Token
545 ( p_message_name => 'ENG_ASSY_COMP_ATP_MISMATCH'
546 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
547 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
548 , p_Token_Tbl => g_Token_Tbl
549 );
550 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
551 g_token_tbl.delete(2);
552 RETURN FALSE; -- ATP Item Attribute Invalid
553 END IF;
554 --dbms_output.put_line('End of Item Attribute Validation . . .');
555
556 -- If control comes till this point then it would mean a success of
557 -- attribute validation. Hence,
558
559 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
560 RETURN TRUE;
561
562 END Verify_Item_Attributes;
563
564
565 /****************************************************************************
566 * Function : Check_PTO_ATO_For_Optional (Local Function)
567 * Returns : 0 if Success
568 * 1 if Optional value is incorrect for ATO/PTO Model/OC
569 * 2 if Optional value is incorrect for Planning/Standard Bill
570 * Purpose : Function will verify the following things:
571 * 1. Optional must be NO (2) if Bill if Planning or Standard
572 * 2. If Bill is PTO Model or Option Class and component is
573 * ATO Standard with no base model then Optional must be Yes
574 * (1)
575 *****************************************************************************/
576 --
577 -- Check if the PTO and ATO flags of Assembly and Component for the
578 -- Optional flag to be correct.
579 --
580
581 FUNCTION Check_PTO_ATO_for_Optional
582 RETURN NUMBER
583 IS
584 BEGIN
585
586 --dbms_output.put_line('Value of Optional when checking ATO / PTO . . .');
587
588 IF ( g_Assy_PTO_flag = 'Y' AND
589 g_Assy_Item_Type IN ( l_MODEL, l_OPTION_CLASS) AND
590 g_Comp_ATO_flag = 'Y' AND
591 g_Comp_Item_Type = l_STANDARD AND
592 g_Comp_Config = 'N' AND
593 g_Rev_Component_Rec.optional = 2
594 )
595 THEN
596 RETURN 1;
597 ELSIF ( g_Assy_Item_Type IN (l_STANDARD, l_PLANNING) AND
598 g_Rev_Component_Rec.optional = 1
599 )
600 THEN
601 RETURN 2;
602 ELSE
603 RETURN 0;
604 END IF;
605
606 END Check_PTO_ATO_for_Optional;
607
608 /*****************************************************************************
609 * Function : Check_Planning_Percent
610 * Returns : 0 for Success else 1, 2, or 3 for errors
611 * Purpose : The function will verify the following:
612 * Planning percent can be <> 100 only if
613 * 1. Bill is Planning else RETURN error code 1 Or
614 * 2. Bill is a Model/Option Class and Component is optional Or
615 * else return error code 2.
616 * 3. Bill is a Model/Option Class and component is not Optional
617 * and forecase control is = 2 (Consume and Derive)
618 *****************************************************************************/
619 FUNCTION Check_Planning_Percent RETURN NUMBER
620 IS
621 BEGIN
622 IF g_rev_component_rec.planning_percent <> 100 THEN
623 IF g_Assy_Item_Type = l_STANDARD THEN
624 RETURN 1;
625 ELSIF ( g_Assy_Item_Type IN (l_MODEL, l_OPTION_CLASS) AND
626 g_rev_component_rec.optional <> 1 AND
627 g_Comp_ATO_Forecast_Control <> 2
628 )
629 THEN
630 RETURN 2;
631 ELSIF ( g_Assy_Item_Type IN (l_MODEL, l_OPTION_CLASS) AND
632 ( g_rev_component_rec.optional = 1 OR
633 g_Comp_ATO_Forecast_Control <> 2
634 )
635 )
636 THEN
637 RETURN 3;
638 ELSE
639 RETURN 0;
640 END IF;
641 END IF;
642
643 END Check_Planning_Percent;
644
645 /****************************************************************************
646 * Function : Chk_Req_For_Rev_Or_Shp
647 * Returns : 1 if Required for Revenue is invalid
648 * 2 if Required to Ship is invalid
649 * 3 if both are incorrect
650 * 0 if both are correct
651 * Purpose : Function will verify the following:
652 * Required for Revenue / Required to Ship must be NO if
653 * Replenish_To_Order_Flag is 'Y' for the Bill
654 *****************************************************************************/
655 FUNCTION Chk_Req_For_Rev_Or_Shp
656 RETURN NUMBER
657 IS
658 BEGIN
659
660 IF g_rev_component_rec.required_for_revenue = 1 AND
661 g_rev_component_rec.required_to_ship = 2 AND
662 g_Assy_ATO_Flag = 'Y'
663 THEN
664 RETURN 1;
665 ELSIF g_rev_component_rec.required_to_ship = 1 AND
666 g_rev_component_rec.required_for_revenue = 2 AND
667 g_Assy_ATO_Flag = 'Y'
668 THEN
669 RETURN 2;
670 ELSIF g_rev_component_rec.required_to_ship = 1 AND
671 g_rev_component_rec.required_for_revenue = 1 AND
672 g_Assy_ATO_Flag = 'Y'
673 THEN
674 RETURN 3;
675 ELSE
676 RETURN 0;
677 END IF;
678
679 END Chk_Req_For_Rev_Or_Shp;
680
681
682 -- Local Function Check_ATP
683 /****************************************************************************
684 *Function : Check_ATP
685 *Returns : 1 if the ATP value is incorrect because qty is negative
686 * 2 if the ATP value is incorrect because qty is fractional
687 * 0 if the ATP value is valid.
688 *Purpose : Function will verify if the Check_Atp value is correct
689 * wrt to the check_atp and atp_components_flag of the parent
690 * and component. It will also check if the component quantity
691 * is greater than 0 for the check_atp to be yes.
692 *****************************************************************************/
693 FUNCTION Check_ATP RETURN NUMBER
694 IS
695 BEGIN
696 --dbms_output.put_line('Withing Function Check_ATP . . .');
697
698 IF g_Assy_ATP_Comp_flag = 'Y' AND
699 g_Comp_ATP_Check_flag= 'Y'
700 THEN
701 IF g_rev_component_rec.quantity_per_assembly < 0 THEN
702
703 RETURN 1;
704 ELSIF round(g_rev_component_rec.quantity_per_assembly) <>
705 g_rev_component_rec.quantity_per_assembly
706 THEN
707 RETURN 2;
708 ELSE
709 RETURN 0;
710 END IF;
711 ELSE
712 RETURN 1;
713 END IF;
714
715 END Check_ATP;
716
717 /*****************************************************************************
718 * Function : Check_Mutually_Exclusive
719 * Returns : 0 if the Mutually exlusive values is correct
720 * 1 if BOM is not Installed
721 * 2 if Revised Component is Model or Option Class
722 * Purpose : Will verify the value of mutually exclusive options column
723 * by verifying if BOM is Installed and the component is
724 * either a Model or Option Class. In only this case the column
725 * can have a value of Yes (1).
726 ******************************************************************************/
727 --Local function to validate Mutually_Exclusive_Option
728 FUNCTION Check_Mutually_Exclusive RETURN NUMBER
729 IS
730 X_Bom_Status VARCHAR2(80);
731 X_Industry VARCHAR2(80);
732 X_Install_Bom BOOLEAN;
733 BEGIN
734 --dbms_output.put_Line('Checking Mutually Exclusive for value : ' ||
735 -- to_char(g_rev_component_rec.Mutually_Exclusive));
736 IF g_rev_component_rec.Mutually_Exclusive = 1 THEN
737 X_install_bom := Fnd_Installation.Get
738 ( appl_id => '702',
739 dep_appl_id => '702',
740 status => X_bom_status,
741 industry => X_industry);
742 IF UPPER(X_Bom_Status) = 'INSTALLED' AND
743 g_Comp_Item_Type IN (l_MODEL, l_OPTION_CLASS)
744 THEN
745 RETURN 0;
746 ELSIF g_Comp_Item_Type NOT IN (l_MODEL, l_OPTION_CLASS)
747 THEN
748 RETURN 2;
749 ELSE
750 RETURN 1;
751 END IF;
752 ELSE
753 RETURN 0;
754 END IF;
755
756 END Check_Mutually_Exclusive;
757
758 /****************************************************************************
759 * Function : Check_Supply_Type
760 * Returns : TRUE if the supply type is correct, false otherwise
761 * Purpose : Function will verify if the Wip_supply_Type value is
762 * is correct by doing the following checks:
763 * 1. Wip_Supply_Type = 6 (Phantom), then component must have
764 * a bill, else log a warning.
765 * 2. Wip_Supply_Type must be Phantom if the component is
766 * Model or Option Class
767 *****************************************************************************/
768 FUNCTION Check_Supply_Type
769 ( p_Mesg_Token_Tbl IN Error_Handler.Mesg_Token_Tbl_Type
770 , x_Mesg_token_tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type)
771 RETURN BOOLEAN
772 IS
773 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
774 BEGIN
775 l_Mesg_token_Tbl := p_Mesg_Token_Tbl;
776
777 IF g_rev_component_rec.WIP_Supply_Type IS NOT NULL
778 THEN
779 IF g_rev_component_rec.wip_supply_type = 6 /* PHANTOM */
780 THEN
781 BEGIN
782 -- If Phantom then Component must be a Bill
783 SELECT 'Valid'
784 INTO l_dummy
785 FROM bom_bill_of_materials
786 WHERE assembly_item_id =
787 g_rev_comp_unexp_rec.component_item_id
788 AND organization_id =
789 g_rev_comp_unexp_rec.organization_id;
790
791 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
792 RETURN TRUE;
793
794 EXCEPTION
795 WHEN NO_DATA_FOUND THEN
796 Error_Handler.Add_Error_Token
797 ( p_message_name => 'BOM_NO_BILL'
798 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
799 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
800 , p_Token_Tbl => g_Token_Tbl
801 );
802 x_Mesg_token_tbl := l_Mesg_Token_Tbl;
803 RETURN TRUE;
804 -- Since this is a warning return true
805 END;
806 ELSE
807 -- If component is Model/OC then WIP_Supply Type must
808 -- be Phantom
809 IF g_Comp_Item_Type IN (l_MODEL, l_OPTION_CLASS) AND
810 g_rev_component_rec.wip_supply_type <> 6
811 THEN
812 Error_Handler.Add_Error_Token
813 ( p_message_name => 'ENG_WIP_SUPPLY_PHANTOM'
814 ,p_Mesg_Token_Tbl=> l_Mesg_token_Tbl
815 ,x_Mesg_Token_Tbl=> l_Mesg_Token_Tbl
816 ,p_token_tbl => g_Token_Tbl
817 );
818 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
819 --dbms_output.put_line('Returing False from Check Supply Type . . .');
820
821 RETURN FALSE;
822 END IF;
823 END IF;
824 ELSE
825 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
826 RETURN TRUE;
827 END IF;
828
829 END Check_Supply_Type;
830
831 -- Local Function to verify Minimum Quantity.
832 FUNCTION Check_Min_Quantity RETURN BOOLEAN
833 IS
834 BEGIN
835 --dbms_output.put_line('Low Quantity : ' ||
836 -- to_char(g_rev_component_rec.minimum_allowed_quantity));
837 --dbms_output.put_line('Component Quantity : ' ||
838 -- to_char(g_rev_component_rec.quantity_per_assembly));
839 IF NVL(g_rev_component_rec.minimum_allowed_quantity, 0) >
840 NVL(g_rev_component_rec.quantity_per_assembly, 0) THEN
841 RETURN FALSE;
842 ELSE
843 RETURN TRUE;
844 END IF;
845
846 END Check_Min_Quantity;
847
848 -- Local function to verify High Quantity
849 FUNCTION Check_Max_Quantity RETURN BOOLEAN
850 IS
851 BEGIN
852 --dbms_output.put_line('High Quantity : ' ||
853 -- to_char(g_rev_component_rec.maximum_allowed_quantity));
854 --dbms_output.put_line('Component Quantity : ' ||
855 -- to_char(g_rev_component_rec.quantity_per_assembly));
856
857 IF NVL(g_rev_component_rec.maximum_allowed_quantity, 0) <
858 NVL(g_rev_component_rec.quantity_per_assembly, 0) THEN
859 RETURN FALSE;
860 ELSE
861 RETURN TRUE;
862 END IF;
863
864 END Check_Max_Quantity;
865
866 -- Local function to check supply subinventory
867 FUNCTION Check_Supply_SubInventory RETURN BOOLEAN
868 IS
869 l_allow_expense_to_asset VARCHAR2(10);
870 l_RestrictSubInventory VARCHAR2(1);
871 l_InventoryAsset VARCHAR2(1);
872
873 CURSOR c_Restrict_SubInv_Asset IS
874 SELECT locator_type
875 FROM mtl_item_sub_ast_trk_val_v
876 WHERE inventory_item_id = g_rev_comp_Unexp_rec.component_item_id
877 AND organization_id = g_rev_comp_Unexp_rec.organization_id
878 AND secondary_inventory_name =
879 g_rev_component_rec.supply_subinventory;
880
881 CURSOR c_Restrict_SubInv_Trk IS
882 SELECT locator_type
883 FROM mtl_item_sub_trk_val_v
884 WHERE inventory_item_id = g_rev_comp_Unexp_rec.component_item_id
885 AND organization_id = g_rev_comp_Unexp_rec.organization_id
886 AND secondary_inventory_name =
887 g_rev_component_rec.supply_subinventory;
888
889 CURSOR c_SubInventory_Asset IS
890 SELECT locator_type
891 FROM mtl_sub_ast_trk_val_v
892 WHERE organization_id = g_rev_comp_Unexp_rec.organization_id
893 AND secondary_inventory_name =
894 g_rev_component_rec.supply_subinventory;
895
896 CURSOR c_Subinventory_Tracked IS
897 SELECT locator_type
898 FROM mtl_subinventories_trk_val_v
899 WHERE organization_id = g_rev_comp_Unexp_rec.organization_id
900 AND secondary_inventory_name =
901 g_rev_component_rec.supply_subinventory;
902
903 BEGIN
904
905 --dbms_output.put_line('Checking Subinv value . . . ' ||
906 -- g_rev_component_rec.supply_subinventory);
907
908 l_allow_expense_to_asset := fnd_profile.value
909 ('INV:EXPENSE_TO_ASSET_TRANSFER');
910
911 -- Get Restrict_Subinventory_Flag for the Item
912 SELECT DECODE(restrict_subinventories_code, 1, 'Y', 'N'),
913 inventory_asset_flag
914 INTO l_RestrictSubInventory,
915 l_InventoryAsset
916 FROM mtl_system_items
917 WHERE inventory_item_id = g_rev_comp_Unexp_rec.component_item_id
918 AND organization_id = g_rev_comp_Unexp_rec.organization_id;
919
920 IF l_RestrictSubInventory = 'Y' THEN
921
922 --dbms_output.put_line('Subinventory is Restricted . . . ');
923
924 IF l_allow_expense_to_asset = '1' THEN
925
926 --dbms_output.put_line('Allow Expense to Asset 1 . . .');
927
928 OPEN c_Restrict_SubInv_Trk;
929 FETCH c_Restrict_SubInv_Trk INTO l_Sub_Locator_Control;
930 --dbms_output.put_line('Within locator check ' || to_char(l_Sub_Locator_Control));
931
932 IF c_Restrict_SubInv_Trk%Found THEN
933 CLOSE c_Restrict_SubInv_Trk;
934 RETURN TRUE;
935 ELSE
936 --dbms_output.put_line('Sub loc in Subinv: ' || to_char(l_Sub_Locator_Control));
937
938 CLOSE c_Restrict_SubInv_Trk;
939 RETURN FALSE;
940 END IF;
941 ELSE
942 IF l_InventoryAsset = 'Y' THEN
943
944 --dbms_output.put_line('Inventory Asset Yes . . .');
945
946 OPEN c_Restrict_SubInv_Asset;
947 FETCH c_Restrict_SubInv_Asset INTO
948 l_Sub_Locator_Control;
949 IF c_Restrict_SubInv_Asset%Found THEN
950 CLOSE c_Restrict_SubInv_Asset;
951 RETURN TRUE;
952 ELSE
953 CLOSE c_Restrict_SubInv_Asset;
954 RETURN FALSE;
955 END IF;
956 ELSE
957 --dbms_output.put_line('Inventory Asset No . . .');
958
959 OPEN c_Restrict_SubInv_Trk;
960 FETCH c_Restrict_SubInv_Trk INTO
961 l_Sub_Locator_Control;
962 IF c_Restrict_SubInv_Trk%Found THEN
963 CLOSE c_Restrict_SubInv_Trk;
964 RETURN TRUE;
965 ELSE
966 CLOSE c_Restrict_SubInv_Trk;
967 RETURN FALSE;
968 END IF;
969
970 END IF;
971 END IF;
972 ELSE
973
974 --dbms_output.put_line('Subinventory not restricted . . .');
975
976 IF l_Allow_Expense_To_Asset = '1' THEN
977
978 --dbms_output.put_line('Allow Expense to Asset = 1 . . .');
979
980 OPEN c_SubInventory_Tracked;
981 FETCH c_SubInventory_Tracked INTO l_Sub_Locator_Control;
982 IF c_SubInventory_Tracked%FOUND THEN
983 CLOSE c_SubInventory_Tracked;
984 RETURN TRUE;
985 ELSE
986 CLOSE c_SubInventory_Tracked;
987 RETURN FALSE;
988 END IF;
989 ELSE
990 IF l_InventoryAsset = 'Y' THEN
991 --dbms_output.put_line('Inventory Asset = Y . . .');
992
993 OPEN c_SubInventory_Asset;
994 FETCH c_SubInventory_Asset INTO
995 l_Sub_Locator_Control;
996 IF c_SubInventory_Asset%FOUND THEN
997 CLOSE c_SubInventory_Asset;
998 RETURN TRUE;
999 ELSE
1000 CLOSE c_SubInventory_Asset;
1001 RETURN FALSE;
1002 END IF;
1003 ELSE
1004 --dbms_output.put_line('Fetched from Subinventory Tracked . . .');
1005
1006 OPEN c_Subinventory_Tracked;
1007 FETCH c_Subinventory_Tracked INTO
1008 l_Sub_Locator_Control;
1009 IF c_SubInventory_Tracked%FOUND THEN
1010 CLOSE c_Subinventory_Tracked;
1011 RETURN TRUE;
1012 ELSE
1013 CLOSE c_Subinventory_Tracked;
1014 RETURN FALSE;
1015 END IF;
1016 END IF;
1017 END IF;
1018 END IF;
1019 END Check_Supply_SubInventory;
1020
1021 -- Local function to verify locators
1022 FUNCTION Check_Locators RETURN BOOLEAN IS
1023 Cursor CheckDuplicate is
1024 SELECT 'checking for duplicates' dummy
1025 FROM sys.dual
1026 WHERE EXISTS (
1027 SELECT null
1028 FROM mtl_item_locations
1029 WHERE organization_id = g_rev_comp_Unexp_rec.organization_id
1030 AND inventory_location_id = g_rev_comp_Unexp_rec.supply_locator_id
1031 AND subinventory_code <> g_rev_component_rec.supply_subinventory);
1032
1033 x_Control NUMBER;
1034 l_Success BOOLEAN;
1035 BEGIN
1036
1037 l_org_locator_control := 0 ;
1038 l_item_locator_control := 0;
1039
1040
1041 --dbms_output.put_line('Within Check Locators function. . .');
1042
1043 -- Get Value of Org_Locator and item_Locator.
1044 SELECT stock_locator_control_code
1045 INTO l_org_locator_control
1046 FROM mtl_parameters
1047 WHERE organization_id = g_rev_comp_Unexp_rec.organization_id;
1048
1049 -- Get Value of Item Locator
1050 SELECT location_control_code
1051 INTO l_item_locator_control
1052 FROM mtl_system_items
1053 WHERE organization_id = g_rev_comp_Unexp_rec.organization_id
1054 AND inventory_item_id = g_rev_comp_Unexp_rec.component_item_id;
1055
1056 -- Get if locator is restricted or unrestricted
1057
1058 SELECT RESTRICT_LOCATORS_CODE
1059 INTO l_item_loc_restricted
1060 FROM mtl_system_items
1061 WHERE organization_id = g_rev_comp_Unexp_rec.organization_id
1062 AND inventory_item_id = g_rev_comp_Unexp_rec.component_item_id;
1063
1064 --
1065 -- When a SubInventory is validated, then depending on the Cursor being
1066 -- used in the Check_SubInventory procedure, the value of
1067 -- l_Sub_Locator_Control would be set. Else if there is no change in subinv
1068 -- then excute subinv check.
1069 --
1070
1071 --dbms_output.put_line('Checking Subinventory locator control,
1072 -- calling Check_Supply_SubInventory . . .');
1073
1074 IF l_Sub_Locator_Control IS NULL AND
1075 g_rev_component_rec.supply_subinventory IS NOT NULL THEN
1076 l_Success := Check_Supply_SubInventory;
1077 END IF;
1078
1079 --dbms_output.put_line('After calling Check_Supply_SubInventory in Check_Locators . . .');
1080
1081
1082 --
1083 -- Locator cannot be NULL is if locator restricted
1084 --
1085 IF g_rev_component_rec.location_name IS NULL
1086 AND l_item_loc_restricted = 1
1087 THEN
1088 l_locator_control := 4;
1089 RETURN FALSE;
1090 ELSIF g_rev_component_rec.location_name IS NULL
1091 AND l_item_loc_restricted = 2
1092 THEN
1093 RETURN TRUE;
1094 END IF;
1095
1096 --dbms_output.put_line('Within Check locators . . .');
1097
1098 IF l_org_locator_control is not null AND
1099 l_sub_locator_control is not null AND
1100 l_item_locator_control is not null THEN
1101 --dbms_output.put_line('Org _Control: ' || to_char(l_org_locator_control));
1102 --dbms_output.put_line('Sub _Control: ' || to_char(l_sub_locator_control));
1103 --dbms_output.put_line('Item Control: ' || to_char(l_item_locator_control));
1104
1105 x_control := Control( Org_Control => l_org_locator_control,
1106 Sub_Control => l_sub_locator_control,
1107 Item_Control => l_item_locator_control
1108 );
1109
1110
1111 l_locator_control := x_control;
1112 -- Variable to identify if the dynamic loc. message must be logged.
1113
1114 IF x_Control = 1 THEN -- No Locator Control
1115 RETURN FALSE; -- No Locator and Locator Id is supplied then raise Error
1116 ELSIF x_Control = 2 THEN -- PRESPECIFIED
1117 BEGIN
1118
1119 --dbms_output.put_line('Checking when x_control returned 2 and item locator is '
1120 -- || to_char(l_item_locator_control));
1121
1122 -- If restrict locators is Y then check in mtl_secondary_locators
1123 -- if the item is assigned to the subinventory/location combination
1124 -- If restrict locators is N then check that the locator exists
1125 -- and is assigned to the subinventory and this combination is
1126 -- found in mtl_item_locations.
1127
1128 IF l_item_loc_restricted = 1 -- Restrict Locators = YES
1129 THEN
1130
1131 -- **** Check for restrict Locators YES ****
1132 SELECT 'Valid'
1133 INTO l_dummy
1134 FROM mtl_item_locations mil,
1135 mtl_secondary_locators msl
1136 WHERE msl.inventory_item_id =
1137 g_rev_comp_Unexp_rec.component_item_id
1138 AND msl.organization_id =
1139 g_rev_comp_Unexp_rec.organization_id
1140 AND msl.subinventory_code =
1141 g_rev_component_rec.supply_subinventory
1142 AND msl.secondary_locator =
1143 g_rev_comp_Unexp_rec.supply_locator_id
1144 AND mil.inventory_location_id = msl.secondary_locator
1145 AND mil.organization_id =
1146 msl.organization_id
1147 AND NVL(mil.disable_date, SYSDATE+1) > SYSDATE ;
1148
1149 --If no exception is raised then the Locator is Valid
1150 RETURN TRUE;
1151 ELSE
1152 -- **** Check for restrict Locators NO ****
1153 --dbms_output.put_line('Item restrict locators is NO . . .');
1154 SELECT 'Valid'
1155 INTO l_dummy
1156 FROM mtl_item_locations mil
1157 WHERE mil.subinventory_code =
1158 g_rev_component_rec.supply_subinventory
1159 AND mil.inventory_location_id =
1160 g_rev_comp_Unexp_rec.supply_locator_id
1161 AND mil.organization_id =
1162 g_rev_comp_Unexp_rec.organization_id
1163 AND NVL(mil.DISABLE_DATE, SYSDATE+1) > SYSDATE;
1164
1165 --If no exception is raised then the Locator is Valid
1166 RETURN TRUE;
1167
1168 END IF;
1169
1170 EXCEPTION
1171 WHEN NO_DATA_FOUND THEN
1172 RETURN FALSE;
1173 END;
1174
1175 ELSIF x_Control = 3 THEN -- DYNAMIC LOCATORS ARE NOT ALLOWED IN OI.
1176 -- Dynamic locators are not allowed in open interface, so
1177 -- raise an error if the locator control is dynamic.
1178 l_locator_control := 3;
1179 RETURN FALSE;
1180 ELSE
1181 --dbms_output.put_line('Finally returing a true value . . .');
1182 RETURN TRUE;
1183
1184 END IF; -- X_control Checking Ends
1185
1186 ELSE
1187 RETURN TRUE;
1188 END IF; -- If Locator Control check Ends.
1189
1190 END Check_Locators;
1191
1192 /*****************************************************************************
1193 * Function : Check_Op_Seq
1194 * Returns : 0 if new op_seq or op_seq is valid
1195 * 1 if new_op_seq or op_seq does not exist
1196 * 2 if new_op_seq or op_seq is not unique
1197 * Purpose : Verify the following:
1198 * Function Check_Op_Seq will check if the op_seq_num or the
1199 * new_op_seq_num exists.
1200 * If they exist, then it will go ahead and check if the the same
1201 * component does not already exist with the same op_seq_num
1202 ******************************************************************************/
1203 FUNCTION Check_Op_Seq RETURN NUMBER
1204 IS
1205 CURSOR Valid_Op_Seq IS
1206 SELECT 'Valid' valid_op_seq
1207 FROM bom_operational_routings bor,
1208 bom_operation_sequences bos
1209 WHERE bor.assembly_item_id = g_rev_comp_Unexp_rec.revised_item_id
1210 AND bor.organization_id = g_rev_comp_Unexp_rec.organization_id
1211 AND NVL(bor.alternate_routing_designator, 'NONE') =
1212 NVL(g_rev_component_rec.alternate_bom_code, 'NONE')
1213 AND bos.routing_sequence_id = bor.routing_sequence_id
1214 AND bos.operation_seq_num =
1215 decode( g_rev_component_rec.new_operation_sequence_number,
1216 NULL,
1217 g_rev_component_rec.Operation_Sequence_Number,
1218 g_rev_component_rec.new_Operation_sequence_number
1219 );
1220
1221 CURSOR c_Op_Seq_Used IS
1222 /* Verify that the same component is not already effective */
1223 SELECT 'Already Used' op_seq_used
1224 FROM bom_inventory_components bic
1225 WHERE bic.bill_sequence_id = g_rev_comp_Unexp_rec.bill_sequence_id
1226 AND bic.component_item_id = g_rev_comp_Unexp_rec.component_item_id
1227 AND bic.operation_seq_num =
1228 decode( g_rev_component_rec.new_operation_sequence_number,
1229 NULL,
1230 g_rev_component_rec.operation_sequence_number,
1231 g_rev_component_rec.new_operation_sequence_number
1232 );
1233 BEGIN
1234
1235 -- If a record is found then it will mean that though
1236 -- the Operation Sequence exists in the Routings table
1237 -- a component already exist with that operation sequence abd
1238 -- Effectivity date so it cannot be inserted. So generate an error
1239 -- hence, this function will return a false.
1240
1241 FOR l_valid_op IN Valid_Op_Seq LOOP
1242 -- if operation_seq exists in Operation_Sequences then
1243 -- verify that the same component does not already exist
1244 -- for that bill with the same operation seq.
1245
1246 FOR l_Op_Seq_Used IN c_Op_Seq_Used LOOP
1247 RETURN 2;
1248 -- Op_seq_num or the new_op_seq_num is not unique
1249 END LOOP;
1250
1251 --dbms_output.put_line('Check Op Seq returing with Success (0) ');
1252
1253 RETURN 0; -- op_seq_num or new_op_seq_num is valid
1254
1255 END LOOP;
1256
1257 RETURN 1;
1258 -- op_seq_num or new_op_seq_num is invalid
1259 -- i.e does not exist in bom_oper_sequences
1260
1261 END Check_Op_Seq;
1262
1263
1264 FUNCTION Check_Optional RETURN BOOLEAN
1265 IS
1266 CURSOR c_CheckOptional IS
1267 SELECT 'Valid' is_Valid
1268 FROM mtl_system_items assy,
1269 mtl_system_items comp
1270 WHERE assy.organization_id = g_rev_comp_Unexp_rec.organization_id
1271 AND assy.inventory_item_id = g_rev_comp_Unexp_rec.revised_item_id
1272 AND comp.organization_id = g_rev_comp_Unexp_rec.organization_id
1273 AND comp.inventory_item_id = g_rev_comp_Unexp_rec.component_item_id
1274 AND ( ( assy.bom_item_type IN ( l_Planning, l_Standard)
1275 AND g_rev_component_rec.optional = 2 /* NO */
1276 )
1277 OR
1278 ( assy.bom_item_type IN ( l_Model, l_Option_Class)
1279 AND assy.pick_components_flag = 'Y'
1280 /* PTO Model or PTO Option Class */
1281 AND comp.bom_item_type = l_Standard
1282 AND comp.replenish_to_order_flag = 'Y'
1283 AND comp.base_item_id IS NULL
1284 AND g_rev_component_rec.Optional = 1
1285 )
1286 );
1287
1288 BEGIN
1289 -- Optional must be 2 if Bill is Planning or Standard.
1290 -- If the Bill is PTO Model or PTO Option Class and the Component
1291 -- is an ATO Standard item with no Base Model then Optional must be 1
1292 -- Else it can be anything from 1 and 2.
1293
1294 OPEN c_CheckOptional;
1295 FETCH c_CheckOptional INTO l_dummy;
1296 IF c_CheckOptional%FOUND THEN
1297 CLOSE c_CheckOptional;
1298 RETURN TRUE;
1299 ELSE
1300 CLOSE c_CheckOptional;
1301 RETURN FALSE;
1302 END IF;
1303
1304 END Check_Optional;
1305
1306 /****************************************************************************
1307 * Function : Check_Common_Other_Orgs
1308 * Return : True if component exists in other orgs, False otherwise
1309 * Purpose : If component is being added to a bill that is being referenced
1310 * by items (bills) in other orgs (as a common), the component
1311 * must exist in those orgs as well, with the correct item
1312 * attributes. This function will verify this and will return
1313 * on success and false on failure.
1314 ******************************************************************************/
1315 FUNCTION Check_Common_Other_Orgs
1316 RETURN NUMBER
1317 IS
1318 l_other_orgs BOOLEAN;
1319 CURSOR bom_enabled_in_other_orgs IS
1320 SELECT 1
1321 FROM BOM_BILL_OF_MATERIALS bom
1322 WHERE bom.common_bill_sequence_id =
1323 g_rev_comp_Unexp_rec.bill_sequence_id
1324 AND bom.organization_id <> g_rev_comp_Unexp_rec.organization_id
1325 AND NOT EXISTS (SELECT 1
1326 FROM MTL_SYSTEM_ITEMS msi
1327 WHERE msi.organization_id = bom.organization_id
1328 AND msi.inventory_item_id =
1329 g_rev_comp_Unexp_rec.component_item_id
1330 AND msi.bom_enabled_flag = 'Y'
1331 );
1332
1333 CURSOR in_other_orgs IS
1334 SELECT 1
1335 FROM BOM_BILL_OF_MATERIALS bom
1336 WHERE bom.common_bill_sequence_id =
1337 g_rev_comp_Unexp_rec.bill_sequence_id
1338 AND bom.organization_id <> g_rev_comp_Unexp_rec.organization_id
1339 AND NOT EXISTS (SELECT 1
1340 FROM MTL_SYSTEM_ITEMS msi
1341 WHERE msi.organization_id = bom.organization_id
1342 AND msi.inventory_item_id =
1343 g_rev_comp_Unexp_rec.component_item_id
1344 );
1345
1346 CURSOR eng_flag_in_other_orgs
1347 IS
1348 SELECT 1
1349 FROM BOM_BILL_OF_MATERIALS bom
1350 WHERE bom.common_bill_sequence_id =
1351 g_rev_comp_Unexp_rec.bill_sequence_id
1352 AND bom.organization_id <> g_rev_comp_Unexp_rec.organization_id
1353 AND NOT EXISTS (SELECT 1
1354 FROM MTL_SYSTEM_ITEMS msi
1355 WHERE msi.organization_id = bom.organization_id
1356 AND msi.inventory_item_id =
1357 g_rev_comp_Unexp_rec.component_item_id
1358 AND msi.bom_enabled_flag = 'Y'
1359 AND (( bom.assembly_type = 1 AND
1360 msi.eng_item_flag = 'N'
1361 )
1362 OR bom.assembly_type = 2
1363 )
1364 );
1365 BEGIN
1366
1367 -- If component not in other Orgs that refer the bill as common
1368 -- then return an error code of 1
1369
1370 --dbms_output.put_line('Checking if component exists in other ORGS when bill is common . . .');
1371
1372 FOR c_other_orgs IN in_other_orgs LOOP
1373 RETURN 1;
1374 END LOOP;
1375
1376 --dbms_output.put_line('Checking if component is BOM enabled in other ORGS . . .');
1377
1378 FOR c_bom_enabled IN bom_enabled_in_other_orgs LOOP
1379 RETURN 2;
1380 END LOOP;
1381
1382 --dbms_output.put_line('Checking if component is ENG flag is compatible in other ORGS . . .');
1383
1384 FOR c_eng_flag IN eng_flag_in_other_orgs LOOP
1385 RETURN 3;
1386 END LOOP;
1387
1388 RETURN 0;
1389
1390 END Check_Common_Other_Orgs;
1391
1392 FUNCTION Check_PrimaryBill
1393 RETURN BOOLEAN
1394 IS
1395 CURSOR c_CheckPrimary IS
1396 SELECT 1
1397 FROM bom_bill_of_materials
1398 WHERE assembly_item_id = g_Rev_Comp_Unexp_Rec.revised_item_id
1399 AND organization_id = g_Rev_Comp_Unexp_Rec.Organization_Id
1400 AND NVL(alternate_bom_designator, 'NONE') = 'NONE';
1401 BEGIN
1402 FOR CheckPrimary IN c_CheckPrimary LOOP
1403 RETURN TRUE;
1404 END LOOP;
1405
1406 -- If the loop does not execute then
1407 -- return false
1408
1409 RETURN FALSE;
1410 END Check_PrimaryBill;
1411
1412 /*****************************************************************************
1413 * Procedure : Check_Entity
1414 * Parameters IN : Revised component exposed column record
1415 * Revised component unexposed column record
1416 * Revised component old exposed column record
1417 * Revised component old unexposed column record
1418 * Parameters OUT: Mesg _Token_Tbl
1419 * Return Status
1420 * Purpose : Check_Entity validate the entity for the correct business
1421 * logic. It will verify the values by running checks on inter-
1422 * dependent columns. It will also verify that changes in one
1423 * column value does not invalidate some other columns.
1424 ******************************************************************************/
1425 PROCEDURE Check_Entity
1426 ( x_return_status OUT NOCOPY VARCHAR2
1427 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1428 , p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
1429 , p_Rev_Comp_Unexp_Rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
1430 , p_Old_Rev_Component_Rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
1431 , p_Old_Rev_Comp_Unexp_Rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
1432 )
1433 IS
1434 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1435 l_bill_sequence_id NUMBER;
1436 l_processed BOOLEAN;
1437 l_result NUMBER;
1438 l_Err_Text VARCHAR2(2000);
1439 l_bom_item_type NUMBER;
1440 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1441 l_is_comp_unit_Controlled BOOLEAN := FALSE;
1442
1443 CURSOR c_RefDesgs IS
1444 SELECT count(component_sequence_id) number_of_desgs
1445 FROM bom_reference_designators
1446 WHERE component_sequence_id = p_rev_comp_unexp_rec.component_sequence_id;
1447
1448 CURSOR c_NewBill IS
1449 SELECT revised_item_id,
1450 change_notice,
1451 organization_id
1452 FROM eng_revised_items
1453 WHERE revised_item_sequence_id =
1454 p_rev_comp_Unexp_rec.revised_item_sequence_id;
1455
1456 CURSOR CheckForNew IS
1457 SELECT 'Valid'
1458 FROM eng_revised_items eri
1459 WHERE eri.revised_item_sequence_id =
1460 p_rev_comp_Unexp_rec.revised_item_sequence_id
1461 AND eri.bill_sequence_id IS NULL
1462 AND NOT EXISTS (SELECT 1
1463 FROM bom_bill_of_materials bom
1464 WHERE bom.bill_sequence_id =
1465 p_rev_comp_Unexp_rec.bill_sequence_id
1466 );
1467
1468 item_num_for_bill number := 0;
1469 CURSOR c_ItemNum IS
1470 SELECT 'Valid'
1471 FROM BOM_inventory_components
1472 WHERE item_num = p_rev_component_rec.item_sequence_number
1473 AND bill_sequence_id = p_Rev_Comp_Unexp_rec.bill_sequence_id;
1474
1475 l_bom_ifce_key VARCHAR2(80);
1476
1477 -------------------------------------------------
1478 -- Added since quantity cannot be fractional
1479 -- if OE is installed and revised item is
1480 -- ATO/PTO.
1481
1482 -- Fix made by AS 04/27/98
1483 -- Bug 651689
1484 -------------------------------------------------
1485 Is_OE_Installed VARCHAR2(1) := 'N';
1486
1487 CURSOR c_OE_installed IS
1488 SELECT distinct 'I'
1489 FROM fnd_product_installations
1490 WHERE application_id = 300
1491 AND status = 'I';
1492
1493 Is_Item_ATO VARCHAR2(1) := 'N';
1494 Is_Item_PTO VARCHAR2(1) := 'N';
1495
1496 CURSOR c_ATO_PTO IS
1497 SELECT mi.replenish_to_order_flag, mi.pick_components_flag
1498 FROM mtl_system_items mi, eng_revised_items eri
1499 WHERE mi.inventory_item_id = eri.revised_item_id
1500 AND mi.organization_id = eri.organization_id
1501 AND eri.revised_item_sequence_id =
1502 p_rev_comp_Unexp_rec.revised_item_sequence_id;
1503
1504 Cursor Unit_Controlled_Item IS
1505 SELECT effectivity_control
1506 FROM mtl_system_items
1507 WHERE inventory_item_id = p_rev_comp_unexp_rec.revised_item_id
1508 AND organization_id = p_rev_comp_unexp_rec.organization_id;
1509
1510 l_bill_to_end_number VARCHAR2(30);
1511 CURSOR c_To_End_Item_Number IS
1512 SELECT To_End_Item_Unit_Number
1513 FROM BOM_Inventory_Components
1514 WHERE component_sequence_id =
1515 g_rev_comp_unexp_rec.old_component_sequence_id;
1516
1517 BEGIN
1518
1519 g_rev_component_rec := p_rev_component_rec;
1520 g_Rev_Comp_Unexp_Rec := p_Rev_Comp_Unexp_Rec;
1521
1522 --dbms_output.put_line('Subinventory. . . ' ||
1523 -- g_rev_component_rec.supply_subinventory);
1524 --dbms_output.put_line('Locator . . . ' ||
1525 -- to_char(g_Rev_Comp_Unexp_Rec.supply_locator_id));
1526
1527
1528 --dbms_output.put_line('Performing Revised component Entity Validation . . .');
1529
1530 IF Bom_Globals.Get_Unit_Controlled_Item IS NULL
1531 THEN
1532 FOR Unit_Cont_Item IN Unit_Controlled_Item
1533 LOOP
1534 IF Unit_Cont_Item.Effectivity_Control = 2
1535 THEN
1536 Bom_Globals.Set_Unit_Controlled_Item
1537 ( p_unit_controlled_item => TRUE);
1538 ELSIF Unit_Cont_Item.Effectivity_Control = 1
1539 THEN
1540 Bom_Globals.Set_Unit_Controlled_Item
1541 ( p_unit_controlled_item => FALSE);
1542 END IF;
1543 END LOOP;
1544 END IF;
1545
1546 l_is_comp_unit_controlled := Bom_GLOBALS.Get_Unit_Controlled_Component;
1547
1548 -- First Query all the attributes for the Assembly item and
1549 -- component item.
1550 SELECT assy.bom_item_type,
1551 assy.pick_components_flag,
1552 assy.replenish_to_order_flag,
1553 assy.wip_supply_type,
1554 DECODE(NVL(assy.base_item_id, 0), 0 , 'N', 'Y'),
1555 assy.eng_item_flag,
1556 assy.atp_components_flag,
1557 assy.atp_flag,
1558 assy.bom_enabled_flag,
1559 comp.bom_item_type,
1560 comp.pick_components_flag,
1561 comp.replenish_to_order_flag,
1562 comp.wip_supply_type,
1563 DECODE(NVL(comp.base_item_id, 0), 0 , 'N', 'Y'),
1564 comp.eng_item_flag,
1565 comp.atp_components_flag,
1566 comp.atp_flag,
1567 comp.bom_enabled_flag,
1568 comp.ato_forecast_control
1569 INTO g_Assy_Item_Type,
1570 g_Assy_PTO_flag,
1571 g_Assy_ATO_flag,
1572 g_Assy_Wip_Supply_Type,
1573 g_Assy_Config,
1574 g_Assy_Eng_Flag,
1575 g_Assy_ATP_Comp_flag,
1576 g_Assy_ATP_Check_flag,
1577 g_Assy_Bom_Enabled_flag,
1578 g_Comp_Item_Type,
1579 g_Comp_PTO_flag,
1580 g_Comp_ATO_flag,
1581 g_Comp_Wip_Supply_Type,
1582 g_Comp_Config,
1583 g_Comp_Eng_Flag,
1584 g_Comp_ATP_Comp_flag,
1585 g_Comp_ATP_Check_flag,
1586 g_Comp_Bom_Enabled_flag,
1587 g_Comp_ATO_Forecast_Control
1588 FROM mtl_system_items assy,
1589 mtl_system_items comp
1590 WHERE assy.organization_id = g_rev_Comp_Unexp_Rec.Organization_Id
1591 AND assy.inventory_item_id = g_rev_Comp_Unexp_Rec.revised_item_id
1592 AND comp.organization_id = g_rev_Comp_Unexp_Rec.Organization_Id
1593 AND comp.inventory_item_id = g_rev_Comp_Unexp_Rec.Component_item_id;
1594 --dbms_output.put_line('Queried all assembly and component attributes. . .');
1595
1596 --
1597 -- Set the 1st token of Token Table to Revised Component value
1598 --
1599 g_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
1600 g_Token_Tbl(1).Token_Value := p_rev_component_rec.component_item_name;
1601
1602 -- The ECO can be updated but a warning needs to be generated and
1603 -- scheduled revised items need to be update to Open
1604 -- and the ECO status need to be changed to Not Submitted for Approval
1605
1606
1607 Bom_GLOBALS.Check_Approved_For_Process
1608 (p_change_notice => p_rev_component_rec.eco_name,
1609 p_organization_id => p_rev_comp_unexp_rec.organization_id,
1610 x_processed => l_processed,
1611 x_err_text => l_err_text
1612 );
1613 IF l_processed = TRUE
1614 THEN
1615 -- If the above process returns true then set the ECO approval.
1616 BEGIN
1617 Bom_GLOBALS.Set_Request_For_Approval
1618 (p_change_notice =>
1619 p_rev_component_rec.eco_name,
1620 p_organization_id =>
1621 p_rev_comp_unexp_rec.organization_id,
1622 x_err_text => l_err_text
1623 );
1624
1625 EXCEPTION
1626 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1627 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1628 END;
1629 END IF;
1630
1631 -- To End Item Unit Number must be greater than or equal to
1632 -- From End Item Unit Number
1633
1634 IF NVL(p_rev_component_rec.From_End_Item_Unit_Number, FND_API.G_MISS_CHAR)
1635 >
1636 NVL(p_rev_component_rec.To_End_Item_Unit_Number, FND_API.G_MISS_CHAR)
1637 THEN
1638 g_token_tbl.delete;
1639 g_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
1640 g_token_tbl(1).token_value :=
1641 g_rev_component_rec.component_item_name;
1642 Error_Handler.Add_Error_Token
1643 ( p_message_name => 'ENG_TOUNIT_LESS_THAN_FROMUNIT'
1644 , p_mesg_token_tbl => l_Mesg_Token_Tbl
1645 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1646 , p_token_tbl => g_token_tbl
1647 );
1648
1649 l_return_status := FND_API.G_RET_STS_ERROR;
1650 END IF;
1651
1652 --
1653 -- All validations that only apply to Operation Type CREATE
1654 --
1655 IF p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_CREATE THEN
1656
1657 -- When disabling a component, to end item number must be the same
1658 -- as the value in the bill component being disabled.
1659
1660 IF g_rev_component_rec.acd_type = 3 AND
1661 l_is_comp_unit_controlled
1662 THEN
1663 FOR To_End_Item_Number IN c_To_End_Item_Number
1664 LOOP
1665 l_bill_to_end_number :=
1666 To_End_Item_Number.To_End_Item_Unit_Number;
1667 END LOOP;
1668
1669 IF l_bill_to_end_number <>
1670 p_rev_component_rec.to_end_item_unit_number
1671 THEN
1672 g_token_tbl.delete;
1673 g_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
1674 g_token_tbl(1).token_value :=
1675 g_rev_component_rec.component_item_name;
1676
1677 Error_Handler.Add_Error_Token
1678 ( p_message_name => 'ENG_DISABLE_TOUNIT_INVALID'
1679 , p_mesg_token_tbl => l_Mesg_Token_Tbl
1680 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1681 , p_token_tbl => g_token_tbl
1682 );
1683
1684 l_return_status := FND_API.G_RET_STS_ERROR;
1685 END IF;
1686 END IF;
1687
1688 -- Component can be unit controlled only if parent revised item is
1689 -- unit controlled.
1690 -- Added by AS on 07/08/99 as part of unit effectivity changes
1691
1692 IF g_rev_component_rec.acd_type = 1 AND
1693 l_is_comp_unit_controlled AND
1694 NOT Bom_Globals.Get_Unit_Controlled_Item
1695 THEN
1696 g_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
1697 g_token_tbl(1).token_value :=
1698 g_rev_component_rec.component_item_name;
1699 g_token_tbl(2).token_name := 'REVISED_ITEM_NAME';
1700 g_token_tbl(2).token_value :=
1701 g_rev_component_rec.revised_item_name;
1702
1703 Error_Handler.Add_Error_Token
1704 ( p_message_name => 'ENG_CMP_UNIT_RIT_NOT_UNIT'
1705 , p_mesg_token_tbl => l_Mesg_Token_Tbl
1706 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1707 , p_token_tbl => g_token_tbl
1708 );
1709
1710 l_return_status := FND_API.G_RET_STS_ERROR;
1711 END IF;
1712
1713 -- Unit controlled revised items can only have non-ATO or non-PTO
1714 -- standard items as components.
1715 -- Added by AS on 07/08/99 as part of unit effectivity changes
1716
1717 IF g_rev_component_rec.acd_type = 1 AND
1718 l_is_comp_unit_controlled AND
1719 (g_comp_item_type <> 4 OR
1720 g_comp_ato_flag = 'Y' OR
1721 g_comp_pto_flag = 'Y')
1722 THEN
1723 g_token_tbl.delete;
1724 g_token_tbl(1).token_name := 'REVISED_ITEM_NAME';
1725 g_token_tbl(1).token_value :=
1726 g_rev_component_rec.revised_item_name;
1727
1728 Error_Handler.Add_Error_Token
1729 ( p_message_name => 'ENG_CMP_UNIT_TYPE_NOT_VALID'
1730 , p_mesg_token_tbl => l_Mesg_Token_Tbl
1731 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1732 , p_token_tbl => g_token_tbl
1733 );
1734
1735 l_return_status := FND_API.G_RET_STS_ERROR;
1736 END IF;
1737
1738 --
1739 -- Verify that the Parent has BOM Enabled
1740 --
1741 IF g_Assy_Bom_Enabled_flag <> 'Y'
1742 THEN
1743 g_token_tbl(1).token_name := 'REVISED_ITEM_NAME';
1744 g_token_tbl(1).token_value :=
1745 g_rev_component_rec.revised_item_name;
1746
1747 Error_Handler.Add_Error_Token
1748 ( p_message_name => 'ENG_REV_ITEM_BOM_NOT_ENABLED'
1749 , p_mesg_token_tbl => l_Mesg_Token_Tbl
1750 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1751 , p_token_tbl => g_token_tbl
1752 );
1753
1754 l_return_status := FND_API.G_RET_STS_ERROR;
1755 g_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
1756 g_token_tbl(1).token_value :=
1757 p_rev_component_rec.component_item_name;
1758
1759 END IF;
1760
1761 IF NOT Check_PrimaryBill THEN
1762 --dbms_output.put_line('ACD type to be add if primary bill does not exist . . .');
1763 IF p_rev_component_rec.acd_type <> 1
1764 THEN
1765 /*****************************************************
1766 --
1767 -- If the primary bill does not exist then the acd type
1768 -- of the component cannot be other that add.
1769 --
1770 ******************************************************/
1771 IF FND_MSG_PUB.Check_Msg_Level
1772 (FND_MSG_PUB.G_MSG_LVL_ERROR)
1773 THEN
1774 Error_Handler.Add_Error_Token
1775 ( p_message_name => 'ENG_CMP_ACD_TYPE_ADD'
1776 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1777 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1778 , p_token_tbl => g_token_tbl
1779 );
1780 END IF;
1781 l_return_status := FND_API.G_RET_STS_ERROR;
1782 END IF;
1783
1784 END IF; -- End checking of ACD Type
1785
1786
1787 -- Component cannot be added if the Bill is referencing another
1788 -- bill as common
1789 BEGIN
1790 SELECT 'Valid'
1791 INTO l_dummy
1792 FROM eng_revised_items eri,
1793 bom_bill_of_materials bom
1794 WHERE eri.revised_item_sequence_id =
1795 p_Rev_Comp_Unexp_Rec.revised_item_sequence_id
1796 AND bom.bill_sequence_id = eri.bill_sequence_id
1797 AND bom.common_bill_sequence_id <> bom.bill_sequence_id;
1798
1799 -- If no exception is raised then Bill is referencing another
1800 -- bill
1801 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1802 THEN
1803 g_token_tbl(2).token_name := 'REVISED_ITEM_NAME';
1804 g_token_tbl(2).token_value :=
1805 p_rev_component_rec.revised_item_name;
1806 Error_Handler.Add_Error_Token
1807 ( p_Message_Name => 'ENG_BILL_COMMON'
1808 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1809 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1810 , p_Token_Tbl => g_Token_Tbl
1811 );
1812 END IF;
1813 l_return_status := FND_API.G_RET_STS_ERROR;
1814
1815 EXCEPTION
1816 WHEN NO_DATA_FOUND THEN
1817 NULL;
1818 WHEN OTHERS THEN
1819 IF FND_MSG_PUB.Check_Msg_Level
1820 (FND_MSG_PUB.G_MSG_LVL_ERROR)
1821 THEN
1822 Error_Handler.Add_Error_Token
1823 ( p_message_name => NULL
1824 , p_message_text => 'Common Bill Check ' ||
1825 SUBSTR(SQLERRM, 1, 30) || ' '||
1826 TO_CHAR(SQLCODE)
1827 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1828 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1829 );
1830 END IF;
1831 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1832 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1833
1834 END; -- Check if parent Common Ends
1835
1836 /*****************************************************************
1837 --
1838 -- Verify that the component item and revised item are not the same
1839 --
1840 ******************************************************************/
1841 IF p_rev_comp_unexp_rec.revised_item_id =
1842 p_rev_comp_unexp_rec.component_item_id
1843 THEN
1844 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1845 THEN
1846 g_token_tbl(1).token_name := 'REVISED_ITEM_NAME';
1847 g_token_tbl(1).token_value :=
1848 g_rev_component_rec.revised_item_name;
1849
1850 Error_Handler.Add_Error_Token
1851 ( x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1852 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1853 , p_Message_name => 'ENG_COMP_SAME_AS_BILL'
1854 , p_token_tbl => g_token_tbl
1855 );
1856 END IF;
1857 l_return_status := FND_API.G_RET_STS_ERROR;
1858 g_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
1859 g_token_tbl(1).token_value :=
1860 p_rev_component_rec.component_item_name;
1861
1862 END IF;
1863
1864 /*************************************************************
1865 --
1866 -- Verify BOM_Enabled_Flag of the component being added.
1867 -- Only components with value Yes can be added.
1868 --
1869 **************************************************************/
1870 IF g_Comp_Bom_Enabled_Flag = 'N' THEN
1871 -- Bom_Enabled is N, so cannot add a component.
1872 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1873 THEN
1874 Error_Handler.Add_Error_Token
1875 ( x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1876 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1877 , p_Message_name => 'ENG_COMP_ITEM_BOM_NOT_ENABLED'
1878 , p_token_tbl => g_token_tbl
1879 );
1880 END IF;
1881 l_return_status := FND_API.G_RET_STS_ERROR;
1882 END IF;
1883
1884 /*******************************************************************
1885 --
1886 -- Item Num Check
1887 --
1888 ********************************************************************/
1889 IF p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_CREATE
1890 AND
1891 ( p_rev_component_rec.acd_type = 1 OR
1892 ( p_rev_component_rec.acd_type = 2 AND
1893 p_rev_component_rec.item_sequence_number <>
1894 p_old_rev_component_rec.item_sequence_number
1895 )
1896 )
1897 THEN
1898 -- Verify if a component is already added using this item_num
1899 -- If there is, log a WARNING.
1900
1901 item_num_for_bill := 0;
1902
1903 FOR Item_Num in c_ItemNum LOOP
1904 item_num_for_bill := 1;
1905 END LOOP;
1906
1907 IF item_num_for_bill = 1 THEN
1908 Error_Handler.Add_Error_Token
1909 ( x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1910 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1911 , p_message_name => 'ENG_ITEM_NUM_INVALID'
1912 , p_Token_Tbl => g_Token_Tbl
1913 );
1914 END IF;
1915
1916 END IF; -- Item Num Check ends
1917
1918 /********************************************************************
1919 --
1920 -- Also verify that the component being added is not a Product Family
1921 --
1922 *********************************************************************/
1923
1924 IF g_Comp_Item_Type = 5
1925 THEN
1926 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1927 THEN
1928 Error_Handler.Add_Error_Token
1929 ( p_message_name => 'ENG_ITEM_PRODUCT_FAMILY'
1930 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1931 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1932 , p_token_tbl => g_token_tbl
1933 );
1934 END IF;
1935
1936 END IF ; -- Product Family Check Ends
1937
1938 IF NOT Verify_Item_Attributes( p_Mesg_token_Tbl => l_Mesg_Token_Tbl
1939 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1940 )
1941 THEN
1942 l_return_status := FND_API.G_RET_STS_ERROR;
1943 END IF;
1944
1945 --
1946 -- Verify if the revised item is being referenced as common in
1947 -- other orgs then it satisfies all the criteria required for the
1948 -- component to be on the bill.
1949 --
1950 l_result := Check_Common_Other_Orgs;
1951
1952 --dbms_output.put_line('Check Common_Other_Orgs returned with result ' || to_char(l_result));
1953
1954 IF l_result <> 0
1955 THEN
1956 IF l_result = 1 THEN
1957 l_err_text := 'ENG_COMP_NOTEXIST_IN_OTHER_ORG';
1958 ELSIF l_result = 2 THEN
1959 l_err_text := 'ENG_COMP_NOT_BOMENABLED';
1960
1961 ELSIF l_result = 3 THEN
1962 l_err_text := 'ENG_COMP_NOT_ENG_IN_OTHER_ORGS';
1963 END IF;
1964
1965 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1966 THEN
1967 Error_Handler.Add_Error_Token
1968 ( p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1969 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1970 , p_message_name => l_err_text
1971 , p_token_tbl => g_token_tbl
1972 );
1973 END IF;
1974 l_return_status := FND_API.G_RET_STS_ERROR;
1975 END IF; -- Check if exists in other orgs if common Ends
1976
1977 END IF; -- End of Operation Type CREATE
1978
1979 /**************************************************************************
1980 --
1981 -- Operations specific to the Transaction Type of Update
1982 --
1983 **************************************************************************/
1984
1985 IF p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
1986 THEN
1987
1988 --
1989 -- Verify that the user is not trying to Update non-updateable columns
1990 --
1991 IF p_Old_Rev_Component_Rec.Shipping_Allowed <>
1992 p_rev_component_rec.shipping_allowed
1993 THEN
1994 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1995 THEN
1996 Error_Handler.Add_Error_Token
1997 ( p_message_name => 'ENG_SHIP_ALLOWED_NOT_UPDATE'
1998 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1999 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2000 , p_token_tbl => g_token_tbl
2001 );
2002 END IF;
2003 l_return_status := FND_API.G_RET_STS_ERROR;
2004 END IF;
2005
2006 IF p_rev_component_rec.old_effectivity_date IS NOT NULL AND
2007 p_rev_component_rec.old_effectivity_date <> FND_API.G_MISS_DATE
2008 THEN
2009 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2010 THEN
2011 Error_Handler.Add_Error_Token
2012 ( p_message_name => 'ENG_OLD_EFFECTIVITY_GIVEN'
2013 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2014 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2015 , p_token_tbl => g_token_tbl
2016 );
2017 END IF;
2018 l_return_status := FND_API.G_RET_STS_ERROR;
2019
2020 END IF;
2021
2022 IF p_rev_component_rec.old_operation_sequence_number IS NOT NULL AND
2023 p_rev_component_rec.old_operation_sequence_number <> FND_API.G_MISS_NUM
2024 THEN
2025 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2026 THEN
2027 Error_Handler.Add_Error_Token
2028 ( p_message_name => 'ENG_OLD_OP_SEQ_NUM_GIVEN'
2029 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2030 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2031 , p_token_tbl => g_token_tbl
2032 );
2033 END IF;
2034 l_return_status := FND_API.G_RET_STS_ERROR;
2035
2036 END IF;
2037
2038 --
2039 -- ACD Type not updateable
2040 --
2041 IF p_rev_component_rec.acd_type <>
2042 p_old_rev_component_rec.acd_type
2043 THEN
2044 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2045 THEN
2046 Error_Handler.Add_Error_Token
2047 ( p_message_name => 'ENG_ACD_TYPE_NOT_UPDATEABLE'
2048 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2049 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2050 , p_token_tbl => g_token_tbl
2051 );
2052 END IF;
2053 l_return_status := FND_API.G_RET_STS_ERROR;
2054 END IF;
2055
2056 --
2057 -- Verify that the user is not trying to update a component which
2058 -- is Disabled on the ECO
2059 --
2060 IF p_old_rev_component_rec.acd_type = 3
2061 THEN
2062 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2063 THEN
2064 Error_Handler.Add_Error_Token
2065 ( p_message_name => 'ENG_COMPONENT_DISABLED'
2066 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2067 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2068 , p_token_tbl => g_token_tbl
2069 );
2070 END IF;
2071 l_return_status := FND_API.G_RET_STS_ERROR;
2072 END IF;
2073
2074 -- User cannot update to_end_item_unit_number when the component
2075 -- is disabled.
2076
2077 IF p_rev_component_rec.acd_type = 3 AND
2078 p_rev_component_rec.to_end_item_unit_number <>
2079 p_old_rev_component_rec.to_end_item_unit_number
2080 THEN
2081 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2082 THEN
2083 Error_Handler.Add_Error_Token
2084 ( p_message_name => 'ENG_DISABLE_TOUNIT_NONUPD'
2085 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2086 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2087 , p_token_tbl => g_token_tbl
2088 );
2089 END IF;
2090 l_return_status := FND_API.G_RET_STS_ERROR;
2091 END IF;
2092
2093 END IF; /* Operation UPDATE ENDS */
2094
2095 /*************************************************************************
2096 --
2097 -- All operations that would be common for Create and Update with slight
2098 --variation in
2099 -- checks.
2100 --
2101 **************************************************************************/
2102 IF p_rev_component_rec.Transaction_Type IN
2103 (Bom_GLOBALS.G_OPR_CREATE, Bom_GLOBALS.G_OPR_UPDATE)
2104 THEN
2105 --
2106 -- Verify that the disable date is greater than effectivity date
2107 -- for both operations Create and Update
2108 --
2109
2110 IF p_rev_component_rec.disable_date <
2111 p_rev_component_rec.start_effective_date THEN
2112 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2113 THEN
2114 Error_Handler.Add_Error_Token
2115 ( p_message_name => 'ENG_COMP_DIS_DATE_INVALID'
2116 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2117 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2118 , p_Token_Tbl => g_Token_Tbl
2119 );
2120 END IF;
2121 l_return_status := FND_API.G_RET_STS_ERROR;
2122 END IF;
2123
2124 --dbms_output.put_line('Verified disable date . . . ' || l_return_status);
2125
2126 /********************************************************************
2127 --
2128 -- Verify that the number of reference designators equals the component
2129 -- quantity if quantity related = 1
2130 --
2131 **********************************************************************/
2132 IF p_rev_component_rec.quantity_related = 1
2133 THEN
2134 FOR cnt_desg IN c_RefDesgs
2135 LOOP
2136 IF cnt_desg.number_of_desgs <>
2137 p_rev_component_rec.quantity_per_assembly
2138 THEN
2139 --
2140 -- Give a warning
2141 --
2142 Error_Handler.Add_Error_Token
2143 ( p_message_name => 'ENG_QTY_REL_QTY_REF_DESG'
2144 , p_Mesg_Token_Tbl=> l_Mesg_Token_Tbl
2145 , x_Mesg_Token_Tbl=> l_Mesg_Token_Tbl
2146 , p_Token_Tbl => g_Token_Tbl
2147 );
2148 END IF;
2149 END LOOP;
2150 END IF;
2151
2152 /********************************************************************
2153 --
2154 -- Verify that if the user is trying to create or update rev. comp
2155 -- to quantity related when the quantity_per_assembly is fractional
2156 --
2157 *********************************************************************/
2158 IF round(p_rev_component_rec.quantity_per_assembly) <>
2159 p_rev_component_rec.quantity_per_assembly AND
2160 p_rev_component_rec.quantity_related = 1
2161 THEN
2162 Error_Handler.Add_Error_Token
2163 ( p_message_name => 'ENG_QTY_REL_QTY_FRACTIONAL'
2164 , p_Mesg_Token_Tbl=> l_Mesg_Token_Tbl
2165 , x_Mesg_Token_Tbl=> l_Mesg_Token_Tbl
2166 , p_Token_Tbl => g_Token_Tbl
2167 );
2168
2169 l_return_status := FND_API.G_RET_STS_ERROR;
2170 END IF;
2171
2172 /*********************************************************************
2173 --
2174 -- Verify yield factor
2175 -- IF Component is Option Class or bill is planning
2176 -- then yield must be 1
2177 -- If yield is >0 and less than 1 then give a warning.
2178 --
2179 *********************************************************************/
2180 IF p_rev_component_rec.projected_yield <> 1 THEN
2181 IF g_assy_item_type = 3 -- Planning parent
2182 OR
2183 g_comp_item_type = 2 -- Option Class component
2184 THEN
2185 IF FND_MSG_PUB.Check_Msg_Level
2186 (FND_MSG_PUB.G_MSG_LVL_ERROR)
2187 THEN
2188 g_token_tbl(2).token_name :=
2189 'REVISED_ITEM_NAME';
2190 g_token_tbl(2).token_value :=
2191 g_rev_component_Rec.revised_item_name;
2192 Error_Handler.Add_Error_Token
2193 ( p_Message_Name => 'ENG_COMP_YIELD_NOT_ONE'
2194 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2195 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2196 , p_Token_Tbl => g_Token_Tbl
2197 );
2198 g_token_tbl.delete(2);
2199 END IF;
2200 l_return_status := FND_API.G_RET_STS_ERROR;
2201 END IF;
2202 END IF;
2203
2204 /********************************************************************
2205 --
2206 -- If the Operation is Create with an Acd_Type of Change or Disable
2207 -- then component pointed to by old_component_sequence_id should
2208 -- already be implemented
2209 --
2210 *********************************************************************/
2211
2212 IF (p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_CREATE) AND
2213 p_rev_component_rec.acd_type IN (2, 3)
2214 THEN
2215 --dbms_output.put_line('Old sequence: ' ||
2216 -- to_char(p_rev_comp_Unexp_rec.old_component_sequence_id));
2217 BEGIN
2218 SELECT 'Component Implemented'
2219 INTO l_dummy
2220 FROM bom_inventory_components
2221 WHERE component_sequence_id =
2222 p_rev_comp_Unexp_rec.old_component_sequence_id
2223 AND implementation_date IS NOT NULL;
2224
2225 EXCEPTION
2226 WHEN NO_DATA_FOUND THEN
2227 -- component is yet not implemented
2228 IF FND_MSG_PUB.Check_Msg_Level
2229 (FND_MSG_PUB.G_MSG_LVL_ERROR)
2230 THEN
2231 Error_Handler.Add_Error_Token
2232 ( p_message_name => 'ENG_OLD_COMP_SEQ_ID_INVALID'
2233 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2234 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2235 , p_Token_Tbl => g_Token_Tbl
2236 );
2237 END IF;
2238 l_return_status := FND_API.G_RET_STS_ERROR;
2239 WHEN OTHERS THEN
2240 --dbms_output.put_line(SQLERRM);
2241 Error_Handler.Add_Error_Token
2242 ( p_message_name => NULL
2243 , p_message_text => 'ERROR Rev Cmp entity validate ' ||
2244 SUBSTR(SQLERRM, 1, 30) || ' ' ||
2245 TO_CHAR(SQLCODE)
2246 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2247 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2248 );
2249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2250 END;
2251 END IF;
2252
2253 /********************************************************************
2254 --
2255 -- If Operation_Seq_Num is not NULL then it must be unique in case of
2256 -- Creates and in Case of Updates new_operation_sequence must be valid
2257 -- if the user is trying to update operation_sequence_number
2258 --
2259 *********************************************************************/
2260
2261 IF ( p_rev_component_rec.operation_sequence_number <> 1 AND
2262 p_rev_Component_rec.ACD_Type = 1
2263 ) OR
2264 ( NVL(p_rev_component_rec.new_operation_sequence_number,1) <> 1 AND
2265 ( ( p_rev_component_rec.ACD_Type = 2 AND
2266 p_rev_component_rec.transaction_type =
2267 Bom_Globals.G_OPR_CREATE
2268 ) OR
2269 p_rev_component_rec.transaction_type =
2270 Bom_Globals.G_OPR_UPDATE
2271 ) AND
2272 NVL(p_old_rev_component_rec.operation_sequence_number, 1) <>
2273 NVL(p_rev_component_rec.new_operation_sequence_number, 1)
2274 )
2275 THEN
2276 --dbms_output.put_line('Verifying operation Sequence Number. . . ');
2277 --dbms_output.put_line('Op Seq Num: ' ||
2278 -- to_char(p_rev_component_rec.operation_sequence_number));
2279 --dbms_output.put_line('New Op Seq Num: ' ||
2280 -- to_char(p_rev_component_rec.new_operation_sequence_number));
2281 --dbms_output.put_line('Old Op Seq Num: ' ||
2282 -- to_char(p_rev_component_rec.old_operation_sequence_number));
2283
2284 l_result := Check_Op_Seq;
2285
2286 IF l_result = 1
2287 THEN
2288 IF FND_MSG_PUB.Check_Msg_Level
2289 (FND_MSG_PUB.G_MSG_LVL_ERROR)
2290 THEN
2291 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
2292 g_Token_Tbl(1).Token_Value :=
2293 g_Rev_Component_rec.revised_item_name;
2294 Error_Handler.Add_Error_Token
2295 ( p_Message_Name => 'ENG_OP_SEQ_NUM_INVALID'
2296 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2297 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2298 , p_Token_Tbl => g_Token_Tbl
2299 );
2300 --
2301 -- reset the first token to revised component name.
2302 --
2303 g_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
2304 g_Token_Tbl(1).Token_Value :=
2305 g_Rev_Component_rec.component_item_name;
2306 END IF;
2307 l_return_status := FND_API.G_RET_STS_ERROR;
2308 ELSIF l_result = 2 THEN
2309 IF FND_MSG_PUB.Check_Msg_Level
2310 (FND_MSG_PUB.G_MSG_LVL_ERROR)
2311 THEN
2312 g_Token_Tbl(2).Token_Name := 'OP_SEQ_NUM';
2313 g_Token_Tbl(2).Token_Value :=
2314 to_char
2315 (g_Rev_Component_rec.operation_sequence_number);
2316 Error_Handler.Add_Error_Token
2317 ( p_message_name => 'ENG_OP_SEQ_NUM_NOT_UNIQUE'
2318 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2319 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2320 , p_Token_Tbl => g_Token_Tbl
2321 );
2322 END IF;
2323 g_Token_Tbl.DELETE(2);
2324 l_return_status := FND_API.G_RET_STS_ERROR;
2325 ELSIF l_result = 0 AND
2326 p_Rev_Component_rec.old_operation_sequence_number
2327 IS NOT NULL AND
2328 p_Rev_Component_rec.old_operation_sequence_number <>
2329 FND_API.G_MISS_NUM
2330 THEN
2331 BEGIN
2332 SELECT operation_seq_num
2333 INTO l_result
2334 FROM bom_inventory_components
2335 WHERE component_sequence_id =
2336 p_rev_comp_unexp_rec.old_component_sequence_id
2337 AND operation_seq_num =
2338 p_Rev_Component_rec.old_operation_sequence_number;
2339
2340 EXCEPTION
2341 WHEN OTHERS THEN
2342 l_result := 0;
2343 END;
2344
2345 IF FND_MSG_PUB.Check_Msg_Level
2346 (FND_MSG_PUB.G_MSG_LVL_ERROR) AND
2347 l_result = 0
2348 THEN
2349 g_Token_Tbl(2).Token_Name := 'OLD_OP_SEQUENCE_NUM';
2350 g_Token_Tbl(2).Token_Value :=
2351 to_char
2352 (g_Rev_Component_rec.old_operation_sequence_number);
2353 Error_Handler.Add_Error_Token
2354 ( p_message_name => 'ENG_OLD_OP_SEQ_NUM_GIVEN'
2355 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2356 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2357 , p_Token_Tbl => g_Token_Tbl
2358 );
2359 END IF;
2360 g_Token_Tbl.DELETE(2);
2361 l_return_status := FND_API.G_RET_STS_ERROR;
2362 END IF;
2363 END IF; -- Operation_seq_Num Check Ends.
2364
2365 /********************************************************************
2366 --
2367 -- Check for Overlapping dates for the component being inserted.
2368 --
2369 *********************************************************************/
2370 IF Check_Overlap_Dates
2371 (X_Effectivity_date => p_rev_component_rec.start_effective_date,
2372 X_Disable_date => p_rev_component_rec.disable_date,
2373 X_Member_Item_Id => p_rev_comp_unexp_rec.component_item_id,
2374 X_Bill_Sequence_id => p_rev_comp_unexp_rec.bill_sequence_id,
2375 X_Rowid => NULL)
2376 THEN
2377 --if function return true then component dates overlapp
2378
2379 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
2380 THEN
2381 Error_Handler.Add_Error_Token
2382 ( p_Message_Name => 'ENG_COMP_OPSEQ_DATE_OVERLAP'
2383 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2384 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2385 );
2386 END IF;
2387 -- Since Open Interface allows for Dates to be
2388 -- overlapping do set the error status.
2389 END IF;
2390
2391 --dbms_output.put_line('Verified overlapping dates . . . ' || l_return_status);
2392
2393 /********************************************************************
2394 --
2395 -- Check for Overlapping numbers for the component being inserted.
2396 --
2397 *********************************************************************/
2398 IF Check_Overlap_Numbers
2399 (X_From_end_item_number
2400 => p_rev_component_rec.from_end_item_unit_number,
2401 X_to_end_item_number
2402 => p_rev_component_rec.to_end_item_unit_number,
2403 X_Member_Item_Id => p_rev_comp_unexp_rec.component_item_id,
2404 X_Bill_Sequence_id => p_rev_comp_unexp_rec.bill_sequence_id,
2405 X_Rowid => NULL)
2406 THEN
2407 --if function return true then component dates overlapp
2408
2409 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
2410 THEN
2411 Error_Handler.Add_Error_Token
2412 ( p_Message_Name => 'ENG_COMP_OPSEQ_UNIT_OVERLAP'
2413 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2414 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2415 );
2416 END IF;
2417 -- Since Open Interface allows for Dates to be
2418 -- overlapping do set the error status.
2419 END IF;
2420
2421 --dbms_output.put_line('Verified overlapping unit numbers. . .' || l_return_status);
2422
2423 /*********************************************************************
2424 --
2425 -- Check whether the entered attributes match with the current
2426 -- component attributes
2427 --
2428 **********************************************************************/
2429 IF (( p_rev_component_rec.Transaction_Type=Bom_GLOBALS.G_OPR_CREATE AND
2430 p_rev_component_rec.acd_type = 1
2431 ) OR
2432 (((p_rev_component_rec.Transaction_Type=Bom_GLOBALS.G_OPR_CREATE AND
2433 p_rev_component_rec.acd_type = 2
2434 ) OR
2435 p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
2436 )
2437 AND
2438 NVL(p_Old_rev_component_rec.optional, 0) <>
2439 p_rev_component_rec.optional
2440 )
2441 )
2442 THEN
2443 l_Result := Check_PTO_ATO_for_Optional;
2444 IF l_Result = 1 THEN
2445 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2446 THEN
2447 Error_Handler.Add_Error_Token
2448 ( p_message_name => 'ENG_COMP_OPTIONAL'
2449 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2450 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2451 , p_Token_Tbl => g_Token_Tbl
2452 );
2453 END IF;
2454 l_return_status := FND_API.G_RET_STS_ERROR;
2455 ELSIF l_Result = 2
2456 THEN
2457 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2458 THEN
2459 Error_Handler.Add_Error_Token
2460 ( p_message_name => 'ENG_COMP_NOT_OPTIONAL'
2461 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2462 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2463 );
2464 END IF;
2465 l_return_status := FND_API.G_RET_STS_ERROR;
2466 END IF;
2467 END IF;
2468
2469 --dbms_output.put_line('Verified PTO / ATO . . .' || l_return_status);
2470
2471 /*********************************************************************
2472 --
2473 -- Planning Percent can be other than 100 for only some combination of
2474 -- Assembly and component_types.
2475 --
2476 **********************************************************************/
2477 --
2478 IF (( p_rev_component_rec.Transaction_Type=Bom_GLOBALS.G_OPR_CREATE AND
2479 p_rev_component_rec.acd_type = 1 AND
2480 p_rev_component_rec.planning_percent <> 100
2481 ) OR
2482 (((p_rev_component_rec.Transaction_Type=Bom_GLOBALS.G_OPR_CREATE AND
2483 p_rev_component_rec.acd_type = 2
2484 ) OR
2485 p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
2486 )
2487 AND
2488 NVL(p_Old_rev_component_rec.planning_percent, 0) <>
2489 p_rev_component_rec.planning_percent
2490 )
2491 )
2492 THEN
2493 l_Result := Check_Planning_Percent;
2494 IF l_Result = 1 THEN
2495 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2496 THEN
2497 g_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
2498 g_Token_Tbl(2).Token_Value :=
2499 g_rev_component_rec.revised_item_name;
2500 Error_Handler.Add_Error_Token
2501 ( p_Message_Name => 'ENG_NOT_A_PLANNING_PARENT'
2502 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2503 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2504 , p_Token_Tbl => g_Token_Tbl
2505 );
2506 g_Token_Tbl.DELETE(2);
2507 END IF;
2508 l_return_status := FND_API.G_RET_STS_ERROR;
2509 ELSIF l_Result = 2 THEN
2510 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2511 THEN
2512 g_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
2513 g_Token_Tbl(2).Token_Value :=
2514 g_rev_component_rec.revised_item_name;
2515 g_Token_Tbl(3).Token_Name := 'ITEM_TYPE';
2516 IF g_Assy_Item_Type = l_MODEL
2517 THEN
2518 g_Token_Tbl(3).Token_Value := 'ENG_MODEL_TYPE';
2519 ELSIF g_Assy_Item_Type = l_OPTION_CLASS THEN
2520 g_Token_Tbl(3).Token_Value :=
2521 'ENG_OPTION_CLASS_TYPE';
2522 END IF;
2523 g_Token_Tbl(3).Translate := TRUE;
2524 Error_Handler.Add_Error_Token
2525 ( p_Message_Name => 'ENG_COMP_MODEL_OC_OPTIONAL'
2526 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2527 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2528 , p_Token_Tbl => g_Token_Tbl
2529 );
2530 g_Token_Tbl.DELETE(2);
2531 g_Token_Tbl.DELETE(3);
2532 l_return_status := FND_API.G_RET_STS_ERROR;
2533 END IF;
2534 ELSIF l_Result = 3 THEN
2535 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2536 THEN
2537 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
2538 g_Token_Tbl(1).Token_Value :=
2539 g_rev_component_rec.revised_item_name;
2540
2541 Error_Handler.Add_Error_Token
2542 ( p_Message_Name =>
2543 'ENG_COMP_OPTIONAL_ATO_FORECAST'
2544 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2545 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2546 , p_Token_Tbl => g_Token_Tbl
2547 );
2548 g_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
2549 g_Token_Tbl(1).Token_Value :=
2550 g_rev_component_rec.component_item_name;
2551
2552 END IF;
2553 l_return_status := FND_API.G_RET_STS_ERROR;
2554 END IF; -- If Result Ends
2555 END IF; -- If Plannng <> 100 Ends
2556
2557 --dbms_output.put_line('Verified Planning % . . .' || l_return_status);
2558
2559 /*********************************************************************
2560 --
2561 -- Check Required for Revenue / Required to Ship
2562 --
2563 **********************************************************************/
2564 IF (( p_rev_component_rec.Transaction_Type=Bom_GLOBALS.G_OPR_CREATE AND
2565 p_rev_component_rec.acd_type = 1 AND
2566 ( p_rev_component_rec.required_for_revenue = 1 OR
2567 p_rev_component_rec.required_to_ship = 1
2568 )
2569 ) OR
2570 (((p_rev_component_rec.Transaction_Type=Bom_GLOBALS.G_OPR_CREATE AND
2571 p_rev_component_rec.acd_type = 2
2572 ) OR
2573 p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
2574 )
2575 AND
2576 ( NVL(p_Old_rev_component_rec.required_for_revenue, 0) <>
2577 p_rev_component_rec.required_for_revenue OR
2578 NVL(p_old_rev_component_rec.required_to_ship, 0) <>
2579 p_rev_component_rec.required_to_ship
2580 )
2581 )
2582 )
2583 THEN
2584
2585 l_Result := Chk_Req_For_Rev_Or_Shp;
2586 IF l_Result = 1 THEN
2587 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2588 THEN
2589 g_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
2590 g_Token_Tbl(2).Token_Value :=
2591 g_rev_component_rec.revised_item_name;
2592 Error_Handler.Add_Error_Token
2593 ( p_message_name => 'ENG_COMP_REQ_FOR_REV_INVALID'
2594 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2595 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2596 , p_Token_Tbl => g_Token_Tbl
2597 );
2598 g_Token_Tbl.DELETE(2);
2599 END IF;
2600 l_return_status := FND_API.G_RET_STS_ERROR;
2601 ELSIF l_Result = 2 THEN
2602 g_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
2603 g_Token_Tbl(2).Token_Value :=
2604 g_rev_component_rec.revised_item_name;
2605 Error_Handler.Add_Error_Token
2606 ( p_message_name => 'ENG_COMP_REQ_TO_SHIP_INVALID'
2607 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2608 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2609 , p_Token_Tbl => g_Token_Tbl
2610 );
2611 g_Token_Tbl.DELETE(2);
2612 l_return_status := FND_API.G_RET_STS_ERROR;
2613 ELSIF l_Result = 3 THEN
2614 g_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
2615 g_Token_Tbl(2).Token_Value :=
2616 g_rev_component_rec.revised_item_name;
2617 Error_Handler.Add_Error_Token
2618 ( p_message_name => 'ENG_COMP_REQ_TO_SHIP_INVALID'
2619 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2620 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2621 , p_Token_Tbl => g_Token_Tbl
2622 );
2623
2624 g_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
2625 g_Token_Tbl(2).Token_Value :=
2626 g_rev_component_rec.revised_item_name;
2627 Error_Handler.Add_Error_Token
2628 ( p_message_name => 'ENG_COMP_REQ_FOR_REV_INVALID'
2629 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2630 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2631 , p_Token_Tbl => g_Token_Tbl
2632 );
2633
2634 g_Token_Tbl.DELETE(2);
2635 l_return_status := FND_API.G_RET_STS_ERROR;
2636 END IF;
2637 END IF;
2638
2639 --dbms_output.put_line('Verified Req for Rev and Shipping . . . ' ||
2640 -- l_return_status);
2641
2642 /*********************************************************************
2643 --
2644 -- Verify the value of SO_Basis
2645 --
2646 *********************************************************************/
2647 IF p_rev_component_rec.so_basis = 1 AND
2648 g_Comp_Item_Type <> l_OPTION_CLASS
2649 THEN
2650 Error_Handler.Add_Error_Token
2651 ( p_message_name => 'ENG_SO_BASIS_ONE'
2652 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2653 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2654 , p_Token_Tbl => g_Token_Tbl
2655 );
2656 l_Return_Status := FND_API.G_RET_STS_ERROR;
2657 END IF;
2658
2659 /********************************************************************
2660 --
2661 -- Check Check_ATP Flag. Check_ATP can be True only if Assembly has
2662 -- Atp Components flag = Y and the Component has a Check_ATP.
2663 --
2664 ********************************************************************/
2665 IF (( p_rev_component_rec.Transaction_Type=Bom_GLOBALS.G_OPR_CREATE AND
2666 p_rev_component_rec.acd_type = 1 AND
2667 p_rev_component_rec.check_atp = 1
2668 ) OR
2669 ( ( ( p_rev_component_rec.Transaction_Type =
2670 Bom_GLOBALS.G_OPR_CREATE AND
2671 p_rev_component_rec.acd_type = 2
2672 ) OR
2673 p_rev_component_rec.Transaction_Type =
2674 Bom_GLOBALS.G_OPR_UPDATE
2675 ) AND
2676 NVL(p_Old_rev_component_rec.check_atp, 0) <>
2677 p_rev_component_rec.check_atp
2678 )
2679 )
2680 THEN
2681 l_result := Check_ATP;
2682 /* We will not be using the result of the check_atp procedure
2683 to decide the translatable token since the message text
2684 is now changed. Please refer to text for ENG_ATP_CHECK_NOT_NO
2685 */
2686 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) AND
2687 l_result <> 0
2688 THEN
2689 Error_Handler.Add_Error_Token
2690 ( p_message_name => 'ENG_ATP_CHECK_NOT_NO'
2691 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2692 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2693 , p_Token_Tbl => g_Token_Tbl
2694 );
2695 g_Token_Tbl.DELETE(2);
2696 l_return_status := FND_API.G_RET_STS_ERROR;
2697 END IF;
2698 END IF;
2699
2700 --dbms_output.put_line('After verification of Check ATP, Req for Rev' );
2701
2702 /********************************************************************
2703 --
2704 -- Check Mutually Exclusive, which can be set only if the
2705 -- Component is an Option Class and BOM is installed.
2706 --
2707 *********************************************************************/
2708
2709 IF ( p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_CREATE OR
2710 p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
2711 )
2712 AND
2713 p_rev_component_rec.mutually_exclusive = 1
2714 THEN
2715 l_result := Check_Mutually_Exclusive;
2716 IF l_result <> 0 THEN
2717 IF l_result = 1 THEN
2718 l_err_text := 'ENG_MUT_EXCL_BOM_NOT_INST';
2719 ELSIF l_result = 2 THEN
2720 l_err_text := 'ENG_MUT_EXCL_NOT_MDL_OPTCLASS';
2721 END IF;
2722
2723 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2724 THEN
2725 Error_Handler.Add_Error_Token
2726 ( p_message_name => l_err_text
2727 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2728 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2729 , p_Token_Tbl => g_Token_Tbl
2730 );
2731 END IF;
2732 l_return_status := FND_API.G_RET_STS_ERROR;
2733 END IF;
2734 END IF;
2735
2736 --dbms_output.put_line('After verification of Mutually exclusive . . .' ||
2737 -- l_return_status);
2738
2739 -- So process can continue in case of a warning. Since it has
2740 -- indecisive o/p to continue or not, the function will
2741 -- log the error or warning and return TRUE if warning
2742 -- so the process can continue or will return an FALSE if
2743 -- process needs to return
2744 IF (( p_rev_component_rec.Transaction_Type=Bom_GLOBALS.G_OPR_CREATE AND
2745 p_rev_component_rec.acd_type = 1 AND
2746 p_rev_component_rec.wip_supply_type IS NOT NULL
2747 ) OR
2748 (((p_rev_component_rec.Transaction_Type=Bom_GLOBALS.G_OPR_CREATE AND
2749 p_rev_component_rec.acd_type = 2
2750 ) OR
2751 p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
2752 )
2753 AND
2754 NVL(p_Old_rev_component_rec.wip_supply_type, 0) <>
2755 p_rev_component_rec.wip_supply_type
2756 )
2757 )
2758 AND
2759 Check_Supply_Type
2760 ( p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2761 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl ) = FALSE
2762 THEN
2763 l_return_status := FND_API.G_RET_STS_ERROR;
2764 END IF;
2765
2766 --dbms_output.put_line
2767 --('After verification of Supply Type . . .' || l_return_status);
2768
2769 -- Check Minimum Quantity which must be <= Component Quantity
2770 IF ( p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_CREATE OR
2771 p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
2772 ) AND
2773 p_rev_component_rec.minimum_allowed_quantity is not null
2774 AND
2775 NOT Check_Min_Quantity THEN
2776 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2777 THEN
2778 Error_Handler.Add_Error_Token
2779 ( p_message_name => 'ENG_MIN_QUANTITY_INVALID'
2780 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2781 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2782 , p_Token_Tbl => g_Token_Tbl
2783 );
2784 END IF;
2785 l_return_status := FND_API.G_RET_STS_ERROR;
2786 END IF;
2787
2788 -- Check Maximun Quantity which must be >= Component Quantity or
2789 -- should be NULL if the minimum quantity is NULL.
2790 IF ( p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_CREATE OR
2791 p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
2792 ) AND
2793 p_rev_component_rec.maximum_allowed_quantity IS NOT NULL
2794 AND
2795 NOT Check_Max_Quantity THEN
2796 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2797 THEN
2798 Error_Handler.Add_Error_Token
2799 ( p_message_name => 'ENG_MAX_QUANTITY_INVALID'
2800 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2801 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2802 , p_Token_Tbl => g_Token_Tbl
2803 );
2804 END IF;
2805 l_return_status := FND_API.G_RET_STS_ERROR;
2806 END IF;
2807
2808 --dbms_output.put_line('After verification of Min / Max quantity . . .');
2809
2810 -------------------------------------------------
2811 -- Required since quantity cannot be fractional
2812 -- if OE is installed and revised item is
2813 -- ATO/PTO.
2814 -- Fix made by AS 04/27/98
2815 -- Bug 651689
2816 -------------------------------------------------
2817
2818 OPEN c_OE_Installed;
2819 FETCH c_OE_Installed INTO Is_OE_Installed;
2820 CLOSE c_OE_Installed;
2821
2822 Is_Item_ATO := 'N';
2823 IS_Item_PTO := 'N';
2824
2825 FOR Is_Item_ATO_PTO IN c_ATO_PTO
2826 LOOP
2827 Is_Item_ATO := Is_Item_ATO_PTO.replenish_to_order_flag;
2828 Is_Item_PTO := Is_Item_ATO_PTO.pick_components_flag;
2829 END LOOP;
2830
2831 IF ( p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_CREATE OR
2832 p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
2833 ) AND
2834 (Is_OE_Installed = 'I'
2835 AND ( ( Is_Item_ATO = 'Y' OR
2836 Is_Item_PTO = 'Y'
2837 ) AND
2838 (round(p_rev_component_rec.quantity_per_assembly)
2839 <> p_rev_component_rec.quantity_per_assembly)
2840 )
2841 )
2842 THEN
2843 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2844 THEN
2845 Error_Handler.Add_Error_Token
2846 ( p_message_name => 'ENG_COMP_QTY_FRACTIONAL'
2847 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2848 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2849 , p_Token_Tbl => g_Token_Tbl
2850 );
2851 END IF;
2852 l_return_status := FND_API.G_RET_STS_ERROR;
2853 END IF;
2854
2855 --dbms_output.put_line('Checked if fractional quantity is valid . . .' ||
2856 -- l_return_status);
2857
2858
2859 /********************************************************************
2860 --
2861 -- Verify if the Check_Atp is Yes and the Component quantity is
2862 -- negative. If it is then give out an error.
2863 --
2864 ********************************************************************/
2865
2866 IF g_Comp_Atp_Check_Flag = 'Y' AND
2867 p_rev_component_rec.quantity_per_assembly < 0
2868 THEN
2869 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2870 THEN
2871 Error_Handler.Add_Error_Token
2872 ( p_message_name => 'ENG_COMP_QTY_NEGATIVE'
2873 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2874 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2875 , p_Token_Tbl => g_Token_Tbl
2876 );
2877 END IF;
2878 l_return_status := FND_API.G_RET_STS_ERROR;
2879 END IF;
2880
2881 /********************************************************************
2882 --
2883 -- If component is a PTO Option Class, then component quantity cannot
2884 -- be negative
2885 --
2886 ********************************************************************/
2887 IF g_Comp_PTO_Flag = 'Y' AND
2888 p_rev_component_rec.quantity_per_assembly < 0
2889 THEN
2890 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2891 THEN
2892 Error_Handler.Add_Error_Token
2893 ( p_message_name => 'ENG_COMP_PTO_QTY_NEGATIVE'
2894 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2895 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2896 , p_Token_Tbl => g_Token_Tbl
2897 );
2898 END IF;
2899 l_return_status := FND_API.G_RET_STS_ERROR;
2900 END IF;
2901
2902 --dbms_output.put_line('Checking Supply Subinventory . . . ' ||
2903 -- p_rev_component_rec.Supply_SubInventory);
2904
2905 /*******************************************************************
2906 --
2907 -- Check Supply Subinventory
2908 --
2909 ********************************************************************/
2910 IF (( p_rev_component_rec.Transaction_Type=Bom_GLOBALS.G_OPR_CREATE AND
2911 p_rev_component_rec.acd_type = 1 AND
2912 p_rev_component_rec.Supply_SubInventory IS NOT NULL
2913 ) OR
2914 (((p_rev_component_rec.Transaction_Type=Bom_GLOBALS.G_OPR_CREATE AND
2915 p_rev_component_rec.acd_type = 2
2916 ) OR
2917 p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
2918 )
2919 AND
2920 NVL(p_Old_rev_component_rec.supply_subinventory, 'NONE') <>
2921 NVL(p_rev_component_rec.supply_subinventory, 'NONE')
2922 )
2923 )
2924 AND
2925 NOT Check_Supply_SubInventory THEN
2926
2927 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2928 THEN
2929 Error_Handler.Add_Error_Token
2930 ( p_message_name => 'ENG_SUBINV_INVALID'
2931 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2932 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2933 );
2934 END IF;
2935 l_return_status := FND_API.G_RET_STS_ERROR;
2936 END IF;
2937 --dbms_output.put_line('After checking Subinventory . . . ' || l_return_status);
2938
2939 --dbms_output.put_line('Checking Locators . . . .');
2940 /********************************************************************
2941 --
2942 -- Check Locators
2943 --
2944 ********************************************************************/
2945 IF (( p_rev_component_rec.Transaction_Type=Bom_GLOBALS.G_OPR_CREATE AND
2946 p_rev_component_rec.acd_type = 1 AND
2947 p_rev_component_rec.Supply_SubInventory IS NOT NULL AND
2948 p_rev_component_rec.Supply_SubInventory <> FND_API.G_MISS_CHAR
2949 ) OR
2950 ((( p_rev_component_rec.Transaction_Type=Bom_GLOBALS.G_OPR_CREATE AND
2951 p_rev_component_rec.acd_type = 2
2952 ) OR
2953 p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
2954 )
2955 AND
2956 NVL(p_Old_rev_comp_unexp_rec.supply_locator_id, 0) <>
2957 NVL(p_rev_comp_unexp_rec.supply_locator_id, 0)
2958 )
2959 )
2960 AND
2961 NOT Check_Locators
2962 THEN
2963 --dbms_output.put_line('Locators check returned with an error-' ||
2964 -- to_char(l_locator_control));
2965
2966 IF l_locator_control = 4 THEN
2967 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2968 THEN
2969 Error_Handler.Add_Error_Token
2970 ( p_message_name => 'ENG_LOCATOR_REQUIRED'
2971 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2972 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2973 , p_Token_Tbl => g_Token_Tbl
2974 );
2975 END IF;
2976 ELSIF l_locator_control = 3 THEN
2977 -- Log the Dynamic locator control message.
2978 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2979 THEN
2980 Error_Handler.Add_Error_Token
2981 ( p_message_name => 'ENG_LOCATOR_CANNOT_BE_DYNAMIC'
2982 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2983 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2984 , p_Token_Tbl => g_Token_Tbl
2985 );
2986 END IF;
2987 ELSIF l_locator_control = 2 THEN
2988 IF l_item_loc_restricted = 1 THEN
2989
2990 -- if error occured when item_locator_control was
2991 -- restrcited
2992
2993 IF FND_MSG_PUB.Check_Msg_Level
2994 (FND_MSG_PUB.G_MSG_LVL_ERROR)
2995 THEN
2996 Error_Handler.Add_Error_Token
2997 ( p_message_name => 'ENG_ITEM_LOCATOR_RESTRICTED'
2998 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2999 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3000 , p_Token_Tbl => g_Token_Tbl
3001 );
3002 END IF;
3003 ELSE
3004 IF FND_MSG_PUB.Check_Msg_Level
3005 (FND_MSG_PUB.G_MSG_LVL_ERROR)
3006 THEN
3007 g_Token_Tbl(1).token_name := 'REVISED_COMPONENT_NAME';
3008 g_Token_Tbl(1).token_value:= p_rev_component_rec.component_item_name;
3009
3010 Error_Handler.Add_Error_Token
3011 ( p_message_name => 'ENG_LOCATOR_NOT_IN_SUBINV'
3012 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3013 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3014 , p_Token_Tbl => g_Token_Tbl
3015 );
3016 END IF;
3017 END IF;
3018 ELSIF l_locator_control = 1 THEN
3019 IF FND_MSG_PUB.Check_Msg_Level
3020 (FND_MSG_PUB.G_MSG_LVL_ERROR)
3021 THEN
3022 Error_Handler.Add_Error_Token
3023 ( p_message_name => 'ENG_ITEM_NO_LOCATOR_CONTROL'
3024 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3025 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3026 , p_Token_Tbl => g_Token_Tbl
3027 );
3028 END IF;
3029
3030 END IF;
3031 l_return_status := FND_API.G_RET_STS_ERROR;
3032 ELSIF p_rev_component_rec.location_name IS NOT NULL AND
3033 p_rev_component_rec.supply_subinventory IS NULL
3034 THEN
3035 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3036 THEN
3037 Error_Handler.Add_Error_Token
3038 ( p_message_name => 'ENG_LOCATOR_MUST_BE_NULL'
3039 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3040 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3041 , p_Token_Tbl => g_Token_Tbl
3042 );
3043 END IF;
3044 l_return_status := FND_API.G_RET_STS_ERROR;
3045 END IF;
3046
3047 --dbms_output.put_line('Operation CREATE ENDS . . .' || l_return_status);
3048
3049 END IF; -- Operation in UPDATE or CREATE
3050
3051 x_return_status := l_return_status;
3052 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3053
3054 --dbms_output.put_line('Entity Validation done . . . Return Status is ' ||
3055 -- l_return_status);
3056
3057 EXCEPTION
3058
3059 WHEN FND_API.G_EXC_ERROR THEN
3060
3061 --dbms_output.put_line('Expected Error in Rev. Comp. Entity Validation . . .');
3062
3063 x_return_status := FND_API.G_RET_STS_ERROR;
3064 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3065
3066 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3067
3068 --dbms_output.put_line('UNExpected Error in Rev. Comp. Entity Validation . . .');
3069
3070 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3071 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3072
3073 WHEN OTHERS THEN
3074 --dbms_output.put_line(SQLERRM || ' ' || TO_CHAR(SQLCODE));
3075 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3076 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3077
3078 END Check_Entity;
3079
3080 /*****************************************************************************
3081 * Procedure : Check_Attribute (Validation)
3082 * Parameters IN : Revised Component Record of exposed columns
3083 * Parameters OUT: Mesg_Token_Tbl
3084 * Return_Status
3085 * Purpose : Attribute validation procedure will validate each attribute
3086 * of Revised component in its entirety. If the validation of
3087 * a column requires looking at some other columns value then
3088 * the validation is done at the Entity level instead.
3089 * All errors in the attribute validation are accumulated before
3090 * the procedure returns with a Return_Status of 'E'.
3091 Item**************************************************************************/
3092 PROCEDURE Check_Attributes
3093 ( x_return_status OUT NOCOPY VARCHAR2
3094 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3095 , p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
3096 , p_Rev_Comp_Unexp_Rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
3097 )
3098 IS
3099 l_return_status VARCHAR2(1);
3100 l_err_text VARCHAR2(2000);
3101 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3102 BEGIN
3103
3104 x_return_status := FND_API.G_RET_STS_SUCCESS;
3105 l_return_status := FND_API.G_RET_STS_SUCCESS;
3106
3107 g_rev_component_rec := p_rev_component_rec;
3108
3109 -- Set the first token to be equal to the component_name
3110 g_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
3111 g_Token_Tbl(1).Token_Value := p_rev_component_rec.component_item_name;
3112
3113
3114 --
3115 -- Check if the user is trying to create/update a record with missing
3116 -- value when the column value is required.
3117 --
3118 IF p_rev_component_rec.item_sequence_number = FND_API.G_MISS_NUM
3119 THEN
3120 Error_Handler.Add_Error_Token
3121 ( p_Message_Name => 'ENG_ITEM_NUM_MISSING'
3122 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3123 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3124 , p_Token_Tbl => g_Token_Tbl
3125 );
3126 l_return_status := FND_API.G_RET_STS_ERROR;
3127 END IF;
3128
3129 IF p_rev_component_rec.quantity_per_assembly = FND_API.G_MISS_NUM
3130 THEN
3131 Error_Handler.Add_Error_Token
3132 ( p_Message_Name => 'ENG_COMP_QUANTITY_MISSING'
3133 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3134 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3135 , p_Token_Tbl => g_Token_Tbl
3136 );
3137 l_return_status := FND_API.G_RET_STS_ERROR;
3138 END IF;
3139
3140 IF p_rev_component_rec.projected_yield = FND_API.G_MISS_NUM
3141 THEN
3142 Error_Handler.Add_Error_Token
3143 ( p_Message_Name => 'ENG_COMP_YIELD_MISSING'
3144 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3145 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3146 , p_Token_Tbl => g_Token_Tbl
3147 );
3148 l_return_status := FND_API.G_RET_STS_ERROR;
3149 END IF;
3150
3151 IF p_rev_component_rec.planning_percent = FND_API.G_MISS_NUM
3152 THEN
3153 Error_Handler.Add_Error_Token
3154 ( p_Message_Name => 'ENG_PLAN_PERCENT_MISSING'
3155 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3156 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3157 , p_Token_Tbl => g_Token_Tbl
3158 );
3159 l_return_status := FND_API.G_RET_STS_ERROR;
3160 END IF;
3161
3162 IF p_rev_component_rec.quantity_related = FND_API.G_MISS_NUM
3163 THEN
3164 Error_Handler.Add_Error_Token
3165 ( p_Message_Name => 'ENG_QUANTITY_RELATED_MISSING'
3166 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3167 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3168 , p_Token_Tbl => g_Token_Tbl
3169 );
3170 l_return_status := FND_API.G_RET_STS_ERROR;
3171 END IF;
3172
3173 IF p_rev_component_rec.include_in_cost_rollup = FND_API.G_MISS_NUM
3174 THEN
3175 Error_Handler.Add_Error_Token
3176 ( p_Message_Name => 'ENG_INCL_IN_COST_ROLLUP_MISSING'
3177 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3178 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3179 , p_Token_Tbl => g_Token_Tbl
3180 );
3181 l_return_status := FND_API.G_RET_STS_ERROR;
3182 END IF;
3183
3184 IF p_rev_component_rec.check_atp = FND_API.G_MISS_NUM
3185 THEN
3186 Error_Handler.Add_Error_Token
3187 ( p_Message_Name => 'ENG_CHECK_ATP_MISSING'
3188 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3189 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3190 , p_Token_Tbl => g_Token_Tbl
3191 );
3192 l_return_status := FND_API.G_RET_STS_ERROR;
3193 END IF;
3194
3195
3196 IF p_rev_component_rec.acd_type IS NOT NULL AND
3197 p_rev_component_rec.acd_type NOT IN (1, 2, 3)
3198 THEN
3199 g_token_tbl(2).token_name := 'ACD_TYPE';
3200 g_token_tbl(2).token_value := p_rev_component_rec.acd_type;
3201
3202 Error_Handler.Add_Error_Token
3203 ( p_Message_Name => 'ENG_ACD_TYPE_INVALID'
3204 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3205 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3206 , p_Token_Tbl => g_Token_Tbl
3207 );
3208 l_return_status := FND_API.G_RET_STS_ERROR;
3209 END IF;
3210
3211 -- Validate rev_component attributes
3212
3213 /* -- Not necessary since the UUI to UI conversions take care of this
3214
3215 IF p_rev_component_rec.transaction_type = Bom_GLOBALS.G_OPR_CREATE AND
3216 (p_rev_component_rec.from_end_item_unit_number IS NOT NULL
3217 OR
3218 p_rev_component_rec.from_end_item_unit_number <> FND_API.G_MISS_CHAR)
3219 THEN
3220 IF NOT ENG_Validate.End_Item_Unit_Number
3221 ( p_from_end_item_unit_number =>
3222 p_rev_component_rec.from_end_item_unit_number
3223 , p_revised_item_id =>
3224 p_rev_component_rec.component_item_id
3225 , x_err_text => l_err_text
3226 )
3227 THEN
3228 x_return_status := FND_API.G_RET_STS_ERROR;
3229 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3230 THEN
3231 g_token_tbl(1).token_name := 'FROM_END_ITEM_UNIT_NUMBER';
3232 g_token_tbl(1).token_value :=
3233 p_rev_component_rec.from_end_item_unit_number;
3234 Error_Handler.Add_Error_Token
3235 ( p_Message_Name => 'ENG_FROM_END_ITEM_INVALID'
3236 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3237 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3238 , p_Token_Tbl => g_Token_Tbl
3239 );
3240 END IF;
3241 END IF;
3242 END IF;
3243 */
3244
3245 IF p_rev_component_rec.transaction_type = Bom_GLOBALS.G_OPR_CREATE AND
3246 (p_rev_component_rec.to_end_item_unit_number IS NOT NULL
3247 OR
3248 p_rev_component_rec.to_end_item_unit_number <> FND_API.G_MISS_CHAR)
3249 THEN
3250 IF NOT ENG_Validate.End_Item_Unit_Number
3251 ( p_from_end_item_unit_number =>
3252 p_rev_component_rec.to_end_item_unit_number
3253 , p_revised_item_id =>
3254 p_rev_comp_unexp_rec.component_item_id
3255 , x_err_text => l_err_text
3256 )
3257 THEN
3258 x_return_status := FND_API.G_RET_STS_ERROR;
3259 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3260 THEN
3261 g_token_tbl(1).token_name := 'FROM_END_ITEM_UNIT_NUMBER';
3262 g_token_tbl(1).token_value :=
3263 p_rev_component_rec.to_end_item_unit_number;
3264 g_token_tbl(2).token_name := 'REVISED_COMPONENT_NAME';
3265 g_token_tbl(2).token_value :=
3266 p_rev_component_rec.component_item_name;
3267 g_token_tbl(3).token_name := 'ORGANIZATION_CODE';
3268 g_token_tbl(3).token_value :=
3269 p_rev_component_rec.organization_code;
3270 Error_Handler.Add_Error_Token
3271 ( p_Message_Name => 'ENG_CMP_TO_UNIT_NUM_INVALID'
3272 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3273 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3274 , p_Token_Tbl => g_Token_Tbl
3275 );
3276 END IF;
3277 END IF;
3278 END IF;
3279
3280 IF p_rev_component_rec.transaction_type = Bom_GLOBALS.G_OPR_CREATE AND
3281 p_rev_component_rec.to_end_item_unit_number = FND_API.G_MISS_CHAR
3282 THEN
3283 Error_Handler.Add_Error_Token
3284 ( p_Message_Name => 'ENG_CMP_TO_UNIT_NUM_NULL'
3285 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3286 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3287 , p_Token_Tbl => g_Token_Tbl
3288 );
3289 l_return_status := FND_API.G_RET_STS_ERROR;
3290 END IF;
3291
3292 IF p_rev_component_rec.wip_supply_type IS NOT NULL AND
3293 p_rev_component_rec.wip_supply_type = 7
3294 THEN
3295 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3296 THEN
3297 g_token_tbl(2).token_name := 'WIP_SUPPLY_TYPE';
3298 g_token_tbl(2).token_value :=
3299 p_rev_component_rec.wip_supply_type;
3300 Error_Handler.Add_Error_Token
3301 ( p_Message_Name => 'ENG_WIP_SUPPLY_TYPE_7'
3302 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3303 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3304 , p_Token_Tbl => g_Token_Tbl
3305 );
3306 g_token_tbl.delete(2);
3307 END IF;
3308 l_return_status := FND_API.G_RET_STS_ERROR;
3309
3310 ELSIF p_rev_component_rec.wip_supply_type IS NOT NULL AND
3311 p_rev_component_rec.wip_supply_type <> 7 AND
3312 NOT ENG_Validate.Wip_Supply_Type
3313 ( p_wip_supply_type => p_rev_component_rec.wip_supply_type
3314 , x_err_text => l_err_text
3315 )
3316 THEN
3317 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
3318 THEN
3319 g_token_tbl(1).token_name := 'WIP_SUPPLY_TYPE';
3320 g_token_tbl(1).token_value :=
3321 p_rev_component_rec.wip_supply_type;
3322
3323 Error_Handler.Add_Error_Token
3324 ( p_Message_Name => 'ENG_WIP_SUPPLY_TYPE_INVALID'
3325 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3326 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3327 , p_Token_Tbl => g_Token_Tbl
3328 );
3329 g_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
3330 g_token_tbl(1).token_value :=
3331 p_rev_component_rec.component_item_name;
3332
3333 END IF;
3334 l_return_status := FND_API.G_RET_STS_ERROR;
3335 END IF;
3336
3337 IF p_rev_component_rec.operation_sequence_number IS NOT NULL AND
3338 p_rev_component_rec.operation_sequence_number <= 0
3339 THEN
3340 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3341 THEN
3342 Error_Handler.Add_Error_Token
3343 ( p_Message_Name => 'ENG_OPSEQ_LESS_THAN_ZERO'
3344 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3345 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3346 , p_Token_Tbl => g_Token_Tbl
3347 );
3348 END IF;
3349 l_return_status := FND_API.G_RET_STS_ERROR;
3350 END IF;
3351
3352 --dbms_output.put_line('After Operation Sequence Num . . . ' || l_return_status);
3353
3354 IF p_rev_component_rec.item_sequence_number IS NOT NULL AND
3355 ( p_rev_component_rec.item_sequence_number < 0 OR
3356 p_rev_component_rec.item_sequence_number > 9999
3357 )
3358 THEN
3359 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3360 THEN
3361 Error_Handler.Add_Error_Token
3362 ( p_Message_Name => 'ENG_ITEM_NUM_INVALID'
3363 , p_Mesg_token_Tbl => l_Mesg_token_Tbl
3364 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3365 , p_token_tbl => g_Token_Tbl
3366 );
3367 END IF;
3368 l_return_status := FND_API.G_RET_STS_ERROR;
3369 END IF;
3370
3371 IF p_rev_component_rec.projected_yield IS NOT NULL AND
3372 p_rev_component_rec.projected_yield < 0 OR
3373 p_rev_component_rec.projected_yield > 1
3374 THEN
3375 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
3376 THEN
3377 g_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
3378 g_token_tbl(1).token_value := p_rev_component_rec.component_item_name;
3379 Error_Handler.Add_Error_Token
3380 ( p_Message_Name => 'ENG_COMPYIELD_OUT_OF_RANGE'
3381 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3382 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3383 , p_token_tbl => g_token_tbl
3384 );
3385 END IF;
3386 l_return_status := FND_API.G_RET_STS_ERROR;
3387 END IF;
3388
3389 IF p_rev_component_rec.include_in_cost_rollup IS NOT NULL AND
3390 p_rev_component_rec.include_in_cost_rollup NOT IN (1,2)
3391 THEN
3392 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
3393 THEN
3394 Error_Handler.Add_Error_Token
3395 ( p_Message_Name => 'ENG_INCL_IN_COST_ROLL_INVALID'
3396 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3397 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3398 , p_Token_Tbl => g_Token_Tbl
3399 );
3400 END IF;
3401 l_return_status := FND_API.G_RET_STS_ERROR;
3402 END IF;
3403
3404 IF p_rev_component_rec.SO_Basis IS NOT NULL AND
3405 p_rev_component_rec.SO_Basis NOT IN (1, 2)
3406 THEN
3407 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
3408 THEN
3409 Error_Handler.Add_Error_Token
3410 ( p_Message_Name => 'ENG_SO_BASIS_INVALID'
3411 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3412 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3413 , p_Token_Tbl => g_Token_Tbl
3414 );
3415 END IF;
3416 l_return_status := FND_API.G_RET_STS_ERROR;
3417 END IF;
3418
3419 IF p_rev_component_rec.optional IS NOT NULL AND
3420 p_rev_component_rec.optional NOT IN (1, 2)
3421 THEN
3422 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
3423 THEN
3424 Error_Handler.Add_Error_Token
3425 ( p_Message_Name => 'ENG_OPTIONAL_INVALID'
3426 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3427 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3428 , p_Token_Tbl => g_Token_Tbl
3429 );
3430 END IF;
3431 l_return_status := FND_API.G_RET_STS_ERROR;
3432 END IF;
3433
3434 IF p_rev_component_rec.mutually_exclusive IS NOT NULL AND
3435 p_rev_component_rec.mutually_exclusive NOT IN (1, 2)
3436 THEN
3437 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
3438 THEN
3439 Error_Handler.Add_Error_Token
3440 ( p_Message_Name => 'ENG_MUTUALLY_EXCLUSIVE_INVALID'
3441 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3442 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3443 , p_Token_Tbl => g_Token_Tbl
3444 );
3445 END IF;
3446 l_return_status := FND_API.G_RET_STS_ERROR;
3447 END IF;
3448
3449 IF p_rev_component_rec.check_atp IS NOT NULL AND
3450 p_rev_component_rec.check_atp NOT IN (1, 2)
3451 THEN
3452 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
3453 THEN
3454 Error_Handler.Add_Error_Token
3455 ( p_Message_Name => 'ENG_CHECK_ATP_INVALID'
3456 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3457 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3458 , p_Token_Tbl => g_Token_Tbl
3459 );
3460 END IF;
3461 l_return_status := FND_API.G_RET_STS_ERROR;
3462 END IF;
3463
3464 IF p_rev_component_rec.shipping_allowed IS NOT NULL AND
3465 p_rev_component_rec.shipping_allowed NOT IN (1, 2)
3466 THEN
3467 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
3468 THEN
3469 Error_Handler.Add_Error_Token
3470 ( p_Message_Name => 'ENG_SHIPPING_ALLOWED_INVALID'
3471 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3472 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3473 , p_Token_Tbl => g_Token_Tbl
3474 );
3475 END IF;
3476 l_return_status := FND_API.G_RET_STS_ERROR;
3477 END IF;
3478
3479 IF p_rev_component_rec.required_to_ship IS NOT NULL AND
3480 p_rev_component_rec.required_to_ship NOT IN (1, 2)
3481 THEN
3482 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
3483 THEN
3484 Error_Handler.Add_Error_Token
3485 ( p_Message_Name => 'ENG_REQUIRED_TO_SHIP_INVALID'
3486 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3487 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3488 , p_Token_Tbl => g_Token_Tbl
3489 );
3490 END IF;
3491 l_return_status := FND_API.G_RET_STS_ERROR;
3492 END IF;
3493
3494 IF p_rev_component_rec.required_for_revenue IS NOT NULL AND
3495 p_rev_component_rec.required_for_revenue NOT IN (1, 2)
3496 THEN
3497 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
3498 THEN
3499 Error_Handler.Add_Error_Token
3500 ( p_Message_Name => 'ENG_REQ_FOR_REVENUE_INVALID'
3501 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3502 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3503 , p_Token_Tbl => g_Token_Tbl
3504 );
3505 END IF;
3506 l_return_status := FND_API.G_RET_STS_ERROR;
3507 END IF;
3508
3509 IF p_rev_component_rec.include_on_ship_docs IS NOT NULL AND
3510 p_rev_component_rec.include_on_ship_docs NOT IN (1, 2)
3511 THEN
3512 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
3513 THEN
3514 Error_Handler.Add_Error_Token
3515 ( p_Message_Name => 'ENG_INCL_ON_SHIP_DOCS_INVALID'
3516 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3517 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3518 , p_Token_Tbl => g_Token_Tbl
3519 );
3520 END IF;
3521 l_return_status := FND_API.G_RET_STS_ERROR;
3522 END IF;
3523
3524 IF p_rev_component_rec.quantity_related IS NOT NULL AND
3525 p_rev_component_rec.quantity_related NOT IN (1, 2)
3526 THEN
3527 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
3528 THEN
3529 Error_Handler.Add_Error_Token
3530 ( p_Message_Name => 'ENG_QTY_RELATED_INVALID'
3531 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3532 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3533 , p_Token_Tbl => g_Token_Tbl
3534 );
3535 END IF;
3536 l_return_status := FND_API.G_RET_STS_ERROR;
3537 END IF;
3538
3539 -- Done validating attributes
3540
3541 x_return_status := l_return_status;
3542 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3543
3544 EXCEPTION
3545
3546 WHEN OTHERS THEN
3547 --dbms_output.put_line('Some unknown error in Attribute Validation . . .' ||
3548 -- SQLERRM );
3549
3550 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3551 THEN
3552 Error_Handler.Add_Error_Token
3553 ( p_Message_Name => NULL
3554 , p_Message_Text => 'Error in Rev Comp Attr. Validation '
3555 || SUBSTR(SQLERRM, 1, 30) || ' ' ||
3556 to_char(SQLCODE)
3557 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3558 , p_Mesg_Token_Tbl => l_Mesg_token_Tbl
3559 );
3560 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3561 END IF;
3562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3563
3564 END Check_Attributes;
3565
3566 PROCEDURE Check_Entity_Delete
3567 ( x_return_status OUT NOCOPY VARCHAR2
3568 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3569 , p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
3570 , p_Rev_Comp_Unexp_Rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
3571 )
3572 IS
3573 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3574 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3575
3576 BEGIN
3577
3578 g_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
3579 g_Token_Tbl(1).Token_Value := p_rev_component_rec.component_item_name;
3580
3581 --
3582 -- Verify that the component is not already cancelled.
3583 --
3584 IF p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_DELETE THEN
3585 BEGIN
3586 SELECT 'Component cancelled'
3587 INTO l_dummy
3588 FROM sys.dual
3589 WHERE NOT EXISTS
3590 (SELECT 1 from bom_inventory_components
3591 WHERE component_sequence_id =
3592 p_rev_comp_Unexp_rec.component_sequence_id
3593 )
3594 AND EXISTS (SELECT 1 from eng_revised_components
3595 WHERE component_sequence_id =
3596 p_rev_comp_Unexp_rec.component_sequence_id);
3597
3598 --
3599 -- if not exception is raised then record is deleted. so raise an error.
3600 --
3601 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3602 THEN
3603 Error_Handler.Add_Error_Token
3604 ( p_Message_Name => 'ENG_COMP_CANCELLED'
3605 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3606 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3607 , p_Token_Tbl => g_Token_Tbl
3608 );
3609 END IF;
3610 l_return_status := FND_API.G_RET_STS_ERROR;
3611 EXCEPTION
3612 WHEN NO_DATA_FOUND THEN
3613 null; -- do nothing the record is valid.
3614 END;
3615 END IF;
3616
3617 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3618 x_return_status := l_return_status;
3619
3620 EXCEPTION
3621
3622 WHEN FND_API.G_EXC_ERROR THEN
3623
3624 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3625 x_return_status := FND_API.G_RET_STS_ERROR;
3626
3627 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3628 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3630
3631 WHEN OTHERS THEN
3632 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3633 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3634
3635 END Check_Entity_Delete;
3636
3637 /*****************************************************************************
3638 * Procedure : Check_Existence
3639 * Parameters IN : Revised component exposed column record
3640 * Revised component unexposed column record
3641 * Parameters OUT: Old Revised component exposed column record
3642 * Old Revised component unexposed column record
3643 * Mesg Token Table
3644 * Return Status
3645 * Purpose : Check_Existence will poerform a query using the primary key
3646 * information and will return a success if the operation is
3647 * CREATE and the record EXISTS or will return an
3648 * error if the operation is UPDATE and the record DOES NOT
3649 * EXIST.
3650 * In case of UPDATE if the record exists then the procedure
3651 * will return the old record in the old entity parameters
3652 * with a success status.
3653 ****************************************************************************/
3654 PROCEDURE Check_Existence
3655 ( p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
3656 , p_rev_comp_unexp_rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
3657 , x_old_rev_component_rec IN OUT NOCOPY Bom_Bo_Pub.Rev_Component_Rec_Type
3658 , x_old_rev_comp_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
3659 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3660 , x_return_status OUT NOCOPY VARCHAR2
3661 )
3662 IS
3663 l_token_tbl Error_Handler.Token_Tbl_Type;
3664 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3665 l_return_status VARCHAR2(1);
3666 BEGIN
3667 l_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
3668 l_Token_Tbl(1).Token_Value := p_rev_component_rec.component_item_name;
3669 l_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
3670 l_Token_Tbl(2).Token_Value := p_rev_component_rec.revised_item_name;
3671
3672 ENG_Rev_Component_Util.Query_Row
3673 ( p_Component_Item_Id => p_rev_comp_unexp_rec.component_item_id
3674 , p_Operation_Sequence_Number =>
3675 p_rev_component_rec.operation_sequence_number
3676 , p_Effectivity_Date =>
3677 p_rev_component_rec.start_effective_date
3678 , p_Bill_Sequence_Id => p_rev_comp_unexp_rec.bill_sequence_id
3679 , p_from_end_item_number => p_rev_component_rec.from_end_item_unit_number
3680 , x_Rev_Component_Rec => x_old_rev_component_rec
3681 , x_Rev_Comp_Unexp_Rec => x_old_rev_comp_unexp_rec
3682 , x_Return_Status => l_return_status
3683 );
3684
3685 IF l_return_status = Bom_Globals.G_RECORD_FOUND AND
3686 p_rev_component_rec.transaction_type = Bom_Globals.G_OPR_CREATE
3687 THEN
3688 Error_Handler.Add_Error_Token
3689 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
3690 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3691 , p_message_name => 'ENG_REV_COMP_ALREADY_EXISTS'
3692 , p_token_tbl => l_token_tbl
3693 );
3694 l_return_status := FND_API.G_RET_STS_ERROR;
3695 ELSIF l_return_status = Bom_Globals.G_RECORD_NOT_FOUND AND
3696 p_rev_component_rec.transaction_type IN
3697 (Bom_Globals.G_OPR_UPDATE, Bom_Globals.G_OPR_DELETE)
3698 THEN
3699 Error_Handler.Add_Error_Token
3700 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
3701 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3702 , p_message_name => 'ENG_REV_COMP_DOESNOT_EXIST'
3703 , p_token_tbl => l_token_tbl
3704 );
3705 l_return_status := FND_API.G_RET_STS_ERROR;
3706 ELSIF l_Return_status = FND_API.G_RET_STS_UNEXP_ERROR
3707 THEN
3708 Error_Handler.Add_Error_Token
3709 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
3710 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3711 , p_message_name => NULL
3712 , p_message_text =>
3713 'Unexpected error while existence verification of ' ||
3714 'Revised component '||
3715 p_rev_component_rec.component_item_name
3716 , p_token_tbl => l_token_tbl
3717 );
3718 ELSE
3719 l_return_status := FND_API.G_RET_STS_SUCCESS;
3720 END IF;
3721
3722 x_return_status := l_return_status;
3723 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3724
3725 END Check_Existence;
3726
3727 /****************************************************************************
3728 * Prcoedure : Check_Lineage
3729 * Parameters IN : Revised Component exposed column record
3730 * Revised Component unexposed column record
3731 * Parameters OUT: Mesg_Token_Tbl
3732 * Return_Status
3733 * Purpose : Check_Lineage procedure will verify that the entity record
3734 * that the user has passed is for the right parent and that
3735 * the parent exists.
3736 *****************************************************************************/
3737 PROCEDURE Check_Lineage
3738 ( p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
3739 , p_rev_comp_unexp_rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
3740 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3741 , x_Return_Status OUT NOCOPY VARCHAR2
3742 )
3743 IS
3744 CURSOR c_GetComponent IS
3745 SELECT revised_item_sequence_id
3746 FROM bom_inventory_components
3747 WHERE component_item_id = p_rev_comp_unexp_rec.component_item_id
3748 AND bill_sequence_id = p_rev_comp_unexp_rec.bill_sequence_id
3749 AND operation_seq_num = p_rev_component_rec.operation_sequence_number
3750 AND effectivity_date = p_rev_component_rec.start_effective_date;
3751
3752 l_Token_Tbl Error_Handler.Token_Tbl_Type;
3753 l_return_status VARCHAR2(1);
3754 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3755
3756 BEGIN
3757 l_return_status := FND_API.G_RET_STS_SUCCESS;
3758
3759 /******************************************************************
3760 --
3761 -- In case of an update, based on the revised item information
3762 -- Bill Sequence Id and Revised Item Sequence Id is queried from
3763 -- the database. The revised item sequence id can however be different
3764 -- from that in the database and should be checked and given an
3765 -- error.
3766 *******************************************************************/
3767
3768 IF p_rev_component_rec.transaction_type IN
3769 (Bom_Globals.G_OPR_UPDATE, Bom_Globals.G_OPR_DELETE,
3770 Bom_Globals.G_OPR_CANCEL)
3771 THEN
3772 FOR Component IN c_GetComponent LOOP
3773 IF Component.revised_item_sequence_id <>
3774 p_rev_comp_unexp_rec.revised_item_sequence_id
3775 THEN
3776 l_Token_Tbl(1).token_name :=
3777 'REVISED_COMPONENT_NAME';
3778 l_Token_Tbl(1).token_value :=
3779 p_rev_component_rec.component_item_name;
3780 l_Token_Tbl(2).token_name :=
3781 'REVISED_ITEM_NAME';
3782 l_Token_Tbl(2).token_value :=
3783 p_rev_component_rec.revised_item_name;
3784 l_token_tbl(3).token_name := 'ECO_NAME';
3785 l_token_tbl(3).token_value :=
3786 p_rev_component_rec.eco_name;
3787
3788 Error_Handler.Add_Error_Token
3789 ( p_Message_Name => 'ENG_REV_ITEM_MISMATCH'
3790 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3791 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3792 , p_Token_Tbl => l_Token_Tbl
3793 );
3794 l_return_status := FND_API.G_RET_STS_ERROR;
3795 END IF;
3796 END LOOP;
3797 END IF;
3798
3799 x_return_status := l_return_status;
3800 x_mesg_token_tbl := l_mesg_token_tbl;
3801
3802 END Check_Lineage;
3803
3804 /****************************************************************************
3805 * Procedure : Check_Access
3806 * Parameters IN : Revised Item Unique Key
3807 * Revised Component unique key
3808 * Parameters OUT: Mesg_Token_Tbl
3809 * Return_Status
3810 * Purpose : Procedure will verify that the revised item and the revised
3811 * component is accessible to the user.
3812 *****************************************************************************/
3813 PROCEDURE Check_Access
3814 ( p_revised_item_name IN VARCHAR2
3815 , p_revised_item_id IN NUMBER
3816 , p_organization_id IN NUMBER
3817 , p_change_notice IN VARCHAR2
3818 , p_new_item_revision IN VARCHAR2
3819 , p_effectivity_date IN DATE
3820 , p_component_item_id IN NUMBER
3821 , p_operation_seq_num IN NUMBER
3822 , p_bill_sequence_id IN NUMBER
3823 , p_component_name IN VARCHAR2
3824 , p_Mesg_Token_Tbl IN Error_Handler.Mesg_Token_Tbl_Type :=
3825 Error_Handler.G_MISS_MESG_TOKEN_TBL
3826 , p_entity_processed IN VARCHAR2 := 'RC'
3827 , p_rfd_sbc_name IN VARCHAR2 := NULL
3828 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3829 , x_Return_Status OUT NOCOPY VARCHAR2
3830 )
3831 IS
3832 l_Token_Tbl Error_Handler.Token_Tbl_Type;
3833 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type :=
3834 p_Mesg_Token_Tbl;
3835 l_return_status VARCHAR2(1);
3836 l_Rev_Comp_Item_Type NUMBER;
3837 l_error_name VARCHAR2(30);
3838 l_is_comp_unit_controlled BOOLEAN := FALSE;
3839
3840 CURSOR c_CheckCancelled IS
3841 SELECT 1
3842 FROM sys.dual
3843 WHERE NOT EXISTS
3844 ( SELECT component_sequence_id
3845 FROM bom_inventory_components
3846 WHERE component_item_id = p_component_item_id
3847 AND bill_sequence_id = p_bill_sequence_id
3848 AND effectivity_date = p_effectivity_date
3849 AND operation_seq_num = p_operation_seq_num
3850 ) AND
3851 EXISTS
3852 ( SELECT component_sequence_id
3853 FROM eng_revised_components
3854 WHERE component_item_id = p_component_item_id
3855 AND bill_sequence_id = p_bill_sequence_id
3856 AND effectivity_date = p_effectivity_date
3857 AND operation_sequence_num = p_operation_seq_num
3858 );
3859
3860 CURSOR c_CheckDisabled IS
3861 SELECT component_item_id
3862 FROM bom_inventory_components
3863 WHERE component_item_id = p_component_item_id
3864 AND bill_sequence_id = p_bill_sequence_id
3865 AND effectivity_date = p_effectivity_date
3866 AND operation_seq_num = p_operation_seq_num
3867 AND acd_type = 3;
3868
3869 CURSOR c_Check_Unit_Effective IS
3870 SELECT effectivity_control
3871 FROM mtl_system_items
3872 WHERE inventory_item_id = p_component_item_id
3873 AND organization_id = p_organization_id;
3874
3875 BEGIN
3876 l_return_status := FND_API.G_RET_STS_SUCCESS;
3877
3878 --
3879 -- Revised Item Access check should be done by the calling
3880 -- program
3881 --
3882 /***********************************************
3883 Eng_Validate_Revised_Item.Check_Access
3884 ( p_revised_item_name => p_revised_item_name
3885 , p_revised_item_id => p_revised_item_id
3886 , p_change_notice => p_change_notice
3887 , p_organization_id => p_organization_id
3888 , p_new_item_revision => p_new_item_revision
3889 , p_effectivity_date => p_effectivity_date
3890 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3891 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3892 , x_Return_Status => l_Return_Status
3893 );
3894 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3895 THEN
3896 x_Return_Status := l_return_status;
3897 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3898
3899 RETURN;
3900 END IF;
3901 ***************************************************/
3902
3903
3904 /***************************************************************
3905 --
3906 -- Check if the user has access to the revised component's
3907 -- bom_item_type
3908 --
3909 ****************************************************************/
3910 SELECT bom_item_type
3911 INTO l_rev_comp_item_type
3912 FROM mtl_system_items
3913 WHERE inventory_item_id = p_component_item_id
3914 AND organization_id = p_organization_id;
3915
3916 IF l_rev_comp_item_type NOT IN
3917 (NVL(Bom_Globals.Get_MDL_Item_Access,0),
3918 NVL(Bom_Globals.Get_OC_Item_Access,0),
3919 NVL(Bom_Globals.Get_PLN_Item_Access,0),
3920 NVL(Bom_Globals.Get_STD_Item_Access,0)
3921 )
3922 THEN
3923 l_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
3924 l_token_tbl(1).token_value := p_component_name;
3925 l_token_tbl(2).token_name := 'BOM_ITEM_TYPE';
3926 l_token_tbl(2).translate := TRUE;
3927
3928 IF l_rev_comp_Item_Type = 1
3929 THEN
3930 l_Token_Tbl(2).Token_Value := 'ENG_MODEL';
3931 ELSIF l_rev_comp_Item_Type = 2
3932 THEN
3933 l_Token_Tbl(2).Token_Value:='ENG_OPTION_CLASS';
3934 ELSIF l_rev_comp_Item_Type = 3
3935 THEN
3936 l_Token_Tbl(2).Token_Value := 'ENG_PLANNING';
3937 ELSIF l_rev_comp_Item_Type = 4
3938 THEN
3939 l_Token_Tbl(2).Token_Value := 'ENG_STANDARD';
3940 END IF;
3941
3942 Error_Handler.Add_Error_Token
3943 ( p_Message_Name => 'ENG_REV_COMP_ACCESS_DENIED'
3944 , p_Mesg_Token_Tbl => l_mesg_token_tbl
3945 , x_Mesg_Token_Tbl => l_mesg_token_tbl
3946 , p_Token_Tbl => l_token_tbl
3947 );
3948 l_token_tbl.DELETE(2);
3949 l_return_status := FND_API.G_RET_STS_ERROR;
3950 END IF;
3951
3952 IF l_rev_comp_item_type = 5 /* Product Family */
3953 THEN
3954 Error_Handler.Add_Error_Token
3955 ( p_Message_Name => 'ENG_REV_COMP_PRODUCT_FAMILY'
3956 , p_Mesg_Token_Tbl => l_mesg_token_tbl
3957 , x_Mesg_Token_Tbl => l_mesg_token_tbl
3958 , p_Token_Tbl => l_token_tbl
3959 );
3960 l_return_status := FND_API.G_RET_STS_ERROR;
3961 END IF;
3962
3963 /****************************************************************
3964 --
3965 -- If revised item check is successful, then check if the revised
3966 -- component is not cancelled. This check will not prove useful for
3967 -- the revised item itself, since the check existence for a cancelled
3968 -- component would fail. But this procedure can be called by the
3969 -- child records of the revised component and make sure that the
3970 -- parent record is not cancelled.
3971 --
3972 ********************************************************************/
3973
3974 IF Bom_Globals.Is_RComp_Cancl IS NULL THEN
3975 FOR RevComp IN c_CheckCancelled
3976 LOOP
3977 l_token_tbl.DELETE;
3978 l_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
3979 l_Token_Tbl(1).Token_value := p_component_name;
3980 Error_Handler.Add_Error_Token
3981 ( p_Message_Name => 'ENG_REV_COMP_CANCELLED'
3982 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3983 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3984 , p_Token_Tbl => l_token_tbl
3985 );
3986 l_return_status := FND_API.G_RET_STS_ERROR;
3987 END LOOP;
3988 END IF;
3989
3990 /*********************************************************************
3991 -- Added by AS on 07/06/99
3992 -- Checks that unit effective items are allowed only if the profile
3993 -- value allows them (profile value stored in system_information)
3994 *********************************************************************/
3995
3996 FOR UnitEffective IN c_Check_Unit_Effective
3997 LOOP
3998 IF UnitEffective.Effectivity_Control = 2
3999 THEN
4000 Bom_Globals.Set_Unit_Controlled_Component
4001 ( p_unit_controlled_component => TRUE);
4002
4003 l_is_comp_unit_controlled := TRUE;
4004
4005 ELSIF UnitEffective.Effectivity_Control = 1
4006 THEN
4007 Bom_Globals.Set_Unit_Controlled_Component
4008 ( p_unit_controlled_component => FALSE);
4009
4010 l_is_comp_unit_controlled := FALSE;
4011 END IF;
4012 END LOOP;
4013
4014 IF NOT Bom_Globals.Get_Unit_Effectivity AND
4015 l_is_comp_unit_controlled
4016 THEN
4017 l_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
4018 l_token_tbl(1).token_value :=
4019 p_component_name;
4020 l_token_tbl(2).token_name := 'ECO_NAME';
4021 l_token_tbl(2).token_value :=
4022 p_change_notice;
4023 Error_Handler.Add_Error_Token
4024 ( p_Message_Name => 'ENG_REV_COMP_UNIT_CONTROL'
4025 , p_Mesg_Token_Tbl => l_mesg_token_tbl
4026 , x_Mesg_Token_Tbl => l_mesg_token_tbl
4027 , p_Token_Tbl => l_token_tbl
4028 );
4029 l_return_status := FND_API.G_RET_STS_ERROR;
4030 END IF;
4031
4032 /**************************************************************
4033 --
4034 -- If the Entity being processed is Reference Designator
4035 -- or subsitute component then check if the parent component is
4036 -- disabled. If it is then Error this record and also all the
4037 -- siblings
4038 --
4039 **************************************************************/
4040 IF p_entity_processed IN ('RFD', 'SBC')
4041 THEN
4042 FOR isdisabled IN c_CheckDisabled LOOP
4043 IF p_entity_processed = 'RFD'
4044 THEN
4045 l_error_name := 'ENG_RFD_COMP_ACD_TYPE_DISABLE';
4046 l_token_tbl(1).token_name :=
4047 'REFERENCE_DESIGNATOR_NAME';
4048 l_token_tbl(1).token_value := p_rfd_sbc_name;
4049 ELSE
4050 l_error_name := 'ENG_SBC_COMP_ACD_TYPE_DISABLE';
4051 l_token_tbl(1).token_name :=
4052 'SUBSTITUTE_ITEM_NAME';
4053 l_token_tbl(1).token_value := p_rfd_sbc_name;
4054 END IF;
4055
4056 l_token_tbl(2).token_name := 'REVISED_COMPONENT_NAME';
4057 l_token_tbl(2).token_value := p_component_name;
4058
4059 l_return_status := FND_API.G_RET_STS_ERROR;
4060
4061 Error_Handler.Add_Error_Token
4062 ( p_Message_Name => l_error_name
4063 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4064 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4065 , p_Token_Tbl => l_token_tbl
4066 );
4067 END LOOP;
4068 END IF;
4069
4070 x_Return_Status := l_return_status;
4071 x_Mesg_Token_Tbl := l_mesg_token_tbl;
4072
4073 END Check_Access;
4074
4075 END ENG_Validate_Rev_Component;
[Home] [Help]