-- 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_ResPlandaten')
BEGIN
CREATE TABLE [dbo].[ut_ResPlandaten]
(
  [RecId]  integer NOT NULL
 ,[LfdNr] char(8) DEFAULT ''
 ,[ResTyp] char(2) DEFAULT ''
 ,[ResNr] char(4) DEFAULT ''
 ,[Jahr] char(4) DEFAULT ''
 ,[Monat] char(2) DEFAULT ''
 ,[Tag] char(2) DEFAULT ''
 ,[SS] char(2) DEFAULT ''
 ,[SM] char(2) DEFAULT ''
 ,[ES] char(2) DEFAULT ''
 ,[EM] char(2) DEFAULT ''
 ,[PrjNr] char(8) DEFAULT ''
 ,[BelArt] char(2) DEFAULT ''
 ,[BelegNr] char(8) DEFAULT ''
 ,[Farbe] char(10) DEFAULT ''
 ,[KurzBezeichnung] char(42) DEFAULT ''
 ,[Uebernommen] char(1) DEFAULT ''
 ,[Serie] integer DEFAULT 0
 ,[ZuVerbuchen] tinyint DEFAULT 0
 ,[AbwDauer] float DEFAULT 0
 ,[ExchgDrop] tinyint DEFAULT 0
 ,[ExpGuid] char(40) DEFAULT ''
 ,[ExpStamp] char(16) DEFAULT ''
  CONSTRAINT [Pk_ResPlandaten]  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_ResPlandaten_KeyDel')
  DROP TRIGGER [dbo].[tr_ResPlandaten_KeyDel]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'TR'   and [name] = 'tr_ResPlandaten_KeyUpd')
  DROP TRIGGER [dbo].[tr_ResPlandaten_KeyUpd]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'U' and [name] = 'ut_ResPlandaten_Keys')
  DROP TABLE [dbo].[ut_ResPlandaten_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_ResPlandaten' and sc.[name] = 'LfdNr')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [LfdNr] 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_ResPlandaten' and sc.[name] = 'ResTyp')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [ResTyp] 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_ResPlandaten' and sc.[name] = 'ResNr')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [ResNr] char(4) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Jahr')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [Jahr] char(4) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Monat')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [Monat] 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_ResPlandaten' and sc.[name] = 'Tag')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [Tag] 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_ResPlandaten' and sc.[name] = 'SS')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [SS] 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_ResPlandaten' and sc.[name] = 'SM')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [SM] 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_ResPlandaten' and sc.[name] = 'ES')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [ES] 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_ResPlandaten' and sc.[name] = 'EM')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [EM] 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_ResPlandaten' and sc.[name] = 'PrjNr')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] 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_ResPlandaten' and sc.[name] = 'BelArt')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] 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_ResPlandaten' and sc.[name] = 'BelegNr')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [BelegNr] 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_ResPlandaten' and sc.[name] = 'Farbe')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [Farbe] 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_ResPlandaten' and sc.[name] = 'KurzBezeichnung')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [KurzBezeichnung] char(42) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Uebernommen')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [Uebernommen] 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_ResPlandaten' and sc.[name] = 'Serie')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [Serie] 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_ResPlandaten' and sc.[name] = 'ZuVerbuchen')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [ZuVerbuchen] 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_ResPlandaten' and sc.[name] = 'AbwDauer')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [AbwDauer] 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_ResPlandaten' and sc.[name] = 'ExchgDrop')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [ExchgDrop] 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_ResPlandaten' and sc.[name] = 'ExpGuid')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [ExpGuid] 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_ResPlandaten' and sc.[name] = 'ExpStamp')
BEGIN
  ALTER TABLE [dbo].[ut_ResPlandaten] ADD [ExpStamp] char(16) NOT NULL DEFAULT ''
END
GO

