-- This file is part of the it-crafts software and is subject to copyright protection.
-- (C) 2022..2026, 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_KladdenZeile')
BEGIN
CREATE TABLE [dbo].[ut_KladdenZeile]
(
  [RecId]  integer NOT NULL
 ,[Code] char(20) DEFAULT ''
 ,[LfdNr] integer DEFAULT 0
 ,[Joined] tinyint DEFAULT 0
 ,[TransNo] char(8) DEFAULT ''
 ,[Betrag] float DEFAULT 0
 ,[MWSt] float DEFAULT 0
 ,[Netto] float DEFAULT 0
 ,[Typ] char(1) DEFAULT ''
 ,[Kto] char(8) DEFAULT ''
 ,[SchlDatev] char(10) DEFAULT ''
 ,[UStSatz] float DEFAULT 0
 ,[KtoUSt] char(8) DEFAULT ''
 ,[GK_Typ] char(1) DEFAULT ''
 ,[GK_Kto] char(8) DEFAULT ''
 ,[GK_UStSatz] float DEFAULT 0
 ,[GK_KtoUSt] char(8) DEFAULT ''
 ,[PrjNr] char(8) DEFAULT ''
 ,[BelArt] char(2) DEFAULT ''
 ,[BelNr] char(8) DEFAULT ''
 ,[ExtBelNr] char(20) DEFAULT ''
 ,[Kst] char(20) DEFAULT ''
 ,[Ktr] char(20) DEFAULT ''
 ,[BuText] char(50) DEFAULT ''
 ,[Faellig] integer DEFAULT 0
 ,[SkoFrist] integer DEFAULT 0
 ,[SkoSatz] float DEFAULT 0
 ,[Name1] char(40) DEFAULT ''
 ,[Name2] char(40) DEFAULT ''
 ,[Strasse] char(40) DEFAULT ''
 ,[Plz] char(8) DEFAULT ''
 ,[Ort] char(40) DEFAULT ''
 ,[VkzExport] integer DEFAULT 0
 ,[BuchDatum] datetime DEFAULT CONVERT(datetime,'1900-01-01',120)
 ,[BelegDatum] datetime DEFAULT CONVERT(datetime,'1900-01-01',120)
 ,[Purpose] integer DEFAULT 0
 ,[Vorgang] char(20) DEFAULT ''
 ,[AusglFuer] char(8) DEFAULT ''
 ,[AusglBetr] float DEFAULT 0
 ,[AusglSkon] float DEFAULT 0
 ,[AusglKto] char(8) DEFAULT ''
 ,[AusglSKto] char(8) DEFAULT ''
 ,[DatumsTyp] integer DEFAULT 0
 ,[AttStamp] char(32) DEFAULT ''
 ,[AttName] char(30) DEFAULT ''
  CONSTRAINT [Pk_KladdenZeile]  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_KladdenZeile_KeyDel')
  DROP TRIGGER [dbo].[tr_KladdenZeile_KeyDel]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'TR'   and [name] = 'tr_KladdenZeile_KeyUpd')
  DROP TRIGGER [dbo].[tr_KladdenZeile_KeyUpd]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'U' and [name] = 'ut_KladdenZeile_Keys')
  DROP TABLE [dbo].[ut_KladdenZeile_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_KladdenZeile' and sc.[name] = 'Code')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [Code] 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_KladdenZeile' and sc.[name] = 'LfdNr')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [LfdNr] 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_KladdenZeile' and sc.[name] = 'Joined')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [Joined] 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_KladdenZeile' and sc.[name] = 'TransNo')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [TransNo] 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_KladdenZeile' and sc.[name] = 'Betrag')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [Betrag] 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_KladdenZeile' and sc.[name] = 'MWSt')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [MWSt] 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_KladdenZeile' and sc.[name] = 'Netto')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [Netto] 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_KladdenZeile' and sc.[name] = 'Typ')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [Typ] 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_KladdenZeile' and sc.[name] = 'Kto')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [Kto] 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_KladdenZeile' and sc.[name] = 'SchlDatev')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [SchlDatev] char(10) 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_KladdenZeile' and sc.[name] = 'UStSatz')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [UStSatz] 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_KladdenZeile' and sc.[name] = 'KtoUSt')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [KtoUSt] 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_KladdenZeile' and sc.[name] = 'GK_Typ')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [GK_Typ] 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_KladdenZeile' and sc.[name] = 'GK_Kto')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [GK_Kto] 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_KladdenZeile' and sc.[name] = 'GK_UStSatz')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [GK_UStSatz] 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_KladdenZeile' and sc.[name] = 'GK_KtoUSt')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [GK_KtoUSt] 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_KladdenZeile' and sc.[name] = 'PrjNr')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [PrjNr] 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_KladdenZeile' and sc.[name] = 'BelArt')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [BelArt] 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_KladdenZeile' and sc.[name] = 'BelNr')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [BelNr] 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_KladdenZeile' and sc.[name] = 'ExtBelNr')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [ExtBelNr] 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_KladdenZeile' and sc.[name] = 'Kst')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [Kst] 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_KladdenZeile' and sc.[name] = 'Ktr')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [Ktr] 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_KladdenZeile' and sc.[name] = 'BuText')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [BuText] char(50) 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_KladdenZeile' and sc.[name] = 'Faellig')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [Faellig] 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_KladdenZeile' and sc.[name] = 'SkoFrist')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [SkoFrist] 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_KladdenZeile' and sc.[name] = 'SkoSatz')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [SkoSatz] 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_KladdenZeile' and sc.[name] = 'Name1')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [Name1] char(40) 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_KladdenZeile' and sc.[name] = 'Name2')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [Name2] char(40) 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_KladdenZeile' and sc.[name] = 'Strasse')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [Strasse] char(40) 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_KladdenZeile' and sc.[name] = 'Plz')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [Plz] 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_KladdenZeile' and sc.[name] = 'Ort')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [Ort] char(40) 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_KladdenZeile' and sc.[name] = 'VkzExport')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [VkzExport] 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_KladdenZeile' and sc.[name] = 'BuchDatum')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [BuchDatum] 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_KladdenZeile' and sc.[name] = 'BelegDatum')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [BelegDatum] 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_KladdenZeile' and sc.[name] = 'Purpose')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [Purpose] 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_KladdenZeile' and sc.[name] = 'Vorgang')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [Vorgang] 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_KladdenZeile' and sc.[name] = 'AusglFuer')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [AusglFuer] 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_KladdenZeile' and sc.[name] = 'AusglBetr')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [AusglBetr] 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_KladdenZeile' and sc.[name] = 'AusglSkon')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [AusglSkon] 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_KladdenZeile' and sc.[name] = 'AusglKto')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [AusglKto] 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_KladdenZeile' and sc.[name] = 'AusglSKto')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [AusglSKto] 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_KladdenZeile' and sc.[name] = 'DatumsTyp')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [DatumsTyp] 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_KladdenZeile' and sc.[name] = 'AttStamp')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [AttStamp] char(32) 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_KladdenZeile' and sc.[name] = 'AttName')
BEGIN
  ALTER TABLE [dbo].[ut_KladdenZeile] ADD [AttName] char(30) NOT NULL DEFAULT ''
END
GO

