
CREATE OR ALTER VIEW LD0V0U27 AS SELECT * FROM LD0P0U27
GO
CREATE OR ALTER VIEW LD0VLU27 AS SELECT * FROM LD0P0U27
GO
CREATE OR ALTER VIEW LD0VYU27 AS SELECT * FROM LD0P0U27
GO
CREATE OR ALTER VIEW LD0VAA0SearchSurveyAdditionalData
AS
SELECT
U27.*
  ,A.NAMN AS PERSONNAME
  ,A.KUNDNUMMER
  ,A.FODDAT
  ,A.KON
   ,A.ORT
   ,A.ADRESS
   ,A.POSTNR
   ,A.COADR AS PERSONCOADDRESS
  ,U01.PERSONNR
  ,Q1.KLARTEXT as OVRFORD1_T
  ,Q2.KLARTEXT as OVRFORD2_T
  ,Q3.KLARTEXT as OVRFORD3_T
  FROM LD0P0U27 U27
LEFT OUTER JOIN LD0P0U01 AS U01 ON U01.KARTLAGGNR = U27.KARTLAGGNR
JOIN LD0V0A A ON A.PNR = U01.PERSONNR 
LEFT OUTER JOIN ld0v0q01 AS Q1 ON Q1.KODTYP = 'LNFORDON' and Q1.KOD = U27.OVRFORD1
LEFT OUTER JOIN ld0v0q01 AS Q2 ON Q2.KODTYP = 'LNFORDON' and Q2.KOD = U27.OVRFORD2
LEFT OUTER JOIN ld0v0q01 AS Q3 ON Q3.KODTYP = 'LNFORDON' and Q3.KOD = U27.OVRFORD3
GO
CREATE OR ALTER VIEW LD0VAA0SurveyAdditionalData
AS
SELECT
U27.KORKORT
,U27.KORKORTA
,U27.KORKORTB
,U27.KORKORTC
,U27.KORKORTD
,U27.KORKORTE
,U27.OVRFORD1
,U27.OVRFORD2
,U27.OVRFORD3
,U27.UPDDAT
,U27.UPDSIGN
,U27.KORKORTA1
,U27.KORKORTBE
,U27.KORKORTCE
,U27.KORKORTDE
,U27.UTLANDSKT
,U27.GILTIGTTOM
,U27.KORKORTAM
,U27.KORKORTA2
,U27.KORKORTB96
,U27.KORKORTC1
,U27.KORKORTC1E
,U27.KORKORTD1
,U27.KORKORTD1E
,U27.KARTLAGGNR
,U27.AUTOMATIC
,Q1.KLARTEXT as OVRFORD1_T
,Q2.KLARTEXT as OVRFORD2_T
,Q3.KLARTEXT as OVRFORD3_T
,Q4.KLARTEXT as ERSATTNTYP_T
FROM LD0P0U27 U27
LEFT OUTER JOIN ld0v0q01 AS Q1 ON Q1.KODTYP = 'LNFORDON' and Q1.KOD = U27.OVRFORD1
LEFT OUTER JOIN ld0v0q01 AS Q2 ON Q2.KODTYP = 'LNFORDON' and Q2.KOD = U27.OVRFORD2
LEFT OUTER JOIN ld0v0q01 AS Q3 ON Q3.KODTYP = 'LNFORDON' and Q3.KOD = U27.OVRFORD3
LEFT OUTER JOIN ld0v0q01 AS Q4 ON Q4.KODTYP = 'LNERSTYP' and Q4.KOD = U27.ERSATTNTYP
GO

CREATE OR ALTER VIEW LD0VAA0DocumentPdf 
AS
select T43.*, 
CASE WHEN T43.AGARTYP = 7017 THEN Q01I.KLARTEXT
WHEN T43.AGARTYP = 7040 THEN Q01A.KLARTEXT 
WHEN T43.AGARTYP = 7025 THEN Q01E.KLARTEXT
WHEN T43.AGARTYP = 7083 THEN Q01S.KLARTEXT
WHEN T43.AGARTYP = 7041 THEN Q01B01.KLARTEXT
END AS AGARTYP_T,
CASE WHEN PDF.DOKUMENTID IS NOT NULL THEN 1 ELSE 0 END AS HASREDACTEDPDFDOCUMENTS,
CASE WHEN Q01A.XNUM40 IS NOT NULL THEN Q01A.XNUM40 
WHEN Q01I.XNUM40 IS NOT NULL THEN Q01I.XNUM40
WHEN Q01S.XNUM40 IS NOT NULL THEN Q01S.XNUM40
ELSE 0 END AS WORKFLOW_T