-- check lengths of text fields
IF (8 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'LfdNr'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'LfdNr'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [LfdNr] char(8)
END
GO
IF (8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'LfdNr'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'LfdNr'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [LfdNr] 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_ResPlandaten' and sc.[name] = 'ResTyp'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'ResTyp'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [ResTyp] char(2)
END
GO
IF (2 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'ResTyp'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'ResTyp'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [ResTyp] varchar(2)
END
GO
IF (4 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'ResNr'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'ResNr'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [ResNr] char(4)
END
GO
IF (4 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'ResNr'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'ResNr'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [ResNr] varchar(4)
END
GO
IF (4 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Jahr'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Jahr'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [Jahr] char(4)
END
GO
IF (4 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Jahr'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Jahr'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [Jahr] varchar(4)
END
GO
IF (2 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Monat'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Monat'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [Monat] char(2)
END
GO
IF (2 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Monat'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Monat'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [Monat] varchar(2)
END
GO
IF (2 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Tag'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Tag'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [Tag] char(2)
END
GO
IF (2 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Tag'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Tag'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [Tag] varchar(2)
END
GO
IF (2 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'SS'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'SS'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [SS] char(2)
END
GO
IF (2 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'SS'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'SS'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [SS] varchar(2)
END
GO
IF (2 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'SM'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'SM'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [SM] char(2)
END
GO
IF (2 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'SM'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'SM'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [SM] varchar(2)
END
GO
IF (2 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'ES'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'ES'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [ES] char(2)
END
GO
IF (2 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'ES'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'ES'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [ES] varchar(2)
END
GO
IF (2 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'EM'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'EM'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [EM] char(2)
END
GO
IF (2 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'EM'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'EM'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [EM] 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_ResPlandaten' 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_ResPlandaten' and sc.[name] = 'PrjNr'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] 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_ResPlandaten' 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_ResPlandaten' and sc.[name] = 'PrjNr'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] 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_ResPlandaten' 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_ResPlandaten' and sc.[name] = 'BelArt'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] 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_ResPlandaten' 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_ResPlandaten' and sc.[name] = 'BelArt'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] 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_ResPlandaten' and sc.[name] = 'BelegNr'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'BelegNr'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [BelegNr] char(8)
END
GO
IF (8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'BelegNr'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'BelegNr'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [BelegNr] 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_ResPlandaten' and sc.[name] = 'Farbe'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Farbe'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [Farbe] char(10)
END
GO
IF (10 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Farbe'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Farbe'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [Farbe] varchar(10)
END
GO
IF (42 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'KurzBezeichnung'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'KurzBezeichnung'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [KurzBezeichnung] char(42)
END
GO
IF (42 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'KurzBezeichnung'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'KurzBezeichnung'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [KurzBezeichnung] varchar(42)
END
GO
IF (1 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Uebernommen'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Uebernommen'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [Uebernommen] char(1)
END
GO
IF (1 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Uebernommen'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'Uebernommen'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [Uebernommen] varchar(1)
END
GO
IF (40 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'ExpGuid'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'ExpGuid'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [ExpGuid] char(40)
END
GO
IF (40 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'ExpGuid'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'ExpGuid'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [ExpGuid] varchar(40)
END
GO
IF (16 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'ExpStamp'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'ExpStamp'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [ExpStamp] char(16)
END
GO
IF (16 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'ExpStamp'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_ResPlandaten' and sc.[name] = 'ExpStamp'))
BEGIN
   ALTER TABLE [dbo].[ut_ResPlandaten] ALTER COLUMN [ExpStamp] varchar(16)
END
GO

-- create dedicated view
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'ResPlandaten')
  DROP VIEW [dbo].[ResPlandaten]
GO
CREATE VIEW [dbo].[ResPlandaten] AS
  SELECT [RecId]
 ,[LfdNr]
 ,[ResTyp]
 ,[ResNr]
 ,[Jahr]
 ,[Monat]
 ,[Tag]
 ,[SS]
 ,[SM]
 ,[ES]
 ,[EM]
 ,[PrjNr]
 ,[BelArt]
 ,[BelegNr]
 ,[Farbe]
 ,[KurzBezeichnung]
 ,[Uebernommen]
 ,[Serie]
 ,[ZuVerbuchen]
 ,[AbwDauer]
 ,[ExchgDrop]
 ,[ExpGuid]
 ,[ExpStamp]
  FROM [dbo].[ut_ResPlandaten]
GO


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

-- select with classic client
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Sel_ResPlandaten')
  DROP PROCEDURE [dbo].[up_Sel_ResPlandaten]
GO
CREATE PROCEDURE [dbo].[up_Sel_ResPlandaten]  @mRI integer
AS
  SELECT
    [LfdNr]
   ,[ResTyp]
   ,[ResNr]
   ,[Jahr]
   ,[Monat]
   ,[Tag]
   ,[SS]
   ,[SM]
   ,[ES]
   ,[EM]
   ,[PrjNr]
   ,[BelArt]
   ,[BelegNr]
   ,[Farbe]
   ,[KurzBezeichnung]
   ,[Uebernommen]
   ,[Serie]
   ,[ZuVerbuchen]
   ,[AbwDauer]
   ,[ExchgDrop]
   ,[ExpGuid]
   ,[ExpStamp]
  FROM [dbo].[ut_ResPlandaten] WHERE RecId = @mRI
GO

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

-- insert with classic client
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Append_ResPlandaten')
  DROP PROCEDURE [dbo].[up_Append_ResPlandaten]
GO
CREATE PROCEDURE [dbo].[up_Append_ResPlandaten]
   @Param_LfdNr char(8)
  ,@Param_ResTyp char(2)
  ,@Param_ResNr char(4)
  ,@Param_Jahr char(4)
  ,@Param_Monat char(2)
  ,@Param_Tag char(2)
  ,@Param_SS char(2)
  ,@Param_SM char(2)
  ,@Param_ES char(2)
  ,@Param_EM char(2)
  ,@Param_PrjNr char(8)
  ,@Param_BelArt char(2)
  ,@Param_BelegNr char(8)
  ,@Param_Farbe char(10)
  ,@Param_KurzBezeichnung char(42)
  ,@Param_Uebernommen char(1)
  ,@Param_Serie integer
  ,@Param_ZuVerbuchen tinyint
  ,@Param_AbwDauer float
  ,@Param_ExchgDrop tinyint
  ,@Param_ExpGuid char(40)
  ,@Param_ExpStamp char(16)
AS
  DECLARE @NeueId integer
  DECLARE @m_CountDown integer = 10
  WHILE @m_CountDown > 0 BEGIN
    BEGIN TRANSACTION
    BEGIN TRY
      SET @NeueId = dbo.itcfunc_NextRecId_ResPlandaten()
      INSERT INTO [dbo].[ut_ResPlandaten] (RecId
        ,[LfdNr]
        ,[ResTyp]
        ,[ResNr]
        ,[Jahr]
        ,[Monat]
        ,[Tag]
        ,[SS]
        ,[SM]
        ,[ES]
        ,[EM]
        ,[PrjNr]
        ,[BelArt]
        ,[BelegNr]
        ,[Farbe]
        ,[KurzBezeichnung]
        ,[Uebernommen]
        ,[Serie]
        ,[ZuVerbuchen]
        ,[AbwDauer]
        ,[ExchgDrop]
        ,[ExpGuid]
        ,[ExpStamp]
          ) VALUES (@NeueId
        ,UPPER(RTRIM(@Param_LfdNr))
        ,UPPER(RTRIM(@Param_ResTyp))
        ,UPPER(RTRIM(@Param_ResNr))
        ,UPPER(RTRIM(@Param_Jahr))
        ,UPPER(RTRIM(@Param_Monat))
        ,UPPER(RTRIM(@Param_Tag))
        ,UPPER(RTRIM(@Param_SS))
        ,UPPER(RTRIM(@Param_SM))
        ,UPPER(RTRIM(@Param_ES))
        ,UPPER(RTRIM(@Param_EM))
        ,UPPER(RTRIM(@Param_PrjNr))
        ,UPPER(RTRIM(@Param_BelArt))
        ,UPPER(RTRIM(@Param_BelegNr))
        ,UPPER(RTRIM(@Param_Farbe))
        ,RTRIM(@Param_KurzBezeichnung)
        ,UPPER(RTRIM(@Param_Uebernommen))
        ,@Param_Serie
        ,@Param_ZuVerbuchen
        ,@Param_AbwDauer
        ,@Param_ExchgDrop
        ,RTRIM(@Param_ExpGuid)
        ,RTRIM(@Param_ExpStamp)
          )
      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_ResPlandaten')
  DROP PROCEDURE [dbo].[up_Add_ResPlandaten]
GO
CREATE PROCEDURE [dbo].[up_Add_ResPlandaten]
AS
  DECLARE @NeueId integer = dbo.itcfunc_NextRecId_ResPlandaten()
  INSERT INTO [dbo].[ut_ResPlandaten] (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_ResPlandaten')
  DROP PROCEDURE [dbo].[up_Upd_ResPlandaten]
GO
CREATE PROCEDURE [dbo].[up_Upd_ResPlandaten] @mRI integer
  ,@Param_LfdNr char(8)
  ,@Param_ResTyp char(2)
  ,@Param_ResNr char(4)
  ,@Param_Jahr char(4)
  ,@Param_Monat char(2)
  ,@Param_Tag char(2)
  ,@Param_SS char(2)
  ,@Param_SM char(2)
  ,@Param_ES char(2)
  ,@Param_EM char(2)
  ,@Param_PrjNr char(8)
  ,@Param_BelArt char(2)
  ,@Param_BelegNr char(8)
  ,@Param_Farbe char(10)
  ,@Param_KurzBezeichnung char(42)
  ,@Param_Uebernommen char(1)
  ,@Param_Serie integer
  ,@Param_ZuVerbuchen tinyint
  ,@Param_AbwDauer float
  ,@Param_ExchgDrop tinyint
  ,@Param_ExpGuid char(40)
  ,@Param_ExpStamp char(16)
AS
  UPDATE [dbo].[ut_ResPlandaten] SET
    [LfdNr] = UPPER(RTRIM(@Param_LfdNr))
   ,[ResTyp] = UPPER(RTRIM(@Param_ResTyp))
   ,[ResNr] = UPPER(RTRIM(@Param_ResNr))
   ,[Jahr] = UPPER(RTRIM(@Param_Jahr))
   ,[Monat] = UPPER(RTRIM(@Param_Monat))
   ,[Tag] = UPPER(RTRIM(@Param_Tag))
   ,[SS] = UPPER(RTRIM(@Param_SS))
   ,[SM] = UPPER(RTRIM(@Param_SM))
   ,[ES] = UPPER(RTRIM(@Param_ES))
   ,[EM] = UPPER(RTRIM(@Param_EM))
   ,[PrjNr] = UPPER(RTRIM(@Param_PrjNr))
   ,[BelArt] = UPPER(RTRIM(@Param_BelArt))
   ,[BelegNr] = UPPER(RTRIM(@Param_BelegNr))
   ,[Farbe] = UPPER(RTRIM(@Param_Farbe))
   ,[KurzBezeichnung] = RTRIM(@Param_KurzBezeichnung)
   ,[Uebernommen] = UPPER(RTRIM(@Param_Uebernommen))
   ,[Serie] = @Param_Serie
   ,[ZuVerbuchen] = @Param_ZuVerbuchen
   ,[AbwDauer] = @Param_AbwDauer
   ,[ExchgDrop] = @Param_ExchgDrop
   ,[ExpGuid] = RTRIM(@Param_ExpGuid)
   ,[ExpStamp] = RTRIM(@Param_ExpStamp)
  WHERE RecId = @mRI
GO

-- update with modern clients
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Upd2_ResPlandaten')
  DROP PROCEDURE [dbo].[up_Upd2_ResPlandaten]
GO
CREATE PROCEDURE [dbo].[up_Upd2_ResPlandaten] @Param_RecId varchar(20)
  ,@Param_LfdNr varchar(8)
  ,@Param_ResTyp varchar(2)
  ,@Param_ResNr varchar(4)
  ,@Param_Jahr varchar(4)
  ,@Param_Monat varchar(2)
  ,@Param_Tag varchar(2)
  ,@Param_SS varchar(2)
  ,@Param_SM varchar(2)
  ,@Param_ES varchar(2)
  ,@Param_EM varchar(2)
  ,@Param_PrjNr varchar(8)
  ,@Param_BelArt varchar(2)
  ,@Param_BelegNr varchar(8)
  ,@Param_Farbe varchar(10)
  ,@Param_KurzBezeichnung varchar(42)
  ,@Param_Uebernommen varchar(1)
  ,@Param_Serie varchar(8)
  ,@Param_ZuVerbuchen varchar(1)
  ,@Param_AbwDauer varchar(5)
  ,@Param_ExchgDrop varchar(1)
  ,@Param_ExpGuid varchar(40)
  ,@Param_ExpStamp varchar(16)
AS
  DECLARE @mRI integer = dbo.itcfunc_integer(@Param_RecId)
  UPDATE [dbo].[ut_ResPlandaten] SET
    [LfdNr] = UPPER(RTRIM(@Param_LfdNr))
   ,[ResTyp] = UPPER(RTRIM(@Param_ResTyp))
   ,[ResNr] = UPPER(RTRIM(@Param_ResNr))
   ,[Jahr] = UPPER(RTRIM(@Param_Jahr))
   ,[Monat] = UPPER(RTRIM(@Param_Monat))
   ,[Tag] = UPPER(RTRIM(@Param_Tag))
   ,[SS] = UPPER(RTRIM(@Param_SS))
   ,[SM] = UPPER(RTRIM(@Param_SM))
   ,[ES] = UPPER(RTRIM(@Param_ES))
   ,[EM] = UPPER(RTRIM(@Param_EM))
   ,[PrjNr] = UPPER(RTRIM(@Param_PrjNr))
   ,[BelArt] = UPPER(RTRIM(@Param_BelArt))
   ,[BelegNr] = UPPER(RTRIM(@Param_BelegNr))
   ,[Farbe] = UPPER(RTRIM(@Param_Farbe))
   ,[KurzBezeichnung] = RTRIM(@Param_KurzBezeichnung)
   ,[Uebernommen] = UPPER(RTRIM(@Param_Uebernommen))
   ,[Serie] = dbo.itcfunc_integer(@Param_Serie)
   ,[ZuVerbuchen] = dbo.itcfunc_integer(@Param_ZuVerbuchen)
   ,[AbwDauer] = dbo.itcfunc_float(@Param_AbwDauer)
   ,[ExchgDrop] = dbo.itcfunc_integer(@Param_ExchgDrop)
   ,[ExpGuid] = RTRIM(@Param_ExpGuid)
   ,[ExpStamp] = RTRIM(@Param_ExpStamp)
  WHERE RecId = @mRI

  EXECUTE [dbo].[up_Sel2_ResPlandaten] @mRI
GO

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

-- text index
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_TrSel_ResPlandaten')
  DROP PROCEDURE [dbo].[up_TrSel_ResPlandaten]
GO
CREATE PROCEDURE [dbo].[up_TrSel_ResPlandaten]
AS
  SELECT RecId,
    LTRIM(RTRIM([LfdNr]))
   +LTRIM(RTRIM([ResTyp]))
   +LTRIM(RTRIM([ResNr]))
   +LTRIM(RTRIM([Jahr]))
   +LTRIM(RTRIM([Monat]))
   +LTRIM(RTRIM([Tag]))
   +LTRIM(RTRIM([SS]))
   +LTRIM(RTRIM([SM]))
   +LTRIM(RTRIM([ES]))
   +LTRIM(RTRIM([EM]))
   +LTRIM(RTRIM([PrjNr]))
   +LTRIM(RTRIM([BelArt]))
   +LTRIM(RTRIM([BelegNr]))
   +LTRIM(RTRIM([Farbe]))
   +LTRIM(RTRIM([KurzBezeichnung]))
   +LTRIM(RTRIM([Uebernommen]))
   +LTRIM(RTRIM([ExpGuid]))
   +LTRIM(RTRIM([ExpStamp]))
  AS Data FROM [dbo].[ut_ResPlandaten] WHERE RecId > 0 ORDER BY RecId
GO

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

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

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

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

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

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key0)
    WHERE (LfdNr > @oldLfdNr)
    ORDER BY 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_ResPlandaten_Key0')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_ResPlandaten_Key0] @oldRecId integer AS EXECUTE [dbo].[up_Next_ResPlandaten_Key0] @oldRecId')
GO

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

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key0)
    WHERE (LfdNr < @oldLfdNr)
    ORDER BY 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_ResPlandaten_Key0')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_ResPlandaten_Key0] @oldRecId integer AS EXECUTE [dbo].[up_Prior_ResPlandaten_Key0] @oldRecId')
GO

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

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

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

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

  IF @xLen = 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key0) WHERE 
      LfdNr = @mLfdNr
      ORDER BY 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_ResPlandaten] WITH(INDEX = Ik_Key0) WHERE 
      LfdNr >= @mLfdNr AND SUBSTRING(LfdNr,1,LEN(@mLfdNr)) = @mLfdNr
      ORDER BY LfdNr,RecId
    RETURN
  END

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

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

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

  IF @xLen = 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key0) WHERE 
      LfdNr = @mLfdNr
      ORDER BY 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_ResPlandaten] WITH(INDEX = Ik_Key0) WHERE 
      SUBSTRING(LfdNr,1,LEN(@mLfdNr)) = @mLfdNr
      ORDER BY LfdNr desc,RecId desc
    RETURN
  END

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


-- logical key  ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
IF 0 < (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_ResPlandaten' and so.[id] = si.[id] and si.[name] = 'Ik_Key1')
  DROP INDEX [Ik_Key1] ON [dbo].[ut_ResPlandaten]
GO
IF 0 = (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_ResPlandaten' and so.[id] = si.[id] and si.[name] = 'Ik_Key1')
  CREATE INDEX [Ik_Key1] ON [dbo].[ut_ResPlandaten] (ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId)
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_ResPlandaten_Key1')
  DROP PROCEDURE [dbo].[up_First_ResPlandaten_Key1]
GO
CREATE PROCEDURE [dbo].[up_First_ResPlandaten_Key1] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE (0 = ExchgDrop) ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_ResPlandaten_Key1')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_ResPlandaten_Key1] AS EXECUTE [dbo].[up_First_ResPlandaten_Key1]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_ResPlandaten_Key1')
  DROP PROCEDURE [dbo].[up_Last_ResPlandaten_Key1]
GO
CREATE PROCEDURE [dbo].[up_Last_ResPlandaten_Key1] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE (0 = ExchgDrop) ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_ResPlandaten_Key1')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_ResPlandaten_Key1] AS EXECUTE [dbo].[up_Last_ResPlandaten_Key1]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_ResPlandaten_Key1')
  DROP PROCEDURE [dbo].[up_Next_ResPlandaten_Key1]
GO
CREATE PROCEDURE [dbo].[up_Next_ResPlandaten_Key1] @oldRecId integer AS
  DECLARE @oldResTyp char(2)
  DECLARE @oldResNr char(4)
  DECLARE @oldJahr char(4)
  DECLARE @oldMonat char(2)
  DECLARE @oldTag char(2)
  DECLARE @oldSS char(2)
  DECLARE @oldSM char(2)
  SELECT @oldResTyp = ResTyp, @oldResNr = ResNr, @oldJahr = Jahr, @oldMonat = Monat, @oldTag = Tag, @oldSS = SS, @oldSM = SM FROM [dbo].[ut_ResPlandaten] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1)
    WHERE ((ResTyp = @oldResTyp) AND (ResNr = @oldResNr) AND (Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag = @oldTag) AND (SS = @oldSS) AND (SM = @oldSM) AND (RecId > @oldRecId)) AND ((0 = ExchgDrop))
    ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1)
    WHERE ((ResTyp = @oldResTyp) AND (ResNr = @oldResNr) AND (Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag = @oldTag) AND (SS = @oldSS) AND (SM > @oldSM)) AND ((0 = ExchgDrop))
    ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1)
    WHERE ((ResTyp = @oldResTyp) AND (ResNr = @oldResNr) AND (Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag = @oldTag) AND (SS > @oldSS)) AND ((0 = ExchgDrop))
    ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1)
    WHERE ((ResTyp = @oldResTyp) AND (ResNr = @oldResNr) AND (Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag > @oldTag)) AND ((0 = ExchgDrop))
    ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1)
    WHERE ((ResTyp = @oldResTyp) AND (ResNr = @oldResNr) AND (Jahr = @oldJahr) AND (Monat > @oldMonat)) AND ((0 = ExchgDrop))
    ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1)
    WHERE ((ResTyp = @oldResTyp) AND (ResNr = @oldResNr) AND (Jahr > @oldJahr)) AND ((0 = ExchgDrop))
    ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1)
    WHERE ((ResTyp = @oldResTyp) AND (ResNr > @oldResNr)) AND ((0 = ExchgDrop))
    ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1)
    WHERE ((ResTyp > @oldResTyp)) AND ((0 = ExchgDrop))
    ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,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_ResPlandaten_Key1')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_ResPlandaten_Key1] @oldRecId integer AS EXECUTE [dbo].[up_Next_ResPlandaten_Key1] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_ResPlandaten_Key1')
  DROP PROCEDURE [dbo].[up_Prior_ResPlandaten_Key1]
GO
CREATE PROCEDURE [dbo].[up_Prior_ResPlandaten_Key1] @oldRecId integer AS
  DECLARE @oldResTyp char(2)
  DECLARE @oldResNr char(4)
  DECLARE @oldJahr char(4)
  DECLARE @oldMonat char(2)
  DECLARE @oldTag char(2)
  DECLARE @oldSS char(2)
  DECLARE @oldSM char(2)
  SELECT @oldResTyp = ResTyp, @oldResNr = ResNr, @oldJahr = Jahr, @oldMonat = Monat, @oldTag = Tag, @oldSS = SS, @oldSM = SM FROM [dbo].[ut_ResPlandaten] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1)
    WHERE ((ResTyp = @oldResTyp) AND (ResNr = @oldResNr) AND (Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag = @oldTag) AND (SS = @oldSS) AND (SM = @oldSM) AND (RecId < @oldRecId)) AND ((0 = ExchgDrop))
    ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1)
    WHERE ((ResTyp = @oldResTyp) AND (ResNr = @oldResNr) AND (Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag = @oldTag) AND (SS = @oldSS) AND (SM < @oldSM)) AND ((0 = ExchgDrop))
    ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1)
    WHERE ((ResTyp = @oldResTyp) AND (ResNr = @oldResNr) AND (Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag = @oldTag) AND (SS < @oldSS)) AND ((0 = ExchgDrop))
    ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1)
    WHERE ((ResTyp = @oldResTyp) AND (ResNr = @oldResNr) AND (Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag < @oldTag)) AND ((0 = ExchgDrop))
    ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1)
    WHERE ((ResTyp = @oldResTyp) AND (ResNr = @oldResNr) AND (Jahr = @oldJahr) AND (Monat < @oldMonat)) AND ((0 = ExchgDrop))
    ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1)
    WHERE ((ResTyp = @oldResTyp) AND (ResNr = @oldResNr) AND (Jahr < @oldJahr)) AND ((0 = ExchgDrop))
    ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1)
    WHERE ((ResTyp = @oldResTyp) AND (ResNr < @oldResNr)) AND ((0 = ExchgDrop))
    ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1)
    WHERE ((ResTyp < @oldResTyp)) AND ((0 = ExchgDrop))
    ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM 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_ResPlandaten_Key1')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_ResPlandaten_Key1] @oldRecId integer AS EXECUTE [dbo].[up_Prior_ResPlandaten_Key1] @oldRecId')
