IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME = 'LD0V0GoalSettings')
BEGIN
DROP VIEW LD0V0GoalSettings
END
GO
CREATE VIEW LD0V0GoalSettings
AS
SELECT 
M11.MALNR,
M11.MALSATTN,
M11.DATUM,
M11.KOMMENTAR,
ISNULL(Q01_MALSATT.KLARTEXT,'') AS MALSATTN_TXT 
FROM 
LD0V0M10 M10
JOIN LD0V0M11 M11 ON M10.MALNR = M11.MALNR
LEFT JOIN LD0V0Q01 Q01_MALSATT ON Q01_MALSATT.KODTYP = 'MALSATT' AND M11.MALSATTN = Q01_MALSATT.KOD
GO
GRANT ALL ON LD0V0GoalSettings TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME = 'LD0V0Goals')
BEGIN
DROP VIEW LD0V0Goals
END
GO
CREATE VIEW LD0V0Goals
AS
SELECT 
M10.*,
ISNULL(Q01_MALGRAD.KLARTEXT,'') AS UTVOMDME_TXT 
FROM 
LD0V0M10 M10
LEFT JOIN LD0V0Q01 Q01_MALGRAD ON Q01_MALGRAD.KODTYP = 'MALGRAD' AND M10.UTVOMDME = Q01_MALGRAD.KOD
GO
GRANT ALL ON LD0V0Goals TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAAYReminders')
BEGIN
DROP VIEW LD0VAAYReminders
END
GO
CREATE VIEW LD0VAAYReminders
AS
SELECT BVK.*, Q01.KLARTEXT as BEVAKTYP_T, I.NAMN as HAND_T, A.NAMN as PNR_T, BVO.IDM_TEXT as OBJEKTID_T, A.KUNDNUMMER as KUNDNUMMER
FROM LD0VYBVK BVK
LEFT OUTER JOIN LD0V0Q01 Q01 
ON BVK.BEVAKTYP = Q01.KOD AND Q01.KODTYP = 'BEVAKTYP'
LEFT OUTER JOIN LD0V0I I
ON BVK.HAND = I.HAND 
LEFT OUTER JOIN LD0V0A A
ON BVK.PNR = A.PNR
LEFT OUTER JOIN LD0V0BVO BVO
ON BVK.OBJEKTID = BVO.IDM_ID 
GO
GRANT ALL ON LD0VAAYReminders TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0Reminders')
BEGIN
DROP VIEW LD0VAA0Reminders
END
GO
CREATE VIEW LD0VAA0Reminders
AS
SELECT BVK.*, Q01.KLARTEXT as BEVAKTYP_T, I.NAMN as HAND_T, A.NAMN as PNR_T, BVO.IDM_TEXT as OBJEKTID_T, A.KUNDNUMMER as KUNDNUMMER
FROM LD0V0BVK BVK
LEFT OUTER JOIN LD0V0Q01 Q01 
ON BVK.BEVAKTYP = Q01.KOD AND Q01.KODTYP = 'BEVAKTYP'
LEFT OUTER JOIN LD0V0I I
ON BVK.HAND = I.HAND 
LEFT OUTER JOIN LD0V0A A
ON BVK.PNR = A.PNR
LEFT OUTER JOIN LD0V0BVO BVO
ON BVK.OBJEKTID = BVO.IDM_ID 
GO
GRANT ALL ON LD0VAA0Reminders TO LD0ROANV
GO

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

CREATE VIEW LD0VAA0ASSIGNMENTIFO AS
 SELECT N900.*
