IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_PS01')
	DROP PROCEDURE dbo.crp_PS01
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_PS02')
	DROP PROCEDURE dbo.crp_PS02
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_PS03')
	DROP PROCEDURE dbo.crp_PS03
GO



CREATE PROCEDURE dbo.crp_PS01
			 @mNRHW1 char(15) = ''
			,@mNRHW2 char(15) = ''
			,@mNRHW3 char(15) = ''
			,@mNRHW4 char(15) = ''
                        ,@mLvNr  char(1)  = ''
AS

DECLARE @ArtNr  char(20)
DECLARE @PosNr  char(10)
DECLARE @LvNr   char(18)
DECLARE @Brutto decimal(17,2)
DECLARE @Netto  decimal(17,2)
DECLARE @Rab1   decimal(17,2)
DECLARE @Rab2   decimal(17,2)
DECLARE @Text   char(50)
DECLARE @NRHW   char(10)
DECLARE @Menge  decimal(17,2)
DECLARE @Me     char(10)

DECLARE @lkz1   char(40)
DECLARE @lkz2   char(40)
DECLARE @lkz3   char(40)
DECLARE @lkz4   char(40)

SET @lkz1 = ISNULL((SELECT TOP 1 A.Name1 FROM dbo.Bestellungen B
                    INNER JOIN dbo.Adressen A
                           ON (A.DESTTYP = ' 3') AND (A.DESTDEBNR = B.KDNRGH) And (B.NRHW = @mNRHW1)  
                   ), '')
SET @lkz2 = ISNULL((SELECT TOP 1 A.Name1 FROM dbo.Bestellungen B
                    INNER JOIN dbo.Adressen A
                           ON (A.DESTTYP = ' 3') AND (A.DESTDEBNR = B.KDNRGH) And (B.NRHW = @mNRHW2)  
                   ), '')
SET @lkz3 = ISNULL((SELECT TOP 1 A.Name1 FROM dbo.Bestellungen B
                    INNER JOIN dbo.Adressen A
                           ON (A.DESTTYP = ' 3') AND (A.DESTDEBNR = B.KDNRGH) And (B.NRHW = @mNRHW3)  
                   ), '')
SET @lkz4 = ISNULL((SELECT TOP 1 A.Name1 FROM dbo.Bestellungen B
                    INNER JOIN dbo.Adressen A
                           ON (A.DESTTYP = ' 3') AND (A.DESTDEBNR = B.KDNRGH) And (B.NRHW = @mNRHW4)  
                   ), '')

CREATE TABLE dbo.#Tmp (
                       artnr   char(20)       ,posnr   char(10)       ,menge   float          ,me      char(10),
                       lkz1    char(40)       ,lkz2    char(40)       ,lkz3    char(40)       ,lkz4    char(40),
                       brutto1 decimal(17,2)  ,brutto2 decimal(17,2)  ,brutto3 decimal(17,2)  ,brutto4 decimal(17,2),
                       netto1  decimal(17,2)  ,netto2  decimal(17,2)  ,netto3  decimal(17,2)  ,netto4  decimal(17,2),
                       rab11   decimal(17,2)  ,rab12   decimal(17,2)  ,rab13   decimal(17,2)  ,rab14   decimal(17,2),
                       rab21   decimal(17,2)  ,rab22   decimal(17,2)  ,rab23   decimal(17,2)  ,rab24   decimal(17,2),
                       text1   char(50)       ,text2   char(50)       ,text3   char(50)       ,text4   char(50),
                       bst1    char(10)       ,bst2    char(10)       ,bst3    char(10)       ,bst4    char(10),
                       lvnr    char(18)       ,txt     char(50)
                      )
