IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0COMPILATIONEGNAMEDEL')
BEGIN
DROP VIEW LD0V0COMPILATIONEGNAMEDEL
END
GO
CREATE VIEW LD0V0COMPILATIONEGNAMEDEL      
AS      
SELECT       
	C20.INSNR AS InsatNumber,      
	C20.AKTPNR AS PersonId,      
	A01.NAMN AS PersonName,      
	C20.HAND AS Caseworker,      
	VI1.NAMN AS CaseworkerName,      
	C20.MEDHAND AS CoCaseworker,      
	VI2.NAMN As CoCaseworkerName,    
	J01.ORGID AS OrganizationId,    
	J01.ORGENHNR AS OrganizationUnitId,    
	J01.Namn AS OrganizationName,      
	C20.INKONTO,      
	C20.UTKONTO,    
	C20.FOMDAT AS FromDate,  
	C20.TOMDAT AS ToDate,
	C20.SALFMED AS StartBalance,
	C20.INSTYP AS ServiceType
FROM LD0V0C20 C20      
INNER JOIN LD0V0Q01 Q01 ON Q01.KODTYP = 'INSATS' AND Q01.KOD = C20.INSTYP AND Q01.XNUM2 = 2   
LEFT JOIN LD0V0I VI1 ON VI1.HAND = C20.HAND      
LEFT JOIN LD0V0I VI2 ON VI2.HAND = C20.MEDHAND       
LEFT JOIN LD0V0J01 J01  ON J01.ORGID = C20.ORGID AND J01.ORGENHNR = C20.ORGENHNR     
LEFT JOIN LD0V0J J0  ON J0.ORGID = J01.ORGID   
LEFT JOIN  LD0V0A   A01 ON C20.AKTPNR = A01.PNR      
GO
GRANT ALL ON LD0V0COMPILATIONEGNAMEDEL TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0SEARCHSALARY')
BEGIN
DROP VIEW LD0VAA0SEARCHSALARY
END
GO
CREATE VIEW LD0VAA0SEARCHSALARY
AS SELECT
L50.*
,L.PNR as RESOURCEPERSONID
,L.RESURSBEN as RESOURCENAME
,L01.RESURSTYP_T as ASRESURSTYP_T
,C20.AKTPNR as CLIENTPERSONID
,C20.FOMDAT as SERVICE_FOMDAT
,C20.INSTYP_T
,N70.RESURSTYP_T
,N70.LOENID
,A.NAMN
,PER.KUNDNUMMER
,Q01.KLARTEXT as ARVODERING_T
,L2.RESURSBEN AS LONPNR_NAMN_RES
,L03.NAMN AS LONPNR_NAMN_HUS
,A2.NAMN AS LONPNR_NAMN_PER
FROM LD0V0L50 L50
LEFT OUTER JOIN LD0VAAYRESOURCEALLOCATION N70 ON N70.RESTILLDNR = L50.RESTILLDNR
LEFT OUTER JOIN LD0VAAYSERVICES AS C20 ON C20.INSNR = N70.INSNR
LEFT OUTER JOIN LD0P0L AS L ON L.RESURSNR = L50.RESURSNR
LEFT OUTER JOIN LD0VAA0RESOURCEAS AS L01 ON L01.RESURSSOMNR = L50.RESURSSOMNR
LEFT OUTER JOIN LD0P0A AS A ON A.PNR = C20.AKTPNR
LEFT OUTER JOIN LD0P0A AS PER ON PER.PNR = L50.PNR
LEFT OUTER JOIN LD0V0Q01 Q01 ON Q01.KODTYP = 'ARVODERING' AND Q01.KOD = L50.ARVODERING
LEFT OUTER JOIN LD0P0L AS L2 ON L2.PNR = L50.LONPNR
LEFT OUTER JOIN LD0P0L03 AS L03 ON L03.PNR = L50.LONPNR AND L03.RESURSNR = L50.RESURSNR
LEFT OUTER JOIN LD0P0A AS A2 ON A2.PNR = L50.LONPNR
GO
GRANT SELECT ON LD0VAA0SEARCHSALARY TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0SALARY')
BEGIN
DROP VIEW LD0VAA0SALARY
END
GO
CREATE VIEW LD0VAA0SALARY
AS SELECT
L50.*
,L.PNR AS RESOURCEPERSONID
,C20.AKTPNR AS CLIENTPERSONID
,Q01.KLARTEXT AS ARVODERING_T
,N70.TOMDAT
,L2.RESURSBEN AS LONPNR_NAMN_RES
,L03.NAMN AS LONPNR_NAMN_HUS
,A.NAMN AS LONPNR_NAMN_PER
FROM LD0V0L50 L50
LEFT OUTER JOIN LD0VYN70 N70 ON N70.RESTILLDNR = L50.RESTILLDNR
LEFT OUTER JOIN LD0VYC20 AS C20 ON C20.INSNR = N70.INSNR
LEFT OUTER JOIN LD0P0L AS L ON L.RESURSNR = L50.RESURSNR
LEFT OUTER JOIN LD0V0Q01 Q01 ON Q01.KOD = L50.ARVODERING AND Q01.KODTYP = 'ARVODERING' 
LEFT OUTER JOIN LD0P0L AS L2 ON L2.PNR = L50.LONPNR
LEFT OUTER JOIN LD0P0L03 AS L03 ON L03.PNR = L50.LONPNR AND L03.RESURSNR = L50.RESURSNR
LEFT OUTER JOIN LD0P0A AS A ON A.PNR = L50.LONPNR
GO
GRANT ALL ON LD0VAA0SALARY TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0LMSearchActivity')
BEGIN
DROP VIEW LD0VAA0LMSearchActivity
END
GO
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,
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 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
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 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 SELECT ON LD0VAA0LMSearchActivity TO LD0ROANV
GO


IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0COMPILATIONINVSER')
BEGIN
DROP VIEW LD0V0COMPILATIONINVSER
END
GO
CREATE VIEW LD0V0COMPILATIONINVSER
AS  
SELECT   
VLC.AKTPNR  AS PersonNumber,  
V0A.NAMN  AS PersonName,  
V0A.KUNDNUMMER  AS PersonKey,  
Q01.KODTYP  AS [Utredning/Insats],  
Q01.KLARTEXT AS Type,  
V0I.NAMN  AS Caseworker,   
VLC.HAND  AS CaseworkerId,    
VLC.MEDHAND  AS CoCaseworkerId,  
V02.NAMN  AS CoAdministrator,  
VLC.ORGID  AS OrganisationsID,  
VLC.ORGENHNR AS OrganisationNumber,   
J01.NAMN  AS Organisation,  
VLC.STARTDAT AS FromDate,  
VLC.STOPDAT  AS ToDate,  
VLC.FAMRTYP1 AS FAMRTYP1,  
VLC.FAMRTYP2 AS FAMRTYP2,  
VLC.FAMRTYP3 AS FAMRTYP3,  
C20.AVBRYT  AS IsAborted,  
Q02.KLARTEXT AS AbortCause,  
Q011.KLARTEXT AS Guardian,  
OMR.NAMN        AS Area  
FROM   
LD0VLC VLC   
INNER JOIN LD0V0I  V0I  ON  V0I.HAND = VLC.HAND  
INNER JOIN LD0P0J01  J01  ON VLC.ORGID = J01.ORGID AND VLC.ORGENHNR = J01.ORGENHNR      
INNER JOIN LD0V0Q01T Q01  ON Q01.KODTYP = 'UTREDTYP' AND VLC.AEVERKTYP = Q01.KOD  
LEFT JOIN  LD0V0I  V02  ON  V02.HAND = VLC.MEDHAND   
INNER JOIN LD0V0A  V0A  ON  V0A.PNR = VLC.AKTPNR  
LEFT JOIN  LD0V0C20T C20  ON  C20.PNR = VLC.AKTPNR 
LEFT JOIN  LD0V0Q01T Q011 ON Q011.KODTYP = 'VARDHAVARE' AND C20.VARDNHAV= Q011.KOD 
LEFT JOIN  LD0V0Q01T        Q02     ON  Q02.KODTYP = 'INSAVBORS' AND Q02.KOD = C20.AVBORS  
LEFT JOIN  LD0VAA0OMRADENKO OMR     ON  V0A.NYCKKOD = OMR.NYCKKOD  
WHERE VLC.TMARK = 0   
UNION ALL  
--Service  
SELECT   
C20.AKTPNR  AS PersonNumber,  
V0A.NAMN  AS PersonName,  
V0A.KUNDNUMMER  AS PersonKey,  
Q01.KODTYP  AS [Utredning/Insats],  
Q01.KLARTEXT AS Type,  
V0I.NAMN  AS Caseworker,   
C20.HAND  AS CaseworkerId,  
C20.MEDHAND  AS CoCaseworkerId,  
V02.NAMN  AS CoAdministrator,  
C20.ORGID  AS OrganisationsID,  
C20.ORGENHNR AS OrganisationNumber,   
J01.NAMN  AS Organisation,  
C20.FOMDAT  AS FromDate,  
C20.TOMDAT  AS ToDate,  
0    AS FAMRTYP1,  
0    AS FAMRTYP2,  
0    AS FAMRTYP3,  
C20.AVBRYT  AS IsAborted,  
Q02.KLARTEXT AS AbortCause,  
Q011.KLARTEXT AS Guardian,  
OMR.NAMN        AS Area  
FROM  
LD0VLC20     C20  
INNER JOIN LD0V0Q01T  Q01  ON Q01.KODTYP = 'INSATS' AND C20.INSTYP = Q01.KOD  
INNER JOIN LD0V0A   V0A  ON V0A.PNR = C20.AKTPNR  
LEFT JOIN  LD0P0J01   J01  ON C20.ORGID = J01.ORGID AND C20.ORGENHNR = J01.ORGENHNR      
LEFT JOIN  LD0V0I   V0I  ON V0I.HAND = C20.HAND  
LEFT JOIN  LD0V0I   V02  ON V02.HAND = C20.MEDHAND   
LEFT JOIN  LD0V0Q01T  Q011 ON Q011.KODTYP = 'VARDHAVARE' AND C20.VARDNHAV = Q011.KOD  
LEFT JOIN  LD0V0Q01T        Q02     ON  Q02.KODTYP = 'INSAVBORS' AND Q02.KOD = C20.AVBORS  
LEFT JOIN  LD0VAA0OMRADENKO OMR     ON  V0A.NYCKKOD = OMR.NYCKKOD  
GO
GRANT ALL ON LD0V0COMPILATIONINVSER TO LD0ROANV
GO


