IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Lagerscheine')
	DROP PROCEDURE dbo.crp_Lagerscheine
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Lagerpos')
	DROP PROCEDURE dbo.crp_Lagerpos
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Lagerust')
	DROP PROCEDURE dbo.crp_Lagerust
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Lagerabgang')
	DROP PROCEDURE dbo.crp_Lagerabgang
GO

CREATE PROCEDURE dbo.crp_Lagerscheine
				@VonPrj   varchar(8)
                               ,@BisPrj   varchar(8)
                               ,@VonDatum varchar(10)
                               ,@BisDatum varchar(10)
                               ,@VonLief  varchar(15)
                               ,@BisLief  varchar(15)
AS
DECLARE @m_VonDatum datetime
DECLARE @m_BisDatum datetime
DECLARE @KeinVonDatum int
DECLARE @KeinBisDatum int
DECLARE @KeinVonPrj   int
DECLARE @KeinBisPrj   int
DECLARE @KeinVonLief  int
DECLARE @KeinBisLief  int
SET @KeinVonDatum = 1
SET @KeinBisDatum = 1
SET @KeinVonPrj   = 0
SET @KeinBisPrj   = 0
SET @KeinVonLief  = 0
SET @KeinBisLief  = 0
SET @m_VonDatum = GETDATE()
SET @m_BisDatum = GETDATE()

IF ISDATE(@VonDatum) = 1
BEGIN 
  SET @KeinVonDatum = 0
  SET @m_VonDatum   = CONVERT(datetime, @VonDatum)
END

IF ISDATE(@BisDatum) = 1
BEGIN 
  SET @KeinBisDatum = 0
  SET @m_BisDatum   = CONVERT(datetime, @BisDatum)
END

IF (@VonPrj IS NULL) OR (@VonPrj = 'NULL') OR (@VonPrj = '') OR (@VonPrj = '0')
  SET @KeinVonPrj = 1

IF (@BisPrj IS NULL) OR (@BisPrj = 'NULL') OR (@BisPrj = '') OR (@BisPrj = '0')
  SET @KeinBisPrj = 1
 
IF (@VonLief IS NULL) OR (@VonLief = 'NULL') OR (@VonLief = '') OR (@VonLief = '0')
  SET @KeinVonLief = 1

IF (@BisLief IS NULL) OR (@BisLief = 'NULL') OR (@BisLief = '') OR (@BisLief = '0')
  SET @KeinBisLief = 1

SELECT
   LK.BelNr ,LK.PrjNr ,LK.Datum ,LK.AdrNr ,LK.UZ ,LK.ExtBelnr ,LK.KredKto
  ,LK.Name1 ,LK.Waehrung ,LK.Eurokurs ,LK.SummeBrutto ,LK.FaelligTage 
  ,LK.SkontoTage ,LK.SkontoSatz, LK.Typ
  ,ISNULL(
    (
      SELECT SUM( U1.Netto ) 
      FROM dbo.LagerEinUst U1 
      WHERE U1.BelNr = LK.BelNr
    )
   , 0 )                     AS Netto
  ,ISNULL(
    (
      SELECT SUM( U2.Ust ) 
      FROM dbo.LagerEinUst U2 
      WHERE U2.BelNr = LK.BelNr
    )
   , 0 )                     AS Mwst
  ,AN.Nr                     AS Nr
  ,AN.Name1       	     AS F_Name1
  ,AN.Name2                  AS F_Name2
  ,AN.Name3                  AS F_Name3
  ,AN.Strasse                AS F_Strasse
  ,AN.PlzOrt                 AS F_PlzOrt
  ,AN.Telefon                AS F_Telefon
  ,AN.FaxNr                  AS F_Fax
  ,AN.Bank1_Name             AS F_Bank1_Name
  ,AN.Bank1_BLZ              AS F_Bank1_Blz
  ,AN.Bank1_Konto            AS F_Bank1_Konto
  ,AN.Bank2_Name             AS F_Bank2_Name
  ,AN.Bank2_BLZ              AS F_Bank2_Blz
  ,AN.Bank2_Konto            AS F_Bank2_Konto
FROM dbo.LagerEinKopf LK
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE ( @KeinVonLief  = 1 OR LK.AdrNr >= @VonLief    ) AND 
      ( @KeinBisLief  = 1 OR LK.AdrNr <= @BisLief    ) AND   
      ( @KeinVonPrj   = 1 OR LK.PrjNr >= @VonPrj     ) AND  
      ( @KeinBisPrj   = 1 OR LK.PrjNr <= @BisPrj     ) AND  
      ( @KeinVonDatum = 1 OR LK.Datum >= @m_VonDatum ) AND
      ( @KeinBisDatum = 1 OR LK.Datum <= @m_BisDatum ) 




