1 PACKAGE BODY WSH_WV_PVT as
2 /* $Header: WSHUTWVB.pls 115.10 99/07/16 08:24:16 porting ship $ */
3
4 -- Name departure_weight_volume
5 -- Purpose Validates parameters and invokes dep_weight_volume
6
7 -- Arguments
8 -- source 'DPW' or 'SC'
9 -- departure_id
10 -- organization_id
11 -- wv_flag weight/vol DPW/SC flag ('ALWAYS' or 'NEVER')
12 -- update_flag 'Y' or 'N'
13 -- menu_flag 'Y' or 'N' (indicates if invoked from
14 -- the menu by the user or not).
15 -- dpw_pack_flag 'Y' or 'N' to automatically pack containers
16 -- (valid only when source = 'DPW')
17 -- x_sc_wv_mode 'ALL' or 'ENTERED' shipped quantites to use
18 -- master_weight_uom
19 -- net_weight (input/output -- weight of all goods)
20 -- tare_weight (input/output -- weight of all containers)
21 -- master_volume_uom
22 -- volume (input/output)
23 -- status (input/output)
24 -- -1 = error; 0 = success; 1 = warning
25
26 -- Dependencies
27 -- dep_weight_volume (for functionality)
28 -- FND_MESSAGE package
29
30 PROCEDURE departure_weight_volume(
31 source IN VARCHAR2,
32 departure_id IN NUMBER,
33 organization_id IN NUMBER,
34 wv_flag IN VARCHAR2,
35 update_flag IN VARCHAR2,
36 menu_flag IN VARCHAR2,
37 dpw_pack_flag IN VARCHAR2,
38 x_sc_wv_mode IN VARCHAR2 DEFAULT 'ALL',
39 master_weight_uom IN VARCHAR2,
40 net_weight IN OUT NUMBER,
41 tare_weight IN OUT NUMBER,
42 master_volume_uom IN VARCHAR2,
43 volume IN OUT NUMBER,
44 status IN OUT NUMBER)
45 IS
46 BEGIN
47 status := 0;
48 IF master_weight_uom IS NULL
49 OR master_volume_uom IS NULL THEN
50 status := -1;
51 -- **Message: must specify weight and volume uoms.
52 FND_MESSAGE.Set_Name('OE', 'WSH_WV_MISSING_UOMS');
53 ELSIF source IS NULL
54 OR departure_id IS NULL
55 OR organization_id IS NULL
56 OR wv_flag IS NULL
57 OR update_flag IS NULL
58 OR menu_flag IS NULL THEN
59 status := -1;
60 -- **Message: incomplete parameters
61 FND_MESSAGE.Set_Name('OE', 'WSH_WV_INCOMPLETE_PARAMETERS');
62 ELSIF source NOT IN ('DPW', 'SC')
63 OR wv_flag NOT IN ('ALWAYS', 'NEVER')
64 OR update_flag NOT IN ('Y', 'N')
65 OR menu_flag NOT IN ('Y', 'N')
66 OR (dpw_pack_flag NOT IN ('Y', 'N') AND source = 'DPW')
67 OR (x_sc_wv_mode NOT IN ('ALL', 'ENTERED') AND source = 'SC') THEN
68 status := -1;
69 -- **Message: invalid parameter values
70 FND_MESSAGE.Set_Name('OE', 'WSH_WV_INVALID_VALUES');
71 ELSE
72 WSH_WV_PVT.dep_weight_volume(
73 source, departure_id, organization_id,
74 wv_flag, update_flag, menu_flag, dpw_pack_flag, x_sc_wv_mode,
75 master_weight_uom, net_weight, tare_weight,
76 master_volume_uom, volume,
77 status);
78 END IF;
79
80 EXCEPTION
81 WHEN OTHERS THEN
82 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
83 FND_MESSAGE.Set_Token('PACKAGE','wsh_wv_pvt(1)');
84 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
85 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
86 status := -1;
87 END departure_weight_volume;
88
89
90 -- Name dep_weight_volume
91 -- Purpose Computes departure net weight and volume
92 -- and, if update_flag is 'Y', updates the table WSH_DELIVERIES
93 -- Called by departure_weight_volume
94
95 -- Arguments
96 -- source 'DPW' or 'SC'
97 -- departure_id
98 -- organization_id
99 -- wv_flag weight/vol DPW/SC flag ('ALWAYS' or 'NEVER')
100 -- update_flag 'Y' or 'N'
101 -- menu_flag 'Y' or 'N' (indicates if invoked from
102 -- the menu by the user or not).
103 -- dpw_pack_flag 'Y' or 'N' to automatically pack containers
104 -- (valid only when source = 'DPW')
105 -- x_sc_wv_mode 'ALL' or 'ENTERED' shipped quantites to use
106 -- master_weight_uom
107 -- net_weight (input/output -- weight of all goods)
108 -- tare_weight (input/output -- weight of all containers)
109 -- master_volume_uom
110 -- volume (input/output)
111 -- status (input/output)
112 -- -1 = error; 0 = success; 1 = warning
113
114 -- Dependencies
115 -- del_volume, del_weight, convert_uom, dep_loose_weight_volume
116
117 PROCEDURE dep_weight_volume(
118 source IN VARCHAR2,
119 departure_id IN NUMBER,
120 organization_id IN NUMBER,
121 wv_flag IN VARCHAR2,
122 update_flag IN VARCHAR2,
123 menu_flag IN VARCHAR2,
124 dpw_pack_flag IN VARCHAR2,
125 x_sc_wv_mode IN VARCHAR2 DEFAULT 'ALL',
126 master_weight_uom IN VARCHAR2,
127 net_weight IN OUT NUMBER,
128 tare_weight IN OUT NUMBER,
129 master_volume_uom IN VARCHAR2,
130 volume IN OUT NUMBER,
131 status IN OUT NUMBER)
132 IS
133 CURSOR delivery_recs(x_dep_id NUMBER) IS
134 SELECT delivery_id,
135 gross_weight, weight_uom_code,
136 volume, volume_uom_code
137 FROM wsh_deliveries
138 WHERE actual_departure_id = x_dep_id
139 AND status_code <> 'CA';
140
141 -- bug 650601 fix: this cursor added to calculate tare weight
142 -- which will be used to adjust departure's net weight.
143 CURSOR del_tare_weight(x_del_id NUMBER, x_o_id NUMBER,
144 x_to_uom VARCHAR2) IS
145 SELECT NVL(SUM( wsh_wv_pvt.convert_uom(msi.weight_uom_code, x_to_uom,
146 NVL(msi.unit_weight, 0))
147 * pc.quantity),
148 0) tare_weight
149 FROM wsh_packed_containers pc,
150 mtl_system_items msi
151 WHERE msi.inventory_item_id = pc.container_inventory_item_id
152 AND pc.delivery_id = x_del_id
153 AND msi.organization_id = x_o_id;
154
155 x_del_tare NUMBER;
156 x_gross_weight NUMBER;
157 x_volume NUMBER;
158 pack_status NUMBER;
159 overpack_warned BOOLEAN;
160
161 BEGIN
162 IF menu_flag = 'N' AND wv_flag = 'NEVER' THEN
163 -- In this case, there is no work to do.
164 RETURN;
165 END IF;
166
167 net_weight := 0;
168 tare_weight := 0;
169
170 volume := 0;
171
172 overpack_warned := FALSE; -- not yet issued a warning about overpacking.
173
174 FOR d in delivery_recs(departure_id) LOOP
175 x_volume := 0;
176 x_gross_weight := 0;
177
178 IF source = 'DPW' AND dpw_pack_flag = 'Y' THEN
179 WSH_WV_PVT.del_autopack(d.delivery_id, organization_id, pack_status);
180
181 IF pack_status = 1 AND NOT overpack_warned THEN
182 overpack_warned := TRUE;
183 -- Set status to warning only if it was success
184 IF status = 0 then
185 status := 1;
186 END IF;
187 FND_MESSAGE.Set_Name('OE', 'WSH_WV_AUTOPACK_BELOW_MIN_FILL');
188 END IF;
189 END IF;
190
191 -- bug 650601 fix
192 -- calculate delivery's tare weight
193
194 OPEN del_tare_weight(d.delivery_id, organization_id,
195 master_weight_uom);
196 FETCH del_tare_weight INTO x_del_tare;
197 IF del_tare_weight%NOTFOUND OR x_del_tare IS NULL THEN
198 x_del_tare := 0;
199 END IF;
200 CLOSE del_tare_weight;
201
202 -- bug 650601 fix
203 -- update the departure's tare weight.
204
205 tare_weight := tare_weight + x_del_tare;
206
207
208 IF menu_flag = 'Y' THEN
209 -- Always recalculate
210
211 WSH_WV_PVT.del_weight(source, d.delivery_id, organization_id,
212 menu_flag, x_sc_wv_mode,
213 master_weight_uom, x_gross_weight,
214 status);
215 net_weight := net_weight + x_gross_weight;
216
217 WSH_WV_PVT.del_volume(source, d.delivery_id, organization_id,
218 x_sc_wv_mode,
219 master_volume_uom, x_volume,
220 status);
221 volume := volume + x_volume;
222
223 ELSIF wv_flag = 'ALWAYS' THEN
224
225 IF d.gross_weight IS NULL THEN
226 WSH_WV_PVT.del_weight(source, d.delivery_id, organization_id,
227 menu_flag, x_sc_wv_mode,
228 master_weight_uom, x_gross_weight,
229 status);
230 net_weight := net_weight + x_gross_weight;
231 ELSE
232 net_weight := net_weight + WSH_WV_PVT.convert_uom(d.weight_uom_code,
233 master_weight_uom,
234 d.gross_weight);
235 END IF;
236
237 IF d.volume IS NULL THEN
238 WSH_WV_PVT.del_volume(source, d.delivery_id, organization_id,
239 x_sc_wv_mode,
240 master_volume_uom, x_volume,
241 status);
242 volume := volume + x_volume;
243 ELSE
244 volume := volume + WSH_WV_PVT.convert_uom(d.volume_uom_code,
245 master_volume_uom,
246 d.volume);
247 END IF;
248 END IF;
249
250 IF update_flag = 'Y'
251 AND (x_gross_weight > 0 OR x_volume > 0) THEN
252 UPDATE wsh_deliveries
253 SET gross_weight = decode(x_gross_weight, 0, gross_weight,
254 x_gross_weight),
255 weight_uom_code = decode(x_gross_weight, 0, weight_uom_code,
256 master_weight_uom),
257 volume = decode(x_volume, 0, x_volume,
258 x_volume),
259 volume_uom_code = decode(x_volume, 0, volume_uom_code,
260 master_volume_uom)
261 WHERE delivery_id = d.delivery_id;
262 END IF;
263
264 END LOOP;
265
266 -- now add weight and volume of items loose in this departure
267
268 WSH_WV_PVT.dep_loose_weight_volume(
269 source,
270 departure_id,
271 organization_id,
272 x_sc_wv_mode,
273 master_weight_uom,
274 x_gross_weight,
275 master_volume_uom,
276 x_volume,
277 status);
278
279 net_weight := net_weight + x_gross_weight;
280 volume := volume + x_volume;
281
282 -- bug 650601 fix
283 -- adjust departure's net weight to exclude the tare weight
284
285 net_weight := net_weight - tare_weight;
286
287 EXCEPTION
288 WHEN OTHERS THEN
289 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
290 FND_MESSAGE.Set_Token('PACKAGE','wsh_wv_pvt(2)');
291 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
292 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
293 IF delivery_recs%ISOPEN THEN
294 CLOSE delivery_recs;
295 END IF;
296 IF del_tare_weight%ISOPEN THEN
297 CLOSE del_tare_weight;
298 END IF;
299 status := -1;
300 END dep_weight_volume;
301
302
303
304
305
306 -- Name dep_loose_weight_volume
307 -- Purpose Computes weight and volume of items loose in this departure
308 -- (i.e., these items are not assigned to any delivery)
309 -- Called by dep_weight_volume
310
311 -- Arguments
312 -- source 'DPW' or 'SC'
313 -- departure_id
314 -- organization_id
315 -- master_weight_uom
316 -- weight (input/output)
317 -- x_sc_wv_mode 'ALL' or 'ENTERED' shipped quantites to use
318 -- master_volume_uom
319 -- volume (input/output)
320 -- status (input/output)
321 -- -1 = error; 0 = success; 1 = warning
322
323 PROCEDURE dep_loose_weight_volume(
324 source IN VARCHAR2,
325 departure_id IN NUMBER,
326 organization_id IN NUMBER,
327 x_sc_wv_mode IN VARCHAR2 DEFAULT 'ALL',
328 master_weight_uom IN VARCHAR2,
329 weight IN OUT NUMBER,
330 master_volume_uom IN VARCHAR2,
331 volume IN OUT NUMBER,
332 status IN OUT NUMBER)
333 IS
334
335 CURSOR dpw_loose_weight(x_dep_id NUMBER, x_o_id NUMBER) IS
336 SELECT SUM( NVL(msi.unit_weight, 0) *
337 WSH_WV_PVT.convert_uom(sld.unit_code,
338 msi.primary_uom_code,
339 sld.quantity,
340 sld.inventory_item_id) ) weight,
341 msi.weight_uom_code uom
342 FROM so_line_details sld,
343 mtl_system_items msi
344 WHERE sld.departure_id = x_dep_id
345 AND sld.delivery_id IS NULL
346 AND msi.inventory_item_id = sld.inventory_item_id
347 AND msi.organization_id = x_o_id
348 GROUP BY msi.weight_uom_code;
349
350 CURSOR dpw_loose_volume(x_dep_id NUMBER, x_o_id NUMBER) IS
351 SELECT SUM( NVL(msi.unit_volume, 0) *
352 WSH_WV_PVT.convert_uom(sld.unit_code,
353 msi.primary_uom_code,
354 sld.quantity,
355 sld.inventory_item_id) ) volume,
356 msi.volume_uom_code uom
357 FROM so_line_details sld,
358 mtl_system_items msi
359 WHERE sld.departure_id = x_dep_id
360 AND sld.delivery_id IS NULL
361 AND msi.inventory_item_id = sld.inventory_item_id
362 AND msi.organization_id = x_o_id
363 GROUP BY msi.volume_uom_code;
364
365
366 CURSOR dpw_bo_loose_weight(x_dep_id NUMBER, x_o_id NUMBER) IS
367 SELECT SUM( NVL(msi.unit_weight, 0) *
368 WSH_WV_PVT.convert_uom(sl.unit_code,
369 msi.primary_uom_code,
370 sld.requested_quantity,
371 sl.inventory_item_id) ) weight,
372 msi.weight_uom_code uom
373 FROM so_picking_line_details sld,
374 so_picking_lines_all sl,
375 mtl_system_items msi
376 WHERE sld.departure_id = x_dep_id
377 AND sld.delivery_id IS NULL
378 AND sl.picking_line_id = sld.picking_line_id
379 AND sl.picking_header_id = 0 -- backordered
380 AND msi.inventory_item_id = sl.inventory_item_id
381 AND msi.organization_id = x_o_id
382 GROUP BY msi.weight_uom_code;
383
384 CURSOR dpw_bo_loose_volume(x_dep_id NUMBER, x_o_id NUMBER) IS
385 SELECT SUM( NVL(msi.unit_volume, 0) *
386 WSH_WV_PVT.convert_uom(sl.unit_code,
387 msi.primary_uom_code,
388 sld.requested_quantity,
389 sl.inventory_item_id) ) volume,
390 msi.volume_uom_code uom
391 FROM so_picking_line_details sld,
392 so_picking_lines_all sl,
393 mtl_system_items msi
394 WHERE sld.departure_id = x_dep_id
395 AND sld.delivery_id IS NULL
396 AND sl.picking_line_id = sld.picking_line_id
397 AND sl.picking_header_id = 0 -- backordered
398 AND msi.inventory_item_id = sl.inventory_item_id
399 AND msi.organization_id = x_o_id
400 GROUP BY msi.volume_uom_code;
401
402 CURSOR dpw_loose_ato(x_dep_id NUMBER) IS
403 SELECT l.line_id ato_line_id,
404 sum(ld.quantity) qty
405 FROM so_line_details ld,
406 so_lines_all l
407 WHERE ld.departure_id = x_dep_id
408 AND ld.delivery_id IS NULL
409 AND ld.included_item_flag = 'N'
410 AND l.line_id = ld.line_id
411 AND l.ato_flag = 'Y'
412 AND l.ato_line_id IS NULL
413 GROUP BY l.line_id;
414
415 CURSOR dpw_bo_loose_ato(x_dep_id NUMBER,
416 x_w_uom VARCHAR2, x_v_uom VARCHAR2) IS
417 SELECT l.line_id ato_line_id,
418 sum(NVL(pld.requested_quantity, 0)) qty,
419 --
420 -- for weight and/or volume
421 -- add the components only if the UOMs and values are same
422 -- for the model its the configuration item.
423 -- The WHERE clause only finds models where at least
424 -- one physical attribute is the same; there may be two.
425 --
426 decode(m_msi.weight_uom_code,
427 i_msi.weight_uom_code, decode(m_msi.unit_weight,
428 i_msi.unit_weight, x_w_uom,
429 NULL),
430 NULL) weight_uom,
431 decode(m_msi.volume_uom_code,
432 i_msi.volume_uom_code, decode(m_msi.unit_volume,
433 i_msi.unit_volume, x_v_uom,
434 NULL),
435 NULL) volume_uom
436 FROM so_picking_line_details pld,
437 so_picking_lines_all pl,
438 so_line_details ld,
439 so_lines_all l,
440 mtl_system_items i_msi, -- configuration item
441 mtl_system_items m_msi -- model
442 WHERE pld.departure_id = x_dep_id
443 AND pld.delivery_id IS NULL
444 AND pl.picking_line_id = pld.picking_line_id
445 AND pl.picking_header_id+0 = 0 -- backordered
446 AND ld.line_detail_id = pl.line_detail_id
447 AND ld.configuration_item_flag = 'Y'
448 AND l.line_id = ld.line_id
449 AND l.ato_flag = 'Y'
450 AND l.ato_line_id IS NULL
451 AND i_msi.inventory_item_id = pl.inventory_item_id
452 AND i_msi.organization_id = pl.warehouse_id
453 AND m_msi.inventory_item_id = l.inventory_item_id
454 AND m_msi.organization_id = pl.warehouse_id
455 AND (
456 -- we will need to calculate only one or both attributes:
457 -- same weight attributes
458 (NVL(m_msi.weight_uom_code, 'EMPTY')
459 = NVL(i_msi.weight_uom_code, 'EMPTY')
460 AND NVL(m_msi.unit_weight, 0) = NVL(i_msi.unit_weight, 0))
461 OR
462 -- same volume attributes
463 (NVL(m_msi.volume_uom_code, 'EMPTY')
464 = NVL (i_msi.volume_uom_code, 'EMPTY')
465 AND NVL(m_msi.unit_volume, 0) = NVL(i_msi.unit_volume, 0))
466
467 )
468 GROUP BY l.line_id,
469 m_msi.weight_uom_code, i_msi.weight_uom_code,
470 m_msi.unit_weight, i_msi.unit_weight,
471 m_msi.volume_uom_code, i_msi.volume_uom_code,
472 m_msi.unit_volume, i_msi.unit_volume;
473
474 CURSOR sc_loose_weight(x_dep_id NUMBER, x_o_id NUMBER, x_wv_mode NUMBER) IS
475 SELECT SUM( NVL(msi.unit_weight, 0) *
476 WSH_WV_PVT.convert_uom(sl.unit_code,
477 msi.primary_uom_code,
478 NVL(sld.shipped_quantity,
479 x_wv_mode*sld.requested_quantity),
480 sl.inventory_item_id) ) weight,
481 msi.weight_uom_code uom
482 FROM so_picking_line_details sld,
483 so_picking_lines_all sl,
484 mtl_system_items msi
485 WHERE sld.departure_id = x_dep_id
486 AND sld.delivery_id IS NULL
487 AND sl.picking_line_id = sld.picking_line_id
488 AND sl.picking_header_id+0 > 0 -- NOT backordered
489 AND msi.inventory_item_id = sl.inventory_item_id
490 AND msi.organization_id = x_o_id
491 GROUP BY msi.weight_uom_code;
492
493 CURSOR sc_loose_volume(x_dep_id NUMBER, x_o_id NUMBER, x_wv_mode NUMBER) IS
494 SELECT SUM( NVL(msi.unit_volume, 0) *
495 WSH_WV_PVT.convert_uom(sl.unit_code,
496 msi.primary_uom_code,
497 NVL(sld.shipped_quantity,
498 x_wv_mode*sld.requested_quantity),
499 sl.inventory_item_id) ) volume,
500 msi.volume_uom_code uom
501 FROM so_picking_line_details sld,
502 so_picking_lines_all sl,
503 mtl_system_items msi
504 WHERE sld.departure_id = x_dep_id
505 AND sld.delivery_id IS NULL
506 AND sl.picking_line_id = sld.picking_line_id
507 AND sl.picking_header_id+0 > 0 -- NOT backordered
508 AND msi.inventory_item_id = sl.inventory_item_id
509 AND msi.organization_id = x_o_id
510 GROUP BY msi.volume_uom_code;
511
512 CURSOR sc_loose_ato(x_dep_id NUMBER, x_w_uom VARCHAR2, x_v_uom VARCHAR2,
513 x_wv_mode NUMBER) IS
514 SELECT l.line_id ato_line_id,
515 sum(NVL(pld.shipped_quantity,
516 x_wv_mode * pld.requested_quantity)) qty,
517 --
518 -- for weight and/or volume
519 -- add the components only if the UOMs and values are same
520 -- for the model and the configuration item.
521 -- The WHERE clause only finds models where at least
522 -- one physical attribute is the same; there may be two.
523 --
524 decode(m_msi.weight_uom_code,
525 i_msi.weight_uom_code, decode(m_msi.unit_weight,
526 i_msi.unit_weight, x_w_uom,
527 NULL),
528 NULL) weight_uom,
529 decode(m_msi.volume_uom_code,
530 i_msi.volume_uom_code, decode(m_msi.unit_volume,
531 i_msi.unit_volume, x_v_uom,
532 NULL),
533 NULL) volume_uom
534 FROM so_picking_line_details pld,
535 so_picking_lines_all pl,
536 so_line_details ld,
537 so_lines_all l,
538 mtl_system_items i_msi, -- configuration item
539 mtl_system_items m_msi -- model
540 WHERE pld.departure_id = x_dep_id
541 AND pld.delivery_id IS NULL
542 AND pl.picking_line_id = pld.picking_line_id
543 AND pl.picking_header_id+0 > 0 -- NOT backordered
544 AND ld.line_detail_id = pl.line_detail_id
545 AND ld.configuration_item_flag = 'Y'
546 AND l.line_id = ld.line_id
547 AND l.ato_flag = 'Y'
548 AND l.ato_line_id IS NULL
549 AND i_msi.inventory_item_id = pl.inventory_item_id
550 AND i_msi.organization_id = pl.warehouse_id
551 AND m_msi.inventory_item_id = l.inventory_item_id
552 AND m_msi.organization_id = pl.warehouse_id
553 AND (
554 -- we will need to calculate only one or both attributes:
555 -- same weight attributes
556 (NVL(m_msi.weight_uom_code, 'EMPTY')
557 = NVL(i_msi.weight_uom_code, 'EMPTY')
558 AND NVL(m_msi.unit_weight, 0) = NVL(i_msi.unit_weight, 0))
559 OR
560 -- same volume attributes
561 (NVL(m_msi.volume_uom_code, 'EMPTY')
562 = NVL (i_msi.volume_uom_code, 'EMPTY')
563 AND NVL(m_msi.unit_volume, 0) = NVL(i_msi.unit_volume, 0))
564
565 )
566 GROUP BY l.line_id,
567 m_msi.weight_uom_code, i_msi.weight_uom_code,
568 m_msi.unit_weight, i_msi.unit_weight,
569 m_msi.volume_uom_code, i_msi.volume_uom_code,
570 m_msi.unit_volume, i_msi.unit_volume;
571
572 ato_weight NUMBER;
573 ato_volume NUMBER;
574 WV_MODE NUMBER := 1;
575
576 BEGIN
577
578 weight := 0;
579 volume := 0;
580
581 IF source = 'DPW' THEN
582
583 FOR w IN dpw_loose_weight(departure_id, organization_id) LOOP
584 weight := weight + WSH_WV_PVT.convert_uom(w.uom, master_weight_uom,
585 w.weight);
586 END LOOP;
587 FOR w IN dpw_bo_loose_weight(departure_id, organization_id) LOOP
588 weight := weight + WSH_WV_PVT.convert_uom(w.uom, master_weight_uom,
589 w.weight);
590 END LOOP;
591
592 FOR v IN dpw_loose_volume(departure_id, organization_id) LOOP
593 volume := volume + WSH_WV_PVT.convert_uom(v.uom, master_volume_uom,
594 v.volume);
595 END LOOP;
596 FOR v IN dpw_bo_loose_volume(departure_id, organization_id) LOOP
597 volume := volume + WSH_WV_PVT.convert_uom(v.uom, master_volume_uom,
598 v.volume);
599 END LOOP;
600
601 FOR a IN dpw_loose_ato(departure_id) LOOP
602 wsh_wvx_pvt.ato_weight_volume(source,
603 a.ato_line_id,
604 a.qty,
605 master_weight_uom,
606 ato_weight,
607 master_volume_uom,
608 ato_volume,
609 status);
610 weight := weight + ato_weight;
611 volume := volume + ato_volume;
612 END LOOP;
613
614 FOR a IN dpw_bo_loose_ato(departure_id,
615 master_weight_uom, master_volume_uom) LOOP
616 wsh_wvx_pvt.ato_weight_volume('BO',
617 a.ato_line_id,
618 a.qty,
619 a.weight_uom,
620 ato_weight,
621 a.volume_uom,
622 ato_volume,
623 status);
624 weight := weight + ato_weight;
625 volume := volume + ato_volume;
626 END LOOP;
627
628 ELSIF source = 'SC' THEN
629
630 IF UPPER(X_SC_WV_MODE) = 'ENTERED' THEN
631 WV_MODE := 0;
632 ELSE
633 WV_MODE := 1;
634 END IF;
635
636 FOR w IN sc_loose_weight(departure_id, organization_id, wv_mode) LOOP
637 weight := weight + WSH_WV_PVT.convert_uom(w.uom, master_weight_uom,
638 w.weight);
639 END LOOP;
640 FOR v IN sc_loose_volume(departure_id, organization_id, wv_mode) LOOP
641 volume := volume + WSH_WV_PVT.convert_uom(v.uom, master_volume_uom,
642 v.volume);
643 END LOOP;
644
645 FOR a IN sc_loose_ato(departure_id,
646 master_weight_uom, master_volume_uom, wv_mode) LOOP
647 wsh_wvx_pvt.ato_weight_volume(source,
648 a.ato_line_id,
649 a.qty,
650 a.weight_uom,
651 ato_weight,
652 a.volume_uom,
653 ato_volume,
654 status);
655 weight := weight + ato_weight;
656 volume := volume + ato_volume;
657 END LOOP;
658
659 END IF;
660
661 EXCEPTION
662 WHEN OTHERS THEN
663 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
664 FND_MESSAGE.Set_Token('PACKAGE','wsh_wv_pvt(3)');
665 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
666 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
667 status := -1;
668 END dep_loose_weight_volume;
669
670
671
672 -- Name dep_fill_percentage
673 -- Purpose Computes percentage of the vehicle (or container) filled.
674
675 -- Arguments
676 -- departure_id
677 -- organization_id
678 -- vehicle_id (or container)
679 -- vehicle_max_weight
680 -- weight
681 -- vehicle_max_volume
682 -- volume
683 -- vehicle_min_fill
684 -- actual_fill (input/output) in percentage
685 -- status (input/output)
686 -- -1 = error; 0 = success; 1 = warning
687
688 -- Dependencies
689 -- FND_MESSAGE package
690
691 PROCEDURE dep_fill_percentage(
692 departure_id IN NUMBER,
693 organization_id IN NUMBER,
694 vehicle_id IN NUMBER,
695 vehicle_max_weight IN NUMBER,
696 gross_weight IN NUMBER,
697 vehicle_max_volume IN NUMBER,
698 volume IN NUMBER,
699 vehicle_min_fill IN NUMBER,
700 actual_fill IN OUT NUMBER,
701 status IN OUT NUMBER)
702 IS
703 CURSOR container_fill(x_dep_id NUMBER, x_o_id NUMBER, x_veh_id NUMBER) IS
704 SELECT SUM(pc.quantity / cl.max_load_quantity) * 100
705 FROM wsh_container_load cl,
706 wsh_packed_containers pc,
707 wsh_deliveries d
708 WHERE d.actual_departure_id = x_dep_id
709 AND d.delivery_id = pc.delivery_id
710 AND pc.container_inventory_item_id = cl.load_item_id
711 AND cl.container_item_id = x_veh_id
712 AND cl.master_organization_id =
713 (SELECT master_organization_id
714 FROM mtl_parameters
715 WHERE organization_id = x_o_id)
716 AND NVL(cl.max_load_quantity, 0) > 0
717 AND (pc.parent_sequence_number IS NULL
718 OR not exists (select sequence_number from wsh_packed_containers
719 where delivery_id = pc.delivery_id
720 and sequence_number = pc.parent_sequence_number))
721 GROUP BY 1;
722
723 basis_flag VARCHAR2(1);
724 fill_percentage NUMBER := NULL;
725 fill_found BOOLEAN := FALSE;
726
727 BEGIN
728
729 status := 0;
730
731 WSH_PARAMETERS_PVT.get_param_value(organization_id,
732 'PERCENT_FILL_BASIS_FLAG',
733 basis_flag);
734 IF basis_flag not in ('Q', 'V', 'W') THEN
735 basis_flag := 'W'; -- default to weight as basis.
736 END IF;
737
738 IF basis_flag = 'Q' THEN -- use container quantity
739
740 OPEN container_fill(departure_id, organization_id, vehicle_id);
741
742 FETCH container_fill INTO fill_percentage;
743 fill_found := container_fill%FOUND;
744
745 CLOSE container_fill;
746
747 IF NOT fill_found THEN
748 fill_percentage := NULL;
749 status := 0;
750
751 --* Disabled the warning, per bug 590630
752 --* status := 1;
753 --* -- **Message: Outermost containers not defined (or invalid values).
754 --* FND_MESSAGE.Set_Name('OE', 'WSH_WV_UNDEFINED_CONTAINERS');
755 END IF;
756
757 ELSIF basis_flag = 'V' THEN -- use volume
758
759 IF vehicle_max_volume > 0 THEN
760 fill_percentage := 100 * volume / vehicle_max_volume;
761 END IF;
762
763 ELSIF basis_flag = 'W' THEN -- use weight
764
765 IF vehicle_max_weight > 0 THEN
766 fill_percentage := 100 * gross_weight / vehicle_max_weight;
767 END IF;
768
769 END IF;
770
771 -- overwrite the fill percentage field only if value has been calculated.
772 -- and validate the value.
773 IF fill_percentage IS NOT NULL THEN
774 actual_fill := round(fill_percentage);
775
776 if actual_fill > 100 then
777 status := 1;
778 -- **Message: fill percentage exceeds 100%.
779 FND_MESSAGE.Set_Name('OE', 'WSH_WV_FILL_OVER_100');
780 elsif actual_fill < vehicle_min_fill then
781 status := 1;
782 -- **Message: fill percentage does not meet
783 -- the minimum fill percentage required.
784 FND_MESSAGE.Set_Name('OE', 'WSH_WV_FILL_BELOW_MIN');
785 end if;
786
787 END IF;
788
789 if volume > vehicle_max_volume then
790 status := 1;
791 -- **Message: volume exceeds the vehicle's maximum volume.
792 FND_MESSAGE.Set_Name('OE', 'WSH_WV_VEH_OVER_MAX_VOL');
793 end if;
794
795 if gross_weight > vehicle_max_weight then
796 status := 1;
797 -- **Message: load weight exceeds the vehicle's maximum weight
798 FND_MESSAGE.Set_Name('OE', 'WSH_WV_VEH_OVER_MAX_WEIGHT');
799 end if;
800
801
802 EXCEPTION
803 WHEN OTHERS THEN
804 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
805 FND_MESSAGE.Set_Token('PACKAGE','wsh_wv_pvt(4)');
806 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
807 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
808 IF container_fill%ISOPEN THEN
809 CLOSE container_fill;
810 END IF;
811 actual_fill := NULL;
812 status := -1;
813 END dep_fill_percentage;
814
815
816
817
818 -- Name del_weight_volume
819 -- Purpose Computes one delivery's net weight and volume
820 -- and, if update_flag is 'Y', updates the table WSH_DELIVERIES
821
822 -- Arguments
823 -- source 'DPW' or 'SC'
824 -- delivery_id
825 -- organization_id
826 -- update_flag 'Y' or 'N' (update WSH_DELIVERIES?)
827 -- menu_flag 'Y' or 'N' (indicates if invoked from
828 -- the menu by the user or not).
829 -- dpw_pack_flag 'Y' or 'N' to automatically pack containers
830 -- (valid only when source = 'DPW')
831 -- master_weight_uom
832 -- net_weight (input/output)
833 -- master_volume_uom
834 -- volume (input/output)
835 -- status (input/output)
836 -- -1 = error; 0 = success; 1 = warning
837 -- x_sc_wv_mode 'ALL' or 'ENTERED' shipped quantites to use
838
839 PROCEDURE del_weight_volume(
840 source IN VARCHAR2,
841 del_id IN NUMBER,
842 organization_id IN NUMBER,
843 update_flag IN VARCHAR2,
844 menu_flag IN VARCHAR2,
845 dpw_pack_flag IN VARCHAR2,
846 x_sc_wv_mode IN VARCHAR2 DEFAULT 'ALL',
847 master_weight_uom IN VARCHAR2,
848 gross_weight IN OUT NUMBER,
849 master_volume_uom IN VARCHAR2,
850 volume IN OUT NUMBER,
851 status IN OUT NUMBER)
852 IS
853
854 pack_status NUMBER;
855 x_volume NUMBER := 0;
856 x_gross_weight NUMBER := 0;
857 x_del_id NUMBER := del_id;
858 x_master_weight_uom VARCHAR2(4) := master_weight_uom;
859 x_master_volume_uom VARCHAR2(4) := master_volume_uom;
860
861 BEGIN
862
863 x_del_id := del_id;
864 x_master_weight_uom := master_weight_uom;
865 x_master_volume_uom := master_volume_uom;
866
867 IF source = 'DPW' AND dpw_pack_flag = 'Y' THEN
868 WSH_WV_PVT.del_autopack(del_id, organization_id, pack_status);
869 IF pack_status = 1 THEN
870 IF status = 0 then -- don't want to change error into warning.
871 status := 1;
872 END IF;
873 FND_MESSAGE.Set_Name('OE', 'WSH_WV_AUTOPACK_BELOW_MIN_FILL');
874 END IF;
875 END IF;
876
877 WSH_WV_PVT.del_weight(source, del_id, organization_id,
878 menu_flag, x_sc_wv_mode,
879 master_weight_uom, x_gross_weight,
880 status);
881 gross_weight := x_gross_weight;
882
883 WSH_WV_PVT.del_volume(source, del_id, organization_id,
884 x_sc_wv_mode,
885 master_volume_uom, x_volume,
886 status);
887 volume := x_volume;
888
889 IF update_flag = 'Y' THEN
890 IF x_gross_weight > 0 AND x_volume > 0 THEN
891
892 UPDATE wsh_deliveries
893 SET gross_weight = x_gross_weight,
894 weight_uom_code = x_master_weight_uom,
895 volume = x_volume,
896 volume_uom_code = x_master_volume_uom
897 WHERE delivery_id = x_del_id;
898
899 ELSIF x_gross_weight > 0 THEN
900
901 UPDATE wsh_deliveries
902 SET gross_weight = x_gross_weight,
903 weight_uom_code = x_master_weight_uom
904 WHERE delivery_id = x_del_id;
905
906 ELSIF x_volume > 0 THEN
907
908 UPDATE wsh_deliveries
909 SET volume = x_volume,
910 volume_uom_code = x_master_volume_uom
911 WHERE delivery_id = x_del_id;
912
913 END IF;
914 END IF;
915
916 EXCEPTION
917 WHEN OTHERS THEN
918 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
919 FND_MESSAGE.Set_Token('PACKAGE','wsh_wv_pvt(5)');
920 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
921 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
922 status := -1;
923 END;
924
925
926
927 -- Name del_volume
928 -- Purpose Computes the delivery actual volume
929 -- Called by dep_volume_weight
930
931 -- Arguments
932 -- source 'DPW' or 'SC'
933 -- delivery_id
934 -- organization_id
935 -- x_sc_wv_mode 'ALL' or 'ENTERED' shipped quantites to use
936 -- master_uom
937 -- volume (input/output)
938 -- status (input/output)
939 -- -1 = error; 0 = success; 1 = warning
940
941 -- Dependencies
942 -- convert_uom
943
944 PROCEDURE del_volume(
945 source IN VARCHAR2,
946 delivery_id IN NUMBER,
947 organization_id IN NUMBER,
948 x_sc_wv_mode IN VARCHAR2 DEFAULT 'ALL',
949 master_uom IN VARCHAR2,
950 volume IN OUT NUMBER,
951 status IN OUT NUMBER)
952 IS
953 CURSOR delivery_volumes(x_del_id NUMBER, x_o_id NUMBER) IS
954 SELECT SUM(NVL(msi.unit_volume, 0) * pc.quantity) volume,
955 msi.volume_uom_code uom
956 FROM wsh_packed_containers pc,
957 mtl_system_items msi
958 WHERE msi.inventory_item_id = pc.container_inventory_item_id
959 AND pc.delivery_id = x_del_id
960 AND msi.organization_id = x_o_id
961 AND (pc.parent_sequence_number IS NULL
962 OR not exists (select sequence_number from wsh_packed_containers
963 where delivery_id = pc.delivery_id
964 and sequence_number = pc.parent_sequence_number))
965 GROUP BY msi.volume_uom_code;
966
967 CURSOR dpw_unpacked_volume(x_del_id NUMBER, x_o_id NUMBER) IS
968 SELECT SUM( NVL(msi.unit_volume, 0) *
969 WSH_WV_PVT.convert_uom(sld.unit_code,
970 msi.primary_uom_code,
971 sld.quantity,
972 sld.inventory_item_id) ) volume,
973 msi.volume_uom_code uom
974 FROM so_line_details sld,
975 mtl_system_items msi
976 WHERE sld.delivery_id = x_del_id
977 AND sld.master_container_item_id IS NULL -- no default containers may
978 AND sld.detail_container_item_id IS NULL -- mean that it is unpacked.
979 AND msi.inventory_item_id = sld.inventory_item_id
980 AND msi.organization_id = x_o_id
981 GROUP BY msi.volume_uom_code;
982
983 CURSOR dpw_unpacked_bo_volume(x_del_id NUMBER, x_o_id NUMBER) IS
984 SELECT SUM( NVL(msi.unit_volume, 0) *
985 WSH_WV_PVT.convert_uom(sl.unit_code,
986 msi.primary_uom_code,
987 sld.requested_quantity,
988 sl.inventory_item_id) ) volume,
989 msi.volume_uom_code uom
990 FROM so_picking_line_details sld,
991 so_picking_lines_all sl,
992 mtl_system_items msi
993 WHERE sld.delivery_id = x_del_id
994 AND sl.picking_line_id = sld.picking_line_id
995 AND sld.master_container_item_id IS NULL -- no default containers may
996 AND sld.detail_container_item_id IS NULL -- mean that it is unpacked.
997 AND sl.picking_header_id = 0 -- backordered
998 AND msi.inventory_item_id = sl.inventory_item_id
999 AND msi.organization_id = x_o_id
1000 GROUP BY msi.volume_uom_code;
1001
1002 CURSOR dpw_unpacked_ato(x_del_id NUMBER) IS
1003 SELECT l.line_id ato_line_id,
1004 sum(NVL(ld.quantity, 0)) qty
1005 FROM so_line_details ld,
1006 so_lines_all l
1007 WHERE ld.delivery_id = x_del_id
1008 AND ld.master_container_item_id IS NULL -- no default containers may
1009 AND ld.detail_container_item_id IS NULL -- mean that it is unpacked.
1010 AND ld.included_item_flag = 'N'
1011 AND l.line_id = ld.line_id
1012 AND l.ato_flag = 'Y'
1013 AND l.ato_line_id IS NULL
1014 GROUP BY l.line_id;
1015
1016 CURSOR dpw_unpacked_bo_ato(x_del_id NUMBER) IS
1017 SELECT l.line_id ato_line_id,
1018 sum(NVL(pld.requested_quantity, 0)) qty
1019 FROM so_picking_line_details pld,
1020 so_picking_lines_all pl,
1021 so_line_details ld,
1022 so_lines_all l,
1023 mtl_system_items i_msi, -- configuration item
1024 mtl_system_items m_msi -- model
1025 WHERE pld.delivery_id = x_del_id
1026 AND pld.container_id IS NULL
1027 AND pl.picking_line_id = pld.picking_line_id
1028 AND pl.picking_header_id+0 = 0 -- backordered
1029 AND ld.line_detail_id = pl.line_detail_id
1030 AND pld.master_container_item_id IS NULL -- no default containers may
1031 AND pld.detail_container_item_id IS NULL -- mean that it is unpacked.
1032 AND ld.configuration_item_flag = 'Y'
1033 AND l.line_id = ld.line_id
1034 AND l.ato_flag = 'Y'
1035 AND l.ato_line_id IS NULL
1036 AND i_msi.inventory_item_id = pl.inventory_item_id
1037 AND i_msi.organization_id = pl.warehouse_id
1038 AND m_msi.inventory_item_id = l.inventory_item_id
1039 AND m_msi.organization_id = pl.warehouse_id
1040 AND -- same volume attributes
1041 (NVL(m_msi.volume_uom_code, 'EMPTY')
1042 = NVL(i_msi.volume_uom_code, 'EMPTY')
1043 AND NVL(m_msi.unit_volume, 0) = NVL(i_msi.unit_volume, 0))
1044 GROUP BY l.line_id;
1045
1046
1047 CURSOR sc_unpacked_volume(x_del_id NUMBER, x_o_id NUMBER,
1048 x_wv_mode NUMBER) IS
1049 SELECT SUM( NVL(msi.unit_volume, 0) *
1050 WSH_WV_PVT.convert_uom(sl.unit_code,
1051 msi.primary_uom_code,
1052 NVL(sld.shipped_quantity,
1053 x_wv_mode*sld.requested_quantity),
1054 sl.inventory_item_id) ) volume,
1055 msi.volume_uom_code uom
1056 FROM so_picking_line_details sld,
1057 so_picking_lines_all sl,
1058 mtl_system_items msi
1059 WHERE sld.delivery_id = x_del_id
1060 AND sl.picking_line_id = sld.picking_line_id
1061 AND sld.container_id IS NULL -- definitely unpacked.
1062 AND sl.picking_header_id+0 > 0 -- NOT backordered
1063 AND msi.inventory_item_id = sl.inventory_item_id
1064 AND msi.organization_id = x_o_id
1065 GROUP BY msi.volume_uom_code;
1066
1067 CURSOR sc_unpacked_ato(x_del_id NUMBER, x_wv_mode NUMBER) IS
1068 SELECT l.line_id ato_line_id,
1069 sum(NVL(pld.shipped_quantity,
1070 x_wv_mode * pld.requested_quantity)) qty
1071 FROM so_picking_line_details pld,
1072 so_picking_lines_all pl,
1073 so_line_details ld,
1074 so_lines_all l,
1075 mtl_system_items i_msi, -- configuration item
1076 mtl_system_items m_msi -- model
1077 WHERE pld.delivery_id = x_del_id
1078 AND pld.container_id IS NULL
1079 AND pl.picking_line_id = pld.picking_line_id
1080 AND pl.picking_header_id+0 > 0 -- NOT backordered
1081 AND ld.line_detail_id = pl.line_detail_id
1082 AND ld.configuration_item_flag = 'Y'
1083 AND l.line_id = ld.line_id
1084 AND l.ato_flag = 'Y'
1085 AND l.ato_line_id IS NULL
1086 AND i_msi.inventory_item_id = pl.inventory_item_id
1087 AND i_msi.organization_id = pl.warehouse_id
1088 AND m_msi.inventory_item_id = l.inventory_item_id
1089 AND m_msi.organization_id = pl.warehouse_id
1090 AND -- same volume attributes
1091 (NVL(m_msi.volume_uom_code, 'EMPTY')
1092 = NVL(i_msi.volume_uom_code, 'EMPTY')
1093 AND NVL(m_msi.unit_volume, 0) = NVL(i_msi.unit_volume, 0))
1094 GROUP BY l.line_id;
1095
1096 ato_volume NUMBER;
1097 ato_weight NUMBER;
1098 WV_MODE NUMBER := 1;
1099
1100 BEGIN
1101 volume := 0;
1102 FOR dv IN delivery_volumes(delivery_id, organization_id) LOOP
1103 volume := volume + WSH_WV_PVT.convert_uom(dv.uom, master_uom, dv.volume);
1104 END LOOP;
1105
1106 -- include volume of unpacked items in this delivery.
1107
1108 IF source = 'DPW' THEN
1109
1110 FOR v IN dpw_unpacked_volume(delivery_id, organization_id) LOOP
1111 volume := volume + WSH_WV_PVT.convert_uom(v.uom, master_uom, v.volume);
1112 END LOOP;
1113
1114 FOR v IN dpw_unpacked_bo_volume(delivery_id, organization_id) LOOP
1115 volume := volume + WSH_WV_PVT.convert_uom(v.uom, master_uom, v.volume);
1116 END LOOP;
1117
1118 FOR a IN dpw_unpacked_ato(delivery_id) LOOP
1119 wsh_wvx_pvt.ato_weight_volume(source,
1120 a.ato_line_id,
1121 a.qty,
1122 NULL,
1123 ato_weight,
1124 master_uom,
1125 ato_volume,
1126 status);
1127 volume := volume + ato_volume;
1128 END LOOP;
1129
1130 FOR a IN dpw_unpacked_bo_ato(delivery_id) LOOP
1131 wsh_wvx_pvt.ato_weight_volume('BO',
1132 a.ato_line_id,
1133 a.qty,
1134 NULL,
1135 ato_weight,
1136 master_uom,
1137 ato_volume,
1138 status);
1139 volume := volume + ato_volume;
1140 END LOOP;
1141
1142 ELSIF source = 'SC' THEN
1143
1144 IF UPPER(X_SC_WV_MODE) = 'ENTERED' THEN
1145 WV_MODE := 0;
1146 ELSE
1147 WV_MODE := 1;
1148 END IF;
1149
1150 FOR v IN sc_unpacked_volume(delivery_id, organization_id, wv_mode) LOOP
1151 volume := volume + WSH_WV_PVT.convert_uom(v.uom, master_uom, v.volume);
1152 END LOOP;
1153
1154 FOR a IN sc_unpacked_ato(delivery_id, wv_mode) LOOP
1155 wsh_wvx_pvt.ato_weight_volume(source,
1156 a.ato_line_id,
1157 a.qty,
1158 NULL,
1159 ato_weight,
1160 master_uom,
1161 ato_volume,
1162 status);
1163 volume := volume + ato_volume;
1164 END LOOP;
1165
1166 END IF;
1167
1168 EXCEPTION
1169 WHEN OTHERS THEN
1170 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
1171 FND_MESSAGE.Set_Token('PACKAGE','wsh_wv_pvt(6)');
1172 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
1173 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
1174 status := -1;
1175 END del_volume;
1176
1177
1178 -- Name del_weight
1179 -- Purpose Computes the delivery actual weight
1180 -- Called by dep_volume_weight
1181
1182 -- Arguments
1183 -- source 'DPW' or 'SC'
1184 -- delivery_id
1185 -- organization_id
1186 -- menu_flag 'Y' or 'N' (indicates if invoked from
1187 -- the menu by the user or not).
1188 -- x_sc_wv_mode 'ALL' or 'ENTERED' shipped quantites to use
1189 -- master_uom
1190 -- gross_weight (input/output)
1191 -- status (input/output)
1192 -- -1 = error; 0 = success; 1 = warning
1193
1194 -- Dependencies
1195 -- containers_weight, unpacked_items_weight, convert_uom
1196 -- FND_MESSAGE package
1197
1198 PROCEDURE del_weight(
1199 source IN VARCHAR2,
1200 delivery_id IN NUMBER,
1201 organization_id IN NUMBER,
1202 menu_flag IN VARCHAR2,
1203 x_sc_wv_mode IN VARCHAR2 DEFAULT 'ALL',
1204 master_uom IN VARCHAR2,
1205 gross_weight IN OUT NUMBER,
1206 status IN OUT NUMBER)
1207 IS
1208 CURSOR delivery_weights(x_del_id NUMBER, x_o_id NUMBER) IS
1209 SELECT SUM(NVL(msi.unit_weight, 0) * pc.quantity) weight,
1210 msi.weight_uom_code uom
1211 FROM wsh_packed_containers pc,
1212 mtl_system_items msi
1213 WHERE msi.inventory_item_id = pc.container_inventory_item_id
1214 AND pc.delivery_id = x_del_id
1215 AND msi.organization_id = x_o_id
1216 GROUP BY msi.weight_uom_code;
1217
1218 -- cursors are for DPW case.
1219
1220 CURSOR net_weight(x_del_id NUMBER, x_o_id NUMBER) IS
1221 SELECT SUM( NVL(msi.unit_weight, 0) *
1222 WSH_WV_PVT.convert_uom(sld.unit_code,
1223 msi.primary_uom_code,
1224 sld.quantity,
1225 sld.inventory_item_id) ) weight,
1226 msi.weight_uom_code uom
1227 FROM so_line_details sld,
1228 mtl_system_items msi
1229 WHERE sld.delivery_id = x_del_id
1230 AND msi.inventory_item_id = sld.inventory_item_id
1231 AND msi.organization_id = x_o_id
1232 GROUP BY msi.weight_uom_code;
1233
1234 CURSOR net_bo_weight(x_del_id NUMBER, x_o_id NUMBER) IS
1235 SELECT SUM( NVL(msi.unit_weight, 0) *
1236 WSH_WV_PVT.convert_uom(sl.unit_code,
1237 msi.primary_uom_code,
1238 sld.requested_quantity,
1239 sl.inventory_item_id) ) weight,
1240 msi.weight_uom_code uom
1241 FROM so_picking_line_details sld,
1242 so_picking_lines_all sl,
1243 mtl_system_items msi
1244 WHERE sld.delivery_id = x_del_id
1245 AND sl.picking_line_id = sld.picking_line_id
1246 AND sl.picking_header_id = 0 -- backordered
1247 AND msi.inventory_item_id = sl.inventory_item_id
1248 AND msi.organization_id = x_o_id
1249 GROUP BY msi.weight_uom_code;
1250
1251 CURSOR ato(x_del_id NUMBER) IS
1252 SELECT l.line_id ato_line_id,
1253 sum(ld.quantity) qty
1254 FROM so_line_details ld,
1255 so_lines_all l
1256 WHERE ld.delivery_id = x_del_id
1257 AND ld.included_item_flag = 'N'
1258 AND l.line_id = ld.line_id
1259 AND l.ato_flag = 'Y'
1260 AND l.ato_line_id IS NULL
1261 GROUP BY l.line_id;
1262
1263 CURSOR bo_ato(x_del_id NUMBER) IS
1264 SELECT l.line_id ato_line_id,
1265 sum(NVL(pld.requested_quantity, 0)) qty
1266 FROM so_picking_line_details pld,
1267 so_picking_lines_all pl,
1268 so_line_details ld,
1269 so_lines_all l,
1270 mtl_system_items i_msi, -- configuration item
1271 mtl_system_items m_msi -- model
1272 WHERE pld.delivery_id = x_del_id
1273 AND pl.picking_line_id = pld.picking_line_id
1274 AND pl.picking_header_id+0 = 0 -- backordered
1275 AND ld.line_detail_id = pl.line_detail_id
1276 AND ld.configuration_item_flag = 'Y'
1277 AND l.line_id = ld.line_id
1278 AND l.ato_flag = 'Y'
1279 AND l.ato_line_id IS NULL
1280 AND i_msi.inventory_item_id = pl.inventory_item_id
1281 AND i_msi.organization_id = pl.warehouse_id
1282 AND m_msi.inventory_item_id = l.inventory_item_id
1283 AND m_msi.organization_id = pl.warehouse_id
1284 AND -- same weight attributes
1285 (NVL(m_msi.weight_uom_code, 'EMPTY')
1286 = NVL(i_msi.weight_uom_code, 'EMPTY')
1287 AND NVL(m_msi.unit_weight, 0) = NVL(i_msi.unit_weight, 0))
1288 GROUP BY l.line_id;
1289
1290 -- select other master containers whose parent_sequence_numbers
1291 -- are not NULL but not assigned to any other containers (or themselves).
1292 CURSOR sc_other_master_containers(x_del_id NUMBER) IS
1293 SELECT DISTINCT pc.parent_sequence_number
1294 FROM wsh_packed_containers pc
1295 WHERE pc.delivery_id = x_del_id
1296 AND pc.parent_sequence_number IS NOT NULL
1297 AND not exists (select sequence_number from wsh_packed_containers
1298 where delivery_id = pc.delivery_id
1299 and sequence_number = pc.parent_sequence_number);
1300
1301 weight_c NUMBER := 0;
1302 weight_ui NUMBER := 0;
1303 ato_weight NUMBER;
1304 ato_volume NUMBER;
1305
1306 BEGIN
1307 gross_weight := 0;
1308
1309 IF source = 'DPW' THEN
1310 -- Add the tare weights.
1311 FOR dw IN delivery_weights(delivery_id, organization_id) LOOP
1312 gross_weight := gross_weight
1313 + WSH_WV_PVT.convert_uom(dw.uom, master_uom, dw.weight);
1314 END LOOP;
1315
1316 -- Add the estimated net weight of delivery.
1317 FOR nw IN net_weight(delivery_id, organization_id) LOOP
1318 gross_weight := gross_weight
1319 + WSH_WV_PVT.convert_uom(nw.uom, master_uom, nw.weight);
1320 END LOOP;
1321 FOR nw IN net_bo_weight(delivery_id, organization_id) LOOP
1322 gross_weight := gross_weight
1323 + WSH_WV_PVT.convert_uom(nw.uom, master_uom, nw.weight);
1324 END LOOP;
1325
1326 FOR a IN ato(delivery_id) LOOP
1327 wsh_wvx_pvt.ato_weight_volume(source,
1328 a.ato_line_id,
1329 a.qty,
1330 master_uom,
1331 ato_weight,
1332 NULL,
1333 ato_volume,
1334 status);
1335 gross_weight := gross_weight + ato_weight;
1336 END LOOP;
1337
1338 FOR a IN bo_ato(delivery_id) LOOP
1339 wsh_wvx_pvt.ato_weight_volume('BO',
1340 a.ato_line_id,
1341 a.qty,
1342 master_uom,
1343 ato_weight,
1344 NULL,
1345 ato_volume,
1346 status);
1347 gross_weight := gross_weight + ato_weight;
1348 END LOOP;
1349
1350 ELSIF source = 'SC' THEN
1351 WSH_WV_PVT.containers_weight(delivery_id, organization_id,
1352 NULL,
1353 menu_flag, x_sc_wv_mode,
1354 master_uom, weight_c, status);
1355
1356 FOR c in sc_other_master_containers(delivery_id) LOOP
1357 -- weight_c will accumulate, as we weight the other containers
1358 WSH_WV_PVT.containers_weight(delivery_id, organization_id,
1359 c.parent_sequence_number,
1360 menu_flag, x_sc_wv_mode,
1361 master_uom, weight_c, status);
1362 END LOOP;
1363
1364 -- Add unpacked items,
1365 -- even if this addition may inflate the gross_weight value.
1366 WSH_WV_PVT.unpacked_items_weight(delivery_id, organization_id,
1367 x_sc_wv_mode,
1368 master_uom, weight_ui, status);
1369
1370 gross_weight := weight_c + weight_ui;
1371 ELSE
1372 status := -1;
1373 -- **Message: invalid source value
1374 FND_MESSAGE.Set_Name('OE', 'WSH_WV_INVALID_SOURCE_VALUE');
1375 END IF;
1376
1377 EXCEPTION
1378 WHEN OTHERS THEN
1379 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
1380 FND_MESSAGE.Set_Token('PACKAGE','wsh_wv_pvt(7)');
1381 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
1382 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
1383 IF delivery_weights%ISOPEN THEN
1384 CLOSE delivery_weights;
1385 END IF;
1386 IF sc_other_master_containers%ISOPEN THEN
1387 CLOSE sc_other_master_containers;
1388 END IF;
1389 status := -1;
1390 END del_weight;
1391
1392
1393 -- Name validate_packed_qty
1394 -- Purpose Validates that all items shipped are packed.
1395 -- Assumption This function will be called after all items are packed.
1396
1397 -- Arguments
1398 -- delivery_id
1399 -- pack_mode specifies what NULL shipped_quantity
1400 -- will be:
1401 -- 'ALL' -- non-zero, shipped
1402 -- 'ENTERED' -- zero, backordered
1403 -- status (input/output)
1404 -- -1 = error; 0 = success; 1 = warning
1405 -- RETURN BOOLEAN (TRUE = everything is packed
1406 -- FALSE = not all is packed; warning is also
1407 -- set in status)
1408
1409 -- Dependencies
1410 -- convert_uom, WSH_UTIL.item_flex_name
1411 -- FND_MESSAGE package
1412
1413 FUNCTION validate_packed_qty(
1414 delivery_id IN NUMBER,
1415 pack_mode IN VARCHAR2 DEFAULT 'ALL',
1416 status IN OUT NUMBER)
1417 RETURN BOOLEAN
1418 IS
1419 CURSOR unpacked_items(x_del_id NUMBER, x_ship_all_flag NUMBER) IS
1420 SELECT spl.inventory_item_id id,
1421 spl.warehouse_id o_id
1422 FROM so_picking_line_details spld,
1423 so_picking_lines_all spl
1424 WHERE spld.delivery_id = x_del_id
1425 AND spld.container_id IS NULL -- not packed.
1426 -- but this item will be shipped...
1427 AND NVL(spld.shipped_quantity, x_ship_all_flag) > 0
1428 AND spl.picking_line_id = spld.picking_line_id
1429 AND spl.picking_header_id+0 > 0
1430 GROUP BY spl.inventory_item_id, spl.warehouse_id
1431 ORDER BY spl.inventory_item_id;
1432
1433 result BOOLEAN := TRUE;
1434 ship_all NUMBER := 0;
1435 unpacked_list VARCHAR2(2000) := NULL;
1436
1437 BEGIN
1438 status := 0;
1439
1440 if pack_mode = 'ALL' then
1441 ship_all := 1;
1442 else
1443 ship_all := 0;
1444 end if;
1445
1446 FOR si IN unpacked_items(delivery_id, ship_all) LOOP
1447 result := FALSE;
1448 -- pass token to unpacked_list
1449 IF unpacked_list IS NULL THEN
1450 unpacked_list := WSH_CORE.item_flex_name(si.id, si.o_id);
1451 ELSE
1452 unpacked_list := unpacked_list || ', '
1453 || WSH_CORE.item_flex_name(si.id, si.o_id);
1454 END IF;
1455 END LOOP;
1456
1457 IF result = FALSE THEN
1458 status := 1; -- warning, not error.
1459 FND_MESSAGE.Set_Name('OE', 'WSH_WV_UNPACKED');
1460 FND_MESSAGE.Set_Token('UNPACKED_LIST', unpacked_list);
1461 END IF;
1462
1463 RETURN result;
1464
1465 EXCEPTION
1466 WHEN OTHERS THEN
1467 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
1468 FND_MESSAGE.Set_Token('PACKAGE','wsh_wv_pvt(8)');
1469 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
1470 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
1471 if unpacked_items%ISOPEN then
1472 close unpacked_items;
1473 end if;
1474 status := -1;
1475 RETURN result;
1476 END validate_packed_qty;
1477
1478
1479
1480 -- Name containers_load_check
1481 -- Purpose Checks whether any container is overloaded (based on
1482 -- container load relationships or weight or volume).
1483 -- Also checks whether the minimum fill percentages are met.
1484 --
1485 -- Note If function returns TRUE and status is 1, it means that
1486 -- some containers are underpacked, but none is overpacked.
1487 --
1488 -- Assumption This function will be called after all containers' weights
1489 -- are calculated or input by the user.
1490
1491 -- Arguments
1492 -- delivery_id
1493 -- organization_id
1494 -- pack_mode specifies what NULL shipped_quantity
1495 -- will be:
1496 -- 'ALL' -- non-zero, shipped
1497 -- 'ENTERED' -- zero, backordered
1498 -- status (input/output)
1499 -- -1 = error; 0 = success; 1 = warning
1500 -- RETURN BOOLEAN (TRUE = within maximum load
1501 -- OR with no information found
1502 -- FALSE = exceeds maximum load, check warning)
1503
1504 FUNCTION containers_load_check(
1505 delivery_id IN NUMBER,
1506 pack_mode IN VARCHAR2 DEFAULT 'ALL',
1507 status IN OUT NUMBER)
1508 RETURN BOOLEAN
1509 IS
1510 BEGIN
1511 RETURN wsh_wvx_pvt.x_containers_load_check(
1512 delivery_id,
1513 pack_mode,
1514 status);
1515 END containers_load_check;
1516
1517
1518 -- Name containers_weight_check
1519 -- Purpose Checks whether any container's weight exceeds max_load_weight.
1520 -- Assumption This function will be called after all weights are calculated
1521 -- or input by the user.
1522
1523 -- Arguments
1524 -- delivery_id
1525 -- organization_id
1526 -- status (input/output)
1527 -- -1 = error; 0 = success; 1 = warning
1528 -- RETURN BOOLEAN (TRUE = within maximum load weight
1529 -- OR with no information found
1530 -- FALSE = exceeds maximum load weight)
1531
1532 -- Dependencies
1533 -- convert_uom
1534 -- FND_MESSAGE package
1535
1536 FUNCTION containers_weight_check(
1537 delivery_id IN NUMBER,
1538 organization_id IN NUMBER,
1539 status IN OUT NUMBER)
1540 RETURN BOOLEAN
1541 IS
1542 CURSOR containers_info(x_del_id NUMBER, x_o_id NUMBER) IS
1543 SELECT pc.sequence_number seq_num,
1544 pc.sequence_number name,
1545 pc.quantity quantity,
1546 pc.gross_weight weight,
1547 pc.weight_uom_code w_uom_code,
1548 msi.maximum_load_weight max_weight,
1549 msi.weight_uom_code mw_uom_code
1550 FROM wsh_packed_containers pc,
1551 mtl_system_items msi
1552 WHERE pc.delivery_id = x_del_id
1553 AND msi.inventory_item_id = pc.container_inventory_item_id
1554 AND msi.organization_id = x_o_id
1555 AND NVL(msi.maximum_load_weight, 0) > 0
1556 AND ( ( pc.weight_uom_code = msi.weight_uom_code
1557 AND (pc.gross_weight / pc.quantity) > msi.maximum_load_weight)
1558 OR (pc.weight_uom_code <> msi.weight_uom_code));
1559 -- The last AND clause ensures that all cursor rows fetched will
1560 -- either have excessive weight or have different UOMs which we
1561 -- must explicitly convert and then check.
1562
1563 overweight BOOLEAN := FALSE;
1564 result BOOLEAN := TRUE;
1565
1566 container_seq_list VARCHAR2(2000) := NULL;
1567 BEGIN
1568 status := 0;
1569
1570 FOR ci IN containers_info(delivery_id, organization_id) LOOP
1571 overweight := FALSE;
1572
1573 IF ci.w_uom_code = ci.mw_uom_code THEN
1574 overweight := TRUE;
1575 ELSE
1576 IF WSH_WV_PVT.convert_uom(ci.w_uom_code, ci.mw_uom_code, ci.weight)
1577 > ci.max_weight THEN
1578 overweight := TRUE;
1579 END IF;
1580 END IF;
1581
1582 IF overweight THEN
1583 result := FALSE;
1584 -- Build a token to pass to the message string.
1585 IF container_seq_list IS NULL THEN
1586 container_seq_list := ci.name;
1587 ELSE
1588 container_seq_list := container_seq_list || ', ' || ci.name;
1589 END IF;
1590 END IF;
1591 END LOOP;
1592
1593 IF container_seq_list IS NOT NULL THEN
1594 status := 1;
1595 -- **Message: Max Load is exceeded for container(s) *Container_Seq_List
1596 FND_MESSAGE.Set_Name('OE', 'WSH_WV_MAX_LOAD_EXCEEDED');
1597 FND_MESSAGE.Set_Token('Container_Seq_List', container_seq_list);
1598 END IF;
1599
1600 RETURN result;
1601
1602 EXCEPTION
1603 WHEN OTHERS THEN
1604 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
1605 FND_MESSAGE.Set_Token('PACKAGE','wsh_wv_pvt(9)');
1606 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
1607 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
1608 IF containers_info%ISOPEN THEN
1609 CLOSE containers_info;
1610 END IF;
1611 status := -1;
1612 RETURN result;
1613 END containers_weight_check;
1614
1615
1616 -- Name containers_weight
1617 -- Purpose Calculates the weight of containers (recursively if needed)
1618
1619 -- Arguments
1620 -- delivery_id
1621 -- organization_id
1622 -- sequence_number
1623 -- menu_flag 'Y' or 'N' (indicates if invoked from
1624 -- the menu by the user or not).
1625 -- x_sc_wv_mode 'ALL' or 'ENTERED' shipped quantites to use
1626 -- master_uom
1627 -- weight (input/output)
1628 -- status (input/output)
1629 -- -1 = error; 0 = success; 1 = warning
1630
1631 -- Dependencies
1632 -- convert_uom
1633
1634 PROCEDURE containers_weight(
1635 delivery_id IN NUMBER,
1636 organization_id IN NUMBER,
1637 sequence_number IN NUMBER,
1638 menu_flag IN VARCHAR2,
1639 x_sc_wv_mode IN VARCHAR2 DEFAULT 'ALL',
1640 master_uom IN VARCHAR2,
1641 weight IN OUT NUMBER,
1642 status IN OUT NUMBER)
1643 IS
1644 CURSOR container_lookups(x_del_id NUMBER, x_o_id NUMBER, x_seq_num NUMBER) IS
1645 SELECT pc.container_id id,
1646 pc.sequence_number sequence_number,
1647 pc.gross_weight gross_weight,
1648 pc.weight_uom_code gross_uom_code,
1649 pc.quantity quantity,
1650 pc.container_inventory_item_id containter_inventory_item_id,
1651 pc.rowid rid,
1652 msi.unit_weight unit_weight,
1653 msi.weight_uom_code uom_code
1654 FROM wsh_packed_containers pc,
1655 mtl_system_items msi
1656 WHERE pc.delivery_id = x_del_id
1657 AND pc.container_inventory_item_id = msi.inventory_item_id
1658 AND msi.organization_id = x_o_id
1659 AND NVL(pc.parent_sequence_number, -1) = NVL(x_seq_num, -1);
1660
1661 CURSOR contents_lookups(x_del_id NUMBER, x_o_id NUMBER, x_cont_id NUMBER,
1662 x_wv_mode NUMBER) IS
1663 SELECT pl.inventory_item_id,
1664 SUM( NVL(msi.unit_weight, 0) *
1665 WSH_WV_PVT.convert_uom(pl.unit_code,
1666 msi.primary_uom_code,
1667 NVL(cc.shipped_quantity,
1668 x_wv_mode*cc.requested_quantity),
1669 pl.inventory_item_id) ) weight,
1670 msi.weight_uom_code uom_code
1671 FROM so_picking_line_details cc,
1672 so_picking_lines_all pl,
1673 mtl_system_items msi
1674 WHERE cc.container_id = x_cont_id
1675 AND cc.delivery_id = x_del_id
1676 AND pl.picking_line_id = cc.picking_line_id
1677 AND pl.picking_header_id+0 > 0
1678 AND pl.inventory_item_id = msi.inventory_item_id
1679 AND msi.organization_id = x_o_id
1680 GROUP BY pl.inventory_item_id, weight_uom_code;
1681
1682 CURSOR packed_ato(x_del_id NUMBER, x_cont_id NUMBER, x_wv_mode NUMBER) IS
1683 SELECT l.line_id ato_line_id,
1684 sum(NVL(pld.shipped_quantity,
1685 x_wv_mode * pld.requested_quantity)) qty
1686 FROM so_picking_line_details pld,
1687 so_picking_lines_all pl,
1688 so_line_details ld,
1689 so_lines_all l,
1690 mtl_system_items i_msi, -- configuration item
1691 mtl_system_items m_msi -- model
1692 WHERE pld.delivery_id = x_del_id
1693 AND pld.container_id = x_cont_id
1694 AND pl.picking_line_id = pld.picking_line_id
1695 AND pl.picking_header_id+0 > 0 -- NOT backordered
1696 AND ld.line_detail_id = pl.line_detail_id
1697 AND ld.configuration_item_flag = 'Y'
1698 AND l.line_id = ld.line_id
1699 AND l.ato_flag = 'Y'
1700 AND l.ato_line_id IS NULL
1701 AND i_msi.inventory_item_id = pl.inventory_item_id
1702 AND i_msi.organization_id = pl.warehouse_id
1703 AND m_msi.inventory_item_id = l.inventory_item_id
1704 AND m_msi.organization_id = pl.warehouse_id
1705 AND -- same weight attributes
1706 (NVL(m_msi.weight_uom_code, 'EMPTY')
1707 = NVL(i_msi.weight_uom_code, 'EMPTY')
1708 AND NVL(m_msi.unit_weight, 0) = NVL(i_msi.unit_weight, 0))
1709 GROUP BY l.line_id;
1710
1711 cont_weight NUMBER := 0;
1712 ato_weight NUMBER;
1713 ato_volume NUMBER;
1714 x_cont_weight NUMBER := 0;
1715 x_master_uom VARCHAR2(4);
1716 WV_MODE NUMBER := 1;
1717
1718 BEGIN
1719 IF weight IS NULL THEN
1720 weight := 0;
1721 END IF;
1722 IF status IS NULL THEN
1723 status := 0;
1724 END IF;
1725
1726 IF UPPER(X_SC_WV_MODE) = 'ENTERED' THEN
1727 WV_MODE := 0;
1728 ELSE
1729 WV_MODE := 1;
1730 END IF;
1731
1732 FOR container IN container_lookups(delivery_id, organization_id,
1733 sequence_number) LOOP
1734 cont_weight := 0;
1735
1736 IF container.gross_weight IS NULL OR menu_flag = 'Y' THEN
1737 -- Calculate its gross weight and update the table:
1738
1739 -- 1. Recursively weight the containers inside this container
1740 -- only if the container has a (non-NULL) sequence number.
1741
1742 IF container.sequence_number IS NOT NULL THEN
1743 WSH_WV_PVT.containers_weight(delivery_id, organization_id,
1744 container.sequence_number,
1745 menu_flag, x_sc_wv_mode,
1746 master_uom, cont_weight, status);
1747 END IF;
1748
1749 -- 2. Add the weights of items "loose" in this container, including ATO.
1750 FOR contents IN contents_lookups(delivery_id, organization_id,
1751 container.id, wv_mode) LOOP
1752 cont_weight := cont_weight
1753 + WSH_WV_PVT.convert_uom(contents.uom_code, master_uom,
1754 contents.weight);
1755 END LOOP;
1756
1757 FOR a IN packed_ato(delivery_id, container.id, wv_mode) LOOP
1758 wsh_wvx_pvt.ato_weight_volume('SC',
1759 a.ato_line_id,
1760 a.qty,
1761 master_uom,
1762 ato_weight,
1763 NULL,
1764 ato_volume,
1765 status);
1766 cont_weight := cont_weight + ato_weight;
1767 END LOOP;
1768
1769 -- 3. Include the container's tare weight, scaled by its quantity.
1770 -- (The contents are independent of the container's quantity,
1771 -- so their weight isn't scaled in step 2.)
1772 cont_weight := cont_weight
1773 + WSH_WV_PVT.convert_uom(container.uom_code, master_uom,
1774 container.unit_weight)
1775 * container.quantity;
1776
1777 -- 4. Now update the table.
1778 x_cont_weight := cont_weight;
1779 x_master_uom := master_uom;
1780 UPDATE wsh_packed_containers
1781 SET gross_weight = x_cont_weight,
1782 weight_uom_code = x_master_uom
1783 WHERE rowid = container.rid;
1784
1785 ELSE
1786 -- Use its aggregate gross weight which has been entered into the table.
1787 cont_weight := WSH_WV_PVT.convert_uom(container.gross_uom_code,
1788 master_uom,
1789 container.gross_weight);
1790 END IF;
1791
1792 weight := weight + cont_weight;
1793 END LOOP;
1794
1795 EXCEPTION
1796 WHEN OTHERS THEN
1797 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
1798 FND_MESSAGE.Set_Token('PACKAGE','wsh_wv_pvt(10)');
1799 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
1800 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
1801 IF container_lookups%ISOPEN THEN
1802 CLOSE container_lookups;
1803 END IF;
1804 IF contents_lookups%ISOPEN THEN
1805 CLOSE contents_lookups;
1806 END IF;
1807 status := -1;
1808 END containers_weight;
1809
1810
1811 -- Name unpacked_items_weight
1812 -- Purpose Calculates the weight of unpacked items for del_weight
1813
1814 -- Arguments
1815 -- delivery_id
1816 -- organization_id
1817 -- x_sc_wv_mode 'ALL' or 'ENTERED' shipped quantites to use
1818 -- master_uom
1819 -- weight (input/output)
1820 -- status (input/output)
1821 -- -1 = error; 0 = success; 1 = warning
1822
1823 -- Dependencies
1824 -- convert_uom
1825 -- FND_MESSAGE package
1826
1827 PROCEDURE unpacked_items_weight(
1828 delivery_id IN NUMBER,
1829 organization_id IN NUMBER,
1830 x_sc_wv_mode IN VARCHAR2 DEFAULT 'ALL',
1831 master_uom IN VARCHAR2,
1832 weight IN OUT NUMBER,
1833 status IN OUT NUMBER)
1834 IS
1835 CURSOR unpacked_items(x_del_id NUMBER, x_o_id NUMBER, x_wv_mode NUMBER) IS
1836 SELECT SUM(NVL(msi.unit_weight, 0) *
1837 WSH_WV_PVT.convert_uom(spl.unit_code,
1838 msi.primary_uom_code,
1839 NVL(spld.shipped_quantity,
1840 x_wv_mode*spld.requested_quantity),
1841 spl.inventory_item_id) ) weight,
1842 msi.weight_uom_code uom
1843 FROM so_picking_line_details spld,
1844 so_picking_lines_all spl,
1845 mtl_system_items msi
1846 WHERE spld.delivery_id = x_del_id
1847 AND spld.container_id IS NULL -- not packed in any container
1848 AND spld.picking_line_id = spl.picking_line_id
1849 AND spl.picking_header_id+0 > 0
1850 AND msi.inventory_item_id = spl.inventory_item_id
1851 AND msi.organization_id = x_o_id
1852 GROUP BY msi.weight_uom_code;
1853
1854 CURSOR unpacked_ato(x_del_id NUMBER, x_wv_mode NUMBER) IS
1855 SELECT l.line_id ato_line_id,
1856 sum(NVL(pld.shipped_quantity,
1857 x_wv_mode * pld.requested_quantity)) qty
1858 FROM so_picking_line_details pld,
1859 so_picking_lines_all pl,
1860 so_line_details ld,
1861 so_lines_all l,
1862 mtl_system_items i_msi, -- configuration item
1863 mtl_system_items m_msi -- model
1864 WHERE pld.delivery_id = x_del_id
1865 AND pld.container_id IS NULL -- not packed in any container
1866 AND pl.picking_line_id = pld.picking_line_id
1867 AND pl.picking_header_id+0 > 0
1868 AND ld.line_detail_id = pl.line_detail_id
1869 AND ld.configuration_item_flag = 'Y'
1870 AND l.line_id = ld.line_id
1871 AND l.ato_flag = 'Y'
1872 AND l.ato_line_id IS NULL
1873 AND i_msi.inventory_item_id = pl.inventory_item_id
1874 AND i_msi.organization_id = pl.warehouse_id
1875 AND m_msi.inventory_item_id = l.inventory_item_id
1876 AND m_msi.organization_id = pl.warehouse_id
1877 AND -- same weight attributes
1878 (NVL(m_msi.weight_uom_code, 'EMPTY')
1879 = NVL(i_msi.weight_uom_code, 'EMPTY')
1880 AND NVL(m_msi.unit_weight, 0) = NVL(i_msi.unit_weight, 0))
1881 GROUP BY l.line_id;
1882
1883 total_weight NUMBER := 0;
1884 ato_weight NUMBER;
1885 ato_volume NUMBER;
1886 WV_MODE NUMBER := 1;
1887
1888 BEGIN
1889 status := 0;
1890
1891 IF UPPER(X_SC_WV_MODE) = 'ENTERED' THEN
1892 WV_MODE := 0;
1893 ELSE
1894 WV_MODE := 1;
1895 END IF;
1896
1897 FOR ti IN unpacked_items(delivery_id, organization_id, wv_mode) LOOP
1898 total_weight := total_weight
1899 + WSH_WV_PVT.convert_uom(ti.uom, master_uom, ti.weight);
1900 END LOOP;
1901
1902 FOR a IN unpacked_ato(delivery_id, wv_mode) LOOP
1903 wsh_wvx_pvt.ato_weight_volume('SC',
1904 a.ato_line_id,
1905 a.qty,
1906 master_uom,
1907 ato_weight,
1908 NULL,
1909 ato_volume,
1910 status);
1911 total_weight := total_weight + ato_weight;
1912 END LOOP;
1913
1914 weight := total_weight;
1915
1916 EXCEPTION
1917 WHEN OTHERS THEN
1918 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
1919 FND_MESSAGE.Set_Token('PACKAGE','wsh_wv_pvt(11)');
1920 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
1921 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
1922 status := -1;
1923 END unpacked_items_weight;
1924
1925
1926
1927 -- Name del_autopack
1928 -- Purpose Computes and pack a number of master containers for each
1929 -- line detail assigned to this delivery.
1930
1931 -- Arguments
1932 -- delivery_id
1933 -- organization_id
1934 -- status (input/output)
1935 -- -1 = error; 0 = success; 1 = warning
1936 -- (warning means that some containers
1937 -- are underpacked for some item(s) because
1938 -- of their minimum fill percentages.)
1939
1940 PROCEDURE del_autopack(
1941 del_id IN NUMBER,
1942 organization_id IN NUMBER,
1943 status IN OUT NUMBER)
1944 IS
1945
1946
1947 -- Pack enough containers for all items.
1948 -- Warn the user if minimum_fill_percent is not satisfied.
1949
1950 -- Get all the delivery lines that have a master/detail container assigned:
1951 -- 1. Obtain the number of containers each line needs.
1952 -- 2. Sort them by their loading sequence number and by their containers
1953 --
1954 -- In the code:
1955 -- 1. Break the list into groups by the container,
1956 -- so that we consolidate the items into the same container
1957 -- to ensure efficient use of containers.
1958 -- 2. And each container packed will take the first load_seq_number,
1959 -- but the container may not span from non-NULL load_seq_number to NULL.
1960
1961 -- dl = delivery line
1962
1963 CURSOR autopack_list(x_del_id NUMBER, x_o_id NUMBER) IS
1964 SELECT dl.load_seq_number load_seq_number,
1965 wcl.container_item_id iid,
1966 WSH_WV_PVT.convert_uom(dl.unit_code,
1967 item_msi.primary_uom_code,
1968 dl.quantity,
1969 dl.inventory_item_id)
1970 / wcl.max_load_quantity raw_qty,
1971 0.01*NVL(cont_msi.minimum_fill_percent, 0) min_fill,
1972 cont_msi.weight_uom_code
1973 FROM so_line_details dl,
1974 mtl_system_items item_msi,
1975 wsh_container_load wcl,
1976 mtl_system_items cont_msi
1977 WHERE
1978 dl.delivery_id = x_del_id
1979 AND item_msi.inventory_item_id = dl.inventory_item_id
1980 AND item_msi.organization_id = x_o_id
1981 AND wcl.load_item_id = dl.inventory_item_id
1982 AND wcl.master_organization_id =
1983 (SELECT master_organization_id
1984 FROM mtl_parameters
1985 WHERE organization_id = x_o_id)
1986 AND wcl.container_item_id = NVL(dl.master_container_item_id,
1987 dl.detail_container_item_id)
1988 AND wcl.max_load_quantity > 0
1989 AND cont_msi.inventory_item_id = wcl.container_item_id
1990 AND cont_msi.organization_id = x_o_id
1991 UNION ALL
1992 -- copied from SELECT above and modified for backordered picking lines
1993 SELECT dl.load_seq_number load_seq_number,
1994 wcl.container_item_id iid,
1995 WSH_WV_PVT.convert_uom(spl.unit_code,
1996 item_msi.primary_uom_code,
1997 NVL(dl.shipped_quantity,
1998 dl.requested_quantity),
1999 spl.inventory_item_id)
2000 / wcl.max_load_quantity raw_qty,
2001 0.01*NVL(cont_msi.minimum_fill_percent, 0) min_fill,
2002 cont_msi.weight_uom_code
2003 FROM so_picking_line_details dl,
2004 so_picking_lines_all spl,
2005 mtl_system_items item_msi,
2006 wsh_container_load wcl,
2007 mtl_system_items cont_msi
2008 WHERE
2009 spl.picking_line_id = dl.picking_line_id
2010 AND spl.picking_header_id = 0 -- backordered
2011 AND dl.delivery_id = x_del_id
2012 AND item_msi.inventory_item_id = spl.inventory_item_id
2013 AND item_msi.organization_id = x_o_id
2014 AND wcl.load_item_id = item_msi.inventory_item_id
2015 AND wcl.master_organization_id =
2016 (SELECT master_organization_id
2017 FROM mtl_parameters
2018 WHERE organization_id = x_o_id)
2019 AND wcl.container_item_id = NVL(dl.master_container_item_id,
2020 dl.detail_container_item_id)
2021 AND wcl.max_load_quantity > 0
2022 AND cont_msi.inventory_item_id = wcl.container_item_id
2023 AND cont_msi.organization_id = x_o_id
2024 ORDER BY 1, 2;
2025
2026 load_seq_number SO_LINE_DETAILS.load_seq_number%TYPE := NULL;
2027 current_container_item_id WSH_CONTAINER_LOAD.container_item_id%TYPE := NULL;
2028 raw_qty NUMBER := 0;
2029 min_fill NUMBER := 0;
2030 weight_code MTL_SYSTEM_ITEMS.weight_uom_code%TYPE := '';
2031 x_del_id NUMBER := del_id;
2032
2033 BEGIN
2034
2035 status := 0;
2036
2037 SAVEPOINT before_autopack;
2038
2039 x_del_id := del_id;
2040 DELETE FROM wsh_packed_containers
2041 WHERE delivery_id = x_del_id;
2042
2043 FOR c IN autopack_list(del_id, organization_id) LOOP
2044 IF (c.iid = current_container_item_id)
2045 AND NOT (load_seq_number IS NOT NULL and c.load_seq_number IS NULL) THEN
2046
2047 -- As long as it's the same container,
2048 -- and it does not span from non-NULL load_seq_number to NULL.
2049 raw_qty := raw_qty + c.raw_qty;
2050
2051 ELSE
2052
2053 -- record the old container,
2054 -- and update variables with the new container's information.
2055 IF current_container_item_id IS NOT NULL THEN
2056
2057 /* Bug 770276 :Increment the sequence number by 10 */
2058 load_seq_number := nvl(load_seq_number,0) + 10;
2059
2060 WSH_WV_PVT.del_packcont(del_id,
2061 organization_id,
2062 current_container_item_id,
2063 raw_qty,
2064 min_fill,
2065 NULL,
2066 load_seq_number,
2067 weight_code,
2068 status);
2069 IF status = -1 THEN
2070 ROLLBACK TO before_autopack;
2071 RETURN;
2072 END IF;
2073 END IF;
2074
2075 /* Bug 770276 :Do not associate the container sequence number with load
2076 sequence number. Instead Increment the sequence number by 10 */
2077 -- load_seq_number := c.load_seq_number;
2078
2079 current_container_item_id := c.iid;
2080 raw_qty := c.raw_qty;
2081 min_fill := c.min_fill;
2082 weight_code := c.weight_uom_code;
2083
2084 END IF;
2085 END LOOP;
2086
2087 IF current_container_item_id IS NOT NULL THEN
2088 -- pack the last container. ("flush")
2089
2090 /* Bug 770276 :Increment the sequence number by 10 */
2091 load_seq_number := nvl(load_seq_number,0) + 10;
2092
2093 WSH_WV_PVT.del_packcont(del_id,
2094 organization_id,
2095 current_container_item_id,
2096 raw_qty,
2097 min_fill,
2098 NULL,
2099 load_seq_number,
2100 weight_code,
2101 status);
2102 IF status = -1 THEN
2103 ROLLBACK TO before_autopack;
2104 RETURN;
2105 END IF;
2106 END IF;
2107
2108 EXCEPTION
2109 WHEN OTHERS THEN
2110 ROLLBACK TO before_autopack;
2111 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
2112 FND_MESSAGE.Set_Token('PACKAGE','wsh_wv_pvt(12)');
2113 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
2114 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
2115 status := -1;
2116 END del_autopack;
2117
2118
2119
2120 -- Name del_packcont
2121 -- Purpose Packs the containers (called by del_autopack)
2122
2123 -- status (input/output)
2124 -- -1 = error; 0 = success; 1 = warning
2125 -- (warning means that some containers
2126 -- are underpacked for some item(s) because
2127 -- of their minimum fill percentages.)
2128
2129 PROCEDURE del_packcont(
2130 del_id IN NUMBER,
2131 organization_id IN NUMBER,
2132 cont_item_id IN NUMBER,
2133 raw_qty IN NUMBER,
2134 min_fill IN NUMBER, -- range 0.00-1.00 (not 0-100)
2135 parent_seq IN NUMBER,
2136 load_seq_number IN NUMBER,
2137 weight_uom_code IN VARCHAR2,
2138 status IN OUT NUMBER)
2139 IS
2140
2141 qty NUMBER;
2142 current_user NUMBER;
2143
2144 BEGIN
2145 IF min_fill = 0 THEN
2146 qty := CEIL(raw_qty);
2147 ELSE
2148 -- add the padding for minimum fill and then cut the fraction off.
2149 -- If the remaining items do not meet the minimum fill,
2150 -- warn the user.
2151 qty := FLOOR(raw_qty + (1 - min_fill));
2152
2153 IF qty < raw_qty THEN
2154 status := 1; -- warning: some containers are underpacked.
2155 END IF;
2156
2157 -- Then round the containers' quantity up to pack all items anyway.
2158 qty := CEIL(raw_qty);
2159 END IF;
2160
2161 current_user := to_number(FND_PROFILE.VALUE('USER_ID'));
2162 if current_user is null then
2163 current_user := 0;
2164 end if;
2165
2166 INSERT INTO wsh_packed_containers
2167 (container_id, delivery_id,
2168 container_inventory_item_id, quantity,
2169 parent_sequence_number, sequence_number, -- sequences
2170 weight_uom_code, -- weight
2171 organization_id,
2172 creation_date, created_by, -- creation
2173 last_update_date, last_updated_by) -- update
2174 VALUES
2175 (wsh_packed_containers_s.nextval, del_id,
2176 cont_item_id, qty,
2177 parent_seq, load_seq_number, -- sequences
2178 weight_uom_code, -- weight
2179 organization_id,
2180 sysdate, current_user, -- creation
2181 sysdate, current_user -- update
2182 );
2183
2184 EXCEPTION
2185 WHEN OTHERS THEN
2186 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
2187 FND_MESSAGE.Set_Token('PACKAGE','wsh_wv_pvt(13)');
2188 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
2189 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
2190 status := -1;
2191 END del_packcont;
2192
2193
2194
2195 -- Name order_net_weight_in_delivery
2196 -- Purpose Calculates the net weight of order's items in a delivery.
2197 -- SC only.
2198
2199 -- Arguments
2200 -- order_number (if NULL, delivery's net weight is computed)
2201 -- order_type_id (if NULL, ignore this type)
2202 -- delivery_id
2203 -- weight_uom
2204 -- RETURN number
2205
2206 FUNCTION order_net_weight_in_delivery(
2207 order_number IN NUMBER,
2208 order_type_id IN NUMBER,
2209 delivery_id IN NUMBER,
2210 weight_uom IN VARCHAR2)
2211 RETURN NUMBER IS
2212 BEGIN
2213 return wsh_wvx_pvt.x_order_net_wt_in_delivery(
2214 order_number => order_number,
2215 order_type_id => order_type_id,
2216 delivery_id => delivery_id,
2217 weight_uom => weight_uom);
2218 END order_net_weight_in_delivery;
2219
2220
2221 -- Name convert_uom
2222 -- Purpose Converts one UOM into another; unless item_id is
2223 -- specified, the UOMs must be in the same class.
2224
2225 -- Arguments
2226 -- from_uom
2227 -- to_uom
2228 -- quantity
2229 -- item_id (optional)
2230 -- RETURN number
2231
2232 -- Dependencies
2233 -- inv_convert.inv_um_convert (when item_id is not NULL)
2234
2235 FUNCTION convert_uom(from_uom IN VARCHAR2,
2236 to_uom IN VARCHAR2,
2237 quantity IN NUMBER,
2238 item_id IN NUMBER DEFAULT NULL)
2239 RETURN NUMBER
2240 IS
2241 this_item NUMBER;
2242 to_rate NUMBER;
2243 from_rate NUMBER;
2244 result NUMBER;
2245
2246 BEGIN
2247 IF from_uom = to_uom THEN
2248 result := quantity;
2249 ELSIF from_uom IS NULL
2250 OR to_uom IS NULL THEN
2251 result := 0;
2252 ELSE
2253 result := INV_CONVERT.inv_um_convert(item_id,
2254 6, -- precision digits
2255 quantity,
2256 from_uom,
2257 to_uom,
2258 NULL,
2259 NULL);
2260
2261 -- hard-coded value that means undefined conversion
2262 -- For example, conversion of FT2 to FT3 doesn't make sense...
2263 -- Reset the result to 0 to preserve compatibility before
2264 -- the bug fix made above (namely, always call inv_um_convert).
2265 if result = -99999 then
2266 result := 0;
2267 end if;
2268 END IF;
2269
2270 RETURN result;
2271 END convert_uom;
2272
2273
2274 PROCEDURE set_messages(message_string IN VARCHAR2,
2275 message_count IN OUT NUMBER,
2276 message_text1 IN OUT VARCHAR2,
2277 message_text2 IN OUT VARCHAR2,
2278 message_text3 IN OUT VARCHAR2,
2279 message_text4 IN OUT VARCHAR2)
2280 IS
2281 BEGIN
2282
2283 IF message_count = 0 THEN
2284 message_count := message_count + 1;
2285 message_text1 := message_string;
2286
2287 ELSIF message_count = 1 THEN
2288 message_count := message_count + 1;
2289 message_text2 := message_string;
2290
2291 ELSIF message_count = 2 THEN
2292 message_count := message_count + 1;
2293 message_text3 := message_string;
2294
2295 ELSIF message_count = 3 THEN
2296 message_count := message_count + 1;
2297 message_text4 := message_string;
2298 END IF;
2299
2300 END set_messages;
2301
2302
2303 END WSH_WV_PVT;
[Home] [Help]