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

IF 0 = (SELECT count(*) from [sysobjects] where ([xtype] = 'U' or [xtype] = 'V') and [name] = 'ut_Stammpositionen')
BEGIN
CREATE TABLE [dbo].[ut_Stammpositionen]
(
  [RecId]  integer NOT NULL
 ,[Lkz] char(2) DEFAULT ''
 ,[Artnr] char(18) DEFAULT ''
 ,[Suchen] char(15) DEFAULT ''
 ,[Bez1] char(42) DEFAULT ''
 ,[Bez2] char(42) DEFAULT ''
 ,[Merkmal] char(42) DEFAULT ''
 ,[Textnr] char(18) DEFAULT ''
 ,[Kzpos] char(1) DEFAULT ''
 ,[Kzlager] char(1) DEFAULT ''
 ,[Einheit] integer DEFAULT 0
 ,[Kglohn] integer DEFAULT 0
 ,[Kgmat] integer DEFAULT 0
 ,[Zeit] float DEFAULT 0
 ,[Zubehoerdm] float DEFAULT 0
 ,[Vkl2] float DEFAULT 0
 ,[Vkm2] float DEFAULT 0
 ,[Kzzubehoer] char(1) DEFAULT ''
 ,[Bstnr] char(20) DEFAULT ''
 ,[Katalogseite] char(4) DEFAULT ''
 ,[Kzmwst] integer DEFAULT 0
 ,[Wargru] char(6) DEFAULT ''
 ,[Rabgru] char(6) DEFAULT ''
 ,[NeAnteil] tinyint DEFAULT 0
 ,[Pe] integer DEFAULT 0
 ,[Bruttoek] float DEFAULT 0
 ,[Rabatt1] float DEFAULT 0
 ,[Rabatt2] float DEFAULT 0
 ,[Ekmat] float DEFAULT 0
 ,[Datumpreis] datetime DEFAULT CONVERT(datetime,'1900-01-01',120)
 ,[Lkzlager] char(2) DEFAULT ''
 ,[Artnrlager] char(18) DEFAULT ''
 ,[Lagerort] char(15) DEFAULT ''
 ,[Minbest] float DEFAULT 0
 ,[Bstbest] float DEFAULT 0
 ,[Lagbest] float DEFAULT 0
 ,[Invbest] float DEFAULT 0
 ,[Wekum] float DEFAULT 0
 ,[Wakum] float DEFAULT 0
 ,[Datumlager] datetime DEFAULT CONVERT(datetime,'1900-01-01',120)
 ,[Durchek] float DEFAULT 0
 ,[Lagerwert] float DEFAULT 0
 ,[Kzdrk1] integer DEFAULT 0
 ,[Kzdrk2] integer DEFAULT 0
 ,[Eklohnh] float DEFAULT 0
 ,[Kzinventur] integer DEFAULT 0
 ,[Gebinde] float DEFAULT 0
 ,[Lkztextnr] char(2) DEFAULT ''
 ,[D6] char(5) DEFAULT ''
 ,[Invdatum] datetime DEFAULT CONVERT(datetime,'1900-01-01',120)
 ,[Bildnummer] char(8) DEFAULT ''
 ,[Menge] float DEFAULT 0
 ,[Vkladen] float DEFAULT 0
 ,[Preiskz] integer DEFAULT 0
 ,[Textkz] integer DEFAULT 0
 ,[EANcode] char(14) DEFAULT ''
 ,[Gruppe] char(6) DEFAULT ''
 ,[D7] char(28) DEFAULT ''
 ,[KzVk2JN] tinyint DEFAULT 0
 ,[KzFremdJN] tinyint DEFAULT 0
 ,[SollEkJe1] float DEFAULT 0
 ,[OmdDatum] datetime DEFAULT CONVERT(datetime,'1900-01-01',120)
 ,[Resbest] float DEFAULT 0
  CONSTRAINT [Pk_Stammpositionen]  PRIMARY KEY NONCLUSTERED ( [RecId] )
)
END
GO

-- drop obsolete triggers and the triggered table with keyvalues
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'TR'   and [name] = 'tr_Stammpositionen_KeyDel')
  DROP TRIGGER [dbo].[tr_Stammpositionen_KeyDel]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'TR'   and [name] = 'tr_Stammpositionen_KeyUpd')
  DROP TRIGGER [dbo].[tr_Stammpositionen_KeyUpd]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'U' and [name] = 'ut_Stammpositionen_Keys')
  DROP TABLE [dbo].[ut_Stammpositionen_Keys]
GO

-- add new fields
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lkz')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Lkz] char(2) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Artnr')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Artnr] char(18) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Suchen')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Suchen] char(15) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bez1')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Bez1] char(42) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bez2')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Bez2] char(42) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Merkmal')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Merkmal] char(42) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Textnr')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Textnr] char(18) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kzpos')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Kzpos] char(1) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kzlager')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Kzlager] char(1) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Einheit')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Einheit] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kglohn')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Kglohn] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kgmat')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Kgmat] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Zeit')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Zeit] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Zubehoerdm')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Zubehoerdm] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Vkl2')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Vkl2] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Vkm2')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Vkm2] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kzzubehoer')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Kzzubehoer] char(1) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bstnr')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Bstnr] char(20) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Katalogseite')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Katalogseite] char(4) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kzmwst')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Kzmwst] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Wargru')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Wargru] char(6) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Rabgru')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Rabgru] char(6) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'NeAnteil')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [NeAnteil] tinyint NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Pe')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Pe] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bruttoek')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Bruttoek] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Rabatt1')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Rabatt1] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Rabatt2')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Rabatt2] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Ekmat')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Ekmat] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Datumpreis')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Datumpreis] datetime NOT NULL DEFAULT CONVERT(datetime,'1900-01-01',120)
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lkzlager')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Lkzlager] char(2) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Artnrlager')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Artnrlager] char(18) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lagerort')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Lagerort] char(15) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Minbest')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Minbest] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bstbest')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Bstbest] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lagbest')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Lagbest] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Invbest')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Invbest] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Wekum')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Wekum] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Wakum')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Wakum] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Datumlager')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Datumlager] datetime NOT NULL DEFAULT CONVERT(datetime,'1900-01-01',120)
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Durchek')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Durchek] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lagerwert')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Lagerwert] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kzdrk1')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Kzdrk1] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kzdrk2')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Kzdrk2] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Eklohnh')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Eklohnh] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kzinventur')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Kzinventur] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Gebinde')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Gebinde] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lkztextnr')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Lkztextnr] char(2) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'D6')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [D6] char(5) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Invdatum')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Invdatum] datetime NOT NULL DEFAULT CONVERT(datetime,'1900-01-01',120)
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bildnummer')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Bildnummer] char(8) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Menge')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Menge] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Vkladen')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Vkladen] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Preiskz')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Preiskz] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Textkz')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Textkz] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'EANcode')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [EANcode] char(14) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Gruppe')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Gruppe] char(6) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'D7')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [D7] char(28) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'KzVk2JN')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [KzVk2JN] tinyint NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'KzFremdJN')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [KzFremdJN] tinyint NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'SollEkJe1')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [SollEkJe1] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'OmdDatum')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [OmdDatum] datetime NOT NULL DEFAULT CONVERT(datetime,'1900-01-01',120)
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Resbest')
BEGIN
  ALTER TABLE [dbo].[ut_Stammpositionen] ADD [Resbest] float NOT NULL DEFAULT 0
END
GO

