IF EXISTS(SELECT * FROM SYS.procedures WHERE NAME = 'P_CG_FND_VERBA_MOVIMENTACAO_CONTRATO_ALL')
    BEGIN
        DROP PROCEDURE P_CG_FND_VERBA_MOVIMENTACAO_CONTRATO_ALL;
    END;
GO
 
--exec P_CG_FND_VERBA_MOVIMENTACAO_CONTRATO_ALL
CREATE PROCEDURE P_CG_FND_VERBA_MOVIMENTACAO_CONTRATO_ALL
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @USO INT;
	DECLARE @CD_EMP INT;
	DECLARE @TP_LANC INT;
	DECLARE @CD_FND_VERBA INT;

	DECLARE @ErrorMessage VARCHAR(4000);  
	DECLARE @ErrorSeverity INT;  
	DECLARE @ErrorState INT;

	CREATE TABLE #CONTRATOS_GERAR( CD_EMP       INT
								, CD_FND_VERBA INT
								, USO          INT
								, TP_LANC      INT
								);

	CREATE TABLE #CONTRATOS_FECHAR(
								CD_EMP       INT
								, CD_FND_VERBA INT
								);

--***********BUSCANDO OS CONTRATOS PARA GERAÇÃO DE VERBA************        

	begin try

		INSERT INTO #CONTRATOS_GERAR
		SELECT
				V.CD_EMP
				, V.CD_FND_VERBA
				, 0 AS USO
				, V.TP_LANC
		FROM
				CG_FND_VERBA V
				INNER JOIN CG_FND_VERBA_LIB L ON V.CD_EMP = L.CD_EMP
												AND V.CD_FND_VERBA = L.CD_FND_VERBA
				LEFT JOIN CG_FND_VERBA_HIST VH ON V.CD_FND_VERBA = VH.CD_FND_VERBA
												AND VH.DT_HIST = CONVERT(DATE, GETDATE())
												AND VH.TP_HIST = 'GERACAO'
		WHERE CONVERT(DATE, DATEADD(DAY, -1, GETDATE())) BETWEEN DT_INI_APURACAO AND DT_FIM_APURACAO
				AND VH.CD_EMP IS NULL
				AND V.TP_LANC <> 0
		UNION ALL
		SELECT
				CG_FND_VERBA_USO.CD_EMP
				, CG_FND_VERBA_USO_CG_FND_VERBA.CD_FND_VERBA
				, 1 AS USO
				, V.TP_LANC
		FROM
				CG_FND_VERBA_USO
				INNER JOIN CG_FND_VERBA_USO_CG_FND_VERBA ON CG_FND_VERBA_USO.CD_EMP = CG_FND_VERBA_USO_CG_FND_VERBA.CD_EMP
															AND CG_FND_VERBA_USO.CD_FND_VERBA_USO = CG_FND_VERBA_USO_CG_FND_VERBA.CD_FND_VERBA_USO
				INNER JOIN CG_FND_VERBA V ON CG_FND_VERBA_USO_CG_FND_VERBA.CD_EMP = V.CD_EMP
											AND CG_FND_VERBA_USO_CG_FND_VERBA.CD_FND_VERBA = V.CD_FND_VERBA
		WHERE CONVERT(DATE, DATEADD(DAY, -1, GETDATE())) BETWEEN CG_FND_VERBA_USO.DT_INI_APURACAO AND CG_FND_VERBA_USO.DT_FIM_APURACAO
				AND V.TP_LANC <> 0
		UNION ALL
		SELECT
				CG_FND_VERBA_USO.CD_EMP
				, CG_FND_VERBA_USO_CG_FND_VERBA.CD_FND_VERBA
				, 1 AS USO
				, V.TP_LANC
		FROM
				CG_FND_VERBA_USO
				INNER JOIN CG_FND_VERBA_USO_CG_FND_VERBA ON CG_FND_VERBA_USO.CD_EMP = CG_FND_VERBA_USO_CG_FND_VERBA.CD_EMP
															AND CG_FND_VERBA_USO.CD_FND_VERBA_USO = CG_FND_VERBA_USO_CG_FND_VERBA.CD_FND_VERBA_USO
				INNER JOIN CG_FND_VERBA V ON CG_FND_VERBA_USO_CG_FND_VERBA.CD_EMP = V.CD_EMP
											AND CG_FND_VERBA_USO_CG_FND_VERBA.CD_FND_VERBA = V.CD_FND_VERBA
				LEFT JOIN CG_FND_VERBA_NOTA_CREDITO_GRUPO_CG_FND_VERBA ON CG_FND_VERBA_USO_CG_FND_VERBA.CD_EMP = CG_FND_VERBA_NOTA_CREDITO_GRUPO_CG_FND_VERBA.CD_EMP
																		AND CG_FND_VERBA_USO_CG_FND_VERBA.CD_FND_VERBA = CG_FND_VERBA_NOTA_CREDITO_GRUPO_CG_FND_VERBA.CD_FND_VERBA
		WHERE CONVERT(DATE, DATEADD(DAY, -1, GETDATE())) BETWEEN CG_FND_VERBA_USO.DT_INI_APURACAO AND CG_FND_VERBA_USO.DT_FIM_APURACAO
				AND V.TP_LANC = 0
				AND CG_FND_VERBA_NOTA_CREDITO_GRUPO_CG_FND_VERBA.CD_EMP IS NULL;
    end try
	begin catch
	
		Set @ErrorMessage  =ERROR_MESSAGE();  
		Set @ErrorSeverity =ERROR_SEVERITY();  
		Set @ErrorState    =ERROR_STATE();  
	
		INSERT INTO ADM_ERRO (numero, severidade, estado, rotina, linha, mensagem, dtErro )
		SELECT ERROR_NUMBER() AS numero,
				@ErrorSeverity AS severidade,
				@ErrorState AS estado,
				ERROR_PROCEDURE() AS rotina,
				ERROR_LINE() AS linha,
				@ErrorMessage AS mensagem,
				GETDATE() AS dtErro;
	   
		RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState ); 
	end catch    
		
	DECLARE _CURSOR_GERAR CURSOR
    FOR SELECT
                CD_EMP
                , CD_FND_VERBA
                , USO
                , TP_LANC
            FROM
                #CONTRATOS_GERAR
            ORDER BY
                    USO;
        OPEN _CURSOR_GERAR;
        FETCH NEXT FROM _CURSOR_GERAR INTO @CD_EMP, @CD_FND_VERBA, @USO, @TP_LANC;

