[Home] [Help]
PACKAGE BODY: APPS.OE_SHIP_CONFIRMATION_PUB
Source
1 PACKAGE BODY OE_Ship_Confirmation_Pub AS
2 /* $Header: OEXPSHCB.pls 120.47.12020000.12 2013/04/09 08:02:22 spothula ship $ */
3
4
5 -- Global constant holding the package name
6
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Ship_Confirmation_Pub';
8 G_SKIP_SHIP VARCHAR2(30) := OE_GLOBALS.G_COMPLETE_ACTIVITY; -- Bug 10032407
9
10 Type ship_confirm_models is table of NUMBER
11 index by binary_integer;
12
13 Type ship_confirm_sets is table of NUMBER
14 index by binary_integer;
15
16 g_non_shippable_rec Ship_Line_Rec_Type;
17
18 -- bug 4170119
19 PROCEDURE Handle_Bulk_Mode_Per_Order
20 ( p_ship_line_rec IN OUT NOCOPY Ship_Line_Rec_Type
21 ,p_line_adj_rec IN Ship_Adj_Rec_Type
22 ,p_start_index IN NUMBER
23 ,p_end_index IN NUMBER
24 ,x_return_status OUT NOCOPY VARCHAR2);
25
26 PROCEDURE Ship_Confirm_Split_Lines
27 ( p_ship_line_rec IN OUT NOCOPY Ship_Line_Rec_Type
28 ,p_index IN NUMBER);
29
30 PROCEDURE Process_Requests;
31
32 -- Start of Comments
33 -- API name Ship_Confirm
34 -- Type Public
35 -- Version Current version = 1.0
36 -- Initial version = 1.0
37
38 PROCEDURE Ship_Confirm
39 (
40 p_api_version_number IN NUMBER
41 , p_line_tbl IN OE_Order_PUB.Line_Tbl_Type
42 , p_line_adj_tbl IN OE_ORDER_PUB.Line_adj_Tbl_Type
43 , p_req_qty_tbl IN Req_Quantity_Tbl_Type
44 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
45 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
46 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
47 )
48 IS
49 l_api_version_number CONSTANT NUMBER := 1.0;
50 l_line_rec OE_Order_PUB.Line_Rec_Type;
51 l_line_adj_rec OE_ORDER_PUB.Line_adj_rec_type;
52
53 l_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
54 l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
55 l_temp_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
56 l_notify BOOLEAN := FALSE;
57
58 l_result_out VARCHAR2(30);
59
60 l_actual_shipment_date DATE;
61 l_shipped_quantity NUMBER;
62
63 l_shipping_quantity NUMBER;
64 l_shipping_quantity_uom VARCHAR2(3);
65 l_ordered_quantity NUMBER;
66 l_order_quantity_uom VARCHAR2(3);
67
68 l_inventory_item_id NUMBER;
69 l_ship_from_org_id NUMBER;
70 l_item_type_code VARCHAR2(30);
71 l_ship_set_id NUMBER;
72 l_ship_set_id_mod NUMBER; -- Bug 8795918
73 l_top_model_line_id NUMBER;
74 l_ato_line_id NUMBER;
75 l_model_remnant_flag VARCHAR2(1);
76 l_make_remnant VARCHAR2(1) := 'N'; --bug 4701487
77 l_header_id NUMBER;
78 l_ship_tolerance_below NUMBER;
79 l_over_ship_reason_code VARCHAR2(30);
80
81 l_ship_tolerance_below_upd NUMBER;
82 l_over_ship_reason_code_upd VARCHAR2(30);
83
84 /* l_OPM_shipped_quantity NUMBER(19,9); -- INVCONV
85 l_OPM_shipping_quantity_uom VARCHAR2(4);
86 l_OPM_order_quantity_uom VARCHAR2(4); */
87 l_item_rec OE_ORDER_CACHE.item_rec_type;
88 l_status VARCHAR2(1);
89
90 l_count NUMBER;
91 l_msg_data VARCHAR2(500);
92
93 l_temp_shipped_quantity NUMBER;
94 l_validated_quantity NUMBER;
95 l_primary_quantity NUMBER;
96 l_qty_return_status VARCHAR2(1);
97 l_return_status VARCHAR2(1);
98 l_smc_flag VARCHAR2(1);
99 l_price_adjustment_id NUMBER;
100
101 TYPE Ship_Confirm_Rec IS RECORD
102 (
103 type_id NUMBER
104 , ship_confirm_type VARCHAR2(30)
105 );
106
107 TYPE Ship_Confirm_Table IS TABLE OF Ship_Confirm_rec
108 INDEX BY BINARY_INTEGER;
109
110 l_ship_confirm_tbl ship_confirm_table;
111 l_ship_confirm_index NUMBER;
112
113 l_temp_requested_quantity NUMBER;
114 l_temp_requested_quantity2 NUMBER; -- INVCONV
115
116 -- HW addeded variables for DUAL (OPM)
117 -- l_OPM_requested_quantity NUMBER; INVCONV
118 l_ordered_quantity2 NUMBER;
119 l_split_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
120 l_line_set_id NUMBER;
121 l_control_rec OE_GLOBALS.Control_Rec_Type;
122 l_set_recursion VARCHAR2(1) := 'N';
123
124 l_calculate_price_flag VARCHAR2(1);
125 -- Variables for update global picture jolin
126 l_notify_index NUMBER;
127 l_loop_index NUMBER;
128
129
130 /* -- HW OPM BUG#: 2415731 local variables to be used for lot specific conversion
131 l_lot_number VARCHAR2(3);
132 l_sublot_number VARCHAR2(32);
133 l_lot_id NUMBER; */
134 l_lot_number VARCHAR2(80);
135
136 CURSOR LOT_INFO (p_line_id NUMBER ) IS
137 SELECT WDD.LOT_NUMBER,
138 -- WDD.SUBLOT_NUMBER, INVCONV
139 WDD.SHIPPED_QUANTITY
140
141 FROM WSH_DELIVERY_DETAILS WDD
142
143 WHERE WDD.SOURCE_LINE_ID = p_line_Id
144 AND WDD.RELEASED_STATUS ='C'
145 AND WDD.SOURCE_CODE='OE'
146 AND NVL ( WDD.OE_INTERFACED_FLAG , 'N' ) <> 'Y';
147
148 LOT LOT_INFO%ROWTYPE;
149
150 l_temp_dual_shipped_qty NUMBER; -- INVCONV
151
152 -- HW OPM end of changes for 2415731
153 --bug3480047 start
154 l_shippable_lines NUMBER;
155 --bug3480047 contd
156
157 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
158 l_line_id_mod NUMBER; -- Bug 8795918
159 l_top_model_line_id_mod NUMBER; -- Bug 8795918
160
161 BEGIN
162
163 IF l_debug_level > 0 THEN
164 oe_debug_pub.add( 'ENTERING OE_SHIP_CONFIRM.SHIP_CONFIRM' , 1 ) ;
165 END IF;
166
167 IF OE_GLOBALS.G_ASO_INSTALLED IS NULL THEN
168 OE_GLOBALS.G_ASO_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(697);
169 END IF;
170
171 IF OE_GLOBALS.G_EC_INSTALLED IS NULL THEN
172 OE_GLOBALS.G_EC_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(175);
173 END IF;
174
175 IF l_debug_level > 0 THEN
176 oe_debug_pub.add( 'NUMBER OF RECORDS LINE/ADJ/REQ :
177 '||P_LINE_TBL.COUNT||'/'||P_LINE_ADJ_TBL.COUNT||'/'||P_REQ_QTY_TBL.COUNT , 3 ) ;
178 END IF;
179
180 IF p_req_qty_tbl.count > 0 THEN
181
182 FOR J IN 1..p_req_qty_tbl.count
183
184 LOOP
185 -- HW Retrieve ship_from_org_id,inventory_item_id and ordered_quantity2
186 -- to determine if the line is dual
187 SELECT ordered_quantity,
188 order_quantity_uom,
189 inventory_item_id,
190 top_model_line_id,
191 ato_line_id,
192 item_type_code,
193 line_set_id,
194 ship_from_org_id,
195 ordered_quantity2
196 INTO l_ordered_quantity,
197 l_order_quantity_uom,
198 l_inventory_item_id,
199 l_top_model_line_id,
200 l_ato_line_id,
201 l_item_type_code,
202 l_line_set_id,
203 l_ship_from_org_id,
204 l_ordered_quantity2
205 FROM OE_ORDER_LINES
206 WHERE line_id = p_req_qty_tbl(J).line_id;
207
208 IF l_debug_level > 0 THEN
209 oe_debug_pub.add( 'REQUESTED/ORDER QUANTITY : '||P_REQ_QTY_TBL ( J ) .REQUESTED_QUANTITY||'/'||L_ORDERED_QUANTITY , 3
210 ) ;
211 END IF;
212 IF l_debug_level > 0 THEN
213 oe_debug_pub.add( 'ORD UOM/SHIPPING UOM : '||L_ORDER_QUANTITY_UOM||'/'||P_REQ_QTY_TBL ( J ) .SHIPPING_QUANTITY_UOM , 3
214 ) ;
215 END IF;
216 IF l_debug_level > 0 THEN -- INVCONV
217 oe_debug_pub.add( 'SHIPPING UOM2 : '||P_REQ_QTY_TBL ( J ) .SHIPPING_QUANTITY_UOM2 , 3 ) ;
218 END IF;
219
220 -- HW print OPM qty2 INVCONV DELETE
221 IF l_debug_level > 0 THEN
222 oe_debug_pub.add( 'REQUESTED/ORDER QUANTITY2 : '||P_REQ_QTY_TBL ( J ) .REQUESTED_QUANTITY2||'/'||L_ORDERED_QUANTITY2 ,
223 3 ) ;
224 END IF;
225
226 IF nvl(l_top_model_line_id,-1) <> nvl(l_ato_line_id,-1) AND
227 l_top_model_line_id IS NOT NULL THEN
228
229 IF l_debug_level > 0 THEN
230 oe_debug_pub.add( 'PTO MODEL LINE WITH REQUESTED QUANTITY NO SPLIT' , 3 ) ;
231 END IF;
232 GOTO END_REQ_QTY;
233
234 END IF;
235
236 IF l_order_quantity_uom <> p_req_qty_tbl(J).shipping_quantity_uom THEN
237 /* -- HW Need to branch -- INVCONV NOT NEEDED NOW
238
239 IF oe_line_util.Process_Characteristics
240 (l_inventory_item_id
241 ,l_ship_from_org_id
242 ,l_item_rec) THEN
243 -- Get the OPM equivalent code for order_quantity_uom
244 =====================================================
245 IF l_debug_level > 0 THEN
246 oe_debug_pub.add( 'OPM PROCESS SHIPPING UPDATE ' , 1 ) ;
247 END IF;
248 l_temp_shipped_quantity := GMI_Reservation_Util.get_opm_converted_qty(
249 p_apps_item_id => l_inventory_item_id,
250 p_organization_id => l_ship_from_org_id,
251 p_apps_from_uom => p_req_qty_tbl(J).shipping_quantity_uom,
252 p_apps_to_uom => l_order_quantity_uom,
253 p_original_qty => p_req_qty_tbl(J).requested_quantity);
254 -- LG 4-17-03, bug 2900072
255 l_temp_requested_quantity := l_temp_shipped_quantity ;
256 -- HW This line is discrete
257 ELSE */
258
259
260 l_temp_requested_quantity := OE_Order_Misc_Util.Convert_Uom
261 (
262 l_inventory_item_id,
263 p_req_qty_tbl(J).shipping_quantity_uom,
264 l_order_quantity_uom,
265 p_req_qty_tbl(J).requested_quantity
266 );
267 IF l_debug_level > 0 THEN
268 oe_debug_pub.add( 'CONVERTED REQUESTED QUANTITY : '|| TO_CHAR ( L_TEMP_REQUESTED_QUANTITY ) , 1 ) ;
269 END IF;
270
271 -- END IF; -- HW end of branching INVCONV
272
273 ELSE
274
275 l_temp_requested_quantity := p_req_qty_tbl(J).requested_quantity;
276 l_temp_requested_quantity2 := p_req_qty_tbl(J).requested_quantity2; -- INVCONV
277 END IF;
278
279 IF l_temp_requested_quantity <> trunc(l_temp_requested_quantity) THEN
280
281 Inv_Decimals_PUB.Validate_Quantity
282 (
283 p_item_id => l_inventory_item_id,
284 p_organization_id => OE_Sys_Parameters.value('MASTER_ORGANIZATION_ID'),
285 p_input_quantity => l_temp_requested_quantity,
286 p_uom_code => l_order_quantity_uom,
287 x_output_quantity => l_validated_quantity,
288 x_primary_quantity => l_primary_quantity,
289 x_return_status => l_qty_return_status
290 );
291
292 IF l_debug_level > 0 THEN
293 oe_debug_pub.add( 'RETURN STATUS FROM INV API : '||L_QTY_RETURN_STATUS , 1 ) ;
294 END IF;
295 IF l_qty_return_status = 'W' THEN
296
297 l_temp_requested_quantity := l_validated_quantity;
298
299 END IF;
300
301 END IF;
302
303 IF l_debug_level > 0 THEN
304 oe_debug_pub.add( 'FINAL REQUESTED QUANTITY : '|| TO_CHAR ( L_TEMP_REQUESTED_QUANTITY ) , 1 ) ;
305 oe_debug_pub.add( 'FINAL REQUESTED QUANTITY2 : '|| TO_CHAR ( L_TEMP_REQUESTED_QUANTITY2 ) , 1 ) ;
306 END IF;
307
308 IF l_ordered_quantity <= l_temp_requested_quantity THEN
309
310 IF l_debug_level > 0 THEN
311 oe_debug_pub.add( 'ERROR !!! CAN NOT SPLIT THE LINE ORDERED QUANTTITY <= REQUESTED QTY ' , 3 ) ;
312 END IF;
313 GOTO END_REQ_QTY;
314
315 END IF;
316
317 -- Assign the first record of the table for process order for update
318 -- of the ordered quantity.
319
320 l_split_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
321
322 IF l_ato_line_id IS NOT NULL and l_item_type_code <> 'STANDARD' THEN
323 l_split_line_tbl(1).line_id := l_top_model_line_id;
324 IF l_debug_level > 0 THEN
325 oe_debug_pub.add( 'SPLIT THE TOP MODEL : '||L_SPLIT_LINE_TBL ( 1 ) .LINE_ID , 3 ) ;
326 END IF;
327 ELSE
328 IF l_debug_level > 0 THEN
329 oe_debug_pub.add( 'SPLIT THE LINE : '||L_SPLIT_LINE_TBL ( 1 ) .LINE_ID , 3 ) ;
330 END IF;
331 l_split_line_tbl(1).line_id := p_req_qty_tbl(J).line_id;
332 END IF;
333
334 l_split_line_tbl(1).line_set_id := l_line_set_id;
335 l_split_line_tbl(1).ordered_quantity := l_temp_requested_quantity;
336 -- HW added qty2 for OPM
337 -- l_split_line_tbl(1).ordered_quantity2 := nvl(p_req_qty_tbl(J).requested_quantity2,0); -- INVCONV
338 l_split_line_tbl(1).ordered_quantity2 := nvl(l_temp_requested_quantity2, 0); -- INVCONV
339 l_split_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
340 l_split_line_tbl(1).split_action_code := 'SPLIT';
341 l_split_line_tbl(1).split_by := 'SYSTEM';
342 l_split_line_tbl(1).change_reason := 'SYSTEM';
343 l_split_line_tbl(1).ship_from_org_id := l_ship_from_org_id; --9733938
344
345 -- Assign the second record of the table for process order for
346 -- create of new line.
347
348 l_split_line_tbl(2) := OE_ORDER_PUB.G_MISS_LINE_REC;
349
350 IF l_ato_line_id IS NOT NULL and l_item_type_code <> 'STANDARD' THEN
351 l_split_line_tbl(2).split_from_line_id := l_top_model_line_id;
352 IF l_debug_level > 0 THEN
353 oe_debug_pub.add( 'SPLIT FROM THE TOP MODEL : '||L_SPLIT_LINE_TBL ( 1 ) .LINE_ID , 3 ) ;
354 END IF;
355 ELSE
356 IF l_debug_level > 0 THEN
357 oe_debug_pub.add( 'SPLIT FROM THE LINE : '||L_SPLIT_LINE_TBL ( 1 ) .LINE_ID , 3 ) ;
358 END IF;
359 l_split_line_tbl(2).split_from_line_id := p_req_qty_tbl(J).line_id;
360 END IF;
361 l_split_line_tbl(2).line_set_id := l_line_set_id;
362 l_split_line_tbl(2).ordered_quantity := l_ordered_quantity - l_temp_requested_quantity;
363 -- HW Added qty2 for OPM
364 l_split_line_tbl(2).ordered_quantity2 := nvl(l_ordered_quantity2,0)
365 -- nvl(p_req_qty_tbl(J).requested_quantity2,0); INVCONV
366 - nvl(l_temp_requested_quantity2, 0);
367
368 l_split_line_tbl(2).operation := OE_GLOBALS.G_OPR_CREATE;
369 l_split_line_tbl(2).split_by := 'SYSTEM';
370 l_split_line_tbl(2).change_reason := 'SYSTEM';
371
372 IF l_debug_level > 0 THEN
373 oe_debug_pub.add( 'SPLIT FROM LINE ID : '||TO_CHAR ( L_SPLIT_LINE_TBL ( 2 ) .SPLIT_FROM_LINE_ID ) , 3 ) ;
374 END IF;
375 IF l_debug_level > 0 THEN
376 oe_debug_pub.add( 'ORIGINAL ORDERED QUANTITY : '||TO_CHAR ( L_LINE_REC.ORDERED_QUANTITY ) , 3 ) ;
377 END IF;
378 IF l_debug_level > 0 THEN
379 oe_debug_pub.add( 'ORDERED QUANTITY OLD LINE : '||TO_CHAR ( L_SPLIT_LINE_TBL ( 1 ) .ORDERED_QUANTITY ) , 3 ) ;
380 END IF;
381 IF l_debug_level > 0 THEN
382 oe_debug_pub.add( 'ORDERED QUANTITY NEW LINE : '||TO_CHAR ( L_SPLIT_LINE_TBL ( 2 ) .ORDERED_QUANTITY ) , 3 ) ;
383 END IF;
384
385 -- HW print qty2 for OPM -- INVCONV DELETE this comment
386 IF l_debug_level > 0 THEN
387 oe_debug_pub.add( 'ORIGINAL ORDERED QUANTITY2 : '||TO_CHAR ( L_LINE_REC.ORDERED_QUANTITY2 ) , 3 ) ;
388 END IF;
389 IF l_debug_level > 0 THEN
390 oe_debug_pub.add( 'ORDERED QUANTITY2 OLD LINE : '||TO_CHAR ( L_SPLIT_LINE_TBL ( 1 ) .ORDERED_QUANTITY2 ) , 3 ) ;
391 END IF;
392 IF l_debug_level > 0 THEN
393 oe_debug_pub.add( 'ORDERED QUANTITY2 NEW LINE : '||TO_CHAR ( L_SPLIT_LINE_TBL ( 2 ) .ORDERED_QUANTITY2 ) , 3 ) ;
394 END IF;
395
396 -- 4. Call to process order will result in call to
397 -- update_shipping_attributes for update of ordered quantity
398 -- and creation of new line.
399
400 l_control_rec.validate_entity := FALSE;
401 l_control_rec.check_security := FALSE;
402
403 IF OE_GLOBALS.G_RECURSION_MODE = 'Y' THEN
404
405 l_set_recursion := 'N';
406
407 ELSE
408
409 l_set_recursion := 'Y';
410
411 END IF;
412
413 OE_Shipping_Integration_Pvt.Call_Process_Order
414 (
415 p_line_tbl => l_split_line_tbl,
416 p_control_rec => l_control_rec,
417 x_return_status => l_return_status
418 );
419
420 IF l_set_recursion = 'Y' THEN
421
422 l_set_recursion := 'N';
423 END IF;
424
425 IF l_debug_level > 0 THEN
426 oe_debug_pub.add( 'RETURN STATUS FROM PROCESS ORDER : '||L_RETURN_STATUS , 3 ) ;
427 END IF;
428
429 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
430 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
431 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
432 RAISE FND_API.G_EXC_ERROR;
433 END IF;
434
435 << END_REQ_QTY >>
436 NULL;
437
438 END LOOP;
439
440 END IF; /* Req quantity table */
441
442 IF p_line_adj_tbl.COUNT > 0 THEN
443 -- Create Freight Cost Records Here
444 NULL;
445 FOR J IN 1..p_line_adj_tbl.COUNT
446 LOOP
447 SELECT OE_PRICE_ADJUSTMENTS_S.nextval
448 INTO l_price_adjustment_id
449 FROM DUAL;
450
451 l_line_adj_rec := p_line_adj_tbl(J);
452 l_Line_adj_rec.price_adjustment_id := l_price_adjustment_id;
453 l_Line_adj_rec.last_update_date := SYSDATE;
454 l_Line_adj_rec.last_updated_by := FND_GLOBAL.USER_ID;
455 l_Line_adj_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
456 l_Line_adj_rec.creation_date := SYSDATE;
457 l_Line_adj_rec.created_by := FND_GLOBAL.USER_ID;
458 -- #3015849
459 IF l_debug_level > 0 THEN
460 OE_DEBUG_PUB.add('Calling convert_miss_to_null for this adj record',5);
461 END IF;
462 OE_LINE_ADJ_UTIL.CONVERT_MISS_TO_NULL( l_line_adj_rec );
463 IF l_debug_level > 0 THEN
464 oe_debug_pub.add( 'INSERTING THE ADJ RECORD '||TO_CHAR ( L_LINE_ADJ_REC.PRICE_ADJUSTMENT_ID ) , 2 ) ;
465 END IF;
466 OE_LINE_ADJ_UTIL.INSERT_ROW(p_Line_Adj_rec => l_line_adj_rec);
467 END LOOP;
468 END IF;
469
470 IF l_debug_level > 0 THEN
471 oe_debug_pub.add( 'NUMBER OF LINES SHIPPED : '||P_LINE_TBL.COUNT , 3 ) ;
472 END IF;
473
474 IF p_line_tbl.COUNT > 0 THEN
475 -- Update Line records for required attributes..
476
477 FOR J IN 1..p_line_tbl.COUNT
478 LOOP
479
480 SELECT top_model_line_id
481 INTO l_top_model_line_id
482 FROM oe_order_lines
483 WHERE line_id = p_line_tbl(J).line_id;
484
485
486 l_line_id_mod := mod(p_line_tbl(J).line_id,OE_GLOBALS.G_BINARY_LIMIT); -- Bug 8795918
487 --bug3549422
488 --added the NOWAIT to the select statements and
489 BEGIN
490
491 -- Lock the Model line if the line is part of configuration.
492 IF nvl(l_top_model_line_id,0) <> 0 THEN
493
494 IF l_debug_level > 0 THEN
495 oe_debug_pub.add( 'LOCKING MODEL '||L_TOP_MODEL_LINE_ID||'/'||TO_CHAR ( SYSDATE , 'DD-MM-YYYY HH24:MI:SS' ) , 3 )
496 ;
497 END IF;
498
499 SELECT top_model_line_id
500 INTO l_top_model_line_id
501 FROM oe_order_lines
502 WHERE line_id = l_top_model_line_id
503 FOR UPDATE NOWAIT;
504
505 IF l_debug_level > 0 THEN
506 oe_debug_pub.add( 'MODEL LOCKED '||TO_CHAR ( SYSDATE , 'DD-MM-YYYY HH24:MI:SS' ) , 3 ) ;
507 END IF;
508
509 END IF;
510
511 SELECT shipping_quantity,
512 shipping_quantity_uom,
513 order_quantity_uom,
514 actual_shipment_date,
515 inventory_item_id,
516 ship_from_org_id,
517 ship_set_id,
518 top_model_line_id,
519 ato_line_id,
520 model_remnant_flag,
521 ordered_quantity,
522 ship_tolerance_below,
523 over_ship_reason_code,
524 item_type_code,
525 header_id,
526 calculate_price_flag,
527 ship_model_complete_flag
528 INTO l_shipping_quantity,
529 l_shipping_quantity_uom,
530 l_order_quantity_uom,
531 l_actual_shipment_date,
532 l_inventory_item_id,
533 l_ship_from_org_id,
534 l_ship_set_id,
535 l_top_model_line_id,
536 l_ato_line_id,
537 l_model_remnant_flag,
538 l_ordered_quantity,
539 l_ship_tolerance_below,
540 l_over_ship_reason_code,
541 l_item_type_code,
542 l_header_id,
543 l_calculate_price_flag,
544 l_smc_flag
545 FROM OE_ORDER_LINES
546 WHERE line_id = p_line_tbl(J).line_id
547 FOR UPDATE NOWAIT;
548
549 l_ship_set_id_mod := mod(l_ship_set_id,OE_GLOBALS.G_BINARY_LIMIT); -- Bug 8795918
550
551 EXCEPTION
552
553 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
554 IF l_debug_level > 0 THEN
555 OE_DEBUG_PUB.Add('Unable to lock the line/parent',3);
556 END IF;
557 RAISE FND_API.G_EXC_ERROR;
558
559
560 WHEN OTHERS THEN
561 IF l_debug_level > 0 THEN
562 OE_DEBUG_PUB.Add('Unable to process ship confirm line:'||
563 sqlerrm,3);
564 END IF;
565 RAISE FND_API.G_EXC_ERROR;
566
567 END;
568 --bug3549422 ends
569
570 IF p_line_tbl(J).ship_tolerance_below = FND_API.G_MISS_NUM THEN
571
572 l_ship_tolerance_below_upd := l_ship_tolerance_below;
573
574 ELSE
575
576 l_ship_tolerance_below_upd := p_line_tbl(J).ship_tolerance_below;
577
578 END IF;
579
580 IF p_line_tbl(J).over_ship_reason_code = FND_API.G_MISS_CHAR THEN
581
582 l_over_ship_reason_code_upd := l_over_ship_reason_code;
583
584 ELSE
585
586 l_over_ship_reason_code_upd := p_line_tbl(J).over_ship_reason_code;
587
588 END IF;
589
590 IF NOT OE_GLOBALS.Equal(p_line_tbl(J).shipping_quantity,l_shipping_quantity) THEN
591 -- Convert the shipping quantity from shipping quantity UOM to
592 -- Ordered quantity UOM and update the field shipped quantity
593 -- Call API to convert the shipping quantity to shipped quantity
594 -- from shipping quantity UOM to ordered quantity UOM and assign
595 -- the returned quantity to shipped quantity.
596
597 IF l_debug_level > 0 THEN
598 oe_debug_pub.add( 'ORDER QUANTITY UOM : '|| L_ORDER_QUANTITY_UOM , 2 ) ;
599 END IF;
600 IF l_debug_level > 0 THEN
601 oe_debug_pub.add( 'SHIPPING QUANTITY UOM : '||P_LINE_TBL ( J ) .SHIPPING_QUANTITY_UOM , 2 ) ;
602 END IF;
603
604 IF p_line_tbl(J).shipping_quantity_uom <> l_order_quantity_uom THEN
605
606 /* --OPM 06/SEP/00 invoke process Uom Conversion for process line
607 --============================================================ */
608 -- Invoke lot Uom Conversion possibly if for DUAL line INVCONV
609
610 IF oe_line_util.dual_uom_control
611 (l_inventory_item_id
612 ,l_ship_from_org_id
613 ,l_item_rec) THEN
614
615 IF l_debug_level > 0 THEN
616 oe_debug_pub.add( 'DUAL ITEM - SHIPPING UPDATE ' , 1 ) ;
617 END IF;
618
619 -- HW OPM BUG#:2415731 Since OM does not save lot and sublot information and shipping does,
620 -- we need to retrieve the information from wsh_delivery_details
621
622 l_temp_dual_shipped_qty := 0;
623
624 -- Fetch lot information from shipping table
625
626 OPEN LOT_INFO(p_line_tbl(J).line_id);
627 FETCH LOT_INFO INTO LOT;
628 while LOT_INFO%FOUND LOOP
629 /* -- INVCONV
630 -- HW OPM BUG#:2415731
631 -- Sublot could be NULL, so we need to have different select statements
632 -- Unique Key in ic_loct_mst:lot_no,sublot_no and item_id.
633 -- Primary key is: item_id,lot_id.
634
635 -- Case 1: Both lot_no and sublot_no are not NULL
636 -- Uday Phadtare Bug 2886396 added exception and commented lot_id condition.
637 IF ( LOT.lot_number is NOT NULL AND LOT.sublot_number IS NOT NULL) THEN
638 BEGIN
639 SELECT ic.lot_id
640 INTO l_lot_id
641 FROM ic_lots_mst ic
642 WHERE ic.lot_no = LOT.lot_number
643 AND ic.item_id = l_item_rec.opm_item_id
644 AND ic.sublot_no = LOT.sublot_number;
645 -- AND ic.lot_id <> 0 ;
646 EXCEPTION
647 WHEN OTHERS THEN
648 l_lot_id := 0;
649 END;
650 -- HW BUG#:2415731
651 -- Case 2: Sublot_no is NULL and lot_no is not
652 -- Uday Phadtare Bug 2886396 added exception and commented lot_id condition.
653 ELSIF (LOT.lot_number is NOT NULL AND LOT.sublot_number is NULL) THEN
654 BEGIN
655 SELECT ic.lot_id
656 INTO l_lot_id
657 FROM ic_lots_mst ic
658 WHERE ic.lot_no = LOT.lot_number
659 AND ic.item_id = l_item_rec.opm_item_id
660 AND ic.sublot_no IS NULL;
661 -- AND ic.lot_id <> 0 ;
662 EXCEPTION
663 WHEN OTHERS THEN
664 l_lot_id := 0;
665 END;
666 -- HW BUG#:2415731
667 -- Case 3: Both lot_no and sublot_no are NULL
668 ELSE
669 l_lot_id := 0;
670 END IF;
671
672 IF l_debug_level > 0 THEN
673 oe_debug_pub.add( 'OPM , VALUE OF LOT_ID IS : '||L_LOT_ID , 1 ) ;
674 END IF;
675
676 */
677 -- INVCONV
678
679
680
681
682
683 -- BUG#: 2415731 pass l_lot_number to perform any lot specific conversion -- INVCONV
684 -- and pass LOT.shipped_quantity to calculate shipped_qty for each line
685
686 /*l_temp_shipped_quantity := GMI_Reservation_Util.get_opm_converted_qty(
687 p_apps_item_id => l_inventory_item_id,
688 p_organization_id => l_ship_from_org_id,
689 p_apps_from_uom => p_line_tbl(J).shipping_quantity_uom,
690 p_apps_to_uom => l_order_quantity_uom,
691 p_original_qty => LOT.shipped_quantity,
692 p_lot_id => l_lot_id); */
693
694 l_temp_shipped_quantity :=
695 INV_CONVERT.INV_UM_CONVERT(l_inventory_item_id , l_lot_number -- INVCONV
696 , l_ship_from_org_id -- INVCONV
697 ,9 -- Precision (Default precision is 6 decimals) ?
698 ,LOT.shipped_quantity
699 ,p_line_tbl(J).shipping_quantity_uom
700 ,l_order_quantity_uom
701 ,NULL -- From uom name
702 ,NULL -- To uom name
703 );
704
705 -- BUG 2415731, sum the shipped_qty -- INVCONV
706 l_temp_dual_shipped_qty := l_temp_dual_shipped_qty + l_temp_shipped_quantity; -- INVCONV
707
708 IF l_debug_level > 0 THEN
709 oe_debug_pub.add( 'OPM , VALUE OF TEMP_DUAL_SHIPPED_QTY IS '||L_TEMP_DUAL_SHIPPED_QTY , 1 ) ; -- INVCONV
710 END IF;
711
712 -- HW BUG#:2415731 Fetch the next record and close the cursor
713 FETCH LOT_INFO into LOT;
714 END LOOP;
715 << loop_end>>
716 CLOSE LOT_INFO;
717
718 l_temp_shipped_quantity := l_temp_dual_shipped_qty; --INVCONV
719 -- HW end of changes for bug 2415731
720
721 IF l_debug_level > 0 THEN
722 oe_debug_pub.add( 'DUAL PROCESS SHIPPING UPDATE CONVERSION' || ' GIVES SHIPPED QUANTITY OF ' ||
723 L_TEMP_SHIPPED_QUANTITY , 1 ) ;
724 END IF;
725
726 ELSE
727
728
729 l_temp_shipped_quantity := OE_Order_Misc_Util.Convert_Uom
730 (
731 l_inventory_item_id,
732 p_line_tbl(J).shipping_quantity_uom,
733 l_order_quantity_uom,
734 p_line_tbl(J).shipping_quantity
735 );
736 IF l_debug_level > 0 THEN
737 oe_debug_pub.add( 'CONVERTED SHIPPED QUANTITY : '|| TO_CHAR ( L_TEMP_SHIPPED_QUANTITY ) , 1 ) ;
738 END IF;
739 END IF; --IF oe_line_util.dual_uom_control -- INVCONV
740
741 --OPM 06/SEP/00 END
742
743
744
745 oe_debug_pub.ADD('Converted Shipped Quantity : '||to_char(l_temp_shipped_quantity),1);
746
747 IF l_temp_shipped_quantity <> trunc(l_temp_shipped_quantity) THEN
748
749 Inv_Decimals_PUB.Validate_Quantity
750 (
751 p_item_id => l_inventory_item_id,
752 p_organization_id => OE_Sys_Parameters.value('MASTER_ORGANIZATION_ID'),
753 p_input_quantity => l_temp_shipped_quantity,
754 p_uom_code => l_order_quantity_uom,
755 x_output_quantity => l_validated_quantity,
756 x_primary_quantity => l_primary_quantity,
757 x_return_status => l_qty_return_status
758 );
759
760 IF l_debug_level > 0 THEN
761 oe_debug_pub.add( 'RETURN STATUS FROM INV API : '||L_QTY_RETURN_STATUS , 1 ) ;
762 END IF;
763 IF l_qty_return_status = 'W' THEN
764
765 l_shipped_quantity := l_validated_quantity;
766 ELSE
767
768 l_shipped_quantity := l_temp_shipped_quantity;
769
770 END IF;
771
772 ELSE
773 l_shipped_quantity := l_temp_shipped_quantity;
774
775 END IF; -- IF l_temp_shipped_quantity <> trunc
776
777 ELSE
778 l_shipped_quantity := p_line_tbl(J).shipping_quantity;
779
780 END IF; --IF p_line_tbl(J).shipping_quantity_uom <> l_order_quantity_uom
781
782 IF l_debug_level > 0 THEN
783 oe_debug_pub.add( 'SHIPPED QUANTITY : '|| TO_CHAR ( L_SHIPPED_QUANTITY ) , 1 ) ;
784 END IF;
785
786
787 -- updating remnant flag for various conditions.
788
789 IF l_top_model_line_id is not NULL AND
790 nvl(l_smc_flag, 'N') = 'N' AND
791 nvl(l_model_remnant_flag, 'N') = 'N' AND
792 OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508'
793 THEN
794
795 SELECT ato_line_id
796 INTO l_count
797 FROM oe_order_lines
798 WHERE line_id = l_top_model_line_id;
799
800 IF l_count is NULL THEN
801
802 IF l_debug_level > 0 THEN
803 oe_debug_pub.add( 'LINE PART OF NON SMC PTO' , 4 ) ;
804 END IF;
805
806 -- if ato+pto with external lines, make it remnant.
807
808 --bug3480047 contd
809 SELECT COUNT(*)
810 INTO l_shippable_lines
811 FROM OE_ORDER_LINES
812 WHERE top_model_line_id = l_top_model_line_id
813 AND NVL(CANCELLED_FLAG,'N')='N'
814 AND NVL(SHIPPABLE_FLAG,'N')='Y';
815
816 IF l_shippable_lines > 1 THEN
817
818 SELECT count(*)
819 INTO l_count
820 FROM oe_order_lines
821 WHERE top_model_line_id = l_top_model_line_id
822 AND cancelled_flag = 'N'
823 AND source_type_code = 'EXTERNAL';
824
825 IF l_count > 0 THEN
826 IF l_debug_level > 0 THEN
827 oe_debug_pub.add( 'EXTERNAL LINES EXIST' , 2 ) ;
828 END IF;
829 l_make_remnant := 'Y'; --bug 4701487
830 END IF;
831 END IF; --bug3480047 ends
832
833 -- if non smc model with ato and config line not created
834 -- make it remanant.
835 IF l_make_remnant <> 'Y' THEN -- bug 4701487
836 l_count := 0;
837
838 /* MOAC_SQL_CHANGE */
839 SELECT count(*)
840 INTO l_count
841 FROM oe_order_lines oe1
842 WHERE top_model_line_id = l_top_model_line_id
843 AND ato_line_id = line_id
844 AND item_type_code = 'CLASS'
845 AND cancelled_flag = 'N'
846 AND not exists
847 (SELECT NULL
848 FROM oe_order_lines_all
849 WHERE top_model_line_id = l_top_model_line_id
850 AND ato_line_id = oe1.line_id
851 AND cancelled_flag = 'N'
852 AND item_type_code = 'CONFIG');
853
854 IF l_count > 0 THEN
855 IF l_debug_level > 0 THEN
856 oe_debug_pub.add( 'ATO model line does not have CONFIG created' , 2 ) ;
857 END IF;
858 l_make_remnant := 'Y'; --bug 4701487
859 END IF;
860 END IF;
861
862 IF l_make_remnant <> 'Y' THEN -- bug 4701487
863 l_count := 0;
864 BEGIN
865 SELECT 1 INTO l_count
866 FROM DUAL
867 WHERE EXISTS (
868 SELECT NULL
869 FROM oe_order_lines
870 WHERE top_model_line_id = l_top_model_line_id
871 AND cancelled_flag = 'N'
872 AND schedule_ship_date is NULL);
873 EXCEPTION
874 WHEN OTHERS THEN
875 NULL;
876 END;
877
878 IF l_count > 0 THEN
879 IF l_debug_level > 0 THEN
880 oe_debug_pub.add('Atleast one un-scheduled line exists in the Model...' , 2 ) ;
881 END IF;
882 l_make_remnant := 'Y'; -- bug 4701487
883 END IF;
884 END IF;
885
886 IF l_make_remnant = 'Y' THEN -- bug 4701487
887 IF l_debug_level > 0 THEN
888 oe_debug_pub.add( 'Making the model REMNANT:'|| L_TOP_MODEL_LINE_ID , 3 ) ;
889 END IF;
890 UPDATE oe_order_lines
891 SET model_remnant_flag = 'Y'
892 WHERE top_model_line_id = l_top_model_line_id;
893
894 l_model_remnant_flag := 'Y';
895 END IF;
896
897 END IF; -- if top model is not ATO.
898
899 END IF; -- if part of model.
900
901
902
903 -- Add to the ship confirm table
904
905 IF (l_ship_set_id IS NOT NULL AND
906 l_ship_set_id <> FND_API.G_MISS_NUM) THEN
907 IF l_debug_level > 0 THEN
908 oe_debug_pub.add( 'SHIP SET SHIPPED : '||L_SHIP_SET_ID , 3 ) ;
909 END IF;
910
911 -- IF l_ship_confirm_tbl.EXISTS(l_ship_set_id) THEN -- Bug 8795918
912 IF l_ship_confirm_tbl.EXISTS(l_ship_set_id_mod) THEN
913
914 IF l_debug_level > 0 THEN
915 oe_debug_pub.add( 'SHIP SET ALREADY EXISTS : '||L_SHIP_SET_ID , 3 ) ;
916 END IF;
917 ELSE
918 IF l_debug_level > 0 THEN
919 oe_debug_pub.add( 'ADD SHIP SET : '||L_SHIP_SET_ID , 3 ) ;
920 END IF;
921 --l_ship_confirm_tbl(l_ship_set_id).type_id := l_ship_set_id; -- Bug 8795918
922 --l_ship_confirm_tbl(l_ship_set_id).ship_confirm_type := 'SHIP_SET'; -- Bug 8795918
923 l_ship_confirm_tbl(l_ship_set_id_mod).type_id := l_ship_set_id;
924 l_ship_confirm_tbl(l_ship_set_id_mod).ship_confirm_type := 'SHIP_SET';
925
926 END IF;
927
928 ELSIF (l_ato_line_id IS NOT NULL AND
929 l_ato_line_id <> FND_API.G_MISS_NUM) AND
930 l_item_type_code = Oe_Globals.G_ITEM_CONFIG AND
931 l_ato_line_id = l_top_model_line_id THEN
932
933 IF l_debug_level > 0 THEN
934 oe_debug_pub.add( 'ATO SHIPPED : '||P_LINE_TBL ( J ) .LINE_ID , 3 ) ;
935 END IF;
936
937 IF l_ship_confirm_tbl.EXISTS(p_line_tbl(J).line_id) THEN
938
939 IF l_debug_level > 0 THEN
940 oe_debug_pub.add( 'ATO ALREADY EXISTS : '||P_LINE_TBL ( J ) .LINE_ID , 3 ) ;
941 END IF;
942
943 ELSE
944 IF l_debug_level > 0 THEN
945 oe_debug_pub.add( 'ADD ATO : '||P_LINE_TBL ( J ) .LINE_ID , 3 ) ;
946 END IF;
947 -- l_ship_confirm_tbl(p_line_tbl(J).line_id).type_id := p_line_tbl(J).line_id; -- Bug 8795918
948 -- l_ship_confirm_tbl(p_line_tbl(J).line_id).ship_confirm_type := 'ATO'; -- Bug 8795918
949 l_ship_confirm_tbl(l_line_id_mod).type_id := p_line_tbl(J).line_id;
950 l_ship_confirm_tbl(l_line_id_mod).ship_confirm_type := 'ATO';
951
952 END IF;
953
954 ELSIF (l_top_model_line_id IS NOT NULL AND
955 l_top_model_line_id <> FND_API.G_MISS_NUM) AND
956 nvl(l_model_remnant_flag,'N') = 'N' THEN
957
958 l_top_model_line_id_mod := mod(l_top_model_line_id,OE_GLOBALS.G_BINARY_LIMIT);-- Bug 8795918
959
960 IF l_debug_level > 0 THEN
961 oe_debug_pub.add( 'PTO/KIT SHIPPED : '||L_TOP_MODEL_LINE_ID , 3 ) ;
962 END IF;
963
964 -- IF l_ship_confirm_tbl.EXISTS(l_top_model_line_id) THEN Bug 8795918
965 IF l_ship_confirm_tbl.EXISTS(l_top_model_line_id_mod) THEN
966
967 IF l_debug_level > 0 THEN
968 oe_debug_pub.add( 'PTO/KIT ALREADY EXISTS : '||L_TOP_MODEL_LINE_ID , 3 ) ;
969 END IF;
970
971 ELSE
972 IF l_debug_level > 0 THEN
973 oe_debug_pub.add( 'ADD PTO/KIT : '||L_TOP_MODEL_LINE_ID , 3 ) ;
974 END IF;
975 -- l_ship_confirm_tbl(l_top_model_line_id).type_id := l_top_model_line_id; -- Bug 8795918
976 -- l_ship_confirm_tbl(l_top_model_line_id).ship_confirm_type := 'PTO_KIT'; -- Bug 8795918
977 l_ship_confirm_tbl(l_top_model_line_id_mod).type_id := l_top_model_line_id;
978 l_ship_confirm_tbl(l_top_model_line_id_mod).ship_confirm_type := 'PTO_KIT';
979
980 END IF;
981
982 ELSE
983 IF l_debug_level > 0 THEN
984 oe_debug_pub.add( 'NORMAL SHIPPED : '||P_LINE_TBL ( J ) .LINE_ID , 3 ) ;
985 END IF;
986
987 -- IF l_ship_confirm_tbl.EXISTS(p_line_tbl(J).line_id) THEN -- Bug 8795918
988 IF l_ship_confirm_tbl.EXISTS(l_line_id_mod) THEN
989
990 IF l_debug_level > 0 THEN
991 oe_debug_pub.add( 'ALREADY EXISTS : '||P_LINE_TBL ( J ) .LINE_ID , 3 ) ;
992 END IF;
993
994 ELSE
995 IF l_debug_level > 0 THEN
996 oe_debug_pub.add( 'ADD : '||P_LINE_TBL ( J ) .LINE_ID , 3 ) ;
997 END IF;
998 -- l_ship_confirm_tbl(p_line_tbl(J).line_id).type_id := p_line_tbl(J).line_id; -- Bug 8795918
999 -- l_ship_confirm_tbl(p_line_tbl(J).line_id).ship_confirm_type := l_item_type_code; -- Bug 8795918
1000 l_ship_confirm_tbl(l_line_id_mod).type_id := p_line_tbl(J).line_id;
1001 l_ship_confirm_tbl(l_line_id_mod).ship_confirm_type := l_item_type_code;
1002
1003 END IF;
1004
1005 END IF;
1006
1007 END IF; -- IF NOT OE_GLOBALS.Equal
1008
1009
1010 /* jolin start */
1011 /* Moved call to query record out NOCOPY of IF ordered=shipped below so that
1012 it always happens to populate the current and old line table for notification */
1013
1014 OE_Line_Util.Query_Rows(p_line_id => p_line_tbl(J).line_id,
1015 x_line_tbl => l_temp_line_tbl);
1016
1017 IF l_debug_level > 0 THEN
1018 oe_debug_pub.add( 'RETURNED FROM QUERY ROW ' , 1 ) ;
1019 END IF;
1020 l_old_line_tbl(J) := l_temp_line_tbl(1);
1021 l_line_tbl(J) := l_old_line_tbl(J);
1022
1023 /* jolin end */
1024
1025 -- If no partial shipment then
1026 IF l_ordered_quantity = l_shipped_quantity THEN
1027
1028 IF l_debug_level > 0 THEN
1029 oe_debug_pub.add( 'UPDATING LINE : '||P_LINE_TBL ( J ) .LINE_ID , 3 ) ;
1030 END IF;
1031
1032 IF ((OE_GLOBALS.G_ASO_INSTALLED = 'Y') OR
1033 (OE_GLOBALS.G_EC_INSTALLED = 'Y') OR
1034 (NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'),'N') = 'Y') )
1035 THEN
1036 /* l_line_tbl(J) already holds the old info, so just overwrite what changed */
1037 IF l_debug_level > 0 THEN
1038 oe_debug_pub.add( 'NEED TO CALL PROCESS REQUEST ' , 3 ) ;
1039 END IF;
1040 l_line_tbl(J).shipping_quantity := p_line_tbl(J).shipping_quantity;
1041 l_line_tbl(J).shipping_quantity2 := p_line_tbl(J).shipping_quantity2;
1042 l_line_tbl(J).shipped_quantity2 := p_line_tbl(J).shipping_quantity2;
1043 l_line_tbl(J).shipping_quantity_uom := p_line_tbl(J).shipping_quantity_uom;
1044 l_line_tbl(J).shipping_quantity_uom2 := p_line_tbl(J).shipping_quantity_uom2; -- INVCONV
1045 l_line_tbl(J).shipped_quantity := l_shipped_quantity; -- PLA ?
1046
1047 l_line_tbl(J).actual_shipment_date := p_line_tbl(J).actual_shipment_date;
1048 l_line_tbl(J).over_ship_reason_code := l_over_ship_reason_code_upd;
1049 l_line_tbl(J).ship_tolerance_below := l_ship_tolerance_below_upd;
1050 l_notify := TRUE;
1051 END IF; /* need to update global picture */
1052
1053 UPDATE OE_ORDER_LINES
1054 SET shipping_quantity = p_line_tbl(J).shipping_quantity,
1055 shipping_quantity2 = p_line_tbl(J).shipping_quantity2,
1056 shipped_quantity2 = p_line_tbl(J).shipping_quantity2,
1057 shipping_quantity_uom = p_line_tbl(J).shipping_quantity_uom,
1058 shipping_quantity_uom2 = p_line_tbl(J).shipping_quantity_uom2, -- INVCONV
1059 actual_shipment_date = p_line_tbl(J).actual_shipment_date,
1060 ship_tolerance_below = l_ship_tolerance_below_upd,
1061 over_ship_reason_code = l_over_ship_reason_code_upd,
1062 shipped_quantity = l_shipped_quantity
1063 WHERE line_id = p_line_tbl(J).line_id;
1064
1065 ELSE /* ordered does not equal shipped qty */
1066
1067 IF ((OE_GLOBALS.G_ASO_INSTALLED = 'Y') OR
1068 (OE_GLOBALS.G_EC_INSTALLED = 'Y') OR
1069 (NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'),'N') = 'Y'))
1070 THEN
1071 /* l_line_tbl(J) already holds the old info, so just overwrite what changed */
1072 IF l_debug_level > 0 THEN
1073 oe_debug_pub.add( 'NEED TO CALL PROCESS REQUEST ' , 3 ) ;
1074 END IF;
1075 l_line_tbl(J).shipping_quantity := p_line_tbl(J).shipping_quantity;
1076 l_line_tbl(J).shipping_quantity2 := p_line_tbl(J).shipping_quantity2;
1077 l_line_tbl(J).shipped_quantity2 := p_line_tbl(J).shipping_quantity2;
1078 l_line_tbl(J).shipping_quantity_uom := p_line_tbl(J).shipping_quantity_uom;
1079 l_line_tbl(J).shipping_quantity_uom2 := p_line_tbl(J).shipping_quantity_uom2; -- INVCONV
1080
1081 l_line_tbl(J).shipped_quantity := l_shipped_quantity; -- INVCONV
1082 l_line_tbl(J).actual_shipment_date := p_line_tbl(J).actual_shipment_date;
1083 l_line_tbl(J).over_ship_reason_code := l_over_ship_reason_code_upd;
1084 l_line_tbl(J).ship_tolerance_below := l_ship_tolerance_below_upd;
1085 l_notify := TRUE;
1086 END IF; /* need to update global picture */
1087
1088 OE_Shipping_Integration_PVT.Check_Shipment_Line(
1089 p_line_rec => l_temp_line_tbl(1)
1090 , p_shipped_quantity => l_Shipped_Quantity
1091 , x_result_out => l_result_out
1092 );
1093
1094 IF l_result_out = OE_GLOBALS.G_PARTIALLY_SHIPPED THEN
1095 -- This line will split, set the calculate_price_flag to 'P' if 'Y'
1096 IF l_debug_level > 0 THEN
1097 oe_debug_pub.add( 'CALCULATE PRICE FLAG : '||L_CALCULATE_PRICE_FLAG , 3 ) ;
1098 END IF;
1099 IF l_calculate_price_flag = 'Y' THEN
1100 l_calculate_price_flag := 'P';
1101 END IF;
1102 END IF; /* partially shipped */
1103
1104 IF l_debug_level > 0 THEN
1105 oe_debug_pub.add( 'CALCULATE PRICE FLAG : '||L_CALCULATE_PRICE_FLAG , 3 ) ;
1106 END IF;
1107 UPDATE OE_ORDER_LINES
1108 SET shipping_quantity = p_line_tbl(J).shipping_quantity,
1109 shipping_quantity2 = p_line_tbl(J).shipping_quantity2,
1110 shipped_quantity2 = p_line_tbl(J).shipping_quantity2,
1111 shipping_quantity_uom = p_line_tbl(J).shipping_quantity_uom,
1112 shipping_quantity_uom2 = p_line_tbl(J).shipping_quantity_uom2, -- INVCONV
1113 actual_shipment_date = p_line_tbl(J).actual_shipment_date,
1114 ship_tolerance_below = l_ship_tolerance_below_upd,
1115 over_ship_reason_code = l_over_ship_reason_code_upd,
1116 shipped_quantity = l_shipped_quantity,
1117 calculate_price_flag = l_calculate_price_flag
1118 WHERE line_id = p_line_tbl(J).line_id;
1119
1120 END IF; /* ordered = shipped */
1121
1122 /* Log the pricing delayed request */
1123
1124 IF l_calculate_price_flag IN ('Y','P')
1125 OR (p_line_tbl(J).shipping_quantity2 IS NOT NULL
1126 AND p_line_tbl(J).shipping_quantity2 <> 0 ) -- bug 3598987,3659454
1127 THEN
1128 oe_debug_pub.add( 'Before logging Delayed request for pricing',3);
1129 OE_delayed_requests_Pvt.log_request(
1130 p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
1131 p_entity_id => p_line_tbl(J).line_id,
1132 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_ALL,
1133 p_requesting_entity_id => p_line_tbl(J).line_id,
1134 p_request_unique_key1 => 'SHIP',
1135 p_param1 => l_header_id,
1136 p_param2 => 'SHIP',
1137 p_request_type => OE_GLOBALS.G_PRICE_LINE,
1138 x_return_status => l_return_status);
1139
1140 END IF;
1141
1142
1143 END LOOP; /* over J */
1144
1145 -- Now process ship confirms.
1146
1147 IF l_debug_level > 0 THEN
1148 oe_debug_pub.add( 'NUMBER OF SHIP CONFIRMS : '||L_SHIP_CONFIRM_TBL.COUNT , 3 ) ;
1149 END IF;
1150
1151 l_ship_confirm_index := l_ship_confirm_tbl.FIRST;
1152
1153 WHILE l_ship_confirm_index IS NOT NULL
1154 LOOP
1155
1156 IF l_debug_level > 0 THEN
1157 oe_debug_pub.add( 'TYPE/ID : '||L_SHIP_CONFIRM_TBL ( L_SHIP_CONFIRM_INDEX )
1158 .SHIP_CONFIRM_TYPE||'/'||L_SHIP_CONFIRM_TBL ( L_SHIP_CONFIRM_INDEX ) .TYPE_ID , 3 ) ;
1159 END IF;
1160
1161 OE_Shipping_Integration_PVT.Process_Ship_Confirm
1162
1163 (p_process_id => l_ship_confirm_tbl(l_ship_confirm_index).type_id,
1164 p_process_type => l_ship_confirm_tbl(l_ship_confirm_index).ship_confirm_type,
1165 x_return_status => l_return_status
1166
1167 );
1168
1169 IF l_debug_level > 0 THEN
1170 oe_debug_pub.add( 'RETRUN STATUS FROM PROCESS_SHIP_CONFIRM : '||L_RETURN_STATUS , 3 ) ;
1171 END IF;
1172
1173 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1175 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1176 RAISE FND_API.G_EXC_ERROR;
1177 END IF;
1178
1179 l_ship_confirm_index := l_ship_confirm_tbl.NEXT(l_ship_confirm_index);
1180
1181 END LOOP; /* over ship confirm index */
1182
1183 -- jolin start
1184 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN
1185
1186 -- Here we call update_global_picture to get the index of each line being updated in the
1187 -- update statement above, and we set the global variables for each.
1188
1189 -- loop over l_line_tbl using loop_index
1190
1191 l_loop_index := l_line_tbl.FIRST;
1192
1193 WHILE l_loop_index IS NOT NULL LOOP
1194
1195 -- call notification framework to get this line's index position
1196 OE_ORDER_UTIL.Update_Global_Picture
1197 (p_Upd_New_Rec_If_Exists =>FALSE
1198 , p_header_id => l_line_tbl(l_loop_index).header_id
1199 , p_line_rec => l_line_tbl(l_loop_index)
1200 , p_old_line_rec => l_old_line_tbl(l_loop_index)
1201 , p_line_id => l_line_tbl(l_loop_index).line_id
1202 , x_index => l_notify_index
1203 , x_return_status => l_return_status);
1204
1205 IF l_debug_level > 0 THEN
1206 oe_debug_pub.add('UPDATE_GLOBAL RET_STATUS FOR LINE_ID '||L_LINE_TBL ( L_LOOP_INDEX ) .LINE_ID ||' IS: ' || L_RETURN_STATUS , 1
1207 ) ;
1208 oe_debug_pub.add('UPDATE_GLOBAL INDEX FOR LINE_ID '||L_LINE_TBL ( L_LOOP_INDEX ) .LINE_ID ||' IS: ' || L_NOTIFY_INDEX , 1 ) ;
1209 END IF;
1210
1211 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1212 RAISE FND_API.G_EXC_ERROR;
1213 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1215 END IF;
1216
1217 IF l_notify_index is not null then
1218 -- modify Global Picture
1219 -- uncommented for bug 2934535
1220 OE_ORDER_UTIL.g_old_line_tbl(l_notify_index) := l_old_line_tbl(l_loop_index);
1221
1222 -- Commented for bug 2818553 hashraf
1223 -- OE_ORDER_UTIL.g_line_tbl(l_notify_index) := OE_ORDER_UTIL.g_old_line_tbl(l_notify_index);
1224 OE_ORDER_UTIL.g_line_tbl(l_notify_index).line_id := l_line_tbl(l_loop_index).line_id;
1225 OE_ORDER_UTIL.g_line_tbl(l_notify_index).header_id := l_line_tbl(l_loop_index).header_id;
1226 OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_date := l_line_tbl(l_loop_index).last_update_date;
1227 OE_ORDER_UTIL.g_line_tbl(l_notify_index).shipping_quantity:= l_line_tbl(l_loop_index).shipping_quantity;
1228 OE_ORDER_UTIL.g_line_tbl(l_notify_index).shipping_quantity2:= l_line_tbl(l_loop_index).shipping_quantity2;
1229 OE_ORDER_UTIL.g_line_tbl(l_notify_index).shipped_quantity:= l_line_tbl(l_loop_index).shipped_quantity;
1230 OE_ORDER_UTIL.g_line_tbl(l_notify_index).shipped_quantity2:= l_line_tbl(l_loop_index).shipped_quantity2;
1231 OE_ORDER_UTIL.g_line_tbl(l_notify_index).shipping_quantity_uom:= l_line_tbl(l_loop_index).shipping_quantity_uom;
1232 OE_ORDER_UTIL.g_line_tbl(l_notify_index).shipping_quantity_uom2:= l_line_tbl(l_loop_index).shipping_quantity_uom2; -- INVCONV
1233 OE_ORDER_UTIL.g_line_tbl(l_notify_index).actual_shipment_date:= l_line_tbl(l_loop_index).actual_shipment_date;
1234 OE_ORDER_UTIL.g_line_tbl(l_notify_index).ship_tolerance_below:= l_line_tbl(l_loop_index).ship_tolerance_below;
1235 OE_ORDER_UTIL.g_line_tbl(l_notify_index).over_ship_reason_code:= l_line_tbl(l_loop_index).over_ship_reason_code;
1236 OE_ORDER_UTIL.g_line_tbl(l_notify_index).calculate_price_flag:= l_line_tbl(l_loop_index).calculate_price_flag;
1237 -- bug 3272489
1238 OE_ORDER_UTIL.g_line_tbl(l_notify_index).line_category_code := l_line_tbl(l_loop_index).line_category_code;
1239
1240 IF l_debug_level > 0 THEN
1241 oe_debug_pub.add( 'GLOBAL LINE SHIPPING_QUANTITY: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX )
1242 .SHIPPING_QUANTITY , 1 ) ;
1243 oe_debug_pub.add( 'GLOBAL LINE SHIPPING_QUANTITY2: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX )
1244 .SHIPPING_QUANTITY2 , 1 ) ;
1245 oe_debug_pub.add( 'GLOBAL LINE SHIPPED_QUANTITY: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX )
1246 .SHIPPED_QUANTITY , 1 ) ;
1247 oe_debug_pub.add( 'GLOBAL LINE SHIPPED_QUANTITY2: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX )
1248 .SHIPPED_QUANTITY2 , 1 ) ;
1249 oe_debug_pub.add( 'GLOBAL LINE SHIPPING_QUANTITY_UOM: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX )
1250 .SHIPPING_QUANTITY_UOM , 1 ) ;
1251 oe_debug_pub.add( 'GLOBAL LINE SHIPPING_QUANTITY_UOM2: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX )
1252 .SHIPPING_QUANTITY_UOM2 , 1 ) ; -- INVCONV
1253 oe_debug_pub.add( 'GLOBAL LINE ACTUAL_SHIPMENT_DATE: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX )
1254 .ACTUAL_SHIPMENT_DATE , 1 ) ;
1255 oe_debug_pub.add( 'GLOBAL LINE SHIP_TOLERANCE_BELOW: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX )
1256 .SHIP_TOLERANCE_BELOW , 1 ) ;
1257 oe_debug_pub.add( 'GLOBAL LINE OVER_SHIP_REASON_CODE: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX )
1258 .OVER_SHIP_REASON_CODE , 1 ) ;
1259 oe_debug_pub.add( 'GLOBAL LINE CALCULATE_PRICE_FLAG: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX )
1260 .CALCULATE_PRICE_FLAG , 1 ) ;
1261 oe_debug_pub.add( 'OLD GLOBAL LINE SHIPPED QTY IS: ' || OE_ORDER_UTIL.G_OLD_LINE_TBL ( L_NOTIFY_INDEX )
1262 .SHIPPED_QUANTITY ) ;
1263 oe_debug_pub.add( 'GLOBAL LINE FLOW STATUS IS: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX ) .FLOW_STATUS_CODE
1264 ) ;
1265 oe_debug_pub.add( 'OLD GLOBAL LINE FLOW STATUS IS: ' || OE_ORDER_UTIL.G_OLD_LINE_TBL ( L_NOTIFY_INDEX )
1266 .FLOW_STATUS_CODE ) ;
1267 oe_debug_pub.add( 'OLD GLOBAL LINE Category Code is ' || OE_ORDER_UTIL.G_OLD_LINE_TBL ( L_NOTIFY_INDEX )
1268 .LINE_CATEGORY_CODE ) ;
1269 END IF;
1270
1271 END IF ; /* global entity index null check */
1272
1273 l_loop_index := l_line_tbl.NEXT(l_loop_index);
1274
1275 END LOOP; -- over each line to update global picture
1276
1277 OE_Order_PVT.Process_Requests_And_Notify
1278 ( p_process_requests => TRUE
1279 , p_notify => FALSE -- was l_notify
1280 , x_return_status => l_return_status
1281 , p_line_tbl => l_line_tbl
1282 , p_old_line_tbl => l_old_line_tbl
1283 );
1284
1285 IF l_debug_level > 0 THEN
1286 oe_debug_pub.add( 'RETURNED FROM PROCESS REQUEST AND NOTIFY : '||L_RETURN_STATUS , 3 ) ;
1287 END IF;
1288
1289 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1290 RAISE FND_API.G_EXC_ERROR;
1291 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1292 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1293 END IF;
1294
1295 ELSE /* pre-code H */
1296
1297 OE_Set_Util.Process_Sets
1298 (p_x_line_tbl => l_line_tbl);
1299
1300 OE_Order_PVT.Process_Requests_And_Notify
1301 ( p_process_requests => TRUE
1302 , p_notify => l_notify
1303 , x_return_status => l_return_status
1304 , p_line_tbl => l_line_tbl
1305 , p_old_line_tbl => l_old_line_tbl
1306 );
1307
1308 IF l_debug_level > 0 THEN
1309 oe_debug_pub.add( 'RETURNED FROM PROCESS REQUEST AND NOTIFY : '||L_RETURN_STATUS , 3 ) ;
1310 END IF;
1311
1312 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1313 RAISE FND_API.G_EXC_ERROR;
1314 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1315 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1316 END IF;
1317
1318 END IF; /* code set is pack H or higher */
1319 /* jolin end*/
1320
1321 END IF; --IF p_line_tbl.COUNT > 0
1322
1323 x_return_status := FND_API.G_RET_STS_SUCCESS;
1324
1325 OE_MSG_PUB.Count_And_Get
1326 ( p_count => x_msg_count
1327 , p_data => x_msg_data
1328 );
1329
1330 IF l_debug_level > 0 THEN
1331 oe_debug_pub.add( 'RETURNED FROM OE_SHIP_CONFIRM.SHIP_CONFIRM '||X_RETURN_STATUS , 1 ) ;
1332 END IF;
1333
1334 EXCEPTION
1335
1336 WHEN FND_API.G_EXC_ERROR THEN
1337
1338 IF l_debug_level > 0 THEN
1339 oe_debug_pub.add( 'EXC ERROR : '||SQLERRM , 1 ) ;
1340 END IF;
1341 x_return_status := FND_API.G_RET_STS_ERROR;
1342
1343 -- Get message count and data
1344
1345 OE_MSG_PUB.Count_And_Get
1346 ( p_count => x_msg_count
1347 , p_data => x_msg_data
1348 );
1349
1350 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1351
1352 IF l_debug_level > 0 THEN
1353 oe_debug_pub.add( 'UNEXPECTED ERROR : '||SQLERRM , 1 ) ;
1354 END IF;
1355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1356
1357 -- Get message count and data
1358
1359 OE_MSG_PUB.Count_And_Get
1360 ( p_count => x_msg_count
1361 , p_data => x_msg_data
1362 );
1363
1364 WHEN OTHERS THEN
1365
1366 IF l_debug_level > 0 THEN
1367 oe_debug_pub.add( 'IN OTHERS '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
1368 END IF;
1369
1370 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1371 THEN
1372 FND_MSG_PUB.Add_Exc_Msg
1373 ( G_PKG_NAME
1374 , 'Process_Line'
1375 );
1376 END IF;
1377
1378 -- Get message count and data
1379
1380 OE_MSG_PUB.Count_And_Get
1381 ( p_count => x_msg_count
1382 , p_data => x_msg_data
1383 );
1384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1385
1386 END Ship_Confirm;
1387
1388 PROCEDURE Process_Requests
1389 IS
1390 l_return_status VARCHAR2(1);
1391 --
1392 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1393 --
1394 BEGIN
1395
1396 IF (OE_GLOBALS.G_RECURSION_MODE = 'N'
1397 AND OE_DELAYED_REQUESTS_PVT.Requests_Count > 0 )
1398 THEN
1399
1400 OE_DELAYED_REQUESTS_PVT.Process_Request_for_Entity
1401 (p_entity_code => OE_GLOBALS.G_ENTITY_HEADER_ADJ
1402 ,p_delete => FND_API.G_TRUE
1403 ,x_return_status => l_return_status
1404 );
1405 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1406 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1407 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1408 RAISE FND_API.G_EXC_ERROR;
1409 END IF;
1410
1411 OE_DELAYED_REQUESTS_PVT.Process_Request_for_Entity
1412 (p_entity_code => OE_GLOBALS.G_ENTITY_Header_Price_Att
1413 ,p_delete => FND_API.G_TRUE
1414 ,x_return_status => l_return_status
1415 );
1416 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1417 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1418 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1419 RAISE FND_API.G_EXC_ERROR;
1420 END IF;
1421
1422 OE_DELAYED_REQUESTS_PVT.Process_Request_for_Entity
1423 (p_entity_code => OE_GLOBALS.G_ENTITY_Header_Adj_Att
1424 ,p_delete => FND_API.G_TRUE
1425 ,x_return_status => l_return_status
1426 );
1427 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1428 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1429 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1430 RAISE FND_API.G_EXC_ERROR;
1431 END IF;
1432
1433 OE_DELAYED_REQUESTS_PVT.Process_Request_for_Entity
1434 (p_entity_code => OE_GLOBALS.G_ENTITY_Header_Adj_Assoc
1435 ,p_delete => FND_API.G_TRUE
1436 ,x_return_status => l_return_status
1437 );
1438 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1439 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1440 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1441 RAISE FND_API.G_EXC_ERROR;
1442 END IF;
1443
1444 OE_DELAYED_REQUESTS_PVT.Process_Request_for_Entity
1445 (p_entity_code => OE_GLOBALS.G_ENTITY_Header_Scredit
1446 ,p_delete => FND_API.G_TRUE
1447 ,x_return_status => l_return_status
1448 );
1449 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1451 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1452 RAISE FND_API.G_EXC_ERROR;
1453 END IF;
1454
1455 OE_DELAYED_REQUESTS_PVT.Process_Request_for_Entity
1456 (p_entity_code => OE_GLOBALS.G_ENTITY_LINE
1457 ,p_delete => FND_API.G_TRUE
1458 ,x_return_status => l_return_status
1459 );
1460 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1461 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1462 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1463 RAISE FND_API.G_EXC_ERROR;
1464 END IF;
1465
1466 OE_DELAYED_REQUESTS_PVT.Process_Request_for_Entity
1467 (p_entity_code => OE_GLOBALS.G_ENTITY_LINE_ADJ
1468 ,p_delete => FND_API.G_TRUE
1469 ,x_return_status => l_return_status
1470 );
1471 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1473 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1474 RAISE FND_API.G_EXC_ERROR;
1475 END IF;
1476
1477 OE_DELAYED_REQUESTS_PVT.Process_Request_for_Entity
1478 (p_entity_code => OE_GLOBALS.G_ENTITY_Line_Scredit
1479 ,p_delete => FND_API.G_TRUE
1480 ,x_return_status => l_return_status
1481 );
1482 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1483 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1484 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1485 RAISE FND_API.G_EXC_ERROR;
1486 END IF;
1487
1488 OE_DELAYED_REQUESTS_PVT.Process_Request_for_Entity
1489 (p_entity_code => OE_GLOBALS.G_ENTITY_Line_Price_Att
1490 ,p_delete => FND_API.G_TRUE
1491 ,x_return_status => l_return_status
1492 );
1493 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1495 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1496 RAISE FND_API.G_EXC_ERROR;
1497 END IF;
1498
1499 OE_DELAYED_REQUESTS_PVT.Process_Request_for_Entity
1500 (p_entity_code => OE_GLOBALS.G_ENTITY_Line_Adj_Att
1501 ,p_delete => FND_API.G_TRUE
1502 ,x_return_status => l_return_status
1503 );
1504 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1505 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1506 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1507 RAISE FND_API.G_EXC_ERROR;
1508 END IF;
1509
1510 OE_DELAYED_REQUESTS_PVT.Process_Request_for_Entity
1511 (p_entity_code => OE_GLOBALS.G_ENTITY_Line_Adj_Assoc
1512 ,p_delete => FND_API.G_TRUE
1513 ,x_return_status => l_return_status
1514 );
1515 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1516 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1517 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1518 RAISE FND_API.G_EXC_ERROR;
1519 END IF;
1520
1521 -- Execute all remaining delayed requests. This would execute
1522 -- requests logged against entity G_ENTITY_HEADER and G_ENTITY_ALL
1523
1524 OE_DELAYED_REQUESTS_PVT.Process_Delayed_Requests(
1525 x_return_status => l_return_status
1526 );
1527
1528 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1529 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1530 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1531 RAISE FND_API.G_EXC_ERROR;
1532 END IF;
1533
1534
1535 END IF; --End of requests processing
1536
1537
1538 END Process_Requests;
1539
1540
1541 /*----------------------------------------------------------------
1542 PROCEDURE Validate_Quantity
1543
1544 what about requested quantity 2 check??
1545 code to handle p_handle_req_qty -- decode -- INVCONV ADDED FOR OPM INVENTORY CONVERGENCE
1546 -----------------------------------------------------------------*/
1547 PROCEDURE Validate_Quantity
1548 ( p_ship_line_rec IN OUT NOCOPY Ship_Line_Rec_Type
1549 ,p_handle_req_qty IN VARCHAR2 := 'N'
1550 ,p_index IN NUMBER )
1551 --,x_dual_item OUT NOCOPY VARCHAR2) INVCONV
1552 IS
1553 l_item_rec OE_ORDER_CACHE.item_rec_type;
1554 l_return_status VARCHAR2(1);
1555 l_primary_quantity NUMBER;
1556 l_validated_quantity NUMBER;
1557 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1558 l_secondary_quantity NUMBER; -- bug 12423066
1559 l_ship_sec_qty VARCHAR2(1) := SUBSTR(NVL(fnd_profile.value('ONT_DERIVE_SHPQTY_FROM_SEC'), 'Y'),1,1);
1560 BEGIN
1561
1562 IF l_debug_level > 0 THEN
1563 oe_debug_pub.add('entering Validate_Quantity '|| p_index, 5);
1564 oe_debug_pub.add
1565 ('order uom ' ||p_ship_line_rec.order_quantity_uom(p_index), 5);
1566 oe_debug_pub.add
1567 ('shipping uom ' || p_ship_line_rec.shipping_quantity_uom(p_index), 5);
1568 oe_debug_pub.add
1569 ('order uom 2' ||p_ship_line_rec.order_quantity_uom2(p_index), 5);
1570 oe_debug_pub.add
1571 ('shipping uom 2 ' || p_ship_line_rec.shipping_quantity_uom2(p_index), 5);
1572 oe_debug_pub.add
1573 ('shipping qty ' || p_ship_line_rec.shipping_quantity(p_index), 5);
1574 oe_debug_pub.add
1575 ('shipping qty 2 ' || p_ship_line_rec.shipping_quantity2(p_index), 5);
1576 oe_debug_pub.add
1577 ('ordered qty ' || p_ship_line_rec.ordered_quantity(p_index), 5);
1578 oe_debug_pub.add
1579 ('ordered qty 2 ' || p_ship_line_rec.ordered_quantity2(p_index), 5);
1580 oe_debug_pub.add
1581 ('p_handle_req_qty ' || p_handle_req_qty, 5);
1582 oe_debug_pub.add('ONT_DERIVE_SHPQTY_FROM_SEC:'|| l_ship_sec_qty, 5);
1583 END IF;
1584
1585 IF p_ship_line_rec.order_quantity_uom(p_index) <>
1586 p_ship_line_rec.shipping_quantity_uom(p_index) THEN
1587
1588 IF l_debug_level > 0 THEN
1589 oe_debug_pub.add('uom diff ', 5);
1590 END IF;
1591
1592 IF p_handle_req_qty = 'Y' THEN
1593 l_primary_quantity := p_ship_line_rec.requested_quantity(p_index);
1594 ELSE
1595 l_primary_quantity := p_ship_line_rec.shipping_quantity(p_index);
1596 END IF;
1597
1598 /* IF OE_Line_Util.dual_uom_control -- INVCONV RENAME PROCess_Characteristics INVCONV NO LONGER NEEDED
1599 (p_inventory_item_id => p_ship_line_rec.inventory_item_id(p_index)
1600 ,p_ship_from_org_id => p_ship_line_rec.ship_from_org_id(p_index)
1601 ,x_item_rec => l_item_rec) THEN
1602
1603 IF l_debug_level > 0 THEN
1604 oe_debug_pub.add( 'DUAL PROCESS SHIPPING UPDATE ', 5); -- INVCONV
1605 END IF;
1606
1607 x_dual_item := 'Y'; -- INVCONV
1608
1609 p_ship_line_rec.shipped_quantity(p_index) :=
1610 GMI_Reservation_Util.get_opm_converted_qty
1611 (p_apps_item_id => p_ship_line_rec.inventory_item_id(p_index),
1612 p_organization_id => p_ship_line_rec.ship_from_org_id(p_index),
1613 p_apps_from_uom => p_ship_line_rec.shipping_quantity_uom(p_index),
1614 p_apps_to_uom => p_ship_line_rec.order_quantity_uom(p_index),
1615 p_original_qty => l_primary_quantity);
1616
1617 ELSE */
1618
1619 -- END IF; -- if opm
1620
1621 --Added for 12423066
1622 IF p_handle_req_qty = 'Y' THEN
1623 l_secondary_quantity :=p_ship_line_rec.requested_quantity2(p_index);
1624 ELSE
1625 l_secondary_quantity := p_ship_line_rec.shipping_quantity2(p_index);
1626 END IF;
1627
1628 --Bug#9437761
1629 IF l_ship_sec_qty = 'Y' AND -- condition to the left added for 12423066
1630 p_ship_line_rec.order_quantity_uom2(p_index) IS NOT NULL AND
1631 p_ship_line_rec.order_quantity_uom2(p_index) = p_ship_line_rec.order_quantity_uom(p_index) THEN
1632
1633 IF l_debug_level > 0 THEN
1634 oe_debug_pub.add('Copy primary from secondary', 5);
1635 END IF;
1636
1637 p_ship_line_rec.shipped_quantity(p_index) := l_secondary_quantity; --12423066
1638
1639 ELSE
1640 IF l_debug_level > 0 THEN
1641 oe_debug_pub.add('call OE_Order_Misc_Util.Convert_Uom from primary', 5);
1642 END IF;
1643
1644 IF p_ship_line_rec.shipped_quantity(p_index) IS NULL THEN --ER#14763609
1645 p_ship_line_rec.shipped_quantity(p_index) :=
1646 OE_Order_Misc_Util.Convert_Uom
1647 (p_item_id => p_ship_line_rec.inventory_item_id(p_index),
1648 p_from_uom_code => p_ship_line_rec.shipping_quantity_uom(p_index),
1649 p_to_uom_code => p_ship_line_rec.order_quantity_uom(p_index),
1650 p_from_qty => l_primary_quantity);
1651 END IF ;--14763609
1652 --End for 12423066
1653 END IF ;
1654
1655
1656 ELSE
1657
1658 IF p_handle_req_qty = 'Y' THEN
1659
1660 IF l_debug_level > 0 THEN
1661 oe_debug_pub.add
1662 ('shipped qty ' ||p_ship_line_rec.shipped_quantity(p_index), 5);
1663 oe_debug_pub.add
1664 ('requested qty '||p_ship_line_rec.requested_quantity(p_index), 5);
1665 END IF;
1666
1667 p_ship_line_rec.shipped_quantity(p_index) :=
1668 p_ship_line_rec.requested_quantity(p_index);
1669
1670 p_ship_line_rec.shipped_quantity2(p_index) := -- INVCONV
1671 p_ship_line_rec.requested_quantity2(p_index);
1672
1673 ELSE
1674 IF l_debug_level > 0 THEN
1675 oe_debug_pub.add
1676 ('shping qty '||p_ship_line_rec.shipping_quantity(p_index), 5);
1677 END IF;
1678
1679 p_ship_line_rec.shipped_quantity(p_index) :=
1680 p_ship_line_rec.shipping_quantity(p_index);
1681 p_ship_line_rec.shipped_quantity2(p_index) := -- INVCONV
1682 p_ship_line_rec.shipping_quantity2(p_index);
1683
1684 END IF;
1685
1686 IF l_debug_level > 0 THEN
1687 oe_debug_pub.add
1688 ('uom is same,shpd qty '||p_ship_line_rec.shipped_quantity(p_index), 5);
1689 oe_debug_pub.add
1690 ('uom2 is same,shpd qty2 '||p_ship_line_rec.shipped_quantity2(p_index), 5); -- INVCONV
1691 END IF;
1692
1693
1694 END IF;
1695 -- commented for bug 13808309
1696 /* IF p_ship_line_rec.shipped_quantity(p_index) <>
1697 TRUNC(p_ship_line_rec.shipped_quantity(p_index)) THEN
1698
1699 IF l_debug_level > 0 THEN
1700 oe_debug_pub.add('shipped qty deciaml', 5);
1701 END IF;
1702
1703 Inv_Decimals_PUB.Validate_Quantity
1704 (p_item_id => p_ship_line_rec.inventory_item_id(p_index),
1705 p_organization_id => OE_Sys_Parameters.value('MASTER_ORGANIZATION_ID'),
1706 p_input_quantity => p_ship_line_rec.shipped_quantity(p_index),
1707 p_uom_code => p_ship_line_rec.order_quantity_uom(p_index),
1708 x_output_quantity => l_validated_quantity,
1709 x_primary_quantity => l_primary_quantity,
1710 x_return_status => l_return_status );
1711
1712 IF l_return_status = 'W' THEN
1713 p_ship_line_rec.shipped_quantity(p_index) := l_validated_quantity;
1714 END IF;
1715
1716 IF l_debug_level > 0 THEN
1717 oe_debug_pub.add('RET STS FROM INV API : '
1718 ||L_RETURN_STATUS || l_validated_quantity,1);
1719 END IF;
1720
1721 END IF; -- if decimal
1722 */
1723 END Validate_Quantity;
1724
1725
1726 /*----------------------------------------------------------------
1727 PROCEDURE Handle_Requested_Qty
1728 set recurstion flag ??
1729
1730 Record History
1731 3329866 - Split From Line id is populated for second line
1732 3317898 - split from line id populated incorrectly for models
1733 -----------------------------------------------------------------*/
1734 PROCEDURE Handle_Requested_Qty
1735 ( p_requested_line_rec IN OUT NOCOPY Ship_Line_Rec_Type)
1736 IS
1737 --J NUMBER := 0; bug 4422886
1738 l_split_line_rec OE_Order_Pub.Line_Rec_Type;
1739 l_control_rec OE_GLOBALS.Control_Rec_Type;
1740 l_return_status VARCHAR2(1);
1741 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1742 l_split_line_tbl OE_Order_Pub.Line_Tbl_Type;
1743 l_wdd_count NUMBER;
1744 BEGIN
1745 IF l_debug_level > 0 THEN
1746 oe_debug_pub.add('entering handle_requested_Quantity ', 5);
1747 END IF;
1748
1749 FOR I in p_requested_line_rec.line_id.FIRST..
1750 p_requested_line_rec.line_id.LAST
1751 LOOP
1752
1753 IF l_debug_level > 0 THEN
1754 oe_debug_pub.add('Before checking deleievry detail split ', 1);
1755 oe_debug_pub.add('Item type code :'||p_requested_line_rec.item_type_code(I), 1);
1756 END IF;
1757
1758 -- Bug 7648161
1759 p_requested_line_rec.shipped_quantity.extend;
1760 p_requested_line_rec.shipped_quantity2.extend;
1761
1762 p_requested_line_rec.shipped_quantity(I) := null;
1763 p_requested_line_rec.shipped_quantity2(I) := null;
1764 -- Bug 7648161
1765
1766 IF p_requested_line_rec.item_type_code(I) = 'STANDARD' THEN
1767
1768 SELECT count(*)
1769 INTO l_wdd_count
1770 FROM wsh_delivery_details
1771 WHERE source_line_id = p_requested_line_rec.line_id(I)
1772 AND released_status <> 'D'
1773 AND source_code = 'OE'
1774 AND oe_interfaced_flag = 'N';
1775
1776 END IF;
1777
1778 IF nvl(l_wdd_count,1) > 0 THEN
1779
1780 IF l_debug_level > 0 THEN
1781 OE_DEBUG_PUB.Add('SR5331603 Delivery Detail Has Split..'||l_wdd_count,1);
1782 END IF;
1783
1784 IF l_debug_level > 0 THEN
1785 oe_debug_pub.add('looping over requested qty '
1786 || p_requested_line_rec.requested_quantity(I), 1);
1787 oe_debug_pub.add('looping over ordered qty '
1788 || p_requested_line_rec.ordered_quantity(I), 1);
1789 END IF;
1790
1791 Validate_Quantity
1792 ( p_ship_line_rec => p_requested_line_rec
1793 ,p_handle_req_qty => 'Y'
1794 ,p_index => I );
1795 -- ,x_dual_item => l_return_status); -- INVCONV
1796
1797 l_split_line_Rec := OE_ORDER_PUB.G_MISS_LINE_REC;
1798
1799 --J := J + 1; bug 4422886
1800
1801 IF p_requested_line_rec.ordered_quantity(I) >
1802 p_requested_line_rec.shipped_quantity(I) THEN
1803
1804 IF p_requested_line_rec.ato_line_id(I) IS NOT NULL AND
1805 p_requested_line_rec.item_type_code(I) <> 'STANDARD' THEN
1806
1807 l_split_line_rec.line_id := p_requested_line_rec.top_model_line_id(I);
1808
1809 IF l_debug_level > 0 THEN
1810 oe_debug_pub.add('SPLIT MODEL : '||l_SPLIT_LINE_rec.LINE_ID,3);
1811 END IF;
1812
1813 ELSE
1814
1815 l_split_line_rec.line_id := p_requested_line_rec.line_id(I);
1816
1817 IF l_debug_level > 0 THEN
1818 oe_debug_pub.add('SPLIT LINE : '||L_SPLIT_line_rec.LINE_ID,3);
1819 END IF;
1820
1821 END IF;
1822
1823 l_split_line_rec.line_set_id := p_requested_line_rec.line_set_id(I);
1824 l_split_line_rec.ordered_quantity := p_requested_line_rec.shipped_quantity(I);
1825 l_split_line_rec.ordered_quantity2
1826 -- := nvl(p_requested_line_rec.requested_quantity2(I),0); --?? INVCONV
1827 := p_requested_line_rec.shipped_quantity2(I); -- INVCONV
1828 l_split_line_rec.ship_from_org_id := p_requested_line_rec.ship_from_org_id(I); --9733938
1829
1830
1831 l_split_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
1832 l_split_line_rec.split_action_code := 'SPLIT';
1833 l_split_line_rec.split_by := 'SYSTEM';
1834 l_split_line_rec.change_reason := 'SYSTEM';
1835
1836 l_split_line_tbl(1) := l_split_line_rec; --bug 4422886
1837 IF l_debug_level > 0 THEN
1838 oe_debug_pub.add(1 ||' opr update qty '
1839 || l_split_line_tbl(1).ordered_quantity, 1);
1840 oe_debug_pub.add(1 ||' opr update qty2 '
1841 || l_split_line_tbl(1).ordered_quantity2, 1);
1842
1843 END IF;
1844
1845 --Bug 7534520. Ord qty2 was incorrect because two minus signs
1846 --were used. Removed one of them.
1847
1848 l_split_line_Rec := OE_ORDER_PUB.G_MISS_LINE_REC;
1849
1850 l_split_line_rec.line_set_id := p_requested_line_rec.line_set_id(I);
1851 l_split_line_rec.ordered_quantity
1852 := p_requested_line_rec.ordered_quantity(I)
1853 - p_requested_line_rec.shipped_quantity(I);
1854 l_split_line_rec.ordered_quantity2
1855 := nvl(p_requested_line_rec.ordered_quantity2(I),0) - p_requested_line_rec.shipped_quantity2(I); -- INVCONV
1856 l_split_line_rec.split_from_line_id := l_split_line_tbl(1).line_id;
1857
1858 l_split_line_rec.split_by := 'SYSTEM';
1859 l_split_line_rec.change_reason := 'SYSTEM';
1860 l_split_line_rec.operation := OE_GLOBALS.G_OPR_CREATE;
1861
1862 -- J := J+1; bug 4422886
1863
1864 l_split_line_tbl(2) := l_split_line_rec;
1865
1866 IF l_debug_level > 0 THEN
1867 oe_debug_pub.add(2 ||' opr create qty '|| l_split_line_tbl(2).ordered_quantity
1868 || 'split from ' || l_split_line_tbl(2).split_from_line_id, 1);
1869 oe_debug_pub.add(2 ||' opr create qty2 '|| l_split_line_tbl(2).ordered_quantity2
1870 || 'split from ' || l_split_line_tbl(2).split_from_line_id, 1);
1871 END IF;
1872 l_control_rec.validate_entity := FALSE;
1873 l_control_rec.check_security := FALSE;
1874
1875
1876 OE_Shipping_Integration_Pvt.Call_Process_Order
1877 ( p_line_tbl => l_split_line_tbl,
1878 p_process_requests => TRUE,
1879 p_control_rec => l_control_rec,
1880 x_return_status => l_return_status );
1881
1882 IF l_debug_level > 0 THEN
1883 oe_debug_pub.add('RET STS FROM PROCESS ORDER : '||L_RETURN_STATUS,3);
1884 END IF;
1885
1886 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1887 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1888 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1889 RAISE FND_API.G_EXC_ERROR;
1890 END IF;
1891
1892 ELSE
1893 IF l_debug_level > 0 THEN
1894 oe_debug_pub.add('can not SPLIT LINE, ord qty < ship qty',3);
1895 END IF;
1896 END IF;
1897 END IF; --l_wdd_count condition
1898 END LOOP;
1899
1900 IF l_debug_level > 0 THEN
1901 oe_debug_pub.add('leaving Handle_Requested_Qty',3);
1902 END IF;
1903
1904 EXCEPTION
1905 WHEN others THEN
1906 IF l_debug_level > 0 THEN
1907 oe_debug_pub.add('error in Handle_Requested_Qty: '||sqlerrm,3);
1908 END IF;
1909 RAISE;
1910 END Handle_Requested_Qty;
1911
1912
1913 /*------------------------------------------------------------
1914 PROCEDURE Split_Line
1915
1916 -- In OPM, User can order in a 3rd UOM that is not primary -- INVCONV
1917 nor secondary but a convertable UOM.
1918 3rd conversion case, we need to perform
1919 an item specific conversion
1920
1921 select order_quantity_uom2??
1922 -------------------------------------------------------------*/
1923 PROCEDURE Split_Line
1924 ( p_ship_line_rec IN OUT NOCOPY Ship_Line_Rec_Type
1925 -- ,p_opm_check IN VARCHAR2 := 'Y' -- INVCONV - NOT NEEDed NOW
1926 ,p_index IN NUMBER
1927 ,p_split_model IN VARCHAR2 := 'N')
1928 IS
1929 l_line_tbl OE_Order_Pub.Line_Tbl_Type;
1930 l_control_rec OE_GLOBALS.Control_Rec_Type;
1931 l_return_status VARCHAR2(1);
1932 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1933 l_return NUMBER; --Bug 7528326
1934 BEGIN
1935
1936 IF l_debug_level > 0 THEN
1937 oe_debug_pub.add('entering local split line', 3);
1938 END IF;
1939
1940 l_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
1941 l_line_tbl(2) := l_line_tbl(1);
1942 IF l_debug_level > 0 THEN
1943 oe_debug_pub.add('local split line 1 ', 3);
1944 END IF;
1945
1946 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
1947 l_line_tbl(1).split_by := 'SYSTEM';
1948 l_line_tbl(1).change_reason := 'SYSTEM';
1949 l_line_tbl(1).split_action_code := 'SPLIT';
1950 l_line_tbl(1).ordered_quantity :=
1951 p_ship_line_rec.shipped_quantity(p_index);
1952 l_line_tbl(1).ship_from_org_id :=
1953 p_ship_line_rec.ship_from_org_id(p_index); --9733938
1954
1955 IF l_debug_level > 0 THEN
1956 oe_debug_pub.add('local split line 2 ', 3);
1957 END IF;
1958 l_line_tbl(2).operation := OE_GLOBALS.G_OPR_CREATE;
1959 l_line_tbl(2).split_by := 'SYSTEM';
1960 l_line_tbl(2).change_reason := 'SYSTEM';
1961 l_line_tbl(2).ordered_quantity :=
1962 p_ship_line_rec.ordered_quantity(p_index) -
1963 p_ship_line_rec.shipped_quantity(p_index);
1964
1965 IF l_debug_level > 0 THEN
1966 oe_debug_pub.add('local split line 3 ', 3);
1967 END IF;
1968 IF p_split_model = 'N' THEN
1969
1970 l_line_tbl(1).line_id := p_ship_line_rec.line_id(p_index);
1971 l_line_tbl(2).split_from_line_id := l_line_tbl(1).line_id;
1972
1973 IF l_debug_level > 0 THEN
1974 oe_debug_pub.add('local split line 4 ', 3);
1975 END IF;
1976 IF l_debug_level > 0 THEN
1977 oe_debug_pub.add
1978 ('split single line '|| p_ship_line_rec.line_id(p_index), 3);
1979 oe_debug_pub.add
1980 ('shp qty ' || p_ship_line_rec.shipped_quantity(p_index), 3);
1981 oe_debug_pub.add
1982 ('ord qty ' || p_ship_line_rec.ordered_quantity(p_index), 3);
1983 END IF;
1984
1985 ELSE -- model split
1986
1987 IF p_ship_line_rec.ato_line_id(p_index)
1988 = p_ship_line_rec.top_model_line_id(p_index) THEN
1989
1990 l_line_tbl(1).line_id := p_ship_line_rec.ato_line_id(p_index);
1991 l_line_tbl(2).split_from_line_id := l_line_tbl(1).line_id;
1992
1993 IF l_debug_level > 0 THEN
1994 oe_debug_pub.add
1995 ('split ato '|| p_ship_line_rec.ato_line_id(p_index), 3);
1996 END IF;
1997 -- bug11714193
1998 ELSIF p_ship_line_rec.ato_line_id(p_index) <> p_ship_line_rec.top_model_line_id(p_index)
1999 AND p_ship_line_rec.model_remnant_flag(p_index) = 'Y' THEN
2000
2001 SELECT item_type_code
2002 INTO l_line_tbl(1).item_type_code
2003 FROM oe_order_lines_all
2004 WHERE line_id = p_ship_line_rec.ato_line_id(p_index);
2005
2006 l_line_tbl(1).line_id := p_ship_line_rec.ato_line_id(p_index);
2007 l_line_tbl(1).ato_line_id := p_ship_line_rec.ato_line_id(p_index);
2008 l_line_tbl(2).split_from_line_id := l_line_tbl(1).line_id;
2009
2010 IF l_debug_level > 0 THEN
2011 oe_debug_pub.add ('split ato under pto: '|| p_ship_line_rec.ato_line_id(p_index), 3);
2012 oe_debug_pub.add('item_type_code after query ' || l_line_tbl(1).item_type_code, 3);
2013 END IF;
2014 --end bug11714193
2015
2016 ELSE
2017
2018 l_line_tbl(1).line_id := p_ship_line_rec.top_model_line_id(p_index);
2019 l_line_tbl(2).split_from_line_id := l_line_tbl(1).line_id;
2020
2021
2022
2023 IF l_debug_level > 0 THEN
2024 oe_debug_pub.add
2025 ('split pto '|| p_ship_line_rec.top_model_line_id(p_index), 3);
2026 END IF;
2027 END IF;
2028
2029 END IF;
2030 IF l_debug_level > 0 THEN
2031 oe_debug_pub.add('here 1 index is '|| p_index, 3);
2032 END IF;
2033
2034 -- HW
2035
2036 -- IF p_opm_check = 'Y' AND -- INVCONV -- pete
2037
2038 IF ( p_ship_line_rec.order_quantity_uom2(p_index) is NOT NULL
2039 and p_ship_line_rec.order_quantity_uom2(p_index) <> FND_API.G_MISS_CHAR )
2040 THEN
2041
2042 IF p_ship_line_rec.order_quantity_uom(p_index)
2043 <> p_ship_line_rec.order_quantity_uom2(p_index) THEN
2044
2045 /* l_line_tbl(1).ordered_quantity2
2046
2047 := GMI_Reservation_Util.get_opm_converted_qty
2048 (p_apps_item_id => p_ship_line_rec.inventory_item_id(p_index),
2049 p_organization_id => p_ship_line_rec.ship_from_org_id(p_index),
2050 p_apps_from_uom => p_ship_line_rec.order_quantity_uom(p_index),
2051 p_apps_to_uom => p_ship_line_rec.order_quantity_uom2(p_index),
2052 p_original_qty => l_line_tbl(1).ordered_quantity);
2053
2054 l_line_tbl(2).ordered_quantity2
2055
2056 := GMI_Reservation_Util.get_opm_converted_qty
2057 (p_apps_item_id => p_ship_line_rec.inventory_item_id(p_index),
2058 p_organization_id => p_ship_line_rec.ship_from_org_id(p_index),
2059 p_apps_from_uom => p_ship_line_rec.order_quantity_uom(p_index),
2060 p_apps_to_uom => p_ship_line_rec.order_quantity_uom2(p_index),
2061 p_original_qty => l_line_tbl(2).ordered_quantity); */
2062
2063 --Begin Bug 7528326. Assign shipped_qty2 to ordered_qty2 instead of calculating from ordered_qty.
2064
2065 /*
2066 l_line_tbl(1).ordered_quantity2 := INV_CONVERT.INV_UM_CONVERT(p_ship_line_rec.inventory_item_id(p_index)-- INVCONV
2067 , NULL
2068 ,p_ship_line_rec.ship_from_org_id(p_index)
2069 ,5 --NULL
2070 ,l_line_tbl(1).ordered_quantity
2071 ,p_ship_line_rec.order_quantity_uom(p_index)
2072 ,p_ship_line_rec.order_quantity_uom2(p_index)
2073 ,NULL -- From uom name
2074 ,NULL -- To uom name
2075 );
2076
2077 l_line_tbl(2).ordered_quantity2 := INV_CONVERT.INV_UM_CONVERT(p_ship_line_rec.inventory_item_id(p_index)-- INVCONV
2078 , NULL
2079 ,p_ship_line_rec.ship_from_org_id(p_index)
2080 ,5 --NULL
2081 ,l_line_tbl(2).ordered_quantity
2082 ,p_ship_line_rec.order_quantity_uom(p_index)
2083 ,p_ship_line_rec.order_quantity_uom2(p_index)
2084 ,NULL -- From uom name
2085 ,NULL -- To uom name
2086 );
2087 */
2088
2089 --Assign ship qty2 to order qty2 for original line
2090 IF p_ship_line_rec.shipping_quantity2(p_index) IS NOT NULL THEN
2091 l_line_tbl(1).ordered_quantity2 := p_ship_line_rec.shipping_quantity2(p_index);
2092 ELSE --use conversion
2093 l_line_tbl(1).ordered_quantity2 :=
2094 INV_CONVERT.INV_UM_CONVERT( p_ship_line_rec.inventory_item_id(p_index)-- INVCONV
2095 ,NULL
2096 ,p_ship_line_rec.ship_from_org_id(p_index)
2097 ,5 --NULL
2098 ,l_line_tbl(1).ordered_quantity
2099 ,p_ship_line_rec.order_quantity_uom(p_index)
2100 ,p_ship_line_rec.order_quantity_uom2(p_index)
2101 ,NULL -- From uom name
2102 ,NULL -- To uom name
2103 );
2104 END IF;
2105
2106 --Assign difference between original line order qty2 and original line ship qty2 to
2107 --split line order qty2, only if the difference is grater than or equal to zero.
2108 IF (p_ship_line_rec.shipping_quantity2(p_index) IS NOT NULL AND
2109 p_ship_line_rec.ordered_quantity2(p_index) - p_ship_line_rec.shipping_quantity2(p_index) >= 0) THEN
2110 l_line_tbl(2).ordered_quantity2 := p_ship_line_rec.ordered_quantity2(p_index) -
2111 p_ship_line_rec.shipping_quantity2(p_index);
2112 --Check if order qty1 and order qty2 of split line are within deviation, only for positive qty.
2113 --IF l_line_tbl(2).ordered_quantity2 > 0 THEN
2114 --Bug 12589643. Added '=' when the ordered_quantity2 is 0
2115 IF l_line_tbl(2).ordered_quantity2 >= 0 THEN
2116 l_return := INV_CONVERT.Within_Deviation -- INVCONV
2117 ( p_organization_id => p_ship_line_rec.ship_from_org_id(p_index)
2118 , p_inventory_item_id => p_ship_line_rec.inventory_item_id(p_index)
2119 , p_lot_number => NULL -- p_lot_number -- INVCONV
2120 , p_precision => 5
2121 , p_quantity => l_line_tbl(2).ordered_quantity
2122 , p_uom_code1 => p_ship_line_rec.order_quantity_uom(p_index)
2123 , p_quantity2 => l_line_tbl(2).ordered_quantity2
2124 , p_uom_code2 => p_ship_line_rec.order_quantity_uom2(p_index)
2125 );
2126
2127 IF l_return = 0 THEN --Not within deviation, hence use conversion
2128 l_line_tbl(2).ordered_quantity2 :=
2129 INV_CONVERT.INV_UM_CONVERT( p_ship_line_rec.inventory_item_id(p_index)-- INVCONV
2130 ,NULL
2131 ,p_ship_line_rec.ship_from_org_id(p_index)
2132 ,5 --NULL
2133 ,l_line_tbl(2).ordered_quantity
2134 ,p_ship_line_rec.order_quantity_uom(p_index)
2135 ,p_ship_line_rec.order_quantity_uom2(p_index)
2136 ,NULL -- From uom name
2137 ,NULL -- To uom name
2138 );
2139 END IF;
2140 END IF;
2141 ELSE --use conversion
2142 l_line_tbl(2).ordered_quantity2 :=
2143 INV_CONVERT.INV_UM_CONVERT( p_ship_line_rec.inventory_item_id(p_index)-- INVCONV
2144 ,NULL
2145 ,p_ship_line_rec.ship_from_org_id(p_index)
2146 ,5 --NULL
2147 ,l_line_tbl(2).ordered_quantity
2148 ,p_ship_line_rec.order_quantity_uom(p_index)
2149 ,p_ship_line_rec.order_quantity_uom2(p_index)
2150 ,NULL -- From uom name
2151 ,NULL -- To uom name
2152 );
2153 END IF;
2154 --End Bug 7528326.
2155
2156
2157 IF l_debug_level > 0 THEN
2158 oe_debug_pub.add
2159 ('DUAL 3RD CONVERSION 1 '||l_line_tbl(1).ORDERED_QUANTITY2,1);
2160 END IF;
2161
2162 ELSE -- order uom = order uom 2
2163
2164 -- fix for 8479324 start below
2165 -- re write fix below for BUG 9395809 - INTERFACE TRIP STOP COMPLETES WITH ERROR MSG: A QUANTITY IS REQUIRED ON THE LINE
2166 -- to use shipping_quantity2 as in this flow p_ship_line_rec.shipped_quantity2(p_index) will always come as NULL, as Shipping never passes
2167 -- p_ship_line_rec.shipped_quantity2.
2168
2169 /*-- fix for 8681362 fp of 8479324 start below
2170 l_line_tbl(1).ordered_quantity2 := p_ship_line_rec.shipped_quantity2(p_index);
2171 l_line_tbl(1).ordered_quantity := l_line_tbl(1).ordered_quantity2;
2172 l_line_tbl(2).ordered_quantity2 := p_ship_line_rec.ordered_quantity2(p_index) -
2173 p_ship_line_rec.shipped_quantity2(p_index);
2174 l_line_tbl(2).ordered_quantity := l_line_tbl(2).ordered_quantity2;
2175 -- fix for 8681362 fp of 8479324 end */
2176
2177 l_line_tbl(1).ordered_quantity2 := p_ship_line_rec.shipping_quantity2(p_index);
2178 l_line_tbl(1).ordered_quantity := l_line_tbl(1).ordered_quantity2;
2179
2180 l_line_tbl(2).ordered_quantity2 := p_ship_line_rec.ordered_quantity2(p_index) -
2181 p_ship_line_rec.shipping_quantity2(p_index);
2182
2183 l_line_tbl(2).ordered_quantity := l_line_tbl(2).ordered_quantity2;
2184
2185 -- end BUG 9395809
2186
2187
2188
2189
2190
2191
2192
2193
2194
2195
2196 END IF;
2197
2198 ELSE
2199
2200 l_line_tbl(1).ordered_quantity2 := NULL;
2201 l_line_tbl(2).ordered_quantity2 := NULL;
2202 END IF;
2203
2204
2205
2206 IF l_debug_level > 0 THEN
2207 oe_debug_pub.add('SPLIT FROM : '||L_LINE_TBL(2).SPLIT_FROM_LINE_ID,3);
2208 oe_debug_pub.add('ORD qty old : '||L_LINE_TBL(1).ORDERED_QUANTITY,3);
2209 oe_debug_pub.add('ORD QTY2 old : '||L_LINE_TBL(1).ORDERED_QUANTITY2,3);
2210 oe_debug_pub.add('ORD QTY NEW : '||L_LINE_TBL(2).ORDERED_QUANTITY,3);
2211 oe_debug_pub.add('ORD QTY2 NEW : '||L_LINE_TBL(2).ORDERED_QUANTITY2,3);
2212 END IF;
2213
2214
2215 l_control_rec.validate_entity := FALSE;
2216 l_control_rec.check_security := FALSE;
2217
2218 OE_Shipping_Integration_Pvt.Call_Process_Order
2219 ( p_line_tbl => l_line_tbl,
2220 p_control_rec => l_control_rec,
2221 p_process_requests => TRUE,
2222 x_return_status => l_return_status);
2223
2224 IF l_debug_level > 0 THEN
2225 oe_debug_pub.add('local split line RET STS : '||L_RETURN_STATUS,3);
2226 END IF;
2227
2228 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2229 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2230 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2231 RAISE FND_API.G_EXC_ERROR;
2232 END IF;
2233
2234
2235 EXCEPTION
2236 WHEN others THEN
2237 IF l_debug_level > 0 THEN
2238 oe_debug_pub.add('here 12 index is '|| p_index, 3);
2239 oe_debug_pub.add('error in split_line '||sqlerrm,3);
2240 END IF;
2241 RAISE;
2242 END Split_Line;
2243
2244
2245 /*------------------------------------------------------------
2246 PROCEDURE Fulfill_Remnant_PTO
2247 -------------------------------------------------------------*/
2248 PROCEDURE Fulfill_Remnant_PTO
2249 (p_top_model_line_id IN NUMBER)
2250 IS
2251 l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
2252 l_x_result_out VARCHAR2(30);
2253 l_return_status VARCHAR2(1);
2254 l_msg_count NUMBER;
2255 l_msg_data VARCHAR2(2000);
2256 l_result_code VARCHAR2(30);
2257 l_activity_status VARCHAR2(8);
2258 l_activity_result VARCHAR2(30);
2259 l_activity_id NUMBER;
2260 l_fulfillment_activity VARCHAR2(30);
2261 l_fulfill_tbl OE_Order_Pub.Line_Tbl_Type;
2262 l_fulfill_index NUMBER := 0 ;
2263 l_fulfillment_set_flag VARCHAR2(1); -- for bug 4176692
2264
2265 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2266
2267 CURSOR fulfill_remnant_lines IS
2268 SELECT line_id, ordered_quantity
2269 ,ordered_quantity2 -- bug 12731457
2270 ,header_id, actual_shipment_date, order_firmed_date
2271 ,blanket_number,blanket_line_number,blanket_version_number
2272 FROM oe_order_lines
2273 WHERE top_model_line_id = p_top_model_line_id
2274 AND nvl(ato_line_id,-99) = -99 /*added for bug 6640292*/
2275 AND shippable_flag = 'N'
2276 AND model_remnant_flag = 'Y';
2277 BEGIN
2278 IF l_debug_level > 0 THEN
2279 oe_debug_pub.add('entering fulfill_remnant_pto'||p_top_model_line_id, 3);
2280 END IF;
2281 -- for bug 4176692
2282 l_fulfillment_set_flag :=
2283 OE_Line_Fullfill.Is_Part_Of_Fulfillment_Set(p_top_model_line_id);
2284
2285 FOR line_rec in fulfill_remnant_lines
2286 LOOP
2287 IF l_debug_level > 0 THEN
2288 oe_debug_pub.add('CALLING GET ACTIVITY RESULT FOR : '|| line_rec.line_id, 3);
2289 END IF;
2290
2291 OE_LINE_FULLFILL.Get_Activity_Result
2292 (p_item_type => OE_GLOBALS.G_WFI_LIN
2293 ,p_item_key => to_char(line_rec.line_id)
2294 ,p_activity_name => 'FULFILL_LINE'
2295 ,x_return_status => l_return_status
2296 ,x_activity_result => l_activity_result
2297 ,x_activity_status_code => l_activity_status
2298 ,x_activity_id => l_activity_id );
2299
2300 IF l_debug_level > 0 THEN
2301 oe_debug_pub.add('RET STS FROM GET ACT RESULT: '||L_RETURN_STATUS , 3 ) ;
2302 END IF;
2303
2304 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2306 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2307 IF l_debug_level > 0 THEN
2308 oe_debug_pub.add('LINE IS NOT AT FULFILLMENT ACTIVITY ',3);
2309 END IF;
2310 ELSE
2311 IF l_activity_status = 'NOTIFIED' THEN
2312
2313 IF l_debug_level > 0 THEN
2314 oe_debug_pub.add( 'LINE IS AT FULFILLMENT ACTIVITY ',3);
2315 END IF;
2316
2317 OE_LINE_FULLFILL.Get_Fulfillment_Activity
2318 (p_item_key => line_rec.line_id,
2319 p_activity_id => l_activity_id,
2320 x_fulfillment_activity => l_fulfillment_activity,
2321 x_return_status => l_return_status);
2322
2323 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2324 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2325 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2326 RAISE FND_API.G_EXC_ERROR;
2327 END IF;
2328
2329 IF l_debug_level > 0 THEN
2330 oe_debug_pub.add( 'FULFILLMENT ACT : '||L_FULFILLMENT_ACTIVITY , 3 ) ;
2331 END IF;
2332
2333 IF (l_fulfillment_activity = 'NO_ACTIVITY' OR
2334 l_fulfillment_activity = 'SHIP_LINE') AND
2335 l_fulfillment_set_flag = FND_API.G_FALSE THEN -- bug 4176692
2336
2337 l_fulfill_index := l_fulfill_index + 1;
2338 l_fulfill_tbl(l_fulfill_index) := OE_Order_PUB.G_MISS_LINE_REC;
2339 l_fulfill_tbl(l_fulfill_index).line_id := line_rec.line_id;
2340 l_fulfill_tbl(l_fulfill_index).fulfilled_flag := 'Y';
2341 l_fulfill_tbl(l_fulfill_index).fulfillment_date := SYSDATE;
2342 l_fulfill_tbl(l_fulfill_index).fulfilled_quantity
2343 := line_rec.ordered_quantity;
2344 l_fulfill_tbl(l_fulfill_index).fulfilled_quantity2
2345 := line_rec.ordered_quantity2; -- bug 12731457
2346 l_fulfill_tbl(l_fulfill_index).header_id := line_rec.header_id;
2347 l_fulfill_tbl(l_fulfill_index).actual_shipment_date
2348 := line_rec.actual_shipment_date;
2349 l_fulfill_tbl(l_fulfill_index).order_firmed_date
2350 := line_rec.order_firmed_date;
2351 --BSA changes.
2352 l_fulfill_tbl(l_fulfill_index).blanket_number
2353 := line_rec.blanket_number;
2354 l_fulfill_tbl(l_fulfill_index).blanket_line_number
2355 := line_rec.blanket_line_number;
2356 l_fulfill_tbl(l_fulfill_index).blanket_version_number
2357 := line_rec.blanket_version_number;
2358 --BSA changes
2359
2360 l_fulfill_tbl(l_fulfill_index).operation := OE_GLOBALS.G_OPR_UPDATE;
2361
2362 IF l_debug_level > 0 THEN
2363 oe_debug_pub.add('FULFILLED QUANTITY : '
2364 || L_FULFILL_TBL(L_FULFILL_INDEX).FULFILLED_QUANTITY,3);
2365 -- Added for bug 12731457
2366 oe_debug_pub.add('FULFILLED QUANTITY 2 : '
2367 || L_FULFILL_TBL(L_FULFILL_INDEX).FULFILLED_QUANTITY2,3);
2368 END IF;
2369
2370 END IF; -- no activity
2371 END IF; -- line at fulfillment
2372 END IF; -- return status is error
2373 END LOOP; -- split line tbl has rows
2374
2375
2376 IF l_fulfill_index <> 0 THEN
2377 OE_Line_Fullfill.Fulfill_Line
2378 (p_line_tbl => l_fulfill_tbl,
2379 p_mode => 'TABLE',
2380 p_fulfillment_type => 'No Activity',
2381 p_fulfillment_activity => 'NO_ACTIVITY',
2382 x_return_status => l_return_status);
2383
2384 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2385 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2386 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2387 RAISE FND_API.G_EXC_ERROR;
2388 END IF;
2389
2390 FOR l_fulfill_index IN 1 .. l_fulfill_tbl.count
2391 LOOP
2392 IF l_debug_level > 0 THEN
2393 oe_debug_pub.add( 'CALLING FLOW STATUS API ' , 3 ) ;
2394 END IF;
2395
2396 OE_Order_WF_Util.Update_Flow_Status_Code
2397 (p_header_id => l_fulfill_tbl(l_fulfill_index).header_id,
2398 p_line_id => l_fulfill_tbl(l_fulfill_index).line_id,
2399 p_flow_status_code => 'FULFILLED',
2400 x_return_status => l_return_status);
2401
2402 IF l_debug_level > 0 THEN
2403 oe_debug_pub.add('RET STS FROM FLOW STATUS API '||L_RETURN_STATUS,3);
2404 END IF;
2405
2406 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2407 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2408 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2409 RAISE FND_API.G_EXC_ERROR;
2410 END IF;
2411
2412 OE_Delayed_Requests_Pvt.Log_Request
2413 ( p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
2414 p_entity_id => l_fulfill_tbl(l_fulfill_index).line_id,
2415 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
2416 p_requesting_entity_id => l_fulfill_tbl(l_fulfill_index).line_id,
2417 p_request_type => OE_GLOBALS.G_COMPLETE_ACTIVITY,
2418 p_param1 => OE_GLOBALS.G_WFI_LIN,
2419 p_param2 => to_char(l_fulfill_tbl(l_fulfill_index).line_id),--??
2420 p_param3 => 'FULFILL_LINE',
2421 p_param4 => OE_GLOBALS.G_WFR_COMPLETE,
2422 x_return_status => l_return_status);
2423
2424 OE_LINE_FULLFILL.Fulfill_Service_Lines
2425 (p_line_id => l_fulfill_tbl(l_fulfill_index).line_id,
2426 p_header_id => l_fulfill_tbl(l_fulfill_index).header_id,
2427 x_return_status => l_return_status);
2428
2429 IF l_debug_level > 0 THEN
2430 oe_debug_pub.add( 'RET STS FULFILL SERVICE '||L_RETURN_STATUS , 3 ) ;
2431 END IF;
2432
2433 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2435 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2436 RAISE FND_API.G_EXC_ERROR;
2437 END IF;
2438
2439 END LOOP; -- fulfill table has rows.
2440
2441 END IF; -- fullfill index <> 0
2442
2443 IF l_debug_level > 0 THEN
2444 OE_DEBUG_PUB.Add('Exiting fulfill_remnant_pto', 3);
2445 END IF;
2446 EXCEPTION
2447 WHEN others THEN
2448 IF l_debug_level > 0 THEN
2449 oe_debug_pub.add('error in Fulfill_Remnant_PTO '||sqlerrm,3);
2450 END IF;
2451 RAISE;
2452 END Fulfill_Remnant_PTO;
2453
2454 /*------------------------------------------------------------
2455 PROCEDURE Ship_Confirm_Line
2456
2457 -- price line for atos??
2458 --shipping_quantity_uom2?? in update
2459
2460 Change Record
2461
2462 -------------------------------------------------------------*/
2463 PROCEDURE Ship_Confirm_Line
2464 ( p_ship_line_rec IN OUT NOCOPY Ship_Line_Rec_Type
2465 ,p_check_line_set IN VARCHAR2 := 'Y'
2466 ,p_index IN NUMBER
2467 ,p_model_call IN VARCHAR2 := 'N'
2468 ,p_ato_only IN VARCHAR2 := 'N')
2469 IS
2470 l_return_status VARCHAR2(1);
2471 l_wdd_count NUMBER;
2472 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2473 -- bug4460242
2474 l_activity_status VARCHAR2(8);
2475 l_activity_result VARCHAR2(30);
2476 l_activity_id NUMBER;
2477
2478 BEGIN
2479
2480 IF l_debug_level > 0 THEN
2481 oe_debug_pub.add('entering Ship_Confirm_Line '||p_ato_only
2482 ||'line_id: ' || p_ship_line_rec.line_id(p_index),3);
2483 oe_debug_pub.add('ord qty '||p_ship_line_rec.ordered_quantity(p_index),3);
2484 oe_debug_pub.add('shp qty '||p_ship_line_rec.shipped_quantity(p_index),3);
2485 END IF;
2486
2487 IF p_ato_only = 'Y' THEN
2488
2489 UPDATE oe_order_lines
2490 SET shipped_quantity = (ordered_quantity *
2491 p_ship_line_rec.shipped_quantity(p_index)/
2492 p_ship_line_rec.ordered_quantity(p_index))
2493 ,actual_shipment_date
2494 = p_ship_line_rec.actual_shipment_date(p_index)
2495 ,lock_control = lock_control + 1
2496 --last_updated_by**
2497 WHERE ato_line_id = p_ship_line_rec.ato_line_id(p_index)
2498 AND header_id = p_ship_line_rec.header_id(p_index)
2499 AND shippable_flag = 'N';
2500
2501 END IF;
2502
2503
2504 IF p_ship_line_rec.ato_line_id(p_index) IS NOT NULL AND
2505 p_ship_line_rec.item_type_code(p_index) = 'CONFIG' THEN
2506
2507 -- bug fix 4460242
2508 IF l_debug_level > 0 THEN
2509 oe_debug_pub.add( 'GET ACTIVITY RESULT : '|| p_ship_line_rec.ato_line_id(p_index) , 3 ) ;
2510 END IF;
2511 OE_LINE_FULLFILL.Get_Activity_Result
2512 ( p_item_type => OE_GLOBALS.G_WFI_LIN
2513 , p_item_key => p_ship_line_rec.ato_line_id(p_index)
2514 , p_activity_name => 'WAIT_FOR_CTO'
2515 , x_return_status => l_return_status
2516 , x_activity_result => l_activity_result
2517 , x_activity_status_code => l_activity_status
2518 , x_activity_id => l_activity_id );
2519
2520 IF l_debug_level > 0 THEN
2521 oe_debug_pub.add( 'STATUS GET ACTIVITY RESULT : '||L_RETURN_STATUS , 3 ) ;
2522 END IF;
2523
2524 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2526 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2527 IF l_debug_level > 0 THEN
2528 oe_debug_pub.add( 'LINE IS NOT AT WAIT_FOR_CTO ACTIVITY' , 1 ) ;
2529 END IF;
2530 ELSE
2531 IF l_activity_status = 'NOTIFIED' THEN
2532 IF l_debug_level > 0 THEN
2533 oe_debug_pub.add( 'LINE IS AT WAIT_FOR_CTO ACTIVITY' , 1 ) ;
2534 oe_debug_pub.add( 'COMPLETE ACTIVITY , FOR '|| p_ship_line_rec.ato_line_id(p_index) , 3 ) ;
2535 END IF;
2536
2537 OE_DEBUG_PUB.Add('Log delayed request for Complete CTO-Bug-3471040');
2538
2539 OE_Delayed_Requests_Pvt.Log_Request
2540 ( p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
2541 p_entity_id => p_ship_line_rec.ato_line_id(p_index),
2542 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
2543 p_requesting_entity_id => p_ship_line_rec.ato_line_id(p_index),
2544 p_request_type => OE_GLOBALS.G_COMPLETE_ACTIVITY,
2545 p_param1 => OE_GLOBALS.G_WFI_LIN,
2546 p_param2 => p_ship_line_rec.ato_line_id(p_index),
2547 p_param3 => 'WAIT_FOR_CTO',
2548 p_param4 => OE_GLOBALS.G_WFR_COMPLETE,
2549 x_return_status => l_return_status);
2550
2551 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2552 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2553 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2554 RAISE FND_API.G_EXC_ERROR;
2555 END IF;
2556
2557 IF l_debug_level > 0 THEN
2558 oe_debug_pub.add('ato parent workflow progressed '
2559 || p_ship_line_rec.ato_line_id(p_index), 5);
2560 END IF;
2561 END IF;-- end of IF l_activity_status = 'NOTIFIED'
2562 END IF; -- end of IF l_return_status =SUCCESS OR NOT
2563 END IF;
2564 -- end of bug 4460242
2565
2566
2567 IF l_debug_level > 0 THEN
2568 oe_debug_pub.add('fulfilled? ' ||p_ship_line_rec.fulfilled_flag(p_index)
2569 || ' model? '||p_model_call, 1);
2570 END IF;
2571
2572 IF p_model_call = 'N' THEN
2573
2574 IF p_ship_line_rec.fulfilled_flag(p_index) = 'Y' THEN
2575
2576 IF p_check_line_set = 'Y' AND
2577 p_ship_line_rec.item_type_code(p_index) = 'STANDARD' THEN
2578
2579 Ship_Confirm_Split_Lines
2580 (p_ship_line_rec => p_ship_line_rec
2581 ,p_index => p_index);
2582
2583 END IF;
2584
2585 ELSE
2586
2587 IF p_ship_line_rec.shipped_quantity(p_index)
2588 < p_ship_line_rec.ordered_quantity(p_index) THEN
2589
2590 IF nvl(p_ship_line_rec.ship_tolerance_above(p_index),0) = 0 AND
2591 nvl(p_ship_line_rec.ship_tolerance_below(p_index),0) = 0 AND
2592 p_ship_line_rec.item_type_code(p_index) = 'STANDARD' AND
2593 (floor(p_ship_line_rec.ordered_quantity(p_index))
2594 <> p_ship_line_rec.ordered_quantity(p_index) OR
2595 floor(p_ship_line_rec.shipped_quantity(p_index))
2596 <> p_ship_line_rec.shipped_quantity(p_index)) THEN
2597 IF NVL(p_ship_line_rec.source_type_code(p_index),'INTERNAL') = 'INTERNAL' THEN -- Added for bug 6877315
2598 -- 3590689
2599 BEGIN
2600 -- getting delivery details
2601 SELECT count(*)
2602 INTO l_wdd_count
2603 FROM wsh_delivery_details
2604 WHERE source_line_id = p_ship_line_rec.line_id(p_index)
2605 AND released_status <> 'D'
2606 AND source_code = 'OE'
2607 AND oe_interfaced_flag = 'N';
2608 EXCEPTION
2609 WHEN OTHERS THEN
2610 NULL;
2611 END;
2612
2613 IF l_debug_level > 0 THEN
2614 oe_debug_pub.add('WSH Count : ' || l_wdd_count, 1);
2615 END IF;
2616 END IF; -- For bug 7243039 (Added for bug 6877315)
2617 ELSE
2618 l_wdd_count := 1;
2619 IF l_debug_level > 0 THEN
2620 oe_debug_pub.add('setting the count 1, split' , 1);
2621 END IF;
2622 END IF;
2623
2624 -- END IF; -- Added for bug 6877315 : Commented for bug 7243039
2625
2626 IF l_wdd_count > 0
2627 OR NVL(p_ship_line_rec.source_type_code(p_index),'INTERNAL') = 'EXTERNAL' THEN -- Modified for bug 6877315
2628 Split_Line
2629 ( p_ship_line_rec => p_ship_line_rec
2630 ,p_index => p_index
2631 ,p_split_model => p_ato_only);
2632
2633 IF l_debug_level > 0 THEN
2634 oe_debug_pub.add('after split line for standard item', 5);
2635 END IF;
2636 END IF; -- Item_type_code
2637
2638 END IF;
2639
2640 END IF;
2641 END IF;
2642
2643 BEGIN
2644 IF l_debug_level > 0 THEN
2645 oe_debug_pub.add('loggin request to wf complete', 5);
2646 oe_debug_pub.add('Activity to be performed' || G_SKIP_SHIP, 5); -- Bug 10032407
2647 END IF;
2648
2649 OE_Delayed_Requests_Pvt.Log_Request
2650 ( p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
2651 p_entity_id => p_ship_line_rec.line_id(p_index),
2652 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
2653 p_requesting_entity_id => p_ship_line_rec.line_id(p_index),
2654 p_request_type => G_SKIP_SHIP, -- Bug 10032407
2655 p_param1 => OE_GLOBALS.G_WFI_LIN,
2656 p_param2 => p_ship_line_rec.line_id(p_index),
2657 p_param3 => 'SHIP_LINE',
2658 p_param4 => 'SHIP_CONFIRM',
2659 x_return_status => l_return_status);
2660
2661 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2662 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2663 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2664 RAISE FND_API.G_EXC_ERROR;
2665 END IF;
2666
2667 IF l_debug_level > 0 THEN
2668 oe_debug_pub.add('after wf completion delayed request', 3);
2669 END IF;
2670
2671 EXCEPTION
2672 WHEN others THEN
2673 IF l_debug_level > 0 THEN
2674 oe_debug_pub.ADD('wf CompleteActivity error ' || sqlerrm,3);
2675 END IF;
2676 RAISE;
2677 END;
2678
2679 IF l_debug_level > 0 THEN
2680 oe_debug_pub.add('calling Update_Flow_Status_Code', 3);
2681 END IF;
2682
2683 OE_Order_WF_Util.Update_Flow_Status_Code
2684 (p_header_id => p_ship_line_rec.header_id(p_index),
2685 p_line_id => p_ship_line_rec.line_id(p_index),
2686 p_flow_status_code => 'SHIPPED',
2687 x_return_status => l_return_status );
2688
2689
2690 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2692 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2693 RAISE FND_API.G_EXC_ERROR;
2694 END IF;
2695
2696 IF l_debug_level > 0 THEN
2697 oe_debug_pub.add('leaving Ship_Confirm_Line', 3);
2698 END IF;
2699 EXCEPTION
2700 WHEN others THEN
2701 IF l_debug_level > 0 THEN
2702 oe_debug_pub.add('error in Ship_Confirm_Line ' || sqlerrm, 3);
2703 END IF;
2704
2705 RAISE;
2706 END Ship_Confirm_Line;
2707
2708
2709 /*------------------------------------------------------------
2710 PROCEDURE Ship_Confirm_PTO
2711 Bug 3359702 : Check for line in p_ship_line_rec if it is not there
2712 append it.
2713 -------------------------------------------------------------*/
2714 PROCEDURE Ship_Confirm_PTO
2715 ( p_top_model_line_id IN NUMBER
2716 ,p_index IN NUMBER
2717 ,p_ship_line_rec IN OUT NOCOPY Ship_Line_Rec_Type)
2718 IS
2719 l_count NUMBER;
2720 l_count1 NUMBER;
2721 l_proportional_ship VARCHAR2(1) := 'Y';
2722 l_split_line_tbl OE_Order_Pub.Line_Tbl_Type;
2723 l_return_status VARCHAR2(1) := 'Y';
2724 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2725 l_index NUMBER;
2726 l_model_remnant_flag VARCHAR2(1) := 'N';
2727 l_make_remnant VARCHAR2(1) := 'N'; -- bug 4701487
2728 l_line_found NUMBER := 0;
2729 -- bug4466040
2730 l_shipped_ratio NUMBER;
2731 l_model_ordered_qty NUMBER;
2732 l_new_model_shipped_qty NUMBER;
2733 l_line_model_shipped_qty NUMBER; --added for bug 8774783
2734 l_line_model_ordered_qty NUMBER; --added for bug 8774783
2735
2736 CURSOR shipped_lines IS
2737 SELECT line_id, ordered_quantity, shipped_quantity
2738 FROM oe_order_lines
2739 WHERE shippable_flag = 'Y'
2740 AND shipped_quantity is NOT NULL
2741 AND top_model_line_id = p_top_model_line_id;
2742 BEGIN
2743
2744 SELECT count(*)
2745 INTO l_count1
2746 FROM oe_order_lines
2747 WHERE top_model_line_id = p_top_model_line_id
2748 AND cancelled_flag = 'N'
2749 AND shippable_flag = 'Y';
2750
2751 -- updating remnant flag for various conditions.
2752 --Commented IF condition to handle SMC Case Bug Fix 5008069
2753 -- IF nvl(p_ship_line_rec.smc_flag(p_index), 'N') = 'N' THEN
2754 IF l_debug_level > 0 THEN
2755 oe_debug_pub.add('LINE PART OF NON SMC PTO '|| l_count1, 4);
2756 END IF;
2757
2758 -- if ato+pto with external lines, make it remnant.
2759 IF l_count1 > 1 THEN
2760 SELECT count(*)
2761 INTO l_count
2762 FROM oe_order_lines
2763 WHERE top_model_line_id = p_top_model_line_id
2764 AND cancelled_flag = 'N'
2765 AND source_type_code = 'EXTERNAL';
2766
2767 IF l_count > 0 THEN
2768 IF l_debug_level > 0 THEN
2769 oe_debug_pub.add( 'EXTERNAL LINES EXIST' , 2 ) ;
2770 END IF;
2771 l_make_remnant := 'Y'; -- bug 4701487
2772 END IF;
2773 END IF; -- if more than 1 shippable lines.
2774
2775 -- if non smc model with ato and config line not created
2776 -- make it remanant.
2777 IF l_make_remnant <> 'Y' THEN
2778
2779 l_count := 0;
2780
2781 /* MOAC_SQL_CHANGE */
2782 SELECT count(*)
2783 INTO l_count
2784 FROM oe_order_lines oe1
2785 WHERE top_model_line_id = p_top_model_line_id
2786 AND ato_line_id = line_id
2787 AND item_type_code = 'CLASS'
2788 AND cancelled_flag = 'N'
2789 AND not exists
2790 (SELECT NULL
2791 FROM oe_order_lines_all
2792 WHERE top_model_line_id = p_top_model_line_id
2793 AND ato_line_id = oe1.line_id
2794 AND cancelled_flag = 'N'
2795 AND item_type_code = 'CONFIG');
2796
2797 IF l_count > 0 THEN
2798 IF l_debug_level > 0 THEN
2799 oe_debug_pub.add( 'ATO model line does not have CONFIG created', 2 ) ;
2800 END IF;
2801 l_make_remnant := 'Y'; -- bug 4701487
2802 END IF;
2803 END IF;
2804
2805 IF l_make_remnant <> 'Y' THEN -- bug 4701487
2806 l_count := 0;
2807 BEGIN
2808 SELECT 1 INTO l_count
2809 FROM DUAL
2810 WHERE EXISTS (
2811 SELECT NULL
2812 FROM oe_order_lines
2813 WHERE top_model_line_id = p_top_model_line_id
2814 AND cancelled_flag = 'N'
2815 AND schedule_ship_date is NULL);
2816 EXCEPTION
2817 WHEN OTHERS THEN
2818 NULL;
2819 END;
2820
2821 IF l_count > 0 THEN
2822 IF l_debug_level > 0 THEN
2823 oe_debug_pub.add('Atleast one un-scheduled line exists in the Model...' , 2 ) ;
2824 END IF;
2825 l_make_remnant := 'Y';
2826 END IF;
2827 END IF; -- bug 4701487
2828
2829 IF l_make_remnant = 'Y' THEN
2830 IF l_debug_level > 0 THEN
2831 oe_debug_pub.add( 'Making the model REMNANT:'|| p_TOP_MODEL_LINE_ID , 2) ;
2832 END IF;
2833
2834 UPDATE oe_order_lines
2835 SET model_remnant_flag = 'Y'
2836 ,lock_control = lock_control + 1
2837 WHERE top_model_line_id = p_top_model_line_id
2838 AND cancelled_flag = 'N';
2839
2840 l_model_remnant_flag := 'Y';
2841 END IF;
2842
2843 -- END IF; -- if part of a non smc model.
2844
2845 -------------------- remnant upadate done ---------------------
2846
2847 IF l_debug_level > 0 THEN
2848 oe_debug_pub.add('entering Ship_Confirm_PTO, model line_id: '
2849 || p_top_model_line_id, 3);
2850 END IF;
2851
2852 IF l_model_remnant_flag = 'N' THEN
2853
2854 SELECT count(*)
2855 INTO l_count
2856 FROM oe_order_lines
2857 WHERE top_model_line_id = p_top_model_line_id
2858 AND shippable_flag = 'Y'
2859 AND NVL(CANCELLED_FLAG,'N')='N' --9822866
2860 AND shipped_quantity is NULL;
2861
2862 IF l_count > 0 THEN
2863 IF l_debug_level > 0 THEN
2864 oe_debug_pub.add('1 select '|| l_count , 3);
2865 END IF;
2866 l_proportional_ship := 'N';
2867 ELSE
2868 BEGIN
2869
2870 /* MOAC_SQL_CHANGE */
2871 SELECT count(*)
2872 INTO l_count
2873 FROM oe_order_lines oe1
2874 WHERE top_model_line_id = p_top_model_line_id
2875 AND shippable_flag = 'Y'
2876 AND NVL(OE1.CANCELLED_FLAG,'N')='N' --9822866
2877 AND ordered_quantity/shipped_quantity = ALL
2878 (SELECT ordered_quantity/shipped_quantity
2879 FROM oe_order_lines_all oe2
2880 WHERE oe2.top_model_line_id = oe1.top_model_line_id
2881 AND oe2.line_id <> oe1.line_id
2882 AND oe2.shippable_flag = 'Y'
2883 AND NVL(OE2.CANCELLED_FLAG,'N')='N' --9822866
2884 );
2885
2886 IF l_debug_level > 0 THEN
2887 oe_debug_pub.add('2 select '|| l_count || l_count1 , 3);
2888 END IF;
2889
2890 IF l_count1 = l_count THEN
2891 /* bug fix 4466040 */
2892 -- getting shipped ratio for shippable lines
2893
2894 --begin changes for bug 8774783
2895 /* -- commented for bug 8774783
2896
2897 SELECT shipped_quantity/ordered_quantity
2898 INTO l_shipped_ratio
2899 FROM oe_order_lines_all
2900 WHERE top_model_line_id = p_top_model_line_id
2901 AND shippable_flag = 'Y'
2902 AND ROWNUM =1;
2903
2904 SELECT ordered_quantity
2905 into l_model_ordered_qty
2906 FROM oe_order_lines_all
2907 WHERE line_id = p_top_model_line_id;
2908
2909 l_new_model_shipped_qty :=(l_model_ordered_qty *l_shipped_ratio);
2910 */ --end comment for bug 8774783
2911 SELECT shipped_quantity,ordered_quantity
2912 INTO l_line_model_shipped_qty,l_line_model_ordered_qty
2913 FROM oe_order_lines_all
2914 WHERE top_model_line_id = p_top_model_line_id
2915 AND shippable_flag = 'Y'
2916 AND ROWNUM =1;
2917
2918 SELECT ordered_quantity
2919 into l_model_ordered_qty
2920 FROM oe_order_lines_all
2921 WHERE line_id = p_top_model_line_id;
2922
2923 l_new_model_shipped_qty :=(l_model_ordered_qty *l_line_model_shipped_qty/l_line_model_ordered_qty);
2924
2925 --end changes for bug 8774783
2926
2927 IF ((l_new_model_shipped_qty -TRUNC(l_new_model_shipped_qty,0))=0) THEN
2928 l_proportional_ship := 'Y';
2929 ELSE
2930 -- top_model_line can't be decimal qty
2931 l_proportional_ship := 'N';
2932 END IF;
2933 -- end of bug 4466040
2934 ELSE
2935 l_proportional_ship := 'N';
2936 END IF;
2937 EXCEPTION
2938 WHEN others THEN
2939 IF l_debug_level > 0 THEN
2940 oe_debug_pub.add('others '|| sqlerrm, 3);
2941 END IF;
2942 RAISE;
2943 END;
2944 END IF;
2945
2946 ELSE
2947 l_proportional_ship := 'N';
2948 END IF; -- if proportional shipment
2949
2950
2951 l_count := 0;
2952 l_count1 := 0;
2953
2954 --------------- handle proportional shipment --------------------
2955
2956 IF l_proportional_ship = 'Y' THEN
2957
2958 UPDATE oe_order_lines
2959 SET shipped_quantity = (ordered_quantity *
2960 p_ship_line_rec.shipped_quantity(p_index)/
2961 p_ship_line_rec.ordered_quantity(p_index))
2962 ,actual_shipment_date =
2963 p_ship_line_rec.actual_shipment_date(p_index)
2964 ,lock_control = lock_control + 1
2965 --last_updated_by**
2966 WHERE top_model_line_id = p_top_model_line_id
2967 AND open_flag = 'Y'
2968 AND nvl(cancelled_flag, 'N') = 'N'
2969 AND source_type_code = 'INTERNAL'
2970 AND shippable_flag = 'N';
2971
2972 IF SQL%FOUND THEN
2973 IF l_debug_level > 0 THEN
2974 oe_debug_pub.add('nonshippable lines updated '|| sql%rowcount, 3);
2975 END IF;
2976
2977 END IF;
2978
2979 IF l_debug_level > 0 THEN
2980 oe_debug_pub.add('append model line ' || p_ship_line_rec.line_id.LAST, 3);
2981 END IF;
2982
2983 l_index := p_ship_line_rec.line_id.LAST + 1;
2984 p_ship_line_rec.line_id.extend;
2985 p_ship_line_rec.shipped_quantity.extend;
2986 p_ship_line_rec.ordered_quantity.extend;
2987 p_ship_line_rec.order_quantity_uom2.extend; -- INVCONV 4199186
2988 p_ship_line_rec.ato_line_id.extend;
2989 p_ship_line_rec.top_model_line_id.extend;
2990 p_ship_line_rec.item_type_code.extend;
2991 p_ship_line_rec.ship_from_org_id.extend; -- Bug 10338240
2992
2993 SELECT line_id, shipped_quantity, ordered_quantity, ordered_quantity_uom2, -- INVCONV 4199186
2994 ato_line_id,top_model_line_id, item_type_code, ship_from_org_id -- Bug 10338240
2995 INTO p_ship_line_rec.line_id(l_index),
2996 p_ship_line_rec.shipped_quantity(l_index),
2997 p_ship_line_rec.ordered_quantity(l_index),
2998 p_ship_line_rec.order_quantity_uom2(l_index), -- INVCONV 4199186
2999 p_ship_line_rec.ato_line_id(l_index),
3000 p_ship_line_rec.top_model_line_id(l_index),
3001 p_ship_line_rec.item_type_code(l_index),
3002 p_ship_line_rec.ship_from_org_id(l_index) -- Bug 10338240
3003 FROM oe_order_lines
3004 WHERE line_id = p_top_model_line_id;
3005
3006
3007 IF l_debug_level > 0 THEN
3008 oe_debug_pub.add('p_index of this line '|| p_index, 3);
3009 oe_debug_pub.add
3010 ('shp qty: '|| p_ship_line_rec.shipped_quantity(p_index)
3011 || 'ord qty :'||p_ship_line_rec.ordered_quantity(p_index), 3);
3012 oe_debug_pub.add('model line added to l_index '|| l_index, 3);
3013 oe_debug_pub.add('model shp and ord qtys: '
3014 || p_ship_line_rec.shipped_quantity(l_index) || ' '
3015 || p_ship_line_rec.ordered_quantity(l_index), 3);
3016 END IF;
3017
3018 IF p_ship_line_rec.shipped_quantity(l_index)
3019 < p_ship_line_rec.ordered_quantity(l_index) THEN
3020
3021 IF l_debug_level > 0 THEN
3022 oe_debug_pub.add('calling split line from ship_confirm_pto', 3);
3023 END IF;
3024
3025 Split_Line
3026 ( p_ship_line_rec => p_ship_line_rec
3027 -- ,p_opm_check => 'N' -- INVCONV no longer needed
3028 ,p_index => l_index
3029 ,p_split_model => 'Y');
3030 END IF;
3031
3032 -- delete the appended model line
3033
3034 IF l_debug_level > 0 THEN
3035 oe_debug_pub.add('removing model line', 3);
3036 END IF;
3037
3038 p_ship_line_rec.line_id.trim;
3039 p_ship_line_rec.shipped_quantity.trim;
3040 p_ship_line_rec.ordered_quantity.trim;
3041 p_ship_line_rec.ato_line_id.trim;
3042 p_ship_line_rec.top_model_line_id.trim;
3043 p_ship_line_rec.item_type_code.trim;
3044
3045 IF l_debug_level > 0 THEN
3046 oe_debug_pub.add('done removing model line ' || p_ship_line_rec.line_id.first || '-'||p_ship_line_rec.line_id.last, 3);
3047 END IF;
3048
3049 FOR I in p_ship_line_rec.line_id.first..p_ship_line_rec.line_id.last
3050 LOOP
3051
3052 IF p_ship_line_rec.top_model_line_id(I) = p_top_model_line_id AND
3053 p_ship_line_rec.shippable_flag(i) = 'Y'
3054 -- 4396294
3055 THEN
3056
3057 IF l_debug_level > 0 THEN
3058 oe_debug_pub.add(I || ' calling Ship_Confirm_line for line_id '
3059 ||p_ship_line_rec.line_id(I) , 3);
3060 END IF;
3061
3062 Ship_Confirm_Line
3063 (p_ship_line_rec => p_ship_line_rec
3064 ,p_check_line_set => 'N'
3065 ,p_model_call => 'Y'
3066 ,p_index => I);
3067
3068 END IF;
3069
3070 END LOOP;
3071
3072 IF l_debug_level > 0 THEN
3073 oe_debug_pub.add('1 leaving Ship_Confirm_PTO ', 3);
3074 END IF;
3075
3076 RETURN;
3077 END IF;
3078
3079 ----------------------- handle non proportional -------------
3080
3081 IF l_model_remnant_flag = 'N' THEN
3082 -- send only shippable lines, else all
3083
3084 l_count := 0;
3085
3086 FOR I in p_ship_line_rec.line_id.first..p_ship_line_rec.line_id.last
3087 LOOP
3088
3089 IF p_ship_line_rec.top_model_line_id(I) = p_top_model_line_id THEN
3090 IF l_debug_level > 0 THEN
3091 oe_debug_pub.add('line_id '|| p_ship_line_rec.line_id(I), 1);
3092 END IF;
3093
3094 l_count := l_count + 1;
3095
3096 l_split_line_tbl(l_count).line_id := p_ship_line_rec.line_id(I);
3097 IF l_debug_level > 0 THEN
3098 oe_debug_pub.add('quring row ', 1);
3099 END IF;
3100
3101 OE_Line_Util.Query_Row
3102 ( p_line_id => p_ship_line_rec.line_id(I)
3103 ,x_line_rec => l_split_line_tbl(l_count));
3104 IF l_debug_level > 0 THEN
3105 oe_debug_pub.add('zz2', 1);
3106 END IF;
3107
3108 l_split_line_tbl(l_count).operation := OE_GLOBALS.G_OPR_UPDATE;
3109
3110 l_split_line_tbl(l_count).split_by := 'SYSTEM';
3111 l_split_line_tbl(l_count).change_reason := 'SYSTEM';
3112 l_split_line_tbl(l_count).split_action_code := 'SPLIT';
3113 l_split_line_tbl(l_count).shipped_quantity
3114 := p_ship_line_rec.shipped_quantity(I);
3115 l_split_line_tbl(l_count).ordered_quantity
3116 := p_ship_line_rec.ordered_quantity(I) -
3117 p_ship_line_rec.shipped_quantity(I);
3118 l_split_line_tbl(l_count).item_type_code
3119 := p_ship_line_rec.item_type_code(I);
3120 l_split_line_tbl(l_count).top_model_line_id
3121 := p_ship_line_rec.top_model_line_id(I);
3122 END IF;
3123
3124 END LOOP;
3125
3126 IF l_count > 0 THEN
3127
3128 IF l_debug_level > 0 THEN
3129 oe_debug_pub.add('calling Cascade_Non_Proportional_Split', 3);
3130 END IF;
3131
3132 OE_Split_Util.Cascade_Non_Proportional_Split
3133 (p_x_line_tbl => l_split_line_tbl,
3134 x_return_status => l_return_status);
3135 END IF;
3136
3137 l_index := p_ship_line_rec.line_id.LAST;
3138 l_count1 := 0;
3139
3140 FOR I in l_split_line_tbl.FIRST..l_split_line_tbl.LAST
3141 LOOP
3142 IF l_split_line_tbl(I).shippable_flag = 'Y' AND
3143 l_split_line_tbl(I).shipped_quantity > 0 AND
3144 l_split_line_tbl(I).split_from_line_id is NOT NULL
3145 THEN
3146 IF l_debug_level > 0 THEN
3147 oe_debug_pub.add('append line ' || l_split_line_tbl(I).line_id, 3);
3148 END IF;
3149 l_line_found := 0;
3150
3151 FOR J in p_ship_line_rec.line_id.FIRST..p_ship_line_rec.line_id.LAST
3152 LOOP
3153 IF p_ship_line_rec.line_id(J) = l_split_line_tbl(I).line_id THEN
3154 IF l_debug_level > 0 THEN
3155 OE_DEBUG_PUB.Add('Line Exists already in Ship Rec'||
3156 l_split_line_tbl(I).line_id,3);
3157 END IF;
3158 l_line_found := 1;
3159 EXIT;
3160 END IF;
3161 END LOOP;
3162
3163 -- Append the line only if the line is not found in the
3164 -- Ship line Record.
3165
3166 IF l_line_found = 0 THEN
3167
3168 l_index := l_index + 1;
3169 l_count1 := l_count1 + 1;
3170
3171 p_ship_line_rec.line_id.extend;
3172 p_ship_line_rec.shipped_quantity.extend;
3173 p_ship_line_rec.ordered_quantity.extend;
3174 p_ship_line_rec.top_model_line_id.extend;
3175 p_ship_line_rec.fulfilled_flag.extend;
3176 p_ship_line_rec.header_id.extend;
3177 p_ship_line_rec.ato_line_id.extend;
3178 p_ship_line_rec.item_type_code.extend;
3179
3180 SELECT line_id, shipped_quantity, ordered_quantity,
3181 header_id,ato_line_id,item_type_code
3182 INTO p_ship_line_rec.line_id(l_index),
3183 p_ship_line_rec.shipped_quantity(l_index),
3184 p_ship_line_rec.ordered_quantity(l_index),
3185 p_ship_line_rec.header_id(l_index),
3186 p_ship_line_rec.ato_line_id(l_index),
3187 p_ship_line_rec.item_type_code(l_index)
3188 FROM oe_order_lines
3189 WHERE line_id = l_split_line_tbl(I).line_id;
3190
3191 p_ship_line_rec.top_model_line_id(l_index) := p_top_model_line_id;
3192 -- the top model line id is actually diff, this is to make
3193 -- sure that ship_confirm_line gets called.
3194
3195 p_ship_line_rec.fulfilled_flag(p_index) := 'Y';
3196
3197 END IF;
3198 END IF;
3199 END LOOP;
3200
3201 END IF; -- we did not make it remnant
3202
3203 IF l_debug_level > 0 THEN
3204 oe_debug_pub.add('last: '|| p_ship_line_rec.line_id.last, 5);
3205 END IF;
3206
3207 FOR I in p_ship_line_rec.line_id.first..p_ship_line_rec.line_id.last
3208 LOOP
3209 IF l_debug_level > 0 THEN
3210 oe_debug_pub.add('top model: '|| p_ship_line_rec.top_model_line_id(I), 5);
3211 END IF;
3212 IF p_ship_line_rec.top_model_line_id(I) = p_top_model_line_id THEN
3213
3214 IF l_debug_level > 0 THEN
3215 oe_debug_pub.add('3 calling Ship_Confirm_line '
3216 ||p_ship_line_rec.line_id(I) , 3);
3217 END IF;
3218
3219 IF l_model_remnant_flag = 'Y' THEN
3220 l_proportional_ship := 'N'; -- yes split because did not call splits
3221
3222 IF p_ship_line_rec.shipped_quantity(I) <
3223 p_ship_line_rec.ordered_quantity(I)THEN
3224 p_ship_line_rec.fulfilled_flag(I) := 'N';
3225 ELSE
3226 p_ship_line_rec.fulfilled_flag(I) := 'Y';
3227 END IF;
3228
3229 IF l_debug_level > 0 THEN
3230 oe_debug_pub.add(' ful: ' ||p_ship_line_rec.fulfilled_flag(I), 3);
3231 END IF;
3232 ELSE
3233 l_proportional_ship := 'Y'; -- reusing the variable
3234 END IF;
3235
3236 IF l_debug_level > 0 THEN
3237 oe_debug_pub.add('calling Ship_Confirm_Line '
3238 || l_model_remnant_flag, 3);
3239 END IF;
3240
3241 Ship_Confirm_Line
3242 (p_ship_line_rec => p_ship_line_rec
3243 ,p_check_line_set => 'N'
3244 ,p_model_call => l_proportional_ship
3245 ,p_index => I);
3246 END IF;
3247
3248 IF l_debug_level > 0 THEN
3249 oe_debug_pub.add('done calling Ship_Confirm_Line '
3250 || p_ship_line_rec.line_id(I), 3);
3251 END IF;
3252 END LOOP;
3253
3254 IF l_count1 > 0 THEN
3255
3256 IF l_debug_level > 0 THEN
3257 oe_debug_pub.add('triming appended tables : ' ||l_count1 , 3);
3258 END IF;
3259
3260 p_ship_line_rec.line_id.trim(l_count1);
3261 p_ship_line_rec.shipped_quantity.trim(l_count1);
3262 p_ship_line_rec.ordered_quantity.trim(l_count1);
3263 p_ship_line_rec.top_model_line_id.trim(l_count1);
3264 p_ship_line_rec.fulfilled_flag.trim(l_count1);
3265 p_ship_line_rec.header_id.trim(l_count1);
3266
3267 END IF;
3268
3269
3270 -- Check for remnant model lines and fulfill them.
3271 IF l_split_line_tbl.COUNT > 0 THEN
3272
3273 FOR I in l_split_line_tbl.FIRST..l_split_line_tbl.LAST
3274 LOOP
3275 IF (l_split_line_tbl(I).top_model_line_id =
3276 l_split_line_tbl(I).line_id) AND
3277 l_split_line_tbl(I).model_remnant_flag ='Y' THEN
3278
3279 IF l_debug_level > 0 THEN
3280 oe_debug_pub.add('calling Fulfill_Remnant_PTO ', 3);
3281 END IF;
3282
3283 -- handle all remnant non-shippable lines
3284 Fulfill_Remnant_PTO
3285 (p_top_model_line_id => l_split_line_tbl(I).top_model_line_id);
3286 END IF;
3287 END LOOP;
3288
3289 ELSE -- could be remnant case that we make.
3290 IF l_model_remnant_flag = 'Y' THEN
3291
3292 IF l_debug_level > 0 THEN
3293 oe_debug_pub.add('remnant made by us, calling Fulfill_Remnant_PTO '
3294 || p_top_model_line_id, 3);
3295 END IF;
3296
3297 Fulfill_Remnant_PTO
3298 (p_top_model_line_id => p_top_model_line_id);
3299 END IF;
3300 END IF;
3301
3302 IF l_debug_level > 0 THEN
3303 oe_debug_pub.add('2 leaving Ship_Confirm_PTO ', 3);
3304 END IF;
3305 EXCEPTION
3306 WHEN others THEN
3307 IF l_debug_level > 0 THEN
3308 oe_debug_pub.add('error in Ship_Confirm_PTO '||sqlerrm,3);
3309 END IF;
3310 RAISE;
3311 END Ship_Confirm_PTO;
3312
3313 /*-------------------------------------------------------------
3314 PROCEDURE Ship_Confirm_Split_Lines
3315 This procedure is used to ship confirm split lines when ship
3316 confirmation of other lines in the line set results in fulfilling
3317 the ordered quantity within or beyond tolerance.
3318
3319 It will be used for standard lines and Config items.
3320 p_line_rec provide details about the line getting ship confirmed
3321 we need to cehck if due shipment of this line if we need to
3322 close other lines.
3323
3324 alcoa bug 2605086 - closing lines in line set
3325
3326 Change Record
3327 Bug 3613716 - extend ato_line_id and item_type_code
3328 --------------------------------------------------------------*/
3329 PROCEDURE Ship_Confirm_Split_Lines
3330 ( p_ship_line_rec IN OUT NOCOPY Ship_Line_Rec_Type
3331 ,p_index IN NUMBER)
3332 IS
3333 l_line_set_rec OE_Ship_Confirmation_Pub.Ship_Line_Rec_Type;
3334 l_return_status VARCHAR2(1);
3335 l_count NUMBER;
3336
3337 /* MOAC_SQL_CHANGE */
3338 CURSOR split_lines IS
3339 SELECT line_id, line_set_id, ordered_quantity, ordered_quantity2,
3340 order_quantity_uom, ordered_quantity_uom2, inventory_item_id,
3341 header_id
3342 FROM oe_order_lines oe
3343 WHERE line_id in
3344 (SELECT line_id
3345 FROM oe_order_lines_all
3346 WHERE line_set_id = p_ship_line_rec.line_set_id(p_index)
3347 AND line_id <> p_ship_line_rec.line_id(p_index))
3348 AND open_flag = 'Y'
3349 AND shipped_quantity is NULL
3350 AND line_id in
3351 (SELECT source_line_id
3352 FROM wsh_delivery_details
3353 WHERE source_header_id = oe.header_id);
3354
3355 I NUMBER := 0;
3356 --
3357 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3358 --
3359 BEGIN
3360
3361 IF l_debug_level > 0 THEN
3362 oe_debug_pub.add('entering Ship_Confirm_Split_Lines '
3363 ||p_ship_line_rec.line_id(p_index) ,3);
3364 END IF;
3365
3366 FOR line_rec in split_lines
3367 LOOP
3368
3369 SELECT count(*)
3370 INTO l_count
3371 FROM wsh_delivery_details
3372 WHERE source_line_id = line_rec.line_id
3373 AND released_status <> 'D';
3374
3375 IF l_count > 0 THEN
3376 IF l_debug_level > 0 THEN
3377 oe_debug_pub.add('ignoring this line, can not close '
3378 || line_rec.line_id , 3 ) ;
3379 END IF;
3380 ELSE
3381
3382 IF l_debug_level > 0 THEN
3383 oe_debug_pub.add('now processing '|| line_rec.line_id, 3);
3384 END IF;
3385
3386 I := I + 1;
3387
3388 l_line_set_rec.shipping_quantity2.extend;
3389 l_line_set_rec.shipped_quantity2.extend;
3390
3391 IF p_ship_line_rec.shipped_quantity2(p_index) is not NULL THEN
3392 l_line_set_rec.shipping_quantity2(I) := 0;
3393 l_line_set_rec.shipped_quantity2(I) := 0;
3394 END IF;
3395 IF l_debug_level > 0 THEN
3396 oe_debug_pub.add('Start extending', 3);
3397 END IF;
3398
3399 l_line_set_rec.line_id.extend;
3400 l_line_set_rec.header_id.extend;
3401
3402 l_line_set_rec.shipping_quantity.extend;
3403 l_line_set_rec.shipped_quantity.extend;
3404 l_line_set_rec.shipping_quantity_uom.extend;
3405 l_line_set_rec.shipping_quantity_uom2.extend; -- INVCONV
3406 l_line_set_rec.actual_shipment_date.extend;
3407 l_line_set_rec.fulfilled_flag.extend;
3408 l_line_set_rec.ordered_quantity.extend;
3409 l_line_set_rec.ordered_quantity2.extend; -- INVCONV 4199186
3410 l_line_set_rec.ato_line_id.extend;
3411 l_line_set_rec.item_type_code.extend;
3412
3413 l_line_set_rec.line_id(I) := line_rec.line_id;
3414 l_line_set_rec.header_id(I) := line_rec.header_id;
3415
3416 l_line_set_rec.shipping_quantity(I) := 0;
3417 l_line_set_rec.shipped_quantity(I) := 0;
3418 l_line_set_rec.shipping_quantity_uom(I)
3419 := p_ship_line_rec.shipping_quantity_uom(p_index);
3420
3421 l_line_set_rec.shipping_quantity2(I) := 0; -- INVCONV
3422 l_line_set_rec.shipped_quantity2(I) := 0; -- INVCONV
3423 l_line_set_rec.shipping_quantity_uom2(I) -- INVCONV
3424 := p_ship_line_rec.shipping_quantity_uom2(p_index);
3425
3426 l_line_set_rec.actual_shipment_date(I)
3427 := p_ship_line_rec.actual_shipment_date(p_index);
3428 l_line_set_rec.ordered_quantity(I)
3429 := p_ship_line_rec.ordered_quantity(p_index);
3430 l_line_set_rec.ordered_quantity2(I)
3431 := nvl(p_ship_line_rec.ordered_quantity2(p_index), 0) ; -- INVCONV
3432 l_line_set_rec.fulfilled_flag(I) := 'Y';
3433
3434 Call_Notification_Framework
3435 ( p_ship_line_rec => p_ship_line_rec
3436 ,p_caller => 'SHIP_CONFIRM_SPLIT_LINES');
3437
3438 IF l_debug_level > 0 THEN
3439 oe_debug_pub.add('Before quantities db update', 3);
3440 END IF;
3441
3442 UPDATE OE_ORDER_LINES
3443 SET shipping_quantity = l_line_set_rec.shipping_quantity(I),
3444 shipped_quantity = l_line_set_rec.shipped_quantity(I),
3445 shipping_quantity2 = l_line_set_rec.shipping_quantity2(I),
3446 shipped_quantity2 = l_line_set_rec.shipped_quantity2(I),
3447 shipping_quantity_uom = l_line_set_rec.shipping_quantity_uom(I),
3448 shipping_quantity_uom2 = l_line_set_rec.shipping_quantity_uom2(I), -- INVCONV
3449 actual_shipment_date = l_line_set_rec.actual_shipment_date(I),
3450 lock_control = lock_control + 1
3451 WHERE line_id = l_line_set_rec.line_id(I);
3452
3453 G_SKIP_SHIP := OE_GLOBALS.G_SKIP_ACTIVITY; -- Bug 10032407
3454
3455 IF l_debug_level > 0 THEN
3456 oe_debug_pub.add('line set id '|| line_rec.line_set_id , 3 ) ;
3457 END IF;
3458
3459 Ship_Confirm_Line
3460 (p_ship_line_rec => l_line_set_rec
3461 ,p_check_line_set => 'N'
3462 ,p_index => I);
3463
3464 G_SKIP_SHIP:= OE_GLOBALS.G_COMPLETE_ACTIVITY; -- Bug 10032407
3465
3466 IF l_debug_level > 0 THEN
3467 oe_debug_pub.add('ret sts for me '||l_return_status , 3 ) ;
3468 END IF;
3469
3470 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3471 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3472 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3473 RAISE FND_API.G_EXC_ERROR;
3474 END IF;
3475
3476 END IF; -- if wdd has lines staged/shipped or not deleted
3477
3478 END LOOP;
3479
3480 IF l_debug_level > 0 THEN
3481 oe_debug_pub.add('leaving Ship_Confirm_Split_Lines',3);
3482 END IF;
3483
3484 EXCEPTION
3485 WHEN OTHERS THEN
3486 G_SKIP_SHIP:= OE_GLOBALS.G_COMPLETE_ACTIVITY; -- Bug 10032407
3487 IF l_debug_level > 0 THEN
3488 oe_debug_pub.add('error in Ship_Confirm_Split_Lines '|| sqlerrm,3);
3489 END IF;
3490 RAISE;
3491 END Ship_Confirm_Split_Lines;
3492
3493 /*-------------------------------------------------------------
3494 PROCEDURE Call_Ship_Confirm_Old
3495 --------------------------------------------------------------*/
3496 PROCEDURE Call_Ship_Confirm_Old
3497 ( p_ship_line_rec IN OUT NOCOPY OE_Ship_Confirmation_Pub.Ship_Line_Rec_Type
3498 ,p_index IN NUMBER
3499 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
3500 IS
3501 l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
3502 l_line_adj_tbl OE_ORDER_PUB.Line_adj_Tbl_Type;
3503 l_req_qty_tbl Req_Quantity_Tbl_Type;
3504 l_msg_count NUMBER;
3505 l_msg_data VARCHAR2(2000);
3506 I NUMBER;
3507 --
3508 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3509 --
3510 BEGIN
3511
3512 IF l_debug_level > 0 THEN
3513 oe_debug_pub.add('entering Call_Ship_Confirm_Old', 3);
3514 END IF;
3515
3516 I := p_ship_line_rec.line_id.FIRST;
3517 IF l_debug_level > 0 THEN
3518 oe_debug_pub.add('Initial I is: '|| I, 3);
3519 END IF;
3520
3521 WHILE I is NOT NULL
3522 LOOP
3523 l_line_tbl(I).actual_shipment_date :=
3524 p_ship_line_rec.actual_shipment_date(I);
3525 l_line_tbl(I).shipping_quantity :=
3526 p_ship_line_rec.shipping_quantity(I);
3527 l_line_tbl(I).shipping_quantity2 :=
3528 p_ship_line_rec.shipping_quantity2(I);
3529 l_line_tbl(I).shipping_quantity_uom :=
3530 p_ship_line_rec.shipping_quantity_uom(I);
3531 l_line_tbl(I).shipping_quantity_uom2 :=
3532 p_ship_line_rec.shipping_quantity_uom2(I);
3533 l_line_tbl(I).line_id :=
3534 p_ship_line_rec.line_id(I);
3535 l_line_tbl(I).header_id :=
3536 p_ship_line_rec.header_id(I);
3537 I := p_ship_line_rec.line_id.NEXT(I);
3538 IF l_debug_level > 0 THEN
3539 oe_debug_pub.add('New I is: '|| I, 3);
3540 END IF;
3541 END LOOP;
3542
3543 Ship_Confirm
3544 (p_api_version_number => 1.0
3545 ,p_line_tbl => l_line_tbl
3546 ,p_line_adj_tbl => l_line_adj_tbl
3547 ,p_req_qty_tbl => l_req_qty_tbl
3548 ,x_return_status => x_return_status
3549 ,x_msg_count => l_msg_count
3550 ,x_msg_data => l_msg_data);
3551
3552 IF l_debug_level > 0 THEN
3553 oe_debug_pub.add('leaving Call_Ship_Confirm_Old '|| x_return_status, 3);
3554 END IF;
3555 EXCEPTION
3556 WHEN OTHERS THEN
3557 IF l_debug_level > 0 THEN
3558 oe_debug_pub.add('error in Call_Ship_Confirm_Old '|| sqlerrm,3);
3559 END IF;
3560 RAISE;
3561 END Call_Ship_Confirm_Old;
3562
3563
3564 /*-----------------------------------------------------------
3565 PROCEDURE Remove_Lines_From_Shipset
3566 ??check if you can avoid process order call.
3567
3568 3670530 - Lock the order lines belonging to the ship set before
3569 removing the lines from the ship set. Handle lock
3570 exception in the exception handler
3571 ------------------------------------------------------------*/
3572 PROCEDURE Remove_Lines_From_Shipset
3573 ( p_set_tbl IN ship_confirm_sets)
3574 IS
3575 CURSOR remove_lines(p_ship_set_id NUMBER) IS
3576 SELECT line_id, item_type_code,
3577 top_model_line_id,
3578 nvl(model_remnant_flag, 'N') model_remnant_flag,
3579 fulfilled_quantity,
3580 open_flag,
3581 invoiced_quantity
3582 FROM oe_order_lines
3583 WHERE ship_set_id = p_ship_set_id
3584 AND shipped_quantity is NULL;
3585
3586 I NUMBER;
3587 J NUMBER;
3588 l_set_tbl OE_Order_Pub.Line_Tbl_Type;
3589 l_control_rec OE_GLOBALS.Control_Rec_Type;
3590 l_return_status VARCHAR2(1);
3591 l_line_id NUMBER ;
3592 --
3593 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3594 --
3595 BEGIN
3596
3597 IF l_debug_level > 0 THEN
3598 oe_debug_pub.add('entering Remove_Lines_From_Shipset',3);
3599 END IF;
3600
3601 I := p_set_tbl.FIRST;
3602 WHILE I is NOT NULL
3603 LOOP
3604 IF l_debug_level > 0 THEN
3605 Oe_Debug_pub.Add('check if set id needs to be updated '|| I, 3);
3606 END IF;
3607
3608 J := 0;
3609
3610 -- FOR line_rec in remove_lines(I) -- Bug 8795918
3611 FOR line_rec in remove_lines(p_set_tbl(I))
3612 LOOP
3613 --- 4052633
3614 IF NVL(line_rec.fulfilled_quantity,0) > 0 OR
3615 NVL(line_rec.invoiced_quantity,0) > 0 OR
3616 line_rec.open_flag = 'N' THEN
3617
3618 IF l_debug_level > 0 THEN
3619 oe_debug_pub.add('DIRECT UPDATE LINE ID: '||line_rec.line_id,3);
3620 END IF;
3621
3622 -- do direct update
3623 UPDATE oe_order_lines
3624 SET ship_set_id = NULL
3625 WHERE line_id=line_rec.line_id;
3626
3627 ELSE
3628 -- lock the lines belonging to the particular set
3629 SELECT line_id
3630 INTO l_line_id
3631 FROM oe_order_lines
3632 WHERE line_id = line_rec.line_id
3633 FOR UPDATE NOWAIT;
3634
3635 J := J + 1;
3636
3637 l_set_tbl(J) := OE_Order_Pub.G_MISS_LINE_REC;
3638
3639 IF line_rec.model_remnant_flag = 'N' AND
3640 line_rec.top_model_line_id is NOT NULL THEN
3641 l_set_tbl(J).line_id := line_rec.top_model_line_id;
3642 ELSE
3643 l_set_tbl(J).line_id := line_rec.line_id;
3644 END IF;
3645
3646 IF l_debug_level > 0 THEN
3647 oe_debug_pub.add('unshipped LINE ID : '||L_SET_TBL(J).LINE_ID,3);
3648 END IF;
3649
3650 l_set_tbl(J).ship_set_id := NULL;
3651 l_set_tbl(J).operation := OE_GLOBALS.G_OPR_UPDATE;
3652 END IF;
3653 END LOOP; -- all unshipped lines are in l_set_tbl
3654
3655 IF J > 0 THEN
3656 l_control_rec := OE_GLOBALS.G_MISS_CONTROL_REC;
3657 l_control_rec.validate_entity := FALSE;
3658 l_control_rec.check_security := FALSE;
3659
3660 IF l_debug_level > 0 THEN
3661 oe_debug_pub.add('UPDATE SHIP SET ID '|| J,3);
3662 END IF;
3663
3664 OE_Shipping_Integration_Pvt.Call_Process_Order
3665 ( p_line_tbl => l_set_tbl,
3666 p_control_rec => l_control_rec,
3667 x_return_status => l_return_status );
3668
3669 IF l_debug_level > 0 THEN
3670 oe_debug_pub.add('RET STS FROM PROCESS ORDER : '||L_RETURN_STATUS,3);
3671 END IF;
3672
3673 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3675 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3676 RAISE FND_API.G_EXC_ERROR;
3677 END IF;
3678 END IF;
3679
3680 I := p_set_tbl.NEXT(I);
3681 END LOOP; -- loop over the set tbl
3682
3683 IF l_debug_level > 0 THEN
3684 oe_debug_pub.add('leaving Remove_Lines_From_Shipset',3);
3685 END IF;
3686
3687 EXCEPTION
3688 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
3689 IF l_debug_level > 0 THEN
3690 OE_DEBUG_PUB.Add('Unable to lock the line',3);
3691 END IF;
3692 OE_Msg_Pub.Add_Text('Could not obtain Lock on Order Line/s');
3693 RAISE FND_API.G_EXC_ERROR;
3694
3695 WHEN OTHERS THEN
3696 IF l_debug_level > 0 THEN
3697 oe_debug_pub.add('error in Remove_Lines_From_Shipset '|| sqlerrm,3);
3698 END IF;
3699 RAISE;
3700 END Remove_Lines_From_Shipset;
3701
3702 /*-----------------------------------------------------------
3703 PROCEDURE Handle_NonBulk_Mode
3704 ??making remnant -- do it in ship_confirm_pto
3705
3706 3358774 -- Update Ordered Quantity if there is a call to
3707 Handle_Requested_Quantity
3708 3613716 -- extend fields ordered_quantuty,ship_tolerance_below
3709 and ship_tolerance_above
3710 3670530 -- If the order line belongs to a set, lock the
3711 corresponding set from oe_sets table. handle lock
3712 exception in the exception handler. also, move the
3713 code to lock order lines to a separate block
3714 ------------------------------------------------------------*/
3715 PROCEDURE Handle_NonBulk_Mode
3716 ( p_ship_line_rec IN OUT NOCOPY Ship_Line_Rec_Type
3717 ,p_requested_line_rec IN OUT NOCOPY Ship_Line_Rec_Type
3718 ,x_return_status OUT NOCOPY VARCHAR2)
3719 IS
3720 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3721 l_split_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
3722 J NUMBER;
3723 l_price_control_rec QP_PREQ_GRP.control_record_type;
3724 l_request_rec OE_Order_PUB.request_rec_type;
3725 l_rem_top_model_line_id NUMBER := -1;
3726 l_varchar1 VARCHAR2(1);
3727 l_model_tbl ship_confirm_models;
3728 l_set_tbl ship_confirm_sets;
3729 I NUMBER;
3730 l_line_tbl OE_Order_Pub.Line_Tbl_Type;
3731 l_old_line_tbl OE_Order_Pub.Line_Tbl_Type;
3732 l_return_status VARCHAR2(1);
3733 l_ship_set_id NUMBER := -1;
3734 l_arrival_set_id NUMBER := -1;
3735 --bug 3654553
3736 K NUMBER;
3737 l_change_line_tbl Oe_Line_Adj_Util.G_CHANGED_LINE_TBL1;
3738 l_order_has_lines Oe_Order_Adj_Pvt.Index_Tbl_Type;
3739 l_ind Number;
3740 l_ind_hdr Number;
3741 l_top_model_line_id_mod Number; -- Bug 8795918
3742 l_ship_set_id_mod NUMBER; -- Bug 8795918
3743 l_cnt_config NUMBER ; --14840826
3744 --CC Reversal ER#16014135 Start
3745 l_msg_count NUMBER := 0;
3746 l_msg_data VARCHAR2(2000);
3747 l_cc_rev_reauth_code VARCHAR2(30) := NULL;
3748 l_header_rec OE_Order_PUB.Header_Rec_Type;
3749 l_result_out VARCHAR2(30);
3750 --CC Reversal ER#16014135 End
3751
3752 BEGIN
3753
3754 IF l_debug_level > 0 THEN
3755 Oe_Debug_pub.Add('----------entering handle_nonbulk_mode', 3);
3756 END IF;
3757 --9354229
3758 oe_globals.g_call_process_req := FALSE;
3759
3760 x_return_status := FND_API.G_RET_STS_SUCCESS;
3761 IF l_debug_level > 0 THEN
3762 oe_debug_pub.ADD('request_line_rec line_id count: ' || p_requested_line_rec.line_id.COUNT, 3);
3763 END IF;
3764
3765 -- Setting the message context, bug 4516453
3766 OE_MSG_PUB.set_msg_context(
3767 p_entity_code => 'HEADER'
3768 ,p_entity_id => p_ship_line_rec.header_id(p_ship_line_rec.header_id.FIRST)
3769 ,p_header_id => p_ship_line_rec.header_id(p_ship_line_rec.header_id.FIRST)
3770 ,p_line_id => null
3771 ,p_order_source_id => null
3772 ,p_orig_sys_document_ref => null
3773 ,p_orig_sys_document_line_ref => null
3774 ,p_change_sequence => null
3775 ,p_source_document_type_id => null
3776 ,p_source_document_id => null
3777 ,p_source_document_line_id => null );
3778
3779 IF p_requested_line_rec.line_id.COUNT > 0 THEN
3780 Handle_Requested_Qty
3781 (p_requested_line_rec => p_requested_line_rec);
3782
3783 FOR I in p_ship_line_rec.line_id.FIRST..p_ship_line_rec.line_id.LAST
3784 LOOP
3785 -- 3590689
3786 -- Added ship_tolerance_below, ship_tolerance_above
3787 -- 3613716 - extending the fields
3788 -- 4396294 - removed size to extend, no need
3789 p_ship_line_rec.ordered_quantity.extend;
3790 p_ship_line_rec.ship_tolerance_below.extend;
3791 p_ship_line_rec.ship_tolerance_above.extend;
3792
3793 SELECT ordered_quantity,
3794 ship_tolerance_below,
3795 ship_tolerance_above
3796 INTO p_ship_line_rec.ordered_quantity(I),
3797 p_ship_line_rec.ship_tolerance_below(I),
3798 p_ship_line_rec.ship_tolerance_above(I)
3799 FROM oe_order_lines_all
3800 WHERE line_id = p_ship_line_rec.line_id(I);
3801 END LOOP;
3802 END IF;
3803
3804 IF l_debug_level > 0 THEN
3805 oe_debug_pub.add('last- ' || p_ship_line_rec.line_id.LAST || ' first- '||
3806 p_ship_line_rec.line_id.FIRST, 5);
3807 END IF;
3808
3809 J := p_ship_line_rec.line_id.LAST-p_ship_line_rec.line_id.FIRST + 1;
3810
3811
3812 IF l_debug_level > 0 THEN
3813 oe_debug_pub.add('extending the tables '|| J, 1);
3814 END IF;
3815
3816 --3590689
3817 p_ship_line_rec.ship_tolerance_below.extend(J);
3818 p_ship_line_rec.ship_tolerance_above.extend(J);
3819 /* We are extending tolerance_xxx for use in
3820 ship_confirm_line. These 2 vars are only populated
3821 when requested_line_rec count is non-zero. See comment
3822 in ship_confrim_line for details */
3823
3824 p_ship_line_rec.shipped_quantity.extend(J);
3825 p_ship_line_rec.shipped_quantity2.extend(J);
3826 p_ship_line_rec.shippable_flag.extend(J); -- 4396294
3827 p_ship_line_rec.source_type_code.extend(J); -- Bug 7218408
3828
3829 J := -1;
3830
3831 -- SAVEPOINT opm_check; -- INVCONV
3832
3833 FOR I in p_ship_line_rec.line_id.FIRST..p_ship_line_rec.line_id.LAST
3834 LOOP
3835
3836 IF p_ship_line_rec.shipped_quantity(I) IS NULL THEN --14763609
3837 p_ship_line_rec.shipped_quantity(I) := NULL;
3838 END IF;--14763609
3839
3840 -- p_ship_line_rec.shipped_quantity(I) := null;--14763609
3841 p_ship_line_rec.shipped_quantity2(I) := null;
3842 p_ship_line_rec.model_remnant_flag(I):=
3843 nvl(p_ship_line_rec.model_remnant_flag(I), 'N');
3844
3845 IF l_debug_level > 0 THEN
3846 oe_debug_pub.add(I || 'looping over ship line rec, line_id: '
3847 || p_ship_line_rec.line_id(I), 5);
3848 END IF;
3849
3850 BEGIN
3851 --Bug #5058663 start
3852 IF l_debug_level > 0 THEN
3853 oe_debug_pub.ADD('Ship_line_rec.line_id '|| i || ':' || p_ship_line_rec.line_id(I), 5);
3854 END IF;
3855 --
3856 SELECT top_model_line_id, shippable_flag
3857 INTO p_ship_line_rec.top_model_line_id(I), p_ship_line_rec.shippable_flag(I)
3858 FROM OE_ORDER_LINES
3859 WHERE line_id = p_ship_line_rec.line_id(I)
3860 FOR UPDATE NOWAIT;
3861 --
3862
3863 IF l_debug_level > 0 THEN
3864 oe_debug_pub.add('aa2 locked line' , 1);
3865 oe_debug_pub.ADD('shippable_flag '|| i || ':' || p_ship_line_rec.shippable_flag(i), 5);
3866 oe_debug_pub.ADD('top_model_line_id '|| i || ':' || p_ship_line_rec.top_model_line_id(I), 5);
3867
3868 END IF;
3869
3870 IF p_ship_line_rec.top_model_line_id(I) is NOT NULL AND
3871 p_ship_line_rec.top_model_line_id(I) <> l_rem_top_model_line_id THEN
3872
3873 SELECT top_model_line_id
3874 INTO l_rem_top_model_line_id
3875 FROM oe_order_lines
3876 WHERE line_id = p_ship_line_rec.top_model_line_id(I)
3877 FOR UPDATE NOWAIT;
3878 --Bug #5058663 End of changes.
3879 IF l_debug_level > 0 THEN
3880 oe_debug_pub.add('aa3 model locked '|| l_rem_top_model_line_id, 1);
3881 END IF;
3882 END IF;
3883
3884 -- 4396294
3885
3886 EXCEPTION
3887 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
3888 IF l_debug_level > 0 THEN
3889 OE_DEBUG_PUB.Add('Unable to lock the line',3);
3890 END IF;
3891 OE_Msg_Pub.Add_Text('Could not obtain Lock on Order Line/s');
3892 RAISE FND_API.G_EXC_ERROR;
3893 WHEN others THEN
3894 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3895 IF l_debug_level > 0 THEN
3896 Oe_Debug_pub.Add('error in handle_nonbulk_mode '|| sqlerrm, 3);
3897 END IF;
3898 RAISE;
3899 END ;
3900
3901 -- locking OE_SETS table also
3902 BEGIN
3903 IF p_ship_line_rec.ship_set_id(I) IS NOT NULL AND
3904 p_ship_line_rec.ship_set_id(I) <> l_ship_set_id THEN
3905 SELECT Set_id
3906 INTO l_ship_set_id
3907 FROM OE_SETS
3908 WHERE set_id = p_ship_line_rec.ship_set_id(I)
3909 FOR UPDATE NOWAIT ;
3910 IF l_debug_level > 0 THEN
3911 oe_debug_pub.add('locked ship set id : '|| l_ship_set_id);
3912 END IF;
3913 END IF ;
3914
3915 EXCEPTION
3916 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
3917 IF l_debug_level > 0 THEN
3918 OE_DEBUG_PUB.Add('Unable to lock the ship set :' || l_ship_set_id ,3);
3919 END IF;
3920 OE_Msg_Pub.Add_Text('Could not obtain Lock on Ship Set');
3921 RAISE FND_API.G_EXC_ERROR;
3922 WHEN others THEN
3923 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3924 IF l_debug_level > 0 THEN
3925 Oe_Debug_pub.Add('error in handle_nonbulk_mode '|| sqlerrm, 3);
3926 END IF;
3927 RAISE;
3928
3929 END ;
3930
3931 BEGIN
3932 IF p_ship_line_rec.arrival_set_id(I) IS NOT NULL AND
3933 p_ship_line_rec.arrival_set_id(I) <> l_arrival_set_id THEN
3934
3935 SELECT Set_id
3936 INTO l_arrival_set_id
3937 FROM OE_SETS
3938 WHERE set_id = p_ship_line_rec.arrival_set_id(I)
3939 FOR UPDATE NOWAIT ;
3940 IF l_debug_level > 0 THEN
3941 oe_debug_pub.add('locked arrival set id : ' || l_arrival_set_id);
3942 END IF;
3943 END IF ;
3944
3945 EXCEPTION
3946 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
3947 IF l_debug_level > 0 THEN
3948 OE_DEBUG_PUB.Add('Unable to lock the arrival set :' || l_arrival_set_id ,3);
3949 END IF;
3950 OE_Msg_Pub.Add_Text('Could not obtain Lock on Arrival Set');
3951 RAISE FND_API.G_EXC_ERROR;
3952 WHEN others THEN
3953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3954 IF l_debug_level > 0 THEN
3955 Oe_Debug_pub.Add('error in handle_nonbulk_mode '|| sqlerrm, 3);
3956 END IF;
3957 RAISE;
3958
3959 END ;
3960
3961
3962 ------------------------- locking done -------------
3963
3964 Validate_Quantity
3965 ( p_ship_line_rec => p_ship_line_rec
3966 ,p_index => I );
3967 -- ,x_dual_item => l_varchar1); -- INVCONV
3968
3969 /* IF l_debug_level > 0 THEN INVCONV WHOLE IF l_varchar1 = 'Y' THEN NOT USED.
3970 Oe_Debug_pub.Add('after Validate_Quantity '|| l_varchar1, 3);
3971 END IF;
3972
3973 IF l_varchar1 = 'Y' THEN
3974
3975 ROLLBACK to opm_check;
3976
3977 IF l_debug_level > 0 THEN
3978 Oe_Debug_pub.Add('opm item found'|| p_ship_line_rec.line_id(I), 5);
3979 END IF;
3980
3981 OE_Delayed_Requests_PVT.Clear_Request
3982 ( x_return_status => x_return_status);
3983
3984 Call_Ship_Confirm_Old
3985 ( p_ship_line_rec => p_ship_line_rec
3986 ,p_index => I
3987 ,x_return_status => x_return_status);
3988
3989 IF l_debug_level > 0 THEN
3990 Oe_Debug_pub.Add('returning ...'|| x_return_status, 5);
3991 END IF;
3992
3993 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3994 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3995 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3996 RAISE FND_API.G_EXC_ERROR;
3997 END IF;
3998
3999 RETURN;
4000
4001 END IF; */ -- INVCONV
4002
4003 IF p_ship_line_rec.fulfilled_flag(I) = 'N' AND
4004 p_ship_line_rec.shipped_quantity(I) < p_ship_line_rec.ordered_quantity(I)
4005 THEN
4006 -- line will split
4007 IF p_ship_line_rec.calculate_price_flag(I) = 'Y' THEN
4008 p_ship_line_rec.calculate_price_flag(I) := 'P';
4009 END IF;
4010 END IF;
4011
4012 Call_Notification_Framework
4013 ( p_ship_line_rec => p_ship_line_rec
4014 ,p_caller => 'HANDLE_NON_BULK');
4015
4016 UPDATE OE_ORDER_LINES
4017 SET shipping_quantity = p_ship_line_rec.shipping_quantity(I),
4018 shipping_quantity2 = p_ship_line_rec.shipping_quantity2(I),
4019 shipped_quantity2 = p_ship_line_rec.shipping_quantity2(I),
4020 shipped_quantity = p_ship_line_rec.shipped_quantity(I),
4021 shipping_quantity_uom = p_ship_line_rec.shipping_quantity_uom(I),
4022 shipping_quantity_uom2 = p_ship_line_rec.shipping_quantity_uom2(I), -- INVCONV
4023 actual_shipment_date = p_ship_line_rec.actual_shipment_date(I),
4024 over_ship_reason_code = p_ship_line_rec.over_ship_reason_code(I),
4025 calculate_price_flag = p_ship_line_rec.calculate_price_flag(I),
4026 lock_control = lock_control + 1
4027 WHERE line_id = p_ship_line_rec.line_id(I);
4028
4029 IF l_debug_level > 0 THEN
4030 Oe_Debug_pub.Add('shipped qty updated to: '
4031 || p_ship_line_rec.shipped_quantity(I) || ' shp qty 2 is :'
4032 || p_ship_line_rec.shipping_quantity2(I), 3);
4033 END IF;
4034
4035 IF p_ship_line_rec.arrival_set_id(I) IS NOT NULL AND
4036 p_ship_line_rec.arrival_set_id(I) <> J
4037 THEN
4038 UPDATE OE_SETS
4039 SET SET_STATUS = 'C'
4040 WHERE SET_ID = p_ship_line_rec.arrival_set_id(I)
4041 AND SET_STATUS <> 'C';
4042
4043 IF SQL%FOUND AND
4044 l_debug_level > 0 THEN
4045 oe_debug_pub.add('arrival SET CLOSED: '
4046 || p_ship_line_rec.arrival_set_id(I), 3 ) ;
4047 END IF;
4048
4049 J := p_ship_line_rec.arrival_set_id(I);
4050 END IF;
4051
4052 ------------- validate, update and close arrival set --------
4053
4054 IF l_debug_level > 0 THEN
4055 OE_DEBUG_PUB.Add('Before Logging Pricing Delayed Request');
4056 END IF;
4057
4058
4059 IF p_ship_line_rec.calculate_price_flag(I) IN ('Y','P') OR
4060 (p_ship_line_rec.shipping_quantity2(I) IS NOT NULL
4061 AND p_ship_line_rec.shipping_quantity2(I) <> 0 ) -- bug 3598987,3659454
4062 THEN
4063 --bug 3654553, 8795918
4064
4065 IF p_ship_line_rec.line_id(I) > OE_GLOBALS.G_BINARY_LIMIT THEN
4066 l_ind := mod(p_ship_line_rec.line_id(I),OE_GLOBALS.G_BINARY_LIMIT);
4067 ELSE
4068 l_ind := p_ship_line_rec.line_id(I);
4069 END IF;
4070 IF p_ship_line_rec.header_id(I) > OE_GLOBALS.G_BINARY_LIMIT THEN
4071 l_ind_hdr := mod(p_ship_line_rec.header_id(I),OE_GLOBALS.G_BINARY_LIMIT);
4072 ELSE
4073 l_ind_hdr := p_ship_line_rec.header_id(I);
4074 END IF;
4075 /* Caching lines which needs to be priced */
4076 IF NOT l_change_line_tbl.EXISTS(l_ind) THEN
4077 l_change_line_tbl(l_ind).line_id := p_ship_line_rec.line_id(I);
4078 l_change_line_tbl(l_ind).header_id := p_ship_line_rec.header_id(I);
4079 END IF;
4080 /* Caching the order which has at least a line that is being shipped */
4081 IF NOT l_order_has_lines.EXISTS(l_ind_hdr) THEN
4082 l_order_has_lines(l_ind_hdr) := p_ship_line_rec.header_id(I);
4083 END IF;
4084
4085 END IF;
4086
4087
4088 IF p_ship_line_rec.ship_set_id(I) is NOT NULL THEN
4089 l_ship_set_id_mod := mod(p_ship_line_rec.ship_set_id(I),OE_GLOBALS.G_BINARY_LIMIT); --Bug 8795918
4090 -- IF l_set_tbl.EXISTS(p_ship_line_rec.ship_set_id(I)) -- Bug 8795918
4091 IF l_set_tbl.EXISTS(l_ship_set_id_mod)
4092 THEN
4093
4094 IF l_debug_level > 0 THEN
4095 Oe_Debug_pub.Add('set id exists ', 5);
4096 END IF;
4097
4098 --l_set_tbl(p_ship_line_rec.ship_set_id(I))
4099 --:= l_set_tbl(p_ship_line_rec.ship_set_id(I)) +1;
4100 ELSE
4101 -- l_set_tbl(p_ship_line_rec.ship_set_id(I)) := 1;-- Bug 8795918
4102 --l_set_tbl(l_ship_set_id_mod) := 1; commented for bug 14777162
4103 l_set_tbl(l_ship_set_id_mod) := l_ship_set_id_mod; --Bug 14777162
4104
4105 UPDATE OE_SETS
4106 SET SET_STATUS = 'C'
4107 WHERE SET_ID = p_ship_line_rec.ship_set_id(I)
4108 AND SET_STATUS <> 'C';
4109
4110 IF l_debug_level > 0 THEN
4111 Oe_Debug_pub.Add('set closed '|| p_ship_line_rec.ship_set_id(I), 3);
4112 END IF;
4113 END IF;
4114 END IF;
4115 IF l_debug_level > 0 THEN
4116 Oe_Debug_pub.Add('decide - ship confirm now', 3);
4117 END IF;
4118 IF p_ship_line_rec.top_model_line_id(I) is not null AND
4119 p_ship_line_rec.top_model_line_id(I) <>
4120 nvl(p_ship_line_rec.ato_line_id(I), -1) AND
4121 p_ship_line_rec.model_remnant_flag(I) = 'N' THEN
4122 l_top_model_line_id_mod := mod(p_ship_line_rec.top_model_line_id(I),OE_GLOBALS.G_BINARY_LIMIT);-- Bug 8795918
4123 IF l_debug_level > 0 THEN
4124 Oe_Debug_pub.Add('this line is part of a pto model', 3);
4125 END IF;
4126 -- IF l_model_tbl.EXISTS(p_ship_line_rec.top_model_line_id(I)) -- Bug 8795918
4127 IF l_model_tbl.EXISTS(l_top_model_line_id_mod)
4128 THEN
4129 IF l_debug_level > 0 THEN
4130 Oe_Debug_pub.Add('here 1-2-3 '
4131 || p_ship_line_rec.top_model_line_id(I), 3);
4132 END IF;
4133 ELSE
4134 -- l_model_tbl(p_ship_line_rec.top_model_line_id(I)) := I; -- Bug 8795918
4135 l_model_tbl(l_top_model_line_id_mod) := I;
4136
4137 IF l_debug_level > 0 THEN
4138 Oe_Debug_pub.Add(p_ship_line_rec.top_model_line_id(I)
4139 || ' added model ' || l_model_tbl(l_top_model_line_id_mod), 3);
4140 -- || l_model_tbl(p_ship_line_rec.top_model_line_id(I)), 3); -- Bug 8795918
4141 END IF;
4142 END IF; -- added the model to table
4143
4144 ELSE -- standard or remnant
4145 IF l_debug_level > 0 THEN
4146 Oe_Debug_pub.Add
4147 ('line is standalone '|| p_ship_line_rec.ato_line_id(I), 3);
4148 END IF;
4149 IF p_ship_line_rec.top_model_line_id(I) = p_ship_line_rec.ato_line_id(I)
4150 AND p_ship_line_rec.item_type_code(I) = 'CONFIG' THEN
4151 l_varchar1 := 'Y';
4152 ELSE
4153 l_varchar1 := 'N';
4154 END IF;
4155 -- bug11714193
4156 Oe_Debug_pub.Add('l_varchar1 before ::: '|| l_varchar1);
4157 IF p_ship_line_rec.top_model_line_id(I) <> p_ship_line_rec.ato_line_id(I)
4158 AND p_ship_line_rec.item_type_code(I) = 'CONFIG'
4159 AND p_ship_line_rec.model_remnant_flag(I) ='Y' THEN
4160
4161 --bug 14840826 start
4162 ---There are few test cases where there stil may be two (or more) config lines associated with
4163 --- same ato model line.. in that case we should not split the whole ato model hence pass l_varchar1 as N
4164
4165 l_cnt_config := 0 ;
4166 SELECT Count(*)
4167 INTO l_cnt_config
4168 FROM oe_order_lines_all
4169 WHERE ato_line_id = p_ship_line_rec.ato_line_id(I)
4170 AND item_type_code = 'CONFIG' ;
4171
4172 IF l_debug_level > 0 THEN
4173 Oe_Debug_pub.Add('l_varchar1 l_cnt_config ::: '|| l_cnt_config);
4174 END IF;
4175
4176 IF l_cnt_config > 1 THEN
4177 l_varchar1 := 'N';
4178 ELSE
4179 l_varchar1 := 'Y';
4180 END IF ;
4181 -- l_varchar1 := 'Y';
4182 --end bug 14840826
4183 IF l_debug_level > 0 THEN
4184 Oe_Debug_pub.Add('l_varchar1 after::: '|| l_varchar1);
4185 END IF;
4186 END IF;
4187 -- bug11714193
4188 IF l_debug_level > 0 THEN
4189 Oe_Debug_pub.Add('------ calling Ship_Confirm_Line '|| l_varchar1, 3);
4190 END IF;
4191
4192 Ship_Confirm_Line
4193 (p_ship_line_rec => p_ship_line_rec
4194 ,p_index => I
4195 ,p_ato_only => l_varchar1);
4196 END IF; -- if model
4197
4198 IF l_debug_level > 0 THEN
4199 Oe_Debug_pub.Add
4200 (I ||' -------- done with this line: '||p_ship_line_rec.line_id(I), 5);
4201 END IF;
4202 END LOOP; -- end of big loop
4203
4204 /* bug 3654553 calling to register changed lines */
4205 K := l_change_line_tbl.FIRST;
4206 WHILE K IS NOT NULL
4207 LOOP
4208 Oe_Line_Adj_Util.Register_Changed_Lines
4209 ( p_line_id => l_change_line_tbl(K).line_id
4210 ,p_header_id => l_change_line_tbl(K).header_id
4211 ,p_operation => OE_GLOBALS.G_OPR_UPDATE);
4212 K := l_change_line_tbl.NEXT(K);
4213 END LOOP;
4214 l_change_line_tbl.delete;
4215
4216 /* bug 3654553 logging price_order request to price the whole order at one go
4217 intead of pricing line by line to improve performance
4218 */
4219 K := l_order_has_lines.FIRST;
4220 WHILE K is NOT NULL
4221 LOOP
4222 OE_delayed_requests_Pvt.log_request
4223 ( p_entity_code => OE_GLOBALS.G_ENTITY_ALL
4224 ,p_entity_id => l_order_has_lines(K)
4225 ,p_requesting_entity_code => OE_GLOBALS.G_ENTITY_ALL
4226 ,p_requesting_entity_id => l_order_has_lines(K)
4227 ,p_request_unique_key1 => 'SHIP'
4228 ,p_param1 => l_order_has_lines(K)
4229 ,p_param2 => 'SHIP'
4230 ,p_request_type => OE_GLOBALS.G_PRICE_ORDER
4231 ,x_return_status => l_return_status);
4232
4233 IF l_debug_level > 0 THEN
4234 Oe_Debug_pub.Add('Ret sts After Delayed Req'||l_return_status, 4);
4235 END IF;
4236
4237 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4238 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4239 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4240 RAISE FND_API.G_EXC_ERROR;
4241 END IF;
4242
4243 K := l_order_has_lines.NEXT(K);
4244 END LOOP;
4245 l_order_has_lines.delete;
4246 --bug 3654553
4247
4248
4249 I := l_model_tbl.FIRST;
4250 WHILE I is NOT NULL
4251 LOOP
4252 IF l_debug_level > 0 THEN
4253 Oe_Debug_pub.Add('----------- calling Ship_Confirm_PTO '|| I, 3);
4254 END IF;
4255
4256 Ship_Confirm_PTO
4257 ( p_top_model_line_id => p_ship_line_rec.top_model_line_id(l_model_tbl(I))
4258 -- ( p_top_model_line_id => I -- Bug 8795918
4259 ,p_index => l_model_tbl(I)
4260 ,p_ship_line_rec => p_ship_line_rec);
4261
4262 I := l_model_tbl.NEXT(I);
4263 END LOOP;
4264
4265 IF l_set_tbl.COUNT > 0 THEN
4266 Remove_Lines_From_Shipset
4267 (p_set_tbl => l_set_tbl);
4268 END IF;
4269
4270 --9354229
4271 oe_globals.g_call_process_req := TRUE;
4272
4273 IF l_debug_level > 0 THEN
4274 oe_debug_pub.add('calling Process_Requests_And_Notify......', 1);
4275 END IF;
4276
4277 OE_Order_PVT.Process_Requests_And_Notify
4278 ( p_process_requests => TRUE
4279 ,p_notify => TRUE
4280 ,x_return_status => x_return_status
4281 ,p_line_tbl => l_line_tbl
4282 ,p_old_line_tbl => l_old_line_tbl );
4283
4284 OE_MSG_PUB.Reset_Msg_Context (p_entity_code => 'HEADER'); -- bug 4516453
4285
4286 --CC Reversal ER#16014135 Start
4287 FOR I in p_ship_line_rec.line_id.FIRST..p_ship_line_rec.line_id.LAST
4288 LOOP
4289 l_cc_rev_reauth_code:= NULL;
4290 l_cc_rev_reauth_code:= OE_VERIFY_PAYMENT_PUB.Get_CC_Rev_Reauth_Code(p_ship_line_rec.header_id(I));
4291 IF l_cc_rev_reauth_code is not null and l_cc_rev_reauth_code in ('REV_REAUTH_AFTER_SHIP','REV_REAUTH','CC_HOLD') THEN
4292 BEGIN
4293 IF l_debug_level > 0 THEN
4294 OE_DEBUG_PUB.ADD('Calling OE_Verify_Payment_PUB.Authorize_MultiPayments',5);
4295 END IF;
4296 OE_Header_UTIL.Query_Row
4297 (p_header_id => p_ship_line_rec.header_id(I)
4298 ,x_header_rec => l_header_rec
4299 );
4300 OE_Verify_Payment_PUB.Authorize_MultiPayments
4301 ( p_header_rec => l_header_rec
4302 , p_calling_action => 'REVERSAL_REAUTH'
4303 , p_risk_eval_flag => null
4304 , p_msg_count => l_msg_count
4305 , p_msg_data => l_msg_data
4306 , p_result_out => l_result_out
4307 , p_return_status => l_return_status
4308 );
4309
4310 l_request_rec.return_status := l_return_status;
4311 IF l_debug_level > 0 THEN
4312 oe_debug_pub.add('After call to Authorize_MultiPayments return status : ' || l_return_status,5);
4313 END IF;
4314
4315 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4316 RAISE FND_API.G_EXC_ERROR;
4317 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4319 END IF;
4320 END;
4321 END IF;
4322 END LOOP;
4323 --CC Reversal ER#16014135 End
4324
4325 IF l_debug_level > 0 THEN
4326 Oe_Debug_pub.Add('leaving handle_nonbulk_mode '||x_return_status, 3);
4327 END IF;
4328
4329 EXCEPTION
4330 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
4331 IF l_debug_level > 0 THEN
4332 OE_DEBUG_PUB.Add('Unable to lock the line',3);
4333 END IF;
4334 --9354229
4335 oe_globals.g_call_process_req := TRUE;
4336 OE_Msg_Pub.Add_Text('Could not obtain Lock on Order Line/s');
4337 OE_MSG_PUB.Save_API_Messages(); --bug 4516453
4338 OE_MSG_PUB.Reset_Msg_Context (p_entity_code => 'HEADER'); -- bug 4516453
4339 RAISE FND_API.G_EXC_ERROR;
4340
4341 WHEN others THEN
4342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4343 IF l_debug_level > 0 THEN
4344 Oe_Debug_pub.Add('error in handle_nonbulk_mode '|| sqlerrm, 3);
4345 END IF;
4346 --9354229
4347 oe_globals.g_call_process_req := TRUE;
4348 OE_MSG_PUB.Save_API_Messages(); --bug 4516453
4349 OE_MSG_PUB.Reset_Msg_Context (p_entity_code => 'HEADER'); -- bug 4516453
4350 RAISE;
4351 END Handle_NonBulk_Mode;
4352
4353
4354 /*-----------------------------------------------------------
4355 PROCEDURE Handle_Bulk_Mode
4356
4357 The code from this API has been moved to Handle_Bulk_Mode_per_order
4358 api, written for bug 4170119
4359 ------------------------------------------------------------*/
4360 PROCEDURE Handle_Bulk_Mode
4361 ( p_ship_line_rec IN OUT NOCOPY Ship_Line_Rec_Type
4362 ,p_line_adj_rec IN Ship_Adj_Rec_Type
4363 ,p_start_index IN NUMBER
4364 ,p_end_index IN NUMBER
4365 ,x_return_status OUT NOCOPY VARCHAR2)
4366 IS
4367 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4368 l_order_start_index NUMBER;
4369 l_order_end_index NUMBER;
4370 l_index NUMBER;
4371 l_temp_index NUMBER;
4372 l_temp_header_id NUMBER;
4373
4374 BEGIN
4375 x_return_status := FND_API.G_RET_STS_SUCCESS;
4376
4377 l_temp_header_id := p_ship_line_rec.header_id(p_start_index);
4378 l_order_start_index := p_start_index;
4379
4380 IF l_temp_header_id = p_ship_line_rec.header_id(p_end_index) THEN
4381
4382 IF l_debug_level > 0 THEN
4383 oe_debug_pub.ADD('only one order in the trip', 1);
4384 END IF;
4385
4386 Handle_Bulk_Mode_Per_Order
4387 ( p_ship_line_rec => p_ship_line_rec
4388 ,p_line_adj_rec => p_line_adj_rec
4389 ,p_start_index => p_start_index
4390 ,p_end_index => p_end_index
4391 ,x_return_status => x_return_status );
4392
4393 IF l_debug_level > 0 THEN
4394 oe_debug_pub.ADD('1 leaving Handle_Bulk_Mode '|| x_return_status, 1);
4395 END IF;
4396
4397 RETURN;
4398 END IF;
4399
4400 FOR I IN p_start_index..p_end_index
4401 LOOP
4402
4403 IF l_debug_level > 0 THEN
4404 oe_debug_pub.ADD(I ||' ---------- shipping_quantity '
4405 || p_ship_line_rec.shipping_quantity(I),5);
4406 oe_debug_pub.ADD('shipping_quantity2 '
4407 || p_ship_line_rec.shipping_quantity2(I),5);
4408 oe_debug_pub.ADD('shipping_quantity_uom '
4409 || p_ship_line_rec.shipping_quantity_uom(I),5);
4410 oe_debug_pub.ADD('shipping_quantity_uom2 '
4411 || p_ship_line_rec.shipping_quantity_uom2(I),5);
4412 oe_debug_pub.ADD('actual_shipment_date '
4413 || p_ship_line_rec.actual_shipment_date(I),5);
4414 oe_debug_pub.ADD('line_id '
4415 || p_ship_line_rec.line_id(I),5);
4416 END IF;
4417
4418 -- header change logic here
4419 IF p_ship_line_rec.header_id(I) <> l_temp_header_id THEN
4420 l_order_end_index := I - 1;
4421 IF l_debug_level > 0 THEN
4422 oe_debug_pub.ADD('order start index '|| l_order_start_index
4423 || '--order end index '|| l_order_end_index , 1);
4424 END IF;
4425
4426 Handle_Bulk_Mode_Per_Order
4427 ( p_ship_line_rec => p_ship_line_rec
4428 ,p_line_adj_rec => p_line_adj_rec
4429 ,p_start_index => l_order_start_index
4430 ,p_end_index => l_order_end_index
4431 ,x_return_status => x_return_status );
4432
4433 l_order_start_index := I;
4434 l_temp_header_id := p_ship_line_rec.header_id(I);
4435
4436 END IF;
4437
4438 IF I = p_end_index THEN
4439 IF l_debug_level > 0 THEN
4440 oe_debug_pub.ADD('order start index '|| l_order_start_index
4441 || '--order end index '|| p_end_index , 1);
4442 END IF;
4443
4444 Handle_Bulk_Mode_Per_Order
4445 ( p_ship_line_rec => p_ship_line_rec
4446 ,p_line_adj_rec => p_line_adj_rec
4447 ,p_start_index => l_order_start_index
4448 ,p_end_index => p_end_index
4449 ,x_return_status => x_return_status );
4450
4451 END IF;
4452 END LOOP;
4453
4454 IF l_debug_level > 0 THEN
4455 oe_debug_pub.ADD('2 leaving Handle_Bulk_Mode '|| x_return_status, 1);
4456 END IF;
4457
4458 EXCEPTION
4459 WHEN others THEN
4460 IF l_debug_level > 0 THEN
4461 oe_debug_pub.add('error in Handle_Bulk_Mode ' || sqlerrm, 1);
4462 END IF;
4463
4464 RAISE;
4465 END Handle_Bulk_Mode;
4466
4467 /*-----------------------------------------------------------
4468 * PROCEDURE Handle_Bulk_Mode_Per_Order
4469 * The code from the Handle_bulk_mode API has been put in this new api for
4470 * bug4170119
4471 *
4472 * locking -- update table will lock rows, not doing for now
4473 * before notification framework.
4474 *
4475 * change record:
4476 * bug bugs 3544045, 3544209: a mix of internal and external
4477 * lines with complete shipped quantity should make the
4478 * model remnant even in case of BULK model call from WSH.
4479 *
4480 * Bug 3679500: Changed the return_status variable to be l_return_status
4481 * in the call to Price_Line and Process_Requests_And_Notify. Previously,
4482 * x_return_status was being used.
4483 * ------------------------------------------------------------*/
4484 PROCEDURE Handle_Bulk_Mode_Per_Order
4485 ( p_ship_line_rec IN OUT NOCOPY Ship_Line_Rec_Type
4486 ,p_line_adj_rec IN Ship_Adj_Rec_Type
4487 ,p_start_index IN NUMBER
4488 ,p_end_index IN NUMBER
4489 ,x_return_status OUT NOCOPY VARCHAR2)
4490 IS
4491 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4492 l_price_control_rec QP_PREQ_GRP.control_record_type;
4493 l_request_rec OE_Order_PUB.request_rec_type;
4494 l_line_tbl OE_ORDER_PUB.line_Tbl_type;
4495 l_old_line_tbl OE_ORDER_PUB.line_Tbl_type;
4496 l_return_status VARCHAR2(1);
4497 l_last_top_model NUMBER := -1;
4498 --bug 3654553
4499 J NUMBER;
4500 K NUMBER;
4501 l_change_line_tbl Oe_Line_Adj_Util.G_CHANGED_LINE_TBL1;
4502 l_order_has_lines Oe_Order_Adj_Pvt.Index_Tbl_Type;
4503 l_ind Number;
4504 l_ind_hdr Number;
4505 -- tso
4506 l_top_container_model Varchar2(1);
4507 l_part_of_container Varchar2(1);
4508 l_last_calc_price NUMBER; -- Bug 7149219
4509 --CC Reversal ER#16014135 Start
4510 l_msg_count NUMBER := 0;
4511 l_msg_data VARCHAR2(2000);
4512 l_result_out VARCHAR2(30);
4513 l_cc_rev_reauth_code VARCHAR2(30) := NULL;
4514 l_header_rec OE_Order_PUB.Header_Rec_Type;
4515 --CC Reversal ER#16014135 End
4516
4517 BEGIN
4518
4519 IF l_debug_level > 0 THEN
4520 oe_debug_pub.ADD('entering Handle_Bulk_Mode_Per_Order '
4521 || p_ship_line_rec.header_id(p_start_index),5);
4522 END IF;
4523
4524 x_return_status := FND_API.G_RET_STS_SUCCESS;
4525
4526 -- Setting the message context, bug 4516453
4527 OE_MSG_PUB.set_msg_context(
4528 p_entity_code => 'HEADER'
4529 ,p_entity_id => p_ship_line_rec.header_id(p_start_index)
4530 ,p_header_id => p_ship_line_rec.header_id(p_start_index)
4531 ,p_line_id => null
4532 ,p_order_source_id => null
4533 ,p_orig_sys_document_ref => null
4534 ,p_orig_sys_document_line_ref => null
4535 ,p_change_sequence => null
4536 ,p_source_document_type_id => null
4537 ,p_source_document_id => null
4538 ,p_source_document_line_id => null );
4539
4540 Call_Notification_Framework
4541 ( p_ship_line_rec => p_ship_line_rec
4542 ,p_start_index => p_start_index
4543 ,p_end_index => p_end_index
4544 ,p_caller => 'HANDLE_BULK');
4545
4546 FORALL I in p_start_index..p_end_index
4547
4548 UPDATE OE_ORDER_LINES_ALL
4549 SET shipping_quantity = p_ship_line_rec.shipping_quantity(I),
4550 shipping_quantity2 = p_ship_line_rec.shipping_quantity2(I),
4551 shipped_quantity2 = p_ship_line_rec.shipping_quantity2(I),
4552 shipped_quantity = p_ship_line_rec.ordered_quantity(I),
4553 -- shipped_quantity2 = p_ship_line_rec.ordered_quantity2(I), -- INVCONV
4554 shipping_quantity_uom = p_ship_line_rec.shipping_quantity_uom(I),
4555 shipping_quantity_uom2 = p_ship_line_rec.shipping_quantity_uom2(I),
4556 actual_shipment_date = p_ship_line_rec.actual_shipment_date(I),
4557 flow_status_code = 'SHIPPED',
4558 last_update_date = sysdate,--6901322
4559 lock_control = lock_control + 1
4560 WHERE line_id = p_ship_line_rec.line_id(I);
4561
4562
4563 IF SQL%FOUND THEN
4564 oe_debug_pub.ADD('updated lines with shipped qty '|| sql%rowcount,1);
4565 END IF;
4566
4567 --bug 10331066
4568 FOR I in p_start_index..p_end_index loop
4569 OE_ORDER_UTIL.RAISE_BUSINESS_EVENT(p_ship_line_rec.header_id(I),
4570 p_ship_line_rec.line_id(I),
4571 'SHIPPED');
4572 END LOOP;
4573
4574 FORALL I in p_start_index..p_end_index
4575 UPDATE OE_ORDER_LINES_ALL oe1
4576 SET model_remnant_flag = 'Y'
4577 WHERE top_model_line_id is not NULL
4578 AND top_model_line_id = p_ship_line_rec.top_model_line_id(I)
4579 AND model_remnant_flag is NULL
4580 AND (EXISTS (SELECT NULL
4581 FROM oe_order_lines_all oe2
4582 WHERE oe2.top_model_line_id = oe1.top_model_line_id
4583 AND source_type_code = 'EXTERNAL')
4584 OR -- added for bug 4701487
4585 EXISTS (SELECT NULL
4586 FROM oe_order_lines_all oe3
4587 WHERE oe3.top_model_line_id = oe1.top_model_line_id
4588 AND cancelled_flag = 'N'
4589 AND schedule_ship_date is NULL));
4590
4591 IF SQL%FOUND THEN
4592 oe_debug_pub.ADD('updated lines model_remnant_flag '|| sql%rowcount,1);
4593 END IF;
4594
4595 IF l_debug_level > 0 THEN
4596 oe_debug_pub.ADD(' Start Index : '||p_start_index);
4597 oe_debug_pub.ADD(' End Index : '||p_end_index);
4598 END IF;
4599
4600 p_ship_line_rec.error_flag.extend(p_end_index - p_start_index + 1);
4601
4602 -- Added for bug 7149219
4603 l_last_calc_price := nvl(p_ship_line_rec.calculate_price_flag.LAST,p_end_index);
4604
4605 IF l_debug_level > 0 THEN
4606 oe_debug_pub.ADD(' Last Calc Price is : '||l_last_calc_price);
4607 END IF;
4608
4609 -- Commented for 7450821 start
4610 -- Added for bug 7149219
4611 /*IF p_end_index >= p_start_index and p_end_index > l_last_calc_price THEN
4612 p_ship_line_rec.calculate_price_flag.EXTEND(p_end_index - l_last_calc_price);
4613 IF l_debug_level > 0 THEN
4614 oe_debug_pub.ADD(' calculate_price_flag is extended');
4615 END IF;
4616 END IF;*/
4617 -- Commented for 7450821 end
4618
4619 FOR i in p_start_index..p_end_index
4620 LOOP
4621 p_ship_line_rec.error_flag(i) := 'N';
4622
4623 IF l_debug_level > 0 THEN
4624 oe_debug_pub.ADD('CompleteActivity '|| p_ship_line_rec.line_id(i), 1);
4625 END IF;
4626
4627 BEGIN
4628
4629
4630 -- Modified for 7450821 start
4631 -- Commented for bug 7149219
4632 -- p_ship_line_rec.calculate_price_flag.extend(I);
4633 p_ship_line_rec.calculate_price_flag.extend(1);
4634 -- Modified for 7450821 end
4635
4636 SELECT calculate_price_flag
4637 INTO p_ship_line_rec.calculate_price_flag(i)
4638 FROM oe_order_lines_all
4639 WHERE line_id = p_ship_line_rec.line_id(i);
4640
4641 IF p_ship_line_rec.calculate_price_flag(i) IN ('Y','P') OR
4642 (p_ship_line_rec.shipping_quantity2(I) IS NOT NULL
4643 AND p_ship_line_rec.shipping_quantity2(I) <> 0 ) -- bug 3598987,3659454
4644 THEN
4645
4646 --bug 3654553
4647 IF p_ship_line_rec.line_id(I) > 2147483647 THEN
4648 l_ind := mod(p_ship_line_rec.line_id(I),2147483647);
4649 ELSE
4650 l_ind := p_ship_line_rec.line_id(I);
4651 END IF;
4652 IF p_ship_line_rec.header_id(I) > 2147483647 THEN
4653 l_ind_hdr := mod(p_ship_line_rec.header_id(I),2147483647);
4654 ELSE
4655 l_ind_hdr := p_ship_line_rec.header_id(I);
4656 END IF;
4657 /* Caching lines that need to be priced */
4658 IF NOT l_change_line_tbl.EXISTS(l_ind) THEN
4659 l_change_line_tbl(l_ind).line_id := p_ship_line_rec.line_id(I);
4660 l_change_line_tbl(l_ind).header_id := p_ship_line_rec.header_id(I);
4661 END IF;
4662 IF NOT l_order_has_lines.EXISTS(l_ind_hdr) THEN
4663 l_order_has_lines(l_ind_hdr) := p_ship_line_rec.header_id(I);
4664 END IF;
4665 --bug 3654553
4666 END IF;
4667 -- workflow completion code moved for bug 4070931
4668
4669 EXCEPTION
4670 WHEN others THEN
4671 NULL;
4672
4673 END;
4674
4675 END LOOP;
4676
4677 --bug 3654553
4678 /* Call to price one order at a time */
4679 J := l_order_has_lines.first;
4680 WHILE J is NOT NULL
4681 LOOP
4682 K := l_change_line_tbl.FIRST;
4683 WHILE K is NOT NULL
4684 LOOP
4685 IF l_change_line_tbl(K).header_id = l_order_has_lines(J) THEN
4686 Oe_Line_Adj_Util.Register_Changed_Lines
4687 ( p_line_id => l_change_line_tbl(K).line_id
4688 ,p_header_id => l_change_line_tbl(K).header_id
4689 ,p_operation => OE_GLOBALS.G_OPR_UPDATE);
4690 l_change_line_tbl.delete(K);
4691 END IF;
4692 K := l_change_line_tbl.NEXT(K);
4693 END LOOP;
4694
4695 l_Price_Control_Rec.pricing_event := 'SHIP';
4696 l_Price_Control_Rec.calculate_flag := QP_PREQ_GRP.G_SEARCH_N_CALCULATE;
4697 l_Price_Control_Rec.Simulation_Flag := 'N';
4698
4699 OE_Order_Adj_Pvt.Price_Line
4700 ( x_return_status => l_return_status
4701 ,p_Header_id => l_order_has_lines(J)
4702 ,p_Request_Type_code => 'ONT'
4703 ,p_Control_rec => l_Price_Control_Rec
4704 ,p_write_to_db => TRUE
4705 ,p_request_rec => l_request_rec
4706 ,x_line_Tbl => l_Line_Tbl);
4707
4708 l_line_tbl := l_old_line_tbl; -- bug 3303011
4709
4710 IF l_debug_level > 0 THEN
4711 Oe_Debug_pub.Add('After price line for '||l_return_status, 1);
4712 END IF;
4713
4714 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4715 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4716 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4717 RAISE FND_API.G_EXC_ERROR;
4718 END IF;
4719
4720 J := l_order_has_lines.NEXT(J);
4721 END LOOP;
4722 --CC Reversal ER#16014135 Start
4723 l_cc_rev_reauth_code:= OE_VERIFY_PAYMENT_PUB.Get_CC_Rev_Reauth_Code(p_ship_line_rec.header_id(p_start_index));
4724 IF l_cc_rev_reauth_code is not null and l_cc_rev_reauth_code IN ('REV_REAUTH_AFTER_SHIP','REV_REAUTH','CC_HOLD') THEN
4725 BEGIN
4726 IF l_debug_level > 0 THEN
4727 OE_DEBUG_PUB.ADD('Calling OE_Verify_Payment_PUB.Authorize_MultiPayments',5);
4728 END IF;
4729
4730 OE_Header_UTIL.Query_Row
4731 (p_header_id => p_ship_line_rec.header_id(p_start_index)
4732 ,x_header_rec => l_header_rec
4733 );
4734
4735 OE_Verify_Payment_PUB.Authorize_MultiPayments
4736 ( p_header_rec => l_header_rec
4737 , p_calling_action => 'REVERSAL_REAUTH'
4738 , p_risk_eval_flag => null
4739 , p_msg_count => l_msg_count
4740 , p_msg_data => l_msg_data
4741 , p_result_out => l_result_out
4742 , p_return_status => l_return_status
4743 );
4744
4745 l_request_rec.return_status := l_return_status;
4746
4747 IF l_debug_level > 0 THEN
4748 oe_debug_pub.add('After call to Authorizat Payment return status : ' || l_return_status,5);
4749 END IF;
4750
4751 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4752 RAISE FND_API.G_EXC_ERROR;
4753 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4754 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4755 END IF;
4756 END;
4757 END IF;
4758 --CC Reversal ER#16014135 End
4759
4760 l_order_has_lines.delete;
4761 l_change_line_tbl.delete;
4762 -- bug 4070931 starts
4763 FOR i in p_start_index..p_end_index
4764 LOOP
4765 IF l_debug_level > 0 THEN
4766 oe_debug_pub.ADD('CompleteActivity '|| p_ship_line_rec.line_id(i), 1);
4767 END IF;
4768
4769 BEGIN
4770 WF_Engine.CompleteActivityInternalName
4771 ( itemtype => OE_GLOBALS.G_WFI_LIN
4772 ,itemkey => to_char(p_ship_line_rec.line_id(i))
4773 ,activity => 'SHIP_LINE'
4774 ,result => 'SHIP_CONFIRM'
4775 ,raise_engine_exception => TRUE ); --15870313
4776
4777 IF l_debug_level > 0 THEN
4778 oe_debug_pub.ADD('Returned from wf CompleteActivity',1);
4779 END IF;
4780
4781 EXCEPTION
4782 WHEN others THEN
4783 IF l_debug_level > 0 THEN
4784 oe_debug_pub.ADD('wf CompleteActivity error ' || sqlerrm,1);
4785 END IF;
4786
4787 p_ship_line_rec.error_flag(i) := 'Y';
4788 x_return_status := 'W';
4789
4790 IF l_debug_level > 0 THEN
4791 OE_DEBUG_PUB.Add ('X_Return_status is now WARNING',1);
4792 END IF;
4793
4794 Call_Notification_Framework
4795 ( p_ship_line_rec => p_ship_line_rec
4796 ,p_index => i
4797 ,p_caller => 'HANDLE_BULK_FAILURE');
4798
4799 END;
4800
4801 END LOOP; -- bug 4070931 ends
4802
4803 --bug 3654553
4804 IF l_debug_level > 0 THEN
4805 oe_debug_pub.ADD('gg1 '|| p_start_index ||'-' ||p_end_index,1);
4806 END IF;
4807 FORALL i in p_start_index..p_end_index
4808 UPDATE oe_order_lines_all
4809 SET shipped_quantity = ordered_quantity, -- INVCONV
4810 shipped_quantity2 = ordered_quantity2 -- INVCONV
4811 ,actual_shipment_date = p_ship_line_rec.actual_shipment_date(i)
4812 ,lock_control = lock_control + 1
4813 WHERE line_id in
4814 (SELECT line_id
4815 FROM oe_order_lines_all
4816 WHERE top_model_line_id =
4817 p_ship_line_rec.top_model_line_id(i))
4818 AND shippable_flag = 'N'
4819 AND p_ship_line_rec.error_flag(i) = 'N'
4820 AND shipped_quantity is NULL
4821 AND open_flag = 'Y'
4822 AND nvl(cancelled_flag, 'N') = 'N'
4823 AND source_type_code = 'INTERNAL'
4824 RETURNING line_id,
4825 ato_line_id,
4826 item_type_code,
4827 shipped_quantity,
4828 shipped_quantity2, -- INVCONV
4829 actual_shipment_date,
4830 model_remnant_flag,
4831 top_model_line_id
4832 BULK COLLECT
4833 INTO g_non_shippable_rec.line_id,
4834 g_non_shippable_rec.ato_line_id,
4835 g_non_shippable_rec.item_type_code,
4836 g_non_shippable_rec.shipped_quantity,
4837 g_non_shippable_rec.shipped_quantity2, -- INVCONV
4838 g_non_shippable_rec.actual_shipment_date,
4839 g_non_shippable_rec.model_remnant_flag,
4840 g_non_shippable_rec.top_model_line_id;
4841
4842
4843 IF SQL%FOUND THEN
4844 IF l_debug_level > 0 THEN
4845 oe_debug_pub.ADD('nonshippable lines updated '|| sql%rowcount,1);
4846 END IF;
4847
4848 FOR I in g_non_shippable_rec.line_id.FIRST..
4849 g_non_shippable_rec.line_id.LAST
4850 LOOP
4851 -- TSO with Equipment
4852 OE_CONFIG_TSO_PVT.Is_Part_Of_Container_Model
4853 (p_line_id => g_non_shippable_rec.line_id(I),
4854 x_top_container_model => l_top_container_model,
4855 x_part_of_container => l_part_of_container);
4856
4857 IF l_part_of_container = 'Y' THEN
4858 UPDATE oe_order_lines_all
4859 SET shipped_quantity = NULL
4860 ,actual_shipment_date = NULL
4861 ,lock_control = lock_control + 1
4862 WHERE line_id = g_non_shippable_rec.line_id(I);
4863 END IF;
4864 -- TSO with equipment ends
4865
4866 IF g_non_shippable_rec.line_id(I) =
4867 g_non_shippable_rec.ato_line_id(I) AND
4868 (g_non_shippable_rec.item_type_code(I) = 'MODEL' OR
4869 g_non_shippable_rec.item_type_code(I) = 'CLASS')
4870 THEN -- what about ato item??
4871
4872 IF l_debug_level > 0 THEN
4873 oe_debug_pub.ADD(I || ' complete wait for cto '
4874 || g_non_shippable_rec.ato_line_id(I), 1);
4875 END IF;
4876
4877 BEGIN
4878 WF_Engine.CompleteActivityInternalName
4879 (itemtype => OE_GLOBALS.G_WFI_LIN,
4880 itemkey => to_char(g_non_shippable_rec.ato_line_id(I)),
4881 activity => 'WAIT_FOR_CTO',
4882 result => OE_GLOBALS.G_WFR_COMPLETE,
4883 raise_engine_exception => TRUE ); --15870313
4884
4885 EXCEPTION
4886 WHEN OTHERS THEN
4887 oe_debug_pub.add('Workflow complete activity failed. Raising');
4888 RAISE ;
4889 END ;
4890
4891 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4892 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4893 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4894 RAISE FND_API.G_EXC_ERROR;
4895 END IF;
4896
4897 END IF;
4898
4899 IF g_non_shippable_rec.model_remnant_flag(I) = 'Y' THEN
4900
4901 UPDATE oe_order_lines_all
4902 SET shipped_quantity = null
4903 ,lock_control = lock_control + 1
4904 WHERE line_id = g_non_shippable_rec.line_id(I);
4905
4906
4907 IF l_last_top_model <> g_non_shippable_rec.top_model_line_id(I) THEN
4908
4909 IF l_debug_level > 0 THEN
4910 oe_debug_pub.ADD(I || ' calling fulfill remnant lines '
4911 || g_non_shippable_rec.top_model_line_id(I), 1);
4912 END IF;
4913
4914 fulfill_remnant_pto
4915 (p_top_model_line_id => g_non_shippable_rec.top_model_line_id(I));
4916 END IF;
4917
4918 l_last_top_model := g_non_shippable_rec.top_model_line_id(I);
4919 END IF;
4920 END LOOP;
4921
4922 g_non_shippable_rec.line_id.delete;
4923 g_non_shippable_rec.ato_line_id.delete;
4924 g_non_shippable_rec.item_type_code.delete;
4925 g_non_shippable_rec.shipped_quantity.delete;
4926 g_non_shippable_rec.shipped_quantity2.delete; -- INVCONV
4927 g_non_shippable_rec.actual_shipment_date.delete;
4928
4929 END IF; -- if non shippable lines
4930 -- 4052633
4931 FORALL I in p_start_index..p_end_index
4932 UPDATE OE_ORDER_LINES_ALL oe1
4933 SET ship_set_id = NULL
4934 WHERE ship_set_id is not NULL
4935 AND shipped_quantity is NULL
4936 AND ship_set_id = p_ship_line_rec.ship_set_id(I);
4937
4938 IF SQL%FOUND THEN
4939 oe_debug_pub.ADD('removed lines from shipset '|| sql%rowcount,1);
4940 END IF;
4941
4942 FORALL i in p_start_index..p_end_index
4943
4944 UPDATE oe_sets
4945 SET set_status = 'C'
4946 WHERE set_id = p_ship_line_rec.ship_set_id(i)
4947 AND SET_STATUS <> 'C'
4948 AND p_ship_line_rec.error_flag(i) = 'N';
4949
4950 IF SQL%FOUND THEN
4951 oe_debug_pub.ADD('gg4 sets closed '|| sql%rowcount,1);
4952 END IF;
4953
4954 -- { bug3309470: close arrival sets also
4955 FORALL i IN p_start_index..p_end_index
4956 UPDATE oe_sets
4957 SET set_status = 'C'
4958 WHERE set_id = p_ship_line_rec.arrival_set_id(i)
4959 AND SET_STATUS <> 'C'
4960 AND p_ship_line_rec.error_flag(i) = 'N';
4961
4962 IF SQL%FOUND THEN
4963 OE_DEBUG_PUB.Add('Arrival Sets Closed:'||sql%rowcount,1);
4964 END IF;
4965 -- bug3309470 ends }
4966
4967 IF x_return_status = 'W' THEN
4968 FORALL i in p_start_index..p_end_index
4969 UPDATE OE_ORDER_LINES_ALL
4970 SET shipping_quantity = null,
4971 shipping_quantity2 = null,
4972 shipped_quantity2 = null,
4973 shipped_quantity = null,
4974 shipping_quantity_uom = null,
4975 shipping_quantity_uom2 = null,
4976 actual_shipment_date = null,
4977 flow_status_code = p_ship_line_rec.flow_status_code(i),
4978 lock_control = lock_control - 1
4979 WHERE line_id = p_ship_line_rec.line_id(i)
4980 AND p_ship_line_rec.error_flag(i) = 'Y';
4981
4982 IF SQL%FOUND THEN
4983 oe_debug_pub.ADD('errored lines '|| sql%rowcount,1);
4984 END IF;
4985 END IF;
4986
4987 IF l_debug_level > 0 THEN
4988 oe_debug_pub.add('calling Process_Requests_And_Notify......', 1);
4989 END IF;
4990
4991 OE_Order_PVT.Process_Requests_And_Notify
4992 ( p_process_requests => FALSE
4993 ,p_notify => TRUE
4994 ,x_return_status => l_return_status
4995 ,p_line_tbl => l_line_tbl
4996 ,p_old_line_tbl => l_old_line_tbl );
4997
4998 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4999 x_return_status := l_return_status;
5000 IF l_debug_level > 0 THEN
5001 OE_DEBUG_PUB.Add ('Failure in Process_Requests_ANd_Notify',1);
5002 END IF;
5003 END IF;
5004
5005 --12552430 Start
5006 IF l_debug_level > 0 THEN
5007 oe_debug_pub.add('OEXPSHCB:calling Tax Delayed Request');
5008 END IF;
5009 OE_DELAYED_REQUESTS_PVT.Process_Request_for_Reqtype
5010 (p_request_type => OE_GLOBALS.G_TAX_LINE
5011 ,p_delete => FND_API.G_TRUE
5012 ,x_return_status => l_return_status
5013 );
5014 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5015 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5016 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
5017 RAISE FND_API.G_EXC_ERROR;
5018 END IF;
5019 --12552430 End
5020
5021 -- Reseting the msg context, bug 4516453
5022 OE_MSG_PUB.Reset_Msg_Context (p_entity_code => 'HEADER');
5023
5024 IF l_debug_level > 0 THEN
5025 oe_debug_pub.add('leaving Handle_Bulk_Mode_Per_Order '|| x_return_status, 1);
5026 END IF;
5027
5028 EXCEPTION
5029 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
5030 IF l_debug_level > 0 THEN
5031 OE_DEBUG_PUB.Add('Unable to lock the line',3);
5032 END IF;
5033 OE_Msg_Pub.Add_Text('Could not obtain Lock on Order Line/s');
5034 OE_MSG_PUB.Save_API_Messages(); --bug 4516453
5035 OE_MSG_PUB.Reset_Msg_Context (p_entity_code => 'HEADER');
5036 RAISE FND_API.G_EXC_ERROR;
5037
5038 WHEN others THEN
5039 IF l_debug_level > 0 THEN
5040 oe_debug_pub.add('error in Handle_Bulk_Mode_Per_Order ' || sqlerrm, 1);
5041 END IF;
5042 OE_MSG_PUB.Save_API_Messages(); --bug 4516453
5043 OE_MSG_PUB.Reset_Msg_Context (p_entity_code => 'HEADER');
5044 RAISE;
5045 END Handle_Bulk_Mode_Per_Order;
5046
5047 /*------------------------------------------------------------
5048 PROCEDURE Ship_Confirm_New
5049
5050 p_start_index and p_end_index are applicable only in bulk mode
5051 and only to p_ship_line_rec. The p_line_adj_rec will only
5052 contain records corresponding to that batch of tables between
5053 p_start_index and p_end_index in ship_line_rec.
5054
5055 handle_bulk and handle non bulk really do not need
5056 x_return_status, may be remove.
5057 -------------------------------------------------------------*/
5058 PROCEDURE Ship_Confirm_New
5059 ( p_ship_line_rec IN OUT NOCOPY Ship_Line_Rec_Type
5060 ,p_requested_line_rec IN OUT NOCOPY Ship_Line_Rec_Type
5061 ,p_line_adj_rec IN OUT NOCOPY Ship_Adj_Rec_Type
5062 ,p_bulk_mode IN VARCHAR2
5063 ,p_start_index IN NUMBER
5064 ,p_end_index IN NUMBER
5065 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
5066 ,x_msg_data OUT NOCOPY VARCHAR2
5067 ,x_return_status OUT NOCOPY VARCHAR2)
5068 IS
5069 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5070 l_org_id NUMBER;
5071 l_return_status VARCHAR2(1); --BUG 14125646
5072 BEGIN
5073 IF l_debug_level > 0 THEN
5074 oe_debug_pub.add('entering Ship_Confirm_New '
5075 || p_line_adj_rec.line_id.COUNT
5076 || p_ship_line_rec.line_id.COUNT , 1);
5077 END IF;
5078
5079 -- MOAC check for Org_id
5080 l_org_id := MO_GLOBAL.get_current_org_id;
5081 IF (l_org_id IS NULL OR l_org_id = FND_API.G_MISS_NUM) THEN
5082 FND_MESSAGE.set_name('FND','MO_ORG_REQUIRED');
5083 OE_MSG_PUB.Add;
5084 RAISE FND_API.G_EXC_ERROR;
5085 END IF;
5086
5087 SAVEPOINT om_ship_confirm;
5088
5089 x_return_status := FND_API.G_RET_STS_SUCCESS;
5090
5091 ---------- Create Freight Cost Records first --------
5092 IF p_line_adj_rec.line_id.COUNT > 0 THEN
5093
5094 IF l_debug_level > 0 THEN
5095 oe_debug_pub.add('inserting adjustments '
5096 || p_line_adj_rec.line_id.COUNT, 5);
5097 END IF;
5098
5099 --bug 4558089
5100 FORALL i IN p_line_adj_rec.line_id.FIRST..p_line_adj_rec.line_id.LAST
5101 DELETE FROM OE_PRICE_ADJUSTMENTS
5102 WHERE LINE_ID = p_line_adj_rec.line_id(i)
5103 AND CHARGE_TYPE_CODE IN ('FTEPRICE','FTECHARGE')
5104 AND p_line_adj_rec.charge_type_code(i) IN ('FTEPRICE','FTECHARGE')
5105 AND list_line_type_code = 'COST'
5106 AND p_line_adj_rec.list_line_type_code(i) = 'COST'
5107 AND ESTIMATED_FLAG = 'Y';
5108 --bug 4558089
5109
5110 FORALL i IN p_line_adj_rec.line_id.FIRST..p_line_adj_rec.line_id.LAST
5111 INSERT INTO OE_PRICE_ADJUSTMENTS
5112 ( price_adjustment_id
5113 ,cost_id
5114 ,automatic_flag
5115 ,list_line_type_code
5116 ,charge_type_code
5117 ,header_id
5118 ,line_id
5119 ,adjusted_amount
5120 ,arithmetic_operator
5121 ,last_update_date
5122 ,last_updated_by
5123 ,last_update_login
5124 ,creation_date
5125 ,created_by)
5126 VALUES
5127 ( OE_PRICE_ADJUSTMENTS_S.nextval
5128 ,p_line_adj_rec.cost_id(i)
5129 ,p_line_adj_rec.automatic_flag(i)
5130 ,p_line_adj_rec.list_line_type_code(i)
5131 ,p_line_adj_rec.charge_type_code(i)
5132 ,p_line_adj_rec.header_id(i)
5133 ,p_line_adj_rec.line_id(i)
5134 ,p_line_adj_rec.adjusted_amount(i)
5135 ,p_line_adj_rec.arithmetic_operator(i)
5136 ,SYSDATE
5137 ,FND_GLOBAL.USER_ID
5138 ,FND_GLOBAL.LOGIN_ID
5139 ,SYSDATE
5140 ,FND_GLOBAL.USER_ID)
5141 RETURNING price_adjustment_id
5142 BULK COLLECT
5143 INTO p_line_adj_rec.price_adjustment_id;
5144 END IF;
5145
5146
5147 --------- seperate bulk and non bulk mode -----------
5148
5149 IF p_bulk_mode = 'Y' THEN
5150
5151 IF l_debug_level > 0 THEN
5152 oe_debug_pub.add('calling handle_bulk_mode '
5153 || p_start_index ||'-'|| p_end_index, 1);
5154 END IF;
5155
5156 Handle_Bulk_Mode
5157 (p_ship_line_rec => p_ship_line_rec
5158 ,p_line_adj_rec => p_line_adj_rec
5159 ,p_start_index => p_start_index
5160 ,p_end_index => p_end_index
5161 ,x_return_status => x_return_status );
5162
5163 ELSE
5164
5165 IF l_debug_level > 0 THEN
5166 oe_debug_pub.add('calling handle_NONbulk_mode '
5167 || p_start_index ||'-'|| p_end_index, 5);
5168 END IF;
5169
5170 Handle_NonBulk_Mode
5171 (p_ship_line_rec => p_ship_line_rec
5172 ,p_requested_line_rec => p_requested_line_rec
5173 ,x_return_status => x_return_status );
5174
5175 END IF;
5176
5177 IF x_return_status = FND_API.G_RET_STS_ERROR
5178 THEN
5179 RAISE FND_API.G_EXC_ERROR;
5180 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
5181 x_return_status is NULL THEN
5182 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5183 END IF;
5184
5185 IF l_debug_level > 0 THEN
5186 oe_debug_pub.add('leaving Ship_Confirm_New', 2);
5187 END IF;
5188
5189 EXCEPTION
5190
5191 WHEN FND_API.G_EXC_ERROR THEN
5192 IF l_debug_level > 0 THEN
5193 oe_debug_pub.add('ship_confirm_new EXC ERROR: '||SQLERRM,1);
5194 END IF;
5195
5196 --Bug 14125646 clear the delayed requests
5197 OE_Delayed_Requests_PVT.Clear_Request
5198 ( x_return_status => l_return_status);
5199
5200 x_return_status := FND_API.G_RET_STS_ERROR;
5201
5202 OE_MSG_PUB.Count_And_Get
5203 (p_count => x_msg_count
5204 ,p_data => x_msg_data);
5205
5206 IF l_debug_level > 0 THEN
5207 oe_debug_pub.add('rollback to om_ship_confirm',1);
5208 END IF;
5209 ROLLBACK to om_ship_confirm;
5210
5211 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5212 IF l_debug_level > 0 THEN
5213 oe_debug_pub.add('ship_confirm_new UNEXPECTED ERROR : '||SQLERRM, 1);
5214 END IF;
5215
5216 --Bug 14125646 clear the delayed requests
5217 OE_Delayed_Requests_PVT.Clear_Request
5218 ( x_return_status => l_return_status);
5219
5220 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5221
5222 OE_MSG_PUB.Count_And_Get
5223 (p_count => x_msg_count
5224 ,p_data => x_msg_data);
5225
5226 IF l_debug_level > 0 THEN
5227 oe_debug_pub.add('rollback to om_ship_confirm : ',1);
5228 END IF;
5229 ROLLBACK to om_ship_confirm;
5230
5231 WHEN OTHERS THEN
5232 IF l_debug_level > 0 THEN
5233 oe_debug_pub.add('IN ship_confirm_new OTHERS ' || sqlerrm, 1);
5234 END IF;
5235
5236 --Bug 14125646 clear the delayed requests
5237 OE_Delayed_Requests_PVT.Clear_Request
5238 ( x_return_status => l_return_status);
5239
5240 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5241 THEN
5242 FND_MSG_PUB.Add_Exc_Msg
5243 ( G_PKG_NAME
5244 ,'ship_confirm_new');
5245 END IF;
5246
5247 OE_MSG_PUB.Count_And_Get
5248 (p_count => x_msg_count
5249 ,p_data => x_msg_data);
5250
5251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5252
5253 IF l_debug_level > 0 THEN
5254 oe_debug_pub.add('rollback to om_ship_confirm : ',1);
5255 END IF;
5256 ROLLBACK to om_ship_confirm;
5257 END Ship_Confirm_New;
5258
5259 /*-------------------------------------------------------------
5260 PROCEDURE Call_Notification_Framework
5261
5262 Call this procedure to handle the Notification_Framework call
5263 instead of scattering the code all over acorss different
5264 procedures.
5265
5266 not using p_line_rec and p_old_line_rec, will support if
5267 needed - it will take more local variable declarations and copy of
5268 tables.
5269
5270 if p_index is sent in, work on only one record.
5271 p_start_index and p_end_index are passed in case iof handle_bulk.
5272 if they are not passes, loop over the entire ship_line_rec.
5273
5274 Change Record:
5275 Bug 3730537 - ***
5276 --------------------------------------------------------------*/
5277 PROCEDURE Call_Notification_Framework
5278 ( p_ship_line_rec IN Ship_Line_Rec_Type
5279 ,p_index IN NUMBER := NULL
5280 ,p_start_index IN NUMBER := NULL
5281 ,p_end_index IN NUMBER := NULL
5282 ,p_caller IN VARCHAR2)
5283 IS
5284 --
5285 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5286 --
5287 l_line_rec OE_Order_Pub.Line_Rec_Type;
5288 l_old_line_rec OE_Order_Pub.Line_Rec_Type;
5289 l_start_time NUMBER;
5290 l_end_time NUMBER;
5291 I NUMBER;
5292 J NUMBER;
5293 l_end_index NUMBER;
5294 l_return_status VARCHAR2(1);
5295 BEGIN
5296
5297 IF l_debug_level > 0 THEN
5298 oe_debug_pub.add('entering Call_Notification_Framework ' || p_caller, 3);
5299 END IF;
5300
5301 l_return_status := FND_API.G_RET_STS_SUCCESS;
5302
5303 -- Commented for bug 8799963
5304 /*IF NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'),'N') = 'N' THEN
5305 IF l_debug_level > 0 THEN
5306 oe_debug_pub.add('returning from Call_Notification_Framework ', 3);
5307 END IF;
5308
5309 RETURN;
5310 END IF;*/
5311
5312 -- Bug 5640601 =>
5313 -- Selecting hsecs from v$times is changed to execute only when debug
5314 -- is enabled, as hsec is used for logging only when debug is enabled.
5315 IF l_debug_level > 0 Then
5316 SELECT hsecs INTO l_start_time from v$timer;
5317 end if;
5318
5319 IF p_index is NULL THEN
5320 IF p_start_index is NULL THEN -- non bulk
5321 I := p_ship_line_rec.line_id.FIRST;
5322 l_end_index := p_ship_line_rec.line_id.LAST + 1;
5323 ELSE
5324 I := p_start_index;
5325 l_end_index := p_end_index + 1;
5326 END If;
5327 ELSE -- one record only
5328 I := p_index;
5329 l_end_index := p_index + 1;
5330 END IF;
5331
5332
5333 IF l_debug_level > 0 THEN
5334 oe_debug_pub.add('p_start_index is ' || p_start_index
5335 ||' p_end_index is ' || p_end_index
5336 ||' l_end_index is ' || l_end_index, 3);
5337 END IF;
5338
5339 WHILE I <> l_end_index
5340 LOOP
5341
5342 IF l_debug_level > 0 THEN
5343 oe_debug_pub.add(I || 'line_id : ' || l_line_rec.line_id, 3);
5344 oe_debug_pub.add('ord qty : ' || p_ship_line_rec.ordered_quantity(I), 3);
5345 oe_debug_pub.add('ship qty: ' || l_line_rec.shipped_quantity, 3);
5346 END IF;
5347
5348 l_line_rec.line_id := p_ship_line_rec.line_id(I);
5349 l_line_rec.header_id := p_ship_line_rec.header_id(I);
5350
5351 l_line_rec.shipping_quantity := p_ship_line_rec.shipping_quantity(I);
5352 l_line_rec.shipping_quantity2 := p_ship_line_rec.shipping_quantity2(I);
5353 -- l_line_rec.shipped_quantity2 := p_ship_line_rec.ordered_quantity2(I); -- INVCONV
5354
5355 l_line_rec.shipping_quantity_uom
5356 := p_ship_line_rec.shipping_quantity_uom(I);
5357 l_line_rec.shipping_quantity_uom2
5358 := p_ship_line_rec.shipping_quantity_uom2(I);
5359 l_line_rec.actual_shipment_date
5360 := p_ship_line_rec.actual_shipment_date(I);
5361
5362 IF p_caller = 'HANDLE_BULK' THEN
5363 l_line_rec.flow_status_code := 'SHIPPED';
5364 l_line_rec.shipped_quantity := p_ship_line_rec.ordered_quantity(I);
5365 l_line_rec.shipped_quantity2 := p_ship_line_rec.ordered_quantity2(I); -- INVCONV
5366 IF l_debug_level > 0 THEN
5367 oe_debug_pub.add('bulk:inside setting shipped qty '
5368 || l_line_rec.shipped_quantity, 3);
5369
5370 oe_debug_pub.add('bulk:inside setting shipped qty2 ' -- INVCONV
5371 || l_line_rec.shipped_quantity2, 3);
5372 END IF;
5373
5374 ELSIF p_caller = 'HANDLE_BULK_FAILURE' THEN
5375 l_line_rec.shipping_quantity := null;
5376 l_line_rec.shipping_quantity2 := null;
5377 l_line_rec.shipped_quantity2 := null;
5378 l_line_rec.shipped_quantity := null;
5379 l_line_rec.shipping_quantity_uom := null;
5380 l_line_rec.shipping_quantity_uom2 := null;
5381 l_line_rec.actual_shipment_date := null;
5382 l_line_rec.flow_status_code := p_ship_line_rec.flow_status_code(i);
5383
5384 IF l_debug_level > 0 THEN
5385 oe_debug_pub.add('bulk failure:inside setting shipped qty '
5386 || l_line_rec.shipped_quantity, 3);
5387 END IF;
5388 ELSIF p_caller = 'HANDLE_NON_BULK' OR
5389 p_caller = 'SHIP_CONFIRM_SPLIT_LINES' THEN
5390 l_line_rec.shipped_quantity := p_ship_line_rec.shipped_quantity(I);
5391 l_line_rec.shipped_quantity2 := p_ship_line_rec.shipped_quantity2(I); -- INVCONV
5392 IF l_debug_level > 0 THEN
5393 oe_debug_pub.add('non bulk:inside setting shipped qty '
5394 || l_line_rec.shipped_quantity, 3);
5395 oe_debug_pub.add('non bulk:inside setting shipped qty2 ' -- INVCONV
5396 || l_line_rec.shipped_quantity2, 3);
5397
5398 END IF;
5399 END IF;
5400
5401 IF l_debug_level > 0 THEN
5402 oe_debug_pub.add
5403 ('update GLOBAL FLOW_STATUS is: '||l_line_rec.flow_status_code,3);
5404 END IF;
5405
5406 OE_ORDER_UTIL.Update_Global_Picture
5407 (p_Upd_New_Rec_If_Exists => False,
5408 --p_header_id => l_line_rec.header_id,
5409 p_old_line_rec => l_old_line_rec,
5410 p_line_rec => l_line_rec,
5411 p_line_id => l_line_rec.line_id,
5412 x_index => J,
5413 x_return_status => l_return_status);
5414
5415 IF l_debug_level > 0 THEN
5416 oe_debug_pub.add(J || ' UPDATE_GLOBAL ret sts: ' || l_retuRN_STATUS);
5417 END IF;
5418
5419 IF l_return_status = FND_API.G_RET_STS_ERROR
5420 THEN
5421 RAISE FND_API.G_EXC_ERROR;
5422 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
5423 l_return_status is NULL THEN
5424 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5425 END IF;
5426
5427 IF J IS NOT NULL THEN
5428
5429 OE_ORDER_UTIL.g_line_tbl(J)
5430 := OE_ORDER_UTIL.g_old_line_tbl(J);
5431
5432 OE_ORDER_UTIL.g_line_tbl(J).line_id := l_line_rec.line_id;
5433 OE_ORDER_UTIL.g_line_tbl(J).header_id:= l_line_rec.header_id;
5434 OE_ORDER_UTIL.g_line_tbl(J).last_update_date := SYSDATE;
5435 OE_ORDER_UTIL.g_line_tbl(J).last_updated_by := FND_GLOBAL.USER_ID;
5436 OE_ORDER_UTIL.g_line_tbl(J).last_update_login:= FND_GLOBAL.LOGIN_ID;
5437
5438
5439 OE_ORDER_UTIL.g_line_tbl(J).shipping_quantity
5440 := l_line_rec.shipping_quantity;
5441 OE_ORDER_UTIL.g_line_tbl(J).shipping_quantity2
5442 := l_line_rec.shipping_quantity2;
5443 OE_ORDER_UTIL.g_line_tbl(J).shipped_quantity2
5444 := l_line_rec.shipped_quantity2;
5445 OE_ORDER_UTIL.g_line_tbl(J).shipped_quantity
5446 := l_line_rec.shipped_quantity;
5447 OE_ORDER_UTIL.g_line_tbl(J).shipping_quantity_uom
5448 := l_line_rec.shipping_quantity_uom;
5449 OE_ORDER_UTIL.g_line_tbl(J).shipping_quantity_uom2
5450 := l_line_rec.shipping_quantity_uom2;
5451 OE_ORDER_UTIL.g_line_tbl(J).actual_shipment_date
5452 := l_line_rec.actual_shipment_date;
5453 OE_ORDER_UTIL.g_line_tbl(J).flow_status_code
5454 := l_line_rec.flow_status_code;
5455 OE_ORDER_UTIL.g_line_tbl(J).operation
5456 := OE_GLOBALS.G_OPR_UPDATE; -- Bug 8442372
5457
5458 IF l_debug_level > 0 THEN
5459 oe_debug_pub.add
5460 ('AFTER UPDATE GLOBAL FLOW_STATUS_CODE IS: '
5461 || OE_ORDER_UTIL.G_LINE_TBL( J ).FLOW_STATUS_CODE ,1);
5462 END IF;
5463
5464 END IF; -- if index is not null
5465
5466 I := I + 1; -- should not be an gaps in the ship_line_rec
5467
5468 END LOOP; -- loop over ship line rec
5469
5470 -- Bug 5640601 =>
5471 -- Selecting hsecs from v$times is changed to execute only when debug
5472 -- is enabled, as hsec is used for logging only when debug is enabled.
5473 IF l_debug_level > 0 Then
5474 SELECT hsecs INTO l_end_time from v$timer;
5475 end if;
5476
5477 FND_FILE.PUT_LINE
5478 (FND_FILE.LOG,'Time spent in notification framework is (sec) '
5479 ||((l_end_time-l_start_time)/100));
5480
5481 EXCEPTION
5482 WHEN others THEN
5483 IF l_debug_level > 0 THEN
5484 oe_debug_pub.add('error in Call_Notification_Framework' || sqlerrm, 1);
5485 END IF;
5486 RAISE;
5487 END Call_Notification_Framework;
5488
5489 --ER number 7360612
5490 /*#
5491 * This API ship confirms a line with zero shipped quantity and completes the
5492 * SHIP_LINE (Ship) workflow activity, provided that tolerances across the line
5493 * set for the input line are met. Parameter x_return_status reports API success
5494 * or failure, and x_result_out narrows down the cause of failure.
5495 * @param p_line_id Input value of line id to be shipped with zero quantity
5496 * @param x_result_out Returns reason for failure (W = Workflow not at Ship:Notified,T = Tolerances not met, D = Delivery details
5497 already shipped)
5498 * @param x_return_status Return status (S = Success, E = Error, U = Unexpected Error)
5499 * @param x_msg_count Returns number of mesages generated while executing the API
5500 * @param x_msg_data Returns text of messages generated
5501 * @rep:scope public
5502 * @rep:lifecycle active
5503 * @rep:displayname Ship Confirm with Zero Quantity
5504 */
5505 PROCEDURE Ship_Zero
5506 ( p_line_id IN NUMBER,
5507 x_result_out OUT NOCOPY VARCHAR2,
5508 x_return_status IN OUT NOCOPY VARCHAR2,
5509 x_msg_count OUT NOCOPY NUMBER,
5510 x_msg_data OUT NOCOPY VARCHAR2
5511 )
5512 IS
5513
5514 l_update_lines_tbl OE_ORDER_PUB.Request_Tbl_Type;
5515 l_line_rec OE_ORDER_PUB.line_rec_type;
5516 l_tolerance_check VARCHAR2(256);
5517 l_count NUMBER DEFAULT 0;
5518 l_return_status VARCHAR2(256);
5519
5520 BEGIN
5521 x_result_out := 'S';
5522 x_return_status := FND_API.G_RET_STS_SUCCESS;
5523
5524 --query row
5525 OE_Line_Util.Query_Row
5526 (
5527 p_line_id => p_line_id,
5528 x_line_rec => l_line_rec
5529 );
5530 --check line status
5531 --IF (l_line_rec.flow_status_code <> 'AWAITING_SHIPPING')
5532 --THEN
5533 -- x_result_out := 'F';
5534 -- RAISE FND_API.G_EXC_ERROR;
5535 --END IF;
5536 l_count := 0;
5537 select Count (1)
5538 INTO l_count
5539 from wf_item_activity_statuses wias, wf_process_activities wpa
5540 where wias.process_activity = wpa.instance_id
5541 and to_number(wias.item_key) = p_line_id
5542 and wias.item_type = 'OEOL'
5543 AND wpa.activity_item_type = wias.item_type
5544 and wias.activity_status = 'NOTIFIED'
5545 and wpa.activity_name = 'SHIP_LINE';
5546
5547 IF (l_count < 1)
5548 THEN
5549 x_result_out := 'W';
5550 RAISE FND_API.G_EXC_ERROR;
5551 END IF;
5552
5553 OE_Shipping_Integration_PVT.Check_Shipment_Line
5554 (
5555 p_line_rec => l_line_rec,
5556 x_result_out => l_tolerance_check
5557 );
5558
5559 IF ( l_tolerance_check NOT IN ( OE_GLOBALS.G_SHIPPED_WITHIN_TOL_BELOW,
5560 OE_GLOBALS.G_SHIPPED_WITHIN_TOL_ABOVE,
5561 OE_GLOBALS.G_FULLY_SHIPPED,
5562 OE_GLOBALS.G_SHIPPED_BEYOND_TOLERANCE)
5563 )
5564 THEN
5565 x_result_out := 'T';
5566 RAISE FND_API.G_EXC_ERROR;
5567 END IF;
5568 l_count := 0;
5569 SELECT Count(1)
5570 INTO l_count
5571 FROM wsh_delivery_details
5572 WHERE source_line_id = p_line_id
5573 AND released_status = 'C';
5574
5575 IF (l_count > 0 )
5576 THEN
5577 x_result_out := 'D';
5578 RAISE FND_API.G_EXC_ERROR;
5579 END IF;
5580
5581 --prepare call to shipping API
5582 l_update_lines_tbl(1).entity_id := p_line_id;
5583 l_update_lines_tbl(1).param1 := FND_API.G_FALSE;
5584 l_update_lines_tbl(1).param2 := FND_API.G_FALSE;
5585 l_update_lines_tbl(1).param5 := FND_API.G_TRUE;
5586 l_update_lines_tbl(1).request_type := OE_GLOBALS.G_OPR_UPDATE;
5587
5588 --Step 4 : Call shipping integration
5589 OE_Shipping_Integration_PVT.Update_Shipping_From_OE
5590 (
5591 p_update_lines_tbl => l_update_lines_tbl,
5592 x_return_status => l_return_status
5593 );
5594
5595 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
5596 THEN
5597 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5598 END IF;
5599
5600
5601 OE_DELAYED_REQUESTS_PVT.Process_Request_for_Reqtype
5602 (
5603 p_request_type =>OE_GLOBALS.G_COMPLETE_ACTIVITY
5604 ,p_delete => FND_API.G_TRUE
5605 ,x_return_status => l_return_status
5606 );
5607 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
5608 THEN
5609 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5610 END IF;
5611 EXCEPTION
5612 WHEN FND_API.G_EXC_ERROR THEN
5613 x_return_status := FND_API.G_RET_STS_ERROR;
5614 OE_MSG_PUB.Count_And_Get
5615 ( p_count => x_msg_count
5616 , p_data => x_msg_data
5617 );
5618
5619 WHEN OTHERS THEN
5620 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5621 x_result_out := 'U';
5622 OE_MSG_PUB.Count_And_Get
5623 ( p_count => x_msg_count
5624 , p_data => x_msg_data
5625 );
5626 END ship_zero;
5627
5628 END OE_Ship_Confirmation_Pub;