-- check lengths of text fields
IF (20 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Code'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Code'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Code] char(20)
END
GO
IF (20 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Code'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Code'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Code] varchar(20)
END
GO
IF (8 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'TransNo'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'TransNo'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [TransNo] char(8)
END
GO
IF (8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'TransNo'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'TransNo'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [TransNo] varchar(8)
END
GO
IF (1 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Typ'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Typ'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Typ] char(1)
END
GO
IF (1 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Typ'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Typ'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Typ] varchar(1)
END
GO
IF (8 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Kto'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Kto'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Kto] char(8)
END
GO
IF (8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Kto'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Kto'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Kto] varchar(8)
END
GO
IF (10 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'SchlDatev'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'SchlDatev'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [SchlDatev] char(10)
END
GO
IF (10 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'SchlDatev'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'SchlDatev'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [SchlDatev] varchar(10)
END
GO
IF (8 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'KtoUSt'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'KtoUSt'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [KtoUSt] char(8)
END
GO
IF (8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'KtoUSt'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'KtoUSt'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [KtoUSt] varchar(8)
END
GO
IF (1 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'GK_Typ'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'GK_Typ'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [GK_Typ] char(1)
END
GO
IF (1 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'GK_Typ'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'GK_Typ'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [GK_Typ] varchar(1)
END
GO
IF (8 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'GK_Kto'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'GK_Kto'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [GK_Kto] char(8)
END
GO
IF (8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'GK_Kto'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'GK_Kto'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [GK_Kto] varchar(8)
END
GO
IF (8 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'GK_KtoUSt'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'GK_KtoUSt'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [GK_KtoUSt] char(8)
END
GO
IF (8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'GK_KtoUSt'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'GK_KtoUSt'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [GK_KtoUSt] varchar(8)
END
GO
IF (8 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'PrjNr'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'PrjNr'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [PrjNr] char(8)
END
GO
IF (8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'PrjNr'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'PrjNr'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [PrjNr] varchar(8)
END
GO
IF (2 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'BelArt'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'BelArt'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [BelArt] char(2)
END
GO
IF (2 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'BelArt'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'BelArt'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [BelArt] varchar(2)
END
GO
IF (8 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'BelNr'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'BelNr'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [BelNr] char(8)
END
GO
IF (8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'BelNr'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'BelNr'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [BelNr] varchar(8)
END
GO
IF (20 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'ExtBelNr'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'ExtBelNr'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [ExtBelNr] char(20)
END
GO
IF (20 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'ExtBelNr'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'ExtBelNr'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [ExtBelNr] varchar(20)
END
GO
IF (20 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Kst'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Kst'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Kst] char(20)
END
GO
IF (20 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Kst'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Kst'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Kst] varchar(20)
END
GO
IF (20 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Ktr'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Ktr'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Ktr] char(20)
END
GO
IF (20 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Ktr'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Ktr'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Ktr] varchar(20)
END
GO
IF (50 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'BuText'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'BuText'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [BuText] char(50)
END
GO
IF (50 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'BuText'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'BuText'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [BuText] varchar(50)
END
GO
IF (40 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Name1'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Name1'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Name1] char(40)
END
GO
IF (40 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Name1'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Name1'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Name1] varchar(40)
END
GO
IF (40 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Name2'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Name2'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Name2] char(40)
END
GO
IF (40 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Name2'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Name2'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Name2] varchar(40)
END
GO
IF (40 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Strasse'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Strasse'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Strasse] char(40)
END
GO
IF (40 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Strasse'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Strasse'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Strasse] varchar(40)
END
GO
IF (8 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Plz'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Plz'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Plz] char(8)
END
GO
IF (8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Plz'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Plz'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Plz] varchar(8)
END
GO
IF (40 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Ort'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Ort'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Ort] char(40)
END
GO
IF (40 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Ort'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Ort'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Ort] varchar(40)
END
GO
IF (20 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Vorgang'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Vorgang'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Vorgang] char(20)
END
GO
IF (20 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Vorgang'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'Vorgang'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [Vorgang] varchar(20)
END
GO
IF (8 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AusglFuer'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AusglFuer'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [AusglFuer] char(8)
END
GO
IF (8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AusglFuer'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AusglFuer'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [AusglFuer] varchar(8)
END
GO
IF (8 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AusglKto'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AusglKto'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [AusglKto] char(8)
END
GO
IF (8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AusglKto'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AusglKto'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [AusglKto] varchar(8)
END
GO
IF (8 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AusglSKto'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AusglSKto'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [AusglSKto] char(8)
END
GO
IF (8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AusglSKto'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AusglSKto'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [AusglSKto] varchar(8)
END
GO
IF (32 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AttStamp'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AttStamp'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [AttStamp] char(32)
END
GO
IF (32 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AttStamp'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AttStamp'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [AttStamp] varchar(32)
END
GO
IF (30 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AttName'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AttName'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [AttName] char(30)
END
GO
IF (30 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AttName'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_KladdenZeile' and sc.[name] = 'AttName'))
BEGIN
   ALTER TABLE [dbo].[ut_KladdenZeile] ALTER COLUMN [AttName] varchar(30)
END
GO

-- create dedicated view
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'KladdenZeile')
  DROP VIEW [dbo].[KladdenZeile]
GO
CREATE VIEW [dbo].[KladdenZeile] AS
  SELECT [RecId]
 ,[Code]
 ,[LfdNr]
 ,[Joined]
 ,[TransNo]
 ,[Betrag]
 ,[MWSt]
 ,[Netto]
 ,[Typ]
 ,[Kto]
 ,[SchlDatev]
 ,[UStSatz]
 ,[KtoUSt]
 ,[GK_Typ]
 ,[GK_Kto]
 ,[GK_UStSatz]
 ,[GK_KtoUSt]
 ,[PrjNr]
 ,[BelArt]
 ,[BelNr]
 ,[ExtBelNr]
 ,[Kst]
 ,[Ktr]
 ,[BuText]
 ,[Faellig]
 ,[SkoFrist]
 ,[SkoSatz]
 ,[Name1]
 ,[Name2]
 ,[Strasse]
 ,[Plz]
 ,[Ort]
 ,[VkzExport]
 ,[BuchDatum]
 ,[BelegDatum]
 ,[Purpose]
 ,[Vorgang]
 ,[AusglFuer]
 ,[AusglBetr]
 ,[AusglSkon]
 ,[AusglKto]
 ,[AusglSKto]
 ,[DatumsTyp]
 ,[AttStamp]
 ,[AttName]
  FROM [dbo].[ut_KladdenZeile]
GO


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

-- select with classic client
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Sel_KladdenZeile')
  DROP PROCEDURE [dbo].[up_Sel_KladdenZeile]
GO
CREATE PROCEDURE [dbo].[up_Sel_KladdenZeile]  @mRI integer
AS
  SELECT
    [Code]
   ,[LfdNr]
   ,[Joined]
   ,[TransNo]
   ,[Betrag]
   ,[MWSt]
   ,[Netto]
   ,[Typ]
   ,[Kto]
   ,[SchlDatev]
   ,[UStSatz]
   ,[KtoUSt]
   ,[GK_Typ]
   ,[GK_Kto]
   ,[GK_UStSatz]
   ,[GK_KtoUSt]
   ,[PrjNr]
   ,[BelArt]
   ,[BelNr]
   ,[ExtBelNr]
   ,[Kst]
   ,[Ktr]
   ,[BuText]
   ,[Faellig]
   ,[SkoFrist]
   ,[SkoSatz]
   ,[Name1]
   ,[Name2]
   ,[Strasse]
   ,[Plz]
   ,[Ort]
   ,[VkzExport]
   ,[BuchDatum]
   ,[BelegDatum]
   ,[Purpose]
   ,[Vorgang]
   ,[AusglFuer]
   ,[AusglBetr]
   ,[AusglSkon]
   ,[AusglKto]
   ,[AusglSKto]
   ,[DatumsTyp]
   ,[AttStamp]
   ,[AttName]
  FROM [dbo].[ut_KladdenZeile] WHERE RecId = @mRI
GO

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

-- insert with classic client
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Append_KladdenZeile')
  DROP PROCEDURE [dbo].[up_Append_KladdenZeile]
GO
CREATE PROCEDURE [dbo].[up_Append_KladdenZeile]
   @Param_Code char(20)
  ,@Param_LfdNr integer
  ,@Param_Joined tinyint
  ,@Param_TransNo char(8)
  ,@Param_Betrag float
  ,@Param_MWSt float
  ,@Param_Netto float
  ,@Param_Typ char(1)
  ,@Param_Kto char(8)
  ,@Param_SchlDatev char(10)
  ,@Param_UStSatz float
  ,@Param_KtoUSt char(8)
  ,@Param_GK_Typ char(1)
  ,@Param_GK_Kto char(8)
  ,@Param_GK_UStSatz float
  ,@Param_GK_KtoUSt char(8)
  ,@Param_PrjNr char(8)
  ,@Param_BelArt char(2)
  ,@Param_BelNr char(8)
  ,@Param_ExtBelNr char(20)
  ,@Param_Kst char(20)
  ,@Param_Ktr char(20)
  ,@Param_BuText char(50)
  ,@Param_Faellig integer
  ,@Param_SkoFrist integer
  ,@Param_SkoSatz float
  ,@Param_Name1 char(40)
  ,@Param_Name2 char(40)
  ,@Param_Strasse char(40)
  ,@Param_Plz char(8)
  ,@Param_Ort char(40)
  ,@Param_VkzExport integer
  ,@Param_BuchDatum datetime
  ,@Param_BelegDatum datetime
  ,@Param_Purpose integer
  ,@Param_Vorgang char(20)
  ,@Param_AusglFuer char(8)
  ,@Param_AusglBetr float
  ,@Param_AusglSkon float
  ,@Param_AusglKto char(8)
  ,@Param_AusglSKto char(8)
  ,@Param_DatumsTyp integer
  ,@Param_AttStamp char(32)
  ,@Param_AttName char(30)
AS
  DECLARE @NeueId integer
  DECLARE @m_CountDown integer = 10
  WHILE @m_CountDown > 0 BEGIN
    BEGIN TRANSACTION
    BEGIN TRY
      SET @NeueId = dbo.itcfunc_NextRecId_KladdenZeile()
      INSERT INTO [dbo].[ut_KladdenZeile] (RecId
        ,[Code]
        ,[LfdNr]
        ,[Joined]
        ,[TransNo]
        ,[Betrag]
        ,[MWSt]
        ,[Netto]
        ,[Typ]
        ,[Kto]
        ,[SchlDatev]
        ,[UStSatz]
        ,[KtoUSt]
        ,[GK_Typ]
        ,[GK_Kto]
        ,[GK_UStSatz]
        ,[GK_KtoUSt]
        ,[PrjNr]
        ,[BelArt]
        ,[BelNr]
        ,[ExtBelNr]
        ,[Kst]
        ,[Ktr]
        ,[BuText]
        ,[Faellig]
        ,[SkoFrist]
        ,[SkoSatz]
        ,[Name1]
        ,[Name2]
        ,[Strasse]
        ,[Plz]
        ,[Ort]
        ,[VkzExport]
        ,[BuchDatum]
        ,[BelegDatum]
        ,[Purpose]
        ,[Vorgang]
        ,[AusglFuer]
        ,[AusglBetr]
        ,[AusglSkon]
        ,[AusglKto]
        ,[AusglSKto]
        ,[DatumsTyp]
        ,[AttStamp]
        ,[AttName]
          ) VALUES (@NeueId
        ,UPPER(RTRIM(@Param_Code))
        ,@Param_LfdNr
        ,@Param_Joined
        ,UPPER(RTRIM(@Param_TransNo))
        ,@Param_Betrag
        ,@Param_MWSt
        ,@Param_Netto
        ,UPPER(RTRIM(@Param_Typ))
        ,UPPER(RTRIM(@Param_Kto))
        ,UPPER(RTRIM(@Param_SchlDatev))
        ,@Param_UStSatz
        ,UPPER(RTRIM(@Param_KtoUSt))
        ,UPPER(RTRIM(@Param_GK_Typ))
        ,UPPER(RTRIM(@Param_GK_Kto))
        ,@Param_GK_UStSatz
        ,UPPER(RTRIM(@Param_GK_KtoUSt))
        ,UPPER(RTRIM(@Param_PrjNr))
        ,UPPER(RTRIM(@Param_BelArt))
        ,UPPER(RTRIM(@Param_BelNr))
        ,UPPER(RTRIM(@Param_ExtBelNr))
        ,UPPER(RTRIM(@Param_Kst))
        ,UPPER(RTRIM(@Param_Ktr))
        ,RTRIM(@Param_BuText)
        ,@Param_Faellig
        ,@Param_SkoFrist
        ,@Param_SkoSatz
        ,RTRIM(@Param_Name1)
        ,RTRIM(@Param_Name2)
        ,RTRIM(@Param_Strasse)
        ,RTRIM(@Param_Plz)
        ,RTRIM(@Param_Ort)
        ,@Param_VkzExport
        ,@Param_BuchDatum
        ,@Param_BelegDatum
        ,@Param_Purpose
        ,UPPER(RTRIM(@Param_Vorgang))
        ,UPPER(RTRIM(@Param_AusglFuer))
        ,@Param_AusglBetr
        ,@Param_AusglSkon
        ,UPPER(RTRIM(@Param_AusglKto))
        ,UPPER(RTRIM(@Param_AusglSKto))
        ,@Param_DatumsTyp
        ,UPPER(RTRIM(@Param_AttStamp))
        ,RTRIM(@Param_AttName)
          )
      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_KladdenZeile')
  DROP PROCEDURE [dbo].[up_Add_KladdenZeile]
GO
CREATE PROCEDURE [dbo].[up_Add_KladdenZeile]
AS
  DECLARE @NeueId integer = dbo.itcfunc_NextRecId_KladdenZeile()
  INSERT INTO [dbo].[ut_KladdenZeile] (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_KladdenZeile')
  DROP PROCEDURE [dbo].[up_Upd_KladdenZeile]
GO
CREATE PROCEDURE [dbo].[up_Upd_KladdenZeile] @mRI integer
  ,@Param_Code char(20)
  ,@Param_LfdNr integer
  ,@Param_Joined tinyint
  ,@Param_TransNo char(8)
  ,@Param_Betrag float
  ,@Param_MWSt float
  ,@Param_Netto float
  ,@Param_Typ char(1)
  ,@Param_Kto char(8)
  ,@Param_SchlDatev char(10)
  ,@Param_UStSatz float
  ,@Param_KtoUSt char(8)
  ,@Param_GK_Typ char(1)
  ,@Param_GK_Kto char(8)
  ,@Param_GK_UStSatz float
  ,@Param_GK_KtoUSt char(8)
  ,@Param_PrjNr char(8)
  ,@Param_BelArt char(2)
  ,@Param_BelNr char(8)
  ,@Param_ExtBelNr char(20)
  ,@Param_Kst char(20)
  ,@Param_Ktr char(20)
  ,@Param_BuText char(50)
  ,@Param_Faellig integer
  ,@Param_SkoFrist integer
  ,@Param_SkoSatz float
  ,@Param_Name1 char(40)
  ,@Param_Name2 char(40)
  ,@Param_Strasse char(40)
  ,@Param_Plz char(8)
  ,@Param_Ort char(40)
  ,@Param_VkzExport integer
  ,@Param_BuchDatum datetime
  ,@Param_BelegDatum datetime
  ,@Param_Purpose integer
  ,@Param_Vorgang char(20)
  ,@Param_AusglFuer char(8)
  ,@Param_AusglBetr float
  ,@Param_AusglSkon float
  ,@Param_AusglKto char(8)
  ,@Param_AusglSKto char(8)
  ,@Param_DatumsTyp integer
  ,@Param_AttStamp char(32)
  ,@Param_AttName char(30)
AS
  UPDATE [dbo].[ut_KladdenZeile] SET
    [Code] = UPPER(RTRIM(@Param_Code))
   ,[LfdNr] = @Param_LfdNr
   ,[Joined] = @Param_Joined
   ,[TransNo] = UPPER(RTRIM(@Param_TransNo))
   ,[Betrag] = @Param_Betrag
   ,[MWSt] = @Param_MWSt
   ,[Netto] = @Param_Netto
   ,[Typ] = UPPER(RTRIM(@Param_Typ))
   ,[Kto] = UPPER(RTRIM(@Param_Kto))
   ,[SchlDatev] = UPPER(RTRIM(@Param_SchlDatev))
   ,[UStSatz] = @Param_UStSatz
   ,[KtoUSt] = UPPER(RTRIM(@Param_KtoUSt))
   ,[GK_Typ] = UPPER(RTRIM(@Param_GK_Typ))
   ,[GK_Kto] = UPPER(RTRIM(@Param_GK_Kto))
   ,[GK_UStSatz] = @Param_GK_UStSatz
   ,[GK_KtoUSt] = UPPER(RTRIM(@Param_GK_KtoUSt))
   ,[PrjNr] = UPPER(RTRIM(@Param_PrjNr))
   ,[BelArt] = UPPER(RTRIM(@Param_BelArt))
   ,[BelNr] = UPPER(RTRIM(@Param_BelNr))
   ,[ExtBelNr] = UPPER(RTRIM(@Param_ExtBelNr))
   ,[Kst] = UPPER(RTRIM(@Param_Kst))
   ,[Ktr] = UPPER(RTRIM(@Param_Ktr))
   ,[BuText] = RTRIM(@Param_BuText)
   ,[Faellig] = @Param_Faellig
   ,[SkoFrist] = @Param_SkoFrist
   ,[SkoSatz] = @Param_SkoSatz
   ,[Name1] = RTRIM(@Param_Name1)
   ,[Name2] = RTRIM(@Param_Name2)
   ,[Strasse] = RTRIM(@Param_Strasse)
   ,[Plz] = RTRIM(@Param_Plz)
   ,[Ort] = RTRIM(@Param_Ort)
   ,[VkzExport] = @Param_VkzExport
   ,[BuchDatum] = @Param_BuchDatum
   ,[BelegDatum] = @Param_BelegDatum
   ,[Purpose] = @Param_Purpose
   ,[Vorgang] = UPPER(RTRIM(@Param_Vorgang))
   ,[AusglFuer] = UPPER(RTRIM(@Param_AusglFuer))
   ,[AusglBetr] = @Param_AusglBetr
   ,[AusglSkon] = @Param_AusglSkon
   ,[AusglKto] = UPPER(RTRIM(@Param_AusglKto))
   ,[AusglSKto] = UPPER(RTRIM(@Param_AusglSKto))
   ,[DatumsTyp] = @Param_DatumsTyp
   ,[AttStamp] = UPPER(RTRIM(@Param_AttStamp))
   ,[AttName] = RTRIM(@Param_AttName)
  WHERE RecId = @mRI
GO

-- update with modern clients
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Upd2_KladdenZeile')
  DROP PROCEDURE [dbo].[up_Upd2_KladdenZeile]
GO
CREATE PROCEDURE [dbo].[up_Upd2_KladdenZeile] @Param_RecId varchar(20)
  ,@Param_Code varchar(20)
  ,@Param_LfdNr varchar(8)
  ,@Param_Joined varchar(1)
  ,@Param_TransNo varchar(8)
  ,@Param_Betrag varchar(12)
  ,@Param_MWSt varchar(12)
  ,@Param_Netto varchar(12)
  ,@Param_Typ varchar(1)
  ,@Param_Kto varchar(8)
  ,@Param_SchlDatev varchar(10)
  ,@Param_UStSatz varchar(6)
  ,@Param_KtoUSt varchar(8)
  ,@Param_GK_Typ varchar(1)
  ,@Param_GK_Kto varchar(8)
  ,@Param_GK_UStSatz varchar(6)
  ,@Param_GK_KtoUSt varchar(8)
  ,@Param_PrjNr varchar(8)
  ,@Param_BelArt varchar(2)
  ,@Param_BelNr varchar(8)
  ,@Param_ExtBelNr varchar(20)
  ,@Param_Kst varchar(20)
  ,@Param_Ktr varchar(20)
  ,@Param_BuText varchar(50)
  ,@Param_Faellig varchar(3)
  ,@Param_SkoFrist varchar(3)
  ,@Param_SkoSatz varchar(4)
  ,@Param_Name1 varchar(40)
  ,@Param_Name2 varchar(40)
  ,@Param_Strasse varchar(40)
  ,@Param_Plz varchar(8)
  ,@Param_Ort varchar(40)
  ,@Param_VkzExport varchar(1)
  ,@Param_BuchDatum varchar(10)
  ,@Param_BelegDatum varchar(10)
  ,@Param_Purpose varchar(1)
  ,@Param_Vorgang varchar(20)
  ,@Param_AusglFuer varchar(8)
  ,@Param_AusglBetr varchar(12)
  ,@Param_AusglSkon varchar(12)
  ,@Param_AusglKto varchar(8)
  ,@Param_AusglSKto varchar(8)
  ,@Param_DatumsTyp varchar(1)
  ,@Param_AttStamp varchar(32)
  ,@Param_AttName varchar(30)
AS
  DECLARE @mRI integer = dbo.itcfunc_integer(@Param_RecId)
  UPDATE [dbo].[ut_KladdenZeile] SET
    [Code] = UPPER(RTRIM(@Param_Code))
   ,[LfdNr] = dbo.itcfunc_integer(@Param_LfdNr)
   ,[Joined] = dbo.itcfunc_integer(@Param_Joined)
   ,[TransNo] = UPPER(RTRIM(@Param_TransNo))
   ,[Betrag] = dbo.itcfunc_float(@Param_Betrag)
   ,[MWSt] = dbo.itcfunc_float(@Param_MWSt)
   ,[Netto] = dbo.itcfunc_float(@Param_Netto)
   ,[Typ] = UPPER(RTRIM(@Param_Typ))
   ,[Kto] = UPPER(RTRIM(@Param_Kto))
   ,[SchlDatev] = UPPER(RTRIM(@Param_SchlDatev))
   ,[UStSatz] = dbo.itcfunc_float(@Param_UStSatz)
   ,[KtoUSt] = UPPER(RTRIM(@Param_KtoUSt))
   ,[GK_Typ] = UPPER(RTRIM(@Param_GK_Typ))
   ,[GK_Kto] = UPPER(RTRIM(@Param_GK_Kto))
   ,[GK_UStSatz] = dbo.itcfunc_float(@Param_GK_UStSatz)
   ,[GK_KtoUSt] = UPPER(RTRIM(@Param_GK_KtoUSt))
   ,[PrjNr] = UPPER(RTRIM(@Param_PrjNr))
   ,[BelArt] = UPPER(RTRIM(@Param_BelArt))
   ,[BelNr] = UPPER(RTRIM(@Param_BelNr))
   ,[ExtBelNr] = UPPER(RTRIM(@Param_ExtBelNr))
   ,[Kst] = UPPER(RTRIM(@Param_Kst))
   ,[Ktr] = UPPER(RTRIM(@Param_Ktr))
   ,[BuText] = RTRIM(@Param_BuText)
   ,[Faellig] = dbo.itcfunc_integer(@Param_Faellig)
   ,[SkoFrist] = dbo.itcfunc_integer(@Param_SkoFrist)
   ,[SkoSatz] = dbo.itcfunc_float(@Param_SkoSatz)
   ,[Name1] = RTRIM(@Param_Name1)
   ,[Name2] = RTRIM(@Param_Name2)
   ,[Strasse] = RTRIM(@Param_Strasse)
   ,[Plz] = RTRIM(@Param_Plz)
   ,[Ort] = RTRIM(@Param_Ort)
   ,[VkzExport] = dbo.itcfunc_integer(@Param_VkzExport)
   ,[BuchDatum] = dbo.itcfunc_datetime(@Param_BuchDatum)
   ,[BelegDatum] = dbo.itcfunc_datetime(@Param_BelegDatum)
   ,[Purpose] = dbo.itcfunc_integer(@Param_Purpose)
   ,[Vorgang] = UPPER(RTRIM(@Param_Vorgang))
   ,[AusglFuer] = UPPER(RTRIM(@Param_AusglFuer))
   ,[AusglBetr] = dbo.itcfunc_float(@Param_AusglBetr)
   ,[AusglSkon] = dbo.itcfunc_float(@Param_AusglSkon)
   ,[AusglKto] = UPPER(RTRIM(@Param_AusglKto))
   ,[AusglSKto] = UPPER(RTRIM(@Param_AusglSKto))
   ,[DatumsTyp] = dbo.itcfunc_integer(@Param_DatumsTyp)
   ,[AttStamp] = UPPER(RTRIM(@Param_AttStamp))
   ,[AttName] = RTRIM(@Param_AttName)
  WHERE RecId = @mRI

  EXECUTE [dbo].[up_Sel2_KladdenZeile] @mRI
GO

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

-- text index
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_TrSel_KladdenZeile')
  DROP PROCEDURE [dbo].[up_TrSel_KladdenZeile]
GO
CREATE PROCEDURE [dbo].[up_TrSel_KladdenZeile]
AS
  SELECT RecId,
    LTRIM(RTRIM([Code]))
   +LTRIM(RTRIM([TransNo]))
   +LTRIM(RTRIM([Typ]))
   +LTRIM(RTRIM([Kto]))
   +LTRIM(RTRIM([SchlDatev]))
   +LTRIM(RTRIM([KtoUSt]))
   +LTRIM(RTRIM([GK_Typ]))
   +LTRIM(RTRIM([GK_Kto]))
   +LTRIM(RTRIM([GK_KtoUSt]))
   +LTRIM(RTRIM([PrjNr]))
   +LTRIM(RTRIM([BelArt]))
   +LTRIM(RTRIM([BelNr]))
   +LTRIM(RTRIM([ExtBelNr]))
   +LTRIM(RTRIM([Kst]))
   +LTRIM(RTRIM([Ktr]))
   +LTRIM(RTRIM([BuText]))
   +LTRIM(RTRIM([Name1]))
   +LTRIM(RTRIM([Name2]))
   +LTRIM(RTRIM([Strasse]))
   +LTRIM(RTRIM([Plz]))
   +LTRIM(RTRIM([Ort]))
   +LTRIM(RTRIM([Vorgang]))
   +LTRIM(RTRIM([AusglFuer]))
   +LTRIM(RTRIM([AusglKto]))
   +LTRIM(RTRIM([AusglSKto]))
   +LTRIM(RTRIM([AttStamp]))
   +LTRIM(RTRIM([AttName]))
  AS Data FROM [dbo].[ut_KladdenZeile] WHERE RecId > 0 ORDER BY RecId
GO

-- reindex
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Reorg_KladdenZeile')
  DROP PROCEDURE [dbo].[up_Reorg_KladdenZeile]
GO
CREATE PROCEDURE [dbo].[up_Reorg_KladdenZeile] @bDbcc integer,@bRecId integer
AS
  IF (@bRecId = 1)
  BEGIN
    DECLARE  C        CURSOR FOR SELECT RecId FROM [dbo].[ut_KladdenZeile] 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_KladdenZeile] 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_KladdenZeile] )
  END
GO

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_KladdenZeile_Key00')
  DROP PROCEDURE [dbo].[up_First_KladdenZeile_Key00]
GO
CREATE PROCEDURE [dbo].[up_First_KladdenZeile_Key00] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key00) ORDER BY Code,LfdNr,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_KladdenZeile_Key00')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_KladdenZeile_Key00] AS EXECUTE [dbo].[up_First_KladdenZeile_Key00]')
GO

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_KladdenZeile_Key00')
  DROP PROCEDURE [dbo].[up_Next_KladdenZeile_Key00]
GO
CREATE PROCEDURE [dbo].[up_Next_KladdenZeile_Key00] @oldRecId integer AS
  DECLARE @oldCode char(20)
  DECLARE @oldLfdNr integer
  SELECT @oldCode = Code, @oldLfdNr = LfdNr FROM [dbo].[ut_KladdenZeile] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key00)
    WHERE (Code = @oldCode) AND (LfdNr > @oldLfdNr)
    ORDER BY Code,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key00)
    WHERE (Code > @oldCode)
    ORDER BY Code,LfdNr,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_KladdenZeile_Key00')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_KladdenZeile_Key00] @oldRecId integer AS EXECUTE [dbo].[up_Next_KladdenZeile_Key00] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_KladdenZeile_Key00')
  DROP PROCEDURE [dbo].[up_Prior_KladdenZeile_Key00]
GO
CREATE PROCEDURE [dbo].[up_Prior_KladdenZeile_Key00] @oldRecId integer AS
  DECLARE @oldCode char(20)
  DECLARE @oldLfdNr integer
  SELECT @oldCode = Code, @oldLfdNr = LfdNr FROM [dbo].[ut_KladdenZeile] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key00)
    WHERE (Code = @oldCode) AND (LfdNr < @oldLfdNr)
    ORDER BY Code desc,LfdNr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key00)
    WHERE (Code < @oldCode)
    ORDER BY Code desc,LfdNr 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_KladdenZeile_Key00')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_KladdenZeile_Key00] @oldRecId integer AS EXECUTE [dbo].[up_Prior_KladdenZeile_Key00] @oldRecId')
GO

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

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_KladdenZeile_Key00')
  DROP PROCEDURE [dbo].[up_FindF_KladdenZeile_Key00]
GO
CREATE PROCEDURE [dbo].[up_FindF_KladdenZeile_Key00] @xKey char(250), @xLen integer AS
  DECLARE @mCode char(20) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,20)
  DECLARE @mLfdNr integer = dbo.itcfunc_KeyToken_integer(@xKey,@xLen,21,8)

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

  IF @xLen = 28 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key00) WHERE 
      Code = @mCode AND 
      LfdNr = @mLfdNr
      ORDER BY Code,LfdNr,RecId
    RETURN
  END

  IF @xLen = 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key00) WHERE 
      Code = @mCode
      ORDER BY Code,LfdNr,RecId
    RETURN
  END

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

  IF @xLen < 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key00) WHERE 
      Code >= @mCode AND SUBSTRING(Code,1,LEN(@mCode)) = @mCode
      ORDER BY Code,LfdNr,RecId
    RETURN
  END


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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_KladdenZeile_Key00')
  DROP PROCEDURE [dbo].[up_FindL_KladdenZeile_Key00]
GO
CREATE PROCEDURE [dbo].[up_FindL_KladdenZeile_Key00] @xKey char(250), @xLen integer AS
  DECLARE @mCode char(20) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,20)
  DECLARE @mLfdNr integer = dbo.itcfunc_KeyToken_integer(@xKey,@xLen,21,8)

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

  IF @xLen = 28 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key00) WHERE 
      Code = @mCode AND 
      LfdNr = @mLfdNr
      ORDER BY Code desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen = 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key00) WHERE 
      Code = @mCode
      ORDER BY Code desc,LfdNr desc,RecId desc
    RETURN
  END

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

  IF @xLen < 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key00) WHERE 
      SUBSTRING(Code,1,LEN(@mCode)) = @mCode
      ORDER BY Code desc,LfdNr desc,RecId desc
    RETURN
  END


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


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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_KladdenZeile_Key01')
  DROP PROCEDURE [dbo].[up_First_KladdenZeile_Key01]
GO
CREATE PROCEDURE [dbo].[up_First_KladdenZeile_Key01] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) ORDER BY PrjNr,BelArt,BelNr,Code,LfdNr,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_KladdenZeile_Key01')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_KladdenZeile_Key01] AS EXECUTE [dbo].[up_First_KladdenZeile_Key01]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_KladdenZeile_Key01')
  DROP PROCEDURE [dbo].[up_Last_KladdenZeile_Key01]
GO
CREATE PROCEDURE [dbo].[up_Last_KladdenZeile_Key01] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) ORDER BY PrjNr desc,BelArt desc,BelNr desc,Code desc,LfdNr desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_KladdenZeile_Key01')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_KladdenZeile_Key01] AS EXECUTE [dbo].[up_Last_KladdenZeile_Key01]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_KladdenZeile_Key01')
  DROP PROCEDURE [dbo].[up_Next_KladdenZeile_Key01]
GO
CREATE PROCEDURE [dbo].[up_Next_KladdenZeile_Key01] @oldRecId integer AS
  DECLARE @oldPrjNr char(8)
  DECLARE @oldBelArt char(2)
  DECLARE @oldBelNr char(8)
  DECLARE @oldCode char(20)
  DECLARE @oldLfdNr integer
  SELECT @oldPrjNr = PrjNr, @oldBelArt = BelArt, @oldBelNr = BelNr, @oldCode = Code, @oldLfdNr = LfdNr FROM [dbo].[ut_KladdenZeile] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01)
    WHERE (PrjNr = @oldPrjNr) AND (BelArt = @oldBelArt) AND (BelNr = @oldBelNr) AND (Code = @oldCode) AND (LfdNr = @oldLfdNr) AND (RecId > @oldRecId)
    ORDER BY PrjNr,BelArt,BelNr,Code,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01)
    WHERE (PrjNr = @oldPrjNr) AND (BelArt = @oldBelArt) AND (BelNr = @oldBelNr) AND (Code = @oldCode) AND (LfdNr > @oldLfdNr)
    ORDER BY PrjNr,BelArt,BelNr,Code,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01)
    WHERE (PrjNr = @oldPrjNr) AND (BelArt = @oldBelArt) AND (BelNr = @oldBelNr) AND (Code > @oldCode)
    ORDER BY PrjNr,BelArt,BelNr,Code,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01)
    WHERE (PrjNr = @oldPrjNr) AND (BelArt = @oldBelArt) AND (BelNr > @oldBelNr)
    ORDER BY PrjNr,BelArt,BelNr,Code,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01)
    WHERE (PrjNr = @oldPrjNr) AND (BelArt > @oldBelArt)
    ORDER BY PrjNr,BelArt,BelNr,Code,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01)
    WHERE (PrjNr > @oldPrjNr)
    ORDER BY PrjNr,BelArt,BelNr,Code,LfdNr,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_KladdenZeile_Key01')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_KladdenZeile_Key01] @oldRecId integer AS EXECUTE [dbo].[up_Next_KladdenZeile_Key01] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_KladdenZeile_Key01')
  DROP PROCEDURE [dbo].[up_Prior_KladdenZeile_Key01]
GO
CREATE PROCEDURE [dbo].[up_Prior_KladdenZeile_Key01] @oldRecId integer AS
  DECLARE @oldPrjNr char(8)
  DECLARE @oldBelArt char(2)
  DECLARE @oldBelNr char(8)
  DECLARE @oldCode char(20)
  DECLARE @oldLfdNr integer
  SELECT @oldPrjNr = PrjNr, @oldBelArt = BelArt, @oldBelNr = BelNr, @oldCode = Code, @oldLfdNr = LfdNr FROM [dbo].[ut_KladdenZeile] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01)
    WHERE (PrjNr = @oldPrjNr) AND (BelArt = @oldBelArt) AND (BelNr = @oldBelNr) AND (Code = @oldCode) AND (LfdNr = @oldLfdNr) AND (RecId < @oldRecId)
    ORDER BY PrjNr desc,BelArt desc,BelNr desc,Code desc,LfdNr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01)
    WHERE (PrjNr = @oldPrjNr) AND (BelArt = @oldBelArt) AND (BelNr = @oldBelNr) AND (Code = @oldCode) AND (LfdNr < @oldLfdNr)
    ORDER BY PrjNr desc,BelArt desc,BelNr desc,Code desc,LfdNr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01)
    WHERE (PrjNr = @oldPrjNr) AND (BelArt = @oldBelArt) AND (BelNr = @oldBelNr) AND (Code < @oldCode)
    ORDER BY PrjNr desc,BelArt desc,BelNr desc,Code desc,LfdNr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01)
    WHERE (PrjNr = @oldPrjNr) AND (BelArt = @oldBelArt) AND (BelNr < @oldBelNr)
    ORDER BY PrjNr desc,BelArt desc,BelNr desc,Code desc,LfdNr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01)
    WHERE (PrjNr = @oldPrjNr) AND (BelArt < @oldBelArt)
    ORDER BY PrjNr desc,BelArt desc,BelNr desc,Code desc,LfdNr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01)
    WHERE (PrjNr < @oldPrjNr)
    ORDER BY PrjNr desc,BelArt desc,BelNr desc,Code desc,LfdNr 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_KladdenZeile_Key01')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_KladdenZeile_Key01] @oldRecId integer AS EXECUTE [dbo].[up_Prior_KladdenZeile_Key01] @oldRecId')