GO

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

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_ResPlandaten_Key1')
  DROP PROCEDURE [dbo].[up_FindF_ResPlandaten_Key1]
GO
CREATE PROCEDURE [dbo].[up_FindF_ResPlandaten_Key1] @xKey char(250), @xLen integer AS
  DECLARE @mResTyp char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,2)
  DECLARE @mResNr char(4) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,3,4)
  DECLARE @mJahr char(4) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,7,4)
  DECLARE @mMonat char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,11,2)
  DECLARE @mTag char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,13,2)
  DECLARE @mSS char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,15,2)
  DECLARE @mSM char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,17,2)

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

  IF @xLen = 18 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SS = @mSS AND 
      SM = @mSM
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
    RETURN
  END

  IF @xLen = 16 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SS = @mSS
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
    RETURN
  END

  IF @xLen = 14 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
    RETURN
  END

  IF @xLen = 12 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      Jahr = @mJahr AND 
      Monat = @mMonat
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
    RETURN
  END

  IF @xLen = 10 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      Jahr = @mJahr
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
    RETURN
  END

  IF @xLen = 6 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
    RETURN
  END

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

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp >= @mResTyp AND SUBSTRING(ResTyp,1,LEN(@mResTyp)) = @mResTyp
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
    RETURN
  END

  IF @xLen < 6 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr >= @mResNr AND SUBSTRING(ResNr,1,LEN(@mResNr)) = @mResNr
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
    RETURN
  END

  IF @xLen < 10 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      Jahr >= @mJahr AND SUBSTRING(Jahr,1,LEN(@mJahr)) = @mJahr
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
    RETURN
  END

  IF @xLen < 12 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      Jahr = @mJahr AND 
      Monat >= @mMonat AND SUBSTRING(Monat,1,LEN(@mMonat)) = @mMonat
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
    RETURN
  END

  IF @xLen < 14 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag >= @mTag AND SUBSTRING(Tag,1,LEN(@mTag)) = @mTag
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
    RETURN
  END

  IF @xLen < 16 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SS >= @mSS AND SUBSTRING(SS,1,LEN(@mSS)) = @mSS
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
    RETURN
  END

  IF @xLen < 18 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SS = @mSS AND 
      SM >= @mSM AND SUBSTRING(SM,1,LEN(@mSM)) = @mSM
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp,ResNr,Jahr,Monat,Tag,SS,SM,RecId
    RETURN
  END

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_ResPlandaten_Key1')
  DROP PROCEDURE [dbo].[up_FindL_ResPlandaten_Key1]
