IF (OBJECT_ID('P_UFD_EXP_CTB_MOV_PG_CRED')) IS NOT NULL 
    BEGIN
	DROP procedure P_UFD_EXP_CTB_MOV_PG_CRED
    END
  GO 
CREATE procedure P_UFD_EXP_CTB_MOV_PG_CRED(@CD_EMP INT,@CD_FILIAL INT,@DT_INI DATETIME,@DT_FIM DATETIME)
AS
/*
 declare 
 @cd_emp int =1,
 @cd_filial int = 1 ,
 @dt_ini datetime ='2017-01-01',
 @dt_fim datetime = '2017-01-31'
 
 EXEC P_UFD_EXP_CTB_MOV_PG_CRED 1,900,'20250401','20250401'

 */
CREATE TABLE #RS_RETURN (
		CD_EMP			INT, 
		CD_CNT			INT,
		CD_FILIAL		INT,
		CD_FORN			INT,
		HIST			VARCHAR(500),
		NR_CHQ			INT,
		SERIE_CHQ		VARCHAR(50),
		ANO_CHQ			INT,
		CD_PG_DEB_LOTE	INT,
		VL_PGTO			MONEY,
		VL_JUR			MONEY,
		VL_DESC			MONEY,
		VL_IMP			MONEY,
		VLR_DESP_OUTR	MONEY,
		VLR_DEVOLUCAO	MONEY,
		RZ_FORN			VARCHAR(200), 
		NR_TIT			VARCHAR(100),
		NM_FANT			VARCHAR(200),
		NR_NF			INT,
		DT_PAG			DATETIME,
		CD_BC_LANC		INT,
		TIPO_PAGTO		VARCHAR(50),
		NR_DP           VARCHAR (50),
		NR_CNT          VARCHAR (50),
		DS_CNT          VARCHAR (150), 
		CD_BC           INT,
		DS_BC           VARCHAR (30))
		
