-- This file is part of the it-crafts software and is subject to copyright protection.
-- (C) 2024..2025, All rights reserved by Steffen Anders, Elsteraue - Germany
-- Visit also https://www.it-crafts.de.
----------------------------------------------------------------------------------------------
-- Eingangsrechnung kalkulieren und buchen
----------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------
-- zu buchende Umsatzsteuergruppierung einer Eingangsrechnung
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'itcview_LagerEinPos_UstGruppen')
  DROP VIEW [dbo].[itcview_LagerEinPos_UstGruppen]
GO
CREATE VIEW [dbo].[itcview_LagerEinPos_UstGruppen] AS
  select NettoSummen.*, ROUND(Netto * UstVH / 100.0, 2) AS UstBetrag, Netto + ROUND(Netto * UstVH / 100.0, 2) AS Brutto
  from 
      (select 
         (select min(RecId) from ut_LagerEinPos p where p.BelTyp = 'Z' AND p.BelNr = upg.BelNr and p.UstSchl = upg.UstSchl and p.LgKto = upg.LgKto and p.PrjNr = upg.PrjNr and p.Kst = upg.Kst) as RecId
        ,upg.*
	    ,ISNULL(kp.Konto,'')     AS UstKto
		,ISNULL(kp.[Name],'')    AS UstBez
		,ISNULL(kp.AutoSatz,0.0) AS UstVH
		,ISNULL(kp.SchlDatev,'') AS SchlDatev
       from (select BelNr, UstSchl, LgKto, PrjNr, Kst, sum(NettoGes) as Netto from ut_LagerEinPos where BelTyp = 'Z' AND UPosNr = 0 group by BelNr, UstSchl, LgKto, PrjNr, Kst) AS upg
       left join ut_Kontenplan kp on kp.Schl = upg.UstSchl and kp.Typ = 'S' and kp.KzKasse = 'U' and kp.KzUst = 'V' and kp.Schl <> ''
      ) NettoSummen
GO
-- select * FROM [dbo].[itcview_LagerEinPos_UstGruppen] where BelNr = 70000061

---------------------------------------------------------------------------------------------------
-- Summen der Umsatzsteuer, Detail und Korrekturbetraege einer Eingangsrechnung
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'itcview_LagerEinPos_UstSummen')
  DROP VIEW [dbo].[itcview_LagerEinPos_UstSummen]
GO
CREATE VIEW [dbo].[itcview_LagerEinPos_UstSummen] AS
  select 
     lgeu.*
    ,lgeu.Ust + lgeu.UstKorrekt AS UstManuell 
    ,lgeu.Brutto + lgeu.UstKorrekt AS BruttoManuell 
    -- die sich aus den detailierten Abkontierungen ergebenden Beträge
    ,(SELECT SUM(Netto) FROM [dbo].[itcview_LagerEinPos_UstGruppen] ug where ug.BelNr = lgeu.BelNr AND ug.UstSchl = lgeu.UstSchl) AS NettoDetails
    ,(SELECT SUM(UstBetrag) FROM [dbo].[itcview_LagerEinPos_UstGruppen] ug where ug.BelNr = lgeu.BelNr AND ug.UstSchl = lgeu.UstSchl) AS UstDetails
    ,(SELECT SUM(Brutto) FROM [dbo].[itcview_LagerEinPos_UstGruppen] ug where ug.BelNr = lgeu.BelNr AND ug.UstSchl = lgeu.UstSchl) AS BruttoDetails
  from [dbo].[ut_LagerEinUst] lgeu WHERE BelTyp = 'Z'
GO
-- select * FROM [dbo].[itcview_LagerEinPos_UstSummen] where BelNr = 70000061
-- select UstSchl, ROUND(UstManuell - UstDetails, 2) AS UstDifferenz FROM [dbo].[itcview_LagerEinPos_UstSummen] where BelNr = 70000061

----------------------------------------------------------------------------------------------
-- Abgangsposten der Eingangsrechnungen wertmaessig gruppiert nach Buchungskriterien
-- wird nicht mehr gebraucht, loeschen falls noch vorhanden
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'itcview_LagerEinPos_VerbrauchsWerte')
  DROP VIEW [dbo].[itcview_LagerEinPos_VerbrauchsWerte]
GO

----------------------------------------------------------------------------------------------
-- Eingangsrechnung aufsummieren und Umsatzsteuersummen bilden
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_LagerEin_Calculate')
  DROP PROCEDURE [dbo].[itcproc_LagerEin_Calculate]
GO
CREATE PROCEDURE [dbo].[itcproc_LagerEin_Calculate]
  @Parm_BelNr integer  