IF SUBSTRING(@@VERSION,22,1) = '2' OR SUBSTRING(@@VERSION,23,1) = '2'
BEGIN
  execute('
            ALTER TABLE dbo.#Tmp ALTER COLUMN artnr  char(20) COLLATE database_default 
            ALTER TABLE dbo.#Tmp ALTER COLUMN posnr  char(10) COLLATE database_default 
            ALTER TABLE dbo.#Tmp ALTER COLUMN me     char(10) COLLATE database_default 
            ALTER TABLE dbo.#Tmp ALTER COLUMN lkz1   char(40) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN lkz2   char(40) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN lkz3   char(40) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN lkz4   char(40) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN text1  char(50) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN text2  char(50) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN text3  char(50) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN text4  char(50) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN bst1   char(10) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN bst2   char(10) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN bst3   char(10) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN bst4   char(10) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN lvnr   char(18) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN txt    char(50) COLLATE database_default
        ')
END

--**************************************(Nach LvNummer)******************************************

IF @mLvNr = '1' Or @mLvNr = 'j' Or @mLvNr = 'J'
BEGIN
INSERT INTO dbo.#Tmp (
                  artnr   ,posnr   ,menge   ,me, 
                  lkz1    ,lkz2    ,lkz3    ,lkz4,    
                  brutto1 ,brutto2 ,brutto3 ,brutto4 ,netto1 ,netto2 ,netto3 ,netto4,
                  rab11   ,rab12   ,rab13   ,rab14   ,rab21  ,rab22  ,rab23  ,rab24,
                  text1   ,text2   ,text3   ,text4   ,bst1   ,bst2   ,bst3   ,bst4,
                  lvnr
                 )
       SELECT '', '', 0, '',
              @lkz1, @lkz2, @lkz3, @lkz4,  
              0,0,0,0,  0,0,0,0,  0,0,0,0,  0,0,0,0,  '','','','',  
              @mNRHW1,@mNRHW2,@mNRHW3,@mNRHW4, 
              BP.LVNR 
       FROM  dbo.Bestellungen B
       INNER JOIN dbo.Bestellpositionen BP 
               ON B.NRHW = BP.NRHW
       WHERE B.NRHW = @mNRHW1 Or B.NRHW = @mNRHW2 Or B.NRHW = @mNRHW3 Or B.NRHW = @mNRHW4 
       GROUP BY BP.LVNR 

  DECLARE art_cursor CURSOR FOR 
  SELECT BP.NRHW, BP.POSNRHW, BP.LVNR, BP.ARTNR, BP.MENGE, M.[Name], 
         BP.BRUTTO, BP.NETTO, BP.RAB1, BP.RAB2, BP.TEXT1 
       FROM  dbo.Bestellungen B
       INNER JOIN dbo.Bestellpositionen BP 
               ON B.NRHW = BP.NRHW
       LEFT  JOIN dbo.Mengeneinheiten M 
               ON BP.ME = M.Nr
       WHERE B.NRHW = @mNRHW1 Or B.NRHW = @mNRHW2 Or B.NRHW = @mNRHW3 Or B.NRHW = @mNRHW4 
                  
  OPEN art_cursor
  FETCH NEXT FROM art_cursor
  INTO @NRHW, @PosNr, @LvNr, @ArtNr, @Menge, @Me, @Brutto, @Netto, @Rab1, @Rab2, @Text
  WHILE @@FETCH_STATUS = 0
  BEGIN    
       UPDATE dbo.#Tmp 
       	 SET 
              artnr = ISNULL(@ArtNr ,'')
	     ,posnr = ISNULL(@PosNr ,'')
             ,menge = ISNULL(@Menge ,0 )
	     ,me    = ISNULL(@Me    ,'')
	     ,txt   = ISNULL(@Text  ,'')
             
             ,brutto1  = CASE @NRHW WHEN @mNRHW1 THEN ISNULL(@Brutto ,0)   ELSE brutto1 END
	     ,netto1   = CASE @NRHW WHEN @mNRHW1 THEN ISNULL(@Netto  ,0)   ELSE netto1  END
             ,rab11    = CASE @NRHW WHEN @mNRHW1 THEN ISNULL(@Rab1   ,0)   ELSE rab11   END
             ,rab21    = CASE @NRHW WHEN @mNRHW1 THEN ISNULL(@Rab2   ,0)   ELSE rab21   END
             ,text1    = CASE @NRHW WHEN @mNRHW1 THEN ISNULL(@Text   ,'')  ELSE text1   END

             ,brutto2  = CASE @NRHW WHEN @mNRHW2 THEN ISNULL(@Brutto ,0)   ELSE brutto2 END
	     ,netto2   = CASE @NRHW WHEN @mNRHW2 THEN ISNULL(@Netto  ,0)   ELSE netto2  END
             ,rab12    = CASE @NRHW WHEN @mNRHW2 THEN ISNULL(@Rab1   ,0)   ELSE rab12   END
             ,rab22    = CASE @NRHW WHEN @mNRHW2 THEN ISNULL(@Rab2   ,0)   ELSE rab22   END
             ,text2    = CASE @NRHW WHEN @mNRHW2 THEN ISNULL(@Text   ,'')  ELSE text2   END

             ,brutto3  = CASE @NRHW WHEN @mNRHW3 THEN ISNULL(@Brutto ,0)   ELSE brutto3 END
	     ,netto3   = CASE @NRHW WHEN @mNRHW3 THEN ISNULL(@Netto  ,0)   ELSE netto3  END
             ,rab13    = CASE @NRHW WHEN @mNRHW3 THEN ISNULL(@Rab1   ,0)   ELSE rab13   END
             ,rab23    = CASE @NRHW WHEN @mNRHW3 THEN ISNULL(@Rab2   ,0)   ELSE rab23   END
             ,text3    = CASE @NRHW WHEN @mNRHW3 THEN ISNULL(@Text   ,'')  ELSE text3   END

             ,brutto4  = CASE @NRHW WHEN @mNRHW4 THEN ISNULL(@Brutto ,0)   ELSE brutto4 END
	     ,netto4   = CASE @NRHW WHEN @mNRHW4 THEN ISNULL(@Netto  ,0)   ELSE netto4  END
             ,rab14    = CASE @NRHW WHEN @mNRHW4 THEN ISNULL(@Rab1   ,0)   ELSE rab14   END
             ,rab24    = CASE @NRHW WHEN @mNRHW4 THEN ISNULL(@Rab2   ,0)   ELSE rab24   END
             ,text4    = CASE @NRHW WHEN @mNRHW4 THEN ISNULL(@Text   ,'')  ELSE text4   END
         WHERE lvnr = @LvNr

    FETCH NEXT FROM art_cursor
    INTO @NRHW, @PosNr, @LvNr, @ArtNr, @Menge, @Me, @Brutto, @Netto, @Rab1, @Rab2, @Text
  END
  CLOSE art_cursor
  DEALLOCATE art_cursor
