SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GentugCariHesapOrtalamaVade]
(
@caricins AS TINYINT
,@carikod AS VARCHAR(25)
)
RETURNS DATETIME
AS
BEGIN
DECLARE @meblag AS FLOAT
DECLARE @summeblag AS FLOAT
DECLARE @vade AS INT
DECLARE @summeblagvade AS FLOAT
DECLARE @kapanan AS FLOAT
DECLARE @rec AS INT
DECLARE @vadetarih AS DATETIME
DECLARE @tarih AS DATETIME
DECLARE @mintarih AS DATETIME -- Kapanmayan min evrak tarihi
DECLARE @hartip AS TINYINT
DECLARE @bakiye AS FLOAT
DECLARE @end AS TINYINT
DECLARE @Xvadetarih AS FLOAT -- Ortalama Vade Tarihi
DECLARE @Xevrvade AS FLOAT -- Ortalama Evrak Tarihi
SET @bakiye = ISNULL(dbo.fn_CariHesapBakiye(0 ,0 ,@carikod ,'' ,'' ,0 ,0) ,0)
IF @bakiye<=0
SET @hartip = 1
ELSE
SET @hartip = 0
SET @bakiye = ABS(@bakiye)
IF ROUND(@bakiye ,2 ,1)<=0
OR (@caricins NOT IN (0 ,1))
BEGIN
SET @end = 1
END
ELSE
BEGIN
SET @end = 0
END
SET @summeblag = 0
SET @summeblagvade = 0
SET @kapanan = 0(
SELECT TOP 1 @tarih = cha_tarihi
,@vadetarih = dbo.fn_OpVadeTarih(cha_vade ,cha_tarihi)
,@rec = cha_RECno
FROM CARI_HESAP_HAREKETLERI
WHERE (
cha_cari_cins=@caricins
AND cha_kod=@carikod
AND cha_tip=@hartip
AND cha_tpoz=0
)
)
ORDER BY cha_tarihi DESC, dbo.fn_OpVadeTarih(cha_vade ,cha_tarihi) DESC ,
cha_RECno DESC
WHILE @end=0
BEGIN
(
SELECT @meblag = CASE
WHEN (cha_cinsi IN (13 ,29)) THEN (
cha_meblag- cha_vergi1- cha_vergi2-
cha_vergi3- cha_vergi4- cha_vergi5-
cha_vergi6- cha_vergi7- cha_vergi8-
cha_vergi9- cha_vergi10
)
WHEN (cha_cinsi=33) THEN cha_aratoplam
WHEN ((cha_cinsi=11) OR (cha_evrak_tip=59))
AND (dbo.fn_FirmaAnaDovizCinsi()<>cha_d_cins) THEN 0
ELSE cha_meblag END
,@vade=dbo.fn_OpVadeGun(cha_vade ,cha_tarihi) FROM
CARI_HESAP_HAREKETLERI WHERE cha_RECno=@rec
)
(
SELECT @kapanan = SUM(chk_Tutar)
FROM CARI_HAREKET_BORC_ALACAK_ESLEME
WHERE (chk_ChCinsi=@caricins AND chk_ChKodu=@carikod)
AND ((chk_BorcRECid=@rec AND @hartip=0))
)
IF @kapanan>@meblag
SET @kapanan = @meblag
IF (@summeblag+(@meblag-ISNULL(@kapanan ,0)))>@bakiye
SET @meblag = @bakiye- @summeblag
SET @summeblag = @summeblag+(@meblag-ISNULL(@kapanan ,0))
SET @summeblagvade = @summeblagvade+((@meblag-ISNULL(@kapanan ,0))*@vade)
IF ROUND(@summeblag ,0)>=ROUND(@bakiye ,0)
BEGIN
SET @end = 1
SET @Xvadetarih = CASE
WHEN @summeblagvade<>0 THEN @summeblagvade/@summeblag
ELSE 0
END
SET @mintarih = @tarih
SET @summeblagvade = 0
SET @summeblag = (@meblag-ISNULL(@kapanan ,0))
END
ELSE
BEGIN
(
SELECT TOP 1 @rec = cha_RECno
,@tarih = cha_tarihi
,@vadetarih = dbo.fn_OpVadeTarih(cha_vade ,cha_tarihi)
FROM CARI_HESAP_HAREKETLERI
WHERE (
cha_cari_cins=@caricins
AND cha_kod=@carikod
AND cha_tip=@hartip
AND cha_tpoz=0
)
AND (
(cha_tarihi<@tarih)
OR (
cha_tarihi=@tarih
AND dbo.fn_OpVadeTarih(cha_vade ,cha_tarihi)
<@vadetarih
)
OR (
cha_tarihi=@tarih
AND dbo.fn_OpVadeTarih(cha_vade ,cha_tarihi)
=@vadetarih
AND cha_RECno<@rec
)
)
)
ORDER BY cha_tarihi DESC,dbo.fn_OpVadeTarih(cha_vade ,cha_tarihi)
DESC ,cha_RECno DESC
END
END
BEGIN
WHILE @end=1
BEGIN
IF ROUND(@summeblag ,0)>=ROUND(@bakiye ,0)
BEGIN
SET @end = 0
SET @Xevrvade = CASE
WHEN @summeblagvade<>0 THEN @summeblagvade/
@summeblag
ELSE 0
END
END
ELSE
BEGIN
(
SELECT TOP 1 @rec = cha_RECno
,@tarih = cha_tarihi
,@vadetarih = dbo.fn_OpVadeTarih(cha_vade ,cha_tarihi)
FROM CARI_HESAP_HAREKETLERI
WHERE (
cha_cari_cins=@caricins
AND cha_kod=@carikod
AND cha_tip=@hartip
AND cha_tpoz=0
)
AND (
(cha_tarihi>@tarih)
OR (
cha_tarihi=@tarih
AND dbo.fn_OpVadeTarih(cha_vade ,cha_tarihi)
>@vadetarih
)
OR (
cha_tarihi=@tarih
AND dbo.fn_OpVadeTarih(cha_vade ,cha_tarihi)
=@vadetarih
AND cha_RECno>@rec
)
)
)
ORDER BY cha_tarihi ASC ,dbo.fn_OpVadeTarih(cha_vade ,cha_tarihi)
ASC ,cha_RECno ASC(
SELECT @meblag = CASE
WHEN (cha_cinsi IN (13 ,29)) THEN (
cha_meblag- cha_vergi1-
cha_vergi2- cha_vergi3-
cha_vergi4- cha_vergi5-
cha_vergi6- cha_vergi7-
cha_vergi8- cha_vergi9-
cha_vergi10
)
WHEN (cha_cinsi=33) THEN cha_aratoplam
WHEN ((cha_cinsi=11) OR (cha_evrak_tip=59))
AND (dbo.fn_FirmaAnaDovizCinsi()<>cha_d_cins) THEN 0 ELSE
cha_meblag END
,@vade=dbo.fn_gunfarkibul(@mintarih ,cha_tarihi) FROM
CARI_HESAP_HAREKETLERI WHERE cha_RECno=@rec
)
(
SELECT @kapanan = SUM(chk_Tutar)
FROM CARI_HAREKET_BORC_ALACAK_ESLEME
WHERE (chk_ChCinsi=@caricins AND chk_ChKodu=@carikod)
AND (
(chk_BorcRECid=@rec AND @hartip=0)
OR (chk_AlcRECid=@rec AND @hartip=1)
)
)
IF @kapanan>@meblag
SET @kapanan = @meblag -- Hatalı kayıtlar gözardı ediliyor...
SET @summeblag = @summeblag+(@meblag-ISNULL(@kapanan ,0))
SET @summeblagvade = @summeblagvade+((@meblag-ISNULL(@kapanan ,0))*@vade)
END
END
END
RETURN
CASE
WHEN @summeblag<>0 THEN @mintarih+ROUND((@Xvadetarih+@Xevrvade) ,0)
END
END
GO