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