-- This file is part of the it-crafts software and is subject to copyright protection.
-- (C) 2022..2024, All rights reserved by Steffen Anders, Elsteraue - Germany
-- Visit also https://www.it-crafts.de.
--
-- Datei: hwext_tr.sql
-- Stand: 10/2001, 06/2022, 01/2024
-- Autor: Steffen Anders
-- Volltextsuche 


ALTER PROCEDURE [dbo].[up_TrSel_Adressen]
AS
  DECLARE @Opt5311 integer = ISNULL( (SELECT TOP 1 OptDataI FROM [dbo].[ut_Optionen] WHERE OptId = ' 5311'), 0 )

  SELECT   RecId,
      LTRIM(RTRIM([DESTDEBNR]))
      +LTRIM(RTRIM([SUCHEN]))
      +LTRIM(RTRIM([VorName]))
      +LTRIM(RTRIM([Name1]))
      +LTRIM(RTRIM([Name2]))
      +LTRIM(RTRIM([Branche]))
      +LTRIM(RTRIM([Strasse]))
      +LTRIM(RTRIM([Ort]))
      +LTRIM(RTRIM([Land]))
      +LTRIM(RTRIM([Plz]))
      +LTRIM(RTRIM([Dokument]))
      +LTRIM(RTRIM([Info1]))
      +LTRIM(RTRIM([Info2]))
      +LTRIM(RTRIM([Info3]))
      +LTRIM(RTRIM([Info4]))
      +LTRIM(RTRIM([UstId1]))
      +LTRIM(RTRIM([Telefon1]))
      +LTRIM(RTRIM([EMail]))
      +LTRIM(RTRIM([zuHaenden]))
      +LTRIM(RTRIM([zuHaeAnre]))
      +LTRIM(RTRIM([Tel1Kurz]))
      +LTRIM(RTRIM([Telefon2]))
      +LTRIM(RTRIM([Tel2Kurz]))
      +LTRIM(RTRIM([Telefon3]))
      +LTRIM(RTRIM([Tel3Kurz]))
      +LTRIM(RTRIM([FaxNummer]))
      +LTRIM(RTRIM([FaxKurz]))
      +LTRIM(RTRIM([Selection1]))
      +LTRIM(RTRIM([Selection2]))
      +LTRIM(RTRIM([FibuKonto]))
      +LTRIM(RTRIM([HomePage]))
      +LTRIM(RTRIM([LandName]))
      +LTRIM(RTRIM([PsfPlz]))
      +LTRIM(RTRIM([Psf]))
      +LTRIM(RTRIM([PlzGrossEm]))
    AS Data
  FROM     [dbo].[ut_Adressen]
  WHERE    (RecId > 0) AND (([DESTTYP] < '10') OR ((@Opt5311 > 0) AND ([DESTTYP] like '2%')))
  ORDER BY RecId
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_TrSel_Lieferanten')
  DROP PROCEDURE [dbo].[up_TrSel_Lieferanten]
