 create or alter  procEDURE [dbo].[sp_mb_XML_Customer_List_delphi]              
 @IslemNo nvarchar(30)              
 AS               
  ---26-04-2022 - baglı irsler gelmiyordu.   irs.nolar tadilat yapıldı.          
              
SELECT top 1                
 Adres.VergiNo as "VERGI_NO",              
 Adres.VergiDairesi AS "VERGI_DAIRESI",              
--  '7230055623' as "VERGI_NO",              
  -- '2234567890' AS "VERGI_NO",               
--'1234567801' AS "VERGI_NO",               
-- AMUSTERI.TCKimlikNo AS "VERGI_NO",              
 null as "KAPI_NO",              
 AMUSTERI.MUSADRES2  as "SOKAK_ADI",              
 null as "BINA_ADI",              
 null as "BINA_NO",              
 AMUSTERI.MUSILCE  as "ILCE",              
 AMUSTERI.MUSIL as "IL",              
 null as "POSTA_KODU",              
 AMUSTERI.MUSADRES1 as "MAHALLE",              
 Adres.Ulke as "ULKE_ADI",              
 Adres.VergiDairesi as "VERGI_DA",              
 AMusteri1.email_1 AS "EPOSTA",              
 Adres.Tel1 as "TEL_NO",              
 Adres.Faks as "FAKS",              
 AMusteri1.WebAdresi_1 as "WEB_SITE",               
    AMUSTERI.MUSADI as "UNVANI",              
 CASE WHEN LEN(AMUSTERI.MUSVERNO)>10 THEN AMusteri1.KimlikAdi ELSE AMUSTERI.MUSADI END    AS "ADI",              
 CASE WHEN LEN(AMUSTERI.MUSVERNO)>10 THEN AMusteri1.KimlikSoyAdi ELSE '' END     AS "SOYADI",              
   -- 'urn:mail:defaultpk@pasaogluyumurta.com' AS "ETIKET" ,               
    AMusteri1.email_1 AS "ETIKET",              
  STK.FatSerNo+convert(varchar,year(getdate()))+ substring(CONVERT(VARCHAR,STK.FaturaNo),2,len(CONVERT(VARCHAR,STK.FaturaNo)))  as "EFATURA_NO",                 
    (SELECT CAST(S.IrsNo AS VARCHAR(MAX))  +';'  
    FROM              
 (              
select distinct S.IrsNo from STOKHARDETBAG S where  
S.IslemNo in (select IslemNo from STOKHAR WHERE @IslemNo in (IslemNo,IslemNo3)) and FisTuru='I' and S.CariNo=STK.CariNo  
  )S     
  ORDER BY S.IrsNo ASC             
    FOR XML PATH(''),TYPE   ) as "IRSALIYE_NO",   
  (SELECT CAST(S.IrsaliyeTarihi AS VARCHAR(MAX))+';'               
    FROM     
 (SELECT DISTINCT CONVERT(varchar(10), S.FaturaTarihi ,105) IrsaliyeTarihi FROM               
  STOKHARDETBAG S where S.IslemNo in (select IslemNo from STOKHAR WHERE @IslemNo in (IslemNo,IslemNo3)) and FisTuru='I' and S.CariNo=STK.CariNo   
         
  )S     
  ORDER BY S.IrsaliyeTarihi ASC             
    FOR XML PATH(''),TYPE   ) as "IRSALIYE_TARIHI",     
CASE               
WHEN ISNULL (DD.Kdv,0)=0 AND STK.OzelKod1='31' AND STK.YazarKasaFisNo<>2 and STK.EF_ISTISNA=0  THEN    '351;Vergiden muaf.'                
WHEN  ISNULL (DD.Kdv,0)=0 AND STK.OzelKod1='31' AND STK.YazarKasaFisNo=2 and STK.EF_ISTISNA=0  THEN   '11/1-a Mal ihracatı'               
when SUBSTRING(ISNULL(OzelKod1,'00'), 1, 1)=3 and ISNULL (DD.Kdv,0)=0 and STK.EF_ISTISNA=0  then '701;11/1-c md. Kapsamındaki İhraç Kayıtlı Satış'               
when DD.TopTutar=0 and STK.EF_ISTISNA=0 then '351;KısmiBedelsiz'           
 WHEN ISNULL (DD.Kdv,0)=0  and ISNULL(STK.EF_ISTISNA,0)=0  THEN   (SELECT Deger FROM Parametre WHERE Param='eFaturaMuafiyetAciklama')               
