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 CTF.Compromete_Factura = 1
							AND CANR.Id_Trans = CBN.Id_Trans
							AND CTN.Id_Tipo_Cbte = CBN.Id_Tipo_Cbte
							AND CTN.Compromete_Factura = -1
							AND CAFR.Importe = CANR.Importe) --- 2022/04/14 Solo se considera que una NC cancela la FC si la aplicación es por el mismo importe
						, 0)


RETURN ISNULL(@Id_Trans_NC, 0)
END
