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

--  P_EST_PROD_CPL_TRIB_ST_RET_EXTRATO 1 ,200 ,23036 ,'01/01/2014' ,'01/31/2015' 
--Versao 2765
CREATE PROCEDURE [dbo].[P_EST_PROD_CPL_TRIB_ST_RET_EXTRATO](@CD_EMP	INT,  
											   @CD_FILIAL	INT,  
											   @CD_PROD		INT,  
											   @DT_INI		DATETIME,  
											   @DT_FIM		DATETIME
											   ) AS
BEGIN  
SET NOCOUNT ON 
CREATE TABLE #RS_MOV (
						CD_NF					INT, 
						TP_MOV					INT,		 
						DT_MOV					DATETIME, 
						CD_MOV					INT,
						DS_MOV_CPL				VARCHAR(80), 
						DS_MOV					VARCHAR(150),		
						QT_IT					MONEY,
						VLR_BASE_ICMS_ST		MONEY,
						VLR_ICMS_ST				MONEY,
						DT_CAD					DATETIME)

CREATE TABLE #RS_EXTRATO 
		(ORDEM					INT IDENTITY (1, 1),
		CD_NF					INT, 
		TP_MOV					INT,		 
		DT_MOV					DATETIME, 
		CD_MOV					INT,
		DS_MOV_CPL				VARCHAR(80), 
		DS_MOV					VARCHAR(150),		
		QT_IT					MONEY,
		VLR_BASE_ICMS_ST		MONEY,
		VLR_ICMS_ST				MONEY,
		QT_IT_SLD				MONEY,
		VLR_BASE_ICMS_ST_SLD	MONEY,
		VLR_ICMS_ST_SLD			MONEY,
		DT_CAD					DATETIME
		)


DECLARE @QT_IT_SLD				MONEY
DECLARE @VLR_BASE_ICMS_ST_SLD	MONEY
DECLARE @VLR_ICMS_ST_SLD		MONEY

DECLARE @RS_ORDEM				INT
DECLARE @RS_TP_MOV				INT
DECLARE @RS_QT_IT				MONEY
DECLARE @RS_VLR_BASE_ICMS_ST	MONEY
DECLARE @RS_VLR_ICMS_ST			MONEY
  
INSERT INTO #RS_MOV  
SELECT       
	0 CD_NF,
	0 TP_MOV,
	cast(dateadd(DAY, -1, @DT_INI) AS DATETIME),   
	0 AS CD_MOV,  
	DS_MOV_CPL = 'POSICAO DO ESTOQUE',  
	DS_MOV = 'POSICAO ATE O DIA ' + CONVERT(VARCHAR(10), cast(dateadd(DAY, -1, @DT_INI) AS DATETIME),103) ,    
	SUM(ISNULL(T.QT_IT,0)) AS QT_IT,
	SUM(ISNULL(T.VLR_BASE_ICMS_ST,0)) AS VLR_BASE_ICMS_ST,
	SUM(ISNULL(T.VLR_ICMS_ST,0)) AS VLR_ICMS_ST,
	CAST(DATEADD(DAY, -1, @DT_INI) AS DATETIME) AS DT_CAD
FROM 
		(SELECT 
			SUM(QT_IT) AS QT_IT,
			SUM(VLR_BASE_ICMS_ST * ISNULL(QT_IT,0)) AS VLR_BASE_ICMS_ST,
			SUM(VLR_ICMS_ST * ISNULL(QT_IT,0)) AS VLR_ICMS_ST
		FROM EST_PROD_CPL_TRIB_ST_CRED	
		WHERE CD_EMP = @CD_EMP	
			AND CD_FILIAL = @CD_FILIAL
			AND CD_PROD = @CD_PROD
			AND DT_MOV < @DT_INI
		UNION ALL
		SELECT 
			SUM(QT_IT) * -1 AS QT_IT ,
			SUM(VLR_BASE_ICMS_ST * ISNULL(QT_IT,0)) * -1 AS VLR_BASE_ICMS_ST,
			SUM(VLR_ICMS_ST * ISNULL(QT_IT,0)) * -1 AS VLR_ICMS_ST
		FROM EST_PROD_CPL_TRIB_ST_DEB	
		WHERE CD_EMP = @CD_EMP	
			AND CD_FILIAL = @CD_FILIAL
			AND CD_PROD = @CD_PROD
			AND DT_MOV < @DT_INI) T
	
