1 PACKAGE BODY BOM_Config_Validation_Pub AS
2 /* $Header: BOMPCFGB.pls 120.1 2005/12/02 04:44:03 hgelli noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='BOM_Config_Validation_Pub';
5
6
7 /*-----------------------------------------------------------------------
8 Forward Declarations
9 ------------------------------------------------------------------------*/
10
11 Procedure Check_Min_Max
12 ( p_top_bill_sequence_id IN NUMBER
13 ,p_model_qty IN NUMBER
14 ,p_effective_date IN DATE
15 ,p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
16 ,x_valid_config OUT NOCOPY VARCHAR2
17 ,x_return_status OUT NOCOPY VARCHAR2);
18
19
20 PROCEDURE Check_Ratio_And_Parent
21 (p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
22 ,p_top_model_line_id IN NUMBER
23 ,x_return_status OUT NOCOPY VARCHAR2);
24
25
26 PROCEDURE Check_Class_Has_Options
27 (p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
28 ,x_complete_config OUT NOCOPY VARCHAR2
29 ,x_return_status OUT NOCOPY VARCHAR2);
30
31
32 PROCEDURE Check_Mut_Excl_Options
33 ( p_top_bill_sequence_id IN NUMBER
34 ,p_effective_date IN DATE
35 ,p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
36 ,x_valid_config OUT NOCOPY VARCHAR2
37 ,x_return_status OUT NOCOPY VARCHAR2);
38
39
40 PROCEDURE Check_Mandatory_Classes
41 ( p_top_bill_sequence_id IN NUMBER
42 ,p_top_model_line_id IN NUMBER
43 ,p_effective_date IN DATE
44 ,p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
45 ,x_complete_config OUT NOCOPY VARCHAR2
46 ,x_return_status OUT NOCOPY VARCHAR2);
47
48
49 FUNCTION Mutually_Exclusive_Comps_exist
50 ( p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
51 ,p_component_code IN VARCHAR2)
52 RETURN BOOLEAN;
53
54
55 FUNCTION Mandatory_Comps_Missing
56 ( p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
57 ,p_component_code IN VARCHAR2)
58 RETURN BOOLEAN;
59
60
61 FUNCTION Get_Parent_Quantity
62 ( p_component_code IN VARCHAR2
63 ,p_top_model_line_id IN NUMBER
64 ,p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE)
65 RETURN NUMBER;
66
67
68 FUNCTION Check_Option_Exist
69 ( p_component_code IN VARCHAR2
70 ,p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE)
71 RETURN BOOLEAN;
72
73
74 FUNCTION Check_Parent_Exists
75 ( p_component_code IN VARCHAR2
76 ,p_index IN NUMBER
77 ,p_top_model_line_id IN NUMBER
78 ,p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE)
79 RETURN BOOLEAN;
80
81
82 PROCEDURE Handle_Ret_Status
83 (p_valid_config IN VARCHAR2 := NULL
84 ,p_complete_config IN VARCHAR2 := NULL);
85
86
87 PROCEDURE Print_Time(p_msg IN VARCHAR2);
88
89 /*-----------------------------------------------------------------------
90 PROCEDURE: Bom_Based_Config_Validation
91
92 This API takes a list of selected options (p_options_tbl) for the
93 top level model(p_top_model_line_id ) in OM.
94
95 It performs certain validations based on the BOM setup.
96 1) if the ordered quantity of any option is not out side of
97 the Min - Max quantity settings in BOM.
98 2) if the ratio of ordered quantity of a class to model
99 and option to class is integer ratio i.e. exact multiple.
100 3) to see that a class does not exist w/o any options selected for it.
101 4) if a class that has mutually exclusive options, does not have
102 more than one options selected under it.
103 5) if at least one option is selected per mandatory class.
104
105 If any of the validation fails, it will populate error messages
106 and will return with a status of error.
107
108 Change Record:
109
110 ER2625376: changes made to BOM should be visible to Order unitl Booking.
111
112 OE_Config_Util.Get_Config_effective_Date should be used to decide
113 the date for effective/diabled filter on bom_explosions sqls.
114
115 p_creatione_date parameter will be renamed to p_effective_date.
116 ------------------------------------------------------------------------*/
117
118 Procedure Bom_Based_Config_Validation
119 ( p_top_model_line_id IN NUMBER
120 ,p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
121 ,x_valid_config OUT NOCOPY VARCHAR2
122 ,x_complete_config OUT NOCOPY VARCHAR2
123 ,x_return_status OUT NOCOPY VARCHAR2
124 )
125 IS
126 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
127 l_top_bill_sequence_id NUMBER;
128 l_effective_date DATE;
129 l_model_qty NUMBER;
130 l_valid_config VARCHAR2(10);
131 l_complete_config VARCHAR2(10);
132 l_old_behavior VARCHAR2(1);
133 l_frozen_model_bill VARCHAR2(1);
134 --
135 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
136 --
137 BEGIN
138
139 IF l_debug_level > 0 THEN
140 oe_debug_pub.add('ENTERING BOM_BASED_CONFIG_VALIDATION' , 1 ) ;
141 END IF;
142 Print_Time('Bom_Based_Config_Validation start time');
143
144 G_VALID_CONFIG := 'TRUE';
145 G_COMPLETE_CONFIG := 'TRUE';
146
147 BEGIN
148 SELECT component_sequence_id, ordered_quantity
149 INTO l_top_bill_sequence_id, l_model_qty
150 FROM oe_order_lines
151 WHERE line_id = p_top_model_line_id;
152 EXCEPTION
153 WHEN OTHERS THEN
154 IF l_debug_level > 0 THEN
155 oe_debug_pub.add('SELECT FAILED '|| SQLERRM , 1 ) ;
156 END IF;
157 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
158 END;
159
160
161 OE_Config_Util.Get_Config_Effective_Date
162 ( p_model_line_id => p_top_model_line_id
163 ,x_old_behavior => l_old_behavior
164 ,x_config_effective_date => l_effective_date
165 ,x_frozen_model_bill => l_frozen_model_bill);
166
167
168 IF l_debug_level > 0 THEN
169 oe_debug_pub.add('CALLING CHECK_RATIO_AND_PARENT' , 1 ) ;
170 END IF;
171
172 Check_Ratio_And_Parent
173 ( p_options_tbl => p_options_tbl
174 ,p_top_model_line_id => p_top_model_line_id
175 ,x_return_status => l_return_status);
176
177 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
178 Handle_Ret_Status(p_valid_config => l_valid_config);
179 END IF;
180
181 IF l_debug_level > 0 THEN
182 oe_debug_pub.add('CALLING CHECK_MIN_MAX' , 1 ) ;
183 END IF;
184
185 Check_Min_Max
186 ( p_top_bill_sequence_id => l_top_bill_sequence_id
187 ,p_model_qty => l_model_qty
188 ,p_effective_date => l_effective_date
189 ,p_options_tbl => p_options_tbl
190 ,x_valid_config => l_valid_config
191 ,x_return_status => l_return_status);
192
193 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
194 Handle_Ret_Status(p_valid_config => l_valid_config);
195 END IF;
196
197
198 IF l_debug_level > 0 THEN
199 oe_debug_pub.add('CALLING CHECK_CLASS_HAS_OPTIONS' , 1 ) ;
200 END IF;
201
202 Check_Class_Has_Options
203 ( p_options_tbl => p_options_tbl
204 ,x_complete_config => l_complete_config
205 ,x_return_status => l_return_status);
206
207 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
208 Handle_Ret_Status(p_complete_config => l_complete_config);
209 END IF;
210
211 IF l_debug_level > 0 THEN
212 oe_debug_pub.add('CALLING CHECK_MUT_EXCL_OPTIONS' , 1 ) ;
213 END IF;
214
215 Check_Mut_Excl_Options
216 ( p_top_bill_sequence_id => l_top_bill_sequence_id
217 ,p_effective_date => l_effective_date
218 ,p_options_tbl => p_options_tbl
219 ,x_valid_config => l_valid_config
220 ,x_return_status => l_return_status);
221
222 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
223 Handle_Ret_Status(p_valid_config => l_valid_config);
224 END IF;
225
226
227 IF l_debug_level > 0 THEN
228 oe_debug_pub.add('CALLIN CHECK_MANDATORY_CLASSES' , 1 ) ;
229 END IF;
230
231 Check_Mandatory_Classes
232 ( p_top_bill_sequence_id => l_top_bill_sequence_id
233 ,p_top_model_line_id => p_top_model_line_id
234 ,p_effective_date => l_effective_date
235 ,p_options_tbl => p_options_tbl
236 ,x_complete_config => l_complete_config
237 ,x_return_status => l_return_status);
238
239 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
240 Handle_Ret_Status(p_complete_config => l_complete_config);
241 END IF;
242
243
244 x_valid_config := G_VALID_CONFIG;
245 x_complete_config := G_COMPLETE_CONFIG;
246
247 IF G_VALID_CONFIG = 'FALSE' OR
248 G_COMPLETE_CONFIG = 'FALSE' THEN
249 x_return_status := FND_API.G_RET_STS_ERROR;
250 ELSE
251 x_return_status := FND_API.G_RET_STS_SUCCESS;
252 END IF;
253
254 Print_Time('Bom_Based_Config_Validation end time');
255 IF l_debug_level > 0 THEN
256 oe_debug_pub.add('LEAVING BOM_BASED_CONFIG_VALIDATION'
257 || X_RETURN_STATUS , 1 ) ;
258 END IF;
259 EXCEPTION
260 WHEN OTHERS THEN
261 IF l_debug_level > 0 THEN
262 oe_debug_pub.add('BOM_BASED_CONFIG_VALIDATION EXCEPTION'|| SQLERRM,1);
263 END IF;
264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
265 RAISE;
266 END Bom_Based_Config_Validation;
267
268
269 /*------------------------------------------------------------------------
270 PROCEDURE: Check_Ratio_And_Parent
271 This procedure performs 2 checks,
272 1) that the child's ordered qty should be excat mutilple of the parent.
273 2) option do not exist w/o its parent class.
274
275 get the top model's quantity. We loop throgh the option table.
276 check the ratio of the quatities.
277 in the same run, check that the parent exists using a helper.
278
279 any error in this check should not be allowed to be saved.
280 so raise exception.
281
282 Part of the logic in this procedure will not be used any more
283 -- the decimal ratio check due to Decimal quantities for ATO Options
284 Project.The decimal ratio check will be part of line entity validation
285 in OEXLLINB.pls
286 -------------------------------------------------------------------------*/
287
288 PROCEDURE Check_Ratio_And_Parent
289 (p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
290 ,p_top_model_line_id IN NUMBER
291 ,x_return_status OUT NOCOPY VARCHAR2)
292 IS
293 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
294 l_parent_qty NUMBER;
295 I NUMBER;
296 l_parent_exists BOOLEAN := FALSE;
297 l_ordered_item VARCHAR2(2000);
298 l_item_type_code VARCHAR2(30);
299 --
300 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
301 --
302 BEGIN
303 IF l_debug_level > 0 THEN
304 oe_debug_pub.add('ENTERING CHECK_RATIO_AND_PARENT' , 1 ) ;
305 END IF;
306 Print_Time('Check_Ratio_And_Parent start time');
307
308 SELECT ordered_quantity
309 INTO l_parent_qty
310 FROM oe_order_lines
311 WHERE line_id = p_top_model_line_id;
312
313 I := p_options_tbl.FIRST ;
314
315 WHILE I is not null
316 LOOP
317 IF INSTR(p_options_tbl(I).component_code , '-') <> 0 THEN -- not for model
318
319 /****************** commneted during OM pack J project
320 IF p_options_tbl(I).ordered_quantity = 0 THEN
321 IF l_debug_level > 0 THEN
322 oe_debug_pub.add('QTY IS 0 , DONT CHECK'|| L_PARENT_QTY , 2 ) ;
323 END IF;
324
325 ELSE
326 IF TRUNC(p_options_tbl(I).ordered_quantity/l_parent_qty) <>
327 (p_options_tbl(I).ordered_quantity/l_parent_qty)
328 THEN
329 IF l_debug_level > 0 THEN
330 oe_debug_pub.add('DECIMAL RATIO '||P_OPTIONS_TBL ( I ) .COMPONENT_CODE , 1 ) ;
331 END IF;
332
333 FND_MESSAGE.Set_Name('ONT', 'OE_CONFIG_DECIMAL_RATIO');
334
335 IF p_options_tbl(I).bom_item_type = 1 THEN
336 l_item_type_code := 'MODEL';
337 ELSIF p_options_tbl(I).bom_item_type = 2 THEN
338 l_item_type_code := 'CLASS';
339 ELSE
340 l_item_type_code := 'OPTION';
341 END IF;
342
343 FND_MESSAGE.Set_TOKEN('ITEM', p_options_tbl(I).ordered_item);
344 FND_MESSAGE.Set_TOKEN('TYPECODE', l_item_type_code);
345 FND_MESSAGE.Set_TOKEN
346 ('VALUE', p_options_tbl(I).ordered_quantity/l_parent_qty);
347
348 SELECT ordered_item, item_type_code
349 INTO l_ordered_item, l_item_type_code
350 FROM oe_order_lines
351 WHERE line_id = p_top_model_line_id;
352
353 FND_MESSAGE.Set_TOKEN('MODEL', l_ordered_item);
354 FND_MESSAGE.Set_TOKEN('PTYPECODE', l_item_type_code);
355
356 OE_Msg_Pub.Add;
357
358 RAISE FND_API.G_EXC_ERROR;
359 END IF;
360
361 END IF; -- if the qty = 0
362
363 *********************************************************/
364
365 IF l_debug_level > 0 THEN
366 oe_debug_pub.add('NOW CHECKING IF PARENT PRESENT' , 1 ) ;
367 END IF;
368 l_parent_exists := Check_Parent_Exists
369 ( p_component_code => p_options_tbl(I).component_code
373
370 ,p_index => I
371 ,p_top_model_line_id => p_top_model_line_id
372 ,p_options_tbl => p_options_tbl);
374 IF NOT l_parent_exists THEN
375 IF l_debug_level > 0 THEN
376 oe_debug_pub.add('MAJOR ERROR , CAN NOT GO FURTHER' , 2 ) ;
377 END IF;
378 RAISE FND_API.G_EXC_ERROR;
379 END IF;
380
381 END IF; -- if not model
382
383 I := p_options_tbl.NEXT(I);
384 END LOOP;
385
386 x_return_status := l_return_status;
387
388 Print_Time('Check_Ratio_And_Parent end time');
389 IF l_debug_level > 0 THEN
390 oe_debug_pub.add('LEAVING CHECK_RATIO_AND_PARENT' , 1 ) ;
391 END IF;
392
393 EXCEPTION
394 WHEN OTHERS THEN
395 IF l_debug_level > 0 THEN
396 oe_debug_pub.add('CHECK_RATIO_AND_PARENT EXCEPTION'|| SQLERRM , 1 ) ;
397 END IF;
398 RAISE;
399 END Check_Ratio_And_Parent;
400
401
402 /*-----------------------------------------------------------------------
403 PROCEDURE: Check_Parent_Exists
404 This procedure is used to detect corrupt data(Oracle IT had this).
405 To see if every item in the configuration has immediate parent selected.
406 ------------------------------------------------------------------------*/
407 FUNCTION Check_Parent_Exists
408 ( p_component_code IN VARCHAR2
409 ,p_index IN NUMBER
410 ,p_top_model_line_id IN NUMBER
411 ,p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE)
412 RETURN BOOLEAN
413 IS
414 I NUMBER;
415 l_open_flag VARCHAR2(1);
416 l_ordered_item VARCHAR2(2000);
417 l_line VARCHAR2(100);
418 l_line_number NUMBER;
419 l_shipment_number NUMBER;
420 l_option_number NUMBER;
421 --
422 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
423 --
424 BEGIN
425
426 IF l_debug_level > 0 THEN
427 oe_debug_pub.add('ENTERING CHECK_PARENT_EXISTS'|| P_COMPONENT_CODE , 1 ) ;
428 END IF;
429 Print_Time('Check_Parent_Exists start time');
430
431 I := p_options_tbl.FIRST;
432 WHILE I is not null
433 LOOP
434 IF p_options_tbl(I).component_code = SUBSTR(p_component_code, 1,
435 INSTR(p_component_code, '-', -1) - 1)
436 THEN
437 IF l_debug_level > 0 THEN
438 oe_debug_pub.add('PARENT FOUND '|| P_OPTIONS_TBL ( I ) .COMPONENT_CODE , 1 ) ;
439 END IF;
440 RETURN TRUE;
441 END IF;
442
443 I := p_options_tbl.NEXT(I);
444 END LOOP;
445
446 IF l_debug_level > 0 THEN
447 oe_debug_pub.add('PARENT NOT FOUND , MAY BE CANCELED' , 1 ) ;
448 END IF;
449
450 BEGIN
451
452 -- should not get more than 1 rows in all cases.
453 SELECT open_flag
454 INTO l_open_flag
455 FROM oe_order_lines
456 where line_id =
457 (SELECT line_id
458 FROM oe_order_lines
459 WHERE top_model_line_id = p_top_model_line_id
460 AND component_code = SUBSTR(p_component_code, 1,
461 INSTR(p_component_code, '-', -1) - 1));
462
463 IF l_debug_level > 0 THEN
464 oe_debug_pub.add('OPEN_FLAG , QTY: '|| L_OPEN_FLAG , 3 ) ;
465 END IF;
466
467 IF l_open_flag = 'N' THEN
468 IF l_debug_level > 0 THEN
469 oe_debug_pub.add('PARENT WAS CANCELLED' , 3 ) ;
470 END IF;
471 RETURN TRUE;
472 END IF;
473
474 EXCEPTION
475 WHEN NO_DATA_FOUND THEN
476 IF l_debug_level > 0 THEN
477 oe_debug_pub.add('NO PARENT' , 3 ) ;
478 END IF;
479
480 FND_MESSAGE.Set_Name('ONT', 'OE_CONFIG_NO_PARENT');
481 FND_MESSAGE.Set_TOKEN('ITEM', p_options_tbl(p_index).ordered_item);
482
483 BEGIN
484 SELECT ordered_item, line_number, shipment_number, option_number
485 INTO l_ordered_item, l_line_number, l_shipment_number, l_option_number
486 FROM oe_order_lines
487 WHERE top_model_line_id = p_top_model_line_id
488 AND component_code = p_component_code;
489
490 l_line := l_line_number || '.'|| l_shipment_number ||'.'|| l_option_number;
491
492 EXCEPTION
493 WHEN NO_DATA_FOUND THEN
494 l_line := null;
495 END;
496
497 IF l_debug_level > 0 THEN
498 oe_debug_pub.add('NOT YET CREATED' , 3 ) ;
499 END IF;
500
501 FND_MESSAGE.Set_TOKEN('LINE', l_line);
502 OE_MSG_PUB.Add;
503
504 RAISE FND_API.G_EXC_ERROR;
505 END;
506
507 RETURN FALSE;
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 IF l_debug_level > 0 THEN
512 oe_debug_pub.add('CHECK_PARENT_EXISTS EXCEPTION'|| SQLERRM , 1 ) ;
513 END IF;
514 RETURN FALSE;
515 END Check_Parent_Exists;
516
517
518
519 /*-----------------------------------------------------------------------
520 PROCEDURE: Check_Min_Max
521 This procedure loops through the options table.
522 For each option,
523 it selects the min and max quantity from bom_explosions table. Some or all
524 of the options might not have the min max range set up in the BOM set up.
525 if the ordered quantity is not within the range, it popuplates a message
526 and it sets the return staus to error.
527 If all the options are within the min max range, we return success.
528 ------------------------------------------------------------------------*/
529 Procedure Check_Min_Max
533 ,p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
530 ( p_top_bill_sequence_id IN NUMBER
531 ,p_model_qty IN NUMBER
532 ,p_effective_date IN DATE
534 ,x_valid_config OUT NOCOPY VARCHAR2
535 ,x_return_status OUT NOCOPY VARCHAR2)
536 IS
537 I NUMBER;
538 l_max_allowed_qty NUMBER;
539 l_min_allowed_qty NUMBER;
540 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
541 l_valid_config VARCHAR2(10) := 'TRUE';
542 --
543 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
544 --
545 BEGIN
546 IF l_debug_level > 0 THEN
547 oe_debug_pub.add('ENTERING CHECK_MIN_MAX' , 1 ) ;
548 END IF;
549 Print_Time('Check_Min_Max start time');
550
551 I := p_options_tbl.FIRST;
552
553 WHILE I is not NULL
554
555 LOOP
556 IF l_debug_level > 0 THEN
557 oe_debug_pub.add('CHECKING MIN MAK FOR: '|| P_OPTIONS_TBL ( I ) .COMPONENT_CODE , 1 ) ;
558 END IF;
559 -- if the qty is 0, we delete the line before cancellation
560 -- we close the line after cancellation, so no need to do this check.
561
562 IF p_options_tbl(I).ordered_quantity = 0 THEN
563 IF l_debug_level > 0 THEN
564 oe_debug_pub.add('NO CHECK SINCE QTY = 0' ) ;
565 END IF;
566 ELSE
567
568 SELECT /*+ INDEX(BE1 BOM_EXPLOSIONS_N1) */
569 nvl(be1.high_quantity,0), nvl(be1.low_quantity,0)
570 INTO l_max_allowed_qty, l_min_allowed_qty
571 FROM bom_explosions be1
572 WHERE be1.TOP_BILL_SEQUENCE_ID = p_top_bill_sequence_id
573 AND be1.component_code = p_options_tbl(I).component_code
574 AND be1.effectivity_date <=
575 p_effective_date
576 AND be1.disable_date >
577 p_effective_date
578 AND be1.explosion_type = 'OPTIONAL'
579 AND be1.plan_level >= 0;
580 --AND be1.sort_order = p_options_tbl(I).sort_order;
581
582 IF l_min_allowed_qty <> 0 AND l_max_allowed_qty <> 0 THEN
583
584 -- the trunc functions below were added for bugfix 3870948
585 IF trunc(p_options_tbl(I).ordered_quantity/p_model_qty,9) < l_min_allowed_qty OR
586 trunc(p_options_tbl(I).ordered_quantity/p_model_qty,9) > l_max_allowed_qty
587 THEN
588
589 IF l_debug_level > 0 THEN
590 oe_debug_pub.add('ORDERED QTY OUT OF RANGE' || P_OPTIONS_TBL ( I ) .ORDERED_QUANTITY , 1 ) ;
591 END IF;
592 IF l_debug_level > 0 THEN
593 oe_debug_pub.add('MIN: '||L_MIN_ALLOWED_QTY || 'MAX: '||L_MAX_ALLOWED_QTY , 1 ) ;
594 END IF;
595 IF l_debug_level > 0 THEN
596 oe_debug_pub.add('ITEM'|| P_OPTIONS_TBL ( I ) .ORDERED_ITEM , 1 ) ;
597 END IF;
598
599 FND_MESSAGE.Set_Name('ONT', 'OE_VAL_CONFIG_QTY_OUT_OF_RANGE');
600 FND_MESSAGE.Set_Token('ITEM', p_options_tbl(I).ordered_item);
601 FND_MESSAGE.Set_Token('LOW', l_min_allowed_qty);
602 FND_MESSAGE.Set_Token('HIGH', l_max_allowed_qty);
603 OE_Msg_Pub.Add;
604 l_valid_config := 'FALSE';
605 l_return_status := FND_API.G_RET_STS_ERROR;
606 END IF;
607
608 ELSE
609 IF l_debug_level > 0 THEN
610 oe_debug_pub.add('MIN MAX RANGE NOT SET' , 1 ) ;
611 END IF;
612 END IF; -- if there is min max setting
613
614 END IF; -- if the qty was 0
615
616 I := p_options_tbl.NEXT(I);
617 END LOOP;
618
619 x_valid_config := l_valid_config;
620 x_return_status := l_return_status;
621
622 Print_Time('Check_Min_Max end time');
623 IF l_debug_level > 0 THEN
624 oe_debug_pub.add('LEAVING CHECK_MIN_MAX' , 1 ) ;
625 END IF;
626
627 EXCEPTION
628 WHEN OTHERS THEN
629 IF l_debug_level > 0 THEN
630 oe_debug_pub.add('CHECK_MIN_MAX EXCEPTION'|| SQLERRM , 1 ) ;
631 END IF;
632 RAISE;
633 END Check_Min_Max;
634
635
636 /*------------------------------------------------------------------------
637 FUNCTION: Check_Class_Has_Options
638 All the classes in a configuration should have at least
639 one option selected.
640 -------------------------------------------------------------------------*/
641 PROCEDURE Check_Class_Has_Options
642 (p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
643 ,x_complete_config OUT NOCOPY VARCHAR2
644 ,x_return_status OUT NOCOPY VARCHAR2)
645 IS
646 I NUMBER;
647 l_no_child BOOLEAN;
648 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
649 l_complete_config VARCHAR2(10) := 'TRUE';
650 --
651 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
652 --
653 BEGIN
654 IF l_debug_level > 0 THEN
655 oe_debug_pub.add('ENTERING CHECK_CLASS_HAS_OPTIONS' , 1 ) ;
656 END IF;
657 Print_Time('Check_Class_Has_Options start time');
658
659 I := p_options_tbl.FIRST ;
660
661 WHILE I is not null
662 LOOP
663 IF l_debug_level > 0 THEN
664 oe_debug_pub.add('BOM_ITEM_TYPE: '|| P_OPTIONS_TBL ( I ) .BOM_ITEM_TYPE , 1 ) ;
665 END IF;
666 -- model line has null, so will not go in.
667 IF p_options_tbl(I).bom_item_type = 2 THEN
668
669 IF p_options_tbl(I).ordered_quantity = 0 THEN
670 IF l_debug_level > 0 THEN
671 oe_debug_pub.add('NO NEED TO CHECK , EITHER DELETE OR CLOSE' , 1 ) ;
672 END IF;
673
674 ELSE
675
676 l_no_child := Check_Option_Exist
680 FND_MESSAGE.Set_Name('ONT', 'OE_VAL_CONFIG_CLASS_NO_OPTION');
677 ( p_component_code => p_options_tbl(I).component_code
678 ,p_options_tbl => p_options_tbl);
679 IF NOT l_no_child THEN
681 FND_MESSAGE.Set_Token('CLASS', p_options_tbl(I).ordered_item);
682 OE_Msg_Pub.Add;
683 l_complete_config := 'FALSE';
684 l_return_status := FND_API.G_RET_STS_ERROR;
685 END IF; -- if no child
686
687 END IF; -- if qty = 0
688
689 END IF; -- if a class
690
691 I := p_options_tbl.NEXT(I);
692 END LOOP;
693
694 x_complete_config := l_complete_config;
695 x_return_status := l_return_status;
696
697 Print_Time('Check_Class_Has_Options end time');
698 IF l_debug_level > 0 THEN
699 oe_debug_pub.add('LEAVING CHECK_CLASS_HAS_OPTIONS' , 1 ) ;
700 END IF;
701 EXCEPTION
702 WHEN OTHERS THEN
703 IF l_debug_level > 0 THEN
704 oe_debug_pub.add('CHECK_CLASS_HAS_OPTIONS EXCEPTION'|| SQLERRM , 1 ) ;
705 END IF;
706 RAISE;
707 END Check_Class_Has_Options;
708
709
710 /*------------------------------------------------------------------------
711 FUNCTION: Check_Option_Exist
712 p_component_code is the class for which we will find out
713 if a option exist or not.
714 -------------------------------------------------------------------------*/
715 FUNCTION Check_Option_Exist
716 ( p_component_code IN VARCHAR2
717 ,p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE)
718 RETURN BOOLEAN
719 IS
720 I NUMBER;
721 --
722 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
723 --
724 BEGIN
725 IF l_debug_level > 0 THEN
726 oe_debug_pub.add('ENTERING CHECK_OPTION_EXIST' , 1 ) ;
727 END IF;
728
729 I := p_options_tbl.FIRST;
730 WHILE I is not null
731 LOOP
732 IF p_component_code = SUBSTR(p_options_tbl(I).component_code, 1,
733 INSTR(p_options_tbl(I).component_code, '-', -1) - 1)
734 THEN
735 IF l_debug_level > 0 THEN
736 oe_debug_pub.add('CHILD FOUND '|| P_OPTIONS_TBL ( I ) .COMPONENT_CODE , 1 ) ;
737 END IF;
738 RETURN true;
739 END IF;
740
741 I := p_options_tbl.NEXT(I);
742 END LOOP;
743
744 IF l_debug_level > 0 THEN
745 oe_debug_pub.add('LEAVING CHECK_OPTION_EXIST WITH NO CHILD' , 1 ) ;
746 END IF;
747 RETURN false;
748 EXCEPTION
749 WHEN OTHERS THEN
750 IF l_debug_level > 0 THEN
751 oe_debug_pub.add('CHECK_OPTION_EXIST EXCEPTION'|| SQLERRM , 1 ) ;
752 END IF;
753 RAISE;
754 END Check_Option_Exist;
755
756
757 /*------------------------------------------------------------------------
758 PROCEDURE: Check_Mut_Excl_Options
759 Opens a cursor of all the classes which have mutually exclusive options
760 set up in the BOM.
761 For every such class,
762 finds out if more than one option is selected. If so, it popuplates a error
763 message and sets the return status to error.
764 If all the classes with mutually exclusive options have only one option
765 selected, it returs success.
766 -------------------------------------------------------------------------*/
767
768 PROCEDURE Check_Mut_Excl_Options
769 ( p_top_bill_sequence_id IN NUMBER
770 ,p_effective_date IN DATE
771 ,p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
772 ,x_valid_config OUT NOCOPY VARCHAR2
773 ,x_return_status OUT NOCOPY VARCHAR2)
774 IS
775 I NUMBER;
776 l_top_bill_sequence_id NUMBER;
777 l_description VARCHAR2(240);
778 l_component_code VARCHAR2(1000);
779 l_result BOOLEAN;
780 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
781 l_valid_config VARCHAR2(10) := 'TRUE';
782
783 CURSOR MUTUALLY_EXCLUSIVE_OPTIONS IS
784 SELECT /*+ INDEX(BOMEXP BOM_EXPLOSIONS_N1) */
785 bomexp.description, bomexp.component_code
786 FROM BOM_EXPLOSIONS BOMEXP
787 WHERE bomexp.explosion_type = 'OPTIONAL'
788 AND bomexp.top_bill_sequence_id = p_top_bill_sequence_id
789 AND bomexp.plan_level >= 0
790 AND bomexp.effectivity_date <=
791 p_effective_date
792 AND bomexp.disable_date >
793 p_effective_date
794 AND bomexp.bom_item_type in ( 1, 2 ) /* Model, Class */
795 AND bomexp.mutually_exclusive_options = 1 /* Exclusive */
796 ORDER BY bomexp.sort_order;
797
798 --
799 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
800 --
801 BEGIN
802 IF l_debug_level > 0 THEN
803 oe_debug_pub.add('ENTERING CHECK_MUT_EXCL_OPTIONS' , 1 ) ;
804 END IF;
805 Print_Time('Check_Mut_Excl_Options start time');
806
807 FOR bom_rec in MUTUALLY_EXCLUSIVE_OPTIONS
808 LOOP
809 l_result := Mutually_Exclusive_Comps_exist
810 ( p_options_tbl => p_options_tbl
811 ,p_component_code => bom_rec.component_code);
812
813 IF l_result THEN
814 FND_MESSAGE.Set_Name('ONT', 'OE_VAL_CONFIG_EXCLUSIVE_CLASS');
815 FND_MESSAGE.Set_Token('CLASS', bom_rec.description);
816 OE_Msg_Pub.Add;
817 l_valid_config := 'FALSE';
818 l_return_status := FND_API.G_RET_STS_ERROR;
819 END IF;
820
821 END LOOP;
822
823 x_valid_config := l_valid_config;
824 x_return_status := l_return_status;
825
826 Print_Time('Check_Mut_Excl_Options end time');
827 IF l_debug_level > 0 THEN
831 WHEN OTHERS THEN
828 oe_debug_pub.add('LEAVING CHECK_MUT_EXCL_OPTIONS' , 1 ) ;
829 END IF;
830 EXCEPTION
832 IF l_debug_level > 0 THEN
833 oe_debug_pub.add('CHECK_MUT_EXCL_OPTIONS EXCEPTION'|| SQLERRM , 1 ) ;
834 END IF;
835 RAISE;
836 END Check_Mut_Excl_Options;
837
838
839 /*------------------------------------------------------------------------
840 FUNCTION Mutually_Exclusive_Comps_exist
841
842 This function will be called for 1 class with
843 mutually exclusive options at a time.
844
845 Loop through options table, find ou number of options for class
846 p_component_code, using the component_code field.
847 e.g.:
848 p_component_code => 100-200
849 p_options_tbl =>
850 100-200 -- mutually exclusive options set in BOM.
851 100-200-300 -- 1st
852 100-200-400 -- 2nd, this is error
853 100-500
854 100
855
856 IF a configuration has more than 1 components from a class
857 that has mutually exclusive options, this function returns true.
858 Else it returns false.
859 -------------------------------------------------------------------------*/
860
861 FUNCTION Mutually_Exclusive_Comps_exist
862 ( p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
863 ,p_component_code IN VARCHAR2)
864 RETURN BOOLEAN
865 IS
866 l_count NUMBER;
867 I NUMBER;
868 l_component VARCHAR2(1000);
869 --
870 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
871 --
872 BEGIN
873 IF l_debug_level > 0 THEN
874 oe_debug_pub.add('ENTERING MUTUALLY_EXCLUSIVE_COMPS_EXIST' , 1 ) ;
875 END IF;
876
877 I := p_options_tbl.FIRST;
878
879 l_count := 0;
880
881 WHILE I is not NULL AND l_count < 2
882 LOOP
883 l_component := SUBSTR(p_options_tbl(I).component_code,
884 1, (INSTR(p_options_tbl(I).component_code, '-', -1) - 1));
885
886 IF l_component = p_component_code AND
887 p_options_tbl(I).ordered_quantity <> 0 -- if 0, either will be deleted or closed
888 THEN
889 l_count := l_count + 1;
890 IF l_debug_level > 0 THEN
891 oe_debug_pub.add( L_COUNT ||' OPTION PRESENT'|| P_OPTIONS_TBL ( I ) .COMPONENT_CODE , 2 ) ;
892 END IF;
893 END IF;
894
895 I := p_options_tbl.NEXT(I);
896 END LOOP;
897
898 IF l_count >= 2 THEN
899 RETURN true;
900 ELSE
901 IF l_debug_level > 0 THEN
902 oe_debug_pub.add('NO MUTAUL EXCLUSION IN CLASS '|| P_COMPONENT_CODE , 1 ) ;
903 END IF;
904 RETURN false;
905 END IF;
906
907 IF l_debug_level > 0 THEN
908 oe_debug_pub.add('LEAVING MUTUALLY_EXCLUSIVE_COMPS_EXIST' , 1 ) ;
909 END IF;
910 EXCEPTION
911 WHEN OTHERS THEN
912 IF l_debug_level > 0 THEN
913 oe_debug_pub.add('MUTUALLY_EXCLUSIVE_COMPS_EXIST EXCEPTION'|| SQLERRM , 1 ) ;
914 END IF;
915 RAISE;
916 END Mutually_Exclusive_Comps_exist;
917
918
919 /*------------------------------------------------------------------------
920 PROCEDURE: Check_Mandatory_Classes
921 Opens a cursor which has all classes which are mandatory to this model.
922 For every such class,
923 check if at least one option is selectd. If not populte error messate and
924 set retruns status to error.
925 -------------------------------------------------------------------------*/
926
927 PROCEDURE Check_Mandatory_Classes
928 ( p_top_bill_sequence_id IN NUMBER
929 ,p_top_model_line_id IN NUMBER
930 ,p_effective_date IN DATE
931 ,p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
932 ,x_complete_config OUT NOCOPY VARCHAR2
933 ,x_return_status OUT NOCOPY VARCHAR2)
934 IS
935 l_top_bill_sequence_id NUMBER;
936 l_description VARCHAR2(240);
937 l_component_code VARCHAR2(1000);
938 l_result BOOLEAN;
939 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
940 l_complete_config VARCHAR2(10) := 'TRUE';
941
942 CURSOR MANDATORY_COMPONENTS IS
943 SELECT /*+ INDEX(BOMEXP BOM_EXPLOSIONS_N1) */
944 bomexp.description, bomexp.component_code
945 --,bomexp.sort_order???? perf
946 FROM bom_explosions bomexp
947 WHERE bomexp.explosion_type = 'OPTIONAL'
948 AND bomexp.top_bill_sequence_id = p_top_bill_sequence_id
949 AND bomexp.plan_level >= 0
950 AND bomexp.effectivity_date <=
951 p_effective_date
952 AND bomexp.disable_date >
953 p_effective_date
954 AND bomexp.bom_item_type IN ( 1, 2 ) -- Model, Class
955 AND bomexp.optional = 2; -- Mandatory
956
957 /*
958 CURSOR MANDATORY_COMPONENTS IS
959 SELECT bomexp.description, bomexp.component_code
960 FROM OE_ORDER_LINES OECFG
961 , BOM_EXPLOSIONS BOMEXP
962 WHERE OECFG.TOP_MODEL_LINE_ID = p_top_model_line_id
963 AND OECFG.SERVICE_REFERENCE_LINE_ID IS NULL
964 AND OECFG.ITEM_TYPE_CODE IN ( 'MODEL', 'CLASS' )
965 AND BOMEXP.EXPLOSION_TYPE = 'OPTIONAL'
966 AND BOMEXP.TOP_BILL_SEQUENCE_ID = p_top_bill_sequence_id ???????? + 0
967 AND BOMEXP.PLAN_LEVEL >= 0
968 AND BOMEXP.EFFECTIVITY_DATE <= sysdate
969 AND BOMEXP.DISABLE_DATE > sysdate
970 AND BOMEXP.BOM_ITEM_TYPE IN ( 1, 2 ) Model, Class
971 AND BOMEXP.OPTIONAL = 2 Mandatory
972 AND BOMEXP.Component_code like OECFG.component_code || '%'
973 AND SUBSTR( BOMEXP.COMPONENT_CODE, 1,
974 LENGTH( RTRIM( BOMEXP.COMPONENT_CODE,
975 '0123456789' ) ) - 1 ) = OECFG.COMPONENT_CODE
976 AND NOT EXISTS (
977 SELECT NULL
981 AND OEOPT.COMPONENT_CODE = BOMEXP.COMPONENT_CODE
978 FROM OE_ORDER_LINES OEOPT
979 WHERE OEOPT.TOP_MODEL_LINE_ID = p_top_model_line_id
980 AND OEOPT.SERVICE_REFERENCE_LINE_ID IS NULL
982 )
983 ORDER BY BOMEXP.SORT_ORDER;
984 */
985 --
986 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
987 --
988 BEGIN
989 IF l_debug_level > 0 THEN
990 oe_debug_pub.add('ENTERING CHECK_MANDATORY_CLASSES' , 1 ) ;
991 END IF;
992 Print_Time('Check_Mandatory_Classes start time');
993
994 for bom_rec in MANDATORY_COMPONENTS
995 LOOP
996 l_result := Mandatory_Comps_Missing
997 ( p_options_tbl => p_options_tbl
998 ,p_component_code => bom_rec.component_code);
999
1000 IF l_result THEN
1001 FND_MESSAGE.Set_Name('ONT', 'OE_VAL_CONFIG_MANDATORY_CLASS');
1002 FND_MESSAGE.Set_Token('CLASS', bom_rec.description);
1003 OE_Msg_Pub.Add;
1004 l_complete_config := 'FALSE';
1005 l_return_status := FND_API.G_RET_STS_ERROR;
1006 END IF;
1007
1008 END LOOP;
1009
1010 x_complete_config := l_complete_config;
1011 x_return_status := l_return_status;
1012
1013 Print_Time('Check_Mandatory_Classes end time');
1014 IF l_debug_level > 0 THEN
1015 oe_debug_pub.add('LEAVING CHECK_MANDATORY_CLASSES '|| L_RETURN_STATUS , 1 ) ;
1016 END IF;
1017 EXCEPTION
1018 WHEN OTHERS THEN
1019 IF l_debug_level > 0 THEN
1020 oe_debug_pub.add('CHECK_MANDATORY_CLASSES EXCEPTION'|| SQLERRM , 1 ) ;
1021 END IF;
1022 RAISE;
1023 END Check_Mandatory_Classes;
1024
1025
1026 /*------------------------------------------------------------------------
1027 FUNCTION: Mandatory_Comps_Missing
1028
1029 This function finds out if a mandatory component is missing
1030 in the configuration, working on one class at a time.
1031
1032 It uses the pl/sql table of options => p_options_tbl to figure out if
1033 any of the mandatory components for a particular class/model =>
1034 p_component_code is missing.
1035
1036 Returns true, if finds out that mandatory component is missing.
1037 Else returns false.
1038 e.g.=>
1039 p_component_code => 100-500
1040 p_options_tbl =>
1041 100-200
1042 100-200-300
1043 100-500 -- mandatory class w/o options is error.
1044 100
1045
1046 -------------------------------------------------------------------------*/
1047 FUNCTION Mandatory_Comps_Missing
1048 ( p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE
1049 ,p_component_code IN VARCHAR2)
1050 RETURN BOOLEAN
1051 IS
1052 I NUMBER;
1053 l_found BOOLEAN;
1054 l_component VARCHAR2(1000);
1055 J NUMBER;
1056 l_parent BOOLEAN;
1057 l_parent_component VARCHAR2(1000);
1058 --
1059 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1060 --
1061 BEGIN
1062
1063 IF l_debug_level > 0 THEN
1064 oe_debug_pub.add('ENTERING MANDATORY_COMPS_MISSING' , 1 ) ;
1065 END IF;
1066 IF l_debug_level > 0 THEN
1067 oe_debug_pub.add('COMPONENT IN ' || P_COMPONENT_CODE , 2 ) ;
1068 END IF;
1069
1070 -- Check for mandatory component parent existance in the table.
1071 -- Look for options only if the parent of the passed component is
1072 -- found. Other wise return false.
1073
1074 l_parent := false;
1075
1076 l_parent_component := SUBSTR(p_component_code,1,
1077 (INSTR(p_component_code, '-', -1) -1));
1078
1079 J := p_options_tbl.FIRST;
1080
1081 WHILE J is not NULL AND NOT l_parent
1082 LOOP
1083
1084 --bug3542229: Front porting
1085 --Make sure that the option class itself is present
1086 --The parent may be present due to selection of some other child
1087 --as well
1088 IF l_debug_level > 0 THEN
1089 oe_debug_pub.add('ORDQTY:'||p_options_tbl(J).ordered_quantity);
1090 END IF;
1091
1092 --IF p_options_tbl(J).component_code = p_component_code AND
1093 IF p_options_tbl(J).component_code = l_parent_component AND --fp: 3618150
1094 p_options_tbl(J).ordered_quantity <> 0
1095 THEN
1096 l_parent := true;
1097 IF l_debug_level > 0 THEN
1098 oe_debug_pub.add('PARENT FOUND ' || L_PARENT_COMPONENT , 2 ) ;
1099 END IF;
1100 END IF;
1101
1102 J := p_options_tbl.NEXT(J);
1103
1104 END LOOP;
1105
1106
1107 IF l_parent THEN
1108 I := p_options_tbl.FIRST;
1109
1110 l_found := false;
1111
1112 WHILE I is not NULL AND NOT l_found
1113 LOOP
1114 l_component := SUBSTR(p_options_tbl(I).component_code, 1,
1115 (INSTR(p_options_tbl(I).component_code, '-', -1) - 1));
1116
1117 IF l_component = p_component_code AND
1118 p_options_tbl(I).ordered_quantity <> 0
1119 THEN
1120 IF l_debug_level > 0 THEN
1121 oe_debug_pub.add('MANDATORY OPTION PRESENT'|| P_OPTIONS_TBL ( I ) .COMPONENT_CODE , 2 ) ;
1122 END IF;
1123 l_found := true;
1124 END IF;
1125
1126 I := p_options_tbl.NEXT(I);
1127 END LOOP;
1128
1129 IF l_found THEN
1130 RETURN false;
1131 ELSE
1132 IF l_debug_level > 0 THEN
1133 oe_debug_pub.add('MANDATORY COMP MISSING IN CLASS '|| L_COMPONENT , 1 ) ;
1134 END IF;
1135 RETURN true;
1136 END IF;
1137
1138 ELSE
1139
1140 RETURN false;
1141
1142 END IF;
1143
1144 IF l_debug_level > 0 THEN
1145 oe_debug_pub.add('LEAVING MANDATORY_COMPS_MISSING' , 1 ) ;
1146 END IF;
1147 EXCEPTION
1151 END IF;
1148 WHEN OTHERS THEN
1149 IF l_debug_level > 0 THEN
1150 oe_debug_pub.add('MANDATORY_COMPS_MISSING EXCEPTION'|| SQLERRM , 1 ) ;
1152 RAISE;
1153 END Mandatory_Comps_Missing;
1154
1155
1156 /*------------------------------------------------------------------------
1157 PROCEDURE Print_Time
1158
1159 -------------------------------------------------------------------------*/
1160
1161 PROCEDURE Print_Time(p_msg IN VARCHAR2)
1162 IS
1163 l_time VARCHAR2(100);
1164 --
1165 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1166 --
1167 BEGIN
1168 l_time := to_char (new_time (sysdate, 'PST', 'EST'),
1169 'DD-MON-YY HH24:MI:SS');
1170 IF l_debug_level > 0 THEN
1171 oe_debug_pub.add( P_MSG || ': '|| L_TIME , 1 ) ;
1172 END IF;
1173 END Print_Time;
1174
1175
1176 /*------------------------------------------------------------------------
1177 PROCEDURE Handle_Ret_Status
1178
1179 -------------------------------------------------------------------------*/
1180
1181 PROCEDURE Handle_Ret_Status
1182 (p_valid_config IN VARCHAR2 := NULL
1183 ,p_complete_config IN VARCHAR2 := NULL)
1184 IS
1185 --
1186 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1187 --
1188 BEGIN
1189 IF nvl(p_valid_config, 'TRUE') = 'FALSE' THEN
1190 IF l_debug_level > 0 THEN
1191 oe_debug_pub.add('SETTING VALID_CONFIG TO FALSE' , 1 ) ;
1192 END IF;
1193 G_VALID_CONFIG := 'FALSE';
1194 END IF;
1195
1196 IF nvl(p_complete_config, 'TRUE') = 'FALSE' THEN
1197 IF l_debug_level > 0 THEN
1198 oe_debug_pub.add('SETTING COMPLETE_CONFIG TO FALSE' , 1 ) ;
1199 END IF;
1200 G_VALID_CONFIG := 'FALSE';
1201 END IF;
1202 END Handle_Ret_Status;
1203
1204
1205
1206 /*------------------------------------------------------------------------
1207 FUNCTION: Get_Parent_Quantity
1208 Loops through p_options_tbl. Finds out the parent of p_component_code
1209 using component_code field in the p_options_tbl. returns parent's qty.
1210
1211 Not used, similar code in Check_Parent_Exists.
1212 -------------------------------------------------------------------------*/
1213 FUNCTION Get_Parent_Quantity
1214 ( p_component_code IN VARCHAR2
1215 ,p_top_model_line_id IN NUMBER
1216 ,p_options_tbl IN VALIDATE_OPTIONS_TBL_TYPE)
1217 RETURN NUMBER
1218 IS
1219 I NUMBER;
1220 l_open_flag VARCHAR2(1);
1221 l_ordered_quantity NUMBER;
1222 l_ordered_item VARCHAR2(2000);
1223 l_line_number NUMBER;
1224 l_shipment_number NUMBER;
1225 l_option_number NUMBER;
1226 --
1227 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1228 --
1229 BEGIN
1230 IF l_debug_level > 0 THEN
1231 oe_debug_pub.add('ENTERING GET_PARENT_QUANTITY' , 1 ) ;
1232 END IF;
1233 IF l_debug_level > 0 THEN
1234 oe_debug_pub.add('FIND PARENT QTY FOR: '|| P_COMPONENT_CODE , 1 ) ;
1235 END IF;
1236
1237 I := p_options_tbl.FIRST;
1238 WHILE I is not null
1239 LOOP
1240 IF p_options_tbl(I).component_code = SUBSTR(p_component_code, 1,
1241 INSTR(p_component_code, '-', -1) - 1)
1242 THEN
1243 IF l_debug_level > 0 THEN
1244 oe_debug_pub.add('PARENT FOUND '|| P_OPTIONS_TBL ( I ) .COMPONENT_CODE , 1 ) ;
1245 END IF;
1246 RETURN p_options_tbl(I).ordered_quantity;
1247 END IF;
1248
1249 I := p_options_tbl.NEXT(I);
1250 END LOOP;
1251
1252 IF l_debug_level > 0 THEN
1253 oe_debug_pub.add('NO PARENT FOR ' || P_COMPONENT_CODE , 1 ) ;
1254 END IF;
1255 RAISE FND_API.G_EXC_ERROR;
1256
1257 EXCEPTION
1258 WHEN FND_API.G_EXC_ERROR THEN
1259 IF l_debug_level > 0 THEN
1260 oe_debug_pub.add('PARENT NOT FOUND , MAY BE CANCELED' , 1 ) ;
1261 END IF;
1262
1263 BEGIN
1264
1265 SELECT open_flag, ordered_quantity
1266 INTO l_open_flag, l_ordered_quantity
1267 FROM oe_order_lines
1268 where line_id =
1269 (SELECT line_id
1270 FROM oe_order_lines
1271 WHERE top_model_line_id = p_top_model_line_id
1272 AND component_code = SUBSTR(p_component_code, 1,
1273 INSTR(p_component_code, '-', -1) - 1));
1274
1275 IF l_debug_level > 0 THEN
1276 oe_debug_pub.add('OPEN_FLAG , QTY: '|| L_OPEN_FLAG || L_ORDERED_QUANTITY , 3 ) ;
1277 END IF;
1278
1279 IF l_open_flag = 'N' THEN
1280 IF l_debug_level > 0 THEN
1281 oe_debug_pub.add('PARENT WAS CANCELLED' , 3 ) ;
1282 END IF;
1283 RETURN l_ordered_quantity;
1284 END IF;
1285 EXCEPTION
1286 WHEN NO_DATA_FOUND THEN
1287 IF l_debug_level > 0 THEN
1288 oe_debug_pub.add('NO PARENT' , 3 ) ;
1289 END IF;
1290
1291 SELECT ordered_item, line_number, shipment_number, option_number
1292 INTO l_ordered_item, l_line_number, l_shipment_number, l_option_number
1293 FROM oe_order_lines
1294 WHERE top_model_line_id = p_top_model_line_id
1295 AND component_code = p_component_code;
1296
1297 FND_MESSAGE.Set_Name('ONT', 'OE_CONFIG_NO_PARENT');
1298 FND_MESSAGE.Set_TOKEN('ITEM', l_ordered_item);
1299 FND_MESSAGE.Set_TOKEN
1300 ('LINE', l_line_number || '.'|| l_shipment_number ||'.'|| l_option_number);
1301 OE_MSG_PUB.Add;
1302 RAISE FND_API.G_EXC_ERROR;
1303 END;
1304
1305 WHEN OTHERS THEN
1306 IF l_debug_level > 0 THEN
1310 END Get_Parent_Quantity;
1307 oe_debug_pub.add('GET_PARENT_QUANTITY EXCEPTION'|| SQLERRM , 1 ) ;
1308 END IF;
1309 RAISE;
1311
1312 END BOM_Config_Validation_Pub;
[Home] [Help]