-- Autor: Steffen Anders
-- Stand: 2022/06

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'U' and [name] = 'ut_UmsatzJahr')
  DROP TABLE [dbo].[ut_UmsatzJahr]
GO
CREATE TABLE dbo.ut_UmsatzJahr ( Monat integer, Jahr integer )
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'UmsatzJahr')
  DROP VIEW [dbo].[UmsatzJahr]
GO
CREATE VIEW dbo.UmsatzJahr AS
  SELECT * FROM dbo.ut_UmsatzJahr
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'hwp_SetUmsatzjahr')
  DROP PROCEDURE [dbo].[hwp_SetUmsatzjahr]
GO
CREATE PROCEDURE dbo.hwp_SetUmsatzjahr
AS
   DECLARE @Opt      INTEGER
   DECLARE @OptData  CHAR(7)

   SET @Opt     = (SELECT TOP 1 OptDataI FROM Optionen WHERE OptId=' 7201')
   SET @OptData = STR(MONTH(GETDATE()),2)+'/'+STR(YEAR(GETDATE()),4)
   IF SUBSTRING(@OptData,1,1)=' '
   BEGIN
     SET @OptData = '0'+SUBSTRING(@OptData,2,6)
   END  
   IF @Opt IS NULL
   BEGIN
     SET @Opt = (SELECT 1+MAX(RecId) FROM Optionen)
     IF @Opt IS NULL
     BEGIN
       SET @Opt = 1
     END
     INSERT INTO Optionen (RecId,OptId,OptDataI,OptDataD) VALUES (@Opt,' 7201',100,0.01)
   END
   UPDATE Optionen SET OptDataC=@OptData WHERE OptId=' 7201'

   DELETE FROM UmsatzJahr

   IF MONTH(GETDATE()) = 1
   BEGIN
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 1), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,12), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,11), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,10), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 9), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 8), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 7), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 6), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 5), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 4), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 3), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 2), CONVERT(integer,YEAR(GETDATE())-1) )
     RETURN
   END

   IF MONTH(GETDATE()) = 2
   BEGIN
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 2), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 1), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,12), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,11), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,10), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 9), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 8), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 7), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 6), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 5), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 4), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 3), CONVERT(integer,YEAR(GETDATE())-1) )
     RETURN
   END

   IF MONTH(GETDATE()) = 3
   BEGIN
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 3), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 2), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 1), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,12), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,11), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,10), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 9), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 8), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 7), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 6), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 5), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 4), CONVERT(integer,YEAR(GETDATE())-1) )
     RETURN
   END

   IF MONTH(GETDATE()) = 4
   BEGIN
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 4), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 3), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 2), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 1), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,12), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,11), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,10), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 9), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 8), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 7), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 6), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 5), CONVERT(integer,YEAR(GETDATE())-1) )
     RETURN
   END

   IF MONTH(GETDATE()) = 5
   BEGIN
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 5), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 4), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 3), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 2), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 1), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,12), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,11), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,10), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 9), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 8), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 7), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 6), CONVERT(integer,YEAR(GETDATE())-1) )
     RETURN
   END

   IF MONTH(GETDATE()) = 6
   BEGIN
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 6), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 5), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 4), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 3), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 2), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 1), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,12), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,11), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,10), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 9), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 8), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 7), CONVERT(integer,YEAR(GETDATE())-1) )
     RETURN
   END

   IF MONTH(GETDATE()) = 7
   BEGIN
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 7), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 6), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 5), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 4), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 3), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 2), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 1), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,12), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,11), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,10), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 9), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 8), CONVERT(integer,YEAR(GETDATE())-1) )
     RETURN
   END

   IF MONTH(GETDATE()) = 8
   BEGIN
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 8), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 7), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 6), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 5), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 4), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 3), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 2), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 1), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,12), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,11), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,10), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 9), CONVERT(integer,YEAR(GETDATE())-1) )
     RETURN
   END

   IF MONTH(GETDATE()) = 9
   BEGIN
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 9), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 8), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 7), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 6), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 5), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 4), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 3), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 2), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 1), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,12), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,11), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,10), CONVERT(integer,YEAR(GETDATE())-1) )
     RETURN
   END

   IF MONTH(GETDATE()) = 10
   BEGIN
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,10), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 9), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 8), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 7), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 6), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 5), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 4), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 3), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 2), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 1), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,12), CONVERT(integer,YEAR(GETDATE())-1) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,11), CONVERT(integer,YEAR(GETDATE())-1) )
     RETURN
   END

   IF MONTH(GETDATE()) = 11
   BEGIN
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,11), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,10), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 9), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 8), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 7), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 6), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 5), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 4), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 3), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 2), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 1), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,12), CONVERT(integer,YEAR(GETDATE())-1) )
     RETURN
   END

   IF MONTH(GETDATE()) = 12
   BEGIN
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,12), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,11), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer,10), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 9), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 8), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 7), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 6), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 5), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 4), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 3), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 2), CONVERT(integer,YEAR(GETDATE())  ) )
     INSERT INTO UmsatzJahr (Monat,Jahr) VALUES( CONVERT(integer, 1), CONVERT(integer,YEAR(GETDATE())  ) )
     RETURN
   END
GO

---------------------------------------------------------------------
-- Procedure zum Auffuellen der Warebngruppentabelle 
-- aus dem Artikelstamm                              
-- Stand: 05/2000                                    
-- Autor: sa                                         

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'hwp_Warengruppen_Fuellen')
  DROP PROCEDURE [dbo].[hwp_Warengruppen_Fuellen]
