1 PACKAGE INV_ITEM_INQ AS
2 /* $Header: INVIQWMS.pls 120.0 2005/05/25 06:32:00 appldev noship $ */
3
4
5 TYPE t_genref IS REF CURSOR;
6
7 FUNCTION get_status_code (
8 p_status_id mtl_material_statuses_vl.status_id%TYPE
9 ) RETURN VARCHAR2 ;
10
11 PROCEDURE INV_ITEM_INQUIRIES (
12 x_item_inquiries OUT NOCOPY t_genref,
13 p_Organization_Id IN NUMBER,
14 p_Inventory_Item_Id IN NUMBER DEFAULT NULL,
15 p_Revision IN VARCHAR2 DEFAULT NULL,
16 p_Lot_Number IN VARCHAR2 DEFAULT NULL,
17 p_Subinventory_Code IN VARCHAR2 DEFAULT NULL,
18 p_Locator_Id IN NUMBER DEFAULT NULL,
19 x_Status OUT NOCOPY VARCHAR2,
20 x_Message OUT NOCOPY VARCHAR2);
21
22 PROCEDURE WMS_LOOSE_ITEM_INQUIRIES (
23 x_item_inquiries OUT NOCOPY t_genref,
24 p_organization_id IN NUMBER,
25 p_inventory_item_id IN NUMBER DEFAULT NULL,
26 p_revision IN VARCHAR2 DEFAULT NULL,
27 p_lot_number IN VARCHAR2 DEFAULT NULL,
28 p_subinventory_code IN VARCHAR2 DEFAULT NULL,
29 p_locator_id IN NUMBER DEFAULT NULL,
30 p_cost_Group_id IN NUMBER DEFAULT NULL,
31 x_status OUT NOCOPY VARCHAR2,
32 x_message OUT NOCOPY VARCHAR2);
33
34 PROCEDURE INV_SERIAL_INQUIRIES (
35 x_serial_inquiries OUT NOCOPY t_genref,
36 p_Organization_Id IN NUMBER,
37 p_Serial_Number IN VARCHAR2 DEFAULT NULL,
38 p_Inventory_Item_Id IN NUMBER DEFAULT NULL,
39 p_Revision IN VARCHAR2 DEFAULT NULL,
40 p_Lot_Number IN VARCHAR2 DEFAULT NULL,
41 p_Subinventory_Code IN VARCHAR2 DEFAULT NULL,
42 p_Locator_Id IN NUMBER DEFAULT NULL,
43 x_Status OUT NOCOPY VARCHAR2,
44 x_Message OUT NOCOPY VARCHAR2);
45
46 PROCEDURE WMS_LOOSE_SERIAL_INQUIRIES (
47 x_serial_inquiries OUT NOCOPY t_genref,
48 p_Organization_Id IN NUMBER,
49 p_Serial_Number IN VARCHAR2 DEFAULT NULL,
50 p_Inventory_Item_Id IN NUMBER DEFAULT NULL,
51 p_Revision IN VARCHAR2 DEFAULT NULL,
52 p_Lot_Number IN VARCHAR2 DEFAULT NULL,
53 p_Subinventory_Code IN VARCHAR2 DEFAULT NULL,
54 p_Locator_Id IN NUMBER DEFAULT NULL,
55 p_cost_Group_id IN NUMBER DEFAULT NULL,
56 x_Status OUT NOCOPY VARCHAR2,
57 x_Message OUT NOCOPY VARCHAR2);
58
59
60 -- Added an extra parameter p_lpn_context_id as part of Bug 2091699 and defaulting it
61 PROCEDURE LOT_ATTRIBUTES(
62 x_lot_attributes OUT NOCOPY t_genref,
63 p_lot_number IN VARCHAR2,
64 p_organization_id IN NUMBER,
65 p_inventory_item_id IN NUMBER,
66 p_lpn_context_id IN NUMBER DEFAULT 0); -- Bug 2091699
67
68 -- Added an extra parameter p_lpn_context_id as part of Bug 2091699 and defaulting it
69 PROCEDURE SERIAL_ATTRIBUTES(
70 x_serial_attributes OUT NOCOPY t_genref,
71 p_serial_number IN VARCHAR2,
72 p_organization_id IN NUMBER,
73 p_inventory_item_id IN NUMBER,
74 p_lpn_context_id IN NUMBER DEFAULT 0);
75
76 PROCEDURE GET_SERIAL_NUMBER(
77 x_serialLOV OUT NOCOPY t_genref,
78 p_organization_id IN NUMBER,
79 p_inventory_item_id IN NUMBER,
80 p_serial_number IN VARCHAR2);
81
82 PROCEDURE Get_Serial_Number_Inq(
83 x_serialLOV OUT NOCOPY t_genref,
84 p_organization_id IN NUMBER,
85 p_inventory_item_id IN NUMBER,
86 p_serial_number in VARCHAR2);
87
88 PROCEDURE Get_Serial_Number_RcptTrx(
89 x_serialLOV OUT NOCOPY t_genref,
90 p_organization_id IN NUMBER,
91 p_inventory_item_id IN NUMBER,
92 p_serial_number in VARCHAR2,
93 p_transactiontypeid in NUMBER);
94
95
96
97 PROCEDURE get_pup_SERIAL_NUMBER(
98 x_serialLOV OUT NOCOPY t_genref,
99 p_organization_id IN NUMBER,
100 p_inventory_item_id IN NUMBER,
101 p_serial_number IN VARCHAR2,
102 p_txn_type_id IN NUMBER := 0,
103 p_wms_installed IN VARCHAR2 :='TRUE');
104
105
106 PROCEDURE get_serial_lov(x_serial_number OUT NOCOPY t_genref,
107 p_organization_id IN NUMBER,
108 p_item_id IN VARCHAR2,
109 p_serial IN VARCHAR2);
110
111
112 PROCEDURE SELECT_SERIAL_NUMBER(
113 x_serial_numbers OUT NOCOPY t_genref,
114 p_organization_id IN NUMBER,
115 p_inventory_item_id IN NUMBER,
116 p_revision IN VARCHAR2,
117 p_subinventory_code IN VARCHAR2,
118 p_locator_id IN NUMBER,
119 p_cost_Group_id IN NUMBER,
120 p_lot_number IN VARCHAR2);
121
122 -- Added by Amy (qxliu) Sept. 20, 2001
123 -- Overloaded procedure to find serial numbers in a LPN
124 -- Added an extra parameter p_lpn_context_id as part of Bug 2091699 and defaulting it
125 PROCEDURE SELECT_SERIAL_NUMBER(
126 x_serial_numbers OUT NOCOPY t_genref,
127 p_organization_id IN NUMBER,
128 p_inventory_item_id IN NUMBER,
129 p_lot_number IN VARCHAR2,
130 p_lpn_id IN NUMBER,
131 p_lpn_context_id IN NUMBER DEFAULT 0,
132 p_revision IN VARCHAR2);
133
134 PROCEDURE GET_LPN_CONTENTS(
135 x_lpn_contents OUT NOCOPY t_genref,
136 p_parent_lpn_id IN NUMBER);
137
138 PROCEDURE GET_LPN_FOR_ITEM(
139 x_lpn_for_item OUT NOCOPY t_genref
140 , p_organization_id IN NUMBER
141 , p_inventory_item_id IN NUMBER
142 , p_subinventory_code IN VARCHAR2
143 , p_locator_id IN NUMBER
144 , p_lot_number IN VARCHAR2
145 , p_serial_number IN VARCHAR2
146 , p_revision IN VARCHAR2
147 , p_cost_group_id IN NUMBER);
148
149
150 FUNCTION GET_AVAILABLE_QTY (
151 p_Organization_Id IN NUMBER,
152 p_Inventory_Item_Id IN NUMBER,
153 p_Revision IN VARCHAR2,
154 p_Subinventory_Code IN VARCHAR2,
155 p_Locator_Id IN NUMBER,
156 p_Lot_Number IN VARCHAR2,
157 p_cost_group_id IN NUMBER,
158 p_revision_control IN VARCHAR2 ,
159 p_lot_control IN VARCHAR2,
160 p_serial_control IN VARCHAR2)RETURN NUMBER;
161
162 FUNCTION GET_PACKED_QUANTITY (
163 p_Organization_Id IN NUMBER,
164 p_Inventory_Item_Id IN NUMBER,
165 p_Revision IN VARCHAR2,
166 p_Subinventory_Code IN VARCHAR2,
167 p_Locator_Id IN NUMBER,
168 p_Lot_Number IN VARCHAR2,
169 p_cost_group IN NUMBER) RETURN NUMBER;
170
171 FUNCTION GET_LOOSE_QUANTITY (
172 p_Organization_Id IN NUMBER,
173 p_Inventory_Item_Id IN NUMBER,
174 p_Revision IN VARCHAR2,
175 p_Subinventory_Code IN VARCHAR2,
176 p_Locator_Id IN NUMBER,
177 p_Lot_Number IN VARCHAR2,
178 p_cost_group IN NUMBER) RETURN NUMBER;
179
180 -- INVCONV start
181
182 PROCEDURE GET_PACKED_QTY(p_organization_id IN NUMBER,
183 p_inventory_item_id IN NUMBER,
184 p_revision IN VARCHAR2,
185 p_subinventory_code IN VARCHAR2,
186 p_locator_id IN NUMBER,
187 p_lot_number IN VARCHAR2,
188 p_cost_Group IN NUMBER,
189 x_packed_qty OUT NOCOPY NUMBER,
190 x_sec_packed_qty OUT NOCOPY NUMBER);
191
192
193 PROCEDURE GET_LOOSE_QTY(p_organization_id IN NUMBER,
194 p_inventory_item_id IN NUMBER,
195 p_revision IN VARCHAR2,
196 p_subinventory_code IN VARCHAR2,
197 p_locator_id IN NUMBER,
198 p_lot_number IN VARCHAR2,
199 p_cost_Group IN NUMBER,
200 x_loose_qty OUT NOCOPY NUMBER,
201 x_sec_loose_qty OUT NOCOPY NUMBER);
202
203 PROCEDURE GET_PACKED_LOOSE_QTY(p_organization_id IN NUMBER,
204 p_inventory_item_id IN NUMBER,
205 p_revision IN VARCHAR2,
206 p_subinventory_code IN VARCHAR2,
207 p_locator_id IN NUMBER,
208 p_lot_number IN VARCHAR2,
209 p_cost_Group IN NUMBER,
210 x_packed_qty OUT NOCOPY NUMBER,
211 x_loose_qty OUT NOCOPY NUMBER,
212 x_sec_packed_qty OUT NOCOPY NUMBER,
213 x_sec_loose_qty OUT NOCOPY NUMBER);
214
215 -- INVCONV end
216
217 --UPDATE_QUANTITY
218 -- Used to update the quantity tree. This is a wrapper procedure
219 -- for the INV_QUANITY_TREE_PUB.update_quantities procedure. This
220 -- procedure should only be used for inventory transactions (not
221 -- reservations). Also, if you are transacting against an already
222 -- existing reservation, please use the API in INV_QUANITY_TREE_PUB.a
223 --
224 -- Please Note: Calling this function will NOT update any inventory
225 -- tables. You must update the tables yourself.
226 --
227 -- Please pass 1 for p_containerized if quantity being transacted is
228 -- inside a container; 0 otherwise.
229 --
230 -- X_qoh is the quantity on hand for the item in the location you pass in;
231 -- X_att is the available quantity (Onhand - reservations - suggestions)
232
233 PROCEDURE UPDATE_QUANTITY(
234 p_organization_id IN NUMBER
235 , p_inventory_item_id IN NUMBER
236 , p_revision IN VARCHAR2 DEFAULT NULL
237 , p_lot_number IN VARCHAR2 DEFAULT NULL
238 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
239 , p_locator_id IN NUMBER DEFAULT NULL
240 , p_cost_group_id IN NUMBER DEFAULT NULL
241 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
242 , p_primary_quantity IN NUMBER
243 , p_containerized IN NUMBER
244 , x_qoh OUT NOCOPY NUMBER
245 , x_att OUT NOCOPY NUMBER
246 , x_return_status OUT NOCOPY VARCHAR2
247 , x_msg_count OUT NOCOPY NUMBER
248 , x_msg_data OUT NOCOPY VARCHAR2
249 );
250
251 --Overloaded procedure (for filtering on project and task)
252 PROCEDURE INV_ITEM_INQUIRIES (
253 x_item_inquiries OUT NOCOPY t_genref,
254 p_Organization_Id IN NUMBER,
255 p_Inventory_Item_Id IN NUMBER DEFAULT NULL,
256 p_Revision IN VARCHAR2 DEFAULT NULL,
257 p_Lot_Number IN VARCHAR2 DEFAULT NULL,
258 p_Subinventory_Code IN VARCHAR2 DEFAULT NULL,
259 p_Locator_Id IN NUMBER DEFAULT NULL,
260 p_project_id IN NUMBER DEFAULT NULL,
261 p_task_id IN NUMBER DEFAULT NULL,
262 x_Status OUT NOCOPY VARCHAR2,
263 x_Message OUT NOCOPY VARCHAR2);
264
265 --Overloaded procedure (for filtering on project and task)
266 PROCEDURE WMS_LOOSE_ITEM_INQUIRIES (
267 x_item_inquiries OUT NOCOPY t_genref,
268 p_organization_id IN NUMBER,
269 p_inventory_item_id IN NUMBER DEFAULT NULL,
270 p_revision IN VARCHAR2 DEFAULT NULL,
271 p_lot_number IN VARCHAR2 DEFAULT NULL,
272 p_subinventory_code IN VARCHAR2 DEFAULT NULL,
273 p_locator_id IN NUMBER DEFAULT NULL,
274 p_cost_Group_id IN NUMBER DEFAULT NULL,
275 p_project_id IN NUMBER DEFAULT NULL,
276 p_task_id IN NUMBER DEFAULT NULL,
277 x_status OUT NOCOPY VARCHAR2,
278 x_message OUT NOCOPY VARCHAR2);
279
280 --Overloaded procedure (for filtering on project, task and unit number)
281 PROCEDURE INV_SERIAL_INQUIRIES (
282 x_serial_inquiries OUT NOCOPY t_genref,
283 p_Organization_Id IN NUMBER,
284 p_Serial_Number IN VARCHAR2 DEFAULT NULL,
285 p_Inventory_Item_Id IN NUMBER DEFAULT NULL,
286 p_Revision IN VARCHAR2 DEFAULT NULL,
287 p_Lot_Number IN VARCHAR2 DEFAULT NULL,
288 p_Subinventory_Code IN VARCHAR2 DEFAULT NULL,
289 p_Locator_Id IN NUMBER DEFAULT NULL,
290 p_project_id IN NUMBER DEFAULT NULL,
291 p_task_id IN NUMBER DEFAULT NULL,
292 p_unit_number IN VARCHAR2 DEFAULT NULL,
293 x_Status OUT NOCOPY VARCHAR2,
294 x_Message OUT NOCOPY VARCHAR2);
295
296
297 --Overloaded procedure (for filtering on project and task)
298 PROCEDURE WMS_LOOSE_SERIAL_INQUIRIES (
299 x_serial_inquiries OUT NOCOPY t_genref,
300 p_Organization_Id IN NUMBER,
301 p_Serial_Number IN VARCHAR2 DEFAULT NULL,
302 p_Inventory_Item_Id IN NUMBER DEFAULT NULL,
303 p_Revision IN VARCHAR2 DEFAULT NULL,
304 p_Lot_Number IN VARCHAR2 DEFAULT NULL,
305 p_Subinventory_Code IN VARCHAR2 DEFAULT NULL,
306 p_Locator_Id IN NUMBER DEFAULT NULL,
307 p_cost_Group_id IN NUMBER DEFAULT NULL,
308 p_project_id IN NUMBER DEFAULT NULL,
309 p_task_id IN NUMBER DEFAULT NULL,
310 p_unit_number IN VARCHAR2 DEFAULT NULL,
311 x_Status OUT NOCOPY VARCHAR2,
312 x_Message OUT NOCOPY VARCHAR2);
313
314 --Procedure to fetch Unit Numbers for the item
315 PROCEDURE GET_UNIT_NUMBERS (
316 x_unit_numbers OUT NOCOPY t_genref,
317 p_organization_id IN NUMBER,
318 p_inventory_item_id IN NUMBER,
319 p_restrict_unit_numbers IN VARCHAR2);
320
321 /****************************************************************************
322 * Overloaded procedure to find serial numbers given a unit # and even serial #
323 * This procedure would be used when the ItemOnhandPage displays data for a
324 * Unit Number and/or a Serial Number
325 ****************************************************************************/
326 PROCEDURE SELECT_SERIAL_NUMBER(
327 x_serial_numbers OUT NOCOPY t_genref,
328 p_organization_id IN NUMBER,
329 p_inventory_item_id IN NUMBER,
330 p_revision IN VARCHAR2,
331 p_subinventory_code IN VARCHAR2,
332 p_locator_id IN NUMBER,
333 p_cost_Group_id IN NUMBER,
334 p_lot_number IN VARCHAR2,
335 p_unit_number IN VARCHAR := NULL,
336 p_serial_number IN VARCHAR2 := NULL);
337
338 --Item Inquiry based on project, task and unit number for MSCA orgs
339 PROCEDURE INV_UNIT_NUMBER_INQUIRIES (
340 x_unit_inquiries OUT NOCOPY t_genref,
341 p_Organization_Id IN NUMBER,
342 p_unit_number IN VARCHAR2 DEFAULT NULL,
343 p_Inventory_Item_Id IN NUMBER DEFAULT NULL,
344 p_Revision IN VARCHAR2 DEFAULT NULL,
345 p_Lot_Number IN VARCHAR2 DEFAULT NULL,
346 p_Subinventory_Code IN VARCHAR2 DEFAULT NULL,
347 p_Locator_Id IN NUMBER DEFAULT NULL,
348 p_project_id IN NUMBER DEFAULT NULL,
349 p_task_id IN NUMBER DEFAULT NULL,
350 x_Status OUT NOCOPY VARCHAR2,
351 x_Message OUT NOCOPY VARCHAR2);
352
353 --Item Inquiry based on project, task and unit number for WMS orgs
354 PROCEDURE WMS_UNIT_NUMBER_INQUIRIES (
355 x_unit_inquiries OUT NOCOPY t_genref,
356 p_Organization_Id IN NUMBER,
357 p_unit_number IN VARCHAR2 DEFAULT NULL,
358 p_Inventory_Item_Id IN NUMBER DEFAULT NULL,
359 p_Revision IN VARCHAR2 DEFAULT NULL,
360 p_Lot_Number IN VARCHAR2 DEFAULT NULL,
361 p_Subinventory_Code IN VARCHAR2 DEFAULT NULL,
362 p_Locator_Id IN NUMBER DEFAULT NULL,
363 p_cost_Group_id IN NUMBER DEFAULT NULL,
364 p_project_id IN NUMBER DEFAULT NULL,
365 p_task_id IN NUMBER DEFAULT NULL,
366 x_Status OUT NOCOPY VARCHAR2,
367 x_Message OUT NOCOPY VARCHAR2);
368
369 --changes for walkup loc project
370 /******************************************
371 * Obtain onhand information
372 * WMS org, provide cost group information
373 * query wms related information
374 *****************************************/
375 /* THIS PROCEDURE IS NOT BEING USED ANYWHERE */
376 PROCEDURE WMS_LOOSE_ITEM_INQUIRIES (
377 x_item_inquiries OUT NOCOPY t_genref,
378 p_Organization_Id IN NUMBER,
379 p_Inventory_Item_Id IN NUMBER DEFAULT NULL,
380 p_Subinventory_Code IN VARCHAR2 DEFAULT NULL,
381 p_Locator_Id IN NUMBER DEFAULT NULL,
382 x_Status OUT NOCOPY VARCHAR2,
383 x_Message OUT NOCOPY VARCHAR2);
384
385
386 -- INVCONV start
387 PROCEDURE GET_AVAILABLE_QTIES (p_organization_id IN NUMBER,
388 p_inventory_item_id IN NUMBER,
389 p_revision IN VARCHAR2,
390 p_subinventory_code IN VARCHAR2,
391 p_locator_id IN NUMBER,
392 p_lot_number IN VARCHAR2,
393 p_cost_group_id IN NUMBER,
394 p_revision_control IN VARCHAR2,
395 p_lot_control IN VARCHAR2,
396 p_serial_control IN VARCHAR2,
397 x_available_qty OUT NOCOPY NUMBER,
398 x_sec_available_qty OUT NOCOPY NUMBER);
399 -- INVCONV end
400
401
402
403
404 -- INVCONV, NSRIVAST, START
405 /*
406 * Overloaded procedure that calls the the update_quantity procedure
407 * with secondary quantity.
408 */
409
410 PROCEDURE UPDATE_QUANTITY (
411 p_organization_id IN NUMBER
412 , p_inventory_item_id IN NUMBER
413 , p_revision IN VARCHAR2 DEFAULT NULL
414 , p_lot_number IN VARCHAR2 DEFAULT NULL
415 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
416 , p_locator_id IN NUMBER DEFAULT NULL
417 , p_cost_group_id IN NUMBER DEFAULT NULL
418 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
419 , p_primary_quantity IN NUMBER
420 , p_containerized IN NUMBER
421 , p_secondary_quntity IN NUMBER -- INVCONV, NSRIVAST,
422 , x_qoh OUT NOCOPY NUMBER
423 , x_att OUT NOCOPY NUMBER
424 , x_return_status OUT NOCOPY VARCHAR2
425 , x_msg_count OUT NOCOPY NUMBER
426 , x_msg_data OUT NOCOPY VARCHAR2
427 ) ;
428
429
430
431 END inv_ITEM_INQ;
[Home] [Help]