[Home] [Help]
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 (+)
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
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
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
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
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
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
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 (+)
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 (+)
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')
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')
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')
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')
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
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')
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')
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)
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);
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')
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 (+)
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 (+)
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 (+)
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 (+)
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 (+)
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 (+)
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
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
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')
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
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')
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