IF EXISTS (SELECT * FROM sys.objects WHERE  object_id = OBJECT_ID(N'[dbo].[P_GERA_EST_WMS_SAI]') AND type IN(N'P', N'PC')
)
	DROP PROCEDURE [dbo].[P_GERA_EST_WMS_SAI];
GO
/*
=======================================================================================
'Nvel Classificao.: INFORMAO RESTRITA
'Verso..............: 20230927
'Job.................:  
'Nome................: [P_GERA_EST_WMS_SAI] 
'Objetivo............: 
'Tabelas.............:  
'Dt Criao..........: -
'Dt Ult Alteraco....: 2023-09-27
'Alterado por........: Jerfeson Barbosa
'Criado por..........: -
'=======================================================================================
FARMAGOI-34446
**Adicionado filtro (TRANSF.DT_TRANSF > getdate() - 30)
*/
CREATE   PROCEDURE  [dbo].[P_GERA_EST_WMS_SAI] 
AS
set nocount on;
SET XACT_ABORT ON; 
/**********************************************************************************************************/
	IF EXISTS(SELECT * FROM PRC_EMP_CONFIG WHERE CD_CHAVE ='BLOQUEAR_ENVIO_RESSUPRIMENTO_WMS'
							AND CD_EMP=1 AND VALOR='1')
	BEGIN
		Print 'Envio para WMS Bloqueado'
		RETURN;
	END
  	IF  OBJECT_ID('TEMPDB.DBO.#tmpPedWMS') IS NOT NULL
	BEGIN
		 DROP TABLE #tmpPedWMS
	END 
Create table #tmpPedWMS (CD_EMP	int
						,CD_FILIAL	int
						,CD_CTR		int
						,CD_FIL_DEST int
						,DT_TRANSF	datetime
						,CD_USU		int
						,CD_PROD	Int
						,QT_SOL		Money
						,VLR_IT		MONEY
						,NM_FANT    varchar	(160)
						,TP_TRANSF	int
						,CD_PED_WMS	INT)

/**********************************************************************************************************/
	INSERT INTO #tmpPedWMS 
	SELECT
		   TRANSF.CD_EMP
		 , TRANSF.CD_FILIAL
		 , TRANSF.CD_CTR
		 , TRANSF.CD_FIL_DEST
		 , TRANSF.DT_TRANSF
		 , TRANSF.CD_USU
		 , TRANSF_IT.CD_PROD
		 , TRANSF_IT.QT_SOL
		 , TRANSF_IT.VLR_CUSTO
		 , PRC_FILIAL.NM_FANT
		 , TRANSF.TP_TRANSF
		 , 0 CD_PED_WMS
	FROM
		 EST_PROD_TRANSF TRANSF
		 JOIN EST_PROD_TRANSF_PEND_WMS PEND ON TRANSF.CD_EMP = PEND.CD_EMP
													 AND TRANSF.CD_FILIAL = PEND.CD_FILIAL
													 AND TRANSF.CD_CTR = PEND.CD_CTR
		 JOIN EST_PROD_TRANSF_IT TRANSF_IT ON TRANSF.CD_EMP = TRANSF_IT.CD_EMP
													AND TRANSF.CD_FILIAL = TRANSF_IT.CD_FILIAL
													AND TRANSF.CD_CTR = TRANSF_IT.CD_CTR
		 INNER JOIN PRC_FILIAL ON TRANSF.CD_EMP = PRC_FILIAL.CD_EMP
								  AND TRANSF.CD_FIL_DEST = PRC_FILIAL.CD_FILIAL
	WHERE 
		  pend.PEND_ENVIO_WMS = 0
		  AND (TRANSF.DT_TRANSF > getdate() - 15);

CREATE NONCLUSTERED INDEX IX_#tmpPedWMS_Emp ON #tmpPedWMS (CD_EMP, CD_FILIAL, CD_CTR, CD_FIL_DEST, DT_TRANSF,CD_USU, NM_FANT)
CREATE NONCLUSTERED INDEX IX_#tmpPedWMS_CD_PED_WMS ON #tmpPedWMS (CD_PED_WMS, CD_FIL_DEST)
CREATE NONCLUSTERED INDEX IX_#tmpPedWMS_CD_EMP_FILIAL ON #tmpPedWMS (CD_EMP, CD_CTR, CD_FILIAL) INCLUDE (CD_PED_WMS)
CREATE NONCLUSTERED INDEX IX_#tmpPedWMS_CD_PED_WMS_CD_PROD ON #tmpPedWMS (CD_EMP, CD_CTR, CD_PED_WMS,CD_PROD, CD_FILIAL) INCLUDE (QT_SOL, VLR_IT)