,A.NAMN as PERSNAMN
,A.KUNDNUMMER as PERSONKEY
,E.DATUM as BESLUTDATUM
,I1.NAMN as UTFHAND_T
,I2.NAMN as UTFMEDHAND_T
,I3.NAMN as UTFMEDHAND2_T
,I4.NAMN as MOTHAND_T
,J01.NAMN as ORGNAMN_T
,Q01_1.KLARTEXT as STATUS_T
,Q01_2.KLARTEXT BESLUT_T 
,E.BESFATT as BESFATT
,IE.NAMN as BESFATT_T
,C.HAND AS INV_CASEWORKER
,C.MEDHAND AS INV_COCASEWORKER
,VI_INV1.NAMN AS INV_CASEWORKERNAME_T
,VI_INV2.NAMN AS INV_COCASEWORKERNAME_T
,C20.HAND AS SERV_CASEWORKER
,C20.MEDHAND AS SERV_COCASEWORKER
,VI_SERV1.NAMN AS SERV_CASEWORKERNAME_T
,VI_SERV2.NAMN AS SERV_COCASEWORKERNAME_T
FROM LD0V0N900 N900
JOIN LD0V0A A ON A.PNR = N900.PNR
JOIN LD0VYE E ON E.BESLUTNR = N900.BESLUTNR 
LEFT OUTER JOIN LD0V0I AS I1 ON I1.HAND = N900.UTFHAND
LEFT OUTER JOIN LD0V0I AS I2 ON I2.HAND = N900.UTFMEDHAND
LEFT OUTER JOIN LD0V0I AS I3 ON I3.HAND = N900.UTFMEDHAND2
LEFT OUTER JOIN LD0V0I AS I4 ON I4.HAND = N900.MOTHAND
LEFT OUTER JOIN LD0V0J01 AS J01 ON J01.ORGID = N900.ORGID AND J01.ORGENHNR = N900.ORGENHNR
LEFT OUTER JOIN LD0V0Q01 AS Q01_1 ON Q01_1.KODTYP = 'UIFOSTATUS' AND Q01_1.KOD = N900.STATUS 
LEFT OUTER JOIN LD0V0Q01 AS Q01_2 ON Q01_2.KODTYP = 'BESLUT' AND Q01_2.KOD = E.BESLUT
LEFT OUTER JOIN LD0V0I AS IE ON IE.HAND = E.BESFATT
LEFT OUTER JOIN LD0VYC AS C ON C.AEVERKNR = E.AEVERKNR
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 LD0VYC20 AS C20 ON C20.INSNR = E.INSNR
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
GO

GRANT ALL ON LD0VAA0ASSIGNMENTIFO TO LD0ROANV
GO

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

CREATE VIEW LD0VAAYASSIGNMENTIFO AS
 SELECT N900.*
,A.NAMN as PERSNAMN
,A.KUNDNUMMER as PERSONKEY
,E.DATUM as BESLUTDATUM
,I1.NAMN as UTFHAND_T
,I2.NAMN as UTFMEDHAND_T
,I3.NAMN as UTFMEDHAND2_T
,I4.NAMN as MOTHAND_T
,J01.NAMN as ORGNAMN_T
,Q01_1.KLARTEXT as STATUS_T
,Q01_2.KLARTEXT BESLUT_T
,E.BESFATT as BESFATT
,IE.NAMN as BESFATT_T
,C.HAND AS INV_CASEWORKER
,C.MEDHAND AS INV_COCASEWORKER
,VI_INV1.NAMN AS INV_CASEWORKERNAME_T
,VI_INV2.NAMN AS INV_COCASEWORKERNAME_T
,C20.HAND AS SERV_CASEWORKER
,C20.MEDHAND AS SERV_COCASEWORKER
,VI_SERV1.NAMN AS SERV_CASEWORKERNAME_T
,VI_SERV2.NAMN AS SERV_COCASEWORKERNAME_T
FROM LD0VYN900 N900
LEFT OUTER JOIN LD0V0A A ON A.PNR = N900.PNR
JOIN LD0VYE E ON E.BESLUTNR = N900.BESLUTNR 
LEFT OUTER JOIN LD0V0I AS I1 ON I1.HAND = N900.UTFHAND
LEFT OUTER JOIN LD0V0I AS I2 ON I2.HAND = N900.UTFMEDHAND
LEFT OUTER JOIN LD0V0I AS I3 ON I3.HAND = N900.UTFMEDHAND2
LEFT OUTER JOIN LD0V0I AS I4 ON I4.HAND = N900.MOTHAND
LEFT OUTER JOIN LD0V0J01 AS J01 ON J01.ORGID = N900.ORGID AND J01.ORGENHNR = N900.ORGENHNR
LEFT OUTER JOIN LD0V0Q01 AS Q01_1 ON Q01_1.KODTYP = 'UIFOSTATUS' AND Q01_1.KOD = N900.STATUS 
LEFT OUTER JOIN LD0V0Q01 AS Q01_2 ON Q01_2.KODTYP = 'BESLUT' AND Q01_2.KOD = E.BESLUT
LEFT OUTER JOIN LD0V0I AS IE ON IE.HAND = E.BESFATT
LEFT OUTER JOIN LD0VYC AS C ON C.AEVERKNR = E.AEVERKNR
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 LD0VYC20 AS C20 ON C20.INSNR = E.INSNR
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
GO

