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
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' 
GO

GRANT ALL ON LD0VAA0SALARY TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME = 'LD0VAA0SearchForms')
BEGIN
DROP VIEW LD0VAA0SearchForms
END
GO
CREATE VIEW LD0VAA0SearchForms AS
SELECT V09.LOPNR, V09.FORMNR, V09.OBJEKTNYCKLALFA, T41.UPDDAT, T41.UPDSIGN, V09.LOCKED, V09.LOCKSIGN, V09.LOCKDATE, V09.DYNDELSYS, V09.VERSION, V09.OBJEKTTYP,
V04.TYPDOK,
A.NAMN,
T41.DOKUMENTNR, T41.DDATUM,
Q01.KLARTEXT, Q01.AKTIV
FROM LD0V0V09 V09
INNER JOIN LD0V0v04 V04 ON V09.FORMNR = V04.FORMNR
INNER JOIN ld0v0q01 Q01 ON V04.TYPDOK = Q01.KOD
INNER JOIN LD0V0A A ON V09.OBJEKTNYCKLALFA = A.PNR
INNER JOIN LD0V1T41 T41 ON V09.LOPNR = T41.HUVUDOBJNR
WHERE V04.TYPDOK > 0 AND V04.AVSNITT = 0 AND V04.PARENT = 0
AND V04.VERSION = V09.VERSION AND V04.DYNDELSYS = V09.DYNDELSYS
AND T41.HUVUDOBJTYP = 7219 AND Q01.AKTIV != 0 AND Q01.KODTYP = 'TYPDOK'
GO
GRANT ALL ON LD0VAA0SearchForms 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,
(SELECT COALESCE(MIN(NULLIF(U35.PLATSFROM, '')), U34.VERKSTART) FROM LD0V0U35 AS U35 WHERE U34.PLANAKTIVITETNR=U35.PLANAKTIVITETNR AND U34.INTROARB=2 AND U35.STATUS=2) AS VERKSTART,
(SELECT COALESCE(MAX(NULLIF(U35.PLATSTOM, '')), U34.VERKSLUT) FROM LD0V0U35 AS U35 WHERE U34.PLANAKTIVITETNR=U35.PLANAKTIVITETNR AND U34.INTROARB=2 AND U35.STATUS=2) AS VERKSLUT
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 ALL ON LD0VAA0LMSearchActivity 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     
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 = 'LD0ApiDecision')
BEGIN
DROP VIEW LD0ApiDecision
END
GO
CREATE VIEW LD0ApiDecision          
AS                    
SELECT DISTINCT      
E.BESLUTNR,      
E.STARTTIME,      
E.ENDTIME,      
E.DATUM,      
E.FOMDAT,      
E.TOMDAT,      
E.AEVERKNR,      
E.INSNR,      
E.BELOPP,      
E.AKTUALNR,      
E.BESFATT,      
E.BESLUT,      
E.BESLTYP,      
E.ORSAK,      
E.VERKSEJ,      
E.GEMVARD,      
E.PKLTYP,      
E.BARNVAEG,      
E.VARDNHAV,      
E.UNDANTAGSTATISTIK,      
E.ORSAKMED,      
VI.NAMN BNAMN_T,                   
VI.TJANST BTJANST_T,                   
Q01.KLARTEXT BORSAK_T,                   
Q01.XTXT1 BORSAKRUBRIK_T,                   
Q01_2.KLARTEXT BESLUT_T,                   
Q01_3.KLARTEXT BMEDORSAK_T,          
Q01_4.KLARTEXT AS BESLTYP_T,      
Q01_3.XTXT1 BMEDORSAKRUBRIK_T,                   
Q01_2.XNUM5 AMOUNT_TO_BALANCE,                   
Q01_2.XNUM2 LEADS_TO_SERVICE,                   
Q01_2.XNUM33 LEADS_TO_ASSIGNMENT,      
T10.KLARTEXT STATGRP1_T,      
T11.KLARTEXT STATGRP2_T,      
T12.KLARTEXT STATGRP3_T,      
T13.KLARTEXT STATGRP4_T,            
T14.KLARTEXT STATGRP5_T,      
CASE WHEN E.AEVERKNR>0 THEN                  
 (SELECT DISTINCT KUNDNUMMER FROM LD0P0A A                   
  WHERE EXISTS (SELECT * FROM LD0V0CT C WHERE C.AKTPNR=A.PNR                   
   AND C.AEVERKNR=E.AEVERKNR))                  
  WHEN E.INSNR>0 THEN                  
  (SELECT DISTINCT  KUNDNUMMER FROM LD0P0A A                   
  WHERE EXISTS (SELECT * FROM LD0V0C20T C20 WHERE C20.AKTPNR=A.PNR                   
   AND C20.INSNR=E.INSNR))                  
  END AS KUNDNUMMER,                  