--*******EFETUANDO GERAÇÃO DAS VERBAS*********
	WHILE @@FETCH_STATUS <> -1
	BEGIN
		begin try
			--Gerar contrato
			IF @USO = 0
			BEGIN
				PRINT 'Gerando carga do contrato.: '+CONVERT(VARCHAR, @CD_FND_VERBA)+' - Time '+CONVERT(VARCHAR(20), GETDATE(), 108);
				EXEC P_CG_FND_VERBA_MOVIMENTACAO_CONTRATO @CD_EMP , @CD_FND_VERBA;
			END;

			--Uso de verba para recomposicao de margem
			IF @USO = 1
			BEGIN
			IF @TP_LANC <> 0
			BEGIN
				PRINT 'Gerando recomposicao margem.: '+CONVERT(VARCHAR, @CD_FND_VERBA)+' - Time '+CONVERT(VARCHAR(20), GETDATE(), 108);
				EXEC P_CG_FND_VERBA_MOVIMENTACAO_USO_CONTRATO 		@CD_EMP, @CD_FND_VERBA;
			END;
			ELSE
				BEGIN
					PRINT 'Gerando recomposicao margem veba fixa.: '+CONVERT(VARCHAR, @CD_FND_VERBA)+' - Time '+CONVERT(VARCHAR(20), GETDATE(), 108);
					EXEC P_CG_FND_VERBA_MOVIMENTACAO_USO_CONTRATO_VERBA_FIXA @CD_EMP, @CD_FND_VERBA;
				END;
			END;
		end try
		begin catch

			Set @ErrorMessage  =ERROR_MESSAGE();  
			Set @ErrorSeverity =ERROR_SEVERITY();  
			Set @ErrorState    =ERROR_STATE();  
	
			INSERT INTO ADM_ERRO (numero, severidade, estado, rotina, linha, mensagem, dtErro )
			SELECT ERROR_NUMBER() AS numero,
					@ErrorSeverity AS severidade,
					@ErrorState AS estado,
					ERROR_PROCEDURE() AS rotina,
					ERROR_LINE() AS linha,
					@ErrorMessage AS mensagem,
					GETDATE() AS dtErro;
	   
			RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState );
		end catch
		
		FETCH NEXT FROM _CURSOR_GERAR INTO @CD_EMP		, @CD_FND_VERBA		, @USO		, @TP_LANC;
		
	END;

	CLOSE _CURSOR_GERAR;
	DEALLOCATE _CURSOR_GERAR;


