
IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0PROCESSMETADATA')
BEGIN
DROP VIEW LD0V0PROCESSMETADATA
END
;
CREATE VIEW LD0V0PROCESSMETADATA AS 
SELECT	*
FROM LD0P0PROCESSMETADATA
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1PROCESSMETADATA')
BEGIN
DROP VIEW LD0V1PROCESSMETADATA
END
;
CREATE VIEW LD0V1PROCESSMETADATA AS     
SELECT
M.*,P.PROCESSOWNERTYPE AS OWNERTYPE,
P.PROCESSTYPEOID, P.NAME AS PROCESSNAME
FROM LD0P0PROCESSMETADATA M
LEFT JOIN LD0V0PROCESS P
ON M.PROCESSID = P.ID
;
IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1PROCESSMETADATAWITHCASE')
BEGIN
DROP VIEW LD0V1PROCESSMETADATAWITHCASE
END
;
CREATE VIEW LD0V1PROCESSMETADATAWITHCASE
AS
SELECT	M.Id AS PROCESSMETADATAID,
		M.OWNERID,
		P.PROCESSOWNERTYPE AS OWNERTYPE,
		P.Id AS PROCESSID, 
		P.NAME AS PROCESSNAME,
		C.CASEOID
FROM LD0P0PROCESSMETADATA M 
LEFT JOIN LD0V0PROCESS P ON M.PROCESSID = P.ID
LEFT JOIN LD0V0CASE C ON C.ID = M.CASEID
;

GRANT ALL ON LD0V1PROCESSMETADATAWITHCASE TO LD0ROANV
IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0PROCESSSERVICECATEGORYMAPPING')
BEGIN
DROP VIEW LD0V0PROCESSSERVICECATEGORYMAPPING
END
;
CREATE VIEW LD0V0PROCESSSERVICECATEGORYMAPPING AS 
SELECT	*
FROM LD0P0PROCESSSERVICECATEGORYMAPPING
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1EHRJOURNALNOTES')
BEGIN
DROP VIEW LD0V1EHRJOURNALNOTES
END
;
CREATE VIEW LD0V1EHRJOURNALNOTES  
AS  
SELECT EWD.ID,  
EWD.ENCOUNTERID,  
EWD.DSCID,  
EWD.DOCUMENTTYPEOID,  
PMD.PERSONKEY,  
EWD.METADATAID,  
EWD.MODIFYDATE,  
EWD.CASEWORKERID,  
EWD.COMPOSITIONSTATE,  
JN.PROCESSID,  
JN.NAME AS EHRDOCUMENTNAME,  
PMD.SERVICECATEGORYOID,  
PMD.SERVICETYPEOID,  
P.PROCESSTYPEOID,  
K.VALUE AS SERVICECATEGORYTEXT, 
LD.NAMN AS CASEWORKERNAME  
FROM LD0V0EHRWELFAREDOCUMENTS EWD  
JOIN LD0V0PROCESSMETADATA PMD ON PMD.ID = EWD.METADATAID AND EWD.EHRDOCUMENTTYPE = 2  
JOIN LD0V0JOURNALNOTEWELFAREDOCUMENTMAPPING JNDM ON JNDM.EHRWELFAREDOCUMENTSID = EWD.ID  
JOIN LD0V0JOURNALNOTE JN ON JN.ID = JNDM.JOURNALNOTEID  
JOIN LD0V0PROCESS P ON P.ID = JN.PROCESSID  
JOIN LD0V0I LD ON EWD.CASEWORKERID = LD.HAND  
LEFT JOIN LD0V0KANTA K ON K.[VERSION] + '.' + K.ORG_OID = PMD.SERVICECATEGORYOID AND K.ORG_OID <> '0'  
;

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

CREATE VIEW LD0VAA0LMEnrollmentOrg AS SELECT * FROM LD0P0J01 J01 
  WHERE  J01.HANDLORGAM = 1
	  WITH CHECK OPTION 
;

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

CREATE VIEW LD0VAA0SearchAssignmentIfoPartProvider
AS SELECT
N901.*
,N900.PNR
,A.NAMN as PERSNAMN
,A.KUNDNUMMER as PERSONKEY
,A.ADRESS as ADRESS
,A.COADR as COADR
,A.POSTNR as POSTNR
,A.ORT as ORT
,A.FODDAT as FODDAT
,A.KON as KON
,I1.NAMN as UTFHAND_T
,I2.NAMN as UTFMEDHAND_T
,I3.NAMN as UTFMEDHAND2_T
,J01.NAMN as ORGNAMN_T
,Q01_1.KLARTEXT as BEHANDLSATT_T
, CASE 
  WHEN N901.FOMDAT = '00000000' THEN 1 
  WHEN N901.FOMDAT != '00000000' AND N901.TOMDAT = '99999999' THEN 3 
  WHEN N901.TOMDAT != '99999999' THEN 4 
ELSE 9 END as STATUS
FROM LD0V0N901 N901
LEFT OUTER JOIN LD0VYN900 N900 ON N901.UPPDRAGIFONR = N900.UPPDRAGIFONR
LEFT OUTER JOIN LD0V0A A ON A.PNR = N900.PNR
LEFT OUTER JOIN LD0V0I AS I1 ON I1.HAND = N901.UTFHAND
LEFT OUTER JOIN LD0V0I AS I2 ON I2.HAND = N901.UTFMEDHAND
LEFT OUTER JOIN LD0V0I AS I3 ON I3.HAND = N901.UTFMEDHAND2
LEFT OUTER JOIN LD0V0J01 AS J01 ON J01.ORGID = N901.ORGID AND J01.ORGENHNR = N901.ORGENHNR
LEFT OUTER JOIN LD0V0Q01 AS Q01_1 ON Q01_1.KODTYP = 'BEHANDLTYP' AND Q01_1.KOD = N901.BEHANDLSATT
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0TERMS')
BEGIN
DROP VIEW LD0V0TERMS
END
;
CREATE VIEW LD0V0TERMS AS   
SELECT   
TS.CS_ID AS CODESET,  
TSA.VALUE AS VERSION,  
TIE.FOMDAT,  
TIE.TOMDAT,  
TSA2.VALUE AS CODESETNAME,  
TIE.CS_ID AS CODE,  
ISNULL(TIEA.VALUE,'') AS CODENAMESHORT,  
TIEA2.VALUE AS CODENAMELONG,  
ISNULL(TIEA3.VALUE,'') AS ABBREV,   
TSA.VALUE + '.' + TIE.CS_ID AS SERVICECATEGORYOID
FROM LD0V0TERMSYSTEM AS TS   
LEFT JOIN LD0V0TERMSYSTEMATTR AS TSA ON TS.ID = TSA.TERMSYSTEM_ID AND TSA.TYPE='version'   
LEFT JOIN LD0V0TERMSYSTEMATTR AS TSA2 ON TS.ID = TSA2.TERMSYSTEM_ID AND TSA2.TYPE='longname'   
LEFT JOIN LD0V0TERMITEMENTRY AS TIE ON TS.ID = TIE.TERMSYSTEM_ID   
LEFT JOIN LD0V0TERMITEMENTRYATTR AS TIEA ON TIE.ID = TIEA.TERMITEMENTRY_ID AND TIEA.TYPE='shortname'   
LEFT JOIN LD0V0TERMITEMENTRYATTR AS TIEA2 ON TIE.ID = TIEA2.TERMITEMENTRY_ID AND TIEA2.TYPE='longname'   
LEFT JOIN LD0V0TERMITEMENTRYATTR AS TIEA3 ON TIE.ID = TIEA3.TERMITEMENTRY_ID AND TIEA3.TYPE='abbreviation'   
; 

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1EHRWELFAREDOCUMENTSBASE')
BEGIN
DROP VIEW LD0V1EHRWELFAREDOCUMENTSBASE
END
;
CREATE VIEW LD0V1EHRWELFAREDOCUMENTSBASE AS
SELECT ED.*, DM.EHRDOCUMENTNAME
FROM LD0V0EHRWELFAREDOCUMENTS ED
INNER JOIN LD0V0COMPONENTDOCUMENTTYPEMAPPING DM ON DM.ID = ED.COMPONENTDOCUMENTTYPEMAPPINGID
WHERE ED.EHRDOCUMENTTYPE = 1

