1 PACKAGE BODY Oe_Config_Pvt AS
2 /* $Header: OEXVCFGB.pls 120.13.12010000.2 2008/09/16 04:30:33 spothula ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='oe_config_pvt';
5
6
7 /*--------------------------------------------------------------------
8 forward declarations
9 -------------------------------------------------------------------*/
10
11 Procedure update_link_to_line_id
12 ( p_top_model_line_id IN NUMBER
13 ,p_remnant_flag IN VARCHAR2
14 ,p_config_hdr_id IN NUMBER);
15
16 Procedure update_ato_line_attributes
17 ( p_top_model_line_id IN NUMBER
18 ,p_ui_flag IN VARCHAR2
19 ,p_config_hdr_id IN NUMBER);
20
21 PROCEDURE Check_If_cancellation
22 ( p_line_id IN NUMBER
23 ,p_top_model_line_id IN NUMBER
24 ,p_item_type_code IN VARCHAR2
25 ,x_cancellation OUT NOCOPY /* file.sql.39 change */ BOOLEAN
26 ,x_current_quantity OUT NOCOPY /* file.sql.39 change */ NUMBER);
27
28 PROCEDURE Handle_Inserts
29 ( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
30 ,p_config_hdr_id IN NUMBER
31 ,p_config_rev_nbr IN NUMBER
32 ,p_config_instance_tbl IN csi_datastructures_pub.instance_cz_tbl
33 ,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
34 ,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
35 ,p_direct_save IN BOOLEAN := FALSE
36 ,p_ui_flag IN VARCHAR2 := 'Y');
37
38 PROCEDURE Handle_Inserts_Old
39 ( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
40 ,p_config_hdr_id IN NUMBER
41 ,p_config_rev_nbr IN NUMBER
42 ,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
43 ,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
44 ,p_direct_save IN BOOLEAN := FALSE);
45
46 PROCEDURE Handle_Updates
47 ( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
48 ,p_config_hdr_id IN NUMBER
49 ,p_config_rev_nbr IN NUMBER
50 ,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
51 ,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
52 ,p_direct_save IN BOOLEAN := FALSE
53 ,p_ui_flag IN VARCHAR2);
54
55 PROCEDURE Handle_Updates_old
56 ( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
57 ,p_config_hdr_id IN NUMBER
58 ,p_config_rev_nbr IN NUMBER
59 ,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
60 ,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
61 ,p_direct_save IN BOOLEAN := FALSE
62 ,p_ui_flag IN VARCHAR2);
63
64 PROCEDURE Handle_Deletes
65 ( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
66 ,p_config_hdr_id IN NUMBER
67 ,p_config_rev_nbr IN NUMBER
68 ,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
69 ,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
70 ,p_direct_save IN BOOLEAN := FALSE
71 ,p_ui_flag IN VARCHAR2);
72
73 PROCEDURE Handle_Deletes_Old
74 ( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
75 ,p_config_hdr_id IN NUMBER
76 ,p_config_rev_nbr IN NUMBER
77 ,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
78 ,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
79 ,p_direct_save IN BOOLEAN := FALSE
80 ,p_ui_flag IN VARCHAR2);
81
82 PROCEDURE Print_Time(p_msg IN VARCHAR2);
83
84 /* --------------------------------------------------------------------
85 Procedure Name : Process_Config
86 Description :
87
88 Lock the configuration. Explode the bill.
89
90 Get the records from cz_config_details_v
91 and bom_explosions and
92 insert,update,or deletethem from order_lines.
93
94 We will open three cursors :
95 the first to pass an operation of INSERT
96 for records which do not exist
97 => handle_inserts proc
98 The second to pass an operation of UPDATE
99 for records which already exist.
100 => handle_updates prc
101 The third to pass an operation of DELETE
102 for records which exist but are no longer selected.
103 => handle_deletes proc
104
105 get values from cz_config_details_v
106 viz. comp_code, qty, cfg_hdr_id, rev_no
107 and bom_explosions viz.
108 component_sequence_id, sort_order, bom_item_type,
109 bill_sequence_id, top_bill_sequence_id
110
111 call process_order and update the links.
112
113 If the p_ui_flag 'Y', we set the control_rec.process to TRUE
114 This means when options are created using configurator,
115 we want all the delayed requests to be processed.
116 when order_import calls process_order to create options,
117 control_rec.process is set to false so that delayed requests
118 do not get processed in the recursive call to process_order
119 after batch validation.
120
121 Change Record:
122 Bug 2181376: explode bill is not required since in handle_inserts
123 procedure we will selecet all required data from cz_config_details_v
124 and do not need to join with bom_explosions anymore. This change is
125 also useful for multiple instance project.
126 --------------------------------------------------------------------*/
127
128 Procedure Process_Config(p_header_id IN NUMBER
129 ,p_config_hdr_id IN NUMBER
130 ,p_config_rev_nbr IN NUMBER
131 ,p_top_model_line_id IN NUMBER
132 ,p_ui_flag IN VARCHAR2 :='Y'
133 ,p_config_instance_tbl IN
134 csi_datastructures_pub.instance_cz_tbl := G_CONFIG_INSTANCE_TBL
135 ,x_change_flag OUT NOCOPY /* file.sql.39 change */ VARCHAR2
136 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
137 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
138 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
139 )
140 IS
141
142 -- general, column_changes and and cz's delete api stuff
143 l_direct_save BOOLEAN;
144 l_return_status_del VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
145 l_model_line_rec OE_ORDER_PUB.Line_Rec_Type;
146 l_profile_value VARCHAR2(1) :=
147 upper(FND_PROFILE.VALUE('ONT_CONFIG_QUICK_SAVE'));
148
149 -- process_order in params
150 l_control_rec OE_GLOBALS.Control_Rec_Type;
151 l_header_rec OE_Order_PUB.Header_Rec_Type;
152 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
153 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
154 l_class_line_tbl OE_Order_PUB.Line_Tbl_Type;
155 l_operation VARCHAR2(1) := 'A';
156 l_msg_count NUMBER;
157 l_msg_data VARCHAR2(2000);
158 l_return_status VARCHAR2(1);
159 l_model_new_qty NUMBER;
160 --
161 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
162 --
163 BEGIN
164
165 Print_Time('Entering Process Config '|| p_config_hdr_id);
166
167 OE_Msg_Pub.Set_Msg_Context
168 ( p_entity_code => OE_Globals.G_ENTITY_LINE
169 ,p_entity_id => p_top_model_line_id
170 ,p_header_id => p_header_id
171 ,p_line_id => p_top_model_line_id);
172
173
174 OE_LINE_UTIL.Lock_Row (p_line_id => p_top_model_line_id
175 ,p_x_line_rec => l_model_line_rec
176 ,x_return_status => l_return_status);
177
178 OE_MSG_PUB.update_msg_context
179 ( p_entity_code => 'LINE'
180 ,p_entity_id => l_model_line_rec.line_id
181 ,p_header_id => l_model_line_rec.header_id
182 ,p_line_id => l_model_line_rec.line_id
183 ,p_orig_sys_document_ref => l_model_line_rec.orig_sys_document_ref
184 ,p_orig_sys_document_line_ref => l_model_line_rec.orig_sys_line_ref
185 ,p_orig_sys_shipment_ref => l_model_line_rec.orig_sys_shipment_ref
186 ,p_change_sequence => l_model_line_rec.change_sequence
187 ,p_source_document_id => l_model_line_rec.source_document_id
188 ,p_source_document_line_id => l_model_line_rec.source_document_line_id
189 ,p_order_source_id => l_model_line_rec.order_source_id
190 ,p_source_document_type_id => l_model_line_rec.source_document_type_id);
191
192 IF l_model_line_rec.booked_flag = 'N' and l_profile_value = 'Y' AND
193 p_ui_flag = 'Y' THEN
194 IF l_debug_level > 0 THEN
195 oe_debug_pub.add( 'DIRECT SAVE ON' , 1 ) ;
196 END IF;
197 l_direct_save := TRUE;
198 ELSE
199 IF l_debug_level > 0 THEN
200 oe_debug_pub.add( 'DIRECT SAVE OFF' || L_PROFILE_VALUE , 1 ) ;
201 END IF;
202 l_direct_save := FALSE;
203 END IF;
204
205
206 -- if model goes thro process_config 1st time i.e just created,
207 -- and if qty of model changed in configurator, we have to update it.
208 -- in this situation(will happen onlu once in entire life of configuration)
209
210 IF l_model_line_rec.config_header_id is null AND
211 l_model_line_rec.config_rev_nbr is null THEN
212
213 BEGIN
214 SELECT quantity, config_item_id
215 INTO l_model_new_qty, l_model_line_rec.configuration_id
216 FROM cz_config_details_v
217 WHERE config_hdr_id = p_config_hdr_id
218 AND config_rev_nbr = p_config_rev_nbr
219 AND inventory_item_id = l_model_line_rec.inventory_item_id;
220
221 -- Bug 6073974 Update the Configuration id for model line
222 UPDATE OE_ORDER_LINES_ALL
223 SET CONFIGURATION_ID = l_model_line_rec.configuration_id
224 WHERE inventory_item_id = l_model_line_rec.inventory_item_id
225 AND LINE_ID = l_model_line_rec.line_id;
226
227 IF l_debug_level > 0 THEN
228 oe_debug_pub.add( 'Configuration_id for Model: '|| l_model_line_rec.line_id , 1 ) ;
229 END IF;
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 IF l_debug_level > 0 THEN
234 oe_debug_pub.add( 'QTY SELECT: '|| SQLERRM , 1 ) ;
235 END IF;
236 RAISE;
237 END;
238
239 IF l_model_new_qty <> nvl(l_model_line_rec.ordered_quantity, 0) THEN
240
241 IF l_debug_level > 0 THEN
242 oe_debug_pub.add('UPDATE MODEL WITH NEW QTY '|| L_MODEL_NEW_QTY,1);
243 END IF;
244
245 l_model_line_rec.ordered_quantity := l_model_new_qty;
246 l_model_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
247 l_line_tbl(nvl(l_line_tbl.LAST, 0) + 1) := l_model_line_rec;
248
249 END IF;
250
251 END IF;
252
253
254 Handle_Inserts(p_model_line_rec => l_model_line_rec
255 ,p_config_hdr_id => p_config_hdr_id
256 ,p_config_rev_nbr => p_config_rev_nbr
257 ,p_x_line_tbl => l_line_tbl
258 ,p_config_instance_tbl => p_config_instance_tbl
259 ,p_x_class_line_tbl => l_class_line_tbl
260 ,p_direct_save => l_direct_save
261 ,p_ui_flag => p_ui_flag);
262
263 IF l_model_line_rec.config_header_id is not null AND
264 l_model_line_rec.config_rev_nbr is not null THEN
265
266 IF l_debug_level > 0 THEN
267 oe_debug_pub.add('MODEL QTY: '|| L_MODEL_LINE_REC.ORDERED_QUANTITY,1);
268 END IF;
269
270 Handle_Updates(p_model_line_rec => l_model_line_rec
271 ,p_config_hdr_id => p_config_hdr_id
272 ,p_config_rev_nbr => p_config_rev_nbr
273 ,p_x_line_tbl => l_line_tbl
274 ,p_x_class_line_tbl => l_class_line_tbl
275 ,p_direct_save => FALSE
276 ,p_ui_flag => p_ui_flag);
277
278 Handle_Deletes(p_model_line_rec => l_model_line_rec
279 ,p_config_hdr_id => p_config_hdr_id
280 ,p_config_rev_nbr => p_config_rev_nbr
281 ,p_x_line_tbl => l_line_tbl
282 ,p_x_class_line_tbl => l_class_line_tbl
283 ,p_direct_save => FALSE
284 ,p_ui_flag => p_ui_flag);
285 ELSE
286 IF l_debug_level > 0 THEN
287 oe_debug_pub.add( 'FIRST TIME CREATE , NO UPD/DEL REQ.' , 1 ) ;
288 END IF;
289 l_operation := 'C';
290 END IF;
291
292
293 --even if line_count = 0, we need to call, for change columns.
294
295 IF l_debug_level > 0 THEN
296 oe_debug_pub.add( 'PROCESS CONFIG , LINES: '||L_LINE_TBL.COUNT , 1 ) ;
297 oe_debug_pub.add( 'DIRECT SAVE LINES: '||L_CLASS_LINE_TBL.COUNT , 1 ) ;
298 END IF;
299
300 l_control_rec.check_security := TRUE;
301
302 IF p_ui_flag = 'Y' THEN
303 l_control_rec.process := TRUE;
304 ELSE
305 l_control_rec.process := FALSE;
306 END IF;
307
308 oe_config_pvt.Call_Process_Order
309 ( p_line_tbl => l_line_tbl
310 ,p_class_line_tbl => l_class_line_tbl
311 ,p_control_rec => l_control_rec
312 ,p_ui_flag => p_ui_flag
313 ,p_top_model_line_id => p_top_model_line_id
314 ,p_config_hdr_id => p_config_hdr_id
315 ,p_config_rev_nbr => p_config_rev_nbr
316 ,p_update_columns => TRUE
317 ,x_return_status => l_return_status);
318
319 IF l_debug_level > 0 THEN
320 oe_debug_pub.add('PROCESS ORDER RETURN_STATUS: ' || L_RETURN_STATUS ,1);
321 END IF;
322
323 IF p_config_hdr_id = l_model_line_rec.config_header_id AND
324 p_config_rev_nbr = l_model_line_rec.config_rev_nbr THEN
325 oe_debug_pub.add('do not delete, special', 1);
326 ELSE
327 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
328 -- since we could not save the config successfully,
329 -- delete current rev. in SPC
330
331 Delete_Config( p_config_hdr_id => p_config_hdr_id
332 ,p_config_rev_nbr => p_config_rev_nbr
333 ,x_return_status => l_return_status_del);
334
335 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
336 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
337 ELSE
338 RAISE FND_API.G_EXC_ERROR;
339 END IF;
340 ELSE
341 -- If we successfully save the configuration in oe_order_lines then
342 -- Time to delete previous revisions from spc
343
344 IF l_model_line_rec.config_header_id is not null AND
345 l_model_line_rec.config_rev_nbr is not null
346 THEN
347 Delete_Config( p_config_hdr_id => l_model_line_rec.config_header_id
348 ,p_config_rev_nbr => l_model_line_rec.config_rev_nbr
349 ,x_return_status => l_return_status_del);
350 END IF;
351 END IF; -- if success.
352 END IF; -- do no delete
353
354 -- setting change flag, decides to commit and query line block or not.
355 IF p_ui_flag = 'Y' AND ( nvl(l_line_tbl.count, 0) > 0 OR
356 p_config_hdr_id is not null ) THEN
357 x_change_flag := 'Y';
358 IF l_debug_level > 0 THEN
359 oe_debug_pub.add( 'CHANGED CONFIG INTERACTIVELY' , 1 ) ;
360 END IF;
361 END IF;
362
363 x_return_status := FND_API.G_RET_STS_SUCCESS;
364
365 Print_Time('Leaving Process Config');
366
367 EXCEPTION
368 WHEN FND_API.G_EXC_ERROR THEN
369
370 x_return_status := FND_API.G_RET_STS_ERROR;
371 -- Get message count and data
372 oe_msg_pub.count_and_get
373 ( p_count => x_msg_count
374 , p_data => x_msg_data
375 );
379 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
376
377 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
378
380 -- Get message count and data
381 oe_msg_pub.count_and_get
382 ( p_count => x_msg_count
383 , p_data => x_msg_data
384 );
385
386 WHEN OTHERS THEN
387 IF l_debug_level > 0 THEN
388 oe_debug_pub.add( 'ERROR: ' || SUBSTR ( SQLERRM , 1 , 100 ) , 1 ) ;
389 END IF;
390
391 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR)
392 THEN
393 oe_msg_pub.Add_Exc_Msg
394 ( G_PKG_NAME
395 , 'Process_Config'
396 );
397 END IF;
398
399 -- Get message count and data
400 oe_msg_pub.count_and_get
401 ( p_count => x_msg_count
402 , p_data => x_msg_data
403 );
404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
405
406 END Process_Config;
407
408
409 /*------------------------------------------------------------------
410 PROCEDURE Handle_Inserts
411
412 if a component is present in cz_config_details_v but not present
413 in oe_order_lines, we need to insert it.
414
415 Change Record:
416 Bug 2181376: explode bill is not required since in this
417 procedure we will selecet all required data from cz_config_details_v
418 and do not need to join with bom_explosions anymore. This change is
419 also useful for multiple instance project
420 Bug 2869052 :
421 Default_Child_Line procedure would be called only if there are
422 any new class lines to be created and direct save shoule be true.
423 If the call returns an error an exception would be raised. New
424 variable l_default_child_line has been created.
425
426 MACD: Line Type Support - modified the cursor to select the line_type
427 also and then if it null and not missing, assigning the child line
428 record's line_type_id to the retreived line_type
429
430 Bug 3611416
431 Send reason for CREATE operation also, will be required if there is
432 a require reason constraint for versioning during create operation.
433
434 bug3578056
435 for a new configuration, the IB fields should be NULL for the
436 top model and child lines
437 -------------------------------------------------------------------*/
438
439 PROCEDURE Handle_Inserts
440 ( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
441 ,p_config_hdr_id IN NUMBER
442 ,p_config_rev_nbr IN NUMBER
443 ,p_config_instance_tbl IN csi_datastructures_pub.instance_cz_tbl
444 ,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
445 ,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
446 ,p_direct_save IN BOOLEAN := FALSE
447 ,p_ui_flag IN VARCHAR2 := 'Y')
448 IS
449
450 CURSOR config_ins_cursor IS
451 SELECT c.component_code, c.quantity
452 ,c.uom_code, c.inventory_item_id
453 ,c.instance_hdr_id, c.instance_rev_nbr
454 ,c.component_sequence_id, c.bom_sort_order
455 ,c.bom_item_type, c.config_item_id
456 ,c.line_type
457 FROM CZ_CONFIG_DETAILS_V c
458 WHERE c.config_hdr_id = p_config_hdr_id
459 AND c.config_rev_nbr = p_config_rev_nbr
460 AND NOT EXISTS
461 ( SELECT 'X'
462 FROM oe_order_lines l
463 WHERE l.top_model_line_id = p_model_line_rec.line_id
464 AND l.component_code = c.component_code
465 AND l.configuration_id = c.config_item_id
466 AND l.open_flag = 'Y')
467 ORDER BY c.component_code;
468
469
470 l_line_rec OE_ORDER_PUB.Line_Rec_Type;
471 l_class_line_rec OE_ORDER_PUB.Line_Rec_Type;
472 l_line_count NUMBER;
473 l_class_line_count NUMBER;
474 l_return_status VARCHAR2(1);
475 l_concatenated_segments VARCHAR2(163);
476 l_default_child_line BOOLEAN := TRUE;
477 --
478 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
479 --
480 l_top_container_model VARCHAR2(1);
481 l_part_of_container VARCHAR2(1);
482 l_default_line_type_id NUMBER; --Added for bug 5107271
483 BEGIN
484 Print_Time('Handle_Inserts start time');
485
486 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110508' THEN
487
488 IF l_debug_level > 0 THEN
489 oe_debug_pub.add( 'INS: PACK H NEW LOGIC' , 1 ) ;
490 END IF;
491
492 BEGIN
493
494 UPDATE oe_order_lines oe
495 SET ( configuration_id , sort_order ) =
496 (SELECT config_item_id , bom_sort_order --bug6628691
497 FROM cz_config_details_v
498 WHERE config_hdr_id = p_config_hdr_id
499 AND config_rev_nbr = p_config_rev_nbr
500 AND component_code = oe.component_code
501 )
502 WHERE top_model_line_id = p_model_line_rec.line_id
503 AND config_header_id is NULL
504 AND configuration_id is NULL
505 AND item_type_code in ('MODEL', 'CLASS', 'OPTION', 'KIT')
506 AND open_flag = 'Y';
507
511 END IF;
508 IF SQL%FOUND THEN
509 IF l_debug_level > 0 THEN
510 oe_debug_pub.add( 'CONFIGURATION_ID UPDATED '|| SQL%ROWCOUNT ,3);
512 ELSE
513 IF l_debug_level > 0 THEN
514 oe_debug_pub.add( 'CRM 1:CONFIGURATION_ID NOT UPDATED ' , 3 ) ;
515 END IF;
516
517 UPDATE oe_order_lines oe
518 SET ( configuration_id , sort_order ) =
519 (SELECT config_item_id , bom_sort_order --bug6628691
520 FROM cz_config_details_v
521 WHERE config_hdr_id = p_config_hdr_id
522 AND config_rev_nbr = p_config_rev_nbr
523 AND component_code = oe.component_code
524 )
525 WHERE top_model_line_id = p_model_line_rec.line_id
526 AND configuration_id is NULL
527 AND item_type_code in ('MODEL', 'CLASS', 'OPTION', 'KIT')
528 AND open_flag = 'Y';
529
530 IF SQL%FOUND THEN
531 IF l_debug_level > 0 THEN
532 oe_debug_pub.add( 'CRM: CONFIGURATION_ID '|| SQL%ROWCOUNT , 3 ) ;
533 END IF;
534 ELSE
535 IF l_debug_level > 0 THEN
536 oe_debug_pub.add( 'CRM 2:CONFIGURATION_ID NOT UPDATED ' , 3 ) ;
537 END IF;
538 END IF;
539 END IF;
540
541 EXCEPTION
542 WHEN TOO_MANY_ROWS THEN
543 FND_Message.Set_Name('ONT', 'OE_CONFIG_MI_NOT_ALLOWED');
544 FND_MESSAGE.SET_TOKEN('MODEL', p_model_line_rec.ordered_item);
545 FND_MESSAGE.SET_TOKEN('LINE_NUMBER', p_model_line_rec.line_number ||
546 p_model_line_rec.shipment_number);
547 OE_Msg_Pub.Add;
548 RAISE;
549 WHEN OTHERS THEN
550 IF l_debug_level > 0 THEN
551 oe_debug_pub.add( 'OTHERS IN CONFIG ID UPD '|| SQLERRM , 1 ) ;
552 END IF;
553 RAISE;
554 END;
555
556 ELSE
557 Handle_Inserts_Old
558 (p_model_line_rec => p_model_line_rec
559 ,p_config_hdr_id => p_config_hdr_id
560 ,p_config_rev_nbr => p_config_rev_nbr
561 ,p_x_line_tbl => p_x_line_tbl
562 ,p_x_class_line_tbl => p_x_class_line_tbl
563 ,p_direct_save => p_direct_save);
564
565 IF l_debug_level > 0 THEN
566 oe_debug_pub.add( 'RETURNING..' , 1 ) ;
567 END IF;
568
569 RETURN;
570 END IF;
571
572
573 l_line_count := nvl(p_x_line_tbl.LAST, 0);
574 l_class_line_count := nvl(p_x_class_line_tbl.LAST, 0);
575
576 l_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
577 l_line_rec.operation := OE_GLOBALS.G_OPR_CREATE;
578 l_line_rec.header_id := p_model_line_rec.header_id;
579 l_line_rec.item_identifier_type := 'INT';
580 l_line_rec.config_header_id := p_config_hdr_id;
581 l_line_rec.config_rev_nbr := p_config_rev_nbr;
582 l_line_rec.change_reason := 'SYSTEM';
583
584 IF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' THEN
585
586 OE_CONFIG_TSO_PVT.Is_Part_Of_Container_Model
587 ( p_line_id => p_model_line_rec.line_id
588 ,x_top_container_model => l_top_container_model
589 ,x_part_of_container => l_part_of_container );
590
591 IF l_debug_level > 0 THEN
592 OE_DEBUG_PUB.Add('Line ID:'||p_model_line_rec.line_id,3);
593 OE_DEBUG_PUB.Add('Top Container:'||l_top_container_model,3);
594 OE_DEBUG_PUB.Add('Part of Container:'||l_part_of_container,3);
595 END IF;
596
597 IF l_top_container_model = 'Y' THEN
598
599 l_line_rec.ib_owner := NULL;
600 l_line_rec.ib_current_location := NULL;
601 l_line_rec.ib_installed_at_location := NULL;
602
603 IF l_debug_level > 0 THEN
604 OE_DEBUG_PUB.Add('IB Fields set to NULL for child lines',3);
605 END IF;
606
607 END IF;
608 END IF;
609
610
611 FOR config_rec in config_ins_cursor
612 LOOP
613
614 --MACD---------------------------------------------
615 --should populate IB values for all lines except the top
616 --container model itself.
617
618 IF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' THEN
619 FOR T IN 1..p_config_instance_tbl.COUNT LOOP
620 IF l_debug_level > 0 THEN
621 oe_debug_pub.add('CZ Inst Hdr:'||config_rec.instance_hdr_id,2);
622 oe_debug_pub.add('IB inst Hdr:'
623 ||p_config_instance_tbl(T).config_instance_hdr_id,2);
624 oe_debug_pub.add('CZ Rev Nbr:'||config_rec.instance_rev_nbr,2);
625 oe_debug_pub.add('IB Rev:'
626 ||p_config_instance_tbl(T).config_instance_rev_number,2);
627 oe_debug_pub.add('CZ Item:'||config_rec.config_item_id,3);
628 oe_debug_pub.add('IB Item:'||
629 p_config_instance_tbl(T).config_instance_item_id,2);
630 END IF;
631
632 IF config_rec.instance_hdr_id =
633 p_config_instance_tbl(T).config_instance_hdr_id AND
634 config_rec.config_item_id =
635 p_config_instance_tbl(T).config_instance_item_id THEN
636
637 IF l_debug_level > 0 THEN
638 oe_debug_pub.add
639 ('match found for item:'||config_rec.inventory_item_id);
640 END IF;
641
645 p_config_instance_tbl(T).ship_to_site_use_id;
642 l_line_rec.invoice_to_org_id :=
643 p_config_instance_tbl(T).bill_to_site_use_id;
644 l_line_rec.ship_to_org_id :=
646 l_line_rec.ib_owner := 'INSTALL_BASE';
647 l_line_rec.ib_current_location := 'INSTALL_BASE';
648 l_line_rec.ib_installed_at_location := 'INSTALL_BASE';
649 END IF;
650 END LOOP;
651 ELSE
652 IF l_debug_level > 0 THEN
653 OE_DEBUG_PUB.Add('Not in pack J. No MACD Logic',3);
654 OE_DEBUG_PUB.Add('IB Values NOT populated',3);
655 END IF;
656
657 END IF;
658 --MACD---------------------------------------------
659
660 -- Get the concatanted segment value to be stored in
661 -- order lines at ordered_item
662
663 BEGIN
664 SELECT concatenated_segments
665 INTO l_concatenated_segments
666 FROM MTL_SYSTEM_ITEMS_KFV
667 WHERE inventory_item_id = config_rec.inventory_item_id
668 AND organization_id = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID');
669 EXCEPTION
670 WHEN NO_DATA_FOUND THEN
671 RAISE FND_API.G_EXC_ERROR;
672 END;
673
674 IF l_debug_level > 0 THEN
675 oe_debug_pub.add('CONCAT SEGMENT IS: ' || L_CONCATENATED_SEGMENTS,3);
676 oe_debug_pub.add('INSERTING COMP CODE: '||CONFIG_REC.COMPONENT_CODE,1);
677 oe_debug_pub.add('CONFIGURATION ID: ' || CONFIG_REC.CONFIG_ITEM_ID ,1);
678 END IF;
679
680 IF p_direct_save AND
681 (config_rec.bom_item_type = 2 OR config_rec.bom_item_type = 1)
682 THEN
683
684 IF l_default_child_line THEN
685
686 oe_debug_pub.add( 'DIRECT SAVE IS ON' , 3 ) ;
687
688 l_class_line_rec.config_header_id := p_config_hdr_id;
689 l_class_line_rec.config_rev_nbr := p_config_rev_nbr;
690
691 OE_Config_Util.Default_Child_Line
692 ( p_parent_line_rec => p_model_line_rec
693 ,p_x_child_line_rec => l_class_line_rec
694 ,p_direct_save => p_direct_save
695 ,x_return_status => l_return_status);
696
697 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
699 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
700 RAISE FND_API.G_EXC_ERROR;
701 END IF;
702
703 l_default_child_line := FALSE;
704 l_default_line_type_id := l_class_line_rec.line_type_id; --Added for bug 5107271
705
706 END IF;
707
708 IF config_rec.line_type IS NOT NULL AND
709 config_rec.line_type <> FND_API.G_MISS_NUM THEN
710
711 IF l_debug_level > 0 THEN
712 OE_DEBUG_PUB.Add('Line Type from cz:'||config_rec.line_type,3);
713 END IF;
714 l_class_line_rec.line_type_id := config_rec.line_type;
715 ELSE
716 /* Modified the below statement for bug 5107271 to assign the value of
717 l_default_line_type_id instead of FND_API.G_MISS_NUM */
718 -- l_class_line_rec.line_type_id := FND_API.G_MISS_NUM;
719 l_class_line_rec.line_type_id := l_default_line_type_id;
720 END IF;
721
722 l_class_line_rec.ordered_quantity := config_rec.quantity;
723 l_class_line_rec.order_quantity_uom := config_rec.uom_code;
724 l_class_line_rec.component_sequence_id
725 := config_rec.component_sequence_id;
726 l_class_line_rec.component_code := config_rec.component_code;
727 l_class_line_rec.sort_order := config_rec.bom_sort_order;
728 l_class_line_rec.inventory_item_id := config_rec.inventory_item_id;
729 l_class_line_rec.configuration_id := config_rec.config_item_id;
730 l_class_line_rec.ordered_item := l_concatenated_segments;
731
732 SELECT OE_ORDER_LINES_S.NEXTVAL
733 INTO l_class_line_rec.line_id
734 FROM DUAL;
735
736 l_class_line_rec.pricing_quantity_uom
737 := l_class_line_rec.order_quantity_uom;
738 l_class_line_rec.pricing_quantity
739 := l_class_line_rec.ordered_quantity;
740
741 l_class_line_count := l_class_line_count+1;
742 p_x_class_line_tbl(l_class_line_count) := l_class_line_rec;
743
744 ELSE
745
746 IF config_rec.line_type IS NOT NULL AND
747 config_rec.line_type <> FND_API.G_MISS_NUM THEN
748
749 IF l_debug_level > 0 THEN
750 OE_DEBUG_PUB.Add('cz LineType:'||config_rec.line_type,3);
751 END IF;
752 l_line_rec.line_type_id := config_rec.line_type;
753 ELSE
754 l_line_rec.line_type_id := FND_API.G_MISS_NUM;
755 END IF;
756
757
758 l_line_rec.ordered_quantity := config_rec.quantity;
759 l_line_rec.order_quantity_uom := config_rec.uom_code;
760 l_line_rec.component_sequence_id := config_rec.component_sequence_id;
761 l_line_rec.top_model_line_id := p_model_line_rec.line_id;
762 l_line_rec.component_code := config_rec.component_code;
763 l_line_rec.sort_order := config_rec.bom_sort_order;
764 l_line_rec.inventory_item_id := config_rec.inventory_item_id;
768 IF config_rec.bom_item_type = 1 OR
765 l_line_rec.configuration_id := config_rec.config_item_id;
766 l_line_rec.ordered_item := l_concatenated_segments;
767
769 config_rec.bom_item_type = 2 THEN
770 l_line_rec.item_type_code := OE_GLOBALS.G_ITEM_CLASS;
771 ELSE
772 l_line_rec.item_type_code := null;
773 END IF;
774
775 l_line_count := l_line_count+1;
776 p_x_line_tbl(l_line_count) := l_line_rec;
777 END IF;
778
779 END LOOP;
780
781 Print_Time('Handle_Inserts end time');
782 EXCEPTION
783 WHEN OTHERS THEN
784 IF l_debug_level > 0 THEN
785 oe_debug_pub.add( 'EXCEPTION IN HANDLE_INSERTS'|| SQLERRM , 1 ) ;
786 END IF;
787
788 RAISE;
789 END Handle_Inserts;
790
791
792
793 /*------------------------------------------------------------
794 PROCEDURE Handle_Inserts_Old
795
796 if a component is present in cz_config_details_v but not present
797 in oe_order_lines, we need to insert it.
798
799 Change Record:
800 Bug 2181376: explode bill is not required since in this
801 procedure we will selecet all required data from cz_config_details_v
802 and do not need to join with bom_explosions anymore. This change is
803 also useful for multiple instance project.
804 Bug 2869052 :
805 Default_Child_Line procedure would be called only if there are
806 any new class lines to be created and direct save shoule be true.
807 If the call returns an error an exception would be raised. New
808 variable l_default_child_line has been created.
809 -------------------------------------------------------------*/
810
811 PROCEDURE Handle_Inserts_Old
812 ( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
813 ,p_config_hdr_id IN NUMBER
814 ,p_config_rev_nbr IN NUMBER
815 ,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
816 ,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
817 ,p_direct_save IN BOOLEAN := FALSE)
818 IS
819
820 CURSOR config_ins_cursor IS
821 SELECT c.component_code, c.quantity
822 ,c.uom_code, c.inventory_item_id
823 ,c.component_sequence_id, c.bom_sort_order
824 ,c.bom_item_type
825 FROM CZ_CONFIG_DETAILS_V c
826 WHERE c.config_hdr_id = p_config_hdr_id
827 AND c.config_rev_nbr = p_config_rev_nbr
828 AND NOT EXISTS
829 ( SELECT 'X'
830 FROM oe_order_lines l
831 WHERE l.top_model_line_id = p_model_line_rec.line_id
832 AND l.component_code = c.component_code
833 AND l.open_flag = 'Y')
834 ORDER BY c.component_code;
835
836 l_line_rec OE_ORDER_PUB.Line_Rec_Type;
837 l_class_line_rec OE_ORDER_PUB.Line_Rec_Type;
838 l_line_count NUMBER;
839 l_class_line_count NUMBER;
840 l_return_status VARCHAR2(1);
841 l_concatenated_segments VARCHAR2(163);
842 l_default_child_line BOOLEAN := TRUE;
843 --
844 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
845 --
846 BEGIN
847 Print_Time('Handle_Inserts start time');
848
849 l_line_count := nvl(p_x_line_tbl.LAST, 0);
850 l_class_line_count := nvl(p_x_class_line_tbl.LAST, 0);
851
852 l_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
853 l_line_rec.operation := OE_GLOBALS.G_OPR_CREATE;
854 l_line_rec.header_id := p_model_line_rec.header_id;
855 l_line_rec.item_identifier_type := 'INT';
856
857
858 FOR config_rec in config_ins_cursor
859 LOOP
860
861 -- Get the concatanted segment value to be stored in
862 -- order lines at ordered_item
863
864 BEGIN
865 SELECT concatenated_segments
866 INTO l_concatenated_segments
867 FROM MTL_SYSTEM_ITEMS_KFV
868 WHERE inventory_item_id = config_rec.inventory_item_id
869 AND organization_id = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID');
870 EXCEPTION
871 WHEN NO_DATA_FOUND THEN
872 RAISE FND_API.G_EXC_ERROR;
873 END;
874
875 IF l_debug_level > 0 THEN
876 oe_debug_pub.add('CONCAT SEGMENT IS: ' || L_CONCATENATED_SEGMENTS ,3);
877 oe_debug_pub.add('INSERTING COMP CODE: ' || CONFIG_REC.COMPONENT_CODE,1);
878 END IF;
879
880 IF p_direct_save AND
881 (config_rec.bom_item_type = 2 OR config_rec.bom_item_type = 1)
882 THEN
883
884 IF l_default_child_line THEN
885
886 oe_debug_pub.add( 'DIRECT SAVE IS ON' , 3 ) ;
887
888 OE_Config_Util.Default_Child_Line
889 ( p_parent_line_rec => p_model_line_rec
890 ,p_x_child_line_rec => l_class_line_rec
891 ,p_direct_save => p_direct_save
892 ,x_return_status => l_return_status);
893
894 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
895 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
896 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
897 RAISE FND_API.G_EXC_ERROR;
898 END IF;
899
900 l_default_child_line := FALSE;
901
905 l_class_line_rec.order_quantity_uom := config_rec.uom_code;
902 END IF; -- end if l_default_child_line
903
904 l_class_line_rec.ordered_quantity := config_rec.quantity;
906 l_class_line_rec.component_sequence_id
907 := config_rec.component_sequence_id;
908 l_class_line_rec.component_code := config_rec.component_code;
909 l_class_line_rec.sort_order := config_rec.bom_sort_order;
910 l_class_line_rec.inventory_item_id := config_rec.inventory_item_id;
911 l_class_line_rec.ordered_item := l_concatenated_segments;
912
913 SELECT OE_ORDER_LINES_S.NEXTVAL
914 INTO l_class_line_rec.line_id
915 FROM DUAL;
916
917 l_class_line_rec.pricing_quantity_uom
918 := l_class_line_rec.order_quantity_uom;
919 l_class_line_rec.pricing_quantity
920 := l_class_line_rec.ordered_quantity;
921
922 l_class_line_count := l_class_line_count+1;
923 p_x_class_line_tbl(l_class_line_count) := l_class_line_rec;
924
925 ELSE
926
927 l_line_rec.ordered_quantity := config_rec.quantity;
928 l_line_rec.order_quantity_uom := config_rec.uom_code;
929 l_line_rec.component_sequence_id := config_rec.component_sequence_id;
930 l_line_rec.top_model_line_id := p_model_line_rec.line_id;
931 l_line_rec.component_code := config_rec.component_code;
932 l_line_rec.sort_order := config_rec.bom_sort_order;
933 l_line_rec.inventory_item_id := config_rec.inventory_item_id;
934 l_line_rec.ordered_item := l_concatenated_segments;
935
936 IF config_rec.bom_item_type = 1 OR
937 config_rec.bom_item_type = 2 THEN
938 l_line_rec.item_type_code := OE_GLOBALS.G_ITEM_CLASS;
939 ELSE
940 l_line_rec.item_type_code := null;
941 END IF;
942
943 l_line_count := l_line_count+1;
944 p_x_line_tbl(l_line_count) := l_line_rec;
945 END IF;
946
947 END LOOP;
948
949 Print_Time('Handle_Inserts_Old end time');
950 EXCEPTION
951 WHEN OTHERS THEN
952 IF l_debug_level > 0 THEN
953 oe_debug_pub.add( 'EXCEPTION IN HANDLE_INSERTS_OLD'|| SQLERRM , 1 ) ;
954 END IF;
955 RAISE;
956 END Handle_Inserts_Old;
957
958
959 /*-----------------------------------------------------------
960 PROCEDURE Handle_Updates
961
962 If quantity of a component is different in oe_order_lines and
963 cz_config_details_v, we need to update that component.
964
965 for config UI only: if there is a constraint on qty change,
966 should we pass a hardcoded reason/comment, or should we fail?
967 we should fail.
968
969 MACD: Compare the line type in OM and CZ and if different
970 select into cursor. Also, set the child line record's line_type to
971 the selected value from cz
972 ------------------------------------------------------------*/
973
974 PROCEDURE Handle_Updates
975 ( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
976 ,p_config_hdr_id IN NUMBER
977 ,p_config_rev_nbr IN NUMBER
978 ,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
979 ,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
980 ,p_direct_save IN BOOLEAN := FALSE
981 ,p_ui_flag IN VARCHAR2)
982 IS
983 CURSOR config_upd_cursor IS
984 SELECT distinct
985 l.line_id
986 ,c.component_code
987 ,c.quantity
988 ,l.ordered_quantity
989 ,l.item_type_code
990 ,c.line_type
991 ,c.bom_sort_order
992 FROM CZ_CONFIG_DETAILS_V c, oe_order_lines l
993 WHERE c.config_hdr_id = p_config_hdr_id
994 AND c.config_rev_nbr = p_config_rev_nbr
995 AND (c.quantity <> l.ordered_quantity OR
996 c.line_type <> l.line_type_id OR
997 c.bom_sort_order <> l.sort_order)
998 AND l.top_model_line_id = p_model_line_rec.line_id
999 AND l.component_code = c.component_code
1000 AND l.configuration_id = c.config_item_id
1001 AND l.open_flag = 'Y';
1002
1003 l_line_rec OE_ORDER_PUB.Line_Rec_Type;
1004 l_class_line_rec OE_ORDER_PUB.Line_Rec_Type;
1005 l_line_count NUMBER;
1006 l_class_line_count NUMBER;
1007 --
1008 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1009 --
1010 BEGIN
1011 Print_Time('Handle_Updates start time');
1012
1013 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110508'
1014 THEN
1015 IF l_debug_level > 0 THEN
1016 oe_debug_pub.add( 'PACK H NEW LOGIC' , 1 ) ;
1017 END IF;
1018 ELSE
1019 Handle_Updates_Old
1020 (p_model_line_rec => p_model_line_rec
1021 ,p_config_hdr_id => p_config_hdr_id
1022 ,p_config_rev_nbr => p_config_rev_nbr
1023 ,p_x_line_tbl => p_x_line_tbl
1024 ,p_x_class_line_tbl => p_x_class_line_tbl
1025 ,p_direct_save => FALSE
1026 ,p_ui_flag => p_ui_flag);
1027
1028 IF l_debug_level > 0 THEN
1029 oe_debug_pub.add( 'RETURNING..' , 1 ) ;
1030 END IF;
1031
1035 l_line_count := nvl(p_x_line_tbl.LAST, 0);
1032 RETURN;
1033 END IF;
1034
1036 l_class_line_count := nvl(p_x_class_line_tbl.LAST, 0);
1037
1038 l_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
1039 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
1040 l_line_rec.top_model_line_id := p_model_line_rec.line_id;
1041
1042
1043 FOR config_rec in config_upd_cursor
1044 LOOP
1045
1046 IF l_debug_level > 0 THEN
1047 oe_debug_pub.add
1048 ('OE QTY: '||CONFIG_REC.ORDERED_QUANTITY||'CZ QTY '||CONFIG_REC.QUANTITY ,1);
1049 oe_debug_pub.add('UPDATING COMP CODE: ' || CONFIG_REC.COMPONENT_CODE ,1);
1050 oe_debug_pub.add( 'I LINE ID:' || CONFIG_REC.LINE_ID , 1 ) ;
1051 END IF;
1052
1053 l_line_rec.line_id := config_rec.line_id;
1054 l_line_rec.ordered_quantity := config_rec.quantity;
1055 l_line_rec.sort_order := config_rec.bom_sort_order;
1056
1057 IF config_rec.line_type IS NOT NULL AND
1058 config_rec.line_type <> FND_API.G_MISS_NUM THEN
1059
1060 IF l_debug_level > 0 THEN
1061 OE_DEBUG_PUB.Add('MACD Logic,cz Line_type:'||config_rec.line_type,3);
1062 END IF;
1063 l_line_rec.line_type_id := config_rec.line_type;
1064 ELSE
1065 l_line_rec.line_type_id := FND_API.G_MISS_NUM;
1066 END IF;
1067
1068
1069 IF p_ui_flag = 'N' THEN
1070 l_line_rec.change_reason := 'SYSTEM';
1071 l_line_rec.change_comments := 'Change Cascaded';
1072 ELSE
1073 l_line_rec.change_reason := 'CONFIGURATOR';
1074 l_line_rec.change_comments := 'Changes in Configurator Window';
1075 END IF;
1076
1077 IF p_direct_save AND config_rec.item_type_code = OE_GLOBALS.G_ITEM_CLASS
1078 THEN
1079 l_class_line_count := l_class_line_count+1;
1080 p_x_class_line_tbl(l_class_line_count) := l_line_rec;
1081 ELSE
1082 l_line_count := l_line_count+1;
1083 p_x_line_tbl(l_line_count) := l_line_rec;
1084 END IF;
1085
1086 END LOOP;
1087
1088 Print_Time('Handle_Updates end time');
1089 EXCEPTION
1090 WHEN OTHERS THEN
1091 IF l_debug_level > 0 THEN
1092 oe_debug_pub.add( 'EXCEPTION IN HANDLE_UPDATES'|| SQLERRM , 1 ) ;
1093 END IF;
1094 RAISE;
1095 END Handle_Updates;
1096
1097
1098 /*-----------------------------------------------------------
1099 PROCEDURE Handle_Updates_Old
1100
1101 If quantity of a component is different in oe_order_lines and
1102 cz_config_details_v, we need to update that component.
1103
1104 for config UI only: if there is a constraint on qty change,
1105 should we pass a hardcoded reason/comment, or should we fail?
1106 we should fail.
1107 ------------------------------------------------------------*/
1108
1109 PROCEDURE Handle_Updates_Old
1110 ( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
1111 ,p_config_hdr_id IN NUMBER
1112 ,p_config_rev_nbr IN NUMBER
1113 ,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
1114 ,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
1115 ,p_direct_save IN BOOLEAN := FALSE
1116 ,p_ui_flag IN VARCHAR2)
1117 IS
1118 CURSOR config_upd_cursor IS
1119 SELECT distinct
1120 l.line_id
1121 ,c.component_code
1122 ,c.quantity
1123 ,l.ordered_quantity
1124 ,l.item_type_code
1125 FROM CZ_CONFIG_DETAILS_V c, oe_order_lines l
1126 WHERE c.config_hdr_id = p_config_hdr_id
1127 AND c.config_rev_nbr = p_config_rev_nbr
1128 AND c.quantity <> l.ordered_quantity
1129 AND l.top_model_line_id = p_model_line_rec.line_id
1130 AND l.component_code = c.component_code
1131 AND l.open_flag = 'Y';
1132
1133 l_line_rec OE_ORDER_PUB.Line_Rec_Type;
1134 l_class_line_rec OE_ORDER_PUB.Line_Rec_Type;
1135 l_line_count NUMBER;
1136 l_class_line_count NUMBER;
1137 --
1138 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1139 --
1140 BEGIN
1141 Print_Time('Handle_Updates_Old start time');
1142
1143 l_line_count := nvl(p_x_line_tbl.LAST, 0);
1144 l_class_line_count := nvl(p_x_class_line_tbl.LAST, 0);
1145
1146 l_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
1147 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
1148 l_line_rec.top_model_line_id := p_model_line_rec.line_id;
1149
1150
1151 FOR config_rec in config_upd_cursor
1152 LOOP
1153
1154 IF l_debug_level > 0 THEN
1155 oe_debug_pub.add('OE QTY: '||CONFIG_REC.QUANTITY||'CZ QTY '
1156 ||CONFIG_REC.QUANTITY , 1 ) ;
1157 oe_debug_pub.add('UPDATING COMP CODE: '||CONFIG_REC.COMPONENT_CODE ,1);
1158 oe_debug_pub.add( 'I LINE ID:' || CONFIG_REC.LINE_ID , 1 ) ;
1159 END IF;
1160
1161 l_line_rec.line_id := config_rec.line_id;
1162 l_line_rec.ordered_quantity := config_rec.quantity;
1163
1164 IF p_ui_flag = 'N' THEN
1165 l_line_rec.change_reason := 'SYSTEM';
1166 l_line_rec.change_comments := 'Change Cascaded';
1167 ELSE
1171
1168 l_line_rec.change_reason := 'CONFIGURATOR';
1169 l_line_rec.change_comments := 'Changes in Configurator Window';
1170 END IF;
1172 IF p_direct_save AND config_rec.item_type_code = OE_GLOBALS.G_ITEM_CLASS
1173 THEN
1174 l_class_line_count := l_class_line_count+1;
1175 p_x_class_line_tbl(l_class_line_count) := l_line_rec;
1176 ELSE
1177 l_line_count := l_line_count+1;
1178 p_x_line_tbl(l_line_count) := l_line_rec;
1179 END IF;
1180
1181 END LOOP;
1182
1183 Print_Time('Handle_Updates_Old end time');
1184 EXCEPTION
1185 WHEN OTHERS THEN
1186 IF l_debug_level > 0 THEN
1187 oe_debug_pub.add( 'EXCEPTION IN HANDLE_UPDATES_OLD'|| SQLERRM , 1 ) ;
1188 END IF;
1189 RAISE;
1190 END Handle_Updates_Old;
1191
1192
1193 /*---------------------------------------------------------
1194 PROCEDURE Handle_Deletes
1195
1196 If a component exists in oe_order_lines, but does not exist
1197 in cz_config_details_v, we need to delete that component.
1198
1199 Change Record:
1200
1201 bug 1939531: to not call check_if_cancellation
1202 if first configuration is yet getting saved for the first
1203 time and link_to_line_id is not yet populated.
1204
1205 do not call the check_if_cancellation id p_ui_flag is 'Y'.
1206 This is because,
1207 1) you can not enter reason and comment in configurator, so
1208 if cancellation constraint is on, delete will fail.
1209 2) configuraor will take care of cascading change to
1210 child and parent lines, so we do not have to check in
1211 oe_order_lines.
1212 ----------------------------------------------------------*/
1213
1214 PROCEDURE Handle_Deletes
1215 ( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
1216 ,p_config_hdr_id IN NUMBER
1217 ,p_config_rev_nbr IN NUMBER
1218 ,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
1219 ,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
1220 ,p_direct_save IN BOOLEAN := FALSE
1221 ,p_ui_flag IN VARCHAR2)
1222 IS
1223 CURSOR config_del_cursor IS
1224 SELECT l.line_id, l.item_type_code, l.link_to_line_id,
1225 l.component_code, nvl(l.cancelled_flag, 'N') cancelled_flag
1226 FROM oe_order_lines l
1227 WHERE l.top_model_line_id = p_model_line_rec.line_id
1228 AND (l.item_type_code = OE_GLOBALS.G_ITEM_CLASS OR
1229 l.item_type_code = OE_GLOBALS.G_ITEM_OPTION OR
1230 l.item_type_code = OE_GLOBALS.G_ITEM_KIT)
1231 AND l.open_flag = 'Y'
1232 AND
1233 (NOT EXISTS
1234 ( SELECT 'X'
1235 FROM CZ_CONFIG_DETAILS_V c
1236 WHERE c.component_code = l.component_code
1237 AND c.config_item_id = l.configuration_id
1238 AND c.config_hdr_id = p_config_hdr_id
1239 AND c.config_rev_nbr = p_config_rev_nbr )
1240 OR EXISTS
1241 ( SELECT 'X'
1242 FROM CZ_CONFIG_DETAILS_V c
1243 WHERE c.component_code = l.component_code
1244 AND c.config_item_id = l.configuration_id
1245 AND c.config_hdr_id = p_config_hdr_id
1246 AND c.config_rev_nbr = p_config_rev_nbr
1247 AND c.quantity = 0));
1248
1249 l_line_rec OE_ORDER_PUB.Line_Rec_Type;
1250 l_class_line_rec OE_ORDER_PUB.Line_Rec_Type;
1251 l_line_count NUMBER;
1252 l_class_line_count NUMBER;
1253 l_cancellation BOOLEAN;
1254 l_change_reason VARCHAR2(30);
1255 l_change_comments VARCHAR2(30);
1256 l_qty NUMBER;
1257 --
1258 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1259 --
1260 BEGIN
1261 Print_Time('Handle_Deletes start time');
1262
1263 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110508'
1264 THEN
1265 IF l_debug_level > 0 THEN
1266 oe_debug_pub.add( 'PACK H NEW LOGIC' , 1 ) ;
1267 END IF;
1268 ELSE
1269 Handle_Deletes_Old
1270 ( p_model_line_rec => p_model_line_rec
1271 ,p_config_hdr_id => p_config_hdr_id
1272 ,p_config_rev_nbr => p_config_rev_nbr
1273 ,p_x_line_tbl => p_x_line_tbl
1274 ,p_x_class_line_tbl => p_x_class_line_tbl
1275 ,p_direct_save => FALSE
1276 ,p_ui_flag => p_ui_flag);
1277
1278 IF l_debug_level > 0 THEN
1279 oe_debug_pub.add( 'RETURNING..' , 1 ) ;
1280 END IF;
1281
1282 RETURN;
1283 END IF;
1284
1285 l_line_count := nvl(p_x_line_tbl.LAST, 0);
1286 l_class_line_count := nvl(p_x_class_line_tbl.LAST, 0);
1287 l_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
1288
1289 IF l_debug_level > 0 THEN
1290 oe_debug_pub.add( 'CONFIG_DEL CURSOR OPENED' , 1 ) ;
1291 END IF;
1292
1293 FOR config_rec in config_del_cursor
1294 LOOP
1295
1296 l_cancellation := FALSE;
1297
1298 IF (config_rec.link_to_line_id is NOT NULL AND
1299 p_ui_flag = 'N') OR
1300 p_ui_flag = 'Y' THEN
1301
1302 IF l_debug_level > 0 THEN
1303 oe_debug_pub.add('CALLING CHECK_IF.. '||CONFIG_REC.COMPONENT_CODE,3);
1304 END IF;
1305
1306 IF p_ui_flag = 'Y' THEN
1310 l_change_comments := 'Change Cascaded';
1307 l_change_comments := 'Changes in Configurator Window';
1308 l_change_reason := 'CONFIGURATOR';
1309 ELSE
1311 l_change_reason := 'SYSTEM';
1312 END IF;
1313
1314 Is_Cancel_OR_Delete
1315 ( p_line_id => config_rec.line_id
1316 ,p_change_reason => l_change_reason
1317 ,p_change_comments => l_change_comments
1318 ,x_cancellation => l_cancellation
1319 ,x_line_rec => l_line_rec);
1320
1321 END IF;
1322
1323 IF NOT l_cancellation THEN
1324
1325 IF l_debug_level > 0 THEN
1326 oe_debug_pub.add( 'DELETING LINE : ' || CONFIG_REC.LINE_ID ) ;
1327 END IF;
1328
1329 l_line_rec.line_id := config_rec.line_id;
1330 l_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
1331
1332 IF l_debug_level > 0 THEN
1333 oe_debug_pub.add('LINE_ID TO BE DELETED: ' || CONFIG_REC.LINE_ID ,1);
1334 END IF;
1335
1336 IF p_direct_save AND
1337 config_rec.item_type_code = OE_GLOBALS.G_ITEM_CLASS
1338 THEN
1339 l_class_line_count := l_class_line_count+1;
1340 p_x_class_line_tbl(l_class_line_count) := l_line_rec;
1341 ELSE
1342 l_line_count := l_line_count+1;
1343 p_x_line_tbl(l_line_count) := l_line_rec;
1344 END IF;
1345
1346 ELSE
1347 IF l_debug_level > 0 THEN
1348 oe_debug_pub.add( 'YES CANCELLATION' , 3 ) ;
1349 END IF;
1350
1351 IF config_rec.cancelled_flag = 'N' THEN
1352
1353 l_line_rec.line_id := config_rec.line_id;
1354 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
1355 l_line_rec.ordered_quantity := 0;
1356
1357 IF p_direct_save AND
1358 config_rec.item_type_code =OE_GLOBALS.G_ITEM_CLASS
1359 THEN
1360 l_class_line_count := l_class_line_count+1;
1361 p_x_class_line_tbl(l_class_line_count) := l_line_rec;
1362 ELSE
1363 l_line_count := l_line_count+1;
1364 p_x_line_tbl(l_line_count) := l_line_rec;
1365 END IF;
1366
1367 END IF;
1368 END IF;
1369
1370 END LOOP;
1371
1372 Print_Time('Handle_Deletes end time');
1373 EXCEPTION
1374 WHEN OTHERS THEN
1375 IF l_debug_level > 0 THEN
1376 oe_debug_pub.add( 'EXCEPTION IN HANDLE_DELETES'|| SQLERRM , 1 ) ;
1377 END IF;
1378 RAISE;
1379 END Handle_Deletes;
1380
1381
1382 /*---------------------------------------------------------
1383 PROCEDURE Handle_Deletes_Old
1384
1385 If a component exists in oe_order_lines, but does not exist
1386 in cz_config_details_v, we need to delete that component.
1387
1388 Change Record:
1389
1390 bug 1939531: to not call check_if_cancellation
1391 if first configuration is yet getting saved for the first
1392 time and link_to_line_id is not yet populated.
1393
1394 do not call the check_if_cancellation id p_ui_flag is 'Y'.
1395 This is because,
1396 1) you can not enter reason and comment in configurator, so
1397 if cancellation constraint is on, delete will fail.
1398 2) configuraor will take care of cascading change to
1399 child and parent lines, so we do not have to check in
1400 oe_order_lines.
1401 ----------------------------------------------------------*/
1402
1403 PROCEDURE Handle_Deletes_Old
1404 ( p_model_line_rec IN OE_Order_Pub.Line_rec_Type
1405 ,p_config_hdr_id IN NUMBER
1406 ,p_config_rev_nbr IN NUMBER
1407 ,p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
1408 ,p_x_class_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_Type
1409 ,p_direct_save IN BOOLEAN := FALSE
1410 ,p_ui_flag IN VARCHAR2)
1411 IS
1412 CURSOR config_del_cursor IS
1413 SELECT l.line_id, l.item_type_code, l.link_to_line_id,
1414 l.component_code
1415 FROM oe_order_lines l
1416 WHERE l.top_model_line_id = p_model_line_rec.line_id
1417 AND (l.item_type_code = OE_GLOBALS.G_ITEM_CLASS OR
1418 l.item_type_code = OE_GLOBALS.G_ITEM_OPTION OR
1419 l.item_type_code = OE_GLOBALS.G_ITEM_KIT)
1420 AND l.open_flag = 'Y'
1421 AND
1422 (NOT EXISTS
1423 ( SELECT 'X'
1424 FROM CZ_CONFIG_DETAILS_V c
1425 WHERE c.component_code = l.component_code
1426 AND c.config_hdr_id = p_config_hdr_id
1427 AND c.config_rev_nbr = p_config_rev_nbr )
1428 OR EXISTS
1429 ( SELECT 'X'
1430 FROM CZ_CONFIG_DETAILS_V c
1431 WHERE c.component_code = l.component_code
1432 AND c.config_hdr_id = p_config_hdr_id
1433 AND c.config_rev_nbr = p_config_rev_nbr
1434 AND c.quantity = 0));
1435
1436 l_line_rec OE_ORDER_PUB.Line_Rec_Type;
1437 l_class_line_rec OE_ORDER_PUB.Line_Rec_Type;
1438 l_line_count NUMBER;
1439 l_class_line_count NUMBER;
1440 l_cancellation BOOLEAN;
1441 l_qty NUMBER;
1442 --
1446 Print_Time('Handle_Deletes_Old start time');
1443 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1444 --
1445 BEGIN
1447
1448 l_line_count := nvl(p_x_line_tbl.LAST, 0);
1449 l_class_line_count := nvl(p_x_class_line_tbl.LAST, 0);
1450 l_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
1451
1452 IF l_debug_level > 0 THEN
1453 oe_debug_pub.add( 'CONFIG_DEL CURSOR OPENED' , 1 ) ;
1454 END IF;
1455
1456 FOR config_rec in config_del_cursor
1457 LOOP
1458
1459 l_cancellation := FALSE;
1460
1461 IF config_rec.link_to_line_id is NOT NULL AND
1462 p_ui_flag = 'N' THEN
1463
1464 IF l_debug_level > 0 THEN
1465 oe_debug_pub.add('CALLING CHECK_IF.. '
1466 || CONFIG_REC.COMPONENT_CODE , 3 ) ;
1467 END IF;
1468
1469 Check_If_cancellation
1470 ( p_line_id => config_rec.line_id
1471 ,p_top_model_line_id => p_model_line_rec.line_id
1472 ,p_item_type_code => config_rec.item_type_code
1473 ,x_cancellation => l_cancellation
1474 ,x_current_quantity => l_qty);
1475
1476 ELSIF p_ui_flag = 'Y' THEN
1477
1478 Is_Cancel_OR_Delete
1479 ( p_line_id => config_rec.line_id
1480 ,p_change_reason => 'CONFIGURATOR'
1481 ,p_change_comments => 'Changes in Configurator Window'
1482 ,x_cancellation => l_cancellation
1483 ,x_line_rec => l_line_rec);
1484
1485 END IF;
1486
1487 IF NOT l_cancellation THEN
1488
1489 IF l_debug_level > 0 THEN
1490 oe_debug_pub.add( 'DELETING LINE : ' || CONFIG_REC.LINE_ID ) ;
1491 END IF;
1492
1493 l_line_rec.line_id := config_rec.line_id;
1494 l_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
1495
1496 IF l_debug_level > 0 THEN
1497 oe_debug_pub.add('LINE_ID TO BE DELETED: '||CONFIG_REC.LINE_ID,1);
1498 END IF;
1499
1500 IF p_direct_save AND
1501 config_rec.item_type_code = OE_GLOBALS.G_ITEM_CLASS
1502 THEN
1503 l_class_line_count := l_class_line_count+1;
1504 p_x_class_line_tbl(l_class_line_count) := l_line_rec;
1505 ELSE
1506 l_line_count := l_line_count+1;
1507 p_x_line_tbl(l_line_count) := l_line_rec;
1508 END IF;
1509
1510 ELSE
1511 IF l_debug_level > 0 THEN
1512 oe_debug_pub.add( 'YES CANCELLATION' , 3 ) ;
1513 END IF;
1514
1515 IF l_qty <> 0 OR
1516 p_ui_flag = 'Y' THEN
1517 l_line_rec.line_id := config_rec.line_id;
1518 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
1519 l_line_rec.ordered_quantity := 0;
1520 l_line_rec.change_reason := 'SYSTEM';
1521 l_line_rec.change_comments := 'Change Cascaded';
1522
1523 IF p_direct_save AND
1524 config_rec.item_type_code =OE_GLOBALS.G_ITEM_CLASS
1525 THEN
1526 l_class_line_count := l_class_line_count+1;
1527 p_x_class_line_tbl(l_class_line_count) := l_line_rec;
1528 ELSE
1529 l_line_count := l_line_count+1;
1530 p_x_line_tbl(l_line_count) := l_line_rec;
1531 END IF;
1532
1533 END IF;
1534 END IF;
1535
1536 END LOOP;
1537
1538 Print_Time('Handle_Deletes_Old end time');
1539 EXCEPTION
1540 WHEN OTHERS THEN
1541 IF l_debug_level > 0 THEN
1542 oe_debug_pub.add( 'EXCEPTION IN HANDLE_DELETES_OLD'|| SQLERRM , 1 ) ;
1543 END IF;
1544 RAISE;
1545 END Handle_Deletes_Old;
1546
1547
1548 /*--------------------------------------------------------------------
1549 PROCEDURE Check_If_cancellation
1550 This procedure is used to see if the deletion of the option/class
1551 is actually a complete cancellation. If so, we will not delete
1552 the lines from oe_order_lines and they will be closed instead.
1553
1554 Change Record:
1555 bug 2191666: the sqls and logic modified when a class gets
1556 cancelled as a result of cascading.
1557 ---------------------------------------------------------------------*/
1558 PROCEDURE Check_If_cancellation
1559 ( p_line_id IN NUMBER
1560 ,p_top_model_line_id IN NUMBER
1561 ,p_item_type_code IN VARCHAR2
1562 ,x_cancellation OUT NOCOPY /* file.sql.39 change */ BOOLEAN
1563 ,x_current_quantity OUT NOCOPY /* file.sql.39 change */ NUMBER)
1564 IS
1565 l_open_flag VARCHAR2(1);
1566 l_line_id NUMBER;
1567 l_parent_line_id NUMBER;
1568 l_ordered_quantity NUMBER := FND_API.G_MISS_NUM;
1569 l_component_code VARCHAR2(1000);
1570 --
1571 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1572 --
1573 BEGIN
1574
1575 IF l_debug_level > 0 THEN
1576 oe_debug_pub.add('ENTERING CHECK_IF_CANCELLATION ' || P_LINE_ID ,1);
1577 END IF;
1578
1579 l_line_id := p_line_id;
1580 l_parent_line_id := p_line_id;
1581
1582
1583 BEGIN
1584 SELECT ordered_quantity, open_flag, component_code
1585 INTO l_ordered_quantity, l_open_flag, l_component_code
1586 FROM oe_order_lines
1587 WHERE line_id = l_line_id;
1591 IF l_debug_level > 0 THEN
1588
1589 IF l_ordered_quantity = 0 THEN
1590 IF l_open_flag = 'N' THEN
1592 oe_debug_pub.add('THIS WAS A COMPLETE CANCELLATION'|| L_LINE_ID,3);
1593 END IF;
1594 x_cancellation := TRUE;
1595 x_current_quantity := l_ordered_quantity;
1596 RETURN;
1597 ELSE
1598 IF l_debug_level > 0 THEN
1599 oe_debug_pub.add('NOT COMPLETE CANCEL, SO DELETE'|| L_LINE_ID ,3);
1600 END IF;
1601 x_cancellation := FALSE;
1602 x_current_quantity := l_ordered_quantity;
1603 RETURN;
1604 END IF;
1605 END IF;
1606
1607 EXCEPTION
1608 WHEN no_data_found THEN
1609 IF l_debug_level > 0 THEN
1610 oe_debug_pub.add( 'LINE NOT IN DB'|| L_LINE_ID , 1 ) ;
1611 END IF;
1612 WHEN OTHERS THEN
1613 IF l_debug_level > 0 THEN
1614 oe_debug_pub.add( 'OTHERS '|| L_LINE_ID || SQLERRM , 1 ) ;
1615 END IF;
1616 RAISE;
1617 END;
1618
1619 -- if we came here, that is because of cascading effect not
1620 -- because the line itself is set to qty = 0.
1621
1622 l_open_flag := 'Y';
1623
1624 IF l_debug_level > 0 THEN
1625 oe_debug_pub.add('CHECK IF CHANGE CASCADED DOWN '||P_ITEM_TYPE_CODE ,3);
1626 END IF;
1627
1628 WHILE l_open_flag = 'Y' AND
1629 l_parent_line_id <> p_top_model_line_id
1630 LOOP
1631
1632 IF l_debug_level > 0 THEN
1633 oe_debug_pub.add( 'PARENT LINE ID: ' || L_PARENT_LINE_ID
1634 || 'LINE ID: '|| L_LINE_ID , 3 ) ;
1635 END IF;
1636
1637 SELECT link_to_line_id
1638 INTO l_parent_line_id
1639 FROM oe_order_lines
1640 WHERE line_id = l_line_id;
1641
1642 IF l_debug_level > 0 THEN
1643 oe_debug_pub.add( 'CAME HERE '|| L_PARENT_LINE_ID , 3 ) ;
1644 END IF;
1645
1646 l_line_id := l_parent_line_id;
1647 BEGIN
1648
1649 SELECT open_flag
1650 INTO l_open_flag
1651 FROM oe_order_lines
1652 WHERE line_id = l_parent_line_id;
1653
1654 EXCEPTION
1655 WHEN TOO_MANY_ROWS THEN
1656 IF l_debug_level > 0 THEN
1657 oe_debug_pub.add( 'OPTION: TOO MANY ROWS IN CHECK CANCEL' , 1 ) ;
1658 END IF;
1659 RAISE;
1660
1661 WHEN NO_DATA_FOUND THEN
1662 IF l_debug_level > 0 THEN
1663 oe_debug_pub.add( 'OPTIONS PARENT NOT FOUND'|| L_OPEN_FLAG , 1 ) ;
1664 END IF;
1665 l_parent_line_id := p_top_model_line_id;
1666 END;
1667
1668 IF l_debug_level > 0 THEN
1669 oe_debug_pub.add( 'OPEN FLAG OF PARENT: '|| L_OPEN_FLAG , 3 ) ;
1670 END IF;
1671
1672 END LOOP;
1673
1674 IF p_item_type_code = OE_GLOBALS.G_ITEM_CLASS AND
1675 l_open_flag = 'Y'
1676 THEN
1677
1678 IF l_debug_level > 0 THEN
1679 oe_debug_pub.add('CHECK IF CHANGE CASCADED UPWARDS TO CLASS/KIT' ,1);
1680 END IF;
1681
1682 -- this will happen only if the last option under this class
1683 -- is set to qty of 0.
1684 BEGIN
1685
1686 SELECT count(*)
1687 INTO l_line_id
1688 FROM oe_order_lines
1689 WHERE top_model_line_id = p_top_model_line_id
1690 AND link_to_line_id = p_line_id
1691 AND open_flag = 'Y'
1692 AND item_type_code IN ('CLASS', 'OPTION', 'KIT');
1693
1694 SELECT count(*)
1695 INTO l_parent_line_id
1696 FROM oe_order_lines
1697 WHERE top_model_line_id = p_top_model_line_id
1698 AND link_to_line_id = p_line_id
1699 AND item_type_code IN ('CLASS', 'OPTION', 'KIT');
1700
1701 IF l_debug_level > 0 THEN
1702 oe_debug_pub.add( 'NO. OF OPEN CHILD: '|| L_LINE_ID , 3 ) ;
1703 oe_debug_pub.add( 'NO. OF CHILD: '|| L_PARENT_LINE_ID , 3 ) ;
1704 END IF;
1705
1706 IF l_parent_line_id = 0 THEN
1707 IF l_debug_level > 0 THEN
1708 oe_debug_pub.add( 'IT IS A DELETE' , 1 ) ;
1709 END IF;
1710 l_open_flag := 'Y';
1711 ELSIF l_line_id = 0 THEN
1712 l_open_flag := 'N';
1713 IF l_debug_level > 0 THEN
1714 oe_debug_pub.add( 'IT IS A CANCELLATION' , 1 ) ;
1715 END IF;
1716 ELSE
1717
1718 BEGIN
1719 SELECT count(*)
1720 INTO l_line_id
1721 FROM oe_order_lines
1722 WHERE top_model_line_id = p_top_model_line_id
1723 AND component_code like (l_component_code || '%')
1724 AND open_flag = 'N'
1725 AND cancelled_flag = 'Y'
1726 AND item_type_code IN ('CLASS', 'OPTION', 'KIT');
1727
1728 EXCEPTION
1729 WHEN NO_DATA_FOUND THEN
1730 IF l_debug_level > 0 THEN
1731 oe_debug_pub.add( 'NO CANELLED LINES' , 2 ) ;
1732 END IF;
1733 END;
1734
1735 IF l_line_id > 0 THEN
1736 l_open_flag := 'N';
1737 END IF;
1738
1739 END IF;
1740
1741 EXCEPTION
1742 WHEN TOO_MANY_ROWS THEN
1743 IF l_debug_level > 0 THEN
1747
1744 oe_debug_pub.add( 'TOO MANY ROWS IN CHECK CANCELLATION' , 1 ) ;
1745 END IF;
1746 RAISE;
1748 WHEN OTHERS THEN
1749 IF l_debug_level > 0 THEN
1750 oe_debug_pub.add( 'OTHERS CLASS/KIT'|| L_LINE_ID || SQLERRM , 1 ) ;
1751 END IF;
1752 RAISE;
1753 END;
1754
1755 END IF;
1756
1757 x_current_quantity := l_ordered_quantity;
1758
1759 IF l_open_flag = 'N' THEN
1760 x_cancellation := TRUE;
1761 ELSE
1762 x_cancellation := FALSE;
1763 END IF;
1764
1765 IF l_debug_level > 0 THEN
1766 oe_debug_pub.add( 'LEAVING CHECK_IF_CANCELLATION ' || L_OPEN_FLAG , 1 ) ;
1767 END IF;
1768
1769 EXCEPTION
1770 WHEN OTHERS THEN
1771 IF l_debug_level > 0 THEN
1772 oe_debug_pub.add( 'EXCEPTION IN CHECK_IF_CANCELLATION'|| SQLERRM , 1 ) ;
1773 END IF;
1774 RAISE;
1775 END Check_If_cancellation;
1776
1777
1778 /*------------------------------------------------------------
1779 PROCEDURE: Call_Process_Order
1780 helper to call process_order
1781
1782 The direct operation of update and delete should not
1783 happen, no records should be in class_tbl for update
1784 and delete, since a a value of FALSE is passes to
1785 handle_updates and handle_deletes.
1786
1787 We set a bunch of globals before and after call to lines.
1788 in a delayed request, process order calls batch validate.
1789 after batch validation we might insert/update/delete option
1790 &/ classes.
1791 because of this change there should not be again a delayed request
1792 for batch validation. hence a global OECFG_VALIDATE_CONFIG varchar2(1)
1793 will be reset and set before and after a call to process_order resp.
1794 also we do not wnat to cascade here,
1795 set OE_CONFIG_UTIL.CASCADE_CHANGES_FLAG := 'Y' before the call
1796 oe_config_ui_used: idicates, options window/configurator call.
1797 There are some other flags also.
1798
1799 Change Record:
1800 added Insert_into_Set call
1801 If the parent is in fulfillment set then push the child
1802 into same fulfillment set. This will handle cases that
1803 are not calling Lines procedure and doing direct inserts
1804 into the the table.
1805
1806 MACD: Modified the control record to pass security
1807 ---------------------------------------------------------------*/
1808
1809 PROCEDURE Call_Process_Order
1810 ( p_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
1811 ,p_class_line_tbl IN OE_Order_Pub.Line_Tbl_Type
1812 := OE_ORDER_PUB.G_MISS_LINE_TBL
1813 ,p_control_rec IN OUT NOCOPY OE_GLOBALS.Control_Rec_Type
1814 ,p_ui_flag IN VARCHAR2 := 'N'
1815 ,p_top_model_line_id IN NUMBER := null
1816 ,p_config_hdr_id IN NUMBER := null
1817 ,p_config_rev_nbr IN NUMBER := null
1818 ,p_update_columns IN BOOLEAN := FALSE
1819 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1820 IS
1821 -- process_order in variables
1822 I NUMBER;
1823 l_line_rec OE_Order_PUB.Line_Rec_Type;
1824 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
1825 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
1826 l_msg_count NUMBER;
1827 l_msg_data VARCHAR2(2000);
1828 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1829 l_index NUMBER;
1830 l_return_code NUMBER; --Bug 4165102
1831 l_error_buffer VARCHAR2(240); --Bug 4165102
1832
1833 --
1834 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1835 --
1836 BEGIN
1837
1838 IF l_debug_level > 0 THEN
1839 oe_debug_pub.add( 'IN CALL_PROCESS_ORDER '|| P_LINE_TBL.COUNT , 1 ) ;
1840 END IF;
1841
1842 IF p_control_rec.check_security IS NULL THEN
1843 p_control_rec.check_security := TRUE;
1844 END IF;
1845
1846 IF p_control_rec.process IS NULL THEN
1847 IF p_ui_flag = 'Y' THEN
1848 p_control_rec.process := TRUE;
1849 ELSE
1850 p_control_rec.process := FALSE;
1851 END IF;
1852 END IF;
1853
1854 IF p_line_tbl.COUNT > 0 THEN
1855
1856 -- caller set the security and procees flags on ctrl rec.
1857 p_control_rec.default_attributes := TRUE;
1858 p_control_rec.controlled_operation := TRUE;
1859 p_control_rec.change_attributes := TRUE;
1860 p_control_rec.validate_entity := TRUE;
1861 p_control_rec.write_to_DB := TRUE;
1862 p_control_rec.process_entity := OE_GLOBALS.G_ENTITY_LINE;
1863
1864 -- change made for bug 2350478
1865 IF p_ui_flag = 'Y' THEN
1866 OE_CONFIG_PVT.OECFG_CONFIGURATION_PRICING := 'Y';
1867 END IF;
1868
1869 --OE_CONFIG_UTIL.CASCADE_CHANGES_FLAG := 'Y';
1870 OE_CONFIG_PVT.OECFG_VALIDATE_CONFIG := 'N';
1871
1872 IF p_ui_flag = 'Y' THEN
1873 OE_CONFIG_UTIL.G_CONFIG_UI_USED := 'Y';
1874 END IF;
1875
1876 fnd_profile.put('OE_CALCULATE_TAX_IN_OM', 'N');
1877
1878 Print_Time('call to lines start time');
1879 OE_Order_Pvt.Lines
1880 ( p_validation_level => FND_API.G_VALID_LEVEL_NONE
1881 , p_control_rec => p_control_rec
1885 Print_Time('call to lines end time');
1882 , p_x_line_tbl => p_line_tbl
1883 , p_x_old_line_tbl => l_old_line_tbl
1884 , x_return_status => l_return_status);
1886
1887 -- OE_GLOBALS.G_RECURSION_MODE := 'N';
1888 --OE_CONFIG_UTIL.CASCADE_CHANGES_FLAG := 'N';
1889 OE_CONFIG_PVT.OECFG_VALIDATE_CONFIG := 'Y';
1890 OE_CONFIG_PVT.OECFG_CONFIGURATION_PRICING := 'N';
1891 OE_CONFIG_UTIL.G_CONFIG_UI_USED := 'N';
1892
1893 fnd_profile.put('OE_CALCULATE_TAX_IN_OM', 'Y');
1894
1895 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1896 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1897 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1898 RAISE FND_API.G_EXC_ERROR;
1899 END IF;
1900 END IF; -- count > 0
1901
1902
1903
1904 ----------------------------------------------------------------------
1905 -- Direct insert/update/delete
1906 ----------------------------------------------------------------------
1907
1908 Print_Time('direct ins/upd/del start time');
1909
1910 l_index := l_line_tbl.COUNT;
1911
1912 IF nvl(p_class_line_tbl.count, -1) > 0 AND p_ui_flag = 'Y' THEN
1913 IF l_debug_level > 0 THEN
1914 oe_debug_pub.add('DIRECT OP ON CLASS: '|| P_CLASS_LINE_TBL.COUNT ,1);
1915 END IF;
1916
1917 I := p_class_line_tbl.FIRST;
1918
1919 WHILE I is not NULL
1920 LOOP
1921
1922 IF l_debug_level > 0 THEN
1923 oe_debug_pub.add('I: '|| P_CLASS_LINE_TBL ( I ) .LINE_ID , 1 ) ;
1924 END IF;
1925 l_line_rec := p_class_line_tbl(I);
1926
1927 IF l_line_rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
1928
1929 /* Bug 4165102 : Call to Globalization hook is included for class lines
1930 * if the profile option 'OM: Configuration Quick Save' is set to Yes.
1931 * Note: JG API defaults global_attributes ,only if they are passed as
1932 * NULL.
1933 */
1934 l_line_rec.global_attribute1 := NULL;
1935 l_line_rec.global_attribute2 := NULL;
1936 l_line_rec.global_attribute3 := NULL;
1937 l_line_rec.global_attribute4 := NULL;
1938 l_line_rec.global_attribute5 := NULL;
1939 l_line_rec.global_attribute6 := NULL;
1940 l_line_rec.global_attribute7 := NULL;
1941 l_line_rec.global_attribute8 := NULL;
1942 l_line_rec.global_attribute9 := NULL;
1943 l_line_rec.global_attribute10 := NULL;
1944 l_line_rec.global_attribute11 := NULL;
1945 l_line_rec.global_attribute12 := NULL;
1946 l_line_rec.global_attribute13 := NULL;
1947 l_line_rec.global_attribute14 := NULL;
1948 l_line_rec.global_attribute15 := NULL;
1949 l_line_rec.global_attribute16 := NULL;
1950 l_line_rec.global_attribute17 := NULL;
1951 l_line_rec.global_attribute18 := NULL;
1952 l_line_rec.global_attribute19 := NULL;
1953 l_line_rec.global_attribute20 := NULL;
1954 l_line_rec.global_attribute_category := NULL;
1955
1956 IF l_debug_level > 0 THEN
1957 OE_DEBUG_PUB.Add('Before calling JG ',2);
1958 END IF;
1959
1960 JG_ZZ_OM_COMMON_PKG.default_gdf
1961 (x_line_rec => l_line_rec,
1962 x_return_code => l_return_code,
1963 x_error_buffer => l_error_buffer);
1964
1965 IF l_debug_level > 0 THEN
1966 OE_DEBUG_PUB.Add('After JG Call:'|| l_return_code || l_error_buffer,2);
1967 END IF;
1968
1969
1970 IF l_debug_level > 0 THEN
1971 oe_debug_pub.add('INSERT: ' || L_LINE_REC.COMPONENT_CODE , 1 ) ;
1972 END IF;
1973
1974 OE_Line_Util.Insert_Row( p_line_rec => l_line_rec);
1975
1976 OE_Default_Line.Insert_into_set
1977 (p_line_id => l_line_rec.top_model_line_id,
1978 p_child_line_id => l_line_rec.line_id,
1979 x_return_status => l_return_status);
1980
1981 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1982 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1983 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1984 RAISE FND_API.G_EXC_ERROR;
1985 END IF;
1986 -- Bug 5912216: Start the line level workflows for config child
1987 -- lines only in case of normal sales order lines, NOT
1988 -- when processing negotiation lines.
1989 IF ( Nvl(l_line_rec.transaction_phase_code, 'F') <> 'N' ) THEN
1990 OE_Order_WF_Util.CreateStart_LineProcess(l_line_rec);
1991 END IF;
1992
1993 ELSIF l_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
1994 IF l_debug_level > 0 THEN
1995 oe_debug_pub.add('UPDATE: ' || L_LINE_REC.LINE_ID , 1 ) ;
1996 END IF;
1997
1998 UPDATE oe_order_lines
1999 SET ordered_quantity = l_line_rec.ordered_quantity
2000 WHERE line_id = l_line_rec.line_id;
2001
2002 ELSIF l_line_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
2003 IF l_debug_level > 0 THEN
2004 oe_debug_pub.add('DELETE: ' || L_LINE_REC.LINE_ID , 1 ) ;
2005 END IF;
2006
2007 DELETE FROM oe_order_lines
2008 WHERE line_id = l_line_rec.line_id;
2009
2013 END IF;
2010 ELSE
2011 IF l_debug_level > 0 THEN
2012 oe_debug_pub.add('OPERATION: '|| L_LINE_REC.OPERATION , 1 ) ;
2014 END IF;
2015
2016 l_index := l_index + 1;
2017 l_line_tbl(l_index) := l_line_rec;
2018
2019 I := p_class_line_tbl.NEXT(I);
2020 END LOOP;
2021
2022 Print_Time('direct ins/upd/del end time');
2023 END IF;
2024
2025
2026 ---------------------------------------------------------------------
2027 -- call to update link_to_line_id, ato_line_id etc.
2028 ---------------------------------------------------------------------
2029 IF p_update_columns = TRUE THEN
2030
2031 IF l_debug_level > 0 THEN
2032 oe_debug_pub.add('CALLING CHANGE COLUMNS' , 2 ) ;
2033 END IF;
2034
2035 oe_config_pvt.Change_Columns
2036 (p_top_model_line_id => p_top_model_line_id,
2037 p_config_hdr_id => p_config_hdr_id,
2038 p_config_rev_nbr => p_config_rev_nbr,
2039 p_ui_flag => p_ui_flag);
2040
2041 -- for bug 2247331
2042 oe_service_util.Update_Service_Option_Numbers
2043 (p_top_model_line_id => p_top_model_line_id);
2044
2045 END IF;
2046
2047
2048 ---------------------------------------------------------------------
2049 -- call to process_request_and_notify
2050 ---------------------------------------------------------------------
2051
2052 Print_Time('Process_Requests_And_notify call start time');
2053 IF p_ui_flag = 'Y' AND
2054 p_line_tbl.COUNT > 0 THEN
2055
2056 IF (p_line_tbl(1).booked_flag = 'Y') THEN
2057 OE_DELAYED_REQUESTS_PVT.Process_Request_for_Reqtype
2058 (p_request_type =>OE_GLOBALS.G_PRICE_ORDER
2059 ,p_delete => FND_API.G_TRUE
2060 ,x_return_status => l_return_status
2061 );
2062 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2063 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2064 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2065 RAISE FND_API.G_EXC_ERROR;
2066 END IF;
2067 END IF;
2068
2069 OE_ORDER_PVT.Process_Requests_And_notify
2070 ( p_process_requests => TRUE
2071 ,p_notify => TRUE
2072 ,x_return_status => l_return_status
2073 ,p_line_tbl => l_line_tbl
2074 ,p_old_line_tbl => l_old_line_tbl);
2075
2076 Print_Time('Process_Requests_And_notify call end time');
2077 END IF;
2078
2079
2080 oe_msg_pub.count_and_get
2081 ( p_count => l_msg_count
2082 ,p_data => l_msg_data );
2083
2084
2085 IF l_debug_level > 0 THEN
2086 oe_debug_pub.add('AFTER CALLING PROCESS ORDER' , 1 ) ;
2087 oe_debug_pub.add('L_RETURN_STATUS IS ' || L_RETURN_STATUS , 1 ) ;
2088 END IF;
2089
2090 x_return_status := l_return_status;
2091
2092 IF l_debug_level > 0 THEN
2093 oe_debug_pub.add('LEAVING CALL_PROCESS_ORDER' , 1 ) ;
2094 END IF;
2095
2096 EXCEPTION
2097 WHEN OTHERS THEN
2098 IF l_debug_level > 0 THEN
2099 oe_debug_pub.add('EXCEPTION IN CALL_PROCESS_ORDER: '|| SQLERRM , 1 ) ;
2100 END IF;
2101 RAISE;
2102 END Call_Process_Order;
2103
2104
2105 /*----------------------------------------------------------------
2106 Procedure Name : Change_Columns
2107 Description :
2108 Update link_to_line_id, ATO_line_id, option_number
2109 config_header_id and config_rev_nbr of model/class/option
2110 after updating ato_linr_id, for ato's under pto,
2111 default stuff.
2112 update lock_control, once is enough, however if
2113 p_config_flag is 'N', that is why also update in
2114 option_number update.
2115 we can set config_hdr and rev in the line_rec passed
2116 to process_order. But that will change the hdr and revb
2117 of only those options that got updated/newly inserted
2118 not all the options of this model.
2119
2120 change columns updates:
2121 column when do we want to update
2122 ------------------------------ ----------------------------
2123 link_to_line_id => creates
2124 ato_line_id => creates
2125 option_number => creates/deletes
2126 config_header_id, config_rev_nbr => always
2127
2128 p_operation : C for create
2129 D for delete
2130 A for all (proportional split, copy config calls)
2131 Change Record :
2132 Bug-2405271 : Update Option numbers for Config Item
2133 Bug-3318910 : Update config hdr/rev/id only if top level is MODEL
2134 Bug-3082485 : validation of decimal ratio for options to classes
2135 Bug-3700148 : do not do ratio check if model is remnant
2136 -----------------------------------------------------------------*/
2137
2138 Procedure Change_Columns
2139 (p_top_model_line_id IN NUMBER,
2140 p_config_hdr_id IN NUMBER,
2141 p_config_rev_nbr IN NUMBER,
2142 p_ui_flag IN VARCHAR2 := 'N',
2143 p_operation IN VARCHAR2 := 'A')
2144 IS
2145 l_line_id NUMBER;
2146 l_option_nbr NUMBER := 0;
2147 l_link NUMBER;
2148 l_line_count NUMBER := 0;
2149
2150 l_model_item_type_code VARCHAR2(30);
2154 l_prev_config_rev_nbr NUMBER;
2151 l_item_type_code VARCHAR2(30);
2152 l_model_ato_line_id NUMBER;
2153 l_prev_config_header_id NUMBER;
2155 l_remnant_flag VARCHAR2(1);
2156 l_configuration_id NUMBER;
2157 l_child_ordered_quantity NUMBER ;
2158 l_parent_ordered_quantity NUMBER ;
2159 l_child_ordered_item VARCHAR2(200);
2160 l_parent_ordered_item VARCHAR2(200);
2161 l_parent_item_type_code VARCHAR2(30);
2162 l_child_inv_item_id NUMBER ;
2163 l_parent_inv_item_id NUMBER ;
2164 l_ato_line_id NUMBER ;
2165 l_ratio_check VARCHAR2(1);
2166 l_indivisible_flag VARCHAR2(1);
2167
2168 -- option_number of config, talk to PM
2169 -- 3082485- changing the cursor to chose ordered_quantity ,
2170 -- ato_line_id , inventory item id and ordered item
2171 CURSOR option_nbr IS
2172 SELECT line_id, link_to_line_id, item_type_code,ordered_quantity,
2173 ato_line_id,inventory_item_id,ordered_item
2174 FROM oe_order_lines
2175 WHERE top_model_line_id = p_top_model_line_id
2176 AND line_id <> p_top_model_line_id
2177 AND service_reference_line_id is null
2178 AND item_type_code <> OE_GLOBALS.G_ITEM_INCLUDED
2179 AND item_type_code <> OE_GLOBALS.G_ITEM_CONFIG
2180 order by sort_order;
2181
2182 --
2183 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2184 --
2185 BEGIN
2186
2187 Print_Time('Entering Change_Columns');
2188
2189 BEGIN
2190 SELECT item_type_code, ato_line_id, config_header_id, config_rev_nbr,
2191 nvl(model_remnant_flag, 'N')
2192 INTO l_model_item_type_code , l_model_ato_line_id,
2193 l_prev_config_header_id, l_prev_config_rev_nbr,
2194 l_remnant_flag
2195 FROM oe_order_lines
2196 WHERE line_id = p_top_model_line_id;
2197 EXCEPTION
2198 WHEN OTHERS THEN
2199 IF l_debug_level > 0 THEN
2200 oe_debug_pub.add('EXCEPTION IN QUERY MODEL ATTIRBS' , 1 ) ;
2201 END IF;
2202 RAISE;
2203 END;
2204
2205
2206 --/*************** config ids *********************/
2207
2208 IF l_remnant_flag = 'N' AND
2209 l_model_item_type_code = 'MODEL' THEN
2210 IF p_config_hdr_id is NULL THEN
2211 IF l_debug_level > 0 THEN
2212 oe_debug_pub.add('OPTIONS WINDOW , CONFIGURATION ID' , 1 ) ;
2213 END IF;
2214
2215 UPDATE oe_order_lines
2216 SET configuration_id = nvl(configuration_id, 0) + 1,
2217 lock_control = lock_control + 1
2218 WHERE top_model_line_id = p_top_model_line_id
2219 AND item_type_code IN ('MODEL', 'CLASS', 'OPTION', 'KIT');
2220
2221 ELSE
2222 IF l_debug_level > 0 THEN
2223 oe_debug_pub.add( P_CONFIG_HDR_ID || ' ' || P_CONFIG_REV_NBR , 1 ) ;
2224 oe_debug_pub.add( L_PREV_CONFIG_HEADER_ID
2225 ||' '||L_PREV_CONFIG_REV_NBR , 1 ) ;
2226 END IF;
2227
2228 UPDATE oe_order_lines
2229 SET config_header_id = p_config_hdr_id,
2230 config_rev_nbr = p_config_rev_nbr,
2231 lock_control = lock_control + 1
2232 WHERE top_model_line_id = p_top_model_line_id
2233 AND item_type_code IN ('MODEL', 'CLASS', 'OPTION', 'KIT');
2234 END IF;
2235 END IF; -- remnant flag = N
2236
2237 --/*** update ato line_id and related attributes for subconfig **/
2238
2239 IF (p_operation = 'C' OR p_operation = 'A') AND
2240 l_model_item_type_code = OE_GLOBALS.G_ITEM_MODEL AND
2241 l_model_ato_line_id is NULL
2242 THEN
2243 IF l_debug_level > 0 THEN
2244 oe_debug_pub.add('UPDATE ATO ATTRIBS FOR SUBASSEMBLIES' , 1 ) ;
2245 END IF;
2246 update_ato_line_attributes( p_top_model_line_id => p_top_model_line_id
2247 ,p_ui_flag => p_ui_flag
2248 ,p_config_hdr_id => p_config_hdr_id);
2249
2250 --## bug fix 1643546, added new and condition ##1820608
2251 UPDATE oe_order_lines
2252 SET shippable_flag = 'N'
2253 WHERE top_model_line_id = p_top_model_line_id
2254 AND ato_line_id is NOT NULL
2255 AND item_type_code <> OE_GLOBALS.G_ITEM_CONFIG
2256 AND NOT (item_type_code = OE_GLOBALS.G_ITEM_OPTION AND
2257 ato_line_id = line_id);
2258 END IF;
2259
2260
2261 --/************* update link_to_line_id *****************/
2262
2263 IF p_operation = 'C' OR
2264 p_operation = 'A' THEN
2265 update_link_to_line_id
2266 ( p_top_model_line_id => p_top_model_line_id
2267 ,p_remnant_flag => l_remnant_flag
2268 ,p_config_hdr_id => p_config_hdr_id);
2269 END IF;
2270
2271
2272 --/************* update option_number *****************/
2273
2274 IF l_debug_level > 0 THEN
2275 oe_debug_pub.add('UPDATING OPTION_NUMBER IN OE_ORDER_LINES' , 1 ) ;
2276 END IF;
2277
2278 OPEN option_nbr;
2279 LOOP
2280 FETCH option_nbr into l_line_id, l_link, l_item_type_code,
2281 l_child_ordered_quantity, l_ato_line_id,
2282 l_child_inv_item_id,l_child_ordered_item;
2283 EXIT when option_nbr%notfound;
2284
2285 l_option_nbr := l_option_nbr + 1;
2286
2287 UPDATE oe_order_lines
2291
2288 SET option_number = l_option_nbr,
2289 lock_control = lock_control + 1
2290 WHERE line_id = l_line_id;
2292 IF l_debug_level > 0 THEN
2293 oe_debug_pub.add('FOR '|| L_LINE_ID || ' LLID '|| L_LINK , 3 ) ;
2294 END IF;
2295
2296 -- 3082485
2297 IF l_link <> p_top_model_line_id AND
2298 p_config_hdr_id is NULL AND
2299 l_remnant_flag = 'N' THEN
2300
2301 IF l_debug_level > 0 THEN
2302 oe_debug_pub.add('do we need to check ration with parent' , 3 ) ;
2303 END IF;
2304
2305 l_ratio_check := 'Y';
2306
2307 IF l_ato_line_id is not null AND
2308 l_item_type_code = 'OPTION' AND
2309 l_ato_line_id <> l_line_id THEN
2310
2311 SELECT INDIVISIBLE_FLAG
2312 INTO l_indivisible_flag
2313 FROM mtl_system_items
2314 WHERE inventory_item_id = l_child_inv_item_id
2315 AND organization_id =
2316 OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID');
2317
2318 IF nvl(l_indivisible_flag, 'N') = 'N' THEN
2319 IF l_debug_level > 0 THEN
2320 OE_DEBUG_PUB.ADD('this Option can have decimal ratio', 1);
2321 END IF;
2322
2323 l_ratio_check := 'N';
2324 ELSE
2325 IF l_debug_level > 0 THEN
2326 OE_DEBUG_PUB.ADD('this Option can not have decimal ratio', 1);
2327 END IF;
2328 END IF;
2329 END IF;
2330
2331
2332 IF l_debug_level > 0 THEN
2333 oe_debug_pub.add('check ration with parent '|| l_ratio_check , 3 ) ;
2334 END IF;
2335
2336 IF l_ratio_check = 'Y' THEN
2337
2338 SELECT ordered_quantity,ordered_item,
2339 item_type_code,inventory_item_id
2340 INTO l_parent_ordered_quantity,l_parent_ordered_item,
2341 l_parent_item_type_code, l_parent_inv_item_id
2342 FROM OE_ORDER_LINES
2343 WHERE line_id = l_link;
2344
2345 IF mod(l_child_ordered_quantity,l_parent_ordered_quantity) <> 0
2346 THEN
2347
2348 FND_MESSAGE.SET_NAME('ONT', 'OE_CONFIG_DECIMAL_RATIO');
2349 FND_MESSAGE.Set_TOKEN
2350 ('ITEM', nvl(l_child_ordered_item,l_child_inv_item_id));
2351 FND_MESSAGE.Set_TOKEN
2352 ('TYPECODE',l_item_type_code);
2353 FND_MESSAGE.Set_TOKEN
2354 ('VALUE',to_char(l_child_ordered_quantity/l_parent_ordered_quantity));
2355 FND_MESSAGE.Set_TOKEN
2356 ('MODEL', nvl(l_parent_ordered_item,l_parent_inv_item_id));
2357 FND_MESSAGE.Set_TOKEN('PTYPECODE', l_parent_item_type_code);
2358 OE_MSG_PUB.Add;
2359 RAISE FND_API.G_EXC_ERROR ;
2360 END IF;
2361
2362 END IF; -- ratio check
2363 END IF; -- options window
2364 -- fix for 3082485 ends
2365
2366
2367 IF l_model_ato_line_id is NULL AND
2368 (l_item_type_code = OE_GLOBALS.G_ITEM_CLASS OR
2369 l_item_type_code = OE_GLOBALS.G_ITEM_KIT)
2370 THEN
2371 BEGIN
2372 UPDATE oe_order_lines
2373 SET option_number = l_option_nbr,
2374 lock_control = lock_control + 1
2375 WHERE top_model_line_id = p_top_model_line_id
2376 AND link_to_line_id = l_line_id
2377 AND item_type_code = 'INCLUDED';
2378
2379 EXCEPTION
2380 WHEN NO_DATA_FOUND THEN
2381 IF l_debug_level > 0 THEN
2382 oe_debug_pub.add('NO INCLUDED ITEMS' , 3 ) ;
2383 END IF;
2384 END;
2385
2386 BEGIN
2387 UPDATE oe_order_lines
2388 SET option_number = l_option_nbr,
2389 lock_control = lock_control + 1
2390 WHERE top_model_line_id = p_top_model_line_id
2391 AND ato_line_id = l_line_id
2392 AND item_type_code = 'CONFIG';
2393
2394 EXCEPTION
2395 WHEN NO_DATA_FOUND THEN
2396 IF l_debug_level > 0 THEN
2397 oe_debug_pub.add('NO CONFIG ITEMS' , 3 ) ;
2398 END IF;
2399 END;
2400
2401 END IF;
2402 END LOOP;
2403
2404 CLOSE option_nbr;
2405
2406 IF l_model_ato_line_id is NULL THEN
2407
2408 UPDATE oe_order_lines o
2409 SET ordered_quantity =
2410 (SELECT ordered_quantity
2411 FROM oe_order_lines
2412 WHERE line_id = o.link_to_line_id)
2413 WHERE top_model_line_id = p_top_model_line_id
2414 AND item_type_code = OE_GLOBALS.G_ITEM_CONFIG
2415 AND nvl(model_remnant_flag, 'N') = 'N';
2416
2417 UPDATE oe_order_lines
2418 SET cancelled_flag = 'Y'
2419 WHERE top_model_line_id = p_top_model_line_id
2420 AND item_type_code = 'CONFIG'
2421 AND ordered_quantity = 0;
2422
2423
2424 END IF;
2425
2426 IF l_debug_level > 0 THEN
2427 oe_debug_pub.add('LEAVING CHANGE_COLUMNS IN OE_CONFIG_PVT' , 1 ) ;
2428 END IF;
2429
2430 EXCEPTION
2431 WHEN FND_API.G_EXC_ERROR THEN
2432 IF l_debug_level > 0 THEN
2433 oe_debug_pub.add('EXC ERROR IN CHANGE_COLUMNS IN OE_CONFIG_PVT' , 1 ) ;
2434 END IF;
2438 IF l_debug_level > 0 THEN
2435 RAISE FND_API.G_EXC_ERROR;
2436
2437 WHEN OTHERS THEN
2439 oe_debug_pub.add('ERROR IN CHANGE_COLUMNS IN OE_CONFIG_PVT' , 1 ) ;
2440 END IF;
2441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2442 END Change_Columns;
2443
2444
2445 /*----------------------------------------------------------------
2446 Procedure : update_link_to_line_id
2447 OE_CODE_CONTROL.Get_Code_Release_Level >= '110508' : Pack H
2448 -----------------------------------------------------------------*/
2449 Procedure update_link_to_line_id
2450 ( p_top_model_line_id IN NUMBER
2451 ,p_remnant_flag IN VARCHAR2
2452 ,p_config_hdr_id IN NUMBER)
2453 IS
2454 --
2455 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2456 --
2457 BEGIN
2458
2459 IF l_debug_level > 0 THEN
2460 oe_debug_pub.add('UPDATING LINK_TO_LINE_ID '|| P_REMNANT_FLAG , 1 ) ;
2461 oe_debug_pub.add( P_CONFIG_HDR_ID || ' MODEL: '|| P_TOP_MODEL_LINE_ID ,1);
2462 END IF;
2463
2464 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110508' THEN
2465 IF l_debug_level > 0 THEN
2466 oe_debug_pub.add('1 LLID: PACK H NEW LOGIC FOR SPLIT ' , 1 ) ;
2467 END IF;
2468
2469 UPDATE oe_order_lines OEOPT
2470 SET link_to_line_id =
2471 (SELECT line_id
2472 FROM oe_order_lines oe1
2473 WHERE split_from_line_id =
2474 (SELECT link_to_line_id
2475 FROM oe_order_lines oe2
2476 WHERE line_id = OEOPT.split_from_line_id
2477 AND oe2.open_flag = 'Y')
2478 AND oe1.top_model_line_id = p_top_model_line_id
2479 AND oe1.open_flag = 'Y' )
2480 WHERE OEOPT.top_model_line_id = p_top_model_line_id
2481 AND OEOPT.line_id <> p_top_model_line_id
2482 AND OEOPT.link_to_line_id is NULL
2483 AND OEOPT.split_from_line_id is NOT NULL
2484 AND OEOPT.open_flag = 'Y';
2485
2486 IF SQL%FOUND THEN
2487 IF l_debug_level > 0 THEN
2488 oe_debug_pub.add('1 LLID UPDATED ' || SQL%ROWCOUNT ) ;
2489 END IF;
2490 END IF;
2491
2492 END IF;
2493
2494
2495 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110508' AND
2496 p_config_hdr_id is NOT NULL THEN
2497 IF l_debug_level > 0 THEN
2498 oe_debug_pub.add('2 LLID: PACK H NEW LOGIC MI '|| P_REMNANT_FLAG , 1 ) ;
2499 END IF;
2500
2501 UPDATE oe_order_lines OEOPT
2502 SET link_to_line_id =
2503 ( SELECT line_id
2504 FROM oe_order_lines OELNK
2505 WHERE OELNK.top_model_line_id = OEOPT.top_model_line_id
2506 AND OELNK.configuration_id =
2507 ( SELECT parent_config_item_id
2508 FROM cz_config_details_v
2509 WHERE config_hdr_id = OELNK.config_header_id
2510 AND config_rev_nbr = OELNK.config_rev_nbr
2511 AND config_item_id = OEOPT.configuration_id
2512 )
2513 AND OELNK.open_flag = 'Y'
2514 )
2515 WHERE OEOPT.top_model_line_id = p_top_model_line_id
2516 AND OEOPT.line_id <> p_top_model_line_id
2517 AND OEOPT.link_to_line_id IS NULL;
2518
2519 IF SQL%FOUND THEN
2520 IF l_debug_level > 0 THEN
2521 oe_debug_pub.add('2 LLID UPDATED ' || SQL%ROWCOUNT ) ;
2522 END IF;
2523 END IF;
2524
2525 ELSE
2526
2527 IF l_debug_level > 0 THEN
2528 oe_debug_pub.add('LLID OPTIONS WINDOW OR OLD LOGIC ' , 3 ) ;
2529 END IF;
2530
2531 UPDATE oe_order_lines OEOPT
2532 SET link_to_line_id =
2533 ( SELECT OELNK.line_id
2534 FROM oe_order_lines OELNK
2535 WHERE (( OELNK.line_id = oeopt.top_model_line_id OR
2536 OELNK.top_model_line_id = OEOPT.top_model_line_id ))
2537 AND (OELNK.component_code = SUBSTR( OEOPT.component_code,1,
2538 LENGTH( RTRIM( OEOPT.component_code,'0123456789' )) - 1)
2539 OR (OELNK.component_code = OEOPT.component_code AND
2540 OEOPT.item_type_code = OE_GLOBALS.G_ITEM_MODEL))
2541 AND open_flag = 'Y'
2542 )
2543 WHERE OEOPT.top_model_line_id = p_top_model_line_id
2544 AND OEOPT.line_id <> p_top_model_line_id
2545 AND OEOPT.link_to_line_id IS NULL
2546 AND OEOPT.item_type_code <> OE_GLOBALS.G_ITEM_INCLUDED;
2547
2548 IF SQL%FOUND THEN
2549 IF l_debug_level > 0 THEN
2550 oe_debug_pub.add('3 LLID UPDATED ' || SQL%ROWCOUNT ) ;
2551 END IF;
2552 END IF;
2553
2554 -- only in a post split situation.
2555
2556 UPDATE oe_order_lines OEOPT
2557 SET link_to_line_id =
2558 (SELECT line_id
2559 FROM oe_order_lines oe1
2560 WHERE split_from_line_id =
2561 (SELECT link_to_line_id
2562 FROM oe_order_lines oe2
2563 WHERE line_id = OEOPT.split_from_line_id
2564 AND oe2.open_flag = 'Y')
2565 AND oe1.top_model_line_id = p_top_model_line_id
2566 AND oe1.open_flag = 'Y' )
2567 WHERE OEOPT.top_model_line_id = p_top_model_line_id
2568 AND OEOPT.line_id <> p_top_model_line_id
2572 AND OEOPT.open_flag = 'Y';
2569 AND OEOPT.item_type_code = OE_GLOBALS.G_ITEM_INCLUDED
2570 AND OEOPT.link_to_line_id is NULL
2571 AND OEOPT.split_from_line_id is NOT NULL
2573
2574 IF SQL%FOUND THEN
2575 IF l_debug_level > 0 THEN
2576 oe_debug_pub.add('1 LLID UPDATED ' || SQL%ROWCOUNT ) ;
2577 END IF;
2578 END IF;
2579
2580 END IF;
2581
2582 IF l_debug_level > 0 THEN
2583 oe_debug_pub.add('UPDATED LINK_TO_LINE_ID IN OE_ORDER_LINES' , 2 ) ;
2584 END IF;
2585
2586 EXCEPTION
2587 WHEN OTHERS THEN
2588 IF l_debug_level > 0 THEN
2589 oe_debug_pub.add('ERROR IN UPDATE_LINK_TO_LINE_ID' || SQLERRM , 1 ) ;
2590 END IF;
2591 RAISE;
2592 END update_link_to_line_id;
2593
2594
2595 /*----------------------------------------------------------------
2596 Procedure : update_ato_line_attributes
2597
2598 Change Record:
2599 bug 1894331
2600 the select statement for getting ato_line_id in case of
2601 pto+ato case is modified. look at the bug for more details.
2602 also made same change in OEXDLINB.pls:get_ato_line.
2603
2604 bug 2143052: added open_flag to cursors.
2605
2606 From mi project, we have decided that ato_line_id will not be
2607 updated if the model is remnant here. The ato_line_id populated
2608 by SPLIT defaulting remains.
2609 -----------------------------------------------------------------*/
2610 Procedure update_ato_line_attributes
2611 ( p_top_model_line_id IN NUMBER
2612 ,p_ui_flag IN VARCHAR2
2613 ,p_config_hdr_id IN NUMBER)
2614 IS
2615 l_component_code varchar2(1000);
2616 l_project_id number;
2617 l_task_id number;
2618 l_ship_from_org_id number;
2619 l_ship_to_org_id number;
2620 l_schedule_ship_date date;
2621 l_schedule_arrival_date date;
2622 l_request_date date;
2623 l_shipping_method_code varchar2(30);
2624 l_freight_carrier_code varchar2(30);
2625 l_ato_line_id number;
2626 l_option_line_id number;
2627 l_ato_line_rec OE_ORDER_PUB.Line_Rec_Type;
2628 l_line_rec OE_ORDER_PUB.Line_Rec_Type;
2629 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
2630 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
2631 l_control_rec OE_GLOBALS.Control_Rec_Type;
2632 l_line_count NUMBER := 0;
2633 l_return_status VARCHAR2(1);
2634
2635 -- cursor modified, ## 1820608
2636
2637 CURSOR ATO_MODELS IS
2638 SELECT unique(ato_line_id)
2639 FROM oe_order_lines_all
2640 WHERE top_model_line_id = p_top_model_line_id
2641 AND ato_line_id is not null
2642 AND item_type_code = OE_GLOBALS.G_ITEM_CLASS
2643 AND open_flag = 'Y'; -- ato subconfigs
2644
2645 CURSOR ATO_OPTIONS(p_ato_line_id IN NUMBER)
2646 IS
2647 SELECT opt.line_id
2648 FROM oe_order_lines_all opt, oe_order_lines_all ato_model
2649 WHERE opt.top_model_line_id = p_top_model_line_id AND
2650 ato_model.top_model_line_id = p_top_model_line_id AND
2651 ato_model.line_id = p_ato_line_id AND
2652 opt.open_flag = 'Y' AND
2653 opt.ato_line_id = p_ato_line_id AND
2654 (nvl(ato_model.project_id,-1) <>
2655 nvl(opt.project_id,-1) OR
2656
2657 nvl(ato_model.task_id,-1) <>
2658 nvl(opt.task_id,-1) OR
2659
2660 nvl(ato_model.ship_from_org_id,-1) <>
2661 nvl(opt.ship_from_org_id,-1) OR
2662
2663 nvl(ato_model.ship_to_org_id,-1) <>
2664 nvl(opt.ship_to_org_id,-1) OR
2665
2666 nvl(ato_model.schedule_ship_date,SYSDATE) <>
2667 nvl(opt.schedule_ship_date,SYSDATE) OR
2668
2669 nvl(ato_model.schedule_arrival_date,SYSDATE) <>
2670 nvl(opt.schedule_arrival_date,SYSDATE) OR
2671
2672 nvl(ato_model.request_date,SYSDATE) <>
2673 nvl(opt.request_date,SYSDATE) OR
2674
2675 nvl(ato_model.shipping_method_code,'-') <>
2676 nvl(opt.shipping_method_code,'-') OR
2677
2678 nvl(ato_model.freight_carrier_code,'-') <>
2679 nvl(opt.freight_carrier_code,'-') );
2680
2681 --
2682 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2683 --
2684 BEGIN
2685
2686 BEGIN
2687 -- If the model line is not ATO, then for all ATO lines, set the
2688 -- ATO_LINE_ID to the "highest" ATO line
2689
2690 IF l_debug_level > 0 THEN
2691 oe_debug_pub.add('UPDATING ATO_LINE_ID IN FOR SUBASSEMBLIES' , 1 ) ;
2692 END IF;
2693
2694
2695 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110508' AND
2696 p_config_hdr_id is NOT NULL
2697 THEN
2698 IF l_debug_level > 0 THEN
2699 oe_debug_pub.add('UPDATE_ATO: PACK H NEW LOGIC MI' , 1 ) ;
2700 END IF;
2701
2702 UPDATE oe_order_lines OEOPT
2703 SET ato_line_id =
2704 ( SELECT line_id
2705 FROM oe_order_lines OEATO
2706 WHERE OEOPT.top_model_line_id = OEATO.top_model_line_id
2707 AND OEATO.configuration_id =
2708 (SELECT ato_config_item_id
2712 AND config_item_id = OEOPT.configuration_id)
2709 FROM cz_config_details_v
2710 WHERE config_hdr_id = OEOPT.config_header_id
2711 AND config_rev_nbr = OEOPT.config_rev_nbr
2713 AND OEATO.open_flag = 'Y'
2714 )
2715 WHERE TOP_MODEL_LINE_ID = p_top_model_line_id
2716 AND NOT (item_type_code = 'OPTION' AND
2717 ato_line_id = line_id AND
2718 ato_line_id is not null)
2719 AND item_type_code <> 'CONFIG' -- not config line important.
2720 AND nvl(model_remnant_flag, 'N') = 'N'
2721 AND ordered_quantity > 0;
2722 -- model remnant condition important
2723
2724 IF SQL%FOUND THEN
2725 IF l_debug_level > 0 THEN
2726 oe_debug_pub.add('NEW ATO_LINE UPDATED ' || SQL%ROWCOUNT ) ;
2727 END IF;
2728 ELSE
2729 IF l_debug_level > 0 THEN
2730 oe_debug_pub.add('DID NOT UPDATE ANY LINE WITH ATO' ) ;
2731 END IF;
2732 END IF;
2733
2734 -- note that the CONFIG line needs ato_line_id after
2735 -- proportional split.
2736
2737 UPDATE oe_order_lines OEOPT
2738 SET ato_line_id =
2739 (SELECT line_id
2740 FROM oe_order_lines oe1
2741 WHERE split_from_line_id =
2742 (SELECT ato_line_id
2743 FROM oe_order_lines oe2
2744 WHERE line_id = OEOPT.split_from_line_id
2745 AND oe2.open_flag = 'Y')
2746 AND oe1.top_model_line_id = p_top_model_line_id
2747 AND oe1.open_flag = 'Y' )
2748 WHERE OEOPT.top_model_line_id = p_top_model_line_id
2749 AND OEOPT.line_id <> p_top_model_line_id
2750 AND OEOPT.split_from_line_id is NOT NULL
2751 AND OEOPT.open_flag = 'Y'
2752 AND OEOPT.item_type_code = 'CONFIG'
2753 AND OEOPT.ato_line_id is null;
2754
2755 IF SQL%FOUND THEN
2756 IF l_debug_level > 0 THEN
2757 oe_debug_pub.add
2758 ('DUE TO MI: ATO LINE SPLIT FOR CONFIG ' || SQL%ROWCOUNT ) ;
2759 END IF;
2760 END IF;
2761
2762 ELSE
2763 -- in case of ato', all of the lines will be remanant or
2764 -- or none. we do not have choice here, even options window
2765 -- will have this change.
2766
2767 UPDATE oe_order_lines OEOPT
2768 SET ato_line_id =
2769 (SELECT line_id
2770 FROM oe_order_lines oe1
2771 WHERE split_from_line_id =
2772 (SELECT ato_line_id
2773 FROM oe_order_lines oe2
2774 WHERE line_id = OEOPT.split_from_line_id
2775 AND oe2.open_flag = 'Y')
2776 AND oe1.top_model_line_id = p_top_model_line_id
2777 AND oe1.open_flag = 'Y' )
2778 WHERE OEOPT.top_model_line_id = p_top_model_line_id
2779 AND OEOPT.line_id <> p_top_model_line_id
2780 AND OEOPT.split_from_line_id is NOT NULL
2781 AND OEOPT.open_flag = 'Y'
2782 AND OEOPT.model_remnant_flag = 'Y'
2783 AND NOT (item_type_code = 'OPTION' AND
2784 ato_line_id = line_id AND
2785 ato_line_id is not null);
2786
2787 IF SQL%FOUND THEN
2788 IF l_debug_level > 0 THEN
2789 oe_debug_pub.add('DUE TO MI: ATO_LINE SPLIT' || SQL%ROWCOUNT ) ;
2790 END IF;
2791 ELSE
2792
2793 IF l_debug_level > 0 THEN
2794 oe_debug_pub.add('OLD ATO_LINE_ID / NOT SPLIT' , 1 ) ;
2795 END IF;
2796
2797 UPDATE OE_ORDER_LINES_ALL OEOPT
2798 SET ATO_LINE_ID =
2799 ( SELECT OEATO.LINE_ID
2800 FROM OE_ORDER_LINES_ALL OEATO
2801 WHERE OEATO.TOP_MODEL_LINE_ID =
2802 OEOPT.TOP_MODEL_LINE_ID
2803 AND ITEM_TYPE_CODE = 'CLASS'
2804 AND OEATO.COMPONENT_CODE =
2805 SUBSTR( OEOPT.COMPONENT_CODE, 1,
2806 LENGTH( OEATO.COMPONENT_CODE )
2807 )
2808 AND OEATO.inventory_item_id =
2809 ( SELECT inventory_item_id
2810 FROM mtl_system_items
2811 WHERE inventory_item_id =
2812 OEATO.inventory_item_id
2813 AND organization_id =
2814 OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID')
2815 AND replenish_to_order_flag = 'Y'
2816 )
2817 AND OEATO.COMPONENT_CODE =
2818 ( SELECT MIN( OEMIN.COMPONENT_CODE )
2819 FROM OE_ORDER_LINES_ALL OEMIN
2820 WHERE OEMIN.TOP_MODEL_LINE_ID
2821 = OEOPT.TOP_MODEL_LINE_ID
2822 AND OEMIN.COMPONENT_CODE =
2823 SUBSTR( OEOPT.COMPONENT_CODE, 1,
2824 LENGTH( OEMIN.COMPONENT_CODE ))
2825 AND OEMIN.inventory_item_id =
2826 ( SELECT inventory_item_id
2827 FROM mtl_system_items
2828 WHERE inventory_item_id =
2832 AND replenish_to_order_flag = 'Y'
2829 OEMIN.inventory_item_id
2830 AND organization_id =
2831 OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID')
2833 )
2834 )
2835 AND ((SUBSTR(OEOPT.component_code,
2836 LENGTH(OEATO.component_code) + 1, 1) = '-' OR
2837 SUBSTR(OEOPT.component_code,
2838 LENGTH(OEATO.component_code) + 1, 1) is NULL)
2839 )
2840 )
2841 WHERE TOP_MODEL_LINE_ID = p_top_model_line_id
2842 AND NOT (item_type_code = 'OPTION' AND
2843 ato_line_id = line_id AND
2844 ato_line_id is not null);
2845
2846 IF SQL%FOUND THEN
2847 IF l_debug_level > 0 THEN
2848 oe_debug_pub.add('OLD ATO_LINE UPDATED ' || SQL%ROWCOUNT ) ;
2849 END IF;
2850 END IF;
2851
2852 END IF; -- split or not
2853
2854 END IF; -- pack H or not
2855
2856 EXCEPTION
2857 WHEN OTHERS THEN
2858 IF l_debug_level > 0 THEN
2859 oe_debug_pub.add('UNEXPECTED ERROR IN UPDATE ATO_LINE_ID' , 1 ) ;
2860 END IF;
2861 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2862 END;
2863
2864 ------------------------------------------------------------------
2865
2866 IF l_debug_level > 0 THEN
2867 oe_debug_pub.add
2868 ('UPDATING OTHER ATTRIBUTES ON OPTIONS/CLASSES OF SUBASSEMBLIES' , 1);
2869 END IF;
2870
2871 OPEN ATO_MODELS;
2872 LOOP
2873 FETCH ATO_MODELS INTO l_ato_line_id;
2874 EXIT WHEN ATO_MODELS%NOTFOUND;
2875
2876 IF l_debug_level > 0 THEN
2877 oe_debug_pub.add('ATO LINE: ' || L_ATO_LINE_ID , 1 ) ;
2878 END IF;
2879
2880 OE_LINE_UTIL.Lock_Row
2881 (p_line_id => l_ato_line_id
2882 ,p_x_line_rec => l_ato_line_rec
2883 ,x_return_status => l_return_status);
2884
2885 OPEN ATO_OPTIONS(l_ato_line_id);
2886 LOOP
2887 FETCH ATO_OPTIONS INTO l_option_line_id;
2888 EXIT WHEN ATO_OPTIONS%NOTFOUND;
2889
2890 OE_LINE_UTIL.Query_Row
2891 (p_line_id => l_option_line_id
2892 ,x_line_rec => l_line_rec);
2893
2894 l_line_count := l_line_count + 1;
2895 l_old_line_tbl(l_line_count) := l_line_rec;
2896
2897 IF l_debug_level > 0 THEN
2898 oe_debug_pub.add('UPDATING LINE: ' || L_OPTION_LINE_ID , 1 ) ;
2899 END IF;
2900
2901 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
2902 l_line_rec.line_id := l_option_line_id;
2903 l_line_rec.project_id := l_ato_line_rec.project_id;
2904 l_line_rec.task_id := l_ato_line_rec.task_id;
2905 l_line_rec.ship_from_org_id := l_ato_line_rec.ship_from_org_id;
2906 l_line_rec.ship_to_org_id := l_ato_line_rec.ship_to_org_id;
2907 l_line_rec.schedule_ship_date :=
2908 l_ato_line_rec.schedule_ship_date;
2909 l_line_rec.schedule_arrival_date :=
2910 l_ato_line_rec.schedule_arrival_date;
2911 l_line_rec.request_date :=
2912 l_ato_line_rec.request_date;
2913 l_line_rec.shipping_method_code :=
2914 l_ato_line_rec.shipping_method_code;
2915 l_line_rec.freight_carrier_code :=
2916 l_ato_line_rec.freight_carrier_code;
2917
2918 l_line_tbl(l_line_count) := l_line_rec;
2919
2920 END LOOP;
2921 CLOSE ATO_OPTIONS;
2922 END LOOP;
2923 CLOSE ATO_MODELS;
2924
2925 IF l_line_count = 0 THEN
2926 RETURN;
2927 END IF;
2928
2929 -- Set Control Record
2930 l_control_rec.check_security := TRUE;
2931
2932 -- if ui calls prcess_config, we want all the
2933 -- delayed requests to be executed.
2934 -- if batch validation calls it, we do not want
2935 -- the delayed requests to be executed in recursive
2936 -- call to process_order
2937
2938 IF p_ui_flag = 'Y' THEN
2939 l_control_rec.process := TRUE;
2940 ELSE
2941 l_control_rec.process := FALSE;
2942 END IF;
2943
2944 IF l_debug_level > 0 THEN
2945 oe_debug_pub.add('IN UPDATE_ATO_ATTIRBS , CALLING PROCESS_ORDER' , 1 ) ;
2946 END IF;
2947
2948 OE_CONFIG_UTIL.CASCADE_CHANGES_FLAG := 'Y';
2949
2950 Call_Process_Order
2951 ( p_line_tbl => l_line_tbl
2952 ,p_control_rec => l_control_rec
2953 ,p_ui_flag => p_ui_flag
2954 ,x_return_status => l_return_status);
2955
2956 OE_CONFIG_UTIL.CASCADE_CHANGES_FLAG := 'N';
2957
2958 IF l_debug_level > 0 THEN
2959 oe_debug_pub.add
2960 ('IN UPDATE_ATO_ATTIRBS , AFTER PO: ' || L_RETURN_STATUS , 1 ) ;
2961 END IF;
2962
2963 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2964 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2965 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2966 RAISE FND_API.G_EXC_ERROR;
2967 END IF;
2968
2969 EXCEPTION
2970 WHEN OTHERS THEN
2971 IF l_debug_level > 0 THEN
2975 END update_ato_line_attributes;
2972 oe_debug_pub.add('ERROR IN UPDATE_ATO_LINE_ATTRIBUTES' || SQLERRM , 1 ) ;
2973 END IF;
2974 RAISE;
2976
2977
2978 /* --------------------------------------------------------------------
2979 Procedure Name : Delete_Config
2980 Description : Deletes the configuration from SPC's tables
2981 -------------------------------------------------------------------- */
2982
2983 Procedure Delete_Config
2984 (p_config_hdr_id IN NUMBER ,
2985 p_config_rev_nbr IN NUMBER ,
2986 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
2987 IS
2988 l_usage_exists number;
2989 l_return_value number := 1;
2990 l_error_message varchar2(100);
2991
2992 --
2993 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2994 --
2995 BEGIN
2996
2997 IF l_debug_level > 0 THEN
2998 oe_debug_pub.add('ENTERING DELETE_CONFIG IN OE_CONFIG_PVT' , 1 ) ;
2999 END IF;
3000
3001 IF p_config_hdr_id is not null AND
3002 p_config_rev_nbr is not null THEN
3003
3004 IF l_debug_level > 0 THEN
3005 oe_debug_pub.add
3006 ('DEL CFG' ||P_CONFIG_HDR_ID || ' ' || P_CONFIG_REV_NBR , 1 ) ;
3007 END IF;
3008
3009 CZ_CF_API.Delete_Configuration
3010 ( config_hdr_id => p_config_hdr_id
3011 ,config_rev_nbr => p_config_rev_nbr
3012 ,usage_exists => l_usage_exists
3013 ,error_message => l_error_message
3014 ,return_value => l_return_value );
3015
3016 -- when error, returns 0, else 1
3017
3018 IF l_return_value <> 1 THEN
3019 OE_Msg_Pub.Add_Text(l_error_message);
3020 IF l_debug_level > 0 THEN
3021 oe_debug_pub.add('ERROR IN DELETE_CONFIG IN OE_CONFIG_PVT' , 1 ) ;
3022 END IF;
3023 x_return_status := FND_API.G_RET_STS_ERROR;
3024 ELSE
3025 x_return_status := FND_API.G_RET_STS_SUCCESS;
3026 END IF;
3027 ELSE
3028 IF l_debug_level > 0 THEN
3029 oe_debug_pub.add('NOTE : NULL CONFIG_HEADER_ID/CONFIG_REV_NBR PASSED');
3030 END IF;
3031 END IF;
3032
3033 IF l_debug_level > 0 THEN
3034 oe_debug_pub.add('LEAVING DELETE_CONFIG IN OE_CONFIG_PVT' , 1 ) ;
3035 END IF;
3036
3037 EXCEPTION
3038 when others then
3039 IF l_debug_level > 0 THEN
3040 oe_debug_pub.add('ERROR IN DELETE_CONFIG IN OE_CONFIG_PVT' , 1 ) ;
3041 END IF;
3042
3043
3044 END Delete_Config;
3045
3046
3047 /* --------------------------------------------------------------------
3048 Procedure Name : Copy_Config
3049 Description : Copies a configuration in spc's tables asnd gives
3050 back new config_header_id, new_config_rev_nbr and
3051 updates link_to_line_id, ATO_line_id, option_number
3052 config_header_id and config_rev_nbr of model/class/option
3053
3054 Change Record:
3055 2611771 : new cz copy config call
3056 3144865 : skip call to change_columns if only model line is selected
3057 3318910 : call change_columns for KIT/ options window, bug fix on top
3058 of 3144865 to fix the issues in that bug fix.
3059 -------------------------------------------------------------------- */
3060
3061 Procedure Copy_Config(p_top_model_line_id IN NUMBER ,
3062 p_config_hdr_id IN NUMBER ,
3063 p_config_rev_nbr IN NUMBER ,
3064 p_configuration_id IN NUMBER ,
3065 p_remnant_flag IN VARCHAR2 ,
3066 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
3067 IS
3068 l_return_value number;
3069 l_error_message varchar2(100);
3070 l_new_config_flag varchar2(1) := '1';
3071 l_flag varchar2(1) := 'Y';
3072 l_config_hdr_id number;
3073 l_config_rev_nbr number;
3074 l_configuration_id number;
3075 l_ato_line_id number;
3076 l_orig_item_id_tbl CZ_API_PUB.number_tbl_type;
3077 l_new_item_id_tbl CZ_API_PUB.number_tbl_type;
3078 l_msg_count NUMBER;
3079 l_msg_data VARCHAR2(2000);
3080 l_booked_flag varchar2(1);
3081
3082 --
3083 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3084 --
3085 BEGIN
3086
3087 IF l_debug_level > 0 THEN
3088 oe_debug_pub.add('ENTERING COPY_CONFIG '|| P_REMNANT_FLAG , 1 ) ;
3089 oe_debug_pub.add('MODEL LINE: '|| P_TOP_MODEL_LINE_ID , 1 ) ;
3090 END IF;
3091
3092 l_return_value := 1;
3093
3094 -- we need a copy only if not remnant.
3095 IF p_config_hdr_id is not null AND
3096 p_config_rev_nbr is not null AND
3097 p_remnant_flag is null
3098 THEN
3099
3100 IF OE_CODE_CONTROL.Get_Code_Release_Level < '110509' THEN
3101
3102 CZ_CF_API.Copy_Configuration
3103 ( config_hdr_id => p_config_hdr_id
3104 ,config_rev_nbr => p_config_rev_nbr
3105 ,new_config_flag => l_new_config_flag
3106 ,out_config_hdr_id => l_config_hdr_id
3107 ,out_config_rev_nbr => l_config_rev_nbr
3108 ,Error_message => l_error_message
3109 ,Return_value => l_return_value );
3110 -- when error, returns 0, else 1
3111
3112 IF l_return_value <> 1 THEN
3116 END IF;
3113 OE_Msg_Pub.Add_Text(l_error_message);
3114 IF l_debug_level > 0 THEN
3115 oe_debug_pub.add('ERROR FROM SPC COPY: ' || L_ERROR_MESSAGE , 1 ) ;
3117 x_return_status := FND_API.G_RET_STS_ERROR;
3118 RETURN;
3119 END IF;
3120
3121 ELSE
3122
3123 CZ_Config_API_Pub.copy_configuration
3124 ( p_api_version => 1.0
3125 ,p_config_hdr_id => p_config_hdr_id
3126 ,p_config_rev_nbr => p_config_rev_nbr
3127 ,p_copy_mode => CZ_API_PUB.G_NEW_HEADER_COPY_MODE
3128 ,x_config_hdr_id => l_config_hdr_id
3129 ,x_config_rev_nbr => l_config_rev_nbr
3130 ,x_orig_item_id_tbl => l_orig_item_id_tbl
3131 ,x_new_item_id_tbl => l_new_item_id_tbl
3132 ,x_return_status => x_return_status
3133 ,x_msg_count => l_msg_count
3134 ,x_msg_data => l_msg_data);
3135
3136 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3137 OE_Msg_Pub.Add_Text(l_msg_data);
3138 IF l_debug_level > 0 THEN
3139 oe_debug_pub.add('ERROR FROM SPC NEW COPY: ' || L_MSG_DATA , 1 ) ;
3140 END IF;
3141 RETURN;
3142 END IF;
3143
3144 IF l_new_item_id_tbl.COUNT = 0 THEN
3145
3146 IF l_debug_level > 0 THEN
3147 oe_debug_pub.add('no need to update config ids ',1);
3148 END IF;
3149
3150 ELSE
3151
3152 FORALL I IN l_new_item_id_tbl.FIRST..l_new_item_id_tbl.LAST
3153
3154 UPDATE oe_order_lines
3155 SET configuration_id = l_new_item_id_tbl(I)
3156 WHERE top_model_line_id = p_top_model_line_id
3157 AND configuration_id = l_orig_item_id_tbl(I);
3158
3159 IF l_debug_level > 0 THEN
3160 oe_debug_pub.add('DONE UPDATING NEW CONFIG ITEM IDS' , 1 ) ;
3161 END IF;
3162
3163 END IF;
3164
3165 END IF;
3166
3167 ELSE
3168
3169 IF l_debug_level > 0 THEN
3170 oe_debug_pub.add('NULL CONFIG_HEADER_ID TO COPY_CONFIG , OR REMNANT SET' , 1 ) ;
3171 END IF;
3172
3173 UPDATE oe_order_lines
3174 SET configuration_id = null,
3175 config_header_id = null,
3176 config_rev_nbr = null
3177 WHERE top_model_line_id = p_top_model_line_id;
3178
3179 IF SQL%FOUND THEN
3180 IF l_debug_level > 0 THEN
3181 oe_debug_pub.add('CONFIG IDS UPDATED TO NULL' , 2 ) ;
3182 END IF;
3183 END IF;
3184 END IF;
3185
3186
3187 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110508' AND
3188 p_remnant_flag = 'Y'
3189 THEN
3190 IF l_debug_level > 0 THEN
3191 oe_debug_pub.add('1 COPY CONFIG: PACK H NEW LOGIC MI' , 1 ) ;
3192 END IF;
3193
3194 UPDATE oe_order_lines
3195 SET link_to_line_id = NULL
3196 WHERE top_model_line_id = p_top_model_line_id
3197 AND split_from_line_id is not NULL;
3198
3199 ELSE
3200
3201 UPDATE oe_order_lines
3202 SET link_to_line_id = NULL
3203 WHERE top_model_line_id = p_top_model_line_id;
3204
3205 END IF;
3206
3207 ------------------ ato_line_id ----------------------
3208
3209 /* Added this query and if stmt to fix bug 1809046.
3210 Make sure the ato_line_id will be cleared only for Sub config,
3211 Since Update_ato_line_attributes are updating only Class records
3212 The ato_line_id populated by SPLIT code in case of PTO+ATO is
3213 the line_id of the top PTO mode whcih is incorrect. Hence,
3214 it is important to update the ATO with correct ato_line_id.
3215 */
3216
3217 SELECT ato_line_id ,booked_flag, item_type_code
3218 INTO l_ato_line_id, l_booked_flag, l_error_message
3219 FROM oe_order_lines
3220 WHERE line_id = p_top_model_line_id;
3221
3222
3223 -- if ato model, do not clear ato_line_id. also for ato_item
3224 -- under pto model do not clear ato_line_id ## 1820608
3225
3226 IF p_top_model_line_id <> nvl(l_ato_line_id,-99) THEN
3227
3228 IF l_debug_level > 0 THEN
3229 oe_debug_pub.add('NULLING ATO_LINE_ID FOR ATO SUB' , 4 ) ;
3230 END IF;
3231
3232 UPDATE oe_order_lines
3233 SET ato_line_id = NULL
3234 WHERE top_model_line_id = p_top_model_line_id
3235 AND NOT (item_type_code = OE_GLOBALS.G_ITEM_OPTION AND
3236 ato_line_id = line_id);
3237 END IF;
3238
3239 IF p_remnant_flag is NULL THEN
3240
3241 IF nvl(l_booked_flag,'N') = 'N' THEN
3242 IF l_debug_level > 0 THEN
3243 oe_debug_pub.add('Order Not Booked '|| l_error_message, 5);
3244 END IF;
3245
3246 IF p_config_hdr_id is NULL AND
3247 p_config_rev_nbr is NULL AND
3248 p_configuration_id is NULL AND
3249 l_error_message = 'MODEL' THEN
3250
3251 IF l_debug_level > 0 THEN
3252 oe_debug_pub.add
3253 ('config hdr/rev/id null dont call change column',5);
3254 END IF;
3255 update_link_to_line_id
3256 ( p_top_model_line_id => p_top_model_line_id --added for bug 7261021
3257 ,p_remnant_flag => p_remnant_flag
3261 END IF;
3258 ,p_config_hdr_id => p_config_hdr_id);
3259 RETURN;
3260 END IF;
3262
3263 END IF;
3264
3265 Change_Columns(p_top_model_line_id => p_top_model_line_id,
3266 p_config_hdr_id => l_config_hdr_id,
3267 p_config_rev_nbr => l_config_rev_nbr);
3268
3269 IF l_debug_level > 0 THEN
3270 oe_debug_pub.add('NEW CONFIG_HEADER_ID: '|| L_CONFIG_HDR_ID , 5 ) ;
3271 oe_debug_pub.add('NEW CONFIG_REV_NBR: '|| L_CONFIG_REV_NBR , 5 ) ;
3272 END IF;
3273 x_return_status := FND_API.G_RET_STS_SUCCESS;
3274
3275 IF l_debug_level > 0 THEN
3276 oe_debug_pub.add('LEAVING COPY_CONFIG IN OE_CONFIG_PVT' , 1 ) ;
3277 END IF;
3278
3279 EXCEPTION
3280 WHEN OTHERS THEN
3281 IF l_debug_level > 0 THEN
3282 oe_debug_pub.add('ERROR IN COPY_CONFIG,OE_CONFIG_PVT'|| sqlerrm ,1);
3283 END IF;
3284
3285 RAISE;
3286 END Copy_Config;
3287
3288
3289 /*-----------------------------------------------------------------
3290 PROCEDURE put_hold_and_release_hold
3291 Used to put the model line on hold when configuration is invalid/
3292 incomplete after booking. Also the model is released from hold if
3293 it becomesvalid/complete, after the change.
3294
3295 Change Record:
3296 bug fix: 2162660: added check for holds call even before the
3297 apply holds call.
3298 ------------------------------------------------------------------*/
3299
3300 PROCEDURE put_hold_and_release_hold
3301 ( p_header_id IN NUMBER,
3302 p_line_id IN NUMBER,
3303 p_valid_config IN VARCHAR2,
3304 p_complete_config IN VARCHAR2,
3305 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER ,
3306 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
3307 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
3308 IS
3309 l_holds_tbl OE_HOLDS_PVT.order_tbl_type;
3310 l_hold_id NUMBER;
3311 l_hold_comment VARCHAR2(200);
3312 l_release_reason_code VARCHAR2(30);
3313 l_release_comment VARCHAR2(200);
3314 l_hold_result_out VARCHAR2(30):= 'TRUE';
3315 l_result_out VARCHAR2(30);
3316 l_error VARCHAR2(200);
3317 l_line_number NUMBER;
3318 --
3319 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3320 --
3321 BEGIN
3322
3323 Print_Time('entering put_hold_and_release_hold');
3324
3325 l_hold_id := 3;
3326
3327 l_holds_tbl(1).header_id := p_header_id;
3328 l_holds_tbl(1).line_id := p_line_id;
3329
3330 SELECT line_number || '.'|| shipment_number
3331 INTO l_line_number
3332 FROM oe_order_lines
3333 WHERE line_id = p_line_id;
3334
3335 IF l_debug_level > 0 THEN
3336 oe_debug_pub.add('OE_CONFIG_PVT , BEFORE CHECK_HOLDS ON MODEL' , 1 ) ;
3337 END IF;
3338
3339 OE_HOLDS_PUB.CHECK_HOLDS
3340 ( p_api_version => 1.0,
3341 p_line_id => p_line_id,
3342 p_hold_id => l_hold_id,
3343 x_result_out => l_hold_result_out,
3344 x_return_status => x_return_status,
3345 x_msg_count => x_msg_count,
3346 x_msg_data => x_msg_data);
3347
3348 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3349
3350 IF l_hold_result_out = FND_API.G_FALSE AND
3351 (LOWER(p_valid_config) = 'false' OR
3352 LOWER(p_complete_config) = 'false' ) THEN
3353
3354 IF l_debug_level > 0 THEN
3355 oe_debug_pub.add
3356 ('INCOMPLETE/INVALID CONFIGURATION IN A BOOKED ORDER' , 1 ) ;
3357 END IF;
3358
3359 l_hold_comment := 'Validation hold on model';
3360
3361 IF l_debug_level > 0 THEN
3362 oe_debug_pub.add('BEFORE APPLY_HOLDS ON MODEL' , 1 ) ;
3363 END IF;
3364
3365 OE_Holds_pub.apply_holds
3366 ( p_api_version => 1.0,
3367 p_order_tbl => l_holds_tbl,
3368 p_hold_id => l_hold_id,
3369 p_hold_comment => l_hold_comment,
3370 x_return_status => x_return_status,
3371 x_msg_count => x_msg_count,
3372 x_msg_data => x_msg_data );
3373
3374 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3375 IF l_debug_level > 0 THEN
3376 oe_debug_pub.add('OE_CONFIG_PVT , ERROR IN PUT HOLD' , 1 ) ;
3377 END IF;
3378 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3379 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3380 IF l_debug_level > 0 THEN
3381 oe_debug_pub.add('OE_CONFIG_PVT , ERROR IN PUT HOLD' , 1 ) ;
3382 END IF;
3383 RAISE FND_API.G_EXC_ERROR;
3384 END IF;
3385
3386 FND_MESSAGE.Set_Name('ONT', 'OE_VAL_CONFIG_HOLD_INSERT');
3387 FND_MESSAGE.Set_Token('LINE_NUMBER', l_line_number);
3388 OE_Msg_Pub.Add;
3389
3390 IF l_debug_level > 0 THEN
3391 oe_debug_pub.add('AFTER SUCCESSFUL APPLY_HOLDS ON MODEL' , 1 ) ;
3392 END IF;
3393 END IF;
3394
3398
3395 IF l_hold_result_out = FND_API.G_TRUE AND
3396 (LOWER(p_valid_config) = 'true' AND
3397 LOWER(p_complete_config) = 'true' ) THEN
3399 IF l_debug_level > 0 THEN
3400 oe_debug_pub.add('VALID/COMPLETE CONFIGURATION IN BOOKED ORDER',1);
3401 END IF;
3402
3403 l_release_reason_code := 'CZ_AUTOMATIC';
3404 l_release_comment := 'Configuration is now valid';
3405
3406 IF l_debug_level > 0 THEN
3407 oe_debug_pub.add('OE_CONFIG_PVT,BEFORE RELEASE_HOLDS ON MODEL',1);
3408 END IF;
3409
3410 OE_Holds_pub.release_holds
3411 ( p_order_tbl => l_holds_tbl,
3412 p_hold_id => l_hold_id,
3413 p_release_reason_code => l_release_reason_code,
3414 p_release_comment => l_release_comment,
3415 x_return_status => x_return_status,
3416 x_msg_count => x_msg_count,
3417 x_msg_data => x_msg_data );
3418
3419 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3420 IF l_debug_level > 0 THEN
3421 oe_debug_pub.add('OE_CONFIG_PVT , ERROR IN RELEASING HOLD',1);
3422 END IF;
3423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3424 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3425 IF l_debug_level > 0 THEN
3426 oe_debug_pub.add('OE_CONFIG_PVT ,ERROR IN RELEASING HOLD' ,1);
3427 END IF;
3428 RAISE FND_API.G_EXC_ERROR;
3429 END IF;
3430
3431 FND_MESSAGE.Set_Name('ONT', 'OE_VAL_CONFIG_HOLD_REMOVE');
3432 FND_MESSAGE.Set_Token('LINE_NUMBER', l_line_number);
3433 OE_Msg_Pub.Add;
3434
3435 IF l_debug_level > 0 THEN
3436 oe_debug_pub.add('AFTER SUCCESSFUL RELAESE_HOLDS ON MODEL' ,1);
3437 END IF;
3438 END IF;
3439
3440 ELSE -- ret status error
3441 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3442 IF l_debug_level > 0 THEN
3443 oe_debug_pub.add('OE_CONFIG_PVT , ERROR IN CHECK HOLD' , 1 ) ;
3444 END IF;
3445 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3446
3447 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3448 IF l_debug_level > 0 THEN
3449 oe_debug_pub.add('OE_CONFIG_PVT , ERROR IN CHECK HOLD' , 1 ) ;
3450 END IF;
3451 RAISE FND_API.G_EXC_ERROR;
3452 END IF;
3453 END IF; -- check holds ret status check.
3454
3455 Print_Time('leaving put_hold_and_release_hold');
3456
3457 EXCEPTION
3458 when others then
3459 IF l_debug_level > 0 THEN
3460 oe_debug_pub.add
3461 ('ERROR IN PUT_HOLD_AND_RELEASE_HOLD IN OE_CONFIG_PVT' , 1 ) ;
3462 END IF;
3463
3464 END put_hold_and_release_hold;
3465
3466 /*-----------------------------------------------------------
3467 Procedure: Explode_Bill
3468
3469 Explode the BOM for the model, so that we can use the
3470 bom_explosions table on other procedures.
3471 If the component_sequence_id is null for the model line, get
3472 it's value from bom_explosions. Also set the bom values on
3473 the model line rec so that it can be sent in for update.
3474 we will call a direct update on the model line later.
3475
3476 change record:
3477 new parameters for ER config date effectivity 2625376
3478 p_check_effective_date : if need to check model date effectivity.
3479 x_config_effective_date : null if p_check_effective_date is N
3480 x_frozen_model_bill : null if p_check_effective_date is N
3481 ------------------------------------------------------------*/
3482 Procedure Explode_Bill
3483 ( p_model_line_rec IN OUT NOCOPY OE_Order_Pub.Line_Rec_Type
3484 ,p_do_update IN BOOLEAN := TRUE
3485 ,p_check_effective_date IN VARCHAR2 := 'Y'
3486 ,x_config_effective_date OUT NOCOPY DATE
3487 ,x_frozen_model_bill OUT NOCOPY VARCHAR2
3488 ,x_return_status OUT NOCOPY VARCHAR2)
3489 IS
3490 /* variables for call to explode */
3491 l_rev_date DATE;
3492 l_validation_org NUMBER :=
3493 OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID');
3494 l_stdcompflag VARCHAR2(10)
3495 := OE_Config_Util.OE_BMX_OPTION_COMPS;
3496 l_top_item_id NUMBER;
3497 l_op_qty NUMBER;
3498 l_top_bill_sequence_id NUMBER;
3499 l_frozen_model_bill VARCHAR2(1);
3500 l_old_behavior VARCHAR2(1);
3501 l_error_code NUMBER;
3502 l_msg_count NUMBER;
3503 l_msg_data VARCHAR2(2000);
3504 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
3505 --
3506 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3507 --
3508 BEGIN
3509
3510 IF l_debug_level > 0 THEN
3511 oe_debug_pub.add( 'model line_id : '|| p_model_line_rec.line_id ,1);
3512 END IF;
3513
3514 IF p_check_effective_date = 'Y' THEN
3515
3516 OE_Config_Util.Get_Config_Effective_Date
3517 ( p_model_line_id => p_model_line_rec.line_id
3518 ,p_model_line_rec => p_model_line_rec
3519 ,x_old_behavior => l_old_behavior
3520 ,x_config_effective_date => l_rev_date
3524 RAISE FND_API.G_EXC_ERROR;
3521 ,x_frozen_model_bill => l_frozen_model_bill);
3522
3523 IF l_rev_date is NULL THEN
3525 END IF;
3526
3527 x_config_effective_date := l_rev_date;
3528 x_frozen_model_bill := l_frozen_model_bill;
3529
3530 IF l_debug_level > 0 THEN
3531 oe_debug_pub.add(x_config_effective_date||':'||x_frozen_model_bill,1);
3532 END IF;
3533
3534 ELSE
3535
3536 l_rev_date := p_model_line_rec.creation_date;
3537
3538 IF l_rev_date is NULL OR
3539 l_rev_date = FND_API.G_MISS_DATE THEN
3540 l_rev_date := sysdate;
3541 END IF;
3542
3543 END IF;
3544
3545 l_top_item_id := p_model_line_rec.inventory_item_id;
3546
3547 IF l_debug_level > 0 THEN
3548 oe_debug_pub.add('VALIDATION_ORG: ' || L_VALIDATION_ORG , 1 ) ;
3549 oe_debug_pub.add('INVENTORY ITEM ID OF MODEL: ' || L_TOP_ITEM_ID , 1 ) ;
3550 oe_debug_pub.add('CREATION DATE IS: ' || L_REV_DATE , 1 ) ;
3551 END IF;
3552
3553 -- Explode the options in Bom_Explosions
3554 IF l_debug_level > 0 THEN
3555 oe_debug_pub.add('CALL TO EXPLOSION' , 1 ) ;
3556 END IF;
3557
3558 OE_CONFIG_UTIL.Explode
3559 ( p_validation_org => l_validation_org
3560 , p_stdcompflag => l_stdcompflag
3561 , p_top_item_id => l_top_item_id
3562 , p_revdate => l_rev_date
3563 , x_msg_data => l_msg_data
3564 , x_error_code => l_error_code
3565 , x_return_status => l_return_status );
3566
3567
3568 IF l_debug_level > 0 THEN
3569 oe_debug_pub.add('AFTER CALL TO EXPLOSION , RETURN STATUS: '
3570 || L_RETURN_STATUS , 1 ) ;
3571 END IF;
3572
3573 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3574 x_return_status := l_return_status;
3575 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3576 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3577 x_return_status := l_return_status;
3578 RAISE FND_API.G_EXC_ERROR;
3579 END IF;
3580
3581 IF p_model_line_rec.component_sequence_id is null OR
3582 p_model_line_rec.component_sequence_id = FND_API.G_MISS_NUM
3583 THEN
3584
3585 BEGIN
3586 SELECT bill_sequence_id
3587 INTO p_model_line_rec.component_sequence_id
3588 FROM bom_bill_of_materials
3589 WHERE assembly_item_id = p_model_line_rec.inventory_item_id
3590 AND organization_id = l_validation_org
3591 AND alternate_bom_designator is NULL;
3592
3593 --bug3392064 start
3594 --modified 0001 to be of varchar type
3595 p_model_line_rec.sort_order := Bom_Common_Definitions.get_initial_sort_code;
3596 --bug3392064 end
3597 p_model_line_rec.component_code :=
3598 to_char(p_model_line_rec.inventory_item_id);
3599
3600 EXCEPTION
3601 WHEN NO_DATA_FOUND THEN
3602 IF l_debug_level > 0 THEN
3603 oe_debug_pub.add('EXPLODE_BILL , BILL_SEQ QUERY FAILED' , 1 ) ;
3604 END IF;
3605 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3606 END;
3607
3608 IF l_debug_level > 0 THEN
3609 oe_debug_pub.add('TOP_BILL_SEQ_ID SELECTED FROM BOM_bill_of_mat' ) ;
3610 END IF;
3611
3612 IF p_do_update THEN
3613 UPDATE oe_order_lines
3614 SET component_sequence_id = p_model_line_rec.component_sequence_id
3615 ,sort_order = p_model_line_rec.sort_order
3616 ,component_code = p_model_line_rec.component_code
3617 ,lock_control = lock_control + 1
3618 WHERE line_id = p_model_line_rec.line_id;
3619
3620 p_model_line_rec.lock_control := p_model_line_rec.lock_control + 1;
3621 ELSE
3622 IF l_debug_level > 0 THEN
3623 oe_debug_pub.add('CALL FROM VORDB' , 3 ) ;
3624 END IF;
3625 END IF;
3626
3627 END IF;
3628
3629 x_return_status := l_return_status;
3630
3631 IF l_debug_level > 0 THEN
3632 oe_debug_pub.add('TOP BILL SEQ ID'
3633 || P_MODEL_LINE_REC.COMPONENT_SEQUENCE_ID , 1 ) ;
3634 END IF;
3635
3636 EXCEPTION
3637 WHEN OTHERS THEN
3638 IF l_debug_level > 0 THEN
3639 oe_debug_pub.add('EXCEPTION IN EXPLODE_BILL: '|| SQLERRM , 1 ) ;
3640 END IF;
3641 RAISE;
3642 END Explode_Bill;
3643
3644
3645 /*-----------------------------------------------------------------
3646 PROCEDURE Modify_Included_Items
3647
3648 It is called from OEXULINB from post_lines process.
3649
3650 input params setting
3651 param1 := old ordered_quantity;
3652 param2 := new ordered_quantity;
3653 param3 := change_reason;
3654 param4 := change_comments;
3655 param5 := project_id;
3656 param6 := task_id;
3657 param7 := ship tol above;
3658 param8 := ship tol below;
3659 param9 := ship_to_org_id;
3660 param10 := operation;
3661 param11 := if complete cancellation or not
3662 param12 := line_id;
3663 param13 := top_model_line_id;
3664 date_param1 := request_date;
3665
3666 This will cascade the changes from the class to included items,
3667 on relevant columns.
3668
3669 For every top model line(param13),
3670 For every class line(param12)
3674
3671 We get all included items, set new values on thel ine_rec and
3672 call process_order.
3673 ------------------------------------------------------------------*/
3675 PROCEDURE Modify_Included_Items
3676 (x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
3677 IS
3678 I NUMBER;
3679 l_index NUMBER;
3680 l_line_rec OE_Order_Pub.Line_Rec_Type
3681 := OE_Order_Pub.G_Miss_Line_Rec;
3682 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
3683 l_control_rec OE_GLOBALS.Control_Rec_Type;
3684 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3685
3686
3687 CURSOR inc_items(p_link_to_line_id NUMBER,
3688 p_top_model_line_id NUMBER)
3689 IS
3690 SELECT line_id, ordered_quantity
3691 FROM oe_order_lines
3692 WHERE top_model_line_id = p_top_model_line_id
3693 AND link_to_line_id = p_link_to_line_id
3694 AND item_type_code = OE_GLOBALS.G_ITEM_INCLUDED;
3695
3696 --
3697 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3698 --
3699 BEGIN
3700 IF l_debug_level > 0 THEN
3701 oe_debug_pub.add('ENTERING MODIFY_INCLUDED_ITEMS' , 1 ) ;
3702 END IF;
3703
3704 l_index := 0;
3705 I := OE_MODIFY_INC_ITEMS_TBL.FIRST;
3706 WHILE I is not NULL
3707 LOOP
3708 IF l_debug_level > 0 THEN
3709 oe_debug_pub.add( I||'CLASS/KIT '
3710 || OE_MODIFY_INC_ITEMS_TBL ( I ) .PARAM12 , 2 ) ;
3711 oe_debug_pub.add
3712 ('OPERATION '|| OE_MODIFY_INC_ITEMS_TBL ( I ) .PARAM10 , 3 ) ;
3713 END IF;
3714
3715 FOR l_rec in inc_items(OE_MODIFY_INC_ITEMS_TBL(I).param12,
3716 OE_MODIFY_INC_ITEMS_TBL(I).param13)
3717 LOOP
3718 IF l_debug_level > 0 THEN
3719 oe_debug_pub.add('INC ITEM '|| L_REC.LINE_ID , 1 ) ;
3720 END IF;
3721
3722 l_line_rec.line_id := l_rec.line_id;
3723 l_line_rec.operation := OE_MODIFY_INC_ITEMS_TBL(I).param10;
3724
3725 -- 1. ordered_quantity.
3726
3727 IF OE_MODIFY_INC_ITEMS_TBL(I).param2 <> FND_API.G_MISS_NUM THEN
3728
3729 -- old ordered qty of parent can not be 0, so no divide by 0.
3730 --bug3993709
3731 l_line_rec.ordered_quantity :=
3732 (l_rec.ordered_quantity/OE_MODIFY_INC_ITEMS_TBL(I).param1) *
3733 OE_MODIFY_INC_ITEMS_TBL(I).param2 ;
3734 l_line_rec.change_reason := OE_MODIFY_INC_ITEMS_TBL(I).param3;
3735 l_line_rec.change_comments := OE_MODIFY_INC_ITEMS_TBL(I).param4;
3736
3737 END IF; -- Quantity check.
3738
3739
3740 -- 2. project and task.
3741
3742 IF nvl(OE_MODIFY_INC_ITEMS_TBL(I).param5, -1) <> FND_API.G_MISS_NUM THEN
3743 l_line_rec.project_id := OE_MODIFY_INC_ITEMS_TBL(I).param5;
3744 END IF; -- project.
3745
3746 IF nvl(OE_MODIFY_INC_ITEMS_TBL(I).param6, -1) <> FND_API.G_MISS_NUM THEN
3747 l_line_rec.task_id := OE_MODIFY_INC_ITEMS_TBL(I).param6;
3748 END IF; -- task.
3749
3750
3751 -- 3. ship_tolerance_above and below
3752
3753 IF nvl(OE_MODIFY_INC_ITEMS_TBL(I).param7, -1) <> FND_API.G_MISS_NUM THEN
3754 l_line_rec.ship_tolerance_above := OE_MODIFY_INC_ITEMS_TBL(I).param7;
3755 END IF; -- ship_tolerance_above.
3756
3757 IF nvl(OE_MODIFY_INC_ITEMS_TBL(I).param8, -1) <> FND_API.G_MISS_NUM THEN
3758 l_line_rec.ship_tolerance_below := OE_MODIFY_INC_ITEMS_TBL(I).param8;
3759 END IF; -- ship_tolerance_below.
3760
3761
3762 -- 4. ship_to and request_date
3763
3764 IF nvl(OE_MODIFY_INC_ITEMS_TBL(I).param9, -1) <> FND_API.G_MISS_NUM THEN
3765 l_line_rec.ship_to_org_id := OE_MODIFY_INC_ITEMS_TBL(I).param9;
3766 END IF; -- ship_to_org_id.
3767
3768 IF nvl(OE_MODIFY_INC_ITEMS_TBL(I).date_param1, sysdate)
3769 <> FND_API.G_MISS_DATE THEN
3770 l_line_rec.request_date := OE_MODIFY_INC_ITEMS_TBL(I).date_param1;
3771 END IF; -- request_date.
3772
3773
3774 IF l_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE AND
3775 l_line_rec.ordered_quantity = 0 AND
3776 OE_MODIFY_INC_ITEMS_TBL(I).param11 = 'N'
3777 THEN
3778 IF l_debug_level > 0 THEN
3779 oe_debug_pub.add('DELETE SINCE NOT FULL CANCEL' , 3 ) ;
3780 END IF;
3781 l_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
3782 END IF;
3783
3784 IF l_debug_level > 0 THEN
3785 oe_debug_pub.add('QTY - OP '|| L_LINE_REC.ORDERED_QUANTITY
3786 || L_LINE_REC.OPERATION , 1 ) ;
3787 END IF;
3788
3789 l_index := l_index + 1;
3790 l_line_tbl(l_index) := l_line_rec;
3791 l_line_rec := OE_Order_Pub.G_MISS_LINE_REC;
3792
3793
3794 END LOOP; -- end cursor
3795 I := OE_MODIFY_INC_ITEMS_TBL.NEXT(I);
3796 END LOOP;
3797
3798 l_control_rec.process := FALSE;
3799
3800 oe_config_pvt.Call_Process_Order
3801 ( p_line_tbl => l_line_tbl
3802 ,p_control_rec => l_control_rec
3803 ,x_return_status => l_return_status);
3804
3805 OE_GLOBALS.G_CASCADING_REQUEST_LOGGED := TRUE;
3806
3807 x_return_status := l_return_status;
3811
3808 IF l_debug_level > 0 THEN
3809 oe_debug_pub.add('EXITING MODIFY_INCLUDED_ITEMS'|| L_RETURN_STATUS , 1 ) ;
3810 END IF;
3812 EXCEPTION
3813 WHEN OTHERS THEN
3814 IF l_debug_level > 0 THEN
3815 oe_debug_pub.add('EXCEPTION IN MODIFY_INCLUDED_ITEMS'|| SQLERRM , 1 ) ;
3816 END IF;
3817 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3818 RAISE;
3819 END;
3820
3821
3822 /*-----------------------------------------------------------------
3823 PROCEDURE Included_Items_DML
3824 this procedure will not be used anymore,
3825 modify_included_items is used and
3826 that is called from OEXULINB
3827 __________________________________________________________________*/
3828
3829 PROCEDURE Included_Items_DML
3830 ( p_x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
3831 ,p_top_model_line_id IN NUMBER
3832 ,p_ui_flag IN VARCHAR2
3833 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
3834 IS
3835 I NUMBER;
3836 l_line_rec OE_Order_Pub.Line_Rec_Type;
3837 l_found BOOLEAN;
3838 l_ordered_qty NUMBER;
3839 l_component_sequence_id NUMBER;
3840 l_creation_date DATE;
3841 l_length NUMBER;
3842 l_code VARCHAR2(1000);
3843 l_index NUMBER;
3844 l_current_qty NUMBER;
3845 l_inventory_item_id NUMBER;
3846 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3847
3848 CURSOR included_items IS
3849 SELECT line_id, component_code, link_to_line_id, ordered_quantity
3850 FROM oe_order_lines
3851 WHERE item_type_code = 'INCLUDED'
3852 AND link_to_line_id <> top_model_line_id
3853 AND top_model_line_id = p_top_model_line_id;
3854
3855 --
3856 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3857 --
3858 BEGIN
3859 IF l_debug_level > 0 THEN
3860 oe_debug_pub.add('ENTERING INCLUDED_ITEMS_DML '|| P_UI_FLAG , 1 ) ;
3861 END IF;
3862
3863 l_index := p_x_line_tbl.LAST;
3864 l_component_sequence_id := NULL;
3865
3866 FOR l_rec in included_items
3867 LOOP
3868 IF l_debug_level > 0 THEN
3869 oe_debug_pub.add('INCLUDED ITEM: '|| L_REC.COMPONENT_CODE , 2 ) ;
3870 END IF;
3871
3872 IF l_component_sequence_id is NULL THEN
3873
3874 SELECT component_sequence_id, creation_date
3875 INTO l_component_sequence_id, l_creation_date
3876 FROM oe_order_lines
3877 WHERE line_id = p_top_model_line_id;
3878 END IF;
3879
3880 l_found := FALSE;
3881
3882 IF p_ui_flag = 'Y' THEN
3883 I := p_x_line_tbl.FIRST;
3884 WHILE I is not null AND NOT l_found
3885 LOOP
3886
3887 l_length := LENGTH(p_x_line_tbl(I).component_code);
3888 l_code := SUBSTR(p_x_line_tbl(I).component_code,
3889 INSTR(p_x_line_tbl(I).component_code, '-', -1) + 1, l_length);
3890
3891 IF SUBSTR(l_rec.component_code, 1,
3892 INSTR(l_rec.component_code, '-') -1) = l_code
3893 THEN
3894 l_line_rec := OE_Order_Pub.G_Miss_Line_Rec;
3895 l_line_rec.operation := p_x_line_tbl(I).operation;
3896 l_line_rec.line_id := l_rec.line_id;
3897
3898 l_line_rec.ordered_quantity := p_x_line_tbl(I).ordered_quantity;
3899 l_line_rec.change_reason := 'SYSTEM';
3900 l_line_rec.change_comments := 'Included Items updation';
3901 l_index := l_index + 1;
3902 p_x_line_tbl(l_index) := l_line_rec;
3903 l_found := TRUE;
3904
3905 IF l_debug_level > 0 THEN
3906 oe_debug_pub.add('PARENT WAS' || L_LINE_REC.OPERATION , 1 ) ;
3907 oe_debug_pub.add('NEW QTY ' || L_LINE_REC.ORDERED_QUANTITY ,1);
3908 END IF;
3909 END IF;
3910
3911 I := p_x_line_tbl.NEXT(I);
3912 IF l_debug_level > 0 THEN
3913 oe_debug_pub.add('COUNT: ' || P_X_LINE_TBL.COUNT || I , 1 ) ;
3914 END IF;
3915 END LOOP;
3916 END IF;
3917
3918 IF NOT l_found THEN
3919 -- already updated/deleted and batch validation logged.
3920 BEGIN
3921 SELECT ordered_quantity
3922 INTO l_ordered_qty
3923 FROM oe_order_lines
3924 WHERE top_model_line_id = p_top_model_line_id
3925 AND line_id = l_rec.link_to_line_id;
3926
3927 l_line_rec := OE_Order_Pub.G_Miss_Line_Rec;
3928 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
3929 l_line_rec.line_id := l_rec.line_id;
3930 l_line_rec.ordered_quantity := l_ordered_qty; -- ratio??***
3931 l_line_rec.change_reason := 'SYSTEM';
3932 l_line_rec.change_comments := 'Included Items updation';
3933 l_index := l_index + 1;
3934 p_x_line_tbl(l_index) := l_line_rec;
3935
3936 IF l_debug_level > 0 THEN
3937 oe_debug_pub.add('PARENT WAS UPDATED' || L_ORDERED_QTY , 3 ) ;
3938 END IF;
3939
3940 EXCEPTION
3941 WHEN NO_DATA_FOUND THEN
3942 l_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
3943 l_line_rec.line_id := l_rec.line_id;
3944 l_index := l_index + 1;
3948 oe_debug_pub.add('PARENT WAS DELETED' , 3 ) ;
3945 p_x_line_tbl(l_index) := l_line_rec;
3946
3947 IF l_debug_level > 0 THEN
3949 END IF;
3950
3951 WHEN OTHERS THEN
3952 IF l_debug_level > 0 THEN
3953 oe_debug_pub.add('COULD NOT SELECT PARENT QTY' , 1 ) ;
3954 END IF;
3955 RAISE;
3956 END;
3957
3958 END IF;
3959
3960 END LOOP;
3961
3962 x_return_status := l_return_status;
3963
3964 IF l_debug_level > 0 THEN
3965 oe_debug_pub.add('EXITING INCLUDED_ITEMS_DML' , 1 ) ;
3966 END IF;
3967 EXCEPTION
3968 WHEN OTHERS THEN
3969 IF l_debug_level > 0 THEN
3970 oe_debug_pub.add('EXCEPTION IN INCLUDED_ITEMS_DML'|| SQLERRM , 1 ) ;
3971 END IF;
3972
3973 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3974 RAISE;
3975 END Included_Items_DML;
3976
3977
3978 /*-----------------------------------------------------------------
3979 Procedure: Copy_Config1
3980 Not Used.
3981 ------------------------------------------------------------------*/
3982 Procedure Copy_Config1(p_config_hdr_id IN NUMBER ,
3983 p_config_rev_nbr IN NUMBER ,
3984 x_config_hdr_id OUT NOCOPY /* file.sql.39 change */ NUMBER ,
3985 x_config_rev_nbr OUT NOCOPY /* file.sql.39 change */ NUMBER ,
3986 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
3987 IS
3988 l_return_value number;
3989 l_error_message varchar2(100);
3990 l_new_config_flag varchar2(1) := '1';
3991 l_flag varchar2(1) := 'Y';
3992 l_config_hdr_id number;
3993 l_config_rev_nbr number;
3994
3995 --
3996 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3997 --
3998 BEGIN
3999
4000 IF l_debug_level > 0 THEN
4001 oe_debug_pub.add('ENTERING COPY_CONFIG IN OE_CONFIG_PVT' , 1 ) ;
4002 END IF;
4003
4004 IF p_config_hdr_id is not null AND
4005 p_config_rev_nbr is not null THEN
4006
4007 CZ_CF_API.Copy_Configuration
4008 (config_hdr_id => p_config_hdr_id ,
4009 config_rev_nbr => p_config_rev_nbr ,
4010 new_config_flag => l_new_config_flag,
4011 out_config_hdr_id => l_config_hdr_id ,
4012 out_config_rev_nbr => l_config_rev_nbr ,
4013 Error_message => l_error_message ,
4014 Return_value => l_return_value );
4015
4016 -- when error, returns 0, else 1
4017
4018 IF l_return_value <> 1 THEN
4019 OE_Msg_Pub.Add_Text(l_error_message);
4020 IF l_debug_level > 0 THEN
4021 oe_debug_pub.add('ERROR FROM SPC COPY: ' || L_ERROR_MESSAGE , 1 ) ;
4022 END IF;
4023 x_return_status :=FND_API.G_RET_STS_ERROR;
4024
4025 ELSE
4026 IF l_debug_level > 0 THEN
4027 oe_debug_pub.add('NEW CONFIG_HEADER_ID: '|| L_CONFIG_HDR_ID , 1 ) ;
4028 END IF;
4029 IF l_debug_level > 0 THEN
4030 oe_debug_pub.add('NEW CONFIG_REV_NBR: '|| L_CONFIG_REV_NBR , 1 ) ;
4031 END IF;
4032 x_config_hdr_id := l_config_hdr_id;
4033 x_config_rev_nbr := l_config_rev_nbr;
4034 x_return_status := FND_API.G_RET_STS_SUCCESS;
4035 END IF;
4036
4037 ELSE
4038 IF l_debug_level > 0 THEN
4039 oe_debug_pub.add('NULL CONFIG_HDR/REV_NBR IS PASSED TO COPY_CONFIG',1);
4040 END IF;
4041
4042 END IF;
4043
4044 IF l_debug_level > 0 THEN
4045 oe_debug_pub.add('LEAVING COPY_CONFIG IN OE_CONFIG_PVT' , 1 ) ;
4046 END IF;
4047
4048 EXCEPTION
4049 when others then
4050 IF l_debug_level > 0 THEN
4051 oe_debug_pub.add('ERROR IN COPY_CONFIG IN OE_CONFIG_PVT' , 1 ) ;
4052 END IF;
4053
4054 END Copy_Config1;
4055
4056
4057 /*--------------------------------------------------------
4058 PROCEDURE Is_Cancel_OR_Delete
4059
4060 --------------------------------------------------------*/
4061 PROCEDURE Is_Cancel_OR_Delete
4062 ( p_line_id IN NUMBER
4063 ,p_change_reason IN VARCHAR2 := null
4064 ,p_change_comments IN VARCHAR2 := null
4065 ,x_cancellation OUT NOCOPY BOOLEAN
4066 ,x_line_rec IN OUT NOCOPY OE_Order_Pub.line_rec_type)
4067 IS
4068 l_return_status VARCHAR2(1);
4069 I NUMBER;
4070 l_msg_count1 NUMBER;
4071 l_msg_count2 NUMBER;
4072 l_sec_result NUMBER;
4073 --
4074 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4075 --
4076 BEGIN
4077
4078 x_cancellation := FALSE;
4079 l_msg_count1 := OE_Msg_Pub.Count_Msg;
4080
4081 OE_LINE_UTIL.Query_Row(p_line_id => p_line_id
4082 ,x_line_rec => x_line_rec);
4083
4084
4085
4086 x_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
4087
4088 OE_Line_Security.Entity
4089 ( p_line_rec => x_line_rec
4090 ,x_result => l_sec_result
4091 ,x_return_status => l_return_status);
4092
4093 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4094 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4095 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4096 RAISE FND_API.G_EXC_ERROR;
4097 END IF;
4098
4099 l_msg_count2 := OE_Msg_Pub.Count_Msg;
4100
4101
4102 IF l_msg_count2 > l_msg_count1 THEN
4103 -- need to remove the messages.
4104 IF l_msg_count1 = 0 THEN
4105 OE_Msg_Pub.Delete_Msg;
4106
4107 ELSE
4108 I := 0;
4109
4110 WHILE l_msg_count2 - l_msg_count1 - I > 0
4111 LOOP
4112
4113 OE_Msg_Pub.Delete_Msg(l_msg_count2 - I);
4114
4115 oe_debug_pub.add(OE_Msg_Pub.g_msg_index || '-'
4116 ||l_msg_count1 || '-'|| l_msg_count2 || '-' ||I, 3 );
4117
4118 I := I + 1;
4119 END LOOP;
4120 END IF;
4121 END IF;
4122
4123 IF l_sec_result = OE_PC_GLOBALS.YES THEN
4124 IF l_debug_level > 0 THEN
4125 oe_debug_pub.add('do cancellation hence update with 0', 3 );
4126 END IF;
4127
4128 x_line_rec.ordered_quantity := 0;
4129 x_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
4130
4131 IF p_change_reason is NOT NULL THEN
4132 x_line_rec.change_reason := p_change_reason;
4133 END IF;
4134
4135 IF p_change_comments is NOT NULL THEN
4136 x_line_rec.change_comments := p_change_comments;
4137 END IF;
4138
4139 x_cancellation := TRUE;
4140 ELSE
4141 IF l_debug_level > 0 THEN
4142 oe_debug_pub.add('no cancellation, delete ok ', 3 ) ;
4143 END IF;
4144 END IF;
4145
4146 IF l_debug_level > 0 THEN
4147 oe_debug_pub.add('option operation '|| x_line_rec.operation, 3);
4148 END IF;
4149 EXCEPTION
4150 when OTHERS then
4151 IF l_debug_level > 0 THEN
4152 oe_debug_pub.add('error in Is_Cancel_OR_Delete '|| sqlerrm, 3);
4153 END IF;
4154 RAISE;
4155 END Is_Cancel_OR_Delete;
4156
4157
4158 /*--------------------------------------------------------
4159 PROCEDURE Print_Time
4160
4161 --------------------------------------------------------*/
4162
4163 PROCEDURE Print_Time(p_msg IN VARCHAR2)
4164 IS
4165 l_time VARCHAR2(100);
4166 --
4167 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4168 --
4169 BEGIN
4170 l_time := to_char (new_time (sysdate, 'PST', 'EST'),
4171 'DD-MON-YY HH24:MI:SS');
4172 IF l_debug_level > 0 THEN
4173 oe_debug_pub.add( P_MSG || ': '|| L_TIME , 1 ) ;
4174 END IF;
4175 END Print_Time;
4176
4177
4178 END Oe_Config_Pvt;
[Home] [Help]