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