[Home] [Help]
1626: SELECT p_inventory_item_id
1627: , p_organization_id
1628: , msn.serial_number
1629: , null
1630: FROM mtl_serial_numbers msn, inv_msn_gtemp img
1631: WHERE msn.inventory_item_id = p_inventory_item_id
1632: AND msn.current_organization_id = p_organization_id
1633: AND nvl(msn.revision,'@@@') = nvl(p_revision,'@@@')
1634: AND nvl(msn.lot_number, '@@@') = nvl(p_lot_number,'@@@')
1657: , img.organization_id
1658: , img.serial_number
1659: --, null dont forget
1660: , msn.status_id
1661: FROM inv_msn_gtemp img, mtl_serial_numbers msn
1662: WHERE img.use_flag = 0
1663: AND msn.serial_number = img.serial_number
1664: AND msn.inventory_item_id = img.inventory_item_id
1665: AND msn.current_organization_id = img.organization_id
1738:
1739: --Bulk insert the return values from user into temp table for validation
1740: BEGIN
1741: FORALL i IN l_custom_select_serials.serial_number.first..l_custom_select_serials.serial_number.last
1742: INSERT INTO inv_msn_gtemp (
1743: inventory_item_id
1744: , organization_id
1745: , serial_number
1746: , use_flag )
1768: , l_selected_serials.serial_number
1769: , l_selected_serials.serial_status;
1770: CLOSE validate_sn_cur;
1771:
1772: --Delete any serial numbers not being used in inv_msn_gtemp
1773: DELETE FROM inv_msn_gtemp
1774: WHERE use_flag = 0;
1775: ELSE
1776: --bug 1348067 - causing serial numbers to detail twice
1769: , l_selected_serials.serial_status;
1770: CLOSE validate_sn_cur;
1771:
1772: --Delete any serial numbers not being used in inv_msn_gtemp
1773: DELETE FROM inv_msn_gtemp
1774: WHERE use_flag = 0;
1775: ELSE
1776: --bug 1348067 - causing serial numbers to detail twice
1777: -- we should initialize ptr once per detailing, not once per function call
1797: , p_organization_id
1798: , p_inventory_item_id
1799: , l_selected_serials.serial_status(i) ) = 'Y' ) THEN
1800: BEGIN
1801: INSERT INTO inv_msn_gtemp (
1802: serial_identifier
1803: , INVENTORY_ITEM_ID
1804: , organization_id
1805: , Serial_number