CREATE OR ALTER PROCEDURE [dbo].[sp_mb_XML_AccountingSupplierParty_List](@Entegrator int) AS update STOKHARDETBAG set eFaturaDurumu='' where eFaturaDurumu='Gönderiliyor' UPDATE ACARI SET Aciklama2='' WHERE Aciklama2=';00-00-0000;' --and IslemNo=@I SELECT SB.VERGINO as "VERGI_NO", --'1234567801' as "VERGI_NO", SB.UNVAN as "UNVAN", SB.KapiNo as "KAPI_NO", SB.SokakAdi as "SOKAK_ADI", SB.BinaAdi as "BINA_ADI", SB.BinaNo as "BINA_NO", SB.Ilce as "ILCE", SB.Il as "IL", SB.PostaKodu as "POSTA_KODU", SB.Mahalle as "MAHALLE", SB.UlkeAdi as "ULKE_ADI", SB.VERGIDA as "VERGI_DA", SB.EPosta1 as "EPOSTA", SB.TELNO as "TEL_NO", SB.Faks as "FAKS", SB.EPosta3 AS "MERSIS_NO", SB.OLisansNo AS "TICARET_SICIL_NO", SB.WebSitesi AS "WEB_SITE", EF_SABIT.MAIL_ADRES AS "ETIKET", EF_SABIT.ACIKLAMA AS "ACIKLAMA", EF_SABIT.ACIKLAMA2 AS "ACIKLAMA2", EF_SABIT.ACIKLAMA3 AS "ACIKLAMA3" , EF_SABIT.IBAN AS "IBAN", SB.Adres1, SB.Adres2 , EF_SABIT.AD as AD, EF_SABIT.SOYAD as SOYAD FROM ASABIT SB INNER JOIN EF_SABIT ON EF_SABIT.Entegrator=@Entegrator GO CREATE or alter PROCEDURE [dbo].[sp_mb_XML_Customer_List_delphi] @IslemNo nvarchar(30) AS -- [sp_mb_XML_Customer_List_delphi] 54323 --- 2022-10-26 -- 2025-02-06 Ü.Ç where STK.IslemTuru IN ('Satış','Alış İade') kriteri eklendi. 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", AMusteri1.email_2 AS "ETIKET_IRSALIYE", 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 (select top 1 OzelKod1 from STOKHARDETBAG where IslemNo=@IslemNo) 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) then 'SATIS' when FaturaTipi=6 and isnull(STK.TevkifatTutari,0)>0 then 'TEVKIFAT' else '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", round((DD.DovizKuru),2) 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.CariNo=AMUSTERI.MUSNO 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 and STK.IslemTuru IN ('Satış','Alış İade')) ; GO CREATE or ALTER procedure [dbo].[sp_mb_XML_DespatchAdvice_List] --1 @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, round(LST.ToplamBirimFiyat,2) ToplamBirimFiyat, round(SUM(LST.Miktar),3) Miktar, round(SUM(LST.KdvsizSatirToplami),2) KdvsizSatirToplami, round( SUM(LST.SatirIskontoToplami) ,2)SatirIskontoToplami, round(SUM(LST.Isksuz_Satir_Toplami) ,2)Isksuz_Satir_Toplami, round(SUM(LST.KdvToplami) ,2)KdvToplami, round(SUM(LST.Alt_Iskonto_Toplami),2) Alt_Iskonto_Toplami, round(SUM(LST.Tutar),2) Tutar, round(SUM(LST.SonTutar),2) SonTutar, LST.Iskonto_Aciklama FROM( SELECT DD.StokKodu, DD.StokAdi AS StokAdi, (FaturaMiktari) as Miktar, case when TopTutar>0 then (SonTutar-KdvTutari)/(DovizKuru) else 0 end as KdvsizSatirToplami,--Net tutar DD.Iskonto1 AS IskOran, case when Tutar>0 then ((FaturaMiktari* BirimFiyat*BirimMiktar) -Tutar )/(DovizKuru) else 0 end as SatirIskontoToplami, case when DovizTut>0 then (FaturaMiktari* BirimFiyat*BirimMiktar)/(DovizKuru) else 0 end as Isksuz_Satir_Toplami,--tutar DD.Kdv, (KdvTutari)/(DovizKuru) as KdvToplami, (Birim) as Birim, case when DovizTut>0 then (Tutar -(SonTutar-KdvTutari))/(DovizKuru) else 0 end as Alt_Iskonto_Toplami, case when TopTutar>0 then (Tutar)/(DovizKuru) else 0 end as Tutar, case when SonTutar >0 then (SonTutar)/(DovizKuru) else 0 end as SonTutar, case when TopTutar>0 then DD.BirimFiyat*DD.BirimMiktar/(DovizKuru) else 0 end AS ToplamBirimFiyat,--Fiyat CASE WHEN ISNULL (DD.Kdv,0)=0 THEN (SELECT top 1 Deger FROM Parametre WHERE Param='eFaturaMuafiyetAciklama') ELSE '' END AS "MUAFIYET", ( SELECT top 1 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 STOKHAR DD where 1=1 AND @IslemNo in(DD.IslemNo, DD.IslemNo3) AND (Tutar >0 or FaturaMiktari>0) )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_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)-(select MustahsilTut from mustahsil )),2) AS "TaxInclusiveAmount", round (((STK.SatirIskontoToplami+Alt_Iskonto_Toplami)),2) AS "AllowanceTotalAmount", round (((STK.SonTutar)-(select MustahsilTut from mustahsil )),2) 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 CREATE OR ALTER PROCEDURE [dbo].[sp_mb_XML_InvoiceLine_List_delphi] /* 2021 01 15 güncellendi. A.t*/ --20230713 tarihinde H.K tarafından güncellendi.Gruplama iptal edildi. -- 2025-09-12 A.T Sofor vs eirsaliye için düzenlendi. @IslemNo nvarchar(30) AS SELECT ROW_NUMBER() OVER (ORDER BY DD.IslemNo3,DD.IslemNo,DD.SiraNo ) SiraNo, DD.StokKodu, DD.StokAdi AS StokAdi, round ((FaturaMiktari),3) as Miktar, round (( (SonTutar-DD.KdvTutari)/ DB.DovizKur),2) as KdvsizSatirToplami, round (( (SonTutar-DD.OTVTutari)/ DB.DovizKur),2) as OTVsizSatirToplami, DD.Iskonto1 AS IskOran, round (( ((FaturaMiktari* BirimFiyat*BirimMiktar) -DD.Tutar )/ DB.DovizKur),2) as SatirIskontoToplami, round (( (FaturaMiktari* BirimFiyat*BirimMiktar)/ DB.DovizKur ),2) as Isksuz_Satir_Toplami,--tutar DD.Kdv, DD.OTVOrani, round (( (DD.KdvTutari)/ DB.DovizKur),2) as KdvToplami, round (( (DD.OTVTutari)/ DB.DovizKur),2) as OtvToplami, (Birim) as Birim, round (( (DD.Tutar -(SonTutar-DD.KdvTutari))/ DB.DovizKur ),2) as Alt_Iskonto_Toplami, round (( (DD.Tutar)/ DB.DovizKur),2) as Tutar, round (( (SonTutar)/ DB.DovizKur),2) as SonTutar, round (( DD.BirimFiyat*DD.BirimMiktar / DB.DovizKur),2) AS ToplamBirimFiyat,--Fiyat CASE WHEN ISNULL (DD.Kdv,0)=0 AND DB.OzelKod1 in('31','4') AND DB.YazarKasaFisNo<>2 THEN '351;Vergiden muaf.' WHEN ISNULL (DD.Kdv,0)=0 AND DB.OzelKod1 in('31','4') 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 top 1 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 top 1 MuafiyetKodu+';'+MuafiyetAciklamasi from StokVergiMuafiyetleri where StokKodu=DD.StokKodu and MuafiyetKodu=DB.EF_ISTISNA and MuafiyetKodu<>'000')<>';' THEN (select top 1 MuafiyetKodu+';'+MuafiyetAciklamasi from StokVergiMuafiyetleri where StokKodu=DD.StokKodu and MuafiyetKodu=DB.EF_ISTISNA) ELSE '' END AS "MUAFIYET", ( SELECT top 1 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 STOKHAR DD JOIN STOKHARDETBAG DB ON DB.IslemNo in (DD.IslemNo,DD.IslemNo3) where DB.FisTuru = 'F' AND @IslemNo in (DD.IslemNo, DD.IslemNo3) GO CREATE OR ALTER procedure [dbo].[sp_mb_XML_TaxSubtotalTypeName_List]( @IslemNo nvarchar(30) ) as select sum(1) as CalculationSequenceNumeric, Oran,Deger, round(sum(VergiToplami),2) "TaxAmount", round(sum(XX.SonTutar-XX.VergiToplami),2) "TaxableAmount", CASE WHEN Oran='KDV' and ISNULL (Deger,0)=0 THEN (SELECT Deger FROM Parametre WHERE Param='eFaturaMuafiyetAciklama') when sum(Tutar)=0 then '351;KısmiBeldesiz' ELSE '' END AS "MUAFIYET" ,VergiKodu from ( select Oran, Deger, VergiTutar, VergisizSatirToplami, VergiToplami, Isksuz_Satir_Toplami, SatirIskontoToplami, Alt_Iskonto_Toplami, STK.SonTutar, STK.Tutar, VergiKodu from STOKHAR STK cross apply ( values ('KDV', Kdv,KdvTutari , ((SonTutar-KdvTutari)/STK.DovizKuru),(KdvTutari/STK.DovizKuru), (FaturaMiktari* BirimFiyat*BirimMiktar/STK.DovizKuru), (((FaturaMiktari* BirimFiyat*BirimMiktar) -Tutar)/STK.DovizKuru), (((Tutar -(SonTutar-KdvTutari))/STK.DovizKuru)),'0015'), ('OTV', OTVOrani,OTVTutari, ((SonTutar-OTVTutari)/STK.DovizKuru), (OTVTutari/STK.DovizKuru) ,(FaturaMiktari* BirimFiyat*BirimMiktar/STK.DovizKuru) , (((FaturaMiktari* BirimFiyat*BirimMiktar) -Tutar)/STK.DovizKuru), (((Tutar -(SonTutar-OTVTutari))/STK.DovizKuru)),(select A_Muh_Kodu from STOKKARTI where StokNo=STK.StokNo)) ) c (Oran, Deger,VergiTutar,VergisizSatirToplami,VergiToplami,Isksuz_Satir_Toplami,SatirIskontoToplami,Alt_Iskonto_Toplami,VergiKodu) where @IslemNo in (STK.IslemNo,STK.IslemNo3) ) XX where ((XX.Oran='OTV' and XX.Deger>0) or (XX.Oran='KDV')) group by Oran,Deger,VergiKodu GO 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, round((SUM(LST.Tutar)/SUM(LST.Miktar)),2) ToplamBirimFiyat, round(SUM(LST.Miktar),2) Miktar, round(SUM(LST.KdvsizSatirToplami),2) KdvsizSatirToplami, round(SUM(LST.SatirIskontoToplami),2) SatirIskontoToplami, round(SUM(LST.Isksuz_Satir_Toplami),2) Isksuz_Satir_Toplami, round(SUM(LST.KdvToplami),2) KdvToplami, round(SUM(LST.Alt_Iskonto_Toplami),2) Alt_Iskonto_Toplami, round(SUM(LST.Tutar),2) Tutar, round(SUM(LST.SonTutar),2) SonTutar, LST.Iskonto_Aciklama, MAX(LST.OTVOrani) OTVOrani, round(sum(LST.OTVsizSatirToplami),2) OTVsizSatirToplami, round(sum(LST.OtvToplami),2) OtvToplami FROM( SELECT DD.StokKodu, DD.StokAdi AS StokAdi, sum (FaturaMiktari) as Miktar, sum ((SonTutar-DD.KdvTutari) / (DovizKuru)) as KdvsizSatirToplami, DD.Iskonto1 AS IskOran, sum (((FaturaMiktari* BirimFiyat*BirimMiktar) -DD.Tutar ) / (DovizKuru) ) as SatirIskontoToplami, sum(((FaturaMiktari* BirimFiyat*BirimMiktar) / (DovizKuru) )) as Isksuz_Satir_Toplami,--tutar DD.Kdv, sum ((DD.KdvTutari) / (DovizKuru)) as KdvToplami, (Birim) as Birim, (SUM((DD.SonTutar-DD.OTVTutari)/ DovizKuru)) as OTVsizSatirToplami, sum ((DD.Tutar -(SonTutar-DD.KdvTutari)) /(DovizKuru)) as Alt_Iskonto_Toplami, sum ((DD.Tutar) /DovizKuru) as Tutar, sum ((SonTutar) / DovizKuru) as SonTutar, sum ((DD.BirimFiyat*DD.BirimMiktar) / DovizKuru) 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 top 1 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 top 1 MuafiyetKodu+';'+MuafiyetAciklamasi from StokVergiMuafiyetleri where StokKodu=DD.StokKodu and MuafiyetKodu=DB.EF_ISTISNA and MuafiyetKodu<>'000')<>';' THEN (select top 1 MuafiyetKodu+';'+MuafiyetAciklamasi from StokVergiMuafiyetleri where StokKodu=DD.StokKodu and MuafiyetKodu=DB.EF_ISTISNA) ELSE '' END AS "MUAFIYET", ( SELECT top 1 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)/ DB.DovizKur) as OtvToplami from STOKHAR DD JOIN STOKHARDETBAG DB ON DB.IslemNo in (DD.IslemNo,DD.IslemNo3) where DB.FisTuru = 'F' AND (@IslemNo in (DD.IslemNo3,DD.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 PROC [dbo].[xml_EFatura_List] @IslemNo int ,@Ent int as -- declare @IslemNo int=112501, @Ent int=2 -- [xml_EFatura_List] 112501 , 2 EXEC sp_mb_XML_AccountingSupplierParty_List @Ent EXEC sp_mb_XML_Customer_List_delphi @IslemNo EXEC sp_mb_XML_TaxLegalMonetary_List @IslemNo EXEC sp_mb_XML_InvoiceLine_List_delphi @IslemNo EXEC sp_mb_XML_TaxSubtotalTypeName_List @IslemNo EXEC sp_mb_XML_TaxSubtotalType_List @IslemNo select FaturaMiktari,BirimFiyat,BirimMiktar, STK.Tutar from STOKHAR STK where @IslemNo in (STK.IslemNo,STK.IslemNo3) EXEC sp_mb_XML_InvoiceLine_List_Grup @IslemNo --Gruplama parametresi kullanılıyorsa --Bu 3 proc Kullanılıyormu bak??? --EXEC sp_mb_XML_InvoiceLine_List @IslemNo --EXEC sp_mb_XML_Customer_List @IslemNo --EXEC sp_mb_XML_DespatchAdvice_List @IslemNo -- Tevkifat istisna Müstahsile bakarak ,Diğer procleri kaldıralım.