CREATE OR ALTER VIEW LD0VAA0SEARCHDECISIONS
AS
SELECT        
E.BESLUTNR, 
E.BESLUT, 
E.BESLTYP, 
E.BESFATT, 
E.INSNR, 
E.AEVERKNR, 
E.ORSAK, 
E.FOMDAT, 
E.TOMDAT, 
E.DATUM, 
E.VERKSEJ, 
E.BELOPP, 
E.UPDDAT, 
E.UPDSIGN, 
E.ORSAKMED, 
E.ENGBEL, 
E.GEMVARD, 
E.PKLTYP, 
E.STATUS, 
E.VARDNHAV, 
E.BFTJANST,
VI.NAMN AS BNAMN_T, 
VI.TJANST AS BTJANST_T, 
Q01.KLARTEXT AS BORSAK_T, 
Q01.XTXT1 AS BORSAKRUBRIK_T, 
Q01_2.KLARTEXT AS BESLUT_T, 
Q01_3.KLARTEXT AS BMEDORSAK_T, 
Q01_3.XTXT1 AS BMEDORSAKRUBRIK_T, 
Q01_2.XNUM5 AS AMOUNT_TO_BALANCE, 
Q01_2.XNUM2 AS LEADS_TO_SERVICE, 
Q01_4.KLARTEXT AS BESLTYP_T, 
Q01_5.KLARTEXT AS VARDNHAV_T, 
C.AKTPNR AS INV_PNR, 
A1.ADRESS AS INV_ADRESS, 
A1.COADR AS INV_COADR, 
A1.POSTNR AS INV_POSTNR, 
A1.ORT AS INV_ORT, 
J01_INV.NAMN AS INV_ORGNAMN, 
C.ORGID AS INV_ORGANIZATIONID, 
C.ORGENHNR AS INV_ORGANIZATIONUNITID, 
C.HAND AS INV_CASEWORKER, 
C.MEDHAND AS INV_COCASEWORKER, 
VI_INV1.NAMN AS INV_CASEWORKERNAME, 
VI_INV2.NAMN AS INV_COCASEWORKERNAME, 
A1.KUNDNUMMER AS INV_KUNDNUMMER, 
A1.NAMN AS INV_NAMN, 
A1.KON AS INV_KON, 
A1.FODDAT AS INV_FODDAT, 
C20.AKTPNR AS SERV_PNR, 
A2.ADRESS AS SERV_ADRESS, 
A2.COADR AS SERV_COADR, 
A2.POSTNR AS SERV_POSTNR, 
A2.ORT AS SERV_ORT, 
J01_SERV.NAMN AS SERV_ORGNAMN, 
C20.ORGID AS SERV_ORGANIZATIONID, 
C20.ORGENHNR AS SERV_ORGANIZATIONUNITID, 
C20.HAND AS SERV_CASEWORKER, 
C20.MEDHAND AS SERV_COCASEWORKER, 
VI_SERV1.NAMN AS SERV_CASEWORKERNAME, 
VI_SERV2.NAMN AS SERV_COCASEWORKERNAME, 
A2.KUNDNUMMER AS SERV_KUNDNUMMER, 
A2.NAMN AS SERV_NAMN, 
A2.KON AS SERV_KON, 
A2.FODDAT AS SERV_FODDAT, 
OMR.NAMN AS OMRADE_NAMN
FROM            
LD0V0E AS E INNER JOIN
LD0V0I AS VI ON E.BESFATT = VI.HAND AND E.BESLTYP <> 6 AND E.BESLTYP <> 12 AND E.BESLUT > 0 LEFT OUTER JOIN
LD0V0Q01 AS Q01 ON Q01.KODTYP = 'BESLORSAK' AND E.ORSAK = Q01.KOD LEFT OUTER JOIN
LD0V0Q01 AS Q01_2 ON Q01_2.KODTYP = 'BESLUT' AND E.BESLUT = Q01_2.KOD LEFT OUTER JOIN
LD0V0Q01 AS Q01_4 ON Q01_4.KODTYP = 'BESLUTTYP' AND E.BESLTYP = Q01_4.KOD LEFT OUTER JOIN
LD0V0Q01 AS Q01_3 ON Q01_3.KODTYP = 'BESLORSAK' AND E.ORSAKMED = Q01_3.KOD LEFT OUTER JOIN
LD0V0Q01 AS Q01_5 ON Q01_5.KODTYP = 'VARDHAVARE' AND E.VARDNHAV = Q01_5.KOD LEFT OUTER JOIN
LD0VLC AS C ON C.AEVERKNR = E.AEVERKNR LEFT OUTER JOIN
LD0V0J01 AS J01_INV ON J01_INV.ORGENHNR = C.ORGENHNR AND J01_INV.ORGID = C.ORGID LEFT OUTER JOIN
LD0V0A AS A1 ON C.AKTPNR = A1.PNR LEFT OUTER JOIN
LD0V0I AS VI_INV1 ON VI_INV1.HAND = C.HAND LEFT OUTER JOIN
LD0V0I AS VI_INV2 ON VI_INV2.HAND = C.MEDHAND LEFT OUTER JOIN
LD0VLC20 AS C20 ON C20.INSNR = E.INSNR LEFT OUTER JOIN
LD0P0J01 AS J01_SERV ON J01_SERV.ORGENHNR = C20.ORGENHNR AND J01_SERV.ORGID = C20.ORGID LEFT OUTER JOIN
LD0V0A AS A2 ON C20.AKTPNR = A2.PNR LEFT OUTER JOIN
LD0V0I AS VI_SERV1 ON VI_SERV1.HAND = C20.HAND LEFT OUTER JOIN
LD0V0I AS VI_SERV2 ON VI_SERV2.HAND = C20.MEDHAND LEFT OUTER JOIN
LD0VAA0OMRADENKO AS OMR ON A1.NYCKKOD = OMR.NYCKKOD
WHERE 
(E.AEVERKNR > 0 AND (C.AEVERKNR IS NOT NULL) OR
(E.INSNR > 0) AND (C20.INSNR IS NOT NULL))
GO
GRANT ALL ON LD0VAA0SEARCHDECISIONS TO LD0ROANV
GO