1 PACKAGE WMS_CATCH_WEIGHT_PVT AUTHID CURRENT_USER AS
2 /* $Header: WMSVCWTS.pls 120.0.12020000.3 2013/04/11 20:59:32 sahmahes ship $ */
3
4 -- Constants for MTL_SYSTEM_ITEMS_B.TRACKING_QUANTITY_IND
5 -- Possible values are P/PS (primary/primary and secondary)
6 G_TRACK_PRIMARY CONSTANT VARCHAR(30) := 'P';
7 G_TRACK_PRIMARY_SECONDARY CONSTANT VARCHAR(30) := 'PS';
8
9 -- Constants for MTL_SYSTEM_ITEMS_B.ONT_PRICING_QTY_SOURCE
10 -- Possible values are P/S (primary/secondary)
11 G_PRICE_PRIMARY CONSTANT VARCHAR(30) := 'P';
12 G_PRICE_SECONDARY CONSTANT VARCHAR(30) := 'S';
13
14 -- Constants for MTL_SYSTEM_ITEMS_B.SECONDARY_DEFAULT_IND
15 -- Possible values are F/D/N (Fixed/Default/No default)
16 G_SECONDARY_FIXED CONSTANT VARCHAR(30) := 'F';
17 G_SECONDARY_DEFAULT CONSTANT VARCHAR(30) := 'D';
18 G_SECONDARY_NO_DEFAULT CONSTANT VARCHAR(30) := 'N';
19
20 -- Constants used for return value of Check_Lpn_Secondary_Quantity
21 G_CHECK_SUCCESS CONSTANT NUMBER := 0;
22 G_CHECK_ERROR CONSTANT NUMBER := 1;
23 G_CHECK_WARNING CONSTANT NUMBER := 2;
24
25 TYPE t_genref IS REF CURSOR;
26
27 -- Start of comments
28 -- API name: Get_Catch_Weight_Attributes
29 -- Type : Private
30 -- Pre-reqs: None.
31 -- Function: Returns the column values from MTL_SYSTEM_ITEMS
32 -- concerning catch weights for a given organization
33 -- and inventory_item_id pair
34 -- Parameters:
35 -- IN: p_organization_id IN NUMBER Required
36 -- Item organization id. Part of the unique key
37 -- that uniquely identifies an item record.
38 -- p_inventory_item_id IN NUMBER Required
39 -- Corresponds to the column INVENTORY_ITEM_ID in
40 -- the table MTL_SYSTEM_ITEMS, and identifies the
41 -- record to in which catch weigth attributes should
42 -- be retrieved.
43 -- p_quantity IN NUMBER Optional
44 -- Quantity which secondary quantity should be calculated from
45 -- p_uom_code IN NUMBER Optional
46 -- The UOM in which secondary quantity should be calculated from
47 -- If no UOM is passes, API will use the primary UOM
48 -- OUT: x_tracking_quantity_ind OUT NOCOPY VARCHAR2
49 -- Corresponds to MTL_SYSTEM_ITEMS_B.TRACKING_QUANTITY_IND
50 -- x_ont_pricing_qty_source OUT NOCOPY VARCHAR2
51 -- Corresponds to MTL_SYSTEM_ITEMS_B.ONT_PRICING_QTY_SOURCE
52 -- x_secondary_default_ind OUT NOCOPY VARCHAR2
53 -- Corresponds to MTL_SYSTEM_ITEMS_B.SECONDARY_DEFULAT_IND
54 -- x_secondary_quantity OUT NOCOPY NUMBER
55 -- If item is catch weight enabled and can be defaulted, returns
56 -- the default secondary quantity based on the conversion of p_quantity
57 -- into the secondary uom. Returns null otherwise.
58 -- x_secondary_uom_code OUT NOCOPY VARCHAR2
59 -- Corresponds to MTL_SYSTEM_ITEMS_B.SECONDARY_UOM_CODE
60 -- x_uom_deviation_high OUT NOCOPY NUMBER
61 -- Corresponds to MTL_SYSTEM_ITEMS_B.DUAL_UOM_DEVIATION_HIGH
62 -- x_uom_deviation_low OUT NOCOPY NUMBER
63 -- Corresponds to MTL_SYSTEM_ITEMS_B.DUAL_UOM_DEVIATION_LOW
64 -- Version : Current version 1.0
65 -- End of comments
66
67 PROCEDURE Get_Catch_Weight_Attributes (
68 p_api_version IN NUMBER
69 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
70 , x_return_status OUT NOCOPY VARCHAR2
71 , x_msg_count OUT NOCOPY NUMBER
72 , x_msg_data OUT NOCOPY VARCHAR2
73 , p_organization_id IN NUMBER
74 , p_inventory_item_id IN NUMBER
75 , p_quantity IN NUMBER := NULL
76 , p_uom_code IN VARCHAR2 := NULL
77 , x_tracking_quantity_ind OUT NOCOPY VARCHAR2
78 , x_ont_pricing_qty_source OUT NOCOPY VARCHAR2
79 , x_secondary_default_ind OUT NOCOPY VARCHAR2
80 , x_secondary_quantity OUT NOCOPY NUMBER
81 , x_secondary_uom_code OUT NOCOPY VARCHAR2
82 , x_uom_deviation_high OUT NOCOPY NUMBER
83 , x_uom_deviation_low OUT NOCOPY NUMBER
84 );
85
86
87 -- Start of comments
88 -- API name: Get_Ont_Pricing_Qty_Source
89 -- Type : Private
90 -- Pre-reqs: None.
91 -- Function: Returns the column value of ONT_PRICING_QTY_SOURCE
92 -- from MTL_SYSTEM_ITEMS for a given organization and
93 -- inventory_item_id pair
94 -- Parameters:
95 -- IN: p_organization_id IN NUMBER Required
96 -- Item organization id. Part of the unique key
97 -- that uniquely identifies an item record.
98 -- p_inventory_item_id IN NUMBER Required
99 -- Corresponds to the column INVENTORY_ITEM_ID in
100 -- the table MTL_SYSTEM_ITEMS, and identifies the
101 -- record to in which catch weigth attributes should
102 -- be retrieved.
103 -- OUT: returns the value of MTL_SYSTEM_ITEMS_B.ONT_PRICING_QTY_SOURCE
104 --
105 -- Version : Current version 1.0
106 -- End of comments
107 FUNCTION Get_Ont_Pricing_Qty_Source (
108 p_api_version IN NUMBER
109 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
110 , x_return_status OUT NOCOPY VARCHAR2
111 , x_msg_count OUT NOCOPY NUMBER
112 , x_msg_data OUT NOCOPY VARCHAR2
113 , p_organization_id IN NUMBER
114 , p_inventory_item_id IN NUMBER
115 ) RETURN VARCHAR2;
116
117 -- Start of comments
118 -- API name: Get_Default_Secondary_Quantity
119 -- Type : Private
120 -- Function: For the given item, org, quantity and uom passed by user for it will
121 -- return the ONT_PRICING_QTY_SOURCE and secondary_uom_code, from
122 -- MTL_SYSTEM_ITEMS and calculate the secondary quantity if the item is
123 -- set up to be 'defaultable'.
124 -- Parameters:
125 -- IN: p_organization_id IN NUMBER Required
126 -- Item organization id. Part of the unique key
127 -- that uniquely identifies an item record.
128 -- p_inventory_item_id IN NUMBER Required
129 -- Item ID of item which secondary quantity should be calculated for.
130 -- p_quantity IN NUMBER Required
131 -- Quantity which secondary quantity should be calculated from
132 -- p_uom_code IN NUMBER Optional
133 -- The UOM in which secondary quantity should be calculated from
134 -- If no UOM is passes, API will use the primary UOM
135 -- p_secondary_uom_code IN VARCHAR2 Optional
136 -- value to be used to calcuate default secondary quantity. If left null
137 -- will use secondary_uom_code defined in MTL_SYSTEM_ITEMS.
138 -- p_ont_pricing_qty_source
139 -- value to be used to determine catch weight enabled. If left null will
140 -- use value defined in MTL_SYSTEM_ITEMS.
141 -- p_secondary_default_ind
142 -- value to be used to determine item can default catch weights. If left null
143 -- will use value defined in MTL_SYSTEM_ITEMS.
144 -- IN OUT:
145 -- x_ont_pricing_qty_source
146 -- takes in MTL_SYSTEM_ITEMS_B.ONT_PRICING_QTY_SOURCE if user has it available
147 -- this reduces number of database hits if not availaible API returns table value
148 -- for the given item
149 -- x_secondary_uom_code OUT NOCOPY VARCHAR2
150 -- takes in secondary uom if user has value already available. If not API
151 -- returns the default secondary uom if item is catch weight is enabled
152 -- null otherwise
153 -- OUT:
154 -- x_secondary_quantity OUT NOCOPY NUMBER
155 -- If item is catch weight enabled and can be defaulted, returns
156 -- the default secondary quantity based on the conversion of p_quantity
157 -- into the secondary uom. Returns null otherwise.
158 -- Version : Current version 1.0
159 -- End of comments
160
161 PROCEDURE Get_Default_Secondary_Quantity (
162 p_api_version IN NUMBER
163 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
164 , x_return_status OUT NOCOPY VARCHAR2
165 , x_msg_count OUT NOCOPY NUMBER
166 , x_msg_data OUT NOCOPY VARCHAR2
167 , p_organization_id IN NUMBER
168 , p_inventory_item_id IN NUMBER
169 , p_quantity IN NUMBER
170 , p_uom_code IN VARCHAR2
171 , p_secondary_default_ind IN VARCHAR2 := NULL
172 , x_ont_pricing_qty_source IN OUT NOCOPY VARCHAR2
173 , x_secondary_uom_code IN OUT NOCOPY VARCHAR2
174 , x_secondary_quantity OUT NOCOPY NUMBER
175 );
176
177 -- Start of comments
178 -- API name: Check_Secondary_Qty_Tolerance
179 -- Type : Private
180 -- Function: Based on the source table (MMTT or WDD) the api will retrieve the necessary
181 -- information from the specified record to see if the item is catch weight
182 -- enabled. If so, it will return the ONT_PRICING_QTY_SOURCE and the secondary
183 -- UOM. If the item is also catch weight defaltable it will try to calculate
184 -- the secondary quantity.
185 -- Parameters:
186 -- IN: p_organization_id IN NUMBER Required
187 -- Item organization id.
188 -- p_inventory_item_id IN NUMBER Required
189 -- Item ID of item which the tolerances should be checked.
190 -- p_quantity IN NUMBER Required
191 -- Quantity which secondary quantity should be checked for tolerance against
192 -- p_uom_code IN NUMBER Required
193 -- The UOM in which secondary quantity should be checked for tolerance
194 -- against. If no UOM is passes, API will use the primary UOM
195 -- p_secondary_quantity IN NUMBER Required
196 -- the secondary quantity in secondary uom
197 -- p_secondary_uom_code IN VARCHAR2 Optional
198 -- value to be used to calcuate default secondary quantity. If left null
199 -- will use secondary_uom_code defined in MTL_SYSTEM_ITEMS.
200 -- p_ont_pricing_qty_source
201 -- value to be used to determine catch weight enabled. If left null will
202 -- use the value defined in MTL_SYSTEM_ITEMS.
203 -- p_uom_deviation_high
204 -- value to be used to determine the upper deviation. If left null will
205 -- use value defined in MTL_SYSTEM_ITEMS.
206 -- p_uom_deviation_low
207 -- value to be used to determine the lower deviation. If left null will
208 -- use value defined in MTL_SYSTEM_ITEMS.
209 -- OUT: returns 0 if the secodnary quantity is within tolerance
210 -- 1 if the secodnary quantity is above the upper tolerance
211 -- -1 if the secodnary quantity is beneath the lower tolerance
212 -- Version : Current version 1.0
213 -- End of comments
214
215 FUNCTION Check_Secondary_Qty_Tolerance (
216 p_api_version IN NUMBER
217 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
218 , x_return_status OUT NOCOPY VARCHAR2
219 , x_msg_count OUT NOCOPY NUMBER
220 , x_msg_data OUT NOCOPY VARCHAR2
221 , p_organization_id IN NUMBER
222 , p_inventory_item_id IN NUMBER
223 , p_quantity IN NUMBER
224 , p_uom_code IN VARCHAR2
225 , p_secondary_quantity IN NUMBER
226 , p_secondary_uom_code IN VARCHAR2 := NULL
227 , p_ont_pricing_qty_source IN VARCHAR2 := NULL
228 , p_uom_deviation_high IN NUMBER := NULL
229 , p_uom_deviation_low IN NUMBER := NULL
230 ) RETURN NUMBER;
231
232 -- Start of comments
233 -- API name: Update_Shipping_Secondary_Qty
234 -- Type : Private
235 -- Function: Given a delivery_detail_id for a line from wsh_delivery_details
236 -- API will check tolerances and call a shipping API to update the
237 -- picked_quantity2 and requested_quantity_uom2 fields
238 -- Parameters:
239 -- IN: p_delivery_detail_id IN NUMBER Required
240 -- Delivery detail ID of record from wsh_delivery_details to be updated
241 -- p_secondary_quantity IN NUMBER Required
242 -- value to be used to update picked_quantity2. If left null, API will
243 -- try to default value if item is defaultable. If value is passed as
244 -- FND_API.G_MISS_NUM, API will null the picked_quantity2 and
245 -- requested_quantity_uom2 fields for that particular delivery detail
246 -- p_secondary_uom_code IN VARCHAR2 Optional
247 -- value to be used to update requested_quantity_uom2. If left null will
248 -- use secondary_uom_code defined in MTL_SYSTEM_ITEMS. If defined, will
249 -- be validated against value defined in MTL_SYSTEM_ITEMS and fail if they
250 -- are not the same, since we currently only support one secondary UOM
251 -- Version : Current version 1.0
252 -- End of comments
253
254 PROCEDURE Update_Shipping_Secondary_Qty (
255 p_api_version IN NUMBER
256 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
257 , p_commit IN VARCHAR2 := fnd_api.g_false
258 , x_return_status OUT NOCOPY VARCHAR2
259 , x_msg_count OUT NOCOPY NUMBER
260 , x_msg_data OUT NOCOPY VARCHAR2
261 , p_delivery_detail_id IN NUMBER
262 , p_secondary_quantity IN NUMBER
263 , p_secondary_uom_code IN VARCHAR2 := NULL
264 );
265
266 -- Start of comments
267 -- API name: Update_Parent_Delivery_Sec_Qty
268 -- Type : Private
269 -- Function: Given a delivery_detail_id for a License Plate Number and content item
270 -- information. API will use a shipping API to update the
271 -- picked_quantity2 and requested_quantity_uom2 fields, distributing to
272 -- proportionately over all child WDD lines for that item/rev/lot combination
273 -- Parameters:
274 -- IN: p_organization_id IN NUMBER Required
275 -- Organization id.
276 -- p_parent_del_det_id IN NUMBER Required
277 -- Delivery detail ID of License Plate Number
278 -- p_inventory_item_id IN NUMBER Required
279 -- Item ID of item to be update with secondary quantity and secondary quantity UOM.
280 -- p_revision IN VARCHAR2 Optional
281 -- Revision of item to be update with secondary quantity and secondary quantity UOM.
282 -- required for revision controlled items.
283 -- p_lot_number IN VARCHAR2 Optional
284 -- Lot number of item to be update with secondary quantity and secondary quantity UOM.
285 -- required for lot controlled items.
286 -- p_quantity IN NUMBER Required
287 -- Quantity which secondary quantity will use to determine proportionate distribution
288 -- p_uom_code IN NUMBER Required
289 -- The UOM in which secondary quantity will use to calculate proportionate distribution.
290 -- p_secondary_quantity IN NUMBER
291 -- the secondary quantity in secondary uom
292 -- p_secondary_uom_code IN VARCHAR2 Optional
293 -- value to be used to update requested_quantity_uom2. If left null will
294 -- use secondary_uom_code defined in MTL_SYSTEM_ITEMS. If defined, will
295 -- be validated against value defined in MTL_SYSTEM_ITEMS and fail if they
296 -- are not the same, since we currently only support one secondary UOM
297 -- Version : Current version 1.0
298 -- End of comments
299
300 PROCEDURE Update_Parent_Delivery_Sec_Qty (
301 p_api_version IN NUMBER
302 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
303 , p_commit IN VARCHAR2 := fnd_api.g_false
304 , x_return_status OUT NOCOPY VARCHAR2
305 , x_msg_count OUT NOCOPY NUMBER
306 , x_msg_data OUT NOCOPY VARCHAR2
307 , p_organization_id IN NUMBER
308 , p_parent_del_det_id IN NUMBER
309 , p_inventory_item_id IN NUMBER
310 , p_revision IN VARCHAR2 := NULL
311 , p_lot_number IN VARCHAR2 := NULL
312 , p_quantity IN NUMBER
313 , p_uom_code IN VARCHAR2
314 , p_secondary_quantity IN NUMBER
315 , p_secondary_uom_code IN VARCHAR2
316 );
317
318 -- Start of comments
319 -- API name: Update_LPN_Secondary_Quantity
320 -- Type : Private
321 -- Function: Given a lpn_id for a License Plate Number and content item information, and
322 -- table source, API will update the picked_quantity2 and requested_quantity_uom2
323 -- fields if source is 'WDD', or secondary quantity and UOM fields in
324 -- MTL_MATERIAL_TRANSACTIONS_TEMP and MTL_TRANSACTION_LOTS_TEMP if source is 'MMTT'.
325 -- The secondary quantity will be distributed proportionately over all child lines
326 -- for that item/rev/lot combination
327 -- Parameters:
328 -- IN: p_record_source IN VARCHAR2 Required
332 -- p_organization_id IN NUMBER Required
329 -- Determines the source table where the record should be updated
330 -- 'MMTT' = MTL_MATERIAL_TRANSACTIONS_TEMP/MTL_TRANSACTION_LOTS_TEMP
331 -- 'WDD' = WSH_DELIVERY_DETAILS
333 -- Organization id.
334 -- p_lpn_id IN NUMBER Required
335 -- LPN ID of License Plate Number
336 -- p_inventory_item_id IN NUMBER Required
337 -- Item ID of item to be update with secondary quantity and secondary quantity UOM.
338 -- p_revision IN VARCHAR2 Optional
339 -- Revision of item to be update with secondary quantity and secondary quantity UOM.
340 -- required for revision controlled items.
341 -- p_lot_number IN VARCHAR2 Optional
342 -- Lot number of item to be update with secondary quantity and secondary quantity UOM.
343 -- required for lot controlled items.
344 -- p_quantity IN NUMBER Required
345 -- Quantity which secondary quantity will use to determine proportionate distribution
346 -- p_uom_code IN NUMBER Required
347 -- The UOM in which secondary quantity will use to calculate proportionate distribution.
348 -- p_secondary_quantity IN NUMBER
349 -- the secondary quantity in secondary uom
350 -- p_secondary_uom_code IN VARCHAR2 Optional
351 -- value to be used to update requested_quantity_uom2. If left null will
352 -- use secondary_uom_code defined in MTL_SYSTEM_ITEMS. If defined, will
353 -- be validated against value defined in MTL_SYSTEM_ITEMS and fail if they
354 -- are not the same, since we currently only support one secondary UOM
355 -- Version : Current version 1.0
356 -- End of comments
357
358 PROCEDURE Update_LPN_Secondary_Quantity (
359 p_api_version IN NUMBER
360 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
361 , p_commit IN VARCHAR2 := fnd_api.g_false
362 , x_return_status OUT NOCOPY VARCHAR2
363 , x_msg_count OUT NOCOPY NUMBER
364 , x_msg_data OUT NOCOPY VARCHAR2
365 , p_record_source IN VARCHAR2
366 , p_organization_id IN NUMBER
367 , p_lpn_id IN NUMBER
368 , p_inventory_item_id IN NUMBER
369 , p_revision IN VARCHAR2 := NULL
370 , p_lot_number IN VARCHAR2 := NULL
371 , p_quantity IN NUMBER
372 , p_uom_code IN VARCHAR2
373 , p_secondary_quantity IN NUMBER
374 , p_secondary_uom_code IN VARCHAR2
375 );
376
377
378 --
379 -- Update LPN primary quantity (when fulfillment base is secondary)
380 --
381 PROCEDURE update_lpn_primary_quantity (
382 p_api_version IN NUMBER
383 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
384 , p_commit IN VARCHAR2 := fnd_api.g_false
385 , x_return_status OUT NOCOPY VARCHAR2
386 , x_msg_count OUT NOCOPY NUMBER
387 , x_msg_data OUT NOCOPY VARCHAR2
388 , p_record_source IN VARCHAR2
389 , p_organization_id IN NUMBER
390 , p_lpn_id IN NUMBER
391 , p_inventory_item_id IN NUMBER
392 , p_revision IN VARCHAR2 := NULL
393 , p_lot_number IN VARCHAR2 := NULL
394 , p_quantity IN NUMBER
395 , p_uom_code IN VARCHAR2
396 , p_secondary_quantity IN NUMBER
397 , p_secondary_uom_code IN VARCHAR2
398 , p_max_pri_residual IN NUMBER
399 , p_ccnt_sec_residual IN VARCHAR2
400 );
401
402
403 -- Start of comments
404 -- API name: Check_LPN_Secondary_Quantity
405 -- Type : Private
406 -- Function: Given a lpn_id for a outermost license plate number and organization ID,
407 -- API will iterate through all it's content items and nested LPNs. Checking
408 -- if the item is catch weight enabled, and if so, check to see if secondary
409 -- quantity and secondary UOM values have been defined for them.
410 -- Parameters:
411 -- IN: p_organization_id IN NUMBER Required
412 -- Organization ID.
413 -- p_outermost_lpn_id IN NUMBER Required
414 -- LPN ID of outermoste license plate number
415 -- OUT: returns:
416 -- 0 (G_CHECK_SUCCESS) if all items in outermost LPN and nested LPNs that are catch
417 -- weight enabled have catch weight values defined.
418 -- 1 (G_CHECK_ERROR) if an item was found in the outermost or nested LPNs that requires
419 -- catch weights but could not be defaulted, either due to setup or invalid uom
420 -- conversion
421 -- 2 (G_CHECK_WARNING) if an item was found in the outermost or nested LPNs that do
422 -- not have catch weight values defined, but can and will be defaulted
423 -- Version : Current version 1.0
424 -- End of comments
425
426 FUNCTION Check_LPN_Secondary_Quantity (
427 p_api_version IN NUMBER
428 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
429 , x_return_status OUT NOCOPY VARCHAR2
430 , x_msg_count OUT NOCOPY NUMBER
431 , x_msg_data OUT NOCOPY VARCHAR2
432 , p_organization_id IN NUMBER
433 , p_outermost_lpn_id IN NUMBER
434 ) RETURN NUMBER;
435
436 -- Start of comments
437 -- API name: GET_OUTER_CATCH_WT_LPN
438 -- Type : Private
439 -- Pre-reqs: None.
440 -- Function: LOV Query, Returns the list of valid Outer LPN
441 -- Parameters:
442 -- IN: p_org_id IN NUMBER Required
443 -- Item organization id. Part of the unique key
444 -- that uniquely identifies an item record.
445 -- p_lpn IN VARCHAR2 Required
446 -- LPN which user might enter.
447 -- p_entry_type IN VARCHAR2 Required
448 -- Could be ALL, NEW, based on the update or New mode user
449 -- selects from the menu
450 -- OUT: x_lpn_lov OUT NOCOPY t_genref
454 PROCEDURE get_outer_catch_wt_lpn
451 -- List of valid outer lpns.
452
453
455 (x_lpn_lov OUT NOCOPY t_genref,
456 p_org_id IN NUMBER,
457 p_lpn IN VARCHAR2,
458 p_entry_type IN VARCHAR2);
459
460 -- Start of comments
461 -- API name: GET_INNER_CATCH_WT_LPN
462 -- Type : Private
463 -- Pre-reqs: None.
464 -- Function: LOV Query, Returns the list of valid Inner LPN
465 -- Parameters:
466 -- IN: p_org_id IN NUMBER Required
467 -- Item organization id. Part of the unique key
468 -- that uniquely identifies an item record.
469 -- p_outer_lpn_id IN NUMBER Required
470 -- LPN Id of the outer lpn.
471 -- p_entry_type IN VARCHAR2 Required
472 -- Could be ALL, NEW, based on the update or New mode user
473 -- selects from the menu
474 -- p_lpn_context IN NUMBER
475 -- lpn context of the outer lpn
476 -- p_inner_lpn IN VARCHAR2
477 -- Partial value of inner lpn user might enter
478 -- OUT: x_lpn_lov OUT NOCOPY t_genref
479 -- List of valid inner lpns.
480
481 PROCEDURE get_inner_catch_wt_lpn
482 (x_lpn_lov OUT NOCOPY t_genref,
483 p_org_id IN NUMBER,
484 p_outer_lpn_id IN NUMBER,
485 p_entry_type IN VARCHAR2,
486 p_lpn_context IN NUMBER,
487 p_inner_lpn IN VARCHAR2);
488
489 -- Start of comments
490 -- API name: GET_CATCH_WT_ITEMS
491 -- Type : Private
492 -- Pre-reqs: None.
493 -- Function: LOV Query, Returns the list of valid Item in the inner LPN
494 -- Parameters:
495 -- IN: p_org_id IN NUMBER Required
496 -- Item organization id. Part of the unique key
497 -- that uniquely identifies an item record.
498 -- p_lpn_id IN NUMBER Required
499 -- LPN Id of the Inner lpn.
500 -- p_entry_type IN VARCHAR2 Required
501 -- Could be ALL, NEW, based on the update or New mode user
502 -- selects from the menu
503 -- p_lpn_context IN NUMBER Required
504 -- lpn context of the outer lpn
505 -- p_concat_item_segment IN VARCHAR2 not Required
506 -- Partial/Full value of the Item
507 -- OUT: x_item_lov OUT NOCOPY t_genref
508 -- List of valid items in inner lpn.
509
510 PROCEDURE get_catch_wt_items
511 (x_item_lov OUT NOCOPY t_genref,
512 p_org_id IN NUMBER,
513 p_lpn_id IN NUMBER,
514 p_entry_type IN VARCHAR2,
515 p_lpn_context IN NUMBER,
516 p_concat_item_segment IN VARCHAR2);
517
518
519 -- Start of comments
520 -- API name: SHOW_CT_WT_FOR_SPLIT
521 -- Type : Private
522 -- Pre-reqs: None.
523 -- Function: This procedure holds the logic to show the catch weight
524 -- entry fields on the MObile UI or DeskTop form
525 -- If fromLPN has catchweight enabled item and ctwt qty has been
526 -- entered + TOLPN has the same item and ctwt qty is present
527 -- then show the ctwt fields.
528 -- If fromLPN has ctwt item and ctwt qty is 0 + TOLPN has same
529 -- item and ctwt qty = 0
530 -- then show the ctwt fields
531 -- Else dont show the fields
532 -- Parameters:
533 -- IN: p_org_id IN NUMBER Required
534 -- Item organization id. Part of the unique key
535 -- that uniquely identifies an item record.
536 -- p_from_delivery_id IN NUMBER Required
537 -- Delivery Detail Id of the LPN entered by user .
538 -- p_from_item_id IN NUMBER Required
539 -- Item id of the fromlpn item to be split
540 -- p_to_lpn_id IN NUMBER Required
541 -- To lpn id
542 -- OUT: x_show_ct_wt OUT NUMBER
543 -- Valid values are 1 = "YES" OR 0 = "NO"
544
545 PROCEDURE SHOW_CT_WT_FOR_SPLIT (
546 p_org_id IN NUMBER
547 , p_from_lpn_id IN NUMBER
548 , p_from_item_id IN NUMBER
549 , p_from_item_revision IN VARCHAR2
550 , p_from_item_lot_number IN VARCHAR2
551 , p_to_lpn_id IN NUMBER
552 , x_show_ct_wt OUT NOCOPY NUMBER
553 , x_return_status OUT NOCOPY VARCHAR2
554 , x_msg_data OUT NOCOPY VARCHAR2
555 , x_msg_count OUT NOCOPY NUMBER
556
557 );
558
559
560 -- Start of comments
561 -- API name: IS_CT_WT_SPLIT_VALID
562 -- Type : Private
563 -- Pre-reqs: None.
564 -- Function: This function holds the logic to validate the split of ctwt item
565 -- Based on the fm lpn, fm item, item pri qty and item sec qty
566 -- it determines if the left over ct wt is also within tolerances
567 -- Parameters:
568 -- IN: p_org_id IN NUMBER Required
569 -- Item organization id. Part of the unique key
570 -- that uniquely identifies an item record.
571 -- p_from_lpn_id IN NUMBER Required
572 -- lpn id of the from lpn entered by user .
573 -- p_from_item_id IN NUMBER Required
574 -- Item id of the fromlpn item to be split
575 -- p_from_item_revision IN VARCHAR2
576 -- p_from_item_lot_number IN VARCHAR2
577 -- p_from_item_pri_qty IN NUMBER Required
578 -- primary qty that needs to be split
579 -- p_from_item_sec_qty IN NUMBER Required
580 -- sec qty for this item/rev/lot
581 -- OUT: RETURN NUMBER
582 -- Valid values are 1 = "YES" OR 0 = "NO"
583
584 FUNCTION IS_CT_WT_SPLIT_VALID (
585 p_org_id IN NUMBER
586 , p_from_lpn_id IN NUMBER
587 , p_from_item_id IN NUMBER
588 , p_from_item_revision IN VARCHAR2
589 , p_from_item_lot_number IN VARCHAR2
590 , p_from_item_pri_qty IN NUMBER
591 , p_from_item_pri_uom IN VARCHAR2
595 , x_msg_count OUT NOCOPY NUMBER
592 , p_from_item_sec_qty IN NUMBER
593 , x_return_status OUT NOCOPY VARCHAR2
594 , x_msg_data OUT NOCOPY VARCHAR2
596 ) RETURN NUMBER;
597
598
599 -- Start of comments
600 -- API name: VALIDATE_CT_WT_FOR_DELIVERYNUM
601 -- Type : Private
602 -- Pre-reqs: None.
603 -- Function: This procedure holds the logic to validate catch weight
604 -- with in delivery number.
605 -- With the delivery number, find out all lpn (including
606 -- inner lpn's) which has ct wt enabled item and
607 -- ct wt is null. If the item attribute secondary_default_ind
608 -- for that item has
609 -- default set to NO, return error, else try to derive
610 -- sec qty for that item if it is <0 then return error
611 -- else return warning.
612 -- Parameters:
613 -- IN: p_org_id IN NUMBER Required
614 -- Item organization id. Part of the unique key
615 -- that uniquely identifies an item record.
616 -- p_delivery_number IN VARCHAR2 Required
617 -- OUT x_return_status: success, failure
618 -- RETURN 1 --> Error
619 -- 2 --> Warning
620
621 FUNCTION VALIDATE_CT_WT_FOR_DELIVERYNUM (
622 p_api_version IN NUMBER
623 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
624 , x_return_status OUT NOCOPY VARCHAR2
625 , x_msg_count OUT NOCOPY NUMBER
626 , x_msg_data OUT NOCOPY VARCHAR2
627 , p_org_id IN NUMBER
628 , p_delivery_name IN VARCHAR2
629 )RETURN NUMBER;
630
631
632 --
633 -- This function checks if an LPN for Direct Ship is eligible
634 -- for catch weight entry
635 --
636 FUNCTION check_ds_lpn (
637 p_lpn_id IN NUMBER
638 , p_organization_id IN NUMBER
639 , p_inventory_item_id IN NUMBER
640 ) RETURN VARCHAR2;
641
642
643 --
644 -- This function checks if catch wt entry is required (based on entry type)
645 --
646 FUNCTION check_wsh_lpn (
647 p_lpn_id IN NUMBER
648 , p_organization_id IN NUMBER
649 , p_inventory_item_id IN NUMBER
650 , p_entry_type IN VARCHAR2
651 ) RETURN VARCHAR2;
652
653
654 --
655 -- LOV for primary UOM (used if fulfillment base is secondary)
656 --
657 PROCEDURE get_primary_uom_class(
658 x_uoms OUT NOCOPY t_genref
659 , p_organization_id IN NUMBER
660 , p_inventory_item_id IN NUMBER
661 , p_uom_code IN VARCHAR2
662 );
663
664
665 TYPE numset_t IS TABLE OF NUMBER;
666 FUNCTION wdd_src_line_id RETURN numset_t PIPELINED;
667
668
669 FUNCTION get_uom_class(p_uom_code IN VARCHAR2)
670 RETURN VARCHAR2;
671
672
673 PROCEDURE update_lpn_tare (
674 x_return_status OUT NOCOPY VARCHAR2
675 , x_msg_count OUT NOCOPY NUMBER
676 , x_msg_data OUT NOCOPY VARCHAR2
677 , p_lpn_id IN NUMBER
678 , p_org_id IN NUMBER
679 , p_tare_weight IN NUMBER
680 , p_tare_uom_code IN VARCHAR2
681 , p_update_tare_flag IN VARCHAR2
682 );
683
684
685 END WMS_CATCH_WEIGHT_PVT;
[Home] [Help]