-- This file is part of the it-crafts software and is subject to copyright protection.
-- (C) 2022..2023, 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_Kontenplan')
BEGIN
CREATE TABLE [dbo].[ut_Kontenplan]
(
  [RecId]  integer NOT NULL
 ,[Typ] char(1) DEFAULT ''
 ,[Konto] char(8) DEFAULT ''
 ,[Schl] char(3) DEFAULT ''
 ,[Passiv] tinyint DEFAULT 0
 ,[Name] char(42) DEFAULT ''
 ,[AutoKonto] char(8) DEFAULT ''
 ,[AutoSatz] float DEFAULT 0
 ,[KzKasse] char(1) DEFAULT ''
 ,[UstSeite] char(1) DEFAULT ''
 ,[UstSpalte] char(3) DEFAULT ''
 ,[BilZeile] char(4) DEFAULT ''
 ,[KzUst] char(1) DEFAULT ''
 ,[DstKonto] char(8) DEFAULT ''
 ,[SumKonto] char(8) DEFAULT ''
 ,[KzPrj] integer DEFAULT 0
 ,[WahlSchl] char(30) DEFAULT ''
 ,[VkzDatev] integer DEFAULT 0
 ,[Automatik] tinyint DEFAULT 0
 ,[SammelKz] tinyint DEFAULT 0
 ,[SammelKto] char(8) DEFAULT ''
 ,[SKR03] char(8) DEFAULT ''
 ,[SKR04] char(8) DEFAULT ''
 ,[SchlDatev] char(10) DEFAULT ''
  CONSTRAINT [Pk_Kontenplan]  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_Kontenplan_KeyDel')
  DROP TRIGGER [dbo].[tr_Kontenplan_KeyDel]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'TR'   and [name] = 'tr_Kontenplan_KeyUpd')
  DROP TRIGGER [dbo].[tr_Kontenplan_KeyUpd]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'U' and [name] = 'ut_Kontenplan_Keys')
  DROP TABLE [dbo].[ut_Kontenplan_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_Kontenplan' and sc.[name] = 'Typ')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [Typ] char(1) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Kontenplan' and sc.[name] = 'Konto')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [Konto] 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_Kontenplan' and sc.[name] = 'Schl')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [Schl] char(3) 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_Kontenplan' and sc.[name] = 'Passiv')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [Passiv] 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_Kontenplan' and sc.[name] = 'Name')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [Name] 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_Kontenplan' and sc.[name] = 'AutoKonto')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [AutoKonto] 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_Kontenplan' and sc.[name] = 'AutoSatz')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [AutoSatz] 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_Kontenplan' and sc.[name] = 'KzKasse')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [KzKasse] 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_Kontenplan' and sc.[name] = 'UstSeite')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [UstSeite] 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_Kontenplan' and sc.[name] = 'UstSpalte')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [UstSpalte] char(3) 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_Kontenplan' and sc.[name] = 'BilZeile')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [BilZeile] 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_Kontenplan' and sc.[name] = 'KzUst')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [KzUst] 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_Kontenplan' and sc.[name] = 'DstKonto')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [DstKonto] 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_Kontenplan' and sc.[name] = 'SumKonto')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [SumKonto] 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_Kontenplan' and sc.[name] = 'KzPrj')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [KzPrj] 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_Kontenplan' and sc.[name] = 'WahlSchl')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [WahlSchl] char(30) 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_Kontenplan' and sc.[name] = 'VkzDatev')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [VkzDatev] 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_Kontenplan' and sc.[name] = 'Automatik')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [Automatik] 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_Kontenplan' and sc.[name] = 'SammelKz')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [SammelKz] 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_Kontenplan' and sc.[name] = 'SammelKto')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [SammelKto] 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_Kontenplan' and sc.[name] = 'SKR03')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [SKR03] 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_Kontenplan' and sc.[name] = 'SKR04')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [SKR04] 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_Kontenplan' and sc.[name] = 'SchlDatev')
BEGIN
  ALTER TABLE [dbo].[ut_Kontenplan] ADD [SchlDatev] char(10) NOT NULL DEFAULT ''
END
GO

-- check lengths of fields
IF 1 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Kontenplan' and sc.[name] = 'Typ')
BEGIN
   ALTER TABLE [dbo].[ut_Kontenplan] ALTER COLUMN [Typ] char(1)
END
GO
IF 8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Kontenplan' and sc.[name] = 'Konto')
BEGIN
   ALTER TABLE [dbo].[ut_Kontenplan] ALTER COLUMN [Konto] char(8)
END
GO
IF 3 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Kontenplan' and sc.[name] = 'Schl')
BEGIN
   ALTER TABLE [dbo].[ut_Kontenplan] ALTER COLUMN [Schl] char(3)
END
GO
IF 42 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Kontenplan' and sc.[name] = 'Name')
BEGIN
   ALTER TABLE [dbo].[ut_Kontenplan] ALTER COLUMN [Name] char(42)
END
GO
IF 8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Kontenplan' and sc.[name] = 'AutoKonto')
BEGIN
   ALTER TABLE [dbo].[ut_Kontenplan] ALTER COLUMN [AutoKonto] char(8)
END
GO
IF 1 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Kontenplan' and sc.[name] = 'KzKasse')
BEGIN
   ALTER TABLE [dbo].[ut_Kontenplan] ALTER COLUMN [KzKasse] char(1)
END
GO
IF 1 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Kontenplan' and sc.[name] = 'UstSeite')
BEGIN
   ALTER TABLE [dbo].[ut_Kontenplan] ALTER COLUMN [UstSeite] char(1)
END
GO
IF 3 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Kontenplan' and sc.[name] = 'UstSpalte')
BEGIN
   ALTER TABLE [dbo].[ut_Kontenplan] ALTER COLUMN [UstSpalte] char(3)
END
GO
IF 4 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Kontenplan' and sc.[name] = 'BilZeile')
BEGIN
   ALTER TABLE [dbo].[ut_Kontenplan] ALTER COLUMN [BilZeile] char(4)
END
GO
IF 1 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Kontenplan' and sc.[name] = 'KzUst')
BEGIN
   ALTER TABLE [dbo].[ut_Kontenplan] ALTER COLUMN [KzUst] char(1)
END
GO
IF 8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Kontenplan' and sc.[name] = 'DstKonto')
BEGIN
   ALTER TABLE [dbo].[ut_Kontenplan] ALTER COLUMN [DstKonto] char(8)
END
GO
IF 8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Kontenplan' and sc.[name] = 'SumKonto')
BEGIN
   ALTER TABLE [dbo].[ut_Kontenplan] ALTER COLUMN [SumKonto] char(8)
END
GO
IF 30 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Kontenplan' and sc.[name] = 'WahlSchl')
BEGIN
   ALTER TABLE [dbo].[ut_Kontenplan] ALTER COLUMN [WahlSchl] char(30)
END
GO
IF 8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Kontenplan' and sc.[name] = 'SammelKto')
BEGIN
   ALTER TABLE [dbo].[ut_Kontenplan] ALTER COLUMN [SammelKto] char(8)
END
GO
IF 8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Kontenplan' and sc.[name] = 'SKR03')
BEGIN
   ALTER TABLE [dbo].[ut_Kontenplan] ALTER COLUMN [SKR03] char(8)
END
GO
IF 8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Kontenplan' and sc.[name] = 'SKR04')
BEGIN
   ALTER TABLE [dbo].[ut_Kontenplan] ALTER COLUMN [SKR04] char(8)
END
GO
IF 10 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Kontenplan' and sc.[name] = 'SchlDatev')
BEGIN
   ALTER TABLE [dbo].[ut_Kontenplan] ALTER COLUMN [SchlDatev] char(10)
END
GO

-- create dedicated view
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'Kontenplan')
  DROP VIEW [dbo].[Kontenplan]
