ALTER procEDURE [dbo].[sp_mb_XML_Customer_List_delphi] @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 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", 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.IslemTuru='Satış' and S.IslemNo !=STK.IslemNo AND S.IrsNo!='' AND S.IrsNo IS NOT NULL )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.FaturaNo=STK.FaturaNo AND S.IslemTuru='Satış' and S.IslemNo !=STK.IslemNo AND S.IrsNo!='' AND S.IrsNo IS NOT NULL )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 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' else case when STK.FaturaTipi=5 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 --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 (DD.IslemNo=@IslemNo or DD.IslemNo3=@IslemNo);