1 PACKAGE BODY WSH_CONTAINER_UTILITIES as
2 /* $Header: WSHCMUTB.pls 120.5 2006/10/30 23:13:42 wrudge noship $ */
3
4
5 /*
6 -----------------------------------------------------------------------------
7 FUNCTION : Get Master Cont Id
8 PARAMETERS : p_container_instance_id - instance id for the container
9 RETURNS : master container instance id
10 DESCRIPTION : This function derives the master container instance id
11 of the container by using a heirarchical SQL query on
12 wsh_delivery_assignments_v table. This function can be used in
13 SELECT statements that need to use the master container id.
14 ------------------------------------------------------------------------------
15 */
16
17
18 --
19 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_CONTAINER_UTILITIES';
20 --
21 FUNCTION Get_Master_Cont_Id (p_cont_instance_id IN NUMBER) RETURN NUMBER IS
22
23 l_mast_cont_id NUMBER;
24
25 CURSOR Get_Master_Cont (v_cont_inst_id NUMBER) IS
26 SELECT delivery_detail_id
27 FROM wsh_delivery_assignments
28 WHERE parent_delivery_detail_id IS NULL
29 AND NVL(type, 'S') in ('S', 'C')
30 START WITH delivery_detail_id = v_cont_inst_id
31 AND NVL(type, 'S') in ('S', 'C')
32 CONNECT BY PRIOR parent_delivery_detail_id = delivery_detail_id;
33
34 --
35 --
36 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_MASTER_CONT_ID';
37 --
38 BEGIN
39
40 --
41 --
42 --
43 OPEN Get_Master_Cont (p_cont_instance_id);
44
45 FETCH Get_Master_Cont INTO l_mast_cont_id;
46
47 IF Get_Master_Cont%NOTFOUND THEN
48 l_mast_cont_id := null;
49 END IF;
50
51 CLOSE Get_Master_Cont;
52
53 IF (l_mast_cont_id = p_cont_instance_id) THEN
54 l_mast_cont_id := NULL;
55 END IF;
56
57 --
58 RETURN l_mast_cont_id;
59
60 END Get_Master_Cont_Id;
61
62
63 /*
64 -----------------------------------------------------------------------------
65 FUNCTION : Get Cont Name
66 PARAMETERS : p_cont_instance_id - instance id for the container
67 RETURNS : container name for the container instance id
68 DESCRIPTION : This function derives the container name for the container id
69
70 ------------------------------------------------------------------------------
71 */
72
73
74 FUNCTION Get_Cont_Name (p_cont_instance_id IN NUMBER) RETURN VARCHAR2 IS
75
76 l_cont_name VARCHAR2(30);
77
78 CURSOR Get_Name (v_cont_inst_id NUMBER) IS
79 SELECT container_name
80 FROM WSH_DELIVERY_DETAILS
81 WHERE delivery_detail_id = v_cont_inst_id
82 AND container_flag in ('Y', 'C');
83
84 --
85 --
86 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CONT_NAME';
87 --
88 BEGIN
89
90 --
91 --
92 --
93 IF (p_cont_instance_id IS NULL) THEN
94 --
95 RETURN NULL;
96 END IF;
97
98 OPEN Get_Name (p_cont_instance_id);
99
100 FETCH Get_Name INTO l_cont_name;
101
102 IF Get_Name%NOTFOUND THEN
103 l_cont_name := null;
104 END IF;
105
106 CLOSE Get_Name;
107
108 --
109 RETURN l_cont_name;
110
111 END Get_Cont_Name;
112
113
114
115 /*
116 -----------------------------------------------------------------------------
117 PROCEDURE : Validate_Hazard_Class
118 PARAMETERS : p_delivery_detail_id - delivery detail id
119 p_container_instance_id - delivery detail id of container
120 x_return_status - return status of API
121 DESCRIPTION : This procedure retrieves the hazard class id of the delivery
122 detail id and checks if there is any incompatability or
123 special restrictions on packing the detail into the specified
124 container. Also checks to see if the hazard class for the
125 detail is incompatible with the other details already in the
126 container. It returns a success if there are no restrictions
127 and returns an error if there is any invalid hazard class.
128 ------------------------------------------------------------------------------
129 */
130
131
132 PROCEDURE Validate_Hazard_Class (
133 p_delivery_detail_id IN NUMBER,
134 p_container_instance_id IN NUMBER,
135 x_return_status OUT NOCOPY VARCHAR2) IS
136
137
138 --
139 l_debug_on BOOLEAN;
140 --
141 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_HAZARD_CLASS';
142 --
143 BEGIN
144
145 -- dummy API for now. will add validation code later on, once the hazard class
146 -- information is available.
147
148
149 --
150 -- Debug Statements
151 --
152 --
153 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
154 --
155 IF l_debug_on IS NULL
156 THEN
157 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
158 END IF;
159 --
160 IF l_debug_on THEN
161 WSH_DEBUG_SV.push(l_module_name);
162 --
163 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
164 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_INSTANCE_ID',P_CONTAINER_INSTANCE_ID);
165 END IF;
166 --
167 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
168
169 --
170 -- Debug Statements
171 --
172 IF l_debug_on THEN
173 WSH_DEBUG_SV.pop(l_module_name);
174 END IF;
175 --
176 END Validate_Hazard_Class;
177
178
179 /*
180 -----------------------------------------------------------------------------
181 PROCEDURE : Validate_Hold_Code
182 PARAMETERS : p_delivery_detail_id - delivery detail id
183 x_return_status - return status of API
184 DESCRIPTION : This procedure retrieves the hold code for the delivery detail
185 id and returns a success if there is no hold code and returns
186 an error if there is any invalid hold code.
187 ------------------------------------------------------------------------------
188 */
189
190
191 PROCEDURE Validate_Hold_Code (
192 p_delivery_detail_id IN NUMBER,
193 x_return_status OUT NOCOPY VARCHAR2) IS
194
195 --
196 l_debug_on BOOLEAN;
197 --
198 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_HOLD_CODE';
199 --
200 BEGIN
201
202 -- dummy API for now because the hold codes haven't been finalized. Add code
203 -- later on.
204
205 --
206 -- Debug Statements
207 --
208 --
209 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
210 --
211 IF l_debug_on IS NULL
212 THEN
213 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
214 END IF;
215 --
216 IF l_debug_on THEN
217 WSH_DEBUG_SV.push(l_module_name);
218 --
219 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
220 END IF;
221 --
222 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
223
224 --
225 -- Debug Statements
226 --
227 IF l_debug_on THEN
228 WSH_DEBUG_SV.pop(l_module_name);
229 END IF;
230 --
231 END Validate_Hold_Code;
232
233 -- Bug 2381184
234 -- Note there are 2 api with same name of estimate detail container
235 -- the name is same to keep the changes consistent in both.
236 -- work to call one of them and have single place for code.
237 /*
238 -----------------------------------------------------------------------------
239 PROCEDURE : Estimate Detail Containers
240 PARAMETERS : p_container_instance_id - instance id for the container
241 x_container_item_id - container item for estimation
242 p_delivery_detail_id - the delivery detail id for which the
243 number of containers is being estimated
244 p_organization_id - organization_id
245 x_num_cont - number of containers required to pack the line.
246 x_return_status - return status of API
247 x_max_qty_per_lpn - returns maximum quantity per LPN
248 x_fill_pc_per_lpn - returns fill percent per LPN for Q,W or V
249 DESCRIPTION : This procedure estimates the number of detail containers that
250 would be required to pack a delivery detail. The container
251 item could be specified or if it is not specified, it is
252 derived from the delivery detail or through the container load
253 relationship. Using the inventory item and quantity on the
254 detail and the container item, the number of containers is
255 calculated/estimated.
256 THIS IS USED ONLY FROM PACK_EMPTY_CONTS as of now.
257 ------------------------------------------------------------------------------
258 */
259
260 PROCEDURE Estimate_Detail_Containers(
261 p_in_record IN inrectype,
262 x_inout_record IN OUT NOCOPY inoutrectype,
263 x_out_record OUT NOCOPY outrectype,
264 x_return_status OUT NOCOPY VARCHAR2
265 ) IS
266
267 CURSOR Get_Cont_Load (v_cont_id NUMBER, v_inv_item_id NUMBER) IS
268 SELECT max_load_quantity, container_item_id
269 FROM WSH_CONTAINER_ITEMS
270 WHERE container_item_id = NVL(v_cont_id, container_item_id)
271 AND load_item_id = v_inv_item_id
272 AND master_organization_id = p_in_record.organization_id
273 AND preferred_flag = DECODE(nvl(v_cont_id,-99),-99,'Y',preferred_flag);
274
275 CURSOR get_flag_value (v_inv_item_id NUMBER, v_org_id NUMBER) IS
276 SELECT indivisible_flag
277 FROM mtl_system_items
278 WHERE organization_id = v_org_id
279 AND inventory_item_id = v_inv_item_id;
280
281 CURSOR Get_Delivery_Details (v_del_detail_id NUMBER) IS
282 SELECT delivery_detail_id, inventory_item_id, item_description,
283 nvl(shipped_quantity,
284 NVL(picked_quantity, requested_quantity)) packed_qty,
285 requested_quantity_uom,
286 master_container_item_id, detail_container_item_id, hold_code,
287 load_seq_number, net_weight,
288 weight_uom_code, volume, volume_uom_code, organization_id
289 FROM WSH_DELIVERY_DETAILS
290 WHERE delivery_detail_id = v_del_detail_id
291 AND container_flag = 'N';
292
293 CURSOR Get_Container_Info (v_cont_instance_id NUMBER, v_org_id NUMBER)IS
294 SELECT delivery_detail_id container_instance_id, container_name lpn,
295 inventory_item_id container_item_id, item_description,
296 gross_weight, net_weight, (gross_weight - net_weight), weight_uom_code,
297 volume, volume_uom_code, fill_percent, maximum_load_weight,
298 maximum_volume, minimum_fill_percent
299 FROM WSH_DELIVERY_DETAILS
300 WHERE delivery_detail_id = v_cont_instance_id
301 AND organization_id = v_org_id
302 AND container_flag in ('Y', 'C');
303
304 CURSOR Get_Cont_Msi (v_cont_item_id NUMBER, v_org_id NUMBER) IS
305 SELECT maximum_load_weight, internal_volume, weight_uom_code, volume_uom_code
306 FROM MTL_SYSTEM_ITEMS
307 WHERE inventory_item_id = v_cont_item_id
308 AND organization_id = v_org_id;
309
310 CURSOR Get_Fill_Basis (v_org_id NUMBER) IS
311 SELECT percent_fill_basis_flag
312 FROM WSH_SHIPPING_PARAMETERS
313 WHERE organization_id = v_org_id;
314
315 CURSOR get_cont_fill_qty (v_container_instance_id NUMBER) IS
316 SELECT sum(nvl(wdd.shipped_quantity,
317 NVL(wdd.picked_quantity, wdd.requested_quantity))) packed_qty
318 FROM wsh_delivery_details wdd
319 WHERE wdd.delivery_detail_id IN
320 (SELECT wda.delivery_detail_id
321 FROM wsh_delivery_assignments_v wda
322 WHERE wda.parent_delivery_detail_id IS NOT NULL
323 AND wda.parent_delivery_detail_id = v_container_instance_id)
324 AND wdd.container_flag ='N';
325
326 l_cont_gross NUMBER;
327 l_cont_net NUMBER;
328 l_cont_tare NUMBER;
329 l_cont_weight_uom VARCHAR2(3);
330 l_cont_volume NUMBER;
331 l_cont_vol_uom VARCHAR2(3);
332 l_lpn VARCHAR2(30);
333 l_par_cont_id NUMBER;
334 l_cont_item_id NUMBER;
335 l_cont_description VARCHAR2(240);
336 l_fill_pc NUMBER;
337 l_max_load_wt NUMBER;
338 l_min_fill_pc NUMBER;
339 l_max_volume NUMBER;
340 l_max_load_qty NUMBER;
341 l_dd_master_cont_id NUMBER;
342 l_dd_det_cont_id NUMBER;
343 l_dd_inv_item_id NUMBER;
344 l_dd_inv_item_desc VARCHAR2(240);
345 l_dd_packed_qty NUMBER;
346 l_dd_hold_flag VARCHAR2(1);
347 l_dd_gross_wt NUMBER;
348 l_dd_net_wt NUMBER;
349 l_dd_tare_wt NUMBER;
350 l_dd_volume NUMBER;
351 l_dd_vol_uom VARCHAR2(3);
352 l_dd_wt_uom VARCHAR2(3);
353 l_dd_req_qty_uom VARCHAR2(3);
354 l_wcl_cont_item_id NUMBER;
355 l_dd_org_id NUMBER;
356 l_cont_org_id NUMBER;
357 l_del_det_id NUMBER;
358 l_cont_instance_id NUMBER;
359 l_dd_load_seq_num NUMBER;
360 l_num_cont NUMBER;
361 l_mtl_wt_uom VARCHAR2(3);
362 l_mtl_vol_uom VARCHAR2(3);
363 l_mtl_max_load NUMBER;
364 l_mtl_max_vol NUMBER;
365 l_fill_pc_basis VARCHAR2(1);
366
367 l_item_name VARCHAR2(2000);
368 l_org_name VARCHAR2(240);
369
370 -- get temporary count of number of containers required
371 l_tmp_num_cont NUMBER;
372 l_discard_message VARCHAR2(2000);
373 l_output_qty NUMBER;
374 l_return_status VARCHAR2(3);
375 l_item_indivisible VARCHAR2(1) := 'N';
376 l_max_qty_per_lpn NUMBER := 0;
377 l_fill_pc_per_lpn NUMBER := 0;
378
379 --the values can be Q1(with preferred container), Q2(without preferred container), W or V
380 l_fill_pc_flag VARCHAR2(2) := 'Q1';
381 l_dd_numerator NUMBER;
382 -- make precision constant thru out the packing code
383 LIMITED_PRECISION NUMBER := 8;
384
385
386 --
387 l_debug_on BOOLEAN;
388 --
389 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ESTIMATE_DETAIL_CONTAINERS';
390 --
391 BEGIN
392
393 -- dbms_output.put_line('in estimating detail containers');
394 --
395 -- Debug Statements
396 --
397 --
398 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
399 --
400 IF l_debug_on IS NULL
401 THEN
402 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
403 END IF;
404 --
405 IF l_debug_on THEN
406 WSH_DEBUG_SV.push(l_module_name);
407 END IF;
408 --
409 x_out_record.max_qty_per_lpn := 0;
410 x_out_record.fill_pc_per_lpn := 0;
411
412 OPEN Get_Delivery_Details (p_in_record.delivery_detail_id);
413
414 FETCH Get_Delivery_Details INTO
415 l_del_det_id,
416 l_dd_inv_item_id,
417 l_dd_inv_item_desc,
418 l_dd_packed_qty,
419 l_dd_req_qty_uom,
420 l_dd_master_cont_id,
421 l_dd_det_cont_id,
422 l_dd_hold_flag,
423 l_dd_load_seq_num,
424 l_dd_net_wt,
425 l_dd_wt_uom,
426 l_dd_volume,
427 l_dd_vol_uom,
428 l_dd_org_id;
429
430
431 IF (Get_Delivery_Details%NOTFOUND) THEN
432 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
433 -- dbms_output.put_line('no delivery details');
434 FND_MESSAGE.SET_NAME('WSH','WSH_DET_INVALID_DETAIL');
435 FND_MESSAGE.SET_TOKEN('DETAIL_ID',p_in_record.delivery_detail_id);
436 CLOSE Get_Delivery_Details;
437 WSH_UTIL_CORE.Add_Message(x_return_status);
438 --
439 -- Debug Statements
440 --
441 IF l_debug_on THEN
442 WSH_DEBUG_SV.pop(l_module_name);
443 END IF;
444 --
445 return;
446 END IF;
447
448 CLOSE Get_Delivery_Details;
449
450 x_inout_record.container_item_id := NVL(NVL(l_dd_det_cont_id, l_dd_master_cont_id), x_inout_record.container_item_id);
451
452 --dbms_output.put_line('x cont item is ' || x_inout_record.container_item_id || ' and inv item is ' || l_dd_inv_item_id || ' and org is ' || p_in_record.organization_id);
456 FETCH Get_Cont_Load INTO
453
454 OPEN Get_Cont_Load (x_inout_record.container_item_id, l_dd_inv_item_id);
455
457 l_max_load_qty,
458 l_wcl_cont_item_id;
459
460 IF (Get_Cont_Load%NOTFOUND AND x_inout_record.container_item_id IS NULL) THEN
461 --
462 -- Debug Statements
463 --
464 IF l_debug_on THEN
465 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ITEM_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
466 END IF;
467 --
468 l_item_name := WSH_UTIL_CORE.Get_Item_Name(l_dd_inv_item_id,l_dd_org_id);
469 --
470 -- Debug Statements
471 --
472 IF l_debug_on THEN
473 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ORG_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
474 END IF;
475 --
476 l_org_name := WSH_UTIL_CORE.Get_Org_Name(p_in_record.organization_id);
477 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_CONT_LOAD');
478 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
479 FND_MESSAGE.SET_TOKEN('ORG_NAME',l_org_name);
480 -- dbms_output.put_line('error in get cont load');
481 CLOSE Get_Cont_Load;
482 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
483 WSH_UTIL_CORE.Add_Message(x_return_status);
484 --
485 -- Debug Statements
486 --
487 IF l_debug_on THEN
488 WSH_DEBUG_SV.pop(l_module_name);
489 END IF;
490 --
491 return;
492 END IF;
493
494 CLOSE Get_Cont_Load;
495
496 x_inout_record.container_item_id := NVL(x_inout_record.container_item_id, l_wcl_cont_item_id);
497
498 -- dbms_output.put_line('before check for p_container_instance_id');
499
500 IF (p_in_record.container_instance_id IS NOT NULL) THEN
501
502 OPEN Get_Container_Info(p_in_record.container_instance_id, nvl(p_in_record.organization_id,l_dd_org_id));
503
504 FETCH Get_Container_Info INTO
505 l_cont_instance_id,
506 l_lpn,
507 l_cont_item_id,
508 l_cont_description,
509 l_cont_gross,
510 l_cont_net,
511 l_cont_tare,
512 l_cont_weight_uom,
513 l_cont_volume,
514 l_cont_vol_uom,
515 l_fill_pc,
516 l_max_load_wt,
517 l_max_volume,
518 l_min_fill_pc;
519
520
521 IF Get_Container_Info%NOTFOUND THEN
522 -- it means that no container load defined. may need to get it from
523 -- container instances table.
524 -- if container isntance id also not defined then
525 -- raise container_item_error;
526 CLOSE Get_Container_Info;
527 -- dbms_output.put_line('container not found');
528 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
529 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_CONTAINER');
530 FND_MESSAGE.SET_TOKEN('CONT_NAME',l_lpn);
531 WSH_UTIL_CORE.Add_Message(x_return_status);
532 --
533 -- Debug Statements
534 --
535 IF l_debug_on THEN
536 WSH_DEBUG_SV.pop(l_module_name);
537 END IF;
538 --
539 return;
540
541 END IF;
542
543 CLOSE Get_Container_Info;
544
545 x_inout_record.container_item_id := NVL(x_inout_record.container_item_id,l_cont_item_id);
546
547 END IF;
548
549 -- dbms_output.put_line('after container instance check');
550
551 IF (x_inout_record.container_item_id IS NULL) THEN
552 -- it means that no container defined for this item type
553 -- raise container_item_error;
554 -- FND_MESSAGE.SET_NAME('WSH','WSH_CONT_EST_ERROR');
555 -- dbms_output.put_line('no container item');
556 -- WSH_UTIL_CORE.Add_Message(x_return_status);
557 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
558 --
559 -- Debug Statements
560 --
561 IF l_debug_on THEN
562 WSH_DEBUG_SV.pop(l_module_name);
563 END IF;
564 --
565 return;
566 ELSE
567
568 OPEN Get_Cont_Msi(x_inout_record.container_item_id, nvl(p_in_record.organization_id,l_dd_org_id));
569
570 FETCH Get_Cont_Msi INTO
571 l_mtl_max_load,
572 l_mtl_max_vol,
573 l_mtl_wt_uom,
574 l_mtl_vol_uom;
575
576 IF Get_Cont_Msi%NOTFOUND THEN
577 -- dbms_output.put_line('no cont_item in mtl_system_items');
578 --
579 -- Debug Statements
580 --
581 IF l_debug_on THEN
582 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ITEM_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
583 END IF;
584 --
585 l_item_name := WSH_UTIL_CORE.Get_Item_Name(x_inout_record.container_item_id,nvl(p_in_record.organization_id,l_dd_org_id));
586 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_INV_ITEM');
587 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
588 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
589 CLOSE Get_Cont_Msi;
590 WSH_UTIL_CORE.Add_Message(x_return_status);
591 --
592 -- Debug Statements
593 --
594 IF l_debug_on THEN
595 WSH_DEBUG_SV.pop(l_module_name);
596 END IF;
597 --
598 return;
599 END IF;
600
601 CLOSE Get_Cont_Msi;
602
603
607 -- Debug Statements
604 -- dbms_output.put_line('max load wt before convert is ' || l_mtl_max_load || 'wt uom ' || l_mtl_wt_uom || ' cont wt uom ' || l_cont_weight_uom);
605
606 --
608 --
609 IF l_debug_on THEN
610 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
611 END IF;
612 --
613 l_max_load_wt := NVL(l_max_load_wt, WSH_WV_UTILS.Convert_Uom (
614 l_mtl_wt_uom,
615 nvl(l_cont_weight_uom,l_mtl_wt_uom),
616 l_mtl_max_load,
617 x_inout_record.container_item_id));
618
619 --
620 -- Debug Statements
621 --
622 IF l_debug_on THEN
623 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
624 END IF;
625 --
626 l_max_volume := NVL(l_max_volume, WSH_WV_UTILS.Convert_Uom (
627 l_mtl_vol_uom,
628 nvl(l_cont_vol_uom,l_mtl_vol_uom),
629 l_mtl_max_vol,
630 x_inout_record.container_item_id));
631
632 END IF;
633
634 OPEN Get_Fill_Basis (nvl(p_in_record.organization_id,l_dd_org_id));
635
636 FETCH Get_Fill_Basis INTO l_fill_pc_basis;
637
638 IF Get_Fill_Basis%NOTFOUND THEN
639 --
640 -- Debug Statements
641 --
642 IF l_debug_on THEN
643 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ORG_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
644 END IF;
645 --
646 l_org_name := WSH_UTIL_CORE.Get_Org_Name(p_in_record.organization_id);
647 FND_MESSAGE.SET_NAME('WSH','WSH_FILL_BASIS_ERROR');
648 FND_MESSAGE.SET_TOKEN('ORG_NAME',l_org_name);
649 -- dbms_output.put_line('fill percent not defined');
650 CLOSE Get_Fill_Basis;
651 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
652 WSH_UTIL_CORE.Add_Message(x_return_status);
653 --
654 -- Debug Statements
655 --
656 IF l_debug_on THEN
657 WSH_DEBUG_SV.pop(l_module_name);
658 END IF;
659 --
660 return;
661 END IF;
662
663 CLOSE Get_Fill_Basis;
664
665
666 OPEN Get_Cont_Load (x_inout_record.container_item_id, l_dd_inv_item_id);
667
668 FETCH Get_Cont_Load INTO
669 l_max_load_qty,
670 l_wcl_cont_item_id;
671
672
673 IF Get_Cont_Load%FOUND AND l_fill_pc_basis = 'Q' THEN
674 -- decided to return decimal number of containers and manage the actual
675 -- number during the creation of the containers.
676
677 --dbms_output.put_line('fill percent is Q');
678 IF l_max_load_qty <> 0 THEN
679
680 l_num_cont := (l_dd_packed_qty / l_max_load_qty);
681
682
683 l_fill_pc_flag := 'Q1';
684
685 --dbms_output.put_line('l_num_cont in Qty set up'||l_num_cont);
686 CLOSE Get_Cont_Load;
687
688 ELSE
689
690 x_out_record.num_cont := 0;
691 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_LOAD_QTY_ERROR');
692 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
693 WSH_UTIL_CORE.Add_Message(x_return_status);
694 --
695 -- Debug Statements
696 --
697 IF l_debug_on THEN
698 WSH_DEBUG_SV.pop(l_module_name);
699 END IF;
700 --
701 return;
702
703 END IF;
704
705 ELSE
706
707 CLOSE Get_Cont_Load;
708
709 -- removed the CEIL from the expressions to check for exact numbers;
710
711 -- dbms_output.put_line('net-weight before convert is ' || l_dd_net_wt);
712
713 --
714 -- Debug Statements
715 --
716 IF l_debug_on THEN
717 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
718 END IF;
719 --
720 l_dd_net_wt := WSH_WV_UTILS.Convert_Uom (
721 l_dd_wt_uom,
722 nvl(l_cont_weight_uom,l_dd_wt_uom),
723 l_dd_net_wt,
724 l_dd_inv_item_id);
725
726
727 -- dbms_output.put_line('net_weight after convert is ' || l_dd_net_wt);
728 -- dbms_output.put_line('max load weight is ' || l_max_load_wt);
729
730 --
731 -- Debug Statements
732 --
733 IF l_debug_on THEN
734 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
735 END IF;
736 --
737 l_dd_volume := WSH_WV_UTILS.Convert_Uom (
738 l_dd_vol_uom,
739 nvl(l_cont_vol_uom,l_dd_vol_uom),
740 l_dd_volume,
741 l_dd_inv_item_id);
742
743
744 IF l_fill_pc_basis = 'W' THEN
745 --dbms_output.put_line('using weights' || l_dd_net_wt || ' and ' || l_max_load_wt);
746
747 IF l_max_load_wt > 0 THEN
748 l_num_cont := (l_dd_net_wt/l_max_load_wt);
749 l_fill_pc_flag := 'W';
750 ELSE
751 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_MAX_LOAD_ERROR');
752 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
753 WSH_UTIL_CORE.Add_Message(x_return_status);
754 x_out_record.num_cont := 0;
755 --
756 -- Debug Statements
757 --
758 IF l_debug_on THEN
759 WSH_DEBUG_SV.pop(l_module_name);
763 END IF;
760 END IF;
761 --
762 return;
764
765 ELSIF l_fill_pc_basis = 'V' THEN
766 --dbms_output.put_line('using volume' || l_dd_volume || ' and ' || l_max_volume);
767
768 IF l_max_volume > 0 THEN
769 l_num_cont := (l_dd_volume/l_max_volume);
770 l_fill_pc_flag := 'V';
771 ELSE
772 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_MAX_VOL_ERROR');
773 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
774 WSH_UTIL_CORE.Add_Message(x_return_status);
775 x_out_record.num_cont := 0;
776 --
777 -- Debug Statements
778 --
779 IF l_debug_on THEN
780 WSH_DEBUG_SV.pop(l_module_name);
781 END IF;
782 --
783 return;
784 END IF;
785
786 ELSIF l_fill_pc_basis = 'Q' THEN
787
788 IF l_max_load_wt > 0 AND l_max_volume > 0 THEN
789
790 IF ((l_dd_net_wt/l_max_load_wt) >= (l_dd_volume/l_max_volume)) THEN
791 l_num_cont := (l_dd_net_wt/l_max_load_wt);
792 l_fill_pc_flag := 'W';
793 ELSE
794 l_num_cont := (l_dd_volume/l_max_volume);
795 l_fill_pc_flag := 'V';
796 END IF;
797 --dbms_output.put_line('using qty with NO CONTAINER LOAD' || l_num_cont);
798 ELSE
799 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_MAX_WT_ERROR');
800 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
801 WSH_UTIL_CORE.Add_Message(x_return_status);
802 x_out_record.num_cont := 0;
803 --
804 -- Debug Statements
805 --
806 IF l_debug_on THEN
807 WSH_DEBUG_SV.pop(l_module_name);
808 END IF;
809 --
810 return;
811 END IF;
812 ELSE
813 --
814 -- Debug Statements
815 --
816 IF l_debug_on THEN
817 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ORG_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
818 END IF;
819 --
820 l_org_name := WSH_UTIL_CORE.Get_Org_Name(nvl(p_in_record.organization_id,l_dd_org_id));
821 FND_MESSAGE.SET_NAME('WSH','WSH_FILL_BASIS_ERROR');
822 FND_MESSAGE.SET_TOKEN('ORG_NAME',l_org_name);
823 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
824 WSH_UTIL_CORE.Add_Message(x_return_status);
825 --
826 -- Debug Statements
827 --
828 IF l_debug_on THEN
829 WSH_DEBUG_SV.pop(l_module_name);
830 END IF;
831 --
832 return;
833 END IF;
834
835
836 END IF;
837
838
839 -- bug 2443162: avoid division by zero if the item's
840 -- weight or volume value or max load quantity is zero.
841 -- We also should catch the case when value is NULL.
842 -- (This is the overloaded procedure with records as parameters.)
843 IF NVL(l_num_cont, 0) = 0 THEN
844 FND_MESSAGE.SET_NAME('WSH', 'WSH_NULL_WEIGHT_VOLUME');
845 FND_MESSAGE.SET_TOKEN('DELIVERY_DETAIL',p_in_record.delivery_detail_id);
846 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
847 WSH_UTIL_CORE.Add_Message(x_return_status);
848 x_out_record.num_cont := 0;
849 --
850 -- Debug Statements
851 --
852 IF l_debug_on THEN
853 WSH_DEBUG_SV.pop(l_module_name);
854 END IF;
855 --
856 return;
857 END IF;
858
859
860 -- This check is required for Bug 2393568
861
862 -- extra code added here to get the value of indivisible flag
863 -- since the check_decimal_quantity was not returning correct.
864
865 OPEN get_flag_value(l_dd_inv_item_id,l_dd_org_id);
866 FETCH get_flag_value
867 INTO l_item_indivisible;
868 IF get_flag_value%NOTFOUND THEN
869 CLOSE get_flag_value;
870 ELSE
871 CLOSE get_flag_value;
872 END IF;
873
874 --
875 -- Debug Statements
876 --
877 IF l_debug_on THEN
878 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DETAILS_VALIDATIONS.CHECK_DECIMAL_QUANTITY',WSH_DEBUG_SV.C_PROC_LEVEL);
879 END IF;
880 --
881 WSH_DETAILS_VALIDATIONS.Check_Decimal_Quantity (
882 l_dd_inv_item_id,
883 l_dd_org_id,
884 ROUND((l_dd_packed_qty/l_num_cont),LIMITED_PRECISION),
885 l_dd_req_qty_uom,
886 l_output_qty,
887 l_return_status);
888
889
890 --dbms_output.put_line('Return Status after CHECK DECIMAL'||l_return_status);
891 -- errors likely mean integers are safe values.
892 -- or if item indivisible flag is set to Y
893 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS OR
894 l_item_indivisible = 'Y'
895 )THEN
896 l_discard_message := FND_MESSAGE.GET;
897
898 l_tmp_num_cont := FLOOR(l_dd_packed_qty/l_num_cont);
899
900 IF (l_tmp_num_cont = 0) THEN
901 -- Bug#: 2503937 - New Error Message
905 x_out_record.num_cont := 0;
902 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_MAX_WT_VOL_ERROR');
903 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
904 WSH_UTIL_CORE.Add_Message(x_return_status);
906 --
907 -- Debug Statements
908 --
909 IF l_debug_on THEN
910 WSH_DEBUG_SV.pop(l_module_name);
911 END IF;
912 --
913 return;
914 END IF;
915
916 l_num_cont := (l_dd_packed_qty/l_tmp_num_cont);
917 END IF;
918
919 -- End of check is required for Bug 2393568
920
921 -- bug 1748609: maximize precision available to get accurate packing
922 x_out_record.num_cont := l_num_cont;
923 --dbms_output.put_line('x_num_cont'||x_out_record.num_cont||'>'||l_num_cont||'>'||l_item_indivisible);
924
925 --USE A FLAG TO SET if the item is indivisible or not - l_item_indivisible
926 -- l_fill_pc_flag is set to W,Q1 or V, in case of Quantity with no preferred
927 -- container, it will be either W or V, so just use that
928
929 --Based on what ever the fill percent is
930 -- Calculate x_max_qty_per_lpn and x_fill_pc_per_lpn
931
932 -- for Quantity
933 -- maximum quantity from container load
934 -- if item is indivisible , FLOOR the max quantity per LPN
935 -- fill % = 100
936 -- else from weight and volume use the better precision
937
938 IF l_fill_pc_flag = 'Q1' THEN
939
940 l_max_qty_per_lpn := l_max_load_qty;
941 l_fill_pc_per_lpn := 100; -- for Quantity it will be 100 %
942
943 --dbms_output.put_line('before floor Max QTY PER LPN is Q -'||l_max_qty_per_lpn);
944 -- for indivisible items need to floor
945 IF l_item_indivisible = 'Y' THEN
946 l_max_qty_per_lpn := FLOOR(l_max_qty_per_lpn);
947 END IF;
948
949 --dbms_output.put_line('Max QTY PER LPN is Q -'||l_max_qty_per_lpn);
950 --dbms_output.put_line('Fill pc PER LPN is Q -'||l_fill_pc_per_lpn);
951
952 -- for weight
953 -- maximum quantity depending on item weight and max_load_weight
954 -- use max quantity to determin fil %
955 -- if item is indivisible, then fill % =
956 -- ((max_qty(total weight/requested_qty))/(max_load_weight))
957 --
958 ELSIF l_fill_pc_flag = 'W' THEN
959 l_max_qty_per_lpn := (l_max_load_wt/(l_dd_net_wt/l_dd_packed_qty));
960 IF l_item_indivisible = 'Y' THEN
961 l_max_qty_per_lpn := FLOOR(l_max_qty_per_lpn);
962 END IF;
963 --dbms_output.put_line('Max QTY PER LPN is W -'||l_max_qty_per_lpn);
964 l_fill_pc_per_lpn := ((l_max_qty_per_lpn*(l_dd_net_wt/l_dd_packed_qty))/(l_max_load_wt));
965 --dbms_output.put_line('Fill pc PER LPN is W -'||l_fill_pc_per_lpn);
966
967 -- for volume
968 -- maximum quantity depending on item volume and internal_volume
969 -- use max quantity to determin fil %
970 -- if item is indivisible, then fill % =
971 -- ((max_qty(total volume/requested_qty))/(internal volume))
972 --
973 ELSIF l_fill_pc_flag = 'V' THEN
974 l_max_qty_per_lpn := (l_max_volume/(l_dd_volume/l_dd_packed_qty));
975 IF l_item_indivisible = 'Y' THEN
976 l_max_qty_per_lpn := FLOOR(l_max_qty_per_lpn);
977 END IF;
978 l_fill_pc_per_lpn := ((l_max_qty_per_lpn*(l_dd_volume/l_dd_packed_qty))/(l_max_volume));
979 --dbms_output.put_line('Max QTY PER LPN is V -'||l_max_qty_per_lpn);
980 --dbms_output.put_line('Fill pc PER LPN is V -'||l_fill_pc_per_lpn);
981
982 END IF;
983
984 x_out_record.max_qty_per_lpn := l_max_qty_per_lpn;
985 x_out_record.fill_pc_per_lpn := l_fill_pc_per_lpn;
986 x_out_record.indivisible_flag := l_item_indivisible;
987 x_out_record.fill_pc_flag := l_fill_pc_basis;
988
989 IF x_out_record.num_cont <= 0 THEN
990 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
991 ELSE
992 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
993 END IF;
994
995 --
996 -- Debug Statements
997 --
998 IF l_debug_on THEN
999 WSH_DEBUG_SV.pop(l_module_name);
1000 END IF;
1001 --
1002 EXCEPTION
1003
1004 WHEN Others THEN
1005 WSH_UTIL_CORE.Default_Handler('WSH_CONTAINER_UTILITIES.Estimate_Detail_Containers(2)');
1006 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1007
1008 --
1009 -- Debug Statements
1010 --
1011 IF l_debug_on THEN
1012 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1013 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1014 END IF;
1015 --
1016 END Estimate_Detail_Containers;
1017 -- end bug 2381184 adding new Estimate_Detail_Containers
1018
1019 /*
1020 -----------------------------------------------------------------------------
1021 PROCEDURE : Estimate Detail Containers
1022 PARAMETERS : p_container_instance_id - instance id for the container
1023 x_container_item_id - container item for estimation
1024 p_delivery_detail_id - the delivery detail id for which the
1025 number of containers is being estimated
1026 p_organization_id - organization_id
1027 x_num_cont - number of containers required to pack the line.
1028 x_return_status - return status of API
1032 derived from the delivery detail or through the container load
1029 DESCRIPTION : This procedure estimates the number of detail containers that
1030 would be required to pack a delivery detail. The container
1031 item could be specified or if it is not specified, it is
1033 relationship. Using the inventory item and quantity on the
1034 detail and the container item, the number of containers is
1035 calculated/estimated.
1036 ------------------------------------------------------------------------------
1037 */
1038
1039
1040 PROCEDURE Estimate_Detail_Containers(
1041 p_container_instance_id IN NUMBER DEFAULT NULL,
1042 x_container_item_id IN OUT NOCOPY NUMBER,
1043 p_delivery_detail_id IN NUMBER,
1044 p_organization_id IN NUMBER,
1045 x_num_cont IN OUT NOCOPY NUMBER,
1046 x_return_status OUT NOCOPY VARCHAR2) IS
1047
1048 CURSOR Get_Cont_Load (v_cont_id NUMBER, v_inv_item_id NUMBER) IS
1049 SELECT max_load_quantity, container_item_id
1050 FROM WSH_CONTAINER_ITEMS
1051 WHERE container_item_id = NVL(v_cont_id, container_item_id)
1052 AND load_item_id = v_inv_item_id
1053 AND master_organization_id = p_organization_id
1054 AND preferred_flag = DECODE(nvl(v_cont_id,-99),-99,'Y',preferred_flag);
1055
1056 CURSOR Get_Delivery_Details (v_del_detail_id NUMBER) IS
1057 SELECT delivery_detail_id, inventory_item_id, item_description,
1058 nvl(shipped_quantity,
1059 NVL(picked_quantity, requested_quantity)) packed_qty,
1060 requested_quantity_uom,
1061 master_container_item_id, detail_container_item_id, hold_code,
1062 load_seq_number, net_weight,
1063 weight_uom_code, volume, volume_uom_code, organization_id
1064 FROM WSH_DELIVERY_DETAILS
1065 WHERE delivery_detail_id = v_del_detail_id
1066 AND container_flag = 'N';
1067
1068 CURSOR Get_Container_Info (v_cont_instance_id NUMBER, v_org_id NUMBER)IS
1069 SELECT delivery_detail_id container_instance_id, container_name lpn,
1070 inventory_item_id container_item_id, item_description,
1071 gross_weight, net_weight, (gross_weight - net_weight), weight_uom_code,
1072 volume, volume_uom_code, fill_percent, maximum_load_weight,
1073 maximum_volume, minimum_fill_percent
1074 FROM WSH_DELIVERY_DETAILS
1075 WHERE delivery_detail_id = v_cont_instance_id
1076 AND organization_id = v_org_id
1077 AND container_flag in ('Y', 'C');
1078
1079 CURSOR Get_Cont_Msi (v_cont_item_id NUMBER, v_org_id NUMBER) IS
1080 SELECT maximum_load_weight, internal_volume, weight_uom_code, volume_uom_code
1081 FROM MTL_SYSTEM_ITEMS
1082 WHERE inventory_item_id = v_cont_item_id
1083 AND organization_id = v_org_id;
1084
1085 CURSOR Get_Fill_Basis (v_org_id NUMBER) IS
1086 SELECT percent_fill_basis_flag
1087 FROM WSH_SHIPPING_PARAMETERS
1088 WHERE organization_id = v_org_id;
1089
1090 l_cont_gross NUMBER;
1091 l_cont_net NUMBER;
1092 l_cont_tare NUMBER;
1093 l_cont_weight_uom VARCHAR2(3);
1094 l_cont_volume NUMBER;
1095 l_cont_vol_uom VARCHAR2(3);
1096 l_lpn VARCHAR2(30);
1097 l_par_cont_id NUMBER;
1098 l_cont_item_id NUMBER;
1099 l_cont_description VARCHAR2(240);
1100 l_fill_pc NUMBER;
1101 l_max_load_wt NUMBER;
1102 l_min_fill_pc NUMBER;
1103 l_max_volume NUMBER;
1104 l_max_load_qty NUMBER;
1105 l_dd_master_cont_id NUMBER;
1106 l_dd_det_cont_id NUMBER;
1107 l_dd_inv_item_id NUMBER;
1108 l_dd_inv_item_desc VARCHAR2(240);
1109 l_dd_packed_qty NUMBER;
1110 l_dd_hold_flag VARCHAR2(1);
1111 l_dd_gross_wt NUMBER;
1112 l_dd_net_wt NUMBER;
1113 l_dd_tare_wt NUMBER;
1114 l_dd_volume NUMBER;
1115 l_dd_vol_uom VARCHAR2(3);
1116 l_dd_wt_uom VARCHAR2(3);
1117 l_dd_req_qty_uom VARCHAR2(3);
1118 l_output_qty NUMBER;
1119 l_return_status VARCHAR2(30);
1120
1121 l_wcl_cont_item_id NUMBER;
1122 l_dd_org_id NUMBER;
1123 l_cont_org_id NUMBER;
1124 l_del_det_id NUMBER;
1125 l_cont_instance_id NUMBER;
1126 l_dd_load_seq_num NUMBER;
1127 l_num_cont NUMBER;
1128 l_mtl_wt_uom VARCHAR2(3);
1129 l_mtl_vol_uom VARCHAR2(3);
1130 l_mtl_max_load NUMBER;
1131 l_mtl_max_vol NUMBER;
1132 l_fill_pc_basis VARCHAR2(1);
1133
1134 l_item_name VARCHAR2(2000);
1135 l_org_name VARCHAR2(240);
1136
1137 l_tmp_num_cont NUMBER;
1138 l_discard_message VARCHAR2(2000);
1139 LIMITED_PRECISION NUMBER := 8;
1140
1141
1142 --
1143 l_debug_on BOOLEAN;
1144 --
1145 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ESTIMATE_DETAIL_CONTAINERS';
1146 --
1147 BEGIN
1148
1149 -- dbms_output.put_line('in estimating detail containers');
1150
1151 --
1152 -- Debug Statements
1153 --
1154 --
1155 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1156 --
1157 IF l_debug_on IS NULL
1158 THEN
1159 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1160 END IF;
1161 --
1162 IF l_debug_on THEN
1163 WSH_DEBUG_SV.push(l_module_name);
1164 --
1165 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_INSTANCE_ID',P_CONTAINER_INSTANCE_ID);
1166 WSH_DEBUG_SV.log(l_module_name,'X_CONTAINER_ITEM_ID',X_CONTAINER_ITEM_ID);
1167 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
1168 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
1172 OPEN Get_Delivery_Details (p_delivery_detail_id);
1169 WSH_DEBUG_SV.log(l_module_name,'X_NUM_CONT',X_NUM_CONT);
1170 END IF;
1171 --
1173
1174 FETCH Get_Delivery_Details INTO
1175 l_del_det_id,
1176 l_dd_inv_item_id,
1177 l_dd_inv_item_desc,
1178 l_dd_packed_qty,
1179 l_dd_req_qty_uom,
1180 l_dd_master_cont_id,
1181 l_dd_det_cont_id,
1182 l_dd_hold_flag,
1183 l_dd_load_seq_num,
1184 l_dd_net_wt,
1185 l_dd_wt_uom,
1186 l_dd_volume,
1187 l_dd_vol_uom,
1188 l_dd_org_id;
1189
1190
1191 IF (Get_Delivery_Details%NOTFOUND) THEN
1192 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1193 --dbms_output.put_line('no delivery details');
1194 FND_MESSAGE.SET_NAME('WSH','WSH_DET_INVALID_DETAIL');
1195 FND_MESSAGE.SET_TOKEN('DETAIL_ID',p_delivery_detail_id);
1196 CLOSE Get_Delivery_Details;
1197 WSH_UTIL_CORE.Add_Message(x_return_status);
1198 --
1199 -- Debug Statements
1200 --
1201 IF l_debug_on THEN
1202 WSH_DEBUG_SV.pop(l_module_name);
1203 END IF;
1204 --
1205 return;
1206 END IF;
1207
1208 CLOSE Get_Delivery_Details;
1209
1210 x_container_item_id := NVL(NVL(l_dd_det_cont_id, l_dd_master_cont_id), x_container_item_id);
1211
1212 --dbms_output.put_line('x cont item is ' || x_container_item_id || ' and inv item is ' || l_dd_inv_item_id || ' and org is ' || p_organization_id);
1213
1214 OPEN Get_Cont_Load (x_container_item_id, l_dd_inv_item_id);
1215
1216 FETCH Get_Cont_Load INTO
1217 l_max_load_qty,
1218 l_wcl_cont_item_id;
1219
1220 IF (Get_Cont_Load%NOTFOUND AND x_container_item_id IS NULL) THEN
1221 --
1222 -- Debug Statements
1223 --
1224 IF l_debug_on THEN
1225 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ITEM_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1226 END IF;
1227 --
1228 l_item_name := WSH_UTIL_CORE.Get_Item_Name(l_dd_inv_item_id,l_dd_org_id);
1229 --
1230 -- Debug Statements
1231 --
1232 IF l_debug_on THEN
1233 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ORG_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1234 END IF;
1235 --
1236 l_org_name := WSH_UTIL_CORE.Get_Org_Name(p_organization_id);
1237 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_CONT_LOAD');
1238 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
1239 FND_MESSAGE.SET_TOKEN('ORG_NAME',l_org_name);
1240 CLOSE Get_Cont_Load;
1241 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1242 WSH_UTIL_CORE.Add_Message(x_return_status);
1243 --
1244 -- Debug Statements
1245 --
1246 IF l_debug_on THEN
1247 WSH_DEBUG_SV.pop(l_module_name);
1248 END IF;
1249 --
1250 return;
1251 END IF;
1252
1253 CLOSE Get_Cont_Load;
1254
1255 x_container_item_id := NVL(x_container_item_id, l_wcl_cont_item_id);
1256
1257
1258 IF (p_container_instance_id IS NOT NULL) THEN
1259
1260 OPEN Get_Container_Info(p_container_instance_id, nvl(p_organization_id,l_dd_org_id));
1261
1262 FETCH Get_Container_Info INTO
1263 l_cont_instance_id,
1264 l_lpn,
1265 l_cont_item_id,
1266 l_cont_description,
1267 l_cont_gross,
1268 l_cont_net,
1269 l_cont_tare,
1270 l_cont_weight_uom,
1271 l_cont_volume,
1272 l_cont_vol_uom,
1273 l_fill_pc,
1274 l_max_load_wt,
1275 l_max_volume,
1276 l_min_fill_pc;
1277
1278
1279 IF Get_Container_Info%NOTFOUND THEN
1280 -- it means that no container load defined. may need to get it from
1281 -- container instances table.
1282 -- if container instance id also not defined then
1283 -- raise container_item_error;
1284 CLOSE Get_Container_Info;
1285 -- dbms_output.put_line('container not found');
1286 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1287 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_CONTAINER');
1288 FND_MESSAGE.SET_TOKEN('CONT_NAME',l_lpn);
1289 WSH_UTIL_CORE.Add_Message(x_return_status);
1290 --
1291 -- Debug Statements
1292 --
1293 IF l_debug_on THEN
1294 WSH_DEBUG_SV.pop(l_module_name);
1295 END IF;
1296 --
1297 return;
1298
1299 END IF;
1300
1301 CLOSE Get_Container_Info;
1302
1303 x_container_item_id := NVL(x_container_item_id,l_cont_item_id);
1304
1305 END IF;
1306
1307
1308 IF (x_container_item_id IS NULL) THEN
1309 -- it means that no container defined for this item type
1310 -- raise container_item_error;
1311 -- FND_MESSAGE.SET_NAME('WSH','WSH_CONT_EST_ERROR');
1312 -- dbms_output.put_line('no container item');
1313 -- WSH_UTIL_CORE.Add_Message(x_return_status);
1314 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1315 --
1316 -- Debug Statements
1317 --
1318 IF l_debug_on THEN
1319 WSH_DEBUG_SV.pop(l_module_name);
1320 END IF;
1321 --
1322 return;
1323 ELSE
1324
1325 OPEN Get_Cont_Msi(x_container_item_id, nvl(p_organization_id,l_dd_org_id));
1326
1327 FETCH Get_Cont_Msi INTO
1331 l_mtl_vol_uom;
1328 l_mtl_max_load,
1329 l_mtl_max_vol,
1330 l_mtl_wt_uom,
1332
1333 IF Get_Cont_Msi%NOTFOUND THEN
1334 --
1335 -- Debug Statements
1336 --
1337 IF l_debug_on THEN
1338 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ITEM_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1339 END IF;
1340 --
1341 l_item_name := WSH_UTIL_CORE.Get_Item_Name(x_container_item_id,nvl(p_organization_id,l_dd_org_id));
1342 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_INV_ITEM');
1343 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
1344 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1345 CLOSE Get_Cont_Msi;
1346 WSH_UTIL_CORE.Add_Message(x_return_status);
1347 --
1348 -- Debug Statements
1349 --
1350 IF l_debug_on THEN
1351 WSH_DEBUG_SV.pop(l_module_name);
1352 END IF;
1353 --
1354 return;
1355 END IF;
1356
1357 CLOSE Get_Cont_Msi;
1358
1359
1360 -- dbms_output.put_line('max load wt before convert is ' || l_mtl_max_load || 'wt uom ' || l_mtl_wt_uom || ' cont wt uom ' || l_cont_weight_uom);
1361
1362 --
1363 -- Debug Statements
1364 --
1365 IF l_debug_on THEN
1366 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
1367 END IF;
1368 --
1369 l_max_load_wt := NVL(l_max_load_wt, WSH_WV_UTILS.Convert_Uom (
1370 l_mtl_wt_uom,
1371 nvl(l_cont_weight_uom,l_mtl_wt_uom),
1372 l_mtl_max_load,
1373 x_container_item_id));
1374
1375 --
1376 -- Debug Statements
1377 --
1378 IF l_debug_on THEN
1379 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
1380 END IF;
1381 --
1382 l_max_volume := NVL(l_max_volume, WSH_WV_UTILS.Convert_Uom (
1383 l_mtl_vol_uom,
1384 nvl(l_cont_vol_uom,l_mtl_vol_uom),
1385 l_mtl_max_vol,
1386 x_container_item_id));
1387
1388 END IF;
1389
1390 OPEN Get_Fill_Basis (nvl(p_organization_id,l_dd_org_id));
1391
1392 FETCH Get_Fill_Basis INTO l_fill_pc_basis;
1393
1394 IF Get_Fill_Basis%NOTFOUND THEN
1395 --
1396 -- Debug Statements
1397 --
1398 IF l_debug_on THEN
1399 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ORG_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1400 END IF;
1401 --
1402 l_org_name := WSH_UTIL_CORE.Get_Org_Name(p_organization_id);
1403 FND_MESSAGE.SET_NAME('WSH','WSH_FILL_BASIS_ERROR');
1404 FND_MESSAGE.SET_TOKEN('ORG_NAME',l_org_name);
1405 -- dbms_output.put_line('fill percent not defined');
1406 CLOSE Get_Fill_Basis;
1407 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1408 WSH_UTIL_CORE.Add_Message(x_return_status);
1409 --
1410 -- Debug Statements
1411 --
1412 IF l_debug_on THEN
1413 WSH_DEBUG_SV.pop(l_module_name);
1414 END IF;
1415 --
1416 return;
1417 END IF;
1418
1419 CLOSE Get_Fill_Basis;
1420
1421
1422 OPEN Get_Cont_Load (x_container_item_id, l_dd_inv_item_id);
1423
1424 FETCH Get_Cont_Load INTO
1425 l_max_load_qty,
1426 l_wcl_cont_item_id;
1427
1428 IF Get_Cont_Load%FOUND AND l_fill_pc_basis = 'Q' THEN
1429 -- decided to return decimal number of containers and manage the actual
1430 -- number during the creation of the containers.
1431
1432 IF l_max_load_qty <> 0 THEN
1433
1434 l_num_cont := (l_dd_packed_qty / l_max_load_qty);
1435 CLOSE Get_Cont_Load;
1436
1437 ELSE
1438
1439 CLOSE Get_Cont_Load;
1440
1441 x_num_cont := 0;
1442 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_LOAD_QTY_ERROR');
1443 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1444 WSH_UTIL_CORE.Add_Message(x_return_status);
1445 --
1446 -- Debug Statements
1447 --
1448 IF l_debug_on THEN
1449 WSH_DEBUG_SV.pop(l_module_name);
1450 END IF;
1451 --
1452 return;
1453 -- dbms_output.put_line('num cont in estimate is ' || l_num_cont);
1454
1455 END IF;
1456
1457 ELSE
1458
1459 CLOSE Get_Cont_Load;
1460
1461 -- removed the CEIL from the expressions to check for exact numbers;
1462
1463 -- dbms_output.put_line('net-weight before convert is ' || l_dd_net_wt);
1464
1465 --
1466 -- Debug Statements
1467 --
1468 IF l_debug_on THEN
1469 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
1470 END IF;
1471 --
1472 l_dd_net_wt := WSH_WV_UTILS.Convert_Uom (
1473 l_dd_wt_uom,
1474 nvl(l_cont_weight_uom,l_dd_wt_uom),
1475 l_dd_net_wt,
1476 l_dd_inv_item_id);
1477
1478
1479 -- dbms_output.put_line('net_weight after convert is ' || l_dd_net_wt);
1480 -- dbms_output.put_line('max load weight is ' || l_max_load_wt);
1481
1482 --
1483 -- Debug Statements
1484 --
1485 IF l_debug_on THEN
1486 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
1487 END IF;
1491 nvl(l_cont_vol_uom,l_dd_vol_uom),
1488 --
1489 l_dd_volume := WSH_WV_UTILS.Convert_Uom (
1490 l_dd_vol_uom,
1492 l_dd_volume,
1493 l_dd_inv_item_id);
1494
1495
1496 IF l_fill_pc_basis = 'W' THEN
1497 -- dbms_output.put_line('using weights' || l_dd_net_wt || ' and ' || l_max_load_wt);
1498
1499 IF l_max_load_wt > 0 THEN
1500
1501 l_num_cont := (l_dd_net_wt/l_max_load_wt);
1502 ELSE
1503 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_MAX_LOAD_ERROR');
1504 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1505 WSH_UTIL_CORE.Add_Message(x_return_status);
1506 x_num_cont := 0;
1507 --
1508 -- Debug Statements
1509 --
1510 IF l_debug_on THEN
1511 WSH_DEBUG_SV.pop(l_module_name);
1512 END IF;
1513 --
1514 return;
1515 END IF;
1516
1517 ELSIF l_fill_pc_basis = 'V' THEN
1518
1519 IF l_max_volume > 0 THEN
1520
1521 l_num_cont := (l_dd_volume/l_max_volume);
1522
1523 ELSE
1524 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_MAX_VOL_ERROR');
1525 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1526 WSH_UTIL_CORE.Add_Message(x_return_status);
1527 x_num_cont := 0;
1528 --
1529 -- Debug Statements
1530 --
1531 IF l_debug_on THEN
1532 WSH_DEBUG_SV.pop(l_module_name);
1533 END IF;
1534 --
1535 return;
1536 END IF;
1537
1538 ELSIF l_fill_pc_basis = 'Q' THEN
1539
1540 IF l_max_load_wt > 0 AND l_max_volume > 0 THEN
1541
1542 IF ((l_dd_net_wt/l_max_load_wt) >= (l_dd_volume/l_max_volume)) THEN
1543 l_num_cont := (l_dd_net_wt/l_max_load_wt);
1544 ELSE
1545 l_num_cont := (l_dd_volume/l_max_volume);
1546 END IF;
1547 ELSE
1548 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_MAX_WT_ERROR');
1549 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1550 WSH_UTIL_CORE.Add_Message(x_return_status);
1551 x_num_cont := 0;
1552 --
1553 -- Debug Statements
1554 --
1555 IF l_debug_on THEN
1556 WSH_DEBUG_SV.pop(l_module_name);
1557 END IF;
1558 --
1559 return;
1560 END IF;
1561 ELSE
1562 --
1563 -- Debug Statements
1564 --
1565 IF l_debug_on THEN
1566 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ORG_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1567 END IF;
1568 --
1569 l_org_name := WSH_UTIL_CORE.Get_Org_Name(nvl(p_organization_id,l_dd_org_id));
1570 FND_MESSAGE.SET_NAME('WSH','WSH_FILL_BASIS_ERROR');
1571 FND_MESSAGE.SET_TOKEN('ORG_NAME',l_org_name);
1572 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1573 WSH_UTIL_CORE.Add_Message(x_return_status);
1574 --
1575 -- Debug Statements
1576 --
1577 IF l_debug_on THEN
1578 WSH_DEBUG_SV.pop(l_module_name);
1579 END IF;
1580 --
1581 return;
1582 END IF;
1583
1584
1585 -- bug 2443162: avoid division by zero if the item's
1586 -- weight or volume value or max load quantity is zero.
1587 -- We also should catch the case when value is NULL.
1588 -- (This is the original API with multiple IN parameters)
1589 IF NVL(l_num_cont, 0) = 0 THEN
1590 FND_MESSAGE.SET_NAME('WSH', 'WSH_NULL_WEIGHT_VOLUME');
1591 FND_MESSAGE.SET_TOKEN('DELIVERY_DETAIL', p_delivery_detail_id);
1592 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1593 WSH_UTIL_CORE.Add_Message(x_return_status);
1594 x_num_cont := 0;
1595 --
1596 -- Debug Statements
1597 --
1598 IF l_debug_on THEN
1599 WSH_DEBUG_SV.pop(l_module_name);
1600 END IF;
1601 --
1602 return;
1603 END IF;
1604
1605
1606
1607 -- THIS CHECK IS REQUIRED FOR WEIGHT AS WELL AS VOLUME PLUS THE CASE OF QTY with no container
1608 -- load set up bug 2381184
1609
1610 --
1611 -- Debug Statements
1612 --
1613 IF l_debug_on THEN
1614 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DETAILS_VALIDATIONS.CHECK_DECIMAL_QUANTITY',WSH_DEBUG_SV.C_PROC_LEVEL);
1615 END IF;
1616 --
1617 WSH_DETAILS_VALIDATIONS.Check_Decimal_Quantity (
1618 l_dd_inv_item_id,
1619 l_dd_org_id,
1620 ROUND((l_dd_packed_qty/l_num_cont),LIMITED_PRECISION),
1621 l_dd_req_qty_uom,
1622 l_output_qty,
1623 l_return_status);
1624
1625
1626 -- errors likely mean integers are safe values.
1627 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1628 l_discard_message := FND_MESSAGE.GET;
1629
1630 l_tmp_num_cont := FLOOR(l_dd_packed_qty/l_num_cont);
1631
1632 IF (l_tmp_num_cont = 0) THEN
1633 -- Bug#: 2503937 - New Error Message
1637 x_num_cont := 0;
1634 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_MAX_WT_VOL_ERROR');
1635 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1636 WSH_UTIL_CORE.Add_Message(x_return_status);
1638 --
1639 -- Debug Statements
1640 --
1641 IF l_debug_on THEN
1642 WSH_DEBUG_SV.pop(l_module_name);
1643 END IF;
1644 --
1645 return;
1646 END IF;
1647
1648 l_num_cont := (l_dd_packed_qty/l_tmp_num_cont);
1649 END IF;
1650
1651
1652 END IF;
1653
1654 -- bug 1748609: maximize precision available to get accurate packing
1655 x_num_cont := l_num_cont;
1656
1657 IF x_num_cont <= 0 THEN
1658 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1659 ELSE
1660 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1661 END IF;
1662
1663 --
1664 -- Debug Statements
1665 --
1666 IF l_debug_on THEN
1667 WSH_DEBUG_SV.pop(l_module_name);
1668 END IF;
1669 --
1670 EXCEPTION
1671
1672 WHEN Others THEN
1673 WSH_UTIL_CORE.Default_Handler('WSH_CONTAINER_UTILITIES.Estimate_Detail_Containers(1)');
1674 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1675
1676 --
1677 -- Debug Statements
1678 --
1679 IF l_debug_on THEN
1680 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1681 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1682 END IF;
1683 --
1684 END Estimate_Detail_Containers;
1685
1686
1687 /*
1688 -----------------------------------------------------------------------------
1689 PROCEDURE : Estimate Master Containers
1690 PARAMETERS : p_container_instance_id - instance id of the detail container
1691 x_mast_cont_item_id - master container item id
1692 p_det_cont_item_id - detail container item id
1693 p_organization_id - organization_id
1694 x_num_cont - number of master containers required to pack
1695 the detail containers.
1696 x_return_status - return status of API
1697 DESCRIPTION : This procedure estimates the number of master containers that
1698 would be required to pack a number of detail containers. The
1699 master container item could be specified or if it is not
1700 specified, it is derived from the container load relationship.
1701 Using the detail container item id and the derived master
1702 container item id the number of master containers is
1703 calculated/estimated.
1704 ------------------------------------------------------------------------------
1705 */
1706
1707
1708 PROCEDURE Estimate_Master_Containers(
1709 p_container_instance_id IN NUMBER,
1710 x_mast_cont_item_id IN OUT NOCOPY NUMBER,
1711 p_det_cont_item_id IN NUMBER,
1712 p_organization_id IN NUMBER,
1713 x_num_cont IN OUT NOCOPY NUMBER,
1714 x_return_status OUT NOCOPY VARCHAR2) IS
1715
1716 CURSOR Get_Cont_Load (v_cont_id NUMBER, v_inv_item_id NUMBER) IS
1717 SELECT max_load_quantity, container_item_id
1718 FROM WSH_CONTAINER_ITEMS
1719 WHERE container_item_id = NVL(v_cont_id, container_item_id)
1720 AND load_item_id = v_inv_item_id
1721 AND master_organization_id = p_organization_id
1722 AND preferred_flag = DECODE(nvl(v_cont_id,-99),-99,'Y',preferred_flag);
1723
1724 -- For container, requested quantity is acceptable
1725 -- what if container is ordered item????
1726 -- then would there be shipped quantity????
1727 -- based on that need to verify for unit weight and unit volume
1728 -- as in estimate_detail_containers
1729 CURSOR Get_Container_Info (v_cont_instance_id NUMBER, v_org_id NUMBER)IS
1730 SELECT delivery_detail_id container_instance_id, container_name lpn,
1731 inventory_item_id container_item_id, item_description,
1732 requested_quantity,requested_quantity_uom,
1733 gross_weight, net_weight, (gross_weight - net_weight), weight_uom_code,
1734 volume, volume_uom_code, fill_percent,
1735 minimum_fill_percent, organization_id
1736 FROM WSH_DELIVERY_DETAILS
1737 WHERE delivery_detail_id = v_cont_instance_id
1738 AND organization_id = nvl(v_org_id, organization_id)
1739 AND container_flag in ('Y', 'C');
1740
1741 CURSOR Get_Cont_Msi (v_cont_item_id NUMBER, v_org_id NUMBER) IS
1742 SELECT maximum_load_weight, internal_volume, weight_uom_code, volume_uom_code
1743 FROM MTL_SYSTEM_ITEMS
1744 WHERE inventory_item_id = v_cont_item_id
1745 AND organization_id = v_org_id;
1746
1747 CURSOR Get_Fill_Basis (v_org_id NUMBER) IS
1748 SELECT percent_fill_basis_flag
1749 FROM WSH_SHIPPING_PARAMETERS
1750 WHERE organization_id = v_org_id;
1751
1752 l_cont_gross NUMBER;
1753 l_cont_net NUMBER;
1754 l_cont_tare NUMBER;
1755 l_cont_weight_uom VARCHAR2(3);
1756 l_cont_volume NUMBER;
1757 l_cont_vol_uom VARCHAR2(3);
1758
1759 l_cont_req_qty NUMBER;
1760 l_cont_req_qty_uom VARCHAR2(3);
1761
1762 l_lpn VARCHAR2(30);
1763 l_par_cont_id NUMBER;
1764 l_cont_item_id NUMBER;
1765 l_cont_description VARCHAR2(240);
1766 l_fill_pc NUMBER;
1767 l_max_load_wt NUMBER;
1768 l_min_fill_pc NUMBER;
1769 l_max_volume NUMBER;
1770 l_max_load_qty NUMBER;
1774 l_num_cont NUMBER;
1771 l_wcl_cont_item_id NUMBER;
1772 l_cont_org_id NUMBER;
1773 l_cont_instance_id NUMBER;
1775 l_mtl_max_load NUMBER;
1776 l_mtl_max_vol NUMBER;
1777 l_mtl_wt_uom VARCHAR2(3);
1778 l_mtl_vol_uom VARCHAR2(3);
1779 l_fill_pc_flag VARCHAR2(1) := 'N';
1780 l_cont_fill_pc NUMBER;
1781
1782 l_item_name VARCHAR2(2000);
1783 l_org_name VARCHAR2(240);
1784
1785 l_cont_name VARCHAR2(30);
1786
1787 l_fill_pc_basis VARCHAR2(1);
1788
1789 l_tmp_num_cont NUMBER;
1790
1791 --
1792 l_debug_on BOOLEAN;
1793 --
1794 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ESTIMATE_MASTER_CONTAINERS';
1795 --
1796 BEGIN
1797
1798 -- dbms_output.put_line('in estimating master containers - container instance id is ' || p_container_instance_id);
1799
1800 --
1801 -- Debug Statements
1802 --
1803 --
1804 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1805 --
1806 IF l_debug_on IS NULL
1807 THEN
1808 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1809 END IF;
1810 --
1811 IF l_debug_on THEN
1812 WSH_DEBUG_SV.push(l_module_name);
1813 --
1814 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_INSTANCE_ID',P_CONTAINER_INSTANCE_ID);
1815 WSH_DEBUG_SV.log(l_module_name,'X_MAST_CONT_ITEM_ID',X_MAST_CONT_ITEM_ID);
1816 WSH_DEBUG_SV.log(l_module_name,'P_DET_CONT_ITEM_ID',P_DET_CONT_ITEM_ID);
1817 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
1818 WSH_DEBUG_SV.log(l_module_name,'X_NUM_CONT',X_NUM_CONT);
1819 END IF;
1820 --
1821 IF (p_container_instance_id IS NOT NULL) THEN
1822
1823 OPEN Get_Container_Info(p_container_instance_id, p_organization_id);
1824
1825 -- Auto Pack Rewrite: Removed l_max_load_wt and l_max_volume from Get_Container_Info cursor
1826 -- These 2 variables are causing incorrect container qty calculation
1827 FETCH Get_Container_Info INTO
1828 l_cont_instance_id,
1829 l_lpn,
1830 l_cont_item_id,
1831 l_cont_description,
1832 l_cont_req_qty,
1833 l_cont_req_qty_uom,
1834 l_cont_gross,
1835 l_cont_net,
1836 l_cont_tare,
1837 l_cont_weight_uom,
1838 l_cont_volume,
1839 l_cont_vol_uom,
1840 l_fill_pc,
1841 l_min_fill_pc,
1842 l_cont_org_id;
1843
1844
1845 IF Get_Container_Info%NOTFOUND THEN
1846 CLOSE Get_Container_Info;
1847 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1848 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_CONTAINER');
1849 FND_MESSAGE.SET_TOKEN('CONT_NAME',l_lpn);
1850 WSH_UTIL_CORE.Add_Message(x_return_status);
1851 --
1852 -- Debug Statements
1853 --
1854 IF l_debug_on THEN
1855 WSH_DEBUG_SV.pop(l_module_name);
1856 END IF;
1857 --
1858 return;
1859
1860 END IF;
1861
1862 CLOSE Get_Container_Info;
1863
1864 l_cont_item_id := NVL(p_det_cont_item_id,l_cont_item_id);
1865
1866 ELSE
1867
1868 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_CONTAINER');
1869 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1870 WSH_UTIL_CORE.Add_Message(x_return_status);
1871 --
1872 -- Debug Statements
1873 --
1874 IF l_debug_on THEN
1875 WSH_DEBUG_SV.pop(l_module_name);
1876 END IF;
1877 --
1878 return;
1879
1880 END IF;
1881
1882 OPEN Get_Cont_Load (x_mast_cont_item_id, l_cont_item_id);
1883
1884 FETCH Get_Cont_Load INTO
1885 l_max_load_qty,
1886 l_wcl_cont_item_id;
1887
1888 IF (Get_Cont_Load%NOTFOUND AND x_mast_cont_item_id IS NULL) 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_ITEM_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1894 END IF;
1895 --
1896 l_item_name := WSH_UTIL_CORE.Get_Item_Name(l_cont_item_id,p_organization_id);
1897 --
1898 -- Debug Statements
1899 --
1900 IF l_debug_on THEN
1901 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ORG_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1902 END IF;
1903 --
1904 l_org_name := WSH_UTIL_CORE.Get_Org_Name(p_organization_id);
1905 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_CONT_LOAD');
1906 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
1907 FND_MESSAGE.SET_TOKEN('ORG_NAME',l_org_name);
1908 -- dbms_output.put_line('error in get cont load');
1909 CLOSE Get_Cont_Load;
1910 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1911 WSH_UTIL_CORE.Add_Message(x_return_status);
1912 --
1913 -- Debug Statements
1914 --
1915 IF l_debug_on THEN
1916 WSH_DEBUG_SV.pop(l_module_name);
1917 END IF;
1918 --
1919 return;
1920 END IF;
1921
1922 CLOSE Get_Cont_Load;
1923
1924 x_mast_cont_item_id := NVL(x_mast_cont_item_id, l_wcl_cont_item_id);
1925
1926 -- dbms_output.put_line('master cont item id is ' || x_mast_cont_item_id);
1927
1928 IF (x_mast_cont_item_id IS NULL) THEN
1932 -- dbms_output.put_line('no container item');
1929 -- it means that no container defined for this item type
1930 -- raise container_item_error;
1931 -- FND_MESSAGE.SET_NAME('WSH','WSH_CONT_EST_ERROR');
1933 -- WSH_UTIL_CORE.Add_Message(x_return_status);
1934 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1935 --
1936 -- Debug Statements
1937 --
1938 IF l_debug_on THEN
1939 WSH_DEBUG_SV.pop(l_module_name);
1940 END IF;
1941 --
1942 return;
1943 ELSE
1944
1945 OPEN Get_Cont_Msi(x_mast_cont_item_id, nvl(p_organization_id,l_cont_org_id));
1946
1947 FETCH Get_Cont_Msi INTO
1948 l_mtl_max_load,
1949 l_mtl_max_vol,
1950 l_mtl_wt_uom,
1951 l_mtl_vol_uom;
1952
1953 IF Get_Cont_Msi%NOTFOUND THEN
1954 --
1955 -- Debug Statements
1956 --
1957 IF l_debug_on THEN
1958 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ITEM_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1959 END IF;
1960 --
1961 l_item_name := WSH_UTIL_CORE.Get_Item_Name(x_mast_cont_item_id,p_organization_id);
1962 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_INV_ITEM');
1963 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
1964 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1965 CLOSE Get_Cont_Msi;
1966 WSH_UTIL_CORE.Add_Message(x_return_status);
1967 --
1968 -- Debug Statements
1969 --
1970 IF l_debug_on THEN
1971 WSH_DEBUG_SV.pop(l_module_name);
1972 END IF;
1973 --
1974 return;
1975 END IF;
1976
1977 CLOSE Get_Cont_Msi;
1978
1979 -- Convert weight/volume of Master Cont into detail container's UOM
1980 --
1981 -- Debug Statements
1982 --
1983 IF l_debug_on THEN
1984 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
1985 END IF;
1986 --
1987 l_max_load_wt := NVL(l_max_load_wt, WSH_WV_UTILS.Convert_Uom (
1988 l_mtl_wt_uom,
1989 nvl(l_cont_weight_uom,l_mtl_wt_uom),
1990 l_mtl_max_load,
1991 x_mast_cont_item_id));
1992
1993 --
1994 -- Debug Statements
1995 --
1996 IF l_debug_on THEN
1997 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
1998 END IF;
1999 --
2000 l_max_volume := NVL(l_max_volume, WSH_WV_UTILS.Convert_Uom (
2001 l_mtl_vol_uom,
2002 nvl(l_cont_vol_uom,l_mtl_vol_uom),
2003 l_mtl_max_vol,
2004 x_mast_cont_item_id));
2005
2006 END IF;
2007
2008
2009 OPEN Get_Fill_Basis (nvl(p_organization_id,l_cont_org_id));
2010
2011 FETCH Get_Fill_Basis INTO l_fill_pc_basis;
2012
2013 IF Get_Fill_Basis%NOTFOUND THEN
2014 --
2015 -- Debug Statements
2016 --
2017 IF l_debug_on THEN
2018 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ORG_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
2019 END IF;
2020 --
2021 l_org_name := WSH_UTIL_CORE.Get_Org_Name(nvl(p_organization_id,l_cont_org_id));
2022 FND_MESSAGE.SET_NAME('WSH','WSH_FILL_BASIS_ERROR');
2023 FND_MESSAGE.SET_TOKEN('ORG_NAME',l_org_name);
2024 -- dbms_output.put_line('fill percent not defined');
2025 CLOSE Get_Fill_Basis;
2026 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2027 WSH_UTIL_CORE.Add_Message(x_return_status);
2028 --
2029 -- Debug Statements
2030 --
2031 IF l_debug_on THEN
2032 WSH_DEBUG_SV.pop(l_module_name);
2033 END IF;
2034 --
2035 return;
2036 END IF;
2037
2038 CLOSE Get_Fill_Basis;
2039
2040
2041 OPEN Get_Cont_Load (x_mast_cont_item_id, l_cont_item_id);
2042
2043 FETCH Get_Cont_Load INTO
2044 l_max_load_qty,
2045 l_wcl_cont_item_id;
2046
2047 IF Get_Cont_Load%FOUND AND l_fill_pc_basis = 'Q' THEN
2048 -- decided to return decimal number of containers and manage the actual
2049 -- number during the creation of the containers.
2050
2051
2052 IF l_max_load_qty <> 0 THEN
2053
2054 l_num_cont := (1 / l_max_load_qty);
2055 CLOSE Get_Cont_Load;
2056
2057 ELSE
2058
2059 CLOSE Get_Cont_Load;
2060
2061 x_num_cont := 0;
2062 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_LOAD_QTY_ERROR');
2063 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2064 WSH_UTIL_CORE.Add_Message(x_return_status);
2065 --
2066 -- Debug Statements
2067 --
2068 IF l_debug_on THEN
2069 WSH_DEBUG_SV.pop(l_module_name);
2070 END IF;
2071 --
2072 return;
2073 -- dbms_output.put_line('num cont in estimate is ' || l_num_cont);
2074
2075 END IF;
2076
2077 ELSE
2078
2079 CLOSE Get_Cont_Load;
2080
2081 -- removed the CEIL from the expressions to check for exact numbers;
2082
2083 IF l_fill_pc_basis = 'W' THEN
2084
2085 IF l_max_load_wt > 0 THEN
2086 l_num_cont := (l_cont_gross/l_max_load_wt);
2087 ELSE
2088 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_MAX_LOAD_ERROR');
2092 --
2089 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2090 WSH_UTIL_CORE.Add_Message(x_return_status);
2091 x_num_cont := 0;
2093 -- Debug Statements
2094 --
2095 IF l_debug_on THEN
2096 WSH_DEBUG_SV.pop(l_module_name);
2097 END IF;
2098 --
2099 return;
2100 END IF;
2101
2102 ELSIF l_fill_pc_basis = 'V' THEN
2103
2104 IF l_max_volume > 0 THEN
2105 l_num_cont := (l_cont_volume/l_max_volume);
2106 ELSE
2107 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_MAX_VOL_ERROR');
2108 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2109 WSH_UTIL_CORE.Add_Message(x_return_status);
2110 x_num_cont := 0;
2111 --
2112 -- Debug Statements
2113 --
2114 IF l_debug_on THEN
2115 WSH_DEBUG_SV.pop(l_module_name);
2116 END IF;
2117 --
2118 return;
2119 END IF;
2120
2121 ELSIF l_fill_pc_basis = 'Q' THEN
2122
2123 IF l_max_load_wt > 0 AND l_max_volume > 0 THEN
2124
2125 IF ((l_cont_gross/l_max_load_wt) >= (l_cont_volume/l_max_volume)) THEN
2126 l_num_cont := (l_cont_gross/l_max_load_wt);
2127 ELSE
2128 l_num_cont := (l_cont_volume/l_max_volume);
2129 END IF;
2130 ELSE
2131 -- Bug#: 2503937 - New Error Message
2132 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_MAX_WT_VOL_ERROR');
2133 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2134 WSH_UTIL_CORE.Add_Message(x_return_status);
2135 x_num_cont := 0;
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 ELSE
2147 --
2148 -- Debug Statements
2149 --
2150 IF l_debug_on THEN
2151 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ORG_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
2152 END IF;
2153 --
2154 l_org_name := WSH_UTIL_CORE.Get_Org_Name(nvl(p_organization_id, l_cont_org_id));
2155 FND_MESSAGE.SET_NAME('WSH','WSH_FILL_BASIS_ERROR');
2156 FND_MESSAGE.SET_TOKEN('ORG_NAME',l_org_name);
2157 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2158 WSH_UTIL_CORE.Add_Message(x_return_status);
2159 --
2160 -- Debug Statements
2161 --
2162 IF l_debug_on THEN
2163 WSH_DEBUG_SV.pop(l_module_name);
2164 END IF;
2165 --
2166 return;
2167 END IF;
2168
2169
2170 END IF;
2171
2172 -- bug 1748609: maximize precision available to get accurate packing
2173 x_num_cont := l_num_cont;
2174
2175 -- dbms_output.put_line('num of cont for container ' || p_container_instance_id || ' is ' || x_num_cont);
2176
2177 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2178
2179 --
2180 -- Debug Statements
2181 --
2182 IF l_debug_on THEN
2183 WSH_DEBUG_SV.pop(l_module_name);
2184 END IF;
2185 --
2186 EXCEPTION
2187
2188 WHEN Others THEN
2189 WSH_UTIL_CORE.Default_Handler('WSH_CONTAINER_UTILITIES.Estimate_Master_Containers');
2190 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2191
2192 --
2193 -- Debug Statements
2194 --
2195 IF l_debug_on THEN
2196 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2197 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2198 END IF;
2199 --
2200 END Estimate_Master_Containers;
2201
2202
2203 /*
2204 -----------------------------------------------------------------------------
2205 PROCEDURE : Get Master Cont Serial
2206 PARAMETERS : p_container_instance_id - instance id for the container
2207 x_master_container_id - the master container of the container
2208 derived using the container hierarchy.
2209 x_master_container_name - container name for the master
2210 container.
2211 x_master_serial_number - serial number of the master container
2212 derived using the container hierarchy.
2213 x_return_status - return status of API
2214 DESCRIPTION : This procedure derives the master container instance id and
2215 master serial number of the container. The master serial
2216 number and master container instance id is derived from the
2217 container instance table using the container heirarchy.
2218 ------------------------------------------------------------------------------
2219 */
2220
2221
2222 PROCEDURE Get_Master_Cont_Serial (
2223 p_container_instance_id IN NUMBER,
2224 x_master_container_id IN OUT NOCOPY NUMBER,
2225 x_master_container_name IN OUT NOCOPY VARCHAR2,
2226 x_master_serial_number IN OUT NOCOPY VARCHAR2,
2227 x_return_status OUT NOCOPY VARCHAR2) IS
2228
2229 CURSOR Get_Master_Cont (v_cont_inst_id NUMBER) IS
2230 SELECT delivery_detail_id
2231 FROM wsh_delivery_assignments
2232 WHERE parent_delivery_detail_id IS NULL
2233 AND NVL(type, 'S') in ('S', 'C')
2234 START WITH delivery_detail_id = v_cont_inst_id
2235 AND NVL(type, 'S') in ('S', 'C')
2239 SELECT master_serial_number, delivery_detail_id, container_flag
2236 CONNECT BY PRIOR parent_delivery_detail_id = delivery_detail_id;
2237
2238 CURSOR Get_Serial_Number (v_detail_id NUMBER) IS
2240 FROM WSH_DELIVERY_DETAILS
2241 WHERE delivery_detail_id = v_detail_id;
2242
2243 l_det_id NUMBER;
2244 l_cont_flag VARCHAR2(1);
2245 l_cont_name VARCHAR2(30);
2246
2247
2248 --
2249 l_debug_on BOOLEAN;
2250 --
2251 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_MASTER_CONT_SERIAL';
2252 --
2253 BEGIN
2254
2255 --
2256 -- Debug Statements
2257 --
2258 --
2259 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2260 --
2261 IF l_debug_on IS NULL
2262 THEN
2263 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2264 END IF;
2265 --
2266 IF l_debug_on THEN
2267 WSH_DEBUG_SV.push(l_module_name);
2268 --
2269 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_INSTANCE_ID',P_CONTAINER_INSTANCE_ID);
2270 WSH_DEBUG_SV.log(l_module_name,'X_MASTER_CONTAINER_ID',X_MASTER_CONTAINER_ID);
2271 WSH_DEBUG_SV.log(l_module_name,'X_MASTER_CONTAINER_NAME',X_MASTER_CONTAINER_NAME);
2272 WSH_DEBUG_SV.log(l_module_name,'X_MASTER_SERIAL_NUMBER',X_MASTER_SERIAL_NUMBER);
2273 END IF;
2274 --
2275 OPEN Get_Master_Cont (p_container_instance_id);
2276 FETCH Get_Master_Cont INTO x_master_container_id;
2277
2278 IF (Get_Master_Cont%NOTFOUND) THEN
2279 CLOSE Get_Master_Cont;
2280 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2281 --
2282 -- Debug Statements
2283 --
2284 IF l_debug_on THEN
2285 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.GET_CONT_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
2286 END IF;
2287 --
2288 l_cont_name := WSH_CONTAINER_UTILITIES.Get_Cont_Name(p_container_instance_id);
2289 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_CONTAINER');
2290 FND_MESSAGE.SET_TOKEN('CONT_NAME',l_cont_name);
2291 WSH_UTIL_CORE.Add_Message(x_return_status);
2292 --
2293 -- Debug Statements
2294 --
2295 IF l_debug_on THEN
2296 WSH_DEBUG_SV.pop(l_module_name);
2297 END IF;
2298 --
2299 return;
2300 END IF;
2301
2302 IF (Get_Master_Cont%ISOPEN) THEN
2303 CLOSE Get_Master_Cont;
2304 END IF;
2305
2306 --
2307 -- Debug Statements
2308 --
2309 IF l_debug_on THEN
2310 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.GET_CONT_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
2311 END IF;
2312 --
2313 x_master_container_name := WSH_CONTAINER_UTILITIES.Get_Cont_Name(x_master_container_id);
2314
2315 OPEN Get_Serial_Number (x_master_container_id);
2316 FETCH Get_Serial_Number INTO x_master_serial_number, l_det_id, l_cont_flag;
2317
2318 IF (Get_Serial_Number%NOTFOUND OR l_cont_flag = 'N') THEN
2319 CLOSE Get_Serial_Number;
2320 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2321 --
2322 -- Debug Statements
2323 --
2324 IF l_debug_on THEN
2325 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.GET_CONT_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
2326 END IF;
2327 --
2328 l_cont_name := WSH_CONTAINER_UTILITIES.Get_Cont_Name(p_container_instance_id);
2329 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_CONTAINER');
2330 FND_MESSAGE.SET_TOKEN('CONT_NAME',l_cont_name);
2331 WSH_UTIL_CORE.Add_Message(x_return_status);
2332 --
2333 -- Debug Statements
2334 --
2335 IF l_debug_on THEN
2336 WSH_DEBUG_SV.pop(l_module_name);
2337 END IF;
2338 --
2339 return;
2340 END IF;
2341
2342 IF (Get_Serial_Number%ISOPEN) THEN
2343 CLOSE Get_Serial_Number;
2344 END IF;
2345
2346 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2347
2348 --
2349 -- Debug Statements
2350 --
2351 IF l_debug_on THEN
2352 WSH_DEBUG_SV.pop(l_module_name);
2353 END IF;
2354 --
2355 EXCEPTION
2356
2357 WHEN Others THEN
2358 WSH_UTIL_CORE.Default_Handler('WSH_CONTAINER_UTILITIES.Get_Master_Container');
2359 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2360
2361 --
2362 -- Debug Statements
2363 --
2364 IF l_debug_on THEN
2365 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2366 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2367 END IF;
2368 --
2369 END Get_Master_Cont_Serial;
2370
2371
2372 /*
2373 -----------------------------------------------------------------------------
2374 PROCEDURE : Update Child Containers
2375 PARAMETERS : p_container_instance_id - instance id for the container
2376 x_master_cont_instance_id - master container of the container
2377 x_master_serial_number - serial number of the master container
2378 x_return_status - return status of API
2379 DESCRIPTION : This procedure updates the master container instance id and
2380 master serial number of all the child containers. When the
2381 master serial number and master container instance id is
2382 changed on the master container, all the child containers are
2386
2383 updated with the new values using this API.
2384 ------------------------------------------------------------------------------
2385 */
2387
2388 PROCEDURE Update_Child_Containers (
2389 p_container_instance_id IN NUMBER,
2390 p_master_cont_instance_id IN NUMBER,
2391 p_master_serial_number IN VARCHAR2,
2392 x_return_status OUT NOCOPY VARCHAR2) IS
2393
2394 CURSOR Get_Child_Containers(v_cont_instance_id NUMBER) IS
2395 SELECT delivery_detail_id
2396 FROM wsh_delivery_assignments_v
2397 START WITH parent_delivery_detail_id = v_cont_instance_id
2398 CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
2399
2400 CURSOR l_get_det_attribs_csr(p_detail_id IN NUMBER) IS
2401 SELECT container_flag, nvl(line_direction, 'O'), organization_id
2402 from wsh_delivery_details
2403 where delivery_detail_id = p_detail_id;
2404
2405 l_cont_instance_id NUMBER;
2406
2407 l_cont_name VARCHAR2(30);
2408
2409 -- K LPN CONV. rv
2410 l_wms_org VARCHAR2(10) := 'N';
2411 l_sync_tmp_rec wsh_glbl_var_strct_grp.sync_tmp_rec_type;
2412 l_sync_tmp_recTbl wsh_glbl_var_strct_grp.sync_tmp_recTbl_type;
2413 l_line_direction VARCHAR2(10);
2414 l_organization_id NUMBER;
2415 l_cnt_flag VARCHAR2(10);
2416 l_child_counter NUMBER;
2417 -- K LPN CONV. rv
2418
2419
2420 --
2421 l_debug_on BOOLEAN;
2422 --
2423 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CHILD_CONTAINERS';
2424 --
2425 BEGIN
2426
2427 --
2428 -- Debug Statements
2429 --
2430 --
2431 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2432 --
2433 IF l_debug_on IS NULL
2434 THEN
2435 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2436 END IF;
2437 --
2438 IF l_debug_on THEN
2439 WSH_DEBUG_SV.push(l_module_name);
2440 --
2441 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_INSTANCE_ID',P_CONTAINER_INSTANCE_ID);
2442 WSH_DEBUG_SV.log(l_module_name,'P_MASTER_CONT_INSTANCE_ID',P_MASTER_CONT_INSTANCE_ID);
2443 WSH_DEBUG_SV.log(l_module_name,'P_MASTER_SERIAL_NUMBER',P_MASTER_SERIAL_NUMBER);
2444 END IF;
2445 --
2446
2447 -- bug 5603825: need to stamp master_serial_number of
2448 -- p_container_instance_id when packing it into
2449 -- a LPN or unpacking it because this record is
2450 -- not included in the results of get_child_containers.
2451 IF (p_container_instance_id <> p_master_cont_instance_id)
2452 OR (p_master_cont_instance_id IS NULL) THEN
2453 --{
2454 UPDATE WSH_DELIVERY_DETAILS
2455 SET master_serial_number = p_master_serial_number
2456 WHERE delivery_detail_id = p_container_instance_id
2457 RETURNING container_flag, NVL(line_direction, 'O'), organization_id
2458 INTO l_cnt_flag, l_line_direction, l_organization_id;
2459
2460 IF l_debug_on THEN
2461 WSH_DEBUG_SV.log(l_module_name, 'Master serial number is updated on this container.', p_container_instance_id);
2462 END IF;
2463
2464 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y' THEN
2465 --{
2466 l_wms_org := wsh_util_validate.check_wms_org(l_organization_id);
2467
2468 IF l_line_direction IN ('O', 'IO')
2469 AND l_cnt_flag = 'Y'
2470 AND
2471 (
2472 (WSH_WMS_LPN_GRP.GK_WMS_UPD_MISC and l_wms_org = 'Y')
2473 OR (WSH_WMS_LPN_GRP.GK_INV_UPD_MISC and l_wms_org = 'N')
2474 )
2475 THEN
2476 --{
2477 l_sync_tmp_rec.delivery_detail_id := p_container_instance_id;
2478 l_sync_tmp_rec.operation_type := 'UPDATE';
2479
2480 IF l_debug_on THEN
2481 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WMS_SYNC_TMP_PKG.MERGE',WSH_DEBUG_SV.C_PROC_LEVEL);
2482 END IF;
2483
2484 WSH_WMS_SYNC_TMP_PKG.MERGE
2485 (
2486 p_sync_tmp_rec => l_sync_tmp_rec,
2487 x_return_status => x_return_status
2488 );
2489
2490
2491 IF l_debug_on THEN
2492 wsh_debug_sv.log(l_module_name,'Return Status',x_return_status);
2493 END IF;
2494
2495 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
2496 WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
2497 IF l_debug_on THEN
2498 WSH_DEBUG_SV.logmsg(l_module_name,'Error occured in WSH_WMS_SYNC_TMP_PKG.MERGE');
2499 WSH_DEBUG_SV.pop(l_module_name);
2500 END IF;
2501 return;
2502 END IF;
2503 --}
2504 END IF;
2505 --}
2506 END IF;
2507 --}
2508 END IF;
2509
2510 --
2511 l_child_counter := 1;
2512 FOR det IN Get_Child_Containers(p_container_instance_id) LOOP
2513 IF (Get_Child_Containers%NOTFOUND) THEN
2514 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2515 --
2516 -- Debug Statements
2517 --
2518 IF l_debug_on THEN
2519 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.GET_CONT_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
2520 END IF;
2521 --
2525 WSH_UTIL_CORE.Add_Message(x_return_status);
2522 l_cont_name := WSH_CONTAINER_UTILITIES.Get_Cont_Name(p_container_instance_id);
2523 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_NO_CHILD');
2524 FND_MESSAGE.SET_TOKEN('CONT_NAME',l_cont_name);
2526 --
2527 -- Debug Statements
2528 --
2529 IF l_debug_on THEN
2530 WSH_DEBUG_SV.pop(l_module_name);
2531 END IF;
2532 --
2533 return;
2534 END IF;
2535 --
2536 -- K LPN CONV. rv
2537
2538 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
2539 THEN
2540 --{
2541 open l_get_det_attribs_csr(det.delivery_detail_id);
2542 fetch l_get_det_attribs_csr into l_cnt_flag, l_line_direction, l_organization_id;
2543 close l_get_det_attribs_csr;
2544
2545 l_wms_org := wsh_util_validate.check_wms_org(l_organization_id);
2546
2547 IF l_line_direction IN ('O', 'IO')
2548 AND l_cnt_flag = 'Y'
2549 AND
2550 (
2551 (WSH_WMS_LPN_GRP.GK_WMS_UPD_MISC and l_wms_org = 'Y')
2552 OR
2553 (WSH_WMS_LPN_GRP.GK_INV_UPD_MISC and l_wms_org = 'N')
2554 )
2555 THEN
2556 --{
2557 l_sync_tmp_rec.delivery_detail_id := det.delivery_detail_id;
2558 l_sync_tmp_rec.operation_type := 'UPDATE';
2559 --
2560 -- Debug Statements
2561 --
2562 IF l_debug_on THEN
2563 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WMS_SYNC_TMP_PKG.MERGE',WSH_DEBUG_SV.C_PROC_LEVEL);
2564 END IF;
2565
2566 WSH_WMS_SYNC_TMP_PKG.MERGE
2567 (
2568 p_sync_tmp_rec => l_sync_tmp_rec,
2569 x_return_status => x_return_status
2570 );
2571
2572 --
2573 IF l_debug_on THEN
2574 wsh_debug_sv.log(l_module_name,'Return Status',x_return_status);
2575 END IF;
2576 --
2577 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
2578 IF l_debug_on THEN
2579 WSH_DEBUG_SV.logmsg(l_module_name,'Error occured in WSH_WMS_SYNC_TMP_PKG.MERGE');
2580 WSH_DEBUG_SV.pop(l_module_name);
2581 END IF;
2582 return;
2583 END IF;
2584 --}
2585 END IF;
2586 --}
2587 END IF;
2588 -- K LPN CONV. rv
2589 --
2590 UPDATE WSH_DELIVERY_DETAILS
2591 SET master_serial_number = p_master_serial_number
2592 WHERE delivery_detail_id = det.delivery_detail_id;
2593
2594 IF (SQL%ROWCOUNT > 1 OR SQL%NOTFOUND) THEN
2595 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2596 --
2597 -- Debug Statements
2598 --
2599 IF l_debug_on THEN
2600 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.GET_CONT_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
2601 END IF;
2602 --
2603 l_cont_name := WSH_CONTAINER_UTILITIES.Get_Cont_Name(det.delivery_detail_id);
2604 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_CONTAINER');
2605 FND_MESSAGE.SET_TOKEN('CONT_NAME',l_cont_name);
2606 WSH_UTIL_CORE.Add_Message(x_return_status);
2607 END IF;
2608 END LOOP;
2609
2610 IF (Get_Child_Containers%ISOPEN) THEN
2611 CLOSE Get_Child_Containers;
2612 END IF;
2613
2614 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2615
2616 --
2617 -- Debug Statements
2618 --
2619 IF l_debug_on THEN
2620 WSH_DEBUG_SV.pop(l_module_name);
2621 END IF;
2622 --
2623 EXCEPTION
2624
2625 WHEN Others THEN
2626 WSH_UTIL_CORE.Default_Handler('WSH_CONTAINER_UTILITIES.Update_Child_Containers');
2627 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2628
2629 --
2630 -- Debug Statements
2631 --
2632 IF l_debug_on THEN
2633 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2634 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2635 END IF;
2636 --
2637 END Update_Child_Containers;
2638
2639
2640 /*
2641 -----------------------------------------------------------------------------
2642 PROCEDURE : Validate Master Serial Number
2643 PARAMETERS : p_container_instance_id - instance id for the container
2644 p_master_serial_number - serial number of the master container
2645 x_return_status - return status of API
2646 DESCRIPTION : This is a dummy procedure created to help customers create
2647 a customizable validation API for the master serial number. It
2648 currently returns success for all cases.
2649 ------------------------------------------------------------------------------
2650 */
2651
2652
2653 PROCEDURE Validate_Master_Serial_Number (
2654 p_master_serial_number IN VARCHAR2,
2655 p_container_instance_id IN NUMBER,
2656 x_return_status OUT NOCOPY VARCHAR2) IS
2657
2658 --
2659 l_debug_on BOOLEAN;
2663 BEGIN
2660 --
2661 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_MASTER_SERIAL_NUMBER';
2662 --
2664
2665 --
2666 -- Debug Statements
2667 --
2668 --
2669 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2670 --
2671 IF l_debug_on IS NULL
2672 THEN
2673 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2674 END IF;
2675 --
2676 IF l_debug_on THEN
2677 WSH_DEBUG_SV.push(l_module_name);
2678 --
2679 WSH_DEBUG_SV.log(l_module_name,'P_MASTER_SERIAL_NUMBER',P_MASTER_SERIAL_NUMBER);
2680 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_INSTANCE_ID',P_CONTAINER_INSTANCE_ID);
2681 END IF;
2682 --
2683 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2684
2685 --
2686 -- Debug Statements
2687 --
2688 IF l_debug_on THEN
2689 WSH_DEBUG_SV.pop(l_module_name);
2690 END IF;
2691 --
2692 EXCEPTION
2693
2694 WHEN Others THEN
2695 WSH_UTIL_CORE.Default_Handler('WSH_CONTAINER_UTILITIES.Validate_Master_serial_Number');
2696 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2697
2698 --
2699 -- Debug Statements
2700 --
2701 IF l_debug_on THEN
2702 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2703 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2704 END IF;
2705 --
2706 END Validate_Master_Serial_Number;
2707
2708
2709
2710 /*
2711 -----------------------------------------------------------------------------
2712 PROCEDURE : Get Master Serial Number
2713 PARAMETERS : p_container_instance_id - instance id for the container
2714 x_master_serial_number - serial number of the master container
2715 x_return_status - return status of API
2716 DESCRIPTION : This procedure retrieves the master serial number for a
2717 container by getting the serial number of the master container
2718 in the container heirarchy.
2719 ------------------------------------------------------------------------------
2720 */
2721
2722 PROCEDURE Get_Master_Serial_Number (
2723 p_container_instance_id IN NUMBER,
2724 x_master_serial_number IN OUT NOCOPY VARCHAR2,
2725 x_return_status OUT NOCOPY VARCHAR2) IS
2726
2727
2728 CURSOR Get_Master_Serial (v_cont_instance_id NUMBER) IS
2729 SELECT master_serial_number
2730 FROM WSH_DELIVERY_DETAILS
2731 WHERE delivery_detail_id = v_cont_instance_id;
2732
2733 l_cont_name VARCHAR2(30);
2734
2735 --
2736 l_debug_on BOOLEAN;
2737 --
2738 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_MASTER_SERIAL_NUMBER';
2739 --
2740 BEGIN
2741
2742 --
2743 -- Debug Statements
2744 --
2745 --
2746 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2747 --
2748 IF l_debug_on IS NULL
2749 THEN
2750 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2751 END IF;
2752 --
2753 IF l_debug_on THEN
2754 WSH_DEBUG_SV.push(l_module_name);
2755 --
2756 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_INSTANCE_ID',P_CONTAINER_INSTANCE_ID);
2757 WSH_DEBUG_SV.log(l_module_name,'X_MASTER_SERIAL_NUMBER',X_MASTER_SERIAL_NUMBER);
2758 END IF;
2759 --
2760 OPEN Get_Master_Serial (p_container_instance_id);
2761
2762 FETCH Get_Master_Serial INTO x_master_serial_number;
2763
2764 IF Get_Master_Serial%NOTFOUND THEN
2765 CLOSE Get_Master_Serial;
2766 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2767 --
2768 -- Debug Statements
2769 --
2770 IF l_debug_on THEN
2771 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.GET_CONT_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
2772 END IF;
2773 --
2774 l_cont_name := WSH_CONTAINER_UTILITIES.Get_Cont_Name(p_container_instance_id);
2775 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_CONTAINER');
2776 FND_MESSAGE.SET_TOKEN('CONT_NAME',l_cont_name);
2777 WSH_UTIL_CORE.Add_Message(x_return_status);
2778 --
2779 -- Debug Statements
2780 --
2781 IF l_debug_on THEN
2782 WSH_DEBUG_SV.pop(l_module_name);
2783 END IF;
2784 --
2785 return;
2786 END IF;
2787
2788 IF Get_Master_Serial%ISOPEN THEN
2789 CLOSE Get_Master_Serial;
2790 END IF;
2791
2792 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2793
2794 --
2795 -- Debug Statements
2796 --
2797 IF l_debug_on THEN
2798 WSH_DEBUG_SV.pop(l_module_name);
2799 END IF;
2800 --
2801 EXCEPTION
2802
2803 WHEN Others THEN
2804 WSH_UTIL_CORE.Default_Handler('WSH_CONTAINER_UTILITIES.Get_Master_Serial_Number');
2805 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2806
2807 --
2808 -- Debug Statements
2809 --
2810 IF l_debug_on THEN
2811 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2812 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2813 END IF;
2814 --
2815 END Get_Master_Serial_Number;
2816
2817
2818 /*
2822 x_empty_flag - flag to return empty or non-empty
2819 -----------------------------------------------------------------------------
2820 PROCEDURE : Is Empty
2821 PARAMETERS : p_container_instance_id - instance id for the container
2823 x_return_status - return status of API
2824 DESCRIPTION : This procedure checks the container to see if there are any
2825 lines packed in the container. If there are no lines it returns
2826 a true flag to indicate that it is empty.
2827 ------------------------------------------------------------------------------
2828 */
2829
2830
2831 PROCEDURE Is_Empty (
2832 p_container_instance_id IN NUMBER,
2833 x_empty_flag IN OUT NOCOPY BOOLEAN,
2834 x_return_status OUT NOCOPY VARCHAR2) IS
2835
2836 CURSOR Fetch_Details (v_cont_instance_id NUMBER) IS
2837 SELECT wda.delivery_detail_id, wda.delivery_id
2838 FROM wsh_delivery_assignments_v wda,
2839 WSH_DELIVERY_DETAILS wdd
2840 WHERE wda.parent_delivery_detail_id = v_cont_instance_id
2841 AND wdd.delivery_detail_id = wda.delivery_detail_id
2842 AND wdd.container_flag = 'N'
2843 AND rownum < 2
2844 AND wda.parent_delivery_detail_id IS NOT NULL;
2845
2846 l_delivery_detail_id NUMBER;
2847 l_delivery_id NUMBER;
2848
2849 --
2850 l_debug_on BOOLEAN;
2851 --
2852 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'IS_EMPTY';
2853 --
2854 BEGIN
2855
2856
2857 --
2858 -- Debug Statements
2859 --
2860 --
2861 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2862 --
2863 IF l_debug_on IS NULL
2864 THEN
2865 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2866 END IF;
2867 --
2868 IF l_debug_on THEN
2869 WSH_DEBUG_SV.push(l_module_name);
2870 --
2871 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_INSTANCE_ID',P_CONTAINER_INSTANCE_ID);
2872 WSH_DEBUG_SV.log(l_module_name,'X_EMPTY_FLAG',X_EMPTY_FLAG);
2873 END IF;
2874 --
2875 OPEN Fetch_Details (p_container_instance_id);
2876
2877 FETCH Fetch_Details INTO l_delivery_detail_id, l_delivery_id;
2878
2879 IF Fetch_Details%FOUND THEN
2880 IF (l_delivery_detail_id IS NOT NULL) THEN
2881 x_empty_flag := FALSE;
2882 ELSE
2883 x_empty_flag := TRUE;
2884 END IF;
2885 ELSE
2886 x_empty_flag := TRUE;
2887 END IF;
2888
2889 IF Fetch_Details%ISOPEN THEN
2890 CLOSE Fetch_Details;
2891 END IF;
2892
2893 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2894
2895 --
2896 -- Debug Statements
2897 --
2898 IF l_debug_on THEN
2899 WSH_DEBUG_SV.pop(l_module_name);
2900 END IF;
2901 --
2902 EXCEPTION
2903
2904 WHEN Others THEN
2905 WSH_UTIL_CORE.Default_Handler('WSH_CONTAINER_UTILITIES.Is_Empty');
2906 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2907
2908 --
2909 -- Debug Statements
2910 --
2911 IF l_debug_on THEN
2912 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2913 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2914 END IF;
2915 --
2916 END Is_Empty;
2917
2918 /*
2919 -----------------------------------------------------------------------------
2920 PROCEDURE : Is Empty
2921 PARAMETERS : p_container_instance_id - instance id for the container
2922 x_empty_flag - flag to return empty or non-empty
2923 x_return_status - return status of API
2924 DESCRIPTION : This procedure checks the container to see if there are any
2925 lines packed in the container. If there are no lines it returns
2926 a 'Y' flag to indicate that it is empty.
2927 If C1 contains C2 and C3. C2 has C4 which is empty , but C3 has a ddid
2928 Based on this API, C1 is not empty, but C2 ind C4 are empty.
2929 Caller must ensure the ID being passed is a container to resolve bug 5100229 by removing the redundant query.
2930 ------------------------------------------------------------------------------
2931 */
2932
2933 PROCEDURE Is_Empty (
2934 p_container_instance_id IN NUMBER,
2935 x_empty_flag OUT NOCOPY VARCHAR2,
2936 x_return_status OUT NOCOPY VARCHAR2) IS
2937
2938
2939 -- bug 4891897, sql 15036897
2940 -- removed the outer query (select 1 from dual where exists ())
2941 -- since it causes 2 full table scan on wsh_delivery_assignments
2942
2943
2944 CURSOR c_details_exist (v_delivery_detail_id NUMBER) IS
2945 SELECT 1
2946 FROM wsh_delivery_details
2947 WHERE delivery_detail_id in (
2948 SELECT delivery_detail_id
2949 FROM wsh_delivery_assignments_v
2950 START WITH delivery_detail_id = v_delivery_detail_id
2951 CONNECT BY prior delivery_detail_id = parent_delivery_detail_id)
2952 AND container_flag = 'N';
2953 l_delivery_detail_id NUMBER;
2954 above_sql_status boolean := FALSE;
2955 --
2956 l_debug_on BOOLEAN;
2957 --
2958 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'IS_EMPTY2';
2959 --
2960 BEGIN
2961
2962 --
2963 -- Debug Statements
2964 --
2965 --
2969 THEN
2966 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2967 --
2968 IF l_debug_on IS NULL
2970 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2971 END IF;
2972 --
2973 IF l_debug_on THEN
2974 WSH_DEBUG_SV.push(l_module_name);
2975 --
2976 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_INSTANCE_ID',P_CONTAINER_INSTANCE_ID);
2977 WSH_DEBUG_SV.log(l_module_name,'X_EMPTY_FLAG',X_EMPTY_FLAG);
2978 END IF;
2979 --
2980 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2981
2982
2983 x_empty_flag := 'Y';
2984
2985 OPEN c_details_exist (p_container_instance_id);
2986 FETCH c_details_exist INTO l_delivery_detail_id;
2987 above_sql_status := c_details_exist%FOUND;
2988 CLOSE c_details_exist;
2989
2990 IF above_sql_status THEN -- Content exists
2991 x_empty_flag := 'N';
2992 END IF;
2993
2994 --
2995 -- Debug Statements
2996 --
2997 IF l_debug_on THEN
2998 WSH_DEBUG_SV.pop(l_module_name);
2999 END IF;
3000 --
3001
3002 EXCEPTION
3003
3004 WHEN Others THEN
3005
3006 IF c_details_exist%ISOPEN THEN
3007 close c_details_exist;
3008 END IF;
3009
3010 WSH_UTIL_CORE.Default_Handler('WSH_CONTAINER_UTILITIES.Is_Empty2');
3011 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3012
3013 --
3014 -- Debug Statements
3015 --
3016 IF l_debug_on THEN
3017 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3018 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3019 END IF;
3020 --
3021
3022 END Is_Empty;
3023
3024 /*
3025 -----------------------------------------------------------------------------
3026 PROCEDURE : Get Fill Percent
3027 PARAMETERS : p_container_instance_id - instance id for the container
3028 x_percent_fill - percent fill of the container
3029 x_return_status - return status of API
3030 DESCRIPTION : This procedure retrieves the percent fill of the container
3031 from the container instances table. If the percent fill is
3032 null, it recalculates the percent fill for the container.
3033 ------------------------------------------------------------------------------
3034 */
3035
3036
3037 PROCEDURE Get_Fill_Percent (
3038 p_container_instance_id IN NUMBER,
3039 x_percent_fill OUT NOCOPY NUMBER,
3040 x_return_status OUT NOCOPY VARCHAR2) IS
3041
3042 CURSOR Get_Fill_Percent (v_cont_instance_id NUMBER) IS
3043 SELECT fill_percent
3044 FROM WSH_DELIVERY_DETAILS
3045 WHERE delivery_detail_id = v_cont_instance_id
3046 AND container_flag in ('Y', 'C');
3047
3048 l_fill_percent NUMBER;
3049
3050 l_cont_name VARCHAR2(30);
3051
3052 --
3053 l_debug_on BOOLEAN;
3054 --
3055 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_FILL_PERCENT';
3056 --
3057 BEGIN
3058
3059 --
3060 -- Debug Statements
3061 --
3062 --
3063 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3064 --
3065 IF l_debug_on IS NULL
3066 THEN
3067 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3068 END IF;
3069 --
3070 IF l_debug_on THEN
3071 WSH_DEBUG_SV.push(l_module_name);
3072 --
3073 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_INSTANCE_ID',P_CONTAINER_INSTANCE_ID);
3074 END IF;
3075 --
3076 OPEN Get_Fill_Percent (p_container_instance_id);
3077
3078 FETCH Get_Fill_Percent INTO l_fill_percent;
3079
3080 IF Get_Fill_Percent%NOTFOUND THEN
3081 CLOSE Get_Fill_Percent;
3082 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3083 --
3084 -- Debug Statements
3085 --
3086 IF l_debug_on THEN
3087 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.GET_CONT_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3088 END IF;
3089 --
3090 l_cont_name := WSH_CONTAINER_UTILITIES.Get_Cont_Name(p_container_instance_id);
3091 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_CONTAINER');
3092 FND_MESSAGE.SET_TOKEN('CONT_NAME',l_cont_name);
3093 WSH_UTIL_CORE.Add_Message(x_return_status);
3094 --
3095 -- Debug Statements
3096 --
3097 IF l_debug_on THEN
3098 WSH_DEBUG_SV.pop(l_module_name);
3099 END IF;
3100 --
3101 return;
3102 END IF;
3103
3104 IF Get_Fill_Percent%ISOPEN THEN
3105 CLOSE Get_Fill_Percent;
3106 END IF;
3107
3108 x_percent_fill := l_fill_percent;
3109
3110 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3111
3112 --
3113 -- Debug Statements
3114 --
3115 IF l_debug_on THEN
3116 WSH_DEBUG_SV.pop(l_module_name);
3117 END IF;
3118 --
3119 EXCEPTION
3120
3121 WHEN Others THEN
3122 WSH_UTIL_CORE.Default_Handler('WSH_CONTAINER_UTILITIES.Get_Fill_Percent');
3123 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3124
3125 --
3126 -- Debug Statements
3127 --
3128 IF l_debug_on THEN
3132 --
3129 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3130 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3131 END IF;
3133 END Get_Fill_Percent;
3134
3135
3136 /*
3137 -----------------------------------------------------------------------------
3138 PROCEDURE : Get Delivery Status
3139 PARAMETERS : p_container_instance_id - instance id for the container
3140 x_delivery_id - delivery id the container is assigned to
3141 x_del_status - status of delivery that the container is
3142 assigned to.
3143 x_return_status - return status of API
3144 DESCRIPTION : This procedure retrieves the delivery id and delivery status
3145 of the delivery that the container is assigned to.
3146 ------------------------------------------------------------------------------
3147 */
3148
3149
3150 PROCEDURE Get_Delivery_Status (
3151 p_container_instance_id IN NUMBER,
3152 x_delivery_id IN OUT NOCOPY NUMBER,
3153 x_del_status IN OUT NOCOPY VARCHAR2,
3154 x_return_status OUT NOCOPY VARCHAR2) IS
3155
3156 CURSOR Get_Delivery_Status (v_cont_instance_id NUMBER) IS
3157 SELECT wda.delivery_id, wnd.status_code
3158 FROM wsh_delivery_assignments_v wda, WSH_NEW_DELIVERIES wnd, WSH_DELIVERY_DETAILS wdd
3159 WHERE wda.delivery_detail_id = v_cont_instance_id
3160 AND wda.delivery_id = wnd.delivery_id
3161 AND wdd.delivery_detail_id = wda.delivery_detail_id
3162 AND wdd.container_flag in ('Y', 'C');
3163
3164 l_delivery_id NUMBER;
3165 l_del_status VARCHAR2(2);
3166
3167 --
3168 l_debug_on BOOLEAN;
3169 --
3170 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DELIVERY_STATUS';
3171 --
3172 BEGIN
3173
3174 --
3175 -- Debug Statements
3176 --
3177 --
3178 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3179 --
3180 IF l_debug_on IS NULL
3181 THEN
3182 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3183 END IF;
3184 --
3185 IF l_debug_on THEN
3186 WSH_DEBUG_SV.push(l_module_name);
3187 --
3188 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_INSTANCE_ID',P_CONTAINER_INSTANCE_ID);
3189 WSH_DEBUG_SV.log(l_module_name,'X_DELIVERY_ID',X_DELIVERY_ID);
3190 WSH_DEBUG_SV.log(l_module_name,'X_DEL_STATUS',X_DEL_STATUS);
3191 END IF;
3192 --
3193 OPEN Get_Delivery_Status (p_container_instance_id);
3194
3195 FETCH Get_Delivery_Status INTO l_delivery_id, l_del_status;
3196 IF (Get_Delivery_Status%FOUND) THEN
3197 x_del_status := l_del_status;
3198 x_delivery_id := l_delivery_id;
3199 CLOSE Get_Delivery_Status;
3200 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3201 --
3202 -- Debug Statements
3203 --
3204 IF l_debug_on THEN
3205 WSH_DEBUG_SV.pop(l_module_name);
3206 END IF;
3207 --
3208 return;
3209 ELSE
3210 x_del_status := 'NA';
3211 x_delivery_id := -99;
3212 CLOSE Get_Delivery_Status;
3213 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3214 --
3215 -- Debug Statements
3216 --
3217 IF l_debug_on THEN
3218 WSH_DEBUG_SV.pop(l_module_name);
3219 END IF;
3220 --
3221 return;
3222 END IF;
3223
3224 IF Get_Delivery_Status%ISOPEN THEN
3225 CLOSE Get_Delivery_Status;
3226 END IF;
3227
3228 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3229
3230 --
3231 -- Debug Statements
3232 --
3233 IF l_debug_on THEN
3234 WSH_DEBUG_SV.pop(l_module_name);
3235 END IF;
3236 --
3237 EXCEPTION
3238
3239 WHEN Others THEN
3240 WSH_UTIL_CORE.Default_Handler('WSH_CONTAINER_UTILITIES.Get_Delivery_Status');
3241 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3242
3243 --
3244 -- Debug Statements
3245 --
3246 IF l_debug_on THEN
3247 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3248 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3249 END IF;
3250 --
3251 END Get_Delivery_Status;
3252
3253 /*
3254 -----------------------------------------------------------------------------
3255 PROCEDURE : Validate_Container
3256 PARAMETERS : p_container_name - container name that needs to be validated.
3257 p_container_instance_id - the delivery detail id for the
3258 container that needs to be updated.
3259 x_return_status - return status of API
3260 DESCRIPTION : This procedure takes in the container name and existing
3261 container id (detail id) and checks to see if the container
3262 that is being updated is assigned to a closed, confirmed or
3263 in-transit delivery. If it is, no update is allowed - if not,
3264 only the container name can be updated if the name is not a
3265 duplicate of an existing container.
3266 ------------------------------------------------------------------------------
3267 */
3268
3269
3270 PROCEDURE Validate_Container (
3271 p_container_name IN VARCHAR2,
3272 p_container_instance_id IN NUMBER,
3273 x_return_status OUT NOCOPY VARCHAR2) IS
3274
3275 CURSOR Check_Dup_Cont IS
3276 SELECT delivery_detail_id
3280
3277 FROM WSH_DELIVERY_DETAILS
3278 WHERE container_name = p_container_name
3279 AND container_flag in ('Y', 'C');
3281 l_cont_instance_id NUMBER;
3282 l_del_id NUMBER;
3283 l_del_sts VARCHAR2(10);
3284
3285 --
3286 l_debug_on BOOLEAN;
3287 --
3288 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_CONTAINER';
3289 --
3290 BEGIN
3291
3292 --
3293 -- Debug Statements
3294 --
3295 --
3296 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3297 --
3298 IF l_debug_on IS NULL
3299 THEN
3300 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3301 END IF;
3302 --
3303 IF l_debug_on THEN
3304 WSH_DEBUG_SV.push(l_module_name);
3305 --
3306 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_NAME',P_CONTAINER_NAME);
3307 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_INSTANCE_ID',P_CONTAINER_INSTANCE_ID);
3308 END IF;
3309 --
3310 IF p_container_name IS NULL THEN
3311 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_INVALID_NAME');
3312 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3313 WSH_UTIL_CORE.Add_Message(x_return_status);
3314 --
3315 -- Debug Statements
3316 --
3317 IF l_debug_on THEN
3318 WSH_DEBUG_SV.pop(l_module_name);
3319 END IF;
3320 --
3321 return;
3322 END IF;
3323
3324 IF p_container_instance_id IS NULL THEN
3325 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_CONTAINER');
3326 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3327 WSH_UTIL_CORE.Add_Message(x_return_status);
3328 --
3329 -- Debug Statements
3330 --
3331 IF l_debug_on THEN
3332 WSH_DEBUG_SV.pop(l_module_name);
3333 END IF;
3334 --
3335 return;
3336 END IF;
3337
3338 OPEN Check_Dup_Cont;
3339
3340 FETCH Check_Dup_Cont INTO
3341 l_cont_instance_id;
3342
3343 IF (Check_Dup_Cont%FOUND AND l_cont_instance_id <> p_container_instance_id) THEN
3344 CLOSE Check_Dup_Cont;
3345 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_NAME_DUPLICATE');
3346 FND_MESSAGE.SET_TOKEN('CONT_NAME',p_container_name);
3347 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3348 WSH_UTIL_CORE.Add_Message(x_return_status);
3349 --
3350 -- Debug Statements
3351 --
3352 IF l_debug_on THEN
3353 WSH_DEBUG_SV.pop(l_module_name);
3354 END IF;
3355 --
3356 return;
3357 END IF;
3358
3359 IF Check_Dup_Cont%ISOPEN THEN
3360 CLOSE Check_Dup_Cont;
3361 END IF;
3362
3363 --
3364 -- Debug Statements
3365 --
3366 IF l_debug_on THEN
3367 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.GET_DELIVERY_STATUS',WSH_DEBUG_SV.C_PROC_LEVEL);
3368 END IF;
3369 --
3370 WSH_CONTAINER_UTILITIES.Get_Delivery_Status (
3371 p_container_instance_id,
3372 l_del_id,
3373 l_del_sts,
3374 x_return_status);
3375
3376 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3377 --
3378 -- Debug Statements
3379 --
3380 IF l_debug_on THEN
3381 WSH_DEBUG_SV.pop(l_module_name);
3382 END IF;
3383 --
3384 return;
3385 END IF;
3386
3387 IF (nvl(l_del_id,-99) <> -99) THEN
3388
3389 IF (nvl(l_del_sts,'N/A') <> 'OP') THEN
3390 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_DEL_STS_INVALID');
3391 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3392 WSH_UTIL_CORE.Add_Message(x_return_status);
3393 --
3394 -- Debug Statements
3395 --
3396 IF l_debug_on THEN
3397 WSH_DEBUG_SV.pop(l_module_name);
3398 END IF;
3399 --
3400 return;
3401 END IF;
3402 END IF;
3403
3404 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3405
3406 --
3407 -- Debug Statements
3408 --
3409 IF l_debug_on THEN
3410 WSH_DEBUG_SV.pop(l_module_name);
3411 END IF;
3412 --
3413 EXCEPTION
3414
3415 WHEN Others THEN
3419 --
3416 WSH_UTIL_CORE.Default_Handler('WSH_CONTAINER_UTILITIES.Validate_Container');
3417 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3418
3420 -- Debug Statements
3421 --
3422 IF l_debug_on THEN
3423 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3424 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3425 END IF;
3426 --
3427 END Validate_Container;
3428
3429 END WSH_CONTAINER_UTILITIES;
[Home] [Help]