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

GO

CREATE VIEW LD0VAA0ActivityLabourMarket
AS 
SELECT
DISTINCT
 U35.*,
 Q1.KLARTEXT AS STATUS_T,
 U60.BENAMNING AS PRAKPROJ_T,
 U10.PERSONNR AS U10_PNR,
 A.NAMN AS PERSON_NAMN,
 I.HAND AS HAND,
 I.NAMN AS HAND_T,
 U34.PLANNR AS U34_PLANNR,
 U34.VERSIONNR AS U34_VERSIONNR,
 U34.PLANSTART AS U34_PLANSTART,
 U34.PLANSLUT AS U34_PLANSLUT,
 U34.AKTIVITET AS U34_AKTIVITET,
 Q1_2.KLARTEXT AS U34_AKTIVITET_T,
 Q1_3.KLARTEXT AS U34_AKTIVITET_TYP_T,
 Q1_3.XNUM7 AS AKTIVITETSCHEMATILLATEN
FROM LD0V0U35 AS U35

LEFT OUTER JOIN LD0V0U60 AS U60 ON U35.PRAKPROJNR = U60.PRAKPROJNR
LEFT OUTER JOIN LD0VYU34 AS U34 ON U35.PLANAKTIVITETNR = U34.PLANAKTIVITETNR
LEFT OUTER JOIN LD0VYU30 AS U30 ON U34.PLANNR = U30.PLANNR
LEFT OUTER JOIN LD0VYU10 AS U10 ON U30.INSKRIVNINGSNR = U10.INSKRIVNINGSNR
INNER JOIN LD0V0A AS A ON U10.PERSONNR = A.PNR
LEFT OUTER JOIN LD0V0I AS I ON U34.HAND = I.HAND
LEFT OUTER JOIN LD0V0Q01 AS Q1 ON Q1.KODTYP = 'LNSTATUS' AND U35.STATUS = Q1.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q1_2 ON Q1_2.KODTYP = 'LNAKTIV' AND U34.AKTIVITET = Q1_2.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q1_3 ON Q1_3.KODTYP = 'LNAKTIVTYP' AND U34.AKTIVTYP = Q1_3.KOD

GO
GRANT ALL ON LD0VAA0ActivityLabourMarket TO LD0ROANV
GO

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

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,
      Q2.XNUM7   AS AKTIVITETSCHEMATILLATEN,
      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
      INNER 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,
      Q2.XNUM7   AS AKTIVITETSCHEMATILLATEN,
      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
      INNER 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
      GO

GRANT ALL ON LD0VAA0LMEnrollmentPlanActivity TO LD0ROANV
GO

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

CREATE VIEW LD0VAAYLMEnrollmentPlanActivity
  AS
    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 as VERKSTART,
      CASE WHEN ISNULL(U35.PLATSTOM, ' ') =' ' THEN U34.VERKSLUT ELSE U35.PLATSTOM END as 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
      ,Q2.XNUM7   AS AKTIVITETSCHEMATILLATEN
      ,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 LD0VYU34 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
      INNER 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
	  LEFT OUTER JOIN LD0V0U35 AS U35 ON U35.PLANAKTIVITETNR = U34.PLANAKTIVITETNR AND U34.INTROARB = 2 AND U35.STATUS = 2
GO

GRANT ALL ON LD0VAAYLMEnrollmentPlanActivity TO LD0ROANV
GO

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

CREATE VIEW LD0VAA0SearchProfessionals AS 
SELECT
I.*,
I02.BESADR AS BESADR_T,
I02.ORT AS BESORT_T,
Q01.KLARTEXT AS DELEGATION_T,
I04.ANVID,
I02_2.BESADR AS BESADR2_T,
I02_2.ORT AS BESORT2_T
FROM LD0V0I I 
LEFT OUTER JOIN LD0V0Q01 Q01 ON Q01.KODTYP = 'DELEGATION' AND Q01.KOD = I.DELEGATION 
LEFT OUTER JOIN LD0V0I02 I02 ON I02.ADRKOD = I.ADRKOD 
LEFT OUTER JOIN LD0V0I04 I04 ON I04.HAND = I.HAND 
LEFT OUTER JOIN LD0V0I02 I02_2 ON I02_2.ADRKOD = I.ADRKODSDN
GO

GRANT SELECT ON LD0VAA0SearchProfessionals TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0SearchResourceAllocations')
BEGIN
    DROP VIEW LD0VAA0SearchResourceAllocations
