IF OBJECT_ID('P_EXP_CTB_MOV_PG_CRED_BANCARIO') IS NOT NULL
	BEGIN
		DROP PROCEDURE P_EXP_CTB_MOV_PG_CRED_BANCARIO
	END
 
GO 
CREATE PROCEDURE P_EXP_CTB_MOV_PG_CRED_BANCARIO(@CD_EMP			INT,
									@CD_FILIAL		INT,
									@DT_INI			DATETIME,
									@DT_FIM			DATETIME,
									@CD_BC_LANC		INT,
									@CODIGO_INTERNO VARCHAR(50) NULL)	
WITH ENCRYPTION  AS 
SET NOCOUNT ON;
DECLARE @RS_CD_EMP					INT
DECLARE @RS_CD_FILIAL				INT
DECLARE @RS_CD_FORN					INT
DECLARE @RS_HIST					VARCHAR(500)
DECLARE @RS_CD_CNT					INT
DECLARE @RS_NR_CHQ					INT
DECLARE @RS_SERIE_CHQ				VARCHAR(5)
DECLARE @RS_ANO_CHQ					INT
DECLARE @RS_CD_PG_DEB_LOTE			INT
DECLARE @RS_VLR_EMIT_DEST			MONEY
DECLARE @RS_VL_JUR					MONEY
DECLARE @RS_VL_DESC					MONEY
DECLARE @RS_VL_IMP					MONEY
DECLARE @RS_VLR_DESP_OUTR			MONEY
DECLARE @RS_VLR_DEVOLUCAO			MONEY
DECLARE @RS_RZ_FORN					VARCHAR(200)
DECLARE @RS_NR_TIT					VARCHAR(50)	
DECLARE @RS_NR_NF					INT 
DECLARE @RS_DT_PAG					DATETIME
DECLARE @RS_VLR_CONTA				MONEY
DECLARE @RS_QTDE_LANC				INT
	
DECLARE @RET_CD_CTB_CNT				VARCHAR(100)
DECLARE @RET_CD_CTB_CNT_RED			INT
DECLARE @RET_DS_CNT					VARCHAR(100)
DECLARE @RET_NR_CNT					VARCHAR(50)	
DECLARE @RET_TITULAR_CNT			VARCHAR(200)
DECLARE @RET_TP_CNT					INT
DECLARE @RS_NR_DP					VARCHAR(50)
DECLARE @RS_TIPO_PAGTO              VARCHAR(20)
DECLARE @NUM_DUPL                   VARCHAR(20)
DECLARE @NUMERODUPL                 VARCHAR(20)
DECLARE @NR_DOCUMENTO               VARCHAR (50) 
DECLARE @RET_CD_BC					INT
DECLARE @HIST						VARCHAR(200)	
--#####################################################################
--CURSOR CONTAS BANCARIAS
--#####################################################################	
DECLARE RS_RESULT_BANCARIO
	CURSOR FOR 
		SELECT 	
			CD_EMP,
			@CD_FILIAL AS CD_FILIAL,
			CD_FILIAL,
			CD_CNT,
			NR_CHQ,
			SERIE_CHQ,
			ANO_CHQ,
			CD_PG_DEB_LOTE,
			ROUND(SUM(VL_PGTO - (VL_DESC + VL_IMP + VLR_DEVOLUCAO)),2) AS VLR_CONTA,
			ROUND(SUM(VL_JUR), 2) 			AS VL_JUR,
			ROUND(SUM(VL_DESC), 2) 			AS VL_DESC,
			0								AS VLR_DESP_OUTR,
			ROUND(SUM(VLR_DEVOLUCAO),2)  	AS VLR_DEVOLUCAO,
			COUNT(1) 						AS QTDE_LANC,
			MAX(RZ_FORN) AS RZ_FORN,
			MAX(NR_TIT) AS NR_TIT,
			DT_PAG,
			MAX(NR_DP) NR_DP,
			CD_PG_DEB_LOTE,
			TIPO_PAGTO
		FROM ##RS_RETURN
		WHERE
			CD_BC_LANC = @CD_BC_LANC
		GROUP BY 
			CD_EMP,	
			CD_FILIAL,
			CD_CNT,
			NR_CHQ,
			SERIE_CHQ,
			ANO_CHQ,
			CD_PG_DEB_LOTE,
			DT_PAG,
			CD_PG_DEB_LOTE,
			TIPO_PAGTO
				
				