ELSE (SELECT top 1  MuafiyetKodu+';'+MuafiyetAciklamasi FROM StokVergiMuafiyetleri  WHERE MuafiyetKodu= STK.EF_ISTISNA )          
 END AS  "MUAFIYET",              
 --(SELECT CONVERT(VARCHAR,DEGISKEN_KODU)+'-'+DEGISKEN_ADI FROM EF_DEGISKENLER WHERE DEGISKEN_KODU=STK.EF_ISTISNA) AS  "MUAFIYET",              
 STK.UUID,              
 STK.Ack1 AS "ACIKLAMA1",              
 STK.Ack2 AS "ACIKLAMA2",              
 STK.Ack3 AS "ACIKLAMA3",      
 STK.Ack4 as "ACIKLAMA4",              
 case  when FaturaTipi=6 then 'KAMU' else  CASE YazarKasaFisNo WHEN  1 THEN 'TEMELFATURA' WHEN 2 THEN 'IHRACAT' ELSE 'TICARIFATURA' end END "FATURA_TUR",                  
 CASE SUBSTRING(ISNULL(OzelKod1,'00'), 1, 1) WHEN 0    THEN 'SATIS' WHEN 1 THEN 'TEVKIFAT' WHEN 2 THEN 'IADE' WHEN 3 THEN 'IHRACKAYITLI' WHEN 4 THEN 'ISTISNA' else case when STK.FaturaTipi in (5,6) then 'SATIS' end END  AS "SATIS_TUR",              
 SUBSTRING(ISNULL(OzelKod1,'00'), 2, 1) AS "ILETIM_TUR",              
    CASE WHEN STK.AltIskYuzde1 >0 THEN  'Alt iskonto oranları : ' +CONVERT(VARCHAR,STK.AltIskYuzde1) ELSE NULL  END +              
 CASE WHEN STK.AltIskYuzde2 >0 THEN   + '|'+CONVERT(VARCHAR,STK.AltIskYuzde2) ELSE NULL END +              
 CASE WHEN STK.AltIskYuzde3 >0 THEN   +         
 '|'+CONVERT(VARCHAR,STK.AltIskYuzde3) ELSE NULL  END  as "ALT_ISKONTO_ACIKLAMA",              
     DOVIZT.IsoKodu AS "DOVIZ_KODU",              
 CASE WHEN STK.Tutar=0 AND STK.DovizTut=0 THEN (DD.SonTutar/DD.SonTutarDov) ELSE(STK.Tutar/STK.DovizTut) END AS "DOVIZ_CARPAN",              
  case when Adres.eFaturaMukellefimi='true' then 1 else 0 end EFatura ,              
  AMUSTERI.MUSNO CariNo,  
  STK.FaturaTipi "TIP"  
 --AMusteri1.email_1 as "ETIKT"               
  --'urn:mail:defaultpk@testkurumelpo.com.tr' AS "ETIKET"         
 --ISNULL(AMusteri1.KimlikAdi,'AD') as "ADI",              
 --ISNULL(AMusteri1.KimlikSoyAdi,'SOYAD') as "SOYADI"              
 FROM  AMUSTERI               
INNER JOIN  Adres ON AMUSTERI.MUSNO = Adres.CariNo              
INNER JOIN STOKHARDETBAG STK ON STK.CariKodu=AMUSTERI.MUSKOD              
JOIN STOKHAR DD ON DD.IslemNo=STK.IslemNo              
INNER JOIN AMusteri1 ON AMusteri1.CariNo=AMUSTERI.MUSNO               
INNER JOIN DOVIZT ON  DOVIZT.NoId=STK.DovizCinsi              
--INNER JOIN FatAciklama  on  FatAciklama.IslemNo2=STK.IslemNo              
WHERE (@IslemNo in(DD.IslemNo,DD.IslemNo3) and STK.FaturaAdresNo=Adres.AdresNo) ;    
  