GO
CREATE VIEW [dbo].[Kontenplan] AS
  SELECT [RecId]
 ,[Typ]
 ,[Konto]
 ,[Schl]
 ,[Passiv]
 ,[Name]
 ,[AutoKonto]
 ,[AutoSatz]
 ,[KzKasse]
 ,[UstSeite]
 ,[UstSpalte]
 ,[BilZeile]
 ,[KzUst]
 ,[DstKonto]
 ,[SumKonto]
 ,[KzPrj]
 ,[WahlSchl]
 ,[VkzDatev]
 ,[Automatik]
 ,[SammelKz]
 ,[SammelKto]
 ,[SKR03]
 ,[SKR04]
 ,[SchlDatev]
  FROM [dbo].[ut_Kontenplan]
GO


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

-- select with classic client
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Sel_Kontenplan')
  DROP PROCEDURE [dbo].[up_Sel_Kontenplan]
GO
CREATE PROCEDURE [dbo].[up_Sel_Kontenplan]  @mRI integer
AS
  SELECT
    [Typ]
   ,[Konto]
   ,[Schl]
   ,[Passiv]
   ,[Name]
   ,[AutoKonto]
   ,[AutoSatz]
   ,[KzKasse]
   ,[UstSeite]
   ,[UstSpalte]
   ,[BilZeile]
   ,[KzUst]
   ,[DstKonto]
   ,[SumKonto]
   ,[KzPrj]
   ,[WahlSchl]
   ,[VkzDatev]
   ,[Automatik]
   ,[SammelKz]
   ,[SammelKto]
   ,[SKR03]
   ,[SKR04]
   ,[SchlDatev]
  FROM [dbo].[ut_Kontenplan] WHERE RecId = @mRI
GO

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

-- insert with classic client
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Append_Kontenplan')
  DROP PROCEDURE [dbo].[up_Append_Kontenplan]
GO
CREATE PROCEDURE [dbo].[up_Append_Kontenplan]
   @Param_Typ char(1)
  ,@Param_Konto char(8)
  ,@Param_Schl char(3)
  ,@Param_Passiv tinyint
  ,@Param_Name char(42)
  ,@Param_AutoKonto char(8)
  ,@Param_AutoSatz float
  ,@Param_KzKasse char(1)
  ,@Param_UstSeite char(1)
  ,@Param_UstSpalte char(3)
  ,@Param_BilZeile char(4)
  ,@Param_KzUst char(1)
  ,@Param_DstKonto char(8)
  ,@Param_SumKonto char(8)
  ,@Param_KzPrj integer
  ,@Param_WahlSchl char(30)
  ,@Param_VkzDatev integer
  ,@Param_Automatik tinyint
  ,@Param_SammelKz tinyint
  ,@Param_SammelKto char(8)
  ,@Param_SKR03 char(8)
  ,@Param_SKR04 char(8)
  ,@Param_SchlDatev char(10)
AS
  DECLARE @NeueId integer
  DECLARE @m_CountDown integer = 10
  WHILE @m_CountDown > 0 BEGIN
    BEGIN TRANSACTION
    BEGIN TRY
      SET @NeueId = dbo.itcfunc_NextRecId_Kontenplan()
      INSERT INTO [dbo].[ut_Kontenplan] (RecId
        ,[Typ]
        ,[Konto]
        ,[Schl]
        ,[Passiv]
        ,[Name]
        ,[AutoKonto]
        ,[AutoSatz]
        ,[KzKasse]
        ,[UstSeite]
        ,[UstSpalte]
        ,[BilZeile]
        ,[KzUst]
        ,[DstKonto]
        ,[SumKonto]
        ,[KzPrj]
        ,[WahlSchl]
        ,[VkzDatev]
        ,[Automatik]
        ,[SammelKz]
        ,[SammelKto]
        ,[SKR03]
        ,[SKR04]
        ,[SchlDatev]
          ) VALUES (@NeueId
        ,UPPER(RTRIM(@Param_Typ))
        ,UPPER(RTRIM(@Param_Konto))
        ,UPPER(RTRIM(@Param_Schl))
        ,@Param_Passiv
        ,RTRIM(@Param_Name)
        ,UPPER(RTRIM(@Param_AutoKonto))
        ,@Param_AutoSatz
        ,UPPER(RTRIM(@Param_KzKasse))
        ,UPPER(RTRIM(@Param_UstSeite))
        ,UPPER(RTRIM(@Param_UstSpalte))
        ,UPPER(RTRIM(@Param_BilZeile))
        ,UPPER(RTRIM(@Param_KzUst))
        ,UPPER(RTRIM(@Param_DstKonto))
        ,UPPER(RTRIM(@Param_SumKonto))
        ,@Param_KzPrj
        ,RTRIM(@Param_WahlSchl)
        ,@Param_VkzDatev
        ,@Param_Automatik
        ,@Param_SammelKz
        ,UPPER(RTRIM(@Param_SammelKto))
        ,UPPER(RTRIM(@Param_SKR03))
        ,UPPER(RTRIM(@Param_SKR04))
        ,RTRIM(@Param_SchlDatev)
          )
      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_Kontenplan')
  DROP PROCEDURE [dbo].[up_Add_Kontenplan]
