
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_AccountingSupplierParty_List]    Script Date: 25.11.2025 09:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


       

CREATE OR ALTER     PROCEDURE [dbo].[sp_mb_XML_AccountingSupplierParty_List](@Entegrator int)    
 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",    
 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
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_CreditNoteLine_List_delphi]    Script Date: 25.11.2025 09:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 CREATE OR ALTER     PROCEDURE [dbo].[sp_mb_XML_CreditNoteLine_List_delphi]       
   /*   
      
   2021 01 15 güncellendi. A.t*/   
   --20230713 tarihinde H.K tarafından güncellendi.Gruplama iptal edildi. 
 @IslemNo nvarchar(30)       
 AS             
SELECT     
	ROW_NUMBER() OVER (ORDER BY DD.IslemNo3,DD.IslemNo,DD.SiraNo ) SiraNo,  
	DD.StokKodu,       
	DD.StokAdi AS StokAdi,       
	(FaturaMiktari) 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),6) 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 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 (DD.IslemTuru  in ('Alış' ,'Müstahsil'  )  AND DB.FisTuru = 'F' 
AND @IslemNo in (DD.IslemNo, DD.IslemNo3))      
GO
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_CreditNote_TaxSubtotalType_List]    Script Date: 25.11.2025 09:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER   procedure [dbo].[sp_mb_XML_CreditNote_TaxSubtotalType_List](@IslemNo decimal)
as
select   
round(STK.KdvToplami,2) AS "TaxAmount",  
round(STK.SonTutar-STK.KdvToplami,2) AS "TaxableAmount",   
STK.sayi AS "CalculationSequenceNumeric",   
Mustahsil as "Mustahsil_ORANI" ,   
OrjinalKodu,Adi

 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,  
CONVERT(decimal(20,2) , round( km,6))  Mustahsil  ,S.OrjinalKodu,max(S.StokAdi) Adi
 from STOKHAR STK 
 join STOKKARTI S on S.Kodu=STK.StokKodu
 where IslemTuru in ('Müstahsil'  )  
  AND  @IslemNo  in (STK.IslemNo,STK.IslemNo3)  
 -- AND Tutar>0  
 group by km,S.OrjinalKodu) STK; 
GO
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_CustomerDelivery_List_delphi]    Script Date: 25.11.2025 09:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER   procedure [dbo].[sp_mb_XML_CustomerDelivery_List_delphi](@IslemNo decimal)
as
select ad.VergiNo as "VERGI_NO",               
 ad.VergiDairesi AS "VERGI_DAIRESI",                        
 null as "KAPI_NO",               
 am.MUSADRES2  as "SOKAK_ADI",               
 null as "BINA_ADI",               
 null as "BINA_NO",               
 am.MUSILCE  as "ILCE",               
 am.MUSIL as "IL",               
 null as "POSTA_KODU",               
 am.MUSADRES1 as "MAHALLE",               
 ad.Ulke as "ULKE_ADI",               
 ad.VergiDairesi as "VERGI_DA",               
 am1.email_1 AS "EPOSTA",               
 ad.Tel1 as "TEL_NO",               
 ad.Faks as "FAKS",               
 am1.WebAdresi_1 as "WEB_SITE",                
    am.MUSADI as "UNVANI",
	sh.AracPlaka,
	sh.TasimaBaslangicZamani,
	sf.EhliyetNo,
	sf.UETDSNo
	from SevkHar sh
	left join Sofor sf on sf.Id=sh.SoforId
 join   AMUSTERI  am on sh.TasiyiciFirmaID=am.MUSNO
INNER JOIN  Adres ad ON am.MUSNO = ad.CariNo               
INNER JOIN AMusteri1 am1 ON am1.CariNo=am.MUSNO   
where sh.IslemNo=@IslemNo
GO
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_Customer_List]    Script Date: 25.11.2025 09:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_Customer_List_delphi]    Script Date: 25.11.2025 09:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER     PROCEDURE [dbo].[sp_mb_XML_Customer_List_delphi]                 
 @IslemNo nvarchar(30)                 
 AS                  
                 
