IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_PROD_EXTRATO_CALC]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_PROD_EXTRATO_CALC]
GO
  

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO 
--exec P_EST_PROD_EXTRATO_CALC 1,13,0,0,0,1
-- Versao 3237
/*
=======================================================================================
'Job.................:  
'Função..............: 
'Objetivo............: Ajuste do estoque pela Movimentação 
'Tabelas.............: 
'Dt Modificação......: 10/06/2024
'Modificação.........: Tag #A001 Validar estono de nota fiscal
'=======================================================================================
Tag #A001 - Alteração do codigo  
Tag #I001 - Inclusão de novo codigo 
*/

CREATE PROCEDURE [dbo].[P_EST_PROD_EXTRATO_CALC](@CD_EMP			INT,
												 @CD_FILIAL		INT,
												 @TP_EST		INT,
												 @FLAG_DEBUG	INT = NULL,
												 @CD_PROD		INT = 0,
												 @CD_USU		INT=1	)
WITH ENCRYPTION AS
SET NOCOUNT ON
DECLARE @DT_ATUAL DATETIME
SET @DT_ATUAL=DATEADD(MONTH,-1,GETDATE())

DECLARE @VALIDA_INVENTARIO_ABERTO_CALCULO_EXTRATO INT

SELECT @VALIDA_INVENTARIO_ABERTO_CALCULO_EXTRATO = VALOR
FROM PRC_FILIAL_CONFIG 
WHERE CD_EMP = @CD_EMP 
AND CD_FILIAL = @CD_FILIAL 
AND CD_CHAVE = 'VALIDA_INVENTARIO_ABERTO_CALCULO_EXTRATO'


IF @VALIDA_INVENTARIO_ABERTO_CALCULO_EXTRATO = 1
BEGIN

	IF EXISTS (SELECT 1 
				FROM EST_SNGPC_REINICIO_INVENTARIO I
				WHERE I.CD_EMP = @CD_EMP
				AND I.CD_FILIAL = @CD_FILIAL
				AND I.STS_INVENTARIO = 0)
	BEGIN

		PRINT 'Existe um inventario em aberto para essa Filial!';

		RETURN;

	END

END


IF NOT EXISTS(SELECT
				CD_EMP
			  FROM
				PRC_FILIAL_EST_FECHA
			  WHERE
				CD_EMP =@CD_EMP
				AND CD_FILIAL=@CD_FILIAL
				AND ANO_MES_FECHA=YEAR(@DT_ATUAL)*100+MONTH(@DT_ATUAL)
				)
BEGIN
	INSERT INTO ADM_ERRO (numero, severidade, estado, rotina, linha, mensagem, dtErro)
	SELECT
		9999 AS numero,
		9999 AS severidade,
		0 AS estado,
		'P_EST_PROD_EXTRATO_CALC' AS rotina,
		0 AS linha,
		'Fechamento de estoque nao realizado filial.: ' + convert(varchar(10),@cd_filial) AS mensagem,
		GETDATE() AS dtErro;
	RETURN
END

CREATE TABLE #RS_SOMA (CD_PROD INT, 
						QT_IT  MONEY)

CREATE TABLE #RS_SUB (CD_PROD INT, 
						QT_IT  MONEY)

CREATE TABLE #RS_MOV (CD_PROD		INT, 
						DS_PROD		VARCHAR(150), 
						QT_IT		MONEY, 
						TP_TRANSA	INT)

CREATE TABLE #RS_EXTRATO (
						ORDEM INT IDENTITY (1, 1), 
						CD_PROD INT, 	
						DS_PROD VARCHAR(150), 
						QT_IT MONEY,
						TP_TRANSA INT, 
						QT_EST MONEY)

CREATE TABLE #RS_MOV_AUDIT(
						   CD_PROD INT,
						   QTDE_MOV MONEY,
						   TIPO_OPERA VARCHAR(100)
						   )
						   
						   
DECLARE  @DT_INI_SISTEMA  AS DATETIME  
DECLARE  @DT_INI  AS DATETIME 
DECLARE  @DT_FIM AS DATETIME 
DECLARE  @DT_FECHA_EST  AS DATETIME 
DECLARE  @FLAG_BALAN_ABERTO INT

SET @DT_INI_SISTEMA = (SELECT TOP 1 ISNULL(DT_INI_SISTEMA,GETDATE())
					  FROM	PRC_FILIAL
					  WHERE CD_FILIAL = @CD_FILIAL
						AND CD_EMP = @CD_EMP);

--Inicia procedure com o balanco aberto
SET @FLAG_BALAN_ABERTO = 1

--Buscando balan?o em aberto
SELECT 
	@DT_FIM = DT_CAD
FROM
	EST_PROD_BALAN
WHERE
	CD_EMP = @CD_EMP
	AND CD_FILIAL = @CD_FILIAL
	AND STS_BALAN <> 2
	AND TP_EST = @TP_EST;

--Abertura balanco retroativo
IF @DT_FIM IS NULL
	begin
		PRINT 'Data de posicao estoque.: ' + CONVERT( CHAR(10),@DT_FIM,112)
		SET @FLAG_BALAN_ABERTO = 0

		SELECT
			@DT_FIM = DT_ABERTURA_BALAN
		FROM
			EST_PROD_BALAN_ABERTURA_RETROATIVA
		WHERE
			CD_EMP = @CD_EMP
			AND CD_FILIAL = @CD_FILIAL
			AND STS_EVENTOS_BALAN = 0
	end

--N?o tem balan?o em aberto
IF @DT_FIM IS NULL
begin
	--Nao possui balanco aberto
	SET @FLAG_BALAN_ABERTO = 0
	SET @DT_FIM = DBO.F_HOJE()
end

SET @DT_INI	  =DBO.F_INICIO_MES (@DT_FIM) 
SET @DT_FECHA_EST = DBO.F_FINAL_MES (@DT_INI-5)

IF @DT_INI_SISTEMA >= @DT_INI 
BEGIN
	SET @DT_INI = @DT_INI_SISTEMA 