GO
CREATE PROCEDURE [dbo].[up_Add_Kontenplan]
AS
  DECLARE @NeueId integer = dbo.itcfunc_NextRecId_Kontenplan()
  INSERT INTO [dbo].[ut_Kontenplan] (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_Kontenplan')
  DROP PROCEDURE [dbo].[up_Upd_Kontenplan]
GO
CREATE PROCEDURE [dbo].[up_Upd_Kontenplan] @mRI integer
  ,@Param_Typ char(1)
  ,@Param_Konto char(8)
  ,@Param_Schl char(3)
  ,@Param_Passiv tinyint
  ,@Param_Name char(42)
  ,@Param_AutoKonto char(8)
  ,@Param_AutoSatz float
  ,@Param_KzKasse char(1)
  ,@Param_UstSeite char(1)
  ,@Param_UstSpalte char(3)
  ,@Param_BilZeile char(4)
  ,@Param_KzUst char(1)
  ,@Param_DstKonto char(8)
  ,@Param_SumKonto char(8)
  ,@Param_KzPrj integer
  ,@Param_WahlSchl char(30)
  ,@Param_VkzDatev integer
  ,@Param_Automatik tinyint
  ,@Param_SammelKz tinyint
  ,@Param_SammelKto char(8)
  ,@Param_SKR03 char(8)
  ,@Param_SKR04 char(8)
  ,@Param_SchlDatev char(10)
AS
  UPDATE [dbo].[ut_Kontenplan] SET
    [Typ] = UPPER(RTRIM(@Param_Typ))
   ,[Konto] = UPPER(RTRIM(@Param_Konto))
   ,[Schl] = UPPER(RTRIM(@Param_Schl))
   ,[Passiv] = @Param_Passiv
   ,[Name] = RTRIM(@Param_Name)
   ,[AutoKonto] = UPPER(RTRIM(@Param_AutoKonto))
   ,[AutoSatz] = @Param_AutoSatz
   ,[KzKasse] = UPPER(RTRIM(@Param_KzKasse))
   ,[UstSeite] = UPPER(RTRIM(@Param_UstSeite))
   ,[UstSpalte] = UPPER(RTRIM(@Param_UstSpalte))
   ,[BilZeile] = UPPER(RTRIM(@Param_BilZeile))
   ,[KzUst] = UPPER(RTRIM(@Param_KzUst))
   ,[DstKonto] = UPPER(RTRIM(@Param_DstKonto))
   ,[SumKonto] = UPPER(RTRIM(@Param_SumKonto))
   ,[KzPrj] = @Param_KzPrj
   ,[WahlSchl] = RTRIM(@Param_WahlSchl)
   ,[VkzDatev] = @Param_VkzDatev
   ,[Automatik] = @Param_Automatik
   ,[SammelKz] = @Param_SammelKz
   ,[SammelKto] = UPPER(RTRIM(@Param_SammelKto))
   ,[SKR03] = UPPER(RTRIM(@Param_SKR03))
   ,[SKR04] = UPPER(RTRIM(@Param_SKR04))
   ,[SchlDatev] = RTRIM(@Param_SchlDatev)
  WHERE RecId = @mRI
GO

-- update with modern clients
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Upd2_Kontenplan')
  DROP PROCEDURE [dbo].[up_Upd2_Kontenplan]
GO
CREATE PROCEDURE [dbo].[up_Upd2_Kontenplan] @Param_RecId varchar(20)
  ,@Param_Typ varchar(1)
  ,@Param_Konto varchar(8)
  ,@Param_Schl varchar(3)
  ,@Param_Passiv varchar(1)
  ,@Param_Name varchar(42)
  ,@Param_AutoKonto varchar(8)
  ,@Param_AutoSatz varchar(7)
  ,@Param_KzKasse varchar(1)
  ,@Param_UstSeite varchar(1)
  ,@Param_UstSpalte varchar(3)
  ,@Param_BilZeile varchar(4)
  ,@Param_KzUst varchar(1)
  ,@Param_DstKonto varchar(8)
  ,@Param_SumKonto varchar(8)
  ,@Param_KzPrj varchar(2)
  ,@Param_WahlSchl varchar(30)
  ,@Param_VkzDatev varchar(1)
  ,@Param_Automatik varchar(1)
  ,@Param_SammelKz varchar(1)
  ,@Param_SammelKto varchar(8)
  ,@Param_SKR03 varchar(8)
  ,@Param_SKR04 varchar(8)
  ,@Param_SchlDatev varchar(10)
AS
  DECLARE @mRI integer = dbo.itcfunc_integer(@Param_RecId)
  UPDATE [dbo].[ut_Kontenplan] SET
    [Typ] = UPPER(RTRIM(@Param_Typ))
   ,[Konto] = UPPER(RTRIM(@Param_Konto))
   ,[Schl] = UPPER(RTRIM(@Param_Schl))
   ,[Passiv] = dbo.itcfunc_integer(@Param_Passiv)
   ,[Name] = RTRIM(@Param_Name)
   ,[AutoKonto] = UPPER(RTRIM(@Param_AutoKonto))
   ,[AutoSatz] = dbo.itcfunc_float(@Param_AutoSatz)
   ,[KzKasse] = UPPER(RTRIM(@Param_KzKasse))
   ,[UstSeite] = UPPER(RTRIM(@Param_UstSeite))
   ,[UstSpalte] = UPPER(RTRIM(@Param_UstSpalte))
   ,[BilZeile] = UPPER(RTRIM(@Param_BilZeile))
   ,[KzUst] = UPPER(RTRIM(@Param_KzUst))
   ,[DstKonto] = UPPER(RTRIM(@Param_DstKonto))
   ,[SumKonto] = UPPER(RTRIM(@Param_SumKonto))
   ,[KzPrj] = dbo.itcfunc_integer(@Param_KzPrj)
   ,[WahlSchl] = RTRIM(@Param_WahlSchl)
   ,[VkzDatev] = dbo.itcfunc_integer(@Param_VkzDatev)
   ,[Automatik] = dbo.itcfunc_integer(@Param_Automatik)
   ,[SammelKz] = dbo.itcfunc_integer(@Param_SammelKz)
   ,[SammelKto] = UPPER(RTRIM(@Param_SammelKto))
   ,[SKR03] = UPPER(RTRIM(@Param_SKR03))
   ,[SKR04] = UPPER(RTRIM(@Param_SKR04))
   ,[SchlDatev] = RTRIM(@Param_SchlDatev)
  WHERE RecId = @mRI

  EXECUTE [dbo].[up_Sel2_Kontenplan] @mRI
GO

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

-- text index
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_TrSel_Kontenplan')
  DROP PROCEDURE [dbo].[up_TrSel_Kontenplan]
GO
CREATE PROCEDURE [dbo].[up_TrSel_Kontenplan]
AS
  SELECT RecId,
    LTRIM(RTRIM([Typ]))
   +LTRIM(RTRIM([Konto]))
   +LTRIM(RTRIM([Schl]))
   +LTRIM(RTRIM([Name]))
   +LTRIM(RTRIM([AutoKonto]))
   +LTRIM(RTRIM([KzKasse]))
   +LTRIM(RTRIM([UstSeite]))
   +LTRIM(RTRIM([UstSpalte]))
   +LTRIM(RTRIM([BilZeile]))
   +LTRIM(RTRIM([KzUst]))
   +LTRIM(RTRIM([DstKonto]))
   +LTRIM(RTRIM([SumKonto]))
   +LTRIM(RTRIM([WahlSchl]))
   +LTRIM(RTRIM([SammelKto]))
   +LTRIM(RTRIM([SKR03]))
   +LTRIM(RTRIM([SKR04]))
   +LTRIM(RTRIM([SchlDatev]))
  AS Data FROM [dbo].[ut_Kontenplan] WHERE RecId > 0 ORDER BY RecId
GO

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

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Kontenplan_Key00')
  DROP PROCEDURE [dbo].[up_First_Kontenplan_Key00]
GO
CREATE PROCEDURE [dbo].[up_First_Kontenplan_Key00] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key00) ORDER BY Typ,Konto,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Kontenplan_Key00')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Kontenplan_Key00] AS EXECUTE [dbo].[up_First_Kontenplan_Key00]')
GO

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Kontenplan_Key00')
  DROP PROCEDURE [dbo].[up_Next_Kontenplan_Key00]
GO
CREATE PROCEDURE [dbo].[up_Next_Kontenplan_Key00] @oldRecId integer AS
  DECLARE @oldTyp char(1)
  DECLARE @oldKonto char(8)
  SELECT @oldTyp = Typ, @oldKonto = Konto FROM [dbo].[ut_Kontenplan] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key00)
    WHERE (Typ = @oldTyp) AND (Konto > @oldKonto)
    ORDER BY Typ,Konto,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key00)
    WHERE (Typ > @oldTyp)
    ORDER BY Typ,Konto,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_Kontenplan_Key00')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Kontenplan_Key00] @oldRecId integer AS EXECUTE [dbo].[up_Next_Kontenplan_Key00] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Kontenplan_Key00')
  DROP PROCEDURE [dbo].[up_Prior_Kontenplan_Key00]
GO
CREATE PROCEDURE [dbo].[up_Prior_Kontenplan_Key00] @oldRecId integer AS
  DECLARE @oldTyp char(1)
  DECLARE @oldKonto char(8)
  SELECT @oldTyp = Typ, @oldKonto = Konto FROM [dbo].[ut_Kontenplan] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key00)
    WHERE (Typ = @oldTyp) AND (Konto < @oldKonto)
    ORDER BY Typ desc,Konto desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key00)
    WHERE (Typ < @oldTyp)
    ORDER BY Typ desc,Konto 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_Kontenplan_Key00')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Kontenplan_Key00] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Kontenplan_Key00] @oldRecId')
GO

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

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

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

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

  IF @xLen = 9 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key00) WHERE 
      Typ = @mTyp AND 
      Konto = @mKonto
      ORDER BY Typ,Konto,RecId
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key00) WHERE 
      Typ = @mTyp
      ORDER BY Typ,Konto,RecId
    RETURN
  END

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

  IF @xLen < 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key00) WHERE 
      Typ >= @mTyp AND SUBSTRING(Typ,1,LEN(@mTyp)) = @mTyp
      ORDER BY Typ,Konto,RecId
    RETURN
  END

  IF @xLen < 9 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key00) WHERE 
      Typ = @mTyp AND 
      Konto >= @mKonto AND SUBSTRING(Konto,1,LEN(@mKonto)) = @mKonto
      ORDER BY Typ,Konto,RecId
    RETURN
  END

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

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

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

  IF @xLen = 9 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key00) WHERE 
      Typ = @mTyp AND 
      Konto = @mKonto
      ORDER BY Typ desc,Konto desc,RecId desc
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key00) WHERE 
      Typ = @mTyp
      ORDER BY Typ desc,Konto desc,RecId desc
    RETURN
  END

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

  IF @xLen < 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key00) WHERE 
      SUBSTRING(Typ,1,LEN(@mTyp)) = @mTyp
      ORDER BY Typ desc,Konto desc,RecId desc
    RETURN
  END

  IF @xLen < 9 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key00) WHERE 
      Typ = @mTyp AND 
      SUBSTRING(Konto,1,LEN(@mKonto)) = @mKonto
      ORDER BY Typ desc,Konto desc,RecId desc
    RETURN
  END

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


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

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

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Kontenplan_Key01')
  DROP PROCEDURE [dbo].[up_Next_Kontenplan_Key01]