END
GO
CREATE VIEW LD0VAA0SearchResourceAllocations AS   
SELECT  N70.*,  
  A.NAMN AS PERSONNAME,  
  A.PNR,  
  A.ADRESS,  
  A.FODDAT,  
  A.POSTNR,  
  A.ORT,  
  A.COADR AS PERSONCOADDRESS,  
  A.KON,  
  A.GRUPP,  
  A.KUNDNUMMER,  
  I_1.HAND AS CASEWORKERID,  
  I_1.NAMN AS CASEWORKER,  
  I_2.HAND AS COCASEWORKERID,  
  I_2.NAMN AS COCASEWORKER,  
  C20.ORGID AS INSORGID,  
  C20.ORGENHNR AS INSORGENHNR,  
  J01.NAMN AS INSORGNAMN,  
  C20.FOMDAT AS INSFOMDAT,  
  C20.TOMDAT AS INSTOMDAT,  
  L01.RESURSTYP,  
  L.RESURSNR,  
  L.RESURSKAT,  
  L.RESURSBEN,  
  L.PNR AS RESURSPNR,  
  E_1.DATUM AS STARTBESLDATE,  
  E_2.DATUM AS STOPBESLDATE,  
  Q01_1.KLARTEXT AS HEMTYP_T,  
  Q01_2.KLARTEXT AS RESURSTYP_T,  
  Q01_3.KLARTEXT AS RESURSKAT_T,  
  Q01_4.KLARTEXT AS STARTBESLTYP_T,  
  Q01_5.KLARTEXT AS STOPBESLTYP_T,  
        Q01_6.KLARTEXT AS INSTYP_T,
  L.COMPANY		
FROM LD0V0N70 N70   
 INNER JOIN LD0VYC20 C20 ON C20.INSNR = N70.INSNR   
 JOIN LD0V0A A ON A.PNR = C20.AKTPNR  
 LEFT OUTER JOIN LD0V0J01 J01 ON J01.ORGID = C20.ORGID AND J01.ORGENHNR = C20.ORGENHNR   
 LEFT OUTER JOIN LD0V0I I_1 ON C20.HAND = I_1.HAND   
 LEFT OUTER JOIN LD0V0I I_2 ON C20.MEDHAND = I_2.HAND   
 LEFT OUTER JOIN LD0V0L01 L01 ON N70.RESURSSOMNR = L01.RESURSSOMNR   
 LEFT OUTER JOIN LD0V0L L ON L.RESURSNR = L01.RESURSNR  
 LEFT OUTER JOIN LD0VYE E_1 ON E_1.BESLUTNR = N70.BESLUTNRSTART  
 LEFT OUTER JOIN LD0VYE E_2 ON E_2.BESLUTNR = N70.BESLUTNRSTOP  
 LEFT OUTER JOIN LD0V0Q01 Q01_1 ON Q01_1.KODTYP = 'HEMTYP' AND Q01_1.KOD = N70.HEMTYP  
 LEFT OUTER JOIN LD0V0Q01 Q01_2  ON Q01_2.KODTYP = 'RESURSTYP' AND Q01_2.KOD = L01.RESURSTYP  
 LEFT OUTER JOIN LD0V0Q01 Q01_3  ON Q01_3.KODTYP = 'RESURSKAT' AND Q01_3.KOD = L.RESURSKAT  
 LEFT OUTER JOIN LD0V0Q01 Q01_4  ON Q01_4.KODTYP = 'BESLUT' AND Q01_4.KOD = E_1.BESLUT  
 LEFT OUTER JOIN LD0V0Q01 Q01_5  ON Q01_5.KODTYP = 'BESLUT' AND Q01_5.KOD = E_2.BESLUT  
 LEFT OUTER JOIN LD0V0Q01 Q01_6  ON Q01_6.KODTYP = 'INSATS' AND Q01_6.KOD = C20.INSTYP  
