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