from LD0VAA0T43 T43
LEFT JOIN LD0P0H A   
ON T43.AGARNR = A.AKTUALNR  AND A.AKTUALNR > 0 
LEFT JOIN LD0P0Q01 Q01A     
ON Q01A.KODTYP ='AKTUAL' AND Q01A.KOD = A.AKTUAL 
LEFT JOIN LD0P0C I
ON T43.AGARNR = I.AEVERKNR  AND I.AEVERKNR > 0 AND I.TMARK = 0  
LEFT JOIN LD0P0Q01 AS Q01I 
ON Q01I.KODTYP = 'UTREDTYP' AND Q01I.KOD = I.AEVERKTYP  
LEFT JOIN LD0P0C E   
ON T43.AGARNR = E.AEVERKNR  AND E.AEVERKNR > 0
LEFT JOIN LD0P0Q01 AS Q01E 
ON Q01E.KODTYP = 'JOURNALTYP' AND Q01E.KOD = E.AEVERKTYP 
LEFT JOIN LD0P0C20 S   
ON T43.AGARNR = S.INSNR  AND S.INSNR > 0
LEFT JOIN LD0P0Q01 AS Q01S 
ON Q01S.KODTYP = 'INSATS' AND Q01S.KOD = S.INSTYP 
LEFT JOIN LD0P0H10 H10 
ON T43.AGARNR = H10.SAMTNR
LEFT JOIN LD0P0B B     
ON H10.AKTNR = B.AKTNR  
LEFT JOIN LD0P0Q01 AS Q01B01   
ON Q01B01.KODTYP = 'AKTTYP' AND Q01B01.KOD = B.TYPNR
LEFT JOIN LD0VYPDF PDF ON PDF.OBJEKT = 'IFO.InfogatDokument' AND PDF.OBJEKTNYCKEL = T43.INFOGANR AND PDF.DOKUMENTID IN (
select DOKUMENTID FROM LD0P0T45 T45  WHERE T45.PARENTDOCUMENTID = PDF.DOKUMENTID)
Go
GRANT ALL ON LD0VAA0DocumentPdf TO LD0ROANV
GO

CREATE OR ALTER VIEW LD0VAA0ScannedDocuments AS 
select T60.*,
CASE WHEN Q01I.KLARTEXT IS NOT NULL THEN Q01I.KLARTEXT
WHEN Q01A.KLARTEXT IS NOT NULL THEN Q01A.KLARTEXT 
WHEN Q01E.KLARTEXT IS NOT NULL THEN Q01E.KLARTEXT
WHEN Q01S.KLARTEXT IS NOT NULL THEN Q01S.KLARTEXT
END AS AGARTYP_T,
CASE WHEN Q01A.XNUM40 IS NOT NULL THEN Q01A.XNUM40 
WHEN Q01I.XNUM40 IS NOT NULL THEN Q01I.XNUM40
WHEN Q01S.XNUM40 IS NOT NULL THEN Q01S.XNUM40
ELSE 0 END AS WORKFLOW_T
from LD0V0T60 T60
LEFT JOIN LD0P0H A   
ON T60.AGARNR = A.AKTUALNR  AND A.AKTUALNR > 0
LEFT JOIN LD0P0Q01 Q01A     
ON Q01A.KODTYP ='AKTUAL' AND Q01A.KOD = A.AKTUAL 
LEFT JOIN 
LD0P0C I
ON T60.AGARNR = I.AEVERKNR  AND I.AEVERKNR > 0 AND I.TMARK = 0  
LEFT JOIN LD0P0Q01 AS Q01I 
ON Q01I.KODTYP = 'UTREDTYP' AND Q01I.KOD = I.AEVERKTYP  
LEFT JOIN LD0P0C E   
ON T60.AGARNR = E.AEVERKNR  AND E.AEVERKNR > 0
LEFT JOIN LD0P0Q01 AS Q01E 
ON Q01E.KODTYP = 'JOURNALTYP' AND Q01E.KOD = E.AEVERKTYP  
LEFT JOIN LD0P0C20 S   
ON T60.AGARNR = S.INSNR  AND S.INSNR > 0
LEFT JOIN LD0P0Q01 AS Q01S 
ON Q01S.KODTYP = 'INSATS' AND Q01S.KOD = S.INSTYP 
GO
GRANT ALL ON LD0VAA0ScannedDocuments TO LD0ROANV
GO