GO
CREATE PROCEDURE dbo.hwp_Warengruppen_Fuellen @Parm_Lkz CHAR(2) = '  '
AS
  IF (@Parm_Lkz = '  ')
    BEGIN
      DECLARE curArtikel CURSOR FOR SELECT DISTINCT  Lkz,Wargru
                                    FROM 	Stammpositionen 
	  			    WHERE   Lkz > ' ' AND Wargru > ' '
    END
  ELSE
    BEGIN
      DECLARE curArtikel CURSOR FOR SELECT DISTINCT  Lkz,Wargru
                                    FROM 	Stammpositionen 
	  			    WHERE   Lkz = @Parm_Lkz AND Wargru > ' '
    END
  
  DECLARE @m_Lkz     CHAR(2)
  DECLARE @m_Wargru  CHAR(6)
  DECLARE @m_Bez1    CHAR(42)
  DECLARE @m_Cnt     INTEGER
  DECLARE @m_New     INTEGER
  DECLARE @m_LastWg  INTEGER

  SET   @m_LastWg = (SELECT MAX(RecId) FROM RabWaGruppen)

  OPEN    curArtikel
  FETCH NEXT FROM curArtikel INTO @m_Lkz,@m_Wargru
  WHILE (@@FETCH_STATUS = 0)
  BEGIN
    SET   @m_Cnt  = (SELECT COUNT(*) FROM  RabWaGruppen
				     WHERE BEREICH='W' AND LKZ=@m_Lkz AND IDENT=@m_Wargru
                    )
    IF (@m_Cnt = 0)
    BEGIN
        BEGIN TRANSACTION
    	SET   @m_Bez1 = (SELECT TOP 1 Bez1 FROM Stammpositionen 
				  WHERE Lkz=@m_Lkz AND Wargru=@m_Wargru
 				  ORDER BY Lkz,Artnr
                    	)
    	SET   @m_Bez1 = 'z.B. ' + @m_Bez1
	SET   @m_New  = (SELECT 1 + MAX(RecId) FROM RabWaGruppen)
	INSERT INTO RabWaGruppen 
                       (RecId,BEREICH,LKZ,IDENT,[TEXT]) 
       		VALUES (@m_New,'W',@m_Lkz,@m_Wargru,@m_Bez1)

        COMMIT TRANSACTION
    END
    FETCH NEXT FROM curArtikel INTO @m_Lkz,@m_Wargru
  END
  CLOSE      curArtikel
  DEALLOCATE curArtikel

  /* Ergebnismenge bereitstellen */
  IF (@m_LastWg IS NULL)
  BEGIN
    SET @m_LastWg = 0
  END

  SELECT 
     LKZ    AS Lkz,
     IDENT  AS WgNr,
     [TEXT] AS Bez 
  FROM  RabWaGruppen
  WHERE RecId > @m_LastWg AND BEREICH = 'W'
GO

/*-------------------------------------------------------------------*/

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_KundenRechnungen')
  DROP VIEW [dbo].[hwv_KundenRechnungen]
GO
CREATE VIEW dbo.hwv_KundenRechnungen AS
   SELECT   
      KDNR        AS KdNr
    , BELEGART    AS BelArt
    , BELEGNR     AS BelNr
    , CONVERT(DATETIME,JAHR+'-'+MONAT+'-'+TAG,120)          AS Datum
    , CASE BELEGART WHEN ' 2' THEN 0 - NETTO ELSE NETTO END AS Umsatz
    , JAHR                                                  AS Jahr
    , MONAT                                                 AS Monat
    , BETREFF1                                              AS Betreff
   FROM  Belege
   WHERE BELEGART>=' 1' AND BELEGART<=' 4'
GO


IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_KundenUmsatz')
  DROP VIEW [dbo].[hwv_KundenUmsatz]
GO
CREATE VIEW dbo.hwv_KundenUmsatz AS
   SELECT DISTINCT
       R.KdNr , R.Jahr, R.Monat
      ,(SELECT SUM(Umsatz) FROM hwv_KundenRechnungen Um
        WHERE Um.KdNr = R.KdNr AND Um.Jahr = R.Jahr AND Um.Monat = R.Monat
       )  AS MonatsUmsatz
      ,(SELECT SUM(Umsatz) FROM hwv_KundenRechnungen Uj
        WHERE Uj.KdNr = R.KdNr AND Uj.Jahr = R.Jahr
       )  AS JahresUmsatz
      ,(SELECT SUM(Umsatz) FROM hwv_KundenRechnungen Ug
        WHERE Ug.KdNr = R.KdNr
       )  AS GesamtUmsatz
      ,A.Name1      AS Name1
      ,A.Strasse    AS Strasse
      ,SUBSTRING(A.Plz,1,8)  AS Plz
      ,A.Ort        AS Ort
      ,A.Telefon1   AS Telefon
      ,A.FaxNummer  AS Fax
   FROM hwv_KundenRechnungen R
   LEFT JOIN Adressen A ON A.DESTDEBNR = R.KdNr
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_Umsatz_Letzte12Monate')
  DROP VIEW [dbo].[hwv_Umsatz_Letzte12Monate]
GO
CREATE VIEW dbo.hwv_Umsatz_Letzte12Monate AS
  SELECT U.* FROM hwv_KundenUmsatz U
     INNER JOIN UmsatzJahr T
     ON T.Jahr = U.Jahr AND T.Monat = U.Monat
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_AktuellerUmsatz')
  DROP VIEW [dbo].[hwv_AktuellerUmsatz]
GO
CREATE VIEW dbo.hwv_AktuellerUmsatz AS
  SELECT DISTINCT
    KdNr,
    (SELECT SUM(MonatsUmsatz) FROM hwv_Umsatz_Letzte12Monate U2
     WHERE U1.KdNr = U2.KdNr
    )  AS Umsatz
    ,Name1,Strasse,Plz,Ort,Telefon,Fax FROM hwv_Umsatz_Letzte12Monate U1
GO


IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_KundenOffen')
  DROP VIEW [dbo].[hwv_KundenOffen]
GO
CREATE VIEW dbo.hwv_KundenOffen AS
  SELECT  
   Z.ADRESSNR       AS KdNr
  ,Z.BELDATUM       AS Datum
  ,Z.FDatum         AS FaelligAm
  ,Z.Brutto         AS Brutto
  ,Z.Netto          AS Netto
  ,Z.Waehrung       AS Waehrung
  ,Z.MahnStufe      AS Mahnstufe
  ,Z.BELART         AS BelArt
  ,Z.BELNR          AS BelNr
  ,A.Name1          AS Name1
  ,A.Telefon1       AS Telefon
  ,Z.SDatum         AS SkontoBis
  ,Z.BezahltSum     AS Bezahlt
  ,Z.Brutto - Z.BezahltSum     AS Offen
  FROM  ZahlBelege  Z
  LEFT JOIN Adressen A ON Z.ADRESSNR = A.DESTDEBNR
  WHERE (Z.Brutto > (Z.BezahltSum + (Z.Brutto * Z.SkontoSatz / 100)))
    AND (Status <> 'G')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'hwp_KundeHatSaldo')
  DROP PROCEDURE [dbo].[hwp_KundeHatSaldo]
GO
CREATE PROCEDURE dbo.hwp_KundeHatSaldo   
         @Parm_KdNr CHAR(15)
AS
  SELECT CONVERT(float,SUM( Offen )) AS Saldo FROM hwv_KundenOffen WHERE KdNr = @Parm_KdNr
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_BelegeKurz')
  DROP VIEW [dbo].[hwv_BelegeKurz]