UNION 

SELECT ED.*, JN.NAME AS EHRDOCUMENTNAME
FROM LD0V0EHRWELFAREDOCUMENTS ED
INNER JOIN LD0V0JOURNALNOTEWELFAREDOCUMENTMAPPING JD ON JD.EHRWELFAREDOCUMENTSID = ED.ID
INNER JOIN LD0V0JOURNALNOTE JN ON JN.ID = JD.JOURNALNOTEID  
WHERE ED.EHRDOCUMENTTYPE = 2
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1EHRWELFAREDOCUMENTDETAILS')
BEGIN
DROP VIEW LD0V1EHRWELFAREDOCUMENTDETAILS
END
;
CREATE VIEW LD0V1EHRWELFAREDOCUMENTDETAILS AS  
SELECT      
ED.ID,  
ED.EHRDOCUMENTNAME,
ED.EHRDOCUMENTTYPE, 
ED.MODIFYDATE,  
ED.COMPONENTDOCUMENTTYPEMAPPINGID,  
ED.ENCOUNTERID,  
ED.COMPOSITIONSTATE,  
ED.DOCUMENTTYPEOID,
LD.HAND CASEWORKERID,  
PMD.ID METADATAID,  
PMD.PROCESSID,   
PMD.OWNERID,  
PMD.SERVICECATEGORYOID,
PMD.SERVICETYPEOID,
P.PROCESSTYPEOID,  
P.[NAME] PROCESSNAME,  
P.PROCESSOWNERTYPE OWNERTYPE,  
PER.KUNDNUMMER PERSONKEY,  
PER.PNR PERSONID,  
PER.NAMN APPLICANTNAME,  
LD.NAMN CASEWORKERNAME,  
K1.VALUE PROCESSTYPETEXT,
K2.VALUE AS SERVICECATEGORYTEXT
FROM LD0V1EHRWELFAREDOCUMENTSBASE ED   
INNER JOIN LD0V0PROCESSMETADATA PMD ON PMD.ID = ED.METADATAID  
INNER JOIN LD0V0PROCESS P ON P.Id = PMD.PROCESSID  
INNER JOIN LD0V0A PER ON PER.KUNDNUMMER = PMD.PERSONKEY  
INNER JOIN LD0V0I LD ON ED.CASEWORKERID = LD.HAND   
INNER JOIN LD0V0KANTA K1 ON P.PROCESSTYPEOID = K1.[VERSION] + '.' + K1.ORG_OID 
LEFT JOIN LD0V0KANTA K2 ON PMD.SERVICECATEGORYOID = K2.[VERSION] + '.' + K2.ORG_OID AND K2.ORG_OID <> '0' 
;


IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1EHRWELFAREDOCUMENTS')
BEGIN
DROP VIEW LD0V1EHRWELFAREDOCUMENTS
END
;
CREATE VIEW LD0V1EHRWELFAREDOCUMENTS AS   
SELECT 
ED.*,
LD.NAMN AS CASEWORKERNAME,
K.VALUE AS SERVICECATEGORYTEXT,
COALESCE(K.ID,'') AS KANTATERMSYSTEMID  
FROM LD0V1EHRWELFAREDOCUMENTSBASE ED      
INNER JOIN LD0V0I LD ON ED.CASEWORKERID = LD.HAND    
INNER JOIN LD0V0PROCESSMETADATA PM ON PM.ID=ED.METADATAID
LEFT JOIN LD0V0KANTA K ON K.[VERSION] + '.' + K.ORG_OID = PM.SERVICECATEGORYOID AND K.ORG_OID <> '0' 
;




IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1NEXTPROCESSES')
BEGIN
DROP VIEW LD0V1NEXTPROCESSES
END
;
CREATE VIEW LD0V1NEXTPROCESSES AS 
SELECT P.ID PROCESSID, 
P.NAME, 
P.DESCRIPTION, 
P.ACTIVE, 
P.PROCESSTYPEOID, 
P.PROCESSOWNERTYPE, 
PSM.SERVICECATEGORYOID, 
K.VALUE SERVICECATEGORYTYPETEXT,
K.ID AS KANTATERMSYSTEMID
FROM LD0V0PROCESSSERVICECATEGORYMAPPING PSM
INNER JOIN LD0V0PROCESS P ON P.ID = PSM.PROCESSID
INNER JOIN LD0V0KANTA K ON PSM.SERVICECATEGORYOID =  K.[VERSION] + '.' + K.ORG_OID AND K.ORG_OID <> '0'
WHERE PROCESSOWNERTYPE <> 1
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1PROCESSSERVICECATEGORY')
BEGIN
DROP VIEW LD0V1PROCESSSERVICECATEGORY 
END
;
CREATE VIEW LD0V1PROCESSSERVICECATEGORY AS
SELECT 
M.SERVICECATEGORYOID,
COALESCE(K.ID,'') AS KANTATERMSYSTEMID,
K.VALUE AS SERVICECATEGORYTEXT,
P.PROCESSOWNERTYPE,
M.OWNERID 
FROM LD0P0PROCESSMETADATA M  
INNER JOIN LD0V0PROCESS P ON M.PROCESSID=P.ID   
LEFT JOIN LD0V0KANTA K ON  M.SERVICECATEGORYOID =  K.[VERSION] + '.' + K.ORG_OID AND K.ORG_OID <> '0'
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1PROCESSSERVICECATEGORYMAPPING')
BEGIN
DROP VIEW LD0V1PROCESSSERVICECATEGORYMAPPING
END
;
CREATE VIEW LD0V1PROCESSSERVICECATEGORYMAPPING AS   
SELECT M.ID,M.PROCESSID,M.SERVICECATEGORYOID, 
K.VALUE AS SERVICECATEGORYTEXT, K.ID AS KANTATERMSYSTEMID 
FROM LD0P0PROCESSSERVICECATEGORYMAPPING M     
INNER JOIN LD0V0KANTA K ON M.SERVICECATEGORYOID = K.[VERSION] + '.' + K.ORG_OID 
AND K.ORG_OID <> '0' 
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1STARTNEWPROCESS')
BEGIN
DROP VIEW LD0V1STARTNEWPROCESS
END
;
CREATE VIEW LD0V1STARTNEWPROCESS AS   
SELECT P.ID PROCESSID, 
P.NAME, 
P.DESCRIPTION, 
P.ACTIVE, 
P.PROCESSTYPEOID, 
P.PROCESSOWNERTYPE, 
P.CANOPENTIMEBOOK,
PSM.SERVICECATEGORYOID, 
K.VALUE SERVICECATEGORYTYPETEXT
FROM LD0V0PROCESS P
INNER JOIN LD0V0PROCESSSERVICECATEGORYMAPPING PSM ON P.ID = PSM.PROCESSID
INNER JOIN LD0V0KANTA K ON PSM.SERVICECATEGORYOID =  K.[VERSION] + '.' + K.ORG_OID AND K.ORG_OID <> '0'
;




IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0PROCESSMETADATA')
BEGIN
DROP VIEW LD0V0PROCESSMETADATA
END
;
CREATE VIEW LD0V0PROCESSMETADATA AS 
SELECT	*
FROM LD0P0PROCESSMETADATA
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1PROCESSMETADATA')
BEGIN
DROP VIEW LD0V1PROCESSMETADATA
END
;
CREATE VIEW LD0V1PROCESSMETADATA AS     
SELECT
M.*,P.PROCESSOWNERTYPE AS OWNERTYPE,
P.PROCESSTYPEOID, P.NAME AS PROCESSNAME
FROM LD0P0PROCESSMETADATA M
LEFT JOIN LD0V0PROCESS P
ON M.PROCESSID = P.ID
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0PROCESSSERVICECATEGORYMAPPING')
BEGIN
DROP VIEW LD0V0PROCESSSERVICECATEGORYMAPPING
END
;
CREATE VIEW LD0V0PROCESSSERVICECATEGORYMAPPING AS 
SELECT	*
FROM LD0P0PROCESSSERVICECATEGORYMAPPING
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1EHRJOURNALNOTES')
BEGIN
DROP VIEW LD0V1EHRJOURNALNOTES
END
;
CREATE VIEW LD0V1EHRJOURNALNOTES  
AS  
SELECT EWD.ID,  
EWD.ENCOUNTERID,  
EWD.DSCID,  
EWD.DOCUMENTTYPEOID,  
PMD.PERSONKEY,  
EWD.METADATAID,  
EWD.MODIFYDATE,  
EWD.CASEWORKERID,  
EWD.COMPOSITIONSTATE,  
JN.PROCESSID,  
JN.NAME AS EHRDOCUMENTNAME,  
PMD.SERVICECATEGORYOID,  
PMD.SERVICETYPEOID,  
P.PROCESSTYPEOID,  
K.VALUE AS SERVICECATEGORYTEXT,  
COALESCE(K.ID,'') AS KANTATERMSYSTEMID,  
LD.NAMN AS CASEWORKERNAME  
FROM LD0V0EHRWELFAREDOCUMENTS EWD  
JOIN LD0V0PROCESSMETADATA PMD ON PMD.ID = EWD.METADATAID AND EWD.EHRDOCUMENTTYPE = 2  
JOIN LD0V0JOURNALNOTEWELFAREDOCUMENTMAPPING JNDM ON JNDM.EHRWELFAREDOCUMENTSID = EWD.ID  
JOIN LD0V0JOURNALNOTE JN ON JN.ID = JNDM.JOURNALNOTEID  
JOIN LD0V0PROCESS P ON P.ID = JN.PROCESSID  
JOIN LD0V0I LD ON EWD.CASEWORKERID = LD.HAND  
LEFT JOIN LD0V0KANTA K ON K.[VERSION] + '.' + K.ORG_OID = PMD.SERVICECATEGORYOID AND K.ORG_OID <> '0'  
;

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

CREATE VIEW LD0VAA0LMEnrollmentOrg AS SELECT * FROM LD0P0J01 J01 
  WHERE  J01.HANDLORGAM = 1
	  WITH CHECK OPTION 
;

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

CREATE VIEW LD0VAA0SearchAssignmentIfoPartProvider
AS SELECT
N901.*
,N900.PNR
,A.NAMN as PERSNAMN
,A.KUNDNUMMER as PERSONKEY
,A.ADRESS as ADRESS
,A.COADR as COADR
,A.POSTNR as POSTNR
,A.ORT as ORT
,A.FODDAT as FODDAT
,A.KON as KON
,I1.NAMN as UTFHAND_T
,I2.NAMN as UTFMEDHAND_T
,I3.NAMN as UTFMEDHAND2_T
,J01.NAMN as ORGNAMN_T
,Q01_1.KLARTEXT as BEHANDLSATT_T
, CASE 
  WHEN N901.FOMDAT = '00000000' THEN 1 
  WHEN N901.FOMDAT != '00000000' AND N901.TOMDAT = '99999999' THEN 3 
  WHEN N901.TOMDAT != '99999999' THEN 4 
ELSE 9 END as STATUS
FROM LD0V0N901 N901
LEFT OUTER JOIN LD0VYN900 N900 ON N901.UPPDRAGIFONR = N900.UPPDRAGIFONR
LEFT OUTER JOIN LD0V0A A ON A.PNR = N900.PNR
LEFT OUTER JOIN LD0V0I AS I1 ON I1.HAND = N901.UTFHAND
LEFT OUTER JOIN LD0V0I AS I2 ON I2.HAND = N901.UTFMEDHAND
LEFT OUTER JOIN LD0V0I AS I3 ON I3.HAND = N901.UTFMEDHAND2
LEFT OUTER JOIN LD0V0J01 AS J01 ON J01.ORGID = N901.ORGID AND J01.ORGENHNR = N901.ORGENHNR
LEFT OUTER JOIN LD0V0Q01 AS Q01_1 ON Q01_1.KODTYP = 'BEHANDLTYP' AND Q01_1.KOD = N901.BEHANDLSATT
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0TERMS')
BEGIN
DROP VIEW LD0V0TERMS
END
;
CREATE VIEW LD0V0TERMS AS   
SELECT   
TS.CS_ID AS CODESET,  
TSA.VALUE AS VERSION,  
TIE.FOMDAT,  
TIE.TOMDAT,  
TSA2.VALUE AS CODESETNAME,  
TIE.CS_ID AS CODE,  
ISNULL(TIEA.VALUE,'') AS CODENAMESHORT,  
TIEA2.VALUE AS CODENAMELONG,  
ISNULL(TIEA3.VALUE,'') AS ABBREV,   
TSA.VALUE + '.' + TIE.CS_ID AS SERVICECATEGORYOID
FROM LD0V0TERMSYSTEM AS TS   
LEFT JOIN LD0V0TERMSYSTEMATTR AS TSA ON TS.ID = TSA.TERMSYSTEM_ID AND TSA.TYPE='version'   
LEFT JOIN LD0V0TERMSYSTEMATTR AS TSA2 ON TS.ID = TSA2.TERMSYSTEM_ID AND TSA2.TYPE='longname'   
LEFT JOIN LD0V0TERMITEMENTRY AS TIE ON TS.ID = TIE.TERMSYSTEM_ID   
LEFT JOIN LD0V0TERMITEMENTRYATTR AS TIEA ON TIE.ID = TIEA.TERMITEMENTRY_ID AND TIEA.TYPE='shortname'   
LEFT JOIN LD0V0TERMITEMENTRYATTR AS TIEA2 ON TIE.ID = TIEA2.TERMITEMENTRY_ID AND TIEA2.TYPE='longname'   
LEFT JOIN LD0V0TERMITEMENTRYATTR AS TIEA3 ON TIE.ID = TIEA3.TERMITEMENTRY_ID AND TIEA3.TYPE='abbreviation'   
; 

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1EHRWELFAREDOCUMENTSBASE')
BEGIN
DROP VIEW LD0V1EHRWELFAREDOCUMENTSBASE
END
;
CREATE VIEW LD0V1EHRWELFAREDOCUMENTSBASE AS
SELECT ED.*, DM.EHRDOCUMENTNAME
FROM LD0V0EHRWELFAREDOCUMENTS ED
INNER JOIN LD0V0COMPONENTDOCUMENTTYPEMAPPING DM ON DM.ID = ED.COMPONENTDOCUMENTTYPEMAPPINGID
WHERE ED.EHRDOCUMENTTYPE = 1

UNION 

