[Home] [Help]
11: , lot_number
12: , 0
13: , current_status
14: , mms.status_code
15: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
16: WHERE inventory_item_id = p_item_id
17: AND (group_mark_id IS NULL
18: OR group_mark_id = -1
19: )
117: , lot_number
118: , 0
119: , current_status
120: , mms.status_code
121: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
122: WHERE msn.inventory_item_id = p_item_id
123: AND (group_mark_id IS NULL
124: OR group_mark_id = -1
125: )
145: , lot_number
146: , 0
147: , current_status
148: , mms.status_code
149: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
150: WHERE inventory_item_id = p_item_id
151: AND (group_mark_id IS NULL
152: OR group_mark_id = -1
153: )
232: , msn.current_status
233: , mms.status_code
234: FROM rcv_serials_supply rss
235: , rcv_shipment_lines rsl
236: , mtl_serial_numbers msn
237: , mtl_material_statuses_tl mms
238: WHERE rss.shipment_line_id(+) = rsl.shipment_line_id
239: --BUG 3417870: The RSL.shipment_line_status_code will be FULLY
240: -- RECEIVED, so we need to comment it out.
272: AND msn.serial_number LIKE (p_serial)
273: ORDER BY LPAD(msn.serial_number, 20);
274:
275: --If the item is not serial controlled in source org, then fetch the
276: --serials from mtl_serial_numbers which reside in the receiving org
277: -- bug #5508238, Displaying ISSUED OUT serials in the LOV if the profile
278: -- 'INV: Restrict receipt of serials' is set to "No"
279: ELSE
280: OPEN x_serial_number FOR
284: , p_lot_num
285: , 0
286: , msn.current_status
287: , mms.status_code
288: FROM mtl_serial_numbers msn
289: , rcv_shipment_lines rsl
290: , mtl_material_statuses_tl mms
291: WHERE msn.inventory_item_id = p_item_id
292: AND rsl.shipment_header_id = p_shipment_header_id
356: , lot_number
357: , 0
358: , current_status
359: , mms.status_code
360: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
361: WHERE inventory_item_id = p_item_id
362: AND (group_mark_id IS NULL
363: OR group_mark_id = -1
364: OR group_mark_id in (select a.serial_transaction_temp_id from mtl_transaction_lots_temp a
563: SELECT msn.current_status
564: , NVL(mms.status_code, '')
565: INTO x_current_status
566: , x_serial_status
567: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
568: WHERE msn.inventory_item_id = p_item_id
569: AND msn.serial_number = p_serial
570: AND msn.status_id = mms.status_id(+)
571: AND mms.language (+) = userenv('LANG');
571: AND mms.language (+) = userenv('LANG');
572: ELSE
573: SELECT msn.current_status
574: INTO x_current_status
575: FROM mtl_serial_numbers msn
576: WHERE msn.inventory_item_id = p_item_id
577: AND msn.serial_number = p_serial;
578: END IF;
579: EXCEPTION
638: , inv_project.get_project_id
639: , inv_project.get_project_number
640: , inv_project.get_task_id
641: , inv_project.get_task_number
642: FROM mtl_serial_numbers a, mtl_lot_numbers b, mtl_material_statuses_tl mms
643: WHERE a.current_organization_id = p_current_organization_id
644: AND NVL(a.lpn_id, -1) = NVL(p_lpn_id, -1)
645: AND a.inventory_item_id = p_inventory_item_id
646: AND (a.group_mark_id IS NULL OR a.group_mark_id = -1)
690: , inv_project.get_project_id
691: , inv_project.get_project_number
692: , inv_project.get_task_id
693: , inv_project.get_task_number
694: FROM mtl_serial_numbers a, mtl_lot_numbers b, mtl_material_statuses_tl mms
695: WHERE a.current_organization_id = p_current_organization_id
696: AND NVL(a.lpn_id, -1) = NVL(p_lpn_id, -1)
697: AND a.inventory_item_id = p_inventory_item_id
698: AND a.current_subinventory_code = p_current_subinventory_code
784: ,lot_number
785: ,0
786: ,current_status
787: ,mms.status_code
788: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
789: WHERE inventory_item_id = p_inventory_item_id
790: AND (group_mark_id IS NULL
791: OR group_mark_id = -1
792: )
812: ,lot_number
813: ,0
814: ,current_status
815: ,mms.status_code
816: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
817: WHERE inventory_item_id = p_inventory_item_id
818: AND (group_mark_id IS NULL OR group_mark_id = -1)
819: AND ((current_organization_id = p_organization_id
820: AND current_status =1
835: , msn.lot_number
836: , 0
837: , msn.current_status
838: , mms.status_code
839: FROM mtl_serial_numbers msn, mtl_physical_inventory_tags mpit, mtl_material_statuses_tl mms
840: WHERE msn.inventory_item_id = p_inventory_item_id
841: AND (msn.group_mark_id IS NULL
842: OR msn.group_mark_id = -1
843: )
899: ,current_status
900: ,status_code
901: FROM (SELECT serial_number, current_subinventory_code, current_locator_id,
902: lot_number, 0, current_status, mms.status_code
903: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
904: WHERE inventory_item_id = p_inventory_item_id
905: AND (group_mark_id IS NULL OR group_mark_id = -1)
906: AND (( current_organization_id = p_organization_id
907: AND current_status IN (1, 3, 4, 6)
935: ) = 'Y'
936: UNION
937: SELECT serial_number, current_subinventory_code, current_locator_id,
938: lot_number, 0, current_status, mms.status_code
939: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
940: WHERE inventory_item_id = p_inventory_item_id
941: AND (group_mark_id IS NULL OR group_mark_id = -1)
942: AND ( ( current_organization_id = p_organization_id
943: AND current_status = 1
973: , msn.lot_number
974: , 0
975: , msn.current_status
976: , mms.status_code
977: FROM mtl_serial_numbers msn, mtl_physical_inventory_tags mpit, mtl_material_statuses_tl mms
978: WHERE msn.inventory_item_id = p_inventory_item_id
979: AND (msn.group_mark_id IS NULL
980: OR msn.group_mark_id = -1
981: )
1043: , msn.lpn_id
1044: , wlpn.license_plate_number
1045: , msn.current_status
1046: , msik.primary_uom_code
1047: FROM mtl_serial_numbers msn, mtl_system_items_kfv msik, wms_license_plate_numbers wlpn
1048: WHERE (msn.group_mark_id IS NULL
1049: OR msn.group_mark_id = -1
1050: )
1051: AND ((msn.current_organization_id = p_organization_id
1084: , msn.lpn_id
1085: , wlpn.license_plate_number
1086: , msn.current_status
1087: , msik.primary_uom_code
1088: FROM mtl_serial_numbers msn, mtl_physical_inventory_tags mpit, mtl_system_items_kfv msik, wms_license_plate_numbers wlpn
1089: WHERE (msn.group_mark_id IS NULL
1090: OR msn.group_mark_id = -1
1091: )
1092: AND msn.current_organization_id = p_organization_id
1163: , lot_number
1164: , 0
1165: , current_status
1166: , mms.status_code
1167: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1168: WHERE inventory_item_id = p_inventory_item_id
1169: AND (group_mark_id IS NULL
1170: OR group_mark_id = -1
1171: )
1248: , msn.lot_number
1249: , 0
1250: , msn.current_status
1251: , mms.status_code
1252: FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce, mtl_material_statuses_tl mms
1253: WHERE msn.inventory_item_id = p_inventory_item_id
1254: AND ((msn.current_organization_id = p_organization_id
1255: AND msn.current_status IN (3, 4)
1256: )
1299: , msn.lot_number
1300: , 0
1301: , msn.current_status
1302: , mms.status_code
1303: FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
1304: mtl_material_statuses_tl mms, mtl_cycle_count_entries mcce
1305: WHERE msn.inventory_item_id = p_inventory_item_id
1306: AND (msn.group_mark_id IS NULL
1307: OR msn.group_mark_id = -1
1396: , lot_number
1397: , 0
1398: , current_status
1399: , mms.status_code
1400: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1401: WHERE inventory_item_id = p_inventory_item_id
1402: AND (group_mark_id IS NULL
1403: OR group_mark_id = -1
1404: )
1483: , msn.lot_number
1484: , 0
1485: , msn.current_status
1486: , mms.status_code
1487: FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce, mtl_material_statuses_tl mms
1488: WHERE msn.inventory_item_id = p_inventory_item_id
1489: AND ((msn.current_organization_id = p_organization_id
1490: AND msn.current_status IN (3, 4)
1491: )
1536: , msn.lot_number
1537: , 0
1538: , msn.current_status
1539: , mms.status_code
1540: FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce,
1541: mtl_material_statuses_tl mms, mtl_cc_serial_numbers mcsn
1542: WHERE msn.inventory_item_id = p_inventory_item_id
1543: AND (msn.group_mark_id IS NULL
1544: OR msn.group_mark_id = -1
1620: , msn.lpn_id
1621: , wlpn.license_plate_number
1622: , msn.current_status
1623: , msik.primary_uom_code
1624: FROM mtl_serial_numbers msn, mtl_system_items_kfv msik, wms_license_plate_numbers wlpn
1625: WHERE (msn.group_mark_id IS NULL
1626: OR msn.group_mark_id = -1
1627: )
1628: AND ((msn.current_organization_id = p_organization_id
1690: , msn.lpn_id
1691: , wlpn.license_plate_number
1692: , msn.current_status
1693: , msik.primary_uom_code
1694: FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce,
1695: mtl_system_items_kfv msik, wms_license_plate_numbers wlpn
1696: WHERE (msn.group_mark_id IS NULL
1697: OR msn.group_mark_id = -1
1698: )
1743: , lot_number
1744: , 0
1745: , current_status
1746: , mms.status_code
1747: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1748: WHERE current_organization_id = p_organization_id
1749: AND inventory_item_id = p_inventory_item_id
1750: --AND current_status IN (1, 3, 5)
1751: AND current_status IN (1, 3, 5, 7)
1780: , lot_number
1781: , 0
1782: , current_status
1783: , status_code
1784: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1785: WHERE current_organization_id = p_organization_id
1786: AND inventory_item_id = p_inventory_item_id
1787: --AND current_status IN (1, 3, 5)
1788: AND current_status IN (1, 3, 5, 7)
1807: , 0
1808: , 0
1809: , ''
1810: , ''
1811: FROM mtl_serial_numbers
1812: WHERE lpn_id = p_lpn_id
1813: AND inventory_item_id = p_item_id
1814: AND NVL(lot_number, 'NOLOT') = NVL(p_lot, 'NOLOT')
1815: AND serial_number LIKE (p_serial)
1848: , lot_number
1849: , 0
1850: , current_status
1851: , mms.status_code
1852: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1853: WHERE msn.inventory_item_id = p_item_id
1854: AND msn.lpn_id = p_lpn_id
1855: AND msn.current_organization_id = p_organization_id
1856: --AND msn.current_status = 5 /* Intransit */
1907: , lot_number
1908: , 0
1909: , current_status
1910: , ' '
1911: FROM mtl_serial_numbers
1912: WHERE inventory_item_id = p_inventory_item_id
1913: AND current_organization_id = p_organization_id
1914: AND (group_mark_id IS NULL
1915: OR group_mark_id = -1
1919: ORDER BY LPAD(serial_number, 20);
1920: ELSE
1921: OPEN x_serial FOR
1922: select serial_number,current_subinventory_code,current_locator_id,lot_number,0,0,''
1923: from mtl_serial_numbers msn
1924: where inventory_item_id = p_inventory_item_id
1925: and current_organization_id = p_organization_id
1926: and (group_mark_id is null or group_mark_id = -1 )
1927: and nvl(current_subinventory_code,'@@@') = nvl(p_subinventory_code,'@@@')
1943: , msn.lot_number
1944: , 0
1945: , msn.current_status
1946: , ''
1947: FROM mtl_serial_numbers msn
1948: WHERE msn.current_organization_id = p_organization_id
1949: AND msn.inventory_item_id = p_item_id
1950: AND msn.lpn_id = p_lpn_id
1951: AND NVL(line_mark_id, -999) <> 1
1949: AND msn.inventory_item_id = p_item_id
1950: AND msn.lpn_id = p_lpn_id
1951: AND NVL(line_mark_id, -999) <> 1
1952: AND NOT EXISTS (SELECT 1
1953: FROM mtl_serial_numbers_temp msnt , wms_loaded_quantities_v wlq
1954: WHERE (msnt.transaction_temp_id = wlq.transaction_temp_id
1955: OR msnt.transaction_temp_id = wlq.serial_transaction_temp_id)
1956: AND msn.serial_number = msnt.fm_serial_number
1957: AND msn.inventory_item_id = wlq.inventory_item_id
1978: , msn.lot_number
1979: , 0
1980: , msn.current_status
1981: , ''
1982: FROM mtl_serial_numbers msn
1983: WHERE msn.current_organization_id = p_organization_id
1984: AND msn.inventory_item_id = p_item_id
1985: AND msn.lpn_id = p_lpn_id
1986: AND NVL(line_mark_id, -9) <> 1
1984: AND msn.inventory_item_id = p_item_id
1985: AND msn.lpn_id = p_lpn_id
1986: AND NVL(line_mark_id, -9) <> 1
1987: AND NOT EXISTS (SELECT 1
1988: FROM mtl_serial_numbers_temp msnt , wms_loaded_quantities_v wlq
1989: WHERE (msnt.transaction_temp_id = wlq.transaction_temp_id
1990: OR msnt.transaction_temp_id = wlq.serial_transaction_temp_id)
1991: AND msn.serial_number = msnt.fm_serial_number
1992: AND msn.inventory_item_id = wlq.inventory_item_id
2009: , msn.lot_number
2010: , 0
2011: , msn.current_status
2012: , ''
2013: FROM mtl_serial_numbers msn
2014: WHERE msn.current_organization_id = p_organization_id
2015: AND msn.inventory_item_id = p_item_id
2016: AND msn.lpn_id = p_lpn_id
2017: AND NVL(line_mark_id, -9) <> 1
2039: SELECT msn.serial_number
2040: , msn.current_subinventory_code
2041: , msn.current_locator_id
2042: , msn.lot_number
2043: FROM mtl_serial_numbers msn
2044: WHERE msn.current_organization_id = p_organization_id
2045: AND msn.inventory_item_id = p_item_id
2046: AND msn.lpn_id = p_lpn_id
2047: AND (group_mark_id IS NULL
2089: , lot_number
2090: , 'NULL'
2091: , current_status
2092: , 'NULL'
2093: FROM mtl_serial_numbers
2094: WHERE inventory_item_id = NVL(p_inventory_item_id, inventory_item_id)
2095: AND current_organization_id = p_organization_id
2096: AND (group_mark_id IS NULL
2097: OR group_mark_id = -1
2126: , lot_number
2127: , 'NULL'
2128: , current_status
2129: , 'NULL'
2130: FROM mtl_serial_numbers msn
2131: WHERE inventory_item_id = p_inventory_item_id
2132: AND current_organization_id = p_organization_id
2133: AND NVL(line_mark_id, -999) <> 1
2134: AND current_subinventory_code = p_subinventory_code
2145: NULL, NULL,
2146: p_organization_id, inventory_item_id,
2147: NULL, NULL, NULL, serial_number, 'S') = 'Y'
2148: AND NOT EXISTS (SELECT 1
2149: FROM mtl_serial_numbers_temp msnt , wms_loaded_quantities_v wlq
2150: WHERE (msnt.transaction_temp_id = wlq.transaction_temp_id
2151: OR msnt.transaction_temp_id = wlq.serial_transaction_temp_id)
2152: AND serial_number = msnt.fm_serial_number
2153: AND msn.inventory_item_id = wlq.inventory_item_id
2203: , lot_number
2204: , 'NULL'
2205: , current_status
2206: , 'NULL'
2207: FROM mtl_serial_numbers
2208: WHERE inventory_item_id = NVL(p_inventory_item_id, inventory_item_id)
2209: AND current_organization_id = p_organization_id
2210: AND (group_mark_id IS NULL
2211: OR group_mark_id = -1
2263: , mil.concatenated_segments
2264: , msn.revision
2265: , msn.cost_group_id
2266: , ccg.cost_group
2267: FROM mtl_item_locations_kfv mil, mtl_serial_numbers msn, cst_cost_groups ccg, mtl_material_statuses_tl mms
2268: WHERE (group_mark_id IS NULL
2269: OR group_mark_id = -1
2270: )
2271: AND mms.status_id(+) = msn.status_id
2301: , mil.concatenated_segments
2302: , msn.revision
2303: , msn.cost_group_id
2304: , ccg.cost_group
2305: FROM mtl_item_locations_kfv mil, mtl_serial_numbers msn, cst_cost_groups ccg, mtl_material_statuses_tl mms
2306: WHERE (group_mark_id IS NULL
2307: OR group_mark_id = -1
2308: )
2309: AND mms.status_id(+) = msn.status_id
2407: , lot_number
2408: , 0
2409: , current_status
2410: , mms.status_code
2411: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2412: WHERE inventory_item_id = p_item_id
2413: AND (group_mark_id IS NULL
2414: OR group_mark_id = -1
2415: )
2473: , msn.lot_number
2474: , 0
2475: , msn.current_status
2476: , mms.status_code
2477: FROM mtl_serial_numbers_temp msnt, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2478: WHERE msn.inventory_item_id = p_item_id
2479: AND msn.current_organization_id = p_organization_id
2480: AND msn.current_status = 3
2481: AND msn.current_subinventory_code = p_subinv_code
2497: , msn.lot_number
2498: , 0
2499: , msn.current_status
2500: , mms.status_code
2501: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2502: WHERE msn.inventory_item_id = p_item_id
2503: AND msn.current_organization_id = p_organization_id
2504: AND msn.current_status = 3
2505: AND msn.current_subinventory_code = p_subinv_code
2539: , lot_number
2540: , 0
2541: , current_status
2542: , mms.status_code
2543: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2544: WHERE inventory_item_id = p_item_id
2545: AND (group_mark_id IS NULL
2546: OR group_mark_id = -1
2547: )
2594: , msn.lot_number
2595: , 0
2596: , msn.current_status
2597: , mms.status_code
2598: FROM wms_allocations_gtmp mag, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2599: WHERE msn.inventory_item_id = p_item_id
2600: AND msn.current_organization_id = p_organization_id
2601: AND msn.current_status = 3
2602: AND msn.current_subinventory_code = p_subinv_code
2620: , msn.lot_number
2621: , 0
2622: , msn.current_status
2623: , mms.status_code
2624: FROM wms_allocations_gtmp mag, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2625: WHERE msn.inventory_item_id = p_item_id
2626: AND msn.current_organization_id = p_organization_id
2627: AND msn.current_status = 3
2628: AND msn.current_subinventory_code = p_subinv_code
2652: 'NULL'
2653: , 0
2654: , --current_status,
2655: 'NULL'
2656: FROM mtl_serial_numbers
2657: WHERE current_organization_id = p_organization_id
2658: AND serial_number LIKE (p_serial)
2659: ORDER BY LPAD(serial_number, 20);
2660: END get_all_serial_lov;
2677: 'NULL'
2678: , 0
2679: , --current_status,
2680: 'NULL'
2681: FROM mtl_serial_numbers
2682: WHERE current_organization_id = p_organization_id
2683: AND inventory_item_id = p_inventory_item_id
2684: AND LENGTH(serial_number) = LENGTH(p_from_serial_number)
2685: AND serial_number LIKE (l_prefix || '%')
2697: dummy_e NUMBER;
2698: dummy_f VARCHAR2(20);
2699: BEGIN
2700: IF (p_upd_group_id = 1) THEN
2701: UPDATE mtl_serial_numbers
2702: SET group_mark_id = NULL
2703: WHERE current_organization_id = p_org_id
2704: AND group_mark_id IS NOT NULL
2705: AND lpn_id = p_lpn_id
2723: 'NULL'
2724: , 0
2725: , --current_status,
2726: 'NULL'
2727: FROM mtl_serial_numbers
2728: WHERE current_organization_id = p_org_id
2729: AND (group_mark_id IS NULL
2730: OR group_mark_id = -1
2731: )
2755: , 0
2756: , 0
2757: , ''
2758: , ''
2759: FROM mtl_serial_numbers_temp
2760: WHERE transaction_temp_id = p_temp_id
2761: ORDER BY LPAD(fm_serial_number, 20);
2762: ELSE
2763: OPEN x_serial_number FOR
2767: , 0
2768: , 0
2769: , ''
2770: , ''
2771: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
2772: WHERE mtlt.transaction_temp_id = p_temp_id
2773: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
2774: ORDER BY LPAD(fm_serial_number, 20);
2775: END IF;
2803: inv_project.get_project_id
2804: , inv_project.get_project_number
2805: , inv_project.get_task_id
2806: , inv_project.get_task_number
2807: FROM mtl_serial_numbers msn
2808: , mtl_system_items_vl msiv
2809: , mtl_item_locations_kfv milv
2810: , mtl_secondary_inventories msi
2811: WHERE msn.current_organization_id = p_current_organization_id
2820: WHERE mr.reservation_id = msn.reservation_id
2821: AND NVL(mr.staged_flag, 'N') = 'Y')
2822: AND NOT EXISTS (
2823: SELECT 1
2824: FROM mtl_serial_numbers_temp msnt
2825: WHERE msn.serial_number BETWEEN msnt.fm_serial_number
2826: AND msnt.to_serial_number)
2827: )
2828:
2866: , current_status
2867: , mms.status_code
2868: , ''
2869: , msn.revision
2870: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2871: WHERE inventory_item_id = p_item_id
2872: AND (group_mark_id IS NULL
2873: OR group_mark_id = -1
2874: OR group_mark_id IN (SELECT transaction_temp_id
2924: , lot_number
2925: , ''
2926: , current_status
2927: , mms.status_code
2928: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2929: WHERE current_organization_id = p_organization_id
2930: AND inventory_item_id = p_item_id
2931: AND msn.status_id = mms.status_id(+)
2932: AND mms.language (+) = userenv('LANG')
2977: , lot_number
2978: , ''
2979: , current_status
2980: , mms.status_code
2981: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2982: WHERE current_organization_id = p_organization_id
2983: AND inventory_item_id = p_item_id
2984: AND current_status = 3
2985: AND msn.status_id = mms.status_id(+)
3043: , lot_number
3044: , ''
3045: , current_status
3046: , mms.status_code
3047: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3048: WHERE current_organization_id = p_organization_id
3049: AND inventory_item_id = p_item_id
3050: AND msn.status_id = mms.status_id(+)
3051: AND mms.language (+) = userenv('LANG')
3107: , lot_number
3108: , 0
3109: , current_status
3110: , mms.status_code
3111: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3112: WHERE inventory_item_id = p_item_id
3113: AND (group_mark_id IS NULL
3114: OR group_mark_id = -1
3115: )
3152: , lot_number
3153: , 0
3154: , current_status
3155: , mms.status_code
3156: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3157: WHERE inventory_item_id = p_item_id
3158: AND (group_mark_id IS NULL
3159: OR group_mark_id = -1
3160: )
3216: , lot_number
3217: , 0
3218: , current_status
3219: , mms.status_code
3220: from mtl_serial_numbers msn, mtl_material_statuses_tl mms
3221: where inventory_item_id = l_wip_assembly_id
3222: and msn.status_id = mms.status_id(+)
3223: AND mms.language (+) = userenv('LANG')
3224: AND CURRENT_organization_id = p_organization_id
3647: , x_n_attribute7
3648: , x_n_attribute8
3649: , x_n_attribute9
3650: , x_n_attribute10
3651: FROM mtl_serial_numbers
3652: WHERE inventory_item_id = p_inventory_item_id
3653: AND serial_number = p_serial_number;
3654: EXCEPTION
3655: WHEN OTHERS THEN
3674: , lot_number
3675: , 0
3676: , current_status
3677: , mms.status_code
3678: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3679: WHERE lpn_id = p_lpn_id
3680: AND current_organization_id = p_organization_id
3681: AND inventory_item_id = p_item_id
3682: AND NVL(lot_number, 'NOLOT') = NVL(p_lot_number, 'NOLOT')
3718: , lot_number
3719: , 0
3720: , current_status
3721: , mms.status_code
3722: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3723: WHERE lpn_id = p_lpn_id
3724: AND current_organization_id = p_organization_id
3725: AND inventory_item_id = p_item_id
3726: AND (group_mark_id IS NULL
3778: , 0
3779: , 0
3780: , ''
3781: , ''
3782: FROM mtl_serial_numbers msn
3783: , rcv_serials_supply rss
3784: , rcv_supply rs
3785: WHERE msn.lpn_id = p_lpn_id
3786: AND msn.inventory_item_id = p_inventory_item_id
3933: , lot_number
3934: , 0
3935: , current_status
3936: , mms.status_code
3937: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3938: WHERE msn.inventory_item_id = p_item_id
3939: AND (group_mark_id IS NULL
3940: OR group_mark_id = -1
3941: )
3962: , lot_number
3963: , 0
3964: , current_status
3965: , mms.status_code
3966: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3967: WHERE inventory_item_id = p_item_id
3968: AND (group_mark_id IS NULL
3969: OR group_mark_id = -1
3970: )
4013: , lot_number
4014: , 0
4015: , current_status
4016: , mms.status_code
4017: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4018: WHERE inventory_item_id = p_item_id
4019: AND (group_mark_id IS NULL
4020: OR group_mark_id = -1
4021: )
4038: , current_status
4039: , mms.status_code
4040: FROM rcv_serials_supply rss
4041: , rcv_shipment_lines rsl
4042: , mtl_serial_numbers msn
4043: , mtl_material_statuses_tl mms
4044: WHERE rss.shipment_line_id(+) = rsl.shipment_line_id
4045: AND nvl(rss.supply_type_code, 'SHIPMENT') = 'SHIPMENT'
4046: AND (msn.group_mark_id IS NULL OR msn.group_mark_id = -1)
4180: , lot_number
4181: , 0
4182: , current_status
4183: , status_code
4184: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4185: WHERE current_organization_id = p_organization_id
4186: AND inventory_item_id = p_inventory_item_id
4187: --AND current_status IN (1, 3, 5)
4188: AND current_status IN (1, 3, 5, 7)
4209: , lot_number
4210: , 0
4211: , current_status
4212: , mms.status_code
4213: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4214: WHERE current_organization_id = p_organization_id
4215: AND inventory_item_id = p_inventory_item_id
4216: --AND current_status IN (1, 3, 5)
4217: AND current_status IN (1, 3, 5, 7)
4248: , lot_number
4249: , 0
4250: , current_status
4251: , status_code
4252: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4253: WHERE current_organization_id = p_organization_id
4254: AND inventory_item_id = p_inventory_item_id
4255: AND (p_lot_number IS NULL
4256: OR lot_number = p_lot_number)
4279: , lot_number
4280: , 0
4281: , current_status
4282: , mms.status_code
4283: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4284: WHERE current_organization_id = p_organization_id
4285: AND inventory_item_id = p_inventory_item_id
4286: --AND current_status IN (1, 3, 5)
4287: AND current_status IN (1, 3, 5, 7)
4327: , lot_number
4328: , 0
4329: , current_status
4330: , mms.status_code
4331: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4332: WHERE inventory_item_id = p_inventory_item_id
4333: AND current_organization_id = p_organization_id
4334: AND (group_mark_id IS NULL OR group_mark_id = -1)
4335: AND current_status = 1