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 (+)
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 (+)
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 (+)
163: , mln.description
164: , mln.expiration_date
165: , mmst.status_code
166: FROM mtl_lot_numbers mln,
167: mtl_material_statuses_tl mmst
168: WHERE mln.organization_id = p_organization_id
169: AND mln.inventory_item_id = p_item_id
170: AND mln.lot_number LIKE (p_lot_number)
171: AND NVL(mln.disable_flag,'2') = '2' --Bug#4108798 Disabled lots must not be displayed
209: , mln.description
210: , mln.expiration_date
211: , mmst.status_code
212: FROM mtl_lot_numbers mln,
213: mtl_material_statuses_tl mmst
214: WHERE mln.organization_id = p_organization_id
215: AND mln.inventory_item_id = p_item_id
216: AND mln.lot_number LIKE (p_lot_number)
217: AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moq
226: , mln.description
227: , mln.expiration_date
228: , mmst.status_code
229: FROM mtl_lot_numbers mln, wms_asn_details wad,
230: rcv_shipment_headers rsh, mtl_material_statuses_tl mmst
231: WHERE mln.organization_id = p_organization_id
232: AND mln.inventory_item_id = p_item_id
233: AND mln.lot_number LIKE (p_lot_number)
234: AND mln.lot_number = wad.lot_number_expected
248: , mln.description
249: , mln.expiration_date
250: , mmst.status_code
251: FROM mtl_lot_numbers mln, rcv_lots_supply rls,
252: rcv_shipment_lines rsl, mtl_material_statuses_tl mmst
253: WHERE rls.shipment_line_id = rsl.shipment_line_id
254: AND rsl.shipment_header_id = p_source_header_id
255: AND rsl.to_organization_id = p_organization_id
256: AND rsl.item_id = p_item_id
309: , mlnv.description
310: , mlnv.expiration_date
311: , mmst.status_code
312: FROM mtl_lot_numbers mlnv, rcv_lots_supply rls,
313: rcv_shipment_lines rsl, mtl_material_statuses_tl mmst
314: WHERE rls.shipment_line_id = rsl.shipment_line_id
315: AND rsl.shipment_header_id = p_shipment_header_id
316: AND rsl.to_organization_id = p_organization_id
317: AND rsl.item_id = p_item_id
363: , mln.description
364: , mln.expiration_date
365: , mmst.status_code
366: FROM mtl_lot_numbers mln,
367: mtl_material_statuses_tl mmst
368: WHERE mln.organization_id = p_organization_id
369: AND mln.inventory_item_id = p_item_id
370: AND mln.lot_number LIKE (p_lot_number)
371: AND exists ( SELECT '1' FROM mtl_onhand_quantities_detail moq
418: , mln.description
419: , mln.expiration_date
420: , mmst.status_code
421: FROM mtl_lot_numbers mln, mtl_onhand_quantities_detail moq,
422: mtl_material_statuses_tl mmst
423: WHERE mln.lot_number = moq.lot_number
424: AND mln.inventory_item_id = p_inventory_item_id
425: AND mln.organization_id = p_organization_id
426: AND mln.status_id = mmst.status_id (+)
447: , mln.description
448: , mln.expiration_date
449: , mmst.status_code
450: FROM mtl_lot_numbers mln, wms_lpn_contents wlc,
451: mtl_material_statuses_tl mmst
452: WHERE mln.lot_number = wlc.lot_number
453: AND mln.inventory_item_id = p_inventory_item_id
454: AND mln.organization_id = p_organization_id
455: AND mln.status_id = mmst.status_id (+)
490: SELECT mln.lot_number
491: , mln.description
492: , mln.expiration_date
493: , mmst.status_code
494: FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
495: WHERE mln.organization_id = p_organization_id
496: AND mln.inventory_item_id = p_item_id
497: AND mln.status_id = mmst.status_id (+)
498: AND mmst.language (+) = userenv('LANG')
519: SELECT mln.lot_number
520: , mln.description
521: , mln.expiration_date
522: , mmst.status_code
523: FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
524: WHERE mln.organization_id = p_organization_id
525: AND mln.inventory_item_id = p_item_id
526: AND mln.status_id = mmst.status_id (+)
527: AND mmst.language (+) = userenv('LANG')
550: SELECT mln.lot_number
551: , mln.description
552: , mln.expiration_date
553: , mmst.status_code
554: FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
555: WHERE mln.organization_id = p_organization_id
556: AND mln.inventory_item_id = p_item_id
557: AND mln.status_id = mmst.status_id (+)
558: AND mmst.language (+) = userenv('LANG')
1501: SELECT mln.lot_number
1502: , mln.description
1503: , mln.expiration_date
1504: , mmst.status_code
1505: FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
1506: WHERE mln.organization_id = p_organization_id
1507: AND mln.inventory_item_id = p_inventory_item_id
1508: AND mln.status_id = mmst.status_id (+)
1509: AND mmst.language (+) = userenv('LANG')
1514: , mln.description
1515: , mln.expiration_date
1516: , mmst.status_code
1517: FROM mtl_lot_numbers mln, mtl_physical_inventory_tags mpit,
1518: mtl_material_statuses_tl mmst
1519: WHERE mln.organization_id = p_organization_id
1520: AND mln.inventory_item_id = p_inventory_item_id
1521: AND mln.lot_number LIKE (p_lot_number)
1522: AND mln.lot_number = mpit.lot_number
1579: SELECT mln.lot_number
1580: , mln.description
1581: , mln.expiration_date
1582: , mmst.status_code
1583: FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
1584: WHERE mln.organization_id = p_organization_id
1585: AND mln.inventory_item_id = p_inventory_item_id
1586: AND mln.status_id = mmst.status_id (+)
1587: AND mmst.language (+) = userenv('LANG')
1607: , mln.description
1608: , mln.expiration_date
1609: , mmst.status_code
1610: FROM mtl_lot_numbers mln, mtl_cycle_count_entries mcce,
1611: mtl_material_statuses_tl mmst
1612: WHERE mln.organization_id = p_organization_id
1613: AND mln.inventory_item_id = p_inventory_item_id
1614: AND mln.status_id = mmst.status_id (+)
1615: AND mmst.language (+) = userenv('LANG')
1693: ,NULL)
1694: )
1695: ) quantity
1696: FROM mtl_lot_numbers a, mtl_txn_request_lines b,
1697: mtl_material_statuses_tl mmst
1698: WHERE b.organization_id = p_organization_id
1699: AND b.inventory_item_id = p_item_id
1700: AND b.lpn_id = p_lpn_id
1701: AND b.lot_number LIKE (p_lot_number)
1760: SELECT DISTINCT mln.lot_number
1761: , mln.description
1762: , mln.expiration_date
1763: , mmst.status_code
1764: FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
1765: WHERE mln.organization_id = p_organization_id
1766: AND mln.status_id = mmst.status_id (+)
1767: AND mmst.language (+) = userenv('LANG')
1768: AND mln.lot_number LIKE (p_lot_number);
1916: SELECT mln.lot_number
1917: , mln.description
1918: , mln.expiration_date
1919: , mmst.status_code
1920: FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
1921: WHERE mln.organization_id = p_organization_id
1922: AND mln.inventory_item_id = p_item_id
1923: AND mln.status_id = mmst.status_id (+)
1924: AND mmst.language (+) = userenv('LANG')
1959: , mmst.status_code
1960: , mtlt.primary_quantity
1961: , mtlt.transaction_quantity
1962: FROM mtl_lot_numbers mln, mtl_transaction_lots_temp mtlt,
1963: mtl_material_statuses_tl mmst
1964: WHERE mln.organization_id = p_organization_id
1965: AND mln.inventory_item_id = p_item_id
1966: AND mln.lot_number LIKE (p_lot_number)
1967: AND mln.status_id = mmst.status_id (+)
1979: , mtlt.primary_quantity
1980: , mtlt.transaction_quantity
1981: FROM mtl_lot_numbers mln,
1982: mtl_transaction_lots_temp mtlt, wms_lpn_contents wlc,
1983: mtl_material_statuses_tl mmst
1984: WHERE mln.organization_id = p_organization_id
1985: AND mln.inventory_item_id = p_item_id
1986: AND mln.lot_number LIKE (p_lot_number)
1987: AND mln.status_id = mmst.status_id (+)
2016: , mmst.status_code
2017: , mtlt.primary_quantity
2018: , mtlt.transaction_quantity
2019: FROM mtl_lot_numbers mln,
2020: mtl_transaction_lots_temp mtlt, mtl_material_statuses_tl mmst
2021: WHERE mln.organization_id = p_organization_id
2022: AND mln.inventory_item_id = p_item_id
2023: AND mln.lot_number LIKE (p_lot_number)
2024: AND mln.status_id = mmst.status_id (+)
2042: , mtlt.primary_quantity
2043: , mtlt.transaction_quantity
2044: FROM mtl_lot_numbers mln,
2045: mtl_transaction_lots_temp mtlt, wms_lpn_contents wlc,
2046: mtl_material_statuses_tl mmst
2047: WHERE mln.organization_id = p_organization_id
2048: AND mln.inventory_item_id = p_item_id
2049: AND mln.lot_number LIKE (p_lot_number)
2050: AND mln.status_id = mmst.status_id (+)
2099: , mmst.status_code
2100: , sum(mag.primary_quantity)
2101: , sum(mag.transaction_quantity)
2102: FROM mtl_lot_numbers mln, wms_allocations_gtmp mag,
2103: mtl_material_statuses_tl mmst
2104: WHERE mln.organization_id = p_organization_id
2105: AND mln.inventory_item_id = p_item_id
2106: AND mln.lot_number LIKE (p_lot_number)
2107: AND mln.status_id = mmst.status_id (+)
2122: , sum(mag.primary_quantity)
2123: , sum(mag.transaction_quantity)
2124: FROM mtl_lot_numbers mln,
2125: mtl_transaction_lots_temp mtlt, wms_allocations_gtmp mag,
2126: mtl_material_statuses_tl mmst
2127: WHERE mln.organization_id = p_organization_id
2128: AND mln.inventory_item_id = p_item_id
2129: AND mln.lot_number LIKE (p_lot_number)
2130: AND mln.status_id = mmst.status_id (+)
2155: SELECT DISTINCT mln.lot_number
2156: , mln.description
2157: , mln.expiration_date
2158: , mstl.status_code
2159: FROM mtl_lot_numbers mln, wms_lpn_contents wlpnc, mtl_material_statuses_b mstb, mtl_material_statuses_tl mstl
2160: WHERE wlpnc.parent_lpn_id = p_lpn_id
2161: AND wlpnc.organization_id = p_org_id
2162: AND wlpnc.inventory_item_id = p_item_id
2163: AND ((wlpnc.revision = p_revision
2270: , mln.description
2271: , mln.expiration_date
2272: , mmst.status_code
2273: FROM mtl_lot_numbers mln, wms_lpn_contents wlc,
2274: mtl_material_statuses_tl mmst
2275: WHERE wlc.organization_id = p_organization_id
2276: AND wlc.inventory_item_id = p_item_id
2277: AND wlc.parent_lpn_id = p_lpn_id
2278: AND mln.inventory_item_id = wlc.inventory_item_id
2392: SELECT mln.lot_number
2393: , mln.description
2394: , mln.expiration_date
2395: , mmst.status_code
2396: FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst , mtl_onhand_quantities_detail moqd
2397: WHERE mln.organization_id = p_organization_id
2398: AND mln.inventory_item_id = p_item_id
2399: AND mln.status_id = mmst.status_id (+)
2400: AND mmst.language (+) = userenv('LANG')
2407: SELECT mln.lot_number
2408: , mln.description
2409: , mln.expiration_date
2410: , mmst.status_code
2411: FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst , wms_license_plate_numbers wlpn , wms_lpn_contents wlc
2412: WHERE wlpn.license_plate_number = p_lpn
2413: AND wlc.parent_lpn_id = wlpn.lpn_id
2414: AND mln.lot_number = wlc.lot_number
2415: AND mln.organization_id = p_organization_id
2433: SELECT mln.lot_number
2434: , mln.description
2435: , mln.expiration_date
2436: , mmst.status_code
2437: FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst ,mtl_onhand_quantities_detail moqd
2438: WHERE mln.organization_id = p_organization_id
2439: AND mln.inventory_item_id = p_item_id
2440: AND mln.status_id = mmst.status_id (+)
2441: AND mmst.language (+) = userenv('LANG')
2451: SELECT mln.lot_number
2452: , mln.description
2453: , mln.expiration_date
2454: , mmst.status_code
2455: FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst , wms_license_plate_numbers wlpn , wms_lpn_contents wlc
2456: WHERE wlpn.license_plate_number = p_lpn
2457: AND wlc.parent_lpn_id = wlpn.lpn_id
2458: AND mln.lot_number = wlc.lot_number
2459: AND mln.organization_id = p_organization_id