CASE WHEN E.AEVERKNR>0 THEN                  
 (SELECT DISTINCT PNR FROM LD0P0A A                   
  WHERE EXISTS (SELECT * FROM LD0V0CT C WHERE C.AKTPNR=A.PNR                   
   AND C.AEVERKNR=E.AEVERKNR))                  
  WHEN E.INSNR>0 THEN                  
  (SELECT DISTINCT PNR FROM LD0P0A A                
                  
  WHERE EXISTS (SELECT * FROM LD0V0C20T C20 WHERE C20.AKTPNR=A.PNR                   
   AND C20.INSNR=E.INSNR))                  
  END AS PNR,        
INV.HAND AS INVESTIGATIONSCASEWORKERID,       
SERV.HAND AS SERVICESCASEWORKERID,      
AKPER.HAND AS EXECUTIONSCASEWORKERID,            
PROF3.NAMN AS INVESTIGATIONSCASEWORKER,       
PROF2.NAMN AS SERVICESCASEWORKER,      
PROF1.NAMN AS EXECUTIONSCASEWORKER,      
J01_SERV.NAMN AS SERVICESORG,      
J01_INV.NAMN AS INVESTIGATIONSORG,      
J01_AKPER.NAMN AS EXECUTIONSORG,      
L20.ORGID AS SERVICESORGID,      
L20.ORGENHNR AS SERVICESORGUNITID,        
INV.ORGID AS INVESTIGATIONSORGID,      
INV.ORGENHNR AS INVESTIGATIONSORGUNITID,      
AKPER.ORGID AS EXECUTIONSORGID,      
AKPER.ORGENHNR AS EXECUTIONSORGUNITID,      
STAT.XNUM7 AS STATISTICINDIVIDUALID,      
STAT1.KLARTEXT AS STATISTICINDIVIDUAL,      
STAT.XNUM8 AS STATISTICQUANTITYID,      
STAT2.KLARTEXT AS STATISTICQUANTITY,      
STAT.XNUM4 AS LAWSECTIONID,      
STAT3.KLARTEXT AS LAWSECTION,      
RES.KOD AS STATISTICAPPLICANTREASONID,      
RES.KLARTEXT AS STATISTICAPPLICANTREASON,      
RES.XTXT1 AS STATISTICAPPLICANTREASONTITLE,      
RES.XTXT2 AS STATISTICAPPLICANTREASONCODE,      
RES1.KOD AS STATISTICCOAPPLICANTREASONID,      
RES1.KLARTEXT AS STATISTICCOAPPLICANTREASON,      
RES1.XTXT1 AS STATISTICCOAPPLICANTREASONTITLE,      
RES1.XTXT2 AS STATISTICCOAPPLICANTREASONCODE,      
GAR.KLARTEXT AS VARDNHAV_T      
                