--- 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((CASE WHEN STK.Tutar=0 AND STK.DovizTut=0 THEN (DD.SonTutar/DD.SonTutarDov) ELSE(STK.Tutar/STK.DovizTut) END),4) 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
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_DespatchAdvice_List]    Script Date: 25.11.2025 09:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER      procedure [dbo].[sp_mb_XML_DespatchAdvice_List]    
 @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,6) ToplamBirimFiyat,    
 round(SUM(LST.Miktar),4) 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)*(DovizTut/TopTutar) else 0 end  as KdvsizSatirToplami,--Net tutar    
DD.Iskonto1 AS IskOran,    
case when Tutar>0 then ((FaturaMiktari* BirimFiyat*BirimMiktar) -Tutar )*(DovizTut/TopTutar) else 0 end as SatirIskontoToplami,    
case when DovizTut>0 then (FaturaMiktari* BirimFiyat*BirimMiktar)*(DovizTut/TopTutar) else 0 end  as Isksuz_Satir_Toplami,--tutar    
 DD.Kdv,    
(KdvTutari)*(DovizTut/TopTutar) as KdvToplami,      
(Birim) as Birim,    
case when DovizTut>0 then (Tutar -(SonTutar-KdvTutari))*(DovizTut/TopTutar) else 0  end as Alt_Iskonto_Toplami,     
case when TopTutar>0 then (Tutar)*(DovizTut/TopTutar) else 0 end as Tutar,    
case when TopTutar >0 then (SonTutar)*(DovizTut/TopTutar) else 0 end as SonTutar,    
case when TopTutar>0 then DD.BirimFiyat*DD.BirimMiktar*(DovizTut/TopTutar) 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
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_FaturaList]    Script Date: 25.11.2025 09:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER   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",
round(STK.Tutar,2) 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: 25.11.2025 09:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER   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, 
	round(LST.ToplamBirimFiyat,6) ToplamBirimFiyat, 
	round((SUM(LST.Miktar)),4) 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, 
(SonTutar-KdvTutari)/(DovizKuru) as KdvsizSatirToplami, 
DD.Iskonto1 AS IskOran, 
((FaturaMiktari* BirimFiyat*BirimMiktar) -Tutar )/(DovizKuru) as SatirIskontoToplami, 
(FaturaMiktari* BirimFiyat*BirimMiktar)/(DovizKuru) as Isksuz_Satir_Toplami,--tutar 
 DD.Kdv, 
(KdvTutari)/(DovizKuru) as KdvToplami,   
(Birim) as Birim, 
(Tutar -(SonTutar-KdvTutari))/(DovizKuru) as Alt_Iskonto_Toplami,  
(Tutar)/((DovizKuru)) as Tutar, 
(SonTutar)/((DovizKuru)) as SonTutar, 
(DD.BirimFiyat*DD.BirimMiktar)/(DovizKuru) 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 (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_Grup]    Script Date: 25.11.2025 09:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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,       
 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) / (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
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_InvoiceLine_List_STOKGRUPLU]    Script Date: 25.11.2025 09:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER    procedure [dbo].[sp_mb_XML_InvoiceLine_List_STOKGRUPLU]  
 @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,  
 AVG (LST.ToplamBirimFiyat) ORT_FİYAT,  
 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 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 (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,  
Iskonto_Aciklama


GO
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_InvoiceLine_List_delphi]    Script Date: 25.11.2025 09:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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,       
	(FaturaMiktari) as Miktar,       
	(SonTutar-DD.KdvTutari)/ DB.DovizKur as KdvsizSatirToplami,       
	(SonTutar-DD.OTVTutari)/ DB.DovizKur as OTVsizSatirToplami,       
	DD.Iskonto1 AS IskOran,       
	((FaturaMiktari* BirimFiyat*BirimMiktar) -DD.Tutar )/ DB.DovizKur as SatirIskontoToplami,       
	(FaturaMiktari* BirimFiyat*BirimMiktar)/ DB.DovizKur  as Isksuz_Satir_Toplami,--tutar       
	DD.Kdv,       
	DD.OTVOrani,       
	(DD.KdvTutari)/ DB.DovizKur as KdvToplami,         
	(DD.OTVTutari)/ DB.DovizKur as OtvToplami,         
	(Birim) as Birim,       
	(DD.Tutar -(SonTutar-DD.KdvTutari))/ DB.DovizKur as Alt_Iskonto_Toplami,        
	(DD.Tutar)/ DB.DovizKur as Tutar,       
	(SonTutar)/ DB.DovizKur as SonTutar,       
	DD.BirimFiyat*DD.BirimMiktar / DB.DovizKur 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
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_StokHarDurumUpdate]    Script Date: 25.11.2025 09:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---------------------------------------------------------------------------------------------

