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

CREATE VIEW LD0VAA0SearchNotes AS 
SELECT T41.*,
CASE
                             WHEN T41.AGARTYP = 68 THEN UPPDRAGIFO.ORGID
                             WHEN T41.AGARTYP = 69 THEN UPPDRAGLM.MOTORGID
                             WHEN T41.AGARTYP = 70 THEN INSKRIVNING.ORGID
                             ELSE 0
END AS ORGID,
CASE
                             WHEN T41.AGARTYP = 68 THEN UPPDRAGIFO.ORGENHNR
                             WHEN T41.AGARTYP = 69 THEN UPPDRAGLM.MOTORGENHNR
                             WHEN T41.AGARTYP = 70 THEN INSKRIVNING.ORGENHNR
                             ELSE ''
END AS ORGENHNR,
CASE
                             WHEN T41.AGARTYP = 68 THEN UPPDRAGIFO.ORGNAMN_T
                             WHEN T41.AGARTYP = 69 THEN UPPDRAGLM.MOTORGNAMN_T
                             WHEN T41.AGARTYP = 70 THEN INSKRIVNING.ORGENH_T
                             ELSE ''
END AS ORGNAMN_T,
CASE
                             WHEN T41.AGARTYP = 68 THEN 'Uppdrag IFO'
                             WHEN T41.AGARTYP = 69 THEN 'Uppdrag AM'
                             WHEN T41.AGARTYP = 70 THEN 'Inskrivning'
                             ELSE ''
END AS KOMPONENT_T,

A.NAMN as PERSONNAMN,
A.KUNDNUMMER as KUNDNUMMER,
Q01.KLARTEXT as ANTECKNTYP_T
FROM LD0V1T41 T41
JOIN LD0V0A A ON A.PNR = T41.PERSONNR
JOIN LD0V0Q01 Q01 ON Q01.KOD = T41.ANTECKNTYP AND Q01.KODTYP = 'ANTECKNTYP'
LEFT JOIN LD0VAA0ASSIGNMENTIFO AS UPPDRAGIFO ON T41.AGARNR = UPPDRAGIFO.UPPDRAGIFONR AND T41.AGARTYP = 68
LEFT JOIN LD0VAA0LMASSIGNMENT AS UPPDRAGLM ON T41.AGARNR = UPPDRAGLM.LNUPPDRAGNR AND T41.AGARTYP = 69
LEFT JOIN LD0VAA0LMENROLLMENT AS INSKRIVNING ON T41.AGARNR = INSKRIVNING.INSKRIVNINGSNR AND T41.AGARTYP = 70
WHERE ANTECKNTYP > 0
;