-- ==========================================================
-- Create Stored Procedure sp_gps_device_configuration_deployment_sel
-- ==========================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Elysee Dagan
-- Create date: 31-08-2012
-- =============================================
IF OBJECT_ID('[dbo].[sp_gps_device_configuration_deployment_sel]') IS NOT NULL 
    BEGIN
        DROP PROCEDURE [dbo].[sp_gps_device_configuration_deployment_sel]
        IF OBJECT_ID('[dbo].[sp_gps_device_configuration_deployment_sel]') IS NOT NULL 
            PRINT '*** FAILED DROPPING PROCEDURE [dbo].[sp_gps_device_configuration_deployment_sel]***'
        ELSE 
            PRINT '*** DROPPED PROCEDURE [dbo].[sp_gps_device_configuration_deployment_sel]****'
    END
go
CREATE PROCEDURE [dbo].[sp_gps_device_configuration_deployment_sel]
    @imei VARCHAR(20) 
AS 
    BEGIN
    
        CREATE TABLE #IDs ( id INT )
      
        INSERT  INTO #IDs
                ( id 
                )
                SELECT TOP 10 d.id
                FROM    dbo.gps_device_configuration_deployment d
                        JOIN dbo.vehicle v ON d.vehicle_id = v.id
                        JOIN dbo.gps_device g ON g.id = v.gps_device_id
                WHERE   (g.imei = @imei OR RIGHT(g.imei,6) = @imei)
                        AND d.status = 0
                        
        UPDATE dbo.gps_device_configuration_deployment
        SET status = 1
        WHERE id IN ( SELECT id FROM #IDs)
        
        SELECT id, input FROM dbo.gps_device_configuration_deployment
        WHERE id IN ( SELECT id FROM #IDs)
        ORDER BY id ASC

    END
  
 GO
GRANT EXEC ON [dbo].[sp_gps_device_configuration_deployment_sel] TO process_role     
 GO

IF OBJECT_ID('[dbo].[sp_gps_device_configuration_deployment_sel]') IS NOT NULL 
    PRINT '*** CREATED PROCEDURE [dbo].[sp_gps_device_configuration_deployment_sel] ***'
ELSE 
    PRINT '*** FAILED CREATING PROCEDURE [dbo].[sp_gps_device_configuration_deployment_sel] ***'
go