GO

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

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_KladdenZeile_Key01')
  DROP PROCEDURE [dbo].[up_FindF_KladdenZeile_Key01]
GO
CREATE PROCEDURE [dbo].[up_FindF_KladdenZeile_Key01] @xKey char(250), @xLen integer AS
  DECLARE @mPrjNr char(8) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,8)
  DECLARE @mBelArt char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,9,2)
  DECLARE @mBelNr char(8) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,11,8)
  DECLARE @mCode char(20) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,19,20)
  DECLARE @mLfdNr integer = dbo.itcfunc_KeyToken_integer(@xKey,@xLen,39,8)

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

  IF @xLen = 46 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) WHERE 
      PrjNr = @mPrjNr AND 
      BelArt = @mBelArt AND 
      BelNr = @mBelNr AND 
      Code = @mCode AND 
      LfdNr = @mLfdNr
      ORDER BY PrjNr,BelArt,BelNr,Code,LfdNr,RecId
    RETURN
  END

  IF @xLen = 38 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) WHERE 
      PrjNr = @mPrjNr AND 
      BelArt = @mBelArt AND 
      BelNr = @mBelNr AND 
      Code = @mCode
      ORDER BY PrjNr,BelArt,BelNr,Code,LfdNr,RecId
    RETURN
  END

  IF @xLen = 18 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) WHERE 
      PrjNr = @mPrjNr AND 
      BelArt = @mBelArt AND 
      BelNr = @mBelNr
      ORDER BY PrjNr,BelArt,BelNr,Code,LfdNr,RecId
    RETURN
  END

  IF @xLen = 10 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) WHERE 
      PrjNr = @mPrjNr AND 
      BelArt = @mBelArt
      ORDER BY PrjNr,BelArt,BelNr,Code,LfdNr,RecId
    RETURN
  END

  IF @xLen = 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) WHERE 
      PrjNr = @mPrjNr
      ORDER BY PrjNr,BelArt,BelNr,Code,LfdNr,RecId
    RETURN
  END

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

  IF @xLen < 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) WHERE 
      PrjNr >= @mPrjNr AND SUBSTRING(PrjNr,1,LEN(@mPrjNr)) = @mPrjNr
      ORDER BY PrjNr,BelArt,BelNr,Code,LfdNr,RecId
    RETURN
  END

  IF @xLen < 10 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) WHERE 
      PrjNr = @mPrjNr AND 
      BelArt >= @mBelArt AND SUBSTRING(BelArt,1,LEN(@mBelArt)) = @mBelArt
      ORDER BY PrjNr,BelArt,BelNr,Code,LfdNr,RecId
    RETURN
  END

  IF @xLen < 18 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) WHERE 
      PrjNr = @mPrjNr AND 
      BelArt = @mBelArt AND 
      BelNr >= @mBelNr AND SUBSTRING(BelNr,1,LEN(@mBelNr)) = @mBelNr
      ORDER BY PrjNr,BelArt,BelNr,Code,LfdNr,RecId
    RETURN
  END

  IF @xLen < 38 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) WHERE 
      PrjNr = @mPrjNr AND 
      BelArt = @mBelArt AND 
      BelNr = @mBelNr AND 
      Code >= @mCode AND SUBSTRING(Code,1,LEN(@mCode)) = @mCode
      ORDER BY PrjNr,BelArt,BelNr,Code,LfdNr,RecId
    RETURN
  END


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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_KladdenZeile_Key01')
  DROP PROCEDURE [dbo].[up_FindL_KladdenZeile_Key01]
