IF NOT EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'FONT'
          AND Object_ID = Object_ID(N'LD0P0T41'))
BEGIN
ALTER TABLE LD0P0T41
  ADD FONT VARCHAR(50) 
  CONSTRAINT LD0D0T41_FONT DEFAULT ' ' NOT NULL
END
;

IF NOT EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'FONTSIZE'
          AND Object_ID = Object_ID(N'LD0P0T41'))
BEGIN
ALTER TABLE LD0P0T41
  ADD FONTSIZE DECIMAL(9) 
  CONSTRAINT LD0D0T41_FONTSIZE DEFAULT 0 NOT NULL
END
;

IF NOT EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'SAVENAMETITEL'
          AND Object_ID = Object_ID(N'LD0P0T41'))
BEGIN
ALTER TABLE LD0P0T41
  ADD SAVENAMETITEL DECIMAL(1) 
  CONSTRAINT LD0D0T41_SAVENAMETITEL DEFAULT 0 NOT NULL
END
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0T41')
BEGIN
	DROP VIEW LD0V0T41
END
;

CREATE VIEW LD0V0T41
	AS SELECT * FROM LD0P0T41
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1T41')
BEGIN
	DROP VIEW LD0V1T41
END
;

CREATE VIEW LD0V1T41
AS SELECT * FROM LD0P0T41 T41
WHERE EXISTS (SELECT 'X' FROM LD0V0C C
		WHERE C.AEVERKNR = T41.AGARNR AND
		      T41.AGARTYP = 21)
   OR EXISTS (SELECT 'X' FROM LD0V0C C 
		WHERE C.AEVERKNR = T41.AGARNR AND
		      T41.AGARTYP = 22)
   OR EXISTS (SELECT 'X' FROM LD0V0C20 C20 
		WHERE C20.INSNR = T41.AGARNR AND
		      T41.AGARTYP = 23)
   OR EXISTS (SELECT 'X' FROM LD0V0H H 
		WHERE H.AKTUALNR = T41.AGARNR AND
		      T41.AGARTYP = 20)
   OR EXISTS (SELECT 'X' FROM LD0V0L L 
   		WHERE L.RESURSNR = T41.AGARNR AND
		      T41.AGARTYP = 38)
   OR EXISTS (SELECT 'X' FROM LD0V0L L 
		WHERE L.UPPDRNR = T41.AGARNR AND
		      T41.AGARTYP = 25)
   OR EXISTS (SELECT 'X' FROM LD0V0A70 A70 
		WHERE A70.FLYKTNR = T41.AGARNR AND
		      T41.AGARTYP = 40)
   OR EXISTS (SELECT 'X' FROM LD0V0A700 A700 
		WHERE A700.FLYKTNR = T41.AGARNR AND
		      T41.AGARTYP = 40)
   OR EXISTS (SELECT 'X' FROM LD0V0B B 
		WHERE B.AKTNR = T41.AGARNR AND
		      T41.AGARTYP = 39)
   OR EXISTS (SELECT 'X' FROM LD0V0E30 E30 
		WHERE E30.ATERLNR = T41.AGARNR AND
		      T41.AGARTYP = 44)
   OR EXISTS (SELECT 'X' FROM LD0V0U10 U10 
		WHERE U10.INSKRIVNINGSNR = T41.AGARNR AND
		      T41.AGARTYP=30)
   OR EXISTS (SELECT 'X' FROM LD0V0U30 U30 
		WHERE U30.PLANNR = T41.AGARNR AND
		      T41.AGARTYP=32)
   OR EXISTS (SELECT 'X' FROM LD0V1U60 U60 
		WHERE U60.PRAKPROJNR = T41.AGARNR AND
		      T41.AGARTYP=33)
   OR EXISTS (SELECT 'X' FROM LD0V1U60 U60 
		WHERE U60.PRAKPROJNR = T41.AGARNR AND
		      T41.AGARTYP=34)
   OR EXISTS (SELECT 'X' FROM LD0V0N90 N90 
		WHERE N90.UPPDRAGNR = T41.AGARNR AND
		      T41.AGARTYP=46)
   OR EXISTS (SELECT 'X' FROM LD0V0L L 
		WHERE L.RESURSNR = T41.AGARNR AND
			  T41.AGARTYP = 47)
   OR EXISTS (SELECT 'X' FROM LD0V0U90 U90 
		WHERE U90.LNUPPDRAGNR = T41.AGARNR AND
		      T41.AGARTYP=48)
   OR EXISTS (SELECT 'X' FROM LD0V0C C
		WHERE C.AEVERKNR = T41.AGARNR AND
		      T41.AGARTYP = 42)
   OR EXISTS (SELECT 'X' FROM LD0V0C20 C20 
		WHERE C20.INSNR = T41.AGARNR AND
		      T41.AGARTYP = 41)
   OR EXISTS (SELECT 'X' FROM LD0V0B B 
		WHERE B.AKTNR = T41.AGARNR AND
		      T41.AGARTYP = 45)
   OR EXISTS (SELECT 'X' FROM LD0V0H H 
		WHERE H.AKTUALNR = T41.AGARNR AND
		      T41.AGARTYP = 60)
   OR EXISTS (SELECT 'X' FROM LD0V0C C 
		WHERE C.AEVERKNR = T41.AGARNR AND
		      T41.AGARTYP = 61)
   OR EXISTS (SELECT 'X' FROM LD0V0U01 U01
		WHERE U01.KARTLAGGNR = T41.AGARNR AND
		      T41.AGARTYP = 62)
   OR EXISTS (SELECT 'X' FROM LD0V0B B 
		WHERE B.AKTNR = T41.AGARNR AND
		      T41.AGARTYP = 50)
   OR EXISTS (SELECT 'X' FROM LD0V0C C 
		WHERE C.AEVERKNR = T41.AGARNR AND
		      T41.AGARTYP = 51)
   OR EXISTS (SELECT 'X' FROM LD0V0C C 
		WHERE C.AEVERKNR = T41.AGARNR AND
		      T41.AGARTYP = 52)
   OR EXISTS (SELECT 'X' FROM LD0V0C20 C20 
		WHERE C20.INSNR = T41.AGARNR AND
		      T41.AGARTYP = 53)
   OR EXISTS (SELECT 'X' FROM LD0V0N900 N900 
		WHERE N900.UPPDRAGIFONR = T41.AGARNR AND
		      T41.AGARTYP = 63)
   OR EXISTS (SELECT 'X' FROM LD0V0U34 U34 
		WHERE U34.PLANAKTIVITETNR = T41.AGARNR AND
		      T41.AGARTYP = 64)
   OR EXISTS (SELECT 'X' FROM LD0V0U70 U70 
		WHERE U70.ENKELPLATSNR = T41.AGARNR AND
		      T41.AGARTYP = 66)
   OR EXISTS (SELECT 'X' FROM LD0V0N900 N900 
		WHERE N900.UPPDRAGIFONR = T41.AGARNR AND
		      T41.AGARTYP = 68)
   OR EXISTS (SELECT 'X' FROM LD0V0U90 U90 
		WHERE U90.LNUPPDRAGNR = T41.AGARNR AND
		      T41.AGARTYP=69)
   OR EXISTS (SELECT 'X' FROM LD0V0U10 U10 
		WHERE U10.INSKRIVNINGSNR = T41.AGARNR AND
		      T41.AGARTYP=70)
   OR EXISTS (SELECT 'X' FROM LD0V0U35 U35 
		WHERE U35.AKTIVITETNR = T41.AGARNR AND
		      T41.AGARTYP=71)
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V2T41')
BEGIN
	DROP VIEW LD0V2T41
