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