GO
CREATE PROCEDURE [dbo].[up_FindL_KladdenZeile_Key01] @xKey char(250), @xLen integer AS
  DECLARE @mPrjNr char(8) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,8)
  DECLARE @mBelArt char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,9,2)
  DECLARE @mBelNr char(8) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,11,8)
  DECLARE @mCode char(20) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,19,20)
  DECLARE @mLfdNr integer = dbo.itcfunc_KeyToken_integer(@xKey,@xLen,39,8)

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

  IF @xLen = 46 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) WHERE 
      PrjNr = @mPrjNr AND 
      BelArt = @mBelArt AND 
      BelNr = @mBelNr AND 
      Code = @mCode AND 
      LfdNr = @mLfdNr
      ORDER BY PrjNr desc,BelArt desc,BelNr desc,Code desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen = 38 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) WHERE 
      PrjNr = @mPrjNr AND 
      BelArt = @mBelArt AND 
      BelNr = @mBelNr AND 
      Code = @mCode
      ORDER BY PrjNr desc,BelArt desc,BelNr desc,Code desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen = 18 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) WHERE 
      PrjNr = @mPrjNr AND 
      BelArt = @mBelArt AND 
      BelNr = @mBelNr
      ORDER BY PrjNr desc,BelArt desc,BelNr desc,Code desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen = 10 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) WHERE 
      PrjNr = @mPrjNr AND 
      BelArt = @mBelArt
      ORDER BY PrjNr desc,BelArt desc,BelNr desc,Code desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen = 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) WHERE 
      PrjNr = @mPrjNr
      ORDER BY PrjNr desc,BelArt desc,BelNr desc,Code desc,LfdNr desc,RecId desc
    RETURN
  END

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

  IF @xLen < 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) WHERE 
      SUBSTRING(PrjNr,1,LEN(@mPrjNr)) = @mPrjNr
      ORDER BY PrjNr desc,BelArt desc,BelNr desc,Code desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen < 10 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) WHERE 
      PrjNr = @mPrjNr AND 
      SUBSTRING(BelArt,1,LEN(@mBelArt)) = @mBelArt
      ORDER BY PrjNr desc,BelArt desc,BelNr desc,Code desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen < 18 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) WHERE 
      PrjNr = @mPrjNr AND 
      BelArt = @mBelArt AND 
      SUBSTRING(BelNr,1,LEN(@mBelNr)) = @mBelNr
      ORDER BY PrjNr desc,BelArt desc,BelNr desc,Code desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen < 38 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key01) WHERE 
      PrjNr = @mPrjNr AND 
      BelArt = @mBelArt AND 
      BelNr = @mBelNr AND 
      SUBSTRING(Code,1,LEN(@mCode)) = @mCode
      ORDER BY PrjNr desc,BelArt desc,BelNr desc,Code desc,LfdNr desc,RecId desc
    RETURN
  END


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


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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_KladdenZeile_Key02')
  DROP PROCEDURE [dbo].[up_First_KladdenZeile_Key02]