GO
CREATE PROCEDURE [dbo].[up_Next_Kontenplan_Key01] @oldRecId integer AS
  DECLARE @oldTyp char(1)
  DECLARE @oldName char(42)
  SELECT @oldTyp = Typ, @oldName = Name FROM [dbo].[ut_Kontenplan] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

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

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key01)
    WHERE (Typ = @oldTyp) AND (Name > @oldName)
    ORDER BY Typ,Name,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key01)
    WHERE (Typ > @oldTyp)
    ORDER BY Typ,Name,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_Kontenplan_Key01')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Kontenplan_Key01] @oldRecId integer AS EXECUTE [dbo].[up_Next_Kontenplan_Key01] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Kontenplan_Key01')
  DROP PROCEDURE [dbo].[up_Prior_Kontenplan_Key01]
GO
CREATE PROCEDURE [dbo].[up_Prior_Kontenplan_Key01] @oldRecId integer AS
  DECLARE @oldTyp char(1)
  DECLARE @oldName char(42)
  SELECT @oldTyp = Typ, @oldName = Name FROM [dbo].[ut_Kontenplan] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

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

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key01)
    WHERE (Typ = @oldTyp) AND (Name < @oldName)
    ORDER BY Typ desc,Name desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key01)
    WHERE (Typ < @oldTyp)
    ORDER BY Typ desc,Name 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_Kontenplan_Key01')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Kontenplan_Key01] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Kontenplan_Key01] @oldRecId')
GO

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

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

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

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

  IF @xLen = 43 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key01) WHERE 
      Typ = @mTyp AND 
      Name = @mName
      ORDER BY Typ,Name,RecId
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key01) WHERE 
      Typ = @mTyp
      ORDER BY Typ,Name,RecId
    RETURN
  END

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

  IF @xLen < 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key01) WHERE 
      Typ >= @mTyp AND SUBSTRING(Typ,1,LEN(@mTyp)) = @mTyp
      ORDER BY Typ,Name,RecId
    RETURN
  END

  IF @xLen < 43 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key01) WHERE 
      Typ = @mTyp AND 
      Name >= @mName AND SUBSTRING(Name,1,LEN(@mName)) = @mName
      ORDER BY Typ,Name,RecId
    RETURN
  END

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

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

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

  IF @xLen = 43 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key01) WHERE 
      Typ = @mTyp AND 
      Name = @mName
      ORDER BY Typ desc,Name desc,RecId desc
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key01) WHERE 
      Typ = @mTyp
      ORDER BY Typ desc,Name desc,RecId desc
    RETURN
  END

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

  IF @xLen < 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key01) WHERE 
      SUBSTRING(Typ,1,LEN(@mTyp)) = @mTyp
      ORDER BY Typ desc,Name desc,RecId desc
    RETURN
  END

  IF @xLen < 43 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key01) WHERE 
      Typ = @mTyp AND 
      SUBSTRING(Name,1,LEN(@mName)) = @mName
      ORDER BY Typ desc,Name desc,RecId desc
    RETURN
  END

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


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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Kontenplan_Key02')
  DROP PROCEDURE [dbo].[up_First_Kontenplan_Key02]
GO
CREATE PROCEDURE [dbo].[up_First_Kontenplan_Key02] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key02) ORDER BY KzKasse,Konto,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Kontenplan_Key02')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Kontenplan_Key02] AS EXECUTE [dbo].[up_First_Kontenplan_Key02]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Kontenplan_Key02')
  DROP PROCEDURE [dbo].[up_Last_Kontenplan_Key02]
GO
CREATE PROCEDURE [dbo].[up_Last_Kontenplan_Key02] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key02) ORDER BY KzKasse desc,Konto desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Kontenplan_Key02')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Kontenplan_Key02] AS EXECUTE [dbo].[up_Last_Kontenplan_Key02]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Kontenplan_Key02')
  DROP PROCEDURE [dbo].[up_Next_Kontenplan_Key02]
GO
CREATE PROCEDURE [dbo].[up_Next_Kontenplan_Key02] @oldRecId integer AS
  DECLARE @oldKzKasse char(1)
  DECLARE @oldKonto char(8)
  SELECT @oldKzKasse = KzKasse, @oldKonto = Konto FROM [dbo].[ut_Kontenplan] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key02)
    WHERE (KzKasse = @oldKzKasse) AND (Konto = @oldKonto) AND (RecId > @oldRecId)
    ORDER BY KzKasse,Konto,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key02)
    WHERE (KzKasse = @oldKzKasse) AND (Konto > @oldKonto)
    ORDER BY KzKasse,Konto,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key02)
    WHERE (KzKasse > @oldKzKasse)
    ORDER BY KzKasse,Konto,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_Kontenplan_Key02')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Kontenplan_Key02] @oldRecId integer AS EXECUTE [dbo].[up_Next_Kontenplan_Key02] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Kontenplan_Key02')
  DROP PROCEDURE [dbo].[up_Prior_Kontenplan_Key02]
GO
CREATE PROCEDURE [dbo].[up_Prior_Kontenplan_Key02] @oldRecId integer AS
  DECLARE @oldKzKasse char(1)
  DECLARE @oldKonto char(8)
  SELECT @oldKzKasse = KzKasse, @oldKonto = Konto FROM [dbo].[ut_Kontenplan] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key02)
    WHERE (KzKasse = @oldKzKasse) AND (Konto = @oldKonto) AND (RecId < @oldRecId)
    ORDER BY KzKasse desc,Konto desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key02)
    WHERE (KzKasse = @oldKzKasse) AND (Konto < @oldKonto)
    ORDER BY KzKasse desc,Konto desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key02)
    WHERE (KzKasse < @oldKzKasse)
    ORDER BY KzKasse desc,Konto 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_Kontenplan_Key02')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Kontenplan_Key02] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Kontenplan_Key02] @oldRecId')
GO

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

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

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

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

  IF @xLen = 9 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key02) WHERE 
      KzKasse = @mKzKasse AND 
      Konto = @mKonto
      ORDER BY KzKasse,Konto,RecId
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key02) WHERE 
      KzKasse = @mKzKasse
      ORDER BY KzKasse,Konto,RecId
    RETURN
  END

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

  IF @xLen < 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key02) WHERE 
      KzKasse >= @mKzKasse AND SUBSTRING(KzKasse,1,LEN(@mKzKasse)) = @mKzKasse
      ORDER BY KzKasse,Konto,RecId
    RETURN
  END

  IF @xLen < 9 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key02) WHERE 
      KzKasse = @mKzKasse AND 
      Konto >= @mKonto AND SUBSTRING(Konto,1,LEN(@mKonto)) = @mKonto
      ORDER BY KzKasse,Konto,RecId
    RETURN
  END

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

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

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

  IF @xLen = 9 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key02) WHERE 
      KzKasse = @mKzKasse AND 
      Konto = @mKonto
      ORDER BY KzKasse desc,Konto desc,RecId desc
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key02) WHERE 
      KzKasse = @mKzKasse
      ORDER BY KzKasse desc,Konto desc,RecId desc
    RETURN
  END

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

  IF @xLen < 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key02) WHERE 
      SUBSTRING(KzKasse,1,LEN(@mKzKasse)) = @mKzKasse
      ORDER BY KzKasse desc,Konto desc,RecId desc
    RETURN
  END

  IF @xLen < 9 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key02) WHERE 
      KzKasse = @mKzKasse AND 
      SUBSTRING(Konto,1,LEN(@mKonto)) = @mKonto
      ORDER BY KzKasse desc,Konto desc,RecId desc
    RETURN
  END

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


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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Kontenplan_Key03')
  DROP PROCEDURE [dbo].[up_First_Kontenplan_Key03]