--Abrindo cursor
OPEN RS_RESULT_BANCARIO
	
--Atribuindo valores do select nas variáveis
FETCH NEXT FROM RS_RESULT_BANCARIO INTO 
    @RS_CD_EMP,
	@CD_FILIAL,
    @RS_CD_FILIAL,
    @RS_CD_CNT,
    @RS_NR_CHQ,
    @RS_SERIE_CHQ,
    @RS_ANO_CHQ,
    @RS_CD_PG_DEB_LOTE,			
    @RS_VLR_CONTA,
    @RS_VL_JUR,
    @RS_VL_DESC,
    @RS_VLR_DESP_OUTR,
    @RS_VLR_DEVOLUCAO,
    @RS_QTDE_LANC,
    @RS_RZ_FORN,
    @RS_NR_TIT,
    @RS_DT_PAG,
    @RS_NR_DP,
    @RS_CD_PG_DEB_LOTE,
    @RS_TIPO_PAGTO,
	@RET_CD_BC
		
		
--Iniciando laço
WHILE @@FETCH_STATUS = 0
	BEGIN	
		SET @CODIGO_INTERNO = CONVERT(VARCHAR, @CD_BC_LANC) + ' - ' + CONVERT(VARCHAR, @RS_CD_FILIAL)
		
		SELECT 							
			@RET_CD_CTB_CNT			= CD_CTB_CNT,
			@RET_CD_CTB_CNT_RED		= CD_CTB_CNT_RED,
			@RET_DS_CNT				= DS_CNT,
			@RET_NR_CNT				= NR_CNT,
			@RET_TP_CNT				= TP_CNT,
			@RET_TITULAR_CNT		= TITULAR_CNT,
			@RET_CD_BC				= CD_BC
		FROM UFD_BUSCA_CONTA_BC_CONTAS(@CD_EMP, @RS_CD_CNT)
			
		IF @RS_QTDE_LANC > 1 
			BEGIN
				SET @HIST = 'PAGTO FORNECEDORES DIVERSOS - CONF. '
			END
		ELSE
			BEGIN
				SET @HIST = 'PAGTO FORNECEDOR: ' + @RS_RZ_FORN + ' - CONF. '
			END
			
		IF @RS_NR_CHQ > 0 
			BEGIN
				SET @HIST = @HIST + ' CHEQUE: ' + CAST(@RS_NR_CHQ AS VARCHAR) + ' SERIE: ' + @RS_SERIE_CHQ + ' ANO: ' + CAST(@RS_ANO_CHQ AS VARCHAR)
			END
				
		IF @RS_CD_PG_DEB_LOTE > 0 
			BEGIN
				SET @HIST = @HIST + ' LOTE PGTO: ' + CAST(@RS_CD_PG_DEB_LOTE AS VARCHAR)
			END
		ELSE
			BEGIN
				SET @HIST = @HIST + ' DOC: ' + CAST(@RS_NR_TIT AS VARCHAR)
			END
			
		IF UPPER(@RET_NR_CNT) = UPPER(@RET_DS_CNT) 
			BEGIN
				SET @HIST = @HIST + ' DO ' + CAST(@RET_DS_CNT AS VARCHAR)
			END
		ELSE
			BEGIN
				SET @HIST = @HIST + ' DO ' + CAST(@RET_DS_CNT AS VARCHAR) + ' - ' + CAST(@RET_NR_CNT AS VARCHAR)
			END
								
		SET @HIST = REPLACE (@HIST,'	',' ')
		
		
		 IF @RS_TIPO_PAGTO = 'LOTE'  
				    BEGIN 
					   SET	@NUM_DUPL  =  @RS_CD_PG_DEB_LOTE
					   SET   @NR_DOCUMENTO =  @RS_NR_TIT 
				    END
		  ELSE
			 BEGIN
				SET @NUMERODUPL =  RIGHT('0000'+ CAST(@RS_CD_FILIAL AS VARCHAR(4)),4) + CAST(@RS_NR_DP AS VARCHAR )
				SET @NUM_DUPL  =  @NUMERODUPL
			 END

			 IF @RS_NR_CHQ > 0
				
				BEGIN
					SET	@NUM_DUPL  =  @CD_BC_LANC
				    SET @NR_DOCUMENTO =  @CD_BC_LANC   
				END
			 ELSE
				BEGIN
				    SET   @NR_DOCUMENTO =  @RS_NR_TIT
				END
			



		INSERT INTO ##CTB_EXP_MOV  
		
				(CD_EMP, CD_FILIAL_EXPORT,CD_FILIAL,  CD_MOVIMENTO, MOVIMENTO, OPERACAO, OPERACAO_DEBITO,
				CD_CNT_RED_CREDITO, CD_CNT_CREDITO, CD_CNT_RED_DEBITO, CD_CNT_DEBITO, DT_MOV, HIST,
				VLR_LANC, TP_TRANSA, RATEIO_CENTRO_CUSTO, DS_CENTRO_CUSTO, CNT_CENTRO_CUSTO, VLR_CENTRO_CUSTO,
				ORIG_CONTA, COD_CONTA,NM_PROCEDURE,TIPO_LANCTO, CD_EVENTO, NR_DP, ANO, NR_DOC, CD_BC_LANC, COD_INTERNO_DOMINIO, CD_BC)

			SELECT		@RS_CD_EMP										AS CD_EMP,
						@CD_FILIAL										AS CD_FILIAL_EXPORT,
						@RS_CD_FILIAL									AS CD_FILIAL,
						16												AS CD_MOVIMENTO,
						'PAGAMENTO FORNECEDOR'							AS MOVIMENTO,
						'PAGTO DUPL. - CREDITO'							AS OPERACAO,
						''												AS OPERACAO_DEBITO,
						@RET_CD_CTB_CNT_RED								AS CD_CNT_RED_CREDITO,
						@RET_CD_CTB_CNT									AS CD_CNT_CREDITO,																
						0												AS CD_CNT_RED_DEBITO,
						''												AS CD_CNT_DEBITO,
						@RS_DT_PAG										AS DT_MOV,
						@HIST 											AS HIST,								
						(@RS_VLR_CONTA + @RS_VL_JUR)					AS VLR_LANC,
						'C'												AS TP_TRANSA,
						0 												AS RATEIO_CENTRO_CUSTO,
						'' 												AS DS_CENTRO_CUSTO,
						0 												AS CNT_CENTRO_CUSTO,
						0 												AS VLR_CENTRO_CUSTO,
						'CNT BANC'									    AS ORIG_CONTA,
						@RS_CD_CNT 									    AS COD_CONTA,
						OBJECT_NAME(@@PROCID)							AS NM_PROCEDURE
						,'V' AS TIPO_LANCTO
						  , 0 AS CD_EVENTO
						  , @NUM_DUPL  AS NR_DP
						  , 0  AS ANO
						  , @NR_DOCUMENTO  AS NR_DOC
						  , 0 AS CD_BC_LANC
						  , @CODIGO_INTERNO
						  , @RET_CD_BC;
															
	/*		
		IF @RS_VL_DESC > 0
			BEGIN
				------------------------------------------------------------------------------------------------------------------
				--DESCONTOS OBTIDOS - CREDITO - Ctb_Cnt_Descontos_Obtidos
				------------------------------------------------------------------------------------------------------------------					
				SET @HIST = 'DESCONTOS OBTIDOS (' + UPPER(@RET_DS_CNT) + ' - ' + UPPER(@RET_NR_CNT) 
					
				IF @RS_NR_CHQ > 0 
					BEGIN
						SET @HIST = @HIST + ' CHEQUE: ' + CAST(@RS_NR_CHQ AS VARCHAR) + ' SERIE: ' + @RS_SERIE_CHQ + ' ANO: ' + CAST(@RS_ANO_CHQ AS VARCHAR)
					END
						
				IF @RS_CD_PG_DEB_LOTE > 0 
					BEGIN
						SET @HIST = @HIST + ' LOTE PGTO: ' + CAST(@RS_CD_PG_DEB_LOTE AS VARCHAR)
					END

					INSERT INTO ##CTB_EXP_MOV  
		
					   (CD_EMP, CD_FILIAL_EXPORT,CD_FILIAL,  CD_MOVIMENTO, MOVIMENTO, OPERACAO, OPERACAO_DEBITO,
					   CD_CNT_RED_CREDITO, CD_CNT_CREDITO, CD_CNT_RED_DEBITO, CD_CNT_DEBITO, DT_MOV, HIST,
					   VLR_LANC, TP_TRANSA, RATEIO_CENTRO_CUSTO, DS_CENTRO_CUSTO, CNT_CENTRO_CUSTO, VLR_CENTRO_CUSTO,
					   ORIG_CONTA, COD_CONTA,NM_PROCEDURE,TIPO_LANCTO, CD_EVENTO, NR_DP, ANO, NR_DOC, CD_BC_LANC)  

					SELECT		@RS_CD_EMP										AS CD_EMP,
								@CD_FILIAL										AS CD_FILIAL_EXPORT,
								@RS_CD_FILIAL									AS CD_FILIAL,
								16												AS CD_MOVIMENTO,
								'PAGAMENTO FORNECEDOR'							AS MOVIMENTO,
								'DESCONTOS OBTIDOS - CREDITO'					AS OPERACAO,
								''												AS OPERACAO_DEBITO,
								0												AS CD_CNT_RED_CREDITO,
								''												AS CD_CNT_CREDITO,																
								0												AS CD_CNT_RED_DEBITO,
								''												AS CD_CNT_DEBITO,
								@RS_DT_PAG										AS DT_MOV,
								@HIST 											AS HIST,								
								@RS_VL_DESC										AS VLR_LANC,
								'C'												AS TP_TRANSA,
								0 												AS RATEIO_CENTRO_CUSTO,
								'' 												AS DS_CENTRO_CUSTO,
								0 												AS CNT_CENTRO_CUSTO,
								0 												AS VLR_CENTRO_CUSTO,
								'FILIAIS'									    AS ORIG_CONTA,
								17		 									    AS COD_CONTA,
								OBJECT_NAME(@@PROCID)							AS NM_PROCEDURE

			END
		*/		
		--Atribuindo valores do select nas variáveis
		FETCH NEXT FROM RS_RESULT_BANCARIO INTO 
			 @RS_CD_EMP,
			 @CD_FILIAL,
			 @RS_CD_FILIAL,
			 @RS_CD_CNT,
			 @RS_NR_CHQ,
			 @RS_SERIE_CHQ,
			 @RS_ANO_CHQ,
			 @RS_CD_PG_DEB_LOTE,			
			 @RS_VLR_CONTA,
			 @RS_VL_JUR,
			 @RS_VL_DESC,
			 @RS_VLR_DESP_OUTR,
			 @RS_VLR_DEVOLUCAO,
			 @RS_QTDE_LANC,
			 @RS_RZ_FORN,
			 @RS_NR_TIT,
			 @RS_DT_PAG,
			 @RS_NR_DP,
			 @RS_CD_PG_DEB_LOTE,
			 @RS_TIPO_PAGTO,
			 @RET_CD_BC
										   
	END
					
CLOSE RS_RESULT_BANCARIO
DEALLOCATE RS_RESULT_BANCARIO	