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

-- -----------------------------------------------------------------------------------------------------------
-- Datanorm(4) A-Satz
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_Datanorm_A')
  DROP PROCEDURE [dbo].[itcproc_Datanorm_A]
GO
CREATE PROCEDURE [dbo].[itcproc_Datanorm_A]
  @P_Dummy varchar(10)
 ,@P_Lkz varchar(2)
 ,@P_Artnr varchar(18)
 ,@P_Vkz varchar(1)
 ,@P_KzDrk1 varchar(2)
 ,@P_Ktxt1 varchar(42)
 ,@P_Ktxt2 varchar(42)
 ,@P_Preiskz varchar(1)
 ,@P_PE varchar(10)
 ,@P_ME varchar(4)
 ,@P_Preis varchar(10)
 ,@P_Rabgru varchar(6)
 ,@P_HWgru varchar(3)
 ,@P_TextNr varchar(18)
 ,@P_ChgVkm2 varchar(1) = '1'
AS
  SET NOCOUNT ON

  DECLARE @PV_KzDrk1 integer = dbo.itcfunc_integer(@P_KzDrk1)
  DECLARE @PV_PE integer = dbo.itcfunc_integer(@P_PE)
  DECLARE @PV_ME integer = dbo.itcfunc_integer(@P_ME)
  DECLARE @PV_Preis float = dbo.itcfunc_float(@P_Preis)

  ---------------------------------------- Löschsatz
  IF @P_Vkz = 'L' BEGIN
    DELETE FROM ut_Stammpositionen WHERE Lkz = @P_Lkz AND Artnr = @P_Artnr AND Kzlager <> 'J'
	RETURN
  END

  ---------------------------------------- Artikelnummer geändert
  IF @P_Vkz = 'X' BEGIN
    DECLARE @m_NeueArtNr varchar(18) = SUBSTRING(@P_Ktxt1,1,18)
	UPDATE ut_Stammpositionen SET Artnr = @m_NeueArtNr WHERE Lkz = @P_Lkz AND Artnr = @P_Artnr
	UPDATE ut_Kleinteile SET Artnr = @m_NeueArtNr WHERE Lkz = @P_Lkz AND Artnr = @P_Artnr
	UPDATE ut_Kleinteile SET RefArtnr = @m_NeueArtNr WHERE RefLkz = @P_Lkz AND RefArtnr = @P_Artnr
	UPDATE ut_StammPakete SET ArtNr = @m_NeueArtNr WHERE ArtLkz = @P_Lkz AND ArtNr = @P_Artnr
	UPDATE ut_NeZuschlaege SET Artnr = @m_NeueArtNr WHERE Lkz = @P_Lkz AND Artnr = @P_Artnr
	UPDATE ut_Dimensionstexte SET ARTNR = @m_NeueArtNr WHERE LKZ = @P_Lkz AND ARTNR = @P_Artnr 
	DECLARE @m_LkzArtnr varchar(20) = @P_Lkz + @P_Artnr
	UPDATE ut_Lagerbewegungen SET ARTNR = @P_Lkz + @m_NeueArtNr WHERE ARTNR = @m_LkzArtnr
	RETURN
  END

  ---------------------------------------- bisherige Werte ermitteln
  DECLARE @m_RecId integer
  DECLARE @m_Kzdrk1 integer
  DECLARE @m_Bez1 char(42)
  DECLARE @m_Merkmal char(42)
  DECLARE @m_Lkztextnr char(2)
  DECLARE @m_Textnr char(18)
  DECLARE @m_Einheit integer
  DECLARE @m_Wargru char(6)
  DECLARE @m_Rabgru char(6)
  DECLARE @m_Pe integer
  DECLARE @m_Preiskz integer
  DECLARE @m_Bruttoek float
  DECLARE @m_Rabatt1 float
  DECLARE @m_Rabatt2 float
  DECLARE @m_Ekmat float
  DECLARE @m_Datumpreis datetime
  DECLARE @m_Gebinde float
  DECLARE @m_Vkm2 float

  SELECT @m_RecId = RecId, @m_Kzdrk1 = Kzdrk1, @m_Bez1 = Bez1, @m_Merkmal = Merkmal
    , @m_Lkztextnr = Lkztextnr , @m_Textnr = Textnr, @m_Einheit = Einheit
	, @m_Wargru = Wargru, @m_Rabgru = Rabgru, @m_Pe = Pe, @m_Preiskz = Preiskz
	, @m_Bruttoek = ISNULL(Bruttoek, 0), @m_Rabatt1 = ISNULL(Rabatt1, 0), @m_Rabatt2 = ISNULL(Rabatt2, 0)
	, @m_Ekmat = ISNULL(Ekmat, 0), @m_Datumpreis = Datumpreis, @m_Gebinde = Gebinde, @m_Vkm2 = Vkm2
    FROM ut_Stammpositionen WHERE Lkz = @P_Lkz AND Artnr = @P_Artnr

  ---------------------------------------- Neuanlage
  IF @m_RecId IS NULL BEGIN
    IF @P_Vkz <> 'N' BEGIN
	  RETURN
	END
	SET @m_RecId = dbo.itcfunc_NextRecId_Stammpositionen()
	INSERT INTO dbo.ut_Stammpositionen (RecId, Lkz, Artnr, Kgmat, Kglohn) VALUES (@m_RecId, @P_Lkz, @P_Artnr, 1, 1)
  END

  ---------------------------------------- normale Verarbeitung
  DECLARE @mCalculate integer = 0

  IF @PV_KzDrk1 >= 0    SET @m_Kzdrk1 = @PV_KzDrk1
  IF LEN(@P_Ktxt1) > 0  SET @m_Bez1 = @P_Ktxt1
  IF LEN(@P_Ktxt2) > 0  SET @m_Merkmal = @P_Ktxt2
  IF LEN(@P_HWgru) > 0  SET @m_Wargru = @P_HWgru
  IF @PV_ME >= 0        SET @m_Einheit = @PV_ME

  IF @P_Preiskz = '1' BEGIN
    SET @m_Preiskz = 1
	SET @m_Bruttoek = @PV_Preis
	SET @m_Pe = 1;
	SET @m_Gebinde = @PV_PE
	SET @m_Datumpreis = dbo.itcfunc_datetime('H')
    SET @mCalculate = 1
	IF dbo.itcfunc_integer(@P_ChgVkm2) = 1
  	  SET @m_Vkm2 = @m_Bruttoek
  END

  IF @P_Preiskz = '2' BEGIN
    SET @m_Preiskz = 2
	SET @m_Ekmat = @PV_Preis
	SET @m_Pe = 1;
	SET @m_Gebinde = @PV_PE
	SET @m_Datumpreis = dbo.itcfunc_datetime('H')
    SET @mCalculate = 1
  END

  IF LEN(@P_Rabgru) > 0 BEGIN
    DECLARE @mRabattX float 
    SELECT @mRabattX = WERT2 FROM ut_RabWaGruppen WHERE BEREICH = 'R' AND LKZ = @P_Lkz AND IDENT = @P_Rabgru
    SET @m_Rabgru = @P_Rabgru
    IF NOT @mRabattX IS NULL BEGIN
      SET @m_Rabatt1 = @mRabattX
	  SET @mCalculate = 1
	END
  END

  IF LEN(@P_TextNr) > 0 BEGIN
    SET @m_Lkztextnr = @P_Lkz
	SET @m_Textnr = @P_TextNr
  END

  ---------------------------------------- Preis kalkulieren
  IF @mCalculate = 1 AND @m_Preiskz = 1 BEGIN
    SET @m_Ekmat = @m_Bruttoek
	IF @m_Rabatt1 <> 0 SET @m_Ekmat = ROUND(@m_Ekmat - (@m_Ekmat * @m_Rabatt1) / 100.00, 2)
	IF @m_Rabatt2 <> 0 SET @m_Ekmat = ROUND(@m_Ekmat - (@m_Ekmat * @m_Rabatt2) / 100.00, 2)
  END

  -- Rabatt 1 ausrechnen
  IF @mCalculate = 1 AND @m_Preiskz = 2 BEGIN
    IF @m_Bruttoek > @m_Ekmat AND @m_Bruttoek > 0 BEGIN
      SET @m_Rabatt1 = ROUND(100.0 * ((@m_Bruttoek - @m_Ekmat) / @m_Bruttoek), 2) 
      SET @m_Rabatt2 = 0
	END ELSE BEGIN
	  SET @m_Bruttoek = @m_Ekmat
      SET @m_Rabatt1 = 0
      SET @m_Rabatt2 = 0
	END
  END

  ---------------------------------------- Aktualisierung
  UPDATE ut_Stammpositionen SET 
      Kzdrk1 = ISNULL(@m_Kzdrk1,1)
    , Bez1 = ISNULL(@m_Bez1,'')
	, Merkmal = ISNULL(@m_Merkmal,'') 
	, Lkztextnr = ISNULL(@m_Lkztextnr,'') 
	, Textnr = ISNULL(@m_Textnr,'')
	, Einheit = ISNULL(@m_Einheit,0)
	, Wargru = ISNULL(@m_Wargru,'')
	, Rabgru = ISNULL(@m_Rabgru,'')
    , Pe = ISNULL(@m_Pe,1)
	, Preiskz = ISNULL(@m_Preiskz,1)
	, Bruttoek = ISNULL(@m_Bruttoek,0)
	, Rabatt1 = ISNULL(@m_Rabatt1,0) 
	, Rabatt2 = ISNULL(@m_Rabatt2,0)
	, Ekmat = ISNULL(@m_Ekmat,0)
	, Datumpreis = ISNULL(@m_Datumpreis,convert(datetime,'1900-01-01',120))
	, Gebinde = ISNULL(@m_Gebinde,1)
	, Vkm2 = ISNULL(@m_Vkm2,0)
    WHERE RecId = @m_RecId
