IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VJC')
BEGIN
DROP VIEW LD0VJC
END
;
CREATE VIEW LD0VJC AS 
SELECT C.STARTDAT,
CASE WHEN C.AEVERKNR > 0 THEN (SELECT MIN(E.DATUM)	FROM LD0P0E E 
													JOIN LD0V0Q01 Q01 
													ON E.BESLUT = Q01.KOD 
													WHERE E.STATUS = 0 
													and E.AEVERKNR = C.AEVERKNR 
													AND Q01.KODTYP = 'BESLUT' 
													AND Q01.XNUM2 <> 0
													AND Q01.XNUM37<>0) ELSE ' ' END AS GRUNDBESLUTDATUM,
(SELECT MIN(C20.FOMDAT) FROM	LD0P0C20 C20 
								JOIN LD0P0E E 
								ON E.STATUS = 0 
								and E.INSNR=C20.INSNR 
								AND E.INSNR > 0 
								AND E.AEVERKNR>0 
								JOIN LD0V0Q01 Q01 
								ON Q01.KOD=E.BESLUT 
								AND Q01.KODTYP='BESLUT' 
								AND Q01.XNUM2<>0 
								WHERE E.AEVERKNR=C.AEVERKNR 
								AND C.AEVERKNR>0 
								AND E.DATUM IN (SELECT MIN(DATUM) FROM	LD0P0E EB 
																		JOIN LD0V0Q01 Q01B 
																		ON EB.STATUS = 0 
																		and EB.BESLUT = Q01B.KOD 
																		AND Q01B.KODTYP = 'BESLUT' 
																		AND Q01B.XNUM2 <> 0 WHERE EB.AEVERKNR=C.AEVERKNR )) AS INSATSDATUM,
(SELECT MIN(N70.FOMDAT) FROM LD0P0N70 N70 
						JOIN LD0P0E E 
						ON E.STATUS = 0 
						and E.INSNR=N70.INSNR 
						AND E.AEVERKNR>0 
						AND E.INSNR > 0 
						JOIN LD0V0Q01 Q01 
						ON Q01.KOD=E.BESLUT 
						AND Q01.KODTYP='BESLUT' 
						AND Q01.XNUM2<>0 
						WHERE E.status = 0 
						and E.AEVERKNR=C.AEVERKNR 
						AND C.AEVERKNR>0 
						AND E.DATUM IN (SELECT MIN(DATUM) 
											FROM LD0P0E EB 
											JOIN LD0V0Q01 Q01B 
											ON EB.STATUS = 0 
											AND EB.BESLUT = Q01B.KOD 
											AND Q01B.KODTYP = 'BESLUT' 
											AND Q01B.XNUM2 <> 0 
											WHERE EB.AEVERKNR=C.AEVERKNR )) AS RESTILLDNRDATUM,
											C.STOPDAT,C.AEVERKNR,C.ORGID,C.TMARK,C.KEDJENR,C.AKTNR,C.AKTPNR,C.HAND,C.MEDHAND,C.ORGENHNR,C.AEVERKTYP,C.FMMSALDO,C.UPDDAT,C.UPDSIGN,
C.SAMARBRES,
C.ORGENHNR2,
C.FAMRTYP1,
C.FAMRTYP2,
C.FAMRTYP3,
C.AVSLORSAK,
C.FAMRATTNR,
C.STATUS,
C.FAMAVSLORS,
C.AVTALVAR,
C.AVTVFAST,
C.AVTVANSV,
C.AVTVDOM,
C.AVTALUMG,
C.AVTUFAST,
C.AVTUDOM,
C.AVTALBOE,
C.AVTBFAST,
C.AVTBOENDE,
C.AVTBDOM,
C.AVTALUH,
C.AVTUHFAST,
C.AVTEJFAST,
C.AVTUHBEL,
C.AVTUHENG,
C.FAMHANRACK,
C.FAMUTLAND,
C.FAMBOENDE,
C.FAMDNA,
C.FAMCIVST,
C.FAMFLERTAL,
C.FAMBETALARE,
C.FAMTEMPAVTAL,
C.FAMUTBAVTAL,
C.AVTSAMMANBOENDE,
C.CASEID,
A.NAMN,
A.KUNDNUMMER,
I.NAMN HANDLAGGARE_NAMN
FROM LD0VLC C 
JOIN LD0V0Q01 Q 
ON Q.KOD = C.AEVERKTYP 
AND Q.KODTYP = 'UTREDTYP' 
AND (Q.XNUM22 > 0 OR Q.XNUM23 > 0 OR Q.XNUM24 > 0 OR Q.XNUM25 > 0) AND TMARK = 0
JOIN LD0V0A A
ON A.PNR=C.AKTPNR
JOIN LD0P0I I
ON I.HAND=C.HAND
;