GO
CREATE PROCEDURE [dbo].[up_First_KladdenZeile_Key02] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02) ORDER BY TransNo,Code,LfdNr,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_KladdenZeile_Key02')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_KladdenZeile_Key02] AS EXECUTE [dbo].[up_First_KladdenZeile_Key02]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_KladdenZeile_Key02')
  DROP PROCEDURE [dbo].[up_Last_KladdenZeile_Key02]
GO
CREATE PROCEDURE [dbo].[up_Last_KladdenZeile_Key02] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02) ORDER BY TransNo desc,Code desc,LfdNr desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_KladdenZeile_Key02')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_KladdenZeile_Key02] AS EXECUTE [dbo].[up_Last_KladdenZeile_Key02]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_KladdenZeile_Key02')
  DROP PROCEDURE [dbo].[up_Next_KladdenZeile_Key02]
GO
CREATE PROCEDURE [dbo].[up_Next_KladdenZeile_Key02] @oldRecId integer AS
  DECLARE @oldTransNo char(8)
  DECLARE @oldCode char(20)
  DECLARE @oldLfdNr integer
  SELECT @oldTransNo = TransNo, @oldCode = Code, @oldLfdNr = LfdNr FROM [dbo].[ut_KladdenZeile] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02)
    WHERE (TransNo = @oldTransNo) AND (Code = @oldCode) AND (LfdNr = @oldLfdNr) AND (RecId > @oldRecId)
    ORDER BY TransNo,Code,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02)
    WHERE (TransNo = @oldTransNo) AND (Code = @oldCode) AND (LfdNr > @oldLfdNr)
    ORDER BY TransNo,Code,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02)
    WHERE (TransNo = @oldTransNo) AND (Code > @oldCode)
    ORDER BY TransNo,Code,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02)
    WHERE (TransNo > @oldTransNo)
    ORDER BY TransNo,Code,LfdNr,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_KladdenZeile_Key02')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_KladdenZeile_Key02] @oldRecId integer AS EXECUTE [dbo].[up_Next_KladdenZeile_Key02] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_KladdenZeile_Key02')
  DROP PROCEDURE [dbo].[up_Prior_KladdenZeile_Key02]