go

 CREATE or alter PROCEDURE [dbo].[sp_mb_XML_Customer_List]  
 @IslemNo nvarchar(30)  
 AS   
  
  
SELECT top 1    
 AMUSTERI.MUSVERNO as "VERGI_NO",  
 AMUSTERI.MUSVERGI AS "VERGI_DAIRESI",  
--  '7230055623' as "VERGI_NO",  
  -- '2234567890' AS "VERGI_NO",   
--'1234567801' AS "VERGI_NO",   
-- AMUSTERI.TCKimlikNo AS "VERGI_NO",  
 null as "KAPI_NO",  
 AMUSTERI.MUSADRES2  as "SOKAK_ADI",  
 null as "BINA_ADI",  
 null as "BINA_NO",  
 AMUSTERI.MUSILCE  as "ILCE",  
 AMUSTERI.MUSIL as "IL",  
 null as "POSTA_KODU",  
 AMUSTERI.MUSADRES1 as "MAHALLE",  
 null as "ULKE_ADI",  
 Adres.VergiDairesi as "VERGI_DA",  
 AMusteri1.email_1 AS "EPOSTA",  
 Adres.Tel1 as "TEL_NO",  
 Adres.Faks as "FAKS",  
 AMusteri1.WebAdresi_1 as "WEB_SITE",   
    AMUSTERI.MUSADI as "UNVANI",  
 CASE WHEN LEN(AMUSTERI.MUSVERNO)>10 THEN AMusteri1.KimlikAdi ELSE NULL END    AS "ADI",  
 CASE WHEN LEN(AMUSTERI.MUSVERNO)>10 THEN AMusteri1.KimlikSoyAdi ELSE NULL END     AS "SOYADI",  
   -- 'urn:mail:defaultpk@pasaogluyumurta.com' AS "ETIKET" ,   
    AMusteri1.email_1 AS "ETIKET",  
 RIGHT('0000000000' + CONVERT(VARCHAR,STK.FaturaNo), 9) as "EFATURA_NO",   
    (SELECT '--' + CAST(S.IrsNo AS VARCHAR(MAX))   
    FROM  
 (  
 SELECT DISTINCT IrsNo FROM   
  STOKHARDETBAG S WHERE  S.FaturaNo=STK.FaturaNo and S.IslemNo !=STK.IslemNo  AND S.IrsNo!='' AND S.IrsNo IS NOT NULL  
  )S  
    FOR XML PATH(''),TYPE) as "IRSALIYE_NO",  
 (SELECT Deger FROM Parametre WHERE Param='eFaturaMuafiyetAciklama') AS  "MUAFIYET",  
 --(SELECT Deger FROM Parametre WHERE Param=STK.EF_ISTISNA) AS  "MUAFIYET",  
 STK.UUID,  
 STK.Ack1 AS "ACIKLAMA1",  
 STK.Ack2 AS "ACIKLAMA2",  
 STK.Ack3 AS "ACIKLAMA3",  
 CASE YazarKasaFisNo WHEN  1 THEN 'TEMELFATURA' WHEN 2 THEN 'IHRACAT' ELSE 'TICARIFATURA' END "FATURA_TUR",      
 CASE SUBSTRING(ISNULL(OzelKod1,'00'), 1, 1) WHEN 0 THEN 'SATIS' WHEN 1 THEN 'TEVKIFAT' WHEN 2 THEN 'IADE' WHEN 3 THEN 'IHRACKAYITLI' WHEN 4 THEN 'ISTISNA'  END  AS "SATIS_TUR",  
 SUBSTRING(ISNULL(OzelKod1,'00'), 2, 1) AS "ILETIM_TUR",  
    CASE WHEN STK.AltIskYuzde1 >0 THEN  'Alt iskonto oranları : ' +CONVERT(VARCHAR,STK.AltIskYuzde1) ELSE NULL  END +  
 CASE WHEN STK.AltIskYuzde2 >0 THEN   + '|'+CONVERT(VARCHAR,STK.AltIskYuzde2) ELSE NULL END +  
 CASE WHEN STK.AltIskYuzde3 >0 THEN   + '|'+CONVERT(VARCHAR,STK.AltIskYuzde3) ELSE NULL  END  as "ALT_ISKONTO_ACIKLAMA",  
     DOVIZT.IsoKodu AS "DOVIZ_KODU",  
  (STK.Tutar/STK.DovizTut) AS "DOVIZ_CARPAN"  
  
 --AMusteri1.email_1 as "ETIKT"   
  --'urn:mail:defaultpk@testkurumelpo.com.tr' AS "ETIKET"   
 --ISNULL(AMusteri1.KimlikAdi,'AD') as "ADI",  
 --ISNULL(AMusteri1.KimlikSoyAdi,'SOYAD') as "SOYADI"  
 FROM  AMUSTERI   