END
;

CREATE VIEW LD0V2T41
	AS SELECT * FROM LD0P0T41
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V3T41')
BEGIN
	DROP VIEW LD0V3T41
END
;

CREATE VIEW LD0V3T41
AS SELECT * FROM LD0P0T41 T41
WHERE EXISTS (SELECT 'X' FROM LD0V0C C
		WHERE C.AEVERKNR = T41.AGARNR AND
		      T41.AGARTYP = 21)
   OR EXISTS (SELECT 'X' FROM LD0V0C C 
		WHERE C.AEVERKNR = T41.AGARNR AND
		      T41.AGARTYP = 22)
   OR EXISTS (SELECT 'X' FROM LD0V0C20 C20 
		WHERE C20.INSNR = T41.AGARNR AND
		      T41.AGARTYP = 23)
   OR EXISTS (SELECT 'X' FROM LD0V0H H 
		WHERE H.AKTUALNR = T41.AGARNR AND
		      T41.AGARTYP = 20)
   OR EXISTS (SELECT 'X' FROM LD0V0L L 
   		WHERE L.RESURSNR = T41.AGARNR AND
		      T41.AGARTYP = 38)
   OR EXISTS (SELECT 'X' FROM LD0V0L L 
		WHERE L.UPPDRNR = T41.AGARNR AND
		      T41.AGARTYP = 25)
   OR EXISTS (SELECT 'X' FROM LD0V0A700 A700 
		WHERE A700.FLYKTNR = T41.AGARNR AND
		      T41.AGARTYP = 40)
   OR EXISTS (SELECT 'X' FROM LD0V0B B 
		WHERE B.AKTNR = T41.AGARNR AND
		      T41.AGARTYP = 39)
   OR EXISTS (SELECT 'X' FROM LD0V0E30 E30 
		WHERE E30.ATERLNR = T41.AGARNR AND
		      T41.AGARTYP = 44)
   OR EXISTS (SELECT 'X' FROM LD0V0U10 U10 
		WHERE U10.INSKRIVNINGSNR = T41.AGARNR AND
		      T41.AGARTYP=30)
   OR EXISTS (SELECT 'X' FROM LD0V2N90 N90 
		WHERE N90.UPPDRAGNR = T41.AGARNR AND
		      T41.AGARTYP=46)
   OR EXISTS (SELECT 'X' FROM LD0V0N900 N900 
		WHERE N900.UPPDRAGIFONR = T41.AGARNR AND
		      T41.AGARTYP=63)
   OR EXISTS (SELECT 'X' FROM LD0V0L L 
		WHERE L.RESURSNR = T41.AGARNR AND
			  T41.AGARTYP = 47)
   OR EXISTS (SELECT 'X' FROM LD0V0C C
		WHERE C.AEVERKNR = T41.AGARNR AND
		      T41.AGARTYP = 42)
   OR EXISTS (SELECT 'X' FROM LD0V0C C
		WHERE C.AEVERKNR = T41.AGARNR AND
		      T41.AGARTYP = 51)
   OR EXISTS (SELECT 'X' FROM LD0V0C C
		WHERE C.AEVERKNR = T41.AGARNR AND
		      T41.AGARTYP = 52)
   OR EXISTS (SELECT 'X' FROM LD0V0C20 C20 
		WHERE C20.INSNR = T41.AGARNR AND
		      T41.AGARTYP = 53)
   OR EXISTS (SELECT 'X' FROM LD0V0C20 C20 
		WHERE C20.INSNR = T41.AGARNR AND
		      T41.AGARTYP = 41)
   OR EXISTS (SELECT 'X' FROM LD0V0B B 
		WHERE B.AKTNR = T41.AGARNR AND
		      T41.AGARTYP = 45)
   OR EXISTS (SELECT 'X' FROM LD0V0H H 
		WHERE H.AKTUALNR = T41.AGARNR AND
		      T41.AGARTYP = 60)
   OR EXISTS (SELECT 'X' FROM LD0V0C C 
		WHERE C.AEVERKNR = T41.AGARNR AND
		      T41.AGARTYP = 61)
   OR EXISTS (SELECT 'X' FROM LD0V0U01 U01
		WHERE U01.KARTLAGGNR = T41.AGARNR AND
		      T41.AGARTYP = 62)
   OR EXISTS (SELECT 'X' FROM LD0V0U90 U90 
		WHERE U90.LNUPPDRAGNR = T41.AGARNR AND
		      T41.AGARTYP=48)
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='GA1V0T41')
BEGIN
	DROP VIEW GA1V0T41