CREATE OR ALTER VIEW LD0VAAYScannedDocuments AS 
select T60.*,
CASE WHEN Q01I.KLARTEXT IS NOT NULL THEN Q01I.KLARTEXT
WHEN Q01A.KLARTEXT IS NOT NULL THEN Q01A.KLARTEXT 
WHEN Q01E.KLARTEXT IS NOT NULL THEN Q01E.KLARTEXT
WHEN Q01S.KLARTEXT IS NOT NULL THEN Q01S.KLARTEXT
END AS AGARTYP_T,
CASE WHEN Q01A.XNUM40 IS NOT NULL THEN Q01A.XNUM40 
WHEN Q01I.XNUM40 IS NOT NULL THEN Q01I.XNUM40
WHEN Q01S.XNUM40 IS NOT NULL THEN Q01S.XNUM40
ELSE 0 END AS WORKFLOW_T
from LD0VYT60 T60
LEFT JOIN LD0P0H A   
ON T60.AGARNR = A.AKTUALNR  AND A.AKTUALNR > 0
LEFT JOIN LD0P0Q01 Q01A     
ON Q01A.KODTYP ='AKTUAL' AND Q01A.KOD = A.AKTUAL 
LEFT JOIN LD0P0C I
ON T60.AGARNR = I.AEVERKNR  AND I.AEVERKNR > 0 AND I.TMARK = 0  
LEFT JOIN LD0P0Q01 AS Q01I 
ON Q01I.KODTYP = 'UTREDTYP' AND Q01I.KOD = I.AEVERKTYP  
LEFT JOIN LD0P0C E   
ON T60.AGARNR = E.AEVERKNR  AND E.AEVERKNR > 0
LEFT JOIN LD0P0Q01 AS Q01E 
ON Q01E.KODTYP = 'JOURNALTYP' AND Q01E.KOD = E.AEVERKTYP  
LEFT JOIN LD0P0C20 S   
ON T60.AGARNR = S.INSNR  AND S.INSNR > 0
LEFT JOIN LD0P0Q01 AS Q01S 
ON Q01S.KODTYP = 'INSATS' AND Q01S.KOD = S.INSTYP 
GO
GRANT ALL ON LD0VAAYScannedDocuments TO LD0ROANV
GO

CREATE OR ALTER VIEW LD0VAA0Documents
AS
SELECT  
T41.*,  
Q01.KLARTEXT ANTTYP_T,   
CASE WHEN EXISTS(SELECT 'X' FROM LD0P0C C WHERE C.AEVERKNR = T41.AGARNR AND T41.AGARTYP = 42 AND C.TMARK = 1) THEN 1 ELSE 0 END AS ANTTYP_TMARK,  
Q01B.KLARTEXT DOKTYP_T,   
Q01B.XNUM4 BLANKETT,  
Q01C.KLARTEXT LNDOKTYP_T,  
Q01D.KLARTEXT ANTECKNTYP_T,
CASE WHEN Q01I.KLARTEXT IS NOT NULL THEN Q01I.KLARTEXT
WHEN Q01A.KLARTEXT IS NOT NULL THEN Q01A.KLARTEXT 
WHEN Q01E.KLARTEXT IS NOT NULL THEN Q01E.KLARTEXT
WHEN Q01S.KLARTEXT IS NOT NULL THEN Q01S.KLARTEXT
WHEN Q01B01.KLARTEXT IS NOT NULL THEN Q01B01.KLARTEXT
END AS AGARTYP_T,
CASE WHEN PDF.DOKUMENTID IS NOT NULL THEN 1 ELSE 0 END AS HASREDACTEDPDFDOCUMENTS,
CASE WHEN Q01A.XNUM40 IS NOT NULL THEN Q01A.XNUM40 
     WHEN Q01I.XNUM40 IS NOT NULL THEN Q01I.XNUM40
     WHEN Q01S.XNUM40 IS NOT NULL THEN Q01S.XNUM40
     ELSE 0 END AS WORKFLOW_T
