
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'V' and [name] = 'crv_TelefonListe')
	DROP VIEW dbo.crv_TelefonListe
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'V' and [name] = 'crv_ArtikelNePreise')
	DROP VIEW dbo.crv_ArtikelNePreise
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'V' and [name] = 'crv_ArtikelEkPreise')
	DROP VIEW dbo.crv_ArtikelEkPreise
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'V' and [name] = 'crv_ArtikelVkPreise')
	DROP VIEW dbo.crv_ArtikelVkPreise
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'V' and [name] = 'crv_VKPreiseGesamt')
	DROP VIEW dbo.crv_VKPreiseGesamt
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Langtextzeilen')
	DROP PROCEDURE dbo.crp_Langtextzeilen
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'V' and [name] = 'crv_PreisListe')
	DROP VIEW dbo.crv_PreisListe
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_PreisListe')
	DROP PROCEDURE dbo.crp_PreisListe
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_PreisListeEx')
	DROP PROCEDURE dbo.crp_PreisListeEx
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_ArtikelVerkauft')
	DROP PROCEDURE dbo.crp_ArtikelVerkauft
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_ArtikelKalkGruppe')
	DROP PROCEDURE dbo.crp_ArtikelKalkGruppe
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_UmsatzStatistik')
	DROP PROCEDURE dbo.crp_UmsatzStatistik
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'V' and [name] = 'crv_Lagerbestand')
	DROP VIEW dbo.crv_Lagerbestand
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'V' and [name] = 'crv_MindUberschreitung')
	DROP VIEW dbo.crv_MindUberschreitung
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'V' and [name] = 'crv_InvHeute')
	DROP VIEW dbo.crv_InvHeute
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'V' and [name] = 'crv_InvProtokoll')
	DROP VIEW dbo.crv_InvProtokoll
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'V' and [name] = 'crv_InvDiff')
	DROP VIEW dbo.crv_InvDiff
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_RABuch')
	DROP PROCEDURE dbo.crp_RABuch
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Journal')
	DROP PROCEDURE dbo.crp_Journal
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_EAKasse')
	DROP PROCEDURE dbo.crp_EAKasse
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Kassenbuch')
	DROP PROCEDURE dbo.crp_Kassenbuch
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'V' and [name] = 'crv_KontoTransNo')
	DROP VIEW dbo.crv_KontoTransNo
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Kontoblatt')
	DROP PROCEDURE dbo.crp_Kontoblatt
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_BuchProjekt')
	DROP PROCEDURE dbo.crp_BuchProjekt
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Buchblatt')
	DROP PROCEDURE dbo.crp_Buchblatt
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_FreieStapel')
	DROP PROCEDURE dbo.crp_FreieStapel
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Primanota')
	DROP PROCEDURE dbo.crp_Primanota
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'V' and [name] = 'crv_Jumboliste')
	DROP VIEW dbo.crv_Jumboliste
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Zusammenstellung')
	DROP PROCEDURE dbo.crp_Zusammenstellung
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Kalkulationen')
	DROP PROCEDURE dbo.crp_Kalkulationen
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Aufmass')
	DROP PROCEDURE dbo.crp_Aufmass
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Bestellung')
	DROP PROCEDURE dbo.crp_Bestellung
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_BestellungEx')
	DROP PROCEDURE dbo.crp_BestellungEx
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Bestelltexte')
	DROP PROCEDURE dbo.crp_Bestelltexte
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_PreisSpiegel')
	DROP PROCEDURE dbo.crp_PreisSpiegel
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Stammblatt')
	DROP PROCEDURE dbo.crp_Stammblatt
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Lagerfachkarte')
	DROP PROCEDURE dbo.crp_Lagerfachkarte
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Artikeletikett')
	DROP PROCEDURE dbo.crp_Artikeletikett
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'V' and [name] = 'crv_TextKleinteile')
	DROP VIEW dbo.crv_TextKleinteile
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'V' and [name] = 'crv_ErfassungsStapel')
	DROP VIEW dbo.crv_ErfassungsStapel
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_AktuellesJahr')
	DROP PROCEDURE dbo.crp_AktuellesJahr
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_AktuellerMonat')
	DROP PROCEDURE dbo.crp_AktuellerMonat
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_SL_Sachkonten')
	DROP PROCEDURE dbo.crp_SL_Sachkonten
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_SL_Kreditoren')
	DROP PROCEDURE dbo.crp_SL_Kreditoren
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_SL_Debitoren')
	DROP PROCEDURE dbo.crp_SL_Debitoren
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_RechnungsEingang')
	DROP PROCEDURE dbo.crp_RechnungsEingang
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'V' and [name] = 'crv_RessourcenPlanung')
	DROP VIEW dbo.crv_RessourcenPlanung
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Lagerbewegungen')
	DROP PROCEDURE dbo.crp_Lagerbewegungen




GO
CREATE VIEW dbo.crv_TelefonListe
AS
SELECT
         A.RecId ,1 AS MandId ,A.DESTDEBNR ,A.DESTTYP ,A.SUCHEN ,A.Anrede ,A.VorName
        ,A.Name1 ,A.Name2 ,A.Branche ,A.Strasse ,A.Ort ,A.Land ,A.Plz ,A.PsfPlz
        ,A.Psf ,A.PlzGrossEm ,A.zuHaenden ,A.zuHaeAnre ,A.BrAnrede ,A.BrGruss
        ,A.Telefon1 ,A.Tel1Kurz ,A.Telefon2 ,A.Tel2Kurz ,A.Telefon3 ,A.Tel3Kurz
        ,A.FaxNummer ,A.FaxKurz ,A.EMail ,A.SpKzJn ,A.Selection1
        ,A.Selection2 ,A.DatumAnlag ,A.DatumAende ,A.FibuKonto ,A.KzDivers
        ,A.KzAdr ,A.KzKh ,A.Kondition ,A.MahnenJN ,A.MahnKondit ,A.SkontoTole
        ,A.Blz1 ,A.KontoNr1 ,A.Blz2 ,A.KontoNr2 ,A.Blz3 ,A.KontoNr3 ,A.KreditLimi
        ,A.UstId1 ,A.Dokument ,A.Info1 ,A.Info2 ,A.Info3 ,A.Info4 ,A.LkzImport
        ,A.KzPreisPfl ,A.LiefKdNrD ,A.LiefKdNrE ,A.KostSt ,A.AbrGruppe ,A.SollStd
        ,A.BaGruppe ,A.EkLohn ,A.Annr ,A.Res4NG2 ,A.BruttoLohn
	,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.Adressen A
LEFT JOIN dbo.Anschriften AN
       ON AN.Nr = '01'
WHERE A.DESTTYP <> '10'


GO
CREATE VIEW dbo.crv_ArtikelNePreise
AS
SELECT
    NZ.RecId ,1 AS MandId ,NZ.Lkz ,NZ.Artnr ,NZ.NeKz
   ,NZ.Gewicht ,NZ.Gebinde ,NZ.BasisPreis, NZ.KgJeBasis
   ,TP.Tagespreis
   ,TP.KgJePreis AS KgJeTagespreis
FROM dbo.NeZuschlaege NZ
INNER JOIN dbo.Nichteisen TP
        ON TP.Art = NZ.NeKz


GO
CREATE VIEW dbo.crv_ArtikelEkPreise
AS
select
       S.RecId ,1 AS MandId ,S.Lkz ,S.Artnr ,S.Suchen ,S.Bez1 ,S.Bez2 ,S.Merkmal
      ,S.Textnr ,S.Kzpos ,S.Kzlager ,S.Einheit ,S.Kglohn ,S.Kgmat ,S.Zeit
      ,S.Zubehoerdm ,S.Vkl2 ,S.Vkm2 ,S.Kzzubehoer ,S.Bstnr ,S.Katalogseite
      ,S.Kzmwst ,S.Wargru ,S.Rabgru ,S.NeAnteil ,S.Pe ,S.Bruttoek ,S.Rabatt1
      ,S.Rabatt2 ,S.Ekmat ,S.Datumpreis ,S.Lkzlager ,S.Artnrlager ,S.Lagerort
      ,S.Minbest ,S.Bstbest ,S.Lagbest ,S.Invbest ,S.Wekum ,S.Wakum ,S.Datumlager
      ,S.Durchek ,S.Lagerwert ,S.Kzdrk1 ,S.Kzdrk2 ,S.Eklohnh ,S.Kzinventur ,S.Gebinde
      ,S.Lkztextnr ,S.D6 ,S.Invdatum ,S.Bildnummer ,S.Menge ,S.Vkladen ,S.Preiskz
      ,S.Textkz ,S.EANcode ,S.Gruppe ,S.D7
      ,ME.[Name]             AS EinheitenText
      ,PE.Faktor * S.Gebinde AS EkPreisFaktor
      ,KM.VhMat              AS AufschlagMaterial
      ,Oml.OptDataD          AS EkLohnJeMinute
      ,KL.VhLohn             AS AufschlagLohn
      ,RTRIM(Odm.OptDataC)   AS Waehrung
      ,(SELECT
          ISNULL( SUM( NZ.Gewicht / ISNULL(NULLIF(NZ.Gebinde,0),1) * 100 * 
                     (( TP.Tagespreis / ISNULL(NULLIF(TP.KgJePreis,0),1) ) - 
		      ( NZ.BasisPreis / ISNULL(NULLIF(NZ.KgJeBasis,0),1) ))), 0 )
        FROM dbo.NeZuschlaege NZ
        INNER JOIN dbo.Nichteisen TP
                ON TP.Art = NZ.NeKz
        WHERE NZ.Lkz = S.Lkz AND NZ.Artnr = S.Artnr
       )                     AS NePreisJe1      
FROM dbo.Stammpositionen S  
LEFT  JOIN dbo.Mengeneinheiten ME
        ON ME.Nr = STR(S.Einheit,4)
LEFT  JOIN dbo.Preiseinheiten  PE
        ON PE.Nr = STR(S.Pe,2)
LEFT  JOIN dbo.Kalkulationsgruppen KM
        ON KM.Nr = STR(S.Kgmat,2)
LEFT  JOIN dbo.Kalkulationsgruppen KL
        ON KL.Nr = STR(S.Kglohn,2)
LEFT  JOIN dbo.Optionen Oml        
        ON Oml.OptId = ' 7006'
LEFT  JOIN dbo.Optionen Odm        
        ON Odm.OptId = ' 7008'



GO
CREATE PROCEDURE dbo.crp_Langtextzeilen
				@pArt varchar(2)
                               ,@pLkz varchar(2)
			       ,@pNr  varchar(18)
AS
SELECT
    L.Daten AS Daten
FROM dbo.Langtextzeilen L
WHERE L.Art = @pArt AND L.Lkz = @pLkz AND L.Nr = @pNr




GO
CREATE PROCEDURE dbo.crp_PreisListe
				@VonArtNr varchar(20)
                               ,@BisArtNr varchar(20)
			       ,@VonSuch  varchar(15)
                               ,@BisSuch  varchar(15)
			       ,@VonWg    varchar(6)
                               ,@BisWg    varchar(6)
AS
SELECT
 	 VP.RecId ,VP.Lkz ,VP.Artnr ,VP.Suchen ,VP.Wargru ,VP.Bez1 ,VP.Bez2, VP.Textnr ,VP.Merkmal ,VP.Lagerort
        ,VP.Zeit ,VP.Bruttoek ,VP.Rabatt1 ,VP.Rabatt2 ,VP.Gebinde ,VP.EkPreisFaktor ,VP.AufschlagMaterial
        ,VP.EkLohnJeMinute ,VP.AufschlagLohn ,VP.NePreisJe1 ,VP.Rabgru ,VP.Eklohnh
        ,VP.Zubehoerdm                                                      AS Zubehoer
        ,1 AS MandId ,VP.Vkladen
	,(VP.Vkm2 + VP.Vkl2)                                                AS Vkm2
	,(VP.Vkm2 + VP.Vkl2) * ( 1 + O.OptDataD/100 )                       AS Vkm2Inc
        ,ISNULL( VP.Ekmat/ISNULL(NULLIF(VP.EkPreisFaktor,0),1) + 
                 VP.Zubehoerdm + VP.NePreisJe1/100 + 0.005 ,0  )            AS EKPreisJe1
        ,ISNULL( (VP.Ekmat/ISNULL(NULLIF(VP.EkPreisFaktor,0),1) + 
                  VP.Zubehoerdm + VP.NePreisJe1/100)  *
	         (1 + VP.AufschlagMaterial/100) + VP.Zeit * VP.EkLohnJeMinute *
	         (1 + VP.AufschlagLohn/100) ,0 )                            AS VK1gesamt      
	,ISNULL( M.[Name], '0' )                                            AS Mengeneinheit
	,0                                                                  AS VK2incl
	,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.crv_ArtikelEkPreise VP
LEFT  JOIN dbo.Optionen O
        ON O.OptId = ' 7003'
LEFT  JOIN dbo.Mengeneinheiten M
        ON VP.Einheit = M.Nr
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE ( @VonArtNr = 'NULL' OR VP.Lkz + VP.Artnr >= @VonArtNr ) AND 
      ( @BisArtNr = 'NULL' OR VP.Lkz + VP.Artnr <= @BisArtNr ) AND
      ( @VonSuch  = 'NULL' OR VP.Suchen         >= @VonSuch  ) AND 
      ( @BisSuch  = 'NULL' OR VP.Suchen         <= @BisSuch  ) AND
      ( @VonWg    = 'NULL' OR VP.Wargru         >= @VonWg    ) AND 
      ( @BisWg    = 'NULL' OR VP.Wargru         <= @BisWg    ) 



GO
CREATE PROCEDURE dbo.crp_PreisListeEx
								@VonArtNr varchar(20)
                               ,@BisArtNr varchar(20)
							   ,@VonSuch  varchar(15)
                               ,@BisSuch  varchar(15)
							   ,@VonWg    varchar(6)
                               ,@BisWg    varchar(6)
AS
SELECT
 	 VP.RecId ,VP.Lkz ,VP.Artnr ,VP.Suchen ,VP.Wargru ,VP.Bez1 ,VP.Bez2, VP.Textnr ,VP.Merkmal ,VP.Lagerort
    ,VP.Zeit ,VP.Bruttoek ,VP.Rabatt1 ,VP.Rabatt2 ,VP.Gebinde ,VP.EkPreisFaktor ,VP.AufschlagMaterial
    ,VP.EkLohnJeMinute ,VP.AufschlagLohn ,VP.NePreisJe1 ,VP.Rabgru ,VP.Eklohnh
    ,VP.Zubehoerdm                                                      AS Zubehoer
    ,1 AS MandId ,VP.Vkladen
	,(VP.Vkm2 + VP.Vkl2)                                                AS Vkm2
	,(VP.Vkm2 + VP.Vkl2) * ( 1 + O.OptDataD/100 )                       AS Vkm2Inc
    ,ISNULL( VP.Ekmat/ISNULL(NULLIF(VP.EkPreisFaktor,0),1) + 
             VP.Zubehoerdm + VP.NePreisJe1/100 + 0.005 ,0  )            AS EKPreisJe1
    ,ISNULL( (VP.Ekmat/ISNULL(NULLIF(VP.EkPreisFaktor,0),1) + 
              VP.Zubehoerdm + VP.NePreisJe1/100)  *
	         (1 + VP.AufschlagMaterial/100) + VP.Zeit * VP.EkLohnJeMinute *
	         (1 + VP.AufschlagLohn/100) ,0 )                            AS VK1gesamt      
	,ISNULL( M.[Name], '0' )                                            AS Mengeneinheit
	,0                                                                  AS VK2incl
	,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

    ,ISNULL(NZ_CU.Gewicht,0)     AS NE_CU_Gewicht
    ,ISNULL(NZ_CU.Gebinde,0)     AS NE_CU_Gebinde
    ,ISNULL(NZ_CU.BasisPreis,0)  AS NE_CU_BasisPreis
	,ISNULL( (NZ_CU.Gewicht / ISNULL(NULLIF(NZ_CU.Gebinde,0),1)) *
             (NZ_CU.BasisPreis / ISNULL(NULLIF(NZ_CU.KgJeBasis,0),1)) ,0) AS NE_CU_BasisPreisJe1
    ,ISNULL(NZ_CU.KgJeBasis,0)   AS NE_CU_KgJeBasis
    ,ISNULL(TP_CU.Tagespreis,0)  AS NE_CU_Tagespreis
	,ISNULL( (NZ_CU.Gewicht / ISNULL(NULLIF(NZ_CU.Gebinde,0),1)) *
	         (TP_CU.Tagespreis / ISNULL(NULLIF(TP_CU.KgJePreis,0),1)) ,0) AS NE_CU_TagespreisJe1
    ,ISNULL(TP_CU.KgJePreis,0)   AS NE_CU_KgJePreis
	,ISNULL( (NZ_CU.Gewicht / ISNULL(NULLIF(NZ_CU.Gebinde,0),1)) *
	         ((TP_CU.Tagespreis / ISNULL(NULLIF(TP_CU.KgJePreis,0),1)) -
              (NZ_CU.BasisPreis / ISNULL(NULLIF(NZ_CU.KgJeBasis,0),1))) ,0) AS NE_CU_Je1

    ,ISNULL(NZ_AL.Gewicht,0)     AS NE_AL_Gewicht
    ,ISNULL(NZ_AL.Gebinde,0)     AS NE_AL_Gebinde
    ,ISNULL(NZ_AL.BasisPreis,0)  AS NE_AL_BasisPreis
	,ISNULL( (NZ_AL.Gewicht / ISNULL(NULLIF(NZ_AL.Gebinde,0),1)) *
             (NZ_AL.BasisPreis / ISNULL(NULLIF(NZ_AL.KgJeBasis,0),1)) ,0) AS NE_AL_BasisPreisJe1
    ,ISNULL(NZ_AL.KgJeBasis,0)   AS NE_AL_KgJeBasis
    ,ISNULL(TP_AL.Tagespreis,0)  AS NE_AL_Tagespreis
	,ISNULL( (NZ_AL.Gewicht / ISNULL(NULLIF(NZ_AL.Gebinde,0),1)) *
	         (TP_AL.Tagespreis / ISNULL(NULLIF(TP_AL.KgJePreis,0),1)) ,0) AS NE_AL_TagespreisJe1
    ,ISNULL(TP_AL.KgJePreis,0)   AS NE_AL_KgJePreis
	,ISNULL( (NZ_AL.Gewicht / ISNULL(NULLIF(NZ_AL.Gebinde,0),1)) *
	         ((TP_AL.Tagespreis / ISNULL(NULLIF(TP_AL.KgJePreis,0),1)) -
              (NZ_AL.BasisPreis / ISNULL(NULLIF(NZ_AL.KgJeBasis,0),1))) ,0) AS NE_AL_Je1

	,ISNULL( (NZ_CU.Gewicht / ISNULL(NULLIF(NZ_CU.Gebinde,0),1)) *
	         ((TP_CU.Tagespreis / ISNULL(NULLIF(TP_CU.KgJePreis,0),1)) -
              (NZ_CU.BasisPreis / ISNULL(NULLIF(NZ_CU.KgJeBasis,0),1))) ,0)
     +
	 ISNULL( (NZ_AL.Gewicht / ISNULL(NULLIF(NZ_AL.Gebinde,0),1)) *
	         ((TP_AL.Tagespreis / ISNULL(NULLIF(TP_AL.KgJePreis,0),1)) -
              (NZ_AL.BasisPreis / ISNULL(NULLIF(NZ_AL.KgJeBasis,0),1))) ,0) AS NE_ALCU_Je1

FROM dbo.crv_ArtikelEkPreise VP
LEFT  JOIN dbo.Optionen O
        ON O.OptId = ' 7003'
LEFT  JOIN dbo.Mengeneinheiten M
        ON VP.Einheit = M.Nr
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
LEFT  JOIN dbo.NeZuschlaege NZ_CU
        ON NZ_CU.Lkz = VP.Lkz AND NZ_CU.Artnr = VP.Artnr AND NZ_CU.NeKz = 'CU'
LEFT  JOIN dbo.NeZuschlaege NZ_AL
        ON NZ_AL.Lkz = VP.Lkz AND NZ_AL.Artnr = VP.Artnr AND NZ_AL.NeKz = 'AL'
LEFT  JOIN dbo.Nichteisen TP_CU
        ON TP_CU.Art = NZ_CU.NeKz
LEFT  JOIN dbo.Nichteisen TP_AL
        ON TP_AL.Art = NZ_AL.NeKz
WHERE ( @VonArtNr = 'NULL' OR VP.Lkz + VP.Artnr >= @VonArtNr ) AND 
      ( @BisArtNr = 'NULL' OR VP.Lkz + VP.Artnr <= @BisArtNr ) AND
      ( @VonSuch  = 'NULL' OR VP.Suchen         >= @VonSuch  ) AND 
      ( @BisSuch  = 'NULL' OR VP.Suchen         <= @BisSuch  ) AND
      ( @VonWg    = 'NULL' OR VP.Wargru         >= @VonWg    ) AND 
      ( @BisWg    = 'NULL' OR VP.Wargru         <= @BisWg    ) 



