IF OBJECT_ID('P_EXP_CTB_MOV_CHQ_REDEPOS') IS NOT NULL
    BEGIN
        DROP PROCEDURE P_EXP_CTB_MOV_CHQ_REDEPOS;
    END;
GO
--exec P_EXP_CTB_MOV_CHQ_REDEPOS 1,1,'20170127','20170127',0
CREATE PROCEDURE P_EXP_CTB_MOV_CHQ_REDEPOS(@CD_EMP      INT,
                                           @CD_FILIAL   INT,
                                           @DT_INI      DATETIME,
                                           @DT_FIM      DATETIME,
                                           @FLAG_LIMPAR INT      = 0)
WITH ENCRYPTION
AS
    BEGIN TRY
SET NOCOUNT ON;	
        BEGIN
            DECLARE @RS_CD_EMP INT;
            DECLARE @RS_CD_FILIAL INT;
            DECLARE @RS_CD_CNT INT;
            DECLARE @RS_VALORCHEQUES MONEY;
            DECLARE @RS_NR_CHQ INT;
            DECLARE @RS_SR_CHQ VARCHAR(2);
            DECLARE @RS_ANO_CHQ INT;
            DECLARE @RS_CD_CHQ INT;
            DECLARE @RS_DT_REDEPOS DATETIME;
            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_TP_CNT INT;
            DECLARE @RET_TITULAR_CNT VARCHAR(200);
            DECLARE @HIST VARCHAR(200);
			DECLARE @CD_BC_LANC INT;
			DECLARE @NR_CONTROLE INT;
			DECLARE @GEROU INT ;
			DECLARE @CD_BC INT;

            ------------------------------------------------------------------------------------------------------------------------------
            --CRIANDO A TABELA TEMPORARIA ##CTB_EXP_MOV
            ------------------------------------------------------------------------------------------------------------------------------
            EXEC P_EXP_CTB_MOV_CRIAR_CTB_EXP_MOV
                 @FLAG_LIMPAR;
	
            --#####################################################################
            --CURSOR CHEQUES 
            --#####################################################################
            DECLARE RS_RESULT CURSOR
            FOR SELECT CD_EMP
                     , CD_FILIAL
                     , CD_CNT
                     , SUM(VALORCHEQUES) AS VALORCHEQUES
                     , NR_CHQ
                     , SR_CHQ
                     , ANO_CHQ
                     , CD_CHQ
                     , DT_REDEPOS
				 , CD_BC_LANC 
                FROM UFD_EXP_CTB_MOV_CHQ_REDEPOS(@CD_EMP, @CD_FILIAL, @DT_INI, @DT_FIM)
                GROUP BY CD_EMP
                       , CD_FILIAL
                       , CD_CNT
                       , NR_CHQ
                       , SR_CHQ
                       , ANO_CHQ
                       , CD_CHQ
                       , DT_REDEPOS
				   , CD_BC_LANC
                ORDER BY CD_EMP
                       , CD_FILIAL
                       , DT_REDEPOS;		
									
            --Abrindo cursor
            OPEN RS_RESULT;
		
            --Atribuindo valores do select nas variáveis
            FETCH NEXT FROM RS_RESULT INTO @RS_CD_EMP, @RS_CD_FILIAL, @RS_CD_CNT, @RS_VALORCHEQUES, @RS_NR_CHQ, @RS_SR_CHQ, @RS_ANO_CHQ, @RS_CD_CHQ, @RS_DT_REDEPOS,@CD_BC_LANC;			
		  SET @GEROU = 0			 

            --Iniciando laço
            WHILE @@FETCH_STATUS = 0
                BEGIN
                    IF @RS_VALORCHEQUES > 0
                        BEGIN
                            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
								 , @CD_BC = CD_BC
                            FROM UFD_BUSCA_CONTA_BC_CONTAS(@CD_EMP, @RS_CD_CNT);
                            SET @HIST = 'REDEP.CHEQUES: '+CAST(@RS_NR_CHQ AS VARCHAR)+' C3: '+CAST(@RS_SR_CHQ AS VARCHAR)+' Ano: '+CAST(@RS_ANO_CHQ AS VARCHAR)+' CONTA: '+@RET_DS_CNT+' - '+@RET_NR_CNT;
							SET @HIST = REPLACE (@HIST,'	',' ')
					   IF @GEROU = 0 
						  BEGIN 
							 SET @NR_CONTROLE = @CD_BC_LANC
							 SET @GEROU= 1 
					       END
                            ------------------------------------------------------------------------------------------------------------------
                            --REDEPOSITO CHEQUES - DEBITO -  CONTA BANCO
                            ------------------------------------------------------------------------------------------------------------------
                            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, CD_BC)

                                   SELECT @RS_CD_EMP AS CD_EMP
                                        , @CD_FILIAL AS CD_FILIAL_EXPORT
                                        , @RS_CD_FILIAL AS CD_FILIAL
                                        , 23 AS CD_MOVIMENTO
                                        , 'REDEPOSITO DE CHEQUE DEVOLVIDO' AS MOVIMENTO
                                        , '' AS OPERACAO
                                        , 'REDEPOSITO CHEQUES - DEBITO' AS OPERACAO_DEBITO
                                        , 0 AS CD_CNT_RED_CREDITO
                                        , '' AS CD_CNT_CREDITO
                                        , @RET_CD_CTB_CNT_RED AS CD_CNT_RED_DEBITO
                                        , @RET_CD_CTB_CNT AS CD_CNT_DEBITO
                                        , @RS_DT_REDEPOS AS DT_MOV
                                        , @HIST AS HIST
                                        , @RS_VALORCHEQUES AS VLR_LANC
                                        , 'D' 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
                                        , 'T' AS TIPO_LANCTO
                                        , 0 AS CD_EVENTO
										, 0  AS NR_DP
										, 0  AS ANO
										,''  AS NR_DOC
										, @NR_CONTROLE AS CD_BC_LANC
										, @CD_BC AS CD_BC;	
                        END;
					
                    ------------------------------------------------------------------------------------------------------------------
                    --Próximo registro				
                    ------------------------------------------------------------------------------------------------------------------
                    --Atribuindo valores do select nas variáveis
                    FETCH NEXT FROM RS_RESULT INTO @RS_CD_EMP, @RS_CD_FILIAL, @RS_CD_CNT, @RS_VALORCHEQUES, @RS_NR_CHQ, @RS_SR_CHQ, @RS_ANO_CHQ, @RS_CD_CHQ, @RS_DT_REDEPOS,@CD_BC_LANC;
                END;
            CLOSE RS_RESULT;
            DEALLOCATE RS_RESULT;	
					
            --#####################################################################
            --CURSOR VALOR TOTAL DE CHEQUES POR DATA
            --#####################################################################	
            DECLARE RS_RESULT CURSOR
            FOR SELECT SUM(VALORCHEQUES) AS VALORCHEQUES
                     , DT_REDEPOS
                FROM UFD_EXP_CTB_MOV_CHQ_REDEPOS(@CD_EMP, @CD_FILIAL, @DT_INI, @DT_FIM)
                GROUP BY DT_REDEPOS
                ORDER BY DT_REDEPOS;			
									
            --Abrindo cursor
            OPEN RS_RESULT;
		
            --Atribuindo valores do select nas variáveis
            FETCH NEXT FROM RS_RESULT INTO @RS_VALORCHEQUES, @RS_DT_REDEPOS;			
					 

            --Iniciando laço
            WHILE @@FETCH_STATUS = 0
                BEGIN
                    IF @RS_VALORCHEQUES > 0
                        BEGIN
                            SET @HIST = 'CHEQUES REDEPOSITADOS - TESOURARIA';
							SET @HIST = REPLACE (@HIST,'	',' ')
                            ------------------------------------------------------------------------------------------------------------------
                            --REDEPOSITO CHEQUES - CREDITO -  Ctb_Cnt_Cheques_Devolvidos_Tesouraria
                            ------------------------------------------------------------------------------------------------------------------
                            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
                                        , 23 AS CD_MOVIMENTO
                                        , 'REDEPOSITO DE CHEQUE DEVOLVIDO' AS MOVIMENTO
                                        , 'REDEPOSITO CHEQUES - 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_REDEPOS AS DT_MOV
                                        , @HIST AS HIST
                                        , @RS_VALORCHEQUES 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
                                        , 20 AS COD_CONTA
                                        , OBJECT_NAME(@@PROCID) AS NM_PROCEDURE
                                        , 'T' AS TIPO_LANCTO
                                        , 0 AS CD_EVENTO
										, 0  AS NR_DP
										, 0  AS ANO
										,''  AS NR_DOC
										, @NR_CONTROLE AS CD_BC_LANC;	
                        END;
					
                    ------------------------------------------------------------------------------------------------------------------
                    --Próximo registro				
                    ------------------------------------------------------------------------------------------------------------------
                    --Atribuindo valores do select nas variáveis
                    FETCH NEXT FROM RS_RESULT INTO @RS_VALORCHEQUES, @RS_DT_REDEPOS;
                END;
            CLOSE RS_RESULT;
            DEALLOCATE RS_RESULT;
        END;
    END TRY
    BEGIN CATCH
        CLOSE RS_RESULT;
        DEALLOCATE RS_RESULT;
        INSERT INTO ADM_ERRO
        (numero
       , severidade
       , estado
       , rotina
       , linha
       , mensagem
       , dtErro
        )
               SELECT ERROR_NUMBER() AS numero
                    , ERROR_SEVERITY() AS severidade
                    , ERROR_STATE() AS estado
                    , ERROR_PROCEDURE() AS rotina
                    , ERROR_LINE() AS linha
                    , ERROR_MESSAGE() AS mensagem
                    , GETDATE() AS dtErro;
    END CATCH;