     
CREATE OR ALTER      PROCEDURE  [dbo].[sp_mb_XML_TaxLegalMonetary_List]              
 @IslemNo nvarchar(30)              
 as          
 ;with mustahsil as (          
select isnull(sum(DovizTut),0) MustahsilTut from STOKHAR where IslemTuru='Müstahsil' and  IslemNo=@IslemNo           
)             
select            
           
round(STK.KdvToplami,2) AS "TaxAmount",              
round(STK.SonTutar,2)   
AS "TaxableAmount",              
--round(STK.KdvToplami,2)) AS "TaxAmount",              
STK.sayi AS "CalculationSequenceNumeric",              
round(STK.Isksuz_Satir_Toplami,2) AS  "LineExtensionAmount",              
(round(STK.KdvsizSatirToplami,2)) AS "TaxExclusiveAmount",              
--(round(STK.SonTutar,2))  
--round(STK.Isksuz_Satir_Toplami,2)+round(STK.KdvToplami,2)
(round(STK.SonTutar,2)-(select MustahsilTut from mustahsil )) AS "TaxInclusiveAmount",              
(round(STK.SatirIskontoToplami+Alt_Iskonto_Toplami,2)) AS "AllowanceTotalAmount",              
(round(STK.SonTutar,2)-(select MustahsilTut from mustahsil )) AS "PayableAmount",              
isnull(TevkifatKod,'') as TevkifatKod,              
isnull(TevkifatAd,'') as TevkifatAd,isnull(TevkifatOrani,0) TevkifatOrani,isnull(TevkifatTutari,0) TevkifatTutari              
            
 FROM              
(SELECT               
sum(1) as sayi,              
sum (FaturaMiktari* BirimFiyat*BirimMiktar/STK.DovizKuru) as Isksuz_Satir_Toplami,              
sum ( CASE WHEN ((FaturaMiktari* BirimFiyat*BirimMiktar) -STK.Tutar) <0.1 THEN 0 ELSE ((FaturaMiktari* BirimFiyat*BirimMiktar) -STK.Tutar)/STK.DovizKuru END  ) as SatirIskontoToplami,              
cast( sum (CASE WHEN (STK.Tutar -(SonTutar-STK.KdvTutari) )/STK.DovizKuru <0.1 THEN 0 ELSE (STK.Tutar -(SonTutar-STK.KdvTutari) )/STK.DovizKuru END ) as decimal(15,2))   
 as Alt_Iskonto_Toplami,               
sum ((SonTutar-STK.KdvTutari)/STK.DovizKuru) as  
KdvsizSatirToplami,              
sum (STK.KdvTutari/STK.DovizKuru) as KdvToplami,                
sum(STK.Tutar/STK.DovizKuru)-sum(isnull(t.TevkifatTutari,0)) as Tutar,              
sum(SonTutar/STK.DovizKuru)-sum(isnull(t.TevkifatTutari/STK.DovizKuru,0)) as SonTutar,          
t.Kod TevkifatKod,tb.TevkifatAdi TevkifatAd,convert(decimal, (t.TevkifatOrani)) TevkifatOrani,sum(t.TevkifatTutari/STK.DovizKuru) TevkifatTutari              
 from STOKHAR STK              
  inner join STOKHARDETBAG STB ON STB.IslemNo=STK.IslemNo    
   left join Tevkifat t on t.IslemNo2 = STK.IslemNo2  and t.StokNo=STK.StokNo          
   left join tblTevkifat tb on tb.Kod=t.Kod          
 where (STK.IslemTuru ='Satış' or STK.IslemTuru ='Alış İade'  )              
  AND (STK.IslemNo=@IslemNo or STK.IslemNo3=@IslemNo)     
group by t.Kod,tb.TevkifatAdi,t.TevkifatOrani           
  --AND STK.Tutar>0          
   ) STK;   
   
GO


