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, V09.UPDDAT, V09.UPDSIGN, V09.LOCKED, v09.LOCKSIGN, v09.LOCKDATE, v09.DYNDELSYS, v09.VERSION, v09.OBJEKTTYP,
v04.FORMNAME, v04.TYPDOK,
A.NAMN,
t41.DOKUMENTNR, T41.DDATUM
FROM LD0V0V09 v09
INNER JOIN LD0V0v04 V04 ON V09.FORMNR = V04.FORMNR
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
GO
GRANT ALL ON LD0VAA0SearchForms TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME = 'LD0VAA0SearchFormQueries')
BEGIN
DROP VIEW LD0VAA0SearchFormQueries 
END
GO
CREATE VIEW LD0VAA0SearchFormQueries AS
SELECT DISTINCT v11.SVAR, v09.LOPNR, V11.SVARLNR, V25.FRAGENR
FROM Ld0v0v10 v10
INNER JOIN LD0V0v11 V11 ON V10.SVARNR = V11.SVARNR 
INNER JOIN LD0V0v25 V25 ON V10.FRAGENR = V25.FRAGENR
INNER JOIN LD0V0v09 V09 ON V10.OBJEKTNYKLALFA = V09.OBJEKTNYCKLALFA 
INNER JOIN LD0V0v04 V04 ON V09.FORMNR = V04.FORMNR 
WHERE v09.OBJEKTTYP = v10.OBJEKTTYP AND OBJEKTNYCKLNUM = V10.OBJEKTNYKLNUM
AND V09.OBJEKTNYCKLNUM2 = V10.OBJEKTNYKLNUM2 AND V09.OBJEKTNYCKLLNR = V10.OBJEKTNYKLLNR
AND v04.[VERSION] = v09.[VERSION] AND v04.DYNDELSYS = v09.DYNDELSYS
AND v04.TYPDOK > 0 AND v04.AVSNITT = 0 AND V04.PARENT = 0
GO
GRANT ALL ON LD0VAA0SearchFormQueries TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME = 'LD0VAA0SearchFormSeachDefinitions')
BEGIN
DROP VIEW LD0VAA0SearchFormSeachDefinitions 
END
GO
CREATE VIEW LD0VAA0SearchFormSeachDefinitions AS
SELECT DISTINCT v24.SOKDEFNR, v24.SOKDEFNAMN, v04.TYPDOK, v04.DYNDELSYS
FROM Ld0v0v24 V24
INNER JOIN LD0V0v25 V25 ON V25.SOKDEFNR = V24.SOKDEFNR
INNER JOIN LD0V0v05 v05 ON V25.FRAGENR = V05.FRAGENR
INNER JOIN LD0V0v04 v04 ON v04.FORMNR = v05.FORMNR
WHERE v04.TYPDOK > 0
GO
GRANT ALL ON LD0VAA0SearchFormSeachDefinitions TO LD0ROANV
GO




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

AS SELECT D.BESLUTNR, D.BESLUT, D.BESFATT, INV.AKTPNR AS INV_PNR, SERV.AKTPNR AS SERV_PNR, AKPER.PNR AS AK_PNR,    
PER.NAMN AS INV_NAMN,  PER1.NAMN AS SERV_NAMN, AKPER.NAMN AS AK_NAMN , D.DATUM,    
Q01_2.KLARTEXT AS BESLUT_T, PROF.NAMN AS BNAMN_T, PROF1.NAMN AS AKBNAMN_T ,     
INV.HAND AS INV_CASEWORKER, SERV.HAND AS SERV_CASEWORKER,AKPER.HAND AS AK_CASEWORKER,    
PROF3.NAMN AS INV_CASEWORKERNAME, PROF2.NAMN AS SERV_CASEWORKERNAME,PROF1.NAMN AK_CASEWORKERNAME,    
PER.KON AS INV_KON ,PER1.KON AS SERV_KON, PER2.KON AS AK_KON,    
PER.FODDAT AS INV_FODDAT,PER1.FODDAT AS SERV_FODDAT,PER2.FODDAT AS AK_FODDAT,    
PER.KUNDNUMMER AS INV_KUNDNUMMER, PER1.KUNDNUMMER AS SERV_KUNDNUMMER, AKPER.KUNDNUMMER AS AK_KUNDNUMMER,    
D.ORSAK, D.ORSAKMED, J01_SERV.NAMN AS SERV_ORGNAMN,J01_INV.NAMN AS INV_ORGNAMN,D.FOMDAT,D.TOMDAT,D.BELOPP,D.ENGBEL,D.PKLTYP AS PROTOKOLL,    
D.UPDSIGN,D.GEMVARD,D.VERKSEJ,D.REGDAT AS REG_DATUM,D.INSNR,D.AEVERKNR,L20.ORGID AS SERV_ORGANIZATIONID,L20.ORGENHNR AS SERV_ORGANIZATIONUNITID,    
INV.ORGID AS INV_ORGANIZATIONID,INV.ORGENHNR AS INV_ORGANIZATIONUNITID,Q01_4.KLARTEXT AS BESLTYP_T ,    
AH.PNR AS AKPNR, AKPER.PNR AS AKPNR1,     
AREA.NAMN As INVA_NAMN, AREA1.NAMN AS SERVA_NAMN ,  AREA2.NAMN AS AKA_NAMN ,    
AKPER.DIARIENR AS DIARIENUMMER,PROF.TJANST AS BTJANST_T,D.INSATGARD,D.INSATGARD2,    
Q01.KLARTEXT AS BORSAK_T,     
Q03.KLARTEXT AS BORSAKRUBRIK_T,    
D.VARDNHAV, Q01_5.KLARTEXT AS VARDNHAV_T,     
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, J01_INV.INAKTIV ORG_STATUS, Q01.AKTIV AS AORSAK_STATUS,    
AH.PNR, AKPER.HANDNAMN AS AKTUALNR, AH.ORGID AS AKTUAL_ORGID,AKPER.ORGENHNR AS AKTUAL_ORGUNITID ,AKPER.ORG_T AS AKTUALTYP,    
D.BARNVAEG, AKPER.AKTUALNR AS AKTIALISERINGSNUMMER, D.UNDANTAGSTATISTIK,  
AH.RENSAD As CLEARED  
    
