[Home] [Help]
PACKAGE BODY: APPS.WSH_DLVB_COMMON_ACTIONS
Source
1 PACKAGE BODY WSH_DLVB_COMMON_ACTIONS as
2 /* $Header: WSHDDCMB.pls 120.1.12010000.2 2009/05/18 13:17:53 selsubra ship $ */
3
4
5 /*
6 -----------------------------------------------------------------------------
7 PROCEDURE : Assign_Details
8 PARAMETERS : p_detail_tab - table of ids for assigning; could be delivery
9 lines or containers
10 p_parent_detail_id - parent delivery detail id that details
11 need to be assigned to
12 p_delivery_id - delivery_id for assignment of details
13 x_pack_status - status of container after assignment - whether
14 underpacked, overpacked or success.
15 x_return_status - return status of API
16 DESCRIPTION : This procedure takes as input a table of delivery details -
17 both lines and containers and creates an assignment to a parent
18 detail (container) and/or a delivery. The API loops through
19 all input ids and creates the assignment by calling the
20 appropriate delivery detail or container API. This API serves
21 as the wrapper API to handle a multi-selection of both lines
22 and containers.
23
24 ------------------------------------------------------------------------------
25 */
26
27
28 --
29 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DLVB_COMMON_ACTIONS';
30 --
31 PROCEDURE Assign_Details (
32 p_detail_tab IN WSH_UTIL_CORE.id_tab_type,
33 p_parent_detail_id IN NUMBER,
34 p_delivery_id IN NUMBER,
35 p_group_api_flag IN VARCHAR2 DEFAULT NULL,
36 x_pack_status OUT NOCOPY VARCHAR2,
37 x_return_status OUT NOCOPY VARCHAR2) IS
38
39 l_cont_tab WSH_UTIL_CORE.id_tab_type;
40 l_detail_tab WSH_UTIL_CORE.id_tab_type;
41 l_error_tab WSH_UTIL_CORE.id_tab_type;
42
43 l_det_rec WSH_UTIL_CORE.ID_TAB_TYPE;
44
45 l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
46
47 l_cont_name VARCHAR2(30);
48
49 l_pack_status VARCHAR2(30);
50
51 l_wms_filter_flag VARCHAR2(1) := 'N';
52
53 l_entity_ids WSH_UTIL_CORE.id_tab_type;
54 --
55 l_has_lines VARCHAR2(1) := 'N';
56 l_dlvy_freight_terms_code VARCHAR2(30);
57 l_fill_status VARCHAR2(1);
58
59 l_num_warnings number := 0;
60
61
62 cursor c_child_details(p_detail_id in number) is
63 select delivery_id
64 from wsh_delivery_assignments_v da
65 where da.delivery_detail_id = p_detail_id;
66
67 cursor c_delivery_org(p_delivery_id in number) is
68 select organization_id
69 from wsh_new_deliveries
70 where delivery_id = p_delivery_id;
71
72 l_delivery_id NUMBER;
73 l_organization_id NUMBER;
74 l_wms_org VARCHAR2(10);
75 -- K LPN CONV. rv
76 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
77 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
78 l_msg_count NUMBER;
79 l_msg_data VARCHAR2(32767);
80 e_return_excp EXCEPTION;
81 -- K LPN CONV. rv
82
83
84
85 --
86 l_debug_on BOOLEAN;
87 --
88 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ASSIGN_DETAILS';
89 --
90 BEGIN
91 -- bug 1678527: check for packing in WMS orgs
92 --
93 -- Debug Statements
94 --
95 --
96 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
97 --
98 IF l_debug_on IS NULL
99 THEN
100 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
101 END IF;
102 --
103 IF l_debug_on THEN
104 WSH_DEBUG_SV.push(l_module_name);
105 --
106 WSH_DEBUG_SV.log(l_module_name,'P_DETAIL_TAB.COUNT',P_DETAIL_TAB.COUNT);
107 WSH_DEBUG_SV.log(l_module_name,'P_PARENT_DETAIL_ID',P_PARENT_DETAIL_ID);
108 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
109 WSH_DEBUG_SV.log(l_module_name,'P_GROUP_API_FLAG',P_GROUP_API_FLAG);
110 END IF;
111 --
112 IF (p_parent_detail_id IS NOT NULL) AND (NVL(p_group_api_flag, 'N') = 'N') THEN
113 l_wms_filter_flag := 'Y';
114 ELSE
115 l_wms_filter_flag := 'N';
116 END IF;
117
118 OPEN c_delivery_org(p_delivery_id);
119 FETCH c_delivery_org
120 INTO l_organization_id;
121 CLOSE c_delivery_org;
122
123 l_wms_org := WSH_UTIL_VALIDATE.check_wms_org(l_organization_id);
124 --
125 -- Debug Statements
126 --
127 IF l_debug_on THEN
128 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DLVB_COMMON_ACTIONS.SEPARATE_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
129 END IF;
130 --
131 WSH_DLVB_COMMON_ACTIONS.Separate_Details (
132 p_input_tab =>p_detail_tab,
133 x_cont_inst_tab =>l_cont_tab,
134 x_detail_tab =>l_detail_tab,
135 x_error_tab =>l_error_tab,
136 x_return_status =>x_return_status,
137 p_wms_filter_flag =>l_wms_filter_flag);
138
139
140 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
141 IF l_wms_filter_flag = 'Y' THEN
142 FND_MESSAGE.SET_NAME('WSH','WSH_SEPARATE_DET_ERROR');
143 WSH_UTIL_CORE.Add_Message(x_return_status);
144 END IF;
145 --
146 -- Debug Statements
147 --
148 --IF l_debug_on THEN
149 --WSH_DEBUG_SV.pop(l_module_name);
150 --END IF;
151 --
152 --return; LPN CONV. rv
153 raise e_return_excp; -- LPN CONV. rv
154 END IF;
155
156
157 /* first process all the delivery line records */
158 IF l_detail_tab.count <> 0 THEN
159
160
161 IF p_parent_detail_id IS NOT NULL THEN
162
163 --
164 -- Debug Statements
165 --
166 IF l_debug_on THEN
167 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_ACTIONS.ASSIGN_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
168 END IF;
169 --
170 WSH_CONTAINER_ACTIONS.Assign_Detail (
171 p_container_instance_id =>p_parent_detail_id,
172 p_del_detail_tab =>l_detail_tab,
173 x_pack_status =>l_pack_status,
174 x_return_status =>l_return_status);
175
176 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
177 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
178 l_num_warnings := l_num_warnings + 1;
179 ELSE
180 --
181 -- Debug Statements
182 --
183 IF l_debug_on THEN
184 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.GET_CONT_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
185 END IF;
186 --
187 l_cont_name := WSH_CONTAINER_UTILITIES.Get_Cont_Name(p_parent_detail_id);
188 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_DET_ASSG_ERROR');
189 x_return_status := l_return_status;
190 WSH_UTIL_CORE.Add_Message(l_return_status);
191 --
192 -- Debug Statements
193 --
194 --IF l_debug_on THEN
195 --WSH_DEBUG_SV.pop(l_module_name);
196 --END IF;
197 --
198 --return;
199 raise e_return_excp; -- LPN CONV. rv
200 END IF;
201 END IF;
202
203 -- J: W/V Changes
204
205 WSH_WV_UTILS.Check_Fill_Pc (
206 p_container_instance_id => p_parent_detail_id,
207 x_fill_status => l_fill_status,
208 x_return_status => l_return_status);
209
210 IF l_fill_status = 'O' THEN
211
212 IF l_debug_on THEN
213 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.GET_CONT_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
214 END IF;
215 --
216 l_cont_name := WSH_CONTAINER_UTILITIES.Get_Cont_Name(p_parent_detail_id);
217 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_OVERPACKED');
218 FND_MESSAGE.SET_TOKEN('CONT_NAME',l_cont_name);
219 l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
220 l_num_warnings := l_num_warnings + 1; --Bug 8259359
221 WSH_UTIL_CORE.Add_Message(l_return_status);
222 x_pack_status := 'Overpacked';
223
224 ELSIF l_fill_status = 'U' THEN
225 IF l_debug_on THEN
226 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.GET_CONT_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
227 END IF;
228 --
229 l_cont_name := WSH_CONTAINER_UTILITIES.Get_Cont_Name(p_parent_detail_id);
230 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_UNDERPACKED');
231 FND_MESSAGE.SET_TOKEN('CONT_NAME',l_cont_name);
232 l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
233 l_num_warnings := l_num_warnings + 1; --Bug 8259359
234 WSH_UTIL_CORE.Add_Message(l_return_status);
235 x_pack_status := 'Underpacked';
236 ELSE
237 x_pack_status := 'Success';
238 END IF;
239
240 END IF;
241
242 IF p_delivery_id IS NOT NULL THEN
243
244 FOR i IN 1..l_detail_tab.count LOOP
245 l_det_rec(i) := l_detail_tab(i);
246 END LOOP;
247
248 --
249 -- Debug Statements
250 --
251 IF l_debug_on THEN
252 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_MULTIPLE_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
253 END IF;
254 --
255 WSH_DELIVERY_DETAILS_ACTIONS.Assign_Multiple_Details (
256 p_rec_of_detail_ids =>l_det_rec,
257 p_delivery_id =>p_delivery_id,
258 p_cont_ins_id =>NULL,
259 x_return_status =>l_return_status);
260
261 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
262 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
263 l_num_warnings := l_num_warnings + 1;
264 ELSE
265 FND_MESSAGE.SET_NAME('WSH','WSH_DETAILS_ASSG_ERROR');
266 x_return_status := l_return_status;
267 WSH_UTIL_CORE.Add_Message(l_return_status);
268 --
269 -- Debug Statements
270 --
271 --IF l_debug_on THEN
272 --WSH_DEBUG_SV.pop(l_module_name);
273 --END IF;
274 --
275 --return;
276 raise e_return_excp; -- LPN CONV. rv
277 END IF;
278 END IF;
279
280 END IF;
281
282 END IF;
283
284
285 /* process all the container records */
286
287 IF l_cont_tab.count <> 0 THEN
288
289 IF p_delivery_id IS NOT NULL -- J-IB-NPARIKH
290 THEN
291 --{
292 l_has_lines
293 := WSH_DELIVERY_VALIDATIONS.has_lines(p_delivery_id);
294 --}
295 END IF;
296 --
297 FOR i IN 1..l_cont_tab.count LOOP
298
299
300 IF p_parent_detail_id IS NOT NULL THEN
301
302 --
303 -- Debug Statements
304 --
305 IF l_debug_on THEN
306 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_ACTIONS.ASSIGN_TO_CONTAINER',WSH_DEBUG_SV.C_PROC_LEVEL);
307 END IF;
308 --
309 WSH_CONTAINER_ACTIONS.Assign_To_Container (
310 p_det_cont_inst_id =>l_cont_tab(i),
311 p_par_cont_inst_id =>p_parent_detail_id,
312 x_return_status =>l_return_status);
313
314 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
315 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
316 l_num_warnings := l_num_warnings + 1;
317 ELSE
318 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_ASSG_ERROR');
319 x_return_status := l_return_status;
320 WSH_UTIL_CORE.Add_Message(l_return_status);
321 --
322 -- Debug Statements
323 --
324 --IF l_debug_on THEN
325 --WSH_DEBUG_SV.pop(l_module_name);
326 --END IF;
327 --
328 --return;
329 raise e_return_excp; -- LPN CONV. rv
330 END IF;
331 END IF;
332
333 END IF;
334
335
336 IF p_delivery_id IS NOT NULL THEN
337
338 l_delivery_id := NULL;
339
340 IF (l_wms_org = 'Y') THEN
341
342 OPEN c_child_details(l_cont_tab(i));
343 FETCH c_child_details
344 INTO l_delivery_id;
345 CLOSE c_child_details;
346
347 END IF;
348
349 IF (l_delivery_id IS NULL) THEN
350
351 --
352 -- Debug Statements
353 --
354 --
355 WSH_CONTAINER_ACTIONS.Assign_To_Delivery (
356 p_container_instance_id =>l_cont_tab(i),
357 p_delivery_id =>p_delivery_id,
358 x_return_status =>l_return_status,
359 x_dlvy_has_lines => l_has_lines,
360 x_dlvy_freight_terms_code => l_dlvy_freight_terms_code
361 );
362
363 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
364 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
365 l_num_warnings := l_num_warnings + 1;
366 ELSE
367 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_ASSG_ERROR');
368 x_return_status := l_return_status;
369 WSH_UTIL_CORE.Add_Message(l_return_status);
370 --
371 -- Debug Statements
372 --
373 --IF l_debug_on THEN
374 --WSH_DEBUG_SV.pop(l_module_name);
375 --END IF;
376 --
377 --return;
378 raise e_return_excp; -- LPN CONV. rv
379 END IF;
380 END IF;
381
382 END IF;
383
384 END IF;
385
386 END LOOP;
387
388 END IF;
389
390
391 /* H projects: pricing integration csun
392 if the code reaches here, the status is either success or warning */
393 IF p_parent_detail_id is NOT NULL THEN
394 l_entity_ids.delete;
395 l_entity_ids(1) := p_parent_detail_id;
396
397 --
398 -- Debug Statements
399 --
400 IF l_debug_on THEN
401 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_ACTIONS.MARK_REPRICE_REQUIRED',WSH_DEBUG_SV.C_PROC_LEVEL);
402 END IF;
403 --
404 WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
405 p_entity_type => 'DELIVERY_DETAIL',
406 p_entity_ids => l_entity_ids,
407 x_return_status => l_return_status);
408 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
409 FND_MESSAGE.SET_NAME('WSH','WSH_REPRICE_REQUIRED_ERR');
410 WSH_UTIL_CORE.Add_Message(l_return_status);
411 END IF;
412
413 END IF;
414 IF p_delivery_id is NOT NULL THEN
415 l_entity_ids.delete;
416 l_entity_ids(1) := p_delivery_id;
417 --
418 -- Debug Statements
419 --
420 IF l_debug_on THEN
421 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_ACTIONS.MARK_REPRICE_REQUIRED',WSH_DEBUG_SV.C_PROC_LEVEL);
422 END IF;
423 --
424 WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
425 p_entity_type => 'DELIVERY',
426 p_entity_ids => l_entity_ids,
427 x_return_status => l_return_status);
428 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
429 FND_MESSAGE.SET_NAME('WSH','WSH_REPRICE_REQUIRED_ERR');
430 WSH_UTIL_CORE.Add_Message(l_return_status);
431 END IF;
432 END IF;
433
434 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
435 x_pack_status := 'Warning';
436 x_return_status := l_return_status;
437 --
438 -- Debug Statements
439 --
440 --IF l_debug_on THEN
441 --WSH_DEBUG_SV.pop(l_module_name);
442 --END IF;
443 --
444 --return;
445 raise e_return_excp; -- LPN CONV. rv
446 ELSE
447 IF l_num_warnings > 0 THEN
448 x_pack_status := 'Warning';
449 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
450 ELSE
451 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
452 x_pack_status := 'Success';
453 END IF;
454 --
455 -- Debug Statements
456 --
457 IF l_debug_on THEN
458 WSH_DEBUG_SV.log(l_module_name,'Return Status',x_return_status);
459 --WSH_DEBUG_SV.pop(l_module_name);
460 END IF;
461 --
462 --return;
463 END IF;
464
465 --
466 -- K LPN CONV. rv
467 --
468 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
469 THEN
470 --{
471
472 --
473 IF l_debug_on THEN
474 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);
475 END IF;
476 --
477 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
478 (
479 p_in_rec => l_lpn_in_sync_comm_rec,
480 x_return_status => l_return_status,
481 x_out_rec => l_lpn_out_sync_comm_rec
482 );
483 --
484 --
485 IF l_debug_on THEN
486 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
487 END IF;
488 --}
489 END IF;
490 --
491 --
492 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
493 x_pack_status := 'Warning';
494 x_return_status := l_return_status;
495 --
496 -- Debug Statements
497 --
498 IF l_debug_on THEN
499 WSH_DEBUG_SV.pop(l_module_name);
500 END IF;
501 --
502 return;
503 ELSE
504 IF l_num_warnings > 0
505 OR l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
506 x_pack_status := 'Warning';
507 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
508 ELSE
509 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
510 x_pack_status := 'Success';
511 END IF;
512 --
513 -- Debug Statements
514 --
515 IF l_debug_on THEN
516 WSH_DEBUG_SV.log(l_module_name,'Return Status',x_return_status);
517 WSH_DEBUG_SV.pop(l_module_name);
518 END IF;
519 --
520 return;
521 --
522 END IF;
523 --
524 -- K LPN CONV. rv
525 --
526 x_pack_status := 'Success';
527 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
528
529 --
530 -- Debug Statements
531 --
532 IF l_debug_on THEN
533 WSH_DEBUG_SV.pop(l_module_name);
534 END IF;
535 --
536 EXCEPTION
537
538 WHEN e_return_excp THEN
539 --
540 -- K LPN CONV. rv
541 --
542 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
543 THEN
544 --{
545 IF l_debug_on THEN
546 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);
547 END IF;
548
549 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
550 (
551 p_in_rec => l_lpn_in_sync_comm_rec,
552 x_return_status => l_return_status,
553 x_out_rec => l_lpn_out_sync_comm_rec
554 );
555 --
556 --
557 IF l_debug_on THEN
558 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
559 END IF;
560 --
561 --
562 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,WSH_UTIL_CORE.G_RET_STS_ERROR) and x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
563 x_return_status := l_return_status;
564 END IF;
565 --
566 --}
567 END IF;
568 --
569 -- K LPN CONV. rv
570 --
571 IF l_debug_on THEN
572 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_RETURN_EXCP');
573 END IF;
574 --
575 WHEN Others THEN
576 WSH_UTIL_CORE.Default_Handler('WSH_DLVB_COMMON_ACTIONS.Assign_Details');
577 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
578 IF l_debug_on THEN
579 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
580 END IF;
581 --
582 -- K LPN CONV. rv
583 --
584 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
585 THEN
586 --{
587 IF l_debug_on THEN
588 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);
589 END IF;
590
591 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
592 (
593 p_in_rec => l_lpn_in_sync_comm_rec,
594 x_return_status => l_return_status,
595 x_out_rec => l_lpn_out_sync_comm_rec
596 );
597 --
598 --
599 IF l_debug_on THEN
600 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
601 END IF;
602 --
603 --
604 --}
605 END IF;
606 --
607 -- K LPN CONV. rv
608 --
609 --
610 -- Debug Statements
611 --
612 IF l_debug_on THEN
613 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
614 END IF;
615 --
616 END Assign_Details;
617
618
619 /*
620 -----------------------------------------------------------------------------
621 PROCEDURE : Unassign_Details
622 PARAMETERS : p_detail_tab - table of ids for unassigning; could be delivery
623 lines or containers
624 p_parent_detail_flag - 'Y' or 'N' to indicate whether to
625 unassign from parent delivery detail id
626 p_delivery_flag - 'Y' or 'N' to indicate whether to unassign
627 from delivery
628 x_return_status - return status of API
629 DESCRIPTION : This procedure takes as input a table of delivery details -
630 both lines and containers and unassigns the details from either
631 containers or deliveries or both. The API loops through
632 all input ids and unassigns the details based on the two input
633 flags. If the flags are set to 'Y' then the unassigning is
634 done from that entity. If both the flags are set to 'N' then
635 detail is unassigned from both the container and the delivery.
636 The container and delivery weight volumes are re-calculated
637 after the unassigning.
638
639 ------------------------------------------------------------------------------
640 */
641
642
643 PROCEDURE Unassign_Details (
644 p_detail_tab IN WSH_UTIL_CORE.id_tab_type,
645 p_parent_detail_flag IN VARCHAR2,
646 p_delivery_flag IN VARCHAR2,
647 p_group_api_flag IN VARCHAR2 DEFAULT NULL,
648 x_return_status OUT NOCOPY VARCHAR2,
649 p_action_prms IN wsh_glbl_var_strct_grp.dd_action_parameters_rec_type -- J-IB-NPARIKH
650 ) IS
651
652
653 CURSOR Get_Min_Fill(v_cont_id NUMBER) IS
654 SELECT nvl(minimum_fill_percent,0)
655 FROM WSH_DELIVERY_DETAILS
656 WHERE delivery_detail_id = v_cont_id
657 AND container_flag = 'Y';
658
659
660 l_cont_tab WSH_UTIL_CORE.id_tab_type;
661 l_detail_tab WSH_UTIL_CORE.id_tab_type;
662 l_error_tab WSH_UTIL_CORE.id_tab_type;
663
664 l_det_rec WSH_UTIL_CORE.ID_TAB_TYPE;
665
666 l_gross NUMBER;
667 l_net NUMBER;
668 l_volume NUMBER;
669 l_fill NUMBER;
670 l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
671
672 l_min_fill NUMBER;
673
674 l_pack_status VARCHAR2(30);
675 l_wms_filter_flag VARCHAR2(1);
676
677 l_num_warnings number := 0;
678 -- K LPN CONV. rv
679 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
680 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
681 l_msg_count NUMBER;
682 l_msg_data VARCHAR2(32767);
683 e_return_excp EXCEPTION;
684 -- K LPN CONV. rv
685
686 --
687 l_debug_on BOOLEAN;
688 --
689 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UNASSIGN_DETAILS';
690 --
691 BEGIN
692
693 -- bug 1678527: check for unpacking in WMS orgs
694 --
695 -- Debug Statements
696 --
697 --
698 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
699 --
700 IF l_debug_on IS NULL
701 THEN
702 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
703 END IF;
704 --
705 IF l_debug_on THEN
706 WSH_DEBUG_SV.push(l_module_name);
707 --
708 WSH_DEBUG_SV.log(l_module_name,'P_DETAIL_TAB.COUNT',p_detail_tab.count);
709 WSH_DEBUG_SV.log(l_module_name,'P_PARENT_DETAIL_FLAG',P_PARENT_DETAIL_FLAG);
710 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_FLAG',P_DELIVERY_FLAG);
711 WSH_DEBUG_SV.log(l_module_name,'P_GROUP_API_FLAG',P_GROUP_API_FLAG);
712 END IF;
713 --
714 IF (p_parent_detail_flag = 'Y') AND (NVL(p_group_api_flag, 'N') = 'N') THEN
715 l_wms_filter_flag := 'Y';
716 ELSE
717 l_wms_filter_flag := 'N';
718 END IF;
719
720 --
721 -- Debug Statements
722 --
723 IF l_debug_on THEN
724 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DLVB_COMMON_ACTIONS.SEPARATE_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
725 END IF;
726 --
727 WSH_DLVB_COMMON_ACTIONS.Separate_Details (
728 p_detail_tab,
729 l_cont_tab,
730 l_detail_tab,
731 l_error_tab,
732 x_return_status,
733 l_wms_filter_flag);
734
735 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
736 IF l_wms_filter_flag = 'Y' THEN
737 FND_MESSAGE.SET_NAME('WSH','WSH_SEPARATE_DET_ERROR');
738 WSH_UTIL_CORE.Add_Message(x_return_status);
739 END IF;
740 --
741 -- Debug Statements
742 --
743 --IF l_debug_on THEN
744 --WSH_DEBUG_SV.pop(l_module_name);
745 --END IF;
746 --
747 --return;
748 raise e_return_excp; -- LPN CONV. rv
749 END IF;
750
751 /* first process all the delivery line records */
752
753 IF l_detail_tab.count > 0 THEN
754
755 --
756 -- Debug Statements
757 --
758 IF l_debug_on THEN
759 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_ACTIONS.UNASSIGN_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
760 END IF;
761 --
762 WSH_CONTAINER_ACTIONS.Unassign_Detail (
763 NULL,
764 NULL,
765 l_detail_tab,
766 p_parent_detail_flag,
767 p_delivery_flag,
768 l_pack_status,
769 x_return_status,
770 p_action_prms); -- J-IB-NPARIKH
771
772 /** Bug 1559785 and 1562917 -- warning is not being passed to the form in a
773 proper way,so add the part for Warning */
774 IF ((x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
775 (x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
776 FND_MESSAGE.SET_NAME('WSH','WSH_DETAILS_UNASSG_ERROR');
777 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
778 WSH_UTIL_CORE.Add_Message(x_return_status);
779 ELSE
780 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
781 l_num_warnings := l_num_warnings + 1;
782 END IF;
783 l_return_status := x_return_status;
784 END IF;
785
786 END IF;
787
788
789 /* process all the container records */
790
791 IF l_cont_tab.count > 0 THEN
792
793 --
794 -- Debug Statements
795 --
796 IF l_debug_on THEN
797 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_ACTIONS.UNASSIGN_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
798 END IF;
799 --
800 WSH_CONTAINER_ACTIONS.Unassign_Detail (
801 NULL,
802 NULL,
803 l_cont_tab,
804 p_parent_detail_flag,
805 p_delivery_flag,
806 l_pack_status,
807 x_return_status);
808
809 /** Bug 1559785 and 1562917 -- warning is not being passed to the form in a
810 proper way,so add the part for Warning */
811 IF ((x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
812 (x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
813 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_UNASSG_ERROR');
814 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
815 WSH_UTIL_CORE.Add_Message(l_return_status);
816 ELSE
817 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
818 l_num_warnings := l_num_warnings + 1;
819 END IF;
820 l_return_status := x_return_status;
821 END IF;
822
823 END IF;
824
825
826 --
827 -- K LPN CONV. rv
828 --
829 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
830 THEN
831 --{
832 IF l_debug_on THEN
833 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);
834 END IF;
835
836 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
837 (
838 p_in_rec => l_lpn_in_sync_comm_rec,
839 x_return_status => x_return_status,
840 x_out_rec => l_lpn_out_sync_comm_rec
841 );
842 --
843 --
844 IF l_debug_on THEN
845 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',x_return_status);
846 END IF;
847 --
848 --
849 IF ((x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
850 (x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
851
852 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
853 l_return_status := x_return_status;
854 END IF;
855
856 ELSE
857 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
858 l_num_warnings := l_num_warnings + 1;
859 END IF;
860 l_return_status := x_return_status;
861 END IF;
862 --
863 --
864 --}
865 END IF;
866 --
867 -- K LPN CONV. rv
868 --
869 /** Bug 1559785 and 1562917 -- warning is not being passed to the form in a
870 proper way,so add the part for Warning */
871 IF ((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
872 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
873 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
874 ELSE
875 IF l_num_warnings > 0 THEN
876 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
877 ELSE
878 x_return_status := l_return_status; -- modified since it can be S or W
879 END IF;
880 END IF;
881
882 --
883 -- Debug Statements
884 --
885 IF l_debug_on THEN
886 WSH_DEBUG_SV.log(l_module_name,'Return Status',x_return_status);
887 WSH_DEBUG_SV.pop(l_module_name);
888 END IF;
889 --
890 EXCEPTION
891 WHEN e_return_excp THEN
892 --
893 -- K LPN CONV. rv
894 --
895 --
896 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
897 THEN
898 --{
899 IF l_debug_on THEN
900 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);
901 END IF;
902
903 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
904 (
905 p_in_rec => l_lpn_in_sync_comm_rec,
906 x_return_status => l_return_status,
907 x_out_rec => l_lpn_out_sync_comm_rec
908 );
909 --
910 --
911 IF l_debug_on THEN
912 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
913 END IF;
914 --
915 --
916 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,WSH_UTIL_CORE.G_RET_STS_ERROR) and x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
917 x_return_status := l_return_status;
918 END IF;
919 --
920 --}
921 END IF;
922 --
923 IF l_debug_on THEN
924 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:e_return_excp');
925 END IF;
926 --
927 -- K LPN CONV. rv
928 --
929 WHEN Others THEN
930 WSH_UTIL_CORE.Default_Handler('WSH_DLVB_COMMON_ACTIONS.Unassign_Details');
931 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
932 IF l_debug_on THEN
933 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
934 END IF;
935 --
936 -- K LPN CONV. rv
937 --
938 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
939 THEN
940 --{
941 IF l_debug_on THEN
942 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);
943 END IF;
944
945 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
946 (
947 p_in_rec => l_lpn_in_sync_comm_rec,
948 x_return_status => l_return_status,
949 x_out_rec => l_lpn_out_sync_comm_rec
950 );
951 --
952 --
953 IF l_debug_on THEN
954 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
955 END IF;
956 --
957 --
958 --}
959 END IF;
960 --
961 -- K LPN CONV. rv
962 --
963
964
965 --
966 -- Debug Statements
967 --
968 IF l_debug_on THEN
969 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
970 END IF;
971 --
972 END Unassign_Details;
973
974
975 -------------------------------------------------------------------
976 -- This procedure is only for backward compatibility. No one should call
977 -- this procedure.
978 -------------------------------------------------------------------
979
980 PROCEDURE Unassign_Details (
981 p_detail_tab IN WSH_UTIL_CORE.id_tab_type,
982 p_parent_detail_flag IN VARCHAR2,
983 p_delivery_flag IN VARCHAR2,
984 p_group_api_flag IN VARCHAR2 DEFAULT NULL,
985 x_return_status OUT NOCOPY VARCHAR2) IS
986
987
988
989
990 --
991 l_debug_on BOOLEAN;
992 --
993 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UNASSIGN_DETAILS';
994 --
995 l_action_prms wsh_glbl_var_strct_grp.dd_action_parameters_rec_type; -- J-IB-NPARIKH
996
997 BEGIN
998
999 -- bug 1678527: check for unpacking in WMS orgs
1000 --
1001 -- Debug Statements
1002 --
1003 --
1004 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1005 --
1006 IF l_debug_on IS NULL
1007 THEN
1008 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1009 END IF;
1010 --
1011 IF l_debug_on THEN
1012 WSH_DEBUG_SV.push(l_module_name);
1013 --
1014 WSH_DEBUG_SV.log(l_module_name,'P_DETAIL_TAB.COUNT',p_detail_tab.count);
1015 WSH_DEBUG_SV.log(l_module_name,'P_PARENT_DETAIL_FLAG',P_PARENT_DETAIL_FLAG);
1016 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_FLAG',P_DELIVERY_FLAG);
1017 WSH_DEBUG_SV.log(l_module_name,'P_GROUP_API_FLAG',P_GROUP_API_FLAG);
1018 END IF;
1019 --
1020 --
1021 -- Debug Statements
1022 --
1023 IF l_debug_on THEN
1024 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DLVB_COMMON_ACTIONS.UNASSIGN_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
1025 END IF;
1026 --
1027 Unassign_Details (
1028 p_detail_tab,
1029 p_parent_detail_flag,
1030 p_delivery_flag,
1031 p_group_api_flag,
1032 x_return_status,
1033 l_action_prms);
1034 --
1035 -- Debug Statements
1036 --
1037 IF l_debug_on THEN
1038 WSH_DEBUG_SV.pop(l_module_name);
1039 END IF;
1040 --
1041 EXCEPTION
1042
1043 WHEN Others THEN
1044 WSH_UTIL_CORE.Default_Handler('WSH_DLVB_COMMON_ACTIONS.Unassign_Details',l_module_name);
1045 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1046 --
1047 -- Debug Statements
1048 --
1049 IF l_debug_on THEN
1050 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1051 END IF;
1052 --
1053 END Unassign_Details;
1054
1055 /*
1056 -----------------------------------------------------------------------------
1057 PROCEDURE : Auto_Pack_Lines
1058 PARAMETERS : p_group_id_tab - table of group ids if the grouping for the
1059 lines are already known.
1060 p_detail_tab - table of ids for assigning; could be delivery
1061 lines or containers
1062 p_pack_cont_flag - 'Y' or 'N' to determine whether to autopack
1063 detail containers into master containers while autopacking
1064 x_cont_inst_tab - table of delivery detail ids (containers)
1065 that were created due to the autopack.
1066 x_return_status - return status of API
1067 DESCRIPTION : This procedure takes as input a table of delivery details -
1068 both lines and containers and eliminates all container ids and
1069 with the remaining delivery lines, it calls the auto pack API
1070 in the container actions package. This API serves as the
1071 wrapper API to handle a multi-selection of both lines and
1072 containers.
1073
1074 ------------------------------------------------------------------------------
1075 */
1076
1077
1078 PROCEDURE Auto_Pack_Lines (
1079 p_group_id_tab IN WSH_UTIL_CORE.id_tab_type,
1080 p_detail_tab IN WSH_UTIL_CORE.id_tab_type,
1081 p_pack_cont_flag IN VARCHAR2,
1082 p_group_api_flag IN VARCHAR2 DEFAULT NULL,
1083 x_cont_inst_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
1084 x_return_status OUT NOCOPY VARCHAR2) IS
1085
1086
1087 l_cont_tab WSH_UTIL_CORE.id_tab_type;
1088 l_detail_tab WSH_UTIL_CORE.id_tab_type;
1089 l_error_tab WSH_UTIL_CORE.id_tab_type;
1090
1091 l_ret_sts VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1092
1093 l_cont_name VARCHAR2(30);
1094
1095 l_cont_info_tab WSH_CONTAINER_ACTIONS.empty_cont_info_tab;
1096
1097 l_cont_inst_tab WSH_UTIL_CORE.id_tab_type;
1098
1099 i NUMBER;
1100 cont_cnt NUMBER;
1101 l_wms_filter_flag VARCHAR2(1);
1102
1103 /* H projects: pricing integration csun */
1104 l_entity_ids WSH_UTIL_CORE.id_tab_type;
1105 m NUMBER := 0;
1106 mark_reprice_error EXCEPTION;
1107
1108 -- K LPN CONV. rv
1109 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
1110 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
1111 l_msg_count NUMBER;
1112 l_msg_data VARCHAR2(32767);
1113 l_return_status VARCHAR2(10);
1114 l_num_warnings NUMBER := 0;
1115 e_return_excp EXCEPTION;
1116 -- K LPN CONV. rv
1117
1118 --
1119 l_debug_on BOOLEAN;
1120 --
1121 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'AUTO_PACK_LINES';
1122 --
1123 BEGIN
1124 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1125 IF l_debug_on IS NULL THEN
1126 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1127 END IF;
1128 --
1129 IF l_debug_on THEN
1130 WSH_DEBUG_SV.push(l_module_name);
1131 WSH_DEBUG_SV.log(l_module_name,'P_GROUP_ID_TAB.COUNT',P_GROUP_ID_TAB.COUNT);
1132 WSH_DEBUG_SV.log(l_module_name,'P_DETAIL_TAB.COUNT',P_DETAIL_TAB.COUNT);
1133 WSH_DEBUG_SV.log(l_module_name,'P_PACK_CONT_FLAG',P_PACK_CONT_FLAG);
1134 WSH_DEBUG_SV.log(l_module_name,'P_GROUP_API_FLAG',P_GROUP_API_FLAG);
1135 END IF;
1136
1137 l_entity_ids.delete;
1138 -- bug 1678527: disallow auto-packing in WMS orgs
1139 IF NVL(p_group_api_flag, 'N') = 'N' THEN
1140 l_wms_filter_flag := 'Y';
1141 END IF;
1142
1143 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1144
1145 IF l_debug_on THEN
1146 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DLVB_COMMON_ACTIONS.SEPARATE_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
1147 END IF;
1148 --
1149 WSH_DLVB_COMMON_ACTIONS.Separate_Details (
1150 p_detail_tab,
1151 l_cont_tab,
1152 l_detail_tab,
1153 l_error_tab,
1154 x_return_status,
1155 l_wms_filter_flag);
1156
1157
1158 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1159 IF l_wms_filter_flag = 'Y' THEN
1160 FND_MESSAGE.SET_NAME('WSH','WSH_SEPARATE_DET_ERROR');
1161 WSH_UTIL_CORE.Add_Message(x_return_status);
1162 END IF;
1163 --IF l_debug_on THEN
1164 --WSH_DEBUG_SV.pop(l_module_name);
1165 --END IF;
1166 --return;
1167 raise e_return_excp; -- LPN CONV. rv
1168 END IF;
1169
1170 IF (l_error_tab.count > 0) THEN
1171 l_ret_sts := WSH_UTIL_CORE.G_RET_STS_WARNING;
1172 END IF;
1173
1174
1175 IF l_detail_tab.count <= 0 AND l_cont_tab.count <= 0 THEN
1176 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1177 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_DET_ASSG_NULL');
1178 WSH_UTIL_CORE.Add_Message(x_return_status);
1179 --IF l_debug_on THEN
1180 --WSH_DEBUG_SV.pop(l_module_name);
1181 --END IF;
1182 --return;
1183 raise e_return_excp; -- LPN CONV. rv
1184 END IF;
1185
1186 IF l_detail_tab.count > 0 THEN
1187
1188 IF l_debug_on THEN
1189 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_ACTIONS.AUTO_PACK_LINES',WSH_DEBUG_SV.C_PROC_LEVEL);
1190 END IF;
1191
1192 WSH_CONTAINER_ACTIONS.Auto_Pack_Lines (
1193 p_group_id_tab,
1194 l_detail_tab,
1195 p_pack_cont_flag,
1196 l_cont_inst_tab,
1197 x_return_status);
1198
1199 IF l_debug_on THEN
1200 WSH_DEBUG_SV.logmsg(l_module_name,'RETURN_STATUS'||x_return_status);
1201 END IF;
1202 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1203 -- Bug 2847515
1204 l_ret_sts := x_return_status;
1205 END IF;
1206 END IF;
1207
1208 IF l_debug_on THEN
1209 WSH_DEBUG_SV.logmsg(l_module_name,'After Detail Tab');
1210 WSH_DEBUG_SV.logmsg(l_module_name,'X_RETURN_STATUS'||x_return_status);
1211 WSH_DEBUG_SV.logmsg(l_module_name,'L_RETURN_STATUS'||l_ret_sts);
1212 END IF;
1213
1214 FOR i IN 1..l_cont_inst_tab.count LOOP
1215 x_cont_inst_tab(i) := l_cont_inst_tab(i);
1216 /* H projecst : pricing integration csun */
1217 m := m+1;
1218 l_entity_ids(m) := l_cont_inst_tab(i);
1219
1220 END LOOP;
1221
1222 l_cont_inst_tab.delete;
1223
1224 IF l_cont_tab.count > 0 THEN
1225
1226 IF l_debug_on THEN
1227 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_ACTIONS.AUTO_PACK_CONTS',WSH_DEBUG_SV.C_PROC_LEVEL);
1228 END IF;
1229 --
1230 WSH_CONTAINER_ACTIONS.Auto_Pack_Conts (
1231 p_group_id_tab,
1232 l_cont_info_tab,
1233 l_cont_tab,
1234 l_cont_inst_tab,
1235 x_return_status);
1236
1237 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1238 -- Bug 3562797 jckwok
1239 -- Set l_ret_sts to return status of Auto_Pack_Conts. Similar issue
1240 -- (Bug 2847515) was already fixed
1241 -- after call to WSH_CONTAINER_ACTIONS.Auto_Pack_Lines but
1242 -- neglected for WSH_CONTAINER_ACTIONS.Auto_Pack_Conts
1243 l_ret_sts := x_return_status;
1244 END IF;
1245 END IF;
1246
1247 cont_cnt := x_cont_inst_tab.count;
1248 i := 1;
1249
1250 FOR i IN 1..l_cont_inst_tab.count LOOP
1251 cont_cnt := cont_cnt + 1;
1252 x_cont_inst_tab(cont_cnt) := l_cont_inst_tab(i);
1253 /* H projecst : pricing integration csun */
1254 m := m+1;
1255 l_entity_ids(m) := l_cont_inst_tab(i);
1256 END LOOP;
1257
1258 IF l_debug_on THEN
1259 WSH_DEBUG_SV.logmsg(l_module_name,'After Container Tab');
1260 WSH_DEBUG_SV.logmsg(l_module_name,'X_RETURN_STATUS'||x_return_status);
1261 WSH_DEBUG_SV.logmsg(l_module_name,'L_RETURN_STATUS'||l_ret_sts);
1262 END IF;
1263
1264 IF (l_ret_sts <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1265 -- Bug 2847515
1266 x_return_status := l_ret_sts;
1267 FND_MESSAGE.SET_NAME('WSH','WSH_SUM_AUTOPACK_ERROR');
1268 WSH_UTIL_CORE.Add_Message(x_return_status);
1269 IF (l_ret_sts = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN --Bug 3562797 jckwok
1270 --IF l_debug_on THEN
1271 --WSH_DEBUG_SV.pop(l_module_name);
1272 --END IF;
1273 --return;
1274 raise e_return_excp; -- LPN CONV. rv
1275 END IF;
1276 END IF;
1277 /* H projecst : pricing integration csun */
1278 IF l_entity_ids.count > 0 THEN
1279 IF l_debug_on THEN
1280 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_ACTIONS.MARK_REPRICE_REQUIRED',WSH_DEBUG_SV.C_PROC_LEVEL);
1281 END IF;
1282 WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
1283 p_entity_type => 'DELIVERY_DETAIL',
1284 p_entity_ids => l_entity_ids,
1285 x_return_status => l_ret_sts);
1286 IF l_debug_on THEN
1287 WSH_DEBUG_SV.logmsg(l_module_name,'After Mark_Reprice_Required L_RETURN_STATUS:'||l_ret_sts);
1288 END IF;
1289 IF l_ret_sts <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1290 raise mark_reprice_error;
1291 END IF;
1292 END IF;
1293 -- Bug 3562797 jckwok
1294 -- x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1295 --
1296 -- K LPN CONV. rv
1297 --
1298 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1299 THEN
1300 --{
1301
1302 IF l_debug_on THEN
1303 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);
1304 END IF;
1305 --
1306 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1307 (
1308 p_in_rec => l_lpn_in_sync_comm_rec,
1309 x_return_status => l_return_status,
1310 x_out_rec => l_lpn_out_sync_comm_rec
1311 );
1312 --
1313 --
1314 IF l_debug_on THEN
1315 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1316 END IF;
1317 --
1318 --
1319 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1320 --
1321 x_return_status := l_return_status;
1322 --
1323 IF l_debug_on THEN
1324 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_WMS_SYNC_TMP_PKG.MERGE completed with an error');
1325 WSH_DEBUG_SV.pop(l_module_name);
1326 END IF;
1327 --
1328 RETURN;
1329 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1330 l_num_warnings := l_num_warnings + 1;
1331 END IF;
1332 --
1333 --}
1334 END IF;
1335 --
1336 -- K LPN CONV. rv
1337 --
1338
1339 IF (l_num_warnings > 0 AND x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1340 --
1341 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1342 --
1343 END IF;
1344
1345 IF l_debug_on THEN
1346 WSH_DEBUG_SV.logmsg(l_module_name,'X_RETURN_STATUS:'||x_return_status);
1347 WSH_DEBUG_SV.log(l_module_name,'X_CONT_INST_TAB.COUNT',X_CONT_INST_TAB.COUNT);
1348 WSH_DEBUG_SV.pop(l_module_name);
1349 END IF;
1350
1351 EXCEPTION
1352
1353 WHEN e_return_excp THEN
1354 --
1355 -- K LPN CONV. rv
1356 --
1357 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1358 THEN
1359 --{
1360 IF l_debug_on THEN
1361 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);
1362 END IF;
1363
1364 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1365 (
1366 p_in_rec => l_lpn_in_sync_comm_rec,
1367 x_return_status => l_return_status,
1368 x_out_rec => l_lpn_out_sync_comm_rec
1369 );
1370 --
1371 --
1372 IF l_debug_on THEN
1373 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1374 END IF;
1375 --
1376 --
1377 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,WSH_UTIL_CORE.G_RET_STS_ERROR) and x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1378 x_return_status := l_return_status;
1379 END IF;
1380 --
1381 --}
1382 END IF;
1383 --
1384 -- K LPN CONV. rv
1385 --
1386 --
1387 -- Debug Statements
1388 --
1389 IF l_debug_on THEN
1390 WSH_DEBUG_SV.logmsg(l_module_name,'E_RETURN_EXCP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1391 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_RETURN_EXCP');
1392 END IF;
1393 --
1394 --
1395 WHEN mark_reprice_error THEN
1396 FND_MESSAGE.Set_Name('WSH', 'WSH_REPRICE_REQUIRED_ERR');
1397 WSH_UTIL_CORE.add_message (x_return_status);
1398 x_return_status := l_ret_sts;
1399
1400 --
1401 -- K LPN CONV. rv
1402 --
1403 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1404 THEN
1405 --{
1406 IF l_debug_on THEN
1407 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);
1408 END IF;
1409
1410 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1411 (
1412 p_in_rec => l_lpn_in_sync_comm_rec,
1413 x_return_status => l_return_status,
1414 x_out_rec => l_lpn_out_sync_comm_rec
1415 );
1416 --
1417 --
1418 IF l_debug_on THEN
1419 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1420 END IF;
1421 --
1422 --
1423 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1424 x_return_status := l_return_status;
1425 END IF;
1426 --
1427 --}
1428 END IF;
1429 --
1430 -- K LPN CONV. rv
1431 --
1432 --
1433 -- Debug Statements
1434 --
1435 IF l_debug_on THEN
1436 WSH_DEBUG_SV.logmsg(l_module_name,'MARK_REPRICE_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1437 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:MARK_REPRICE_ERROR');
1438 END IF;
1439 --
1440 WHEN Others THEN
1441 WSH_UTIL_CORE.Default_Handler('WSH_DLVB_COMMON_ACTIONS.Auto_Pack_Lines');
1442 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1443 IF l_debug_on THEN
1444 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1445 END IF;
1446 --
1447 -- K LPN CONV. rv
1448 --
1449 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1450 THEN
1451 --{
1452 IF l_debug_on THEN
1453 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);
1454 END IF;
1455
1456 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1457 (
1458 p_in_rec => l_lpn_in_sync_comm_rec,
1459 x_return_status => l_return_status,
1460 x_out_rec => l_lpn_out_sync_comm_rec
1461 );
1462 --
1463 --
1464 IF l_debug_on THEN
1465 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1466 END IF;
1467 --
1468 --
1469 --}
1470 END IF;
1471 --
1472 -- K LPN CONV. rv
1473 --
1474
1475
1476 --
1477 -- Debug Statements
1478 --
1479 IF l_debug_on THEN
1480 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1481 END IF;
1482 --
1483 END Auto_Pack_Lines;
1484
1485
1486 /*
1487 -----------------------------------------------------------------------------
1488 PROCEDURE : Separate_Details
1489 PARAMETERS : p_input_tab - table of ids in input; could be delivery
1490 lines or containers
1491 x_cont_inst_tab - table of delivery detail ids that are
1492 containers
1493 x_detail_tab - table of delivery_details that are lines.
1494 x_error_tab - table of any ids that are erroneous
1495 x_return_status - return status of API
1496 p_wms_filter_flag - Y = do not include records in WMS orgs.
1497 N = include all records.
1498 Default = N
1499 Bug 1678527: disable packing actions
1500 for delivery details in WMS orgs.
1501 DESCRIPTION : This procedure takes as input a table of delivery details -
1502 both lines and containers and separates all container ids and
1503 delivery lines. It returns three tables - one for containers,
1504 one for delivery lines and one for any erroroneous ids.
1505
1506 ------------------------------------------------------------------------------
1507 */
1508
1509
1510 PROCEDURE Separate_Details (
1511 p_input_tab IN WSH_UTIL_CORE.id_tab_type,
1512 x_cont_inst_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
1513 x_detail_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
1514 x_error_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
1515 x_return_status OUT NOCOPY VARCHAR2,
1516 p_wms_filter_flag IN VARCHAR2 DEFAULT 'N') IS
1517
1518 -- bug 1678527: look up wms enabled flag
1519 CURSOR Select_Cont_Flag (v_detail_id NUMBER) IS
1520 SELECT nvl(wdd.container_flag,'N'),
1521 NVL(mp.wms_enabled_flag, 'N'),
1522 nvl(line_direction,'O') line_direction -- J-IB-NPARIKH
1523 FROM WSH_DELIVERY_DETAILS wdd,
1524 MTL_PARAMETERS mp
1525 WHERE wdd.delivery_detail_id = v_detail_id
1526 AND mp.organization_id(+) = wdd.organization_id;
1527
1528 l_cont_flag VARCHAR2(1);
1529 l_wms_enabled VARCHAR2(1);
1530 l_line_direction VARCHAR2(30);
1531 det_cnt NUMBER := 0;
1532 cont_cnt NUMBER := 0;
1533 err_cnt NUMBER := 0;
1534 wms_cnt NUMBER := 0;
1535
1536 l_ret_sts VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1537
1538 --
1539 l_debug_on BOOLEAN;
1540 --
1541 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SEPARATE_DETAILS';
1542 --
1543 BEGIN
1544
1545 --
1546 -- Debug Statements
1547 --
1548 --
1549 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1550 --
1551 IF l_debug_on IS NULL
1552 THEN
1553 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1554 END IF;
1555 --
1556 IF l_debug_on THEN
1557 WSH_DEBUG_SV.push(l_module_name);
1558 --
1559 WSH_DEBUG_SV.log(l_module_name,'P_INPUT_TAB.COUNT',P_INPUT_TAB.COUNT);
1560 WSH_DEBUG_SV.log(l_module_name,'P_WMS_FILTER_FLAG',P_WMS_FILTER_FLAG);
1561 END IF;
1562 --
1563 IF p_input_tab.count <> 0 THEN
1564
1565 FOR i IN 1..p_input_tab.count LOOP
1566
1567 OPEN Select_Cont_Flag (p_input_tab(i));
1568 FETCH Select_Cont_Flag INTO l_cont_flag, l_wms_enabled,l_line_direction;
1569
1570 IF Select_Cont_Flag%NOTFOUND THEN
1571 err_cnt := err_cnt + 1;
1572 x_error_tab(err_cnt) := p_input_tab(i);
1573 CLOSE Select_Cont_Flag;
1574 l_ret_sts := WSH_UTIL_CORE.G_RET_STS_WARNING;
1575 END IF;
1576
1577 IF Select_Cont_Flag%ISOPEN THEN
1578 CLOSE Select_Cont_Flag;
1579 END IF;
1580
1581 IF p_wms_filter_flag = 'Y'
1582 AND l_wms_enabled = 'Y'
1583 AND l_line_direction IN ('O','IO')
1584 THEN
1585 -- bug 1678527: do not add WMS controlled records to lists.
1586 wms_cnt := wms_cnt + 1;
1587 ELSE
1588
1589 IF l_cont_flag = 'N' THEN
1590 det_cnt := det_cnt + 1;
1591 x_detail_tab(det_cnt) := p_input_tab(i);
1592 END IF;
1593
1594 IF l_cont_flag = 'Y' THEN
1595 cont_cnt := cont_cnt + 1;
1596 x_cont_inst_tab(cont_cnt) := p_input_tab(i);
1597 END IF;
1598
1599 END IF;
1600
1601 END LOOP;
1602
1603 IF wms_cnt > 0 THEN
1604
1605 IF wms_cnt = p_input_tab.count THEN
1606 l_ret_sts := WSH_UTIL_CORE.G_RET_STS_ERROR;
1607 FND_MESSAGE.SET_NAME('WSH', 'WSH_WMS_PACK_NOT_ALLOWED');
1608 WSH_UTIL_CORE.Add_message(l_ret_sts);
1609 ELSE
1610 FND_MESSAGE.SET_NAME('WSH', 'WSH_WMS_RECORDS_NOT_PACKED');
1611 FND_MESSAGE.SET_TOKEN('COUNT', wms_cnt);
1612 WSH_UTIL_CORE.Add_message(l_ret_sts);
1613 END IF;
1614
1615 END IF;
1616
1617 ELSE
1618 l_ret_sts := WSH_UTIL_CORE.G_RET_STS_ERROR;
1619 FND_MESSAGE.SET_NAME('WSH','WSH_INPUT_TABLE_NULL');
1620 WSH_UTIL_CORE.Add_Message(l_ret_sts);
1621 END IF;
1622
1623 x_return_status := l_ret_sts;
1624
1625 --
1626 -- Debug Statements
1627 --
1628 IF l_debug_on THEN
1629 WSH_DEBUG_SV.log(l_module_name,'X_DETAIL_TAB.COUNT',X_DETAIL_TAB.COUNT);
1630 WSH_DEBUG_SV.log(l_module_name,'X_CONT_INST_TAB.COUNT',X_CONT_INST_TAB.COUNT);
1631 WSH_DEBUG_SV.pop(l_module_name);
1632 END IF;
1633 --
1634 EXCEPTION
1635
1636 WHEN Others THEN
1637 WSH_UTIL_CORE.Default_Handler('WSH_DLVB_COMMON_ACTIONS.Separate_Details');
1638 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1639
1640 --
1641 -- Debug Statements
1642 --
1643 IF l_debug_on THEN
1644 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1645 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1646 END IF;
1647 --
1648 END Separate_Details;
1649
1650
1651
1652 /*
1653 -----------------------------------------------------------------------------
1654 PROCEDURE : Calc_Cont_Avail
1655 PARAMETERS : p_container_instance_id - delivery detail id of container
1656 x_avail_wt - available weight capacity of container
1657 x_avail_vol - available volume capacity of container
1658 x_wt_uom - weight uom code for above weights
1659 x_vol_uom - volume uom code for above volumes
1660 x_return_status - return status of API
1661 DESCRIPTION : This procedure takes as input a container (delivery detail id)
1662 and returns the available weight and volume capacity for the
1663 container.
1664
1665 ------------------------------------------------------------------------------
1666 */
1667
1668 PROCEDURE Calc_Cont_Avail (
1669 p_container_instance_id IN NUMBER,
1670 x_avail_wt OUT NOCOPY NUMBER,
1671 x_avail_vol OUT NOCOPY NUMBER,
1672 x_wt_uom OUT NOCOPY VARCHAR2,
1673 x_vol_uom OUT NOCOPY VARCHAR2,
1674 x_return_status OUT NOCOPY VARCHAR2) IS
1675
1676 --Bug 3630085 :Select filled_Volume instead of volume
1677
1678 CURSOR Get_Cont_Info IS
1679 SELECT inventory_item_id, gross_weight, net_weight, filled_volume,
1680 maximum_load_weight, maximum_volume,
1681 weight_uom_code, volume_uom_code, organization_id
1682 FROM WSH_DELIVERY_DETAILS
1683 WHERE delivery_detail_id = p_container_instance_id
1684 and container_flag = 'Y';
1685
1686 gr_wt NUMBER;
1687 net_wt NUMBER;
1688 fill_pc NUMBER;
1689 fill_vol NUMBER;
1690 tmp_fil_vol NUMBER;
1691 wt_uom VARCHAR2(3);
1692 vol_uom VARCHAR2(3);
1693 wsh_wt_uom VARCHAR2(3);
1694 wsh_vol_uom VARCHAR2(3);
1695 l_ret_sts VARCHAR2(1);
1696 tmp_gr_wt NUMBER;
1697 tmp_net_wt NUMBER;
1698 tmp_vol NUMBER;
1699 tmp_tare_wt NUMBER;
1700 tare_wt NUMBER;
1701 max_load_wt NUMBER;
1702 tmp_max_load_wt NUMBER;
1703 tmp_max_vol NUMBER;
1704 max_vol NUMBER;
1705 cont_item_id NUMBER;
1706 cont_org_id NUMBER;
1707 dummy_vol NUMBER;
1708 l_cont_name VARCHAR2(30);
1709 l_org_name VARCHAR2(240);
1710
1711 --
1712 l_debug_on BOOLEAN;
1713 --
1714 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CALC_CONT_AVAIL';
1715 --
1716 BEGIN
1717
1718 --
1719 -- Debug Statements
1720 --
1721 --
1722 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1723 --
1724 IF l_debug_on IS NULL
1725 THEN
1726 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1727 END IF;
1728 --
1729 IF l_debug_on THEN
1730 WSH_DEBUG_SV.push(l_module_name);
1731 --
1732 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_INSTANCE_ID',P_CONTAINER_INSTANCE_ID);
1733 END IF;
1734 --
1735 OPEN Get_Cont_Info;
1736
1737 FETCH Get_Cont_Info INTO
1738 cont_item_id,
1739 tmp_gr_wt,
1740 tmp_net_wt,
1741 tmp_fil_vol,
1742 tmp_max_load_wt,
1743 tmp_max_vol,
1744 wt_uom,
1745 vol_uom,
1746 cont_org_id;
1747
1748 IF (Get_Cont_Info%NOTFOUND) THEN
1749 CLOSE Get_Cont_Info;
1750 --
1751 -- Debug Statements
1752 --
1753 IF l_debug_on THEN
1754 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.GET_CONT_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1755 END IF;
1756 --
1757 l_cont_name := WSH_CONTAINER_UTILITIES.Get_Cont_Name(p_container_instance_id);
1758 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_CONTAINER');
1759 FND_MESSAGE.SET_TOKEN('CONT_NAME',l_cont_name);
1760 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1761 WSH_UTIL_CORE.Add_Message(x_return_status);
1762 --
1763 -- Debug Statements
1764 --
1765 IF l_debug_on THEN
1766 WSH_DEBUG_SV.pop(l_module_name);
1767 END IF;
1768 --
1769 return;
1770 END IF;
1771
1772 IF Get_Cont_Info%ISOPEN THEN
1773 CLOSE Get_Cont_Info;
1774 END IF;
1775
1776 IF wt_uom IS NULL OR vol_uom IS NULL THEN
1777 --
1778 -- Debug Statements
1779 --
1780 IF l_debug_on THEN
1781 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.GET_CONT_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1782 END IF;
1783 --
1784 l_cont_name := WSH_CONTAINER_UTILITIES.Get_Cont_Name(p_container_instance_id);
1785 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_WV_UOM_NULL');
1786 FND_MESSAGE.SET_TOKEN('CONT_NAME',l_cont_name);
1787 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1788 WSH_UTIL_CORE.Add_Message(x_return_status);
1789 --
1790 -- Debug Statements
1791 --
1792 IF l_debug_on THEN
1793 WSH_DEBUG_SV.pop(l_module_name);
1794 END IF;
1795 --
1796 return;
1797 END IF;
1798
1799 IF (tmp_gr_wt IS NULL) THEN
1800
1801 -- WSH_WV_UTILS.Container_Weight_Volume (
1802 -- replacing with TPA enabled API..
1803 --
1804 -- Debug Statements
1805 --
1806 IF l_debug_on THEN
1807 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TPA_CONTAINER_PKG.CONTAINER_WEIGHT_VOLUME',WSH_DEBUG_SV.C_PROC_LEVEL);
1808 END IF;
1809
1810 --Bug 3630085:OMFST:J:R2:WRONG VALUE OF WEIGHT AND VOLUME IN PACKING WORKBENCH WINDOW
1811 --x_volume in case of a container represent the total volume of the container
1812 --Collecting x_volume in a dummy variable
1813
1814 WSH_TPA_CONTAINER_PKG.Container_Weight_Volume (
1815 p_container_instance_id => p_container_instance_id,
1816 p_override_flag => 'N',
1817 x_gross_weight => tmp_gr_wt,
1818 x_net_weight => tmp_net_wt,
1819 x_volume => dummy_vol,
1820 p_fill_pc_flag => 'Y',
1821 x_cont_fill_pc => fill_pc,
1822 x_return_status => l_ret_sts);
1823
1824 IF l_ret_sts <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1825 --
1826 -- Debug Statements
1827 --
1828 IF l_debug_on THEN
1829 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.GET_CONT_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1830 END IF;
1831 --
1832 l_cont_name := WSH_CONTAINER_UTILITIES.Get_Cont_Name(p_container_instance_id);
1833 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_WT_VOL_FAILED');
1834 FND_MESSAGE.SET_TOKEN('CONT_NAME',l_cont_name);
1835 l_ret_sts := WSH_UTIL_CORE.G_RET_STS_WARNING;
1836 WSH_UTIL_CORE.Add_Message(l_ret_sts);
1837 tmp_gr_wt := 0;
1838 tmp_net_wt := 0;
1839 fill_pc := 0;
1840 END IF;
1841 END IF;
1842
1843 -- WSH_WV_UTILS.Container_Tare_Weight_Self (
1844 -- replacing with TPA enabled API..
1845
1846 --
1847 -- Debug Statements
1848 --
1849 IF l_debug_on THEN
1850 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TPA_CONTAINER_PKG.CONTAINER_TARE_WEIGHT_SELF',WSH_DEBUG_SV.C_PROC_LEVEL);
1851 END IF;
1852 --
1853 WSH_TPA_CONTAINER_PKG.Container_Tare_Weight_Self (
1854 p_container_instance_id,
1855 cont_item_id,
1856 wt_uom,
1857 cont_org_id,
1858 tmp_tare_wt,
1859 l_ret_sts);
1860
1861 IF l_ret_sts <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1862 --
1863 -- Debug Statements
1864 --
1865 IF l_debug_on THEN
1866 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.GET_CONT_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1867 END IF;
1868 --
1869 l_cont_name := WSH_CONTAINER_UTILITIES.Get_Cont_Name(p_container_instance_id);
1870 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_TARE_SELF_FAIL');
1871 FND_MESSAGE.SET_TOKEN('CONT_NAME',l_cont_name);
1872 l_ret_sts := WSH_UTIL_CORE.G_RET_STS_WARNING;
1873 WSH_UTIL_CORE.Add_Message(l_ret_sts);
1874 tmp_tare_wt := 0;
1875 END IF;
1876
1877 --
1878 -- Debug Statements
1879 --
1880 IF l_debug_on THEN
1881 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.GET_DEFAULT_UOMS',WSH_DEBUG_SV.C_PROC_LEVEL);
1882 END IF;
1883 --
1884 WSH_WV_UTILS.Get_Default_Uoms (
1885 cont_org_id,
1886 wsh_wt_uom,
1887 wsh_vol_uom,
1888 l_ret_sts);
1889
1890 IF l_ret_sts <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1891 --
1892 -- Debug Statements
1893 --
1894 IF l_debug_on THEN
1895 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ORG_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1896 END IF;
1897 --
1898 l_org_name := WSH_UTIL_CORE.Get_Org_Name(cont_org_id);
1899 FND_MESSAGE.SET_NAME('WSH','WSH_DEFAULT_UOM_ERROR');
1900 FND_MESSAGE.SET_TOKEN('ORG_NAME',l_org_name);
1901 l_ret_sts := WSH_UTIL_CORE.G_RET_STS_WARNING;
1902 WSH_UTIL_CORE.Add_Message(l_ret_sts);
1903 wsh_wt_uom := wt_uom;
1904 wsh_vol_uom := vol_uom;
1905 END IF;
1906
1907 x_wt_uom := wsh_wt_uom;
1908 x_vol_uom := wsh_vol_uom;
1909
1910
1911 --
1912 -- Debug Statements
1913 --
1914 IF l_debug_on THEN
1915 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
1916 END IF;
1917 --
1918 gr_wt := WSH_WV_UTILS.Convert_Uom (
1919 wt_uom,
1920 wsh_wt_uom,
1921 tmp_gr_wt,
1922 cont_item_id);
1923
1924 --
1925 -- Debug Statements
1926 --
1927 IF l_debug_on THEN
1928 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
1929 END IF;
1930 --
1931 net_wt := WSH_WV_UTILS.Convert_Uom (
1932 wt_uom,
1933 wsh_wt_uom,
1934 tmp_net_wt,
1935 cont_item_id);
1936
1937 --
1938 -- Debug Statements
1939 --
1940 IF l_debug_on THEN
1941 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
1942 END IF;
1943 --
1944 max_load_wt := WSH_WV_UTILS.Convert_Uom (
1945 wt_uom,
1946 wsh_wt_uom,
1947 tmp_max_load_wt,
1948 cont_item_id);
1949
1950
1951 --
1952 -- Debug Statements
1953 --
1954 IF l_debug_on THEN
1955 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
1956 END IF;
1957 --
1958 fill_vol := WSH_WV_UTILS.Convert_Uom (
1959 vol_uom,
1960 wsh_vol_uom,
1961 tmp_fil_vol,
1962 cont_item_id);
1963
1964
1965 --
1966 -- Debug Statements
1967 --
1968 IF l_debug_on THEN
1969 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
1970 END IF;
1971 --
1972 max_vol := WSH_WV_UTILS.Convert_Uom (
1973 vol_uom,
1974 wsh_vol_uom,
1975 tmp_max_vol,
1976 cont_item_id);
1977
1978
1979 --
1980 -- Debug Statements
1981 --
1982 IF l_debug_on THEN
1983 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
1984 END IF;
1985 --
1986 tare_wt := WSH_WV_UTILS.Convert_Uom (
1987 wt_uom,
1988 wsh_wt_uom,
1989 tmp_tare_wt,
1990 cont_item_id);
1991
1992
1993 x_avail_wt := max_load_wt - nvl(gr_wt,0) + nvl(tare_wt,0);
1994 x_avail_vol := max_vol - nvl(fill_vol,0);
1995
1996 IF x_avail_wt < 0 THEN
1997 x_avail_wt := 0;
1998 END IF;
1999
2000 IF x_avail_vol < 0 THEN
2001 x_avail_vol := 0;
2002 END IF;
2003
2004 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2005
2006 --
2007 -- Debug Statements
2008 --
2009 IF l_debug_on THEN
2010 WSH_DEBUG_SV.log(l_module_name,'X_AVAIL_WT',x_avail_wt);
2011 WSH_DEBUG_SV.log(l_module_name,'X_AVAIL_VOL',x_avail_vol);
2012 WSH_DEBUG_SV.pop(l_module_name);
2013 END IF;
2014 --
2015 EXCEPTION
2016
2017 WHEN Others THEN
2018 WSH_UTIL_CORE.Default_Handler('WSH_DLVB_COMMON_ACTIONS.Calc_Cont_Avail');
2019 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2020
2021 --
2022 -- Debug Statements
2023 --
2024 IF l_debug_on THEN
2025 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2026 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2027 END IF;
2028 --
2029 END Calc_Cont_Avail;
2030
2031
2032
2033 /*
2034 -----------------------------------------------------------------------------
2035 PROCEDURE : Calc_Item_Total
2036 PARAMETERS : p_delivery_detail_id - delivery detail id of line
2037 x_item_wt - weight of line
2038 x_item_vol - volume of line
2039 x_wt_uom - weight uom code for above weights
2040 x_vol_uom - volume uom code for above volumes
2041 x_return_status - return status of API
2042 DESCRIPTION : This procedure takes as input a delivery detail id and
2043 returns the total item weight and volume for the line.
2044
2045 ------------------------------------------------------------------------------
2046 */
2047
2048
2049 PROCEDURE Calc_Item_Total (
2050 p_delivery_detail_id IN NUMBER,
2051 x_item_wt OUT NOCOPY NUMBER,
2052 x_item_vol OUT NOCOPY NUMBER,
2053 x_wt_uom OUT NOCOPY VARCHAR2,
2054 x_vol_uom OUT NOCOPY VARCHAR2,
2055 x_return_status OUT NOCOPY VARCHAR2) IS
2056
2057
2058 CURSOR Get_Detail_Info IS
2059 SELECT inventory_item_id, net_weight, volume,
2060 weight_uom_code, volume_uom_code, organization_id
2061 FROM WSH_DELIVERY_DETAILS
2062 WHERE delivery_detail_id = p_delivery_detail_id
2063 AND nvl(container_flag,'N') = 'N';
2064
2065 net_wt NUMBER;
2066 vol NUMBER;
2067 wt_uom VARCHAR2(3);
2068 vol_uom VARCHAR2(3);
2069 wsh_wt_uom VARCHAR2(3);
2070 wsh_vol_uom VARCHAR2(3);
2071 l_ret_sts VARCHAR2(1);
2072 tmp_net_wt NUMBER;
2073 tmp_vol NUMBER;
2074 det_item_id NUMBER;
2075 det_org_id NUMBER;
2076
2077 l_org_name VARCHAR2(240);
2078
2079 --
2080 l_debug_on BOOLEAN;
2081 --
2082 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CALC_ITEM_TOTAL';
2083 --
2084 BEGIN
2085
2086 --
2087 -- Debug Statements
2088 --
2089 --
2090 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2091 --
2092 IF l_debug_on IS NULL
2093 THEN
2094 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2095 END IF;
2096 --
2097 IF l_debug_on THEN
2098 WSH_DEBUG_SV.push(l_module_name);
2099 --
2100 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
2101 END IF;
2102 --
2103 OPEN Get_Detail_Info;
2104
2105 FETCH Get_Detail_Info INTO
2106 det_item_id,
2107 tmp_net_wt,
2108 tmp_vol,
2109 wt_uom,
2110 vol_uom,
2111 det_org_id;
2112
2113 IF Get_Detail_Info%NOTFOUND THEN
2114 CLOSE Get_Detail_Info;
2115 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2116 FND_MESSAGE.SET_NAME('WSH','WSH_DET_INVALID_DETAIL_ID');
2117 FND_MESSAGE.SET_TOKEN('DETAIL_ID',p_delivery_detail_id);
2118 WSH_UTIL_CORE.Add_Message(x_return_status);
2119 --
2120 -- Debug Statements
2121 --
2122 IF l_debug_on THEN
2123 WSH_DEBUG_SV.pop(l_module_name);
2124 END IF;
2125 --
2126 return;
2127 END IF;
2128
2129 IF Get_Detail_Info%ISOPEN THEN
2130 CLOSE Get_Detail_Info;
2131 END IF;
2132
2133 IF wt_uom IS NULL OR vol_uom IS NULL THEN
2134 FND_MESSAGE.SET_NAME('WSH','WSH_DET_WV_UOM_NULL');
2135 FND_MESSAGE.SET_TOKEN('DETAIL_ID',p_delivery_detail_id);
2136 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2137 WSH_UTIL_CORE.Add_Message(x_return_status);
2138 --
2139 -- Debug Statements
2140 --
2141 IF l_debug_on THEN
2142 WSH_DEBUG_SV.pop(l_module_name);
2143 END IF;
2144 --
2145 return;
2146 END IF;
2147
2148 IF (tmp_net_wt IS NULL OR tmp_vol IS NULL) THEN
2149
2150 --
2151 -- Debug Statements
2152 --
2153 IF l_debug_on THEN
2154 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.DETAIL_WEIGHT_VOLUME',WSH_DEBUG_SV.C_PROC_LEVEL);
2155 END IF;
2156 --
2157 WSH_WV_UTILS.Detail_Weight_Volume (
2158 p_delivery_detail_id => p_delivery_detail_id,
2159 p_update_flag => 'N',
2160 x_net_weight => tmp_net_wt,
2161 x_volume => tmp_vol,
2162 x_return_status => l_ret_sts);
2163
2164 IF l_ret_sts <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2165 FND_MESSAGE.SET_NAME('WSH','WSH_DET_WT_VOL_FAILED');
2166 FND_MESSAGE.SET_TOKEN('DETAIL_ID',p_delivery_detail_id);
2167 l_ret_sts := WSH_UTIL_CORE.G_RET_STS_WARNING;
2168 WSH_UTIL_CORE.Add_Message(l_ret_sts);
2169 tmp_net_wt := 0;
2170 tmp_vol := 0;
2171 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2172 --
2173 -- Debug Statements
2174 --
2175 IF l_debug_on THEN
2176 WSH_DEBUG_SV.pop(l_module_name);
2177 END IF;
2178 --
2179 return;
2180 END IF;
2181
2182 END IF;
2183
2184 --
2185 -- Debug Statements
2186 --
2187 IF l_debug_on THEN
2188 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.GET_DEFAULT_UOMS',WSH_DEBUG_SV.C_PROC_LEVEL);
2189 END IF;
2190 --
2191 WSH_WV_UTILS.Get_Default_Uoms (
2192 det_org_id,
2193 wsh_wt_uom,
2194 wsh_vol_uom,
2195 l_ret_sts);
2196
2197 IF l_ret_sts <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2198 --
2199 -- Debug Statements
2200 --
2201 IF l_debug_on THEN
2202 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ORG_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
2203 END IF;
2204 --
2205 l_org_name := WSH_UTIL_CORE.Get_Org_Name(det_org_id);
2206 FND_MESSAGE.SET_NAME('WSH','WSH_DEFAULT_UOM_ERROR');
2207 FND_MESSAGE.SET_TOKEN('ORG_NAME',l_org_name);
2208 l_ret_sts := WSH_UTIL_CORE.G_RET_STS_WARNING;
2209 WSH_UTIL_CORE.Add_Message(l_ret_sts);
2210 wsh_wt_uom := wt_uom;
2211 wsh_vol_uom := vol_uom;
2212 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2213 END IF;
2214
2215 x_wt_uom := wsh_wt_uom;
2216 x_vol_uom := wsh_vol_uom;
2217
2218
2219 --
2220 -- Debug Statements
2221 --
2222 IF l_debug_on THEN
2223 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
2224 END IF;
2225 --
2226 net_wt := WSH_WV_UTILS.Convert_Uom (
2227 wt_uom,
2228 wsh_wt_uom,
2229 tmp_net_wt,
2230 det_item_id);
2231
2232 --
2233 -- Debug Statements
2234 --
2235 IF l_debug_on THEN
2236 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
2237 END IF;
2238 --
2239 vol := WSH_WV_UTILS.Convert_Uom (
2240 vol_uom,
2241 wsh_vol_uom,
2242 tmp_vol,
2243 det_item_id);
2244
2245
2246
2247 x_item_wt := nvl(net_wt,0);
2248 x_item_vol := nvl(vol,0);
2249
2250 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2251
2252 --
2253 -- Debug Statements
2254 --
2255 IF l_debug_on THEN
2256 WSH_DEBUG_SV.log(l_module_name,'x_item_wt',x_item_wt);
2257 WSH_DEBUG_SV.log(l_module_name,'x_item_vol',x_item_vol);
2258 WSH_DEBUG_SV.pop(l_module_name);
2259 END IF;
2260 --
2261 EXCEPTION
2262
2263 WHEN Others THEN
2264 WSH_UTIL_CORE.Default_Handler('WSH_DLVB_COMMON_ACTIONS.Calc_Item_Total');
2265 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2266
2267 --
2268 -- Debug Statements
2269 --
2270 IF l_debug_on THEN
2271 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2272 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2273 END IF;
2274 --
2275 END Calc_Item_Total;
2276
2277
2278
2279 /*
2280 -----------------------------------------------------------------------------
2281 PROCEDURE : Calculate_Total_Capacities
2282 PARAMETERS : p_detail_input_tab - table of ids in input; could be delivery
2283 lines or containers
2284 x_cont_wt_avail - total available weight capacity of all
2285 containers in the selection of input ids
2286 x_cont_vol_avail - total available volume capacity of all
2287 containers in the selection of input ids.
2288 x_item_wt_total - total weight of all lines in the input
2289 selection of ids.
2290 x_item_vol_total - total volume of all lines in the input
2291 selection of ids.
2292 x_wt_uom - weight uom code for above weights
2293 x_vol_uom - volume uom code for above volumes
2294 x_return_status - return status of API
2295 DESCRIPTION : This procedure takes as input a table of delivery details -
2296 both lines and containers and separates all container ids and
2297 delivery lines. It returns the available container weight and
2298 volume capacities and total weight and volume of lines.
2299
2300 ------------------------------------------------------------------------------
2301 */
2302
2303
2304 PROCEDURE Calculate_Total_Capacities (
2305 p_detail_input_tab IN WSH_UTIL_CORE.id_tab_type,
2306 x_cont_wt_avail OUT NOCOPY NUMBER,
2307 x_cont_vol_avail OUT NOCOPY NUMBER,
2308 x_item_wt_total OUT NOCOPY NUMBER,
2309 x_item_vol_total OUT NOCOPY NUMBER,
2310 x_wt_uom OUT NOCOPY VARCHAR2,
2311 x_vol_uom OUT NOCOPY VARCHAR2,
2312 x_return_status OUT NOCOPY VARCHAR2) IS
2313
2314
2315 l_cont_tab WSH_UTIL_CORE.id_tab_type;
2316 l_detail_tab WSH_UTIL_CORE.id_tab_type;
2317 l_error_tab WSH_UTIL_CORE.id_tab_type;
2318
2319 l_avail_wt NUMBER;
2320 l_avail_vol NUMBER;
2321 l_item_wt_total NUMBER;
2322 l_item_vol_total NUMBER;
2323
2324 l_wt_uom VARCHAR2(3);
2325 l_vol_uom VARCHAR2(3);
2326
2327 l_ret_sts VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2328
2329 l_cont_name VARCHAR2(30);
2330
2331 l_return_status VARCHAR2(1);
2332
2333 -- K LPN CONV. rv
2334 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
2335 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
2336 l_msg_count NUMBER;
2337 l_msg_data VARCHAR2(32767);
2338 e_return_excp EXCEPTION;
2339 -- K LPN CONV. rv
2340
2341 --
2342 l_debug_on BOOLEAN;
2343 --
2344 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CALCULATE_TOTAL_CAPACITIES';
2345 --
2346 BEGIN
2347
2348 --
2349 -- Debug Statements
2350 --
2351 --
2352 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2353 --
2354 IF l_debug_on IS NULL
2355 THEN
2356 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2357 END IF;
2358 --
2359 IF l_debug_on THEN
2360 WSH_DEBUG_SV.push(l_module_name);
2361 WSH_DEBUG_SV.log(l_module_name,'P_DETAIL_INPUT_TAB.COUNT',p_detail_input_tab.count);
2362 END IF;
2363 --
2364 IF p_detail_input_tab.count <= 0 THEN
2365 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2366 FND_MESSAGE.SET_NAME('WSH','WSH_INPUT_TABLE_NULL');
2367 WSH_UTIL_CORE.Add_Message(x_return_status);
2368 --
2369 -- Debug Statements
2370 --
2371 --IF l_debug_on THEN
2372 --WSH_DEBUG_SV.pop(l_module_name);
2373 --END IF;
2374 --
2375 --return;
2376 raise e_return_excp; -- LPN CONV. rv
2377 END IF;
2378
2379 --
2380 -- Debug Statements
2381 --
2382 IF l_debug_on THEN
2383 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DLVB_COMMON_ACTIONS.SEPARATE_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
2384 END IF;
2385 --
2386 WSH_DLVB_COMMON_ACTIONS.Separate_Details (
2387 p_detail_input_tab,
2388 l_cont_tab,
2389 l_detail_tab,
2390 l_error_tab,
2391 x_return_status);
2392
2393
2394
2395 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2396 FND_MESSAGE.SET_NAME('WSH','WSH_SEPARATE_DET_ERROR');
2397 WSH_UTIL_CORE.Add_Message(x_return_status);
2398 --
2399 -- Debug Statements
2400 --
2401 --IF l_debug_on THEN
2402 --WSH_DEBUG_SV.pop(l_module_name);
2403 --END IF;
2404 --
2405 --return;
2406 raise e_return_excp; -- LPN CONV. rv
2407 END IF;
2408
2409
2410 FOR i IN 1..l_cont_tab.count LOOP
2411
2412 --
2413 -- Debug Statements
2414 --
2415 IF l_debug_on THEN
2416 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DLVB_COMMON_ACTIONS.CALC_CONT_AVAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
2417 END IF;
2418 --
2419 WSH_DLVB_COMMON_ACTIONS.Calc_Cont_Avail (
2420 l_cont_tab(i),
2421 l_avail_wt,
2422 l_avail_vol,
2423 l_wt_uom,
2424 l_vol_uom,
2425 l_return_status);
2426
2427
2428 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2429 l_ret_sts := WSH_UTIL_CORE.G_RET_STS_WARNING;
2430 END IF;
2431
2432 x_cont_wt_avail := nvl(x_cont_wt_avail,0) + nvl(l_avail_wt,0);
2433 x_cont_vol_avail := nvl(x_cont_vol_avail,0) + nvl(l_avail_vol,0);
2434
2435 END LOOP;
2436
2437 -- x_item_wt_total := 0;
2438 -- x_item_vol_total := 0;
2439
2440 FOR i IN 1..l_detail_tab.count LOOP
2441
2442
2443 --
2444 -- Debug Statements
2445 --
2446 IF l_debug_on THEN
2447 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DLVB_COMMON_ACTIONS.CALC_ITEM_TOTAL',WSH_DEBUG_SV.C_PROC_LEVEL);
2448 END IF;
2449 --
2450 WSH_DLVB_COMMON_ACTIONS.Calc_Item_Total (
2451 l_detail_tab(i),
2452 l_item_wt_total,
2453 l_item_vol_total,
2454 l_wt_uom,
2455 l_vol_uom,
2456 l_return_status);
2457
2458
2459
2460 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2461 l_ret_sts := WSH_UTIL_CORE.G_RET_STS_WARNING;
2462 END IF;
2463
2464 x_item_wt_total := nvl(x_item_wt_total,0) + nvl(l_item_wt_total,0);
2465 x_item_vol_total := nvl(x_item_vol_total,0) + nvl(l_item_vol_total,0);
2466
2467 END LOOP;
2468 --
2469 -- K LPN CONV. rv
2470 --
2471 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
2472 THEN
2473 --{
2474 IF l_debug_on THEN
2475 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);
2476 END IF;
2477
2478 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
2479 (
2480 p_in_rec => l_lpn_in_sync_comm_rec,
2481 x_return_status => l_return_status,
2482 x_out_rec => l_lpn_out_sync_comm_rec
2483 );
2484 --
2485 --
2486 IF l_debug_on THEN
2487 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
2488 END IF;
2489 --
2490 --
2491 IF l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING)
2492 AND x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
2493 THEN
2494 x_return_status := l_return_status;
2495 END IF;
2496 --
2497 --}
2498 END IF;
2499 --
2500 -- K LPN CONV. rv
2501 --
2502
2503 x_wt_uom := l_wt_uom;
2504 x_vol_uom := l_vol_uom;
2505
2506 IF l_ret_sts <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2507 FND_MESSAGE.SET_NAME('WSH','WSH_CALC_CAPACITY_WARN');
2508 WSH_UTIL_CORE.Add_Message(l_ret_sts);
2509 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2510 ELSE
2511 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2512 END IF;
2513
2514 --
2515 -- Debug Statements
2516 --
2517 IF l_debug_on THEN
2518 WSH_DEBUG_SV.log(l_module_name,'x_item_wt_total',x_item_wt_total);
2519 WSH_DEBUG_SV.log(l_module_name,'x_item_vol_total',x_item_vol_total);
2520 WSH_DEBUG_SV.pop(l_module_name);
2521 END IF;
2522 --
2523 EXCEPTION
2524 --
2525 -- K LPN CONV. rv
2526 WHEN e_return_excp THEN
2527 --
2528 --
2529 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
2530 THEN
2531 --{
2532 IF l_debug_on THEN
2533 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);
2534 END IF;
2535
2536 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
2537 (
2538 p_in_rec => l_lpn_in_sync_comm_rec,
2539 x_return_status => l_return_status,
2540 x_out_rec => l_lpn_out_sync_comm_rec
2541 );
2542 --
2543 --
2544 IF l_debug_on THEN
2545 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
2546 END IF;
2547 --
2548 --
2549 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,WSH_UTIL_CORE.G_RET_STS_ERROR) and x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2550 x_return_status := l_return_status;
2551 END IF;
2552 --
2553 --}
2554 END IF;
2555 --
2556 -- K LPN CONV. rv
2557 --
2558 IF l_debug_on THEN
2559 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_RETURN_EXCP');
2560 END IF;
2561 --
2562 WHEN Others THEN
2563 WSH_UTIL_CORE.Default_Handler('WSH_DLVB_COMMON_ACTIONS.Calculate_Total_Capacities');
2564 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2565 IF l_debug_on THEN
2566 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2567 END IF;
2568 --
2569 -- K LPN CONV. rv
2570 --
2571 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
2572 THEN
2573 --{
2574 IF l_debug_on THEN
2575 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);
2576 END IF;
2577
2578 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
2579 (
2580 p_in_rec => l_lpn_in_sync_comm_rec,
2581 x_return_status => l_return_status,
2582 x_out_rec => l_lpn_out_sync_comm_rec
2583 );
2584 --
2585 --
2586 IF l_debug_on THEN
2587 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
2588 END IF;
2589 --
2590 --
2591 --}
2592 END IF;
2593 --
2594 -- K LPN CONV. rv
2595 --
2596
2597 --
2598 -- Debug Statements
2599 --
2600 IF l_debug_on THEN
2601 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2602 END IF;
2603 --
2604 END Calculate_Total_Capacities;
2605
2606
2607 END WSH_DLVB_COMMON_ACTIONS;