END
ELSE
--**************************************(Nach Artikelnummer)******************************************
BEGIN
INSERT INTO dbo.#Tmp (
                  artnr   ,posnr   ,menge   ,me, 
                  lkz1    ,lkz2    ,lkz3    ,lkz4,    
                  brutto1 ,brutto2 ,brutto3 ,brutto4 ,netto1 ,netto2 ,netto3 ,netto4,
                  rab11   ,rab12   ,rab13   ,rab14   ,rab21  ,rab22  ,rab23  ,rab24,
                  text1   ,text2   ,text3   ,text4   ,bst1   ,bst2   ,bst3   ,bst4,
                  lvnr
                 )
       SELECT BP.ARTNR, '', 0, '',
              @lkz1, @lkz2, @lkz3, @lkz4,  
              0,0,0,0,  0,0,0,0,  0,0,0,0,  0,0,0,0,  '','','','',  
              @mNRHW1, @mNRHW2, @mNRHW3, @mNRHW4,  '' 
       FROM  dbo.Bestellungen B
       INNER JOIN dbo.Bestellpositionen BP 
               ON B.NRHW = BP.NRHW
       WHERE B.NRHW = @mNRHW1 Or B.NRHW = @mNRHW2 Or B.NRHW = @mNRHW3 Or B.NRHW = @mNRHW4 
       GROUP BY BP.ARTNR 

  DECLARE art_cursor CURSOR FOR 
  SELECT BP.NRHW, BP.POSNRHW, BP.LVNR, BP.ARTNR, BP.MENGE, M.[Name], 
         BP.BRUTTO, BP.NETTO, BP.RAB1, BP.RAB2, BP.TEXT1 
       FROM  dbo.Bestellungen B
       INNER JOIN dbo.Bestellpositionen BP 
               ON B.NRHW = BP.NRHW
       LEFT  JOIN dbo.Mengeneinheiten M 
               ON BP.ME = M.Nr
       WHERE B.NRHW = @mNRHW1 Or B.NRHW = @mNRHW2 Or B.NRHW = @mNRHW3 Or B.NRHW = @mNRHW4 
                  
  OPEN art_cursor
  FETCH NEXT FROM art_cursor
  INTO @NRHW, @PosNr, @LvNr, @ArtNr, @Menge, @Me, @Brutto, @Netto, @Rab1, @Rab2, @Text
  WHILE @@FETCH_STATUS = 0
  BEGIN    
       UPDATE dbo.#Tmp 
       	 SET 
	      posnr = ISNULL(@PosNr ,'')
	     ,lvnr  = ISNULL(@LvNr  ,'')
             ,menge = ISNULL(@Menge ,0 )
	     ,me    = ISNULL(@Me    ,'')
	     ,txt   = ISNULL(@Text  ,'')
             
             ,brutto1  = CASE @NRHW WHEN @mNRHW1 THEN ISNULL(@Brutto ,0)   ELSE brutto1 END
	     ,netto1   = CASE @NRHW WHEN @mNRHW1 THEN ISNULL(@Netto  ,0)   ELSE netto1  END
             ,rab11    = CASE @NRHW WHEN @mNRHW1 THEN ISNULL(@Rab1   ,0)   ELSE rab11   END
             ,rab21    = CASE @NRHW WHEN @mNRHW1 THEN ISNULL(@Rab2   ,0)   ELSE rab21   END
             ,text1    = CASE @NRHW WHEN @mNRHW1 THEN ISNULL(@Text   ,'')  ELSE text1   END

             ,brutto2  = CASE @NRHW WHEN @mNRHW2 THEN ISNULL(@Brutto ,0)   ELSE brutto2 END
	     ,netto2   = CASE @NRHW WHEN @mNRHW2 THEN ISNULL(@Netto  ,0)   ELSE netto2  END
             ,rab12    = CASE @NRHW WHEN @mNRHW2 THEN ISNULL(@Rab1   ,0)   ELSE rab12   END
             ,rab22    = CASE @NRHW WHEN @mNRHW2 THEN ISNULL(@Rab2   ,0)   ELSE rab22   END
             ,text2    = CASE @NRHW WHEN @mNRHW2 THEN ISNULL(@Text   ,'')  ELSE text2   END

             ,brutto3  = CASE @NRHW WHEN @mNRHW3 THEN ISNULL(@Brutto ,0)   ELSE brutto3 END
	     ,netto3   = CASE @NRHW WHEN @mNRHW3 THEN ISNULL(@Netto  ,0)   ELSE netto3  END
             ,rab13    = CASE @NRHW WHEN @mNRHW3 THEN ISNULL(@Rab1   ,0)   ELSE rab13   END
             ,rab23    = CASE @NRHW WHEN @mNRHW3 THEN ISNULL(@Rab2   ,0)   ELSE rab23   END
             ,text3    = CASE @NRHW WHEN @mNRHW3 THEN ISNULL(@Text   ,'')  ELSE text3   END

             ,brutto4  = CASE @NRHW WHEN @mNRHW4 THEN ISNULL(@Brutto ,0)   ELSE brutto4 END
	     ,netto4   = CASE @NRHW WHEN @mNRHW4 THEN ISNULL(@Netto  ,0)   ELSE netto4  END
             ,rab14    = CASE @NRHW WHEN @mNRHW4 THEN ISNULL(@Rab1   ,0)   ELSE rab14   END
             ,rab24    = CASE @NRHW WHEN @mNRHW4 THEN ISNULL(@Rab2   ,0)   ELSE rab24   END
             ,text4    = CASE @NRHW WHEN @mNRHW4 THEN ISNULL(@Text   ,'')  ELSE text4   END
         WHERE artnr = @ArtNr

    FETCH NEXT FROM art_cursor
    INTO @NRHW, @PosNr, @LvNr, @ArtNr, @Menge, @Me, @Brutto, @Netto, @Rab1, @Rab2, @Text
  END
  CLOSE art_cursor
  DEALLOCATE art_cursor