INNER JOIN  Adres ON AMUSTERI.MUSNO = Adres.CariNo  
INNER JOIN STOKHARDETBAG STK ON STK.CariKodu=AMUSTERI.MUSKOD  
INNER JOIN AMusteri1 ON AMusteri1.CariNo=AMUSTERI.MUSNO   
INNER JOIN DOVIZT ON  DOVIZT.NoId=STK.DovizCinsi  
--INNER JOIN FatAciklama  on  FatAciklama.IslemNo2=STK.IslemNo  
WHERE STK.IslemNo=@IslemNo;

go

--bencan-04-02 - --güncellendi --bak --27-04
---adres vergino alamayan yerde çalıştı. 26sı procle ayar edin.
  

GO


    
CREATE  or alter  PROC  [dbo].[sp_mb_XML_TaxLegalMonetary_List]          
 @IslemNo nvarchar(30)          
 AS           
          
select           
round(STK.KdvToplami,2) AS "TaxAmount",          
round(STK.SonTutar-STK.KdvToplami,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)) AS "TaxInclusiveAmount",          
(round(STK.SatirIskontoToplami+Alt_Iskonto_Toplami,2)) AS "AllowanceTotalAmount",          
(round(STK.SonTutar,2)) AS "PayableAmount",          
isnull(TevkifatKod,'') as TevkifatKod,          
isnull(TevkifatAd,'') as TevkifatAd,isnull(TevkifatOrani,0) TevkifatOrani,isnull(TevkifatTutari,0) TevkifatTutari          
        
 FROM          
(SELECT      distinct       
sum(1) as sayi,          
sum (FaturaMiktari* BirimFiyat*BirimMiktar*CASE WHEN TopTutar>0 THEN (STK.DovizTut/TopTutar) ELSE 0 END) as Isksuz_Satir_Toplami,          
sum ( CASE WHEN ((FaturaMiktari* BirimFiyat*BirimMiktar) -STK.Tutar) <0.1 THEN 0 ELSE ((FaturaMiktari* BirimFiyat*BirimMiktar) -STK.Tutar)*CASE WHEN TopTutar>0 THEN (STK.DovizTut/TopTutar) ELSE 0 END END  ) as SatirIskontoToplami,          
cast( sum (CASE WHEN (STK.Tutar -(SonTutar-STK.KdvTutari) )*CASE WHEN TopTutar>0 THEN (STK.DovizTut/TopTutar) ELSE 0 END <0.1 THEN 0 ELSE (STK.Tutar -(SonTutar-STK.KdvTutari) )*CASE WHEN TopTutar>0 THEN (STK.DovizTut/TopTutar) ELSE 0 END END ) as decimal(
  
    
15,2)) as Alt_Iskonto_Toplami,           
sum ((SonTutar-STK.KdvTutari)*CASE WHEN TopTutar>0 THEN (STK.DovizTut/TopTutar) ELSE 0 END) as KdvsizSatirToplami,          
sum (STK.KdvTutari*CASE WHEN TopTutar>0 THEN (STK.DovizTut/TopTutar) ELSE 0 END) as KdvToplami,            
sum(STK.Tutar*CASE WHEN TopTutar>0 THEN (STK.DovizTut/TopTutar) ELSE 0 END)-sum(isnull(STB.TevkifatTutari,0)) as Tutar,          
sum(SonTutar*CASE WHEN TopTutar>0 THEN (STK.DovizTut/TopTutar) ELSE 0 END)-sum(isnull(STB.TevkifatTutari,0)) as SonTutar,    
max(STB.TevkifatKod) TevkifatKod,max(STB.TevkifatAdi) TevkifatAd,max(STB.TevkifatOrani) TevkifatOrani,sum(STB.TevkifatTutari) TevkifatTutari          
from STOKHARDETBAG STB ,STOKHAR STK                
where (STB.IslemNo = STK.IslemNo or STB.IslemNo = STK.IslemNo3)  
AND STK.IslemTuru in ('Satış','Alış İade'  )           
AND STB.IslemNo = @IslemNo    
         
  --AND STK.Tutar>0      
   ) STK; 

   go

   -- sp_helptext sp_mb_XML_InvoiceLine_List_Grup    
    
