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