CREATE OR ALTER VIEW LD0VAA0Documents 
AS
SELECT   
T41.*,  
Q01.KLARTEXT ANTTYP_T,   
CASE WHEN EXISTS(SELECT 'X' FROM LD0P0C 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,
CASE WHEN Q01I.KLARTEXT IS NOT NULL THEN Q01I.KLARTEXT
WHEN Q01A.KLARTEXT IS NOT NULL THEN Q01A.KLARTEXT 
WHEN Q01E.KLARTEXT IS NOT NULL THEN Q01E.KLARTEXT
WHEN Q01S.KLARTEXT IS NOT NULL THEN Q01S.KLARTEXT
WHEN Q01B01.KLARTEXT IS NOT NULL THEN Q01B01.KLARTEXT
END AS AGARTYP_T,
CASE WHEN EXISTS ( SELECT 1 FROM LD0P0T45 T45 JOIN LD0VYPDF PDF ON T45.PARENTDOCUMENTID = PDF.DOKUMENTID  WHERE PDF.OBJEKTNYCKEL = CAST(T41.DOKUMENTNR AS VARCHAR(40)) AND PDF.OBJEKT = 'IFO.Dokument') THEN 1 ELSE 0 END AS HASREDACTEDPDFDOCUMENTS,
CASE WHEN Q01A.XNUM40 IS NOT NULL THEN Q01A.XNUM40 
     WHEN Q01I.XNUM40 IS NOT NULL THEN Q01I.XNUM40
     WHEN Q01S.XNUM40 IS NOT NULL THEN Q01S.XNUM40
     ELSE 0 END AS WORKFLOW_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  

LEFT JOIN LD0P0H A   
ON T41.AGARNR = A.AKTUALNR  AND A.AKTUALNR > 0 AND T41.AGARTYP = 20
LEFT JOIN LD0V0Q01 Q01A     
ON Q01A.KODTYP ='AKTUAL' AND Q01A.KOD = A.AKTUAL 
LEFT JOIN LD0P0C I
ON T41.AGARNR = I.AEVERKNR  AND I.AEVERKNR > 0 AND I.TMARK = 0  AND T41.AGARTYP IN (21,42,51)
LEFT JOIN LD0V0Q01 AS Q01I 
ON Q01I.KODTYP = 'UTREDTYP' AND Q01I.KOD = I.AEVERKTYP  
LEFT JOIN LD0P0C E   
ON T41.AGARNR = E.AEVERKNR  AND E.AEVERKNR > 0 AND T41.AGARTYP IN (22,42,52)
LEFT JOIN LD0V0Q01 AS Q01E 
ON Q01E.KODTYP = 'JOURNALTYP' AND Q01E.KOD = E.AEVERKTYP 
LEFT JOIN LD0P0C20 S   
ON T41.AGARNR = S.INSNR  AND S.INSNR > 0 AND T41.AGARTYP IN (23,41,53)
LEFT JOIN LD0V0Q01 AS Q01S 
ON Q01S.KODTYP = 'INSATS' AND Q01S.KOD = S.INSTYP 
LEFT JOIN LD0P0B B     
ON T41.AGARNR = B.AKTNR AND B.AKTNR > 0 
LEFT JOIN LD0V0Q01 AS Q01B01   
ON Q01B01.KODTYP = 'AKTTYP' AND Q01B01.KOD = B.TYPNR   
  
GO

GRANT ALL ON LD0VAA0Documents TO LD0ROANV
GO