GO

-- -----------------------------------------------------------------------------------------------------------
-- Datanorm(4) Z-Satz Nichteisen nach Gewicht
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_Datanorm_Z_Ne')
  DROP PROCEDURE [dbo].[itcproc_Datanorm_Z_Ne]
GO
CREATE PROCEDURE [dbo].[itcproc_Datanorm_Z_Ne]
   @Param_Dummy varchar(20)
  ,@Param_Lkz varchar(2)
  ,@Param_Artnr varchar(18)
  ,@Param_Art varchar(2)
  ,@Param_JeMenge varchar(10)
  ,@Param_BasisPreis varchar(10)
  ,@Param_KgJeBasis varchar(10)
  ,@Param_KgNe varchar(10)
AS
  SET NOCOUNT ON

  DECLARE @mGebinde    float = dbo.itcfunc_float(@Param_JeMenge)
  DECLARE @mPeFaktor   float = 1
  DECLARE @mGewicht    float = dbo.itcfunc_float(@Param_KgNe)
  DECLARE @mBasisPreis float = dbo.itcfunc_float(@Param_BasisPreis)
  DECLARE @mKgJeBasis  float = dbo.itcfunc_float(@Param_KgJeBasis)

  IF @mGebinde < 0
    SELECT @mGebinde = ISNULL(sp.Gebinde,1), @mPeFaktor = ISNULL(pe.Faktor,1) 
	  FROM ut_Stammpositionen sp left join ut_Preiseinheiten pe on pe.Nr = STR(sp.Pe,2)
	  WHERE Lkz = @Param_Lkz AND Artnr = @Param_Artnr
  IF @mGebinde = 0
    SET @mGebinde = 1

  DECLARE @mRecId integer
  SELECT TOP 1 @mRecId = RecId FROM ut_NeZuschlaege WHERE Lkz = @Param_Lkz AND Artnr = @Param_Artnr AND NeKz = @Param_Art
  IF @mRecId IS NULL BEGIN
	SET @mRecId = dbo.itcfunc_NextRecId_NeZuschlaege()
	INSERT INTO ut_NeZuschlaege (RecId, Lkz, Artnr, NeKz, Gebinde, Gewicht, BasisPreis, KgJeBasis)
	VALUES(@mRecId, @Param_Lkz, @Param_Artnr, @Param_Art, @mGebinde, @mGewicht, @mBasisPreis, @mKgJeBasis)
  END ELSE
    UPDATE ut_NeZuschlaege SET Gebinde = @mGebinde * @mPeFaktor, Gewicht = @mGewicht, BasisPreis = @mBasisPreis, KgJeBasis = @mKgJeBasis WHERE RecId = @mRecId