GO
CREATE PROCEDURE [dbo].[up_TrSel_Lieferanten]
AS
  SELECT   RecId,
      LTRIM(RTRIM([DESTDEBNR]))
      +LTRIM(RTRIM([SUCHEN]))
      +LTRIM(RTRIM([VorName]))
      +LTRIM(RTRIM([Name1]))
      +LTRIM(RTRIM([Name2]))
      +LTRIM(RTRIM([Branche]))
      +LTRIM(RTRIM([Strasse]))
      +LTRIM(RTRIM([Ort]))
      +LTRIM(RTRIM([Land]))
      +LTRIM(RTRIM([Plz]))
      +LTRIM(RTRIM([Dokument]))
      +LTRIM(RTRIM([Info1]))
      +LTRIM(RTRIM([Info2]))
      +LTRIM(RTRIM([Info3]))
      +LTRIM(RTRIM([Info4]))
      +LTRIM(RTRIM([UstId1]))
      +LTRIM(RTRIM([Telefon1]))
      +LTRIM(RTRIM([EMail]))
      +LTRIM(RTRIM([zuHaenden]))
      +LTRIM(RTRIM([zuHaeAnre]))
      +LTRIM(RTRIM([Tel1Kurz]))
      +LTRIM(RTRIM([Telefon2]))
      +LTRIM(RTRIM([Tel2Kurz]))
      +LTRIM(RTRIM([Telefon3]))
      +LTRIM(RTRIM([Tel3Kurz]))
      +LTRIM(RTRIM([FaxNummer]))
      +LTRIM(RTRIM([FaxKurz]))
      +LTRIM(RTRIM([Selection1]))
      +LTRIM(RTRIM([Selection2]))
      +LTRIM(RTRIM([FibuKonto]))
      +LTRIM(RTRIM([HomePage]))
      +LTRIM(RTRIM([LandName]))
      +LTRIM(RTRIM([PsfPlz]))
      +LTRIM(RTRIM([Psf]))
      +LTRIM(RTRIM([PlzGrossEm]))
    AS Data
  FROM  [dbo].[ut_Adressen]
  WHERE (RecId > 0) AND ([DESTTYP] = ' 3')
  ORDER BY RecId
GO

ALTER PROCEDURE dbo.[up_TrSel_Bestellungen]
AS
  SELECT   RecId,
      LTRIM(RTRIM([PRJNR]))+'PROJEKT'
      +LTRIM(RTRIM([NRHW]))+'NRHW'
      +LTRIM(RTRIM([NRGH]))
      +LTRIM(RTRIM([STATUS]))
      +LTRIM(RTRIM([KDNRHW]))
      +LTRIM(RTRIM([KDNRGH]))
      +LTRIM(RTRIM([LKZ]))+'LIEFERANT'
      +LTRIM(RTRIM([BETREFF]))
      +LTRIM(RTRIM([TERMIN]))
      +LTRIM(RTRIM([WAEHR]))
      +LTRIM(RTRIM([BEARB]))
      +LTRIM(RTRIM([INFO1]))
      +LTRIM(RTRIM([INFO2]))
      +LTRIM(RTRIM([INFO3]))
      +LTRIM(RTRIM([INFO4]))
      +LTRIM(RTRIM([LADRNR]))
    AS Data
  FROM     dbo.[ut_Bestellungen]
  WHERE    RecId > 0
  ORDER BY RecId
GO

ALTER PROCEDURE [dbo].[up_TrSel_StammPakete]
AS
  SELECT   RecId,
      LTRIM(RTRIM([Gruppe]))
      +LTRIM(RTRIM([PktNr]))
      +LTRIM(RTRIM([LfdNr]))
      +LTRIM(RTRIM([Suchen]))
      +LTRIM(RTRIM([Txt1]))
      +LTRIM(RTRIM([Txt2]))
      +LTRIM(RTRIM([Txt3]))
      +LTRIM(RTRIM([ArtLkz]))
      +LTRIM(RTRIM([ArtNr]))
      +LTRIM(RTRIM([TxtLkz]))
      +LTRIM(RTRIM([TxtNr]))
    AS Data
  FROM     [dbo].[ut_StammPakete]
  WHERE    RecId > 0 AND LfdNr = '000'
  ORDER BY RecId
GO