AS
  SET NOCOUNT ON

  -- Werte reset
  UPDATE dbo.ut_LagerEinUst SET Netto = 0, Ust = 0, Brutto = 0, UstKorrekt = 0 WHERE BelTyp = 'Z' AND BelNr = @Parm_BelNr

  DECLARE @m_UstSchl varchar(3)
  DECLARE @m_Netto float
  DECLARE @m_RecId integer

  DECLARE CUStSchl CURSOR LOCAL FOR
	SELECT UstSchl, sum(NettoGes) AS Netto FROM dbo.ut_LagerEinPos WHERE BelTyp = 'Z' AND BelNr = @Parm_BelNr AND UPosNr = 0 GROUP BY UstSchl
  OPEN CUStSchl

  FETCH NEXT FROM CUstSchl INTO @m_UstSchl, @m_Netto
  WHILE @@FETCH_STATUS = 0 BEGIN
    SET @m_RecId = NULL
    SELECT @m_RecId = RecId FROM dbo.ut_LagerEinUst WHERE BelTyp = 'Z' AND BelNr = @Parm_BelNr AND UstSchl = @m_UstSchl
	-- Datensatz einfuegen, falls noch nicht vorhanden
	IF @m_RecId IS NULL BEGIN
	  SET @m_RecId = dbo.itcfunc_NextRecId_LagerEinUst()
	  INSERT INTO dbo.ut_LagerEinUst (RecId, BelTyp, BelNr, UstSchl) VALUES (@m_RecId, 'Z', @Parm_BelNr, @m_UstSchl)
	END
	UPDATE lgeu SET 
	      Netto  = @m_Netto
		 ,UstVH  = ISNULL(kp.AutoSatz,0)
		 ,Ust    = ROUND(@m_Netto * ISNULL(kp.AutoSatz,0) / 100, 2)
		 ,Brutto = @m_Netto + ROUND(@m_Netto * ISNULL(kp.AutoSatz,0) / 100, 2)
	  FROM dbo.ut_LagerEinUst lgeu 
	  LEFT JOIN dbo.ut_Kontenplan kp on kp.Typ = 'S' AND kp.KzKasse = 'U' AND kp.KzUst = 'V' AND kp.Schl = lgeu.UstSchl AND lgeu.UstSchl <> ''
	  WHERE lgeu.RecId = @m_RecId

    print @m_UstSchl + STR(@m_Netto,12,2)
    FETCH NEXT FROM CUstSchl INTO @m_UstSchl, @m_Netto
  END

  CLOSE CUStSchl
  DEALLOCATE CUStSchl

  -- ueberfluessige Zeilen entfernen 
  DELETE FROM dbo.ut_LagerEinUst WHERE BelTyp = 'Z' AND BelNr = @Parm_BelNr AND Netto = 0

  -- Ergebnis ermitteln und ausgeben
  DECLARE @m_Brutto float
  SELECT @m_Netto = ISNULL(SUM(Netto),0), @m_Brutto = ISNULL(SUM(Brutto),0) FROM dbo.ut_LagerEinUst WHERE BelTyp = 'Z' AND BelNr = @Parm_BelNr
  UPDATE ut_LagerEinKopf SET SummeNetto = @m_Netto, SummeBrutto = @m_Brutto WHERE Typ = 'Z' AND BelNr = @Parm_BelNr
  SELECT @m_Brutto AS Brutto
GO
-- EXECUTE [dbo].[itcproc_LagerEin_Calculate] 70000061
-- SELECT * FROM dbo.ut_LagerEinUst WHERE BelTyp = 'Z' AND BelNr = 70000061

----------------------------------------------------------------------------------------------
-- Eingangsrechnung Positionen kontieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_LagerEin_Kontieren_Positionen')
  DROP PROCEDURE [dbo].[itcproc_LagerEin_Kontieren_Positionen]
GO
CREATE PROCEDURE [dbo].[itcproc_LagerEin_Kontieren_Positionen]
  @Param_BelNr       integer  
 ,@Param_KredTyp     varchar(1)
 ,@Param_KredKonto   varchar(8)
 ,@Param_KladdenCode varchar(20)
 ,@Param_StartLfdNr  integer
 ,@Param_NextLfdNr   integer
 ,@Param_VkzExport   integer
 ,@Param_BelegDatum  datetime
 ,@Param_BuchDatum   datetime
 ,@Param_ExtBelNr    varchar(20)
 ,@Param_BuchText    varchar(50)
