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