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

CREATE VIEW LD0VAA0LMSEARCHDOCUMENTS
AS 
SELECT 
T41.DOKUMENTNR,
T41.AGARNR,
T41.AGARTYP,
T41.DDATUM,
T41.DRUBRIK,
T41.DOKID,
T41.UPDDAT,
T41.UPDSIGN,
CASE 
WHEN T41.AGARTYP = 30 OR T41.AGARTYP = 32 OR T41.AGARTYP = 64 THEN ENROLLMENT.ORGID 
WHEN T41.AGARTYP = 48 THEN LMASSIGNMENT.MOTORGID 
WHEN T41.AGARTYP = 71 THEN PARTICIPANT.ORGID
ELSE '0' END AS ORGID,
CASE
WHEN T41.AGARTYP = 30 OR T41.AGARTYP = 32 OR T41.AGARTYP = 64 THEN ENROLLMENT.ORGENHNR 
WHEN T41.AGARTYP = 48 THEN LMASSIGNMENT.MOTORGENHNR
WHEN T41.AGARTYP = 71 THEN PARTICIPANT.ORGENHNR
ELSE '0' END AS ORGENHNR,
T41.UTLINSNR,
T41.DOCTYPE,
T41.ACTIVE,
T41.DOKTYP,
T41.PERSONNR,
T41.DELETED,
T41.LNDOKTYP,
T41.HUVUDOBJTYP,
T41.HUVUDOBJNR,
T41.VERSIONNR,
T41.ANSVHAND,
T41.ANSVTITEL,
T41.ANTDOKTYP,
T41.EBREV_SENDDATE,
T41.EBREV_SENDTIME,
T41.EBREV_SENDSIGN,
T41.EBREV_SENDACK,
T41.HANDELSEDATE,
T41.LOCKED,
T41.LOCKDATE,
T41.LOCKSIGN,
T41.SKAPADAVGALLRING,
T41.PUBLISH_DATE,
T41.PUBLISH_MOBIL,
T41.PUBLISH_EMAIL,
T41.LIFECARE_CREATED,
T41.TEMPLATE,
T41.RESPOND_DATE,
T41.PUBLISH_HANDL,
T41.PUBLISH_ORGID,
T41.PUBLISH_ORGENHNR,
T41.ACTNOTETYP,
T41.RESPOND_WANTED,
T41.PUBLISH_STATUS,
U35.AKTIVITETNR,
Q01_1.KLARTEXT AS ANTTYP_T,
Q01_2.KLARTEXT AS DOKTYP_T,
Q01_2.XNUM4 AS BLANKETT,P.NAMN, 
Q01_3.KLARTEXT AS LNDOKTYP_T,
P.KUNDNUMMER
FROM LD0V1T41 AS T41 
LEFT JOIN LD0V0A AS P 
ON T41.PERSONNR = P.PNR 
LEFT JOIN LD0V0U35 U35 ON U35.AKTIVITETNR = T41.AGARNR AND T41.AGARTYP = 71
LEFT JOIN LD0V0U10 AS ENROLLMENT ON T41.HUVUDOBJNR = ENROLLMENT.INSKRIVNINGSNR AND T41.HUVUDOBJTYP = 7405
LEFT JOIN LD0V0U90 AS LMASSIGNMENT ON T41.AGARNR = LMASSIGNMENT.LNUPPDRAGNR AND T41.AGARTYP = 48
LEFT JOIN LD0V0U10 AS PARTICIPANT ON U35.INSKRIVNINGSNR = PARTICIPANT.INSKRIVNINGSNR 
LEFT OUTER JOIN LD0V0Q01 AS Q01_1 ON T41.ANTDOKTYP = Q01_1.KOD AND Q01_1.KODTYP = 'ANTTYP' 
LEFT OUTER JOIN LD0V0Q01 AS Q01_2 ON T41.DOKTYP = Q01_2.KOD AND Q01_2.KODTYP = 'TYPDOK' 
LEFT OUTER JOIN LD0V0Q01 AS Q01_3 ON T41.DOKTYP = Q01_3.KOD AND Q01_3.KODTYP = 'LNDOKTYP' 
WHERE (T41.DOKTYP > 0 OR T41.LNDOKTYP > 0) AND (T41.DELETED = 0) AND (T41.AGARTYP IN (30,32, 33,62,66,48, 64,71))
;
 