GO

-- -----------------------------------------------------------------------------------------------------------
-- Datanorm(4) B-Satz
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_Datanorm_B')
  DROP PROCEDURE [dbo].[itcproc_Datanorm_B]
GO
CREATE PROCEDURE [dbo].[itcproc_Datanorm_B]
  @P_Dummy varchar(10)
 ,@P_Lkz varchar(2) = null
 ,@P_Artnr varchar(18) = null
 ,@P_Matchcode varchar(15) = null
 ,@P_Bstnr varchar(20) = null
 ,@P_Katalogseite varchar(8) = null
 ,@P_CuMerker varchar(1) = null
 ,@P_CuBasispreis varchar(3) = null
 ,@P_CuGewicht varchar(6) = null
 ,@P_EANcode varchar(14) = null
 ,@P_AnbindeNr varchar(20) = null
 ,@P_Warengruppe varchar(10) = null
 ,@P_Kostenart varchar(2) = null
 ,@P_VPE varchar(5) = null
 ,@P_ChgSuchen varchar(1) = '0'
 ,@P_ChgEAN varchar(1) = '0'
AS
  SET NOCOUNT ON
  --DECLARE @PV_RecId integer = dbo.itcfunc_integer(@P_RecId)
  DECLARE @PV_CuMerker integer = dbo.itcfunc_integer(@P_CuMerker)
  -- DECLARE @PV_CuBasispreis integer = dbo.itcfunc_integer(@P_CuBasispreis)
  DECLARE @PV_CuGewicht float = dbo.itcfunc_float(@P_CuGewicht)
  --DECLARE @PV_VPE integer = dbo.itcfunc_integer(@P_VPE)

  -- Feldvariablen
  DECLARE @m_RecId integer 
  --DECLARE @m_Lkz char(2)
  --DECLARE @m_Artnr char(18)
  DECLARE @m_Matchcode char(20)
  DECLARE @m_Bstnr char(20)
  DECLARE @m_Katalogseite char(4)
  --DECLARE @m_CuMerker integer
  --DECLARE @m_CuBasispreis integer
  --DECLARE @m_CuGewicht float
  DECLARE @m_EANcode char(14)
  --DECLARE @m_AnbindeNr char(20)
  DECLARE @m_Warengruppe char(6)

  -- bisherige Werte in Feldvariablen speichern
  SELECT @m_RecId = RecId, @m_Matchcode = Suchen, @m_Bstnr = Bstnr, @m_Katalogseite = Katalogseite, @m_EANcode = EANcode, @m_Warengruppe = Wargru
    FROM ut_Stammpositionen WHERE Lkz = @P_Lkz AND Artnr = @P_Artnr

  IF @m_RecId IS NULL
	  RETURN

  IF (LEN(@P_Matchcode) > 0) AND ((@m_Matchcode = '') OR (dbo.itcfunc_integer(@P_ChgSuchen) = 1))
    SET @m_Matchcode = @P_Matchcode
  IF (LEN(@P_EANcode) > 0) AND ((@m_EANcode = '') OR (dbo.itcfunc_integer(@P_ChgEAN) = 1))      
    SET @m_EANcode = @P_EANcode

  IF LEN(@P_Bstnr) > 0          SET @m_Bstnr = @P_Bstnr
  IF LEN(@P_Katalogseite) > 0   SET @m_Katalogseite = @P_Katalogseite
  IF LEN(@P_Warengruppe) > 0    SET @m_Warengruppe = SUBSTRING(@m_Warengruppe,1,3) + SUBSTRING(@P_Warengruppe,1,3)

  -- neue Werte zurückschreiben
  UPDATE ut_Stammpositionen SET 
       Suchen = ISNULL(@m_Matchcode,'')
	 , Bstnr = ISNULL(@m_Bstnr,'')
	 , Katalogseite = ISNULL(@m_Katalogseite,'')
	 , EANcode = ISNULL(@m_EANcode,'')
	 , Wargru = SUBSTRING(ISNULL(@m_Warengruppe,''),1,6)
    WHERE RecId = @m_RecId

  -- Kupferpreis
  IF @PV_CuMerker <> 0 AND @PV_CuGewicht > 0 BEGIN
    IF @PV_CuMerker = 1
	  EXECUTE dbo.itcproc_Datanorm_Z_Ne '', @P_Lkz, @P_Artnr, 'CU', '100', @P_CuBasispreis, '100', @P_CuGewicht  
    IF @PV_CuMerker = 2
	  EXECUTE dbo.itcproc_Datanorm_Z_Ne '', @P_Lkz, @P_Artnr, 'CU', '1000', @P_CuBasispreis, '100', @P_CuGewicht  
  END
