DROP VIEW LD0V0CDOCUMENTS
;
CREATE VIEW LD0V0CDOCUMENTS
AS 
SELECT 
T41.DOKUMENTNR, 
T41.AGARNR, 
T41.AGARTYP, 
T41.DRUBRIK, 
T41.DOKTYP, 
T41.DELETED,
T41.ANTDOKTYP, 
T41.LOCKED, 
T41.LOCKDATE, 
T41.LOCKSIGN,
T41.HANDELSEDATE, 
T41.DDATUM, 
T41.LIFECARE_CREATED, 
T41.UPDDAT, 
UPPER(T41.UPDSIGN) AS UPDSIGN,
I.NAMN CASEWORKERNAME,
Q01_1.KLARTEXT NOTETITLE,
Q01_2.KLARTEXT DOCUMENTTITLE, 
Q01_2.XNUM4 BLANKETT,
T41.PERSONNR, 
A.NAMN AS PERSONNAME,
AKT.AEVERKTYP AKTUALTYP, 
Q1.KLARTEXT AKTUALTYP_T,
INV.AEVERKTYP INVTYP, 
Q2_2.KLARTEXT INVTYP_T, 
INV.TMARK,
INSATS.AEVERKTYP INSATSTYP,
Q3.KLARTEXT INSATSTYP_T,
ACT.TYPDOK ACTTYP,
Q4.KLARTEXT ACTTYP_T,
C20.AKTPNR SERV_PNR, 
A1.NAMN AS SERV_PERSONNAME,
A2.NAMN AS INV_PERSONNAME,
B.AKTPNR ACTNOTE_PNR, 
A3.NAMN AS ACTNOTE_PERSONNAME
FROM LD0V0T41 T41 
LEFT JOIN LD0V0Q01 Q01_1 
ON T41.ANTDOKTYP = Q01_1.KOD 
AND Q01_1.KODTYP = 'ANTTYP'
LEFT JOIN LD0V0Q01 Q01_2 
ON T41.DOKTYP = Q01_2.KOD 
AND Q01_2.KODTYP = 'TYPDOK' 
LEFT JOIN LD0V0H H 
ON H.AKTUALNR = T41.AGARNR 
AND T41.AGARTYP = 20
LEFT JOIN LD0VLC AKT 
ON AKT.AEVERKNR = H.AEVERKNR
LEFT JOIN LD0V0Q01 Q1 
ON Q1.KODTYP = 'AKTUAL' 
AND Q1.KOD = AKT.AEVERKTYP
LEFT JOIN LD0VLC INV 
ON INV.AEVERKNR = T41.AGARNR 
AND (T41.AGARTYP = 21 OR T41.AGARTYP = 22)		
LEFT JOIN LD0V0Q01 Q2_1 
ON Q2_1.KODTYP = 'UTREDTYP' 
AND Q2_1.KOD = INV.AEVERKTYP
LEFT JOIN LD0V0Q01 Q2_2 
ON Q2_2.KODTYP = 'AKTTYP' 
AND Q2_1.XNUM1 = Q2_2.KOD
LEFT JOIN LD0VLC20 C20 
ON C20.INSNR = T41.AGARNR 
AND T41.AGARTYP = 23
LEFT JOIN LD0VLC INSATS 
ON INSATS.AEVERKNR = C20.ARENDENR
LEFT JOIN LD0V0Q01 Q3 
ON Q3.KODTYP = 'INSATS' 
AND Q3.KOD = C20.INSTYP
LEFT JOIN LD0VYB01 ACT 
ON ACT.HAGNR = T41.DOKUMENTNR 
AND ACT.HANDELSE = 859 
AND T41.AGARTYP = 39
LEFT JOIN LD0VLC C 
ON C.AKTNR = ACT.AKTNR 
LEFT JOIN LD0V0Q01 Q4 
ON Q4.KODTYP = 'JOURNALTYP' 
AND Q4.KOD = C.AEVERKTYP 
LEFT JOIN LD0V0B B 
ON B.AKTNR = T41.AGARNR
LEFT JOIN LD0V0I I 
ON T41.UPDSIGN = I.HAND
LEFT JOIN LD0V0A A 
ON T41.PERSONNR = A.PNR
LEFT JOIN LD0V0A A1 
ON C20.AKTPNR = A1.PNR
LEFT JOIN LD0V0A A2 
ON INV.AKTPNR = A2.PNR
LEFT JOIN LD0V0A A3 
ON B.AKTPNR = A3.PNR
WHERE (T41. DOKTYP > 0 OR T41.ANTDOKTYP > 0)
AND T41.AGARTYP IN (20, 21, 22, 23, 39, 40, 41, 42, 45, 46, 47, 51, 52, 53)
;