1 PACKAGE inv_loc_wms_utils AUTHID CURRENT_USER AS
2 /* $Header: INVLCPYS.pls 120.3.12020000.1 2012/06/26 12:13:27 appldev ship $ */
3
4 --Return values for x_retcode(standard for concurrent programs)
5
6 RETCODE_SUCCESS CONSTANT VARCHAR2(1) := '0';
7 RETCODE_WARNING CONSTANT VARCHAR2(1) := '1';
8 RETCODE_ERROR CONSTANT VARCHAR2(1) := '2';
9
10 -- This API returns the current and suggested volume, weight, and units capacity of a
11 -- given locator
12 PROCEDURE get_locator_capacity
13 ( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
14 x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
15 x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
16 x_location_maximum_units OUT NOCOPY NUMBER, -- max number of units that can be stored in locator
17 x_location_current_units OUT NOCOPY NUMBER, -- current number of units in locator
18 x_location_suggested_units OUT NOCOPY NUMBER, -- suggested number of units to be put into locator
19 x_location_available_units OUT NOCOPY NUMBER, -- number of units that can still be put into locator
20 x_location_weight_uom_code OUT NOCOPY VARCHAR2, -- the locator's unit of measure for weight
21 x_max_weight OUT NOCOPY NUMBER, -- max weight the locator can take
22 x_current_weight OUT NOCOPY NUMBER, -- current weight in the locator
23 x_suggested_weight OUT NOCOPY NUMBER, -- suggested weight to be put into locator
24 x_available_weight OUT NOCOPY NUMBER, -- weight the locator can still take
25 x_volume_uom_code OUT NOCOPY VARCHAR2, -- the locator's unit of measure for volume
26 x_max_cubic_area OUT NOCOPY NUMBER, -- max volume the locator can take
27 x_current_cubic_area OUT NOCOPY NUMBER, -- current volume in the locator
28 x_suggested_cubic_area OUT NOCOPY NUMBER, -- suggested volume to be put into locator
29 x_available_cubic_area OUT NOCOPY NUMBER, -- volume the locator can still take
30 p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
31 p_inventory_location_id IN NUMBER -- identifier of locator
32 );
33
34 -- This API only returns the current and suggested unit capacity of a
35 -- given locator
36 PROCEDURE get_locator_unit_capacity
37 ( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
38 x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
39 x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
40 x_location_maximum_units OUT NOCOPY NUMBER, -- max number of units that can be stored in locator
41 x_location_current_units OUT NOCOPY NUMBER, -- current number of units in locator
42 x_location_suggested_units OUT NOCOPY NUMBER, -- suggested number of units to be put into locator
43 x_location_available_units OUT NOCOPY NUMBER, -- number of units that can still be put into locator
44 p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
45 p_inventory_location_id IN NUMBER -- identifier of locator
46 );
47
48 -- This API only returns the current and suggested weight capacity of a
49 -- given locator
50 PROCEDURE get_locator_weight_capacity
51 ( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
52 x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
53 x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
54 x_location_weight_uom_code OUT NOCOPY VARCHAR2, -- the locator's unit of measure for weight
55 x_max_weight OUT NOCOPY NUMBER, -- max weight the locator can take
56 x_current_weight OUT NOCOPY NUMBER, -- current weight in the locator
57 x_suggested_weight OUT NOCOPY NUMBER, -- suggested weight to be put into locator
58 x_available_weight OUT NOCOPY NUMBER, -- weight the locator can still take
59 p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
60 p_inventory_location_id IN NUMBER -- identifier of locator
61 );
62
63 -- This API only returns the current and suggested volume capacity of a
64 -- given locator
65 PROCEDURE get_locator_volume_capacity
66 ( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
67 x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
68 x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
69 x_volume_uom_code OUT NOCOPY VARCHAR2, -- the locator's unit of measure for volume
70 x_max_cubic_area OUT NOCOPY NUMBER, -- max volume the locator can take
71 x_current_cubic_area OUT NOCOPY NUMBER, -- current volume in the locator
72 x_suggested_cubic_area OUT NOCOPY NUMBER, -- suggested volume to be put into locator
73 x_available_cubic_area OUT NOCOPY NUMBER, -- volume the locator can still take
74 p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
75 p_inventory_location_id IN NUMBER -- identifier of locator
76 );
77
78
79 -- This API updates the current volume, weight and units capacity of a locator when items are
80 -- issued or received in the locator
81 PROCEDURE update_loc_current_capacity
82 ( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
83 x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
84 x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
85 p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
86 p_inventory_location_id IN NUMBER, -- identifier of locator
87 p_inventory_item_id IN NUMBER, -- identifier of item
88 p_primary_uom_flag IN VARCHAR2, -- 'Y' - transaction was in item's primary UOM
89 -- 'N' - transaction was NOT in item's primary UOM
90 -- or the information is not known
91 p_transaction_uom_code IN VARCHAR2, -- UOM of the transacted material that causes the
92 -- locator capacity to get updat d
93 p_quantity IN NUMBER, -- transaction quantity in p_transaction_uom_code
94 p_issue_flag IN VARCHAR2 -- 'Y' - Issue transaction
95 -- 'N' - Receipt transaction
96 );
97
98 -- This API updates the current volume, weight and units capacity of a locator when items are
99 -- issued or received in the locator
100 -- It doesn't do an autonomous commit
101 PROCEDURE update_loc_curr_capacity_nauto
102 ( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
103 x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
104 x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
105 p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
106 p_inventory_location_id IN NUMBER, -- identifier of locator
107 p_inventory_item_id IN NUMBER, -- identifier of item
108 p_primary_uom_flag IN VARCHAR2, -- 'Y' - transaction was in item's primary UOM
109 -- 'N' - transaction was NOT in item's primary UOM
110 -- or the information is not known
111 p_transaction_uom_code IN VARCHAR2, -- UOM of the transacted material that causes the
112 -- locator capacity to get updat d
113 p_quantity IN NUMBER, -- transaction quantity in p_transaction_uom_code
114 p_issue_flag IN VARCHAR2 -- 'Y' - Issue transaction
115 -- 'N' - Receipt transaction
116 );
117
118 -- This API updates the suggested volume, weight and units capacity of a locator when items are
119 -- received in the locator
120 -- all suggestions are receipt suggestions
121 PROCEDURE update_loc_suggested_capacity
122 ( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
123 x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
124 x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
125 p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
126 p_inventory_location_id IN NUMBER, -- identifier of locator
127 p_inventory_item_id IN NUMBER, -- identifier of item
128 p_primary_uom_flag IN VARCHAR2, -- 'Y' - transaction was in item's primary UOM
129 -- 'N' - transaction was NOT in item's primary UOM
130 -- or the information is not known
131 p_transaction_uom_code IN VARCHAR2, -- UOM of the transacted material that causes the
132 -- locator capacity to get updated
133 -- Note: can be NULL if p_primary_uom_flag = 'Y'
134 p_quantity IN NUMBER -- transaction quantity in p_transaction_uom_code
135
136 );
137
138 -- This API updates the suggested volume, weight and units capacity of a locator when
139 -- drop off locator is suggested.
140 -- THIS API DOES NOT UPDATE EMPTY FLAG OF THE LOCATOR.
141
142 PROCEDURE update_loc_sugg_cap_wo_empf
143 ( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
144 x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
145 x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
146 p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
147 p_inventory_location_id IN NUMBER, -- identifier of locator
148 p_inventory_item_id IN NUMBER, -- identifier of item
149 p_primary_uom_flag IN VARCHAR2, -- 'Y' - transaction was in item's primary UOM
150 -- 'N' - transaction was NOT in item's primary UOM
151 -- or the information is not known
152 p_transaction_uom_code IN VARCHAR2, -- UOM of the transacted material that causes the
153 -- locator capacity to get updated
154 -- Note: can be NULL if p_primary_uom_flag = 'Y'
155 p_quantity IN NUMBER -- transaction quantity in p_transaction_uom_code
156
157 );
158
159
160 -- This API updates the suggested volume, weight and units capacity of a locator when items are
161 -- received in the locator.
162 -- all suggestions are receipt suggestions
163 -- It doesn't do an autonomous commit.
164 PROCEDURE update_loc_sugg_capacity_nauto
165 ( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
166 x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
167 x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
168 p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
169 p_inventory_location_id IN NUMBER, -- identifier of locator
170 p_inventory_item_id IN NUMBER, -- identifier of item
171 p_primary_uom_flag IN VARCHAR2, -- 'Y' - transaction was in item's primary UOM
172 -- 'N' - transaction was NOT in item's primary UOM
173 -- or the information is not known
174 p_transaction_uom_code IN VARCHAR2, -- UOM of the transacted material that causes the
175 -- locator capacity to get updated
176 -- Note: can be NULL if p_primary_uom_flag = 'Y'
177 p_quantity IN NUMBER -- transaction quantity in p_transaction_uom_code
178 );
179
180 -- This API reverts the updates of the suggested volume, weight and units capacity of a locator
181 -- when a receipt happens.
182 -- In other words, this API can be considered as issue suggestions.
183 PROCEDURE revert_loc_suggested_capacity
184 ( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
185 x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
186 x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
187 p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
188 p_inventory_location_id IN NUMBER, -- identifier of locator
189 p_inventory_item_id IN NUMBER, -- identifier of item
190 p_primary_uom_flag IN VARCHAR2, -- 'Y' - transaction was in item's primary UOM
191 -- 'N' - transaction was NOT in item's primary UOM
192 -- or the information is not known
193 p_transaction_uom_code IN VARCHAR2, -- UOM of the transacted material that causes the
194 -- locator capacity to get updated
195 -- Note: can be NULL if p_primary_uom_flag = 'Y'
196 p_quantity IN NUMBER, -- transaction quantity in p_transaction_uom_code
197 p_content_lpn_id IN NUMBER DEFAULT NUlL --bug#9159019 FPing fix for #8944467
198 );
199
200
201 -- This API reverts the updates of the suggested volume, weight and units capacity of a locator
202 -- when a receipt happens.
203 -- In other words, this API can be considered as issue suggestions.
204 -- THIS API DOES NOT HAVE AUTONOMOUS COMMIT
205 PROCEDURE revert_loc_suggested_cap_nauto
206 ( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
207 x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
208 x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
209 p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
210 p_inventory_location_id IN NUMBER, -- identifier of locator
211 p_inventory_item_id IN NUMBER, -- identifier of item
212 p_primary_uom_flag IN VARCHAR2, -- 'Y' - transaction was in item's primary UOM
213 -- 'N' - transaction was NOT in item's primary UOM
214 -- or the information is not known
215 p_transaction_uom_code IN VARCHAR2, -- UOM of the transacted material that causes the
216 -- locator capacity to get updated
217 -- Note: can be NULL if p_primary_uom_flag = 'Y'
218 p_quantity IN NUMBER, -- transaction quantity in p_transaction_uom_code
219 p_content_lpn_id IN NUMBER DEFAULT NUlL --bug#9159019 FPing fix for #8944467
220 );
221
222 -- This is an upgrade script that updates the locator's capacity information corresponding to
223 -- each onhand quantity record
227 ,x_msg_data OUT NOCOPY VARCHAR2 -- message text when x_msg_count>0
224 PROCEDURE locators_capacity_cleanup
225 ( x_return_status OUT NOCOPY VARCHAR2 -- return status (success/error/unexpected_error)
226 ,x_msg_count OUT NOCOPY NUMBER -- number of messages in the message queue
228 ,p_organization_id IN NUMBER
229 ,p_mixed_flag IN VARCHAR2 DEFAULT NULL
230 ,p_subinventory IN VARCHAR2 DEFAULT NULL
231 ,p_locator_id IN NUMBER DEFAULT NULL
232 );
233
234 -- This procedure initiates the upgrade thru concurrent request
235
236 PROCEDURE launch_upgrade(
237 x_errorbuf OUT NOCOPY VARCHAR2
238 ,x_retcode OUT NOCOPY VARCHAR2
239 ,p_organization_id IN NUMBER
240 ,p_subinventory IN VARCHAR2 DEFAULT NULL
241 ,p_mixed_items_flag IN NUMBER DEFAULT NULL
242 );
243
244 PROCEDURE print_message(dummy IN VARCHAR2 DEFAULT NULL);
245
246 -- This API updates the current volume, weight capacity of a locator considering the LPN's
247 -- weight and volume
248
249 -- bug#2876849. Added the two new parameters from org id and from loc id. These are needed
250 -- for a transfer transaction to decrement the capacity from the souce locator.
251 PROCEDURE update_lpn_loc_curr_capacity
252 ( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
253 x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
254 x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
255 p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
256 p_inventory_location_id IN NUMBER, -- identifier of locator
257 p_inventory_item_id IN NUMBER, -- identifier of item
258 p_primary_uom_FLAG IN VARCHAR2, -- iF Y primary UOM
259 p_transaction_uom_code IN VARCHAR2, -- UOM of the transacted material that causes the
260 -- locator capacity to get updated
261 p_transaction_action_id IN NUMBER, -- transaction action id for pack,unpack,issue,receive,
262 -- transfer
263 p_lpn_id IN NUMBER, -- lpn id
264 p_transfer_lpn_id IN NUMBER, -- transfer_lpn_id
265 p_content_lpn_id IN NUMBER, -- content_lpn_id
266 p_quantity IN NUMBER, -- transaction quantity in p_transaction_uom_code
267 p_container_item_id IN NUMBER DEFAULT NULL,
268 p_cartonization_id IN NUMBER DEFAULT NULL,
269 p_from_org_id IN NUMBER DEFAULT NULL,
270 p_from_loc_id IN NUMBER DEFAULT NULL
271 );
272
273 -- This API updates the current volume, weight capacity of a locator considering the LPN's
274 -- weight and volume .This does not do an autonomous commit
275
276 -- bug#2876849. Added the two new parameters from org id and from loc id. These are needed
277 -- for a transfer transaction to decrement the capacity from the souce locator.
278 PROCEDURE upd_lpn_loc_curr_cpty_nauto
279 ( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
280 x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
281 x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
282 p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
283 p_inventory_location_id IN NUMBER, -- identifier of locator
284 p_inventory_item_id IN NUMBER, -- identifier of item
285 p_primary_uom_FLAG IN VARCHAR2, -- iF Y primary UOM
286 p_transaction_uom_code IN VARCHAR2, -- UOM of the transacted material that causes the
287 -- locator capacity to get updated
288 p_transaction_action_id IN NUMBER, -- transaction action id for pack,unpack,issue,receive,
289 -- transfer
290 p_lpn_id IN NUMBER, -- lpn id
291 p_transfer_lpn_id IN NUMBER, -- transfer_lpn_id
292 p_content_lpn_id IN NUMBER, -- content_lpn_id
293 p_quantity IN NUMBER, -- transaction quantity in p_transaction_uom_code
294 p_container_item_id IN NUMBER DEFAULT NULL,
295 p_cartonization_id IN NUMBER DEFAULT NULL,
296 p_from_org_id IN NUMBER DEFAULT NULL,
297 p_from_loc_id IN NUMBER DEFAULT NULL
298 );
299 -- This is an upgrade script that updates the locator's capacity information considering the LPn's weight and volume
300 PROCEDURE lpn_loc_capacity_clean_up(x_return_status OUT NOCOPY VARCHAR2 --return status
301 ,x_msg_count OUT NOCOPY NUMBER --number of messages in message queue
302 ,x_msg_data OUT NOCOPY VARCHAR2 --message text when x_msg_count>0
303 ,p_organization_id IN NUMBER -- identier for the organization
304 ,p_mixed_flag IN VARCHAR2 DEFAULT NULL
305 ) ;
306 --This API is used to fetch the attributes of the item which is used
307 -- in calculation of locator capacity updation considering the LPN's weight and volume
308
309 PROCEDURE item_attributes(
310 x_return_status OUT NOCOPY VARCHAR2,--return status
311 x_msg_data OUT NOCOPY VARCHAR2,
312 x_msg_count OUT NOCOPY NUMBER, --Count of messages in the Message queue
313 x_item_weight_uom_code OUT NOCOPY VARCHAR2,--Item's weight UOM_code
317 x_item_xacted_weight OUT NOCOPY NUMBER, --Transacted weight of item
314 x_item_unit_weight OUT NOCOPY NUMBER, --Item's unit weight
315 x_item_volume_uom_code OUT NOCOPY VARCHAR2,--Item's Volume UOM_Code
316 x_item_unit_volume OUT NOCOPY NUMBER, --Item's unit volume
318 x_item_xacted_volume OUT NOCOPY NUMBER, --Transacted volume of item
319 p_inventory_item_id IN NUMBER, -- Identifier of Item
320 p_transaction_uom_code IN VARCHAR2 default null,--UOM of the transacted material
321 p_primary_uom_flag IN VARCHAR2 default null,--Y if Primary_UOM
322 p_locator_weight_uom_code IN VARCHAR2 default null,--Locator's weight_UOM_Code
323 p_locator_volume_uom_code IN VARCHAR2 default null,--Locator's Volume_UOM_Code
324 p_quantity IN NUMBER default null,--Transaction quantity
325 p_organization_id IN NUMBER, -- Identier of the Organization
326 p_container_item IN VARCHAR2 DEFAULT 'N'--Flag which indicates the item passed
327 -- is a container item.
328 --Y if the item is a container item
329 -- N if the item is not a container item
330 );
331
332 --This API is used to fetch the attributes of the LPN which is used
333 -- in calculation of locator capacity updation considering the LPN's weight and volume
334 PROCEDURE lpn_attributes (
335 x_return_status OUT NOCOPY VARCHAR2, -- Return status
336 x_msg_data OUT NOCOPY VARCHAR2, --message text when x_msg_count>0
337 x_msg_count OUT NOCOPY NUMBER, -- Count iof message in the message queue
338 x_gross_weight_uom_code OUT NOCOPY VARCHAR2, --Gross_Weight_UOM_Code of the LPN
339 x_content_volume_uom_code OUT NOCOPY VARCHAR2, --Content_Volume_UOM_Code of the LPN
340 x_gross_weight OUT NOCOPY NUMBER, --Gross Weight of the LPN
341 x_content_volume OUT NOCOPY NUMBER, --Content_Volume of the LPN
342 x_container_item_weight OUT NOCOPY NUMBER, -- Container item's weight (in terms of Gross_weight_UOM_code)
343 -- associated with the LPN
344 x_container_item_vol OUT NOCOPY NUMBER, -- Container item Volume's (in terms of Content_Volume_UOM_code)
345 -- associated with the LPN
346 x_lpn_exists_in_locator OUT NOCOPY VARCHAR2, --Flag indicates if Transfer LPN exists in Locator.
347 -- Y if the Transfer LPN exists in locator.
348 -- N if the Transfer LPN does not exists in locator
349 p_lpn_id IN NUMBER, --Identifier of the LPN
350 p_org_id IN NUMBER --Identifier of the Organization
351 ) ;
352
353 --This API is used to calculate the Locator's Current_Cubic_Area which is used
354 -- in calculation of locator capacity updation considering the LPN's weight and volume
355
356 PROCEDURE cal_locator_current_volume(
357 x_return_status OUT NOCOPY VARCHAR2,--return status
358 x_msg_data OUT NOCOPY VARCHAR2,--message text when x_msg_count>0
359 x_msg_count OUT NOCOPY NUMBER,--number of messages in message queue
360 x_locator_current_volume OUT NOCOPY NUMBER,--locator's current_cubic_area
361 p_trn_lpn_container_item_vol IN NUMBER,--container item volume associated with transfer LPN
362 p_trn_lpn_content_volume IN NUMBER,--Content volume of the Transfer LPN
363 p_cnt_lpn_container_item_vol IN NUMBER,--container item volume associated with content LPN
364 p_cnt_lpn_content_volume IN NUMBER,--Content volume of the Content LPN
365 p_lpn_container_item_vol IN NUMBER,--container item volume associated with LPN
366 p_lpn_content_volume IN NUMBER,--Content volume of the LPN
367 p_xacted_volume IN NUMBER,-- Transacted volume
368 p_locator_current_cubic_area IN NUMBER,--locator's current_cubic_area
369 p_transaction_action_id IN NUMBER,-- transaction action id for pack,unpack,issue,receive,Transfer
370 p_transfer_lpn_id IN NUMBER,--Transfer_LPN_ID
371 p_content_lpn_id IN NUMBER,--Content LPN_ID
372 p_lpn_id IN NUMBER,--LPN_ID
373 p_trn_lpn_exists_in_loc IN VARCHAR2--Flag indicates if Transfer LPN exists in Locator.
374 -- Y if the Transfer LPN exists in locator.
375 -- N if the Transfer LPN does not exists in locator
376 );
377 -- This API updates the empty flag,Mixed_Items_Flag and the inventory_item_id column in MTL_ITEM_LOCATIONS table
378 -- No autonomous commit
379 PROCEDURE LOC_EMPTY_MIXED_FLAG( X_RETURN_STATUS OUT NOCOPY VARCHAR2
380 ,X_MSG_COUNT OUT NOCOPY NUMBER
381 ,X_MSG_DATA OUT NOCOPY VARCHAR2
382 ,P_ORGANIZATION_ID IN NUMBER
383 ,P_INVENTORY_LOCATION_ID IN NUMBER
384 ,P_INVENTORY_ITEM_ID IN NUMBER
385 ,P_TRANSACTION_ACTION_ID IN NUMBER
386 ,P_TRANSFER_ORGANIZATION IN NUMBER
387 ,P_TRANSFER_LOCATION_ID IN NUMBER
391 -- for issue transactions
388 ,P_SOURCE IN VARCHAR2 DEFAULT NULL
389 );
390 -- This API returns values for empty_flag,Mixed_Items_Flag and the inventory_item_id column in MTL_ITEM_LOCATIONS table
392 procedure inv_loc_issues (x_return_status OUT NOCOPY VARCHAR2
393 ,X_MSG_COUNT OUT NOCOPY NUMBER
394 ,X_MSG_DATA OUT NOCOPY VARCHAR2
395 ,X_EMPTY_FLAG OUT NOCOPY VARCHAR2
396 ,X_MIXED_FLAG OUT NOCOPY VARCHAR2
397 ,X_ITEM_ID OUT NOCOPY NUMBER
398 ,P_ORG_ID IN NUMBER
399 ,P_LOCATOR_ID IN NUMBER
400 ,P_INVENTORY_ITEM_ID IN NUMBER
401 ,P_SOURCE IN VARCHAR2 DEFAULT NULL
402 );
403 -- This API returns values for empty_flag,Mixed_Items_Flag and the inventory_item_id column in MTL_ITEM_LOCATIONS table
404 -- for receipt transactions
405 procedure inv_loc_receipt (x_return_status OUT NOCOPY VARCHAR2
406 ,X_MSG_COUNT OUT NOCOPY NUMBER
407 ,X_MSG_DATA OUT NOCOPY VARCHAR2
408 ,X_EMPTY_FLAG OUT NOCOPY VARCHAR2
409 ,X_MIXED_FLAG OUT NOCOPY VARCHAR2
410 ,X_ITEM_ID OUT NOCOPY NUMBER
411 ,P_LOCATOR_ID IN NUMBER
412 ,P_ORG_ID IN NUMBER
413 ,P_INVENTORY_ITEM_ID IN NUMBER
414 );
415
416 PROCEDURE lpn_loc_cleanup_mmtt(x_return_status OUT NOCOPY varchar2 --return status
417 ,x_msg_count OUT NOCOPY NUMBER --number of messages in message queue
418 ,x_msg_data OUT NOCOPY varchar2 --message text when x_msg_count>0
419 ,p_organization_id IN NUMBER -- identier for the organization
420 ,p_mixed_flag IN VARCHAR2 DEFAULT NULL
421 );
422 PROCEDURE LPN_LOC_CURRENT_CAPACITY (
423 x_return_status OUT NOCOPY varchar2 --return status
424 ,x_msg_count OUT NOCOPY NUMBER --number of messages in message queue
425 ,x_msg_data OUT NOCOPY varchar2 --message text when x_msg_count>0
426 ,p_organization_id IN NUMBER -- identier for the organization
427 ,p_mixed_flag IN VARCHAR2 DEFAULT NULL
428 ) ;
429
430 -- This API updates the empty flag,Mixed_Items_Flag and the inventory_item_id column in MTL_ITEM_LOCATIONS table
431 -- This does an autonomous commit.The P_source parameter will have a value Null when called from the database trigger and
432 -- will have 'CONCURRENT' when called from the Launch Upgrade concurrent program.
433 PROCEDURE LOC_EMPTY_MIXED_FLAG_AUTO( X_RETURN_STATUS OUT NOCOPY VARCHAR2
434 ,X_MSG_COUNT OUT NOCOPY NUMBER
435 ,X_MSG_DATA OUT NOCOPY VARCHAR2
436 ,P_ORGANIZATION_ID IN NUMBER
437 ,P_INVENTORY_LOCATION_ID IN NUMBER
438 ,P_INVENTORY_ITEM_ID IN NUMBER
439 ,P_TRANSACTION_ACTION_ID IN NUMBER
440 ,P_TRANSFER_ORGANIZATION IN NUMBER
441 ,P_TRANSFER_LOCATION_ID IN NUMBER
442 ,P_SOURCE IN VARCHAR2 DEFAULT NULL
443 );
444
445 procedure upd_empty_mixed_flag_rcv_loc ( x_return_status OUT NOCOPY VARCHAR2
446 ,x_msg_count OUT NOCOPY NUMBER
447 ,x_msg_data OUT NOCOPY VARCHAR2
448 ,p_subinventory IN VARCHAR2
449 ,p_locator_id IN NUMBER
450 ,p_org_id IN NUMBER
451 );
452
453 TYPE LocatorRec IS RECORD (
454 l_locator_weight_uom_code VARCHAR2(3),
455 l_locator_volume_uom_code VARCHAR2(3),
456 l_locator_max_weight NUMBER,
457 l_locator_suggested_weight NUMBER := 0,
458 l_locator_suggested_cubic_area NUMBER := 0,
459 l_locator_current_weight NUMBER := 0,
460 l_locator_available_weight NUMBER,
461 l_locator_max_cubic_area NUMBER,
462 l_locator_current_cubic_area NUMBER := 0,
463 l_locator_available_cubic_area NUMBER,
464 l_locator_available_units NUMBER,
465 l_locator_current_units NUMBER := 0,
466 l_locator_suggested_units NUMBER := 0,
467 l_locator_maximum_units NUMBER
468 );
469
470 TYPE ItemRec IS RECORD (
471 l_item_primary_uom_code VARCHAR2(3),
472 l_item_weight_uom_code VARCHAR2(3),
473 l_item_unit_weight NUMBER := 0,
474 l_item_volume_uom_code VARCHAR2(3),
475 l_item_unit_volume NUMBER := 0,
476 l_item_xacted_weight NUMBER := 0,
477 l_item_xacted_volume NUMBER := 0
478 );
479
480 TYPE LpnRec IS RECORD (
484 l_content_volume NUMBER := 0,
481 l_gross_weight_uom_code varchar2(3),
482 l_content_volume_uom_code varchar2(3),
483 l_gross_weight NUMBER := 0,
485 l_container_item_weight NUMBER := 0,
486 l_container_item_vol NUMBER := 0,
487 l_organization_id NUMBER,
488 l_locator_id NUMBER,
489 l_lpn_exists_in_locator VARCHAR2(1)
490 );
491
492 --Added following procedure for bug #6523490
493 procedure get_locator_id ( x_locator_id OUT NOCOPY NUMBER ,
494 p_locator IN VARCHAR2 ,
495 p_org_id IN NUMBER );
496
497 -- 8721026
498 PROCEDURE get_source_type
499 ( x_source OUT NOCOPY VARCHAR2,
500 p_locator_id IN NUMBER,
501 p_organization_id IN NUMBER,
502 p_inventory_item_id IN NUMBER,
503 p_content_lpn_id IN NUMBER,
504 p_transaction_action_id IN NUMBER,
505 p_primary_quantity IN NUMBER
506 );
507
508
509
510 END inv_loc_wms_utils;
511
[Home] [Help]