SELECT ED.*, JN.NAME AS EHRDOCUMENTNAME
FROM LD0V0EHRWELFAREDOCUMENTS ED
INNER JOIN LD0V0JOURNALNOTEWELFAREDOCUMENTMAPPING JD ON JD.EHRWELFAREDOCUMENTSID = ED.ID
INNER JOIN LD0V0JOURNALNOTE JN ON JN.ID = JD.JOURNALNOTEID  
WHERE ED.EHRDOCUMENTTYPE = 2
;


IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1EHRWELFAREDOCUMENTDETAILS')
BEGIN
DROP VIEW LD0V1EHRWELFAREDOCUMENTDETAILS
END
;
CREATE VIEW LD0V1EHRWELFAREDOCUMENTDETAILS AS  
SELECT      
ED.ID,  
ED.EHRDOCUMENTNAME,
ED.EHRDOCUMENTTYPE, 
ED.MODIFYDATE,  
ED.COMPONENTDOCUMENTTYPEMAPPINGID,  
ED.ENCOUNTERID,  
ED.COMPOSITIONSTATE,  
ED.DOCUMENTTYPEOID,
LD.HAND CASEWORKERID,  
PMD.ID METADATAID,  
PMD.PROCESSID,   
PMD.OWNERID,  
PMD.SERVICECATEGORYOID,
PMD.SERVICETYPEOID,
P.PROCESSTYPEOID,  
P.[NAME] PROCESSNAME,  
P.PROCESSOWNERTYPE OWNERTYPE,  
PER.KUNDNUMMER PERSONKEY,  
PER.PNR PERSONID,  
PER.NAMN APPLICANTNAME,  
LD.NAMN CASEWORKERNAME,  
K1.VALUE PROCESSTYPETEXT,
K2.VALUE AS SERVICECATEGORYTEXT,
COALESCE(K2.ID,'') AS KANTATERMSYSTEMID
FROM LD0V1EHRWELFAREDOCUMENTSBASE ED   
INNER JOIN LD0V0PROCESSMETADATA PMD ON PMD.ID = ED.METADATAID  
INNER JOIN LD0V0PROCESS P ON P.Id = PMD.PROCESSID  
INNER JOIN LD0V0A PER ON PER.KUNDNUMMER = PMD.PERSONKEY  
INNER JOIN LD0V0I LD ON ED.CASEWORKERID = LD.HAND   
INNER JOIN LD0V0KANTA K1 ON P.PROCESSTYPEOID = K1.[VERSION] + '.' + K1.ORG_OID 
LEFT JOIN LD0V0KANTA K2 ON PMD.SERVICECATEGORYOID = K2.[VERSION] + '.' + K2.ORG_OID AND K2.ORG_OID <> '0' 
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1EHRWELFAREDOCUMENTS')
BEGIN
DROP VIEW LD0V1EHRWELFAREDOCUMENTS
END
;
CREATE VIEW LD0V1EHRWELFAREDOCUMENTS AS   
SELECT 
ED.*,
LD.NAMN AS CASEWORKERNAME,
K.VALUE AS SERVICECATEGORYTEXT,
COALESCE(K.ID,'') AS KANTATERMSYSTEMID  
FROM LD0V1EHRWELFAREDOCUMENTSBASE ED      
INNER JOIN LD0V0I LD ON ED.CASEWORKERID = LD.HAND    
INNER JOIN LD0V0PROCESSMETADATA PM ON PM.ID=ED.METADATAID
LEFT JOIN LD0V0KANTA K ON K.[VERSION] + '.' + K.ORG_OID = PM.SERVICECATEGORYOID AND K.ORG_OID <> '0' 
;


IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1NEXTPROCESSES')
BEGIN
DROP VIEW LD0V1NEXTPROCESSES
END
;
CREATE VIEW LD0V1NEXTPROCESSES AS 
SELECT P.ID PROCESSID, 
P.NAME, 
P.DESCRIPTION, 
P.ACTIVE, 
P.PROCESSTYPEOID, 
P.PROCESSOWNERTYPE, 
PSM.SERVICECATEGORYOID, 
K.VALUE SERVICECATEGORYTYPETEXT
FROM LD0V0PROCESSSERVICECATEGORYMAPPING PSM
INNER JOIN LD0V0PROCESS P ON P.ID = PSM.PROCESSID
INNER JOIN LD0V0KANTA K ON PSM.SERVICECATEGORYOID =  K.[VERSION] + '.' + K.ORG_OID AND K.ORG_OID <> '0'
WHERE PROCESSOWNERTYPE <> 1
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1PROCESSSERVICECATEGORY')
BEGIN
DROP VIEW LD0V1PROCESSSERVICECATEGORY 
END
;
CREATE VIEW LD0V1PROCESSSERVICECATEGORY AS
SELECT 
M.SERVICECATEGORYOID,
K.VALUE AS SERVICECATEGORYTEXT,
P.PROCESSOWNERTYPE,
M.OWNERID 
FROM LD0P0PROCESSMETADATA M  
INNER JOIN LD0V0PROCESS P ON M.PROCESSID=P.ID   
LEFT JOIN LD0V0KANTA K ON  M.SERVICECATEGORYOID =  K.[VERSION] + '.' + K.ORG_OID AND K.ORG_OID <> '0'
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1PROCESSSERVICECATEGORYMAPPING')
BEGIN
DROP VIEW LD0V1PROCESSSERVICECATEGORYMAPPING
END
;
CREATE VIEW LD0V1PROCESSSERVICECATEGORYMAPPING AS   
SELECT M.ID,
M.PROCESSID,
M.SERVICECATEGORYOID,    
K.VALUE AS SERVICECATEGORYTEXT 
FROM LD0P0PROCESSSERVICECATEGORYMAPPING M     
INNER JOIN LD0V0KANTA K ON M.SERVICECATEGORYOID = K.[VERSION]    
 + '.' + K.ORG_OID AND K.ORG_OID <> '0' 
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1STARTNEWPROCESS')
BEGIN
DROP VIEW LD0V1STARTNEWPROCESS
END
;
CREATE VIEW LD0V1STARTNEWPROCESS AS   
SELECT P.ID PROCESSID, 
P.NAME, 
P.DESCRIPTION, 
P.ACTIVE, 
P.PROCESSTYPEOID, 
P.PROCESSOWNERTYPE, 
P.CANOPENTIMEBOOK,
PSM.SERVICECATEGORYOID, 
K.VALUE SERVICECATEGORYTYPETEXT,
K.ID AS KANTATERMSYSTEMID
FROM LD0V0PROCESS P
INNER JOIN LD0V0PROCESSSERVICECATEGORYMAPPING PSM ON P.ID = PSM.PROCESSID
INNER JOIN LD0V0KANTA K ON PSM.SERVICECATEGORYOID =  K.[VERSION] + '.' + K.ORG_OID AND K.ORG_OID <> '0'
;


IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1SEARCHPROCESS')
BEGIN
DROP VIEW LD0V1SEARCHPROCESS
END
;
CREATE VIEW LD0V1SEARCHPROCESS AS 
SELECT 
PS.ID,
PS.NAME,
PS.PROCESSTYPEOID,
PMD.ID AS 'PROCESSMETADATAID',
PMD.OWNERTYPE,
PMD.OWNERID,
PMD.PERSONKEY,
PMD.STARTDATE,
PMD.ENDDATE,
PMD.CASEWORKERID,
PMD.COCASEWORKERID,
PMD.ORGANIZATIONID,
PMD.CASEWORKERORGUNITID,
PMD.COCASEWORKERORGUNITID,
PMD.SERVICECATEGORYOID,
PMD.SERVICETYPEOID AS 'KANTASERVICETYPEID',
PR.PNR AS 'PERSONID',
PR.NAMN AS 'PERSONNAME',
H1.NAMN AS 'CASEWORKERNAME',
H2.NAMN AS 'COCASEWORKERNAME',
J1.NAMN AS 'CASEWORKERORGANIZATIONNAME',
J2.NAMN AS 'COCASEWORKERORGANIZATIONNAME',
K1.VALUE AS 'PROCESSTYPE',
K.VALUE SERVICECATEGORYTYPETEXT,
'' AS 'SERVICETYPE',
'' AS 'SERVICETYPEID',
0 AS 'SERVICETYPECODE' 
FROM LD0V0PROCESS PS 
INNER JOIN LD0V1PROCESSMETADATA PMD ON PS.ID = PMD.PROCESSID 
INNER JOIN LD0V0A PR ON PMD.PERSONKEY = PR.KUNDNUMMER 
LEFT JOIN LD0V0I H1 ON PMD.CASEWORKERID = H1.HAND 
LEFT JOIN LD0V0I H2 ON PMD.COCASEWORKERID = H2.HAND 
LEFT JOIN LD0V0J01 J1 ON PMD.ORGANIZATIONID = J1.ORGID AND PMD.CASEWORKERORGUNITID = J1.ORGENHNR 
LEFT JOIN LD0V0J01 J2 ON PMD.ORGANIZATIONID = J2.ORGID AND PMD.COCASEWORKERORGUNITID = J2.ORGENHNR 
LEFT JOIN LD0V0KANTA K1 ON PS.PROCESSTYPEOID = K1.[VERSION] + '.' + K1.ORG_OID 
LEFT JOIN LD0V0KANTA K ON PMD.SERVICECATEGORYOID =  K.[VERSION] + '.' + K.ORG_OID AND K.ORG_OID <> '0'  WHERE PMD.OWNERTYPE !=3 

UNION ALL 

SELECT PS.ID,
PS.NAME,
PS.PROCESSTYPEOID,
PMD.ID AS 'PROCESSMETADATAID',
PMD.OWNERTYPE,
PMD.OWNERID,
PMD.PERSONKEY,
PMD.STARTDATE,
PMD.ENDDATE,
PMD.CASEWORKERID,
PMD.COCASEWORKERID,
PMD.ORGANIZATIONID,
PMD.CASEWORKERORGUNITID,
PMD.COCASEWORKERORGUNITID,
PMD.SERVICECATEGORYOID,
PMD.SERVICETYPEOID AS 'KANTASERVICETYPEID',
PR.PNR AS 'PERSONID',
PR.NAMN AS 'PERSONNAME',
H1.NAMN AS 'CASEWORKERNAME',
H2.NAMN AS 'COCASEWORKERNAME',
J1.NAMN AS 'CASEWORKERORGANIZATIONNAME',
J2.NAMN AS 'COCASEWORKERORGANIZATIONNAME',
K1.VALUE AS 'PROCESSTYPE',
K.VALUE SERVICECATEGORYTYPETEXT,
Q01.KLARTEXT AS 'SERVICETYPE',
K2.[VERSION] + '.' + K2.ORG_OID AS 'SERVICETYPEOID' ,
Q01.KOD AS 'SERVICETYPECODE' 
FROM LD0V0PROCESS PS 
INNER JOIN LD0V1PROCESSMETADATA PMD ON PS.ID = PMD.PROCESSID 
INNER JOIN LD0V0A PR ON PMD.PERSONKEY = PR.KUNDNUMMER 
LEFT JOIN LD0V0I H1 ON PMD.CASEWORKERID = H1.HAND 
LEFT JOIN LD0V0I H2 ON PMD.COCASEWORKERID = H2.HAND 
LEFT JOIN LD0V0J01 J1 ON PMD.ORGANIZATIONID = J1.ORGID AND PMD.CASEWORKERORGUNITID = J1.ORGENHNR 
LEFT JOIN LD0V0J01 J2 ON PMD.ORGANIZATIONID = J2.ORGID AND PMD.COCASEWORKERORGUNITID = J2.ORGENHNR 
LEFT JOIN LD0V0KANTA K1 ON PS.PROCESSTYPEOID = K1.[VERSION] + '.' + K1.ORG_OID 
LEFT JOIN LD0V0KANTA K ON PMD.SERVICECATEGORYOID =  K.[VERSION] + '.' + K.ORG_OID AND K.ORG_OID <> '0' 
INNER JOIN LD0V0CCASEWORKERSERVICES S ON S.INSNR=PMD.OWNERID 
INNER JOIN LD0P0Q01 Q01 ON S.INSTYP = Q01.KOD AND Q01.KODTYP = 'INSATS' AND Q01.AKTIV = 1 
INNER JOIN LD0V0KANTA K2 ON PMD.SERVICETYPEOID = K2.[VERSION] + '.' + K2.ORG_OID AND Q01.XTXT9 = K2.OID
WHERE PMD.OWNERTYPE =3 AND Q01.KOD>0 
;


IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0SearchAssignmentIfoProvider')
BEGIN
DROP VIEW LD0VAA0SearchAssignmentIfoProvider
END
;
CREATE VIEW LD0VAA0SearchAssignmentIfoProvider
AS SELECT DISTINCT
N900.*
,A.NAMN as PERSNAMN
,A.KUNDNUMMER as PERSONKEY
,A.ADRESS as ADRESS
,A.COADR as COADR
,A.POSTNR as POSTNR
,A.ORT as ORT
,A.FODDAT as FODDAT
,A.KON as KON
,I1.NAMN as UTFHAND_T
,I2.NAMN as UTFMEDHAND_T
,I3.NAMN as UTFMEDHAND2_T
,I4.NAMN as MOTHAND_T
,J01.NAMN as ORGNAMN_T
,Q01_1.KLARTEXT as STATUS_T
,C.ORGID AS INV_ORGANIZATIONID
,C.ORGENHNR AS INV_ORGANIZATIONUNITID
,C.HAND AS INV_CASEWORKER
,C.MEDHAND AS INV_COCASEWORKER
,J01_INV.NAMN AS INV_ORGNAMN_T
,VI_INV1.NAMN AS INV_CASEWORKERNAME_T
,VI_INV2.NAMN AS INV_COCASEWORKERNAME_T
,C20.ORGID AS SERV_ORGANIZATIONID
,C20.ORGENHNR AS SERV_ORGANIZATIONUNITID
,C20.HAND AS SERV_CASEWORKER
,C20.MEDHAND AS SERV_COCASEWORKER
,J01_SERV.NAMN AS SERV_ORGNAMN_T
,VI_SERV1.NAMN AS SERV_CASEWORKERNAME_T
,VI_SERV2.NAMN AS SERV_COCASEWORKERNAME_T
,CASE WHEN N901.DELUPPDRAGNR IS NULL THEN '0' ELSE '1' END DELUPPDRAG_FINNS
FROM LD0V0N900 N900
LEFT OUTER JOIN LD0V0N901 N901
ON N900.UPPDRAGIFONR=N901.UPPDRAGIFONR
INNER JOIN LD0VYE E ON E.BESLUTNR = N900.BESLUTNR
LEFT OUTER JOIN LD0V0A A ON A.PNR = N900.PNR
LEFT OUTER JOIN LD0V0I AS I1 ON I1.HAND = N900.UTFHAND
LEFT OUTER JOIN LD0V0I AS I2 ON I2.HAND = N900.UTFMEDHAND
LEFT OUTER JOIN LD0V0I AS I3 ON I3.HAND = N900.UTFMEDHAND2
LEFT OUTER JOIN LD0V0I AS I4 ON I4.HAND = N900.MOTHAND
LEFT OUTER JOIN LD0V0J01 AS J01 ON J01.ORGID = N900.ORGID AND J01.ORGENHNR = N900.ORGENHNR
LEFT OUTER JOIN LD0V0Q01 AS Q01_1 ON Q01_1.KODTYP = 'UIFOSTATUS' AND Q01_1.KOD = N900.STATUS
LEFT OUTER JOIN LD0VYC AS C ON C.AEVERKNR = E.AEVERKNR
LEFT OUTER JOIN LD0V0J01 AS J01_INV ON J01_INV.ORGENHNR = C.ORGENHNR AND J01_INV.ORGID = C.ORGID
LEFT OUTER JOIN LD0V0I AS VI_INV1 ON VI_INV1.HAND = C.HAND
LEFT OUTER JOIN LD0V0I AS VI_INV2 ON VI_INV2.HAND = C.MEDHAND
LEFT OUTER JOIN LD0VYC20 AS C20 ON C20.INSNR = E.INSNR
LEFT OUTER JOIN LD0P0J01 AS J01_SERV ON J01_SERV.ORGENHNR = C20.ORGENHNR AND J01_SERV.ORGID = C20.ORGID
LEFT OUTER JOIN LD0V0I AS VI_SERV1 ON VI_SERV1.HAND = C20.HAND
LEFT OUTER JOIN LD0V0I AS VI_SERV2 ON VI_SERV2.HAND = C20.MEDHAND
;

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