GO
GRANT ALL ON LD0VAA0SearchResourceAllocations TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME = 'LD0ApiResourceAllocation')
BEGIN
DROP VIEW LD0ApiResourceAllocation
END
GO
CREATE VIEW LD0ApiResourceAllocation
AS  
SELECT N70.*,    
N70.STARTTIME,    
N70.ENDTIME,    
Q.KLARTEXT HEMTYP_T,     
L01.RESURSTYP, Q1.KLARTEXT RESURSTYP_T,     
L.RESURSBEN,    
(SELECT DISTINCT KUNDNUMMER FROM LD0P0A A WHERE EXISTS (SELECT * FROM LD0V0C20T C20 WHERE C20.AKTPNR=A.PNR AND C20.INSNR=N70.INSNR)) AS KUNDNUMMER,    
(SELECT DISTINCT PNR FROM LD0P0A A WHERE EXISTS (SELECT * FROM LD0V0C20T C20 WHERE C20.AKTPNR=A.PNR AND C20.INSNR=N70.INSNR)) AS PNR,    
Q01.KLARTEXT AS ResourceCategoryTypeText,    
L0.RESURSSTAT AS ResourceStatus,    
Q02.KLARTEXT AS ResourceStatusText,    
L0.INTEXT AS IsExternalResource,    
L0.AVTAL AS Agreement,    
L01.SCBSTAT AS TypeOfPlacementTypeId,    
Q2.KLARTEXT AS TypeOfPlacementTypeText,    
Q01_STATGROUP1.KLARTEXT AS StatGrp1,    
Q01_STATGROUP2.KLARTEXT AS StatGrp2,    
Q01_STATGROUP3.KLARTEXT AS StatGrp3,    
Q01_STATGROUP4.KLARTEXT AS StatGrp4,    
Q01_STATGROUP5.KLARTEXT AS StatGrp5    
FROM LD0P0N70 N70    
LEFT OUTER JOIN LD0V0Q01T Q ON Q.KODTYP = 'HEMTYP' AND Q.KOD = N70.HEMTYP    
LEFT JOIN LD0V0L01 L01 ON L01.RESURSSOMNR = N70.RESURSSOMNR    
LEFT JOIN LD0V0Q01T Q1 ON Q1.KODTYP = 'RESURSTYP' AND Q1.KOD = L01.RESURSTYP       
LEFT JOIN LD0VAA0RESOURCE L ON L.RESURSNR = L01.RESURSNR      
LEFT JOIN LD0V0Q01T Q2 ON Q2.KODTYP = 'PLFORM'  AND Q2.KOD = L01.SCBSTAT    
LEFT JOIN LD0V0L L0 ON L0.RESURSNR = L01.RESURSNR    
LEFT JOIN LD0V0Q01T Q01 ON Q01.KOD = L0.RESURSKAT AND Q01.KODTYP = 'RESURSKAT'    
LEFT JOIN LD0V0Q01T Q02  ON Q02.KODTYP = 'RESURSSTAT' AND Q02.KOD = L0.RESURSSTAT    
LEFT JOIN LD0V0Q01T Q03  ON Q03.KODTYP = 'PLFORM' AND Q03.KOD = L01.SCBSTAT    
LEFT JOIN LD0V0Q04 Q04_INSATS on Q04_INSATS.KODTYP = 'RESURSTYP' AND Q04_INSATS.KOD = L01.RESURSTYP    
LEFT JOIN LD0V0Q01T Q01_STATGROUP1 on Q01_STATGROUP1.KODTYP = 'STATGRUPP' AND Q01_STATGROUP1.XTXT1 = 'RESURSTYP' AND Q01_STATGROUP1.KOD=Q04_INSATS.STATGRP1      
LEFT JOIN LD0V0Q01T Q01_STATGROUP2 on Q01_STATGROUP2.KODTYP = 'STATGRUPP' AND Q01_STATGROUP2.XTXT1 = 'RESURSTYP' AND Q01_STATGROUP2.KOD=Q04_INSATS.STATGRP2      
LEFT JOIN LD0V0Q01T Q01_STATGROUP3 on Q01_STATGROUP3.KODTYP = 'STATGRUPP' AND Q01_STATGROUP3.XTXT1 = 'RESURSTYP' AND Q01_STATGROUP3.KOD=Q04_INSATS.STATGRP3      
LEFT JOIN LD0V0Q01T Q01_STATGROUP4 on Q01_STATGROUP4.KODTYP = 'STATGRUPP' AND Q01_STATGROUP4.XTXT1 = 'RESURSTYP' AND Q01_STATGROUP4.KOD=Q04_INSATS.STATGRP4      
LEFT JOIN LD0V0Q01T Q01_STATGROUP5 on Q01_STATGROUP5.KODTYP = 'STATGRUPP' AND Q01_STATGROUP5.XTXT1 = 'RESURSTYP' AND Q01_STATGROUP5.KOD=Q04_INSATS.STATGRP5    
GO
GRANT ALL ON LD0ApiResourceAllocation TO LD0ROANV
GO


IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME = 'LD0V0APIInvoiceForTreatment')
BEGIN
DROP VIEW LD0V0APIInvoiceForTreatment
END
GO
CREATE VIEW LD0V0APIInvoiceForTreatment AS 
SELECT N70.RESTILLDNR AS RESOURCEALLOCATIONID
	,A.NAMN AS PERSONNAME
	,A.PNR
	,A.KUNDNUMMER
	,I_1.NAMN AS CASEWORKER
	,J01.NAMN AS INSORGNAMN
	,N70.FOMDAT AS RESFOMDAT
	,N70.TOMDAT AS RESTOMDAT
	,L.RESURSBEN
	,Q01_2.KLARTEXT AS RESURSTYP_T   
FROM LD0P0N70 N70   
INNER JOIN LD0P0C20 C20 ON C20.INSNR = N70.INSNR   
JOIN LD0V0A A ON A.PNR = C20.AKTPNR   
LEFT OUTER JOIN LD0V0J01 J01 ON J01.ORGID = C20.ORGID AND J01.ORGENHNR = C20.ORGENHNR   
LEFT OUTER JOIN LD0V0I I_1 ON C20.HAND = I_1.HAND   
LEFT OUTER JOIN LD0V0L01 L01 ON N70.RESURSSOMNR = L01.RESURSSOMNR   
LEFT OUTER JOIN LD0V0L L ON L.RESURSNR = L01.RESURSNR   
LEFT OUTER JOIN LD0V0Q01T Q01_2 ON Q01_2.KODTYP = 'RESURSTYP' AND Q01_2.KOD = L01.RESURSTYP 
GO
GRANT ALL ON LD0V0APIInvoiceForTreatment TO LD0ROANV
GO

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