GO
CREATE PROCEDURE dbo.crp_ArtikelKalkGruppe
				@VonArtNr varchar(20)
                               ,@BisArtNr varchar(20)
                               ,@VonLief  varchar(2)
                               ,@BisLief  varchar(2)
                               ,@VonKG    varchar(10)
                               ,@BisKG    varchar(10)
AS
IF ISNUMERIC(@VonKG)=0  SET @VonKG='0'
IF ISNUMERIC(@BisKG)=0  SET @BisKG='0'

SELECT
   S.Artnr ,S.Lkz ,S.Bez1 ,S.Bez2 ,S.Merkmal ,S.Wargru ,S.Rabgru
  ,ISNULL(S.Kglohn,'')       AS Kglohn 
  ,ISNULL(S.Kgmat, '')       AS Kgmat
  ,ISNULL(K.VhMat, '')       AS AufMat
  ,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.Stammpositionen S
LEFT  JOIN dbo.Kalkulationsgruppen K
        ON S.Kgmat = K.Nr
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE (@VonArtNr='NULL' OR S.Artnr >= @VonArtNr)           AND 
      (@BisArtNr='NULL' OR S.Artnr <= @BisArtNr)           AND
      (@VonLief ='00'   OR S.Lkz   >= @VonLief)            AND 
      (@BisLief ='00'   OR S.Lkz   <= @BisLief)            AND
      (@VonKG   ='0'    OR S.Kgmat >= CONVERT(int,@VonKG)) AND 
      (@BisKG   ='0'    OR S.Kgmat <= CONVERT(int,@BisKG)) AND
       S.Artnr <> ''




GO


--------------------------------------------------------------------------------------------------------
--- execute dbo.crp_ArtikelVerkauft 'AA001','AA001','01.01.1998','31.05.2019','    ','    ','    ','    '
--- execute dbo.crp_ArtikelVerkauft 'AA001','AA001','01.01.1998','31.05.2019',NULL,NULL,NULL,NULL
--- execute dbo.crp_ArtikelVerkauft 'AA001','AA001',NULL,NULL,NULL,NULL,NULL,NULL
--- execute dbo.crp_ArtikelVerkauft NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
--- execute dbo.crp_ArtikelVerkauft '','',NULL,NULL,'','','',''
--------------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.crp_ArtikelVerkauft
				@VonArtNr varchar(20)
                               ,@BisArtNr varchar(20)
                               ,@VonDatum varchar(10)
                               ,@BisDatum varchar(10)
                               ,@pVonKdNr  varchar(15)
                               ,@pBisKdNr  varchar(15)
                               ,@pVonWGru  varchar(6)
                               ,@pBisWGru  varchar(6)
AS

DECLARE @m_VonDatum varchar(10)
DECLARE @m_BisDatum varchar(10)
SET @m_VonDatum = 'NULL'
SET @m_BisDatum = 'NULL'

DECLARE @VonKdNr  varchar(15)
DECLARE @BisKdNr  varchar(15)
DECLARE @VonWGru  varchar(6)
DECLARE @BisWGru  varchar(6)

SET @VonKdNr = ISNULL(@pVonKdNr,'')
SET @BisKdNr = ISNULL(@pBisKdNr,'')
SET @VonWGru = ISNULL(@pVonWGru,'')
SET @BisWGru = ISNULL(@pBisWGru,'')

IF ISDATE(@VonDatum) <> 0 
    SET @m_VonDatum = SUBSTRING(@VonDatum,7,4)+SUBSTRING(@VonDatum,4,2)+SUBSTRING(@VonDatum,1,2) 
IF ISDATE(@BisDatum) <> 0 
    SET @m_BisDatum = SUBSTRING(@BisDatum,7,4)+SUBSTRING(@BisDatum,4,2)+SUBSTRING(@BisDatum,1,2) 
	
SELECT
   BP.ArtNr ,BP.Ktxt1 ,BP.Ktxt2 ,BP.Ktxt3 ,BP.Menge ,BP.Me
  ,B.BELEGART ,B.BELEGNR ,B.KDNR ,B.TAG ,B.MONAT ,B.JAHR ,B.ANREDE ,B.VORNAME ,B.NAME1
  ,B.NAME2 ,B.STRASSE ,B.LAND ,B.PLZ ,B.ORT ,B.VORHABEN ,B.BETREFF1
  ,BP.AbsMenge ,BP.EPMat ,BP.EPLohn ,BP.EPPos ,BP.GPPos ,BP.AbsGP
  ,BP.EkMat ,BP.VkMat
  ,S.Bruttoek              AS Stamm_Brutto
  ,S.Wargru                AS Stamm_WarGru
  ,S.Rabgru                AS Stamm_RabGru
  ,PE.Faktor  * S.Gebinde  AS Stamm_Faktor
  ,PEB.Faktor * BP.Gebinde AS Beleg_Faktor

  ,(SELECT SUM(ISNULL(BP1.Menge,0)) FROM dbo.BelPos BP1
    INNER JOIN dbo.Belege B1
            ON BP1.PrjNr = B1.PRJNR AND B1.BELEGART = BP1.BelArt AND B1.BELEGNR = BP1.BelNr
    INNER JOIN dbo.Stammpositionen S1
            ON S1.Lkz+S1.Artnr = BP1.ArtNr
    LEFT  JOIN dbo.Preiseinheiten  PE1
            ON PE1.Nr = BP1.Pe
    WHERE BP1.ArtNr = BP.ArtNr AND
          (@m_VonDatum = 'NULL' OR (B1.JAHR + B1.MONAT + B1.TAG >= @m_VonDatum)) AND 
          (@m_BisDatum = 'NULL' OR (B1.JAHR + B1.MONAT + B1.TAG <= @m_BisDatum)) AND
          (@VonKdNr    = ''     OR B1.KDNR   >= @VonKdNr ) AND 
          (@BisKdNr    = ''     OR B1.KDNR   <= @BisKdNr ) AND
          (@VonWGru    = ''     OR S1.Wargru >= @VonWGru ) AND 
          (@BisWGru    = ''     OR S1.Wargru <= @BisWGru ) AND
          (BP1.ArtNr <> '' AND (BP1.SatzArt = 7 OR BP.SatzArt = 8) AND 
           BP1.VkMat <> 0 AND BP1.Menge <> 0 AND BP.MatJN = 1)
   )                         AS SumMenge

  ,(SELECT SUM(BP1.EkMat * BP1.Menge / ISNULL(NULLIF(PE1.Faktor * BP1.Gebinde,0),1)) FROM dbo.BelPos BP1
    INNER JOIN dbo.Belege B1
            ON BP1.PrjNr = B1.PRJNR AND B1.BELEGART = BP1.BelArt AND B1.BELEGNR = BP1.BelNr
    INNER JOIN dbo.Stammpositionen S1
            ON S1.Lkz+S1.Artnr = BP1.ArtNr
    LEFT  JOIN dbo.Preiseinheiten  PE1
            ON PE1.Nr = BP1.Pe
    WHERE BP1.ArtNr = BP.ArtNr AND
          (@m_VonDatum = 'NULL' OR (B1.JAHR + B1.MONAT + B1.TAG >= @m_VonDatum)) AND 
          (@m_BisDatum = 'NULL' OR (B1.JAHR + B1.MONAT + B1.TAG <= @m_BisDatum)) AND
          (@VonKdNr    = ''     OR B1.KDNR   >= @VonKdNr ) AND 
          (@BisKdNr    = ''     OR B1.KDNR   <= @BisKdNr ) AND
          (@VonWGru    = ''     OR S1.Wargru >= @VonWGru ) AND 
          (@BisWGru    = ''     OR S1.Wargru <= @BisWGru ) AND
          (BP1.ArtNr <> '' AND (BP1.SatzArt = 7 OR BP.SatzArt = 8) AND 
           BP1.VkMat <> 0 AND BP1.Menge <> 0 AND BP.MatJN = 1)
   )                         AS SumEkMat

  ,(SELECT SUM(ISNULL(BP1.VkMat * BP1.Menge,0)) FROM dbo.BelPos BP1
    INNER JOIN dbo.Belege B1
            ON BP1.PrjNr = B1.PRJNR AND B1.BELEGART = BP1.BelArt AND B1.BELEGNR = BP1.BelNr
    INNER JOIN dbo.Stammpositionen S1
            ON S1.Lkz+S1.Artnr = BP1.ArtNr
    LEFT  JOIN dbo.Preiseinheiten  PE1
            ON PE1.Nr = BP1.Pe
    WHERE BP1.ArtNr = BP.ArtNr AND
          (@m_VonDatum = 'NULL' OR (B1.JAHR + B1.MONAT + B1.TAG >= @m_VonDatum)) AND 
          (@m_BisDatum = 'NULL' OR (B1.JAHR + B1.MONAT + B1.TAG <= @m_BisDatum)) AND
          (@VonKdNr    = ''     OR B1.KDNR   >= @VonKdNr ) AND 
          (@BisKdNr    = ''     OR B1.KDNR   <= @BisKdNr ) AND
          (@VonWGru    = ''     OR S1.Wargru >= @VonWGru ) AND 
          (@BisWGru    = ''     OR S1.Wargru <= @BisWGru ) AND
          (BP1.ArtNr <> '' AND (BP1.SatzArt = 7 OR BP.SatzArt = 8) AND 
           BP1.VkMat <> 0 AND BP1.Menge <> 0 AND BP.MatJN = 1)
   )                         AS SumVkMat

  ,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.BelPos BP
INNER JOIN dbo.Belege B
        ON BP.PrjNr = B.PRJNR AND B.BELEGART = BP.BelArt AND B.BELEGNR = BP.BelNr
INNER JOIN dbo.Stammpositionen S
        ON S.Lkz+S.Artnr = BP.ArtNr
LEFT  JOIN dbo.Preiseinheiten  PE
        ON PE.Nr = STR(S.Pe,2)
LEFT  JOIN dbo.Preiseinheiten  PEB
        ON PEB.Nr = BP.Pe
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE ( B.BELEGART = ' 1' OR B.BELEGART = ' 3' OR B.BELEGART = ' 4' ) AND
      ( @VonArtNr   = ''     OR BP.ArtNr >= @VonArtNr ) AND 
      ( @BisArtNr   = ''     OR BP.ArtNr <= @BisArtNr ) AND
      ( @m_VonDatum = 'NULL' OR (B.JAHR + B.MONAT + B.TAG >= @m_VonDatum)) AND 
      ( @m_BisDatum = 'NULL' OR (B.JAHR + B.MONAT + B.TAG <= @m_BisDatum)) AND
      ( @VonKdNr    = ''     OR B.KDNR >= @VonKdNr )    AND 
      ( @BisKdNr    = ''     OR B.KDNR <= @BisKdNr )    AND
      ( @VonWGru    = ''     OR S.Wargru >= @VonWGru )  AND 
      ( @BisWGru    = ''     OR S.Wargru <= @BisWGru )  AND
      ( BP.ArtNr <> '' AND (BP.SatzArt = 7 OR BP.SatzArt = 8) AND 
        BP.VkMat <> 0 AND BP.Menge <> 0 AND BP.MatJN = 1 )
GO

--------------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.crp_UmsatzStatistik
                                @FltJahr   varchar(4)
                               ,@FltMon    varchar(2)
                               ,@KeyVon    varchar(15)
                               ,@KeyBis    varchar(15)
                               ,@VonDatum  varchar(10)
                               ,@BisDatum  varchar(10)
AS

IF ( @KeyVon IS NULL ) OR ( @KeyVon = '' )  SET @KeyVon = 'NULL'
IF ( @KeyBis IS NULL ) OR ( @KeyBis = '' )  SET @KeyBis = 'NULL'

DECLARE @p_FltJahr varchar(4) 
DECLARE @p_FltMon  varchar(2)
SET @p_FltJahr = '00'
SET @p_FltMon  = '00'
 
IF ( @FltJahr IS NOT NULL ) AND ( @FltJahr <> '' ) AND ( LEN(@FltJahr) > 0 )
      SET @p_FltJahr = RIGHT( '0' + @FltJahr ,2 )
IF ( @FltMon IS NOT NULL ) AND ( @FltMon <> '' ) AND ( LEN(@FltMon) > 0 )
      SET @p_FltMon  = RIGHT( '0' + @FltMon  ,2 )

DECLARE @p_VonDatum varchar(10) 
DECLARE @p_BisDatum varchar(10) 
SET @p_VonDatum = 'NULL'
SET @p_BisDatum = 'NULL'

IF ISDATE( @VonDatum ) = 1
  SET @p_VonDatum = RIGHT(@VonDatum, 4) + SUBSTRING(@VonDatum,4,2) + LEFT(@VonDatum,2)

IF ISDATE( @BisDatum ) = 1 
  SET @p_BisDatum = RIGHT(@BisDatum, 4) + SUBSTRING(@BisDatum,4,2) + LEFT(@BisDatum,2)

SELECT
       B.BELEGNR, B.BELARTTEXT, B.KDNR, B.TAG, B.MONAT, B.JAHR, B.VORNAME, B.NAME1
      ,B.STRASSE, B.PLZ, B.ORT, B.BETREFF1, B.BETREFF2, B.BETREFF3, B.NETTO, B.WAEHRUNG

      ,ISNULL((
               SELECT SUM( ISNULL(BEL.NETTO,0) / ISNULL(NULLIF(BEL.KURSZUEURO,0),1) ) 
               FROM dbo.Belege BEL 
               WHERE ( BEL.KDNR = A.DESTDEBNR AND (BEL.BELEGART = ' 1' OR BEL.BELEGART = ' 3' OR BEL.BELEGART = ' 4')) AND
                     ( @p_FltJahr  = '00'   OR RIGHT(BEL.JAHR, 2) = @p_FltJahr ) AND
                     ( @p_FltMon   = '00'   OR BEL.MONAT = @p_FltMon  ) AND
                     ( @p_VonDatum = 'NULL' OR BEL.JAHR + BEL.MONAT + BEL.TAG >= @p_VonDatum  ) AND
                     ( @p_BisDatum = 'NULL' OR BEL.JAHR + BEL.MONAT + BEL.TAG <= @p_BisDatum  ) 
              ), 0) -
       ISNULL((
               SELECT SUM( ISNULL(BEL.NETTO,0) / ISNULL(NULLIF(BEL.KURSZUEURO,0),1) ) 
               FROM dbo.Belege BEL 
               WHERE ( BEL.KDNR = A.DESTDEBNR AND BEL.BELEGART = ' 2' ) AND                               
                     ( @p_FltJahr  = '00'   OR RIGHT(BEL.JAHR, 2) = @p_FltJahr ) AND
                     ( @p_FltMon   = '00'   OR BEL.MONAT = @p_FltMon  ) AND
                     ( @p_VonDatum = 'NULL' OR BEL.JAHR + BEL.MONAT + BEL.TAG >= @p_VonDatum  ) AND
                     ( @p_BisDatum = 'NULL' OR BEL.JAHR + BEL.MONAT + BEL.TAG <= @p_BisDatum  ) 
              ), 0)                AS Umsatz

      ,ISNULL( A.SpKzJn, '0' )   AS Sp
      ,ISNULL( A.Telefon1, '0' ) AS Telefon
      ,ISNULL( W.Eurokurs, 1 )   AS Eurokurs	 
      ,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.Adressen A
INNER JOIN dbo.Belege B
        ON ( B.BELEGART = ' 1' OR B.BELEGART = ' 2' OR B.BELEGART = ' 3' OR B.BELEGART = ' 4') AND
             B.KDNR = A.DESTDEBNR
LEFT  JOIN dbo.Waehrungen W
        ON W.Kuerzel = B.WAEHRUNG
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE ( @KeyVon     = 'NULL' OR A.DESTDEBNR >= @KeyVon  ) AND 
      ( @KeyBis     = 'NULL' OR A.DESTDEBNR <= @KeyBis  ) AND
      ( @p_FltJahr  = '00'   OR RIGHT(B.JAHR, 2) = @p_FltJahr ) AND
      ( @p_FltMon   = '00'   OR B.MONAT = @p_FltMon  ) AND
      ( @p_VonDatum = 'NULL' OR B.JAHR + B.MONAT + B.TAG >= @p_VonDatum  ) AND
      ( @p_BisDatum = 'NULL' OR B.JAHR + B.MONAT + B.TAG <= @p_BisDatum  ) 
ORDER BY A.DESTDEBNR




GO
CREATE VIEW dbo.crv_Lagerbestand
AS
SELECT
         S.RecId ,1 AS MandId ,S.Lkz ,S.Artnr ,S.Suchen ,S.Bez1 ,S.Bez2 ,S.Merkmal
        ,S.Textnr ,S.Kzpos ,S.Kzlager ,S.Einheit ,S.Kglohn ,S.Kgmat ,S.Zeit
        ,S.Zubehoerdm ,S.Vkl2 ,S.Vkm2 ,S.Kzzubehoer ,S.Bstnr ,S.Katalogseite
        ,S.Kzmwst ,S.Wargru ,S.Rabgru ,S.NeAnteil ,S.Pe ,S.Bruttoek ,S.Rabatt1
        ,S.Rabatt2 ,S.Ekmat ,S.Datumpreis ,S.Lkzlager ,S.Artnrlager ,S.Lagerort
        ,S.Minbest ,S.Bstbest ,S.Lagbest ,S.Invbest ,S.Wekum ,S.Wakum ,S.Datumlager
        ,S.Durchek ,S.Lagerwert ,S.Kzdrk1 ,S.Kzdrk2 ,S.Eklohnh ,S.Kzinventur ,S.Gebinde
        ,S.Lkztextnr ,S.D6 ,S.Invdatum ,S.Bildnummer ,S.Menge ,S.Vkladen ,S.Preiskz
        ,S.Textkz ,S.EANcode ,S.Gruppe ,S.D7
	,ISNULL( M.[Name], '0' )   AS Mengeneinheit
        ,S.Gebinde * PE.Faktor     AS PrsFaktor
	,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.Stammpositionen S
LEFT JOIN dbo.Mengeneinheiten M
       ON S.Einheit = M.Nr
LEFT  JOIN dbo.Preiseinheiten  PE
        ON PE.Nr = STR(S.Pe,2)
LEFT JOIN dbo.Anschriften AN
       ON AN.Nr = '01'
WHERE S.Kzlager = 'J' AND S.Lagbest <> 0



GO
CREATE VIEW dbo.crv_InvDiff
AS
SELECT
         S.RecId ,1 AS MandId ,S.Lkz ,S.Artnr ,S.Suchen ,S.Bez1 ,S.Bez2 ,S.Merkmal
        ,S.Textnr ,S.Kzpos ,S.Kzlager ,S.Einheit ,S.Kglohn ,S.Kgmat ,S.Zeit
        ,S.Zubehoerdm ,S.Vkl2 ,S.Vkm2 ,S.Kzzubehoer ,S.Bstnr ,S.Katalogseite
        ,S.Kzmwst ,S.Wargru ,S.Rabgru ,S.NeAnteil ,S.Pe ,S.Bruttoek ,S.Rabatt1
        ,S.Rabatt2 ,S.Ekmat ,S.Datumpreis ,S.Lkzlager ,S.Artnrlager ,S.Lagerort
        ,S.Minbest ,S.Bstbest ,S.Lagbest ,S.Invbest ,S.Wekum ,S.Wakum ,S.Datumlager
        ,S.Durchek ,S.Lagerwert ,S.Kzdrk1 ,S.Kzdrk2 ,S.Eklohnh ,S.Kzinventur ,S.Gebinde
        ,S.Lkztextnr ,S.D6 ,S.Invdatum ,S.Bildnummer ,S.Menge ,S.Vkladen ,S.Preiskz
        ,S.Textkz ,S.EANcode ,S.Gruppe ,S.D7
	,ISNULL( M.[Name], '0' )   AS Mengeneinheit
	,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
        ,( SELECT COUNT( L.ARTNR ) FROM dbo.Lagerbewegungen L
           WHERE L.ARTNR = S.Lkz + S.Artnr ) AS LagZeilen
FROM dbo.Stammpositionen S
LEFT JOIN dbo.Mengeneinheiten M
       ON S.Einheit = M.Nr
LEFT JOIN dbo.Anschriften AN
       ON AN.Nr = '01'
WHERE S.Kzlager = 'J'