GO
CREATE PROCEDURE [dbo].[up_Prior_KladdenZeile_Key02] @oldRecId integer AS
  DECLARE @oldTransNo char(8)
  DECLARE @oldCode char(20)
  DECLARE @oldLfdNr integer
  SELECT @oldTransNo = TransNo, @oldCode = Code, @oldLfdNr = LfdNr FROM [dbo].[ut_KladdenZeile] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02)
    WHERE (TransNo = @oldTransNo) AND (Code = @oldCode) AND (LfdNr = @oldLfdNr) AND (RecId < @oldRecId)
    ORDER BY TransNo desc,Code desc,LfdNr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02)
    WHERE (TransNo = @oldTransNo) AND (Code = @oldCode) AND (LfdNr < @oldLfdNr)
    ORDER BY TransNo desc,Code desc,LfdNr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02)
    WHERE (TransNo = @oldTransNo) AND (Code < @oldCode)
    ORDER BY TransNo desc,Code desc,LfdNr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02)
    WHERE (TransNo < @oldTransNo)
    ORDER BY TransNo desc,Code desc,LfdNr 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_KladdenZeile_Key02')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_KladdenZeile_Key02] @oldRecId integer AS EXECUTE [dbo].[up_Prior_KladdenZeile_Key02] @oldRecId')
