[Home] [Help]
PACKAGE BODY: APPS.WSH_WMS_LPN_GRP
Source
1 PACKAGE BODY WSH_WMS_LPN_GRP as
2 /* $Header: WSHWLGPB.pls 120.14.12010000.2 2008/08/04 12:33:58 suppal ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_WMS_LPN_GRP';
5
6 PROCEDURE Handle_miss_info
7 ( p_container_info_rec IN OUT NOCOPY
8 WSH_GLBL_VAR_STRCT_GRP.delivery_details_Rec_Type,
9 x_return_status OUT NOCOPY varchar2
10 );
11
12 --========================================================================
13 -- PROCEDURE : create_update_containers Must be called only by WMS APIs
14 --
15 -- PARAMETERS: p_api_version known api version error buffer
16 -- p_init_msg_list FND_API.G_TRUE to reset list
17 -- x_return_status return status
18 -- x_msg_count number of messages in the list
19 -- x_msg_data text of messages
20 -- p_in_rec Record for caller,
21 -- and action_code ( CREATE,UPDATE,
22 -- UPDATE_NULL)
23 -- p_detail_info_tab Table of attributes for the containers
24 -- x_OUT_rec not used (bms)
25 -- VERSION : current version 1.0
26 -- initial version 1.0
27 -- COMMENT : Creates or updates a record in wsh_new_deliveries table with information
28 -- specified in p_delivery_info
29 --========================================================================
30 PROCEDURE create_update_containers
31 ( p_api_version IN NUMBER,
32 p_init_msg_list IN VARCHAR2,
33 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
34 x_return_status OUT NOCOPY VARCHAR2,
35 x_msg_count OUT NOCOPY NUMBER,
36 x_msg_data OUT NOCOPY VARCHAR2,
37 p_detail_info_tab IN OUT NOCOPY
38 WSH_GLBL_VAR_STRCT_GRP.delivery_details_Attr_tbl_Type,
39 p_IN_rec IN WSH_GLBL_VAR_STRCT_GRP.detailInRecType,
40 x_OUT_rec OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.detailOutRecType
41 )
42 IS
43 --
44 l_debug_on BOOLEAN;
45 --
46 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME ||
47 '.' || 'CREATE_UPDATE_CONTAINERS';
48 --
49 l_return_status VARCHAR2(1);
50 l_api_version_number CONSTANT NUMBER := 1.0;
51 l_api_name CONSTANT VARCHAR2(30):= 'create_update_containers';
52 l_msg_count NUMBER;
53 l_msg_data VARCHAR2(32767);
54 l_param_name VARCHAR2(100);
55 l_in_param_null BOOLEAN := FALSE;
56 l_num_warnings NUMBER := 0;
57 l_num_errors NUMBER := 0;
58 i NUMBER;
59 l_delivery_detail_id NUMBER;
60 l_dummy NUMBER;
61
62 CURSOR c_lock_container(v_lpn_id number) IS
63 SELECT delivery_detail_id
64 FROM wsh_delivery_details
65 WHERE lpn_id = v_lpn_id AND
66 --LPN reuse project
67 released_status = 'X'
68 FOR UPDATE NOWAIT;
69
70 CURSOR c_lpn_exist (v_lpn_id NUMBER) IS
71 SELECT 1
72 FROM wsh_delivery_details
73 WHERE lpn_id = v_lpn_id
74 AND
75 --LPN reuse project
76 released_status = 'X';
77
78 e_success EXCEPTION;
79
80 BEGIN
81 --
82 SAVEPOINT create_update_WSHWLGPB;
83 --
84 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
85 --
86 IF l_debug_on IS NULL
87 THEN
88 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
89 END IF;
90 --
91 IF l_debug_on THEN
92 --
93 wsh_debug_sv.push (l_module_name);
94 wsh_debug_sv.log (l_module_name,'p_api_version',p_api_version);
95 wsh_debug_sv.log (l_module_name,'p_init_msg_list',p_init_msg_list);
96 wsh_debug_sv.log (l_module_name,'p_commit',p_commit);
97 wsh_debug_sv.log (l_module_name,'p_detail_info_tab.count',
98 p_detail_info_tab.count);
99 wsh_debug_sv.log (l_module_name,'caller',p_IN_rec.caller);
100 wsh_debug_sv.log (l_module_name,'action_code',p_IN_rec.action_code);
101 wsh_debug_sv.log (l_module_name,'g_call_group_api',g_call_group_api);
102 wsh_debug_sv.log (l_module_name,'g_update_to_containers',g_update_to_containers);
103 --
104 END IF;
105 --
106 IF NOT FND_API.Compatible_API_Call
107 ( l_api_version_number,
108 p_api_version,
109 l_api_name,
110 G_PKG_NAME
111 )
112 THEN
113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
114 END IF;
115 --
116 IF FND_API.to_Boolean(p_init_msg_list) THEN
117 FND_MSG_PUB.initialize;
118 END IF;
119 --
120 --
121
122
123 IF p_IN_rec.caller IS NULL THEN
124 l_param_name := 'Caller';
125 l_in_param_null := TRUE;
126 ELSIF p_IN_rec.action_code IS NULL THEN
127 l_param_name := 'Action Code';
128 l_in_param_null := TRUE;
129 END IF;
130
131 IF l_in_param_null THEN
132 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
133 FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_param_name);
134 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
135 RAISE FND_API.G_EXC_ERROR;
136 END IF;
137 --
138 IF p_IN_rec.caller not like 'WMS%' THEN
139 IF l_debug_on THEN
140 --
141 wsh_debug_sv.log(l_module_name,'Invalid Caller',p_IN_rec.caller);
142 END IF;
143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
144 END IF;
145
146
147 IF p_detail_info_tab.count = 0
148 OR (g_update_to_containers = 'N' AND g_call_group_api = 'N' )THEN
149 RAISE e_success;
150 END IF;
151
152 g_caller := p_IN_rec.caller;
153
154 IF p_in_rec.action_code = 'UPDATE_NULL' THEN --{
155 i := p_detail_info_tab.FIRST;
156 WHILE i IS NOT NULL LOOP --{
157 IF l_debug_on THEN
158 wsh_debug_sv.log(l_module_name,'lpn_id',
159 p_detail_info_tab(i).lpn_id);
160 END IF;
161
162 --lock the record
163
164 BEGIN
165 OPEN c_lock_container(p_detail_info_tab(i).lpn_id);
166 FETCH c_lock_container INTO l_delivery_detail_id;
167 CLOSE c_lock_container;
168 EXCEPTION
169 WHEN app_exception.application_exception or app_exception.record_lock_exception THEN
170 FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVB_LOCK_FAILED');
171 FND_MESSAGE.SET_TOKEN('DEL_NAME', l_delivery_detail_id);
172 WSH_UTIL_CORE.Add_Message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
173 RAISE FND_API.G_EXC_ERROR;
174 END;
175
176 UPDATE wsh_delivery_details
177 SET lpn_id = NULL
178 WHERE lpn_id = p_detail_info_tab(i).lpn_id AND
179 --LPN reuse project
180 released_status = 'X';
181
182 IF SQL%ROWCOUNT <> 1 THEN
183 FND_MESSAGE.SET_NAME('WSH','WSH_LPN_UPDATE_FAILED'); --bms new
184 FND_MESSAGE.SET_TOKEN('LPNID',p_detail_info_tab(i).lpn_id);
185 WSH_UTIL_CORE.Add_Message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
186 RAISE FND_API.G_EXC_ERROR;
187 END IF;
188 i := p_detail_info_tab.NEXT(i);
189 END LOOP; --}
190
191 ELSIF p_in_rec.action_code IN ( 'UPDATE','CREATE') THEN --}{
192 G_CALLBACK_REQUIRED := 'N';
193 IF p_in_rec.action_code = 'UPDATE' THEN --{
194 i := p_detail_info_tab.FIRST;
195 WHILE i IS NOT NULL LOOP --{
196 IF l_debug_on THEN
197 wsh_debug_sv.log(l_module_name,'lpn_id',
198 p_detail_info_tab(i).lpn_id);
199 END IF;
200 IF p_detail_info_tab(i).lpn_id IS NULL THEN
201 IF l_debug_on THEN
202 wsh_debug_sv.logmsg(l_module_name,'lpn_id cannot be null');
203 END IF;
204 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
205 FND_MESSAGE.SET_TOKEN('FIELD_NAME','LPN_ID');
206 WSH_UTIL_CORE.Add_Message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
207 RAISE FND_API.G_EXC_ERROR;
208 END IF;
209
210 BEGIN
211
212 SELECT delivery_detail_id
213 INTO p_detail_info_tab(i).delivery_detail_id
214 FROM WSH_DELIVERY_DETAILS
215 WHERE lpn_id = p_detail_info_tab(i).lpn_id AND
216 --LPN reuse project
217 released_status = 'X';
218
219
220 EXCEPTION
221 WHEN NO_DATA_FOUND THEN
222 IF l_debug_on THEN
223 wsh_debug_sv.log(l_module_name,'There are no records in wsh_delivery_details for lpn_id: ', p_detail_info_tab(i).lpn_id);
224 END IF;
225
226 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_DETAIL'); --bmso new message
227 WSH_UTIL_CORE.Add_Message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
228 RAISE FND_API.G_EXC_ERROR;
229 END;
230 IF p_IN_rec.caller <> WSH_GLBL_VAR_STRCT_GRP.c_skip_miss_info THEN
231
232 --handle_miss_info is not used, when wsh_container_grp.update_container calls this API.
233
234 IF l_debug_on THEN
235 wsh_debug_sv.log(l_module_name,'lpn_id',p_detail_info_tab(i).lpn_id);
236 wsh_debug_sv.log(l_module_name,'net_weight',p_detail_info_tab(i).net_weight);
237 wsh_debug_sv.log(l_module_name,'weight_uom_code',p_detail_info_tab(i).weight_uom_code);
238 wsh_debug_sv.log(l_module_name,'gross_weight',p_detail_info_tab(i).gross_weight);
239 wsh_debug_sv.log(l_module_name,'volume',p_detail_info_tab(i).volume);
240 wsh_debug_sv.log(l_module_name,'volume_uom_code',p_detail_info_tab(i).volume_uom_code);
241 wsh_debug_sv.log(l_module_name,'filled_volume',p_detail_info_tab(i).filled_volume);
242 END IF;
243
244 handle_miss_info( p_container_info_rec => p_detail_info_tab(i),
245 x_return_status => l_return_status
246 );
247
248 wsh_util_core.api_post_call(
249 p_return_status => l_return_status,
250 x_num_warnings => l_num_warnings,
251 x_num_errors => l_num_errors);
252 END IF;
253 i := p_detail_info_tab.NEXT(i);
254 END LOOP; --}
255 ELSIF p_in_rec.action_code = 'CREATE' THEN --}{
256
257 --check if the lpn_id exists then error out
258
259 i:= p_detail_info_tab.FIRST;
260 WHILE i IS NOT NULL LOOP --{
261
262 IF l_debug_on THEN
263 wsh_debug_sv.log(l_module_name,'lpn_id',p_detail_info_tab(i).lpn_id);
264 wsh_debug_sv.log(l_module_name,'net_weight',p_detail_info_tab(i).net_weight);
265 wsh_debug_sv.log(l_module_name,'weight_uom_code',p_detail_info_tab(i).weight_uom_code);
266 wsh_debug_sv.log(l_module_name,'gross_weight',p_detail_info_tab(i).gross_weight);
267 wsh_debug_sv.log(l_module_name,'volume',p_detail_info_tab(i).volume);
268 wsh_debug_sv.log(l_module_name,'volume_uom_code',p_detail_info_tab(i).volume_uom_code);
269 wsh_debug_sv.log(l_module_name,'filled_volume',p_detail_info_tab(i).filled_volume);
270 END IF;
271
272 OPEN c_lpn_exist(p_detail_info_tab(i).lpn_id);
273 FETCH c_lpn_exist INTO l_dummy;
274 CLOSE c_lpn_exist;
275
276 IF l_dummy = 1 THEN
277 FND_MESSAGE.SET_NAME('WSH','WSH_DUPLICATE_RECORD');
278 WSH_UTIL_CORE.Add_Message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
279 RAISE FND_API.G_EXC_ERROR;
280 END IF;
281
282 i := p_detail_info_tab.NEXT(i);
283 END LOOP; --}
284 END IF ;--}
285 wsh_delivery_details_grp.create_update_delivery_detail(
286 p_api_version_number => p_api_version,
287 p_init_msg_list => FND_API.G_FALSE,
288 p_commit => FND_API.G_FALSE,
289 x_return_status => l_return_status,
290 x_msg_count => l_msg_count,
291 x_msg_data => l_msg_data,
292 p_detail_info_tab => p_detail_info_tab,
293 p_IN_rec => p_in_rec,
294 x_OUT_rec => x_out_rec
295 );
296 wsh_util_core.api_post_call(
297 p_return_status => l_return_status,
298 x_num_warnings => l_num_warnings,
299 x_num_errors => l_num_errors,
300 p_msg_data => l_msg_data);
301 ELSE --}{
302 IF l_debug_on THEN
303 wsh_debug_sv.log(l_module_name,'Invalid Action Code',
304 p_IN_rec.action_code);
305 END IF;
306 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_ACTION_CODE');
307 FND_MESSAGE.SET_TOKEN('ACT_CODE',p_IN_rec.action_code);
308 WSH_UTIL_CORE.Add_Message(WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,l_module_name);
309 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
310 END IF; --}
311
312 IF l_num_errors > 0
313 THEN
314 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
315 ELSIF l_num_warnings > 0
316 THEN
317 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
318 ELSE
319 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
320 END IF;
321
322 G_CALLBACK_REQUIRED := 'Y';
323 g_caller := NULL;
324 --
325
326 IF FND_API.To_Boolean( p_commit ) THEN
327 commit;
328 END IF;
329
330 FND_MSG_PUB.Count_And_Get (
331 p_count => x_msg_count,
332 p_data => x_msg_data,
333 p_encoded => FND_API.G_FALSE);
334 --
335 IF l_debug_on THEN
336 WSH_DEBUG_SV.pop(l_module_name);
337 END IF;
338 --
339 EXCEPTION
340
341 WHEN e_success THEN
342 G_CALLBACK_REQUIRED := 'Y';
343 g_caller := NULL;
344 --g_update_to_container := 'N';
345 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
346 FND_MSG_PUB.Count_And_Get
347 (
348 p_count => x_msg_count,
349 p_data => x_msg_data,
350 p_encoded => FND_API.G_FALSE
351 );
352
353 IF l_debug_on THEN
354 WSH_DEBUG_SV.pop(l_module_name,'Success');
355 END IF;
356 --
357 WHEN FND_API.G_EXC_ERROR THEN
358 G_CALLBACK_REQUIRED := 'Y';
359 g_caller := NULL;
360 --g_update_to_container := 'N';
361 ROLLBACK TO create_update_WSHWLGPB;
362 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
363 FND_MSG_PUB.Count_And_Get
364 (
365 p_count => x_msg_count,
366 p_data => x_msg_data,
367 p_encoded => FND_API.G_FALSE
368 );
369
370 IF l_debug_on THEN
371 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
372 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
373 END IF;
374 --
375 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
376 G_CALLBACK_REQUIRED := 'Y';
377 g_caller := NULL;
378 --g_update_to_container := 'N';
379 ROLLBACK TO create_update_WSHWLGPB;
380 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
381
382 FND_MSG_PUB.Count_And_Get
383 (
384 p_count => x_msg_count,
385 p_data => x_msg_data,
386 p_encoded => FND_API.G_FALSE
387 );
388
389 --
390 IF l_debug_on THEN
391 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
392 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
393 END IF;
394 --
395 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
396 G_CALLBACK_REQUIRED := 'Y';
397 g_caller := NULL;
398 --g_update_to_container := 'N';
399 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
400 --
401 FND_MSG_PUB.Count_And_Get
402 (
403 p_count => x_msg_count,
404 p_data => x_msg_data,
405 p_encoded => FND_API.G_FALSE
406 );
407
408 --
409 IF l_debug_on THEN
410 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
411 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
412 END IF;
413 --
414 WHEN OTHERS THEN
415 G_CALLBACK_REQUIRED := 'Y';
416 g_caller := NULL;
417 --g_update_to_container := 'N';
418 ROLLBACK TO create_update_WSHWLGPB;
419 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
420 wsh_util_core.default_handler('WSH_WMS_LPN_GRP.create_update_containers');
421 FND_MSG_PUB.Count_And_Get
422 (
423 p_count => x_msg_count,
424 p_data => x_msg_data,
425 p_encoded => FND_API.G_FALSE
426 );
427
428 --
429 IF l_debug_on THEN
430 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
431 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
432 END IF;
433 --
434
435 END create_update_containers;
436
437 --========================================================================
438 -- PROCEDURE : Handle_miss_info This procedure is called from
439 -- create_update_containers and will
440 -- populte the fields contain null with
441 -- g_miss and converts all the g_miss to null--
442 -- PARAMETERS: p_container_info_rec In/OUT record to be modified
443 -- x_return_status return status
444 --
445 --========================================================================
446
447 PROCEDURE Handle_miss_info
448 ( p_container_info_rec IN OUT NOCOPY
449 WSH_GLBL_VAR_STRCT_GRP.delivery_details_Rec_Type,
450 x_return_status OUT NOCOPY varchar2
451 )
452 IS
453 --
454 l_debug_on BOOLEAN;
455 --
456 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME ||
457 '.' || 'HANDLE_MISS_INFO';
458 --
459 l_return_status VARCHAR2(1);
460 l_num_warnings NUMBER;
461 l_num_errors NUMBER;
462
463 BEGIN
464 --
465 --
466 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
467 --
468 IF l_debug_on IS NULL
469 THEN
470 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
471 END IF;
472 --
473 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
474 IF l_debug_on THEN
475 --
476 wsh_debug_sv.push (l_module_name);
477 --
478 END IF;
479 --
480
481 --p_container_info_rec.delivery_detail_id := FND_API.G_MISS_NUM;
482 p_container_info_rec.source_code := FND_API.G_MISS_CHAR;
483 p_container_info_rec.source_header_id := FND_API.G_MISS_NUM;
484 p_container_info_rec.source_line_id := FND_API.G_MISS_NUM;
485 p_container_info_rec.customer_id := FND_API.G_MISS_NUM;
486 p_container_info_rec.sold_to_contact_id := FND_API.G_MISS_NUM;
487 p_container_info_rec.inventory_item_id := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.inventory_item_id );
488 p_container_info_rec.item_description := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.item_description );
489 p_container_info_rec.hazard_class_id := FND_API.G_MISS_NUM;
490 p_container_info_rec.country_of_origin := FND_API.G_MISS_CHAR;
491 p_container_info_rec.classification := FND_API.G_MISS_CHAR;
492 p_container_info_rec.ship_from_location_id := FND_API.G_MISS_NUM;
493 p_container_info_rec.ship_to_location_id := FND_API.G_MISS_NUM;
494 p_container_info_rec.ship_to_contact_id := FND_API.G_MISS_NUM;
495 p_container_info_rec.ship_to_site_use_id := FND_API.G_MISS_NUM;
496 p_container_info_rec.deliver_to_location_id := FND_API.G_MISS_NUM;
497 p_container_info_rec.deliver_to_contact_id := FND_API.G_MISS_NUM;
498 p_container_info_rec.deliver_to_site_use_id := FND_API.G_MISS_NUM;
499 p_container_info_rec.intmed_ship_to_location_id := FND_API.G_MISS_NUM;
500 p_container_info_rec.intmed_ship_to_contact_id := FND_API.G_MISS_NUM;
501 p_container_info_rec.hold_code := FND_API.G_MISS_CHAR;
502 p_container_info_rec.ship_tolerance_above := FND_API.G_MISS_NUM;
503 p_container_info_rec.ship_tolerance_below := FND_API.G_MISS_NUM;
504 p_container_info_rec.requested_quantity := FND_API.G_MISS_NUM;
505 p_container_info_rec.shipped_quantity := FND_API.G_MISS_NUM;
506 p_container_info_rec.delivered_quantity := FND_API.G_MISS_NUM;
507 p_container_info_rec.requested_quantity_uom := FND_API.G_MISS_CHAR;
508 p_container_info_rec.subinventory := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.subinventory );
509 p_container_info_rec.revision := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.revision );
510 p_container_info_rec.lot_number := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.lot_number );
511 p_container_info_rec.customer_requested_lot_flag := FND_API.G_MISS_CHAR;
512 p_container_info_rec.serial_number := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.serial_number );
513 p_container_info_rec.locator_id := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.locator_id );
514 p_container_info_rec.date_requested := FND_API.G_MISS_DATE;
515 p_container_info_rec.date_scheduled := FND_API.G_MISS_DATE;
516 p_container_info_rec.master_container_item_id := FND_API.G_MISS_NUM;
517 p_container_info_rec.detail_container_item_id := FND_API.G_MISS_NUM;
518 p_container_info_rec.load_seq_number := FND_API.G_MISS_NUM;
519 p_container_info_rec.ship_method_code := FND_API.G_MISS_CHAR;
520 p_container_info_rec.carrier_id := FND_API.G_MISS_NUM;
521 p_container_info_rec.freight_terms_code := FND_API.G_MISS_CHAR;
522 p_container_info_rec.shipment_priority_code := FND_API.G_MISS_CHAR;
523 p_container_info_rec.fob_code := FND_API.G_MISS_CHAR;
524 p_container_info_rec.customer_item_id := FND_API.G_MISS_NUM;
525 p_container_info_rec.dep_plan_required_flag := FND_API.G_MISS_CHAR;
526 p_container_info_rec.customer_prod_seq := FND_API.G_MISS_CHAR;
527 p_container_info_rec.customer_dock_code := FND_API.G_MISS_CHAR;
528 p_container_info_rec.cust_model_serial_number := FND_API.G_MISS_CHAR;
529 p_container_info_rec.customer_job := FND_API.G_MISS_CHAR;
530 p_container_info_rec.customer_production_line := FND_API.G_MISS_CHAR;
531 p_container_info_rec.net_weight := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.net_weight );
532 p_container_info_rec.weight_uom_code := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.weight_uom_code );
533 p_container_info_rec.volume := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.volume );
534 p_container_info_rec.volume_uom_code := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.volume_uom_code );
535 p_container_info_rec.tp_attribute_category := FND_API.G_MISS_CHAR;
536 p_container_info_rec.tp_attribute1 := FND_API.G_MISS_CHAR;
537 p_container_info_rec.tp_attribute2 := FND_API.G_MISS_CHAR;
538 p_container_info_rec.tp_attribute3 := FND_API.G_MISS_CHAR;
539 p_container_info_rec.tp_attribute4 := FND_API.G_MISS_CHAR;
540 p_container_info_rec.tp_attribute5 := FND_API.G_MISS_CHAR;
541 p_container_info_rec.tp_attribute6 := FND_API.G_MISS_CHAR;
542 p_container_info_rec.tp_attribute7 := FND_API.G_MISS_CHAR;
543 p_container_info_rec.tp_attribute8 := FND_API.G_MISS_CHAR;
544 p_container_info_rec.tp_attribute9 := FND_API.G_MISS_CHAR;
545 p_container_info_rec.tp_attribute10 := FND_API.G_MISS_CHAR;
546 p_container_info_rec.tp_attribute11 := FND_API.G_MISS_CHAR;
547 p_container_info_rec.tp_attribute12 := FND_API.G_MISS_CHAR;
548 p_container_info_rec.tp_attribute13 := FND_API.G_MISS_CHAR;
549 p_container_info_rec.tp_attribute14 := FND_API.G_MISS_CHAR;
550 p_container_info_rec.tp_attribute15 := FND_API.G_MISS_CHAR;
551 p_container_info_rec.attribute_category := FND_API.G_MISS_CHAR;
552 p_container_info_rec.attribute1 := FND_API.G_MISS_CHAR;
553 p_container_info_rec.attribute2 := FND_API.G_MISS_CHAR;
554 p_container_info_rec.attribute3 := FND_API.G_MISS_CHAR;
555 p_container_info_rec.attribute4 := FND_API.G_MISS_CHAR;
556 p_container_info_rec.attribute5 := FND_API.G_MISS_CHAR;
557 p_container_info_rec.attribute6 := FND_API.G_MISS_CHAR;
558 p_container_info_rec.attribute7 := FND_API.G_MISS_CHAR;
559 p_container_info_rec.attribute8 := FND_API.G_MISS_CHAR;
560 p_container_info_rec.attribute9 := FND_API.G_MISS_CHAR;
561 p_container_info_rec.attribute10 := FND_API.G_MISS_CHAR;
562 p_container_info_rec.attribute11 := FND_API.G_MISS_CHAR;
563 p_container_info_rec.attribute12 := FND_API.G_MISS_CHAR;
564 p_container_info_rec.attribute13 := FND_API.G_MISS_CHAR;
565 p_container_info_rec.attribute14 := FND_API.G_MISS_CHAR;
566 p_container_info_rec.attribute15 := FND_API.G_MISS_CHAR;
567 p_container_info_rec.created_by := FND_API.G_MISS_NUM;
568 p_container_info_rec.creation_date := FND_API.G_MISS_DATE;
569 p_container_info_rec.last_update_date := FND_API.G_MISS_DATE;
570 p_container_info_rec.last_update_login := FND_API.G_MISS_NUM;
571 p_container_info_rec.last_updated_by := FND_API.G_MISS_NUM;
572 p_container_info_rec.program_application_id := FND_API.G_MISS_NUM;
573 p_container_info_rec.program_id := FND_API.G_MISS_NUM;
574 p_container_info_rec.program_update_date := FND_API.G_MISS_DATE;
575 p_container_info_rec.request_id := FND_API.G_MISS_NUM;
576 p_container_info_rec.mvt_stat_status := FND_API.G_MISS_CHAR;
577 p_container_info_rec.released_flag := FND_API.G_MISS_CHAR;
578 p_container_info_rec.organization_id := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.organization_id );
579 p_container_info_rec.transaction_temp_id := FND_API.G_MISS_NUM;
580 p_container_info_rec.ship_set_id := FND_API.G_MISS_NUM;
581 p_container_info_rec.arrival_set_id := FND_API.G_MISS_NUM;
582 p_container_info_rec.ship_model_complete_flag := FND_API.G_MISS_CHAR;
583 p_container_info_rec.top_model_line_id := FND_API.G_MISS_NUM;
584 p_container_info_rec.source_header_number := FND_API.G_MISS_CHAR;
585 p_container_info_rec.source_header_type_id := FND_API.G_MISS_NUM;
586 p_container_info_rec.source_header_type_name := FND_API.G_MISS_CHAR;
587 p_container_info_rec.cust_po_number := FND_API.G_MISS_CHAR;
588 p_container_info_rec.ato_line_id := FND_API.G_MISS_NUM;
589 p_container_info_rec.src_requested_quantity := FND_API.G_MISS_NUM;
590 p_container_info_rec.src_requested_quantity_uom := FND_API.G_MISS_CHAR;
591 p_container_info_rec.move_order_line_id := FND_API.G_MISS_NUM;
592 p_container_info_rec.cancelled_quantity := FND_API.G_MISS_NUM;
593 p_container_info_rec.quality_control_quantity := FND_API.G_MISS_NUM;
594 p_container_info_rec.cycle_count_quantity := FND_API.G_MISS_NUM;
595 p_container_info_rec.tracking_number := FND_API.G_MISS_CHAR;
596 p_container_info_rec.movement_id := FND_API.G_MISS_NUM;
597 p_container_info_rec.shipping_instructions := FND_API.G_MISS_CHAR;
598 p_container_info_rec.packing_instructions := FND_API.G_MISS_CHAR;
599 p_container_info_rec.project_id := FND_API.G_MISS_NUM;
600 p_container_info_rec.task_id := FND_API.G_MISS_NUM;
601 p_container_info_rec.org_id := FND_API.G_MISS_NUM;
602 p_container_info_rec.oe_interfaced_flag := FND_API.G_MISS_CHAR;
603 p_container_info_rec.split_from_detail_id := FND_API.G_MISS_NUM;
604 p_container_info_rec.inv_interfaced_flag := FND_API.G_MISS_CHAR;
605 p_container_info_rec.source_line_number := FND_API.G_MISS_CHAR;
606 p_container_info_rec.inspection_flag := FND_API.G_MISS_CHAR;
607 p_container_info_rec.released_status := FND_API.G_MISS_CHAR;
608 p_container_info_rec.container_flag := FND_API.G_MISS_CHAR;
609 p_container_info_rec.container_type_code := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.container_type_code );
610 p_container_info_rec.container_name := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.container_name );
611 p_container_info_rec.fill_percent := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.fill_percent );
612 p_container_info_rec.gross_weight := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.gross_weight );
613 p_container_info_rec.master_serial_number := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.master_serial_number );
614 p_container_info_rec.maximum_load_weight := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.maximum_load_weight );
615 p_container_info_rec.maximum_volume := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.maximum_volume );
616 p_container_info_rec.minimum_fill_percent := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.minimum_fill_percent );
617 p_container_info_rec.seal_code := FND_API.G_MISS_CHAR;
618 p_container_info_rec.unit_number := FND_API.G_MISS_CHAR;
619 p_container_info_rec.unit_price := FND_API.G_MISS_NUM;
620 p_container_info_rec.currency_code := FND_API.G_MISS_CHAR;
621 p_container_info_rec.freight_class_cat_id := FND_API.G_MISS_NUM;
622 p_container_info_rec.commodity_code_cat_id := FND_API.G_MISS_NUM;
623 p_container_info_rec.preferred_grade := FND_API.G_MISS_CHAR;
624 p_container_info_rec.preferred_grade := FND_API.G_MISS_CHAR;
625 p_container_info_rec.src_requested_quantity2 := FND_API.G_MISS_NUM;
626 p_container_info_rec.src_requested_quantity_uom2 := FND_API.G_MISS_CHAR;
627 p_container_info_rec.requested_quantity2 := FND_API.G_MISS_NUM;
628 p_container_info_rec.shipped_quantity2 := FND_API.G_MISS_NUM;
629 p_container_info_rec.delivered_quantity2 := FND_API.G_MISS_NUM;
630 p_container_info_rec.cancelled_quantity2 := FND_API.G_MISS_NUM;
631 p_container_info_rec.quality_control_quantity2 := FND_API.G_MISS_NUM;
632 p_container_info_rec.cycle_count_quantity2 := FND_API.G_MISS_NUM;
633 p_container_info_rec.requested_quantity_uom2 := FND_API.G_MISS_CHAR;
634 p_container_info_rec.lpn_id := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.lpn_id );
635 p_container_info_rec.pickable_flag := FND_API.G_MISS_CHAR;
636 p_container_info_rec.original_subinventory := FND_API.G_MISS_CHAR;
637 p_container_info_rec.to_serial_number := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.to_serial_number );
638 p_container_info_rec.picked_quantity := FND_API.G_MISS_NUM;
639 p_container_info_rec.picked_quantity2 := FND_API.G_MISS_NUM;
640 p_container_info_rec.received_quantity := FND_API.G_MISS_NUM;
641 p_container_info_rec.received_quantity2 := FND_API.G_MISS_NUM;
642 p_container_info_rec.source_line_set_id := FND_API.G_MISS_NUM;
643 p_container_info_rec.batch_id := FND_API.G_MISS_NUM;
644 --p_container_info_rec.ROWID := FND_API.G_MISS_CHAR;
645 p_container_info_rec.transaction_id := FND_API.G_MISS_NUM;
646 p_container_info_rec.VENDOR_ID := FND_API.G_MISS_NUM;
647 p_container_info_rec.SHIP_FROM_SITE_ID := FND_API.G_MISS_NUM;
648 p_container_info_rec.LINE_DIRECTION := FND_API.G_MISS_CHAR;
649 p_container_info_rec.PARTY_ID := FND_API.G_MISS_NUM;
650 p_container_info_rec.ROUTING_REQ_ID := FND_API.G_MISS_NUM;
651 p_container_info_rec.SHIPPING_CONTROL := FND_API.G_MISS_CHAR;
652 p_container_info_rec.SOURCE_BLANKET_REFERENCE_ID := FND_API.G_MISS_NUM;
653 p_container_info_rec.SOURCE_BLANKET_REFERENCE_NUM := FND_API.G_MISS_NUM;
654 p_container_info_rec.PO_SHIPMENT_LINE_ID := FND_API.G_MISS_NUM;
655 p_container_info_rec.PO_SHIPMENT_LINE_NUMBER := FND_API.G_MISS_NUM;
656 p_container_info_rec.RETURNED_QUANTITY := FND_API.G_MISS_NUM;
657 p_container_info_rec.RETURNED_QUANTITY2 := FND_API.G_MISS_NUM;
658 p_container_info_rec.RCV_SHIPMENT_LINE_ID := FND_API.G_MISS_NUM;
659 p_container_info_rec.SOURCE_LINE_TYPE_CODE := FND_API.G_MISS_CHAR;
660 p_container_info_rec.SUPPLIER_ITEM_NUMBER := FND_API.G_MISS_CHAR;
661 p_container_info_rec.IGNORE_FOR_PLANNING := FND_API.G_MISS_CHAR;
662 p_container_info_rec.EARLIEST_PICKUP_DATE := FND_API.G_MISS_DATE;
663 p_container_info_rec.LATEST_PICKUP_DATE := FND_API.G_MISS_DATE;
664 p_container_info_rec.EARLIEST_DROPOFF_DATE := FND_API.G_MISS_DATE;
665 p_container_info_rec.LATEST_DROPOFF_DATE := FND_API.G_MISS_DATE;
666 p_container_info_rec.REQUEST_DATE_TYPE_CODE := FND_API.G_MISS_CHAR;
667 p_container_info_rec.tp_delivery_detail_id := FND_API.G_MISS_NUM;
668 p_container_info_rec.source_document_type_id := FND_API.G_MISS_NUM;
669 p_container_info_rec.unit_weight := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.unit_weight );
670 p_container_info_rec.unit_volume := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.unit_volume );
671 p_container_info_rec.filled_volume := WSH_INTERFACE_EXT_GRP.Handle_missing_info(p_container_info_rec.filled_volume );
672 p_container_info_rec.wv_frozen_flag := FND_API.G_MISS_CHAR;
673 p_container_info_rec.mode_of_transport := FND_API.G_MISS_CHAR;
674 p_container_info_rec.service_level := FND_API.G_MISS_CHAR;
675 p_container_info_rec.po_revision_number := FND_API.G_MISS_NUM;
676 p_container_info_rec.release_revision_number := FND_API.G_MISS_NUM;
677 --
678 IF l_debug_on THEN
679 WSH_DEBUG_SV.pop(l_module_name);
680 END IF;
681 --
682 EXCEPTION
683
684 WHEN OTHERS THEN
685 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
686 wsh_util_core.default_handler('WSH_WMS_LPN_GRP.Handle_miss_info');
687 --
688 IF l_debug_on THEN
689 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
690 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
691 END IF;
692 --
693
694 END Handle_miss_info;
695
696 --========================================================================
697 -- PROCEDURE : Delivery_Detail_Action Must be called only by WMS APIs
698 --
699 -- PARAMETERS: p_api_version known api version error buffer
700 -- p_init_msg_list FND_API.G_TRUE to reset list
701 -- x_return_status return status
702 -- x_msg_count number of messages in the list
703 -- x_msg_data text of messages
704 -- p_lpn_id_tbl PLSQL table of LPN Ids for perform
705 -- any of the actions 'PACK', 'UNPACK'
706 -- 'ASSIGN', 'UNASSIGN'.
707 -- p_del_det_id_tbl PLSQL table of non-container delivery
708 -- lines to perform the same actions as above
709 -- p_action_prms Contains actions related parameters
710 -- like action_code that can take any of the-- four values mentioned above.
711 -- caller should be something like 'WMS%'
712 -- lpn_rec must be populated for actions
713 -- 'PACK' or 'UNPACK'
714 -- x_defaults not used currenlty.
715 -- x_action_out_rec not used currenlty.
716 -- VERSION : current version 1.0
717 -- initial version 1.0
718 -- COMMENT : Performs any of the four actions as mentioned above i.e. 'PACK', 'UNPACK'
719 -- or 'ASSIGN', 'UNASSIGN'.
720 --========================================================================
721
722 PROCEDURE Delivery_Detail_Action
723 (
724 p_api_version_number IN NUMBER,
725 p_init_msg_list IN VARCHAR2,
726 p_commit IN VARCHAR2,
727 x_return_status OUT NOCOPY VARCHAR2,
728 x_msg_count OUT NOCOPY NUMBER,
729 x_msg_data OUT NOCOPY VARCHAR2,
730 p_lpn_id_tbl IN wsh_util_core.id_tab_type,
731 p_del_det_id_tbl IN wsh_util_core.id_tab_type,
732 p_action_prms IN WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type,
733 x_defaults OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.dd_default_parameters_rec_type,
734 x_action_out_rec OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.dd_action_out_rec_type
735 )
736 IS
737 --{
738
739 cursor l_lpn_exists_csr(p_lpn_id IN NUMBER) is
740 select 'X'
741 from wsh_delivery_details
742 where lpn_id = p_lpn_id
743 --LPN Reuse project
744 and released_status = 'X';
745
746 cursor l_get_detail_csr(p_lpn_id IN NUMBER) is
747 select delivery_detail_id,
748 inventory_item_id
749 from wsh_delivery_details
750 where lpn_id = p_lpn_id
751 --LPN Reuse project
752 and released_status = 'X';
753
754 CURSOR l_det_id_csr(p_detail_id IN NUMBER) IS
755 SELECT released_status,
756 organization_id,
757 container_flag,
758 source_code,
759 delivery_detail_id,
760 lpn_id,
761 customer_id,
762 inventory_item_id,
763 ship_from_location_id,
764 ship_to_location_id,
765 intmed_ship_to_location_id,
766 date_requested,
767 date_scheduled,
768 ship_method_code,
769 carrier_id,
770 shipping_control,
771 party_id,
772 line_direction,
773 source_line_id
774 FROM wsh_delivery_details
775 WHERE delivery_detail_id = p_detail_id;
776
777 CURSOR l_det_lpn_id_csr(p_lpn_id IN NUMBER) IS
778 SELECT wdd.released_status,
779 wdd.organization_id,
780 wdd.container_flag,
781 wdd.source_code,
782 wdd.delivery_detail_id,
783 wdd.lpn_id,
784 wdd.customer_id,
785 wdd.inventory_item_id,
786 wdd.ship_from_location_id,
787 wdd.ship_to_location_id,
788 wdd.intmed_ship_to_location_id,
789 wdd.date_requested,
790 wdd.date_scheduled,
791 wdd.ship_method_code,
792 wdd.carrier_id,
793 wdd.shipping_control,
794 wdd.party_id,
795 wdd.line_direction,
796 wdd.source_line_id,
797 wda.delivery_id,
798 wda.parent_delivery_detail_id
799 FROM wsh_delivery_details wdd,
800 wsh_delivery_assignments wda
801 WHERE wdd.lpn_id = p_lpn_id
802 and nvl(wda.type,'S') in ('S', 'C')
803 and wdd.delivery_detail_id = wda.delivery_detail_id
804 --LPN Reuse project
805 and wdd.released_status = 'X';
806
807 l_delivery_id NUMBER;
808 l_del_det_id_tbl wsh_util_core.id_tab_type;
809 l_lpn_exists VARCHAR2(1);
810 l_call_for_update_flag BOOLEAN := FALSE;
811 l_calc_fill_pc_flag BOOLEAN := FALSE;
812 l_update_sub_loc_flag BOOLEAN := FALSE;
813 l_cont_fill_pc NUMBER;
814 l_fill_status VARCHAR2(10);
815 l_action_prms WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type;
816 l_return_status VARCHAR2(1);
817 l_api_version_number CONSTANT NUMBER := 1.0;
818 l_api_name CONSTANT VARCHAR2(30):= 'create_update_containers';
819 l_msg_count NUMBER;
820 l_msg_data VARCHAR2(32767);
821 l_param_name VARCHAR2(100);
822 l_raise_error_flag BOOLEAN := FALSE;
823 l_num_warnings NUMBER := 0;
824 l_num_errors NUMBER := 0;
825 i NUMBER;
826 j NUMBER;
827 k NUMBER;
828 l_index NUMBER;
829 l_detail_info_tab WSH_GLBL_VAR_STRCT_GRP.delivery_details_Attr_tbl_Type;
830
831 l_cr_up_in_rec WSH_GLBL_VAR_STRCT_GRP.detailInRecType;
832 l_cr_up_out_rec WSH_GLBL_VAR_STRCT_GRP.detailOutRecType;
833 l_exist_detail_id NUMBER;
834 l_exist_cnt_item_id NUMBER;
835
836 l_rec_attr_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
837 l_action_out_rec WSH_GLBL_VAR_STRCT_GRP.dd_action_out_rec_type;
838 l_dummy_defaults WSH_GLBL_VAR_STRCT_GRP.dd_default_parameters_rec_type;
839
840 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
841 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
842
843
844
845
846 --}
847 --
848 l_debug_on BOOLEAN;
849 --
850 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELIVERY_DETAIL_ACTION';
851 --
852 BEGIN
853 --{
854 --
855 --
856 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
857 --
858 IF l_debug_on IS NULL
859 THEN
860 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
861 END IF;
862 --
863 --
864 -- Debug Statements
865 --
866 IF l_debug_on THEN
867 WSH_DEBUG_SV.push(l_module_name);
868 --
869 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
870 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
871 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
872 WSH_DEBUG_SV.log(l_module_name,'p_action_prms.caller',p_action_prms.caller);
873 WSH_DEBUG_SV.log(l_module_name,'p_action_prms.action_code',p_action_prms.action_code);
874 WSH_DEBUG_SV.log(l_module_name,'Count of lpn_id tbl is',p_lpn_id_tbl.count);
875 WSH_DEBUG_SV.log(l_module_name,'Count of delivery_detail_id tbl is',p_del_det_id_tbl.count);
876 END IF;
877 --
878 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
879
880 --
881 IF NOT FND_API.COMPATIBLE_API_CALL
882 ( l_api_version_number,
883 p_api_version_number,
884 l_api_name,
885 G_PKG_NAME
886 )
887 THEN
888 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
889 END IF;
890 --
891 IF FND_API.to_Boolean(p_init_msg_list) THEN
892 FND_MSG_PUB.initialize;
893 END IF;
894 --
895
896 IF p_action_prms.caller IS NULL THEN
897 l_param_name := 'Caller';
898 l_raise_error_flag := TRUE;
899 ELSIF p_action_prms.action_code IS NULL THEN
900 l_param_name := 'Action Code';
901 l_raise_error_flag := TRUE;
902 END IF;
903
904 IF l_raise_error_flag THEN
905 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
906 FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_param_name);
907 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
908 RAISE FND_API.G_EXC_ERROR;
909 END IF;
910
911 IF NOT (p_action_prms.caller like 'WMS%') THEN
912 RAISE FND_API.G_EXC_ERROR;
913 END IF;
914
915 IF p_action_prms.action_code NOT IN ('PACK', 'UNPACK', 'ASSIGN', 'UNASSIGN', 'DELETE') THEN
916 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_ACTION_CODE');
917 FND_MESSAGE.SET_TOKEN('ACT_CODE',p_action_prms.action_code);
918 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
919 RAISE FND_API.G_EXC_ERROR;
920 END IF;
921
922 IF (g_call_group_api = 'N') THEN
923 -- no need to do anything.
924 --
925 -- Debug Statements
926 --
927 IF l_debug_on THEN
928 WSH_DEBUG_SV.pop(l_module_name);
929 END IF;
930 --
931 return;
932 END IF;
933
934 g_caller := p_action_prms.caller;
935 g_callback_required := 'N';
936
937 l_action_prms := p_action_prms;
938
939 savepoint DEL_DETAIL_ACTION_WMS_GRP;
940
941 IF p_action_prms.action_code = 'PACK' THEN
942 --{
943 open l_lpn_exists_csr(p_action_prms.lpn_rec.lpn_id);
944 fetch l_lpn_exists_csr into l_lpn_exists;
945 close l_lpn_exists_csr;
946
947 l_detail_info_tab(1) := l_action_prms.lpn_rec;
948
949 l_cr_up_in_rec.caller := l_action_prms.caller;
950
951 IF l_lpn_exists IS NULL THEN
952 l_cr_up_in_rec.action_code := 'CREATE';
953
954 --
955 -- Debug Statements
956 --
957 IF l_debug_on THEN
958 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_GRP.CREATE_UPDATE_DELIVERY_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
959 END IF;
960 --
961 wsh_delivery_details_grp.create_update_delivery_detail(
962 p_api_version_number => p_api_version_number,
963 p_init_msg_list => FND_API.G_FALSE,
964 p_commit => FND_API.G_FALSE,
965 x_return_status => l_return_status,
966 x_msg_count => l_msg_count,
967 x_msg_data => l_msg_data,
968 p_detail_info_tab => l_detail_info_tab,
969 p_IN_rec => l_cr_up_in_rec,
970 x_OUT_rec => l_cr_up_out_rec
971 );
972 --
973 -- Debug Statements
974 --
975 IF l_debug_on THEN
976 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
977 END IF;
978 --
979 wsh_util_core.api_post_call(
980 p_return_status => l_return_status,
981 x_num_warnings => l_num_warnings,
982 x_num_errors => l_num_errors,
983 p_msg_data => l_msg_data
984 );
985
986 l_calc_fill_pc_flag := TRUE;
987 l_update_sub_loc_flag := TRUE;
988 ELSE
989
990 l_cr_up_in_rec.action_code := 'UPDATE';
991 --l_call_for_update_flag := TRUE;
992 END IF;
993
994 --}
995 --ELSIF p_action_prms.action_code = 'UNPACK' THEN
996 --{
997 --l_call_for_update_flag := TRUE;
998 --}
999 END IF;
1000 --
1001 IF (p_action_prms.action_code IN ('PACK', 'UNPACK')) THEN
1002 open l_get_detail_csr(p_action_prms.lpn_rec.lpn_id);
1003 fetch l_get_detail_csr into l_exist_detail_id, l_exist_cnt_item_id;
1004 close l_get_detail_csr;
1005 END IF;
1006
1007 IF (p_lpn_id_tbl.count = 0 AND p_del_det_id_tbl.count = 0) THEN
1008 RAISE FND_API.G_EXC_ERROR;
1009 END IF;
1010
1011 j := 0;
1012 k := 0;
1013
1014 IF (p_lpn_id_tbl.count > 0) THEN
1015 --{
1016 --Build the records to pass to the core group api
1017 l_index := p_lpn_id_tbl.FIRST;
1018 WHILE l_index IS NOT NULL
1019 LOOP
1020 --
1021 l_delivery_id := null;
1022 --
1023 FOR l_det_lpn_id_rec in l_det_lpn_id_csr(p_lpn_id_tbl(l_index)) LOOP
1024 --{
1025 IF ( p_action_prms.action_code in ('PACK', 'ASSIGN', 'UNASSIGN')
1026 or
1027 ( p_action_prms.action_code = 'DELETE'
1028 and l_det_lpn_id_rec.delivery_id is not null
1029 )
1030 or
1031 ( p_action_prms.action_code = 'UNPACK'
1032 and l_det_lpn_id_rec.parent_delivery_detail_id is not null
1033 )
1034 )
1035 THEN
1036 --{
1037 j := j + 1;
1038 l_rec_attr_tab(j).released_status := l_det_lpn_id_rec.released_status;
1039 l_rec_attr_tab(j).organization_id := l_det_lpn_id_rec.organization_id;
1040 l_rec_attr_tab(j).container_flag := l_det_lpn_id_rec.container_flag;
1041 l_rec_attr_tab(j).source_code := l_det_lpn_id_rec.source_code;
1042 l_rec_attr_tab(j).delivery_detail_id := l_det_lpn_id_rec.delivery_detail_id;
1043 l_rec_attr_tab(j).lpn_id := l_det_lpn_id_rec.lpn_id;
1044 l_rec_attr_tab(j).CUSTOMER_ID := l_det_lpn_id_rec.CUSTOMER_ID;
1045 l_rec_attr_tab(j).INVENTORY_ITEM_ID := l_det_lpn_id_rec.INVENTORY_ITEM_ID;
1046 l_rec_attr_tab(j).SHIP_FROM_LOCATION_ID := l_det_lpn_id_rec.SHIP_FROM_LOCATION_ID;
1047 l_rec_attr_tab(j).SHIP_TO_LOCATION_ID := l_det_lpn_id_rec.SHIP_TO_LOCATION_ID;
1048 l_rec_attr_tab(j).INTMED_SHIP_TO_LOCATION_ID := l_det_lpn_id_rec.INTMED_SHIP_TO_LOCATION_ID;
1049 l_rec_attr_tab(j).DATE_REQUESTED := l_det_lpn_id_rec.DATE_REQUESTED;
1050 l_rec_attr_tab(j).DATE_SCHEDULED := l_det_lpn_id_rec.DATE_SCHEDULED;
1051 l_rec_attr_tab(j).SHIP_METHOD_CODE := l_det_lpn_id_rec.SHIP_METHOD_CODE;
1052 l_rec_attr_tab(j).CARRIER_ID := l_det_lpn_id_rec.CARRIER_ID;
1053 l_rec_attr_tab(j).shipping_control := l_det_lpn_id_rec.shipping_control;
1054 l_rec_attr_tab(j).party_id := l_det_lpn_id_rec.party_id;
1055 l_rec_attr_tab(j).line_direction := l_det_lpn_id_rec.line_direction;
1056 l_rec_attr_tab(j).source_line_id := l_det_lpn_id_rec.source_line_id;
1057 --}
1058 END IF;
1059 --
1060 --
1061 IF (p_action_prms.action_code = 'DELETE') THEN
1062 --{
1063 --
1064 k := k + 1;
1065 l_del_det_id_tbl(k) := l_det_lpn_id_rec.delivery_detail_id;
1066 --
1067 --}
1068 END IF;
1069 --
1070 --}
1071 END LOOP;
1072 --
1073 l_index := p_lpn_id_tbl.NEXT(l_index);
1074 --
1075 END LOOP;
1076 --}
1077 END IF;
1078 --
1079 -- Now checking the size of p_del_det_id_tbl
1080 --
1081 j := 0;
1082
1083 IF (p_del_det_id_tbl.count > 0) THEN
1084 --{
1085 --Build the records to pass to the core group api
1086 l_index := p_del_det_id_tbl.FIRST;
1087 WHILE l_index IS NOT NULL
1088 LOOP
1089 j := j + 1;
1090 OPEN l_det_id_csr(p_del_det_id_tbl(l_index));
1091 FETCH l_det_id_csr
1092 INTO l_rec_attr_tab(j).released_status,
1093 l_rec_attr_tab(j).organization_id,
1094 l_rec_attr_tab(j).container_flag,
1095 l_rec_attr_tab(j).source_code,
1096 l_rec_attr_tab(j).delivery_detail_id,
1097 l_rec_attr_tab(j).lpn_id,
1098 l_rec_attr_tab(j).CUSTOMER_ID,
1099 l_rec_attr_tab(j).INVENTORY_ITEM_ID,
1100 l_rec_attr_tab(j).SHIP_FROM_LOCATION_ID,
1101 l_rec_attr_tab(j).SHIP_TO_LOCATION_ID,
1102 l_rec_attr_tab(j).INTMED_SHIP_TO_LOCATION_ID,
1103 l_rec_attr_tab(j).DATE_REQUESTED,
1104 l_rec_attr_tab(j).DATE_SCHEDULED,
1105 l_rec_attr_tab(j).SHIP_METHOD_CODE,
1106 l_rec_attr_tab(j).CARRIER_ID,
1107 l_rec_attr_tab(j).shipping_control,
1108 l_rec_attr_tab(j).party_id,
1109 l_rec_attr_tab(j).line_direction,
1110 l_rec_attr_tab(j).source_line_id;
1111
1112 IF l_det_id_csr%NOTFOUND THEN
1113 CLOSE l_det_id_csr;
1114 RAISE FND_API.G_EXC_ERROR;
1115 END IF;
1116 --
1117 CLOSE l_det_id_csr;
1118 --
1119 l_index := p_del_det_id_tbl.NEXT(l_index);
1120 --
1121 END LOOP;
1122 --}
1123 END IF;
1124
1125 IF ( l_rec_attr_tab.count > 0) THEN
1126 --{
1127
1128 l_action_prms.caller := RTRIM(p_action_prms.caller);
1129 l_action_prms.action_Code := RTRIM(p_action_prms.action_Code);
1130 l_action_prms.delivery_name := RTRIM(p_action_prms.delivery_name);
1131 l_action_prms.wv_override_flag := RTRIM(p_action_prms.wv_override_flag);
1132 l_action_prms.container_name := RTRIM(p_action_prms.lpn_rec.container_name);
1133 l_action_prms.container_flag := RTRIM(p_action_prms.container_flag);
1134 l_action_prms.delivery_flag := RTRIM(p_action_prms.delivery_flag);
1135 l_action_prms.container_instance_id := l_exist_detail_id;
1136 l_action_prms.lpn_rec.delivery_detail_id := l_exist_detail_id;
1137
1138 IF l_action_prms.action_code = 'DELETE' THEN
1139 l_action_prms.action_Code := 'UNASSIGN';
1140 END IF;
1141
1142 --
1143 -- Debug Statements
1144 --
1145 IF l_debug_on THEN
1146 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_GRP.DELIVERY_DETAIL_ACTION',WSH_DEBUG_SV.C_PROC_LEVEL);
1147 END IF;
1148 --
1149 wsh_delivery_details_grp.Delivery_Detail_Action(
1150 p_api_version_number => p_api_version_number,
1151 p_init_msg_list => FND_API.G_FALSE,
1152 p_commit => FND_API.G_FALSE,
1153 x_return_status => l_return_status,
1154 x_msg_count => l_msg_count,
1155 x_msg_data => l_msg_data,
1156 p_rec_attr_tab => l_rec_attr_tab,
1157 p_action_prms => l_action_prms,
1158 x_defaults => l_dummy_defaults,
1159 x_action_out_rec => l_action_out_rec
1160 );
1161 --
1162 --
1163 -- Debug Statements
1164 --
1165 IF l_debug_on THEN
1166 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1167 END IF;
1168 --
1169 wsh_util_core.api_post_call(
1170 p_return_status => l_return_status,
1171 x_num_warnings => l_num_warnings,
1172 x_num_errors => l_num_errors,
1173 p_msg_data => l_msg_data
1174 );
1175
1176 --}
1177 END IF;
1178
1179
1180 IF (l_call_for_update_flag) THEN
1181 --{
1182 --
1183 l_detail_info_tab(1) := l_action_prms.lpn_rec;
1184 l_cr_up_in_rec.caller := l_action_prms.caller;
1185 l_cr_up_in_rec.action_code := 'UPDATE';
1186
1187 --
1188 -- Debug Statements
1189 --
1190 IF l_debug_on THEN
1191 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_GRP.CREATE_UPDATE_DELIVERY_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
1192 END IF;
1193 --
1194 wsh_delivery_details_grp.create_update_delivery_detail(
1195 p_api_version_number => p_api_version_number,
1196 p_init_msg_list => FND_API.G_FALSE,
1197 p_commit => FND_API.G_FALSE,
1198 x_return_status => l_return_status,
1199 x_msg_count => l_msg_count,
1200 x_msg_data => l_msg_data,
1201 p_detail_info_tab => l_detail_info_tab,
1202 p_IN_rec => l_cr_up_in_rec,
1203 x_OUT_rec => l_cr_up_out_rec
1204 );
1205 --
1206 --
1207 --
1208 -- Debug Statements
1209 --
1210 IF l_debug_on THEN
1211 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1212 END IF;
1213 --
1214 wsh_util_core.api_post_call(
1215 p_return_status => l_return_status,
1216 x_num_warnings => l_num_warnings,
1217 x_num_errors => l_num_errors,
1218 p_msg_data => l_msg_data
1219 );
1220 --
1221 --}
1222 END IF;
1223 --
1224 --
1225 IF (l_update_sub_loc_flag) THEN
1226 --{
1227 --
1228 --
1229 -- Debug Statements
1230 --
1231 IF l_debug_on THEN
1232 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_ACTIONS.UPDATE_CHILD_INV_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
1233 END IF;
1234 --
1235 WSH_CONTAINER_ACTIONS.Update_child_inv_info(
1236 p_container_id => l_exist_detail_id,
1237 p_locator_id => p_action_prms.lpn_rec.locator_id,
1238 p_subinventory => p_action_prms.lpn_rec.subinventory,
1239 x_return_status => l_return_status
1240 );
1241 --
1242 --
1243 --
1244 -- Debug Statements
1245 --
1246 IF l_debug_on THEN
1247 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1248 END IF;
1249 --
1250 wsh_util_core.api_post_call(
1251 p_return_status => l_return_status,
1252 x_num_warnings => l_num_warnings,
1253 x_num_errors => l_num_errors
1254 );
1255 --
1256 --}
1257 END IF;
1258 --
1259 -- So that pvt APIs can insert into the global temp if required
1260 g_callback_required := 'Y';
1261 --
1262 IF (l_calc_fill_pc_flag AND l_exist_cnt_item_id IS NOT NULL) THEN
1263 --{
1264 --
1265 --
1266 -- Debug Statements
1267 --
1268 IF l_debug_on THEN
1269 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TPA_CONTAINER_PKG.CALC_CONT_FILL_PC',WSH_DEBUG_SV.C_PROC_LEVEL);
1270 END IF;
1271 --
1272 WSH_TPA_CONTAINER_PKG.Calc_Cont_Fill_Pc (
1273 l_exist_detail_id,
1274 'Y',
1275 NULL,
1276 l_cont_fill_pc,
1277 l_return_status);
1278
1279 --
1280 --
1281 --
1282 -- Debug Statements
1283 --
1284 IF l_debug_on THEN
1285 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1286 END IF;
1287 --
1288 wsh_util_core.api_post_call(
1289 p_return_status => l_return_status,
1290 x_num_warnings => l_num_warnings,
1291 x_num_errors => l_num_errors
1292 );
1293 --
1294 --}
1295 END IF;
1296
1297 IF (p_action_prms.action_code IN ('PACK', 'UNPACK')
1298 AND l_exist_cnt_item_id IS NOT NULL) THEN
1299 --{
1300 --
1301 -- Debug Statements
1302 --
1303 IF l_debug_on THEN
1304 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CHECK_FILL_PC',WSH_DEBUG_SV.C_PROC_LEVEL);
1305 END IF;
1306 --
1307 WSH_WV_UTILS.Check_Fill_Pc (
1308 p_container_instance_id => l_exist_detail_id,
1309 x_fill_status => l_fill_status,
1310 x_return_status => l_return_status);
1311 --
1312 --
1313 --
1314 -- Debug Statements
1315 --
1316 IF l_debug_on THEN
1317 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1318 END IF;
1319 --
1320 wsh_util_core.api_post_call(
1321 p_return_status => l_return_status,
1322 x_num_warnings => l_num_warnings,
1323 x_num_errors => l_num_errors
1324 );
1325 --
1326 IF (l_fill_status = 'O') THEN
1327 --{
1328 FND_MESSAGE.SET_NAME('WSH', 'WSH_CONT_OVERPACKED');
1329 FND_MESSAGE.SET_TOKEN('CONT_NAME', p_action_prms.lpn_rec.container_name);
1330 wsh_util_core.add_message(wsh_util_core.g_ret_sts_warning);
1331 l_num_warnings := l_num_warnings + 1;
1332 --}
1333 ELSIF (l_fill_status = 'U') THEN
1334 --{
1335 FND_MESSAGE.SET_NAME('WSH', 'WSH_CONT_OVERPACKED');
1336 FND_MESSAGE.SET_TOKEN('CONT_NAME', p_action_prms.lpn_rec.container_name);
1337 wsh_util_core.add_message(wsh_util_core.g_ret_sts_warning);
1338 l_num_warnings := l_num_warnings + 1;
1339 --}
1340 END IF;
1341 --
1342 --}
1343 END IF;
1344
1345 IF (p_action_prms.action_code = 'DELETE' and l_del_det_id_tbl.count > 0) THEN
1346 --{
1347 l_index := l_del_det_id_tbl.FIRST;
1348 WHILE l_index IS NOT NULL
1349 LOOP
1350 --
1351 --
1352 -- Debug Statements
1353 --
1354 IF l_debug_on THEN
1355 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_GRP.DELIVERY_DETAIL_ACTION',WSH_DEBUG_SV.C_PROC_LEVEL);
1356 END IF;
1357 --
1358 wsh_container_actions.delete_containers (
1359 p_container_id => l_del_det_id_tbl(l_index),
1360 x_return_status => l_return_status);
1361 --
1362 --
1363 -- Debug Statements
1364 --
1365 IF l_debug_on THEN
1366 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1367 END IF;
1368 --
1369 wsh_util_core.api_post_call(
1370 p_return_status => l_return_status,
1371 x_num_warnings => l_num_warnings,
1372 x_num_errors => l_num_errors);
1373
1374 l_index := l_del_det_id_tbl.NEXT(l_index);
1375 --
1376 END LOOP;
1377 --
1378 --}
1379 END IF;
1380
1381 IF (g_callback_required = 'Y') THEN
1382 --{
1383 --
1384 -- Debug Statements
1385 --
1386 IF l_debug_on THEN
1387 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
1388 END IF;
1389 --
1390 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1391 (
1392 p_in_rec => l_lpn_in_sync_comm_rec,
1393 x_return_status => l_return_status,
1394 x_out_rec => l_lpn_out_sync_comm_rec
1395 );
1396 --
1397 --
1398 -- Debug Statements
1399 --
1400 IF l_debug_on THEN
1401 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1402 END IF;
1403 --
1404 WSH_UTIL_CORE.API_POST_CALL
1405 (
1406 p_return_status => l_return_status,
1407 x_num_warnings => l_num_warnings,
1408 x_num_errors => l_num_errors
1409 );
1410 --}
1411 END IF;
1412
1413
1414 IF l_num_errors > 0 THEN
1415 RAISE FND_API.G_EXC_ERROR;
1416 ELSIF l_num_warnings > 0 THEN
1417 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
1418 ELSE
1419 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1420 END IF;
1421
1422
1423 IF FND_API.To_Boolean( p_commit ) THEN
1424 commit;
1425 END IF;
1426
1427 FND_MSG_PUB.Count_And_Get (
1428 p_count => x_msg_count,
1429 p_data => x_msg_data,
1430 p_encoded => FND_API.G_FALSE);
1431
1432 --
1433 --
1434 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1435 --
1436 --}
1437 --
1438 -- Debug Statements
1439 --
1440 IF l_debug_on THEN
1441 WSH_DEBUG_SV.pop(l_module_name);
1442 END IF;
1443 --
1444 EXCEPTION
1445 --{
1446 WHEN FND_API.G_EXC_ERROR THEN
1447 ROLLBACK TO DEL_DETAIL_ACTION_WMS_GRP;
1448 g_callback_required := 'N';
1449 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1450 FND_MSG_PUB.Count_And_Get
1451 (
1452 p_count => x_msg_count,
1453 p_data => x_msg_data,
1454 p_encoded => FND_API.G_FALSE
1455 );
1456 --
1457 --
1458 -- Debug Statements
1459 --
1460 IF l_debug_on THEN
1461 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1462 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1463 END IF;
1464 --
1465 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1466 ROLLBACK TO DEL_DETAIL_ACTION_WMS_GRP;
1467 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1468
1469 FND_MSG_PUB.Count_And_Get
1470 (
1471 p_count => x_msg_count,
1472 p_data => x_msg_data,
1473 p_encoded => FND_API.G_FALSE
1474 );
1475 --
1476 --
1477 -- Debug Statements
1478 --
1479 IF l_debug_on THEN
1480 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1481 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1482 END IF;
1483 --
1484 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1485 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1486 --
1487 IF l_debug_on THEN
1488 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1489 END IF;
1490 --
1491 IF (g_callback_required = 'Y') THEN
1492 --{
1493 --
1494 -- Debug Statements
1495 --
1496 IF l_debug_on THEN
1497 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
1498 END IF;
1499 --
1500 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1501 (
1502 p_in_rec => l_lpn_in_sync_comm_rec,
1503 x_return_status => l_return_status,
1504 x_out_rec => l_lpn_out_sync_comm_rec
1505 );
1506 --
1507 --
1508 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1509 WSH_UTIL_CORE.G_RET_STS_ERROR)
1510 )
1511 THEN
1512 x_return_status := l_return_status;
1513 END IF;
1514
1515 --}
1516 END IF;
1517 --
1518 FND_MSG_PUB.Count_And_Get
1519 (
1520 p_count => x_msg_count,
1521 p_data => x_msg_data,
1522 p_encoded => FND_API.G_FALSE
1523 );
1524 --
1525 --
1526 -- Debug Statements
1527 --
1528 IF l_debug_on THEN
1529 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1530 END IF;
1531 --
1532 WHEN OTHERS THEN
1533 ROLLBACK TO DEL_DETAIL_ACTION_WMS_GRP;
1534 g_callback_required := 'N';
1535 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1536 WSH_UTIL_CORE.default_handler('WSH_INTERFACE_GRP.Delivery_Detail_Action');
1537 --
1538 --}
1539 --
1540 -- Debug Statements
1541 --
1542 IF l_debug_on THEN
1543 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1544 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1545 END IF;
1546 --
1547 END Delivery_Detail_Action;
1548
1549 --========================================================================
1550 -- PROCEDURE : Check_purge Called only by WMS APIs
1551 --
1552 -- PARAMETERS: p_api_version known api version error buffer
1553 -- p_init_msg_list FND_API.G_TRUE to reset list
1554 -- x_return_status return status
1555 -- x_msg_count number of messages in the list
1556 -- x_msg_data text of messages
1557 -- x_action_out_rec not used currenlty.
1558 -- COMMENT : Validates if the container records identified by
1559 -- p_lpn_rec.lpn_ids, are purgable. It populates the same table
1560 -- with eligible records.
1561 --========================================================================
1562
1563 PROCEDURE Check_purge
1564 (
1565 p_api_version_number IN NUMBER,
1566 p_init_msg_list IN VARCHAR2,
1567 p_commit IN VARCHAR2,
1568 x_return_status OUT NOCOPY VARCHAR2,
1569 x_msg_count OUT NOCOPY NUMBER,
1570 x_msg_data OUT NOCOPY VARCHAR2,
1571 P_lpn_rec IN OUT NOCOPY
1572 WSH_GLBL_VAR_STRCT_GRP.purgeInOutRecType
1573 )
1574 IS
1575 --
1576 l_debug_on BOOLEAN;
1577 --
1578 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME ||
1579 '.' || 'CHECK_PURGE';
1580 --
1581 l_return_status VARCHAR2(1);
1582 l_api_version_number CONSTANT NUMBER := 1.0;
1583 l_msg_count NUMBER;
1584 l_msg_data VARCHAR2(32767);
1585 l_api_name CONSTANT VARCHAR2(30):= 'check_purge';
1586 l_count NUMBER;
1587 --bmso dependency 4298071
1588
1589 Cursor c_populate_out_rec IS
1590 Select lpn_id FROM wsh_lpn_purge_tmp
1591 WHERE eligible_flag = 'Y';
1592
1593
1594 BEGIN
1595 --
1596 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1597 --
1598 IF l_debug_on IS NULL
1599 THEN
1600 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1601 END IF;
1602 --
1603 SAVEPOINT check_purge_WSHWLGPB;
1604 --
1605 IF NOT FND_API.Compatible_API_Call
1606 ( l_api_version_number,
1607 p_api_version_number,
1608 l_api_name,
1609 G_PKG_NAME
1610 )
1611 THEN
1612 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1613 END IF;
1614 --
1615 IF FND_API.to_Boolean(p_init_msg_list) THEN
1616 FND_MSG_PUB.initialize;
1617 END IF;
1618 --
1619 IF l_debug_on THEN
1620 --
1621 wsh_debug_sv.push (l_module_name);
1622 wsh_debug_sv.log (l_module_name,'lpn_ids.count',p_lpn_rec.lpn_ids.COUNT);
1623 wsh_debug_sv.log (l_module_name,'p_init_msg_list',p_init_msg_list);
1624 wsh_debug_sv.log (l_module_name,'p_commit',p_commit);
1625 --
1626 END IF;
1627 --
1628 IF p_lpn_rec.lpn_ids.COUNT > 0 THEN --{
1629
1630 BEGIN
1631 SELECT 1 INTO l_count
1632 FROM wsh_lpn_purge_tmp;
1633 EXCEPTION
1634 WHEN NO_DATA_FOUND THEN
1635 l_count := 0;
1636 END;
1637
1638 IF l_debug_on THEN
1639 wsh_debug_sv.log (l_module_name,'l_count',l_count);
1640 END IF;
1641
1642 IF l_count > 0 THEN
1643 DELETE FROM wsh_lpn_purge_tmp;
1644 END IF;
1645
1646 FORALL I IN p_lpn_rec.lpn_ids.FIRST..p_lpn_rec.lpn_ids.LAST
1647 INSERT INTO wsh_lpn_purge_tmp
1648 ( lpn_id,
1649 ELIGIBLE_FLAG
1650 )VALUES(
1651 P_lpn_rec.lpn_ids(i),
1652 'Y'
1653 );
1654
1655 Update wsh_lpn_purge_tmp
1656 Set eligible_flag = 'N' where
1657 Lpn_id in (
1658 Select wt.lpn_id from
1659 Wsh_lpn_purge_tmp wt, wsh_delivery_details wdd
1660 Where wt.lpn_id = wdd.lpn_id
1661 And nvl(wt.ELIGIBLE_FLAG ,'Y') = 'Y'
1662 And NVL(wdd.line_direction,'O') IN ('IO','O')
1663 And wdd.released_status <> 'C');
1664
1665 IF l_debug_on THEN
1666 wsh_debug_sv.log (l_module_name,'Rows updated',SQL%rowcount);
1667 END IF;
1668
1669 Update wsh_lpn_purge_tmp
1670 Set ELIGIBLE_FLAG = 'N'
1671 WHERE
1672 Lpn_id in (select wt.lpn_id
1673 FROM wms_lpn_histories wlh,
1674 wsh_inbound_txn_history wth,
1675 wsh_lpn_purge_tmp wt
1676 where wlh.parent_lpn_id = wt.lpn_id
1677 and nvl(wt.ELIGIBLE_FLAG,'Y') = 'Y'
1678 and wlh.source_type_id = 1
1679 AND wlh.lpn_context = 7
1680 and wlh.source_header_id = wth.shipment_header_id
1681 and wth.transaction_type in ('RECEIPT', 'ASN'));
1682
1683 IF l_debug_on THEN
1684 wsh_debug_sv.log (l_module_name,'Rows updated',SQL%rowcount);
1685 END IF;
1686
1687 UPDATE wsh_delivery_details
1688 SET lpn_id = NULL WHERE
1689 lpn_id IN (SELECT LPN_ID FROM wsh_lpn_purge_tmp
1690 WHERE eligible_flag = 'Y');
1691
1692 Open c_populate_out_rec;
1693 FETCH c_populate_out_rec BULK COLLECT INTO p_lpn_rec.lpn_ids;
1694 CLOSE c_populate_out_rec;
1695
1696 IF l_debug_on THEN
1697 wsh_debug_sv.log (l_module_name,'lpn_ids.COUNT',
1698 p_lpn_rec.lpn_ids.COUNT);
1699 END IF;
1700
1701 END IF; --}
1702
1703 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1704
1705 FND_MSG_PUB.Count_And_Get (
1706 p_count => x_msg_count,
1707 p_data => x_msg_data,
1708 p_encoded => FND_API.G_FALSE);
1709 --
1710 IF l_debug_on THEN
1711 WSH_DEBUG_SV.pop(l_module_name);
1712 END IF;
1713 --
1714 EXCEPTION
1715
1716 WHEN OTHERS THEN
1717 ROLLBACK TO check_purge_WSHWLGPB;
1718 --p_lpn_rec.lpn_ids.DELETE;
1719 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1720 wsh_util_core.default_handler('WSH_WMS_LPN_GRP.check_purge');
1721 FND_MSG_PUB.Count_And_Get
1722 (
1723 p_count => x_msg_count,
1724 p_data => x_msg_data,
1725 p_encoded => FND_API.G_FALSE
1726 );
1727
1728 --
1729 IF l_debug_on THEN
1730 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1731 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1732 END IF;
1733 --
1734 END Check_purge;
1735
1736
1737 --***************************************************************************--
1738 --========================================================================
1739 -- PROCEDURE : is_valid_consol
1740 --
1741 -- PARAMETERS: p_init_msg_list FND_API.G_TRUE to reset list
1742 -- p_input_delivery_id_tab Table of delivery records to process
1743 --
1744 -- p_target_consol_delivery_id Table of delivery ids to process
1745 -- x_deconsolidation_location deconsolidation location
1746 -- x_msg_count Number of messages in the list
1747 -- x_msg_data Text of messages
1748 -- x_return_status Return status
1749 -- COMMENT : This procedure is to find if a set of deliveries can be assigned to a consol delivery.
1750 -- This procedure is called from WMS.
1751 --
1752 --========================================================================
1753 PROCEDURE is_valid_consol( p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
1754 p_input_delivery_id_tab IN WSH_UTIL_CORE.id_tab_type,
1755 p_target_consol_delivery_id IN NUMBER,
1756 p_caller IN VARCHAR2 DEFAULT NULL,
1757 x_deconsolidation_location OUT NOCOPY NUMBER,
1758 x_return_status OUT NOCOPY VARCHAR2,
1759 x_msg_count OUT NOCOPY NUMBER,
1760 x_msg_data OUT NOCOPY VARCHAR2
1761 ) IS
1762
1763 cursor c_parent_info(p_del_id in NUMBER) is
1764 select s.trip_id, l2.delivery_id
1765 from wsh_trip_stops s,
1766 wsh_delivery_legs l1,
1767 wsh_delivery_legs l2,
1768 wsh_new_deliveries d
1769 where d.initial_pickup_location_id = s.stop_location_id
1770 and l1.delivery_id = d.delivery_id
1771 and l1.pick_up_stop_id = s.stop_id
1772 and d.delivery_id = p_del_id
1773 and l1.parent_delivery_leg_id = l2.delivery_leg_id(+);
1774
1775 cursor c_pickup_trip(p_del_id in NUMBER) is
1776 select s.trip_id
1777 from wsh_trip_stops s,
1778 wsh_delivery_legs l,
1779 wsh_new_deliveries d
1780 where d.initial_pickup_location_id = s.stop_location_id
1781 and l.delivery_id = d.delivery_id
1782 and l.pick_up_stop_id = s.stop_id
1783 and d.delivery_id = p_del_id;
1784
1785
1786 l_temp_trip_id NUMBER := NULL;
1787 l_consol_trip_id NUMBER := NULL;
1788 l_temp_parent_del_id NUMBER := NULL;
1789 l_parent_del_id NUMBER := NULL;
1790 l_debug_on BOOLEAN;
1791 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'IS_VALID_CONSOL';
1792
1793 WSH_INVALID_DECONSOL_POINT EXCEPTION;
1794 WSH_INVALID_PARENT EXCEPTION;
1795 WSH_INVALID_TRIP EXCEPTION;
1796
1797 BEGIN
1798
1799 --
1800 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1801 --
1802 IF l_debug_on IS NULL
1803 THEN
1804 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1805 END IF;
1806 --
1807 IF l_debug_on THEN
1808 --
1809 wsh_debug_sv.push (l_module_name);
1810 wsh_debug_sv.log (l_module_name,'p_input_delivery_id_tab.count',p_input_delivery_id_tab.COUNT);
1811 wsh_debug_sv.log (l_module_name,'p_target_consol_delivery_id',p_target_consol_delivery_id);
1812 --
1813 END IF;
1814 --
1815
1816
1817
1818 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1819
1820 -- 1. Check if the deliveries are not on diff. pickup trips
1821 -- and diff. consol deliveries.
1822
1823 FOR i in 1..p_input_delivery_id_tab.count LOOP
1824
1825 OPEN c_parent_info(p_input_delivery_id_tab(i));
1826 FETCH c_parent_info
1827 INTO l_temp_trip_id, l_temp_parent_del_id;
1828 CLOSE c_parent_info;
1829
1830 IF l_debug_on THEN
1831 --
1832 wsh_debug_sv.logmsg (l_module_name,'----------------------------------');
1833 wsh_debug_sv.log (l_module_name,'p_input_delivery_id_tab-->',p_input_delivery_id_tab(i));
1834 wsh_debug_sv.log (l_module_name,'l_temp_trip_id-->',l_temp_trip_id);
1835 wsh_debug_sv.log (l_module_name,'l_temp_parent_del_id-->',l_temp_parent_del_id);
1836 wsh_debug_sv.log (l_module_name,'l_consol_trip_id-->',l_consol_trip_id);
1837 wsh_debug_sv.log (l_module_name,'l_temp_parent_del_id-->',l_temp_parent_del_id);
1838 wsh_debug_sv.log (l_module_name,'l_parent_del_id-->',l_parent_del_id);
1839 wsh_debug_sv.logmsg (l_module_name,'----------------------------------');
1840 --
1841 END IF;
1842
1843 IF l_temp_trip_id IS NOT NULL AND l_consol_trip_id IS NULL THEN
1844 l_consol_trip_id := l_temp_trip_id;
1845 ELSIF l_temp_trip_id <> l_consol_trip_id THEN
1846 RAISE WSH_INVALID_TRIP;
1847 END IF;
1848
1849 IF l_temp_parent_del_id IS NOT NULL AND l_parent_del_id IS NULL THEN
1850 l_parent_del_id := l_temp_parent_del_id;
1851 ELSIF l_temp_parent_del_id <> l_parent_del_id THEN
1852 RAISE WSH_INVALID_PARENT;
1853 END IF;
1854 END LOOP;
1855
1856 IF l_debug_on THEN
1857 --
1858 wsh_debug_sv.logmsg (l_module_name,'----------------------------------');
1859 wsh_debug_sv.log (l_module_name,'AFTER THE LOOP,l_consol_trip_id-->',l_consol_trip_id);
1860 wsh_debug_sv.log (l_module_name,'AFTER THE LOOP,l_parent_del_id-->',l_parent_del_id);
1861 --
1862 END IF;
1863
1864 IF p_target_consol_delivery_id IS NOT NULL THEN
1865 OPEN c_pickup_trip(p_target_consol_delivery_id);
1866 FETCH c_pickup_trip
1867 INTO l_temp_trip_id;
1868 CLOSE c_pickup_trip;
1869
1870 IF l_debug_on THEN
1871 --
1872 wsh_debug_sv.log (l_module_name,'l_temp_trip_id-->',l_temp_trip_id);
1873 wsh_debug_sv.log (l_module_name,'l_consol_trip_id-->',l_consol_trip_id);
1874 --
1875 END IF;
1876
1877 IF l_temp_trip_id <> l_consol_trip_id THEN
1878 RAISE WSH_INVALID_TRIP;
1879 END IF;
1880 END IF;
1881 IF l_parent_del_id <> p_target_consol_delivery_id THEN
1882 RAISE WSH_INVALID_PARENT;
1883 END IF;
1884
1885 -- 2. Check if deconsol points match.
1886
1887 WSH_FTE_COMP_CONSTRAINT_GRP.is_valid_consol
1888 ( p_init_msg_list => p_init_msg_list,
1889 p_input_delivery_id_tab => p_input_delivery_id_tab,
1890 p_target_consol_delivery_id => p_target_consol_delivery_id,
1891 p_caller => p_caller,
1892 x_deconsolidation_location => x_deconsolidation_location,
1893 x_return_status => x_return_status,
1894 x_msg_count => x_msg_count,
1895 x_msg_data => x_msg_data
1896 );
1897
1898 IF x_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
1899 RAISE WSH_INVALID_DECONSOL_POINT;
1900 END IF;
1901
1902 IF l_debug_on THEN
1903 WSH_DEBUG_SV.pop(l_module_name);
1904 END IF;
1905
1906 EXCEPTION
1907
1908 WHEN WSH_INVALID_PARENT THEN
1909 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1910 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_PARENT');
1911 WSH_UTIL_CORE.Add_Message(x_return_status);
1912 --
1913 -- Debug Statements
1914 --
1915 IF l_debug_on THEN
1916 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_PARENT exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1917
1918 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_PARENT');
1919 END IF;
1920 --
1921 WHEN WSH_INVALID_TRIP THEN
1922 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1923 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_TRIP');
1924 WSH_UTIL_CORE.Add_Message(x_return_status);
1925 --
1926 -- Debug Statements
1927 --
1928 IF l_debug_on THEN
1929 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_TRIP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1930
1931 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_TRIP');
1932 END IF;
1933
1934 WHEN WSH_INVALID_DECONSOL_POINT THEN
1935 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1936 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_DECONSOL_POINT');
1937 WSH_UTIL_CORE.Add_Message(x_return_status);
1938 --
1939 -- Debug Statements
1940 --
1941 IF l_debug_on THEN
1942 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_DECONSOL_POINT exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1943
1944 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_DECONSOL_POINT');
1945 END IF;
1946
1947 WHEN OTHERS THEN
1948 wsh_util_core.default_handler('wsh_new_delivery_actions.Unassign_Dels_from_Consol_Del',l_module_name);
1949 --
1950 IF l_debug_on THEN
1951 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:OTHERS');
1952 END IF;
1953
1954
1955 END is_valid_consol;
1956
1957 END WSH_WMS_LPN_GRP;