IF EXISTS(SELECT * FROM SYS.TRIGGERS WHERE OBJECT_ID=OBJECT_ID(N'[DBO].[TRG_EST_NF_SAI_CANCEL_ULT_MOV]') )
	DROP TRIGGER [DBO].[TRG_EST_NF_SAI_CANCEL_ULT_MOV];
GO

CREATE TRIGGER [DBO].[TRG_EST_NF_SAI_CANCEL_ULT_MOV] ON [DBO].[EST_NF_SAI_CANCEL]
FOR INSERT, UPDATE, DELETE
AS 
	
	DECLARE @CD_CTR_MOVIMENTO INT
	DECLARE @RSPROD TABLE (CD_EMP INT,
						   CD_FILIAL INT,
						   CD_CTR_DT_MOVIMENTO INT,
						   CD_PROD INT)

	INSERT INTO @RSPROD
	SELECT
		INSERTED.CD_EMP,
		INSERTED.CD_FILIAL,
		GLB_DT_MOVIMENTO.CD_CTR_DT_MOVIMENTO,
		IT.CD_PROD
	FROM
		INSERTED 
		INNER JOIN EST_NF_SAI SAI ON SAI.CD_NF = INSERTED.CD_NF
									AND SAI.CD_FILIAL = INSERTED.CD_FILIAL
									AND SAI.CD_EMP = INSERTED.CD_EMP
		INNER JOIN EST_NF_SAI_IT IT ON IT.CD_NF = SAI.CD_NF
										AND IT.CD_FILIAL = SAI.CD_FILIAL
										AND IT.CD_EMP = SAI.CD_EMP
		INNER JOIN GLB_DT_MOVIMENTO ON CONVERT(DATE,SAI.DT_EMI_NF)   = CONVERT(DATE,GLB_DT_MOVIMENTO.DT_MOVIMENTO)

	IF EXISTS (SELECT 1 FROM @RSPROD) 
	BEGIN
	 SET @CD_CTR_MOVIMENTO=ISNULL((SELECT TOP 1 CD_CTR_DT_MOVIMENTO FROM @RSPROD),0)
	 
	 IF NOT EXISTS( SELECT  
							1 CD_EMP,
							F.CD_FILIAL,
							R.CD_PROD
					   FROM EST_PROD_CPL_VD_LOTE_PROC_PENDENTE P
						    INNER JOIN EST_PROD_CPL_VD_LOTE_PROC_PRC_FILIAL F ON P.CD_CTR = F.CD_CTR 
						    INNER JOIN EST_PROD_CPL_VD_LOTE_PROC_EST_PROD R ON P.CD_CTR = R.CD_CTR			
						    INNER JOIN @RSPROD RS ON F.CD_EMP    = RS.CD_EMP 
											     AND F.CD_FILIAL = RS.CD_FILIAL 
											     AND R.CD_PROD   = RS.CD_PROD 
											     AND P.CD_CTR_DT_MOVIMENTO = RS.CD_CTR_DT_MOVIMENTO
						) 
	 BEGIN
		DECLARE @CD_CTR INT					
		SET @CD_CTR=ISNULL((SELECT TOP 1 CD_CTR 
						      FROM EST_PROD_CPL_VD_LOTE_PROC_PENDENTE
						     WHERE CD_CTR_DT_MOVIMENTO  =@CD_CTR_MOVIMENTO),0)				
		IF @CD_CTR=0 
		BEGIN
			 INSERT INTO EST_PROD_CPL_VD_LOTE_PROC_PENDENTE (CD_CTR_DT_MOVIMENTO,DT_CAD,ANO,MES,ANO_MES,ROTINA)
			 SELECT DISTINCT GLB_DT_MOVIMENTO.CD_CTR_DT_MOVIMENTO,
					GETDATE(),
					GLB_DT_MOVIMENTO.ANO_MOVIMENTO,
					GLB_DT_MOVIMENTO.MES_MOVIMENTO,
					GLB_DT_MOVIMENTO.ANO_MES_MOVIMENTO,
					OBJECT_NAME(@@PROCID)
			   FROM GLB_DT_MOVIMENTO  
			  WHERE GLB_DT_MOVIMENTO.CD_CTR_DT_MOVIMENTO = @CD_CTR_MOVIMENTO
			  
			SET @CD_CTR=IDENT_CURRENT('EST_PROD_CPL_VD_LOTE_PROC_PENDENTE')
			
		END  
		
		INSERT INTO EST_PROD_CPL_VD_LOTE_PROC_PRC_FILIAL (CD_CTR, CD_EMP, CD_FILIAL )
		SELECT 
			   DISTINCT @CD_CTR,
			   I.CD_EMP,
			   I.CD_FILIAL
		  FROM INSERTED I 
			   LEFT OUTER JOIN EST_PROD_CPL_VD_LOTE_PROC_PRC_FILIAL ON I.CD_EMP    = EST_PROD_CPL_VD_LOTE_PROC_PRC_FILIAL.CD_EMP 
																   AND I.CD_FILIAL = EST_PROD_CPL_VD_LOTE_PROC_PRC_FILIAL.CD_FILIAL 
																   AND @CD_CTR     = EST_PROD_CPL_VD_LOTE_PROC_PRC_FILIAL.CD_CTR
		 WHERE EST_PROD_CPL_VD_LOTE_PROC_PRC_FILIAL.CD_FILIAL IS NULL
		
		INSERT INTO EST_PROD_CPL_VD_LOTE_PROC_EST_PROD (CD_CTR,CD_EMP,CD_PROD)
		SELECT 
			   DISTINCT @CD_CTR,
			   I.CD_EMP,
			   I.CD_PROD
		  FROM @RSPROD I	
			   LEFT OUTER JOIN EST_PROD_CPL_VD_LOTE_PROC_EST_PROD ON I.CD_EMP  = EST_PROD_CPL_VD_LOTE_PROC_EST_PROD.CD_EMP 
																 AND I.CD_PROD = EST_PROD_CPL_VD_LOTE_PROC_EST_PROD.CD_PROD  
																 AND @CD_CTR   = EST_PROD_CPL_VD_LOTE_PROC_EST_PROD.CD_CTR
		 WHERE EST_PROD_CPL_VD_LOTE_PROC_EST_PROD.CD_PROD IS NULL
	END;
END;