AS
  SET NOCOUNT ON

  DECLARE @m_RecId      integer
  DECLARE @m_NextLfdNr  integer = @Param_NextLfdNr

  DECLARE @m_Joined     integer = 1
  IF @Param_StartLfdNr = @m_NextLfdNr
    SET @m_Joined = 0

  DECLARE @m_AttachmentStamp varchar(32)
  DECLARE @m_AttachmentName varchar(50)
  SELECT TOP 1 @m_AttachmentStamp = Stamp, @m_AttachmentName = 'ER-' + LTRIM(STR(@Param_BelNr,8)) + '.' + RTRIM(Extension)
    FROM dbo.ut_BelegAnhang WHERE BelArt = '41' AND BelNr = STR(@Param_BelNr,8) AND DocType = 1
  If (@m_AttachmentStamp IS NULL) OR (@m_Joined <> 0)
    SELECT @m_AttachmentStamp = '', @m_AttachmentName = ''

  DECLARE @m_Purpose    integer = 2 -- offener Posten
  IF @Param_KredTyp <> 'P' 
    SET @m_Purpose = 0 -- Sachbuchung

  DECLARE CUmsatz CURSOR LOCAL FOR SELECT RecId FROM [dbo].[itcview_LagerEinPos_UstGruppen] where BelNr = @Param_BelNr
  -- Satznummer der Umsatzsteuergruppe
  DECLARE @m_RecId2 integer 

  OPEN CUmsatz
  FETCH NEXT FROM CUmsatz INTO @m_RecId2
  WHILE @@FETCH_STATUS = 0 BEGIN
    SET @m_RecId = dbo.itcfunc_NextRecId_KladdenZeile()
    INSERT INTO dbo.ut_KladdenZeile (RecId, Code, LfdNr, Joined, Purpose) VALUES (@m_RecId, @Param_KladdenCode, @m_NextLfdNr, @m_Joined, @m_Purpose)
    SET @m_NextLfdNr = @m_NextLfdNr + 1
    SET @m_Joined = 1

    UPDATE dbo.ut_KladdenZeile SET
		BelArt = '41'
	   ,BelNr = STR(@Param_BelNr,8)
	   ,ExtBelNr = @Param_ExtBelNr
	   ,BelegDatum =  @Param_BelegDatum
	   ,BuchDatum =  @Param_BuchDatum
	   ,VkzExport = @Param_VkzExport
	   ,GK_Typ = @Param_KredTyp
	   ,GK_Kto = @Param_KredKonto
	   --
       ,Betrag = ustGrp.Brutto
	   ,MWSt = ustGrp.UstBetrag
	   ,Netto = ustGrp.Netto
	   ,Typ = 'S'
	   ,Kto = ustGrp.LgKto
	   ,UStSatz = ustGrp.UstVH
	   ,KtoUSt = ustGrp.UstKto
	   ,SchlDatev = ustGrp.UstSchl
	   ,PrjNr = ustGrp.PrjNr
	   ,Kst = ustGrp.Kst
	   ,Ktr = ustGrp.PrjNr
	   ,BuText = @Param_BuchText
	   ,AttStamp = @m_AttachmentStamp
	   ,AttName =  @m_AttachmentName
	  FROM dbo.itcview_LagerEinPos_UstGruppen ustGrp 
      WHERE dbo.ut_KladdenZeile.RecId = @m_RecId AND ustGrp.RecId = @m_RecId2

    SELECT @m_AttachmentStamp = '', @m_AttachmentName = '' -- keine Wiederholung dieser Werte
    FETCH NEXT FROM CUmsatz INTO @m_RecId2
  END
  CLOSE CUmsatz
  DEALLOCATE CUmsatz

  RETURN @m_NextLfdNr
GO
-- SELECT * FROM [dbo].[itcview_LagerEinPos_UstGruppen] where BelNr = 70000061

----------------------------------------------------------------------------------------------
-- Eingangsrechnung Umsatzsteuerdifferenz in Kladdenzeilen kontieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_LagerEin_Kontieren_UStDifferenz')
  DROP PROCEDURE [dbo].[itcproc_LagerEin_Kontieren_UStDifferenz]
GO
CREATE PROCEDURE [dbo].[itcproc_LagerEin_Kontieren_UStDifferenz]
  @Param_BelNr       integer  
 ,@Param_KredTyp     varchar(1)
 ,@Param_KredKonto   varchar(8)
 ,@Param_KladdenCode varchar(20)
 ,@Param_StartLfdNr  integer
 ,@Param_NextLfdNr   integer
 ,@Param_VkzExport   integer
 ,@Param_BelegDatum  datetime
 ,@Param_BuchDatum   datetime
 ,@Param_ExtBelNr    varchar(20)
 ,@Param_BuchText    varchar(50)
