1 PACKAGE BODY WSH_LPN_SYNC_COMM_PKG as
2 /* $Header: WSHLSCMB.pls 120.5.12010000.2 2009/03/10 08:33:15 ueshanka ship $ */
3
4
5
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_LPN_SYNC_COMM_PKG';
8 --
9 PROCEDURE SYNC_LPNS_TO_WMS
10 (
11 p_in_rec IN WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type,
12 x_return_status OUT NOCOPY VARCHAR2,
13 x_out_rec OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type
14 )
15 IS
16 --{
17 l_return_status VARCHAR2(10);
18 l_msg_count NUMBER;
19 l_msg_data VARCHAR2(32767);
20 l_num_warnings NUMBER :=0;
21 l_num_errors NUMBER :=0;
22
23 l_current_hw_time_stamp DATE;
24 l_original_call_grp_api VARCHAR2(2);
25 l_original_update_to_cnt VARCHAR2(2);
26
27 cursor l_unpack_csr (p_hw_time_stamp IN DATE) is
28 select wddc.lpn_id,
29 wddp.lpn_id old_parent_lpn_id
30 from wsh_delivery_details wddc,
31 wsh_delivery_details wddp,
32 wsh_delivery_assignments_v wda,
33 wsh_wms_sync_tmp wlst
34 where wlst.delivery_detail_id = wda.delivery_detail_id
35 and wddc.delivery_detail_id = wda.delivery_detail_id
36 and wlst.parent_delivery_detail_id = wddp.delivery_detail_id
37 and nvl(wda.parent_delivery_detail_id, -1) <> nvl(wlst.parent_delivery_detail_id,-1)
38 and wlst.parent_delivery_detail_id is not null
39 and wlst.operation_type = 'PRIOR'
40 and wddc.lpn_id is not null
41 and wddp.lpn_id is not null
42 and wlst.creation_date = p_hw_time_stamp
43 order by
44 wlst.parent_delivery_detail_id;
45
46 l_curr_unpack_parent_lpn_id NUMBER;
47 l_prev_unpack_parent_lpn_id NUMBER;
48 l_unpack_lpn_id_tbl wsh_util_core.id_tab_type;
49
50 cursor l_pack_csr (p_hw_time_stamp IN DATE) is
51 select wddc.lpn_id,
52 wddp.lpn_id parent_lpn_id
53 from wsh_delivery_details wddc,
54 wsh_delivery_details wddp,
55 wsh_delivery_assignments_v wda,
56 wsh_wms_sync_tmp wlst
57 where wlst.delivery_detail_id = wda.delivery_detail_id
58 and wddc.delivery_detail_id = wda.delivery_detail_id
59 and wda.parent_delivery_detail_id = wddp.delivery_detail_id
60 and nvl(wda.parent_delivery_detail_id, -1) <> nvl(wlst.parent_delivery_detail_id,-1)
61 and wda.parent_delivery_detail_id is not null
62 and wddc.lpn_id is not null
63 and wddp.lpn_id is not null
64 and wlst.operation_type = 'PRIOR'
65 and wlst.creation_date = p_hw_time_stamp
66 order by
67 wda.parent_delivery_detail_id;
68
69 l_curr_pack_parent_lpn_id NUMBER;
70 l_prev_pack_parent_lpn_id NUMBER;
71 l_pack_lpn_id_tbl wsh_util_core.id_tab_type;
72
73 cursor l_unassign_csr (p_hw_time_stamp IN DATE) is
74 select wdd.lpn_id,
75 wlst.delivery_id old_delivery_id
76 from wsh_delivery_details wdd,
77 wsh_delivery_assignments_v wda,
78 wsh_wms_sync_tmp wlst
79 where wlst.delivery_detail_id = wda.delivery_detail_id
80 and wdd.delivery_detail_id = wda.delivery_detail_id
81 and nvl(wda.delivery_id, -1) <> nvl(wlst.delivery_id,-1)
82 and wlst.delivery_id is not null
83 and wdd.lpn_id is not null
84 and wlst.operation_type = 'PRIOR'
85 and wlst.creation_date = p_hw_time_stamp
86 order by
87 wlst.delivery_id;
88
89
90 l_curr_unasgn_del_id NUMBER;
91 l_prev_unasgn_del_id NUMBER;
92 l_unasgn_lpn_id_tbl wsh_util_core.id_tab_type;
93
94 cursor l_assign_csr (p_hw_time_stamp IN DATE) is
95 select wdd.lpn_id,
96 wda.delivery_id new_delivery_id
97 from wsh_delivery_details wdd,
98 wsh_delivery_assignments_v wda,
99 wsh_wms_sync_tmp wlst
100 where wlst.delivery_detail_id = wda.delivery_detail_id
101 and wdd.delivery_detail_id = wda.delivery_detail_id
102 and nvl(wda.delivery_id, -1) <> nvl(wlst.delivery_id,-1)
103 and wda.delivery_id is not null
104 and wdd.lpn_id is not null
105 and wlst.operation_type = 'PRIOR'
106 and wlst.creation_date = p_hw_time_stamp
107 order by
108 wda.delivery_id;
109
110 l_curr_asgn_del_id NUMBER;
111 l_prev_asgn_del_id NUMBER;
112 l_asgn_lpn_id_tbl wsh_util_core.id_tab_type;
113
114 cursor l_update_csr (p_hw_time_stamp IN DATE) is
115 select wdd.lpn_id,
116 wlst.delivery_detail_id,
117 wdd.container_name,
118 wdd.inventory_item_id,
119 wdd.organization_id,
120 wdd.subinventory,
121 wdd.locator_id,
122 wdd.gross_weight,
123 wdd.volume_uom_code,
124 wdd.filled_volume,
125 wdd.volume,
126 wdd.weight_uom_code,
127 wdd.net_weight
128 from wsh_delivery_details wdd,
129 wsh_wms_sync_tmp wlst
130 where wlst.delivery_detail_id = wdd.delivery_detail_id
131 and wdd.lpn_id is not null
132 and wlst.operation_type = 'UPDATE'
133 and wlst.creation_date = p_hw_time_stamp;
134
135 l_current_parent_detail_id NUMBER;
136 i NUMBER;
137 l_prev_parent_detail_id NUMBER;
138 l_wms_lpn_tbl WMS_DATA_TYPE_DEFINITIONS_PUB.LPNTableType;
139 l_old_date_wm constant date := to_date('01-01-1901 00:00:00', 'DD-MM-YYYY HH24:MI:SS');
140
141 l_tmp_tbl_size NUMBER;
142 --}
143 --
144 l_debug_on BOOLEAN;
145 --
146 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SYNC_LPNS_TO_WMS';
147 --
148 BEGIN
149 --{
150 --
151 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
152 --
153 IF l_debug_on IS NULL
154 THEN
155 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
156 END IF;
157 --
158 --
159 -- Debug Statements
160 --
161 IF l_debug_on THEN
162 WSH_DEBUG_SV.push(l_module_name);
163 END IF;
164 --
165 -- Setting the return status in the begining
166 x_return_status := wsh_util_core.g_ret_sts_success;
167 --
168 IF l_debug_on THEN
169 WSH_DEBUG_SV.log(l_module_name,'wsh_wms_lpn_grp.g_hw_time_stamp', wsh_wms_lpn_grp.g_hw_time_stamp);
170 END IF;
171 --
172 IF ( wsh_wms_lpn_grp.g_hw_time_stamp is null ) THEN
173 wsh_wms_lpn_grp.g_hw_time_stamp := sysdate;
174 END IF;
175 --
176 l_current_hw_time_stamp := wsh_wms_lpn_grp.g_hw_time_stamp;
177 l_original_call_grp_api := wsh_wms_lpn_grp.g_call_group_api;
178
179 -- This is not required any more as g_hw_time_stamp will always be initialized
180 --IF (l_current_hw_time_stamp IS NULL) THEN
181 --{
182 -- l_current_hw_time_stamp := l_old_date_wm;
183 --}
184 --END IF;
185 -- Logic for 'UNPACK'
186 i := 1;
187 --
188 IF l_debug_on THEN
189 WSH_DEBUG_SV.log(l_module_name,'l_current_hw_time_stamp', l_current_hw_time_stamp);
190 END IF;
191
192 -- Bug 8314220: Moved following query out from debug
193 select count(*) into l_tmp_tbl_size from wsh_wms_sync_tmp
194 where creation_date = l_current_hw_time_stamp;
195 --
196 IF l_debug_on THEN
197 WSH_DEBUG_SV.log(l_module_name,'Count of wsh_wms_sync_tmp table is', l_tmp_tbl_size);
198 END IF;
199
200 -- Bug 8314220: Execute following code only if there are any records in wsh_wms_sync_tmp table.
201 IF l_tmp_tbl_size > 0 THEN
202 --{
203 -- We are doing the following update because, if a particular went through various
204 -- steps of for example, update, assign, unassign, and was finally deleted, then
205 -- we do not want to call WMS for these operations
206 update wsh_wms_sync_tmp
207 set operation_type = 'DELETE'
208 where delivery_detail_id in (select delivery_detail_id
209 from wsh_wms_sync_tmp
210 where operation_type = 'DELETE'
211 and creation_date = l_current_hw_time_stamp)
212 and operation_type = 'UPDATE'
213 and creation_date = l_current_hw_time_stamp;
214
215 IF l_debug_on THEN
216 WSH_DEBUG_SV.log(l_module_name,'Count of rows updated is', SQL%ROWCOUNT);
217 END IF;
218
219 FOR unpack_rec in l_unpack_csr(l_current_hw_time_stamp) LOOP
220 --{
221 --
222 l_curr_unpack_parent_lpn_id := unpack_rec.old_parent_lpn_id;
223
224 IF (i = 1 OR
225 nvl(l_prev_unpack_parent_lpn_id,-99) = nvl(l_curr_unpack_parent_lpn_id,-99)
226 )
227 THEN
228 --
229 l_unpack_lpn_id_tbl(i) := unpack_rec.lpn_id;
230 --
231 ELSE
232 --
233 IF l_debug_on THEN
234 WSH_DEBUG_SV.log(l_module_name,'Count of l_unpack_lpn_id_tbl is', l_unpack_lpn_id_tbl.count);
235 WSH_DEBUG_SV.log(l_module_name,'old parent lpn id is', l_prev_unpack_parent_lpn_id);
236 END IF;
237 -- This infrastructure is built for future so that when
238 -- WMS is interested in getting this information, we
239 -- need to make a call at this point
240 -- need to set the WSH_WMS_LPN_GRP.g_call_group_api := 'N';
241 -- before calling WMS
242 l_unpack_lpn_id_tbl.delete;
243 i := 1;
244 --
245 END IF;
246 l_prev_unpack_parent_lpn_id := l_curr_unpack_parent_lpn_id;
247 i := i + 1;
248 --
249 --}
250 END LOOP;
251 --
252 -- Need to make one extra call to WMS for UNPACK to take care of the last set of lpns
253 -- in the l_unpack_lpn_id_tbl after teh loop
254 --
255 IF l_debug_on THEN
256 WSH_DEBUG_SV.log(l_module_name,'Count of l_unpack_lpn_id_tbl is', l_unpack_lpn_id_tbl.count);
257 WSH_DEBUG_SV.log(l_module_name,'new parent lpn id is', l_prev_unpack_parent_lpn_id);
258 END IF;
259
260 -- Logic for 'PACK'
261 i := 1;
262
263 FOR pack_rec in l_pack_csr(l_current_hw_time_stamp) LOOP
264 --{
265 --
266 l_curr_pack_parent_lpn_id := pack_rec.parent_lpn_id;
267
268 IF (i = 1 OR
269 nvl(l_prev_pack_parent_lpn_id,-99) = nvl(l_curr_pack_parent_lpn_id,-99)
270 )
271 THEN
272 --
273 l_pack_lpn_id_tbl(i) := pack_rec.lpn_id;
274 --
275 ELSE
276 --
277 IF l_debug_on THEN
278 WSH_DEBUG_SV.log(l_module_name,'Count of l_pack_lpn_id_tbl is', l_pack_lpn_id_tbl.count);
279 WSH_DEBUG_SV.log(l_module_name,'new parent lpn id is', l_prev_pack_parent_lpn_id);
280 END IF;
281 -- This infrastructure is built for future so that when
282 -- WMS is interested in getting this information, we
283 -- need to make a call at this point
284 -- need to set the WSH_WMS_LPN_GRP.g_call_group_api := 'N';
285 -- before calling WMS
286 l_pack_lpn_id_tbl.delete;
287 i := 1;
288 --
289 END IF;
290 l_prev_pack_parent_lpn_id := l_curr_pack_parent_lpn_id;
291 i := i + 1;
292 --
293 --}
294 END LOOP;
295 --
296 -- Need to make one extra call to WMS for PACK to take care of the last set of lpns
297 -- in the l_pack_lpn_id_tbl after teh loop
298 --
299 IF l_debug_on THEN
300 WSH_DEBUG_SV.log(l_module_name,'Count of l_pack_lpn_id_tbl is', l_pack_lpn_id_tbl.count);
301 WSH_DEBUG_SV.log(l_module_name,'new parent lpn id is', l_prev_pack_parent_lpn_id);
302 END IF;
303
304 -- Logic for 'UNASSIGN'
305 i := 1;
306 FOR unasgn_rec in l_unassign_csr(l_current_hw_time_stamp) LOOP
307 --{
308 --
309 l_curr_unasgn_del_id := unasgn_rec.old_delivery_id;
310
311 IF (i = 1 OR
312 nvl(l_prev_unasgn_del_id,-99) = nvl(l_curr_unasgn_del_id,-99)
313 )
314 THEN
315 --
316 l_unasgn_lpn_id_tbl(i) := unasgn_rec.lpn_id;
317 --
318 ELSE
319 --
320 IF l_debug_on THEN
321 WSH_DEBUG_SV.log(l_module_name,'Count of l_unasgn_lpn_id_tbl is', l_unasgn_lpn_id_tbl.count);
322 WSH_DEBUG_SV.log(l_module_name,'old delivery id is', l_prev_unasgn_del_id);
323 END IF;
324 -- This infrastructure is built for future so that when
325 -- WMS is interested in getting this information, we
326 -- need to make a call at this point
327 -- need to set the WSH_WMS_LPN_GRP.g_call_group_api := 'N';
328 -- before calling WMS
329 l_unasgn_lpn_id_tbl.delete;
330 i := 1;
331 --
332 END IF;
333 l_prev_unasgn_del_id := l_curr_unasgn_del_id;
334 i := i + 1;
335 --
336 --}
337 END LOOP;
338 --
339 -- Need to make one extra call to WMS for UNASSIGN to take care of the last set of lpns
340 -- in the l_unasgn_lpn_id_tbl after the loop
341 --
342 IF l_debug_on THEN
343 WSH_DEBUG_SV.log(l_module_name,'Count of l_unasgn_lpn_id_tbl is', l_unasgn_lpn_id_tbl.count);
344 WSH_DEBUG_SV.log(l_module_name,'new parent lpn id is', l_prev_unasgn_del_id);
345 END IF;
346
347 -- Logic for 'UNASSIGN'
348 i := 1;
349
350 FOR assign_rec in l_assign_csr(l_current_hw_time_stamp) LOOP
351 --{
352 --
353 l_curr_asgn_del_id := assign_rec.new_delivery_id;
354
355 IF (i = 1 OR
356 nvl(l_prev_asgn_del_id,-99) = nvl(l_curr_asgn_del_id,-99)
357 )
358 THEN
359 --
360 l_asgn_lpn_id_tbl(i) := assign_rec.lpn_id;
361 --
362 ELSE
363 --
364 IF l_debug_on THEN
365 WSH_DEBUG_SV.log(l_module_name,'Count of l_asgn_lpn_id_tbl is', l_asgn_lpn_id_tbl.count);
366 WSH_DEBUG_SV.log(l_module_name,'new delivery id is', l_prev_asgn_del_id);
367 END IF;
368 -- This infrastructure is built for future so that when
369 -- WMS is interested in getting this information, we
370 -- need to make a call at this point
371 -- need to set the WSH_WMS_LPN_GRP.g_call_group_api := 'N';
372 -- before calling WMS
373 l_asgn_lpn_id_tbl.delete;
374 i := 1;
375 --
376 END IF;
377 l_prev_asgn_del_id := l_curr_asgn_del_id;
378 i := i + 1;
379 --
380 --}
381 END LOOP;
382 --
383 -- Need to make one extra call to WMS for ASSIGN to take care of the last set of lpns
384 -- in the l_asgn_lpn_id_tbl after the loop
385 --
386 IF l_debug_on THEN
387 WSH_DEBUG_SV.log(l_module_name,'Count of l_asgn_lpn_id_tbl is', l_asgn_lpn_id_tbl.count);
388 WSH_DEBUG_SV.log(l_module_name,'new parent lpn id is', l_prev_asgn_del_id);
389 END IF;
390
391 i := 1;
392 FOR update_rec in l_update_csr(l_current_hw_time_stamp) LOOP
393 --{
394 IF l_debug_on THEN
395 WSH_DEBUG_SV.log(l_module_name,'Inside the loop i is', i);
396 WSH_DEBUG_SV.log(l_module_name,'Inside the loop delivery_detail_id is', update_rec.delivery_detail_id);
397 WSH_DEBUG_SV.log(l_module_name,'Inside the loop lpn_id is', update_rec.lpn_id);
398 END IF;
399
400 IF (update_rec.lpn_id IS NOT NULL) THEN
401 l_wms_lpn_tbl(i).LPN_ID := update_rec.lpn_id;
402 l_wms_lpn_tbl(i).LICENSE_PLATE_NUMBER := update_rec.container_name;
403 l_wms_lpn_tbl(i).INVENTORY_ITEM_ID := update_rec.inventory_item_id;
404 l_wms_lpn_tbl(i).ORGANIZATION_ID := update_rec.ORGANIZATION_ID;
405 l_wms_lpn_tbl(i).SUBINVENTORY_CODE := update_rec.SUBINVENTORY;
406 l_wms_lpn_tbl(i).LOCATOR_ID := update_rec.LOCATOR_ID;
407 l_wms_lpn_tbl(i).GROSS_WEIGHT_UOM_CODE := update_rec.weight_uom_code;
408 l_wms_lpn_tbl(i).GROSS_WEIGHT := update_rec.gross_weight;
409 l_wms_lpn_tbl(i).CONTAINER_VOLUME_UOM := update_rec.volume_uom_code;
410 l_wms_lpn_tbl(i).CONTAINER_VOLUME := update_rec.volume;
411 l_wms_lpn_tbl(i).CONTENT_VOLUME_UOM_CODE := update_rec.volume_uom_code;
412 l_wms_lpn_tbl(i).CONTENT_VOLUME := update_rec.filled_volume;
413 l_wms_lpn_tbl(i).TARE_WEIGHT_UOM_CODE := update_rec.weight_uom_code;
414 l_wms_lpn_tbl(i).TARE_WEIGHT := (update_rec.gross_weight - update_rec.net_weight);
415 i := i + 1;
416 END IF;
417 --}
418 END LOOP;
419 --}
420 END IF;
421 --
422 IF l_debug_on THEN
423 WSH_DEBUG_SV.log(l_module_name,'count of plsql table l_wms_lpn_tbl is', l_wms_lpn_tbl.count);
424 END IF;
425 --
426 IF ( l_wms_lpn_tbl.count > 0 ) THEN
427 --{
428 -- setting the globals appropriately before calling WMS.
429 WSH_WMS_LPN_GRP.g_call_group_api := 'N';
430
431 IF l_debug_on THEN
432 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_CONTAINER_GRP.MODIFY_LPNS',WSH_DEBUG_SV.C_PROC_LEVEL);
433 END IF;
434
435 WMS_Container_GRP.Modify_LPNs (
436 p_api_version => 1.0,
437 p_init_msg_list => FND_API.G_FALSE,
438 p_commit => FND_API.G_FALSE,
439 x_return_status => l_return_status,
440 x_msg_count => l_msg_count,
441 x_msg_data => l_msg_data,
442 p_caller => 'WSH_WMS_SYNC_TMP_PKG',
443 p_lpn_table => l_wms_lpn_tbl
444 );
445 -- resetting the values back to the original values
446 WSH_WMS_LPN_GRP.g_call_group_api := l_original_call_grp_api;
447
448 IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
449 wsh_wms_lpn_grp.g_update_to_containers := 'N';
450 END IF;
451
452 --
453 -- Debug Statements
454 --
455 IF l_debug_on THEN
456 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
457 END IF;
458 --
459 wsh_util_core.api_post_call(
460 p_return_status => l_return_status,
461 x_num_warnings => l_num_warnings,
462 x_num_errors => l_num_errors,
463 p_msg_data => l_msg_data
464 );
465
466 --}
467 END IF;
468
469 wsh_wms_lpn_grp.g_hw_time_stamp := wsh_wms_lpn_grp.g_hw_time_stamp + 1/86400;
470
471 IF l_num_errors > 0 THEN
472 RAISE FND_API.G_EXC_ERROR;
473 ELSIF l_num_warnings > 0 THEN
474 x_return_status := wsh_util_core.g_ret_sts_warning;
475 END IF;
476
477 --}
478 --
479 -- Debug Statements
480 --
481 IF l_debug_on THEN
482 WSH_DEBUG_SV.pop(l_module_name);
483 END IF;
484 --
485 EXCEPTION
486 --{
487 WHEN FND_API.G_EXC_ERROR THEN
488 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
489 WSH_WMS_LPN_GRP.g_call_group_api := l_original_call_grp_api;
490 IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
491 wsh_wms_lpn_grp.g_update_to_containers := 'N';
492 END IF;
493 --
494 --
495 --
496 -- Debug Statements
497 --
498 IF l_debug_on THEN
499 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
500 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
501 END IF;
502 --
503 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
504 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
505 WSH_WMS_LPN_GRP.g_call_group_api := l_original_call_grp_api;
506 IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
507 wsh_wms_lpn_grp.g_update_to_containers := 'N';
508 END IF;
509 --
510 --
511 --
512 -- Debug Statements
513 --
514 IF l_debug_on THEN
515 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
516 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
517 END IF;
518 --
519 WHEN OTHERS THEN
520 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
521 wsh_util_core.default_handler('WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_module_name);
522 WSH_WMS_LPN_GRP.g_call_group_api := l_original_call_grp_api;
523 IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
524 wsh_wms_lpn_grp.g_update_to_containers := 'N';
525 END IF;
526 --
527 -- Debug Statements
528 --
529 IF l_debug_on THEN
530 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
531 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
532 END IF;
533 --
534 --}
535 END SYNC_LPNS_TO_WMS;
536
537 -- This procedure is used to synchronize the updates on LPNs in WSH
538 -- to WMS due to proration logic
539 PROCEDURE SYNC_PRORATED_LPNS_TO_WMS
540 (
541 p_in_rec IN WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type,
542 x_return_status OUT NOCOPY VARCHAR2,
543 x_out_rec OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type
544 )
545 IS
546 --{
547 --
548 cursor l_pr_update_csr (p_hw_time_stamp IN DATE) is
549 select wdd.lpn_id,
550 wlst.delivery_detail_id,
551 wdd.container_name,
552 wdd.inventory_item_id,
553 wdd.organization_id,
554 wdd.subinventory,
555 wdd.locator_id,
556 wdd.gross_weight,
557 wdd.volume_uom_code,
558 wdd.filled_volume,
559 wdd.volume,
560 wdd.weight_uom_code,
561 wdd.net_weight,
562 wlst.call_level
563 from wsh_delivery_details wdd,
564 wsh_wms_sync_tmp wlst
565 where wlst.delivery_detail_id = wdd.delivery_detail_id
566 and wdd.lpn_id is not null
567 and wlst.operation_type = 'UPDATE'
568 and wlst.creation_date = p_hw_time_stamp
569 order by nvl(wlst.call_level,0) desc;
570
571 l_current_hw_time_stamp DATE;
572 l_original_call_grp_api VARCHAR2(2);
573 l_call_level NUMBER := 0;
574 l_prev_call_level NUMBER := 0;
575 l_wms_lpn_tbl WMS_DATA_TYPE_DEFINITIONS_PUB.LPNTableType;
576 l_return_status VARCHAR2(10);
577 l_msg_count NUMBER;
578 l_msg_data VARCHAR2(32767);
579 l_num_warnings NUMBER :=0;
580 l_num_errors NUMBER :=0;
581
582 i NUMBER;
583 l_loopCounter NUMBER;
584 --
585 l_debug_on BOOLEAN;
586 --
587 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SYNC_PRORATED_LPNS_TO_WMS';
588 --
589 --}
590 BEGIN
591 --{
592 --
593 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
594 --
595 IF l_debug_on IS NULL
596 THEN
597 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
598 END IF;
599 --
600 --
601 -- Debug Statements
602 --
603 IF l_debug_on THEN
604 WSH_DEBUG_SV.push(l_module_name);
605 END IF;
606 --
607 -- Setting the return status in the begining
608 x_return_status := wsh_util_core.g_ret_sts_success;
609 --
610 IF ( wsh_wms_lpn_grp.g_hw_time_stamp is null ) THEN
611 wsh_wms_lpn_grp.g_hw_time_stamp := sysdate;
612 END IF;
613
614 l_current_hw_time_stamp := wsh_wms_lpn_grp.g_hw_time_stamp;
615 l_original_call_grp_api := wsh_wms_lpn_grp.g_call_group_api;
616
617 IF l_debug_on THEN
618 WSH_DEBUG_SV.log(l_module_name,'wsh_wms_lpn_grp.g_hw_time_stamp', wsh_wms_lpn_grp.g_hw_time_stamp);
619 WSH_DEBUG_SV.log(l_module_name,'l_original_call_grp_api', l_original_call_grp_api);
620 END IF;
621 --
622 i := 1;
623 l_loopCounter := 1;
624 --
625 --
626 FOR update_rec in l_pr_update_csr(l_current_hw_time_stamp) LOOP
627 --{
628 --
629 IF l_debug_on THEN
630 WSH_DEBUG_SV.log(l_module_name,'count of plsql table l_wms_lpn_tbl is', l_wms_lpn_tbl.count);
631 WSH_DEBUG_SV.log(l_module_name,'l_call_level',l_call_level);
632 WSH_DEBUG_SV.log(l_module_name,'l_prev_call_level',l_prev_call_level);
633 END IF;
634 --
635 l_call_level := nvl(update_rec.call_level,0);
636 --
637 IF ( l_wms_lpn_tbl.count > 0
638 AND l_loopCounter > 1 AND l_call_level <> l_prev_call_level) THEN
639 --{
640 -- setting the globals appropriately before calling WMS.
641 WSH_WMS_LPN_GRP.g_call_group_api := 'N';
642
643 IF l_debug_on THEN
644 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_CONTAINER_GRP.MODIFY_LPNS',WSH_DEBUG_SV.C_PROC_LEVEL);
645 END IF;
646
647 WMS_Container_GRP.Modify_LPNs (
648 p_api_version => 1.0,
649 p_init_msg_list => FND_API.G_FALSE,
650 p_commit => FND_API.G_FALSE,
651 x_return_status => l_return_status,
652 x_msg_count => l_msg_count,
653 x_msg_data => l_msg_data,
654 p_caller => 'WSH_WMS_SYNC_TMP_PKG',
655 p_lpn_table => l_wms_lpn_tbl
656 );
657 -- resetting the values back to the original values
658 WSH_WMS_LPN_GRP.g_call_group_api := l_original_call_grp_api;
659
660 IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
661 wsh_wms_lpn_grp.g_update_to_containers := 'N';
662 END IF;
663
664 --
665 -- Debug Statements
666 --
667 IF l_debug_on THEN
668 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
669 END IF;
670 --
671 wsh_util_core.api_post_call(
672 p_return_status => l_return_status,
673 x_num_warnings => l_num_warnings,
674 x_num_errors => l_num_errors,
675 p_msg_data => l_msg_data
676 );
677
678 l_wms_lpn_tbl.delete;
679
680 i := 1;
681
682 --}
683 END IF;
684 --
685 IF l_debug_on THEN
686 WSH_DEBUG_SV.log(l_module_name,'Inside the loop i is', i);
687 WSH_DEBUG_SV.log(l_module_name,'Inside the loop delivery_detail_id is', update_rec.delivery_detail_id);
688 WSH_DEBUG_SV.log(l_module_name,'Inside the loop lpn_id is', update_rec.lpn_id);
689 END IF;
690
691 l_wms_lpn_tbl(i).LPN_ID := update_rec.lpn_id;
692 l_wms_lpn_tbl(i).LICENSE_PLATE_NUMBER := update_rec.container_name;
693 l_wms_lpn_tbl(i).INVENTORY_ITEM_ID := update_rec.inventory_item_id;
694 l_wms_lpn_tbl(i).ORGANIZATION_ID := update_rec.ORGANIZATION_ID;
695 l_wms_lpn_tbl(i).SUBINVENTORY_CODE := update_rec.SUBINVENTORY;
696 l_wms_lpn_tbl(i).LOCATOR_ID := update_rec.LOCATOR_ID;
697 l_wms_lpn_tbl(i).GROSS_WEIGHT_UOM_CODE := update_rec.weight_uom_code;
698 l_wms_lpn_tbl(i).GROSS_WEIGHT := update_rec.gross_weight;
699 l_wms_lpn_tbl(i).CONTAINER_VOLUME_UOM := update_rec.volume_uom_code;
700 l_wms_lpn_tbl(i).CONTAINER_VOLUME := update_rec.volume;
701 l_wms_lpn_tbl(i).CONTENT_VOLUME_UOM_CODE := update_rec.volume_uom_code;
702 l_wms_lpn_tbl(i).CONTENT_VOLUME := update_rec.filled_volume;
703 l_wms_lpn_tbl(i).TARE_WEIGHT_UOM_CODE := update_rec.weight_uom_code;
704 l_wms_lpn_tbl(i).TARE_WEIGHT := (update_rec.gross_weight - update_rec.net_weight);
705 i := i + 1;
706 l_loopCounter := l_loopCounter + 1;
707 l_prev_call_level := l_call_level;
708
709 --}
710 END LOOP;
711 --
712 IF l_debug_on THEN
713 WSH_DEBUG_SV.log(l_module_name,'count of plsql table l_wms_lpn_tbl is', l_wms_lpn_tbl.count);
714 END IF;
715 --
716 IF ( l_wms_lpn_tbl.count > 0 ) THEN
717 --{
718 -- setting the globals appropriately before calling WMS.
719 WSH_WMS_LPN_GRP.g_call_group_api := 'N';
720
721 IF l_debug_on THEN
722 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_CONTAINER_GRP.MODIFY_LPNS',WSH_DEBUG_SV.C_PROC_LEVEL);
723 END IF;
724
725 WMS_Container_GRP.Modify_LPNs (
726 p_api_version => 1.0,
727 p_init_msg_list => FND_API.G_FALSE,
728 p_commit => FND_API.G_FALSE,
729 x_return_status => l_return_status,
730 x_msg_count => l_msg_count,
731 x_msg_data => l_msg_data,
732 p_caller => 'WSH_WMS_SYNC_TMP_PKG',
733 p_lpn_table => l_wms_lpn_tbl
734 );
735 -- resetting the values back to the original values
736 WSH_WMS_LPN_GRP.g_call_group_api := l_original_call_grp_api;
737
738 IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
739 wsh_wms_lpn_grp.g_update_to_containers := 'N';
740 END IF;
741
742 --
743 -- Debug Statements
744 --
745 IF l_debug_on THEN
746 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
747 END IF;
748 --
749 wsh_util_core.api_post_call(
750 p_return_status => l_return_status,
751 x_num_warnings => l_num_warnings,
752 x_num_errors => l_num_errors,
753 p_msg_data => l_msg_data
754 );
755 --}
756 END IF;
757
758
759 wsh_wms_lpn_grp.g_hw_time_stamp := wsh_wms_lpn_grp.g_hw_time_stamp + 1/86400;
760
761 IF l_num_errors > 0 THEN
762 RAISE FND_API.G_EXC_ERROR;
763 ELSIF l_num_warnings > 0 THEN
764 x_return_status := wsh_util_core.g_ret_sts_warning;
765 END IF;
766
767 --}
768 --
769 -- Debug Statements
770 --
771 IF l_debug_on THEN
772 WSH_DEBUG_SV.pop(l_module_name);
773 END IF;
774 --
775 EXCEPTION
776 --{
777 WHEN FND_API.G_EXC_ERROR THEN
778 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
779 WSH_WMS_LPN_GRP.g_call_group_api := l_original_call_grp_api;
780 IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
781 wsh_wms_lpn_grp.g_update_to_containers := 'N';
782 END IF;
783 --
784 --
785 --
786 -- Debug Statements
787 --
788 IF l_debug_on THEN
789 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
790 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
791 END IF;
792 --
793 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
794 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
795 WSH_WMS_LPN_GRP.g_call_group_api := l_original_call_grp_api;
796 IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
797 wsh_wms_lpn_grp.g_update_to_containers := 'N';
798 END IF;
799 --
800 --
801 --
802 -- Debug Statements
803 --
804 IF l_debug_on THEN
805 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
806 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
807 END IF;
808 --
809 WHEN OTHERS THEN
810 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
811 wsh_util_core.default_handler('WSH_LPN_SYNC_COMM_PKG.SYNC_PRORATED_LPNS_TO_WMS',l_module_name);
812 WSH_WMS_LPN_GRP.g_call_group_api := l_original_call_grp_api;
813 IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
814 wsh_wms_lpn_grp.g_update_to_containers := 'N';
815 END IF;
816 --
817 -- Debug Statements
818 --
819 IF l_debug_on THEN
820 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
821 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
822 END IF;
823 --
824 --}
825 END SYNC_PRORATED_LPNS_TO_WMS;
826
827
828
829
830 END WSH_LPN_SYNC_COMM_PKG;
[Home] [Help]