GRANT ALL ON LD0VAAYASSIGNMENTIFO TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0FollowUpList')
BEGIN
DROP VIEW LD0V0FollowUpList 
END
GO
CREATE VIEW LD0V0FollowUpList  
AS  
SELECT
M11.MALNR, 
M10.MODELLID,
CASE WHEN Q01_INSATS.KLARTEXT != ' ' THEN 'INSATS' WHEN  Q01_BESLUT.KLARTEXT != ' ' THEN 'BESLUT' END  AS MALAVSER,
M10.MALID,
CASE WHEN Q01_INSATS.KLARTEXT != ' ' THEN Q01_INSATS.KLARTEXT WHEN  Q01_BESLUT.KLARTEXT != ' ' THEN Q01_BESLUT.KLARTEXT END  AS MALAVSERTYP,
M11.MALSATTN,
ISNULL(Q01_MALSATT.KLARTEXT,'') AS MALSATTN_TXT,
M10.UTVDATUM,
CASE WHEN (SELECT COUNT(MALNR) FROM LD0P0M13  WHERE MALNR = M11.MALNR) > 0 THEN 1 ELSE 2 END AS GOALSTATUS,
CASE WHEN M10.UTVOMDME > 0 THEN 1 ELSE 2 END AS  RESULT,
Q01_MALGRAD.KLARTEXT AS RESULT_TEXT,
M10.PNR

FROM   
LD0V0M10 M10  
JOIN LD0V0M11 M11 ON M10.MALNR = M11.MALNR  
LEFT JOIN LD0V0Q01 Q01_MALSATT ON Q01_MALSATT.KODTYP = 'MALSATT' AND M11.MALSATTN = Q01_MALSATT.KOD  
LEFT JOIN LD0V0C10 C10 ON M10.MALID = C10.INSNR
LEFT JOIN LD0V0E E ON M10.MALID = E.BESLUTNR 
LEFT JOIN LD0V0Q01 Q01_INSATS ON Q01_INSATS.KODTYP = 'INSATS' AND C10.AEVERKTYP = Q01_INSATS.KOD
LEFT JOIN LD0V0Q01 Q01_BESLUT ON Q01_BESLUT.KODTYP = 'BESLUT' AND E.BESLUT = Q01_BESLUT.KOD
LEFT JOIN LD0V0Q01 Q01_MALGRAD ON Q01_MALGRAD.KODTYP = 'MALGRAD' AND Q01_MALGRAD.KOD = M10.UTVOMDME
GO
GRANT ALL ON LD0V0FollowUpList TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0GoalsList')
BEGIN
DROP VIEW LD0V0GoalsList 
END
GO
CREATE VIEW LD0V0GoalsList  
AS  
SELECT 
M11.MALNR,
M10.MODELLID,
CASE WHEN Q01_INSATS.KLARTEXT != ' ' THEN 'INSATS' WHEN  Q01_BESLUT.KLARTEXT != ' ' THEN 'BESLUT' END  AS MALAVSER,
M10.MALID,
CASE WHEN Q01_INSATS.KLARTEXT != ' ' THEN Q01_INSATS.KLARTEXT WHEN  Q01_BESLUT.KLARTEXT != ' ' THEN Q01_BESLUT.KLARTEXT END  AS MALAVSERTYP,
M11.MALSATTN,
ISNULL(Q01_MALSATT.KLARTEXT,'') AS MALSATTN_TXT,
M11.DATUM,
CASE WHEN M10.UTVOMDME != 0  THEN 3 WHEN (SELECT COUNT(MALNR) FROM LD0P0M13  WHERE MALNR = M11.MALNR) > 0 THEN 2 ELSE 1 END AS GOALSTATUS,
M10.PNR