GO
CREATE PROCEDURE [dbo].[up_FindL_ResPlandaten_Key1] @xKey char(250), @xLen integer AS
  DECLARE @mResTyp char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,2)
  DECLARE @mResNr char(4) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,3,4)
  DECLARE @mJahr char(4) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,7,4)
  DECLARE @mMonat char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,11,2)
  DECLARE @mTag char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,13,2)
  DECLARE @mSS char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,15,2)
  DECLARE @mSM char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,17,2)

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

  IF @xLen = 18 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SS = @mSS AND 
      SM = @mSM
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
    RETURN
  END

  IF @xLen = 16 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SS = @mSS
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
    RETURN
  END

  IF @xLen = 14 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
    RETURN
  END

  IF @xLen = 12 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      Jahr = @mJahr AND 
      Monat = @mMonat
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
    RETURN
  END

  IF @xLen = 10 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      Jahr = @mJahr
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
    RETURN
  END

  IF @xLen = 6 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
    RETURN
  END

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

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      SUBSTRING(ResTyp,1,LEN(@mResTyp)) = @mResTyp
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
    RETURN
  END

  IF @xLen < 6 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      SUBSTRING(ResNr,1,LEN(@mResNr)) = @mResNr
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
    RETURN
  END

  IF @xLen < 10 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      SUBSTRING(Jahr,1,LEN(@mJahr)) = @mJahr
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
    RETURN
  END

  IF @xLen < 12 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      Jahr = @mJahr AND 
      SUBSTRING(Monat,1,LEN(@mMonat)) = @mMonat
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
    RETURN
  END

  IF @xLen < 14 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      SUBSTRING(Tag,1,LEN(@mTag)) = @mTag
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
    RETURN
  END

  IF @xLen < 16 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SUBSTRING(SS,1,LEN(@mSS)) = @mSS
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
    RETURN
  END

  IF @xLen < 18 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key1) WHERE 
      ResTyp = @mResTyp AND 
      ResNr = @mResNr AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SS = @mSS AND 
      SUBSTRING(SM,1,LEN(@mSM)) = @mSM
      AND ((0 = ExchgDrop))
      ORDER BY ResTyp desc,ResNr desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,RecId desc
    RETURN
  END

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