GO
CREATE VIEW dbo.hwv_BelegeKurz AS
    SELECT 
       PRJNR       AS PrjNr
      ,BELEGART    AS BelArt
      ,BELEGNR     AS BelNr
      ,TAG         AS Tag
      ,MONAT       AS Monat
      ,JAHR        AS Jahr
      ,KDNR        AS KdNr
      ,NAME1       AS Name1
      ,BETREFF1    AS Betreff
      ,SUBMISSION  AS Submission
      ,BAUBEGINN   AS BauBeginn
      ,BAUFERTIG   AS BauFertig
      ,NETTO       AS Netto
      ,BRUTTO      AS Brutto
    FROM Belege
    WHERE BELEGART >= ' 0' AND BELEGART <= ' 9'
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_AktuelleBelege')
  DROP VIEW [dbo].[hwv_AktuelleBelege]
GO
CREATE VIEW dbo.hwv_AktuelleBelege AS
    SELECT 
       PRJNR       AS PrjNr
      ,BELEGART    AS BelArt
      ,BELEGNR     AS BelNr
      ,TAG         AS Tag
      ,MONAT       AS Monat
      ,JAHR        AS Jahr
      ,KDNR        AS KdNr
      ,NAME1       AS Name1
      ,BETREFF1    AS Betreff
      ,SUBMISSION  AS Submission
      ,BAUBEGINN   AS BauBeginn
      ,BAUFERTIG   AS BauFertig
      ,NETTO       AS Netto
      ,BRUTTO      AS Brutto
    FROM Belege
    WHERE (BELEGART >= ' 0' AND BELEGART <= ' 9')
      AND ((JAHR+'-'+MONAT+'-'+TAG) >= CONVERT(CHAR(10),GETDATE()-(SELECT TOP 1 OptDataI FROM Optionen WHERE OptId=' 7201'),120))
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_AktuelleAngebote')
  DROP VIEW [dbo].[hwv_AktuelleAngebote]
GO
CREATE VIEW dbo.hwv_AktuelleAngebote AS
    SELECT 
       PRJNR       AS PrjNr
      ,BELEGART    AS BelArt
      ,BELEGNR     AS BelNr
      ,TAG         AS Tag
      ,MONAT       AS Monat
      ,JAHR        AS Jahr
      ,KDNR        AS KdNr
      ,NAME1       AS Name1
      ,BETREFF1    AS Betreff
      ,SUBMISSION  AS Submission
      ,BAUBEGINN   AS BauBeginn
      ,BAUFERTIG   AS BauFertig
      ,NETTO       AS Netto
      ,BRUTTO      AS Brutto
    FROM Belege
    WHERE (BELEGART = ' 8')
      AND ((JAHR+'-'+MONAT+'-'+TAG) >= CONVERT(CHAR(10),GETDATE()-(SELECT TOP 1 OptDataI FROM Optionen WHERE OptId=' 7201'),120))
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_AktuelleAuftraege')
  DROP VIEW [dbo].[hwv_AktuelleAuftraege]
GO
CREATE VIEW dbo.hwv_AktuelleAuftraege AS
    SELECT 
       PRJNR       AS PrjNr
      ,BELEGART    AS BelArt
      ,BELEGNR     AS BelNr
      ,TAG         AS Tag
      ,MONAT       AS Monat
      ,JAHR        AS Jahr
      ,KDNR        AS KdNr
      ,NAME1       AS Name1
      ,BETREFF1    AS Betreff
      ,SUBMISSION  AS Submission
      ,BAUBEGINN   AS BauBeginn
      ,BAUFERTIG   AS BauFertig
      ,NETTO       AS Netto
      ,BRUTTO      AS Brutto
    FROM Belege
    WHERE (BELEGART = ' 7')
      AND ((JAHR+'-'+MONAT+'-'+TAG) >= CONVERT(CHAR(10),GETDATE()-(SELECT TOP 1 OptDataI FROM Optionen WHERE OptId=' 7201'),120))
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_OffeneAngebote')
  DROP VIEW [dbo].[hwv_OffeneAngebote]
GO
CREATE VIEW dbo.hwv_OffeneAngebote AS
  SELECT A.* FROM hwv_AktuelleAngebote  A
  WHERE 0 = (SELECT COUNT( B.BelNr ) 
             FROM   hwv_AktuelleAuftraege B
             WHERE  (B.PrjNr=A.PrjNr) 
               AND (B.KdNr=A.KdNr)
               AND  (B.Jahr+B.Monat+B.Tag) >= (A.Jahr+A.Monat+A.Tag)
            )
GO


IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_KundenArtikel')
  DROP VIEW [dbo].[hwv_KundenArtikel]
GO
CREATE VIEW dbo.hwv_KundenArtikel AS
--ALTER VIEW hwv_KundenArtikel AS
  SELECT 
     SP.Lkz
    ,SP.Artnr
    ,BP.AbsMenge
    ,BP.Me
    ,BP.EPMat
    ,BP.Zeit
    ,SP.Bez1
    ,SP.Bez2
    ,SP.Merkmal
    ,BV.JAHR     AS Jahr
    ,BV.MONAT    AS Monat
    ,BV.TAG      AS Tag
    ,BV.BELEGART AS BelArt
    ,BV.BELEGNR  AS BelNr
    ,BV.KDNR     AS KdNr
    ,BV.WAEHRUNG AS Waehrung
  FROM BelPos BP
  INNER JOIN Stammpositionen SP ON SP.Lkz=SUBSTRING(BP.ArtNr,1,2) AND SP.Artnr=SUBSTRING(BP.ArtNr,3,18)
  INNER JOIN Belege BV          ON BV.PRJNR=BP.PrjNr AND BV.BELEGART=BP.BelArt AND BV.BELEGNR=BP.BelNr
  WHERE (BP.BelArt = ' 1' OR BP.BelArt = ' 4') AND (BP.SatzArt=7 OR BP.SatzArt=8) AND (BP.MatJN=1)
        AND (BP.EPMat >= (SELECT TOP 1 OptDataD FROM Optionen WHERE OptId = ' 7201'))
GO


/* ====================================================== */
/* Auskunft, welcher Kunde hat welchen Artikel je gekauft */
/* ====================================================== */
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_ArtikelKunden')
  DROP VIEW [dbo].[hwv_ArtikelKunden]