FROM LD0V0E D    
LEFT OUTER JOIN LD0VYC20    AS L20   ON D.INSNR =L20.INSNR      
LEFT OUTER JOIN LD0V0I     AS PROF   ON D.BESFATT = PROF.HAND    
LEFT OUTER JOIN LD0V0Q01    AS Q01_2  ON Q01_2.KODTYP = 'BESLUT' AND D.BESLUT = Q01_2.KOD     
LEFT OUTER JOIN LD0V0I     AS PROF2  ON PROF2.HAND = L20.HAND     
LEFT OUTER JOIN LD0V0I     AS VI_SERV2  ON VI_SERV2.HAND = L20.MEDHAND     
LEFT OUTER JOIN LD0P0J01    AS J01_SERV  ON J01_SERV.ORGENHNR = L20.ORGENHNR AND J01_SERV.ORGID = L20.ORGID    
LEFT OUTER JOIN LD0VYC     AS INV   ON  INV.AEVERKNR = D.AEVERKNR      
LEFT OUTER JOIN LD0P0C20     AS SERV   ON SERV.INSNR = D.INSNR     
LEFT OUTER JOIN LD0V0J01    AS J01_INV  ON J01_INV.ORGENHNR =INV.ORGENHNR AND J01_INV.ORGID = INV.ORGID     
LEFT OUTER JOIN LD0V0Q01    AS Q01_4  ON Q01_4.KODTYP = 'BESLUTTYP' AND D.BESLTYP = Q01_4.KOD     
LEFT OUTER JOIN LD0V0Q01    AS Q01_5  ON Q01_5.KODTYP = 'VARDHAVARE' AND D.VARDNHAV = Q01_5.KOD     
LEFT OUTER JOIN LD0VYH     AS LDO   ON LDO.HAND = 'SVEN'  AND LDO.BESLUTNR=  D.BESLUTNR     
LEFT OUTER JOIN LD0V0A     AS PER   ON INV.AKTPNR = PER.PNR     
LEFT OUTER JOIN LD0V0A     AS PER1   ON L20.AKTPNR = PER1.PNR     
LEFT OUTER JOIN LD0V0I     AS PROF3  ON PROF3.HAND = INV.HAND     
LEFT OUTER JOIN LD0V0Q04    AS STATGRPVAL ON STATGRPVAL.KODTYP = 'BESLUT' AND LDO.AKTUAL = STATGRPVAL.KOD      
LEFT OUTER JOIN LD0P0H     AS AH   On D.BESLUTNR = AH.BESLUTNR    
LEFT OUTER JOIN LD0VAA0AKTUALISERING AS AKPER  ON D.BESLUTNR = AKPER.BESLUTNR    
LEFT OUTER JOIN LD0V0A     AS PER2   ON AKPER.PNR = PER2.PNR     
LEFT OUTER JOIN LD0VAA0OMRADENKO  AS AREA   ON PER.NYCKKOD = AREA.NYCKKOD     
LEFT OUTER JOIN LD0VAA0OMRADENKO  AS AREA1  ON PER1.NYCKKOD = AREA1.NYCKKOD    
LEFT OUTER JOIN LD0VAA0OMRADENKO  AS AREA2  ON PER2.NYCKKOD = AREA2.NYCKKOD    
LEFT OUTER JOIN LD0V0I     AS PROF1  ON PROF1.Hand =AKPER.HAND    
LEFT OUTER JOIN LD0V0Q01    AS Q01   ON Q01.KODTYP = 'BESLORSAK' AND D.ORSAK = Q01.KOD     
LEFT OUTER JOIN LD0V0Q01    AS Q03   ON Q03.KODTYP = 'BESLORSAK' AND D.ORSAKMED = Q03.KOD     
LEFT OUTER JOIN LD0V0Q01    AS T10   ON T10.KODTYP = 'STATGRUPP'   AND T10.XTXT1 = 'BESLUT'   AND T10.KOD = STATGRPVAL.STATGRP1       
LEFT OUTER JOIN LD0V0Q01    AS T11   ON T11.KODTYP = 'STATGRUPP'   AND T11.XTXT1 = 'BESLUT'   AND T11.KOD = STATGRPVAL.STATGRP2       
LEFT OUTER JOIN LD0V0Q01    AS T12   ON T12.KODTYP = 'STATGRUPP'   AND T12.XTXT1 = 'BESLUT'   AND T12.KOD = STATGRPVAL.STATGRP3       
LEFT OUTER JOIN LD0V0Q01    AS T13   ON T13.KODTYP = 'STATGRUPP'   AND T13.XTXT1 = 'BESLUT'   AND T13.KOD = STATGRPVAL.STATGRP4       
LEFT OUTER JOIN LD0V0Q01    AS T14   ON T14.KODTYP = 'STATGRUPP'   AND T14.XTXT1 = 'BESLUT'   AND T14.KOD = STATGRPVAL.STATGRP5   

GO
GRANT ALL ON LD0V0ESTATISTICSDECISIONS TO LD0ROANV
GO