-- logical key  ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
IF 0 < (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_ResPlandaten' and so.[id] = si.[id] and si.[name] = 'Ik_Key2')
  DROP INDEX [Ik_Key2] ON [dbo].[ut_ResPlandaten]
GO
IF 0 = (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_ResPlandaten' and so.[id] = si.[id] and si.[name] = 'Ik_Key2')
  CREATE INDEX [Ik_Key2] ON [dbo].[ut_ResPlandaten] (Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId)
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_ResPlandaten_Key2')
  DROP PROCEDURE [dbo].[up_First_ResPlandaten_Key2]
GO
CREATE PROCEDURE [dbo].[up_First_ResPlandaten_Key2] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE (0 = ExchgDrop) ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_ResPlandaten_Key2')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_ResPlandaten_Key2] AS EXECUTE [dbo].[up_First_ResPlandaten_Key2]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_ResPlandaten_Key2')
  DROP PROCEDURE [dbo].[up_Last_ResPlandaten_Key2]
GO
CREATE PROCEDURE [dbo].[up_Last_ResPlandaten_Key2] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE (0 = ExchgDrop) ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,LfdNr desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_ResPlandaten_Key2')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_ResPlandaten_Key2] AS EXECUTE [dbo].[up_Last_ResPlandaten_Key2]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_ResPlandaten_Key2')
  DROP PROCEDURE [dbo].[up_Next_ResPlandaten_Key2]