FROM LD0V1T41 T41   
LEFT JOIN LD0P0Q01 Q01   
ON T41.ANTDOKTYP = Q01.KOD AND Q01.KODTYP = 'ANTTYP'  
AND T41.ANTDOKTYP > 0  
LEFT JOIN LD0P0Q01 Q01B  
ON T41.DOKTYP = Q01B.KOD AND Q01B.KODTYP = 'TYPDOK'   
AND T41.DOKTYP > 0  
LEFT JOIN LD0P0Q01 Q01C  
ON T41.LNDOKTYP = Q01C.KOD AND Q01C.KODTYP = 'LNDOKTYP'   
AND T41.LNDOKTYP > 0  
LEFT JOIN LD0P0Q01 Q01D  
ON T41.ANTECKNTYP = Q01D.KOD AND Q01D.KODTYP = 'ANTECKNTYP'   
AND T41.ANTECKNTYP > 0  

LEFT JOIN LD0P0H A   
ON T41.AGARNR = A.AKTUALNR  AND A.AKTUALNR > 0 AND T41.AGARTYP = 20
LEFT JOIN LD0P0Q01 Q01A     
ON Q01A.KODTYP ='AKTUAL' AND Q01A.KOD = A.AKTUAL 
LEFT JOIN LD0P0C I
ON T41.AGARNR = I.AEVERKNR  AND I.AEVERKNR > 0 AND I.TMARK = 0  AND T41.AGARTYP IN (21,42,51)
LEFT JOIN LD0P0Q01 AS Q01I 
ON Q01I.KODTYP = 'UTREDTYP' AND Q01I.KOD = I.AEVERKTYP  
LEFT JOIN LD0P0C E   
ON T41.AGARNR = E.AEVERKNR  AND E.AEVERKNR > 0 AND T41.AGARTYP IN (22,42,52)
LEFT JOIN LD0P0Q01 AS Q01E 
ON Q01E.KODTYP = 'JOURNALTYP' AND Q01E.KOD = E.AEVERKTYP
LEFT JOIN LD0P0C20 S   
ON T41.AGARNR = S.INSNR  AND S.INSNR > 0 AND T41.AGARTYP IN (23,41,53)
LEFT JOIN LD0P0Q01 AS Q01S 
ON Q01S.KODTYP = 'INSATS' AND Q01S.KOD = S.INSTYP 
LEFT JOIN LD0P0B B     
ON T41.AGARNR = B.AKTNR AND B.AKTNR > 0 
LEFT JOIN LD0P0Q01 AS Q01B01   
ON Q01B01.KODTYP = 'AKTTYP' AND Q01B01.KOD = B.TYPNR 
LEFT JOIN LD0VYPDF PDF ON PDF.OBJEKT = 'IFO.Dokument' AND PDF.OBJEKTNYCKEL = T41.DOKUMENTNR  AND PDF.DOKUMENTID IN (
select DOKUMENTID FROM LD0P0T45 T45  WHERE T45.PARENTDOCUMENTID = PDF.DOKUMENTID)
;  
  
GO

GRANT ALL ON LD0VAA0Documents TO LD0ROANV
GO

CREATE OR 
ALTER VIEW LD0VAAYDocuments
AS 

SELECT  
T41.*,  
Q01.KLARTEXT ANTTYP_T,   
CASE WHEN EXISTS(SELECT 'X' FROM LD0P0C C WHERE C.AEVERKNR = T41.AGARNR AND T41.AGARTYP = 42 AND C.TMARK = 1) THEN 1 ELSE 0 END AS ANTTYP_TMARK,  
Q01B.KLARTEXT DOKTYP_T,   
Q01B.XNUM4 BLANKETT,  
Q01C.KLARTEXT LNDOKTYP_T,  
Q01D.KLARTEXT ANTECKNTYP_T ,

