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

CREATE VIEW LD0VAA0LMEnrollmentPlanActivity
  AS
    SELECT
      U34.PLANAKTIVITETNR,
      U34.URSPRUNG_PLANAKTNR,
      U34.INTROARB,
      U34.AKTIVTYP,
      U34.AKTIVITET,
      U34.INTANORD,
      U34.ANMALDAT,
      U34.PLANSTART,
      U34.PLANSLUT,
      U34.VERKSTART,
      U34.VERKSLUT,
      U34.SIGN,
      U34.UPPFOLJNING,
      U34.RESULTAT,
      U34.UPDDAT,
      U34.UPDSIGN,
      U34.SENASTEVERSION,
      U34.ENKELPLATSNR,
      U34.OVRIGPLATS,
      U34.OVRANT,
      U34.OVRIG1,
      U34.OVRIG2,
      U34.OVRIG3,
      U34.OVRDAT,
      U34.GRUPPNR,
      U34.PLANNR,
      U34.VERSIONNR,
      U34.HAND,
      U10.PERSONNR,
      A.NAMN,
      Q1.KLARTEXT AS AKTIVITET_T,
      Q2.KLARTEXT AS AKTIVTYP_T,
      Q3.KLARTEXT AS RESULTAT_T,
      Q4.KLARTEXT AS INTANORD_T,
      Q5.KLARTEXT AS OVRIGPLATS_T,
      Q6.KLARTEXT AS OVRIG1_T,
      Q7.KLARTEXT AS OVRIG2_T,
      Q8.KLARTEXT AS OVRIG3_T,
      I.NAMN            AS HANDLAGGARE_T,
      U70.BENAMNING AS ENKELPLATS_T
      FROM LD0V0U34 AS U34 
      LEFT OUTER JOIN LD0VYU30 AS U30 ON U30.PLANNR = U34.PLANNR AND U30.VERSIONNR = U34.VERSIONNR
      LEFT OUTER JOIN LD0VYU10 AS U10 ON U10.INSKRIVNINGSNR = U30.INSKRIVNINGSNR AND U30.PLANNR = U34.PLANNR
      LEFT OUTER JOIN LD0V0A AS A ON A.PNR = U10.PERSONNR
      LEFT OUTER JOIN LD0V0Q01 AS Q1 ON Q1.KODTYP = 'LNAKTIV' AND U34.AKTIVITET = Q1.KOD
      LEFT OUTER JOIN LD0V0Q01 AS Q2 ON Q2.KODTYP = 'LNAKTIVTYP' AND U34.AKTIVTYP = Q2.KOD
      LEFT OUTER JOIN LD0V0Q01 AS Q3 ON Q3.KODTYP = 'LNRESAKT' AND U34.RESULTAT = Q3.KOD
      LEFT OUTER JOIN LD0V0Q01 AS Q4 ON Q4.KODTYP = 'LNINTANORD' AND U34.INTANORD = Q4.KOD
      LEFT OUTER JOIN LD0V0Q01 AS Q5 ON Q5.KODTYP = 'LNOVRPLATS' AND U34.OVRIGPLATS = Q5.KOD
      LEFT OUTER JOIN LD0V0Q01 AS Q6 ON Q6.KODTYP = 'LNOVRIG1' AND U34.OVRIG1 = Q6.KOD
      LEFT OUTER JOIN LD0V0Q01 AS Q7 ON Q7.KODTYP = 'LNOVRIG2' AND U34.OVRIG2 = Q7.KOD
      LEFT OUTER JOIN LD0V0Q01 AS Q8 ON Q8.KODTYP = 'LNOVRIG3' AND U34.OVRIG3 = Q8.KOD
      LEFT OUTER JOIN LD0V0I AS I ON I.HAND = U34.HAND
      LEFT OUTER JOIN LD0V0U70 AS U70 ON U70.ENKELPLATSNR = U34.ENKELPLATSNR
      WHERE U34.INTROARB<>2
      UNION 
      SELECT
      U34.PLANAKTIVITETNR,
      U34.URSPRUNG_PLANAKTNR,
      U34.INTROARB,
      U34.AKTIVTYP,
      U34.AKTIVITET,
      U34.INTANORD,
      U34.ANMALDAT,
      U34.PLANSTART,
      U34.PLANSLUT,
      CASE WHEN ISNULL(U35.PLATSFROM, ' ') =' ' THEN U34.VERKSTART ELSE U35.PLATSFROM END,
      CASE WHEN ISNULL(U35.PLATSTOM, ' ') =' ' THEN U34.VERKSLUT ELSE U35.PLATSTOM END,
      U34.SIGN,
      U34.UPPFOLJNING,
      U34.RESULTAT,
      U34.UPDDAT,
      U34.UPDSIGN,
      U34.SENASTEVERSION,
      U34.ENKELPLATSNR,
      U34.OVRIGPLATS,
      U34.OVRANT,
      U34.OVRIG1,
      U34.OVRIG2,
      U34.OVRIG3,
      U34.OVRDAT,
      U34.GRUPPNR,
      U34.PLANNR,
      U34.VERSIONNR,
      U34.HAND,
      U10.PERSONNR,
      A.NAMN,
      Q1.KLARTEXT AS AKTIVITET_T,
      Q2.KLARTEXT AS AKTIVTYP_T,
      Q3.KLARTEXT AS RESULTAT_T,
      Q4.KLARTEXT AS INTANORD_T,
      Q5.KLARTEXT AS OVRIGPLATS_T,
      Q6.KLARTEXT AS OVRIG1_T,
      Q7.KLARTEXT AS OVRIG2_T,
      Q8.KLARTEXT AS OVRIG3_T,
      I.NAMN            AS HANDLAGGARE_T,
      U70.BENAMNING AS ENKELPLATS_T
      FROM LD0V0U34 AS U34 

      LEFT OUTER JOIN LD0V0U35 AS U35 ON U34.PLANAKTIVITETNR=U35.PLANAKTIVITETNR AND U34.INTROARB=2 AND U35.STATUS=2
      LEFT OUTER JOIN LD0VYU30 AS U30 ON U30.PLANNR = U34.PLANNR AND U30.VERSIONNR = U34.VERSIONNR
      LEFT OUTER JOIN LD0VYU10 AS U10 ON U10.INSKRIVNINGSNR = U30.INSKRIVNINGSNR AND U30.PLANNR = U34.PLANNR
      LEFT OUTER JOIN LD0V0A AS A ON A.PNR = U10.PERSONNR
      LEFT OUTER JOIN LD0V0Q01 AS Q1 ON Q1.KODTYP = 'LNAKTIV' AND U34.AKTIVITET = Q1.KOD
      LEFT OUTER JOIN LD0V0Q01 AS Q2 ON Q2.KODTYP = 'LNAKTIVTYP' AND U34.AKTIVTYP = Q2.KOD
      LEFT OUTER JOIN LD0V0Q01 AS Q3 ON Q3.KODTYP = 'LNRESAKT' AND U34.RESULTAT = Q3.KOD
      LEFT OUTER JOIN LD0V0Q01 AS Q4 ON Q4.KODTYP = 'LNINTANORD' AND U34.INTANORD = Q4.KOD
      LEFT OUTER JOIN LD0V0Q01 AS Q5 ON Q5.KODTYP = 'LNOVRPLATS' AND U34.OVRIGPLATS = Q5.KOD
      LEFT OUTER JOIN LD0V0Q01 AS Q6 ON Q6.KODTYP = 'LNOVRIG1' AND U34.OVRIG1 = Q6.KOD
      LEFT OUTER JOIN LD0V0Q01 AS Q7 ON Q7.KODTYP = 'LNOVRIG2' AND U34.OVRIG2 = Q7.KOD
      LEFT OUTER JOIN LD0V0Q01 AS Q8 ON Q8.KODTYP = 'LNOVRIG3' AND U34.OVRIG3 = Q8.KOD
      LEFT OUTER JOIN LD0V0I AS I ON I.HAND = U34.HAND LEFT OUTER JOIN LD0V0U70 AS U70 ON U70.ENKELPLATSNR = U34.ENKELPLATSNR
      ;