AS
  SET NOCOUNT ON

  DECLARE @m_RecId      integer
  DECLARE @m_NextLfdNr  integer = @Param_NextLfdNr

  DECLARE @m_Joined     integer = 1
  IF @Param_StartLfdNr = @m_NextLfdNr
    SET @m_Joined = 0

  DECLARE @m_Purpose    integer = 2 -- offener Posten
  IF @Param_KredTyp <> 'P' 
    SET @m_Purpose = 0 -- Sachbuchung

  DECLARE CUStDiff CURSOR LOCAL FOR SELECT UstSchl, ROUND(UstManuell - UstDetails, 2) AS UstDifferenz FROM [dbo].[itcview_LagerEinPos_UstSummen] 
    WHERE BelNr = @Param_BelNr AND UstSchl <> '' AND ROUND(UstManuell - UstDetails, 2) <> 0.00
  DECLARE @m_UstSchl varchar(3)
  DECLARE @m_UstDifferenz float
  DECLARE @m_UstKonto varchar(8)

  OPEN CUStDiff
  FETCH NEXT FROM CUStDiff INTO @m_UstSchl, @m_UstDifferenz
  WHILE @@FETCH_STATUS = 0 BEGIN
    -- print 'S2 ' + STR(@m_RecId) + ' ' + STR(@m_LfdNr)
    SELECT @m_UstKonto = Konto FROM ut_Kontenplan WHERE Typ = 'S' AND KzKasse = 'U' AND Schl = @m_UstSchl
	IF ISNULL(@m_UstKonto,'') = '' BEGIN
	  CLOSE CUStDiff
	  DEALLOCATE CUstDiff
      SELECT 0 as success, 'Es wurde kein Umsatzsteuerkonto fuer Schluessel ' + @m_UstSchl + ' gefunden.' as msg
	  RETURN -1
	END

    SET @m_RecId = dbo.itcfunc_NextRecId_KladdenZeile()
    INSERT INTO dbo.ut_KladdenZeile (RecId, Code, LfdNr, Joined, Purpose) VALUES (@m_RecId, @Param_KladdenCode, @m_NextLfdNr, @m_Joined, @m_Purpose)
    SET @m_NextLfdNr = @m_NextLfdNr + 1
    SET @m_Joined = 1

    UPDATE dbo.ut_KladdenZeile SET
		BelArt = '41'
	   ,BelNr = STR(@Param_BelNr,8)
	   ,ExtBelNr = @Param_ExtBelNr
	   ,BelegDatum =  @Param_BelegDatum
	   ,BuchDatum =  @Param_BuchDatum
	   ,VkzExport = @Param_VkzExport
	   ,GK_Typ = @Param_KredTyp
	   ,GK_Kto = @Param_KredKonto
	   --
       ,Betrag = @m_UstDifferenz
	   ,MWSt = 0
	   ,Netto = @m_UstDifferenz
	   ,Typ = 'S'
	   ,Kto = @m_UstKonto
	   ,BuText = @Param_BuchText
    WHERE dbo.ut_KladdenZeile.RecId = @m_RecId

    FETCH NEXT FROM CUStDiff INTO @m_UstSchl, @m_UstDifferenz
  END
  CLOSE CUStDiff
  DEALLOCATE CUStDiff

  RETURN @m_NextLfdNr
GO

----------------------------------------------------------------------------------------------
-- Eingangsrechnung Bestandsveraenderung (Wertstellung) kontieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_LagerEin_Kontieren_Wertstellung')
  DROP PROCEDURE [dbo].[itcproc_LagerEin_Kontieren_Wertstellung]
GO
CREATE PROCEDURE [dbo].[itcproc_LagerEin_Kontieren_Wertstellung]
  @Param_BelNr       integer  
 ,@Param_KladdenCode varchar(20)
 ,@Param_StartLfdNr  integer
 ,@Param_NextLfdNr   integer
 ,@Param_VkzExport   integer
 ,@Param_BelegDatum  datetime
 ,@Param_BuchDatum   datetime
 ,@Param_ExtBelNr    varchar(20)
 ,@Param_BuchText    varchar(50)