ALTER PROCEDURE dbo.[up_TrSel_WartungsAnlagen]
AS
  SELECT   W.RecId,
       LTRIM(RTRIM(W.[Nr]))
      +LTRIM(RTRIM(W.[Bez1]))
      +LTRIM(RTRIM(W.[Bez2]))
      +LTRIM(RTRIM(W.[Uhrzeit]))
      +LTRIM(RTRIM(W.[Monteur]))
      +ISNULL((SELECT TOP 1 
                LTRIM(RTRIM(WT.[Bez1]))+LTRIM(RTRIM(WT.[Bez2]))+LTRIM(RTRIM(WT.[Nr]))+
                LTRIM(RTRIM(WT.[PktAnlage]))+LTRIM(RTRIM(WT.[PktWartung]))
               FROM dbo.[WartungsTypen] WT 
               WHERE WT.[Ident] = W.[AnlTyp]),'')
      +ISNULL((SELECT TOP 1 
                LTRIM(RTRIM(O.[Nr]))+LTRIM(RTRIM(O.[Bez1]))
               FROM dbo.[WartungsObjekte] O 
               WHERE O.[Ident] = W.[ObjektId]),'')
      +RIGHT('00'+LTRIM(RTRIM(STR(MONTH(W.[Naechste])))),2)+'/'+LTRIM(RTRIM(STR(YEAR(W.[Naechste]))))
      +LTRIM(RTRIM(STR(YEAR(W.[Naechste]))))
      +CASE
          WHEN MONTH(W.[Naechste]) = 1  AND YEAR(W.[Naechste]) <> 1900 THEN 'JANUARWARTUNG' 
          WHEN MONTH(W.[Naechste]) = 2  AND YEAR(W.[Naechste]) <> 1900 THEN 'FEBRUARWARTUNG' 
          WHEN MONTH(W.[Naechste]) = 3  AND YEAR(W.[Naechste]) <> 1900 THEN 'MÄRZWARTUNG' 
          WHEN MONTH(W.[Naechste]) = 4  AND YEAR(W.[Naechste]) <> 1900 THEN 'APRILWARTUNG' 
          WHEN MONTH(W.[Naechste]) = 5  AND YEAR(W.[Naechste]) <> 1900 THEN 'MAIWARTUNG' 
          WHEN MONTH(W.[Naechste]) = 6  AND YEAR(W.[Naechste]) <> 1900 THEN 'JUNIWARTUNG' 
          WHEN MONTH(W.[Naechste]) = 7  AND YEAR(W.[Naechste]) <> 1900 THEN 'JULIWARTUNG' 
          WHEN MONTH(W.[Naechste]) = 8  AND YEAR(W.[Naechste]) <> 1900 THEN 'AUGUSTWARTUNG' 
          WHEN MONTH(W.[Naechste]) = 9  AND YEAR(W.[Naechste]) <> 1900 THEN 'SEPTEMBERWARTUNG' 
          WHEN MONTH(W.[Naechste]) = 10 AND YEAR(W.[Naechste]) <> 1900 THEN 'OKTOBERWARTUNG' 
          WHEN MONTH(W.[Naechste]) = 11 AND YEAR(W.[Naechste]) <> 1900 THEN 'NOVEMBERWARTUNG' 
          WHEN MONTH(W.[Naechste]) = 12 AND YEAR(W.[Naechste]) <> 1900 THEN 'DEZEMBERWARTUNG' 
          ELSE ''
       END
      +CASE
          WHEN MONTH(W.[RechDatum]) = 1  AND YEAR(W.[RechDatum]) <> 1900 THEN 'JANUARABRECHNUNG' 
          WHEN MONTH(W.[RechDatum]) = 2  AND YEAR(W.[RechDatum]) <> 1900 THEN 'FEBRUARABRECHNUNG' 
          WHEN MONTH(W.[RechDatum]) = 3  AND YEAR(W.[RechDatum]) <> 1900 THEN 'MÄRZABRECHNUNG' 
          WHEN MONTH(W.[RechDatum]) = 4  AND YEAR(W.[RechDatum]) <> 1900 THEN 'APRILABRECHNUNG' 
          WHEN MONTH(W.[RechDatum]) = 5  AND YEAR(W.[RechDatum]) <> 1900 THEN 'MAIABRECHNUNG' 
          WHEN MONTH(W.[RechDatum]) = 6  AND YEAR(W.[RechDatum]) <> 1900 THEN 'JUNIABRECHNUNG' 
          WHEN MONTH(W.[RechDatum]) = 7  AND YEAR(W.[RechDatum]) <> 1900 THEN 'JULIABRECHNUNG' 
          WHEN MONTH(W.[RechDatum]) = 8  AND YEAR(W.[RechDatum]) <> 1900 THEN 'AUGUSTABRECHNUNG' 
          WHEN MONTH(W.[RechDatum]) = 9  AND YEAR(W.[RechDatum]) <> 1900 THEN 'SEPTEMBERABRECHNUNG' 
          WHEN MONTH(W.[RechDatum]) = 10 AND YEAR(W.[RechDatum]) <> 1900 THEN 'OKTOBERABRECHNUNG' 
          WHEN MONTH(W.[RechDatum]) = 11 AND YEAR(W.[RechDatum]) <> 1900 THEN 'NOVEMBERABRECHNUNG' 
          WHEN MONTH(W.[RechDatum]) = 12 AND YEAR(W.[RechDatum]) <> 1900 THEN 'DEZEMBERABRECHNUNG' 
          ELSE ''
       END
      +ISNULL((SELECT TOP 1 
                LTRIM(RTRIM(V.[Nr]))
               FROM dbo.[WartungsObjekte] OB 
               INNER JOIN dbo.[WartungsVertraege] V 
               ON OB.[VertragId] = V.[Ident]
               WHERE OB.[Ident] = W.[ObjektId]),'')
      +ISNULL((SELECT TOP 1 
                LTRIM(RTRIM(A.[DESTDEBNR]))+LTRIM(RTRIM(A.[Name1]))+LTRIM(RTRIM(A.[Name2]))+
                LTRIM(RTRIM(A.[Strasse]))+LTRIM(RTRIM(A.[Plz]))+LTRIM(RTRIM(A.[Ort]))
               FROM dbo.[WartungsObjekte] OB 
               INNER JOIN dbo.[Adressen] A 
               ON OB.[KdNr] = A.[DESTDEBNR]
               WHERE OB.[Ident] = W.[ObjektId]),'')
      +ISNULL((SELECT TOP 1 
                LTRIM(RTRIM(S.[DESTDEBNR]))+LTRIM(RTRIM(S.[Name1]))+LTRIM(RTRIM(S.[Name2]))+
                LTRIM(RTRIM(S.[Strasse]))+LTRIM(RTRIM(S.[Plz]))+LTRIM(RTRIM(S.[Ort]))
               FROM dbo.[WartungsObjekte] OB 
               INNER JOIN dbo.[Adressen] S 
               ON OB.[Standort] = S.[DESTDEBNR]
               WHERE OB.[Ident] = W.[ObjektId]),'')
      +ISNULL((SELECT TOP 1 
                LTRIM(RTRIM(M.[DESTDEBNR]))+LTRIM(RTRIM(M.[Name1]))+LTRIM(RTRIM(M.[Name2]))
               FROM dbo.[Adressen] M 
               WHERE M.[Annr] = W.[Monteur]),'')
      +ISNULL((SELECT TOP 1 
                LTRIM(RTRIM(MI.[DESTDEBNR]))+LTRIM(RTRIM(MI.[Name1]))+LTRIM(RTRIM(MI.[Name2]))
               FROM dbo.[Adressen] MI 
               WHERE MI.[DESTDEBNR] = W.[Mieter]),'')
      +ISNULL((SELECT TOP 1 
                LTRIM(RTRIM(R.[DESTDEBNR]))+LTRIM(RTRIM(R.[Name1]))+LTRIM(RTRIM(R.[Name2]))
               FROM dbo.[Adressen] R 
               WHERE R.[DESTDEBNR] = W.[KdNrRng]),'')
    AS Data
  FROM     dbo.[WartungsAnlagen] W
  WHERE    (W.RecId > 0) AND (W.Nr > '')
  ORDER BY W.RecId
