1 PACKAGE BODY WMS_SHIPPING_PUB AS
2 /* $Header: WMSSHPPB.pls 120.2.12020000.2 2012/07/04 06:36:40 abasheer ship $ */
3
4 G_Debug BOOLEAN := TRUE;
5
6 PROCEDURE DEBUG(p_message IN VARCHAR2) IS
7 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
8 BEGIN
9 if( G_Debug = TRUE ) then
10 inv_mobile_helper_functions.tracelog
11 (p_err_msg => p_message,
12 p_module => 'WMS_SHIPPING_PUB',
13 p_level => 9);
14 end if;
15 END;
16
17
18 PROCEDURE DEL_WSTT_RECS_BY_DELIVERY_ID (x_return_status OUT NOCOPY VARCHAR2,
19 x_msg_count OUT NOCOPY NUMBER,
20 x_msg_data OUT NOCOPY VARCHAR2,
21 p_commit IN VARCHAR2 := FND_API.g_false,
22 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
23 p_api_version IN NUMBER := 1.0, --3555636 changed from varchar2 to number
24 p_delivery_ids IN wsh_util_core.id_tab_type
25 )
26 IS
27 l_delivery_id NUMBER;
28 l_delivery_detail_id NUMBER;
29 l_outermost_lpn_id NUMBER;
30 l_api_version CONSTANT NUMBER := 1.0;
31 l_api_name CONSTANT VARCHAR2(30) := 'DEL_WSTT_RECS_BY_DELIVERY_ID';
32 l_organization_id NUMBER;
33 l_direct_ship_flag VARCHAR2(1) := 'N';
34
35 /*13442511 added following*/
36 TYPE l_num_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
37 l_lpn_id_tab l_num_table_type;
38 l_org_id_tab l_num_table_type;
39 l_delivery_detail_id_tab l_num_table_type;--bug13500119
40
41 /*13442511 modified cursor to fetch all details at once*/
42 CURSOR get_delivery_detail_id (l_delivery_id NUMBER)
43 IS
44 SELECT wlpn.outermost_lpn_id,wlpn.organization_id,wda.delivery_detail_id
45 FROM wsh_delivery_assignments_v wda ,
46 wsh_delivery_details lpn_wdd ,
47 wms_license_plate_numbers wlpn
48 WHERE wda.delivery_id = l_delivery_id
49 AND wda.parent_delivery_detail_id=lpn_wdd.delivery_detail_id
50 AND wlpn.lpn_id=lpn_wdd.lpn_id;--bug13500119
51
52 CURSOR get_lpn_id (l_delivery_detail_id NUMBER)
53 IS
54 SELECT DISTINCT outermost_lpn_id
55 FROM wms_shipping_transaction_temp
56 WHERE delivery_detail_id = l_delivery_detail_id ;
57 -- MRANA .. bug4287561 ..AND nvl(direct_ship_flag,'N') = 'N';
58 /* MRANA .. bug4287561 ...commented the above condition since we
59 --want to process lines loaded thru direct ship page too */
60
61 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
62 BEGIN
63 IF (l_debug = 1) THEN debug('IN ... DEL_WSTT_RECS_BY_DELIVERY_ID ' ); END IF;
64
65 -- Initialize return status to success
66 x_return_status := FND_API.G_RET_STS_SUCCESS;
67
68 IF NOT FND_API.compatible_api_call(l_api_version,
69 p_api_version,
70 l_api_name,
71 G_PKG_NAME) THEN
72 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
73 END IF;
74
75 -- Initialize message list.
76 IF fnd_api.to_boolean(p_init_msg_list) THEN
77 fnd_msg_pub.initialize;
78 END IF;
79
80 SAVEPOINT DEL_WSTT_RECS_BY_DELIVERY_ID;
81
82 IF (p_delivery_ids.COUNT <> 0) THEN
83 FOR i IN p_delivery_ids.first .. p_delivery_ids.last LOOP
84
85 l_delivery_id := p_delivery_ids(i);
86
87 /*13442511 enabled bulking*/
88 OPEN get_delivery_detail_id(l_delivery_id);
89 FETCH get_delivery_detail_id BULK COLLECT
90 INTO l_lpn_id_tab,l_org_id_tab,l_delivery_detail_id_tab;--bug13500119
91 close get_delivery_detail_id;
92
93 IF l_debug = 1 THEN
94 debug('Deleted Delivery : '||l_delivery_id);
95 debug('l_lpn_id_tab.count = : ' || l_lpn_id_tab.COUNT);
96 debug('wms_globals.g_ship_confirm_method : ' || wms_globals.g_ship_confirm_method);
97 END IF;
98
99 -- MRANA .. bug4287561
100 -- Added the condition around the delete, as it should happen only if
101 --it is not called from direct ship
102 IF (wms_globals.g_ship_confirm_method IS NULL OR
103 wms_globals.g_ship_confirm_method <> 'DIRECT') THEN
104
105 IF l_debug = 1 THEN debug('Deleting WDT / MMTT '); END IF;
106
107 /* MRANA :
108 -- {{- Delete all pending staging move transactions in MMTT/WDT, for }}
109 -- {{ all the staged LPNs belonging to the given delivery }} */
110
111 /*13442511 modified the logic to bulk delete.*/
112 IF (l_lpn_id_tab.COUNT > 0 ) THEN
113 FORALL i IN l_lpn_id_tab.first .. l_lpn_id_tab.last
114 DELETE FROM wms_dispatched_tasks wdt
115 WHERE task_type = 7
116 AND organization_id = l_org_id_tab(i)
117 AND transfer_lpn_id = l_lpn_id_tab(i);
118
119 FORALL i IN l_lpn_id_tab.first .. l_lpn_id_tab.last
120 DELETE FROM mtl_material_transactions_temp
121 WHERE wms_task_type = 7
122 AND organization_id = l_org_id_tab(i)
123 AND content_lpn_id = l_lpn_id_tab(i);
124 END IF;
125
126 -- MRANA .. bug4287561
127 -- Moved the deletion of the temp table records from delivery
128 -- detail loop to delivery_id loop for better efficieny.
129 -- In case this API is called from direct ship page, we do
130 -- not want to delete these records since it happens in DS page
131 IF l_debug = 1 THEN debug('Ready to delete wms_direct_ship_temp '); END IF;
132
133 DELETE FROM wms_direct_ship_temp
134 WHERE LPN_ID IN (SELECT DISTINCT outermost_lpn_id
135 FROM wms_shipping_transaction_temp
136 WHERE delivery_id = l_delivery_id);
137 IF SQL%NOTFOUND THEN
138 NULL;
139 IF l_debug = 1 THEN
140 debug('could not find any wms_direct_ship_temp for lpns with delivery_id = ' || l_delivery_id);
141 END IF;
142 ELSE
143 IF l_debug = 1 THEN
144 debug('sucessful deleting wms_direct_ship_temp for all lpns with delivery_id = ' || l_delivery_id);
145 END IF;
146 END IF ;
147
148 IF l_debug = 1 THEN debug('Ready to delete wms_shipping_transaction_temp '); END IF;
149 /* bug 13500119 */
150 IF (l_delivery_detail_id_tab.COUNT > 0 ) THEN
151 FORALL i IN l_delivery_detail_id_tab.first .. l_delivery_detail_id_tab.last
152 DELETE FROM wms_shipping_transaction_temp
153 WHERE delivery_detail_id = l_delivery_detail_id_tab(i);
154 IF l_debug = 1 THEN
155 debug('Delete wms_shipping_transaction_temp where delivery_detail_id = ' || l_delivery_detail_id_tab(i));
156 END IF;
157 END IF;
158 /* bug 13500119 */
159
160 IF SQL%NOTFOUND THEN
161 NULL;
162 IF l_debug = 1 THEN
163 debug('Could not delete wms_shipping_transaction_temp where delivery_id = ' || l_delivery_id);
164 END IF;
165 ELSE
166 IF l_debug = 1 THEN
167 debug('sucessful deleting wms_shipping_transaction_temp where delivery_id = ' || l_delivery_id);
168 END IF;
169 END IF ;
170 END IF; -- wms_globals.g_ship_confirm_method <> 'DIRECT' THEN
171 END LOOP;
172 END IF;
173
174 -- Standard check of p_commit.
175 IF fnd_api.to_boolean(p_commit) THEN
176 COMMIT WORK;
177 END IF;
178
179 IF (l_debug = 1) THEN debug('OUT ... DEL_WSTT_RECS_BY_DELIVERY_ID ' ); END IF;
180 EXCEPTION
181 WHEN fnd_api.g_exc_error THEN
182 x_return_status := FND_API.G_RET_STS_ERROR;
183 ROLLBACK to DEL_WSTT_RECS_BY_DELIVERY_ID;
184 fnd_msg_pub.count_and_get
185 ( p_encoded => FND_API.G_FALSE,
186 p_count => x_msg_count,
187 p_data => x_msg_data
188 );
189 IF (l_debug = 1) THEN
190 DEBUG('Error ! SQL Code : '||sqlcode);
191 END IF;
192
193 WHEN fnd_api.g_exc_unexpected_error THEN
194 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
195 ROLLBACK to DEL_WSTT_RECS_BY_DELIVERY_ID;
196 fnd_msg_pub.count_and_get
197 ( p_encoded => FND_API.G_FALSE,
198 p_count => x_msg_count,
199 p_data => x_msg_data
200 );
201 IF (l_debug = 1) THEN
202 DEBUG('Unknown Error ! SQL Code : '||sqlcode);
203 END IF;
204
205 WHEN others THEN
206 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
207 ROLLBACK TO DEL_WSTT_RECS_BY_DELIVERY_ID;
208 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
209 fnd_msg_pub.add_exc_msg
210 ( 'WMS_SHIPPING_PUB',
211 'DEL_WSTT_RECS_BY_DELIVERY_ID'
212 );
213 END IF;
214 fnd_msg_pub.count_and_get
215 ( p_encoded => FND_API.G_FALSE,
216 p_count => x_msg_count,
217 p_data => x_msg_data
218 );
219 IF (l_debug = 1) THEN
220 DEBUG('Other Error ! SQL Code : '||sqlcode);
221 END IF;
222
223 END DEL_WSTT_RECS_BY_DELIVERY_ID;
224
225 END WMS_SHIPPING_PUB;
[Home] [Help]