-- check lengths of text fields
IF (2 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lkz'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lkz'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Lkz] char(2)
END
GO
IF (2 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lkz'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lkz'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Lkz] varchar(2)
END
GO
IF (18 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Artnr'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Artnr'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Artnr] char(18)
END
GO
IF (18 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Artnr'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Artnr'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Artnr] varchar(18)
END
GO
IF (15 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Suchen'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Suchen'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Suchen] char(15)
END
GO
IF (15 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Suchen'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Suchen'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Suchen] varchar(15)
END
GO
IF (42 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bez1'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bez1'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Bez1] char(42)
END
GO
IF (42 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bez1'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bez1'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Bez1] varchar(42)
END
GO
IF (42 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bez2'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bez2'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Bez2] char(42)
END
GO
IF (42 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bez2'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bez2'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Bez2] varchar(42)
END
GO
IF (42 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Merkmal'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Merkmal'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Merkmal] char(42)
END
GO
IF (42 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Merkmal'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Merkmal'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Merkmal] varchar(42)
END
GO
IF (18 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Textnr'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Textnr'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Textnr] char(18)
END
GO
IF (18 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Textnr'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Textnr'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Textnr] varchar(18)
END
GO
IF (1 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kzpos'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kzpos'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Kzpos] char(1)
END
GO
IF (1 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kzpos'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kzpos'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Kzpos] varchar(1)
END
GO
IF (1 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kzlager'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kzlager'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Kzlager] char(1)
END
GO
IF (1 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kzlager'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kzlager'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Kzlager] varchar(1)
END
GO
IF (1 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kzzubehoer'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kzzubehoer'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Kzzubehoer] char(1)
END
GO
IF (1 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kzzubehoer'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Kzzubehoer'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Kzzubehoer] varchar(1)
END
GO
IF (20 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bstnr'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bstnr'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Bstnr] char(20)
END
GO
IF (20 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bstnr'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bstnr'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Bstnr] varchar(20)
END
GO
IF (4 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Katalogseite'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Katalogseite'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Katalogseite] char(4)
END
GO
IF (4 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Katalogseite'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Katalogseite'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Katalogseite] varchar(4)
END
GO
IF (6 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Wargru'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Wargru'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Wargru] char(6)
END
GO
IF (6 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Wargru'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Wargru'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Wargru] varchar(6)
END
GO
IF (6 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Rabgru'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Rabgru'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Rabgru] char(6)
END
GO
IF (6 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Rabgru'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Rabgru'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Rabgru] varchar(6)
END
GO
IF (2 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lkzlager'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lkzlager'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Lkzlager] char(2)
END
GO
IF (2 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lkzlager'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lkzlager'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Lkzlager] varchar(2)
END
GO
IF (18 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Artnrlager'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Artnrlager'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Artnrlager] char(18)
END
GO
IF (18 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Artnrlager'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Artnrlager'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Artnrlager] varchar(18)
END
GO
IF (15 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lagerort'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lagerort'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Lagerort] char(15)
END
GO
IF (15 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lagerort'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lagerort'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Lagerort] varchar(15)
END
GO
IF (2 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lkztextnr'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lkztextnr'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Lkztextnr] char(2)
END
GO
IF (2 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lkztextnr'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Lkztextnr'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Lkztextnr] varchar(2)
END
GO
IF (5 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'D6'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'D6'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [D6] char(5)
END
GO
IF (5 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'D6'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'D6'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [D6] varchar(5)
END
GO
IF (8 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bildnummer'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bildnummer'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Bildnummer] char(8)
END
GO
IF (8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bildnummer'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Bildnummer'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Bildnummer] varchar(8)
END
GO
IF (14 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'EANcode'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'EANcode'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [EANcode] char(14)
END
GO
IF (14 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'EANcode'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'EANcode'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [EANcode] varchar(14)
END
GO
IF (6 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Gruppe'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Gruppe'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Gruppe] char(6)
END
GO
IF (6 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Gruppe'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'Gruppe'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [Gruppe] varchar(6)
END
GO
IF (28 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'D7'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'D7'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [D7] char(28)
END
GO
IF (28 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'D7'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Stammpositionen' and sc.[name] = 'D7'))
BEGIN
   ALTER TABLE [dbo].[ut_Stammpositionen] ALTER COLUMN [D7] varchar(28)
END
GO

-- create dedicated view
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'Stammpositionen')
  DROP VIEW [dbo].[Stammpositionen]
GO
CREATE VIEW [dbo].[Stammpositionen] AS
  SELECT [RecId]
 ,[Lkz]
 ,[Artnr]
 ,[Suchen]
 ,[Bez1]
 ,[Bez2]
 ,[Merkmal]
 ,[Textnr]
 ,[Kzpos]
 ,[Kzlager]
 ,[Einheit]
 ,[Kglohn]
 ,[Kgmat]
 ,[Zeit]
 ,[Zubehoerdm]
 ,[Vkl2]
 ,[Vkm2]
 ,[Kzzubehoer]
 ,[Bstnr]
 ,[Katalogseite]
 ,[Kzmwst]
 ,[Wargru]
 ,[Rabgru]
 ,[NeAnteil]
 ,[Pe]
 ,[Bruttoek]
 ,[Rabatt1]
 ,[Rabatt2]
 ,[Ekmat]
 ,[Datumpreis]
 ,[Lkzlager]
 ,[Artnrlager]
 ,[Lagerort]
 ,[Minbest]
 ,[Bstbest]
 ,[Lagbest]
 ,[Invbest]
 ,[Wekum]
 ,[Wakum]
 ,[Datumlager]
 ,[Durchek]
 ,[Lagerwert]
 ,[Kzdrk1]
 ,[Kzdrk2]
 ,[Eklohnh]
 ,[Kzinventur]
 ,[Gebinde]
 ,[Lkztextnr]
 ,[D6]
 ,[Invdatum]
 ,[Bildnummer]
 ,[Menge]
 ,[Vkladen]
 ,[Preiskz]
 ,[Textkz]
 ,[EANcode]
 ,[Gruppe]
 ,[D7]
 ,[KzVk2JN]
 ,[KzFremdJN]
 ,[SollEkJe1]
 ,[OmdDatum]
 ,[Resbest]
  FROM [dbo].[ut_Stammpositionen]
GO


-- create function to get the next RecId
IF OBJECT_ID (N'itcfunc_NextRecId_Stammpositionen', N'FN') IS NOT NULL
  DROP FUNCTION dbo.itcfunc_NextRecId_Stammpositionen
GO
CREATE FUNCTION dbo.itcfunc_NextRecId_Stammpositionen() RETURNS integer
AS
BEGIN
  RETURN isnull((SELECT MAX(RecId) FROM [dbo].[ut_Stammpositionen] WITH (READUNCOMMITTED)),0) + 1
END
GO

-- select with classic client
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Sel_Stammpositionen')
  DROP PROCEDURE [dbo].[up_Sel_Stammpositionen]
GO
CREATE PROCEDURE [dbo].[up_Sel_Stammpositionen]  @mRI integer
AS
  SELECT
    [Lkz]
   ,[Artnr]
   ,[Suchen]
   ,[Bez1]
   ,[Bez2]
   ,[Merkmal]
   ,[Textnr]
   ,[Kzpos]
   ,[Kzlager]
   ,[Einheit]
   ,[Kglohn]
   ,[Kgmat]
   ,[Zeit]
   ,[Zubehoerdm]
   ,[Vkl2]
   ,[Vkm2]
   ,[Kzzubehoer]
   ,[Bstnr]
   ,[Katalogseite]
   ,[Kzmwst]
   ,[Wargru]
   ,[Rabgru]
   ,[NeAnteil]
   ,[Pe]
   ,[Bruttoek]
   ,[Rabatt1]
   ,[Rabatt2]
   ,[Ekmat]
   ,[Datumpreis]
   ,[Lkzlager]
   ,[Artnrlager]
   ,[Lagerort]
   ,[Minbest]
   ,[Bstbest]
   ,[Lagbest]
   ,[Invbest]
   ,[Wekum]
   ,[Wakum]
   ,[Datumlager]
   ,[Durchek]
   ,[Lagerwert]
   ,[Kzdrk1]
   ,[Kzdrk2]
   ,[Eklohnh]
   ,[Kzinventur]
   ,[Gebinde]
   ,[Lkztextnr]
   ,[D6]
   ,[Invdatum]
   ,[Bildnummer]
   ,[Menge]
   ,[Vkladen]
   ,[Preiskz]
   ,[Textkz]
   ,[EANcode]
   ,[Gruppe]
   ,[D7]
   ,[KzVk2JN]
   ,[KzFremdJN]
   ,[SollEkJe1]
   ,[OmdDatum]
   ,[Resbest]
  FROM [dbo].[ut_Stammpositionen] WHERE RecId = @mRI
GO

-- select with modern clients
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Sel2_Stammpositionen')
  DROP PROCEDURE [dbo].[up_Sel2_Stammpositionen]
GO
CREATE PROCEDURE [dbo].[up_Sel2_Stammpositionen]  @Param_RecId varchar(20)
AS
  DECLARE @mRI integer = dbo.itcfunc_integer(@Param_RecId)
  SELECT * FROM [dbo].[Stammpositionen] WHERE RecId = @mRI
GO

-- insert with classic client
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Append_Stammpositionen')
  DROP PROCEDURE [dbo].[up_Append_Stammpositionen]
GO
CREATE PROCEDURE [dbo].[up_Append_Stammpositionen]
   @Param_Lkz char(2)
  ,@Param_Artnr char(18)
  ,@Param_Suchen char(15)
  ,@Param_Bez1 char(42)
  ,@Param_Bez2 char(42)
  ,@Param_Merkmal char(42)
  ,@Param_Textnr char(18)
  ,@Param_Kzpos char(1)
  ,@Param_Kzlager char(1)
  ,@Param_Einheit integer
  ,@Param_Kglohn integer
  ,@Param_Kgmat integer
  ,@Param_Zeit float
  ,@Param_Zubehoerdm float
  ,@Param_Vkl2 float
  ,@Param_Vkm2 float
  ,@Param_Kzzubehoer char(1)
  ,@Param_Bstnr char(20)
  ,@Param_Katalogseite char(4)
  ,@Param_Kzmwst integer
  ,@Param_Wargru char(6)
  ,@Param_Rabgru char(6)
  ,@Param_NeAnteil tinyint
  ,@Param_Pe integer
  ,@Param_Bruttoek float
  ,@Param_Rabatt1 float
  ,@Param_Rabatt2 float
  ,@Param_Ekmat float
  ,@Param_Datumpreis datetime
  ,@Param_Lkzlager char(2)
  ,@Param_Artnrlager char(18)
  ,@Param_Lagerort char(15)
  ,@Param_Minbest float
  ,@Param_Bstbest float
  ,@Param_Lagbest float
  ,@Param_Invbest float
  ,@Param_Wekum float
  ,@Param_Wakum float
  ,@Param_Datumlager datetime
  ,@Param_Durchek float
  ,@Param_Lagerwert float
  ,@Param_Kzdrk1 integer
  ,@Param_Kzdrk2 integer
  ,@Param_Eklohnh float
  ,@Param_Kzinventur integer
  ,@Param_Gebinde float
  ,@Param_Lkztextnr char(2)
  ,@Param_D6 char(5)
  ,@Param_Invdatum datetime
  ,@Param_Bildnummer char(8)
  ,@Param_Menge float
  ,@Param_Vkladen float
  ,@Param_Preiskz integer
  ,@Param_Textkz integer
  ,@Param_EANcode char(14)
  ,@Param_Gruppe char(6)
  ,@Param_D7 char(28)
  ,@Param_KzVk2JN tinyint
  ,@Param_KzFremdJN tinyint
  ,@Param_SollEkJe1 float
  ,@Param_OmdDatum datetime
  ,@Param_Resbest float
AS
  DECLARE @NeueId integer
  DECLARE @m_CountDown integer = 10
  WHILE @m_CountDown > 0 BEGIN
    BEGIN TRANSACTION
    BEGIN TRY
      SET @NeueId = dbo.itcfunc_NextRecId_Stammpositionen()
      INSERT INTO [dbo].[ut_Stammpositionen] (RecId
        ,[Lkz]
        ,[Artnr]
        ,[Suchen]
        ,[Bez1]
        ,[Bez2]
        ,[Merkmal]
        ,[Textnr]
        ,[Kzpos]
        ,[Kzlager]
        ,[Einheit]
        ,[Kglohn]
        ,[Kgmat]
        ,[Zeit]
        ,[Zubehoerdm]
        ,[Vkl2]
        ,[Vkm2]
        ,[Kzzubehoer]
        ,[Bstnr]
        ,[Katalogseite]
        ,[Kzmwst]
        ,[Wargru]
        ,[Rabgru]
        ,[NeAnteil]
        ,[Pe]
        ,[Bruttoek]
        ,[Rabatt1]
        ,[Rabatt2]
        ,[Ekmat]
        ,[Datumpreis]
        ,[Lkzlager]
        ,[Artnrlager]
        ,[Lagerort]
        ,[Minbest]
        ,[Bstbest]
        ,[Lagbest]
        ,[Invbest]
        ,[Wekum]
        ,[Wakum]
        ,[Datumlager]
        ,[Durchek]
        ,[Lagerwert]
        ,[Kzdrk1]
        ,[Kzdrk2]
        ,[Eklohnh]
        ,[Kzinventur]
        ,[Gebinde]
        ,[Lkztextnr]
        ,[D6]
        ,[Invdatum]
        ,[Bildnummer]
        ,[Menge]
        ,[Vkladen]
        ,[Preiskz]
        ,[Textkz]
        ,[EANcode]
        ,[Gruppe]
        ,[D7]
        ,[KzVk2JN]
        ,[KzFremdJN]
        ,[SollEkJe1]
        ,[OmdDatum]
        ,[Resbest]
          ) VALUES (@NeueId
        ,UPPER(RTRIM(@Param_Lkz))
        ,UPPER(RTRIM(@Param_Artnr))
        ,UPPER(RTRIM(@Param_Suchen))
        ,RTRIM(@Param_Bez1)
        ,RTRIM(@Param_Bez2)
        ,RTRIM(@Param_Merkmal)
        ,UPPER(RTRIM(@Param_Textnr))
        ,UPPER(RTRIM(@Param_Kzpos))
        ,UPPER(RTRIM(@Param_Kzlager))
        ,@Param_Einheit
        ,@Param_Kglohn
        ,@Param_Kgmat
        ,@Param_Zeit
        ,@Param_Zubehoerdm
        ,@Param_Vkl2
        ,@Param_Vkm2
        ,UPPER(RTRIM(@Param_Kzzubehoer))
        ,UPPER(RTRIM(@Param_Bstnr))
        ,UPPER(RTRIM(@Param_Katalogseite))
        ,@Param_Kzmwst
        ,UPPER(RTRIM(@Param_Wargru))
        ,UPPER(RTRIM(@Param_Rabgru))
        ,@Param_NeAnteil
        ,@Param_Pe
        ,@Param_Bruttoek
        ,@Param_Rabatt1
        ,@Param_Rabatt2
        ,@Param_Ekmat
        ,@Param_Datumpreis
        ,UPPER(RTRIM(@Param_Lkzlager))
        ,UPPER(RTRIM(@Param_Artnrlager))
        ,UPPER(RTRIM(@Param_Lagerort))
        ,@Param_Minbest
        ,@Param_Bstbest
        ,@Param_Lagbest
        ,@Param_Invbest
        ,@Param_Wekum
        ,@Param_Wakum
        ,@Param_Datumlager
        ,@Param_Durchek
        ,@Param_Lagerwert
        ,@Param_Kzdrk1
        ,@Param_Kzdrk2
        ,@Param_Eklohnh
        ,@Param_Kzinventur
        ,@Param_Gebinde
        ,UPPER(RTRIM(@Param_Lkztextnr))
        ,RTRIM(@Param_D6)
        ,@Param_Invdatum
        ,UPPER(RTRIM(@Param_Bildnummer))
        ,@Param_Menge
        ,@Param_Vkladen
        ,@Param_Preiskz
        ,@Param_Textkz
        ,UPPER(RTRIM(@Param_EANcode))
        ,UPPER(RTRIM(@Param_Gruppe))
        ,RTRIM(@Param_D7)
        ,@Param_KzVk2JN
        ,@Param_KzFremdJN
        ,@Param_SollEkJe1
        ,@Param_OmdDatum
        ,@Param_Resbest
          )
      COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
      ROLLBACK TRANSACTION
      SET @NeueId = 0
    END CATCH

    IF @NeueId <> 0
      SET @m_CountDown = 0
    ELSE
      SET @m_CountDown = @m_CountDown - 1

    IF @m_CountDown > 0
      WAITFOR DELAY '00:00:00.050'
  END -- WHILE
  SELECT @NeueId AS RecId
GO

-- insert with obsolete clients
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Add_Stammpositionen')
  DROP PROCEDURE [dbo].[up_Add_Stammpositionen]
GO
CREATE PROCEDURE [dbo].[up_Add_Stammpositionen]
AS
  DECLARE @NeueId integer = dbo.itcfunc_NextRecId_Stammpositionen()
  INSERT INTO [dbo].[ut_Stammpositionen] (RecId) VALUES( @NeueId )
  IF @@ERROR = 0
    SELECT @NeueId AS RecId
  ELSE
    SELECT 0 AS RecId
GO

-- update with classic client
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Upd_Stammpositionen')
  DROP PROCEDURE [dbo].[up_Upd_Stammpositionen]
GO
CREATE PROCEDURE [dbo].[up_Upd_Stammpositionen] @mRI integer
  ,@Param_Lkz char(2)
  ,@Param_Artnr char(18)
  ,@Param_Suchen char(15)
  ,@Param_Bez1 char(42)
  ,@Param_Bez2 char(42)
  ,@Param_Merkmal char(42)
  ,@Param_Textnr char(18)
  ,@Param_Kzpos char(1)
  ,@Param_Kzlager char(1)
  ,@Param_Einheit integer
  ,@Param_Kglohn integer
  ,@Param_Kgmat integer
  ,@Param_Zeit float
  ,@Param_Zubehoerdm float
  ,@Param_Vkl2 float
  ,@Param_Vkm2 float
  ,@Param_Kzzubehoer char(1)
  ,@Param_Bstnr char(20)
  ,@Param_Katalogseite char(4)
  ,@Param_Kzmwst integer
  ,@Param_Wargru char(6)
  ,@Param_Rabgru char(6)
  ,@Param_NeAnteil tinyint
  ,@Param_Pe integer
  ,@Param_Bruttoek float
  ,@Param_Rabatt1 float
  ,@Param_Rabatt2 float
  ,@Param_Ekmat float
  ,@Param_Datumpreis datetime
  ,@Param_Lkzlager char(2)
  ,@Param_Artnrlager char(18)
  ,@Param_Lagerort char(15)
  ,@Param_Minbest float
  ,@Param_Bstbest float
  ,@Param_Lagbest float
  ,@Param_Invbest float
  ,@Param_Wekum float
  ,@Param_Wakum float
  ,@Param_Datumlager datetime
  ,@Param_Durchek float
  ,@Param_Lagerwert float
  ,@Param_Kzdrk1 integer
  ,@Param_Kzdrk2 integer
  ,@Param_Eklohnh float
  ,@Param_Kzinventur integer
  ,@Param_Gebinde float
  ,@Param_Lkztextnr char(2)
  ,@Param_D6 char(5)
  ,@Param_Invdatum datetime
  ,@Param_Bildnummer char(8)
  ,@Param_Menge float
  ,@Param_Vkladen float
  ,@Param_Preiskz integer
  ,@Param_Textkz integer
  ,@Param_EANcode char(14)
  ,@Param_Gruppe char(6)
  ,@Param_D7 char(28)
  ,@Param_KzVk2JN tinyint
  ,@Param_KzFremdJN tinyint
  ,@Param_SollEkJe1 float
  ,@Param_OmdDatum datetime
  ,@Param_Resbest float
AS
  UPDATE [dbo].[ut_Stammpositionen] SET
    [Lkz] = UPPER(RTRIM(@Param_Lkz))
   ,[Artnr] = UPPER(RTRIM(@Param_Artnr))
   ,[Suchen] = UPPER(RTRIM(@Param_Suchen))
   ,[Bez1] = RTRIM(@Param_Bez1)
   ,[Bez2] = RTRIM(@Param_Bez2)
   ,[Merkmal] = RTRIM(@Param_Merkmal)
   ,[Textnr] = UPPER(RTRIM(@Param_Textnr))
   ,[Kzpos] = UPPER(RTRIM(@Param_Kzpos))
   ,[Kzlager] = UPPER(RTRIM(@Param_Kzlager))
   ,[Einheit] = @Param_Einheit
   ,[Kglohn] = @Param_Kglohn
   ,[Kgmat] = @Param_Kgmat
   ,[Zeit] = @Param_Zeit
   ,[Zubehoerdm] = @Param_Zubehoerdm
   ,[Vkl2] = @Param_Vkl2
   ,[Vkm2] = @Param_Vkm2
   ,[Kzzubehoer] = UPPER(RTRIM(@Param_Kzzubehoer))
   ,[Bstnr] = UPPER(RTRIM(@Param_Bstnr))
   ,[Katalogseite] = UPPER(RTRIM(@Param_Katalogseite))
   ,[Kzmwst] = @Param_Kzmwst
   ,[Wargru] = UPPER(RTRIM(@Param_Wargru))
   ,[Rabgru] = UPPER(RTRIM(@Param_Rabgru))
   ,[NeAnteil] = @Param_NeAnteil
   ,[Pe] = @Param_Pe
   ,[Bruttoek] = @Param_Bruttoek
   ,[Rabatt1] = @Param_Rabatt1
   ,[Rabatt2] = @Param_Rabatt2
   ,[Ekmat] = @Param_Ekmat
   ,[Datumpreis] = @Param_Datumpreis
   ,[Lkzlager] = UPPER(RTRIM(@Param_Lkzlager))
   ,[Artnrlager] = UPPER(RTRIM(@Param_Artnrlager))
   ,[Lagerort] = UPPER(RTRIM(@Param_Lagerort))
   ,[Minbest] = @Param_Minbest
   ,[Bstbest] = @Param_Bstbest
   ,[Lagbest] = @Param_Lagbest
   ,[Invbest] = @Param_Invbest
   ,[Wekum] = @Param_Wekum
   ,[Wakum] = @Param_Wakum
   ,[Datumlager] = @Param_Datumlager
   ,[Durchek] = @Param_Durchek
   ,[Lagerwert] = @Param_Lagerwert
   ,[Kzdrk1] = @Param_Kzdrk1
   ,[Kzdrk2] = @Param_Kzdrk2
   ,[Eklohnh] = @Param_Eklohnh
   ,[Kzinventur] = @Param_Kzinventur
   ,[Gebinde] = @Param_Gebinde
   ,[Lkztextnr] = UPPER(RTRIM(@Param_Lkztextnr))
   ,[D6] = RTRIM(@Param_D6)
   ,[Invdatum] = @Param_Invdatum
   ,[Bildnummer] = UPPER(RTRIM(@Param_Bildnummer))
   ,[Menge] = @Param_Menge
   ,[Vkladen] = @Param_Vkladen
   ,[Preiskz] = @Param_Preiskz
   ,[Textkz] = @Param_Textkz
   ,[EANcode] = UPPER(RTRIM(@Param_EANcode))
   ,[Gruppe] = UPPER(RTRIM(@Param_Gruppe))
   ,[D7] = RTRIM(@Param_D7)
   ,[KzVk2JN] = @Param_KzVk2JN
   ,[KzFremdJN] = @Param_KzFremdJN
   ,[SollEkJe1] = @Param_SollEkJe1
   ,[OmdDatum] = @Param_OmdDatum
   ,[Resbest] = @Param_Resbest
  WHERE RecId = @mRI
GO

-- update with modern clients
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Upd2_Stammpositionen')
  DROP PROCEDURE [dbo].[up_Upd2_Stammpositionen]
GO
CREATE PROCEDURE [dbo].[up_Upd2_Stammpositionen] @Param_RecId varchar(20)
  ,@Param_Lkz varchar(2)
  ,@Param_Artnr varchar(18)
  ,@Param_Suchen varchar(15)
  ,@Param_Bez1 varchar(42)
  ,@Param_Bez2 varchar(42)
  ,@Param_Merkmal varchar(42)
  ,@Param_Textnr varchar(18)
  ,@Param_Kzpos varchar(1)
  ,@Param_Kzlager varchar(1)
  ,@Param_Einheit varchar(4)
  ,@Param_Kglohn varchar(2)
  ,@Param_Kgmat varchar(2)
  ,@Param_Zeit varchar(10)
  ,@Param_Zubehoerdm varchar(10)
  ,@Param_Vkl2 varchar(10)
  ,@Param_Vkm2 varchar(10)
  ,@Param_Kzzubehoer varchar(1)
  ,@Param_Bstnr varchar(20)
  ,@Param_Katalogseite varchar(4)
  ,@Param_Kzmwst varchar(1)
  ,@Param_Wargru varchar(6)
  ,@Param_Rabgru varchar(6)
  ,@Param_NeAnteil varchar(1)
  ,@Param_Pe varchar(2)
  ,@Param_Bruttoek varchar(10)
  ,@Param_Rabatt1 varchar(6)
  ,@Param_Rabatt2 varchar(6)
  ,@Param_Ekmat varchar(10)
  ,@Param_Datumpreis varchar(10)
  ,@Param_Lkzlager varchar(2)
  ,@Param_Artnrlager varchar(18)
  ,@Param_Lagerort varchar(15)
  ,@Param_Minbest varchar(12)
  ,@Param_Bstbest varchar(12)
  ,@Param_Lagbest varchar(12)
  ,@Param_Invbest varchar(12)
  ,@Param_Wekum varchar(12)
  ,@Param_Wakum varchar(12)
  ,@Param_Datumlager varchar(10)
  ,@Param_Durchek varchar(14)
  ,@Param_Lagerwert varchar(10)
  ,@Param_Kzdrk1 varchar(1)
  ,@Param_Kzdrk2 varchar(1)
  ,@Param_Eklohnh varchar(6)
  ,@Param_Kzinventur varchar(1)
  ,@Param_Gebinde varchar(12)
  ,@Param_Lkztextnr varchar(2)
  ,@Param_D6 varchar(5)
  ,@Param_Invdatum varchar(10)
  ,@Param_Bildnummer varchar(8)
  ,@Param_Menge varchar(8)
  ,@Param_Vkladen varchar(10)
  ,@Param_Preiskz varchar(1)
  ,@Param_Textkz varchar(1)
  ,@Param_EANcode varchar(14)
  ,@Param_Gruppe varchar(6)
  ,@Param_D7 varchar(28)
  ,@Param_KzVk2JN varchar(1)
  ,@Param_KzFremdJN varchar(1)
  ,@Param_SollEkJe1 varchar(12)
  ,@Param_OmdDatum varchar(10)
  ,@Param_Resbest varchar(12)
AS
  DECLARE @mRI integer = dbo.itcfunc_integer(@Param_RecId)
  UPDATE [dbo].[ut_Stammpositionen] SET
    [Lkz] = UPPER(RTRIM(@Param_Lkz))
   ,[Artnr] = UPPER(RTRIM(@Param_Artnr))
   ,[Suchen] = UPPER(RTRIM(@Param_Suchen))
   ,[Bez1] = RTRIM(@Param_Bez1)
   ,[Bez2] = RTRIM(@Param_Bez2)
   ,[Merkmal] = RTRIM(@Param_Merkmal)
   ,[Textnr] = UPPER(RTRIM(@Param_Textnr))
   ,[Kzpos] = UPPER(RTRIM(@Param_Kzpos))
   ,[Kzlager] = UPPER(RTRIM(@Param_Kzlager))
   ,[Einheit] = dbo.itcfunc_integer(@Param_Einheit)
   ,[Kglohn] = dbo.itcfunc_integer(@Param_Kglohn)
   ,[Kgmat] = dbo.itcfunc_integer(@Param_Kgmat)
   ,[Zeit] = dbo.itcfunc_float(@Param_Zeit)
   ,[Zubehoerdm] = dbo.itcfunc_float(@Param_Zubehoerdm)
   ,[Vkl2] = dbo.itcfunc_float(@Param_Vkl2)
   ,[Vkm2] = dbo.itcfunc_float(@Param_Vkm2)
   ,[Kzzubehoer] = UPPER(RTRIM(@Param_Kzzubehoer))
   ,[Bstnr] = UPPER(RTRIM(@Param_Bstnr))
   ,[Katalogseite] = UPPER(RTRIM(@Param_Katalogseite))
   ,[Kzmwst] = dbo.itcfunc_integer(@Param_Kzmwst)
   ,[Wargru] = UPPER(RTRIM(@Param_Wargru))
   ,[Rabgru] = UPPER(RTRIM(@Param_Rabgru))
   ,[NeAnteil] = dbo.itcfunc_integer(@Param_NeAnteil)
   ,[Pe] = dbo.itcfunc_integer(@Param_Pe)
   ,[Bruttoek] = dbo.itcfunc_float(@Param_Bruttoek)
   ,[Rabatt1] = dbo.itcfunc_float(@Param_Rabatt1)
   ,[Rabatt2] = dbo.itcfunc_float(@Param_Rabatt2)
   ,[Ekmat] = dbo.itcfunc_float(@Param_Ekmat)
   ,[Datumpreis] = dbo.itcfunc_datetime(@Param_Datumpreis)
   ,[Lkzlager] = UPPER(RTRIM(@Param_Lkzlager))
   ,[Artnrlager] = UPPER(RTRIM(@Param_Artnrlager))
   ,[Lagerort] = UPPER(RTRIM(@Param_Lagerort))
   ,[Minbest] = dbo.itcfunc_float(@Param_Minbest)
   ,[Bstbest] = dbo.itcfunc_float(@Param_Bstbest)
   ,[Lagbest] = dbo.itcfunc_float(@Param_Lagbest)
   ,[Invbest] = dbo.itcfunc_float(@Param_Invbest)
   ,[Wekum] = dbo.itcfunc_float(@Param_Wekum)
   ,[Wakum] = dbo.itcfunc_float(@Param_Wakum)
   ,[Datumlager] = dbo.itcfunc_datetime(@Param_Datumlager)
   ,[Durchek] = dbo.itcfunc_float(@Param_Durchek)
   ,[Lagerwert] = dbo.itcfunc_float(@Param_Lagerwert)
   ,[Kzdrk1] = dbo.itcfunc_integer(@Param_Kzdrk1)
   ,[Kzdrk2] = dbo.itcfunc_integer(@Param_Kzdrk2)
   ,[Eklohnh] = dbo.itcfunc_float(@Param_Eklohnh)
   ,[Kzinventur] = dbo.itcfunc_integer(@Param_Kzinventur)
   ,[Gebinde] = dbo.itcfunc_float(@Param_Gebinde)
   ,[Lkztextnr] = UPPER(RTRIM(@Param_Lkztextnr))
   ,[D6] = RTRIM(@Param_D6)
   ,[Invdatum] = dbo.itcfunc_datetime(@Param_Invdatum)
   ,[Bildnummer] = UPPER(RTRIM(@Param_Bildnummer))
   ,[Menge] = dbo.itcfunc_float(@Param_Menge)
   ,[Vkladen] = dbo.itcfunc_float(@Param_Vkladen)
   ,[Preiskz] = dbo.itcfunc_integer(@Param_Preiskz)
   ,[Textkz] = dbo.itcfunc_integer(@Param_Textkz)
   ,[EANcode] = UPPER(RTRIM(@Param_EANcode))
   ,[Gruppe] = UPPER(RTRIM(@Param_Gruppe))
   ,[D7] = RTRIM(@Param_D7)
   ,[KzVk2JN] = dbo.itcfunc_integer(@Param_KzVk2JN)
   ,[KzFremdJN] = dbo.itcfunc_integer(@Param_KzFremdJN)
   ,[SollEkJe1] = dbo.itcfunc_float(@Param_SollEkJe1)
   ,[OmdDatum] = dbo.itcfunc_datetime(@Param_OmdDatum)
   ,[Resbest] = dbo.itcfunc_float(@Param_Resbest)
  WHERE RecId = @mRI

  EXECUTE [dbo].[up_Sel2_Stammpositionen] @mRI
GO

-- delete
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Del_Stammpositionen')
  DROP PROCEDURE [dbo].[up_Del_Stammpositionen]
GO
CREATE PROCEDURE [dbo].[up_Del_Stammpositionen] @mRI integer
AS
  DELETE FROM [dbo].[ut_Stammpositionen] WHERE RecId = @mRI
GO

-- text index
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_TrSel_Stammpositionen')
  DROP PROCEDURE [dbo].[up_TrSel_Stammpositionen]
GO
CREATE PROCEDURE [dbo].[up_TrSel_Stammpositionen]
AS
  SELECT RecId,
    LTRIM(RTRIM([Lkz]))
   +LTRIM(RTRIM([Artnr]))
   +LTRIM(RTRIM([Suchen]))
   +LTRIM(RTRIM([Bez1]))
   +LTRIM(RTRIM([Bez2]))
   +LTRIM(RTRIM([Merkmal]))
   +LTRIM(RTRIM([Textnr]))
   +LTRIM(RTRIM([Kzpos]))
   +LTRIM(RTRIM([Kzlager]))
   +LTRIM(RTRIM([Kzzubehoer]))
   +LTRIM(RTRIM([Bstnr]))
   +LTRIM(RTRIM([Katalogseite]))
   +LTRIM(RTRIM([Wargru]))
   +LTRIM(RTRIM([Rabgru]))
   +LTRIM(RTRIM([Lkzlager]))
   +LTRIM(RTRIM([Artnrlager]))
   +LTRIM(RTRIM([Lagerort]))
   +LTRIM(RTRIM([Lkztextnr]))
   +LTRIM(RTRIM([D6]))
   +LTRIM(RTRIM([Bildnummer]))
   +LTRIM(RTRIM([EANcode]))
   +LTRIM(RTRIM([Gruppe]))
   +LTRIM(RTRIM([D7]))
  AS Data FROM [dbo].[ut_Stammpositionen] WHERE RecId > 0 ORDER BY RecId
GO

-- reindex
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Reorg_Stammpositionen')
  DROP PROCEDURE [dbo].[up_Reorg_Stammpositionen]
GO
CREATE PROCEDURE [dbo].[up_Reorg_Stammpositionen] @bDbcc integer,@bRecId integer
AS
  IF (@bRecId = 1)
  BEGIN
    DECLARE  C        CURSOR FOR SELECT RecId FROM [dbo].[ut_Stammpositionen] ORDER BY RecId
    DECLARE  @CurrId  integer
    DECLARE  @NextId  integer
    --BEGIN TRANSACTION
    OPEN C
    SET @NextId = 1
    FETCH NEXT FROM C INTO @CurrId
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
      IF @CurrId <> @NextId
      UPDATE [dbo].[ut_Stammpositionen] SET RecId=@NextId WHERE CURRENT OF C
      SET @NextId = @NextId + 1
      FETCH NEXT FROM C INTO @CurrId
    END
    CLOSE C
    --COMMIT TRANSACTION
    DEALLOCATE C
  END

  IF (@bDbcc = 1)
  BEGIN
    DBCC DBREINDEX ( [ut_Stammpositionen] )
  END
GO

-- logical key  ORDER BY Lkz,Artnr,RecId
IF 0 < (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Stammpositionen' and so.[id] = si.[id] and si.[name] = 'Ik_Key00')
  DROP INDEX [Ik_Key00] ON [dbo].[ut_Stammpositionen]
GO
IF 0 = (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Stammpositionen' and so.[id] = si.[id] and si.[name] = 'Ik_Key00')
  CREATE INDEX [Ik_Key00] ON [dbo].[ut_Stammpositionen] (Lkz,Artnr,RecId)
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Stammpositionen_Key00')
  DROP PROCEDURE [dbo].[up_First_Stammpositionen_Key00]
GO
CREATE PROCEDURE [dbo].[up_First_Stammpositionen_Key00] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key00) ORDER BY Lkz,Artnr,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Stammpositionen_Key00')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Stammpositionen_Key00] AS EXECUTE [dbo].[up_First_Stammpositionen_Key00]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Stammpositionen_Key00')
  DROP PROCEDURE [dbo].[up_Last_Stammpositionen_Key00]
GO
CREATE PROCEDURE [dbo].[up_Last_Stammpositionen_Key00] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key00) ORDER BY Lkz desc,Artnr desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Stammpositionen_Key00')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Stammpositionen_Key00] AS EXECUTE [dbo].[up_Last_Stammpositionen_Key00]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Stammpositionen_Key00')
  DROP PROCEDURE [dbo].[up_Next_Stammpositionen_Key00]
GO
CREATE PROCEDURE [dbo].[up_Next_Stammpositionen_Key00] @oldRecId integer AS
  DECLARE @oldLkz char(2)
  DECLARE @oldArtnr char(18)
  SELECT @oldLkz = Lkz, @oldArtnr = Artnr FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key00)
    WHERE (Lkz = @oldLkz) AND (Artnr > @oldArtnr)
    ORDER BY Lkz,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key00)
    WHERE (Lkz > @oldLkz)
    ORDER BY Lkz,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next_Stammpositionen_Key00')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Stammpositionen_Key00] @oldRecId integer AS EXECUTE [dbo].[up_Next_Stammpositionen_Key00] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Stammpositionen_Key00')
  DROP PROCEDURE [dbo].[up_Prior_Stammpositionen_Key00]
GO
CREATE PROCEDURE [dbo].[up_Prior_Stammpositionen_Key00] @oldRecId integer AS
  DECLARE @oldLkz char(2)
  DECLARE @oldArtnr char(18)
  SELECT @oldLkz = Lkz, @oldArtnr = Artnr FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key00)
    WHERE (Lkz = @oldLkz) AND (Artnr < @oldArtnr)
    ORDER BY Lkz desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key00)
    WHERE (Lkz < @oldLkz)
    ORDER BY Lkz desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior_Stammpositionen_Key00')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Stammpositionen_Key00] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Stammpositionen_Key00] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Stammpositionen_Key00')
  DROP PROCEDURE [dbo].[up_Next50_Stammpositionen_Key00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next50_Stammpositionen_Key00')
  DROP PROCEDURE [dbo].[qp_Next50_Stammpositionen_Key00]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Stammpositionen_Key00')
  DROP PROCEDURE [dbo].[up_Prior50_Stammpositionen_Key00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior50_Stammpositionen_Key00')
  DROP PROCEDURE [dbo].[qp_Prior50_Stammpositionen_Key00]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Stammpositionen_Key00')
  DROP PROCEDURE [dbo].[up_FindF_Stammpositionen_Key00]
GO
CREATE PROCEDURE [dbo].[up_FindF_Stammpositionen_Key00] @xKey char(250), @xLen integer AS
  DECLARE @mLkz char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,2)
  DECLARE @mArtnr char(18) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,3,18)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key00) WHERE 
      Lkz = @mLkz AND 
      Artnr = @mArtnr
      ORDER BY Lkz,Artnr,RecId
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key00) WHERE 
      Lkz = @mLkz
      ORDER BY Lkz,Artnr,RecId
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key00) WHERE 
      Lkz >= @mLkz AND SUBSTRING(Lkz,1,LEN(@mLkz)) = @mLkz
      ORDER BY Lkz,Artnr,RecId
    RETURN
  END

  IF @xLen < 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key00) WHERE 
      Lkz = @mLkz AND 
      Artnr >= @mArtnr AND SUBSTRING(Artnr,1,LEN(@mArtnr)) = @mArtnr
      ORDER BY Lkz,Artnr,RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindF_Stammpositionen_Key00')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindF_Stammpositionen_Key00] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindF_Stammpositionen_Key00] @xKey, @xLen')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Stammpositionen_Key00')
  DROP PROCEDURE [dbo].[up_FindL_Stammpositionen_Key00]
