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 @RS_MOD_CHEQUE VARCHAR(20);
			DECLARE @RS_VLR_CREDITO MONEY;
			DECLARE @RS_LINHA 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
			DECLARE @CD_BC 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
					, MOD_CHEQUE
					, SUM(VLR_CHQ) AS VLR_CHQ
						 , ROW_NUMBER() OVER (
								PARTITION BY CD_DEPOS, CD_FILIAL, DT_DEPOS
								ORDER BY SUM(VLR_CHQ)  -- ou outro critério desejado
							) AS LINHA
			 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')
               GROUP BY CD_EMP
               , CD_FILIAL
               , DT_DEPOS
               , HIST_LANC
               , VALORDEPOSITO
               , CD_CNT
               , CD_CNT_ORIG
               , TP_CHQ
			   , CD_DEPOS
			   , MOD_CHEQUE
			   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,
			 @RS_MOD_CHEQUE,
			 @RS_VLR_CREDITO,
			 @RS_LINHA;				
					 
            --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
										 , @CD_BC = CD_BC
                                    FROM UFD_BUSCA_CONTA_BC_CONTAS(@CD_EMP, @RS_CD_CNT);
                                    SET @HIST = 'DEPOSITO DE RECBTO CH.'+ @RS_MOD_CHEQUE +': CONTA: '+@RET_DS_CNT+' - '+@RET_NR_CNT+' - '+@RS_HIST_LANC;
									SET @HIST = REPLACE (@HIST,'	',' ')
                                    ------------------------------------------------------------------------------------------------------------------
                                    --DEPOSITO CHEQUES - DEBITO -  CONTA BANCO
                                    ------------------------------------------------------------------------------------------------------------------
                                    IF @RS_LINHA = 1 /*Debito só 1 vez, com valor do deposito completo*/
									BEGIN
									
										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
													, @RS_CD_FILIAL AS CD_FILIAL
													, @CD_FILIAL AS CD_FILIAL_EXPPORT
													, 22 AS CD_MOVIMENTO
													, 'DEPOSITO DE RECBTO CHEQUES' 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
													, 'DEPOSITO DE RECBTO CHEQUES: CONTA: '+@RET_DS_CNT+' - '+@RET_NR_CNT+' - '+@RS_HIST_LANC 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
													, @CD_BC AS CD_BC;																											
									END
		
                                    ------------------------------------------------------------------------------------------------------------------
                                    --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, CD_BC)

                                           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 CHEQUES' 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_VLR_CREDITO 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
                                                , CASE WHEN @RS_MOD_CHEQUE = 'A VISTA' THEN 03 ELSE 02 END AS COD_CONTA--03 /*a vista*/ e 02 /*a prazo */ 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
												, @CD_BC AS CD_BC;
                                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
										 , @CD_BC = CD_BC
                                    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 = 'DEPOSITO DE RECBTO CH.'+ @RS_MOD_CHEQUE +': CONTA: '+@RET_DS_CNT+' - '+@RET_NR_CNT+' - '+@RS_HIST_LANC;
									SET @HIST = REPLACE (@HIST,'	',' ')
                                    ------------------------------------------------------------------------------------------------------------------
                                    --DEPOSITO CHEQUES - CREDITO -  CONTA BANCO (ORIGEM)
                                    ------------------------------------------------------------------------------------------------------------------
                                    
									IF @RS_LINHA = 1 /*Debito só 1 vez, com valor do deposito completo*/
									BEGIN
									
										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
													, @RS_CD_FILIAL AS CD_FILIAL
													, @CD_FILIAL AS CD_FILIAL_EXPPORT
													, 22 AS CD_MOVIMENTO
													, 'DEPOSITO DE RECBTO CHEQUES' 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
													, 'DEPOSITO DE RECBTO CHEQUES: CONTA: '+@RET_DS_CNT+' - '+@RET_NR_CNT+' - '+@RS_HIST_LANC 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
													, @CD_BC AS CD_BC;						
										END
                                    ------------------------------------------------------------------------------------------------------------------
                                    --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
											 , @CD_BC = CD_BC
										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, CD_BC)

                                           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 CHEQUES' 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_VLR_CREDITO 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
														, CASE WHEN @RS_MOD_CHEQUE = 'A VISTA' THEN 03 ELSE 02 END AS COD_CONTA--03 /*a vista*/ e 02 /*a prazo */ 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
														, @CD_BC AS CD_BC;
                                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,
					@RS_MOD_CHEQUE,
					@RS_VLR_CREDITO,
					@RS_LINHA;
                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;