DROP VIEW LD0VAA0SEARCHSERVICES
;
CREATE VIEW LD0VAA0SEARCHSERVICES AS
SELECT        
C20.INSNR, 
C20.INSTYP, 
C20.KEDJENR, 
C20.ARENDENR, 
C20.VERKNR, 
C20.BESLUTNR, 
C20.ORGID, 
C20.ORGENHNR, 
C20.FOMDAT, 
C20.TOMDAT, 
C20.HAND, 
C20.MEDHAND, 
C20.AKTPNR, 
C20.SALFMED, 
C20.UPDDAT, 
C20.UPDSIGN, 
C20.AVBRYT, 
C20.AVBORS, 
C20.INKONTO, 
C20.UTKONTO, 
C20.PRELTOM, 
C20.ANTAL, 
C20.UTFORSAV, 
C20.PERIOD, 
C20.VARDNHAV, 
C20.ORGENHNR2, 
C20.PNR, 
C20.DELREHAB, 
C20.BSKFAMLNR, 
C20.INTDAT, 
C20.UTSKRIVDAT, 
C20.AVSLORSAKTYP, 
C20.AVSLORSAK, 
C20.AVSLORSAKSPEC, 
C20.JDSTATUS, 
C20.BOENDE, 
C20.INGENMEDSOK, 
C20.AVSLORSSTATSOKANDE,
C20.AVSLORSSTATMEDSOKANDE,
C20.AVSLORSSTATHUSHALL, 
Q.KLARTEXT AS INSTYP_T, 
Q_2.KLARTEXT AS AVBORS_T, 
Q_3.KLARTEXT AS AVSLORSAKTYP_T, 
Q_4.KLARTEXT AS AVSLORSAK_T, 
Q_5.KLARTEXT AS AVSLORSAKSPEC_T, 
Q_6.KLARTEXT AVSLORSSTATSOKANDE_T,
Q_7.KLARTEXT AVSLORSSTATMEDSOKANDE_T,
Q_8.KLARTEXT AVSLORSSTATHUSHALL_T,
J01.NAMN AS ORGNAMN, 
I1.NAMN AS CASEWORKER, 
I2.NAMN AS COCASEWORKER, 
A.NAMN, 
A.ADRESS, 
A.COADR, 
A.FODDAT, 
A.KON, 
A.POSTNR, 
A.ORT, 
OMR.NAMN OMRADE_NAMN
FROM LD0VLC20 C20 
INNER JOIN LD0V0Q01 AS Q 
ON Q.KODTYP = 'INSATS' 
AND Q.KOD = C20.INSTYP 
LEFT OUTER JOIN LD0V0Q01 Q_2 
ON Q_2.KODTYP = 'INSAVBORS' 
AND Q_2.KOD = C20.AVBORS 
LEFT OUTER JOIN LD0V0Q01 Q_3 
ON Q_3.KODTYP = 'INSORSTYP' 
AND Q_3.KOD = C20.AVSLORSAKTYP 
LEFT OUTER JOIN LD0V0Q01 Q_4 
ON Q_4.KODTYP = 'INSORSAK' 
AND Q_4.KOD = C20.AVSLORSAK 
LEFT OUTER JOIN LD0V0Q01 Q_5 
ON Q_5.KODTYP = 'INSORSSPEC' 
AND Q_5.KOD = C20.AVSLORSAKSPEC 
LEFT JOIN LD0V0Q01 Q_6 
ON Q_6.KODTYP ='SCBKODAVSL' 
AND Q_6.KOD = C20.AVSLORSSTATSOKANDE
LEFT JOIN LD0V0Q01 Q_7 
ON Q_7.KODTYP ='SCBKODAVSL' 
AND Q_7.KOD = C20.AVSLORSSTATMEDSOKANDE
LEFT JOIN LD0V0Q01 Q_8 
ON Q_8.KODTYP ='SCBKODAVSL' 
AND Q_8.KOD = C20.AVSLORSSTATHUSHALL
LEFT OUTER JOIN LD0P0J01 J01 
ON J01.ORGENHNR = C20.ORGENHNR 
AND J01.ORGID = C20.ORGID 
LEFT OUTER JOIN LD0V0I I1 
ON I1.HAND = c20.HAND 
LEFT OUTER JOIN LD0V0I I2 
ON I2.HAND = c20.MEDHAND 
LEFT OUTER JOIN LD0V0A 
A ON A.PNR = C20.AKTPNR 
LEFT OUTER JOIN LD0VAA0OMRADENKO OMR 
ON A.NYCKKOD = OMR.NYCKKOD 
;