GO
CREATE PROCEDURE [dbo].[up_FindL_Stammpositionen_Key00] @xKey char(250), @xLen integer AS
  DECLARE @mLkz char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,2)
  DECLARE @mArtnr char(18) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,3,18)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key00) WHERE 
      Lkz = @mLkz AND 
      Artnr = @mArtnr
      ORDER BY Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key00) WHERE 
      Lkz = @mLkz
      ORDER BY Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key00) WHERE 
      SUBSTRING(Lkz,1,LEN(@mLkz)) = @mLkz
      ORDER BY Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen < 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key00) WHERE 
      Lkz = @mLkz AND 
      SUBSTRING(Artnr,1,LEN(@mArtnr)) = @mArtnr
      ORDER BY Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindL_Stammpositionen_Key00')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindL_Stammpositionen_Key00] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindL_Stammpositionen_Key00] @xKey, @xLen')
GO


-- logical key  ORDER BY Suchen,RecId
IF 0 < (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Stammpositionen' and so.[id] = si.[id] and si.[name] = 'Ik_Key01')
  DROP INDEX [Ik_Key01] ON [dbo].[ut_Stammpositionen]
GO
IF 0 = (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Stammpositionen' and so.[id] = si.[id] and si.[name] = 'Ik_Key01')
  CREATE INDEX [Ik_Key01] ON [dbo].[ut_Stammpositionen] (Suchen,RecId)
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Stammpositionen_Key01')
  DROP PROCEDURE [dbo].[up_First_Stammpositionen_Key01]
GO
CREATE PROCEDURE [dbo].[up_First_Stammpositionen_Key01] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key01) ORDER BY Suchen,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Stammpositionen_Key01')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Stammpositionen_Key01] AS EXECUTE [dbo].[up_First_Stammpositionen_Key01]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Stammpositionen_Key01')
  DROP PROCEDURE [dbo].[up_Last_Stammpositionen_Key01]
