DROP VIEW LD0VAA0LMEnrollmentDirective
;

CREATE VIEW LD0VAA0LMEnrollmentDirective
AS SELECT
U11.ANVISANDENR
,U11.INSKRIVNINGSNR
,U11.ANVISORGTYP
,U11.ORGID
,U11.ANVISORGINTERN
,U11.ORGEXTERN
,U11.HANDLAGGARE
,U11.FROMDAT
,U11.TOMDAT
,U11.UPDDAT
,U11.UPDSIGN
,U11.TYPAVSLUT
,U11.AVSLUTORS
,U11.AVSLUTKOMMUN
,U11.AVSLUTLAND
,U11.KOMMUNUTLAND
,U11.YRKEAVSLUT_2012
,Q1.KLARTEXT AS TYPAVSLUT_T
,Q2.KLARTEXT AS AVSLUTORS_T
,Q3.KLARTEXT AS AVSLUTKOMMUN_T
,Q4.KLARTEXT AS AVSLUTLAND_T
,Q5.KLARTEXT AS ANVISORGTYP_T
,Q6.KLARTEXT AS ORGEXTERN_T
,U90.LNUPPDRAGNR AS LNUPPDRAGNR
,U99_3.EXTTEXT AS YRKEAVSLUT_LV3T
,U99_4.EXTTEXT AS YRKEAVSLUT_LV4T
,J01.NAMN  AS ANVISORGINTERN_T
,I.NAMN  AS HANDLAGGARE_T
FROM LD0V0U11 AS U11 
LEFT OUTER JOIN LD0V0Q01 AS Q1 ON Q1.KODTYP = 'LNTYPAVSL' AND U11.TYPAVSLUT = Q1.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q2 ON Q2.KODTYP = 'LNAVSLORS' AND U11.AVSLUTORS = Q2.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q3 ON Q3.KODTYP = 'KOMU' AND U11.AVSLUTKOMMUN = Q3.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q4 ON Q4.KODTYP = 'LAND' AND U11.AVSLUTLAND = Q4.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q5 ON Q5.KODTYP = 'LNANVISTYP' AND U11.ANVISORGTYP = Q5.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q6 ON Q6.KODTYP = 'LNANVISORG' AND U11.ORGEXTERN = Q6.KOD
LEFT OUTER JOIN LD0VYU90 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 J01 ON J01.ORGID = U11.ORGID AND J01.ORGENHNR = U11.ANVISORGINTERN
LEFT OUTER JOIN LD0V0I AS I ON I.HAND = U11.HANDLAGGARE
;