GO
CREATE VIEW dbo.hwv_ArtikelKunden AS
--ALTER VIEW hwv_ArtikelKunden AS
  SELECT 
     SP.Lkz
    ,SP.Artnr
    ,BP.AbsMenge
    ,BP.Me
    ,BP.EPMat
    ,BV.JAHR     AS Jahr
    ,BV.MONAT    AS Monat
    ,BV.TAG      AS Tag
    ,BV.BELEGART AS BelArt
    ,BV.BELEGNR  AS BelNr
    ,BV.KDNR     AS KdNr
    ,BV.WAEHRUNG AS Waehrung
    ,AD.Name1    AS Name1
    ,AD.Ort      AS Ort
    ,AD.Telefon1 AS Telefon
  FROM BelPos BP
  INNER JOIN Stammpositionen SP ON SP.Lkz=SUBSTRING(BP.ArtNr,1,2) AND SP.Artnr=SUBSTRING(BP.ArtNr,3,18)
  INNER JOIN Belege BV          ON BV.PRJNR=BP.PrjNr AND BV.BELEGART=BP.BelArt AND BV.BELEGNR=BP.BelNr
  LEFT  JOIN Adressen AD        ON BV.KDNR = AD.DESTDEBNR
  WHERE (BP.BelArt = ' 1' OR BP.BelArt = ' 4') AND (BP.SatzArt=7 OR BP.SatzArt=8) AND (BP.MatJN=1)
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'hwp_ArtikelKunden')
  DROP PROCEDURE [dbo].[hwp_ArtikelKunden]
GO
CREATE PROCEDURE dbo.hwp_ArtikelKunden @Lkz char(2), @ArtNr char(18)
--ALTER PROCEDURE hwp_ArtikelKunden @Lkz char(2), @ArtNr char(18)
AS
  SELECT 
     SP.Lkz
    ,SP.Artnr
    ,BP.AbsMenge
    ,BP.Me
    ,BP.EPMat
    ,BV.JAHR     AS Jahr
    ,BV.MONAT    AS Monat
    ,BV.TAG      AS Tag
    ,BV.BELEGART AS BelArt
    ,BV.BELEGNR  AS BelNr
    ,BV.KDNR     AS KdNr
    ,BV.WAEHRUNG AS Waehrung
    ,AD.Name1    AS Name1
    ,AD.Ort      AS Ort
    ,AD.Telefon1 AS Telefon
    ,BV.BETREFF1 AS Betreff1
    ,BV.BETREFF2 AS Betreff2
    ,BV.BETREFF3 AS Betreff3
  FROM BelPos BP
  INNER JOIN Stammpositionen SP ON SP.Lkz=SUBSTRING(BP.ArtNr,1,2) AND SP.Artnr=SUBSTRING(BP.ArtNr,3,18)
  INNER JOIN Belege BV          ON BV.PRJNR=BP.PrjNr AND BV.BELEGART=BP.BelArt AND BV.BELEGNR=BP.BelNr
  LEFT  JOIN Adressen AD        ON BV.KDNR = AD.DESTDEBNR
  WHERE  (BP.ArtNr = (@Lkz + @ArtNr))
     AND (BP.BelArt = ' 1' OR BP.BelArt = ' 4') 
     AND (BP.SatzArt=7 OR BP.SatzArt=8) AND (BP.MatJN=1)
  ORDER BY KdNr,Jahr DESC,Monat DESC,Tag DESC
GO

/*EXECUTE hwp_ArtikelKunden 'E1','ABBF2746B200,03'*/

/* ====================================================== */


/*-------------------------------------------------------------------*/
/* Sichten auf den Monteurbericht */

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_MonteureUndService')
  DROP VIEW [dbo].[hwv_MonteureUndService]
GO
CREATE VIEW dbo.hwv_MonteureUndService AS
  SELECT 
      BERINR                 AS BeriNr
     ,STATUS                 AS Status
     ,SUBSTRING(DATAKEY,1,4) AS Annr
     ,DATATEXT               AS Monteur
     ,DATAKW                 AS Woche
  FROM   Monteurberichte
  WHERE  SATZART = '81'
GO


IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_ServiceTermine')
  DROP VIEW [dbo].[hwv_ServiceTermine]
GO
CREATE VIEW dbo.hwv_ServiceTermine AS
  SELECT 
     BERINR                                     AS BeriNr
    ,STATUS                                     AS Status
    ,SUBSTRING(DATATEXT,1,4)                    AS Jahr
    ,SUBSTRING(DATATEXT,5,2)                    AS Monat
    ,CONVERT(CHAR(10),CONVERT(DATETIME,'1899-12-31',120)+CONVERT(integer,SUBSTRING(DATATEXT,11,5)),120)  AS TerminAm
    ,CONVERT(CHAR(10),CONVERT(DATETIME,'1899-12-31',120)+CONVERT(integer,SUBSTRING(DATATEXT,11,5)),120)  AS AuftragVom
    ,KDNR                                       AS KdNr  
  FROM   Monteurberichte
  WHERE  SATZART = '10' 
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_MonteurTermineService')
  DROP VIEW [dbo].[hwv_MonteurTermineService]
GO
CREATE VIEW dbo.hwv_MonteurTermineService AS
  SELECT 
    M.Annr
   ,M.Monteur
   ,B.BeriNr
   ,B.Status 
   ,B.TerminAm
   ,B.AuftragVom
   ,B.KdNr
--   ,A.Name1
--   ,A.Telefon1    AS Telefon
--   ,A.FaxNummer   AS Fax
   ,SUBSTRING(B.TerminAm,1,4)  AS Jahr
   ,SUBSTRING(B.TerminAm,6,2)  AS Monat
   ,SUBSTRING(B.TerminAm,9,2)  AS Tag
  FROM        hwv_MonteureUndService M
  INNER JOIN  hwv_ServiceTermine     B ON B.BeriNr = M.BeriNr
  LEFT  JOIN  Adressen A               ON B.KdNr   = A.DESTDEBNR                
GO

/*-------------------------------------------------------------------*/

/* Implementiert die Wiedervorlage */

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_NochZuBearbeiten')
  DROP VIEW [dbo].[hwv_NochZuBearbeiten]
