1 PACKAGE BODY WMS_SHIPPING_PUB AS
2 /* $Header: WMSSHPPB.pls 120.2 2005/10/21 18:15:25 mrana noship $ */
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 CURSOR get_delivery_detail_id (l_delivery_id NUMBER)
36 IS
37 SELECT delivery_detail_id
38 FROM wsh_delivery_assignments_v
39 WHERE delivery_id = l_delivery_id;
40 CURSOR get_lpn_id (l_delivery_detail_id NUMBER)
41 IS
42 SELECT DISTINCT outermost_lpn_id
43 FROM wms_shipping_transaction_temp
44 WHERE delivery_detail_id = l_delivery_detail_id ;
45 -- MRANA .. bug4287561 ..AND nvl(direct_ship_flag,'N') = 'N';
46 /* MRANA .. bug4287561 ...commented the above condition since we
47 --want to process lines loaded thru direct ship page too */
48
49 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
50 BEGIN
51 IF (l_debug = 1) THEN debug('IN ... DEL_WSTT_RECS_BY_DELIVERY_ID ' ); END IF;
52
53 -- Initialize return status to success
54 x_return_status := FND_API.G_RET_STS_SUCCESS;
55
56 IF NOT FND_API.compatible_api_call(l_api_version,
57 p_api_version,
58 l_api_name,
59 G_PKG_NAME) THEN
60 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
61 END IF;
62
63 -- Initialize message list.
64 IF fnd_api.to_boolean(p_init_msg_list) THEN
65 fnd_msg_pub.initialize;
66 END IF;
67
68 SAVEPOINT DEL_WSTT_RECS_BY_DELIVERY_ID;
69
70 IF (p_delivery_ids.COUNT <> 0) THEN
71 FOR i IN p_delivery_ids.first .. p_delivery_ids.last
72 LOOP
73 l_delivery_id := p_delivery_ids(i);
74
75 IF (l_debug = 1) THEN
76 debug('Deleted Delivery : '||l_delivery_id);
77 debug('wms_globals.g_ship_confirm_method: ' || wms_globals.g_ship_confirm_method);
78 END IF;
79
80 OPEN get_delivery_detail_id(l_delivery_id);
81 LOOP
82 FETCH get_delivery_detail_id
83 INTO l_delivery_detail_id;
84 EXIT WHEN get_delivery_detail_id%notfound;
85
86 IF l_debug = 1 THEN
87 debug('wms_globals.g_ship_confirm_method : ' || wms_globals.g_ship_confirm_method);
88 END IF;
89
90 -- MRANA .. bug4287561
91 -- Added the condition around the delete, as it should happen only if
92 --it is not called from direct ship
93 IF (wms_globals.g_ship_confirm_method IS NULL OR
94 wms_globals.g_ship_confirm_method <> 'DIRECT') THEN
95
96 IF l_organization_id IS NULL THEN
97 -- Assuming that all deliveries being ship confirmed belong to the same org
98 SELECT organization_id
99 INTO l_organization_id
100 FROM wsh_delivery_details
101 WHERE delivery_detail_id = l_delivery_detail_id;
102 END IF;
103 IF l_debug = 1 THEN debug('Deleting mtl_material_transactions_temp '); END IF;
104
105 /* MRANA :
106 -- {{- Delete all pending staging move transactions in MMTT/WDT, for }}
107 -- {{ all the staged LPNs belonging to the given delivery }} */
108 BEGIN
109
110 DELETE FROM wms_dispatched_tasks wdt
111 WHERE task_type = 7
112 AND organization_id = l_organization_id
113 AND transfer_lpn_id IN
114 (SELECT wlpn.outermost_lpn_id
115 FROM wms_license_plate_numbers wlpn,
116 wsh_delivery_details wdd,
117 wsh_delivery_assignments_v wda
118 WHERE wda.delivery_detail_id = l_delivery_detail_id
119 AND wda.parent_delivery_detail_id = wdd.delivery_detail_id
120 AND wdd.lpn_id = wlpn.lpn_id);
121 IF l_debug = 1 THEN debug('Deleted Staging move WDTs ' || l_delivery_detail_id); END IF;
122 IF SQL%NOTFOUND THEN
123 NULL; -- will not find for direct ship case
124 IF l_debug = 1 THEN
125 debug('could not fine Staging move WDTs ' || l_delivery_detail_id);
126 END IF;
127 END IF;
128 DELETE FROM mtl_material_transactions_temp
129 WHERE wms_task_type = 7
130 AND organization_id = l_organization_id
131 AND content_lpn_id IN
132 ( SELECT wlpn.outermost_lpn_id
133 FROM wms_license_plate_numbers wlpn,
134 wsh_delivery_details wdd,
135 wsh_delivery_assignments_v wda
136 WHERE wda.delivery_detail_id = l_delivery_detail_id
137 AND wda.parent_delivery_detail_id = wdd.delivery_detail_id
138 AND wdd.lpn_id = wlpn.lpn_id);
139 IF l_debug = 1 THEN debug('Deleted Staging move MMTT ' || l_delivery_detail_id); END IF;
140 IF SQL%NOTFOUND THEN
141 NULL; -- will not find for direct ship case
142 IF l_debug = 1 THEN
143 debug('could not fine Staging move MMTT ' || l_delivery_detail_id);
144 END IF;
145 END IF;
146 EXCEPTION
147 WHEN NO_DATA_FOUND THEN
148 null;
149 IF l_debug = 1 THEN debug('No lpn found in WDD for DDL: ' || l_delivery_detail_id); END IF;
150 END ;
151
152
153 IF l_debug = 1 THEN debug('Open get_lpn_id '); END IF;
154
155 -- get lpn_ids to update the context
156 /* Mrana: MDC & LPN Convergence : The following updates should not be necessary
157 * OPEN get_lpn_id(l_delivery_detail_id);
158 LOOP
159 FETCH get_lpn_id INTO l_outermost_lpn_id;
160 EXIT WHEN get_lpn_id%notfound;
161 -- update lpns with context of picked
162
163 IF l_debug = 1 THEN debug('Ready to update wms_license_plate_numbers '); END IF;
164
165 --// Talk to Jason/Tharian **MRANA
166 UPDATE wms_license_plate_numbers
167 SET lpn_context = 11,
168 last_update_date = Sysdate,
169 last_updated_by = fnd_global.user_id
170 WHERE lpn_id IN (SELECT lpn_id
171 FROM wms_license_plate_numbers
172 START WITH lpn_id = l_outermost_lpn_id
173 CONNECT BY parent_lpn_id = PRIOR lpn_id);
174
175 IF l_debug = 1 THEN
176 debug('Updated wms_license_plate_numbers : ' || l_outermost_lpn_id);
177 END IF;
178 END LOOP;
179 CLOSE get_lpn_id; */
180 END IF; -- wms_globals.g_ship_confirm_method <> 'DIRECT' THEN
181 END LOOP;
182
183 IF l_debug = 1 THEN
184 debug('deleting wms_direct_ship_temp ' || l_delivery_detail_id);
185 END IF;
186
187 CLOSE get_delivery_detail_id;
188
189 -- MRANA .. bug4287561
190 -- Moved the deletion of the temp table records from delivery
191 -- detail loop to delivery_id loop for better efficieny.
192 -- In case this API is called from direct ship page, we do
193 -- not want to delete these records since it happens in DS page
194 IF (wms_globals.g_ship_confirm_method IS NULL OR
195 wms_globals.g_ship_confirm_method <> 'DIRECT') THEN
196 BEGIN
197 IF l_debug = 1 THEN debug('Ready to delete wms_direct_ship_temp '); END IF;
198
199 DELETE FROM wms_direct_ship_temp
200 WHERE LPN_ID IN (SELECT DISTINCT outermost_lpn_id
201 FROM wms_shipping_transaction_temp
202 WHERE delivery_id = l_delivery_id);
203 IF l_debug = 1 THEN
204 debug('sucessful deleting wms_direct_ship_temp for all lpns with delivery_id = ' || l_delivery_id);
205 END IF;
206 IF SQL%NOTFOUND THEN
207 NULL;
208 IF l_debug = 1 THEN
209 debug('could not find any wms_direct_ship_temp for lpns with delivery_id = ' || l_delivery_id);
210 END IF;
211 END IF ;
212 END ;
213 BEGIN
214 IF l_debug = 1 THEN debug('Ready to delete wms_shipping_transaction_temp '); END IF;
215 DELETE FROM wms_shipping_transaction_temp
216 WHERE delivery_id = l_delivery_id ;
217 IF l_debug = 1 THEN
218 debug('sucessful deleting wms_shipping_transaction_temp where delivery_id = ' || l_delivery_id);
219 END IF;
220 IF SQL%NOTFOUND THEN
221 NULL;
222 IF l_debug = 1 THEN
223 debug('Could not delete wms_shipping_transaction_temp where delivery_id = ' || l_delivery_id);
224 END IF;
225 END IF ;
226 END;
227 END IF;
228
229
230 END LOOP;
231 END IF;
232
233 -- Standard check of p_commit.
234 IF fnd_api.to_boolean(p_commit) THEN
235 COMMIT WORK;
236 END IF;
237
238 IF (l_debug = 1) THEN debug('OUT ... DEL_WSTT_RECS_BY_DELIVERY_ID ' ); END IF;
239 EXCEPTION
240 WHEN fnd_api.g_exc_error THEN
241 x_return_status := FND_API.G_RET_STS_ERROR;
242 ROLLBACK to DEL_WSTT_RECS_BY_DELIVERY_ID;
243 fnd_msg_pub.count_and_get
244 ( p_encoded => FND_API.G_FALSE,
245 p_count => x_msg_count,
246 p_data => x_msg_data
247 );
248 IF (l_debug = 1) THEN
249 DEBUG('Error ! SQL Code : '||sqlcode);
250 END IF;
251
252 WHEN fnd_api.g_exc_unexpected_error THEN
253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
254 ROLLBACK to DEL_WSTT_RECS_BY_DELIVERY_ID;
255 fnd_msg_pub.count_and_get
256 ( p_encoded => FND_API.G_FALSE,
257 p_count => x_msg_count,
258 p_data => x_msg_data
259 );
260 IF (l_debug = 1) THEN
261 DEBUG('Unknown Error ! SQL Code : '||sqlcode);
262 END IF;
263
264 WHEN others THEN
265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
266 ROLLBACK TO DEL_WSTT_RECS_BY_DELIVERY_ID;
267 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
268 fnd_msg_pub.add_exc_msg
269 ( 'WMS_SHIPPING_PUB',
270 'DEL_WSTT_RECS_BY_DELIVERY_ID'
271 );
272 END IF;
273 fnd_msg_pub.count_and_get
274 ( p_encoded => FND_API.G_FALSE,
275 p_count => x_msg_count,
276 p_data => x_msg_data
277 );
278 IF (l_debug = 1) THEN
279 DEBUG('Other Error ! SQL Code : '||sqlcode);
280 END IF;
281
282 END DEL_WSTT_RECS_BY_DELIVERY_ID;
283
284 END WMS_SHIPPING_PUB;
[Home] [Help]