--  sp_mb_XML_InvoiceLine_List_Grup 452063      
        
CREATE  or alter   procedure [dbo].[sp_mb_XML_InvoiceLine_List_Grup] -- @IslemNo          
 @IslemNo nvarchar(30)            
 AS   
 SELECT            
 ROW_NUMBER() OVER (ORDER BY LST.StokKodu ) "SiraNo"  ,            
 LST.StokKodu,            
 LST.StokAdi,            
 LST.IskOran,              
 LST.Kdv,            
 LST.Birim,            
 LST.ISO_BIRIM,            
 LST.MUAFIYET,            
 SUM(LST.Tutar)/SUM(LST.Miktar) ToplamBirimFiyat,            
 SUM(LST.Miktar) Miktar,            
 SUM(LST.KdvsizSatirToplami) KdvsizSatirToplami,            
 SUM(LST.SatirIskontoToplami) SatirIskontoToplami,            
 SUM(LST.Isksuz_Satir_Toplami) Isksuz_Satir_Toplami,            
 SUM(LST.KdvToplami) KdvToplami,            
 SUM(LST.Alt_Iskonto_Toplami) Alt_Iskonto_Toplami,            
 SUM(LST.Tutar) Tutar,            
 SUM(LST.SonTutar) SonTutar,             
 LST.Iskonto_Aciklama,          
  MAX(LST.OTVOrani) OTVOrani,              
 sum(LST.OTVsizSatirToplami) OTVsizSatirToplami,              
 sum(LST.OtvToplami) OtvToplami              
 FROM(            
SELECT            
DD.StokKodu,            
DD.StokAdi AS StokAdi,            
sum (FaturaMiktari) as Miktar,            
sum ((SonTutar-DD.KdvTutari)* case when TopTutar>0 then (DD.DovizTut/TopTutar) else 0 end ) as KdvsizSatirToplami,            
DD.Iskonto1 AS IskOran,            
sum (((FaturaMiktari* BirimFiyat*BirimMiktar) -DD.Tutar )* case when TopTutar>0 then (DD.DovizTut/TopTutar) else 0 end ) as SatirIskontoToplami,            
sum(((FaturaMiktari* BirimFiyat*BirimMiktar)* case when TopTutar>0 then (DD.DovizTut/TopTutar) else 0 end )) as Isksuz_Satir_Toplami,--tutar            
 DD.Kdv,            
sum ((DD.KdvTutari)* case when DD.TopTutar>0 then (DD.DovizTut/TopTutar) else 0 end) as KdvToplami,              
(Birim) as Birim,            
(SUM((DD.SonTutar-DD.OTVTutari)* CASE WHEN DD.TopTutar>0 THEN (DD.DovizTut/TopTutar) ELSE 0 END)) as OTVsizSatirToplami,             
sum ((DD.Tutar -(SonTutar-DD.KdvTutari))* case when TopTutar>0 then (DD.DovizTut/TopTutar) else 0 end) as Alt_Iskonto_Toplami,             
sum ((DD.Tutar)* case when TopTutar>0 then (DD.DovizTut/TopTutar) else 0 end ) as Tutar,            
sum ((SonTutar)*(DD.DovizTut/TopTutar)) as SonTutar,            
sum ((DD.BirimFiyat*DD.BirimMiktar)* case when TopTutar>0 then (DD.DovizTut/TopTutar) else 0 end ) AS ToplamBirimFiyat,--Fiyat            
CASE WHEN ISNULL (DD.Kdv,0)=0 AND MAX(DB.OzelKod1)='31' AND MAX(DB.YazarKasaFisNo)<>2 THEN    '351;Vergiden muaf.'            
WHEN  ISNULL (DD.Kdv,0)=0 AND MAX(DB.OzelKod1)='31' AND MAX(DB.YazarKasaFisNo)=2 THEN   '11/1-a Mal ihracatı'           
when SUBSTRING(ISNULL(MAX(DB.OzelKod1),'00'), 1, 1)=3 and ISNULL (DD.Kdv,0)=0 and DB.EF_ISTISNA=0  then '701;11/1-c md. Kapsamındaki İhraç Kayıtlı Satış'              
when SUM(DD.TopTutar)=0 and MAX(DB.EF_ISTISNA)=0 then '351;Kısmibedelsiz'           
 WHEN ISNULL (DD.Kdv,0)=0 AND (select MuafiyetKodu+';'+MuafiyetAciklamasi from StokVergiMuafiyetleri where StokKodu=DD.StokKodu and MuafiyetKodu=DB.EF_ISTISNA and MuafiyetKodu<>'000')=';'  THEN             
    '351;Vergiden muaf.'           
 WHEN ISNULL (DD.Kdv,0)=0 AND (select MuafiyetKodu+';'+MuafiyetAciklamasi from StokVergiMuafiyetleri where StokKodu=DD.StokKodu and MuafiyetKodu=DB.EF_ISTISNA and MuafiyetKodu<>'000')<>';'  THEN           
  (select MuafiyetKodu+';'+MuafiyetAciklamasi from StokVergiMuafiyetleri where StokKodu=DD.StokKodu and MuafiyetKodu=DB.EF_ISTISNA) ELSE ''    END  AS  "MUAFIYET",          
 ( SELECT IsoBirimKodu FROM BIRIM b where b.BirimAdi=DD.Birim  )   AS ISO_BIRIM,            
 CASE WHEN DD.Iskonto1 >0 then '%'+CAST(DD.Iskonto1 as varchar(10))    end +            
 CASE WHEN DD.Iskonto2 >0 then  '%'+CAST(DD.Iskonto2 as varchar(10))  else '' end+            
 CASE WHEN DD.Iskonto3 >0 then  '%'+CAST(DD.Iskonto3 as varchar(10))  else '' end+            
 CASE WHEN DD.Iskonto4 >0 then  '%'+CAST(DD.Iskonto4 as varchar(10))  else '' end as Iskonto_Aciklama,          
  DD.OTVOrani,          
  SUM((DD.OTVTutari)* CASE WHEN TopTutar>0 THEN (DD.DovizTut/TopTutar) ELSE 0 END) as OtvToplami              
            
from STOKHARDETBAG DB ,STOKHAR DD                
where (DB.IslemNo = DD.IslemNo or DB.IslemNo = DD.IslemNo3)  
AND DD.IslemTuru in ('Satış','Alış İade'  )           
AND DB.IslemNo = @IslemNo       
AND DD.Tutar >0   group by StokKodu,StokAdi,Iskonto1,Kdv,Birim,Iskonto2,Iskonto3,Iskonto4,EF_ISTISNA,DD.OTVOrani      
)LST            
GROUP BY             
LST.StokKodu,            
LST.StokAdi,            
LST.IskOran,            
LST.Kdv,            
LST.Birim,            
LST.ISO_BIRIM,            
LST.MUAFIYET,            
LST.ToplamBirimFiyat,            
Iskonto_Aciklama