FROM LD0V0ET E     
INNER JOIN LD0V0I VI ON E.BESFATT = VI.HAND    
LEFT OUTER JOIN LD0V0CT CT ON CT.AEVERKNR = E.AEVERKNR  
LEFT OUTER JOIN LD0V0C20T CT20 ON CT20.INSNR = E.INSNR  
JOIN LD0P0A A ON A.PNR = CT.AKTPNR OR CT20.AKTPNR = A.PNR    
LEFT OUTER JOIN LD0V0Q01T Q01 ON Q01.KODTYP = 'BESLORSAK' AND E.ORSAK = Q01.KOD      
LEFT OUTER JOIN LD0V0Q01T Q01_2 ON Q01_2.KODTYP = 'BESLUT' AND E.BESLUT = Q01_2.KOD      
LEFT OUTER JOIN LD0V0Q01T Q01_3 ON Q01_3.KODTYP = 'BESLORSAK' AND E.ORSAKMED = Q01_3.KOD      
LEFT OUTER JOIN LD0V0Q01T  Q01_4 ON Q01_4.KODTYP = 'BESLUTTYP' AND E.BESLTYP = Q01_4.KOD      
LEFT OUTER JOIN LD0VYC20T    AS L20   ON E.INSNR =L20.INSNR      
LEFT OUTER JOIN LD0VYC20T     AS SERV   ON SERV.INSNR = E.INSNR      
LEFT OUTER JOIN LD0VYCT     AS INV   ON  E.AEVERKNR= INV.AEVERKNR AND INV.TMARK = 0      
LEFT OUTER JOIN LD0VYCT AS AKPER  ON E.AEVERKNR = AKPER.AEVERKNR AND AKPER.TMARK = 1      
LEFT OUTER JOIN LD0V0I     AS PROF3  ON PROF3.HAND = INV.HAND      
LEFT OUTER JOIN LD0V0I     AS PROF2  ON PROF2.HAND = L20.HAND      
LEFT OUTER JOIN LD0V0I     AS PROF1  ON PROF1.Hand = AKPER.HAND      
LEFT OUTER JOIN LD0P0J01    AS J01_SERV  ON J01_SERV.ORGENHNR = L20.ORGENHNR AND J01_SERV.ORGID = L20.ORGID      
LEFT OUTER JOIN LD0V0J01    AS J01_INV  ON J01_INV.ORGENHNR =INV.ORGENHNR AND J01_INV.ORGID = INV.ORGID      
LEFT OUTER JOIN LD0V0J01    AS J01_AKPER  ON J01_AKPER.ORGENHNR =AKPER.ORGENHNR AND J01_AKPER.ORGID = AKPER.ORGID       
LEFT OUTER JOIN LD0V0Q04    AS STATGRPVAL ON STATGRPVAL.KODTYP = 'BESLUT' AND E.BESLUT = STATGRPVAL.KOD      
LEFT OUTER JOIN LD0V0Q01T    AS T10   ON T10.KODTYP = 'STATGRUPP'   AND T10.XTXT1 = 'BESLUT'   AND T10.KOD = STATGRPVAL.STATGRP1             
LEFT OUTER JOIN LD0V0Q01T    AS T11   ON T11.KODTYP = 'STATGRUPP'   AND T11.XTXT1 = 'BESLUT'   AND T11.KOD = STATGRPVAL.STATGRP2      
LEFT OUTER JOIN LD0V0Q01T    AS T12   ON T12.KODTYP = 'STATGRUPP'   AND T12.XTXT1 = 'BESLUT'   AND T12.KOD = STATGRPVAL.STATGRP3      
LEFT OUTER JOIN LD0V0Q01T    AS T13   ON T13.KODTYP = 'STATGRUPP'   AND T13.XTXT1 = 'BESLUT'   AND T13.KOD = STATGRPVAL.STATGRP4      
LEFT OUTER JOIN LD0V0Q01T    AS T14   ON T14.KODTYP = 'STATGRUPP'   AND T14.XTXT1 = 'BESLUT'   AND T14.KOD = STATGRPVAL.STATGRP5      
LEFT OUTER JOIN LD0V0Q01T    AS STAT   ON STAT.KODTYP = 'BESLUT' AND STAT.KOD = E.BESLUT      
LEFT OUTER JOIN LD0V0Q01T    AS STAT1   ON STAT1.KOD = STAT.XNUM7 AND STAT1.KODTYP = 'SCBKODBESL'      
LEFT OUTER JOIN LD0V0Q01T    AS STAT2   ON STAT2.KOD = STAT.XNUM8 AND STAT2.KODTYP = 'SCBKODBESM'      
LEFT OUTER JOIN LD0V0Q01T    AS STAT3   ON STAT3.KOD = STAT.XNUM4 AND STAT3.KODTYP = 'LAGRUM'      
LEFT OUTER JOIN LD0V0Q01T    AS RES  ON RES.KOD = Q01.XNUM1 AND RES.KODTYP = 'SCBKODORS'      
LEFT OUTER JOIN LD0V0Q01T    AS RES1  ON RES1.KOD = Q01_3.XNUM1 AND RES1.KODTYP = 'SCBKODORS'      
LEFT OUTER JOIN LD0V0Q01T    AS GAR  ON GAR.KOD = E.VARDNHAV AND GAR.KODTYP = 'VARDHAVARE' 
GO
GRANT ALL ON LD0ApiDecision TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME = 'LD0ApiInvestigation')
BEGIN
DROP VIEW LD0ApiInvestigation
END
GO
CREATE VIEW LD0ApiInvestigation  
AS      
SELECT      
C.STARTTIME,      
C.ENDTIME,    
B.TYPNR_T AKTTYP_T,       
Q.KLARTEXT AEVERKTYP_T,       
Q.XNUM40 WORKFLOW_T,       
O.NAMN ORG_T,       
H.NAMN CASEWORKER,      
A.NAMN AKTPNR_NAMN,      
A.KUNDNUMMER,    
C.STARTDAT,    
C.STOPDAT,    
C.AKTPNR,    
C.AEVERKTYP,    
c.AEVERKNR,    
H.HAND,    
C.MEDHAND,    
I2.NAMN AS COCASEWORKER,    
C.ORGID,    
C.ORGENHNR,    
C.FAMRTYP1,    
C.FAMRTYP2,    
C.FAMRTYP3,    
T10.KLARTEXT STATGRP1_T,       
T11.KLARTEXT STATGRP2_T,        
T12.KLARTEXT STATGRP3_T,        
T13.KLARTEXT STATGRP4_T,        
T14.KLARTEXT STATGRP5_T,    
STAT.XNUM6 AS STATISTICQUANTITYID,      
STAT1.KLARTEXT AS STATISTICQUANTITY    
FROM LD0P0C C   
INNER JOIN LD0V0A A ON C.AKTPNR = A.PNR 
INNER JOIN LD0VAAYB B ON C.AKTNR = B.AKTNR     
INNER JOIN LD0V0Q01T Q ON Q.KODTYP = 'UTREDTYP' AND Q.KOD = C.AEVERKTYP      
INNER JOIN LD0V0J01 O ON C.ORGID = O.ORGID AND C.ORGENHNR = O.ORGENHNR      
INNER JOIN LD0V0I H ON C.HAND = H.HAND    
LEFT JOIN LD0V0I I2 ON C.MEDHAND = I2.HAND        
LEFT OUTER JOIN LD0V0Q04 STATGRPVAL ON STATGRPVAL.KODTYP = 'UTREDTYP' AND C.AEVERKTYP = STATGRPVAL.KOD     
LEFT OUTER JOIN LD0V0Q01 T10 ON T10.KODTYP = 'STATGRUPP' AND T10.XTXT1 = 'UTREDTYP' AND T10.KOD = STATGRPVAL.STATGRP1    
LEFT OUTER JOIN LD0V0Q01 T11 ON T11.KODTYP = 'STATGRUPP' AND T11.XTXT1 = 'UTREDTYP' AND T11.KOD = STATGRPVAL.STATGRP2     
LEFT OUTER JOIN LD0V0Q01 T12 ON T12.KODTYP = 'STATGRUPP' AND T12.XTXT1 = 'UTREDTYP' AND T12.KOD = STATGRPVAL.STATGRP3      
LEFT OUTER JOIN LD0V0Q01 T13 ON T13.KODTYP = 'STATGRUPP' AND T13.XTXT1 = 'UTREDTYP' AND T13.KOD = STATGRPVAL.STATGRP4    
LEFT OUTER JOIN LD0V0Q01 T14 ON T14.KODTYP = 'STATGRUPP' AND T14.XTXT1 = 'UTREDTYP' AND T14.KOD = STATGRPVAL.STATGRP5      
LEFT OUTER JOIN LD0V0Q01T    AS STAT   ON STAT.KODTYP = 'UTREDTYP' AND STAT.KOD = C.AEVERKTYP    
LEFT OUTER JOIN LD0V0Q01T    AS STAT1   ON STAT1.KOD = STAT.XNUM6 AND STAT1.KODTYP = 'SCBKODUTR'     
WHERE C.TMARK = 0     
GO
GRANT ALL ON LD0ApiInvestigation TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME = 'LD0ApiService')
BEGIN
DROP VIEW LD0ApiService
END
GO
CREATE VIEW LD0ApiService AS    
SELECT    
C20.AKTPNR,  
C20.STARTTIME,    
C20.ENDTIME,    
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,    
C21.PNR AS MEDSOKPNR,    
CASE WHEN ARENDENR>0 THEN   (SELECT DISTINCT KLARTEXT FROM LD0P0B B  JOIN LD0V0Q01T Q01  ON Q01.KODTYP='AKTTYP'  AND Q01.KOD=B.TYPNR    
AND EXISTS (SELECT * FROM LD0V0CT C WHERE C.AKTNR=B.AKTNR AND C.AEVERKNR=C20.ARENDENR))    
WHEN VERKNR>0 THEN  (SELECT DISTINCT KLARTEXT FROM LD0P0B B  JOIN LD0V0Q01T Q01  ON Q01.KODTYP='AKTTYP'  AND Q01.KOD=B.TYPNR   
AND EXISTS (SELECT * FROM LD0V0CT C WHERE C.AKTNR=B.AKTNR AND C.AEVERKNR=C20.VERKNR))    
END AS AKTTYP_T,  
P.KUNDNUMMER  ,  
C20.FOMDAT,  
C20.TOMDAT,  
C20.INSNR,  
C20.INSTYP,   
C20.HAND,  
C20.MEDHAND,  
C20.ORGID,  
C20.ORGENHNR,  
C20.ARENDENR,  
C20.VERKNR,  
C20.BESLUTNR,  
C20.AVBRYT,  
C20.AVSLORSSTATSOKANDE,  
C20.AVSLORSSTATMEDSOKANDE,  
C20.AVSLORSSTATHUSHALL,  
C20.ENSAMKOMMANDE_BARN,  
T10.KLARTEXT STATGRP1_T,    
T11.KLARTEXT STATGRP2_T,    
T12.KLARTEXT STATGRP3_T,    
T13.KLARTEXT STATGRP4_T,          
T14.KLARTEXT STATGRP5_T,  
Q.XNUM9 AS STATISTICQUANTITYID ,  
STAT2.KLARTEXT AS STATISTICQUANTITY    
    
