IF EXISTS( SELECT 1 FROM SYS.OBJECTS WHERE NAME ='TRG_EST_NF_ENT_TRANSF_EST_WMS_ENT'AND TYPE = 'TR')
BEGIN
	DROP TRIGGER dbo.TRG_EST_NF_ENT_TRANSF_EST_WMS_ENT
END
GO

CREATE TRIGGER TRG_EST_NF_ENT_TRANSF_EST_WMS_ENT ON [dbo].[EST_NF_SAI_PENDENCIA] FOR DELETE AS
BEGIN		
		DECLARE @FLAG_WMS INT		
		SET @FLAG_WMS = ISNULL(( SELECT TOP  1 d.CD_EMP
							 FROM 
								DELETED d 
								INNER JOIN PRC_FILIAL_DEPOSITO H ON 
								D.CD_EMP		 = H.CD_EMP AND
								D.CD_FILIAL_DEST = H.CD_FILIAL),0)		
		IF  OBJECT_ID('TEMPDB.DBO.#TMP_WMS') IS NOT NULL
		BEGIN
			 DROP TABLE #TMP_WMS
		END
		CREATE TABLE #TMP_WMS(DT_PED DATETIME, 
							CD_USU INT, 
							DT_CAD DATETIME, 
							DT_ULT_ALT DATETIME, 
							STS_PED INT, 
							CD_DEST_ORIG INT, 
							NM_DEST_ORIG VARCHAR(400))								
		IF @FLAG_WMS = 0 
		BEGIN
			RETURN;
		END

		IF EXISTS (
			SELECT 
				* 
			FROM 
				EST_NF_ENT_EST_WMS_ENT 
				INNER JOIN EST_NF_ENT_EST_NF_SAI_TRANSF ON 
					EST_NF_ENT_EST_WMS_ENT.CD_EMP = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP AND 
					EST_NF_ENT_EST_WMS_ENT.CD_FILIAL = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_DEST AND 
					EST_NF_ENT_EST_WMS_ENT.CD_NF = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_ENT 
				INNER JOIN DELETED ON 
					EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP = DELETED.CD_EMP AND 
					EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_ORIG = DELETED.CD_FILIAL AND 
					EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_SAI = DELETED.CD_NF)
		BEGIN
			RETURN;
		END

		-------------------------------------------------------------------------------
		----------------------Gravando o cabealho da integrao-----------------------
		-------------------------------------------------------------------------------
		INSERT INTO #TMP_WMS (DT_PED, CD_USU, DT_CAD, DT_ULT_ALT, STS_PED, CD_DEST_ORIG, NM_DEST_ORIG)
		SELECT     
			CONVERT(CHAR(10),DT_REGISTRO,112),
			EST_NF_ENT.CD_USU,
			GETDATE(),
			NULL,
			0,
			--Proviniente de entrada de transferencias
			CAST ('2' + RIGHT('00000000' + CAST(PG_FORN.CD_FORN AS VARCHAR), 8) AS NUMERIC) AS CD_FORN,
			PG_FORN.RZ_FORN					
		FROM         
			EST_NF_ENT 
			INNER JOIN EST_NF_ENT_EST_NF_SAI_TRANSF ON 
			EST_NF_ENT.CD_EMP = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP AND 
            EST_NF_ENT.CD_FILIAL = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_DEST AND 
            EST_NF_ENT.CD_NF = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_ENT 
			INNER JOIN EST_NF_SAI ON 
			EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP = EST_NF_SAI.CD_EMP AND 
            EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_ORIG = EST_NF_SAI.CD_FILIAL AND 
            EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_SAI = EST_NF_SAI.CD_NF 
            INNER JOIN GLB_MOV_OP O ON  
            EST_NF_ENT.CD_NT_MOV = O.CD_MOV_OP   
            INNER JOIN PRC_FILIAL_PG_FORN   
            INNER JOIN PG_FORN ON 
            PRC_FILIAL_PG_FORN.CD_EMP = PG_FORN.CD_EMP AND 
            PRC_FILIAL_PG_FORN.CD_FORN = PG_FORN.CD_FORN
            INNER JOIN DELETED ON 
            PRC_FILIAL_PG_FORN.CD_EMP = DELETED.CD_EMP AND 
            PRC_FILIAL_PG_FORN.CD_FILIAL = DELETED.CD_FILIAL ON 
            EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP = DELETED.CD_EMP AND 
            EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_ORIG = DELETED.CD_FILIAL AND 
            EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_SAI = DELETED.CD_NF
		WHERE
			o.TP_ESTQ= 0
			AND O.ATU_EST =1
			AND EST_NF_SAI.FLAG_NOTA_INTEGRACAO <> 1
		IF NOT EXISTS (SELECT * FROM #TMP_WMS)
		BEGIN				
			RETURN;
		END

		INSERT INTO EST_WMS_ENT (DT_PED, CD_USU, DT_CAD, DT_ULT_ALT, STS_PED, CD_DEST_ORIG, NM_DEST_ORIG)
		select * from #TMP_WMS
		-------------------------------------------------------------------------------
		--Gravando os itens da integrao
		-------------------------------------------------------------------------------
		INSERT INTO EST_WMS_ENT_IT (CD_PED_WMS, CD_PROD, LOTE, QT_PROD, CD_EMP)
		SELECT
			IDENT_CURRENT('EST_WMS_ENT'),     
			EST_NF_SAI_IT.CD_PROD, 
			CASE WHEN EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_LOTE IS NULL THEN
				'ND'
			ELSE
				CONVERT(VARCHAR(1000),EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_LOTE)
			END AS LOTE, 
			SUM((CASE WHEN EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_LOTE IS NULL THEN EST_NF_SAI_IT.QT_IT ELSE EST_NF_SAI_IT_EST_PROD_CPL_LOTE.QTDE_PROD END)),
			EST_NF_SAI_IT.CD_EMP
		FROM         
			EST_NF_SAI_IT 
			INNER JOIN DELETED ON 
			EST_NF_SAI_IT.CD_EMP	= DELETED.CD_EMP AND 
            EST_NF_SAI_IT.CD_FILIAL = DELETED.CD_FILIAL AND 
            EST_NF_SAI_IT.CD_NF		= DELETED.CD_NF 
            LEFT OUTER JOIN EST_NF_SAI_IT_EST_PROD_CPL_LOTE ON 
            EST_NF_SAI_IT.CD_EMP = EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_EMP AND 
            EST_NF_SAI_IT.CD_FILIAL = EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_FILIAL AND 
            EST_NF_SAI_IT.CD_NF = EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_NF AND 
            EST_NF_SAI_IT.CD_PROD = EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_PROD AND 
            EST_NF_SAI_IT.CD_IT = EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_IT
		GROUP BY
			EST_NF_SAI_IT.CD_EMP,
			EST_NF_SAI_IT.CD_PROD,
			EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_LOTE
		-------------------------------------------------------------------------------
		--------------Gravando os itens da integrao----------------------------------
		-------------------------------------------------------------------------------
		INSERT INTO EST_NF_ENT_EST_WMS_ENT (CD_EMP, CD_FILIAL, CD_NF, CD_PED_WMS)
		SELECT     
			EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP,
			EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_DEST,
			EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_ENT,
			IDENT_CURRENT('EST_WMS_ENT')
		FROM         
			EST_NF_ENT_EST_NF_SAI_TRANSF 
			INNER JOIN DELETED ON 
            EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP = DELETED.CD_EMP AND 
            EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_ORIG = DELETED.CD_FILIAL AND 
            EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_SAI = DELETED.CD_NF
		IF  OBJECT_ID('TEMPDB.DBO.#TMP_WMS') IS NOT NULL
		BEGIN
			 DROP TABLE #TMP_WMS
		END
END	

GO

ALTER TABLE [dbo].[EST_NF_SAI_PENDENCIA] ENABLE TRIGGER [TRG_EST_NF_ENT_TRANSF_EST_WMS_ENT]
GO