END
--****************************************************************************************************

SELECT
         T.artnr   ,T.posnr   ,T.menge   ,T.me
        ,T.lkz1    ,T.lkz2    ,T.lkz3    ,T.lkz4    
        ,T.brutto1 ,T.brutto2 ,T.brutto3 ,T.brutto4 ,T.netto1 ,T.netto2 ,T.netto3 ,T.netto4  
        ,T.rab11   ,T.rab12   ,T.rab13   ,T.rab14   ,T.rab21  ,T.rab22  ,T.rab23  ,T.rab24
        ,T.text1   ,T.text2   ,T.text3   ,T.text4   ,T.bst1   ,T.bst2   ,T.bst3   ,T.bst4
        ,T.lvnr    ,T.txt
	,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.#Tmp T
LEFT JOIN dbo.Anschriften AN
       ON AN.Nr = '01'





--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$


GO
CREATE PROCEDURE dbo.crp_PS02
			 @mNRHW char(15) = ''
			,@mLKZ1 char(2)  = ''
			,@mLKZ2 char(2)  = ''
			,@mLKZ3 char(2)  = ''
AS

DECLARE @ArtNr  char(20)
DECLARE @PosNr  char(10)
DECLARE @LvNr   char(18)
DECLARE @Brutto decimal(17,2)
DECLARE @Netto  decimal(17,2)
DECLARE @Rab1   decimal(17,2)
DECLARE @Rab2   decimal(17,2)
DECLARE @Text   char(50)
DECLARE @NRHW   char(10)
DECLARE @Menge  decimal(17,2)
DECLARE @Me     char(10)

DECLARE @lkz1   char(40)
DECLARE @lkz2   char(40)
DECLARE @lkz3   char(40)
DECLARE @lkz4   char(40)

SET @lkz1 = ISNULL((SELECT TOP 1 A.Name1 FROM dbo.Bestellungen B
                    INNER JOIN dbo.Adressen A
                           ON (A.DESTTYP = ' 3') AND (A.DESTDEBNR = B.KDNRGH) And (B.NRHW = @mNRHW)  
                   ), '')
SET @lkz2 = ISNULL((SELECT TOP 1 A.Name1 FROM dbo.Adressen A
                    WHERE (A.DESTTYP = ' 3') AND (A.LkzImport = @mLKZ1)  
                   ), '')
SET @lkz3 = ISNULL((SELECT TOP 1 A.Name1 FROM dbo.Adressen A
                    WHERE (A.DESTTYP = ' 3') AND (A.LkzImport = @mLKZ2)  
                   ), '')
SET @lkz4 = ISNULL((SELECT TOP 1 A.Name1 FROM dbo.Adressen A
                    WHERE (A.DESTTYP = ' 3') AND (A.LkzImport = @mLKZ3)  
                   ), '')

CREATE TABLE dbo.#Tmp (
                       artnr   char(20)       ,posnr   char(10)       ,menge   float          ,me      char(10),
                       lkz1    char(40)       ,lkz2    char(40)       ,lkz3    char(40)       ,lkz4    char(40),
                       brutto1 decimal(17,2)  ,brutto2 decimal(17,2)  ,brutto3 decimal(17,2)  ,brutto4 decimal(17,2),
                       netto1  decimal(17,2)  ,netto2  decimal(17,2)  ,netto3  decimal(17,2)  ,netto4  decimal(17,2),
                       rab11   decimal(17,2)  ,rab12   decimal(17,2)  ,rab13   decimal(17,2)  ,rab14   decimal(17,2),
                       rab21   decimal(17,2)  ,rab22   decimal(17,2)  ,rab23   decimal(17,2)  ,rab24   decimal(17,2),
                       text1   char(50)       ,text2   char(50)       ,text3   char(50)       ,text4   char(50),
                       bst1    char(10)       ,bst2    char(10)       ,bst3    char(10)       ,bst4    char(10),
                       lvnr    char(18)       ,txt     char(50)
                      )