GO
CREATE PROCEDURE dbo.crp_Lagerpos
				@BelNr   varchar(8)
                               ,@PrjNr   varchar(8)
AS
DECLARE @m_BelNr     int
DECLARE @KeinProjekt int
SET @m_BelNr     = 0
SET @KeinProjekt = 1

IF ISNUMERIC(@BelNr) = 1
  SET @m_BelNr = CONVERT(int, @BelNr)   

IF (@PrjNr <> 'NULL') AND (@PrjNr <> '0') AND (LEN(@PrjNr) > 0)
  SET @KeinProjekt = 0
  
SELECT
   LK.BelNr                  AS Kopf_BelNr 
  ,LK.PrjNr                  AS Kopf_PrjNr
  ,LK.Datum                  AS Kopf_Datum
  ,LK.AdrNr                  AS Kopf_AdrNr
  ,LK.UZ                     AS Kopf_UZ
  ,LK.ExtBelnr               AS Kopf_ExtBelnr
  ,LK.KredKto                AS Kopf_KredKto
  ,LK.Name1                  AS Kopf_Name1 
  ,LK.Waehrung               AS Kopf_Waehrung
  ,LK.Eurokurs               AS Kopf_Eurokurs
  ,LK.SummeBrutto            AS Kopf_SummeBrutto
  ,LK.FaelligTage            AS Kopf_FaelligTage 
  ,LK.SkontoTage             AS Kopf_SkontoTage
  ,LK.SkontoSatz             AS Kopf_SkontoSatz
  ,LK.Typ                    AS Kopf_Typ
  ,B1.VORHABEN               AS Kopf_Vorhaben
  ,B1.BETREFF1               AS Kopf_Betreff1
  ,B1.BETREFF2               AS Kopf_Betreff2
  ,B1.BETREFF3               AS Kopf_Betreff3
  ,K.AutoSatz                AS Ust_VH
  ,ME.[Name]                 AS Me_Name
  ,L.BelNr ,L.PosNr ,L.UPosNr ,L.PrjNr ,L.Lkz ,L.Artnr ,L.Bez1 ,L.Bez2 
  ,L.Merkmal ,L.LgKto ,L.Menge ,L.PreisJe ,L.BruttoEk ,L.Rabatt1 ,L.Rabatt2 
  ,L.NettoEk ,L.NeJe100 ,L.NettoGes ,L.UstSchl ,L.VerbMenge ,L.VerbNetto
  ,ISNULL(
    (
      SELECT 
        SUM( LP.Menge ) 
      FROM dbo.LagerEinPos LP
      WHERE (LP.BelNr = L.BelNr) AND (LP.PosNr = L.PosNr) AND (LP.UPosNr <> 0)
    )
   , 0)                      AS MengeAbgang                        
  ,B2.VORHABEN               AS Pos_Vorhaben
  ,B2.BETREFF1               AS Pos_Betreff1
  ,B2.BETREFF2               AS Pos_Betreff2
  ,B2.BETREFF3               AS Pos_Betreff3
  ,AN.Nr                     AS Nr
  ,AN.Name1       	     AS F_Name1
  ,AN.Name2                  AS F_Name2
  ,AN.Name3                  AS F_Name3
  ,AN.Strasse                AS F_Strasse
  ,AN.PlzOrt                 AS F_PlzOrt
  ,AN.Telefon                AS F_Telefon
  ,AN.FaxNr                  AS F_Fax
  ,AN.Bank1_Name             AS F_Bank1_Name
  ,AN.Bank1_BLZ              AS F_Bank1_Blz
  ,AN.Bank1_Konto            AS F_Bank1_Konto
  ,AN.Bank2_Name             AS F_Bank2_Name
  ,AN.Bank2_BLZ              AS F_Bank2_Blz
  ,AN.Bank2_Konto            AS F_Bank2_Konto
FROM dbo.LagerEinKopf LK
LEFT  JOIN dbo.LagerEinPos L
        ON LK.BelNr = L.BelNr
LEFT  JOIN dbo.Kontenplan K
        ON K.Schl = L.UstSchl AND K.KzUst = 'V' AND K.Typ = 'S' AND K.KzKasse = 'U'
LEFT  JOIN dbo.Stammpositionen S
        ON L.Lkz = S.Lkz AND L.Artnr = S.Artnr
