1 PACKAGE BODY inv_inv_lovs AS
2 /* $Header: INVINVLB.pls 120.19.12010000.2 2008/09/25 17:45:33 abasheer ship $ */
3
4
5 -- Name: GET_LOT_LOV
6 --
7 -- Input parameters:
8 -- p_organization_id Organization ID
9 -- p_item_id Inventory Item id
10 -- p_lot_number Lot Number
11 -- p_transaction_type_id Used for Material Status Applicability Check
12 -- p_wms_installed Used for Material Status Applicability Check
13 -- p_lpn_id LPN ID
14 -- p_subinventory_code SubInventory Code
15 -- p_locator_id Locator ID
16 -- p_planning_org_id Planning Organization ID - Consignment and VMI Changes
17 -- p_planning_tp_type Planning TP Type - Consignment and VMI Changes
18 -- p_owning_org_id Owning Organization ID - Consignment and VMI Changes
19 -- p_owning_tp_type Owning TP Type - Consignment and VMI Changes
20 --
21 -- Output parameters:
22 -- x_lot_num_lov Returns the LOV rows as a Reference Cursor
23 --
24 -- Functions: This API returns Lot number for a given org and Item Id
25 --Passed subinventory and locator in is_status_applicable call when sub is not null.
26 PROCEDURE get_lot_lov(
27 x_lot_num_lov OUT NOCOPY t_genref
28 , p_organization_id IN NUMBER
29 , p_item_id IN NUMBER
30 , p_lot_number IN VARCHAR2
31 , p_transaction_type_id IN NUMBER
32 , p_wms_installed IN VARCHAR2
33 , p_lpn_id IN NUMBER
34 , p_subinventory_code IN VARCHAR2
35 , p_locator_id IN NUMBER
36 , p_planning_org_id IN NUMBER
37 , p_planning_tp_type IN NUMBER
38 , p_owning_org_id IN NUMBER
39 , p_owning_tp_type IN NUMBER
40 ) IS
41 BEGIN
42 IF p_subinventory_code IS NULL THEN
43 OPEN x_lot_num_lov FOR
44 SELECT DISTINCT mln.lot_number
45 , mln.description
46 , mln.expiration_date
47 , mmst.status_code
48 FROM mtl_lot_numbers mln
49 , mtl_material_statuses_tl mmst
50 WHERE mln.organization_id = p_organization_id
51 AND mln.inventory_item_id = p_item_id
52 AND mln.lot_number LIKE (p_lot_number)
53 AND mln.status_id = mmst.status_id (+)
54 AND mmst.language (+) = userenv('LANG')
55 AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moqd
56 WHERE moqd.lot_number = mln.lot_number
57 AND moqd.inventory_item_id = mln.inventory_item_id
58 AND moqd.organization_id = mln.organization_id
59 AND ((moqd.containerized_flag = 1 AND p_lpn_id IS NOT NULL)
60 OR (moqd.containerized_flag = 2 AND p_lpn_id IS NULL))
61 AND (p_planning_org_id IS NULL
62 OR moqd.planning_organization_id = p_planning_org_id)
63 AND (p_planning_tp_type IS NULL
64 OR moqd.planning_tp_type = p_planning_tp_type)
65 AND (p_owning_org_id IS NULL
66 OR moqd.owning_organization_id = p_owning_org_id)
67 AND (p_owning_tp_type IS NULL
68 OR moqd.owning_tp_type = p_owning_tp_type))
69 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y'
70 -- Bug 5018199
71 UNION
72 SELECT DISTINCT mln.parent_lot_number
73 , mln.description
74 , mln.expiration_date
75 , mmst.status_code
76 FROM mtl_lot_numbers mln
77 , mtl_material_statuses_tl mmst
78 WHERE mln.organization_id = p_organization_id
79 AND mln.inventory_item_id = p_item_id
80 AND mln.lot_number LIKE (p_lot_number)
81 AND mln.status_id = mmst.status_id (+)
82 AND mmst.language (+) = userenv('LANG')
83 AND mln.parent_lot_number IS NOT NULL
84 AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moqd
85 WHERE moqd.lot_number = mln.lot_number
86 AND moqd.inventory_item_id = mln.inventory_item_id
87 AND moqd.organization_id = mln.organization_id
88 AND ((moqd.containerized_flag = 1 AND p_lpn_id IS NOT NULL)
89 OR (moqd.containerized_flag = 2 AND p_lpn_id IS NULL))
90 AND (p_planning_org_id IS NULL
91 OR moqd.planning_organization_id = p_planning_org_id)
92 AND (p_planning_tp_type IS NULL
93 OR moqd.planning_tp_type = p_planning_tp_type)
94 AND (p_owning_org_id IS NULL
95 OR moqd.owning_organization_id = p_owning_org_id)
96 AND (p_owning_tp_type IS NULL
97 OR moqd.owning_tp_type = p_owning_tp_type))
98 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y';
99 -- End Bug 5018199
100 ELSE
101 OPEN x_lot_num_lov FOR
102 SELECT DISTINCT mln.lot_number
103 , mln.description
104 , mln.expiration_date
105 , mmst.status_code
106 FROM mtl_lot_numbers mln
107 , mtl_material_statuses_tl mmst
108 WHERE mln.organization_id = p_organization_id
109 AND mln.inventory_item_id = p_item_id
110 AND mln.lot_number LIKE (p_lot_number)
111 AND mln.status_id = mmst.status_id (+)
112 AND mmst.language (+) = userenv('LANG')
113 AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moqd
114 WHERE moqd.lot_number = mln.lot_number
115 AND moqd.inventory_item_id = mln.inventory_item_id
116 AND moqd.organization_id = mln.organization_id
117 AND ((moqd.containerized_flag = 1 AND p_lpn_id IS NOT NULL)
118 OR (moqd.containerized_flag = 2 AND p_lpn_id IS NULL))
119 AND moqd.subinventory_code = p_subinventory_code
120 AND NVL(moqd.locator_id, -1) = NVL(NVL(p_locator_id, moqd.locator_id), -1)
121 AND (p_planning_org_id IS NULL
122 OR moqd.planning_organization_id = p_planning_org_id)
123 AND (p_planning_tp_type IS NULL
124 OR moqd.planning_tp_type = p_planning_tp_type)
125 AND (p_owning_org_id IS NULL
126 OR moqd.owning_organization_id = p_owning_org_id)
127 AND (p_owning_tp_type IS NULL
128 OR moqd.owning_tp_type = p_owning_tp_type))
129 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y';
130 --Passed p_subinventory_code and p_locator_id in is_status_applicable call for Onhand status support project ,Bug#6633612
131 END IF;
132 END get_lot_lov;
133
134 -- Name: GET_LOT_LOV_FOR_RECEIVING
135 --
136 -- Input parameters:
137 -- p_Organization_Id which restricts LOV SQL to current org
138 -- p_item_id Inventory Item id
139 -- p_lot_number which restricts LOV SQL to the user input text
140 --
141 -- Output parameters:
142 -- x_lot_num_lov returns LOV rows as reference cursor
143 --
144 -- Functions: This API returns Lot number for a given org and
145 -- and Item Id
146 --
147 --
148
149 PROCEDURE get_lot_lov_for_receiving(
150 x_lot_num_lov OUT NOCOPY t_genref
151 , p_organization_id IN NUMBER
152 , p_item_id IN NUMBER
153 , p_lot_number IN VARCHAR2
154 , p_transaction_type_id IN NUMBER
155 , p_wms_installed IN VARCHAR2
156 , p_lpn_id IN NUMBER
157 , p_subinventory_code IN VARCHAR2
158 , p_locator_id IN NUMBER
159 ) IS
160 BEGIN
161 OPEN x_lot_num_lov FOR
162 SELECT DISTINCT mln.lot_number
163 , mln.description
164 , mln.expiration_date
165 , mmst.status_code
166 FROM mtl_lot_numbers mln,
167 mtl_material_statuses_tl mmst
168 WHERE mln.organization_id = p_organization_id
169 AND mln.inventory_item_id = p_item_id
170 AND mln.lot_number LIKE (p_lot_number)
171 AND NVL(mln.disable_flag,'2') = '2' --Bug#4108798 Disabled lots must not be displayed
172 AND NVL(mln.expiration_date,sysdate +1 ) > sysdate -- Expired lots must not be displayed . -- Bug#5360600 - Items with null expiration date should be displayed.
173 AND mln.status_id = mmst.status_id (+)
174 AND mmst.language (+) = userenv('LANG')
175 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code, p_locator_id , mln.lot_number, NULL, 'O') = 'Y';
176 END get_lot_lov_for_receiving;
177
178 -- Name: ASN_LOT_LOV
179 --
180 -- Input parameters:
181 -- p_Organization_Id which restricts LOV SQL to current org
182 -- p_item_id Inventory Item id
183 -- p_lot_number which restricts LOV SQL to the user input text
184 -- p_source_header_id which restricts to the shipment
185 --
186 -- Output parameters:
187 -- x_lot_num_lov returns LOV rows as reference cursor
188 --
189 -- Functions: This API returns Lot number for a given org and
190 -- and Item Id
191 --
192 --
193
194 PROCEDURE asn_lot_lov(
195 x_lot_num_lov OUT NOCOPY t_genref
196 , p_organization_id IN NUMBER
197 , p_item_id IN NUMBER
198 , p_lot_number IN VARCHAR2
199 , p_transaction_type_id IN NUMBER
200 , p_wms_installed IN VARCHAR2
201 , p_lpn_id IN NUMBER
202 , p_subinventory_code IN VARCHAR2
203 , p_locator_id IN NUMBER
204 , p_source_header_id IN NUMBER
205 ) IS
206 BEGIN
207 OPEN x_lot_num_lov FOR
208 SELECT DISTINCT mln.lot_number
209 , mln.description
210 , mln.expiration_date
211 , mmst.status_code
212 FROM mtl_lot_numbers mln,
213 mtl_material_statuses_tl mmst
214 WHERE mln.organization_id = p_organization_id
215 AND mln.inventory_item_id = p_item_id
216 AND mln.lot_number LIKE (p_lot_number)
217 AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moq
218 WHERE moq.lot_number = mln.lot_number
219 AND moq.inventory_item_id = mln.inventory_item_id
220 AND moq.organization_id = mln.organization_id)
221 AND mln.status_id = mmst.status_id (+)
222 AND mmst.language (+) = userenv('LANG')
223 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
224 UNION
225 SELECT DISTINCT mln.lot_number
226 , mln.description
227 , mln.expiration_date
228 , mmst.status_code
229 FROM mtl_lot_numbers mln, wms_asn_details wad,
230 rcv_shipment_headers rsh, mtl_material_statuses_tl mmst
231 WHERE mln.organization_id = p_organization_id
232 AND mln.inventory_item_id = p_item_id
233 AND mln.lot_number LIKE (p_lot_number)
234 AND mln.lot_number = wad.lot_number_expected
235 AND mln.inventory_item_id = wad.item_id
236 AND mln.organization_id = wad.organization_id
237 AND mln.status_id = mmst.status_id (+)
238 AND mmst.language (+) = userenv('LANG')
239 AND wad.organization_id = p_organization_id
240 AND wad.discrepancy_reporting_context = 'O'
241 AND wad.item_id = p_item_id
242 AND wad.shipment_num = rsh.shipment_num
243 AND rsh.shipment_header_id = p_source_header_id
244 --Bug5726837:Added the following union to take care of direct delivery cases
245 --when there is no data present in wms_asn_details and moqd.
246 UNION
247 SELECT rls.lot_num lot_number
248 , mln.description
249 , mln.expiration_date
250 , mmst.status_code
251 FROM mtl_lot_numbers mln, rcv_lots_supply rls,
252 rcv_shipment_lines rsl, mtl_material_statuses_tl mmst
253 WHERE rls.shipment_line_id = rsl.shipment_line_id
254 AND rsl.shipment_header_id = p_source_header_id
255 AND rsl.to_organization_id = p_organization_id
256 AND rsl.item_id = p_item_id
257 AND rls.supply_type_code = 'SHIPMENT'
258 AND mln.inventory_item_id = p_item_id
259 AND rls.lot_num = mln.lot_number
260 AND rls.lot_num LIKE (p_lot_number)
261 AND mln.status_id = mmst.status_id (+)
262 AND mmst.language (+) = userenv('LANG')
263 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL,p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code , p_locator_id , lot_number, NULL, 'O') = 'Y'
264 GROUP BY rls.lot_num, mln.description, mln.expiration_date, mmst.status_code
265 HAVING SUM(rls.primary_quantity) > 0;
266 END asn_lot_lov;
267
268 -- Name: GET_LOT_LOV_INT_SHP_RCV
269 --
270 -- Input parameters:
271 -- p_Organization_Id which restricts LOV SQL to current org
272 -- p_item_id Inventory Item id
273 -- p_shipment_header_id
274 -- p_lot_number which restricts LOV SQL to the user input text
275 --
276 -- Output parameters:
277 -- x_lot_num_lov returns LOV rows as reference cursor
278 --
279 -- Functions: This API returns Lot number for a given org, lpn
280 -- and Item Id
281 --
282 --
283 -- Added p_subinventory_code and p_locator_id parameters as part of onhand status support project
284 PROCEDURE get_lot_lov_int_shp_rcv(x_lot_num_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER, p_shipment_header_id IN NUMBER,p_lot_number IN VARCHAR2, p_transaction_type_id IN NUMBER,
285 p_wms_installed IN VARCHAR2 ,p_subinventory_code IN VARCHAR2,p_locator_id IN NUMBER
286 , p_from_lpn_id IN NUMBER DEFAULT NULL --Bug 6908946
287 ) IS
288 l_lot_control_code NUMBER;
289 BEGIN
290 BEGIN
291 SELECT msik.lot_control_code
292 INTO l_lot_control_code
293 FROM mtl_system_items_kfv msik, rcv_shipment_lines rsl
294 WHERE msik.inventory_item_id = p_item_id
295 AND msik.inventory_item_id = rsl.item_id --Bug 4235750
296 AND rsl.shipment_header_id = p_shipment_header_id
297 AND rsl.to_organization_id = p_organization_id
298 AND rsl.from_organization_id = msik.organization_id
299 AND Nvl(rsl.asn_lpn_id,-1) = Nvl(decode(p_from_lpn_id,0,NULL,p_from_lpn_id),Nvl(rsl.asn_lpn_id,-1)) --Bug 6908946
300 AND rownum < 2; --Bug 4235750
301 EXCEPTION
302 WHEN OTHERS THEN
303 l_lot_control_code := 2;
304 END;
305
306 IF l_lot_control_code = 2 THEN
307 OPEN x_lot_num_lov FOR
308 SELECT rls.lot_num lot_number
309 , mlnv.description
310 , mlnv.expiration_date
311 , mmst.status_code
312 FROM mtl_lot_numbers mlnv, rcv_lots_supply rls,
313 rcv_shipment_lines rsl, mtl_material_statuses_tl mmst
314 WHERE rls.shipment_line_id = rsl.shipment_line_id
315 AND rsl.shipment_header_id = p_shipment_header_id
316 AND rsl.to_organization_id = p_organization_id
317 AND rsl.item_id = p_item_id
318 AND Nvl(rsl.asn_lpn_id,-1) = Nvl(decode(p_from_lpn_id,0,NULL,p_from_lpn_id),Nvl(rsl.asn_lpn_id,-1)) --Bug 6908946
319 AND mlnv.organization_id = rsl.from_organization_id
320 AND mlnv.inventory_item_id = p_item_id
321 AND rls.lot_num = mlnv.lot_number
322 AND rls.lot_num LIKE (p_lot_number)
323 AND rls.SUPPLY_TYPE_CODE = 'SHIPMENT' --Bug 6908946
324 AND mlnv.status_id = mmst.status_id (+)
325 AND mmst.language (+) = userenv('LANG')
326 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code , p_locator_id , lot_number, NULL, 'O') = 'Y'
327 GROUP BY rls.lot_num, mlnv.description, mlnv.expiration_date, mmst.status_code
328 HAVING SUM(rls.quantity) > 0;
329 ELSE --Added p_subinventory_code and p_locator_id in below call:
330 get_lot_lov_for_receiving(x_lot_num_lov, p_organization_id, p_item_id, p_lot_number, p_transaction_type_id, p_wms_installed, NULL, p_subinventory_code, p_locator_id);
331 END IF;
332 END get_lot_lov_int_shp_rcv;
333
334 -- Name: GET_PACK_LOT_LOV
335 --
336 -- Input parameters:
337 -- p_Organization_Id which restricts LOV SQL to current org
341 -- Output parameters:
338 -- p_item_id Inventory Item id
339 -- p_lot_number which restricts LOV SQL to the user input text
340 --
342 -- x_lot_num_lov returns LOV rows as reference cursor
343 --
344 -- Functions: This API returns Lot number for a given org and
345 -- and Item Id
346 --
347 --
348
349 PROCEDURE get_pack_lot_lov(
350 x_lot_num_lov OUT NOCOPY t_genref
351 , p_organization_id IN NUMBER
352 , p_item_id IN NUMBER
353 , p_revision IN VARCHAR2 := NULL
354 , p_subinventory_code IN VARCHAR2 := NULL
355 , p_locator_id IN NUMBER := 0
356 , p_lot_number IN VARCHAR2
357 , p_transaction_type_id IN NUMBER := 0
358 , p_wms_installed IN VARCHAR2 := 'TRUE'
359 ) IS
360 BEGIN
361 OPEN x_lot_num_lov FOR
362 SELECT DISTINCT mln.lot_number
363 , mln.description
364 , mln.expiration_date
365 , mmst.status_code
366 FROM mtl_lot_numbers mln,
367 mtl_material_statuses_tl mmst
368 WHERE mln.organization_id = p_organization_id
369 AND mln.inventory_item_id = p_item_id
370 AND mln.lot_number LIKE (p_lot_number)
371 AND exists ( SELECT '1' FROM mtl_onhand_quantities_detail moq
372 WHERE mln.lot_number = moq.lot_number
373 AND moq.organization_id = p_organization_id
374 AND moq.lpn_id IS NULL -- added for bug 4614645
375 AND NVL(moq.revision, '@') = NVL(p_revision, NVL(moq.revision, '@'))
376 AND NVL(moq.subinventory_code, '@') = NVL(p_subinventory_code, NVL(moq.subinventory_code, '@'))
377 AND NVL(moq.locator_id, -999) = NVL(p_locator_id, NVL(moq.locator_id, -999))
378 AND moq.inventory_item_id = p_item_id)
379 AND mln.status_id = mmst.status_id (+)
380 AND mmst.language (+) = userenv('LANG')
381 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y';
382 END get_pack_lot_lov;
383
384 -- Name: GET_CGUPDATE_LOT_LOV
385 --
386 -- Input parameters:
387 -- p_Organization_Id restricts LOV SQL to current org
388 -- p_lpn_id
389 -- p_inventory_item_id restricts LOV SQL to Inventory Item id
390 -- p_revision
391 -- p_subinventory_code
392 -- p_locator_id
393 -- p_from_cost_Group_id
394 -- p_lot_number restricts LOV SQL to the user input text
395 --
396 -- Output parameters:
397 -- x_lot_num_lov returns LOV rows as reference cursor
398 --
399 -- Functions: This API returns Lot number for a given org and
400 -- and Item Id
401 --
402
403 PROCEDURE get_cgupdate_lot_lov(
404 x_lot_num_lov OUT NOCOPY t_genref
405 , p_organization_id IN NUMBER
406 , p_lpn_id IN NUMBER
407 , p_inventory_item_id IN NUMBER
408 , p_revision IN VARCHAR2
409 , p_subinventory_code IN VARCHAR2
410 , p_locator_id IN NUMBER
411 , p_from_cost_group_id IN NUMBER
412 , p_lot_number IN VARCHAR2
413 ) IS
414 BEGIN
415 IF p_lpn_id IS NULL THEN
416 OPEN x_lot_num_lov FOR
417 SELECT DISTINCT moq.lot_number
418 , mln.description
419 , mln.expiration_date
420 , mmst.status_code
421 FROM mtl_lot_numbers mln, mtl_onhand_quantities_detail moq,
422 mtl_material_statuses_tl mmst
423 WHERE mln.lot_number = moq.lot_number
424 AND mln.inventory_item_id = p_inventory_item_id
425 AND mln.organization_id = p_organization_id
426 AND mln.status_id = mmst.status_id (+)
427 AND mmst.language (+) = userenv('LANG')
428 AND inv_material_status_grp.is_status_applicable('TRUE', NULL, 86, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code , p_locator_id , moq.lot_number, NULL, 'O') = 'Y'
429 AND moq.lot_number LIKE (p_lot_number)
430 AND (moq.cost_group_id = p_from_cost_group_id
431 OR p_from_cost_group_id IS NULL
432 )
433 AND (moq.revision = p_revision
434 OR (moq.revision IS NULL
435 AND p_revision IS NULL
436 )
437 )
438 AND moq.containerized_flag = 2
439 AND moq.inventory_item_id = p_inventory_item_id
440 AND moq.locator_id = p_locator_id
441 AND moq.subinventory_code = p_subinventory_code
442 AND moq.organization_id = p_organization_id
443 ORDER BY moq.lot_number;
444 ELSE --As lpn_id is not null ,hence not passing sub and loc in is_status_applicable
445 OPEN x_lot_num_lov FOR
446 SELECT DISTINCT wlc.lot_number
447 , mln.description
448 , mln.expiration_date
449 , mmst.status_code
450 FROM mtl_lot_numbers mln, wms_lpn_contents wlc,
451 mtl_material_statuses_tl mmst
452 WHERE mln.lot_number = wlc.lot_number
453 AND mln.inventory_item_id = p_inventory_item_id
454 AND mln.organization_id = p_organization_id
455 AND mln.status_id = mmst.status_id (+)
456 AND mmst.language (+) = userenv('LANG')
457 AND inv_material_status_grp.is_status_applicable('TRUE', NULL, 86, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, NULL, wlc.lot_number, NULL, 'O') = 'Y'
461 )
458 AND wlc.lot_number LIKE (p_lot_number)
459 AND (wlc.cost_group_id = p_from_cost_group_id
460 OR p_from_cost_group_id IS NULL
462 AND (wlc.revision = p_revision
463 OR (wlc.revision IS NULL
464 AND p_revision IS NULL
465 )
466 )
467 AND wlc.inventory_item_id = p_inventory_item_id
468 AND wlc.parent_lpn_id = p_lpn_id
469 ORDER BY wlc.lot_number;
470 END IF;
471 END get_cgupdate_lot_lov;
472
473 -- Name: GET_INQ_LOT_LOV
474 --
475 -- Input parameters:
476 -- p_Organization_Id which restricts LOV SQL to current org
477 -- p_item_id Inventory Item id
478 -- p_lot_number which restricts LOV SQL to the user input text
479 --
480 -- Output parameters:
481 -- x_lot_num_lov returns LOV rows as reference cursor
482 --
483 -- Functions: This API returns Lot number for a given org and
484 -- and Item Id without status restrictiong for inquiry purpose.
485 --
486
487 PROCEDURE get_inq_lot_lov(x_lot_num_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER, p_lot_number IN VARCHAR2) IS
488 BEGIN
489 OPEN x_lot_num_lov FOR
490 SELECT mln.lot_number
491 , mln.description
492 , mln.expiration_date
493 , mmst.status_code
494 FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
495 WHERE mln.organization_id = p_organization_id
496 AND mln.inventory_item_id = p_item_id
497 AND mln.status_id = mmst.status_id (+)
498 AND mmst.language (+) = userenv('LANG')
499 AND mln.lot_number LIKE (p_lot_number);
500 END get_inq_lot_lov;
501
502 -- Name: GET_FROM_STATUS_LOT_LOV
503 --
504 -- Input parameters:
505 -- p_Organization_Id which restricts LOV SQL to current org
506 -- p_item_id Inventory Item id
507 -- p_lot_number which restricts LOV SQL to the user input text
508 --
509 -- Output parameters:
510 -- x_lot_num_lov returns LOV rows as reference cursor
511 --
512 -- Functions: This API returns Lot number for a given org and
513 -- and Item Id
514 --
515
516 PROCEDURE get_from_status_lot_lov(x_lot_num_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER, p_lot_number IN VARCHAR2) IS
517 BEGIN
518 OPEN x_lot_num_lov FOR
519 SELECT mln.lot_number
520 , mln.description
521 , mln.expiration_date
522 , mmst.status_code
523 FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
524 WHERE mln.organization_id = p_organization_id
525 AND mln.inventory_item_id = p_item_id
526 AND mln.status_id = mmst.status_id (+)
527 AND mmst.language (+) = userenv('LANG')
528 AND mln.lot_number LIKE (p_lot_number)
529 ORDER BY mln.lot_number;
530 END get_from_status_lot_lov;
531
532 -- Name: GET_TO_STATUS_LOT_LOV
533 --
534 -- Input parameters:
535 -- p_Organization_Id which restricts LOV SQL to current org
536 -- p_item_id Inventory Item id
537 -- p_lot_number which restricts LOV SQL to the user input text
538 -- p_from_lot_number starting lot number
539 --
540 -- Output parameters:
541 -- x_lot_num_lov returns LOV rows as reference cursor
542 --
543 -- Functions: This API returns Lot number for a given org and
544 -- and Item Id
545 --
546
547 PROCEDURE get_to_status_lot_lov(x_lot_num_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER, p_from_lot_number IN VARCHAR2, p_lot_number IN VARCHAR2) IS
548 BEGIN
549 OPEN x_lot_num_lov FOR
550 SELECT mln.lot_number
551 , mln.description
552 , mln.expiration_date
553 , mmst.status_code
554 FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
555 WHERE mln.organization_id = p_organization_id
556 AND mln.inventory_item_id = p_item_id
557 AND mln.status_id = mmst.status_id (+)
558 AND mmst.language (+) = userenv('LANG')
559 AND mln.lot_number >= p_from_lot_number
560 AND mln.lot_number LIKE (p_lot_number)
561 ORDER BY mln.lot_number;
562 END get_to_status_lot_lov;
563
564 -- Name: GET_REASON_LOV
565 --
566 -- Input parameters:
567 -- p_reason which restricts LOV SQL to the user input text
568 --
569 -- Output parameters:
570 -- x_reason_lov returns LOV rows as reference cursor
571 --
572 -- Functions: This API returns Transaction Reasons
573 --
574
575 PROCEDURE get_reason_lov(x_reason_lov OUT NOCOPY t_genref, p_reason IN VARCHAR2) IS
576 BEGIN
577 OPEN x_reason_lov FOR
578 SELECT reason_name
579 , description
580 , reason_id
581 FROM mtl_transaction_reasons
582 WHERE reason_name LIKE (p_reason)
583 AND nvl(DISABLE_DATE,SYSDATE+1) > SYSDATE ;
584 END;
585
586 -- Name: GET_REASON_LOV
587 -- Overloaed Procedure
588 -- Input parameters:
589 -- p_reason restricts LOV SQL to the user input text
593 --
590 -- p_txn_type_id restricts LOV SQL specific transaction type id.
591 -- Output parameters:
592 -- x_reason_lov returns LOV rows as reference cursor
594 -- Functions: This API returns Transaction Reasons
595 --
596
597 PROCEDURE get_reason_lov(x_reason_lov OUT NOCOPY t_genref, p_reason IN VARCHAR2, p_txn_type_id IN VARCHAR2 ) IS
598 BEGIN
599 OPEN x_reason_lov FOR
600 SELECT reason_name
601 , description
602 , reason_id
603 FROM mtl_transaction_reasons
604 WHERE reason_name LIKE (p_reason)
605 AND nvl(DISABLE_DATE,SYSDATE+1) > SYSDATE
606 -- nsrivast, invconv , transaction reason security
607 AND ( NVL ( fnd_profile.value_wnps('INV_TRANS_REASON_SECURITY'), 'N') = 'N'
608 OR
609 reason_id IN (SELECT reason_id FROM mtl_trans_reason_security mtrs
610 WHERE(( responsibility_id = fnd_global.resp_id OR NVL(responsibility_id, -1) = -1 )
611 AND
612 ( mtrs.transaction_type_id = p_txn_type_id OR NVL(mtrs.transaction_type_id, -1) = -1 )
613 )-- where ends
614 )-- select ends
615 ) -- and condn ends ,-- nsrivast, invconv
616 ORDER BY REASON_NAME
617
618 ;
619 END;
620
621 -- Procedure overloaded for Transaction Reason Security build. 4505091, nsrivast
622 PROCEDURE get_to_org_lov(x_to_org_lov OUT NOCOPY t_genref, p_from_organization_id IN NUMBER, p_to_organization_code IN VARCHAR2) IS
623 BEGIN
624 OPEN x_to_org_lov FOR
625 SELECT DISTINCT org.organization_id
626 , org.organization_code
627 , org.organization_name
628 FROM org_organization_definitions org, mtl_system_items msi
629 WHERE org.organization_id <> p_from_organization_id
630 AND org.organization_id = msi.organization_id
631 AND msi.inventory_item_id IN (SELECT inventory_item_id
632 FROM mtl_system_items
633 WHERE organization_id = p_from_organization_id)
634 AND org.organization_code LIKE (p_to_organization_code);
635 END get_to_org_lov;
636
637 -- used by org transfer
638
639 PROCEDURE get_to_org(x_organizations OUT NOCOPY t_genref, p_from_organization_id IN NUMBER, p_to_organization_code IN VARCHAR2) IS
640 BEGIN
641 OPEN x_organizations FOR
642 SELECT a.to_organization_id
643 , b.organization_code
644 , c.NAME
645 , a.intransit_type
646 FROM mtl_interorg_parameters a, mtl_parameters b, hr_all_organization_units c
647 WHERE a.from_organization_id = p_from_organization_id
648 AND a.to_organization_id = b.organization_id
649 AND a.to_organization_id = c.organization_id
650 AND a.internal_order_required_flag = 2
651 AND b.organization_code LIKE (p_to_organization_code)
652 ORDER BY 2;
653 END get_to_org;
654
655 PROCEDURE get_all_orgs(x_organizations OUT NOCOPY t_genref, p_organization_code IN VARCHAR2) IS
656 BEGIN
657 OPEN x_organizations FOR
658 /* SELECT DISTINCT organization_id
659 , organization_code
660 , organization_name
661 , 0
662 FROM org_organization_definitions
663 WHERE organization_code LIKE (p_organization_code)
664 ORDER BY 2; */
665 --Bug 2649387
666 SELECT distinct mp.organization_id
667 , mp.organization_code
668 , hu.name organization_name
669 ,0
670 FROM mtl_parameters mp
671 , hr_organization_units hu
672 WHERE mp.organization_code LIKE (p_organization_code || '%')
673 and mp.organization_id = hu.organization_id order by 2;
674 END get_all_orgs;
675
676 PROCEDURE get_cost_group(x_cost_group_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_cost_group IN VARCHAR2) IS
677 BEGIN
678 OPEN x_cost_group_lov FOR
679 -- Bug 6378032 Cost group LOV was fetching all cost groups irrespective of
680 -- organization
681 /* SELECT cost_group
682 , cost_group_id
683 , description
684 FROM cst_cost_groups
685 WHERE cost_group LIKE (p_cost_group); */
686 select ccg.cost_group ,ccga.cost_group_id, ccg.description
687 from cst_cost_groups ccg, cst_cost_group_accounts ccga
688 where ccg.cost_group_id = ccga.cost_group_id
689 and ccga.organization_id = nvl(p_organization_id, ccga.organization_id)
690 --WHERE organization_id = p_organization_id
691 and ccg.cost_group LIKE (p_cost_group);
692 END get_cost_group;
693
694
695 -- Name: GET_CGUPDATE_COST_GROUP
696 --
697 -- Input parameters:
698 -- p_organization_id Restricts LOV SQL to specific org
699 -- p_lpn_id Restricts LOV SQL to specific LPN
700 -- p_inventory_item_id Restricts LOV SQL to specific item
701 -- p_subinventory Restricts LOV SQL to specific sub
702 -- p_locator_id Restricts LOV SQL to specific loc if given
703 -- p_from_cost_group_id Restricts LOV SQL to not include the
704 -- from cost group if not null
708 -- Output parameters:
705 -- p_from_cost_group Restricts LOV SQL to user input text
706 -- p_to_cost_group Restricts LOV SQL to user input text
707 --
709 -- x_cost_group_lov Output reference cursor which stores
710 -- the LOV rows for valid cost groups
711 --
712 -- Functions: This API returns a reference cursor for valid cost groups
713 -- in Cost Group update UI associated with the given parameters
714 --
715 /* PJM-WMS Integration:
716 /* Return only Cost Groups of types other than 'project'.*/
717 PROCEDURE get_cgupdate_cost_group(
718 x_cost_group_lov OUT NOCOPY t_genref
719 , p_organization_id IN NUMBER
720 , p_lpn_id IN NUMBER
721 , p_inventory_item_id IN NUMBER
722 , p_revision IN VARCHAR2
723 , p_subinventory_code IN VARCHAR2
724 , p_locator_id IN NUMBER
725 , p_from_cost_group_id IN NUMBER
726 , p_from_cost_group IN VARCHAR2
727 , p_to_cost_group IN VARCHAR2
728 ) IS
729 BEGIN
730 IF p_from_cost_group_id IS NULL THEN
731 IF p_lpn_id IS NULL THEN
732 OPEN x_cost_group_lov FOR
733 SELECT ccg.cost_group
734 , ccg.cost_group_id
735 , ccg.description
736 FROM cst_cost_groups ccg, mtl_onhand_quantities_detail moq
737 WHERE ccg.cost_group LIKE (p_from_cost_group)
738 AND ccg.cost_group_id = moq.cost_group_id
739 AND ccg.cost_group_type <> 1 --PJM-WMS Integration
740 AND ((moq.revision = p_revision)
741 OR (moq.revision IS NULL
742 AND p_revision IS NULL
743 )
744 )
745 AND moq.containerized_flag = 2
746 AND moq.inventory_item_id = p_inventory_item_id
747 AND moq.locator_id = p_locator_id
748 AND moq.subinventory_code = p_subinventory_code
749 AND moq.organization_id = p_organization_id
750 GROUP BY ccg.cost_group, ccg.cost_group_id, ccg.description
751 ORDER BY ccg.cost_group;
752 ELSE
753 OPEN x_cost_group_lov FOR
754 SELECT ccg.cost_group
755 , ccg.cost_group_id
756 , ccg.description
757 FROM cst_cost_groups ccg, wms_lpn_contents wlc
758 WHERE ccg.cost_group LIKE (p_from_cost_group)
759 AND ccg.cost_group_id = wlc.cost_group_id
760 AND ccg.cost_group_type <> 1 --PJM-WMS Integration
761 AND ((wlc.revision = p_revision)
762 OR (wlc.revision IS NULL
763 AND p_revision IS NULL
764 )
765 )
766 AND wlc.inventory_item_id = p_inventory_item_id
767 AND wlc.parent_lpn_id = p_lpn_id
768 GROUP BY ccg.cost_group, ccg.cost_group_id, ccg.description
769 ORDER BY ccg.cost_group;
770 END IF;
771 ELSE
772 OPEN x_cost_group_lov FOR
773 SELECT ccg.cost_group
774 , ccg.cost_group_id
775 , ccg.description
776 FROM cst_cost_groups ccg, cst_cost_group_accounts ccga
777 WHERE ccg.cost_group LIKE (p_to_cost_group)
778 AND ccg.cost_group_id = ccga.cost_group_id
779 AND ccga.cost_group_id <> p_from_cost_group_id
780 AND ccg.cost_group_type <> 1 --PJM-WMS Integration
781 AND ccga.organization_id = p_organization_id
782 GROUP BY ccg.cost_group, ccg.cost_group_id, ccg.description
783 ORDER BY ccg.cost_group;
784 END IF;
785 END get_cgupdate_cost_group;
786
787 PROCEDURE get_phyinv_cost_group(
788 x_cost_group_lov OUT NOCOPY t_genref
789 , p_organization_id IN NUMBER
790 , p_cost_group IN VARCHAR2
791 , p_inventory_item_id IN NUMBER
792 , p_subinventory IN VARCHAR2
793 , p_locator_id IN NUMBER
794 , p_dynamic_entry_flag IN NUMBER
795 , p_physical_inventory_id IN NUMBER
796 , p_parent_lpn_id IN NUMBER
797 ) IS
798 BEGIN
799 IF (p_dynamic_entry_flag = 1) THEN -- Dynamic entries are allowed
800 OPEN x_cost_group_lov FOR
801 SELECT cost_group
802 , cost_group_id
803 , description
804 FROM cst_cost_groups
805 WHERE organization_id = p_organization_id
806 AND cost_group LIKE (p_cost_group);
807 ELSE -- Dynamic entries are not allowed
808 OPEN x_cost_group_lov FOR
809 SELECT UNIQUE ccg.cost_group
810 , ccg.cost_group_id
811 , ccg.description
812 FROM cst_cost_groups ccg, mtl_physical_inventory_tags mpit
813 WHERE ccg.organization_id = p_organization_id
814 AND ccg.cost_group LIKE (p_cost_group)
815 AND ccg.cost_group_id = mpit.cost_group_id
816 AND mpit.physical_inventory_id = p_physical_inventory_id
817 AND mpit.organization_id = p_organization_id
818 AND mpit.subinventory = p_subinventory
819 AND NVL(mpit.locator_id, -99999) = NVL(p_locator_id, -99999)
823 AND mpit.adjustment_id IN (SELECT adjustment_id
820 AND mpit.inventory_item_id = p_inventory_item_id
821 AND NVL(mpit.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
822 AND NVL(mpit.void_flag, 2) = 2
824 FROM mtl_physical_adjustments
825 WHERE physical_inventory_id = p_physical_inventory_id
826 AND organization_id = p_organization_id
827 AND approval_status IS NULL);
828 END IF;
829 END get_phyinv_cost_group;
830
831 PROCEDURE get_cyc_cost_group(
832 x_cost_group_lov OUT NOCOPY t_genref
833 , p_organization_id IN NUMBER
834 , p_cost_group IN VARCHAR2
835 , p_inventory_item_id IN NUMBER
836 , p_subinventory IN VARCHAR2
837 , p_locator_id IN NUMBER
838 , p_unscheduled_entry IN NUMBER
839 , p_cycle_count_header_id IN NUMBER
840 , p_parent_lpn_id IN NUMBER
841 ) IS
842 BEGIN
843 IF (p_unscheduled_entry = 1) THEN -- Unscheduled entries are allowed
844 OPEN x_cost_group_lov FOR
845 SELECT cost_group
846 , cost_group_id
847 , description
848 FROM cst_cost_groups
849 WHERE organization_id = p_organization_id
850 AND cost_group LIKE (p_cost_group);
851 ELSE -- Unscheduled entries are not allowed
852 OPEN x_cost_group_lov FOR
853 SELECT UNIQUE ccg.cost_group
854 , ccg.cost_group_id
855 , ccg.description
856 FROM cst_cost_groups ccg, mtl_cycle_count_entries mcce
857 WHERE ccg.organization_id = p_organization_id
858 AND ccg.cost_group LIKE (p_cost_group)
859 AND ccg.cost_group_id = mcce.cost_group_id
860 AND mcce.cycle_count_header_id = p_cycle_count_header_id
861 AND mcce.organization_id = p_organization_id
862 AND mcce.subinventory = p_subinventory
863 AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
864 AND mcce.inventory_item_id = p_inventory_item_id
865 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
866 AND mcce.entry_status_code IN (1, 3);
867 END IF;
868 END get_cyc_cost_group;
869
870 PROCEDURE get_txn_types(x_txntypelov OUT NOCOPY t_genref, p_transaction_action_id IN NUMBER, p_transaction_source_type_id IN NUMBER, p_transaction_type_name IN VARCHAR2) IS
871 BEGIN
872 IF (p_transaction_action_id = 2
873 AND p_transaction_source_type_id = 13
874 ) THEN
875 OPEN x_txntypelov FOR
876 SELECT transaction_type_id
877 , transaction_type_name
878 , description
879 , transaction_action_id
880 FROM mtl_transaction_types
881 WHERE transaction_action_id = p_transaction_action_id
882 AND transaction_source_type_id = p_transaction_source_type_id
883 AND transaction_type_name LIKE (p_transaction_type_name)
884 AND transaction_type_id NOT IN (66, 67, 68);
885 ELSE
886 OPEN x_txntypelov FOR
887 SELECT transaction_type_id
888 , transaction_type_name
889 , description
890 , transaction_action_id
891 FROM mtl_transaction_types
892 WHERE transaction_action_id = p_transaction_action_id
893 AND transaction_source_type_id = p_transaction_source_type_id
894 AND transaction_type_name LIKE (p_transaction_type_name);
895 END IF;
896 END get_txn_types;
897
898 -- Name: GET_ITEM_LOT_LOV
899 --
900 -- Input parameters:
901 -- p_wms_installed which restricts LOV SQL to wms installed
902 -- p_Organization_Id which restricts LOV SQL to current org
903 -- p_txn_type_id which restricts LOV SQL to txn type
904 -- p_inventory_item_id which restricts LOV SQL to inventory item
905 -- p_lot_number which restricts LOV SQL to the user input text
906 -- p_project_id which restricts LOV SQL to project
907 -- p_task_id which restricts LOV SQL to task
908 --
909 -- Output parameters:
910 -- x_lot_num_lov returns LOV rows as reference cursor
911 --
912 -- Functions: This API returns Lot number for a given org and
913 -- and Item Id
914 --
915 --
916 -- Added p_subinventory_code and p_locator_id parameters as part of onhand status support project
917 PROCEDURE get_item_lot_lov(x_lot_num_lov OUT NOCOPY t_genref, p_wms_installed IN VARCHAR2, p_organization_id IN NUMBER, p_txn_type_id IN NUMBER, p_inventory_item_id IN VARCHAR2,
918 p_lot_number IN VARCHAR2, p_project_id IN NUMBER, p_task_id IN NUMBER ,p_subinventory_code IN VARCHAR2 ,p_locator_id IN NUMBER) IS
919 l_inventory_item_id VARCHAR2(100);
920 BEGIN
921 IF p_inventory_item_id IS NULL THEN
922 l_inventory_item_id := '%';
923 ELSE
924 l_inventory_item_id := p_inventory_item_id;
925 END IF;
926
927 IF p_txn_type_id = inv_globals.g_type_inv_lot_split -- Lot Split (82)
928 THEN
932 , msik.concatenated_segments concatenated_segments
929 OPEN x_lot_num_lov FOR
930 SELECT mln.lot_number lot_number
931 , mln.inventory_item_id
933 , msik.description
934 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
935 , mms.status_code status_code
936 , mms.status_id
937 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
938 WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
939 AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
940 AND mln.organization_id = p_organization_id
941 AND mln.organization_id = msik.organization_id
942 AND mln.inventory_item_id = msik.inventory_item_id
943 AND mln.inventory_item_id LIKE l_inventory_item_id
944 AND msik.lot_split_enabled = 'Y'
945 AND mln.lot_number LIKE (p_lot_number)
946 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled,
947 p_organization_id, msik.inventory_item_id, p_locator_id , p_subinventory_code , mln.lot_number, NULL, 'O') = 'Y'
948 UNION ALL
949 SELECT mln.lot_number lot_number
950 , mln.inventory_item_id
951 , msik.concatenated_segments concatenated_segments
952 , msik.description
953 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
954 , NULL status_code
955 , msik.default_lot_status_id -- Bug#2267947
956 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
957 WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
958 AND mln.organization_id = p_organization_id
959 AND mln.organization_id = msik.organization_id
960 AND mln.inventory_item_id = msik.inventory_item_id
961 AND mln.inventory_item_id LIKE l_inventory_item_id
962 AND msik.lot_split_enabled = 'Y'
963 AND mln.lot_number LIKE (p_lot_number)
964 ORDER BY lot_number, concatenated_segments;
965 ELSE
966 IF p_txn_type_id = inv_globals.g_type_inv_lot_merge -- Lot Merge 83
967 THEN
968 IF (p_project_id IS NOT NULL) THEN
969 OPEN x_lot_num_lov FOR
970 SELECT DISTINCT moq.lot_number
971 , moq.inventory_item_id
972 , msik.concatenated_segments concatenated_segments
973 , msik.description
974 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
975 , mms.status_code
976 , mms.status_id
977 FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms, mtl_item_locations mil
978 WHERE moq.organization_id = p_organization_id
979 AND moq.lot_number IS NOT NULL
980 AND moq.organization_id = mil.organization_id
981 AND moq.organization_id = mln.organization_id
982 AND moq.organization_id = msik.organization_id
983 AND mil.segment19 = p_project_id
984 AND (mil.segment20 = p_task_id
985 OR (mil.segment20 IS NULL
986 AND p_task_id IS NULL
987 )
988 )
989 AND mln.lot_number = moq.lot_number
990 AND mms.status_id = msik.default_lot_status_id -- Bug#2267947
991 AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
992 AND mln.inventory_item_id = msik.inventory_item_id
993 AND mln.inventory_item_id LIKE l_inventory_item_id
994 AND msik.lot_merge_enabled = 'Y'
995 AND mln.lot_number LIKE (p_lot_number)
996 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled,
997 p_organization_id, msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
998 UNION ALL
999 SELECT DISTINCT moq.lot_number
1000 , moq.inventory_item_id
1001 , msik.concatenated_segments concatenated_segments
1002 , msik.description
1003 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
1004 , NULL status_code
1005 , msik.default_lot_status_id -- Bug#2267947
1006 FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms, mtl_item_locations mil
1007 WHERE moq.organization_id = p_organization_id
1008 AND moq.lot_number IS NOT NULL
1009 AND moq.organization_id = mil.organization_id
1010 AND moq.organization_id = mln.organization_id
1011 AND moq.organization_id = msik.organization_id
1012 AND mil.segment19 = p_project_id
1013 AND (mil.segment20 = p_task_id
1014 OR (mil.segment20 IS NULL
1015 AND p_task_id IS NULL
1016 )
1020 AND mln.inventory_item_id = msik.inventory_item_id
1017 )
1018 AND mln.lot_number = moq.lot_number
1019 AND msik.default_lot_status_id IS NULL -- Bug#2267947
1021 AND mln.inventory_item_id LIKE l_inventory_item_id
1022 AND msik.lot_merge_enabled = 'Y'
1023 AND mln.lot_number LIKE (p_lot_number)
1024 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled,
1025 p_organization_id, msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
1026 ORDER BY 1, concatenated_segments;
1027 ELSE
1028 OPEN x_lot_num_lov FOR
1029 SELECT mln.lot_number lot_number
1030 , mln.inventory_item_id
1031 , msik.concatenated_segments concatenated_segments
1032 , msik.description
1033 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
1034 , mms.status_code
1035 , mms.status_id
1036 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
1037 WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
1038 AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
1039 AND mln.organization_id = p_organization_id
1040 AND mln.organization_id = msik.organization_id
1041 AND mln.inventory_item_id = msik.inventory_item_id
1042 AND mln.inventory_item_id LIKE l_inventory_item_id
1043 AND msik.lot_merge_enabled = 'Y'
1044 AND mln.lot_number LIKE (p_lot_number)
1045 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled,
1046 p_organization_id, msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
1047 UNION ALL
1048 SELECT mln.lot_number lot_number
1049 , mln.inventory_item_id
1050 , msik.concatenated_segments concatenated_segments
1051 , msik.description
1052 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
1053 , NULL status_code
1054 , msik.default_lot_status_id -- Bug#2267947
1055 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
1056 WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
1057 AND mln.organization_id = p_organization_id
1058 AND mln.organization_id = msik.organization_id
1059 AND mln.inventory_item_id = msik.inventory_item_id
1060 AND mln.inventory_item_id LIKE l_inventory_item_id
1061 AND msik.lot_merge_enabled = 'Y'
1062 AND mln.lot_number LIKE (p_lot_number)
1063 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled,
1064 p_organization_id, msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
1065 ORDER BY lot_number, concatenated_segments;
1066 END IF;
1067 -- For bug 4306954: Added ELSIF condtion for Lot Translate case.
1068 -- SQL st will allow only those rows that are Lot Translate enabled.
1069 ELSIF p_txn_type_id = inv_globals.G_TYPE_INV_LOT_TRANSLATE THEN -- for Lot Translate
1070 OPEN x_lot_num_lov FOR -- Lot Translate 84
1071 SELECT mln.lot_number lot_number
1072 , mln.inventory_item_id
1073 , msik.concatenated_segments concatenated_segments
1074 , msik.description
1075 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
1076 , mms.status_code
1077 , mms.status_id
1078 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
1079 WHERE mms.status_id = msik.default_lot_status_id
1080 AND msik.default_lot_status_id IS NOT NULL
1081 AND mln.organization_id = p_organization_id
1082 AND mln.organization_id = msik.organization_id
1083 AND mln.inventory_item_id = msik.inventory_item_id
1084 AND msik.lot_control_code = 2
1085 AND mln.inventory_item_id LIKE l_inventory_item_id
1086 AND mln.lot_number LIKE (p_lot_number)
1087 AND msik.lot_translate_enabled = 'Y'
1088 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled,
1089 p_organization_id, msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
1090 UNION ALL
1091 SELECT mln.lot_number LN
1092 , mln.inventory_item_id
1093 , msik.concatenated_segments cs
1094 , msik.description
1095 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
1096 , NULL status_code
1097 , msik.default_lot_status_id -- Bug#2267947
1098 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
1099 WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
1100 AND mln.organization_id = p_organization_id
1104 AND mln.inventory_item_id LIKE l_inventory_item_id
1101 AND mln.organization_id = msik.organization_id
1102 AND mln.inventory_item_id = msik.inventory_item_id
1103 AND msik.lot_control_code = 2
1105 AND mln.lot_number LIKE (p_lot_number)
1106 AND msik.lot_translate_enabled = 'Y'
1107 ORDER BY lot_number, concatenated_segments;
1108 ELSE
1109 OPEN x_lot_num_lov FOR
1110 SELECT mln.lot_number lot_number
1111 , mln.inventory_item_id
1112 , msik.concatenated_segments concatenated_segments
1113 , msik.description
1114 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
1115 , mms.status_code
1116 , mms.status_id
1117 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
1118 WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
1119 AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
1120 AND mln.organization_id = p_organization_id
1121 AND mln.organization_id = msik.organization_id
1122 AND mln.inventory_item_id = msik.inventory_item_id
1123 AND msik.lot_control_code = 2
1124 AND mln.inventory_item_id LIKE l_inventory_item_id
1125 AND mln.lot_number LIKE (p_lot_number)
1126 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id,
1127 p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
1128 UNION ALL
1129 SELECT mln.lot_number LN
1130 , mln.inventory_item_id
1131 , msik.concatenated_segments cs
1132 , msik.description
1133 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
1134 , NULL status_code
1135 , msik.default_lot_status_id -- Bug#2267947
1136 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
1137 WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
1138 AND mln.organization_id = p_organization_id
1139 AND mln.organization_id = msik.organization_id
1140 AND mln.inventory_item_id = msik.inventory_item_id
1141 AND msik.lot_control_code = 2
1142 AND mln.inventory_item_id LIKE l_inventory_item_id
1143 AND mln.lot_number LIKE (p_lot_number)
1144 ORDER BY lot_number, concatenated_segments;
1145 END IF;
1146 END IF;
1147 END get_item_lot_lov;
1148
1149 -- Name: GET_ITEM_LOT_LOV, overloaded with Lot Status ID
1150 --
1151 -- Input parameters:
1152 -- p_wms_installed which restricts LOV SQL to wms installed
1153 -- p_Organization_Id which restricts LOV SQL to current org
1154 -- p_txn_type_id which restricts LOV SQL to txn type
1155 -- p_inventory_item_id which restricts LOV SQL to inventory item
1156 -- p_lot_number which restricts LOV SQL to the user input text
1157 -- p_project_id which restricts LOV SQL to project
1158 -- p_task_id which restricts LOV SQL to task
1159 -- p_status_id which restricts LOV SQL to lot_status
1160 --
1161 -- Output parameters:
1162 -- x_lot_num_lov returns LOV rows as reference cursor
1163 --
1164 -- Functions: This API returns Lot number for a given org and
1165 -- and Item Id
1166 --
1167 --
1168
1169 PROCEDURE get_item_lot_lov(
1170 x_lot_num_lov OUT NOCOPY t_genref
1171 , p_wms_installed IN VARCHAR2
1172 , p_organization_id IN NUMBER
1173 , p_txn_type_id IN NUMBER
1174 , p_inventory_item_id IN VARCHAR2
1175 , p_lot_number IN VARCHAR2
1176 , p_project_id IN NUMBER
1177 , p_task_id IN NUMBER
1178 , p_status_id IN NUMBER) IS
1179
1180 l_inventory_item_id VARCHAR2(100);
1181 l_status_id VARCHAR2(100);
1182 l_allow_different_status NUMBER := 0;
1183 BEGIN
1184 IF p_inventory_item_id IS NULL THEN
1185 l_inventory_item_id := '%';
1186 ELSE
1187 l_inventory_item_id := p_inventory_item_id;
1188 END IF;
1189
1190 -- Fetch the all_different_Status from MTL_PARAMETER for the current organization and restrict
1191 -- the lot_status only if the parameter is set to 1.
1192 SELECT allow_different_status INTO l_allow_different_status FROM mtl_parameters WHERE organization_id = p_organization_id;
1193
1194 IF p_status_id IS NOT NULL AND p_status_id <> 0
1195 AND NVL(FND_PROFILE.VALUE('INV_MATERIAL_STATUS'),2) = 1 -- IF INV: Material Status Support is set to Yes
1196 AND l_allow_different_status = 1 THEN -- If Allow Different Lot status is set to 1
1197 l_status_id := p_status_id;
1198 ELSE
1199 l_status_id := '%';
1200 END IF;
1201
1202 IF p_txn_type_id = inv_globals.g_type_inv_lot_split -- Lot Split (82)
1203 THEN
1204 OPEN x_lot_num_lov FOR
1205 SELECT mln.lot_number lot_number
1206 , mln.inventory_item_id
1207 , msik.concatenated_segments concatenated_segments
1208 , msik.description
1209 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
1210 , mms.status_code status_code
1211 , mms.status_id
1215 AND mln.organization_id = p_organization_id
1212 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
1213 WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
1214 AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
1216 AND mln.organization_id = msik.organization_id
1217 AND mln.inventory_item_id = msik.inventory_item_id
1218 AND mln.inventory_item_id LIKE l_inventory_item_id
1219 AND msik.lot_split_enabled = 'Y'
1220 AND mln.lot_number LIKE (p_lot_number)
1221 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y'
1222 UNION ALL
1223 SELECT mln.lot_number lot_number
1224 , mln.inventory_item_id
1225 , msik.concatenated_segments concatenated_segments
1226 , msik.description
1227 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
1228 , NULL status_code
1229 , msik.default_lot_status_id -- Bug#2267947
1230 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
1231 WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
1232 AND mln.organization_id = p_organization_id
1233 AND mln.organization_id = msik.organization_id
1234 AND mln.inventory_item_id = msik.inventory_item_id
1235 AND mln.inventory_item_id LIKE l_inventory_item_id
1236 AND msik.lot_split_enabled = 'Y'
1237 AND mln.lot_number LIKE (p_lot_number)
1238 ORDER BY lot_number, concatenated_segments;
1239 ELSE
1240 IF p_txn_type_id = inv_globals.g_type_inv_lot_merge -- Lot Merge 83
1241 THEN
1242 IF (p_project_id IS NOT NULL) THEN
1243 OPEN x_lot_num_lov FOR
1244 SELECT DISTINCT moq.lot_number
1245 , moq.inventory_item_id
1246 , msik.concatenated_segments concatenated_segments
1247 , msik.description
1248 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
1249 , mms.status_code
1250 , mms.status_id
1251 FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms, mtl_item_locations mil
1252 WHERE moq.organization_id = p_organization_id
1253 AND moq.lot_number IS NOT NULL
1254 AND moq.organization_id = mil.organization_id
1255 AND moq.organization_id = mln.organization_id
1256 AND moq.organization_id = msik.organization_id
1257 AND mil.segment19 = p_project_id
1258 AND (mil.segment20 = p_task_id
1259 OR (mil.segment20 IS NULL
1260 AND p_task_id IS NULL
1261 )
1262 )
1263 AND mln.lot_number = moq.lot_number
1264 AND mms.status_id = msik.default_lot_status_id -- Bug#2267947
1265 AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
1266 AND mln.inventory_item_id = msik.inventory_item_id
1267 AND mln.inventory_item_id LIKE l_inventory_item_id
1268 AND msik.lot_merge_enabled = 'Y'
1269 AND mln.lot_number LIKE (p_lot_number)
1270 AND mln.status_id LIKE (l_status_id) -- restrict to lot_status
1271 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id, NULL, NULL, mln.lot_number, NULL, 'O') =
1272 'Y'
1273 UNION ALL
1274 SELECT DISTINCT moq.lot_number
1275 , moq.inventory_item_id
1276 , msik.concatenated_segments concatenated_segments
1277 , msik.description
1278 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
1279 , NULL status_code
1280 , msik.default_lot_status_id -- Bug#2267947
1281 FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms, mtl_item_locations mil
1282 WHERE moq.organization_id = p_organization_id
1283 AND moq.lot_number IS NOT NULL
1284 AND moq.organization_id = mil.organization_id
1285 AND moq.organization_id = mln.organization_id
1286 AND moq.organization_id = msik.organization_id
1287 AND mil.segment19 = p_project_id
1288 AND (mil.segment20 = p_task_id
1289 OR (mil.segment20 IS NULL
1290 AND p_task_id IS NULL
1291 )
1292 )
1293 AND mln.lot_number = moq.lot_number
1294 AND msik.default_lot_status_id IS NULL -- Bug#2267947
1295 AND mln.inventory_item_id = msik.inventory_item_id
1299 AND mln.status_id LIKE (l_status_id) -- restrict to lot_status
1296 AND mln.inventory_item_id LIKE l_inventory_item_id
1297 AND msik.lot_merge_enabled = 'Y'
1298 AND mln.lot_number LIKE (p_lot_number)
1300 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id, NULL, NULL, mln.lot_number, NULL, 'O') =
1301 'Y'
1302 ORDER BY 1, concatenated_segments;
1303 ELSE
1304 OPEN x_lot_num_lov FOR
1305 SELECT mln.lot_number lot_number
1306 , mln.inventory_item_id
1307 , msik.concatenated_segments concatenated_segments
1308 , msik.description
1309 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
1310 , mms.status_code
1311 , mms.status_id
1312 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
1313 WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
1314 AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
1315 AND mln.organization_id = p_organization_id
1316 AND mln.organization_id = msik.organization_id
1317 AND mln.inventory_item_id = msik.inventory_item_id
1318 AND mln.inventory_item_id LIKE l_inventory_item_id
1319 AND msik.lot_merge_enabled = 'Y'
1320 AND mln.lot_number LIKE (p_lot_number)
1321 AND mln.status_id LIKE (l_status_id) -- restrict to lot_status
1322 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y'
1323 UNION ALL
1324 SELECT mln.lot_number lot_number
1325 , mln.inventory_item_id
1326 , msik.concatenated_segments concatenated_segments
1327 , msik.description
1328 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
1329 , (select status_code from mtl_material_statuses_vl where status_id = mln.status_id) status_code
1330 , mln.status_id -- Bug#2347381
1331 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
1332 WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
1333 AND mln.organization_id = p_organization_id
1334 AND mln.organization_id = msik.organization_id
1335 AND mln.inventory_item_id = msik.inventory_item_id
1336 AND mln.inventory_item_id LIKE l_inventory_item_id
1337 AND msik.lot_merge_enabled = 'Y'
1338 AND mln.lot_number LIKE (p_lot_number)
1339 AND mln.status_id LIKE (l_status_id) -- restrict to lot_status
1340 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y'
1341 ORDER BY lot_number, concatenated_segments;
1342 END IF;
1343 -- For bug 4306954: Added ELSIF condtion for Lot Translate case.
1344 -- SQL st will allow only those rows that are Lot Translate enabled.
1345 ELSIF p_txn_type_id = inv_globals.G_TYPE_INV_LOT_TRANSLATE THEN -- for Lot Translate
1346 OPEN x_lot_num_lov FOR -- Lot Translate 84
1347 SELECT mln.lot_number lot_number
1348 , mln.inventory_item_id
1349 , msik.concatenated_segments concatenated_segments
1350 , msik.description
1351 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
1352 , mms.status_code
1353 , mms.status_id
1354 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
1355 WHERE mms.status_id = msik.default_lot_status_id
1356 AND msik.default_lot_status_id IS NOT NULL
1357 AND mln.organization_id = p_organization_id
1358 AND mln.organization_id = msik.organization_id
1359 AND mln.inventory_item_id = msik.inventory_item_id
1360 AND msik.lot_control_code = 2
1361 AND mln.inventory_item_id LIKE l_inventory_item_id
1362 AND mln.lot_number LIKE (p_lot_number)
1363 AND msik.lot_translate_enabled = 'Y'
1364 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y'
1365 UNION ALL
1366 SELECT mln.lot_number LN
1367 , mln.inventory_item_id
1368 , msik.concatenated_segments cs
1369 , msik.description
1370 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
1371 , NULL status_code
1372 , msik.default_lot_status_id -- Bug#2267947
1373 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
1374 WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
1375 AND mln.organization_id = p_organization_id
1376 AND mln.organization_id = msik.organization_id
1377 AND mln.inventory_item_id = msik.inventory_item_id
1381 AND msik.lot_translate_enabled = 'Y'
1378 AND msik.lot_control_code = 2
1379 AND mln.inventory_item_id LIKE l_inventory_item_id
1380 AND mln.lot_number LIKE (p_lot_number)
1382 ORDER BY lot_number, concatenated_segments;
1383 ELSE
1384 OPEN x_lot_num_lov FOR
1385 SELECT mln.lot_number lot_number
1386 , mln.inventory_item_id
1387 , msik.concatenated_segments concatenated_segments
1388 , msik.description
1389 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
1390 , mms.status_code
1391 , mms.status_id
1392 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
1393 WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
1394 AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
1395 AND mln.organization_id = p_organization_id
1396 AND mln.organization_id = msik.organization_id
1397 AND mln.inventory_item_id = msik.inventory_item_id
1398 AND msik.lot_control_code = 2
1399 AND mln.inventory_item_id LIKE l_inventory_item_id
1400 AND mln.lot_number LIKE (p_lot_number)
1401 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y'
1402 UNION ALL
1403 SELECT mln.lot_number LN
1404 , mln.inventory_item_id
1405 , msik.concatenated_segments cs
1406 , msik.description
1407 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
1408 , NULL status_code
1409 , msik.default_lot_status_id -- Bug#2267947
1410 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
1411 WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
1412 AND mln.organization_id = p_organization_id
1413 AND mln.organization_id = msik.organization_id
1414 AND mln.inventory_item_id = msik.inventory_item_id
1415 AND msik.lot_control_code = 2
1416 AND mln.inventory_item_id LIKE l_inventory_item_id
1417 AND mln.lot_number LIKE (p_lot_number)
1418 ORDER BY lot_number, concatenated_segments;
1419 END IF;
1420 END IF;
1421 END get_item_lot_lov;
1422
1423 -- modified by manu gupta 031601 per request to use concat segs everywhere
1424 PROCEDURE get_account_alias(x_accounts_info OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_description IN VARCHAR2) IS
1425 BEGIN
1426 OPEN x_accounts_info FOR
1427 SELECT distribution_account
1428 , disposition_id
1429 , concatenated_segments
1430 FROM mtl_generic_dispositions_kfv
1431 WHERE organization_id = p_organization_id
1432 AND ((concatenated_segments LIKE ('%'|| p_description))
1433 OR (concatenated_segments IS NULL
1434 AND p_description IS NULL
1435 )
1436 OR (concatenated_segments IS NULL
1437 AND p_description = '%'
1438 )
1439 )
1440 AND enabled_flag = 'Y'
1441 AND NVL(effective_date, SYSDATE - 1) <= SYSDATE
1442 AND NVL(disable_date, SYSDATE + 1) > SYSDATE
1443 ORDER BY concatenated_segments;
1444 END get_account_alias;
1445
1446 PROCEDURE get_accounts(x_accounts OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_concatenated_segments IN VARCHAR2) IS
1447 BEGIN
1448 OPEN x_accounts FOR
1449 SELECT a.code_combination_id
1450 , a.concatenated_segments
1451 , a.chart_of_accounts_id
1452 FROM gl_code_combinations_kfv a, org_organization_definitions b
1453 WHERE b.organization_id = p_organization_id
1454 AND a.chart_of_accounts_id = b.chart_of_accounts_id
1455 AND a.concatenated_segments LIKE (p_concatenated_segments)
1456 AND a.enabled_flag = 'Y'
1457 AND NVL(a.start_date_active, SYSDATE - 1) <= SYSDATE
1458 AND NVL(a.end_date_active, SYSDATE + 1) > SYSDATE
1459 AND a.SUMMARY_FLAG in ('N') -- Bug 3792738
1460 AND a.DETAIL_POSTING_ALLOWED NOT IN ('N') -- Bug 3792738
1461 ORDER BY a.concatenated_segments;
1462 END get_accounts;
1463
1464 --
1465 -- GET_MO_ACCOUNTS
1466 --
1467 PROCEDURE get_mo_accounts(x_accounts OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_moheader_id IN NUMBER, p_concatenated_segments IN VARCHAR2) IS
1468 BEGIN
1469 OPEN x_accounts FOR
1470 --Bug#2963407: DISTINCT added to display distinct account segments.
1471 -- If a MO Issue is created with same Destination a/c for all MO Lines,
1472 -- and in MSCA QUERY MO Page, Account LOV shows multiple times the same
1473 -- a/c segments which is incorrect. Hence this distinct.
1474 SELECT DISTINCT a.code_combination_id
1475 , a.concatenated_segments
1476 FROM gl_code_combinations_kfv a, org_organization_definitions b, mtl_txn_request_lines c
1477 WHERE c.header_id = p_moheader_id
1478 AND b.organization_id = p_organization_id
1479 AND a.chart_of_accounts_id = b.chart_of_accounts_id
1480 AND c.to_account_id = a.code_combination_id
1481 AND a.concatenated_segments LIKE (p_concatenated_segments)
1482 AND a.enabled_flag = 'Y'
1486
1483 AND NVL(a.start_date_active, SYSDATE - 1) <= SYSDATE
1484 AND NVL(a.end_date_active, SYSDATE + 1) > SYSDATE;
1485 END;
1487 PROCEDURE get_phyinv_lot_lov(
1488 x_lots OUT NOCOPY t_genref
1489 , p_organization_id IN NUMBER
1490 , p_subinventory_code IN VARCHAR2
1491 , p_locator_id IN NUMBER
1492 , p_inventory_item_id IN NUMBER
1493 , p_lot_number IN VARCHAR2
1494 , p_dynamic_entry_flag IN NUMBER
1495 , p_physical_inventory_id IN NUMBER
1496 , p_parent_lpn_id IN NUMBER
1497 ) IS
1498 BEGIN
1499 IF (p_dynamic_entry_flag = 1) THEN -- Dynamic entries are allowed
1500 OPEN x_lots FOR
1501 SELECT mln.lot_number
1502 , mln.description
1503 , mln.expiration_date
1504 , mmst.status_code
1505 FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
1506 WHERE mln.organization_id = p_organization_id
1507 AND mln.inventory_item_id = p_inventory_item_id
1508 AND mln.status_id = mmst.status_id (+)
1509 AND mmst.language (+) = userenv('LANG')
1510 AND mln.lot_number LIKE (p_lot_number);
1511 ELSE -- Dynamic entries are not allowed
1512 OPEN x_lots FOR
1513 SELECT UNIQUE mln.lot_number
1514 , mln.description
1515 , mln.expiration_date
1516 , mmst.status_code
1517 FROM mtl_lot_numbers mln, mtl_physical_inventory_tags mpit,
1518 mtl_material_statuses_tl mmst
1519 WHERE mln.organization_id = p_organization_id
1520 AND mln.inventory_item_id = p_inventory_item_id
1521 AND mln.lot_number LIKE (p_lot_number)
1522 AND mln.lot_number = mpit.lot_number
1523 AND mln.status_id = mmst.status_id (+)
1524 AND mmst.language (+) = userenv('LANG')
1525 AND mpit.inventory_item_id = p_inventory_item_id
1526 AND mpit.physical_inventory_id = p_physical_inventory_id
1527 AND mpit.subinventory = p_subinventory_code
1528 AND NVL(mpit.locator_id, -99999) = NVL(p_locator_id, -99999)
1529 AND NVL(mpit.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
1530 AND NVL(mpit.void_flag, 2) = 2
1531 AND mpit.adjustment_id IN (SELECT adjustment_id
1532 FROM mtl_physical_adjustments
1533 WHERE physical_inventory_id = p_physical_inventory_id
1534 AND organization_id = p_organization_id
1535 AND approval_status IS NULL);
1536 END IF;
1537 END get_phyinv_lot_lov;
1538
1539 PROCEDURE get_cyc_lot_lov(
1540 x_lots OUT NOCOPY t_genref
1541 , p_organization_id IN NUMBER
1542 , p_subinventory_code IN VARCHAR2
1543 , p_locator_id IN NUMBER
1544 , p_inventory_item_id IN NUMBER
1545 , p_lot_number IN VARCHAR2
1546 , p_unscheduled_entry IN NUMBER
1547 , p_cycle_count_header_id IN NUMBER
1548 , p_parent_lpn_id IN NUMBER
1549 ) IS
1550 l_serial_count_option NUMBER;
1551 l_serial_discrepancy_option NUMBER;
1552 l_container_discrepancy_option NUMBER;
1553 l_serial_number_control_code NUMBER;
1554 BEGIN
1555 -- Get the cycle count discrepancy option flags
1556 SELECT NVL(serial_discrepancy_option, 2)
1557 , NVL(container_discrepancy_option, 2)
1558 INTO l_serial_discrepancy_option
1559 , l_container_discrepancy_option
1560 FROM mtl_cycle_count_headers
1561 WHERE cycle_count_header_id = p_cycle_count_header_id;
1562
1563 -- Get the serial count option for the cycle count header
1564 SELECT NVL(serial_count_option, 1)
1565 INTO l_serial_count_option
1566 FROM mtl_cycle_count_headers
1567 WHERE cycle_count_header_id = p_cycle_count_header_id
1568 AND organization_id = p_organization_id;
1569
1570 -- Get the serial number control code for the item
1571 SELECT NVL(serial_number_control_code, 1)
1572 INTO l_serial_number_control_code
1573 FROM mtl_system_items
1574 WHERE inventory_item_id = p_inventory_item_id
1575 AND organization_id = p_organization_id;
1576
1577 IF (p_unscheduled_entry = 1) THEN -- Unscheduled entries are allowed
1578 OPEN x_lots FOR
1579 SELECT mln.lot_number
1580 , mln.description
1581 , mln.expiration_date
1582 , mmst.status_code
1583 FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
1584 WHERE mln.organization_id = p_organization_id
1585 AND mln.inventory_item_id = p_inventory_item_id
1586 AND mln.status_id = mmst.status_id (+)
1587 AND mmst.language (+) = userenv('LANG')
1588 AND mln.lot_number LIKE (p_lot_number)
1589 -- Bug# 2770853
1590 -- Honor the lot material status for cycle count adjustment transaction
1591 AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
1592 NULL,
1593 4,
1594 'Y',
1595 NULL,
1596 p_organization_id,
1597 p_inventory_item_id,
1598 p_subinventory_code ,
1599 p_locator_id ,
1600 mln.lot_number,
1601 NULL,
1602 'O',
1603 p_parent_lpn_id) = 'Y'); /*Bug 6889528-Added p_parent_lpn_id to the call*/
1604 ELSE -- Unscheduled entries are not allowed
1605 OPEN x_lots FOR
1606 SELECT UNIQUE mln.lot_number
1607 , mln.description
1608 , mln.expiration_date
1609 , mmst.status_code
1610 FROM mtl_lot_numbers mln, mtl_cycle_count_entries mcce,
1611 mtl_material_statuses_tl mmst
1615 AND mmst.language (+) = userenv('LANG')
1612 WHERE mln.organization_id = p_organization_id
1613 AND mln.inventory_item_id = p_inventory_item_id
1614 AND mln.status_id = mmst.status_id (+)
1616 AND mln.lot_number LIKE (p_lot_number)
1617 AND mln.lot_number = mcce.lot_number
1618 AND mcce.inventory_item_id = p_inventory_item_id
1619 AND mcce.cycle_count_header_id = p_cycle_count_header_id
1620 -- The sub and loc have to match an existing cycle count entry
1621 -- OR the entry contains an LPN and
1622 -- container discrepancies are allowed
1623 -- OR the item is serial controlled, the cycle count header allows
1624 -- serial items and serial discrepancies are allowed
1625 AND ((mcce.subinventory = p_subinventory_code
1626 AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
1627 )
1628 OR (mcce.parent_lpn_id IS NOT NULL
1629 AND l_container_discrepancy_option = 1
1630 )
1631 OR (l_serial_count_option <> 1
1632 AND l_serial_number_control_code NOT IN (1, 6)
1633 AND l_serial_discrepancy_option = 1
1634 )
1635 )
1636 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
1637 AND mcce.entry_status_code IN (1, 3)
1638 -- Bug# 2770853
1639 -- Honor the lot material status for cycle count adjustment transaction
1640 AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
1641 NULL,
1642 4,
1643 'Y',
1644 NULL,
1645 p_organization_id,
1646 p_inventory_item_id,
1647 p_subinventory_code ,
1648 p_locator_id ,
1649 mln.lot_number,
1650 NULL,
1651 'O',
1652 p_parent_lpn_id) = 'Y'); /*Bug 6889528-Added p_parent_lpn_id to the call*/
1653 END IF;
1654 END get_cyc_lot_lov;
1655
1656 -- Name: GET_INSPECT_LOT_LOV
1657 --
1658 -- Input parameters:
1659 -- p_Organization_Id which restricts LOV SQL to current org
1660 -- p_item_id Inventory Item id
1661 -- p_lpn_id LPN that is being inspected
1662 -- p_lot_number which restricts LOV SQL to the user input text
1663 --
1664 -- Output parameters:
1665 -- x_lot_num_lov returns LOV rows as reference cursor
1666 --
1667 -- Functions: This API returns Lot number for a given org and
1668 -- and Item Id
1669 --
1670
1671 PROCEDURE get_inspect_lot_lov(x_lot_num_lov OUT NOCOPY t_genref,
1672 p_organization_id IN NUMBER,
1673 p_item_id IN NUMBER,
1674 p_lpn_id IN NUMBER,
1675 p_lot_number IN VARCHAR2,
1676 p_uom_code IN VARCHAR2 ) IS
1677 BEGIN
1678 OPEN x_lot_num_lov FOR
1679 SELECT a.lot_number lot_number
1680 , a.description description
1681 , a.expiration_date expiration_date
1682 , mmst.status_code status_code
1683 , SUM(Decode(Nvl(p_uom_code,uom_code),
1684 uom_code,
1685 b.quantity - Nvl(b.quantity_delivered,0),
1686 inv_convert.inv_um_convert(
1687 p_item_id
1688 ,NULL
1689 ,b.quantity - Nvl(b.quantity_delivered,0)
1690 ,uom_code
1691 ,p_uom_code
1692 ,NULL
1693 ,NULL)
1694 )
1695 ) quantity
1696 FROM mtl_lot_numbers a, mtl_txn_request_lines b,
1697 mtl_material_statuses_tl mmst
1698 WHERE b.organization_id = p_organization_id
1699 AND b.inventory_item_id = p_item_id
1700 AND b.lpn_id = p_lpn_id
1701 AND b.lot_number LIKE (p_lot_number)
1702 AND b.inspection_status = 1 -- To be inspected yet
1703 AND Nvl(b.wms_process_flag,1) <> 2 --Don't pick up those that has been processed
1704 AND b.inventory_item_id = a.inventory_item_id
1705 AND b.organization_id = a.organization_id
1706 AND a.status_id = mmst.status_id (+)
1707 AND mmst.language (+) = userenv('LANG')
1708 AND b.lot_number = a.lot_number
1709 AND b.line_status = 7
1710 AND b.quantity - Nvl(b.quantity_delivered,0) > 0
1711 GROUP BY a.lot_number,a.description,a.expiration_date,mmst.status_code;
1712 END get_inspect_lot_lov;
1713
1714 -- Added p_subinventory_code and p_locator_id parameters as part of onhand status support project
1715 PROCEDURE get_cont_lot_lov(x_lot_num_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER,
1716 p_lpn_id IN NUMBER,p_lot_number IN VARCHAR2 ,p_subinventory_code IN VARCHAR2 ,p_locator_id IN NUMBER) IS
1717 BEGIN
1718 OPEN x_lot_num_lov FOR
1719 SELECT DISTINCT wlc.lot_number
1720 , mln.description
1721 , mln.expiration_date
1722 , '0'
1723 , '0' --wlc.quantity
1724 FROM mtl_lot_numbers mln, wms_lpn_contents wlc
1725 WHERE wlc.organization_id = p_organization_id
1726 AND wlc.inventory_item_id = p_item_id
1727 AND NVL(wlc.parent_lpn_id, '0') = NVL(p_lpn_id, NVL(wlc.parent_lpn_id, '0'))
1728 AND mln.inventory_item_id = wlc.inventory_item_id
1729 AND mln.lot_number = wlc.lot_number
1730 AND mln.organization_id = wlc.organization_id
1731 AND wlc.lot_number LIKE (p_lot_number)
1732 AND inv_material_status_grp.is_status_applicable('TRUE', NULL, 501, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y';
1733 END get_cont_lot_lov;
1737 p_lpn_id IN NUMBER,p_lot_number IN VARCHAR2,p_subinventory_code IN VARCHAR2 ,p_locator_id IN NUMBER) IS
1734
1735 -- Added p_subinventory_code and p_locator_id parameters as part of onhand status support project
1736 PROCEDURE get_split_cont_lot_lov(x_lot_num_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER,
1738 BEGIN
1739 OPEN x_lot_num_lov FOR
1740 SELECT DISTINCT wlc.lot_number
1741 , mln.description
1742 , mln.expiration_date
1743 , '0'
1744 , wlc.quantity
1745 FROM mtl_lot_numbers mln, wms_lpn_contents wlc
1746 WHERE wlc.organization_id = p_organization_id
1747 AND wlc.inventory_item_id = p_item_id
1748 AND NVL(wlc.parent_lpn_id, '0') = NVL(p_lpn_id, NVL(wlc.parent_lpn_id, '0'))
1749 AND mln.inventory_item_id = wlc.inventory_item_id
1750 AND mln.lot_number = wlc.lot_number
1751 AND mln.organization_id = wlc.organization_id
1752 AND wlc.lot_number LIKE (p_lot_number)
1753 AND (inv_material_status_grp.is_status_applicable('TRUE', NULL, 501, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code, p_locator_id, mln.lot_number, NULL, 'O') = 'Y')
1754 AND (inv_material_status_grp.is_status_applicable('TRUE', NULL, 500, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code, p_locator_id, mln.lot_number, NULL, 'O') = 'Y');
1755 END get_split_cont_lot_lov;
1756
1757 PROCEDURE get_all_lot_lov(x_lot_num_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_lot_number IN VARCHAR2) IS
1758 BEGIN
1759 OPEN x_lot_num_lov FOR
1760 SELECT DISTINCT mln.lot_number
1761 , mln.description
1762 , mln.expiration_date
1763 , mmst.status_code
1764 FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
1765 WHERE mln.organization_id = p_organization_id
1766 AND mln.status_id = mmst.status_id (+)
1767 AND mmst.language (+) = userenv('LANG')
1768 AND mln.lot_number LIKE (p_lot_number);
1769 END get_all_lot_lov;
1770
1771 PROCEDURE get_oh_cost_group_lov(x_cost_group OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_inventory_item_id IN VARCHAR2, p_subinventory_code IN VARCHAR2, p_locator_id IN VARCHAR2, p_cost_group IN VARCHAR2) IS
1772 BEGIN
1773 OPEN x_cost_group FOR
1774 SELECT DISTINCT ccg.cost_group
1775 , ccg.cost_group_id
1776 , ccg.description
1777 FROM cst_cost_groups ccg, mtl_onhand_quantities_detail moq
1778 WHERE ccg.cost_group_id = moq.cost_group_id
1779 AND ccg.cost_group_type = 3
1780 AND NVL(ccg.organization_id, moq.organization_id) = moq.organization_id
1781 AND NVL(moq.subinventory_code, '@') = NVL(p_subinventory_code, NVL(moq.subinventory_code, '@'))
1782 AND NVL(moq.locator_id, -999) = NVL(TO_NUMBER(p_locator_id), NVL(moq.locator_id, -999))
1783 AND moq.inventory_item_id = NVL(TO_NUMBER(p_inventory_item_id), moq.inventory_item_id)
1784 AND moq.organization_id = p_organization_id
1785 AND ccg.cost_group LIKE (p_cost_group);
1786 END get_oh_cost_group_lov;
1787
1788 -- Call the api for checking open periods
1789
1790
1791 PROCEDURE tdatechk(p_org_id IN INTEGER, p_transaction_date IN DATE, x_period_id OUT NOCOPY INTEGER) IS
1792 l_open_past_period BOOLEAN := FALSE;
1793 BEGIN
1794 invttmtx.tdatechk(p_org_id, p_transaction_date, x_period_id, l_open_past_period);
1795 END;
1796
1797 -- This is the procedure called from the LabelPage.java.
1798 PROCEDURE get_label_type_lov(x_source_lov OUT NOCOPY t_genref, p_wms_installed IN VARCHAR2, p_lookup_type IN VARCHAR2) IS
1799 BEGIN
1800 IF (p_wms_installed IN ('true', 'TRUE')) THEN
1801 OPEN x_source_lov FOR
1802 SELECT meaning
1803 , lookup_code
1804 FROM mfg_lookups
1805 WHERE lookup_type = 'WMS_LABEL_TYPE'
1806 AND lookup_code NOT IN (9)
1807 AND meaning LIKE (p_lookup_type)
1808 ORDER BY lookup_code;
1809 ELSE
1810 OPEN x_source_lov FOR
1811 SELECT meaning
1812 , lookup_code
1813 FROM mfg_lookups
1814 WHERE lookup_type = 'WMS_LABEL_TYPE'
1815 AND lookup_code IN (1, 2, 6, 7, 8, 10)
1816 AND meaning LIKE (p_lookup_type)
1817 ORDER BY lookup_code;
1818 END IF;
1819 END get_label_type_lov;
1820
1821 -- Added for the Label Reprint Project.
1822 -- A new procedure is created for this project because the procedure called from the LabelPage.java
1823 -- has a restriction for the "WIP Content" Label.
1824 PROCEDURE get_label_type_reprint_lov(x_source_lov OUT NOCOPY t_genref, p_wms_installed IN VARCHAR2, p_lookup_type IN VARCHAR2) IS
1825 BEGIN
1826 IF (p_wms_installed IN ('true', 'TRUE')) THEN
1827 OPEN x_source_lov FOR
1828 SELECT meaning
1829 , lookup_code
1830 FROM mfg_lookups
1831 WHERE lookup_type = 'WMS_LABEL_TYPE'
1832 AND meaning LIKE (p_lookup_type)
1833 ORDER BY lookup_code;
1834 ELSE
1835 OPEN x_source_lov FOR
1836 SELECT meaning
1837 , lookup_code
1838 FROM mfg_lookups
1839 WHERE lookup_type = 'WMS_LABEL_TYPE'
1840 AND lookup_code IN (1, 2, 6, 7, 8, 10)
1844 END get_label_type_reprint_lov;
1841 AND meaning LIKE (p_lookup_type)
1842 ORDER BY lookup_code;
1843 END IF;
1845
1846 -- Added for the Label Reprint Project.
1847 PROCEDURE get_businessflow_type_lov(x_source_lov OUT NOCOPY t_genref, p_wms_installed IN VARCHAR2, p_lookup_type IN VARCHAR2) IS
1848 BEGIN
1849 IF (p_wms_installed IN ('true', 'TRUE')) THEN -- WMS Enabled.
1850 OPEN x_source_lov FOR
1851 SELECT meaning
1852 , lookup_code
1853 FROM mfg_lookups
1854 WHERE lookup_type = 'WMS_BUSINESS_FLOW'
1855 AND lookup_code NOT IN (3)
1856 AND meaning LIKE (p_lookup_type)
1857 ORDER BY lookup_code;
1858 ELSE -- INV Enabled.
1859 OPEN x_source_lov FOR
1860 SELECT meaning
1861 , lookup_code
1862 FROM mfg_lookups
1863 WHERE lookup_type = 'WMS_BUSINESS_FLOW'
1864 AND lookup_code IN (1, 2, 3, 8, 9, 13, 14, 15, 17, 21, 23, 24, 26, 31, 32, 33)
1865 AND meaning LIKE (p_lookup_type)
1866 ORDER BY lookup_code;
1867 END IF;
1868 END get_businessflow_type_lov;
1869
1870 -- Name: GET_ALL_LABEL_TYPE_LOV
1871 --
1872 -- Input parameters:
1873 -- p_wms_installed true/false if wms is installed
1874 -- p_all_label_str translated string for all label types
1875 -- p_lookup_type partial completion on lookup type
1876 -- Functions: This API returns all label types
1877
1878 PROCEDURE GET_ALL_LABEL_TYPE_LOV
1879 (x_source_lov OUT NOCOPY t_genref,
1880 p_wms_installed IN VARCHAR2,
1881 p_all_label_str IN VARCHAR2,
1882 p_lookup_type IN VARCHAR2
1883 )
1884 IS
1885 BEGIN
1886 IF ( p_wms_installed IN('true','TRUE') ) THEN
1887 OPEN x_source_lov FOR
1888 SELECT p_all_label_str meaning, 0 lookup_code
1889 FROM DUAL
1890 WHERE p_all_label_str LIKE (p_lookup_type)
1891 UNION ALL
1892 SELECT meaning, lookup_code
1893 FROM mfg_lookups
1894 WHERE lookup_type = 'WMS_LABEL_TYPE'
1895 AND meaning LIKE (p_lookup_type)
1896 ORDER BY lookup_code;
1897 ELSE
1898 OPEN x_source_lov FOR
1899 SELECT p_all_label_str meaning, 0 lookup_code
1900 FROM DUAL
1901 WHERE p_all_label_str LIKE (p_lookup_type)
1902 UNION ALL
1903 SELECT meaning, lookup_code
1904 FROM mfg_lookups
1905 WHERE lookup_type = 'WMS_LABEL_TYPE'
1906 AND lookup_code NOT IN (3,4,5,9)
1907 AND meaning LIKE (p_lookup_type)
1908 ORDER BY lookup_code;
1909 END IF;
1910 END GET_ALL_LABEL_TYPE_LOV;
1911
1912
1913 PROCEDURE get_notrx_item_lot_lov(x_lot_num_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER, p_lot_number IN VARCHAR2) IS
1914 BEGIN
1915 OPEN x_lot_num_lov FOR
1916 SELECT mln.lot_number
1917 , mln.description
1918 , mln.expiration_date
1919 , mmst.status_code
1920 FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
1921 WHERE mln.organization_id = p_organization_id
1922 AND mln.inventory_item_id = p_item_id
1923 AND mln.status_id = mmst.status_id (+)
1924 AND mmst.language (+) = userenv('LANG')
1925 AND mln.lot_number LIKE (p_lot_number);
1926 END get_notrx_item_lot_lov;
1927
1928 PROCEDURE get_td_lot_lov(
1929 x_lot_num_lov OUT NOCOPY t_genref
1930 , p_organization_id IN NUMBER
1931 , p_item_id IN NUMBER
1932 , p_lot_number IN VARCHAR2
1933 , p_transaction_type_id IN NUMBER
1934 , p_wms_installed IN VARCHAR2
1935 , p_lpn_id IN NUMBER
1936 , p_subinventory_code IN VARCHAR2
1937 , p_locator_id IN NUMBER
1938 , p_txn_temp_id IN NUMBER
1939 ) IS
1940 l_negative_rcpt_code NUMBER;
1941 BEGIN
1942 SELECT negative_inv_receipt_code
1943 INTO l_negative_rcpt_code
1944 FROM mtl_parameters
1945 WHERE organization_id = p_organization_id;
1946
1947 IF (l_negative_rcpt_code = 1) THEN
1948 -- Negative inventory balances allowed
1949
1950 IF (p_lpn_id IS NULL
1951 OR p_lpn_id = 0
1952 ) THEN
1953 OPEN x_lot_num_lov FOR
1954 -- In case where negative onhand inventory balances are
1955 -- allowed, we don't do location validation for loose items.
1956 SELECT DISTINCT mln.lot_number
1957 , mln.description
1958 , mln.expiration_date
1959 , mmst.status_code
1960 , mtlt.primary_quantity
1961 , mtlt.transaction_quantity
1962 FROM mtl_lot_numbers mln, mtl_transaction_lots_temp mtlt,
1963 mtl_material_statuses_tl mmst
1964 WHERE mln.organization_id = p_organization_id
1965 AND mln.inventory_item_id = p_item_id
1966 AND mln.lot_number LIKE (p_lot_number)
1967 AND mln.status_id = mmst.status_id (+)
1968 AND mmst.language (+) = userenv('LANG')
1969 AND mtlt.lot_number = mln.lot_number
1970 AND mtlt.transaction_temp_id = p_txn_temp_id
1971 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y';
1972 ELSE --as lpn_id is not null ,hence sub and loc are not passed in is_status_applicable
1973 -- It however remains same for LPNs
1974 OPEN x_lot_num_lov FOR
1978 , mmst.status_code
1975 SELECT DISTINCT mln.lot_number
1976 , mln.description
1977 , mln.expiration_date
1979 , mtlt.primary_quantity
1980 , mtlt.transaction_quantity
1981 FROM mtl_lot_numbers mln,
1982 mtl_transaction_lots_temp mtlt, wms_lpn_contents wlc,
1983 mtl_material_statuses_tl mmst
1984 WHERE mln.organization_id = p_organization_id
1985 AND mln.inventory_item_id = p_item_id
1986 AND mln.lot_number LIKE (p_lot_number)
1987 AND mln.status_id = mmst.status_id (+)
1988 AND mmst.language (+) = userenv('LANG')
1989 AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moq
1990 WHERE moq.lot_number = mln.lot_number
1991 AND moq.inventory_item_id = mln.inventory_item_id
1992 AND moq.organization_id = mln.organization_id
1993 AND moq.containerized_flag = 1
1994 AND moq.subinventory_code = NVL(p_subinventory_code, moq.subinventory_code)
1995 AND NVL(moq.locator_id, -1) = NVL(NVL(p_locator_id, moq.locator_id), -1))
1996 AND mtlt.lot_number = mln.lot_number
1997 AND mtlt.transaction_temp_id = p_txn_temp_id
1998 AND wlc.parent_lpn_id = p_lpn_id
1999 AND wlc.lot_number = mln.lot_number
2000 AND wlc.inventory_item_id = p_item_id
2001 AND wlc.organization_id = p_organization_id
2002 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y';
2003 END IF;
2004 ELSE
2005 -- Negative inventory balances not allowed
2006
2007 IF (p_lpn_id IS NULL
2008 OR p_lpn_id = 0
2009 ) THEN
2010 OPEN x_lot_num_lov FOR
2011 -- In case where negative onhand inventory balances are
2012 -- not allowed, we do location validation for loose items.
2013 SELECT DISTINCT mln.lot_number
2014 , mln.description
2015 , mln.expiration_date
2016 , mmst.status_code
2017 , mtlt.primary_quantity
2018 , mtlt.transaction_quantity
2019 FROM mtl_lot_numbers mln,
2020 mtl_transaction_lots_temp mtlt, mtl_material_statuses_tl mmst
2021 WHERE mln.organization_id = p_organization_id
2022 AND mln.inventory_item_id = p_item_id
2023 AND mln.lot_number LIKE (p_lot_number)
2024 AND mln.status_id = mmst.status_id (+)
2025 AND mmst.language (+) = userenv('LANG')
2026 AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moq
2027 WHERE moq.lot_number = mln.lot_number
2028 AND moq.inventory_item_id = mln.inventory_item_id
2029 AND moq.organization_id = mln.organization_id
2030 AND moq.containerized_flag = 2
2031 AND moq.subinventory_code = NVL(p_subinventory_code, moq.subinventory_code)
2032 AND NVL(moq.locator_id, -1) = NVL(NVL(p_locator_id, moq.locator_id), -1))
2033 AND mtlt.lot_number = mln.lot_number
2034 AND mtlt.transaction_temp_id = p_txn_temp_id
2035 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y';
2036 ELSE
2037 OPEN x_lot_num_lov FOR
2038 SELECT DISTINCT mln.lot_number
2039 , mln.description
2040 , mln.expiration_date
2041 , mmst.status_code
2042 , mtlt.primary_quantity
2043 , mtlt.transaction_quantity
2044 FROM mtl_lot_numbers mln,
2045 mtl_transaction_lots_temp mtlt, wms_lpn_contents wlc,
2046 mtl_material_statuses_tl mmst
2047 WHERE mln.organization_id = p_organization_id
2048 AND mln.inventory_item_id = p_item_id
2049 AND mln.lot_number LIKE (p_lot_number)
2050 AND mln.status_id = mmst.status_id (+)
2051 AND mmst.language (+) = userenv('LANG')
2052 AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moq
2053 WHERE moq.lot_number = mln.lot_number
2054 AND moq.inventory_item_id = mln.inventory_item_id
2055 AND moq.organization_id = mln.organization_id
2056 AND moq.containerized_flag = 1
2057 AND moq.subinventory_code = NVL(p_subinventory_code, moq.subinventory_code)
2058 AND NVL(moq.locator_id, -1) = NVL(NVL(p_locator_id, moq.locator_id), -1))
2059 AND mtlt.lot_number = mln.lot_number
2060 AND mtlt.transaction_temp_id = p_txn_temp_id
2061 AND wlc.parent_lpn_id = p_lpn_id
2062 AND wlc.lot_number = mln.lot_number
2063 AND wlc.inventory_item_id = p_item_id
2064 AND wlc.organization_id = p_organization_id
2065 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y';
2066 END IF;
2067 END IF;
2068 END get_td_lot_lov;
2069
2070 PROCEDURE get_apl_lot_lov(
2071 x_lot_num_lov OUT NOCOPY t_genref
2072 , p_organization_id IN NUMBER
2073 , p_item_id IN NUMBER
2074 , p_lot_number IN VARCHAR2
2075 , p_transaction_type_id IN NUMBER
2076 , p_wms_installed IN VARCHAR2
2077 , p_lpn_id IN NUMBER
2078 , p_subinventory_code IN VARCHAR2
2079 , p_locator_id IN NUMBER
2080 , p_txn_temp_id IN NUMBER
2081 ) IS
2082 l_negative_rcpt_code NUMBER;
2083 BEGIN
2084 -- Since Negative Balance check is through APL Set-up form prameter.We would
2085 --not required to check negative balance here through back-end.
2086 -- Vikas v1 10/04/04 start removed earlier code,also removed AND clause
2090 IF (p_lpn_id IS NULL
2087 --selecting mtl_onhand_quantities_detail
2088
2089
2091 OR p_lpn_id = 0
2092 ) THEN
2093 OPEN x_lot_num_lov FOR
2094 -- In case where negative onhand inventory balances are
2095 -- not allowed, we do location validation for loose items.
2096 SELECT mln.lot_number
2097 , mln.description
2098 , mln.expiration_date
2099 , mmst.status_code
2100 , sum(mag.primary_quantity)
2101 , sum(mag.transaction_quantity)
2102 FROM mtl_lot_numbers mln, wms_allocations_gtmp mag,
2103 mtl_material_statuses_tl mmst
2104 WHERE mln.organization_id = p_organization_id
2105 AND mln.inventory_item_id = p_item_id
2106 AND mln.lot_number LIKE (p_lot_number)
2107 AND mln.status_id = mmst.status_id (+)
2108 AND mmst.language (+) = userenv('LANG')
2109 AND mag.lot_number = mln.lot_number
2110 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
2111 GROUP BY mln.lot_number
2112 , mln.description
2113 , mln.expiration_date
2114 , mmst.status_code
2115 HAVING sum(mag.transaction_quantity) > 0;
2116 ELSE
2117 OPEN x_lot_num_lov FOR
2118 SELECT DISTINCT mln.lot_number
2119 , mln.description
2120 , mln.expiration_date
2121 , mmst.status_code
2122 , sum(mag.primary_quantity)
2123 , sum(mag.transaction_quantity)
2124 FROM mtl_lot_numbers mln,
2125 mtl_transaction_lots_temp mtlt, wms_allocations_gtmp mag,
2126 mtl_material_statuses_tl mmst
2127 WHERE mln.organization_id = p_organization_id
2128 AND mln.inventory_item_id = p_item_id
2129 AND mln.lot_number LIKE (p_lot_number)
2130 AND mln.status_id = mmst.status_id (+)
2131 AND mmst.language (+) = userenv('LANG')
2132 AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moq
2133 WHERE moq.lot_number = mln.lot_number
2134 AND moq.inventory_item_id = mln.inventory_item_id
2135 AND moq.organization_id = mln.organization_id
2136 AND moq.containerized_flag = 1
2137 AND moq.subinventory_code = NVL(p_subinventory_code, moq.subinventory_code)
2138 AND NVL(moq.locator_id, -1) = NVL(NVL(p_locator_id, moq.locator_id), -1))
2139 AND mtlt.lot_number = mln.lot_number
2140 AND mtlt.transaction_temp_id = p_txn_temp_id
2141 AND mag.lot_number = mln.lot_number
2142 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y'
2143 GROUP BY mln.lot_number
2144 , mln.description
2145 , mln.expiration_date
2146 , mmst.status_code
2147 HAVING sum(mag.transaction_quantity) > 0;
2148 END IF;
2149 END get_apl_lot_lov;
2150
2151 --"Returns"
2152 PROCEDURE get_return_lot_lov(x_lot_num_lov OUT NOCOPY t_genref, p_org_id IN NUMBER, p_lpn_id IN NUMBER, p_item_id IN NUMBER, p_revision IN VARCHAR2, p_lot_number IN VARCHAR2) IS
2153 BEGIN
2154 OPEN x_lot_num_lov FOR
2155 SELECT DISTINCT mln.lot_number
2156 , mln.description
2157 , mln.expiration_date
2158 , mstl.status_code
2159 FROM mtl_lot_numbers mln, wms_lpn_contents wlpnc, mtl_material_statuses_b mstb, mtl_material_statuses_tl mstl
2160 WHERE wlpnc.parent_lpn_id = p_lpn_id
2161 AND wlpnc.organization_id = p_org_id
2162 AND wlpnc.inventory_item_id = p_item_id
2163 AND ((wlpnc.revision = p_revision
2164 AND p_revision IS NOT NULL
2165 )
2166 OR (p_revision IS NULL
2167 AND wlpnc.revision IS NULL
2168 )
2169 )
2170 AND wlpnc.source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING')
2171 AND wlpnc.lot_number LIKE (p_lot_number)
2172 AND mln.lot_number = wlpnc.lot_number
2173 AND mln.organization_id = wlpnc.organization_id
2174 AND mln.inventory_item_id = wlpnc.inventory_item_id
2175 AND mln.status_id = mstb.status_id(+)
2176 AND mstb.status_id = mstl.status_id(+)
2177 AND mstl.LANGUAGE(+) = USERENV('LANG');
2178 END get_return_lot_lov;
2179
2180 --"Returns"
2181
2182 PROCEDURE get_lot_lov_for_unload(x_lot_num_lov OUT NOCOPY t_genref, p_temp_id IN NUMBER) IS
2183 BEGIN
2184 OPEN x_lot_num_lov FOR
2185 SELECT lot_number
2186 , ' '
2187 , ' '
2188 , ' '
2189 FROM mtl_transaction_lots_temp
2190 WHERE transaction_temp_id = p_temp_id;
2191 END get_lot_lov_for_unload;
2192
2196 -- p_label_type_id SELECTED label type.
2193 -- Name: GET_FORMAT_LOV
2194 -- Added by joabraha.
2195 -- Input parameters:
2197 -- Functions: This API returns all formats for a specific label type.
2198 PROCEDURE get_format_lov(x_format_lov OUT NOCOPY t_genref, p_label_type_id IN NUMBER, p_format_name IN VARCHAR2) IS
2199 BEGIN
2200 OPEN x_format_lov FOR
2201 SELECT label_format_id
2202 , label_format_name
2203 , Decode(label_entity_type,1,'Label Set', 'Format')
2204 FROM wms_label_formats
2205 WHERE document_id = p_label_type_id
2206 AND NVL(format_disable_date, SYSDATE + 1) > SYSDATE --Bug #3452076
2207 AND label_format_name like (p_format_name)
2208 ORDER BY label_format_name;
2209 END get_format_lov;
2210
2211 -- Name: GET_USER_PRINTERS_LOV
2212 -- Added by joabraha for jsheu.
2213 -- Input parameters:
2214 -- p_printer_name partial completion on printer_name
2215 -- Functions: This API returns all printers
2216
2217 PROCEDURE get_user_printers_lov(x_printer_lov OUT NOCOPY t_genref, p_printer_name IN VARCHAR2) IS
2218 BEGIN
2219 OPEN x_printer_lov FOR
2220 SELECT printer_name
2221 , printer_type
2222 FROM fnd_printer
2223 WHERE printer_name LIKE (p_printer_name)
2224 ORDER BY printer_name;
2225 END get_user_printers_lov;
2226
2227 PROCEDURE get_flow_schedule_lov(x_flow_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_from_schedule_number IN VARCHAR2, p_schedule_number IN VARCHAR2) IS
2228 BEGIN
2229 OPEN x_flow_lov FOR
2230 SELECT schedule_number
2231 , organization_id
2232 FROM wip_flow_schedules
2233 WHERE organization_id = NVL(p_organization_id, organization_id)
2234 AND schedule_number >= NVL(p_from_schedule_number, 0)
2235 AND schedule_number LIKE (p_schedule_number);
2236 END get_flow_schedule_lov;
2237
2238 PROCEDURE get_lot_control_from_org(x_lot_control_code OUT NOCOPY NUMBER, x_from_org_id OUT NOCOPY NUMBER, p_organization_id IN NUMBER, p_shipment_header_id IN NUMBER, p_item_id IN NUMBER) IS
2239 BEGIN
2240 SELECT msik.lot_control_code
2241 , rsl.from_organization_id
2242 INTO x_lot_control_code
2243 , x_from_org_id
2244 FROM mtl_system_items_kfv msik, rcv_shipment_lines rsl
2245 WHERE msik.inventory_item_id = p_item_id
2246 AND rsl.shipment_header_id = p_shipment_header_id
2247 AND rsl.to_organization_id = p_organization_id
2248 AND rsl.from_organization_id = msik.organization_id
2249 AND ROWNUM < 2;
2250 EXCEPTION
2251 WHEN OTHERS THEN
2252 x_lot_control_code := 1;
2253 END get_lot_control_from_org;
2254
2255 -- Added p_subinventory_code and p_locator_id parameters as part of onhand status support project
2256 PROCEDURE get_item_load_lot_lov
2257 (x_lot_num_lov OUT NOCOPY t_genref ,
2258 p_organization_id IN NUMBER ,
2259 p_item_id IN NUMBER ,
2260 p_lpn_id IN NUMBER ,
2261 p_lot_number IN VARCHAR2 ,
2262 p_subinventory_code IN VARCHAR2 ,
2263 p_locator_id IN NUMBER)
2264 IS
2265
2266 BEGIN
2267
2268 OPEN x_lot_num_lov FOR
2269 SELECT DISTINCT mln.lot_number
2270 , mln.description
2271 , mln.expiration_date
2272 , mmst.status_code
2273 FROM mtl_lot_numbers mln, wms_lpn_contents wlc,
2274 mtl_material_statuses_tl mmst
2275 WHERE wlc.organization_id = p_organization_id
2276 AND wlc.inventory_item_id = p_item_id
2277 AND wlc.parent_lpn_id = p_lpn_id
2278 AND mln.inventory_item_id = wlc.inventory_item_id
2279 AND mln.lot_number = wlc.lot_number
2280 AND mln.organization_id = wlc.organization_id
2281 AND mln.status_id = mmst.status_id (+)
2282 AND mmst.language (+) = userenv('LANG')
2283 AND wlc.lot_number LIKE (p_lot_number)
2284 AND inv_material_status_grp.is_status_applicable('TRUE',
2285 NULL,
2286 INV_GLOBALS.G_TYPE_CONTAINER_UNPACK,
2287 NULL,
2288 NULL,
2289 p_organization_id,
2290 p_item_id,
2291 p_subinventory_code,
2292 p_locator_id,
2293 mln.lot_number,
2294 NULL,
2295 'O') = 'Y'
2296 ORDER BY mln.lot_number;
2297
2298 END get_item_load_lot_lov;
2299
2300
2301 FUNCTION validate_account_segments(
2302 p_segments VARCHAR2,
2303 p_data_set NUMBER
2304 ) RETURN VARCHAR2 IS
2305
2306 ftype fnd_flex_key_api.flexfield_type;
2307 stype fnd_flex_key_api.structure_type;
2308 l_return_status BOOLEAN;
2309
2310 l_values_or_ids CONSTANT VARCHAR2(1) := 'V';
2311 l_flex_code CONSTANT VARCHAR2(10) := 'GL#';
2312 l_appl_short_name CONSTANT VARCHAR2(10) := 'SQLGL';
2313 l_operation CONSTANT VARCHAR2(20) := 'FIND_COMBINATION';
2314 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2315
2316 BEGIN
2317
2318 fnd_flex_key_api.set_session_mode('seed_data');
2319
2320 IF (l_debug=1) THEN
2321 inv_mobile_helper_functions.tracelog
2322 (p_err_msg => ' inputs '||p_segments||' :: '||p_data_set,
2323 p_module => 'INV_INV_LOVS',
2324 p_level => 1);
2325 END IF;
2326
2327 ftype := fnd_flex_key_api.find_flexfield(
2328 appl_short_name => l_appl_short_name,
2329 flex_code => l_flex_code
2330 );
2331 IF (l_debug=1) THEN
2332 inv_mobile_helper_functions.tracelog
2333 (p_err_msg => 'Got flex definition',
2334 p_module => 'INV_INV_LOVS',
2335 p_level => 1);
2336
2337 inv_mobile_helper_functions.tracelog
2338 (p_err_msg => 'Flex Title :'||ftype.flex_title||'description :'||ftype.description,
2339 p_module => 'INV_INV_LOVS',
2340 p_level => 1);
2341 END IF;
2342
2343
2344
2345 l_return_status := FND_FLEX_KEYVAL.VALIDATE_SEGS(
2346 OPERATION => l_operation,
2347 APPL_SHORT_NAME => l_appl_short_name,
2348 KEY_FLEX_CODE => l_flex_code,
2349 STRUCTURE_NUMBER => p_data_set,
2350 CONCAT_SEGMENTS => p_segments,
2351 VALUES_OR_IDS => l_values_or_ids
2352 );
2353
2354 IF l_return_status THEN
2355
2356 IF (l_debug=1) THEN
2357
2358 inv_mobile_helper_functions.tracelog
2359 (p_err_msg => 'Returned true after flex validation',
2360 p_module => 'INV_INV_LOVS',
2361 p_level => 1);
2362
2363 END IF;
2364
2365 RETURN 'TRUE';
2366
2367 ELSE
2368
2369 IF (l_debug=1) THEN
2370
2371 inv_mobile_helper_functions.tracelog
2372 (p_err_msg => 'Returned false after flex validation',
2373 p_module => 'INV_INV_LOVS',
2374 p_level => 1);
2375
2376 END IF;
2377
2378 RETURN 'FALSE';
2379 END IF;
2380
2381 END validate_account_segments;
2382
2383 --added for lpn status project to handle lot in lpn and loose
2384 procedure get_from_onstatus_lot_lov(x_lot_num_lov OUT NOCOPY t_genref,
2385 p_organization_id IN NUMBER,
2386 p_lpn VARCHAR2 ,
2387 p_item_id IN NUMBER,
2388 p_lot_number IN VARCHAR2) IS
2389 BEGIN
2390 IF(p_lpn IS NULL) then
2391 OPEN x_lot_num_lov FOR
2392 SELECT mln.lot_number
2393 , mln.description
2394 , mln.expiration_date
2395 , mmst.status_code
2396 FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst , mtl_onhand_quantities_detail moqd
2397 WHERE mln.organization_id = p_organization_id
2398 AND mln.inventory_item_id = p_item_id
2399 AND mln.status_id = mmst.status_id (+)
2400 AND mmst.language (+) = userenv('LANG')
2401 AND mln.lot_number LIKE (p_lot_number)
2402 AND moqd.lot_number = mln.lot_number
2403 AND moqd.lpn_id is NULL
2404 ORDER BY mln.lot_number;
2405 ELSE
2406 OPEN x_lot_num_lov FOR
2407 SELECT mln.lot_number
2408 , mln.description
2409 , mln.expiration_date
2410 , mmst.status_code
2411 FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst , wms_license_plate_numbers wlpn , wms_lpn_contents wlc
2412 WHERE wlpn.license_plate_number = p_lpn
2413 AND wlc.parent_lpn_id = wlpn.lpn_id
2414 AND mln.lot_number = wlc.lot_number
2415 AND mln.organization_id = p_organization_id
2416 AND mln.inventory_item_id = p_item_id
2417 AND mln.status_id = mmst.status_id (+)
2418 AND mmst.language (+) = userenv('LANG')
2419 AND mln.lot_number LIKE (p_lot_number)
2420 ORDER BY mln.lot_number;
2421 END IF;
2422 END get_from_onstatus_lot_lov;
2423
2424 PROCEDURE get_to_onstatus_lot_lov(x_lot_num_lov OUT NOCOPY t_genref,
2425 p_organization_id IN NUMBER,
2426 p_lpn varchar2,
2427 p_item_id IN NUMBER,
2428 p_from_lot_number IN VARCHAR2,
2429 p_lot_number IN VARCHAR2) IS
2430 BEGIN
2431 IF(p_lpn is null)then
2432 OPEN x_lot_num_lov FOR
2433 SELECT mln.lot_number
2434 , mln.description
2435 , mln.expiration_date
2436 , mmst.status_code
2437 FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst ,mtl_onhand_quantities_detail moqd
2438 WHERE mln.organization_id = p_organization_id
2439 AND mln.inventory_item_id = p_item_id
2440 AND mln.status_id = mmst.status_id (+)
2441 AND mmst.language (+) = userenv('LANG')
2442 AND mln.lot_number >= p_from_lot_number
2443 AND mln.lot_number LIKE (p_lot_number)
2444 AND moqd.lot_number = mln.lot_number
2445 AND moqd.lpn_id is NULL
2446 ORDER BY mln.lot_number;
2447
2448
2449 ELSE
2450 OPEN x_lot_num_lov FOR
2451 SELECT mln.lot_number
2452 , mln.description
2453 , mln.expiration_date
2454 , mmst.status_code
2455 FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst , wms_license_plate_numbers wlpn , wms_lpn_contents wlc
2456 WHERE wlpn.license_plate_number = p_lpn
2457 AND wlc.parent_lpn_id = wlpn.lpn_id
2458 AND mln.lot_number = wlc.lot_number
2459 AND mln.organization_id = p_organization_id
2460 AND mln.inventory_item_id = p_item_id
2461 AND mln.status_id = mmst.status_id (+)
2462 AND mmst.language (+) = userenv('LANG')
2463 AND mln.lot_number >= p_from_lot_number
2464 AND mln.lot_number LIKE (p_lot_number)
2465 ORDER BY mln.lot_number;
2466 END IF;
2467 END get_to_onstatus_lot_lov;
2468
2469 --end of lpn status project
2470
2471
2472 END inv_inv_lovs;
[Home] [Help]