FROM   
LD0V0M10 M10  
JOIN LD0V0M11 M11 ON M10.MALNR = M11.MALNR  
LEFT JOIN LD0V0Q01 Q01_MALSATT ON Q01_MALSATT.KODTYP = 'MALSATT' AND M11.MALSATTN = Q01_MALSATT.KOD  
LEFT JOIN LD0V0C10 C10 ON M10.MALID = C10.INSNR
LEFT JOIN LD0V0E E ON M10.MALID = E.BESLUTNR 
LEFT JOIN LD0V0Q01 Q01_INSATS ON Q01_INSATS.KODTYP = 'INSATS' AND C10.AEVERKTYP = Q01_INSATS.KOD
LEFT JOIN LD0V0Q01 Q01_BESLUT ON Q01_BESLUT.KODTYP = 'BESLUT' AND E.BESLUT = Q01_BESLUT.KOD
GO
GRANT ALL ON LD0V0GoalsList TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME = 'LD0V0COMPILATIONADVANCEBENEFIT')
BEGIN
DROP VIEW LD0V0COMPILATIONADVANCEBENEFIT
END
GO
CREATE VIEW LD0V0COMPILATIONADVANCEBENEFIT      
AS          
SELECT   
V1.BESLUTNR AS BESLUTNR,    
ISNULL(V11.AKTPNR,V18.PNR) AS PNR,    
ISNULL(V14.NAMN,V18.NAMN) AS NAMN,    
V1.DATUM,  
V3.KLARTEXT AS BESLUT_T,   
V4.KLARTEXT AS BESLTYP ,  
V9.NAMN AS BESFATT,    
V5.KLARTEXT AS REASON,         
V15.NAMN AS INSHANDNAMN,        
V13.NAMN AS AVERKHANDNAMN,        
V16.NAMN  AS SERVICESORG,        
V17.NAMN AS INVESTIGATIONSORG,     
V1.FOMDAT,  
V1.TOMDAT,  
V1.BELOPP AS BELOPP  ,   
'0.00' AS INBETALT_BELOPP ,   
(CASE WHEN V2.BESLNR2 is null  THEN '0.00' ELSE V2.BELOPP END) AS  EFTERGIVET_BELOPP,  
V1.UPDSIGN AS UPDSIGN,          
V1.DATUM AS REGDAT,        
isnull (V10.INSNR,0.00) AS INSNR,         
V11.AEVERKNR AS AEVERKNR,        
V10.ORGID AS INV_ORGANIZATIONID,        
V10.ORGENHNR AS INV_ORGENHNR,        
V11.ORGID AS SERV_ORGANIZATIONID,        
V11.ORGENHNR AS SERV_ORGENHNR,           
V21.KLARTEXT AS BETJANINGSOMRADE,        
V22.KLARTEXT AS KOMMUNDEL,  
(CASE WHEN V2.BESLNR2 is null  THEN V1.BESLTYP ELSE V2.BESLTYP END) AS  BESLTYP_JUSTERING,  
V15.Hand AS SERVICECASEWORKERID,      
V13.Hand AS INVESTIGATIONCASEWORKERId   
FROM LD0V0E V1   
LEFT JOIN LD0V0E V2 on  V1.BESLUTNR=V2. BESLNR2   
LEFT JOIN LD0V0Q01 V3 ON  V1.BESLUT=V3.KOD AND KODTYP='BESLUT'    
LEFT JOIN LD0V0Q01 V4 ON  V1.BESLTYP=V4.KOD AND V4.KODTYP='BESLUTTYP'   
LEFT JOIN LD0V0F04 L1 ON  V1.BESLUTNR = L1.BESLUTNR  
LEFT JOIN LD0V0Q01 V5 ON  V1.ORSAK=V5.KOD AND V5.KODTYP='BESLORSAK'   
LEFT JOIN LD0V0I V9 ON  V1.BESFATT=V9.HAND    
LEFT JOIN LD0VLC20 V10 ON V1.INSNR=V10.INSNR   
LEFT JOIN LD0VLC V11 ON V1.AEVERKNR=V11.AEVERKNR   
LEFT JOIN LD0V0I04 V12 on V11.HAND=V12.HAND     
LEFT JOIN LD0V0I V13 on V12.HAND=V13.HAND      
LEFT JOIN LD0V0A V14 ON V11.AKTPNR=V14.PNR   
LEFT JOIN LD0V0I V15 ON V10.HAND= V15.HAND          
LEFT JOIN LD0V0J01 V16 ON V10.ORGID=V16.ORGID AND V10.ORGENHNR= V16.ORGENHNR          
LEFT JOIN LD0V0J01 V17 ON V11.ORGID=V17.ORGID AND V11.ORGENHNR= V17.ORGENHNR    
LEFT JOIN LD0V0A V18 ON V10.AKTPNR=V18.PNR   
LEFT JOIN LD0V0Q01  V21 ON V18.BETJOMR = V21.KOD  AND V21.KODTYP= 'BETJOMR'          
LEFT JOIN LD0V0Q01  V22 ON V18.KOMDEL = V22.KOD AND V22.KODTYP= 'KOMMUNDEL'   
WHERE V1.BESLTYP = 2   
  
