--04-30 tarihli dosyaları kullanır.  Ürün gruplama - Bağlı irsaliyeler - tm

/****** Object:  StoredProcedure [dbo].[sp_mb_XML_AccountingSupplierParty_List]    Script Date: 12.05.2021 16:46:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_mb_XML_AccountingSupplierParty_List]
 AS 
  
 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"
 FROM ASABIT SB
INNER JOIN EF_SABIT  ON 1=1;
GO
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_Customer_List]    Script Date: 12.05.2021 16:46:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_Customer_List_delphi]    Script Date: 12.05.2021 16:46:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  CREATE procEDURE [dbo].[sp_mb_XML_Customer_List_delphi]        
 @IslemNo nvarchar(30)        
 AS         
;with Islemler as (
SELECT STK.IslemNo,STK.FaturaNo 
  
	  FROM  STOKHAR DD         
JOIN  STOKHARDETBAG STK ON DD.IslemNo=STK.IslemNo           
WHERE @IslemNo IN (DD.IslemNo,DD.IslemNo3)--
)
 
 , Irsaliye as(
 SELECT   
      
  replace( replace(    
(
    SELECT distinct S.IrsNo
             from Islemler STK
  cross apply STOKHARDETBAG  S WHERE  S.FaturaNo=STK.FaturaNo and (S.IslemNo !=STK.IslemNo)  AND ISNULL(S.IrsNo,'')!='' 
  AND S.IrsNo!=STK.FaturaNo
                  ORDER BY S.IrsNo  asc
				  FOR XML PATH('')
) ,'<IrsNo>',''),'</IrsNo>','--')  AS IrsaliyeNo)
        
        
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  IrsaliyeNo FROM  Irsaliye        
       
  ) "IRSALIYE_NO",        
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   STOKHAR DD         
JOIN  STOKHARDETBAG STK ON DD.IslemNo=STK.IslemNo  
INNER JOIN AMUSTERI ON STK.CariKodu=AMUSTERI.MUSKOD     
INNER JOIN  Adres ON AMUSTERI.MUSNO = Adres.CariNo        
   
      
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); 
GO
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_FaturaList]    Script Date: 12.05.2021 16:46:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE  [dbo].[sp_mb_XML_FaturaList]
 @MusKod nvarchar(100),
 @V_ILKTARIH  DATETIME,
 @V_SONTARIH  DATETIME,
 @V_TUR nvarchar(100),
 @V_SERI_NO nvarchar(100)
 AS 

SELECT 
STK.IslemNo AS "ISLEMNO",
STK.CariKodu AS "CARIKODU",
STK.CariAdi AS "CARIADI",
STK.Tutar AS "TUTAR",
STK.FaturaNo AS "FATURANO",
STK.eFaturaDurumu AS "DURUMU",
AMUS.email_1 AS	"MAIL",
STK.FaturaTarihi AS "FATURATARIHI",
--GETDATE() AS "FATURATARIHI"	,
STK.FatSerNo AS "SERI_NO",
CONVERT(varchar,eFaturaGonderimTarihi,104) +'/'+STK.UUID as "FAT_YOL",
CONVERT(varchar,eFaturaGonderimTarihi,104) AS "EFATURAGONDERIMTARIH"

--'28.01.2020/11d5f9e5-a44a-4649-9007-2d610a8fd116' as "FAT_YOL"
--MUS.TCKimlikNo AS "MUS_VERGI_NO",
--SB.VERGINO AS "FRM_VERGI_NO",
--'urn:mail:defaultgb@testkurumelpo.com.tr' AS "GONDERICI_ETIKET",
--'urn:mail:defaultpk@testkurumelpo.com.tr' AS "ALICI_ETIKET"
FROM
STOKHARDETBAG STK
INNER JOIN Adres AD ON AD.CariKodu=STK.CariKodu
INNER JOIN AMusteri1 AMUS ON AMUS.CariNo=STK.CariNo 
INNER JOIN AMUSTERI MUS ON STK.CariKodu=MUS.MUSKOD  
--INNER JOIN ASABIT SB ON 1=1
WHERE 
MUS.MUSVERNO IS NOT NULL AND MUS.MUSVERNO <>'' AND 
AD.AdresNo=STK.FaturaAdresNo AND STK.FatSerNo LIKE '%'+@V_SERI_NO+'%' AND
((( CONVERT(datetime,FaturaTarihi,104) BETWEEN  CONVERT(datetime,@V_ILKTARIH,104) AND CONVERT(datetime,@V_SONTARIH,104)+1)
OR (@V_ILKTARIH IS NULL AND convert(varchar,STK.FaturaTarihi,23)>convert(varchar, getdate()-10, 23) )
)AND (IslemTuru ='Satış' or IslemTuru ='Alış İade'  ) and FisTuru='F')
AND
(
	( 
		(
		(@V_TUR IS NULL OR @V_TUR ='' AND (UUID IS NULL OR UUID=''  OR  eFaturaDurumu = 'Reddedildi' OR  eFaturaDurumu = 'Gonderilemedi' ))  
		OR (@V_TUR ='1' AND UUID IS NOT NULL AND eFaturaDurumu = 'Gib e iletildi')  
		OR (@V_TUR ='2' AND UUID IS NOT NULL AND eFaturaDurumu = 'Onay Bekliyor') 
		OR (@V_TUR ='3' AND UUID IS NOT NULL AND eFaturaDurumu = 'Reddedildi' ) 
		OR (@V_TUR ='4' AND UUID IS NOT NULL AND eFaturaDurumu = 'Onaylandı' )
		)
		AND AD.eFaturaMukellefimi ='true' 
	) 
	OR
	(
		(
	      (@V_TUR ='10' AND  (UUID IS NULL OR UUID=''  OR  eFaturaDurumu = 'Fatura İptal' OR  eFaturaDurumu = 'Gonderilemedi' ))
		  OR (@V_TUR ='11' AND UUID IS NOT NULL AND eFaturaDurumu = 'Fatura Kayıt Edildi') 
		  OR (@V_TUR ='12' AND UUID IS NOT NULL AND eFaturaDurumu = 'Fatura İptal' ) 
		)
	AND AD.eFaturaMukellefimi <>'true' 
	)
)
order by FaturaTarihi asc;
GO
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_InvoiceLine_List]    Script Date: 12.05.2021 16:46:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_mb_XML_InvoiceLine_List]
 @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,
	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
 FROM(
SELECT 
 
DD.StokKodu,
DD.StokAdi AS StokAdi,
(FaturaMiktari) as Miktar,
(SonTutar-KdvTutari)*(DovizTut/TopTutar) as KdvsizSatirToplami,
DD.Iskonto1 AS IskOran,
((FaturaMiktari* BirimFiyat*BirimMiktar) -Tutar )*(DovizTut/TopTutar) as SatirIskontoToplami,
(FaturaMiktari* BirimFiyat*BirimMiktar)*(DovizTut/TopTutar) as Isksuz_Satir_Toplami,--tutar
 DD.Kdv,
(KdvTutari)*(DovizTut/TopTutar) as KdvToplami,  
(Birim) as Birim,
(Tutar -(SonTutar-KdvTutari))*(DovizTut/TopTutar) as Alt_Iskonto_Toplami, 
(Tutar)*(DovizTut/TopTutar) as Tutar,
(SonTutar)*(DovizTut/TopTutar) as SonTutar,
DD.BirimFiyat*DD.BirimMiktar*(DovizTut/TopTutar) AS ToplamBirimFiyat,--Fiyat
CASE WHEN ISNULL (DD.Kdv,0)=0 THEN (SELECT Deger FROM Parametre WHERE Param='eFaturaMuafiyetAciklama') 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 STOKHAR DD  where (IslemTuru ='Satış' or IslemTuru ='Alış İade'  )
AND (DD.IslemNo=@IslemNo or DD.IslemNo3=@IslemNo)
AND Tutar >0 
)LST
GROUP BY 
LST.StokKodu,
LST.StokAdi,
LST.IskOran,
LST.Kdv,
LST.Birim,
LST.ISO_BIRIM,
LST.MUAFIYET,
LST.ToplamBirimFiyat,
Iskonto_Aciklama
GO
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_InvoiceLine_List_delphi]    Script Date: 12.05.2021 16:46:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 STOKHAR DD     
JOIN STOKHARDETBAG DB ON DD.IslemNo=DB.IslemNo    
 where (DD.IslemTuru ='Satış' or DD.IslemTuru ='Alış İade'  )    
AND (DD.IslemNo=@IslemNo or DD.IslemNo3=@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
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_StokHarDurumUpdate]    Script Date: 12.05.2021 16:46:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE	[dbo].[sp_mb_XML_StokHarDurumUpdate] 
	@IslemNo int,
	@eFaturaGonderimTarihi DateTime, 
	@eFaturaDurumu nvarchar(20),
	@UUID nvarchar(60)
AS 
update STOKHARDETBAG
 set
  UUID=@UUID, 
  eFaturaGonderimTarihi=@eFaturaGonderimTarihi,	
  eFaturaDurumu=@eFaturaDurumu  
  where   IslemNo=@IslemNo;
GO
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_StokHarDurumUpdateDelphi]    Script Date: 12.05.2021 16:46:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE [dbo].[sp_mb_XML_StokHarDurumUpdateDelphi]     
/* 2020 01 16 a.t güncelleme fatno*/
 @IslemNo int,    
 @eFaturaGonderimTarihi DateTime,     
 @eFaturaDurumu nvarchar(20),    
 @UUID nvarchar(60),    
 @Efaturano nvarchar(60)=''    