CREATE OR ALTER   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: 25.11.2025 09:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER    PROCEDURE [dbo].[sp_mb_XML_StokHarDurumUpdateDelphi]       
/* 2020 01 16 a.t güncelleme fatno*/  
/*2021 07 17 a.t entegrator*/  
 @IslemNo int,      
 @eFaturaGonderimTarihi DateTime,       
 @eFaturaDurumu nvarchar(20),      
 @UUID nvarchar(60),      
 @Efaturano nvarchar(60)='',  
 @Entegrator int=0,  
 @ProcessType int=1  
AS       
update STK      
 set      
 
    IRSALIYEUUID= CASE WHEN  @ProcessType=3  THEN   @UUID ELSE IRSALIYEUUID END  ,      
   UUID= CASE WHEN  @ProcessType<>3  THEN   @UUID ELSE UUID END  ,  
  eFaturaGonderimTarihi=@eFaturaGonderimTarihi,       
  EIRSALIYEDURUM= CASE WHEN  @ProcessType=3  THEN   @eFaturaDurumu ELSE EIRSALIYEDURUM END  ,      
   eFaturaDurumu= CASE WHEN  @ProcessType<>3  THEN   @eFaturaDurumu ELSE eFaturaDurumu END  ,  
  eFaturaNo=case when @ProcessType<>3 then  @Efaturano   else '' end,  
 IrsNo= case when @ProcessType=3 and @Efaturano<>'' then right (@eFaturaNo,9)  else IrsNo  end  
 ,EIrsaliyeNo=case when @ProcessType=3 then  @Efaturano else EIrsaliyeNo end
 ,FatSerNo=case when @ProcessType<>3 and @Efaturano<>'' then  convert(varchar(3),left(@Efaturano,3)) else FatSerNo   end  
 ,STK.FaturaNo=case when @ProcessType<>3 and  @Efaturano<>'' then  right (@eFaturaNo,9) else STK.FaturaNo end  
 ,Entegrator=@Entegrator  
 from STOKHARDETBAG STK  
  
  where   (STK.IslemNo=@IslemNo OR STK.IslemNo3=@IslemNo);  
GO
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_StokHarDurumUpdateUUID]    Script Date: 25.11.2025 09:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER   PROCEDURE [dbo].[sp_mb_XML_StokHarDurumUpdateUUID]      
 @eFaturaDurumu nvarchar(20),    
 @UUID nvarchar(60),    
 @eFaturaNo nvarchar(20)    
AS     
declare @IslemNo int;  
  
Select @IslemNo = IslemNo FRom STOKHARDETBAG where UUID=@UUID  
  
UPDATE STOKHARDETBAG    
   SET eFaturaDurumu=@eFaturaDurumu,    
       eFaturaNo=  @eFaturaNo    
 WHERE UUID=@UUID;  
  
UPDATE A
SET A.ACIKLAMA = 
    CASE 
        WHEN LEN(S.eFaturaNo) >= 16 THEN S.eFaturaNo + ' no.lu Fatura'
        WHEN LEN(S.eFaturaNo) >= LEN(S.FaturaNo) THEN S.eFaturaNo + ' no.lu Fatura'
        ELSE cast(S.FaturaNo as nvarchar(20)) + ' no.lu Fatura'
    END
FROM ACARI A
JOIN STOKHARDETBAG S ON A.ISLEMNO = S.IslemNo
WHERE S.UUID=@UUID  AND BAACIKLA LIKE ('Alış%') or BAACIKLA LIKE ('Satış%')

/*UPDATE ACARI   
   SET ACIKLAMA = @eFaturaNo + ' no.lu Fatura'   
 WHERE ISLEMNO = @IslemNo;  */