CREATE VIEW LD0ApiAktualisering
AS
SELECT H.*,   
H.STARTTIME,   
H.ENDTIME,   
T5.KLARTEXT AKTUALTYP_T,  
T.KLARTEXT AKTUAL_T,  
T3.KLARTEXT AVEM_T,  
T2.KLARTEXT AORSAK_T,  
I.NAMN CASEWORKER,  
I2.NAMN COCASEWORKER,  
T4.NAMN ORGNAMN,  
P.NAMN,  
P.ADRESS,  
P.COADR,  
P.ORT,   
P.POSTNR,  
P.HEMTEL,  
P.ARBTEL,  
P.MOBIL,  
P.EPOSTHEM,  
P.FODDAT,  
P.KON,  
P.KUNDNUMMER,  
T6.KLARTEXT AKTUAVSER_T,  
AKTS.STATUS AKTUALSTATUS,  
STATGRPVAL.STATGRP1,   
STATGRPVAL.STATGRP2,  
STATGRPVAL.STATGRP3,   
STATGRPVAL.STATGRP4,  
STATGRPVAL.STATGRP5,   
T10.KLARTEXT STATGRP1_T,  
T11.KLARTEXT STATGRP2_T,   
T12.KLARTEXT STATGRP3_T,  
T13.KLARTEXT STATGRP4_T,   
T14.KLARTEXT STATGRP5_T  
FROM LD0P0H H   
LEFT OUTER JOIN LD0V0A P   
ON H.PNR = P.PNR   
LEFT OUTER JOIN LD0V0I I   
ON I.HAND = H.HAND   
LEFT OUTER JOIN LD0V0Q01T T   
ON T.KODTYP = 'AKTUAL' AND T.KOD = H.AKTUAL   
LEFT OUTER JOIN LD0V0Q01T T5  
ON T5.KOD = H.AKTUALTYP AND T5.KODTYP = 'AKTUALTYP'   
LEFT OUTER JOIN LD0V0Q01T T2   
ON T2.KODTYP = 'AKTUORSAK' AND T2.KOD = H.AORSAK   
LEFT OUTER JOIN LD0V0Q01T T3   
ON T3.KOD = H.AVEM AND T3.KODTYP = 'AKTUFRÅN'   
LEFT OUTER JOIN LD0V0J01 T4   
ON T4.ORGID = H.ORGID AND T4.ORGENHNR = H.ORGENHNR   
LEFT OUTER JOIN LD0V0I I2   
ON I2.HAND = H.HAND2   
LEFT OUTER JOIN LD0V0Q01T T6  
ON T6.KOD = H.AVSER AND T6.KODTYP = 'AKTUAVSER'   
LEFT OUTER JOIN LD0V0AAktualiseringStatus AKTS  
ON AKTS.AKTUALNR = H.AKTUALNR  
LEFT OUTER JOIN LD0V0Q04 STATGRPVAL   
ON STATGRPVAL.KODTYP = 'AKTUAL'   
AND H.AKTUAL = STATGRPVAL.KOD   
LEFT OUTER JOIN LD0V0Q01T T10     
ON T10.KODTYP = 'STATGRUPP'     
AND T10.XTXT1 = 'AKTUAL'     
AND T10.KOD = STATGRPVAL.STATGRP1     
LEFT OUTER JOIN LD0V0Q01T T11     
ON T11.KODTYP = 'STATGRUPP'     
AND T11.XTXT1 = 'AKTUAL'     
AND T11.KOD = STATGRPVAL.STATGRP2     
LEFT OUTER JOIN LD0V0Q01T T12     
ON T12.KODTYP = 'STATGRUPP'     
AND T12.XTXT1 = 'AKTUAL'     
AND T12.KOD = STATGRPVAL.STATGRP3     
LEFT OUTER JOIN LD0V0Q01T T13     
ON T13.KODTYP = 'STATGRUPP'     
AND T13.XTXT1 = 'AKTUAL'     
AND T13.KOD = STATGRPVAL.STATGRP4     
LEFT OUTER JOIN LD0V0Q01T T14     
ON T14.KODTYP = 'STATGRUPP'     
AND T14.XTXT1 = 'AKTUAL'     
AND T14.KOD = STATGRPVAL.STATGRP5
GO
GRANT ALL ON LD0ApiAktualisering TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1APPEAL')
BEGIN
DROP VIEW LD0V1APPEAL
END
GO
CREATE VIEW LD0V1APPEAL   
AS 
SELECT APP.*, E.DATUM AS DECISIONDATE
FROM LD0P0APPEAL APP
LEFT JOIN LD0V0E E
ON APP.BESLUTNR = E.BESLUTNR
GO
GRANT ALL ON LD0V1APPEAL TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0AKTUALISERING')
BEGIN
DROP VIEW LD0VAA0AKTUALISERING
END
GO
CREATE VIEW LD0VAA0AKTUALISERING  
AS  
SELECT     H.AKTUALNR, H.INSNR, H.ORGID, H.AEVERKNR, H.BESLUTNR, H.AKTUAL, T.KLARTEXT AKTUAL_T, H.AKTUALTYP, H.BTYP, H.ADATUM, H.AVEM, H.AORSAK,T2.KLARTEXT AORSAK_T, H.ORGENHNR,   
                      H.PNR, H.HAND, H.UPDDAT, H.UPDSIGN, H.HAND2, H.ORGENHNR2, H.RENSAD, H.ATEXT1, H.ATEXT2, H.ATEXT3, H.ATEXT4, H.FAMRATTNR, H.MODER,   
                      H.STATUS, H.ATGARD, H.ATGORS, H.DIARIENR, H.AKTNR, H.AVBFROMDAT, H.AVBTOMDAT, H.AVBORSAK, H.REGTID, H.SPOTCHECK, H.NEXTMONTH, H.PROCDATUM,   
                      H.BIDRTFNR, H.AVSER, H.URSPRUNG, H.APPLICATIONID, H.ATTACHMENTURL, P.NAMN, P.KUNDNUMMER, T3.KLARTEXT AVEM_T,  
       J01.NAMN ORG_T, I.NAMN HANDNAMN, I2.NAMN HAND2NAMN, T.XNUM40 AS WORKFLOW_T, APP.REASONFORAPPEAL as REASONFORAPPEAL
	   ,APP.APPEALEDDECISION AS DECISIONFORAPPEAL, E.DATUM AS DECISIONDATE