GO
CREATE PROCEDURE [dbo].[up_First_Kontenplan_Key03] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03) ORDER BY Typ,KzKasse,KzUst,Schl,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Kontenplan_Key03')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Kontenplan_Key03] AS EXECUTE [dbo].[up_First_Kontenplan_Key03]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Kontenplan_Key03')
  DROP PROCEDURE [dbo].[up_Last_Kontenplan_Key03]
GO
CREATE PROCEDURE [dbo].[up_Last_Kontenplan_Key03] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03) ORDER BY Typ desc,KzKasse desc,KzUst desc,Schl desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Kontenplan_Key03')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Kontenplan_Key03] AS EXECUTE [dbo].[up_Last_Kontenplan_Key03]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Kontenplan_Key03')
  DROP PROCEDURE [dbo].[up_Next_Kontenplan_Key03]
GO
CREATE PROCEDURE [dbo].[up_Next_Kontenplan_Key03] @oldRecId integer AS
  DECLARE @oldTyp char(1)
  DECLARE @oldKzKasse char(1)
  DECLARE @oldKzUst char(1)
  DECLARE @oldSchl char(3)
  SELECT @oldTyp = Typ, @oldKzKasse = KzKasse, @oldKzUst = KzUst, @oldSchl = Schl FROM [dbo].[ut_Kontenplan] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03)
    WHERE (Typ = @oldTyp) AND (KzKasse = @oldKzKasse) AND (KzUst = @oldKzUst) AND (Schl = @oldSchl) AND (RecId > @oldRecId)
    ORDER BY Typ,KzKasse,KzUst,Schl,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03)
    WHERE (Typ = @oldTyp) AND (KzKasse = @oldKzKasse) AND (KzUst = @oldKzUst) AND (Schl > @oldSchl)
    ORDER BY Typ,KzKasse,KzUst,Schl,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03)
    WHERE (Typ = @oldTyp) AND (KzKasse = @oldKzKasse) AND (KzUst > @oldKzUst)
    ORDER BY Typ,KzKasse,KzUst,Schl,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03)
    WHERE (Typ = @oldTyp) AND (KzKasse > @oldKzKasse)
    ORDER BY Typ,KzKasse,KzUst,Schl,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03)
    WHERE (Typ > @oldTyp)
    ORDER BY Typ,KzKasse,KzUst,Schl,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_Kontenplan_Key03')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Kontenplan_Key03] @oldRecId integer AS EXECUTE [dbo].[up_Next_Kontenplan_Key03] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Kontenplan_Key03')
  DROP PROCEDURE [dbo].[up_Prior_Kontenplan_Key03]
GO
CREATE PROCEDURE [dbo].[up_Prior_Kontenplan_Key03] @oldRecId integer AS
  DECLARE @oldTyp char(1)
  DECLARE @oldKzKasse char(1)
  DECLARE @oldKzUst char(1)
  DECLARE @oldSchl char(3)
  SELECT @oldTyp = Typ, @oldKzKasse = KzKasse, @oldKzUst = KzUst, @oldSchl = Schl FROM [dbo].[ut_Kontenplan] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03)
    WHERE (Typ = @oldTyp) AND (KzKasse = @oldKzKasse) AND (KzUst = @oldKzUst) AND (Schl = @oldSchl) AND (RecId < @oldRecId)
    ORDER BY Typ desc,KzKasse desc,KzUst desc,Schl desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03)
    WHERE (Typ = @oldTyp) AND (KzKasse = @oldKzKasse) AND (KzUst = @oldKzUst) AND (Schl < @oldSchl)
    ORDER BY Typ desc,KzKasse desc,KzUst desc,Schl desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03)
    WHERE (Typ = @oldTyp) AND (KzKasse = @oldKzKasse) AND (KzUst < @oldKzUst)
    ORDER BY Typ desc,KzKasse desc,KzUst desc,Schl desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03)
    WHERE (Typ = @oldTyp) AND (KzKasse < @oldKzKasse)
    ORDER BY Typ desc,KzKasse desc,KzUst desc,Schl desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03)
    WHERE (Typ < @oldTyp)
    ORDER BY Typ desc,KzKasse desc,KzUst desc,Schl 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_Kontenplan_Key03')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Kontenplan_Key03] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Kontenplan_Key03] @oldRecId')
GO

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

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Kontenplan_Key03')
  DROP PROCEDURE [dbo].[up_FindF_Kontenplan_Key03]
GO
CREATE PROCEDURE [dbo].[up_FindF_Kontenplan_Key03] @xKey char(250), @xLen integer AS
  DECLARE @mTyp char(1) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,1)
  DECLARE @mKzKasse char(1) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,2,1)
  DECLARE @mKzUst char(1) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,3,1)
  DECLARE @mSchl char(3) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,4,3)

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

  IF @xLen = 6 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      KzUst = @mKzUst AND 
      Schl = @mSchl
      ORDER BY Typ,KzKasse,KzUst,Schl,RecId
    RETURN
  END

  IF @xLen = 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      KzUst = @mKzUst
      ORDER BY Typ,KzKasse,KzUst,Schl,RecId
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse
      ORDER BY Typ,KzKasse,KzUst,Schl,RecId
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03) WHERE 
      Typ = @mTyp
      ORDER BY Typ,KzKasse,KzUst,Schl,RecId
    RETURN
  END

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

  IF @xLen < 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03) WHERE 
      Typ >= @mTyp AND SUBSTRING(Typ,1,LEN(@mTyp)) = @mTyp
      ORDER BY Typ,KzKasse,KzUst,Schl,RecId
    RETURN
  END

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03) WHERE 
      Typ = @mTyp AND 
      KzKasse >= @mKzKasse AND SUBSTRING(KzKasse,1,LEN(@mKzKasse)) = @mKzKasse
      ORDER BY Typ,KzKasse,KzUst,Schl,RecId
    RETURN
  END

  IF @xLen < 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      KzUst >= @mKzUst AND SUBSTRING(KzUst,1,LEN(@mKzUst)) = @mKzUst
      ORDER BY Typ,KzKasse,KzUst,Schl,RecId
    RETURN
  END

  IF @xLen < 6 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      KzUst = @mKzUst AND 
      Schl >= @mSchl AND SUBSTRING(Schl,1,LEN(@mSchl)) = @mSchl
      ORDER BY Typ,KzKasse,KzUst,Schl,RecId
    RETURN
  END

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Kontenplan_Key03')
  DROP PROCEDURE [dbo].[up_FindL_Kontenplan_Key03]
GO
CREATE PROCEDURE [dbo].[up_FindL_Kontenplan_Key03] @xKey char(250), @xLen integer AS
  DECLARE @mTyp char(1) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,1)
  DECLARE @mKzKasse char(1) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,2,1)
  DECLARE @mKzUst char(1) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,3,1)
  DECLARE @mSchl char(3) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,4,3)

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

  IF @xLen = 6 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      KzUst = @mKzUst AND 
      Schl = @mSchl
      ORDER BY Typ desc,KzKasse desc,KzUst desc,Schl desc,RecId desc
    RETURN
  END

  IF @xLen = 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      KzUst = @mKzUst
      ORDER BY Typ desc,KzKasse desc,KzUst desc,Schl desc,RecId desc
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse
      ORDER BY Typ desc,KzKasse desc,KzUst desc,Schl desc,RecId desc
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03) WHERE 
      Typ = @mTyp
      ORDER BY Typ desc,KzKasse desc,KzUst desc,Schl desc,RecId desc
    RETURN
  END

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

  IF @xLen < 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03) WHERE 
      SUBSTRING(Typ,1,LEN(@mTyp)) = @mTyp
      ORDER BY Typ desc,KzKasse desc,KzUst desc,Schl desc,RecId desc
    RETURN
  END

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03) WHERE 
      Typ = @mTyp AND 
      SUBSTRING(KzKasse,1,LEN(@mKzKasse)) = @mKzKasse
      ORDER BY Typ desc,KzKasse desc,KzUst desc,Schl desc,RecId desc
    RETURN
  END

  IF @xLen < 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      SUBSTRING(KzUst,1,LEN(@mKzUst)) = @mKzUst
      ORDER BY Typ desc,KzKasse desc,KzUst desc,Schl desc,RecId desc
    RETURN
  END

  IF @xLen < 6 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key03) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      KzUst = @mKzUst AND 
      SUBSTRING(Schl,1,LEN(@mSchl)) = @mSchl
      ORDER BY Typ desc,KzKasse desc,KzUst desc,Schl desc,RecId desc
    RETURN
  END

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


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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Kontenplan_Key04')
  DROP PROCEDURE [dbo].[up_First_Kontenplan_Key04]
