CREATE OR ALTER proc sp_TahsilatVadeRaporu(@MusKod varchar(50))    
-- sp_TahsilatVadeRaporu '120.ZNC.003'
as    
declare @cteHesap table (Sira int,CariNo int,IslemNo int,Tarih Datetime,Tutar money,C_Bky money,Devir Money )
insert into @cteHesap
    SELECT ROW_NUMBER() OVER (ORDER BY Tarih desc,IslemNo) as Sira,CariNo, IslemNo, Tarih ,ISNULL(SUM(Borc),0) Tutar,
	(select MUSBAKIYE FROM  V_AMUSTERI WHERE V_AMUSTERI.MUSNO=CariNo) C_Bky,0 as Devir
    FROM Odeme O   where O.CariKodu=@MusKod  AND Borc>0
	GROUP BY Tarih,IslemNo,CariNo
 
 update @cteHesap set Devir=(cte1.C_Bky-(select sum(cte2.Tutar) from @cteHesap as cte2 where cte2.Sira<=cte1.Sira))  from @cteHesap as cte1
 where Sira=cte1.Sira and (cte1.C_Bky-(select sum(cte2.Tutar) from @cteHesap as cte2 where cte2.Sira<=cte1.Sira))>0

 --delete @cteHesap where Sira>(select min(Sira) from @cteHesap where Devir=0)
 


  SELECT TOP 100 PERCENT dbo.AMUSTERI.MUSNO AS CariNo, dbo.AMUSTERI.MUSKOD AS CariKodu, dbo.AMUSTERI.MUSADI AS CariAdi, 
 Odeme_1.BAAcikla AS IslemTuru,      
 Odeme_1.IslemNo, Odeme_1.Aciklama, dbo.ACARI.TARIH AS IslemTarihi, Odeme_1.Tarih AS VadeTarihi,     
 datediff(day,Odeme_1.Tarih,getdate()) AS GecikenGunSayisi,cte1.Devir,cte1.Tutar as Borc,cte1.C_Bky as Bakiye,
 dbo.STOKHARDETBAG.FatSerNo, dbo.STOKHARDETBAG.FaturaNo   
 FROM         dbo.AMUSTERI
 INNER JOIN   dbo.Odeme AS Odeme_1 ON dbo.AMUSTERI.MUSKOD = Odeme_1.CariKodu 
 INNER JOIN   dbo.ACARI ON Odeme_1.IslemNo = dbo.ACARI.ISLEMNO AND Odeme_1.CariKodu = dbo.ACARI.MUSKOD 
 inner join   @cteHesap as cte1 on cte1.CariNo=dbo.AMUSTERI.MUSNO and cte1.IslemNo=dbo.ACARI.ISLEMNO
 
 LEFT OUTER JOIN    dbo.STOKHARDETBAG ON dbo.ACARI.ISLEMNO = dbo.STOKHARDETBAG.IslemNo    
-- WHERE     (Odeme_1.Borc > 0) AND (Odeme_1.Alacak = 0) AND (dbo.AMUSTERI.MUSBAKIYE + Odeme_1.Borc -   
--                          (SELECT     SUM(Borc) AS Expr1    
--                            FROM          dbo.Odeme AS Odeme_2  
--                            WHERE      (CariNo = Odeme_1.CariNo) AND (Tarih >= Odeme_1.Tarih)) > 0)   

--and (dbo.AMUSTERI.MUSKOD=@MusKod)    
    
ORDER BY Sira DESC


 

  
 