GO
CREATE PROCEDURE [dbo].[up_Last_Stammpositionen_Key01] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key01) ORDER BY Suchen desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Stammpositionen_Key01')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Stammpositionen_Key01] AS EXECUTE [dbo].[up_Last_Stammpositionen_Key01]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Stammpositionen_Key01')
  DROP PROCEDURE [dbo].[up_Next_Stammpositionen_Key01]
GO
CREATE PROCEDURE [dbo].[up_Next_Stammpositionen_Key01] @oldRecId integer AS
  DECLARE @oldSuchen char(15)
  SELECT @oldSuchen = Suchen FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key01)
    WHERE (Suchen = @oldSuchen) AND (RecId > @oldRecId)
    ORDER BY Suchen,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key01)
    WHERE (Suchen > @oldSuchen)
    ORDER BY Suchen,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next_Stammpositionen_Key01')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Stammpositionen_Key01] @oldRecId integer AS EXECUTE [dbo].[up_Next_Stammpositionen_Key01] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Stammpositionen_Key01')
  DROP PROCEDURE [dbo].[up_Prior_Stammpositionen_Key01]
GO
CREATE PROCEDURE [dbo].[up_Prior_Stammpositionen_Key01] @oldRecId integer AS
  DECLARE @oldSuchen char(15)
  SELECT @oldSuchen = Suchen FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key01)
    WHERE (Suchen = @oldSuchen) AND (RecId < @oldRecId)
    ORDER BY Suchen desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key01)
    WHERE (Suchen < @oldSuchen)
    ORDER BY Suchen desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior_Stammpositionen_Key01')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Stammpositionen_Key01] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Stammpositionen_Key01] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Stammpositionen_Key01')
  DROP PROCEDURE [dbo].[up_Next50_Stammpositionen_Key01]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next50_Stammpositionen_Key01')
  DROP PROCEDURE [dbo].[qp_Next50_Stammpositionen_Key01]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Stammpositionen_Key01')
  DROP PROCEDURE [dbo].[up_Prior50_Stammpositionen_Key01]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior50_Stammpositionen_Key01')
  DROP PROCEDURE [dbo].[qp_Prior50_Stammpositionen_Key01]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Stammpositionen_Key01')
  DROP PROCEDURE [dbo].[up_FindF_Stammpositionen_Key01]
GO
CREATE PROCEDURE [dbo].[up_FindF_Stammpositionen_Key01] @xKey char(250), @xLen integer AS
  DECLARE @mSuchen char(15) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,15)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 15 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key01) WHERE 
      Suchen = @mSuchen
      ORDER BY Suchen,RecId
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 15 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key01) WHERE 
      Suchen >= @mSuchen AND SUBSTRING(Suchen,1,LEN(@mSuchen)) = @mSuchen
      ORDER BY Suchen,RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindF_Stammpositionen_Key01')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindF_Stammpositionen_Key01] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindF_Stammpositionen_Key01] @xKey, @xLen')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Stammpositionen_Key01')
  DROP PROCEDURE [dbo].[up_FindL_Stammpositionen_Key01]
GO
CREATE PROCEDURE [dbo].[up_FindL_Stammpositionen_Key01] @xKey char(250), @xLen integer AS
  DECLARE @mSuchen char(15) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,15)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 15 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key01) WHERE 
      Suchen = @mSuchen
      ORDER BY Suchen desc,RecId desc
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 15 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key01) WHERE 
      SUBSTRING(Suchen,1,LEN(@mSuchen)) = @mSuchen
      ORDER BY Suchen desc,RecId desc
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindL_Stammpositionen_Key01')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindL_Stammpositionen_Key01] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindL_Stammpositionen_Key01] @xKey, @xLen')
GO


-- logical key  ORDER BY Lkz,Rabgru,Artnr,RecId
IF 0 < (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Stammpositionen' and so.[id] = si.[id] and si.[name] = 'Ik_Key02')
  DROP INDEX [Ik_Key02] ON [dbo].[ut_Stammpositionen]
GO
IF 0 = (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Stammpositionen' and so.[id] = si.[id] and si.[name] = 'Ik_Key02')
  CREATE INDEX [Ik_Key02] ON [dbo].[ut_Stammpositionen] (Lkz,Rabgru,Artnr,RecId)
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Stammpositionen_Key02')
  DROP PROCEDURE [dbo].[up_First_Stammpositionen_Key02]
GO
CREATE PROCEDURE [dbo].[up_First_Stammpositionen_Key02] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02) ORDER BY Lkz,Rabgru,Artnr,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Stammpositionen_Key02')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Stammpositionen_Key02] AS EXECUTE [dbo].[up_First_Stammpositionen_Key02]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Stammpositionen_Key02')
  DROP PROCEDURE [dbo].[up_Last_Stammpositionen_Key02]
GO
CREATE PROCEDURE [dbo].[up_Last_Stammpositionen_Key02] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02) ORDER BY Lkz desc,Rabgru desc,Artnr desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Stammpositionen_Key02')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Stammpositionen_Key02] AS EXECUTE [dbo].[up_Last_Stammpositionen_Key02]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Stammpositionen_Key02')
  DROP PROCEDURE [dbo].[up_Next_Stammpositionen_Key02]
GO
CREATE PROCEDURE [dbo].[up_Next_Stammpositionen_Key02] @oldRecId integer AS
  DECLARE @oldLkz char(2)
  DECLARE @oldRabgru char(6)
  DECLARE @oldArtnr char(18)
  SELECT @oldLkz = Lkz, @oldRabgru = Rabgru, @oldArtnr = Artnr FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02)
    WHERE (Lkz = @oldLkz) AND (Rabgru = @oldRabgru) AND (Artnr = @oldArtnr) AND (RecId > @oldRecId)
    ORDER BY Lkz,Rabgru,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02)
    WHERE (Lkz = @oldLkz) AND (Rabgru = @oldRabgru) AND (Artnr > @oldArtnr)
    ORDER BY Lkz,Rabgru,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02)
    WHERE (Lkz = @oldLkz) AND (Rabgru > @oldRabgru)
    ORDER BY Lkz,Rabgru,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02)
    WHERE (Lkz > @oldLkz)
    ORDER BY Lkz,Rabgru,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next_Stammpositionen_Key02')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Stammpositionen_Key02] @oldRecId integer AS EXECUTE [dbo].[up_Next_Stammpositionen_Key02] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Stammpositionen_Key02')
  DROP PROCEDURE [dbo].[up_Prior_Stammpositionen_Key02]
GO
CREATE PROCEDURE [dbo].[up_Prior_Stammpositionen_Key02] @oldRecId integer AS
  DECLARE @oldLkz char(2)
  DECLARE @oldRabgru char(6)
  DECLARE @oldArtnr char(18)
  SELECT @oldLkz = Lkz, @oldRabgru = Rabgru, @oldArtnr = Artnr FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02)
    WHERE (Lkz = @oldLkz) AND (Rabgru = @oldRabgru) AND (Artnr = @oldArtnr) AND (RecId < @oldRecId)
    ORDER BY Lkz desc,Rabgru desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02)
    WHERE (Lkz = @oldLkz) AND (Rabgru = @oldRabgru) AND (Artnr < @oldArtnr)
    ORDER BY Lkz desc,Rabgru desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02)
    WHERE (Lkz = @oldLkz) AND (Rabgru < @oldRabgru)
    ORDER BY Lkz desc,Rabgru desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02)
    WHERE (Lkz < @oldLkz)
    ORDER BY Lkz desc,Rabgru desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior_Stammpositionen_Key02')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Stammpositionen_Key02] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Stammpositionen_Key02] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Stammpositionen_Key02')
  DROP PROCEDURE [dbo].[up_Next50_Stammpositionen_Key02]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next50_Stammpositionen_Key02')
  DROP PROCEDURE [dbo].[qp_Next50_Stammpositionen_Key02]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Stammpositionen_Key02')
  DROP PROCEDURE [dbo].[up_Prior50_Stammpositionen_Key02]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior50_Stammpositionen_Key02')
  DROP PROCEDURE [dbo].[qp_Prior50_Stammpositionen_Key02]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Stammpositionen_Key02')
  DROP PROCEDURE [dbo].[up_FindF_Stammpositionen_Key02]