------------------------------------------------------------------------------------  
--Entrada de mercadoria por compras  
	INSERT INTO #RS_MOV  
	SELECT    
		V_EST_NF_ENT.CD_NF,
		0 TP_MOV,
		V_EST_NF_ENT.DT_ENT,   
		CD_MOV = V_EST_NF_ENT.CD_MOV_OP,  
		DS_MOV_CPL = V_EST_NF_ENT.DS_MOV_OP,  
		DS_OP = DS_MOV_OP + ' REF. NOTA FISCAL NR.: ' + CONVERT(VARCHAR(10),NR_NF) + '   CD. ORIGEM.: ' + CONVERT(VARCHAR(10),CD_ORIG),      
		ST_CRED.QT_IT,  
		ST_CRED.VLR_BASE_ICMS_ST * ST_CRED.QT_IT,
		ST_CRED.VLR_ICMS_ST * ST_CRED.QT_IT,
		ST_CRED.DT_CAD
	FROM      
		V_EST_NF_ENT  
		LEFT JOIN PDV_VD_EST_NF_ENT  ON
		V_EST_NF_ENT.CD_EMP		= PDV_VD_EST_NF_ENT.CD_EMP AND  
		V_EST_NF_ENT.CD_FILIAL	= PDV_VD_EST_NF_ENT.CD_FILIAL AND  
		V_EST_NF_ENT.CD_NF		= PDV_VD_EST_NF_ENT.CD_NF		
		INNER JOIN EST_NF_ENT_IT_EST_PROD_CPL_TRIB_ST_CRED NF_ENT_ST ON 
		V_EST_NF_ENT.CD_EMP		= NF_ENT_ST.CD_EMP AND  
		V_EST_NF_ENT.CD_FILIAL	= NF_ENT_ST.CD_FILIAL AND  
		V_EST_NF_ENT.CD_NF		= NF_ENT_ST.CD_NF AND
		V_EST_NF_ENT.CD_PROD	= NF_ENT_ST.CD_PROD
		INNER JOIN EST_PROD_CPL_TRIB_ST_CRED ST_CRED ON 
		NF_ENT_ST.CD_CTR = ST_CRED.CD_CTR
		
	WHERE     
		V_EST_NF_ENT.CD_EMP = @CD_EMP   
		AND V_EST_NF_ENT.CD_FILIAL = @CD_FILIAL  
		AND V_EST_NF_ENT.DT_ENT BETWEEN @DT_INI AND @DT_FIM  
		AND V_EST_NF_ENT.CD_PROD = @CD_PROD  
		AND V_EST_NF_ENT.ATU_EST = 1  
		AND V_EST_NF_ENT.TP_ESTQ = 0  
		AND V_EST_NF_ENT.STS_NF = 0  
		AND PDV_VD_EST_NF_ENT.CD_EMP IS NULL  
	order by 
		V_EST_NF_ENT.CD_NF

	INSERT INTO #RS_MOV  
	SELECT 
		0 CD_NF,
		0 TP_MOV,     
		EST_NF_ENT.DT_ENT,   
		CD_MOV = CD_MOV_OP,  
		DS_MOV_CPL = DS_MOV_OP,  
		DS_OP = DS_MOV_OP + ' REF. CUPOM NR.: ' + CONVERT(VARCHAR(10),PDV_VD.NR_ECF) + ' DATA VENDA.: ' + convert(varchar,PDV_VD.DT_VD,103),    
		ST_CRED.QT_IT,  
		ST_CRED.VLR_BASE_ICMS_ST * ST_CRED.QT_IT,
		ST_CRED.VLR_ICMS_ST * ST_CRED.QT_IT,
		ST_CRED.DT_CAD		
	FROM           
		PDV_VD_EST_NF_ENT 
		INNER JOIN EST_NF_ENT ON   
		PDV_VD_EST_NF_ENT.CD_EMP = EST_NF_ENT.CD_EMP AND   
		PDV_VD_EST_NF_ENT.CD_FILIAL = EST_NF_ENT.CD_FILIAL AND   
		PDV_VD_EST_NF_ENT.CD_NF = EST_NF_ENT.CD_NF   
		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   
		INNER JOIN GLB_MOV_OP ON   
		EST_NF_ENT.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP   
		INNER JOIN PDV_VD ON   
		PDV_VD_EST_NF_ENT.CD_EMP = PDV_VD.CD_EMP AND   
		PDV_VD_EST_NF_ENT.CD_FILIAL = PDV_VD.CD_FILIAL AND   
		PDV_VD_EST_NF_ENT.CD_VD = PDV_VD.CD_VD   
		INNER JOIN EST_NF_ENT_IT_EST_PROD_CPL_TRIB_ST_CRED NF_ENT_ST ON 
		EST_NF_ENT_IT.CD_EMP	= NF_ENT_ST.CD_EMP AND  
		EST_NF_ENT_IT.CD_FILIAL	= NF_ENT_ST.CD_FILIAL AND  
		EST_NF_ENT_IT.CD_NF		= NF_ENT_ST.CD_NF AND
		EST_NF_ENT_IT.CD_PROD	= NF_ENT_ST.CD_PROD
		INNER JOIN EST_PROD_CPL_TRIB_ST_CRED ST_CRED ON 
		NF_ENT_ST.CD_CTR = ST_CRED.CD_CTR		
	WHERE       
		EST_NF_ENT.CD_EMP = @CD_EMP   
		AND EST_NF_ENT.CD_FILIAL = @CD_FILIAL  
		AND EST_NF_ENT_IT.CD_PROD = @CD_PROD  
		AND EST_NF_ENT.DT_ENT BETWEEN @DT_INI AND @DT_FIM  
		AND EST_NF_ENT.STS_NF = 0  
  
  
 --Vendas por nota fiscal  
	INSERT INTO #RS_MOV  
	SELECT   
		0 CD_NF,
		1 TP_MOV,     
		DT_EMI_NF,   
		CD_MOV = CD_MOV_OP,  
		DS_MOV_CPL = DS_MOV_OP,  
		DS_OP = DS_MOV_OP + ' REF. NOTA FISCAL NR.: ' + CONVERT(VARCHAR(10),NF_NF) + '  CD. DEST.: ' + CONVERT(VARCHAR(10),CD_CLI),  
		ST_DEB.QT_IT,  
		ST_DEB.VLR_BASE_ICMS_ST * ST_DEB.QT_IT,
		ST_DEB.VLR_ICMS_ST * ST_DEB.QT_IT,
		ST_DEB.DT_CAD
	FROM      
		V_EST_NF_SAI  
		INNER JOIN EST_NF_SAI_IT_EST_PROD_CPL_TRIB_ST_DEB NF_SAI_ST ON 
		V_EST_NF_SAI.CD_EMP		= NF_SAI_ST.CD_EMP AND  
		V_EST_NF_SAI.CD_FILIAL	= NF_SAI_ST.CD_FILIAL AND  
		V_EST_NF_SAI.CD_NF		= NF_SAI_ST.CD_NF AND
		V_EST_NF_SAI.CD_PROD	= NF_SAI_ST.CD_PROD AND
		V_EST_NF_SAI.CD_IT		= NF_SAI_ST.CD_IT
		INNER JOIN EST_PROD_CPL_TRIB_ST_DEB ST_DEB ON 
		NF_SAI_ST.CD_CTR = ST_DEB.CD_CTR
	WHERE     
		V_EST_NF_SAI.DT_EMI_NF BETWEEN @DT_INI AND @DT_FIM  
		AND V_EST_NF_SAI.CD_EMP = @CD_EMP   
		AND V_EST_NF_SAI.CD_FILIAL = @CD_FILIAL  
		AND V_EST_NF_SAI.CD_PROD = @CD_PROD  
		AND V_EST_NF_SAI.BX_EST = 1  
		AND V_EST_NF_SAI.TP_ESTQ = 0  
		AND V_EST_NF_SAI.STS_NF = 0  
		AND NOT EXISTS (SELECT   
							PDV_VD_EST_NF_SAI.CD_NF   
						FROM   
							PDV_VD_EST_NF_SAI  
						WHERE  
							V_EST_NF_SAI.CD_EMP = PDV_VD_EST_NF_SAI.CD_EMP AND  
							V_EST_NF_SAI.CD_FILIAL = PDV_VD_EST_NF_SAI.CD_FILIAL AND  
							V_EST_NF_SAI.CD_NF = PDV_VD_EST_NF_SAI.CD_NF AND  
							V_EST_NF_SAI.CD_EMP = @CD_EMP  
							AND V_EST_NF_SAI.CD_FILIAL = @CD_FILIAL)  
  ORDER BY
	V_EST_NF_SAI.DT_EMI_NF

 
	--xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  
	INSERT INTO #RS_EXTRATO (CD_NF, TP_MOV, DT_MOV,CD_MOV,DS_MOV_CPL,DS_MOV,
							 QT_IT, VLR_BASE_ICMS_ST, VLR_ICMS_ST, QT_IT_SLD, VLR_BASE_ICMS_ST_SLD, VLR_ICMS_ST_SLD, DT_CAD)  
	SELECT 
		CD_NF, TP_MOV, DT_MOV,CD_MOV,DS_MOV_CPL,DS_MOV,
		QT_IT, VLR_BASE_ICMS_ST, VLR_ICMS_ST, 0 AS QT_IT_SLD, 0 AS VLR_BASE_ICMS_ST_SLD, 0 AS VLR_ICMS_ST_SLD, DT_CAD
	FROM 
		#RS_MOV 
	ORDER BY 
		DT_MOV,
		DT_CAD,
		TP_MOV		
	
	SET @QT_IT_SLD = 0  
	SET @VLR_BASE_ICMS_ST_SLD = 0
	SET @VLR_ICMS_ST_SLD = 0

	DECLARE CalcEstoque CURSOR FAST_FORWARD FOR 
	SELECT 
		ORDEM, TP_MOV, QT_IT, VLR_BASE_ICMS_ST, VLR_ICMS_ST  
	FROM 
		#RS_EXTRATO
	ORDER BY 
		ORDEM
				
	OPEN CalcEstoque
	
	FETCH NEXT FROM CalcEstoque INTO @RS_ORDEM, @RS_TP_MOV, @RS_QT_IT, @RS_VLR_BASE_ICMS_ST, @RS_VLR_ICMS_ST
	WHILE @@FETCH_STATUS=0
		Begin
			SET @QT_IT_SLD = CASE WHEN @RS_TP_MOV IN (0) THEN  
								ISNULL((@QT_IT_SLD + @RS_QT_IT),0)  
							 WHEN @RS_TP_MOV = 1 THEN  
								ISNULL((@QT_IT_SLD - @RS_QT_IT),0)
							 END 

			SET @VLR_BASE_ICMS_ST_SLD = CASE WHEN @RS_TP_MOV IN (0) THEN  
								ISNULL((@VLR_BASE_ICMS_ST_SLD + @RS_VLR_BASE_ICMS_ST),0)  
							 WHEN @RS_TP_MOV = 1 THEN  
								ISNULL((@VLR_BASE_ICMS_ST_SLD - @RS_VLR_BASE_ICMS_ST),0)
							 END 

			SET @VLR_ICMS_ST_SLD = CASE WHEN @RS_TP_MOV IN (0) THEN  
								ISNULL((@VLR_ICMS_ST_SLD + @RS_VLR_ICMS_ST),0)  
							 WHEN @RS_TP_MOV = 1 THEN  
								ISNULL((@VLR_ICMS_ST_SLD - @RS_VLR_ICMS_ST),0)
							 END 

			UPDATE #RS_EXTRATO SET												 				
				QT_IT_SLD 				= @QT_IT_SLD,
				VLR_BASE_ICMS_ST_SLD 	= @VLR_BASE_ICMS_ST_SLD,  
				VLR_ICMS_ST_SLD 		= @VLR_ICMS_ST_SLD
			where
				ORDEM = @RS_ORDEM
			--Proximo registro
			FETCH NEXT FROM CalcEstoque INTO @RS_ORDEM, @RS_TP_MOV, @RS_QT_IT, @RS_VLR_BASE_ICMS_ST, @RS_VLR_ICMS_ST
		End

	--Fechando cursos
	CLOSE CalcEstoque;
	--Desalocando cursos da memoria
	DEALLOCATE CalcEstoque;

	SELECT 
		ORDEM,
		CD_NF,
		TP_MOV,
		DT_MOV,
		CD_MOV,
		DS_MOV_CPL,
		DS_MOV,
		QT_IT,
		VLR_BASE_ICMS_ST,
		VLR_ICMS_ST,
		QT_IT_SLD,
		VLR_BASE_ICMS_ST_SLD,
		VLR_ICMS_ST_SLD
	FROM 
		#RS_EXTRATO 
	ORDER BY
		ORDEM

RETURN  
	DROP TABLE #RS_MOV;
	DROP TABLE #RS_EXTRATO
END