FROM   LD0P0C20 C20     
INNER JOIN LD0V0A  P  ON C20.AKTPNR = P.PNR    
INNER JOIN  LD0V0Q01T Q  ON Q.KODTYP = 'INSATS'   AND Q.KOD = C20.INSTYP    
LEFT OUTER JOIN LD0V0Q01T Q_2  ON Q_2.KODTYP = 'INSAVBORS'  AND Q_2.KOD = C20.AVBORS    
LEFT OUTER JOIN LD0V0Q01T Q_3  ON Q_3.KODTYP = 'INSORSTYP'  AND Q_3.KOD = C20.AVSLORSAKTYP    
LEFT OUTER JOIN LD0V0Q01T Q_4  ON Q_4.KODTYP = 'INSORSAK'  AND Q_4.KOD = C20.AVSLORSAK    
LEFT OUTER JOIN LD0V0Q01T Q_5  ON Q_5.KODTYP = 'INSORSSPEC' AND Q_5.KOD = C20.AVSLORSAKSPEC    
LEFT OUTER JOIN LD0V0Q01T Q_6  ON Q_6.KODTYP ='SCBKODAVSL'  AND Q_6.KOD = C20.AVSLORSSTATSOKANDE    
LEFT OUTER JOIN LD0V0Q01T Q_7  ON Q_7.KODTYP ='SCBKODAVSL'  AND Q_7.KOD = C20.AVSLORSSTATMEDSOKANDE    
LEFT OUTER JOIN LD0V0Q01T Q_8  ON Q_8.KODTYP ='SCBKODAVSL'  AND Q_8.KOD = C20.AVSLORSSTATHUSHALL    
LEFT OUTER JOIN LD0V0Q01T 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     
LEFT OUTER JOIN LD0P0C21 C21  ON C21.INSNR = C20.INSNR  AND C21.MEDSOKANDE = 1  AND C21.ENDTIME=C20.ENDTIME    
LEFT OUTER JOIN LD0V0Q04 STAT1 ON STAT1.KODTYP = 'INSATS'  AND C20.INSTYP = STAT1.KOD     
LEFT OUTER JOIN LD0V0Q01T T10  ON T10.KODTYP = 'STATGRUPP'  AND T10.XTXT1 = 'INSATS'   AND T10.KOD = STAT1.STATGRP1     
LEFT OUTER JOIN LD0V0Q01T T11  ON T11.KODTYP = 'STATGRUPP'  AND T11.XTXT1 = 'INSATS'   AND T11.KOD = STAT1.STATGRP2     
LEFT OUTER JOIN LD0V0Q01T T12  ON T12.KODTYP = 'STATGRUPP'  AND T12.XTXT1 = 'INSATS'   AND T12.KOD = STAT1.STATGRP3     
LEFT OUTER JOIN LD0V0Q01T T13  ON T13.KODTYP = 'STATGRUPP'  AND T13.XTXT1 = 'INSATS'   AND T13.KOD = STAT1.STATGRP4     
LEFT OUTER JOIN LD0V0Q01T T14  ON T14.KODTYP = 'STATGRUPP'  AND T14.XTXT1 = 'INSATS'   AND T14.KOD = STAT1.STATGRP5    
LEFT OUTER JOIN LD0V0Q01T STAT2   ON STAT2.KOD = Q.XNUM9      AND STAT2.KODTYP = 'SCBKODINS'  
GO
GRANT ALL ON LD0ApiService TO LD0ROANV
GO