DROP VIEW LD0VAA0SEARCHJOURNALNOTE
;
CREATE VIEW LD0VAA0SEARCHJOURNALNOTE 
AS 
SELECT        
T41.DOKUMENTNR,
T41.AGARNR,
T41.AGARTYP,
T41.DDATUM,
T41.DRUBRIK,
T41.DOKID,
T41.UPDDAT,
T41.UPDSIGN,
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,
Q01_1.KLARTEXT ANTTYP_T,
C.AEVERKNR INVESTIGATIONID,
0 EXECUTIONID,
0 SERVICEID,
C.AKTPNR INVESTIGATIONPNR,
' ' EXECUTIONPNR, 
' ' SERVICEPNR,
A.PNR,
A.NAMN AS PERSONNAME
FROM LD0V0T41 T41
LEFT OUTER JOIN LD0V0Q01 Q01_1
ON T41.ANTDOKTYP = Q01_1.KOD 
AND Q01_1.KODTYP = 'ANTTYP'
LEFT JOIN LD0VLC C
ON T41.AGARNR = C.AEVERKNR AND C.TMARK = 0 AND (T41.AGARTYP = 42 OR T41.AGARTYP = 51)
JOIN LD0V0A A
ON A.PNR=C.AKTPNR
WHERE T41.DELETED = 0
UNION
SELECT        
T41.DOKUMENTNR,
T41.AGARNR,
T41.AGARTYP,
T41.DDATUM,
T41.DRUBRIK,
T41.DOKID,
T41.UPDDAT,
T41.UPDSIGN,
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,
Q01_1.KLARTEXT ANTTYP_T,
0 INVESTIGATIONID,
C.AEVERKNR EXECUTIONID,
0 SERVICEID,
' ' INVESTIGATIONPNR,
C.AKTPNR EXECUTIONPNR, 
' ' SERVICEPNR,
A.PNR,
A.NAMN AS PERSONNAME
FROM LD0V0T41 T41
LEFT OUTER JOIN LD0V0Q01 Q01_1
ON T41.ANTDOKTYP = Q01_1.KOD 
AND Q01_1.KODTYP = 'ANTTYP'
LEFT JOIN LD0VLC C
ON T41.AGARNR = C.AEVERKNR AND C.TMARK = 1 AND (T41.AGARTYP = 42 OR T41.AGARTYP = 52)
JOIN LD0V0A A
ON A.PNR=C.AKTPNR
WHERE T41.DELETED = 0
UNION
SELECT        
T41.DOKUMENTNR,
T41.AGARNR,
T41.AGARTYP,
T41.DDATUM,
T41.DRUBRIK,
T41.DOKID,
T41.UPDDAT,
T41.UPDSIGN,
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,
Q01_1.KLARTEXT ANTTYP_T,
0 INVESTIGATIONID,
0 EXECUTIONID,
C20.INSNR SERVICEID,
' ' INVESTIGATIONPNR,
' ' EXECUTIONPNR, 
C20.AKTPNR SERVICEPNR,
A.PNR,
A.NAMN AS PERSONNAME
FROM LD0V0T41 T41
LEFT OUTER JOIN LD0V0Q01 Q01_1
ON T41.ANTDOKTYP = Q01_1.KOD 
AND Q01_1.KODTYP = 'ANTTYP'
LEFT JOIN LD0VLC20 C20
ON T41.AGARNR = C20.INSNR AND (T41.AGARTYP = 41 OR T41.AGARTYP = 53)
JOIN LD0V0A A
ON A.PNR=C20.AKTPNR
WHERE T41.DELETED = 0
;