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