AS     
update STK    
 set    
  UUID=@UUID,     
  eFaturaGonderimTarihi=@eFaturaGonderimTarihi,     
  eFaturaDurumu=@eFaturaDurumu  ,    
  eFaturaNo=@Efaturano   ,
 IrsNo=	case when @Efaturano<>'' then RIGHT(@Efaturano,10) else IrsNo end
 ,FatSerNo=case when @Efaturano<>'' then  convert(varchar(3),left(@Efaturano,3)) else FatSerNo end
 ,STK.FaturaNo=case when @Efaturano<>'' then  right (@eFaturaNo,10) else STK.FaturaNo end
 from STOKHARDETBAG STK

  where   (STK.IslemNo=@IslemNo OR STK.IslemNo3=@IslemNo);  
GO
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_StokHarDurumUpdateUUID]    Script Date: 12.05.2021 16:46:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE	[dbo].[sp_mb_XML_StokHarDurumUpdateUUID]  
	@eFaturaDurumu nvarchar(20),
	@UUID nvarchar(60),
    @eFaturaNo nvarchar(20)
AS 
update STOKHARDETBAG
 set 
  eFaturaDurumu=@eFaturaDurumu,
  eFaturaNo=  @eFaturaNo
  where   UUID=@UUID 
GO
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_TaxLegalMonetary_List]    Script Date: 12.05.2021 16:46:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procEDURE  [dbo].[sp_mb_XML_TaxLegalMonetary_List]    
 @IslemNo nvarchar(30)    
 AS     
    
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)) 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     
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 STOKHAR STK    
 join STOKHARDETBAG STB ON STB.IslemNo=STK.IslemNo where (STK.IslemTuru ='Satış' or STK.IslemTuru ='Alış İade'  )    
  AND (STK.IslemNo=@IslemNo or STK.IslemNo3=@IslemNo)    
  --AND STK.Tutar>0
   ) STK;    
GO
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_TaxSubtotalType_List]    Script Date: 12.05.2021 16:46:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create   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
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_VergiNoList]    Script Date: 12.05.2021 16:46:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[sp_mb_XML_VergiNoList]
 AS 
SELECT AMUSTERI.MUSVERNO   AS VERGI_NO,
AMUSTERI.MUSNO 
 FROM AMUSTERI
INNER JOIN Adres on Adres.CariNo =AMUSTERI.MUSNO
WHERE MUSNO IN 
(SELECT CariNo FROM STOKHARDETBAG WHERE FaturaTarihi > GETDATE()-60 ) 
and Adres.eFaturaMukellefimi <>'true'
GO
