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