GO
CREATE PROCEDURE [dbo].[up_Next_ResPlandaten_Key2] @oldRecId integer AS
  DECLARE @oldSerie integer
  DECLARE @oldJahr char(4)
  DECLARE @oldMonat char(2)
  DECLARE @oldTag char(2)
  DECLARE @oldSS char(2)
  DECLARE @oldSM char(2)
  DECLARE @oldLfdNr char(8)
  SELECT @oldSerie = Serie, @oldJahr = Jahr, @oldMonat = Monat, @oldTag = Tag, @oldSS = SS, @oldSM = SM, @oldLfdNr = LfdNr FROM [dbo].[ut_ResPlandaten] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2)
    WHERE ((Serie = @oldSerie) AND (Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag = @oldTag) AND (SS = @oldSS) AND (SM = @oldSM) AND (LfdNr = @oldLfdNr) AND (RecId > @oldRecId)) AND ((0 = ExchgDrop))
    ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2)
    WHERE ((Serie = @oldSerie) AND (Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag = @oldTag) AND (SS = @oldSS) AND (SM = @oldSM) AND (LfdNr > @oldLfdNr)) AND ((0 = ExchgDrop))
    ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2)
    WHERE ((Serie = @oldSerie) AND (Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag = @oldTag) AND (SS = @oldSS) AND (SM > @oldSM)) AND ((0 = ExchgDrop))
    ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2)
    WHERE ((Serie = @oldSerie) AND (Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag = @oldTag) AND (SS > @oldSS)) AND ((0 = ExchgDrop))
    ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2)
    WHERE ((Serie = @oldSerie) AND (Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag > @oldTag)) AND ((0 = ExchgDrop))
    ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2)
    WHERE ((Serie = @oldSerie) AND (Jahr = @oldJahr) AND (Monat > @oldMonat)) AND ((0 = ExchgDrop))
    ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2)
    WHERE ((Serie = @oldSerie) AND (Jahr > @oldJahr)) AND ((0 = ExchgDrop))
    ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2)
    WHERE ((Serie > @oldSerie)) AND ((0 = ExchgDrop))
    ORDER BY Serie,Jahr,Monat,Tag,SS,SM,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_ResPlandaten_Key2')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_ResPlandaten_Key2] @oldRecId integer AS EXECUTE [dbo].[up_Next_ResPlandaten_Key2] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_ResPlandaten_Key2')
  DROP PROCEDURE [dbo].[up_Prior_ResPlandaten_Key2]
GO
CREATE PROCEDURE [dbo].[up_Prior_ResPlandaten_Key2] @oldRecId integer AS
  DECLARE @oldSerie integer
  DECLARE @oldJahr char(4)
  DECLARE @oldMonat char(2)
  DECLARE @oldTag char(2)
  DECLARE @oldSS char(2)
  DECLARE @oldSM char(2)
  DECLARE @oldLfdNr char(8)
  SELECT @oldSerie = Serie, @oldJahr = Jahr, @oldMonat = Monat, @oldTag = Tag, @oldSS = SS, @oldSM = SM, @oldLfdNr = LfdNr FROM [dbo].[ut_ResPlandaten] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2)
    WHERE ((Serie = @oldSerie) AND (Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag = @oldTag) AND (SS = @oldSS) AND (SM = @oldSM) AND (LfdNr = @oldLfdNr) AND (RecId < @oldRecId)) AND ((0 = ExchgDrop))
    ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM 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_ResPlandaten] WITH(INDEX = Ik_Key2)
    WHERE ((Serie = @oldSerie) AND (Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag = @oldTag) AND (SS = @oldSS) AND (SM = @oldSM) AND (LfdNr < @oldLfdNr)) AND ((0 = ExchgDrop))
    ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM 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_ResPlandaten] WITH(INDEX = Ik_Key2)
    WHERE ((Serie = @oldSerie) AND (Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag = @oldTag) AND (SS = @oldSS) AND (SM < @oldSM)) AND ((0 = ExchgDrop))
    ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM 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_ResPlandaten] WITH(INDEX = Ik_Key2)
    WHERE ((Serie = @oldSerie) AND (Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag = @oldTag) AND (SS < @oldSS)) AND ((0 = ExchgDrop))
    ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM 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_ResPlandaten] WITH(INDEX = Ik_Key2)
    WHERE ((Serie = @oldSerie) AND (Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag < @oldTag)) AND ((0 = ExchgDrop))
    ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM 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_ResPlandaten] WITH(INDEX = Ik_Key2)
    WHERE ((Serie = @oldSerie) AND (Jahr = @oldJahr) AND (Monat < @oldMonat)) AND ((0 = ExchgDrop))
    ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM 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_ResPlandaten] WITH(INDEX = Ik_Key2)
    WHERE ((Serie = @oldSerie) AND (Jahr < @oldJahr)) AND ((0 = ExchgDrop))
    ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM 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_ResPlandaten] WITH(INDEX = Ik_Key2)
    WHERE ((Serie < @oldSerie)) AND ((0 = ExchgDrop))
    ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM 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_ResPlandaten_Key2')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_ResPlandaten_Key2] @oldRecId integer AS EXECUTE [dbo].[up_Prior_ResPlandaten_Key2] @oldRecId')
GO

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

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_ResPlandaten_Key2')
  DROP PROCEDURE [dbo].[up_FindF_ResPlandaten_Key2]