/**********************************************************************************************************/
BEGIN TRY
	BEGIN TRANSACTION;
		INSERT INTO EST_WMS_SAI (DT_PED,CD_USU,DT_CAD,DT_ULT_ALT,STS_PED,CD_ROTA,CD_DEST_ORIG,NM_DEST_ORIG,CROSSDOCKING,CNPJ_DEST,CD_DEPO_ORIG)
		SELECT DISTINCT 
			   DT_TRANSF DT_PED
			 , CD_USU
			 , GETDATE () DT_CAD
			 , NULL DT_ULT_ALT
			 , 0 STS_PED
			 , t.CD_CTR CD_ROTA -- Aproveitando para facilitar o vinculo
			 , t.CD_FIL_DEST CD_DEST_ORIG
			 , T.NM_FANT NM_DEST_ORIG
			 , 0 CROSSDOCKING
			 , '' CNPJ_DEST
			 , T.CD_FILIAL
		FROM 
			#tmpPedWMS t
			LEFT JOIN EST_PROD_TRANSF_EST_WMS_SAI W on
			t.CD_EMP = W.CD_EMP AND
			t.CD_CTR = W.CD_CTR  AND
			t.CD_FILIAL = W.CD_FILIAL
		WHERE
			w.CD_EMP IS NULL;
		DECLARE @cd_FilialDep INT;
		DECLARE c_DepGeraWms 
		 CURSOR FAST_FORWARD FOR
		SELECT DISTINCT CD_FILIAL FROM #tmpPedWMS;
 		OPEN c_DepGeraWms
		FETCH NEXT FROM c_DepGeraWms INTO @cd_FilialDep
		WHILE @@FETCH_STATUS = 0
			BEGIN
				PRINT 'Procesando dep. ' + CONVERT(VARCHAR, @cd_FilialDep) 
				UPDATE T
				SET 
					CD_PED_WMS= S.CD_PED_WMS 
				FROM 
					#tmpPedWMS T
					JOIN EST_WMS_SAI S ON 
						T.CD_EMP = 1 AND 
						T.CD_FILIAL = S.CD_DEPO_ORIG  AND 
						T.CD_CTR	= S.CD_ROTA 
					LEFT JOIN EST_PROD_TRANSF_EST_WMS_SAI W on
						t.CD_EMP	= W.CD_EMP AND
						t.CD_CTR	= W.CD_CTR  AND
						t.CD_FILIAL = W.CD_FILIAL
				WHERE
					w.CD_EMP IS NULL
					AND S.CD_DEPO_ORIG=@cd_FilialDep;
				FETCH NEXT FROM c_DepGeraWms INTO @cd_FilialDep
			END
		CLOSE c_DepGeraWms;
		DEALLOCATE c_DepGeraWms; 
		UPDATE T
		SET 
			CD_PED_WMS= W.CD_PED_WMS 
		FROM 
			#tmpPedWMS T
			JOIN EST_PROD_TRANSF_EST_WMS_SAI W on
			t.CD_EMP	= W.CD_EMP AND
			t.CD_CTR	= W.CD_CTR  AND
			t.CD_FILIAL = W.CD_FILIAL;
	-------------------------------------------------------------------------------
		--Corrige a cd_rota
		--CREATE NONCLUSTERED INDEX IX_#tmpPedWMS_CD_PED_WMS ON #tmpPedWMS (CD_PED_WMS, CD_FIL_DEST)
		update s
		set
			CD_ROTA =t.CD_FIL_DEST
		from
			#tmpPedWMS T
			JOIN EST_WMS_SAI S ON 
			t.CD_PED_WMS = s.CD_PED_WMS;
	-------------------------------------------------------------------------------
	--CREATE NONCLUSTERED INDEX IX_#tmpPedWMS_CD_EMP_FILIAL ON #tmpPedWMS (CD_EMP, CD_CTR, CD_FILIAL) INCLUDE (CD_PED_WMS)
		INSERT INTO EST_PROD_TRANSF_EST_WMS_SAI(CD_EMP     , CD_FILIAL     , CD_CTR     , CD_PED_WMS)
		SELECT DISTINCT 
			  T.CD_EMP
			, T.CD_FILIAL
			, T.CD_CTR
			, T.CD_PED_WMS
		FROM 
			#tmpPedWMS T
			LEFT JOIN EST_PROD_TRANSF_EST_WMS_SAI W on
				t.CD_EMP	= W.CD_EMP AND
				t.CD_CTR	= W.CD_CTR  AND
				t.CD_FILIAL = W.CD_FILIAL
		WHERE
			w.CD_EMP IS NULL
		--Inser os itens
	--CREATE NONCLUSTERED INDEX IX_#tmpPedWMS_CD_PED_WMS_CD_PROD ON #tmpPedWMS (CD_EMP, CD_CTR, CD_PED_WMS,CD_PROD, CD_FILIAL) INCLUDE (QT_SOL, VLR_IT)
		INSERT INTO EST_WMS_SAI_IT(CD_PED_WMS,CD_PROD,QT_PROD,CD_EMP,VLR_IT)
		SELECT 
			T.CD_PED_WMS,
			T.CD_PROD,
			T.QT_SOL QT_PROD,
			T.CD_EMP,
			T.VLR_IT
		from 
			#tmpPedWMS T
			left join EST_WMS_SAI_IT i on 
			T.CD_PED_WMS =I.CD_PED_WMS AND
			T.CD_PROD =I.CD_PROD 
			JOIN EST_PROD_TRANSF_EST_WMS_SAI W on
			t.CD_EMP	= W.CD_EMP AND
			t.CD_CTR	= W.CD_CTR  AND
			t.CD_FILIAL = W.CD_FILIAL
		WHERE 
			I.CD_PED_WMS IS NULL;
	 COMMIT TRANSACTION; 
END TRY
BEGIN CATCH
	DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
        @ErrorMessage = ERROR_MESSAGE(),  
        @ErrorSeverity = ERROR_SEVERITY(),  
        @ErrorState = ERROR_STATE();  
    -- Use RAISERROR inside the CATCH block to return error  
    -- information about the original error that caused  
    -- execution to jump to the CATCH block.  
    RAISERROR (@ErrorMessage, -- Message text.  
               @ErrorSeverity, -- Severity.  
               @ErrorState -- State.  
               );  
	IF (XACT_STATE()) <> 0 
    BEGIN  
        ROLLBACK TRANSACTION;  
    END;  
END CATCH
DROP table #tmpPedWMS
GO