CREATE VIEW LD0VAA0LMSEARCHDOCUMENTS
AS 
SELECT 
T41.DOKUMENTNR,
T41.AGARNR,
T41.AGARTYP,
T41.DDATUM,
T41.DRUBRIK,
T41.DOKID,
T41.UPDDAT,
T41.UPDSIGN,
CASE 
WHEN T41.AGARTYP = 30 OR T41.AGARTYP = 32 OR T41.AGARTYP = 64 THEN ENROLLMENT.ORGID 
WHEN T41.AGARTYP = 48 THEN LMASSIGNMENT.MOTORGID 
WHEN T41.AGARTYP = 71 THEN PARTICIPANT.ORGID
ELSE '0' END AS ORGID,
CASE
WHEN T41.AGARTYP = 30 OR T41.AGARTYP = 32 OR T41.AGARTYP = 64 THEN ENROLLMENT.ORGENHNR 
WHEN T41.AGARTYP = 48 THEN LMASSIGNMENT.MOTORGENHNR
WHEN T41.AGARTYP = 71 THEN PARTICIPANT.ORGENHNR
ELSE '0' END AS ORGENHNR,
T41.UTLINSNR,
T41.DOCTYPE,
T41.ACTIVE,
T41.DOKTYP,
T41.PERSONNR,
T41.DELETED,
T41.LNDOKTYP,
T41.HUVUDOBJTYP,
T41.HUVUDOBJNR,
T41.VERSIONNR,
T41.ANSVHAND,
T41.ANSVTITEL,
T41.ANTDOKTYP,
T41.EBREV_SENDDATE,
T41.EBREV_SENDTIME,
T41.EBREV_SENDSIGN,
T41.EBREV_SENDACK,
T41.HANDELSEDATE,
T41.LOCKED,
T41.LOCKDATE,
T41.LOCKSIGN,
T41.SKAPADAVGALLRING,
T41.PUBLISH_DATE,
T41.PUBLISH_MOBIL,
T41.PUBLISH_EMAIL,
T41.LIFECARE_CREATED,
T41.TEMPLATE,
T41.RESPOND_DATE,
T41.PUBLISH_HANDL,
T41.PUBLISH_ORGID,
T41.PUBLISH_ORGENHNR,
T41.ACTNOTETYP,
T41.RESPOND_WANTED,
T41.PUBLISH_STATUS,
U35.AKTIVITETNR,
Q01_1.KLARTEXT AS ANTTYP_T,
Q01_2.KLARTEXT AS DOKTYP_T,
Q01_2.XNUM4 AS BLANKETT,P.NAMN, 
Q01_3.KLARTEXT AS LNDOKTYP_T
FROM LD0V0T41 AS T41 
JOIN LD0V0A AS P 
ON T41.PERSONNR = P.PNR 
LEFT JOIN LD0V0U35 U35 ON U35.AKTIVITETNR = T41.AGARNR AND T41.AGARTYP = 71
LEFT JOIN LD0V0U10 AS ENROLLMENT ON T41.HUVUDOBJNR = ENROLLMENT.INSKRIVNINGSNR AND T41.HUVUDOBJTYP = 7405
LEFT JOIN LD0V0U90 AS LMASSIGNMENT ON T41.AGARNR = LMASSIGNMENT.LNUPPDRAGNR AND T41.AGARTYP = 48
LEFT JOIN LD0V0U10 AS PARTICIPANT ON U35.INSKRIVNINGSNR = PARTICIPANT.INSKRIVNINGSNR 
LEFT OUTER JOIN LD0V0Q01 AS Q01_1 ON T41.ANTDOKTYP = Q01_1.KOD AND Q01_1.KODTYP = 'ANTTYP' 
LEFT OUTER JOIN LD0V0Q01 AS Q01_2 ON T41.DOKTYP = Q01_2.KOD AND Q01_2.KODTYP = 'TYPDOK' 
LEFT OUTER JOIN LD0V0Q01 AS Q01_3 ON T41.DOKTYP = Q01_3.KOD AND Q01_3.KODTYP = 'LNDOKTYP' 
WHERE (T41.DOKTYP > 0 OR T41.LNDOKTYP > 0) AND (T41.DELETED = 0) AND (T41.AGARTYP IN (30,32,62,66,48, 64,71))
;

GRANT ALL ON LD0VAA0LMSEARCHDOCUMENTS TO LD0ROANV
;


IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0KANTA')
BEGIN
DROP VIEW LD0V0KANTA 
END
;
CREATE VIEW LD0V0KANTA AS
SELECT  
D.VALUE AS NIVA,  
TSA.VALUE AS VERSION,  
A.CS_ID AS ID,  
A.CS_ID + '.' + B.CS_ID AS OID,  
CONVERT(VARCHAR, B.FOMDAT, 112) AS FOMDAT,  
CONVERT(VARCHAR, B.TOMDAT, 112) AS TOMDAT,  
ISNULL(C.VALUE, E.VALUE) AS VALUE,  
ISNULL(I.VALUE, '') AS SV_VALUE,
ISNULL(C.ID, 0) AS SERVCATID,  
B.CS_ID AS ORG_OID,  
ISNULL(F.VALUE, '') AS WELFAREMAINUNIT,  
ISNULL(G.VALUE, '') AS WELFARESERVICEUNIT,  
ISNULL(H.VALUE, '') AS YTUNNUS   
FROM LD0V0TERMSYSTEM A   
JOIN LD0V0TERMITEMENTRY B ON A.ID = B.TERMSYSTEM_ID   
LEFT OUTER JOIN LD0V0TERMITEMENTRYATTR C ON B.ID = C.TERMITEMENTRY_ID AND C.TYPE = 'shortname'   
LEFT OUTER JOIN LD0V0TERMITEMENTRYATTR E ON B.ID = E.TERMITEMENTRY_ID AND E.TYPE = 'longname'   
LEFT OUTER JOIN LD0V0TERMITEMENTRYATTR F ON B.ID = F.TERMITEMENTRY_ID AND F.TYPE = 'Sos.toimintayksikkö'   
LEFT OUTER JOIN LD0V0TERMITEMENTRYATTR G ON B.ID = G.TERMITEMENTRY_ID AND G.TYPE = 'Sos.palveluyksikkö'   
LEFT OUTER JOIN LD0V0TERMITEMENTRYATTR H ON B.ID = H.TERMITEMENTRY_ID AND H.TYPE = 'Y-Tunnus'   
LEFT OUTER JOIN LD0V0TERMITEMENTRYATTR I ON B.ID = I.TERMITEMENTRY_ID AND I.TYPE = 'Långt_namn'   
JOIN LD0V0TERMITEMENTRYATTR D ON B.ID = D.TERMITEMENTRY_ID AND D.TYPE = 'hierarchylevel' AND (C.VALUE IS NOT NULL OR E.VALUE IS NOT NULL)  
LEFT JOIN LD0V0TERMSYSTEMATTR AS TSA ON A.ID = TSA.TERMSYSTEM_ID AND TSA.TYPE = 'version'   
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0STATISTICSRESOURCEALLOCATIONS')
BEGIN
DROP VIEW LD0VAA0STATISTICSRESOURCEALLOCATIONS 
END
;
CREATE VIEW LD0VAA0STATISTICSRESOURCEALLOCATIONS AS
   SELECT 
  V0L.ANNANKOMU,
  V0L.AVTAL,
  N30.belopp,
  N70.FOMDAT,
  V0I.HAND AS CASEWORKERID,
  V0I.NAMN AS CASEWORKER,
  V0L.HUVUDMAN,
  Q01_INSATS.KLARTEXT AS INSATS,
  C20.INSNR,
  Q01_KOMU.KLARTEXT AS KOMMUN,
  V0A.KON,
  Q01_LAGRUM.KLARTEXT AS LAGRUM,
  Q01_LAN.KLARTEXT AS LAN,
  L50.LONBEL,
  V0A.NAMN,
  V0L.OMRADE,
  C20.ORGENHNR,
  V0A.PNR,
  Q01_PLFORM.KLARTEXT AS Placeringsform,
  N20.SUMMA AS Intäkterbelopp,
  N70.PLACNR,
  N50.SUMMA AS Lönekostnaderbelopp,
  N50.ARVODE,
  N50.OMKOSTNAD,
  N50.TOMDAT AS Preltill,
  N70.PROCENT,
  V0L.RESURSBEN,
  Q01_RESURSKAT.KLARTEXT AS RESURSKAT_TEXT,
  Q01_RESURSKAT.KOD AS RESURSKAT,
  V0L.PNR AS RESURSPNR,
  N70.RESTILLDNR,
  Q01_RESURSTYP.KLARTEXT AS RESURSTYP_TEXT,
  Q01_RESURSTYP.KOD AS RESURSTYP,
  Q01_StatistikGroup1.KLARTEXT AS StatistikGroup1_Text,
  Q01_StatistikGroup2.KLARTEXT AS StatistikGroup2_Text,
  Q01_StatistikGroup3.KLARTEXT AS StatistikGroup3_Text,
  Q01_StatistikGroup4.KLARTEXT AS StatistikGroup4_Text,
  Q01_StatistikGroup5.KLARTEXT AS StatistikGroup5_Text,
   Q01_StatistikGroup1.KOD AS StatistikGroup1,
  Q01_StatistikGroup2.KOD AS StatistikGroup2,
  Q01_StatistikGroup3.KOD AS StatistikGroup3,
  Q01_StatistikGroup4.KOD AS StatistikGroup4,
  Q01_StatistikGroup5.KOD AS StatistikGroup5,
  Q01_RESURSSTAT.KLARTEXT AS STATUS,
   N70.TOMDAT,
  Q01_HEMTYP.KLARTEXT AS HEMTYP,
  J01.NAMN AS ORGNAMN,
  V0A.FODDAT
  FROM LD0V1N70 N70
left join LD0VYC20  C20 on N70.INSNR=C20.INSNR 
left join LD0V0A  V0A on C20.AKTPNR = V0A.PNR
left join LD0V0I  V0I on N70.HAND = V0I.HAND
left join LD0V0L01  L01 on N70.RESURSSOMNR=L01.RESURSSOMNR 
left join LD0V0L V0L on L01.RESURSNR =V0L.RESURSNR
left join LD0V0N50 N50 on N50.RESTILLDNR!=null and N50.RESTILLDNR=N70.RESTILLDNR 
left join LD0V0L50 L50 on L50.RESTILLDNR!=null AND L50.RESTILLDNR=N70.RESTILLDNR
left join LD0V0N20 N20 on N20.RESTILLDNR!=null AND N20.RESTILLDNR=N70.RESTILLDNR
left join LD0V0N30 N30 on N30.RESTILLDNR!=null AND N30.RESTILLDNR=N70.RESTILLDNR
left join LD0V0J01 J01 on J01.ORGID = C20.ORGID AND N70.ORGENHNR = J01.ORGENHNR 
left join LD0V0Q01 Q01_INSATS on Q01_INSATS.KODTYP = 'INSATS' AND Q01_INSATS.KOD = C20.INSTYP
left join LD0V0Q04 Q04_INSATS on Q04_INSATS.KODTYP = 'INSATS' AND Q04_INSATS.KOD = C20.INSTYP
left join LD0V0Q01 Q01_StatistikGroup1 on Q01_StatistikGroup1.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup1.XTXT1 = 'INSATS' AND Q01_StatistikGroup1.KOD=Q04_INSATS.STATGRP1
left join LD0V0Q01 Q01_StatistikGroup2 on Q01_StatistikGroup2.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup2.XTXT1 = 'INSATS' AND Q01_StatistikGroup2.KOD=Q04_INSATS.STATGRP2
left join LD0V0Q01 Q01_StatistikGroup3 on Q01_StatistikGroup3.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup3.XTXT1 = 'INSATS' AND Q01_StatistikGroup3.KOD=Q04_INSATS.STATGRP3
left join LD0V0Q01 Q01_StatistikGroup4 on Q01_StatistikGroup4.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup4.XTXT1 = 'INSATS' AND Q01_StatistikGroup4.KOD=Q04_INSATS.STATGRP4
left join LD0V0Q01 Q01_StatistikGroup5 on Q01_StatistikGroup5.KODTYP = 'STATGRUPP' AND Q01_StatistikGroup5.XTXT1 = 'INSATS' AND Q01_StatistikGroup5.KOD=Q04_INSATS.STATGRP5
left join LD0V0Q01  Q01_RESURSKAT on Q01_RESURSKAT.KODTYP = 'RESURSKAT' AND Q01_RESURSKAT.KOD = V0L.RESURSKAT
left join LD0V0Q01 Q01_RESURSTYP  on  Q01_RESURSTYP.KODTYP = 'RESURSTYP' AND Q01_RESURSTYP.KOD = L01.RESURSTYP
left join LD0V0Q01  Q01_KOMU on Q01_KOMU.KODTYP = 'KOMU' AND Q01_KOMU.KOD = V0L.KOMMUN
left join LD0V0Q01  Q01_RESHUVMAN on Q01_RESHUVMAN.KODTYP = 'RESHUVMAN' AND Q01_RESHUVMAN.KOD = V0L.HUVUDTYP
left join  LD0V0Q01  Q01_LAN on Q01_LAN.KODTYP = 'LÄN' AND Q01_LAN.KOD = V0L.FOLKLAN
left join  LD0V0Q01  Q01_RESURSSTAT  on Q01_RESURSSTAT.KODTYP = 'RESURSSTAT' AND Q01_RESURSSTAT.KOD = V0L.RESURSSTAT
left join  LD0V0Q01  Q01_PLFORM on Q01_PLFORM.KODTYP = 'PLFORM' AND Q01_PLFORM.KOD = L01.SCBSTAT
left join  LD0V0Q01 Q01_BETJOMR on Q01_BETJOMR.KODTYP = 'BETJOMR' AND Q01_BETJOMR.KOD = V0A.BETJOMR
left join  LD0V0Q01 Q01_KOMMUNDEL on Q01_KOMMUNDEL.KODTYP = 'KOMMUNDEL' AND Q01_KOMMUNDEL.KOD = V0A.KOMDEL
left join  LD0V0Q01 Q01_HEMTYP on Q01_HEMTYP.KODTYP = 'HEMTYP' AND Q01_HEMTYP.KOD = N70.HEMTYP
left join  LD0V0Q01 Q01_LAGRUM  on Q01_LAGRUM.KODTYP = 'LAGRUM' AND Q01_LAGRUM.KOD = Q01_INSATS.XNUM5 
left join  LD0V0Q01 Q01_LAGRUMTYP on Q01_LAGRUMTYP.KODTYP = 'LAGRUMTYP' AND Q01_LAGRUMTYP.KOD=2
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1PROCESSMETADATAWITHCASE')
BEGIN
DROP VIEW LD0V1PROCESSMETADATAWITHCASE
END
;
CREATE VIEW LD0V1PROCESSMETADATAWITHCASE
AS
SELECT	M.Id AS PROCESSMETADATAID,
		M.OWNERID,
		P.PROCESSOWNERTYPE AS OWNERTYPE,
		P.Id AS PROCESSID, 
		P.NAME AS PROCESSNAME,
		C.CASEOID