CASE WHEN Q01I.KLARTEXT IS NOT NULL THEN Q01I.KLARTEXT
WHEN Q01A.KLARTEXT IS NOT NULL THEN Q01A.KLARTEXT 
WHEN Q01E.KLARTEXT IS NOT NULL THEN Q01E.KLARTEXT
WHEN Q01S.KLARTEXT IS NOT NULL THEN Q01S.KLARTEXT
WHEN Q01B01.KLARTEXT IS NOT NULL THEN Q01B01.KLARTEXT
END AS AGARTYP_T,
CASE WHEN PDF.DOKUMENTID IS NOT NULL THEN 1 ELSE 0 END AS HASREDACTEDPDFDOCUMENTS,
CASE WHEN Q01A.XNUM40 IS NOT NULL THEN Q01A.XNUM40 
     WHEN Q01I.XNUM40 IS NOT NULL THEN Q01I.XNUM40
     WHEN Q01S.XNUM40 IS NOT NULL THEN Q01S.XNUM40
     ELSE 0 END AS WORKFLOW_T
FROM LD0V0T41 T41   
LEFT JOIN LD0P0Q01 Q01   
ON T41.ANTDOKTYP = Q01.KOD AND Q01.KODTYP = 'ANTTYP'  
AND T41.ANTDOKTYP > 0  
LEFT JOIN LD0P0Q01 Q01B  
ON T41.DOKTYP = Q01B.KOD AND Q01B.KODTYP = 'TYPDOK'   
AND T41.DOKTYP > 0  
LEFT JOIN LD0P0Q01 Q01C  
ON T41.LNDOKTYP = Q01C.KOD AND Q01C.KODTYP = 'LNDOKTYP'   
AND T41.LNDOKTYP > 0  
LEFT JOIN LD0P0Q01 Q01D  
ON T41.ANTECKNTYP = Q01D.KOD AND Q01D.KODTYP = 'ANTECKNTYP'   
AND T41.ANTECKNTYP > 0  

LEFT JOIN LD0P0H A   
ON T41.AGARNR = A.AKTUALNR  AND A.AKTUALNR > 0 	 AND T41.AGARTYP = 20
LEFT JOIN LD0P0Q01 Q01A     
ON Q01A.KODTYP ='AKTUAL' AND Q01A.KOD = A.AKTUAL 
LEFT JOIN LD0P0C I 
ON T41.AGARNR = I.AEVERKNR  AND I.AEVERKNR > 0 AND I.TMARK = 0  AND T41.AGARTYP IN (21,42,51)
LEFT JOIN LD0P0Q01 AS Q01I 
ON Q01I.KODTYP = 'UTREDTYP' AND Q01I.KOD = I.AEVERKTYP  
LEFT JOIN LD0P0C E   
ON T41.AGARNR = E.AEVERKNR  AND E.AEVERKNR > 0 AND T41.AGARTYP IN (22,42,52)
LEFT JOIN LD0P0Q01 AS Q01E 
ON Q01E.KODTYP = 'JOURNALTYP' AND Q01E.KOD = E.AEVERKTYP
LEFT JOIN LD0P0C20 S   
ON T41.AGARNR = S.INSNR  AND S.INSNR > 0 AND T41.AGARTYP IN (23,41,53)
LEFT JOIN LD0P0Q01 AS Q01S 
ON Q01S.KODTYP = 'INSATS' AND Q01S.KOD = S.INSTYP 
LEFT JOIN LD0P0B B     
ON T41.AGARNR = B.AKTNR AND B.AKTNR > 0  
LEFT JOIN LD0P0Q01 AS Q01B01   
ON Q01B01.KODTYP = 'AKTTYP' AND Q01B01.KOD = B.TYPNR  
LEFT JOIN LD0VYPDF PDF ON PDF.OBJEKT = 'IFO.Dokument' AND PDF.OBJEKTNYCKEL = T41.DOKUMENTNR AND PDF.DOKUMENTID IN (
select DOKUMENTID FROM LD0P0T45 T45  WHERE T45.PARENTDOCUMENTID = PDF.DOKUMENTID)
GO

GRANT ALL ON LD0VAAYDocuments TO LD0ROANV
GO

IF OBJECT_ID ('LD0VAA0SALARY_INTEGRATION', 'V') IS NOT NULL
  DROP VIEW LD0VAA0SALARY_INTEGRATION;