GO
CREATE VIEW dbo.hwv_NochZuBearbeiten AS

  SELECT 
     CONVERT(CHAR(2),'SB')         AS  BelArt
    ,A.BeriNr                      AS  BelNr
    ,A.KdNr                        AS  KdNr
    ,SUBSTRING(A.TerminAm,1,4)     AS  Jahr
    ,SUBSTRING(A.TerminAm,6,2)     AS  Monat
    ,SUBSTRING(A.TerminAm,9,2)     AS  Tag
    ,CONVERT(CHAR(42),'Service')   AS  Betreff
    ,SUBSTRING(B.DATATEXT,31,10)   AS  uZ
  FROM  hwv_ServiceTermine A 
  LEFT JOIN Monteurberichte B ON A.BeriNr = B.BERINR AND B.SATZART = '10'
  WHERE A.Status < '3'

  UNION

  SELECT 
     A.BelArt
    ,A.BelNr
    ,A.KdNr
    ,A.Jahr
    ,A.Monat
    ,A.Tag 
    ,A.Betreff
    ,B.UNSERZEI   AS uZ
  FROM hwv_AktuelleAngebote A
  LEFT JOIN Belege B ON A.BelArt = B.BELEGART AND A.BelNr = B.BELEGNR

GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'hwp_Wiedervorlage')
  DROP PROCEDURE [dbo].[hwp_Wiedervorlage]
GO
CREATE PROCEDURE dbo.hwp_Wiedervorlage @m_UZ CHAR(10) = NULL
AS
   IF @m_UZ IS NULL
   BEGIN
     SELECT W.*,A.Name1,A.Telefon1 AS Telefon FROM hwv_NochZuBearbeiten W
     LEFT JOIN Adressen A ON W.KdNr = A.DESTDEBNR
     ORDER BY BelArt DESC, BelNr
   END
   ELSE
   BEGIN
     SELECT W.*,A.Name1,A.Telefon1 AS Telefon FROM hwv_NochZuBearbeiten W
     LEFT JOIN Adressen A ON W.KdNr = A.DESTDEBNR
     WHERE uZ = @m_UZ
     ORDER BY BelArt DESC, BelNr
   END
GO


IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'hwp_PaketPflegen')
  DROP PROCEDURE [dbo].[hwp_PaketPflegen]
GO
CREATE PROCEDURE dbo.hwp_PaketPflegen 
         @PktNr char(17) = NULL
AS

 IF (@PktNr IS NULL)
 BEGIN  
  UPDATE StammPakete SET
    Suchen  = (SELECT TOP 1 Suchen  FROM Stammpositionen P WHERE P.Lkz = ArtLkz AND P.Artnr = ArtNr)
   ,Txt1    = (SELECT TOP 1 Bez1    FROM Stammpositionen P WHERE P.Lkz = ArtLkz AND P.Artnr = ArtNr)
   ,Txt2    = (SELECT TOP 1 Bez2    FROM Stammpositionen P WHERE P.Lkz = ArtLkz AND P.Artnr = ArtNr)
   ,Txt3    = (SELECT TOP 1 Merkmal FROM Stammpositionen P WHERE P.Lkz = ArtLkz AND P.Artnr = ArtNr)
   ,Einheit = (SELECT TOP 1 Einheit FROM Stammpositionen P WHERE P.Lkz = ArtLkz AND P.Artnr = ArtNr)
  WHERE   
    LfdNr > '000' AND
    0 < (SELECT COUNT(Lkz+Artnr) FROM Stammpositionen P WHERE P.Lkz = ArtLkz AND P.Artnr = ArtNr)

  SELECT * FROM StammPakete WHERE LfdNr > '000'
 END

 ELSE
 BEGIN
  UPDATE StammPakete SET
    Suchen  = (SELECT TOP 1 Suchen  FROM Stammpositionen P WHERE P.Lkz = ArtLkz AND P.Artnr = ArtNr)
   ,Txt1    = (SELECT TOP 1 Bez1    FROM Stammpositionen P WHERE P.Lkz = ArtLkz AND P.Artnr = ArtNr)
   ,Txt2    = (SELECT TOP 1 Bez2    FROM Stammpositionen P WHERE P.Lkz = ArtLkz AND P.Artnr = ArtNr)
   ,Txt3    = (SELECT TOP 1 Merkmal FROM Stammpositionen P WHERE P.Lkz = ArtLkz AND P.Artnr = ArtNr)
   ,Einheit = (SELECT TOP 1 Einheit FROM Stammpositionen P WHERE P.Lkz = ArtLkz AND P.Artnr = ArtNr)
  WHERE   
    PktNr = @PktNr AND LfdNr > '000' AND
    0 < (SELECT COUNT(Lkz+Artnr) FROM Stammpositionen P WHERE P.Lkz = ArtLkz AND P.Artnr = ArtNr)

  SELECT * FROM StammPakete WHERE PktNr = @PktNr AND LfdNr > '000'
 END
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'hwp_ArtikelUmtaufen')
  DROP PROCEDURE [dbo].[hwp_ArtikelUmtaufen]
GO
CREATE PROCEDURE dbo.hwp_ArtikelUmtaufen    
         @OldLkz char(2)
        ,@OldNr  char(18)
        ,@NewLkz char(2)
        ,@NewNr  char(18)
AS
   /* Test ob Artikel existiert und umbenannt werden kann */
   BEGIN TRANSACTION
   IF 0 < (SELECT COUNT(Artnr) FROM Stammpositionen WHERE Lkz = @NewLkz AND Artnr = @NewNr)
   BEGIN
     ROLLBACK TRANSACTION
     SELECT  CONVERT(integer,0)  AS ReturnOk
     RETURN
   END
   IF 0 = (SELECT COUNT(Artnr) FROM Stammpositionen WHERE Lkz = @OldLkz AND Artnr = @OldNr)
   BEGIN
     ROLLBACK TRANSACTION
     SELECT  CONVERT(integer,0)  AS ReturnOk
     RETURN
   END

   /* Umtaufen des Artikels !!! wenn Constraints vereinbart sind schlaegt es so fehl */

   UPDATE Stammpositionen   SET   Lkz=@NewLkz  ,Artnr=@NewNr    WHERE Lkz=@OldLkz AND Artnr=@OldNr

   DELETE FROM Kleinteile   WHERE Lkz=@NewLkz AND Artnr=@NewNr
   UPDATE Kleinteile        SET   Lkz=@NewLkz  ,Artnr=@NewNr    WHERE Lkz=@OldLkz AND Artnr=@OldNr

   UPDATE Lagerbewegungen   SET   ARTNR=@NewLkz+@NewNr          WHERE ARTNR=@OldLkz+@OldNr

   DELETE FROM Dimensionstexte WHERE LKZ=@NewLkz AND ARTNR=@NewNr
   UPDATE Dimensionstexte   SET   LKZ=@NewLkz  ,ARTNR=@NewNr    WHERE LKZ=@OldLkz AND ARTNR=@OldNr

   /* Zubehoer und Paketrueckverweise */   

   UPDATE Kleinteile        SET   RefLkz=@NewLkz,RefArtnr=@NewNr  WHERE RefLkz=@OldLkz AND RefArtnr=@OldNr
   UPDATE StammPakete       SET   ArtLkz=@NewLkz,ArtNr=@NewNr     WHERE ArtLkz=@OldLkz AND ArtNr=@OldNr

   /* Bestellpositionen */
   UPDATE  Bestellpositionen 
     SET   ARTNRHWP=@NewLkz+@NewNr  ,  ARTNR=@NewNr
     WHERE ARTNRHWP=@OldLkz+@OldNr

   /* Belegpositionen */
   UPDATE  BelPos
     SET   ArtNr = @NewLkz+@NewNr
     WHERE ArtNr = @OldLkz+@OldNr AND (SatzArt=7 OR SatzArt=8)

   COMMIT TRANSACTION
   SELECT  CONVERT(integer,1)  AS ReturnOk