IF SUBSTRING(@@VERSION,22,1) = '2' OR SUBSTRING(@@VERSION,23,1) = '2'
BEGIN
  execute('
            ALTER TABLE dbo.#Tmp ALTER COLUMN artnr  char(20) COLLATE database_default 
            ALTER TABLE dbo.#Tmp ALTER COLUMN posnr  char(10) COLLATE database_default 
            ALTER TABLE dbo.#Tmp ALTER COLUMN me     char(10) COLLATE database_default 
            ALTER TABLE dbo.#Tmp ALTER COLUMN lkz1   char(40) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN lkz2   char(40) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN lkz3   char(40) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN lkz4   char(40) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN text1  char(50) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN text2  char(50) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN text3  char(50) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN text4  char(50) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN bst1   char(10) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN bst2   char(10) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN bst3   char(10) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN bst4   char(10) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN lvnr   char(18) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN txt    char(50) COLLATE database_default
        ')
END

INSERT INTO dbo.#Tmp (
                  artnr   ,posnr   ,menge   ,me, 
                  lkz1    ,lkz2    ,lkz3    ,lkz4,    
                  brutto1 ,brutto2 ,brutto3 ,brutto4 ,netto1 ,netto2 ,netto3 ,netto4,
                  rab11   ,rab12   ,rab13   ,rab14   ,rab21  ,rab22  ,rab23  ,rab24,
                  text1   ,text2   ,text3   ,text4   ,bst1   ,bst2   ,bst3   ,bst4,
                  lvnr
                 )
       SELECT BP.ARTNR, '', 0, '',
              @lkz1, @lkz2, @lkz3, @lkz4,  
              0,0,0,0,  0,0,0,0,  0,0,0,0,  0,0,0,0,  '','','','',  
              @mNRHW, '', '', '',  '' 
       FROM  dbo.Bestellungen B
       INNER JOIN dbo.Bestellpositionen BP 
               ON B.NRHW = BP.NRHW
       WHERE B.NRHW = @mNRHW 
       GROUP BY BP.ARTNR 

  DECLARE art_cursor CURSOR FOR 
  SELECT BP.NRHW, BP.POSNRHW, BP.LVNR, BP.ARTNR, BP.MENGE, M.[Name], 
         BP.BRUTTO, BP.NETTO, BP.RAB1, BP.RAB2, BP.TEXT1 
       FROM  dbo.Bestellungen B
       INNER JOIN dbo.Bestellpositionen BP 
               ON B.NRHW = BP.NRHW
       LEFT  JOIN dbo.Mengeneinheiten M 
               ON BP.ME = M.Nr
       WHERE B.NRHW = @mNRHW 
                  
  OPEN art_cursor
  FETCH NEXT FROM art_cursor
  INTO @NRHW, @PosNr, @LvNr, @ArtNr, @Menge, @Me, @Brutto, @Netto, @Rab1, @Rab2, @Text
  WHILE @@FETCH_STATUS = 0
  BEGIN    
       UPDATE dbo.#Tmp 
       	 SET 
	      posnr = ISNULL(@PosNr ,'')
	     ,lvnr  = ISNULL(@LvNr  ,'')
             ,menge = ISNULL(@Menge ,0 )
	     ,me    = ISNULL(@Me    ,'')
	     ,txt   = ISNULL(@Text  ,'')
             
             ,brutto1  = CASE @NRHW WHEN @mNRHW THEN ISNULL(@Brutto ,0)   ELSE brutto1 END
	     ,netto1   = CASE @NRHW WHEN @mNRHW THEN ISNULL(@Netto  ,0)   ELSE netto1  END
             ,rab11    = CASE @NRHW WHEN @mNRHW THEN ISNULL(@Rab1   ,0)   ELSE rab11   END
             ,rab21    = CASE @NRHW WHEN @mNRHW THEN ISNULL(@Rab2   ,0)   ELSE rab21   END
             ,text1    = CASE @NRHW WHEN @mNRHW THEN ISNULL(@Text   ,'')  ELSE text1   END

             ,brutto2  = ISNULL( (SELECT TOP 1 @Menge * S2.Bruttoek FROM dbo.Stammpositionen S2
                                  WHERE S2.Lkz = @mLKZ1 And S2.Artnr = @ArtNr), 0 )
             ,netto2   = ISNULL( (SELECT TOP 1 @Menge * S2.Ekmat FROM dbo.Stammpositionen S2
                                  WHERE S2.Lkz = @mLKZ1 And S2.Artnr = @ArtNr), 0 )
             ,rab12    = ISNULL( (SELECT TOP 1 S2.Rabatt1 FROM dbo.Stammpositionen S2
                                  WHERE S2.Lkz = @mLKZ1 And S2.Artnr = @ArtNr), 0 )
             ,rab22    = ISNULL( (SELECT TOP 1 S2.Rabatt2 FROM dbo.Stammpositionen S2
                                  WHERE S2.Lkz = @mLKZ1 And S2.Artnr = @ArtNr), 0 )
             ,text2    = ISNULL( (SELECT TOP 1 S2.Bez1 FROM dbo.Stammpositionen S2
                                  WHERE S2.Lkz = @mLKZ1 And S2.Artnr = @ArtNr), 0 )

             ,brutto3  = ISNULL( (SELECT TOP 1 @Menge * S3.Bruttoek FROM dbo.Stammpositionen S3
                                  WHERE S3.Lkz = @mLKZ2 And S3.Artnr = @ArtNr), 0 )
             ,netto3   = ISNULL( (SELECT TOP 1 @Menge * S3.Ekmat FROM dbo.Stammpositionen S3
                                  WHERE S3.Lkz = @mLKZ2 And S3.Artnr = @ArtNr), 0 )
             ,rab13    = ISNULL( (SELECT TOP 1 S3.Rabatt1 FROM dbo.Stammpositionen S3
                                  WHERE S3.Lkz = @mLKZ2 And S3.Artnr = @ArtNr), 0 )
             ,rab23    = ISNULL( (SELECT TOP 1 S3.Rabatt2 FROM dbo.Stammpositionen S3
                                  WHERE S3.Lkz = @mLKZ2 And S3.Artnr = @ArtNr), 0 )
             ,text3    = ISNULL( (SELECT TOP 1 S3.Bez1 FROM dbo.Stammpositionen S3
                                  WHERE S3.Lkz = @mLKZ2 And S3.Artnr = @ArtNr), 0 )

             ,brutto4  = ISNULL( (SELECT TOP 1 @Menge * S4.Bruttoek FROM dbo.Stammpositionen S4
                                  WHERE S4.Lkz = @mLKZ3 And S4.Artnr = @ArtNr), 0 )
             ,netto4   = ISNULL( (SELECT TOP 1 @Menge * S4.Ekmat FROM dbo.Stammpositionen S4
                                  WHERE S4.Lkz = @mLKZ3 And S4.Artnr = @ArtNr), 0 )
             ,rab14    = ISNULL( (SELECT TOP 1 S4.Rabatt1 FROM dbo.Stammpositionen S4
                                  WHERE S4.Lkz = @mLKZ3 And S4.Artnr = @ArtNr), 0 )
             ,rab24    = ISNULL( (SELECT TOP 1 S4.Rabatt2 FROM dbo.Stammpositionen S4
                                  WHERE S4.Lkz = @mLKZ3 And S4.Artnr = @ArtNr), 0 )
             ,text4    = ISNULL( (SELECT TOP 1 S4.Bez1 FROM dbo.Stammpositionen S4
                                  WHERE S4.Lkz = @mLKZ3 And S4.Artnr = @ArtNr), 0 )
         WHERE artnr = @ArtNr

    FETCH NEXT FROM art_cursor
    INTO @NRHW, @PosNr, @LvNr, @ArtNr, @Menge, @Me, @Brutto, @Netto, @Rab1, @Rab2, @Text
  END
  CLOSE art_cursor
  DEALLOCATE art_cursor

