ERP ve Excel - ORTVADE fonksiyon hk.

create FUNCTION [dbo].[ORTVADE](@FIRMA_ID varchar(10), @YIL_ID int) RETURNS SMALLDATETIME

AS

BEGIN

DECLARE @REFDATE SMALLDATETIME

DECLARE @Bakiye DECIMAL(38,2)

DECLARE @xBA VARCHAR(1)

DECLARE CariOZET_Cursor CURSOR FOR

SELECT bakiye FROM Cariozet

WHERE (FIRMAKODU = @FIRMA_ID) AND (YIL = @YIL_ID)

OPEN CariOZET_Cursor

FETCH NEXT FROM CariOZET_Cursor

INTO @Bakiye

CLOSE CariOZET_Cursor

DEALLOCATE CariOZET_Cursor

IF @Bakiye >= 0 SET @xBA = 'B'

ELSE SET @xBA = 'A'

SET @REFDATE = Getdate()-2000

DECLARE @SAdat Float, @SBakiye Float, @Flag int

DECLARE @VADETAR SMALLDATETIME, @BA VARCHAR(1), @BORC Float, @ALACAK float

DECLARE CariEkstre_Cursor CURSOR FOR

SELECT Vadetar, BA, BORC, ALACAK FROM CariEkstre

WHERE (FIRMAKODU = @FIRMA_ID) AND (YIL = @YIL_ID) AND (BA = @xBA)

ORDER BY VADETAR DESC

OPEN CariEkstre_Cursor

FETCH NEXT FROM CariEkstre_Cursor

INTO @VADETAR, @BA, @BORC, @ALACAK

SET @SAdat=0

SET @SBakiye = 0

SET @FLAG = @@FETCH_STATUS

WHILE (@FLAG = 0 )

BEGIN

IF @xBA = 'B'

BEGIN

If @SBakiye + @Borc > @Bakiye

BEGIN

SET @SAdat=@SAdat + (@Bakiye - @SBakiye) * CONVERT(float, @VADETAR - @RefDate)

SET @SBakiye = @Bakiye

SET @Flag=1

END

ELSE

BEGIN

SET @SBakiye = @SBakiye + @Borc

SET @SAdat=@SAdat + @BORC * CONVERT(float, @VADETAR - @RefDate)

END

END

ELSE

BEGIN

If @SBakiye + @Alacak > @Bakiye

BEGIN

SET @SAdat=@SAdat + (@Bakiye - @SBakiye) * CONVERT(float, @VADETAR - @RefDate)

SET @SBakiye = @Bakiye

SET @Flag=1

END

ELSE

BEGIN

SET @SBakiye = @SBakiye + @Alacak

SET @SAdat=@SAdat + @Alacak * CONVERT(float, @VADETAR - @RefDate)

END

END

FETCH NEXT FROM CariEkstre_Cursor

INTO @VADETAR, @BA, @BORC, @ALACAK

IF @@FETCH_STATUS <> 0 SET @Flag=1

END

CLOSE CariEkstre_Cursor

DEALLOCATE CariEkstre_Cursor

RETURN @REFDATE + FLOOR(@SAdat / @SBakiye)

END