CREATE TABLE #RSNOTA (CD_EMP INT, CD_FILIAL INT, CD_NF INT)

INSERT INTO #RSNOTA
SELECT
	CD_EMP,
	CD_FILIAL,
	CD_NF
FROM
	EST_NF_SAI_NFE_CANCEL
GROUP BY
	CD_EMP,
	CD_FILIAL,
	CD_NF 
HAVING 
	COUNT(CD_NF) >1

DELETE 
	EST_NF_SAI_NFE_CANCEL
FROM
	EST_NF_SAI_NFE_CANCEL INNER JOIN #RSNOTA N ON
	EST_NF_SAI_NFE_CANCEL.CD_EMP = N.CD_EMP AND
	EST_NF_SAI_NFE_CANCEL.CD_FILIAL = N.CD_FILIAL AND
	EST_NF_SAI_NFE_CANCEL.CD_NF = N.CD_NF 

DROP TABLE #RSNOTA

IF EXISTS(SELECT 1 FROM SYS.OBJECTS WHERE NAME = 'PK_EST_NF_SAI_NFE_CANCEL')
	ALTER TABLE [DBO].[EST_NF_SAI_NFE_CANCEL] DROP CONSTRAINT [PK_EST_NF_SAI_NFE_CANCEL]
GO

IF EXISTS(SELECT 1 FROM SYS.OBJECTS WHERE NAME = 'PK_EST_NF_SAI_NFE_CANCEL_1')
	ALTER TABLE [DBO].[EST_NF_SAI_NFE_CANCEL] DROP CONSTRAINT [PK_EST_NF_SAI_NFE_CANCEL_1]
GO

ALTER TABLE DBO.EST_NF_SAI_NFE_CANCEL ADD CONSTRAINT
	PK_EST_NF_SAI_NFE_CANCEL PRIMARY KEY CLUSTERED 
	(
	CD_EMP,
	CD_FILIAL,
	CD_NF
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


IF NOT OBJECT_ID ('FK_EST_NF_SAI_NFE_CANCEL_EST_NF_SAI') IS NULL
	BEGIN
		ALTER TABLE DBO.EST_NF_SAI_NFE_CANCEL
			DROP CONSTRAINT FK_EST_NF_SAI_NFE_CANCEL_EST_NF_SAI
	END

GO

DELETE
	EST_NF_SAI_NFE_CANCEL
FROM
	EST_NF_SAI_NFE_CANCEL LEFT OUTER JOIN EST_NF_SAI ON
	EST_NF_SAI_NFE_CANCEL.CD_EMP =EST_NF_SAI.CD_EMP AND
	EST_NF_SAI_NFE_CANCEL.CD_FILIAL = EST_NF_SAI.CD_FILIAL AND
	EST_NF_SAI_NFE_CANCEL.CD_NF = EST_NF_SAI.CD_NF 
WHERE
	EST_NF_SAI.CD_NF IS NULL

ALTER TABLE DBO.EST_NF_SAI_NFE_CANCEL ADD CONSTRAINT
	FK_EST_NF_SAI_NFE_CANCEL_EST_NF_SAI FOREIGN KEY
	(
	CD_EMP,
	CD_FILIAL,
	CD_NF
	) REFERENCES DBO.EST_NF_SAI
	(
	CD_EMP,
	CD_FILIAL,
	CD_NF
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 

UPDATE
	EST_NF_SAI
SET 
	EST_NF_SAI.STS_NF =0
FROM
	EST_NF_SAI_CANCEL INNER JOIN EST_NF_SAI ON
	EST_NF_SAI_CANCEL.CD_EMP = EST_NF_SAI.CD_EMP AND
	EST_NF_SAI_CANCEL.CD_FILIAL = EST_NF_SAI.CD_FILIAL AND
	EST_NF_SAI_CANCEL.CD_NF = EST_NF_SAI.CD_NF 
WHERE
	EST_NF_SAI_CANCEL.CD_EMP =1
	AND EST_NF_SAI_CANCEL.CANCEL_FORA_PRAZO=1
	AND EST_NF_SAI.STS_NF = 1