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

SELECT q.ID AS Id,
    w.PNR AS PersonId, a.NAMN AS PersonName, w.DOCOID AS DocumentOid, q.DOCUMENTGROUP AS DocumentType,
    w.CREATEDAT AS CreateDate, w.ARCHDAT AS ArchiveDate, w.VERSION AS DocumentVersion,
    w.ARCHSTATE AS ArchiveState, w.USERNAME AS UserName, i.NAMN AS Caseworker,
    q.ARCHIVECOUNT AS ArchiveCount, q.ERRORMESSAGE AS ErrorMessage, q.ERRORLEVEL AS ErrorLevel
FROM LD0P0DOCUMENTQUEUE q
    INNER JOIN LD0P0SOCIALWELFARECUSTSHIP w ON q.WELFARECUSTSHIPID = w.ID
    LEFT OUTER JOIN LD0P0I i ON w.USERNAME = i.HAND
    LEFT OUTER JOIN LD0P0A a ON w.PNR = a.PNR
WHERE q.ARCHIVECOUNT > 0

UNION ALL

SELECT q.ID AS Id,
    w.PNR AS PersonId, a.NAMN AS PersonName, c.DOCOID AS DocumentOid, q.DOCUMENTGROUP AS DocumentType,
    c.CREATEDAT AS CreateDate, c.ARCHDAT AS ArchiveDate, c.VERSION AS DocumentVersion,
    c.ARCHSTATE AS ArchiveState, w.USERNAME AS UserName, i.NAMN AS Caseworker,
    q.ARCHIVECOUNT AS ArchiveCount, q.ERRORMESSAGE AS ErrorMessage, q.ERRORLEVEL AS ErrorLevel
FROM LD0P0DOCUMENTQUEUE q
    INNER JOIN LD0P0CASE c ON q.CASEID = c.ID
    INNER JOIN LD0P0SERVICECATEGORYCUSTSHIP sc ON c.SERVCATCUSTID=sc.ID
    INNER JOIN LD0P0SOCIALWELFARECUSTSHIP w ON sc.WELFAREID=w.ID
    LEFT OUTER JOIN LD0P0I i ON w.USERNAME = i.HAND
    LEFT OUTER JOIN LD0P0A a ON w.PNR = a.PNR
WHERE q.ARCHIVECOUNT > 0

UNION ALL

SELECT q.ID AS Id,
    w.PNR AS PersonId, a.NAMN AS PersonName, cust.DOCOID + '.' + RTRIM(LTRIM(STR(cust.DOCVERSION))) AS DocumentOid,
    q.DOCUMENTGROUP AS DocumentType,
    cust.CREATEDAT AS CreateDate, cust.ARCHDAT AS ArchiveDate, cust.DOCVERSION AS DocumentVersion,
    cust.ARCHSTATE AS ArchiveState, cust.USERNAME AS UserName, i.NAMN AS Caseworker,
    q.ARCHIVECOUNT AS ArchiveCount, q.ERRORMESSAGE AS ErrorMessage, q.ERRORLEVEL AS ErrorLevel
FROM LD0P0DOCUMENTQUEUE q 
    INNER JOIN LD0P0CUSTOMERDOCUMENT cust ON q.CUSTOMERDOCUMENTID = cust.ID
    INNER JOIN LD0P0CASE c ON cust.CASEID = c.ID
    INNER JOIN LD0P0SERVICECATEGORYCUSTSHIP sc ON c.SERVCATCUSTID=sc.ID
    INNER JOIN LD0P0SOCIALWELFARECUSTSHIP w ON sc.WELFAREID=w.ID
    LEFT OUTER JOIN LD0P0I i ON w.USERNAME = i.HAND
    LEFT OUTER JOIN LD0P0A a ON w.PNR = a.PNR
WHERE q.ARCHIVECOUNT > 0
;