GO
CREATE VIEW dbo.crv_MindUberschreitung
AS
SELECT
         S.RecId ,1 AS MandId ,S.Lkz ,S.Artnr ,S.Suchen ,S.Bez1 ,S.Bez2 ,S.Merkmal
        ,S.Textnr ,S.Kzpos ,S.Kzlager ,S.Einheit ,S.Kglohn ,S.Kgmat ,S.Zeit
        ,S.Zubehoerdm ,S.Vkl2 ,S.Vkm2 ,S.Kzzubehoer ,S.Bstnr ,S.Katalogseite
        ,S.Kzmwst ,S.Wargru ,S.Rabgru ,S.NeAnteil ,S.Pe ,S.Bruttoek ,S.Rabatt1
        ,S.Rabatt2 ,S.Ekmat ,S.Datumpreis ,S.Lkzlager ,S.Artnrlager ,S.Lagerort
        ,S.Minbest ,S.Bstbest ,S.Lagbest ,S.Invbest ,S.Wekum ,S.Wakum ,S.Datumlager
        ,S.Durchek ,S.Lagerwert ,S.Kzdrk1 ,S.Kzdrk2 ,S.Eklohnh ,S.Kzinventur ,S.Gebinde
        ,S.Lkztextnr ,S.D6 ,S.Invdatum ,S.Bildnummer ,S.Menge ,S.Vkladen ,S.Preiskz
        ,S.Textkz ,S.EANcode ,S.Gruppe ,S.D7
	,ISNULL( M.[Name], '0' )           AS Mengeneinheit
	,ISNULL( EK.NePreisJe1, 0 )        AS NeJe100	
	,ISNULL( EK.EkPreisFaktor, 0 )     AS PreisFaktor      
	,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.Stammpositionen S
INNER JOIN dbo.crv_ArtikelEkPreise EK
        ON EK.Artnr = S.Artnr AND EK.Lkz = S.Lkz And S.Minbest > S.Lagbest
LEFT  JOIN dbo.Mengeneinheiten M
        ON S.Einheit = M.Nr
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE S.Kzlager = 'J' AND S.Minbest > S.Lagbest



GO
CREATE VIEW dbo.crv_InvHeute
AS
SELECT
         S.RecId ,1 AS MandId ,S.Lkz ,S.Artnr ,S.Suchen ,S.Bez1 ,S.Bez2 ,S.Merkmal
        ,S.Textnr ,S.Kzpos ,S.Kzlager ,S.Einheit ,S.Kglohn ,S.Kgmat ,S.Zeit
        ,S.Zubehoerdm ,S.Vkl2 ,S.Vkm2 ,S.Kzzubehoer ,S.Bstnr ,S.Katalogseite
        ,S.Kzmwst ,S.Wargru ,S.Rabgru ,S.NeAnteil ,S.Pe ,S.Bruttoek ,S.Rabatt1
        ,S.Rabatt2 ,S.Ekmat ,S.Datumpreis ,S.Lkzlager ,S.Artnrlager ,S.Lagerort
        ,S.Minbest ,S.Bstbest ,S.Lagbest ,S.Invbest ,S.Wekum ,S.Wakum ,S.Datumlager
        ,S.Durchek ,S.Lagerwert ,S.Kzdrk1 ,S.Kzdrk2 ,S.Eklohnh ,S.Kzinventur ,S.Gebinde
        ,S.Lkztextnr ,S.D6 ,S.Invdatum ,S.Bildnummer ,S.Menge ,S.Vkladen ,S.Preiskz
        ,S.Textkz ,S.EANcode ,S.Gruppe ,S.D7
	,(SELECT
	     ISNULL( SUM( L.MENGE ), 0)
          FROM dbo.Lagerbewegungen L
          WHERE L.ARTNR = S.Lkz + S.Artnr AND
                L.KZ_ZA = 3 AND
                L.TAG +'.'+ L.MONAT +'.'+ L.JAHR = CONVERT(varchar(20), GETDATE(), 104)
          )                        AS L_Menge
	,ISNULL( M.[Name], '0' )   AS Mengeneinheit
	,ISNULL( P.Faktor, 0   )   AS Faktor
	,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.Stammpositionen S
LEFT  JOIN dbo.Mengeneinheiten M
        ON S.Einheit = M.Nr
LEFT  JOIN dbo.Preiseinheiten P
        ON STR(S.Pe,2) = P.Nr
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE S.Kzlager = 'J'



GO
CREATE VIEW dbo.crv_InvProtokoll
AS
SELECT
         L.RecId ,1 AS MandId ,L.ARTNR ,L.TAG ,L.TAGDUMMY
        ,L.MONAT ,L.MONDUMMY ,L.JAHR ,L.KZ_ZA ,L.INFO
        ,L.MENGE ,L.VERBUCHT ,L.EKPREIS ,L.GEBINDE ,L.PrjNr
	,S.Lkz+'/'+S.Artnr         AS ArtikelNr
	,S.Lkz                     AS Lkz
	,S.Lagerort                AS Ort
	,S.Bez1                    AS KurzText
	,S.Lagbest                 AS Lagbest
	,S.Invbest                 AS Invbest
	,S.Durchek                 AS Durchek
        ,S.Kzinventur
	,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.Stammpositionen S
INNER JOIN dbo.Lagerbewegungen L
        ON L.ARTNR = S.Lkz + S.Artnr
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE S.Kzlager = 'J' And S.Kzinventur = 1 And S.Lagbest <> 0




GO
CREATE PROCEDURE dbo.crp_RABuch
		          @FltMon     varchar(2)
   		         ,@FltJahr    varchar(4)
                         ,@VonDatum   varchar(10)
                         ,@BisDatum   varchar(10)
                         ,@VonBeleg   varchar(8)
                         ,@BisBeleg   varchar(8)
AS
DECLARE @m_FltMon   varchar(2)
DECLARE @m_FltJahr  varchar(4)
DECLARE @m_VonDatum varchar(10)
DECLARE @m_BisDatum varchar(10)
SET @m_VonDatum = 'NULL'
SET @m_BisDatum = 'NULL'
SET @m_FltMon   = '00'
SET @m_FltJahr  = '0000'

IF (@FltMon IS NOT NULL) AND (@FltMon <> '') AND (@FltMon <> '0')
    SET @m_FltMon = RIGHT('00' + LTRIM(RTRIM(@FltMon)), 2)
IF (@FltJahr IS NOT NULL) AND (@FltJahr <> '') AND (@FltJahr <> 'NULL')
    SET @m_FltJahr = RIGHT('0000' + LTRIM(RTRIM(@FltJahr)), 2)
IF ISDATE(@VonDatum) = 1
   SET @m_VonDatum = RIGHT(@VonDatum,4) +'.'+ SUBSTRING(@VonDatum,4,2) +'.'+ LEFT(@VonDatum,2)
IF ISDATE(@BisDatum) = 1
   SET @m_BisDatum = RIGHT(@BisDatum,4) +'.'+ SUBSTRING(@BisDatum,4,2) +'.'+ LEFT(@BisDatum,2)

SELECT
         B.RecId ,1 AS MandId ,B.PRJNR ,B.BELEGART ,B.BELEGNR ,B.VERSION
        ,B.LFDNR ,B.NRKREIS ,B.BELARTTEXT ,B.ANZNACHTR ,B.ANZRAEUME 
        ,B.ANZBAUPHA ,B.BAUPHAAKT ,B.KDNR ,B.BLNR ,B.TAG ,B.TAGDUMMY 
        ,B.MONAT ,B.MONDUMMY ,B.JAHR ,B.SUBMISSION ,B.BAUBEGINN 
        ,B.BAUFERTIG ,B.FERTIGJN ,B.EIGANSCH ,B.PHASE ,B.SKONTOTAGE
        ,B.SKONTOSATZ ,B.DRKFFTITEL ,B.DRKINCMWST ,B.DRKAUFMASS ,B.DRKLM
        ,B.DRKZUBEHOE ,B.DRKDMRAB ,B.DRKRABATT ,B.DRKNULLWER ,B.AUFLOHN
        ,B.AUFMAT ,B.AUFFREMD ,B.MITTLOHN ,B.EXACT ,B.KGRPS ,B.KZZUBEHOER
        ,B.LOHNANTEIL ,B.KZVK2 ,B.TITEL ,B.MANUELL ,B.OZMASKE ,B.ANREDE
        ,B.VORNAME ,B.NAME1 ,B.NAME2 ,B.ZUHAENDEN ,B.STRASSE ,B.LAND
        ,B.PLZ ,B.ORT ,B.BRANREDE ,B.VORHABEN ,B.BETREFF1 ,B.BETREFF2
        ,B.BETREFF3 ,B.EDMENGEN ,B.EDARTNR ,B.EDLPOSNR ,B.DRKNETTO
        ,B.UEBERTRAG ,B.TITELZUS ,B.DRKTEXT ,B.DRKUPOS ,B.DRKUPOSPR 
        ,B.EDFAKTOR ,B.FAELLTAGE ,B.DRKARTNR ,B.DRKBRANRE ,B.UNSERZEI
        ,B.SICHBEHALT ,B.DRKVORTEXT ,B.DRKNACTEXT ,B.DRKTITSUMM ,B.JOURNAL
        ,B.ZUVERBUCH ,B.VERBUCHT ,B.NETTO ,B.MWST ,B.MWSTSATZ ,B.BRUTTO
        ,B.VERBUCHTDM ,B.SUMMEMAT ,B.SUMMELOHN ,B.SUMMEFREMD ,B.SUMMEZEIT
        ,B.SUMMEEKFR ,B.SUMMEEKM ,B.GEMEINK ,B.GEMEINKVH ,B.MAHNENJN ,B.MAHNSTUFE
        ,B.BEZAHLT ,B.ZULETZTBEZ ,B.ZAHLDATUM ,B.WAEHRUNG ,B.KURSZUEURO
        ,B.DRKBANK ,B.EGRUMAT ,B.EGRULOHN ,B.EGRUFREMD ,B.EGRUGEMEIN ,B.KST
        ,B.KTR ,B.DEBKONTO ,B.NERECHNEN ,B.NEAUSCHLAG ,B.NETRENNEN ,B.FREMDPROJ
        ,B.GAEBJN ,B.GAEBNAME ,B.GAEBPHASE ,B.NETTO1 ,B.NETTO2 ,B.UST1 ,B.UST2
        ,B.USTVH2 ,B.RES4NG2 ,B.NUMRECS
	,ISNULL( O.OptDataC, 'EUR' )    AS OptWaehrung
	,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.Belege B
LEFT  JOIN dbo.Optionen O
        ON O.OptId = ' 7008'
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE (B.JOURNAL = 1) AND
      ( B.BELEGART  = ' 1'   OR B.BELEGART = ' 2' OR 
        B.BELEGART  = ' 3'   OR B.BELEGART = ' 4' ) AND
      ( @m_FltMon   = '00'   OR B.MONAT = @m_FltMon ) AND
      ( @m_FltJahr  = '0000' OR RIGHT(B.JAHR,2) = RIGHT(@m_FltJahr,2) ) AND
      ((@m_VonDatum = 'NULL') OR (ISDATE(B.TAG +'.'+ B.MONAT +'.'+ B.JAHR) = 1 AND 
                                        B.JAHR +'.'+ B.MONAT +'.'+ B.TAG  >= @m_VonDatum)) AND
      ((@m_BisDatum = 'NULL') OR (ISDATE(B.TAG +'.'+ B.MONAT +'.'+ B.JAHR) = 1 AND 
                                        B.JAHR +'.'+ B.MONAT +'.'+ B.TAG  <= @m_BisDatum)) AND
      ((@VonBeleg = 'NULL') OR (@VonBeleg = '') OR (LTRIM(RTRIM(B.BELEGNR)) >= @VonBeleg)) AND
      ((@BisBeleg = 'NULL') OR (@BisBeleg = '') OR (LTRIM(RTRIM(B.BELEGNR)) <= @BisBeleg)) 



GO
CREATE PROCEDURE dbo.crp_Journal
	                  @BelegArt varchar(2)
                         ,@VonDatum varchar(10)
                         ,@BisDatum varchar(10)
                         ,@VonKnd   varchar(15)
                         ,@BisKnd   varchar(15)
                         ,@VonPrj   varchar(8)
                         ,@BisPrj   varchar(8)
AS
DECLARE @Von varchar(10)
DECLARE @Bis varchar(10)
DECLARE @Art varchar(2)
SET @Von = '0000.00.00'
SET @Bis = '0000.00.00'
SET @Art = '00'

IF @BelegArt <> '' AND @BelegArt <> '0' AND @BelegArt <> '10' AND @BelegArt <> '11' AND LEN(@BelegArt) > 0
   SET @Art = @BelegArt
IF @BelegArt = '12'
   SET @Art = '22'
IF ISDATE(@VonDatum) = 1
   SET @Von = RIGHT(@VonDatum,4) +'.'+ SUBSTRING(@VonDatum,4,2) +'.'+ LEFT(@VonDatum,2)
IF ISDATE(@BisDatum) = 1
   SET @Bis = RIGHT(@BisDatum,4) +'.'+ SUBSTRING(@BisDatum,4,2) +'.'+ LEFT(@BisDatum,2)

SELECT
         B.RecId ,1 AS MandId ,B.PRJNR ,B.BELEGART ,B.BELEGNR ,B.VERSION
        ,B.LFDNR ,B.NRKREIS ,B.BELARTTEXT ,B.ANZNACHTR ,B.ANZRAEUME 
        ,B.ANZBAUPHA ,B.BAUPHAAKT ,B.KDNR ,B.BLNR ,B.TAG ,B.TAGDUMMY 
        ,B.MONAT ,B.MONDUMMY ,B.JAHR ,B.SUBMISSION ,B.BAUBEGINN 
        ,B.BAUFERTIG 
        ,CASE P.FERTIGJN WHEN 1 THEN 1
                         ELSE B.FERTIGJN
         END AS FERTIGJN 
        ,B.EIGANSCH ,B.PHASE ,B.SKONTOTAGE
        ,B.SKONTOSATZ ,B.DRKFFTITEL ,B.DRKINCMWST ,B.DRKAUFMASS ,B.DRKLM
        ,B.DRKZUBEHOE ,B.DRKDMRAB ,B.DRKRABATT ,B.DRKNULLWER ,B.AUFLOHN
        ,B.AUFMAT ,B.AUFFREMD ,B.MITTLOHN ,B.EXACT ,B.KGRPS ,B.KZZUBEHOER
        ,B.LOHNANTEIL ,B.KZVK2 ,B.TITEL ,B.MANUELL ,B.OZMASKE ,B.ANREDE
        ,B.VORNAME ,B.NAME1 ,B.NAME2 ,B.ZUHAENDEN ,B.STRASSE ,B.LAND
        ,B.PLZ ,B.ORT ,B.BRANREDE ,B.VORHABEN ,B.BETREFF1 ,B.BETREFF2
        ,B.BETREFF3 ,B.EDMENGEN ,B.EDARTNR ,B.EDLPOSNR ,B.DRKNETTO
        ,B.UEBERTRAG ,B.TITELZUS ,B.DRKTEXT ,B.DRKUPOS ,B.DRKUPOSPR 
        ,B.EDFAKTOR ,B.FAELLTAGE ,B.DRKARTNR ,B.DRKBRANRE ,B.UNSERZEI
        ,B.SICHBEHALT ,B.DRKVORTEXT ,B.DRKNACTEXT ,B.DRKTITSUMM ,B.JOURNAL
        ,B.ZUVERBUCH ,B.VERBUCHT ,B.NETTO ,B.MWST ,B.MWSTSATZ ,B.BRUTTO
        ,B.VERBUCHTDM ,B.SUMMEMAT ,B.SUMMELOHN ,B.SUMMEFREMD ,B.SUMMEZEIT
        ,B.SUMMEEKFR ,B.SUMMEEKM ,B.GEMEINK ,B.GEMEINKVH ,B.MAHNENJN ,B.MAHNSTUFE
        ,B.BEZAHLT ,B.ZULETZTBEZ ,B.ZAHLDATUM ,B.WAEHRUNG ,B.KURSZUEURO
        ,B.DRKBANK ,B.EGRUMAT ,B.EGRULOHN ,B.EGRUFREMD ,B.EGRUGEMEIN ,B.KST
        ,B.KTR ,B.DEBKONTO ,B.NERECHNEN ,B.NEAUSCHLAG ,B.NETRENNEN ,B.FREMDPROJ
        ,B.GAEBJN ,B.GAEBNAME ,B.GAEBPHASE ,B.NETTO1 ,B.NETTO2 ,B.UST1 ,B.UST2
        ,B.USTVH2 ,B.RES4NG2 ,B.NUMRECS
	,ISNULL((SELECT TOP 1 W.Eurokurs FROM dbo.Waehrungen W WHERE W.Kuerzel = B.WAEHRUNG), 1)  AS RechnungsEurokurs
	,ISNULL((SELECT TOP 1 W.Eurokurs FROM dbo.Waehrungen W WHERE W.Kuerzel = O.OptDataC), 1)  AS FirmenEurokurs
	,ISNULL((SELECT TOP 1 W.Eurokurs FROM dbo.Waehrungen W WHERE W.Kuerzel = 'DM'),       1)  AS DMEurokurs
	,ISNULL((SELECT TOP 1 W.Eurokurs FROM dbo.Waehrungen W WHERE W.Kuerzel = 'DEM'),      1)  AS DEMEurokurs
	,ISNULL((SELECT TOP 1 W.Eurokurs FROM dbo.Waehrungen W WHERE W.Kuerzel = 'EUR'),      1)  AS EurokursEurokurs
	,ISNULL( O.OptDataC, 'EUR' )    AS OptWaehrung
	,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.Belege B
LEFT  JOIN (SELECT P.PRJNR,P.FERTIGJN FROM dbo.Belege P WHERE P.BELEGART = ' 0') P
        ON B.PRJNR = P.PRJNR
LEFT  JOIN dbo.Optionen O
        ON O.OptId = ' 7008'
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE (B.JOURNAL = 1) AND
      ((@Art = '00') OR (REPLACE(B.BELEGART,' ','') = REPLACE(@Art,' ',''))) AND
      ((@Von = '0000.00.00') OR (ISDATE(B.TAG +'.'+ B.MONAT +'.'+ B.JAHR) = 1 AND 
                                        B.JAHR +'.'+ B.MONAT +'.'+ B.TAG  >= @Von)) AND
      ((@Bis = '0000.00.00') OR (ISDATE(B.TAG +'.'+ B.MONAT +'.'+ B.JAHR) = 1 AND 
                                        B.JAHR +'.'+ B.MONAT +'.'+ B.TAG  <= @Bis)) AND
      ((@VonKnd = 'NULL') OR (@VonKnd = '') OR (B.KDNR  >= @VonKnd)) AND
      ((@BisKnd = 'NULL') OR (@BisKnd = '') OR (B.KDNR  <= @BisKnd)) AND
      ((@VonPrj = 'NULL') OR (@VonPrj = '') OR (B.PRJNR >= @VonPrj)) AND
      ((@BisPrj = 'NULL') OR (@BisPrj = '') OR (B.PRJNR <= @BisPrj)) 





GO
CREATE PROCEDURE dbo.crp_EAKasse
			@mKonto char(8) = 'NULL'
AS
SELECT
         K.RecId, K.Jahr, K.Monat, K.Tag, K.TransNo, K.KtoSach, K.UstSchl
        ,K.Kst, K.Ktr, K.KzZA, K.Waehrung, K.BetrWaehr, K.BuText
	,ISNULL(W1.Eurokurs,1)     AS BWEurokurs
	,ISNULL(W.Eurokurs,1)      AS MWEurokurs
	,ISNULL(W.Kuerzel,'EUR')   AS Kuerzel     
	,ISNULL(W2.Eurokurs,1)     AS DMEurokurs
	,ISNULL(W3.Eurokurs,1)     AS DEMEurokurs
	,ISNULL(W4.Eurokurs,1)     AS EurokursEurokurs
	,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.Kassenbuch K
LEFT  JOIN dbo.Optionen O
        ON O.OptId = ' 7008'
LEFT  JOIN dbo.Waehrungen W
        ON W.Kuerzel = O.OptDataC
LEFT  JOIN dbo.Waehrungen W1
        ON W1.Kuerzel = K.Waehrung
LEFT  JOIN dbo.Waehrungen W2
        ON W2.Kuerzel = 'DM'
LEFT  JOIN dbo.Waehrungen W3
        ON W3.Kuerzel = 'DEM'
LEFT  JOIN dbo.Waehrungen W4
        ON W4.Kuerzel = 'EUR'
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE K.KtoKasse = @mKonto




GO
CREATE PROCEDURE dbo.crp_Kassenbuch
				@mKonto  varchar(8),
				@FltMon  varchar(2),
				@FltJahr varchar(4)
AS
DECLARE @SaldoVor  float
DECLARE @OffenVor  float
DECLARE @m_FltMon  varchar(2)
DECLARE @m_FltJahr varchar(4)

SET @m_FltMon  = '00'
SET @m_FltJahr = STR(YEAR(GETDATE()), 4)

IF (@FltMon IS NOT NULL) AND (@FltMon <> '') AND (@FltMon <> '0')
    SET @m_FltMon = RIGHT('00' + LTRIM(RTRIM(@FltMon)), 2)
IF (@FltJahr IS NOT NULL) AND (@FltJahr <> '') AND (@FltJahr <> 'NULL')
    SET @m_FltJahr = RIGHT('0000' + LTRIM(RTRIM(@FltJahr)), 2)

