IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1SEARCHPROCESS')
BEGIN
DROP VIEW LD0V1SEARCHPROCESS
END
;
CREATE VIEW LD0V1SEARCHPROCESS
AS 
SELECT	DISTINCT 
PS.ID,
PS.NAME,
PR.PNR AS 'PERSONID',
PR.NAMN AS 'PERSONNAME',
K.VALUE AS 'SERVICECATEGORY',
K1.VALUE AS 'PROCESSTYPE',
PS.SERVICECATEGORYID,
PS.PROCESSTYPEID,
PMD.ID AS 'PROCESSMETADATAID',
PMD.OBJECTTYPE,
PMD.OBJECTID,
PMD.PERSONKEY,
PMD.STARTDATE,
PMD.ENDDATE,
PMD.CASEWORKERID,
PMD.COCASEWORKERID,
PMD.ORGANIZATIONID,
PMD.CASEWORKERORGUNITID,
PMD.COCASEWORKERORGUNITID,
H1.NAMN AS 'CASEWORKERNAME',
H2.NAMN AS 'COCASEWORKERNAME',
J1.NAMN AS 'CASEWORKERORGANIZATIONNAME',
J2.NAMN AS 'COCASEWORKERORGANIZATIONNAME'
FROM
LD0V0PROCESS PS
INNER JOIN LD0V0PROCESSMETADATA 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 K 
ON PS.SERVICECATEGORYID = K.SERVCATID 
LEFT JOIN LD0V0KANTA K1 
ON PS.PROCESSTYPEID = K1.SERVCATID
WHERE PS.SERVICECATEGORYID <> 0
;