GO
CREATE PROCEDURE [dbo].[up_FindF_ResPlandaten_Key2] @xKey char(250), @xLen integer AS
  DECLARE @mSerie integer = dbo.itcfunc_KeyToken_integer(@xKey,@xLen,1,8)
  DECLARE @mJahr char(4) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,9,4)
  DECLARE @mMonat char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,13,2)
  DECLARE @mTag char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,15,2)
  DECLARE @mSS char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,17,2)
  DECLARE @mSM char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,19,2)
  DECLARE @mLfdNr char(8) = dbo.itcfunc_KeyToken_varchar(@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_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SS = @mSS AND 
      SM = @mSM AND 
      LfdNr = @mLfdNr
      AND ((0 = ExchgDrop))
      ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
    RETURN
  END

  IF @xLen = 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SS = @mSS AND 
      SM = @mSM
      AND ((0 = ExchgDrop))
      ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
    RETURN
  END

  IF @xLen = 18 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SS = @mSS
      AND ((0 = ExchgDrop))
      ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
    RETURN
  END

  IF @xLen = 16 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag
      AND ((0 = ExchgDrop))
      ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
    RETURN
  END

  IF @xLen = 14 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      Monat = @mMonat
      AND ((0 = ExchgDrop))
      ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
    RETURN
  END

  IF @xLen = 12 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr
      AND ((0 = ExchgDrop))
      ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
    RETURN
  END

  IF @xLen = 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie
      AND ((0 = ExchgDrop))
      ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
    RETURN
  END

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


  IF @xLen < 12 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr >= @mJahr AND SUBSTRING(Jahr,1,LEN(@mJahr)) = @mJahr
      AND ((0 = ExchgDrop))
      ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
    RETURN
  END

  IF @xLen < 14 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      Monat >= @mMonat AND SUBSTRING(Monat,1,LEN(@mMonat)) = @mMonat
      AND ((0 = ExchgDrop))
      ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
    RETURN
  END

  IF @xLen < 16 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag >= @mTag AND SUBSTRING(Tag,1,LEN(@mTag)) = @mTag
      AND ((0 = ExchgDrop))
      ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
    RETURN
  END

  IF @xLen < 18 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SS >= @mSS AND SUBSTRING(SS,1,LEN(@mSS)) = @mSS
      AND ((0 = ExchgDrop))
      ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
    RETURN
  END

  IF @xLen < 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SS = @mSS AND 
      SM >= @mSM AND SUBSTRING(SM,1,LEN(@mSM)) = @mSM
      AND ((0 = ExchgDrop))
      ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
    RETURN
  END

  IF @xLen < 28 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SS = @mSS AND 
      SM = @mSM AND 
      LfdNr >= @mLfdNr AND SUBSTRING(LfdNr,1,LEN(@mLfdNr)) = @mLfdNr
      AND ((0 = ExchgDrop))
      ORDER BY Serie,Jahr,Monat,Tag,SS,SM,LfdNr,RecId
    RETURN
  END

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_ResPlandaten_Key2')
  DROP PROCEDURE [dbo].[up_FindL_ResPlandaten_Key2]
GO
CREATE PROCEDURE [dbo].[up_FindL_ResPlandaten_Key2] @xKey char(250), @xLen integer AS
  DECLARE @mSerie integer = dbo.itcfunc_KeyToken_integer(@xKey,@xLen,1,8)
  DECLARE @mJahr char(4) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,9,4)
  DECLARE @mMonat char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,13,2)
  DECLARE @mTag char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,15,2)
  DECLARE @mSS char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,17,2)
  DECLARE @mSM char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,19,2)
  DECLARE @mLfdNr char(8) = dbo.itcfunc_KeyToken_varchar(@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_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SS = @mSS AND 
      SM = @mSM AND 
      LfdNr = @mLfdNr
      AND ((0 = ExchgDrop))
      ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen = 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SS = @mSS AND 
      SM = @mSM
      AND ((0 = ExchgDrop))
      ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen = 18 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SS = @mSS
      AND ((0 = ExchgDrop))
      ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen = 16 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag
      AND ((0 = ExchgDrop))
      ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen = 14 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      Monat = @mMonat
      AND ((0 = ExchgDrop))
      ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen = 12 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr
      AND ((0 = ExchgDrop))
      ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen = 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie
      AND ((0 = ExchgDrop))
      ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,LfdNr desc,RecId desc
    RETURN
  END

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


  IF @xLen < 12 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      SUBSTRING(Jahr,1,LEN(@mJahr)) = @mJahr
      AND ((0 = ExchgDrop))
      ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen < 14 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      SUBSTRING(Monat,1,LEN(@mMonat)) = @mMonat
      AND ((0 = ExchgDrop))
      ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen < 16 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      SUBSTRING(Tag,1,LEN(@mTag)) = @mTag
      AND ((0 = ExchgDrop))
      ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen < 18 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SUBSTRING(SS,1,LEN(@mSS)) = @mSS
      AND ((0 = ExchgDrop))
      ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen < 20 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SS = @mSS AND 
      SUBSTRING(SM,1,LEN(@mSM)) = @mSM
      AND ((0 = ExchgDrop))
      ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM desc,LfdNr desc,RecId desc
    RETURN
  END

  IF @xLen < 28 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key2) WHERE 
      Serie = @mSerie AND 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SS = @mSS AND 
      SM = @mSM AND 
      SUBSTRING(LfdNr,1,LEN(@mLfdNr)) = @mLfdNr
      AND ((0 = ExchgDrop))
      ORDER BY Serie desc,Jahr desc,Monat desc,Tag desc,SS desc,SM 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_ResPlandaten_Key2')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindL_ResPlandaten_Key2] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindL_ResPlandaten_Key2] @xKey, @xLen')
GO


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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_ResPlandaten_Key3')
  DROP PROCEDURE [dbo].[up_First_ResPlandaten_Key3]
GO
CREATE PROCEDURE [dbo].[up_First_ResPlandaten_Key3] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3) WHERE (ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop) ORDER BY Jahr,Monat,Tag,ResNr,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_ResPlandaten_Key3')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_ResPlandaten_Key3] AS EXECUTE [dbo].[up_First_ResPlandaten_Key3]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_ResPlandaten_Key3')
  DROP PROCEDURE [dbo].[up_Last_ResPlandaten_Key3]
GO
CREATE PROCEDURE [dbo].[up_Last_ResPlandaten_Key3] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3) WHERE (ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop) ORDER BY Jahr desc,Monat desc,Tag desc,ResNr desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_ResPlandaten_Key3')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_ResPlandaten_Key3] AS EXECUTE [dbo].[up_Last_ResPlandaten_Key3]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_ResPlandaten_Key3')
  DROP PROCEDURE [dbo].[up_Next_ResPlandaten_Key3]
GO
CREATE PROCEDURE [dbo].[up_Next_ResPlandaten_Key3] @oldRecId integer AS
  DECLARE @oldJahr char(4)
  DECLARE @oldMonat char(2)
  DECLARE @oldTag char(2)
  DECLARE @oldResNr char(4)
  SELECT @oldJahr = Jahr, @oldMonat = Monat, @oldTag = Tag, @oldResNr = ResNr FROM [dbo].[ut_ResPlandaten] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3)
    WHERE ((Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag = @oldTag) AND (ResNr = @oldResNr) AND (RecId > @oldRecId)) AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
    ORDER BY Jahr,Monat,Tag,ResNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3)
    WHERE ((Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag = @oldTag) AND (ResNr > @oldResNr)) AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
    ORDER BY Jahr,Monat,Tag,ResNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3)
    WHERE ((Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag > @oldTag)) AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
    ORDER BY Jahr,Monat,Tag,ResNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3)
    WHERE ((Jahr = @oldJahr) AND (Monat > @oldMonat)) AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
    ORDER BY Jahr,Monat,Tag,ResNr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3)
    WHERE ((Jahr > @oldJahr)) AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
    ORDER BY Jahr,Monat,Tag,ResNr,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_ResPlandaten_Key3')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_ResPlandaten_Key3] @oldRecId integer AS EXECUTE [dbo].[up_Next_ResPlandaten_Key3] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_ResPlandaten_Key3')
  DROP PROCEDURE [dbo].[up_Prior_ResPlandaten_Key3]