BEGIN
		DECLARE @CD_GRP_ECON INT
	
	    SET @CD_GRP_ECON = (SELECT PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON
							FROM PRC_FILIAL
								INNER JOIN PRC_GRP_ECON_PRC_FILIAL ON
									PRC_FILIAL.CD_EMP = PRC_GRP_ECON_PRC_FILIAL.CD_EMP AND 
									PRC_FILIAL.CD_FILIAL = PRC_GRP_ECON_PRC_FILIAL.CD_FILIAL
							WHERE
							    PRC_FILIAL.CD_EMP 			= @CD_EMP
							    AND PRC_FILIAL.CD_FILIAL 	= @CD_FILIAL
							    AND PRC_FILIAL.FILIAL_MTZ_GRP_ECON = 1)
		
		IF ISNULL(@CD_GRP_ECON,0) = 0 
			BEGIN
				RETURN
			END

		INSERT INTO #RS_RETURN
		SELECT 
			PG_CRED.CD_EMP, 
			PG_DEB.CD_CNT,
			PG_DEB.CD_FILIAL,
			PG_CRED.CD_FORN,
			PG_DEB.HIST,
			0 AS NR_CHQ,
			' ' as SERIE_CHQ,
			0 as ANO_CHQ,
			ISNULL(PG_DEB_LOTE_PG_DEB.CD_PG_DEB_LOTE,0) AS CD_PG_DEB_LOTE,
			SUM(PG_DEB.VL_PGTO) AS VL_PGTO,
			SUM(PG_DEB.VL_JUR) AS VL_JUR,
			SUM(PG_DEB.VL_DESC) AS VL_DESC,
			SUM(PG_DEB.VL_IMP) As VL_IMP,
			SUM(PG_CRED.VLR_DESP_OUTR) As VLR_DESP_OUTR,
			SUM(PG_DEB.VLR_DEVOLUCAO) As VLR_DEVOLUCAO,
			PG_FORN.RZ_FORN, 
			PG_CRED.NR_TIT,
			PRC_FILIAL.NM_FANT,
			ISNULL(PG_DEB.CD_PG_CRED,0) AS NR_NF,
			PG_DEB.DT_PAG,
			ISNULL(PG_DEB_LOTE_BC_LANC.CD_BC_LANC,PG_DEB_BC_LANC.CD_BC_LANC) AS CD_BC_LANC,
			CASE WHEN ISNULL(PG_DEB_LOTE_BC_LANC.CD_BC_LANC,0)>0 THEN
				'LOTE'
			ELSE
				'NAO LOTE'
			END AS TIPO_PAGTO,
			PG_CRED.NR_DP,
            NR_CNT,
			DS_CNT, 
			BC_BANCO.CD_BC, 
			LEFT(DS_BC,15) DS_BC

		FROM PG_DEB 
			LEFT OUTER JOIN PG_DEB_LOTE_PG_DEB ON 
				PG_DEB.CD_EMP = PG_DEB_LOTE_PG_DEB.CD_EMP AND 
				PG_DEB.CD_FILIAL = PG_DEB_LOTE_PG_DEB.CD_FILIAL AND 
				PG_DEB.CD_PG_CRED = PG_DEB_LOTE_PG_DEB.CD_PG_CRED AND 
				PG_DEB.CD_DP_LANC = PG_DEB_LOTE_PG_DEB.CD_DP_LANC 
			LEFT OUTER JOIN PG_DEB_LOTE_BC_LANC ON 
				PG_DEB_LOTE_PG_DEB.CD_EMP = PG_DEB_LOTE_BC_LANC.CD_EMP AND 
				PG_DEB_LOTE_PG_DEB.CD_PG_DEB_LOTE = PG_DEB_LOTE_BC_LANC.CD_PG_DEB_LOTE 
			
			LEFT OUTER JOIN PG_DEB_BC_LANC ON 
				PG_DEB.CD_EMP = PG_DEB_BC_LANC.CD_EMP AND 
				PG_DEB.CD_FILIAL = PG_DEB_BC_LANC.CD_FILIAL AND 
				PG_DEB.CD_PG_CRED = PG_DEB_BC_LANC.CD_PG_CRED AND 
				PG_DEB.CD_DP_LANC = PG_DEB_BC_LANC.CD_DP_LANC 
			INNER JOIN PG_CRED ON 
				PG_DEB.CD_EMP = PG_CRED.CD_EMP AND
				PG_DEB.CD_FILIAL = PG_CRED.CD_FILIAL AND
				PG_DEB.CD_PG_CRED = PG_CRED.CD_PG_CRED

			INNER JOIN PG_FORN ON 
				PG_CRED.CD_EMP = PG_FORN.CD_EMP AND
				PG_CRED.CD_FORN = PG_FORN.CD_FORN

			INNER JOIN PRC_FILIAL ON
				PG_DEB.CD_EMP = PRC_FILIAL.CD_EMP AND
				PG_DEB.CD_FILIAL = PRC_FILIAL.CD_FILIAL

			LEFT JOIN PG_DEB_EST_NF_SAI ON
				PG_DEB.CD_EMP = PG_DEB_EST_NF_SAI.CD_EMP AND
				PG_DEB.CD_FILIAL = PG_DEB_EST_NF_SAI.CD_FILIAL AND
				PG_DEB.CD_PG_CRED = PG_DEB_EST_NF_SAI.CD_PG_CRED AND
				PG_DEB.CD_DP_LANC = PG_DEB_EST_NF_SAI.CD_DP_LANC

			LEFT JOIN BC_CHQ_LANC_CPL ON
				PG_DEB.CD_EMP = BC_CHQ_LANC_CPL.CD_EMP AND 
				PG_DEB.CD_FILIAL = BC_CHQ_LANC_CPL.CD_FILIAL AND
				PG_DEB.CD_PG_CRED = BC_CHQ_LANC_CPL.CD_PG_CRED AND
				PG_DEB.CD_DP_LANC = BC_CHQ_LANC_CPL.CD_DP_LANC
            INNER JOIN BC_CONTAS ON 
			    PG_DEB.CD_EMP =  BC_CONTAS.CD_EMP
			    AND PG_DEB.CD_CNT =  BC_CONTAS.CD_CNT
			INNER JOIN BC_BANCO ON 
			    BC_CONTAS.CD_EMP = BC_BANCO.CD_EMP 
			AND BC_CONTAS.CD_BC = BC_BANCO.CD_BC 

		WHERE 
			PG_DEB.CD_EMP 						= @CD_EMP
			AND PG_DEB.DT_PAG 					BETWEEN @DT_INI AND @DT_FIM
			AND PG_DEB.CD_FILIAL 				IN	   (SELECT CD_FILIAL 
														FROM PRC_GRP_ECON_PRC_FILIAL
														WHERE CD_GRP_ECON = @CD_GRP_ECON)			
			AND PG_FORN.IGNORAR_EXP_CONTABIL 	= 0
			AND PG_DEB_EST_NF_SAI.CD_EMP 		IS NULL
			AND BC_CHQ_LANC_CPL.CD_EMP 			IS NULL
		 
		GROUP BY 
			PG_DEB.CD_CNT, 
			PG_DEB_LOTE_PG_DEB.CD_PG_DEB_LOTE, 
			PG_CRED.CD_FORN, PG_DEB.HIST,
			PG_FORN.RZ_FORN, PG_CRED.NR_TIT, 
			PG_CRED.CD_EMP, PG_DEB.CD_FILIAL, 
			PRC_FILIAL.NM_FANT,
			PG_DEB.CD_PG_CRED, PG_DEB.DT_PAG,
			PG_DEB_LOTE_BC_LANC.CD_BC_LANC,PG_DEB_BC_LANC.CD_BC_LANC,
			PG_CRED.NR_DP 
            ,NR_CNT,DS_CNT, BC_BANCO.CD_BC, DS_BC
			
		INSERT INTO #RS_RETURN 
    
		SELECT 
			PG_CRED.CD_EMP,
			PG_DEB.CD_CNT,
			PG_DEB.CD_FILIAL,
			PG_CRED.CD_FORN,
			PG_DEB.HIST, 
			BC_CHQ_LANC.NR_CHQ,
			BC_CHQ_LANC.SERIE_CHQ,
			BC_CHQ_LANC.ANO_CHQ,
			0,
			SUM(PG_DEB.VL_PGTO) AS VL_PGTO,
			SUM(PG_DEB.VL_JUR) AS VL_JUR,
			SUM(PG_DEB.VL_DESC) AS VL_DESC,
			SUM(PG_DEB.VL_IMP) AS VL_IMP, 
			SUM(PG_CRED.VLR_DESP_OUTR) As VLR_DESP_OUTR,
			SUM(PG_DEB.VLR_DEVOLUCAO) As VLR_DEVOLUCAO,
			PG_FORN.RZ_FORN,
			PG_CRED.NR_TIT,
			PRC_FILIAL.NM_FANT,	
			ISNULL(PG_DEB.CD_PG_CRED,0) AS NR_NF ,
			PG_DEB.DT_PAG,
			ISNULL(BC_LANC_BC_CHQ_LANC.CD_BC_LANC,0) AS CD_BC_LANC,
			'CHEQUE EMPRESA' AS TIPO_PAGTO,
			PG_CRED.NR_DP
			,NR_CNT,DS_CNT, BC_BANCO.CD_BC, LEFT(DS_BC,15) DS_BC
		FROM 
			PG_CRED 
			INNER JOIN PG_FORN ON 
				PG_CRED.CD_EMP = PG_FORN.CD_EMP AND
				PG_CRED.CD_FORN = PG_FORN.CD_FORN

			INNER JOIN PG_DEB ON
				PG_CRED.CD_EMP = PG_DEB.CD_EMP AND 
				PG_CRED.CD_FILIAL = PG_DEB.CD_FILIAL AND 
				PG_DEB.CD_PG_CRED = PG_CRED.CD_PG_CRED

			LEFT OUTER JOIN BC_CHQ_LANC_CPL ON 
				BC_CHQ_LANC_CPL.CD_EMP = PG_DEB.CD_EMP AND 
				BC_CHQ_LANC_CPL.CD_FILIAL = PG_DEB.CD_FILIAL AND 
				BC_CHQ_LANC_CPL.CD_PG_CRED = PG_DEB.CD_PG_CRED AND 
				BC_CHQ_LANC_CPL.CD_DP_LANC = PG_DEB.CD_DP_LANC 

			LEFT OUTER JOIN BC_CHQ_LANC ON 
				BC_CHQ_LANC.CD_EMP = BC_CHQ_LANC_CPL.CD_EMP AND 
				BC_CHQ_LANC.CD_CHQ_LANC = BC_CHQ_LANC_CPL.CD_CHQ_LANC 

			LEFT OUTER JOIN BC_LANC_BC_CHQ_LANC ON 
				BC_CHQ_LANC.CD_EMP = BC_LANC_BC_CHQ_LANC.CD_EMP AND 
				BC_CHQ_LANC.CD_CHQ_LANC = BC_LANC_BC_CHQ_LANC.CD_CHQ_LANC 

			INNER JOIN PRC_FILIAL ON
				PG_DEB.CD_EMP = PRC_FILIAL.CD_EMP AND
				PG_DEB.CD_FILIAL = PRC_FILIAL.CD_FILIAL
           
		   INNER JOIN BC_CONTAS ON 
			   PG_DEB.CD_EMP =  BC_CONTAS.CD_EMP
			   AND PG_DEB.CD_CNT =  BC_CONTAS.CD_CNT
			INNER JOIN BC_BANCO ON 
			   BC_CONTAS.CD_EMP = BC_BANCO.CD_EMP 
			AND BC_CONTAS.CD_BC = BC_BANCO.CD_BC 

		WHERE 
			PG_DEB.CD_EMP 		= @CD_EMP
			AND PG_DEB.DT_PAG 	BETWEEN @DT_INI AND @DT_FIM
			AND PG_DEB.CD_CNT 	IN (SELECT CD_CNT FROM BC_CONTAS_PRC_GRP_ECON WHERE CD_EMP = @CD_EMP AND CD_GRP_ECON = @CD_GRP_ECON) 
			AND PG_DEB.CD_DOC 	= 2 
			AND PG_FORN.IGNORAR_EXP_CONTABIL = 0

		GROUP BY 
			PG_CRED.CD_EMP, PG_CRED.CD_FORN, PG_DEB.CD_CNT, 
			PG_DEB.HIST, BC_CHQ_LANC.NR_CHQ, 
			BC_CHQ_LANC.SERIE_CHQ, BC_CHQ_LANC.ANO_CHQ,
			PG_FORN.RZ_FORN, PG_CRED.NR_TIT,
			PG_DEB.CD_FILIAL, PRC_FILIAL.NM_FANT, 
			PG_DEB.CD_PG_CRED, PG_DEB.DT_PAG,
			BC_LANC_BC_CHQ_LANC.CD_BC_LANC,
			PG_CRED.NR_DP
		    ,NR_CNT,DS_CNT, BC_BANCO.CD_BC, DS_BC 
		
END
SELECT * FROM #RS_RETURN	