GO

ALTER PROCEDURE dbo.[up_TrSel_WartungsObjekte]
AS
  SELECT   W.RecId,
       LTRIM(RTRIM(W.[Nr]))
      +LTRIM(RTRIM(W.[Bez1]))
      +LTRIM(RTRIM(W.[Bez2]))
      +ISNULL((SELECT TOP 1 
                LTRIM(RTRIM(V.[Nr]))
               FROM dbo.[WartungsVertraege] V 
               WHERE W.[VertragId] = V.[Ident]),'')
      +ISNULL((SELECT TOP 1 
                LTRIM(RTRIM(A.[DESTDEBNR]))+LTRIM(RTRIM(A.[Name1]))+LTRIM(RTRIM(A.[Name2]))+LTRIM(RTRIM(A.[Strasse]))+
                LTRIM(RTRIM(A.[Plz]))+LTRIM(RTRIM(A.[Ort]))
               FROM dbo.[Adressen] A 
               WHERE A.[DESTDEBNR] = W.[KdNr]),'')
      +ISNULL((SELECT TOP 1 
                LTRIM(RTRIM(S.[DESTDEBNR]))+LTRIM(RTRIM(S.[Name1]))+LTRIM(RTRIM(S.[Name2]))+LTRIM(RTRIM(S.[Strasse]))+
                LTRIM(RTRIM(S.[Plz]))+LTRIM(RTRIM(S.[Ort]))
               FROM dbo.[Adressen] S 
               WHERE W.[Standort] = S.[DESTDEBNR]),'')
    AS Data
  FROM     dbo.[WartungsObjekte] W
  WHERE    (W.RecId > 0) AND (Nr > '')
  ORDER BY W.RecId
