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

CREATE VIEW LD0VAA0LMSearchActivity
AS 
SELECT DISTINCT U34.PLANAKTIVITETNR AS U34_PLANAKTIVITETNR, U34.INTROARB AS INTROARB, U34.AKTIVTYP AS U34_AKTIVTYP, U34.AKTIVITET AS U34_AKTIVITET, U34.INTANORD AS U34_INTANORD, 
    U34.ANMALDAT AS U34_ANMALDAT, U34.PLANSTART AS U34_PLANSTART, U34.PLANSLUT AS U34_PLANSLUT, U34.VERKSTART AS U34_VERKSTART,
    U34.VERKSLUT AS U34_VERKSLUT, U34.UPPFOLJNING AS U34_UPPFOLJNING, U34.RESULTAT AS U34_RESULTAT, U34.OVRANT AS U34_OVRANT_T,
    U34.UPDDAT AS U34_UPDDAT, U34.UPDSIGN AS U34_UPDSIGN, U34.ENKELPLATSNR AS U34_ENKELPLATSNR, U34.HAND,
    Q01B.KLARTEXT AS U34_AKTIVTYP_T, Q01C.KLARTEXT AS U34_AKTIVITET_T, Q01D.KLARTEXT AS U34_RESULTAT_T, Q01J.KLARTEXT AS U34_INTANORD_T, 
    U10.INSKRIVNINGSNR AS U10_INSKRIVNINGSNR, U10.PERSONNR AS U10_PERSONNR, U10.INLEDDAT AS U10_INLEDDAT, 
    U10.AVSLUTDAT AS U10_AVSLUTDAT, Q01E.KLARTEXT AS U10_AVSLUTORS_T,
      A.NAMN AS A_NAMN, A.KUNDNUMMER AS KUNDNUMMER, A.ADRESS AS A_ADRESS, A.POSTNR AS A_POSTNR, A.COADR AS A_COADR,  A.ORT AS A_ORT, A.MOBIL AS A_MOBIL, A.FODLAND AS A_FODLAND, A.FODDAT AS A_FODDAT, A.KON AS A_KON, 
      CASE WHEN A.KON = 1 THEN 'Man' ELSE 'Kvinna' END AS A_KON_T , A.OMRADE, A.NYCKKOD, Q01.KLARTEXT AS A_FODLAND_T,
      OMR.OMRID AS OMRID,
      OMR.OMRENHNR AS OMRENHNR,
      OMR.NAMN AS OMRADE_NAMN, 
      Q01K.KLARTEXT AS OMRADE_T,
      U12.ORGID AS ORGID, U12.ANSVARIGORG AS ORGENHNR, 
      U30.PLANNR AS U30_PLANNR,	U30.SLUTLIG AS U30_SLUTLIG,
      U35.PRAKPROJNR AS U35_PRAKPROJNR, U35.STATUS AS U35_STATUS, I.NAMN AS I_NAMN,
      U35.FROMDAT AS U35_FROMDAT, U35.TOMDAT AS U35_TOMDAT, U35.PLATSFROM AS U35_PLATSFROM, U35.PLATSTOM AS U35_PLATSTOM, U35.REGDATUM AS U35_REGDATUM, Q01I.KLARTEXT AS U35_STATUS_T,
      U60.PRAKPROJNR AS U60_PRAKPROJNR, U60.ANORDNARNR AS U60_ANORDNARNR, U60.UTFORARNR AS U60_UTFORARNR, Q01F.KLARTEXT AS U60_PRAKTIKTYP_T, U60.BENAMNING AS U60_BENAMNING,
      Q01G.KLARTEXT AS U60_INRIKTNING_T, U50.BENAMNING AS U50_BENAMNING,
      U70.ENKELPLATSNR AS U70_ENKELPLATSNR, U70.BENAMNING AS U70_BENAMNING,
      Q01H.KLARTEXT AS U70_HUVUDMAN_T,
	CASE WHEN U30.ANSVARIGORG <> '0' AND U30.ANSVARIGORG <> '' THEN
      J01U30.NAMN 
	WHEN
	 U30.ANSVARIGORG = '0' OR U30.ANSVARIGORG = '' THEN
	 J01U12.NAMN
	END
	AS J01_NAMN