GO
CREATE VIEW LD0VAA0SALARY_INTEGRATION
AS SELECT DISTINCT
L50.*
,C20.ORGID AS ORGID
,C20.ORGENHNR AS ORGEHNR
,L.RESURSBEN AS RESURS_BENAMNING
,L.ANSTALLNINGSNR AS RESURS_ANSTALLNINGNR
,Q01.KLARTEXT AS ARVODERING_T
,A.NAMN AS RESURS_NAMN
,A2.NAMN AS KLIENT_NAMN
FROM LD0V0L50 L50
INNER JOIN LD0V0L AS L ON L50.RESURSNR = L.RESURSNR
INNER JOIN LD0V0A AS A2 ON L50.PNR = A2.PNR
LEFT OUTER JOIN LD0V0N70 N70 ON L50.RESTILLDNR = N70.RESTILLDNR
INNER JOIN LD0V0C20 AS C20 ON C20.INSNR = N70.INSNR
LEFT OUTER JOIN LD0V0A AS A ON L50.LONPNR = A.PNR
LEFT OUTER JOIN LD0V0Q01 Q01 ON Q01.KOD = L50.ARVODERING AND Q01.KODTYP = 'ARVODERING'
GO

GRANT SELECT ON LD0VAA0SALARY_INTEGRATION TO LD0ROLAS;
GO

IF OBJECT_ID ('LD0VAA0ACCOUNT_REC_INTEGRATION', 'V') IS NOT NULL
  DROP VIEW LD0VAA0ACCOUNT_REC_INTEGRATION;
GO

CREATE VIEW LD0VAA0ACCOUNT_REC_INTEGRATION
AS SELECT
N20.*
,C20.AKTPNR
,C20.KORTKOD N70KORTKONTO
,C20.ORGID
,C20.ORGENHNR
,I.NAMN
,I.EPOST
,I.TELE1
,I.TELE2
,E.BESLUTNR
,E.DATUM
FROM LD0V0N20 N20
JOIN LD0V0C20 C20 ON N20.INSATSNR = C20.INSNR
JOIN LD0V0I I ON C20.HAND = I.HAND
JOIN LD0V0E E ON C20.BESLUTNR = E.BESLUTNR
GO

GRANT SELECT ON LD0VAA0ACCOUNT_REC_INTEGRATION TO LD0ROLAS;
GO

CREATE OR ALTER VIEW LD0VAA0AktualiseringSummary
AS
SELECT 
H.AKTUALNR,
H.INSNR,
H.AEVERKNR,
H.BESLUTNR,
H.AKTUAL,
T.KLARTEXT AKTUAL_T,
H.AKTUALTYP,
H.ADATUM, 
H.PNR,
H.HAND,
H.UPDDAT,
H.UPDSIGN,
H.HAND2,
H.RENSAD,
H.DIARIENR,
H.AKTNR,
H.REGTID,
H.SPOTCHECK,
P.NAMN,
P.KUNDNUMMER,
C.STARTDAT INV_STARTDAT,
C.STOPDAT INV_STOPDAT,
C.AEVERKTYP INV_AEVERKTYP,
QC.KLARTEXT INV_AEVERKTYP_T,
QC.XNUM40 WORKFLOW_T_C,
C20.FOMDAT SERV_FOMDAT,
C20.TOMDAT SERV_TOMDAT,
C20.INSTYP SERV_INSTYP,
QC20.KLARTEXT INSTYP_T,
QC20.XNUM40 WORKFLOW_T_C20,
H.WORKFLOW,
T.XNUM40 AS WORKFLOW_T,
T1.KLARTEXT AS WORKFLOW_TEXT,
S.STATUS AS AKTUALSTATUS,
APP.BESLUTNR as APPEALDECISION
FROM LD0V0H H 
LEFT JOIN LD0P0A P 
ON H.PNR = P.PNR
INNER JOIN LD0V0Q01 T 
ON T.KODTYP ='AKTUAL' 
AND T.KOD = H.AKTUAL
LEFT OUTER JOIN LD0V0Q01 T1 
ON T1.KODTYP ='LCFLOW' 
AND T1.KOD = T.XNUM40
LEFT JOIN LD0P0C C 
ON C.AEVERKNR = H.AEVERKNR
LEFT JOIN LD0V0Q01 QC 
ON QC.KODTYP = 'UTREDTYP' 
AND QC.KOD = C.AEVERKTYP
LEFT JOIN LD0P0C20 C20 
ON C20.INSNR = H.INSNR
LEFT JOIN LD0V0Q01 QC20
ON QC20.KODTYP ='INSATS' 
AND QC20.KOD = C20.INSTYP
LEFT JOIN LD0P0AktualiseringStatus S
ON H.AKTUALNR=S.AKTUALNR
LEFT JOIN LD0V0APPEAL APP
ON APP.AKTUALNR = H.AKTUALNR
GO