FROM LD0V0H H   
INNER JOIN LD0V0A P   
ON H.PNR = P.PNR  
INNER JOIN LD0V0Q01 T   
ON T.KODTYP ='AKTUAL'   
AND T.KOD = H.AKTUAL  
INNER JOIN LD0V0Q01 T2   
ON T2.KODTYP ='AKTUORSAK'   
AND T2.KOD = H.AORSAK  
INNER JOIN LD0V0Q01 T3   
ON T3.KODTYP ='AKTUFRÅN'   
AND T3.KOD = H.AVEM  
LEFT JOIN LD0V0J01 J01  
ON H.ORGID = J01.ORGID   
AND H.ORGENHNR = J01.ORGENHNR   
LEFT JOIN LD0V0I I   
ON H.HAND = I.HAND   
LEFT JOIN LD0V0I I2   
ON H.HAND2 = I2.HAND  
LEFT JOIN LD0V0APPEAL APP  
ON APP.AKTUALNR = H.AKTUALNR  
LEFT JOIN LD0V0E E
ON APP.BESLUTNR = E.BESLUTNR
GO
GRANT ALL ON LD0VAA0AKTUALISERING TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME = 'LD0ApiPersons')
BEGIN
DROP VIEW LD0ApiPersons
END
GO
CREATE VIEW LD0ApiPersons 
AS SELECT           
PER.PNR   AS 'PERSONID',          
PER.KUNDNUMMER AS 'CUSTOMERNUMBER',          
PER.FODDAT  AS 'BIRTHDATE',          
PER.KON   AS 'GENDER',          
KOD1.KLARTEXT AS 'MARITALSTATUS',          
PER.NYCKKOD  AS 'KEYCODE',          
PER.OMRADE  AS 'AREAID' ,        
KOD6.KLARTEXT AS 'Area' ,      
KOD4.XTXT1 AS 'CITIZENSHIPCODE',
KOD2.KLARTEXT AS 'CITIZENSHIP',  
KOD5.XTXT1  AS 'BORNCOUNTRYCODE', 
KOD3.KLARTEXT AS 'BORNCOUNTRY',
KOD4.XTXT1  AS 'CITIZENSHIPCODEPOPULATIONREGISTRY',        
KOD4.KLARTEXT AS 'CITIZENSHIPPOPULATIONREGISTRY',        
KOD5.XTXT1  AS 'BORNCOUNTRYCODEPOPULATIONREGISTRY',        
KOD5.KLARTEXT AS 'BORNCOUNTRYPOPULATIONREGISTRY'  ,      
KOD7.KLARTEXT AS 'MunicipalityDistrict' ,      
KOD8.KLARTEXT AS 'ServiceArea',      
PER.CIVKOD AS 'MaritalStatusId'      
FROM LD0P0A PER           
LEFT OUTER JOIN LD0V0Q01 KOD1 ON KOD1.KODTYP = 'CIVS' AND KOD1.KOD = PER.CIVKOD           
LEFT OUTER JOIN LD0V0Q01 KOD2 ON KOD2.KODTYP = 'LAND' AND KOD2.KOD = PER.MEDBSKAP          
LEFT OUTER JOIN LD0V0Q01 KOD3 ON KOD3.KODTYP = 'LAND' AND KOD3.KOD = PER.FODLAND          
LEFT OUTER JOIN LD0P0Q01 KOD4 ON KOD4.KODTYP = 'LAND' AND KOD4.KOD = PER.MEDBSKAP          
LEFT OUTER JOIN LD0P0Q01 KOD5 ON KOD5.KODTYP = 'LAND' AND KOD5.KOD = PER.FODLAND          
lEFT OUTER JOIN LD0V0Q01 KOD6 ON KOD6.KODTYP = 'OMRÅDE' AND  KOD6.KOD = PER.OMRADE      
LEFT OUTER JOIN LD0V0Q01 KOD7 ON KOD7.KODTYP = 'KOMMUNDEL' AND KOD7.KOD = PER.KOMDEL      
LEFT OUTER JOIN LD0V0Q01 KOD8 ON KOD8.KODTYP = 'BETJOMR' AND KOD8.KOD = PER.BETJOMR      
   