GO

ALTER PROCEDURE dbo.[up_TrSel_WartungsMeldungen]
AS
  SELECT   W.RecId,
       LTRIM(RTRIM(W.[Monteur]))
      +LTRIM(RTRIM(W.[Bearbeiter]))
      +LTRIM(RTRIM(W.[Uhrzeit]))
      +LTRIM(RTRIM(W.[AnlagenNr]))
      +LTRIM(RTRIM(W.[AnlagenBez]))
      +LTRIM(RTRIM(W.[ObjektNr]))
      +LTRIM(RTRIM(W.[ObjektBez]))
      +LTRIM(RTRIM(W.[KdNr]))
      +LTRIM(RTRIM(W.[KdName]))
      +LTRIM(RTRIM(W.[Standort]))
      +LTRIM(RTRIM(W.[HausMstr]))
      +LTRIM(RTRIM(W.[Mieter]))
      +LTRIM(RTRIM(W.[SoName]))
      +LTRIM(RTRIM(W.[SoStrasse]))
      +LTRIM(RTRIM(W.[SoOrt]))
      +LTRIM(RTRIM(W.[SoPlz]))
      +LTRIM(RTRIM(W.[BelNr]))
      +LTRIM(RTRIM(W.[PrjNr]))
      +RIGHT('00'+LTRIM(RTRIM(STR(DAY(W.[Datum])))),2)+'/'+
       RIGHT('00'+LTRIM(RTRIM(STR(MONTH(W.[Datum])))),2)+'/'+
       LTRIM(RTRIM(STR(YEAR(W.[Datum]))))
      +ISNULL(CASE
                WHEN W.[Status] = 1 THEN 'WMS01'
                WHEN W.[Status] = 2 THEN 'WMS02'
                WHEN W.[Status] = 3 THEN 'WMS03'
                WHEN W.[Status] = 4 THEN 'WMS04'
                WHEN W.[Status] = 5 THEN 'WMS05'
                ELSE ''
              END,'')
      +ISNULL(CASE
               WHEN W.[Vorgemerkt] = 1 THEN 'VGM'
               ELSE ''
              END,'')
      +ISNULL((SELECT TOP 1  LTRIM(RTRIM(A.[Nr]))+
                             CASE A.[Art] WHEN 0 THEN 'WMF01' 
                                          WHEN 1 THEN 'WMF02'
                                          WHEN 2 THEN 'WMF03'
                             END
               FROM dbo.[WartungsAuftraege] A 
               WHERE A.[Ident] = W.[AuftragId]),'')
      +ISNULL((SELECT TOP 1 LTRIM(RTRIM(M.[DESTDEBNR]))+LTRIM(RTRIM(M.[Name1]))+LTRIM(RTRIM(M.[Name2]))
               FROM dbo.[Adressen] M 
               WHERE M.[Annr] = W.[Monteur]),'')
      +ISNULL((SELECT TOP 1 LTRIM(RTRIM(K.[Name1]))+LTRIM(RTRIM(K.[Name2]))
               FROM dbo.[Adressen] K 
               WHERE K.[DESTDEBNR] = W.[KdNr]),'')
      +ISNULL((SELECT TOP 1 CASE WHEN O.[Sammel] = 1 THEN 'WMF05' ELSE 'WMF04' END
               FROM dbo.[WartungsAnlagen] A
               INNER JOIN dbo.[WartungsObjekte] O
                       ON O.[Ident] = A.[ObjektId]
               WHERE A.[Ident] = W.[AnlagenId]),'')
      +ISNULL((SELECT TOP 1 V.[Nr]
               FROM dbo.[WartungsAnlagen] A
               INNER JOIN dbo.[WartungsObjekte] O
                       ON O.[Ident] = A.[ObjektId]
               INNER JOIN dbo.[WartungsVertraege] V
                       ON O.[VertragId] = V.[Ident]
               WHERE A.[Ident] = W.[AnlagenId]),'')
      AS Data
  FROM     dbo.[WartungsMeldungen] W
  WHERE    W.RecId > 0
  ORDER BY W.RecId
