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

CREATE VIEW LD0VAA0SearchPdfDocuments2
AS 
select 
DOC.*,
A.KUNDNUMMER,
CASE 
WHEN DOC.AGARTYP = 7040 THEN AKTUAL.ORGID 
WHEN DOC.AGARTYP = 7017 THEN INVESTIGATION.ORGID 
WHEN DOC.AGARTYP = 7083 THEN SERV.ORGID 
WHEN DOC.AGARTYP = 7025 THEN EXECUTION.ORGID ELSE '0' END AS ORGID,
CASE 
WHEN DOC.AGARTYP = 7040 THEN AKTUAL.ORGENHNR 
WHEN DOC.AGARTYP = 7017 THEN INVESTIGATION.ORGENHNR 
WHEN DOC.AGARTYP = 7083 THEN SERV.ORGENHNR 
WHEN DOC.AGARTYP = 7025 THEN EXECUTION.ORGENHNR ELSE '0' END AS ORGENHNR,
UPPDRAGIFO.RESTYPGRP_T,
UPPDRAGIFO.EJBISTTYP_T
from LD0VAA0SEARCHPDFDOCUMENTS AS DOC
LEFT JOIN LD0V0A A on  DOC.PNR = A.PNR
LEFT JOIN LD0VAA0Aktualisering AS AKTUAL ON DOC.AGARNR = AKTUAL.AKTUALNR AND DOC.AGARTYP = 7040
LEFT JOIN LD0VAA0Investigations AS INVESTIGATION ON DOC.AGARNR = INVESTIGATION.AEVERKNR AND DOC.AGARTYP = 7017
LEFT JOIN LD0VAA0Executions AS EXECUTION ON DOC.AGARNR = EXECUTION.AEVERKNR AND DOC.AGARTYP = 7025
LEFT JOIN LD0VAA0Services AS SERV ON DOC.AGARNR = SERV.INSNR AND DOC.AGARTYP = 7083
LEFT JOIN LD0VAA0ASSIGNMENTIFO AS UPPDRAGIFO ON DOC.AGARNR = UPPDRAGIFO.UPPDRAGIFONR AND DOC.AGARTYP = 28171
GO

GRANT ALL ON LD0VAA0SearchPdfDocuments2 TO LD0ROANV
GO
