[Home] [Help]
PACKAGE BODY: APPS.GML_MOBILE_SHIP_CONFIRM
Source
1 PACKAGE BODY GML_MOBILE_SHIP_CONFIRM AS
2 /* $Header: GMLMOSCB.pls 120.0 2005/05/25 16:50:52 appldev noship $ */
3
4
5 g_gtin_cross_ref_type VARCHAR2(25) := fnd_profile.value('INV:GTIN_CROSS_REFERENCE_TYPE');
6 g_gtin_code_length NUMBER := 14;
7
8
9 PROCEDURE CHECK_SHIP_SET(
10 p_delivery_id IN NUMBER,
11 x_ship_set OUT NOCOPY VARCHAR2,
12 x_return_Status OUT NOCOPY VARCHAR2,
13 x_error_msg OUT NOCOPY VARCHAR2)
14 IS
15 l_ship_set VARCHAR2(2000) := NULL;
16 l_ship_set_id NUMBER;
17 l_ship_set_name VARCHAR2(30);
18 unshipped_count NUMBER;
19
20 CURSOR specified_ship_set IS
21 SELECT wdd.ship_set_id
22 FROM wsh_delivery_details wdd,
23 wsh_delivery_assignments wda
24 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
25 AND EXISTS (SELECT 'x'
26 FROM wsh_delivery_details wdd2
27 WHERE wdd2.delivery_detail_id = wdd.delivery_detail_id
28 AND wdd2.ship_set_id is not null
29 AND wdd2.shipped_quantity is not null)
30 AND wda.delivery_id = p_delivery_id;
31
32 BEGIN
33 x_return_status := 'C';
34 OPEN specified_ship_set;
35 loop
36 FETCH specified_ship_set INTO l_ship_set_id;
37 EXIT WHEN specified_ship_set%NOTFOUND;
38 SELECT count(*)
39 INTO unshipped_count
40 FROM wsh_delivery_details wdd,
41 wsh_delivery_assignments wda,
42 wsh_new_deliveries wnd
43 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
44 AND wda.delivery_id = wnd.delivery_id
45 AND wnd.delivery_id = p_delivery_id
46 AND wdd.ship_set_id = l_ship_set_id
47 AND wdd.shipped_quantity is null;
48 if (unshipped_count >0 ) then
49 select set_name
50 into l_ship_set_name
51 from oe_sets
52 where set_id = l_ship_set_id;
53 if (l_ship_set is null) then
54 l_ship_set := l_ship_set_name;
55 else l_ship_set := l_ship_set ||', '||l_ship_set_name;
56 end if;
57 end if;
58 end loop;
59 close specified_ship_set;
60 if l_ship_set is null then
61 x_return_status := 'C';
62 else
63 x_return_status := 'E';
64 x_ship_set := l_ship_set;
65 end if;
66 EXCEPTION
67 WHEN OTHERS THEN
68 x_return_status := 'U';
69 END CHECK_SHIP_SET;
70
71 PROCEDURE CHECK_COMPLETE_DELVIERY(
72 p_delivery_id IN NUMBER,
73 x_return_Status OUT NOCOPY VARCHAR2,
74 x_error_msg OUT NOCOPY VARCHAR2) IS
75 exist_unspecified NUMBER;
76 BEGIN
77 x_return_Status := 'C';
78 select 1
79 into exist_unspecified
80 from dual
81 where exists (select 1
82 from wsh_delivery_details wdd,
83 wsh_delivery_assignments wda
84 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
85 AND wdd.shipped_quantity is null
86 AND wda.delivery_id = p_delivery_id
87 );
88 if exist_unspecified = 1 then x_return_Status := 'E'; end if;
89 EXCEPTION
90 WHEN NO_DATA_FOUND THEN
91 x_return_Status := 'C';
92 WHEN OTHERS THEN
93 x_return_Status := 'U';
94 END CHECK_COMPLETE_DELVIERY;
95
96 PROCEDURE INV_DELIVERY_LINE_INFO(x_deliveryLineInfo OUT NOCOPY t_genref,
97 p_delivery_id IN NUMBER,
98 p_inventory_item_id IN NUMBER,
99 x_return_Status OUT NOCOPY VARCHAR2) IS
100 BEGIN
101 x_return_Status := 'C';
102
103 OPEN x_deliveryLineInfo FOR
104 SELECT ' ',del.name delivery_name, dd.delivery_detail_id,
105 dd.inventory_item_id,msik.concatenated_segments, msik.description,
106 dd.requested_quantity, dd.requested_quantity_uom,
107 dd.serial_number, del.waybill, Nvl(msik.serial_number_control_code, 1),
108 dd.subinventory, Nvl(dd.locator_id,0),dd.tracking_number,
109 nvl(dd.transaction_temp_id,0),
110 dd.picked_quantity, dd.requested_quantity_uom2, NVL(dd.lot_number, ' '), NVL(dd.picked_quantity2,0)
111 ---FROM wsh_new_deliveries_ob_grp_v del, wsh_delivery_details_ob_grp_v dd,
112 FROM wsh_new_deliveries del, wsh_delivery_details dd,
113 wsh_delivery_assignments da, mtl_system_items_kfv msik
114 WHERE da.delivery_id = del.delivery_id
115 AND da.delivery_detail_id = dd.delivery_detail_id
116 AND ( dd.inventory_item_id = p_inventory_item_id or p_inventory_item_id = -1 )
117 AND NVL( dd.inv_interfaced_flag, 'N') = 'N'
118 AND dd.released_status = 'Y'
119 AND del.delivery_id = p_delivery_id
120 AND msik.inventory_item_id(+) = dd.inventory_item_id
121 AND msik.organization_id(+) = dd.organization_id
122 ORDER BY dd.subinventory,dd.locator_id, msik.concatenated_segments;
123
124 EXCEPTION
125 when others then
126 x_return_Status := 'E';
127
128 END INV_DELIVERY_LINE_INFO;
129
130
131 PROCEDURE INV_LINE_RETURN_TO_STOCK(p_delivery_id IN NUMBER,
132 p_delivery_line_id IN NUMBER,
133 p_shipped_quantity IN NUMBER,
134 p_shipped_quantity2 IN NUMBER,
135 x_return_status OUT NOCOPY VARCHAR2,
136 x_msg_data OUT NOCOPY VARCHAR2,
137 x_msg_count OUT NOCOPY NUMBER,
138 p_commit_flag IN VARCHAR2 DEFAULT FND_API.g_false,
139 p_relieve_rsv IN VARCHAR2 DEFAULT 'Y')
140 IS
141 cursor delivery_line(p_delivery_detail_id NUMBER) is
142 select dd.delivery_detail_id,
143 dd.requested_quantity,
144 dd.picked_quantity,
145 NVL(dd.requested_quantity2,0),
146 NVL(dd.picked_quantity2, 0)
147 ---from wsh_delivery_details_ob_grp_v dd
148 from wsh_delivery_details dd
149 WHERE dd.delivery_detail_id = p_delivery_detail_id;
150
151 cursor lpn_csr(p_delivery_detail_id in NUMBER) is
152 select wdd.delivery_detail_id, wda.delivery_assignment_id,wda2.delivery_assignment_id
153 ---from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments wda, wsh_delivery_details_ob_grp_v wdd2
154 from wsh_delivery_details wdd, wsh_delivery_assignments wda, wsh_delivery_details wdd2
155 , wsh_delivery_assignments wda2
156 where wdd.delivery_detail_id = wda.parent_delivery_detail_id
157 and wda.delivery_detail_id = wdd2.delivery_detail_id
158 and wdd2.delivery_detail_id = p_delivery_detail_id
159 and wda2.delivery_detail_id = wdd.delivery_detail_id;
160
161 CURSOR nested_parent_lpn_cursor(l_inner_lpn_id NUMBER) is
162 SELECT lpn_id
163 FROM WMS_LICENSE_PLATE_NUMBERS
164 START WITH lpn_id = l_inner_lpn_id
165 CONNECT BY lpn_id = PRIOR parent_lpn_id;
166
167 l_delivery_details_id_table WSH_UTIL_CORE.ID_TAB_TYPE;
168 l_backorder_quantities_table WSH_UTIL_CORE.ID_TAB_TYPE;
169 l_backorder_quantities2_table WSH_UTIL_CORE.ID_TAB_TYPE;
170 l_requested_quantities_table WSH_UTIL_CORE.ID_TAB_TYPE;
171 l_requested_quantities2_table WSH_UTIL_CORE.ID_TAB_TYPE;
172 l_overpicked_quantities_table WSH_UTIL_CORE.ID_TAB_TYPE;
173 l_overpicked_quantities2_table WSH_UTIL_CORE.ID_TAB_TYPE;
174 l_dummy_table wsh_util_core.id_tab_type;
175 l_out_rows wsh_util_core.id_tab_type;
176 l_detail_attributes wsh_delivery_details_pub.ChangedAttributeTabType;
177 l_dummy_num_var NUMBER := NULL;
178 l_table_index NUMBER := 1;
179
180 l_picked_quantity NUMBER;
181 l_picked_quantity2 NUMBER;
182 l_parent_delivery_detail_id NUMBER;
183 l_bo_delivery_detail_id NUMBER;
184 l_delivery_assignment_id NUMBER;
185 l_par_delivery_assignment_id NUMBER;
186 l_lpn_id NUMBER;
187
188 l_more_detail NUMBER;
189
190 l_return_status VARCHAR2(1);
191 l_msg_count NUMBER;
192 l_msg_data VARCHAR2(2000);
193
194 BEGIN
195 x_return_status := fnd_api.g_ret_sts_success;
196
197 --this cursor only returns 1 record because delivery_line_id is an
198 --unique key
199 OPEN delivery_line(p_delivery_line_id);
200
201 FETCH delivery_line INTO
202 l_delivery_details_id_table(1),
203 l_requested_quantities_table(1),
204 l_picked_quantity,
205 l_requested_quantities2_table(1),
206 l_picked_quantity2;
207
208
209 IF l_picked_quantity > l_requested_quantities_table(1) THEN
210 l_backorder_quantities_table(1) :=
211 l_picked_quantity - p_shipped_quantity;
212
213 l_overpicked_quantities_table(1) :=
214 l_picked_quantity - l_requested_quantities_table(1);
215 ELSE
216 l_backorder_quantities_table(1) :=
217 l_requested_quantities_table(1) -
218 p_shipped_quantity;
219
220 l_overpicked_quantities_table(1) := 0;
221 END IF;
222
223 IF l_picked_quantity2 > l_requested_quantities2_table(1) THEN
224 l_backorder_quantities2_table(1) :=
225 l_picked_quantity2 - p_shipped_quantity2;
226
227 l_overpicked_quantities2_table(1) :=
228 l_picked_quantity2 - l_requested_quantities2_table(1);
229 ELSE
230 l_backorder_quantities2_table(1) :=
231 l_requested_quantities2_table(1) -
232 p_shipped_quantity2;
233
234 l_overpicked_quantities2_table(1) := 0;
235 END IF;
236
237 l_dummy_table(1) := NULL;
238
239 CLOSE delivery_line;
240
241 IF p_shipped_quantity = 0 THEN
242
243 OPEN lpn_csr(l_delivery_details_id_table(1));
244 LOOP
245 FETCH lpn_csr INTO
246 l_parent_delivery_detail_id, l_delivery_assignment_id,
247 l_par_delivery_assignment_id;
248
249 EXIT WHEN lpn_csr%NOTFOUND;
250
251 SELECT lpn_id
252 INTO l_lpn_id
253 ---FROM wsh_delivery_details_ob_grp_v wdd
254 FROM wsh_delivery_details wdd
255 WHERE delivery_detail_id = l_parent_delivery_detail_id;
256
257 --update LPN(s) context to Resides in Inventory
258 FOR l_par_lpn_id IN nested_parent_lpn_cursor(l_lpn_id) LOOP
259 UPDATE wms_license_plate_numbers
260 SET lpn_context = 1,
261 last_update_date = SYSDATE,
262 last_updated_by = fnd_global.user_id
263 WHERE lpn_id = l_par_lpn_id.lpn_id;
264 END LOOP;
265
266 --**Check whether Shipping's backorder API does
267 --1. Unassign the delivery line from container
268 --2. if container becomes empty, unassign the container from
269 -- delivery
270 END LOOP;
271
272 CLOSE lpn_csr;
273
274 ELSE --corresponding if: p_shipped_quantity = 0
275
276 WSH_DELIVERY_DETAILS_PUB.split_line
277 (p_api_version => 1.0,
278 p_init_msg_list => fnd_api.g_false,
279 p_commit => p_commit_flag,
280 x_return_status => l_return_status,
281 x_msg_count => l_msg_count,
282 x_msg_data => l_msg_data,
283 p_from_detail_id => l_delivery_details_id_table(1),
284 x_new_detail_id => l_bo_delivery_detail_id,
285 x_split_quantity => l_backorder_quantities_table(1),
286 x_split_quantity2 => l_backorder_quantities2_table(1));
287
288 IF l_return_status <> fnd_api.g_ret_sts_success THEN
289 RAISE fnd_api.g_exc_unexpected_error;
290 END IF;
291
292 l_detail_attributes(1).delivery_detail_id :=
293 l_delivery_details_id_table(1);
294 l_detail_attributes(1).shipped_quantity := p_shipped_quantity;
295 l_detail_attributes(1).shipped_quantity2 := p_shipped_quantity2;
296
297 wsh_delivery_details_pub.update_shipping_attributes
298 (p_api_version_number => 1.0,
299 p_init_msg_list => fnd_api.g_false,
300 p_commit => p_commit_flag,
301 x_return_status => l_return_status,
302 x_msg_count => l_msg_count,
303 x_msg_data => l_msg_data,
304 p_changed_attributes => l_detail_attributes,
305 p_source_code => 'OE');
306
307 IF l_return_status <> fnd_api.g_ret_sts_success THEN
308 RAISE fnd_api.g_exc_unexpected_error;
309 END IF;
310
311 l_delivery_details_id_table(1) := l_bo_delivery_detail_id;
312 END IF;
313
314 --bug3564157: Shipping's API require the dummy_table to be initialized
315 l_dummy_table(1) := 0;
316 wsh_ship_confirm_actions2.backorder
317 (p_detail_ids => l_delivery_details_id_table,
318 p_bo_qtys => l_backorder_quantities_table,
319 p_req_qtys => l_backorder_quantities_table,
320 p_bo_qtys2 => l_backorder_quantities2_table,
321 p_overpick_qtys => l_overpicked_quantities_table,
322 p_overpick_qtys2 => l_overpicked_quantities2_table,
323 p_bo_mode => 'UNRESERVE',
324 x_out_rows => l_out_rows,
325 x_return_status => l_return_status);
326
327
328 IF l_return_status <> fnd_api.g_ret_sts_success THEN
329 RAISE fnd_api.g_exc_unexpected_error;
330 END IF;
331
332 IF p_commit_flag = fnd_api.g_true THEN
333 commit;
334 END IF;
335 EXCEPTION
336 WHEN FND_API.G_EXC_ERROR THEN
337 x_return_status := FND_API.G_RET_STS_ERROR;
338 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
339 , p_data => x_msg_data);
340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
342 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
343 , p_data => x_msg_data);
344 WHEN OTHERS THEN
345 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
346 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
347 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'INV_LINE_RETURN_TO_STOCK');
348 END IF;
349 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
350 , p_data => x_msg_data);
351
352 END INV_LINE_RETURN_TO_STOCK;
353
354 PROCEDURE INV_REPORT_MISSING_QTY(
355 p_delivery_line_id IN NUMBER,
356 p_missing_quantity IN NUMBER,
357 p_missing_quantity2 IN NUMBER,
358 x_return_status OUT NOCOPY VARCHAR2,
359 x_msg_data OUT NOCOPY VARCHAR2,
360 x_msg_count OUT NOCOPY NUMBER) IS
361 l_detail_attributes WSH_DELIVERY_DETAILS_PUB.ChangedAttributeTabType;
362 l_details VARCHAR2(2000);
363 BEGIN
364 l_detail_attributes(1).cycle_count_quantity := p_missing_quantity;
365 l_detail_attributes(1).cycle_count_quantity2 := p_missing_quantity2;
366 l_detail_attributes(1).delivery_detail_id := p_delivery_line_id;
367
368 wsh_delivery_details_pub.update_shipping_attributes
369 (p_api_version_number => 1.0,
370 p_init_msg_list => FND_API.G_TRUE,
371 p_commit => FND_API.G_FALSE,
372 x_return_status => x_return_status,
373 x_msg_count => x_msg_count,
374 x_msg_data => x_msg_data,
375 p_changed_attributes => l_detail_attributes,
376 p_source_code => 'OE');
377
378 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
379 RAISE fnd_api.g_exc_unexpected_error;
380 END IF;
381
382 EXCEPTION
383 WHEN fnd_api.g_exc_unexpected_error THEN
384 wsh_util_core.get_messages
385 (p_init_msg_list => 'Y',
386 x_summary => x_msg_data,
387 x_details => l_details,
388 x_count => x_msg_count);
389 when no_data_found then
390 -- do nothing for now
391 null;
392
393 END INV_REPORT_MISSING_QTY;
394
395 PROCEDURE SUBMIT_DELIVERY_LINE(p_delivery_line_id IN NUMBER,
396 p_quantity IN NUMBER,
397 p_quantity2 IN NUMBER,
398 p_trackingNumber IN VARCHAR2,
399 x_return_status OUT NOCOPY VARCHAR2,
400 x_msg_data OUT NOCOPY VARCHAR2,
401 x_msg_count OUT NOCOPY NUMBER ) IS
402 l_detail_attributes WSH_DELIVERY_DETAILS_PUB.ChangedAttributeTabType;
403
404
405 l_details VARCHAR2(2000);
406
407 /*
408 CURSOR c_weight_vol_info IS
409 SELECT unit_weight,
410 unit_volume
411 --- nvl(wv_frozen_flag ,'N') wv_frozen_flag
412 FROM WSH_DELIVERY_DETAILS_OB_GRP_V
413 WHERE delivery_detail_id = p_delivery_line_id;
414
415 l_weight_vol_info c_weight_vol_info%ROWTYPE;
416
417 l_gross_weight NUMBER;
418
419 l_net_weight NUMBER;
420
421 l_total_volume NUMBER;
422 */
423
424 BEGIN
425
426 IF p_quantity IS NOT NULL then
427 l_detail_attributes(1).shipped_quantity := p_quantity;
428
429 IF p_quantity2 IS NOT NULL then
430 l_detail_attributes(1).shipped_quantity2 := p_quantity2;
431 END IF;
432
433 /** Eddie : Do we need the following ?? wv_frozen_flag is not in WSH_DELIVERY_DETAILS_OB_GRP_V
434 OPEN c_weight_vol_info;
435
436 FETCH c_weight_vol_info INTO l_weight_vol_info;
437
438 CLOSE c_weight_vol_info;
439
440 IF (l_weight_vol_info.wv_frozen_flag= 'N' AND
441 (l_weight_vol_info.unit_weight IS NOT NULL OR l_weight_vol_info.unit_volume IS NOT NULL)) THEN
442
443 IF l_weight_vol_info.unit_weight IS NOT NULL THEN
444 l_detail_attributes(1).gross_weight := p_quantity*l_weight_vol_info.unit_weight;
445 l_detail_attributes(1).net_weight := p_quantity*l_weight_vol_info.unit_weight;
446 END IF;
447
448 IF l_weight_vol_info.unit_volume IS NOT NULL THEN
449 l_detail_attributes(1).volume := p_quantity*l_weight_vol_info.unit_volume;
450 END IF;
451
452 END IF;
453 */
454
455 END IF;
456
457 IF p_trackingNumber IS NOT NULL THEN
458 l_detail_attributes(1).tracking_number := p_trackingNumber;
459 END IF;
460
461 l_detail_attributes(1).delivery_detail_id := p_delivery_line_id;
462
463 wsh_delivery_details_pub.update_shipping_attributes
464 (p_api_version_number => 1.0,
465 p_init_msg_list => FND_API.G_TRUE,
466 p_commit => FND_API.G_FALSE,
467 x_return_status => x_return_status,
468 x_msg_count => x_msg_count,
469 x_msg_data => x_msg_data,
470 p_changed_attributes => l_detail_attributes,
471 p_source_code => 'OE');
472
473 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
474 RAISE fnd_api.g_exc_unexpected_error;
475 END IF;
476
477 EXCEPTION
478 WHEN fnd_api.g_exc_unexpected_error THEN
479 wsh_util_core.get_messages
480 (p_init_msg_list => 'Y',
481 x_summary => x_msg_data,
482 x_details => l_details,
483 x_count => x_msg_count);
484
485 when no_data_found then
486 -- do nothing for now
487 null;
488
489 END SUBMIT_DELIVERY_LINE;
490
491 FUNCTION GET_SHIPMETHOD_MEANING(p_ship_method_code IN VARCHAR2)
492 RETURN VARCHAR2 IS
493 l_ship_method_meaning VARCHAR2(80);
494 BEGIN
495 if p_ship_method_code is null then
496 return '';
497 else
498 select meaning
499 into l_ship_method_meaning
500 from fnd_lookup_values_vl
501 where lookup_type = 'SHIP_METHOD'
502 and view_application_id = 3
503 and lookup_code = p_ship_method_code;
504 end if;
505 return l_ship_method_meaning;
506 EXCEPTION
507 WHEN OTHERS THEN
508 return '';
509 END GET_SHIPMETHOD_MEANING;
510
511 PROCEDURE GET_DELIVERY_INFO(x_delivery_info OUT NOCOPY t_genref,
512 p_delivery_id IN NUMBER) IS
513
514 BEGIN
515 open x_delivery_info for
516 SELECT wnd.name, wnd.delivery_id, nvl(wnd.gross_weight, 0), wnd.weight_uom_code,
517 wnd.waybill,' ',
518 GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
519 FROM wsh_new_deliveries wnd
520 WHERE wnd.delivery_id = p_delivery_id;
521 END GET_DELIVERY_INFO;
522
523
524 PROCEDURE CONFIRM_DELIVERY (
525 p_ship_delivery IN VARCHAR2 DEFAULT NULL,
526 p_delivery_id IN NUMBER,
527 p_organization_id IN NUMBER,
528 p_delivery_name IN VARCHAR2,
529 p_carrier_id IN NUMBER,
530 p_ship_method_code IN VARCHAR2,
531 p_gross_weight IN NUMBER,
532 p_gross_weight_uom IN VARCHAR2,
533 p_bol IN VARCHAR2,
534 p_waybill IN VARCHAR2,
535 p_action_flag IN VARCHAR2,
536 x_return_status OUT NOCOPY VARCHAR2,
537 x_ret_code OUT NOCOPY NUMBER,
538 x_msg_data OUT NOCOPY VARCHAR2,
539 x_msg_count OUT NOCOPY NUMBER) IS
540
541 l_ship_set VARCHAR2(2000) := NULL;
542 l_error_msg VARCHAR2(2000) := NULL;
543
544 unspec_ship_set_exists EXCEPTION;
545 incomplete_delivery EXCEPTION;
546
547 BEGIN
548 x_return_status := FND_API.G_RET_STS_SUCCESS;
549 x_ret_code := 0;
550 fnd_msg_pub.initialize;
551
552 IF p_ship_delivery = 'YES' THEN
553 CHECK_SHIP_SET(
554 p_delivery_id => p_delivery_id,
555 x_ship_set => l_ship_set,
556 x_return_Status => x_return_status,
557 x_error_msg => l_error_msg);
558 IF x_return_status = 'E' THEN
559 FND_MESSAGE.SET_NAME('INV', 'WMS_WSH_SHIPSET_FORCED');
560 FND_MESSAGE.SET_TOKEN('SHIP_SET_NAME', l_ship_set);
561 FND_MSG_PUB.ADD;
562 RAISE unspec_ship_set_exists;
563 ELSIF x_return_status = 'U' THEN
564 RAISE fnd_api.g_exc_unexpected_error;
565 END IF;
566
567 CHECK_COMPLETE_DELVIERY(
568 p_delivery_id => p_delivery_id,
569 x_return_Status => x_return_status,
570 x_error_msg => l_error_msg);
571 IF x_return_status = 'E' THEN
572 FND_MESSAGE.SET_NAME('INV', 'WMS_INCOMPLETE_DELI');
573 FND_MSG_PUB.ADD;
574 RAISE incomplete_delivery;
575 ELSIF x_return_status = 'U' THEN
576 RAISE fnd_api.g_exc_unexpected_error;
577 END IF;
578
579 WMS_SHIPPING_TRANSACTION_PUB.SHIP_CONFIRM_ALL(
580 p_delivery_id => p_delivery_id,
581 p_organization_id => p_organization_id,
582 p_delivery_name => p_delivery_name,
583 p_carrier_id => p_carrier_id,
584 p_ship_method_code => p_ship_method_code,
585 p_gross_weight => p_gross_weight,
586 p_gross_weight_uom => p_gross_weight_uom,
587 p_bol => p_bol,
588 p_waybill => p_waybill,
589 p_action_flag => p_action_flag,
590 x_return_status => x_return_status,
591 x_msg_data => x_msg_data,
592 x_msg_count => x_msg_count);
593
594 ELSE
595 WMS_SHIPPING_TRANSACTION_PUB.SHIP_CONFIRM(
596 p_delivery_id => p_delivery_id,
597 p_organization_id => p_organization_id,
598 p_delivery_name => p_delivery_name,
599 p_carrier_id => p_carrier_id,
600 p_ship_method_code => p_ship_method_code,
601 p_gross_weight => p_gross_weight,
602 p_gross_weight_uom => p_gross_weight_uom,
603 p_bol => p_bol,
604 p_waybill => p_waybill,
605 p_action_flag => p_action_flag,
606 x_return_status => x_return_status,
607 x_msg_data => x_msg_data,
608 x_msg_count => x_msg_count);
609 END IF;
610
611 IF x_return_status not in ('S','W') THEN
612 RAISE fnd_api.g_exc_unexpected_error;
613 END IF;
614
615 EXCEPTION
616 WHEN unspec_ship_set_exists THEN
617 x_return_status := fnd_api.g_ret_sts_error;
618 x_ret_code := 1;
619
620 -- Get message count and data
621 fnd_msg_pub.count_and_get
622 ( p_count => x_msg_count
623 , p_data => x_msg_data
624 );
625
626 WHEN incomplete_delivery THEN
627 x_return_status := fnd_api.g_ret_sts_error;
628 x_ret_code := 2;
629
630 -- Get message count and data
631 fnd_msg_pub.count_and_get
632 ( p_count => x_msg_count
633 , p_data => x_msg_data
634 );
635
636 WHEN OTHERS THEN
637 x_return_status := fnd_api.g_ret_sts_unexp_error;
638
639 END CONFIRM_DELIVERY;
640
641
642 PROCEDURE UNASSIGN_LINES_AND_CONFIRM (
643 p_delivery_id IN NUMBER,
644 p_organization_id IN NUMBER,
645 p_delivery_name IN VARCHAR2,
646 p_carrier_id IN NUMBER,
647 p_ship_method_code IN VARCHAR2,
648 p_gross_weight IN NUMBER,
649 p_gross_weight_uom IN VARCHAR2,
650 p_bol IN VARCHAR2,
651 p_waybill IN VARCHAR2,
652 p_action_flag IN VARCHAR2,
653 x_return_status OUT NOCOPY VARCHAR2,
654 x_msg_data OUT NOCOPY VARCHAR2,
655 x_msg_count OUT NOCOPY NUMBER) IS
656 l_error_msg VARCHAR2(2000) := NULL;
657 unassign_lines_exc EXCEPTION;
658 BEGIN
659 fnd_msg_pub.initialize;
660
661 INV_SHIPPING_TRANSACTION_PUB.UNASSIGN_DELIVERY_LINES(
662 p_delivery_id => p_delivery_id,
663 x_return_Status => x_return_status,
664 x_error_msg => l_error_msg);
665 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
666 RAISE unassign_lines_exc;
667 END IF;
668
669 WMS_SHIPPING_TRANSACTION_PUB.SHIP_CONFIRM_ALL(
670 p_delivery_id => p_delivery_id,
671 p_organization_id => p_organization_id,
672 p_delivery_name => p_delivery_name,
673 p_carrier_id => p_carrier_id,
674 p_ship_method_code => p_ship_method_code,
675 p_gross_weight => p_gross_weight,
676 p_gross_weight_uom => p_gross_weight_uom,
677 p_bol => p_bol,
678 p_waybill => p_waybill,
679 p_action_flag => p_action_flag,
680 x_return_status => x_return_status,
681 x_msg_data => x_msg_data,
682 x_msg_count => x_msg_count);
683
684 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
685 RAISE fnd_api.g_exc_unexpected_error;
686 END IF;
687
688 EXCEPTION
689 WHEN unassign_lines_exc THEN
690 x_return_status := fnd_api.g_ret_sts_error;
691
692 WHEN OTHERS THEN
693 x_return_status := fnd_api.g_ret_sts_unexp_error;
694
695 -- Get message count and data
696 fnd_msg_pub.count_and_get
697 ( p_count => x_msg_count
698 , p_data => x_msg_data
699 );
700
701 END UNASSIGN_LINES_AND_CONFIRM;
702
703
704 PROCEDURE Get_Ship_Conf_Delivery_Lov(x_deliveryLOV OUT NOCOPY t_genref,
705 p_delivery_name IN VARCHAR2,
706 p_organization_id IN NUMBER) IS
707 BEGIN
708 OPEN x_deliveryLOV for
709 SELECT distinct wnd.name, wnd.delivery_id, wnd.gross_weight, wnd.weight_uom_code,
710
711 wnd.waybill,
712 Get_Shipmethod_Meaning(wnd.ship_method_code)
713 FROM wsh_new_deliveries wnd, wsh_delivery_assignments wda,wsh_delivery_details wdd
714
715 WHERE wda.delivery_Detail_id = wdd.delivery_Detail_id
716 AND wda.delivery_id = wnd.delivery_id
717 and ( wdd.released_status = 'Y' or wdd.released_status = 'X')
718 /*
719 ( wdd.released_status = 'X' and
720 exists (select 1
721 from mtl_system_items_b msi
722 where msi.organization_id = wdd.organization_id
723 and msi.inventory_item_id = wdd.inventory_item_id
724 and msi.mtl_transactions_enabled_flag = 'N')) --
725
726
727 )
728 */
729 and wdd.organization_id = p_organization_id
730 and wnd.name like (p_delivery_name)
731 AND status_code not in ('CO', 'CL', 'IT');
732 END Get_Ship_Conf_Delivery_Lov;
733
734
735 PROCEDURE Get_Ship_Items_Lov(x_items OUT NOCOPY t_genref,
736 p_organization_id IN NUMBER,
737 p_delivery_id IN NUMBER,
738 p_concatenated_segments IN VARCHAR2) IS
739 l_cross_ref varchar2(204);
740 BEGIN
741
742
743 l_cross_ref := lpad(Rtrim(p_concatenated_segments, '%'), g_gtin_code_length,
744 '00000000000000');
745
746 OPEN x_items FOR
747 SELECT DISTINCT msik.concatenated_segments concatenated_segments
748 , msik.inventory_item_id
749 , msik.description
750 FROM mtl_system_items_kfv msik, wsh_delivery_details dd, wsh_delivery_assignments da,
751 wsh_new_deliveries nd
752 WHERE msik.concatenated_segments LIKE (p_concatenated_segments)
753 AND msik.organization_id = p_organization_id
754 AND msik.inventory_item_id = dd.inventory_item_id
755 AND nd.delivery_id = p_delivery_id
756 AND nd.delivery_id = da.delivery_id
757 AND da.delivery_detail_id = dd.delivery_detail_id
758 AND (dd.inv_interfaced_flag = 'N' OR dd.inv_interfaced_flag IS NULL)
759 AND dd.released_status = 'Y'
760 AND nd.status_code NOT IN ('CO', 'CL', 'IT')
761
762 --Changes for GTIN
763 UNION
764
765
766 SELECT DISTINCT msik.concatenated_segments concatenated_segments
767 , msik.inventory_item_id
768 , msik.description
769 FROM mtl_system_items_kfv msik,
770 wsh_delivery_details dd,
771 wsh_delivery_assignments da,
772 wsh_new_deliveries nd,
773 mtl_cross_references mcr
774 WHERE msik.organization_id = p_organization_id
775 AND msik.inventory_item_id = dd.inventory_item_id
776
777 AND nd.delivery_id = p_delivery_id
778 AND nd.delivery_id = da.delivery_id
779 AND da.delivery_detail_id = dd.delivery_detail_id
780 AND (dd.inv_interfaced_flag = 'N' OR dd.inv_interfaced_flag IS NULL)
781 AND dd.released_status = 'Y'
782 AND nd.status_code NOT IN ('CO', 'CL', 'IT')
783 AND msik.inventory_item_id = mcr.inventory_item_id
784 AND mcr.cross_reference_type = g_gtin_cross_ref_type
785 AND mcr.cross_reference LIKE l_cross_ref
786 AND (mcr.organization_id = msik.organization_id
787 OR
788
789 mcr.org_independent_flag = 'Y')
790 ORDER BY concatenated_segments;
791 END get_ship_items_lov;
792
793 PROCEDURE Get_Ship_Method_LoV(x_shipMethodLOV OUT NOCOPY t_genref,
794 p_organization_id IN NUMBER,
795 p_ship_method_name IN VARCHAR2) IS
796 BEGIN
797 OPEN x_shipMethodLOV for
798 select
799 meaning,
800
801 description,
802 lookup_code ship_method_code
803 from fnd_lookup_values_vl flv
804 where lookup_type = 'SHIP_METHOD'
805 and view_application_id = 3
806 and nvl(start_date_active,sysdate)<=sysdate
807 AND nvl(end_date_active,sysdate)>=sysdate
808 AND enabled_flag = 'Y'
809 AND meaning like ( p_ship_method_name)
810 AND lookup_code in (select ship_method_code
811 from wsh_carrier_services wcs, wsh_org_carrier_services wocs,
812 wsh_carriers wc
813 where wocs.organization_id = p_organization_id
814 AND wcs.ship_method_code = flv.lookup_code
815 AND wcs.enabled_flag = 'Y'
816 AND wocs.enabled_flag = 'Y'
817 AND wcs.carrier_service_id = wocs.carrier_service_id
818 and wcs.carrier_id = wc.carrier_id)
819 --- AND NVL(wc.generic_flag, 'N') = 'N')
820 order by meaning;
821
822
823 END Get_Ship_Method_LoV;
824
825 END GML_MOBILE_SHIP_CONFIRM;