[Home] [Help]
PACKAGE BODY: APPS.OE_SHIPPING_INTEGRATION_PVT
Source
1 PACKAGE BODY OE_Shipping_Integration_PVT AS
2 /* $Header: OEXVSHPB.pls 120.24.12020000.5 2013/04/09 08:06:51 spothula ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Shipping_Integration_PVT';
7
8 -- Start of Comments
9 -- API name OE_Shipping_Integration_PVT
10 -- Type Private
11 -- Version Current version = 1.0
12 -- Initial version = 1.0
13
14 /*---------------------------------------------------------------
15 Forward Declarations
16 ----------------------------------------------------------------*/
17
18 PROCEDURE Ship_Confirm_Split_Lines
19 ( p_line_rec IN OE_Order_Pub.Line_Rec_Type
20 ,p_shipment_status IN VARCHAR2);
21
22
23 PROCEDURE Call_Notification_Framework
24 ( p_line_rec IN OE_Order_Pub.Line_Rec_Type
25 ,p_caller IN VARCHAR2
26 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
27
28 PROCEDURE Print_Time(p_msg IN VARCHAR2);
29
30
31 /*---------------------------------------------------------------
32 PROCEDURE Validate_Release_Status
33 ----------------------------------------------------------------*/
34 PROCEDURE Validate_Release_Status
35 (
36 p_application_id IN NUMBER
37 , p_entity_short_name IN VARCHAR2
38 , p_validation_entity_short_name IN VARCHAR2
39 , p_validation_tmplt_short_name IN VARCHAR2
40 , p_record_set_short_name IN VARCHAR2
41 , p_scope IN VARCHAR2
42 , x_result_out OUT NOCOPY NUMBER
43
44 )
45 IS
46 l_Release_Status VARCHAR2(1);
47 --
48 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
49 --
50 BEGIN
51 IF g_debug_call > 0 THEN
52 G_DEBUG_MSG := G_DEBUG_MSG || '1,';
53 END IF;
54
55 SELECT PICK_STATUS
56 INTO l_release_status
57 FROM WSH_DELIVERY_LINE_STATUS_V
58 WHERE SOURCE_CODE = 'OE'
59 AND SOURCE_LINE_ID = OE_LINE_SECURITY.g_record.line_id
60 AND PICK_STATUS = 'S';
61 IF l_release_status = 'S' THEN
62 x_result_out := 1;
63 ELSE
64 x_result_out := 0;
65 END IF;
66 IF g_debug_call > 0 THEN
67 G_DEBUG_MSG := G_DEBUG_MSG || '2';
68 END IF;
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN
71 IF l_debug_level > 0 THEN
72 oe_debug_pub.add( 'NO DATA FOUND IN VALIDATE RELEASE STATUS' , 1 ) ;
73 END IF;
74 x_result_out := 0;
75 WHEN TOO_MANY_ROWS THEN
76 IF l_debug_level > 0 THEN
77 oe_debug_pub.add( 'TOO MANY ROWS IN VALIDATE RELEASE STATUS' , 1 ) ;
78 END IF;
79 x_result_out := 1;
80 WHEN OTHERS THEN
81 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
82 OE_MSG_PUB.Add_Exc_Msg
83 ( G_PKG_NAME
84 , 'Validate_Release_Status'
85 );
86 END IF;
87 IF l_debug_level > 0 THEN
88 oe_debug_pub.add( 'ERROR MESSAGE IN VALIDATE RELEASE STATUS : '||SUBSTR ( SQLERRM , 1 , 100 ) , 1 ) ;
89 END IF;
90 END Validate_Release_Status;
91
92
93 /*---------------------------------------------------------------
94 PROCEDURE Validate_Pick
95 ----------------------------------------------------------------*/
96 PROCEDURE Validate_Pick
97 (
98 p_application_id IN NUMBER
99 , p_entity_short_name IN VARCHAR2
100 , p_validation_entity_short_name IN VARCHAR2
101 , p_validation_tmplt_short_name IN VARCHAR2
102 , p_record_set_short_name IN VARCHAR2
103 , p_scope IN VARCHAR2
104 , x_result_out OUT NOCOPY NUMBER
105
106 )
107 IS
108 l_pick_status VARCHAR2(1);
109 --
110 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
111 --
112 BEGIN
113
114 SELECT PICK_STATUS
115 INTO l_pick_status
116 FROM WSH_DELIVERY_LINE_STATUS_V
117 WHERE SOURCE_CODE = 'OE'
118 AND SOURCE_LINE_ID = OE_LINE_SECURITY.g_record.line_id
119 AND PICK_STATUS = 'Y';
120
121 IF l_pick_status = 'Y' THEN
122 x_result_out := 1;
123 ELSE
124 x_result_out := 0;
125 END IF;
126
127
128 EXCEPTION
129 WHEN NO_DATA_FOUND THEN
130 x_result_out := 0;
131 WHEN TOO_MANY_ROWS THEN
132 x_result_out := 1;
133 WHEN OTHERS THEN
134
135 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
136 THEN
137 OE_MSG_PUB.Add_Exc_Msg
138 ( G_PKG_NAME
139 , 'Validate_Pick'
140 );
141 END IF;
142
143 IF l_debug_level > 0 THEN
144 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 100 ) , 1 ) ;
145 END IF;
146 IF g_debug_call > 0 THEN
147 G_DEBUG_MSG := G_DEBUG_MSG || 'E1';
148 END IF;
149 END Validate_Pick;
150
151
152 /*---------------------------------------------------------------
153 PROCEDURE Call_Process_Order
154 ----------------------------------------------------------------*/
155 PROCEDURE Call_Process_Order
156 (p_line_tbl IN OE_Order_PUB.Line_Tbl_Type
157 ,p_control_rec IN OE_GLOBALS.Control_Rec_Type DEFAULT OE_GLOBALS.G_MISS_CONTROL_REC
158 ,p_process_requests IN BOOLEAN := FALSE
159 ,x_return_status OUT NOCOPY VARCHAR2
160
161 )
162 IS
163 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
164 l_control_rec OE_GLOBALS.Control_Rec_Type;
165 l_header_out_rec OE_ORDER_PUB.Header_Rec_Type;
166 l_line_out_tbl OE_ORDER_PUB.Line_Tbl_Type;
167 l_line_adj_out_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;
168 l_header_adj_out_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
169 l_header_scredit_out_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
170 l_line_scredit_out_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
171 l_action_request_out_tbl OE_Order_PUB.Request_Tbl_Type;
172 l_header_price_att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
173 l_header_adj_assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
174 l_header_adj_att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
175 l_line_price_att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
176 l_line_adj_assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
177 l_line_adj_att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
178 l_lot_serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
179 l_return_status VARCHAR2(1);
180 l_msg_count NUMBER;
181 l_msg_data VARCHAR2(2000);
182 --serla begin
183 l_header_payment_out_tbl OE_Order_PUB.Header_Payment_Tbl_Type;
184 l_line_payment_out_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
185 --serla end
186 --
187 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
188 --
189 BEGIN
190
191 l_line_tbl := p_line_tbl;
192 l_control_rec := p_control_rec;
193 IF g_debug_call > 0 THEN
194 G_DEBUG_MSG := G_DEBUG_MSG || '3,';
195 END IF;
196
197 IF l_control_rec.check_security = FALSE THEN
198 IF l_debug_level > 0 THEN
199 oe_debug_pub.add( 'CHECK SECURITY = FALSE' , 1 ) ;
200 END IF;
201 ELSE
202 IF l_debug_level > 0 THEN
203 oe_debug_pub.add( 'CHECK SECURITY = TRUE' , 1 ) ;
204 END IF;
205 END IF;
206
207 l_control_rec.process := p_process_requests;
208
209 l_control_rec.controlled_operation := TRUE;
210 IF g_debug_call > 0 THEN
211 G_DEBUG_MSG := G_DEBUG_MSG || '4,';
212 END IF;
213
214 OE_Order_PVT.Process_Order
215 (
216 p_api_version_number => 1.0
217 , x_return_status => l_return_status
218 , x_msg_count => l_msg_count
219 , x_msg_data => l_msg_data
220 , p_x_line_tbl => l_line_tbl
221 , p_control_rec => l_control_rec
222 , p_x_header_rec => l_header_out_rec
223 , p_x_header_adj_tbl => l_header_adj_out_tbl
224 , p_x_header_scredit_tbl => l_header_scredit_out_tbl
225 --serla begin
226 , p_x_header_payment_tbl => l_header_payment_out_tbl
227 --serla end
228 , p_x_line_adj_tbl => l_line_adj_out_tbl
229 , p_x_line_scredit_tbl => l_line_scredit_out_tbl
230 --serla begin
231 , p_x_line_payment_tbl => l_line_payment_out_tbl
232 --serla end
233 , p_x_action_request_tbl => l_action_request_out_tbl
234 , p_x_lot_serial_tbl => l_lot_serial_tbl
235 , p_x_header_price_att_tbl => l_header_price_att_tbl
236 , p_x_header_adj_att_tbl => l_header_adj_att_tbl
237 , p_x_header_adj_assoc_tbl => l_header_adj_assoc_tbl
238 , p_x_line_price_att_tbl => l_line_price_att_tbl
239 , p_x_line_adj_att_tbl => l_line_adj_att_tbl
240 , p_x_line_adj_assoc_tbl => l_line_adj_assoc_tbl
241 );
242
243 x_return_status := l_return_status;
244 IF g_debug_call > 0 THEN
245 G_DEBUG_MSG := G_DEBUG_MSG || '4';
246 END IF;
247
248 EXCEPTION
249 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
251
252 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
253 THEN
254 OE_MSG_PUB.Add_Exc_Msg
255 ( G_PKG_NAME,
256 'Call_Process_Order'
257 );
258 END IF;
259 IF g_debug_call > 0 THEN
260 G_DEBUG_MSG := G_DEBUG_MSG || 'E4';
261 END IF;
262
263 WHEN FND_API.G_EXC_ERROR THEN
264 x_return_status := FND_API.G_RET_STS_ERROR;
265 WHEN OTHERS THEN
266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
267
268 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
269 THEN
270 OE_MSG_PUB.Add_Exc_Msg
271 ( G_PKG_NAME,
272 'Call_Process_Order'
273 );
274 END IF;
275 IF l_debug_level > 0 THEN
276 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 100 ) , 1 ) ;
277 END IF;
278 IF g_debug_call > 0 THEN
279 G_DEBUG_MSG := G_DEBUG_MSG || 'E5';
280 END IF;
281 END Call_Process_Order;
282
283 /*----------------------------------------------------------------
284 PROCEDURE Update_Shipping_From_OE
285
286 parameters in request rec=>
287 request_unique_key1: line_rec.operation
288 param1: true/false, whether to update shipping from OE
289 param2: true/false, explosion_date_changed
290 param3:
291 param4:
292 param5: ordered_quantity_changed
293
294 meaning of action flag,
295 I : delete
296 S : create
297 U : update
298 -----------------------------------------------------------------*/
299
300 PROCEDURE Update_Shipping_From_OE
301 ( p_update_lines_tbl IN OE_ORDER_PUB.Request_Tbl_Type
302 ,x_return_status OUT NOCOPY VARCHAR2)
303 IS
304 l_shp_index NUMBER :=0;
305 l_line_index NUMBER :=0;
306 l_changed_attributes WSH_INTERFACE.ChangedAttributeTabType;
307 l_line_rec OE_Order_Pub.line_rec_type;
308 l_line_id NUMBER;
309 l_return_status VARCHAR2(1);
310 l_line_at_shipping VARCHAR2(1) := FND_API.G_FALSE;
311 l_result_out VARCHAR2(30);
312 l_source_code VARCHAR2(2) := 'OE';
313 l_msg_count NUMBER;
314 l_msg_data VARCHAR2(2000);
315 l_x_result_out VARCHAR2(30);
316 l_result_code VARCHAR2(30);
317 l_error_msg VARCHAR2(240);
318 l_msg_index NUMBER;
319 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
320 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
321 l_line_number VARCHAR2(150);
322 l_index NUMBER;
323 l_shipping_interfaced_flag VARCHAR2(1);
324 -- odaboval : Begin of OPM Changes
325 --l_ic_item_mst_rec GMI_Reservation_Util.ic_item_mst_rec; OPM INVCONV 4742691
326 -- l_opm_lot_id NUMBER; -- INVCONV
327 -- l_opm_uom VARCHAR2(4);-- INVCONV
328 -- l_opm_uom2 VARCHAR2(4);-- INVCONV
329 -- l_apps_uom VARCHAR2(3);-- INVCONV
330 -- l_apps_uom2 VARCHAR2(3);-- INVCONV
331 l_item_rec OE_ORDER_CACHE.item_rec_type; -- INVCONV
332
333 l_force_ui VARCHAR2(1) := 'N';
334 l_header_id NUMBER;
335 l_item_type_code VARCHAR2(240);
336 -- odaboval : End of OPM Changes
337 --
338 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
339 -- 4437814 start changes
340 l_activity_status VARCHAR2(8);
341 l_activity_result VARCHAR2(30);
342 l_activity_id NUMBER;
343 l_shipline_notified VARCHAR2(1):='Y';
344 -- 4437814 end changes
345 --
346 --l_assign_flag boolean := FALSE;
347
348 --code fix for 6391881 start changes
349 l_wsh_no_data_found BOOLEAN := FALSE;
350 l_ok_to_cancel BOOLEAN := TRUE;
351 --code fix for 6391881 end changes
352
353 -- code fix for 5590106
354 i number;
355 l_cxl_dd_count NUMBER ; -- cancelled delivery detail count
356
357
358
359 BEGIN
360 IF l_debug_level > 0 THEN -- Added debug for bug 13435459
361 OE_DEBUG_PUB.add('Entering Update_Shipping_From_OE() at ',0.5);
362 END IF;
363 IF OE_GLOBALS.G_ASO_INSTALLED IS NULL THEN
364 OE_GLOBALS.G_ASO_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(697);
365 END IF;
366
367 IF l_debug_level > 0 THEN
368 oe_debug_pub.add('ENTERING oexvshpb UPDATE_SHIPPING_FROM_OE',1);
369 oe_debug_pub.add('Tbl Count:'||p_update_lines_tbl.COUNT,4);
370 END IF;
371 IF g_debug_call > 0 THEN
372 G_DEBUG_MSG := G_DEBUG_MSG || '6,';
373 END IF;
374
375 FOR l_line_index IN p_update_lines_tbl.First .. p_update_lines_tbl.Last
376 LOOP
377
378 l_line_id := p_update_lines_tbl(l_line_index).entity_id;
379 l_line_at_shipping := FND_API.G_FALSE;
380
381 OE_Line_Util.Query_Row --bug 4516453, moved query_row here to set msg context
382 ( p_line_id => l_line_id,
383 x_line_rec => l_line_rec);
384 --- setting the msg context
385 OE_MSG_PUB.set_msg_context(
386 p_entity_code => 'LINE'
387 ,p_entity_id => l_line_rec.line_id
388 ,p_header_id => l_line_rec.header_id
389 ,p_line_id => l_line_rec.line_id
390 ,p_order_source_id => l_line_rec.order_source_id
391 ,p_orig_sys_document_ref => l_line_rec.orig_sys_document_ref
392 ,p_orig_sys_document_line_ref => l_line_rec.orig_sys_line_ref
393 ,p_orig_sys_shipment_ref => l_line_rec.orig_sys_shipment_ref
394 ,p_change_sequence => l_line_rec.change_sequence
395 ,p_source_document_type_id => l_line_rec.source_document_type_id
396 ,p_source_document_id => l_line_rec.source_document_id
397 ,p_source_document_line_id => l_line_rec.source_document_line_id
398 );
399
400 -------------- handle delete operation -----------------
401
402 IF p_update_lines_tbl(l_line_index).request_unique_key1
403 = OE_GLOBALS.G_OPR_DELETE THEN
404 -- fix for 3779333
405 -- select item type code from oe_orderlines_table. For config lines
406 -- pass action code as 'U' to shipping. for other item types, pass
407 -- action code as 'D'
408
409 SELECT item_type_code
410 INTO l_item_type_code
411 FROM oe_order_lines
412 WHERE line_id = l_line_id;
413
414 --{ bug3831490 starts
415 IF l_item_type_code <> 'CONFIG' THEN
416
417 l_shp_index := l_shp_index + 1;
418 l_changed_attributes(l_shp_index).action_flag := 'D';
419 l_changed_attributes(l_shp_index).source_line_id
420 := p_update_lines_tbl(l_line_index).entity_id;
421 l_changed_attributes(l_shp_index).ordered_quantity := 0;
422
423 IF l_debug_level > 0 THEN
424 oe_debug_pub.add('line is deleted '
425 || L_CHANGED_ATTRIBUTES(L_SHP_INDEX).SOURCE_LINE_ID , 3 ) ;
426 oe_debug_pub.add('action flag = '
427 ||L_CHANGED_ATTRIBUTES ( L_SHP_INDEX ) .ACTION_FLAG , 3 ) ;
428 oe_debug_pub.add('item type code :' || l_item_type_code, 3);
429 oe_debug_pub.add('ordered quantity '
430 || L_CHANGED_ATTRIBUTES(L_SHP_INDEX).ORDERED_QUANTITY, 3 ) ;
431 END IF;
432
433 GOTO END_UPDATE_SHIPPING_LOOP;
434 ELSE
435 --this is if it is DELETE + ITEM_TYPE_CODE==CONFIG
436 l_shp_index := l_shp_index + 1;
437 l_changed_attributes(l_shp_index).source_line_id
438 := p_update_lines_tbl(l_line_index).entity_id;
439 IF l_debug_level > 0 THEN
440 OE_DEBUG_PUB.Add ('This is a config item cancel/delete case',1);
441 END IF;
442 END IF;
443
444 END IF; -- if operation is delete
445
446 IF l_debug_level > 0 THEN
447 oe_debug_pub.add('processing line_ID ' ||L_LINE_ID, 3);
448 END IF;
449
450 ---------- Check If explosion date has changed and -----------
451 -- the current activity is shipping for the line.
452 -- this code is not required and explosion date never changes.
453
454
455 IF p_update_lines_tbl(l_line_index).param2 = FND_API.G_TRUE THEN
456 IF g_debug_call > 0 THEN
457 G_DEBUG_MSG := G_DEBUG_MSG || '7,';
458 END IF;
459
460 IF OE_Shipping_Integration_PUB.Is_Activity_Shipping
461 (1.0, l_line_id) = FND_API.G_TRUE THEN
462
463 IF l_debug_level > 0 THEN
464 oe_debug_pub.add
465 ('explosion date changed, line is at ship '|| l_line_id,3);
466 END IF;
467
468 l_line_at_shipping := FND_API.G_TRUE;
469 ELSE
470 IF g_debug_call > 0 THEN
471 G_DEBUG_MSG := G_DEBUG_MSG || '8,';
472 END IF;
473
474 IF l_debug_level > 0 THEN
475 oe_debug_pub.add
476 ('explosion date changed, but LINE not at shipping '
477 || TO_CHAR ( L_LINE_ID ) , 3 ) ;
478 END IF;
479
480 GOTO END_UPDATE_SHIPPING_LOOP;
481 END IF;
482
483 END IF; -- if exlosion date changes
484
485
486 ----------- Assign the values to table for Shipping ---------
487
488 l_shp_index := l_shp_index + 1;
489 l_changed_attributes(l_shp_index).released_status
490 := FND_API.G_MISS_CHAR;
491
492 -- Line is at SHIP_LINE call shipping with action code 'I'
493 -- If the line is not yet interfaced
494
495 IF p_update_lines_tbl(l_line_index).param4 = FND_API.G_TRUE THEN
496
497 IF l_debug_level > 0 THEN
498 oe_debug_pub.add('line at shipping, update released flag '
499 || TO_CHAR ( L_LINE_ID ) , 3 ) ;
500 END IF;
501
502
503 SELECT shipping_interfaced_flag, header_id
504 INTO l_shipping_interfaced_flag, l_header_id
505 FROM oe_order_lines
506 WHERE line_id = l_line_id;
507 IF g_debug_call > 0 THEN
508 G_DEBUG_MSG := G_DEBUG_MSG || '10,';
509 END IF;
510
511 IF nvl(l_shipping_interfaced_flag,'N') = 'N' THEN
512
513 IF l_debug_level > 0 THEN
514 oe_debug_pub.add('NOT INTERFACED TO SHIPPING ' , 3 ) ;
515 END IF;
516
517 l_changed_attributes(l_shp_index).source_line_id
518 := l_line_id;
519 l_changed_attributes(l_shp_index).action_flag := 'I';
520 l_changed_attributes(l_shp_index).released_status := 'R';
521
522
523 GOTO END_UPDATE_SHIPPING_LOOP;
524 ELSE
525 l_changed_attributes(l_shp_index).released_status := 'R';
526 END IF; -- if shipping interfaed or not
527
528 IF g_debug_call > 0 THEN
529 G_DEBUG_MSG := G_DEBUG_MSG || '11,';
530 END IF;
531 END IF; -- if param4 is true
532
533 /* the code has been moved up for bug 4516453 to set msg context
534 OE_Line_Util.Query_Row
535 ( p_line_id => l_line_id,
536 x_line_rec => l_line_rec); */
537
538
539 -- Check If only explosion date has changed and the current activity
540 -- is shipping for the line.
541
542 IF p_update_lines_tbl(l_line_index).param2 = FND_API.G_TRUE
543 AND l_line_at_shipping = FND_API.G_TRUE THEN
544
545 IF l_debug_level > 0 THEN
546 oe_debug_pub.add('explosion date changed, activity shipping '
547 || TO_CHAR ( L_LINE_ID ) , 3 ) ;
548 END IF;
549 IF g_debug_call > 0 THEN
550 G_DEBUG_MSG := G_DEBUG_MSG || '12,';
551 END IF;
552
553 -- Check if the line is shippable.
554 -- If the line is shippable update Shipping
555 -- Else complete the shipment activity and go to next record.
556
557 IF nvl(l_line_rec.shippable_flag,'N') = 'Y' THEN
558 l_changed_attributes(l_shp_index).released_status := 'R';
559 ELSE
560
561 IF l_debug_level > 0 THEN
562 oe_debug_pub.add
563 ('explosion date has changed for a non shippable line'
564 || TO_CHAR ( L_LINE_ID ) , 3 ) ;
565 END IF;
566
567 l_result_code := 'NON_SHIPPABLE';
568
569 -- Log a delayed request for Complete Activity, 1739574
570
571 IF l_debug_level > 0 THEN
572 oe_debug_pub.add( 'log delayed request complet activity for '
573 || L_LINE_REC.LINE_ID , 3 ) ;
574 END IF;
575
576 OE_Delayed_Requests_Pvt.Log_Request
577 ( p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
578 p_entity_id => l_line_rec.line_id,
579 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
580 p_requesting_entity_id => l_line_rec.line_id,
581 p_request_type => OE_GLOBALS.G_COMPLETE_ACTIVITY,
582 p_param1 => OE_GLOBALS.G_WFI_LIN,
583 p_param2 => l_line_rec.line_id,
584 p_param3 => 'SHIP_LINE',
585 p_param4 => l_result_code,
586 x_return_status => l_return_status);
587 IF g_debug_call > 0 THEN
588 G_DEBUG_MSG := G_DEBUG_MSG || '13,';
589 END IF;
590 GOTO END_UPDATE_SHIPPING_LOOP;
591 END IF; -- if shippable
592 END IF; -- param2 and lien at shipping
593
594
595 ------------- assign other parameters to changed record --------
596
597 l_changed_attributes(l_shp_index).source_header_id
598 := l_line_rec.header_id;
599 IF g_debug_call > 0 THEN
600 G_DEBUG_MSG := G_DEBUG_MSG || '14,';
601 END IF;
602
603 IF l_line_rec.split_from_line_id IS NULL THEN
604 l_changed_attributes(l_shp_index).original_source_line_id
605 := l_line_rec.line_id;
606 ELSE
607 l_changed_attributes(l_shp_index).original_source_line_id
608 := l_line_rec.split_from_line_id;
609 END IF;
610
611 l_changed_attributes(l_shp_index).source_line_id
612 := l_line_rec.line_id;
613
614 IF l_debug_level > 0 THEN
615 oe_debug_pub.add('source line id : '
616 || L_CHANGED_ATTRIBUTES ( L_SHP_INDEX ) .SOURCE_LINE_ID , 3 ) ;
617 END IF;
618
619 l_changed_attributes(l_shp_index).sold_to_org_id
620 := l_line_rec.sold_to_org_id;
621 l_changed_attributes(l_shp_index).ship_from_org_id
622 := l_line_rec.ship_from_org_id;
623 l_changed_attributes(l_shp_index).ship_to_org_id
624 := l_line_rec.ship_to_org_id;
625 l_changed_attributes(l_shp_index).ship_to_contact_id
626 := l_line_rec.ship_to_contact_id;
627 l_changed_attributes(l_shp_index).deliver_to_org_id
628 := l_line_rec.deliver_to_org_id;
629 l_changed_attributes(l_shp_index).deliver_to_contact_id
630 := l_line_rec.deliver_to_contact_id;
631 l_changed_attributes(l_shp_index).intmed_ship_to_org_id
632 := l_line_rec.intermed_ship_to_org_id;
633 l_changed_attributes(l_shp_index).intmed_ship_to_contact_id
634 := l_line_rec.intermed_ship_to_contact_id;
635 l_changed_attributes(l_shp_index).ship_tolerance_above
636 := l_line_rec.ship_tolerance_above;
637 l_changed_attributes(l_shp_index).ship_tolerance_below
638 := l_line_rec.ship_tolerance_below;
639 IF g_debug_call > 0 THEN
640 G_DEBUG_MSG := G_DEBUG_MSG || '15,';
641 END IF;
642
643 -- Changes for Bug-2579571
644 l_changed_attributes(l_shp_index).source_line_set_id
645 := l_line_rec.line_set_id;
646
647 -- CMS Date Changes
648
649 l_changed_attributes(l_shp_index).schedule_arrival_date
650 := l_line_rec.schedule_arrival_date;
651 l_changed_attributes(l_shp_index).promise_date
652 := l_line_rec.promise_date;
653 l_changed_attributes(l_shp_index).earliest_acceptable_date
654 := l_line_rec.earliest_acceptable_date;
655 l_changed_attributes(l_shp_index).latest_acceptable_date
656 := l_line_rec.latest_acceptable_date;
657 l_changed_attributes(l_shp_index).earliest_ship_date
658 := l_line_rec.earliest_ship_date;
659
660 IF g_debug_call > 0 THEN
661 G_DEBUG_MSG := G_DEBUG_MSG || '16,';
662 END IF;
663 IF p_update_lines_tbl(l_line_index).request_unique_key1
664 = OE_GLOBALS.G_OPR_CREATE THEN
665 l_changed_attributes(l_shp_index).action_flag := 'S';
666 ELSE
667 l_changed_attributes(l_shp_index).action_flag := 'U';
668 END IF;
669
670 IF l_debug_level > 0 THEN
671 oe_debug_pub.add('action flag = '
672 ||L_CHANGED_ATTRIBUTES ( L_SHP_INDEX ) .ACTION_FLAG , 3 ) ;
673 END IF;
674
675 IF l_line_rec.shipped_quantity IS NOT NULL AND
676 l_line_rec.shipped_quantity <> FND_API.G_MISS_NUM THEN
677 l_changed_attributes(l_shp_index).shipped_flag := 'Y';
678 ELSE
679 l_changed_attributes(l_shp_index).shipped_flag := 'N';
680 END IF;
681
682 IF l_debug_level > 0 THEN
683 oe_debug_pub.add('shipped flag '
684 || L_CHANGED_ATTRIBUTES ( L_SHP_INDEX ) .SHIPPED_FLAG , 3 ) ;
685 END IF;
686 IF g_debug_call > 0 THEN
687 G_DEBUG_MSG := G_DEBUG_MSG || '17,';
688 END IF;
689
690
691 -------------- line is shipped with qty = 0 ---------------------
692 -- If a line is being deleted then the ordered quantity to be notified
693 -- to shipping will be 0.
694
695 IF l_debug_level > 0 THEN
696 oe_debug_pub.add('under shipment tolerance : '
697 ||L_LINE_REC.SHIP_TOLERANCE_BELOW , 3 ) ;
698 oe_debug_pub.add(p_update_lines_tbl(l_line_index).param5
699 || '-- param5---', 1);
700 END IF;
701
702 IF p_update_lines_tbl(l_line_index).param5 = FND_API.G_TRUE AND
703 nvl(l_line_rec.shipped_quantity,0) = 0 AND
704 nvl(l_line_rec.ordered_quantity,0) <> 0 AND -- bug 2129287
705 nvl(l_line_rec.ship_tolerance_below,0) < 100 THEN --1829490
706 IF g_debug_call > 0 THEN
707 G_DEBUG_MSG := G_DEBUG_MSG || '18,';
708 END IF;
709
710 IF l_debug_level > 0 THEN
711 oe_debug_pub.add('ordered quantity has been reduced' , 3 ) ;
712 END IF;
713
714 -- ordered quantity changed check for the shipment status.
715
716 Check_Shipment_Line( p_line_rec => l_line_rec,
717 x_result_out => l_x_result_out);
718 IF g_debug_call > 0 THEN
719 G_DEBUG_MSG := G_DEBUG_MSG || '19,';
720 END IF;
721 -- 4437814 start changes
722 IF (l_line_rec.item_type_code = 'CONFIG'
723 OR
724 (l_line_rec.ato_line_id = l_line_rec.line_id AND
725 l_line_rec.item_type_code IN ('STANDARD','OPTION','INCLUDED'))) THEN --9775352
726 --Get the activity result
727 OE_LINE_FULLFILL.Get_Activity_Result
728 (p_item_type => OE_GLOBALS.G_WFI_LIN
729 ,p_item_key => l_line_rec.line_id
730 ,p_activity_name => 'SHIP_LINE'
731 ,x_return_status => l_return_status
732 ,x_activity_result => l_activity_result
733 ,x_activity_status_code => l_activity_status
734 ,x_activity_id => l_activity_id );
735
736 IF NVL(l_activity_status,'NON_NOTIFIED') <> 'NOTIFIED' THEN
737 l_shipline_notified := 'N';
738 END IF;
739 END IF;
740 -- 4437814 end changes
741 IF l_debug_level > 0 THEN
742 oe_debug_pub.add('shipline notified flag:' || l_shipline_notified) ;
743 END IF;
744 IF l_x_result_out = OE_GLOBALS.G_SHIPPED_WITHIN_TOL_BELOW OR
745 l_x_result_out = OE_GLOBALS.G_SHIPPED_WITHIN_TOL_ABOVE OR
746 l_x_result_out = OE_GLOBALS.G_FULLY_SHIPPED OR
747 l_x_result_out = OE_GLOBALS.G_SHIPPED_BEYOND_TOLERANCE THEN
748 IF l_shipline_notified = 'Y'THEN --4437814
749 IF l_debug_level > 0 THEN
750 oe_debug_pub.add
751 ('line shipped within tolerance, complete ship_line',3);
752 END IF;
753 IF g_debug_call > 0 THEN
754 G_DEBUG_MSG := G_DEBUG_MSG || '20,';
755 END IF;
756
757 -- Start 1829490
758
759 /* AG: Change to check if the ASO is installed and then only
760 call Process_Requests_And_Notify */
761
762 UPDATE oe_order_lines
763 SET flow_status_code = 'SHIPPED',
764 shipped_quantity = 0,
765 last_update_date = SYSDATE,
766 last_updated_by = FND_GLOBAL.USER_ID,
767 last_update_login = FND_GLOBAL.LOGIN_ID
768 WHERE line_id = l_line_rec.line_id;
769 IF g_debug_call > 0 THEN
770 G_DEBUG_MSG := G_DEBUG_MSG || '21,';
771 END IF;
772
773 l_old_line_tbl(1) := l_line_rec;
774 l_line_tbl(1) := l_line_rec;
775 l_line_tbl(1).flow_status_code := 'SHIPPED';
776 l_line_tbl(1).shipped_quantity := 0;
777
778 -- added for notification framework, pack H onwards
779
780 IF ((OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508') OR
781 (NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'),'N') = 'Y'))
782 THEN
783 IF g_debug_call > 0 THEN
784 G_DEBUG_MSG := G_DEBUG_MSG || '22,';
785 END IF;
786 OE_ORDER_UTIL.Update_Global_Picture
787 (p_Upd_New_Rec_If_Exists =>False,
788 p_header_id => l_line_rec.header_id,
789 p_old_line_rec => l_old_line_tbl(1),
790 p_line_rec => l_line_tbl(1),
791 p_line_id => l_line_rec.line_id,
792 x_index => l_index,
793 x_return_status => l_return_status);
794
795 IF l_debug_level > 0 THEN
796 oe_debug_pub.add( 'after update global picture, status: '
797 || L_RETURN_STATUS ) ;
798 oe_debug_pub.add( 'global picture index: '
799 || L_INDEX , 1 ) ;
800 END IF;
801
802 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
803 RAISE FND_API.G_EXC_ERROR;
804 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
805 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
806 END IF;
807
808 IF l_index IS NOT NULL THEN
809
810 --update Global Picture directly
811
812
813 OE_ORDER_UTIL.g_old_line_tbl(l_index)
814 := l_old_line_tbl(1);
815 OE_ORDER_UTIL.g_line_tbl(l_index)
816 := OE_ORDER_UTIL.g_old_line_tbl(l_index);
817 OE_ORDER_UTIL.g_line_tbl(l_index).line_id
818 := l_line_tbl(1).line_id;
819 OE_ORDER_UTIL.g_line_tbl(l_index).header_id
820 := l_line_tbl(1).header_id;
821 OE_ORDER_UTIL.g_line_tbl(l_index).flow_status_code
822 := l_line_tbl(1).flow_status_code;
823 OE_ORDER_UTIL.g_line_tbl(l_index).shipped_quantity
824 := l_line_tbl(1).shipped_quantity;
825 OE_ORDER_UTIL.g_line_tbl(l_index).last_update_date
826 := SYSDATE;
827 OE_ORDER_UTIL.g_line_tbl(l_index).last_updated_by
828 := FND_GLOBAL.USER_ID;
829 OE_ORDER_UTIL.g_line_tbl(l_index).last_update_login
830 := FND_GLOBAL.LOGIN_ID;
831
832 IF l_debug_level > 0 THEN
833 oe_debug_pub.add
834 ('global flow status code after update: '
835 || OE_ORDER_UTIL.G_LINE_TBL( L_INDEX ).FLOW_STATUS_CODE ,1);
836 END IF;
837
838 END IF; -- if index is not null
839
840 ELSE --pre-pack H
841
842 IF OE_GLOBALS.G_ASO_INSTALLED = 'Y' THEN
843
844
845 OE_Order_PVT.Process_Requests_And_Notify
846 ( p_process_requests => FALSE
847 , p_notify => TRUE
848 , p_process_ack => FALSE
849 , x_return_status => l_return_status
850 , p_line_tbl => l_line_tbl
851 , p_old_line_tbl => l_old_line_tbl);
852
853 IF l_debug_level > 0 THEN
854 oe_debug_pub.add
855 ('return status Process_Requests_And_Notify() '||L_RETURN_STATUS , 3 ) ;
856 END IF;
857
858 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
859 RAISE FND_API.G_EXC_ERROR;
860 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
861 THEN
862 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
863 END IF;
864 END IF; -- aso_installed
865 END IF; -- code_release_level
866
867 l_line_rec.flow_status_code := 'SHIPPED';
868 l_line_rec.shipped_quantity := 0;
869 -- END 1829490
870
871 ------------------------ continue -------------------------
872
873 l_changed_attributes(l_shp_index).ordered_quantity := 0;
874
875 -- odaboval : Begin of OPM Changes
876 l_changed_attributes(l_shp_index).ordered_quantity2 := NULL;
877
878 /* IF (INV_GMI_RSV_BRANCH.Process_Branch -- INVCONV NOT NEEDED
879 ( p_organization_id => l_line_rec.ship_from_org_id) ) THEN
880 IF g_debug_call > 0 THEN
881 G_DEBUG_MSG := G_DEBUG_MSG || '28,';
882 END IF;
883 GMI_RESERVATION_UTIL.Get_OPM_Item_From_Apps
884 ( p_organization_id => l_line_rec.ship_from_org_id,
885 p_inventory_item_id => l_line_rec.inventory_item_id,
886 x_ic_item_mst_rec => l_ic_item_mst_rec,
887 x_return_status => l_return_status,
888 x_msg_count => l_msg_count,
889 x_msg_data => l_msg_data);
890
891 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
892 IF l_debug_level > 0 THEN
893 oe_debug_pub.add
894 (' ( MLP_DBG ) CALL TO GET_OPM_ITEM_FROM APPS FAILED' , 1 ) ;
895 END IF;
896 RAISE FND_API.G_EXC_ERROR;
897 END IF; */
898
899 IF oe_line_util.dual_uom_control -- INVCONV Process_Characteristics
900 (l_line_rec.inventory_item_id,l_line_rec.ship_from_org_id,l_item_rec) THEN
901 IF l_item_rec.tracking_quantity_ind = 'PS' THEN -- INVCONV
902 -- IF (l_ic_item_mst_rec.dualum_ind > 0) THEN -- ONVCONV
903 l_changed_attributes(l_shp_index).ordered_quantity2 := 0;
904 END IF;
905 END IF;
906 ---------------------- odaboval : End of OPM Changes
907
908 IF l_line_rec.item_type_code = 'CONFIG' THEN
909
910 IF l_debug_level > 0 THEN
911 oe_debug_pub.add('config line, complete ato model '
912 || l_line_rec.ato_line_id, 2);
913 END IF;
914
915 Handle_Config_Parent
916 ( p_ato_line_id => l_line_rec.ato_line_id);
917 END IF;
918
919 l_result_code := 'SHIP_CONFIRM';
920
921 -- Log a delayed request for Complete Activity, 1739574
922 IF l_debug_level > 0 THEN
923 oe_debug_pub.add( 'LOGGING DELAYED REQUEST FOR '
924 || L_LINE_REC.LINE_ID , 3 ) ;
925 END IF;
926
927 OE_Delayed_Requests_Pvt.Log_Request
928 ( p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
929 p_entity_id => l_line_rec.line_id,
930 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
931 p_requesting_entity_id => l_line_rec.line_id,
932 p_request_type => OE_GLOBALS.G_COMPLETE_ACTIVITY,
933 p_param1 => OE_GLOBALS.G_WFI_LIN,
934 p_param2 => l_line_rec.line_id,
935 p_param3 => 'SHIP_LINE',
936 p_param4 => l_result_code,
937 x_return_status => l_return_status);
938 IF g_debug_call > 0 THEN
939 G_DEBUG_MSG := G_DEBUG_MSG || 'Shp-4-31';
940 END IF;
941 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
942 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
943 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
944 RAISE FND_API.G_EXC_ERROR;
945 END IF;
946
947 IF l_line_rec.arrival_set_id IS NOT NULL AND
948 l_line_rec.arrival_set_id <> FND_API.G_MISS_NUM THEN
949 -- Update the set status to closed.
950 UPDATE OE_SETS
951 SET SET_STATUS = 'C'
952 WHERE SET_ID = l_line_rec.arrival_set_id;
953
954 IF l_debug_level > 0 THEN
955 oe_debug_pub.add('SET IS CLOSED : '
956 ||TO_CHAR ( L_LINE_REC.ARRIVAL_SET_ID ) , 3 ) ;
957 END IF;
958 END IF;
959 END IF; --4437814
960 ELSE -- regular qtu update
961
962 l_changed_attributes(l_shp_index).ordered_quantity
963 := l_line_rec.ordered_quantity;
964
965 -- odaboval : Begin of OPM Changes
966 IF g_debug_call > 0 THEN
967 G_DEBUG_MSG := G_DEBUG_MSG || '33,';
968 END IF;
969
970 IF l_debug_level > 0 THEN
971 oe_debug_pub.add( ' ( MLP_DBG ) ORDERED_QUANTITY IS '
972 || L_CHANGED_ATTRIBUTES ( L_SHP_INDEX ) .ORDERED_QUANTITY , 1 ) ;
973 END IF;
974
975 l_changed_attributes(l_shp_index).ordered_quantity2
976 := l_line_rec.ordered_quantity2;
977
978 IF l_debug_level > 0 THEN
979 oe_debug_pub.add( ' ( MLP_DBG ).ORDERED_QUANTITY2 IS '
980 || L_CHANGED_ATTRIBUTES ( L_SHP_INDEX ) .ORDERED_QUANTITY2 , 1 ) ;
981 END IF;
982
983 -- odaboval : End of OPM Changes
984 END IF; --if shipped within tolerance below
985 IF g_debug_call > 0 THEN
986 G_DEBUG_MSG := G_DEBUG_MSG || '34,';
987 END IF;
988 ELSE
989
990 l_changed_attributes(l_shp_index).ordered_quantity
991 := l_line_rec.ordered_quantity;
992 -- odaboval : Begin of OPM Changes
993 --{ bug3831490 contd
994 IF l_line_rec.item_type_code = 'CONFIG' AND
995 p_update_lines_tbl(l_line_index).request_unique_key1 =
996 OE_GLOBALS.G_OPR_DELETE THEN
997
998 l_changed_attributes(l_shp_index).ordered_quantity := 0;
999 IF l_debug_level > 0 THEN
1000 OE_DEBUG_PUB.Add('This is Config cancel/delete case!',1);
1001 OE_DEBUG_PUB.Add('Overrriden Ord qty to 0',1);
1002 END IF;
1003
1004 END IF;
1005 --bug3831490 ends }
1006
1007
1008 IF l_debug_level > 0 THEN
1009 oe_debug_pub.add( ' ( MLP_DBG ).ORDERED_QUANTITY IS '
1010 || L_CHANGED_ATTRIBUTES ( L_SHP_INDEX ) .ORDERED_QUANTITY , 1 ) ;
1011 END IF;
1012
1013 l_changed_attributes(l_shp_index).ordered_quantity2
1014 := l_line_rec.ordered_quantity2;
1015
1016 IF l_debug_level > 0 THEN
1017 oe_debug_pub.add(' ( MLP_DBG ).ORDERED_QUANTITY2 IS '
1018 || L_CHANGED_ATTRIBUTES ( L_SHP_INDEX ) .ORDERED_QUANTITY2 , 1 ) ;
1019 END IF;
1020 -- odaboval : End of OPM Changes
1021 END IF; -- p_update_lines_tbl(l_line_index).param5 etc
1022
1023
1024 IF l_debug_level > 0 THEN
1025 oe_debug_pub.add( 'ORDERED QUANTITY '
1026 || L_CHANGED_ATTRIBUTES ( L_SHP_INDEX ) .ORDERED_QUANTITY, 3 ) ;
1027 END IF;
1028
1029 l_changed_attributes(l_shp_index).order_quantity_uom
1030 := l_line_rec.order_quantity_uom;
1031
1032 -- odaboval : Begin of OPM Changes
1033 l_changed_attributes(l_shp_index).ordered_quantity_uom2
1034 := l_line_rec.ordered_quantity_uom2;
1035 l_changed_attributes(l_shp_index).preferred_grade
1036 := l_line_rec.preferred_grade;
1037 -- odaboval : End of OPM Changes
1038
1039 -- #1818531
1040 -- l_changed_attributes(l_shp_index).revision
1041 -- := l_line_rec.item_revision;
1042 IF g_debug_call > 0 THEN
1043 G_DEBUG_MSG := G_DEBUG_MSG || '36,';
1044 END IF;
1045
1046 l_changed_attributes(l_shp_index).date_requested
1047 := l_line_rec.request_date;
1048 l_changed_attributes(l_shp_index).date_scheduled
1049 := l_line_rec.schedule_ship_date;
1050 l_changed_attributes(l_shp_index).shipping_method_code
1051 := l_line_rec.shipping_method_code;
1052 l_changed_attributes(l_shp_index).freight_carrier_code
1053 := l_line_rec.freight_carrier_code;
1054 l_changed_attributes(l_shp_index).freight_terms_code
1055 := l_line_rec.freight_terms_code;
1056
1057 IF l_debug_level > 0 THEN
1058 oe_debug_pub.add( 'FREIGHT TERMS CODE '
1059 || L_CHANGED_ATTRIBUTES ( L_SHP_INDEX ) .FREIGHT_TERMS_CODE , 3 ) ;
1060 END IF;
1061
1062 l_changed_attributes(l_shp_index).shipment_priority_code
1063 := l_line_rec.shipment_priority_code;
1064 l_changed_attributes(l_shp_index).fob_code
1065 := l_line_rec.fob_point_code;
1066 --l_changed_attributes(l_shp_index).customer_item_id
1067 -- := l_line_rec.customer_item_id;
1068
1069 -- Above condition is uncommented for the bug 2939731
1070
1071 IF (l_line_rec.item_identifier_type = 'CUST') THEN
1072 -- Added for the bug 3762407
1073 l_changed_attributes(l_shp_index).customer_item_id := l_line_rec.ordered_item_id;
1074
1075 END IF;
1076
1077 l_changed_attributes(l_shp_index).dep_plan_required_flag
1078 := l_line_rec.dep_plan_required_flag;
1079 l_changed_attributes(l_shp_index).customer_dock_code
1080 := l_line_rec.customer_dock_code;
1081 l_changed_attributes(l_shp_index).customer_prod_seq
1082 := l_line_rec.cust_production_seq_num;
1083
1084 -- Alcoa enhancement three fields added
1085
1086 l_changed_attributes(l_shp_index).customer_job
1087 := l_line_rec.customer_job;
1088 l_changed_attributes(l_shp_index).cust_model_serial_number
1089 := l_line_rec.cust_model_serial_number;
1090 l_changed_attributes(l_shp_index).customer_production_line
1091 := l_line_rec.customer_production_line;
1092
1093
1094 l_changed_attributes(l_shp_index).top_model_line_id
1095 := l_line_rec.top_model_line_id;
1096 l_changed_attributes(l_shp_index).ship_set_id
1097 := l_line_rec.ship_set_id;
1098 l_changed_attributes(l_shp_index).ato_line_id
1099 := l_line_rec.ato_line_id;
1100 l_changed_attributes(l_shp_index).arrival_set_id
1101 := l_line_rec.arrival_set_id;
1102 l_changed_attributes(l_shp_index).ship_model_complete_flag
1103 := l_line_rec.ship_model_complete_flag;
1104 l_changed_attributes(l_shp_index).cust_po_number
1105 := l_line_rec.cust_po_number;
1106 l_changed_attributes(l_shp_index).shipping_instructions
1107 := l_line_rec.shipping_instructions;
1108 l_changed_attributes(l_shp_index).packing_instructions
1109 := l_line_rec.packing_instructions;
1110 l_changed_attributes(l_shp_index).subinventory
1111 := l_line_rec.subinventory;
1112
1113 IF l_line_rec.top_model_line_id IS NOT NULL AND
1114 nvl(l_line_rec.model_remnant_flag, 'N') = 'N' THEN
1115 l_force_ui := 'Y';
1116 END IF;
1117
1118 IF l_debug_level > 0 THEN
1119 oe_debug_pub.add( 'SUB-INVENTORY '
1120 || L_CHANGED_ATTRIBUTES ( L_SHP_INDEX ) .SUBINVENTORY , 3 ) ;
1121 END IF;
1122
1123
1124
1125 l_changed_attributes(l_shp_index).project_id
1126 := l_line_rec.project_id;
1127 IF g_debug_call > 0 THEN
1128 G_DEBUG_MSG := G_DEBUG_MSG || '39,';
1129 END IF;
1130 IF l_debug_level > 0 THEN
1131 oe_debug_pub.add( 'PROJECT_ID:'
1132 || L_CHANGED_ATTRIBUTES ( L_SHP_INDEX ) .PROJECT_ID , 3 ) ;
1133 END IF;
1134
1135 l_changed_attributes(l_shp_index).task_id
1136 := l_line_rec.task_id;
1137
1138
1139 IF l_debug_level > 0 THEN
1140 oe_debug_pub.add( 'TASK_ID:'
1141 || L_CHANGED_ATTRIBUTES ( L_SHP_INDEX ) .TASK_ID , 3 ) ;
1142 END IF;
1143
1144 IF g_debug_call > 0 THEN
1145 G_DEBUG_MSG := G_DEBUG_MSG || '40,';
1146 END IF;
1147
1148 -- Changes for passing the line number. Enhancement 1495329
1149
1150 IF l_line_rec.service_number is not null THEN
1151 IF l_line_rec.option_number is not null THEN
1152 IF l_line_rec.component_number is not null THEN
1153
1154 l_line_number
1155 := l_line_rec.line_number||'.'
1156 ||l_line_rec.shipment_number||'.'
1157 ||l_line_rec.option_number||'.'||
1158 l_line_rec.component_number||'.'||
1159 l_line_rec.service_number;
1160 ELSE
1161 l_line_number := l_line_rec.line_number||'.'
1162 ||l_line_rec.shipment_number||'.'||
1163 l_line_rec.option_number||'..'
1164 ||l_line_rec.service_number;
1165 END IF;
1166 IF g_debug_call > 0 THEN
1167 G_DEBUG_MSG := G_DEBUG_MSG || '41,';
1168 END IF;
1169
1170 --- if a option is not attached
1171 ELSE
1172 IF l_line_rec.component_number is not null THEN
1173 l_line_number := l_line_rec.line_number||'.'
1174 ||l_line_rec.shipment_number||'..'||
1175 l_line_rec.component_number||'.'||l_line_rec.service_number;
1176 ELSE
1177 l_line_number := l_line_rec.line_number||'.'
1178 ||l_line_rec.shipment_number||
1179 '...'||l_line_rec.service_number;
1180 END IF;
1181
1182 END IF; /* if option number is not null */
1183
1184 -- if the service number is null
1185 ELSE
1186 IF l_line_rec.option_number is not null THEN
1187 IF l_line_rec.component_number is not null THEN
1188 l_line_number := l_line_rec.line_number||'.'||l_line_rec.shipment_number||'.'||
1189 l_line_rec.option_number||'.'||l_line_rec.component_number;
1190 ELSE
1191 l_line_number := l_line_rec.line_number||'.'||l_line_rec.shipment_number||'.'||
1192 l_line_rec.option_number;
1193 END IF;
1194
1195 --- if a option is not attached
1196 ELSE
1197 IF l_line_rec.component_number is not null THEN
1198 l_line_number := l_line_rec.line_number||'.'||l_line_rec.shipment_number||'..'||
1199 l_line_rec.component_number;
1200 ELSE
1201 l_line_number := l_line_rec.line_number||'.'||l_line_rec.shipment_number;
1202 END IF;
1203
1204 END IF; /* if option number is not null */
1205
1206 END IF; /* if service number is not null */
1207
1208
1209
1210 l_changed_attributes(l_shp_index).line_number := l_line_number;
1211
1212 /* Added below code for ER 6110708 */
1213 l_changed_attributes(l_shp_index).inventory_item_id := l_line_rec.inventory_item_id;
1214 -- Commented for bug 7665831
1215 -- This assignment is already done above as a part of bugfix 3762407
1216
1217 -- l_changed_attributes(l_shp_index).customer_item_id := l_line_rec.ordered_item_id;
1218 /* End of changes for ER 6110708 */
1219
1220 IF l_debug_level > 0 THEN
1221 oe_debug_pub.add('LINE-NUMBER '
1222 || L_CHANGED_ATTRIBUTES ( L_SHP_INDEX ) .LINE_NUMBER , 3 ) ;
1223 END IF;
1224
1225 <<END_UPDATE_SHIPPING_LOOP>>
1226 NULL;
1227
1228 END LOOP; -- big loop
1229
1230 IF l_debug_level > 0 THEN
1231 oe_debug_pub.add( 'COMMING OUT LOOP , NUMBER OF RECORDS '
1232 ||L_CHANGED_ATTRIBUTES.COUNT , 3 ) ;
1233
1234 END IF;
1235 IF g_debug_call > 0 THEN
1236 G_DEBUG_MSG := G_DEBUG_MSG || '44,';
1237 END IF;
1238
1239
1240 ------------------ Call update_shipping_attribute.-------------
1241
1242 IF l_changed_attributes.count > 0 THEN
1243
1244 IF l_debug_level > 0 THEN
1245 oe_debug_pub.add('CALLING WSH UPDATE_SHIPPING_ATTRIBUTES' , 2 ) ;
1246
1247 END IF;
1248
1249 IF (NVL(FND_PROFILE.VALUE('WSH_ENABLE_DCP'), -1) = 1 OR
1250 NVL(FND_PROFILE.VALUE('WSH_ENABLE_DCP'), -1) = 2) AND
1251 WSH_DCP_PVT.G_CALL_DCP_CHECK = 'Y' THEN
1252 l_msg_data := 'CHECK_WSH_DCP';
1253 WSH_DCP_PVT.G_INIT_MSG_COUNT := fnd_msg_pub.count_msg;
1254 END IF;
1255
1256 SAVEPOINT CATCH_DCP;
1257 <<BEFORE_CATCH_DCP>>
1258
1259 WSH_INTERFACE.Update_Shipping_Attributes
1260 ( p_source_code => l_source_code
1261 ,p_changed_attributes => l_changed_attributes
1262 ,x_return_status => l_return_status);
1263
1264 IF l_debug_level > 0 THEN
1265 oe_debug_pub.add( 'RETURNED FROM WSH UPDATE_SHIPPING_ATTRIBUTES '
1266 || L_RETURN_STATUS , 2 ) ;
1267 END IF;
1268
1269 IF g_debug_call > 0 THEN
1270 G_DEBUG_MSG := G_DEBUG_MSG || '46,';
1271 END IF;
1272
1273 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1274
1275 --Changes for Bug - 2898616
1276 IF l_force_ui = 'Y' THEN
1277 IF l_debug_level > 0 THEN
1278 oe_debug_pub.add('Setting the global to force UI Block',2);
1279 END IF;
1280 OE_GLOBALS.G_FORCE_CLEAR_UI_BLOCK := 'Y';
1281 END IF;
1282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1283 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1284 -- 3571148 changes
1285 IF l_debug_level > 0 THEN
1286 oe_debug_pub.add( ' INSIDE EXPECTED ERROR' , 1 ) ;
1287 END IF;
1288 OE_MSG_PUB.Transfer_Msg_Stack(p_type => 'ERROR' ); --bug4741573
1289 l_msg_count := OE_MSG_PUB.COUNT_MSG;
1290
1291 FOR I IN 1..l_msg_count LOOP
1292 l_msg_data := OE_MSG_PUB.Get(I,'F');
1293 IF l_debug_level > 0 THEN
1294 oe_debug_pub.add( L_MSG_DATA , 1 ) ;
1295 END IF;
1296 --code fix bug 6391881 :START
1297 IF ( InStr(l_msg_data, 'WSH_NO_DATA_FOUND') > 0 ) THEN
1298 oe_debug_pub.ADD('Bug 6391881: Setting l_wsh_no_data_found to true...' , 1);
1299 l_wsh_no_data_found := TRUE;
1300 END IF;
1301 --code fix bug 6391881 :END
1302 END LOOP; -- 3571148 ends
1303 IF l_force_ui = 'Y' THEN
1304 IF l_debug_level > 0 THEN
1305 oe_debug_pub.add('Setting the global to force UI Block',2);
1306 END IF;
1307 OE_GLOBALS.G_FORCE_CLEAR_UI_BLOCK := 'Y';
1308 END IF;
1309 -- Start: Changes for bug 6391881
1310 -- Check if we are coming from wsh_no_data_found error.
1311 IF ( l_wsh_no_data_found = TRUE) THEN
1312 oe_debug_pub.ADD('Bug 6391881::: Checking for cancellation...' , 1);
1313 FOR I in 1..l_changed_attributes.count LOOP
1314 IF ( l_changed_attributes(I).action_flag = 'U' AND
1315 l_changed_attributes(I).ordered_quantity = 0 ) THEN
1316 oe_debug_pub.ADD('Bug 6391881::: Cancellation is YES.', 1);
1317 -- Check for existence of non-cancelled delivery details.
1318 -- If they exist, cancellation is not allowed.
1319 DECLARE
1320 l_non_cxl_dd_count NUMBER := -1;
1321 l_exist_dd_count NUMBER := -1;
1322 BEGIN
1323 SELECT Count(*)
1324 INTO l_exist_dd_count
1325 FROM wsh_delivery_details
1326 WHERE source_line_id = l_changed_attributes(I).source_line_id
1327 AND source_code = 'OE'
1328 ;
1329 IF ( l_exist_dd_count > 0 ) THEN
1330 SELECT Count(*)
1331 INTO l_non_cxl_dd_count
1332 FROM wsh_delivery_details wdd, oe_order_lines line
1333 WHERE wdd.source_line_id = line.line_id
1334 AND wdd.source_code = 'OE'
1335 AND wdd.source_line_id = l_changed_attributes(I).source_line_id
1336 AND Nvl(wdd.released_status, 'N') <> 'D'
1337 ;
1338 oe_debug_pub.ADD('Bug 6391881::: l_non_cxl_dd_count: '
1339 || l_non_cxl_dd_count, 1);
1340 IF ( l_non_cxl_dd_count > 0 ) THEN
1341 oe_debug_pub.ADD('Bug 6391881::: Setting l_ok_to_cancel to false...', 1);
1342 l_ok_to_cancel := FALSE;
1343 ELSE
1344 oe_debug_pub.ADD('Bug 6391881::: Setting l_return_status to SUCCESS...', 1);
1345 l_msg_data := NULL;
1346 l_return_status := FND_API.G_RET_STS_SUCCESS;
1347 oe_msg_pub.delete_msg;
1348 END IF;
1349 ELSE -- we don't cancel a line when delivery details go missing.
1350 l_ok_to_cancel := FALSE;
1351 END IF; -- on l_exist_dd_count
1352 EXCEPTION
1353 WHEN OTHERS THEN
1354 RAISE;
1355 END;
1356 END IF;
1357 END LOOP;
1358 END IF; -- on l_wsh_no_data_found
1359
1360 IF l_wsh_no_data_found = TRUE THEN
1361 oe_debug_pub.ADD('Bug 6391881::: l_wsh_no_data_found is true.', 1);
1362 ELSE
1363 oe_debug_pub.ADD('Bug 6391881::: l_wsh_data_found is false.', 1);
1364 END IF;
1365
1366 IF l_ok_to_cancel = TRUE THEN
1367 oe_debug_pub.ADD('Bug 6391881::: l_ok_to_cancel is true.', 1);
1368 ELSE
1369 oe_debug_pub.ADD('Bug 6391881::: l_ok_to_cancel is false.', 1);
1370 END IF;
1371
1372 IF NOT ( l_wsh_no_data_found = TRUE AND l_ok_to_cancel = TRUE ) THEN
1373 RAISE fnd_api.g_exc_error;
1374 END IF;
1375 -- End: Changes for bug 6391881
1376
1377 IF g_debug_call > 0 THEN
1378 G_DEBUG_MSG := G_DEBUG_MSG || '47,';
1379 END IF;
1380
1381 ELSE ------- success, call catch DCP code ----------
1382
1383 -- temp for testing only
1384 -- update oe_order_lines set ship_from_org_id = 99
1385 -- where line_id = l_changed_attributes(1).source_line_id;
1386
1387 IF l_changed_attributes.COUNT = 1 AND
1388 l_changed_attributes(1).action_flag = 'D' THEN
1389 IF l_debug_level > 0 THEN
1390 oe_debug_pub.add('action delete, do not call dcp here',2);
1391 END IF;
1392 l_msg_data := null;
1393 END IF;
1394
1395 IF l_msg_data = 'CHECK_WSH_DCP' THEN
1396
1397 l_header_id := nvl(l_line_rec.header_id, l_header_id);
1398 IF l_header_id is NULL THEN
1399 SELECT header_id
1400 INTO l_header_id
1401 FROM oe_order_lines
1402 WHERE line_id = l_changed_attributes(1).source_line_id;
1403 END IF;
1404
1405
1406 l_line_id := null;
1407 IF l_changed_attributes.COUNT = 1 THEN
1408 l_line_id := l_changed_attributes(1).source_line_id;
1409 END IF;
1410
1411 BEGIN
1412
1413 WSH_DCP_PVT.g_dc_table.DELETE;
1414
1415 IF l_debug_level > 0 THEN
1416 oe_debug_pub.add('CALLING WSH_DCP_PVT.Check_Scripts '
1417 ||'l_header-'|| l_header_id, 1);
1418 END IF;
1419
1420 WSH_DCP_PVT.Check_Scripts
1421 ( p_source_header_id => l_header_id
1422 ,p_source_line_id => l_line_id
1423 ,x_data_inconsistent => l_msg_data);
1424
1425 IF l_debug_level > 0 THEN
1426 oe_debug_pub.add
1427 ('CALLING WSH_DCP_PVT.Post_Process '|| l_msg_data, 1);
1428 END IF;
1429
1430 WSH_DCP_PVT.Post_Process
1431 ( p_action_code => 'OM'
1432 ,p_raise_exception => 'Y');
1433
1434 EXCEPTION
1435 WHEN WSH_DCP_PVT.dcp_caught THEN
1436 IF l_debug_level > 0 THEN
1437 oe_debug_pub.add('OM call to WSH DCP Caught,
1438 rollback and call wsh again', 1);
1439 END IF;
1440
1441 ROLLBACK to CATCH_DCP;
1442 GOTO BEFORE_CATCH_DCP;
1443
1444 WHEN others THEN
1445 IF l_debug_level > 0 THEN
1446 oe_msg_pub.add_text
1447 ('Update_Shipping_From_OE, DCP post process'|| sqlerrm);
1448 oe_debug_pub.add('OM call to WSH DCP,others '|| sqlerrm, 1);
1449 END IF;
1450 END;
1451 END IF; -- profile is yes
1452 END IF; -- return status check
1453
1454 ELSE
1455
1456 l_return_status := FND_API.G_RET_STS_SUCCESS;
1457 IF l_debug_level > 0 THEN
1458 oe_debug_pub.add('DO NOT CALL SHIPPING API,RETURN SUCCESS ',3);
1459 END IF;
1460
1461 END IF;-- count is 0
1462
1463 OE_MSG_PUB.Reset_Msg_Context (p_entity_code => 'LINE');
1464 x_return_status := l_return_status;
1465
1466
1467 -- code changes for 5590106 starts here.
1468
1469 FOR I in 1..l_changed_attributes.count LOOP
1470
1471 IF l_changed_attributes(I).action_flag = 'U' AND
1472 l_changed_attributes(I).ordered_quantity = 0 THEN
1473
1474 OE_DEBUG_PUB.add('order line id '||l_changed_attributes(I).source_line_id,5);
1475 OE_DEBUG_PUB.add('action flag '||l_changed_attributes(I).action_flag,5);
1476 OE_DEBUG_PUB.add('ordered quantity '||l_changed_attributes(I).ordered_quantity,5);
1477
1478 BEGIN
1479
1480 SELECT COUNT(*)
1481 INTO l_cxl_dd_count
1482 FROM WSH_DELIVERY_DETAILS
1483 WHERE SOURCE_LINE_ID = l_changed_attributes(I).source_line_id
1484 AND SOURCE_CODE = 'OE'
1485 AND RELEASED_STATUS <> 'D';
1486
1487 IF l_cxl_dd_count > 0 THEN
1488 OE_DEBUG_PUB.add('DD is not cancelled',1);
1489 fnd_message.set_name('ONT', 'OE_CANCEL_NOTHING');
1490 oe_msg_pub.add;
1491 RAISE FND_API.G_EXC_ERROR ;
1492
1493 END IF;
1494
1495 EXCEPTION WHEN OTHERS THEN
1496 RAISE;
1497 END;
1498 END IF;
1499
1500 END LOOP;
1501
1502 IF l_debug_level > 0 THEN -- debug level 0.5 added for bug 13435459
1503 oe_debug_pub.add('Exit update_shipping_from_oe() at ',0.5);
1504 END IF;
1505
1506 EXCEPTION
1507 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1508
1509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1510
1511 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1512 THEN
1513 OE_MSG_PUB.Add_Exc_Msg
1514 ( G_PKG_NAME,
1515 'Update_Shipping_From_OE'
1516 );
1517 END IF;
1518 OE_MSG_PUB.Save_API_Messages(); --bug 4516453
1519 OE_MSG_PUB.Reset_Msg_Context (p_entity_code => 'LINE');
1520 IF g_debug_call > 0 THEN
1521 G_DEBUG_MSG := G_DEBUG_MSG || 'E5';
1522 END IF;
1523 WHEN FND_API.G_EXC_ERROR THEN
1524 x_return_status := FND_API.G_RET_STS_ERROR;
1525 OE_MSG_PUB.Save_API_Messages(); --bug 4516453
1526 OE_MSG_PUB.Reset_Msg_Context (p_entity_code => 'LINE');
1527 IF g_debug_call > 0 THEN
1528 G_DEBUG_MSG := G_DEBUG_MSG || 'Shp-E-4';
1529 END IF;
1530 WHEN OTHERS THEN
1531 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1532
1533 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1534 THEN
1535 OE_MSG_PUB.Add_Exc_Msg
1536 ( G_PKG_NAME,
1537 'Update_Shipping_From_OE'
1538 );
1539 END IF;
1540 IF l_debug_level > 0 THEN
1541 oe_debug_pub.add( 'ERROR MESSAGE : '||SQLERRM , 1 ) ;
1542 END IF;
1543 OE_MSG_PUB.Save_API_Messages(); --bug 4516453
1544 OE_MSG_PUB.Reset_Msg_Context (p_entity_code => 'LINE');
1545 IF g_debug_call > 0 THEN
1546 G_DEBUG_MSG := G_DEBUG_MSG || 'Shp-E-5';
1547 END IF;
1548 END Update_Shipping_From_OE;
1549
1550 /*----------------------------------------------------------------
1551 PROCEDURE Update_Shipping_PVT
1552 -----------------------------------------------------------------*/
1553 PROCEDURE Update_Shipping_PVT
1554 (
1555 p_line_id IN NUMBER
1556 , p_hold_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
1557 , p_shipping_activity IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
1558 , x_return_status OUT NOCOPY VARCHAR2
1559
1560 )
1561 IS
1562 l_update_lines_tbl OE_ORDER_PUB.Request_Tbl_Type;
1563 --
1564 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1565 --
1566 BEGIN
1567
1568 IF l_debug_level > 0 THEN
1569 oe_debug_pub.add( 'ENTERING OE_SHIPPING_INTEGRATION_PVT.UPDATE_SHIPPING_PVT' , 1 ) ;
1570 END IF;
1571 IF g_debug_call > 0 THEN
1572 G_DEBUG_MSG := G_DEBUG_MSG || '51,';
1573 END IF;
1574
1575 -- Prepare the table for calling Update_Shipping_From_OE
1576
1577 l_update_lines_tbl(1).entity_id := p_line_id;
1578 l_update_lines_tbl(1).param1 := FND_API.G_FALSE;
1579 l_update_lines_tbl(1).param2 := FND_API.G_FALSE;
1580
1581 IF p_hold_type IS NOT NULL AND
1582 p_hold_type <> FND_API.G_MISS_CHAR THEN
1583
1584 l_update_lines_tbl(1).param3 := p_hold_type;
1585
1586 END IF;
1587
1588 IF p_shipping_activity IS NOT NULL AND
1589 p_shipping_activity <> FND_API.G_MISS_CHAR THEN
1590
1591 l_update_lines_tbl(1).param4 := p_shipping_activity;
1592
1593 END IF;
1594
1595 Update_Shipping_From_OE
1596 (
1597 p_update_lines_tbl => l_update_lines_tbl,
1598 x_return_status => x_return_status
1599 );
1600
1601 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1602 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1603 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1604 RAISE FND_API.G_EXC_ERROR;
1605 END IF;
1606 IF l_debug_level > 0 THEN
1607 oe_debug_pub.add( 'EXITING OE_SHIPPING_INTEGRATION_PVT.UPDATE_SHIPPING_PVT' , 1 ) ;
1608 END IF;
1609 IF g_debug_call > 0 THEN
1610 G_DEBUG_MSG := G_DEBUG_MSG || '54,';
1611 END IF;
1612 EXCEPTION
1613
1614 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1615 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1616
1617 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1618 THEN
1619 OE_MSG_PUB.Add_Exc_Msg
1620 ( G_PKG_NAME,
1621 'Update_Shipping_PVT'
1622 );
1623 END IF;
1624 IF g_debug_call > 0 THEN
1625 G_DEBUG_MSG := G_DEBUG_MSG || 'E6,';
1626 END IF;
1627 WHEN FND_API.G_EXC_ERROR THEN
1628 x_return_status := FND_API.G_RET_STS_ERROR;
1629 WHEN OTHERS THEN
1630 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1631
1632 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1633 THEN
1634 OE_MSG_PUB.Add_Exc_Msg
1635 ( G_PKG_NAME,
1636 'Update_Shipping_PVT'
1637 );
1638 END IF;
1639 IF l_debug_level > 0 THEN
1640 oe_debug_pub.add( 'ERROR MESSAGE : '||SQLERRM , 1 ) ;
1641 END IF;
1642 IF g_debug_call > 0 THEN
1643 G_DEBUG_MSG := G_DEBUG_MSG || 'E7,';
1644 END IF;
1645 NULL;
1646 END Update_Shipping_PVT;
1647
1648 /*-------------------------------------------------------------------
1649 PROCEDURE Check_Shipment_Line
1650
1651 Line_id should be passed to check the shipment across the split lines.
1652 If line id is not passed the ordered and shipped quantity must be passed
1653 in order to do the check for shipment.
1654 --------------------------------------------------------------------*/
1655 PROCEDURE Check_Shipment_Line --INVCONV PAL OPEN ISSUE
1656 ( p_line_rec IN OE_Order_Pub.Line_Rec_Type
1657 ,p_shipped_quantity IN NUMBER DEFAULT 0
1658 ,x_result_out OUT NOCOPY VARCHAR2 )
1659 IS
1660 l_line_id NUMBER;
1661 l_line_rec OE_Order_Pub.Line_Rec_Type;
1662 l_msg_count NUMBER;
1663 l_msg_data VARCHAR2(2000);
1664 l_ordered_quantity NUMBER := 0;
1665 l_shipped_quantity NUMBER := 0;
1666 l_shipping_quantity NUMBER := 0;
1667 l_temp_shipped_quantity NUMBER := 0;
1668 l_tolerance_quantity_below NUMBER := 0;
1669 l_tolerance_quantity_above NUMBER := 0;
1670 l_ship_tolerance_below NUMBER := 0;
1671 l_ship_tolerance_above NUMBER := 0;
1672 l_validated_quantity NUMBER := 0;
1673 l_primary_quantity NUMBER := 0;
1674 l_qty_return_status VARCHAR2(1);
1675 l_x_return_status VARCHAR2(1);
1676 l_org_id NUMBER;
1677 l_wdd_count NUMBER :=0;
1678
1679 --
1680 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1681 --
1682 BEGIN
1683
1684 IF l_debug_level > 0 THEN
1685 oe_debug_pub.add
1686 ('ENTERING OE_SHIPPING_INTEGRATION_PVT.CHECK_SHIPMENT_LINE : '
1687 ||TO_CHAR ( P_LINE_REC.LINE_ID ) ||'/'||TO_CHAR ( P_SHIPPED_QUANTITY )
1688 ||'/'||P_LINE_REC.ITEM_TYPE_CODE , 1 ) ;
1689 END IF;
1690 IF g_debug_call > 0 THEN
1691 G_DEBUG_MSG := G_DEBUG_MSG || '61';
1692 END IF;
1693
1694 IF p_line_rec.top_model_line_id IS NOT NULL AND
1695 p_line_rec.top_model_line_id <> FND_API.G_MISS_NUM AND
1696 NVL(p_line_rec.model_remnant_flag,'N') = 'N' THEN
1697
1698
1699 SELECT line_id,
1700 line_set_id,
1701 ordered_quantity,
1702 shipped_quantity,
1703 shipping_quantity,
1704 ship_tolerance_below,
1705 ship_tolerance_above,
1706 item_type_code,
1707 inventory_item_id,
1708 order_quantity_uom,
1709 shipping_quantity_uom
1710 INTO l_line_rec.line_id,
1711 l_line_rec.line_set_id,
1712 l_line_rec.ordered_quantity,
1713 l_line_rec.shipped_quantity,
1714 l_line_rec.shipping_quantity,
1715 l_line_rec.ship_tolerance_below,
1716 l_line_rec.ship_tolerance_above,
1717 l_line_rec.item_type_code,
1718 l_line_rec.inventory_item_id,
1719 l_line_rec.order_quantity_uom,
1720 l_line_rec.shipping_quantity_uom
1721 FROM OE_ORDER_LINES
1722 WHERE line_id = p_line_rec.top_model_line_id;
1723
1724 IF l_debug_level > 0 THEN
1725 oe_debug_pub.add('SHIPPED QUANTITY : '
1726 ||TO_CHAR ( L_LINE_REC.SHIPPED_QUANTITY ) , 3 ) ;
1727 END IF;
1728 IF g_debug_call > 0 THEN
1729 G_DEBUG_MSG := G_DEBUG_MSG || '62,';
1730 END IF;
1731
1732 ELSE
1733 l_line_rec:= p_line_rec;
1734 END IF; -- if part of model
1735
1736
1737 IF l_line_rec.ship_tolerance_below IS NULL OR
1738 l_line_rec.ship_tolerance_below = FND_API.G_MISS_NUM THEN
1739 l_ship_tolerance_below := 0;
1740 ELSE
1741 l_ship_tolerance_below := l_line_rec.ship_tolerance_below;
1742 END IF;
1743
1744
1745 IF l_line_rec.ship_tolerance_above IS NULL OR
1746 l_line_rec.ship_tolerance_above = FND_API.G_MISS_NUM THEN
1747
1748 l_ship_tolerance_above := 0;
1749 ELSE
1750 l_ship_tolerance_above := l_line_rec.ship_tolerance_above;
1751 END IF;
1752
1753
1754
1755 -- Call Get_Quantity to get the cumulative ordered and shipped quantity if
1756 -- this line is part of any line set - only for oe table
1757
1758 IF l_debug_level > 0 THEN
1759 oe_debug_pub.add('LINE SET ID '||TO_CHAR(L_LINE_REC.LINE_SET_ID),3);
1760 END IF;
1761
1762 IF l_line_rec.line_set_id IS NOT NULL AND
1763 l_line_rec.line_set_id <> FND_API.G_MISS_NUM THEN
1764
1765 IF g_debug_call > 0 THEN
1766 G_DEBUG_MSG := G_DEBUG_MSG || '64';
1767 END IF;
1768
1769 OE_Shipping_Integration_PUB.Get_Quantity
1770 (p_api_version_number => 1.0,
1771 p_line_id => l_line_rec.line_id,
1772 p_line_set_id => l_line_rec.line_set_id,
1773 x_ordered_quantity => l_ordered_quantity,
1774 x_shipped_quantity => l_shipped_quantity,
1775 x_shipping_quantity => l_shipping_quantity,
1776 x_return_status => l_x_return_status,
1777 x_msg_count => l_msg_count,
1778 x_msg_data => l_msg_data);
1779
1780 IF l_x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1781 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1782 ELSIF l_x_return_status = FND_API.G_RET_STS_ERROR THEN
1783 RAISE FND_API.G_EXC_ERROR;
1784 END IF;
1785
1786 ELSE
1787
1788 l_ordered_quantity := l_line_rec.ordered_quantity;
1789 l_shipped_quantity := nvl(l_line_rec.shipped_quantity,0);
1790 l_shipping_quantity := nvl(l_line_rec.shipping_quantity,0);
1791
1792 END IF;
1793
1794
1795 --********
1796 l_shipped_quantity := nvl(l_shipped_quantity,0) + nvl(p_shipped_quantity,0);
1797 --********
1798
1799 -- Calculate the tolerance quantities
1800
1801 l_tolerance_quantity_below:=l_ordered_quantity*l_ship_tolerance_below/100;
1802 l_tolerance_quantity_above:=l_ordered_quantity*l_ship_tolerance_above/100;
1803
1804 IF l_tolerance_quantity_below <> trunc(l_tolerance_quantity_below) THEN
1805
1806 IF l_line_rec.item_type_code IN
1807 ('MODEL', 'OPTION','KIT','CLASS','INCLUDED','CONFIG') THEN
1808 l_tolerance_quantity_below := FLOOR(l_tolerance_quantity_below);
1809
1810 ELSE
1811 IF l_debug_level > 0 THEN
1812 oe_debug_pub.add('CALLING INVENTORY VALIDATE QUANTITY : '
1813 ||L_QTY_RETURN_STATUS , 2 ) ;
1814 END IF;
1815
1816
1817 Inv_Decimals_PUB.Validate_Quantity
1818 ( p_item_id => l_line_rec.inventory_item_id,
1819 p_organization_id => OE_Sys_Parameters.value('MASTER_ORGANIZATION_ID'),
1820 p_input_quantity => l_tolerance_quantity_below,
1821 p_uom_code => l_line_rec.order_quantity_uom,
1822 x_output_quantity => l_validated_quantity,
1823 x_primary_quantity => l_primary_quantity,
1824 x_return_status => l_qty_return_status);
1825
1826 IF l_debug_level > 0 THEN
1827 oe_debug_pub.add('RET STS FROM INV API : '||L_QTY_RETURN_STATUS , 2 ) ;
1828 END IF;
1829
1830 IF g_debug_call > 0 THEN
1831 G_DEBUG_MSG := G_DEBUG_MSG || '6-6';
1832 END IF;
1833
1834 IF l_qty_return_status IN ('W','S') THEN
1835
1836 IF l_debug_level > 0 THEN
1837 oe_debug_pub.add('TOL BELOW/VALID QTY : '
1838 ||TO_CHAR ( L_TOLERANCE_QUANTITY_BELOW )
1839 ||'/'||TO_CHAR ( L_VALIDATED_QUANTITY ) , 3 ) ;
1840 END IF;
1841
1842 l_tolerance_quantity_below := l_validated_quantity;
1843 ELSE
1844 l_tolerance_quantity_below := FLOOR(l_tolerance_quantity_below);
1845 END IF; -- if qty stst is W, S
1846 END IF; -- item type =model...
1847 END IF; -- if tol in decimal
1848
1849
1850 IF l_tolerance_quantity_above <> trunc(l_tolerance_quantity_above) THEN
1851
1852 IF l_line_rec.item_type_code IN
1853 ('MODEL', 'OPTION','KIT','CLASS','INCLUDED','CONFIG') THEN
1854 l_tolerance_quantity_above := CEIL(l_tolerance_quantity_above);
1855 ELSE
1856 IF l_debug_level > 0 THEN
1857 oe_debug_pub.add('CALLING INVENTORY VALIDATE QUANTITY : '
1858 ||L_QTY_RETURN_STATUS , 2 ) ;
1859 END IF;
1860
1861 Inv_Decimals_PUB.Validate_Quantity
1862 ( p_item_id => l_line_rec.inventory_item_id,
1863 p_organization_id => OE_Sys_Parameters.value('MASTER_ORGANIZATION_ID'),
1864 p_input_quantity => l_tolerance_quantity_above,
1865 p_uom_code => l_line_rec.order_quantity_uom,
1866 x_output_quantity => l_validated_quantity,
1867 x_primary_quantity => l_primary_quantity,
1868 x_return_status => l_qty_return_status);
1869
1870 IF l_debug_level > 0 THEN
1871 oe_debug_pub.add('RETURN STATUS FROM INV API : '
1872 ||L_QTY_RETURN_STATUS , 2 ) ;
1873 END IF;
1874
1875 IF l_qty_return_status IN ('W','S') THEN
1876 IF l_debug_level > 0 THEN
1877 oe_debug_pub.add( 'TOL ABOVE/VALID QTY : '
1878 ||TO_CHAR ( L_TOLERANCE_QUANTITY_ABOVE )
1879 ||'/'||TO_CHAR ( L_VALIDATED_QUANTITY ) , 3 ) ;
1880 END IF;
1881
1882 l_tolerance_quantity_above := l_validated_quantity;
1883 ELSE
1884 l_tolerance_quantity_above := CEIL(l_tolerance_quantity_above);
1885 END IF;
1886 END IF;
1887 END IF;
1888
1889 IF g_debug_call > 0 THEN
1890 G_DEBUG_MSG := G_DEBUG_MSG || '68,';
1891 END IF;
1892
1893 ---------- done with getting tol above and below.
1894
1895
1896 IF l_debug_level > 0 THEN
1897 oe_debug_pub.add('ORDERED QUANTITY ' || TO_CHAR ( L_ORDERED_QUANTITY),3 );
1898 oe_debug_pub.add('SHIPPED QUANTITY ' || TO_CHAR ( L_SHIPPED_QUANTITY),3 );
1899 oe_debug_pub.add('SHIPPING QUANTITY ' || TO_CHAR( L_SHIPPING_QUANTITY),3 );
1900 oe_debug_pub.add('TOLERANCE BELOW ' || TO_CHAR (L_SHIP_TOLERANCE_BELOW),3 );
1901 oe_debug_pub.add('TOLERAQNCE ABOVE ' || TO_CHAR(L_SHIP_TOLERANCE_ABOVE),3 );
1902 oe_debug_pub.add('TOLE QTY BELOW '||TO_CHAR(L_TOLERANCE_QUANTITY_BELOW),3 );
1903 oe_debug_pub.add('TOLE QTY ABOVE '|| TO_CHAR(L_TOLERANCE_QUANTITY_ABOVE),3 );
1904 END IF;
1905
1906
1907
1908 IF l_ordered_quantity= l_shipped_quantity THEN
1909 x_result_out :=OE_GLOBALS.G_FULLY_SHIPPED;
1910
1911 ELSIF l_ordered_quantity <= l_shipped_quantity + l_tolerance_quantity_below AND
1912 l_shipped_quantity < l_ordered_quantity THEN
1913 x_result_out :=OE_GLOBALS.G_SHIPPED_WITHIN_TOL_BELOW;
1914
1915 ELSIF l_ordered_quantity > l_shipped_quantity + l_tolerance_quantity_below THEN
1916
1917 IF l_line_rec.shipping_quantity_uom <> l_line_rec.order_quantity_uom THEN
1918 l_temp_shipped_quantity :=
1919 OE_Order_Misc_Util.Convert_Uom
1920 ( l_line_rec.inventory_item_id,
1921 l_line_rec.shipping_quantity_uom,
1922 l_line_rec.order_quantity_uom,
1923 l_shipping_quantity);
1924
1925 IF l_debug_level > 0 THEN
1926 oe_debug_pub.add('CONVERTED SHIPPED QUANTITY : '
1927 || TO_CHAR ( L_TEMP_SHIPPED_QUANTITY),3 );
1928 END IF;
1929
1930 IF g_debug_call > 0 THEN
1931 G_DEBUG_MSG := G_DEBUG_MSG || '69,';
1932 END IF;
1933
1934 IF l_temp_shipped_quantity <> trunc(l_temp_shipped_quantity) THEN
1935 IF l_debug_level > 0 THEN
1936 oe_debug_pub.add('CALLING INVENTORY VALIDATE QUANTITY : '
1937 ||L_QTY_RETURN_STATUS , 2 );
1938 END IF;
1939
1940 Inv_Decimals_PUB.Validate_Quantity
1941 (p_item_id => l_line_rec.inventory_item_id,
1942 p_organization_id => OE_Sys_Parameters.value('MASTER_ORGANIZATION_ID'),
1943 p_input_quantity => l_temp_shipped_quantity,
1944 p_uom_code => l_line_rec.order_quantity_uom,
1945 x_output_quantity => l_validated_quantity,
1946 x_primary_quantity => l_primary_quantity,
1947 x_return_status => l_qty_return_status);
1948
1949 IF l_debug_level > 0 THEN
1950 oe_debug_pub.add('RET STS FROM INV API : '||L_QTY_RETURN_STATUS , 2 );
1951 END IF;
1952
1953 IF l_qty_return_status = 'W' THEN
1954 IF l_debug_level > 0 THEN
1955 oe_debug_pub.add('VALID QTY : '||TO_CHAR ( L_VALIDATED_QUANTITY),3 );
1956 END IF;
1957 l_temp_shipped_quantity := l_validated_quantity;
1958 END IF;
1959
1960 IF l_debug_level > 0 THEN
1961 oe_debug_pub.add( 'VALIDATED SHIPPED QUANTITY : '
1962 || TO_CHAR ( L_TEMP_SHIPPED_QUANTITY),3 );
1963 END IF;
1964
1965 END IF; -- done validate decimal
1966
1967 IF l_ordered_quantity> l_temp_shipped_quantity + l_tolerance_quantity_below
1968 THEN
1969 x_result_out :=OE_GLOBALS.G_PARTIALLY_SHIPPED;
1970 ELSE
1971 x_result_out :=OE_GLOBALS.G_FULLY_SHIPPED;
1972 END IF;
1973
1974 IF l_debug_level > 0 THEN
1975 OE_DEBUG_PUB.Add('Check the Decimals for Standard Items..Different UOM');
1976 OE_DEBUG_PUB.Add('Changes for Bug-3468847');
1977 END IF;
1978
1979 IF p_line_rec.item_type_code = 'STANDARD' THEN
1980
1981 SELECT count(*)
1982 INTO l_wdd_count
1983 FROM wsh_delivery_details
1984 WHERE source_line_id = p_line_rec.line_id
1985 AND released_status <> 'D'
1986 AND source_code = 'OE'
1987 AND oe_interfaced_flag = 'N';
1988
1989
1990 IF l_wdd_count = 0 THEN
1991 IF l_debug_level > 0 THEN
1992 OE_DEBUG_PUB.Add('Delivery Detail Did not Split..');
1993 END IF;
1994 x_result_out := OE_GLOBALS.G_FULLY_SHIPPED;
1995 ELSE
1996 IF l_debug_level > 0 THEN
1997 OE_DEBUG_PUB.Add('Delivery Detail Has Split..');
1998 END IF;
1999 END IF;
2000 END IF;
2001
2002 ELSE
2003 x_result_out :=OE_GLOBALS.G_PARTIALLY_SHIPPED;
2004 END IF; -- different uom
2005
2006 IF g_debug_call > 0 THEN
2007 G_DEBUG_MSG := G_DEBUG_MSG || '61,';
2008 END IF;
2009
2010 -- Check for Shipment within ship tolerance above
2011
2012 ELSIF l_shipped_quantity <= l_ordered_quantity + l_tolerance_quantity_above AND
2013 l_ordered_quantity < l_shipped_quantity THEN
2014 x_result_out :=OE_GLOBALS.G_SHIPPED_WITHIN_TOL_ABOVE;
2015
2016 -- Check for Shipment above ship tolerance above
2017
2018 ELSIF l_shipped_quantity > l_ordered_quantity + l_tolerance_quantity_above THEN
2019 x_result_out :=OE_GLOBALS.G_SHIPPED_BEYOND_TOLERANCE;
2020 END IF;
2021
2022 -- Bug 5332001
2023 IF l_debug_level > 0 THEN
2024 OE_DEBUG_PUB.add('Checking for complete dropship receipt',1);
2025 END IF;
2026
2027 IF p_line_rec.source_type_code = 'EXTERNAL' THEN
2028
2029 DECLARE
2030
2031 poll_received_quantity NUMBER;
2032 actual_quantity NUMBER;
2033
2034 BEGIN
2035
2036 SELECT QUANTITY, QUANTITY_RECEIVED
2037 INTO actual_quantity,poll_received_quantity
2038 FROM PO_LINE_LOCATIONS_ALL
2039 WHERE LINE_LOCATION_ID = (SELECT LINE_LOCATION_ID
2040 FROM OE_DROP_SHIP_SOURCES
2041 WHERE LINE_ID = l_line_rec.line_id);
2042
2043 IF l_debug_level > 0 THEN
2044 OE_DEBUG_PUB.add('actual quantity '||actual_quantity||' poll received '||poll_received_quantity,1);
2045 END IF;
2046
2047 IF poll_received_quantity = actual_quantity THEN
2048 x_result_out := OE_GLOBALS.G_FULLY_SHIPPED;
2049 END IF;
2050
2051 EXCEPTION WHEN OTHERS THEN
2052 IF l_debug_level > 0 THEN
2053 OE_DEBUG_PUB.add('error when checking receipt info '||sqlerrm,1);
2054 END IF;
2055 NULL;
2056 END;
2057
2058 END IF;
2059 -- Bug 5332001 end
2060
2061 IF l_debug_level > 0 THEN
2062 oe_debug_pub.add('EXITING FROM CHECK_SHIPMENT_LINE : '||X_RESULT_OUT , 1 );
2063 END IF;
2064
2065 EXCEPTION
2066 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2067 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2068
2069 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2070 THEN
2071 OE_MSG_PUB.Add_Exc_Msg
2072 ( G_PKG_NAME,
2073 'Check_Shipment_Line'
2074 );
2075 END IF;
2076 IF g_debug_call > 0 THEN
2077 G_DEBUG_MSG := G_DEBUG_MSG || 'E8,';
2078 END IF;
2079
2080 WHEN OTHERS THEN
2081
2082 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2083 THEN
2084 OE_MSG_PUB.Add_Exc_Msg
2085 (G_PKG_NAME,
2086 'CHeck_Shipment_Line'
2087 );
2088 END IF;
2089 IF g_debug_call > 0 THEN
2090 G_DEBUG_MSG := G_DEBUG_MSG || 'E9,';
2091 END IF;
2092 IF l_debug_level > 0 THEN
2093 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 100 ) , 1 );
2094 END IF;
2095 END Check_Shipment_Line;
2096
2097 /*--------------------------------------------------------------------
2098 PROCEDURE Get_PTO_Shipped_Quantity
2099 ---------------------------------------------------------------------*/
2100 PROCEDURE Get_PTO_Shipped_Quantity
2101 (
2102 p_top_model_line_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
2103 , p_x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
2104 , x_ratio_status OUT NOCOPY VARCHAR2
2105
2106 , x_return_status OUT NOCOPY VARCHAR2
2107
2108 )
2109 IS
2110 l_pto_shipment_tbl Shipment_Tbl_Type;
2111 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
2112 l_line_index NUMBER;
2113 l_pto_index NUMBER :=0;
2114 l_top_index NUMBER :=0;
2115 l_final_index NUMBER :=0;
2116 l_parent_index NUMBER :=0;
2117 l_return_index NUMBER :=0;
2118 l_top_shipped_quantity NUMBER :=0;
2119 l_parent_shipped_quantity NUMBER :=0;
2120 l_ratio_status VARCHAR2(1) := FND_API.G_TRUE;
2121 l_ship_date DATE;
2122
2123 /* Added for bug 1952023 */
2124 l_over_shipped VARCHAR2(1) := FND_API.G_FALSE;
2125 l_under_shipped VARCHAR2(1) := FND_API.G_FALSE;
2126 l_line_shipped VARCHAR2(1) := FND_API.G_TRUE;
2127
2128 --
2129 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2130 --
2131 BEGIN
2132 IF l_debug_level > 0 THEN
2133 oe_debug_pub.add( 'ENTERING OE_SHIPPING_INTEGRATION_PVT.GET_PTO_SHIPPED_QUANTITY '|| TO_CHAR ( P_TOP_MODEL_LINE_ID ) , 1 ) ;
2134 END IF;
2135
2136 IF p_top_model_line_id IS NOT NULL AND
2137 p_top_model_line_id <> FND_API.G_MISS_NUM THEN
2138
2139 -- Call function to get all the lines in a PTO.
2140
2141 -- p_x_line_tbl := OE_Config_Util.Query_Options(p_top_model_line_id);
2142 OE_Config_Util.Query_Options(p_top_model_line_id => p_top_model_line_id,
2143 p_send_cancel_lines => 'Y',
2144 x_line_tbl => l_line_tbl);
2145
2146 ELSE
2147
2148 l_line_tbl := p_x_line_tbl;
2149
2150 END IF;
2151
2152 IF g_debug_call > 0 THEN
2153 G_DEBUG_MSG := G_DEBUG_MSG || '71';
2154 END IF;
2155
2156 IF l_debug_level > 0 THEN
2157 oe_debug_pub.add( 'NUMBER OF LINES IN THE TABLE '|| TO_CHAR ( L_LINE_TBL.COUNT ) , 3 ) ;
2158 END IF;
2159
2160 -- Populate the local shipment table.
2161
2162 FOR l_line_index IN l_line_tbl.FIRST .. l_line_tbl.LAST
2163 LOOP
2164
2165 IF l_line_tbl(l_line_index).ordered_quantity = 0 THEN
2166
2167 IF l_debug_level > 0 THEN
2168 oe_debug_pub.add( 'LINE WITH ZERO QUANTITY : '||L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID||'/'||L_LINE_TBL ( L_LINE_INDEX ) .ITEM_TYPE_CODE , 3 ) ;
2169 END IF;
2170 GOTO END_PREPARE_LOOP;
2171 END IF;
2172 l_final_index := l_final_index + 1;
2173 p_x_line_tbl(l_final_index) := l_line_tbl(l_line_index);
2174 -- l_pto_index := l_line_tbl(l_line_index).line_id; -- Bug 8795918
2175 l_pto_index := mod(l_line_tbl(l_line_index).line_id,OE_GLOBALS.G_BINARY_LIMIT);
2176
2177 l_pto_shipment_tbl(l_pto_index).line_id := l_line_tbl(l_line_index).line_id;
2178 l_pto_shipment_tbl(l_pto_index).top_model_line_id := l_line_tbl(l_line_index).top_model_line_id;
2179 l_pto_shipment_tbl(l_pto_index).ordered_quantity := l_line_tbl(l_line_index).ordered_quantity;
2180 l_pto_shipment_tbl(l_pto_index).shipped_quantity := l_line_tbl(l_line_index).shipped_quantity;
2181 l_pto_shipment_tbl(l_pto_index).link_to_line_id := l_line_tbl(l_line_index).link_to_line_id;
2182
2183 IF g_debug_call > 0 THEN
2184 G_DEBUG_MSG := G_DEBUG_MSG || '73,';
2185 END IF;
2186
2187 IF nvl(l_line_tbl(l_line_index).shippable_flag,'N') = 'Y' THEN
2188 l_pto_shipment_tbl(l_pto_index).shippable_flag := FND_API.G_TRUE;
2189 l_ship_date := l_line_tbl(l_line_index).actual_shipment_date;
2190
2191 IF nvl(l_line_tbl(l_line_index).shipped_quantity,0) = 0 THEN
2192 IF l_debug_level > 0 THEN
2193 oe_debug_pub.add( 'LINE IS NOT SHIPPED : '||L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID , 3 ) ;
2194 END IF;
2195 l_line_shipped := FND_API.G_FALSE;
2196 END IF;
2197 ELSE
2198 l_pto_shipment_tbl(l_pto_index).shippable_flag := FND_API.G_FALSE;
2199 END IF;
2200
2201 /* Added for bug 1952023 */
2202 IF nvl(l_line_tbl(l_line_index).shipped_quantity,0) > nvl(l_line_tbl(l_line_index).ordered_quantity,0) THEN
2203
2204 IF l_debug_level > 0 THEN
2205 oe_debug_pub.add( 'LINE IS OVER SHIPPED '||L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID , 3 ) ;
2206 END IF;
2207 l_over_shipped := FND_API.G_TRUE;
2208
2209 END IF;
2210
2211 IF nvl(l_line_tbl(l_line_index).shipped_quantity,0) < nvl(l_line_tbl(l_line_index).ordered_quantity,0) AND
2212 nvl(l_line_tbl(l_line_index).shippable_flag,'N') = 'Y' THEN
2213
2214 IF l_debug_level > 0 THEN
2215 oe_debug_pub.add( 'LINE IS UNDER SHIPPED '||L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID , 3 ) ;
2216 END IF;
2217 l_under_shipped := FND_API.G_TRUE;
2218
2219 END IF;
2220
2221 << END_PREPARE_LOOP >>
2222 NULL;
2223
2224 END LOOP;
2225 IF g_debug_call > 0 THEN
2226 G_DEBUG_MSG := G_DEBUG_MSG || '74';
2227 END IF;
2228 -- Calculate the ratios.
2229
2230 l_pto_index := l_pto_shipment_tbl.FIRST;
2231
2232 WHILE l_pto_index IS NOT NULL
2233 LOOP
2234
2235
2236 -- l_top_index := l_pto_shipment_tbl(l_pto_index).top_model_line_id; -- Bug 8795918
2237 l_top_index := mod(l_pto_shipment_tbl(l_pto_index).top_model_line_id,OE_GLOBALS.G_BINARY_LIMIT);
2238
2239 -- l_parent_index := nvl(l_pto_shipment_tbl(l_pto_index).link_to_line_id,l_pto_shipment_tbl(l_pto_index).top_model_line_id); -- Bug 8795918
2240 l_parent_index := mod(nvl(l_pto_shipment_tbl(l_pto_index).link_to_line_id,l_pto_shipment_tbl(l_pto_index).top_model_line_id),OE_GLOBALS.G_BINARY_LIMIT);
2241 l_pto_shipment_tbl(l_pto_index).ratio_to_top_model := l_pto_shipment_tbl(l_pto_index).ordered_quantity/l_pto_shipment_tbl(l_top_index).ordered_quantity;
2242
2243 IF l_parent_index <> 0 THEN
2244
2245 l_pto_shipment_tbl(l_pto_index).ratio_to_parent := l_pto_shipment_tbl(l_pto_index).ordered_quantity/l_pto_shipment_tbl(l_parent_index).ordered_quantity;
2246 ELSE
2247 l_pto_shipment_tbl(l_pto_index).ratio_to_parent := 1;
2248 END IF;
2249
2250 IF l_debug_level > 0 THEN
2251 oe_debug_pub.add( 'RATIO TO PARENT = '||TO_CHAR ( L_PTO_SHIPMENT_TBL ( L_PTO_INDEX ) .RATIO_TO_PARENT ) , 3 ) ;
2252 END IF;
2253 IF l_debug_level > 0 THEN
2254 oe_debug_pub.add( 'RATIO TO TOP = '||TO_CHAR ( L_PTO_SHIPMENT_TBL ( L_PTO_INDEX ) .RATIO_TO_TOP_MODEL ) , 3 ) ;
2255 END IF;
2256
2257 l_pto_index := l_pto_shipment_tbl.NEXT(l_pto_index);
2258
2259 END LOOP;
2260
2261 -- Calculate the shipped quantity for non-shippable lines.
2262
2263 l_pto_index := l_pto_shipment_tbl.FIRST;
2264
2265 WHILE l_pto_index IS NOT NULL AND
2266 l_ratio_status = FND_API.G_TRUE
2267 LOOP
2268
2269 IF l_pto_shipment_tbl(l_pto_index).shippable_flag = FND_API.G_FALSE THEN
2270
2271 GOTO SKIP_THE_LINE;
2272
2273 END IF;
2274
2275 -- l_parent_index := nvl(l_pto_shipment_tbl(l_pto_index).link_to_line_id,l_pto_shipment_tbl(l_pto_index).top_model_line_id); --Bug 8795918
2276 -- l_top_index := l_pto_shipment_tbl(l_pto_index).top_model_line_id; -- Bug 8795918
2277 l_parent_index := mod(nvl(l_pto_shipment_tbl(l_pto_index).link_to_line_id,l_pto_shipment_tbl(l_pto_index).top_model_line_id),OE_GLOBALS.G_BINARY_LIMIT);
2278 l_top_index := mod(l_pto_shipment_tbl(l_pto_index).top_model_line_id,OE_GLOBALS.G_BINARY_LIMIT);
2279
2280 l_parent_shipped_quantity := nvl(l_pto_shipment_tbl(l_pto_index).shipped_quantity,0)/l_pto_shipment_tbl(l_pto_index).ratio_to_parent;
2281 IF l_debug_level > 0 THEN
2282 oe_debug_pub.add( 'SHIPPED QUANTITY FOR PARENT : '|| TO_CHAR ( L_PARENT_SHIPPED_QUANTITY ) , 3 ) ;
2283 END IF;
2284
2285 IF l_pto_shipment_tbl(l_parent_index).shipped_quantity IS NOT NULL AND
2286 l_pto_shipment_tbl(l_parent_index).shipped_quantity <> l_parent_shipped_quantity THEN
2287
2288 IF l_debug_level > 0 THEN
2289 oe_debug_pub.add( 'PTO HAS NOT BEEN SHIPPED IN PROPORTION ' , 3 ) ;
2290 END IF;
2291 l_ratio_status := FND_API.G_FALSE;
2292 GOTO END_CALCULATE_SHIPPED_QUANTITY;
2293 ELSE
2294 l_pto_shipment_tbl(l_parent_index).shipped_quantity := l_parent_shipped_quantity;
2295 END IF;
2296
2297 IF l_pto_shipment_tbl(l_parent_index).shipped_quantity <> trunc(l_pto_shipment_tbl(l_parent_index).shipped_quantity) THEN
2298
2299 IF l_debug_level > 0 THEN
2300 oe_debug_pub.add( 'SHIPPED QUANTITY IN DECIMAL '||TO_CHAR ( L_PTO_SHIPMENT_TBL ( L_PARENT_INDEX ) .SHIPPED_QUANTITY ) , 3 ) ;
2301 END IF;
2302 l_ratio_status := FND_API.G_FALSE;
2303 GOTO END_CALCULATE_SHIPPED_QUANTITY;
2304
2305 END IF;
2306
2307 l_top_shipped_quantity := nvl(l_pto_shipment_tbl(l_pto_index).shipped_quantity,0)/l_pto_shipment_tbl(l_pto_index).ratio_to_top_model;
2308 IF l_debug_level > 0 THEN
2309 oe_debug_pub.add( 'SHIPPED QUANTITY FOR TOP MODEL : '|| TO_CHAR ( L_TOP_SHIPPED_QUANTITY ) , 3 ) ;
2310 END IF;
2311
2312 IF l_pto_shipment_tbl(l_top_index).shipped_quantity IS NOT NULL AND
2313 l_pto_shipment_tbl(l_top_index).shipped_quantity <> l_top_shipped_quantity THEN
2314
2315 IF l_debug_level > 0 THEN
2316 oe_debug_pub.add( 'PTO HAS NOT BEEN SHIPPED IN PROPORTION ' , 3 ) ;
2317 END IF;
2318 l_ratio_status := FND_API.G_FALSE;
2319 GOTO END_CALCULATE_SHIPPED_QUANTITY;
2320 ELSE
2321 l_pto_shipment_tbl(l_top_index).shipped_quantity := l_top_shipped_quantity;
2322 END IF;
2323
2324 IF l_pto_shipment_tbl(l_top_index).shipped_quantity <> trunc(l_pto_shipment_tbl(l_top_index).shipped_quantity) THEN
2325
2326 IF l_debug_level > 0 THEN
2327 oe_debug_pub.add( 'SHIPPED QUANTITY IN DECIMAL '||TO_CHAR ( L_PTO_SHIPMENT_TBL ( L_TOP_INDEX ) .SHIPPED_QUANTITY ) , 3 ) ;
2328 END IF;
2329 l_ratio_status := FND_API.G_FALSE;
2330 GOTO END_CALCULATE_SHIPPED_QUANTITY;
2331
2332 END IF;
2333
2334 <<SKIP_THE_LINE>>
2335 l_pto_index := l_pto_shipment_tbl.NEXT(l_pto_index);
2336
2337 END LOOP;
2338
2339 <<END_CALCULATE_SHIPPED_QUANTITY>>
2340
2341 -- If the ratio is not broken populate the out line table.
2342
2343 IF l_ratio_status = FND_API.G_TRUE THEN
2344
2345 -- Assign the shipped quantity if not assigned to any of the lines
2346
2347 l_pto_index := l_pto_shipment_tbl.FIRST;
2348
2349 WHILE l_pto_index IS NOT NULL
2350 LOOP
2351
2352 -- l_parent_index := nvl(l_pto_shipment_tbl(l_pto_index).link_to_line_id,l_pto_shipment_tbl(l_pto_index).top_model_line_id); -- Bug 8795918
2353 -- l_top_index := l_pto_shipment_tbl(l_pto_index).top_model_line_id; -- Bug 8795918
2354 l_parent_index := mod(nvl(l_pto_shipment_tbl(l_pto_index).link_to_line_id,l_pto_shipment_tbl(l_pto_index).top_model_line_id),OE_GLOBALS.G_BINARY_LIMIT);
2355 l_top_index := mod(l_pto_shipment_tbl(l_pto_index).top_model_line_id,OE_GLOBALS.G_BINARY_LIMIT);
2356
2357 IF nvl(l_pto_shipment_tbl(l_pto_index).shipped_quantity,0) = 0 THEN
2358
2359 l_pto_shipment_tbl(l_pto_index).shipped_quantity := nvl(l_pto_shipment_tbl(l_top_index).shipped_quantity,0) * l_pto_shipment_tbl(l_pto_index).ratio_to_top_model;
2360
2361 END IF;
2362
2363 IF l_debug_level > 0 THEN
2364 oe_debug_pub.add( 'SHIPPED QUANTITY : '||TO_CHAR ( L_PTO_SHIPMENT_TBL ( L_PTO_INDEX ) .SHIPPED_QUANTITY ) , 1 ) ;
2365 END IF;
2366
2367 l_pto_index := l_pto_shipment_tbl.NEXT(l_pto_index);
2368 END LOOP;
2369
2370 l_final_index := 0;
2371
2372 FOR l_line_index IN l_line_tbl.FIRST .. l_line_tbl.LAST
2373 LOOP
2374
2375 IF l_line_tbl(l_line_index).ordered_quantity = 0 THEN
2376
2377 IF l_debug_level > 0 THEN
2378 oe_debug_pub.add( 'LINE WITH ZERO QUANTITY : '||L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID||'/'||L_LINE_TBL ( L_LINE_INDEX ) .ITEM_TYPE_CODE , 3 ) ;
2379 END IF;
2380 GOTO END_FINAL_LOOP;
2381 END IF;
2382
2383 l_final_index := l_final_index + 1;
2384 p_x_line_tbl(l_final_index) := l_line_tbl(l_line_index);
2385
2386 -- l_pto_index := l_line_tbl(l_line_index).line_id; -- Bug 8795918
2387 l_pto_index := mod(l_line_tbl(l_line_index).line_id,OE_GLOBALS.G_BINARY_LIMIT);
2388 IF l_debug_level > 0 THEN
2389 oe_debug_pub.add( 'INDEX/LINE_ID/FINAL_INDEX '|| TO_CHAR ( L_LINE_INDEX ) ||'/'||P_X_LINE_TBL ( L_FINAL_INDEX ) .LINE_ID||'/'||TO_CHAR ( L_FINAL_INDEX ) , 3 ) ;
2390 END IF;
2391
2392 IF l_pto_shipment_tbl(l_pto_index).shippable_flag = FND_API.G_FALSE THEN
2393 p_x_line_tbl(l_final_index).shipped_quantity := l_pto_shipment_tbl(l_pto_index).shipped_quantity;
2394 p_x_line_tbl(l_final_index).actual_shipment_date := l_ship_date;
2395 IF l_debug_level > 0 THEN
2396 oe_debug_pub.add( 'SHIPPED QUANTITY ASSIGNED '||P_X_LINE_TBL ( L_FINAL_INDEX ) .ITEM_TYPE_CODE||TO_CHAR ( P_X_LINE_TBL ( L_FINAL_INDEX ) .SHIPPED_QUANTITY ) , 3 ) ;
2397 END IF;
2398 IF l_debug_level > 0 THEN
2399 oe_debug_pub.add( 'SHIPMENT DATE ASSIGNED '||P_X_LINE_TBL ( L_FINAL_INDEX ) .ITEM_TYPE_CODE||TO_CHAR ( P_X_LINE_TBL ( L_FINAL_INDEX ) .ACTUAL_SHIPMENT_DATE , 'DD-MM-YY' ) , 3 ) ;
2400 END IF;
2401 END IF;
2402
2403 << END_FINAL_LOOP >>
2404 NULL;
2405
2406 END LOOP;
2407 /* Added for bug 1952023 */
2408 ELSE
2409
2410 IF l_over_shipped = FND_API.G_TRUE AND
2411 l_under_shipped = FND_API.G_FALSE AND
2412 l_line_shipped = FND_API.G_TRUE THEN
2413
2414 IF l_debug_level > 0 THEN
2415 oe_debug_pub.add( 'ONE OF THE LINE HAS BEEN OVER SHIPPED , MAKE THE MODEL REMNANT '||P_X_LINE_TBL ( 1 ) .TOP_MODEL_LINE_ID , 3 ) ;
2416 END IF;
2417
2418
2419 update oe_order_lines
2420 set model_remnant_flag = 'Y'
2421 where top_model_line_id = p_x_line_tbl(1).top_model_line_id;
2422
2423 IF l_debug_level > 0 THEN
2424 oe_debug_pub.add( 'NOW SET THE REMNANT FLAG FOR ALL THE LINES ' , 3 ) ;
2425 END IF;
2426
2427 FOR l_line_index IN p_x_line_tbl.FIRST .. p_x_line_tbl.LAST
2428 LOOP
2429
2430 IF l_debug_level > 0 THEN
2431 oe_debug_pub.add( 'LINE/ITEM '||P_X_LINE_TBL ( L_LINE_INDEX ) .LINE_ID||'/'||P_X_LINE_TBL ( L_LINE_INDEX ) .ITEM_TYPE_CODE , 3 ) ;
2432 END IF;
2433 p_x_line_tbl(l_line_index).model_remnant_flag := 'Y';
2434
2435 END LOOP;
2436
2437 END IF;
2438 END IF;
2439
2440 --x_line_tbl := l_line_tbl;
2441 x_ratio_status := l_ratio_status;
2442 x_return_status := FND_API.G_RET_STS_SUCCESS;
2443
2444 IF l_debug_level > 0 THEN
2445 oe_debug_pub.add( 'EXITING OE_SHIPPING_INTEGRATION_PVT.GET_PTO_SHIPPED_QUANTITY ' , 1 ) ;
2446 END IF;
2447 EXCEPTION
2448 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2449 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2450
2451 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2452 THEN
2453 OE_MSG_PUB.Add_Exc_Msg
2454 ( G_PKG_NAME,
2455 'Get_PTO_Shipped_Quantity'
2456 );
2457 END IF;
2458 IF g_debug_call > 0 THEN
2459 G_DEBUG_MSG := G_DEBUG_MSG || 'Shp-E-10';
2460 END IF;
2461
2462 WHEN FND_API.G_EXC_ERROR THEN
2463 x_return_status := FND_API.G_RET_STS_ERROR;
2464 IF g_debug_call > 0 THEN
2465 G_DEBUG_MSG := G_DEBUG_MSG || 'Shp-E-11';
2466 END IF;
2467
2468 WHEN OTHERS THEN
2469 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2470
2471 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2472 THEN
2473 OE_MSG_PUB.Add_Exc_Msg
2474 ( G_PKG_NAME,
2475 'Get_PTO_Shipped_Quantity'
2476 );
2477 END IF;
2478
2479 IF l_debug_level > 0 THEN
2480 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 100 ) , 1 ) ;
2481 END IF;
2482 IF g_debug_call > 0 THEN
2483 G_DEBUG_MSG := G_DEBUG_MSG || 'Shp-E-12';
2484 END IF;
2485 NULL;
2486 END Get_PTO_Shipped_Quantity;
2487
2488
2489
2490 /*--------------------------------------------------------------
2491 PROCEDURE Ship_Confirm_PTO_KIT
2492
2493 This procedure is called for ship confirmation PTO models.
2494 It checks if the model is shipped in proprtion or not.
2495
2496 If model is shipped in proportion.:
2497 It sets the shipped quantity on nonshippable lines. It splits the shippble
2498 lines if partailly shipped. Then it logs requests to
2499 progress the wf for shippable lines.
2500
2501 If the model is not shipped in proportion:
2502 it performs nonproportional split of the model if it is not a
2503 remnant already. it then progress the wf for shippble lines.
2504 finally it prrgressed th fulfillment of non-shippable lines
2505 if not in fulfillment set and are at fulfillment activity.
2506
2507 Change record:
2508 bug 2361720: added call to ship_confirm_standard_line if the
2509 shipped not in proportion branch, whcih will complete the ATO
2510 model's wait for cto inturn.
2511 ---------------------------------------------------------------*/
2512 PROCEDURE Ship_Confirm_PTO_KIT
2513 ( p_top_model_line_id IN NUMBER
2514 ,x_return_status OUT NOCOPY VARCHAR2)
2515
2516 IS
2517 l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
2518 l_split_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
2519 l_split_index NUMBER :=0;
2520 l_pto_index NUMBER;
2521 l_ratio_status VARCHAR2(1) := FND_API.G_TRUE;
2522 l_ship_tolerance_below NUMBER;
2523 l_ship_tolerance_above NUMBER;
2524 l_update_tolerance_value NUMBER := 0 ;
2525 l_x_result_out VARCHAR2(30);
2526 l_return_status VARCHAR2(1);
2527 l_msg_count NUMBER;
2528 l_msg_data VARCHAR2(2000);
2529 l_result_code VARCHAR2(30);
2530 l_unreserve_quantity NUMBER;
2531 l_activity_status VARCHAR2(8);
2532 l_activity_result VARCHAR2(30);
2533 l_activity_id NUMBER;
2534 l_fulfillment_activity VARCHAR2(30);
2535 l_item_key VARCHAR2(240);
2536 l_fulfill_tbl OE_Order_Pub.Line_Tbl_Type;
2537 l_fulfill_index NUMBER := 0 ;
2538 l_fulfillment_set_flag VARCHAR2(1);
2539
2540 -- Variables to call process order to update the shipped quantity for
2541 -- non shippable lines.
2542 l_upd_tbl_index NUMBER := 0;
2543 l_update_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
2544 l_control_rec OE_GLOBALS.Control_Rec_Type;
2545 l_top_model_index NUMBER;
2546 l_ato_line_index NUMBER := 0;
2547 l_set_recursion VARCHAR2(1) := 'N';
2548
2549 --
2550 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2551 --
2552 BEGIN
2553
2554 IF l_debug_level > 0 THEN
2555 oe_debug_pub.add( 'ENTERING SHIP_CONFIRM_PTO_KIT '|| P_TOP_MODEL_LINE_ID , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
2556 END IF;
2557
2558 -- Call get PTO shipped quantity to get the shipment status and shipped
2559 -- quantities for MODEL and CLASS.
2560
2561
2562 Get_PTO_Shipped_Quantity
2563 (p_top_model_line_id => p_top_model_line_id
2564 ,p_x_line_tbl => l_line_tbl
2565 ,x_ratio_status => l_ratio_status
2566 ,x_return_status => l_return_status );
2567
2568 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2569 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2570 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2571 RAISE FND_API.G_EXC_ERROR;
2572 END IF;
2573
2574 -- Get the top model line id index.
2575
2576 FOR l_pto_index IN 1 .. l_line_tbl.count
2577 LOOP
2578
2579 IF l_debug_level > 0 THEN
2580 oe_debug_pub.add( 'INDEX/LINE_ID/TOP_MODEL_LINE_ID :'|| TO_CHAR ( L_PTO_INDEX ) || TO_CHAR ( L_LINE_TBL ( L_PTO_INDEX ) .LINE_ID ) || TO_CHAR ( L_LINE_TBL ( L_PTO_INDEX ) .TOP_MODEL_LINE_ID ) , 3 ) ;
2581 END IF;
2582
2583 IF l_line_tbl(l_pto_index).line_id =
2584 l_line_tbl(l_pto_index).top_model_line_id THEN
2585 l_top_model_index := l_pto_index;
2586 END IF;
2587
2588 IF l_line_tbl(l_pto_index).line_id = l_line_tbl(l_pto_index).ato_line_id
2589 THEN
2590 l_ato_line_index := l_pto_index;
2591 END IF;
2592
2593
2594
2595 IF nvl(l_line_tbl(l_pto_index).ship_tolerance_below,0) <> 0 AND
2596 l_line_tbl(l_pto_index).shippable_flag = 'Y' THEN
2597 l_update_tolerance_value := l_line_tbl(l_pto_index).ship_tolerance_below;
2598 ELSE
2599 l_update_tolerance_value := 0;
2600 END IF;
2601
2602 END LOOP;
2603
2604 IF l_debug_level > 0 THEN
2605 oe_debug_pub.add( 'TOP MODEL INDEX : '||L_TOP_MODEL_INDEX , 3 ) ;
2606 END IF;
2607 IF l_debug_level > 0 THEN
2608 oe_debug_pub.add( 'ATO LINE INDEX : '||L_ATO_LINE_INDEX , 3 ) ;
2609 END IF;
2610 IF l_debug_level > 0 THEN
2611 oe_debug_pub.add( 'NEW TOLERANCE VALUE : '||L_UPDATE_TOLERANCE_VALUE , 3 ) ;
2612 END IF;
2613
2614
2615 -- Check for shipment status when the ratio is not broken and the ship
2616 -- tolerances will be honoured.
2617
2618 IF l_ratio_status = FND_API.G_TRUE THEN
2619
2620 -- Loop to Update the shipped quantity of non shippable lines by calling
2621 -- process_order.
2622
2623 FOR l_pto_index IN l_line_tbl.FIRST .. l_line_tbl.LAST
2624 LOOP
2625 IF l_debug_level > 0 THEN
2626 oe_debug_pub.add( 'INSIDE THE LOOP FOR NON SHIPPABLE LINE : '|| TO_CHAR ( L_LINE_TBL ( L_PTO_INDEX ) .LINE_ID ) ||'/'|| L_LINE_TBL ( L_PTO_INDEX ) .SHIPPED_QUANTITY , 3 ) ;
2627 END IF;
2628
2629 IF l_debug_level > 0 THEN
2630 oe_debug_pub.add( 'SHIPPABLE FLAG/OPEN FLAG : '|| L_LINE_TBL ( L_PTO_INDEX ) .SHIPPABLE_FLAG||'/'|| L_LINE_TBL ( L_PTO_INDEX ) .OPEN_FLAG , 3 ) ;
2631 END IF;
2632
2633 IF nvl(l_line_tbl(l_pto_index).shippable_flag,'N') <> 'Y' THEN
2634
2635 l_upd_tbl_index := l_upd_tbl_index + 1;
2636
2637 IF l_debug_level > 0 THEN
2638 oe_debug_pub.add( 'NONSHIPPABLE INDEX UPD TABLE '|| L_UPD_TBL_INDEX , 3 ) ;
2639 END IF;
2640
2641 l_update_line_tbl(l_upd_tbl_index) := l_line_tbl(l_pto_index);
2642 l_update_line_tbl(l_upd_tbl_index).operation
2643 := OE_GLOBALS.G_OPR_UPDATE;
2644
2645 IF l_line_tbl(l_top_model_index).ship_tolerance_below <>
2646 nvl(l_line_tbl(l_pto_index).ship_tolerance_below,0) AND
2647 l_line_tbl(l_top_model_index).line_id =
2648 l_line_tbl(l_pto_index).line_id AND
2649 nvl(l_line_tbl(l_pto_index).ship_tolerance_below,0) <> 0
2650 THEN
2651
2652 l_update_line_tbl(l_upd_tbl_index).ship_tolerance_below
2653 := l_update_tolerance_value;
2654
2655 IF l_debug_level > 0 THEN
2656 oe_debug_pub.add( 'NEW TOLERANCE = '|| L_UPDATE_LINE_TBL ( L_UPD_TBL_INDEX ) .SHIP_TOLERANCE_BELOW , 3 ) ;
2657 END IF;
2658
2659 END IF;
2660
2661 IF l_debug_level > 0 THEN
2662 oe_debug_pub.add( 'SHIPPED QUANTITY = '|| TO_CHAR ( L_UPDATE_LINE_TBL ( L_UPD_TBL_INDEX ) .SHIPPED_QUANTITY ) , 3 ) ;
2663 END IF;
2664
2665 END IF;
2666
2667 END LOOP;
2668
2669
2670 IF l_debug_level > 0 THEN
2671 oe_debug_pub.add( 'CALLING PO TO UPDATE SHIPPED QTY FOR NON SHIPPABLE LINES' , 3 ) ;
2672 END IF;
2673
2674 IF g_debug_call > 0 THEN
2675 G_DEBUG_MSG := G_DEBUG_MSG || 'Shp-8-6';
2676 END IF;
2677
2678 l_control_rec := OE_GLOBALS.G_MISS_CONTROL_REC;
2679 l_control_rec.validate_entity := FALSE;
2680 l_control_rec.check_security := FALSE;
2681
2682 IF OE_GLOBALS.G_RECURSION_MODE = 'Y' THEN
2683 l_set_recursion := 'N';
2684 ELSE
2685 l_set_recursion := 'Y';
2686 -- OE_GLOBALS.G_RECURSION_MODE := 'Y';
2687 END IF;
2688
2689 IF g_debug_call > 0 THEN
2690 G_DEBUG_MSG := G_DEBUG_MSG || '87,';
2691 END IF;
2692
2693 Call_Process_Order
2694 ( p_line_tbl => l_update_line_tbl,
2695 p_control_rec => l_control_rec,
2696 x_return_status => l_return_status );
2697
2698 IF l_set_recursion = 'Y' THEN
2699 l_set_recursion := 'N';
2700 -- OE_GLOBALS.G_RECURSION_MODE := 'N';
2701 END IF;
2702
2703 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2704 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2705 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2706 RAISE FND_API.G_EXC_ERROR;
2707 END IF;
2708 IF g_debug_call > 0 THEN
2709 G_DEBUG_MSG := G_DEBUG_MSG || '88,';
2710 END IF;
2711
2712 IF l_line_tbl(l_top_model_index).ordered_quantity =
2713 l_line_tbl(l_top_model_index).shipped_quantity THEN
2714
2715 IF l_debug_level > 0 THEN
2716 oe_debug_pub.add( 'FULLY SHIPPED , SHIPMENT RATIO IS NOT BROKEN' , 3 ) ;
2717 END IF;
2718 l_x_result_out := OE_GLOBALS.G_FULLY_SHIPPED;
2719
2720 ELSE
2721 IF l_debug_level > 0 THEN
2722 oe_debug_pub.add( 'NOT FULLY SHIPPED , SHIPMENT RATIO IS NOT BROKEN' , 3 ) ;
2723 END IF;
2724 -- Check the shipment status
2725
2726 l_ship_tolerance_below
2727 := l_line_tbl(l_top_model_index).ship_tolerance_below;
2728 l_ship_tolerance_above
2729 := l_line_tbl(l_top_model_index).ship_tolerance_above;
2730 -- Check the shipment status
2731
2732 IF l_debug_level > 0 THEN
2733 oe_debug_pub.add( 'CHECKING THE SHIPMENT STATUS FOR LINE : '|| L_LINE_TBL ( L_TOP_MODEL_INDEX ) .LINE_ID || ' ' || L_LINE_TBL ( L_TOP_MODEL_INDEX ) .ITEM_TYPE_CODE , 3 ) ;
2734 END IF;
2735
2736 Check_Shipment_Line
2737 (p_line_rec => l_line_tbl(l_top_model_index),
2738 x_result_out => l_x_result_out);
2739
2740 END IF;
2741
2742
2743 -- Call the split API if it is a partial shipment.
2744
2745 IF l_x_result_out = OE_GLOBALS.G_PARTIALLY_SHIPPED AND
2746 l_line_tbl(l_top_model_index).ordered_quantity >
2747 l_line_tbl(l_top_model_index).shipped_quantity THEN
2748
2749 IF l_debug_level > 0 THEN
2750 oe_debug_pub.add( 'CALLING OE_ORDER_PVT.PROCESS_ORDER FOR MODEL LINE' , 3 ) ;
2751 END IF;
2752
2753 -- Assign the first record of the table for process order for update
2754 -- of the ordered quantity.
2755
2756 l_split_line_tbl(1) := l_line_tbl(l_top_model_index);
2757 l_split_line_tbl(1).ordered_quantity
2758 := l_line_tbl(l_top_model_index).shipped_quantity;
2759 l_split_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
2760 l_split_line_tbl(1).split_action_code := 'SPLIT';
2761 l_split_line_tbl(1).split_by := 'SYSTEM';
2762
2763 IF g_debug_call > 0 THEN
2764 G_DEBUG_MSG := G_DEBUG_MSG || '80,';
2765 END IF;
2766 -- Assign the second record of the table for process order for
2767 -- create of new line.
2768
2769 l_split_line_tbl(2) := OE_ORDER_PUB.G_MISS_LINE_REC;
2770 l_split_line_tbl(2).split_from_line_id
2771 := l_line_tbl(l_top_model_index).line_id;
2772 l_split_line_tbl(2).ordered_quantity
2773 := l_line_tbl(l_top_model_index).ordered_quantity -
2774 l_line_tbl(l_top_model_index).shipped_quantity;
2775 l_split_line_tbl(2).operation := OE_GLOBALS.G_OPR_CREATE;
2776 l_split_line_tbl(2).split_by := 'SYSTEM';
2777
2778 IF l_debug_level > 0 THEN
2779 oe_debug_pub.add( 'SPLIT FROM LINE ID : '|| TO_CHAR ( L_SPLIT_LINE_TBL ( 2 ) .SPLIT_FROM_LINE_ID ) , 3 ) ;
2780 END IF;
2781
2782 IF l_debug_level > 0 THEN
2783 oe_debug_pub.add( 'ORIGINAL ORDERED QUANTITY : '|| TO_CHAR ( L_LINE_TBL ( 1 ) .ORDERED_QUANTITY ) , 3 ) ;
2784 END IF;
2785
2786 IF l_debug_level > 0 THEN
2787 oe_debug_pub.add( 'ORDERED QUANTITY OLD LINE : '|| TO_CHAR ( L_SPLIT_LINE_TBL ( 1 ) .ORDERED_QUANTITY ) , 3 ) ;
2788 END IF;
2789
2790 IF l_debug_level > 0 THEN
2791 oe_debug_pub.add( 'ORDERED QUANTITY NEW LINE : '|| TO_CHAR ( L_SPLIT_LINE_TBL ( 2 ) .ORDERED_QUANTITY ) , 3 ) ;
2792 END IF;
2793
2794
2795 l_control_rec := OE_GLOBALS.G_MISS_CONTROL_REC;
2796 l_control_rec.controlled_operation := TRUE;
2797 l_control_rec.check_security := FALSE;
2798 l_control_rec.change_attributes := TRUE;
2799 l_control_rec.default_attributes := TRUE;
2800 l_control_rec.clear_dependents := TRUE;
2801
2802 IF OE_GLOBALS.G_RECURSION_MODE = 'Y' THEN
2803 l_set_recursion := 'N';
2804 ELSE
2805 l_set_recursion := 'Y';
2806 -- OE_GLOBALS.G_RECURSION_MODE := 'Y';
2807 END IF;
2808
2809 IF g_debug_call > 0 THEN
2810 G_DEBUG_MSG := G_DEBUG_MSG || '81,';
2811 END IF;
2812
2813 Call_Process_Order
2814 ( p_line_tbl => l_split_line_tbl,
2815 p_control_rec => l_control_rec,
2816 x_return_status => l_return_status);
2817
2818 IF l_set_recursion = 'Y' THEN
2819 l_set_recursion := 'N';
2820 -- OE_GLOBALS.G_RECURSION_MODE := 'N';
2821 END IF;
2822
2823 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2825 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2826 RAISE FND_API.G_EXC_ERROR;
2827 END IF;
2828
2829 IF l_debug_level > 0 THEN
2830 oe_debug_pub.add( 'RET STS FROM PROCESS ORDER: '||L_RETURN_STATUS , 3 ) ;
2831 END IF;
2832 END IF;
2833
2834 ---------------------- split done -------------------------
2835
2836
2837 -- Loop to ship confirm the shippable lines of a PTO/KIT.
2838 FOR l_pto_index IN l_line_tbl.FIRST .. l_line_tbl.LAST
2839 LOOP
2840
2841 IF l_debug_level > 0 THEN
2842 oe_debug_pub.add( 'INSIDE THE CONFIRM SHIPMENT LOOP : LINE ID : '|| TO_CHAR ( L_LINE_TBL ( L_PTO_INDEX ) .LINE_ID ) , 3 ) ;
2843 END IF;
2844 IF l_debug_level > 0 THEN
2845 oe_debug_pub.add( 'INSIDE THE CONFIRM SHIPMENT LOOP : ITEM TYPE : '|| L_LINE_TBL ( L_PTO_INDEX ) .ITEM_TYPE_CODE , 3 ) ;
2846 END IF;
2847
2848 IF nvl(l_line_tbl(l_pto_index).shippable_flag,'N') = 'Y' THEN
2849
2850 IF l_debug_level > 0 THEN
2851 oe_debug_pub.add( 'INSIDE THE IF FOR SHIPPABLE LINE : LINE ID : '|| TO_CHAR ( L_LINE_TBL ( L_PTO_INDEX ) .LINE_ID ) , 3 ) ;
2852 END IF;
2853 IF l_debug_level > 0 THEN
2854 oe_debug_pub.add( 'INSIDE THE IF FOR SHIPPABLE LINE : ITEM TYPE : '|| L_LINE_TBL ( L_PTO_INDEX ) .ITEM_TYPE_CODE , 3 ) ;
2855 END IF;
2856
2857 Ship_Confirm_Standard_Line
2858 ( p_line_rec => l_line_tbl(l_pto_index),
2859 p_shipment_status => l_x_result_out,
2860 x_return_status => x_return_status );
2861
2862
2863 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2864 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2865 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2866 RAISE FND_API.G_EXC_ERROR;
2867 END IF;
2868
2869 END IF;
2870
2871 END LOOP;
2872
2873 ------------------ ratio_status = TRUE done ---------------
2874
2875 -- It is over/under shipepd with shipment ratio broken. The lines
2876 -- will split if under ship. The shipment activity will be
2877 -- completed for lines which have been overshipped.
2878
2879 IF g_debug_call > 0 THEN
2880 G_DEBUG_MSG := G_DEBUG_MSG || '84,';
2881 END IF;
2882
2883 ELSIF l_ratio_status = FND_API.G_FALSE THEN
2884
2885 IF l_debug_level > 0 THEN
2886 oe_debug_pub.add( 'NOT SHIPPED , SHIPMENT RATIO IS BROKEN : ' , 3 ) ;
2887 END IF;
2888
2889 -- Call process order to update the shipping tolerances to 0
2890 -- if there is some value in tolerance
2891
2892 IF nvl(l_line_tbl(l_top_model_index).ship_tolerance_below,0) <> 0 OR
2893 nvl(l_line_tbl(l_top_model_index).ship_tolerance_above,0) <> 0 THEN
2894
2895
2896 UPDATE OE_ORDER_LINES_ALL
2897 SET SHIP_TOLERANCE_BELOW = 0,
2898 SHIP_TOLERANCE_ABOVE = 0
2899 WHERE TOP_MODEL_LINE_ID = l_line_tbl(l_top_model_index).line_id;
2900
2901 END IF;
2902
2903 FOR l_pto_index IN l_line_tbl.FIRST .. l_line_tbl.LAST
2904 LOOP
2905
2906 IF l_debug_level > 0 THEN
2907 oe_debug_pub.add( 'INSIDE THE RATIO BROKEN LOOP : LINE ID : '|| TO_CHAR ( L_LINE_TBL ( L_PTO_INDEX ) .LINE_ID ) , 3 ) ;
2908 END IF;
2909 IF l_debug_level > 0 THEN
2910 oe_debug_pub.add( 'INSIDE THE RATIO BROKEN LOOP : ITEM TYPE : '|| L_LINE_TBL ( L_PTO_INDEX ) .ITEM_TYPE_CODE , 3 ) ;
2911 END IF;
2912
2913 /* Added for bug 1952023 */
2914
2915
2916 IF nvl(l_line_tbl(l_top_model_index).model_remnant_flag,'N') = 'N'
2917 THEN
2918 IF nvl(l_line_tbl(l_pto_index).shippable_flag,'N') = 'Y' THEN
2919
2920 IF l_debug_level > 0 THEN
2921 oe_debug_pub.add( 'INSIDE THE IF FOR SHIPPABLE LINE : LINE ID : '|| TO_CHAR ( L_LINE_TBL ( L_PTO_INDEX ) .LINE_ID ) , 3 ) ;
2922 END IF;
2923
2924 IF l_debug_level > 0 THEN
2925 oe_debug_pub.add( 'INSIDE THE IF FOR SHIPPABLE LINE : ITEM TYPE : '|| L_LINE_TBL ( L_PTO_INDEX ) .ITEM_TYPE_CODE , 3 ) ;
2926 END IF;
2927
2928 IF nvl(l_line_tbl(l_pto_index).shipped_quantity,0) <> 0 THEN
2929 IF l_debug_level > 0 THEN
2930 oe_debug_pub.add( 'THE LINE IS UNDERSHIPPED : SPLIT LINE'|| TO_CHAR ( L_LINE_TBL ( L_PTO_INDEX ) .LINE_ID ) , 3 ) ;
2931 END IF;
2932
2933 l_split_index := l_split_index + 1;
2934 l_split_line_tbl(l_split_index) := l_line_tbl(l_pto_index);
2935 l_split_line_tbl(l_split_index).ordered_quantity
2936 := l_line_tbl(l_pto_index).ordered_quantity -
2937 l_line_tbl(l_pto_index).shipped_quantity;
2938 l_split_line_tbl(l_split_index).shipped_quantity
2939 := l_line_tbl(l_pto_index).shipped_quantity;
2940 l_split_line_tbl(l_split_index).operation
2941 := OE_GLOBALS.G_OPR_UPDATE;
2942 l_split_line_tbl(l_split_index).split_by := 'SYSTEM';
2943 l_split_line_tbl(l_split_index).split_action_code := 'SPLIT';
2944
2945 IF l_debug_level > 0 THEN
2946 oe_debug_pub.add( 'NEW ORDERED QUANTITY : '|| TO_CHAR ( L_SPLIT_LINE_TBL ( L_SPLIT_INDEX ) .ORDERED_QUANTITY ) , 3 ) ;
2947 END IF;
2948 IF l_debug_level > 0 THEN
2949 oe_debug_pub.add( 'SHIPPED QUANTITY : '|| TO_CHAR ( L_SPLIT_LINE_TBL ( L_SPLIT_INDEX ) .SHIPPED_QUANTITY ) , 3 ) ;
2950 END IF;
2951 -- Call the process order to split the line
2952 END IF;
2953 END IF; -- Shippable flag = Y
2954
2955 ELSE
2956 IF l_debug_level > 0 THEN
2957 oe_debug_pub.add( 'REMNANT_MODEL ' , 3 ) ;
2958 END IF;
2959 l_split_index := l_split_index + 1;
2960 l_split_line_tbl(l_split_index) := l_line_tbl(l_pto_index);
2961 END IF; -- Model Remnant
2962
2963 END LOOP;
2964
2965 -- Call un-proportional split if any of the lines has under shipped
2966
2967 IF l_split_line_tbl.count > 0 THEN
2968
2969 IF nvl(l_line_tbl(l_top_model_index).model_remnant_flag,'N') = 'N'
2970 THEN
2971 IF l_debug_level > 0 THEN
2972 oe_debug_pub.add( 'CALLING NON PROPORTIONAL SPLIT' , 3 ) ;
2973 END IF;
2974
2975 IF g_debug_call > 0 THEN
2976 G_DEBUG_MSG := G_DEBUG_MSG || '88,';
2977 END IF;
2978
2979 oe_split_util.cascade_non_proportional_Split
2980 (p_x_line_tbl => l_split_line_tbl,
2981 x_return_status => l_return_status);
2982
2983 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2984 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2985 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2986 RAISE FND_API.G_EXC_ERROR;
2987 END IF;
2988
2989 IF l_debug_level > 0 THEN
2990 oe_debug_pub.add( 'RET STS FROM NON PRO SPLIT/'||L_RETURN_STATUS , 3 ) ;
2991 END IF;
2992
2993 IF l_debug_level > 0 THEN
2994 oe_debug_pub.add( 'NUMBER OF ROWS RETURNED : '|| TO_CHAR ( L_SPLIT_LINE_TBL.COUNT ) , 3 ) ;
2995 END IF;
2996 END IF; -- Remnant flag
2997 IF g_debug_call > 0 THEN
2998 G_DEBUG_MSG := G_DEBUG_MSG || '89';
2999 END IF;
3000
3001 -- Complete the SHIP_LINE work flow activity for shippable lines.
3002 -- Complete the FULFILL_LINE work flow activity for non shippable
3003 -- lines if the fulfillment activity is SHIP_LINE or no fulfillment
3004 -- activity.
3005
3006 FOR l_split_index IN 1 .. l_split_line_tbl.count
3007 LOOP
3008
3009 l_fulfillment_set_flag :=
3010 OE_Line_Fullfill.Is_Part_Of_Fulfillment_Set
3011 (l_split_line_tbl(l_split_index).line_id);
3012
3013 IF l_debug_level > 0 THEN
3014 oe_debug_pub.add( 'PART FULFILLMENT SET: '||L_FULFILLMENT_SET_FLAG , 3 ) ;
3015 END IF;
3016
3017
3018 IF nvl(l_split_line_tbl(l_split_index).shippable_flag,'N') = 'Y' AND
3019 nvl(l_split_line_tbl(l_split_index).shipped_quantity,0) <> 0
3020 THEN
3021
3022 -- check if line is already ship confirmed.
3023
3024 l_item_key := to_char(l_split_line_tbl(l_split_index).line_id);
3025
3026 IF l_debug_level > 0 THEN
3027 oe_debug_pub.add('CALLING GET ACTIVITY RESULT FOR : '
3028 || L_ITEM_KEY||'/'||'SHIP_LINE' , 3 ) ;
3029 END IF;
3030
3031 OE_LINE_FULLFILL.Get_Activity_Result
3032 (p_item_type => OE_GLOBALS.G_WFI_LIN
3033 ,p_item_key => l_item_key
3034 ,p_activity_name => 'SHIP_LINE'
3035 ,x_return_status => l_return_status
3036 ,x_activity_result => l_activity_result
3037 ,x_activity_status_code => l_activity_status
3038 ,x_activity_id => l_activity_id );
3039
3040 IF l_activity_status = 'NOTIFIED' THEN
3041
3042 IF l_debug_level > 0 THEN
3043 oe_debug_pub.add( 'CALLING SHIP_CONFIRM_STANDARD_LINE '
3044 || L_SPLIT_LINE_TBL ( L_SPLIT_INDEX ) .LINE_ID , 3 ) ;
3045 END IF;
3046
3047 Ship_Confirm_Standard_Line
3048 ( p_line_rec => l_split_line_tbl(l_split_index),
3049 p_shipment_status => l_x_result_out,
3050 x_return_status => x_return_status );
3051
3052 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3053 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3054 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3055 RAISE FND_API.G_EXC_ERROR;
3056 END IF;
3057
3058 END IF;
3059 ---- non shippable lines not in fulfillment set start ---------
3060
3061 ELSIF nvl(l_split_line_tbl(l_split_index).shippable_flag,'N')= 'N' AND
3062 nvl(l_split_line_tbl(l_split_index).model_remnant_flag,'N') = 'Y'
3063 AND l_fulfillment_set_flag = FND_API.G_FALSE THEN
3064
3065 l_item_key := to_char(l_split_line_tbl(l_split_index).line_id);
3066
3067 IF l_debug_level > 0 THEN
3068 oe_debug_pub.add( 'CALLING GET ACTIVITY RESULT FOR : '|| L_ITEM_KEY||'/'||'FULFILL_LINE' , 3 ) ;
3069 END IF;
3070
3071 OE_LINE_FULLFILL.Get_Activity_Result
3072 (p_item_type => OE_GLOBALS.G_WFI_LIN
3073 ,p_item_key => l_item_key
3074 ,p_activity_name => 'FULFILL_LINE'
3075 ,x_return_status => l_return_status
3076 ,x_activity_result => l_activity_result
3077 ,x_activity_status_code => l_activity_status
3078 ,x_activity_id => l_activity_id );
3079
3080 IF l_debug_level > 0 THEN
3081 oe_debug_pub.add( 'RET STS FROM GET ACT RESULT: '||L_RETURN_STATUS , 3 ) ;
3082 END IF;
3083 IF g_debug_call > 0 THEN
3084 G_DEBUG_MSG := G_DEBUG_MSG || '89';
3085 END IF;
3086 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3087 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3088 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3089 IF l_debug_level > 0 THEN
3090 oe_debug_pub.add( 'LINE IS NOT AT FULFILLMENT ACTIVITY : '|| TO_CHAR ( L_SPLIT_LINE_TBL ( L_SPLIT_INDEX ) .LINE_ID ) , 3 ) ;
3091 END IF;
3092 ELSE
3093 IF l_debug_level > 0 THEN
3094 oe_debug_pub.add( 'LINE IS AT FULFILLMENT ACTIVITY : '|| TO_CHAR ( L_SPLIT_LINE_TBL ( L_SPLIT_INDEX ) .LINE_ID ) , 3 ) ;
3095 END IF;
3096
3097 IF l_debug_level > 0 THEN
3098 oe_debug_pub.add( 'CALLING GET FULFILLMENT ACTIVITY ' , 3 ) ;
3099 END IF;
3100
3101 IF g_debug_call > 0 THEN
3102 G_DEBUG_MSG := G_DEBUG_MSG || '90';
3103 END IF;
3104
3105 OE_LINE_FULLFILL.Get_Fulfillment_Activity
3106 (p_item_key => l_item_key,
3107 p_activity_id => l_activity_id,
3108 x_fulfillment_activity => l_fulfillment_activity,
3109 x_return_status => l_return_status);
3110
3111 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3113 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3114 RAISE FND_API.G_EXC_ERROR;
3115 END IF;
3116
3117 IF l_debug_level > 0 THEN
3118 oe_debug_pub.add( 'FULFILLMENT ACT : '||L_FULFILLMENT_ACTIVITY , 3 ) ;
3119 END IF;
3120
3121 IF l_fulfillment_activity = 'NO_ACTIVITY' OR
3122 l_fulfillment_activity = 'SHIP_LINE' THEN
3123
3124 l_fulfill_index := l_fulfill_index + 1;
3125 l_fulfill_tbl(l_fulfill_index) := OE_Order_PUB.G_MISS_LINE_REC;
3126 l_fulfill_tbl(l_fulfill_index).line_id
3127 := l_split_line_tbl(l_split_index).line_id;
3128 l_fulfill_tbl(l_fulfill_index).fulfilled_flag := 'Y';
3129 l_fulfill_tbl(l_fulfill_index).fulfillment_date := SYSDATE;
3130 l_fulfill_tbl(l_fulfill_index).fulfilled_quantity
3131 := l_split_line_tbl(l_split_index).ordered_quantity;
3132 l_fulfill_tbl(l_fulfill_index).header_id
3133 := l_split_line_tbl(l_split_index).header_id;
3134 l_fulfill_tbl(l_fulfill_index).actual_shipment_date
3135 :=l_split_line_tbl(l_split_index).actual_shipment_date;
3136 l_fulfill_tbl(l_fulfill_index).order_firmed_date
3137 :=l_split_line_tbl(l_split_index).order_firmed_date;
3138 --BSA changes for AFD
3139 l_fulfill_tbl(l_fulfill_index).blanket_number
3140 :=l_split_line_tbl(l_split_index).blanket_number;
3141 l_fulfill_tbl(l_fulfill_index).blanket_line_number
3142 :=l_split_line_tbl(l_split_index).blanket_line_number;
3143 l_fulfill_tbl(l_fulfill_index).blanket_version_number
3144 :=l_split_line_tbl(l_split_index).blanket_version_number;
3145 --BSA changes for AFD
3146
3147 l_fulfill_tbl(l_fulfill_index).operation
3148 := OE_GLOBALS.G_OPR_UPDATE;
3149
3150 IF l_debug_level > 0 THEN
3151 oe_debug_pub.add( 'FULFILL INDEX : '||TO_CHAR ( L_FULFILL_INDEX ) , 3 ) ;
3152 END IF;
3153 IF l_debug_level > 0 THEN
3154 oe_debug_pub.add( 'FULFILLED FLAG : '|| L_FULFILL_TBL ( L_FULFILL_INDEX ) .FULFILLED_FLAG , 3 ) ;
3155 END IF;
3156 IF l_debug_level > 0 THEN
3157 oe_debug_pub.add( 'FULFILLED QUANTITY : '|| TO_CHAR ( L_FULFILL_TBL ( L_FULFILL_INDEX ) .FULFILLED_QUANTITY ) , 3 ) ;
3158 END IF;
3159
3160 END IF; -- no activity
3161
3162 END IF; -- line at fulfillment
3163
3164 END IF; -- big if shippable etc.
3165
3166 END LOOP; -- split line tbl has rows
3167
3168 --------ship confirm done, call to fullfill strats -------
3169
3170 IF l_fulfill_index <> 0 THEN
3171
3172 IF l_debug_level > 0 THEN
3173 oe_debug_pub.add( 'CALLING FULFILL LINE TABLE : '|| TO_CHAR ( L_FULFILL_INDEX ) , 3 ) ;
3174 END IF;
3175 IF g_debug_call > 0 THEN
3176 G_DEBUG_MSG := G_DEBUG_MSG || '91';
3177 END IF;
3178 OE_Line_Fullfill.Fulfill_Line
3179 (p_line_tbl => l_fulfill_tbl,
3180 p_mode => 'TABLE',
3181 p_fulfillment_type => 'No Activity',
3182 p_fulfillment_activity => 'NO_ACTIVITY',
3183 x_return_status => l_return_status);
3184
3185 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3187 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3188 RAISE FND_API.G_EXC_ERROR;
3189 END IF;
3190
3191 FOR l_fulfill_index IN 1 .. l_fulfill_tbl.count
3192 LOOP
3193 IF l_debug_level > 0 THEN
3194 oe_debug_pub.add( 'CALLING FLOW STATUS API ' , 3 ) ;
3195 END IF;
3196
3197 OE_Order_WF_Util.Update_Flow_Status_Code
3198 (p_header_id => l_fulfill_tbl(l_fulfill_index).header_id,
3199 p_line_id => l_fulfill_tbl(l_fulfill_index).line_id,
3200 p_flow_status_code => 'FULFILLED',
3201 x_return_status => l_return_status);
3202
3203 IF l_debug_level > 0 THEN
3204 oe_debug_pub.add( 'RET STS FROM FLOW STATUS API '||L_RETURN_STATUS , 3 ) ;
3205 END IF;
3206
3207 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3208 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3209 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3210 RAISE FND_API.G_EXC_ERROR;
3211 END IF;
3212
3213 l_item_key := to_char(l_fulfill_tbl(l_fulfill_index).line_id);
3214
3215 -- 1739574 Log a delayed request for Complete Activity
3216 IF l_debug_level > 0 THEN
3217 oe_debug_pub.add( 'COMPLETE ACT :LOGGING REQUEST '|| L_ITEM_KEY , 3 ) ;
3218 END IF;
3219
3220 OE_Delayed_Requests_Pvt.Log_Request
3221 ( p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
3222 p_entity_id => l_fulfill_tbl(l_fulfill_index).line_id,
3223 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
3224 p_requesting_entity_id => l_fulfill_tbl(l_fulfill_index).line_id,
3225 p_request_type => OE_GLOBALS.G_COMPLETE_ACTIVITY,
3226 p_param1 => OE_GLOBALS.G_WFI_LIN,
3227 p_param2 => l_item_key,
3228 p_param3 => 'FULFILL_LINE',
3229 p_param4 => OE_GLOBALS.G_WFR_COMPLETE,
3230 x_return_status => l_return_status);
3231
3232 IF l_debug_level > 0 THEN
3233 oe_debug_pub.add( 'FULFILL ASSOCIATED SERVICE LINES '|| L_FULFILL_TBL ( L_FULFILL_INDEX ) .LINE_ID , 3 ) ;
3234 END IF;
3235
3236 OE_LINE_FULLFILL.Fulfill_Service_Lines
3237 (p_line_id => l_fulfill_tbl(l_fulfill_index).line_id,
3238 p_header_id => l_fulfill_tbl(l_fulfill_index).header_id,
3239 x_return_status => l_return_status);
3240
3241 IF l_debug_level > 0 THEN
3242 oe_debug_pub.add( 'RET STS FULFILL SERVICE '||L_RETURN_STATUS , 3 ) ;
3243 END IF;
3244
3245 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3247 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3248 RAISE FND_API.G_EXC_ERROR;
3249 END IF;
3250
3251 END LOOP; -- fulfill table has rows.
3252
3253 END IF; -- fullfill index <> 0
3254
3255 END IF; --split count more than 0
3256
3257 END IF;
3258
3259 IF l_debug_level > 0 THEN
3260 oe_debug_pub.add( 'EXITING SHIP_CONFIRM_PTO_KIT : '||X_RETURN_STATUS , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
3261 END IF;
3262
3263 EXCEPTION
3264 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3266
3267 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3268 THEN
3269 OE_MSG_PUB.Add_Exc_Msg
3270 ( G_PKG_NAME
3271 , 'Ship_Confirm_PTO_KIT'
3272 );
3273 END IF;
3274
3275 WHEN FND_API.G_EXC_ERROR THEN
3276 x_return_status := FND_API.G_RET_STS_ERROR;
3277
3278 WHEN OTHERS THEN
3279 IF l_debug_level > 0 THEN
3280 oe_debug_pub.add( 'SHIP_CONFIRM_PTO_KIT : EXITING WITH OTHERS ERROR' , 1 ) ;
3281 END IF;
3282 IF l_debug_level > 0 THEN
3283 oe_debug_pub.add( 'ERROR MESSAGE : '||SQLERRM , 1 ) ;
3284 END IF;
3285 IF g_debug_call > 0 THEN
3286 G_DEBUG_MSG := G_DEBUG_MSG || 'E9';
3287 END IF;
3288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3289
3290 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3291 THEN
3292 OE_MSG_PUB.Add_Exc_Msg
3293 ( G_PKG_NAME
3294 , 'Ship_Confirm_PTO_KIT'
3295 );
3296 END IF;
3297 IF g_debug_call > 0 THEN
3298 G_DEBUG_MSG := G_DEBUG_MSG || 'E14';
3299 END IF;
3300
3301 -- Get message count and data
3302
3303 END Ship_Confirm_PTO_KIT;
3304
3305
3306 /*--------------------------------------------------------------*/
3307 PROCEDURE Ship_Confirm_Standard_Line
3308 (p_line_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
3309 ,p_line_rec IN OE_ORDER_PUB.line_rec_type
3310 DEFAULT OE_ORDER_PUB.G_MISS_LINE_REC
3311 ,p_shipment_status IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
3312 ,p_check_line_set IN VARCHAR2 := 'Y'
3313 ,x_return_status OUT NOCOPY VARCHAR2)
3314
3315 IS
3316 l_line_rec OE_ORDER_PUB.line_rec_type;
3317 l_shipped_quantity NUMBER;
3318 l_x_result_out VARCHAR2(30);
3319 l_return_status VARCHAR2(1);
3320 l_msg_count NUMBER;
3321 l_msg_data VARCHAR2(2000);
3322 l_unreserve_quantity NUMBER;
3323 l_result_code VARCHAR2(30);
3324 l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
3325 l_control_rec OE_GLOBALS.Control_Rec_Type;
3326 l_set_recursion VARCHAR2(1) := 'N';
3327
3328 -- HW 2415731 variables for OPM
3329 l_temp_shipped_qty NUMBER;
3330 l_item_rec OE_ORDER_CACHE.item_rec_type;
3331 l_uom_different NUMBER;
3332 l_requested_qty_uom VARCHAR2(3);
3333
3334 --
3335 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3336 --
3337
3338 BEGIN
3339
3340 IF l_debug_level > 0 THEN
3341 oe_debug_pub.add( 'ENTERING SHIP_CONFIRM_STANDARD_LINE ' , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
3342 END IF;
3343
3344 IF g_debug_call > 0 THEN
3345 G_DEBUG_MSG := G_DEBUG_MSG || '101,';
3346 END IF;
3347
3348 IF p_line_id IS NULL OR
3349 p_line_id = FND_API.G_MISS_NUM THEN
3350 l_line_rec := p_line_rec;
3351 IF l_debug_level > 0 THEN
3352 oe_debug_pub.add( 'p_line_rec.line_id '||P_LINE_REC.LINE_ID , 3 ) ;
3353 END IF;
3354 ELSE
3355 IF l_debug_level > 0 THEN
3356 oe_debug_pub.add( 'line_id '||P_LINE_ID , 3 ) ;
3357 END IF;
3358
3359 OE_Line_Util.Query_Row(p_line_id => p_line_id,
3360 x_line_rec => l_line_rec);
3361 END IF;
3362
3363
3364 -- Check for Shipment Status
3365
3366 IF p_shipment_status <> FND_API.G_MISS_CHAR AND
3367 p_shipment_status IS NOT NULL THEN
3368
3369 IF l_debug_level > 0 THEN
3370 oe_debug_pub.add( '1 shipment status '|| P_SHIPMENT_STATUS , 1 ) ;
3371 END IF;
3372 l_x_result_out := p_shipment_status;
3373
3374 IF g_debug_call > 0 THEN
3375 G_DEBUG_MSG := G_DEBUG_MSG || '103,';
3376 END IF;
3377 ELSE
3378
3379 BEGIN
3380
3381 SELECT nvl(sum(shipped_quantity),0), requested_quantity_uom
3382 INTO l_shipped_quantity, l_requested_qty_uom
3383 FROM wsh_delivery_details
3384 WHERE source_line_id in
3385 (SELECT line_id
3386 FROM oe_order_lines
3387 WHERE line_set_id = l_line_rec.line_set_id
3388 AND shipped_quantity IS NULL
3389 AND line_id <> l_line_rec.line_id)
3390 AND source_code = 'OE'
3391 AND released_status = 'C'
3392 GROUP BY requested_quantity_uom;
3393
3394
3395 EXCEPTION
3396 WHEN OTHERS THEN
3397 IF l_debug_level > 0 THEN
3398 oe_debug_pub.add('wsh select error '|| sqlerrm, 1);
3399 END IF;
3400 END;
3401
3402 IF l_debug_level > 0 THEN
3403 oe_debug_pub.add('additional shipped qty '|| l_shipped_quantity, 1);
3404 END IF;
3405
3406 IF l_line_rec.order_quantity_uom <> l_requested_qty_uom THEN
3407
3408 l_shipped_quantity := OE_Order_Misc_Util.Convert_Uom
3409 ( l_line_rec.inventory_item_id,
3410 l_requested_qty_uom,
3411 l_line_rec.order_quantity_uom,
3412 l_shipped_quantity );
3413
3414 IF l_debug_level > 0 THEN
3415 oe_debug_pub.add('AFTER UOM CONV : '|| L_SHIPPED_QUANTITY,3);
3416 END IF;
3417 IF g_debug_call > 0 THEN
3418 G_DEBUG_MSG := G_DEBUG_MSG || '105,';
3419 END IF;
3420
3421 END IF;
3422
3423 IF g_debug_call > 0 THEN
3424 G_DEBUG_MSG := G_DEBUG_MSG || '106,';
3425 END IF;
3426
3427 Check_Shipment_Line
3428 ( p_line_rec => l_line_rec,
3429 p_shipped_quantity => l_shipped_quantity,
3430 x_result_out => l_x_result_out);
3431
3432 IF l_debug_level > 0 THEN
3433 oe_debug_pub.add('AFTER check_shipement: '|| l_x_result_out,3);
3434 END IF;
3435
3436 END IF; -- if shipment status was sent in
3437
3438
3439
3440
3441 IF l_x_result_out = OE_GLOBALS.G_SHIPPED_WITHIN_TOL_BELOW THEN
3442
3443 -- Call API to releive allocation
3444 IF l_debug_level > 0 THEN
3445 oe_debug_pub.add( 'SHIPPED WITHIN TOLERANCE BELOW ' , 3 ) ;
3446 END IF;
3447
3448 /* unreserve function need not to be performed as lines
3449 will be shipping interfaced, and delete reservation
3450 will be taken care by shipping */
3451
3452 IF g_debug_call > 0 THEN
3453 G_DEBUG_MSG := G_DEBUG_MSG || '104';
3454 END IF;
3455
3456 END IF; -- if below tolerance
3457
3458
3459 IF l_x_result_out = OE_GLOBALS.G_SHIPPED_BEYOND_TOLERANCE THEN
3460 NULL;
3461 -- Send the notification ??
3462 END IF;
3463
3464
3465 IF l_x_result_out = OE_GLOBALS.G_PARTIALLY_SHIPPED AND
3466 l_line_rec.ordered_quantity > l_line_rec.shipped_quantity AND
3467 (l_line_rec.top_model_line_id is null OR
3468 l_line_rec.top_model_line_id = FND_API.G_MISS_NUM OR
3469 nvl(l_line_rec.model_remnant_flag,'N') = 'Y') THEN
3470
3471 -- Split API is called only for Standard line. For PTO/ATO it
3472 -- will be called from respective procedures.
3473 -- Call Split API It should perform the followings :
3474 -- 1. Update the ordered quantity on the old line
3475 -- 2. Create a new line with Shipping_Interfaced_Flag = 'Y'
3476 -- 3. Call Process Order to perform 1 and 2.
3477
3478 IF l_debug_level > 0 THEN
3479 oe_debug_pub.add( 'CALLING OE_ORDER_PVT.PROCESS_ORDER ' , 3 ) ;
3480 END IF;
3481 -- Assign the first record of the table for process order for update
3482 -- of the ordered quantity.
3483
3484 l_line_tbl(1) := l_line_rec;
3485 IF g_debug_call > 0 THEN
3486 G_DEBUG_MSG := G_DEBUG_MSG || '112';
3487 END IF;
3488
3489 -- HW BUG#:2415731 initialize variables -- INVCONV
3490 l_uom_different :=0;
3491 l_temp_shipped_qty :=0;
3492
3493 -- HW OPM BUG#:2415731. Is shipped_uom different from order_uom,
3494 -- if so, we need to perform item specific conversion Not sure if need this RIGHT NOW cos converted above in misc convert
3495
3496 IF ( l_line_tbl(1).shipping_quantity_uom <> l_line_tbl(1).order_quantity_uom )
3497 AND oe_line_util.dual_uom_control -- INVCONV
3498 (l_line_tbl(1).inventory_item_id
3499 ,l_line_tbl(1).ship_from_org_id
3500 ,l_item_rec) THEN
3501
3502 /*l_temp_shipped_qty := GMI_Reservation_Util.get_opm_converted_qty(
3503 p_apps_item_id => l_line_tbl(1).inventory_item_id,
3504 p_organization_id => l_line_tbl(1).ship_from_org_id,
3505 p_apps_from_uom => l_line_tbl(1).shipping_quantity_uom,
3506 p_apps_to_uom => l_line_tbl(1).order_quantity_uom,
3507 p_original_qty => l_line_tbl(1).shipping_quantity); */
3508
3509 l_temp_shipped_qty := INV_CONVERT.INV_UM_CONVERT(l_line_tbl(1).inventory_item_id -- INVCONV
3510 ,5 --NULL
3511 ,l_line_tbl(1).shipping_quantity
3512 ,l_line_tbl(1).shipping_quantity_uom
3513 ,l_line_tbl(1).order_quantity_uom
3514 ,NULL -- From uom name
3515 ,NULL -- To uom name
3516 );
3517
3518 IF l_debug_level > 0 THEN
3519 oe_debug_pub.add('Ship_Confirm_Standard_Line : l_temp_shipped_qty for dual control item is '|| l_temp_shipped_qty);
3520 END IF;
3521
3522
3523 IF g_debug_call > 0 THEN
3524 G_DEBUG_MSG := G_DEBUG_MSG || '107';
3525 END IF;
3526
3527 IF l_debug_level > 0 THEN
3528 oe_debug_pub.add( 'OPM , SHIPPED_UOM <> ORD_UOM AND CONVERSION VALUE IS: '||L_TEMP_SHIPPED_QTY , 1 ) ;
3529 END IF;
3530
3531 l_line_tbl(1).ordered_quantity := l_temp_shipped_qty;
3532 -- Check if item is dual UOM
3533 IF ( l_line_tbl(1).ordered_quantity_uom2 <> FND_API.G_MISS_CHAR ) THEN
3534
3535 -- User can order in a 3rd UOM that is not primary nor secondary
3536 -- but a convertable UOM.
3537 -- 3rd conversion case, we need to perform an item specific conversion
3538 -- IF ( l_line_tbl(1).order_quantity_uom <> l_line_tbl(1).ordered_quantity_uom2) THEN
3539
3540 -- l_line_tbl(1).ordered_quantity2 := GMI_Reservation_Util.get_opm_converted_qty
3541 -- (p_apps_item_id => l_line_tbl(1).inventory_item_id,
3542 -- p_organization_id => l_line_tbl(1).ship_from_org_id,
3543 -- p_apps_from_uom => l_line_tbl(1).order_quantity_uom,
3544 -- p_apps_to_uom => l_line_tbl(1).ordered_quantity_uom2,
3545 -- p_original_qty => l_temp_shipped_qty);
3546 -- IF l_debug_level > 0 THEN
3547 -- oe_debug_pub.add( 'OPM 3RD CONVERSION AND CONV. VALUE IS '|| L_LINE_TBL ( 1 ) .ORDERED_QUANTITY2 , 1 ) ;
3548 -- END IF;
3549 IF g_debug_call > 0 THEN
3550 G_DEBUG_MSG := G_DEBUG_MSG || '109,';
3551 END IF;
3552 -- ordered item is in secondary UOM
3553 -- ELSE
3554 -- l_line_tbl(1).ordered_quantity2 := l_temp_shipped_qty;
3555 -- END IF;
3556 l_line_tbl(1).ordered_quantity2 := l_line_rec.shipped_quantity2; --bug 2999767
3557 -- item is a single UOM
3558 ELSE
3559 l_line_tbl(1).ordered_quantity2 :=NULL;
3560 IF g_debug_call > 0 THEN
3561 G_DEBUG_MSG := G_DEBUG_MSG || '115,';
3562 END IF;
3563 END IF;
3564
3565 l_uom_different :=1;
3566
3567 ELSE -- UOMS are same. This is good for discrete and OPM
3568 l_line_tbl(1).ordered_quantity := l_line_rec.shipped_quantity;
3569 -- check if item is a dual UOM
3570 IF ( l_line_tbl(1).ordered_quantity_uom2 <> FND_API.G_MISS_CHAR ) THEN
3571 l_line_tbl(1).ordered_quantity2 := l_line_rec.shipped_quantity2;
3572 -- single UOM item
3573 ELSE
3574 l_line_tbl(1).ordered_quantity2 := NULL;
3575 END IF;
3576
3577 END IF; -- end of uoms are different
3578 -- IF ( l_line_tbl(1).shipping_quantity_uom <> l_line_tbl(1).order_quantity_uom )
3579 -- AND oe_line_util.Process_Characteristics
3580 -- HW end of changes for BUG#: 2415731
3581
3582 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
3583 l_line_tbl(1).split_action_code := 'SPLIT';
3584 l_line_tbl(1).split_by := 'SYSTEM';
3585
3586 -- Assign the second record of the table for process order for
3587 -- create of new line.
3588
3589 l_line_tbl(2) := OE_ORDER_PUB.G_MISS_LINE_REC;
3590 l_line_tbl(2).split_from_line_id := l_line_rec.line_id;
3591
3592 -- HW BUG#: 2415731.
3593
3594 IF ( l_uom_different = 1 ) THEN -- UOMS are different and it is dual item line -- INVCONV
3595 l_line_tbl(2).ordered_quantity := l_line_rec.ordered_quantity - l_temp_shipped_qty;
3596 -- Check if item is a dual UOM
3597 IF ( l_line_tbl(1).ordered_quantity_uom2 <> FND_API.G_MISS_CHAR ) THEN
3598
3599 -- In OPM, User can order in a 3rd UOM that is not primary nor secondary
3600 -- but a convertable UOM.
3601 -- 3rd conversion case, we need to perform an item specific conversion
3602
3603 -- IF ( l_line_tbl(1).order_quantity_uom <> l_line_tbl(1).ordered_quantity_uom2) THEN
3604 -- l_line_tbl(2).ordered_quantity2 := GMI_Reservation_Util.get_opm_converted_qty
3605 -- ( p_apps_item_id => l_line_tbl(1).inventory_item_id,
3606 -- p_organization_id => l_line_tbl(1).ship_from_org_id,
3607 -- p_apps_from_uom => l_line_tbl(1).order_quantity_uom,
3608 -- p_apps_to_uom => l_line_tbl(1).ordered_quantity_uom2,
3609 -- p_original_qty => l_line_tbl(2).ordered_quantity);
3610
3611 -- ordered item is in secondary UOM
3612 -- ELSE
3613 -- l_line_tbl(2).ordered_quantity2 := l_line_rec.ordered_quantity2 -
3614 -- l_temp_shipped_qty;
3615 -- END IF;
3616 -- Item is a single UOM
3617
3618 -- bug2999767
3619 l_line_tbl(2).ordered_quantity2 := l_line_rec.ordered_quantity2 -
3620 l_line_rec.shipped_quantity2;
3621 ELSE
3622 l_line_tbl(2).ordered_quantity2 := NULL;
3623 END IF;
3624 IF g_debug_call > 0 THEN
3625 G_DEBUG_MSG := G_DEBUG_MSG || 'Shp-9-18';
3626 END IF;
3627
3628 ELSE -- UOMS are same. This is good for discrete and OPM
3629 l_line_tbl(2).ordered_quantity := l_line_rec.ordered_quantity -
3630 l_line_rec.shipped_quantity;
3631 -- Check if item is a dual UOM
3632 IF ( l_line_tbl(1).ordered_quantity_uom2 <> FND_API.G_MISS_CHAR ) THEN
3633 l_line_tbl(2).ordered_quantity2 := l_line_rec.ordered_quantity2 -
3634 l_line_rec.shipped_quantity2;
3635 -- Item is a single UOM
3636 ELSE
3637 l_line_tbl(2).ordered_quantity2 := NULL;
3638 END IF;
3639
3640 END IF; -- IF ( l_line_tbl(1).shipping_quantity_uom <> l_line_tbl(1).order_quantity_uom )
3641 -- AND oe_line_util.Process_Characteristics end of branching
3642 -- HW end of changes 2415731
3643
3644
3645 l_line_tbl(2).operation := OE_GLOBALS.G_OPR_CREATE;
3646 l_line_tbl(2).split_by := 'SYSTEM';
3647
3648 -- HW BUG#:2415731 added debugging messages for qty2 and others
3649 IF l_debug_level > 0 THEN
3650 oe_debug_pub.add( 'SPLIT FROM LINE: '||TO_CHAR ( L_LINE_TBL ( 2 ) .SPLIT_FROM_LINE_ID ) , 3 ) ;
3651 END IF;
3652 IF l_debug_level > 0 THEN
3653 oe_debug_pub.add( 'ORIGINAL ORD QTY : '||TO_CHAR ( L_LINE_REC.ORDERED_QUANTITY ) , 3 ) ;
3654 END IF;
3655 IF l_debug_level > 0 THEN
3656 oe_debug_pub.add( 'ORD QTY OLD LINE : '||TO_CHAR ( L_LINE_TBL ( 1 ) .ORDERED_QUANTITY ) , 3 ) ;
3657 END IF;
3658 IF l_debug_level > 0 THEN
3659 oe_debug_pub.add( 'ORD QTY2 OLD LINE : '||TO_CHAR ( L_LINE_TBL ( 1 ) .ORDERED_QUANTITY2 ) , 3 ) ;
3660 END IF;
3661 IF l_debug_level > 0 THEN
3662 oe_debug_pub.add( 'ORD QTY NEW LINE : '||TO_CHAR ( L_LINE_TBL ( 2 ) .ORDERED_QUANTITY ) , 3 ) ;
3663 END IF;
3664 IF l_debug_level > 0 THEN
3665 oe_debug_pub.add( 'ORD QTY2 NEW LINE : '||TO_CHAR ( L_LINE_TBL ( 2 ) .ORDERED_QUANTITY2 ) , 3 ) ;
3666 END IF;
3667 IF g_debug_call > 0 THEN
3668 G_DEBUG_MSG := G_DEBUG_MSG || 'Shp-9-19';
3669 END IF;
3670
3671 -- 4. Call to process order will result in call to
3672 -- update_shipping_attributes for update of ordered quantity
3673 -- and creation of new line.
3674
3675 l_control_rec.validate_entity := FALSE;
3676 l_control_rec.check_security := FALSE;
3677
3678 IF OE_GLOBALS.G_RECURSION_MODE = 'Y' THEN
3679 l_set_recursion := 'N';
3680 ELSE
3681 l_set_recursion := 'Y';
3682 END IF;
3683
3684 Call_Process_Order
3685 ( p_line_tbl => l_line_tbl,
3686 p_control_rec => l_control_rec,
3687 x_return_status => l_return_status);
3688
3689 IF l_set_recursion = 'Y' THEN
3690 l_set_recursion := 'N';
3691 END IF;
3692
3693 IF l_debug_level > 0 THEN
3694 oe_debug_pub.add( 'RET STS PROCESS ORDER : '||L_RETURN_STATUS , 3 ) ;
3695 END IF;
3696
3697 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3699 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3700 RAISE FND_API.G_EXC_ERROR;
3701 END IF;
3702 END IF; -- split
3703
3704 IF g_debug_call > 0 THEN
3705 G_DEBUG_MSG := G_DEBUG_MSG || 'Shp-9-21';
3706 END IF;
3707
3708 IF l_debug_level > 0 THEN
3709 oe_debug_pub.add( 'ITEM TYPE CODE HERE '|| L_LINE_REC.ITEM_TYPE_CODE , 1 ) ;
3710 END IF;
3711
3712 IF l_line_rec.item_type_code = 'CONFIG' THEN
3713
3714 SELECT ato_line_id
3715 INTO l_line_rec.ato_line_id
3716 FROM oe_order_lines
3717 WHERE line_id = l_line_rec.line_id;
3718
3719 Handle_Config_Parent
3720 ( p_ato_line_id => l_line_rec.ato_line_id);
3721 END IF;
3722
3723 -- Check for Shipment within ship tolerance below, within ship tolerance
3724 -- above and beyond tolerance.
3725
3726 IF l_x_result_out = OE_GLOBALS.G_SHIPPED_WITHIN_TOL_BELOW OR
3727 l_x_result_out = OE_GLOBALS.G_SHIPPED_WITHIN_TOL_ABOVE OR
3728 l_x_result_out = OE_GLOBALS.G_PARTIALLY_SHIPPED OR
3729 l_x_result_out = OE_GLOBALS.G_FULLY_SHIPPED OR
3730 l_x_result_out = OE_GLOBALS.G_SHIPPED_BEYOND_TOLERANCE
3731 THEN
3732
3733 IF p_check_line_set = 'Y' AND
3734 l_x_result_out <> OE_GLOBALS.G_PARTIALLY_SHIPPED AND
3735 l_line_rec.item_type_code IN ('STANDARD') THEN
3736
3737 -- not now 'CONFIG'
3738 -- l_line_rec.ordered_quantity <??? l_line_rec.shipped_quantity AND
3739 -- 2617708 OE_CODE_CONTROL.CODE_RELEASE_LEVEL <= '110508' THEN
3740
3741 IF l_debug_level > 0 THEN
3742 oe_debug_pub.add('CALLING Ship_Confirm_Split_Lines '
3743 ||l_line_rec.shipped_quantity ,3);
3744 END IF;
3745 IF g_debug_call > 0 THEN
3746 G_DEBUG_MSG := G_DEBUG_MSG || '145';
3747 END IF;
3748 Ship_Confirm_Split_Lines
3749 ( p_line_rec => l_line_rec
3750 ,p_shipment_status => l_x_result_out);
3751
3752 END IF;
3753
3754 IF l_debug_level > 0 THEN
3755 oe_debug_pub.add( 'CALLING FLOW STATUS API ' , 3 ) ;
3756 END IF;
3757 IF g_debug_call > 0 THEN
3758 G_DEBUG_MSG := G_DEBUG_MSG || '9-24';
3759 END IF;
3760
3761 OE_Order_WF_Util.Update_Flow_Status_Code
3762 (p_header_id => l_line_rec.header_id,
3763 p_line_id => l_line_rec.line_id,
3764 p_flow_status_code => 'SHIPPED',
3765 x_return_status => l_return_status );
3766
3767 IF l_debug_level > 0 THEN
3768 oe_debug_pub.add( 'RETURN STS FLOW STATUS API '||L_RETURN_STATUS , 3 ) ;
3769 END IF;
3770
3771 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3772 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3773 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3774 RAISE FND_API.G_EXC_ERROR;
3775 END IF;
3776 IF g_debug_call > 0 THEN
3777 G_DEBUG_MSG := G_DEBUG_MSG || '9-25';
3778 END IF;
3779 -- Call WF function to complete the shipment activity
3780
3781 IF l_x_result_out = OE_GLOBALS.G_SHIPPED_BEYOND_TOLERANCE THEN
3782 IF l_debug_level > 0 THEN
3783 oe_debug_pub.add( 'LINE SHIPPED BEYOND OVERSHIPMENT TOLERANCE ' , 3 ) ;
3784 END IF;
3785 l_result_code := 'OVER_SHIPPED';
3786 ELSE
3787 l_result_code := 'SHIP_CONFIRM';
3788 END IF;
3789
3790 -- 1739574 Log a delayed request for Complete Activity
3791 IF l_debug_level > 0 THEN
3792 oe_debug_pub.add( 'COMPLETEACTIVITY , LOG DELAYED REQ '||L_LINE_REC.LINE_ID , 3 ) ;
3793 END IF;
3794
3795 OE_Delayed_Requests_Pvt.Log_Request
3796 (p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
3797 p_entity_id => l_line_rec.line_id,
3798 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
3799 p_requesting_entity_id => l_line_rec.line_id,
3800 p_request_type => OE_GLOBALS.G_COMPLETE_ACTIVITY,
3801 p_param1 => OE_GLOBALS.G_WFI_LIN,
3802 p_param2 => l_line_rec.line_id,
3803 p_param3 => 'SHIP_LINE',
3804 p_param4 => l_result_code,
3805 x_return_status => l_return_status);
3806 IF g_debug_call > 0 THEN
3807 G_DEBUG_MSG := G_DEBUG_MSG || '9-27';
3808 END IF;
3809 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3810 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3811 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3812 RAISE FND_API.G_EXC_ERROR;
3813 END IF;
3814
3815 IF l_line_rec.arrival_set_id IS NOT NULL AND
3816 l_line_rec.arrival_set_id <> FND_API.G_MISS_NUM THEN
3817 UPDATE OE_SETS
3818 SET SET_STATUS = 'C'
3819 WHERE SET_ID = l_line_rec.arrival_set_id;
3820 IF l_debug_level > 0 THEN
3821 oe_debug_pub.add( 'SET CLOSED: '|| L_LINE_REC.ARRIVAL_SET_ID , 3 ) ;
3822 END IF;
3823 END IF;
3824
3825 END IF;
3826 IF g_debug_call > 0 THEN
3827 G_DEBUG_MSG := G_DEBUG_MSG || '9-28';
3828 END IF;
3829 x_return_status := FND_API.G_RET_STS_SUCCESS;
3830
3831 IF l_debug_level > 0 THEN
3832 oe_debug_pub.add( 'EXITING SHIP_CONFIRM_STANDARD_LINE '|| X_RETURN_STATUS , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
3833 END IF;
3834
3835 EXCEPTION
3836 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3838
3839 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3840 THEN
3841 OE_MSG_PUB.Add_Exc_Msg
3842 ( G_PKG_NAME,
3843 'Ship_Confirm_Standard_Line'
3844 );
3845 END IF;
3846 IF g_debug_call > 0 THEN
3847 G_DEBUG_MSG := G_DEBUG_MSG || 'E20';
3848 END IF;
3849
3850 WHEN FND_API.G_EXC_ERROR THEN
3851 IF l_debug_level > 0 THEN
3852 oe_debug_pub.add( 'SHIP_CONFIRM_STANDARD_LINE EXC ERROR' , 1 ) ;
3853 END IF;
3854 x_return_status := FND_API.G_RET_STS_ERROR;
3855 IF g_debug_call > 0 THEN
3856 G_DEBUG_MSG := G_DEBUG_MSG || 'E-21';
3857 END IF;
3858
3859 WHEN OTHERS THEN
3860 IF l_debug_level > 0 THEN
3861 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 100 ) , 1 ) ;
3862 END IF;
3863 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3864
3865 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3866 THEN
3867 OE_MSG_PUB.Add_Exc_Msg
3868 ( G_PKG_NAME,
3869 'Ship_Confirm_Standard_Line'
3870 );
3871 END IF;
3872 IF g_debug_call > 0 THEN
3873 G_DEBUG_MSG := G_DEBUG_MSG || 'E22';
3874 END IF;
3875 END Ship_Confirm_Standard_Line;
3876
3877 /*--------------------------------------------------------------
3878 PROCEDURE Handle_Config_Parent
3879 To progress the ATO parent of config line.
3880 ---------------------------------------------------------------*/
3881 PROCEDURE Handle_Config_Parent
3882 ( p_ato_line_id IN NUMBER)
3883 IS
3884 l_return_status VARCHAR2(1);
3885 l_activity_status VARCHAR2(8);
3886 l_activity_result VARCHAR2(30);
3887 l_activity_id NUMBER;
3888 --
3889 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3890 --
3891 BEGIN
3892
3893 IF l_debug_level > 0 THEN
3894 oe_debug_pub.add( 'ENTERING HANDLE_CONFIG_PARENT ' , 1 ) ;
3895 END IF;
3896
3897 -- Call work flow engine to complete the WAIT_FOR_CTO work flow
3898 -- activity for the MODEL line
3899
3900 IF l_debug_level > 0 THEN
3901 oe_debug_pub.add( 'GET ACTIVITY RESULT : '|| P_ATO_LINE_ID , 3 ) ;
3902 END IF;
3903
3904 IF g_debug_call > 0 THEN
3905 G_DEBUG_MSG := G_DEBUG_MSG || '10-1';
3906 END IF;
3907
3908 OE_LINE_FULLFILL.Get_Activity_Result
3909 ( p_item_type => OE_GLOBALS.G_WFI_LIN
3910 , p_item_key => p_ato_line_id
3911 , p_activity_name => 'WAIT_FOR_CTO'
3912 , x_return_status => l_return_status
3913 , x_activity_result => l_activity_result
3914 , x_activity_status_code => l_activity_status
3915 , x_activity_id => l_activity_id );
3916
3917 IF l_debug_level > 0 THEN
3918 oe_debug_pub.add( 'STATUS GET ACTIVITY RESULT : '||L_RETURN_STATUS , 3 ) ;
3919 END IF;
3920
3921 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3922 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3923 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3924 IF l_debug_level > 0 THEN
3925 oe_debug_pub.add( 'LINE IS NOT AT WAIT_FOR_CTO ACTIVITY' , 1 ) ;
3926 END IF;
3927 ELSE
3928
3929
3930 IF l_activity_status = 'NOTIFIED' THEN
3931 IF l_debug_level > 0 THEN
3932 oe_debug_pub.add( 'LINE IS AT WAIT_FOR_CTO ACTIVITY' , 1 ) ;
3933 END IF;
3934
3935 -- 1739574 Log a delayed request for Complete Activity
3936
3937 IF l_debug_level > 0 THEN
3938 oe_debug_pub.add( 'COMPLETE ACTIVITY , FOR '|| P_ATO_LINE_ID , 3 ) ;
3939 END IF;
3940 IF g_debug_call > 0 THEN
3941 G_DEBUG_MSG := G_DEBUG_MSG || '10-3';
3942 END IF;
3943
3944 OE_Delayed_Requests_Pvt.Log_Request
3945 ( p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
3946 p_entity_id => p_ato_line_id,
3947 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
3948 p_requesting_entity_id => p_ato_line_id,
3949 p_request_type => OE_GLOBALS.G_COMPLETE_ACTIVITY,
3950 p_param1 => OE_GLOBALS.G_WFI_LIN,
3951 p_param2 => p_ato_line_id,
3952 p_param3 => 'WAIT_FOR_CTO',
3953 p_param4 => OE_GLOBALS.G_WFR_COMPLETE,
3954 x_return_status => l_return_status);
3955
3956 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3957 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3958 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3959 RAISE FND_API.G_EXC_ERROR;
3960 END IF;
3961
3962 END IF;
3963 END IF;
3964
3965 IF l_debug_level > 0 THEN
3966 oe_debug_pub.add( 'EXITING HANDLE_CONFIG_PARENT '|| L_RETURN_STATUS , 1 ) ;
3967 END IF;
3968 IF g_debug_call > 0 THEN
3969 G_DEBUG_MSG := G_DEBUG_MSG || '10-4';
3970 END IF;
3971 EXCEPTION
3972 WHEN OTHERS THEN
3973 IF l_debug_level > 0 THEN
3974 oe_debug_pub.add( 'HANDLE_CONFIG_PARENT ERROR '|| SQLERRM , 1 ) ;
3975 END IF;
3976 IF g_debug_call > 0 THEN
3977 G_DEBUG_MSG := G_DEBUG_MSG || 'E-23';
3978 END IF;
3979 RAISE;
3980 END Handle_Config_Parent;
3981
3982
3983 /*------------------------------------------------------------------
3984 PROCEDURE Ship_Confirm_ATO
3985
3986 This procedure should be called for a CONFIG line with
3987 model_remnant_clag = 'N' and only when the CONFIG line
3988 is part of a top level ATO.
3989 The PTO+ATO case is handled by Ship_Confirm_PTO_KIT procedure
3990 -------------------------------------------------------------------*/
3991 -- INVCONV - OPEN ISSUE FOR opm CONVREGENCE RIGHT NOW FOR ato SUPPORT
3992
3993 PROCEDURE Ship_Confirm_ATO
3994 ( p_line_id IN NUMBER
3995 ,x_return_status OUT NOCOPY VARCHAR2)
3996 IS
3997 l_line_id NUMBER;
3998 l_return_status VARCHAR2(1);
3999 l_line_rec OE_ORDER_PUB.Line_Rec_Type;
4000 l_update_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
4001 l_control_rec OE_GLOBALS.Control_Rec_Type;
4002 l_set_recursion VARCHAR2(1) := 'N';
4003 l_split_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
4004 l_result_out VARCHAR2(30);
4005 --
4006 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4007 --
4008 BEGIN
4009
4010 IF l_debug_level > 0 THEN
4011 oe_debug_pub.add('ENTERING OE_SHIPPING_INTEGRATION_PVT.SHIP_CONFIRM_ATO '
4012 || TO_CHAR ( P_LINE_ID ) , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
4013 END IF;
4014
4015 l_line_id := p_line_id;
4016
4017 -- Call Process Order to Update the MODEL SHIPPED Quantity
4018
4019 OE_Line_Util.Query_Row
4020 (p_line_id => p_line_id,
4021 x_line_rec => l_line_rec);
4022
4023 IF g_debug_call > 0 THEN
4024 G_DEBUG_MSG := G_DEBUG_MSG || '10-5';
4025 END IF;
4026 OE_Line_Util.Query_Rows
4027 (p_line_id => l_line_rec.top_model_line_id,
4028 x_line_tbl => l_update_line_tbl);
4029
4030 l_control_rec := OE_GLOBALS.G_MISS_CONTROL_REC;
4031 l_control_rec.validate_entity:= FALSE;
4032 l_control_rec.check_security := FALSE;
4033
4034 l_update_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
4035 l_update_line_tbl(1).shipped_quantity := l_line_rec.shipped_quantity;
4036 l_update_line_tbl(1).actual_shipment_date := l_line_rec.actual_shipment_date;
4037
4038 IF l_debug_level > 0 THEN
4039 oe_debug_pub.add('ACTUAL SHIPMENT DATE :'
4040 ||TO_CHAR(L_UPDATE_LINE_TBL(1).ACTUAL_SHIPMENT_DATE,'DD-MM-YY'),3);
4041 END IF;
4042
4043 IF OE_GLOBALS.G_RECURSION_MODE = 'Y' THEN
4044 l_set_recursion := 'N';
4045 ELSE
4046 l_set_recursion := 'Y';
4047 END IF;
4048
4049 Call_Process_Order
4050 ( p_line_tbl => l_update_line_tbl,
4051 p_control_rec => l_control_rec,
4052 x_return_status => l_return_status);
4053
4054 IF l_set_recursion = 'Y' THEN
4055 l_set_recursion := 'N';
4056 END IF;
4057
4058 IF l_debug_level > 0 THEN
4059 oe_debug_pub.add('RET STS FROM PROCESS ORDER : '||L_RETURN_STATUS , 3 ) ;
4060 END IF;
4061
4062 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4063 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4064 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4065 RAISE FND_API.G_EXC_ERROR;
4066 END IF;
4067
4068 IF g_debug_call > 0 THEN
4069 G_DEBUG_MSG := G_DEBUG_MSG || '10-7';
4070 END IF;
4071 --------- split the entire ato MODEL if partailly shipped -------
4072
4073 Check_Shipment_Line
4074 (p_line_rec => l_line_rec,
4075 -- p_shipped_quantity => l_shipped_quantity,
4076 x_result_out => l_result_out);
4077
4078 IF l_debug_level > 0 THEN
4079 oe_debug_pub.add( 'SHIPMENT STATUS : '||L_RESULT_OUT , 3 ) ;
4080 END IF;
4081
4082
4083 IF l_result_out = OE_GLOBALS.G_PARTIALLY_SHIPPED AND
4084
4085 l_line_rec.ordered_quantity > l_line_rec.shipped_quantity THEN
4086
4087 l_split_line_tbl(1) := l_update_line_tbl(1);
4088 l_split_line_tbl(1).ordered_quantity := l_line_rec.shipped_quantity;
4089 l_split_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
4090 l_split_line_tbl(1).split_action_code:= 'SPLIT';
4091 l_split_line_tbl(1).split_by := 'SYSTEM';
4092
4093 -- Assign the second record of the table for process order for
4094 -- create of new line.
4095
4096 l_split_line_tbl(2) := OE_ORDER_PUB.G_MISS_LINE_REC;
4097 l_split_line_tbl(2).split_from_line_id := l_update_line_tbl(1).line_id;
4098 l_split_line_tbl(2).ordered_quantity :=
4099 l_line_rec.ordered_quantity - l_line_rec.shipped_quantity;
4100 l_split_line_tbl(2).operation := OE_GLOBALS.G_OPR_CREATE;
4101 l_split_line_tbl(2).split_by := 'SYSTEM';
4102
4103 IF l_debug_level > 0 THEN
4104 oe_debug_pub.add( 'ITEM TYPE CODE : '||L_LINE_REC.ITEM_TYPE_CODE , 3 ) ;
4105 END IF;
4106 IF g_debug_call > 0 THEN
4107 G_DEBUG_MSG := G_DEBUG_MSG || '10-8';
4108 END IF;
4109
4110 IF l_debug_level > 0 THEN
4111 oe_debug_pub.add( 'SPLIT FROM LINE ID : '
4112 ||TO_CHAR ( L_SPLIT_LINE_TBL ( 2 ) .SPLIT_FROM_LINE_ID ) , 3 ) ;
4113
4114 oe_debug_pub.add( 'ORIGINAL ORDERED QUANTITY : '
4115 ||TO_CHAR ( L_LINE_REC.ORDERED_QUANTITY ) , 3 ) ;
4116 oe_debug_pub.add( 'ORDERED QUANTITY OLD LINE : '
4117 ||TO_CHAR ( L_SPLIT_LINE_TBL ( 1 ) .ORDERED_QUANTITY ) , 3 ) ;
4118
4119 oe_debug_pub.add( 'ORDERED QUANTITY NEW LINE : '
4120 ||TO_CHAR ( L_SPLIT_LINE_TBL ( 2 ) .ORDERED_QUANTITY ) , 3 ) ;
4121 END IF;
4122
4123 l_control_rec := OE_GLOBALS.G_MISS_CONTROL_REC;
4124 l_control_rec.controlled_operation := TRUE;
4125 l_control_rec.check_security := FALSE;
4126 l_control_rec.change_attributes := TRUE;
4127 l_control_rec.default_attributes := TRUE;
4128 l_control_rec.clear_dependents := TRUE;
4129
4130 IF OE_GLOBALS.G_RECURSION_MODE = 'Y' THEN
4131 l_set_recursion := 'N';
4132 ELSE
4133 l_set_recursion := 'Y';
4134 END IF;
4135
4136 Call_Process_Order
4137 (p_line_tbl => l_split_line_tbl,
4138 p_control_rec => l_control_rec,
4139 x_return_status => l_return_status);
4140
4141 IF l_set_recursion = 'Y' THEN
4142 l_set_recursion := 'N';
4143 END IF;
4144 IF g_debug_call > 0 THEN
4145 G_DEBUG_MSG := G_DEBUG_MSG || 'Shp-10-9';
4146 END IF;
4147 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4149 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4150 RAISE FND_API.G_EXC_ERROR;
4151 END IF;
4152
4153 IF l_debug_level > 0 THEN
4154 oe_debug_pub.add('RET STS FROM PROCESS ORDER:'||L_RETURN_STATUS , 3 ) ;
4155 END IF;
4156
4157 END IF;
4158
4159
4160 ------- call ship confirm std line to complete wf, flow sts ---
4161
4162 Ship_Confirm_Standard_Line
4163 (p_line_id => l_line_id,
4164 x_return_status => l_return_status);
4165
4166 IF g_debug_call > 0 THEN
4167 G_DEBUG_MSG := G_DEBUG_MSG || '1-10';
4168 END IF;
4169
4170 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4172 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4173 RAISE FND_API.G_EXC_ERROR;
4174 END IF;
4175
4176 IF l_debug_level > 0 THEN -- Added debug for bug 13435459
4177 OE_DEBUG_PUB.add('Exit Ship_Confirm_ATO() at ',0.5);
4178 END IF;
4179 x_return_status := FND_API.G_RET_STS_SUCCESS;
4180
4181 EXCEPTION
4182 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4184
4185 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4186 THEN
4187 OE_MSG_PUB.Add_Exc_Msg
4188 (G_PKG_NAME,
4189 'Ship_Confirm_ATO');
4190 END IF;
4191 IF g_debug_call > 0 THEN
4192 G_DEBUG_MSG := G_DEBUG_MSG || 'E-24';
4193 END IF;
4194
4195 WHEN FND_API.G_EXC_ERROR THEN
4196 x_return_status := FND_API.G_RET_STS_ERROR;
4197 IF g_debug_call > 0 THEN
4198 G_DEBUG_MSG := G_DEBUG_MSG || 'E-25';
4199 END IF;
4200
4201 WHEN OTHERS THEN
4202 IF l_debug_level > 0 THEN
4203 oe_debug_pub.add('ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 100 ) , 1 ) ;
4204 END IF;
4205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4206
4207 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4208 THEN
4209 OE_MSG_PUB.Add_Exc_Msg
4210 ( G_PKG_NAME,
4211 'Ship_Confirm_ATO');
4212 END IF;
4213 IF g_debug_call > 0 THEN
4214 G_DEBUG_MSG := G_DEBUG_MSG || 'E-26';
4215 END IF;
4216
4217 END Ship_Confirm_ATO;
4218
4219
4220 /*---------------------------------------------------------------------
4221 PROCEDURE Ship_Confirm_Ship_Set
4222 ---------------------------------------------------------------------*/
4223 PROCEDURE Ship_Confirm_Ship_Set
4224 (
4225 p_ship_set_id IN NUMBER
4226 , x_return_status OUT NOCOPY VARCHAR2
4227
4228 )
4229 IS
4230 l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
4231 l_line_index NUMBER;
4232 l_process_tbl OE_Order_Pub.Line_Tbl_Type;
4233 l_process_index NUMBER := 0;
4234 l_set_tbl OE_ORDER_PUB.Line_Tbl_Type;
4235 l_set_index NUMBER := 0;
4236 l_control_rec OE_GLOBALS.Control_Rec_Type;
4237 l_line_shipped VARCHAR2(1) := FND_API.G_FALSE;
4238 l_return_status VARCHAR2(1);
4239 l_set_recursion VARCHAR2(1) := 'N';
4240
4241 --
4242 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4243 --
4244 BEGIN
4245
4246 IF l_debug_level > 0 THEN
4247 -- debug level changed to 0.5 for bug 13435459
4248 oe_debug_pub.add( 'ENTERING OE_SHIPPING_INTEGRATION_PVT.SHIP_CONFIRM_SHIP_SET '|| TO_CHAR ( P_SHIP_SET_ID ) , 0.5 ) ;
4249 END IF;
4250
4251 IF g_debug_call > 0 THEN
4252 G_DEBUG_MSG := G_DEBUG_MSG || '11-1';
4253 END IF;
4254 -- Call set function to get all the lines in a set.
4255
4256 -- l_line_tbl := OE_Set_Util.Query_Set_Rows(p_ship_set_id);
4257
4258 OE_Set_Util.Query_Set_Rows(p_set_id => p_ship_set_id,
4259 x_line_tbl => l_line_tbl);
4260
4261 IF l_debug_level > 0 THEN
4262 oe_debug_pub.add( 'NUMBER OF LINES IN THE SET '|| TO_CHAR ( L_LINE_TBL.COUNT ) , 3 ) ;
4263 END IF;
4264 -- Update the set status to closed.
4265
4266 UPDATE OE_SETS
4267 SET SET_STATUS = 'C'
4268 WHERE SET_ID = p_ship_set_id;
4269
4270 IF l_debug_level > 0 THEN
4271 oe_debug_pub.add( 'SET IS CLOSED : '||TO_CHAR ( P_SHIP_SET_ID ) , 3 ) ;
4272 END IF;
4273
4274 -- Prepare a table with PTO_KIT/ATO/STANDARD lines.
4275
4276 FOR l_line_index IN l_line_tbl.FIRST .. l_line_tbl.LAST
4277 LOOP
4278
4279 IF l_debug_level > 0 THEN
4280 oe_debug_pub.add( 'IN THE SET LOOP ' , 3 ) ;
4281 END IF;
4282 IF l_debug_level > 0 THEN
4283 oe_debug_pub.add( 'INDEX/ITEM TYPE : '||TO_CHAR ( L_LINE_INDEX ) ||'/'||L_LINE_TBL ( L_LINE_INDEX ) .ITEM_TYPE_CODE , 3 ) ;
4284 END IF;
4285 IF l_debug_level > 0 THEN
4286 oe_debug_pub.add( 'LINE_ID/TOP_MODEL_LINE_ID : '||TO_CHAR ( L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID ) ||'/'||TO_CHAR ( L_LINE_TBL ( L_LINE_INDEX ) .TOP_MODEL_LINE_ID ) , 3 ) ;
4287 END IF;
4288
4289 IF (l_line_tbl(l_line_index).link_to_line_id = l_line_tbl(l_line_index).top_model_line_id AND
4290 l_line_tbl(l_line_index).item_type_code = OE_Globals.G_ITEM_CONFIG) THEN
4291 IF l_debug_level > 0 THEN
4292 oe_debug_pub.add( 'IT IS AN ATO ' , 3 ) ;
4293 END IF;
4294 l_process_index := l_process_index + 1;
4295 l_process_tbl(l_process_index) := l_line_tbl(l_line_index);
4296
4297
4298 ELSIF (l_line_tbl(l_line_index).top_model_line_id IS NOT NULL AND
4299 l_line_tbl(l_line_index).top_model_line_id <> FND_API.G_MISS_NUM AND
4300 l_line_tbl(l_line_index).line_id = l_line_tbl(l_line_index).top_model_line_id) AND
4301 (l_line_tbl(l_line_index).ato_line_id IS NULL OR
4302 l_line_tbl(l_line_index).ato_line_id = FND_API.G_MISS_NUM) THEN
4303 IF l_debug_level > 0 THEN
4304 oe_debug_pub.add( 'IT IS A PTO/KIT ' , 3 ) ;
4305 END IF;
4306 l_process_index := l_process_index + 1;
4307 l_process_tbl(l_process_index) := l_line_tbl(l_line_index);
4308
4309 ELSIF l_line_tbl(l_line_index).item_type_code = OE_GLOBALS.G_ITEM_STANDARD THEN
4310
4311 IF l_debug_level > 0 THEN
4312 oe_debug_pub.add( 'IT IS A STANDARD LINE' , 3 ) ;
4313 END IF;
4314 l_process_index := l_process_index + 1;
4315 l_process_tbl(l_process_index) := l_line_tbl(l_line_index);
4316 END IF;
4317
4318 END LOOP;
4319
4320 IF l_debug_level > 0 THEN
4321 oe_debug_pub.add( 'NUMBER OF LINES TO PROCESS : '||TO_CHAR ( L_PROCESS_INDEX ) , 3 ) ;
4322 END IF;
4323 IF g_debug_call > 0 THEN
4324 G_DEBUG_MSG := G_DEBUG_MSG || 'Shp-11-3';
4325 END IF;
4326 FOR l_process_index IN l_process_tbl.FIRST .. l_process_tbl.LAST
4327 LOOP
4328
4329 IF l_process_tbl(l_process_index).top_model_line_id IS NOT NULL AND
4330 l_process_tbl(l_process_index).top_model_line_id <> FND_API.G_MISS_NUM AND
4331 /* Commented for bug 1820608
4332 (l_process_tbl(l_process_index).ato_line_id IS NULL OR
4333 l_process_tbl(l_process_index).ato_line_id = FND_API.G_MISS_NUM) THEN */
4334 /* Added condition of KIT for bug 1926571 */
4335 l_process_tbl(l_process_index).item_type_code IN ('MODEL','KIT') THEN
4336 IF l_debug_level > 0 THEN
4337 oe_debug_pub.add( 'IT IS PTO/KIT '||TO_CHAR ( L_PROCESS_TBL ( L_PROCESS_INDEX ) .TOP_MODEL_LINE_ID ) , 3 ) ;
4338 END IF;
4339
4340 -- Check if any of the option has been shipped for the model
4341
4342
4343 FOR l_line_index IN l_line_tbl.FIRST .. l_line_tbl.LAST
4344 LOOP
4345
4346 IF l_line_tbl(l_line_index).top_model_line_id = l_process_tbl(l_process_index).top_model_line_id AND
4347 nvl(l_line_tbl(l_line_index).shippable_flag,'N') = 'Y' AND
4348 nvl(l_line_tbl(l_line_index).shipped_quantity,0) <> 0 AND
4349 l_line_tbl(l_line_index).shipped_quantity <> FND_API.G_MISS_NUM THEN
4350 l_line_shipped := FND_API.G_TRUE;
4351 IF l_debug_level > 0 THEN
4352 oe_debug_pub.add( 'LINE IS SHIPPED '||TO_CHAR ( L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID ) , 3 ) ;
4353 END IF;
4354
4355 Ship_Confirm_PTO_KIT
4356 (
4357 p_top_model_line_id => l_process_tbl(l_process_index).top_model_line_id,
4358 x_return_status => l_return_status
4359 );
4360 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4362 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4363 RAISE FND_API.G_EXC_ERROR;
4364 END IF;
4365
4366 GOTO END_PTO_KIT_SET;
4367
4368 END IF;
4369
4370 END LOOP;
4371 <<END_PTO_KIT_SET>>
4372 NULL;
4373
4374 ELSIF nvl(l_process_tbl(l_process_index).shipped_quantity,0) <> 0 AND
4375 l_process_tbl(l_process_index).shipped_quantity <> FND_API.G_MISS_NUM THEN
4376 IF l_debug_level > 0 THEN
4377 oe_debug_pub.add( 'INSIDE THE LOOP : LINE ID : '|| TO_CHAR ( L_PROCESS_TBL ( L_PROCESS_INDEX ) .LINE_ID ) , 3 ) ;
4378 END IF;
4379 IF l_process_tbl(l_process_index).item_type_code = OE_GLOBALS.G_ITEM_STANDARD THEN
4380 Ship_Confirm_Standard_Line
4381 (
4382 p_line_rec => l_process_tbl(l_process_index),
4383 x_return_status => l_return_status
4384 );
4385
4386 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4387 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4388 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4389 RAISE FND_API.G_EXC_ERROR;
4390 END IF;
4391 END IF;
4392
4393 IF l_process_tbl(l_process_index).item_type_code = OE_GLOBALS.G_ITEM_CONFIG THEN
4394 Ship_Confirm_ATO
4395 (
4396 p_line_id => l_process_tbl(l_process_index).line_id,
4397 x_return_status => l_return_status
4398 );
4399
4400 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4401 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4402 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4403 RAISE FND_API.G_EXC_ERROR;
4404 END IF;
4405 END IF;
4406
4407 l_line_shipped := FND_API.G_TRUE;
4408 END IF;
4409
4410 IF l_line_shipped = FND_API.G_FALSE THEN
4411
4412 IF l_debug_level > 0 THEN
4413 oe_debug_pub.add( 'SHIP SET IS PARTIALLY SHIPPED REMOVING THE SETID '||TO_CHAR ( L_PROCESS_TBL ( L_PROCESS_INDEX ) .LINE_ID ) , 3 ) ;
4414 END IF;
4415 l_set_index := l_set_index + 1;
4416 l_set_tbl(l_set_index) := OE_Order_Pub.G_MISS_LINE_REC;
4417 IF l_process_tbl(l_process_index).item_type_code = OE_GLOBALS.G_ITEM_CONFIG THEN
4418 l_set_tbl(l_set_index).line_id := l_process_tbl(l_process_index).top_model_line_id;
4419 ELSE
4420 l_set_tbl(l_set_index).line_id := l_process_tbl(l_process_index).line_id;
4421 END IF;
4422 IF l_debug_level > 0 THEN
4423 oe_debug_pub.add( 'LINE ID : '||TO_CHAR ( L_SET_TBL ( L_SET_INDEX ) .LINE_ID ) , 3 ) ;
4424 END IF;
4425 l_set_tbl(l_set_index).ship_set_id := NULL;
4426 l_set_tbl(l_set_index).operation := OE_GLOBALS.G_OPR_UPDATE;
4427
4428 END IF;
4429
4430 l_line_shipped := FND_API.G_FALSE;
4431
4432 END LOOP;
4433
4434 IF l_set_index <> 0 THEN
4435
4436 l_control_rec := OE_GLOBALS.G_MISS_CONTROL_REC;
4437 l_control_rec.validate_entity := FALSE;
4438 l_control_rec.check_security := FALSE;
4439
4440 IF l_debug_level > 0 THEN
4441 oe_debug_pub.add( 'CALLING PROCESS ORDER TO UPDATE SHIP SET ID' , 3 ) ;
4442 END IF;
4443 IF l_debug_level > 0 THEN
4444 oe_debug_pub.add( 'NUMBER OF LINES '||TO_CHAR ( L_SET_INDEX ) , 3 ) ;
4445 END IF;
4446
4447 IF OE_GLOBALS.G_RECURSION_MODE = 'Y' THEN
4448
4449 l_set_recursion := 'N';
4450
4451 ELSE
4452
4453 l_set_recursion := 'Y';
4454 -- OE_GLOBALS.G_RECURSION_MODE := 'Y';
4455
4456 END IF;
4457 IF g_debug_call > 0 THEN
4458 G_DEBUG_MSG := G_DEBUG_MSG || '11-8';
4459 END IF;
4460
4461 Call_Process_Order
4462 (
4463 p_line_tbl => l_set_tbl,
4464 p_control_rec => l_control_rec,
4465 x_return_status => l_return_status
4466 );
4467
4468 IF l_set_recursion = 'Y' THEN
4469
4470 l_set_recursion := 'N';
4471 -- OE_GLOBALS.G_RECURSION_MODE := 'N';
4472 END IF;
4473
4474 IF l_debug_level > 0 THEN
4475 oe_debug_pub.add( 'RETURN STATUS FROM PROCESS ORDER : '||L_RETURN_STATUS , 3 ) ;
4476 END IF;
4477 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4478 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4479 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4480 RAISE FND_API.G_EXC_ERROR;
4481 END IF;
4482 END IF;
4483
4484 x_return_status := l_return_status;
4485
4486 IF l_debug_level > 0 THEN
4487 -- debug level changed to 0.5 for bug 13435459
4488 oe_debug_pub.add( 'EXITING FROM OE_SHIPPING_INTEGRATION_PVT.SHIP_CONFIRM_SHIP_SET : '||X_RETURN_STATUS , 0.5 ) ;
4489 END IF;
4490 IF g_debug_call > 0 THEN
4491 G_DEBUG_MSG := G_DEBUG_MSG || '11-9';
4492 END IF;
4493
4494 EXCEPTION
4495 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4497
4498 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4499 THEN
4500 OE_MSG_PUB.Add_Exc_Msg
4501 ( G_PKG_NAME,
4502 'Ship_Confirm_Ship_Set'
4503 );
4504 END IF;
4505 IF g_debug_call > 0 THEN
4506 G_DEBUG_MSG := G_DEBUG_MSG || 'E-27';
4507 END IF;
4508
4509 WHEN FND_API.G_EXC_ERROR THEN
4510 x_return_status := FND_API.G_RET_STS_ERROR;
4511 IF g_debug_call > 0 THEN
4512 G_DEBUG_MSG := G_DEBUG_MSG || 'E-28';
4513 END IF;
4514 WHEN OTHERS THEN
4515 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4516
4517 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4518 THEN
4519 OE_MSG_PUB.Add_Exc_Msg
4520 ( G_PKG_NAME,
4521 'Ship_Confirm_Ship_Set'
4522 );
4523 END IF;
4524
4525 IF l_debug_level > 0 THEN
4526 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 100 ) , 1 ) ;
4527 END IF;
4528 IF g_debug_call > 0 THEN
4529 G_DEBUG_MSG := G_DEBUG_MSG || 'E-29';
4530 END IF;
4531 NULL;
4532
4533 END Ship_Confirm_Ship_Set;
4534
4535 PROCEDURE Process_Ship_Confirm
4536 (
4537 p_process_id IN NUMBER
4538 , p_process_type IN VARCHAR2
4539 , x_return_status OUT NOCOPY VARCHAR2
4540
4541 )
4542 IS
4543 l_line_rec OE_ORDER_PUB.line_rec_type;
4544 l_line_id NUMBER;
4545 l_ordered_quantity NUMBER;
4546 l_shipped_quantity NUMBER;
4547 l_x_return_status VARCHAR2(1);
4548 l_msg_count NUMBER;
4549 l_msg_data VARCHAR2(2000);
4550 l_tolerance_quantity_below NUMBER;
4551 l_tolerance_quantity_above NUMBER;
4552 l_old_recursion_mode VARCHAR2(1) := OE_GLOBALS.G_RECURSION_MODE;
4553
4554 --
4555 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4556 --
4557 BEGIN
4558
4559 IF l_debug_level > 0 THEN
4560 -- debug level changed to 0.5 for bug 13435459
4561 oe_debug_pub.add( 'ENTERING OE_SHIPPING_INTEGRATION_PVT.PROCESS_SHIP_CONFIRM '|| P_PROCESS_TYPE , 0.5 ) ;
4562 END IF;
4563
4564 -- Setting the Global variable OE_GLOBALS.G_RECURSION_MODE to Y so that the
4565 -- Price request for "SHIP" event is not triggered before COST records are
4566 -- inserted into OE_PRICE_ADJUSTMENTS.
4567
4568 IF g_debug_call > 0 THEN
4569 G_DEBUG_MSG := G_DEBUG_MSG || '12-1';
4570 END IF;
4571
4572 IF OE_GLOBALS.G_RECURSION_MODE <> 'Y' THEN
4573 -- OE_GLOBALS.G_RECURSION_MODE := 'Y';
4574 null;
4575 END IF;
4576
4577
4578 IF p_process_type = 'ATO' AND
4579 (p_process_id IS NOT NULL AND
4580 p_process_id <> FND_API.G_MISS_NUM) THEN
4581
4582 -- Call procedure for Ship Confirmation of ATO
4583 Ship_Confirm_ATO
4584 (
4585 p_line_id => p_process_id,
4586 x_return_status => l_x_return_status
4587 );
4588 IF l_x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4589 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4590 ELSIF l_x_return_status = FND_API.G_RET_STS_ERROR THEN
4591 RAISE FND_API.G_EXC_ERROR;
4592 END IF;
4593
4594 ELSIF p_process_type = 'PTO_KIT' AND
4595 (p_process_id IS NOT NULL AND
4596 p_process_id <> FND_API.G_MISS_NUM) THEN
4597
4598 -- Call procedure for Ship Confirmation of PTO and KIT
4599
4600
4601 Ship_Confirm_PTO_KIT
4602 (
4603 p_top_model_line_id => p_process_id,
4604 x_return_status => l_x_return_status
4605 );
4606 IF l_x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4607 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4608 ELSIF l_x_return_status = FND_API.G_RET_STS_ERROR THEN
4609 RAISE FND_API.G_EXC_ERROR;
4610 END IF;
4611
4612
4613 ELSIF p_process_type = 'SHIP_SET' AND
4614 (p_process_id IS NOT NULL AND
4615 p_process_id <> FND_API.G_MISS_NUM) THEN
4616
4617 -- Call procedure for Ship Confirmation of SHIP_SET
4618
4619 Ship_Confirm_Ship_Set
4620 (
4621 p_ship_set_id => p_process_id,
4622 x_return_status => l_x_return_status
4623 );
4624 IF l_x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4626 ELSIF l_x_return_status = FND_API.G_RET_STS_ERROR THEN
4627 RAISE FND_API.G_EXC_ERROR;
4628 END IF;
4629 ELSE
4630
4631 -- Call procedure for Ship Confirmation of Standard Line
4632 IF g_debug_call > 0 THEN
4633 G_DEBUG_MSG := G_DEBUG_MSG || '12-5';
4634 END IF;
4635
4636 Ship_Confirm_Standard_Line
4637 (
4638 p_line_id => p_process_id,
4639 x_return_status => l_x_return_status
4640 );
4641
4642 IF l_x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4643 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4644 ELSIF l_x_return_status = FND_API.G_RET_STS_ERROR THEN
4645 RAISE FND_API.G_EXC_ERROR;
4646 END IF;
4647
4648
4649 END IF;
4650
4651 x_return_status := FND_API.G_RET_STS_SUCCESS;
4652 IF l_old_recursion_mode <> OE_GLOBALS.G_RECURSION_MODE THEN
4653 -- OE_GLOBALS.G_RECURSION_MODE := l_old_recursion_mode;
4654 null;
4655 END IF;
4656
4657 IF l_debug_level > 0 THEN
4658 -- debug level changed to 0.5 for bug 13435459
4659 oe_debug_pub.add( 'RETURNED FROM OE_SHIPPING_INTEGRATION_PVT.PROCESS_SHIP_CONFIRM '|| X_RETURN_STATUS , 0.5 ) ;
4660 END IF;
4661
4662 EXCEPTION
4663 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4664 IF l_old_recursion_mode <> OE_GLOBALS.G_RECURSION_MODE THEN
4665 -- OE_GLOBALS.G_RECURSION_MODE := l_old_recursion_mode;
4666 null;
4667 END IF;
4668
4669 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4670
4671 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4672 THEN
4673 OE_MSG_PUB.Add_Exc_Msg
4674 ( G_PKG_NAME,
4675 'Process_Ship_Confirm'
4676 );
4677 END IF;
4678 IF g_debug_call > 0 THEN
4679 G_DEBUG_MSG := G_DEBUG_MSG || 'E-30';
4680 END IF;
4681
4682 WHEN FND_API.G_EXC_ERROR THEN
4683 IF l_old_recursion_mode <> OE_GLOBALS.G_RECURSION_MODE THEN
4684 -- OE_GLOBALS.G_RECURSION_MODE := l_old_recursion_mode;
4685 null;
4686 END IF;
4687 x_return_status := FND_API.G_RET_STS_ERROR;
4688 IF g_debug_call > 0 THEN
4689 G_DEBUG_MSG := G_DEBUG_MSG || 'E31';
4690 END IF;
4691 WHEN OTHERS THEN
4692 IF l_old_recursion_mode <> OE_GLOBALS.G_RECURSION_MODE THEN
4693 -- OE_GLOBALS.G_RECURSION_MODE := l_old_recursion_mode;
4694 null;
4695 END IF;
4696 IF g_debug_call > 0 THEN
4697 G_DEBUG_MSG := G_DEBUG_MSG || 'E-32';
4698 END IF;
4699 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4700
4701 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4702 THEN
4703 OE_MSG_PUB.Add_Exc_Msg
4704 ( G_PKG_NAME,
4705 'Process_Ship_Confirm'
4706 );
4707 END IF;
4708
4709 IF l_debug_level > 0 THEN
4710 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 100 ) , 1 ) ;
4711 END IF;
4712 NULL;
4713
4714 END Process_Ship_Confirm;
4715
4716
4717 /*-------------------------------------------------------------
4718 PROCEDURE Process_Shipping_Activity
4719
4720 This procedure is called from workflow.
4721 It broadly has 3 sections,
4722 external/return lines
4723 SMC lines
4724 non SMC and standard lines
4725
4726 Change Record : bug fix 3539694 - included items if created
4727 by scheduling wf activity, bypass hvop.
4728 --------------------------------------------------------------*/
4729 PROCEDURE Process_Shipping_Activity
4730 ( p_api_version_number IN NUMBER
4731 ,p_line_id IN NUMBER
4732 ,x_result_out OUT NOCOPY VARCHAR2
4733
4734 ,x_return_status OUT NOCOPY VARCHAR2
4735
4736 ,x_msg_count OUT NOCOPY VARCHAR2
4737
4738 ,x_msg_data OUT NOCOPY VARCHAR2)
4739
4740 IS
4741 l_line_index NUMBER;
4742 l_shipping_activity VARCHAR2(1) := FND_API.G_TRUE;
4743 l_return_status VARCHAR2(1);
4744 l_freeze_method VARCHAR2(30);
4745 l_flow_status_code VARCHAR2(30);
4746 l_line_category_code VARCHAR2(30);
4747 l_source_type_code VARCHAR2(30);
4748 l_ship_model_complete_flag VARCHAR2(1);
4749 l_top_model_line_id NUMBER;
4750 l_shippable_flag VARCHAR2(1);
4751 l_explosion_date DATE;
4752 l_ato_line_id NUMBER;
4753 l_line_id NUMBER;
4754 l_item_type_code VARCHAR2(30);
4755 l_header_id NUMBER;
4756 l_model_remnant_flag VARCHAR2(1);
4757 l_link_to_line_id NUMBER;
4758 l_parent_explosion_date DATE;
4759 l_enforce_smc_flag VARCHAR2(1) := 'N';
4760 l_ship_from_org_id NUMBER;
4761 l_count NUMBER;
4762 --
4763 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4764 --
4765
4766 CURSOR c_lines IS
4767 SELECT line_id
4768 FROM oe_order_lines,wf_item_activity_statuses wias,
4769 wf_process_activities wpa
4770 WHERE header_id = l_header_id
4771 AND top_model_line_id = l_top_model_line_id
4772 AND link_to_line_id = p_line_id
4773 AND item_type_code = 'INCLUDED'
4774 AND wias.item_type = 'OEOL'
4775 AND wias.item_key = to_char(line_id)
4776 AND wias.process_activity = wpa.instance_id
4777 AND wpa.activity_name = 'SHIP_LINE'
4778 AND wias.activity_status = 'NOTIFIED'
4779 AND shipping_interfaced_flag = 'N'
4780 ORDER BY line_number,shipment_number,nvl(option_number,-1),
4781 nvl(component_number,-1);
4782
4783 BEGIN
4784
4785 IF l_debug_level > 0 THEN
4786 oe_debug_pub.add( 'ENTERING PROCESS_SHIPPING_ACTIVITY '|| P_LINE_ID , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
4787 END IF;
4788
4789 IF g_debug_call > 0 THEN
4790 G_DEBUG_MSG := G_DEBUG_MSG || '201';
4791 END IF;
4792
4793 IF OE_BULK_WF_UTIL.G_LINE_INDEX IS NOT NULL THEN
4794 IF l_debug_level > 0 THEN
4795 oe_debug_pub.add
4796 ('bulk call?? line id '|| OE_BULK_ORDER_PVT.G_LINE_REC.line_id
4797 (OE_BULK_WF_UTIL.G_LINE_INDEX), 2);
4798 END IF;
4799 END IF;
4800
4801 IF OE_BULK_WF_UTIL.G_LINE_INDEX IS NOT NULL AND
4802 OE_BULK_ORDER_PVT.G_LINE_REC.line_id(OE_BULK_WF_UTIL.G_LINE_INDEX) =
4803 p_line_id THEN
4804 IF l_debug_level > 0 THEN
4805 oe_debug_pub.add
4806 ('2 shipping bulk mode '|| OE_BULK_WF_UTIL.G_LINE_INDEX, 4);
4807 END IF;
4808
4809 l_line_category_code := OE_BULK_ORDER_PVT.G_LINE_REC.line_category_code
4810 (OE_BULK_WF_UTIL.G_LINE_INDEX);
4811
4812 l_source_type_code := OE_BULK_ORDER_PVT.G_LINE_REC.source_type_code
4813 (OE_BULK_WF_UTIL.G_LINE_INDEX);
4814
4815 --oe_debug_pub.add('here 2: ', 2);
4816
4817 l_ship_model_complete_flag
4818 := OE_BULK_ORDER_PVT.G_LINE_REC.ship_model_complete_flag
4819 (OE_BULK_WF_UTIL.G_LINE_INDEX);
4820
4821 l_top_model_line_id := OE_BULK_ORDER_PVT.G_LINE_REC.top_model_line_id
4822 (OE_BULK_WF_UTIL.G_LINE_INDEX);
4823 l_link_to_line_id := OE_BULK_ORDER_PVT.G_LINE_REC.link_to_line_id
4824 (OE_BULK_WF_UTIL.G_LINE_INDEX);
4825
4826 l_ato_line_id := OE_BULK_ORDER_PVT.G_LINE_REC.ato_line_id
4827 (OE_BULK_WF_UTIL.G_LINE_INDEX);
4828 l_model_remnant_flag := OE_BULK_ORDER_PVT.G_LINE_REC.model_remnant_flag
4829 (OE_BULK_WF_UTIL.G_LINE_INDEX);
4830
4831 l_shippable_flag := OE_BULK_ORDER_PVT.G_LINE_REC.shippable_flag
4832 (OE_BULK_WF_UTIL.G_LINE_INDEX);
4833
4834 l_explosion_date := OE_BULK_ORDER_PVT.G_LINE_REC.explosion_date
4835 (OE_BULK_WF_UTIL.G_LINE_INDEX);
4836
4837 l_line_id := OE_BULK_ORDER_PVT.G_LINE_REC.line_id
4838 (OE_BULK_WF_UTIL.G_LINE_INDEX);
4839 l_item_type_code := OE_BULK_ORDER_PVT.G_LINE_REC.item_type_code
4840 (OE_BULK_WF_UTIL.G_LINE_INDEX);
4841 --oe_debug_pub.add('here 3: ', 2);
4842 l_header_id := OE_BULK_ORDER_PVT.G_LINE_REC.header_id
4843 (OE_BULK_WF_UTIL.G_LINE_INDEX);
4844
4845 --oe_debug_pub.add('here 4: ', 2);
4846 ELSE
4847 SELECT line_category_code,
4848 source_type_code,
4849 ship_model_complete_flag,
4850 top_model_line_id,
4851 link_to_line_id,
4852 shippable_flag,
4853 explosion_date,
4854 ato_line_id,
4855 line_id,
4856 item_type_code,
4857 header_id,
4858 model_remnant_flag,
4859 ship_from_org_id
4860
4861 INTO l_line_category_code,
4862 l_source_type_code,
4863 l_ship_model_complete_flag,
4864 l_top_model_line_id,
4865 l_link_to_line_id,
4866 l_shippable_flag,
4867 l_explosion_date,
4868 l_ato_line_id,
4869 l_line_id,
4870 l_item_type_code,
4871 l_header_id,
4872 l_model_remnant_flag,
4873 l_ship_from_org_id
4874 FROM OE_ORDER_LINES_ALL
4875 WHERE LINE_ID = p_line_id;
4876
4877 END IF; -- bulk mode switch
4878
4879 l_model_remnant_flag := nvl(l_model_remnant_flag, 'N');
4880 l_shippable_flag := nvl(l_shippable_flag, 'N');
4881 l_ship_model_complete_flag := nvl(l_ship_model_complete_flag, 'N');
4882
4883 x_result_out := 'NOTIFIED:#NULL';
4884
4885 IF g_debug_call > 0 THEN
4886 G_DEBUG_MSG := G_DEBUG_MSG || '204';
4887 END IF;
4888 IF l_line_category_code = 'RETURN' THEN
4889
4890 IF l_debug_level > 0 THEN
4891 oe_debug_pub.add( 'IT IS A RETURN LINE NOT ELIGIBLE FOR SHIPPING' , 3 ) ;
4892 END IF;
4893 x_result_out := 'COMPLETE:NON_SHIPPABLE';
4894 GOTO END_SHIPPING_PROCESSING;
4895
4896 END IF;
4897
4898 IF l_source_type_code = 'EXTERNAL' THEN
4899 IF l_debug_level > 0 THEN
4900 oe_debug_pub.add( 'IT IS A DROP SHIP LINE' , 3 ) ;
4901 END IF;
4902
4903 IF nvl(l_shippable_flag, 'N') = 'N' THEN
4904 x_result_out := 'COMPLETE:NON_SHIPPABLE';
4905 END IF;
4906
4907 GOTO END_SHIPPING_PROCESSING;
4908 END IF;
4909
4910 ------------ external and return lines done ------------
4911
4912 IF l_shippable_flag = 'N' THEN
4913 IF l_debug_level > 0 THEN
4914 oe_debug_pub.add( 'NON SHIPPABLE LINE ' || L_ITEM_TYPE_CODE , 3 ) ;
4915 END IF;
4916 x_result_out := 'COMPLETE:NON_SHIPPABLE';
4917 END IF;
4918
4919 IF g_debug_call > 0 THEN
4920 G_DEBUG_MSG := G_DEBUG_MSG || '205';
4921 END IF;
4922
4923 IF OE_BULK_WF_UTIL.G_LINE_INDEX IS NOT NULL AND
4924 OE_Code_Control.Code_Release_Level >= '110510' AND
4925 OE_BULK_ORDER_PVT.G_LINE_REC.line_id(OE_BULK_WF_UTIL.G_LINE_INDEX) =
4926 p_line_id THEN
4927 IF l_debug_level > 0 THEN
4928 oe_debug_pub.add('3 shipping bulk mode',5);
4929 END IF;
4930
4931 IF OE_BULK_ORDER_PVT.G_LINE_REC.shippable_flag
4932 (OE_BULK_WF_UTIL.G_LINE_INDEX) = 'Y' THEN
4933
4934 IF l_debug_level > 0 THEN
4935 oe_debug_pub.add
4936 (OE_BULK_ORDER_PVT.G_LINE_REC.shipping_eligible_flag.COUNT,5);
4937 END IF;
4938
4939 IF OE_BULK_ORDER_PVT.G_LINE_REC.shipping_eligible_flag.COUNT <
4940 OE_BULK_ORDER_PVT.G_LINE_REC.line_id.COUNT THEN
4941
4942 --------------------------------------------------------
4943 -- 1st eligible line for wsh interface, so extend all.
4944 --------------------------------------------------------
4945
4946 l_count := OE_BULK_ORDER_PVT.G_LINE_REC.line_id.COUNT -
4947 OE_BULK_ORDER_PVT.G_LINE_REC.shipping_eligible_flag.COUNT;
4948
4949 IF l_debug_level > 0 THEN
4950 oe_debug_pub.add('bulk wsh attribs extend begin '
4951 || OE_BULK_ORDER_PVT.G_LINE_REC.line_id.COUNT
4952 || ' ' || l_count, 2);
4953 END IF;
4954
4955
4956 IF l_count > 0 THEN
4957 OE_BULK_ORDER_PVT.G_LINE_REC.shipping_eligible_flag.extend(l_count);
4958 OE_BULK_ORDER_PVT.G_LINE_REC.sold_to_contact_id.extend(l_count);
4959 OE_BULK_ORDER_PVT.G_LINE_REC.source_header_number.extend(l_count);
4960 OE_BULK_ORDER_PVT.G_LINE_REC.order_date_type_code.extend(l_count);
4961 OE_BULK_ORDER_PVT.G_LINE_REC.source_header_type_id.extend(l_count);
4962 OE_BULK_ORDER_PVT.G_LINE_REC.source_header_type_name.extend(l_count);
4963 OE_BULK_ORDER_PVT.G_LINE_REC.source_line_number.extend(l_count);
4964 OE_BULK_ORDER_PVT.G_LINE_REC.currency_code.extend(l_count);
4965
4966 OE_BULK_ORDER_PVT.G_LINE_REC.INTERMED_SHIP_TO_CONTACT_ID.extend(l_count);
4967 OE_BULK_ORDER_PVT.G_LINE_REC.INTERMED_SHIP_TO_ORG_ID.extend(l_count);
4968
4969 OE_BULK_ORDER_PVT.G_LINE_REC.item_description.extend(l_count);
4970 OE_BULK_ORDER_PVT.G_LINE_REC.hazard_class_id.extend(l_count);
4971 OE_BULK_ORDER_PVT.G_LINE_REC.weight_uom_code.extend(l_count);
4972 OE_BULK_ORDER_PVT.G_LINE_REC.volume_uom_code.extend(l_count);
4973 OE_BULK_ORDER_PVT.G_LINE_REC.requested_quantity_uom.extend(l_count);
4974 OE_BULK_ORDER_PVT.G_LINE_REC.mtl_unit_weight.extend(l_count);
4975 OE_BULK_ORDER_PVT.G_LINE_REC.mtl_unit_volume.extend(l_count);
4976 OE_BULK_ORDER_PVT.G_LINE_REC.pickable_flag.extend(l_count);
4977
4978 IF l_debug_level > 0 THEN
4979 oe_debug_pub.add('bulk wsh attribs extended '|| l_count,2);
4980 END IF;
4981 END IF;
4982 END IF; -- if not yet extended
4983
4984 --------------------------------------------------------
4985 -- Assign values to this line
4986 --------------------------------------------------------
4987 OE_BULK_ORDER_PVT.G_LINE_REC.shipping_eligible_flag
4988 (OE_BULK_WF_UTIL.G_LINE_INDEX) := 'Y';
4989
4990 --oe_debug_pub.add('here 5: ', 2);
4991
4992 l_count := OE_BULK_CACHE.Load_Item
4993 ( p_key1
4994 => OE_BULK_ORDER_PVT.G_LINE_REC.inventory_item_id
4995 (OE_BULK_WF_UTIL.G_LINE_INDEX)
4996 ,p_key2
4997 => OE_BULK_ORDER_PVT.G_LINE_REC.ship_from_org_id
4998 (OE_BULK_WF_UTIL.G_LINE_INDEX));
4999
5000 --oe_debug_pub.add('here 5-1: '
5001 --|| OE_BULK_ORDER_PVT.G_LINE_REC.ordered_item_id
5002 --(OE_BULK_WF_UTIL.G_LINE_INDEX), 2);
5003
5004 IF g_debug_call > 0 THEN
5005 G_DEBUG_MSG := G_DEBUG_MSG || '208';
5006 END IF;
5007
5008 OE_BULK_ORDER_PVT.G_LINE_REC.item_description
5009 (OE_BULK_WF_UTIL.G_LINE_INDEX) :=
5010 OE_BULK_CACHE.G_ITEM_TBL(l_count).item_description;
5011
5012 OE_BULK_ORDER_PVT.G_LINE_REC.hazard_class_id
5013 (OE_BULK_WF_UTIL.G_LINE_INDEX) :=
5014 OE_BULK_CACHE.G_ITEM_TBL(l_count).hazard_class_id;
5015
5016 OE_BULK_ORDER_PVT.G_LINE_REC.weight_uom_code
5017 (OE_BULK_WF_UTIL.G_LINE_INDEX) :=
5018 OE_BULK_CACHE.G_ITEM_TBL(l_count).weight_uom_code;
5019
5020 --oe_debug_pub.add('here 6: ', 2);
5021
5022 OE_BULK_ORDER_PVT.G_LINE_REC.volume_uom_code
5023 (OE_BULK_WF_UTIL.G_LINE_INDEX) :=
5024 OE_BULK_CACHE.G_ITEM_TBL(l_count).volume_uom_code;
5025
5026 --oe_debug_pub.add('here 6-1: ', 2);
5027
5028 OE_BULK_ORDER_PVT.G_LINE_REC.requested_quantity_uom
5029 (OE_BULK_WF_UTIL.G_LINE_INDEX) :=
5030 OE_BULK_CACHE.G_ITEM_TBL(l_count).primary_uom_code;
5031
5032 --oe_debug_pub.add('here 6-2: ', 2);
5033
5034 OE_BULK_ORDER_PVT.G_LINE_REC.mtl_unit_weight
5035 (OE_BULK_WF_UTIL.G_LINE_INDEX) :=
5036 OE_BULK_CACHE.G_ITEM_TBL(l_count).unit_weight;
5037
5038 --oe_debug_pub.add('here 6-3: ', 2);
5039
5040 OE_BULK_ORDER_PVT.G_LINE_REC.mtl_unit_volume
5041 (OE_BULK_WF_UTIL.G_LINE_INDEX) :=
5042 OE_BULK_CACHE.G_ITEM_TBL(l_count).unit_volume;
5043
5044 --oe_debug_pub.add('here 6-4: ', 2);
5045
5046 OE_BULK_ORDER_PVT.G_LINE_REC.pickable_flag
5047 (OE_BULK_WF_UTIL.G_LINE_INDEX) :=
5048 OE_BULK_CACHE.G_ITEM_TBL(l_count).pickable_flag;
5049
5050 --oe_debug_pub.add('here 7: ', 2);
5051 --------------- from header/ xn types --------------------
5052
5053 OE_BULK_ORDER_PVT.G_LINE_REC.sold_to_contact_id
5054 (OE_BULK_WF_UTIL.G_LINE_INDEX) :=
5055 OE_Bulk_Order_PVT.g_header_rec.sold_to_contact_id
5056 (OE_BULK_WF_UTIL.G_HEADER_INDEX);
5057
5058 OE_BULK_ORDER_PVT.G_LINE_REC.source_header_number
5059 (OE_BULK_WF_UTIL.G_LINE_INDEX) :=
5060 OE_Bulk_Order_PVT.g_header_rec.order_number
5061 (OE_BULK_WF_UTIL.G_HEADER_INDEX);
5062
5063 OE_BULK_ORDER_PVT.G_LINE_REC.order_date_type_code
5064 (OE_BULK_WF_UTIL.G_LINE_INDEX) :=
5065 OE_Bulk_Order_PVT.g_header_rec.order_date_type_code
5066 (OE_BULK_WF_UTIL.G_HEADER_INDEX);
5067
5068 --oe_debug_pub.add('here 8: ', 2);
5069 OE_BULK_ORDER_PVT.G_LINE_REC.source_header_type_id
5070 (OE_BULK_WF_UTIL.G_LINE_INDEX) :=
5071 OE_Bulk_Order_PVT.g_header_rec.order_type_id
5072 (OE_BULK_WF_UTIL.G_HEADER_INDEX);
5073
5074 OE_BULK_ORDER_PVT.G_LINE_REC.currency_code
5075 (OE_BULK_WF_UTIL.G_LINE_INDEX) :=
5076 OE_Bulk_Order_PVT.g_header_rec.transactional_curr_code
5077 (OE_BULK_WF_UTIL.G_HEADER_INDEX);
5078
5079 l_count := OE_BULK_CACHE.Load_Order_Type
5080 ( p_key
5081 => OE_BULK_ORDER_PVT.G_HEADER_REC.order_type_id
5082 (OE_BULK_WF_UTIL.G_HEADER_INDEX));
5083
5084 OE_BULK_ORDER_PVT.G_LINE_REC.source_header_type_name
5085 (OE_BULK_WF_UTIL.G_LINE_INDEX) :=
5086 OE_BULK_CACHE.G_ORDER_TYPE_TBL(l_count).name;
5087
5088 --oe_debug_pub.add('here 9: '|| OE_BULK_ORDER_PVT.G_LINE_REC.source_header_type_name(OE_BULK_WF_UTIL.G_LINE_INDEX), 2);
5089
5090 OE_BULK_ORDER_PVT.G_LINE_REC.source_line_number
5091 (OE_BULK_WF_UTIL.G_LINE_INDEX) :=
5092 RTRIM(OE_BULK_ORDER_PVT.G_LINE_REC.line_number
5093 (OE_BULK_WF_UTIL.G_LINE_INDEX) || '.' ||
5094 OE_BULK_ORDER_PVT.G_LINE_REC.shipment_number
5095 (OE_BULK_WF_UTIL.G_LINE_INDEX) || '.' ||
5096 OE_BULK_ORDER_PVT.G_LINE_REC.option_number
5097 (OE_BULK_WF_UTIL.G_LINE_INDEX) || '.' ||
5098 OE_BULK_ORDER_PVT.G_LINE_REC.component_number
5099 (OE_BULK_WF_UTIL.G_LINE_INDEX) || '.' ||
5100 OE_BULK_ORDER_PVT.G_LINE_REC.service_number
5101 (OE_BULK_WF_UTIL.G_LINE_INDEX),'.');
5102
5103 --oe_debug_pub.add('here 10: ', 2);
5104
5105 IF l_debug_level > 0 THEN
5106 oe_debug_pub.add('eligible for shipping '||
5107 OE_BULK_ORDER_PVT.G_LINE_REC.line_id
5108 (OE_BULK_WF_UTIL.G_LINE_INDEX), 1 ) ;
5109 END IF;
5110
5111 END IF; -- if shippable
5112
5113 IF g_debug_call > 0 THEN
5114 G_DEBUG_MSG := G_DEBUG_MSG || '209';
5115 END IF;
5116 x_return_status := FND_API.G_RET_STS_SUCCESS;
5117 RETURN;
5118 END IF; -- bulk mode
5119 --Start Bug 4094824
5120 BEGIN
5121 SELECT Enforce_Ship_Set_And_Smc
5122 INTO l_enforce_smc_flag
5123 FROM Wsh_Shipping_Parameters
5124 WHERE Organization_Id = l_ship_from_org_Id;
5125 -- Added condition, in case of enforce smc flag is never changed from Checked to Unchecked or
5126 -- vice versa returns null value
5127 --
5128 IF l_enforce_smc_flag IS NULL THEN
5129 l_enforce_smc_flag := 'N';
5130 END IF;
5131 --
5132 IF l_debug_level > 0 THEN
5133 OE_DEBUG_PUB.add('Enforce SMC - flag is '||l_enforce_smc_flag||' for org '||l_ship_from_org_id,1);
5134 END IF;
5135 EXCEPTION
5136 WHEN OTHERS THEN
5137 IF l_debug_level > 0 THEN
5138 oe_debug_pub.add('did not find shipping parameter Enforce Ship Set',1);
5139 END IF;
5140 NULL;
5141 END;
5142 --End Bug 4094824
5143 IF l_item_type_code in ('MODEL', 'CLASS', 'KIT') AND
5144 l_explosion_date IS NULL THEN
5145
5146 l_return_status := OE_Config_Util.Process_Included_Items(
5147 p_line_id => p_line_id
5148 ,p_freeze => TRUE
5149 ,p_process_requests => TRUE);
5150
5151 IF g_debug_call > 0 THEN
5152 G_DEBUG_MSG := G_DEBUG_MSG || '211';
5153 END IF;
5154 IF l_debug_level > 0 THEN
5155 oe_debug_pub.add('After calling explosion : '|| l_return_status , 3 ) ;
5156 END IF;
5157
5158 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5159 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5160 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
5161 RAISE FND_API.G_EXC_ERROR;
5162 END IF;
5163
5164 IF l_ship_model_complete_flag = 'N' OR l_enforce_smc_flag = 'Y' THEN
5165
5166 -- Model or Kit is at Shippping we need to interface
5167 -- the included items to shipping. Interface included items part
5168 -- of Non Sip Model Complete. Process_SMC_Shipping will take care of
5169 -- interfacing SMC.
5170
5171 OE_DEBUG_PUB.Add('Interface Included Items part of NON SMC to shipping', 1);
5172
5173 FOR c_line IN C_LINES
5174 LOOP
5175
5176 IF l_debug_level > 0 THEN
5177 OE_DEBUG_PUB.Add('Interfacing Line :'||c_line.line_id);
5178 END IF;
5179
5180 Update_Shipping_PVT(p_line_id => c_line.line_id,
5181 p_shipping_activity => l_shipping_activity,
5182 x_return_status => l_return_status);
5183 END LOOP;
5184 END IF;
5185
5186 END IF;
5187
5188 -- Hold all the included items if they are part of
5189 -- SMC or NON SMC.
5190
5191 IF l_item_type_code = 'INCLUDED' AND
5192 (l_ship_model_complete_flag = 'N' OR l_enforce_smc_flag = 'Y') THEN
5193
5194 IF g_debug_call > 0 THEN
5195 G_DEBUG_MSG := G_DEBUG_MSG || '211';
5196 END IF;
5197
5198 SELECT explosion_date
5199 INTO l_parent_explosion_date
5200 FROM oe_order_lines
5201 WHERE top_model_line_id = l_top_model_line_id
5202 AND header_id = l_header_id
5203 AND line_id = l_link_to_line_id;
5204
5205 IF l_parent_explosion_date IS NULL THEN
5206
5207 IF l_debug_level > 0 THEN
5208 OE_DEBUG_PUB.Add('Included Item at Shipping:'||l_line_id, 5 ) ;
5209 END IF;
5210
5211 -- Update Flow Status Code as Ship Line is Notified.
5212 -- Donot update shipping now!!
5213
5214 GOTO END_SHIPPING_PROCESSING;
5215 END IF;
5216
5217 END IF;
5218
5219
5220 IF l_ship_model_complete_flag = 'Y' AND
5221 l_enforce_smc_flag = 'N' AND
5222 l_top_model_line_id IS NOT NULL AND
5223 l_model_remnant_flag = 'N' THEN
5224
5225 IF l_debug_level > 0 THEN
5226 oe_debug_pub.add( 'processing line is part of SMC PTO at shipping activity' , 5 ) ;
5227 END IF;
5228
5229 Process_SMC_Shipping
5230 (p_line_id => p_line_id
5231 ,p_top_model_line_id => l_top_model_line_id
5232 ,x_return_status => l_return_status);
5233
5234 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5236 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
5237 RAISE FND_API.G_EXC_ERROR;
5238 END IF;
5239
5240 ELSE -- standard, non-smc etc
5241
5242 IF l_shippable_flag = 'Y' THEN
5243
5244 IF l_debug_level > 0 THEN
5245 oe_debug_pub.add( 'shippable line is at shipping activity, item type '||L_ITEM_TYPE_CODE , 5 ) ;
5246 END IF;
5247 IF g_debug_call > 0 THEN
5248 G_DEBUG_MSG := G_DEBUG_MSG || '213';
5249 END IF;
5250
5251 Update_Shipping_PVT(p_line_id => l_line_id,
5252 p_shipping_activity => l_shipping_activity,
5253 x_return_status => l_return_status);
5254
5255 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5257 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
5258 RAISE FND_API.G_EXC_ERROR;
5259 END IF;
5260 END IF;
5261
5262 END IF; -- big IF smc etc.
5263
5264 --Added the If condition to fix the bug 14826530
5265 IF g_debug_call > 0 THEN
5266 G_DEBUG_MSG := G_DEBUG_MSG || 'Shp-14';
5267 END IF;
5268
5269 <<END_SHIPPING_PROCESSING>>
5270
5271 IF x_result_out = 'NOTIFIED:#NULL' THEN
5272
5273 IF l_debug_level > 0 THEN
5274 oe_debug_pub.add( 'CALLING FLOW STATUS API ' , 3 ) ;
5275 END IF;
5276
5277 IF l_source_type_code = 'EXTERNAL' THEN
5278 l_flow_status_code := 'AWAITING_RECEIPT';
5279 ELSE
5280 l_flow_status_code := 'AWAITING_SHIPPING';
5281 END IF;
5282
5283 IF ((l_item_type_code = 'CONFIG' OR
5284 (l_item_type_code IN ('STANDARD','OPTION','INCLUDED') AND --9775352
5285 l_ato_line_id = l_line_id)) AND
5286 l_source_type_code = 'INTERNAL') OR
5287 nvl(l_shippable_flag,'N') = 'N' THEN
5288
5289 IF l_debug_level > 0 THEN
5290 oe_debug_pub.add( 'DO NOT CALL FLOW STATUS API ' , 3 ) ;
5291 END IF;
5292
5293 IF g_debug_call > 0 THEN
5294 G_DEBUG_MSG := G_DEBUG_MSG || '215';
5295 END IF;
5296
5297 ELSE
5298
5299 OE_Order_WF_Util.Update_Flow_Status_Code
5300 (p_header_id => l_header_id,
5301 p_line_id => l_line_id,
5302 p_flow_status_code => l_flow_status_code,
5303 x_return_status => l_return_status);
5304
5305 IF l_debug_level > 0 THEN
5306 oe_debug_pub.add( 'RETURN STS FLOW STATUS API '||L_RETURN_STATUS , 3 ) ;
5307 END IF;
5308
5309 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5310 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5311 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
5312 RAISE FND_API.G_EXC_ERROR;
5313 END IF;
5314
5315 END IF;
5316
5317 END IF; -- if result_out = notified
5318
5319 IF g_debug_call > 0 THEN
5320 G_DEBUG_MSG := G_DEBUG_MSG || '216';
5321 END IF;
5322
5323 x_return_status := FND_API.G_RET_STS_SUCCESS;
5324
5325 IF l_debug_level > 0 THEN
5326 oe_debug_pub.add( 'EXITING PROCESS_SHIPPING_ACTIVITY SUCCESSFULLY ' , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
5327 END IF;
5328
5329 EXCEPTION
5330
5331 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5332 IF l_debug_level > 0 THEN
5333 oe_debug_pub.add( 'PROCESS_SHIPPING_ACTIVITY : UNEXPECTED ERROR' , 1 ) ;
5334 END IF;
5335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5336
5337 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5338 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
5339 'Process_Shipping_Activity');
5340 END IF;
5341 IF g_debug_call > 0 THEN
5342 G_DEBUG_MSG := G_DEBUG_MSG || 'E-33';
5343 END IF;
5344
5345 WHEN FND_API.G_EXC_ERROR THEN
5346 IF l_debug_level > 0 THEN
5347 oe_debug_pub.add( 'PROCESS_SHIPPING_ACTIVITY : EXE ERROR' , 1 ) ;
5348 END IF;
5349 x_return_status := FND_API.G_RET_STS_ERROR;
5350 IF g_debug_call > 0 THEN
5351 G_DEBUG_MSG := G_DEBUG_MSG || 'E-34';
5352 END IF;
5353
5354 WHEN OTHERS THEN
5355 IF l_debug_level > 0 THEN
5356 oe_debug_pub.add( 'PROCESS_SHIPPING_ACTIVITY : OTHERS ERROR' , 1 ) ;
5357 END IF;
5358
5359 IF l_debug_level > 0 THEN
5360 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 100 ) , 1 ) ;
5361 END IF;
5362 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5363
5364 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5365 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
5366 'Process_Shipping_Activity');
5367 END IF;
5368
5369 -- Get message count and data
5370
5371 OE_MSG_PUB.Count_And_Get
5372 ( p_count => x_msg_count
5373 ,p_data => x_msg_data);
5374 IF g_debug_call > 0 THEN
5375 G_DEBUG_MSG := G_DEBUG_MSG || 'E-35';
5376 END IF;
5377
5378 END Process_Shipping_Activity;
5379
5380
5381 /*-------------------------------------------------------------
5382 PROCEDURE Process_SMC_Shipping
5383
5384 Do we need to complete the activity for MODEL/CLASS ??
5385 Yes for now. It will be done in Update Shipping procedure
5386 on the change of explosion date because of explosion.
5387 --------------------------------------------------------------*/
5388 PROCEDURE Process_SMC_Shipping
5389 ( p_line_id IN NUMBER
5390 ,p_top_model_line_id IN NUMBER
5391 ,x_return_status OUT NOCOPY VARCHAR2)
5392
5393 IS
5394 l_line_tbl OE_Order_Pub.Line_Tbl_Type;
5395 l_update_lines_tbl OE_ORDER_PUB.Request_Tbl_Type;
5396 l_update_lines_index NUMBER := 0;
5397 l_lines_not_at_ship VARCHAR2(1) := FND_API.G_FALSE;
5398 l_activity_status_code VARCHAR2(8);
5399 l_dummy VARCHAR2(52);
5400 l_line_index NUMBER;
5401 --
5402 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5403 --
5404 BEGIN
5405
5406 IF l_debug_level > 0 THEN
5407 oe_debug_pub.add( 'ENTERING PROCESS_SMC_SHIPPING' , 3 ) ;
5408 END IF;
5409
5410 x_return_status := FND_API.G_RET_STS_SUCCESS;
5411
5412 OE_Config_Util.Query_Options
5413 (p_top_model_line_id => p_top_model_line_id,
5414 x_line_tbl => l_line_tbl);
5415
5416 IF l_debug_level > 0 THEN
5417 oe_debug_pub.add( 'LINES IN THE SMC_PTO '|| L_LINE_TBL.COUNT , 3 ) ;
5418 END IF;
5419
5420 l_line_index := l_line_tbl.FIRST;
5421
5422 IF g_debug_call > 0 THEN
5423 G_DEBUG_MSG := G_DEBUG_MSG || '13-2';
5424 END IF;
5425
5426 WHILE l_line_index is NOT NULL
5427 LOOP
5428
5429 IF l_debug_level > 0 THEN
5430 oe_debug_pub.add( 'PROCESSING LINE : '|| L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID || ' '|| L_LINE_TBL ( L_LINE_INDEX ) .ITEM_TYPE_CODE ||'/' ||L_LINE_TBL ( L_LINE_INDEX ) .SHIPPABLE_FLAG , 3 ) ;
5431 END IF;
5432
5433 l_dummy := null;
5434
5435 IF nvl(l_line_tbl(l_line_index).shippable_flag,'N') = 'Y' THEN
5436
5437 IF l_line_tbl(l_line_index).shipping_interfaced_flag = 'Y' THEN
5438
5439 IF l_debug_level > 0 THEN
5440 oe_debug_pub.add( 'THE LINE IS SHIPPING INTERFACED ' , 5 ) ;
5441 END IF;
5442
5443 BEGIN
5444
5445 SELECT 'Line is Released , staged or confirmed..'
5446 INTO l_dummy
5447 FROM WSH_DELIVERY_DETAILS
5448 WHERE SOURCE_LINE_ID = l_line_tbl(l_line_index).line_id
5449 AND RELEASED_STATUS <> 'N';
5450
5451
5452 EXCEPTION
5453 WHEN NO_DATA_FOUND THEN
5454 IF l_debug_level > 0 THEN
5455 oe_debug_pub.add( 'SHIPPING NEEDS UPDATE'||L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID , 3 ) ;
5456 END IF;
5457 l_dummy := 'U';
5458
5459 WHEN OTHERS THEN
5460 IF l_debug_level > 0 THEN
5461 oe_debug_pub.add( 'ONE/MORE LINES RELEASED IN THIS BATCH..' , 5 ) ;
5462 END IF;
5463
5464 END;
5465
5466 ELSE
5467
5468 IF l_debug_level > 0 THEN
5469 oe_debug_pub.add( 'THE LINE IS NOT YET INTERFACED TO SHIPPING ' , 5 ) ;
5470 END IF;
5471 l_dummy := 'U';
5472 END IF; -- if not interfaced
5473
5474 IF l_dummy = 'U' THEN
5475 l_update_lines_index := l_update_lines_index + 1;
5476 l_update_lines_tbl(l_update_lines_index).entity_id
5477 := l_line_tbl(l_line_index).line_id;
5478 l_update_lines_tbl(l_update_lines_index).param1
5479 := FND_API.G_FALSE;
5480 l_update_lines_tbl(l_update_lines_index).param2
5481 := FND_API.G_FALSE;
5482 l_update_lines_tbl(l_update_lines_index).param4
5483 := FND_API.G_TRUE;
5484 END IF;
5485 END IF; -- if shippable
5486
5487
5488 IF p_line_id <> l_line_tbl(l_line_index).line_id
5489 THEN
5490 BEGIN
5491
5492 IF l_line_tbl(l_line_index).line_id
5493 = nvl(l_line_tbl(l_line_index).ato_line_id,0) AND
5494 l_line_tbl(l_line_index).item_type_code IN ('MODEL','CLASS')
5495 THEN -- Change for bug 1820608
5496
5497 IF l_debug_level > 0 THEN
5498 oe_debug_pub.add( 'AN ATO MODEL LINE '||L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID , 1 ) ;
5499 END IF;
5500
5501
5502 BEGIN
5503 SELECT 'X'
5504 INTO l_dummy
5505 FROM oe_order_lines
5506 WHERE top_model_line_id = p_top_model_line_id
5507 AND ato_line_id = l_line_tbl(l_line_index).ato_line_id
5508 AND item_type_code = 'CONFIG';
5509 EXCEPTION
5510 WHEN no_data_found THEN
5511 IF l_debug_level > 0 THEN
5512 oe_debug_pub.add( 'ATO MODEL NOT PROCESSED IN A SMC' , 1 ) ;
5513 END IF;
5514 l_lines_not_at_ship := FND_API.G_TRUE;
5515
5516 WHEN others THEN
5517 IF l_debug_level > 0 THEN
5518 oe_debug_pub.add( 'ATO MODEL SQL '|| SQLERRM , 1 ) ;
5519 END IF;
5520 END;
5521 ELSE
5522
5523 select activity_status
5524 into l_activity_status_code
5525 from wf_item_activity_statuses wias,
5526 wf_process_activities wpa
5527 where wias.item_type = 'OEOL'
5528 and wias.item_key = to_char(l_line_tbl(l_line_index).line_id)
5529 and wias.process_activity = wpa.instance_id
5530 and wpa.activity_name = 'SHIP_LINE'
5531 and wias.activity_status in ('NOTIFIED','COMPLETE');
5532
5533 END IF; -- if ato.
5534
5535 IF l_debug_level > 0 THEN
5536 oe_debug_pub.add( 'LINE IS AT SHIPPING : ' || L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID || ' ' || L_LINE_TBL ( L_LINE_INDEX ) .ITEM_TYPE_CODE || '--' || L_ACTIVITY_STATUS_CODE , 3 ) ;
5537 END IF;
5538
5539 EXCEPTION
5540
5541 WHEN NO_DATA_FOUND THEN
5542 IF l_debug_level > 0 THEN
5543 oe_debug_pub.add( 'NOT AT SHIPPING: '|| TO_CHAR ( L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID ) || ' '|| L_LINE_TBL ( L_LINE_INDEX ) .ITEM_TYPE_CODE , 3 ) ;
5544 END IF;
5545
5546 l_lines_not_at_ship := FND_API.G_TRUE;
5547
5548 WHEN TOO_MANY_ROWS THEN
5549 IF l_debug_level > 0 THEN
5550 oe_debug_pub.add( 'LINE IS AT SHIPPING TOO MANY ROWS : ' || TO_CHAR ( L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID ) || ' ' || L_LINE_TBL ( L_LINE_INDEX ) .ITEM_TYPE_CODE , 3 ) ;
5551 END IF;
5552
5553 WHEN OTHERS THEN
5554 IF l_debug_level > 0 THEN
5555 oe_debug_pub.add( 'PROCESS SHIPPING ACTIVITY :'||SUBSTR ( SQLERRM , 1 , 200 ) , 3 ) ;
5556 END IF;
5557
5558 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5559 END;
5560
5561 IF l_lines_not_at_ship = FND_API.G_TRUE THEN
5562 IF l_debug_level > 0 THEN
5563 oe_debug_pub.add( 'ALL LINES NOT AT SHIP , RETURNING...' , 1 ) ;
5564 END IF;
5565 RETURN;
5566 END IF;
5567
5568 END IF; -- if line_id <> p_line_id
5569
5570 l_line_index := l_line_tbl.NEXT(l_line_index);
5571
5572 END LOOP;
5573
5574 IF g_debug_call > 0 THEN
5575 G_DEBUG_MSG := G_DEBUG_MSG || '13-7';
5576 END IF;
5577
5578 IF l_debug_level > 0 THEN
5579 oe_debug_pub.add( 'ALL THE SMC LINES ARE AT SHIPPING : ' , 3 ) ;
5580 END IF;
5581 -- Inform shipping that lines are ready to pick release
5582
5583 IF l_update_lines_tbl.count > 0 THEN
5584 IF l_debug_level > 0 THEN
5585 oe_debug_pub.add( 'UPDATE SHIPPING '||L_UPDATE_LINES_TBL.COUNT , 3 ) ;
5586 END IF;
5587
5588 Update_Shipping_From_OE
5589 ( p_update_lines_tbl => l_update_lines_tbl,
5590 x_return_status => x_return_status);
5591
5592 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5594 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
5595 RAISE FND_API.G_EXC_ERROR;
5596 END IF;
5597 IF l_debug_level > 0 THEN
5598 oe_debug_pub.add( 'UPDATE_SHIPPING_FROM_OE : '|| X_RETURN_STATUS , 3 ) ;
5599 END IF;
5600
5601 IF g_debug_call > 0 THEN
5602 G_DEBUG_MSG := G_DEBUG_MSG || '13-8';
5603 END IF;
5604
5605 ELSE
5606 IF l_debug_level > 0 THEN
5607 oe_debug_pub.add( 'DONT CALL UPDATE_SHIPPING_FROM_OE' , 3 ) ;
5608 END IF;
5609 END IF; -- count > 0
5610
5611
5612 -- all non-shippable lines would be waiting, complete them.
5613
5614 l_line_index := l_line_tbl.FIRST;
5615
5616 WHILE l_line_index is NOT NULL
5617 LOOP
5618 IF l_debug_level > 0 THEN
5619 oe_debug_pub.add( 'PROCESSING LINE : '|| TO_CHAR ( L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID ) || ' '|| L_LINE_TBL ( L_LINE_INDEX ) .ITEM_TYPE_CODE ||'/' ||L_LINE_TBL ( L_LINE_INDEX ) .SHIPPABLE_FLAG , 3 ) ;
5620 END IF;
5621
5622 IF nvl(l_line_tbl(l_line_index).shippable_flag,'N') = 'N' AND
5623 OE_Shipping_Integration_PUB.Is_Activity_Shipping
5624 (1.0, l_line_tbl(l_line_index).line_id) = FND_API.G_TRUE AND
5625 p_line_id <> l_line_tbl(l_line_index).line_id THEN
5626
5627 IF l_debug_level > 0 THEN
5628 oe_debug_pub.add( 'COMP SHIP_LINE , NONSHIP: '||L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID , 3 ) ;
5629 END IF;
5630
5631 /* 1739754 */
5632 -- Log a delayed request for Complete Activity
5633
5634 IF l_debug_level > 0 THEN
5635 oe_debug_pub.add( 'COMP ACT DELAYED REQ '|| L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID , 3 ) ;
5636 END IF;
5637
5638 BEGIN
5639 wf_engine.CompleteActivityInternalName
5640 ('OEOL', to_char(l_line_tbl(l_line_index).line_id),
5641 'SHIP_LINE', 'NON_SHIPPABLE',TRUE ); --14599978
5642 EXCEPTION
5643 WHEN OTHERS THEN
5644 oe_debug_pub.add('Workflow complete activity failed. Raising');
5645 RAISE ;
5646 END ;
5647
5648 IF l_debug_level > 0 THEN
5649 oe_debug_pub.add( 'WF_ENGINE.COMPLETEACTIVITYINTERNALNAME ' || TO_CHAR ( L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID ) , 3 ) ;
5650 END IF;
5651
5652 END IF; -- if non shippable in a SMC.
5653
5654 l_line_index := l_line_tbl.NEXT(l_line_index);
5655
5656 END LOOP;
5657
5658 IF l_debug_level > 0 THEN
5659 oe_debug_pub.add( 'LEAVING PROCESS_SMC_SHIPPING' , 3 ) ;
5660 END IF;
5661
5662 EXCEPTION
5663 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5664 IF l_debug_level > 0 THEN
5665 oe_debug_pub.add( 'PROCESS_SMC_SHIPPING : UNEXPECTED ERROR' , 1 ) ;
5666 END IF;
5667 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5668
5669 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5670 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
5671 'Process_SMC_Shipping');
5672 END IF;
5673 IF g_debug_call > 0 THEN
5674 G_DEBUG_MSG := G_DEBUG_MSG || 'Shp-E-36';
5675 END IF;
5676
5677 WHEN FND_API.G_EXC_ERROR THEN
5678 IF l_debug_level > 0 THEN
5679 oe_debug_pub.add( 'PROCESS_SMC_SHIPPING : EXE ERROR' , 1 ) ;
5680 END IF;
5681 x_return_status := FND_API.G_RET_STS_ERROR;
5682 IF g_debug_call > 0 THEN
5683 G_DEBUG_MSG := G_DEBUG_MSG || 'E-37';
5684 END IF;
5685
5686 WHEN OTHERS THEN
5687 IF l_debug_level > 0 THEN
5688 oe_debug_pub.add( 'PROCESS_SMC_SHIPPING : OTHERS ERROR' , 1 ) ;
5689 END IF;
5690
5691 IF l_debug_level > 0 THEN
5692 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 100 ) , 1 ) ;
5693 END IF;
5694 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5695
5696 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5697 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
5698 'Process_SMC_Shipping');
5699 END IF;
5700 IF g_debug_call > 0 THEN
5701 G_DEBUG_MSG := G_DEBUG_MSG || 'E-38';
5702 END IF;
5703
5704 END Process_SMC_Shipping;
5705
5706 /*-------------------------------------------------------------
5707 PROCEDURE Ship_Confirm_Split_Lines
5708 This procedure is used to ship confirm split lines when ship
5709 confirmation of other lines in the line set results in fulfilling
5710 the ordered quantity within or beyond tolerance.
5711
5712 It will be used for standard lines and Config items.
5713 p_line_rec provide details about the line getting ship confirmed
5714 we need to cehck if due shipment of this line if we need to
5715 close other lines.
5716
5717 alcoa bug 2605086 - closing lines in line set
5718 --------------------------------------------------------------*/
5719 PROCEDURE Ship_Confirm_Split_Lines
5720 ( p_line_rec IN OE_Order_Pub.Line_Rec_Type
5721 ,p_shipment_status IN VARCHAR2)
5722 IS
5723 l_line_set_rec OE_ORDER_PUB.line_rec_type;
5724 l_return_status VARCHAR2(1);
5725 l_count NUMBER;
5726
5727 CURSOR split_lines IS
5728 SELECT /* MOAC_SQL_CHANGE */ line_id, line_set_id, ordered_quantity, ordered_quantity2,
5729 order_quantity_uom, ordered_quantity_uom2, inventory_item_id
5730 FROM oe_order_lines_all oe
5731 WHERE line_id in
5732 (SELECT line_id
5733 FROM oe_order_lines_all
5734 WHERE line_set_id = p_line_rec.line_set_id
5735 AND line_id <> p_line_rec.line_id)
5736 AND open_flag = 'Y'
5737 AND shipped_quantity is NULL
5738 -- AND line_id not in
5739 -- (SELECT source_line_id
5740 -- FROM wsh_delivery_details
5741 -- WHERE released_status in ('Y', 'C')
5742 AND line_id in
5743 (SELECT source_line_id
5744 FROM wsh_delivery_details
5745 WHERE source_header_id = oe.header_id);
5746
5747
5748 --
5749 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5750 --
5751 BEGIN
5752
5753 IF l_debug_level > 0 THEN
5754 oe_debug_pub.add
5755 ('entering Ship_Confirm_Split_Lines '||p_line_rec.line_id ,3);
5756 END IF;
5757
5758 IF p_line_rec.shipped_quantity2 is not NULL THEN
5759 l_line_set_rec.shipping_quantity2 := 0;
5760 l_line_set_rec.shipped_quantity2 := 0;
5761 END IF;
5762
5763 IF g_debug_call > 0 THEN
5764 G_DEBUG_MSG := G_DEBUG_MSG || 'Shp-14-1';
5765 END IF;
5766
5767 FOR line_rec in split_lines -- what about notify oc?
5768 LOOP
5769
5770 SELECT count(*)
5771 INTO l_count
5772 FROM wsh_delivery_details
5773 WHERE source_line_id = line_rec.line_id
5774 AND released_status <> 'D';
5775
5776
5777 IF l_count > 0 THEN
5778 IF l_debug_level > 0 THEN
5779 oe_debug_pub.add('ignoring this line, can no close '|| line_rec.line_id , 3 ) ;
5780 END IF;
5781 ELSE
5782
5783 IF l_debug_level > 0 THEN
5784 oe_debug_pub.add('now processing '|| line_rec.line_id, 3 ) ;
5785 END IF;
5786
5787 l_line_set_rec.line_id := line_rec.line_id;
5788 l_line_set_rec.line_set_id := line_rec.line_set_id;
5789 l_line_set_rec.ordered_quantity := line_rec.ordered_quantity;
5790 l_line_set_rec.ordered_quantity2 := line_rec.ordered_quantity2;
5791 l_line_set_rec.order_quantity_uom := line_rec.order_quantity_uom;
5792 l_line_set_rec.ordered_quantity_uom2 := line_rec.ordered_quantity_uom2;
5793 l_line_set_rec.inventory_item_id := line_rec.inventory_item_id;
5794
5795
5796 l_line_set_rec.shipping_quantity := 0;
5797 l_line_set_rec.shipped_quantity := 0;
5798 l_line_set_rec.shipping_quantity_uom := p_line_rec.shipping_quantity_uom;
5799 l_line_set_rec.shipping_quantity_uom2 := p_line_rec.shipping_quantity_uom2; -- INVCONV
5800 l_line_set_rec.actual_shipment_date := p_line_rec.actual_shipment_date;
5801
5802 Call_Notification_Framework
5803 ( p_line_rec => l_line_set_rec
5804 ,p_caller => 'Ship_Confirm_Split_Lines'
5805 ,x_return_status => l_return_status);
5806
5807
5808 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5809 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5810 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
5811 RAISE FND_API.G_EXC_ERROR;
5812 END IF;
5813
5814
5815 UPDATE OE_ORDER_LINES
5816 SET shipping_quantity = l_line_set_rec.shipping_quantity,
5817 shipped_quantity = l_line_set_rec.shipped_quantity,
5818 shipping_quantity2 = l_line_set_rec.shipping_quantity2,
5819 shipped_quantity2 = l_line_set_rec.shipped_quantity2,
5820 shipping_quantity_uom = l_line_set_rec.shipping_quantity_uom,
5821 shipping_quantity_uom2 = l_line_set_rec.shipping_quantity_uom2, -- INVCONV
5822 actual_shipment_date = l_line_set_rec.actual_shipment_date
5823 WHERE line_id = l_line_set_rec.line_id;
5824
5825
5826 IF l_debug_level > 0 THEN
5827 oe_debug_pub.add('line set id '|| line_rec.line_set_id , 3 ) ;
5828 END IF;
5829
5830 Ship_Confirm_Standard_Line
5831 ( p_line_rec => l_line_set_rec,
5832 p_shipment_status => p_shipment_status,
5833 p_check_line_set => 'N',
5834 x_return_status => l_return_status );
5835
5836 IF l_debug_level > 0 THEN
5837 oe_debug_pub.add('ret sts for me '||l_return_status , 3 ) ;
5838 END IF;
5839
5840 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5841 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5842 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
5843 RAISE FND_API.G_EXC_ERROR;
5844 END IF;
5845
5846 END IF; -- if wdd has lines staged/shipped or not deleted
5847
5848 END LOOP;
5849
5850 IF l_debug_level > 0 THEN
5851 oe_debug_pub.add('leaving Ship_Confirm_Split_Lines',3);
5852 END IF;
5853
5854 EXCEPTION
5855 WHEN OTHERS THEN
5856 IF l_debug_level > 0 THEN
5857 oe_debug_pub.add('error in Ship_Confirm_Split_Lines '|| sqlerrm,3);
5858 END IF;
5859 RAISE;
5860 END Ship_Confirm_Split_Lines;
5861
5862
5863 /*-------------------------------------------------------------
5864 PROCEDURE Call_Notification_Framework
5865
5866 Call this procedure to handle the Notification_Framework call
5867 instead of scattering the code all over acorss different
5868 procedures.
5869
5870 not using p_line_rec and p_old_line_rec, will support if
5871 needed - it will take more local variable declarations and copy of
5872 tables.
5873 --------------------------------------------------------------*/
5874 PROCEDURE Call_Notification_Framework
5875 ( p_line_rec IN OE_Order_Pub.Line_Rec_Type
5876 ,p_caller IN VARCHAR2
5877 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
5878 IS
5879 --
5880 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5881 --
5882 I NUMBER := 1;
5883 J NUMBER;
5884 l_line_tbl OE_Order_Pub.Line_Tbl_Type;
5885 l_old_line_tbl OE_Order_Pub.Line_Tbl_Type;
5886
5887
5888 BEGIN
5889
5890 Print_Time
5891 ('entering Call_Notification_Framework ' || p_caller);
5892
5893 x_return_status := FND_API.G_RET_STS_SUCCESS;
5894
5895
5896 IF p_caller = 'Ship_Confirm_Split_Lines' THEN
5897 l_line_tbl(I) := p_line_rec;
5898 l_old_line_tbl(I) := l_line_tbl(I);
5899
5900 l_old_line_tbl(I).shipping_quantity := null;
5901 l_old_line_tbl(I).shipped_quantity := null;
5902 l_old_line_tbl(I).shipping_quantity2 := null;
5903 l_old_line_tbl(I).shipped_quantity2 := null;
5904 l_old_line_tbl(I).shipping_quantity_uom := null;
5905 l_old_line_tbl(I).shipping_quantity_uom2 := null; -- INVCONV
5906 l_old_line_tbl(I).actual_shipment_date := null;
5907 ELSE
5908 RETURN;
5909 END IF;
5910
5911
5912 IF ((OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508') OR
5913 (NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'),'N') = 'Y'))
5914 THEN
5915
5916 I := l_line_tbl.FIRST;
5917
5918 IF g_debug_call > 0 THEN
5919 G_DEBUG_MSG := G_DEBUG_MSG || '14-6';
5920 END IF;
5921 WHILE I is not NULL
5922 LOOP
5923
5924 OE_ORDER_UTIL.Update_Global_Picture
5925 (p_Upd_New_Rec_If_Exists => False,
5926 p_header_id => l_line_tbl(I).header_id,
5927 p_old_line_rec => l_old_line_tbl(I),
5928 p_line_rec => l_line_tbl(I),
5929 p_line_id => l_line_tbl(I).line_id,
5930 x_index => J,
5931 x_return_status => x_return_status);
5932
5933 IF l_debug_level > 0 THEN
5934 oe_debug_pub.add
5935 (J || ' UPDATE_GLOBAL ret sts: ' || x_RETURN_STATUS);
5936 END IF;
5937
5938 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5939 RETURN;
5940 END IF;
5941
5942
5943 IF J IS NOT NULL THEN
5944
5945 --update Global Picture directly
5946 OE_ORDER_UTIL.g_old_line_tbl(J) := l_old_line_tbl(I);
5947 OE_ORDER_UTIL.g_line_tbl(J).line_id := l_line_tbl(I).line_id;
5948 OE_ORDER_UTIL.g_line_tbl(J).header_id:= l_line_tbl(I).header_id;
5949
5950 OE_ORDER_UTIL.g_line_tbl(J).last_update_date := SYSDATE;
5951 OE_ORDER_UTIL.g_line_tbl(J).last_updated_by := FND_GLOBAL.USER_ID;
5952 OE_ORDER_UTIL.g_line_tbl(J).last_update_login:= FND_GLOBAL.LOGIN_ID;
5953
5954 OE_ORDER_UTIL.g_line_tbl(J)
5955 := OE_ORDER_UTIL.g_old_line_tbl(J);
5956 OE_ORDER_UTIL.g_line_tbl(J).flow_status_code
5957 := l_line_tbl(I).flow_status_code;
5958 OE_ORDER_UTIL.g_line_tbl(J).shipped_quantity
5959 := l_line_tbl(I).shipped_quantity;
5960 OE_ORDER_UTIL.g_line_tbl(J).shipped_quantity2 -- INVCONV
5961 := l_line_tbl(I).shipped_quantity2;
5962
5963
5964 IF l_debug_level > 0 THEN
5965 oe_debug_pub.add
5966 ('AFTER UPDATE GLOBAL FLOW_STATUS_CODE IS: '
5967 || OE_ORDER_UTIL.G_LINE_TBL( J ).FLOW_STATUS_CODE ,1);
5968 END IF;
5969
5970 END IF; -- if index is not null
5971
5972 I := l_line_tbl.NEXT(I);
5973 END LOOP;
5974
5975 ELSE --pre-pack H
5976
5977 IF OE_GLOBALS.G_ASO_INSTALLED = 'Y' THEN
5978
5979 OE_Order_PVT.Process_Requests_And_Notify
5980 ( p_process_requests => FALSE
5981 , p_notify => TRUE
5982 , p_process_ack => FALSE
5983 , x_return_status => x_return_status
5984 , p_line_tbl => l_line_tbl
5985 , p_old_line_tbl => l_old_line_tbl);
5986
5987 IF l_debug_level > 0 THEN
5988 oe_debug_pub.add('RETRURNED FROM PRN: '||x_RETURN_STATUS , 3 ) ;
5989 END IF;
5990 IF g_debug_call > 0 THEN
5991 G_DEBUG_MSG := G_DEBUG_MSG || '14-7';
5992 END IF;
5993
5994 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5995 RETURN;
5996 END IF;
5997
5998 END IF; -- aso_installed
5999 END IF; -- code_release_level
6000
6001 Print_Time('leaving Call_Notification_Framework');
6002
6003 EXCEPTION
6004 WHEN others THEN
6005 IF l_debug_level > 0 THEN
6006 oe_debug_pub.add('error in Call_Notification_Framework' || sqlerrm, 1);
6007 END IF;
6008 RAISE;
6009 END Call_Notification_Framework;
6010
6011
6012 /*-----------------------------------------------------------------
6013 PROCEDURE OM_To_WSH_Interface
6014
6015 OM-WSH_HVOP
6016 ------------------------------------------------------------------*/
6017 PROCEDURE OM_To_WSH_Interface
6018 ( p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
6019 ,p_header_rec IN OE_BULK_ORDER_PVT.HEADER_REC_TYPE
6020 ,x_return_status OUT NOCOPY VARCHAR2)
6021 IS
6022 l_action_rec WSH_BULK_TYPES_GRP.action_parameters_rectype;
6023 l_out_rec WSH_BULK_TYPES_GRP.Bulk_process_out_rec_type;
6024 l_msg_count NUMBER;
6025 l_return_status VARCHAR2(1);
6026 l_msg_data VARCHAR2(2000);
6027 l_index NUMBER;
6028 l_error_msg VARCHAR2(240);
6029 l_msg_index NUMBER;
6030 l_firm_flag VARCHAR2(1) := Null;
6031 --
6032 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6033 --
6034 BEGIN
6035
6036 l_action_rec.caller := 'OM';
6037 l_action_rec.action_code := 'CREATE';
6038 -- commented for MOAC FND_PROFILE.Get('ORG_ID', l_action_rec.org_id);
6039 l_action_rec.org_id := MO_GLOBAL.get_current_org_id();
6040
6041 WSH_bulk_process_grp.Create_update_delivery_details
6042 ( p_api_version_number => 1.0
6043 ,p_init_msg_list => FND_API.G_TRUE
6044 ,p_commit => FND_API.G_FALSE
6045 ,p_action_prms => l_action_rec
6046 ,p_line_rec => p_line_rec
6047 ,x_out_rec => l_out_rec
6048 ,x_return_status => l_return_status
6049 ,x_msg_count => l_msg_count
6050 ,x_msg_data => l_msg_data);
6051
6052 oe_debug_pub.add
6053 ('return status from WSH '|| l_return_status || '-' || l_msg_count, 5);
6054
6055 IF l_return_status = FND_API.G_RET_STS_SUCCESS OR
6056 l_return_status = 'W' THEN
6057
6058 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' AND
6059 OE_Sys_Parameters.Value('FIRM_DEMAND_EVENTS') = 'SHIPPING_INTERFACED'
6060 THEN
6061 l_firm_flag := 'Y';
6062 END IF;
6063
6064 FORALL I in p_line_rec.line_id.FIRST..p_line_rec.line_id.LAST
6065 UPDATE oe_order_lines
6066 SET shipping_interfaced_flag = 'Y',
6067 flow_status_code = 'AWAITING_SHIPPING',
6068 firm_demand_flag = NVL(l_firm_flag,firm_demand_flag)
6069 WHERE line_id = p_line_rec.line_id(I)
6070 AND p_line_rec.shipping_interfaced_flag(I) = 'Y';
6071
6072 oe_debug_pub.add('afer updating shipping interfaced flag', 5);
6073 G_BULK_WSH_INTERFACE_CALLED := TRUE; -- bug 4070931 starts
6074
6075 FOR I in p_line_rec.line_id.FIRST..p_line_rec.line_id.LAST
6076 LOOP
6077 IF p_line_rec.shipping_interfaced_flag(I) is NULL AND
6078 p_line_rec.shippable_flag(I) = 'Y' THEN
6079
6080 OE_BULK_WF_UTIL.G_LINE_INDEX := I;
6081
6082 BEGIN
6083 WF_ENGINE.HandleError
6084 (itemtype => 'OEOL',
6085 itemkey => to_char(p_line_rec.line_id(I)),
6086 activity => 'SHIP_LINE',
6087 command => 'RETRY',
6088 result => NULL);
6089
6090 EXCEPTION
6091 WHEN OTHERS THEN
6092 oe_debug_pub.add('Exception caught after Wf_engine.Handle_Error',5);
6093 END; -- Erroring out the WF table
6094
6095 OE_BULK_WF_UTIL.G_LINE_INDEX := NULL;
6096 END IF; -- lines not shipping_interfaced
6097 p_line_rec.shipping_eligible_flag(I) := null;
6098 END LOOP;
6099
6100 G_BULK_WSH_INTERFACE_CALLED := FALSE; -- bug 4070931ends
6101 END IF;
6102
6103
6104 IF l_msg_count > 0 THEN
6105
6106 l_index := P_LINE_REC.error_message_count.FIRST;
6107
6108 WHILE l_index is not NULL
6109 LOOP
6110 l_msg_count := 1;
6111
6112 WHILE P_LINE_REC.error_message_count(l_index) > 0
6113 LOOP
6114 IF l_msg_count = 1 THEN
6115
6116 l_msg_count := null;
6117
6118 -- Set the message context for errors.
6119 oe_bulk_msg_pub.set_msg_context
6120 ( p_entity_code => 'LINE'
6121 ,p_entity_id => P_LINE_REC.line_id(l_index)
6122 ,p_header_id => P_LINE_REC.header_id(l_index)
6123 ,p_line_id => P_LINE_REC.line_id(l_index)
6124 ,p_orig_sys_document_ref
6125 => P_LINE_REC.orig_sys_document_ref(l_index)
6126 ,p_orig_sys_document_line_ref
6127 => P_LINE_REC.orig_sys_line_ref(l_index)
6128 ,p_source_document_id => NULL
6129 ,p_source_document_line_id => NULL
6130 ,p_order_source_id
6131 => P_LINE_REC.order_source_id(l_index)
6132 ,p_source_document_type_id => NULL);
6133
6134 END IF;
6135
6136 Fnd_Msg_Pub.GET
6137 (p_encoded => FND_API.G_FALSE,
6138 p_data => l_error_msg,
6139 p_msg_index_out => l_msg_index);
6140
6141 IF l_debug_level > 0 THEN
6142 oe_debug_pub.add( 'ERROR : '||L_ERROR_MSG , 2 ) ;
6143 END IF;
6144
6145 fnd_message.set_name('ONT','OE_SHP_ERROR');
6146 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE',l_error_msg);
6147 --oe_msg_pub.add;
6148
6149 OE_BULK_MSG_PUB.ADD;
6150 oe_debug_pub.add('here again 2 ', 5);
6151
6152 P_LINE_REC.error_message_count(l_index)
6153 := p_line_rec.error_message_count(l_index) - 1;
6154 END LOOP; -- message count> 0
6155
6156 l_index := p_line_rec.error_message_count.NEXT(l_index);
6157 END LOOP; -- loop over all lines
6158
6159 END IF; -- if there were errors
6160
6161 IF l_return_status = 'W' THEN
6162 x_return_status := FND_API.G_RET_STS_SUCCESS;
6163 ELSE
6164 x_return_status := l_return_status;
6165 END IF;
6166
6167 IF l_debug_level > 0 THEN
6168 oe_debug_pub.add('return status from vshpb'|| x_return_status, 5);
6169 END IF;
6170
6171 EXCEPTION
6172 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6173 IF l_debug_level > 0 THEN
6174 oe_debug_pub.add('OM_TO_WSH_INTERFACE : UNEXPECTED ERROR',1);
6175 END IF;
6176
6177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
6178
6179 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
6180 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
6181 'OM_To_WSH_Interface');
6182 END IF;
6183 IF g_debug_call > 0 THEN
6184 G_DEBUG_MSG := G_DEBUG_MSG || 'E-39';
6185 END IF;
6186
6187 WHEN FND_API.G_EXC_ERROR THEN
6188 IF l_debug_level > 0 THEN
6189 oe_debug_pub.add('OM_TO_WSH_INTERFACE : EXE ERROR',1);
6190 END IF;
6191 x_return_status := FND_API.G_RET_STS_ERROR;
6192
6193 WHEN OTHERS THEN
6194 IF l_debug_level > 0 THEN
6195 oe_debug_pub.add('OM_TO_WSH_INTERFACE, OTHERS '|| sqlerrm ,1);
6196 END IF;
6197
6198 IF l_debug_level > 0 THEN
6199 oe_debug_pub.add('ERROR MESSAGE : '||SUBSTR(SQLERRM ,1,100),1);
6200 END IF;
6201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
6202
6203 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
6204 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
6205 'OM_To_WSH_Interface');
6206 END IF;
6207
6208 END OM_To_WSH_Interface;
6209
6210 /*------------------------------------------------------------------------
6211 PROCEDURE Print_Time
6212
6213 -------------------------------------------------------------------------*/
6214
6215 PROCEDURE Print_Time(p_msg IN VARCHAR2)
6216 IS
6217 l_time VARCHAR2(100);
6218 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6219 BEGIN
6220 l_time := to_char (new_time (sysdate, 'PST', 'EST'),
6221 'DD-MON-YY HH24:MI:SS');
6222 IF l_debug_level > 0 THEN
6223 oe_debug_pub.add( P_MSG || ': '|| L_TIME , 1 ) ;
6224 END IF;
6225 END Print_Time;
6226 /*--------------------------------------------------------------------------------
6227 PROCEDURE Ship_Complete
6228 Added for bug 6021460. This API will be used for Ship Complete Validation template.
6229 ----------------------------------------------------------------------------------*/
6230 PROCEDURE ship_complete
6231 (
6232 p_application_id IN NUMBER
6233 , p_entity_short_name IN VARCHAR2
6234 , p_validation_entity_short_name IN VARCHAR2
6235 , p_validation_tmplt_short_name IN VARCHAR2
6236 , p_record_set_short_name IN VARCHAR2
6237 , p_scope IN VARCHAR2
6238 , x_result_out OUT NOCOPY NUMBER
6239
6240 )
6241 IS
6242 l_ship_confirm_status VARCHAR2(30);
6243 l_count_reserve NUMBER;
6244 --
6245 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6246 --
6247 BEGIN
6248
6249 select activity_result_code
6250 into l_ship_confirm_status
6251 from wf_process_activities p, wf_item_activity_statuses s
6252 where p.instance_id = s.process_activity
6253 and item_type = 'OEOL'
6254 and item_key = to_char(OE_LINE_SECURITY.g_record.line_id)
6255 and p.activity_name = 'SHIP_LINE'
6256 and s.activity_status = 'COMPLETE';
6257
6258 IF l_ship_confirm_status = 'UNRESERVE' THEN
6259 x_result_out := 0;
6260 ELSE
6261 x_result_out := 1;
6262 END IF;
6263
6264
6265 EXCEPTION
6266 WHEN NO_DATA_FOUND THEN
6267 x_result_out := 0;
6268 WHEN TOO_MANY_ROWS THEN
6269
6270 select count(1)
6271 into l_count_reserve
6272 from wf_process_activities p, wf_item_activity_statuses s
6273 where p.instance_id = s.process_activity
6274 and item_type = 'OEOL'
6275 and item_key = to_char(OE_LINE_SECURITY.g_record.line_id)
6276 and p.activity_name = 'SHIP_LINE'
6277 and s.activity_status = 'COMPLETE'
6278 and (activity_result_code IS NULL OR activity_result_code <> 'UNRESERVE');
6279
6280 if l_count_reserve = 0 then
6281
6282 x_result_out := 0;
6283
6284 else
6285
6286 x_result_out := 1;
6287
6288 end if;
6289
6290 WHEN OTHERS THEN
6291
6292 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6293 THEN
6294 OE_MSG_PUB.Add_Exc_Msg
6295 ( G_PKG_NAME
6296 , 'ship_complete'
6297 );
6298 END IF;
6299
6300 IF l_debug_level > 0 THEN
6301 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 100 ) , 1 ) ;
6302 END IF;
6303 IF g_debug_call > 0 THEN
6304 G_DEBUG_MSG := G_DEBUG_MSG || 'E1';
6305 END IF;
6306 RAISE;
6307 END ship_complete;
6308
6309
6310 END OE_Shipping_Integration_PVT;