GO

ALTER PROCEDURE dbo.[up_TrSel_WartungsAuftraege]
AS
  SELECT   W.RecId,
       LTRIM(RTRIM(W.[Nr]))
      +LTRIM(RTRIM(W.[Monteur]))
      +LTRIM(RTRIM(W.[Betreff]))
      +CASE W.[Status]
         WHEN 1 THEN 'OFFEN'+'OOO'
         WHEN 2 THEN 'ERLEDIGT'+'EEE'
         WHEN 3 THEN 'ABGERECHNET'+'ABGESCHLOSSEN'+'FERTIG'+'AAA'
       END
      +ISNULL((SELECT TOP 1 
                LTRIM(RTRIM(O.[Nr]))+LTRIM(RTRIM(O.[Bez1]))+LTRIM(RTRIM(O.[Bez2]))
               FROM dbo.[WartungsObjekte] O 
               WHERE W.[ObjektId] = O.[Ident]),'')
      +ISNULL((SELECT TOP 1 
                LTRIM(RTRIM(AL.[Nr]))+LTRIM(RTRIM(AL.[Bez1]))+LTRIM(RTRIM(AL.[Bez2]))
               FROM dbo.[WartungsAnlagen] AL 
               WHERE W.[AnlagenId] = AL.[Ident]),'')
      +ISNULL((SELECT TOP 1 
                LTRIM(RTRIM(A.[DESTDEBNR]))+LTRIM(RTRIM(A.[Name1]))+LTRIM(RTRIM(A.[Name2]))+LTRIM(RTRIM(A.[Strasse]))+
                LTRIM(RTRIM(A.[Plz]))+LTRIM(RTRIM(A.[Ort]))
               FROM dbo.[WartungsObjekte] O1 
               INNER JOIN dbo.[Adressen] A 
                       ON A.[DESTDEBNR] = O1.[KdNr]
               WHERE W.[ObjektId] = O1.[Ident]),'')
      +ISNULL((SELECT TOP 1 
                LTRIM(RTRIM(S.[DESTDEBNR]))+LTRIM(RTRIM(S.[Name1]))+LTRIM(RTRIM(S.[Name2]))+LTRIM(RTRIM(S.[Strasse]))+
                LTRIM(RTRIM(S.[Plz]))+LTRIM(RTRIM(S.[Ort]))
               FROM dbo.[WartungsObjekte] O2 
               INNER JOIN dbo.[Adressen] S 
                       ON S.[DESTDEBNR] = O2.[Standort]
               WHERE W.[ObjektId] = O2.[Ident]),'')
    AS Data
  FROM     dbo.[WartungsAuftraege] W
  WHERE    W.Art = 2 AND W.RecId > 0
  ORDER BY W.RecId