GO
CREATE PROCEDURE [dbo].[up_First_Kontenplan_Key04] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04) ORDER BY Typ,KzKasse,Schl,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Kontenplan_Key04')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Kontenplan_Key04] AS EXECUTE [dbo].[up_First_Kontenplan_Key04]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Kontenplan_Key04')
  DROP PROCEDURE [dbo].[up_Last_Kontenplan_Key04]
GO
CREATE PROCEDURE [dbo].[up_Last_Kontenplan_Key04] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04) ORDER BY Typ desc,KzKasse desc,Schl desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Kontenplan_Key04')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Kontenplan_Key04] AS EXECUTE [dbo].[up_Last_Kontenplan_Key04]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Kontenplan_Key04')
  DROP PROCEDURE [dbo].[up_Next_Kontenplan_Key04]
GO
CREATE PROCEDURE [dbo].[up_Next_Kontenplan_Key04] @oldRecId integer AS
  DECLARE @oldTyp char(1)
  DECLARE @oldKzKasse char(1)
  DECLARE @oldSchl char(3)
  SELECT @oldTyp = Typ, @oldKzKasse = KzKasse, @oldSchl = Schl FROM [dbo].[ut_Kontenplan] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04)
    WHERE (Typ = @oldTyp) AND (KzKasse = @oldKzKasse) AND (Schl = @oldSchl) AND (RecId > @oldRecId)
    ORDER BY Typ,KzKasse,Schl,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04)
    WHERE (Typ = @oldTyp) AND (KzKasse = @oldKzKasse) AND (Schl > @oldSchl)
    ORDER BY Typ,KzKasse,Schl,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04)
    WHERE (Typ = @oldTyp) AND (KzKasse > @oldKzKasse)
    ORDER BY Typ,KzKasse,Schl,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04)
    WHERE (Typ > @oldTyp)
    ORDER BY Typ,KzKasse,Schl,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_Kontenplan_Key04')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Kontenplan_Key04] @oldRecId integer AS EXECUTE [dbo].[up_Next_Kontenplan_Key04] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Kontenplan_Key04')
  DROP PROCEDURE [dbo].[up_Prior_Kontenplan_Key04]
GO
CREATE PROCEDURE [dbo].[up_Prior_Kontenplan_Key04] @oldRecId integer AS
  DECLARE @oldTyp char(1)
  DECLARE @oldKzKasse char(1)
  DECLARE @oldSchl char(3)
  SELECT @oldTyp = Typ, @oldKzKasse = KzKasse, @oldSchl = Schl FROM [dbo].[ut_Kontenplan] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04)
    WHERE (Typ = @oldTyp) AND (KzKasse = @oldKzKasse) AND (Schl = @oldSchl) AND (RecId < @oldRecId)
    ORDER BY Typ desc,KzKasse desc,Schl desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04)
    WHERE (Typ = @oldTyp) AND (KzKasse = @oldKzKasse) AND (Schl < @oldSchl)
    ORDER BY Typ desc,KzKasse desc,Schl desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04)
    WHERE (Typ = @oldTyp) AND (KzKasse < @oldKzKasse)
    ORDER BY Typ desc,KzKasse desc,Schl desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04)
    WHERE (Typ < @oldTyp)
    ORDER BY Typ desc,KzKasse desc,Schl 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_Kontenplan_Key04')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Kontenplan_Key04] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Kontenplan_Key04] @oldRecId')
GO

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

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

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

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

  IF @xLen = 5 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      Schl = @mSchl
      ORDER BY Typ,KzKasse,Schl,RecId
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse
      ORDER BY Typ,KzKasse,Schl,RecId
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04) WHERE 
      Typ = @mTyp
      ORDER BY Typ,KzKasse,Schl,RecId
    RETURN
  END

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

  IF @xLen < 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04) WHERE 
      Typ >= @mTyp AND SUBSTRING(Typ,1,LEN(@mTyp)) = @mTyp
      ORDER BY Typ,KzKasse,Schl,RecId
    RETURN
  END

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04) WHERE 
      Typ = @mTyp AND 
      KzKasse >= @mKzKasse AND SUBSTRING(KzKasse,1,LEN(@mKzKasse)) = @mKzKasse
      ORDER BY Typ,KzKasse,Schl,RecId
    RETURN
  END

  IF @xLen < 5 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      Schl >= @mSchl AND SUBSTRING(Schl,1,LEN(@mSchl)) = @mSchl
      ORDER BY Typ,KzKasse,Schl,RecId
    RETURN
  END

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

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

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

  IF @xLen = 5 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      Schl = @mSchl
      ORDER BY Typ desc,KzKasse desc,Schl desc,RecId desc
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse
      ORDER BY Typ desc,KzKasse desc,Schl desc,RecId desc
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04) WHERE 
      Typ = @mTyp
      ORDER BY Typ desc,KzKasse desc,Schl desc,RecId desc
    RETURN
  END

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

  IF @xLen < 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04) WHERE 
      SUBSTRING(Typ,1,LEN(@mTyp)) = @mTyp
      ORDER BY Typ desc,KzKasse desc,Schl desc,RecId desc
    RETURN
  END

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04) WHERE 
      Typ = @mTyp AND 
      SUBSTRING(KzKasse,1,LEN(@mKzKasse)) = @mKzKasse
      ORDER BY Typ desc,KzKasse desc,Schl desc,RecId desc
    RETURN
  END

  IF @xLen < 5 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key04) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      SUBSTRING(Schl,1,LEN(@mSchl)) = @mSchl
      ORDER BY Typ desc,KzKasse desc,Schl desc,RecId desc
    RETURN
  END

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


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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Kontenplan_Key05')
  DROP PROCEDURE [dbo].[up_First_Kontenplan_Key05]
GO
CREATE PROCEDURE [dbo].[up_First_Kontenplan_Key05] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05) ORDER BY Typ,KzKasse,Konto,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Kontenplan_Key05')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Kontenplan_Key05] AS EXECUTE [dbo].[up_First_Kontenplan_Key05]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Kontenplan_Key05')
  DROP PROCEDURE [dbo].[up_Last_Kontenplan_Key05]
GO
CREATE PROCEDURE [dbo].[up_Last_Kontenplan_Key05] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05) ORDER BY Typ desc,KzKasse desc,Konto desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Kontenplan_Key05')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Kontenplan_Key05] AS EXECUTE [dbo].[up_Last_Kontenplan_Key05]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Kontenplan_Key05')
  DROP PROCEDURE [dbo].[up_Next_Kontenplan_Key05]
GO
CREATE PROCEDURE [dbo].[up_Next_Kontenplan_Key05] @oldRecId integer AS
  DECLARE @oldTyp char(1)
  DECLARE @oldKzKasse char(1)
  DECLARE @oldKonto char(8)
  SELECT @oldTyp = Typ, @oldKzKasse = KzKasse, @oldKonto = Konto FROM [dbo].[ut_Kontenplan] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05)
    WHERE (Typ = @oldTyp) AND (KzKasse = @oldKzKasse) AND (Konto = @oldKonto) AND (RecId > @oldRecId)
    ORDER BY Typ,KzKasse,Konto,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05)
    WHERE (Typ = @oldTyp) AND (KzKasse = @oldKzKasse) AND (Konto > @oldKonto)
    ORDER BY Typ,KzKasse,Konto,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05)
    WHERE (Typ = @oldTyp) AND (KzKasse > @oldKzKasse)
    ORDER BY Typ,KzKasse,Konto,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05)
    WHERE (Typ > @oldTyp)
    ORDER BY Typ,KzKasse,Konto,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_Kontenplan_Key05')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Kontenplan_Key05] @oldRecId integer AS EXECUTE [dbo].[up_Next_Kontenplan_Key05] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Kontenplan_Key05')
  DROP PROCEDURE [dbo].[up_Prior_Kontenplan_Key05]