FROM LD0P0PROCESSMETADATA M 
LEFT JOIN LD0V0PROCESS P ON M.PROCESSID = P.ID
LEFT JOIN LD0V0CASE C ON C.ID = M.CASEID
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0SearchHousingSituation')
BEGIN
DROP VIEW LD0VAA0SearchHousingSituation
END
;
CREATE VIEW LD0VAA0SearchHousingSituation AS
SELECT U26.*,
Q1.KLARTEXT AS FASTIGHETSAG_T,
Q1.XTXT1 AS FASTIGHETSAG_BENAMNING,
Q1.XTXT2 AS FASTIGHETSAG_KONTAKTPERSON,
Q1.XTXT3 AS FASTIGHETSAG_GATUADRESS,
Q1.XTXT4 AS FASTIGHETSAG_POSTNUMMER,
Q1.XTXT5 AS FASTIGHETSAG_POSTORT,
Q2.KLARTEXT AS BOSTADTYP_T,
Q3.KLARTEXT AS LAND_T,
Q4.KLARTEXT AS OMRADE_T,
V0A.ADRESS AS ADRESS,
V0A.ARBTEL AS ARBTEL,
V0A.COADR AS COADRESS,
V0A.EPOSTHEM AS EPOSTHEM,
V0A.EPOSTARB AS EPOSTARB,
V0A.FODDAT AS FODDAT,
V0A.FODLAND AS FODLAND,
V0A.HEMTEL AS HEMTEL,
V0A.MOBIL AS MOBIL,
V0A.KON AS KON,
V0A.NAMN AS PERSONNAMN,
V0A.ORT AS ORT,
V0A.PNR AS PERSONNUMMER,
V0A.POSTNR AS POSTNR,
V0A.KUNDNUMMER AS KUNDNUMMER
FROM LD0P0U26 AS U26
JOIN LD0V0U01 AS U01 ON U01.KARTLAGGNR = U26.KARTLAGGNR
JOIN LD0V0A AS V0A ON V0A.PNR = U01.PERSONNR
LEFT JOIN LD0V0Q01 AS Q1 ON Q1.KOD = U26.FASTIGHETSAG AND Q1.KODTYP = 'LNFASTAG'
LEFT JOIN LD0V0Q01 AS Q2 ON Q2.KOD = U26.BOSTADTYP AND Q2.KODTYP = 'LNBOSTAD'
LEFT JOIN LD0V0Q01 AS Q3 ON Q3.KOD = V0A.FODLAND AND Q3.KODTYP = 'LAND'
LEFT JOIN LD0V0Q01 AS Q4 ON Q4.KOD = V0A.OMRADE AND Q4.KODTYP = 'OMRÅDE'
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0RESOURCEAS')
BEGIN
DROP VIEW LD0VAA0RESOURCEAS
END
;
CREATE VIEW LD0VAA0RESOURCEAS
AS
SELECT	  
		  L01.*
		, Q.KLARTEXT RESURSTYP_T
		, L.RESURSBEN
		, L.RESURSKAT
		, L.RESURSKAT_T
		, L.ANSTALLNINGSNR
		, Q1.KLARTEXT PLACFTYP_T 
	FROM LD0V0L01 L01  
	LEFT JOIN LD0V0Q01 Q ON Q.KODTYP = 'RESURSTYP' AND Q.KOD = L01.RESURSTYP  
	LEFT JOIN LD0VAA0RESOURCE L ON L.RESURSNR = L01.RESURSNR
	LEFT JOIN LD0V0Q01 Q1 ON Q1.KODTYP = 'PLFORM'  AND Q1.KOD = L01.SCBSTAT
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0SearchSFI')
BEGIN
DROP VIEW LD0VAA0SearchSFI 
END
;
CREATE VIEW LD0VAA0SearchSFI AS 
SELECT V0A.PNR,
V0A.NAMN,
V0A.ADRESS,
V0A.COADR,
V0A.POSTNR,
V0A.ORT,
U21.ANALFABET,
U21.SEMIANALFA,
U21.UPDDAT as U21UPDDAT,
U21.UPDSIGN as U21UPDSIGN,
U22.SFI,
u22.SFINR,
U22.FROMDAT,
U22.TOMDAT,LNKURSPLAN.KLARTEXT 'KURSPLAN',
LNRESULSFI.KLARTEXT 'RESULTAT',
KOMU.KLARTEXT 'KOMMUN',
LNKOEN.KLARTEXT 'KON',
U22.UPDDAT as SFIUPDDAT,
U22.UPDSIGN as SFIUPDSIGN,
OMR.NAMN as Omrade
,U10.INLEDDAT,U10.AVSLUTDAT
FROM LD0V0U22 U22
JOIN LD0V0U01 U01 ON U22.KARTLAGGNR = U01.KARTLAGGNR
JOIN LD0V0A V0A ON V0A.PNR = U01.PERSONNR
LEFT JOIN LD0VLU10 U10 ON U10.PERSONNR = U01.PERSONNR
left jOIN LD0VAA0NKO NKO on NKO.FOMDAT <= (select convert(varchar, getdate(), 112)) AND NKO.TOMDAT >= (select convert(varchar, getdate(), 112)) AND V0A.NYCKKOD=NKO.NYCKKOD
left JOIN LD0VAA0OMR OMR on NKO.OMRID =OMR.OMRID and NKO.OMRENHNR=OMR.OMRENHNR
LEFT JOIN LD0VLU21 U21 ON U22.KARTLAGGNR = U21.KARTLAGGNR
LEFT JOIN LD0V0Q01 LAND ON LAND.KODTYP = 'LAND' AND LAND.KOD = V0A.FODLAND
LEFT JOIN LD0V0Q01 LNRESULSFI ON LNRESULSFI.KODTYP = 'LNRESULSFI' AND LNRESULSFI.KOD = U22.RESULTAT
LEFT JOIN LD0V0Q01 LNKOEN ON LNKOEN.KODTYP = 'LNKOEN' AND LNKOEN.KOD = V0A.KON
LEFT JOIN LD0V0Q01 KOMU ON KOMU.KODTYP = 'KOMU' AND KOMU.KOD = U22.KOMMUN
LEFT JOIN LD0V0Q01 LNKURSPLAN ON LNKURSPLAN.KODTYP = 'LNKURSPLAN' AND LNKURSPLAN.KOD = U22.KURSPLAN
;
