IF OBJECT_ID('P_EXP_CTB_MOV_BC_LANC_REC') IS NOT NULL
    BEGIN
        DROP PROCEDURE P_EXP_CTB_MOV_BC_LANC_REC;
    END;
GO 
--  exec P_EXP_CTB_MOV_BC_LANC_REC 1,3,'20180710','20180710',0
CREATE PROCEDURE P_EXP_CTB_MOV_BC_LANC_REC(@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_VLR_RECTA MONEY;
            DECLARE @RS_CD_CNT INT;
            DECLARE @RS_CD_DESP_REC INT;
            DECLARE @RS_DS_DESP_REC VARCHAR(100);
            DECLARE @RS_HIST_LANC VARCHAR(150);
            DECLARE @RS_DS_HIST VARCHAR(150);
            DECLARE @RS_NR_CNT_DESP VARCHAR(50);
            DECLARE @RS_CD_CNT_CTB_RED_DESP INT;
            DECLARE @RS_CD_CTB_CNT_CONTA VARCHAR(50);
            DECLARE @RS_CD_CTB_CNT_RED_CONTA INT;
            DECLARE @RS_DT_LANCTO 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 @RS_CD_BC_LANC INT;
			DECLARE @CD_CONTROLE AS INT;
			DECLARE @CD_TIPO_TRANSACAO AS INT;
			DECLARE @CD_FILIAL_CNT AS  INT;
			DECLARE @CD_BC AS 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                      , VLR_RECTA
                     , CD_CNT                     , CD_DESP_REC
                     , DS_DESP_REC                      , HIST_LANC
                     , DS_HIST                     , NR_CNT_DESP
                     , CD_CNT_CTB_RED_DESP
                     , CD_CTB_CNT_CONTA
                     , CD_CTB_CNT_RED_CONTA
                     , DT_LANCTO
					 , CD_BC_LANC
					 ,CD_TIPO_TRANSACAO
					 ,CD_CONTROLE
				FROM UFD_EXP_CTB_MOV_BC_LANC_RECTA(@CD_EMP, @CD_FILIAL, @DT_INI, @DT_FIM)  
               
			   ORDER BY				 
                CD_EMP
              , CD_FILIAL
              , DT_LANCTO;
									
            --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_VLR_RECTA, @RS_CD_CNT, @RS_CD_DESP_REC, @RS_DS_DESP_REC, @RS_HIST_LANC, @RS_DS_HIST, 
										  @RS_NR_CNT_DESP, @RS_CD_CNT_CTB_RED_DESP, @RS_CD_CTB_CNT_CONTA, @RS_CD_CTB_CNT_RED_CONTA, @RS_DT_LANCTO,@RS_CD_BC_LANC,
										  @CD_TIPO_TRANSACAO,@CD_CONTROLE;				
					 

            --Iniciando laço
            WHILE @@FETCH_STATUS = 0
                BEGIN
					IF @RS_VLR_RECTA > 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_FILIAL_CNT = CD_FILIAL_CNT
								 , @CD_BC = CD_BC
						 FROM UFD_BUSCA_CONTA_BC_CONTAS(@CD_EMP, @RS_CD_CNT);
						
                            IF @RS_HIST_LANC = ''
                                BEGIN
                                    SET @HIST = @RS_DS_HIST+' - CONTA: '+@RET_DS_CNT+' - '+@RET_NR_CNT;
                                END;
                            ELSE
                                BEGIN
                                    SET @HIST = @RS_HIST_LANC+' - CONTA: '+@RET_DS_CNT+' - '+@RET_NR_CNT;
                                END;								
								SET @HIST = REPLACE (@HIST,'	',' ')

                            ----------------------------------------------------------------------------------------------------------------
                            --MOVIMENTO BANCARIO - 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
										,CASE WHEN @RS_CD_FILIAL <> @CD_FILIAL_CNT THEN @CD_FILIAL_CNT ELSE @RS_CD_FILIAL END AS CD_FILIAL
                                        , 21 AS CD_MOVIMENTO
                                        , 'MOVIMENTO BANCARIO - RECEITAS' AS MOVIMENTO
                                        , '' AS OPERACAO
                                        , 'MOVIMENTO BANCARIO' 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_LANCTO AS DT_MOV
                                        , @HIST AS HIST
                                        , @RS_VLR_RECTA 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
                                        , CASE WHEN @CD_TIPO_TRANSACAO=3 THEN 'COMPRA CREDITO BC' ELSE 'CNT BANC' END 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
										, '' AS NR_DOC
										, @RS_CD_BC_LANC AS CD_BC_LANC
										, @CD_BC AS CD_BC;																				
						
		
                            ------------------------------------------------------------------------------------------------------------------
                            --MOVIMENTO BANCARIO - CREDITO -  RECEITAS
                            ------------------------------------------------------------------------------------------------------------------
							IF @CD_TIPO_TRANSACAO=3 --Venda de titulo convenio
							BEGIN
								SET @HIST = ''
								SET @RS_HIST_LANC=''
								SET @RS_CD_CNT=(SELECT
													CD_CNT
												FROM
													BC_LANC INNER JOIN RC_VD_CONV_RC_DEB_BC_LANC ON 
													BC_LANC.CD_EMP = RC_VD_CONV_RC_DEB_BC_LANC.CD_EMP AND 
													BC_LANC.CD_BC_LANC = RC_VD_CONV_RC_DEB_BC_LANC.CD_BC_LANC
												WHERE
													RC_VD_CONV_RC_DEB_BC_LANC.CD_CTR=@CD_CONTROLE
													AND BC_LANC.VLR_LANC > 0 
													AND BC_LANC.TP_LANC =1 )
								
								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);
						
								IF @RS_HIST_LANC = ''
									BEGIN
										SET @HIST = @RS_DS_HIST+' - CONTA: '+@RET_DS_CNT+' - '+@RET_NR_CNT;
									END;
								ELSE
									BEGIN
										SET @HIST = @RS_HIST_LANC+' - CONTA: '+@RET_DS_CNT+' - '+@RET_NR_CNT;
									END;								
								SET @HIST = REPLACE (@HIST,'	',' ')
							END
							--Fim 
							                            
							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
                                        , 21 AS CD_MOVIMENTO
                                        , 'MOVIMENTO BANCARIO - RECEITAS' AS MOVIMENTO
                                        , 'MOVIMENTO BANCARIO' AS OPERACAO
                                        , '' AS OPERACAO_DEBITO
                                        ,  CASE WHEN @CD_TIPO_TRANSACAO=3 THEN @RET_CD_CTB_CNT_RED ELSE @RS_CD_CNT_CTB_RED_DESP END AS CD_CNT_RED_CREDITO
                                        ,  CASE WHEN @CD_TIPO_TRANSACAO=3 then @RET_CD_CTB_CNT ELSE @RS_NR_CNT_DESP END AS CD_CNT_CREDITO
                                        , 0 AS CD_CNT_RED_DEBITO
                                        , '' AS CD_CNT_DEBITO
                                        , @RS_DT_LANCTO AS DT_MOV
                                        , @HIST AS HIST
                                        , @RS_VLR_RECTA 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
                                        , CASE WHEN @CD_TIPO_TRANSACAO=3 THEN 'COMPRA CREDITO BC' ELSE 'DESP\REC' END AS ORIG_CONTA
                                        , @RS_CD_DESP_REC AS COD_CONTA
                                        , OBJECT_NAME(@@PROCID) AS NM_PROCEDURE
                                        , 'X' AS TIPO_LANCTO
                                        , 0 AS CD_EVENTO
										, 0  AS NR_DP
										, 0  AS ANO
										, '' AS NR_DOC
										, @RS_CD_BC_LANC 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_VLR_RECTA, @RS_CD_CNT, @RS_CD_DESP_REC, @RS_DS_DESP_REC, @RS_HIST_LANC, @RS_DS_HIST, 
										  @RS_NR_CNT_DESP, @RS_CD_CNT_CTB_RED_DESP, @RS_CD_CTB_CNT_CONTA, @RS_CD_CTB_CNT_RED_CONTA, @RS_DT_LANCTO,@RS_CD_BC_LANC,
										  @CD_TIPO_TRANSACAO,@CD_CONTROLE;		                
			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;