LEFT  JOIN dbo.Mengeneinheiten ME
        ON ME.Nr = STR(S.Einheit,4)
LEFT  JOIN dbo.Belege B1
        ON ( LK.PrjNr = B1.PRJNR ) AND ( LK.PrjNr = B1.BELEGNR ) AND ( B1.BELEGART = ' 0' )
LEFT  JOIN dbo.Belege B2
        ON ( L.PrjNr = B2.PRJNR )  AND ( L.PrjNr = B2.BELEGNR )  AND ( B2.BELEGART = ' 0' )
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE ( LK.BelNr = @m_BelNr ) AND
      ( @KeinProjekt = 1 OR L.PrjNr = @PrjNr )  
ORDER BY L.PosNr, L.UPosNr
GO

CREATE PROCEDURE dbo.crp_Lagerust
				@BelNr varchar(8)
AS
DECLARE @m_BelNr int
SET @m_BelNr = 0

IF ISNUMERIC(@BelNr) = 1
  SET @m_BelNr = CONVERT(int, @BelNr)   

SELECT
   L.BelNr ,L.UstSchl ,L.Netto ,L.UstVH ,L.Ust ,L.Brutto 
  ,K.Typ                     AS Kto_Typ
  ,K.Konto                   AS Kto_Konto
  ,K.[Name]                  AS Kto_Name
  ,K.AutoKonto               AS Kto_AutoKonto
  ,K.AutoSatz                AS Ust_VH
  ,AN.Nr                     AS Nr
  ,AN.Name1       	     AS F_Name1
  ,AN.Name2                  AS F_Name2
  ,AN.Name3                  AS F_Name3
  ,AN.Strasse                AS F_Strasse
  ,AN.PlzOrt                 AS F_PlzOrt
  ,AN.Telefon                AS F_Telefon
  ,AN.FaxNr                  AS F_Fax
  ,AN.Bank1_Name             AS F_Bank1_Name
  ,AN.Bank1_BLZ              AS F_Bank1_Blz
  ,AN.Bank1_Konto            AS F_Bank1_Konto
  ,AN.Bank2_Name             AS F_Bank2_Name
  ,AN.Bank2_BLZ              AS F_Bank2_Blz
  ,AN.Bank2_Konto            AS F_Bank2_Konto
FROM dbo.LagerEinUst L
LEFT  JOIN dbo.Kontenplan K
        ON K.Schl = L.UstSchl AND K.KzUst = 'V' AND K.Typ = 'S' AND K.KzKasse = 'U'
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE L.BelNr = @m_BelNr
ORDER BY L.UstSchl





GO
CREATE PROCEDURE dbo.crp_Lagerabgang
				@BelNr varchar(8)
AS

DECLARE @m_BelNr int

SET @m_BelNr = 0
IF ISNUMERIC(@BelNr) = 1
    SET @m_BelNr = CONVERT(int, @BelNr)   

SELECT
   L.BelNr ,L.PosNr ,L.UPosNr ,L.PrjNr ,L.Lkz ,L.Artnr ,L.Bez1 ,L.Bez2 
  ,L.Merkmal ,L.LgKto ,L.Menge ,L.PreisJe ,L.BruttoEk ,L.Rabatt1 ,L.Rabatt2 
  ,L.NettoEk ,L.NeJe100 ,L.NettoGes ,L.UstSchl ,L.VerbMenge ,L.VerbNetto
  ,ME.[Name]                 AS Me_Name
  ,LK.BelNr                  AS Kopf_BelNr 
  ,LK.PrjNr                  AS Kopf_PrjNr
  ,LK.Datum                  AS Kopf_Datum
  ,B.KDNR                    AS Kopf_AdrNr
  ,LK.UZ                     AS Kopf_UZ
  ,LK.ExtBelnr               AS Kopf_ExtBelnr
  ,LK.KredKto                AS Kopf_KredKto
  ,B.NAME1                   AS Kopf_Name1 
  ,LK.Waehrung               AS Kopf_Waehrung
  ,LK.Eurokurs               AS Kopf_Eurokurs
  ,LK.SummeBrutto            AS Kopf_SummeBrutto
  ,LK.FaelligTage            AS Kopf_FaelligTage 
  ,LK.SkontoTage             AS Kopf_SkontoTage
  ,LK.SkontoSatz             AS Kopf_SkontoSatz
  ,LK.Typ                    AS Kopf_Typ
  ,B.VORHABEN                AS Kopf_Vorhaben
  ,B.BETREFF1                AS Kopf_Betreff1
  ,B.BETREFF2                AS Kopf_Betreff2
  ,B.BETREFF3                AS Kopf_Betreff3
  ,AN.Nr                     AS F_Nr
  ,AN.Name1       	     AS F_Name1
  ,AN.Name2                  AS F_Name2
  ,AN.Name3                  AS F_Name3
  ,AN.Strasse                AS F_Strasse
  ,AN.PlzOrt                 AS F_PlzOrt
  ,AN.Telefon                AS F_Telefon
  ,AN.FaxNr                  AS F_Fax
  ,AN.Bank1_Name             AS F_Bank1_Name
  ,AN.Bank1_BLZ              AS F_Bank1_Blz
  ,AN.Bank1_Konto            AS F_Bank1_Konto
  ,AN.Bank2_Name             AS F_Bank2_Name
  ,AN.Bank2_BLZ              AS F_Bank2_Blz
  ,AN.Bank2_Konto            AS F_Bank2_Konto