AS
  SET NOCOUNT ON

  -- Wertstellung ist deaktiviert
  IF 0 <> ISNULL((SELECT OptDataI FROM dbo.ut_Optionen WHERE OptId = ' 6221'), 0)
	  RETURN @Param_NextLfdNr

  DECLARE @m_RecId      integer
  DECLARE @m_NextLfdNr  integer = @Param_NextLfdNr
  DECLARE @m_Joined     integer = 1
  IF @Param_StartLfdNr = @m_NextLfdNr
    SET @m_Joined = 0

  DECLARE @m_Wert float = (select ISNULL(SUM(NettoGes), 0) from ut_LagerEinPos lp 
		where BelTyp = 'Z' AND BelNr = @Param_BelNr AND NonStock = 0 AND lp.ZuAb = 'Z' AND lp.UPosNr = 0
		AND (1 = (SELECT COUNT(*) FROM ut_Stammpositionen sp where sp.Lkz = lp.Lkz AND sp.Artnr = lp.Artnr AND sp.Kzlager = 'J'))
	)

  DECLARE @m_GuVKonto varchar(8) = (SELECT OptDataC FROM dbo.ut_Optionen WHERE OptId = ' 6117')
  DECLARE @m_LagerKonto varchar(8) = (SELECT OptDataC FROM dbo.ut_Optionen WHERE OptId = ' 6213')

  IF @m_Wert <> 0.00 AND @m_GuVKonto <> '' AND @m_LagerKonto <> '' BEGIN
      SET @m_RecId = dbo.itcfunc_NextRecId_KladdenZeile()
      INSERT INTO dbo.ut_KladdenZeile (RecId, Code, LfdNr, Joined, Purpose) VALUES (@m_RecId, @Param_KladdenCode, @m_NextLfdNr, @m_Joined, 0)
      SET @m_NextLfdNr = @m_NextLfdNr + 1
      SET @m_Joined = 1

      UPDATE dbo.ut_KladdenZeile SET
  		  BelArt = '41'
	     ,BelNr = STR(@Param_BelNr,8)
	     ,ExtBelNr = @Param_ExtBelNr
	     ,BelegDatum =  @Param_BelegDatum
	     ,BuchDatum =  @Param_BuchDatum
	     ,VkzExport = @Param_VkzExport
	     --
         ,Betrag = @m_Wert
	     -- ,MWSt = 0
	     ,Netto = @m_Wert
	     ,Typ = 'S'
	     ,Kto = @m_LagerKonto
	     ,GK_Typ = 'S'
	     ,GK_Kto = @m_GuVKonto
	     ,SchlDatev = '40'
	     --,PrjNr = @m_VonPrj
	     --,Kst = @m_VonKst
	     --,Ktr = @m_VonPrj
	     ,BuText = @Param_BuchText
      WHERE dbo.ut_KladdenZeile.RecId = @m_RecId
  END
  RETURN @m_NextLfdNr
GO

----------------------------------------------------------------------------------------------
-- Eingangsrechnung Verbrauchzeilen in Kladdenzeilen kontieren
-- wird nicht mehr gebraucht
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_LagerEin_Kontieren_Verbrauch')
  DROP PROCEDURE [dbo].[itcproc_LagerEin_Kontieren_Verbrauch]
GO
----------------------------------------------------------------------------------------------
-- Eingangsrechnung Umbuchungszeilen kontieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_LagerEin_Kontieren_Umbuchungen')
  DROP PROCEDURE [dbo].[itcproc_LagerEin_Kontieren_Umbuchungen]
GO
CREATE PROCEDURE [dbo].[itcproc_LagerEin_Kontieren_Umbuchungen]
  @Param_BelNr       integer  
 ,@Param_KladdenCode varchar(20)
 ,@Param_StartLfdNr  integer
 ,@Param_NextLfdNr   integer
 ,@Param_VkzExport   integer
 ,@Param_BelegDatum  datetime
 ,@Param_BuchDatum   datetime
 ,@Param_ExtBelNr    varchar(20)
 ,@Param_BuchText    varchar(50)