GO
CREATE PROCEDURE [dbo].[up_FindF_Stammpositionen_Key02] @xKey char(250), @xLen integer AS
  DECLARE @mLkz char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,2)
  DECLARE @mRabgru char(6) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,3,6)
  DECLARE @mArtnr char(18) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,9,18)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 26 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02) WHERE 
      Lkz = @mLkz AND 
      Rabgru = @mRabgru AND 
      Artnr = @mArtnr
      ORDER BY Lkz,Rabgru,Artnr,RecId
    RETURN
  END

  IF @xLen = 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02) WHERE 
      Lkz = @mLkz AND 
      Rabgru = @mRabgru
      ORDER BY Lkz,Rabgru,Artnr,RecId
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02) WHERE 
      Lkz = @mLkz
      ORDER BY Lkz,Rabgru,Artnr,RecId
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02) WHERE 
      Lkz >= @mLkz AND SUBSTRING(Lkz,1,LEN(@mLkz)) = @mLkz
      ORDER BY Lkz,Rabgru,Artnr,RecId
    RETURN
  END

  IF @xLen < 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02) WHERE 
      Lkz = @mLkz AND 
      Rabgru >= @mRabgru AND SUBSTRING(Rabgru,1,LEN(@mRabgru)) = @mRabgru
      ORDER BY Lkz,Rabgru,Artnr,RecId
    RETURN
  END

  IF @xLen < 26 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02) WHERE 
      Lkz = @mLkz AND 
      Rabgru = @mRabgru AND 
      Artnr >= @mArtnr AND SUBSTRING(Artnr,1,LEN(@mArtnr)) = @mArtnr
      ORDER BY Lkz,Rabgru,Artnr,RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindF_Stammpositionen_Key02')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindF_Stammpositionen_Key02] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindF_Stammpositionen_Key02] @xKey, @xLen')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Stammpositionen_Key02')
  DROP PROCEDURE [dbo].[up_FindL_Stammpositionen_Key02]
GO
CREATE PROCEDURE [dbo].[up_FindL_Stammpositionen_Key02] @xKey char(250), @xLen integer AS
  DECLARE @mLkz char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,2)
  DECLARE @mRabgru char(6) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,3,6)
  DECLARE @mArtnr char(18) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,9,18)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 26 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02) WHERE 
      Lkz = @mLkz AND 
      Rabgru = @mRabgru AND 
      Artnr = @mArtnr
      ORDER BY Lkz desc,Rabgru desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen = 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02) WHERE 
      Lkz = @mLkz AND 
      Rabgru = @mRabgru
      ORDER BY Lkz desc,Rabgru desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02) WHERE 
      Lkz = @mLkz
      ORDER BY Lkz desc,Rabgru desc,Artnr desc,RecId desc
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02) WHERE 
      SUBSTRING(Lkz,1,LEN(@mLkz)) = @mLkz
      ORDER BY Lkz desc,Rabgru desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen < 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02) WHERE 
      Lkz = @mLkz AND 
      SUBSTRING(Rabgru,1,LEN(@mRabgru)) = @mRabgru
      ORDER BY Lkz desc,Rabgru desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen < 26 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key02) WHERE 
      Lkz = @mLkz AND 
      Rabgru = @mRabgru AND 
      SUBSTRING(Artnr,1,LEN(@mArtnr)) = @mArtnr
      ORDER BY Lkz desc,Rabgru desc,Artnr desc,RecId desc
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindL_Stammpositionen_Key02')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindL_Stammpositionen_Key02] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindL_Stammpositionen_Key02] @xKey, @xLen')
GO


-- logical key  ORDER BY Lkz,Wargru,Artnr,RecId
IF 0 < (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Stammpositionen' and so.[id] = si.[id] and si.[name] = 'Ik_Key03')
  DROP INDEX [Ik_Key03] ON [dbo].[ut_Stammpositionen]
GO
IF 0 = (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Stammpositionen' and so.[id] = si.[id] and si.[name] = 'Ik_Key03')
  CREATE INDEX [Ik_Key03] ON [dbo].[ut_Stammpositionen] (Lkz,Wargru,Artnr,RecId)
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Stammpositionen_Key03')
  DROP PROCEDURE [dbo].[up_First_Stammpositionen_Key03]
GO
CREATE PROCEDURE [dbo].[up_First_Stammpositionen_Key03] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03) ORDER BY Lkz,Wargru,Artnr,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Stammpositionen_Key03')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Stammpositionen_Key03] AS EXECUTE [dbo].[up_First_Stammpositionen_Key03]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Stammpositionen_Key03')
  DROP PROCEDURE [dbo].[up_Last_Stammpositionen_Key03]
GO
CREATE PROCEDURE [dbo].[up_Last_Stammpositionen_Key03] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03) ORDER BY Lkz desc,Wargru desc,Artnr desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Stammpositionen_Key03')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Stammpositionen_Key03] AS EXECUTE [dbo].[up_Last_Stammpositionen_Key03]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Stammpositionen_Key03')
  DROP PROCEDURE [dbo].[up_Next_Stammpositionen_Key03]
GO
CREATE PROCEDURE [dbo].[up_Next_Stammpositionen_Key03] @oldRecId integer AS
  DECLARE @oldLkz char(2)
  DECLARE @oldWargru char(6)
  DECLARE @oldArtnr char(18)
  SELECT @oldLkz = Lkz, @oldWargru = Wargru, @oldArtnr = Artnr FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03)
    WHERE (Lkz = @oldLkz) AND (Wargru = @oldWargru) AND (Artnr = @oldArtnr) AND (RecId > @oldRecId)
    ORDER BY Lkz,Wargru,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03)
    WHERE (Lkz = @oldLkz) AND (Wargru = @oldWargru) AND (Artnr > @oldArtnr)
    ORDER BY Lkz,Wargru,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03)
    WHERE (Lkz = @oldLkz) AND (Wargru > @oldWargru)
    ORDER BY Lkz,Wargru,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03)
    WHERE (Lkz > @oldLkz)
    ORDER BY Lkz,Wargru,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next_Stammpositionen_Key03')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Stammpositionen_Key03] @oldRecId integer AS EXECUTE [dbo].[up_Next_Stammpositionen_Key03] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Stammpositionen_Key03')
  DROP PROCEDURE [dbo].[up_Prior_Stammpositionen_Key03]
GO
CREATE PROCEDURE [dbo].[up_Prior_Stammpositionen_Key03] @oldRecId integer AS
  DECLARE @oldLkz char(2)
  DECLARE @oldWargru char(6)
  DECLARE @oldArtnr char(18)
  SELECT @oldLkz = Lkz, @oldWargru = Wargru, @oldArtnr = Artnr FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03)
    WHERE (Lkz = @oldLkz) AND (Wargru = @oldWargru) AND (Artnr = @oldArtnr) AND (RecId < @oldRecId)
    ORDER BY Lkz desc,Wargru desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03)
    WHERE (Lkz = @oldLkz) AND (Wargru = @oldWargru) AND (Artnr < @oldArtnr)
    ORDER BY Lkz desc,Wargru desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03)
    WHERE (Lkz = @oldLkz) AND (Wargru < @oldWargru)
    ORDER BY Lkz desc,Wargru desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03)
    WHERE (Lkz < @oldLkz)
    ORDER BY Lkz desc,Wargru desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior_Stammpositionen_Key03')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Stammpositionen_Key03] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Stammpositionen_Key03] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Stammpositionen_Key03')
  DROP PROCEDURE [dbo].[up_Next50_Stammpositionen_Key03]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next50_Stammpositionen_Key03')
  DROP PROCEDURE [dbo].[qp_Next50_Stammpositionen_Key03]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Stammpositionen_Key03')
  DROP PROCEDURE [dbo].[up_Prior50_Stammpositionen_Key03]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior50_Stammpositionen_Key03')
  DROP PROCEDURE [dbo].[qp_Prior50_Stammpositionen_Key03]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Stammpositionen_Key03')
  DROP PROCEDURE [dbo].[up_FindF_Stammpositionen_Key03]
GO
CREATE PROCEDURE [dbo].[up_FindF_Stammpositionen_Key03] @xKey char(250), @xLen integer AS
  DECLARE @mLkz char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,2)
  DECLARE @mWargru char(6) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,3,6)
  DECLARE @mArtnr char(18) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,9,18)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 26 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03) WHERE 
      Lkz = @mLkz AND 
      Wargru = @mWargru AND 
      Artnr = @mArtnr
      ORDER BY Lkz,Wargru,Artnr,RecId
    RETURN
  END

  IF @xLen = 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03) WHERE 
      Lkz = @mLkz AND 
      Wargru = @mWargru
      ORDER BY Lkz,Wargru,Artnr,RecId
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03) WHERE 
      Lkz = @mLkz
      ORDER BY Lkz,Wargru,Artnr,RecId
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03) WHERE 
      Lkz >= @mLkz AND SUBSTRING(Lkz,1,LEN(@mLkz)) = @mLkz
      ORDER BY Lkz,Wargru,Artnr,RecId
    RETURN
  END

  IF @xLen < 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03) WHERE 
      Lkz = @mLkz AND 
      Wargru >= @mWargru AND SUBSTRING(Wargru,1,LEN(@mWargru)) = @mWargru
      ORDER BY Lkz,Wargru,Artnr,RecId
    RETURN
  END

  IF @xLen < 26 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03) WHERE 
      Lkz = @mLkz AND 
      Wargru = @mWargru AND 
      Artnr >= @mArtnr AND SUBSTRING(Artnr,1,LEN(@mArtnr)) = @mArtnr
      ORDER BY Lkz,Wargru,Artnr,RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindF_Stammpositionen_Key03')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindF_Stammpositionen_Key03] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindF_Stammpositionen_Key03] @xKey, @xLen')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Stammpositionen_Key03')
  DROP PROCEDURE [dbo].[up_FindL_Stammpositionen_Key03]
GO
CREATE PROCEDURE [dbo].[up_FindL_Stammpositionen_Key03] @xKey char(250), @xLen integer AS
  DECLARE @mLkz char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,2)
  DECLARE @mWargru char(6) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,3,6)
  DECLARE @mArtnr char(18) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,9,18)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 26 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03) WHERE 
      Lkz = @mLkz AND 
      Wargru = @mWargru AND 
      Artnr = @mArtnr
      ORDER BY Lkz desc,Wargru desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen = 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03) WHERE 
      Lkz = @mLkz AND 
      Wargru = @mWargru
      ORDER BY Lkz desc,Wargru desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03) WHERE 
      Lkz = @mLkz
      ORDER BY Lkz desc,Wargru desc,Artnr desc,RecId desc
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03) WHERE 
      SUBSTRING(Lkz,1,LEN(@mLkz)) = @mLkz
      ORDER BY Lkz desc,Wargru desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen < 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03) WHERE 
      Lkz = @mLkz AND 
      SUBSTRING(Wargru,1,LEN(@mWargru)) = @mWargru
      ORDER BY Lkz desc,Wargru desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen < 26 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key03) WHERE 
      Lkz = @mLkz AND 
      Wargru = @mWargru AND 
      SUBSTRING(Artnr,1,LEN(@mArtnr)) = @mArtnr
      ORDER BY Lkz desc,Wargru desc,Artnr desc,RecId desc
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindL_Stammpositionen_Key03')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindL_Stammpositionen_Key03] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindL_Stammpositionen_Key03] @xKey, @xLen')
GO


-- logical key  ORDER BY EANcode,Lkz,RecId
IF 0 < (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Stammpositionen' and so.[id] = si.[id] and si.[name] = 'Ik_Key04')
  DROP INDEX [Ik_Key04] ON [dbo].[ut_Stammpositionen]
GO
IF 0 = (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Stammpositionen' and so.[id] = si.[id] and si.[name] = 'Ik_Key04')
  CREATE INDEX [Ik_Key04] ON [dbo].[ut_Stammpositionen] (EANcode,Lkz,RecId)
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Stammpositionen_Key04')
  DROP PROCEDURE [dbo].[up_First_Stammpositionen_Key04]
GO
CREATE PROCEDURE [dbo].[up_First_Stammpositionen_Key04] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key04) ORDER BY EANcode,Lkz,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Stammpositionen_Key04')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Stammpositionen_Key04] AS EXECUTE [dbo].[up_First_Stammpositionen_Key04]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Stammpositionen_Key04')
  DROP PROCEDURE [dbo].[up_Last_Stammpositionen_Key04]
GO
CREATE PROCEDURE [dbo].[up_Last_Stammpositionen_Key04] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key04) ORDER BY EANcode desc,Lkz desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Stammpositionen_Key04')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Stammpositionen_Key04] AS EXECUTE [dbo].[up_Last_Stammpositionen_Key04]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Stammpositionen_Key04')
  DROP PROCEDURE [dbo].[up_Next_Stammpositionen_Key04]
GO
CREATE PROCEDURE [dbo].[up_Next_Stammpositionen_Key04] @oldRecId integer AS
  DECLARE @oldEANcode char(14)
  DECLARE @oldLkz char(2)
  SELECT @oldEANcode = EANcode, @oldLkz = Lkz FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key04)
    WHERE (EANcode = @oldEANcode) AND (Lkz = @oldLkz) AND (RecId > @oldRecId)
    ORDER BY EANcode,Lkz,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key04)
    WHERE (EANcode = @oldEANcode) AND (Lkz > @oldLkz)
    ORDER BY EANcode,Lkz,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key04)
    WHERE (EANcode > @oldEANcode)
    ORDER BY EANcode,Lkz,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next_Stammpositionen_Key04')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Stammpositionen_Key04] @oldRecId integer AS EXECUTE [dbo].[up_Next_Stammpositionen_Key04] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Stammpositionen_Key04')
  DROP PROCEDURE [dbo].[up_Prior_Stammpositionen_Key04]