Go
GRANT ALL ON LD0ApiPersons TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAAYAKTUALISERING')
BEGIN
DROP VIEW LD0VAAYAKTUALISERING
END
GO
CREATE VIEW LD0VAAYAKTUALISERING  
AS  
SELECT     H.AKTUALNR, H.INSNR, H.ORGID, H.AEVERKNR, H.BESLUTNR, H.AKTUAL, T.KLARTEXT AKTUAL_T, H.AKTUALTYP, H.BTYP, H.ADATUM, H.AVEM, H.AORSAK,T2.KLARTEXT AORSAK_T, H.ORGENHNR,   
                      H.PNR, H.HAND, H.UPDDAT, H.UPDSIGN, H.HAND2, H.ORGENHNR2, H.RENSAD, H.ATEXT1, H.ATEXT2, H.ATEXT3, H.ATEXT4, H.FAMRATTNR, H.MODER,   
                      H.STATUS, H.ATGARD, H.ATGORS, H.DIARIENR, H.AKTNR, H.AVBFROMDAT, H.AVBTOMDAT, H.AVBORSAK, H.REGTID, H.SPOTCHECK, H.NEXTMONTH, H.PROCDATUM,   
                      H.BIDRTFNR, H.AVSER, H.URSPRUNG, H.APPLICATIONID, H.ATTACHMENTURL, P.NAMN, P.KUNDNUMMER, T3.KLARTEXT AVEM_T,  
                      J01.NAMN ORG_T, I.NAMN HANDNAMN, I2.NAMN HAND2NAMN, T.XNUM40 AS WORKFLOW_T, APP.REASONFORAPPEAL as REASONFORAPPEAL  ,APP.APPEALEDDECISION AS DECISIONFORAPPEAL, E.DATUM AS DECISIONDATE
FROM LD0P0H H   
INNER JOIN LD0V0A P   
ON H.PNR = P.PNR  
INNER JOIN LD0V0Q01 T   
ON T.KODTYP ='AKTUAL'   
AND T.KOD = H.AKTUAL  
INNER JOIN LD0V0Q01 T2   
ON T2.KODTYP ='AKTUORSAK'   
AND T2.KOD = H.AORSAK  
INNER JOIN LD0V0Q01 T3   
ON T3.KODTYP ='AKTUFRÅN'   
AND T3.KOD = H.AVEM  
LEFT JOIN LD0V0J01 J01  
ON H.ORGID = J01.ORGID   
AND H.ORGENHNR = J01.ORGENHNR   
LEFT JOIN LD0V0I I   
ON H.HAND = I.HAND   
LEFT JOIN LD0V0I I2   
ON H.HAND2 = I2.HAND  
LEFT JOIN LD0V0APPEAL APP  
ON APP.AKTUALNR = H.AKTUALNR
LEFT JOIN LD0V0E E  
ON APP.BESLUTNR = E.BESLUTNR  
GO

GRANT ALL ON LD0VAAYAKTUALISERING TO LD0ROANV
GO


IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0ActivityAttendance')
BEGIN
    DROP VIEW LD0VAA0ActivityAttendance
END
GO
CREATE VIEW LD0VAA0ActivityAttendance
AS
SELECT
U80.*,
Q.KLARTEXT AS FRANVAROORSAK_T,
U34.AKTIVTYP,
U34.AKTIVITET,
U34.ENKELPLATSNR,
U35.PRAKPROJNR,
U10.PERSONNR,
A.NAMN,
CASE WHEN U34.INTROARB = 2 THEN
    U60.BENAMNING 
ELSE 
    U70.BENAMNING