GO

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

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_KladdenZeile_Key02')
  DROP PROCEDURE [dbo].[up_FindF_KladdenZeile_Key02]
GO
CREATE PROCEDURE [dbo].[up_FindF_KladdenZeile_Key02] @xKey char(250), @xLen integer AS
  DECLARE @mTransNo char(8) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,8)
  DECLARE @mCode char(20) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,9,20)
  DECLARE @mLfdNr integer = dbo.itcfunc_KeyToken_integer(@xKey,@xLen,29,8)

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

  IF @xLen = 36 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02) WHERE 
      TransNo = @mTransNo AND 
      Code = @mCode AND 
      LfdNr = @mLfdNr
      ORDER BY TransNo,Code,LfdNr,RecId
    RETURN
  END

  IF @xLen = 28 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02) WHERE 
      TransNo = @mTransNo AND 
      Code = @mCode
      ORDER BY TransNo,Code,LfdNr,RecId
    RETURN
  END

  IF @xLen = 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02) WHERE 
      TransNo = @mTransNo
      ORDER BY TransNo,Code,LfdNr,RecId
    RETURN
  END

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

  IF @xLen < 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02) WHERE 
      TransNo >= @mTransNo AND SUBSTRING(TransNo,1,LEN(@mTransNo)) = @mTransNo
      ORDER BY TransNo,Code,LfdNr,RecId
    RETURN
  END

  IF @xLen < 28 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02) WHERE 
      TransNo = @mTransNo AND 
      Code >= @mCode AND SUBSTRING(Code,1,LEN(@mCode)) = @mCode
      ORDER BY TransNo,Code,LfdNr,RecId
    RETURN
  END


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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_KladdenZeile_Key02')
  DROP PROCEDURE [dbo].[up_FindL_KladdenZeile_Key02]
GO
CREATE PROCEDURE [dbo].[up_FindL_KladdenZeile_Key02] @xKey char(250), @xLen integer AS
  DECLARE @mTransNo char(8) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,8)
  DECLARE @mCode char(20) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,9,20)
  DECLARE @mLfdNr integer = dbo.itcfunc_KeyToken_integer(@xKey,@xLen,29,8)

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

  IF @xLen = 36 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02) WHERE 
      TransNo = @mTransNo AND 
      Code = @mCode AND 
      LfdNr = @mLfdNr
      ORDER BY TransNo desc,Code desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen = 28 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02) WHERE 
      TransNo = @mTransNo AND 
      Code = @mCode
      ORDER BY TransNo desc,Code desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen = 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02) WHERE 
      TransNo = @mTransNo
      ORDER BY TransNo desc,Code desc,LfdNr desc,RecId desc
    RETURN
  END

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

  IF @xLen < 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02) WHERE 
      SUBSTRING(TransNo,1,LEN(@mTransNo)) = @mTransNo
      ORDER BY TransNo desc,Code desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen < 28 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key02) WHERE 
      TransNo = @mTransNo AND 
      SUBSTRING(Code,1,LEN(@mCode)) = @mCode
      ORDER BY TransNo desc,Code desc,LfdNr desc,RecId desc
    RETURN
  END


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


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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_KladdenZeile_Key03')
  DROP PROCEDURE [dbo].[up_First_KladdenZeile_Key03]
GO
CREATE PROCEDURE [dbo].[up_First_KladdenZeile_Key03] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key03) ORDER BY VkzExport,Code,LfdNr,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_KladdenZeile_Key03')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_KladdenZeile_Key03] AS EXECUTE [dbo].[up_First_KladdenZeile_Key03]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_KladdenZeile_Key03')
  DROP PROCEDURE [dbo].[up_Last_KladdenZeile_Key03]
GO
CREATE PROCEDURE [dbo].[up_Last_KladdenZeile_Key03] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key03) ORDER BY VkzExport desc,Code desc,LfdNr desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_KladdenZeile_Key03')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_KladdenZeile_Key03] AS EXECUTE [dbo].[up_Last_KladdenZeile_Key03]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_KladdenZeile_Key03')
  DROP PROCEDURE [dbo].[up_Next_KladdenZeile_Key03]
GO
CREATE PROCEDURE [dbo].[up_Next_KladdenZeile_Key03] @oldRecId integer AS
  DECLARE @oldVkzExport integer
  DECLARE @oldCode char(20)
  DECLARE @oldLfdNr integer
  SELECT @oldVkzExport = VkzExport, @oldCode = Code, @oldLfdNr = LfdNr FROM [dbo].[ut_KladdenZeile] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key03)
    WHERE (VkzExport = @oldVkzExport) AND (Code = @oldCode) AND (LfdNr = @oldLfdNr) AND (RecId > @oldRecId)
    ORDER BY VkzExport,Code,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key03)
    WHERE (VkzExport = @oldVkzExport) AND (Code = @oldCode) AND (LfdNr > @oldLfdNr)
    ORDER BY VkzExport,Code,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key03)
    WHERE (VkzExport = @oldVkzExport) AND (Code > @oldCode)
    ORDER BY VkzExport,Code,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key03)
    WHERE (VkzExport > @oldVkzExport)
    ORDER BY VkzExport,Code,LfdNr,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_KladdenZeile_Key03')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_KladdenZeile_Key03] @oldRecId integer AS EXECUTE [dbo].[up_Next_KladdenZeile_Key03] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_KladdenZeile_Key03')
  DROP PROCEDURE [dbo].[up_Prior_KladdenZeile_Key03]
GO
CREATE PROCEDURE [dbo].[up_Prior_KladdenZeile_Key03] @oldRecId integer AS
  DECLARE @oldVkzExport integer
  DECLARE @oldCode char(20)
  DECLARE @oldLfdNr integer
  SELECT @oldVkzExport = VkzExport, @oldCode = Code, @oldLfdNr = LfdNr FROM [dbo].[ut_KladdenZeile] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key03)
    WHERE (VkzExport = @oldVkzExport) AND (Code = @oldCode) AND (LfdNr = @oldLfdNr) AND (RecId < @oldRecId)
    ORDER BY VkzExport desc,Code desc,LfdNr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key03)
    WHERE (VkzExport = @oldVkzExport) AND (Code = @oldCode) AND (LfdNr < @oldLfdNr)
    ORDER BY VkzExport desc,Code desc,LfdNr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key03)
    WHERE (VkzExport = @oldVkzExport) AND (Code < @oldCode)
    ORDER BY VkzExport desc,Code desc,LfdNr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key03)
    WHERE (VkzExport < @oldVkzExport)
    ORDER BY VkzExport desc,Code desc,LfdNr 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_KladdenZeile_Key03')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_KladdenZeile_Key03] @oldRecId integer AS EXECUTE [dbo].[up_Prior_KladdenZeile_Key03] @oldRecId')
