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


CREATE FUNCTION [dbo].[CF_NC_A_FC](@pId_Trans_Fac INT) 
RETURNS INT
AS
BEGIN
/*Variable de Retorno*/
DECLARE @Id_Trans_NC			INT
/*FIN Variable de Retorno*/

/*Variables Intermedias*/
/*FIN Variables Intermedias*/

SET @Id_Trans_NC = ISNULL((SELECT MAX(canr.id_trans)
							FROM Cbtes_Aplica CAFR, Cbtes_Aplica CANR, Cbtes_Tipos CTF, Cbtes_Tipos CTN, Cbtes CBF, Cbtes CBN
							WHERE 
							CAFR.Id_Trans = @pId_Trans_Fac
							AND CAFR.id_trans_d = CANR.id_trans_d
							AND CAFR.Id_Trans = CBF.Id_Trans
							AND CTF.Id_Tipo_Cbte = CBF.Id_Tipo_Cbte
							AND ISNULL(CTF.Compromete_Factura, 0) =1
							AND CANR.Id_Trans = CBN.Id_Trans
							AND CTN.Id_Tipo_Cbte = CBN.Id_Tipo_Cbte
							AND ISNULL(CTN.Compromete_Factura, 0) = -1)
						, 0)


RETURN ISNULL(@Id_Trans_NC, 0)
END