END AS BENAMNING,
Q1.KLARTEXT AS AKTIVTYP_T,
Q1.XNUM6 AS TYPEOFAKTIVTYP,
Q2.KLARTEXT AS AKTIVITET_T
FROM LD0V0U80 AS U80 
LEFT OUTER JOIN LD0V0Q01 AS Q ON Q.KODTYP = 'LNFRANVORS' AND U80.FRANVAROORSAK = Q.KOD
LEFT OUTER JOIN LD0P0U34 AS U34 ON U34.PLANAKTIVITETNR = U80.PLANAKTIVITETNR
LEFT OUTER JOIN LD0V0Q01 AS Q1 ON Q1.KODTYP = 'LNAKTIVTYP' AND U34.AKTIVTYP = Q1.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q2 ON Q2.KODTYP = 'LNAKTIV' AND U34.AKTIVITET = Q2.KOD
LEFT OUTER JOIN LD0VYU30 AS U30 ON U30.PLANNR = U34.PLANNR AND U30.VERSIONNR = U34.VERSIONNR AND U34.PLANAKTIVITETNR = U80.PLANAKTIVITETNR 
LEFT OUTER JOIN LD0VYU10 AS U10 ON U10.INSKRIVNINGSNR = U30.INSKRIVNINGSNR AND U30.PLANNR = U34.PLANNR
INNER JOIN LD0V0A AS A ON A.PNR = U10.PERSONNR
LEFT OUTER JOIN LD0V0U70 AS U70 ON U70.ENKELPLATSNR = U34.ENKELPLATSNR AND U34.ENKELPLATSNR > 0
LEFT OUTER JOIN LD0V0U35 AS U35 ON U35.PLANAKTIVITETNR = U34.PLANAKTIVITETNR AND U35.PLATSFROM <= U80.DATUM AND U35.PLATSTOM >= U80.DATUM
LEFT OUTER JOIN LD0V0U60 AS U60 ON U60.PRAKPROJNR = U35.PRAKPROJNR AND U34.PLANAKTIVITETNR = U35.PLANAKTIVITETNR
GO
GRANT ALL ON LD0VAA0ActivityAttendance 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,
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  LD0V0Q01T	Q011	ON	Q011.KODTYP	= 'VARDHAVARE' AND VLC.AEVERKTYP= Q011.KOD
LEFT JOIN  LD0V0C20T	C20		ON  C20.PNR = VLC.AKTPNR
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,
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.INSTYP = 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


IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1U80')
BEGIN
    DROP VIEW LD0V1U80
END
GO
CREATE VIEW LD0V1U80 AS
SELECT U80.*, U10.INSKRIVNINGSNR, U34.INTROARB, U34.AKTIVTYP, U34.AKTIVITET, U34.HAND AS U34HAND, A.PNR AS PERSONNR, A.NAMN, A.FODDAT, A.KON, A.KUNDNUMMER FROM LD0V0U80 U80 
   JOIN LD0VLU34 U34 ON U80.PLANAKTIVITETNR = U34.PLANAKTIVITETNR
   JOIN LD0VLU30 U30 ON U30.PLANNR = U34.PLANNR AND U30.VERSIONNR = U34.VERSIONNR
   JOIN LD0VLU10 U10 ON U10.INSKRIVNINGSNR = U30.INSKRIVNINGSNR
   JOIN LD0V0A A ON A.PNR = U10.PERSONNR