SET @SaldoVor = ISNULL((SELECT TOP 1 0-B.SaldoNeu FROM dbo.Buchungszeilen B 
                        WHERE (B.Konto = LTRIM( @mKonto )) AND 
                              ((RIGHT(B.Jahr,2) = RIGHT(@m_FltJahr,2) AND B.Monat < @m_FltMon) OR (RIGHT(B.Jahr,2) < RIGHT(@m_FltJahr,2))) 
                        ORDER BY B.Jahr DESC,B.Monat DESC,B.Tag DESC,B.TransNo DESC), 0) 

SET @OffenVor = ISNULL((SELECT SUM(CASE WHEN K.KzZA = 'A' THEN 0-K.BetrEuro ELSE K.BetrEuro END) FROM dbo.Kassenbuch K
                        WHERE (K.KtoKasse = LTRIM( @mKonto )) AND (K.TransNo = '') AND
                              ((RIGHT(K.Jahr,2) = RIGHT(@m_FltJahr,2) AND K.Monat < @m_FltMon) OR (RIGHT(K.Jahr,2) < RIGHT(@m_FltJahr,2)))), 0) 

SELECT
         1                         AS MandId
        ,0                         AS RecId
        ,@SaldoVor                 AS SaldoVor
        ,@OffenVor                 AS OffenVor
        ,''                        AS Jahr
        ,''                        AS Monat
        ,''                        AS Tag
        ,''                        AS TransNo
        ,0                         AS Soll
        ,0                         AS Haben
        ,0                         AS Betrag
        ,0                         AS SaldoAlt
        ,0                         AS SaldoNeu
        ,''                        AS BuText
        ,''                        AS Kst
        ,''                        AS Ktr
        ,'Header'                  AS KtoKasse
        ,''                        AS Typ
        ,''                        AS PrjNr
        ,''                        AS BelNr
        ,0                         AS Verbucht
	,0                         AS MWEurokurs
	,''                        AS Kuerzel     
	,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.Anschriften AN
WHERE AN.Nr = '01'
UNION
SELECT
         1			                AS MandId
        ,K.RecId                    AS RecId
        ,@SaldoVor                  AS SaldoVor
        ,@OffenVor                  AS OffenVor
        ,K.Jahr                     AS Jahr
        ,K.Monat                    AS Monat
        ,K.Tag                      AS Tag
        ,K.TransNo                  AS TransNo
        ,K.Soll                     AS Soll
        ,K.Haben                    AS Haben
        ,K.Soll-K.Haben             AS Betrag
        ,K.SaldoAlt                 AS SaldoAlt
        ,K.SaldoNeu                 AS SaldoNeu
        ,K.[Text]                   AS BuText
        ,K.Kst                      AS Kst
        ,K.Ktr                      AS Ktr
        ,K.Konto                    AS KtoKasse
        ,K.Typ                      AS Typ
        ,K.PrjNr                    AS PrjNr
        ,K.BelNr                    AS BelNr
        ,1                          AS Verbucht
	,ISNULL(W.Eurokurs,1)       AS MWEurokurs
	,ISNULL(W.Kuerzel,'EUR')    AS Kuerzel     
	,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.Buchungszeilen K
LEFT  JOIN dbo.Optionen O
        ON O.OptId = ' 7008'
LEFT  JOIN dbo.Waehrungen W
        ON W.Kuerzel = O.OptDataC
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE ( K.Konto = LTRIM( @mKonto ) ) AND
      ( @m_FltMon = '00' OR @m_FltMon = K.Monat ) AND
      ( RIGHT(@m_FltJahr,2) = RIGHT(K.Jahr,2) ) 
UNION
SELECT
         1			                AS MandId
        ,K.RecId                    AS RecId
        ,@SaldoVor                  AS SaldoVor
        ,@OffenVor                  AS OffenVor
        ,K.Jahr                     AS Jahr
        ,K.Monat                    AS Monat
        ,K.Tag                      AS Tag
        ,K.TransNo                  AS TransNo
        ,0                          AS Soll
        ,0                          AS Haben
        ,CASE WHEN K.KzZA = 'A' THEN 0 - ( K.BetrEuro * W.Eurokurs )  
              WHEN K.KzZA = 'Z' THEN K.BetrEuro * W.Eurokurs
              ELSE 0 END            AS Betrag
        ,0                          AS SaldoAlt
        ,0                          AS SaldoNeu
        ,K.BuText                   AS BuText
        ,K.Kst                      AS Kst
        ,K.Ktr                      AS Ktr
        ,K.KtoKasse                 AS KtoKasse
        ,'0'                        AS Typ
        ,'0'                        AS PrjNr
        ,K.BelegNr                  AS BelNr
        ,0                          AS Verbucht
	,ISNULL(W.Eurokurs,1)       AS MWEurokurs
	,ISNULL(W.Kuerzel,'EUR')    AS Kuerzel     
	,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.Kassenbuch K
LEFT  JOIN dbo.Optionen O
        ON O.OptId = ' 7008'
LEFT  JOIN dbo.Waehrungen W
        ON W.Kuerzel = O.OptDataC
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE ( K.KtoKasse = LTRIM(@mKonto) ) AND ( K.TransNo = '' OR K.TransNo IS NULL ) AND
      ( @m_FltMon = '00' OR @m_FltMon = K.Monat ) AND
      ( RIGHT(@m_FltJahr,2) = RIGHT(K.Jahr,2) ) 



GO
CREATE VIEW dbo.crv_KontoTransNo
AS
SELECT
    TOP 100 PERCENT 
    KP.Konto
   ,KP.Typ
   ,BZ.TransNo
   ,BZ.RecId
FROM dbo.Kontenplan KP
INNER JOIN dbo.Buchungszeilen BZ
        ON KP.Konto = BZ.Konto AND KP.Typ = BZ.Typ
ORDER BY KP.Konto, BZ.TransNo, BZ.RecId



GO
CREATE PROCEDURE dbo.crp_Kontoblatt
                            @VonKto  varchar(8)
                           ,@BisKto  varchar(8)
                           ,@FltTyp  varchar(1)
                           ,@FltMon  varchar(2)
                           ,@FltJahr varchar(4)
                           ,@Projekt varchar(8)
AS
DECLARE @m_FltMon  INT
DECLARE @m_FltJahr INT
SET @m_FltMon  = 0
SET @m_FltJahr = 0

DECLARE @m_VonKto INT
DECLARE @m_BisKto INT
SET @m_VonKto = 0
SET @m_BisKto = 0

IF NOT @FltMon  IS NULL AND @FltMon  <> '' AND @FltMon  <> '0'
    SET @m_FltMon  = 1
IF NOT @FltJahr IS NULL AND @FltJahr <> '' AND @FltJahr <> '0'
    SET @m_FltJahr = 1

SET @FltMon  = RIGHT('0' + @FltMon  ,2)
SET @FltJahr = RIGHT('0' + @FltJahr ,2)

IF NOT @VonKto IS NULL AND @VonKto <> '' AND @VonKto <> '0' AND @VonKto <> 'NULL'
    SET @m_VonKto  = 1
IF NOT @BisKto IS NULL AND @BisKto <> '' AND @BisKto <> '0' AND @BisKto <> 'NULL'
    SET @m_BisKto  = 1

DECLARE @m_FltTyp varchar(1)
IF NOT @FltTyp IS NULL AND @FltTyp <> '' AND @FltTyp <> '0'
    SET @m_FltTyp = @FltTyp
ELSE
    SET @m_FltTyp = 'S'

DECLARE @m_Projekt varchar(8)
IF @Projekt IS NOT NULL AND @Projekt <> 'NULL' AND LEN( @Projekt ) > 0 
    SET @m_Projekt = @Projekt
ELSE
    SET @m_Projekt = 'NULL'

SELECT
	TOP 100 PERCENT 
         K.RecId ,1 AS MandId ,K.Typ ,K.Konto ,K.Jahr ,K.Monat ,K.Tag
        ,K.TransNo ,K.Soll ,K.Haben ,K.SaldoAlt ,K.SaldoNeu ,K.BtrOrg
        ,K.BtrWae ,K.Saldiert ,K.Abgeholt ,K.[Text] ,K.SaldoUms ,K.Kst
        ,K.Ktr ,K.PrjNr ,K.BelNr ,K.FJahr ,K.FMonat ,K.FTag ,K.AusglTRN
        ,K.AusglBtr ,K.SkoFrist ,K.SkoSatz
	,ISNULL( KO.[Name]   ,'' )  AS Konto_Name
	,ISNULL( Bu.[Text]   ,'' )  AS Konto_Text
	,ISNULL( KT.[Typ]    ,'' )  AS Konto_Typ
	,ISNULL( KT.Konto    ,'' )  AS Konto_Konto
	,ISNULL( KT.TransNo  ,'' )  AS Konto_TransNo
	,ISNULL( KT.RecId    ,'' )  AS Konto_RecId
	,ISNULL( Bu.Soll     ,0  )  AS Konto_Soll
	,ISNULL( Bu.Haben    ,0  )  AS Konto_Haben
	,ISNULL( Bu.BtrOrg   ,0  )  AS Konto_Btr
	,ISNULL( Bu.BtrWae   ,'' )  AS Konto_Wae
	,ISNULL( Bu.Tag      ,'' )  AS Konto_Tag
	,ISNULL( Bu.Monat    ,'' )  AS Konto_Monat
	,ISNULL( Bu.Jahr     ,'' )  AS Konto_Jahr
	,ISNULL( Bu.SaldoAlt ,0  )  AS Konto_SaldoAlt
	,ISNULL( Bu.SaldoNeu ,0  )  AS Konto_SaldoNeu
	,ISNULL( Bu.BelDatum ,'' )  AS Konto_BelDatum
	,ISNULL( Bu.PrjNr    ,'' )  AS Konto_PrjNr
	,ISNULL( Bu.BelNr    ,'' )  AS Konto_BelNr
	,ISNULL( O.OptDataC, 'EUR') AS Waehrung
	,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.crv_KontoTransNo KT
INNER JOIN dbo.Buchungszeilen Bu
        ON KT.RecId = Bu.RecId
INNER JOIN dbo.Buchungszeilen K
        ON KT.TransNo = K.TransNo
LEFT  JOIN dbo.Kontenplan KO
        ON KO.Konto = KT.Konto AND KO.Typ = KT.Typ 
LEFT  JOIN dbo.Optionen O
        ON O.OptId = ' 7008'
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE ( @m_VonKto  = 0 OR @VonKto <= KT.Konto )           AND
      ( @m_BisKto  = 0 OR @BisKto >= KT.Konto )           AND
      ( UPPER(@m_FltTyp) = UPPER(KT.Typ) )                AND
      ( @m_FltMon  = 0 OR @FltMon  = Bu.Monat )           AND
      ( @m_FltJahr = 0 OR @FltJahr = RIGHT(Bu.Jahr,2)  )  AND
      ( @m_Projekt = 'NULL' OR @m_Projekt = Bu.PrjNr)
ORDER BY KT.Konto, K.Jahr, K.Monat, K.Tag, KT.RecId, K.RecId 




GO
CREATE PROCEDURE dbo.crp_BuchProjekt
                            @Projekt  varchar(8)
AS
DECLARE @p_Projekt varchar(8)
SET @p_Projekt = 'NULL'

IF @Projekt IS NOT NULL AND @Projekt <> 'NULL' AND LEN( @Projekt ) > 0 
    SET @p_Projekt = @Projekt

SELECT
	TOP 100 PERCENT 
         K.RecId ,1 AS MandId ,K.Typ ,K.Konto ,K.Jahr ,K.Monat ,K.Tag
        ,K.TransNo ,K.Soll ,K.Haben ,K.SaldoAlt ,K.SaldoNeu ,K.BtrOrg
        ,K.BtrWae ,K.Saldiert ,K.Abgeholt ,K.[Text] ,K.SaldoUms ,K.Kst
        ,K.Ktr ,K.PrjNr ,K.BelNr ,K.FJahr ,K.FMonat ,K.FTag ,K.AusglTRN
        ,K.AusglBtr ,K.SkoFrist ,K.SkoSatz
	,ISNULL( KO.[Name]   ,'' )  AS Konto_Name
	,ISNULL( Bu.[Text]   ,'' )  AS Konto_Text
	,ISNULL( KT.[Typ]    ,'' )  AS Konto_Typ
	,ISNULL( KT.Konto    ,'' )  AS Konto_Konto
	,ISNULL( KT.TransNo  ,'' )  AS Konto_TransNo
	,ISNULL( KT.RecId    ,'' )  AS Konto_RecId
	,ISNULL( Bu.Soll     ,0  )  AS Konto_Soll
	,ISNULL( Bu.Haben    ,0  )  AS Konto_Haben
	,ISNULL( Bu.BtrOrg   ,0  )  AS Konto_Btr
	,ISNULL( Bu.BtrWae   ,'' )  AS Konto_Wae
	,ISNULL( Bu.Tag      ,'' )  AS Konto_Tag
	,ISNULL( Bu.Monat    ,'' )  AS Konto_Monat
	,ISNULL( Bu.Jahr     ,'' )  AS Konto_Jahr
	,ISNULL( Bu.SaldoAlt ,0  )  AS Konto_SaldoAlt
	,ISNULL( Bu.SaldoNeu ,0  )  AS Konto_SaldoNeu
	,ISNULL( O.OptDataC, 'EUR') AS Waehrung
	,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.crv_KontoTransNo KT
INNER JOIN dbo.Buchungszeilen K
        ON KT.TransNo = K.TransNo
INNER JOIN dbo.Buchungszeilen Bu
        ON KT.RecId = Bu.RecId
LEFT  JOIN dbo.Kontenplan KO
        ON KO.Konto = KT.Konto AND KO.Typ = KT.Typ 
LEFT  JOIN dbo.Optionen O
        ON O.OptId = ' 7008'
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE ( @p_Projekt = 'NULL' OR @p_Projekt = Bu.PrjNr )
ORDER BY KT.Konto, K.Jahr, K.Monat, K.Tag, KT.RecId, K.RecId 




GO
CREATE PROCEDURE dbo.crp_Buchblatt
                            @FltKto  varchar(8)
                           ,@FltTyp  varchar(1)
AS
SELECT
	TOP 100 PERCENT 
         BU.RecId ,1 AS MandId ,BU.Typ ,BU.Konto ,BU.Jahr ,BU.Monat ,BU.Tag
        ,BU.TransNo ,BU.Soll ,BU.Haben ,BU.SaldoAlt ,BU.SaldoNeu ,BU.BtrOrg
        ,BU.BtrWae ,BU.Saldiert ,BU.Abgeholt ,BU.[Text] ,BU.SaldoUms ,BU.Kst
        ,BU.Ktr ,BU.PrjNr ,BU.BelNr ,BU.FJahr ,BU.FMonat ,BU.FTag ,BU.AusglTRN
        ,BU.AusglBtr ,BU.SkoFrist ,BU.SkoSatz
	,ISNULL( K_HDR.[Name],'' )   AS HDR_Name
	,ISNULL( K_HDR.Typ   ,'' )   AS HDR_Typ
	,ISNULL( K_HDR.Konto ,'' )   AS HDR_Konto
	,ISNULL( K_TRN.[Name]  ,'' ) AS TRN_Name
	,ISNULL( K_TRN.KzKasse ,'' ) AS TRN_KzKasse
	,ISNULL( O.OptDataC  ,'EUR') AS Waehrung     
        ,ST.PosTyp                   AS PosTyp
	,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.BuchblattZeilen BZ
INNER JOIN dbo.Buchungszeilen BU
        ON BU.TransNo = BZ.TransNo
INNER JOIN (SELECT 'A' AS PosTyp UNION SELECT 'B' AS PosTyp) ST
        ON ST.PosTyp = 'A' OR ST.PosTyp = 'B'
LEFT  JOIN dbo.Kontenplan K_HDR
        ON K_HDR.Konto = BZ.BBKtoNr AND K_HDR.Typ = BZ.BBKtoTyp
LEFT  JOIN dbo.Kontenplan K_TRN
        ON K_TRN.Konto = BU.Konto AND K_TRN.Typ = BU.Typ 
LEFT  JOIN dbo.Optionen O
        ON O.OptId = ' 7008'
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE ( @FltKto = BZ.BBKtoNr ) AND ( UPPER(@FltTyp) = UPPER(BZ.BBKtoTyp) ) AND 
      ( BZ.State = ' ' ) AND ( BZ.BBKtoTyp <> 'F' ) AND ( BU.Soll <> 0 OR BU.Haben <> 0 )
ORDER BY BZ.Jahr,BZ.Monat,BZ.Tag, BZ.TransNo, ST.PosTyp, BU.RecId 




GO
CREATE PROCEDURE dbo.crp_FreieStapel
AS
SELECT
	TOP 100 PERCENT 
         BU.RecId ,1 AS MandId ,BU.Typ ,BU.Konto ,BU.Jahr ,BU.Monat ,BU.Tag
        ,BU.TransNo ,BU.Soll ,BU.Haben ,BU.SaldoAlt ,BU.SaldoNeu ,BU.BtrOrg
        ,BU.BtrWae ,BU.Saldiert ,BU.Abgeholt ,BU.[Text] ,BU.SaldoUms ,BU.Kst
        ,BU.Ktr ,BU.PrjNr ,BU.BelNr ,BU.FJahr ,BU.FMonat ,BU.FTag ,BU.AusglTRN
        ,BU.AusglBtr ,BU.SkoFrist ,BU.SkoSatz
	,ISNULL( K.[Name]  ,'' )     AS TRN_Name
	,ISNULL( K.KzKasse ,'' )     AS TRN_KzKasse
	,ISNULL( O.OptDataC  ,'EUR') AS Waehrung     
	,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.BuchblattZeilen BZ
INNER JOIN dbo.Buchungszeilen BU
        ON BU.TransNo = BZ.TransNo
LEFT  JOIN dbo.Kontenplan K
        ON K.Konto = BU.Konto AND K.Typ = BU.Typ 
LEFT  JOIN dbo.Optionen O
        ON O.OptId = ' 7008'
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE ( BZ.State = ' ' ) AND ( BZ.BBKtoTyp = 'F' ) AND ( BU.Soll <> 0 OR BU.Haben <> 0 )
ORDER BY BZ.Jahr,BZ.Monat,BZ.Tag, BZ.TransNo, BU.RecId 



GO
CREATE PROCEDURE dbo.crp_Primanota
                            @VonDatum   varchar(10)
                           ,@BisDatum   varchar(10)
                           ,@VonBDatum  varchar(10)
                           ,@BisBDatum  varchar(10)
                           ,@Projekt    varchar(8)
AS
DECLARE @p_VonDatum  varchar(10) 
DECLARE @p_BisDatum  varchar(10) 
DECLARE @p_VonBDatum varchar(10) 
DECLARE @p_BisBDatum varchar(10) 
DECLARE @p_Projekt   varchar(8) 
SET @p_VonDatum  = 'NULL'
SET @p_BisDatum  = 'NULL'
SET @p_VonBDatum = 'NULL'
SET @p_BisBDatum = 'NULL'
SET @p_Projekt   = 'NULL'

IF ISDATE( @VonDatum ) = 1
    SET @p_VonDatum = RIGHT(@VonDatum, 4) + SUBSTRING(@VonDatum,4,2) + LEFT(@VonDatum,2)
IF ISDATE( @BisDatum ) = 1 
    SET @p_BisDatum = RIGHT(@BisDatum, 4) + SUBSTRING(@BisDatum,4,2) + LEFT(@BisDatum,2)

IF ISDATE( @VonBDatum ) = 1
    SET @p_VonBDatum = RIGHT(@VonBDatum, 4) +'.'+ SUBSTRING(@VonBDatum,4,2) +'.'+ LEFT(@VonBDatum,2)
IF ISDATE( @BisBDatum ) = 1 
    SET @p_BisBDatum = RIGHT(@BisBDatum, 4) +'.'+ SUBSTRING(@BisBDatum,4,2) +'.'+ LEFT(@BisBDatum,2)

IF @Projekt IS NOT NULL AND @Projekt <> 'NULL' AND LEN( @Projekt ) > 0 
    SET @p_Projekt = @Projekt

SELECT
	TOP 100 PERCENT 
         BU.RecId ,1 AS MandId ,BU.Typ ,BU.Konto ,BU.Jahr ,BU.Monat ,BU.Tag
        ,BU.TransNo ,BU.Soll ,BU.Haben ,BU.SaldoAlt ,BU.SaldoNeu ,BU.BtrOrg
        ,BU.BtrWae ,BU.Saldiert ,BU.Abgeholt ,BU.[Text] ,BU.SaldoUms ,BU.Kst
        ,BU.Ktr ,BU.PrjNr ,BU.BelNr ,BU.FJahr ,BU.FMonat ,BU.FTag ,BU.AusglTRN
        ,BU.AusglBtr ,BU.SkoFrist ,BU.SkoSatz, BU.BelDatum
	,ISNULL( K.[Name]  ,'' )     AS TRN_Name
	,ISNULL( K.KzKasse ,'' )     AS TRN_KzKasse
	,ISNULL( O.OptDataC  ,'EUR') AS Waehrung   
	,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.Buchungszeilen BU
LEFT  JOIN dbo.Kontenplan K
        ON K.Konto = BU.Konto AND K.Typ = BU.Typ 
