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	LD0VLC20 C20 
								JOIN LD0V0E 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 LD0V0N70 N70 
						JOIN LD0V0E 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)
JOIN LD0V0A A
ON A.PNR=C.AKTPNR
JOIN LD0P0I I
ON I.HAND=C.HAND
;
IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VJE')
BEGIN
DROP VIEW LD0VJE
END
;
CREATE VIEW LD0VJE AS
SELECT E.DATUM AS GRUNDBESLUTDATUM,

(SELECT MIN(C20.FOMDAT) FROM LD0VLC20 C20 
          WHERE E.INSNR = C20.INSNR 
          AND E.INSNR > 0)
          AS INSATSDATUM,

(SELECT MIN(N70.FOMDAT) FROM LD0V0N70 N70 
          WHERE E.INSNR=N70.INSNR 
          AND E.INSNR > 0 
          ) AS RESTILLDNRDATUM,

C.STOPDAT AS STOPDAT,
C.AKTPNR AS PNR,

E.BESLUTNR,
E.INSNR,
E.AEVERKNR,
E.BESLNR2,
E.BESLUT,
E.BESLTYP,
E.FOMDAT,
E.TOMDAT,
E.ORSAK,
E.BESFATT,
E.BELOPP,
E.UPDSIGN,
E.UPDDAT,
E.REGDAT,
C.ORGID,
C.ORGENHNR,
E.VHAND,
E.REFBESLUTNR,
E.STATUS,
C.HAND,
C.MEDHAND,
E.ORSAKMED,
E.SPARRDAT,
E.SPARRSIGN,
E.SPARRORS,
E.ORGENHNR2,
E.ORSMEDSAKN,
E.BESLNRAR,
E.BESLNRLNR,
E.MAKDAT,
E.MAKSIGN,
E.STATORS1,
E.STATORS2,
E.STATORS3,
E.STATORS4,
E.STATORS5,
E.STATORS6,
E.STATORS7,
E.STATORS8,
E.BESFATTORG,
E.INSTNR,
E.UPDTID,
E.FORFAVSER,
E.VERKSEJ,
E.INSATGARD,
E.INSATGARD2,
E.GRUND,
E.KOMPL,
E.ARBERS,
E.RESERS,
E.AKTUALNR,
E.ORSAKSEN,
A.NAMN AS NAMN,
A.KUNDNUMMER,
I1.NAMN BESFATT_NAMN,
I2.NAMN HANDLAGGARE_NAMN
FROM LD0V0E E 
JOIN LD0V0Q01 Q 
ON Q.KOD = E.BESLUT 
AND Q.KODTYP = 'BESLUT' 
AND Q.XNUM2 <> 0 
AND (Q.XNUM36 > 0 OR Q.XNUM37 > 0 OR Q.XNUM38 > 0 OR Q.XNUM39 > 0)
JOIN LD0VLC C 
ON C.AEVERKNR = E.AEVERKNR
LEFT JOIN LD0V0A A 
ON C.AKTPNR = A.PNR
LEFT JOIN LD0P0I I1
ON I1.HAND=E.BESFATT
LEFT JOIN LD0P0I I2
ON I2.HAND=E.HAND
WHERE E.STATUS = 0 AND E.AEVERKNR > 0
;
IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VJH')
BEGIN
DROP VIEW LD0VJH
END
;
CREATE VIEW LD0VJH AS 
SELECT H.PROCDATUM
,CASE WHEN H.AEVERKNR > 0 THEN (SELECT MIN(E.DATUM) FROM LD0P0E E JOIN 
													LD0V0Q01 Q01 
													ON E.STATUS = 0 
													AND E.BESLUT = Q01.KOD 
													WHERE E.AEVERKNR = H.AEVERKNR 
													AND Q01.KODTYP = 'BESLUT' 
													AND Q01.XNUM2 <> 0
													AND Q01.XNUM37<>0) 
ELSE ' ' END AS GRUNDBESLUTDATUM,
CASE WHEN H.AEVERKNR > 0 THEN (SELECT DISTINCT(C20.FOMDAT) FROM LD0VLC20 C20 
																JOIN LD0V0E E 
																ON E.STATUS = 0 
																AND E.INSNR=C20.INSNR 
																AND E.AEVERKNR>0 
																AND C20.BESLUTNR = E.BESLUTNR
																JOIN LD0V0Q01 Q01 
																ON Q01.KOD=E.BESLUT 
																AND Q01.KODTYP='BESLUT' 
																AND Q01.XNUM2<>0
																WHERE E.BESLUTNR IN (SELECT min(BESLUTNR) FROM LD0P0E EB 
																										  JOIN LD0V0Q01 Q01B 
																										  ON EB.STATUS = 0 
																										  AND Q01B.KOD=EB.BESLUT 
																										  AND Q01B.KODTYP='BESLUT' 
																										  AND Q01B.XNUM2<>0 WHERE EB.AEVERKNR=H.AEVERKNR)
																AND E.AEVERKNR=H.AEVERKNR )
ELSE ' ' END AS INSATSDATUM,
C.STOPDAT AS AVSLUTDATUM,
H.AKTUALNR,
H.INSNR,
H.ORGID,
H.AEVERKNR,
H.BESLUTNR,
H.AKTUAL,
H.AKTUALTYP,
H.BTYP,
H.ADATUM,
H.AVEM,
H.AORSAK,
H.ORGENHNR,
H.PNR,
H.HAND,
H.UPDDAT,
H.UPDSIGN,
H.HAND2,
H.ORGENHNR2,
H.RENSAD,
H.ATEXT1,
H.ATEXT2,
H.ATEXT3,
H.ATEXT4,
H.FAMRATTNR,
H.MODER,
H.STATUS,
H.ATGARD,
H.ATGORS,
H.DIARIENR,
H.AKTNR,
H.AVBFROMDAT,
H.AVBTOMDAT,
H.AVBORSAK,
H.REGTID,
H.NEXTMONTH,
H.BIDRTFNR,
H.AVSER,
H.URSPRUNG,
H.APPLICATIONID,
H.ATTACHMENTURL ,
H.WORKFLOW,
H.AKTTYP,
H.CASEID,
H.CASEVALUE,
A.NAMN,
A.KUNDNUMMER,
I.NAMN HANDLAGGARE_NAMN
FROM LD0V0H H 
JOIN LD0V0Q01 Q 
ON Q.KOD = H.AKTUAL 
AND Q.KODTYP = 'AKTUAL' 
AND (Q.XNUM5 > 0 OR Q.XNUM14 > 0 OR Q.XNUM15 > 0 OR Q.XNUM16 > 0) 
LEFT JOIN LD0VLC C 
ON C.AEVERKNR = H.AEVERKNR 
JOIN LD0V0A A 
ON A.PNR=H.PNR
LEFT JOIN LD0P0I I
ON I.HAND=H.HAND
;
