REM Updated By: Hannu Saukkosaari
REM Summary: Returns a list of all investigations for ongoing work.
REM Only 1 actualization will be selected for each investigation (K).
REM The latest journal entry will be added to each row (J).

DROP VIEW LD0V0CONGOINGWORK
;
CREATE VIEW LD0V0CONGOINGWORK
AS 
SELECT
    C.AEVERKNR, C.STARTDAT, C.STOPDAT, Q.KLARTEXT AS INVESTIGATIONTYPETEXT, A.KUNDNUMMER AS PERSONKEY,
    C.AKTPNR, A.NAMN AS PERSONNAME, O.NAMN AS ORGANIZATION, C.HAND, C.MEDHAND, H.NAMN AS CASEWORKERNAME,
    K.AKTUALNR, J.UPDDAT, J.UPDSIGN, J.HTEXT1, C.TMARK

FROM LD0V0C C
    INNER JOIN LD0V0Q01 Q ON Q.KODTYP = 'UTREDTYP' AND Q.KOD = C.AEVERKTYP 
    INNER JOIN LD0V0J01 O ON C.ORGID = O.ORGID AND C.ORGENHNR = O.ORGENHNR
    INNER JOIN LD0V0I H ON C.HAND = H.HAND 
    INNER JOIN LD0V0B B ON C.AKTNR = B.AKTNR
    INNER JOIN LD0V0Q01 Q1 ON Q1.KODTYP = 'ARKIV' AND Q1.KOD = B.ARKIVNR 
    INNER JOIN LD0V0Q01 T ON T.KODTYP = 'AKTTYP' AND T.KOD = B.TYPNR
    LEFT OUTER JOIN LD0V0A A ON C.AKTPNR = A.PNR
    OUTER APPLY (
        SELECT TOP 1 AKTUALNR 
        FROM LD0V0CAKTUALSERING
        WHERE AEVERKNR = C.AEVERKNR 
		ORDER BY ADATUM DESC, AKTUALNR DESC
    ) K
    OUTER APPLY (
        SELECT TOP 1 UPDDAT, UPDSIGN, HTEXT1
        FROM LD0V0C11
        WHERE AEVERKNR = C.AEVERKNR 
        ORDER BY AEVERKNR, INSNR, HTYP, HAGNR
    ) J
;