AS
  SET NOCOUNT ON

  DECLARE @m_RecId      integer
  DECLARE @m_NextLfdNr  integer = @Param_NextLfdNr
  DECLARE @m_Joined     integer = 1
  IF @Param_StartLfdNr = @m_NextLfdNr
    SET @m_Joined = 0

  -- ----------------------------------------------- Umbuchungsposten buchen ------------
  DECLARE @m_PosNr integer
  DECLARE @m_Kto varchar(8)
  DECLARE @m_GKto varchar(8)
  DECLARE @m_PrjNr varchar(8)
  DECLARE @m_Kst varchar(8)
  DECLARE @m_NettoGes float
  DECLARE @m_BuText varchar(50)

  DECLARE CUmbuchen CURSOR LOCAL FOR SELECT PosNr, LgKto, VerbrKto, PrjNr, Kst, NettoGes, Bez1 FROM [dbo].[ut_LagerEinPos] 
	WHERE BelTyp = 'Z' AND BelNr = @Param_BelNr AND UPosNr <> 0 AND ZuAb = ' '
  OPEN CUmbuchen

  FETCH NEXT FROM CUmbuchen INTO @m_PosNr, @m_Kto, @m_GKto, @m_PrjNr, @m_Kst, @m_NettoGes, @m_BuText
  WHILE @@FETCH_STATUS = 0 BEGIN
	IF ISNULL(@m_Kto,'') = '' BEGIN
	  CLOSE CUmbuchen
	  DEALLOCATE CUmbuchen
      SELECT 0 as success, 'Es wurde kein Sachkonto bei Umbuchung von Pos. ' + LTRIM(STR(@m_PosNr,4)) + ' angegeben.' as msg
	  RETURN -1
	END
	IF ISNULL(@m_GKto,'') = '' BEGIN
	  SELECT @m_GKto = LgKto FROM [dbo].[ut_LagerEinPos] WHERE BelTyp = 'Z' AND BelNr = @Param_BelNr AND UPosNr = 0 AND PosNr = @m_PosNr
	END
	IF @m_BuText = '' BEGIN
	  SET @m_BuText = @Param_BuchText
	END

	IF @m_GKto <> @m_Kto BEGIN
      SET @m_RecId = dbo.itcfunc_NextRecId_KladdenZeile()
      INSERT INTO dbo.ut_KladdenZeile (RecId, Code, LfdNr, Joined, Purpose) VALUES (@m_RecId, @Param_KladdenCode, @m_NextLfdNr, @m_Joined, 0)
      SET @m_NextLfdNr = @m_NextLfdNr + 1
      SET @m_Joined = 1

      UPDATE dbo.ut_KladdenZeile SET
  		  BelArt = '41'
	     ,BelNr = STR(@Param_BelNr,8)
	     ,ExtBelNr = @Param_ExtBelNr
	     ,BelegDatum =  @Param_BelegDatum
	     ,BuchDatum =  @Param_BuchDatum
	     ,VkzExport = @Param_VkzExport
	     --
         ,Betrag = @m_NettoGes
	     ,MWSt = 0
	     ,Netto = @m_NettoGes
	     ,Typ = 'S'
	     ,Kto = @m_Kto
	     ,GK_Typ = 'S'
	     ,GK_Kto = @m_GKto
	     ,SchlDatev = '40'
	     ,PrjNr = @m_PrjNr
	     ,Kst = @m_Kst
	     ,Ktr = @m_PrjNr
	     ,BuText = @m_BuText
      WHERE dbo.ut_KladdenZeile.RecId = @m_RecId
	END
    FETCH NEXT FROM CUmbuchen INTO @m_PosNr, @m_Kto, @m_GKto, @m_PrjNr, @m_Kst, @m_NettoGes, @m_BuText
  END

  CLOSE CUmbuchen
  DEALLOCATE CUmbuchen
  RETURN @m_NextLfdNr
GO

----------------------------------------------------------------------------------------------
-- Eingangsrechnung Lagerbewegungen buchen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_LagerEin_Bewegungen_Buchen')
  DROP PROCEDURE [dbo].[itcproc_LagerEin_Bewegungen_Buchen]
GO
CREATE PROCEDURE [dbo].[itcproc_LagerEin_Bewegungen_Buchen]
  @Param_BelNr       integer  
 ,@Param_BuchDatum   datetime
 ,@Param_ExtBelNr    varchar(20)
 ,@Param_BuchText    varchar(50)