GO

ALTER PROCEDURE dbo.[up_TrSel_LagerEinKopf]
AS
  SELECT   L.RecId,
       LTRIM(RTRIM(L.[BelNr]))
      +LTRIM(RTRIM(L.[PrjNr]))
      +LTRIM(RTRIM(L.[AdrNr]))
      +LTRIM(RTRIM(L.[UZ]))
      +LTRIM(RTRIM(L.[ExtBelnr]))
      +LTRIM(RTRIM(L.[KredKto]))
      +LTRIM(RTRIM(L.[Name1]))
      +LTRIM(RTRIM(L.[Waehrung]))
      +LTRIM(RTRIM(L.[TransNo]))
      +LTRIM(RTRIM(L.[Typ]))
      +RIGHT('00'+LTRIM(RTRIM(STR(DAY(L.[Datum])))),2)+'/'+
       RIGHT('00'+LTRIM(RTRIM(STR(MONTH(L.[Datum])))),2)+'/'+
       LTRIM(RTRIM(STR(YEAR(L.[Datum]))))
      +ISNULL(CASE
                WHEN L.[Typ] = 'Z' THEN 'ZUGANG'
                WHEN L.[Typ] = 'A' THEN 'ABGANG'
                ELSE ''
              END,'')
      +ISNULL((SELECT TOP 1 LTRIM(RTRIM(M.[DESTDEBNR]))+LTRIM(RTRIM(M.[VorName]))+
                            LTRIM(RTRIM(M.[Name1]))+LTRIM(RTRIM(M.[SUCHEN]))+LTRIM(RTRIM(M.[LkzImport]))
               FROM dbo.[Adressen] M 
               WHERE M.[DESTDEBNR] = L.[AdrNr]),'')
      +ISNULL((SELECT TOP 1 LTRIM(RTRIM(B.[VORHABEN]))+LTRIM(RTRIM(B.[BETREFF1]))+
                            LTRIM(RTRIM(B.[BETREFF2]))+LTRIM(RTRIM(B.[BETREFF3]))
               FROM dbo.[Belege] B 
               WHERE B.[PRJNR] = L.[PrjNr] AND B.[BELEGART] = ' 0' AND B.[BELEGNR] = L.[PrjNr]),'')
      +ISNULL((SELECT TOP 1 LTRIM(RTRIM(O.[NRHW]))+LTRIM(RTRIM(O.[BETREFF]))+LTRIM(RTRIM(O.[INFO1]))+
                            LTRIM(RTRIM(O.[INFO2]))+LTRIM(RTRIM(O.[INFO3]))+LTRIM(RTRIM(O.[INFO4]))
               FROM dbo.[Bestellungen] O 
               WHERE O.[NRHW] IN (SELECT P.NRHW FROM dbo.[LagerEinPos] P WHERE P.BelNr = L.BelNr)),'')
      AS Data
  FROM     dbo.[LagerEinKopf] L
  WHERE    L.RecId > 0
  ORDER BY L.RecId
GO