LEFT  JOIN dbo.Optionen O
        ON O.OptId = ' 7008'
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE ( BU.TransNo IS NOT NULL ) AND ( LEN(BU.TransNo) > 0 ) AND 
      ( BU.Soll <> 0 OR BU.Haben <> 0 ) AND
      ((@p_Projekt   = 'NULL') OR (@p_Projekt IN (SELECT PrjNr FROM dbo.Buchungszeilen WHERE TransNo = BU.TransNo))) AND
      ((@p_VonDatum  = 'NULL') OR (BU.Jahr + BU.Monat + BU.Tag >= @p_VonDatum)) AND
      ((@p_BisDatum  = 'NULL') OR (BU.Jahr + BU.Monat + BU.Tag <= @p_BisDatum)) AND
      ((@p_VonBDatum = 'NULL') OR (CONVERT(varchar(10), BU.BelDatum, 102) >= @p_VonBDatum)) AND
      ((@p_BisBDatum = 'NULL') OR (CONVERT(varchar(10), BU.BelDatum, 102) <= @p_BisBDatum)) 
ORDER BY BU.TransNo, BU.RecId 




GO
CREATE VIEW dbo.crv_Jumboliste
AS
SELECT 
	 S.PktNr
	,S.LfdNr
	,ISNULL(LEFT( S.Txt1, LEN(S.Txt1) ) +' '+
	        LEFT( S.Txt2, LEN(S.Txt2) ) +' '+
	        LEFT( S.Txt3, LEN(S.Txt3) ),'') As Txt
	,1 AS MandId
	,S.PktNr                    AS TeilPktNr
	,S.LfdNr                    AS TeilLfdNr
	,S.Aufwand                  AS Aufwand
	,M1.[Name]                  AS Me
	,ISNULL(LEFT( SP.Bez1,    LEN(SP.Bez1) ) +' '+
	        LEFT( SP.Bez2,    LEN(SP.Bez2) ) +' '+
	        LEFT( SP.Merkmal, LEN(SP.Merkmal) ),'') As [Text]
	,SP.Lkz                     AS StammLkz
	,SP.Artnr                   AS StammArtNr
	,SP.Einheit                 AS StammEinheit
	,SP.Kglohn                  AS Kglohn
	,SP.Kgmat                   AS Kgmat
	,SP.Pe                      AS Pe
	,SP.Gebinde                 AS Gebinde
	,SP.Kgmat                   AS VhMatNr
	,SP.Kglohn                  AS VhLohnNr
	,SP.Zeit                    AS Zeit
	,SP.Zubehoerdm              AS Zubehoer
	,SP.Ekmat                   AS Ekmat
	,M2.[Name]                  AS StammMe
	,P.Faktor * SP.Gebinde      AS PreisFaktor
	,ISNULL( 
                (SELECT TOP 1
                     EKP.NePreisJe1
                 FROM dbo.crv_ArtikelEkPreise EKP
                 WHERE EKP.Artnr = SP.Artnr And EKP.Lkz = SP.Lkz
                ) 
               , 0) / 100           AS NePreis
	,O.OptDataD                 AS OptDataD
	,K1.VhMat                   AS VhMat
	,K2.VhLohn                  AS VhLohn
	,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.StammPakete S 
LEFT  JOIN dbo.Stammpositionen SP 
        ON S.ArtLkz = SP.Lkz And S.ArtNr = SP.Artnr
LEFT  JOIN dbo.Mengeneinheiten M1
        ON S.Einheit = M1.Nr
LEFT  JOIN dbo.Mengeneinheiten M2
        ON SP.Einheit = M2.Nr
LEFT  JOIN dbo.Preiseinheiten P
        ON P.Nr = SP.Pe
INNER JOIN dbo.Optionen O
        ON O.OptId = ' 7006'
LEFT  JOIN dbo.Kalkulationsgruppen K1
        ON K1.Nr = SP.Kgmat
LEFT  JOIN dbo.Kalkulationsgruppen K2
        ON K2.Nr = SP.Kglohn
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'




GO
CREATE PROCEDURE dbo.crp_Zusammenstellung
				@FltKey char(18) = 'NULL'
AS
DECLARE @prjnr  char(8)
DECLARE @belart char(2)
DECLARE @belnr  char(8)
SET @prjnr  = 'NULL'
SET @belart = 'NULL'
SET @belnr  = 'NULL'
IF LEN(@FltKey) > 0 AND @FltKey <> 'NULL' 
BEGIN
  SET @prjnr  = LEFT(@FltKey,8)
  SET @belart = SUBSTRING(@FltKey,9,2)
  SET @belnr  = RIGHT(@FltKey,8)
END
SELECT
         B.PrjNr, B.BelArt, B.BelNr, B.LfdNr, B.SatzArt, B.Ktxt1, B.Menge, B.VkMat, B.VkLohn, B.VkFremd
        ,B.EkMat, B.EkLohn, B.EkFremd, B.RabattDM, B.AbsMenge, B.EPPos, B.GPPos
	,BEL.PRJNR                   AS BelPRJNR
	,BEL.FAELLTAGE ,BEL.BELARTTEXT ,BEL.WAEHRUNG ,BEL.KDNR ,BEL.BELEGART ,BEL.TAG
	,BEL.MONAT ,BEL.JAHR ,BEL.SUMMEFREMD ,BEL.MWSTSATZ ,BEL.EIGANSCH
	,BAK.DestDebNr               AS KNr
	,BAK.Anrede                  AS KAnrede
	,BAK.Name1                   AS KName1
	,BAK.Name2                   AS KName2
	,BAK.Strasse                 AS KStrasse
	,LEFT(BAK.Plz,5)+' '+BAK.Ort AS KPlzOrt
	,BAB.DestDebNr               AS BNr
	,BAB.Anrede                  AS BAnrede
	,BAB.Name1                   AS BName1
	,BAB.Name2                   AS BName2
	,BAB.Strasse                 AS BStrasse
	,LEFT(BAB.Plz,5)+' '+BAB.Ort AS BPlzOrt
	,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.Belege BEL
INNER JOIN dbo.BelPos B
        ON B.PrjNr = BEL.PRJNR And B.BelArt = BEL.BELEGART And B.BelNr = BEL.BELEGNR
LEFT  JOIN dbo.BelKopfExt BKE
        ON B.PrjNr = BKE.PrjNr And B.BelArt = BKE.BelArt And B.BelNr = BKE.BelNr
LEFT  JOIN dbo.BelAdresse BAK
        ON B.PrjNr = BAK.PrjNr And B.BelArt = BAK.BelArt And B.BelNr = BAK.BelNr And BAK.LfdNr = '0'
LEFT  JOIN dbo.BelAdresse BAB
        ON B.PrjNr = BAB.PrjNr And B.BelArt = BAB.BelArt And B.BelNr = BAB.BelNr And BAB.LfdNr = '1'
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE (B.SatzArt = 1 Or B.SatzArt > 11) And
      BEL.PRJNR = @prjnr And BEL.BELEGART = @belart And BEL.BELEGNR = @belnr



GO
CREATE PROCEDURE dbo.crp_Kalkulationen
				@FltKey char(18) = 'NULL'