FROM dbo.LagerEinKopf LK
LEFT  JOIN dbo.LagerEinPos L
        ON LK.BelNr = L.BelNr And L.UPosNr = 0
LEFT  JOIN dbo.Stammpositionen S
        ON L.Lkz = S.Lkz AND L.Artnr = S.Artnr
LEFT  JOIN dbo.Mengeneinheiten ME
        ON ME.Nr = STR(S.Einheit,4)
LEFT  JOIN dbo.Belege B
        ON ( L.PrjNr = B.PRJNR )  AND ( L.PrjNr = B.BELEGNR )  AND ( B.BELEGART = ' 0' )
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE LK.BelNr = @m_BelNr
ORDER BY L.PrjNr, L.PosNr
GO

--*****************************************************************************************
-- Artikellangtexte
--*****************************************************************************************

IF 0 < (select count(*) from sysobjects where [xtype] = 'U' and [name] = 'crt_ArtikelTexte1')
	DROP TABLE dbo.crt_ArtikelTexte1
GO
IF 0 < (select count(*) from sysobjects where [xtype] = 'U' and [name] = 'crt_ArtikelTexte2')
	DROP TABLE dbo.crt_ArtikelTexte2
GO


CREATE TABLE dbo.crt_ArtikelTexte1 ( Lkz      char(2)      not null
 	                            ,Artnr    char(18)     not null
	                            ,Art      char(1)      not null
	                            ,LfdNr    integer      not null
	                            ,LineData varchar(160) not null )
GO
CREATE TABLE dbo.crt_ArtikelTexte2 ( Lkz      char(2)      not null
 	                            ,Artnr    char(18)     not null
	                            ,LfdNr    char(6)      not null
	                            ,LineData varchar(160) not null )
GO

