4 --<TPA_PUBLIC_NAME=WSH_TPA_CONTAINER_PKG>
1 PACKAGE WSH_WV_UTILS AUTHID CURRENT_USER as
2 /* $Header: WSHWVUTS.pls 120.4.12020000.2 2013/03/14 16:04:17 suppal ship $ */
3
5 --<TPA_PUBLIC_FILE_NAME=WSHTPCO>
6
7 -- OTM R12 : packing ECO
8 G_RESET_WV VARCHAR2(1) := 'N';
9 -- End of OTM R12 : packing ECO
10
11 --
12 -- Procedure: Convert_Uom
13 -- Parameters: from_uom - Uom code to convert from
14 -- to_uom - Uom code to convert to
15 -- quantity - quantity to convert
16 -- item_id - inventory item id
17 -- Description: This procedure will convert quantity from one Uom to another by
18 -- calling an inventory convert uom procedure
19 --
20
21 -- HW OPMCONV - Added lot_number and org_id parameters
22 FUNCTION convert_uom(from_uom IN VARCHAR2,
23 to_uom IN VARCHAR2,
24 quantity IN NUMBER,
25 item_id IN NUMBER DEFAULT NULL,
26 p_max_decimal_digits IN NUMBER DEFAULT 5, -- RV DEC_QTY
27 lot_number VARCHAR2 DEFAULT NULL,
28 org_id IN NUMBER DEFAULT NULL) RETURN NUMBER;
29
30 FUNCTION convert_uom_core(from_uom IN VARCHAR2,
31 to_uom IN VARCHAR2,
32 quantity IN NUMBER,
33 item_id IN NUMBER DEFAULT NULL,
34 p_max_decimal_digits IN NUMBER DEFAULT 5, -- RV DEC_QTY
35 lot_number VARCHAR2 DEFAULT NULL,
36 org_id IN NUMBER DEFAULT NULL,
37 x_return_status OUT NOCOPY VARCHAR2) RETURN NUMBER;
38
39
40 -- The following pragma is used to allow convert_uom to be used in a select statement
41 -- WNDS : Write No Database State (does not allow tables to be altered)
42
43 pragma restrict_references (convert_uom, WNDS);
44
45
46 --
47 -- Procedure: Get_Default_Uoms
48 -- Parameters: p_organization_id - Organization where shipping parameters are defined
49 -- x_weight_uom_code - Default weight uom code
50 -- x_volume_uom_code - Default volume uom code
51 -- x_return_status - status of procedure call
52 -- Description: This procedure will find the default weight and volume uom codes from
53 -- wsh_shipping_parameters table, for a particular organization
54 --
55
56 PROCEDURE get_default_uoms ( p_organization_id IN NUMBER,
57 x_weight_uom_code OUT NOCOPY VARCHAR2,
58 x_volume_uom_code OUT NOCOPY VARCHAR2,
59 x_return_status OUT NOCOPY VARCHAR2);
60
61
62 --
63 -- Procedure: Detail_Weight_Volume
64 -- Parameters: p_delivery_detail_id - Delivery detail id
65 -- p_update_flag - if 'Y' then delivery weight/volume is updated
66 -- x_net_weight - calculated net weight
67 -- x_volume - calculated volume
68 -- x_return_status - status of procedure call
69 -- Description: This procedure will calculate the net weight and volume
70 -- of a delivery detail by finding the inventory unit weight/volume and
71 -- multiplying it with the converted shipped quantity (or requested quantity)
72 --
73
74
75 PROCEDURE Detail_Weight_Volume (
76 p_delivery_detail_id IN NUMBER,
77 p_update_flag IN VARCHAR2,
78 p_calc_wv_if_frozen IN VARCHAR2 DEFAULT 'Y',
79 x_net_weight OUT NOCOPY NUMBER,
80 x_volume OUT NOCOPY NUMBER,
81 x_return_status OUT NOCOPY VARCHAR2);
82
83
84 --
85 -- Procedure: Add_Container_Wt_Vol
86 -- Parameters: p_container_instance_id - Container id (delivery_detail_id of container)
87 -- p_detail_id - id of delivery_detail to be added to container
88 -- p_detail_type - 'L' - line, 'C' - cont
89 -- p_fill_pc_flag - if 'Y' then fill percent is also calculated.
90 -- x_net_weight - calculated net weight
91 -- x_gross_weight - calculated gross weight
92 -- x_volume - calculated volume
93 -- x_cont_fill_pc - calculated container fill percent
94 -- x_return_status - status of procedure call
95 -- Description: This procedure will add weight and volume of a delivery detail
96 -- (container or line) to the container it is being assigned to
97 -- Caution: the procedure will override existing weight/volume
98 -- on the container.
99 --
100
101 PROCEDURE Add_Container_Wt_Vol (
102 p_container_instance_id IN NUMBER,
103 p_detail_id IN NUMBER,
104 p_detail_type IN VARCHAR2,
105 p_fill_pc_flag IN VARCHAR2,
106 x_gross_weight OUT NOCOPY NUMBER,
107 x_net_weight OUT NOCOPY NUMBER,
108 x_volume OUT NOCOPY NUMBER,
109 x_cont_fill_pc OUT NOCOPY NUMBER,
110 x_return_status OUT NOCOPY VARCHAR2);
111
112 --
113 -- Procedure: Update_Container_Wt_Vol
114 -- Parameters: p_container_instance_id - Container instance Id for update
115 -- p_override_flag - if 'Y' then container weight/volume is updated
116 -- x_net_weight - calculated net weight
117 -- x_gross_weight - calculated gross weight
118 -- x_volume - calculated volume
122 -- Description: This procedure will update container weight/volume with calculated values
119 -- p_fill_pc_flag - if 'Y' then fill percent is also calculated.
120 -- x_cont_fill_pc - calculated container fill percent
121 -- x_return_status - status of procedure call
123 -- If override flag is 'Y' then all three fields will be updated, else only
124 -- the null fields will be updated.
125 --
126
127
128 PROCEDURE Update_Container_Wt_Vol (p_container_instance_id IN NUMBER,
129 p_gross_weight IN NUMBER,
130 p_net_weight IN NUMBER,
131 p_volume IN NUMBER,
132 p_filled_volume IN NUMBER,
133 p_fill_pc_flag IN VARCHAR2,
134 p_unit_weight IN NUMBER DEFAULT -99,
135 p_unit_volume IN NUMBER DEFAULT -99,
136 x_cont_fill_pc OUT NOCOPY NUMBER,
137 x_return_status OUT NOCOPY VARCHAR2);
138
139 --
140 -- Procedure: Container_Weight_Volume
141 -- Parameters: p_container_instance_id - Container instance Id
142 -- p_override_flag - if 'Y' then all container (this container instance and
143 -- all its child containers) weights/volume is updated
144 -- p_calc_wv_if_frozen - if 'Y' manually entered W/V will be overwritten
145 -- with calculated W/V
146 -- x_net_weight - calculated net weight
147 -- x_gross_weight - calculated gross weight
148 -- x_volume - calculated volume
149 -- p_fill_pc_flag - if 'Y' then fill percent is also calculated.
150 -- x_cont_fill_pc - calculated container fill percent
151 -- x_return_status - status of procedure call
152 -- Description: This procedure will calculate the net and gross weight and volume
153 -- of a container and its child containers by summing up all child containers
154 -- and loose item weights/volumes.
155 -- FOR TPA SELECTOR USE: wsh_tpa_selector_pkg.containerTP
156 --
157
158
159 PROCEDURE Container_Weight_Volume (
160 p_container_instance_id IN NUMBER,
161 p_override_flag IN VARCHAR2,
162 x_gross_weight OUT NOCOPY NUMBER,
163 x_net_weight OUT NOCOPY NUMBER,
164 x_volume OUT NOCOPY NUMBER,
165 p_fill_pc_flag IN VARCHAR2,
166 x_cont_fill_pc OUT NOCOPY NUMBER,
167 x_return_status OUT NOCOPY VARCHAR2,
168 p_calc_wv_if_frozen IN VARCHAR2 DEFAULT 'Y');
169
170 --<TPA_PUBLIC_NAME>
171 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.CONTAINERTP>
172
173 -- Start of comments
174 -- API name : Detail_Weight_Volume
175 -- Type : Public
176 -- Pre-reqs : None.
177 -- Function : Calculates Weight and Volume of Multiple Delivery Details
178 -- If p_update_flag is 'Y' then the calculated W/V is updated on Delivery Detail
179 -- Otherwise, the API returns the calculated W/V
180 -- If p_calc_wv_if_frozen is 'N' then W/V will be calculated not be calculated
181 -- for entities whose W/V is manually entered
182 -- Parameters :
183 -- IN:
184 -- p_detail_rows IN wsh_util_core.id_tab_type REQUIRED
185 -- p_update_flag IN VARCHAR2
186 -- 'Y' if the detail needs to be updated with the calculated W/V
187 -- p_calc_wv_if_frozen IN VARCHAR2
188 -- 'Y' if manual W/V can be overriden
189 -- OUT:
190 -- x_return_status OUT VARCHAR2 Required
191 -- gives the return status of API
192 -- Version : 1.0
193 -- End of comments
194
195 PROCEDURE Detail_Weight_Volume (
196 p_detail_rows IN wsh_util_core.id_tab_type,
197 p_override_flag IN VARCHAR2,
198 p_calc_wv_if_frozen IN VARCHAR2 DEFAULT 'Y',
199 x_return_status OUT NOCOPY VARCHAR2);
200
201
202 --
203 -- Procedure: Calc_Cont_Fill_Pc
204 -- Parameters: p_container_instance_id - Container Instance Id
205 -- p_update_flag - if 'Y' then fill percent and wt/vol is updated
206 -- p_fill_pc_basis - fill percent basis flag that determines if
207 -- fill percent is calculated by wt, vol or qty
208 -- x_fill_percent - calculated fill percent of container
209 -- x_return_status - status of procedure call
210 -- Description: This procedure will calculate the fill percent of the
211 -- container based on the fill percent basis flag. If the
212 -- container weight and volume has not been calculated it will
213 -- calculate the weight and volume before calculating the fill
214 -- percent. It will update the container instances table if
215 -- update flag is set to 'Y'
216 -- FOR TPA SELECTOR USE: wsh_tpa_selector_pkg.containerTP
217 --
218
219 PROCEDURE Calc_Cont_Fill_Pc (
220 p_container_instance_id IN NUMBER,
221 p_update_flag IN VARCHAR2,
222 p_fill_pc_basis IN VARCHAR2,
223 x_fill_percent OUT NOCOPY NUMBER,
224 x_return_status OUT NOCOPY VARCHAR2) ;
225
226 --<TPA_PUBLIC_NAME>
227 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.CONTAINERTP>
228
229 --
230 -- Procedure: Container_Tare_Weight_Self
231 -- Parameters: p_container_instance_id - Container Instance id
232 -- p_cont_item_id - inventory item id of container
233 -- p_wt_uom - weight uom code of tare weight
234 -- p_organization_id - organization id
235 -- x_cont_tare_wt - calculated weight of only specified container
236 -- x_return_status - status of procedure call
237 -- Description: This procedure will calculate the unit weight of the just the
238 -- container that is specified. Does not include tares of any
239 -- child containers.
240 --
241
242
243 PROCEDURE Container_Tare_Weight_Self (
244 p_container_instance_id IN NUMBER,
245 p_cont_item_id IN NUMBER,
249 x_return_status OUT NOCOPY VARCHAR2);
246 p_wt_uom IN VARCHAR2,
247 p_organization_id IN NUMBER,
248 x_cont_tare_wt OUT NOCOPY NUMBER,
250
251 --<TPA_PUBLIC_NAME>
252 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.CONTAINERTP>
253
254 --
255 -- Procedure: Check Fill PC
256 -- Parameters: p_container_instance_id - container instance id of container
257 -- p_calculation_flag - w/v calculation flag, 'Y' for Automatic and 'N' for Manual
258 -- x_fill_status - fill status of container - 'Overpacked',
259 -- 'Underpacked',or 'Success' (returns 'O','U' or 'S')
260 -- x_return_status - status of procedure call
261 -- Description: This procedure will check the fill status of container by
262 -- comparing the fill pc with min fill pc. If fill pc < min fill
263 -- pc then it is underpacked. If fill pc > 100 then overpacked
264 -- else 'Success'.
265 --
266
267 PROCEDURE Check_Fill_Pc (
268 p_container_instance_id IN NUMBER,
269 p_calc_wt_vol_flag IN VARCHAR2 DEFAULT 'Y', -- bug 2790656
270 x_fill_status OUT NOCOPY VARCHAR2,
271 x_return_status OUT NOCOPY VARCHAR2);
272
273
274 --
275 -- Procedure: Delivery_Weight_Volume
276 -- Parameters: p_delivery_id - Delivery_id of delivery
277 -- p_update_flag - if 'Y' then delivery weight/volume is updated
278 -- p_calc_wv_if_frozen - if 'N' then manually entered W/V will not be
279 -- overwritten with calculated W/V
280 -- x_net_weight - calculated net weight
281 -- x_gross_weight - calculated gross weight
282 -- x_volume - calculated volume
283 -- x_return_status - status of procedure call
284 -- Description: This procedure will calculate the net and gross weight and volume
285 -- of a delivery by summing up all container and
286 -- loose item weights/volumes
287 --
288
289 PROCEDURE Delivery_Weight_Volume
290 ( p_delivery_id IN NUMBER,
291 p_update_flag IN VARCHAR2,
292 p_calc_wv_if_frozen IN VARCHAR2 DEFAULT 'Y',
293 x_gross_weight OUT NOCOPY NUMBER,
294 x_net_weight OUT NOCOPY NUMBER,
295 x_volume OUT NOCOPY NUMBER,
296 x_return_status OUT NOCOPY VARCHAR2);
297
298 --
299 -- Procedure: Delivery_Weight_Volume
300 -- Parameters: p_del_rows - Delivery ids
301 -- p_update_flag - if 'Y' then delivery weight/volume is updated
302 -- p_calc_wv_if_frozen - if 'N' then manually entered W/V will not be
303 -- overwritten with calculated W/V
304 -- x_return_status - status of procedure call
305 -- Description: This procedure will calculate the net and gross weight and volume
306 -- of deliveries by calling the single delivery_weight_volume procedure
307 --
308
309 PROCEDURE Delivery_Weight_Volume
310 ( p_del_rows IN wsh_util_core.id_tab_type,
311 p_update_flag IN VARCHAR2,
312 p_calc_wv_if_frozen IN VARCHAR2 DEFAULT 'Y',
313 x_return_status OUT NOCOPY VARCHAR2);
314
315 -- Start of comments
316 -- API name : DD_WV_Post_Process
317 -- Type : Public
318 -- Pre-reqs : None.
319 -- Function : API to do post processing(Log exceptions in manual mode and
320 -- adjust W/V on parents in automatic mode) for a delivery detail or container
321 -- Parameters :
322 -- IN:
323 -- p_delivery_detail_id IN NUMBER Required
324 -- p_diff_gross_wt IN NUMBER
325 -- Gross Wt that needs to be adjusted on parent entities
326 -- p_diff_net_wt IN NUMBER
327 -- Net Wt that needs to be adjusted on parent entities
328 -- p_diff_volume IN NUMBER
329 -- Volume that needs to be adjusted on parent entities
330 -- p_diff_fill_volume IN NUMBER
331 -- Filled Volume that needs to be adjusted on parent entities
332 -- p_check_for_empty IN VARCHAR2
333 -- Check if the parent of p_delivery_detail_id becomes empty
334 -- without p_delivery_detail_id
335 -- OUT:
336 -- x_return_status OUT VARCHAR2 Required
337 -- give the return status of API
338 -- Version : 1.0
339 -- End of comments
340
341 PROCEDURE DD_WV_Post_Process(
342 p_delivery_detail_id IN NUMBER,
343 p_diff_gross_wt IN NUMBER,
344 p_diff_net_wt IN NUMBER,
345 p_diff_volume IN NUMBER DEFAULT null,
346 p_diff_fill_volume IN NUMBER DEFAULT null,
347 p_check_for_empty IN VARCHAR2 DEFAULT 'N',
348 x_return_status OUT NOCOPY VARCHAR2);
349
350 -- Start of comments
351 -- API name : Del_WV_Post_Process
352 -- Type : Public
353 -- Pre-reqs : None.
354 -- Function : API to do post processing(Log exceptions in manual mode and
355 -- adjust W/V on parents in automatic mode) for a delivery
356 -- Parameters :
357 -- IN:
358 -- p_delivery_id IN NUMBER Required
359 -- p_diff_gross_wt IN NUMBER
360 -- Gross Wt that needs to be adjusted on parent entities
361 -- p_diff_net_wt IN NUMBER
362 -- Net Wt that needs to be adjusted on parent entities
363 -- p_diff_volume IN NUMBER
364 -- Volume that needs to be adjusted on parent entities
365 -- p_check_for_empty IN VARCHAR2
366 -- Check if the parent of p_delivery_id becomes empty
367 -- without p_delivery_id
368 -- p_leg_id IN VARCHAR2
369 -- Do Post Processing only for the specified delivery/delivery leg
370 -- OUT:
371 -- x_return_status OUT VARCHAR2 Required
372 -- give the return status of API
373 -- Version : 1.0
374
375 PROCEDURE Del_WV_Post_Process(
376 p_delivery_id IN NUMBER,
380 p_check_for_empty IN VARCHAR2 DEFAULT 'N',
377 p_diff_gross_wt IN NUMBER,
378 p_diff_net_wt IN NUMBER,
379 p_diff_volume IN NUMBER,
381 p_leg_id IN NUMBER DEFAULT NULL,
382 x_return_status OUT NOCOPY VARCHAR2);
383
384 -- Start of comments
385 -- API name : Detail_Weight_Volume
386 -- Type : Public
387 -- Pre-reqs : None.
388 -- Function : Calculates Weight and Volume of Delivery Detail
389 -- If p_update_flag is 'Y' then the calculated W/V is updated on Delivery Detail
390 -- Otherwise, the API returns the calculated W/V
391 -- Parameters :
392 -- IN:
393 -- p_delivery_detail_id IN NUMBER Required
394 -- p_update_flag IN VARCHAR2
395 -- 'Y' if the detail needs to be updated with the calculated W/V
396 -- p_post_process_flag IN VARCHAR2
397 -- 'Y' if W/V post processing is required
398 -- p_calc_wv_if_frozen IN VARCHAR2
399 -- 'N' if W/V should not be calculated if W/V is frozen
400 -- OUT:
401 -- x_net_weight OUT NUMBER
402 -- gives the net weight of delivery detail
403 -- x_volume OUT NUMBER
404 -- gives the volume of delivery detail
405 -- x_return_status OUT VARCHAR2 Required
406 -- give the return status of API
407 -- Version : 1.0
408 -- End of comments
409
410 PROCEDURE Detail_Weight_Volume (
411 p_delivery_detail_id IN NUMBER,
412 p_update_flag IN VARCHAR2,
413 p_post_process_flag IN VARCHAR2,
414 p_calc_wv_if_frozen IN VARCHAR2 DEFAULT 'Y',
415 x_net_weight OUT NOCOPY NUMBER,
416 x_volume OUT NOCOPY NUMBER,
417 x_return_status OUT NOCOPY VARCHAR2);
418
419 -- Start of comments
420 -- API name : Container_Weight_Volume
421 -- Type : Private
422 -- Pre-reqs : None.
423 -- Function : Calculates Weight and Volume of Container
424 -- If p_override_flag is 'Y' then the calculated W/V is updated on Container
425 -- Otherwise, the API returns the calculated W/V
426 -- If p_post_process_flag is 'Y' then calls post processing API
427 -- Parameters :
428 -- IN:
429 -- p_container_instance_id IN NUMBER Required
430 -- p_override_flag IN VARCHAR2
431 -- 'Y' if the detail needs to be updated with the calculated W/V
432 -- p_fill_pc_flag IN VARCHAR2
433 -- 'Y' if fill% needs to be calculated
434 -- p_post_process_flag IN VARCHAR2
435 -- 'Y' if W/V post processing is required
436 -- p_calc_wv_if_frozen IN VARCHAR2
437 -- 'Y' if manual W/V can be overriden
438 -- OUT:
439 -- x_gross_weight OUT NUMBER
440 -- gives the gross weight of container
441 -- x_net_weight OUT NUMBER
442 -- gives the net weight of container
443 -- x_volume OUT NUMBER
444 -- gives the volume of container
445 -- x_cont_fill_pc OUT NUMBER
446 -- gives the Fill% of container
447 -- x_return_status OUT VARCHAR2 Required
448 -- give the return status of API
449 -- Version : 1.0
450 -- End of comments
451
452 PROCEDURE Container_Weight_Volume (
453 p_container_instance_id IN NUMBER,
454 p_override_flag IN VARCHAR2,
455 p_fill_pc_flag IN VARCHAR2,
456 p_post_process_flag IN VARCHAR2,
457 p_calc_wv_if_frozen IN VARCHAR2 DEFAULT 'Y',
458 x_gross_weight OUT NOCOPY NUMBER,
459 x_net_weight OUT NOCOPY NUMBER,
460 x_volume OUT NOCOPY NUMBER,
461 x_cont_fill_pc OUT NOCOPY NUMBER,
462 x_return_status OUT NOCOPY VARCHAR2);
463
464 PROCEDURE Adjust_parent_WV (
465 p_entity_type IN VARCHAR2,
466 p_entity_id IN NUMBER,
467 p_gross_weight IN NUMBER,
468 p_net_weight IN NUMBER,
469 p_volume IN NUMBER DEFAULT null,
470 p_filled_volume IN NUMBER DEFAULT null,
471 p_wt_uom_code IN VARCHAR2,
472 p_vol_uom_code IN VARCHAR2,
473 p_inv_item_id IN NUMBER,
474 x_return_status OUT NOCOPY VARCHAR2,
475 p_stop_type IN VARCHAR2 DEFAULT NULL);
476
477 -- HW OPMCONV - new routine to check deviation
478 -- Function: within_deviation
479 -- Parameters: p_organization_id - organization id
480 -- p_inventory_item_id - Inventory Item id
481 -- p_lot_number - Lot number
482 -- p_precision - Precision - default 5
483 -- p_quantity - Primary Qty
484 -- p_uom1 - Uom code to convert from
485 -- p_quantity2 - Secondary Qty
486 -- p_uom2 - Uom code to convert to
487 -- muom
488 /* When splitting a line/detail, the deviation check is done only on the split quantity.
489 If the deviation check needs to be done on the remaining quantity, then this api needs
490 to be called for the remaining quantity and p_split_check should be passed as 'Y'. If
491 the deviation check fails, then a new message is shown.
492 */
493 -- p_split_check - check for remaining split qty
494 -- Description: This function calls new INV routine INV_CONVERT.within_deviation
495 -- to check if Qtys are within deviation for item types 'D' and 'N'
496 --
497 FUNCTION within_deviation (
498 p_organization_id IN NUMBER,
499 p_inventory_item_id IN NUMBER,
500 p_lot_number IN VARCHAR2,
501 p_precision IN NUMBER default 5,
502 p_quantity IN NUMBER,
503 p_uom1 IN VARCHAR2,
504 p_quantity2 IN NUMBER,
505 p_uom2 IN VARCHAR2,
506 -- muom
507 p_split_check IN VARCHAR2 DEFAULT NULL) RETURN NUMBER;
508
509 -- Bug#4254552:"Proration of weight from Delivery to delivery lines" Project.
510 -- Procedure name : Prorate_weight
511 -- Pre-reqs : Prorate_wt_flag should be 'Y' for the delivery.
512 -- Description : Prorates weight of the given delivery/container to its immediate children
513 --
514 -- Parameters :
515 -- p_entity_type - 'DELIVERY' or 'CONTAINER'
516 -- p_entity_id - Delivery_id or Container_id
517 -- p_old_gross_wt - Original Gross Weight of the entity
518 -- p_new_gross_wt - New Gross Weight of the entity
519 -- p_old_net_wt - Original Net Weight of the entity
520 -- p_net_net_wt - New Net Weight of the entity
521 -- p_weight_uom_code - Weight UOM of the entity
522
523 PROCEDURE Prorate_weight(
524 p_entity_type IN VARCHAR2,
525 p_entity_id IN NUMBER,
526 p_old_gross_wt IN NUMBER,
527 p_new_gross_wt IN NUMBER,
528 p_old_net_wt IN NUMBER,
529 p_new_net_wt IN NUMBER,
530 p_weight_uom_code IN VARCHAR2,
531 x_return_status OUT NOCOPY VARCHAR2,
532 p_call_level IN NUMBER DEFAULT NULL);
533
534 END WSH_WV_UTILS;
535