GO

-- -----------------------------------------------------------------------------------------------------------
-- Datanorm(4) C-Satz Arbeit-1
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_Datanorm_C_Montage')
  DROP PROCEDURE [dbo].[itcproc_Datanorm_C_Montage]
GO
CREATE PROCEDURE [dbo].[itcproc_Datanorm_C_Montage]
   @Param_Dummy varchar(20)
  ,@Param_Lkz varchar(2)
  ,@Param_Artnr varchar(18)
  ,@Param_Minuten varchar(20)
AS
  SET NOCOUNT ON
  DECLARE @mMinuten float = dbo.itcfunc_float(@Param_Minuten)
  update ut_Stammpositionen set Zeit = @mMinuten where Lkz = @Param_Lkz and Artnr = @Param_Artnr 
GO

-- -----------------------------------------------------------------------------------------------------------
-- Datanorm(4) P-Satz
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_Datanorm_P')
  DROP PROCEDURE [dbo].[itcproc_Datanorm_P]
GO
CREATE PROCEDURE [dbo].[itcproc_Datanorm_P]
   @Param_Dummy varchar(20)
  ,@Param_Lkz varchar(2)
  ,@Param_Artnr varchar(18)
  ,@Param_PreisKz varchar(10)
  ,@Param_Preis varchar(20)
  ,@Param_Rabkz1 varchar(10)
  ,@Param_Rabgru varchar(6)
  ,@Param_Rabatt1 varchar(10)
  ,@Param_Rabkz2 varchar(10)
  ,@Param_Rabatt2 varchar(10)
  ,@P_PreisJe varchar(12) = '-1'
  ,@P_ChgVkm2 varchar(1) = 1
