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);