[Home] [Help]
141: ELSE
142: -- Get the current sub where the serial resides according to the system
143: SELECT NVL(current_subinventory_code, '@@@@@')
144: INTO l_serial_sub
145: FROM mtl_serial_numbers
146: WHERE inventory_item_id = p_inventory_item_id
147: AND serial_number = p_serial_number
148: AND current_organization_id = p_organization_id;
149: IF (l_debug = 1) THEN
1688: WHERE parent_lpn_id = l_current_lpn
1689: AND NVL(serial_summary_entry, 2) = 2;
1690: CURSOR lpn_serial_contents_cursor IS
1691: SELECT *
1692: FROM MTL_SERIAL_NUMBERS
1693: WHERE lpn_id = l_current_lpn;
1694:
1695: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1696: BEGIN
1827: END IF;
1828: x_status := -1;
1829: ELSE
1830:
1831: UPDATE mtl_serial_numbers
1832: SET group_mark_id = -1
1833: WHERE inventory_item_id = p_item_id
1834: AND serial_number in
1835: (SELECT DISTINCT serial_num
1841: )
1842: AND nvl(group_mark_id,-1) <> -1;
1843:
1844: IF (l_debug = 1) THEN
1845: print_debug('Updated ' || SQL%ROWCOUNT || ' Records in mtl_serial_numbers for the inventory_item_id ' || p_item_id);
1846: print_debug('*** end unmark_serials***');
1847: END IF;
1848:
1849: x_status := 0;
1902: /*Bug7829724-Commeneted locator*/
1903: BEGIN
1904: SELECT 1,MP.ORGANIZATION_CODE,ML.MEANING
1905: INTO l_valid_serial,x_organization_code,x_current_status
1906: FROM MTL_SERIAL_NUMBERS MSN , MTL_PARAMETERS MP, MFG_LOOKUPS ML
1907: WHERE SERIAL_NUMBER like p_serial_num
1908: AND MSN.INVENTORY_ITEM_ID = p_inventory_item_id
1909: AND MSN.CURRENT_ORGANIZATION_ID = p_organization_id
1910: AND MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
1938: * serial should not exist anywhere
1939: */
1940: SELECT MP.ORGANIZATION_CODE,ML.MEANING
1941: INTO x_organization_code,x_current_status
1942: FROM MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
1943: WHERE SERIAL_NUMBER like p_serial_num
1944: AND MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
1945: AND MSN.CURRENT_STATUS = ML.LOOKUP_CODE
1946: AND ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
1951: * serial should not exist in same org, same item
1952: */
1953: SELECT MP.ORGANIZATION_CODE,ML.MEANING
1954: INTO x_organization_code,x_current_status
1955: FROM MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
1956: WHERE MSN.SERIAL_NUMBER like p_serial_num
1957: AND MSN.INVENTORY_ITEM_ID = p_inventory_item_id
1958: AND MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
1959: AND MSN.CURRENT_STATUS = ML.LOOKUP_CODE
1970: BEGIN
1971:
1972: SELECT MP.ORGANIZATION_CODE,ML.MEANING
1973: INTO x_organization_code,x_current_status
1974: FROM MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
1975: WHERE MSN.SERIAL_NUMBER like p_serial_num
1976: AND MSN.INVENTORY_ITEM_ID = p_inventory_item_id
1977: AND MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
1978: AND MSN.CURRENT_STATUS = ML.LOOKUP_CODE
1990: * serial should not exist in same org
1991: */
1992: SELECT MP.ORGANIZATION_CODE,ML.MEANING
1993: INTO x_organization_code,x_current_status
1994: FROM MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
1995: WHERE MSN.SERIAL_NUMBER like p_serial_num
1996: AND MSN.CURRENT_ORGANIZATION_ID = p_organization_id
1997: AND MSN.CURRENT_ORGANIZATION_ID = MP. ORGANIZATION_ID
1998: AND MSN.CURRENT_STATUS = ML.LOOKUP_CODE
2260: WHERE organization_id = p_org_id
2261: AND physical_inventory_id = p_phy_inv_id AND void_flag = 2)
2262:
2263: ) wms,
2264: mtl_serial_numbers msn,
2265: mtl_lot_numbers mln
2266: WHERE wms.organization_id = msn.current_organization_id (+)
2267: AND wms.inventory_item_id = msn.inventory_item_id (+)
2268: AND wms.subinventory_name = msn.current_subinventory_code(+)