GO

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

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_KladdenZeile_Key03')
  DROP PROCEDURE [dbo].[up_FindF_KladdenZeile_Key03]
GO
CREATE PROCEDURE [dbo].[up_FindF_KladdenZeile_Key03] @xKey char(250), @xLen integer AS
  DECLARE @mVkzExport integer = dbo.itcfunc_KeyToken_integer(@xKey,@xLen,1,1)
  DECLARE @mCode char(20) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,2,20)
  DECLARE @mLfdNr integer = dbo.itcfunc_KeyToken_integer(@xKey,@xLen,22,8)

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

  IF @xLen = 29 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key03) WHERE 
      VkzExport = @mVkzExport AND 
      Code = @mCode AND 
      LfdNr = @mLfdNr
      ORDER BY VkzExport,Code,LfdNr,RecId
    RETURN
  END

  IF @xLen = 21 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key03) WHERE 
      VkzExport = @mVkzExport AND 
      Code = @mCode
      ORDER BY VkzExport,Code,LfdNr,RecId
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key03) WHERE 
      VkzExport = @mVkzExport
      ORDER BY VkzExport,Code,LfdNr,RecId
    RETURN
  END

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


  IF @xLen < 21 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key03) WHERE 
      VkzExport = @mVkzExport AND 
      Code >= @mCode AND SUBSTRING(Code,1,LEN(@mCode)) = @mCode
      ORDER BY VkzExport,Code,LfdNr,RecId
    RETURN
  END


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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_KladdenZeile_Key03')
  DROP PROCEDURE [dbo].[up_FindL_KladdenZeile_Key03]
GO
CREATE PROCEDURE [dbo].[up_FindL_KladdenZeile_Key03] @xKey char(250), @xLen integer AS
  DECLARE @mVkzExport integer = dbo.itcfunc_KeyToken_integer(@xKey,@xLen,1,1)
  DECLARE @mCode char(20) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,2,20)
  DECLARE @mLfdNr integer = dbo.itcfunc_KeyToken_integer(@xKey,@xLen,22,8)

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

  IF @xLen = 29 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key03) WHERE 
      VkzExport = @mVkzExport AND 
      Code = @mCode AND 
      LfdNr = @mLfdNr
      ORDER BY VkzExport desc,Code desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen = 21 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key03) WHERE 
      VkzExport = @mVkzExport AND 
      Code = @mCode
      ORDER BY VkzExport desc,Code desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key03) WHERE 
      VkzExport = @mVkzExport
      ORDER BY VkzExport desc,Code desc,LfdNr desc,RecId desc
    RETURN
  END

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


  IF @xLen < 21 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key03) WHERE 
      VkzExport = @mVkzExport AND 
      SUBSTRING(Code,1,LEN(@mCode)) = @mCode
      ORDER BY VkzExport desc,Code desc,LfdNr desc,RecId desc
    RETURN
  END


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


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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_KladdenZeile_Key04')
  DROP PROCEDURE [dbo].[up_First_KladdenZeile_Key04]
GO
CREATE PROCEDURE [dbo].[up_First_KladdenZeile_Key04] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key04) WHERE (VkzExport = 1) OR (TransNo = '') ORDER BY Code,LfdNr,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_KladdenZeile_Key04')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_KladdenZeile_Key04] AS EXECUTE [dbo].[up_First_KladdenZeile_Key04]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_KladdenZeile_Key04')
  DROP PROCEDURE [dbo].[up_Last_KladdenZeile_Key04]
GO
CREATE PROCEDURE [dbo].[up_Last_KladdenZeile_Key04] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key04) WHERE (VkzExport = 1) OR (TransNo = '') ORDER BY Code desc,LfdNr desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_KladdenZeile_Key04')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_KladdenZeile_Key04] AS EXECUTE [dbo].[up_Last_KladdenZeile_Key04]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_KladdenZeile_Key04')
  DROP PROCEDURE [dbo].[up_Next_KladdenZeile_Key04]
GO
CREATE PROCEDURE [dbo].[up_Next_KladdenZeile_Key04] @oldRecId integer AS
  DECLARE @oldCode char(20)
  DECLARE @oldLfdNr integer
  SELECT @oldCode = Code, @oldLfdNr = LfdNr FROM [dbo].[ut_KladdenZeile] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key04)
    WHERE ((Code = @oldCode) AND (LfdNr = @oldLfdNr) AND (RecId > @oldRecId)) AND ((VkzExport = 1) OR (TransNo = ''))
    ORDER BY Code,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key04)
    WHERE ((Code = @oldCode) AND (LfdNr > @oldLfdNr)) AND ((VkzExport = 1) OR (TransNo = ''))
    ORDER BY Code,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key04)
    WHERE ((Code > @oldCode)) AND ((VkzExport = 1) OR (TransNo = ''))
    ORDER BY Code,LfdNr,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_KladdenZeile_Key04')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_KladdenZeile_Key04] @oldRecId integer AS EXECUTE [dbo].[up_Next_KladdenZeile_Key04] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_KladdenZeile_Key04')
  DROP PROCEDURE [dbo].[up_Prior_KladdenZeile_Key04]
GO
CREATE PROCEDURE [dbo].[up_Prior_KladdenZeile_Key04] @oldRecId integer AS
  DECLARE @oldCode char(20)
  DECLARE @oldLfdNr integer
  SELECT @oldCode = Code, @oldLfdNr = LfdNr FROM [dbo].[ut_KladdenZeile] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key04)
    WHERE ((Code = @oldCode) AND (LfdNr = @oldLfdNr) AND (RecId < @oldRecId)) AND ((VkzExport = 1) OR (TransNo = ''))
    ORDER BY Code desc,LfdNr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key04)
    WHERE ((Code = @oldCode) AND (LfdNr < @oldLfdNr)) AND ((VkzExport = 1) OR (TransNo = ''))
    ORDER BY Code desc,LfdNr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key04)
    WHERE ((Code < @oldCode)) AND ((VkzExport = 1) OR (TransNo = ''))
    ORDER BY Code desc,LfdNr 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_KladdenZeile_Key04')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_KladdenZeile_Key04] @oldRecId integer AS EXECUTE [dbo].[up_Prior_KladdenZeile_Key04] @oldRecId')
GO

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

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_KladdenZeile_Key04')
  DROP PROCEDURE [dbo].[up_FindF_KladdenZeile_Key04]
GO
CREATE PROCEDURE [dbo].[up_FindF_KladdenZeile_Key04] @xKey char(250), @xLen integer AS
  DECLARE @mCode char(20) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,20)
  DECLARE @mLfdNr integer = dbo.itcfunc_KeyToken_integer(@xKey,@xLen,21,8)

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

  IF @xLen = 28 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key04) WHERE 
      Code = @mCode AND 
      LfdNr = @mLfdNr
      AND ((VkzExport = 1) OR (TransNo = ''))
      ORDER BY Code,LfdNr,RecId
    RETURN
  END

  IF @xLen = 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key04) WHERE 
      Code = @mCode
      AND ((VkzExport = 1) OR (TransNo = ''))
      ORDER BY Code,LfdNr,RecId
    RETURN
  END

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

  IF @xLen < 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key04) WHERE 
      Code >= @mCode AND SUBSTRING(Code,1,LEN(@mCode)) = @mCode
      AND ((VkzExport = 1) OR (TransNo = ''))
      ORDER BY Code,LfdNr,RecId
    RETURN
  END


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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_KladdenZeile_Key04')
  DROP PROCEDURE [dbo].[up_FindL_KladdenZeile_Key04]
GO
CREATE PROCEDURE [dbo].[up_FindL_KladdenZeile_Key04] @xKey char(250), @xLen integer AS
  DECLARE @mCode char(20) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,20)
  DECLARE @mLfdNr integer = dbo.itcfunc_KeyToken_integer(@xKey,@xLen,21,8)

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

  IF @xLen = 28 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key04) WHERE 
      Code = @mCode AND 
      LfdNr = @mLfdNr
      AND ((VkzExport = 1) OR (TransNo = ''))
      ORDER BY Code desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen = 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key04) WHERE 
      Code = @mCode
      AND ((VkzExport = 1) OR (TransNo = ''))
      ORDER BY Code desc,LfdNr desc,RecId desc
    RETURN
  END

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

  IF @xLen < 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_KladdenZeile] WITH(INDEX = Ik_Key04) WHERE 
      SUBSTRING(Code,1,LEN(@mCode)) = @mCode
      AND ((VkzExport = 1) OR (TransNo = ''))
      ORDER BY Code desc,LfdNr desc,RecId desc
    RETURN
  END


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