GO
CREATE PROCEDURE [dbo].[up_Prior_Stammpositionen_Key04] @oldRecId integer AS
  DECLARE @oldEANcode char(14)
  DECLARE @oldLkz char(2)
  SELECT @oldEANcode = EANcode, @oldLkz = Lkz FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key04)
    WHERE (EANcode = @oldEANcode) AND (Lkz = @oldLkz) AND (RecId < @oldRecId)
    ORDER BY EANcode desc,Lkz desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key04)
    WHERE (EANcode = @oldEANcode) AND (Lkz < @oldLkz)
    ORDER BY EANcode desc,Lkz desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key04)
    WHERE (EANcode < @oldEANcode)
    ORDER BY EANcode desc,Lkz desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior_Stammpositionen_Key04')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Stammpositionen_Key04] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Stammpositionen_Key04] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Stammpositionen_Key04')
  DROP PROCEDURE [dbo].[up_Next50_Stammpositionen_Key04]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next50_Stammpositionen_Key04')
  DROP PROCEDURE [dbo].[qp_Next50_Stammpositionen_Key04]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Stammpositionen_Key04')
  DROP PROCEDURE [dbo].[up_Prior50_Stammpositionen_Key04]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior50_Stammpositionen_Key04')
  DROP PROCEDURE [dbo].[qp_Prior50_Stammpositionen_Key04]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Stammpositionen_Key04')
  DROP PROCEDURE [dbo].[up_FindF_Stammpositionen_Key04]
GO
CREATE PROCEDURE [dbo].[up_FindF_Stammpositionen_Key04] @xKey char(250), @xLen integer AS
  DECLARE @mEANcode char(14) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,14)
  DECLARE @mLkz char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,15,2)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 16 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key04) WHERE 
      EANcode = @mEANcode AND 
      Lkz = @mLkz
      ORDER BY EANcode,Lkz,RecId
    RETURN
  END

  IF @xLen = 14 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key04) WHERE 
      EANcode = @mEANcode
      ORDER BY EANcode,Lkz,RecId
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 14 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key04) WHERE 
      EANcode >= @mEANcode AND SUBSTRING(EANcode,1,LEN(@mEANcode)) = @mEANcode
      ORDER BY EANcode,Lkz,RecId
    RETURN
  END

  IF @xLen < 16 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key04) WHERE 
      EANcode = @mEANcode AND 
      Lkz >= @mLkz AND SUBSTRING(Lkz,1,LEN(@mLkz)) = @mLkz
      ORDER BY EANcode,Lkz,RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindF_Stammpositionen_Key04')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindF_Stammpositionen_Key04] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindF_Stammpositionen_Key04] @xKey, @xLen')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Stammpositionen_Key04')
  DROP PROCEDURE [dbo].[up_FindL_Stammpositionen_Key04]
GO
CREATE PROCEDURE [dbo].[up_FindL_Stammpositionen_Key04] @xKey char(250), @xLen integer AS
  DECLARE @mEANcode char(14) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,14)
  DECLARE @mLkz char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,15,2)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 16 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key04) WHERE 
      EANcode = @mEANcode AND 
      Lkz = @mLkz
      ORDER BY EANcode desc,Lkz desc,RecId desc
    RETURN
  END

  IF @xLen = 14 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key04) WHERE 
      EANcode = @mEANcode
      ORDER BY EANcode desc,Lkz desc,RecId desc
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 14 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key04) WHERE 
      SUBSTRING(EANcode,1,LEN(@mEANcode)) = @mEANcode
      ORDER BY EANcode desc,Lkz desc,RecId desc
    RETURN
  END

  IF @xLen < 16 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key04) WHERE 
      EANcode = @mEANcode AND 
      SUBSTRING(Lkz,1,LEN(@mLkz)) = @mLkz
      ORDER BY EANcode desc,Lkz desc,RecId desc
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindL_Stammpositionen_Key04')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindL_Stammpositionen_Key04] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindL_Stammpositionen_Key04] @xKey, @xLen')
GO


-- logical key  ORDER BY Gruppe,Suchen,RecId
IF 0 < (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Stammpositionen' and so.[id] = si.[id] and si.[name] = 'Ik_Key05')
  DROP INDEX [Ik_Key05] ON [dbo].[ut_Stammpositionen]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Stammpositionen_Key05')
  DROP PROCEDURE [dbo].[up_First_Stammpositionen_Key05]
GO
CREATE PROCEDURE [dbo].[up_First_Stammpositionen_Key05] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen]  ORDER BY Gruppe,Suchen,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Stammpositionen_Key05')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Stammpositionen_Key05] AS EXECUTE [dbo].[up_First_Stammpositionen_Key05]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Stammpositionen_Key05')
  DROP PROCEDURE [dbo].[up_Last_Stammpositionen_Key05]
GO
CREATE PROCEDURE [dbo].[up_Last_Stammpositionen_Key05] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen]  ORDER BY Gruppe desc,Suchen desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Stammpositionen_Key05')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Stammpositionen_Key05] AS EXECUTE [dbo].[up_Last_Stammpositionen_Key05]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Stammpositionen_Key05')
  DROP PROCEDURE [dbo].[up_Next_Stammpositionen_Key05]
GO
CREATE PROCEDURE [dbo].[up_Next_Stammpositionen_Key05] @oldRecId integer AS
  DECLARE @oldGruppe char(6)
  DECLARE @oldSuchen char(15)
  SELECT @oldGruppe = Gruppe, @oldSuchen = Suchen FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] 
    WHERE (Gruppe = @oldGruppe) AND (Suchen = @oldSuchen) AND (RecId > @oldRecId)
    ORDER BY Gruppe,Suchen,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] 
    WHERE (Gruppe = @oldGruppe) AND (Suchen > @oldSuchen)
    ORDER BY Gruppe,Suchen,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] 
    WHERE (Gruppe > @oldGruppe)
    ORDER BY Gruppe,Suchen,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next_Stammpositionen_Key05')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Stammpositionen_Key05] @oldRecId integer AS EXECUTE [dbo].[up_Next_Stammpositionen_Key05] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Stammpositionen_Key05')
  DROP PROCEDURE [dbo].[up_Prior_Stammpositionen_Key05]
GO
CREATE PROCEDURE [dbo].[up_Prior_Stammpositionen_Key05] @oldRecId integer AS
  DECLARE @oldGruppe char(6)
  DECLARE @oldSuchen char(15)
  SELECT @oldGruppe = Gruppe, @oldSuchen = Suchen FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] 
    WHERE (Gruppe = @oldGruppe) AND (Suchen = @oldSuchen) AND (RecId < @oldRecId)
    ORDER BY Gruppe desc,Suchen desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] 
    WHERE (Gruppe = @oldGruppe) AND (Suchen < @oldSuchen)
    ORDER BY Gruppe desc,Suchen desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] 
    WHERE (Gruppe < @oldGruppe)
    ORDER BY Gruppe desc,Suchen desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior_Stammpositionen_Key05')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Stammpositionen_Key05] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Stammpositionen_Key05] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Stammpositionen_Key05')
  DROP PROCEDURE [dbo].[up_Next50_Stammpositionen_Key05]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next50_Stammpositionen_Key05')
  DROP PROCEDURE [dbo].[qp_Next50_Stammpositionen_Key05]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Stammpositionen_Key05')
  DROP PROCEDURE [dbo].[up_Prior50_Stammpositionen_Key05]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior50_Stammpositionen_Key05')
  DROP PROCEDURE [dbo].[qp_Prior50_Stammpositionen_Key05]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Stammpositionen_Key05')
  DROP PROCEDURE [dbo].[up_FindF_Stammpositionen_Key05]
GO
CREATE PROCEDURE [dbo].[up_FindF_Stammpositionen_Key05] @xKey char(250), @xLen integer AS
  DECLARE @mGruppe char(6) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,6)
  DECLARE @mSuchen char(15) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,7,15)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 21 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen]  WHERE 
      Gruppe = @mGruppe AND 
      Suchen = @mSuchen
      ORDER BY Gruppe,Suchen,RecId
    RETURN
  END

  IF @xLen = 6 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen]  WHERE 
      Gruppe = @mGruppe
      ORDER BY Gruppe,Suchen,RecId
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 6 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen]  WHERE 
      Gruppe >= @mGruppe AND SUBSTRING(Gruppe,1,LEN(@mGruppe)) = @mGruppe
      ORDER BY Gruppe,Suchen,RecId
    RETURN
  END

  IF @xLen < 21 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen]  WHERE 
      Gruppe = @mGruppe AND 
      Suchen >= @mSuchen AND SUBSTRING(Suchen,1,LEN(@mSuchen)) = @mSuchen
      ORDER BY Gruppe,Suchen,RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindF_Stammpositionen_Key05')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindF_Stammpositionen_Key05] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindF_Stammpositionen_Key05] @xKey, @xLen')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Stammpositionen_Key05')
  DROP PROCEDURE [dbo].[up_FindL_Stammpositionen_Key05]
GO
CREATE PROCEDURE [dbo].[up_FindL_Stammpositionen_Key05] @xKey char(250), @xLen integer AS
  DECLARE @mGruppe char(6) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,6)
  DECLARE @mSuchen char(15) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,7,15)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 21 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen]  WHERE 
      Gruppe = @mGruppe AND 
      Suchen = @mSuchen
      ORDER BY Gruppe desc,Suchen desc,RecId desc
    RETURN
  END

  IF @xLen = 6 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen]  WHERE 
      Gruppe = @mGruppe
      ORDER BY Gruppe desc,Suchen desc,RecId desc
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 6 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen]  WHERE 
      SUBSTRING(Gruppe,1,LEN(@mGruppe)) = @mGruppe
      ORDER BY Gruppe desc,Suchen desc,RecId desc
    RETURN
  END

  IF @xLen < 21 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen]  WHERE 
      Gruppe = @mGruppe AND 
      SUBSTRING(Suchen,1,LEN(@mSuchen)) = @mSuchen
      ORDER BY Gruppe desc,Suchen desc,RecId desc
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindL_Stammpositionen_Key05')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindL_Stammpositionen_Key05] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindL_Stammpositionen_Key05] @xKey, @xLen')
GO


-- logical key  ORDER BY Kzlager,Lagerort,Lkz,Artnr,RecId
IF 0 < (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Stammpositionen' and so.[id] = si.[id] and si.[name] = 'Ik_Key06')
  DROP INDEX [Ik_Key06] ON [dbo].[ut_Stammpositionen]
GO
IF 0 = (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Stammpositionen' and so.[id] = si.[id] and si.[name] = 'Ik_Key06')
  CREATE INDEX [Ik_Key06] ON [dbo].[ut_Stammpositionen] (Kzlager,Lagerort,Lkz,Artnr,RecId)
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Stammpositionen_Key06')
  DROP PROCEDURE [dbo].[up_First_Stammpositionen_Key06]
GO
CREATE PROCEDURE [dbo].[up_First_Stammpositionen_Key06] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06) ORDER BY Kzlager,Lagerort,Lkz,Artnr,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Stammpositionen_Key06')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Stammpositionen_Key06] AS EXECUTE [dbo].[up_First_Stammpositionen_Key06]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Stammpositionen_Key06')
  DROP PROCEDURE [dbo].[up_Last_Stammpositionen_Key06]
GO
CREATE PROCEDURE [dbo].[up_Last_Stammpositionen_Key06] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06) ORDER BY Kzlager desc,Lagerort desc,Lkz desc,Artnr desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Stammpositionen_Key06')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Stammpositionen_Key06] AS EXECUTE [dbo].[up_Last_Stammpositionen_Key06]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Stammpositionen_Key06')
  DROP PROCEDURE [dbo].[up_Next_Stammpositionen_Key06]
GO
CREATE PROCEDURE [dbo].[up_Next_Stammpositionen_Key06] @oldRecId integer AS
  DECLARE @oldKzlager char(1)
  DECLARE @oldLagerort char(15)
  DECLARE @oldLkz char(2)
  DECLARE @oldArtnr char(18)
  SELECT @oldKzlager = Kzlager, @oldLagerort = Lagerort, @oldLkz = Lkz, @oldArtnr = Artnr FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06)
    WHERE (Kzlager = @oldKzlager) AND (Lagerort = @oldLagerort) AND (Lkz = @oldLkz) AND (Artnr = @oldArtnr) AND (RecId > @oldRecId)
    ORDER BY Kzlager,Lagerort,Lkz,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06)
    WHERE (Kzlager = @oldKzlager) AND (Lagerort = @oldLagerort) AND (Lkz = @oldLkz) AND (Artnr > @oldArtnr)
    ORDER BY Kzlager,Lagerort,Lkz,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06)
    WHERE (Kzlager = @oldKzlager) AND (Lagerort = @oldLagerort) AND (Lkz > @oldLkz)
    ORDER BY Kzlager,Lagerort,Lkz,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06)
    WHERE (Kzlager = @oldKzlager) AND (Lagerort > @oldLagerort)
    ORDER BY Kzlager,Lagerort,Lkz,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06)
    WHERE (Kzlager > @oldKzlager)
    ORDER BY Kzlager,Lagerort,Lkz,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next_Stammpositionen_Key06')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Stammpositionen_Key06] @oldRecId integer AS EXECUTE [dbo].[up_Next_Stammpositionen_Key06] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Stammpositionen_Key06')
  DROP PROCEDURE [dbo].[up_Prior_Stammpositionen_Key06]