ALTER PROCEDURE dbo.[up_TrSel_ZahlBelege]
AS
  SELECT   Z.RecId,
       LTRIM(RTRIM(Z.[BELNR]))
      +LTRIM(RTRIM(Z.[PRJNR]))
      +LTRIM(RTRIM(Z.[ADRESSNR]))
      +LTRIM(RTRIM(Z.[BELDATUM]))
      +LTRIM(RTRIM(Z.[FDatum]))
      +LTRIM(RTRIM(Z.[SDatum]))
      +LTRIM(RTRIM(Z.[Name1]))
      +LTRIM(RTRIM(Z.[Vorhaben]))
      +LTRIM(RTRIM(Z.[DebKonto]))
      +LTRIM(RTRIM(Z.[Betreff1]))
      +CASE Z.[Status] WHEN 'G' THEN 'GGG' ELSE 'OOO' END
      +ISNULL((SELECT TOP 1 
                LTRIM(RTRIM(B.[VORHABEN]))+LTRIM(RTRIM(B.[BETREFF1]))+
                LTRIM(RTRIM(B.[BETREFF2]))+LTRIM(RTRIM(B.[BETREFF3]))
               FROM dbo.[Belege] B 
               WHERE B.[PRJNR]=Z.[PRJNR] AND B.[BELEGART]=Z.[BELART] AND B.[BELEGNR]=Z.[BELNR]),'')
      +ISNULL((SELECT TOP 1 
                LTRIM(RTRIM(BA.[DestDebNr]))+LTRIM(RTRIM(BA.[Suchen]))+
                LTRIM(RTRIM(BA.[Vorname]))+LTRIM(RTRIM(BA.[Name1]))+LTRIM(RTRIM(BA.[Name2]))+
                LTRIM(RTRIM(BA.[Branche]))+LTRIM(RTRIM(BA.[Strasse]))+LTRIM(RTRIM(BA.[Ort]))+
                LTRIM(RTRIM(BA.[Plz]))+LTRIM(RTRIM(BA.[zuHaenden]))+LTRIM(RTRIM(BA.[BrAnrede]))+
                LTRIM(RTRIM(BA.[BrGruss]))+LTRIM(RTRIM(BA.[Telefon1]))+LTRIM(RTRIM(BA.[Telefon2]))+
                LTRIM(RTRIM(BA.[FaxNummer]))+LTRIM(RTRIM(BA.[EMail]))+LTRIM(RTRIM(BA.[FibuKonto]))
               FROM dbo.[BelAdresse] BA 
               WHERE BA.[PrjNr]=Z.[PRJNR] AND BA.[BelArt]=Z.[BELART] AND BA.[BelNr]=Z.[BELNR] AND BA.[LfdNr]=0),'')
      AS Data
  FROM     dbo.[ZahlBelege] Z
  WHERE    Z.RecId > 0
  ORDER BY Z.RecId
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_TrSel_Lagerpositionen')
  DROP PROCEDURE [dbo].[up_TrSel_Lagerpositionen]
GO
CREATE PROCEDURE [dbo].[up_TrSel_Lagerpositionen]
AS
  SELECT RecId,
    LTRIM(RTRIM([Lkz]))
   +LTRIM(RTRIM([Artnr]))
   +LTRIM(RTRIM([Suchen]))
   +LTRIM(RTRIM([Bez1]))
   +LTRIM(RTRIM([Bez2]))
   +LTRIM(RTRIM([Merkmal]))
   +LTRIM(RTRIM([Textnr]))
   +LTRIM(RTRIM([Kzpos]))
   +LTRIM(RTRIM([Kzlager]))
   +LTRIM(RTRIM([Kzzubehoer]))
   +LTRIM(RTRIM([Bstnr]))
   +LTRIM(RTRIM([Katalogseite]))
   +LTRIM(RTRIM([Wargru]))
   +LTRIM(RTRIM([Rabgru]))
   +LTRIM(RTRIM([Lkzlager]))
   +LTRIM(RTRIM([Artnrlager]))
   +LTRIM(RTRIM([Lagerort]))
   +LTRIM(RTRIM([Lkztextnr]))
   +LTRIM(RTRIM([D6]))
   +LTRIM(RTRIM([Bildnummer]))
   +LTRIM(RTRIM([EANcode]))
   +LTRIM(RTRIM([Gruppe]))
   +LTRIM(RTRIM([D7]))
  AS Data 
  FROM [dbo].[ut_Stammpositionen] 
  WHERE RecId > 0 AND Kzlager = 'J'
  ORDER BY RecId
GO
