1 PACKAGE WSH_WV_UTILS as
2 /* $Header: WSHWVUTS.pls 120.4 2007/01/05 19:26:03 parkhj noship $ */
3
4 --<TPA_PUBLIC_NAME=WSH_TPA_CONTAINER_PKG>
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
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
122 -- Description: This procedure will update container weight/volume with calculated values
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,
246 p_wt_uom IN VARCHAR2,
247 p_organization_id IN NUMBER,
248 x_cont_tare_wt OUT NOCOPY NUMBER,
249 x_return_status OUT NOCOPY VARCHAR2);
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,
377 p_diff_gross_wt IN NUMBER,
378 p_diff_net_wt IN NUMBER,
379 p_diff_volume IN NUMBER,
380 p_check_for_empty IN VARCHAR2 DEFAULT 'N',
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 -- Description: This function calls new INV routine INV_CONVERT.within_deviation
488 -- to check if Qtys are within deviation for item types 'D' and 'N'
489 --
490 FUNCTION within_deviation (
491 p_organization_id IN NUMBER,
492 p_inventory_item_id IN NUMBER,
493 p_lot_number IN VARCHAR2,
494 p_precision IN NUMBER default 5,
495 p_quantity IN NUMBER,
496 p_uom1 IN VARCHAR2,
497 p_quantity2 IN NUMBER,
498 p_uom2 IN VARCHAR2) RETURN NUMBER;
499
500 -- Bug#4254552:"Proration of weight from Delivery to delivery lines" Project.
501 -- Procedure name : Prorate_weight
502 -- Pre-reqs : Prorate_wt_flag should be 'Y' for the delivery.
503 -- Description : Prorates weight of the given delivery/container to its immediate children
504 --
505 -- Parameters :
506 -- p_entity_type - 'DELIVERY' or 'CONTAINER'
507 -- p_entity_id - Delivery_id or Container_id
508 -- p_old_gross_wt - Original Gross Weight of the entity
509 -- p_new_gross_wt - New Gross Weight of the entity
510 -- p_old_net_wt - Original Net Weight of the entity
511 -- p_net_net_wt - New Net Weight of the entity
512 -- p_weight_uom_code - Weight UOM of the entity
513
514 PROCEDURE Prorate_weight(
515 p_entity_type IN VARCHAR2,
516 p_entity_id IN NUMBER,
517 p_old_gross_wt IN NUMBER,
518 p_new_gross_wt IN NUMBER,
519 p_old_net_wt IN NUMBER,
520 p_new_net_wt IN NUMBER,
521 p_weight_uom_code IN VARCHAR2,
522 x_return_status OUT NOCOPY VARCHAR2,
523 p_call_level IN NUMBER DEFAULT NULL);
524
525 END WSH_WV_UTILS;
526
[Home] [Help]