[Home] [Help]
1759: SELECT p_inventory_item_id
1760: , p_organization_id
1761: , msn.serial_number
1762: , null
1763: FROM mtl_serial_numbers msn, inv_msn_gtemp img
1764: WHERE msn.inventory_item_id = p_inventory_item_id
1765: AND msn.current_organization_id = p_organization_id
1766: AND nvl(msn.revision,'@@@') = nvl(p_revision,'@@@')
1767: AND nvl(msn.lot_number, '@@@') = nvl(p_lot_number,'@@@')
1790: , img.organization_id
1791: , img.serial_number
1792: --, null dont forget
1793: , msn.status_id
1794: FROM inv_msn_gtemp img, mtl_serial_numbers msn
1795: WHERE img.use_flag = 0
1796: AND msn.serial_number = img.serial_number
1797: AND msn.inventory_item_id = img.inventory_item_id
1798: AND msn.current_organization_id = img.organization_id
1871:
1872: --Bulk insert the return values from user into temp table for validation
1873: BEGIN
1874: FORALL i IN l_custom_select_serials.serial_number.first..l_custom_select_serials.serial_number.last
1875: INSERT INTO inv_msn_gtemp (
1876: inventory_item_id
1877: , organization_id
1878: , serial_number
1879: , use_flag )
1901: , l_selected_serials.serial_number
1902: , l_selected_serials.serial_status;
1903: CLOSE validate_sn_cur;
1904:
1905: --Delete any serial numbers not being used in inv_msn_gtemp
1906: DELETE FROM inv_msn_gtemp
1907: WHERE use_flag = 0;
1908: ELSE
1909: --bug 1348067 - causing serial numbers to detail twice
1902: , l_selected_serials.serial_status;
1903: CLOSE validate_sn_cur;
1904:
1905: --Delete any serial numbers not being used in inv_msn_gtemp
1906: DELETE FROM inv_msn_gtemp
1907: WHERE use_flag = 0;
1908: ELSE
1909: --bug 1348067 - causing serial numbers to detail twice
1910: -- we should initialize ptr once per detailing, not once per function call
1930: , p_organization_id
1931: , p_inventory_item_id
1932: , l_selected_serials.serial_status(i) ) = 'Y' ) THEN
1933: BEGIN
1934: INSERT INTO inv_msn_gtemp (
1935: serial_identifier
1936: , INVENTORY_ITEM_ID
1937: , organization_id
1938: , Serial_number