
IF EXISTS(SELECT * 
		FROM SYS.SYSOBJECTS 
		WHERE NAME ='P_GERA_EST_WMS_SAI' AND TYPE=N'P')
BEGIN
	DROP PROCEDURE P_GERA_EST_WMS_SAI
END
GO
CREATE PROCEDURE  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;

/**********************************************************************************************************/

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;
	
		CREATE NONCLUSTERED INDEX IX_#tmpPedWMS_Emp
			ON #tmpPedWMS (CD_EMP, CD_FILIAL, CD_CTR); 

		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
		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;

	-------------------------------------------------------------------------------

		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
		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
-------------------------------------------------------------------------------