END
;

CREATE VIEW GA1V0T41 
	AS SELECT * FROM LD0P0T41
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0Documents')
BEGIN
	DROP VIEW LD0VAA0Documents
END
;

CREATE VIEW LD0VAA0Documents
AS
SELECT
T41.*,
Q01.KLARTEXT ANTTYP_T, 
CASE WHEN EXISTS(SELECT 'X' FROM LD0VYC C WHERE C.AEVERKNR = T41.AGARNR AND T41.AGARTYP = 42 AND C.TMARK = 1) THEN 1 ELSE 0 END AS ANTTYP_TMARK,
Q01B.KLARTEXT DOKTYP_T, 
Q01B.XNUM4 BLANKETT,
Q01C.KLARTEXT LNDOKTYP_T,
Q01D.KLARTEXT ANTECKNTYP_T
FROM LD0V1T41 T41 
LEFT JOIN LD0V0Q01 Q01 
ON T41.ANTDOKTYP = Q01.KOD AND Q01.KODTYP = 'ANTTYP'
AND T41.ANTDOKTYP > 0
LEFT JOIN LD0V0Q01 Q01B
ON T41.DOKTYP = Q01B.KOD AND Q01B.KODTYP = 'TYPDOK' 
AND T41.DOKTYP > 0
LEFT JOIN LD0V0Q01 Q01C
ON T41.LNDOKTYP = Q01C.KOD AND Q01C.KODTYP = 'LNDOKTYP' 
AND T41.LNDOKTYP > 0
LEFT JOIN LD0V0Q01 Q01D
ON T41.ANTECKNTYP = Q01D.KOD AND Q01D.KODTYP = 'ANTECKNTYP' 
AND T41.ANTECKNTYP > 0
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAAYDocuments')
BEGIN
	DROP VIEW LD0VAAYDocuments
