1 PACKAGE BODY WSH_LPN_SYNC_COMM_PKG as
2 /* $Header: WSHLSCMB.pls 120.5 2005/11/16 11:15:21 rvishnuv noship $ */
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 IF l_debug_on THEN
193 select count(*) into l_tmp_tbl_size from wsh_wms_sync_tmp
194 where creation_date = l_current_hw_time_stamp;
195 WSH_DEBUG_SV.log(l_module_name,'Count of wsh_wms_sync_tmp table is', l_tmp_tbl_size);
196 END IF;
197
198 -- We are doing the following update because, if a particular went through various
199 -- steps of for example, update, assign, unassign, and was finally deleted, then
200 -- we do not want to call WMS for these operations
201 update wsh_wms_sync_tmp
202 set operation_type = 'DELETE'
203 where delivery_detail_id in (select delivery_detail_id
204 from wsh_wms_sync_tmp
205 where operation_type = 'DELETE'
206 and creation_date = l_current_hw_time_stamp)
207 and operation_type = 'UPDATE'
208 and creation_date = l_current_hw_time_stamp;
209
210 IF l_debug_on THEN
211 WSH_DEBUG_SV.log(l_module_name,'Count of rows updated is', SQL%ROWCOUNT);
212 END IF;
213
214 FOR unpack_rec in l_unpack_csr(l_current_hw_time_stamp) LOOP
215 --{
216 --
217 l_curr_unpack_parent_lpn_id := unpack_rec.old_parent_lpn_id;
218
219 IF (i = 1 OR
220 nvl(l_prev_unpack_parent_lpn_id,-99) = nvl(l_curr_unpack_parent_lpn_id,-99)
221 )
222 THEN
223 --
224 l_unpack_lpn_id_tbl(i) := unpack_rec.lpn_id;
225 --
226 ELSE
227 --
228 IF l_debug_on THEN
229 WSH_DEBUG_SV.log(l_module_name,'Count of l_unpack_lpn_id_tbl is', l_unpack_lpn_id_tbl.count);
230 WSH_DEBUG_SV.log(l_module_name,'old parent lpn id is', l_prev_unpack_parent_lpn_id);
231 END IF;
232 -- This infrastructure is built for future so that when
233 -- WMS is interested in getting this information, we
234 -- need to make a call at this point
235 -- need to set the WSH_WMS_LPN_GRP.g_call_group_api := 'N';
236 -- before calling WMS
237 l_unpack_lpn_id_tbl.delete;
238 i := 1;
239 --
240 END IF;
241 l_prev_unpack_parent_lpn_id := l_curr_unpack_parent_lpn_id;
242 i := i + 1;
243 --
244 --}
245 END LOOP;
246 --
247 -- Need to make one extra call to WMS for UNPACK to take care of the last set of lpns
248 -- in the l_unpack_lpn_id_tbl after teh loop
249 --
250 IF l_debug_on THEN
251 WSH_DEBUG_SV.log(l_module_name,'Count of l_unpack_lpn_id_tbl is', l_unpack_lpn_id_tbl.count);
252 WSH_DEBUG_SV.log(l_module_name,'new parent lpn id is', l_prev_unpack_parent_lpn_id);
253 END IF;
254
255 -- Logic for 'PACK'
256 i := 1;
257
258 FOR pack_rec in l_pack_csr(l_current_hw_time_stamp) LOOP
259 --{
260 --
261 l_curr_pack_parent_lpn_id := pack_rec.parent_lpn_id;
262
263 IF (i = 1 OR
264 nvl(l_prev_pack_parent_lpn_id,-99) = nvl(l_curr_pack_parent_lpn_id,-99)
265 )
266 THEN
267 --
268 l_pack_lpn_id_tbl(i) := pack_rec.lpn_id;
269 --
270 ELSE
271 --
272 IF l_debug_on THEN
273 WSH_DEBUG_SV.log(l_module_name,'Count of l_pack_lpn_id_tbl is', l_pack_lpn_id_tbl.count);
274 WSH_DEBUG_SV.log(l_module_name,'new parent lpn id is', l_prev_pack_parent_lpn_id);
275 END IF;
276 -- This infrastructure is built for future so that when
277 -- WMS is interested in getting this information, we
278 -- need to make a call at this point
279 -- need to set the WSH_WMS_LPN_GRP.g_call_group_api := 'N';
280 -- before calling WMS
281 l_pack_lpn_id_tbl.delete;
282 i := 1;
283 --
284 END IF;
285 l_prev_pack_parent_lpn_id := l_curr_pack_parent_lpn_id;
286 i := i + 1;
287 --
288 --}
289 END LOOP;
290 --
291 -- Need to make one extra call to WMS for PACK to take care of the last set of lpns
292 -- in the l_pack_lpn_id_tbl after teh loop
293 --
294 IF l_debug_on THEN
295 WSH_DEBUG_SV.log(l_module_name,'Count of l_pack_lpn_id_tbl is', l_pack_lpn_id_tbl.count);
296 WSH_DEBUG_SV.log(l_module_name,'new parent lpn id is', l_prev_pack_parent_lpn_id);
297 END IF;
298
299 -- Logic for 'UNASSIGN'
300 i := 1;
301 FOR unasgn_rec in l_unassign_csr(l_current_hw_time_stamp) LOOP
302 --{
303 --
304 l_curr_unasgn_del_id := unasgn_rec.old_delivery_id;
305
306 IF (i = 1 OR
307 nvl(l_prev_unasgn_del_id,-99) = nvl(l_curr_unasgn_del_id,-99)
308 )
309 THEN
310 --
311 l_unasgn_lpn_id_tbl(i) := unasgn_rec.lpn_id;
312 --
313 ELSE
314 --
315 IF l_debug_on THEN
316 WSH_DEBUG_SV.log(l_module_name,'Count of l_unasgn_lpn_id_tbl is', l_unasgn_lpn_id_tbl.count);
317 WSH_DEBUG_SV.log(l_module_name,'old delivery id is', l_prev_unasgn_del_id);
318 END IF;
319 -- This infrastructure is built for future so that when
320 -- WMS is interested in getting this information, we
321 -- need to make a call at this point
322 -- need to set the WSH_WMS_LPN_GRP.g_call_group_api := 'N';
323 -- before calling WMS
324 l_unasgn_lpn_id_tbl.delete;
325 i := 1;
326 --
327 END IF;
328 l_prev_unasgn_del_id := l_curr_unasgn_del_id;
329 i := i + 1;
330 --
331 --}
332 END LOOP;
333 --
334 -- Need to make one extra call to WMS for UNASSIGN to take care of the last set of lpns
335 -- in the l_unasgn_lpn_id_tbl after the loop
336 --
337 IF l_debug_on THEN
338 WSH_DEBUG_SV.log(l_module_name,'Count of l_unasgn_lpn_id_tbl is', l_unasgn_lpn_id_tbl.count);
339 WSH_DEBUG_SV.log(l_module_name,'new parent lpn id is', l_prev_unasgn_del_id);
340 END IF;
341
342 -- Logic for 'UNASSIGN'
343 i := 1;
344
345 FOR assign_rec in l_assign_csr(l_current_hw_time_stamp) LOOP
346 --{
347 --
348 l_curr_asgn_del_id := assign_rec.new_delivery_id;
349
350 IF (i = 1 OR
351 nvl(l_prev_asgn_del_id,-99) = nvl(l_curr_asgn_del_id,-99)
352 )
353 THEN
354 --
355 l_asgn_lpn_id_tbl(i) := assign_rec.lpn_id;
356 --
357 ELSE
358 --
359 IF l_debug_on THEN
360 WSH_DEBUG_SV.log(l_module_name,'Count of l_asgn_lpn_id_tbl is', l_asgn_lpn_id_tbl.count);
361 WSH_DEBUG_SV.log(l_module_name,'new delivery id is', l_prev_asgn_del_id);
362 END IF;
363 -- This infrastructure is built for future so that when
364 -- WMS is interested in getting this information, we
365 -- need to make a call at this point
366 -- need to set the WSH_WMS_LPN_GRP.g_call_group_api := 'N';
367 -- before calling WMS
368 l_asgn_lpn_id_tbl.delete;
369 i := 1;
370 --
371 END IF;
372 l_prev_asgn_del_id := l_curr_asgn_del_id;
373 i := i + 1;
374 --
375 --}
376 END LOOP;
377 --
378 -- Need to make one extra call to WMS for ASSIGN to take care of the last set of lpns
379 -- in the l_asgn_lpn_id_tbl after the loop
380 --
381 IF l_debug_on THEN
382 WSH_DEBUG_SV.log(l_module_name,'Count of l_asgn_lpn_id_tbl is', l_asgn_lpn_id_tbl.count);
383 WSH_DEBUG_SV.log(l_module_name,'new parent lpn id is', l_prev_asgn_del_id);
384 END IF;
385
386 i := 1;
387 FOR update_rec in l_update_csr(l_current_hw_time_stamp) LOOP
388 --{
389 IF l_debug_on THEN
390 WSH_DEBUG_SV.log(l_module_name,'Inside the loop i is', i);
391 WSH_DEBUG_SV.log(l_module_name,'Inside the loop delivery_detail_id is', update_rec.delivery_detail_id);
392 WSH_DEBUG_SV.log(l_module_name,'Inside the loop lpn_id is', update_rec.lpn_id);
393 END IF;
394
395 IF (update_rec.lpn_id IS NOT NULL) THEN
396 l_wms_lpn_tbl(i).LPN_ID := update_rec.lpn_id;
397 l_wms_lpn_tbl(i).LICENSE_PLATE_NUMBER := update_rec.container_name;
398 l_wms_lpn_tbl(i).INVENTORY_ITEM_ID := update_rec.inventory_item_id;
399 l_wms_lpn_tbl(i).ORGANIZATION_ID := update_rec.ORGANIZATION_ID;
400 l_wms_lpn_tbl(i).SUBINVENTORY_CODE := update_rec.SUBINVENTORY;
401 l_wms_lpn_tbl(i).LOCATOR_ID := update_rec.LOCATOR_ID;
402 l_wms_lpn_tbl(i).GROSS_WEIGHT_UOM_CODE := update_rec.weight_uom_code;
403 l_wms_lpn_tbl(i).GROSS_WEIGHT := update_rec.gross_weight;
404 l_wms_lpn_tbl(i).CONTAINER_VOLUME_UOM := update_rec.volume_uom_code;
405 l_wms_lpn_tbl(i).CONTAINER_VOLUME := update_rec.volume;
406 l_wms_lpn_tbl(i).CONTENT_VOLUME_UOM_CODE := update_rec.volume_uom_code;
407 l_wms_lpn_tbl(i).CONTENT_VOLUME := update_rec.filled_volume;
408 l_wms_lpn_tbl(i).TARE_WEIGHT_UOM_CODE := update_rec.weight_uom_code;
409 l_wms_lpn_tbl(i).TARE_WEIGHT := (update_rec.gross_weight - update_rec.net_weight);
410 i := i + 1;
411 END IF;
412 --}
413 END LOOP;
414 --
415 IF l_debug_on THEN
416 WSH_DEBUG_SV.log(l_module_name,'count of plsql table l_wms_lpn_tbl is', l_wms_lpn_tbl.count);
417 END IF;
418 --
419 IF ( l_wms_lpn_tbl.count > 0 ) THEN
420 --{
421 -- setting the globals appropriately before calling WMS.
422 WSH_WMS_LPN_GRP.g_call_group_api := 'N';
423
424 IF l_debug_on THEN
425 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_CONTAINER_GRP.MODIFY_LPNS',WSH_DEBUG_SV.C_PROC_LEVEL);
426 END IF;
427
428 WMS_Container_GRP.Modify_LPNs (
429 p_api_version => 1.0,
430 p_init_msg_list => FND_API.G_FALSE,
431 p_commit => FND_API.G_FALSE,
432 x_return_status => l_return_status,
433 x_msg_count => l_msg_count,
434 x_msg_data => l_msg_data,
435 p_caller => 'WSH_WMS_SYNC_TMP_PKG',
436 p_lpn_table => l_wms_lpn_tbl
437 );
438 -- resetting the values back to the original values
439 WSH_WMS_LPN_GRP.g_call_group_api := l_original_call_grp_api;
440
441 IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
442 wsh_wms_lpn_grp.g_update_to_containers := 'N';
443 END IF;
444
445 --
446 -- Debug Statements
447 --
448 IF l_debug_on THEN
449 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
450 END IF;
451 --
452 wsh_util_core.api_post_call(
453 p_return_status => l_return_status,
454 x_num_warnings => l_num_warnings,
455 x_num_errors => l_num_errors,
456 p_msg_data => l_msg_data
457 );
458
459 --}
460 END IF;
461
462 wsh_wms_lpn_grp.g_hw_time_stamp := wsh_wms_lpn_grp.g_hw_time_stamp + 1/86400;
463
464 IF l_num_errors > 0 THEN
465 RAISE FND_API.G_EXC_ERROR;
466 ELSIF l_num_warnings > 0 THEN
467 x_return_status := wsh_util_core.g_ret_sts_warning;
468 END IF;
469
470 --}
471 --
472 -- Debug Statements
473 --
474 IF l_debug_on THEN
475 WSH_DEBUG_SV.pop(l_module_name);
476 END IF;
477 --
478 EXCEPTION
479 --{
480 WHEN FND_API.G_EXC_ERROR THEN
481 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
482 WSH_WMS_LPN_GRP.g_call_group_api := l_original_call_grp_api;
483 IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
484 wsh_wms_lpn_grp.g_update_to_containers := 'N';
485 END IF;
486 --
487 --
488 --
489 -- Debug Statements
490 --
491 IF l_debug_on THEN
492 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
493 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
494 END IF;
495 --
496 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
497 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
498 WSH_WMS_LPN_GRP.g_call_group_api := l_original_call_grp_api;
499 IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
500 wsh_wms_lpn_grp.g_update_to_containers := 'N';
501 END IF;
502 --
503 --
504 --
505 -- Debug Statements
506 --
507 IF l_debug_on THEN
508 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
509 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
510 END IF;
511 --
512 WHEN OTHERS THEN
513 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
514 wsh_util_core.default_handler('WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_module_name);
515 WSH_WMS_LPN_GRP.g_call_group_api := l_original_call_grp_api;
516 IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
517 wsh_wms_lpn_grp.g_update_to_containers := 'N';
518 END IF;
519 --
520 -- Debug Statements
521 --
522 IF l_debug_on THEN
523 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
524 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
525 END IF;
526 --
527 --}
528 END SYNC_LPNS_TO_WMS;
529
530 -- This procedure is used to synchronize the updates on LPNs in WSH
531 -- to WMS due to proration logic
532 PROCEDURE SYNC_PRORATED_LPNS_TO_WMS
533 (
534 p_in_rec IN WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type,
535 x_return_status OUT NOCOPY VARCHAR2,
536 x_out_rec OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type
537 )
538 IS
539 --{
540 --
541 cursor l_pr_update_csr (p_hw_time_stamp IN DATE) is
542 select wdd.lpn_id,
543 wlst.delivery_detail_id,
544 wdd.container_name,
545 wdd.inventory_item_id,
546 wdd.organization_id,
547 wdd.subinventory,
548 wdd.locator_id,
549 wdd.gross_weight,
550 wdd.volume_uom_code,
551 wdd.filled_volume,
552 wdd.volume,
553 wdd.weight_uom_code,
554 wdd.net_weight,
555 wlst.call_level
556 from wsh_delivery_details wdd,
557 wsh_wms_sync_tmp wlst
558 where wlst.delivery_detail_id = wdd.delivery_detail_id
559 and wdd.lpn_id is not null
560 and wlst.operation_type = 'UPDATE'
561 and wlst.creation_date = p_hw_time_stamp
562 order by nvl(wlst.call_level,0) desc;
563
564 l_current_hw_time_stamp DATE;
565 l_original_call_grp_api VARCHAR2(2);
566 l_call_level NUMBER := 0;
567 l_prev_call_level NUMBER := 0;
568 l_wms_lpn_tbl WMS_DATA_TYPE_DEFINITIONS_PUB.LPNTableType;
569 l_return_status VARCHAR2(10);
570 l_msg_count NUMBER;
571 l_msg_data VARCHAR2(32767);
572 l_num_warnings NUMBER :=0;
573 l_num_errors NUMBER :=0;
574
575 i NUMBER;
576 l_loopCounter NUMBER;
577 --
578 l_debug_on BOOLEAN;
579 --
580 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SYNC_PRORATED_LPNS_TO_WMS';
581 --
582 --}
583 BEGIN
584 --{
585 --
586 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
587 --
588 IF l_debug_on IS NULL
589 THEN
590 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
591 END IF;
592 --
593 --
594 -- Debug Statements
595 --
596 IF l_debug_on THEN
597 WSH_DEBUG_SV.push(l_module_name);
598 END IF;
599 --
600 -- Setting the return status in the begining
601 x_return_status := wsh_util_core.g_ret_sts_success;
602 --
603 IF ( wsh_wms_lpn_grp.g_hw_time_stamp is null ) THEN
604 wsh_wms_lpn_grp.g_hw_time_stamp := sysdate;
605 END IF;
606
607 l_current_hw_time_stamp := wsh_wms_lpn_grp.g_hw_time_stamp;
608 l_original_call_grp_api := wsh_wms_lpn_grp.g_call_group_api;
609
610 IF l_debug_on THEN
611 WSH_DEBUG_SV.log(l_module_name,'wsh_wms_lpn_grp.g_hw_time_stamp', wsh_wms_lpn_grp.g_hw_time_stamp);
612 WSH_DEBUG_SV.log(l_module_name,'l_original_call_grp_api', l_original_call_grp_api);
613 END IF;
614 --
615 i := 1;
616 l_loopCounter := 1;
617 --
618 --
619 FOR update_rec in l_pr_update_csr(l_current_hw_time_stamp) LOOP
620 --{
621 --
622 IF l_debug_on THEN
623 WSH_DEBUG_SV.log(l_module_name,'count of plsql table l_wms_lpn_tbl is', l_wms_lpn_tbl.count);
624 WSH_DEBUG_SV.log(l_module_name,'l_call_level',l_call_level);
625 WSH_DEBUG_SV.log(l_module_name,'l_prev_call_level',l_prev_call_level);
626 END IF;
627 --
628 l_call_level := nvl(update_rec.call_level,0);
629 --
630 IF ( l_wms_lpn_tbl.count > 0
631 AND l_loopCounter > 1 AND l_call_level <> l_prev_call_level) THEN
632 --{
633 -- setting the globals appropriately before calling WMS.
634 WSH_WMS_LPN_GRP.g_call_group_api := 'N';
635
636 IF l_debug_on THEN
637 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_CONTAINER_GRP.MODIFY_LPNS',WSH_DEBUG_SV.C_PROC_LEVEL);
638 END IF;
639
640 WMS_Container_GRP.Modify_LPNs (
641 p_api_version => 1.0,
642 p_init_msg_list => FND_API.G_FALSE,
643 p_commit => FND_API.G_FALSE,
644 x_return_status => l_return_status,
645 x_msg_count => l_msg_count,
646 x_msg_data => l_msg_data,
647 p_caller => 'WSH_WMS_SYNC_TMP_PKG',
648 p_lpn_table => l_wms_lpn_tbl
649 );
650 -- resetting the values back to the original values
651 WSH_WMS_LPN_GRP.g_call_group_api := l_original_call_grp_api;
652
653 IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
654 wsh_wms_lpn_grp.g_update_to_containers := 'N';
655 END IF;
656
657 --
658 -- Debug Statements
659 --
660 IF l_debug_on THEN
661 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
662 END IF;
663 --
664 wsh_util_core.api_post_call(
665 p_return_status => l_return_status,
666 x_num_warnings => l_num_warnings,
667 x_num_errors => l_num_errors,
668 p_msg_data => l_msg_data
669 );
670
671 l_wms_lpn_tbl.delete;
672
673 i := 1;
674
675 --}
676 END IF;
677 --
678 IF l_debug_on THEN
679 WSH_DEBUG_SV.log(l_module_name,'Inside the loop i is', i);
680 WSH_DEBUG_SV.log(l_module_name,'Inside the loop delivery_detail_id is', update_rec.delivery_detail_id);
681 WSH_DEBUG_SV.log(l_module_name,'Inside the loop lpn_id is', update_rec.lpn_id);
682 END IF;
683
684 l_wms_lpn_tbl(i).LPN_ID := update_rec.lpn_id;
685 l_wms_lpn_tbl(i).LICENSE_PLATE_NUMBER := update_rec.container_name;
686 l_wms_lpn_tbl(i).INVENTORY_ITEM_ID := update_rec.inventory_item_id;
687 l_wms_lpn_tbl(i).ORGANIZATION_ID := update_rec.ORGANIZATION_ID;
688 l_wms_lpn_tbl(i).SUBINVENTORY_CODE := update_rec.SUBINVENTORY;
689 l_wms_lpn_tbl(i).LOCATOR_ID := update_rec.LOCATOR_ID;
690 l_wms_lpn_tbl(i).GROSS_WEIGHT_UOM_CODE := update_rec.weight_uom_code;
691 l_wms_lpn_tbl(i).GROSS_WEIGHT := update_rec.gross_weight;
692 l_wms_lpn_tbl(i).CONTAINER_VOLUME_UOM := update_rec.volume_uom_code;
693 l_wms_lpn_tbl(i).CONTAINER_VOLUME := update_rec.volume;
694 l_wms_lpn_tbl(i).CONTENT_VOLUME_UOM_CODE := update_rec.volume_uom_code;
695 l_wms_lpn_tbl(i).CONTENT_VOLUME := update_rec.filled_volume;
696 l_wms_lpn_tbl(i).TARE_WEIGHT_UOM_CODE := update_rec.weight_uom_code;
697 l_wms_lpn_tbl(i).TARE_WEIGHT := (update_rec.gross_weight - update_rec.net_weight);
698 i := i + 1;
699 l_loopCounter := l_loopCounter + 1;
700 l_prev_call_level := l_call_level;
701
702 --}
703 END LOOP;
704 --
705 IF l_debug_on THEN
706 WSH_DEBUG_SV.log(l_module_name,'count of plsql table l_wms_lpn_tbl is', l_wms_lpn_tbl.count);
707 END IF;
708 --
709 IF ( l_wms_lpn_tbl.count > 0 ) THEN
710 --{
711 -- setting the globals appropriately before calling WMS.
712 WSH_WMS_LPN_GRP.g_call_group_api := 'N';
713
714 IF l_debug_on THEN
715 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_CONTAINER_GRP.MODIFY_LPNS',WSH_DEBUG_SV.C_PROC_LEVEL);
716 END IF;
717
718 WMS_Container_GRP.Modify_LPNs (
719 p_api_version => 1.0,
720 p_init_msg_list => FND_API.G_FALSE,
721 p_commit => FND_API.G_FALSE,
722 x_return_status => l_return_status,
723 x_msg_count => l_msg_count,
724 x_msg_data => l_msg_data,
725 p_caller => 'WSH_WMS_SYNC_TMP_PKG',
726 p_lpn_table => l_wms_lpn_tbl
727 );
728 -- resetting the values back to the original values
729 WSH_WMS_LPN_GRP.g_call_group_api := l_original_call_grp_api;
730
731 IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
732 wsh_wms_lpn_grp.g_update_to_containers := 'N';
733 END IF;
734
735 --
736 -- Debug Statements
737 --
738 IF l_debug_on THEN
739 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
740 END IF;
741 --
742 wsh_util_core.api_post_call(
743 p_return_status => l_return_status,
744 x_num_warnings => l_num_warnings,
745 x_num_errors => l_num_errors,
746 p_msg_data => l_msg_data
747 );
748 --}
749 END IF;
750
751
752 wsh_wms_lpn_grp.g_hw_time_stamp := wsh_wms_lpn_grp.g_hw_time_stamp + 1/86400;
753
754 IF l_num_errors > 0 THEN
755 RAISE FND_API.G_EXC_ERROR;
756 ELSIF l_num_warnings > 0 THEN
757 x_return_status := wsh_util_core.g_ret_sts_warning;
758 END IF;
759
760 --}
761 --
762 -- Debug Statements
763 --
764 IF l_debug_on THEN
765 WSH_DEBUG_SV.pop(l_module_name);
766 END IF;
767 --
768 EXCEPTION
769 --{
770 WHEN FND_API.G_EXC_ERROR THEN
771 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
772 WSH_WMS_LPN_GRP.g_call_group_api := l_original_call_grp_api;
773 IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
774 wsh_wms_lpn_grp.g_update_to_containers := 'N';
775 END IF;
776 --
777 --
778 --
779 -- Debug Statements
780 --
781 IF l_debug_on THEN
782 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
783 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
784 END IF;
785 --
786 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
787 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
788 WSH_WMS_LPN_GRP.g_call_group_api := l_original_call_grp_api;
789 IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
790 wsh_wms_lpn_grp.g_update_to_containers := 'N';
791 END IF;
792 --
793 --
794 --
795 -- Debug Statements
796 --
797 IF l_debug_on THEN
798 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
799 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
800 END IF;
801 --
802 WHEN OTHERS THEN
803 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
804 wsh_util_core.default_handler('WSH_LPN_SYNC_COMM_PKG.SYNC_PRORATED_LPNS_TO_WMS',l_module_name);
805 WSH_WMS_LPN_GRP.g_call_group_api := l_original_call_grp_api;
806 IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
807 wsh_wms_lpn_grp.g_update_to_containers := 'N';
808 END IF;
809 --
810 -- Debug Statements
811 --
812 IF l_debug_on THEN
813 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
814 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
815 END IF;
816 --
817 --}
818 END SYNC_PRORATED_LPNS_TO_WMS;
819
820
821
822
823 END WSH_LPN_SYNC_COMM_PKG;
[Home] [Help]