IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0SearchEnrollment')
BEGIN
DROP VIEW LD0VAA0SearchEnrollment
END
;
CREATE VIEW LD0VAA0SEARCHENROLLMENT
AS SELECT
U10.*
,A.PNR
,A.NAMN as PERSNAMN
,A.KUNDNUMMER AS KUNDNUMMER
,A.FODDAT 
,A.KON 
,A.ORT 
,A.ADRESS 
,A.POSTNR 
,A.COADR AS PERSONCOADDRESS
,A.MOBIL
,A.EPOSTARB
,A.HEMTEL
,A.ARBTEL
,A.FODLAND
,Q1.KLARTEXT AS TYPAVSLUT_T
,Q2.KLARTEXT AS AVSLUTORS_T
,Q3.KLARTEXT AS AVSLUTKOMMUN_T
,Q4.KLARTEXT AS AVSLUTLAND_T
,Q5.KLARTEXT AS INSKRTYP_T
,Q6.KLARTEXT AS UTANARB_T
,J01.NAMN  AS  ORGENH_T
,OMR.NAMN AS OMRADE_NAMN
,U11.ANVISANDENR
,U11.ANVISORGTYP
,U11.ANVISORGINTERN
,U11.ORGEXTERN
,U11.HANDLAGGARE
,U11.FROMDAT AS DIRFROMDAT
,U11.TOMDAT AS DIRTODAT
,U11.TYPAVSLUT AS DIRTYPAVSLUT
,U11.AVSLUTORS AS DIRAVSLUTORS
,U11.YRKEAVSLUT_2012 AS DIRYRKEAVSLUT_2012
,U11.AVSLUTLAND AS DIRAVSLUTLAND
,U11.AVSLUTKOMMUN AS DIRAVSLUTKOMMUN
,U11.KOMMUNUTLAND AS DIRKOMMUNUTLAND
,Q7.KLARTEXT AS DIRTYPAVSLUT_T
,Q8.KLARTEXT AS DIRAVSLUTORS_T
,Q9.KLARTEXT AS DIRAVSLUTKOMMUN_T
,Q10.KLARTEXT AS DIRAVSLUTLAND_T
,Q11.KLARTEXT AS DIRANVISORGTYP_T
,Q12.KLARTEXT AS DIRORGEXTERN_T
,U90.LNUPPDRAGNR AS LNUPPDRAGNR
,U99_3.EXTTEXT AS YRKEAVSLUT_LV3T
,U99_4.EXTTEXT AS YRKEAVSLUT_LV4T
,J02.NAMN  AS DIRANVISORGINTERN_T
,I.NAMN  AS DIRHANDLAGGARE_T

FROM LD0V0U10 AS U10
LEFT OUTER JOIN LD0V0A AS A ON A.PNR = U10.PERSONNR
LEFT OUTER JOIN LD0V0Q01 AS Q1 ON Q1.KODTYP = 'LNTYPAVSL' AND U10.TYPAVSLUT = Q1.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q2 ON Q2.KODTYP = 'LNAVSLORS' AND U10.AVSLUTORS = Q2.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q3 ON Q3.KODTYP = 'KOMU' AND U10.AVSLUTKOMMUN = Q3.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q4 ON Q4.KODTYP = 'LAND' AND U10.AVSLUTLAND = Q4.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q5 ON Q5.KODTYP = 'LNINSKTYP' AND U10.INSKRTYP = Q5.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q6 ON Q6.KODTYP = 'LNUTANARB' AND Q6.KOD = U10.UTANARB 
LEFT OUTER JOIN LD0V0J01 AS J01 ON J01.ORGID = U10.ORGID AND J01.ORGENHNR = U10.ORGENHNR
LEFT OUTER JOIN LD0VAA0OMRADENKO OMR ON A.NYCKKOD = OMR.NYCKKOD 
LEFT OUTER JOIN LD0V0U11 AS U11 ON U10.INSKRIVNINGSNR=u11.INSKRIVNINGSNR
LEFT OUTER JOIN LD0V0Q01 AS Q7 ON Q7.KODTYP = 'LNTYPAVSL' AND U11.TYPAVSLUT = Q7.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q8 ON Q8.KODTYP = 'LNAVSLORS' AND U11.AVSLUTORS = Q8.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q9 ON Q9.KODTYP = 'KOMU' AND U11.AVSLUTKOMMUN = Q9.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q10 ON Q10.KODTYP = 'LAND' AND U11.AVSLUTLAND = Q10.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q11 ON Q11.KODTYP = 'LNANVISTYP' AND U11.ANVISORGTYP = Q11.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q12 ON Q12.KODTYP = 'LNANVISORG' AND U11.ORGEXTERN = Q12.KOD
LEFT OUTER JOIN LD0V0U90 AS U90 ON U90.ANVISANDENR = U11.ANVISANDENR
LEFT OUTER JOIN LD0V0U99 AS U99_3 ON U99_3.EXTKODTYP = 4 AND U99_3.EXTKOD = LEFT(U11.YRKEAVSLUT_2012,3) AND U99_3.NIVA = 3
LEFT OUTER JOIN LD0V0U99 AS U99_4 ON U99_4.EXTKODTYP = 4 AND U99_4.EXTKOD = U11.YRKEAVSLUT_2012 AND U99_4.NIVA = 4
LEFT OUTER JOIN LD0V0J01 AS J02 ON J02.ORGID = U11.ORGID AND J02.ORGENHNR = U11.ANVISORGINTERN
LEFT OUTER JOIN LD0V0I AS I ON I.HAND = U11.HANDLAGGARE
;