/**************************************************************************************/


    PRINT 'Buscando contratos para fechamento.: '+CONVERT(VARCHAR, @CD_FND_VERBA)+' - Time '+CONVERT(VARCHAR(20), GETDATE(), 108);

	begin try
		INSERT INTO #CONTRATOS_FECHAR
		SELECT
			V.CD_EMP
			, V.CD_FND_VERBA
		FROM
			CG_FND_VERBA V
			INNER JOIN CG_FND_VERBA_LIB L ON V.CD_EMP = L.CD_EMP
											AND V.CD_FND_VERBA = L.CD_FND_VERBA
			LEFT JOIN #CONTRATOS_FECHAR C ON V.CD_FND_VERBA = C.CD_FND_VERBA
		WHERE V.DT_FIM_APURACAO = DATEADD(DAY, -1, CONVERT(DATE, GETDATE()))
			AND V.TP_LANC <> 0
			AND C.CD_FND_VERBA IS NULL
			AND NOT EXISTS ( SELECT* FROM CG_FND_VERBA_FECHAMENTO F
							WHERE V.CD_EMP = F.CD_EMP
									AND V.CD_FND_VERBA = F.CD_FND_VERBA
									AND F.DT_FECHAMENTO = CONVERT(DATE, GETDATE()));

		DECLARE _CURSOR_FECHAR CURSOR
		FOR SELECT *
			FROM #CONTRATOS_FECHAR;

		OPEN _CURSOR_FECHAR;
		FETCH NEXT FROM _CURSOR_FECHAR INTO @CD_EMP, @CD_FND_VERBA;
	
		WHILE @@FETCH_STATUS <> -1
			BEGIN
				EXEC P_CG_FND_VERBA_FECHAMENTO_CONTRATO 1, @CD_FND_VERBA;
				FETCH NEXT FROM _CURSOR_FECHAR INTO @CD_EMP, @CD_FND_VERBA;
			END;
		CLOSE _CURSOR_FECHAR;
		DEALLOCATE _CURSOR_FECHAR;

	end try
	begin catch

		Set @ErrorMessage  =ERROR_MESSAGE();  
		Set @ErrorSeverity =ERROR_SEVERITY();  
		Set @ErrorState    =ERROR_STATE();  
	
		INSERT INTO ADM_ERRO (numero, severidade, estado, rotina, linha, mensagem, dtErro )
		SELECT ERROR_NUMBER() AS numero,
				@ErrorSeverity AS severidade,
				@ErrorState AS estado,
				ERROR_PROCEDURE() AS rotina,
				ERROR_LINE() AS linha,
				@ErrorMessage AS mensagem,
				GETDATE() AS dtErro;
	   
		RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState );
	end catch

END;