UNION   
  
SELECT   
V1.BESLUTNR AS BESLUTNR,    
ISNULL(V11.AKTPNR,V18.PNR) AS PNR,    
ISNULL(V14.NAMN,V18.NAMN) AS NAMN,    
V1.DATUM,  
V3.KLARTEXT AS BESLUT_T,   
V23.KLARTEXT AS BESLTYP ,  
V9.NAMN AS BESFATT,    
V5.KLARTEXT AS REASON,         
V15.NAMN AS INSHANDNAMN,        
V13.NAMN AS AVERKHANDNAMN,        
V16.NAMN  AS SERVICESORG,        
V17.NAMN AS INVESTIGATIONSORG,     
V1.FOMDAT,  
V1.TOMDAT,  
'0.00' as BELOPP ,   
L1.BELOPP AS INBETALT_BELOPP,  
(CASE WHEN V2.BESLNR2 is null  THEN '0.00' ELSE '0.00' END) AS  EFTERGIVET_BELOPP,  
V1.UPDSIGN AS UPDSIGN,          
V1.DATUM AS REGDAT,        
isnull (V10.INSNR,0.00) AS INSNR,         
V11.AEVERKNR AS AEVERKNR,        
V10.ORGID AS INV_ORGANIZATIONID,        
V10.ORGENHNR AS INV_ORGENHNR,        
V11.ORGID AS SERV_ORGANIZATIONID,        
V11.ORGENHNR AS SERV_ORGENHNR,           
V21.KLARTEXT AS BETJANINGSOMRADE,        
V22.KLARTEXT AS KOMMUNDEL,  
(CASE WHEN V2.BESLNR2 is null THEN V1.BESLTYP ELSE V2.BESLTYP END) AS  BESLTYP_JUSTERING,  
V15.Hand AS SERVICECASEWORKERID,      
V13.Hand AS INVESTIGATIONCASEWORKERId   
FROM LD0V0E V1  
LEFT JOIN LD0V0E  V2  ON  V1.BESLUTNR = V2. BESLNR2 --OR V1.BESLUTNR = V2. BESLNR2   
LEFT JOIN LD0V0Q01 V3  ON V1.BESLUT =V3.KOD AND KODTYP = 'BESLUT'  
INNER JOIN LD0V0F04 L1  ON V1.BESLUTNR = L1.BESLUTNR  
LEFT JOIN LD0V0Q01 V5  ON V1.ORSAK=V5.KOD AND  V5.KODTYP = 'BESLORSAK'   
LEFT JOIN LD0V0I V9  ON V1.BESFATT=V9.HAND    
LEFT JOIN LD0VLC20 V10 ON V1.INSNR=V10.INSNR   
LEFT JOIN LD0VLC V11  ON V1.AEVERKNR = V11.AEVERKNR   
LEFT JOIN LD0V0I04 V12 ON V11.HAND = V12.HAND    
LEFT JOIN LD0V0I V13  ON V12.HAND=V13.HAND      
LEFT JOIN LD0V0A V14  ON V11.AKTPNR=V14.PNR   
LEFT JOIN LD0V0I V15  ON V10.HAND= V15.HAND          
LEFT JOIN LD0V0J01 V16 ON V10.ORGID=V16.ORGID AND V10.ORGENHNR= V16.ORGENHNR          
LEFT JOIN LD0V0J01 V17 ON V11.ORGID=V17.ORGID AND V11.ORGENHNR= V17.ORGENHNR    
LEFT JOIN LD0V0A V18  ON V10.AKTPNR=V18.PNR   
LEFT JOIN LD0V0F04 V19 ON V1.BESLUTNR=V19.BESLUTNR      
LEFT JOIN LD0V0F V20  ON V19.BETALNR= V20.BETALNR    
LEFT JOIN LD0V0Q01 V23 ON V20.BETSATT =V23.KOD AND V23.KODTYP = 'BETALSATT'     
LEFT JOIN LD0V0Q01 V21 ON V18.BETJOMR = V21.KOD  AND V21.KODTYP= 'BETJOMR'          
LEFT JOIN LD0V0Q01 V22 ON V18.KOMDEL = V22.KOD AND V22.KODTYP= 'KOMMUNDEL'   
WHERE V1.BESLTYP = 2
GO
GRANT ALL ON LD0V0COMPILATIONADVANCEBENEFIT TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0ECompilationRefund')
BEGIN
DROP VIEW LD0V0ECompilationRefund
END
GO
CREATE VIEW LD0V0ECompilationRefund
AS   
  
