[Home] [Help]
PACKAGE BODY: APPS.OE_SHIPPING_INTEGRATION_PUB
Source
1 PACKAGE BODY OE_Shipping_Integration_PUB AS
2 /* $Header: OEXPSHPB.pls 120.0 2005/06/01 01:23:13 appldev noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Shipping_Integration_PUB';
7
8 -- Start of Comments
9 -- API name OE_Shipping_Integration_PUB
10 -- Type Public
11 -- Version Current version = 1.0
12 -- Initial version = 1.0
13
14 /*
15 This function returns FND_API.G_TRUE, if the line's next eligible WF
16 activity is "SHIP_LINE", otherwise returns FND_API.G_FALSE
17 */
18
19 FUNCTION Is_Activity_Shipping
20 (
21 p_api_version_number IN NUMBER
22 , p_line_id IN NUMBER
23 ) return VARCHAR2
24 IS
25 l_count NUMBER := 0;
26 l_api_version_number CONSTANT NUMBER := 1.0;
27 l_api_name CONSTANT VARCHAR2(30) := 'Is_Activity_Shipping';
28 --
29 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
30 --
31 BEGIN
32
33 -- Standard call to check for call compatibility
34
35 IF NOT FND_API.Compatible_API_Call
36 ( l_api_version_number
37 , p_api_version_number
38 , l_api_name
39 , G_PKG_NAME
40 )
41 THEN
42 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
43 END IF;
44
45
46 select count(*)
47 into l_count
48 from wf_item_activity_statuses wias,
49 wf_process_activities wpa
50 where wias.item_type = 'OEOL'
51 and wias.item_key = to_char(p_line_id)
52 and wias.ACTIVITY_STATUS = 'NOTIFIED'
53 and wias.process_activity = wpa.instance_id
54 and wpa.activity_name = 'SHIP_LINE';
55
56 IF l_count > 0 THEN
57 return FND_API.G_TRUE;
58 ELSE
59 return FND_API.G_FALSE;
60 END IF;
61
62 EXCEPTION
63
64 WHEN FND_API.G_EXC_ERROR THEN
65
66 return FND_API.G_RET_STS_ERROR;
67
68 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
69
70 return FND_API.G_RET_STS_UNEXP_ERROR ;
71
72 WHEN OTHERS THEN
73
74 return FND_API.G_RET_STS_UNEXP_ERROR ;
75
76 END Is_Activity_Shipping;
77
78 /*
79 This procedure releases the BLOCK on "SHIP_LINE" activity. This Procedure
80 will be called whenever "SHIPPED_QUANTITY" column in OE_ORDER_LINES is
81 updated or a non-shippable line reaches the SHIP_LINE activity.
82 */
83
84 PROCEDURE Complete_Ship_Line_Activity
85 ( p_api_version_number IN NUMBER
86 , p_line_id IN NUMBER
87 , p_result_code IN VARCHAR2
88 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
89 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
90 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
91 ) IS
92 l_api_version_number CONSTANT NUMBER := 1.0;
93 l_api_name CONSTANT VARCHAR2(30) := 'Complete_Ship_Line_Activity';
94 l_errname VARCHAR2(30);
95 l_errmsg VARCHAR2(2000);
96 l_errstack VARCHAR2(2000);
97 --
98 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
99 --
100 BEGIN
101
102 -- Standard call to check for call compatibility
103 IF l_debug_level > 0 THEN
104 oe_debug_pub.add( 'ENTERING OE_SHIPPING_INTEGRATION_PUB.COMPLETE_SHIP_LINE_ACTIVITY '|| TO_CHAR ( P_LINE_ID ) ||' '||P_RESULT_CODE , 1 ) ;
105 END IF;
106
107 IF NOT FND_API.Compatible_API_Call
108 ( l_api_version_number
109 , p_api_version_number
110 , l_api_name
111 , G_PKG_NAME
112 )
113 THEN
114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
115 END IF;
116
117 IF OE_Validate.Line(p_line_id)
118 THEN
119 IF l_debug_level > 0 THEN
120 oe_debug_pub.add( 'CALLING WF_ENGINE.COMPLETEACTIVITYINTERNALNAME '|| TO_CHAR ( P_LINE_ID ) , 2 ) ;
121 END IF;
122 wf_engine.CompleteActivityInternalName('OEOL', to_char(p_line_id), 'SHIP_LINE', p_result_code);
123 IF l_debug_level > 0 THEN
124 oe_debug_pub.add( 'RETURNED FROM WF_ENGINE.COMPLETEACTIVITYINTERNALNAME '|| TO_CHAR ( P_LINE_ID ) , 2 ) ;
125 END IF;
126 END IF;
127
128 x_return_status := FND_API.G_RET_STS_SUCCESS;
129
130 IF l_debug_level > 0 THEN
131 oe_debug_pub.add( 'EXITING OE_SHIPPING_INTEGRATION_PUB.COMPLETE_SHIP_LINE_ACTIVITY '|| TO_CHAR ( P_LINE_ID ) , 1 ) ;
132 END IF;
133 EXCEPTION
134
135 WHEN FND_API.G_EXC_ERROR THEN
136
137 x_return_status := FND_API.G_RET_STS_ERROR;
138
139 -- Get message count and data
140
141 FND_MSG_PUB.Count_And_Get
142 ( p_count => x_msg_count
143 , p_data => x_msg_data
144 );
145
146 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
147
148 IF l_debug_level > 0 THEN
149 oe_debug_pub.add( 'UNEXPECTED ERROR : '||SQLERRM , 1 ) ;
150 END IF;
151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
152
153 -- Get message count and data
154
155 FND_MSG_PUB.Count_And_Get
156 ( p_count => x_msg_count
157 , p_data => x_msg_data
158 );
159
160 WHEN OTHERS THEN
161
162 IF l_debug_level > 0 THEN
163 oe_debug_pub.add( 'WORK FLOW ERROR HAS OCCURED '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
164 END IF;
165
166 WF_CORE.Get_Error(l_errname, l_errmsg, l_errstack);
167 IF l_errname IS NOT NULL THEN
168 IF l_debug_level > 0 THEN
169 oe_debug_pub.add( 'ERROR MESSAGE '||L_ERRMSG , 1 ) ;
170 END IF;
171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
172 ELSE
173 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
174 THEN
175 FND_MSG_PUB.Add_Exc_Msg
176 ( G_PKG_NAME
177 , 'Complete_Ship_Line_Activity'
178 );
179 END IF;
180
181 -- Get message count and data
182
183 FND_MSG_PUB.Count_And_Get
184 ( p_count => x_msg_count
185 , p_data => x_msg_data
186 );
187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
188 END IF;
189
190 END Complete_Ship_Line_Activity;
191
192
193 /* Returns FND_API_G_TRUE if credit check passes,
194 FND_API.G_FALSE if credit check fails */
195 /** This function is not used ******/
196 FUNCTION Credit_Check
197 (
198 p_api_version_number IN NUMBER
199 , p_header_id IN NUMBER
200 , p_line_id IN NUMBER DEFAULT NULL
201 ) return VARCHAR2
202 IS
203 l_x_result_out VARCHAR2(80);
204 l_x_return_status VARCHAR2(1);
205 l_x_msg_count NUMBER;
206 l_x_msg_data VARCHAR2(2000);
207 l_api_version_number CONSTANT NUMBER := 1.0;
208 l_api_name CONSTANT VARCHAR2(30) := 'Credit_Check';
209 --
210 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
211 --
212 BEGIN
213
214 -- Standard call to check for call compatibility
215
216 IF NOT FND_API.Compatible_API_Call
217 ( l_api_version_number
218 , p_api_version_number
219 , l_api_name
220 , G_PKG_NAME
221 )
222 THEN
223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
224 END IF;
225 /*
226 OE_Credit_PUB.Check_Available_Credit
227 (
228 p_header_id => p_header_id,
229 p_msg_count => l_x_msg_count,
230 p_msg_data => l_x_msg_data,
231 p_result_out => l_x_result_out,
232 p_return_status => l_x_return_status
233 );
234 IF l_x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
236 ELSIF l_x_return_status = FND_API.G_RET_STS_ERROR THEN
237 RAISE FND_API.G_EXC_ERROR;
238 END IF;
239 */
240 IF UPPER(l_x_result_out) = 'PASS' THEN
241 return FND_API.G_TRUE;
242 ELSE
243 return FND_API.G_FALSE;
244 END IF;
245
246 EXCEPTION
247
248 WHEN FND_API.G_EXC_ERROR THEN
249
250 return FND_API.G_RET_STS_ERROR;
251
252 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
253
254 return FND_API.G_RET_STS_UNEXP_ERROR ;
255
256 WHEN OTHERS THEN
257
258 return FND_API.G_RET_STS_UNEXP_ERROR ;
259
260 END Credit_Check;
261
262 /*
263 If this funtion returns FND_API.G_TRUE, a hold exists on this line.
264 If this funtion returns FND_API.G_FALSE, a hold does not exists
265 on this line.
266 */
267
268 FUNCTION Check_Holds_For_SC
269 (
270 p_api_version_number IN NUMBER
271 , p_header_id IN NUMBER DEFAULT NULL
272 , p_line_id IN NUMBER
273 ) return VARCHAR2
274 IS
275 l_x_result_out VARCHAR2(1);
276 l_x_return_status VARCHAR2(1);
277 l_x_msg_count NUMBER;
278 l_x_msg_data VARCHAR2(2000);
279 l_credit_check_pass VARCHAR2(1);
280 l_api_version_number CONSTANT NUMBER := 1.0;
281 l_api_name CONSTANT VARCHAR2(30) := 'Check_Holds_For_SC';
282 --
283 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
284 --
285 BEGIN
286
287 -- Standard call to check for call compatibility
288
289 IF NOT FND_API.Compatible_API_Call
290 ( l_api_version_number
291 , p_api_version_number
292 , l_api_name
293 , G_PKG_NAME
294 )
295 THEN
296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
297 END IF;
298
299 OE_Holds_PUB.Check_Holds
300 (
301 p_api_version => 1.0,
302 p_line_id => p_line_id,
303 x_result_out => l_x_result_out,
304 x_return_status => l_x_return_status,
305 x_msg_count => l_x_msg_count,
306 x_msg_data => l_x_msg_data
307 );
308 IF l_x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
309 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
310 ELSIF l_x_return_status = FND_API.G_RET_STS_ERROR THEN
311 RAISE FND_API.G_EXC_ERROR;
312 END IF;
313 return l_x_result_out;
314
315 EXCEPTION
316
317 WHEN FND_API.G_EXC_ERROR THEN
318
319 return FND_API.G_RET_STS_ERROR;
320
321 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
322
323 return FND_API.G_RET_STS_UNEXP_ERROR ;
324
325 WHEN OTHERS THEN
326
327 return FND_API.G_RET_STS_UNEXP_ERROR ;
328
329 END Check_Holds_For_SC;
330
331
332 /*
333 This procedure will return the ship_tolerance_above and ship_tolerance_below
334 for a passed line id
335 */
336
337 PROCEDURE Get_Tolerance
338 (
339 p_api_version_number IN NUMBER
340 , p_cal_tolerance_tbl IN Cal_Tolerance_Tbl_Type
341 , x_update_tolerance_flag OUT NOCOPY /* file.sql.39 change */ VARCHAR2
342 , x_ship_tolerance OUT NOCOPY /* file.sql.39 change */ NUMBER
343 , x_ship_beyond_tolerance OUT NOCOPY /* file.sql.39 change */ VARCHAR2
344 , x_shipped_within_tolerance OUT NOCOPY /* file.sql.39 change */ VARCHAR2
345 , x_config_broken OUT NOCOPY /* file.sql.39 change */ VARCHAR2
346 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
347 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
348 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
349 )
350 IS
351 l_api_version_number CONSTANT NUMBER := 1.0;
352 l_api_name CONSTANT VARCHAR2(30) := 'Get_Tolerance';
353 l_line_rec OE_Order_Pub.Line_Rec_Type;
354 l_line_tbl OE_Order_Pub.Line_Tbl_Type;
355 l_temp_line_tbl OE_Order_Pub.Line_Tbl_Type;
356 l_tbl_index NUMBER;
357 l_line_index NUMBER;
358 l_cal_tolerance_tbl Cal_Tolerance_Tbl_Type;
359 l_model_kit_line VARCHAR2(1) := FND_API.G_FALSE;
360 l_x_result_out VARCHAR2(30);
361 l_shipped_quantity NUMBER := 0;
362 l_ordered_quantity NUMBER := 0;
363 l_x_shipped_quantity NUMBER := 0;
364 l_x_shipping_quantity NUMBER := 0;
365 l_x_ordered_quantity NUMBER := 0;
366 l_msg_count NUMBER;
367 l_msg_data VARCHAR2(2000);
368 l_ship_tolerance NUMBER := 0;
369 l_x_return_status VARCHAR2(1);
370 l_top_model_line_id NUMBER;
371 -- l_x_line_tbl OE_Order_Pub.Line_Tbl_Type;
372 l_ratio_status VARCHAR2(1);
373 l_planned_quantity_passed VARCHAR2(1) := FND_API.G_FALSE;
374 l_top_model_index NUMBER := 0;
375
376 l_count_unshipped NUMBER := 0;
377 l_del_shipping_quantity NUMBER := 0;
378
379 --
380 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
381 --
382 BEGIN
383
384 IF l_debug_level > 0 THEN
385 oe_debug_pub.add( 'ENTERING OE_SHIPPING_INTEGRATION_PUB.GET_TOLERANCE ' , 1 ) ;
386 END IF;
387 -- Standard call to check for call compatibility
388 x_update_tolerance_flag := FND_API.G_FALSE;
389 x_ship_beyond_tolerance := FND_API.G_FALSE;
390 x_shipped_within_tolerance := FND_API.G_FALSE;
391 x_config_broken := FND_API.G_FALSE;
392
393 IF NOT FND_API.Compatible_API_Call
394 ( l_api_version_number
395 , p_api_version_number
396 , l_api_name
397 , G_PKG_NAME
398 )
399 THEN
400 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
401 END IF;
402
403 -- Convert the passed table
404
405 FOR l_tbl_index IN p_cal_tolerance_tbl.FIRST .. p_cal_tolerance_tbl.LAST
406 LOOP
407 IF l_debug_level > 0 THEN
408 oe_debug_pub.add( 'ENTERING THE CONVERT LOOP : ' || TO_CHAR ( L_TBL_INDEX ) , 3 ) ;
409 END IF;
410 l_cal_tolerance_tbl(p_cal_tolerance_tbl(l_tbl_index).line_id).line_id := p_cal_tolerance_tbl(l_tbl_index).line_id;
411 l_cal_tolerance_tbl(p_cal_tolerance_tbl(l_tbl_index).line_id).quantity_to_be_shipped := p_cal_tolerance_tbl(l_tbl_index).quantity_to_be_shipped;
412
413 /* to fix the bug 2127323 */
414 IF p_cal_tolerance_tbl(l_tbl_index).planned_quantity = FND_API.G_MISS_NUM THEN
415 IF l_debug_level > 0 THEN
416 oe_debug_pub.add( 'PLANNED QUANTITY IS MISSING SETTING IT TO ZERO ' , 3 ) ;
417 END IF;
418
419 l_cal_tolerance_tbl(p_cal_tolerance_tbl(l_tbl_index).line_id).planned_quantity := 0;
420
421 END IF;
422
423 l_cal_tolerance_tbl(p_cal_tolerance_tbl(l_tbl_index).line_id).shipping_uom := p_cal_tolerance_tbl(l_tbl_index).shipping_uom;
424 IF nvl(p_cal_tolerance_tbl(l_tbl_index).planned_quantity,0) <> 0 AND
425 p_cal_tolerance_tbl(l_tbl_index).planned_quantity <> FND_API.G_MISS_NUM THEN
426 l_planned_quantity_passed := FND_API.G_TRUE;
427 END IF;
428 IF l_debug_level > 0 THEN
429 oe_debug_pub.add( 'SHIPPING UOM : '||P_CAL_TOLERANCE_TBL ( L_TBL_INDEX ) .SHIPPING_UOM , 3 ) ;
430 END IF;
431 IF l_debug_level > 0 THEN
432 oe_debug_pub.add( 'QUANTITY TO BE SHIPPED : '||TO_CHAR ( P_CAL_TOLERANCE_TBL ( L_TBL_INDEX ) .QUANTITY_TO_BE_SHIPPED ) , 3 ) ;
433 END IF;
434 IF l_debug_level > 0 THEN
435 oe_debug_pub.add( 'PLANNED QUANTITY : '||TO_CHAR ( P_CAL_TOLERANCE_TBL ( L_TBL_INDEX ) .PLANNED_QUANTITY ) , 3 ) ;
436 END IF;
437
438 END LOOP;
439
440 FOR l_tbl_index IN p_cal_tolerance_tbl.FIRST .. p_cal_tolerance_tbl.LAST
441 LOOP
442
443 IF l_debug_level > 0 THEN
444 oe_debug_pub.add( 'ENTERING THE LOOP : ' || TO_CHAR ( L_TBL_INDEX ) , 2 ) ;
445 END IF;
446 IF l_debug_level > 0 THEN
447 oe_debug_pub.add( 'LINE ID : ' || TO_CHAR ( P_CAL_TOLERANCE_TBL ( L_TBL_INDEX ) .LINE_ID ) , 2 ) ;
448 END IF;
449 -- l_line_rec := OE_Line_Util.Query_Row(p_cal_tolerance_tbl(l_tbl_index).line_id);
450 OE_Line_Util.Query_Row(p_line_id => p_cal_tolerance_tbl(l_tbl_index).line_id,
451 x_line_rec => l_line_rec);
452 IF l_line_rec.top_model_line_id IS NOT NULL AND
453 l_line_rec.top_model_line_id <> FND_API.G_MISS_NUM THEN
454
455 l_model_kit_line := FND_API.G_TRUE;
456 IF l_debug_level > 0 THEN
457 oe_debug_pub.add( 'IT IS A MODEL/KIT LINE ' , 3 ) ;
458 END IF;
459 l_top_model_line_id := l_line_rec.top_model_line_id;
460
461 END IF;
462 NULL;
463 END LOOP;
464
465 IF l_model_kit_line = FND_API.G_TRUE THEN
466
467 IF l_debug_level > 0 THEN
468 oe_debug_pub.add( 'IT IS A MODEL/KIT LINE ' , 3 ) ;
469 END IF;
470 -- l_line_tbl := OE_Config_Util.Query_Options(l_line_rec.top_model_line_id);
471 OE_Config_Util.Query_Options(p_top_model_line_id => l_line_rec.top_model_line_id,
472 x_line_tbl => l_line_tbl);
473 -- Get the top model line id index.
474
475 FOR l_line_index IN 1 .. l_line_tbl.count
476 LOOP
477
478 IF l_debug_level > 0 THEN
479 oe_debug_pub.add( 'INDEX/LINE_ID/TOP_MODEL_LINE_ID :'||TO_CHAR ( L_LINE_INDEX ) ||TO_CHAR ( L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID ) ||TO_CHAR ( L_LINE_TBL ( L_LINE_INDEX ) .TOP_MODEL_LINE_ID ) , 3 ) ;
480 END IF;
481 IF l_line_tbl(l_line_index).line_id = l_line_tbl(l_line_index).top_model_line_id THEN
482 l_top_model_index := l_line_index;
483 GOTO END_GET_TOP_MODEL;
484 END IF;
485
486 END LOOP;
487 << END_GET_TOP_MODEL >>
488
489 IF l_debug_level > 0 THEN
490 oe_debug_pub.add( 'TOP MODEL INDEX : '||L_TOP_MODEL_INDEX , 3 ) ;
491 END IF;
492
493 l_temp_line_tbl := l_line_tbl;
494
495 -- Assign the quantity to be shipped for shippable lines.
496 FOR l_line_index IN l_line_tbl.FIRST .. l_line_tbl.LAST
497 LOOP
498
499 IF l_debug_level > 0 THEN
500 oe_debug_pub.add( 'INSIDE THE MODEL LOOP '||TO_CHAR ( L_LINE_INDEX ) , 3 ) ;
501 END IF;
502 IF l_debug_level > 0 THEN
503 oe_debug_pub.add( 'ITEM TYPE '||L_LINE_TBL ( L_LINE_INDEX ) .ITEM_TYPE_CODE , 3 ) ;
504 END IF;
505
506 IF l_cal_tolerance_tbl.EXISTS(l_line_tbl(l_line_index).line_id) THEN
507
508 l_temp_line_tbl(l_line_index).shipping_quantity := nvl(l_line_tbl(l_line_index).shipping_quantity,0) + l_cal_tolerance_tbl(l_line_tbl(l_line_index).line_id).quantity_to_be_shipped;
509 IF l_debug_level > 0 THEN
510 oe_debug_pub.add( 'SHIPPING QUANTITY '|| TO_CHAR ( L_TEMP_LINE_TBL ( L_LINE_INDEX ) .SHIPPING_QUANTITY ) , 3 ) ;
511 END IF;
512 IF l_debug_level > 0 THEN
513 oe_debug_pub.add( 'ORDERED UOM '||L_LINE_TBL ( L_LINE_INDEX ) .ORDER_QUANTITY_UOM , 3 ) ;
514 END IF;
515 IF l_debug_level > 0 THEN
516 oe_debug_pub.add( 'SHIPPING UOM '||L_CAL_TOLERANCE_TBL ( L_LINE_TBL ( L_LINE_INDEX ) .LINE_ID ) .SHIPPING_UOM , 3 ) ;
517 END IF;
518 IF l_line_tbl(l_line_index).order_quantity_uom <> l_cal_tolerance_tbl(l_line_tbl(l_line_index).line_id).shipping_uom THEN
519 IF l_debug_level > 0 THEN
520 oe_debug_pub.add( 'UOMS ARE DIFFRENT ' , 3 ) ;
521 END IF;
522 l_temp_line_tbl(l_line_index).shipped_quantity := OE_Order_Misc_Util.Convert_Uom
523 (
524 l_line_rec.inventory_item_id,
525 l_cal_tolerance_tbl(l_line_tbl(l_line_index).line_id).shipping_uom,
526 l_line_tbl(l_line_index).order_quantity_uom,
527 l_temp_line_tbl(l_line_index).shipped_quantity
528 );
529
530 ELSE
531 l_temp_line_tbl(l_line_index).shipped_quantity := l_temp_line_tbl(l_line_index).shipping_quantity;
532
533 END IF;
534 IF l_debug_level > 0 THEN
535 oe_debug_pub.add( 'SHIPPED QUANTITY '|| TO_CHAR ( L_TEMP_LINE_TBL ( L_LINE_INDEX ) .SHIPPED_QUANTITY ) , 3 ) ;
536 END IF;
537 END IF;
538
539 END LOOP;
540
541 -- Calculate the shipped quantity of top model line.
542 OE_Shipping_Integration_PVT.Get_PTO_Shipped_Quantity
543 (
544 p_x_line_tbl => l_temp_line_tbl
545 , x_ratio_status => l_ratio_status
546 , x_return_status => l_x_return_status
547 );
548
549 IF l_debug_level > 0 THEN
550 oe_debug_pub.add( 'RATIO STATUS/RETURN STATUS : '||L_RATIO_STATUS||L_X_RETURN_STATUS , 3 ) ;
551 END IF;
552
553 IF l_ratio_status = FND_API.G_TRUE THEN
554
555 l_shipped_quantity := l_temp_line_tbl(l_top_model_index).shipped_quantity;
556 l_line_rec := l_temp_line_tbl(l_top_model_index);
557
558 IF l_debug_level > 0 THEN
559 oe_debug_pub.add( 'SHIPPED QUANTITY '||TO_CHAR ( L_SHIPPED_QUANTITY ) , 3 ) ;
560 END IF;
561 OE_Shipping_Integration_PVT.Check_Shipment_Line
562 (
563 p_line_rec => l_line_rec,
564 p_shipped_quantity => l_shipped_quantity,
565 x_result_out => l_x_result_out
566 );
567
568 IF l_x_result_out = OE_GLOBALS.G_SHIPPED_WITHIN_TOL_BELOW AND
569 l_planned_quantity_passed = FND_API.G_TRUE THEN
570
571 IF l_debug_level > 0 THEN
572 oe_debug_pub.add( 'SHIPPED WITHIN TOLERANCE BELOW' , 2 ) ;
573 END IF;
574
575 -- Tolerance needs to be updated so that ship confirm can result
576 -- in split.
577
578 l_temp_line_tbl := l_line_tbl;
579
580 -- Assign the quantity to be shipped for shippable lines.
581 FOR l_line_index IN l_line_tbl.FIRST .. l_line_tbl.LAST
582 LOOP
583
584 IF l_debug_level > 0 THEN
585 oe_debug_pub.add( 'INSIDE THE MODEL LOOP '||TO_CHAR ( L_LINE_INDEX ) , 3 ) ;
586 END IF;
587 IF l_debug_level > 0 THEN
588 oe_debug_pub.add( 'ITEM TYPE '||L_LINE_TBL ( L_LINE_INDEX ) .ITEM_TYPE_CODE , 3 ) ;
589 END IF;
590
591 IF l_cal_tolerance_tbl.EXISTS(l_line_tbl(l_line_index).line_id) THEN
592
593 l_temp_line_tbl(l_line_index).shipping_quantity := nvl(l_line_tbl(l_line_index).shipping_quantity,0) +
594 l_cal_tolerance_tbl(l_line_tbl(l_line_index).line_id).quantity_to_be_shipped +
595 nvl(l_cal_tolerance_tbl(l_line_tbl(l_line_index).line_id).planned_quantity,0);
596
597 IF l_debug_level > 0 THEN
598 oe_debug_pub.add( 'SHIPPING QUANTITY '|| TO_CHAR ( L_TEMP_LINE_TBL ( L_LINE_INDEX ) .SHIPPING_QUANTITY ) , 3 ) ;
599 END IF;
600 IF l_line_tbl(l_line_index).order_quantity_uom <> l_cal_tolerance_tbl(l_line_tbl(l_line_index).line_id).shipping_uom THEN
601 l_temp_line_tbl(l_line_index).shipped_quantity := OE_Order_Misc_Util.Convert_Uom
602 (
603 l_line_rec.inventory_item_id,
604 l_cal_tolerance_tbl(l_line_tbl(l_line_index).line_id).shipping_uom,
605 l_line_tbl(l_line_index).order_quantity_uom,
606 l_temp_line_tbl(l_line_index).shipped_quantity
607 );
608
609 ELSE
610 l_temp_line_tbl(l_line_index).shipped_quantity := l_temp_line_tbl(l_line_index).shipping_quantity;
611 END IF;
612 IF l_debug_level > 0 THEN
613 oe_debug_pub.add( 'SHIPPED QUANTITY '|| TO_CHAR ( L_TEMP_LINE_TBL ( L_LINE_INDEX ) .SHIPPED_QUANTITY ) , 3 ) ;
614 END IF;
615 END IF;
616
617 END LOOP;
618 -- Calculate the shipped quantity of top model line.
619 OE_Shipping_Integration_PVT.Get_PTO_Shipped_Quantity
620 (
621 p_x_line_tbl => l_temp_line_tbl
622 , x_ratio_status => l_ratio_status
623 , x_return_status => l_x_return_status
624 );
625 IF l_ratio_status = FND_API.G_TRUE THEN
626
627 l_shipped_quantity := l_temp_line_tbl(l_top_model_index).shipped_quantity;
628 l_ordered_quantity := l_temp_line_tbl(l_top_model_index).ordered_quantity;
629 l_line_rec := l_temp_line_tbl(l_top_model_index);
630
631 IF l_debug_level > 0 THEN
632 oe_debug_pub.add( 'TOTAL QUANTITY TO BE SHIPPED : '||TO_CHAR ( L_SHIPPED_QUANTITY ) , 3 ) ;
633 END IF;
634 IF l_debug_level > 0 THEN
635 oe_debug_pub.add( 'TOTAL ORDERED QUANTITY : '||TO_CHAR ( L_ORDERED_QUANTITY ) , 3 ) ;
636 END IF;
637
638 -- Get the total ordered and shipped quantities if it is a part of
639 -- a line set.
640
641 IF l_line_rec.line_set_id IS NOT NULL AND
642 l_line_rec.line_set_id <> FND_API.G_MISS_NUM THEN
643
644 OE_Shipping_Integration_PUB.Get_Quantity
645 (
646 p_api_version_number => 1.0,
647 p_line_id => l_line_rec.line_id,
648 p_line_set_id => l_line_rec.line_set_id,
649 x_ordered_quantity => l_x_ordered_quantity,
650 x_shipped_quantity => l_x_shipped_quantity,
651 x_shipping_quantity => l_x_shipping_quantity,
652 x_return_status => l_x_return_status,
653 x_msg_count => l_msg_count,
654 x_msg_data => l_msg_data
655 );
656
657 IF l_debug_level > 0 THEN
658 oe_debug_pub.add( 'TOTAL ORDERED QUANTITY : '||TO_CHAR ( L_X_ORDERED_QUANTITY ) , 3 ) ;
659 END IF;
660 IF l_debug_level > 0 THEN
661 oe_debug_pub.add( 'TOTAL SHIPPED QUANTITY : '||TO_CHAR ( L_X_SHIPPED_QUANTITY ) , 3 ) ;
662 END IF;
663 l_shipped_quantity := l_shipped_quantity + l_x_shipped_quantity;
664 l_ordered_quantity := l_x_ordered_quantity;
665
666 END IF;
667
668 -- Calculate the new tolerance value
669 IF l_debug_level > 0 THEN
670 oe_debug_pub.add( 'TOTAL ORDERED QUANTITY : '||TO_CHAR ( L_ORDERED_QUANTITY ) , 3 ) ;
671 END IF;
672 IF l_debug_level > 0 THEN
673 oe_debug_pub.add( 'TOTAL SHIPPED QUANTITY : '||TO_CHAR ( L_SHIPPED_QUANTITY ) , 3 ) ;
674 END IF;
675
676 l_ship_tolerance := ((l_ordered_quantity - l_shipped_quantity)/l_ordered_quantity) * 100;
677 IF l_ship_tolerance < 0 THEN
678 l_ship_tolerance := 0;
679 END IF;
680 IF l_debug_level > 0 THEN
681 oe_debug_pub.add( 'NEW TOLERANCE VALUE : '|| TO_CHAR ( L_SHIP_TOLERANCE ) , 3 ) ;
682 END IF;
683 x_update_tolerance_flag := FND_API.G_TRUE;
684 x_ship_tolerance := l_ship_tolerance;
685 ELSE
686 x_config_broken := FND_API.G_TRUE;
687 END IF;
688
689 ELSIF l_x_result_out = OE_GLOBALS.G_SHIPPED_BEYOND_TOLERANCE THEN
690 IF l_debug_level > 0 THEN
691 oe_debug_pub.add( 'SHIPPED BEYOND TOLERANCE ' , 3 ) ;
692 END IF;
693 x_ship_beyond_tolerance := FND_API.G_TRUE;
694 ELSIF l_x_result_out = OE_GLOBALS.G_SHIPPED_WITHIN_TOL_BELOW OR
695 l_x_result_out = OE_GLOBALS.G_SHIPPED_WITHIN_TOL_ABOVE THEN
696 IF l_debug_level > 0 THEN
697 oe_debug_pub.add( 'SHIPPED WITHIN TOLERANCE ' , 3 ) ;
698 END IF;
699 x_shipped_within_tolerance := FND_API.G_TRUE;
700 END IF;
701
702 ELSE
703 x_config_broken := FND_API.G_TRUE;
704 END IF;
705
706 ELSE
707 IF l_debug_level > 0 THEN
708 oe_debug_pub.add( 'IT IS A STANDARD LINE ' , 3 ) ;
709 END IF;
710
711 IF l_line_rec.line_set_id IS NOT NULL THEN
712
713 IF l_debug_level > 0 THEN
714 oe_debug_pub.add( 'LINE SET ID NOT NULL '||L_LINE_REC.LINE_SET_ID , 3 ) ;
715 END IF;
716 SELECT count(*)
717 INTO l_count_unshipped
718 FROM oe_order_lines
719 WHERE line_set_id = l_line_rec.line_set_id
720 AND shipped_quantity IS NULL
721 AND line_id <> l_line_rec.line_id ;
722
723 IF l_debug_level > 0 THEN
724 oe_debug_pub.add( 'COUNT '||L_COUNT_UNSHIPPED , 3 ) ;
725 END IF;
726
727 IF nvl(l_count_unshipped,0) > 0 THEN
728
729 BEGIN
730
731 SELECT nvl(sum(shipped_quantity),0)
732 INTO l_del_shipping_quantity
733 FROM wsh_delivery_details
734 where source_line_id in (SELECT line_id
735 FROM oe_order_lines
736 WHERE line_set_id = l_line_rec.line_set_id
737 AND shipped_quantity IS NULL
738 AND line_id <> l_line_rec.line_id)
739 and source_code = 'OE'
740 and released_status = 'C'
741 group by requested_quantity_uom;
742
743 EXCEPTION
744
745 WHEN NO_DATA_FOUND THEN
746 NULL;
747
748 END;
749
750 IF l_debug_level > 0 THEN
751 oe_debug_pub.add( 'DELIVERY SHIPPING QUANTITY '||L_DEL_SHIPPING_QUANTITY , 3 ) ;
752 END IF;
753
754 END IF; /* unshipped count more than 0 */
755
756 END IF; /* Line set id not null */
757
758 l_line_rec.shipping_quantity := nvl(l_line_rec.shipping_quantity,0) + p_cal_tolerance_tbl(1).quantity_to_be_shipped + l_del_shipping_quantity;
759
760 IF l_line_rec.order_quantity_uom <> p_cal_tolerance_tbl(1).shipping_uom THEN
761 l_shipped_quantity := OE_Order_Misc_Util.Convert_Uom
762 (
763 l_line_rec.inventory_item_id,
764 p_cal_tolerance_tbl(1).shipping_uom,
765 l_line_rec.order_quantity_uom,
766 l_line_rec.shipping_quantity
767 );
768 IF l_debug_level > 0 THEN
769 oe_debug_pub.add( 'TOTAL QUANTITY TO BE SHIPPED AFTER UOM CONV : '||TO_CHAR ( L_SHIPPED_QUANTITY ) , 3 ) ;
770 END IF;
771 ELSE
772 l_shipped_quantity := l_line_rec.shipping_quantity;
773 END IF;
774
775 -- Check if the current quantity shipping will fulfill the line within
776 -- tolerance.
777
778 IF l_debug_level > 0 THEN
779 oe_debug_pub.add( 'SHIPPED QUANTITY '||TO_CHAR ( L_SHIPPED_QUANTITY ) , 3 ) ;
780 END IF;
781 OE_Shipping_Integration_PVT.Check_Shipment_Line
782 (
783 p_line_rec => l_line_rec,
784 p_shipped_quantity => l_shipped_quantity,
785 x_result_out => l_x_result_out
786 );
787
788 IF l_x_result_out = OE_GLOBALS.G_SHIPPED_WITHIN_TOL_BELOW AND
789 l_planned_quantity_passed = FND_API.G_TRUE THEN
790
791 IF l_debug_level > 0 THEN
792 oe_debug_pub.add( 'SHIPPED WITHIN TOLERANCE BELOW' , 3 ) ;
793 END IF;
794
795 -- Tolerance needs to be updated so that ship confirm can result
796 -- in split.
797
798 l_shipped_quantity := 0;
799 l_line_rec.shipping_quantity := p_cal_tolerance_tbl(1).quantity_to_be_shipped + p_cal_tolerance_tbl(1).planned_quantity;
800 l_ordered_quantity := l_line_rec.ordered_quantity;
801 IF l_debug_level > 0 THEN
802 oe_debug_pub.add( 'TOTAL QUANTITY TO BE SHIPPED : '||TO_CHAR ( L_SHIPPED_QUANTITY ) , 3 ) ;
803 END IF;
804 IF l_debug_level > 0 THEN
805 oe_debug_pub.add( 'TOTAL ORDERED QUANTITY : '||TO_CHAR ( L_ORDERED_QUANTITY ) , 3 ) ;
806 END IF;
807 IF l_line_rec.order_quantity_uom <> p_cal_tolerance_tbl(1).shipping_uom THEN
808 l_shipped_quantity := OE_Order_Misc_Util.Convert_Uom
809 (
810 l_line_rec.inventory_item_id,
811 p_cal_tolerance_tbl(1).shipping_uom,
812 l_line_rec.order_quantity_uom,
813 l_line_rec.shipping_quantity
814 );
815 IF l_debug_level > 0 THEN
816 oe_debug_pub.add( 'TOTAL QUANTITY TO BE SHIPPED AFTER UOM CONV : '||TO_CHAR ( L_SHIPPED_QUANTITY ) , 3 ) ;
817 END IF;
818 ELSE
819 l_shipped_quantity := l_line_rec.shipping_quantity;
820 END IF;
821
822 IF l_debug_level > 0 THEN
823 oe_debug_pub.add( 'SHIPPED QUANTITY '||TO_CHAR ( L_SHIPPED_QUANTITY ) , 3 ) ;
824 END IF;
825
826 -- Get the total ordered and shipped quantities if it is a part of
827 -- a line set.
828
829 IF l_line_rec.line_set_id IS NOT NULL AND
830 l_line_rec.line_set_id <> FND_API.G_MISS_NUM THEN
831
832 OE_Shipping_Integration_PUB.Get_Quantity
833 (
834 p_api_version_number => 1.0,
835 p_line_id => l_line_rec.line_id,
836 p_line_set_id => l_line_rec.line_set_id,
837 x_ordered_quantity => l_x_ordered_quantity,
838 x_shipped_quantity => l_x_shipped_quantity,
839 x_shipping_quantity => l_x_shipping_quantity,
840 x_return_status => l_x_return_status,
841 x_msg_count => l_msg_count,
842 x_msg_data => l_msg_data
843 );
844
845 IF l_debug_level > 0 THEN
846 oe_debug_pub.add( 'TOTAL ORDERED QUANTITY : '||TO_CHAR ( L_X_ORDERED_QUANTITY ) , 3 ) ;
847 END IF;
848 IF l_debug_level > 0 THEN
849 oe_debug_pub.add( 'TOTAL SHIPPED QUANTITY : '||TO_CHAR ( L_X_SHIPPED_QUANTITY ) , 3 ) ;
850 END IF;
851 l_shipped_quantity := l_shipped_quantity + l_x_shipped_quantity;
852 l_ordered_quantity := l_x_ordered_quantity;
853
854 END IF;
855
856 -- Calculate the new tolerance value
857 IF l_debug_level > 0 THEN
858 oe_debug_pub.add( 'TOTAL ORDERED QUANTITY : '||TO_CHAR ( L_ORDERED_QUANTITY ) , 3 ) ;
859 END IF;
860 IF l_debug_level > 0 THEN
861 oe_debug_pub.add( 'TOTAL SHIPPED QUANTITY : '||TO_CHAR ( L_SHIPPED_QUANTITY ) , 3 ) ;
862 END IF;
863
864 l_ship_tolerance := ((l_ordered_quantity - l_shipped_quantity)/l_ordered_quantity) * 100;
865 IF l_ship_tolerance < 0 THEN
866 l_ship_tolerance := 0;
867 END IF;
868 IF l_debug_level > 0 THEN
869 oe_debug_pub.add( 'NEW TOLERANCE VALUE : '|| TO_CHAR ( L_SHIP_TOLERANCE ) , 3 ) ;
870 END IF;
871 x_update_tolerance_flag := FND_API.G_TRUE;
872 x_ship_tolerance := l_ship_tolerance;
873
874 ELSIF l_x_result_out = OE_GLOBALS.G_SHIPPED_BEYOND_TOLERANCE THEN
875 x_ship_beyond_tolerance := FND_API.G_TRUE;
876 ELSIF l_x_result_out = OE_GLOBALS.G_SHIPPED_WITHIN_TOL_BELOW OR
877 l_x_result_out = OE_GLOBALS.G_FULLY_SHIPPED OR
878 l_x_result_out = OE_GLOBALS.G_SHIPPED_WITHIN_TOL_ABOVE THEN
879 IF l_debug_level > 0 THEN
880 oe_debug_pub.add( 'SHIPPED WITHIN TOLERANCE ' , 3 ) ;
881 END IF;
882 x_shipped_within_tolerance := FND_API.G_TRUE;
883 END IF;
884
885 END IF;
886
887
888 x_return_status := FND_API.G_RET_STS_SUCCESS;
889 IF l_debug_level > 0 THEN
890 oe_debug_pub.add( 'EXITING FROM OE_SHIPPING_INTEGRATION_PUB.GET_TOLERANCE ' , 1 ) ;
891 END IF;
892
893 EXCEPTION
894
895 WHEN NO_DATA_FOUND THEN
896
897
898 x_return_status := FND_API.G_RET_STS_ERROR;
899
900 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
901 THEN
902 x_return_status := FND_API.G_RET_STS_ERROR;
903 END IF;
904
905 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
906
907 IF l_debug_level > 0 THEN
908 oe_debug_pub.add( 'UNEXPECTED ERROR : '||SQLERRM , 1 ) ;
909 END IF;
910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
911
912 -- Get message count and data
913
914 FND_MSG_PUB.Count_And_Get
915 ( p_count => x_msg_count
916 , p_data => x_msg_data
917 );
918
919 WHEN OTHERS THEN
920
921 IF l_debug_level > 0 THEN
922 oe_debug_pub.add( 'UNEXPECTED ERROR : '||SQLERRM , 1 ) ;
923 END IF;
924 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
925
926 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
927 THEN
928 FND_MSG_PUB.Add_Exc_Msg
929 ( G_PKG_NAME
930 , 'Get_Tolerance'
931 );
932 END IF;
933
934 -- Get message count and data
935
936 FND_MSG_PUB.Count_And_Get
937 ( p_count => x_msg_count
938 , p_data => x_msg_data
939 );
940
941 END Get_Tolerance;
942
943 /*
944 This procedure will return the total ordered quantity and the total shipped
945 quantity. It adds up the ordered and shipped quantities for all the lines
946 which may have been created because of split.
947 */
948
949 PROCEDURE Get_Quantity
950 (
951 p_api_version_number IN NUMBER
952 , p_line_id IN NUMBER
953 , p_line_set_id IN NUMBER
954 , x_ordered_quantity OUT NOCOPY /* file.sql.39 change */ NUMBER
955 , x_shipped_quantity OUT NOCOPY /* file.sql.39 change */ NUMBER
956 , x_shipping_quantity OUT NOCOPY /* file.sql.39 change */ NUMBER
957 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
958 , x_msg_count OUT NOCOPY /* file.sql.39 change */ VARCHAR2
959 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
960 )
961 IS
962
963 l_api_version_number CONSTANT NUMBER := 1.0;
964 l_api_name CONSTANT VARCHAR2(30) := 'Get_Quantity';
965 l_x_ordered_quantity NUMBER;
966 l_x_shipped_quantity NUMBER;
967 l_x_shipping_quantity NUMBER;
968
969 --
970 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
971 --
972 BEGIN
973
974 -- Standard call to check for call compatibility
975 IF l_debug_level > 0 THEN
976 oe_debug_pub.add( 'ENTERING OE_SHIPPING_INTEGRATION_PUB.GET_QUANTITY '||TO_CHAR ( P_LINE_ID ) ||'/'||TO_CHAR ( P_LINE_SET_ID ) , 1 ) ;
977 END IF;
978
979 IF NOT FND_API.Compatible_API_Call
980 ( l_api_version_number
981 , p_api_version_number
982 , l_api_name
983 , G_PKG_NAME
984 )
985 THEN
986 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
987 END IF;
988
989 SELECT SUM(ordered_quantity)
990 , SUM(shipped_quantity)
991 , SUM(shipping_quantity)
992 INTO l_x_ordered_quantity
993 , l_x_shipped_quantity
994 , l_x_shipping_quantity
995 FROM oe_order_lines
996 WHERE line_set_id = p_line_set_id;
997
998 x_ordered_quantity := l_x_ordered_quantity;
999 x_shipped_quantity := l_x_shipped_quantity;
1000 x_shipping_quantity := l_x_shipping_quantity;
1001 x_return_status := FND_API.G_RET_STS_SUCCESS;
1002
1003 IF l_debug_level > 0 THEN
1004 oe_debug_pub.add( 'EXITING OE_SHIPPING_INTEGRATION_PUB.GET_QUANTITY ' , 1 ) ;
1005 END IF;
1006 EXCEPTION
1007
1008 WHEN NO_DATA_FOUND THEN
1009
1010 x_ordered_quantity := 0;
1011 x_shipped_quantity := 0;
1012 x_shipping_quantity := 0;
1013
1014 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1015 THEN
1016 x_return_status := FND_API.G_RET_STS_ERROR;
1017 END IF;
1018
1019 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1020
1021 IF l_debug_level > 0 THEN
1022 oe_debug_pub.add( 'UNEXPECTED ERROR : '||SQLERRM , 1 ) ;
1023 END IF;
1024 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1025
1026 -- Get message count and data
1027
1028 FND_MSG_PUB.Count_And_Get
1029 ( p_count => x_msg_count
1030 , p_data => x_msg_data
1031 );
1032
1033 WHEN OTHERS THEN
1034
1035 IF l_debug_level > 0 THEN
1036 oe_debug_pub.add( 'UNEXPECTED ERROR : '||SQLERRM , 1 ) ;
1037 END IF;
1038 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1039
1040 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1041 THEN
1042 FND_MSG_PUB.Add_Exc_Msg
1043 ( G_PKG_NAME
1044 , 'Get_Quantity'
1045 );
1046 END IF;
1047
1048 -- Get message count and data
1049
1050 FND_MSG_PUB.Count_And_Get
1051 ( p_count => x_msg_count
1052 , p_data => x_msg_data
1053 );
1054
1055 END Get_Quantity;
1056
1057 PROCEDURE Update_Shipping_Interface
1058 (
1059 p_api_version_number IN NUMBER
1060 , p_line_id IN NUMBER
1061 , p_shipping_interfaced_flag IN VARCHAR2
1062 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1063 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1064 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1065 )
1066 IS
1067 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
1068 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
1069 l_index Number;
1070 l_firm_flag Varchar2(1) := Null;
1071 l_ato_line_id Number;
1072 l_top_model_line_id Number;
1073 l_item_type_code Varchar2(30);
1074 l_smc_flag Varchar2(1);
1075 --
1076 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1077 --
1078 l_is_op_create VARCHAR2(1) := 'N';
1079 BEGIN
1080
1081 IF l_debug_level > 0 THEN
1082 oe_debug_pub.add( 'ENTERING OE_SHIPPING_INTEGRATION_PUB.UPDATE_SHIPPING_INTERFACE' , 1 ) ;
1083 END IF;
1084
1085 IF OE_GLOBALS.G_ASO_INSTALLED IS NULL THEN
1086 OE_GLOBALS.G_ASO_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(697);
1087 END IF;
1088
1089 IF p_line_id IS NULL OR
1090 p_line_id = FND_API.G_MISS_NUM THEN
1091
1092 IF l_debug_level > 0 THEN
1093 oe_debug_pub.add( 'LINE ID IS NULL ' , 1 ) ;
1094 END IF;
1095
1096 FND_MESSAGE.SET_NAME('ONT','OE_SHP_LINE_ID_MISSING');
1097 OE_MSG_PUB.ADD;
1098 RAISE FND_API.G_EXC_ERROR;
1099
1100 END IF;
1101
1102 IF p_shipping_interfaced_flag IS NOT NULL AND
1103 p_shipping_interfaced_flag NOT IN ('Y','N') THEN
1104
1105 IF l_debug_level > 0 THEN
1106 oe_debug_pub.add( 'INVALID VALUE FOR SHIPPING INTERFACED FLAG' , 1 ) ;
1107 END IF;
1108
1109 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
1110 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1111 OE_Order_Util.Get_Attribute_Name('shipping_interfaced_flag'));
1112 OE_MSG_PUB.Add;
1113
1114 RAISE FND_API.G_EXC_ERROR;
1115
1116 END IF;
1117
1118 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510'
1119 AND Oe_Sys_Parameters.Value('FIRM_DEMAND_EVENTS') = 'SHIPPING_INTERFACED'
1120 THEN
1121 l_firm_flag := 'Y';
1122 END IF;
1123
1124 SAVEPOINT Update_Shipping_Interface;
1125
1126 update oe_order_lines
1127 set shipping_interfaced_flag = p_shipping_interfaced_flag,
1128 firm_demand_flag = NVL(l_firm_flag,firm_demand_flag),
1129 last_update_date = SYSDATE,
1130 last_updated_by = FND_GLOBAL.USER_ID,
1131 last_update_login = FND_GLOBAL.LOGIN_ID,
1132 lock_control = lock_control + 1
1133 where line_id = p_line_id;
1134
1135
1136 -- Note that even though a direct update is happening here in the preceeding statement,
1137 -- we are not calling the notification framework
1138
1139 -- Check if ISO is installed, then only lock the rows and call Process_Requests_And_Notify
1140
1141 IF ( (OE_GLOBALS.G_ASO_INSTALLED = 'Y' ) OR
1142 (NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'),'N') = 'Y') ) THEN
1143
1144 OE_Line_Util.Lock_Rows
1145 (p_line_id => p_line_id
1146 ,x_line_tbl => l_old_line_tbl
1147 ,x_return_status => x_return_status
1148 );
1149
1150 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1151 RAISE FND_API.G_EXC_ERROR;
1152 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1154 END IF;
1155
1156 l_line_tbl := l_old_line_tbl;
1157 l_line_tbl(1).shipping_interfaced_flag := p_shipping_interfaced_flag;
1158 l_line_tbl(1).firm_demand_flag := NVL(l_firm_flag,
1159 l_line_tbl(1).firm_demand_flag);
1160 l_line_tbl(1).last_update_date := SYSDATE;
1161 l_line_tbl(1).last_updated_by := FND_GLOBAL.USER_ID;
1162 l_line_tbl(1).last_update_login := FND_GLOBAL.LOGIN_ID;
1163 l_line_tbl(1).lock_control := l_line_tbl(1).lock_control + 1;
1164
1165 /* comenting following lines because of bug 1880716
1166 IF (l_line_tbl(1).item_type_code = 'CONFIG') OR
1167 (l_line_tbl(1).item_type_code IN ('STANDARD','OPTION') AND -- 1820608
1168 l_line_tbl(1).ato_line_id IS NOT NULL) THEN
1169 oe_debug_pub.add('Do not update flow status ');
1170 ELSE
1171 oe_debug_pub.add('Update flow status ');
1172 l_line_tbl(1).flow_status_code := 'AWAITING_SHIPPING';
1173 END IF;
1174 */
1175
1176 IF l_firm_flag = 'Y' THEN
1177 IF l_line_tbl(1).ship_model_complete_flag = 'Y' THEN
1178
1179 Update oe_order_lines_all
1180 Set firm_demand_flag = 'Y'
1181 Where top_model_line_id = l_line_tbl(1).top_model_line_id;
1182
1183 ELSIF (l_line_tbl(1).ato_line_id is not null
1184 AND NOT (l_line_tbl(1).ato_line_id = p_line_id AND
1185 l_line_tbl(1).item_type_code IN
1186 (OE_GLOBALS.G_ITEM_STANDARD,
1187 OE_GLOBALS.G_ITEM_OPTION)))
1188 THEN
1189
1190 Update oe_order_lines_all
1191 Set firm_demand_flag = 'Y'
1192 Where ato_line_id = l_line_tbl(1).ato_line_id;
1193
1194 END IF;
1195
1196
1197 END IF; -- Firm flag
1198
1199 -- added for notification framework
1200 -- If using notification framework, no call to process_requests_and_notify is needed
1201 -- check code release level first. Notification framework is at Pack H level
1202
1203 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN
1204
1205 -- calling notification framework to get index position
1206 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
1207 p_header_id =>l_line_tbl(1).header_id,
1208 p_old_line_rec => l_old_line_tbl(1),
1209 p_line_rec =>l_line_tbl(1),
1210 p_line_id => p_line_id,
1211 x_index => l_index,
1212 x_return_status => x_return_status);
1213 IF l_debug_level > 0 THEN
1214 oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_SHIPPING_INTEGRATION_PUB IS:' || X_RETURN_STATUS ) ;
1215 END IF;
1216 IF l_debug_level > 0 THEN
1217 oe_debug_pub.add( 'GLOBAL PICTURE INDEX IS: ' || L_INDEX , 1 ) ;
1218 END IF;
1219
1220 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1221 RAISE FND_API.G_EXC_ERROR;
1222 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1224 END IF;
1225
1226 IF l_index IS NOT NULL THEN
1227 -- update the global picture directly
1228
1229 -- bug 3454703
1230
1231 IF OE_ORDER_UTIL.g_old_line_tbl.EXISTS(l_index) THEN
1232 IF OE_ORDER_UTIL.g_old_line_tbl(l_index).OPERATION = oe_globals.g_opr_create THEN
1233 l_is_op_create := 'Y';
1234 END IF;
1235 END IF;
1236 -- end bug 3454703
1237
1238 OE_ORDER_UTIL.g_old_line_tbl(l_index) := l_old_line_tbl(1);
1239 OE_ORDER_UTIL.g_line_tbl(l_index) := OE_ORDER_UTIL.g_old_line_tbl(l_index);
1240 -- bug 3454703
1241 IF l_is_op_create = 'Y' THEN
1242 OE_ORDER_UTIL.g_old_line_tbl(l_index).OPERATION := oe_globals.g_opr_create;
1243 END IF;
1244 -- end bug 3454703
1245 OE_ORDER_UTIL.g_line_tbl(l_index).line_id := l_line_tbl(1).line_id;
1246 OE_ORDER_UTIL.g_line_tbl(l_index).header_id := l_line_tbl(1).header_id;
1247
1248 OE_ORDER_UTIL.g_line_tbl(l_index).shipping_interfaced_flag := p_shipping_interfaced_flag;
1249 OE_ORDER_UTIL.g_line_tbl(l_index).last_update_date := SYSDATE;
1250 OE_ORDER_UTIL.g_line_tbl(l_index).last_updated_by := FND_GLOBAL.USER_ID;
1251 OE_ORDER_UTIL.g_line_tbl(l_index).last_update_login := FND_GLOBAL.LOGIN_ID;
1252 OE_ORDER_UTIL.g_line_tbl(l_index).last_update_login := FND_GLOBAL.LOGIN_ID;
1253 END IF;
1254
1255 ELSE /*pre-pack H*/
1256
1257 OE_Order_PVT.Process_Requests_And_Notify
1258 ( p_process_requests => FALSE
1259 , p_notify => TRUE
1260 , x_return_status => x_return_status
1261 , p_line_tbl => l_line_tbl
1262 , p_old_line_tbl => l_old_line_tbl
1263 );
1264
1265 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1266 RAISE FND_API.G_EXC_ERROR;
1267 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1268 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1269 END IF;
1270
1271 END IF; /* pack H */
1272
1273 ELSE
1274
1275 IF l_firm_flag = 'Y' THEN
1276
1277 BEGIN
1278
1279 Select ato_line_id ,
1280 item_type_code,
1281 ship_model_complete_flag,
1282 top_model_line_id
1283 Into l_ato_line_id,
1284 l_item_type_code,
1285 l_smc_flag,
1286 l_top_model_line_id
1287 From oe_order_lines_all
1288 Where line_id = p_line_id;
1289
1290 IF l_smc_flag = 'Y' THEN
1291
1292 Update oe_order_lines_all
1293 Set firm_demand_flag = 'Y'
1294 Where top_model_line_id = l_top_model_line_id;
1295 ELSIF (l_ato_line_id is not null
1296 AND NOT (l_ato_line_id = p_line_id AND
1297 l_item_type_code IN (OE_GLOBALS.G_ITEM_STANDARD,
1298 OE_GLOBALS.G_ITEM_OPTION)))
1299 THEN
1300
1301 Update oe_order_lines_all
1302 Set firm_demand_flag = 'Y'
1303 Where ato_line_id = l_ato_line_id;
1304
1305 END IF;
1306
1307 END; -- For begin
1308
1309
1310 END IF; -- Firm flag
1311 END IF; -- IF OE_GLOBALS.G_ASO_INSTALLED = 'Y' or DBI installed.
1312
1313 IF l_debug_level > 0 THEN
1314 oe_debug_pub.add( 'EXITING OE_SHIPPING_INTEGRATION_PUB.UPDATE_SHIPPING_INTERFACE' , 1 ) ;
1315 END IF;
1316
1317 x_return_status := FND_API.G_RET_STS_SUCCESS;
1318
1319 EXCEPTION
1320
1321 WHEN FND_API.G_EXC_ERROR THEN
1322
1323 ROLLBACK TO Update_Shipping_Interface;
1324
1325 OE_MSG_PUB.Count_And_Get
1326 ( p_count => x_msg_count
1327 , p_data => x_msg_data
1328 );
1329
1330 x_return_status := FND_API.G_RET_STS_ERROR;
1331
1332 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1333
1334 ROLLBACK TO Update_Shipping_Interface;
1335
1336 -- Get message count and data
1337
1338 OE_MSG_PUB.Count_And_Get
1339 ( p_count => x_msg_count
1340 , p_data => x_msg_data
1341 );
1342
1343 IF l_debug_level > 0 THEN
1344 oe_debug_pub.add( 'UNEXPECTED ERROR : '||SQLERRM , 1 ) ;
1345 END IF;
1346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1347
1348 WHEN OTHERS THEN
1349
1350 ROLLBACK TO Update_Shipping_Interface;
1351
1352 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1353 THEN
1354 OE_MSG_PUB.Add_Exc_Msg
1355 ( G_PKG_NAME
1356 , 'Update_Shipping_Interface'
1357 );
1358 END IF;
1359
1360 -- Get message count and data
1361
1362 OE_MSG_PUB.Count_And_Get
1363 ( p_count => x_msg_count
1364 , p_data => x_msg_data
1365 );
1366
1367 IF l_debug_level > 0 THEN
1368 oe_debug_pub.add( 'UNEXPECTED ERROR : '||SQLERRM , 1 ) ;
1369 END IF;
1370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1371
1372 END Update_Shipping_Interface;
1373
1374 -- HW added qty2 for OPM in the procedure parameters
1375
1376 PROCEDURE Get_Min_Max_Tolerance_Quantity
1377 (
1378 p_api_version_number IN NUMBER
1379 , p_line_id IN NUMBER
1380 , x_min_remaining_quantity OUT NOCOPY /* file.sql.39 change */ NUMBER
1381 , x_max_remaining_quantity OUT NOCOPY /* file.sql.39 change */ NUMBER
1382 , x_min_remaining_quantity2 OUT NOCOPY /* file.sql.39 change */ NUMBER
1383 , x_max_remaining_quantity2 OUT NOCOPY /* file.sql.39 change */ NUMBER
1384 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1385 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1386 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1387 )
1388
1389 IS
1390
1391 l_api_version_number CONSTANT NUMBER := 1.0;
1392 l_api_name CONSTANT VARCHAR2(30) := 'Get_Min_Max_Tolerance_Quantity';
1393 l_line_set_id NUMBER;
1394 l_ship_tolerance_above NUMBER;
1395 l_ship_tolerance_below NUMBER;
1396 l_tolerance_quantity_below NUMBER;
1397 l_tolerance_quantity_above NUMBER;
1398
1399 l_ordered_quantity NUMBER;
1400 l_shipped_quantity NUMBER;
1401 l_shipping_quantity NUMBER;
1402 l_min_quantity_remaining NUMBER;
1403 l_max_quantity_remaining NUMBER;
1404 -- HW OPM added qty2 for OPM
1405 l_ordered_quantity2 NUMBER;
1406 l_shipped_quantity2 NUMBER;
1407 l_shipping_quantity2 NUMBER;
1408 l_min_quantity_remaining2 NUMBER;
1409 l_max_quantity_remaining2 NUMBER;
1410
1411 l_top_model_line_id NUMBER;
1412 l_ato_line_id NUMBER;
1413
1414 --
1415 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1416 --
1417 BEGIN
1418
1419 IF l_debug_level > 0 THEN
1420 oe_debug_pub.add( 'ENTERING OE_SHIPPING_INTEGRATION_PUB.GET_MIN_MAX_TOLERANCE_QUANTITY' , 1 ) ;
1421 END IF;
1422
1423 x_return_status := FND_API.G_RET_STS_SUCCESS;
1424 /*
1425 IF NOT FND_API.Compatible_API_Call
1426 ( l_api_version_number
1427 , p_api_version_number
1428 , l_api_name
1429 , G_PKG_NAME
1430 )
1431 THEN
1432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1433 END IF;
1434 */
1435
1436
1437 OE_Shipping_Tolerances_PUB.Get_Min_Max_Tolerance_Quantity
1438 (
1439 p_api_version_number => p_api_version_number,
1440 p_line_id => p_line_id,
1441 x_min_remaining_quantity => x_min_remaining_quantity,
1442 x_max_remaining_quantity => x_max_remaining_quantity,
1443 x_min_remaining_quantity2 => x_min_remaining_quantity2,
1444 x_max_remaining_quantity2 => x_max_remaining_quantity2,
1445 x_return_status => x_return_status,
1446 x_msg_count => x_msg_count,
1447 x_msg_data => x_msg_data
1448 );
1449
1450 IF l_debug_level > 0 THEN
1451 oe_debug_pub.add( 'SHP RETURN MIN REMAINING QUANTITY : '||X_MIN_REMAINING_QUANTITY , 3 ) ;
1452 END IF;
1453 IF l_debug_level > 0 THEN
1454 oe_debug_pub.add( 'SHP RETURN MAX REMAINING QUANTITY : '||X_MAX_REMAINING_QUANTITY , 3 ) ;
1455 END IF;
1456 IF l_debug_level > 0 THEN
1457 oe_debug_pub.add( 'SHP RETURN MIN REMAINING QUANTITY2 : '||X_MIN_REMAINING_QUANTITY2 , 3 ) ;
1458 END IF;
1459 IF l_debug_level > 0 THEN
1460 oe_debug_pub.add( 'SHP RETURN MAX REMAINING QUANTITY2 : '||X_MAX_REMAINING_QUANTITY2 , 3 ) ;
1461 END IF;
1462
1463 IF l_debug_level > 0 THEN
1464 oe_debug_pub.add( 'EXITING OE_SHIPPING_INTEGRATION_PUB.GET_MIN_MAX_TOLERANCE_QUANTITY '||X_RETURN_STATUS , 1 ) ;
1465 END IF;
1466
1467 EXCEPTION
1468
1469 WHEN NO_DATA_FOUND THEN
1470
1471 x_min_remaining_quantity := 0;
1472 x_max_remaining_quantity := 0;
1473
1474 -- HW reset values for qty2 for OPM
1475 x_min_remaining_quantity2 := 0;
1476 x_max_remaining_quantity2 := 0;
1477
1478 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1479 THEN
1480 x_return_status := FND_API.G_RET_STS_ERROR;
1481 END IF;
1482
1483 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1484
1485 IF l_debug_level > 0 THEN
1486 oe_debug_pub.add( 'UNEXPECTED ERROR : '||SQLERRM , 1 ) ;
1487 END IF;
1488 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1489
1490 -- Get message count and data
1491
1492 FND_MSG_PUB.Count_And_Get
1493 ( p_count => x_msg_count
1494 , p_data => x_msg_data
1495 );
1496
1497 WHEN OTHERS THEN
1498
1499 IF l_debug_level > 0 THEN
1500 oe_debug_pub.add( 'UNEXPECTED ERROR : '||SQLERRM , 1 ) ;
1501 END IF;
1502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1503
1504 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1505 THEN
1506 FND_MSG_PUB.Add_Exc_Msg
1507 ( G_PKG_NAME
1508 , 'Get_Min_Max_Tolerance_Quantity'
1509 );
1510 END IF;
1511
1512 -- Get message count and data
1513
1514 FND_MSG_PUB.Count_And_Get
1515 ( p_count => x_msg_count
1516 , p_data => x_msg_data
1517 );
1518
1519 END Get_Min_Max_Tolerance_Quantity;
1520
1521
1522 FUNCTION Check_Import_Pending_Lines
1523 (p_header_id IN NUMBER
1524 ,p_ship_set_id IN NUMBER
1525 ,p_top_model_line_id IN NUMBER
1526 ,p_transactable_flag IN VARCHAR2
1527 ,x_return_status OUT NOCOPY VARCHAR2)
1528 RETURN BOOLEAN IS
1529 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1530 l_line_id NUMBER;
1531 BEGIN
1532 x_return_status := FND_API.G_RET_STS_SUCCESS;
1533
1534 SELECT oel.line_id
1535 INTO l_line_id
1536 FROM oe_order_lines_all oel,
1537 mtl_system_items msi
1538 WHERE oel.header_id = p_header_id
1539 AND (oel.ship_set_id = p_ship_set_id OR
1540 (oel.top_model_line_id = p_top_model_line_id AND
1541 oel.ship_model_complete_flag = 'Y'))
1542 AND oel.inventory_item_id = msi.inventory_item_id
1543 and oel.ship_from_org_id = msi.organization_id
1544 AND oel.ordered_quantity > 0
1545 and ((p_transactable_flag = 'N') OR
1546 (p_transactable_flag = 'Y' AND
1547 msi.mtl_transactions_enabled_flag = 'Y'))
1548 AND oel.shipping_interfaced_flag = 'N'
1549 AND (oel.shippable_flag = 'Y' OR
1550 (EXISTS (SELECT 'Y'
1551 FROM oe_order_lines_all oel1
1552 WHERE oel1.header_id = p_header_id
1553 AND (oel1.ship_set_id = p_ship_set_id OR
1554 (oel1.top_model_line_id = p_top_model_line_id AND
1555 oel1.ship_model_complete_flag = 'Y'))
1556 AND oel1.ato_line_id = oel1.line_id
1557 AND oel1.item_type_code in ('MODEL','CLASS')
1558 AND oel1.ordered_quantity > 0
1559 AND NOT EXISTS (SELECT 'Y'
1560 FROM oe_order_lines_all oel2
1561 WHERE oel2.top_model_line_id
1562 = oel1.top_model_line_id
1563 AND oel2.ato_line_id
1564 = oel1.ato_line_id
1565 AND oel2.item_type_code = 'CONFIG'))))
1566 AND ROWNUM = 1;
1567
1568 Return True;
1569
1570 EXCEPTION
1571 WHEN NO_DATA_FOUND THEN
1572 if l_debug_level > 0 then
1573 OE_DEBUG_PUB.Add('No Pending_Lines', 1);
1574 End if;
1575 Return False;
1576
1577 WHEN OTHERS THEN
1578 if l_debug_level > 0 then
1579 OE_DEBUG_PUB.Add('When Others in Check_Pick_pending_Lines'||SqlErrm, 1);
1580 End if;
1581 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1582 End Check_Import_Pending_Lines;
1583
1584 PROCEDURE ATO_Config_Line_Ship_Notified( p_application_id IN NUMBER,
1585 p_entity_short_name in VARCHAR2,
1586 p_validation_entity_short_name in VARCHAR2,
1587 p_validation_tmplt_short_name in VARCHAR2,
1588 p_record_set_tmplt_short_name in VARCHAR2,
1589 p_scope in VARCHAR2,
1590 p_result OUT NOCOPY NUMBER ) IS
1591
1592
1593 l_config_line_id NUMBER;
1594
1595 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1596
1597 config_line_ship_notified NUMBER := 0;
1598 BEGIN
1599
1600
1601
1602 IF (OE_LINE_SECURITY.g_record.ato_line_id IS NULL) OR
1603 (OE_LINE_SECURITY.g_record.ato_line_id = FND_API.G_MISS_NUM) OR
1604 (OE_LINE_SECURITY.g_record.item_type_code not in ('MODEL', 'CLASS')) OR
1605 (OE_LINE_SECURITY.g_record.line_id IS NULL) OR
1606 (OE_LINE_SECURITY.g_record.line_id = FND_API.G_MISS_NUM) OR
1607 (OE_LINE_SECURITY.g_record.ato_line_id <> OE_LINE_SECURITY.g_record.line_id)
1608 THEN
1609 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
1610 p_result := 0;
1611
1612 ELSE
1613
1614 BEGIN
1615
1616 SELECT line_id
1617 INTO l_config_line_id
1618 FROM oe_order_lines
1619 WHERE header_id = OE_LINE_SECURITY.g_record.header_id
1620 AND ato_line_id = OE_LINE_SECURITY.g_record.line_id
1621 AND item_type_code = 'CONFIG';
1622
1623 IF l_debug_level > 0 THEN
1624 oe_debug_pub.add('Config line line_id' || l_config_line_id);
1625 END IF;
1626
1627
1628 SELECT 1
1629 INTO config_line_ship_notified
1630 FROM wf_item_activity_statuses wias, wf_process_activities wpa
1631 WHERE wias.item_type='OEOL'
1632 AND wias.item_key=l_config_line_id
1633 AND wpa.activity_name='SHIP_LINE'
1634 AND wias.activity_status='NOTIFIED'
1635 AND wias.PROCESS_ACTIVITY = wpa.INSTANCE_ID;
1636
1637 EXCEPTION
1638 WHEN NO_DATA_FOUND THEN
1639 config_line_ship_notified := 0;
1640 IF l_debug_level > 0 THEN
1641 oe_debug_pub.add('In NO DATA FOUND - No config lines for this model have been ship notified');
1642 END IF;
1643 END;
1644
1645 IF config_line_ship_notified = 1 THEN
1646 p_result := 1;
1647 ELSE
1648 p_result := 0;
1649 END IF;
1650
1651 END IF;
1652
1653 END ATO_Config_Line_Ship_Notified;
1654
1655 /*-----------------------------------------------------------------
1656 -- PROCEDURE Get_SetSMC_Interface_Status
1657 -- Description : This API was added for bug 3623149 ,
1658 to be used by shipping.
1659 Setsmc_Output_Rec_Type.x_interface_status can
1660 have Y if all lines available in
1661 shipping, N if all lines are not available in shipping.
1662 Shipping will use the x_interface_status value
1663 only if the x_return_status is Success.
1664
1665 Change Record - This API is not only for SMC models but also
1666 used for non_SMC models and the name may be confusing...
1667 -----------------------------------------------------------------*/
1668 PROCEDURE Get_SetSMC_Interface_Status
1669 (p_setsmc_input_rec IN Setsmc_Input_Rec_Type
1670 ,p_setsmc_output_rec OUT NOCOPY /* file.sql.39 change */ Setsmc_Output_Rec_Type
1671 ,x_return_status OUT NOCOPY VARCHAR2)
1672 IS
1673 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1674 l_line_id NUMBER;
1675 l_header_id NUMBER := p_setsmc_input_rec.header_id;
1676 l_top_model_line_id NUMBER := p_setsmc_input_rec.top_model_line_id;
1677 l_ship_set_id NUMBER := p_setsmc_input_rec.ship_set_id;
1678 BEGIN
1679 x_return_status := FND_API.G_RET_STS_SUCCESS;
1680
1681 IF l_debug_level > 0 THEN
1682 oe_debug_pub.add('Entering Get_SetSMC_Interface_Status ', 1);
1683 oe_debug_pub.add('headerID = '||l_header_id, 3);
1684 oe_debug_pub.add('top_model_line_id = '||l_top_model_line_id, 3);
1685 oe_debug_pub.add('ship_set_id = '||l_ship_set_id, 3);
1686 END IF;
1687 BEGIN
1688 SELECT oel.line_id
1689 INTO l_line_id
1690 FROM oe_order_lines_all oel
1691 WHERE oel.header_id = l_header_id
1692 AND (oel.ship_set_id = l_ship_set_id OR
1693 oel.top_model_line_id = l_top_model_line_id)
1694 AND oel.shipping_interfaced_flag = 'N'
1695 AND (oel.shippable_flag = 'Y' OR
1696 (EXISTS (SELECT 'Y'
1697 FROM oe_order_lines_all oel1
1698 WHERE oel1.header_id = l_header_id
1699 AND (oel1.ship_set_id = l_ship_set_id OR
1700 oel1.top_model_line_id = l_top_model_line_id)
1701 AND oel1.ato_line_id = oel1.line_id
1702 AND oel1.item_type_code in ('MODEL','CLASS')
1703 AND NOT EXISTS (SELECT 'Y'
1704 FROM oe_order_lines_all oel2
1705 WHERE oel2.top_model_line_id
1706 = oel1.top_model_line_id
1707 AND oel2.ato_line_id
1708 = oel1.ato_line_id
1709 AND oel2.item_type_code = 'CONFIG'))))
1710 AND ROWNUM = 1;
1711
1712 -- If some shippable lines are found not interfaced to wsh
1713 p_setsmc_output_rec.x_interface_status := 'N';
1714
1715 IF l_debug_level > 0 THEN
1716 oe_debug_pub.add('leaving Get_SetSMC_Interface_Status '
1717 || p_setsmc_output_rec.x_interface_status
1718 || '-' || x_return_status, 1);
1719 END IF;
1720 EXCEPTION
1721 WHEN NO_DATA_FOUND THEN
1722 IF l_debug_level > 0 THEN
1723 oe_debug_pub.add('all shippable lines are interfaced to wsh', 1);
1724 END IF;
1725 p_setsmc_output_rec.x_interface_status := 'Y';
1726 END;
1727
1728 EXCEPTION
1729 WHEN OTHERS THEN
1730 IF l_debug_level > 0 THEN
1731 oe_debug_pub.add('OTHERS EXCEPTION ' || sqlerrm, 1);
1732 END IF;
1733
1734 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1735 p_setsmc_output_rec.x_interface_status := 'N';
1736
1737 END Get_SetSMC_Interface_Status;
1738 -- 3623149 changes ends
1739 END OE_Shipping_Integration_PUB;
1740