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

CREATE FUNCTION [dbo].[CF_Cbte_Saldo](@trans INT,@fecha datetime) 
RETURNS DECIMAL(16,2)
AS
BEGIN
declare @fechaCbte datetime
DECLARE @Retorno DECIMAL(17,5)

set @fechacbte = (SELECT fecha FROM Cbtes WHERE Id_Trans = @trans )

if @fecha < @fechacbte 
   return 0

set @Retorno = isnull((select sum(ABS(isnull(z.importe,0))) 
                   from cbtes_aplica z , CBTES a
                   where z.id_trans = @trans 
                   and a.Id_Trans = z.ID_TRANS_d
                   and a.Id_Estado_Cbte not in (0,3)
                   and a.Fecha < @fecha +1 ),0)
                   

set @Retorno = @Retorno + isnull((select sum(ABS(isnull(z.importe,0))) 
                   from cbtes_aplica z , CBTES a, cbtes_tipos NC
                   where z.id_trans_D = @trans 
                   and a.Id_Trans = z.ID_TRANS_d
                   and NC.id_tipo_cbte = a.id_tipo_cbte
                   and NC.Compromete_Factura in (-1)
                   and a.Id_Estado_Cbte not in (0,3)
                   and a.Fecha < @fecha +1 ),0)   
				   

set @Retorno = @Retorno + isnull((select sum(ABS(isnull(z.importe,0))) 
                   from cbtes_aplica z , CBTES a, cbtes_tipos NC
                   where z.id_trans_D = @trans 
                   and a.Id_Trans = z.ID_TRANS_d
                   and NC.id_tipo_cbte = a.id_tipo_cbte
                   and NC.Compromete_Recibos in (1)
                   and a.Id_Estado_Cbte not in (0,3)
                   and a.Fecha < @fecha +1 
				   and z.id_trans <> 0 ),0) 

set @Retorno =  (SELECT Imp_Total FROM Cbtes WHERE Id_Trans = @trans )- @Retorno
     
RETURN @Retorno
END
   

                        
                   