AS
  SET NOCOUNT ON

  DECLARE @m_Lkz varchar(2)
  DECLARE @m_Artnr varchar(18)
  DECLARE @m_KzZuAb varchar(1)
  DECLARE @m_Menge float
  DECLARE @m_EkPreis float
  DECLARE @m_Gebinde float
  DECLARE @m_Wert float
  DECLARE @m_PrjNr varchar(8)
  DECLARE @m_BelNr varchar(8) = STR(@Param_BelNr, 8)

  DECLARE CPos CURSOR LOCAL FOR SELECT Lkz, Artnr, ZuAb,  Menge, NettoEk, PreisJe, NettoGes, PrjNr 
    FROM dbo.ut_LagerEinPos WHERE BelTyp = 'Z' AND BelNr = @Param_BelNr AND NonStock = 0 ORDER BY PosNr, UPosNr
  OPEN CPos
  FETCH NEXT FROM CPos INTO @m_Lkz, @m_Artnr, @m_KzZuAb, @m_Menge, @m_EkPreis, @m_Gebinde, @m_Wert, @m_PrjNr
  WHILE @@FETCH_STATUS = 0 BEGIN
    IF (ROUND(@m_Menge,2) <> 0) AND (1 = (SELECT count(*) FROM dbo.ut_Stammpositionen WHERE Lkz = @m_Lkz AND Artnr = @m_Artnr AND Kzlager = 'J')) BEGIN
        EXECUTE [dbo].[itcproc_Lagerbewegung_Buchen2]  @m_Lkz, @m_Artnr, @m_KzZuAb, @Param_BuchDatum,
          @m_Menge, @m_EkPreis, @m_Gebinde, @m_Wert, @m_PrjNr, '41', @m_BelNr, @Param_ExtBelNr, @Param_BuchText
	    UPDATE dbo.ut_LagerEinPos SET VerbMenge = @m_Menge, VerbNetto = @m_Wert WHERE CURRENT OF CPos
	  END
    FETCH NEXT FROM CPos INTO @m_Lkz, @m_Artnr, @m_KzZuAb, @m_Menge, @m_EkPreis, @m_Gebinde, @m_Wert, @m_PrjNr
  END

  CLOSE CPos
  DEALLOCATE CPos
GO

----------------------------------------------------------------------------------------------
-- Eingangsrechnung in Kladdenzeilen kontieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_LagerEin_Kontieren')
  DROP PROCEDURE [dbo].[itcproc_LagerEin_Kontieren]
GO
CREATE PROCEDURE [dbo].[itcproc_LagerEin_Kontieren]
  @Param_BelNr integer  
