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

CREATE VIEW LD0VAA0LMSearchEnrollmentPlans 
AS 
SELECT U30.*, A.NAMN, A.ADRESS, A.ORT, A.COADR, A.POSTNR,
 U10.INLEDDAT AS INLEDDAT_U10, U10.AVSLUTDAT AS AVSLUTDAT_U10, 
 U12.ANSVARIGFOR AS ANSVARIGFOR_U12, U12.ANSVARIGORG AS ANSVARIGORG_U12, U12.HANDLAGGARE AS HANDLAGGARE_U12, U12.STARTDATE AS STARDATE_U12, U12.ENDDATE AS ENDDATE_U12, 
 Q01.KLARTEXT AS ANVARIGFOR_U12_T,
 I.NAMN AS HANDLAGGARE_U12_T,
 Q01B.KLARTEXT AS PLANTYP_T,
 J01.NAMN AS NAMN_J01_T
FROM LD0VLU30 as U30 JOIN LD0V1U10 AS U10 ON U30.INSKRIVNINGSNR = U10.INSKRIVNINGSNR 
LEFT OUTER JOIN LD0V0U12 AS U12 ON U12.INSKRIVNINGSNR = U10.INSKRIVNINGSNR
LEFT OUTER JOIN LD0V0Q01 AS Q01 ON Q01.KOD = U12.ANSVARIGFOR AND Q01.KODTYP = 'LNANSVFOR'
LEFT OUTER JOIN LD0V0Q01 AS Q01B ON Q01B.KOD = U30.PLANTYP AND  Q01B.KODTYP = 'LNPLANTYP'
LEFT OUTER JOIN LD0V0I AS I ON I.HAND = U12.HANDLAGGARE
LEFT OUTER JOIN LD0V0J01 AS J01 ON J01.ORGENHNR = U12.ANSVARIGORG AND J01.ORGID = U12.ORGID
LEFT OUTER JOIN LD0VYA AS A ON A.PNR = U10.PERSONNR
;