go
    
CREATE  or alter PROCEDURE [dbo].[sp_mb_XML_InvoiceLine_List_delphi]        
   /*    
       
   2021 01 15 güncellendi. A.t*/    
 @IslemNo nvarchar(30)        
 AS          
 SELECT        
 ROW_NUMBER() OVER (ORDER BY LST.StokKodu ) "SiraNo"  ,        
 LST.StokKodu,        
 LST.StokAdi,        
 LST.IskOran,  --LST.IskOran,        
 LST.Kdv,        
 LST.Birim,        
 LST.ISO_BIRIM,        
 LST.MUAFIYET,        
 LST.ToplamBirimFiyat,        
 SUM(LST.Miktar) Miktar,        
 SUM(LST.KdvsizSatirToplami) KdvsizSatirToplami,        
 SUM(LST.SatirIskontoToplami) SatirIskontoToplami,        
 SUM(LST.Isksuz_Satir_Toplami) Isksuz_Satir_Toplami,        
 SUM(LST.KdvToplami) KdvToplami,        
 SUM(LST.Alt_Iskonto_Toplami) Alt_Iskonto_Toplami,        
 SUM(LST.Tutar) Tutar,        
 SUM(LST.SonTutar) SonTutar,         
 LST.Iskonto_Aciklama,        
 LST.OTVOrani,        
 sum(LST.OTVsizSatirToplami) OTVsizSatirToplami,        
 sum(LST.OtvToplami) OtvToplami        
        
 FROM(        
SELECT       
         
DD.StokKodu,        
DD.StokAdi AS StokAdi,        
(FaturaMiktari) as Miktar,        
(SonTutar-DD.KdvTutari)* CASE WHEN TopTutar>0 THEN (DD.DovizTut/TopTutar) ELSE 0 END as KdvsizSatirToplami,        
(SonTutar-DD.OTVTutari)* CASE WHEN TopTutar>0 THEN (DD.DovizTut/TopTutar) ELSE 0 END as OTVsizSatirToplami,        
DD.Iskonto1 AS IskOran,        
((FaturaMiktari* BirimFiyat*BirimMiktar) -DD.Tutar )* CASE WHEN TopTutar>0 THEN (DD.DovizTut/TopTutar) ELSE 0 END as SatirIskontoToplami,        
(FaturaMiktari* BirimFiyat*BirimMiktar)* CASE WHEN TopTutar>0 THEN (DD.DovizTut/TopTutar) ELSE 0 END  as Isksuz_Satir_Toplami,--tutar        
 DD.Kdv,        
 DD.OTVOrani,        
(DD.KdvTutari)* CASE WHEN TopTutar>0 THEN (DD.DovizTut/TopTutar) ELSE 0 END as KdvToplami,          
(DD.OTVTutari)* CASE WHEN TopTutar>0 THEN (DD.DovizTut/TopTutar) ELSE 0 END as OtvToplami,          
(Birim) as Birim,        
(DD.Tutar -(SonTutar-DD.KdvTutari))* CASE WHEN TopTutar>0 THEN (DD.DovizTut/TopTutar) ELSE 0 END as Alt_Iskonto_Toplami,         
(DD.Tutar)* CASE WHEN TopTutar>0 THEN (DD.DovizTut/TopTutar) ELSE 0 END as Tutar,        
(SonTutar)* CASE WHEN TopTutar>0 THEN (DD.DovizTut/TopTutar) ELSE 0 END as SonTutar,        
DD.BirimFiyat*DD.BirimMiktar* CASE WHEN TopTutar>0 THEN (DD.DovizTut/TopTutar) ELSE 0 END AS ToplamBirimFiyat,--Fiyat        
CASE WHEN ISNULL (DD.Kdv,0)=0 AND DB.OzelKod1='31' AND DB.YazarKasaFisNo<>2 THEN    '351;Vergiden muaf.'      
WHEN  ISNULL (DD.Kdv,0)=0 AND DB.OzelKod1='31' AND DB.YazarKasaFisNo=2 THEN   '11/1-a Mal ihracatı'     
when SUBSTRING(ISNULL(DB.OzelKod1,'00'), 1, 1)=3 and ISNULL (DD.Kdv,0)=0 and DB.EF_ISTISNA=0  then '701;11/1-c md. Kapsamındaki İhraç Kayıtlı Satış'        
when DD.TopTutar=0 and DB.EF_ISTISNA=0 then '351;Kısmibedelsiz'     
 WHEN ISNULL (DD.Kdv,0)=0 AND (select MuafiyetKodu+';'+MuafiyetAciklamasi from StokVergiMuafiyetleri where StokKodu=DD.StokKodu and MuafiyetKodu=DB.EF_ISTISNA and MuafiyetKodu<>'000')=';'  THEN       
    '351;Vergiden muaf.'     
 WHEN ISNULL (DD.Kdv,0)=0 AND (select MuafiyetKodu+';'+MuafiyetAciklamasi from StokVergiMuafiyetleri where StokKodu=DD.StokKodu and MuafiyetKodu=DB.EF_ISTISNA and MuafiyetKodu<>'000')<>';'  THEN     
  (select MuafiyetKodu+';'+MuafiyetAciklamasi from StokVergiMuafiyetleri where StokKodu=DD.StokKodu and MuafiyetKodu=DB.EF_ISTISNA) ELSE ''    END  AS  "MUAFIYET",        
 ( SELECT IsoBirimKodu FROM BIRIM b where b.BirimAdi=DD.Birim  )   AS ISO_BIRIM,        
 CASE WHEN DD.Iskonto1 >0 then '%'+CAST(DD.Iskonto1 as varchar(10))    end +        
 CASE WHEN DD.Iskonto2 >0 then  '%'+CAST(DD.Iskonto2 as varchar(10))  else '' end+        
 CASE WHEN DD.Iskonto3 >0 then  '%'+CAST(DD.Iskonto3 as varchar(10))  else '' end+        
 CASE WHEN DD.Iskonto4 >0 then  '%'+CAST(DD.Iskonto4 as varchar(10))  else '' end as "Iskonto_Aciklama"            
from STOKHARDETBAG DB ,STOKHAR DD                
where (DB.IslemNo = DD.IslemNo or DB.IslemNo = DD.IslemNo3)  
AND DD.IslemTuru in ('Satış','Alış İade'  )           
AND DB.IslemNo = @IslemNo
--AND DD.Tutar >0         
)LST        
GROUP BY         
LST.StokKodu,        
LST.StokAdi,        
LST.IskOran,       
LST.Kdv,        
LST.Birim,        
LST.ISO_BIRIM,        
LST.MUAFIYET,        
LST.ToplamBirimFiyat,        
Iskonto_Aciklama,        
LST.OTVOrani; 

