DROP VIEW LD0V0CSCANNEDDOCUMENTS
;

REM--SUMMARY: GETS ALL THE SCANNED DOCUMENTS	


CREATE VIEW LD0V0CSCANNEDDOCUMENTS 

AS 

SELECT 
     DATE,DOCUMENTID,TITLE,DOCUMENTNAME,PERSONID,SERVICECATEGORY,DOCUMENTTYPE,ACTUALIZATIONID,
	 SERVICEID,INVESTIGATIONID,ORGANIZATIONNAME,ORGENHNR AS ORGANIZATIONID,UPDDAT,UPDSIGN

FROM (

SELECT  
    T60.DDATUM DATE,T60.DOKUMENTNR DOCUMENTID,T60.DRUBRIK TITLE,T60.DOKNAMN DOCUMENTNAME,C20.AKTPNR PERSONID,
    Q1.KLARTEXT SERVICECATEGORY,Q2.KLARTEXT DOCUMENTTYPE,T60.AGARNR ACTUALIZATIONID,T60.INSNR SERVICEID,
    T60.AEVERKNR INVESTIGATIONID,ORG.NAMN AS ORGANIZATIONNAME,ORG.ORGENHNR,T60.UPDDAT,T60.UPDSIGN 

FROM LD0V0T60 T60 

    INNER JOIN LD0V0C20 C20 ON T60.INSNR = C20.INSNR AND C20.INSNR <> 0 AND C20.VERKNR <> 0 
    INNER JOIN LD0V0C INSATS ON INSATS.AEVERKNR = C20.VERKNR INNER JOIN LD0V0B B1 ON INSATS.AKTNR = B1.AKTNR 
    LEFT JOIN LD0V0Q01 Q1 ON B1.TYPNR = Q1.KOD AND Q1.KODTYP = 'AKTTYP' 
    LEFT JOIN LD0V0Q01 Q2 ON Q2.KOD = T60.SCATYP AND Q2.KODTYP = 'DOKTYP' 
    LEFT JOIN LD0V0J01 ORG ON C20.ORGENHNR = ORG.ORGENHNR 


UNION ALL

SELECT 
   T60.DDATUM DATE,T60.DOKUMENTNR DOCUMENTID,T60.DRUBRIK TITLE,T60.DOKNAMN DOCUMENTNAME,C20.AKTPNR PERSONID,
   Q1.KLARTEXT SERVICECATEGORY,Q2.KLARTEXT DOCUMENTTYPE,T60.AGARNR ACTUALIZATIONID,T60.INSNR SERVICEID,
   T60.AEVERKNR INVESTIGATIONID,ORG.NAMN AS ORGANIZATIONNAME,ORG.ORGENHNR,T60.UPDDAT,T60.UPDSIGN 

FROM LD0V0T60 T60 

   INNER JOIN LD0V0C20 C20 ON T60.INSNR = C20.INSNR AND C20.INSNR <> 0 AND C20.ARENDENR <> 0 
   INNER JOIN LD0V0C INSATS ON INSATS.AEVERKNR = C20.ARENDENR 
   INNER JOIN LD0V0B B1 ON INSATS.AKTNR = B1.AKTNR 
   LEFT JOIN LD0V0Q01 Q1 ON B1.TYPNR = Q1.KOD AND Q1.KODTYP = 'AKTTYP' 
   LEFT JOIN LD0V0Q01 Q2 ON Q2.KOD = T60.SCATYP AND Q2.KODTYP = 'DOKTYP' 
   LEFT JOIN LD0V0J01 ORG ON C20.ORGENHNR = ORG.ORGENHNR  

UNION ALL 

SELECT 
   T60.DDATUM DATE,T60.DOKUMENTNR DOCUMENTID,T60.DRUBRIK TITLE,T60.DOKNAMN DOCUMENTNAME,C.AKTPNR PERSONID,
   Q2.KLARTEXT SERVICECATEGORY,Q3.KLARTEXT DOCUMENTTYPE,T60.AGARNR ACTUALIZATIONID,T60.INSNR SERVICEID,
   T60.AEVERKNR INVESTIGATIONID,ORG.NAMN AS ORGANIZATIONNAME,ORG.ORGENHNR,T60.UPDDAT,T60.UPDSIGN 

FROM LD0V0T60 T60 

   INNER JOIN LD0V0C C ON T60.AEVERKNR = C.AEVERKNR AND T60.INSNR = 0 
   LEFT JOIN LD0V0Q01 Q1 ON C.AEVERKTYP = Q1.KOD AND Q1.KODTYP = 'UTREDTYP' 
   LEFT JOIN LD0V0Q01 Q2 ON Q1.XNUM1 = Q2.KOD AND Q2.KODTYP = 'AKTTYP' 
   LEFT JOIN LD0V0Q01 Q3 ON Q3.KOD = T60.SCATYP AND Q3.KODTYP = 'DOKTYP' 
   LEFT JOIN LD0V0J01 ORG ON C.ORGENHNR = ORG.ORGENHNR 

UNION ALL 

SELECT 
   T60.DDATUM DATE,T60.DOKUMENTNR DOCUMENTID,T60.DRUBRIK TITLE,T60.DOKNAMN DOCUMENTNAME,H.PNR PERSONID,
   Q2.KLARTEXT SERVICECATEGORY,Q3.KLARTEXT DOCUMENTTYPE,T60.AGARNR ACTUALIZATIONID,T60.INSNR SERVICEID,
   T60.AEVERKNR INVESTIGATIONID,ORG.NAMN AS ORGANIZATIONNAME,ORG.ORGENHNR,T60.UPDDAT,T60.UPDSIGN 

FROM LD0V0T60 T60 

   INNER JOIN LD0V0H H ON H.AKTUALNR = T60.AGARNR AND T60.INSNR = 0 AND T60.AEVERKNR = 0 
   INNER JOIN LD0V0C C ON C.AEVERKNR = H.AEVERKNR AND H.AEVERKNR <> 0 
   LEFT JOIN LD0V0Q01 Q1 ON Q1.KOD = C.AEVERKTYP AND Q1.KODTYP = 'UTREDTYP' 
   LEFT JOIN LD0V0Q01 Q2 ON Q1.XNUM1 = Q2.KOD AND Q2.KODTYP = 'AKTTYP' 
   LEFT JOIN LD0V0Q01 Q3 ON Q3.KOD = T60.SCATYP AND Q3.KODTYP = 'DOKTYP' 
   LEFT JOIN LD0V0J01 ORG ON H.ORGENHNR = ORG.ORGENHNR 

UNION ALL 

SELECT 
    T60.DDATUM DATE,T60.DOKUMENTNR DOCUMENTID,T60.DRUBRIK TITLE,T60.DOKNAMN DOCUMENTNAME,H.PNR PERSONID,
    Q1.KLARTEXT SERVICECATEGORY,Q2.KLARTEXT DOCUMENTTYPE,T60.AGARNR ACTUALIZATIONID,T60.INSNR SERVICEID,
    T60.AEVERKNR INVESTIGATIONID,ORG.NAMN AS ORGANIZATIONNAME,ORG.ORGENHNR,T60.UPDDAT,T60.UPDSIGN 

FROM LD0V0T60 T60 

   INNER JOIN LD0V0H H ON H.AKTUALNR = T60.AGARNR AND T60.INSNR = 0 AND T60.AEVERKNR = 0 
   INNER JOIN LD0V0C20 C20 ON C20.INSNR = H.INSNR AND H.INSNR <> 0 
   INNER JOIN LD0V0C C ON C.AEVERKNR = C20.VERKNR 
   INNER JOIN LD0V0B B1 ON C.AKTNR = B1.AKTNR 
   LEFT JOIN LD0V0Q01 Q1 ON B1.TYPNR = Q1.KOD AND Q1.KODTYP = 'AKTTYP' 
   LEFT JOIN LD0V0Q01 Q2 ON Q2.KOD = T60.SCATYP AND Q2.KODTYP = 'DOKTYP'
   LEFT JOIN LD0V0J01 ORG ON H.ORGENHNR = ORG.ORGENHNR 

UNION ALL 

SELECT
    T60.DDATUM DATE,T60.DOKUMENTNR DOCUMENTID,T60.DRUBRIK TITLE,T60.DOKNAMN DOCUMENTNAME,H.PNR PERSONID,
	Q1.KLARTEXT SERVICECATEGORY,Q2.KLARTEXT DOCUMENTTYPE,T60.AGARNR ACTUALIZATIONID,T60.INSNR SERVICEID,
	T60.AEVERKNR INVESTIGATIONID,ORG.NAMN AS ORGANIZATIONNAME,ORG.ORGENHNR,T60.UPDDAT,T60.UPDSIGN

FROM LD0V0T60 T60 

    INNER JOIN LD0V0H H ON H.AKTUALNR = T60.AGARNR AND T60.INSNR = 0 AND T60.AEVERKNR = 0 
    INNER JOIN LD0V0C20 C20 ON C20.INSNR = H.INSNR AND H.INSNR <> 0 
    INNER JOIN LD0V0C C ON C.AEVERKNR = C20.ARENDENR 
    INNER JOIN LD0V0B B1 ON C.AKTNR = B1.AKTNR 
    LEFT JOIN LD0V0Q01 Q1 ON B1.TYPNR = Q1.KOD AND Q1.KODTYP = 'AKTTYP' 
    LEFT JOIN LD0V0Q01 Q2 ON Q2.KOD = T60.SCATYP AND Q2.KODTYP = 'DOKTYP' 
    LEFT JOIN LD0V0J01 ORG ON H.ORGENHNR = ORG.ORGENHNR 

UNION ALL

SELECT 
    T60.DDATUM DATE,T60.DOKUMENTNR DOCUMENTID,T60.DRUBRIK TITLE,T60.DOKNAMN DOCUMENTNAME,T60.PNR PERSONID,
	'' SERVICECATEGORY,Q1.KLARTEXT DOCUMENTTYPE,T60.AGARNR ACTUALIZATIONID,T60.INSNR SERVICEID,
	T60.AEVERKNR INVESTIGATIONID,ORG.NAMN AS ORGANIZATIONNAME,ORG.ORGENHNR,T60.UPDDAT,T60.UPDSIGN 

FROM LD0V0T60 T60 

     LEFT JOIN LD0V0Q01 Q1 ON Q1.KOD = T60.SCATYP AND Q1.KODTYP = 'DOKTYP'
     LEFT JOIN LD0V0J01 ORG ON T60.ORGENHNR = ORG.ORGENHNR WHERE T60.ORGID <> 0 AND T60.AEVERKNR = 0 AND T60.INSNR = 0 AND T60.AGARNR = 0 ) AS DOCUMENTS 
;