IF SUBSTRING(@@VERSION,22,1) = '2' OR SUBSTRING(@@VERSION,23,1) = '2'
BEGIN
   EXECUTE('
            ALTER TABLE dbo.crt_ArtikelTexte1 ALTER COLUMN Lkz      char(2)   COLLATE database_default
            ALTER TABLE dbo.crt_ArtikelTexte1 ALTER COLUMN Artnr    char(18)  COLLATE database_default
            ALTER TABLE dbo.crt_ArtikelTexte1 ALTER COLUMN Art      char(1)   COLLATE database_default
            ALTER TABLE dbo.crt_ArtikelTexte1 ALTER COLUMN LineData char(160) COLLATE database_default
          ')
END
GO

IF SUBSTRING(@@VERSION,22,1) = '2' OR SUBSTRING(@@VERSION,23,1) = '2'
BEGIN
   EXECUTE('
            ALTER TABLE dbo.crt_ArtikelTexte2 ALTER COLUMN Lkz      char(2)   COLLATE database_default
            ALTER TABLE dbo.crt_ArtikelTexte2 ALTER COLUMN Artnr    char(18)  COLLATE database_default
            ALTER TABLE dbo.crt_ArtikelTexte2 ALTER COLUMN LfdNr    char(6)   COLLATE database_default
            ALTER TABLE dbo.crt_ArtikelTexte2 ALTER COLUMN LineData char(160) COLLATE database_default
          ')
END
GO

CREATE INDEX IkPrimary ON dbo.crt_ArtikelTexte1( Lkz,Artnr,Art,LfdNr )
GO
CREATE INDEX IkPrimary ON dbo.crt_ArtikelTexte2( Lkz,Artnr,LfdNr )
GO

IF 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'crp_MakeArtikelText_01')
	DROP PROCEDURE dbo.crp_MakeArtikelText_01
GO
CREATE PROCEDURE dbo.crp_MakeArtikelText_01
                                            @Parm_ArtLkz   char(2)
                                           ,@Parm_ArtNr    char(18)
                                           ,@Parm_Art      char(1)
                                           ,@Parm_TextArt  char(2)
                                           ,@Parm_TextLkz  char(2)
                                           ,@Parm_TextNr   char(18)
AS
  DECLARE @mLfdNr AS integer
  SET @mLfdNr = ISNULL((SELECT TOP 1 LfdNr FROM dbo.crt_ArtikelTexte1 
                        WHERE Lkz=@Parm_ArtLkz AND Artnr=@Parm_ArtNr AND Art=@Parm_Art ORDER BY LfdNr DESC),0)
  SET @mLfdNr = @mLfdNr + 1

  DECLARE   CT1 CURSOR FOR 
   SELECT   Daten,CntChar,SoftBreak+LineFeeds,Abschluss
   FROM     dbo.Langtextzeilen 
   WHERE    Art=@Parm_TextArt AND Lkz=@Parm_TextLkz AND Nr=@Parm_TextNr 
   ORDER BY LfdNr
  OPEN CT1
  
  DECLARE @mDaten  char(55)
  DECLARE @mCnt    integer
  DECLARE @mBreak  integer
  DECLARE @mEnde   integer
  DECLARE @mText   varchar(160)

  SET @mText   = ''
  FETCH NEXT FROM CT1 INTO @mDaten,@mCnt,@mBreak,@mEnde
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF (LEN(@mText)+@mCnt) > 160
    BEGIN
      INSERT INTO dbo.crt_ArtikelTexte1 (Lkz ,Artnr ,Art ,LfdNr ,LineData) VALUES (@Parm_ArtLkz ,@Parm_ArtNr ,@Parm_Art ,@mLfdNr ,@mText)
      SET @mLfdNr = @mLfdNr + 1
      SET @mText  = ''
    END

    IF (@mCnt > 0)
    BEGIN
      SET @mText = @mText + SUBSTRING(@mDaten,1,@mCnt)
    END	

    WHILE (@mBreak > 0)
    BEGIN
      INSERT INTO dbo.crt_ArtikelTexte1 (Lkz ,Artnr ,Art ,LfdNr ,LineData) VALUES (@Parm_ArtLkz ,@Parm_ArtNr ,@Parm_Art ,@mLfdNr ,@mText)
      SET @mLfdNr = @mLfdNr + 1
      SET @mText  = ''
      SET @mBreak = @mBreak - 1
    END

    IF @mEnde > 0 BREAK
    FETCH NEXT FROM CT1 INTO @mDaten,@mCnt,@mBreak,@mEnde
  END

  IF LEN(@mText) > 0
  BEGIN
      INSERT INTO dbo.crt_ArtikelTexte1 (Lkz ,Artnr ,Art ,LfdNr ,LineData) VALUES (@Parm_ArtLkz ,@Parm_ArtNr ,@Parm_Art ,@mLfdNr ,@mText)
      SET @mLfdNr = @mLfdNr + 1
      SET @mText  = ''
  END

  CLOSE CT1
  DEALLOCATE CT1
GO

IF 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'crp_MakeArtikelText_Replace')
	DROP PROCEDURE dbo.crp_MakeArtikelText_Replace
GO
CREATE PROCEDURE dbo.crp_MakeArtikelText_Replace
                                                 @Parm_ArtLkz   char(2)
                                                ,@Parm_ArtNr    char(18)
                                                ,@Parm_Repl     varchar(2000)
                                                ,@Parm_VonZeile integer
                                                ,@Parm_BisZeile integer
