[Home] [Help]
PACKAGE BODY: APPS.WMS_SHIPPING_LPN_UTILS_PUB
Source
1 PACKAGE BODY WMS_Shipping_LPN_Utils_PUB AS
2 /* $Header: WMSSHUTB.pls 120.1.12020000.2 2012/07/04 06:36:49 abasheer ship $ */
3
4
5 PROCEDURE mydebug(msg in varchar2)
6 IS
7 l_msg VARCHAR2(5100);
8 l_ts VARCHAR2(30);
9 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
10 BEGIN
11 -- select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
12 -- l_msg:=l_ts||' '||msg;
13
14 l_msg := msg;
15
16 inv_mobile_helper_functions.tracelog
17 (p_err_msg => l_msg,
18 p_module => 'WMS_Shipping_LPN_Utils_PUB',
19 p_level => 4);
20
21 END;
22
23
24 PROCEDURE update_lpn_context
25 ( p_delivery_id IN NUMBER,
26 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
27 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
28 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
29 IS
30
31 CURSOR child_lpns_cursor(l_lpn_id IN NUMBER) IS
32 SELECT wlpn.lpn_id
33 FROM wms_license_plate_numbers wlpn
34 WHERE wlpn.lpn_context <> wms_globals.lpn_context_inv
35 START WITH wlpn.lpn_id = l_lpn_id
36 CONNECT BY wlpn.lpn_id = PRIOR parent_lpn_id;
37
38 CURSOR lpns_in_delivery IS
39 SELECT wdd.lpn_id
40 FROM
41 wsh_delivery_assignments_v wda,
42 wsh_delivery_details wdd
43 WHERE wda.delivery_id = p_delivery_id
44 AND wda.delivery_detail_id = wdd.delivery_detail_id
45 AND wdd.lpn_id IS NOT NULL;
46
47 l_lpn_id NUMBER;
48 l_innermost_lpn_id NUMBER;
49
50 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
51 BEGIN
52
53 IF (l_debug = 1) THEN
54 mydebug('update_lpn_context: Begin update_lpn_context');
55 END IF;
56
57 OPEN lpns_in_delivery;
58 LOOP
59 FETCH lpns_in_delivery INTO l_innermost_lpn_id;
60 EXIT WHEN lpns_in_delivery%notfound;
61
62 OPEN child_lpns_cursor(l_innermost_lpn_id);
63 LOOP
64 FETCH child_lpns_cursor INTO l_lpn_id;
65 EXIT WHEN child_lpns_cursor%notfound;
66
67 UPDATE wms_license_plate_numbers
68 SET lpn_context = wms_globals.lpn_context_inv
69 WHERE lpn_id = l_lpn_id;
70
71 END LOOP;
72 CLOSE child_lpns_cursor;
73
74 END LOOP;
75 CLOSE lpns_in_delivery;
76
77 IF (l_debug = 1) THEN
78 mydebug('update_lpn_context: End update_lpn_context');
79 END IF;
80
81 EXCEPTION
82
83 WHEN FND_API.G_EXC_ERROR THEN
84 x_return_status:=FND_API.G_RET_STS_ERROR;
85
86 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
87 fnd_msg_pub.ADD;
88
89 fnd_msg_pub.count_and_get
90 ( p_count => x_msg_count
91 , p_data => x_msg_data
92 );
93
94 IF (l_debug = 1) THEN
95 mydebug('update_lpn_context: Error in update_lpn_context API: ' || sqlerrm);
96 END IF;
97
98 WHEN OTHERS THEN
99
100 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
101
102 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
103 fnd_msg_pub.ADD;
104
105 fnd_msg_pub.count_and_get
106 ( p_count => x_msg_count
107 , p_data => x_msg_data
108 );
109
110 IF (l_debug = 1) THEN
111 mydebug('update_lpn_context: Unexpected Error in update_lpn_context API: ' || sqlerrm);
112 END IF;
113
114 END;
115
116
117
118 END WMS_Shipping_LPN_Utils_PUB;