END
;

CREATE VIEW LD0VAAYDocuments
AS
SELECT
T41.*,
Q01.KLARTEXT ANTTYP_T, 
CASE WHEN EXISTS(SELECT 'X' FROM LD0VYC C WHERE C.AEVERKNR = T41.AGARNR AND T41.AGARTYP = 42 AND C.TMARK = 1) THEN 1 ELSE 0 END AS ANTTYP_TMARK,
Q01B.KLARTEXT DOKTYP_T, 
Q01B.XNUM4 BLANKETT,
Q01C.KLARTEXT LNDOKTYP_T,
Q01D.KLARTEXT ANTECKNTYP_T
FROM LD0V0T41 T41 
LEFT JOIN LD0V0Q01 Q01 
ON T41.ANTDOKTYP = Q01.KOD AND Q01.KODTYP = 'ANTTYP'
AND T41.ANTDOKTYP > 0
LEFT JOIN LD0V0Q01 Q01B
ON T41.DOKTYP = Q01B.KOD AND Q01B.KODTYP = 'TYPDOK' 
AND T41.DOKTYP > 0
LEFT JOIN LD0V0Q01 Q01C
ON T41.LNDOKTYP = Q01C.KOD AND Q01C.KODTYP = 'LNDOKTYP' 
AND T41.LNDOKTYP > 0
LEFT JOIN LD0V0Q01 Q01D
ON T41.ANTECKNTYP = Q01D.KOD AND Q01D.KODTYP = 'ANTECKNTYP' 
AND T41.ANTECKNTYP > 0
;