AS
  DECLARE   CTD CURSOR FOR 
   SELECT   LineData
   FROM     dbo.crt_ArtikelTexte1
   WHERE    Lkz=@Parm_ArtLkz AND Artnr=@Parm_ArtNr AND Art='D' 
        AND LfdNr >  STR(@Parm_VonZeile,6) 
        AND LfdNr <= STR(@Parm_BisZeile,6) 
   ORDER BY LfdNr
  OPEN CTD
  DECLARE @mSrc   varchar(160)
  DECLARE @mDst   varchar(160)
  DECLARE @mLen   integer
  DECLARE @mLenR  integer
  DECLARE @mPosR  integer
  DECLARE @mRepl  varchar(2000)

  SET @mPosR = 1
  SET @mLenR = LEN(@Parm_Repl)
  SET @mRepl = ''

  WHILE @mPosR <= @mLenR
  BEGIN
    IF SUBSTRING(@Parm_Repl,@mPosR,1) <> '$'
        SET @mRepl = @mRepl + SUBSTRING(@Parm_Repl,@mPosR,1)
    SET @mPosR = @mPosR + 1
  END

  SET @mPosR = 1
  SET @mLenR = LEN(@mRepl)

  FETCH NEXT FROM CTD INTO @mSrc
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @mDst = ''
    SET @mLen = LEN(@mSrc)
    WHILE @mLen > 0
    BEGIN
      IF (SUBSTRING(@mSrc,1,1) = '$') AND (@mPosR <= @mLenR)
        BEGIN
          SET @mDst  = @mDst  + SUBSTRING(@mRepl,@mPosR,1) 
          SET @mPosR = @mPosR + 1
        END
      ELSE
        BEGIN
          SET @mDst = @mDst + SUBSTRING(@mSrc,1,1)
        END

      SET @mLen = @mLen - 1
      IF  @mLen > 0
          SET @mSrc = SUBSTRING(@mSrc,2,@mLen) 
    END
    UPDATE dbo.crt_ArtikelTexte1 SET LineData = @mDst WHERE CURRENT OF CTD
    FETCH NEXT FROM CTD INTO @mSrc
  END

  CLOSE CTD
  DEALLOCATE CTD
GO

IF 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'crp_MakeArtikelText_D')
	DROP PROCEDURE dbo.crp_MakeArtikelText_D
GO
CREATE PROCEDURE dbo.crp_MakeArtikelText_D
                                           @Parm_ArtLkz   char(2)
                                          ,@Parm_ArtNr    char(18)
AS
  DECLARE   CT2 CURSOR FOR 
   SELECT   KENNUNG,LKZTEXTNR,TEXTNR,TEXTZEILE
   FROM     Dimensionstexte
   WHERE    LKZ=@Parm_ArtLkz AND ARTNR=@Parm_ArtNr 
   ORDER BY LFDNR
  OPEN CT2

  DECLARE @mKennung   char(1)
  DECLARE @mLkz       char(2)
  DECLARE @mNr        char(18)
  DECLARE @mText      char(42)
  DECLARE @mToFetch   integer
  DECLARE @mLfdNr     integer
  SET @mLfdNr = 1

  DECLARE @mRepl   varchar(2000)
  DECLARE @mTmpVon integer
  DECLARE @mTmpBis integer
  DECLARE @mLkz2   char(2)
  DECLARE @mNr2    char(18)
  DECLARE @mStatus integer
   
  FETCH NEXT FROM CT2 INTO @mKennung,@mLkz,@mNr,@mText
  SET @mStatus = @@FETCH_STATUS
  WHILE @mStatus = 0
  BEGIN
    SET  @mToFetch = 1

    IF @mKennung = 'F'
    BEGIN
      INSERT INTO dbo.crt_ArtikelTexte1 (Lkz, Artnr, Art, LfdNr, LineData) VALUES (@Parm_ArtLkz, @Parm_ArtNr, 'D', @mLfdNr, @mText)
      SET @mLfdNr  = @mLfdNr + @@ROWCOUNT
    END

    IF @mKennung = 'T'
    BEGIN
      EXECUTE dbo.crp_MakeArtikelText_01 @Parm_ArtLkz, @Parm_ArtNr, 'D', '11', @mLkz, @mNr
    END

    IF @mKennung = 'E'
    BEGIN
      SET @mTmpVon   = ISNULL((SELECT TOP 1 LfdNr FROM dbo.crt_ArtikelTexte1 
                               WHERE Lkz=@Parm_ArtLkz AND Artnr=@Parm_ArtNr AND Art='D' ORDER BY LfdNr DESC),0)
      SET @mToFetch  = 0    
      SET @mLkz2     = @mLkz
      SET @mNr2      = @mNr
      SET @mRepl     = RTRIM(@mText)

      EXECUTE dbo.crp_MakeArtikelText_01 @Parm_ArtLkz, @Parm_ArtNr, 'D', '14', @mLkz, @mNr
      FETCH NEXT FROM CT2 INTO @mKennung,@mLkz,@mNr,@mText
      SET @mStatus = @@FETCH_STATUS
      WHILE (@mStatus = 0) AND (@mKennung = 'E') AND (@mLkz = @mLkz2) AND (@mNr = @mNr2)
      BEGIN
        SET @mRepl   = @mRepl + RTRIM(@mText)
	FETCH NEXT FROM CT2 INTO @mKennung,@mLkz,@mNr,@mText
        SET @mStatus = @@FETCH_STATUS
      END
      SET @mTmpBis = ISNULL((SELECT TOP 1 LfdNr FROM dbo.crt_ArtikelTexte1 
                             WHERE Lkz=@Parm_ArtLkz AND Artnr=@Parm_ArtNr AND Art='D' ORDER BY LfdNr DESC),0)
      EXECUTE dbo.crp_MakeArtikelText_Replace @Parm_ArtLkz, @Parm_ArtNr, @mRepl, @mTmpVon, @mTmpBis
    END

    IF @mToFetch > 0
    BEGIN
      FETCH NEXT FROM CT2 INTO @mKennung,@mLkz,@mNr,@mText
      SET @mStatus = @@FETCH_STATUS
    END
  END

  CLOSE CT2
  DEALLOCATE CT2