SELECT distinct  
(CASE WHEN E.BESLNR2 > 0  THEN E.BESLNR2 ELSE E.BESLUTNR END) AS Beslutsnummer,  
(CASE WHEN V0A.PNR is null THEN V0A_1.PNR ELSE V0A.PNR END) AS Personnummer,  
(CASE WHEN V0A.NAMN is null THEN V0A_1.NAMN ELSE V0A.NAMN END) AS NAMN,  
E.DATUM   AS Datum,  
E.DATUM         AS DateValue,  
Q01.KLARTEXT    AS Beslut,  
Q01_3.KLARTEXT  AS Beslutstyp,  
V01.NAMN        AS Beslutsfattare,  
Q01_1.KLARTEXT  AS Orsak,  
V02.NAMN        AS Insatshandläggare, 
V02.HAND        AS Insatshandläggare_ID,
V03.NAMN        AS Utredningshandläggare, 
V03.HAND        AS Utredningshandläggare_ID,
J01.NAMN        As Insatsens_organisation,  
J01.ORGID       As Insatsens_organisation_ID,
J01.ORGENHNR    As Insatsens_organisation_UnitID,
J02.NAMN        As Utredningens_organisation,  
J02.ORGID       As Utredningens_organisation_ID,
J02.ORGENHNR    As Utredningens_organisation_UnitID,
E.FOMDAT        AS Från,   
E.TOMDAT        AS Till,  
(CASE WHEN E.BESLNR2 != 0 or Q01_3.KLARTEXT='Inbetalning' THEN '0.00' ELSE E.BELOPP END) AS Belopp,  
(CASE WHEN E.BESLNR2 = 0 or Q01_3.KLARTEXT='Inbetalning' THEN '0.00' ELSE E.BELOPP END) AS  EFTERGIVET_BELOPP,  
'0.00' AS Inbetalt_belopp,  
E.UPDSIGN  AS Ändrat_av,  
E.REGDAT        AS Registreringsdatum,  
E.INSNR   AS Insatsnummer,  
J01.ORGID       AS Insatsens_organisations_ID,  
J01.ORGENHNR    AS Insatsens_organisation_enhetsnummer,  
VLC.ORGID       AS Utredningens_organisations_ID,  
VLC.ORGENHNR    AS Utredningens_organisation_enhetsnummer  
FROM   LD0V0E  E   
left join LD0V0Q01  Q01      on  Q01.KODTYP = 'BESLUT' AND Q01.KOD = E.BESLUT  
left join LD0V0Q01  Q01_1    on  Q01_1.KODTYP = 'BESLORSAK' AND Q01_1.KOD = E.ORSAK  
left join LD0V0Q01  Q01_3    on  Q01_3.KODTYP = 'BESLUTTYP' AND Q01_3.KOD = E.BESLTYP  
left join LD0V0I    V01      on  V01.HAND = E.BESFATT  
left join LD0V0I04  I04      on  I04.HAND = E.BESFATT  
left join LD0VLC20  VLC20    on  VLC20.INSNR = E.INSNR  
left join LD0V0I    V02      on  V02.HAND = VLC20.HAND  
left join LD0V0I04  I04_1    on  I04_1.HAND = VLC20.HAND  
left join LD0V0J01  J01      on  J01.ORGID = VLC20.ORGID AND J01.ORGENHNR = VLC20.ORGENHNR   
left join LD0VLC    VLC      on  VLC.AEVERKNR = E.AEVERKNR  
left join LD0V0J01  J02      on  J02.ORGID = VLC.ORGID AND J02.ORGENHNR = VLC.ORGENHNR   
left join LD0V0A    V0A      on  V0A.PNR = VLC20.AKTPNR  
left join LD0V0A    V0A_1    on  V0A_1.PNR = VLC.AKTPNR  
left join LD0V0I    V03      on  V03.HAND = VLC.HAND  
left join LD0V0F04  F04      on  F04.BESLUTNR = E.BESLUTNR  
left join LD0V0F    F        on  F.BETALNR = F04.BETALNR  
left join LD0V0Q01  Q01_2    on  Q01_2.KODTYP = 'BETJOMR' AND Q01_2.KOD = V0A.BETJOMR  
left join LD0V0Q01  V22      ON  V0A.KOMDEL = V22.KOD AND V22.KODTYP= 'KOMMUNDEL'   
WHERE (E.BESLTYP = 1 OR E.BESLTYP = 3)  
  
