IF OBJECT_ID('P_EXP_CTB_MOV_CHQ_DEPOS') IS NOT NULL
    BEGIN
        DROP PROCEDURE P_EXP_CTB_MOV_CHQ_DEPOS;
    END;
GO
--  exec P_EXP_CTB_MOV_CHQ_DEPOS 1,1,'20170127','20170127',0
CREATE PROCEDURE P_EXP_CTB_MOV_CHQ_DEPOS(@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_DEPOS DATETIME;
            DECLARE @RS_HIST_LANC VARCHAR(200);
            DECLARE @RS_VALORDEPOSITO MONEY;
            DECLARE @RS_CD_CNT INT;
            DECLARE @RS_CD_CNT_ORIG INT;
            DECLARE @RS_TP_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_DESPOS INT;
	
            ------------------------------------------------------------------------------------------------------------------------------
            --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_DEPOS
                     , HIST_LANC
                     , VALORDEPOSITO
                     , CD_CNT
                     , CD_CNT_ORIG
                     , TP_CHQ
				 , CD_DEPOS
			 FROM UFD_EXP_CTB_MOV_CHQ_DEPOS(@CD_EMP, @CD_FILIAL, @DT_INI, @DT_FIM)
			 --FROM UFD_EXP_CTB_MOV_CHQ_DEPOS(1, 1, '20170104', '20170104')
                ORDER BY CD_EMP
                       , CD_FILIAL
                       , DT_DEPOS;				
									
            --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_DEPOS, 
			 @RS_HIST_LANC, 
			 @RS_VALORDEPOSITO, 
			 @RS_CD_CNT, 
			 @RS_CD_CNT_ORIG, 
			 @RS_TP_CHQ,
			 @RS_CD_DESPOS;				
					 
            --Iniciando laço
            WHILE @@FETCH_STATUS = 0
                BEGIN
                    IF @RS_VALORDEPOSITO > 0
                        BEGIN
                            IF @RS_TP_CHQ = 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
                                    FROM UFD_BUSCA_CONTA_BC_CONTAS(@CD_EMP, @RS_CD_CNT);
                                    SET @HIST = 'DEPOSITO DE RECBTO CH. AVISTA: CONTA: '+@RET_DS_CNT+' - '+@RET_NR_CNT+' - '+@RS_HIST_LANC;
									SET @HIST = REPLACE (@HIST,'	',' ')
                                    ------------------------------------------------------------------------------------------------------------------
                                    --DEPOSITO 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)

                                           SELECT @RS_CD_EMP AS CD_EMP
								        , @RS_CD_FILIAL AS CD_FILIAL
                                                , @CD_FILIAL AS CD_FILIAL_EXPPORT
                                                , 22 AS CD_MOVIMENTO
                                                , 'DEPOSITO DE RECBTO CH. AVISTA' AS MOVIMENTO
                                                , '' AS OPERACAO
                                                , 'DEPOSITO CHEQUES' 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_DEPOS AS DT_MOV
                                                , @HIST AS HIST
                                                , @RS_VALORDEPOSITO 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
                                                , 'X' AS TIPO_LANCTO
                                                , 0 AS CD_EVENTO
												, 0  AS NR_DP
												, 0  AS ANO
												, @RS_CD_DESPOS AS NR_DOC
												, '' AS CD_BC_LANC;																											
						
		
                                    ------------------------------------------------------------------------------------------------------------------
                                    --MOVIMENTO BANCARIO - CREDITO -  Ctb_Cnt_Tranf_Dinheiro_Para_Matriz
                                    ------------------------------------------------------------------------------------------------------------------
                                    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
								        , @RS_CD_FILIAL AS CD_FILIAL
                                                , @CD_FILIAL AS CD_FILIAL_EXPPORT
                                                , 22 AS CD_MOVIMENTO
                                                , 'DEPOSITO DE RECBTO CH. AVISTA' AS MOVIMENTO
                                                , 'DEPOSITO CHEQUES' 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_DEPOS AS DT_MOV
                                                , @HIST AS HIST
                                                , @RS_VALORDEPOSITO 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
                                                , 'MATRIZ' AS ORIG_CONTA
                                                , 15 AS COD_CONTA
                                                , OBJECT_NAME(@@PROCID) AS NM_PROCEDURE
                                                , 'X' AS TIPO_LANCTO
                                                , 0 AS CD_EVENTO
												, 0  AS NR_DP
												, 0  AS ANO
												, @RS_CD_DESPOS AS NR_DOC
												, '' AS CD_BC_LANC;
                                END;
                            ELSE
                                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
                                    FROM UFD_BUSCA_CONTA_BC_CONTAS(@CD_EMP, @RS_CD_CNT);
                                    SET @HIST = 'DEPOSITO DE RECBTO CH. AVISTA: CONTA: '+@RET_DS_CNT+' - '+@RET_NR_CNT+' - '+@RS_HIST_LANC;
									SET @HIST = REPLACE (@HIST,'	',' ')
                                    ------------------------------------------------------------------------------------------------------------------
                                    --DEPOSITO CHEQUES - CREDITO -  CONTA BANCO (ORIGEM)
                                    ------------------------------------------------------------------------------------------------------------------
                                    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
								       , @RS_CD_FILIAL AS CD_FILIAL
                                                , @CD_FILIAL AS CD_FILIAL_EXPPORT
                                                , 22 AS CD_MOVIMENTO
                                                , 'DEPOSITO DE RECBTO CH. AVISTA' AS MOVIMENTO
                                                , '' AS OPERACAO
                                                , 'DEPOSITO CHEQUES' 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_DEPOS AS DT_MOV
                                                , @HIST AS HIST
                                                , @RS_VALORDEPOSITO 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
                                                , 'X' AS TIPO_LANCTO
                                                , 0 AS CD_EVENTO
												, 0  AS NR_DP
												, 0  AS ANO
												, @RS_CD_DESPOS AS NR_DOC
												, '' AS CD_BC_LANC;						
		
                                    ------------------------------------------------------------------------------------------------------------------
                                    --MOVIMENTO BANCARIO - CREDITO -  Ctb_Cnt_Tranf_Dinheiro_Para_Matriz
                                    ------------------------------------------------------------------------------------------------------------------

                                    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_ORIG);
                                   
							 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
								        , @RS_CD_FILIAL AS CD_FILIAL
                                                , @CD_FILIAL AS CD_FILIAL_EXPPORT
                                                , 22 AS CD_MOVIMENTO
                                                , 'DEPOSITO DE RECBTO CH. AVISTA' AS MOVIMENTO
                                                , 'DEPOSITO CHEQUES' 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_DEPOS AS DT_MOV
                                                , @HIST AS HIST
                                                , @RS_VALORDEPOSITO 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_ORIG AS COD_CONTA
                                                , OBJECT_NAME(@@PROCID) AS NM_PROCEDURE
                                                , 'X' AS TIPO_LANCTO
                                                , 0 AS CD_EVENTO
												, 0  AS NR_DP
												, 0  AS ANO
												, @RS_CD_DESPOS AS NR_DOC
												, '' AS CD_BC_LANC;
                                END;
                        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_DEPOS, 
				    @RS_HIST_LANC, 
				    @RS_VALORDEPOSITO, 
				    @RS_CD_CNT, 
				    @RS_CD_CNT_ORIG, 
				    @RS_TP_CHQ,
				    @RS_CD_DESPOS;
                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;