GO
GRANT ALL ON LD0V1U80 TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0SEARCHSERVICES')
BEGIN
DROP VIEW LD0VAA0SEARCHSERVICES
END
GO
CREATE VIEW LD0VAA0SEARCHSERVICES AS  
SELECT   
C20.INSNR,  
E.DATUM as BaseDecisionDate, 
C20.INSTYP,  
M10.MALNR as MÅLSATT,  
A.KUNDNUMMER,  
C20.ENSAMKOMMANDE_BARN,   
C20.BOENDE,  
C20.KEDJENR,  
C20.ARENDENR,  
C20.VERKNR,  
C20.BESLUTNR,  
C20.ORGID,  
C20.ORGENHNR,  
C20.FOMDAT,  
C20.TOMDAT,  
C20.HAND,  
C20.MEDHAND,  
C20.AKTPNR,  
C20.SALFMED,  
C20.UPDDAT,   
C20.UPDSIGN,  
C20.AVBRYT,  
C20.AVBORS,  
C20.INKONTO,  
C20.UTKONTO,  
C20.PRELTOM,  
C20.ANTAL,  
C20.UTFORSAV,  
C20.PERIOD,  
C20.VARDNHAV,  
C20.ORGENHNR2,  
C20.PNR,  
C20.DELREHAB,  
C20.BSKFAMLNR,  
C20.INTDAT,  
C20.UTSKRIVDAT,  
C20.AVSLORSAKTYP,  
C20.AVSLORSAK,  
C20.AVSLORSAKSPEC,  
C20.JDSTATUS,  
C20.INGENMEDSOK,  
C20.AVSLORSSTATSOKANDE,  
C20.AVSLORSSTATMEDSOKANDE,  
C20.AVSLORSSTATHUSHALL,  
Q.KLARTEXT AS INSTYP_T,  
Q_2.KLARTEXT AS AVBORS_T,  
Q_3.KLARTEXT AS AVSLORSAKTYP_T,  
Q_4.KLARTEXT AS AVSLORSAK_T,  
Q_5.KLARTEXT AS AVSLORSAKSPEC_T,  
Q_6.KLARTEXT AVSLORSSTATSOKANDE_T,  
Q_7.KLARTEXT AVSLORSSTATMEDSOKANDE_T,  
Q_8.KLARTEXT AVSLORSSTATHUSHALL_T,  
Q_9.KLARTEXT BOENDE_T,  
J01.NAMN AS ORGNAMN,  
I1.NAMN AS CASEWORKER,  
I2.NAMN AS COCASEWORKER,  
A.NAMN,A.ADRESS,  
A.COADR,  
A.FODDAT,  
A.KON,  
A.POSTNR,  
A.ORT,  
A.MINSIDA,  
A.MINSIDA_VUXNA,  
OMR.NAMN OMRADE_NAMN,  
CASE WHEN EXISTS(SELECT 'X' FROM LD0VYN70 N70 WHERE N70.INSNR = C20.INSNR) THEN '1' ELSE '0' END AS HASRESOURCEALLOCATION,  
CASE WHEN EXISTS(SELECT 'X' FROM LD0P0N900 N900 JOIN LD0P0E E ON N900.BESLUTNR = E.BESLUTNR AND E.INSNR=C20.INSNR) THEN '1' ELSE '0' END AS HASASSIGNMENTIFO,     
C21.PNR AS MEDSOKPNR,  
AC21.KUNDNUMMER AS MEDSOKKUNDNR,  
AC21.NAMN AS MEDSOKNAMN,  
AC21.ADRESS AS MEDSOKADRESS,  
AC21.COADR AS MEDSOKCOADRESS,  
AC21.POSTNR AS MEDSOKPOSTNR,  
AC21.ORT AS MEDSOKORT  
  
FROM LD0VLC20 C20   
  
INNER JOIN LD0V0Q01 AS Q   
ON Q.KODTYP = 'INSATS'   
AND Q.KOD = C20.INSTYP   
LEFT OUTER JOIN LD0V0Q01 Q_2   
ON Q_2.KODTYP = 'INSAVBORS'   
AND Q_2.KOD = C20.AVBORS   
LEFT OUTER JOIN LD0V0Q01 Q_3   
ON Q_3.KODTYP = 'INSORSTYP'  
AND Q_3.KOD = C20.AVSLORSAKTYP   
LEFT OUTER JOIN LD0V0Q01 Q_4   
ON Q_4.KODTYP = 'INSORSAK'   
AND Q_4.KOD = C20.AVSLORSAK   
LEFT OUTER JOIN LD0V0Q01 Q_5   
ON Q_5.KODTYP = 'INSORSSPEC'   
AND Q_5.KOD = C20.AVSLORSAKSPEC   
LEFT JOIN LD0V0Q01 Q_6   
ON Q_6.KODTYP ='SCBKODAVSL'   
AND Q_6.KOD = C20.AVSLORSSTATSOKANDE   
LEFT JOIN LD0V0Q01 Q_7   
ON Q_7.KODTYP ='SCBKODAVSL'   
AND Q_7.KOD = C20.AVSLORSSTATMEDSOKANDE   
LEFT JOIN LD0V0Q01 Q_8   
ON Q_8.KODTYP ='SCBKODAVSL'   
AND Q_8.KOD = C20.AVSLORSSTATHUSHALL   
LEFT JOIN LD0V0Q01 Q_9   
ON Q_9.KODTYP ='SCBKODBOEN'   
AND Q_9.KOD = C20.BOENDE   
LEFT OUTER JOIN LD0P0J01 J01   
ON J01.ORGENHNR = C20.ORGENHNR   
AND J01.ORGID = C20.ORGID   
LEFT OUTER JOIN LD0V0I I1   
ON I1.HAND = C20.HAND  
LEFT OUTER JOIN LD0V0I I2   
ON I2.HAND = C20.MEDHAND   
JOIN LD0V0A A   
ON A.PNR = C20.AKTPNR   
LEFT OUTER JOIN LD0VAA0OMRADENKO OMR   
ON A.NYCKKOD = OMR.NYCKKOD   
LEFT OUTER JOIN LD0V0M10 M10   
ON M10.MODELLID = 7083   
AND M10.MALID = C20.INSNR   
AND M10.PNR = C20.AKTPNR   
LEFT OUTER JOIN LD0V0C21 C21  
ON C21.INSNR = C20.INSNR AND C21.MEDSOKANDE = 1  
LEFT OUTER JOIN LD0V0A AC21  
ON AC21.PNR = C21.PNR 
left outer join LD0P0E E 
on E.BESLUTNR = C20.BESLUTNR
GO
GRANT ALL ON LD0VAA0SEARCHSERVICES TO LD0ROANV
GO