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
;
