IF EXISTS (SELECT * FROM SYS.procedures WHERE NAME = 'P_EST_PROD_CPL_ESTOQUE_CDS_DISTRIBUIDORAS_APLICA') 
	DROP PROCEDURE P_EST_PROD_CPL_ESTOQUE_CDS_DISTRIBUIDORAS_APLICA;

GO

--EXEC P_EST_PROD_CPL_ESTOQUE_CDS_DISTRIBUIDORAS_APLICA
CREATE  PROCEDURE [dbo].[P_EST_PROD_CPL_ESTOQUE_CDS_DISTRIBUIDORAS_APLICA] AS

	DECLARE @CD_EMP INT
	DECLARE @CD_FILIAL INT
	DECLARE @CD_IMP INT

	PRINT 'Buscando CDs para atualizacao' + CONVERT(VARCHAR(12), GETDATE(), 114)

	DECLARE 
		CDeposito 
	CURSOR FAST_FORWARD READ_ONLY FOR 
	SELECT
		CD_EMP,
		CD_FILIAL
	FROM
		PRC_FILIAL_DEPOSITO

	OPEN CDeposito

	FETCH NEXT FROM CDeposito INTO @CD_EMP, @CD_FILIAL
		WHILE @@FETCH_STATUS = 0
		begin

			PRINT 'Ajustando estoque do dia anterior ' + CONVERT(VARCHAR(12), GETDATE(), 114) 	
			--Retirando o estoque do CD, para o novo estoque importado
			SET @CD_IMP = (SELECT ISNULL(MAX(CD_IMP),0) + 1 FROM EST_PROD_IMP WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL)

			INSERT INTO
				EST_PROD_IMP (CD_EMP, CD_FILIAL, CD_IMP, CD_PROD, QT_ANT, QT_IMP, CD_USU, DT_CAD, HIST, TP_IMP, TP_EST, VLR_CUSTO_MD, DT_CAD_INFO) 
			SELECT
				CD_EMP,
				CD_FILIAL,
				@CD_IMP,
				CD_PROD,
				0,
				QT_EST * -1,
				1,
				CONVERT(CHAR(10),GETDATE(),112),
				'AJUSTE DE ESTOQUE DO DIA ANTERIOR DISTRIBUIDORA' AS HIST,
				0,
				0,
				VLR_CUSTO_MD,
				GETDATE() 
			FROM
				EST_PROD_CPL
			WHERE
				CD_EMP = @CD_EMP
				AND CD_FILIAL = @CD_FILIAL
				AND QT_EST <> 0


			PRINT 'Importando ajuste do estoque atual ' + CONVERT(VARCHAR(12), GETDATE(), 114) 	
			SET @CD_IMP = 0
			--Importando novo estoque do CD
			SET @CD_IMP = (SELECT ISNULL(MAX(CD_IMP),0) + 1 FROM EST_PROD_IMP WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL)


			INSERT INTO 
				EST_PROD_IMP (CD_EMP, CD_FILIAL, CD_IMP, CD_PROD, QT_ANT, QT_IMP, CD_USU, DT_CAD, HIST, TP_IMP, TP_EST, VLR_CUSTO_MD, DT_CAD_INFO) 
			SELECT
				d.CD_EMP,
				d.CD_FILIAL,
				@CD_IMP,
				p.CD_PROD,
				0,
				CASE WHEN COALESCE((d.QT_EST * p.APRES_EMB_CP * p.APRES_QT_CP), 0) > 900000 THEN 900000 ELSE COALESCE((d.QT_EST * p.APRES_EMB_CP * p.APRES_QT_CP), 0) END AS QT_EST,
				1,
				CONVERT(CHAR(10),GETDATE(),112),
				'AJUSTE DE ESTOQUE ATUAL DISTRIBUIDORA' AS HIST,
				0,
				0,
				0,
				GETDATE()
			FROM
				(SELECT
					DISTINCT
					CD_EMP,
					CD_PROD,
					CD_PROD_FORN,
					MAX(APRES_EMB_CP) AS APRES_EMB_CP,
					MAX(APRES_QT_CP) AS APRES_QT_CP
				FROM
					EST_PROD_FORN
				WHERE
					CD_FORN IN (591, 593, 1690, 952, 989,3851,4109, 3915)
				GROUP BY
					CD_EMP,
					CD_PROD,
					CD_PROD_FORN) P
				INNER JOIN EST_PROD_CPL_ESTOQUE_CDS_DISTRIBUIDORAS D ON
					P.CD_EMP = D.CD_EMP AND
					P.CD_PROD_FORN = D.CD_PROD_FORN COLLATE LATIN1_GENERAL_CI_AS
				INNER JOIN EST_PROD_CPL c  ON
					d.CD_EMP = c.CD_EMP AND
					d.CD_FILIAL = c.CD_FILIAL AND
					p.CD_PROD = c.CD_PROD 
			WHERE
				d.CD_EMP = 1
				AND d.CD_FILIAL = @CD_FILIAL
				AND D.QT_EST <> 0
			
			PRINT 'Ajustando estoque atual ' + CONVERT(VARCHAR(12), GETDATE(), 114) 	
			exec P_EST_PROD_EXTRATO_CALC @CD_EMP, @CD_FILIAL, 0


			FETCH NEXT FROM CDeposito INTO @CD_EMP, @CD_FILIAL
		end
	
	CLOSE CDeposito;
	DEALLOCATE CDeposito;

GO


