[Home] [Help]
PACKAGE BODY: APPS.OE_SHIPPING_TOLERANCES_PUB
Source
1 PACKAGE BODY OE_Shipping_Tolerances_PUB AS
2 /* $Header: OEXPTOLB.pls 120.0 2005/06/01 01:45:33 appldev noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Shipping_Tolerances_PUB';
7
8 -- Start of Comments
9 -- API name OE_Shipping_Tolerances_PUB
10 -- Type Public
11 -- Version Current version = 1.0
12 -- Initial version = 1.0
13
14 -- HW added qty2 for OPM in the procedure parameters
15 -- INVCONV - NOT SURE IF NEED TO CHANGE THESE
16
17 PROCEDURE Get_Min_Max_Tolerance_Quantity
18 (
19 p_api_version_number IN NUMBER
20 , p_line_id IN NUMBER
21 , x_min_remaining_quantity OUT NOCOPY /* file.sql.39 change */ NUMBER
22 , x_max_remaining_quantity OUT NOCOPY /* file.sql.39 change */ NUMBER
23 , x_min_remaining_quantity2 OUT NOCOPY /* file.sql.39 change */ NUMBER
24 , x_max_remaining_quantity2 OUT NOCOPY /* file.sql.39 change */ NUMBER
25 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
26 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
27 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
28 )
29
30 IS
31
32 l_api_version_number CONSTANT NUMBER := 1.0;
33 l_api_name CONSTANT VARCHAR2(30) := 'Get_Min_Max_Tolerance_Quantity';
34 l_line_set_id NUMBER;
35 l_ship_tolerance_above NUMBER;
36 l_ship_tolerance_below NUMBER;
37 l_tolerance_quantity_below NUMBER;
38 l_tolerance_quantity_above NUMBER;
39
40 l_ordered_quantity NUMBER;
41 l_shipped_quantity NUMBER;
42 l_shipping_quantity NUMBER;
43 l_min_quantity_remaining NUMBER;
44 l_max_quantity_remaining NUMBER;
45 -- HW OPM added qty2 for OPM
46 l_ordered_quantity2 NUMBER;
47 l_shipped_quantity2 NUMBER;
48 l_shipping_quantity2 NUMBER;
49 l_min_quantity_remaining2 NUMBER;
50 l_max_quantity_remaining2 NUMBER;
51
52 l_top_model_line_id NUMBER;
53 l_ato_line_id NUMBER;
54 l_order_quantity_uom VARCHAR2(3);
55 l_shipping_quantity_uom VARCHAR2(3);
56 l_del_shipping_quantity NUMBER;
57 l_del_shipped_quantity NUMBER;
58 -- l_del_shipping_quantity2 NUMBER; -- INVCONV not used
59 -- l_del_shipped_quantity2 NUMBER; -- INVCONV not used
60
61 l_count_unshipped NUMBER;
62 l_item_rec OE_ORDER_CACHE.item_rec_type;
63 l_OPM_shipped_quantity NUMBER(19,9);
64 l_OPM_shipping_quantity_uom VARCHAR2(4);
65 l_OPM_order_quantity_uom VARCHAR2(4);
66 l_inventory_item_id NUMBER;
67 l_ship_from_org_id NUMBER;
68
69 l_status VARCHAR2(1);
70
71 l_msg_count NUMBER;
72 l_msg_data VARCHAR2(2000);
73
74 l_validated_quantity NUMBER;
75 l_primary_quantity NUMBER;
76 l_qty_return_status VARCHAR2(1);
77
78 --
79 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
80 --
81 BEGIN
82
83 IF l_debug_level > 0 THEN
84 oe_debug_pub.add( 'ENTERING OE_SHIPPING_TOLERANCES_PUB.GET_MIN_MAX_TOLERANCE_QUANTITY' , 1 ) ;
85 END IF;
86
87 x_return_status := FND_API.G_RET_STS_SUCCESS;
88 /*
89 IF NOT FND_API.Compatible_API_Call
90 ( l_api_version_number
91 , p_api_version_number
92 , l_api_name
93 , G_PKG_NAME
94 )
95 THEN
96 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
97 END IF;
98 */
99 -- HW OPM retrieve qty2 for OPM
100 SELECT ship_tolerance_below,
101 ship_tolerance_above,
102 line_set_id,
103 ordered_quantity,
104 shipped_quantity,
105 ordered_quantity2,
106 shipped_quantity2,
107 top_model_line_id,
108 ato_line_id,
109 order_quantity_uom,
110 ship_from_org_id,
111 inventory_item_id
112 INTO l_ship_tolerance_below,
113 l_ship_tolerance_above,
114 l_line_set_id,
115 l_ordered_quantity,
116 l_shipped_quantity,
117 l_ordered_quantity2,
118 l_shipped_quantity2,
119 l_top_model_line_id,
120 l_ato_line_id,
121 l_order_quantity_uom,
122 l_ship_from_org_id,
123 l_inventory_item_id
124 FROM OE_ORDER_LINES_ALL
125 WHERE line_id = p_line_id;
126 /* Comented for bug 2193139
127 IF nvl(l_top_model_line_id,-1) = nvl(l_ato_line_id,-1) AND
128 l_top_model_line_id IS NOT NULL THEN
129
130 oe_debug_pub.add('It is a ATO MODEL ',3);
131
132 SELECT line_set_id
133 INTO l_line_set_id
134 FROM OE_ORDER_LINES_ALL
135 WHERE line_id = l_top_model_line_id;
136
137 oe_debug_pub.add('Line set id : '||l_line_set_id,3);
138
139 END IF;
140 */
141 IF l_line_set_id IS NOT NULL THEN
142 -- HW Sum qty2 for OPM
143 IF l_debug_level > 0 THEN
144 oe_debug_pub.add( 'LINE SET ID : '||L_LINE_SET_ID , 3 ) ;
145 END IF;
146 SELECT SUM(ordered_quantity)
147 , SUM(nvl(shipped_quantity,0))
148 , SUM(nvl(shipping_quantity,0))
149 , SUM(nvl(ordered_quantity2,0))
150 , SUM(nvl(shipped_quantity2,0))
151 , SUM(nvl(shipping_quantity2,0))
152 INTO l_ordered_quantity
153 , l_shipped_quantity
154 , l_shipping_quantity
155 , l_ordered_quantity2
156 , l_shipped_quantity2
157 , l_shipping_quantity2
158 FROM oe_order_lines_all
159 WHERE line_set_id = l_line_set_id;
160
161 IF l_debug_level > 0 THEN
162 oe_debug_pub.add( 'ORDER LINE SHIPPED QUANTITY '||L_SHIPPED_QUANTITY , 3 ) ;
163 oe_debug_pub.add( 'ORDER LINE SHIPPED QUANTITY2 '||L_SHIPPED_QUANTITY2 , 3 ) ; -- INVCONV
164 END IF;
165 SELECT count(*)
166 INTO l_count_unshipped
167 FROM oe_order_lines_all
168 WHERE line_set_id = l_line_set_id
169 AND shipped_quantity IS NULL
170 AND line_id <> p_line_id ;
171
172 IF l_debug_level > 0 THEN
173 oe_debug_pub.add( 'COUNT '||L_COUNT_UNSHIPPED , 3 ) ;
174 END IF;
175
176 IF nvl(l_count_unshipped,0) > 0 THEN
177
178 BEGIN
179
180 SELECT nvl(sum(shipped_quantity),0),requested_quantity_uom
181 INTO l_del_shipping_quantity,l_shipping_quantity_uom
182 FROM wsh_delivery_details
183 where source_line_id in (SELECT line_id
184 FROM oe_order_lines_all
185 WHERE line_set_id = l_line_set_id
186 AND shipped_quantity IS NULL
187 AND line_id <> p_line_id)
188 and source_code = 'OE'
189 group by requested_quantity_uom;
190
191 EXCEPTION
192
193 WHEN NO_DATA_FOUND THEN
194 l_del_shipping_quantity := 0;
195 l_shipping_quantity_uom := l_order_quantity_uom;
196
197 END;
198
199 IF l_debug_level > 0 THEN
200 oe_debug_pub.add( 'DELIVERY SHIPPING QUANTITY/UOM '||L_DEL_SHIPPING_QUANTITY||'/'||L_SHIPPING_QUANTITY_UOM , 3 ) ;
201 END IF;
202
203
204 IF l_order_quantity_uom <> l_shipping_quantity_uom AND
205 nvl(l_del_shipping_quantity,0) <> 0 THEN
206
207 /* IF oe_line_util.Process_Characteristics -- INVCONV NOT NEEDED NOW FOR OPM CONVERGENCE
208 (l_inventory_item_id
209 ,l_ship_from_org_id
210 ,l_item_rec) THEN
211
212 -- PAL Feb 2003 2683316 - changed the call to GMI uom_conversion and Get_OPMUOM_from_AppsUOM above to
213 -- get_opm_converted_qty to resolve rounding issues
214
215
216 l_OPM_shipped_quantity := GMI_Reservation_Util.get_opm_converted_qty(
217 p_apps_item_id => l_inventory_item_id,
218 p_organization_id => l_ship_from_org_id,
219 p_apps_from_uom => l_shipping_quantity_uom,
220 p_apps_to_uom => l_order_quantity_uom,
221 p_original_qty => l_del_shipping_quantity);
222
223 oe_debug_pub.add('OPM shipped quantity in proc Get_Min_Max_Tolerance_Quantity after new get_opm_converted_qty is '||l_OPM_shipped_quantity, 5);
224
225 l_del_shipped_quantity := l_OPM_shipped_quantity ;
226
227 -- HW This line is discrete
228 ELSE */
229 l_del_shipped_quantity := OE_Order_Misc_Util.Convert_Uom
230 (
231 l_inventory_item_id,
232 l_shipping_quantity_uom,
233 l_order_quantity_uom,
234 l_del_shipping_quantity
235 );
236 IF l_debug_level > 0 THEN
237 oe_debug_pub.add( 'CONVERTED SHIPPED QUANTITY : '|| L_DEL_SHIPPED_QUANTITY , 3 ) ;
238 END IF;
239
240 -- END IF; -- HW end of branching INVCONV
241
242 IF l_del_shipped_quantity <> trunc(l_del_shipped_quantity) THEN
243
244 Inv_Decimals_PUB.Validate_Quantity
245 (
246 p_item_id => l_inventory_item_id,
247 p_organization_id => OE_Sys_Parameters.value('MASTER_ORGANIZATION_ID'),
248 p_input_quantity => l_del_shipped_quantity,
249 p_uom_code => l_order_quantity_uom,
250 x_output_quantity => l_validated_quantity,
251 x_primary_quantity => l_primary_quantity,
252 x_return_status => l_qty_return_status
253 );
254
255 IF l_debug_level > 0 THEN
256 oe_debug_pub.add( 'RETURN STATUS FROM INV API : '||L_QTY_RETURN_STATUS , 1 ) ;
257 END IF;
258 IF l_qty_return_status = 'W' THEN
259
260 l_del_shipped_quantity := l_validated_quantity;
261
262 END IF;
263 END IF;
264
265 ELSE
266
267 l_del_shipped_quantity := l_del_shipping_quantity;
268
269 END IF; /* not same UOM */
270
271 l_shipped_quantity := l_shipped_quantity + l_del_shipped_quantity;
272
273 END IF;/* Unshipped lines */
274
275 END IF;/*line set */
276
277 IF l_debug_level > 0 THEN
278 oe_debug_pub.add( 'TOTAL ORDERED QUANTITY : '||TO_CHAR ( L_ORDERED_QUANTITY ) , 3 ) ;
279 END IF;
280 IF l_debug_level > 0 THEN
281 oe_debug_pub.add( 'TOTAL SHIPPED QUANTITY : '||TO_CHAR ( L_SHIPPED_QUANTITY ) , 3 ) ;
282 END IF;
283
284 l_tolerance_quantity_below := nvl(l_ordered_quantity,0)*nvl(l_ship_tolerance_below,0)/100;
285 l_tolerance_quantity_above := nvl(l_ordered_quantity,0)*nvl(l_ship_tolerance_above,0)/100;
286
287 IF l_debug_level > 0 THEN
288 oe_debug_pub.add( 'TOLERANCE QUANTITY BELOW : '||L_TOLERANCE_QUANTITY_BELOW , 3 ) ;
289 END IF;
290 IF l_debug_level > 0 THEN
291 oe_debug_pub.add( 'TOLERANCE QUANTITY ABOVE : '||L_TOLERANCE_QUANTITY_ABOVE , 3 ) ;
292 END IF;
293
294 l_min_quantity_remaining := l_ordered_quantity - nvl(l_shipped_quantity,0) - l_tolerance_quantity_below;
295 l_max_quantity_remaining := l_ordered_quantity - nvl(l_shipped_quantity,0) + l_tolerance_quantity_above;
296
297 -- HW Get min and max qty2 for OPM
298 l_min_quantity_remaining2 := nvl(l_ordered_quantity2,0) - nvl(l_shipped_quantity2,0) - l_tolerance_quantity_below;
299 l_max_quantity_remaining2 := nvl(l_ordered_quantity2,0) - nvl(l_shipped_quantity2,0) + l_tolerance_quantity_above;
300
301 IF l_debug_level > 0 THEN
302 oe_debug_pub.add( 'MIN REMAINING QUANTITY : '||L_MIN_QUANTITY_REMAINING , 3 ) ;
303 END IF;
304 IF l_debug_level > 0 THEN
305 oe_debug_pub.add( 'MAX REMAINING QUANTITY : '||L_MAX_QUANTITY_REMAINING , 3 ) ;
306 END IF;
307
308 -- HW Print Qty2 for OPM
309 IF l_debug_level > 0 THEN
310 oe_debug_pub.add( 'MIN REMAINING QUANTITY2 : '||L_MIN_QUANTITY_REMAINING2 , 3 ) ;
311 END IF;
312 IF l_debug_level > 0 THEN
313 oe_debug_pub.add( 'MAX REMAINING QUANTITY2 : '||L_MAX_QUANTITY_REMAINING2 , 3 ) ;
314 END IF;
315
316 IF l_min_quantity_remaining < 0 THEN
317
318 l_min_quantity_remaining := 0;
319
320 END IF;
321
322 IF l_min_quantity_remaining2 < 0 THEN
323 -- HW reset qty2 for OPM
324 l_min_quantity_remaining2 := 0;
325
326 END IF;
327
328 IF l_max_quantity_remaining < 0 THEN
329
330 l_max_quantity_remaining := 0;
331 END IF;
332
333 IF l_max_quantity_remaining2 < 0 THEN
334 -- HW reset qty2 for OPM
335 l_max_quantity_remaining2 := 0;
336
337 END IF;
338
339 x_min_remaining_quantity := l_min_quantity_remaining;
340 x_max_remaining_quantity := l_max_quantity_remaining;
341
342 -- HW added qty2 for OPM
343 x_min_remaining_quantity2 := nvl(l_min_quantity_remaining2,0);
344 x_max_remaining_quantity2 := nvl(l_max_quantity_remaining2,0);
345
346 IF l_debug_level > 0 THEN
347 oe_debug_pub.add( 'RETURN MIN REMAINING QUANTITY : '||X_MIN_REMAINING_QUANTITY , 3 ) ;
348 END IF;
349 IF l_debug_level > 0 THEN
350 oe_debug_pub.add( 'RETURN MAX REMAINING QUANTITY : '||X_MAX_REMAINING_QUANTITY , 3 ) ;
351 END IF;
352
353 -- HW print qty2 for OPM
354 IF l_debug_level > 0 THEN
355 oe_debug_pub.add( 'RETURN MIN REMAINING QUANTITY2 : '||X_MIN_REMAINING_QUANTITY2 , 3 ) ;
356 END IF;
357 IF l_debug_level > 0 THEN
358 oe_debug_pub.add( 'RETURN MAX REMAINING QUANTITY2 : '||X_MAX_REMAINING_QUANTITY2 , 3 ) ;
359 END IF;
360
361 IF l_debug_level > 0 THEN
362 oe_debug_pub.add( 'EXITING OE_SHIPPING_TOLERANCES_PUB.GET_MIN_MAX_TOLERANCE_QUANTITY '||X_RETURN_STATUS , 1 ) ;
363 END IF;
364
365 EXCEPTION
366
367 WHEN NO_DATA_FOUND THEN
368
369 x_min_remaining_quantity := 0;
370 x_max_remaining_quantity := 0;
371
372 -- HW reset values for qty2 for OPM
373 x_min_remaining_quantity2 := 0;
374 x_max_remaining_quantity2 := 0;
375
376 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
377 THEN
378 x_return_status := FND_API.G_RET_STS_ERROR;
379 END IF;
380
381 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
382
383 IF l_debug_level > 0 THEN
384 oe_debug_pub.add( 'UNEXPECTED ERROR : '||SQLERRM , 1 ) ;
385 END IF;
386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
387
388 -- Get message count and data
389
390 FND_MSG_PUB.Count_And_Get
391 ( p_count => x_msg_count
392 , p_data => x_msg_data
393 );
394
395 WHEN OTHERS THEN
396
397 IF l_debug_level > 0 THEN
398 oe_debug_pub.add( 'UNEXPECTED ERROR : '||SQLERRM , 1 ) ;
399 END IF;
400 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
401
402 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
403 THEN
404 FND_MSG_PUB.Add_Exc_Msg
405 ( G_PKG_NAME
406 , 'Get_Min_Max_Tolerance_Quantity'
407 );
408 END IF;
409
410 -- Get message count and data
411
412 FND_MSG_PUB.Count_And_Get
413 ( p_count => x_msg_count
414 , p_data => x_msg_data
415 );
416
417 END Get_Min_Max_Tolerance_Quantity;
418
419 PROCEDURE Get_Min_Max_quantity_Uom
420 (
421 p_api_version_number IN NUMBER
422 , p_line_id IN NUMBER
423 , x_min_remaining_quantity OUT NOCOPY /* file.sql.39 change */ NUMBER
424 , x_max_remaining_quantity OUT NOCOPY /* file.sql.39 change */ NUMBER
425 , x_quantity_uom OUT NOCOPY /* file.sql.39 change */ VARCHAR2
426 , x_min_remaining_quantity2 OUT NOCOPY /* file.sql.39 change */ NUMBER
427 , x_max_remaining_quantity2 OUT NOCOPY /* file.sql.39 change */ NUMBER
428 , x_quantity_uom2 OUT NOCOPY /* file.sql.39 change */ VARCHAR2
429 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
430 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
431 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
432 )
433 IS
434
435 --
436 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
437 --
438 BEGIN
439
440 IF l_debug_level > 0 THEN
441 oe_debug_pub.add( 'ENTERING OE_SHIPPING_INTEGRATION_PUB.GET_MIN_MAX_QUANTITY_UOM' , 1 ) ;
442 END IF;
443
444 x_return_status := FND_API.G_RET_STS_SUCCESS;
445
446 OE_Shipping_Tolerances_PUB.Get_Min_Max_Tolerance_Quantity
447 (
448 p_api_version_number => p_api_version_number,
449 p_line_id => p_line_id,
450 x_min_remaining_quantity => x_min_remaining_quantity,
451 x_max_remaining_quantity => x_max_remaining_quantity,
452 x_min_remaining_quantity2 => x_min_remaining_quantity2,
453 x_max_remaining_quantity2 => x_max_remaining_quantity2,
454 x_return_status => x_return_status,
455 x_msg_count => x_msg_count,
456 x_msg_data => x_msg_data
457 );
458
459 SELECT order_quantity_uom,
460 ordered_quantity_uom2
461 INTO x_quantity_uom,
462 x_quantity_uom2
463 FROM oe_order_lines_all
464 WHERE line_id = p_line_id;
465
466 IF l_debug_level > 0 THEN
467 oe_debug_pub.add( 'RETURN MIN REMAINING QUANTITY : '||X_MIN_REMAINING_QUANTITY , 3 ) ;
468 END IF;
469 IF l_debug_level > 0 THEN
470 oe_debug_pub.add( 'RETURN MAX REMAINING QUANTITY : '||X_MAX_REMAINING_QUANTITY , 3 ) ;
471 END IF;
472 IF l_debug_level > 0 THEN
473 oe_debug_pub.add( 'RETURN MIN REMAINING QUANTITY2 : '||X_MIN_REMAINING_QUANTITY2 , 3 ) ;
474 END IF;
475 IF l_debug_level > 0 THEN
476 oe_debug_pub.add( 'RETURN MAX REMAINING QUANTITY2 : '||X_MAX_REMAINING_QUANTITY2 , 3 ) ;
477 END IF;
478 IF l_debug_level > 0 THEN
479 oe_debug_pub.add( 'RETURN QUANTITY UOM : '||X_QUANTITY_UOM , 3 ) ;
480 END IF;
481 IF l_debug_level > 0 THEN
482 oe_debug_pub.add( 'RETURN QUANTITY UOM2 : '||X_QUANTITY_UOM2 , 3 ) ;
483 END IF;
484
485 IF l_debug_level > 0 THEN
486 oe_debug_pub.add( 'EXITING OE_SHIPPING_INTEGRATION_PUB.GET_MIN_MAX_QUANTITY_UOM '||X_RETURN_STATUS , 1 ) ;
487 END IF;
488
489 EXCEPTION
490
491 WHEN NO_DATA_FOUND THEN
492
493 x_min_remaining_quantity := 0;
494 x_max_remaining_quantity := 0;
495
496 x_min_remaining_quantity2 := 0;
497 x_max_remaining_quantity2 := 0;
498
499 x_quantity_uom := '';
500 x_quantity_uom2 := '';
501
502 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
503 THEN
504 x_return_status := FND_API.G_RET_STS_ERROR;
505 END IF;
506
507 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
508
509 IF l_debug_level > 0 THEN
510 oe_debug_pub.add( 'UNEXPECTED ERROR : '||SQLERRM , 1 ) ;
511 END IF;
512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
513
514 -- Get message count and data
515
516 FND_MSG_PUB.Count_And_Get
517 ( p_count => x_msg_count
518 , p_data => x_msg_data
519 );
520
521 WHEN OTHERS THEN
522
523 IF l_debug_level > 0 THEN
524 oe_debug_pub.add( 'UNEXPECTED ERROR : '||SQLERRM , 1 ) ;
525 END IF;
526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
527
528 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
529 THEN
530 FND_MSG_PUB.Add_Exc_Msg
531 ( G_PKG_NAME
532 , 'Get_Min_Max_Quantity_Uom'
533 );
534 END IF;
535
536 -- Get message count and data
537
538 FND_MSG_PUB.Count_And_Get
539 ( p_count => x_msg_count
540 , p_data => x_msg_data
541 );
542
543
544 END Get_Min_Max_Quantity_Uom;
545
546 END OE_Shipping_Tolerances_PUB;
547