IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = 'UFD_CG_FND_VERBA_RETORNA_VLR_PROD')
BEGIN
	DROP FUNCTION UFD_CG_FND_VERBA_RETORNA_VLR_PROD
END
GO

CREATE FUNCTION
	DBO.UFD_CG_FND_VERBA_RETORNA_VLR_PROD (@CD_EMP INT, @CD_FND_VERBA INT)
RETURNS 
	@RS_RETORNO
TABLE (
	CD_EMP INT,
	CD_FND_VERBA INT,
	CD_PROD INT,
	VLR_GERADO MONEY,
	VLR_ALOCADO MONEY,
	VLR_SALDO MONEY)  WITH ENCRYPTION
AS
BEGIN
	
	INSERT INTO
		@RS_RETORNO
	SELECT
		CG_FND_VERBA_DIARIA_PROD.CD_EMP,
		CG_FND_VERBA_DIARIA_PROD.CD_FND_VERBA,
		CG_FND_VERBA_DIARIA_PROD.CD_PROD,
		
		COALESCE(SUM(TBL.VLR_GERADO),0) AS VLR_GERADO,
		COALESCE(SUM(TBL.VLR_ALOCADO),0) AS VLR_ALOCADO,
		COALESCE(SUM(TBL.VLR_GERADO),0) - COALESCE(SUM(VLR_ALOCADO),0) AS VLR_SALDO

	FROM 
		CG_FND_VERBA_DIARIA_PROD
		INNER JOIN CG_FND_VERBA ON
			CG_FND_VERBA_DIARIA_PROD.CD_EMP = CG_FND_VERBA.CD_EMP
			AND CG_FND_VERBA_DIARIA_PROD.CD_FND_VERBA = CG_FND_VERBA.CD_FND_VERBA
		LEFT JOIN (
		
			SELECT
				R.CD_EMP,
				R.CD_FND_VERBA,
				IV.CD_PROD,
				SUM(VLR_PROD_VERBA_USADA) AS VLR_GERADO,
				0 AS VLR_ALOCADO
			FROM
				CG_FND_VERBA_RECB R
				INNER JOIN CG_FND_VERBA_RECB_CG_FND_VERBA_GERACAO_PDV_VD_IT V ON
					R.CD_EMP = V.CD_EMP
					AND R.CD_FND_VERBA = V.CD_FND_VERBA
					AND R.CD_FND_VERBA_RECB = V.CD_FND_VERBA_RECB
				INNER JOIN CG_FND_VERBA_GERACAO_PDV_VD_IT I ON
					V.CD_EMP = I.CD_EMP
					AND V.CD_FND_VERBA = I.CD_FND_VERBA
					AND V.CD_FILIAL = I.CD_FILIAL
					AND V.CD_VD = I.CD_VD
					AND V.CD_IT = I.CD_IT
				INNER JOIN PDV_VD_IT IV ON
					I.CD_EMP = IV.CD_EMP
					AND I.CD_FILIAL = IV.CD_FILIAL
					AND I.CD_VD = IV.CD_VD
					AND I.CD_IT = IV.CD_IT
			WHERE
				R.CD_EMP = @CD_EMP
				AND R.CD_FND_VERBA = @CD_FND_VERBA
				AND R.STATUS = 0
			GROUP BY
				R.CD_EMP,
				R.CD_FND_VERBA,
				IV.CD_PROD

			UNION ALL

			SELECT
				R.CD_EMP,
				R.CD_FND_VERBA,
				I.CD_PROD,
				SUM(VLR_PROD_VERBA_USADA) AS VLR_GERADO,
				0 AS VLR_ALOCADO
			FROM
				CG_FND_VERBA_RECB R
				INNER JOIN CG_FND_VERBA_RECB_CG_FND_VERBA_GERACAO_EST_NF_ENT_IT V ON
					R.CD_EMP = V.CD_EMP
					AND R.CD_FND_VERBA = V.CD_FND_VERBA
					AND R.CD_FND_VERBA_RECB = V.CD_FND_VERBA_RECB
				INNER JOIN CG_FND_VERBA_GERACAO_EST_NF_ENT_IT I ON
					V.CD_EMP = I.CD_EMP
					AND V.CD_FND_VERBA = I.CD_FND_VERBA
					AND V.CD_FILIAL = I.CD_FILIAL
					AND V.CD_NF = I.CD_NF
					AND V.CD_PROD = I.CD_PROD
			WHERE
				R.CD_EMP = @CD_EMP
				AND R.CD_FND_VERBA = @CD_FND_VERBA
				AND R.STATUS = 0
			GROUP BY
				R.CD_EMP,
				R.CD_FND_VERBA,
				I.CD_PROD

			UNION ALL

			SELECT
				R.CD_EMP,
				R.CD_FND_VERBA,
				IV.CD_PROD,
				0 AS VLR_GERADO,
				SUM(IV.VLR_USO) AS VLR_ALOCADO
			FROM
				CG_FND_VERBA_SAQUE R
				INNER JOIN CG_FND_VERBA_SAQUE_CG_FND_VERBA_USO_PDV_VD_IT V ON
					R.CD_EMP = V.CD_EMP
					AND R.CD_FND_VERBA = V.CD_FND_VERBA
					AND R.CD_FND_VERBA_SAQUE = V.CD_FND_VERBA_SAQUE				
				INNER JOIN CG_FND_VERBA_USO_PDV_VD_IT IV ON
					V.CD_EMP = IV.CD_EMP
					AND V.CD_FILIAL = IV.CD_FILIAL
					AND V.CD_VD = IV.CD_VD
					AND V.CD_IT = IV.CD_IT
					AND V.CD_FND_VERBA_USO = IV.CD_FND_VERBA_USO
			WHERE
				R.CD_EMP = @CD_EMP
				AND R.CD_FND_VERBA = @CD_FND_VERBA
				AND R.STATUS = 0
			GROUP BY
				R.CD_EMP,
				R.CD_FND_VERBA,
				IV.CD_PROD) TBL ON
		CG_FND_VERBA_DIARIA_PROD.CD_EMP = TBL.CD_EMP
		AND CG_FND_VERBA_DIARIA_PROD.CD_FND_VERBA = TBL.CD_FND_VERBA
		AND CG_FND_VERBA_DIARIA_PROD.CD_PROD = TBL.CD_PROD

	WHERE
		CG_FND_VERBA_DIARIA_PROD.CD_EMP = @CD_EMP
		AND CG_FND_VERBA_DIARIA_PROD.CD_FND_VERBA = @CD_FND_VERBA
		AND CG_FND_VERBA.TP_LANC <> 0
	GROUP BY
		CG_FND_VERBA_DIARIA_PROD.CD_EMP,
		CG_FND_VERBA_DIARIA_PROD.CD_FND_VERBA,
		CG_FND_VERBA_DIARIA_PROD.CD_PROD,
		CG_FND_VERBA.TP_LANC,
		CG_FND_VERBA_DIARIA_PROD.LIMITE_VERBA


	RETURN;
END