SELECT
         T.artnr   ,T.posnr   ,T.menge   ,T.me
        ,T.lkz1    ,T.lkz2    ,T.lkz3    ,T.lkz4    
        ,T.brutto1 ,T.brutto2 ,T.brutto3 ,T.brutto4 ,T.netto1 ,T.netto2 ,T.netto3 ,T.netto4  
        ,T.rab11   ,T.rab12   ,T.rab13   ,T.rab14   ,T.rab21  ,T.rab22  ,T.rab23  ,T.rab24
        ,T.text1   ,T.text2   ,T.text3   ,T.text4   ,T.bst1   ,T.bst2   ,T.bst3   ,T.bst4
        ,T.lvnr    ,T.txt
	,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.#Tmp T
LEFT JOIN dbo.Anschriften AN
       ON AN.Nr = '01'



--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$




GO
CREATE PROCEDURE dbo.crp_PS03
			 @mBelFlt char(18) = ''
			,@mLKZ1   char(2)  = ''
			,@mLKZ2   char(2)  = ''
			,@mLKZ3   char(2)  = ''
AS

DECLARE @ArtNr  char(20)
DECLARE @PosNr  char(20)
DECLARE @LvNr   char(20)
DECLARE @Brutto decimal(17,2)
DECLARE @Netto  decimal(17,2)
DECLARE @Rab1   decimal(17,2)
DECLARE @Rab2   decimal(17,2)
DECLARE @Text   char(42)
DECLARE @Beleg  char(18)
DECLARE @Menge  decimal(17,2)
DECLARE @Me     char(6)

DECLARE @lkz1   char(50)
DECLARE @lkz2   char(50)
DECLARE @lkz3   char(50)
DECLARE @lkz4   char(50)

SET @lkz1 = ISNULL((SELECT TOP 1 B.NAME1 FROM dbo.Belege B
                    WHERE LTRIM(RTRIM(B.PRJNR+B.BELEGART+B.BELEGNR)) = LTRIM(RTRIM(@mBelFlt))
                   ), '')
SET @lkz2 = ISNULL((SELECT TOP 1 A.Name1 FROM dbo.Adressen A
                    WHERE (A.DESTTYP = ' 3') AND (A.LkzImport = @mLKZ1)  
                   ), '')
SET @lkz3 = ISNULL((SELECT TOP 1 A.Name1 FROM dbo.Adressen A
                    WHERE (A.DESTTYP = ' 3') AND (A.LkzImport = @mLKZ2)  
                   ), '')
SET @lkz4 = ISNULL((SELECT TOP 1 A.Name1 FROM dbo.Adressen A
                    WHERE (A.DESTTYP = ' 3') AND (A.LkzImport = @mLKZ3)  
                   ), '')