AS
  SET NOCOUNT ON

  DECLARE @mBruttoek float
  DECLARE @mRabgru varchar(10)
  DECLARE @mRabatt1 float 
  DECLARE @mRabatt2 float
  DECLARE @mEkmat float 
  DECLARE @mVkm2 float
  DECLARE @mPreiskz integer
  DECLARE @mDatumpreis datetime
  DECLARE @mCalculate integer = 0
  DECLARE @mGebinde float
  DECLARE @mPeFaktor float
  DECLARE @mPe integer
  DECLARE @mRecId integer

  select @mRecId = sp.RecId, @mBruttoek = sp.Bruttoek, @mRabgru = sp.Rabgru, @mRabatt1 = sp.Rabatt1, @mRabatt2 = sp.Rabatt2, @mEkmat = sp.Ekmat, @mVkm2 = sp.Vkm2
       , @mPreiskz = sp.Preiskz, @mDatumpreis = Datumpreis, @mGebinde = ISNULL(sp.Gebinde,1), @mPeFaktor = ISNULL(pe.Faktor,1), @mPe = sp.Pe
    from ut_Stammpositionen sp left join ut_Preiseinheiten pe on pe.Nr = STR(sp.Pe,2)
    where Lkz = @Param_Lkz and Artnr = @Param_Artnr

  IF @mRecId IS NULL
	RETURN

  IF @mGebinde = 0
    SET @mGebinde = 1

  -- Datanorm 5 kam mit Preiseinheit
  DECLARE @PV_PreisJe float = dbo.itcfunc_float(@P_PreisJe)
  IF @PV_PreisJe > 0 AND (@Param_PreisKz = '1' OR @Param_PreisKz = '2') BEGIN
    SET @mGebinde = @PV_PreisJe
	SET @mPe = 1
  END

  If @Param_Preis <> '0' AND @Param_Preis <> '' BEGIN
	  If @Param_PreisKz = '1' BEGIN
		SET @mBruttoek = dbo.itcfunc_float(@Param_Preis)
		SET @mPreiskz = 1
		SET @mCalculate = 1
		SET @mDatumpreis = dbo.itcfunc_datetime('H')
		IF dbo.itcfunc_integer(@P_ChgVkm2) = 1
  		  SET @mVkm2 = @mBruttoek
	  END ELSE If @Param_PreisKz = '2' BEGIN
		SET @mEkmat = dbo.itcfunc_float(@Param_Preis)
		SET @mPreiskz = 2
		SET @mCalculate = 1
		SET @mDatumpreis = dbo.itcfunc_datetime('H')
	  END
  END

  IF @Param_Rabkz1 = '0' BEGIN
    DECLARE @mRabattX float 
    SELECT @mRabattX = WERT2 FROM ut_RabWaGruppen WHERE BEREICH = 'R' AND LKZ = @Param_Lkz AND IDENT = @Param_Rabgru
	SET @mRabgru = @Param_Rabgru
    IF NOT @mRabattX IS NULL BEGIN
      SET @mRabatt1 = @mRabattX
	  SET @mCalculate = 1
	END
  END ELSE IF @Param_Rabkz1 = '1' BEGIN
    SET @mRabatt1 = dbo.itcfunc_float(@Param_Rabatt1)
	SET @mCalculate = 1
  END

  IF @Param_Rabkz2 = '1' BEGIN
    SET @mRabatt2 = dbo.itcfunc_float(@Param_Rabatt2)
	SET @mCalculate = 1
  END

  -- Nettopreis ausrechnen
  IF @mCalculate = 1 AND @mPreiskz = 1 BEGIN
    SET @mEkmat = @mBruttoek
	IF @mRabatt1 <> 0 SET @mEkmat = ROUND(@mEkmat - (@mEkmat * @mRabatt1) / 100.00, 2)
	IF @mRabatt2 <> 0 SET @mEkmat = ROUND(@mEkmat - (@mEkmat * @mRabatt2) / 100.00, 2)
  END

  -- Rabatt 1 ausrechnen
  IF @mCalculate = 1 AND @mPreiskz = 2 BEGIN
    IF @mBruttoek > @mEkmat AND @mBruttoek > 0 BEGIN
      SET @mRabatt1 = ROUND(100.0 * ((@mBruttoek - @mEkmat) / @mBruttoek), 2) 
      SET @mRabatt2 = 0
	END ELSE BEGIN
	  SET @mBruttoek = @mEkmat
      SET @mRabatt1 = 0
      SET @mRabatt2 = 0
	END
  END

  update ut_Stammpositionen
    set Bruttoek = @mBruttoek, Rabgru = @mRabgru, Rabatt1 = @mRabatt1, Rabatt2 = @mRabatt2, Ekmat = @mEkmat, Vkm2 = @mVkm2
	  , Preiskz = @mPreiskz, Datumpreis = @mDatumpreis, Gebinde = @mGebinde, Pe = @mPe
	where RecId = @mRecId
    --where Lkz = @Param_Lkz and Artnr = @Param_Artnr 