END

	--Inserindo fechamento de estoque para iniciar calculos de 
	--retorno de fechamento de estoque
	INSERT INTO #RS_MOV
	SELECT     
		EST_FECHA.CD_PROD, 
		EST_PROD.DS_PROD,
		CASE WHEN @TP_EST= 0 THEN
			EST_FECHA.QT_EST
		WHEN @TP_EST= 1 THEN
			ISNULL(EST_FECHA.QT_EST_FLUT,0)
		WHEN @TP_EST=2 THEN
			ISNULL(EST_FECHA.QT_EST_EM_TERC,0)
		WHEN @TP_EST=3 THEN
			ISNULL(EST_FECHA.QT_EST_DE_TERC,0)
		END, 
		0
	FROM         
		EST_FECHA 
		INNER JOIN EST_PROD_CPL ON 
		EST_FECHA.CD_EMP	= EST_PROD_CPL.CD_EMP AND 
		EST_FECHA.CD_FILIAL = EST_PROD_CPL.CD_FILIAL AND 
	    EST_FECHA.CD_PROD	= EST_PROD_CPL.CD_PROD 

		INNER JOIN EST_PROD ON 
		EST_PROD_CPL.CD_EMP  = EST_PROD.CD_EMP AND 
		EST_PROD_CPL.CD_PROD = EST_PROD.CD_PROD
	WHERE     
		EST_FECHA.CD_EMP = @CD_EMP 
		AND EST_FECHA.CD_FILIAL = @CD_FILIAL 
		AND EST_FECHA.ANO_FECHA = YEAR(@DT_FECHA_EST)
		AND EST_FECHA.MES_FECHA = MONTH(@DT_FECHA_EST);

	--Inserindo produtos que foram cadastrados no periodo
	--pois os mesmos n?o constam no fechamento do m?s anterior
	--entao inserir os mesmos com o fechamento zerado

	IF DATEPART(DAY,GETDATE() )=1
	BEGIN
		IF NOT EXISTS (SELECT TOP 1 CD_PROD
							FROM #RS_MOV )
			BEGIN
				Print 'Fechamento mes anterior nao realizado';
				RETURN;
			END 
	END

	INSERT INTO #RS_MOV
	SELECT     
		EST_PROD.CD_PROD, 
		EST_PROD.DS_PROD,
		0, 
		0
	FROM         
		EST_PROD 
		LEFT JOIN #RS_MOV RS_MOV	ON 
		EST_PROD.CD_PROD = RS_MOV.CD_PROD  
	WHERE     
		EST_PROD.CD_EMP	 = 1
		AND  RS_MOV.CD_PROD IS NULL;

	INSERT INTO #RS_MOV_AUDIT
	SELECT
		CD_PROD,
		QT_IT,
		'FECHAMENTO'
	FROM
		#RS_MOV; 

IF  @TP_EST = 0 --Estoque de comercializacao
begin

/*********************************************************************************************************************************************/
--Entrada de notas fiscais
/*********************************************************************************************************************************************/

	TRUNCATE TABLE #RS_SOMA;

	INSERT INTO #RS_SOMA 
	SELECT DISTINCT 
		EST_NF_ENT_IT.CD_PROD, 
		SUM(EST_NF_ENT_IT.QT_IT) AS QT_IT
	FROM    
		EST_NF_ENT 
		INNER JOIN GLB_MOV_OP ON 
		EST_NF_ENT.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP 
		INNER JOIN EST_NF_ENT_IT ON 
		EST_NF_ENT.CD_EMP	 = EST_NF_ENT_IT.CD_EMP AND 
		EST_NF_ENT.CD_FILIAL = EST_NF_ENT_IT.CD_FILIAL AND 
		EST_NF_ENT.CD_NF	 = EST_NF_ENT_IT.CD_NF
	WHERE   
		EST_NF_ENT.CD_EMP			= @CD_EMP 
		AND EST_NF_ENT.CD_FILIAL	= @CD_FILIAL
		AND EST_NF_ENT.DT_ENT BETWEEN @DT_INI AND @DT_FIM
		AND GLB_MOV_OP.ATU_EST	= 1
		AND GLB_MOV_OP.TP_ESTQ	= 0
		AND EST_NF_ENT.STS_NF	= 0
	GROUP BY 
		EST_NF_ENT_IT.CD_PROD; 

	INSERT INTO #RS_MOV_AUDIT
	SELECT
		CD_PROD,
		QT_IT,
		'ENTRADA NOTA FISCAL'
	FROM
		#RS_SOMA;
		 
	UPDATE  
		RS   
	SET
		QT_IT = RS.QT_IT + ISNULL(RS_SOMA.QT_IT,0)
	FROM 
		#RS_MOV RS
		INNER JOIN #RS_SOMA RS_SOMA ON
		RS_SOMA.CD_PROD = RS.CD_PROD; 


/*********************************************************************************************************************************************/
--Estorno de notas fiscais
/*********************************************************************************************************************************************/
-- Tag #A001
	DELETE FROM #RS_SOMA

	
	INSERT INTO #RS_SOMA
	SELECT I.CD_PROD, 
		   SUM(I.QT_IT)* - 1 AS QT_IT
	  FROM EST_NF_ENT_IT_ESTORNO I 
		   INNER JOIN GLB_MOV_OP O ON I.CD_MOV_OP = O.CD_MOV_OP
	 WHERE I.CD_EMP		= @CD_EMP   
	   AND I.CD_FILIAL	= @CD_FILIAL  
	   AND CONVERT(DATE,I.DT_CAD)  BETWEEN @DT_INI AND @DT_FIM
	--   AND I.CD_PROD	= @CD_PROD  
	   AND O.ATU_EST	= 1  
	   AND O.TP_ESTQ	= 0  
	 GROUP BY I.CD_PROD


	INSERT INTO #RS_MOV_AUDIT
	SELECT
		CD_PROD,
		QT_IT,
		'ESTORNO NOTA FISCAL'
	FROM
		#RS_SOMA;
	  
	   

	UPDATE  
		RS   
	SET
		QT_IT = RS.QT_IT + ISNULL(RS_SOMA.QT_IT,0)
	FROM 
		#RS_MOV RS
		INNER JOIN #RS_SOMA RS_SOMA ON
		RS_SOMA.CD_PROD = RS.CD_PROD; 


	
/*********************************************************************************************************************************************/
--Ajuste de estoque	
/*********************************************************************************************************************************************/
	
	DELETE FROM #RS_SOMA;

	INSERT INTO #RS_SOMA	 
	SELECT 
		EST_PROD_IMP.CD_PROD ,  
		SUM(EST_PROD_IMP.QT_IMP) 
	FROM 
		EST_PROD_IMP 
	WHERE     
		EST_PROD_IMP.CD_EMP = @CD_EMP 
		AND EST_PROD_IMP.CD_FILIAL = @CD_FILIAL 
		AND EST_PROD_IMP.DT_CAD BETWEEN @DT_INI AND @DT_FIM
		AND EST_PROD_IMP.TP_EST = 0
	GROUP BY 
		EST_PROD_IMP.CD_PROD;

	INSERT INTO #RS_MOV_AUDIT
	SELECT
		CD_PROD,
		QT_IT,
		'AJUSTE ESTOQUE'
	FROM
		#RS_SOMA 

	UPDATE  
		#RS_MOV   
	SET
	   QT_IT = RS.QT_IT + ISNULL(RS_SOMA.QT_IT,0)
	FROM 
		#RS_MOV RS
		INNER JOIN #RS_SOMA RS_SOMA ON
		RS.CD_PROD = RS_SOMA.CD_PROD ; 

/*********************************************************************************************************************************************/
--Saida de venda cupom
/*********************************************************************************************************************************************/

	DELETE FROM #RS_SUB;
	INSERT INTO #RS_SUB
	SELECT     
		PDV_VD_IT.CD_PROD, 
		SUM(PDV_VD_IT.QT_IT) AS QT_IT
	FROM         
		PDV_VD 
		INNER JOIN PDV_VD_IT ON 
		PDV_VD.CD_VD	 = PDV_VD_IT.CD_VD AND 
		PDV_VD.CD_FILIAL = PDV_VD_IT.CD_FILIAL AND 
		PDV_VD.CD_EMP	 = PDV_VD_IT.CD_EMP
	WHERE
		PDV_VD.CD_FILIAL = @CD_FILIAL 
		AND PDV_VD.DT_VD BETWEEN @DT_INI AND @DT_FIM
		AND PDV_VD.CD_EMP = @CD_EMP 
		AND PDV_VD.ST_VD  = 0 
		AND PDV_VD_IT.ST_IT = 1
	GROUP BY 
		PDV_VD_IT.CD_PROD; 

	INSERT INTO #RS_MOV_AUDIT
	SELECT
		CD_PROD,
		QT_IT,
		'CUPOM FISCAL'
	FROM
		#RS_SOMA;

	UPDATE  
	   #RS_MOV   
	SET
	   QT_IT = RS.QT_IT - ISNULL(RS_SUB.QT_IT,0)
	FROM 
		#RS_MOV RS
		INNER JOIN  #RS_SUB RS_SUB ON
		RS.CD_PROD = RS_SUB.CD_PROD; 
		 
/*********************************************************************************************************************************************/
--Saida de nota fiscal
/*********************************************************************************************************************************************/

	DELETE FROM #RS_SUB;
	INSERT INTO #RS_SUB 
	SELECT    	
		EST_NF_SAI_IT.CD_PROD, 
		SUM(EST_NF_SAI_IT.QT_IT) AS QT_IT
	FROM    
		EST_NF_SAI 

		INNER JOIN EST_NF_SAI_IT ON 
		EST_NF_SAI.CD_EMP	 = EST_NF_SAI_IT.CD_EMP AND 
		EST_NF_SAI.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL AND 
		EST_NF_SAI.CD_NF	 = EST_NF_SAI_IT.CD_NF 
 
		INNER JOIN GLB_MOV_OP ON 
		EST_NF_SAI.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP
  
		LEFT JOIN PDV_VD_EST_NF_SAI S ON
		EST_NF_SAI.CD_EMP	  = S.CD_EMP    AND 
		EST_NF_SAI.CD_FILIAL  = S.CD_FILIAL AND
		EST_NF_SAI.CD_NF	  = S.CD_NF	  
	WHERE   
		EST_NF_SAI.CD_EMP		= @CD_EMP 
		AND EST_NF_SAI.DT_EMI_NF BETWEEN @DT_INI AND @DT_FIM
		AND EST_NF_SAI.CD_FILIAL = @CD_FILIAL
		AND GLB_MOV_OP.BX_EST  = 1
		AND GLB_MOV_OP.TP_ESTQ = 0
		AND EST_NF_SAI.STS_NF  = 0
		AND S.CD_NF IS NULL
	GROUP BY 
		EST_NF_SAI_IT.CD_PROD; 

	INSERT INTO #RS_MOV_AUDIT
	SELECT
		CD_PROD,
		QT_IT,
		'SAIDA NOTA FISCAL'
	FROM
		#RS_SOMA;

	UPDATE  
		 #RS_MOV   
	SET
	   QT_IT = RS.QT_IT - ISNULL(RS_SUB.QT_IT,0)
	FROM 
		#RS_MOV RS
		INNER JOIN  #RS_SUB RS_SUB ON
		RS.CD_PROD = RS_SUB.CD_PROD;


/*********************************************************************************************************************************************/
--Cancelamento fora do prazo
/*********************************************************************************************************************************************/

	DELETE FROM #RS_SUB;
	INSERT INTO #RS_SUB 
	SELECT    	
		EST_NF_SAI_IT.CD_PROD, 
		SUM(EST_NF_SAI_IT.QT_IT) AS QT_IT
	FROM    
		EST_NF_SAI 
		INNER JOIN EST_NF_SAI_IT ON 
		EST_NF_SAI.CD_EMP	 = EST_NF_SAI_IT.CD_EMP AND 
		EST_NF_SAI.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL AND 
		EST_NF_SAI.CD_NF	 = EST_NF_SAI_IT.CD_NF 

		INNER JOIN GLB_MOV_OP ON 
		EST_NF_SAI.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP
  
		LEFT JOIN PDV_VD_EST_NF_SAI S ON
		EST_NF_SAI.CD_EMP	  = S.CD_EMP    AND 
		EST_NF_SAI.CD_FILIAL  = S.CD_FILIAL AND
		EST_NF_SAI.CD_NF	  = S.CD_NF	  
		INNER JOIN EST_NF_SAI_CANCEL ON
		EST_NF_SAI.CD_EMP = EST_NF_SAI_CANCEL.CD_EMP AND
		EST_NF_SAI.CD_FILIAL = EST_NF_SAI_CANCEL.CD_FILIAL AND
		EST_NF_SAI.CD_NF = EST_NF_SAI_CANCEL.CD_NF 
		INNER JOIN GLB_USU ON
		EST_NF_SAI_CANCEL.CD_USU = GLB_USU.CD_USU
	WHERE   
		EST_NF_SAI.CD_EMP		= @CD_EMP 
		AND EST_NF_SAI_CANCEL.DT_CANCEL BETWEEN @DT_INI AND @DT_FIM
		AND EST_NF_SAI.CD_FILIAL = @CD_FILIAL
		AND GLB_MOV_OP.BX_EST  = 1
		AND GLB_MOV_OP.TP_ESTQ = 0
		AND EST_NF_SAI.STS_NF  = 0
		AND S.CD_NF IS NULL
		AND EST_NF_SAI_CANCEL.CANCEL_FORA_PRAZO=1
	GROUP BY 
		EST_NF_SAI_IT.CD_PROD;
		 
	INSERT INTO #RS_MOV_AUDIT
	SELECT
		CD_PROD,
		QT_IT,
		'CANCEL FORA PRAZO SAIDA NOTA FISCAL'
	FROM
		#RS_SOMA
 
	UPDATE  
		 #RS_MOV   
	SET
	   QT_IT = RS.QT_IT + ISNULL(RS_SUB.QT_IT,0)
	FROM 
		#RS_MOV RS
		INNER JOIN  #RS_SUB RS_SUB ON
		RS.CD_PROD = RS_SUB.CD_PROD;
		 
/*********************************************************************************************************************************************/
--Saida pedido 
/*********************************************************************************************************************************************/

	DELETE FROM #RS_SUB;

	INSERT INTO #RS_SUB 
	SELECT     
		EST_PED_VD_IT.CD_PROD, 
		SUM(EST_PED_VD_IT.QT_IT) AS QT_IT
	FROM         
		EST_PED_VD 
		INNER JOIN EST_PED_VD_IT ON 
		EST_PED_VD.CD_EMP = EST_PED_VD_IT.CD_EMP AND 
		EST_PED_VD.CD_FILIAL = EST_PED_VD_IT.CD_FILIAL AND 
		EST_PED_VD.CD_PED = EST_PED_VD_IT.CD_PED 
		INNER JOIN EST_PED_VD_ORC ON 
		EST_PED_VD.CD_EMP = EST_PED_VD_ORC.CD_EMP AND 
		EST_PED_VD.CD_FILIAL = EST_PED_VD_ORC.CD_FILIAL AND 
		EST_PED_VD.CD_PED = EST_PED_VD_ORC.CD_PED
	WHERE     
		EST_PED_VD_ORC.CD_EMP = @CD_EMP 
		AND EST_PED_VD_ORC.CD_FILIAL = @CD_FILIAL 
		AND EST_PED_VD_ORC.DT_CAD BETWEEN @DT_INI AND @DT_FIM
	GROUP BY 
		EST_PED_VD_IT.CD_PROD; 

	INSERT INTO #RS_MOV_AUDIT
	SELECT
		CD_PROD,
		QT_IT,
		'ORCAMENTO'
	FROM
		#RS_SOMA 

	UPDATE  
		#RS_MOV   
	SET
	  QT_IT = RS.QT_IT - ISNULL(RS_SUB.QT_IT,0)
	FROM 
 		#RS_MOV RS
		INNER JOIN  #RS_SUB RS_SUB ON
		RS.CD_PROD = RS_SUB.CD_PROD;
				 
/*********************************************************************************************************************************************/
-- pedido compra
/*********************************************************************************************************************************************/

	DELETE FROM #RS_SUB;
	INSERT INTO #RS_SUB 
	SELECT     
		EST_PED_CP_IT.CD_PROD, 
		SUM(EST_PED_CP_IT.QT_IT)
	FROM         
		EST_PED_CP INNER JOIN EST_PED_CP_IT ON 
		EST_PED_CP.CD_EMP = EST_PED_CP_IT.CD_EMP AND 
		EST_PED_CP.CD_FILIAL = EST_PED_CP_IT.CD_FILIAL AND 
		EST_PED_CP.CD_PED = EST_PED_CP_IT.CD_PED 
		INNER JOIN EST_PED_CP_ORC ON 
		EST_PED_CP.CD_EMP = EST_PED_CP_ORC.CD_EMP AND 
		EST_PED_CP.CD_FILIAL = EST_PED_CP_ORC.CD_FILIAL AND 
		EST_PED_CP.CD_PED = EST_PED_CP_ORC.CD_PED
	WHERE
		EST_PED_CP_ORC.CD_EMP = @CD_EMP 
		AND EST_PED_CP_ORC.CD_FILIAL = @CD_FILIAL
		AND EST_PED_CP_ORC.DT_CAD BETWEEN @DT_INI AND @DT_FIM
	GROUP BY 
		EST_PED_CP_IT.CD_PROD; 

	INSERT INTO #RS_MOV_AUDIT
	SELECT
		CD_PROD,
		QT_IT,
		'ORCAMENTO ENTRADA'
	FROM
		#RS_SOMA;

	UPDATE  
		#RS_MOV   
	SET
		QT_IT = RS.QT_IT + ISNULL(RS_SUB.QT_IT,0)
	FROM 
 		#RS_MOV RS
		INNER JOIN  #RS_SUB RS_SUB ON
		RS.CD_PROD = RS_SUB.CD_PROD;

end 


--xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 
--xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 
--xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 

IF  @TP_EST = 1 --Estoque flutuante 
begin
	DELETE FROM #RS_SOMA
	INSERT INTO #RS_SOMA 
	
	SELECT DISTINCT 
		EST_NF_ENT_IT.CD_PROD, 
		SUM(EST_NF_ENT_IT.QT_IT) AS QT_IT
	FROM    
		EST_NF_ENT INNER JOIN GLB_MOV_OP ON 
		EST_NF_ENT.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP 
		INNER JOIN EST_NF_ENT_IT ON 
		EST_NF_ENT.CD_EMP = EST_NF_ENT_IT.CD_EMP AND 
		EST_NF_ENT.CD_FILIAL = EST_NF_ENT_IT.CD_FILIAL AND 
		EST_NF_ENT.CD_NF = EST_NF_ENT_IT.CD_NF
	WHERE   
		EST_NF_ENT.CD_EMP = @CD_EMP 
		AND EST_NF_ENT.CD_FILIAL = @CD_FILIAL
		AND EST_NF_ENT.DT_ENT BETWEEN @DT_INI AND @DT_FIM
		AND GLB_MOV_OP.ATU_EST = 1
		AND GLB_MOV_OP.TP_ESTQ=1
		AND EST_NF_ENT.STS_NF = 0 
	GROUP BY 
		EST_NF_ENT_IT.CD_PROD
		 
	UPDATE  
		#RS_MOV   
	SET
	   QT_IT = RS.QT_IT + ISNULL(RS_SOMA.QT_IT,0)
	FROM 
		#RS_MOV RS, 
		#RS_SOMA RS_SOMA
	WHERE   
		RS_SOMA.CD_PROD = RS.CD_PROD;

	--Auditoria
	INSERT INTO #RS_MOV_AUDIT
	SELECT
		CD_PROD,
		QT_IT,
		'NF ENTRADA'
	FROM
		#RS_MOV 
-----------------------------------------------------------------------------
	DELETE FROM #RS_SOMA
	INSERT INTO #RS_SOMA
	 
	SELECT 
		EST_PROD_IMP.CD_PROD ,  
		SUM(EST_PROD_IMP.QT_IMP) 
	FROM 
		EST_PROD_IMP 
	WHERE     
		EST_PROD_IMP.CD_EMP = @CD_EMP 
		AND EST_PROD_IMP.CD_FILIAL = @CD_FILIAL 
		AND EST_PROD_IMP.DT_CAD BETWEEN @DT_INI AND @DT_FIM
		AND EST_PROD_IMP.TP_EST = 1
	GROUP BY 
		EST_PROD_IMP.CD_PROD ;
		  
	UPDATE  
		#RS_MOV   
	SET
	   QT_IT = RS.QT_IT + ISNULL(RS_SOMA.QT_IT,0)
	FROM 
		#RS_MOV RS, 
		#RS_SOMA RS_SOMA
	WHERE   
		RS_SOMA.CD_PROD = RS.CD_PROD;

	--Auditoria
	INSERT INTO #RS_MOV_AUDIT
	SELECT
		CD_PROD,
		QT_IT,
		'AJUSTE'
	FROM
		#RS_MOV
-----------------------------------------------------------------------------

	DELETE FROM #RS_SUB
	INSERT INTO #RS_SUB 
	SELECT    	
		EST_NF_SAI_IT.CD_PROD, 
		SUM(EST_NF_SAI_IT.QT_IT) AS QT_IT
	FROM    
		EST_NF_SAI INNER JOIN EST_NF_SAI_IT ON 
		EST_NF_SAI.CD_EMP = EST_NF_SAI_IT.CD_EMP AND 
		EST_NF_SAI.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL AND 
		EST_NF_SAI.CD_NF = EST_NF_SAI_IT.CD_NF 
		INNER JOIN GLB_MOV_OP ON 
		EST_NF_SAI.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP
	WHERE   
		EST_NF_SAI.DT_EMI_NF BETWEEN @DT_INI AND @DT_FIM
		AND EST_NF_SAI.CD_EMP = @CD_EMP 
		AND EST_NF_SAI.CD_FILIAL = @CD_FILIAL
		AND GLB_MOV_OP.BX_EST = 1
		AND GLB_MOV_OP.TP_ESTQ=1 
		AND EST_NF_SAI.STS_NF = 0
	GROUP BY EST_NF_SAI_IT.CD_PROD;
	 
	UPDATE  
		 #RS_MOV   
	SET
	   QT_IT = RS.QT_IT - ISNULL(RS_SUB.QT_IT,0)
	FROM 
		#RS_MOV RS, 
		#RS_SUB RS_SUB
	WHERE   
		RS_SUB.CD_PROD = RS.CD_PROD 
			
	--Auditoria
	INSERT INTO #RS_MOV_AUDIT
	SELECT
		CD_PROD,
		QT_IT,
		'NF SAIDA'
	FROM
		#RS_MOV
		
/*********************************************************************************************************************************************/
--Cancelamento fora do prazo
/*********************************************************************************************************************************************/

	DELETE FROM #RS_SUB;
	INSERT INTO #RS_SUB 
	SELECT    	
		EST_NF_SAI_IT.CD_PROD, 
		SUM(EST_NF_SAI_IT.QT_IT) AS QT_IT
	FROM    
		EST_NF_SAI 
		INNER JOIN EST_NF_SAI_IT ON 
		EST_NF_SAI.CD_EMP	 = EST_NF_SAI_IT.CD_EMP AND 
		EST_NF_SAI.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL AND 
		EST_NF_SAI.CD_NF	 = EST_NF_SAI_IT.CD_NF 

		INNER JOIN GLB_MOV_OP ON 
		EST_NF_SAI.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP
  
		LEFT JOIN PDV_VD_EST_NF_SAI S ON
		EST_NF_SAI.CD_EMP	  = S.CD_EMP    AND 
		EST_NF_SAI.CD_FILIAL  = S.CD_FILIAL AND
		EST_NF_SAI.CD_NF	  = S.CD_NF	  
		INNER JOIN EST_NF_SAI_CANCEL ON
		EST_NF_SAI.CD_EMP = EST_NF_SAI_CANCEL.CD_EMP AND
		EST_NF_SAI.CD_FILIAL = EST_NF_SAI_CANCEL.CD_FILIAL AND
		EST_NF_SAI.CD_NF = EST_NF_SAI_CANCEL.CD_NF 
		INNER JOIN GLB_USU ON
		EST_NF_SAI_CANCEL.CD_USU = GLB_USU.CD_USU
	WHERE   
		EST_NF_SAI.CD_EMP		= @CD_EMP 
		AND EST_NF_SAI_CANCEL.DT_CANCEL BETWEEN @DT_INI AND @DT_FIM
		AND EST_NF_SAI.CD_FILIAL = @CD_FILIAL
		AND GLB_MOV_OP.BX_EST  = 1
		AND GLB_MOV_OP.TP_ESTQ = 0
		AND EST_NF_SAI.STS_NF  = 0
		AND S.CD_NF IS NULL
		AND EST_NF_SAI_CANCEL.CANCEL_FORA_PRAZO=1
	GROUP BY 
		EST_NF_SAI_IT.CD_PROD;

--Auditoria
	INSERT INTO #RS_MOV_AUDIT
	SELECT
		CD_PROD,
		QT_IT,
		'CANCEL FORA PRAZO SAIDA NOTA FISCAL'
	FROM
		#RS_SOMA
--Fim
	-----------------------------------------------------------------------------------------------------------------
	UPDATE  
		 #RS_MOV   
	SET
	   QT_IT = RS.QT_IT + ISNULL(RS_SUB.QT_IT,0)
	FROM 
		#RS_MOV RS
		INNER JOIN  #RS_SUB RS_SUB ON
		RS.CD_PROD = RS_SUB.CD_PROD;

	--------------------------------------------------------------------------------------------------------------
end

--xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

IF  @TP_EST = 2 --Estoque em terceiros
begin
	DELETE FROM #RS_SOMA
	INSERT INTO #RS_SOMA 
	SELECT DISTINCT 
		EST_NF_ENT_IT.CD_PROD, 
		SUM(EST_NF_ENT_IT.QT_IT) AS QT_IT
	FROM    
		EST_NF_ENT INNER JOIN GLB_MOV_OP ON 
		EST_NF_ENT.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP 
		INNER JOIN EST_NF_ENT_IT ON 
		EST_NF_ENT.CD_EMP = EST_NF_ENT_IT.CD_EMP AND 
		EST_NF_ENT.CD_FILIAL = EST_NF_ENT_IT.CD_FILIAL AND 
		EST_NF_ENT.CD_NF = EST_NF_ENT_IT.CD_NF
	WHERE   
		EST_NF_ENT.CD_EMP = @CD_EMP 
		AND EST_NF_ENT.CD_FILIAL = @CD_FILIAL
		AND EST_NF_ENT.DT_ENT BETWEEN @DT_INI AND @DT_FIM
		AND GLB_MOV_OP.ATU_EST = 1
		AND GLB_MOV_OP.TP_ESTQ= 2
		AND EST_NF_ENT.STS_NF = 0 
	GROUP BY 
		EST_NF_ENT_IT.CD_PROD

	UPDATE  
		#RS_MOV   
	SET
	   QT_IT = RS.QT_IT + ISNULL(RS_SOMA.QT_IT,0)
	FROM 
		#RS_MOV RS, 
		#RS_SOMA RS_SOMA
	WHERE   
		RS_SOMA.CD_PROD = RS.CD_PROD

-----------------------------------------------------------------------------
	DELETE FROM #RS_SOMA
	INSERT INTO #RS_SOMA
	 
	SELECT 
		EST_PROD_IMP.CD_PROD ,  
		SUM(EST_PROD_IMP.QT_IMP) 
	FROM 
		EST_PROD_IMP 
	WHERE     
		EST_PROD_IMP.CD_EMP = @CD_EMP 
		AND EST_PROD_IMP.CD_FILIAL = @CD_FILIAL 
		AND EST_PROD_IMP.DT_CAD BETWEEN @DT_INI AND @DT_FIM
		AND EST_PROD_IMP.TP_EST = 2
	GROUP BY 
		EST_PROD_IMP.CD_PROD 

	UPDATE  
		#RS_MOV   
	SET
	   QT_IT = RS.QT_IT + ISNULL(RS_SOMA.QT_IT,0)
	FROM 
		#RS_MOV RS, 
		#RS_SOMA RS_SOMA
	WHERE   
		RS_SOMA.CD_PROD = RS.CD_PROD

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

	DELETE FROM #RS_SUB
	INSERT INTO #RS_SUB 
	SELECT    	
		EST_NF_SAI_IT.CD_PROD, 
		SUM(EST_NF_SAI_IT.QT_IT) AS QT_IT
	FROM    
		EST_NF_SAI INNER JOIN EST_NF_SAI_IT ON 
		EST_NF_SAI.CD_EMP = EST_NF_SAI_IT.CD_EMP AND 
		EST_NF_SAI.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL AND 
		EST_NF_SAI.CD_NF = EST_NF_SAI_IT.CD_NF 
		INNER JOIN GLB_MOV_OP ON 
		EST_NF_SAI.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP
	WHERE   
		EST_NF_SAI.DT_EMI_NF BETWEEN @DT_INI AND @DT_FIM
		AND EST_NF_SAI.CD_EMP = @CD_EMP 
		AND EST_NF_SAI.CD_FILIAL = @CD_FILIAL
		AND GLB_MOV_OP.BX_EST = 1
		AND GLB_MOV_OP.TP_ESTQ=2 
		AND EST_NF_SAI.STS_NF = 0
	GROUP BY EST_NF_SAI_IT.CD_PROD
	
	UPDATE  
		 #RS_MOV   
	SET
	   QT_IT = RS.QT_IT - ISNULL(RS_SUB.QT_IT,0)
	FROM 
		#RS_MOV RS, 
		#RS_SUB RS_SUB
	WHERE   
		RS_SUB.CD_PROD = RS.CD_PROD
end
--xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

IF  @TP_EST = 3 --Estoque de terceiros
begin
	DELETE FROM #RS_SOMA
	INSERT INTO #RS_SOMA 
	SELECT DISTINCT 
		EST_NF_ENT_IT.CD_PROD, 
		SUM(EST_NF_ENT_IT.QT_IT) AS QT_IT
	FROM    
		EST_NF_ENT INNER JOIN GLB_MOV_OP ON 
		EST_NF_ENT.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP 
		INNER JOIN EST_NF_ENT_IT ON 
		EST_NF_ENT.CD_EMP = EST_NF_ENT_IT.CD_EMP AND 
		EST_NF_ENT.CD_FILIAL = EST_NF_ENT_IT.CD_FILIAL AND 
		EST_NF_ENT.CD_NF = EST_NF_ENT_IT.CD_NF
	WHERE   
		EST_NF_ENT.CD_EMP = @CD_EMP 
		AND EST_NF_ENT.CD_FILIAL = @CD_FILIAL
		AND EST_NF_ENT.DT_ENT BETWEEN @DT_INI AND @DT_FIM
		AND GLB_MOV_OP.ATU_EST = 1
		AND GLB_MOV_OP.TP_ESTQ=3
		AND EST_NF_ENT.STS_NF = 0 
	GROUP BY 
		EST_NF_ENT_IT.CD_PROD

	UPDATE  
		#RS_MOV   
	SET
	   QT_IT = RS.QT_IT + ISNULL(RS_SOMA.QT_IT,0)
	FROM 
		#RS_MOV RS, 
		#RS_SOMA RS_SOMA
	WHERE   
		RS_SOMA.CD_PROD = RS.CD_PROD
-----------------------------------------------------------------------------
	DELETE FROM #RS_SOMA
	INSERT INTO #RS_SOMA
	 
	SELECT 
		EST_PROD_IMP.CD_PROD ,  
		SUM(EST_PROD_IMP.QT_IMP) 
	FROM 
		EST_PROD_IMP 
	WHERE     
		EST_PROD_IMP.CD_EMP = @CD_EMP 
		AND EST_PROD_IMP.CD_FILIAL = @CD_FILIAL 
		AND EST_PROD_IMP.DT_CAD BETWEEN @DT_INI AND @DT_FIM
		AND EST_PROD_IMP.TP_EST = 3
	GROUP BY 
		EST_PROD_IMP.CD_PROD 

	UPDATE  
		#RS_MOV   
	SET
	   QT_IT = RS.QT_IT + ISNULL(RS_SOMA.QT_IT,0)
	FROM 
		#RS_MOV RS, 
		#RS_SOMA RS_SOMA
	WHERE   
		RS_SOMA.CD_PROD = RS.CD_PROD
-----------------------------------------------------------------------------

	DELETE FROM #RS_SUB
	INSERT INTO #RS_SUB 
	SELECT    	
		EST_NF_SAI_IT.CD_PROD, 
		SUM(EST_NF_SAI_IT.QT_IT) AS QT_IT
	FROM    
		EST_NF_SAI INNER JOIN EST_NF_SAI_IT ON 
		EST_NF_SAI.CD_EMP = EST_NF_SAI_IT.CD_EMP AND 
		EST_NF_SAI.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL AND 
		EST_NF_SAI.CD_NF = EST_NF_SAI_IT.CD_NF 
		INNER JOIN GLB_MOV_OP ON 
		EST_NF_SAI.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP
	WHERE   
		EST_NF_SAI.DT_EMI_NF BETWEEN @DT_INI AND @DT_FIM
		AND EST_NF_SAI.CD_EMP = @CD_EMP 
		AND EST_NF_SAI.CD_FILIAL = @CD_FILIAL
		AND GLB_MOV_OP.BX_EST = 1
		AND GLB_MOV_OP.TP_ESTQ=3 
		AND EST_NF_SAI.STS_NF = 0
	GROUP BY EST_NF_SAI_IT.CD_PROD
	
	UPDATE  
		 #RS_MOV   
	SET
	   QT_IT = RS.QT_IT - ISNULL(RS_SUB.QT_IT,0)
	FROM 
		#RS_MOV RS, 
		#RS_SUB RS_SUB
	WHERE   
		RS_SUB.CD_PROD = RS.CD_PROD
end

IF  @TP_EST = 0 --Estoque de comercializacao
begin
	DECLARE @CD_PROD_PROC INT
	DECLARE @QT_IT_PROC   MONEY
	
	DECLARE estoque CURSOR FAST_FORWARD FOR 
									SELECT 
										RS.CD_PROD, 
										RS.QT_IT  
									FROM 
										EST_PROD_CPL CPL INNER JOIN #RS_MOV RS ON
										CPL.CD_PROD = RS.CD_PROD  AND
										CPL.CD_EMP  = @CD_EMP AND
										CPL.CD_FILIAL = @CD_FILIAL
									WHERE 
										CPL.QT_EST <> RS.QT_IT
	OPEN estoque
	FETCH NEXT FROM estoque INTO @CD_PROD_PROC,@QT_IT_PROC
		WHILE @@FETCH_STATUS=0
		  begin
			UPDATE
				EST_PROD_CPL
			SET 
				QT_EST = @QT_IT_PROC
			WHERE
				CD_EMP = @CD_EMP
				AND CD_FILIAL = @CD_FILIAL
				AND CD_PROD = @CD_PROD_PROC
			FETCH NEXT FROM estoque INTO @CD_PROD_PROC,@QT_IT_PROC
		  end
	CLOSE estoque
	DEALLOCATE estoque
		
	--Se tiver balanco em aberto
	--Refazer o estoque congelado
	IF @FLAG_BALAN_ABERTO = 1
	begin
		UPDATE
			EST_PROD_BALAN_IT
		SET
			EST_PROD_BALAN_IT.QT_IT = EST_PROD_CPL.QT_EST
		FROM
			EST_PROD_BALAN_IT INNER JOIN EST_PROD_CPL ON
			EST_PROD_BALAN_IT.CD_EMP = EST_PROD_CPL.CD_EMP AND
			EST_PROD_BALAN_IT.CD_FILIAL = EST_PROD_CPL.CD_FILIAL AND
			EST_PROD_BALAN_IT.CD_PROD = EST_PROD_CPL.CD_PROD 
			INNER JOIN EST_PROD_BALAN ON
			EST_PROD_BALAN.CD_EMP = EST_PROD_BALAN_IT.CD_EMP AND
			EST_PROD_BALAN.CD_FILIAL = EST_PROD_BALAN_IT.CD_FILIAL  AND
			EST_PROD_BALAN.CD_BALAN = EST_PROD_BALAN_IT.CD_BALAN 
		WHERE
			EST_PROD_BALAN.CD_EMP = @CD_EMP
			AND EST_PROD_BALAN.CD_FILIAL = @CD_FILIAL 
			AND EST_PROD_BALAN.STS_BALAN <> 2
			AND EST_PROD_BALAN.TP_EST = @TP_EST
			AND EST_PROD_BALAN_IT.QT_IT <> EST_PROD_CPL.QT_EST
			
		--Indica no balanco que foi alterado o estoque
		--e que e necessaria a reabertura da tela 
		--para buscar o novo valor
		UPDATE
			EST_PROD_BALAN
		SET
			FLAG_ALTEROU_ESTOQUE_ACERTO = 1
		FROM
			EST_PROD_BALAN 
		WHERE
			EST_PROD_BALAN.CD_EMP = @CD_EMP
			AND EST_PROD_BALAN.CD_FILIAL = @CD_FILIAL 
			AND EST_PROD_BALAN.STS_BALAN <> 2
			AND EST_PROD_BALAN.TP_EST = @TP_EST
	end
end


IF  @TP_EST = 1 --Estoque flutuante
begin
	UPDATE 
		CPL 
	SET 
		CPL.QT_EST_FLUT = RS.QT_IT
	FROM 
		EST_PROD_CPL CPL INNER JOIN #RS_MOV RS ON
		CPL.CD_PROD = RS.CD_PROD  AND
		CPL.CD_EMP  = @CD_EMP AND
		CPL.CD_FILIAL = @CD_FILIAL
	WHERE 
		CPL.QT_EST_FLUT <> RS.QT_IT
end

IF  @TP_EST = 2 --Estoque em terceiros
begin
	UPDATE 
		CPL 
	SET 
		CPL.QT_EST_EM_TERC = RS.QT_IT
	FROM 
		EST_PROD_CPL CPL INNER JOIN #RS_MOV RS ON
		CPL.CD_PROD = RS.CD_PROD  AND
		CPL.CD_EMP  = @CD_EMP AND
		CPL.CD_FILIAL = @CD_FILIAL
	WHERE 
		CPL.QT_EST_EM_TERC <> RS.QT_IT
end

IF  @TP_EST = 3 --Estoque de terceiros
begin
	UPDATE 
		CPL 
	SET 
		CPL.QT_EST_DE_TERC = RS.QT_IT
	FROM 
		EST_PROD_CPL CPL INNER JOIN #RS_MOV RS ON
		CPL.CD_PROD = RS.CD_PROD  AND
		CPL.CD_EMP  = @CD_EMP AND
		CPL.CD_FILIAL = @CD_FILIAL
	WHERE 
		CPL.QT_EST_DE_TERC <> RS.QT_IT
end

IF @FLAG_DEBUG = 1 
begin
	SELECT
		CPL.CD_PROD,
		CPL.QT_EST ,
		RS.QT_IT
	FROM 
		EST_PROD_CPL CPL INNER JOIN #RS_MOV RS ON
		CPL.CD_PROD = RS.CD_PROD  AND
		CPL.CD_EMP  = @CD_EMP AND
		CPL.CD_FILIAL = @CD_FILIAL
		AND CPL.CD_PROD = CASE WHEN @CD_PROD =0 THEN CPL.CD_PROD ELSE @CD_PROD END
	ORDER BY
		RS.CD_PROD
		
	SELECT
		*
	FROM
		#RS_MOV_AUDIT		
	WHERE
		CD_PROD = CASE WHEN @CD_PROD =0 THEN CD_PROD ELSE @CD_PROD END
end
else
begin
	DELETE 
	FROM 
		EST_PROD_BALAN_EVENTOS 
	WHERE 
		CD_EMP=@CD_EMP
		AND CD_FILIAL=@CD_FILIAL 
		AND DT_CAD>=@DT_FIM
		AND CD_EVENTO=2

	INSERT INTO EST_PROD_BALAN_EVENTOS
	(CD_EMP,
	CD_FILIAL,
	DT_CAD,
	CD_EVENTO,
	DS_EVENTO,
	CD_USU,
	DT_REGISTRO)
	SELECT
		@CD_EMP,
		@CD_FILIAL,
		@DT_FIM,
		2,
		'ACERTO DE ESTOQUE PELA MOVIMENTACAO DE PRODUTOS.',
		@CD_USU,
		GETDATE()
end

