DROP VIEW LD0V0KANTA
;

CREATE VIEW LD0V0KANTA AS SELECT  D.VALUE AS NIVA, 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(C.ID, 0) AS SERVCATID,  B.CS_ID AS ORG_OID, IsNull(F.VALUE, '') as WELFAREMAINUNIT, IsNull(G.VALUE, '') as WELFARESERVICEUNIT from 
LD0P0TERMSYSTEM A JOIN LD0P0TERMITEMENTRY B on A.ID = B.TERMSYSTEM_ID
left outer JOIN  LD0P0TERMITEMENTRYATTR C ON B.ID = C.TERMITEMENTRY_ID AND c.TYPE = 'shortname' 
left outer JOIN LD0P0TERMITEMENTRYATTR E ON B.ID = E.TERMITEMENTRY_ID AND E.TYPE = 'longname'
left outer JOIN LD0P0TERMITEMENTRYATTR F ON B.ID = F.TERMITEMENTRY_ID AND F.TYPE = 'Sos.toimintayksikkö'
left outer JOIN LD0P0TERMITEMENTRYATTR G ON B.ID = G.TERMITEMENTRY_ID AND G.TYPE = 'Sos.palveluyksikkö'
JOIN LD0P0TERMITEMENTRYATTR D ON B.ID = D.TERMITEMENTRY_ID and D.TYPE = 'hierarchylevel' and (c.VALUE  is not null or  e.VALUE is not null)
;