GO


/* ------------------------------------------------------------------ */
/* Auskunft Alternativen zum Artikel */

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'hwp_GleicheArtikel')
  DROP PROCEDURE [dbo].[hwp_GleicheArtikel]
GO
CREATE PROCEDURE dbo.hwp_GleicheArtikel @Parm_EANcode CHAR(14)
--ALTER PROCEDURE hwp_GleicheArtikel @Parm_EANcode CHAR(14)
AS
   IF @Parm_EANcode IS NULL
   BEGIN
	   SELECT    Lkz,Artnr,Bez1,Bez2,Merkmal,Bruttoek,Rabatt1,Rabatt2,NeAnteil,Zeit,Vkm2,Vkl2,Lagbest,Ekmat
   	     FROM    Stammpositionen WHERE 0 = 1
	   RETURN
   END
	
   IF (RTRIM(@Parm_EANcode) = '')
   BEGIN
	   SELECT    Lkz,Artnr,Bez1,Bez2,Merkmal,Bruttoek,Rabatt1,Rabatt2,NeAnteil,Zeit,Vkm2,Vkl2,Lagbest,Ekmat
   	     FROM    Stammpositionen WHERE 0 = 1
	   RETURN
   END

   SELECT    Lkz,Artnr,Bez1,Bez2,Merkmal,Bruttoek,Rabatt1,Rabatt2,NeAnteil,Zeit,Vkm2,Vkl2,Lagbest,Ekmat
   FROM      Stammpositionen WHERE EANcode = @Parm_EANcode
   ORDER BY  Lkz,Artnr
GO

---------------------------------------------------------------
-- Dialogseite Offen beim Kunden
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'hwp_KundeHatOffen')
  DROP PROCEDURE [dbo].[hwp_KundeHatOffen]
GO

CREATE PROCEDURE dbo.hwp_KundeHatOffen @DebNr CHAR(15)
AS
  SELECT * FROM hwv_KundenOffen 
    WHERE    KdNr = @DebNr
    ORDER BY Datum DESC
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_KundenOffen')
  DROP VIEW [dbo].[hwv_KundenOffen]
GO
CREATE VIEW dbo.hwv_KundenOffen AS
  SELECT  
   Z.ADRESSNR       AS KdNr
  ,Z.BELDATUM       AS Datum
  ,Z.FDatum         AS FaelligAm
  ,Z.Brutto         AS Brutto
  ,Z.Netto          AS Netto
  ,Z.Waehrung       AS Waehrung
  ,Z.MahnStufe      AS Mahnstufe
  ,Z.BELART         AS BelArt
  ,Z.BELNR          AS BelNr
  ,A.Name1          AS Name1
  ,A.Telefon1       AS Telefon
  ,Z.SDatum         AS SkontoBis
  ,Z.BezahltSum     AS Bezahlt
  ,Z.Brutto - Z.BezahltSum     AS Offen
  FROM  ZahlBelege  Z
  LEFT JOIN Adressen A ON Z.ADRESSNR = A.DESTDEBNR
  WHERE (Z.Brutto > (Z.BezahltSum + (Z.Brutto * Z.SkontoSatz / 100)))
    AND (Status <> 'G')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'hwp_MonteurTermine')
  DROP PROCEDURE [dbo].[hwp_MonteurTermine]
GO
CREATE PROCEDURE dbo.hwp_MonteurTermine @Annr CHAR(4)
AS
    SELECT 
        MT.*
    FROM hwv_MonteureUndService MT 
    WHERE  MT.Annr = @Annr AND MT.Status < '2' 


--    SELECT 
--       MS.* 
--      ,MT.AuftragVom
--      ,MT.TerminAm
--      ,MT.KdNr
--      ,(SELECT Name1    FROM Adressen WHERE DESTDEBNR = MT.KdNr) AS Name1
--      ,(SELECT Strasse  FROM Adressen WHERE DESTDEBNR = MT.KdNr) AS Strasse
--      ,(SELECT Ort      FROM Adressen WHERE DESTDEBNR = MT.KdNr) AS Ort
--      ,(SELECT Telefon1 FROM Adressen WHERE DESTDEBNR = MT.KdNr) AS Telefon
--    FROM   hwv_MonteureUndService MS
--    INNER  JOIN hwv_ServiceTermine MT ON MT.BeriNr = MS.BeriNr
--    WHERE  MS.Annr = @Annr AND MS.Status < '2' 
GO



/* Projektauskunftsdaten feststellen */

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'hwp_ProjektLager')
  DROP PROCEDURE [dbo].[hwp_ProjektLager]
GO
CREATE PROCEDURE dbo.hwp_ProjektLager @PrjNr CHAR(8)
AS
   SELECT 
     B.*, S.Bez1, S.Bez2, S.Merkmal, S.Lagbest
   FROM Lagerbewegungen B
   INNER JOIN Stammpositionen S ON S.Lkz = SUBSTRING(B.ARTNR,1,2) AND S.Artnr = SUBSTRING(B.ARTNR,3,18)
   WHERE B.PrjNr = @PrjNr
GO


IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_BelegeInEuro')
  DROP VIEW [dbo].[hwv_BelegeInEuro]