GRANT ALL ON LD0VAA0AktualiseringSummary TO LD0ROANV
GO

CREATE OR ALTER VIEW LD0V0U61 AS SELECT * FROM LD0P0U61
GO

CREATE OR ALTER VIEW LD0VAA0InternshipRequirement
AS 
SELECT
 U61.*
,Q1.KLARTEXT AS EFTYP_T
,Q2.KLARTEXT AS ERFARFARDIG_T
,Q3.KLARTEXT AS EFTYP2_T
,Q4.KLARTEXT AS ERFARFARDIG2_T
,Q5.KLARTEXT AS OVRFORD_T
,Q6.KLARTEXT AS SPRAK1_T
,Q7.KLARTEXT AS SPRAK2_T
,Q8.KLARTEXT AS KON_T
,Q9.KLARTEXT AS FODLAND_T

FROM LD0V0U61 AS U61 
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
;
GO

CREATE OR ALTER 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,
ISNULL(U61.KORKORTMANUELL,0) as KORKORTMANUELL,
U60.ANORDNARNR,
U60.UTFORARNR,
U60.ADRESS,
U60.POSTNR,
U60.ORT,
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 LD0V1U60 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 LD0VYU50 as U50_ANORDNARE
ON U50_ANORDNARE.ANORDUTFORNR = U60.ANORDNARNR
LEFT OUTER JOIN LD0VYU50 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
GO

CREATE OR ALTER VIEW LD0VAA0LMSEARCHSIMPLEPLACE AS
SELECT
U70.ENKELPLATSNR,
U70.INAKTIV,
U70.ORGANISATIONSNR,
U70.ARBETSUPPGIFT,
U70.BENAMNING,
U70.HUVUDMAN,
U70.BRANSCH,
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,
ISNULL(U61.KORKORTMANUELL,0) as KORKORTMANUELL,
U61.ERFARFARDIG,
U61.ERFARFARDIG2,
U61.EFTYP,
U61.EFTYP2,
U61.SPRAK1,
U61.SPRAK2,
U61.FODLAND,
U61.KON,
U61.ALDERFROM,
U61.ALDERTOM,
U61.OVRFORD,
U70.KONTAKT,
U70.KONTTEL1,
U70.KONTTEL2,
U70.KONTEPOST,
U70.KONTADRESS,
U70.KONTPOSTNR,
U70.KONTORT,
U70.HANDLEDARE,
U70.HANDLTEL1,
U70.HANDLTEL2,
U70.HANDLEPOST,
U70.HANDLADRESS,
U70.HANDLPOSTNR,
U70.HANDLORT,
U70.ADRESS,
U70.POSTNR,
U70.ORT,
U70.UPDDAT,
U70.UPDSIGN
,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
,ISNULL(Q10.KLARTEXT, '') AS BRANSCH_T
,ISNULL(Q11.KLARTEXT, '') AS HUVUDMAN_T
FROM LD0V0U70 U70
LEFT OUTER JOIN LD0V0U61 AS U61
ON U70.ENKELPLATSNR = U61.ENKELPLATSNR
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
LEFT OUTER JOIN LD0V0Q01 AS Q10 ON Q10.KODTYP = 'LNBRANSCH' AND U70.BRANSCH = Q10.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q11 ON Q11.KODTYP = 'LNHUVUDMAN' AND U70.HUVUDMAN = Q11.KOD
GO

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

