[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: )
103: , lot_number
104: , 0
105: , current_status
106: , mms.status_code
107: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
108: WHERE msn.inventory_item_id = p_item_id
109: AND (group_mark_id IS NULL
110: OR group_mark_id = -1
111: )
131: , lot_number
132: , 0
133: , current_status
134: , mms.status_code
135: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
136: WHERE inventory_item_id = p_item_id
137: AND (group_mark_id IS NULL
138: OR group_mark_id = -1
139: )
217: , msn.current_status
218: , mms.status_code
219: FROM rcv_serials_supply rss
220: , rcv_shipment_lines rsl
221: , mtl_serial_numbers msn
222: , mtl_material_statuses_tl mms
223: WHERE rss.shipment_line_id(+) = rsl.shipment_line_id
224: --BUG 3417870: The RSL.shipment_line_status_code will be FULLY
225: -- RECEIVED, so we need to comment it out.
254: AND msn.serial_number LIKE (p_serial)
255: ORDER BY LPAD(msn.serial_number, 20);
256:
257: --If the item is not serial controlled in source org, then fetch the
258: --serials from mtl_serial_numbers which reside in the receiving org
259: -- bug #5508238, Displaying ISSUED OUT serials in the LOV if the profile
260: -- 'INV: Restrict receipt of serials' is set to "No"
261: ELSE
262: OPEN x_serial_number FOR
266: , p_lot_num
267: , 0
268: , msn.current_status
269: , mms.status_code
270: FROM mtl_serial_numbers msn
271: , rcv_shipment_lines rsl
272: , mtl_material_statuses_tl mms
273: WHERE msn.inventory_item_id = p_item_id
274: AND rsl.shipment_header_id = p_shipment_header_id
335: , lot_number
336: , 0
337: , current_status
338: , mms.status_code
339: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
340: WHERE inventory_item_id = p_item_id
341: AND (group_mark_id IS NULL
342: OR group_mark_id = -1
343: OR group_mark_id in (select a.serial_transaction_temp_id from mtl_transaction_lots_temp a
509: SELECT msn.current_status
510: , NVL(mms.status_code, '')
511: INTO x_current_status
512: , x_serial_status
513: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
514: WHERE msn.inventory_item_id = p_item_id
515: AND msn.serial_number = p_serial
516: AND msn.status_id = mms.status_id(+)
517: AND mms.language (+) = userenv('LANG');
517: AND mms.language (+) = userenv('LANG');
518: ELSE
519: SELECT msn.current_status
520: INTO x_current_status
521: FROM mtl_serial_numbers msn
522: WHERE msn.inventory_item_id = p_item_id
523: AND msn.serial_number = p_serial;
524: END IF;
525: EXCEPTION
580: , inv_project.get_project_id
581: , inv_project.get_project_number
582: , inv_project.get_task_id
583: , inv_project.get_task_number
584: FROM mtl_serial_numbers a, mtl_lot_numbers b, mtl_material_statuses_tl mms
585: WHERE a.current_organization_id = p_current_organization_id
586: AND NVL(a.lpn_id, -1) = NVL(p_lpn_id, -1)
587: AND a.inventory_item_id = p_inventory_item_id
588: AND (a.group_mark_id IS NULL OR a.group_mark_id = -1)
632: , inv_project.get_project_id
633: , inv_project.get_project_number
634: , inv_project.get_task_id
635: , inv_project.get_task_number
636: FROM mtl_serial_numbers a, mtl_lot_numbers b, mtl_material_statuses_tl mms
637: WHERE a.current_organization_id = p_current_organization_id
638: AND NVL(a.lpn_id, -1) = NVL(p_lpn_id, -1)
639: AND a.inventory_item_id = p_inventory_item_id
640: AND a.current_subinventory_code = p_current_subinventory_code
725: ,lot_number
726: ,0
727: ,current_status
728: ,mms.status_code
729: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
730: WHERE inventory_item_id = p_inventory_item_id
731: AND (group_mark_id IS NULL
732: OR group_mark_id = -1
733: )
751: ,lot_number
752: ,0
753: ,current_status
754: ,mms.status_code
755: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
756: WHERE inventory_item_id = p_inventory_item_id
757: AND (group_mark_id IS NULL OR group_mark_id = -1)
758: AND ((current_organization_id = p_organization_id
759: AND current_status =1
772: , msn.lot_number
773: , 0
774: , msn.current_status
775: , mms.status_code
776: FROM mtl_serial_numbers msn, mtl_physical_inventory_tags mpit, mtl_material_statuses_tl mms
777: WHERE msn.inventory_item_id = p_inventory_item_id
778: AND (msn.group_mark_id IS NULL
779: OR msn.group_mark_id = -1
780: )
836: ,current_status
837: ,status_code
838: FROM (SELECT serial_number, current_subinventory_code, current_locator_id,
839: lot_number, 0, current_status, mms.status_code
840: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
841: WHERE inventory_item_id = p_inventory_item_id
842: AND (group_mark_id IS NULL OR group_mark_id = -1)
843: AND (( current_organization_id = p_organization_id
844: AND current_status IN (1, 3, 4, 6)
872: ) = 'Y'
873: UNION
874: SELECT serial_number, current_subinventory_code, current_locator_id,
875: lot_number, 0, current_status, mms.status_code
876: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
877: WHERE inventory_item_id = p_inventory_item_id
878: AND (group_mark_id IS NULL OR group_mark_id = -1)
879: AND ( ( current_organization_id = p_organization_id
880: AND current_status = 1
910: , msn.lot_number
911: , 0
912: , msn.current_status
913: , mms.status_code
914: FROM mtl_serial_numbers msn, mtl_physical_inventory_tags mpit, mtl_material_statuses_tl mms
915: WHERE msn.inventory_item_id = p_inventory_item_id
916: AND (msn.group_mark_id IS NULL
917: OR msn.group_mark_id = -1
918: )
980: , msn.lpn_id
981: , wlpn.license_plate_number
982: , msn.current_status
983: , msik.primary_uom_code
984: FROM mtl_serial_numbers msn, mtl_system_items_kfv msik, wms_license_plate_numbers wlpn
985: WHERE (msn.group_mark_id IS NULL
986: OR msn.group_mark_id = -1
987: )
988: AND ((msn.current_organization_id = p_organization_id
1021: , msn.lpn_id
1022: , wlpn.license_plate_number
1023: , msn.current_status
1024: , msik.primary_uom_code
1025: FROM mtl_serial_numbers msn, mtl_physical_inventory_tags mpit, mtl_system_items_kfv msik, wms_license_plate_numbers wlpn
1026: WHERE (msn.group_mark_id IS NULL
1027: OR msn.group_mark_id = -1
1028: )
1029: AND msn.current_organization_id = p_organization_id
1100: , lot_number
1101: , 0
1102: , current_status
1103: , mms.status_code
1104: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1105: WHERE inventory_item_id = p_inventory_item_id
1106: AND (group_mark_id IS NULL
1107: OR group_mark_id = -1
1108: )
1185: , msn.lot_number
1186: , 0
1187: , msn.current_status
1188: , mms.status_code
1189: FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce, mtl_material_statuses_tl mms
1190: WHERE msn.inventory_item_id = p_inventory_item_id
1191: AND ((msn.current_organization_id = p_organization_id
1192: AND msn.current_status IN (3, 4)
1193: )
1236: , msn.lot_number
1237: , 0
1238: , msn.current_status
1239: , mms.status_code
1240: FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
1241: mtl_material_statuses_tl mms, mtl_cycle_count_entries mcce
1242: WHERE msn.inventory_item_id = p_inventory_item_id
1243: AND (msn.group_mark_id IS NULL
1244: OR msn.group_mark_id = -1
1333: , lot_number
1334: , 0
1335: , current_status
1336: , mms.status_code
1337: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1338: WHERE inventory_item_id = p_inventory_item_id
1339: AND (group_mark_id IS NULL
1340: OR group_mark_id = -1
1341: )
1420: , msn.lot_number
1421: , 0
1422: , msn.current_status
1423: , mms.status_code
1424: FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce, mtl_material_statuses_tl mms
1425: WHERE msn.inventory_item_id = p_inventory_item_id
1426: AND ((msn.current_organization_id = p_organization_id
1427: AND msn.current_status IN (3, 4)
1428: )
1473: , msn.lot_number
1474: , 0
1475: , msn.current_status
1476: , mms.status_code
1477: FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce,
1478: mtl_material_statuses_tl mms, mtl_cc_serial_numbers mcsn
1479: WHERE msn.inventory_item_id = p_inventory_item_id
1480: AND (msn.group_mark_id IS NULL
1481: OR msn.group_mark_id = -1
1557: , msn.lpn_id
1558: , wlpn.license_plate_number
1559: , msn.current_status
1560: , msik.primary_uom_code
1561: FROM mtl_serial_numbers msn, mtl_system_items_kfv msik, wms_license_plate_numbers wlpn
1562: WHERE (msn.group_mark_id IS NULL
1563: OR msn.group_mark_id = -1
1564: )
1565: AND ((msn.current_organization_id = p_organization_id
1627: , msn.lpn_id
1628: , wlpn.license_plate_number
1629: , msn.current_status
1630: , msik.primary_uom_code
1631: FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce,
1632: mtl_system_items_kfv msik, wms_license_plate_numbers wlpn
1633: WHERE (msn.group_mark_id IS NULL
1634: OR msn.group_mark_id = -1
1635: )
1680: , lot_number
1681: , 0
1682: , current_status
1683: , mms.status_code
1684: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1685: WHERE current_organization_id = p_organization_id
1686: AND inventory_item_id = p_inventory_item_id
1687: --AND current_status IN (1, 3, 5)
1688: AND current_status IN (1, 3, 5, 7)
1717: , lot_number
1718: , 0
1719: , current_status
1720: , status_code
1721: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1722: WHERE current_organization_id = p_organization_id
1723: AND inventory_item_id = p_inventory_item_id
1724: --AND current_status IN (1, 3, 5)
1725: AND current_status IN (1, 3, 5, 7)
1744: , 0
1745: , 0
1746: , ''
1747: , ''
1748: FROM mtl_serial_numbers
1749: WHERE lpn_id = p_lpn_id
1750: AND inventory_item_id = p_item_id
1751: AND NVL(lot_number, 'NOLOT') = NVL(p_lot, 'NOLOT')
1752: AND serial_number LIKE (p_serial)
1785: , lot_number
1786: , 0
1787: , current_status
1788: , mms.status_code
1789: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1790: WHERE msn.inventory_item_id = p_item_id
1791: AND msn.lpn_id = p_lpn_id
1792: AND msn.current_organization_id = p_organization_id
1793: --AND msn.current_status = 5 /* Intransit */
1844: , lot_number
1845: , 0
1846: , current_status
1847: , ' '
1848: FROM mtl_serial_numbers
1849: WHERE inventory_item_id = p_inventory_item_id
1850: AND current_organization_id = p_organization_id
1851: AND (group_mark_id IS NULL
1852: OR group_mark_id = -1
1856: ORDER BY LPAD(serial_number, 20);
1857: ELSE
1858: OPEN x_serial FOR
1859: select serial_number,current_subinventory_code,current_locator_id,lot_number,0,0,''
1860: from mtl_serial_numbers msn
1861: where inventory_item_id = p_inventory_item_id
1862: and current_organization_id = p_organization_id
1863: and (group_mark_id is null or group_mark_id = -1 )
1864: and nvl(current_subinventory_code,'@@@') = nvl(p_subinventory_code,'@@@')
1880: , msn.lot_number
1881: , 0
1882: , msn.current_status
1883: , ''
1884: FROM mtl_serial_numbers msn
1885: WHERE msn.current_organization_id = p_organization_id
1886: AND msn.inventory_item_id = p_item_id
1887: AND msn.lpn_id = p_lpn_id
1888: AND NVL(line_mark_id, -999) <> 1
1907: , msn.lot_number
1908: , 0
1909: , msn.current_status
1910: , ''
1911: FROM mtl_serial_numbers msn
1912: WHERE msn.current_organization_id = p_organization_id
1913: AND msn.inventory_item_id = p_item_id
1914: AND msn.lpn_id = p_lpn_id
1915: AND NVL(line_mark_id, -9) <> 1
1930: , msn.lot_number
1931: , 0
1932: , msn.current_status
1933: , ''
1934: FROM mtl_serial_numbers msn
1935: WHERE msn.current_organization_id = p_organization_id
1936: AND msn.inventory_item_id = p_item_id
1937: AND msn.lpn_id = p_lpn_id
1938: AND NVL(line_mark_id, -9) <> 1
1960: SELECT msn.serial_number
1961: , msn.current_subinventory_code
1962: , msn.current_locator_id
1963: , msn.lot_number
1964: FROM mtl_serial_numbers msn
1965: WHERE msn.current_organization_id = p_organization_id
1966: AND msn.inventory_item_id = p_item_id
1967: AND msn.lpn_id = p_lpn_id
1968: AND (group_mark_id IS NULL
2010: , lot_number
2011: , 'NULL'
2012: , current_status
2013: , 'NULL'
2014: FROM mtl_serial_numbers
2015: WHERE inventory_item_id = NVL(p_inventory_item_id, inventory_item_id)
2016: AND current_organization_id = p_organization_id
2017: AND (group_mark_id IS NULL
2018: OR group_mark_id = -1
2047: , lot_number
2048: , 'NULL'
2049: , current_status
2050: , 'NULL'
2051: FROM mtl_serial_numbers
2052: WHERE inventory_item_id = p_inventory_item_id
2053: AND current_organization_id = p_organization_id
2054: AND NVL(line_mark_id, -999) <> 1
2055: AND current_subinventory_code = p_subinventory_code
2116: , lot_number
2117: , 'NULL'
2118: , current_status
2119: , 'NULL'
2120: FROM mtl_serial_numbers
2121: WHERE inventory_item_id = NVL(p_inventory_item_id, inventory_item_id)
2122: AND current_organization_id = p_organization_id
2123: AND (group_mark_id IS NULL
2124: OR group_mark_id = -1
2176: , mil.concatenated_segments
2177: , msn.revision
2178: , msn.cost_group_id
2179: , ccg.cost_group
2180: FROM mtl_item_locations_kfv mil, mtl_serial_numbers msn, cst_cost_groups ccg, mtl_material_statuses_tl mms
2181: WHERE (group_mark_id IS NULL
2182: OR group_mark_id = -1
2183: )
2184: AND mms.status_id(+) = msn.status_id
2214: , mil.concatenated_segments
2215: , msn.revision
2216: , msn.cost_group_id
2217: , ccg.cost_group
2218: FROM mtl_item_locations_kfv mil, mtl_serial_numbers msn, cst_cost_groups ccg, mtl_material_statuses_tl mms
2219: WHERE (group_mark_id IS NULL
2220: OR group_mark_id = -1
2221: )
2222: AND mms.status_id(+) = msn.status_id
2320: , lot_number
2321: , 0
2322: , current_status
2323: , mms.status_code
2324: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2325: WHERE inventory_item_id = p_item_id
2326: AND (group_mark_id IS NULL
2327: OR group_mark_id = -1
2328: )
2386: , msn.lot_number
2387: , 0
2388: , msn.current_status
2389: , mms.status_code
2390: FROM mtl_serial_numbers_temp msnt, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2391: WHERE msn.inventory_item_id = p_item_id
2392: AND msn.current_organization_id = p_organization_id
2393: AND msn.current_status = 3
2394: AND msn.current_subinventory_code = p_subinv_code
2410: , msn.lot_number
2411: , 0
2412: , msn.current_status
2413: , mms.status_code
2414: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2415: WHERE msn.inventory_item_id = p_item_id
2416: AND msn.current_organization_id = p_organization_id
2417: AND msn.current_status = 3
2418: AND msn.current_subinventory_code = p_subinv_code
2452: , lot_number
2453: , 0
2454: , current_status
2455: , mms.status_code
2456: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2457: WHERE inventory_item_id = p_item_id
2458: AND (group_mark_id IS NULL
2459: OR group_mark_id = -1
2460: )
2507: , msn.lot_number
2508: , 0
2509: , msn.current_status
2510: , mms.status_code
2511: FROM wms_allocations_gtmp mag, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2512: WHERE msn.inventory_item_id = p_item_id
2513: AND msn.current_organization_id = p_organization_id
2514: AND msn.current_status = 3
2515: AND msn.current_subinventory_code = p_subinv_code
2533: , msn.lot_number
2534: , 0
2535: , msn.current_status
2536: , mms.status_code
2537: FROM wms_allocations_gtmp mag, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2538: WHERE msn.inventory_item_id = p_item_id
2539: AND msn.current_organization_id = p_organization_id
2540: AND msn.current_status = 3
2541: AND msn.current_subinventory_code = p_subinv_code
2565: 'NULL'
2566: , 0
2567: , --current_status,
2568: 'NULL'
2569: FROM mtl_serial_numbers
2570: WHERE current_organization_id = p_organization_id
2571: AND serial_number LIKE (p_serial)
2572: ORDER BY LPAD(serial_number, 20);
2573: END get_all_serial_lov;
2590: 'NULL'
2591: , 0
2592: , --current_status,
2593: 'NULL'
2594: FROM mtl_serial_numbers
2595: WHERE current_organization_id = p_organization_id
2596: AND inventory_item_id = p_inventory_item_id
2597: AND LENGTH(serial_number) = LENGTH(p_from_serial_number)
2598: AND serial_number LIKE (l_prefix || '%')
2610: dummy_e NUMBER;
2611: dummy_f VARCHAR2(20);
2612: BEGIN
2613: IF (p_upd_group_id = 1) THEN
2614: UPDATE mtl_serial_numbers
2615: SET group_mark_id = NULL
2616: WHERE current_organization_id = p_org_id
2617: AND group_mark_id IS NOT NULL
2618: AND lpn_id = p_lpn_id
2636: 'NULL'
2637: , 0
2638: , --current_status,
2639: 'NULL'
2640: FROM mtl_serial_numbers
2641: WHERE current_organization_id = p_org_id
2642: AND (group_mark_id IS NULL
2643: OR group_mark_id = -1
2644: )
2668: , 0
2669: , 0
2670: , ''
2671: , ''
2672: FROM mtl_serial_numbers_temp
2673: WHERE transaction_temp_id = p_temp_id
2674: ORDER BY LPAD(fm_serial_number, 20);
2675: ELSE
2676: OPEN x_serial_number FOR
2680: , 0
2681: , 0
2682: , ''
2683: , ''
2684: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
2685: WHERE mtlt.transaction_temp_id = p_temp_id
2686: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
2687: ORDER BY LPAD(fm_serial_number, 20);
2688: END IF;
2716: inv_project.get_project_id
2717: , inv_project.get_project_number
2718: , inv_project.get_task_id
2719: , inv_project.get_task_number
2720: FROM mtl_serial_numbers msn
2721: , mtl_system_items_vl msiv
2722: , mtl_item_locations_kfv milv
2723: , mtl_secondary_inventories msi
2724: WHERE msn.current_organization_id = p_current_organization_id
2733: WHERE mr.reservation_id = msn.reservation_id
2734: AND NVL(mr.staged_flag, 'N') = 'Y')
2735: AND NOT EXISTS (
2736: SELECT 1
2737: FROM mtl_serial_numbers_temp msnt
2738: WHERE msn.serial_number BETWEEN msnt.fm_serial_number
2739: AND msnt.to_serial_number)
2740: )
2741:
2778: , current_status
2779: , mms.status_code
2780: , ''
2781: , msn.revision
2782: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2783: WHERE inventory_item_id = p_item_id
2784: AND (group_mark_id IS NULL
2785: OR group_mark_id = -1
2786: OR group_mark_id IN (SELECT transaction_temp_id
2835: , lot_number
2836: , ''
2837: , current_status
2838: , mms.status_code
2839: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2840: WHERE current_organization_id = p_organization_id
2841: AND inventory_item_id = p_item_id
2842: AND msn.status_id = mms.status_id(+)
2843: AND mms.language (+) = userenv('LANG')
2888: , lot_number
2889: , ''
2890: , current_status
2891: , mms.status_code
2892: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2893: WHERE current_organization_id = p_organization_id
2894: AND inventory_item_id = p_item_id
2895: AND current_status = 3
2896: AND msn.status_id = mms.status_id(+)
2954: , lot_number
2955: , ''
2956: , current_status
2957: , mms.status_code
2958: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2959: WHERE current_organization_id = p_organization_id
2960: AND inventory_item_id = p_item_id
2961: AND msn.status_id = mms.status_id(+)
2962: AND mms.language (+) = userenv('LANG')
3018: , lot_number
3019: , 0
3020: , current_status
3021: , mms.status_code
3022: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3023: WHERE inventory_item_id = p_item_id
3024: AND (group_mark_id IS NULL
3025: OR group_mark_id = -1
3026: )
3063: , lot_number
3064: , 0
3065: , current_status
3066: , mms.status_code
3067: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3068: WHERE inventory_item_id = p_item_id
3069: AND (group_mark_id IS NULL
3070: OR group_mark_id = -1
3071: )
3127: , lot_number
3128: , 0
3129: , current_status
3130: , mms.status_code
3131: from mtl_serial_numbers msn, mtl_material_statuses_tl mms
3132: where inventory_item_id = l_wip_assembly_id
3133: and msn.status_id = mms.status_id(+)
3134: AND mms.language (+) = userenv('LANG')
3135: AND CURRENT_organization_id = p_organization_id
3558: , x_n_attribute7
3559: , x_n_attribute8
3560: , x_n_attribute9
3561: , x_n_attribute10
3562: FROM mtl_serial_numbers
3563: WHERE inventory_item_id = p_inventory_item_id
3564: AND serial_number = p_serial_number;
3565: EXCEPTION
3566: WHEN OTHERS THEN
3585: , lot_number
3586: , 0
3587: , current_status
3588: , mms.status_code
3589: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3590: WHERE lpn_id = p_lpn_id
3591: AND current_organization_id = p_organization_id
3592: AND inventory_item_id = p_item_id
3593: AND NVL(lot_number, 'NOLOT') = NVL(p_lot_number, 'NOLOT')
3629: , lot_number
3630: , 0
3631: , current_status
3632: , mms.status_code
3633: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3634: WHERE lpn_id = p_lpn_id
3635: AND current_organization_id = p_organization_id
3636: AND inventory_item_id = p_item_id
3637: AND (group_mark_id IS NULL
3689: , 0
3690: , 0
3691: , ''
3692: , ''
3693: FROM mtl_serial_numbers msn
3694: , rcv_serials_supply rss
3695: , rcv_supply rs
3696: WHERE msn.lpn_id = p_lpn_id
3697: AND msn.inventory_item_id = p_inventory_item_id
3844: , lot_number
3845: , 0
3846: , current_status
3847: , mms.status_code
3848: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3849: WHERE msn.inventory_item_id = p_item_id
3850: AND (group_mark_id IS NULL
3851: OR group_mark_id = -1
3852: )
3873: , lot_number
3874: , 0
3875: , current_status
3876: , mms.status_code
3877: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3878: WHERE inventory_item_id = p_item_id
3879: AND (group_mark_id IS NULL
3880: OR group_mark_id = -1
3881: )
3924: , lot_number
3925: , 0
3926: , current_status
3927: , mms.status_code
3928: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3929: WHERE inventory_item_id = p_item_id
3930: AND (group_mark_id IS NULL
3931: OR group_mark_id = -1
3932: )
3949: , current_status
3950: , mms.status_code
3951: FROM rcv_serials_supply rss
3952: , rcv_shipment_lines rsl
3953: , mtl_serial_numbers msn
3954: , mtl_material_statuses_tl mms
3955: WHERE rss.shipment_line_id(+) = rsl.shipment_line_id
3956: AND nvl(rss.supply_type_code, 'SHIPMENT') = 'SHIPMENT'
3957: AND (msn.group_mark_id IS NULL OR msn.group_mark_id = -1)
4091: , lot_number
4092: , 0
4093: , current_status
4094: , status_code
4095: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4096: WHERE current_organization_id = p_organization_id
4097: AND inventory_item_id = p_inventory_item_id
4098: --AND current_status IN (1, 3, 5)
4099: AND current_status IN (1, 3, 5, 7)
4120: , lot_number
4121: , 0
4122: , current_status
4123: , mms.status_code
4124: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4125: WHERE current_organization_id = p_organization_id
4126: AND inventory_item_id = p_inventory_item_id
4127: --AND current_status IN (1, 3, 5)
4128: AND current_status IN (1, 3, 5, 7)
4159: , lot_number
4160: , 0
4161: , current_status
4162: , status_code
4163: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4164: WHERE current_organization_id = p_organization_id
4165: AND inventory_item_id = p_inventory_item_id
4166: AND (p_lot_number IS NULL
4167: OR lot_number = p_lot_number)
4190: , lot_number
4191: , 0
4192: , current_status
4193: , mms.status_code
4194: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4195: WHERE current_organization_id = p_organization_id
4196: AND inventory_item_id = p_inventory_item_id
4197: --AND current_status IN (1, 3, 5)
4198: AND current_status IN (1, 3, 5, 7)