GO

-- -----------------------------------------------------------------------------------------------------------
-- Datanorm(4) C-Satz Preispflege auslösen 
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_Datanorm_C_Preispflege')
  DROP PROCEDURE [dbo].[itcproc_Datanorm_C_Preispflege]
GO
CREATE PROCEDURE [dbo].[itcproc_Datanorm_C_Preispflege]
  @mLkz varchar(2)
AS
  SET NOCOUNT ON
  DECLARE @mRecId integer
  DECLARE @mArtnr varchar(18)
  DECLARE @mBruttoek float
  DECLARE @mRabatt1 float
  DECLARE @mRabatt2 float
  DECLARE @m_Ekmat float

  DECLARE C CURSOR LOCAL FOR SELECT sp.RecId, Artnr, sp.Bruttoek, rg.WERT2, sp.Rabatt2 FROM ut_Stammpositionen sp WITH(INDEX=Ik_Key00)
    left join ut_RabWaGruppen rg on rg.BEREICH = 'R' and rg.LKZ = sp.Lkz and rg.IDENT = sp.Rabgru
    WHERE sp.Lkz = @mLkz AND sp.Preiskz = 1
  OPEN C

  FETCH NEXT FROM C INTO @mRecId, @mArtnr, @mBruttoek, @mRabatt1, @mRabatt2
  WHILE @@FETCH_STATUS = 0 BEGIN
    IF NOT @mRabatt1 IS NULL BEGIN
      SET @m_Ekmat = @mBruttoek
	  IF @mRabatt1 <> 0 SET @m_Ekmat = ROUND(@m_Ekmat - (@m_Ekmat * @mRabatt1) / 100.00, 2)
  	  IF @mRabatt2 <> 0 SET @m_Ekmat = ROUND(@m_Ekmat - (@m_Ekmat * @mRabatt2) / 100.00, 2)
      UPDATE ut_Stammpositionen SET Rabatt1 = @mRabatt1, Ekmat = @m_Ekmat WHERE RecId = @mRecId
    END
    FETCH NEXT FROM C INTO @mRecId, @mArtnr, @mBruttoek, @mRabatt1, @mRabatt2
  END
  CLOSE C
  DEALLOCATE C