CREATE TABLE dbo.#Tmp (
                       artnr   char(20)       ,posnr   char(20)       ,menge   float          ,me      char(10),
                       lkz1    char(40)       ,lkz2    char(40)       ,lkz3    char(40)       ,lkz4    char(40),
                       brutto1 decimal(17,2)  ,brutto2 decimal(17,2)  ,brutto3 decimal(17,2)  ,brutto4 decimal(17,2),
                       netto1  decimal(17,2)  ,netto2  decimal(17,2)  ,netto3  decimal(17,2)  ,netto4  decimal(17,2),
                       rab11   decimal(17,2)  ,rab12   decimal(17,2)  ,rab13   decimal(17,2)  ,rab14   decimal(17,2),
                       rab21   decimal(17,2)  ,rab22   decimal(17,2)  ,rab23   decimal(17,2)  ,rab24   decimal(17,2),
                       text1   char(50)       ,text2   char(50)       ,text3   char(50)       ,text4   char(50),
                       bst1    char(20)       ,bst2    char(20)       ,bst3    char(20)       ,bst4    char(20),
                       lvnr    char(20)       ,txt     char(50)
                      )
IF SUBSTRING(@@VERSION,22,1) = '2' OR SUBSTRING(@@VERSION,23,1) = '2'
BEGIN
  execute('
            ALTER TABLE dbo.#Tmp ALTER COLUMN artnr  char(20) COLLATE database_default 
            ALTER TABLE dbo.#Tmp ALTER COLUMN posnr  char(20) COLLATE database_default 
            ALTER TABLE dbo.#Tmp ALTER COLUMN me     char(10) COLLATE database_default 
            ALTER TABLE dbo.#Tmp ALTER COLUMN lkz1   char(40) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN lkz2   char(40) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN lkz3   char(40) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN lkz4   char(40) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN text1  char(50) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN text2  char(50) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN text3  char(50) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN text4  char(50) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN bst1   char(20) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN bst2   char(20) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN bst3   char(20) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN bst4   char(20) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN lvnr   char(20) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN txt    char(50) COLLATE database_default
        ')
END

INSERT INTO dbo.#Tmp (
                  artnr   ,posnr   ,menge   ,me, 
                  lkz1    ,lkz2    ,lkz3    ,lkz4,    
                  brutto1 ,brutto2 ,brutto3 ,brutto4 ,netto1 ,netto2 ,netto3 ,netto4,
                  rab11   ,rab12   ,rab13   ,rab14   ,rab21  ,rab22  ,rab23  ,rab24,
                  text1   ,text2   ,text3   ,text4   ,bst1   ,bst2   ,bst3   ,bst4,
                  lvnr
                 )
       SELECT SUBSTRING(BP.ArtNr, 3, LEN(BP.ArtNr)), '', 0, '',
              @lkz1, @lkz2, @lkz3, @lkz4,  
              0,0,0,0,  0,0,0,0,  0,0,0,0,  0,0,0,0,  '','','','',  
              @mBelFlt, '', '', '',  '' 
       FROM  dbo.Belege B
       INNER JOIN dbo.BelPos BP 
               ON B.BELEGNR = BP.BelNr AND B.BELEGART = BP.BelArt AND B.PRJNR = BP.PrjNr AND
                  ( BP.SatzArt = 6 OR BP.SatzArt = 7 )
       WHERE LTRIM(RTRIM(B.PRJNR+B.BELEGART+B.BELEGNR)) = LTRIM(RTRIM(@mBelFlt)) 
       GROUP BY BP.ArtNr 

  DECLARE art_cursor CURSOR FOR 
  SELECT BP.PrjNr+BP.BelArt+BP.BelNr, BP.PosNr, BP.PosNr, SUBSTRING(BP.ArtNr, 3, LEN(BP.ArtNr)), BP.Menge, BP.Me, 
         BP.Brutto, BP.EkMat, BP.Rab1, BP.Rab2, BP.Ktxt1 
       FROM  dbo.Belege B
       INNER JOIN dbo.BelPos BP 
               ON B.BELEGNR = BP.BelNr AND B.BELEGART = BP.BelArt AND B.PRJNR = BP.PrjNr AND
                  ( BP.SatzArt = 6 OR BP.SatzArt = 7 ) 
       WHERE LTRIM(RTRIM(B.PRJNR+B.BELEGART+B.BELEGNR)) = LTRIM(RTRIM(@mBelFlt)) 
                  
  OPEN art_cursor
  FETCH NEXT FROM art_cursor
  INTO @Beleg, @PosNr, @LvNr, @ArtNr, @Menge, @Me, @Brutto, @Netto, @Rab1, @Rab2, @Text
  WHILE @@FETCH_STATUS = 0
  BEGIN    
       UPDATE dbo.#Tmp 
       	 SET 
	      posnr = ISNULL(@PosNr ,'')
	     ,lvnr  = ISNULL(@LvNr  ,'')
             ,menge = ISNULL(@Menge ,0 )
	     ,me    = ISNULL(@Me    ,'')
	     ,txt   = ISNULL(@Text  ,'')
             
             ,brutto1  = CASE @Beleg WHEN @mBelFlt THEN ISNULL(@Brutto ,0)   ELSE brutto1 END
	     ,netto1   = CASE @Beleg WHEN @mBelFlt THEN ISNULL(@Netto  ,0)   ELSE netto1  END
             ,rab11    = CASE @Beleg WHEN @mBelFlt THEN ISNULL(@Rab1   ,0)   ELSE rab11   END
             ,rab21    = CASE @Beleg WHEN @mBelFlt THEN ISNULL(@Rab2   ,0)   ELSE rab21   END
             ,text1    = CASE @Beleg WHEN @mBelFlt THEN ISNULL(@Text   ,'')  ELSE text1   END

             ,brutto2  = ISNULL( (SELECT TOP 1 @Menge * S2.Bruttoek FROM dbo.Stammpositionen S2
                                  WHERE S2.Lkz = @mLKZ1 And S2.Artnr = @ArtNr), 0 )
             ,netto2   = ISNULL( (SELECT TOP 1 @Menge * S2.Ekmat FROM dbo.Stammpositionen S2
                                  WHERE S2.Lkz = @mLKZ1 And S2.Artnr = @ArtNr), 0 )
             ,rab12    = ISNULL( (SELECT TOP 1 S2.Rabatt1 FROM dbo.Stammpositionen S2
                                  WHERE S2.Lkz = @mLKZ1 And S2.Artnr = @ArtNr), 0 )
             ,rab22    = ISNULL( (SELECT TOP 1 S2.Rabatt2 FROM dbo.Stammpositionen S2
                                  WHERE S2.Lkz = @mLKZ1 And S2.Artnr = @ArtNr), 0 )
             ,text2    = ISNULL( (SELECT TOP 1 S2.Bez1 FROM dbo.Stammpositionen S2
                                  WHERE S2.Lkz = @mLKZ1 And S2.Artnr = @ArtNr), 0 )

             ,brutto3  = ISNULL( (SELECT TOP 1 @Menge * S3.Bruttoek FROM dbo.Stammpositionen S3
                                  WHERE S3.Lkz = @mLKZ2 And S3.Artnr = @ArtNr), 0 )
             ,netto3   = ISNULL( (SELECT TOP 1 @Menge * S3.Ekmat FROM dbo.Stammpositionen S3
                                  WHERE S3.Lkz = @mLKZ2 And S3.Artnr = @ArtNr), 0 )
             ,rab13    = ISNULL( (SELECT TOP 1 S3.Rabatt1 FROM dbo.Stammpositionen S3
                                  WHERE S3.Lkz = @mLKZ2 And S3.Artnr = @ArtNr), 0 )
             ,rab23    = ISNULL( (SELECT TOP 1 S3.Rabatt2 FROM dbo.Stammpositionen S3
                                  WHERE S3.Lkz = @mLKZ2 And S3.Artnr = @ArtNr), 0 )
             ,text3    = ISNULL( (SELECT TOP 1 S3.Bez1 FROM dbo.Stammpositionen S3
                                  WHERE S3.Lkz = @mLKZ2 And S3.Artnr = @ArtNr), 0 )

             ,brutto4  = ISNULL( (SELECT TOP 1 @Menge * S4.Bruttoek FROM dbo.Stammpositionen S4
                                  WHERE S4.Lkz = @mLKZ3 And S4.Artnr = @ArtNr), 0 )
             ,netto4   = ISNULL( (SELECT TOP 1 @Menge * S4.Ekmat FROM dbo.Stammpositionen S4
                                  WHERE S4.Lkz = @mLKZ3 And S4.Artnr = @ArtNr), 0 )
             ,rab14    = ISNULL( (SELECT TOP 1 S4.Rabatt1 FROM dbo.Stammpositionen S4
                                  WHERE S4.Lkz = @mLKZ3 And S4.Artnr = @ArtNr), 0 )
             ,rab24    = ISNULL( (SELECT TOP 1 S4.Rabatt2 FROM dbo.Stammpositionen S4
                                  WHERE S4.Lkz = @mLKZ3 And S4.Artnr = @ArtNr), 0 )
             ,text4    = ISNULL( (SELECT TOP 1 S4.Bez1 FROM dbo.Stammpositionen S4
                                  WHERE S4.Lkz = @mLKZ3 And S4.Artnr = @ArtNr), 0 )
         WHERE artnr = @ArtNr

    FETCH NEXT FROM art_cursor
    INTO @Beleg, @PosNr, @LvNr, @ArtNr, @Menge, @Me, @Brutto, @Netto, @Rab1, @Rab2, @Text
  END
  CLOSE art_cursor
  DEALLOCATE art_cursor

SELECT
         T.artnr   ,T.posnr   ,T.menge   ,T.me
        ,T.lkz1    ,T.lkz2    ,T.lkz3    ,T.lkz4    
        ,T.brutto1 ,T.brutto2 ,T.brutto3 ,T.brutto4 ,T.netto1 ,T.netto2 ,T.netto3 ,T.netto4  
        ,T.rab11   ,T.rab12   ,T.rab13   ,T.rab14   ,T.rab21  ,T.rab22  ,T.rab23  ,T.rab24
        ,T.text1   ,T.text2   ,T.text3   ,T.text4   ,T.bst1   ,T.bst2   ,T.bst3   ,T.bst4
        ,T.lvnr    ,T.txt
	,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.#Tmp T
LEFT JOIN dbo.Anschriften AN
       ON AN.Nr = '01'

GO
