/****** OBJECT:  STOREDPROCEDURE [DBO].[P_GERA_RESSUPRIMENTO_DEPOSITO_FILIAL_DIVIDE_RESSUP]    SCRIPT DATE: 09/08/2014 07:24:16 ******/
IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[P_GERA_RESSUPRIMENTO_DEPOSITO_FILIAL_DIVIDE_RESSUP]') AND TYPE IN (N'P', N'PC'))
DROP PROCEDURE [DBO].[P_GERA_RESSUPRIMENTO_DEPOSITO_FILIAL_DIVIDE_RESSUP]
GO
CREATE PROCEDURE [DBO].[P_GERA_RESSUPRIMENTO_DEPOSITO_FILIAL_DIVIDE_RESSUP] (@CD_EMP INT=1, @QTD_DIVIDE INT=299)
AS
	 
	DECLARE @CD_FILIAL INT
	DECLARE @CD_CTR INT
	DECLARE @CD_CTR_ULT INT
	DECLARE @ULT_CD_PROD INT
	DECLARE @SQLSTRING AS NVARCHAR(MAX)

	CREATE TABLE #TMP_TRANSF( CD_EMP INT, CD_FILIAL INT, CD_CTR INT, GERADO INT);
	CREATE TABLE #TMP_PROD (CD_EMP INT,	CD_FILIAL INT , 
									CD_PROD INT,QT_SOL MONEY, QT_AT MONEY, VLR_CUSTO MONEY,
									QT_EST_RES MONEY,QT_MEDIAF MONEY, QT_EMAX MONEY,QT_ESEG MONEY,
									FLAG_RATEIO INT,QT_EST_DEPO_RES MONEY,PERC_RATEIO MONEY,PERC_DESC MONEY,
									QT_EST MONEY,QT_PEND_ENT MONEY,	EMAX_RES MONEY,ESEG_RES MONEY,
									VLR_CUSTO_MD  MONEY,VLR_CUSTO_ACERTO  MONEY,QT_UNI_RESSUPRIMENTO  MONEY )
	SET @ULT_CD_PROD=0;
												
	INSERT INTO #TMP_TRANSF					
	SELECT  
		T.CD_EMP,
		T.CD_FILIAL,
		T.CD_CTR,
		0 GERADO
	FROM 
		EST_PROD_TRANSF T 
		INNER JOIN EST_PROD_TRANSF_IT I ON 
		T.CD_EMP	= I.CD_EMP AND
		T.CD_FILIAL = I.CD_FILIAL AND
		T.CD_CTR	= I.CD_CTR
	WHERE
		T.CD_EMP=1 
		AND ISNULL(T.RESSUP_DIVIDO,0) = 0
		AND T.STS_TRANSF = 0
	GROUP BY 
		T.CD_EMP,
		T.CD_FILIAL,
		T.CD_CTR
	HAVING COUNT(I.CD_EMP)> @QTD_DIVIDE;  

	-- Suspende as transferencias que irao ser dividas											
	UPDATE T
	SET
		STS_TRANSF=4
	FROM 
		EST_PROD_TRANSF T 
		INNER JOIN #TMP_TRANSF F ON 
		T.CD_EMP	= F.CD_EMP AND
		T.CD_FILIAL = F.CD_FILIAL AND
		T.CD_CTR	= F.CD_CTR
	WHERE
		T.CD_EMP=1;


	DECLARE CTRANSF CURSOR FAST_FORWARD FOR 
	SELECT CD_EMP, CD_FILIAL,CD_CTR  FROM #TMP_TRANSF
	
	OPEN CTRANSF 
	FETCH NEXT FROM CTRANSF INTO @CD_EMP,@CD_FILIAL,@CD_CTR;
	WHILE @@FETCH_STATUS=0	
		BEGIN
		
			TRUNCATE TABLE #TMP_PROD;
			
			SET @SQLSTRING =N'INSERT INTO #TMP_PROD (CD_EMP,	CD_FILIAL,
									CD_PROD,QT_SOL,	QT_AT,	VLR_CUSTO,
									QT_EST_RES,	QT_MEDIAF,	QT_EMAX,QT_ESEG,
									FLAG_RATEIO,QT_EST_DEPO_RES,PERC_RATEIO,PERC_DESC,
									QT_EST,QT_PEND_ENT,	EMAX_RES,ESEG_RES,
									VLR_CUSTO_MD,VLR_CUSTO_ACERTO,QT_UNI_RESSUPRIMENTO )
			SELECT  TOP ' + CONVERT(VARCHAR, @QTD_DIVIDE) + '
				@CD_EMP		CD_EMP,
				@CD_FILIAL  CD_FILIAL, 
				I.CD_PROD,
				I.QT_SOL,
				I.QT_AT,
				I.VLR_CUSTO,
				I.QT_EST_RES,
				I.QT_MEDIAF,
				I.QT_EMAX,
				I.QT_ESEG,
				I.FLAG_RATEIO,
				I.QT_EST_DEPO_RES,
				I.PERC_RATEIO,
				I.PERC_DESC,
				I.QT_EST,
				I.QT_PEND_ENT,
				I.EMAX_RES,
				I.ESEG_RES,
				I.VLR_CUSTO_MD,
				I.VLR_CUSTO_ACERTO,
				I.QT_UNI_RESSUPRIMENTO
			FROM 
				EST_PROD_TRANSF_IT I 
			WHERE
				I.CD_PROD > @ULT_CD_PROD
				AND I.CD_EMP	= @CD_EMP
				AND I.CD_FILIAL = @CD_FILIAL 
				AND I.CD_CTR	= @CD_CTR
			ORDER BY I.CD_PROD;'

			EXECUTE SP_EXECUTESQL @SQLSTRING,  N'@CD_EMP INT, @CD_FILIAL INT, @CD_CTR_ULT INT, @ULT_CD_PROD INT, @CD_CTR INT', 
												@CD_EMP, @CD_FILIAL, @CD_CTR_ULT, @ULT_CD_PROD, @CD_CTR;
				
			IF EXISTS(SELECT TOP 1 CD_EMP FROM #TMP_PROD)
			BEGIN
			
				SET @ULT_CD_PROD = (SELECT MAX(CD_PROD)FROM	#TMP_PROD);

				begin try

					SET @CD_CTR_ULT = ISNULL((SELECT MAX(CD_CTR) FROM  EST_PROD_TRANSF T WITH (NOLOCK)
											WHERE T.CD_EMP=1 
													AND T.CD_FILIAL = @CD_FILIAL),0) + 1; 

					INSERT INTO EST_PROD_TRANSF (CD_EMP,CD_FILIAL,CD_CTR,TP_TRANSF,CD_FIL_ORIG,CD_FIL_DEST,DT_TRANSF,
													DT_ENTR,STS_TRANSF,DT_CAD,CD_USU,DT_ULT_ATU,
													QTDE_VOLUMES,FLAG_CAIXA_FECHADA,
													FLAG_CHECKOUT_INICIADO,FLAG_QTDE_CHECKOUT,HR_TRANSF,RESSUP_DIVIDO)
				
					SELECT 
						CD_EMP,
						CD_FILIAL,
						@CD_CTR_ULT CD_CTR,
						TP_TRANSF,
						CD_FIL_ORIG,
						CD_FIL_DEST,
						DT_TRANSF,
						DT_ENTR,
						0 STS_TRANSF,
						DT_CAD,
						CD_USU,
						DT_ULT_ATU,
						QTDE_VOLUMES,
						FLAG_CAIXA_FECHADA,
						FLAG_CHECKOUT_INICIADO,
						FLAG_QTDE_CHECKOUT,
						HR_TRANSF,
						1 RESSUP_DIVIDO
					 FROM EST_PROD_TRANSF
					 WHERE
						CD_EMP=@CD_EMP
						AND CD_FILIAL =@CD_FILIAL 
						AND CD_CTR = @CD_CTR;
				 
					INSERT INTO EST_PROD_TRANSF_IT (CD_EMP,	CD_FILIAL,CD_CTR,
										CD_PROD,QT_SOL,	QT_AT,	VLR_CUSTO,
										QT_EST_RES,	QT_MEDIAF,	QT_EMAX,QT_ESEG,
										FLAG_RATEIO,QT_EST_DEPO_RES,PERC_RATEIO,PERC_DESC,
										QT_EST,QT_PEND_ENT,	EMAX_RES,ESEG_RES,
										VLR_CUSTO_MD,VLR_CUSTO_ACERTO,QT_UNI_RESSUPRIMENTO )
									
					SELECT
						CD_EMP,	CD_FILIAL,@CD_CTR_ULT CD_CTR,
						CD_PROD,QT_SOL,	QT_AT,	VLR_CUSTO,
						QT_EST_RES,	QT_MEDIAF,	QT_EMAX,QT_ESEG,
						FLAG_RATEIO,QT_EST_DEPO_RES,PERC_RATEIO,PERC_DESC,
						QT_EST,QT_PEND_ENT,	EMAX_RES,ESEG_RES,
						VLR_CUSTO_MD,VLR_CUSTO_ACERTO,QT_UNI_RESSUPRIMENTO 
					FROM 
						#TMP_PROD;

				end try
				begin catch
					--Devolve o status em caso de erro
					UPDATE EST_PROD_TRANSF
					SET STS_TRANSF =0
					WHERE
						CD_EMP=@CD_EMP
						AND CD_FILIAL =@CD_FILIAL 
						AND CD_CTR = @CD_CTR;

					if ERROR_NUMBER() =2627
						print 'Falha ao buscar o codigo transferencia'

				end catch
			END
			
			IF NOT EXISTS( SELECT I.CD_PROD FROM 
								EST_PROD_TRANSF_IT I 
							WHERE
								I.CD_PROD >@ULT_CD_PROD
								AND I.CD_EMP	= @CD_EMP
								AND I.CD_FILIAL = @CD_FILIAL 
								AND I.CD_CTR	= @CD_CTR )
			BEGIN
				
				SET @ULT_CD_PROD=0;
				
				--APAGA A TRANSF ORIGINAL UMA VEZ QUE FOI DIVIDA EM OUTRAS..
				DELETE FROM EST_PROD_TRANSF 
				WHERE 
					CD_EMP	= @CD_EMP
					AND CD_FILIAL = @CD_FILIAL 
					AND CD_CTR	= @CD_CTR ;
					
				FETCH NEXT FROM CTRANSF INTO @CD_EMP,@CD_FILIAL,@CD_CTR;
			END
			
		END 
		CLOSE CTRANSF;		
		DEALLOCATE CTRANSF;

GO
