[Home] [Help]
PACKAGE BODY: APPS.WSH_WVX_PVT
Source
1 PACKAGE BODY WSH_WVX_PVT as
2 /* $Header: WSHUTWXB.pls 115.12 99/08/11 19:23:07 porting ship $ */
3
4 -- Name x_order_net_wt_in_delivery
5 -- Purpose Calculates the net weight of order's items in a delivery.
6 -- SC only.
7
8 -- Arguments
9 -- order_number (if NULL, delivery's net weight is computed)
10 -- order_type_id (if NULL, ignore this type)
11 -- delivery_id
12 -- weight_uom
13 -- RETURN number
14
15 FUNCTION x_order_net_wt_in_delivery(
16 order_number IN NUMBER,
17 order_type_id IN NUMBER,
18 delivery_id IN NUMBER,
19 weight_uom IN VARCHAR2)
20 RETURN NUMBER IS
21
22 CURSOR items_weight(x_del_id NUMBER, x_order_n NUMBER, x_order_t NUMBER) IS
23 SELECT SUM( NVL(msi.unit_weight, 0) *
24 WSH_WV_PVT.convert_uom(spl.unit_code,
25 msi.primary_uom_code,
26 NVL(spld.shipped_quantity,
27 spld.requested_quantity),
28 spl.inventory_item_id) ) weight,
29 msi.weight_uom_code uom
30 FROM so_picking_line_details spld,
31 so_picking_lines_all spl,
32 so_lines_all sl,
33 so_headers_all sh,
34 mtl_system_items msi,
35 wsh_deliveries wd
36 WHERE spld.delivery_id = x_del_id
37 AND wd.delivery_id = x_del_id
38 AND spl.picking_line_id = spld.picking_line_id
39 AND spl.picking_header_id+0 > 0 -- NOT backordered
40 AND sl.line_id = spl.order_line_id
41 AND sh.header_id = sl.header_id
42 AND sh.order_number = x_order_n
43 AND sh.order_type_id = NVL(x_order_t, sh.order_type_id)
44 AND msi.inventory_item_id = spl.inventory_item_id
45 AND msi.organization_id = wd.organization_id
46 GROUP BY msi.weight_uom_code;
47
48 CURSOR delivery_weight(x_del_id NUMBER) IS
49 SELECT SUM( NVL(msi.unit_weight, 0) *
50 WSH_WV_PVT.convert_uom(spl.unit_code,
51 msi.primary_uom_code,
52 -- if qty is NULL, it must be 0 here.
53 NVL(spld.shipped_quantity, 0),
54 spl.inventory_item_id) ) weight,
55 msi.weight_uom_code uom
56 FROM so_picking_line_details spld,
57 so_picking_lines_all spl,
58 mtl_system_items msi,
59 wsh_deliveries wd
60 WHERE spld.delivery_id = x_del_id
61 AND wd.delivery_id = x_del_id
62 AND spl.picking_line_id = spld.picking_line_id
63 AND spl.picking_header_id+0 > 0 -- NOT backordered
64 AND msi.inventory_item_id = spl.inventory_item_id
65 AND msi.organization_id = wd.organization_id
66 GROUP BY msi.weight_uom_code;
67
68 -- for both delivery and order.
69 CURSOR ato(x_del_id NUMBER, x_order_n NUMBER, x_order_t NUMBER) IS
70 SELECT l.line_id ato_line_id,
71 -- if qty is NULL, it must be 0 here.
72 sum(NVL(pld.shipped_quantity, 0)) qty
73 FROM so_picking_line_details pld,
74 so_picking_lines_all pl,
75 so_line_details ld,
76 so_lines_all l,
77 so_headers_all h,
78 mtl_system_items i_msi, -- configuration item
79 mtl_system_items m_msi -- model
80 WHERE pld.delivery_id = x_del_id
81 AND pl.picking_line_id = pld.picking_line_id
82 AND pl.picking_header_id+0 > 0 -- NOT backordered
83 AND ld.line_detail_id = pl.line_detail_id
84 AND ld.configuration_item_flag = 'Y'
85 AND l.line_id = ld.line_id
86 AND l.ato_flag = 'Y'
87 AND l.ato_line_id IS NULL
88 AND h.header_id = l.header_id
89 AND h.order_number = NVL(x_order_n, h.order_number)
90 AND h.order_type_id = NVL(x_order_t, h.order_type_id)
91 AND i_msi.inventory_item_id = pl.inventory_item_id
92 AND i_msi.organization_id = pl.warehouse_id
93 AND m_msi.inventory_item_id = l.inventory_item_id
94 AND m_msi.organization_id = pl.warehouse_id
95 AND -- same weight attributes
96 (NVL(m_msi.weight_uom_code, 'EMPTY')
97 = NVL(i_msi.weight_uom_code, 'EMPTY')
98 AND NVL(m_msi.unit_weight, 0) = NVL(i_msi.unit_weight, 0))
99 GROUP BY l.line_id;
100
101 net_weight NUMBER;
102 ato_weight NUMBER;
103 ato_volume NUMBER;
104 status NUMBER := 0;
105
106 BEGIN
107
108 net_weight := 0;
109
110 if order_number is not null then
111 for w in items_weight(delivery_id, order_number, order_type_id) loop
112 net_weight := net_weight
113 + WSH_WV_PVT.convert_uom(w.uom, weight_uom, w.weight);
114 end loop;
115 else
116 for w in delivery_weight(delivery_id) loop
117 net_weight := net_weight
118 + WSH_WV_PVT.convert_uom(w.uom, weight_uom, w.weight);
119 end loop;
120 end if;
121
122 FOR a in ato(delivery_id, order_number, order_type_id) LOOP
123 WSH_WVX_PVT.ato_weight_volume('SC',
124 a.ato_line_id,
125 a.qty,
126 weight_uom,
127 ato_weight,
128 NULL,
129 ato_volume,
130 status);
131 net_weight := net_weight + ato_weight;
132 END LOOP;
133
134 return net_weight;
135
136
137 EXCEPTION
138 WHEN OTHERS THEN
139 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
140 FND_MESSAGE.Set_Token('PACKAGE','wsh_wvx_pvt(14)');
141 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
142 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
143 return 0;
144 END x_order_net_wt_in_delivery;
145
146
147
148 -- Name ato_weight_volume
149 -- Purpose calculate the weight/volume of the ATO model's components.
150 --
151 -- Regardless of the source (DPW, BO or SC), the ATO model's
152 -- physical attributes are not added because they are already
153 -- added (as "standard" items)--this is an important assumption.
154 -- (BO is DPW where the ATO config. item has been backordered.)
155 -- That is, for BO and SC, this routine would be invoked
156 -- only if the configuration item's physical attributes
157 -- match the ATO model's (meaning that BOM has copied them
158 -- directly).
159 -- Arguments
160 -- source (DPW or SC or BO)
161 -- ato_line_id
162 -- quantity
163 -- weight_uom (if NULL, don't calculate weight)
164 -- weight (OUTPUT)
165 -- volume_uom (if NULL, don't calculate volume)
166 -- volume (OUTPUT)
167 -- status (-1 = error, 0 = success, +1 = warning)
168
169 PROCEDURE ato_weight_volume(
170 source IN VARCHAR2,
171 ato_line_id IN NUMBER,
172 quantity IN NUMBER,
173 weight_uom IN VARCHAR2,
174 weight OUT NUMBER,
175 volume_uom IN VARCHAR2,
176 volume OUT NUMBER,
177 status IN OUT NUMBER)
178 IS
179
180 CURSOR ato_model(x_a_line_id NUMBER) IS
181 SELECT (NVL(sl.ordered_quantity, 0) - NVL(sl.cancelled_quantity, 0)) qty
182 FROM so_lines_all sl
183 WHERE sl.line_id = x_a_line_id;
184
185
186 -- The two cursors ato_weight and ato_volume have the
187 -- same WHERE clause. The only difference is weight vs. volume.
188
189 CURSOR ato_weight(x_a_line_id NUMBER) IS
190 SELECT SUM( NVL(msi.unit_weight, 0) *
191 WSH_WV_PVT.convert_uom(sld.unit_code,
192 msi.primary_uom_code,
193 sld.quantity,
194 sld.inventory_item_id) ) weight,
195 msi.weight_uom_code uom
196 FROM so_line_details sld,
197 so_lines_all sl,
198 mtl_system_items msi
199 WHERE sl.ato_line_id = x_a_line_id
200 AND sld.line_id = sl.line_id
201 AND sld.included_item_flag = 'N'
202 AND NVL(sld.configuration_item_flag, 'N') = 'N'
203 AND msi.inventory_item_id = sld.inventory_item_id
204 AND msi.organization_id = sld.warehouse_id
205 GROUP BY msi.weight_uom_code;
206
207 CURSOR ato_volume(x_a_line_id NUMBER) IS
208 SELECT SUM( NVL(msi.unit_volume, 0) *
209 WSH_WV_PVT.convert_uom(sld.unit_code,
210 msi.primary_uom_code,
211 sld.quantity,
212 sld.inventory_item_id) ) volume,
213 msi.volume_uom_code uom
214 FROM so_line_details sld,
215 so_lines_all sl,
216 mtl_system_items msi
217 WHERE sl.ato_line_id = x_a_line_id
218 AND sld.line_id = sl.line_id
219 AND sld.included_item_flag = 'N'
220 AND NVL(sld.configuration_item_flag, 'N') = 'N'
221 AND msi.inventory_item_id = sld.inventory_item_id
222 AND msi.organization_id = sld.warehouse_id
223 GROUP BY msi.volume_uom_code;
224
225 model_quantity NUMBER;
226 ratio NUMBER;
227 model_weight NUMBER := 0;
228 model_volume NUMBER := 0;
229 w_uom MTL_SYSTEM_ITEMS.weight_uom_code%TYPE;
230 v_uom MTL_SYSTEM_ITEMS.volume_uom_code%TYPE;
231
232 BEGIN
233 weight := 0;
234 volume := 0;
235
236 -- If quantity is 0, skip the unnecessary effort.
237 IF quantity = 0 THEN
238 RETURN;
239 END IF;
240
241 OPEN ato_model(ato_line_id);
242 FETCH ato_model INTO model_quantity;
243 IF ato_model%NOTFOUND THEN
244 return;
245 END IF;
246 CLOSE ato_model;
247
248 -- Compute the ratio, to adjust the weight/volume.
249 IF model_quantity <= 0 THEN
250 -- In this singular case, just return.
251 ratio := 0;
252 return;
253 ELSE
254 ratio := quantity / model_quantity;
255 END IF;
256
257 IF weight_uom IS NOT NULL THEN
258 FOR aw IN ato_weight(ato_line_id) LOOP
259 model_weight := model_weight
260 + WSH_WV_PVT.convert_uom(aw.uom, weight_uom, aw.weight);
261 END LOOP;
262 weight := model_weight * ratio;
263 END IF;
264
265 IF volume_uom IS NOT NULL THEN
266 FOR av IN ato_volume(ato_line_id) LOOP
267 model_volume := model_volume
268 + WSH_WV_PVT.convert_uom(av.uom, volume_uom, av.volume);
269 END LOOP;
270 volume := model_volume * ratio;
271 END IF;
272
273 EXCEPTION
274 WHEN OTHERS THEN
275 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
276 FND_MESSAGE.Set_Token('PACKAGE','wsh_wvx_pvt(15)');
277 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
278 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
279 status := -1;
280 END ato_weight_volume;
281
282
283 -- Name x_containers_load_check
284 -- Purpose Checks whether any container is overloaded (based on
285 -- container load relationships or weight or volume).
286 -- Also checks whether the minimum fill percentages are met.
287 --
288 -- Note If function returns TRUE and status is 1, it means that
289 -- some containers are underpacked, but none is overpacked.
290 --
291 -- Assumption This function will be called after all containers' weights
292 -- are calculated or input by the user.
293
294 -- Arguments
295 -- delivery_id
296 -- organization_id
297 -- pack_mode specifies what NULL shipped_quantity
298 -- will be:
299 -- 'ALL' -- non-zero, shipped
300 -- 'ENTERED' -- zero, backordered
301 -- status (input/output)
302 -- -1 = error; 0 = success; 1 = warning
303 -- RETURN BOOLEAN (TRUE = within maximum load
304 -- OR with no information found
305 -- FALSE = exceeds maximum load, check warning)
306
307 FUNCTION x_containers_load_check(
308 delivery_id IN NUMBER,
309 pack_mode IN VARCHAR2 DEFAULT 'ALL',
310 status IN OUT NUMBER)
311 RETURN BOOLEAN
312 IS
313
314 cursor delivery_uoms(x_delivery_id NUMBER) is
315 select wd.weight_uom_code,
316 wd.volume_uom_code,
317 wd.organization_id
318 from wsh_deliveries wd
319 where wd.delivery_id = x_delivery_id;
320
321 -- Do not NVL the values because NULL means "don't care".
322 cursor cont_info(x_delivery_id NUMBER) is
323 select wpc.sequence_number sequence_number,
324 wpc.container_id container_id,
325 wpc.container_inventory_item_id inventory_item_id,
326 wpc.quantity quantity,
327 -- net_weight = gross_weight - tare_weight
328 WSH_WV_PVT.convert_uom(wpc.weight_uom_code,
329 msi.weight_uom_code,
330 NVL(wpc.gross_weight, 0),
331 msi.inventory_item_id)
332 - wpc.quantity * NVL(msi.unit_weight,0) net_weight,
333 msi.maximum_load_weight * wpc.quantity max_weight,
334 msi.weight_uom_code weight_uom,
335 msi.internal_volume * wpc.quantity max_volume,
336 msi.volume_uom_code volume_uom,
337 msi.minimum_fill_percent min_fill_percent
338 from wsh_packed_containers wpc,
339 mtl_system_items msi
340 where wpc.delivery_id = x_delivery_id
341 and msi.inventory_item_id = wpc.container_inventory_item_id
342 and msi.organization_id = wpc.organization_id
343 order by wpc.sequence_number;
344
345 overloaded BOOLEAN := FALSE;
346 underloaded BOOLEAN := FALSE;
347
348 -- tolerance_factor to scale the maximum weight/volume,
349 -- to tolerate any errors in unit conversions and
350 -- perhaps, measurements.
351 -- 1.001 means tolerance of one part per thousand (.001).
352 -- Do not use tolernace_factor
353 -- with fill percentages, as they are truncated (toward 0).
354
355 tolerance_factor NUMBER := 1.001;
356
357
358
359 organization_id NUMBER := NULL;
360 net_volume NUMBER := 0;
361 fill_percent NUMBER := 0;
362 message_name VARCHAR2(30);
363
364 message_count NUMBER := 0;
365 message_string VARCHAR2(300) := '';
366 message_text1 VARCHAR2(300) := '';
367 message_text2 VARCHAR2(300) := '';
368 message_text3 VARCHAR2(300) := '';
369 message_text4 VARCHAR2(300) := '';
370
371 -- statistical variables
372 -- count = number of containers that are overloaded
373 -- net_excess = sum of overload exceess (e.g., sum(weight - max_weight))
374 -- sequence = identification of the worst container
375 -- seq_excess = relative amount to beat to become the worst!
376 -- relative amount = 100% * (net_weight / max_weight)
377 --
378 -- Note: excess weight and volume are expressed in w_uom and v_uom.
379
380 excess NUMBER := 0;
381 w_uom WSH_DELIVERIES.weight_uom_code%TYPE := NULL;
382 v_uom WSH_DELIVERIES.volume_uom_code%TYPE := NULL;
383
384 -- Weight
385 w_count NUMBER := 0;
386 w_net_excess NUMBER := 0;
387 w_sequence WSH_PACKED_CONTAINERS.sequence_number%TYPE;
388 w_seq_excess NUMBER := 0;
389
390 -- Volume
391 v_count NUMBER := 0;
392 v_net_excess NUMBER := 0;
393 v_sequence WSH_PACKED_CONTAINERS.sequence_number%TYPE;
394 v_seq_excess NUMBER := 0;
395
396 -- Load (based on load relationships)
397 l_count NUMBER := 0;
398 l_sum_excess NUMBER := 0;
399 l_avg_excess NUMBER := 0;
400 l_sequence WSH_PACKED_CONTAINERS.sequence_number%TYPE;
401 l_seq_excess NUMBER := 0;
402
403 -- Underpacked (based on minimum fill percentage and its basis)
404 fill_basis_flag VARCHAR2(1);
405 u_count NUMBER := 0;
406 u_sequence WSH_PACKED_CONTAINERS.sequence_number%TYPE;
407 u_seq_fill NUMBER := 0;
408 u_least_packed NUMBER := 1;
409 u_factor NUMBER := 0; -- temporary result holder
410
411 BEGIN
412 status := 0;
413
414 OPEN delivery_uoms(delivery_id);
415 FETCH delivery_uoms INTO w_uom, v_uom, organization_id;
416 CLOSE delivery_uoms;
417
418
419 WSH_PARAMETERS_PVT.get_param_value(organization_id,
420 'PERCENT_FILL_BASIS_FLAG',
421 fill_basis_flag);
422 IF fill_basis_flag not in ('Q', 'V', 'W') THEN
423 fill_basis_flag := 'W'; -- default to weight as basis.
424 END IF;
425
426
427 FOR container IN cont_info(delivery_id) LOOP
428
429 -- remember: max_weight and max_volume may be NULL.
430 -- remember: comparisons with NULL are always false.
431 -- Thus, if max_weight or max_volume is not defined, it's not checked.
432
433 ---
434 ---
435 --- Check the container's load weight
436 ---
437 ---
438
439 IF container.net_weight > container.max_weight * tolerance_factor THEN
440
441 overloaded := TRUE;
442 w_count := w_count + 1; -- count container sequence, not quantity
443
444 IF w_uom IS NULL THEN
445 -- in case delivery doesn't have one, use a backup uom.
446 w_uom := container.weight_uom;
447 END IF;
448
449 -- calculate the absolute excess
450 excess := WSH_WV_PVT.convert_uom(container.weight_uom,
451 w_uom,
452 (container.net_weight
453 - container.max_weight));
454
455 w_net_excess := w_net_excess + excess;
456
457 -- now calculate the relative excess to find the worst container
458 IF container.max_weight = 0 THEN
459 -- bizarre case, but can't be the worst.
460 excess := NULL;
461 ELSE
462 excess := container.net_weight / container.max_weight;
463 END IF;
464
465 IF (excess > w_seq_excess) THEN
466 w_sequence := container.sequence_number;
467 w_seq_excess := excess;
468 END IF;
469
470 END IF; -- container.net_weight > container.max_weight
471
472 ---
473 ---
474 --- Check the container's load volume volume
475 ---
476 ---
477
478 WSH_WVX_PVT.container_net_volume(delivery_id,
479 organization_id,
480 container.container_id,
481 container.sequence_number,
482 container.volume_uom,
483 pack_mode,
484 net_volume);
485
486 IF net_volume > container.max_volume * tolerance_factor THEN
487
488 overloaded := TRUE;
489 v_count := v_count + 1; -- count container sequence, not quantity
490
491 IF v_uom IS NULL THEN
492 -- in case delivery doesn't have one, use a backup uom.
493 v_uom := container.volume_uom;
494 END IF;
495
496 -- calculate the absolute excess
497 excess := WSH_WV_PVT.convert_uom(container.volume_uom,
498 v_uom,
499 (net_volume
500 - container.max_volume));
501
502 v_net_excess := v_net_excess + excess;
503
504 -- now calculate the relative excess to find the worst container
505 IF container.max_volume = 0 THEN
506 -- bizarre case, but can't be the worst.
507 excess := NULL;
508 ELSE
509 excess := net_volume / container.max_volume;
510 END IF;
511
512 IF (excess > v_seq_excess) THEN
513 v_sequence := container.sequence_number;
514 v_seq_excess := excess;
515 END IF;
516
517 END IF; -- net_volume > container.max_volume
518
519 ---
520 ---
521 --- Check the container's load fill percentage doesn't exceed 100%.
522 ---
523 ---
524
525 WSH_WVX_PVT.container_fill_percent(delivery_id,
526 organization_id,
527 container.container_id,
528 container.inventory_item_id,
529 container.sequence_number,
530 container.quantity,
531 pack_mode,
532 fill_percent);
533
534 IF fill_percent > 100 THEN
535
536 overloaded := TRUE;
537 l_count := l_count + 1; -- count container sequence, not quantity
538
539 excess := fill_percent - 100;
540
541 l_sum_excess := l_sum_excess + excess;
542
543 IF (excess > l_seq_excess) THEN
544 l_sequence := container.sequence_number;
545 l_seq_excess := excess;
546 END IF;
547
548 END IF; -- fill_percent > 100
549
550
551 ---
552 ---
553 --- Check the container's minimum fill percentage if it's more than 0%.
554 ---
555 ---
556
557 IF container.min_fill_percent > 0 THEN
558
559 -- recalculate fill_percent if necessary.
560 -- Note that if any variable is NULL, fill_percent will be NULL, too.
561 -- But watch out for division by 0.
562
563 IF fill_basis_flag = 'Q' THEN -- use load quantity (relationships)
564
565 NULL; -- fill_percent already calculated
566
567 ELSIF fill_basis_flag = 'V' THEN -- use volume
568
569 IF container.max_volume > 0 THEN
570 fill_percent := trunc(100 * net_volume
571 / container.max_volume);
572 ELSE
573 fill_percent := NULL;
574 END IF;
575
576 ELSIF fill_basis_flag = 'W' THEN -- use weight
577
578 IF container.max_weight > 0 THEN
579 fill_percent := trunc(100 * container.net_weight
580 / container.max_weight);
581 ELSE
582 fill_percent := NULL;
583 END IF;
584
585 END IF;
586
587 IF fill_percent < container.min_fill_percent THEN
588 underloaded := TRUE;
589 u_count := u_count + 1; -- count container sequence, not quantity
590
591 u_factor := fill_percent / container.min_fill_percent;
592 IF u_factor < u_least_packed THEN
593 u_sequence := container.sequence_number;
594 u_least_packed := u_factor;
595 END IF;
596 END IF;
597
598 END IF; -- container.min_fill_percent > 0
599
600 END LOOP;
601
602
603 IF w_count > 0 THEN
604 w_net_excess := ceil(w_net_excess * 1000) / 1000;
605 FND_MESSAGE.Set_Name('OE', 'WSH_WV_CONT_OVERLOAD_W');
606 FND_MESSAGE.Set_Token('W_COUNT', to_char(w_count));
607 FND_MESSAGE.Set_Token('WEIGHT_AMOUNT', to_char(w_net_excess));
608 FND_MESSAGE.Set_Token('WEIGHT_UOM', w_uom);
609 FND_MESSAGE.Set_Token('W_SEQ_NUMBER', to_char(w_sequence));
610
611 message_string := FND_MESSAGE.Get;
612 WSH_WVX_PVT.set_messages(message_string,
613 message_count,
614 message_text1, message_text2, message_text3, message_text4);
615 END IF;
616
617 IF v_count > 0 THEN
618 v_net_excess := ceil(v_net_excess * 1000) / 1000;
619 FND_MESSAGE.Set_Name('OE', 'WSH_WV_CONT_OVERLOAD_V');
620 FND_MESSAGE.Set_Token('V_COUNT', to_char(v_count));
621 FND_MESSAGE.Set_Token('VOLUME_AMOUNT', to_char(v_net_excess));
622 FND_MESSAGE.Set_Token('VOLUME_UOM', v_uom);
623 FND_MESSAGE.Set_Token('V_SEQ_NUMBER', to_char(v_sequence));
624
625 message_string := FND_MESSAGE.Get;
626 WSH_WVX_PVT.set_messages(message_string,
627 message_count,
628 message_text1, message_text2, message_text3, message_text4);
629 END IF;
630
631 IF l_count > 0 THEN
632 -- overload percentage is average of the containers' overload percentage
633 l_avg_excess := ceil(l_sum_excess / l_count);
634 FND_MESSAGE.Set_Name('OE', 'WSH_WV_CONT_OVERLOAD_L');
635 FND_MESSAGE.Set_Token('L_COUNT', to_char(l_count));
636 FND_MESSAGE.Set_Token('LOAD_PERCENT', to_char(l_avg_excess));
637 FND_MESSAGE.Set_Token('L_SEQ_NUMBER', to_char(l_sequence));
638
639 message_string := FND_MESSAGE.Get;
640 WSH_WVX_PVT.set_messages(message_string,
641 message_count,
642 message_text1, message_text2, message_text3, message_text4);
643 END IF;
644
645 IF u_count > 0 THEN
646 FND_MESSAGE.Set_Name('OE', 'WSH_WV_CONT_UNDERLOAD');
647 FND_MESSAGE.Set_Token('U_COUNT', to_char(u_count));
648 FND_MESSAGE.Set_Token('U_SEQ_NUMBER', to_char(u_sequence));
649
650 message_string := FND_MESSAGE.Get;
651 WSH_WVX_PVT.set_messages(message_string,
652 message_count,
653 message_text1, message_text2, message_text3, message_text4);
654 END IF;
655
656 -- underloaded or overloaded will generate the messages above.
657
658 IF message_count > 0 THEN
659 status := 1;
660
661 FND_MESSAGE.Set_Name('OE', 'WSH_WV_COMBO');
662 FND_MESSAGE.Set_Token('MESSAGE_1', message_text1);
663 FND_MESSAGE.Set_Token('MESSAGE_2', message_text2);
664 FND_MESSAGE.Set_Token('MESSAGE_3', message_text3);
665 FND_MESSAGE.Set_Token('MESSAGE_4', message_text4);
666 END IF;
667
668 RETURN NOT overloaded;
669
670 EXCEPTION
671 WHEN OTHERS THEN
672 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
673 FND_MESSAGE.Set_Token('PACKAGE','wsh_wvx_pvt(1)');
674 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
675 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
676 IF delivery_uoms%ISOPEN THEN
677 CLOSE delivery_uoms;
678 END IF;
679 IF cont_info%ISOPEN THEN
680 CLOSE cont_info;
681 END IF;
682 status := -1;
683 RETURN NOT overloaded;
684 END x_containers_load_check;
685
686
687 -- Name container_net_volume (not recursive, does not update tables!)
688 -- Purpose Calculates the net volume of items and packed containers
689 -- in this container
690
691 -- Arguments
692 -- delivery_id
693 -- organization_id
694 -- container_id
695 -- sequence_number
696 -- master_uom
697 -- pack_mode specifies what NULL shipped_quantity
698 -- will be:
699 -- 'ALL' -- non-zero, shipped
700 -- 'ENTERED' -- zero, backordered
701 -- volume (output only)
702
703 PROCEDURE container_net_volume(
704 delivery_id IN NUMBER,
705 organization_id IN NUMBER,
706 container_id IN NUMBER,
707 sequence_number IN NUMBER,
708 master_uom IN VARCHAR2,
709 pack_mode IN VARCHAR2,
710 volume OUT NUMBER) IS
711
712 CURSOR container_volume(x_del_id NUMBER, x_o_id NUMBER, x_seq_num NUMBER) IS
713 SELECT pc.container_inventory_item_id inventory_item_id,
714 NVL(msi.unit_volume, 0) * pc.quantity volume,
715 msi.volume_uom_code uom_code
716 FROM wsh_packed_containers pc,
717 mtl_system_items msi
718 WHERE pc.delivery_id = x_del_id
719 AND pc.container_inventory_item_id = msi.inventory_item_id
720 AND msi.organization_id = x_o_id
721 AND NVL(pc.parent_sequence_number, -1) = NVL(x_seq_num, -1);
722
723 CURSOR contents_volume(x_del_id NUMBER, x_o_id NUMBER,
724 x_cont_id NUMBER, x_ship_f NUMBER) IS
725 SELECT pl.inventory_item_id,
726 SUM( NVL(msi.unit_volume, 0) *
727 WSH_WV_PVT.convert_uom(pl.unit_code,
728 msi.primary_uom_code,
729 NVL(cc.shipped_quantity,
730 x_ship_f*cc.requested_quantity),
731 pl.inventory_item_id) ) volume,
732 msi.volume_uom_code uom_code
733 FROM so_picking_line_details cc,
734 so_picking_lines_all pl,
735 mtl_system_items msi
736 WHERE cc.container_id = x_cont_id
737 AND cc.delivery_id = x_del_id
738 AND pl.picking_line_id = cc.picking_line_id
739 AND pl.picking_header_id+0 > 0
740 AND pl.inventory_item_id = msi.inventory_item_id
741 AND msi.organization_id = x_o_id
742 GROUP BY pl.inventory_item_id, volume_uom_code;
743
744 CURSOR packed_ato(x_del_id NUMBER, x_cont_id NUMBER, x_ship_f NUMBER) IS
745 SELECT l.line_id ato_line_id,
746 sum(NVL(pld.shipped_quantity,
747 x_ship_f*pld.requested_quantity)) qty
748 FROM so_picking_line_details pld,
749 so_picking_lines_all pl,
750 so_line_details ld,
751 so_lines_all l,
752 mtl_system_items i_msi, -- configuration item
753 mtl_system_items m_msi -- model
754 WHERE pld.delivery_id = x_del_id
755 AND pld.container_id = x_cont_id
756 AND pl.picking_line_id = pld.picking_line_id
757 AND pl.picking_header_id+0 > 0 -- NOT backordered
758 AND ld.line_detail_id = pl.line_detail_id
759 AND ld.configuration_item_flag = 'Y'
760 AND l.line_id = ld.line_id
761 AND l.ato_flag = 'Y'
762 AND l.ato_line_id IS NULL
763 AND i_msi.inventory_item_id = pl.inventory_item_id
764 AND i_msi.organization_id = pl.warehouse_id
765 AND m_msi.inventory_item_id = l.inventory_item_id
766 AND m_msi.organization_id = pl.warehouse_id
767 AND -- same volume attributes
768 (NVL(m_msi.volume_uom_code, 'EMPTY')
769 = NVL(i_msi.volume_uom_code, 'EMPTY')
770 AND NVL(m_msi.unit_volume, 0) = NVL(i_msi.unit_volume, 0))
771 GROUP BY l.line_id;
772
773 net_volume NUMBER := 0;
774 ato_weight NUMBER;
775 ato_volume NUMBER;
776 ship_factor NUMBER := 0;
777 status NUMBER := 0;
778
779 BEGIN
780
781 If pack_mode = 'ALL' THEN
782 ship_factor := 1;
783 ELSE
784 ship_factor := 0;
785 END IF;
786
787 FOR container IN container_volume(delivery_id, organization_id,
788 sequence_number) LOOP
789 net_volume := net_volume
790 + WSH_WV_PVT.convert_uom(container.uom_code, master_uom,
791 container.volume,
792 container.inventory_item_id);
793 END LOOP;
794
795 FOR item IN contents_volume(delivery_id, organization_id,
796 container_id, ship_factor) LOOP
797 net_volume := net_volume
798 + WSH_WV_PVT.convert_uom(item.uom_code, master_uom,
799 item.volume,
800 item.inventory_item_id);
801 END LOOP;
802
803
804 FOR a IN packed_ato(delivery_id, container_id, ship_factor) LOOP
805 WSH_WVX_PVT.ato_weight_volume('SC',
806 a.ato_line_id,
807 a.qty,
808 NULL,
809 ato_weight,
810 master_uom,
811 ato_volume,
812 status);
813
814 net_volume := net_volume + ato_volume;
815 END LOOP;
816
817 volume := net_volume;
818
819 EXCEPTION
820 WHEN OTHERS THEN
821 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
822 FND_MESSAGE.Set_Token('PACKAGE','wsh_wvx_pvt(2)');
823 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
824 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
825 IF container_volume%ISOPEN THEN
826 CLOSE container_volume;
827 END IF;
828 IF contents_volume%ISOPEN THEN
829 CLOSE contents_volume;
830 END IF;
831 IF packed_ato%ISOPEN THEN
832 CLOSE packed_ato;
833 END IF;
834 volume := NULL;
835 END container_net_volume;
836
837
838 -- Name container_fill_percent
839 -- Purpose Calculates the fill percentage of items and packed containers
840 -- in this container, based on their load relationships
841 -- Note: the fill percentage basis parameter is ignored;
842 -- That is, the basis is always Quantity in this procedure.
843
844 -- Arguments
845 -- delivery_id
846 -- organization_id
847 -- container_id
848 -- container_item_id
849 -- sequence_number
850 -- container_qty
851 -- pack_mode specifies what NULL shipped_quantity
852 -- will be:
853 -- 'ALL' -- non-zero, shipped
854 -- 'ENTERED' -- zero, backordered
855 -- fill_percent (output only)
856
857 PROCEDURE container_fill_percent(
858 delivery_id IN NUMBER,
859 organization_id IN NUMBER,
860 container_id IN NUMBER,
861 container_item_id IN NUMBER,
862 sequence_number IN NUMBER,
863 container_qty IN NUMBER,
864 pack_mode IN VARCHAR2,
865 fill_percent OUT NUMBER) IS
866
867 CURSOR cont_fill(x_del_id NUMBER, x_o_id NUMBER,
868 x_container_item_id NUMBER, x_seq_num NUMBER) IS
869 SELECT SUM(pc.quantity / cl.max_load_quantity) fill
870 FROM wsh_container_load cl,
871 wsh_packed_containers pc
872 WHERE pc.delivery_id = x_del_id
873 AND NVL(pc.parent_sequence_number, -1) = NVL(x_seq_num, -1)
874 AND cl.load_item_id = pc.container_inventory_item_id
875 AND cl.container_item_id = x_container_item_id
876 AND cl.master_organization_id =
877 (SELECT master_organization_id
878 FROM mtl_parameters
879 WHERE organization_id = x_o_id)
880 AND NVL(cl.max_load_quantity, 0) > 0
881 GROUP BY 1;
882
883 CURSOR contents_fill(x_del_id NUMBER, x_o_id NUMBER,
884 x_container_item_id NUMBER,
885 x_cont_id NUMBER, x_ship_f NUMBER) IS
886 SELECT SUM( WSH_WV_PVT.convert_uom(pl.unit_code,
887 msi.primary_uom_code,
888 NVL(pld.shipped_quantity,
889 x_ship_f*pld.requested_quantity))
890 / cl.max_load_quantity) fill
891 FROM wsh_container_load cl,
892 so_picking_line_details pld,
893 so_picking_lines_all pl,
894 mtl_system_items msi
895 WHERE pld.delivery_id = x_del_id
896 AND pld.container_id = x_cont_id
897 AND pl.picking_line_id = pld.picking_line_id
898 AND msi.inventory_item_id = pl.inventory_item_id
899 AND msi.organization_id = x_o_id
900 AND cl.load_item_id = pl.inventory_item_id
901 AND cl.container_item_id = x_container_item_id
902 AND cl.master_organization_id =
903 (SELECT master_organization_id
904 FROM mtl_parameters
905 WHERE organization_id = x_o_id)
906 AND NVL(cl.max_load_quantity, 0) > 0
907 GROUP BY 1;
908
909 fill NUMBER := 0;
910 ato_weight NUMBER;
911 ato_volume NUMBER;
912 ship_factor NUMBER := 0;
913
914 BEGIN
915
916 -- don't want to divide by zero at end of this function.
917 IF container_qty = 0 THEN
918 -- Besides, there's no container to fill.
919 fill_percent := 0;
920 return;
921 END IF;
922
923 If pack_mode = 'ALL' THEN
924 ship_factor := 1;
925 ELSE
926 ship_factor := 0;
927 END IF;
928
929 FOR container IN cont_fill(delivery_id, organization_id,
930 container_item_id,
931 sequence_number) LOOP
932 fill := fill + container.fill;
933 END LOOP;
934
935 FOR contents IN contents_fill(delivery_id, organization_id,
936 container_item_id,
937 container_id, ship_factor) LOOP
938 fill := fill + contents.fill;
939 END LOOP;
940
941 -- divide by container_qty to get the average fill percentage per container.
942 fill_percent := TRUNC(100 * fill / container_qty);
943
944 EXCEPTION
945 WHEN OTHERS THEN
946 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
947 FND_MESSAGE.Set_Token('PACKAGE','wsh_wvx_pvt(3)');
948 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
949 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
950 IF cont_fill%ISOPEN THEN
951 CLOSE cont_fill;
952 END IF;
953 IF contents_fill%ISOPEN THEN
954 CLOSE contents_fill;
955 END IF;
956 fill_percent := NULL;
957 END container_fill_percent;
958
959
960
961 PROCEDURE set_messages(message_string IN VARCHAR2,
962 message_count IN OUT NUMBER,
963 message_text1 IN OUT VARCHAR2,
964 message_text2 IN OUT VARCHAR2,
965 message_text3 IN OUT VARCHAR2,
966 message_text4 IN OUT VARCHAR2)
967 IS
968 BEGIN
969
970 IF message_count = 0 THEN
971 message_count := message_count + 1;
972 message_text1 := message_string;
973
974 ELSIF message_count = 1 THEN
975 message_count := message_count + 1;
976 message_text2 := message_string;
977
978 ELSIF message_count = 2 THEN
979 message_count := message_count + 1;
980 message_text3 := message_string;
981
982 ELSIF message_count = 3 THEN
983 message_count := message_count + 1;
984 message_text4 := message_string;
985 END IF;
986
987 END set_messages;
988
989
990 -- Name containers_net_weight
991 -- Purpose Calculates the net weight of order's items in this
992 -- container, also includes the weight of the items in the
993 -- inner containers.
994 --
995 -- Arguments
996 -- container_id
997 -- pack_mode
998 -- master_uom
999 -- net_weight
1000 PROCEDURE containers_net_weight(
1001 X_container_id IN NUMBER,
1002 X_organization_id IN NUMBER,
1003 X_pack_mode IN VARCHAR2,
1004 X_master_uom IN VARCHAR2,
1005 X_net_weight IN OUT NUMBER)
1006 IS
1007 CURSOR cont_net_weight(
1008 P_container_id NUMBER,
1009 P_organization_id NUMBER,
1010 P_to_uom VARCHAR2,
1011 P_pack_flag NUMBER)
1012 IS
1013 SELECT NVL(wsh_wv_pvt.convert_uom(msi.weight_uom_code,
1014 P_to_uom,
1015 NVL(msi.unit_weight, 0)) *
1016 NVL(pld.shipped_quantity,
1017 P_pack_flag *
1018 pld.requested_quantity),
1019 0) cont_net_weight
1020 FROM so_picking_line_details pld,
1021 so_picking_lines_all pl,
1022 mtl_system_items msi
1023 WHERE pl.picking_line_id = pld.picking_line_id
1024 AND msi.inventory_item_id = pl.inventory_item_id
1025 AND msi.organization_id = P_organization_id
1026 AND pld.container_id = P_container_id;
1027
1028 CURSOR get_container_id(
1029 P_container_id NUMBER)
1030 IS
1031 SELECT container_id
1032 FROM wsh_packed_containers
1033 START WITH container_id = P_container_id
1034 CONNECT BY PRIOR container_id = parent_container_id;
1035
1036 L_pack_flag NUMBER;
1037 L_tmp_container_id NUMBER;
1038 L_net_weight NUMBER := 0;
1039 BEGIN
1040 IF X_pack_mode = 'ENTERED' THEN
1041 L_pack_flag := 0;
1042 ELSE
1043 L_pack_flag := 1;
1044 END IF;
1045
1046 X_net_weight := 0;
1047
1048 OPEN get_container_id(X_container_id);
1049 LOOP
1050 FETCH get_container_id INTO L_tmp_container_id;
1051 EXIT WHEN get_container_id%NOTFOUND;
1052
1053 OPEN cont_net_weight(L_tmp_container_id, X_organization_id, X_master_uom, L_pack_flag);
1054 LOOP
1055 FETCH cont_net_weight INTO L_net_weight;
1056 EXIT WHEN cont_net_weight%NOTFOUND;
1057 X_net_weight := X_net_weight + L_net_weight;
1058 END LOOP;
1059 CLOSE cont_net_weight;
1060 END LOOP;
1061 CLOSE get_container_id;
1062
1063 END containers_net_weight;
1064
1065
1066 -- Name containers_tare_weight
1067 -- Purpose Calculates the weight of this container
1068 -- and the weight of the containers inside
1069 -- of this container.
1070 --
1071 -- Arguments
1072 -- container_id
1073 -- master_uom
1074 -- tare_weight
1075 PROCEDURE containers_tare_weight(
1076 X_container_id IN NUMBER,
1077 X_master_uom IN VARCHAR2,
1078 X_tare_weight IN OUT NUMBER,
1079 x_org_id IN NUMBER)
1080 IS
1081 CURSOR cont_tare_weight(
1082 P_container_id NUMBER,
1083 P_to_uom VARCHAR2,
1084 p_org_id NUMBER)
1085 IS
1086 SELECT NVL(wsh_wv_pvt.convert_uom(msi.weight_uom_code,
1087 P_to_uom,
1088 NVL(msi.unit_weight, 0)) * pc.quantity,
1089 0) cont_tare_weight
1090 FROM wsh_packed_containers pc,
1091 mtl_system_items msi
1092 WHERE msi.inventory_item_id = pc.container_inventory_item_id
1093 AND container_id = P_container_id
1094 AND msi.organization_id = p_org_id;
1095
1096 CURSOR get_container_id(
1097 P_container_id NUMBER)
1098 IS
1099 SELECT container_id
1100 FROM wsh_packed_containers pc
1101 START WITH container_id = P_container_id
1102 CONNECT BY PRIOR container_id = parent_container_id;
1103
1104 L_tmp_container_id NUMBER;
1105 L_tare_weight NUMBER := 0;
1106 BEGIN
1107 X_tare_weight := 0;
1108 OPEN get_container_id(X_container_id);
1109 LOOP
1110 FETCH get_container_id INTO L_tmp_container_id;
1111 EXIT WHEN get_container_id%NOTFOUND;
1112
1113 OPEN cont_tare_weight(L_tmp_container_id, x_master_uom, x_org_id);
1114 FETCH cont_tare_weight INTO L_tare_weight;
1115
1116 X_tare_weight := X_tare_weight + L_tare_weight;
1117 CLOSE cont_tare_weight;
1118 END LOOP;
1119 CLOSE get_container_id;
1120
1121 END containers_tare_weight;
1122
1123 PROCEDURE containers_tare_weight_self(
1124 X_container_id IN NUMBER,
1125 X_org_id IN NUMBER,
1126 X_master_uom IN VARCHAR2,
1127 X_tare_weight IN OUT NUMBER)
1128 IS
1129 CURSOR cont_tare_weight(
1130 P_container_id NUMBER,
1131 P_org_id NUMBER,
1132 P_to_uom VARCHAR2)
1133 IS
1134 SELECT NVL(wsh_wv_pvt.convert_uom(msi.weight_uom_code,
1135 P_to_uom,
1136 NVL(msi.unit_weight, 0)) * pc.quantity,
1137 0) cont_tare_weight
1138 FROM wsh_packed_containers pc,
1139 mtl_system_items msi
1140 WHERE msi.inventory_item_id = pc.container_inventory_item_id
1141 AND container_id = P_container_id
1142 AND msi.organization_id = P_org_id;
1143
1144 L_tare_weight NUMBER := 0;
1145 BEGIN
1146 OPEN cont_tare_weight(X_container_id, X_org_id, X_master_uom);
1147 FETCH cont_tare_weight INTO X_tare_weight;
1148 CLOSE cont_tare_weight;
1149 END containers_tare_weight_self;
1150
1151 PROCEDURE del_containers_tare_weight(
1152 X_del_id IN NUMBER,
1153 x_org_id IN NUMBER,
1154 X_master_uom IN VARCHAR2,
1155 X_tare_weight IN OUT NUMBER)
1156 IS
1157 CURSOR cont_tare_weight(
1158 P_container_id NUMBER,
1159 P_org_id NUMBER,
1160 P_to_uom VARCHAR2)
1161 IS
1162 SELECT NVL(wsh_wv_pvt.convert_uom(msi.weight_uom_code,
1163 P_to_uom,
1164 NVL(msi.unit_weight, 0)) * pc.quantity,
1165 0) cont_tare_weight
1166 FROM wsh_packed_containers pc,
1167 mtl_system_items msi
1168 WHERE msi.inventory_item_id = pc.container_inventory_item_id
1169 AND container_id = P_container_id
1170 AND msi.organization_id = P_org_id;
1171
1172 CURSOR containers_lookup(
1173 p_del_id IN NUMBER)
1174 IS
1175 SELECT container_id
1176 FROM wsh_packed_containers pc
1177 WHERE pc.delivery_id = p_del_id;
1178
1179 L_tmp_container_id NUMBER;
1180 L_tare_weight NUMBER := 0;
1181
1182 BEGIN
1183 X_tare_weight := 0;
1184 OPEN containers_lookup(X_del_id);
1185 LOOP
1186 FETCH containers_lookup INTO L_tmp_container_id;
1187 EXIT WHEN containers_lookup%NOTFOUND;
1188
1189 OPEN cont_tare_weight(L_tmp_container_id, x_org_id, X_master_uom);
1190 FETCH cont_tare_weight INTO L_tare_weight;
1191
1192 X_tare_weight := X_tare_weight + L_tare_weight;
1193 CLOSE cont_tare_weight;
1194 END LOOP;
1195 CLOSE containers_lookup;
1196 END del_containers_tare_weight;
1197
1198
1199 PROCEDURE auto_calc_cont(x_del_id IN NUMBER,
1200 x_org_id IN NUMBER,
1201 x_pack_mode IN VARCHAR2,
1202 x_fill_base IN VARCHAR2)
1203 IS
1204 CURSOR containers_lookup(p_del_id IN NUMBER,
1205 p_org_id IN NUMBER)
1206 IS
1207 SELECT pc.container_id id,
1208 pc.weight_uom_code wt_uom,
1209 pc.volume_uom_code v_uom,
1210 pc.sequence_number seq_num,
1211 pc.gross_weight gross_wt,
1212 pc.net_weight net_wt,
1213 pc.volume v,
1214 pc.container_inventory_item_id cont_inv_id,
1215 pc.quantity quantity,
1216 msi.internal_volume max_v,
1217 msi.maximum_load_weight max_wt,
1218 msi.weight_uom_code msi_wt_uom,
1219 msi.volume_uom_code msi_v_uom,
1220 pc.fill_percent fill
1221 FROM wsh_packed_containers pc,
1222 mtl_system_items msi
1223 WHERE pc.delivery_id = p_del_id
1224 AND msi.inventory_item_id = pc.container_inventory_item_id
1225 AND msi.organization_id = p_org_id;
1226
1227 l_gross_wt NUMBER;
1228 l_net_wt NUMBER := -99;
1229 l_tare_wt NUMBER := -99;
1230 l_volume NUMBER := -99;
1231 l_fill_percent NUMBER;
1232 l_tare_wt_self NUMBER;
1233 l_tmp_wt NUMBER;
1234 l_tmp_max_wt NUMBER;
1235 l_tmp_max_v NUMBER;
1236
1237 BEGIN
1238 FOR container IN containers_lookup(x_del_id, x_org_id) LOOP
1239 IF container.gross_wt IS NULL THEN
1240 containers_net_weight(container.id,
1241 x_org_id,
1242 x_pack_mode,
1243 container.wt_uom,
1244 l_net_wt);
1245
1246 containers_tare_weight(container.id,
1247 container.wt_uom,
1248 l_tare_wt,
1249 x_org_id);
1250
1251 l_gross_wt := l_net_wt + l_tare_wt;
1252
1253 IF (l_gross_wt >= 0 AND l_net_wt >= 0 AND l_tare_wt >= 0) THEN
1254 UPDATE wsh_packed_containers
1255 SET gross_weight = l_gross_wt,
1256 net_weight = l_net_wt
1257 WHERE container_id = container.id
1258 AND delivery_id = x_del_id;
1259 END IF;
1260 END IF;
1261
1262 IF container.v IS NULL THEN
1263 container_net_volume(x_del_id,
1264 x_org_id,
1265 container.id,
1266 container.seq_num,
1267 container.v_uom,
1268 x_pack_mode,
1269 l_volume);
1270
1271 IF (l_volume >= 0) THEN
1272 UPDATE wsh_packed_containers
1273 SET volume = l_volume
1274 WHERE container_id = container.id
1275 AND delivery_id = x_del_id;
1276 END IF;
1277 END IF;
1278
1279 IF x_fill_base = 'Q' THEN
1280 container_fill_percent(x_del_id,
1281 x_org_id,
1282 container.id,
1283 container.cont_inv_id,
1284 container.seq_num,
1285 container.quantity,
1286 x_pack_mode,
1287 l_fill_percent);
1288 ELSIF x_fill_base = 'V' THEN
1289 IF (l_volume = -99) THEN
1290 l_volume := container.v;
1291 END IF;
1292 l_tmp_max_v := wsh_wv_pvt.convert_uom(container.msi_v_uom,
1293 container.v_uom,
1294 container.max_v);
1295 l_fill_percent := l_volume /
1296 (l_tmp_max_v * container.quantity);
1297 l_fill_percent := Round(l_fill_percent * 100, 2);
1298 ELSIF x_fill_base = 'W' THEN
1299 IF (l_net_wt = -99) THEN
1300 l_net_wt := container.net_wt;
1301 END IF;
1302
1303 IF (l_tare_wt = -99) THEN
1304 l_tare_wt := container.gross_wt - container.net_wt;
1305 END IF;
1306
1307 l_tmp_max_wt := wsh_wv_pvt.convert_uom(container.msi_wt_uom,
1308 container.wt_uom,
1309 container.max_wt);
1310 containers_tare_weight_self(container.id,
1311 x_org_id,
1312 container.wt_uom,
1313 l_tare_wt_self);
1314 l_tmp_wt := l_tare_wt - l_tare_wt_self + l_net_wt;
1315 l_fill_percent := l_tmp_wt /
1316 (l_tmp_max_wt * container.quantity);
1317 l_fill_percent := Round(l_fill_percent * 100, 2);
1318 END IF;
1319
1320 IF (l_fill_percent >= 0) THEN
1321 UPDATE wsh_packed_containers
1322 SET fill_percent = l_fill_percent
1323 WHERE container_id = container.id
1324 AND delivery_id = x_del_id;
1325 END IF;
1326
1327 l_volume := -99; -- reset test value
1328 l_tare_wt := -99;
1329 l_net_wt := -99;
1330 END LOOP;
1331 END auto_calc_cont;
1332
1333
1334 END WSH_WVX_PVT;