GO
CREATE PROCEDURE [dbo].[up_Prior_Stammpositionen_Key06] @oldRecId integer AS
  DECLARE @oldKzlager char(1)
  DECLARE @oldLagerort char(15)
  DECLARE @oldLkz char(2)
  DECLARE @oldArtnr char(18)
  SELECT @oldKzlager = Kzlager, @oldLagerort = Lagerort, @oldLkz = Lkz, @oldArtnr = Artnr FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06)
    WHERE (Kzlager = @oldKzlager) AND (Lagerort = @oldLagerort) AND (Lkz = @oldLkz) AND (Artnr = @oldArtnr) AND (RecId < @oldRecId)
    ORDER BY Kzlager desc,Lagerort desc,Lkz desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06)
    WHERE (Kzlager = @oldKzlager) AND (Lagerort = @oldLagerort) AND (Lkz = @oldLkz) AND (Artnr < @oldArtnr)
    ORDER BY Kzlager desc,Lagerort desc,Lkz desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06)
    WHERE (Kzlager = @oldKzlager) AND (Lagerort = @oldLagerort) AND (Lkz < @oldLkz)
    ORDER BY Kzlager desc,Lagerort desc,Lkz desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06)
    WHERE (Kzlager = @oldKzlager) AND (Lagerort < @oldLagerort)
    ORDER BY Kzlager desc,Lagerort desc,Lkz desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06)
    WHERE (Kzlager < @oldKzlager)
    ORDER BY Kzlager desc,Lagerort desc,Lkz desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior_Stammpositionen_Key06')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Stammpositionen_Key06] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Stammpositionen_Key06] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Stammpositionen_Key06')
  DROP PROCEDURE [dbo].[up_Next50_Stammpositionen_Key06]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next50_Stammpositionen_Key06')
  DROP PROCEDURE [dbo].[qp_Next50_Stammpositionen_Key06]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Stammpositionen_Key06')
  DROP PROCEDURE [dbo].[up_Prior50_Stammpositionen_Key06]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior50_Stammpositionen_Key06')
  DROP PROCEDURE [dbo].[qp_Prior50_Stammpositionen_Key06]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Stammpositionen_Key06')
  DROP PROCEDURE [dbo].[up_FindF_Stammpositionen_Key06]
GO
CREATE PROCEDURE [dbo].[up_FindF_Stammpositionen_Key06] @xKey char(250), @xLen integer AS
  DECLARE @mKzlager char(1) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,1)
  DECLARE @mLagerort char(15) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,2,15)
  DECLARE @mLkz char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,17,2)
  DECLARE @mArtnr char(18) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,19,18)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 36 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06) WHERE 
      Kzlager = @mKzlager AND 
      Lagerort = @mLagerort AND 
      Lkz = @mLkz AND 
      Artnr = @mArtnr
      ORDER BY Kzlager,Lagerort,Lkz,Artnr,RecId
    RETURN
  END

  IF @xLen = 18 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06) WHERE 
      Kzlager = @mKzlager AND 
      Lagerort = @mLagerort AND 
      Lkz = @mLkz
      ORDER BY Kzlager,Lagerort,Lkz,Artnr,RecId
    RETURN
  END

  IF @xLen = 16 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06) WHERE 
      Kzlager = @mKzlager AND 
      Lagerort = @mLagerort
      ORDER BY Kzlager,Lagerort,Lkz,Artnr,RecId
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06) WHERE 
      Kzlager = @mKzlager
      ORDER BY Kzlager,Lagerort,Lkz,Artnr,RecId
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06) WHERE 
      Kzlager >= @mKzlager AND SUBSTRING(Kzlager,1,LEN(@mKzlager)) = @mKzlager
      ORDER BY Kzlager,Lagerort,Lkz,Artnr,RecId
    RETURN
  END

  IF @xLen < 16 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06) WHERE 
      Kzlager = @mKzlager AND 
      Lagerort >= @mLagerort AND SUBSTRING(Lagerort,1,LEN(@mLagerort)) = @mLagerort
      ORDER BY Kzlager,Lagerort,Lkz,Artnr,RecId
    RETURN
  END

  IF @xLen < 18 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06) WHERE 
      Kzlager = @mKzlager AND 
      Lagerort = @mLagerort AND 
      Lkz >= @mLkz AND SUBSTRING(Lkz,1,LEN(@mLkz)) = @mLkz
      ORDER BY Kzlager,Lagerort,Lkz,Artnr,RecId
    RETURN
  END

  IF @xLen < 36 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06) WHERE 
      Kzlager = @mKzlager AND 
      Lagerort = @mLagerort AND 
      Lkz = @mLkz AND 
      Artnr >= @mArtnr AND SUBSTRING(Artnr,1,LEN(@mArtnr)) = @mArtnr
      ORDER BY Kzlager,Lagerort,Lkz,Artnr,RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindF_Stammpositionen_Key06')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindF_Stammpositionen_Key06] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindF_Stammpositionen_Key06] @xKey, @xLen')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Stammpositionen_Key06')
  DROP PROCEDURE [dbo].[up_FindL_Stammpositionen_Key06]
GO
CREATE PROCEDURE [dbo].[up_FindL_Stammpositionen_Key06] @xKey char(250), @xLen integer AS
  DECLARE @mKzlager char(1) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,1)
  DECLARE @mLagerort char(15) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,2,15)
  DECLARE @mLkz char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,17,2)
  DECLARE @mArtnr char(18) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,19,18)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 36 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06) WHERE 
      Kzlager = @mKzlager AND 
      Lagerort = @mLagerort AND 
      Lkz = @mLkz AND 
      Artnr = @mArtnr
      ORDER BY Kzlager desc,Lagerort desc,Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen = 18 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06) WHERE 
      Kzlager = @mKzlager AND 
      Lagerort = @mLagerort AND 
      Lkz = @mLkz
      ORDER BY Kzlager desc,Lagerort desc,Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen = 16 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06) WHERE 
      Kzlager = @mKzlager AND 
      Lagerort = @mLagerort
      ORDER BY Kzlager desc,Lagerort desc,Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06) WHERE 
      Kzlager = @mKzlager
      ORDER BY Kzlager desc,Lagerort desc,Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06) WHERE 
      SUBSTRING(Kzlager,1,LEN(@mKzlager)) = @mKzlager
      ORDER BY Kzlager desc,Lagerort desc,Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen < 16 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06) WHERE 
      Kzlager = @mKzlager AND 
      SUBSTRING(Lagerort,1,LEN(@mLagerort)) = @mLagerort
      ORDER BY Kzlager desc,Lagerort desc,Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen < 18 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06) WHERE 
      Kzlager = @mKzlager AND 
      Lagerort = @mLagerort AND 
      SUBSTRING(Lkz,1,LEN(@mLkz)) = @mLkz
      ORDER BY Kzlager desc,Lagerort desc,Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen < 36 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key06) WHERE 
      Kzlager = @mKzlager AND 
      Lagerort = @mLagerort AND 
      Lkz = @mLkz AND 
      SUBSTRING(Artnr,1,LEN(@mArtnr)) = @mArtnr
      ORDER BY Kzlager desc,Lagerort desc,Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindL_Stammpositionen_Key06')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindL_Stammpositionen_Key06] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindL_Stammpositionen_Key06] @xKey, @xLen')
GO


-- logical key  ORDER BY Kzlager,Lkz,Artnr,RecId
IF 0 < (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Stammpositionen' and so.[id] = si.[id] and si.[name] = 'Ik_Key07')
  DROP INDEX [Ik_Key07] ON [dbo].[ut_Stammpositionen]
GO
IF 0 = (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Stammpositionen' and so.[id] = si.[id] and si.[name] = 'Ik_Key07')
  CREATE INDEX [Ik_Key07] ON [dbo].[ut_Stammpositionen] (Kzlager,Lkz,Artnr,RecId)
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Stammpositionen_Key07')
  DROP PROCEDURE [dbo].[up_First_Stammpositionen_Key07]
GO
CREATE PROCEDURE [dbo].[up_First_Stammpositionen_Key07] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07) ORDER BY Kzlager,Lkz,Artnr,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Stammpositionen_Key07')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Stammpositionen_Key07] AS EXECUTE [dbo].[up_First_Stammpositionen_Key07]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Stammpositionen_Key07')
  DROP PROCEDURE [dbo].[up_Last_Stammpositionen_Key07]
GO
CREATE PROCEDURE [dbo].[up_Last_Stammpositionen_Key07] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07) ORDER BY Kzlager desc,Lkz desc,Artnr desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Stammpositionen_Key07')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Stammpositionen_Key07] AS EXECUTE [dbo].[up_Last_Stammpositionen_Key07]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Stammpositionen_Key07')
  DROP PROCEDURE [dbo].[up_Next_Stammpositionen_Key07]
GO
CREATE PROCEDURE [dbo].[up_Next_Stammpositionen_Key07] @oldRecId integer AS
  DECLARE @oldKzlager char(1)
  DECLARE @oldLkz char(2)
  DECLARE @oldArtnr char(18)
  SELECT @oldKzlager = Kzlager, @oldLkz = Lkz, @oldArtnr = Artnr FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07)
    WHERE (Kzlager = @oldKzlager) AND (Lkz = @oldLkz) AND (Artnr = @oldArtnr) AND (RecId > @oldRecId)
    ORDER BY Kzlager,Lkz,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07)
    WHERE (Kzlager = @oldKzlager) AND (Lkz = @oldLkz) AND (Artnr > @oldArtnr)
    ORDER BY Kzlager,Lkz,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07)
    WHERE (Kzlager = @oldKzlager) AND (Lkz > @oldLkz)
    ORDER BY Kzlager,Lkz,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07)
    WHERE (Kzlager > @oldKzlager)
    ORDER BY Kzlager,Lkz,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next_Stammpositionen_Key07')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Stammpositionen_Key07] @oldRecId integer AS EXECUTE [dbo].[up_Next_Stammpositionen_Key07] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Stammpositionen_Key07')
  DROP PROCEDURE [dbo].[up_Prior_Stammpositionen_Key07]
GO
CREATE PROCEDURE [dbo].[up_Prior_Stammpositionen_Key07] @oldRecId integer AS
  DECLARE @oldKzlager char(1)
  DECLARE @oldLkz char(2)
  DECLARE @oldArtnr char(18)
  SELECT @oldKzlager = Kzlager, @oldLkz = Lkz, @oldArtnr = Artnr FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07)
    WHERE (Kzlager = @oldKzlager) AND (Lkz = @oldLkz) AND (Artnr = @oldArtnr) AND (RecId < @oldRecId)
    ORDER BY Kzlager desc,Lkz desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07)
    WHERE (Kzlager = @oldKzlager) AND (Lkz = @oldLkz) AND (Artnr < @oldArtnr)
    ORDER BY Kzlager desc,Lkz desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07)
    WHERE (Kzlager = @oldKzlager) AND (Lkz < @oldLkz)
    ORDER BY Kzlager desc,Lkz desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07)
    WHERE (Kzlager < @oldKzlager)
    ORDER BY Kzlager desc,Lkz desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior_Stammpositionen_Key07')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Stammpositionen_Key07] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Stammpositionen_Key07] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Stammpositionen_Key07')
  DROP PROCEDURE [dbo].[up_Next50_Stammpositionen_Key07]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next50_Stammpositionen_Key07')
  DROP PROCEDURE [dbo].[qp_Next50_Stammpositionen_Key07]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Stammpositionen_Key07')
  DROP PROCEDURE [dbo].[up_Prior50_Stammpositionen_Key07]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior50_Stammpositionen_Key07')
  DROP PROCEDURE [dbo].[qp_Prior50_Stammpositionen_Key07]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Stammpositionen_Key07')
  DROP PROCEDURE [dbo].[up_FindF_Stammpositionen_Key07]
GO
CREATE PROCEDURE [dbo].[up_FindF_Stammpositionen_Key07] @xKey char(250), @xLen integer AS
  DECLARE @mKzlager char(1) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,1)
  DECLARE @mLkz char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,2,2)
  DECLARE @mArtnr char(18) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,4,18)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 21 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07) WHERE 
      Kzlager = @mKzlager AND 
      Lkz = @mLkz AND 
      Artnr = @mArtnr
      ORDER BY Kzlager,Lkz,Artnr,RecId
    RETURN
  END

  IF @xLen = 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07) WHERE 
      Kzlager = @mKzlager AND 
      Lkz = @mLkz
      ORDER BY Kzlager,Lkz,Artnr,RecId
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07) WHERE 
      Kzlager = @mKzlager
      ORDER BY Kzlager,Lkz,Artnr,RecId
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07) WHERE 
      Kzlager >= @mKzlager AND SUBSTRING(Kzlager,1,LEN(@mKzlager)) = @mKzlager
      ORDER BY Kzlager,Lkz,Artnr,RecId
    RETURN
  END

  IF @xLen < 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07) WHERE 
      Kzlager = @mKzlager AND 
      Lkz >= @mLkz AND SUBSTRING(Lkz,1,LEN(@mLkz)) = @mLkz
      ORDER BY Kzlager,Lkz,Artnr,RecId
    RETURN
  END

  IF @xLen < 21 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07) WHERE 
      Kzlager = @mKzlager AND 
      Lkz = @mLkz AND 
      Artnr >= @mArtnr AND SUBSTRING(Artnr,1,LEN(@mArtnr)) = @mArtnr
      ORDER BY Kzlager,Lkz,Artnr,RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindF_Stammpositionen_Key07')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindF_Stammpositionen_Key07] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindF_Stammpositionen_Key07] @xKey, @xLen')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Stammpositionen_Key07')
  DROP PROCEDURE [dbo].[up_FindL_Stammpositionen_Key07]
GO
CREATE PROCEDURE [dbo].[up_FindL_Stammpositionen_Key07] @xKey char(250), @xLen integer AS
  DECLARE @mKzlager char(1) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,1)
  DECLARE @mLkz char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,2,2)
  DECLARE @mArtnr char(18) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,4,18)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 21 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07) WHERE 
      Kzlager = @mKzlager AND 
      Lkz = @mLkz AND 
      Artnr = @mArtnr
      ORDER BY Kzlager desc,Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen = 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07) WHERE 
      Kzlager = @mKzlager AND 
      Lkz = @mLkz
      ORDER BY Kzlager desc,Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07) WHERE 
      Kzlager = @mKzlager
      ORDER BY Kzlager desc,Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07) WHERE 
      SUBSTRING(Kzlager,1,LEN(@mKzlager)) = @mKzlager
      ORDER BY Kzlager desc,Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen < 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07) WHERE 
      Kzlager = @mKzlager AND 
      SUBSTRING(Lkz,1,LEN(@mLkz)) = @mLkz
      ORDER BY Kzlager desc,Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen < 21 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key07) WHERE 
      Kzlager = @mKzlager AND 
      Lkz = @mLkz AND 
      SUBSTRING(Artnr,1,LEN(@mArtnr)) = @mArtnr
      ORDER BY Kzlager desc,Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindL_Stammpositionen_Key07')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindL_Stammpositionen_Key07] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindL_Stammpositionen_Key07] @xKey, @xLen')
GO