GO
CREATE VIEW dbo.hwv_BelegeInEuro
AS
  SELECT  PRJNR      AS PrjNr
         ,BELEGART   AS BelArt
         ,BELEGNR    AS BelNr
         ,JAHR+'-'+MONAT+'-'+TAG AS Datum
         ,CASE WHEN KURSZUEURO = 0 OR KURSZUEURO = 1 THEN NETTO ELSE NETTO / KURSZUEURO END AS EuroNetto
         ,CASE WHEN KURSZUEURO = 0 OR KURSZUEURO = 1 THEN SUMMEEKM  ELSE SUMMEEKM  / KURSZUEURO END AS EuroEkMat
         ,CASE WHEN KURSZUEURO = 0 OR KURSZUEURO = 1 THEN SUMMEEKFR ELSE SUMMEEKFR / KURSZUEURO END AS EuroEkFremd
         ,SUMMEZEIT  AS Minuten
  FROM   Belege B
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_BelegStatistik')
  DROP VIEW [dbo].[hwv_BelegStatistik]
GO
CREATE VIEW dbo.hwv_BelegStatistik
AS
  SELECT
      PrjNr
     ,'Angebote'                                               AS Kategorie
     ,(SELECT SUM(EuroEkMat)   FROM hwv_BelegeInEuro V12 
       WHERE BelArt=' 8' AND V12.PrjNr = V11.PrjNr)            AS EkMat
     ,(SELECT SUM(EuroEkFremd) FROM hwv_BelegeInEuro V13
       WHERE BelArt=' 8' AND V13.PrjNr = V11.PrjNr)            AS EkFremd
     ,(SELECT SUM(EuroNetto)   FROM hwv_BelegeInEuro V14
       WHERE BelArt=' 8' AND V14.PrjNr = V11.PrjNr)            AS Netto
     ,(SELECT SUM(Minuten/60)  FROM hwv_BelegeInEuro V15
       WHERE BelArt=' 8' AND V15.PrjNr = V11.PrjNr)            AS Stunden
  FROM hwv_BelegeInEuro V11 WHERE V11.BelArt = ' 0'
  UNION
  SELECT
      PrjNr
     ,'Aufträge'                                               AS Kategorie
     ,(SELECT SUM(EuroEkMat)   FROM hwv_BelegeInEuro V12 
       WHERE BelArt=' 7' AND V12.PrjNr = V11.PrjNr)            AS EkMat
     ,(SELECT SUM(EuroEkFremd) FROM hwv_BelegeInEuro V13
       WHERE BelArt=' 7' AND V13.PrjNr = V11.PrjNr)            AS EkFremd
     ,(SELECT SUM(EuroNetto)   FROM hwv_BelegeInEuro V14
       WHERE BelArt=' 7' AND V14.PrjNr = V11.PrjNr)            AS Netto
     ,(SELECT SUM(Minuten/60)  FROM hwv_BelegeInEuro V15
       WHERE BelArt=' 7' AND V15.PrjNr = V11.PrjNr)            AS Stunden
  FROM hwv_BelegeInEuro V11 WHERE V11.BelArt = ' 0'
  UNION
  SELECT
      PrjNr
     ,'Rechnungen'                                             AS Kategorie
     ,(SELECT SUM(EuroEkMat)   FROM hwv_BelegeInEuro V12 
       WHERE BelArt=' 1' AND V12.PrjNr = V11.PrjNr)            AS EkMat
     ,(SELECT SUM(EuroEkFremd) FROM hwv_BelegeInEuro V13
       WHERE BelArt=' 1' AND V13.PrjNr = V11.PrjNr)            AS EkFremd
     ,(SELECT SUM(EuroNetto)   FROM hwv_BelegeInEuro V14
       WHERE BelArt=' 1' AND V14.PrjNr = V11.PrjNr)            AS Netto
     ,(SELECT SUM(Minuten/60)  FROM hwv_BelegeInEuro V15
       WHERE BelArt=' 1' AND V15.PrjNr = V11.PrjNr)            AS Stunden
  FROM hwv_BelegeInEuro V11 WHERE V11.BelArt = ' 0'
  UNION
  SELECT
      PrjNr
     ,'Gutschriften'                                           AS Kategorie
     ,(SELECT SUM(EuroEkMat)   FROM hwv_BelegeInEuro V12 
       WHERE BelArt=' 2' AND V12.PrjNr = V11.PrjNr)            AS EkMat
     ,(SELECT SUM(EuroEkFremd) FROM hwv_BelegeInEuro V13
       WHERE BelArt=' 2' AND V13.PrjNr = V11.PrjNr)            AS EkFremd
     ,(SELECT SUM(EuroNetto)   FROM hwv_BelegeInEuro V14
       WHERE BelArt=' 2' AND V14.PrjNr = V11.PrjNr)            AS Netto
     ,(SELECT SUM(Minuten/60)  FROM hwv_BelegeInEuro V15
       WHERE BelArt=' 2' AND V15.PrjNr = V11.PrjNr)            AS Stunden
  FROM hwv_BelegeInEuro V11 WHERE V11.BelArt = ' 0'
  UNION
  SELECT
      PrjNr
     ,'Teilrechnung'                                           AS Kategorie
     ,(SELECT SUM(EuroEkMat)   FROM hwv_BelegeInEuro V12 
       WHERE BelArt=' 3' AND V12.PrjNr = V11.PrjNr)            AS EkMat
     ,(SELECT SUM(EuroEkFremd) FROM hwv_BelegeInEuro V13
       WHERE BelArt=' 3' AND V13.PrjNr = V11.PrjNr)            AS EkFremd
     ,(SELECT SUM(EuroNetto)   FROM hwv_BelegeInEuro V14
       WHERE BelArt=' 3' AND V14.PrjNr = V11.PrjNr)            AS Netto
     ,(SELECT SUM(Minuten/60)  FROM hwv_BelegeInEuro V15
       WHERE BelArt=' 3' AND V15.PrjNr = V11.PrjNr)            AS Stunden
  FROM hwv_BelegeInEuro V11 WHERE V11.BelArt = ' 0'
  UNION
  SELECT
      PrjNr
     ,'Schlussrechnung'                                        AS Kategorie
     ,(SELECT SUM(EuroEkMat)   FROM hwv_BelegeInEuro V12 
       WHERE BelArt=' 4' AND V12.PrjNr = V11.PrjNr)            AS EkMat
     ,(SELECT SUM(EuroEkFremd) FROM hwv_BelegeInEuro V13
       WHERE BelArt=' 4' AND V13.PrjNr = V11.PrjNr)            AS EkFremd
     ,(SELECT SUM(EuroNetto)   FROM hwv_BelegeInEuro V14
       WHERE BelArt=' 4' AND V14.PrjNr = V11.PrjNr)            AS Netto
     ,(SELECT SUM(Minuten/60)  FROM hwv_BelegeInEuro V15
       WHERE BelArt=' 4' AND V15.PrjNr = V11.PrjNr)            AS Stunden
  FROM hwv_BelegeInEuro V11 WHERE V11.BelArt = ' 0'
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'hwp_SollIstVergleich')
  DROP PROCEDURE [dbo].[hwp_SollIstVergleich]