GO

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

CREATE PROCEDURE dbo.crp_MakeArtikelText_L
                                           @Parm_Lkz      char(2)
                                          ,@Parm_Artnr    char(18)
AS
  DECLARE @mTextkz    integer
  DECLARE @mLkztextnr varchar(2)
  DECLARE @mTextnr    varchar(18)
  DECLARE @mTextart   char(2)

  SET @mTextart = '11'
  SET @mTextkz    = ISNULL((SELECT  TOP 1 Textkz    FROM dbo.Stammpositionen WHERE Lkz = @Parm_Lkz AND Artnr = @Parm_Artnr),0)
  SET @mLkztextnr = ISNULL((SELECT  TOP 1 Lkztextnr FROM dbo.Stammpositionen WHERE Lkz = @Parm_Lkz AND Artnr = @Parm_Artnr),'')
  SET @mTextnr    = ISNULL((SELECT  TOP 1 Textnr    FROM dbo.Stammpositionen WHERE Lkz = @Parm_Lkz AND Artnr = @Parm_Artnr),'')

  IF @@ROWCOUNT <> 0
  BEGIN
    IF @mTextkz = 2 BEGIN
        SET @mTextart = '13'
        SET @mLkztextnr = @Parm_Lkz
        SET @mTextnr    = @Parm_Artnr
    END
    IF @mLkztextnr = '' BEGIN
        SET @mLkztextnr = @Parm_Lkz
    END

    EXECUTE crp_MakeArtikelText_01  @Parm_Lkz ,@Parm_Artnr, 'L', @mTextart, @mLkztextnr, @mTextnr
  END
GO

IF 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'crp_MakeArtikelText_K1')
	DROP PROCEDURE dbo.crp_MakeArtikelText_K1
GO
CREATE PROCEDURE dbo.crp_MakeArtikelText_K1
                                           @Parm_Lkz      char(2)
                                          ,@Parm_Artnr    char(18)
AS
  DECLARE @mText varchar(42)
  SET @mText = (SELECT TOP 1 RTRIM(Bez1) FROM Stammpositionen WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr)

  IF @mText IS NULL  RETURN
  IF LEN(@mText) = 0 RETURN
  INSERT INTO dbo.crt_ArtikelTexte1 (Lkz,Artnr,Art,LfdNr,LineData) VALUES (@Parm_Lkz,@Parm_Artnr,'1',1,@mText)
GO


IF 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'crp_MakeArtikelText_K2')
	DROP PROCEDURE dbo.crp_MakeArtikelText_K2
GO
CREATE PROCEDURE dbo.crp_MakeArtikelText_K2
                                           @Parm_Lkz      char(2)
                                          ,@Parm_Artnr    char(18)
AS
  DECLARE @mText varchar(42)
  SET @mText = (SELECT TOP 1 RTRIM(Bez2) FROM Stammpositionen WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr)

  IF @mText IS NULL  RETURN
  IF LEN(@mText) = 0 RETURN
  INSERT INTO dbo.crt_ArtikelTexte1 (Lkz,Artnr,Art,LfdNr,LineData) VALUES (@Parm_Lkz,@Parm_Artnr,'2',1,@mText)
GO

IF 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'crp_MakeArtikelText_K3')
	DROP PROCEDURE dbo.crp_MakeArtikelText_K3
GO
CREATE PROCEDURE dbo.crp_MakeArtikelText_K3
                                           @Parm_Lkz      char(2)
                                          ,@Parm_Artnr    char(18)
AS
  DECLARE @mText varchar(42)
  SET @mText = (SELECT TOP 1 RTRIM(Merkmal) FROM Stammpositionen WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr)

  IF @mText IS NULL  RETURN
  IF LEN(@mText) = 0 RETURN
  INSERT INTO dbo.crt_ArtikelTexte1 (Lkz,Artnr,Art,LfdNr,LineData) VALUES (@Parm_Lkz,@Parm_Artnr,'3',1,@mText)
