IF OBJECT_ID('P_EXP_CTB_MOV_CHQ_DEVO') IS NOT NULL
    BEGIN
        DROP PROCEDURE P_EXP_CTB_MOV_CHQ_DEVO;
    END;
GO
--exec P_EXP_CTB_MOV_CHQ_DEVO 1,1,'20170127','20170127',1
CREATE PROCEDURE P_EXP_CTB_MOV_CHQ_DEVO(@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_DT_DEVO DATETIME;
            DECLARE @RS_CD_CNT INT;
            DECLARE @RS_VLR_DEVO MONEY;
            DECLARE @RS_NR_CHQ INT;
            DECLARE @RS_SR_CHQ VARCHAR(2);
            DECLARE @RS_ANO_CHQ 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_TP_CNT INT;
            DECLARE @RET_TITULAR_CNT VARCHAR(200);
            DECLARE @HIST VARCHAR(200);
		  DECLARE @RS_CD_BC_LANC INT;
		  DECLARE @RS_CD_CNT_DEB INT;
		  DECLARE @RS_CD_CNT_CRED INT;
		  DECLARE @RS_CD_BC_LANC_CRED INT;
		  DECLARE @RS_CD_BC_LANC_DEB INT;
		  DECLARE @NR_DOC VARCHAR(15);

            ------------------------------------------------------------------------------------------------------------------------------
            --CRIANDO A TABELA TEMPORARIA ##CTB_EXP_MOV
            ------------------------------------------------------------------------------------------------------------------------------
            EXEC P_EXP_CTB_MOV_CRIAR_CTB_EXP_MOV
                 @FLAG_LIMPAR;
            DECLARE RS_RESULT CURSOR
            FOR SELECT CD_EMP
                     , CD_FILIAL
                     , DT_DEVO
                     , CD_CNT_CRED
				 , CD_CNT_DEB
                     , VLR_DEVO
                     , NR_CHQ
                     , SR_CHQ
                     , ANO_CHQ
				 ,CD_BC_LANC_CRED
				 ,CD_BC_LANC_DEB
               --SELECT * FROM UFD_EXP_CTB_MOV_CHQ_DEVO(1, 1, '20171002', '20171002')
			 FROM UFD_EXP_CTB_MOV_CHQ_DEVO(@CD_EMP, @CD_FILIAL, @DT_INI, @DT_FIM)
                ORDER BY CD_EMP
                       , CD_FILIAL
                       , DT_DEVO;		
									
            --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_DT_DEVO, 
		  @RS_CD_CNT_CRED,@RS_CD_CNT_DEB, @RS_VLR_DEVO, @RS_NR_CHQ, @RS_SR_CHQ, @RS_ANO_CHQ,@RS_CD_BC_LANC_CRED,@RS_CD_BC_LANC_DEB;				
					 

            --Iniciando laço
            WHILE @@FETCH_STATUS = 0
                BEGIN
                    IF @RS_VLR_DEVO > 0
                        BEGIN
				    SET @NR_DOC =  RIGHT('00000000'+  CONVERT (VARCHAR,@RS_NR_CHQ),8) + CONVERT (VARCHAR,@RS_SR_CHQ) +CONVERT (VARCHAR,@RS_ANO_CHQ)
                            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
                            FROM UFD_BUSCA_CONTA_BC_CONTAS(@CD_EMP, @RS_CD_CNT_DEB);
                            SET @HIST = 'DEVOLUCAO CHEQUE: '+CAST(@RS_NR_CHQ AS VARCHAR)+' - SERIE: '+CAST(@RS_SR_CHQ AS VARCHAR)+' ANO: '+CAST(@RS_ANO_CHQ AS VARCHAR)+' CONTA: '+@RET_DS_CNT+' - '+@RET_NR_CNT;
						SET @HIST = REPLACE (@HIST,'	',' ')
						  --SELECT * FROM UFD_BUSCA_CONTA_BC_CONTAS (1,2)
						  --SELECT * FROM UFD_BUSCA_CONTA_BC_CONTAS (1,105)
						  --SELECT * FROM UFD_EXP_CTB_MOV_CHQ_DEVO(1, 904, '20170602', '20170602')
                            ------------------------------------------------------------------------------------------------------------------
                            --RECEB.CHQ.COBRANÇA - DEBITO -  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
                                        , 27 AS CD_MOVIMENTO
                                        , 'DEVOLUCAO CHEQUE' AS MOVIMENTO
                                        , '' AS OPERACAO
                                        , 'DEVOLUCAO CHEQUE - DEBITO' 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_DEVO AS DT_MOV
                                        , @HIST AS HIST
                                        , @RS_VLR_DEVO 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
                                        , 'FILIAIS' AS ORIG_CONTA
                                        , 20 AS COD_CONTA
                                        , OBJECT_NAME(@@PROCID) AS NM_PROCEDURE
                                        , 'X' AS TIPO_LANCTO
                                        , 0 AS CD_EVENTO
										, 0  AS NR_DP
										, 0  AS ANO
										, @NR_DOC AS NR_DOC
										, @RS_CD_BC_LANC_DEB AS CD_BC_LANC;	

                            ------------------------------------------------------------------------------------------------------------------
                            --RECEB.CHQ.COBRANÇA - CREDITO -  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)

                                   SELECT @RS_CD_EMP AS CD_EMP
                                        , @CD_FILIAL AS CD_FILIAL_EXPORT
                                        , @RS_CD_FILIAL AS CD_FILIAL
                                        , 27 AS CD_MOVIMENTO
                                        , 'DEVOLUCAO CHEQUE' AS MOVIMENTO
                                        , 'DEVOLUCAO CHEQUE - 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_DEVO AS DT_MOV
                                        , @HIST AS HIST
                                        , @RS_VLR_DEVO 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_CRED AS COD_CONTA
                                        , OBJECT_NAME(@@PROCID) AS NM_PROCEDURE
                                        , 'X' AS TIPO_LANCTO
                                        , 0 AS CD_EVENTO
										, 0  AS NR_DP
										, 0  AS ANO
										, @NR_DOC AS NR_DOC
										, @RS_CD_BC_LANC_CRED AS CD_BC_LANC;	
                        END;								

                    ------------------------------------------------------------------------------------------------------------------
                    --Próximo registro				
                    ------------------------------------------------------------------------------------------------------------------
                    --Atribuindo valores do select nas variáveis
                     FETCH NEXT FROM RS_RESULT INTO @RS_CD_EMP, @RS_CD_FILIAL, @RS_DT_DEVO, 
				   @RS_CD_CNT_CRED,@RS_CD_CNT_DEB, @RS_VLR_DEVO, @RS_NR_CHQ, @RS_SR_CHQ, @RS_ANO_CHQ,@RS_CD_BC_LANC_CRED,@RS_CD_BC_LANC_DEB;	
                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;