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