AS
  DECLARE @p_prjnr  char(8)
  DECLARE @p_belart char(2)
  DECLARE @p_belnr  char(8)
  SET @p_prjnr  = 'NULL'
  SET @p_belart = 'NULL'
  SET @p_belnr  = 'NULL'
  IF LEN(@FltKey) > 0 AND @FltKey <> 'NULL' 
  BEGIN
    SET @p_prjnr  = LEFT(@FltKey,8)
    SET @p_belart = SUBSTRING(@FltKey,9,2)
    SET @p_belnr  = RIGHT(@FltKey,8)
  END

  DECLARE @BelNr     varchar(8)
  DECLARE @BelArt    varchar(2)
  DECLARE @PrjNr     varchar(8)
  DECLARE @Ebene     int
  DECLARE @LastEbene int
  DECLARE @SatzArt   int
  DECLARE @LfdNr     varchar(6)
  DECLARE @Cnt       int
  
  DECLARE @E1_Lfd  varchar(6)
  DECLARE @E2_Lfd  varchar(6)
  DECLARE @E3_Lfd  varchar(6)
  DECLARE @E4_Lfd  varchar(6)
  DECLARE @E5_Lfd  varchar(6)
  DECLARE @E6_Lfd  varchar(6)
  DECLARE @E7_Lfd  varchar(6)
  DECLARE @E8_Lfd  varchar(6)
  DECLARE @E9_Lfd  varchar(6)
  SET @E1_Lfd = ''
  SET @E2_Lfd = ''
  SET @E3_Lfd = ''
  SET @E4_Lfd = ''
  SET @E5_Lfd = ''
  SET @E6_Lfd = ''
  SET @E7_Lfd = ''
  SET @E8_Lfd = ''
  SET @E9_Lfd = ''

  SET @Cnt = 1
  CREATE TABLE dbo.#Tmp (PrjNr char(8), BelNr char(8), BelArt char(2), 
                         Ebene int, SatzArt int, LfdNr char(6), Cnt int  )
  IF SUBSTRING(@@VERSION,22,1) = '2' OR SUBSTRING(@@VERSION,23,1) = '2'
  BEGIN
    EXECUTE('
             ALTER TABLE dbo.#Tmp ALTER COLUMN PrjNr  char(8) COLLATE database_default 
             ALTER TABLE dbo.#Tmp ALTER COLUMN BelNr  char(8) COLLATE database_default 
             ALTER TABLE dbo.#Tmp ALTER COLUMN BelArt char(2) COLLATE database_default 
             ALTER TABLE dbo.#Tmp ALTER COLUMN LfdNr  char(6) COLLATE database_default
           ')
  END

  DECLARE KC CURSOR FOR 
	SELECT B.PrjNr, B.BelArt, B.BelNr, B.Ebene, B.SatzArt, B.LfdNr 
        FROM dbo.Belege BEL
        INNER JOIN dbo.BelPos B
                ON B.PrjNr = BEL.PRJNR AND B.BelArt = BEL.BELEGART AND B.BelNr = BEL.BELEGNR
        LEFT  JOIN dbo.Preiseinheiten PE
                ON PE.Nr = B.Pe
        WHERE BEL.PRJNR = @p_prjnr And BEL.BELEGART = @p_belart And BEL.BELEGNR = @p_belnr
        ORDER BY B.LfdNr
  OPEN KC

  FETCH NEXT FROM KC
  INTO @PrjNr, @BelArt, @BelNr, @Ebene, @SatzArt, @LfdNr
  WHILE @@FETCH_STATUS = 0
  BEGIN
     IF @Ebene < @LastEbene
     BEGIN
        IF @Ebene < 10 AND @E9_Lfd <> ''
        BEGIN 
            INSERT INTO dbo.#Tmp( PrjNr, BelNr, BelArt, Ebene, SatzArt, LfdNr, Cnt )
            SELECT TOP 1 BP.PrjNr, BP.BelNr, BP.BelArt, BP.Ebene, '19', BP.LfdNr, @Cnt
            FROM dbo.BelPos BP
            WHERE BP.PrjNr = @PrjNr AND BP.BelArt = @BelArt AND BP.BelNr = @BelNr AND BP.LfdNr = @E9_Lfd
            SET @Cnt = @Cnt + 1
            SET @E9_Lfd = '' 
        END
        IF @Ebene < 9 AND @E8_Lfd <> ''
        BEGIN 
            INSERT INTO dbo.#Tmp( PrjNr, BelNr, BelArt, Ebene, SatzArt, LfdNr, Cnt )
            SELECT TOP 1 BP.PrjNr, BP.BelNr, BP.BelArt, BP.Ebene, '19', BP.LfdNr, @Cnt
            FROM dbo.BelPos BP
            WHERE BP.PrjNr = @PrjNr AND BP.BelArt = @BelArt AND BP.BelNr = @BelNr AND BP.LfdNr = @E8_Lfd
            SET @Cnt = @Cnt + 1
            SET @E8_Lfd = '' 
        END
        IF @Ebene < 8 AND @E7_Lfd <> ''
        BEGIN 
            INSERT INTO dbo.#Tmp( PrjNr, BelNr, BelArt, Ebene, SatzArt, LfdNr, Cnt )
            SELECT TOP 1 BP.PrjNr, BP.BelNr, BP.BelArt, BP.Ebene, '19', BP.LfdNr, @Cnt
            FROM dbo.BelPos BP
            WHERE BP.PrjNr = @PrjNr AND BP.BelArt = @BelArt AND BP.BelNr = @BelNr AND BP.LfdNr = @E7_Lfd
            SET @Cnt = @Cnt + 1
            SET @E7_Lfd = '' 
        END
        IF @Ebene < 7 AND @E6_Lfd <> ''
        BEGIN 
            INSERT INTO dbo.#Tmp( PrjNr, BelNr, BelArt, Ebene, SatzArt, LfdNr, Cnt )
            SELECT TOP 1 BP.PrjNr, BP.BelNr, BP.BelArt, BP.Ebene, '19', BP.LfdNr, @Cnt
            FROM dbo.BelPos BP
            WHERE BP.PrjNr = @PrjNr AND BP.BelArt = @BelArt AND BP.BelNr = @BelNr AND BP.LfdNr = @E6_Lfd
            SET @Cnt = @Cnt + 1
            SET @E6_Lfd = '' 
        END
        IF @Ebene < 6 AND @E5_Lfd <> ''
        BEGIN 
            INSERT INTO dbo.#Tmp( PrjNr, BelNr, BelArt, Ebene, SatzArt, LfdNr, Cnt )
            SELECT TOP 1 BP.PrjNr, BP.BelNr, BP.BelArt, BP.Ebene, '19', BP.LfdNr, @Cnt
            FROM dbo.BelPos BP
            WHERE BP.PrjNr = @PrjNr AND BP.BelArt = @BelArt AND BP.BelNr = @BelNr AND BP.LfdNr = @E5_Lfd
            SET @Cnt = @Cnt + 1
            SET @E5_Lfd = '' 
        END
        IF @Ebene < 5 AND @E4_Lfd <> ''
        BEGIN 
            INSERT INTO dbo.#Tmp( PrjNr, BelNr, BelArt, Ebene, SatzArt, LfdNr, Cnt )
            SELECT TOP 1 BP.PrjNr, BP.BelNr, BP.BelArt, BP.Ebene, '19', BP.LfdNr, @Cnt
            FROM dbo.BelPos BP
            WHERE BP.PrjNr = @PrjNr AND BP.BelArt = @BelArt AND BP.BelNr = @BelNr AND BP.LfdNr = @E4_Lfd
            SET @Cnt = @Cnt + 1
            SET @E4_Lfd = '' 
        END
        IF @Ebene < 4 AND @E3_Lfd <> ''
        BEGIN 
            INSERT INTO dbo.#Tmp( PrjNr, BelNr, BelArt, Ebene, SatzArt, LfdNr, Cnt )
            SELECT TOP 1 BP.PrjNr, BP.BelNr, BP.BelArt, BP.Ebene, '19', BP.LfdNr, @Cnt
            FROM dbo.BelPos BP
            WHERE BP.PrjNr = @PrjNr AND BP.BelArt = @BelArt AND BP.BelNr = @BelNr AND BP.LfdNr = @E3_Lfd
            SET @Cnt = @Cnt + 1
            SET @E3_Lfd = '' 
        END
        IF @Ebene < 3 AND @E2_Lfd <> ''
        BEGIN 
            INSERT INTO dbo.#Tmp( PrjNr, BelNr, BelArt, Ebene, SatzArt, LfdNr, Cnt )
            SELECT TOP 1 BP.PrjNr, BP.BelNr, BP.BelArt, BP.Ebene, '19', BP.LfdNr, @Cnt
            FROM dbo.BelPos BP
            WHERE BP.PrjNr = @PrjNr AND BP.BelArt = @BelArt AND BP.BelNr = @BelNr AND BP.LfdNr = @E2_Lfd
            SET @Cnt = @Cnt + 1
            SET @E2_Lfd = '' 
        END
        IF @Ebene < 2 AND @E1_Lfd <> ''
        BEGIN 
            INSERT INTO dbo.#Tmp( PrjNr, BelNr, BelArt, Ebene, SatzArt, LfdNr, Cnt )
            SELECT TOP 1 BP.PrjNr, BP.BelNr, BP.BelArt, BP.Ebene, '19', BP.LfdNr, @Cnt
            FROM dbo.BelPos BP
            WHERE BP.PrjNr = @PrjNr AND BP.BelArt = @BelArt AND BP.BelNr = @BelNr AND BP.LfdNr = @E1_Lfd
            SET @Cnt = @Cnt + 1
            SET @E1_Lfd = '' 
        END
     END

     IF @SatzArt = 4
     BEGIN
         IF @Ebene = 1 SET @E1_Lfd = @LfdNr
         IF @Ebene = 2 SET @E2_Lfd = @LfdNr
         IF @Ebene = 3 SET @E3_Lfd = @LfdNr
         IF @Ebene = 4 SET @E4_Lfd = @LfdNr
         IF @Ebene = 5 SET @E5_Lfd = @LfdNr
         IF @Ebene = 6 SET @E6_Lfd = @LfdNr
         IF @Ebene = 7 SET @E7_Lfd = @LfdNr
         IF @Ebene = 8 SET @E8_Lfd = @LfdNr
         IF @Ebene = 9 SET @E9_Lfd = @LfdNr
     END

     INSERT INTO dbo.#Tmp( PrjNr, BelNr, BelArt, Ebene, SatzArt, LfdNr, Cnt ) 
            VALUES( @PrjNr, @BelNr, @BelArt, @Ebene, @SatzArt, @LfdNr, @Cnt )
     SET @LastEbene = @Ebene
     SET @Cnt = @Cnt + 1

     FETCH NEXT FROM KC
     INTO @PrjNr, @BelArt, @BelNr, @Ebene, @SatzArt, @LfdNr
  END
  CLOSE KC
  DEALLOCATE KC
  SELECT
 	 T.Ebene                     AS TEbene
	,T.SatzArt                   AS TSatzArt
	,T.LfdNr                     AS TLfdNr
	,T.Cnt                       AS ILfdNr
        ,B.PrjNr, B.BelArt, B.BelNr, B.Ebene, B.SatzArt, B.Bedarf, B.Alternativ, B.Entfaellt, B.PosNr
        ,B.ArtNr, B.Ktxt1, B.Menge, B.VkMat, B.VkLohn, B.VkFremd, B.EkMat, B.EkLohn, B.EkFremd
        ,B.EkZube, B.MittelLohn, B.Zeit, B.Me, B.Gebinde, B.Ktxt2, B.Ktxt3, B.EkNe
        ,B.OldMLohn, B.AbsMenge, B.EPPos, B.AbsGP
        ,BEL.PRJNR                   AS BelPRJNR
	,BEL.FAELLTAGE ,BEL.BELARTTEXT ,BEL.WAEHRUNG ,BEL.KDNR ,BEL.BELEGART ,BEL.TAG ,BEL.MONAT ,BEL.JAHR
	,BEL.SUMMEFREMD ,BEL.MWSTSATZ ,BEL.EIGANSCH ,BEL.TITEL ,BEL.BETREFF1 ,BEL.BETREFF2 ,BEL.BETREFF3
	,BEL.VORHABEN ,BEL.BELEGNR ,BKE.Lose ,BKE.Untertitel	
	,BAK.DestDebNr               AS KNr
	,BAK.Anrede                  AS KAnrede
	,BAK.Name1                   AS KName1
	,BAK.Name2                   AS KName2
	,BAK.Strasse                 AS KStrasse
	,LEFT(BAK.Plz,5)+' '+BAK.Ort AS KPlzOrt
	,PE.Faktor                   AS Faktor
	,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
  INNER JOIN dbo.Belege BEL
          ON T.PrjNr  = BEL.PRJNR AND T.BelArt = BEL.BELEGART AND T.BelNr  = BEL.BELEGNR  
  LEFT  JOIN dbo.BelPos B
          ON B.PrjNr  = T.PrjNr AND B.BelArt = T.BelArt AND B.BelNr = T.BelNr AND B.LfdNr  = T.LfdNr      
  LEFT  JOIN dbo.BelKopfExt BKE
          ON T.PrjNr  = BKE.PrjNr AND T.BelArt = BKE.BelArt AND T.BelNr  = BKE.BelNr   
  LEFT  JOIN dbo.BelAdresse BAK
          ON T.PrjNr  = BAK.PrjNr AND T.BelArt = BAK.BelArt AND T.BelNr  = BAK.BelNr AND BAK.LfdNr = 0
  LEFT  JOIN dbo.Preiseinheiten PE
          ON PE.Nr = B.Pe
  LEFT  JOIN dbo.Anschriften AN
          ON AN.Nr = '01'
  WHERE BEL.PRJNR = @p_prjnr And BEL.BELEGART = @p_belart And BEL.BELEGNR = @p_belnr
  ORDER BY T.Cnt 




GO
CREATE PROCEDURE  dbo.crp_Aufmass
								@FltKey  char(18) = 'NULL'
                               ,@LeerBa  char(4)  = '0'
                               ,@LeerPos char(4)  = '0'
AS
DECLARE @szKdNr     char(15)
DECLARE @szName     char(40)
DECLARE @szPrjNr    char(8)
DECLARE @szBelNr    char(8)
DECLARE @szBelArt   char(2)
DECLARE @szVorhaben char(10)
DECLARE @szBetreff  char(42)

SET @szPrjNr     = LEFT(@FltKey, 8)
SET @szBelNr     = RIGHT(@FltKey, 8)
SET @szBelArt    = SUBSTRING(@FltKey,9,2)
SET @szKdNr      = (SELECT TOP 1 BE.KDNR     FROM dbo.Belege BE 
                    WHERE BE.PRJNR = @szPrjNr AND BE.BELEGART = @szBelArt AND BE.BELEGNR = @szBelNr)
SET @szName      = (SELECT TOP 1 BE.NAME1    FROM dbo.Belege BE 
                    WHERE BE.PRJNR = @szPrjNr AND BE.BELEGART = @szBelArt AND BE.BELEGNR = @szBelNr)
SET @szVorhaben  = (SELECT TOP 1 BE.VORHABEN FROM dbo.Belege BE 
                    WHERE BE.PRJNR = @szPrjNr AND BE.BELEGART = @szBelArt AND BE.BELEGNR = @szBelNr)
SET @szBetreff   = (SELECT TOP 1 BE.BETREFF1 FROM dbo.Belege BE 
                    WHERE BE.PRJNR = @szPrjNr AND BE.BELEGART = @szBelArt AND BE.BELEGNR = @szBelNr)

DECLARE @szLfdNr char(6)
DECLARE @szBaRef char(20)
SET @szLfdNr = (SELECT TOP 1 BP.LfdNr FROM dbo.BelPos BP
                WHERE (BP.PrjNr = @szPrjNr AND BP.BelArt = @szBelArt AND BP.BelNr = @szBelNr) AND
                      ((BP.SatzArt = 6) OR (BP.SatzArt = 7)) AND (BP.Marked = 1)
                ORDER BY BP.LfdNr DESC)

IF (@szLfdNr IS NULL) OR (LEN(@szLfdNr) = 0)
BEGIN 
SET @szLfdNr = (SELECT TOP 1 BP.LfdNr FROM dbo.BelPos BP
                WHERE (BP.PrjNr = @szPrjNr AND BP.BelArt = @szBelArt AND BP.BelNr = @szBelNr) AND
                      ((BP.SatzArt = 6) Or (BP.SatzArt = 7))
                ORDER BY BP.LfdNr DESC)
END
SET @szBaRef = ISNULL((SELECT TOP 1 A.BaRef FROM dbo.BelMassPosRef A
                       WHERE A.PrjNr = @szPrjNr AND A.BelArt = @szBelArt AND A.BelNr = @szBelNr
                       ORDER BY A.BaRef DESC), '')

DECLARE @nNumLeerBa  int
DECLARE @nNumLeerPos int
SET @nNumLeerBa = 0
SET @nNumLeerPos = 0

IF ISNUMERIC(@LeerBa) = 1 BEGIN SET @nNumLeerBa = CONVERT(int, @LeerBa) END
IF @nNumLeerBa > 999 BEGIN SET @nNumLeerBa = 999 END
IF ISNUMERIC(@LeerPos) = 1 BEGIN SET @nNumLeerPos = CONVERT(int, @LeerPos) END
IF @nNumLeerPos > 999 BEGIN SET @nNumLeerPos = 999 END

CREATE TABLE dbo.#Tmp_LeerBa (BaRef char(20), LfdNr char(6))
CREATE TABLE dbo.#Tmp_LeerPos (BaRef char(20), LfdNr char(6))
IF SUBSTRING(@@VERSION,22,1) = '2' OR SUBSTRING(@@VERSION,23,1) = '2'
BEGIN
   EXECUTE('
            ALTER TABLE dbo.#Tmp_LeerBa ALTER COLUMN BaRef  char(20) COLLATE database_default 
            ALTER TABLE dbo.#Tmp_LeerBa ALTER COLUMN LfdNr  char(6)  COLLATE database_default 
          ')
   EXECUTE('
            ALTER TABLE dbo.#Tmp_LeerPos ALTER COLUMN BaRef  char(20) COLLATE database_default 
            ALTER TABLE dbo.#Tmp_LeerPos ALTER COLUMN LfdNr  char(6)  COLLATE database_default 
          ')
END

WHILE @nNumLeerBa > 0
BEGIN
   INSERT INTO dbo.#Tmp_LeerBa (BaRef , LfdNr) 
          VALUES ('ZZZ' + LTRIM(RTRIM(STR(@nNumLeerBa))), @szLfdNr)
   SET @nNumLeerBa = @nNumLeerBa - 1
END
WHILE @nNumLeerPos > 0
BEGIN
   INSERT INTO dbo.#Tmp_LeerPos (BaRef , LfdNr) 
          VALUES (@szBaRef, 'ZZZ' + LTRIM(RTRIM(STR(@nNumLeerPos))))
   SET @nNumLeerPos = @nNumLeerPos - 1
END

SELECT TOP 100 PERCENT 
        ISNULL(BMPR.BaRef,'')      AS BaRef
       ,ISNULL(BMR.Wert,0)         AS Wert
       ,ISNULL(BMPR.AmRefKey,'')   AS AmRefKey
       ,ISNULL(BMR.AmNr,0)         AS AmNr
       ,BP.SatzArt ,BP.PosNr ,BP.BelNr ,BP.BelArt ,BP.Ktxt1 ,BP.PrjNr ,BP.ArtNr ,BP.Menge ,BP.Me ,BP.Ktxt2 
       ,BP.Ktxt3 ,BP.TxtNr ,BP.RefKey, BP.LfdNr ,B.BELARTTEXT ,B.KDNR ,B.ANREDE
       ,B.VORNAME ,B.NAME1 ,B.NAME2 ,B.STRASSE ,B.LAND ,B.PLZ ,B.ORT ,B.VORHABEN
       ,B.BETREFF1 ,B.BETREFF2 ,B.BETREFF3 
       ,ISNULL(BMPR.Datum,'') AS Datum
       ,BP.Marked
       ,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.Belege B 
INNER JOIN dbo.BelPos BP
        ON (BP.BelNr = B.BELEGNR) And (BP.BelArt = B.BELEGART) And (BP.PrjNr = B.PRJNR) And
           (BP.SatzArt = 6 Or BP.SatzArt = 7)
LEFT  JOIN dbo.BelMassPosRef BMPR
        ON (BMPR.PosRefKey = BP.RefKey) And (BMPR.BelNr = BP.BelNr) And (BMPR.BelArt = BP.BelArt)
LEFT  JOIN dbo.BelMassRef BMR
        ON (BMPR.AmRefKey = BMR.AmRefKey) And (BP.BelNr = BMR.BelNr) And (BP.BelArt = BMR.BelArt)
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE B.PRJNR = @szPrjNr AND B.BELEGART = @szBelArt AND B.BELEGNR = @szBelNr 
UNION
SELECT TOP 100 PERCENT        
        ISNULL(T.BaRef,'')         AS BaRef
       ,ISNULL(0,0)                AS Wert
       ,ISNULL(BMPR.AmRefKey,'')   AS AmRefKey
       ,ISNULL(-1,0)               AS AmNr
       ,BP.SatzArt ,BP.PosNr ,BP.BelNr ,BP.BelArt ,BP.Ktxt1 ,BP.PrjNr ,BP.ArtNr ,BP.Menge ,BP.Me ,BP.Ktxt2 
       ,BP.Ktxt3 ,BP.TxtNr ,BP.RefKey, BP.LfdNr, B.BELARTTEXT ,B.KDNR ,B.ANREDE
       ,B.VORNAME ,B.NAME1 ,B.NAME2 ,B.STRASSE ,B.LAND ,B.PLZ ,B.ORT ,B.VORHABEN
       ,B.BETREFF1 ,B.BETREFF2 ,B.BETREFF3 
       ,ISNULL(BMPR.Datum,'') AS Datum
       ,BP.Marked
       ,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.Belege B 
INNER JOIN dbo.BelPos BP
        ON (BP.BelNr = B.BELEGNR) And (BP.BelArt = B.BELEGART) And (BP.PrjNr = B.PRJNR) And
           (BP.SatzArt = 6 Or BP.SatzArt = 7)
INNER JOIN dbo.#Tmp_LeerBa T
        ON T.LfdNr = BP.LfdNr
LEFT  JOIN dbo.BelMassPosRef BMPR
        ON (BMPR.PosRefKey = BP.RefKey) And (BMPR.BelNr = BP.BelNr) And (BMPR.BelArt = BP.BelArt)
LEFT  JOIN dbo.BelMassRef BMR
        ON (BMPR.AmRefKey = BMR.AmRefKey) And (BP.BelNr = BMR.BelNr) And (BP.BelArt = BMR.BelArt)
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE B.PRJNR = @szPrjNr AND B.BELEGART = @szBelArt AND B.BELEGNR = @szBelNr 
UNION
SELECT TOP 100 PERCENT        
        ISNULL(T.BaRef,'')  AS BaRef
       ,0    AS Wert
       ,''   AS AmRefKey
       ,-1   AS AmNr
       ,0 ,'' ,@szBelNr ,@szBelArt ,'' ,'' ,'' ,0 ,'' ,'' 
       ,'' ,'' ,'', T.LfdNr, '' ,@szKdNr ,''
       ,'' ,@szName ,'' ,'' ,'' ,'' ,'' ,@szVorhaben
       ,@szBetreff ,'' ,'' 
       ,''
       ,0
       ,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.#Tmp_LeerPos T
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'






GO
CREATE  PROCEDURE dbo.crp_Bestellung
				  @mNRHW      char(15)
				 ,@mAbruf     char(8)
AS
SELECT 
  B.RecId ,1 AS MandId ,B.PRJNR ,B.NRHW ,B.NRGH ,B.STATUS ,B.KDNRHW ,B.KDNRGH ,B.LKZ ,B.BETREFF 
 ,B.TERMIN ,B.WAEHR ,B.BEARB ,B.INFO1 ,B.INFO2 ,B.INFO3 ,B.INFO4

 ,BP.POSNRHW ,BP.ARTNR ,BP.MENGE ,BP.TEXT1 ,BP.TEXT2 ,BP.BRUTTO ,BP.RAB1 ,BP.RAB2 ,BP.NETTO ,BP.ME ,BP.PE 
 ,BP.LVNR

 ,ISNULL((SELECT TOP 1 T.Datum  FROM dbo.BestellAbruf T WHERE T.NRHW = B.NRHW AND T.LfdNr = @mAbruf),'') AS TeilDatum 
 ,ISNULL((SELECT TOP 1 T.MENGE  FROM dbo.BestellAbrufMengen T WHERE T.NRHW = B.NRHW AND T.LfdNr = @mAbruf AND
                                                                    T.POSNRHW = BP.POSNRHW),0) AS TeilMenge 

 ,ISNULL((SELECT TOP 1 A.Anrede    FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Anrede 
 ,ISNULL((SELECT TOP 1 A.VorName   FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Vorname 
 ,ISNULL((SELECT TOP 1 A.Name1     FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Name1 
 ,ISNULL((SELECT TOP 1 A.Name2     FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Name2 
 ,ISNULL((SELECT TOP 1 A.Strasse   FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Strasse 
 ,ISNULL((SELECT TOP 1 A.Ort       FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Ort 
 ,ISNULL((SELECT TOP 1 A.Plz       FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Plz 
 ,ISNULL((SELECT TOP 1 A.Telefon1  FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Telefon1 
 ,ISNULL((SELECT TOP 1 A.Tel1Kurz  FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS TelKurz1 
 ,ISNULL((SELECT TOP 1 A.Telefon2  FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Telefon2 
 ,ISNULL((SELECT TOP 1 A.Tel2Kurz  FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS TelKurz2 
 ,ISNULL((SELECT TOP 1 A.Telefon3  FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Telefon3 
 ,ISNULL((SELECT TOP 1 A.Tel3Kurz  FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS TelKurz3 
 ,ISNULL((SELECT TOP 1 A.FaxNummer FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS FaxNummer
 --,ISNULL((SELECT TOP 1 A.FaxKurz   FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS FaxKurz 
 ,CONVERT(varchar(20),B.Datum,104) AS FaxKurz 
 ,ISNULL((SELECT TOP 1 A.EMail     FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS EMail 

 ,ISNULL((SELECT TOP 1 L.Anrede    FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Anrede 
 ,ISNULL((SELECT TOP 1 L.VorName   FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Vorname 
 ,ISNULL((SELECT TOP 1 L.Name1     FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Name1 
 ,ISNULL((SELECT TOP 1 L.Name2     FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Name2 
 ,ISNULL((SELECT TOP 1 L.Strasse   FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Strasse 
 ,ISNULL((SELECT TOP 1 L.Ort       FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Ort 
 ,ISNULL((SELECT TOP 1 L.Plz       FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Plz 
 ,ISNULL((SELECT TOP 1 L.Telefon1  FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Telefon1 
 ,ISNULL((SELECT TOP 1 L.Tel1Kurz  FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_TelKurz1 
 ,ISNULL((SELECT TOP 1 L.Telefon2  FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Telefon2 
 ,ISNULL((SELECT TOP 1 L.Tel2Kurz  FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_TelKurz2 
 ,ISNULL((SELECT TOP 1 L.Telefon3  FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Telefon3 
 ,ISNULL((SELECT TOP 1 L.Tel3Kurz  FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_TelKurz3 
 ,ISNULL((SELECT TOP 1 L.FaxNummer FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_FaxNummer
 ,ISNULL((SELECT TOP 1 L.FaxKurz   FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_FaxKurz 
 ,ISNULL((SELECT TOP 1 L.EMail     FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_EMail 

 ,ISNULL((SELECT TOP 1 M.[Name] FROM dbo.Mengeneinheiten M WHERE BP.ME = M.Nr),'') AS MeName
 ,ISNULL((SELECT COUNT(*) FROM dbo.Bestelltexte BT WHERE BT.NRHW = B.NRHW And BT.POSNRHW = BP.POSNRHW),0) AS ZusatzText

 ,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.Bestellungen B
LEFT  JOIN dbo.Bestellpositionen BP 
        ON B.NRHW = BP.NRHW
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE (B.NRHW = @mNRHW) AND
      (@mAbruf = '0' OR BP.POSNRHW IN (SELECT POSNRHW FROM BestellAbrufMengen WHERE NRHW = @mNRHW AND LfdNr = @mAbruf))




GO
CREATE  PROCEDURE dbo.crp_BestellungEx
				  @mNRHW      char(15)
				 ,@mAbruf     char(8)
AS
SELECT 
  B.RecId ,1 AS MandId ,B.PRJNR ,B.NRHW ,B.NRGH ,B.STATUS ,B.KDNRHW ,B.KDNRGH ,B.LKZ ,B.BETREFF 
 ,B.TERMIN ,B.WAEHR ,B.BEARB ,B.INFO1 ,B.INFO2 ,B.INFO3 ,B.INFO4

 ,BP.POSNRHW ,BP.ARTNR ,BP.MENGE ,BP.TEXT1 ,BP.TEXT2 ,BP.BRUTTO ,BP.RAB1 ,BP.RAB2 ,BP.NETTO ,BP.ME ,BP.PE 
 ,BP.LVNR

 ,ISNULL((SELECT TOP 1 T.Datum  FROM dbo.BestellAbruf T WHERE T.NRHW = B.NRHW AND T.LfdNr = @mAbruf),'') AS TeilDatum 
 ,ISNULL((SELECT TOP 1 T.MENGE  FROM dbo.BestellAbrufMengen T WHERE T.NRHW = B.NRHW AND T.LfdNr = @mAbruf AND
                                                                    T.POSNRHW = BP.POSNRHW),0) AS TeilMenge 

 ,ISNULL((SELECT TOP 1 A.Anrede    FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Anrede 
 ,ISNULL((SELECT TOP 1 A.VorName   FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Vorname 
 ,ISNULL((SELECT TOP 1 A.Name1     FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Name1 
 ,ISNULL((SELECT TOP 1 A.Name2     FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Name2 
 ,ISNULL((SELECT TOP 1 A.Strasse   FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Strasse 
 ,ISNULL((SELECT TOP 1 A.Ort       FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Ort 
 ,ISNULL((SELECT TOP 1 A.Plz       FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Plz 
 ,ISNULL((SELECT TOP 1 A.Telefon1  FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Telefon1 
 ,ISNULL((SELECT TOP 1 A.Tel1Kurz  FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS TelKurz1 
 ,ISNULL((SELECT TOP 1 A.Telefon2  FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Telefon2 
 ,ISNULL((SELECT TOP 1 A.Tel2Kurz  FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS TelKurz2 
 ,ISNULL((SELECT TOP 1 A.Telefon3  FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS Telefon3 
 ,ISNULL((SELECT TOP 1 A.Tel3Kurz  FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS TelKurz3 
 ,ISNULL((SELECT TOP 1 A.FaxNummer FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS FaxNummer
 --,ISNULL((SELECT TOP 1 A.FaxKurz   FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS FaxKurz 
 ,CONVERT(varchar(20),B.Datum,104) AS FaxKurz 
 ,ISNULL((SELECT TOP 1 A.EMail     FROM dbo.Adressen A WHERE A.LkzImport = B.LKZ AND A.DESTTYP = ' 3'),'') AS EMail 

 ,ISNULL((SELECT TOP 1 L.Anrede    FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Anrede 
 ,ISNULL((SELECT TOP 1 L.VorName   FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Vorname 
 ,ISNULL((SELECT TOP 1 L.Name1     FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Name1 
 ,ISNULL((SELECT TOP 1 L.Name2     FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Name2 
 ,ISNULL((SELECT TOP 1 L.Strasse   FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Strasse 
 ,ISNULL((SELECT TOP 1 L.Ort       FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Ort 
 ,ISNULL((SELECT TOP 1 L.Plz       FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Plz 
 ,ISNULL((SELECT TOP 1 L.Telefon1  FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Telefon1 
 ,ISNULL((SELECT TOP 1 L.Tel1Kurz  FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_TelKurz1 
 ,ISNULL((SELECT TOP 1 L.Telefon2  FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Telefon2 
 ,ISNULL((SELECT TOP 1 L.Tel2Kurz  FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_TelKurz2 
 ,ISNULL((SELECT TOP 1 L.Telefon3  FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_Telefon3 
 ,ISNULL((SELECT TOP 1 L.Tel3Kurz  FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_TelKurz3 
 ,ISNULL((SELECT TOP 1 L.FaxNummer FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_FaxNummer
 ,ISNULL((SELECT TOP 1 L.FaxKurz   FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_FaxKurz 
 ,ISNULL((SELECT TOP 1 L.EMail     FROM dbo.Adressen L WHERE L.DESTDEBNR = B.LADRNR),'') AS L_EMail 

 ,ISNULL((SELECT TOP 1 M.[Name] FROM dbo.Mengeneinheiten M WHERE BP.ME = M.Nr),'') AS MeName
 ,ISNULL((SELECT COUNT(*) FROM dbo.Bestelltexte BT WHERE BT.NRHW = B.NRHW And BT.POSNRHW = BP.POSNRHW),0) AS ZusatzText

 ,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

 ,ISNULL(UZName.Text42,'')   AS UZ_Name
 ,ISNULL(UZFon.Text42,'')    AS UZ_Fon
 ,ISNULL(UZFax.Text42,'')    AS UZ_Fax
 ,ISNULL(UZMail.Text42,'')   AS UZ_Mail
FROM dbo.Bestellungen B
LEFT  JOIN dbo.Bestellpositionen BP 
        ON B.NRHW = BP.NRHW
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
LEFT  JOIN dbo.Zusatztexte UZName
        ON UZName.Ident = 'SYSNAME4' + UPPER(B.BEARB)
LEFT  JOIN dbo.Zusatztexte UZFon
        ON UZFon.Ident = 'SYSFON4' + UPPER(B.BEARB)
LEFT  JOIN dbo.Zusatztexte UZFax
        ON UZFax.Ident = 'SYSFAX4' + UPPER(B.BEARB)
LEFT  JOIN dbo.Zusatztexte UZMail
        ON UZMail.Ident = 'SYSMAIL4' + UPPER(B.BEARB)
WHERE (B.NRHW = @mNRHW) AND
      (@mAbruf = '0' OR BP.POSNRHW IN (SELECT POSNRHW FROM BestellAbrufMengen WHERE NRHW = @mNRHW AND LfdNr = @mAbruf))





GO
CREATE PROCEDURE dbo.crp_Bestelltexte
				 @mNRHW    char(15)
				,@mPOSNRHW char(10)             
AS
SELECT
  T.NRHW, T.POSNRHW, T.POSNRGH, T.LFDNR, T.[TEXT]
FROM dbo.Bestelltexte T
WHERE T.NRHW = @mNRHW AND T.POSNRHW = @mPOSNRHW  




GO
CREATE PROCEDURE dbo.crp_PreisSpiegel
				 @mNRHW1 char(15) = ''
				,@mNRHW2 char(15) = ''
				,@mNRHW3 char(15) = ''
				,@mNRHW4 char(15) = ''
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 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 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 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 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),
                   preis1  decimal(17,2)  ,preis2  decimal(17,2)  ,preis3  decimal(17,2)  ,preis4  decimal(17,2),
                   brutto1 decimal(17,2)  ,brutto2 decimal(17,2)  ,brutto3 decimal(17,2)  ,brutto4 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)
                  )
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
        ')
END

INSERT INTO dbo.#Tmp (
                  artnr  ,posnr  ,menge  ,me, 
                  lkz1   ,lkz2   ,lkz3   ,lkz4,    
                  preis1 ,preis2 ,preis3 ,preis4  ,brutto1 ,brutto2 ,brutto3 ,brutto4,
                  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    ,'')
             
             ,brutto1  = CASE @NRHW WHEN @mNRHW1 THEN ISNULL(@Brutto ,0)   ELSE brutto1 END
	     ,preis1   = CASE @NRHW WHEN @mNRHW1 THEN ISNULL(@Netto  ,0)   ELSE preis1  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
	     ,preis2   = CASE @NRHW WHEN @mNRHW2 THEN ISNULL(@Netto  ,0)   ELSE preis2  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
	     ,preis3   = CASE @NRHW WHEN @mNRHW3 THEN ISNULL(@Netto  ,0)   ELSE preis3  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
	     ,preis4   = CASE @NRHW WHEN @mNRHW4 THEN ISNULL(@Netto  ,0)   ELSE preis4  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
SELECT
         T.artnr  ,T.posnr  ,T.menge  ,T.me
        ,T.lkz1   ,T.lkz2   ,T.lkz3   ,T.lkz4    
        ,T.preis1 ,T.preis2 ,T.preis3 ,T.preis4  ,T.brutto1 ,T.brutto2 ,T.brutto3 ,T.brutto4
        ,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
	,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_Artikeletikett
			@VonArtNr  char(20) = 'NULL'
                       ,@BisArtNr  char(20) = 'NULL'
AS
SELECT
       P.Lkz  ,P.Artnr ,P.Suchen ,P.Wargru ,P.Bez1 ,P.Bez2 ,P.Merkmal
      ,P.Bruttoek ,P.Vkladen  
      ,ISNULL( (P.Ekmat/ISNULL(NULLIF(P.EkPreisFaktor,0),1) + P.Zubehoerdm + P.NePreisJe1/100) *
               (1 + P.AufschlagMaterial/100) + P.Zeit * P.EkLohnJeMinute *
	       (1 + P.AufschlagLohn/100) ,0 )                            AS VK1gesamt      
FROM dbo.crv_ArtikelEkPreise P
WHERE @VonArtNr <= P.Lkz+P.Artnr AND @BisArtNr >= P.Lkz+P.Artnr



GO
CREATE PROCEDURE dbo.crp_Lagerfachkarte
			@VonArtNr  char(20) = 'NULL'
                       ,@BisArtNr  char(20) = 'NULL'
AS
SELECT
       S.Lkz  ,S.Artnr ,S.Suchen ,S.Bez1 ,S.Bez2 ,S.Merkmal 
      ,S.Bstnr ,S.Wargru ,S.Rabgru ,S.Bruttoek ,S.Rabatt1 ,S.Rabatt2 
      ,S.Datumpreis ,S.Lagerort ,S.Lagbest ,S.Invbest ,S.Wekum ,S.Wakum 
      ,S.Durchek ,S.Lagerwert ,S.Invdatum ,S.EANcode ,S.Gruppe
      ,ME.[Name]                 AS MeName
      ,PE.Faktor * S.Gebinde     AS PreisFaktor
      ,KM.VhMat                  AS KGM_VhMat
      ,Oml.OptDataD * S.Zeit     AS mEkLohn
      ,KL.VhLohn                 AS KGL_VhLohn
      ,KL.VhMat                  AS KGL_VhMat
      ,RTRIM(Odm.OptDataC)       AS Waehrung
      ,(SELECT
          ISNULL( SUM( 100 / NZ.Gebinde * NZ.Gewicht * 
                     (( TP.Tagespreis / NULLIF(TP.KgJePreis,0) ) - 
		      ( NZ.BasisPreis / NULLIF(NZ.KgJeBasis,0) ))) ,0 )
        FROM dbo.NeZuschlaege NZ
        INNER JOIN dbo.Nichteisen TP
                ON TP.Art = NZ.NeKz
        WHERE NZ.Lkz = S.Lkz AND NZ.Artnr = S.Artnr
       )                         AS Ne100
      ,ISNULL((SELECT TOP 1 NZ.Gewicht
               FROM dbo.NeZuschlaege NZ
               WHERE NZ.Lkz = S.Lkz AND NZ.Artnr = S.Artnr AND NZ.NeKz = 'CU' 
       ),0)                      AS mCuZahl
      ,ISNULL((SELECT TOP 1 NZ.NeKz
               FROM dbo.NeZuschlaege NZ
               WHERE NZ.Lkz = S.Lkz AND NZ.Artnr = S.Artnr AND NZ.NeKz = 'CU'
       ),'0')                    AS NeKz
      ,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.Stammpositionen S  
LEFT JOIN dbo.Mengeneinheiten ME
       ON ME.Nr = STR(S.Einheit,4)
LEFT JOIN dbo.Preiseinheiten  PE
       ON PE.Nr = STR(S.Pe,2)
LEFT JOIN dbo.Kalkulationsgruppen KM
       ON KM.Nr = STR(S.Kgmat,2)
LEFT JOIN dbo.Kalkulationsgruppen KL
       ON KL.Nr = STR(S.Kglohn,2)
LEFT JOIN dbo.Optionen Oml        
       ON Oml.OptId = ' 7006'
LEFT JOIN dbo.Optionen Odm      
       ON Odm.OptId = ' 7008'
LEFT JOIN dbo.Anschriften AN
       ON AN.Nr = '01'
WHERE @VonArtNr <= S.Lkz+S.Artnr AND @BisArtNr >= S.Lkz+S.Artnr



GO
CREATE PROCEDURE dbo.crp_Stammblatt
			@VonArtNr  char(20) = 'NULL'
                       ,@BisArtNr  char(20) = 'NULL'
AS
SELECT
       S.Lkz  ,S.Artnr ,S.Suchen ,S.Bez1 ,S.Bez2 ,S.Merkmal ,S.Textnr 
      ,S.Zeit ,S.Zubehoerdm ,S.Vkl2 ,S.Vkm2 ,S.Bstnr ,S.Wargru ,S.Rabgru
      ,S.Bruttoek ,S.Rabatt1 ,S.Rabatt2 ,S.Ekmat ,S.Datumpreis ,S.Lagerort
      ,S.Lagbest ,S.Durchek ,S.Lagerwert ,S.Lkztextnr ,S.Vkladen
      ,S.Textkz ,S.EANcode ,S.Gruppe
      ,ME.[Name]                   AS MeName
      ,PE.Faktor * S.Gebinde       AS PreisFaktor
      ,KM.VhMat                    AS KGM_VhMat
      ,Oml.OptDataD * S.Zeit       AS mEkLohn
      ,KL.VhLohn                   AS KGL_VhLohn
      ,KL.VhMat                    AS KGL_VhMat
      ,RTRIM(Odm.OptDataC)         AS Waehrung
      ,(SELECT
          ISNULL( SUM( 100 / NZ.Gebinde * NZ.Gewicht * 
                     (( TP.Tagespreis / NULLIF(TP.KgJePreis,0) ) - 
		      ( NZ.BasisPreis / NULLIF(NZ.KgJeBasis,0) ))) ,0 )
        FROM dbo.NeZuschlaege NZ
        INNER JOIN dbo.Nichteisen TP
                ON TP.Art = NZ.NeKz
        WHERE NZ.Lkz = S.Lkz AND NZ.Artnr = S.Artnr
       )                           AS Ne100
      ,ISNULL((SELECT TOP 1 NZ.Gewicht
               FROM dbo.NeZuschlaege NZ
               WHERE NZ.Lkz = S.Lkz AND NZ.Artnr = S.Artnr And NZ.NeKz = 'CU' 
       ),0)                        AS mCuZahl
      ,ISNULL((SELECT TOP 1 NZ.NeKz
               FROM dbo.NeZuschlaege NZ
               WHERE NZ.Lkz = S.Lkz AND NZ.Artnr = S.Artnr And NZ.NeKz = 'CU'
       ),'0')                      AS NeKz
      ,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.Stammpositionen S  
LEFT JOIN dbo.Mengeneinheiten ME
       ON ME.Nr = STR(S.Einheit,4)
LEFT JOIN dbo.Preiseinheiten  PE
       ON PE.Nr = STR(S.Pe,2)
LEFT JOIN dbo.Kalkulationsgruppen KM
       ON KM.Nr = STR(S.Kgmat,2)
LEFT JOIN dbo.Kalkulationsgruppen KL
       ON KL.Nr = STR(S.Kglohn,2)
LEFT JOIN dbo.Optionen Oml        
       ON Oml.OptId = ' 7006'
LEFT JOIN dbo.Optionen Odm      
       ON Odm.OptId = ' 7008'
LEFT JOIN dbo.Anschriften AN
       ON AN.Nr = '01'
WHERE @VonArtNr <= S.Lkz+S.Artnr AND @BisArtNr >= S.Lkz+S.Artnr




GO
CREATE VIEW dbo.crv_TextKleinteile
AS
SELECT 
         K.RecId ,1 AS MandId ,K.Lkz ,K.Artnr
        ,K.LfdNr ,K.RefLkz ,K.RefArtnr ,K.RefMenge
	,ISNULL( S.Bez1,    '')     AS Bez1
	,ISNULL( S.Bez2,    '')     AS Bez2
	,ISNULL( S.Merkmal, '')     AS Merkmal
FROM dbo.Kleinteile K
INNER JOIN dbo.Stammpositionen S
        ON  K.RefLkz = S.Lkz And K.RefArtnr = S.Artnr


GO
CREATE VIEW dbo.crv_ErfassungsStapel
AS
SELECT 
         B.RecId ,1 AS MandId ,B.TransNo ,B.LfdNr ,B.Jahr ,B.Monat ,B.Tag
        ,B.KtoSoll ,B.KtoHaben ,B.UstSchl ,B.Betrag ,B.Waehrung ,B.BuText
        ,B.BelegNr ,B.Verb_Bsh ,B.Verb_Ext ,B.Kst ,B.Ktr ,B.Faellig ,B.SkoFrist
        ,B.SkoSatz ,B.Name1 ,B.Name2 ,B.Strasse ,B.Plz ,B.Ort
        ,B.PrjNr ,B.KontoArtS ,B.KontoArtH
	,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.Buchungskladde B
LEFT JOIN dbo.Anschriften AN
       ON AN.Nr = '01'


GO
CREATE PROCEDURE dbo.crp_AktuellesJahr
					@HeutigesDatum char(10) = 'NULL'
AS

DECLARE @Heute char(4)
IF ISDATE(@HeutigesDatum) = 0
	set @Heute = STR( YEAR(GETDATE()), 4 )
ELSE
	set @Heute = RIGHT( @HeutigesDatum, 4)

DECLARE @wochennr  int 
DECLARE @monat     int 
DECLARE @tag       int 
DECLARE @wochentag int 
 set @wochennr  = 1
 set @monat     = 1
 set @tag       = 1
 set @wochentag = DATEPART( dw, @Heute + '/01/01')

DECLARE @Mo char(2)
DECLARE @Di char(2)
DECLARE @Mi char(2)
DECLARE @Do char(2)
DECLARE @Fr char(2)
DECLARE @Sa char(2)
DECLARE @So char(2)
DECLARE @WMo char(1)
DECLARE @WDi char(1)
DECLARE @WMi char(1)
DECLARE @WDo char(1)
DECLARE @WFr char(1)
DECLARE @WSa char(1)
DECLARE @WSo char(1)
DECLARE @F   char(1)
CREATE TABLE dbo.#crt_Jahr ( MandId int NOT NULL, monat char(2), wochennr int, 
                         Mo char(2), Di char(2), Mi char(2), Do char(2),
                         Fr char(2), Sa char(2), So char(2), WMo char(1),
			 WDi char(1), WMi char(1), WDo char(1), WFr char(1),
                         WSa char(1), WSo char(1)
                       )
WHILE @monat < 13
BEGIN
 set @Mo  = ''
 set @Di  = ''
 set @Mi  = ''
 set @Do  = ''
 set @Fr  = ''
 set @Sa  = ''
 set @So  = ''
 set @WMo = ''
 set @WDi = ''
 set @WMi = ''
 set @WDo = ''
 set @WFr = ''
 set @WSa = ''
 set @WSo = ''
 set @F   = ''
 IF @wochentag > 7
 BEGIN
   set @wochentag = 1
   set @wochennr  = @wochennr + 1
 END
  WHILE @wochentag < 8
  BEGIN
      IF 1 = ( select count(*) from dbo.Feiertage F 
               where  CAST(F.tag   AS int) = @tag   And
                      CAST(F.monat AS int) = @monat And
                      CAST(F.jahr  AS int) = YEAR(GETDATE()) )
        set @F = 'F'
      ELSE
        set @F = 'A'
	  IF @wochentag = 1
          BEGIN
		set @Mo  = @tag
                set @WMo = @F
          END
	  IF @wochentag = 2
          BEGIN
		set @Di  = @tag
                set @WDi = @F
          END
	  IF @wochentag = 3
          BEGIN
		set @Mi  = @tag
                set @WMi = @F
          END
	  IF @wochentag = 4
          BEGIN
		set @Do  = @tag
                set @WDo = @F
          END
	  IF @wochentag = 5
          BEGIN
		set @Fr  = @tag
                set @WFr = @F
          END
	  IF @wochentag = 6
          BEGIN
		set @Sa  = @tag
                set @WSa = @F
          END
	  IF @wochentag = 7
          BEGIN
		set @So  = @tag
                set @WSo = @F
          END
	  set @wochentag = @wochentag + 1	
	  set @tag = @tag + 1     
	  IF (@tag = 32 And (@monat = 1 Or @monat = 3  Or @monat = 5  Or @monat = 7 Or
	                     @monat = 8 Or @monat = 10 Or @monat = 12 )) Or
	     (@tag = 31 And (@monat = 4 Or @monat = 6  Or @monat = 9  Or @monat = 11)) Or
	     (@tag = 29 And @monat = 2 And @Heute <> '2004' And @Heute <> '2008' And @Heute <> '2012' ) Or
	     (@tag = 30 And @monat = 2 )
		break
  END
  insert into #crt_Jahr( MandId, monat, wochennr, Mo, Di, Mi, Do, Fr, Sa, So,
                         WMo, WDi, WMi, WDo, WFr, WSa, WSo ) 
         values ( 1, @monat, @wochennr, @Mo, @Di, @Mi, @Do, @Fr, @Sa, @So,
                         @WMo, @WDi, @WMi, @WDo, @WFr, @WSa, @WSo )	
  IF (@tag = 32 And (@monat = 1 Or @monat = 3  Or @monat = 5  Or @monat = 7 Or
                     @monat = 8 Or @monat = 10 Or @monat = 12 )) Or
     (@tag = 31 And (@monat = 4 Or @monat = 6  Or @monat = 9  Or @monat = 11)) Or
     (@tag = 29 And @monat = 2 And @Heute <> '2004' And @Heute <> '2008' And @Heute <> '2012' And @Heute <> '2016' ) Or
     (@tag = 30 And @monat = 2 )
  BEGIN
        set @monat = @monat + 1
        set @tag   = 1
  END
  ELSE
  BEGIN
        set @wochentag = 1
        set @wochennr  = @wochennr + 1
  END
END
SELECT 
         1 AS MandId, J.monat, J.wochennr, J.Mo, J.Di, J.Mi, J.Do
        ,J.Fr, J.Sa, J.So, J.WMo, J.WDi, J.WMi, J.WDo, J.WFr
        ,J.WSa, J.WSo
	,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.#crt_Jahr J
LEFT JOIN dbo.Anschriften AN
       ON AN.Nr = '01'





GO
CREATE PROCEDURE dbo.crp_AktuellerMonat
					@HeutigesDatum varchar(10) = 'NULL'
AS

DECLARE @Heute char(4)
IF ISDATE(@HeutigesDatum) = 0
	set @Heute = STR( YEAR(GETDATE()) , 4 )
ELSE
	set @Heute = RIGHT( @HeutigesDatum, 4)

DECLARE @wochennr   int 
DECLARE @monat      int 
DECLARE @monat_verg int 
DECLARE @tag        int 
DECLARE @wochentag  int 
 set @wochennr  = 1
 set @monat     = 1
 set @tag       = 1
 set @wochentag = DATEPART( dw, @Heute + '/01/01')
DECLARE @wochentagTxt char(2) 

IF ISDATE(@HeutigesDatum) = 0
   set @monat_verg = MONTH( GETDATE() )
ELSE
   set @monat_verg = MONTH( CONVERT(datetime, @HeutigesDatum) )

CREATE TABLE dbo.#crt_Monat ( MandId int NOT NULL, monat int, tag int, wochentag char(2), wochennr int) 

WHILE @monat < 13
BEGIN
 IF @wochentag > 7
 BEGIN
   set @wochentag = 1
   set @wochennr  = @wochennr + 1
 END
  WHILE @wochentag < 8
  BEGIN

	  set @wochentagTxt =
		CASE @wochentag
                  WHEN 1 THEN 'Mo'
                  WHEN 2 THEN 'Di'
                  WHEN 3 THEN 'Mi'
                  WHEN 4 THEN 'Do'
                  WHEN 5 THEN 'Fr'
                  WHEN 6 THEN 'Sa'
                  WHEN 7 THEN 'So'
                END
  insert into dbo.#crt_Monat( MandId, monat, tag, wochentag, wochennr) 
         values ( 1, @monat, @tag, @wochentagTxt, @wochennr)

	  set @wochentag = @wochentag + 1	
	  set @tag = @tag + 1     
	  IF (@tag = 32 And (@monat = 1 Or @monat = 3  Or @monat = 5  Or @monat = 7 Or
	                     @monat = 8 Or @monat = 10 Or @monat = 12 )) Or
	     (@tag = 31 And (@monat = 4 Or @monat = 6  Or @monat = 9  Or @monat = 11)) Or
	     (@tag = 29 And @monat = 2 And @Heute <> '2004' And @Heute <> '2008' And @Heute <> '2012' ) Or
	     (@tag = 30 And @monat = 2 )
		break
  END
	
  IF (@tag = 32 And (@monat = 1 Or @monat = 3  Or @monat = 5  Or @monat = 7 Or
                     @monat = 8 Or @monat = 10 Or @monat = 12 )) Or
     (@tag = 31 And (@monat = 4 Or @monat = 6  Or @monat = 9  Or @monat = 11)) Or
     (@tag = 29 And @monat = 2 And @Heute <> '2004' And @Heute <> '2008' And @Heute <> '2012' And @Heute <> '2016' ) Or
     (@tag = 30 And @monat = 2 )
  BEGIN
        set @monat = @monat + 1
        set @tag   = 1
  END
  ELSE
  BEGIN
        set @wochentag = 1
        set @wochennr  = @wochennr + 1
  END
END
SELECT 
         1 AS MandId, M.monat, M.tag, M.wochentag, M.wochennr
	,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.#crt_Monat M
LEFT JOIN dbo.Anschriften AN
       ON AN.Nr = '01'
WHERE M.monat = @monat_verg And 
      M.wochentag <> 'So' And M.wochentag <> 'Sa'




GO
CREATE PROCEDURE dbo.crp_SL_Sachkonten
				    @BisDatum  varchar(10)
                                   ,@DebKonto  varchar(8)
                                   ,@KreKonto  varchar(8)
AS
DECLARE @m_BisDatum varchar(8)
SET @m_BisDatum = 'N'

IF LEN(ISNULL(@BisDatum,'')) = 10 BEGIN
    SET @m_BisDatum = SUBSTRING(@BisDatum,7,4) + SUBSTRING(@BisDatum,4,2) + SUBSTRING(@BisDatum,1,2)
END

DECLARE @m_DebSaldo float
DECLARE @m_KreSaldo float

CREATE TABLE dbo.#SL_Tmp (Konto char(8), Saldo float, Typ char(1))
IF SUBSTRING(@@VERSION,22,1) = '2' OR SUBSTRING(@@VERSION,23,1) = '2' BEGIN
  EXECUTE('ALTER TABLE dbo.#SL_Tmp ALTER COLUMN Konto char(8) COLLATE database_default')
  EXECUTE('ALTER TABLE dbo.#SL_Tmp ALTER COLUMN Typ   char(1) COLLATE database_default')
END

INSERT INTO dbo.#SL_Tmp (Konto, Saldo, Typ)
SELECT K.Konto
      ,(SELECT TOP 1 B.SaldoNeu FROM dbo.Buchungszeilen B 
        WHERE (B.Konto = K.Konto) AND (B.Typ = K.Typ) AND
              (@m_BisDatum = 'N' OR B.Jahr + B.Monat + B.Tag <= @m_BisDatum) 
        ORDER BY B.Jahr DESC,B.Monat DESC, B.Tag DESC, B.TransNo DESC, B.RecId DESC)
      ,'D'
FROM dbo.Kontenplan K WHERE K.Typ = 'P' AND K.KzKasse = ''

INSERT INTO dbo.#SL_Tmp (Konto, Saldo, Typ)
SELECT K.Konto
      ,(SELECT TOP 1 B.SaldoNeu FROM dbo.Buchungszeilen B 
        WHERE (B.Konto = K.Konto) AND (B.Typ = K.Typ) AND 
              (@m_BisDatum = 'N' OR B.Jahr + B.Monat + B.Tag <= @m_BisDatum) 
        ORDER BY B.Jahr DESC,B.Monat DESC, B.Tag DESC, B.TransNo DESC, B.RecId DESC)
      ,'K'
FROM dbo.Kontenplan K WHERE K.Typ = 'P' AND K.KzKasse = 'L'

SET @m_DebSaldo = ISNULL((SELECT SUM(ISNULL(T.Saldo,0)) FROM dbo.#SL_Tmp T WHERE T.Typ = 'D'), 0)
SET @m_KreSaldo = ISNULL((SELECT SUM(ISNULL(T.Saldo,0)) FROM dbo.#SL_Tmp T WHERE T.Typ = 'K'), 0)


SELECT
  ISNULL(K.[Name]  ,0 )   AS K_Name
 ,ISNULL(K.Typ     ,0 )   AS K_Typ
 ,ISNULL(K.Konto   ,0 )   AS K_Konto
 ,ISNULL(K.KzKasse ,'')   AS K_KzKasse
 ,ISNULL(K.Schl    ,'')   AS K_Schl

 ,ISNULL((SELECT TOP 1 B.SaldoNeu FROM dbo.Buchungszeilen B 
          WHERE (B.Konto = K.Konto) AND (B.Typ = K.Typ) AND 
                (@m_BisDatum = 'N' OR B.Jahr + B.Monat + B.Tag <= @m_BisDatum) 
          ORDER BY B.Jahr DESC, B.Monat DESC, B.Tag DESC, B.TransNo DESC, B.RecId DESC), 0) 
  + CASE WHEN K.Konto = @DebKonto THEN @m_DebSaldo ELSE 0 END
  + CASE WHEN K.Konto = @KreKonto THEN @m_KreSaldo ELSE 0 END    AS B_SaldoNeu

 ,ISNULL((SELECT TOP 1 B.SaldoUms FROM dbo.Buchungszeilen B 
          WHERE (B.Konto = K.Konto) AND (B.Typ = K.Typ) AND 
                (@m_BisDatum = 'N' OR B.Jahr + B.Monat + B.Tag <= @m_BisDatum) 
          ORDER BY B.Jahr DESC, B.Monat DESC, B.Tag DESC, B.TransNo DESC, B.RecId DESC), 0) AS B_SaldoUms

 ,ISNULL((SELECT TOP 1 B.Tag +'.'+ B.Monat +'.'+ B.Jahr FROM dbo.Buchungszeilen B 
          WHERE (B.Konto = K.Konto) AND (B.Typ = K.Typ) AND 
                (@m_BisDatum = 'N' OR B.Jahr + B.Monat + B.Tag <= @m_BisDatum) 
          ORDER BY B.Jahr DESC, B.Monat DESC, B.Tag DESC, B.TransNo DESC, B.RecId DESC), '') AS B_Datum

 ,ISNULL((SELECT TOP 1 O.OptDataC FROM dbo.Optionen O WHERE O.OptId = ' 7008'),'') AS O_Waehrung

 ,ISNULL((SELECT TOP 1 A.DESTDEBNR FROM dbo.Adressen A WHERE A.FibuKonto = K.Konto),'') AS A_DESTDEBNR
 ,ISNULL((SELECT TOP 1 A.Name1     FROM dbo.Adressen A WHERE A.FibuKonto = K.Konto),'') AS A_Name1
 ,ISNULL((SELECT TOP 1 A.Telefon1  FROM dbo.Adressen A WHERE A.FibuKonto = K.Konto),'') AS A_Telefon1

 ,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.Kontenplan K
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE (K.Typ = 'S') AND 
      ( (0 < ISNULL((SELECT COUNT(*) FROM dbo.Buchungszeilen Bu WHERE Bu.Typ = K.Typ AND Bu.Konto = K.Konto),0)) OR
        (K.Konto = @DebKonto) OR (K.Konto = @KreKonto) )



GO
CREATE PROCEDURE dbo.crp_SL_Kreditoren
				    @BisDatum  varchar(10)
AS
DECLARE @m_BisDatum varchar(8)
SET @m_BisDatum = 'N'

IF LEN(ISNULL(@BisDatum,'')) = 10 BEGIN
    SET @m_BisDatum = SUBSTRING(@BisDatum,7,4) + SUBSTRING(@BisDatum,4,2) + SUBSTRING(@BisDatum,1,2)
END

SELECT
  ISNULL(K.[Name]  ,0 )   AS K_Name
 ,ISNULL(K.Typ     ,0 )   AS K_Typ
 ,ISNULL(K.Konto   ,0 )   AS K_Konto
 ,ISNULL(K.KzKasse ,'')   AS K_KzKasse
 ,ISNULL(K.Schl    ,'')   AS K_Schl

 ,ISNULL((SELECT TOP 1 B.SaldoNeu FROM dbo.Buchungszeilen B 
          WHERE (B.Konto = K.Konto) AND (B.Typ = K.Typ) AND 
                (@m_BisDatum = 'N' OR B.Jahr + B.Monat + B.Tag <= @m_BisDatum) 
          ORDER BY B.Jahr DESC, B.Monat DESC, B.Tag DESC, B.TransNo DESC, B.RecId DESC), 0) AS B_SaldoNeu

 ,ISNULL((SELECT TOP 1 B.SaldoUms FROM dbo.Buchungszeilen B 
          WHERE (B.Konto = K.Konto) AND (B.Typ = K.Typ) AND 
                (@m_BisDatum = 'N' OR B.Jahr + B.Monat + B.Tag <= @m_BisDatum) 
          ORDER BY B.Jahr DESC, B.Monat DESC, B.Tag DESC, B.TransNo DESC, B.RecId DESC), 0) AS B_SaldoUms

 ,ISNULL((SELECT TOP 1 B.Tag +'.'+ B.Monat +'.'+ B.Jahr FROM dbo.Buchungszeilen B 
          WHERE (B.Konto = K.Konto) AND (B.Typ = K.Typ) AND 
                (@m_BisDatum = 'N' OR B.Jahr + B.Monat + B.Tag <= @m_BisDatum) 
          ORDER BY B.Jahr DESC, B.Monat DESC, B.Tag DESC, B.TransNo DESC, B.RecId DESC), '') AS B_Datum

 ,ISNULL((SELECT TOP 1 O.OptDataC FROM dbo.Optionen O WHERE O.OptId = ' 7008'),'') AS O_Waehrung

 ,ISNULL((SELECT TOP 1 A.DESTDEBNR FROM dbo.Adressen A WHERE A.FibuKonto = K.Konto),'') AS A_DESTDEBNR
 ,ISNULL((SELECT TOP 1 A.Name1     FROM dbo.Adressen A WHERE A.FibuKonto = K.Konto),'') AS A_Name1
 ,ISNULL((SELECT TOP 1 A.Telefon1  FROM dbo.Adressen A WHERE A.FibuKonto = K.Konto),'') AS A_Telefon1

 ,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.Kontenplan K
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE (K.Typ = 'P') AND (K.KzKasse = 'L') AND
      (0 <> ISNULL((SELECT TOP 1 Bu.SaldoNeu FROM dbo.Buchungszeilen Bu 
                    WHERE (Bu.Typ = K.Typ) AND (Bu.Konto = K.Konto) AND
                          (@m_BisDatum = 'N' OR Bu.Jahr + Bu.Monat + Bu.Tag <= @m_BisDatum) 
                    ORDER BY Bu.Jahr DESC, Bu.Monat DESC, Bu.Tag DESC, Bu.TransNo DESC, Bu.RecId DESC),0))




GO
CREATE PROCEDURE dbo.crp_SL_Debitoren
				    @BisDatum  varchar(10)
AS
DECLARE @m_BisDatum varchar(8)
SET @m_BisDatum = 'N'

IF LEN(ISNULL(@BisDatum,'')) = 10 BEGIN
    SET @m_BisDatum = SUBSTRING(@BisDatum,7,4) + SUBSTRING(@BisDatum,4,2) + SUBSTRING(@BisDatum,1,2)
END

SELECT
  ISNULL(K.[Name]  ,0 )   AS K_Name
 ,ISNULL(K.Typ     ,0 )   AS K_Typ
 ,ISNULL(K.Konto   ,0 )   AS K_Konto
 ,ISNULL(K.KzKasse ,'')   AS K_KzKasse
 ,ISNULL(K.Schl    ,'')   AS K_Schl

 ,ISNULL((SELECT TOP 1 B.SaldoNeu FROM dbo.Buchungszeilen B 
          WHERE (B.Konto = K.Konto) AND (B.Typ = K.Typ) AND 
                (@m_BisDatum = 'N' OR B.Jahr + B.Monat + B.Tag <= @m_BisDatum) 
          ORDER BY B.Jahr DESC, B.Monat DESC, B.Tag DESC, B.TransNo DESC, B.RecId DESC), 0) AS B_SaldoNeu

 ,ISNULL((SELECT TOP 1 B.SaldoUms FROM dbo.Buchungszeilen B 
          WHERE (B.Konto = K.Konto) AND (B.Typ = K.Typ) AND 
                (@m_BisDatum = 'N' OR B.Jahr + B.Monat + B.Tag <= @m_BisDatum) 
          ORDER BY B.Jahr DESC, B.Monat DESC, B.Tag DESC, B.TransNo DESC, B.RecId DESC), 0) AS B_SaldoUms

 ,ISNULL((SELECT TOP 1 B.Tag +'.'+ B.Monat +'.'+ B.Jahr FROM dbo.Buchungszeilen B 
          WHERE (B.Konto = K.Konto) AND (B.Typ = K.Typ) AND 
                (@m_BisDatum = 'N' OR B.Jahr + B.Monat + B.Tag <= @m_BisDatum) 
          ORDER BY B.Jahr DESC, B.Monat DESC, B.Tag DESC, B.TransNo DESC, B.RecId DESC), '') AS B_Datum

 ,ISNULL((SELECT TOP 1 O.OptDataC FROM dbo.Optionen O WHERE O.OptId = ' 7008'),'') AS O_Waehrung

 ,ISNULL((SELECT TOP 1 A.DESTDEBNR FROM dbo.Adressen A WHERE A.FibuKonto = K.Konto),'') AS A_DESTDEBNR
 ,ISNULL((SELECT TOP 1 A.Name1     FROM dbo.Adressen A WHERE A.FibuKonto = K.Konto),'') AS A_Name1
 ,ISNULL((SELECT TOP 1 A.Telefon1  FROM dbo.Adressen A WHERE A.FibuKonto = K.Konto),'') AS A_Telefon1

 ,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.Kontenplan K
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE (K.Typ = 'P') AND (K.KzKasse = '') AND
      (0 <> ISNULL((SELECT TOP 1 Bu.SaldoNeu FROM dbo.Buchungszeilen Bu 
                    WHERE (Bu.Typ = K.Typ) AND (Bu.Konto = K.Konto) AND
                          (@m_BisDatum = 'N' OR Bu.Jahr + Bu.Monat + Bu.Tag <= @m_BisDatum) 
                    ORDER BY Bu.Jahr DESC, Bu.Monat DESC, Bu.Tag DESC, Bu.TransNo DESC, Bu.RecId DESC),0))




GO
CREATE PROCEDURE dbo.crp_RechnungsEingang
                                  @mFltMon  varchar(2)
                                 ,@mFltJahr varchar(4)
                                 ,@mKonto   varchar(8)
AS
DECLARE @m_FltMon  varchar(2)
DECLARE @m_FltJahr varchar(4)
SET @m_FltMon  = '0'
SET @m_FltJahr = '0'

IF @mFltMon IS NOT NULL  AND LEN(@mFltMon) > 0  AND @mFltMon  <> '0'
    SET @m_FltMon  = RIGHT('0' + @mFltMon,  2)
IF @mFltJahr IS NOT NULL AND LEN(@mFltJahr) > 0 AND @mFltJahr <> '0'
    SET @m_FltJahr = RIGHT('0' + @mFltJahr, 2)

SELECT
         K.RecId ,1 AS MandId ,K.Typ ,K.Konto ,K.Jahr ,K.Monat ,K.Tag
        ,K.TransNo ,K.Soll ,K.Haben ,K.SaldoAlt ,K.SaldoNeu ,K.BtrOrg
        ,K.BtrWae ,K.Saldiert ,K.Abgeholt ,K.[Text] ,K.SaldoUms ,K.Kst
        ,K.Ktr ,K.PrjNr ,K.BelNr ,K.FJahr ,K.FMonat ,K.FTag ,K.AusglTRN
        ,K.AusglBtr ,K.SkoFrist ,K.SkoSatz
        ,ISNULL(
           (SELECT SUM(ISNULL(TRN1.Soll,0) - ISNULL(TRN1.Haben,0)) FROM dbo.Buchungszeilen TRN1
            INNER JOIN dbo.Kontenplan KP1
                    ON KP1.Konto = TRN1.Konto AND KP1.Typ = TRN1.Typ AND KP1.KzKasse = 'N'
            WHERE TRN1.TransNo = K.TransNo)
         ,0 )                       AS TRN_Netto
        ,ISNULL(
           (SELECT SUM(ISNULL(TRN2.Soll,0) - ISNULL(TRN2.Haben,0)) FROM dbo.Buchungszeilen TRN2
            INNER JOIN dbo.Kontenplan KP2
                    ON KP2.Konto = TRN2.Konto AND KP2.Typ = TRN2.Typ AND KP2.KzKasse = 'U' AND KP2.Schl = '008'
            WHERE TRN2.TransNo = K.TransNo)
         ,0 )                       AS TRN_Ust8
        ,ISNULL(
           (SELECT SUM(ISNULL(TRN3.Soll,0) - ISNULL(TRN3.Haben,0)) FROM dbo.Buchungszeilen TRN3
            INNER JOIN dbo.Kontenplan KP3
                    ON KP3.Konto = TRN3.Konto AND KP3.Typ = TRN3.Typ AND KP3.KzKasse = 'U' AND KP3.Schl = '009'
            WHERE TRN3.TransNo = K.TransNo)
         ,0 )                       AS TRN_Ust9
        ,ISNULL(
           (SELECT SUM(ISNULL(TRN4.Soll,0) - ISNULL(TRN4.Haben,0)) FROM dbo.Buchungszeilen TRN4
            INNER JOIN dbo.Kontenplan KP4
                    ON KP4.Konto = TRN4.Konto AND KP4.Typ = TRN4.Typ AND KP4.KzKasse = 'U' AND KP4.Schl <> '008' AND KP4.Schl <> '009'
            WHERE TRN4.TransNo = K.TransNo)
         ,0 )                       AS TRN_UstS
	,ISNULL( O.OptDataC, 'EUR') AS Waehrung
	,ISNULL( KP.[Name], 0 )     AS [Name]
	,ISNULL( KP.Typ, 0 )        AS KPTyp
	,ISNULL( KP.Konto, 0 )      AS KPKonto
	,ISNULL( KP.KzKasse, '' )   AS KPKzKasse
	,ISNULL( KP.Schl, '' )      AS KPSchl
	,A.Name1        	    AS K_Name1
	,A.Telefon1        	    AS K_Telefon1
	,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.Kontenplan KP
LEFT  JOIN dbo.Buchungszeilen K
        ON KP.Konto = K.Konto AND KP.Typ = K.Typ AND K.Tag <> '00' AND K.Tag <> '32' AND K.Soll = 0
LEFT  JOIN dbo.Optionen O
        ON O.OptId = ' 7008'
LEFT  JOIN dbo.Adressen A
        ON A.FibuKonto = KP.Konto
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE (KP.Typ = 'P') AND (KP.KzKasse = 'L') AND
      (@m_FltMon  = '0' OR K.Monat = @m_FltMon) AND (@m_FltJahr = '0' OR RIGHT(K.Jahr, 2) = @m_FltJahr) AND
      (@mKonto = 'NULL' OR KP.Konto = @mKonto) 




GO
CREATE VIEW dbo.crv_RessourcenPlanung
AS
SELECT
         R.RecId ,1 AS MandId ,R.LfdNr ,R.ResTyp ,R.ResNr ,R.Jahr ,R.Monat ,R.Tag
        ,R.SS ,R.SM ,R.ES ,R.EM ,R.PrjNr ,R.BelArt ,R.BelegNr ,R.Farbe
        ,R.KurzBezeichnung ,R.Uebernommen
	,A.DESTTYP ,A.DESTDEBNR ,A.Anrede ,A.VorName ,A.Name1 ,A.Name2 ,A.Strasse
	,A.Ort ,A.Plz ,A.Telefon1 ,A.FaxNummer 
	,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.Adressen A
INNER JOIN dbo.ResPlandaten R
        ON A.Annr = R.ResNr
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'




GO
CREATE PROCEDURE dbo.crp_Lagerbewegungen
                                  @Lkz   varchar(2)
                                 ,@Artnr varchar(18)
AS
SELECT 
  ARTNR, TAG, TAGDUMMY, MONAT, MONDUMMY, JAHR,
  KZ_ZA, INFO, MENGE, VERBUCHT, EKPREIS, GEBINDE, PrjNr
FROM dbo.Lagerbewegungen
WHERE @Lkz + @Artnr = ARTNR
GO




--*****************************************************************************************************************
-----------------------  Langtexte für Planung --------------------------------------------------------------------
--*****************************************************************************************************************



IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'U' and [name] = 'crt_TerminLangTexte')
	DROP TABLE dbo.crt_TerminLangTexte
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_MakeTerminLangText')
	DROP PROCEDURE dbo.crp_MakeTerminLangText
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_TerminText')
	DROP PROCEDURE dbo.crp_TerminText
GO


CREATE TABLE dbo.crt_TerminLangTexte(  Ident     varchar(8)  
                                      ,LfdNr     varchar(6)   
	                              ,LineData  varchar(200) )
GO
CREATE INDEX IkPrimary ON dbo.crt_TerminLangTexte( Ident, LfdNr )
GO


CREATE PROCEDURE dbo.crp_MakeTerminLangText
                                        @mIdent   varchar(8)
AS
  SET @mIdent = REPLACE(@mIdent,'.','')
  DECLARE @mLfdNr integer
  SET @mLfdNr = 1

  DECLARE   CWA CURSOR FOR 
    SELECT   Daten, CntChar, SoftBreak+LineFeeds, Abschluss
    FROM     dbo.Langtextzeilen 
    WHERE    REPLACE(Art + Lkz + Nr,' ','') = REPLACE('26RTTERMIN' + @mIdent,' ','') 
    ORDER BY Lkz,Nr,LfdNr
  OPEN CWA
  
  DECLARE @mDaten  char(55)
  DECLARE @mCnt    integer
  DECLARE @mBreak  integer
  DECLARE @mEnde   integer
  DECLARE @mText   varchar(160)

  SET @mText   = ''
  FETCH NEXT FROM CWA INTO @mDaten, @mCnt, @mBreak, @mEnde
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF (LEN(@mText)+@mCnt) > 200
    BEGIN
      INSERT INTO dbo.crt_TerminLangTexte (Ident, LfdNr, LineData) VALUES (@mIdent, STR(@mLfdNr,6), @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_TerminLangTexte (Ident, LfdNr, LineData) VALUES (@mIdent, STR(@mLfdNr,6), @mText)
      SET @mLfdNr = @mLfdNr + 1
      SET @mText  = ''
      SET @mBreak = @mBreak - 1
    END

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

  IF LEN(@mText) > 0
  BEGIN
      INSERT INTO dbo.crt_TerminLangTexte (Ident, LfdNr, LineData) VALUES (@mIdent, STR(@mLfdNr,6), @mText)
      SET @mLfdNr = @mLfdNr + 1
      SET @mText  = ''
  END

  CLOSE CWA
  DEALLOCATE CWA
GO


CREATE PROCEDURE dbo.crp_TerminText
			    @pIdent  varchar(8)
AS
  SET @pIdent = REPLACE(@pIdent,'.','')
  DELETE FROM dbo.crt_TerminLangTexte WHERE Ident = @pIdent
  EXECUTE dbo.crp_MakeTerminLangText @pIdent
  SELECT LfdNr, LineData AS Daten FROM dbo.crt_TerminLangTexte WHERE Ident = @pIdent ORDER BY LfdNr
  DELETE FROM dbo.crt_TerminLangTexte WHERE Ident = @pIdent
GO

