IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'dbo.CF_Item_Paid') 
                   and type = 'FN')
BEGIN
    DROP FUNCTION dbo.CF_Item_Paid
END
GO

CREATE FUNCTION [dbo].[CF_Item_Paid](@pId_Cliente INT, @pId_Producto VARCHAR(14), @pFecha DATETIME)
RETURNS TINYINT
AS
BEGIN
DECLARE @Flag_Item_Pagado TINYINT

DECLARE @Flag_Mes				TINYINT
DECLARE @Flag_Periodo			TINYINT
DECLARE @Flag_Existe_Producto	TINYINT
DECLARE @Flag_Existe_Cliente	TINYINT
DECLARE @Flag_PermiteProdPorPed	INT

---------------------------------------------------------------------------------------------------------------

SET @Flag_Existe_Producto = ISNULL((SELECT 1 FROM Productos WHERE Id_Producto = @pId_Producto), 0)

IF(@Flag_Existe_Producto = 0)
BEGIN
	SET @Flag_Item_Pagado = 0
	RETURN @Flag_Item_Pagado
END

---------------------------------------------------------------------------------------------------------------

SET @Flag_Existe_Cliente = ISNULL((SELECT 1 FROM Clientes WHERE Id_Cliente = @pId_Cliente), 0)

IF(@Flag_Existe_Cliente = 0)
BEGIN
	SET @Flag_Item_Pagado = 0
	RETURN @Flag_Item_Pagado
END

---------------------------------------------------------------------------------------------------------------

SELECT  
@Flag_Mes		= ISNULL(Flag_Mes, 0),
@Flag_Periodo	= ISNULL(Flag_Periodo, 0)
FROM Productos
WHERE
Id_Producto = @pId_Producto

---------------------------------------------------------------------------------------------------------------

IF (@Flag_Mes = 0 AND @Flag_Periodo = 0) --No controla ni mes ni periodo, con que alguna vez lo haya pagado esta ok
BEGIN	
	SET @Flag_Item_Pagado = (SELECT MAX(1)
							FROM Cbtes CB, Cbtes_Items CI, Cbtes_Tipos CT
							WHERE
							CB.Id_Trans = CI.Id_Trans
							AND ISNULL(CB.Id_Tipo_Cbte, '') = CT.Id_Tipo_Cbte
							AND ISNULL(CT.Compromete_Factura, 0) IN (1)
							AND ISNULL(CI.Id_Cliente, 0) = @pId_Cliente
							AND LTRIM(RTRIM(ISNULL(CI.Id_Producto, ''))) = LTRIM(RTRIM(@pId_Producto))
							AND ((ISNULL(CB.Id_Estado_Cbte, 0) = 2) OR (ISNULL(CB.Id_Estado_Cbte, 0) IN (1, 4) AND ISNULL(CI.Imp_Final, 0) = 0))
							)
	RETURN ISNULL(@Flag_Item_Pagado, 0)
END

---------------------------------------------------------------------------------------------------------------

IF (@Flag_Mes = 1) --Controla mes tiene prioridad por sobre Periodo
BEGIN	
	SET @Flag_Item_Pagado = (SELECT MAX(1)
							FROM Cbtes CB, Cbtes_Items CI, Cbtes_Tipos CT
							WHERE
							CB.Id_Trans = CI.Id_Trans
							AND ISNULL(CB.Id_Tipo_Cbte, '') = CT.Id_Tipo_Cbte
							AND ISNULL(CT.Compromete_Factura, 0) IN (1)
							AND ISNULL(CI.Id_Cliente, 0) = @pId_Cliente
							AND LTRIM(RTRIM(ISNULL(CI.Id_Producto, ''))) = LTRIM(RTRIM(@pId_Producto))
							AND ((ISNULL(CB.Id_Estado_Cbte, 0) = 2) OR (ISNULL(CB.Id_Estado_Cbte, 0) IN (1, 4) AND ISNULL(CI.Imp_Final, 0) = 0))
							AND dbo.CF_FOMONTH(CONVERT(DATE, ISNULL(CI.Fecha_QA, '19000101'))) = dbo.CF_FOMONTH(CONVERT(DATE, @pFecha))
							)

	RETURN ISNULL(@Flag_Item_Pagado, 0)
END

---------------------------------------------------------------------------------------------------------------

IF (@Flag_Periodo = 1)
BEGIN	
	SET @Flag_Item_Pagado = (SELECT MAX(1)
							FROM Cbtes CB, Cbtes_Items CI, Cbtes_Tipos CT
							WHERE
							CB.Id_Trans = CI.Id_Trans
							AND ISNULL(CB.Id_Tipo_Cbte, '') = CT.Id_Tipo_Cbte
							AND ISNULL(CT.Compromete_Factura, 0) IN (1)
							AND ISNULL(CI.Id_Cliente, 0) = @pId_Cliente
							AND LTRIM(RTRIM(ISNULL(CI.Id_Producto, ''))) = LTRIM(RTRIM(@pId_Producto))
							AND ((ISNULL(CB.Id_Estado_Cbte, 0) = 2) OR (ISNULL(CB.Id_Estado_Cbte, 0) IN (1, 4) AND ISNULL(CI.Imp_Final, 0) = 0))
							AND CONVERT(DATE, ISNULL(CI.Fecha_QA, '19000101')) <= CONVERT(DATE, @pFecha)
							AND CONVERT(DATE, ISNULL(CI.Fecha_Venc, '19000101')) >= CONVERT(DATE, @pFecha)
							)

	RETURN ISNULL(@Flag_Item_Pagado, 0)
END

---------------------------------------------------------------------------------------------------------------

RETURN ISNULL(@Flag_Item_Pagado, 0)
END