FROM LD0V0U10 U10 
JOIN LD0V0A AS A ON U10.PERSONNR = A.PNR
LEFT OUTER JOIN LD0P0Q01 AS Q01 ON Q01.KOD = A.FODLAND  AND Q01.KODTYP = 'LAND'
LEFT OUTER JOIN LD0V0U30 AS U30 ON U30.INSKRIVNINGSNR = U10.INSKRIVNINGSNR
RIGHT JOIN LD0V0U34 AS U34 ON U34.PLANNR = U30.PLANNR AND U34.VERSIONNR = U30.VERSIONNR AND U30.INSKRIVNINGSNR = U10.INSKRIVNINGSNR
LEFT OUTER JOIN LD0V0U12 U12 ON U12.ANSVARIGNR = U30.ANSVARIGNR
LEFT OUTER JOIN LD0V0I I ON U34.HAND = I.HAND
LEFT OUTER JOIN LD0V0U35 AS U35 ON U35.PLANAKTIVITETNR = U34.PLANAKTIVITETNR
LEFT OUTER JOIN LD0V0U60 AS U60 ON U60.PRAKPROJNR = U35.PRAKPROJNR 
LEFT OUTER JOIN LD0V0U50 AS U50 ON U50.ANORDUTFORNR = U60.ANORDNARNR
LEFT OUTER JOIN LD0V0U70 AS U70 ON U70.ENKELPLATSNR = U34.ENKELPLATSNR
LEFT OUTER JOIN LD0VAA0OMRADENKO OMR ON A.NYCKKOD = OMR.NYCKKOD 
LEFT OUTER JOIN LD0V0Q01 AS Q01B ON Q01B.KOD = U34.AKTIVTYP AND Q01B.KODTYP = 'LNAKTIVTYP'
LEFT OUTER JOIN LD0V0Q01 AS Q01C ON Q01C.KOD = U34.AKTIVITET AND Q01C.KODTYP = 'LNAKTIV'
LEFT OUTER JOIN LD0V0Q01 AS Q01D ON Q01D.KOD = U34.RESULTAT AND Q01D.KODTYP = 'LNRESAKT'
LEFT OUTER JOIN LD0V0Q01 AS Q01E ON Q01E.KOD = U10.AVSLUTORS AND Q01E.KODTYP = 'LNAVSLORS'
LEFT OUTER JOIN LD0V0Q01 AS Q01F ON Q01F.KOD = U60.PRAKTIKTYP AND Q01F.KODTYP = 'LNPRAKTYP'
LEFT OUTER JOIN LD0V0Q01 AS Q01G ON Q01G.KOD = U60.PRAKTIKTYP AND Q01G.KODTYP = 'LNINRIKTN'
LEFT OUTER JOIN LD0V0Q01 AS Q01H ON Q01H.KOD = U70.HUVUDMAN AND Q01H.KODTYP = 'LNHUVUDMAN'
LEFT OUTER JOIN LD0V0Q01 AS Q01I ON Q01I.KOD = U35.STATUS AND Q01I.KODTYP = 'LNSTATUS'
LEFT OUTER JOIN LD0V0Q01 AS Q01J ON Q01J.KOD = U34.INTANORD AND Q01J.KODTYP = 'LNINTANORD'
LEFT OUTER JOIN LD0V0Q01 AS Q01K ON Q01K.KOD = OMRADE AND Q01K.KODTYP = 'OMRÅDE'

LEFT OUTER JOIN LD0V0J01 J01U30 ON 
	J01U30.ORGID = U30.ORGID AND J01U30.ORGENHNR = U30.ANSVARIGORG
	AND U12.ANSVARIGNR = U30.ANSVARIGNR
LEFT OUTER JOIN LD0V0J01 J01U12 
	ON J01U12.ORGID = U12.ORGID 
	AND J01U12.ORGENHNR = U12.ANSVARIGORG 
    AND U12.ANSVARIGNR = U30.ANSVARIGNR

;


