IF OBJECT_ID('V_CG_FND_VERBA_LIBER_CONTRATOS') IS NOT NULL
	DROP VIEW V_CG_FND_VERBA_LIBER_CONTRATOS

GO
CREATE VIEW
	V_CG_FND_VERBA_LIBER_CONTRATOS
AS
SELECT
	V.CD_FND_VERBA,
	V.TP_LANC,
	V.HIST_LANC,
	F.CD_FORN,
	F.NM_FANT,
	V.VLR_LANC,
	
	CASE WHEN T.TIPO_ACAO_VERBA = 0 THEN
		V.VLR_LANC
	ELSE
		COALESCE(VP.VLR_PRODUTO,0)
	END AS VLR_PRODUTO,

	CASE WHEN V.TP_LANC = 0 THEN
		V.VLR_LANC
	ELSE
		COALESCE(VP.VLR_LIMITE,0)
	END AS VLR_LIMITE,

	V.DT_INI_APURACAO,
	V.DT_FIM_APURACAO,
	V.DT_VENCTO,
	0 AS CD_DOC,
	'' AS DS_DOC,
	VP.QTDE,
	V.CD_USU, 
	CASE WHEN LIB.CD_USU_FORN IS NOT NULL THEN
		LIB.CD_USU_FORN
	ELSE
		LIB.CD_USU
	END AS CD_USU_LIB,

	CASE WHEN LIB.CD_USU_FORN IS NOT NULL THEN
		UF.NM_USU_FORN
	ELSE
		USU.NM_USU
	END AS NM_USU_LIB,

	CASE WHEN LIB.CD_USU_FORN IS NOT NULL THEN
		'SIM'
	ELSE
		'NAO'
	END AS USU_FORN,
	LIB.HR_LIB,
	v.DT_LANCTO,
	ISNULL(BLQ.CD_FND_VERBA,0) AS BLOQUEADO
FROM
	CG_FND_VERBA V
	INNER JOIN CG_FND_VERBA_TIPO T ON
		V.CD_FND_VERBA_TIPO = T.CD_FND_VERBA_TIPO
	LEFT JOIN (
		SELECT
			CD_EMP,
			CD_FND_VERBA,
			COUNT(*) AS QTDE,
			SUM(VLR_IT) AS VLR_PRODUTO,
			SUM(LIMITE_VERBA) AS VLR_LIMITE
		FROM
			CG_FND_VERBA_DIARIA_PROD
		GROUP BY
			CD_EMP,
			CD_FND_VERBA) VP ON
		V.CD_EMP = VP.CD_EMP
		AND V.CD_FND_VERBA = VP.CD_FND_VERBA
		
	INNER JOIN PG_FORN F ON
		V.CD_EMP = F.CD_EMP
		AND V.CD_FORN = F.CD_FORN

	LEFT JOIN CG_FND_VERBA_LIB LIB ON
		LIB.CD_EMP = V.CD_EMP
		AND LIB.CD_FND_VERBA = V.CD_FND_VERBA

	LEFT JOIN CG_FND_VERBA_BLOQUEIO BLQ ON
		BLQ.CD_EMP = V.CD_EMP
		AND BLQ.CD_FND_VERBA = V.CD_FND_VERBA

	LEFT JOIN GLB_USU_FORN UF ON
		LIB.CD_EMP = UF.CD_EMP AND
		LIB.CD_USU_FORN  = UF.CD_USU_FORN

	LEFT JOIN GLB_USU USU ON
		LIB.CD_USU = USU.CD_USU

		
