DROP VIEW LD0V0CDOCUMENTS
;
CREATE VIEW LD0V0CDOCUMENTS
AS 
SELECT
		DOKUMENTNR, AGARNR, AGARTYP, DRUBRIK, DOKTYP, ANTDOKTYP,DOCUMENTTYPETEXT, DDATUM,
		DELETED, LOCKED, LOCKDATE, LOCKSIGN, HANDELSEDATE, UPDDAT,
		CASEWORKERID,NAMN AS CASEWORKERNAME,
		PERSONNR, PERSONNAME,	
		AEVERKTYP AS AKTUALTYPECODE, KLARTEXT AS AKTUALTYPETEXT, TMARK AS ISINVESTIGATIONORIMPLEMENTATION,
		LIFECARE_CREATED, BLANKETT
FROM
(
SELECT 
		T41.DOKUMENTNR, T41.AGARNR, T41.AGARTYP, T41.DRUBRIK, T41.DOKTYP, T41.ANTDOKTYP, AKTUAL.KLARTEXT AS DOCUMENTTYPETEXT , T41.DDATUM,
		T41.DELETED, T41.LOCKED, T41.LOCKDATE, T41.LOCKSIGN, T41.HANDELSEDATE, T41.UPDDAT,
		UPPER(T41.UPDSIGN) CASEWORKERID, CASEWORKER.NAMN,
		T41.PERSONNR, PERSON.NAMN AS PERSONNAME,
		AKT.AEVERKTYP, Q1.KLARTEXT, 0 AS TMARK,
		T41.LIFECARE_CREATED, AKTUAL.XNUM4 AS BLANKETT
FROM LD0V0T41 T41 
		INNER JOIN LD0V0H H ON H.AKTUALNR = T41.AGARNR AND T41.AGARTYP = 20 AND T41. DOKTYP > 0
		LEFT JOIN LD0VLC AKT ON AKT.AEVERKNR = H.AEVERKNR
		LEFT JOIN LD0V0Q01 Q1 ON Q1.KODTYP = 'AKTTYP' AND Q1.KOD = AKT.AEVERKTYP
		LEFT JOIN LD0V0Q01 AKTUAL ON T41.DOKTYP = AKTUAL.KOD AND AKTUAL.KODTYP = 'TYPDOK'
		LEFT JOIN LD0V0I CASEWORKER ON T41.UPDSIGN = CASEWORKER.HAND
		LEFT JOIN LD0V0A PERSON ON T41.PERSONNR = PERSON.PNR
UNION 
SELECT 
		T41.DOKUMENTNR, T41.AGARNR, T41.AGARTYP, T41.DRUBRIK, T41.DOKTYP, T41.ANTDOKTYP, AKTUAL.KLARTEXT AS DOCUMENTTYPETEXT , T41.DDATUM,
		T41.DELETED, T41.LOCKED, T41.LOCKDATE, T41.LOCKSIGN, T41.HANDELSEDATE, T41.UPDDAT,
		UPPER(T41.UPDSIGN) CASEWORKERID, CASEWORKER.NAMN,
		INV.AKTPNR, PERSON.NAMN AS PERSONNAME,
		INV.AEVERKTYP, Q2_2.KLARTEXT, INV.TMARK,
		T41.LIFECARE_CREATED, AKTUAL.XNUM4 AS BLANKETT
FROM LD0V0T41 T41 
		INNER JOIN LD0VLC INV ON INV.AEVERKNR = T41.AGARNR AND T41.AGARTYP IN (21,22) AND T41. DOKTYP > 0
		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 LD0V0Q01 AKTUAL ON T41.DOKTYP = AKTUAL.KOD AND AKTUAL.KODTYP = 'TYPDOK'
		LEFT JOIN LD0V0I CASEWORKER ON T41.UPDSIGN = CASEWORKER.HAND
		LEFT JOIN LD0V0A PERSON ON INV.AKTPNR = PERSON.PNR
UNION
SELECT 
		T41.DOKUMENTNR, T41.AGARNR, T41.AGARTYP, T41.DRUBRIK, T41.DOKTYP, T41.ANTDOKTYP, AKTUAL.KLARTEXT AS DOCUMENTTYPETEXT , T41.DDATUM,
		T41.DELETED, T41.LOCKED, T41.LOCKDATE, T41.LOCKSIGN, T41.HANDELSEDATE, T41.UPDDAT,
		UPPER(T41.UPDSIGN) CASEWORKERID, CASEWORKER.NAMN,
		C20.AKTPNR, PERSON.NAMN AS PERSONNAME,
		INSATS.AEVERKTYP,Q3.KLARTEXT, 0 AS TMARK,
		T41.LIFECARE_CREATED, AKTUAL.XNUM4 AS BLANKETT
FROM LD0V0T41 T41 
		INNER JOIN LD0VLC20 C20 ON C20.INSNR = T41.AGARNR AND T41.AGARTYP = 23 AND T41. DOKTYP > 0
		LEFT JOIN LD0VLC INSATS ON INSATS.AEVERKNR = C20.VERKNR
		LEFT JOIN LD0V0B B1 ON INSATS.AKTNR = B1.AKTNR
		LEFT JOIN LD0V0Q01 Q3 ON Q3.KODTYP = 'AKTTYP' AND Q3.KOD = B1.TYPNR
		LEFT JOIN LD0V0Q01 AKTUAL ON T41.DOKTYP = AKTUAL.KOD AND AKTUAL.KODTYP = 'TYPDOK'
		LEFT JOIN LD0V0I CASEWORKER ON T41.UPDSIGN = CASEWORKER.HAND
		LEFT JOIN LD0V0A PERSON ON C20.AKTPNR = PERSON.PNR
UNION
SELECT 
		T41.DOKUMENTNR, T41.AGARNR, T41.AGARTYP, T41.DRUBRIK, T41.DOKTYP, T41.ANTDOKTYP, AKTUAL.KLARTEXT AS DOCUMENTTYPETEXT , T41.DDATUM,
		T41.DELETED, T41.LOCKED, T41.LOCKDATE, T41.LOCKSIGN, T41.HANDELSEDATE, T41.UPDDAT,
		UPPER(T41.UPDSIGN) CASEWORKERID, CASEWORKER.NAMN,
		T41.PERSONNR, PERSON.NAMN AS PERSONNAME,
		ACT.TYPDOK,Q4.KLARTEXT, 0 AS TMARK,
		T41.LIFECARE_CREATED, AKTUAL.XNUM4 AS BLANKETT
FROM LD0V0T41 T41 
		INNER JOIN LD0VYB01 ACT ON ACT.HAGNR = T41.DOKUMENTNR AND ACT.HANDELSE = 859 AND T41.AGARTYP = 39 AND T41. DOKTYP > 0
		LEFT JOIN LD0V0B B ON B.AKTNR = ACT.AKTNR
		LEFT JOIN LD0V0Q01 Q4 ON Q4.KODTYP = 'AKTTYP' AND Q4.KOD = B.TYPNR
		LEFT JOIN LD0V0Q01 AKTUAL ON T41.DOKTYP = AKTUAL.KOD AND AKTUAL.KODTYP = 'TYPDOK'
		LEFT JOIN LD0V0I CASEWORKER ON T41.UPDSIGN = CASEWORKER.HAND
		LEFT JOIN LD0V0A PERSON ON T41.PERSONNR = PERSON.PNR
UNION
SELECT 
		T41.DOKUMENTNR, T41.AGARNR, T41.AGARTYP, T41.DRUBRIK, T41.DOKTYP, T41.ANTDOKTYP, AKTUAL.KLARTEXT AS DOCUMENTTYPETEXT , T41.DDATUM,
		T41.DELETED, T41.LOCKED, T41.LOCKDATE, T41.LOCKSIGN, T41.HANDELSEDATE, T41.UPDDAT,
		UPPER(T41.UPDSIGN) CASEWORKERID, CASEWORKER.NAMN,
		INV.AKTPNR, PERSON.NAMN AS PERSONNAME,
		INV.AEVERKTYP, Q2_2.KLARTEXT, INV.TMARK,
		T41.LIFECARE_CREATED, AKTUAL.XNUM4 AS BLANKETT
FROM LD0V0T41 T41 
		INNER JOIN LD0V0C11 C11 ON C11.HAGNR = T41.DOKUMENTNR AND C11.HANDELSE = 320 AND T41.AGARTYP IN (41, 42) AND T41.ANTDOKTYP > 0
		INNER JOIN LD0VLC INV ON INV.AEVERKNR = C11.AEVERKNR
		INNER JOIN LD0V0B B on INV.AKTNR = B.AKTNR		
		LEFT JOIN LD0V0Q01 AKTUAL ON T41.ANTDOKTYP = AKTUAL.KOD AND AKTUAL.KODTYP = 'ANTTYP'		
		LEFT JOIN LD0V0Q01 Q2_2 ON Q2_2.KOD = B.TYPNR AND Q2_2.KODTYP = 'AKTTYP'
		LEFT JOIN LD0V0I CASEWORKER ON T41.UPDSIGN = CASEWORKER.HAND
		INNER JOIN LD0V0A PERSON ON INV.AKTPNR = PERSON.PNR
UNION
SELECT 
		T41.DOKUMENTNR, T41.AGARNR, T41.AGARTYP, T41.DRUBRIK, T41.DOKTYP, T41.ANTDOKTYP, AKTUAL.KLARTEXT AS DOCUMENTTYPETEXT , T41.DDATUM,
		T41.DELETED, T41.LOCKED, T41.LOCKDATE, T41.LOCKSIGN, T41.HANDELSEDATE, T41.UPDDAT,
		UPPER(T41.UPDSIGN) CASEWORKERID, CASEWORKER.NAMN,
		B.AKTPNR, PERSON.NAMN AS PERSONNAME,
		B.TYPNR,Q2.KLARTEXT,0 AS TMARK,
		T41.LIFECARE_CREATED, AKTUAL.XNUM4 AS BLANKETT
FROM LD0V0T41 T41 
		INNER JOIN LD0VYB01 NOTE ON NOTE.HAGNR = T41.DOKUMENTNR AND NOTE.HANDELSE = 320 AND T41.ANTDOKTYP > 0
		INNER JOIN LD0V0B B ON B.AKTNR = NOTE.AKTNR
		LEFT JOIN LD0V0Q01 AKTUAL ON T41.ANTDOKTYP = AKTUAL.KOD AND AKTUAL.KODTYP = 'ANTTYP'
		LEFT JOIN LD0V0Q01 Q2 ON B.TYPNR = Q2.KOD AND Q2.KODTYP = 'AKTTYP'
		
		LEFT JOIN LD0V0I CASEWORKER ON T41.UPDSIGN = CASEWORKER.HAND
		INNER JOIN LD0V0A PERSON ON B.AKTPNR = PERSON.PNR
		
UNION
SELECT 
		T41.DOKUMENTNR, T41.AGARNR, T41.AGARTYP, T41.DRUBRIK, T41.DOKTYP, T41.ANTDOKTYP, AKTUAL.KLARTEXT AS DOCUMENTTYPETEXT , T41.DDATUM,
		T41.DELETED, T41.LOCKED, T41.LOCKDATE, T41.LOCKSIGN, T41.HANDELSEDATE, T41.UPDDAT,
		UPPER(T41.UPDSIGN) CASEWORKERID, CASEWORKER.NAMN,
		T41.PERSONNR, PERSON.NAMN AS PERSONNAME,
		NULL,NULL, 0 AS TMARK,
		T41.LIFECARE_CREATED, AKTUAL.XNUM4 AS BLANKETT
FROM LD0V0T41 T41 	
		INNER JOIN LD0V0Q01 AKTUAL ON T41.DOKTYP = AKTUAL.KOD AND AKTUAL.KODTYP = 'TYPDOK'
		AND T41.AGARTYP IN (40,46, 47, 51, 52, 53) AND T41. DOKTYP > 0
		
		LEFT JOIN LD0V0I CASEWORKER ON T41.UPDSIGN = CASEWORKER.HAND
		LEFT JOIN LD0V0A PERSON ON T41.PERSONNR = PERSON.PNR
)
AS DOCUMENTS
;