[Home] [Help]
PACKAGE BODY: APPS.INV_ITEM_INQ
Source
1 PACKAGE BODY inv_ITEM_INQ AS
2 /* $Header: INVIQWMB.pls 120.15 2008/02/13 12:00:39 abaid noship $ */
3
4 FUNCTION get_status_code (
5 p_status_id mtl_material_statuses_vl.status_id%TYPE
6 ) RETURN VARCHAR2 IS
7 x_status_code mtl_material_statuses_vl.status_code%TYPE;
8 BEGIN
9 IF p_status_id IS NULL THEN
10 x_status_code := '';
11 ELSE
12 SELECT status_code
13 INTO x_status_code
14 FROM mtl_material_statuses_vl
15 WHERE status_id = p_status_id;
16 END IF;
17 return x_status_code;
18 END get_status_code;
19
20 /***************************
21 * Obtain onhand information
22 * INV org
23 **************************/
24 /* THIS PROCEDURE IS NOT BEING USED ANYWHERE */
25 PROCEDURE INV_ITEM_INQUIRIES (
26 x_item_inquiries OUT NOCOPY t_genref,
27 p_Organization_Id IN NUMBER,
28 p_Inventory_Item_Id IN NUMBER DEFAULT NULL,
29 p_Revision IN VARCHAR2 DEFAULT NULL,
30 p_Lot_Number IN VARCHAR2 DEFAULT NULL,
31 p_Subinventory_Code IN VARCHAR2 DEFAULT NULL,
32 p_Locator_Id IN NUMBER DEFAULT NULL,
33 x_Status OUT NOCOPY VARCHAR2,
34 x_Message OUT NOCOPY VARCHAR2) IS
35 BEGIN
36 OPEN x_item_inquiries FOR
37 SELECT msik.concatenated_segments, -- Item Concatenated Segments
38 moq.revision,
39 msik.description,
40 moq.subinventory_code,
41 moq.locator_id,
42 milk.concatenated_segments, -- Locator Concatenated Segments
43 moq.lot_number,
44 msik.primary_uom_code,
45 sum(nvl(moq.primary_transaction_quantity, 0)),
46 inv_ITEM_INQ.get_available_qty(
47 moq.organization_id,
48 moq.inventory_item_id,
49 moq.revision,
50 moq.subinventory_code,
51 moq.locator_id,
52 moq.lot_number,null,
53 decode(moq.revision, NULL, 'FALSE', 'TRUE'),
54 decode(msik.lot_control_code, 2, 'TRUE', 'FALSE'),
55 decode(msik.serial_number_control_code, NULL, 'FALSE', 1, 'FALSE', 'TRUE')),
56 inv_item_inq.get_status_code(msub.status_id),
57 inv_item_inq.get_status_code(milk.status_id),
58 inv_item_inq.get_status_code(mln.status_id),
59 msik.serial_number_control_code,
60 moq.cost_group_id
61
62 FROM mtl_onhand_quantities_detail moq,
63 mtl_system_items_vl msik, -- Modified for Bug # 5472330
64 mtl_item_locations_kfv milk,
65 mtl_secondary_inventories msub,
66 mtl_lot_numbers mln
67 /* mtl_serial_numbers msn Commenting for bug 1643966 as this table is not reqd. */
68 WHERE moq.organization_id = msik.organization_id
69 AND moq.inventory_item_id = msik.inventory_item_id
70 AND moq.organization_id = msub.organization_id
71 AND moq.subinventory_code = msub.secondary_inventory_name(+)
72 AND moq.organization_id = milk.organization_id(+)
73 AND moq.locator_id = milk.inventory_location_id(+)
74 AND moq.subinventory_code = milk.subinventory_code(+)
75 AND moq.organization_id = mln.organization_id(+)
76 AND moq.inventory_item_id = mln.inventory_item_id(+)
77 /* AND moq.organization_id = msn.current_organization_id(+) bug 1643966 rnrao
78 AND moq.inventory_item_id = msn.inventory_item_id(+)
79 and moq.cost_group_id = msn.cost_group_id(+)*/
80 AND moq.lot_number = mln.lot_number(+)
81 AND moq.organization_id = p_Organization_Id
82 AND moq.inventory_item_id =
83 decode (p_Inventory_Item_Id, NULL, moq.inventory_item_id, p_Inventory_Item_Id)
84 -- Bug 4301817 Not check mtl_transactions_enabled_flag to query non-transactable items
85 -- AND msik.mtl_transactions_enabled_flag = 'Y'
86 AND nvl(moq.revision, '!@#$%^&') =
87 decode(p_Revision, NULL, nvl(moq.revision, '!@#$%^&'), p_Revision)
88 AND nvl(moq.lot_number, '!@#$%^&') =
89 decode (p_Lot_Number, NULL, nvl(moq.lot_number, '!@#$%^&'), p_Lot_Number)
90 AND nvl(moq.subinventory_code, '!@#$%^&') =
91 decode (p_Subinventory_Code, NULL, nvl(moq.subinventory_code, '!@#$%^&'), p_Subinventory_Code)
92 AND nvl(moq.locator_id, 0) =
93 decode(p_Locator_Id, NULL, nvl(moq.locator_id, 0), p_Locator_Id)
94 GROUP BY moq.organization_id, moq.inventory_item_id,
95 msik.concatenated_segments, moq.revision, msik.description,
96 moq.subinventory_code, moq.locator_id, milk.concatenated_segments,
97 moq.lot_number, msik.primary_uom_code,
98 inv_item_inq.get_available_qty(
99 moq.organization_id,
100 moq.inventory_item_id,
101 moq.revision,
102 moq.subinventory_code,
103 moq.locator_id,
104 moq.lot_number, null,
105 decode(moq.revision, NULL, 'FALSE', 'TRUE'),
106 decode(msik.lot_control_code, 2, 'TRUE', 'FALSE'),
107 decode(msik.serial_number_control_code, NULL, 'FALSE', 1, 'FALSE', 'TRUE')),
108 inv_item_inq.get_status_code(msub.status_id),
109 inv_item_inq.get_status_code(milk.status_id),
110 inv_item_inq.get_status_code(mln.status_id),
111 msik.serial_number_control_code,
112 moq.cost_group_id;
113
114 x_status := 'C';
115 x_message := 'Records found';
116 END INV_ITEM_INQUIRIES;
117
118 /******************************************
119 * Obtain onhand information
120 * WMS org, provide cost group information
121 * query wms related information
122 *****************************************/
123 /* THIS PROCEDURE IS NOT BEING USED ANYWHERE */
124 PROCEDURE WMS_LOOSE_ITEM_INQUIRIES (
125 x_item_inquiries OUT NOCOPY t_genref,
126 p_Organization_Id IN NUMBER,
127 p_Inventory_Item_Id IN NUMBER DEFAULT NULL,
128 p_Revision IN VARCHAR2 DEFAULT NULL,
129 p_Lot_Number IN VARCHAR2 DEFAULT NULL,
130 p_Subinventory_Code IN VARCHAR2 DEFAULT NULL,
131 p_Locator_Id IN NUMBER DEFAULT NULL,
132 p_cost_Group_id IN NUMBER DEFAULT NULL,
133 x_Status OUT NOCOPY VARCHAR2,
134 x_Message OUT NOCOPY VARCHAR2) IS
135 BEGIN
136 OPEN x_item_inquiries FOR
137 SELECT b.msik_concatenated_segments,
138 b.revision,
139 b.description,
140 b.subinventory_code,
141 b.subinventory_status,
142 b.locator_id,
143 b.milk_concatenated_segments,
144 b.locator_status,
145 b.cost_group_id,
146 b.cost_group,
147 b.lot_number,
148 b.lot_status,
149 b.primary_uom_code,
150 b.sum_txn_qty,
151 inv_item_inq.get_available_qty(
152 b.organization_id,
153 b.inventory_item_id,
154 b.revision,
155 b.subinventory_code,
156 b.locator_id,
157 b.lot_number,
158 b.cost_group_id,
159 decode(b.revision, NULL, 'FALSE', 'TRUE'),
160 decode(b.lot_control_code, 2, 'TRUE', 'FALSE'),
161 decode(b.serial_number_control_code, NULL, 'FALSE', 1, 'FALSE', 'TRUE')),
162 inv_item_inq.get_packed_quantity(
163 b.organization_id, b.inventory_item_id, b.revision,
164 b.subinventory_code, b.locator_id, b.lot_number, b.cost_Group_id),
165 inv_item_inq.get_loose_quantity(
166 b.organization_id, b.inventory_item_id, b.revision,
167 b.subinventory_code, b.locator_id, b.lot_number, b.cost_Group_id),
168 b.serial_number_control_code
169 FROM
170 (SELECT moq.organization_id organization_id,
171 moq.inventory_item_id inventory_item_id,
172 msik.concatenated_segments msik_concatenated_segments,
173 moq.revision revision,
174 msik.description description,
175 moq.subinventory_code subinventory_code,
176 mms1.status_code subinventory_status,
177 moq.locator_id locator_id,
178 milk.concatenated_segments milk_concatenated_segments,
179 mms2.status_code locator_status,
180 moq.cost_group_id cost_group_id,
181 csg.cost_group cost_group,
182 moq.lot_number lot_number,
183 mms3.status_code lot_status,
184 msik.primary_uom_code primary_uom_code,
185 sum(nvl(moq.primary_transaction_quantity, 0)) sum_txn_qty,
186 msik.lot_control_code lot_control_code,
187 msik.serial_number_control_code serial_number_control_code
188 FROM mtl_onhand_quantities_detail moq,
189 mtl_system_items_vl msik, -- Modified for Bug # 5472330
190 mtl_item_locations_kfv milk,
191 mtl_secondary_inventories msub,
192 mtl_lot_numbers mlot,
193 mtl_material_statuses_vl mms1,
194 mtl_material_statuses_vl mms2,
195 mtl_material_statuses_vl mms3,
196 cst_cost_groups csg
197 WHERE moq.organization_id = msik.organization_id
198 AND moq.inventory_item_id = msik.inventory_item_id
199 AND moq.organization_id = msub.organization_id
200 AND moq.subinventory_code = msub.secondary_inventory_name(+)
201 AND msub.status_id = mms1.status_id(+)
202 AND moq.organization_id = milk.organization_id
203 AND moq.locator_id = milk.inventory_location_id(+)
204 aND milk.status_id = mms2.status_id(+)
205 AND moq.subinventory_code = milk.subinventory_code(+)
206 AND moq.lot_number = mlot.lot_number(+)
207 AND moq.inventory_item_id = mlot.inventory_item_id(+)
208 ANd moq.organization_id = mlot.organization_id(+)
209 AND mlot.status_id = mms3.status_id(+)
210 AND moq.cost_group_id = csg.cost_group_id(+)
211 -- AND moq.organization_id = csg.organization_id(+)
212 AND moq.organization_id = p_Organization_Id
213 AND moq.inventory_item_id =
214 decode (p_Inventory_Item_Id, NULL, moq.inventory_item_id, p_Inventory_Item_Id)
215 -- Bug 4301817 Not check mtl_transactions_enabled_flag to query non-transactable items
216 -- AND msik.mtl_transactions_enabled_flag = 'Y'
217 AND nvl(moq.revision, '!@#$%^&') =
218 decode(p_Revision, NULL, nvl(moq.revision, '!@#$%^&'), p_Revision)
219 AND nvl(moq.lot_number, '!@#$%^&') =
220 decode (p_Lot_Number, NULL, nvl(moq.lot_number, '!@#$%^&'), p_Lot_Number)
221 AND nvl(moq.subinventory_code, '!@#$%^&') =
222 decode (p_Subinventory_Code, NULL, nvl(moq.subinventory_code, '!@#$%^&'), p_Subinventory_Code)
223 AND nvl(moq.locator_id, 0) =
224 decode(p_Locator_Id, NULL, nvl(moq.locator_id, 0), p_Locator_Id)
225 AND nvl(moq.cost_group_id, 0) =
226 decode(p_cost_group_id, NULL, nvl(moq.cost_group_id, 0), p_cost_group_id)
227 GROUP BY moq.organization_id,
228 moq.inventory_item_id,
229 msik.concatenated_segments,
230 moq.revision,
231 msik.description,
232 moq.subinventory_code,
233 mms1.status_code,
234 moq.locator_id,
235 milk.concatenated_segments,
236 mms2.status_code,
237 moq.cost_group_id,
238 csg.cost_group,
239 moq.lot_number,
240 mms3.status_code,
241 msik.primary_uom_code,
242 msik.lot_control_code,
243 msik.serial_number_control_code) b;
244 x_status := 'C';
245 x_message := 'Records found';
246 EXCEPTION
247 when others then
248 x_status := 'E';
249 -- x_message := SUBSTR (SQLERRM , 1 , 240);
250 x_message := 'System error in select statement';
251 END WMS_LOOSE_ITEM_INQUIRIES;
252
253 /******************************************
254 * Query for Inv org, giving serial number
255 *****************************************/
256 /* THIS PROCEDURE IS NOT BEING USED ANYWHERE */
257 PROCEDURE INV_SERIAL_INQUIRIES (
258 x_serial_inquiries OUT NOCOPY t_genref,
259 p_Organization_Id IN NUMBER,
260 p_Serial_Number IN VARCHAR2 DEFAULT NULL,
261 p_Inventory_Item_Id IN NUMBER DEFAULT NULL,
262 p_Revision IN VARCHAR2 DEFAULT NULL,
263 p_Lot_Number IN VARCHAR2 DEFAULT NULL,
264 p_Subinventory_Code IN VARCHAR2 DEFAULT NULL,
265 p_Locator_Id IN NUMBER DEFAULT NULL,
266 x_Status OUT NOCOPY VARCHAR2,
267 x_Message OUT NOCOPY VARCHAR2) IS
268 BEGIN
269 OPEN x_serial_inquiries FOR
270 SELECT msik.concatenated_segments, -- Item Concatenated Segments
271 msn.revision,
272 msik.description,
273 msn.current_subinventory_code,
274 msn.current_locator_id,
275 milk.concatenated_segments, -- Locator Concatenated Segments
276 msn.lot_number,
277 msn.serial_number,
278 msik.primary_uom_code,
279 1
280 FROM MTL_SERIAL_NUMBERS msn,
281 MTL_SYSTEM_ITEMS_VL msik, /* Bug 5581528 */
282 MTL_ITEM_LOCATIONS_KFV milk
283 WHERE milk.organization_id(+) = msn.current_organization_id
284 AND milk.subinventory_code(+) = msn.current_subinventory_code
285 AND milk.inventory_location_id(+) = msn.current_locator_id
286 AND msn.inventory_item_id = msik.inventory_item_id
287 AND msn.current_organization_id = msik.organization_id
288 AND msik.organization_id = p_Organization_Id
289 -- Bug 4301817 Not check mtl_transactions_enabled_flag to query non-transactable items
290 -- AND msik.mtl_transactions_enabled_flag = 'Y'
291 AND msn.serial_number =
292 decode(p_Serial_Number, NULL, msn.serial_number, p_Serial_Number)
293 AND msn.inventory_item_id = p_Inventory_Item_Id
294 AND nvl(msn.revision, '!@#$%^&') =
295 decode(p_Revision, NULL, nvl(msn.revision, '!@#$%^&'), p_Revision)
296 AND nvl(msn.current_subinventory_code, '!@#$%^&') =
297 decode(p_Subinventory_Code, NULL, nvl(msn.current_subinventory_code, '!@#$%^&'), p_Subinventory_Code)
298 AND nvl(msn.current_locator_id, 99999999) =
299 decode(p_Locator_Id, NULL, nvl(msn.current_locator_id, 99999999), p_Locator_Id)
300 AND nvl(msn.lot_number, '!@#$%^&') =
301 decode(p_Lot_Number, NULL, nvl(msn.lot_number, '!@#$%^&'), p_Lot_Number);
302
303 x_status := 'C';
304 x_message := 'Records found';
305 END INV_SERIAL_INQUIRIES;
306
307 /******************************************
308 * Query for WMS org, giving serial number
309 *****************************************/
310
311 PROCEDURE WMS_LOOSE_SERIAL_INQUIRIES (
312 x_serial_inquiries OUT NOCOPY t_genref,
313 p_Organization_Id IN NUMBER,
314 p_Serial_Number IN VARCHAR2 DEFAULT NULL,
315 p_Inventory_Item_Id IN NUMBER DEFAULT NULL,
316 p_Revision IN VARCHAR2 DEFAULT NULL,
317 p_Lot_Number IN VARCHAR2 DEFAULT NULL,
318 p_Subinventory_Code IN VARCHAR2 DEFAULT NULL,
319 p_Locator_Id IN NUMBER DEFAULT NULL,
323 BEGIN
320 p_cost_Group_id IN NUMBER DEFAULT NULL,
321 x_Status OUT NOCOPY VARCHAR2,
322 x_Message OUT NOCOPY VARCHAR2) IS
324 OPEN x_serial_inquiries FOR
325 SELECT msik.concatenated_segments, -- Item Concatenated Segments
326 msn.revision,
327 msik.description,
328 msn.current_subinventory_code,
329 mms1.status_code subinventory_status,
330 msn.current_locator_id,
331 milk.concatenated_segments, -- Locator Concatenated Segments
332 mms2.status_code locator_status,
333 msn.cost_group_id,
334 csg.cost_group,
335 msn.lot_number,
336 mms3.status_code lot_status,
337 msn.serial_number,
338 mms4.status_code serial_status,
339 msik.primary_uom_code,
340 1
341 FROM MTL_SERIAL_NUMBERS msn,
342 MTL_SYSTEM_ITEMS_VL msik, /* Bug 5581528 */
343 MTL_ITEM_LOCATIONS_KFV milk,
344 MTL_SECONDARY_INVENTORIES msub,
345 MTL_LOT_NUMBERS mlot,
346 MTL_MATERIAL_STATUSES_vl mms1,
347 MTL_MATERIAL_STATUSES_vl mms2,
348 MTL_MATERIAL_STATUSES_vl mms3,
349 MTL_MATERIAL_STATUSES_vl mms4,
350 CST_COST_GROUPS csg
351 WHERE milk.organization_id(+) = msn.current_organization_id
352 AND milk.subinventory_code(+) = msn.current_subinventory_code
353 AND milk.inventory_location_id(+) = msn.current_locator_id
354 AND milk.status_id = mms2.status_id(+)
355 AND msn.inventory_item_id = msik.inventory_item_id
356 AND msn.current_organization_id = msik.organization_id
357 AND msn.current_subinventory_code = msub.secondary_inventory_name(+)
358 AND msn.current_organization_id = msub.organization_id(+)
359 AND msub.status_id = mms1.status_id(+)
360 AND msn.cost_group_id = csg.cost_group_id(+)
361 AND msn.lot_number = mlot.lot_number (+)
362 AND msn.current_organization_id = mlot.organization_id(+)
363 AND msn.inventory_item_id = mlot.inventory_item_id(+)
364 AND mlot.status_id = mms3.status_id(+)
365 AND msn.status_id = mms4.status_id(+)
366 AND msik.organization_id = p_Organization_Id
367 -- Bug 4301817 Not check mtl_transactions_enabled_flag to query non-transactable items
368 -- AND msik.mtl_transactions_enabled_flag = 'Y'
369 AND msn.serial_number =
370 decode(p_Serial_Number, NULL, msn.serial_number, p_Serial_Number)
371 AND msn.inventory_item_id = p_Inventory_Item_Id
372 AND nvl(msn.revision, '!@#$%^&') =
373 decode(p_Revision, NULL, nvl(msn.revision, '!@#$%^&'), p_Revision)
374 AND nvl(msn.current_subinventory_code, '!@#$%^&') =
375 decode(p_Subinventory_Code, NULL, nvl(msn.current_subinventory_code, '!@#$%^&'), p_Subinventory_Code)
376 AND nvl(msn.current_locator_id, 99999999) =
377 decode(p_Locator_Id, NULL, nvl(msn.current_locator_id, 99999999), p_Locator_Id)
378 AND nvl(msn.cost_group_id, 99999999) =
379 decode(p_cost_Group_id, NULL, nvl(msn.cost_group_id, 99999999), p_cost_group_id)
380 AND nvl(msn.lot_number, '!@#$%^&') =
381 decode(p_Lot_Number, NULL, nvl(msn.lot_number, '!@#$%^&'), p_Lot_Number);
382
383 x_status := 'C';
384 x_message := 'Records found';
385 EXCEPTION
386 when others then
387 x_status := 'E';
388 x_message := 'System error in select statement';
389 END WMS_LOOSE_SERIAL_INQUIRIES;
390
391
392 /****************************************************************************
393 This procedure gets the contents for a given lpn
394 30.1.2002. Changed by venjayar
395 To account for the contents of the LPN in Packing Context also
396 (as part of the bug 2091699)
397 4.4.2002 Changed by venjayar
398 To fetch project and task information also for Loaded LPN since
399 Locator and Sub are fetched. (as part of bug 2314495)
400 ****************************************************************************/
401
402 /* Changes for Bug #2810546
403 * a) Removed outer join between MFG_LOOKUPS and WMS_LICENSE_PLATE_NUMBERS since
404 * an LPN will always have a context associated
405 * b) Forked the code to fetch the dock door if the LPN context is "Loaded to Dock(9)"
406 * For this join between wms_license_plate_numbers, wms_shipping_transactions_temp
407 * and mtl_item_locations.
408 * d) For other LPN contexts, we do not need dock door information. So removed the
409 * join with wms_shipping_transactions_temp and mtl_item_locations (milk2).
410 * e) Use the cached values for PROJECT_NUMBER and TASK_NUMBER instead of fetching
411 * them using project_id and task_id
412 */
413 PROCEDURE GET_LPN_CONTENTS(
414 x_lpn_contents OUT NOCOPY t_genref,
415 p_parent_lpn_id IN NUMBER)
416 IS
417 l_count NUMBER;
418 l_lpn_context_id NUMBER;
419 BEGIN
420
421 SELECT lpn_context INTO l_lpn_context_id
422 FROM wms_license_plate_numbers
423 WHERE lpn_id = p_parent_lpn_id;
424
425 /********************************************************************************
426 * The formation of cursor is different when the LPN Context is Packing Context.
427 * 1) If the LPN Context is Packing then the tables MTL_MATERIAL_TRANSACTIONS_TEMP
428 * and MTL_TRANSACTION_LOTS_TEMPare used to get the required information.
429 * 2) For all other types of LPN Context WMS_LPN_CONTENTS is used.
430 * Bug #4191414 - Modifications to showing the contents for packing context LPNs
431 * The cursor is split in to two select statements
432 * 1. The first SQL fetches the content information for not lot controlled items
433 * by fetching the data from MMTT
434 * 2. The second SQL fetches the content information for each allocated lot
435 * by joining MMTT and MTLT
436 *******************************************************************************/
437
438 IF(l_lpn_context_id = 8) THEN
439 --For non-lot controlled items
440 OPEN x_lpn_contents FOR
441 SELECT
442 mmtt.content_lpn_id ,
443 mmtt.transfer_lpn_id ,
444 lpn.license_plate_number ,
445 mlk.meaning ,
446 mmtt.inventory_item_id ,
447 msiv.concatenated_segments ,
448 msiv.description,
449 mmtt.organization_id ,
450 mp.organization_code ,
451 mmtt.revision,
452 mmtt.subinventory_code ,
453 mmtt.locator_id,
454 INV_PROJECT.GET_LOCSEGS(mlc.inventory_location_id,mlc.organization_id) ,
455 to_char(NULL) lot_number,
456 to_char(NULL) serial_number,
457 mmtt.transaction_quantity,
458 mmtt.transaction_uom,
459 nvl(mmtt.cost_group_id, 0),
460 ccg.cost_group,
461 lpn.outermost_lpn_id,
462 lpn3.license_plate_number ,
463 inv_item_inq.get_status_code(msub.status_id),
464 inv_item_inq.get_status_code(milk.status_id),
465 to_char(NULL), --Lot Status
466 lpn.lpn_context,
467 to_char(NULL), --Dock Door segs
468 msiv.serial_number_control_code,
469 INV_PROJECT.GET_PROJECT_NUMBER, --Project Number
470 INV_PROJECT.GET_TASK_NUMBER, --Task Number
471 to_char(NULL), --Source Name
472 -- INVCONV start
473 NVL(msiv.tracking_quantity_ind, 'P'),
474 msiv.secondary_uom_code,
475 NVL(mmtt.secondary_transaction_quantity, 0),
476 -- INVCONV end
477 --lpn status project start
478 NVL(mmtt.lpn_id,mmtt.content_lpn_id)
479 --lpn status project end
480 FROM mtl_material_transactions_temp mmtt,
481 wms_license_plate_numbers lpn,
482 wms_license_plate_numbers lpn3,
483 mtl_parameters mp,
484 cst_cost_groups ccg,
485 mtl_item_locations_kfv mlc ,
486 mtl_secondary_inventories msub,
487 mtl_item_locations_kfv milk,
488 mtl_system_items_vl msiv, /* Bug 5581528 */
489 mfg_lookups mlk
490 WHERE mmtt.transfer_lpn_id = p_parent_lpn_id
491 AND lpn.lpn_id = mmtt.transfer_lpn_id
492 AND lpn.organization_id = mp.organization_id
493 AND lpn.outermost_lpn_id = lpn3.lpn_id
494 AND mmtt.cost_group_id = ccg.cost_group_id(+)
495 AND mmtt.organization_id = mlc.organization_id(+)
496 AND mmtt.locator_id = mlc.inventory_location_id(+)
497 AND mmtt.organization_id = msub.organization_id(+)
498 AND mmtt.subinventory_code = msub.secondary_inventory_name(+)
502 AND mmtt.organization_id = msiv.organization_id
499 AND mmtt.organization_id = milk.organization_id(+)
500 AND mmtt.locator_id = milk.inventory_location_id(+)
501 AND mmtt.subinventory_code = milk.subinventory_code(+)
503 AND mmtt.inventory_item_id = msiv.inventory_item_id
504 AND mmtt.inventory_item_id is not null
505 AND mlk.lookup_type = 'WMS_LPN_CONTEXT'
506 AND mlk.lookup_code = lpn.lpn_context
507 AND msiv.lot_control_code = 1
508 UNION
509 --For Lot controlled items
510 SELECT
511 mmtt.content_lpn_id ,
512 mmtt.transfer_lpn_id ,
513 lpn.license_plate_number ,
514 mlk.meaning ,
515 mmtt.inventory_item_id ,
516 msiv.concatenated_segments ,
517 msiv.description,
518 mmtt.organization_id ,
519 mp.organization_code ,
520 mmtt.revision,
521 mmtt.subinventory_code ,
522 mmtt.locator_id,
523 INV_PROJECT.GET_LOCSEGS(mlc.inventory_location_id, mlc.organization_id),
524 mtlt.lot_number,
525 to_char(NULL) serial_number,
526 mtLt.transaction_quantity, --Get qty for each lot
527 mmtt.transaction_uom,
528 nvl(mmtt.cost_group_id, 0),
529 ccg.cost_group,
530 lpn.outermost_lpn_id, lpn3.license_plate_number ,
531 inv_item_inq.get_status_code(msub.status_id),
532 inv_item_inq.get_status_code(milk.status_id),
533 inv_item_inq.get_status_code(mln.status_id),
534 lpn.lpn_context,
535 to_char(NULL), --Dock Door segs
536 msiv.serial_number_control_code,
537 TO_CHAR(NULL), --Project Number
538 TO_CHAR(NULL), --Task Number
539 to_char(NULL), --Source Name
540 -- INVCONV start
541 NVL(msiv.tracking_quantity_ind, 'P'),
542 msiv.secondary_uom_code,
543 NVL(mmtt.secondary_transaction_quantity, 0),
544 -- INVCONV end
545 --lpn status project
546 NVL(mmtt.lpn_id,mmtt.content_lpn_id)
547 --lpn status project end
548
549 FROM mtl_material_transactions_temp mmtt,
550 mtl_transaction_lots_temp mtlt,
551 wms_license_plate_numbers lpn,
552 wms_license_plate_numbers lpn3,
553 mtl_parameters mp,
554 cst_cost_groups ccg,
555 mtl_item_locations_kfv mlc ,
556 mtl_secondary_inventories msub,
557 mtl_item_locations_kfv milk,
558 mtl_lot_numbers mln,
559 mtl_system_items_vl msiv, /* Bug 5581528 */
560 mfg_lookups mlk
561 WHERE mmtt.transfer_lpn_id = p_parent_lpn_id
562 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
563 AND lpn.lpn_id = mmtt.transfer_lpn_id
564 AND lpn.organization_id = mp.organization_id
565 AND lpn.outermost_lpn_id = lpn3.lpn_id
566 AND mmtt.cost_group_id = ccg.cost_group_id(+)
567 AND mmtt.organization_id = mlc.organization_id(+)
568 AND mmtt.locator_id = mlc.inventory_location_id(+)
569 AND mmtt.organization_id = msub.organization_id(+)
570 AND mmtt.subinventory_code = msub.secondary_inventory_name(+)
571 AND mmtt.organization_id = milk.organization_id(+)
572 AND mmtt.locator_id = milk.inventory_location_id(+)
573 AND mmtt.subinventory_code = milk.subinventory_code(+)
574 AND mmtt.organization_id = mln.organization_id(+)
575 AND mmtt.inventory_item_id = mln.inventory_item_id(+)
576 AND mmtt.lot_number = mln.lot_number(+)
577 AND mmtt.organization_id = msiv.organization_id
578 AND mmtt.inventory_item_id = msiv.inventory_item_id
579 AND mmtt.inventory_item_id is not null
580 AND mlk.lookup_type = 'WMS_LPN_CONTEXT'
581 AND mlk.lookup_code = lpn.lpn_context
582 AND msiv.lot_control_code > 1;
583
584 /* LPN Context = "Loaded to Dock" - fetch the dock door information */
585 ELSIF (l_lpn_context_id = 9) THEN
586 OPEN x_lpn_contents FOR
587 SELECT
588 wlc.lpn_content_id , wlc.parent_lpn_id , lpn.license_plate_number ,
589 mlk.meaning,
590 wlc.inventory_item_id, msiv.concatenated_segments, msiv.description,
591 wlc.organization_id , mp.organization_code ,
592 wlc.revision,
593 lpn.subinventory_code ,
594 lpn.locator_id, INV_PROJECT.GET_LOCSEGS(mlc.inventory_location_id,mlc.organization_id) ,
595 wlc.lot_number, wlc.serial_number,
596 wlc.quantity, wlc.uom_code,
597 nvl(wlc.cost_group_id, 0), ccg.cost_group,
598 lpn.outermost_lpn_id, lpn3.license_plate_number ,
599 inv_item_inq.get_status_code(msub.status_id) ,
600 inv_item_inq.get_status_code(milk.status_id) ,
601 inv_item_inq.get_status_code(mln.status_id) ,
602 lpn.lpn_context,
603 INV_PROJECT.GET_LOCSEGS(milk2.inventory_location_id, milk2.organization_id) ,
604 msiv.serial_number_control_code,
605 INV_PROJECT.GET_PROJECT_NUMBER(mlc.project_id),
606 INV_PROJECT.GET_TASK_NUMBER(mlc.task_id),
610 msiv.secondary_uom_code,
607 wlc.source_name,
608 -- INVCONV start
609 NVL(msiv.tracking_quantity_ind, 'P'),
611 NVL(wlc.secondary_quantity, 0),
612 -- INVCONV end
613 --lpn status project
614 wlc.parent_lpn_id
615 --lpn status project end
616
617 FROM
618 wms_lpn_contents wlc,
619 wms_license_plate_numbers lpn,
620 wms_license_plate_numbers lpn3,
621 mtl_parameters mp,
622 cst_cost_groups ccg,
623 mtl_item_locations mlc ,
624 mtl_secondary_inventories msub,
625 mtl_item_locations milk,
626 mtl_item_locations milk2,
627 mtl_lot_numbers mln,
628 mtl_system_items_vl msiv, /* Bug 5581528 */
629 mfg_lookups mlk,
630 wms_shipping_transaction_temp wstt
631 WHERE wlc.parent_lpn_id = p_parent_lpn_id
632 AND lpn.lpn_id = wlc.parent_lpn_id
633 AND lpn.organization_id = mp.organization_id
634 AND lpn.outermost_lpn_id = lpn3.lpn_id
635 AND wlc.cost_group_id = ccg.cost_group_id(+)
636 AND lpn.organization_id = mlc.organization_id(+)
637 AND lpn.locator_id = mlc.inventory_location_id(+)
638 AND lpn.organization_id = msub.organization_id(+)
639 AND lpn.subinventory_code = msub.secondary_inventory_name(+)
640 AND lpn.organization_id = milk.organization_id(+)
641 AND lpn.locator_id = milk.inventory_location_id(+)
642 AND lpn.subinventory_code = milk.subinventory_code(+)
643 AND wlc.organization_id = mln.organization_id(+)
644 AND wlc.inventory_item_id = mln.inventory_item_id(+)
645 AND wlc.lot_number = mln.lot_number(+)
646 AND lpn.organization_id = msiv.organization_id
647 AND wlc.inventory_item_id = msiv.inventory_item_id
648 AND wlc.inventory_item_id is not null
649 AND mlk.lookup_type = 'WMS_LPN_CONTEXT'
650 AND mlk.lookup_code = lpn.lpn_context
651 AND lpn.lpn_id = wstt.parent_lpn_id (+)
652 AND wstt.dock_door_id = milk2.inventory_location_id (+)
653 AND milk2.inventory_location_type(+) = 1
654
655 UNION ALL
656
657 SELECT
658 wlc.lpn_content_id , wlc.parent_lpn_id , lpn.license_plate_number ,
659 mlk.meaning,
660 0, null, wlc.item_description,
661 wlc.organization_id , mp.organization_code ,
662 wlc.revision,
663 lpn.subinventory_code ,
664 lpn.locator_id, INV_PROJECT.GET_LOCSEGS(mlc.inventory_location_id, mlc.organization_id),
665 null, null,
666 wlc.quantity, wlc.uom_code,
667 nvl(wlc.cost_group_id, 0), ccg.cost_group,
668 lpn.outermost_lpn_id, lpn3.license_plate_number ,
669 null, null, null,
670 lpn.lpn_context,
671 INV_PROJECT.GET_LOCSEGS(milk2.inventory_location_id, milk2.organization_id),
672 0,
673 INV_PROJECT.GET_PROJECT_NUMBER(mlc.project_id),
674 INV_PROJECT.GET_TASK_NUMBER(mlc.task_id),
675 wlc.source_name,
676 -- INVCONV start
677 'P',
678 NULL,
679 NVL(wlc.secondary_quantity, 0) ,
680 -- INVCONV end
681 --lpn status project start
682 wlc.parent_lpn_id
683 --lpn status project end
684 FROM wms_lpn_contents wlc,
685 wms_license_plate_numbers lpn,
686 mtl_parameters mp,
687 wms_license_plate_numbers lpn3,
688 cst_cost_groups ccg,
689 mtl_item_locations_kfv mlc ,
690 mtl_secondary_inventories msub,
691 mtl_item_locations_kfv milk,
692 mfg_lookups mlk,
693 wms_shipping_transaction_temp wstt,
694 mtl_item_locations_kfv milk2
695 WHERE wlc.parent_lpn_id = p_parent_lpn_id
696 AND lpn.lpn_id = wlc.parent_lpn_id
697 AND lpn.organization_id = mp.organization_id
698 AND lpn.outermost_lpn_id = lpn3.lpn_id
699 AND wlc.cost_group_id = ccg.cost_group_id(+)
700 AND lpn.organization_id = mlc.organization_id(+)
701 AND lpn.locator_id = mlc.inventory_location_id(+)
702 AND lpn.organization_id = msub.organization_id(+)
703 AND lpn.subinventory_code = msub.secondary_inventory_name(+)
704 AND lpn.organization_id = milk.organization_id(+)
705 AND lpn.locator_id = milk.inventory_location_id(+)
706 AND lpn.subinventory_code = milk.subinventory_code(+)
707 AND wlc.inventory_item_id is null
708 AND mlk.lookup_type = 'WMS_LPN_CONTEXT'
709 AND mlk.lookup_code = lpn.lpn_context
710 AND lpn.lpn_id = wstt.parent_lpn_id (+)
711 AND wstt.dock_door_id = milk2.inventory_location_id (+)
712 AND milk2.inventory_location_type(+) = 1;
713 ELSE
714 /* All other Contexts */
715 OPEN x_lpn_contents FOR
716 -- Release 12 (K)
717 -- WLC can have multiple records for same item, but different UOMs
718 -- However, available quantity is not calculated for each UOM
719 -- LPN content can not show seperate record for different UOM
723 SELECT
720 -- Changed the following cursor to not to group by WLC.UOM
721 -- Instead, summarize the WLC records for each item/rev/lot across UOMs
722 -- and return primary quantity and primary UOM
724 /* 3372973 : 0 is selected instead of wlc.lpn_content_id because it is not used and moreover it is part
725 of 'group by'. */
726 0, wwlc.parent_lpn_id ,
727 lpn.license_plate_number ,
728 mlk.meaning,
729 wwlc.inventory_item_id, msiv.concatenated_segments, msiv.description,
730 wwlc.organization_id , mp.organization_code ,
731 wwlc.revision,
732 lpn.subinventory_code ,
733 lpn.locator_id,
734 INV_PROJECT.GET_LOCSEGS(mlc.inventory_location_id,mlc.organization_id) ,
735 wwlc.lot_number, wwlc.serial_number,
736 -- Release 12: change to sum of primary quantity
737 -- and select primary uom
738 -- sum(wlc.quantity), wlc.uom_code, /* 3372973 : Sum of quantity is taken as wlc is grouped */
739 wwlc.primary_quantity,
740 msiv.primary_uom_code,
741 nvl(wwlc.cost_group_id, 0), ccg.cost_group,
742 lpn.outermost_lpn_id, lpn3.license_plate_number ,
743 inv_item_inq.get_status_code(msub.status_id) ,
744 inv_item_inq.get_status_code(milk.status_id) ,
745 inv_item_inq.get_status_code(mln.status_id) ,
746 lpn.lpn_context,
747 NULL, --dock door
748 msiv.serial_number_control_code,
749 INV_PROJECT.GET_PROJECT_NUMBER, --project number
750 INV_PROJECT.GET_TASK_NUMBER, --task number
751 wwlc.source_name,
752 -- INVCONV start
753 NVL(msiv.tracking_quantity_ind, 'P'),
754 msiv.secondary_uom_code,
755 wwlc.secondary_quantity ,
756 -- INVCONV end
757 --lpn status project start
758 wwlc.parent_lpn_id
759 --lpn status project end
760 FROM
761 --Bug 4951729 Included a subquery which selects from wlc for perfomance improvement
762 (SELECT wlc.parent_lpn_id parent_lpn_id,
763 wlc.inventory_item_id inventory_item_id,
764 wlc.organization_id organization_id,
765 wlc.revision revision,
766 wlc.lot_number lot_number,
767 wlc.serial_number serial_number,
768 sum(wlc.primary_quantity) primary_quantity ,
769 nvl(wlc.cost_group_id, 0) cost_group_id,
770 wlc.source_name source_name,
771 sum(wlc.secondary_quantity) secondary_quantity
772 FROM
773 wms_lpn_contents wlc
774
775 WHERE
776 wlc.parent_lpn_id = p_parent_lpn_id
777
778
779 GROUP BY
780 wlc.parent_lpn_id ,
781 wlc.inventory_item_id,
782 wlc.organization_id ,
783 wlc.revision,
784 wlc.lot_number,
785 wlc.serial_number,
786 nvl(wlc.cost_group_id, 0),
787 wlc.source_name
788 ) wwlc,
789 wms_license_plate_numbers lpn,
790 wms_license_plate_numbers lpn3,
791 mtl_parameters mp,
792 cst_cost_groups ccg,
793 mtl_item_locations mlc ,
794 mtl_secondary_inventories msub,
795 mtl_item_locations milk,
796 mtl_lot_numbers mln,
797 mtl_system_items_vl msiv, /* Bug 5581528 */
798 mfg_lookups mlk
799 WHERE
800 lpn.lpn_id = wwlc.parent_lpn_id
801 AND lpn.organization_id = mp.organization_id
802 AND lpn.outermost_lpn_id = lpn3.lpn_id
803 AND wwlc.cost_group_id = ccg.cost_group_id(+)
804 AND lpn.organization_id = mlc.organization_id(+)
805 AND lpn.locator_id = mlc.inventory_location_id(+)
806 AND lpn.organization_id = msub.organization_id(+)
807 AND lpn.subinventory_code = msub.secondary_inventory_name(+)
808 AND lpn.organization_id = milk.organization_id(+)
809 AND lpn.locator_id = milk.inventory_location_id(+)
810 AND lpn.subinventory_code = milk.subinventory_code(+)
811 AND wwlc.organization_id = mln.organization_id(+)
812 AND wwlc.inventory_item_id = mln.inventory_item_id(+)
813 AND wwlc.lot_number = mln.lot_number(+)
814 AND lpn.organization_id = msiv.organization_id
815 AND wwlc.inventory_item_id = msiv.inventory_item_id
816 AND wwlc.inventory_item_id is not null
817 AND mlk.lookup_type = 'WMS_LPN_CONTEXT'
818 AND mlk.lookup_code = lpn.lpn_context
819 /* 3372973: Grouping has to be done because there can be multiple wlc records with same column values (except qty).
820 This can happen, for example, in Direct Org transfer of an LPN with Lot Controlled item (2 lots packed)
821 from a Source Org where the Item is Lot controlled to Dest Org where the Item is not Lot-controlled.
822 After the transfer, Org of the LPN is changed to Dest Org and 'Lot Number' is simply nulled out
823 in wlc records, which leaves multiple records in wlc with same column values.
824 Earlier in Source Org before Direct Org transfer, qty and 'Lot Number' would be having
825 different values (2 different lots packed) to make different wlc records. */
826
827 --Bug 4951729 We need to have group by only on wlc hence included a subquery for the same and commented out
828 -- group by clause .
829
830
831 /*GROUP BY
835 wlc.organization_id , mp.organization_code ,
832 0, wlc.parent_lpn_id , lpn.license_plate_number ,
833 mlk.meaning,
834 wlc.inventory_item_id, msiv.concatenated_segments, msiv.description,
836 wlc.revision,
837 lpn.subinventory_code ,
838 lpn.locator_id,
839 INV_PROJECT.GET_LOCSEGS(mlc.inventory_location_id,mlc.organization_id) ,
840 wlc.lot_number, wlc.serial_number,
841 --Release 12(K), group by primary UOM
842 --wlc.uom_code,
843 msiv.primary_uom_code,
844 nvl(wlc.cost_group_id, 0), ccg.cost_group,
845 lpn.outermost_lpn_id, lpn3.license_plate_number ,
846 inv_item_inq.get_status_code(msub.status_id) ,
847 inv_item_inq.get_status_code(milk.status_id) ,
848 inv_item_inq.get_status_code(mln.status_id) ,
849 lpn.lpn_context,
850 NULL, --dock door
851 msiv.serial_number_control_code,
852 INV_PROJECT.GET_PROJECT_NUMBER, --project number
853 INV_PROJECT.GET_TASK_NUMBER, --task number
854 wlc.source_name,
855 -- INVCONV start
856 NVL(msiv.tracking_quantity_ind, 'P'),
857 msiv.secondary_uom_code
858 -- INVCONV end*/
859
860 UNION ALL
861
862 SELECT
863 wlc.lpn_content_id , wlc.parent_lpn_id , lpn.license_plate_number ,
864 mlk.meaning,
865 0, null, wlc.item_description,
866 wlc.organization_id , mp.organization_code ,
867 wlc.revision,
868 lpn.subinventory_code ,
869 lpn.locator_id,
870 INV_PROJECT.GET_LOCSEGS(mlc.inventory_location_id, mlc.organization_id),
871 null, null,
872 wlc.quantity, wlc.uom_code,
873 nvl(wlc.cost_group_id, 0), ccg.cost_group,
874 lpn.outermost_lpn_id, lpn3.license_plate_number ,
875 null, null, null,
876 lpn.lpn_context,
877 NULL, --dock door
878 0,
879 INV_PROJECT.GET_PROJECT_NUMBER, --project number
880 INV_PROJECT.GET_TASK_NUMBER, --task number
881 wlc.source_name,
882 -- INVCONV start
883 'P',
884 NULL,
885 NVL(wlc.secondary_quantity, 0),
886 -- INVCONV end
887 --lpn status project start
888 wlc.parent_lpn_id
889 --lpn status project end
890
891 FROM wms_lpn_contents wlc,
892 wms_license_plate_numbers lpn,
893 mtl_parameters mp,
894 wms_license_plate_numbers lpn3,
895 cst_cost_groups ccg,
896 mtl_item_locations_kfv mlc ,
897 mtl_secondary_inventories msub,
898 mtl_item_locations_kfv milk,
899 mfg_lookups mlk
900 WHERE wlc.parent_lpn_id = p_parent_lpn_id
901 AND lpn.lpn_id = wlc.parent_lpn_id
902 AND lpn.organization_id = mp.organization_id
903 AND lpn.outermost_lpn_id = lpn3.lpn_id
904 AND wlc.cost_group_id = ccg.cost_group_id(+)
905 AND lpn.organization_id = mlc.organization_id(+)
906 AND lpn.locator_id = mlc.inventory_location_id(+)
907 AND lpn.organization_id = msub.organization_id(+)
908 AND lpn.subinventory_code = msub.secondary_inventory_name(+)
909 AND lpn.organization_id = milk.organization_id(+)
910 AND lpn.locator_id = milk.inventory_location_id(+)
911 AND lpn.subinventory_code = milk.subinventory_code(+)
912 AND wlc.inventory_item_id is null
913 AND mlk.lookup_type = 'WMS_LPN_CONTEXT'
914 AND mlk.lookup_code = lpn.lpn_context;
915 END IF;
916 EXCEPTION
917 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
918 null;
919 END GET_LPN_CONTENTS;
920
921
922 -----------------------------------------------------
923 -- THis procedure is to find the lpn for a given item
924 -----------------------------------------------------
925 PROCEDURE GET_LPN_FOR_ITEM(
926 x_lpn_for_item OUT NOCOPY t_genref
927 , p_organization_id IN NUMBER
928 , p_inventory_item_id IN NUMBER
929 , p_subinventory_code IN VARCHAR2
930 , p_locator_id IN NUMBER
931 , p_lot_number IN VARCHAR2
932 , p_serial_number IN VARCHAR2
933 , p_revision IN VARCHAR2
934 , p_cost_group_id IN NUMBER
935 ) IS
936
937
938 BEGIN
939 OPEN x_lpn_for_item FOR
940 SELECT wlc.lpn_content_id
941 , wlc.parent_lpn_id
942 , lpn.license_plate_number
943 , mlk.meaning
944 , wlc.inventory_item_id
945 , msiv.concatenated_segments
946 , msiv.description
947 , wlc.organization_id
948 , mp.organization_code
949 , wlc.revision
950 , lpn.subinventory_code
951 , lpn.locator_id
952 --, mlc.concatenated_segments
953 , INV_PROJECT.GET_LOCSEGS(lpn.locator_id, lpn.organization_id) concatenated_segments
954 , wlc.lot_number
955 , wlc.serial_number
956 , wlc.quantity
957 , wlc.uom_code
958 , nvl(wlc.cost_group_id, 0)
959 , ccg.cost_group
960 , lpn.outermost_lpn_id
961 , lpn3.license_plate_number
965 , lpn.gross_weight
962 , inv_item_inq.get_status_code(msub.status_id)
963 , inv_item_inq.get_status_code(mlc.status_id)
964 , inv_item_inq.get_status_code(mln.status_id)
966 , lpn.gross_weight_uom_code
967 , lpn.content_volume
968 , lpn.content_volume_uom_code
969 , msiv.serial_number_control_code
970 , INV_PROJECT.GET_PROJECT_NUMBER project_number
971 , INV_PROJECT.GET_TASK_NUMBER task_number
972 , wlc.source_name
973 -- INVCONV start
974 , NVL(msiv.tracking_quantity_ind, 'P')
975 , nvl(wlc.secondary_quantity, 0)
976 , msiv.secondary_uom_code
977 -- INVCONV end
978 FROM WMS_LPN_CONTENTS wlc
979 , WMS_LICENSE_PLATE_NUMBERS lpn
980 , mtl_system_items_vl msiv -- Modified for Bug # 5472330
981 , mtl_parameters mp
982 , wms_license_plate_numbers lpn3
983 , cst_cost_groups ccg
984 , mtl_item_locations mlc
985 , mtl_secondary_inventories msub
986 , mtl_lot_numbers mln
987 , mfg_lookups mlk
988
989 WHERE lpn.organization_id = p_organization_id
990 AND wlc.inventory_item_id = p_inventory_item_id
991 AND lpn.lpn_id = wlc.parent_lpn_id
992 AND lpn.organization_id = mp.organization_id
993 AND lpn.subinventory_code = p_subinventory_code
994 AND nvl(lpn.locator_id, 9999) = nvl(p_locator_id, 9999)
995 AND nvl(wlc.lot_number, '@@@') = nvl(p_lot_number, '@@@')
996
997 /** Bug 2392768 **/
998 --AND nvl(wlc.serial_number, '@@@') = nvl(p_serial_number, '@@@')
999
1000 AND nvl(wlc.revision, '@@@') = nvl(p_revision, '@@@')
1001 /* Bug 4731897 Modified the comparision of the cost group condition
1002 It is possible that for serial controlled item delivered from inbound,
1003 WLC.cost_group_id is NULL but MSN.cost_group_id is not null
1004 changed the where clause to match with p_cost_group_id with WLC.cost_group_id
1005 OR match p_cost_group_id with MSN.cost_group_id
1006 AND nvl(wlc.cost_group_id, 9999) = nvl(p_cost_group_id, 9999) */
1007 AND ((p_cost_group_id IS NULL) OR
1008 (wlc.cost_group_id = p_cost_group_id) OR
1009 ( ( wlc.cost_group_id IS NULL OR wlc.cost_group_id <> p_cost_group_id )AND msiv.serial_number_control_code in (2,5) AND exists
1010 (select 1 from mtl_serial_numbers msn
1011 where msn.lpn_id = wlc.parent_lpn_id
1012 and msn.cost_group_id = p_cost_group_id
1013 )
1014 )
1015 ) -- Bug 4731897
1016 --AND nvl(wlc.cost_group_id, nvl(p_cost_group_id, 9999) )= nvl(p_cost_group_id, 9999)--Bug 4731897
1017 -- Bug 4928751
1018 AND (p_serial_number IS NULL OR EXISTS (SELECT 1 FROM mtl_serial_numbers msn
1019 WHERE msn.serial_number=p_serial_number
1020 AND msn.current_organization_id=p_organization_id
1021 AND msn.lpn_id=wlc.parent_lpn_id
1022 AND msn.inventory_item_id=p_inventory_item_id ) )
1023 AND lpn.outermost_lpn_id = lpn3.lpn_id
1024 AND wlc.cost_group_id = ccg.cost_group_id(+)
1025 AND lpn.organization_id = mlc.organization_id(+)
1026 AND lpn.locator_id = mlc.inventory_location_id(+)
1027 and lpn.organization_id = msub.organization_id(+)
1028 and lpn.subinventory_code = msub.secondary_inventory_name(+)
1029 and wlc.organization_id = mln.organization_id(+)
1030 and wlc.inventory_item_id = mln.inventory_item_id(+)
1031 and wlc.lot_number = mln.lot_number(+)
1032 and lpn.organization_id = msiv.organization_id
1033 and wlc.inventory_item_id = msiv.inventory_item_id
1034
1035 and mlk.lookup_type = 'WMS_LPN_CONTEXT'
1036 and mlk.lookup_code = lpn.lpn_context(+)
1037 and not exists (select wlpn.lpn_id
1038 from wms_license_plate_numbers wlpn,
1039 mtl_material_transactions_temp t,
1040 wms_dispatched_tasks w
1041 where w.status = 4
1042 and w.task_type <> 2
1043 and w.transaction_temp_id = t.transaction_temp_id
1044 and wlpn.lpn_id = lpn.lpn_id
1045 and (t.content_lpn_id = wlpn.lpn_id)); -- #Bug 4892698
1046 -- or t.lpn_id = wlpn.lpn_id)); -- Line commented out for #Bug 4892698
1047 EXCEPTION
1048 when FND_API.G_EXC_UNEXPECTED_ERROR then
1049 null;
1050
1051 END GET_LPN_FOR_ITEM;
1052
1053 FUNCTION GET_PACKED_QUANTITY(p_organization_id IN NUMBER,
1054 p_inventory_item_id IN NUMBER,
1055 p_revision IN VARCHAR2,
1056 p_subinventory_code IN VARCHAR2,
1057 p_locator_id IN NUMBER,
1058 p_lot_number IN VARCHAR2,
1059 p_cost_Group IN NUMBER) RETURN NUMBER IS
1060 l_packed_quantity NUMBER;
1061 BEGIN
1062 select sum(quantity)
1063 into l_packed_quantity
1064 from wms_onhand_and_loaded_qty_v
1065 where organization_id = p_organization_id
1066 and inventory_item_id = decode(p_inventory_item_id, NULL, inventory_item_id, p_inventory_item_id)
1070 decode(p_subinventory_code, NULL, nvl(subinventory_code, '$@#$%'), p_subinventory_code)
1067 AND nvl(revision, '$@#$%') = decode(p_revision, NULL, nvl(revision,'$@#$%'), p_revision)
1068 AND nvl(lot_number, '$@#$%') = decode(p_lot_number, NULL, nvl(lot_number, '$@#$%'), p_lot_number)
1069 AND nvl(subinventory_code, '$@#$%') =
1071 AND nvl(locator_id, 0) = decode(p_locator_id, NULL, nvl(locator_id, 0), p_locator_id)
1072 AND nvl(cost_group_id, 0) = decode(p_cost_group, NULL, nvl(cost_group_id, 0), p_cost_group)
1073 AND nvl(containerized_flag, 2) = 1;
1074 -- group by moq.organization_id, moq.inventory_item_id, moq.revision, moq.subinventory_code,
1075 -- moq.locator_id, moq.cost_group_id, moq.lot_number, moq.cost_group_id, moq.containerized_flag;
1076 return l_packed_quantity;
1077 end;
1078
1079 FUNCTION GET_LOOSE_QUANTITY(p_organization_id IN NUMBER,
1080 p_inventory_item_id IN NUMBER,
1081 p_revision IN VARCHAR2,
1082 p_subinventory_code IN VARCHAR2,
1083 p_locator_id IN NUMBER,
1084 p_lot_number IN VARCHAR2,
1085 p_cost_Group IN NUMBER) RETURN NUMBER IS
1086 l_loose_quantity NUMBER;
1087 BEGIN
1088 select sum(quantity)
1089 into l_loose_quantity
1090 from wms_onhand_and_loaded_qty_v
1091 where organization_id = p_organization_id
1092 and inventory_item_id = decode(p_inventory_item_id, NULL, inventory_item_id, p_inventory_item_id)
1093 AND nvl(revision, '$@#$%') = decode(p_revision, NULL, nvl(revision,'$@#$%'), p_revision)
1094 AND nvl(lot_number, '$@#$%') = decode(p_lot_number, NULL, nvl(lot_number, '$@#$%'), p_lot_number)
1095 AND nvl(subinventory_code, '$@#$%') =
1096 decode(p_subinventory_code, NULL, nvl(subinventory_code, '$@#$%'), p_subinventory_code)
1097 AND nvl(locator_id, 0) = decode(p_locator_id, NULL, nvl(locator_id, 0), p_locator_id)
1098 AND nvl(cost_group_id, 0) = decode(p_cost_group, NULL, nvl(cost_group_id, 0), p_cost_group)
1099 AND nvl(containerized_flag, 2) = 2;
1100 --group by moq.organization_id, moq.inventory_item_id, moq.revision, moq.subinventory_code,
1101 -- moq.locator_id, moq.cost_group_id, moq.lot_number, moq.cost_group_id, moq.containerized_flag;
1102 return l_loose_quantity;
1103 end;
1104
1105 -- INVCONV start
1106
1107 PROCEDURE GET_PACKED_QTY(p_organization_id IN NUMBER,
1108 p_inventory_item_id IN NUMBER,
1109 p_revision IN VARCHAR2,
1110 p_subinventory_code IN VARCHAR2,
1111 p_locator_id IN NUMBER,
1112 p_lot_number IN VARCHAR2,
1113 p_cost_Group IN NUMBER,
1114 x_packed_qty OUT NOCOPY NUMBER,
1115 x_sec_packed_qty OUT NOCOPY NUMBER) IS
1116
1117 BEGIN
1118 select sum(quantity),
1119 sum(secondary_transaction_quantity)
1120 into x_packed_qty,
1121 x_sec_packed_qty
1122 from wms_onhand_and_loaded_qty_v
1123 where organization_id = p_organization_id
1124 and inventory_item_id = decode(p_inventory_item_id, NULL, inventory_item_id, p_inventory_item_id)
1125 AND nvl(revision, '$@#$%') = decode(p_revision, NULL, nvl(revision,'$@#$%'), p_revision)
1126 AND nvl(lot_number, '$@#$%') = decode(p_lot_number, NULL, nvl(lot_number, '$@#$%'), p_lot_number)
1127 AND nvl(subinventory_code, '$@#$%') =
1128 decode(p_subinventory_code, NULL, nvl(subinventory_code, '$@#$%'), p_subinventory_code)
1129 AND nvl(locator_id, 0) = decode(p_locator_id, NULL, nvl(locator_id, 0), p_locator_id)
1130 AND nvl(cost_group_id, 0) = decode(p_cost_group, NULL, nvl(cost_group_id, 0), p_cost_group)
1131 AND nvl(containerized_flag, 2) = 1;
1132 -- group by moq.organization_id, moq.inventory_item_id, moq.revision, moq.subinventory_code,
1133 -- moq.locator_id, moq.cost_group_id, moq.lot_number, moq.cost_group_id, moq.containerized_flag;
1134 END GET_PACKED_QTY;
1135
1136 PROCEDURE GET_LOOSE_QTY(p_organization_id IN NUMBER,
1137 p_inventory_item_id IN NUMBER,
1138 p_revision IN VARCHAR2,
1139 p_subinventory_code IN VARCHAR2,
1140 p_locator_id IN NUMBER,
1141 p_lot_number IN VARCHAR2,
1142 p_cost_Group IN NUMBER,
1143 x_loose_qty OUT NOCOPY NUMBER,
1144 x_sec_loose_qty OUT NOCOPY NUMBER) IS
1145
1146 BEGIN
1147 select sum(quantity),
1148 sum(secondary_transaction_quantity)
1149 into x_loose_qty,
1150 x_sec_loose_qty
1151 from wms_onhand_and_loaded_qty_v
1152 where organization_id = p_organization_id
1153 and inventory_item_id = decode(p_inventory_item_id, NULL, inventory_item_id, p_inventory_item_id)
1154 AND nvl(revision, '$@#$%') = decode(p_revision, NULL, nvl(revision,'$@#$%'), p_revision)
1155 AND nvl(lot_number, '$@#$%') = decode(p_lot_number, NULL, nvl(lot_number, '$@#$%'), p_lot_number)
1156 AND nvl(subinventory_code, '$@#$%') =
1157 decode(p_subinventory_code, NULL, nvl(subinventory_code, '$@#$%'), p_subinventory_code)
1158 AND nvl(locator_id, 0) = decode(p_locator_id, NULL, nvl(locator_id, 0), p_locator_id)
1159 AND nvl(cost_group_id, 0) = decode(p_cost_group, NULL, nvl(cost_group_id, 0), p_cost_group)
1160 AND nvl(containerized_flag, 2) = 2;
1161 --group by moq.organization_id, moq.inventory_item_id, moq.revision, moq.subinventory_code,
1165 PROCEDURE GET_PACKED_LOOSE_QTY(p_organization_id IN NUMBER,
1162 -- moq.locator_id, moq.cost_group_id, moq.lot_number, moq.cost_group_id, moq.containerized_flag;
1163 END GET_LOOSE_QTY;
1164
1166 p_inventory_item_id IN NUMBER,
1167 p_revision IN VARCHAR2,
1168 p_subinventory_code IN VARCHAR2,
1169 p_locator_id IN NUMBER,
1170 p_lot_number IN VARCHAR2,
1171 p_cost_Group IN NUMBER,
1172 x_packed_qty OUT NOCOPY NUMBER,
1173 x_loose_qty OUT NOCOPY NUMBER,
1174 x_sec_packed_qty OUT NOCOPY NUMBER,
1175 x_sec_loose_qty OUT NOCOPY NUMBER) IS
1176
1177 BEGIN
1178
1179 GET_PACKED_QTY(p_organization_id => p_organization_id,
1180 p_inventory_item_id => p_inventory_item_id ,
1181 p_revision => p_revision,
1182 p_subinventory_code => p_subinventory_code,
1183 p_locator_id => p_locator_id,
1184 p_lot_number => p_lot_number,
1185 p_cost_Group => p_cost_Group,
1186 x_packed_qty => x_packed_qty,
1187 x_sec_packed_qty => x_sec_packed_qty);
1188
1189
1190 GET_LOOSE_QTY(p_organization_id => p_organization_id,
1191 p_inventory_item_id => p_inventory_item_id ,
1192 p_revision => p_revision,
1193 p_subinventory_code => p_subinventory_code,
1194 p_locator_id => p_locator_id,
1195 p_lot_number => p_lot_number,
1196 p_cost_Group => p_cost_Group,
1197 x_loose_qty => x_loose_qty,
1198 x_sec_loose_qty => x_sec_loose_qty);
1199
1200 END GET_PACKED_LOOSE_QTY;
1201
1202 PROCEDURE GET_AVAILABLE_QTIES (p_organization_id IN NUMBER,
1203 p_inventory_item_id IN NUMBER,
1204 p_revision IN VARCHAR2,
1205 p_subinventory_code IN VARCHAR2,
1206 p_locator_id IN NUMBER,
1207 p_lot_number IN VARCHAR2,
1208 p_cost_group_id IN NUMBER,
1209 p_revision_control IN VARCHAR2,
1210 p_lot_control IN VARCHAR2,
1211 p_serial_control IN VARCHAR2,
1212 x_available_qty OUT NOCOPY NUMBER,
1213 x_sec_available_qty OUT NOCOPY NUMBER) IS
1214
1215 l_is_revision_control BOOLEAN := FALSE;
1216 l_is_lot_control BOOLEAN := FALSE;
1217 l_is_serial_control BOOLEAN := FALSE;
1218
1219 l_return_status VARCHAR2(1);
1220 l_msg_count NUMBER(10);
1221 l_msg_data VARCHAR2(1000);
1222 l_qoh NUMBER;
1223 l_rqoh NUMBER;
1224 l_qr NUMBER;
1225 l_qs NUMBER;
1226 l_atr NUMBER;
1227 l_sqoh NUMBER;
1228 l_srqoh NUMBER;
1229 l_sqr NUMBER;
1230 l_sqs NUMBER;
1231 l_satr NUMBER;
1232
1233 l_locator_id number;
1234 l_cost_group_id number;
1235 BEGIN
1236
1237
1238 -- Clearing the quantity cache
1239 inv_quantity_tree_pub.clear_quantity_cache;
1240
1241
1242 if upper(p_revision_control) = 'TRUE' then
1243 l_is_revision_control := TRUE;
1244 end if;
1245 if upper(p_lot_control) = 'TRUE' then
1246 l_is_lot_control := TRUE;
1247 end if;
1248 if upper(p_serial_control) = 'TRUE' then
1249 l_is_serial_control := TRUE;
1250 end if;
1251
1252 if p_locator_id <= 0 then
1253 l_locator_id := null;
1254 else
1255 l_locator_id := p_locator_id;
1256 end if;
1257
1258 if p_cost_group_id <= 0 then
1259 l_cost_group_id := null;
1260 else
1261 l_cost_group_id := p_cost_group_id;
1262 end if;
1263
1264 Inv_Quantity_Tree_Pub.Query_Quantities (
1265 p_api_version_number => 1.0,
1266 p_init_msg_lst => fnd_api.g_false,
1267 x_return_status => l_return_status,
1268 x_msg_count => l_msg_count,
1269 x_msg_data => l_msg_data,
1270 p_organization_id => p_organization_id,
1271 p_inventory_item_id => p_inventory_item_id,
1272 p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode,
1273 p_is_revision_control => l_is_revision_control,
1274 p_is_lot_control => l_is_lot_control,
1275 p_is_serial_control => l_is_serial_control,
1276 p_grade_code => NULL,
1277 p_revision => p_revision,
1278 p_lot_number => p_lot_number,
1279 p_subinventory_code => p_subinventory_code,
1280 p_locator_id => l_locator_id,
1281 p_cost_group_id => l_cost_group_id,
1282 x_qoh => l_qoh,
1283 x_rqoh => l_rqoh,
1284 x_qr => l_qr,
1285 x_qs => l_qs,
1286 x_att => x_available_qty,
1287 x_atr => l_atr,
1288 x_sqoh => l_sqoh,
1292 x_satt => x_sec_available_qty,
1289 x_srqoh => l_srqoh,
1290 x_sqr => l_sqr,
1291 x_sqs => l_sqs,
1293 x_satr => l_satr);
1294
1295
1296 END GET_AVAILABLE_QTIES;
1297 -- INVCONV end
1298
1299 FUNCTION GET_AVAILABLE_QTY (p_organization_id IN NUMBER,
1300 p_inventory_item_id IN NUMBER,
1301 p_revision IN VARCHAR2,
1302 p_subinventory_code IN VARCHAR2,
1303 p_locator_id IN NUMBER,
1304 p_lot_number IN VARCHAR2,
1305 p_cost_group_id IN NUMBER,
1306 p_revision_control IN VARCHAR2,
1307 p_lot_control IN VARCHAR2,
1308 p_serial_control IN VARCHAR2)
1309 RETURN NUMBER IS
1310
1311 l_is_revision_control BOOLEAN := FALSE;
1312 l_is_lot_control BOOLEAN := FALSE;
1313 l_is_serial_control BOOLEAN := FALSE;
1314
1315 l_return_status VARCHAR2(1);
1316 l_msg_count NUMBER(10);
1317 l_msg_data VARCHAR2(1000);
1318 l_qoh NUMBER;
1319 l_rqoh NUMBER;
1320 l_qr NUMBER;
1321 l_qs NUMBER;
1322 l_att NUMBER;
1323 l_atr NUMBER;
1324
1325 l_locator_id number;
1326 l_cost_group_id number;
1327 BEGIN
1328
1329 -- Clearing the quantity cache
1330 inv_quantity_tree_pub.clear_quantity_cache;
1331
1332
1333 if upper(p_revision_control) = 'TRUE' then
1334 l_is_revision_control := TRUE;
1335 end if;
1336 if upper(p_lot_control) = 'TRUE' then
1337 l_is_lot_control := TRUE;
1338 end if;
1339 if upper(p_serial_control) = 'TRUE' then
1340 l_is_serial_control := TRUE;
1341 end if;
1342
1343 if p_locator_id <= 0 then
1344 l_locator_id := null;
1345 else
1346 l_locator_id := p_locator_id;
1347 end if;
1348
1349 if p_cost_group_id <= 0 then
1350 l_cost_group_id := null;
1351 else
1352 l_cost_group_id := p_cost_group_id;
1353 end if;
1354
1355 Inv_Quantity_Tree_Pub.Query_Quantities (
1356 p_api_version_number => 1.0,
1357 p_init_msg_lst => fnd_api.g_false,
1358 x_return_status => l_return_status,
1359 x_msg_count => l_msg_count,
1360 x_msg_data => l_msg_data,
1361 p_organization_id => p_organization_id,
1362 p_inventory_item_id => p_inventory_item_id,
1363 p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode,
1364 p_is_revision_control => l_is_revision_control,
1365 p_is_lot_control => l_is_lot_control,
1366 p_is_serial_control => l_is_serial_control,
1367 p_revision => p_revision,
1368 p_lot_number => p_lot_number,
1369 p_subinventory_code => p_subinventory_code,
1370 p_locator_id => l_locator_id,
1371 x_qoh => l_qoh,
1372 x_rqoh => l_rqoh,
1373 x_qr => l_qr,
1374 x_qs => l_qs,
1375 x_att => l_att,
1376 x_atr => l_atr,
1377 p_cost_group_id => l_cost_group_id);
1378 --
1379 IF (l_return_status = fnd_api.g_ret_sts_success)
1380 THEN
1381 return l_atr; -- Return the available quantity
1382 ELSE
1383 return -99999999; -- Return bogus number if error occurs
1384 END IF;
1385 --
1386 END GET_AVAILABLE_QTY;
1387
1388 /****************************************************************************
1389 30.1.2002 Updated by venjayar
1390 To account for getting the LotAttributes of a lot even if the LPN is
1391 in Packing Context (as part of the bug 2091699)
1392 ****************************************************************************/
1393 /*
1394 * BUg 2267890 - add msik.lot_status_enabled for the cursor
1395 */
1396 PROCEDURE LOT_ATTRIBUTES (
1397 x_lot_attributes OUT NOCOPY t_genref,
1398 p_lot_number IN VARCHAR2,
1399 p_organization_id IN NUMBER,
1400 p_inventory_item_id IN NUMBER,
1401 p_lpn_context_id IN NUMBER DEFAULT 0) IS
1402 BEGIN
1403 IF(p_lpn_context_id = 8)
1404 THEN
1405 /* PACKING CONTEXT */
1406 OPEN x_lot_attributes FOR
1407 SELECT mmst.status_code, mmst.status_id,
1408 msik.shelf_life_code, msik.lot_status_enabled, mtlt.lot_expiration_date
1409 FROM mtl_material_statuses_vl mmst,
1410 mtl_system_items_kfv msik,
1411 mtl_transaction_lots_temp mtlt,
1412 mtl_material_transactions_temp mmtt
1413 WHERE mtlt.lot_number = p_lot_number
1414 AND mmtt.organization_id = p_organization_id
1415 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
1416 AND msik.inventory_item_id = p_inventory_item_id
1417 AND mmst.status_id(+) = mtlt.status_id
1421 OPEN x_lot_attributes FOR
1418 AND msik.organization_id = mmtt.organization_id;
1419 ELSE
1420 /* All other Contexts */
1422 SELECT mmst.status_code, mmst.status_id,
1423 msik.shelf_life_code, msik.lot_status_enabled, mln.expiration_date
1424 FROM mtl_material_statuses_vl mmst,
1425 mtl_system_items_kfv msik,
1426 mtl_lot_numbers mln
1427 WHERE mln.lot_number = p_lot_number
1428 AND mln.organization_id = p_organization_id
1429 AND msik.inventory_item_id = p_inventory_item_id
1430 AND mmst.status_id(+) = mln.status_id
1431 AND msik.organization_id = mln.organization_id
1432 -- Following condition is added as a part of Bug fix for Bug# 3549931
1433 AND msik.inventory_item_id = mln.inventory_item_id;
1434 END IF;
1435 EXCEPTION
1436 when FND_API.G_EXC_UNEXPECTED_ERROR then
1437 null;
1438 END LOT_ATTRIBUTES;
1439
1440 /****************************************************************************
1441 30.1.2002 Updated by venjayar
1442 To account for getting the Serial Attributes of a Serial Number
1443 even if the LPN is in Packing Context (as part of the bug 2091699)
1444 ****************************************************************************/
1445 PROCEDURE SERIAL_ATTRIBUTES(
1446 x_serial_attributes OUT NOCOPY t_genref,
1447 p_serial_number IN VARCHAR2,
1448 p_organization_id IN NUMBER,
1449 p_inventory_item_id IN NUMBER,
1450 p_lpn_context_id IN NUMBER DEFAULT 0) IS
1451
1452 BEGIN
1453 IF(p_lpn_context_id = 8)
1454 THEN
1455 /* PACKING CONTEXT */
1456 OPEN x_serial_attributes FOR
1457 SELECT mmst.status_code, msik.serial_status_enabled, mmst.status_id
1458 FROM mtl_material_statuses_vl mmst,
1459 mtl_serial_numbers_temp msnt,
1460 mtl_material_transactions_temp mmtt,
1461 mtl_transaction_lots_temp mtlt,
1462 mtl_system_items_b msik
1463 WHERE mmtt.organization_id = p_organization_id
1464 AND mmtt.inventory_item_id = p_inventory_item_id
1465 AND msik.organization_id = mmtt.organization_id
1466 AND msik.inventory_item_id = mmtt.inventory_item_id
1467 AND msnt.status_id = mmst.status_id(+)
1468 AND msnt.fm_serial_number = p_serial_number
1469 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
1470 AND nvl(mtlt.serial_transaction_temp_id,mmtt.transaction_temp_id) = msnt.transaction_temp_id;
1471 ELSE
1472 /* All other Contexts */
1473 OPEN x_serial_attributes FOR
1474 SELECT mmst.status_code, msik.serial_status_enabled, mmst.status_id
1475 FROM mtl_material_statuses_vl mmst, mtl_serial_numbers msn, mtl_system_items_b msik
1476 WHERE msn.current_organization_id = p_organization_id
1477 AND msn.inventory_item_id = p_inventory_item_id
1478 AND msik.organization_id = msn.current_organization_id
1479 AND msik.inventory_item_id = msn.inventory_item_id
1480 AND msn.status_id = mmst.status_id(+)
1481 AND msn.serial_number = p_serial_number;
1482 END IF;
1483 EXCEPTION
1484 when FND_API.G_EXC_UNEXPECTED_ERROR then
1485 null;
1486 END SERIAL_ATTRIBUTES;
1487
1488 PROCEDURE Get_Serial_Number(
1489 x_serialLOV OUT NOCOPY t_genref,
1490 p_organization_id IN NUMBER,
1491 p_inventory_item_id IN NUMBER,
1492 p_serial_number in VARCHAR2)
1493 IS
1494 BEGIN
1495 /* FP-J Lot/Serial Support Enhancements
1496 * Add current status of resides in receiving
1497 */
1498 OPEN x_serialLOV for
1499 select serial_number, current_subinventory_code, current_locator_id, lot_number,'', current_status, ''
1500 from mtl_serial_numbers
1501 where current_organization_id = p_organization_id
1502 and inventory_item_id = p_inventory_item_id
1503 --and current_status in (3, 5)
1504 AND current_status in (3, 5, 7)
1505 and serial_number like (p_serial_number);
1506 END Get_Serial_number;
1507
1508 PROCEDURE Get_Serial_Number_Inq(
1509 x_serialLOV OUT NOCOPY t_genref,
1510 p_organization_id IN NUMBER,
1511 p_inventory_item_id IN NUMBER,
1512 p_serial_number in VARCHAR2)
1513 IS
1514 BEGIN
1515 /* FP-J Lot/Serial Support Enhancements
1516 * Add current status of resides in receiving
1517 */
1518 OPEN x_serialLOV for
1519 select msn.serial_number, msn.current_subinventory_code, milk.concatenated_segments
1520 , msn.lot_number,'', msn.current_status, '', msn.current_locator_id
1521 from mtl_serial_numbers msn, mtl_item_locations_kfv milk
1522 where msn.current_organization_id = p_organization_id
1523 and msn.inventory_item_id = p_inventory_item_id
1524 --and msn.current_status in (3, 5)
1525 AND msn.current_status in (3, 5, 7)
1526 and msn.serial_number like (p_serial_number)
1527 and milk.organization_id (+) = msn.current_organization_id
1528 and milk.subinventory_code (+) = msn.current_subinventory_code
1529 and milk.inventory_location_id (+) = msn.current_locator_id
1530 ORDER BY msn.serial_number, msn.current_subinventory_code, milk.concatenated_segments;
1531 END Get_Serial_number_Inq;
1532
1533
1534 -- Added by Manu Gupta 28-Feb-2001
1535 -- This works just as Get_Serial_Number but
1536 -- is specific for misc receipts
1537 PROCEDURE Get_Serial_Number_RcptTrx(
1541 p_serial_number in VARCHAR2,
1538 x_serialLOV OUT NOCOPY t_genref,
1539 p_organization_id IN NUMBER,
1540 p_inventory_item_id IN NUMBER,
1542 p_transactiontypeid IN NUMBER)
1543 IS
1544 BEGIN
1545
1546 OPEN x_serialLOV for
1547 select serial_number, current_subinventory_code, current_locator_id, lot_number,'', current_status, ''
1548 from mtl_serial_numbers
1549 where current_organization_id = p_organization_id
1550 and inventory_item_id = p_inventory_item_id
1551 AND(
1552 (current_organization_id = p_organization_id AND current_status = 1)
1553 OR
1554 (current_status = 4 AND Nvl(to_number(fnd_profile.value('INV_RESTRICT_RCPT_SER')), 2) = 2)
1555 OR
1556 (current_status = 4 AND Nvl(to_number(fnd_profile.value('INV_RESTRICT_RCPT_SER')), 2) = 1 AND last_txn_source_type_id Not in (2,5))
1557 )
1558 and serial_number like (p_serial_number)
1559 AND (group_mark_id is null OR group_mark_id = -1) -- Bug # 2591673
1560 and
1561 (INV_MATERIAL_STATUS_GRP.is_status_applicable(
1562 'TRUE',
1563 NULL,
1564 p_transactiontypeid,
1565 NULL,
1566 NULL,
1567 p_organization_id,
1568 p_inventory_item_id,
1569 current_subinventory_code,
1570 current_locator_id,
1571 lot_number,
1572 serial_number,
1573 'S')) = 'Y'
1574 ORDER BY serial_number;
1575 END Get_Serial_Number_RcptTrx;
1576
1577 PROCEDURE Get_PUP_Serial_Number(
1578 x_serialLOV OUT NOCOPY t_genref,
1579 p_organization_id IN NUMBER,
1580 p_inventory_item_id IN NUMBER,
1581 p_serial_number in VARCHAR2,
1582 p_txn_type_id IN NUMBER := 0,
1583 p_wms_installed IN VARCHAR2 :='TRUE')
1584 IS
1585 BEGIN
1586 /* FP-J Lot/Serial Support Enhancements
1587 * Add current status of resides in receiving */
1588 OPEN x_serialLOV for
1589 select serial_number, current_subinventory_code, current_locator_id, lot_number
1590 from mtl_serial_numbers
1591 where current_organization_id = p_organization_id
1592 and inventory_item_id = p_inventory_item_id
1593 --and current_status in (3, 5)
1594 AND current_status in (3, 5, 7)
1595 and serial_number like p_serial_number
1596 AND inv_material_status_grp.is_status_applicable
1597 (p_wms_installed,
1598 NULL,
1599 p_txn_type_id,
1600 NULL,
1601 NULL,
1602 p_organization_id,
1603 NULL,
1604 NULL,
1605 NULL,
1606 NULL,
1607 p_serial_number,
1608 'S') = 'Y' ;
1609 END Get_PUP_Serial_NUMBER;
1610
1611 PROCEDURE get_serial_lov(x_serial_number OUT NOCOPY t_genref,
1612 p_organization_id IN NUMBER,
1613 p_item_id IN VARCHAR2,
1614 p_serial IN VARCHAR2)
1615 IS
1616 BEGIN
1617 OPEN x_serial_number FOR
1618 SELECT serial_number, current_subinventory_code, current_locator_id, lot_number, 'A', 'A', 'A'
1619 FROM mtl_serial_numbers
1620 WHERE inventory_item_id = TO_NUMBER(p_item_id)
1621 AND (group_mark_id is null OR group_mark_id = -1)
1622 AND current_organization_id = p_organization_id
1623 --AND ( (current_organization_id = p_organization_id AND current_status = 1)
1624 -- OR current_status = 4)
1625 AND serial_number LIKE (p_serial)
1626 ORDER BY Lpad(serial_number,20);
1627
1628 END get_serial_lov;
1629
1630
1631 PROCEDURE SELECT_SERIAL_NUMBER(
1632 x_serial_numbers OUT NOCOPY t_genref,
1633 p_organization_id IN NUMBER,
1634 p_inventory_item_id IN NUMBER,
1635 p_revision IN VARCHAR2,
1636 p_subinventory_code IN VARCHAR2,
1637 p_locator_id IN NUMBER,
1638 p_cost_Group_id IN NUMBER,
1639 p_lot_number IN VARCHAR2) IS
1640 BEGIN
1641 open x_serial_numbers FOR
1642 select ms.serial_number, ms.lpn_id
1643 from mtl_serial_numbers ms
1644 where ms.inventory_item_id = p_inventory_item_id
1645 and ms.current_organization_id = p_organization_id
1646 and nvl(ms.revision, '$@#$%') =
1647 decode(p_revision, NULL, nvl(ms.revision, '$@#$%'), p_revision)
1648 AND nvl(ms.current_subinventory_code, '$@#$%') =
1649 decode(p_subinventory_code, NULL, nvl(ms.current_subinventory_code, '$@#$%'), p_subinventory_code)
1650 AND nvl(ms.current_locator_id, 0) = decode(p_locator_id, NULL, nvl(ms.current_locator_id, 0), p_locator_id)
1651 AND nvl(ms.cost_group_id, 0) = decode(p_cost_group_id, NULL, nvl(ms.cost_group_id, 0), p_cost_group_id)
1652 AND nvl(ms.lot_number, '$@#$%') = decode(p_lot_number, NULL, nvl(ms.lot_number, '$@#$%'), p_lot_number)
1653 AND ms.current_status =3
1654 and not exists (select w.status
1655 from mtl_material_transactions_temp t,
1656 wms_dispatched_tasks w
1657 where w.status = 4
1658 and w.task_type <> 2
1659 and w.transaction_temp_id = t.transaction_temp_id
1660 and (t.content_lpn_id = ms.lpn_id or
1664
1661 t.lpn_id = ms.lpn_id));
1662
1663 END SELECT_SERIAL_NUMBER;
1665 /****************************************************************************
1666 Added by Amy (qxliu) Sept. 20, 2001
1667 Overloaded procedure to find serial numbers in a LPN
1668
1669 30.1.2002 Updated by venjayar
1670 To account for getting the Serial Numbers even if the LPN is
1671 in Packing Context (as part of the bug 2091699)
1672 ****************************************************************************/
1673 PROCEDURE SELECT_SERIAL_NUMBER(
1674 x_serial_numbers OUT NOCOPY t_genref,
1675 p_organization_id IN NUMBER,
1676 p_inventory_item_id IN NUMBER,
1677 p_lot_number IN VARCHAR2,
1678 p_lpn_id IN NUMBER,
1679 p_lpn_context_id IN NUMBER DEFAULT 0,
1680 p_revision IN VARCHAR2) IS
1681 BEGIN
1682
1683 IF (p_lpn_context_id = 8)
1684 THEN
1685 /* PACKING CONTEXT */
1686 OPEN x_serial_numbers FOR
1687 SELECT msnt.fm_serial_number, mmtt.transfer_lpn_id, mtlt.lot_number
1688 FROM mtl_serial_numbers_temp msnt,
1689 mtl_material_transactions_temp mmtt,
1690 mtl_transaction_lots_temp mtlt
1691 WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
1692 and nvl(mtlt.serial_transaction_temp_id,mmtt.transaction_temp_id) = msnt.transaction_temp_id
1693 and mmtt.inventory_item_id = p_inventory_item_id
1694 and nvl(mmtt.revision,'$@#$%') = DECODE(p_revision,NULL,nvl(mmtt.revision,'$@#$%'),p_revision)
1695 and mmtt.organization_id = p_organization_id
1696 and mmtt.transfer_lpn_id = p_lpn_id
1697 and nvl(mtlt.lot_number,'$@#$%') = DECODE(p_lot_number,NULL,nvl(mtlt.lot_number,'$@#$%'),p_lot_number);
1698 ELSE
1699 /* FP-J Lot/Serial Support Enhancements
1700 * Add current status of resides in receiving */
1701 /* All other Contexts */
1702 OPEN x_serial_numbers FOR
1703 SELECT ms.serial_number, ms.lpn_id
1704 FROM mtl_serial_numbers ms
1705 WHERE ms.inventory_item_id = p_inventory_item_id
1706 AND ms.current_organization_id = p_organization_id
1707 AND nvl(ms.revision, '$@#$%') = decode(p_revision, NULL, nvl(ms.revision, '$@#$%'), p_revision)
1708 AND nvl(ms.lot_number, '$@#$%') = decode(p_lot_number, NULL, nvl(ms.lot_number, '$@#$%'), p_lot_number)
1709 --AND ms.current_status in (3,4,5)
1710 --Bug no 3589766
1711 --Show serial numbers irrespective of the status of the serial number.
1712 --AND ms.current_status in (3, 4, 5, 7)
1713 AND NOT EXISTS (SELECT w.status
1714 FROM mtl_material_transactions_temp t,
1715 wms_dispatched_tasks w
1716 WHERE w.status = 4
1717 AND w.task_type <> 2
1718 AND w.transaction_temp_id = t.transaction_temp_id
1719 AND (t.content_lpn_id = ms.lpn_id or
1720 t.transfer_lpn_id = ms.lpn_id))
1721 AND ms.lpn_id = p_lpn_id;
1722
1723 END IF;
1724 END SELECT_SERIAL_NUMBER;
1725
1726
1727 PROCEDURE UPDATE_QUANTITY (
1728 p_organization_id IN NUMBER
1729 , p_inventory_item_id IN NUMBER
1730 , p_revision IN VARCHAR2 DEFAULT NULL
1731 , p_lot_number IN VARCHAR2 DEFAULT NULL
1732 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
1733 , p_locator_id IN NUMBER DEFAULT NULL
1734 , p_cost_group_id IN NUMBER DEFAULT NULL
1735 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
1736 , p_primary_quantity IN NUMBER
1737 , p_containerized IN NUMBER
1738 , x_qoh OUT NOCOPY NUMBER
1739 , x_att OUT NOCOPY NUMBER
1740 , x_return_status OUT NOCOPY VARCHAR2
1741 , x_msg_count OUT NOCOPY NUMBER
1742 , x_msg_data OUT NOCOPY VARCHAR2
1743 ) IS
1744
1745 l_api_version_number CONSTANT NUMBER := 1.0;
1746 l_api_name CONSTANT VARCHAR2(30) := 'Update_Quantities';
1747 l_tree_mode NUMBER := INV_Quantity_Tree_Pvt.g_transaction_mode;
1748 l_is_revision_control BOOLEAN;
1749 l_is_lot_control BOOLEAN;
1750 l_is_serial_control BOOLEAN;
1751 l_rev_control_code NUMBER;
1752 l_lot_control_code NUMBER;
1753 l_ser_control_code NUMBER;
1754 l_demand_source_type_id NUMBER := 13;
1755 l_demand_source_header_id NUMBER := -9999;
1756 l_demand_source_line_id NUMBER := -9999;
1757 l_demand_source_name VARCHAR2(30) := NULL;
1758 l_lot_expiration_date DATE;
1759 l_quantity_type NUMBER := inv_quantity_tree_pvt.g_qoh;
1760 l_onhand_source NUMBER := inv_quantity_tree_pvt.g_all_subs;
1761 l_rqoh NUMBER;
1762 l_qr NUMBER;
1763 l_qs NUMBER;
1764 l_atr NUMBER;
1765
1766 cursor iteminfo is
1767 select nvl(msi.revision_qty_control_code, 1)
1768 ,nvl(msi.lot_control_code, 1)
1769 ,nvl(msi.serial_number_control_code,1)
1770 from mtl_system_items msi
1771 where organization_id = p_organization_id
1772 and inventory_item_id = p_inventory_item_id;
1773
1774 BEGIN
1775
1776 l_lot_expiration_date := to_date(NULL);
1777
1778 open iteminfo;
1779 fetch iteminfo into l_rev_control_code
1780 ,l_lot_control_code
1781 ,l_ser_control_code;
1785 end if;
1782 if iteminfo%notfound then
1783 close iteminfo;
1784 raise no_data_found;
1786 close iteminfo;
1787
1788 if l_rev_control_code = 1 then
1789 l_is_revision_control := false;
1790 else
1791 l_is_revision_control := true;
1792 end if;
1793 if l_lot_control_code = 1 then
1794 l_is_lot_control := false;
1795 else
1796 l_is_lot_control := true;
1797 end if;
1798 if l_ser_control_code = 1 then
1799 l_is_serial_control := false;
1800 else
1801 l_is_serial_control := true;
1802 end if;
1803
1804 inv_quantity_tree_pub.update_quantities
1805 ( p_api_version_number => l_api_version_number
1806 , p_init_msg_lst => fnd_api.g_false
1807 , x_return_status => x_return_status
1808 , x_msg_count => x_msg_count
1809 , x_msg_data => x_msg_data
1810 , p_organization_id => p_organization_id
1811 , p_inventory_item_id => p_inventory_item_id
1812 , p_tree_mode => l_tree_mode
1813 , p_is_revision_control => l_is_revision_control
1814 , p_is_lot_control => l_is_lot_control
1815 , p_is_serial_control => l_is_serial_control
1816 , p_demand_source_type_id => l_demand_source_type_id
1817 , p_demand_source_header_id => l_demand_source_header_id
1818 , p_demand_source_line_id => l_demand_source_line_id
1819 , p_demand_source_name => l_demand_source_name
1820 , p_lot_expiration_date => l_lot_expiration_date
1821 , p_revision => p_revision
1822 , p_lot_number => p_lot_number
1823 , p_subinventory_code => p_subinventory_code
1824 , p_locator_id => p_locator_id
1825 , p_primary_quantity => p_primary_quantity
1826 , p_quantity_type => l_quantity_type
1827 , p_onhand_source => l_onhand_source
1828 , x_qoh => x_qoh
1829 , x_rqoh => l_rqoh
1830 , x_qr => l_qr
1831 , x_qs => l_qs
1832 , x_att => x_att
1833 , x_atr => l_atr
1834 , p_transfer_subinventory_code => p_transfer_subinventory_code
1835 , p_cost_group_id => p_cost_group_id
1836 , p_containerized => p_containerized
1837 ) ;
1838
1839 exception
1840 when others then
1841 --
1842 if iteminfo%isopen then
1843 close iteminfo;
1844 end if;
1845 x_return_status := fnd_api.g_ret_sts_unexp_error;
1846 fnd_msg_pub.count_and_get( p_count => x_msg_count
1847 ,p_data => x_msg_data );
1848
1849 END UPDATE_QUANTITY;
1850
1851 -- INVCONV, NSRIVAST, START
1852 /*
1853 * Overloaded procedure that calls the the update_quantity procedure
1854 * with secondary quantity.
1855 */
1856
1857 PROCEDURE UPDATE_QUANTITY (
1858 p_organization_id IN NUMBER
1859 , p_inventory_item_id IN NUMBER
1860 , p_revision IN VARCHAR2 DEFAULT NULL
1861 , p_lot_number IN VARCHAR2 DEFAULT NULL
1862 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
1863 , p_locator_id IN NUMBER DEFAULT NULL
1864 , p_cost_group_id IN NUMBER DEFAULT NULL
1865 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
1866 , p_primary_quantity IN NUMBER
1867 , p_containerized IN NUMBER
1868 , p_secondary_quntity IN NUMBER -- INVCONV, NSRIVAST,
1869 , x_qoh OUT NOCOPY NUMBER
1870 , x_att OUT NOCOPY NUMBER
1871 , x_return_status OUT NOCOPY VARCHAR2
1872 , x_msg_count OUT NOCOPY NUMBER
1873 , x_msg_data OUT NOCOPY VARCHAR2
1874 ) IS
1875
1876 l_api_version_number CONSTANT NUMBER := 1.0;
1877 l_api_name CONSTANT VARCHAR2(30) := 'Update_Quantities';
1878 l_tree_mode NUMBER := INV_Quantity_Tree_Pvt.g_transaction_mode;
1879 l_is_revision_control BOOLEAN;
1880 l_is_lot_control BOOLEAN;
1881 l_is_serial_control BOOLEAN;
1882 l_rev_control_code NUMBER;
1883 l_lot_control_code NUMBER;
1884 l_ser_control_code NUMBER;
1885 l_demand_source_type_id NUMBER := 13;
1886 l_demand_source_header_id NUMBER := -9999;
1887 l_demand_source_line_id NUMBER := -9999;
1888 l_demand_source_name VARCHAR2(30) := NULL;
1889 l_lot_expiration_date DATE;
1890 l_quantity_type NUMBER := inv_quantity_tree_pvt.g_qoh;
1891 l_onhand_source NUMBER := inv_quantity_tree_pvt.g_all_subs;
1892 l_rqoh NUMBER;
1893 l_qr NUMBER;
1894 l_qs NUMBER;
1895 l_atr NUMBER;
1896 -- INVCONV, NSRIVAST, END
1897 l_sqoh NUMBER;
1898 l_srqoh NUMBER;
1899 l_sqr NUMBER;
1900 l_sqs NUMBER;
1901 l_satt NUMBER;
1902 l_satr NUMBER;
1903 l_grade VARCHAR2(150) := NULL ;
1904 -- INVCONV, NSRIVAST, END
1905
1906 cursor iteminfo is
1907 select nvl(msi.revision_qty_control_code, 1)
1908 ,nvl(msi.lot_control_code, 1)
1909 ,nvl(msi.serial_number_control_code,1)
1910 from mtl_system_items msi
1911 where organization_id = p_organization_id
1912 and inventory_item_id = p_inventory_item_id;
1913
1914 BEGIN
1915
1916 l_lot_expiration_date := to_date(NULL);
1917
1921 ,l_ser_control_code;
1918 open iteminfo;
1919 fetch iteminfo into l_rev_control_code
1920 ,l_lot_control_code
1922 if iteminfo%notfound then
1923 close iteminfo;
1924 raise no_data_found;
1925 end if;
1926 close iteminfo;
1927
1928 if l_rev_control_code = 1 then
1929 l_is_revision_control := false;
1930 else
1931 l_is_revision_control := true;
1932 end if;
1933 if l_lot_control_code = 1 then
1934 l_is_lot_control := false;
1935 else
1936 l_is_lot_control := true;
1937 end if;
1938 if l_ser_control_code = 1 then
1939 l_is_serial_control := false;
1940 else
1941 l_is_serial_control := true;
1942 end if;
1943
1944 inv_quantity_tree_pub.update_quantities
1945 ( p_api_version_number => l_api_version_number
1946 , p_init_msg_lst => fnd_api.g_false
1947 , x_return_status => x_return_status
1948 , x_msg_count => x_msg_count
1949 , x_msg_data => x_msg_data
1950 , p_organization_id => p_organization_id
1951 , p_inventory_item_id => p_inventory_item_id
1952 , p_tree_mode => l_tree_mode
1953 , p_is_revision_control => l_is_revision_control
1954 , p_is_lot_control => l_is_lot_control
1955 , p_is_serial_control => l_is_serial_control
1956 , p_demand_source_type_id => l_demand_source_type_id
1957 , p_demand_source_header_id => l_demand_source_header_id
1958 , p_demand_source_line_id => l_demand_source_line_id
1959 , p_demand_source_name => l_demand_source_name
1960 , p_lot_expiration_date => l_lot_expiration_date
1961 , p_revision => p_revision
1962 , p_lot_number => p_lot_number
1963 , p_subinventory_code => p_subinventory_code
1964 , p_locator_id => p_locator_id
1965 , p_primary_quantity => p_primary_quantity
1966 , p_quantity_type => l_quantity_type
1967 , p_onhand_source => l_onhand_source
1968 , x_qoh => x_qoh
1969 , x_rqoh => l_rqoh
1970 , x_qr => l_qr
1971 , x_qs => l_qs
1972 , x_att => x_att
1973 , x_atr => l_atr
1974 , p_transfer_subinventory_code => p_transfer_subinventory_code
1975 , p_cost_group_id => p_cost_group_id
1976 , p_containerized => p_containerized
1977 -- INVCONV, NSRIVAST, Start
1978 , x_sqoh => l_sqoh
1979 , x_srqoh => l_srqoh
1980 , x_sqr => l_sqr
1981 , x_sqs => l_sqs
1982 , x_satt => l_satt
1983 , x_satr => l_satr
1984 , p_grade_code => l_grade
1985 , p_secondary_quantity => p_secondary_quntity
1986 --, p_transfer_locator_id =>
1987 ---, p_lpn_id =>
1988 -- INVCONV, NSRIVAST, End
1989 ) ;
1990
1991 exception
1992 when others then
1993 --
1994 if iteminfo%isopen then
1995 close iteminfo;
1996 end if;
1997 x_return_status := fnd_api.g_ret_sts_unexp_error;
1998 fnd_msg_pub.count_and_get( p_count => x_msg_count
1999 ,p_data => x_msg_data );
2000
2001 END UPDATE_QUANTITY;
2002 -- INVCONV, NSRIVAST, END
2003
2004
2005 --
2006 /******************************************************
2007 * Obtain onhand information for an INV org
2008 * Overloaded to include filtering on project and task
2009 ******************************************************
2010 */
2011
2012 PROCEDURE INV_ITEM_INQUIRIES (
2013 x_item_inquiries OUT NOCOPY t_genref,
2014 p_Organization_Id IN NUMBER,
2015 p_Inventory_Item_Id IN NUMBER DEFAULT NULL,
2016 p_Revision IN VARCHAR2 DEFAULT NULL,
2017 p_Lot_Number IN VARCHAR2 DEFAULT NULL,
2018 p_Subinventory_Code IN VARCHAR2 DEFAULT NULL,
2019 p_Locator_Id IN NUMBER DEFAULT NULL,
2020 p_project_id IN NUMBER DEFAULT NULL,
2021 p_task_id IN NUMBER DEFAULT NULL,
2022 x_Status OUT NOCOPY VARCHAR2,
2023 x_Message OUT NOCOPY VARCHAR2) IS
2024 BEGIN
2025 OPEN x_item_inquiries FOR
2026 SELECT msik.concatenated_segments, -- Item Concatenated Segments
2027 moq.revision,
2028 msik.description,
2029 moq.subinventory_code,
2030 moq.locator_id,
2031 INV_PROJECT.GET_LOCSEGS(moq.locator_id,
2032 p_organization_id) concatenated_segments, --Physical Locator Segs
2033 moq.lot_number,
2034 msik.primary_uom_code,
2035 sum(nvl(moq.primary_transaction_quantity, 0)),
2036 /* Bug 4117556 performance issue for item inquiry
2037 Do not call quantity tree to get available quantity for each onhand record
2038 Instead, calling quantity tree at each page entered event of ItemOnhandPage */
2039 /*inv_ITEM_INQ.get_available_qty(
2043 moq.subinventory_code,
2040 moq.organization_id,
2041 moq.inventory_item_id,
2042 moq.revision,
2044 moq.locator_id,
2045 moq.lot_number,null,
2046 decode(moq.revision, NULL, 'FALSE', 'TRUE'),
2047 decode(msik.lot_control_code, 2, 'TRUE', 'FALSE'),
2048 decode(msik.serial_number_control_code, NULL, 'FALSE', 1, 'FALSE', 'TRUE'))*/
2049 -999,
2050 msub.status_id,
2051 inv_item_inq.get_status_code(msub.status_id),
2052 mil.status_id,
2053 inv_item_inq.get_status_code(mil.status_id),
2054 mln.status_id,
2055 inv_item_inq.get_status_code(mln.status_id),
2056 msik.serial_number_control_code,
2057 moq.cost_group_id,
2058 INV_PROJECT.GET_PROJECT_NUMBER project_number, --Project #
2059 INV_PROJECT.GET_TASK_NUMBER task_number, --Task #
2060 -- INVCONV start
2061 NVL(msik.tracking_quantity_ind, 'P'),
2062 sum(nvl(moq.secondary_transaction_quantity, 0)),
2063 msik.secondary_uom_code
2064 -- INVCONV end
2065 FROM mtl_onhand_quantities_detail moq,
2066 mtl_system_items_vl msik, -- Modified for Bug # 5472330
2067 mtl_item_locations mil,
2068 mtl_secondary_inventories msub,
2069 mtl_lot_numbers mln
2070 WHERE moq.organization_id = msik.organization_id
2071 AND moq.inventory_item_id = msik.inventory_item_id
2072 AND moq.organization_id = msub.organization_id
2073 AND moq.subinventory_code = msub.secondary_inventory_name(+)
2074 AND moq.organization_id = mil.organization_id(+)
2075 AND moq.locator_id = mil.inventory_location_id(+)
2076 AND NVL(mil.project_id,-9999) = NVL(p_project_id, NVL(mil.project_id,-9999)) -- filter on project
2077 AND NVL(mil.task_id, -9999) = NVL(p_task_id, NVL(mil.task_id, -9999))
2078 AND moq.subinventory_code = mil.subinventory_code(+)
2079 AND moq.organization_id = mln.organization_id(+)
2080 AND moq.inventory_item_id = mln.inventory_item_id(+)
2081 AND moq.lot_number = mln.lot_number(+)
2082 AND moq.organization_id = p_Organization_Id
2083 AND moq.inventory_item_id =
2084 decode (p_Inventory_Item_Id, NULL, moq.inventory_item_id, p_Inventory_Item_Id)
2085 -- Bug 4301817 Not check mtl_transactions_enabled_flag to query non-transactable items
2086 -- AND msik.mtl_transactions_enabled_flag = 'Y'
2087 AND nvl(moq.revision, '!@#$%^&') =
2088 decode(p_Revision, NULL, nvl(moq.revision, '!@#$%^&'), p_Revision)
2089 AND nvl(moq.lot_number, '!@#$%^&') =
2090 decode (p_Lot_Number, NULL, nvl(moq.lot_number, '!@#$%^&'), p_Lot_Number)
2091 AND nvl(moq.subinventory_code, '!@#$%^&') =
2092 decode (p_Subinventory_Code, NULL, nvl(moq.subinventory_code, '!@#$%^&'), p_Subinventory_Code)
2093 AND nvl(moq.locator_id, 0) =
2094 decode(p_Locator_Id, NULL, nvl(moq.locator_id, 0), p_Locator_Id)
2095 GROUP BY moq.organization_id, moq.inventory_item_id,
2096 msik.concatenated_segments, moq.revision, msik.description,
2097 moq.subinventory_code, moq.locator_id,
2098 INV_PROJECT.GET_LOCSEGS(moq.locator_id,p_organization_id),
2099 moq.lot_number, msik.primary_uom_code,
2100 /* Bug 4117556 performance issue for item inquiry */
2101 /*inv_item_inq.get_available_qty(
2102 moq.organization_id,
2103 moq.inventory_item_id,
2104 moq.revision,
2105 moq.subinventory_code,
2106 moq.locator_id,
2107 moq.lot_number, null,
2108 decode(moq.revision, NULL, 'FALSE', 'TRUE'),
2109 decode(msik.lot_control_code, 2, 'TRUE', 'FALSE'),
2110 decode(msik.serial_number_control_code, NULL, 'FALSE', 1, 'FALSE', 'TRUE'))*/
2111 -999,
2112 msub.status_id,
2113 inv_item_inq.get_status_code(msub.status_id),
2114 mil.status_id,
2115 inv_item_inq.get_status_code(mil.status_id),
2116 mln.status_id,
2117 inv_item_inq.get_status_code(mln.status_id),
2118 msik.serial_number_control_code,
2119 moq.cost_group_id,
2120 INV_PROJECT.GET_PROJECT_NUMBER,
2121 INV_PROJECT.GET_TASK_NUMBER,
2122 -- INVCONV start
2123 NVL(msik.tracking_quantity_ind, 'P'),
2124 msik.secondary_uom_code;
2125 -- INVCONV end
2126
2127
2128 x_status := 'C';
2129 x_message := 'Records found';
2130 EXCEPTION
2131 when others then
2132 x_status := 'E';
2133 x_message := substr(SQLERRM,1,240);
2134 END INV_ITEM_INQUIRIES;
2135
2136 /*******************************************************************
2137 * Obtain onhand information WMS org, provide cost group
2138 * information query wms related information
2139 * Overloaded to include filtering on project and task
2140 *******************************************************************
2141 */
2142 PROCEDURE WMS_LOOSE_ITEM_INQUIRIES (
2146 p_revision IN VARCHAR2 DEFAULT NULL,
2143 x_item_inquiries OUT NOCOPY t_genref,
2144 p_organization_id IN NUMBER,
2145 p_inventory_item_id IN NUMBER DEFAULT NULL,
2147 p_lot_number IN VARCHAR2 DEFAULT NULL,
2148 p_subinventory_code IN VARCHAR2 DEFAULT NULL,
2149 p_locator_id IN NUMBER DEFAULT NULL,
2150 p_cost_Group_id IN NUMBER DEFAULT NULL,
2151 p_project_id IN NUMBER DEFAULT NULL,
2152 p_task_id IN NUMBER DEFAULT NULL,
2153 x_status OUT NOCOPY VARCHAR2,
2154 x_message OUT NOCOPY VARCHAR2) IS
2155 BEGIN
2156 OPEN x_item_inquiries FOR
2157 SELECT b.msik_concatenated_segments,
2158 b.revision,
2159 b.description,
2160 b.subinventory_code,
2161 b.subinventory_status_id,
2162 b.subinventory_status,
2163 b.locator_id,
2164 b.milk_concatenated_segments,
2165 b.locator_status_id,
2166 b.locator_status,
2167 b.cost_group_id,
2168 b.cost_group,
2169 b.lot_number,
2170 b.lot_status_id,
2171 b.lot_status,
2172 b.primary_uom_code,
2173 b.sum_txn_qty,
2174 /* Bug 4117556 performance issue for item inquiry
2175 Do not call quantity tree to get available quantity for each onhand record
2176 Instead, calling quantity tree at each page entered event of ItemOnhandPage */
2177 /*inv_item_inq.get_available_qty(
2178 b.organization_id,
2179 b.inventory_item_id,
2180 b.revision,
2181 b.subinventory_code,
2182 b.locator_id,
2183 b.lot_number,
2184 b.cost_group_id,
2185 decode(b.revision, NULL, 'FALSE', 'TRUE'),
2186 decode(b.lot_control_code, 2, 'TRUE', 'FALSE'),
2187 decode(b.serial_number_control_code, NULL, 'FALSE', 1, 'FALSE', 'TRUE'))*/
2188 -999,
2189 -- INVCONV start
2190 ---inv_item_inq.get_packed_quantity(
2191 --- b.organization_id, b.inventory_item_id, b.revision,
2192 --- b.subinventory_code, b.locator_id, b.lot_number, b.cost_Group_id),
2193 ---inv_item_inq.get_loose_quantity(
2194 --- b.organization_id, b.inventory_item_id, b.revision,
2195 --- b.subinventory_code, b.locator_id, b.lot_number, b.cost_Group_id),
2196 b.packed_quantity, -- Bug : 4563072
2197 b.loose_quantity, -- Bug : 4563072
2198 b.serial_number_control_code,
2199 b.project_number,
2200 b.task_number,
2201 -- INVCONV start
2202 b.tracking_quantity_ind,
2203 b.secondary_uom_code,
2204 -- INVCONV end
2205 b.sec_packed_quantity, -- Bug : 4563072
2206 b.sec_loose_quantity -- Bug : 4563072
2207 FROM
2208 (SELECT moq.organization_id organization_id,
2209 moq.inventory_item_id inventory_item_id,
2210 msik.concatenated_segments msik_concatenated_segments,
2211 moq.revision revision,
2212 msik.description description,
2213 moq.subinventory_code subinventory_code,
2214 msub.status_id subinventory_status_id,
2215 mms1.status_code subinventory_status,
2216 moq.locator_id locator_id,
2217 INV_PROJECT.GET_LOCSEGS(moq.locator_id,
2218 p_organization_id) milk_concatenated_segments, --Physical Locator Segements
2219 milk.status_id locator_status_id,
2220 mms2.status_code locator_status,
2221 moq.cost_group_id cost_group_id,
2222 csg.cost_group cost_group,
2223 moq.lot_number lot_number,
2224 mlot.status_id lot_status_id,
2225 mms3.status_code lot_status,
2226 msik.primary_uom_code primary_uom_code,
2227 sum(nvl(moq.primary_transaction_quantity, 0)) sum_txn_qty,
2228 -- Start Bug : 4563072
2229 SUM(DECODE(moq.containerized_flag, 1, moq.primary_transaction_quantity, 0)) packed_quantity,
2230 SUM(DECODE(moq.containerized_flag, 1, 0, moq.primary_transaction_quantity)) loose_quantity,
2231 SUM(DECODE(moq.containerized_flag, 1, moq.secondary_transaction_quantity, 0)) sec_packed_quantity,
2232 SUM(DECODE(moq.containerized_flag, 1, 0, moq.secondary_transaction_quantity)) sec_loose_quantity,
2233 -- End Bug : 4563072
2234 msik.lot_control_code lot_control_code,
2235 msik.serial_number_control_code serial_number_control_code,
2236 INV_PROJECT.GET_PROJECT_NUMBER project_number,
2237 INV_PROJECT.GET_TASK_NUMBER task_number,
2238 -- INVCONV start
2239 NVL(msik.tracking_quantity_ind, 'P') tracking_quantity_ind,
2240 msik.secondary_uom_code secondary_uom_code
2241 -- INVCONV end
2242 FROM mtl_onhand_quantities_detail moq,
2243 mtl_system_items_vl msik, -- Modified for Bug # 5472330
2244 mtl_item_locations milk,
2245 mtl_secondary_inventories msub,
2246 mtl_lot_numbers mlot,
2247 mtl_material_statuses_vl mms1,
2248 mtl_material_statuses_vl mms2,
2252 AND moq.inventory_item_id = msik.inventory_item_id
2249 mtl_material_statuses_vl mms3,
2250 cst_cost_groups csg
2251 WHERE moq.organization_id = msik.organization_id
2253 AND moq.organization_id = msub.organization_id
2254 AND moq.subinventory_code = msub.secondary_inventory_name(+)
2255 AND msub.status_id = mms1.status_id(+)
2256 AND moq.organization_id = milk.organization_id
2257 AND moq.locator_id = milk.inventory_location_id(+)
2258 AND milk.status_id = mms2.status_id(+)
2259 AND moq.subinventory_code = milk.subinventory_code(+)
2260 AND moq.lot_number = mlot.lot_number(+)
2261 AND moq.inventory_item_id = mlot.inventory_item_id(+)
2262 AND moq.organization_id = mlot.organization_id(+)
2263 AND mlot.status_id = mms3.status_id(+)
2264 AND moq.cost_group_id = csg.cost_group_id(+)
2265 -- AND moq.organization_id = csg.organization_id(+)
2266 AND moq.organization_id = p_Organization_Id
2267 AND moq.inventory_item_id =
2268 decode (p_Inventory_Item_Id, NULL, moq.inventory_item_id, p_Inventory_Item_Id)
2269 -- Bug 4301817 Not check mtl_transactions_enabled_flag to query non-transactable items
2270 -- AND msik.mtl_transactions_enabled_flag = 'Y'
2271 AND nvl(moq.revision, '!@#$%^&') =
2272 decode(p_Revision, NULL, nvl(moq.revision, '!@#$%^&'), p_Revision)
2273 AND nvl(moq.lot_number, '!@#$%^&') =
2274 decode (p_Lot_Number, NULL, nvl(moq.lot_number, '!@#$%^&'), p_Lot_Number)
2275 AND nvl(moq.subinventory_code, '!@#$%^&') =
2276 decode (p_Subinventory_Code, NULL, nvl(moq.subinventory_code, '!@#$%^&'), p_Subinventory_Code)
2277 AND nvl(moq.locator_id, 0) =
2278 decode(p_Locator_Id, NULL, nvl(moq.locator_id, 0), p_Locator_Id)
2279 AND nvl(moq.cost_group_id, 0) =
2280 decode(p_cost_group_id, NULL, nvl(moq.cost_group_id, 0), p_cost_group_id)
2281 AND NVL(milk.project_id, -9999) = NVL(p_project_id, NVL(milk.project_id, -9999))
2282 AND NVL(milk.task_id, -9999) = NVL(p_task_id, NVL(milk.task_id, -9999))
2283 GROUP BY moq.organization_id,
2284 moq.inventory_item_id,
2285 msik.concatenated_segments,
2286 moq.revision,
2287 msik.description,
2288 moq.subinventory_code,
2289 msub.status_id,
2290 mms1.status_code,
2291 moq.locator_id,
2292 INV_PROJECT.GET_LOCSEGS(moq.locator_id,p_organization_id),
2293 milk.status_id,
2294 mms2.status_code,
2295 moq.cost_group_id,
2296 csg.cost_group,
2297 moq.lot_number,
2298 mlot.status_id,
2299 mms3.status_code,
2300 msik.primary_uom_code,
2301 msik.lot_control_code,
2302 msik.serial_number_control_code,
2303 INV_PROJECT.GET_PROJECT_NUMBER,
2304 INV_PROJECT.GET_TASK_NUMBER,
2305 -- INVCONV start
2306 NVL(msik.tracking_quantity_ind, 'P'),
2307 msik.secondary_uom_code
2308 -- INVCONV end
2309 ) b;
2310 x_status := 'C';
2311 x_message := 'Records found';
2312 EXCEPTION
2313 when others then
2314 x_status := 'E';
2315 x_message := SUBSTR (SQLERRM , 1 , 240);
2316 END WMS_LOOSE_ITEM_INQUIRIES;
2317
2318 /******************************************************
2319 * Query for Inv org, giving serial number
2320 * Overloaded to include filter on project and task
2321 ******************************************************
2322 */
2323 /* THIS PROCEDURE IS NOT BEING USED ANYWHERE */
2324 PROCEDURE INV_SERIAL_INQUIRIES (
2325 x_serial_inquiries OUT NOCOPY t_genref,
2326 p_Organization_Id IN NUMBER,
2327 p_Serial_Number IN VARCHAR2 DEFAULT NULL,
2328 p_Inventory_Item_Id IN NUMBER DEFAULT NULL,
2329 p_Revision IN VARCHAR2 DEFAULT NULL,
2330 p_Lot_Number IN VARCHAR2 DEFAULT NULL,
2331 p_Subinventory_Code IN VARCHAR2 DEFAULT NULL,
2332 p_Locator_Id IN NUMBER DEFAULT NULL,
2333 p_project_id IN NUMBER DEFAULT NULL,
2334 p_task_id IN NUMBER DEFAULT NULL,
2335 p_unit_number IN VARCHAR2 DEFAULT NULL,
2336 x_Status OUT NOCOPY VARCHAR2,
2337 x_Message OUT NOCOPY VARCHAR2) IS
2338 BEGIN
2339 OPEN x_serial_inquiries FOR
2340 SELECT msik.concatenated_segments, -- Item Concatenated Segments
2341 msn.revision,
2342 msik.description,
2343 msn.current_subinventory_code,
2344 msn.current_locator_id,
2345 INV_PROJECT.GET_LOCSEGS(msn.current_locator_id,
2346 p_organization_id) concatenated_segments, --Locator Segments
2347 msn.lot_number,
2348 msn.serial_number,
2349 msik.primary_uom_code,
2350 1,
2351 INV_PROJECT.GET_PROJECT_NUMBER project_number,
2352 INV_PROJECT.GET_TASK_NUMBER task_number,
2353 msik.serial_number_control_code serial_number_control_code
2354 FROM MTL_SERIAL_NUMBERS msn,
2355 MTL_SYSTEM_ITEMS_VL msik, /* Bug 5581528 */
2356 MTL_ITEM_LOCATIONS milk
2360 AND msn.inventory_item_id = msik.inventory_item_id
2357 WHERE milk.organization_id(+) = msn.current_organization_id
2358 AND milk.subinventory_code(+) = msn.current_subinventory_code
2359 AND milk.inventory_location_id(+) = msn.current_locator_id
2361 AND msn.current_organization_id = msik.organization_id
2362 AND msik.organization_id = p_Organization_Id
2363 -- Bug 4301817 Not check mtl_transactions_enabled_flag to query non-transactable items
2364 -- AND msik.mtl_transactions_enabled_flag = 'Y'
2365 AND msn.serial_number =
2366 decode(p_Serial_Number, NULL, msn.serial_number, p_Serial_Number)
2367 AND nvl(msn.end_item_unit_number, '$@#$%') =
2368 decode(p_unit_number, NULL, nvl(msn.end_item_unit_number, '$@#$%'), p_unit_number)
2369 AND msn.inventory_item_id = p_Inventory_Item_Id
2370 AND nvl(msn.revision, '!@#$%^&') =
2371 decode(p_Revision, NULL, nvl(msn.revision, '!@#$%^&'), p_Revision)
2372 AND nvl(msn.current_subinventory_code, '!@#$%^&') =
2373 decode(p_Subinventory_Code, NULL, nvl(msn.current_subinventory_code, '!@#$%^&'), p_Subinventory_Code)
2374 AND nvl(msn.current_locator_id, 99999999) =
2375 decode(p_Locator_Id, NULL, nvl(msn.current_locator_id, 99999999), p_Locator_Id)
2376 AND nvl(msn.lot_number, '!@#$%^&') =
2377 decode(p_Lot_Number, NULL, nvl(msn.lot_number, '!@#$%^&'), p_Lot_Number)
2378 AND NVL(milk.project_id, -9999) = NVL(p_project_id, NVL(milk.project_id, -9999))
2379 AND NVL(milk.task_id, -9999) = NVL(p_task_id, NVL(milk.task_id, -9999));
2380
2381 x_status := 'C';
2382 x_message := 'Records found';
2383 EXCEPTION
2384 when others then
2385 x_status := 'E';
2386 x_message := substr(SQLERRM,1,240);
2387 END INV_SERIAL_INQUIRIES;
2388
2389 /*****************************************************************
2390 * Query for WMS org, giving serial number
2391 * Overloaded to filter on project and task
2392 ****************************************************************/
2393 PROCEDURE WMS_LOOSE_SERIAL_INQUIRIES (
2394 x_serial_inquiries OUT NOCOPY t_genref,
2395 p_Organization_Id IN NUMBER,
2396 p_Serial_Number IN VARCHAR2 DEFAULT NULL,
2397 p_Inventory_Item_Id IN NUMBER DEFAULT NULL,
2398 p_Revision IN VARCHAR2 DEFAULT NULL,
2399 p_Lot_Number IN VARCHAR2 DEFAULT NULL,
2400 p_Subinventory_Code IN VARCHAR2 DEFAULT NULL,
2401 p_Locator_Id IN NUMBER DEFAULT NULL,
2402 p_cost_Group_id IN NUMBER DEFAULT NULL,
2403 p_project_id IN NUMBER DEFAULT NULL,
2404 p_task_id IN NUMBER DEFAULT NULL,
2405 p_unit_number IN VARCHAR2 DEFAULT NULL,
2406 x_Status OUT NOCOPY VARCHAR2,
2407 x_Message OUT NOCOPY VARCHAR2) IS
2408 BEGIN
2409 OPEN x_serial_inquiries FOR
2410 SELECT msik.concatenated_segments, -- Item Concatenated Segments
2411 msn.revision,
2412 msik.description,
2413 msn.current_subinventory_code,
2414 msub.status_id subinventory_status_id,
2415 mms1.status_code subinventory_status,
2416 msn.current_locator_id,
2417 INV_PROJECT.GET_LOCSEGS(msn.current_locator_id,
2418 p_organization_id) concatenated_segments, --Locator Segments
2419 milk.status_id locator_status_id,
2420 mms2.status_code locator_status,
2421 msn.cost_group_id,
2422 csg.cost_group,
2423 msn.lot_number,
2424 mlot.status_id lot_status_id,
2425 mms3.status_code lot_status,
2426 msn.serial_number,
2427 msn.status_id serial_status_id,
2428 mms4.status_code serial_status,
2429 msik.primary_uom_code,
2430 1,
2431 INV_PROJECT.GET_PROJECT_NUMBER project_number,
2432 INV_PROJECT.GET_TASK_NUMBER task_number,
2433 msik.serial_number_control_code serial_number_control_code,
2434 DECODE(msn.lpn_id,NULL,0,1) packed_qty,
2435 DECODE(msn.lpn_id,NULL,1,0) loose_qty
2436 -- INVCONV start
2437 ---NVL(msik.tracking_quantity_ind, 'P'),
2438 ---msik.secondary_uom_code
2439 -- INVCONV end
2440 FROM MTL_SERIAL_NUMBERS msn,
2441 MTL_SYSTEM_ITEMS_VL msik, /* Bug 5581528 */
2442 MTL_ITEM_LOCATIONS milk,
2443 MTL_SECONDARY_INVENTORIES msub,
2444 MTL_LOT_NUMBERS mlot,
2445 MTL_MATERIAL_STATUSES_vl mms1,
2446 MTL_MATERIAL_STATUSES_vl mms2,
2447 MTL_MATERIAL_STATUSES_vl mms3,
2448 MTL_MATERIAL_STATUSES_vl mms4,
2449 CST_COST_GROUPS csg
2450 WHERE milk.organization_id(+) = msn.current_organization_id
2451 AND milk.subinventory_code(+) = msn.current_subinventory_code
2452 AND milk.inventory_location_id(+) = msn.current_locator_id
2453 AND milk.status_id = mms2.status_id(+)
2454 AND msn.inventory_item_id = msik.inventory_item_id
2455 AND msn.current_organization_id = msik.organization_id
2456 AND msn.current_subinventory_code = msub.secondary_inventory_name(+)
2460 AND msn.lot_number = mlot.lot_number (+)
2457 AND msn.current_organization_id = msub.organization_id(+)
2458 AND msub.status_id = mms1.status_id(+)
2459 AND msn.cost_group_id = csg.cost_group_id(+)
2461 AND msn.current_organization_id = mlot.organization_id(+)
2462 AND msn.inventory_item_id = mlot.inventory_item_id(+)
2463 AND mlot.status_id = mms3.status_id(+)
2464 AND msn.status_id = mms4.status_id(+)
2465 AND msik.organization_id = p_Organization_Id
2466 -- Bug 4301817 Not check mtl_transactions_enabled_flag to query non-transactable items
2467 -- AND msik.mtl_transactions_enabled_flag = 'Y'
2468 AND msn.serial_number = NVL(p_Serial_Number, msn.serial_number)
2469 AND msn.inventory_item_id = p_Inventory_Item_Id
2470 AND nvl(msn.revision, '!@#$%^&') =
2471 decode(p_Revision, NULL, nvl(msn.revision, '!@#$%^&'), p_Revision)
2472 AND nvl(msn.current_subinventory_code, '!@#$%^&') =
2473 decode(p_Subinventory_Code, NULL, nvl(msn.current_subinventory_code, '!@#$%^&'), p_Subinventory_Code)
2474 AND nvl(msn.current_locator_id, 99999999) =
2475 decode(p_Locator_Id, NULL, nvl(msn.current_locator_id, 99999999), p_Locator_Id)
2476 AND nvl(msn.cost_group_id, 99999999) =
2477 decode(p_cost_Group_id, NULL, nvl(msn.cost_group_id, 99999999), p_cost_group_id)
2478 AND nvl(msn.lot_number, '!@#$%^&') =
2479 decode(p_Lot_Number, NULL, nvl(msn.lot_number, '!@#$%^&'), p_Lot_Number)
2480 AND nvl(msn.end_item_unit_number, '$@#$%') =
2481 decode(p_unit_number, NULL, nvl(msn.end_item_unit_number, '$@#$%'), p_unit_number)
2482 AND NVL(milk.project_id, -9999) = NVL(p_project_id, NVL(milk.project_id, -9999))
2483 AND NVL(milk.task_id, -9999) = NVL(p_task_id, NVL(milk.task_id, -9999))
2484 AND msn.current_status = 3; -- Bug# 3196252
2485
2486 x_status := 'C';
2487 x_message := 'Records found';
2488 EXCEPTION
2489 when others then
2490 x_status := 'E';
2491 x_message := substr(SQLERRM,1,240);
2492 END WMS_LOOSE_SERIAL_INQUIRIES;
2493
2494 /********************************************************
2495 * Procedure to fetch Unit Numbers for the item
2496 * Called from UnitNumber LOV of Item Inquiry page
2497 ********************************************************/
2498 PROCEDURE GET_UNIT_NUMBERS (
2499 x_unit_numbers OUT NOCOPY t_genref,
2500 p_organization_id IN NUMBER,
2501 p_inventory_item_id IN NUMBER,
2502 p_restrict_unit_numbers IN VARCHAR2) IS
2503 BEGIN
2504 OPEN x_unit_numbers FOR
2505 SELECT distinct end_item_unit_number
2506 FROM mtl_serial_numbers
2507 WHERE inventory_item_id = p_inventory_item_id
2508 AND current_organization_id = p_organization_id
2509 AND end_item_unit_number IS NOT NULL
2510 AND end_item_unit_number like (p_restrict_unit_numbers)
2511 ORDER BY 1;
2512
2513 END GET_UNIT_NUMBERS;
2514
2515 /****************************************************************************
2516 * Overloaded procedure to find serial numbers given a unit # and even serial #
2517 * This procedure would be used when the ItemOnhandPage displays data for a
2518 * Unit Number and/or a Serial Number
2519 ****************************************************************************/
2520 PROCEDURE SELECT_SERIAL_NUMBER(
2521 x_serial_numbers OUT NOCOPY t_genref,
2522 p_organization_id IN NUMBER,
2523 p_inventory_item_id IN NUMBER,
2524 p_revision IN VARCHAR2,
2525 p_subinventory_code IN VARCHAR2,
2526 p_locator_id IN NUMBER,
2527 p_cost_Group_id IN NUMBER,
2528 p_lot_number IN VARCHAR2,
2529 p_unit_number IN VARCHAR := NULL,
2530 p_serial_number IN VARCHAR2 := NULL) IS
2531 BEGIN
2532 open x_serial_numbers FOR
2533 select ms.serial_number, ms.lpn_id
2534 from mtl_serial_numbers ms
2535 where ms.inventory_item_id = p_inventory_item_id
2536 and ms.current_organization_id = p_organization_id
2537 and nvl(ms.revision, '$@#$%') =
2538 decode(p_revision, NULL, nvl(ms.revision, '$@#$%'), p_revision)
2539 AND nvl(ms.current_subinventory_code, '$@#$%') =
2540 decode(p_subinventory_code, NULL, nvl(ms.current_subinventory_code, '$@#$%'), p_subinventory_code)
2541 AND nvl(ms.current_locator_id, 0) = decode(p_locator_id, NULL, nvl(ms.current_locator_id, 0), p_locator_id)
2542 AND nvl(ms.cost_group_id, 0) = decode(p_cost_group_id, NULL, nvl(ms.cost_group_id, 0), p_cost_group_id)
2543 AND nvl(ms.lot_number, '$@#$%') = decode(p_lot_number, NULL, nvl(ms.lot_number, '$@#$%'), p_lot_number)
2544 AND nvl(ms.serial_number, '$@#$%') = decode(p_serial_number, NULL, nvl(ms.serial_number, '$@#$%'), p_serial_number)
2545 AND nvl(ms.end_item_unit_number, '$@#$%') = decode(p_unit_number, NULL, nvl(ms.end_item_unit_number, '$@#$%'), p_unit_number)
2546 AND ms.current_status =3
2547 and not exists (select w.status
2548 from mtl_material_transactions_temp t,
2549 wms_dispatched_tasks w
2550 where w.status = 4
2551 and w.task_type <> 2
2552 and w.transaction_temp_id = t.transaction_temp_id
2553 and (t.content_lpn_id = ms.lpn_id or
2554 t.lpn_id = ms.lpn_id));
2555
2556 END SELECT_SERIAL_NUMBER;
2557
2558 --Item Inquiry based on project, task and unit number for MSCA orgs
2562 p_Organization_Id IN NUMBER,
2559 /* THIS PROCEDURE IS NOT BEING USED ANYWHERE */
2560 PROCEDURE INV_UNIT_NUMBER_INQUIRIES (
2561 x_unit_inquiries OUT NOCOPY t_genref,
2563 p_unit_number IN VARCHAR2 DEFAULT NULL,
2564 p_Inventory_Item_Id IN NUMBER DEFAULT NULL,
2565 p_Revision IN VARCHAR2 DEFAULT NULL,
2566 p_Lot_Number IN VARCHAR2 DEFAULT NULL,
2567 p_Subinventory_Code IN VARCHAR2 DEFAULT NULL,
2568 p_Locator_Id IN NUMBER DEFAULT NULL,
2569 p_project_id IN NUMBER DEFAULT NULL,
2570 p_task_id IN NUMBER DEFAULT NULL,
2571 x_Status OUT NOCOPY VARCHAR2,
2572 x_Message OUT NOCOPY VARCHAR2) IS
2573 BEGIN
2574 OPEN x_unit_inquiries FOR
2575 SELECT msik.concatenated_segments, -- Item Concatenated Segments
2576 msn.revision,
2577 msik.description,
2578 msn.current_subinventory_code,
2579 msn.current_locator_id,
2580 INV_PROJECT.GET_LOCSEGS(msn.current_locator_id,
2581 p_organization_id) concatenated_segments, --Locator Segments
2582 msn.lot_number,
2583 msik.primary_uom_code,
2584 count(msn.serial_number) total_qty,
2585 inv_item_inq.get_status_code(msub.status_id) sub_status,
2586 inv_item_inq.get_status_code(milk.status_id) loc_status,
2587 inv_item_inq.get_status_code(mln.status_id) lot_status,
2588 msik.serial_number_control_code,
2589 msn.cost_group_id,
2590 INV_PROJECT.GET_PROJECT_NUMBER project_number,
2591 INV_PROJECT.GET_TASK_NUMBER task_number
2592 FROM MTL_SERIAL_NUMBERS msn,
2593 MTL_SYSTEM_ITEMS_VL msik, /* Bug 5581528 */
2594 MTL_ITEM_LOCATIONS milk,
2595 MTL_SECONDARY_INVENTORIES msub,
2596 MTL_LOT_NUMBERS mln
2597 WHERE msn.inventory_item_id = p_Inventory_Item_Id
2598 AND milk.organization_id(+) = msn.current_organization_id
2599 AND milk.subinventory_code(+) = msn.current_subinventory_code
2600 AND milk.inventory_location_id(+) = msn.current_locator_id
2601 AND msn.inventory_item_id = msik.inventory_item_id
2602 AND msn.current_organization_id = msik.organization_id
2603 AND msik.organization_id = p_Organization_Id
2604 AND msn.current_organization_id = msub.organization_id(+)
2605 AND msn.current_subinventory_code = msub.secondary_inventory_name(+)
2606 AND msn.current_organization_id = mln.organization_id(+)
2607 AND msn.lot_number = mln.lot_number(+)
2608 -- Bug 4301817 Not check mtl_transactions_enabled_flag to query non-transactable items
2609 -- AND msik.mtl_transactions_enabled_flag = 'Y'
2610 AND nvl(msn.end_item_unit_number, '$@#$%') =
2611 decode(p_unit_number, NULL, nvl(msn.end_item_unit_number, '$@#$%'), p_unit_number)
2612 AND nvl(msn.revision, '!@#$%^&') =
2613 decode(p_Revision, NULL, nvl(msn.revision, '!@#$%^&'), p_Revision)
2614 AND nvl(msn.current_subinventory_code, '!@#$%^&') =
2615 decode(p_Subinventory_Code, NULL, nvl(msn.current_subinventory_code, '!@#$%^&'), p_Subinventory_Code)
2616 AND nvl(msn.current_locator_id, 99999999) =
2617 decode(p_Locator_Id, NULL, nvl(msn.current_locator_id, 99999999), p_Locator_Id)
2618 AND nvl(msn.lot_number, '!@#$%^&') =
2619 decode(p_Lot_Number, NULL, nvl(msn.lot_number, '!@#$%^&'), p_Lot_Number)
2620 AND NVL(milk.project_id, -9999) = NVL(p_project_id, NVL(milk.project_id, -9999))
2621 AND NVL(milk.task_id, -9999) = NVL(p_task_id, NVL(milk.task_id, -9999))
2622 GROUP BY msn.current_organization_id,
2623 msn.inventory_item_id,
2624 msn.revision,
2625 msik.concatenated_segments,
2626 msn.revision,
2627 msik.description,
2628 msn.current_subinventory_code,
2629 msn.current_locator_id,
2630 INV_PROJECT.GET_LOCSEGS(msn.current_locator_id,p_organization_id),
2631 msn.lot_number,
2632 msik.primary_uom_code,
2633 inv_item_inq.get_status_code(msub.status_id),
2634 inv_item_inq.get_status_code(milk.status_id),
2635 inv_item_inq.get_status_code(mln.status_id),
2636 msik.serial_number_control_code,
2637 msn.cost_group_id,
2638 INV_PROJECT.GET_PROJECT_NUMBER,
2639 INV_PROJECT.GET_TASK_NUMBER;
2640
2641 x_status := 'C';
2642 x_message := 'Records found';
2643 EXCEPTION
2644 when others then
2645 x_status := 'E';
2646 x_message := substr(SQLERRM,1,240);
2647 END INV_UNIT_NUMBER_INQUIRIES;
2648
2649 --Item Inquiry based on project, task and unit number for WMS orgs
2650 PROCEDURE WMS_UNIT_NUMBER_INQUIRIES (
2651 x_unit_inquiries OUT NOCOPY t_genref,
2652 p_Organization_Id IN NUMBER,
2653 p_unit_number IN VARCHAR2 DEFAULT NULL,
2654 p_Inventory_Item_Id IN NUMBER DEFAULT NULL,
2655 p_Revision IN VARCHAR2 DEFAULT NULL,
2656 p_Lot_Number IN VARCHAR2 DEFAULT NULL,
2657 p_Subinventory_Code IN VARCHAR2 DEFAULT NULL,
2661 p_task_id IN NUMBER DEFAULT NULL,
2658 p_Locator_Id IN NUMBER DEFAULT NULL,
2659 p_cost_Group_id IN NUMBER DEFAULT NULL,
2660 p_project_id IN NUMBER DEFAULT NULL,
2662 x_Status OUT NOCOPY VARCHAR2,
2663 x_Message OUT NOCOPY VARCHAR2) IS
2664 BEGIN
2665 OPEN x_unit_inquiries FOR
2666 SELECT b.msik_concatenated_segments,
2667 b.revision,
2668 b.description,
2669 b.subinventory_code,
2670 b.subinventory_status_id,
2671 b.subinventory_status,
2672 b.locator_id,
2673 b.milk_concatenated_segments,
2674 b.locator_status_id,
2675 b.locator_status,
2676 b.cost_group_id,
2677 b.cost_group,
2678 b.lot_number,
2679 b.lot_status_id,
2680 b.lot_status,
2681 b.primary_uom_code,
2682 b.total_qty,
2683 b.loose_qty,
2684 b.serial_number_control_code,
2685 b.project_number,
2686 b.task_number
2687 FROM
2688 (SELECT msn.current_organization_id organization_id,
2689 msn.inventory_item_id inventory_item_id,
2690 msik.concatenated_segments msik_concatenated_segments,
2691 msn.revision revision,
2692 msik.description description,
2693 msn.current_subinventory_code subinventory_code,
2694 msub.status_id subinventory_status_id,
2695 mms1.status_code subinventory_status,
2696 msn.current_locator_id locator_id,
2697 INV_PROJECT.GET_LOCSEGS(msn.current_locator_id,
2698 p_organization_id) milk_concatenated_segments, --Physical Locator Segements
2699 milk.status_id locator_status_id,
2700 mms2.status_code locator_status,
2701 msn.cost_group_id cost_group_id,
2702 csg.cost_group cost_group,
2703 msn.lot_number lot_number,
2704 mlot.status_id lot_status_id,
2705 mms3.status_code lot_status,
2706 msik.primary_uom_code primary_uom_code,
2707 count(msn.serial_number) total_qty,
2708 count(decode(msn.lpn_id, null,1)) loose_qty,
2709 msik.lot_control_code lot_control_code,
2710 msik.serial_number_control_code serial_number_control_code,
2711 INV_PROJECT.GET_PROJECT_NUMBER project_number,
2712 INV_PROJECT.GET_TASK_NUMBER task_number
2713 FROM mtl_serial_numbers msn,
2714 mtl_system_items_vl msik, /* Bug 5581528 */
2715 mtl_item_locations milk,
2716 mtl_secondary_inventories msub,
2717 mtl_lot_numbers mlot,
2718 mtl_material_statuses_vl mms1,
2719 mtl_material_statuses_vl mms2,
2720 mtl_material_statuses_vl mms3,
2721 cst_cost_groups csg
2722 WHERE msn.current_organization_id = msik.organization_id
2723 AND msn.current_organization_id = p_Organization_Id
2724 AND msn.inventory_item_id = p_inventory_item_id
2725 AND msn.inventory_item_id = msik.inventory_item_id
2726 AND nvl(msn.end_item_unit_number, '$@#$%') =
2727 decode(p_unit_number, NULL, nvl(msn.end_item_unit_number, '$@#$%'), p_unit_number)
2728 AND msn.current_organization_id = msub.organization_id
2729 AND msn.current_subinventory_code = msub.secondary_inventory_name(+)
2730 AND msub.status_id = mms1.status_id(+)
2731 AND msn.current_organization_id = milk.organization_id
2732 AND msn.current_locator_id = milk.inventory_location_id(+)
2733 aND milk.status_id = mms2.status_id(+)
2734 AND msn.current_subinventory_code = milk.subinventory_code(+)
2735 AND msn.lot_number = mlot.lot_number(+)
2736 AND msn.inventory_item_id = mlot.inventory_item_id(+)
2737 AND msn.current_organization_id = mlot.organization_id(+)
2738 AND mlot.status_id = mms3.status_id(+)
2739 AND msn.cost_group_id = csg.cost_group_id(+)
2740 -- Bug 4301817 Not check mtl_transactions_enabled_flag to query non-transactable items
2741 -- AND msik.mtl_transactions_enabled_flag = 'Y'
2742 AND nvl(msn.revision, '!@#$%^&') =
2743 decode(p_Revision, NULL, nvl(msn.revision, '!@#$%^&'), p_Revision)
2744 AND nvl(msn.lot_number, '!@#$%^&') =
2745 decode (p_Lot_Number, NULL, nvl(msn.lot_number, '!@#$%^&'), p_Lot_Number)
2746 AND nvl(msn.current_subinventory_code, '!@#$%^&') =
2747 decode (p_Subinventory_Code, NULL, nvl(msn.current_subinventory_code, '!@#$%^&'), p_Subinventory_Code)
2748 AND nvl(msn.current_locator_id, 0) =
2749 decode(p_Locator_Id, NULL, nvl(msn.current_locator_id, 0), p_Locator_Id)
2750 AND nvl(msn.cost_group_id, 0) =
2751 decode(p_cost_group_id, NULL, nvl(msn.cost_group_id, 0), p_cost_group_id)
2752 AND NVL(milk.project_id, -9999) = NVL(p_project_id, NVL(milk.project_id, -9999))
2753 AND NVL(milk.task_id, -9999) = NVL(p_task_id, NVL(milk.task_id, -9999))
2754 GROUP BY msn.current_organization_id,
2755 msn.inventory_item_id,
2756 msik.concatenated_segments,
2757 msn.revision,
2758 msik.description,
2759 msn.current_subinventory_code,
2760 msub.status_id,
2761 mms1.status_code,
2762 msn.current_locator_id,
2766 msn.cost_group_id,
2763 INV_PROJECT.GET_LOCSEGS(msn.current_locator_id,p_organization_id),
2764 milk.status_id,
2765 mms2.status_code,
2767 csg.cost_group,
2768 msn.lot_number,
2769 mlot.status_id,
2770 mms3.status_code,
2771 msik.primary_uom_code,
2772 msik.lot_control_code,
2773 msik.serial_number_control_code,
2774 INV_PROJECT.GET_PROJECT_NUMBER,
2775 INV_PROJECT.GET_TASK_NUMBER) b;
2776
2777 x_status := 'C';
2778 x_message := 'Records found';
2779 EXCEPTION
2780 when others then
2781 x_status := 'E';
2782 x_message := substr(SQLERRM,1,240);
2783 END WMS_UNIT_NUMBER_INQUIRIES;
2784
2785 --changes for walkup loc project
2786
2787
2788 /*******************************************************************
2789 * Obtain onhand information WMS org, provide cost group
2790 * information query wms related information
2791 * Overloaded to include filtering on project and task
2792 *******************************************************************
2793 */
2794 PROCEDURE WMS_LOOSE_ITEM_INQUIRIES (
2795 x_item_inquiries OUT NOCOPY t_genref,
2796 p_organization_id IN NUMBER,
2797 p_inventory_item_id IN NUMBER DEFAULT NULL,
2798 p_subinventory_code IN VARCHAR2 DEFAULT NULL,
2799 p_locator_id IN NUMBER DEFAULT NULL,
2800 x_status OUT NOCOPY VARCHAR2,
2801 x_message OUT NOCOPY VARCHAR2) IS
2802 BEGIN
2803 OPEN x_item_inquiries FOR
2804
2805 SELECT b.msik_concatenated_segments,
2806 NULL,--b.revision,
2807 b.description,
2808 b.subinventory_code,
2809 b.subinventory_status_id,
2810 b.subinventory_status,
2811 b.locator_id,
2812 b.milk_concatenated_segments,
2813 b.locator_status_id,
2814 b.locator_status,
2815 NULL,--b.cost_group_id,
2816 NULL,--b.cost_group,
2817 NULL,--b.lot_number,
2818 NULL,--b.lot_status_id,
2819 NULL,--b.lot_status,
2820 b.primary_uom_code,
2821 b.sum_txn_qty,
2822 inv_item_inq.get_available_qty(
2823 b.organization_id,
2824 b.inventory_item_id,
2825 NULL,--b.revision,
2826 b.subinventory_code,
2827 b.locator_id,
2828 NULL,--b.lot_number,
2829 NULL,--b.cost_group_id
2830 'FALSE',
2831 decode(b.lot_control_code, 2, 'TRUE', 'FALSE'),
2832 decode(b.serial_number_control_code, NULL, 'FALSE', 1, 'FALSE', 'TRUE')) ,
2833 inv_item_inq.get_packed_quantity(
2834 b.organization_id,
2835 b.inventory_item_id,
2836 NULL,--b.revision,
2837 b.subinventory_code,
2838 b.locator_id,
2839 NULL,--b.lot_number,
2840 NULL--b.cost_Group_id
2841 ) ,
2842 inv_item_inq.get_loose_quantity(
2843 b.organization_id,
2844 b.inventory_item_id,
2845 NULL,--b.revision,
2846 b.subinventory_code,
2847 b.locator_id,
2848 NULL,--b.lot_number,
2849 NULL--b.cost_Group_id
2850 ),
2851 b.serial_number_control_code,
2852 NULL,--b.project_number,
2853 NULL,--b.task_number
2854 b.inventory_item_id
2855 FROM
2856 (SELECT moq.organization_id organization_id,
2857 moq.inventory_item_id inventory_item_id,
2858 msik.concatenated_segments msik_concatenated_segments,
2859 msik.description description,
2860 moq.subinventory_code subinventory_code,
2861 msub.status_id subinventory_status_id,
2862 mms1.status_code subinventory_status,
2863 moq.locator_id locator_id,
2864 INV_PROJECT.GET_LOCSEGS(moq.locator_id,
2865 p_organization_id) milk_concatenated_segments, --Physical Locator Segements
2866 milk.status_id locator_status_id,
2867 mms2.status_code locator_status,
2868 msik.primary_uom_code primary_uom_code,
2869 sum(nvl(moq.primary_transaction_quantity, 0)) sum_txn_qty,
2870 msik.lot_control_code lot_control_code,
2871 msik.serial_number_control_code serial_number_control_code
2872 FROM mtl_onhand_quantities_detail moq,
2873 mtl_system_items_vl msik, /* Bug 5581528 */
2874 mtl_item_locations milk,
2875 mtl_secondary_inventories msub,
2876 mtl_material_statuses_vl mms1,
2877 mtl_material_statuses_vl mms2
2878 WHERE moq.organization_id = msik.organization_id
2879 AND moq.inventory_item_id = msik.inventory_item_id
2880 AND moq.organization_id = msub.organization_id
2881 AND moq.subinventory_code = msub.secondary_inventory_name(+)
2882 AND msub.status_id = mms1.status_id(+)
2883 AND moq.organization_id = milk.organization_id
2884 AND moq.locator_id = milk.inventory_location_id(+)
2885 AND milk.status_id = mms2.status_id(+)
2889 decode (p_Inventory_Item_Id, NULL, moq.inventory_item_id, p_Inventory_Item_Id)
2886 AND moq.subinventory_code = milk.subinventory_code(+)
2887 AND moq.organization_id = p_Organization_Id
2888 AND moq.inventory_item_id =
2890 -- Bug 4301817 Not check mtl_transactions_enabled_flag to query non-transactable items
2891 -- AND msik.mtl_transactions_enabled_flag = 'Y'
2892 AND nvl(moq.subinventory_code, '!@#$%^&') =
2893 decode (p_Subinventory_Code, NULL, nvl(moq.subinventory_code, '!@#$%^&'), p_Subinventory_Code)
2894 AND nvl(moq.locator_id, 0) =
2895 decode(p_Locator_Id, NULL, nvl(moq.locator_id, 0), p_Locator_Id)
2896 GROUP BY moq.organization_id,
2897 moq.inventory_item_id,
2898 msik.concatenated_segments,
2899 msik.description,
2900 moq.subinventory_code,
2901 msub.status_id,
2902 mms1.status_code,
2903 moq.locator_id,
2904 INV_PROJECT.GET_LOCSEGS(moq.locator_id,p_organization_id),
2905 milk.status_id,
2906 mms2.status_code,
2907 msik.primary_uom_code,
2908 msik.lot_control_code,
2909 msik.serial_number_control_code
2910 ) b;
2911
2912 x_status := 'C';
2913 x_message := 'Records found';
2914 EXCEPTION
2915 when others then
2916 x_status := 'E';
2917 x_message := SUBSTR (SQLERRM , 1 , 240);
2918 END WMS_LOOSE_ITEM_INQUIRIES;
2919
2920
2921
2922
2923
2924
2925
2926 END inv_ITEM_INQ;