union all  
  
SELECT   
(CASE WHEN E.BESLNR2 > 0  THEN E.BESLNR2 ELSE E.BESLUTNR END) AS Beslutsnummer,  
(CASE WHEN V0A.PNR is null THEN V0A_1.PNR ELSE V0A.PNR END) AS Personnummer,  
(CASE WHEN V0A.NAMN is null THEN V0A_1.NAMN ELSE V0A.NAMN END) AS NAMN,  
F.BETDAT   AS Datum,  
E.DATUM         AS DateValue,  
Q01.KLARTEXT    AS Beslut,  
Q01_4.KLARTEXT  AS Beslutstyp,  
V01.NAMN        AS Beslutsfattare,  
Q01_1.KLARTEXT  AS Orsak,  
V02.NAMN        AS Insatshandläggare,
V02.HAND        AS Insatshandläggare_ID,
V03.NAMN        AS Utredningshandläggare, 
V03.HAND        AS Utredningshandläggare_ID,
J01.NAMN        As Insatsens_organisation,  
J01.ORGID       AS Insatsens_organisation_ID,
J01.ORGENHNR    AS Insatsens_organisation_UnitID,
J02.NAMN        As Utredningens_organisation, 
J02.ORGID       AS Utredningens_organisation_ID,
J02.ORGENHNR    AS Utredningens_organisation_UnitID,
E.FOMDAT        AS Från,   
E.TOMDAT        AS Till,   
'0.00'          AS BELOPP ,   
'0.00'          AS EFTERGIVET_BELOPP,  
F.BELOPP        AS Inbetalt_belopp,  
E.UPDSIGN  AS Ändrat_av,  
E.REGDAT        AS Registreringsdatum,  
E.INSNR   AS Insatsnummer,  
J01.ORGID       AS Insatsens_organisations_ID,  
J01.ORGENHNR    AS Insatsens_organisation_enhetsnummer,  
VLC.ORGID       AS Utredningens_organisations_ID,  
VLC.ORGENHNR    AS Utredningens_organisation_enhetsnummer  
FROM   LD0V0E   E   
left join LD0V0Q01   Q01       on  Q01.KODTYP = 'BESLUT' AND Q01.KOD = E.BESLUT  
left join LD0V0Q01   Q01_1     on  Q01_1.KODTYP = 'BESLORSAK' AND Q01_1.KOD = E.ORSAK  
left join LD0V0F04   F04       on  F04.BESLUTNR = E.BESLUTNR  
left join LD0V0F     F         on  F.BETALNR = F04.BETALNR  
right join LD0V0Q01  Q01_4     on F.BETSATT =Q01_4.KOD AND Q01_4.KODTYP = 'BETALSATT'  
left join LD0V0I     V01       on  V01.HAND = E.BESFATT  
left join LD0V0I04   I04       on  I04.HAND = E.BESFATT  
left join LD0VLC20   VLC20     on  VLC20.INSNR = E.INSNR  
left join LD0V0I     V02       on  V02.HAND = VLC20.HAND  
left join LD0V0I04   I04_1     on  I04_1.HAND = VLC20.HAND  
left join LD0V0J01   J01       on  J01.ORGID = VLC20.ORGID AND J01.ORGENHNR = VLC20.ORGENHNR   
left join LD0VLC     VLC       on  VLC.AEVERKNR = E.AEVERKNR  
left join LD0V0J01   J02       on  J02.ORGID = VLC.ORGID AND J02.ORGENHNR = VLC.ORGENHNR   
left join LD0V0A     V0A       on  V0A.PNR = VLC20.AKTPNR  
left join LD0V0A     V0A_1     on  V0A_1.PNR = VLC.AKTPNR  
left join LD0V0I     V03       on  V03.HAND = VLC.HAND  
left join LD0V0Q01   Q01_2     on  Q01_2.KODTYP = 'BETJOMR' AND Q01_2.KOD = V0A.BETJOMR  
left join LD0V0Q01       V22       on V0A.KOMDEL = V22.KOD AND V22.KODTYP= 'KOMMUNDEL'   
  
WHERE (E.BESLTYP = 1 OR E.BESLTYP = 3)  
GO
GRANT ALL ON LD0V0ECompilationRefund TO LD0ROANV
GO