GO
CREATE PROCEDURE [dbo].[up_Prior_Kontenplan_Key05] @oldRecId integer AS
  DECLARE @oldTyp char(1)
  DECLARE @oldKzKasse char(1)
  DECLARE @oldKonto char(8)
  SELECT @oldTyp = Typ, @oldKzKasse = KzKasse, @oldKonto = Konto FROM [dbo].[ut_Kontenplan] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05)
    WHERE (Typ = @oldTyp) AND (KzKasse = @oldKzKasse) AND (Konto = @oldKonto) AND (RecId < @oldRecId)
    ORDER BY Typ desc,KzKasse desc,Konto desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05)
    WHERE (Typ = @oldTyp) AND (KzKasse = @oldKzKasse) AND (Konto < @oldKonto)
    ORDER BY Typ desc,KzKasse desc,Konto desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05)
    WHERE (Typ = @oldTyp) AND (KzKasse < @oldKzKasse)
    ORDER BY Typ desc,KzKasse desc,Konto desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05)
    WHERE (Typ < @oldTyp)
    ORDER BY Typ desc,KzKasse desc,Konto 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_Kontenplan_Key05')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Kontenplan_Key05] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Kontenplan_Key05] @oldRecId')
GO

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

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

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

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

  IF @xLen = 10 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      Konto = @mKonto
      ORDER BY Typ,KzKasse,Konto,RecId
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse
      ORDER BY Typ,KzKasse,Konto,RecId
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05) WHERE 
      Typ = @mTyp
      ORDER BY Typ,KzKasse,Konto,RecId
    RETURN
  END

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

  IF @xLen < 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05) WHERE 
      Typ >= @mTyp AND SUBSTRING(Typ,1,LEN(@mTyp)) = @mTyp
      ORDER BY Typ,KzKasse,Konto,RecId
    RETURN
  END

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05) WHERE 
      Typ = @mTyp AND 
      KzKasse >= @mKzKasse AND SUBSTRING(KzKasse,1,LEN(@mKzKasse)) = @mKzKasse
      ORDER BY Typ,KzKasse,Konto,RecId
    RETURN
  END

  IF @xLen < 10 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      Konto >= @mKonto AND SUBSTRING(Konto,1,LEN(@mKonto)) = @mKonto
      ORDER BY Typ,KzKasse,Konto,RecId
    RETURN
  END

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

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

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

  IF @xLen = 10 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      Konto = @mKonto
      ORDER BY Typ desc,KzKasse desc,Konto desc,RecId desc
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse
      ORDER BY Typ desc,KzKasse desc,Konto desc,RecId desc
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05) WHERE 
      Typ = @mTyp
      ORDER BY Typ desc,KzKasse desc,Konto desc,RecId desc
    RETURN
  END

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

  IF @xLen < 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05) WHERE 
      SUBSTRING(Typ,1,LEN(@mTyp)) = @mTyp
      ORDER BY Typ desc,KzKasse desc,Konto desc,RecId desc
    RETURN
  END

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05) WHERE 
      Typ = @mTyp AND 
      SUBSTRING(KzKasse,1,LEN(@mKzKasse)) = @mKzKasse
      ORDER BY Typ desc,KzKasse desc,Konto desc,RecId desc
    RETURN
  END

  IF @xLen < 10 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key05) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      SUBSTRING(Konto,1,LEN(@mKonto)) = @mKonto
      ORDER BY Typ desc,KzKasse desc,Konto desc,RecId desc
    RETURN
  END

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


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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Kontenplan_Key06')
  DROP PROCEDURE [dbo].[up_First_Kontenplan_Key06]
GO
CREATE PROCEDURE [dbo].[up_First_Kontenplan_Key06] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06) ORDER BY Typ,KzKasse,Name,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Kontenplan_Key06')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Kontenplan_Key06] AS EXECUTE [dbo].[up_First_Kontenplan_Key06]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Kontenplan_Key06')
  DROP PROCEDURE [dbo].[up_Last_Kontenplan_Key06]
GO
CREATE PROCEDURE [dbo].[up_Last_Kontenplan_Key06] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06) ORDER BY Typ desc,KzKasse desc,Name desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Kontenplan_Key06')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Kontenplan_Key06] AS EXECUTE [dbo].[up_Last_Kontenplan_Key06]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Kontenplan_Key06')
  DROP PROCEDURE [dbo].[up_Next_Kontenplan_Key06]
GO
CREATE PROCEDURE [dbo].[up_Next_Kontenplan_Key06] @oldRecId integer AS
  DECLARE @oldTyp char(1)
  DECLARE @oldKzKasse char(1)
  DECLARE @oldName char(42)
  SELECT @oldTyp = Typ, @oldKzKasse = KzKasse, @oldName = Name FROM [dbo].[ut_Kontenplan] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06)
    WHERE (Typ = @oldTyp) AND (KzKasse = @oldKzKasse) AND (Name = @oldName) AND (RecId > @oldRecId)
    ORDER BY Typ,KzKasse,Name,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06)
    WHERE (Typ = @oldTyp) AND (KzKasse = @oldKzKasse) AND (Name > @oldName)
    ORDER BY Typ,KzKasse,Name,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06)
    WHERE (Typ = @oldTyp) AND (KzKasse > @oldKzKasse)
    ORDER BY Typ,KzKasse,Name,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06)
    WHERE (Typ > @oldTyp)
    ORDER BY Typ,KzKasse,Name,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_Kontenplan_Key06')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Kontenplan_Key06] @oldRecId integer AS EXECUTE [dbo].[up_Next_Kontenplan_Key06] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Kontenplan_Key06')
  DROP PROCEDURE [dbo].[up_Prior_Kontenplan_Key06]
GO
CREATE PROCEDURE [dbo].[up_Prior_Kontenplan_Key06] @oldRecId integer AS
  DECLARE @oldTyp char(1)
  DECLARE @oldKzKasse char(1)
  DECLARE @oldName char(42)
  SELECT @oldTyp = Typ, @oldKzKasse = KzKasse, @oldName = Name FROM [dbo].[ut_Kontenplan] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06)
    WHERE (Typ = @oldTyp) AND (KzKasse = @oldKzKasse) AND (Name = @oldName) AND (RecId < @oldRecId)
    ORDER BY Typ desc,KzKasse desc,Name desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06)
    WHERE (Typ = @oldTyp) AND (KzKasse = @oldKzKasse) AND (Name < @oldName)
    ORDER BY Typ desc,KzKasse desc,Name desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06)
    WHERE (Typ = @oldTyp) AND (KzKasse < @oldKzKasse)
    ORDER BY Typ desc,KzKasse desc,Name desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06)
    WHERE (Typ < @oldTyp)
    ORDER BY Typ desc,KzKasse desc,Name 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_Kontenplan_Key06')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Kontenplan_Key06] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Kontenplan_Key06] @oldRecId')