GO

-- -----------------------------------------------------------------------------------------------------------
-- Datanorm(4) C-Satz Textpflege auslösen 
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_Datanorm_C_Textpflege')
  DROP PROCEDURE [dbo].[itcproc_Datanorm_C_Textpflege]
GO
CREATE PROCEDURE [dbo].[itcproc_Datanorm_C_Textpflege]
  @Param_Lkz   varchar(2)
 ,@Param_KzDrk varchar(10)
AS
  SET NOCOUNT ON
  DECLARE @mKzDrk integer = dbo.itcfunc_integer(@Param_KzDrk)

  UPDATE dbo.ut_Stammpositionen SET Kzdrk1 = @mKzDrk WHERE Lkz = @Param_Lkz
GO

-- -----------------------------------------------------------------------------------------------------------
-- Datanorm(4) J-Satz Setmerker 3 oder Z verarbeiten
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_Datanorm_J_Zubehoer')
  DROP PROCEDURE [dbo].[itcproc_Datanorm_J_Zubehoer]
GO
CREATE PROCEDURE [dbo].[itcproc_Datanorm_J_Zubehoer]
  @P_Dummy varchar(10)
 ,@P_Lkz varchar(2) = null
 ,@P_Artnr varchar(18) = null
 ,@P_LfdNr varchar(2) = null
 ,@P_RefLkz varchar(2) = null
 ,@P_RefArtnr varchar(18) = null
 ,@P_RefMenge varchar(10) = null