CREATE VIEW LD0VAA0AktualiseringAppealSummary
AS
SELECT 
A.AKTUALNR,
A.INSNR,
A.AEVERKNR,
A.BESLUTNR,
A.AKTUAL,
A.AKTUAL_T,
A.ADATUM, 
A.PNR,
A.NAMN,
A.HANDNAMN,
A.HAND2NAMN,
A.AORSAK_T,
A.AVEM_T,
A.ORG_T,
A.KUNDNUMMER,
A.WORKFLOW_T,
T1.KLARTEXT as WORKFLOW_TEXT,
S.STATUS as AKTUALSTATUS,
APPEAL.BESLUTNR as APPEALDECISIONID,
APPEAL.APPEALEDDECISION,
APPEAL.REASONFORAPPEAL,
E.DATUM as APPEALEDDECISIONDATE
FROM LD0VAA0AKTUALISERING A 
LEFT JOIN LD0V0Q01 T1 
ON T1.KODTYP ='LCFLOW' 
AND T1.KOD = WORKFLOW_T
LEFT JOIN LD0P0AktualiseringStatus S
ON A.AKTUALNR = S.AKTUALNR
JOIN LD0V0APPEAL APPEAL
ON APPEAL.AKTUALNR = A.AKTUALNR
JOIN LD0VYE E
ON E.BESLUTNR = APPEAL.BESLUTNR
GO

GRANT ALL ON LD0VAA0AktualiseringAppealSummary TO LD0ROANV
GO


CREATE OR ALTER 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,
    Q01M.KLARTEXT AS U34_OVRIG1_T,Q01N.KLARTEXT AS U34_OVRIG2_T,Q01O.KLARTEXT AS U34_OVRIG3_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,  Q01L.KLARTEXT AS U10_TYPAVSLUT_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.EPOSTHEM AS A_EPOSTHEM, 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, 
      '20' + SUBSTRING(U35.REGDATUM,1,2) + '-' + SUBSTRING(U35.REGDATUM,3,2)  + '-' + SUBSTRING(U35.REGDATUM,5,2) + ' ' + SUBSTRING(U35.REGTID,1,2) + ':' + SUBSTRING(U35.REGDATUM,3,2) AS U35_REGDATUMTID,
	  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,
  CASE WHEN U34.INTROARB = 2
  THEN U35.PLATSFROM
  ELSE U34.VERKSTART
  END
  AS VERKSTART,
  CASE WHEN U34.INTROARB = 2
  THEN U35.PLATSTOM
  ELSE U34.VERKSLUT
  END
  AS VERKSLUT,
  CASE WHEN U34.INTROARB = 2
  THEN (SELECT MIN(NULLIF(U35.PLATSFROM, '')) FROM LD0V0U35 AS U35 WHERE U34.PLANAKTIVITETNR=U35.PLANAKTIVITETNR AND U34.INTROARB=2 AND U35.STATUS=2)
  ELSE U34.VERKSTART
  END
  AS FORSTAVERKSTART,
  CASE WHEN U34.INTROARB = 2
  THEN (SELECT MAX(NULLIF(U35.PLATSTOM, '')) FROM LD0V0U35 AS U35 WHERE U34.PLANAKTIVITETNR=U35.PLANAKTIVITETNR AND U34.INTROARB=2 AND U35.STATUS=2)
  ELSE U34.VERKSLUT
  END
  AS SISTAVERKSLUT
FROM LD0V0U10 U10 
JOIN LD0VYA 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 LD0VYU30 AS U30 ON U30.INSKRIVNINGSNR = U10.INSKRIVNINGSNR
JOIN LD0VYU34 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 LD0V0Q01 AS Q01L ON Q01L.KOD = U10.TYPAVSLUT AND Q01L.KODTYP = 'LNTYPAVSL'
LEFT OUTER JOIN LD0V0Q01 AS Q01M ON Q01M.KOD = U34.OVRIG1 AND Q01M.KODTYP = 'LNOVRIG1'
LEFT OUTER JOIN LD0V0Q01 AS Q01N ON Q01N.KOD = U34.OVRIG2 AND Q01N.KODTYP = 'LNOVRIG2'
LEFT OUTER JOIN LD0V0Q01 AS Q01O ON Q01O.KOD = U34.OVRIG3 AND Q01O.KODTYP = 'LNOVRIG3'
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
GO
GRANT ALL ON LD0VAA0LMSearchActivity TO LD0ROANV
GO