DROP VIEW LD0V0CDECISIONS
;
CREATE VIEW LD0V0CDECISIONS
AS 
SELECT
      DECISIONDATE, DECISIONSTATUS, DECISIONTYPEID, DECISIONID, DECISIONMAKERID, CASEWORKERID,  COCASEWORKERID, SERVICENUMBER, INVESTIGATIONNUMBER, STOPDAT,
	  SERVICECATEGORY, DECISIONMAKERNAME, CASEWORKERNAME, DECISIONNAME, DECISIONTYPENAME, DECISIONLEADSTOSERVICE,
	  ISNULL(PERSONID, APP.PNR) PERSONID, ISNULL(PERSONNAME, A2.NAMN) PERSONNAME,
	  DECISIONS.UPDSIGN, DECISIONS.UPDDAT, REFDECISIONID
FROM
(  
REM -- DECISIONS ASSOCIATED WITH SERVICE AND SERV.VERKNR <> 0
SELECT
      E.DATUM DECISIONDATE, E.STATUS DECISIONSTATUS, E.BESLTYP DECISIONTYPEID, E.BESLUTNR DECISIONID, E.BESFATT DECISIONMAKERID, E.HAND CASEWORKERID,  
      E.MEDHAND COCASEWORKERID, E.INSNR SERVICENUMBER, E.AEVERKNR INVESTIGATIONNUMBER, NULL STOPDAT, T.KLARTEXT SERVICECATEGORY, I.NAMN DECISIONMAKERNAME,
	  I2.NAMN CASEWORKERNAME, Q01_1.KLARTEXT DECISIONNAME, Q01_2.KLARTEXT DECISIONTYPENAME, Q01_1.XNUM2 DECISIONLEADSTOSERVICE, SERV.AKTPNR PERSONID,
	  A2.NAMN PERSONNAME, E.UPDSIGN, E.UPDDAT, E.REFBESLUTNR REFDECISIONID
	  	  	  
FROM LD0V0E E

      INNER JOIN LD0V0I I ON E.BESFATT = I.HAND
	  INNER JOIN LD0V0I I2 ON E.HAND = I2.HAND
	  LEFT JOIN LD0V0Q01 Q01_1 ON Q01_1.KODTYP = 'BESLUT' AND E.BESLUT = Q01_1.KOD
      LEFT JOIN LD0V0Q01 Q01_2 ON Q01_2.KODTYP = 'BESLUTTYP' AND E.BESLTYP = Q01_2.KOD
      
	  INNER JOIN LD0V0C20 SERV ON SERV.INSNR = E.INSNR AND SERV.INSNR <> 0 AND SERV.VERKNR <> 0
	  LEFT JOIN LD0V0C EXE ON EXE.AEVERKNR =SERV.VERKNR
	  LEFT JOIN LD0V0B B ON EXE.AKTNR = B.AKTNR
      LEFT JOIN LD0V0Q01 T ON T.KODTYP = 'AKTTYP' AND T.KOD = B.TYPNR
	  LEFT JOIN LD0V0A A2 ON SERV.AKTPNR = A2.PNR
	  
UNION
REM -- DECISIONS ASSOCIATED WITH SERVICE AND SERV.ARENDENR <> 0
SELECT
      E.DATUM DECISIONDATE, E.STATUS DECISIONSTATUS, E.BESLTYP DECISIONTYPEID, E.BESLUTNR DECISIONID, E.BESFATT DECISIONMAKERID, E.HAND CASEWORKERID,  
      E.MEDHAND COCASEWORKERID, E.INSNR SERVICENUMBER, E.AEVERKNR INVESTIGATIONNUMBER, NULL STOPDAT, T.KLARTEXT SERVICECATEGORY, I.NAMN DECISIONMAKERNAME,
	  I2.NAMN CASEWORKERNAME, Q01_1.KLARTEXT DECISIONNAME, Q01_2.KLARTEXT DECISIONTYPENAME, Q01_1.XNUM2 DECISIONLEADSTOSERVICE, SERV.AKTPNR PERSONID,
	  A2.NAMN PERSONNAME, E.UPDSIGN, E.UPDDAT, E.REFBESLUTNR REFDECISIONID
FROM LD0V0E E

      INNER JOIN LD0V0I I ON E.BESFATT = I.HAND
	  INNER JOIN LD0V0I I2 ON E.HAND = I2.HAND
	  LEFT JOIN LD0V0Q01 Q01_1 ON Q01_1.KODTYP = 'BESLUT' AND E.BESLUT = Q01_1.KOD
      LEFT JOIN LD0V0Q01 Q01_2 ON Q01_2.KODTYP = 'BESLUTTYP' AND E.BESLTYP = Q01_2.KOD
      
	 INNER JOIN LD0V0C20 SERV ON SERV.INSNR = E.INSNR AND SERV.INSNR <> 0 AND SERV.ARENDENR <> 0
	  LEFT JOIN LD0V0C EXE ON EXE.AEVERKNR =SERV.ARENDENR
	  LEFT JOIN LD0V0B B ON EXE.AKTNR = B.AKTNR
      LEFT JOIN LD0V0Q01 T ON T.KODTYP = 'AKTTYP' AND T.KOD = B.TYPNR
	  LEFT JOIN LD0V0A A2 ON SERV.AKTPNR = A2.PNR

UNION
REM -- DECISIONS ASSOCIATED WITH INVESTIGATION
SELECT
      E.DATUM DECISIONDATE, E.STATUS DECISIONSTATUS, E.BESLTYP DECISIONTYPEID, E.BESLUTNR DECISIONID, E.BESFATT DECISIONMAKERID, E.HAND CASEWORKERID,  
      E.MEDHAND COCASEWORKERID, E.INSNR SERVICENUMBER, E.AEVERKNR INVESTIGATIONNUMBER, INV.STOPDAT STOPDAT, T.KLARTEXT SERVICECATEGORY, I.NAMN DECISIONMAKERNAME,
	  I2.NAMN CASEWORKERNAME, Q01_1.KLARTEXT DECISIONNAME, Q01_2.KLARTEXT DECISIONTYPENAME, Q01_1.XNUM2 DECISIONLEADSTOSERVICE, INV.AKTPNR PERSONID,
	  A2.NAMN PERSONNAME, E.UPDSIGN, E.UPDDAT, E.REFBESLUTNR REFDECISIONID
FROM LD0V0E E
      INNER JOIN LD0V0I I ON E.BESFATT = I.HAND
	  INNER JOIN LD0V0I I2 ON E.HAND = I2.HAND
	  LEFT JOIN LD0V0Q01 Q01_1 ON Q01_1.KODTYP = 'BESLUT' AND E.BESLUT = Q01_1.KOD
      LEFT JOIN LD0V0Q01 Q01_2 ON Q01_2.KODTYP = 'BESLUTTYP' AND E.BESLTYP = Q01_2.KOD
      
	  INNER JOIN LD0V0C INV ON INV.AEVERKNR =E.AEVERKNR AND E.INSNR = 0
	  LEFT JOIN LD0V0Q01 Q2 ON Q2.KODTYP = 'UTREDTYP' AND Q2.KOD = INV.AEVERKTYP
	  LEFT JOIN LD0V0Q01 T ON T.KODTYP = 'AKTTYP' AND Q2.XNUM1 = T.KOD
	  LEFT JOIN LD0V0A A2 ON INV.AKTPNR = A2.PNR 
)
AS DECISIONS
REM -- DECISIONS ASSOCIATED WITH APPLICATION
LEFT JOIN LD0V0H APP ON APP.BESLUTNR= DECISIONS.DECISIONID
LEFT JOIN LD0V0A A2 ON APP.PNR = A2.PNR
;