AS
  SET NOCOUNT ON

  -- Vorlaufdaten ermitteln
  DECLARE @m_ExtBelNr   varchar(20)
  DECLARE @m_Datum       datetime
  DECLARE @m_KladdenCode varchar(20)
  DECLARE @m_KredKonto varchar(8)
  DECLARE @m_BuText varchar(50)
  DECLARE @m_ArtikelPosten integer
  SELECT 
        @m_ExtBelNr = ExtBelnr
	   ,@m_Datum = Datum
	   ,@m_KladdenCode = KladCode 
	   ,@m_KredKonto = KredKto
	   ,@m_BuText = BuText
	   ,@m_ArtikelPosten = ArtPosten
    FROM dbo.ut_LagerEinKopf WHERE Typ = 'Z' AND BelNr = @Param_BelNr

  -- Kreditorenkontotyp ermitteln
  DECLARE @m_KredTyp varchar(1) = (SELECT Typ FROM dbo.ut_Kontenplan WHERE Konto = @m_KredKonto AND (Typ = 'S' OR Typ = 'P'))
  IF (@m_KredKonto IS NULL) OR (@m_KredTyp IS NULL) BEGIN
    SELECT 0 as success, 'Das Kreditorenkonto ist nicht vorhanden.' as msg
	RETURN -1
  END

  BEGIN TRANSACTION

  -- leeren Kladdencode durch Standard ersetzen
  IF ISNULL(@m_KladdenCode,'') = '' BEGIN
    SET @m_KladdenCode = 'RECHNUNGSEINGANG'
	UPDATE dbo.ut_LagerEinKopf SET KladCode = @m_KladdenCode WHERE Typ = 'Z' AND BelNr = @Param_BelNr
  END

  -- leeren Buchungstext ersetzen
  IF ISNULL(@m_BuText,'') = '' BEGIN
    SET @m_BuText = 'Eingang ' + STR(@Param_BelNr)
	UPDATE dbo.ut_LagerEinKopf SET BuText = @m_BuText WHERE Typ = 'Z' AND BelNr = @Param_BelNr
  END

  -- Exportstatus ermitteln
  DECLARE @m_VkzExport integer
  SELECT @m_VkzExport = VkzExport FROM dbo.ut_KladdenKopf WHERE Code = @m_KladdenCode
  IF @m_VkzExport IS NULL 
    SET @m_VkzExport = 1

  -- Satznummer und laufende Nummer der ersten Kladdenzeile ermitteln
  --DECLARE @m_RecId     integer
  DECLARE @m_LfdNr     integer
  SELECT 
       @m_LfdNr = ISNULL(MAX(LfdNr),0) + 1
	  --,@m_RecId = dbo.itcfunc_NextRecId_KladdenZeile()
    FROM [dbo].[ut_KladdenZeile] WHERE Code = @m_KladdenCode

  -- Startnummer merken
  DECLARE @m_LfdNrStart integer = @m_LfdNr
  -- Status Joined initialisieren
  --DECLARE @m_Joined integer = 0

  -- print @m_KredTyp + '/' + @m_KredKonto + ' ' + STR(@Param_BelNr) + ' ' + STR(@m_RecId) + ' ' + Str(@m_LfdNr)

  -- --------------------------------------- Umsatzsteuergrupen gegen den Kreditor buchen
  EXECUTE @m_LfdNr = [dbo].[itcproc_LagerEin_Kontieren_Positionen] @Param_BelNr, @m_KredTyp, @m_KredKonto, @m_KladdenCode, @m_LfdNrStart, @m_LfdNr, @m_VkzExport, @m_Datum, @m_Datum, @m_ExtBelNr, @m_BuText
  IF @m_LfdNr <= 0 BEGIN
	ROLLBACK TRANSACTION
    RETURN -1
  END
  -- --------------------------------------- manuelle Umsatzsteuerkorrektur buchen
  EXECUTE @m_LfdNr = [dbo].[itcproc_LagerEin_Kontieren_UStDifferenz] @Param_BelNr, @m_KredTyp, @m_KredKonto, @m_KladdenCode, @m_LfdNrStart, @m_LfdNr, @m_VkzExport, @m_Datum, @m_Datum, @m_ExtBelNr, @m_BuText
  IF @m_LfdNr <= 0 BEGIN
	ROLLBACK TRANSACTION
    RETURN -1
  END
  -- ----------------------------------------------- Lagerwertsveraenderung buchen ------------
  EXECUTE @m_LfdNr = [dbo].[itcproc_LagerEin_Kontieren_Wertstellung] @Param_BelNr, @m_KladdenCode, @m_LfdNrStart, @m_LfdNr, @m_VkzExport, @m_Datum, @m_Datum, @m_ExtBelNr, @m_BuText
  IF @m_LfdNr <= 0 BEGIN
	ROLLBACK TRANSACTION
    RETURN -1
  END
  -- ----------------------------------------------- Umbuchungszeilen buchen ------------
  EXECUTE @m_LfdNr = [dbo].[itcproc_LagerEin_Kontieren_Umbuchungen] @Param_BelNr, @m_KladdenCode, @m_LfdNrStart, @m_LfdNr, @m_VkzExport, @m_Datum, @m_Datum, @m_ExtBelNr, @m_BuText
  IF @m_LfdNr <= 0 BEGIN
	ROLLBACK TRANSACTION
    RETURN -1
  END
  -- ----------------------------------------------- Test, ob etwas gebucht wurde
  IF @m_LfdNrStart = @m_LfdNr BEGIN
    ROLLBACK TRANSACTION
    SELECT 0 as success, 'Es wurde nichts gebucht.' as msg
	RETURN -1
  END
  -- ----------------------------------------------- Lagerposten im Artikelstamm zu-/abtragen
  IF @m_ArtikelPosten = 1
    EXECUTE [itcproc_LagerEin_Bewegungen_Buchen] @Param_BelNr, @m_Datum, @m_ExtBelNr, @m_BuText
  -- ----------------------------------------------- Faelligkeit und Kreditorendaten eintragen
  If @m_KredTyp = 'P' BEGIN
    UPDATE z SET
	   Faellig = k.FaelligTage
	  ,SkoFrist = k.SkontoTage
	  ,SkoSatz = k.SkontoSatz
	  ,Name1 = k.Name1
	FROM dbo.ut_KladdenZeile z LEFT JOIN dbo.ut_LagerEinKopf k ON k.Typ = 'Z' AND k.BelNr = @Param_BelNr
	WHERE z.Code = @m_KladdenCode AND z.LfdNr >= @m_LfdNrStart AND z.LfdNr < @m_LfdNr AND z.GK_Kto = @m_KredKonto
  END
  -- ----------------------------------------------- Kladdenreferenz merken
  UPDATE ut_LagerEinKopf SET KladCode = @m_KladdenCode, KladLfdNr = @m_LfdNrStart WHERE Typ = 'Z' AND BelNr = @Param_BelNr
  COMMIT TRANSACTION
  SELECT 1 as success, '' as msg
  RETURN 0
GO
--SELECT z.* FROM dbo.ut_KladdenZeile z LEFT JOIN dbo.ut_LagerEinKopf k ON k.BelNr = 70000061
-- UPDATE ut_LagerEinKopf SET KladLfdNr = 0, TransNo = '' WHERE BelNr = 70000061
-- EXECUTE [dbo].[itcproc_LagerEin_Kontieren] '70000061'
-- DELETE FROM ut_KladdenZeile WHERE Code = 'RECHNUNGSEINGANG'
-- EXECUTE [dbo].[itcproc_LagerEin_Kontieren] 8077