GO








IF 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'crp_MakeArtikelLangtext')
	DROP PROCEDURE dbo.crp_MakeArtikelLangtext
GO
CREATE PROCEDURE dbo.crp_MakeArtikelLangtext
                                             @Parm_Lkz     char(2)
                                            ,@Parm_Artnr   char(18)
AS
  DECLARE @mKzdrk1     integer
  DECLARE @mLfdNr      integer
  SET     @mLfdNr    = 100000

  SET @mKzdrk1 = (SELECT TOP 1 Kzdrk1 FROM Stammpositionen WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr)

  DELETE FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr
  DELETE FROM dbo.crt_ArtikelTexte2 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr

  IF @mKzdrk1 IS NULL
  BEGIN
    RETURN
  END

  EXECUTE dbo.crp_MakeArtikelText_K1  @Parm_Lkz, @Parm_Artnr
  EXECUTE dbo.crp_MakeArtikelText_K2  @Parm_Lkz, @Parm_Artnr
  EXECUTE dbo.crp_MakeArtikelText_K3  @Parm_Lkz, @Parm_Artnr
  EXECUTE dbo.crp_MakeArtikelText_L   @Parm_Lkz, @Parm_Artnr
  EXECUTE dbo.crp_MakeArtikelText_D   @Parm_Lkz, @Parm_Artnr


  ------------------------------------------ 1+2
  IF @mKzdrk1 = 0
  BEGIN
    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='1'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT

    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='2'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT
    RETURN
  END

  ------------------------------------------ L
  IF @mKzdrk1 = 1    
  BEGIN
    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='L'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT
    RETURN
  END


  ------------------------------------------ 1+D
  IF @mKzdrk1 = 2    
  BEGIN
    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='1'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT

    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='D'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT
    RETURN
  END

  ------------------------------------------ L+D
  IF @mKzdrk1 = 3    
  BEGIN
    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='L'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT

    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='D'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT
    RETURN
  END

  ------------------------------------------ 1+3+L
  IF @mKzdrk1 = 4    
  BEGIN
    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='1'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT

    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='3'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT

    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='L'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT
    RETURN
  END

  ------------------------------------------ 1+3+D
  IF @mKzdrk1 = 5
  BEGIN
    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='1'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT

    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='3'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT

    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='D'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT
    RETURN
  END

  ------------------------------------------ 1+3+L+D
  IF @mKzdrk1 = 6
  BEGIN
    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='1'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT

    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='3'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT

    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='L'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT

    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='D'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT
    RETURN
  END

  ------------------------------------------ 1+2+3
  IF @mKzdrk1 = 7
  BEGIN
    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='1'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT

    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='2'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT

    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='3'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT
    RETURN
  END

  ------------------------------------------ 1+2+L+3
  IF @mKzdrk1 = 8
  BEGIN
    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='1'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT

    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='2'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT

    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='L'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT

    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='3'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT
    RETURN
  END

  ------------------------------------------ L+3
  IF @mKzdrk1 = 9
  BEGIN
    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='L'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT

    INSERT INTO dbo.crt_ArtikelTexte2 
    SELECT Lkz, Artnr, STR(@mLfdNr + LfdNr,6), LineData FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr AND Art='3'
    SET @mLfdNr = @mLfdNr + @@ROWCOUNT
    RETURN
  END
GO

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

CREATE PROCEDURE dbo.crp_ArtikelLangtext
        @VonArtNr     char(18)
       ,@BisArtNr     char(18)
AS
DECLARE @mArtnr char(18)
DECLARE @mLkz   char(2)
IF @VonArtNr = @BisArtNr And @VonArtNr <> 'NULL'
BEGIN
  set @mArtnr = SUBSTRING(@VonArtNr,3,Len(@VonArtNr)-2)
  set @mLkz   = LEFT(@VonArtNr,2)

  EXECUTE dbo.crp_MakeArtikelLangtext @mLkz, @mArtnr
  SELECT Lkz, Artnr, LfdNr, LineData FROM dbo.crt_ArtikelTexte2 WHERE Lkz=@mLkz AND Artnr=@mArtnr

  DELETE FROM dbo.crt_ArtikelTexte1 WHERE Lkz=@mLkz AND Artnr=@mArtnr
  DELETE FROM dbo.crt_ArtikelTexte2 WHERE Lkz=@mLkz AND Artnr=@mArtnr
END
ELSE
  SELECT '' As Lkz, '' As Artnr, '' As LfdNr, '' As LineData
GO


