DROP VIEW LD0VAA0LMSEARCHINTERNSHIP
;
CREATE VIEW LD0VAA0LMSEARCHINTERNSHIP AS
SELECT
U60.PRAKPROJNR,
U60.BENAMNING,
U60.FROMDAT,
U60.TOMDAT,
U61.YRKE_2012,
ISNULL(V4U99.NIVA1_EXTEXT, '') as YRKE_TEXT1,
ISNULL(V4U99.NIVA2_EXTEXT, '') as YRKE_TEXT2,
ISNULL(V4U99.NIVA3_EXTEXT, '') as YRKE_TEXT3,
U61.YRKE2_2012,
ISNULL(V4U99_2.NIVA1_EXTEXT, '') as YRKE2_TEXT1,
ISNULL(V4U99_2.NIVA2_EXTEXT, '') as YRKE2_TEXT2,
ISNULL(V4U99_2.NIVA3_EXTEXT, '') as YRKE2_TEXT3,
U61.NIVA,
ISNULL(V0U99.EXTTEXT, '') as NIVA_TEXT,
U61.NIVA2,
ISNULL(V0U99_2.EXTTEXT, '') as NIVA2_TEXT,
ISNULL(U61.KORKORT,0) as KORKORT,
ISNULL(U61.KORKORTA,0) as KORKORTA,
ISNULL(U61.KORKORTA1,0) as KORKORTA1,
ISNULL(U61.KORKORTA2,0) as KORKORTA2,
ISNULL(U61.KORKORTAM,0) as KORKORTAM,
ISNULL(U61.KORKORTB,0) as KORKORTB,
ISNULL(U61.KORKORTBE,0) as KORKORTBE,
ISNULL(U61.KORKORTB96,0) as KORKORTB96,
ISNULL(U61.KORKORTC,0) as KORKORTC,
ISNULL(U61.KORKORTC1,0) as KORKORTC1,
ISNULL(U61.KORKORTC1E,0) as KORKORTC1E,
ISNULL(U61.KORKORTCE,0) as KORKORTCE,
ISNULL(U61.KORKORTD,0) as KORKORTD,
ISNULL(U61.KORKORTD1,0) as KORKORTD1,
ISNULL(U61.KORKORTD1E,0) as KORKORTD1E,
ISNULL(U61.KORKORTDE,0) as KORKORTDE,
U60.ANORDNARNR,
U60.UTFORARNR,
U50_ANORDNARE.BENAMNING as BENAMNINGANORDNARE,
U50_UTFORARE.BENAMNING as BENAMNINGUTFORARE,
U60.SOKORD1,
ISNULL(Q_SOKORD1.KLARTEXT, '') as SOKORD1_T,
U60.SOKORD2,
ISNULL(Q_SOKORD2.KLARTEXT, '') as SOKORD2_T,
U61.ERFARFARDIG,
U61.ERFARFARDIG2,
U61.EFTYP,
U61.EFTYP2,
U61.SPRAK1,
U61.SPRAK2,
U61.FODLAND,
U61.KON,
U61.ALDERFROM,
U61.ALDERTOM,
U61.OVRFORD,
U60.INRIKTNING
,ISNULL(Q1.KLARTEXT, '') AS EFTYP_T
,ISNULL(Q2.KLARTEXT, '') AS ERFARFARDIG_T
,ISNULL(Q3.KLARTEXT, '') AS EFTYP2_T
,ISNULL(Q4.KLARTEXT, '') AS ERFARFARDIG2_T
,ISNULL(Q5.KLARTEXT, '') AS OVRFORD_T
,ISNULL(Q6.KLARTEXT, '') AS SPRAK1_T
,ISNULL(Q7.KLARTEXT, '') AS SPRAK2_T
,ISNULL(Q8.KLARTEXT, '') AS KON_T
,ISNULL(Q9.KLARTEXT, '') AS FODLAND_T
FROM LD0V0U60 U60
LEFT OUTER JOIN LD0V0U61 AS U61
ON U60.PRAKPROJNR = U61.PRAKPROJNR
LEFT OUTER JOIN LD0V0Q01 as Q_SOKORD1
ON Q_SOKORD1.KODTYP = 'LNSOKORD' 
AND Q_SOKORD1.KOD = U60.SOKORD1
LEFT OUTER JOIN LD0V0Q01 as Q_SOKORD2
ON Q_SOKORD2.KODTYP = 'LNSOKORD2' 
AND Q_SOKORD2.KOD = U60.SOKORD2
LEFT OUTER JOIN LD0V0U50 as U50_ANORDNARE
ON U50_ANORDNARE.ANORDUTFORNR = U60.ANORDNARNR
LEFT OUTER JOIN LD0V0U50 as U50_UTFORARE
ON U50_UTFORARE.ANORDUTFORNR = U60.UTFORARNR
LEFT OUTER JOIN LD0V0Q01 AS Q1 ON Q1.KODTYP = 'LNEFTYP' AND U61.EFTYP = Q1.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q2 ON Q2.KODTYP = 'LNEF' AND U61.ERFARFARDIG = Q2.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q3 ON Q3.KODTYP = 'LNEFTYP' AND U61.EFTYP2 = Q3.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q4 ON Q4.KODTYP = 'LNEF' AND U61.ERFARFARDIG2 = Q4.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q5 ON Q5.KODTYP = 'LNFORDON' AND U61.OVRFORD = Q5.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q6 ON Q6.KODTYP = 'SPRAK' AND U61.SPRAK1 = Q6.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q7 ON Q7.KODTYP = 'SPRAK' AND U61.SPRAK2 = Q7.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q8 ON Q8.KODTYP = 'LNKOEN' AND U61.KON = Q8.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q9 ON Q9.KODTYP = 'LAND' AND U61.FODLAND = Q9.KOD
LEFT OUTER JOIN Ld0V4U99 AS V4U99 ON V4U99.NIVA3_EXTKOD = U61.YRKE_2012
LEFT OUTER JOIN Ld0V4U99 AS V4U99_2 ON V4U99_2.NIVA3_EXTKOD = U61.YRKE2_2012
LEFT OUTER JOIN Ld0V0U99 AS V0U99 ON V0U99.EXTKOD = U61.NIVA AND V0U99.NIVA = 3 AND V0U99.EXTKODTYP = 1
LEFT OUTER JOIN Ld0V0U99 AS V0U99_2 ON V0U99_2.EXTKOD = U61.NIVA2 AND V0U99.NIVA = 3 AND V0U99.EXTKODTYP = 1
;