IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1SOCIALCAREINFOOFDOCUMENTCONTENT')
BEGIN
DROP VIEW LD0V1SOCIALCAREINFOOFDOCUMENTCONTENT
END
GO
CREATE VIEW LD0V1SOCIALCAREINFOOFDOCUMENTCONTENT
AS
SELECT
       v05.FORMNR,
	   v10.OBJEKTTYP,
	   v10.OBJEKTNYKLLNR,
	   CONVERT(DECIMAL(9, 0), v10.OBJEKTNYKLNUM) AS ActualizationId,
	   v02.FRAGENR,
	   v10.OBJEKTNYKLALFA AS PersonId,
	   CASE v02.FRAGENR
       WHEN 32309
              THEN 'Ilmoituksen syy'
       WHEN 33273
              THEN 'Ilmoituksesta seuranneet toimenpiteet'
       END AS Question,
	   CASE
	   WHEN ( v11.SVAR IS NULL OR LEN(v11.SVAR) = 0 ) AND v11.LONGTEXT <> 0
              THEN
			  stuff( (SELECT v12.TEXTDEL FROM LD0P0V12 v12 WHERE v12.LONGTEXT = v11.LONGTEXT ORDER BY v12.DELNR
              FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 0, '') 
       ELSE
              v11.SVAR
       END AS Answer,
	   v09.LOPNR
FROM
       LD0P0V02 v02
       JOIN LD0P0V05 v05 ON v02.FRAGENR = v05.FRAGENR
	        AND v02.DYNDELSYS = v05.DYNDELSYS
            AND v02.VERSION = v05.VERSION
       JOIN LD0P0V10 v10 ON v02.Fragenr = v10.fragenr
            AND v02.DYNDELSYS = v10.DYNDELSYS
            AND v02.VERSION = v10.VERSION
       JOIN LD0P0V11 v11 ON v10.svarnr = v11.svarnr
            AND v02.svtyplnr = v11.svarlnr
       JOIN LD0P0V09 v09 ON v05.FORMNR = v09.FORMNR
            AND v10.OBJEKTNYKLNUM = v09.OBJEKTNYCKLNUM
            AND v10.OBJEKTNYKLLNR = v09.OBJEKTNYCKLLNR
			AND v10.VERSION = v09.VERSION
       JOIN LD0P0T41 t ON v09.LOPNR = t.HUVUDOBJNR
WHERE
       t.DELETED = 0
       AND v02.DYNDELSYS = 5
       AND v02.SVTYPLNR = 4
       AND
       (
         v05.FORMNR = 10000242
         AND v02.FRAGENR = 32309
         OR v05.FORMNR = 10000252
         AND v02.FRAGENR = 33273
       )
       AND v10.OBJEKTTYP = 7040
GO
GRANT ALL ON LD0V1SOCIALCAREINFOOFDOCUMENTCONTENT TO LD0ROANV
GO