-- logical key  ORDER BY Lkzlager,Artnrlager,RecId
IF 0 < (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Stammpositionen' and so.[id] = si.[id] and si.[name] = 'Ik_Key08')
  DROP INDEX [Ik_Key08] ON [dbo].[ut_Stammpositionen]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Stammpositionen_Key08')
  DROP PROCEDURE [dbo].[up_First_Stammpositionen_Key08]
GO
CREATE PROCEDURE [dbo].[up_First_Stammpositionen_Key08] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key_Z07) ORDER BY Lkzlager,Artnrlager,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Stammpositionen_Key08')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Stammpositionen_Key08] AS EXECUTE [dbo].[up_First_Stammpositionen_Key08]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Stammpositionen_Key08')
  DROP PROCEDURE [dbo].[up_Last_Stammpositionen_Key08]
GO
CREATE PROCEDURE [dbo].[up_Last_Stammpositionen_Key08] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key_Z07) ORDER BY Lkzlager desc,Artnrlager desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Stammpositionen_Key08')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Stammpositionen_Key08] AS EXECUTE [dbo].[up_Last_Stammpositionen_Key08]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Stammpositionen_Key08')
  DROP PROCEDURE [dbo].[up_Next_Stammpositionen_Key08]
GO
CREATE PROCEDURE [dbo].[up_Next_Stammpositionen_Key08] @oldRecId integer AS
  DECLARE @oldLkzlager char(2)
  DECLARE @oldArtnrlager char(18)
  SELECT @oldLkzlager = Lkzlager, @oldArtnrlager = Artnrlager FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key_Z07)
    WHERE (Lkzlager = @oldLkzlager) AND (Artnrlager = @oldArtnrlager) AND (RecId > @oldRecId)
    ORDER BY Lkzlager,Artnrlager,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key_Z07)
    WHERE (Lkzlager = @oldLkzlager) AND (Artnrlager > @oldArtnrlager)
    ORDER BY Lkzlager,Artnrlager,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key_Z07)
    WHERE (Lkzlager > @oldLkzlager)
    ORDER BY Lkzlager,Artnrlager,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next_Stammpositionen_Key08')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Stammpositionen_Key08] @oldRecId integer AS EXECUTE [dbo].[up_Next_Stammpositionen_Key08] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Stammpositionen_Key08')
  DROP PROCEDURE [dbo].[up_Prior_Stammpositionen_Key08]
GO
CREATE PROCEDURE [dbo].[up_Prior_Stammpositionen_Key08] @oldRecId integer AS
  DECLARE @oldLkzlager char(2)
  DECLARE @oldArtnrlager char(18)
  SELECT @oldLkzlager = Lkzlager, @oldArtnrlager = Artnrlager FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key_Z07)
    WHERE (Lkzlager = @oldLkzlager) AND (Artnrlager = @oldArtnrlager) AND (RecId < @oldRecId)
    ORDER BY Lkzlager desc,Artnrlager desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key_Z07)
    WHERE (Lkzlager = @oldLkzlager) AND (Artnrlager < @oldArtnrlager)
    ORDER BY Lkzlager desc,Artnrlager desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key_Z07)
    WHERE (Lkzlager < @oldLkzlager)
    ORDER BY Lkzlager desc,Artnrlager desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior_Stammpositionen_Key08')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Stammpositionen_Key08] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Stammpositionen_Key08] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Stammpositionen_Key08')
  DROP PROCEDURE [dbo].[up_Next50_Stammpositionen_Key08]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next50_Stammpositionen_Key08')
  DROP PROCEDURE [dbo].[qp_Next50_Stammpositionen_Key08]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Stammpositionen_Key08')
  DROP PROCEDURE [dbo].[up_Prior50_Stammpositionen_Key08]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior50_Stammpositionen_Key08')
  DROP PROCEDURE [dbo].[qp_Prior50_Stammpositionen_Key08]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Stammpositionen_Key08')
  DROP PROCEDURE [dbo].[up_FindF_Stammpositionen_Key08]
GO
CREATE PROCEDURE [dbo].[up_FindF_Stammpositionen_Key08] @xKey char(250), @xLen integer AS
  DECLARE @mLkzlager char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,2)
  DECLARE @mArtnrlager char(18) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,3,18)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key_Z07) WHERE 
      Lkzlager = @mLkzlager AND 
      Artnrlager = @mArtnrlager
      ORDER BY Lkzlager,Artnrlager,RecId
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key_Z07) WHERE 
      Lkzlager = @mLkzlager
      ORDER BY Lkzlager,Artnrlager,RecId
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key_Z07) WHERE 
      Lkzlager >= @mLkzlager AND SUBSTRING(Lkzlager,1,LEN(@mLkzlager)) = @mLkzlager
      ORDER BY Lkzlager,Artnrlager,RecId
    RETURN
  END

  IF @xLen < 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key_Z07) WHERE 
      Lkzlager = @mLkzlager AND 
      Artnrlager >= @mArtnrlager AND SUBSTRING(Artnrlager,1,LEN(@mArtnrlager)) = @mArtnrlager
      ORDER BY Lkzlager,Artnrlager,RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindF_Stammpositionen_Key08')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindF_Stammpositionen_Key08] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindF_Stammpositionen_Key08] @xKey, @xLen')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Stammpositionen_Key08')
  DROP PROCEDURE [dbo].[up_FindL_Stammpositionen_Key08]
GO
CREATE PROCEDURE [dbo].[up_FindL_Stammpositionen_Key08] @xKey char(250), @xLen integer AS
  DECLARE @mLkzlager char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,2)
  DECLARE @mArtnrlager char(18) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,3,18)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key_Z07) WHERE 
      Lkzlager = @mLkzlager AND 
      Artnrlager = @mArtnrlager
      ORDER BY Lkzlager desc,Artnrlager desc,RecId desc
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key_Z07) WHERE 
      Lkzlager = @mLkzlager
      ORDER BY Lkzlager desc,Artnrlager desc,RecId desc
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key_Z07) WHERE 
      SUBSTRING(Lkzlager,1,LEN(@mLkzlager)) = @mLkzlager
      ORDER BY Lkzlager desc,Artnrlager desc,RecId desc
    RETURN
  END

  IF @xLen < 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key_Z07) WHERE 
      Lkzlager = @mLkzlager AND 
      SUBSTRING(Artnrlager,1,LEN(@mArtnrlager)) = @mArtnrlager
      ORDER BY Lkzlager desc,Artnrlager desc,RecId desc
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindL_Stammpositionen_Key08')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindL_Stammpositionen_Key08] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindL_Stammpositionen_Key08] @xKey, @xLen')
GO


-- logical key  ORDER BY Kzinventur,Lkz,Artnr,RecId
IF 0 < (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Stammpositionen' and so.[id] = si.[id] and si.[name] = 'Ik_Key09')
  DROP INDEX [Ik_Key09] ON [dbo].[ut_Stammpositionen]
GO
IF 0 = (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Stammpositionen' and so.[id] = si.[id] and si.[name] = 'Ik_Key09')
  CREATE INDEX [Ik_Key09] ON [dbo].[ut_Stammpositionen] (Kzinventur,Lkz,Artnr,RecId)
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Stammpositionen_Key09')
  DROP PROCEDURE [dbo].[up_First_Stammpositionen_Key09]
GO
CREATE PROCEDURE [dbo].[up_First_Stammpositionen_Key09] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09) ORDER BY Kzinventur,Lkz,Artnr,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Stammpositionen_Key09')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Stammpositionen_Key09] AS EXECUTE [dbo].[up_First_Stammpositionen_Key09]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Stammpositionen_Key09')
  DROP PROCEDURE [dbo].[up_Last_Stammpositionen_Key09]
GO
CREATE PROCEDURE [dbo].[up_Last_Stammpositionen_Key09] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09) ORDER BY Kzinventur desc,Lkz desc,Artnr desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Stammpositionen_Key09')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Stammpositionen_Key09] AS EXECUTE [dbo].[up_Last_Stammpositionen_Key09]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Stammpositionen_Key09')
  DROP PROCEDURE [dbo].[up_Next_Stammpositionen_Key09]
GO
CREATE PROCEDURE [dbo].[up_Next_Stammpositionen_Key09] @oldRecId integer AS
  DECLARE @oldKzinventur integer
  DECLARE @oldLkz char(2)
  DECLARE @oldArtnr char(18)
  SELECT @oldKzinventur = Kzinventur, @oldLkz = Lkz, @oldArtnr = Artnr FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09)
    WHERE (Kzinventur = @oldKzinventur) AND (Lkz = @oldLkz) AND (Artnr = @oldArtnr) AND (RecId > @oldRecId)
    ORDER BY Kzinventur,Lkz,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09)
    WHERE (Kzinventur = @oldKzinventur) AND (Lkz = @oldLkz) AND (Artnr > @oldArtnr)
    ORDER BY Kzinventur,Lkz,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09)
    WHERE (Kzinventur = @oldKzinventur) AND (Lkz > @oldLkz)
    ORDER BY Kzinventur,Lkz,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09)
    WHERE (Kzinventur > @oldKzinventur)
    ORDER BY Kzinventur,Lkz,Artnr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next_Stammpositionen_Key09')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Stammpositionen_Key09] @oldRecId integer AS EXECUTE [dbo].[up_Next_Stammpositionen_Key09] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Stammpositionen_Key09')
  DROP PROCEDURE [dbo].[up_Prior_Stammpositionen_Key09]
GO
CREATE PROCEDURE [dbo].[up_Prior_Stammpositionen_Key09] @oldRecId integer AS
  DECLARE @oldKzinventur integer
  DECLARE @oldLkz char(2)
  DECLARE @oldArtnr char(18)
  SELECT @oldKzinventur = Kzinventur, @oldLkz = Lkz, @oldArtnr = Artnr FROM [dbo].[ut_Stammpositionen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09)
    WHERE (Kzinventur = @oldKzinventur) AND (Lkz = @oldLkz) AND (Artnr = @oldArtnr) AND (RecId < @oldRecId)
    ORDER BY Kzinventur desc,Lkz desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09)
    WHERE (Kzinventur = @oldKzinventur) AND (Lkz = @oldLkz) AND (Artnr < @oldArtnr)
    ORDER BY Kzinventur desc,Lkz desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09)
    WHERE (Kzinventur = @oldKzinventur) AND (Lkz < @oldLkz)
    ORDER BY Kzinventur desc,Lkz desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09)
    WHERE (Kzinventur < @oldKzinventur)
    ORDER BY Kzinventur desc,Lkz desc,Artnr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior_Stammpositionen_Key09')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Stammpositionen_Key09] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Stammpositionen_Key09] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Stammpositionen_Key09')
  DROP PROCEDURE [dbo].[up_Next50_Stammpositionen_Key09]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next50_Stammpositionen_Key09')
  DROP PROCEDURE [dbo].[qp_Next50_Stammpositionen_Key09]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Stammpositionen_Key09')
  DROP PROCEDURE [dbo].[up_Prior50_Stammpositionen_Key09]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior50_Stammpositionen_Key09')
  DROP PROCEDURE [dbo].[qp_Prior50_Stammpositionen_Key09]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Stammpositionen_Key09')
  DROP PROCEDURE [dbo].[up_FindF_Stammpositionen_Key09]
GO
CREATE PROCEDURE [dbo].[up_FindF_Stammpositionen_Key09] @xKey char(250), @xLen integer AS
  DECLARE @mKzinventur integer = dbo.itcfunc_KeyToken_integer(@xKey,@xLen,1,1)
  DECLARE @mLkz char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,2,2)
  DECLARE @mArtnr char(18) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,4,18)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 21 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09) WHERE 
      Kzinventur = @mKzinventur AND 
      Lkz = @mLkz AND 
      Artnr = @mArtnr
      ORDER BY Kzinventur,Lkz,Artnr,RecId
    RETURN
  END

  IF @xLen = 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09) WHERE 
      Kzinventur = @mKzinventur AND 
      Lkz = @mLkz
      ORDER BY Kzinventur,Lkz,Artnr,RecId
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09) WHERE 
      Kzinventur = @mKzinventur
      ORDER BY Kzinventur,Lkz,Artnr,RecId
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)


  IF @xLen < 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09) WHERE 
      Kzinventur = @mKzinventur AND 
      Lkz >= @mLkz AND SUBSTRING(Lkz,1,LEN(@mLkz)) = @mLkz
      ORDER BY Kzinventur,Lkz,Artnr,RecId
    RETURN
  END

  IF @xLen < 21 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09) WHERE 
      Kzinventur = @mKzinventur AND 
      Lkz = @mLkz AND 
      Artnr >= @mArtnr AND SUBSTRING(Artnr,1,LEN(@mArtnr)) = @mArtnr
      ORDER BY Kzinventur,Lkz,Artnr,RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindF_Stammpositionen_Key09')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindF_Stammpositionen_Key09] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindF_Stammpositionen_Key09] @xKey, @xLen')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Stammpositionen_Key09')
  DROP PROCEDURE [dbo].[up_FindL_Stammpositionen_Key09]
GO
CREATE PROCEDURE [dbo].[up_FindL_Stammpositionen_Key09] @xKey char(250), @xLen integer AS
  DECLARE @mKzinventur integer = dbo.itcfunc_KeyToken_integer(@xKey,@xLen,1,1)
  DECLARE @mLkz char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,2,2)
  DECLARE @mArtnr char(18) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,4,18)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 21 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09) WHERE 
      Kzinventur = @mKzinventur AND 
      Lkz = @mLkz AND 
      Artnr = @mArtnr
      ORDER BY Kzinventur desc,Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen = 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09) WHERE 
      Kzinventur = @mKzinventur AND 
      Lkz = @mLkz
      ORDER BY Kzinventur desc,Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09) WHERE 
      Kzinventur = @mKzinventur
      ORDER BY Kzinventur desc,Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)


  IF @xLen < 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09) WHERE 
      Kzinventur = @mKzinventur AND 
      SUBSTRING(Lkz,1,LEN(@mLkz)) = @mLkz
      ORDER BY Kzinventur desc,Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  IF @xLen < 21 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Stammpositionen] WITH(INDEX = Ik_Key09) WHERE 
      Kzinventur = @mKzinventur AND 
      Lkz = @mLkz AND 
      SUBSTRING(Artnr,1,LEN(@mArtnr)) = @mArtnr
      ORDER BY Kzinventur desc,Lkz desc,Artnr desc,RecId desc
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindL_Stammpositionen_Key09')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindL_Stammpositionen_Key09] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindL_Stammpositionen_Key09] @xKey, @xLen')
GO


