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