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

CREATE OR ALTER VIEW LD0VAAYMOUGoal AS 
SELECT
GOAL.*,
GOALTYPE.KLARTEXT AS GOALTYPE_T,
PROF.NAMN AS RESPONSIBLE_T,
CASE 
WHEN GOAL.CONCERNS = 7083 THEN 'Insats'
WHEN GOAL.CONCERNS = 7012 THEN 'Beslut'
WHEN GOAL.CONCERNS = 28471 THEN 'Uppdrag Ifo'
ELSE '' END AS CONCERN_T,
CASE 
WHEN INSATSTYP.KLARTEXT IS NOT NULL THEN CONCAT(INSATSTYP.KLARTEXT,' ',CONVERT(DATE, INSATS.FOMDAT))
WHEN BESLUTTYP.KLARTEXT IS NOT NULL THEN CONCAT(BESLUTTYP.KLARTEXT,' ',CASE	WHEN BESLUT.FOMDAT = '00000000' THEN '' ELSE CONCAT('',CONVERT(DATE, BESLUT.FOMDAT)) END)
WHEN UPPDRAGTYP.KLARTEXT IS NOT NULL THEN CONCAT(UPPDRAGTYP.KLARTEXT, ' ', CASE WHEN UPPDRAG.STARTDAT = '00000000' THEN '' ELSE CONCAT('',CONVERT(DATE, UPPDRAG.STARTDAT)) END)
ELSE '' END AS CONCERNNAME_T,
CASE WHEN FOLLOWUP.CREATEDATE IS NOT NULL THEN FOLLOWUP.CREATEDATE ELSE '00000000' END AS LATESTFOLLOWUP,
CASE 
WHEN INSATSTYP.XNUM40 IS NOT NULL THEN INSATSTYP.XNUM40
WHEN UTREDTYP.XNUM40 IS NOT NULL THEN UTREDTYP.XNUM40
ELSE 0 END AS WORKFLOW,
PERSON.NAMN AS PERSONNAME,
PROF2.NAMN AS GOALRESP_T,
CASE WHEN ASSESSMENT.GRADE IS NOT NULL THEN ASSESSMENT.GRADE ELSE 0 END AS LATESTASSESSMENT,
CASE WHEN FOLLOWUP.NEXTDATE IS NOT NULL THEN (CASE WHEN FOLLOWUP.FINAL = 1 THEN '00000000' ELSE FOLLOWUP.NEXTDATE END) ELSE GOAL.NEXTDATE END AS NEXTFOLLOWUP
FROM LD0V0MOUGoal GOAL
OUTER APPLY (
    SELECT TOP 1 * FROM LD0P0MOUAssessment ASSESSMENT WHERE ASSESSMENT.FOLLOWUPID IN (SELECT FOLLOWUPID FROM LD0P0MOUFollowUp WHERE GOALID = GOAL.GOALID AND SUBGOALID = 0)
    AND ASSESSMENT.GRADE > 0 ORDER BY ASSESSMENT.ASSESSMENTID DESC
) ASSESSMENT
LEFT OUTER JOIN LD0P0Q01 GOALTYPE ON GOALTYPE.KODTYP = 'MOUGOAL' AND GOALTYPE.KOD = GOAL.GOAL
LEFT OUTER JOIN LD0P0I PROF ON PROF.HAND = GOAL.RESPONSIBLE
LEFT OUTER JOIN LD0VYC20 INSATS ON GOAL.CONCERNS = 7083 AND INSATS.INSNR = GOAL.CONCERNID
LEFT OUTER JOIN LD0P0Q01 INSATSTYP ON INSATSTYP.KODTYP = 'INSATS' AND INSATSTYP.KOD = INSATS.INSTYP
LEFT OUTER JOIN LD0P0E BESLUT ON GOAL.CONCERNS = 7012 AND  BESLUT.BESLUTNR = GOAL.CONCERNID
LEFT OUTER JOIN LD0P0Q01 BESLUTTYP ON BESLUTTYP.KODTYP = 'BESLUT' AND BESLUTTYP.KOD = BESLUT.BESLUT
LEFT OUTER JOIN LD0VYC UTREDNING ON BESLUT.AEVERKNR > 0 AND UTREDNING.TMARK = 0 AND UTREDNING.AEVERKNR = BESLUT.AEVERKNR
LEFT OUTER JOIN LD0P0Q01 UTREDTYP ON UTREDTYP.KODTYP = 'UTREDTYP' AND UTREDNING.AEVERKTYP > 0 AND UTREDTYP.KOD = UTREDNING.AEVERKTYP
LEFT OUTER JOIN LD0V0A PERSON ON PERSON.PNR = GOAL.PERSONID
LEFT OUTER JOIN LD0P0I PROF2 ON PROF2.HAND = GOAL.GOALRESP
LEFT OUTER JOIN LD0P0N900 UPPDRAG ON GOAL.CONCERNS = 28471 AND UPPDRAG.UPPDRAGIFONR = GOAL.CONCERNID
LEFT OUTER JOIN LD0P0Q01 UPPDRAGTYP ON UPPDRAGTYP.KODTYP = 'UIFOEJBIST' AND UPPDRAGTYP.KOD = UPPDRAG.EJBISTTYP
LEFT OUTER JOIN LD0P0MOUFollowUp FOLLOWUP ON ASSESSMENT.FOLLOWUPID IS NOT NULL AND FOLLOWUP.FOLLOWUPID = ASSESSMENT.FOLLOWUPID
GO

GRANT ALL ON LD0VAAYMOUGoal TO LD0ROANV
GO