GO
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_TaxLegalMonetary_List]    Script Date: 25.11.2025 09:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER   PROCEDURE [dbo].[sp_mb_XML_TaxLegalMonetary_List]
 @IslemNo nvarchar(30)
 AS
 BEGIN
        -- Müstahsil Tutarı (Düzeltme Tutarı) hesaplanıyor
        ;WITH mustahsil AS (
            SELECT 
                ROUND(ISNULL(SUM(DovizTut), 0), 2) AS MustahsilTut 
            FROM STOKHAR 
            WHERE IslemTuru = 'Müstahsil' 
                AND IslemNo = @IslemNo
        )
        
        SELECT
            
            -- Tüm parasal değerler ROUND(..., 2) ile yuvarlanmıştır
            ROUND(STK.KdvToplami, 2)                     AS "TaxAmount",                        -- KDV Tutarı
            ROUND(STK.SonTutar, 2)                      AS "TaxableAmount",                  -- Vergi Matrahı/Vergilendirilebilir Tutar
            STK.sayi                                    AS "CalculationSequenceNumeric",    -- Satır Sayısı
            ROUND(STK.Isksuz_Satir_Toplami, 2)           AS "LineExtensionAmount",            -- İskontosuz Satır Toplamı (Brüt)
            ROUND(STK.KdvsizSatirToplami, 2)             AS "TaxExclusiveAmount",             -- KDV Hariç Toplam (Net)
            ROUND(STK.SonTutar, 2) - (SELECT MustahsilTut FROM mustahsil)   AS "TaxInclusiveAmount",           -- KDV Dahil Toplam - Müstahsil
            ROUND(STK.SatirIskontoToplami + STK.Alt_Iskonto_Toplami, 2)  AS "AllowanceTotalAmount",           -- Toplam İskonto
            ROUND(STK.SonTutar, 2) - (SELECT MustahsilTut FROM mustahsil)   AS "PayableAmount",                -- Ödenecek Nihai Tutar
            ISNULL(TevkifatKod, '')                     AS TevkifatKod,
            ISNULL(TevkifatAd, '')                      AS TevkifatAd,
            ISNULL(TevkifatOrani, 0)                    AS TevkifatOrani,
            ISNULL(TevkifatTutari, 0)                    AS TevkifatTutari
            
        FROM
        (
            SELECT
                SUM(1) AS sayi,
                ROUND(SUM(FaturaMiktari * BirimFiyat * BirimMiktar / STK.DovizKuru), 4) AS Isksuz_Satir_Toplami, 
                ROUND(SUM(CASE WHEN ((FaturaMiktari * BirimFiyat * BirimMiktar) - STK.Tutar) < 0.1 
                                THEN 0 
                                ELSE ((FaturaMiktari * BirimFiyat * BirimMiktar) - STK.Tutar) / STK.DovizKuru 
                            END), 6) AS SatirIskontoToplami,
                -- Alt İskonto burada CAST ile 2 ondalık basamağa kesiliyor
                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, 4)) 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 AS TevkifatKod,
                tb.TevkifatAdi AS TevkifatAd,
                CONVERT(DECIMAL, (t.TevkifatOrani)) AS TevkifatOrani,
                SUM(t.TevkifatTutari / STK.DovizKuru) AS 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
        ) STK;
        
 END

GO
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_TaxSubtotalTypeName_List]    Script Date: 25.11.2025 09:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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
/****** Object:  StoredProcedure [dbo].[sp_mb_XML_TaxSubtotalType_List]    Script Date: 25.11.2025 09:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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  top 1  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/STK.DovizKuru) as Isksuz_Satir_Toplami,    
sum (((FaturaMiktari* BirimFiyat*BirimMiktar) -Tutar)/STK.DovizKuru) as SatirIskontoToplami,    
cast( sum (((Tutar -(SonTutar-KdvTutari))/STK.DovizKuru)) as decimal(15,2)) as Alt_Iskonto_Toplami,     
sum ((SonTutar-KdvTutari)/STK.DovizKuru) as KdvsizSatirToplami,    
sum (KdvTutari/STK.DovizKuru) as KdvToplami,      
sum(Tutar/STK.DovizKuru) as Tutar,    
sum(SonTutar/STK.DovizKuru) 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: 25.11.2025 09:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER   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
