[Home] [Help]
PACKAGE BODY: APPS.OE_PROCESS_OPTIONS_PVT
Source
1 PACKAGE BODY OE_Process_Options_Pvt AS
2 /* $Header: OEXVOPTB.pls 120.2 2005/11/16 02:47:36 apani noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_Process_Options_Pvt';
5
6
7 /*-----------------------------------------------------------------------
8 Forward Declarations
9 ------------------------------------------------------------------------*/
10
11 Procedure Handle_DML
12 ( p_options_tbl IN OE_Process_Options_Pvt.Selected_Options_Tbl_Type
13 ,p_model_line_rec IN OUT NOCOPY OE_Order_Pub.Line_Rec_Type
14 ,p_ui_flag IN VARCHAR2
15 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
16
17
18 Procedure Fill_In_Classes
19 ( p_top_model_line_id IN NUMBER
20 ,p_model_component IN VARCHAR2
21 ,p_model_quantity IN NUMBER
22 ,p_top_bill_sequence_id IN NUMBER
23 ,p_effective_date IN DATE
24 ,p_ui_flag IN VARCHAR2
25 ,p_x_options_tbl IN OUT NOCOPY OE_Process_Options_Pvt.SELECTED_OPTIONS_TBL_TYPE
26 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
27
28
29 PROCEDURE component_exists
30 ( p_component IN VARCHAR2
31 ,p_options_tbl IN OE_Process_Options_Pvt.Selected_Options_Tbl_Type
32 ,x_result OUT NOCOPY /* file.sql.39 change */ BOOLEAN);
33
34
35 PROCEDURE Check_Duplicate_Components
36 ( p_options_tbl IN OE_Process_Options_Pvt.Selected_Options_Tbl_Type
37 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
38 );
39
40
41 PROCEDURE Load_BOM_Table
42 ( p_options_tbl IN OE_Process_Options_Pvt.SELECTED_OPTIONS_TBL_TYPE
43 ,x_bom_validation_tbl OUT NOCOPY OE_CONFIG_VALIDATION_PVT.VALIDATE_OPTIONS_TBL_TYPE);
44
45
46 PROCEDURE Handle_Disabled_Options
47 ( p_x_option_rec IN OUT NOCOPY OE_Process_Options_Pvt.SELECTED_OPTIONS_REC
48 ,p_top_model_line_id IN NUMBER);
49
50 /* --------------------------------------------------------------------
51 Procedure Name : Process_Config_Options
52 Description :
53 This procedure works on the selected options from options window.
54 1) It first completes the configuration, which means some of the classes
55 that are not selected by the user are filled in the table of options.
56 2) After that the configuration is validated using BOM rules.
57 3) Then we call process_order API to create the option / class lines in
58 oe tables.
59
60 There can not be a duplicate class(llid caode will fail),
61 however I do not know about a duplicate options. May be we should
62 have a handled exception for this.
63
64 Exception block:
65 options window UI populates process_messages window to display errors.
66 If the return status is unexp error, the continue button is disabled
67 if it is execution error, continue button on msg window is enabled.
68
69 we want user to continue only in case of bom based validation failure.
70 other cases no matter what is the ret status(ex: process_order returned
71 execution error, we can not commit user changes, so no point in keeping
72 continue enabled.)
73 So I am manipulating the return status to always return unexp error
74 in exception handling block here, in case of a UI call.
75 In case of delayed request call, we will return what ever
76 is the error.
77 The return status is not used in any other way by Options window UI.
78
79 Change record:
80 3687870 : check the fulfilled_flag, open flag etc. for UI.
81 ---------------------------------------------------------------------- */
82
83 Procedure Process_Config_Options
84 ( p_options_tbl IN OE_Process_Options_Pvt.Selected_Options_Tbl_Type
85 ,p_header_id IN NUMBER
86 ,p_top_model_line_id IN NUMBER
87 ,p_ui_flag IN VARCHAR2 := 'Y'
88 ,p_caller IN VARCHAR2 := '' -- bug 4636208
89 ,x_valid_config OUT NOCOPY /* file.sql.39 change */ VARCHAR2
90 ,x_complete_config OUT NOCOPY /* file.sql.39 change */ VARCHAR2
91 ,x_change_flag OUT NOCOPY /* file.sql.39 change */ VARCHAR2
92 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
93 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
94 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
95 )
96 IS
97 l_model_line_rec OE_Order_Pub.Line_Rec_Type;
98 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
99 l_validation_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
100 l_options_tbl OE_Process_Options_Pvt.Selected_Options_Tbl_Type;
101 l_db_options_tbl OE_Process_Options_Pvt.SELECTED_OPTIONS_TBL_TYPE;
102 l_bom_validation_tbl OE_CONFIG_VALIDATION_PVT.VALIDATE_OPTIONS_TBL_TYPE;
103 l_count NUMBER;
104 I NUMBER;
105 l_index NUMBER;
106 l_operation VARCHAR2(1);
107 l_valid_config VARCHAR2(10);
108 l_complete_config VARCHAR2(10);
109 l_deleted_options_tbl OE_Order_PUB.request_tbl_type;
110 l_updated_options_tbl OE_Order_PUB.request_tbl_type;
111 l_rev_date DATE;
112 l_frozen_model_bill VARCHAR2(1);
113 --
114 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
115 --
116
117 BEGIN
118 IF l_debug_level > 0 THEN
119 oe_debug_pub.add( 'ENTERING PROCESS_CONFIG_OPTIONS' , 5 ) ;
120 END IF;
121
122 Print_Time('Process_Config_Options start time');
123
124 IF p_ui_flag = 'Y' Then
125 OE_Msg_Pub.Initialize;
126 END IF;
127
128 OE_Msg_Pub.Set_Msg_Context
129 ( p_entity_code => OE_Globals.G_ENTITY_LINE
130 ,p_entity_id => p_top_model_line_id
131 ,p_header_id => p_header_id
132 ,p_line_id => p_top_model_line_id);
133
134
135 OE_LINE_UTIL.Lock_Row
136 ( p_line_id => p_top_model_line_id
137 ,p_x_line_rec => l_model_line_rec
138 ,x_return_status => l_return_status);
139
140 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
141 FND_MESSAGE.Set_Name('ONT', 'OE_ORDER_OBJECT_LOCKED');
142 OE_MSG_PUB.Add;
143 END IF;
144
145 Handle_Ret_Status(p_return_status => l_return_status);
146
147 IF p_ui_flag = 'Y' Then
148
149 oe_debug_pub.add('fulfilled_flag'|| l_model_line_rec.fulfilled_flag , 1 ) ;
150 oe_debug_pub.add('open_flag'|| l_model_line_rec.open_flag , 1 ) ;
151
152 IF nvl(l_model_line_rec.open_flag, 'Y') = 'N' THEN
153 FND_MESSAGE.Set_Name('ONT', 'OE_CONFIG_MODEL_CLOSED');
154 FND_MESSAGE.Set_Token('MODEL', l_model_line_rec.ordered_item);
155 OE_MSG_PUB.Add;
156
157 IF l_debug_level > 0 then
158 oe_debug_pub.add('model line is closed', 1);
159 END IF;
160 RAISE FND_API.G_EXC_ERROR;
161 END IF;
162
163 IF l_model_line_rec.fulfilled_flag = 'Y' THEN
164 FND_MESSAGE.Set_Name('ONT', 'OE_CONFIG_MODEL_FULFILLED');
165 FND_MESSAGE.Set_Token('MODEL', l_model_line_rec.ordered_item);
166 OE_MSG_PUB.Add;
167 IF l_debug_level > 0 then
168 oe_debug_pub.add('model line is fulfilled', 1);
169 END IF;
170 RAISE FND_API.G_EXC_ERROR;
171 END IF;
172
173 IF nvl(l_model_line_rec.model_remnant_flag, 'N') = 'Y' THEN
174 FND_MESSAGE.Set_Name('ONT', 'OE_CONFIG_REMNANT_NO_CHANGES');
175 FND_MESSAGE.Set_Token('MODEL', l_model_line_rec.ordered_item);
176 OE_MSG_PUB.Add;
177 IF l_debug_level > 0 then
178 oe_debug_pub.add('remnant model', 1);
179 END IF;
180 RAISE FND_API.G_EXC_ERROR;
181 END IF;
182 END IF;
183
184
185 OE_MSG_PUB.update_msg_context
186 ( p_entity_code => 'LINE'
187 ,p_entity_id => l_model_line_rec.line_id
188 ,p_header_id => l_model_line_rec.header_id
189 ,p_line_id => l_model_line_rec.line_id
190 ,p_order_source_id => l_model_line_rec.order_source_id
191 ,p_orig_sys_document_ref => l_model_line_rec.orig_sys_document_ref
192 ,p_orig_sys_document_line_ref => l_model_line_rec.orig_sys_line_ref
193 ,p_orig_sys_shipment_ref => l_model_line_rec.orig_sys_shipment_ref
194 ,p_change_sequence => l_model_line_rec.change_sequence
195 ,p_source_document_id => l_model_line_rec.source_document_id
196 ,p_source_document_line_id => l_model_line_rec.source_document_line_id
197 ,p_source_document_type_id => l_model_line_rec.source_document_type_id);
198
199 IF l_debug_level > 0 THEN
200 oe_debug_pub.add( 'CALLING EXPLODE_BILL' , 1 ) ;
201 END IF;
202
203 oe_config_pvt.Explode_Bill
204 ( p_model_line_rec => l_model_line_rec
205 ,x_config_effective_date => l_rev_date
206 ,x_frozen_model_bill => l_frozen_model_bill
207 ,x_return_status => l_return_status);
208
209 Handle_Ret_Status(p_return_status => l_return_status);
210
211
212 l_options_tbl := p_options_tbl;
213
214 -- since fill_in_classes and validation needs all the options
215 -- get the ones which arenot passed and populate the options_tbl
216 -- including the model line, the operation should be NONE or CREATE.
217
218 -- if first time create and UI, use i/p options_tbl as base
219 -- if update/del and UI., use db_options_tbl as base.
220 -- in case batch val, db_options tbl is sent in by caller.
221 -- this is for perf reasons.
222
223 IF p_ui_flag = 'Y' THEN
224 IF l_debug_level > 0 THEN
225 oe_debug_pub.add( 'GETTING PREVIOUSLY SAVED OPTIONS FROM DB' , 2 ) ;
226 END IF;
227
228 Get_Options_From_DB( p_top_model_line_id => p_top_model_line_id
229 ,p_get_model_line => TRUE
230 ,p_caller => 'OPTIONS WINDOW UI'
231 ,p_query_criteria => 4
232 ,x_disabled_options => l_frozen_model_bill
233 ,x_options_tbl => l_db_options_tbl);
234
235 l_count := l_options_tbl.LAST;
236 I := l_db_options_tbl.FIRST;
237 WHILE I is not null
238 LOOP
239
240 BEGIN
241 -- if already exist, do not add, continue
242 l_index := Find_Matching_comp_index
243 ( p_options_tbl => l_options_tbl --=> sent in by caller.
244 ,p_comp_code => l_db_options_tbl(I).component_code);
245
246 IF l_debug_level > 0 THEN
247 oe_debug_pub.add
248 ('CONTINUE: '|| L_OPTIONS_TBL ( L_INDEX ) .COMPONENT_CODE , 2 ) ;
249 END IF;
250 EXCEPTION
251 WHEN FND_API.G_EXC_ERROR THEN
252 l_count := l_count + 1;
253 l_options_tbl(l_count) := l_db_options_tbl(I);
254
255 IF l_debug_level > 0 THEN
256 oe_debug_pub.add
257 (I ||' ADD DB COMP: '|| L_DB_OPTIONS_TBL ( I ).COMPONENT_CODE , 1);
258 END IF;
259
260 END;
261
262 I := l_db_options_tbl.NEXT(I);
263 END LOOP;
264 END IF;
265
266 IF l_debug_level > 0 THEN
267 oe_debug_pub.add( 'CALLING CHECK_DUPLICATE_COMPONENTS' , 1 ) ;
268 END IF;
269
270 Check_Duplicate_Components
271 ( p_options_tbl => l_options_tbl
272 ,x_return_status => l_return_status );
273
274 Handle_Ret_Status(p_return_status => l_return_status);
275
276
277 -- cascade updates/deletes
278 IF p_ui_flag = 'Y' THEN
279
280 Prepare_Cascade_Tables
281 ( p_options_tbl => l_options_tbl
282 ,p_top_model_line_id => p_top_model_line_id
283 ,p_x_updated_options_tbl => l_updated_options_tbl
284 ,p_x_deleted_options_tbl => l_deleted_options_tbl);
285
286 IF l_updated_options_tbl.COUNT > 0 OR
287 l_deleted_options_tbl.COUNT > 0 THEN
288
289 IF l_debug_level > 0 THEN
290 oe_debug_pub.add( 'CALLING CASCADE_UPDATES_DELETES' , 1 ) ;
291 END IF;
292
293 OE_Config_Util.Cascade_Updates_Deletes
294 ( p_model_line_id => p_top_model_line_id
295 ,p_model_component => l_model_line_rec.component_code
296 ,p_x_options_tbl => l_options_tbl
297 ,p_deleted_options_tbl => l_deleted_options_tbl
298 ,p_updated_options_tbl => l_updated_options_tbl
299 ,p_ui_flag => p_ui_flag
300 ,x_return_status => l_return_status);
301
302 END IF;
303 END IF; -- if ui flag is Y
304
305
306 IF l_debug_level > 0 THEN
307 oe_debug_pub.add( 'CALLING FILL_IN_CLASSES' , 1 ) ;
308 END IF;
309
310
311 Fill_In_Classes
312 ( p_top_model_line_id => p_top_model_line_id
313 ,p_model_component => l_model_line_rec.component_code
314 ,p_model_quantity => l_model_line_rec.ordered_quantity
315 ,p_top_bill_sequence_id => l_model_line_rec.component_sequence_id
316 ,p_effective_date => l_rev_date
317 ,p_ui_flag => p_ui_flag
318 ,p_x_options_tbl => l_options_tbl
319 ,x_return_status => l_return_status);
320
321 Handle_Ret_Status(p_return_status => l_return_status);
322
323
324 Load_BOM_Table
325 ( p_options_tbl => l_options_tbl
326 ,x_bom_validation_tbl => l_bom_validation_tbl);
327
328
329 IF l_debug_level > 0 THEN
330 oe_debug_pub.add( 'CALLING BOM_BASED_VALIDATION' , 1 ) ;
331 END IF;
332
333 IF l_bom_validation_tbl.COUNT > 0 THEN
334
335 OE_CONFIG_VALIDATION_PVT.Bom_Based_Config_Validation
336 ( p_top_model_line_id => p_top_model_line_id
337 ,p_options_tbl => l_bom_validation_tbl
338 ,x_valid_config => l_valid_config
339 ,x_complete_config => l_complete_config
340 ,x_return_status => l_validation_status);
341
342 x_valid_config := l_valid_config;
343 x_complete_config := l_complete_config;
344
345 IF l_debug_level > 0 THEN
346 oe_debug_pub.add( 'VALIDATION STATUS '|| L_VALIDATION_STATUS , 1 ) ;
347 END IF;
348
349 IF l_validation_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
351 END IF;
352
353 IF l_validation_status <> FND_API.G_RET_STS_SUCCESS THEN
354 FND_MESSAGE.Set_Name('ONT', 'OE_CONFIG_VALIDATION_FAILURE');
355 OE_Msg_Pub.Add;
356 END IF; -- status = success
357
358 -- added for bug 4636208
359 IF p_caller = 'BOOKING' AND
360 (l_valid_config = 'FALSE' OR l_complete_config = 'FALSE') THEN
361
362 IF l_debug_level > 0 THEN
363 oe_debug_pub.add('CALLER IS BOOKING AND ERRORED OUT' , 2 );
364 END IF;
365
366 x_return_status := l_validation_status;
367 RETURN;
368 END IF; -- bug 4636208 ends
369
370 IF nvl(l_model_line_rec.booked_flag, 'N' ) = 'Y' THEN
371 IF l_debug_level > 0 THEN
372 oe_debug_pub.add( 'ORDER IS BOOKED' , 1 ) ;
373 END IF;
374
375 OE_Config_Pvt.put_hold_and_release_hold
376 (p_header_id => p_header_id,
377 p_line_id => p_top_model_line_id,
378 p_valid_config => l_valid_config,
379 p_complete_config => l_complete_config,
380 x_msg_count => x_msg_count,
381 x_msg_data => x_msg_data,
382 x_return_status => l_return_status);
383
384 Handle_Ret_Status(p_return_status => l_return_status);
385 END IF;
386
387 ELSE
388 IF l_debug_level > 0 THEN
389 oe_debug_pub.add( 'NO OPTIONS TO VALIDATE' , 1 ) ;
390 END IF;
391 END IF;
392
393
394 IF l_debug_level > 0 THEN
395 oe_debug_pub.add( 'CALLING HANDLE_DML' , 1 ) ;
396 END IF;
397
398 Handle_DML
399 ( p_options_tbl => l_options_tbl
400 ,p_model_line_rec => l_model_line_rec
401 ,p_ui_flag => p_ui_flag
402 ,x_return_status => l_return_status );
403
404 Handle_Ret_Status(p_return_status => l_return_status);
405
406 oe_msg_pub.count_and_get
407 ( p_count => x_msg_count
408 , p_data => x_msg_data );
409
410 IF l_debug_level > 0 THEN
411 oe_debug_pub.add( 'NO. OF MESSAGES ' || X_MSG_COUNT , 3 ) ;
412 oe_debug_pub.add( 'MESSAGES ' || X_MSG_DATA , 3 ) ;
413 END IF;
414
415 Print_Time('Process_Config_Options end time');
416
417
418 -- from the options window, we want to give user a choice to
419 -- save or not to save. from sales order form, we save
420 -- (unless there is an unexpected error). And
421 -- populate messages, user can go and correct the
422 -- configuratio based on the messages.
423
424 IF p_ui_flag = 'Y' THEN
425 x_return_status := l_validation_status;
426 ELSE
427 x_return_status := l_return_status;
428 END IF;
429
430 IF l_debug_level > 0 THEN
431 oe_debug_pub.add('LEAVING PROCESS_CONFIG_OPTIONS'||X_RETURN_STATUS,5);
432 END IF;
433
434 EXCEPTION
435
436 -- IMP please read procedure description.
437
438 WHEN FND_API.G_EXC_ERROR THEN
439 IF l_debug_level > 0 THEN
440 oe_debug_pub.add( 'EXCEPTION IN PROCESS_CONFIG_OPTIONS'|| SQLERRM ,1);
441 END IF;
442
443 IF p_ui_flag = 'Y' THEN
444 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
445 ELSE
446 x_return_status := FND_API.G_RET_STS_ERROR;
447 END IF;
448
449 oe_msg_pub.count_and_get
450 ( p_count => x_msg_count
451 , p_data => x_msg_data);
452
453 IF l_debug_level > 0 THEN
454 oe_debug_pub.add( 'NO. OF MESSAGES ' || X_MSG_COUNT , 3 ) ;
455 oe_debug_pub.add( 'MESSAGES ' || X_MSG_DATA , 3 ) ;
456 END IF;
457
458 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
459 IF l_debug_level > 0 THEN
460 oe_debug_pub.add('EXCEPTION IN PROCESS_CONFIG_OPTIONS'|| SQLERRM ,1);
461 END IF;
462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
463
464 oe_msg_pub.count_and_get
465 ( p_count => x_msg_count
466 , p_data => x_msg_data);
467
468 IF l_debug_level > 0 THEN
469 oe_debug_pub.add( 'NO. OF MESSAGES ' || X_MSG_COUNT , 3 ) ;
470 oe_debug_pub.add( 'MESSAGES ' || X_MSG_DATA , 3 ) ;
471 END IF;
472
473 WHEN OTHERS THEN
474 IF l_debug_level > 0 THEN
475 oe_debug_pub.add( 'EXCEPTION IN PROCESS_CONFIG_OPTIONS'|| SQLERRM ,1);
476 oe_debug_pub.add( 'ERROR: ' || SUBSTR ( SQLERRM , 1 , 100 ) , 1 ) ;
477 END IF;
478
479 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR)
480 THEN
481 oe_msg_pub.Add_Exc_Msg
482 ( G_PKG_NAME
483 , 'Process_Config');
484 END IF;
485
486 oe_msg_pub.count_and_get
487 ( p_count => x_msg_count
488 , p_data => x_msg_data );
489
490 IF l_debug_level > 0 THEN
491 oe_debug_pub.add( 'NO. OF MESSAGES ' || X_MSG_COUNT , 3 ) ;
492 oe_debug_pub.add( 'MESSAGES ' || X_MSG_DATA , 3 ) ;
493 END IF;
494 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
495
496 END Process_Config_Options;
497
498
499 /*--------------------------------------------------------
500 PROCEDURE : Prepare_Cascade_Tables
501
502 used to prepare i/p tables for cascade_update_deletes API.
503 handles diabled options also.
504 3563690 => pass ordered_item in param10
505 ----------------------------------------------------------*/
506 PROCEDURE Prepare_Cascade_Tables
507 ( p_options_tbl IN OUT NOCOPY
508 OE_Process_Options_Pvt.Selected_Options_Tbl_Type
509 ,p_top_model_line_id IN NUMBER
510 ,p_x_updated_options_tbl IN OUT NOCOPY OE_Order_PUB.request_tbl_type
511 ,p_x_deleted_options_tbl IN OUT NOCOPY OE_Order_PUB.request_tbl_type)
512 IS
513 l_count NUMBER;
514 I NUMBER;
515 l_index NUMBER;
516 l_req_rec OE_Order_Pub.Request_Rec_Type;
517 --
518 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
519 --
520 BEGIN
521
522 IF l_debug_level > 0 THEN
523 oe_debug_pub.add('entering Prepare_Cascade_Tables', 3 );
524 END IF;
525
526 l_count := p_x_updated_options_tbl.COUNT;
527 l_index := p_x_deleted_options_tbl.COUNT;
528
529 I := p_options_tbl.FIRST;
530 WHILE I is not null
531 LOOP
532
533 IF p_options_tbl(I).disabled_flag = 'Y' THEN
534
535 IF l_debug_level > 0 THEN
536 oe_debug_pub.add('disabled: '||p_options_tbl(I).component_code,1);
537 END IF;
538
539 Handle_Disabled_Options
540 ( p_x_option_rec => p_options_tbl(I)
541 ,p_top_model_line_id => p_top_model_line_id);
542 END IF;
543
544 IF p_options_tbl(I).operation = OE_GLOBALS.G_OPR_UPDATE
545 THEN
546
547 IF l_debug_level > 0 THEN
548 oe_debug_pub.add('GET OLD QTY FOR '|| p_OPTIONS_TBL(I).LINE_ID,1);
549 END IF;
550
551 SELECT ordered_quantity
552 INTO p_options_tbl(I).old_ordered_quantity
553 FROM oe_order_lines
554 WHERE line_id = p_options_tbl(I).line_id;
555
556 l_count := l_count + 1;
557 l_req_rec.param1 := p_top_model_line_id;
558 l_req_rec.param2 := p_options_tbl(I).component_code;
559 l_req_rec.param5 := p_options_tbl(I).ordered_quantity;
560 l_req_rec.param4 := p_options_tbl(I).old_ordered_quantity;
561 l_req_rec.param6 := p_options_tbl(I).change_reason;
562 l_req_rec.param7 := p_options_tbl(I).change_comments;
563
564 IF p_options_tbl(I).bom_item_type = 1 THEN
565 l_req_rec.param3 := OE_GLOBALS.G_ITEM_MODEL;
566 ELSIF p_options_tbl(I).bom_item_type = 2 THEN
567 l_req_rec.param3 := OE_GLOBALS.G_ITEM_CLASS;
568 ELSE
569 l_req_rec.param3 := OE_GLOBALS.G_ITEM_OPTION;
570 END IF;
571
572 IF p_options_tbl(I).disabled_flag = 'Y' THEN
573 IF l_debug_level > 0 THEN
574 oe_debug_pub.add('disabled hence setting param8', 4);
575 END IF;
576 l_req_rec.param8 := 'Y';
577 ELSE
578 -- setting cancellation flag to No for now, since user can not
579 -- give reason anyway and it will fail. Can I figure out the flag??
580 -- yes doing it 11/25/2003
581 l_req_rec.param8 := 'N';
582 END IF;
583
584 IF l_debug_level > 0 THEN
585 oe_debug_pub.add
586 ('UPDATE: '||L_REQ_REC.PARAM2||' '
587 ||L_REQ_REC.PARAM3||L_REQ_REC.PARAM5,1);
588 END IF;
589 -- 3563690
590 l_req_rec.param10 := p_options_tbl(I).ordered_item ;
591
592 p_x_updated_options_tbl(l_count) := l_req_rec;
593
594 ELSIF p_options_tbl(I).operation = OE_GLOBALS.G_OPR_DELETE
595 THEN
596 l_index := l_index + 1;
597 l_req_rec.param1 := p_top_model_line_id;
598 l_req_rec.param2 := p_options_tbl(I).component_code;
599
600 IF p_options_tbl(I).bom_item_type = 1 THEN
601 l_req_rec.param3 := OE_GLOBALS.G_ITEM_MODEL;
602 ELSIF p_options_tbl(I).bom_item_type = 2 THEN
603 l_req_rec.param3 := OE_GLOBALS.G_ITEM_CLASS;
604 ELSE
605 l_req_rec.param3 := OE_GLOBALS.G_ITEM_OPTION;
606 END IF;
607 -- 3563690
608 l_req_rec.param10 := p_options_tbl(I).ordered_item ;
609
610 IF l_debug_level > 0 THEN
611 oe_debug_pub.add( 'DELETE: '|| L_REQ_REC.PARAM2 || ' '
612 || L_REQ_REC.PARAM3 , 1 ) ;
613 END IF;
614 p_x_deleted_options_tbl(l_index) := l_req_rec;
615
616 END IF;
617
618 I := p_options_tbl.NEXT(I);
619 END LOOP;
620
621 IF l_debug_level > 0 THEN
622 oe_debug_pub.add('leaving Prepare_Cascade_Tables', 3 ) ;
623 END IF;
624
625 EXCEPTION
626 WHEN OTHERS THEN
627 IF l_debug_level > 0 THEN
628 oe_debug_pub.add('EXCEPTION IN Prepare_Cascade_Tables '|| SQLERRM ,1);
629 END IF;
630 RAISE;
631 END Prepare_Cascade_Tables;
632
633
634 /*-----------------------------------------------------------
635 FUNCTION: Find_Matching_Comp_Index
636 Used to remove duplicates from the options table.
637 ----------------------------------------------------------*/
638 FUNCTION Find_Matching_Comp_Index
639 ( p_options_tbl IN OE_Process_Options_Pvt.Selected_Options_Tbl_Type
640 ,p_comp_code IN VARCHAR2)
641 RETURN NUMBER
642 IS
643 I NUMBER;
644 --
645 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
646 --
647 BEGIN
648 IF l_debug_level > 0 THEN
649 oe_debug_pub.add( 'ENTERING IND_MATCHING_COMP_INDEX'|| P_COMP_CODE , 1 ) ;
650 END IF;
651
652 I := p_options_tbl.FIRST;
653 WHILE I is not NULL
654 LOOP
655
656 IF l_debug_level > 0 THEN
657 oe_debug_pub.add( P_OPTIONS_TBL ( I ) .COMPONENT_CODE
658 || P_OPTIONS_TBL ( I ) .OPERATION , 1 ) ;
659 END IF;
660
661 IF p_options_tbl(I).component_code = p_comp_code AND
662 p_options_tbl(I).operation <> OE_GLOBALS.G_OPR_INSERT
663 THEN
664 RETURN I;
665 END IF;
666
667
668 I := p_options_tbl.NEXT(I);
669 END LOOP;
670
671 RAISE FND_API.G_EXC_ERROR;
672 EXCEPTION
673 WHEN OTHERS THEN
674 IF l_debug_level > 0 THEN
675 oe_debug_pub.add('EXCEPTION IN FIND_MATCHING_COMP_INDEX'|| SQLERRM ,1);
676 END IF;
677 RAISE;
678 END Find_Matching_Comp_Index;
679
680 /*-----------------------------------------------------------
681 Procedure: Handle_DML
682 Currently the option window supports only create operation.
683 For any updates/deletes user will use sales order form.
684
685 To aid performance, in this procedure we first default one option
686 and use the defaulted record as the base record for all other
687 options that need to be created. This saves us from defaulting
688 all n options for 300 or so attributes in oe_order_lines.
689 We set the item dependent attributes (once that are set dependent
690 on inventory_item_id in OEXUDEPB.pls) as missing on all the
691 options so that they will get defaulted individually. Please
692 note that any future additions to OEXUDEPB.pls should be
693 added in this API also.
694
695 We call process_order and then the change columns procedure
696 which sets the configuration related links(link to line id etc)
697 on all the options.
698
699 Change Record:
700 bug fix 1894020,2184255 to support dff.
701 the operation on disabled options should always be none.
702
703 bug fix 3095496, change reason for updates and a call to
704 Is_Cancel_Or_Delete for Delete operation.
705
706 Bug 3611416
707 Send reason for CREATE operation also, will be required if there is
708 a require reason constraint for versioning during create operation.
709 ------------------------------------------------------------*/
710 Procedure Handle_DML
711 ( p_options_tbl IN OE_Process_Options_Pvt.Selected_Options_Tbl_Type
712 ,p_model_line_rec IN OUT NOCOPY OE_Order_Pub.Line_Rec_Type
713 ,p_ui_flag IN VARCHAR2
714 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
715 IS
716 -- process_order in params
717 l_control_rec OE_GLOBALS.Control_Rec_Type;
718 l_line_rec OE_ORDER_PUB.Line_Rec_Type;
719 l_line_upd_rec OE_ORDER_PUB.Line_Rec_Type;
720 l_line_del_rec OE_ORDER_PUB.Line_Rec_Type;
721 l_old_line_rec OE_ORDER_PUB.Line_Rec_Type;
722 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
723 l_model_qty NUMBER;
724 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
725 I NUMBER;
726 l_line_count NUMBER;
727 l_class_line_rec OE_ORDER_PUB.Line_Rec_Type;
728 l_class_line_count NUMBER;
729 l_class_line_tbl OE_Order_PUB.Line_Tbl_Type;
730
731 l_direct_save BOOLEAN;
732 l_profile_value VARCHAR2(1) :=
733 upper(FND_PROFILE.VALUE('ONT_CONFIG_QUICK_SAVE'));
734 l_cancellation BOOLEAN;
735
736 --
737 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
738 --
739 BEGIN
740
741 Print_Time('Entering Handle_DML');
742
743 IF p_model_line_rec.booked_flag = 'N' and l_profile_value = 'Y' AND
744 p_ui_flag = 'Y' THEN
745 IF l_debug_level > 0 THEN
746 oe_debug_pub.add( 'DIRECT SAVE ON' , 1 ) ;
747 END IF;
748 l_direct_save := TRUE;
749 ELSE
750 IF l_debug_level > 0 THEN
751 oe_debug_pub.add( 'DIRECT SAVE OFF' || L_PROFILE_VALUE , 1 ) ;
752 END IF;
753 l_direct_save := FALSE;
754 END IF;
755
756
757 --------------- prepare class line rec ----------------------------
758
759 IF l_direct_save THEN
760
761 IF l_debug_level > 0 THEN
762 oe_debug_pub.add( 'DIRECT SAVE IS ON' , 3 ) ;
763 END IF;
764
765 OE_Config_Util.Default_Child_Line
766 ( p_parent_line_rec => p_model_line_rec
767 ,p_x_child_line_rec => l_class_line_rec
768 ,p_direct_save => l_direct_save
769 ,x_return_status => l_return_status);
770
771 END IF; -- end if direct save
772
773 ----------------- class line rec done --------------------------
774
775
776 l_line_count := 0;
777 l_class_line_count := 0;
778
779 l_line_rec := OE_Order_PUB.G_MISS_LINE_REC;
780 l_line_upd_rec := l_line_rec;
781 l_line_del_rec := l_line_rec;
782
783 l_line_rec.operation := OE_GLOBALS.G_OPR_CREATE;
784 l_line_upd_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
785 l_line_del_rec.operation := OE_GLOBALS.G_OPR_DELETE;
786
787 l_line_rec.header_id := p_model_line_rec.header_id;
788 l_line_rec.top_model_line_id := p_model_line_rec.line_id;
789 l_line_rec.item_identifier_type := 'INT';
790
791
792 I := p_options_tbl.FIRST;
793 WHILE I is not null
794 LOOP
795 -- note that the operation should be INSERT and not create. CREATE is
796 -- used while calling SPC batch validation, not for process_order call.
797
798 IF l_debug_level > 0 THEN
799 oe_debug_pub.add(I|| ' OPEARION: '||P_OPTIONS_TBL(I).OPERATION ,1);
800 END IF;
801
802 IF p_options_tbl(I).operation = OE_GLOBALS.G_OPR_INSERT
803 THEN
804
805 IF l_debug_level > 0 THEN
806 oe_debug_pub.add('INSERT: '|| P_OPTIONS_TBL(I).COMPONENT_CODE ,1);
807 END IF;
808
809 IF l_direct_save AND p_options_tbl(I).bom_item_type = 2
810 THEN
811 l_class_line_rec.ordered_quantity
812 := p_options_tbl(I).ordered_quantity;
813 l_class_line_rec.order_quantity_uom
814 := p_options_tbl(I).order_quantity_uom;
815 l_class_line_rec.component_sequence_id
816 := p_options_tbl(I).component_sequence_id;
817 l_class_line_rec.component_code := p_options_tbl(I).component_code;
818 l_class_line_rec.sort_order := p_options_tbl(I).sort_order;
819 l_class_line_rec.inventory_item_id
820 := p_options_tbl(I).inventory_item_id;
821 l_class_line_rec.ordered_item := p_options_tbl(I).ordered_item;
822
823 l_class_line_rec.attribute1 := p_options_tbl(I).attribute1;
824 l_class_line_rec.attribute2 := p_options_tbl(I).attribute2;
825 l_class_line_rec.attribute3 := p_options_tbl(I).attribute3;
826 l_class_line_rec.attribute4 := p_options_tbl(I).attribute4;
827 l_class_line_rec.attribute5 := p_options_tbl(I).attribute5;
828 l_class_line_rec.attribute6 := p_options_tbl(I).attribute6;
829 l_class_line_rec.attribute7 := p_options_tbl(I).attribute7;
830 l_class_line_rec.attribute8 := p_options_tbl(I).attribute8;
831 l_class_line_rec.attribute9 := p_options_tbl(I).attribute9;
832 l_class_line_rec.attribute10 := p_options_tbl(I).attribute10;
833 l_class_line_rec.attribute11 := p_options_tbl(I).attribute11;
834 l_class_line_rec.attribute12 := p_options_tbl(I).attribute12;
835 l_class_line_rec.attribute13 := p_options_tbl(I).attribute13;
836 l_class_line_rec.attribute14 := p_options_tbl(I).attribute14;
837 l_class_line_rec.attribute15 := p_options_tbl(I).attribute15;
838 l_class_line_rec.attribute16 := p_options_tbl(I).attribute16;
839 l_class_line_rec.attribute17 := p_options_tbl(I).attribute17;
840 l_class_line_rec.attribute18 := p_options_tbl(I).attribute18;
841 l_class_line_rec.attribute19 := p_options_tbl(I).attribute19;
842 l_class_line_rec.attribute20 := p_options_tbl(I).attribute20;
843 l_class_line_rec.context := p_options_tbl(I).context;
844
845 SELECT OE_ORDER_LINES_S.NEXTVAL
846 INTO l_class_line_rec.line_id
847 FROM DUAL;
848
849 l_class_line_rec.pricing_quantity_uom
850 := l_class_line_rec.order_quantity_uom;
851 l_class_line_rec.pricing_quantity
852 := l_class_line_rec.ordered_quantity;
853
854 l_class_line_count := l_class_line_count+1;
855 l_class_line_tbl(l_class_line_count):= l_class_line_rec;
856
857 ELSE
858
859 l_line_rec.ordered_quantity := p_options_tbl(I).ordered_quantity;
860 l_line_rec.order_quantity_uom
861 := p_options_tbl(I).order_quantity_uom;
862 l_line_rec.component_sequence_id
863 := p_options_tbl(I).component_sequence_id;
864 l_line_rec.component_code := p_options_tbl(I).component_code;
865 l_line_rec.sort_order := p_options_tbl(I).sort_order;
866 l_line_rec.inventory_item_id := p_options_tbl(I).inventory_item_id;
867 l_line_rec.ordered_item := p_options_tbl(I).ordered_item;
868
869 l_line_rec.attribute1 := p_options_tbl(I).attribute1;
870 l_line_rec.attribute2 := p_options_tbl(I).attribute2;
871 l_line_rec.attribute3 := p_options_tbl(I).attribute3;
872 l_line_rec.attribute4 := p_options_tbl(I).attribute4;
873 l_line_rec.attribute5 := p_options_tbl(I).attribute5;
874 l_line_rec.attribute6 := p_options_tbl(I).attribute6;
875 l_line_rec.attribute7 := p_options_tbl(I).attribute7;
876 l_line_rec.attribute8 := p_options_tbl(I).attribute8;
877 l_line_rec.attribute9 := p_options_tbl(I).attribute9;
878 l_line_rec.attribute10 := p_options_tbl(I).attribute10;
879 l_line_rec.attribute11 := p_options_tbl(I).attribute11;
880 l_line_rec.attribute12 := p_options_tbl(I).attribute12;
881 l_line_rec.attribute13 := p_options_tbl(I).attribute13;
882 l_line_rec.attribute14 := p_options_tbl(I).attribute14;
883 l_line_rec.attribute15 := p_options_tbl(I).attribute15;
884 l_line_rec.attribute16 := p_options_tbl(I).attribute16;
885 l_line_rec.attribute17 := p_options_tbl(I).attribute17;
886 l_line_rec.attribute18 := p_options_tbl(I).attribute18;
887 l_line_rec.attribute19 := p_options_tbl(I).attribute19;
888 l_line_rec.attribute20 := p_options_tbl(I).attribute20;
889 l_line_rec.context := p_options_tbl(I).context;
890 l_line_rec.change_reason := 'SYSTEM';
891
892 IF p_options_tbl(I).bom_item_type = 2 THEN
893 l_line_rec.item_type_code := OE_GLOBALS.G_ITEM_CLASS;
894 ELSE
895 l_line_rec.item_type_code := null;
896 END IF;
897
898 l_line_count := l_line_count + 1;
899 l_line_tbl(l_line_count) := l_line_rec;
900
901 END IF;
902
903 ELSIF p_options_tbl(I).operation = OE_GLOBALS.G_OPR_UPDATE THEN
904
905 l_line_upd_rec.line_id := p_options_tbl(I).line_id;
906 l_line_upd_rec.component_code := p_options_tbl(I).component_code;
907 l_line_upd_rec.ordered_quantity := p_options_tbl(I).ordered_quantity;
908 l_line_upd_rec.change_reason := p_options_tbl(I).change_reason;
909 l_line_upd_rec.change_comments := p_options_tbl(I).change_comments;
910
911 l_line_upd_rec.attribute1 := p_options_tbl(I).attribute1;
912 l_line_upd_rec.attribute2 := p_options_tbl(I).attribute2;
913 l_line_upd_rec.attribute3 := p_options_tbl(I).attribute3;
914 l_line_upd_rec.attribute4 := p_options_tbl(I).attribute4;
915 l_line_upd_rec.attribute5 := p_options_tbl(I).attribute5;
916 l_line_upd_rec.attribute6 := p_options_tbl(I).attribute6;
917 l_line_upd_rec.attribute7 := p_options_tbl(I).attribute7;
918 l_line_upd_rec.attribute8 := p_options_tbl(I).attribute8;
919 l_line_upd_rec.attribute9 := p_options_tbl(I).attribute9;
920 l_line_upd_rec.attribute10 := p_options_tbl(I).attribute10;
921 l_line_upd_rec.attribute11 := p_options_tbl(I).attribute11;
922 l_line_upd_rec.attribute12 := p_options_tbl(I).attribute12;
923 l_line_upd_rec.attribute13 := p_options_tbl(I).attribute13;
924 l_line_upd_rec.attribute14 := p_options_tbl(I).attribute14;
925 l_line_upd_rec.attribute15 := p_options_tbl(I).attribute15;
926 l_line_upd_rec.attribute16 := p_options_tbl(I).attribute16;
927 l_line_upd_rec.attribute17 := p_options_tbl(I).attribute17;
928 l_line_upd_rec.attribute18 := p_options_tbl(I).attribute18;
929 l_line_upd_rec.attribute19 := p_options_tbl(I).attribute19;
930 l_line_upd_rec.attribute20 := p_options_tbl(I).attribute20;
931 l_line_upd_rec.context := p_options_tbl(I).context;
932
933 IF p_ui_flag = 'Y' THEN
934 l_line_upd_rec.change_reason := 'CONFIGURATOR';
935 l_line_upd_rec.change_comments:= 'Changes in Options Window';
936 END IF;
937
938 l_line_count := l_line_count + 1;
939 l_line_tbl(l_line_count) := l_line_upd_rec;
940
941 IF l_debug_level > 0 THEN
942 oe_debug_pub.add('UPDATE LINE_ID: '|| P_OPTIONS_TBL(I).LINE_ID ,1);
943 END IF;
944
945 ELSIF p_options_tbl(I).operation = OE_GLOBALS.G_OPR_DELETE THEN
946
947 l_line_del_rec.line_id := p_options_tbl(I).line_id;
948 l_line_del_rec.component_code := p_options_tbl(I).component_code;
949 l_line_count := l_line_count + 1;
950
951 IF p_ui_flag = 'Y' AND
952 p_options_tbl(I).disabled_flag = 'N' THEN
953
954 IF l_debug_level > 0 THEN
955 oe_debug_pub.add('calling Is_Cancel_OR_Delete '
956 || l_line_del_rec.line_id, 3);
957 END IF;
958
959 OE_Config_Pvt.Is_Cancel_OR_Delete
960 ( p_line_id => l_line_del_rec.line_id
961 ,p_change_reason => 'CONFIGURATOR'
962 ,p_change_comments => 'Changes in Options Window'
963 ,x_cancellation => l_cancellation
964 ,x_line_rec => l_line_del_rec);
965
966 oe_debug_pub.add('operation '|| l_line_del_rec.operation, 1);
967 END IF;
968
969 l_line_tbl(l_line_count) := l_line_del_rec;
970
971 IF l_debug_level > 0 THEN
972 oe_debug_pub.add('DELETE LINE_ID: '|| P_OPTIONS_TBL(I).LINE_ID , 1);
973 END IF;
974
975 ELSE
976 IF l_debug_level > 0 THEN
977 oe_debug_pub.add
978 ('NO ACTION OPERATION '|| p_options_tbl(I).disabled_flag, 1 ) ;
979 END IF;
980
981 END IF; -- operation = create
982
983 I := p_options_tbl.NEXT(I);
984 END LOOP;
985
986 IF l_debug_level > 0 THEN
987 oe_debug_pub.add
988 ( 'OUT OF LOOP '|| L_LINE_COUNT || ' ' || L_CLASS_LINE_COUNT , 1 ) ;
989 END IF;
990
991
992 --even if line_count = 0, we need to call, for change columns.
993
994 IF p_ui_flag = 'Y' THEN
995 l_control_rec.process := TRUE;
996 ELSE
997 l_control_rec.process := FALSE;
998 END IF;
999
1000 oe_config_pvt.Call_Process_Order
1001 ( p_line_tbl => l_line_tbl
1002 ,p_class_line_tbl => l_class_line_tbl
1003 ,p_control_rec => l_control_rec
1004 ,p_ui_flag => p_ui_flag
1005 ,p_top_model_line_id => p_model_line_rec.top_model_line_id
1006 ,p_update_columns => TRUE
1007 ,x_return_status => l_return_status);
1008
1009 x_return_status := l_return_status;
1010
1011 Print_Time('Leaving Handle_DML '|| x_return_status);
1012
1013 EXCEPTION
1014 WHEN OTHERS THEN
1015 IF l_debug_level > 0 THEN
1016 oe_debug_pub.add( 'EXCEPTION IN HANDLE_DML: '|| SQLERRM , 1 ) ;
1017 END IF;
1018 RAISE;
1019 END Handle_DML;
1020
1021
1022 /*----------------------------------------------------------------------
1023 PROCEDURE: Handle_Disabled_Options
1024
1025 sets correct operation on disabled child line so that
1026 system can eihter delete or cancel them also populates
1027 message back to user to indicate the same.
1028 -----------------------------------------------------------------------*/
1029 PROCEDURE Handle_Disabled_Options
1030 ( p_x_option_rec IN OUT NOCOPY OE_Process_Options_Pvt.SELECTED_OPTIONS_REC
1031 ,p_top_model_line_id IN NUMBER)
1032 IS
1033 l_line_rec OE_ORDER_PUB.Line_Rec_Type;
1034 l_old_line_rec OE_ORDER_PUB.Line_Rec_Type;
1035 I NUMBER;
1036 l_sec_result NUMBER;
1037 l_return_status VARCHAR2(1);
1038 l_line_count NUMBER;
1039 l_dummy VARCHAR2(30);
1040 l_cancellation BOOLEAN;
1041 --
1042 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1043 --
1044 BEGIN
1045
1046 FND_MESSAGE.Set_Name('ONT', 'ONT_CONFIG_DISABLED_OPTION');
1047 FND_MESSAGE.Set_Token('OPTION', nvl(p_x_option_rec.ordered_item,
1048 p_x_option_rec.inventory_item_id));
1049
1050 SELECT ordered_item
1051 INTO l_dummy
1052 FROM oe_order_lines
1053 WHERE line_id = p_top_model_line_id;
1054
1055 FND_MESSAGE.Set_Token('MODEL', nvl(l_dummy, '-'));
1056
1057 SELECT line_number || '.' || shipment_number || '.' ||
1058 option_number || '.' || component_number || '.' ||
1059 service_number
1060 INTO l_dummy
1061 FROM oe_order_lines
1062 WHERE line_id = p_x_option_rec.line_id;
1063
1064 FND_MESSAGE.Set_Token('LINE_NUM', RTRIM(l_dummy, '.'));
1065 OE_Msg_Pub.Add;
1066
1067 OE_Config_Pvt.Is_Cancel_OR_Delete
1068 ( p_line_id => p_x_option_rec.line_id
1069 ,p_change_reason => 'SYSTEM'
1070 ,p_change_comments => 'DISABLED'
1071 ,x_cancellation => l_cancellation
1072 ,x_line_rec => l_line_rec);
1073
1074 IF l_cancellation THEN
1075 IF l_debug_level > 0 THEN
1076 oe_debug_pub.add('do cancellation hence update with 0', 3 );
1077 END IF;
1078
1079 p_x_option_rec.ordered_quantity := 0;
1080 p_x_option_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
1081
1082 p_x_option_rec.change_reason := 'SYSTEM';
1083 p_x_option_rec.change_comments := 'DISABLED';
1084
1085 ELSE
1086 p_x_option_rec.operation := OE_GLOBALS.G_OPR_DELETE;
1087
1088 IF l_debug_level > 0 THEN
1089 oe_debug_pub.add('no cancellation, delete ok ', 3 ) ;
1090 END IF;
1091 END IF;
1092
1093 IF l_debug_level > 0 THEN
1094 oe_debug_pub.add('option operation '|| p_x_option_rec.operation, 3);
1095 END IF;
1096
1097 EXCEPTION
1098 WHEN OTHERS THEN
1099 IF l_debug_level > 0 THEN
1100 oe_debug_pub.add('EXCEPTION Handle_Disabled_Options: '||SQLERRM,1);
1101 END IF;
1102 RAISE;
1103 END Handle_Disabled_Options;
1104
1105 /*-----------------------------------------------------------------------
1106 PROCEDURE: Fill_In_Classes
1107 put every item in p_x_options_tbl in a pl/sql table A
1108 also put all options in databse in table A.
1109 see if the striped compo code is already present in the new table
1110 if not add.
1111 p_x_options_tbl is the table is of lines we want to create.
1112 operation of INSERT indicates that the record is to created in DB.
1113 operation of CREATE means the record is created however the
1114 transaction is yet not commited(mainly in case of delyed requests).
1115 ------------------------------------------------------------------------*/
1116
1117 Procedure Fill_In_Classes
1118 ( p_top_model_line_id IN NUMBER
1119 ,p_model_component IN VARCHAR2
1120 ,p_model_quantity IN NUMBER
1121 ,p_top_bill_sequence_id IN NUMBER
1122 ,p_effective_date IN DATE
1123 ,p_ui_flag IN VARCHAR2
1124 ,p_x_options_tbl IN OUT NOCOPY OE_Process_Options_Pvt.SELECTED_OPTIONS_TBL_TYPE
1125 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1126 IS
1127 I NUMBER;
1128 J NUMBER;
1129 l_count NUMBER;
1130 l_options_tbl_index NUMBER;
1131 l_index_before_fill NUMBER;
1132 l_in_count NUMBER;
1133 l_component VARCHAR2(2000);
1134 l_orig_component VARCHAR2(2000);
1135 l_result BOOLEAN;
1136 l_validation_org NUMBER;
1137 l_last NUMBER;
1138 --
1139 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1140 --
1141 BEGIN
1142
1143 Print_Time('Entering Fill_In_Classes start time');
1144
1145 l_in_count := p_x_options_tbl.COUNT;
1146
1147 -- we will use l_options_tbl_count for p_x_options_tbl because
1148 -- there can be gaps in the tbl, can not use l_count, bug fix.
1149
1150 l_options_tbl_index := p_x_options_tbl.LAST;
1151 l_index_before_fill := l_options_tbl_index; -- used later
1152
1153 I := p_x_options_tbl.FIRST;
1154
1155 IF l_debug_level > 0 THEN
1156 oe_debug_pub.add( 'I: '|| I , 1 ) ;
1157 END IF;
1158
1159 WHILE I is not null
1160 LOOP
1161 IF nvl(p_x_options_tbl(I).operation, OE_GLOBALS.G_OPR_NONE)
1162 = OE_GLOBALS.G_OPR_INSERT OR
1163 nvl(p_x_options_tbl(I).operation, OE_GLOBALS.G_OPR_NONE)
1164 = OE_GLOBALS.G_OPR_CREATE
1165 THEN
1166 J := 2;
1167 l_orig_component := p_x_options_tbl(I).component_code;
1168 l_component :=
1169 SUBSTR(l_orig_component, 1, (INSTR(l_orig_component, '-', 1, J) -1));
1170
1171
1172 IF l_debug_level > 0 THEN
1173 oe_debug_pub.add( I || 'HERE COMPONENT: '|| L_COMPONENT , 1 ) ;
1174 END IF;
1175
1176 WHILE l_component is NOT NULL
1177 LOOP
1178
1179 IF l_debug_level > 0 THEN
1180 oe_debug_pub.add( 'INNER LOOP COMPONENT: '|| L_COMPONENT , 1 ) ;
1181 END IF;
1182 component_exists
1183 ( p_component => l_component
1184 ,p_options_tbl => p_x_options_tbl
1185 ,x_result => l_result);
1186
1187 IF NOT (l_result) THEN
1188 IF l_debug_level > 0 THEN
1189 oe_debug_pub.add( 'COMOPNENT NOT THERE , SO ADD '||L_COMPONENT,1);
1190 END IF;
1191 l_count := l_count + 1;
1192 l_options_tbl_index := l_options_tbl_index + 1;
1193 p_x_options_tbl(l_options_tbl_index).component_code := l_component;
1194 p_x_options_tbl(l_options_tbl_index).operation := OE_GLOBALS.G_OPR_INSERT;
1195 ELSE
1196 IF l_debug_level > 0 THEN
1197 oe_debug_pub.add( 'COMPONENT ALREADY PRESENT' , 1 ) ;
1198 END IF;
1199 END IF;
1200
1201 J := J + 1;
1202 l_component :=
1203 SUBSTR(l_orig_component, 1, (INSTR(l_orig_component, '-', 1, J) -1));
1204 END LOOP;
1205 END IF;
1206 I := p_x_options_tbl.NEXT(I);
1207 END LOOP;
1208
1209 IF l_debug_level > 0 THEN
1210 oe_debug_pub.add('COUNT IN OPTIONS TABLE: '|| P_X_OPTIONS_TBL.COUNT,1);
1211 oe_debug_pub.add( 'COUNT SENT IN: '|| L_IN_COUNT , 1 ) ;
1212 END IF;
1213
1214 IF p_x_options_tbl.count = l_in_count THEN
1215 IF l_debug_level > 0 THEN
1216 oe_debug_pub.add( 'NOTHING TO FILL' , 1 ) ;
1217 END IF;
1218 RETURN;
1219 END IF;
1220
1221 l_validation_org := OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID');
1222
1223 I := l_index_before_fill + 1;
1224 WHILE I is not null
1225 LOOP
1226
1227 l_component := p_x_options_tbl(I).component_code;
1228
1229 IF l_debug_level > 0 THEN
1230 oe_debug_pub.add
1231 ( 'COMP COMPLETING: '|| L_COMPONENT ||' ' || L_VALIDATION_ORG , 1 ) ;
1232 END IF;
1233
1234 SELECT component_sequence_id, component_item_id, sort_order,
1235 primary_uom_code, EXTENDED_QUANTITY * p_model_quantity,
1236 DECODE(bom_item_type, 1, 2, 2, 2, 4, 4)
1237 INTO p_x_options_tbl(I).component_sequence_id,
1238 p_x_options_tbl(I).inventory_item_id,
1239 p_x_options_tbl(I).sort_order,
1240 p_x_options_tbl(I).order_quantity_uom,
1241 p_x_options_tbl(I).ordered_quantity,
1242 p_x_options_tbl(I).bom_item_type
1243 FROM bom_explosions be
1244 WHERE be.explosion_type = OE_Config_Util.OE_BMX_OPTION_COMPS
1245 AND be.top_bill_sequence_id = p_top_bill_sequence_id
1246 AND be.plan_level > 0
1247 AND be.effectivity_date <= p_effective_date
1248 AND be.disable_date > p_effective_date
1249 AND be.component_code = p_x_options_tbl(I).component_code
1250 AND rownum = 1;
1251
1252 BEGIN
1253 SELECT concatenated_segments
1254 INTO p_x_options_tbl(I).ordered_item
1255 FROM MTL_SYSTEM_ITEMS_KFV
1256 WHERE inventory_item_id = p_x_options_tbl(I).inventory_item_id
1257 AND organization_id = l_validation_org;
1258 EXCEPTION
1259 WHEN NO_DATA_FOUND THEN
1260 RAISE FND_API.G_EXC_ERROR;
1261 END;
1262
1263 I := p_x_options_tbl.NEXT(I);
1264 END LOOP;
1265
1266 Print_Time('Fill_In_Classes end time');
1267
1268 EXCEPTION
1269 WHEN OTHERS THEN
1270 IF l_debug_level > 0 THEN
1271 oe_debug_pub.add( 'EXCEPTION IN FILL_IN_CLASSES' || SQLERRM , 1 ) ;
1272 END IF;
1273 RAISE;
1274 END Fill_In_Classes;
1275
1276
1277 /*-----------------------------------------------------------------------
1278 PROCEDURE: component_exists
1279 This procedure loops through the options table and finds out if the item
1280 with component_code = p_component exist in the p_options_table.
1281 If the operation on the matching component record is DELETE, this function
1282 will return a value of false.
1283 ------------------------------------------------------------------------*/
1284
1285 PROCEDURE component_exists
1286 ( p_component IN VARCHAR2
1287 ,p_options_tbl IN OE_Process_Options_Pvt.Selected_Options_Tbl_Type
1288 ,x_result OUT NOCOPY /* file.sql.39 change */ BOOLEAN)
1289 IS
1290 I NUMBER;
1291 --
1292 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1293 --
1294 BEGIN
1295
1296 I := p_options_tbl.FIRST;
1297
1298 WHILE I is not null
1299 LOOP
1300 IF l_debug_level > 0 THEN
1301 oe_debug_pub.add(I || ' COMPARING TO COMPONENT: '
1302 || P_OPTIONS_TBL ( I ) .COMPONENT_CODE , 1 ) ;
1303 END IF;
1304
1305 IF p_options_tbl(I).component_code = p_component AND
1306 nvl(p_options_tbl(I).operation, OE_GLOBALS.G_OPR_NONE) <>
1307 OE_GLOBALS.G_OPR_DELETE THEN
1308 IF l_debug_level > 0 THEN
1309 oe_debug_pub.add( 'COMPONENT FOUND' , 1 ) ;
1310 END IF;
1311 x_result := true;
1312 RETURN;
1313 END IF;
1314
1315 I := p_options_tbl.NEXT(I);
1316 END LOOP;
1317
1318 x_result := false;
1319
1320 IF l_debug_level > 0 THEN
1321 oe_debug_pub.add( 'LEAVING COMPONENT_NOT_EXIST' , 1 ) ;
1322 END IF;
1323 EXCEPTION
1324 WHEN OTHERS THEN
1325 IF l_debug_level > 0 THEN
1326 oe_debug_pub.add( 'EXCEPTION IN COMPONENT_NOT_EXIST' || SQLERRM , 1 ) ;
1327 END IF;
1328 RAISE;
1329 END component_exists;
1330
1331
1332 /*---------------------------------------------------------------------------
1333 PROCEDURE: Check_Duplicate_Components
1334 This procedure makes sure that every component in the configuration
1335 appears only once.
1336 -------------------------------------------------------------------------*/
1337 PROCEDURE Check_Duplicate_Components
1338 ( p_options_tbl IN OE_Process_Options_Pvt.Selected_Options_Tbl_Type
1339 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1340 )
1341 IS
1342 l_outer_index NUMBER;
1343 l_inner_index NUMBER;
1344 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
1345 --
1346 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1347 --
1348 BEGIN
1349 IF l_debug_level > 0 THEN
1350 oe_debug_pub.add( 'ENTERING CHECK_DUPLICATE_COMPONENTS' , 1 ) ;
1351 oe_debug_pub.add( 'COUNT: '|| P_OPTIONS_TBL.COUNT , 1 ) ;
1352 END IF;
1353
1354 l_outer_index := p_options_tbl.FIRST;
1355 WHILE l_outer_index is not NULL
1356 LOOP
1357
1358 IF l_debug_level > 0 THEN
1359 oe_debug_pub.add(p_OPTIONS_TBL(l_outer_index).operation
1360 || p_OPTIONS_TBL(l_outer_index).component_code,3);
1361 END IF;
1362
1363 IF p_options_tbl(l_outer_index).operation =
1364 OE_GLOBALS.G_OPR_CREATE OR
1365 p_options_tbl(l_outer_index).operation =
1366 OE_GLOBALS.G_OPR_INSERT
1367 THEN
1368 IF l_debug_level > 0 THEN
1369 oe_debug_pub.add( L_OUTER_INDEX || ' CHECK DUPL: '
1370 ||P_OPTIONS_TBL ( L_OUTER_INDEX ) .COMPONENT_CODE , 1 ) ;
1371 oe_debug_pub.add( 'SORT ORDER: '
1372 || P_OPTIONS_TBL ( L_OUTER_INDEX ) .SORT_ORDER , 1 ) ;
1373 END IF;
1374
1375 l_inner_index := p_options_tbl.FIRST;
1376
1377 WHILE l_inner_index is not NULL
1378 LOOP
1379
1380 IF l_debug_level > 0 THEN
1381 oe_debug_pub.add(L_INNER_INDEX
1382 || P_OPTIONS_TBL ( L_INNER_INDEX ) .COMPONENT_CODE , 1 ) ;
1383 END IF;
1384
1385 IF l_inner_index <> l_outer_index THEN
1386 IF p_options_tbl(l_inner_index).component_code =
1387 p_options_tbl(l_outer_index).component_code
1388 THEN
1389
1390 IF l_debug_level > 0 THEN
1391 oe_debug_pub.add( 'DUPLICATE EXIST' , 1 ) ;
1392 END IF;
1393
1394 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1395
1396 FND_MESSAGE.Set_Name('ONT', 'OE_CONFIG_DUPLICATE_COMPONENT');
1397 FND_MESSAGE.Set_Token
1398 ('ITEM', p_options_tbl(l_outer_index).ordered_item);
1399 OE_Msg_Pub.Add;
1400 END IF;
1401 END IF;
1402 l_inner_index := p_options_tbl.NEXT(l_inner_index);
1403 END LOOP;
1404 END IF;
1405 l_outer_index := p_options_tbl.NEXT(l_outer_index);
1406 END LOOP;
1407
1408 x_return_status := l_return_status;
1409 IF l_debug_level > 0 THEN
1410 oe_debug_pub.add( 'LEAVING CHECK_DUPLICATE_COMPONENTS' , 1 ) ;
1411 END IF;
1412
1413 EXCEPTION
1414 WHEN OTHERS THEN
1415 IF l_debug_level > 0 THEN
1416 oe_debug_pub.add('EXCEPTION IN CHECK_DUPLICATE_OPTIONS'|| SQLERRM,1);
1417 END IF;
1418 RAISE;
1419 END Check_Duplicate_Components;
1420
1421
1422 /*---------------------------------------------------------------------------
1423 FUNCTION: Use_Configurator
1424
1425 This Function returns true if,
1426 1) configurator is Installed
1427 2) the profile options ONT_USE_CONFIGURATOR is set to 'YES'
1428 Else it returns false.
1429
1430 If the function returns false OM will open options window
1431 to enter options for a model.
1432 Also it will use BOM based validation for configuration validation
1433 1) for order import
1434 2) any modification to configuration through UI.
1435
1436 If the function returns true, Product configurator will be used
1437 to enter options and for batch validation.
1438
1439 Change Record:
1440 bug 1701377 : to use globals for installation statuses.
1441
1442 bug 1922990: For existing customers who were using configurator
1443 but did not have the prodcut configurator installed,
1444 we will use the profile_option BOM: Configurator url.
1445 If the configurator is not installed but this profile
1446 option is set, we will allow customers to use configurator.
1447
1448 ----------------------------------------------------------------------------*/
1449 FUNCTION Use_Configurator
1450 RETURN BOOLEAN
1451 IS
1452 l_status VARCHAR2(1) := NULL;
1453 l_result BOOLEAN;
1454 l_industry VARCHAR2(30) := NULL;
1455 l_configurator_product_id NUMBER := 708;
1456 l_profile_value VARCHAR2(240);
1457
1458 --
1459 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1460 --
1461 BEGIN
1462 IF l_debug_level > 0 THEN
1463 oe_debug_pub.add( 'ENTERING USE_CONFIGURATOR' , 1 ) ;
1464 END IF;
1465
1466 IF OE_GLOBALS.G_CONFIGURATOR_INSTALLED IS NULL THEN
1467 OE_GLOBALS.G_CONFIGURATOR_INSTALLED
1468 := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(l_configurator_product_id);
1469 END IF;
1470
1471 l_profile_value := upper(FND_PROFILE.VALUE('ONT_USE_CONFIGURATOR'));
1472
1473 IF nvl(l_profile_value, 'Y') = 'Y' THEN
1474 IF OE_GLOBALS.G_CONFIGURATOR_INSTALLED = 'Y' THEN
1475 IF l_debug_level > 0 THEN
1476 oe_debug_pub.add('CONFIGURATOR IS INSTALLED PROFILE SET TO YES',1);
1477 END IF;
1478 RETURN true;
1479 ELSE
1480 l_profile_value := FND_PROFILE.VALUE('CZ_UIMGR_URL');
1481
1482 IF l_profile_value is NULL THEN
1483
1484 IF l_debug_level > 0 THEN
1485 oe_debug_pub.add( 'URL VALUE IS NULL , USE OPTIONS WINDOW' , 1 ) ;
1486 END IF;
1487
1488 RETURN false;
1489 ELSE
1490
1491 IF l_debug_level > 0 THEN
1492 oe_debug_pub.add( 'URL PROFILE VALUE '|| L_PROFILE_VALUE , 1 ) ;
1493 END IF;
1494
1495 RETURN true;
1496 END IF;
1497 END IF;
1498 ELSE -- use_configurator profile is set to 'N'
1499 IF l_debug_level > 0 THEN
1500 oe_debug_pub.add( 'USE OPTIONS WINDOW' , 1 ) ;
1501 END IF;
1502 RETURN false;
1503 END IF;
1504
1505 IF l_debug_level > 0 THEN
1506 oe_debug_pub.add( 'LEAVING USE_CONFIGURATOR' , 1 ) ;
1507 END IF;
1508
1509 EXCEPTION
1510 WHEN OTHERS THEN
1511 IF l_debug_level > 0 THEN
1512 oe_debug_pub.add( 'USE_CONFIGURATOR EXCEPTION'|| SQLERRM , 1 ) ;
1513 END IF;
1514 RETURN false;
1515 END Use_Configurator;
1516
1517
1518 /*------------------------------------------------------------------------
1519 PROCEDURE: Get_Options_From_DB
1520 only new options in case of cz and all options in case bom
1521 based validation.
1522 This procedure does not return closed lines on purpose.
1523 The bom based validation is smart enough to see
1524 if the ordered quantity is 0 and not to the check.
1525
1526 Change Record:
1527 added additional flex attributes for bug 2184255.
1528
1529 added 2 new parameters,
1530 p_caller : CONFIGURATOR OR OPTIONS WINDOW
1531 p_query_criteria : 1 - all, disabled flag not set
1532 2 - enabled only,
1533 3 - diabled only
1534 4 - all with disabled flag set
1535 -------------------------------------------------------------------------*/
1536
1537 PROCEDURE Get_Options_From_DB
1538 ( p_top_model_line_id IN NUMBER
1539 ,p_get_model_line IN BOOLEAN := FALSE
1540 ,p_caller IN VARCHAR2:= ''
1541 ,p_query_criteria IN NUMBER := 1
1542 ,x_disabled_options OUT NOCOPY VARCHAR2
1543 ,x_options_tbl OUT NOCOPY
1544 OE_Process_Options_Pvt.SELECTED_OPTIONS_TBL_TYPE)
1545 IS
1546 CURSOR Get_Options
1547 IS
1548 SELECT component_code , ordered_quantity, inventory_item_id,
1549 component_sequence_id, sort_order, order_quantity_uom,
1550 DECODE(item_type_code, 'MODEL', 1, 'CLASS', 2, 4) bom_item_type,
1551 ordered_item, configuration_id, config_header_id, line_id,
1552 attribute1, attribute2, attribute3, attribute4, attribute5,
1553 attribute6, attribute7, attribute8, attribute9, attribute10,
1554 attribute11, attribute12, attribute13, attribute14, attribute15,
1555 attribute16, attribute17, attribute18, attribute19, attribute20,
1556 context
1557 FROM oe_order_lines
1558 WHERE top_model_line_id = p_top_model_line_id
1559 AND open_flag = 'Y'
1560 AND nvl(config_header_id, -1) = -1
1561 AND (item_type_code = OE_GLOBALS.G_ITEM_MODEL
1562 OR item_type_code = OE_GLOBALS.G_ITEM_OPTION
1563 OR item_type_code = OE_GLOBALS.G_ITEM_CLASS
1564 OR item_type_code = OE_GLOBALS.G_ITEM_KIT);
1565
1566 I NUMBER;
1567 l_config_effective_date DATE;
1568 l_frozen_model_bill VARCHAR2(1) := 'Y';
1569 l_old_behavior VARCHAR2(1);
1570 l_validation_org NUMBER :=
1571 OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID');
1572 l_stdcompflag VARCHAR2(10)
1573 := OE_Config_Util.OE_BMX_OPTION_COMPS;
1574 l_top_item_id NUMBER;
1575 l_op_qty NUMBER;
1576 l_top_bill_sequence_id NUMBER;
1577 l_disable_code NUMBER := 1;
1578 l_msg_count NUMBER;
1579 l_msg_data VARCHAR2(2000);
1580 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
1581
1582 --
1583 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1584 --
1585 BEGIN
1586 IF l_debug_level > 0 THEN
1587 oe_debug_pub.add(p_query_criteria || '-ENTERING GET_OPTIONS_FROM_DB'
1588 || P_TOP_MODEL_LINE_ID,1);
1589 END IF;
1590
1591 x_disabled_options := 'N';
1592
1593 IF p_caller is NOT NULL AND
1594 p_query_criteria > 1 AND
1595 OE_CODE_CONTROL.Get_Code_Release_Level >= '110510' THEN
1596
1597 OE_Config_Util.Get_Config_Effective_Date
1598 ( p_model_line_id => p_top_model_line_id
1599 ,x_old_behavior => l_old_behavior
1600 ,x_config_effective_date => l_config_effective_date
1601 ,x_frozen_model_bill => l_frozen_model_bill);
1602
1603
1604 IF l_frozen_model_bill = 'N' THEN
1605 SELECT inventory_item_id, component_sequence_id
1606 INTO l_top_item_id, l_top_bill_sequence_id
1607 FROM oe_order_lines
1608 WHERE line_id = p_top_model_line_id;
1609
1610 OE_CONFIG_UTIL.Explode
1611 ( p_validation_org => OE_SYS_PARAMETERS.VALUE
1612 ('MASTER_ORGANIZATION_ID')
1613 , p_stdcompflag => l_stdcompflag
1614 , p_top_item_id => l_top_item_id
1615 , p_revdate => l_config_effective_date
1616 , x_msg_data => l_msg_data
1617 , x_error_code => l_disable_code
1618 , x_return_status => l_return_status);
1619
1620 Handle_Ret_Status(p_return_status => l_return_status);
1621 ELSE
1622 IF p_query_criteria = 3 THEN
1623
1624 IF l_debug_level > 0 THEN
1625 oe_debug_pub.add('nothing can be disabled ', 1);
1626 END IF;
1627 RETURN;
1628 END IF;
1629 END IF;
1630 END IF;
1631
1632 I := 0;
1633 FOR opt_rec in Get_Options
1634 LOOP
1635
1636 IF l_frozen_model_bill = 'N' THEN
1637 BEGIN
1638
1639 SELECT 1
1640 INTO l_disable_code
1641 FROM bom_explosions
1642 WHERE component_item_id = opt_rec.inventory_item_id
1643 AND explosion_type = Oe_Config_Util.OE_BMX_OPTION_COMPS
1644 AND top_bill_sequence_id = l_top_bill_sequence_id
1645 AND effectivity_date <= l_config_effective_date
1646 AND disable_date > l_config_effective_date
1647 AND organization_id = OE_SYS_PARAMETERS.VALUE
1648 ('MASTER_ORGANIZATION_ID')
1649 AND component_code = opt_rec.component_code;
1650
1651 IF p_query_criteria in (1,2,4) THEN
1652 l_disable_code := 1; -- error code of 1 means not disabled
1653 ELSE
1654 IF l_debug_level > 0 THEN
1655 oe_debug_pub.add('do not enter loop', 1);
1656 END IF;
1657 l_disable_code := 0; -- error code of 0, do not enter loop
1658 END IF;
1659
1660 EXCEPTION
1661 WHEN NO_DATA_FOUND THEN
1662 IF l_debug_level > 0 THEN
1663 oe_debug_pub.add('no data, must be disabled', 1);
1664 END IF;
1665
1666 IF p_query_criteria in (1,3,4) THEN
1667 l_disable_code := 2; -- error code of 2 means disabled
1668 ELSE -- send enabled only
1669 l_disable_code := 0;
1670 END IF;
1671
1672 WHEN TOO_MANY_ROWS THEN
1673 IF l_debug_level > 0 THEN
1674 oe_debug_pub.add('too many rows', 1);
1675 END IF;
1676 RAISE;
1677
1678 WHEN OTHERS THEN
1679 IF l_debug_level > 0 THEN
1680 oe_debug_pub.add('bom item select error '|| sqlerrm, 1);
1681 END IF;
1682 RAISE;
1683 END;
1684 END IF; -- frozen or not
1685
1686 IF l_debug_level > 0 THEN
1687 oe_debug_pub.add('disable_code '|| l_disable_code, 3);
1688 END IF;
1689
1690 IF ((opt_rec.bom_item_type = 1 AND p_get_model_line) OR
1691 opt_rec.bom_item_type <> 1 ) AND
1692 l_disable_code > 0 THEN
1693
1694
1695 IF l_debug_level > 0 THEN
1696 oe_debug_pub.add('SAVED OPTION FROM DB '||OPT_REC.COMPONENT_CODE,3);
1697 END IF;
1698
1699 IF p_caller = 'OPTIONS WINDOW UI' THEN
1700 I := opt_rec.line_id;
1701 ELSE
1702 I := I + 1;
1703 END IF;
1704
1705 x_options_tbl(I).component_code := opt_rec.component_code;
1706 x_options_tbl(I).ordered_quantity := opt_rec.ordered_quantity;
1707 x_options_tbl(I).inventory_item_id := opt_rec.inventory_item_id;
1708 x_options_tbl(I).component_sequence_id := opt_rec.component_sequence_id;
1709 x_options_tbl(I).sort_order := opt_rec.sort_order;
1710 x_options_tbl(I).order_quantity_uom := opt_rec.order_quantity_uom;
1711 x_options_tbl(I).bom_item_type := opt_rec.bom_item_type;
1712 x_options_tbl(I).ordered_item := opt_rec.ordered_item;
1713 x_options_tbl(I).configuration_id := opt_rec.configuration_id;
1714
1715
1716 IF l_disable_code = 2 THEN
1717 x_options_tbl(I).disabled_flag := 'Y';
1718 x_disabled_options := 'Y';
1719
1720 IF l_debug_level > 0 THEN
1721 oe_debug_pub.add('disabled ****', 1);
1722 END IF;
1723 ELSE
1724 x_options_tbl(I).disabled_flag := 'N';
1725 END IF;
1726
1727 x_options_tbl(I).attribute1 := opt_rec.attribute1;
1728 x_options_tbl(I).attribute2 := opt_rec.attribute2;
1729 x_options_tbl(I).attribute3 := opt_rec.attribute3;
1730 x_options_tbl(I).attribute4 := opt_rec.attribute4;
1731 x_options_tbl(I).attribute5 := opt_rec.attribute5;
1732 x_options_tbl(I).attribute6 := opt_rec.attribute6;
1733 x_options_tbl(I).attribute7 := opt_rec.attribute7;
1734 x_options_tbl(I).attribute8 := opt_rec.attribute8;
1735 x_options_tbl(I).attribute9 := opt_rec.attribute9;
1736 x_options_tbl(I).attribute10 := opt_rec.attribute10;
1737 x_options_tbl(I).attribute11 := opt_rec.attribute11;
1738 x_options_tbl(I).attribute12 := opt_rec.attribute12;
1739 x_options_tbl(I).attribute13 := opt_rec.attribute13;
1740 x_options_tbl(I).attribute14 := opt_rec.attribute14;
1741 x_options_tbl(I).attribute15 := opt_rec.attribute15;
1742 x_options_tbl(I).attribute16 := opt_rec.attribute16;
1743 x_options_tbl(I).attribute17 := opt_rec.attribute17;
1744 x_options_tbl(I).attribute18 := opt_rec.attribute18;
1745 x_options_tbl(I).attribute19 := opt_rec.attribute19;
1746 x_options_tbl(I).attribute20 := opt_rec.attribute20;
1747 x_options_tbl(I).context := opt_rec.context;
1748
1749 IF opt_rec.configuration_id is NULL AND opt_rec.config_header_id is NULL
1750 THEN
1751 x_options_tbl(I).operation := OE_GLOBALS.G_OPR_CREATE;
1752 ELSE
1753 x_options_tbl(I).operation := OE_GLOBALS.G_OPR_NONE;
1754 END IF;
1755
1756 x_options_tbl(I).line_id := opt_rec.line_id;
1757
1758 IF l_debug_level > 0 THEN
1759 oe_debug_pub.add('QTY FROM DB '|| OPT_REC.ORDERED_QUANTITY , 3 );
1760 oe_debug_pub.add('ATTRIBUTE1 FROM DB '|| OPT_REC.ATTRIBUTE1 , 3 );
1761 END IF;
1762 ELSE
1763 IF l_debug_level > 0 THEN
1764 oe_debug_pub.add('----- not assigned '||OPT_REC.COMPONENT_CODE,3);
1765 END IF;
1766 END IF; -- if not bom_item = 1
1767
1768 END LOOP;
1769
1770 IF l_debug_level > 0 THEN
1771 oe_debug_pub.add( 'LEAVING GET_OPTIONS_FROM_DB' , 1 ) ;
1772 END IF;
1773
1774 EXCEPTION
1775 WHEN OTHERS THEN
1776 IF l_debug_level > 0 THEN
1777 oe_debug_pub.add( 'GET_OPTIONS_FROM_DB EXCEPTION'|| SQLERRM , 1 ) ;
1778 END IF;
1779 RAISE;
1780 END Get_Options_From_DB;
1781
1782 /*------------------------------------------------------------------------
1783 PROCEDURE Print_Time
1784
1785 -------------------------------------------------------------------------*/
1786
1787 PROCEDURE Print_Time(p_msg IN VARCHAR2)
1788 IS
1789 l_time VARCHAR2(100);
1790 --
1791 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1792 --
1793 BEGIN
1794 l_time := to_char (new_time (sysdate, 'PST', 'EST'),
1795 'DD-MON-YY HH24:MI:SS');
1796 IF l_debug_level > 0 THEN
1797 oe_debug_pub.add( P_MSG || ': '|| L_TIME , 1 ) ;
1798 END IF;
1799 END Print_Time;
1800
1801
1802 /*------------------------------------------------------------------------
1803 PROCEDURE Handle_Ret_Status
1804
1805 -------------------------------------------------------------------------*/
1806
1807 PROCEDURE Handle_Ret_Status(p_return_Status VARCHAR2)
1808 IS
1809 --
1810 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1811 --
1812 BEGIN
1813 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1814 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1815 ELSIF p_return_status = FND_API.G_RET_STS_ERROR THEN
1816 RAISE FND_API.G_EXC_ERROR;
1817 END IF;
1818 END Handle_Ret_Status;
1819
1820
1821 /*------------------------------------------------------------------------
1822 PROCEDURE: Load_BOM_Table
1823
1824 -------------------------------------------------------------------------*/
1825 PROCEDURE Load_BOM_Table
1826 ( p_options_tbl IN OE_Process_Options_Pvt.SELECTED_OPTIONS_TBL_TYPE
1827 ,x_bom_validation_tbl OUT NOCOPY OE_CONFIG_VALIDATION_PVT.VALIDATE_OPTIONS_TBL_TYPE)
1828 IS
1829 I NUMBER;
1830 l_count NUMBER;
1831 --
1832 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1833 --
1834 BEGIN
1835 IF l_debug_level > 0 THEN
1836 oe_debug_pub.add( 'ENTERING LOAD_BOM_TABLE' , 1 ) ;
1837 END IF;
1838
1839 l_count := 0;
1840 I := p_options_tbl.FIRST;
1841
1842 IF l_debug_level > 0 THEN
1843 oe_debug_pub.add('NUMBER OF OPTION SENT IN ' || P_OPTIONS_TBL.COUNT,1);
1844 END IF;
1845
1846 WHILE I is not null
1847 LOOP
1848
1849 IF l_debug_level > 0 THEN
1850 oe_debug_pub.add(P_OPTIONS_TBL(I).disabled_flag || ' child '
1851 || P_OPTIONS_TBL(I).line_id,1);
1852 END IF;
1853
1854 IF nvl(p_options_tbl(I).operation, OE_GLOBALS.G_OPR_NONE)
1855 <> OE_GLOBALS.G_OPR_DELETE AND
1856 nvl(p_options_tbl(I).disabled_flag, 'N') = 'N' THEN
1857
1858 IF l_debug_level > 0 THEN
1859 oe_debug_pub.add
1860 ( 'COMPONENT: '|| P_OPTIONS_TBL ( I ) .COMPONENT_CODE , 2 ) ;
1861 END IF;
1862
1863 l_count := l_count + 1;
1864 x_bom_validation_tbl(l_count).component_code
1865 := p_options_tbl(I).component_code;
1866 x_bom_validation_tbl(l_count).ordered_quantity
1867 := p_options_tbl(I).ordered_quantity;
1868 x_bom_validation_tbl(l_count).ordered_item
1869 := p_options_tbl(I).ordered_item;
1870 x_bom_validation_tbl(l_count).bom_item_type
1871 := p_options_tbl(I).bom_item_type;
1872 x_bom_validation_tbl(l_count).sort_order
1873 := p_options_tbl(I).sort_order;
1874 END IF;
1875 I := p_options_tbl.NEXT(I);
1876 END LOOP;
1877
1878 IF l_debug_level > 0 THEN
1879 oe_debug_pub.add( 'LEAVING LOAD_BOM_TABLE' , 1 ) ;
1880 END IF;
1881 EXCEPTION
1882 WHEN OTHERS THEN
1883 IF l_debug_level > 0 THEN
1884 oe_debug_pub.add( 'LOAD_BOM_TABLE EXCEPTION'|| SQLERRM , 1 ) ;
1885 END IF;
1886 RAISE;
1887 END;
1888
1889
1890 END OE_Process_Options_Pvt;