go
go

  

  
CREATE or alter  PROCEDURE  [dbo].[sp_mb_XML_TaxSubtotalType_List]    
 @IslemNo nvarchar(30)    
 AS     
    
select     
round(STK.KdvToplami,2) AS "TaxAmount",    
round(STK.SonTutar-STK.KdvToplami,2) AS "TaxableAmount",     
STK.sayi AS "CalculationSequenceNumeric",     
Kdv as "KDV_ORANI" ,     
CASE WHEN ISNULL (Kdv,0)=0 THEN (SELECT Deger FROM Parametre WHERE Param='eFaturaMuafiyetAciklama')     
when STK.Tutar=0  then '351;KısmiBeldesiz'     
ELSE '' END AS  "MUAFIYET"    
 FROM    
(SELECT     
sum(1) as sayi,    
sum (FaturaMiktari* BirimFiyat*BirimMiktar* CASE WHEN TopTutar>0 THEN (DovizTut/TopTutar) ELSE 0 END) as Isksuz_Satir_Toplami,    
sum (((FaturaMiktari* BirimFiyat*BirimMiktar) -Tutar)* CASE WHEN TopTutar>0 THEN (DovizTut/TopTutar) ELSE 0 END  ) as SatirIskontoToplami,    
cast( sum (((Tutar -(SonTutar-KdvTutari))* CASE WHEN TopTutar>0 THEN (DovizTut/TopTutar) ELSE 0 END )) as decimal(15,2)) as Alt_Iskonto_Toplami,     
sum ((SonTutar-KdvTutari)*CASE WHEN TopTutar>0 THEN (DovizTut/TopTutar) ELSE 0 END) as KdvsizSatirToplami,    
sum (KdvTutari*CASE WHEN TopTutar>0 THEN (DovizTut/TopTutar) ELSE 0 END) as KdvToplami,      
sum(Tutar*CASE WHEN TopTutar>0 THEN (DovizTut/TopTutar) ELSE 0 END) as Tutar,    
sum(SonTutar*CASE WHEN TopTutar>0 THEN (DovizTut/TopTutar) ELSE 0 END) as SonTutar,    
Kdv      
 from STOKHAR STK  where (IslemTuru ='Satış' or IslemTuru ='Alış İade'  )    
  AND  (STK.IslemNo=@IslemNo or STK.IslemNo3=@IslemNo)    
 -- AND Tutar>0    
 group by Kdv,OTVOrani) STK;    

 go

  

	  -- sp_helptext sp_mb_XML_InvoiceLine_List_delphi 452063   
 -- sp_mb_XML_InvoiceLine_List 452063  
