ALTER   proc [dbo].[spTahsilVadeRaporu_Rut] (@RutAdi nvarchar(max),@RutGunu nvarchar(max),@RutSatici nvarchar(max),@TarihParam date = null)      
as        
create table #odeme        
(        
  RecID integer Identity (1, 1) not null, NoId integer, IslemNo integer, Tarih datetime,        
  CariNo integer,  IslemTuru nvarchar(20) COLLATE DATABASE_DEFAULT,  Bakiye float,  Borc_Kullanilan float ,  Devreden float,  Borc float,        
  Devir float  PRIMARY KEY CLUSTERED  (RecID)        
)        
      
DECLARE @CariNo int      
Declare @DagitimTutari float        
Declare @NoId integer,  @IslemNo integer,  @Tarih datetime,  @Borc decimal (28, 14), @Borc_Kullanilan decimal (28, 14),  @Son_Kalan_Tutar float,  @IslemTuru nvarchar(20), @TmpTutar decimal(28, 14)        
      
declare cr_Rut cursor FAST_FORWARD READ_ONLY for        
select  CariNo from [VRUT] where @RutAdi is not null and RutAdi =@RutAdi and Deger = @RutGunu and SaticiAdi = @RutSatici     
union ALL     
select MUSNO from AMUSTERI where MUSBAKIYE > 0.01 and  @RutAdi is null    
open cr_Rut        
fetch next from cr_Rut        
into  @CariNo      
while (@@FETCH_STATUS = 0)      
begin      
      
 set @DagitimTutari = (select SUM (BORC-ALACAK) from ACARI WHERE MUSNO=@CariNo)         
 set @TmpTutar = @DagitimTutari        
        
 declare cr_Prepare cursor FAST_FORWARD READ_ONLY for        
 select           
   odm.NoId,        
   odm.IslemNo,        
   odm.Tarih,        
   odm.CariNo,        
   odm.Borc,        
   odm.BAAcikla        
 from Odeme odm        
 where        
    odm.CariNo = @CariNo and        
    IsNull (odm.Borc, 0) > 0        
 order by        
   odm.Tarih desc,NoId desc        
        
 open cr_Prepare        
 fetch next from cr_Prepare        
 into        
   @NoId,        
   @IslemNo,        
   @Tarih,        
   @CariNo,        
   @Borc,        
   @IslemTuru        
        
 while (@@FETCH_STATUS = 0) and (@TmpTutar > 0.0)        
 begin        
   if @Borc >= @TmpTutar         
   begin        
  set @Borc_Kullanilan = @TmpTutar        
  set @Son_Kalan_Tutar = @TmpTutar - @Borc        
  set @TmpTutar = 0.0        
   end        
   else        
   begin        
  set @TmpTutar = @TmpTutar - @Borc        
  set @Borc_Kullanilan = @Borc        
  set @Son_Kalan_Tutar = @TmpTutar        
   end        
        
   insert into #odeme         
   (        
	NoId,        
	IslemNo,        
	Tarih,        
	CariNo,        
	Borc,        
	Borc_Kullanilan,        
	Devreden,        
	Bakiye,        
	Devir,        
	IslemTuru        
   )        
   values        
   (        
	@NoId,         
	@IslemNo,        
	@Tarih,        
	@CariNo,        
	@Borc,        
	@Borc_Kullanilan,        
	@Son_Kalan_Tutar,        
	@DagitimTutari,        
	@Son_Kalan_Tutar+@Borc,        
	@IslemTuru        
   )        
        
   fetch next from cr_Prepare        
	into        
	  @NoId,        
	  @IslemNo,        
	  @Tarih,        
	  @CariNo,        
	  @Borc,        
	  @IslemTuru        
 end        
          
 close cr_Prepare        
 deallocate cr_Prepare         
      
   FETCH NEXT FROM cr_Rut  INTO @CariNo      
end         
close cr_Rut        
deallocate cr_Rut  

IF @TarihParam IS NULL
BEGIN      
	select        
		RecID,      
		o.NoId,      
		a.MUSNO  [Cari No],        
		a.MUSKOD [Cari Kodu],        
		a.MUSADI [Cari Adı],        
		o.IslemTuru [İşlem Türü],        
		o.IslemNo [İşlem No],        
		c.ACIKLAMA [Açıklama],        
		c.TARIH  [İşlem Tarihi],        
		o.Tarih  [Vade Tarihi],        
		CAST(GETDATE() - o.Tarih as int) [Geciken Gün],        
		Devreden,      
		o.Borc [Borç],        
		o.Devir,        
		o.Bakiye,        
		d.FatSerNo [Fatura Seri No],        
		d.FaturaNo [Fatura No],
		a.SaticiAdi,
		a.SaticiKodu
	from #odeme o        
	inner join  AMUSTERI a on  o.CariNo = a.MUSNO        
	inner join  ACARI    c on c.ISLEMNO = o.IslemNo and c.MUSNO = o.CariNo and c.BAACIKLA = o.IslemTuru       
	left join   STOKHARDETBAG d on o.IslemNo = d.IslemNo        
	Order by a.MUSNO,o.Tarih desc, o.NoId desc  
 END 
 ELSE 
 BEGIN
	SELECT        
	  o.RecID,      
	  o.NoId,      
	  a.MUSNO AS [Cari No],        
	  a.MUSKOD AS [Cari Kodu],        
	  a.MUSADI AS [Cari Adı],        
	  o.IslemTuru AS [İşlem Türü],        
	  o.IslemNo AS [İşlem No],        
	  c.ACIKLAMA AS [Açıklama],        
	  c.TARIH  [İşlem Tarihi],        
	  o.Tarih  [Vade Tarihi],         
	  CAST(GETDATE() - o.Tarih AS int) AS [Geciken Gün],        
	  o.Devreden,      
	  o.Borc AS [Borç],        
	  o.Devir,        
	  o.Bakiye,        
	  d.FatSerNo AS [Fatura Seri No],        
	  d.FaturaNo AS [Fatura No],
	  a.SaticiAdi,
	  a.SaticiKodu
	FROM AMUSTERI a
	CROSS APPLY (
		SELECT TOP 1 *
		FROM #odeme o
		WHERE o.CariNo = a.MUSNO AND o.Tarih <= @TarihParam
		ORDER BY o.Tarih DESC, o.NoId DESC
	) o
	INNER JOIN ACARI c ON c.ISLEMNO = o.IslemNo AND c.MUSNO = o.CariNo AND c.BAACIKLA = o.IslemTuru
	LEFT JOIN STOKHARDETBAG d ON o.IslemNo = d.IslemNo
	ORDER BY a.MUSNO, o.Tarih DESC, o.NoId DESC;
 END