/****** Object:  StoredProcedure [dbo].[P_PG_CRED_DEB_FORN_EXTRATO]    Script Date: 02/11/2014 18:34:05 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_PG_CRED_DEB_FORN_EXTRATO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_PG_CRED_DEB_FORN_EXTRATO]
GO
--exec P_PG_CRED_DEB_FORN_EXTRATO 1,'2019-18-01','2019-10-01','12',1147
CREATE PROCEDURE [dbo].[P_PG_CRED_DEB_FORN_EXTRATO](  
				@CD_EMP				INT,
				@DT_INI				DATETIME,
				@DT_FIM				DATETIME,
				@CD_FILIAL			VARCHAR(MAX),
				@CD_FORN_PESQ       INT=0)    
  
AS  
BEGIN  
--Desabilitando a qtde de registros afetados a cada instrução para que a procedure retorne dados para o Visual Basic  
SET NOCOUNT ON  

CREATE TABLE #RS_MOV (
				ORDEM INT IDENTITY(1,1),
				CD_EMP INT,
				CD_FORN INT, 
				RZ_FORN VARCHAR(50), 
				CD_LANC INT, 
				VLR_CRED MONEY,
				VLR_DEB MONEY, 
				VLR_SLD MONEY,
				TIPO INT, 
				HIST_LANC VARCHAR(200),
				DT_LANCTO DATETIME,
				CD_FIL_NF   INT,
				CD_NF   INT,
				REGISTRO VARCHAR(50),
				CONCILIADO INT,
				NR_NF VARCHAR(30))
				
				
CREATE TABLE #FORN_LANC_EXTRATO (												
												ORDEM               INT,
												CD_EMP				INT,				
												CD_FORN				INT,
												RZ_FORN			    VARCHAR(50),
												CD_LANC  		    INT,
												VLR_CRED  			MONEY, 
                                                VLR_DEB  			MONEY, 
                                                VLR_SLD             MONEY,
												TIPO                INT,
												HIST_LANC 			VARCHAR(200), 
												DT_LANCTO           DATETIME,
												CD_FIL_NF           INT,
												CD_NF               INT,
												REGISTRO            VARCHAR(50),
												CONCILIADO INT,
												NR_NF VARCHAR(30))				
				
				
DECLARE @VLR_SLD    MONEY
DECLARE @CD_FORN    INT
			
--tabela temporaria com os fornecedores que serão processados				
CREATE TABLE #RsPdv (CD_FORN INT)
INSERT INTO #RsPdv
SELECT 
	CD_FORN 
FROM 
	PG_FORN		
WHERE
	CD_FORN = CASE 
			WHEN 
				@CD_FORN_PESQ > 0 
			THEN 
				@CD_FORN_PESQ 
			ELSE 
				CD_FORN 
			END	
	
DECLARE Fornecedores CURSOR FAST_FORWARD FOR SELECT CD_FORN FROM #RsPdv
OPEN Fornecedores
FETCH NEXT FROM Fornecedores INTO @CD_FORN
WHILE @@FETCH_STATUS=0
	Begin				
----INICIO PROCESSO GERAL
		INSERT INTO #RS_MOV 
		SELECT     
			V_PG_CRED_FORN_CREDITO_DEBITO.CD_EMP,
			V_PG_CRED_FORN_CREDITO_DEBITO.CD_FORN, 
			LEFT(PG_FORN.RZ_FORN,50) AS RZ_FORN,
			0 AS LANC, 
			CASE WHEN (SUM(VLR_CRED) - SUM(VLR_DEB)) > 0 THEN 
				SUM(VLR_CRED) - SUM(VLR_DEB)
			ELSE
				0
			END  AS VLR_CRED , 
			CASE WHEN (SUM(VLR_CRED) - SUM(VLR_DEB)) < 0 THEN 
				SUM(VLR_CRED) - SUM(VLR_DEB)
			ELSE
				0
			END  AS VLR_DEB, 
			0 AS VLR_SLD, 
			CASE WHEN (SUM(VLR_CRED) - SUM(VLR_DEB)) > 0 THEN 
				0
			ELSE
				1
			END  AS TIPO,
			'SALDO INICIAL'  AS HIST_LANC, 
			@DT_INI -1 AS DT_LANCTO,
			0 AS CD_FIL_NF,
			0 CD_NF,
			'SALDO INICIAL' AS REGISTRO,
			1 CONCILIADO,
			'' AS NR_NF
		FROM       
			V_PG_CRED_FORN_CREDITO_DEBITO  INNER JOIN PG_FORN ON  
			V_PG_CRED_FORN_CREDITO_DEBITO.CD_EMP = PG_FORN.CD_EMP AND 
			V_PG_CRED_FORN_CREDITO_DEBITO.CD_FORN = PG_FORN.CD_FORN 
		WHERE      
			V_PG_CRED_FORN_CREDITO_DEBITO.CD_EMP = @CD_EMP 
			AND V_PG_CRED_FORN_CREDITO_DEBITO.CD_FORN = @CD_FORN
			AND V_PG_CRED_FORN_CREDITO_DEBITO.DT_CAD <  @DT_INI 
		GROUP BY
			V_PG_CRED_FORN_CREDITO_DEBITO.CD_EMP,
			V_PG_CRED_FORN_CREDITO_DEBITO.CD_FORN, 
			PG_FORN.RZ_FORN

		UNION ALL

		SELECT     
			PG_CRED_FORN.CD_EMP,
			PG_FORN.CD_FORN, 
			LEFT(PG_FORN.RZ_FORN,50),
			PG_CRED_FORN.CD_CRED_FORN AS LANC, 
			PG_CRED_FORN.VLR_CRED AS VLR_CRED , 
			0 AS VLR_DEB, 
			0 AS VLR_SLD, 
			0 AS TIPO,
			'Credito por dev. da filial.: ' + CONVERT(VARCHAR(5),EST_NF_SAI.CD_FILIAL)  + ' nota fiscal  nr.: ' + CONVERT(VARCHAR(20),EST_NF_SAI.NF_NF) + ' emitida em ' + CONVERT(CHAR(10),EST_NF_SAI.DT_EMI_NF,105) AS HIST_LANC, 
			PG_CRED_FORN.DT_CRED AS DT_LANCTO,
			PG_CRED_FORN_EST_NF_SAI.CD_FILIAL AS CD_FIL_NF,
			PG_CRED_FORN_EST_NF_SAI.CD_NF,
			'1 - CREDITO VINCULO COM NOTAS' AS REGISTRO,
			ISNULL(PG_CRED_FORN.CONCILIADO,0),
			EST_NF_SAI.NF_NF
		FROM       
			PG_CRED_FORN  INNER JOIN PG_FORN ON  
			PG_CRED_FORN.CD_EMP = PG_FORN.CD_EMP AND 
			PG_CRED_FORN.CD_FORN = PG_FORN.CD_FORN 
			LEFT OUTER JOIN PG_CRED_FORN_EST_NF_SAI ON
			PG_CRED_FORN.CD_EMP = PG_CRED_FORN_EST_NF_SAI.CD_EMP AND
			PG_CRED_FORN.CD_CRED_FORN = PG_CRED_FORN_EST_NF_SAI.CD_CRED_FORN
			LEFT OUTER JOIN EST_NF_SAI ON
			PG_CRED_FORN_EST_NF_SAI.CD_EMP = EST_NF_SAI.CD_EMP AND
			PG_CRED_FORN_EST_NF_SAI.CD_FILIAL = EST_NF_SAI.CD_FILIAL AND
			PG_CRED_FORN_EST_NF_SAI.CD_NF = EST_NF_SAI.CD_NF					
		WHERE      
			PG_CRED_FORN.CD_EMP = @CD_EMP 
			AND PG_CRED_FORN.CD_FORN = @CD_FORN
			AND PG_CRED_FORN_EST_NF_SAI.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
			AND PG_CRED_FORN.VLR_CRED > 0
			AND PG_CRED_FORN.DT_CRED BETWEEN  @DT_INI AND @DT_FIM 

		UNION ALL

		SELECT     
			PG_DEB_FORN.CD_EMP,
			PG_FORN.CD_FORN, 
			LEFT(PG_FORN.RZ_FORN,50),
			PG_DEB_FORN.CD_DEB_FORN AS LANC, 
			0 AS VLR_CRED, 
			CASE WHEN ISNULL(PG_DEB_FORN_EST_NF_SAI.VLR_BAIXADO,0) > 0 THEN
				ISNULL(PG_DEB_FORN_EST_NF_SAI.VLR_BAIXADO,0)
			ELSE
				PG_DEB_FORN.VLR_DEB
			END AS VLR_DEB,
			0 AS VLR_SLD, 
			1 AS TIPO, --900-2011-1100012-1)  select * from  PG_DEB_FORN_PG_CRED
			'Debito por abatimento da duplicata.: ' +  CONVERT(VARCHAR(5),ISNULL(PG_DEB_FORN_PG_CRED.CD_FILIAL,''))  + '-' + CONVERT(VARCHAR(20),ISNULL(PG_CRED.ANO ,'')) + '-' + CONVERT(VARCHAR(20),ISNULL(PG_CRED.NR_DP,''))  + '-' + CONVERT(VARCHAR(20),ISNULL(PG_CRED.PARC ,'')) AS HIST_LANC, 
			PG_DEB_FORN.DT_DEB AS DT_LANCTO,
			PG_CRED.CD_FILIAL AS CD_FIL_NF,
			PG_CRED.CD_PG_CRED AS CD_NF,
			'2 - DEBITO VINCULO COM DUPLICATA' AS REGISTRO,
			ISNULL(PG_DEB_FORN.CONCILIADO,0),
			ISNULL(EST_NF_SAI.NF_NF,'') AS NF_NF
		FROM       
			PG_DEB_FORN INNER JOIN PG_FORN ON 
			PG_DEB_FORN.CD_EMP = PG_FORN.CD_EMP AND 
			PG_DEB_FORN.CD_FORN = PG_FORN.CD_FORN 
			INNER JOIN PG_DEB_FORN_PG_CRED ON
			PG_DEB_FORN.CD_EMP = PG_DEB_FORN_PG_CRED.CD_EMP AND
			PG_DEB_FORN.CD_DEB_FORN = PG_DEB_FORN_PG_CRED.CD_DEB_FORN
			INNER JOIN PG_CRED ON
			PG_CRED.CD_EMP = PG_DEB_FORN_PG_CRED.CD_EMP AND
			PG_CRED.CD_FILIAL = PG_DEB_FORN_PG_CRED.CD_FILIAL AND
			PG_CRED.CD_PG_CRED  = PG_DEB_FORN_PG_CRED.CD_PG_CRED 	
		    LEFT OUTER JOIN PG_DEB_FORN_EST_NF_SAI ON 
			PG_DEB_FORN.CD_EMP = PG_DEB_FORN_EST_NF_SAI.CD_EMP AND
			PG_DEB_FORN.CD_DEB_FORN = PG_DEB_FORN_EST_NF_SAI.CD_DEB_FORN
			LEFT OUTER JOIN EST_NF_SAI ON 
			PG_DEB_FORN_EST_NF_SAI.CD_EMP = EST_NF_SAI.CD_EMP AND 
			PG_DEB_FORN_EST_NF_SAI.CD_FILIAL = EST_NF_SAI.CD_FILIAL AND 
			PG_DEB_FORN_EST_NF_SAI.CD_NF = EST_NF_SAI.CD_NF 
		WHERE      
			PG_DEB_FORN.CD_EMP = @CD_EMP 
			AND PG_DEB_FORN.CD_FORN = @CD_FORN   
			AND PG_DEB_FORN_PG_CRED.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))	   
			AND PG_DEB_FORN.DT_DEB BETWEEN  @DT_INI AND @DT_FIM 

		UNION ALL

		SELECT     
			PG_CRED_FORN.CD_EMP,
			PG_FORN.CD_FORN, 
			LEFT(PG_FORN.RZ_FORN,50),
			PG_CRED_FORN.CD_CRED_FORN AS LANC, 
			PG_CRED_FORN.VLR_CRED AS VLR_CRED , 
			0 AS VLR_DEB, 
			0 AS VLR_SLD, 
			0 AS TIPO,
			CASE WHEN DS_HIST IS NULL THEN  'lancamento manual' ELSE DS_HIST END AS HIST_LANC, 
			PG_CRED_FORN.DT_CRED AS DT_LANCTO,
			0 AS CD_FIL_NF,
			0 AS CD_NF,
			'3 - CREDITO SEM VINCULO COM NOTAS' AS REGISTRO,
			ISNULL(PG_CRED_FORN.CONCILIADO,0),
			'' AS NF_NF
		FROM       
			PG_CRED_FORN INNER JOIN PG_FORN ON  
			PG_CRED_FORN.CD_EMP = PG_FORN.CD_EMP AND 
			PG_CRED_FORN.CD_FORN = PG_FORN.CD_FORN 
		WHERE      
			PG_CRED_FORN.CD_EMP = @CD_EMP 
			AND PG_CRED_FORN.CD_FORN = @CD_FORN
			AND NOT EXISTS (SELECT	
								CD_EMP 
							FROM
								PG_CRED_FORN_EST_NF_SAI 
							WHERE
								PG_CRED_FORN.CD_EMP = PG_CRED_FORN_EST_NF_SAI.CD_EMP AND
								PG_CRED_FORN.CD_CRED_FORN = PG_CRED_FORN_EST_NF_SAI.CD_CRED_FORN)
			AND PG_CRED_FORN.VLR_CRED > 0
			AND PG_CRED_FORN.DT_CRED BETWEEN  @DT_INI AND @DT_FIM 
		
		UNION ALL

		SELECT     
			PG_DEB_FORN.CD_EMP,
			PG_FORN.CD_FORN, 
			LEFT(PG_FORN.RZ_FORN,50),
			PG_DEB_FORN.CD_DEB_FORN AS LANC, 
			0 AS VLR_CRED, 
			CASE WHEN ISNULL(PG_DEB_FORN_EST_NF_SAI.VLR_BAIXADO,0) > 0 THEN
				ISNULL(PG_DEB_FORN_EST_NF_SAI.VLR_BAIXADO,0) 
			ELSE
				PG_DEB_FORN.VLR_DEB
			END AS VLR_DEB,
			0 AS VLR_SLD, 
			1 AS TIPO,
			CASE WHEN DS_HIST IS NULL THEN  'lancamento manual' ELSE DS_HIST END AS HIST_LANC, 
			PG_DEB_FORN.DT_DEB AS DT_LANCTO,
			0 AS CD_FIL_NF,
			0 AS CD_NF,
			'4 - DEBITO SEM VINCULO COM NOTAS' AS REGISTRO, 
			ISNULL(PG_DEB_FORN.CONCILIADO,0),
			ISNULL(EST_NF_SAI.NF_NF,'') AS NF_NF			
		FROM       
			PG_DEB_FORN INNER JOIN PG_FORN ON 
			PG_DEB_FORN.CD_EMP = PG_FORN.CD_EMP AND 
			PG_DEB_FORN.CD_FORN = PG_FORN.CD_FORN 
		    LEFT OUTER JOIN PG_DEB_FORN_EST_NF_SAI ON 
			PG_DEB_FORN.CD_EMP = PG_DEB_FORN_EST_NF_SAI.CD_EMP AND
			PG_DEB_FORN.CD_DEB_FORN = PG_DEB_FORN_EST_NF_SAI.CD_DEB_FORN
			LEFT OUTER JOIN EST_NF_SAI ON 
			PG_DEB_FORN_EST_NF_SAI.CD_EMP = EST_NF_SAI.CD_EMP AND 
			PG_DEB_FORN_EST_NF_SAI.CD_FILIAL = EST_NF_SAI.CD_FILIAL AND 
			PG_DEB_FORN_EST_NF_SAI.CD_NF = EST_NF_SAI.CD_NF 			
		WHERE      
			PG_DEB_FORN.CD_EMP = @CD_EMP 
			AND PG_DEB_FORN.CD_FORN = @CD_FORN   
			AND PG_DEB_FORN.DT_DEB BETWEEN  @DT_INI AND @DT_FIM 
			AND NOT EXISTS (SELECT	
								CD_EMP 
							FROM
								PG_DEB_FORN_EST_NF_ENT 
							WHERE
								PG_DEB_FORN.CD_EMP = PG_DEB_FORN_EST_NF_ENT.CD_EMP AND
								PG_DEB_FORN.CD_DEB_FORN = PG_DEB_FORN_EST_NF_ENT.CD_DEB_FORN) 
								
			AND NOT EXISTS (SELECT	
								CD_EMP 
							FROM
								PG_DEB_FORN_PG_CRED 
							WHERE
								PG_DEB_FORN.CD_EMP = PG_DEB_FORN_PG_CRED.CD_EMP AND
								PG_DEB_FORN.CD_DEB_FORN = PG_DEB_FORN_PG_CRED.CD_DEB_FORN)       	       
		UNION ALL

		SELECT     
			PG_DEB_FORN.CD_EMP,
			PG_FORN.CD_FORN, 
			LEFT(PG_FORN.RZ_FORN,50),
			PG_DEB_FORN.CD_DEB_FORN AS LANC, 
			0 AS VLR_CRED, 
			PG_DEB_FORN.VLR_DEB,
			0 AS VLR_SLD, 
			1 AS TIPO,
			CASE WHEN DS_HIST IS NULL THEN  'lancamento manual' ELSE DS_HIST END AS HIST_LANC, 
			PG_DEB_FORN.DT_DEB AS DT_LANCTO,
			0 AS CD_FIL_NF,
			0 AS CD_NF,
			'4 - DEBITO POR ABATIMENTO NOTA FISCAL ENTRADA' AS REGISTRO, 
			ISNULL(PG_DEB_FORN.CONCILIADO,0),
			ISNULL(EST_NF_SAI.NF_NF,'') AS NF_NF			
		FROM       
			PG_DEB_FORN INNER JOIN PG_FORN ON 
			PG_DEB_FORN.CD_EMP = PG_FORN.CD_EMP AND 
			PG_DEB_FORN.CD_FORN = PG_FORN.CD_FORN 
		    LEFT OUTER JOIN PG_DEB_FORN_EST_NF_SAI ON 
			PG_DEB_FORN.CD_EMP = PG_DEB_FORN_EST_NF_SAI.CD_EMP AND
			PG_DEB_FORN.CD_DEB_FORN = PG_DEB_FORN_EST_NF_SAI.CD_DEB_FORN
			LEFT OUTER JOIN EST_NF_SAI ON 
			PG_DEB_FORN_EST_NF_SAI.CD_EMP = EST_NF_SAI.CD_EMP AND 
			PG_DEB_FORN_EST_NF_SAI.CD_FILIAL = EST_NF_SAI.CD_FILIAL AND 
			PG_DEB_FORN_EST_NF_SAI.CD_NF = EST_NF_SAI.CD_NF 			
		WHERE      
			PG_DEB_FORN.CD_EMP = @CD_EMP 
			AND PG_DEB_FORN.CD_FORN = @CD_FORN   
			AND PG_DEB_FORN.DT_DEB BETWEEN  @DT_INI AND @DT_FIM 
			AND EXISTS (SELECT	
								CD_EMP 
							FROM
								PG_DEB_FORN_EST_NF_ENT 
							WHERE
								PG_DEB_FORN.CD_EMP = PG_DEB_FORN_EST_NF_ENT.CD_EMP AND
								PG_DEB_FORN.CD_DEB_FORN = PG_DEB_FORN_EST_NF_ENT.CD_DEB_FORN) 
								
			AND EXISTS (SELECT	
								CD_EMP 
							FROM
								PG_DEB_FORN_PG_CRED_FORN 
							WHERE
								PG_DEB_FORN.CD_EMP = PG_DEB_FORN_PG_CRED_FORN.CD_EMP AND
								PG_DEB_FORN.CD_DEB_FORN = PG_DEB_FORN_PG_CRED_FORN.CD_DEB_FORN) 

			AND NOT EXISTS (SELECT	
								CD_EMP 
							FROM
								PG_DEB_FORN_PG_CRED 
							WHERE
								PG_DEB_FORN.CD_EMP = PG_DEB_FORN_PG_CRED.CD_EMP AND
								PG_DEB_FORN.CD_DEB_FORN = PG_DEB_FORN_PG_CRED.CD_DEB_FORN)       	       
		ORDER BY  
			DT_LANCTO,
			TIPO

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

		SET @VLR_SLD = 0

		UPDATE #RS_MOV SET @VLR_SLD = (CASE WHEN TIPO = 0 THEN
							@VLR_SLD + VLR_CRED
						WHEN TIPO = 1 THEN
							@VLR_SLD - VLR_DEB
						END),
						VLR_SLD = @VLR_SLD 
						WHERE CD_FORN=@CD_FORN 				

		INSERT INTO #FORN_LANC_EXTRATO(ORDEM,CD_EMP, CD_FORN, RZ_FORN, CD_LANC, VLR_CRED, VLR_DEB, VLR_SLD, TIPO ,HIST_LANC,DT_LANCTO,CD_FIL_NF,CD_NF,REGISTRO,CONCILIADO,NR_NF)
		SELECT	
			ORDEM,	
			CD_EMP, 
			CD_FORN, 
			RZ_FORN, 
			CD_LANC, 
			VLR_CRED, 
			VLR_DEB,
			VLR_SLD ,
			TIPO,
			HIST_LANC, 
			DT_LANCTO,
			CD_FIL_NF,
			CD_NF,
			REGISTRO,
			CONCILIADO,
			NR_NF
		FROM		
			#RS_MOV 
		WHERE	
			CD_FORN=@CD_FORN
		ORDER BY 
			ORDEM

		TRUNCATE TABLE #RS_MOV;	  
----FIM PROCESSO GERAL
		FETCH NEXT FROM Fornecedores INTO @CD_FORN
	End	
	


--Fechando cursos
CLOSE Fornecedores;
--Desalocando cursos da memoria
DEALLOCATE Fornecedores;
--Apagando a tabela temporaria
DROP TABLE #RsPdv;	
				
select 
	ORDEM,
	CD_EMP,
	CD_FORN, 
	RZ_FORN, 
	CD_LANC, 
	VLR_CRED,
	CASE WHEN @CD_FORN_PESQ > 0 THEN VLR_DEB ELSE VLR_DEB * -1 END  as VLR_DEB, 
	VLR_SLD,
	TIPO, 
	UPPER(HIST_LANC) AS HIST_LANC,
	DT_LANCTO,
	CD_FIL_NF,
	CD_NF,
	REGISTRO,
	NR_NF
from 
	#FORN_LANC_EXTRATO				
order by
	DT_LANCTO
DROP TABLE #FORN_LANC_EXTRATO;
DROP TABLE #RS_MOV;	  
END 

