IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0SEARCHINVESTIGATIONS')
BEGIN
DROP VIEW LD0VAA0SEARCHINVESTIGATIONS
END
GO

CREATE VIEW LD0VAA0SEARCHINVESTIGATIONS
AS
SELECT 
C.AEVERKNR,
C.STARTDAT,
C.STOPDAT,
C.HAND,
C.MEDHAND,
C.AEVERKTYP,
C.ORGENHNR,
C.ORGENHNR2,
C.UPDDAT,
C.UPDSIGN,
C.AKTPNR,
C.TMARK,
C.FAMRTYP1,
C.FAMRTYP2,
C.FAMRTYP3,
A.NAMN AS APPLICANTNAMN,
A.ADRESS,
A.COADR,
A.ORT,
A.POSTNR,
A.OMRADE,
A.FODDAT,
A.KON,
I.NAMN AS CASEWORKER,
I2.NAMN AS COCASEWORKER,
T.KLARTEXT AS AEVERKTYP_T,
J01.NAMN AS ORGNAMN,
B.TYPNR_T AS DOSSIERTYPE,
J01.ORGID,
OMR.NAMN OMRADE_NAMN,
A.KUNDNUMMER,
A.HEMTEL,
A.ARBTEL,
A.MOBIL
FROM LD0VLC C 
JOIN LD0V0A A 
ON C.AKTPNR = A.PNR 
INNER JOIN LD0V0Q01 T 
ON T.KODTYP = 'UTREDTYP' 
AND T.AKTIV > 0 
AND T.KOD = C.AEVERKTYP 
INNER JOIN LD0V0J01 J01 
ON J01.ORGENHNR = C.ORGENHNR 
INNER JOIN LD0V0I I 
ON C.HAND = I.HAND 
LEFT JOIN LD0VAA0B B 
ON B.AKTNR = C.AKTNR 
LEFT JOIN LD0V0I I2 
ON C.MEDHAND = I2.HAND 
LEFT OUTER JOIN LD0VAA0OMRADENKO OMR 
ON A.NYCKKOD = OMR.NYCKKOD 
WHERE TMARK = 0 
GO

GRANT ALL ON LD0VAA0SEARCHINVESTIGATIONS TO LD0ROANV
GO