GO
CREATE PROCEDURE [dbo].[up_Prior_ResPlandaten_Key3] @oldRecId integer AS
  DECLARE @oldJahr char(4)
  DECLARE @oldMonat char(2)
  DECLARE @oldTag char(2)
  DECLARE @oldResNr char(4)
  SELECT @oldJahr = Jahr, @oldMonat = Monat, @oldTag = Tag, @oldResNr = ResNr FROM [dbo].[ut_ResPlandaten] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3)
    WHERE ((Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag = @oldTag) AND (ResNr = @oldResNr) AND (RecId < @oldRecId)) AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
    ORDER BY Jahr desc,Monat desc,Tag desc,ResNr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3)
    WHERE ((Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag = @oldTag) AND (ResNr < @oldResNr)) AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
    ORDER BY Jahr desc,Monat desc,Tag desc,ResNr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3)
    WHERE ((Jahr = @oldJahr) AND (Monat = @oldMonat) AND (Tag < @oldTag)) AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
    ORDER BY Jahr desc,Monat desc,Tag desc,ResNr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3)
    WHERE ((Jahr = @oldJahr) AND (Monat < @oldMonat)) AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
    ORDER BY Jahr desc,Monat desc,Tag desc,ResNr desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3)
    WHERE ((Jahr < @oldJahr)) AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
    ORDER BY Jahr desc,Monat desc,Tag desc,ResNr 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_ResPlandaten_Key3')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_ResPlandaten_Key3] @oldRecId integer AS EXECUTE [dbo].[up_Prior_ResPlandaten_Key3] @oldRecId')
GO

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

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_ResPlandaten_Key3')
  DROP PROCEDURE [dbo].[up_FindF_ResPlandaten_Key3]
GO
CREATE PROCEDURE [dbo].[up_FindF_ResPlandaten_Key3] @xKey char(250), @xLen integer AS
  DECLARE @mJahr char(4) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,4)
  DECLARE @mMonat char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,5,2)
  DECLARE @mTag char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,7,2)
  DECLARE @mResNr char(4) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,9,4)

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

  IF @xLen = 12 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3) WHERE 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      ResNr = @mResNr
      AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
      ORDER BY Jahr,Monat,Tag,ResNr,RecId
    RETURN
  END

  IF @xLen = 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3) WHERE 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag
      AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
      ORDER BY Jahr,Monat,Tag,ResNr,RecId
    RETURN
  END

  IF @xLen = 6 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3) WHERE 
      Jahr = @mJahr AND 
      Monat = @mMonat
      AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
      ORDER BY Jahr,Monat,Tag,ResNr,RecId
    RETURN
  END

  IF @xLen = 4 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3) WHERE 
      Jahr = @mJahr
      AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
      ORDER BY Jahr,Monat,Tag,ResNr,RecId
    RETURN
  END

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

  IF @xLen < 4 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3) WHERE 
      Jahr >= @mJahr AND SUBSTRING(Jahr,1,LEN(@mJahr)) = @mJahr
      AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
      ORDER BY Jahr,Monat,Tag,ResNr,RecId
    RETURN
  END

  IF @xLen < 6 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3) WHERE 
      Jahr = @mJahr AND 
      Monat >= @mMonat AND SUBSTRING(Monat,1,LEN(@mMonat)) = @mMonat
      AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
      ORDER BY Jahr,Monat,Tag,ResNr,RecId
    RETURN
  END

  IF @xLen < 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3) WHERE 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag >= @mTag AND SUBSTRING(Tag,1,LEN(@mTag)) = @mTag
      AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
      ORDER BY Jahr,Monat,Tag,ResNr,RecId
    RETURN
  END

  IF @xLen < 12 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3) WHERE 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      ResNr >= @mResNr AND SUBSTRING(ResNr,1,LEN(@mResNr)) = @mResNr
      AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
      ORDER BY Jahr,Monat,Tag,ResNr,RecId
    RETURN
  END

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_ResPlandaten_Key3')
  DROP PROCEDURE [dbo].[up_FindL_ResPlandaten_Key3]
GO
CREATE PROCEDURE [dbo].[up_FindL_ResPlandaten_Key3] @xKey char(250), @xLen integer AS
  DECLARE @mJahr char(4) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,4)
  DECLARE @mMonat char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,5,2)
  DECLARE @mTag char(2) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,7,2)
  DECLARE @mResNr char(4) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,9,4)

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

  IF @xLen = 12 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3) WHERE 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      ResNr = @mResNr
      AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
      ORDER BY Jahr desc,Monat desc,Tag desc,ResNr desc,RecId desc
    RETURN
  END

  IF @xLen = 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3) WHERE 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag
      AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
      ORDER BY Jahr desc,Monat desc,Tag desc,ResNr desc,RecId desc
    RETURN
  END

  IF @xLen = 6 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3) WHERE 
      Jahr = @mJahr AND 
      Monat = @mMonat
      AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
      ORDER BY Jahr desc,Monat desc,Tag desc,ResNr desc,RecId desc
    RETURN
  END

  IF @xLen = 4 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3) WHERE 
      Jahr = @mJahr
      AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
      ORDER BY Jahr desc,Monat desc,Tag desc,ResNr desc,RecId desc
    RETURN
  END

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

  IF @xLen < 4 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3) WHERE 
      SUBSTRING(Jahr,1,LEN(@mJahr)) = @mJahr
      AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
      ORDER BY Jahr desc,Monat desc,Tag desc,ResNr desc,RecId desc
    RETURN
  END

  IF @xLen < 6 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3) WHERE 
      Jahr = @mJahr AND 
      SUBSTRING(Monat,1,LEN(@mMonat)) = @mMonat
      AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
      ORDER BY Jahr desc,Monat desc,Tag desc,ResNr desc,RecId desc
    RETURN
  END

  IF @xLen < 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3) WHERE 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      SUBSTRING(Tag,1,LEN(@mTag)) = @mTag
      AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
      ORDER BY Jahr desc,Monat desc,Tag desc,ResNr desc,RecId desc
    RETURN
  END

  IF @xLen < 12 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_ResPlandaten] WITH(INDEX = Ik_Key3) WHERE 
      Jahr = @mJahr AND 
      Monat = @mMonat AND 
      Tag = @mTag AND 
      SUBSTRING(ResNr,1,LEN(@mResNr)) = @mResNr
      AND ((ResTyp = ' 4') AND (Uebernommen = '0') AND (1 = ZuVerbuchen) AND (0 = ExchgDrop))
      ORDER BY Jahr desc,Monat desc,Tag desc,ResNr desc,RecId desc
    RETURN
  END

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


