IF OBJECT_ID('dbo.P_PDV_VD_NFCE_STS_VALID_ATUALIZA') IS NULL
  EXEC ('CREATE PROCEDURE dbo.P_PDV_VD_NFCE_STS_VALID_ATUALIZA AS RETURN 0;') 
 
GO
ALTER PROCEDURE  P_PDV_VD_NFCE_STS_VALID_ATUALIZA
AS
set nocount on;
	update c
	SET
		STS_VALID = 2
	FROM
		PDV_VD_NFCE C
	WHERE  
		C.CD_EMP = 1
		AND NOT STS_VALID = 2
		AND LEN(COALESCE(C.NR_PROT, '')) < 4
		AND C.DS_MOTIVO LIKE '%CONTIGENCIA%';

		
	UPDATE                                                     
	PDV_VD_NFCE                                            
	SET STS_VALID  =3                                          
	WHERE                                                      
	STS_VALID <> 1                                           
	AND STS_VALID <> 3                                       
	AND LEN(NR_PROT) < 4                                     
	AND  DS_MOTIVO NOT LIKE '%CONTIGENCIA%'                

                                                                  
	UPDATE                                                     
	PDV_VD_NFCE                                             
	SET STS_VALID  =2                                          
	WHERE                                                      
	STS_VALID <> 2                                           
	AND STS_VALID <> 3                                       
	AND LEN(NR_PROT) < 4                                     
	AND  DS_MOTIVO  LIKE '%CONTIGENCIA%'                   

	UPDATE                                                     
	PDV_VD_NFCE                                             
	SET STS_VALID  =1                                          
	WHERE                                                      
	STS_VALID <> 1                                           
	AND LEN(NR_PROT) > 4
		 
		
/*
--Sugerido para melhorar a performance

CREATE NONCLUSTERED INDEX IX_PDV_VD_NFCE_STS_VALID ON PDV_VD_NFCE 
	(STS_VALID)  INCLUDE (CH_NFE,NR_PROT,DS_MOTIVO)
	WITH (FILLFACTOR=90, DATA_COMPRESSION=PAGE) 

*/