GO

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

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

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

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

  IF @xLen = 44 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      Name = @mName
      ORDER BY Typ,KzKasse,Name,RecId
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse
      ORDER BY Typ,KzKasse,Name,RecId
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06) WHERE 
      Typ = @mTyp
      ORDER BY Typ,KzKasse,Name,RecId
    RETURN
  END

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

  IF @xLen < 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06) WHERE 
      Typ >= @mTyp AND SUBSTRING(Typ,1,LEN(@mTyp)) = @mTyp
      ORDER BY Typ,KzKasse,Name,RecId
    RETURN
  END

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06) WHERE 
      Typ = @mTyp AND 
      KzKasse >= @mKzKasse AND SUBSTRING(KzKasse,1,LEN(@mKzKasse)) = @mKzKasse
      ORDER BY Typ,KzKasse,Name,RecId
    RETURN
  END

  IF @xLen < 44 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      Name >= @mName AND SUBSTRING(Name,1,LEN(@mName)) = @mName
      ORDER BY Typ,KzKasse,Name,RecId
    RETURN
  END

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

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

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

  IF @xLen = 44 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      Name = @mName
      ORDER BY Typ desc,KzKasse desc,Name desc,RecId desc
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse
      ORDER BY Typ desc,KzKasse desc,Name desc,RecId desc
    RETURN
  END

  IF @xLen = 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06) WHERE 
      Typ = @mTyp
      ORDER BY Typ desc,KzKasse desc,Name desc,RecId desc
    RETURN
  END

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

  IF @xLen < 1 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06) WHERE 
      SUBSTRING(Typ,1,LEN(@mTyp)) = @mTyp
      ORDER BY Typ desc,KzKasse desc,Name desc,RecId desc
    RETURN
  END

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06) WHERE 
      Typ = @mTyp AND 
      SUBSTRING(KzKasse,1,LEN(@mKzKasse)) = @mKzKasse
      ORDER BY Typ desc,KzKasse desc,Name desc,RecId desc
    RETURN
  END

  IF @xLen < 44 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key06) WHERE 
      Typ = @mTyp AND 
      KzKasse = @mKzKasse AND 
      SUBSTRING(Name,1,LEN(@mName)) = @mName
      ORDER BY Typ desc,KzKasse desc,Name desc,RecId desc
    RETURN
  END

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


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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Kontenplan_Key07')
  DROP PROCEDURE [dbo].[up_First_Kontenplan_Key07]
GO
CREATE PROCEDURE [dbo].[up_First_Kontenplan_Key07] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07) ORDER BY KzPrj,Typ,Konto,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Kontenplan_Key07')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Kontenplan_Key07] AS EXECUTE [dbo].[up_First_Kontenplan_Key07]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Kontenplan_Key07')
  DROP PROCEDURE [dbo].[up_Last_Kontenplan_Key07]
GO
CREATE PROCEDURE [dbo].[up_Last_Kontenplan_Key07] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07) ORDER BY KzPrj desc,Typ desc,Konto desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Kontenplan_Key07')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Kontenplan_Key07] AS EXECUTE [dbo].[up_Last_Kontenplan_Key07]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Kontenplan_Key07')
  DROP PROCEDURE [dbo].[up_Next_Kontenplan_Key07]
GO
CREATE PROCEDURE [dbo].[up_Next_Kontenplan_Key07] @oldRecId integer AS
  DECLARE @oldKzPrj integer
  DECLARE @oldTyp char(1)
  DECLARE @oldKonto char(8)
  SELECT @oldKzPrj = KzPrj, @oldTyp = Typ, @oldKonto = Konto FROM [dbo].[ut_Kontenplan] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07)
    WHERE (KzPrj = @oldKzPrj) AND (Typ = @oldTyp) AND (Konto = @oldKonto) AND (RecId > @oldRecId)
    ORDER BY KzPrj,Typ,Konto,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07)
    WHERE (KzPrj = @oldKzPrj) AND (Typ = @oldTyp) AND (Konto > @oldKonto)
    ORDER BY KzPrj,Typ,Konto,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07)
    WHERE (KzPrj = @oldKzPrj) AND (Typ > @oldTyp)
    ORDER BY KzPrj,Typ,Konto,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07)
    WHERE (KzPrj > @oldKzPrj)
    ORDER BY KzPrj,Typ,Konto,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_Kontenplan_Key07')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Kontenplan_Key07] @oldRecId integer AS EXECUTE [dbo].[up_Next_Kontenplan_Key07] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Kontenplan_Key07')
  DROP PROCEDURE [dbo].[up_Prior_Kontenplan_Key07]
GO
CREATE PROCEDURE [dbo].[up_Prior_Kontenplan_Key07] @oldRecId integer AS
  DECLARE @oldKzPrj integer
  DECLARE @oldTyp char(1)
  DECLARE @oldKonto char(8)
  SELECT @oldKzPrj = KzPrj, @oldTyp = Typ, @oldKonto = Konto FROM [dbo].[ut_Kontenplan] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07)
    WHERE (KzPrj = @oldKzPrj) AND (Typ = @oldTyp) AND (Konto = @oldKonto) AND (RecId < @oldRecId)
    ORDER BY KzPrj desc,Typ desc,Konto desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07)
    WHERE (KzPrj = @oldKzPrj) AND (Typ = @oldTyp) AND (Konto < @oldKonto)
    ORDER BY KzPrj desc,Typ desc,Konto desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07)
    WHERE (KzPrj = @oldKzPrj) AND (Typ < @oldTyp)
    ORDER BY KzPrj desc,Typ desc,Konto desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07)
    WHERE (KzPrj < @oldKzPrj)
    ORDER BY KzPrj desc,Typ desc,Konto 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_Kontenplan_Key07')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Kontenplan_Key07] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Kontenplan_Key07] @oldRecId')
GO

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

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Kontenplan_Key07')
  DROP PROCEDURE [dbo].[up_FindF_Kontenplan_Key07]
GO
CREATE PROCEDURE [dbo].[up_FindF_Kontenplan_Key07] @xKey char(250), @xLen integer AS
  DECLARE @mKzPrj integer = dbo.itcfunc_KeyToken_integer(@xKey,@xLen,1,2)
  DECLARE @mTyp char(1) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,3,1)
  DECLARE @mKonto char(8) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,4,8)

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

  IF @xLen = 11 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07) WHERE 
      KzPrj = @mKzPrj AND 
      Typ = @mTyp AND 
      Konto = @mKonto
      ORDER BY KzPrj,Typ,Konto,RecId
    RETURN
  END

  IF @xLen = 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07) WHERE 
      KzPrj = @mKzPrj AND 
      Typ = @mTyp
      ORDER BY KzPrj,Typ,Konto,RecId
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07) WHERE 
      KzPrj = @mKzPrj
      ORDER BY KzPrj,Typ,Konto,RecId
    RETURN
  END

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


  IF @xLen < 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07) WHERE 
      KzPrj = @mKzPrj AND 
      Typ >= @mTyp AND SUBSTRING(Typ,1,LEN(@mTyp)) = @mTyp
      ORDER BY KzPrj,Typ,Konto,RecId
    RETURN
  END

  IF @xLen < 11 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07) WHERE 
      KzPrj = @mKzPrj AND 
      Typ = @mTyp AND 
      Konto >= @mKonto AND SUBSTRING(Konto,1,LEN(@mKonto)) = @mKonto
      ORDER BY KzPrj,Typ,Konto,RecId
    RETURN
  END

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Kontenplan_Key07')
  DROP PROCEDURE [dbo].[up_FindL_Kontenplan_Key07]
GO
CREATE PROCEDURE [dbo].[up_FindL_Kontenplan_Key07] @xKey char(250), @xLen integer AS
  DECLARE @mKzPrj integer = dbo.itcfunc_KeyToken_integer(@xKey,@xLen,1,2)
  DECLARE @mTyp char(1) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,3,1)
  DECLARE @mKonto char(8) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,4,8)

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

  IF @xLen = 11 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07) WHERE 
      KzPrj = @mKzPrj AND 
      Typ = @mTyp AND 
      Konto = @mKonto
      ORDER BY KzPrj desc,Typ desc,Konto desc,RecId desc
    RETURN
  END

  IF @xLen = 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07) WHERE 
      KzPrj = @mKzPrj AND 
      Typ = @mTyp
      ORDER BY KzPrj desc,Typ desc,Konto desc,RecId desc
    RETURN
  END

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07) WHERE 
      KzPrj = @mKzPrj
      ORDER BY KzPrj desc,Typ desc,Konto desc,RecId desc
    RETURN
  END

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


  IF @xLen < 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07) WHERE 
      KzPrj = @mKzPrj AND 
      SUBSTRING(Typ,1,LEN(@mTyp)) = @mTyp
      ORDER BY KzPrj desc,Typ desc,Konto desc,RecId desc
    RETURN
  END

  IF @xLen < 11 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Kontenplan] WITH(INDEX = Ik_Key07) WHERE 
      KzPrj = @mKzPrj AND 
      Typ = @mTyp AND 
      SUBSTRING(Konto,1,LEN(@mKonto)) = @mKonto
      ORDER BY KzPrj desc,Typ desc,Konto desc,RecId desc
    RETURN
  END

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