GO
CREATE PROCEDURE dbo.hwp_SollIstVergleich  @PrjNr CHAR(8)
--ALTER PROCEDURE hwp_SollIstVergleich  @PrjNr CHAR(8)
AS
  DECLARE @FistEuroKurs float
  DECLARE @FistEuroLohn float 

  SET @FistEuroKurs = (SELECT Eurokurs FROM Waehrungen
                       WHERE Kuerzel = (SELECT OptDataC FROM Optionen WHERE OptId = ' 7008')
                      )
  SET @FistEuroLohn = (SELECT OptDataD / @FistEuroKurs FROM Optionen WHERE OptId = ' 7006')

  SELECT 
     PrjNr,Kategorie
    ,CASE WHEN Netto   IS NULL THEN 0 ELSE Netto   * @FistEuroKurs END AS Vk_Netto
    ,CASE WHEN Stunden IS NULL THEN 0 ELSE Stunden                 END AS Zeit_Std
    ,CASE WHEN Stunden IS NULL THEN 0 ELSE Stunden * 60            END AS Zeit_Min
    ,CASE WHEN Stunden IS NULL THEN 0 ELSE Stunden*60*@FistEuroLohn* @FistEuroKurs END AS Ek_Lohn
    ,CASE WHEN EkMat   IS NULL THEN 0 ELSE EkMat   * @FistEuroKurs END AS Ek_Mat
    ,CASE WHEN EkFremd IS NULL THEN 0 ELSE EkFremd * @FistEuroKurs END AS Ek_Fremd
  FROM hwv_BelegStatistik WHERE PrjNr=@PrjNr
GO

/* ==================================================== TUNING BELEGVERARBEITUNG ====================*/
/* ----------------------------------------------- */
/* Procedure zum löschen der alten Belegpositionen */

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'hwp_Beleg_Clear')
  DROP PROCEDURE [dbo].[hwp_Beleg_Clear]
GO
CREATE PROCEDURE dbo.hwp_Beleg_Clear 
    @PrjNr  CHAR(8)
   ,@BelArt CHAR(2)
   ,@BelNr  CHAR(8)
AS

  DELETE FROM BelPos        WHERE PrjNr = @PrjNr AND BelArt = @BelArt AND BelNr = @BelNr
  DELETE FROM BelMassRef    WHERE PrjNr = @PrjNr AND BelArt = @BelArt AND BelNr = @BelNr
  DELETE FROM BelMassPosRef WHERE PrjNr = @PrjNr AND BelArt = @BelArt AND BelNr = @BelNr
  DELETE FROM BelNe         WHERE PrjNr = @PrjNr AND BelArt = @BelArt AND BelNr = @BelNr

GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'hwp_BelegArtikelHistorie')
  DROP PROCEDURE [dbo].[hwp_BelegArtikelHistorie]
GO
CREATE PROCEDURE dbo.hwp_BelegArtikelHistorie 
--ALTER PROCEDURE hwp_BelegArtikelHistorie 
     @Parm_ArtNr   char(20)
    ,@Parm_KdNr    char(15)
AS

  IF RTRIM(@Parm_ArtNr) = ''  SET @Parm_ArtNr = '(leer)'
  IF RTRIM(@Parm_KdNr)  = ''  SET @Parm_KdNr  = '(leer)'

  SELECT 
     BV.JAHR     AS Jahr
    ,BV.MONAT    AS Monat
    ,BV.TAG      AS Tag
    ,BP.BelArt   
    ,BP.BelNr
    ,BP.AbsMenge
    ,BP.Me
    ,BV.WAEHRUNG AS Waehrung
    ,BP.EPMat
    ,BP.EPMat / BW.Eurokurs AS EPMatEuro
    ,BP.EPLohn
    ,BP.EPLohn / BW.Eurokurs AS EPLohnEuro
    ,BP.ArtNr
    ,BP.Ktxt1
    ,BW.Eurokurs
    ,BV.KDNR     AS KdNr
  FROM       BelPos BP
  INNER JOIN Belege BV          
     ON BV.PRJNR=BP.PrjNr AND BV.BELEGART=BP.BelArt AND BV.BELEGNR=BP.BelNr
  INNER JOIN Waehrungen BW
     ON BW.Kuerzel = BV.WAEHRUNG

  WHERE BP.ArtNr = @Parm_ArtNr
    AND BV.KDNR  = @Parm_KdNr
    AND (BV.BELEGART = ' 1' OR BV.BELEGART = ' 3' OR BV.BELEGART = ' 4' OR BV.BELEGART = ' 8')

  ORDER BY BV.JAHR DESC,BV.MONAT DESC,BV.TAG DESC,BP.BelArt,BP.BelNr
GO

/* ========================================================================== */
/* Tuning Langtextabruf                                                       */
/*

IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_Langtextzeilen' 
   and so.[id] = si.[id] and si.[name] = 'Ik_Aktuelle')
CREATE INDEX [Ik_Aktuelle] ON [ut_Langtextzeilen]
  (Abschluss,Art,Lkz,Nr,LfdNr)
--GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_GueltigeLangtextzeilen')
  DROP VIEW [dbo].[hwv_GueltigeLangtextzeilen]
--GO
CREATE VIEW dbo.hwv_GueltigeLangtextzeilen
AS
   SELECT * FROM Langtextzeilen Z1
   WHERE (Z1.Abschluss = 0) OR
         (Z1.Abschluss = 1 AND
          Z1.LfdNr = (SELECT MIN(LfdNr) FROM Langtextzeilen Z2 
                      WHERE Z2.Abschluss = 1 AND Z2.Art = Z1.Art AND Z2.Lkz = Z1.Lkz AND Z2.Nr = Z1.Nr
                     )
         )
--GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'hwp_GetLangtext')
  DROP PROCEDURE [dbo].[hwp_GetLangtext]
--GO
CREATE PROCEDURE dbo.hwp_GetLangtext
   @xArt  char(2)
  ,@xLkz  char(2)
  ,@xNr   char(18)
AS
  SELECT Art,Lkz,Nr,LfdNr,Abschluss,SoftBreak,LineFeeds,CntChar,Daten 
  FROM hwv_GueltigeLangtextzeilen
  WHERE Art=@xArt AND Lkz=@xLkz AND Nr=@xNr
  ORDER BY LfdNr
--GO
*/