AS
  SET NOCOUNT ON

  DECLARE @PV_RefMenge float = dbo.itcfunc_float(@P_RefMenge)
  DECLARE @mRecIdKt    integer
  SELECT @mRecIdKt = RecId FROM ut_Kleinteile WHERE Lkz = @P_Lkz AND Artnr = @P_Artnr AND LfdNr = @P_LfdNr
  IF @mRecIdKt IS NULL BEGIN
    SET @mRecIdKt = dbo.itcfunc_NextRecId_Kleinteile()
	INSERT INTO ut_Kleinteile (RecId,Lkz,Artnr,LfdNr,RefLkz,RefArtnr,RefMenge)
	    VALUES(@mRecIdKt,@P_Lkz,@P_Artnr,@P_LfdNr,@P_RefLkz,@P_RefArtnr,@PV_RefMenge)
  END ELSE
    UPDATE ut_Kleinteile SET RefLkz = @P_RefLkz, RefArtnr = @P_RefArtnr, RefMenge = @PV_RefMenge WHERE RecId = @mRecIdKt

  IF @P_LfdNr = '01'
    DELETE FROM ut_Kleinteile WHERE Lkz = @P_Lkz AND Artnr = @P_Artnr AND LfdNr < @P_LfdNr
GO

-- -----------------------------------------------------------------------------------------------------------
-- Datanorm(4) J-Satz allgemein
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_Datanorm_J')
  DROP PROCEDURE [dbo].[itcproc_Datanorm_J]
GO
CREATE PROCEDURE [dbo].[itcproc_Datanorm_J]
  @P_Dummy varchar(10)
 ,@P_Vkz varchar(1) = null
 ,@P_PktNr varchar(17) = null
 ,@P_LfdNr varchar(3) = null
 ,@P_Suchen varchar(15) = null
 ,@P_Txt1 varchar(42) = null
 ,@P_ArtLkz varchar(2) = null
 ,@P_ArtNr varchar(18) = null
 ,@P_Aufwand varchar(10) = null
AS
  IF @P_Vkz = 'L' BEGIN
    DELETE FROM ut_StammPakete WHERE PktNr = @P_PktNr
    RETURN
  END

  DECLARE @mRecIdKopf integer
  IF @P_LfdNr = '001' BEGIN
    DELETE FROM ut_StammPakete WHERE PktNr = @P_PktNr AND LfdNr < '000'

	SELECT TOP 1 @mRecIdKopf = RecId FROM ut_StammPakete WHERE  PktNr = @P_PktNr AND LfdNr = '000' 
	IF @mRecIdKopf IS NULL BEGIN
       SET @mRecIdKopf = dbo.itcfunc_NextRecId_StammPakete()
       INSERT INTO ut_StammPakete (RecId, PktNr, LfdNr, Aufwand) VALUES (@mRecIdKopf, @P_PktNr, '000', 1)
	END
	UPDATE ut_StammPakete SET Suchen = @P_Suchen, Txt1 = @P_Txt1 WHERE RecId = @mRecIdKopf
  END

  DECLARE @mRecIdZeile integer
  SELECT TOP 1 @mRecIdZeile = RecId FROM ut_StammPakete WHERE  PktNr = @P_PktNr AND LfdNr = @P_LfdNr 
  IF @mRecIdZeile IS NULL BEGIN
    SET @mRecIdZeile = dbo.itcfunc_NextRecId_StammPakete()
    INSERT INTO ut_StammPakete (RecId, PktNr, LfdNr) VALUES (@mRecIdZeile, @P_PktNr, @P_LfdNr)
  END

  DECLARE @PV_Aufwand float = dbo.itcfunc_float(@P_Aufwand)
  DECLARE @mTxt1 char(42)
  DECLARE @mTxt2 char(42)
  DECLARE @mTxt3 char(42)
  DECLARE @mMe   integer
  SELECT @mTxt1 = ISNULL(Bez1,''), @mTxt2 = ISNULL(Bez2,''), @mTxt3 = ISNULL(Merkmal,''), @mMe = ISNULL(Einheit,1)  FROM ut_Stammpositionen WHERE Lkz = @P_ArtLkz AND Artnr = @P_ArtNr
  UPDATE ut_StammPakete SET ArtLkz = @P_ArtLkz, ArtNr = @P_ArtNr, Aufwand = @PV_Aufwand, Txt1 = @mTxt1, Txt2 = @mTxt2, Txt3 = @mTxt3, Einheit = @mMe WHERE RecId = @mRecIdZeile
GO
