CREATE OR ALTER VIEW LD0VAA0DECISION
AS
SELECT  
E.BESLUTNR,
E.INSNR,
E.AEVERKNR,
E.BESLNR2,
E.BESLUT,
E.BESLTYP,
E.DATUM,
E.FOMDAT,
E.TOMDAT,
E.ORSAK,
E.BESFATT,
E.BELOPP,
E.PKLTYP,
E.ENGBEL,
E.GEMVARD,
E.UPDSIGN,
E.UPDDAT,
E.REGDAT,
E.FORMAVS,
E.BARNVAEG,
E.ORGID,
E.ORGENHNR,
E.VHAND,
E.REFBESLUTNR,
E.STATUS,
E.HAND,
E.MEDHAND,
E.ORSAKMED,
E.BIDRTFNR,
E.REGSIGN,
E.LAEGE,
E.SPARRDAT,
E.SPARRSIGN,
E.SPARRORS,
E.INBLANCO,
E.ORGENHNR2,
E.ORSMEDSAKN,
E.BESLNRAR,
E.BESLNRLNR,
E.MAKDAT,
E.MAKSIGN,
E.STATORS1,
E.STATORS2,
E.STATORS3,
E.STATORS4,
E.STATORS5,
E.STATORS6,
E.STATORS7,
E.STATORS8,
E.VARKOMBARN,
E.DELEGATION,
E.BESFATTORG,
E.INSTNR,
E.FAMHEM,
E.FRITTBEL,
E.UPDTID,
E.FORFAVSER,
E.SUPPORTGRP,
E.PLACTYP,
E.ANNATTXT,
E.AVSLUPDDAT,
E.AVSLUPDSIGN,
E.VERKSEJ,
E.INSATGARD,
E.INSATGARD2,
E.GRUND,
E.KOMPL,
E.FOREBYGG,
E.ARBERS,
E.RESERS,
E.AKTUALNR,
E.ORSAKSEN,
E.LEDEREJTILLUPPDRAG,
E.INGENMEDSOK,
E.LEDEREJTILLAMUPPDRAG,
E.LASBESLMEDD, 
E.LIFECARE_CREATED,
E.VARDNHAV,
E.UNDANTAGSTATISTIK,
E.BFTJANST,
VI.NAMN BNAMN_T, 
VI.TJANST BTJANST_T, 
Q01.KLARTEXT BORSAK_T, 
Q01.XTXT1 BORSAKRUBRIK_T, 
Q01_2.KLARTEXT BESLUT_T, 
Q01_3.KLARTEXT BMEDORSAK_T, 
Q01_3.XTXT1 BMEDORSAKRUBRIK_T, 
Q01_2.XNUM5 AMOUNT_TO_BALANCE, 
Q01_2.XNUM2 LEADS_TO_SERVICE, 
Q01_2.XNUM33 LEADS_TO_ASSIGNMENT,
Q01_2.XNUM42 LEADS_TO_ORDEREC
FROM LD0V0E E
INNER JOIN LD0V0I VI 
ON E.BESFATT = VI.HAND
LEFT JOIN LD0V0Q01 Q01 
ON Q01.KODTYP = 'BESLORSAK' 
AND E.ORSAK = Q01.KOD
INNER JOIN LD0V0Q01 Q01_2 
ON Q01_2.KODTYP = 'BESLUT' 
AND E.BESLUT = Q01_2.KOD
LEFT JOIN LD0V0Q01 Q01_3 
ON Q01_3.KODTYP = 'BESLORSAK' 
AND E.ORSAKMED = Q01_3.KOD
GO
GRANT ALL ON LD0VAA0DECISION TO LD0ROANV
GO

CREATE OR ALTER VIEW LD0VAAYDECISION
AS
SELECT  
E.BESLUTNR,
E.INSNR,
E.AEVERKNR,
E.BESLNR2,
E.BESLUT,
E.BESLTYP,
E.DATUM,
E.FOMDAT,
E.TOMDAT,
E.ORSAK,
E.BESFATT,
E.BELOPP,
E.PKLTYP,
E.ENGBEL,
E.GEMVARD,
E.UPDSIGN,
E.UPDDAT,
E.REGDAT,
E.FORMAVS,
E.BARNVAEG,
E.ORGID,
E.ORGENHNR,
E.VHAND,
E.REFBESLUTNR,
E.STATUS,
E.HAND,
E.MEDHAND,
E.ORSAKMED,
E.BIDRTFNR,
E.REGSIGN,
E.LAEGE,
E.SPARRDAT,
E.SPARRSIGN,
E.SPARRORS,
E.INBLANCO,
E.ORGENHNR2,
E.ORSMEDSAKN,
E.BESLNRAR,
E.BESLNRLNR,
E.MAKDAT,
E.MAKSIGN,
E.STATORS1,
E.STATORS2,
E.STATORS3,
E.STATORS4,
E.STATORS5,
E.STATORS6,
E.STATORS7,
E.STATORS8,
E.VARKOMBARN,
E.DELEGATION,
E.BESFATTORG,
E.INSTNR,
E.FAMHEM,
E.FRITTBEL,
E.UPDTID,
E.FORFAVSER,
E.SUPPORTGRP,
E.PLACTYP,
E.ANNATTXT,
E.AVSLUPDDAT,
E.AVSLUPDSIGN,
E.VERKSEJ,
E.INSATGARD,
E.INSATGARD2,
E.GRUND,
E.KOMPL,
E.FOREBYGG,
E.ARBERS,
E.RESERS,
E.AKTUALNR,
E.ORSAKSEN,
E.LEDEREJTILLUPPDRAG,
E.INGENMEDSOK,
E.LEDEREJTILLAMUPPDRAG,
E.LASBESLMEDD, 
E.LIFECARE_CREATED,
E.VARDNHAV,
E.UNDANTAGSTATISTIK,
E.BFTJANST,
VI.NAMN BNAMN_T, 
VI.TJANST BTJANST_T, 
Q01.KLARTEXT BORSAK_T, 
Q01.XTXT1 BORSAKRUBRIK_T, 
Q01_2.KLARTEXT BESLUT_T, 
Q01_3.KLARTEXT BMEDORSAK_T, 
Q01_3.XTXT1 BMEDORSAKRUBRIK_T, 
Q01_2.XNUM5 AMOUNT_TO_BALANCE, 
Q01_2.XNUM2 LEADS_TO_SERVICE, 
Q01_2.XNUM33 LEADS_TO_ASSIGNMENT,
Q01_2.XNUM42 LEADS_TO_ORDEREC
FROM LD0VYE E
INNER JOIN LD0V0I VI 
ON E.BESFATT = VI.HAND
LEFT JOIN LD0V0Q01 Q01 
ON Q01.KODTYP = 'BESLORSAK' 
AND E.ORSAK = Q01.KOD
INNER JOIN LD0V0Q01 Q01_2 
ON Q01_2.KODTYP = 'BESLUT' 
AND E.BESLUT = Q01_2.KOD
LEFT JOIN LD0V0Q01 Q01_3 
ON Q01_3.KODTYP = 'BESLORSAK' 
AND E.ORSAKMED = Q01_3.KOD
GO
GRANT ALL ON LD0VAAYDECISION TO LD0ROANV
GO

CREATE OR ALTER VIEW LD0VAA0LMEnrollment
AS 
SELECT
U10.*
,A.NAMN as PERSNAMN
,A.KUNDNUMMER AS KUNDNUMMER
,Q1.KLARTEXT AS TYPAVSLUT_T
,Q2.KLARTEXT AS AVSLUTORS_T
,Q3.KLARTEXT AS AVSLUTKOMMUN_T
,Q4.KLARTEXT AS AVSLUTLAND_T
,Q5.KLARTEXT AS INSKRTYP_T
,Q6.KLARTEXT AS UTANARB_T
,J01.NAMN  AS  ORGENH_T
,OMR.NAMN AS OMRADE_NAMN,
U99.NIVA4_EXTEXT AS YRKESKOD_T
FROM LD0V0U10 AS U10
LEFT OUTER JOIN LD0V0A AS A ON A.PNR = U10.PERSONNR
LEFT OUTER JOIN LD0V0Q01 AS Q1 ON Q1.KODTYP = 'LNTYPAVSL' AND U10.TYPAVSLUT = Q1.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q2 ON Q2.KODTYP = 'LNAVSLORS' AND U10.AVSLUTORS = Q2.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q3 ON Q3.KODTYP = 'KOMU' AND U10.AVSLUTKOMMUN = Q3.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q4 ON Q4.KODTYP = 'LAND' AND U10.AVSLUTLAND = Q4.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q5 ON Q5.KODTYP = 'LNINSKTYP' AND U10.INSKRTYP = Q5.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q6 ON Q6.KODTYP = 'LNUTANARB' AND Q6.KOD = U10.UTANARB 
LEFT OUTER JOIN LD0V0J01 AS J01 ON J01.ORGID = U10.ORGID AND J01.ORGENHNR = U10.ORGENHNR
LEFT OUTER JOIN LD0VAA0OMRADENKO OMR ON A.NYCKKOD = OMR.NYCKKOD 
LEFT OUTER JOIN LD0V0Q01 AS Q7 ON Q7.KODTYP = 'OMRÅDE' AND Q7.KOD = OMRADE
LEFT OUTER JOIN LD0V5U99 AS U99 ON U99.NIVA4_EXTKOD = U10.YRKEAVSLUT_2012
GO
GRANT ALL ON LD0VAA0LMEnrollment TO LD0ROANV
GO

CREATE OR ALTER VIEW LD0VAAYLMEnrollment
AS 
SELECT
 U10.*
,A.NAMN as PERSNAMN
,A.KUNDNUMMER AS KUNDNUMMER
,Q1.KLARTEXT AS TYPAVSLUT_T
,Q2.KLARTEXT AS AVSLUTORS_T
,Q3.KLARTEXT AS AVSLUTKOMMUN_T
,Q4.KLARTEXT AS AVSLUTLAND_T
,Q5.KLARTEXT AS INSKRTYP_T
,Q6.KLARTEXT AS UTANARB_T
,J01.NAMN  AS  ORGENH_T
,OMR.NAMN AS OMRADE_NAMN
,U99.NIVA4_EXTEXT AS YRKESKOD_T
FROM LD0VYU10 AS U10
LEFT OUTER JOIN LD0V0A AS A ON A.PNR = U10.PERSONNR
LEFT OUTER JOIN LD0V0Q01 AS Q1 ON Q1.KODTYP = 'LNTYPAVSL' AND U10.TYPAVSLUT = Q1.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q2 ON Q2.KODTYP = 'LNAVSLORS' AND U10.AVSLUTORS = Q2.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q3 ON Q3.KODTYP = 'KOMU' AND U10.AVSLUTKOMMUN = Q3.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q4 ON Q4.KODTYP = 'LAND' AND U10.AVSLUTLAND = Q4.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q5 ON Q5.KODTYP = 'LNINSKTYP' AND U10.INSKRTYP = Q5.KOD
LEFT OUTER JOIN LD0V0Q01 AS Q6 ON Q6.KODTYP = 'LNUTANARB' AND Q6.KOD = U10.UTANARB 
LEFT OUTER JOIN LD0V0J01 AS J01 ON J01.ORGID = U10.ORGID AND J01.ORGENHNR = U10.ORGENHNR
LEFT OUTER JOIN LD0VAA0OMRADENKO OMR ON A.NYCKKOD = OMR.NYCKKOD 
LEFT OUTER JOIN LD0V0Q01 AS Q7 ON Q7.KODTYP = 'OMRÅDE' AND Q7.KOD = OMRADE
LEFT OUTER JOIN LD0V5U99 AS U99 ON U99.NIVA4_EXTKOD = U10.YRKEAVSLUT_2012
GO

CREATE OR ALTER VIEW LD0ApiDecision          
AS   
SELECT   
E.BESLUTNR,      
E.STARTTIME,      
E.ENDTIME,      
E.DATUM,      
E.FOMDAT,      
E.TOMDAT,      
E.AEVERKNR,      
E.INSNR,      
E.BELOPP,      
E.AKTUALNR,      
E.BESFATT,      
E.BESLUT,      
E.BESLTYP,      
E.ORSAK,      
E.VERKSEJ,      
E.GEMVARD,      
E.PKLTYP,      
E.BARNVAEG,      
E.VARDNHAV,      
E.UNDANTAGSTATISTIK,      
E.ORSAKMED,      
VI.NAMN BNAMN_T,                   
VI.TJANST BTJANST_T,                   
Q01.KLARTEXT BORSAK_T,                   
Q01.XTXT1 BORSAKRUBRIK_T,                   
Q01_2.KLARTEXT BESLUT_T,                   
Q01_3.KLARTEXT BMEDORSAK_T,          
Q01_4.KLARTEXT AS BESLTYP_T,      
Q01_3.XTXT1 BMEDORSAKRUBRIK_T,                   
Q01_2.XNUM5 AMOUNT_TO_BALANCE,                   
Q01_2.XNUM2 LEADS_TO_SERVICE,                   
Q01_2.XNUM33 LEADS_TO_ASSIGNMENT,      
T10.KLARTEXT STATGRP1_T,      
T11.KLARTEXT STATGRP2_T,      
T12.KLARTEXT STATGRP3_T,      
T13.KLARTEXT STATGRP4_T,            
T14.KLARTEXT STATGRP5_T,      
CASE 
    WHEN E.AEVERKNR > 0 THEN CT.KUNDNUMMER
    WHEN E.INSNR > 0 THEN CT20.KUNDNUMMER
    WHEN E.AEVERKNR = 0 AND E.INSNR = 0 THEN a.KUNDNUMMER
END AS KUNDNUMMER,
CASE 
    WHEN E.AEVERKNR > 0 THEN CT.PNR
    WHEN E.INSNR > 0 THEN CT20.PNR
    WHEN E.AEVERKNR = 0 AND E.INSNR = 0 THEN H.PNR
END AS PNR,      
INV.HAND AS INVESTIGATIONSCASEWORKERID,       
SERV.HAND AS SERVICESCASEWORKERID,      
AKPER.HAND AS EXECUTIONSCASEWORKERID,            
PROF3.NAMN AS INVESTIGATIONSCASEWORKER,       
PROF2.NAMN AS SERVICESCASEWORKER,      
PROF1.NAMN AS EXECUTIONSCASEWORKER,      
J01_SERV.NAMN AS SERVICESORG,      
J01_INV.NAMN AS INVESTIGATIONSORG,      
J01_AKPER.NAMN AS EXECUTIONSORG,      
L20.ORGID AS SERVICESORGID,      
L20.ORGENHNR AS SERVICESORGUNITID,        
INV.ORGID AS INVESTIGATIONSORGID,      
INV.ORGENHNR AS INVESTIGATIONSORGUNITID,      
AKPER.ORGID AS EXECUTIONSORGID,      
AKPER.ORGENHNR AS EXECUTIONSORGUNITID,      
STAT.XNUM7 AS STATISTICINDIVIDUALID,      
STAT1.KLARTEXT AS STATISTICINDIVIDUAL,      
STAT.XNUM8 AS STATISTICQUANTITYID,      
STAT2.KLARTEXT AS STATISTICQUANTITY,      
STAT.XNUM4 AS LAWSECTIONID,      
STAT3.KLARTEXT AS LAWSECTION,      
RES.KOD AS STATISTICAPPLICANTREASONID,      
RES.KLARTEXT AS STATISTICAPPLICANTREASON,      
RES.XTXT1 AS STATISTICAPPLICANTREASONTITLE,      
RES.XTXT2 AS STATISTICAPPLICANTREASONCODE,      
RES1.KOD AS STATISTICCOAPPLICANTREASONID,      
RES1.KLARTEXT AS STATISTICCOAPPLICANTREASON,      
RES1.XTXT1 AS STATISTICCOAPPLICANTREASONTITLE,      
RES1.XTXT2 AS STATISTICCOAPPLICANTREASONCODE,      
GAR.KLARTEXT AS VARDNHAV_T             
FROM LD0V0ET E     
INNER JOIN LD0V0I VI ON E.BESFATT = VI.HAND    
OUTER APPLY (
    SELECT A.KUNDNUMMER, A.PNR
    FROM LD0P0A A 
    JOIN LD0P0C C ON C.AKTPNR = A.PNR
    WHERE C.AEVERKNR = E.AEVERKNR
) CT
OUTER APPLY (
    SELECT A.KUNDNUMMER, A.PNR
    FROM LD0P0A A 
    JOIN LD0P0C20 C20 ON C20.AKTPNR = A.PNR
    WHERE C20.INSNR = E.INSNR
) CT20
LEFT JOIN LD0P0H H ON H.BESLUTNR = E.BESLUTNR
LEFT JOIN LD0P0A A ON A.PNR = H.PNR
LEFT OUTER JOIN LD0P0Q01 Q01 ON Q01.KODTYP = 'BESLORSAK' AND E.ORSAK = Q01.KOD      
LEFT OUTER JOIN LD0P0Q01 Q01_2 ON Q01_2.KODTYP = 'BESLUT' AND E.BESLUT = Q01_2.KOD      
LEFT OUTER JOIN LD0P0Q01 Q01_3 ON Q01_3.KODTYP = 'BESLORSAK' AND E.ORSAKMED = Q01_3.KOD      
LEFT OUTER JOIN LD0P0Q01  Q01_4 ON Q01_4.KODTYP = 'BESLUTTYP' AND E.BESLTYP = Q01_4.KOD      
LEFT OUTER JOIN LD0VYC20T    AS L20   ON E.INSNR =L20.INSNR      
LEFT OUTER JOIN LD0VYC20T     AS SERV   ON SERV.INSNR = E.INSNR      
LEFT OUTER JOIN LD0VYCT     AS INV   ON  E.AEVERKNR= INV.AEVERKNR AND INV.TMARK = 0      
LEFT OUTER JOIN LD0VYCT AS AKPER  ON E.AEVERKNR = AKPER.AEVERKNR AND AKPER.TMARK = 1      
LEFT OUTER JOIN LD0V0I     AS PROF3  ON PROF3.HAND = INV.HAND      
LEFT OUTER JOIN LD0V0I     AS PROF2  ON PROF2.HAND = L20.HAND      
LEFT OUTER JOIN LD0V0I     AS PROF1  ON PROF1.Hand = AKPER.HAND      
LEFT OUTER JOIN LD0P0J01    AS J01_SERV  ON J01_SERV.ORGENHNR = L20.ORGENHNR AND J01_SERV.ORGID = L20.ORGID      
LEFT OUTER JOIN LD0V0J01    AS J01_INV  ON J01_INV.ORGENHNR =INV.ORGENHNR AND J01_INV.ORGID = INV.ORGID      
LEFT OUTER JOIN LD0V0J01    AS J01_AKPER  ON J01_AKPER.ORGENHNR =AKPER.ORGENHNR AND J01_AKPER.ORGID = AKPER.ORGID       
LEFT OUTER JOIN LD0V0Q04    AS STATGRPVAL ON STATGRPVAL.KODTYP = 'BESLUT' AND E.BESLUT = STATGRPVAL.KOD      
LEFT OUTER JOIN LD0V0Q01T    AS T10   ON T10.KODTYP = 'STATGRUPP'   AND T10.XTXT1 = 'BESLUT'   AND T10.KOD = STATGRPVAL.STATGRP1             
LEFT OUTER JOIN LD0V0Q01T    AS T11   ON T11.KODTYP = 'STATGRUPP'   AND T11.XTXT1 = 'BESLUT'   AND T11.KOD = STATGRPVAL.STATGRP2      
LEFT OUTER JOIN LD0V0Q01T    AS T12   ON T12.KODTYP = 'STATGRUPP'   AND T12.XTXT1 = 'BESLUT'   AND T12.KOD = STATGRPVAL.STATGRP3      
LEFT OUTER JOIN LD0V0Q01T    AS T13   ON T13.KODTYP = 'STATGRUPP'   AND T13.XTXT1 = 'BESLUT'   AND T13.KOD = STATGRPVAL.STATGRP4      
LEFT OUTER JOIN LD0V0Q01T    AS T14   ON T14.KODTYP = 'STATGRUPP'   AND T14.XTXT1 = 'BESLUT'   AND T14.KOD = STATGRPVAL.STATGRP5      
LEFT OUTER JOIN LD0V0Q01T    AS STAT   ON STAT.KODTYP = 'BESLUT' AND STAT.KOD = E.BESLUT      
LEFT OUTER JOIN LD0V0Q01T    AS STAT1   ON STAT1.KOD = STAT.XNUM7 AND STAT1.KODTYP = 'SCBKODBESL'      
LEFT OUTER JOIN LD0V0Q01T    AS STAT2   ON STAT2.KOD = STAT.XNUM8 AND STAT2.KODTYP = 'SCBKODBESM'      
LEFT OUTER JOIN LD0V0Q01T    AS STAT3   ON STAT3.KOD = STAT.XNUM4 AND STAT3.KODTYP = 'LAGRUM'      
LEFT OUTER JOIN LD0V0Q01T    AS RES  ON RES.KOD = Q01.XNUM1 AND RES.KODTYP = 'SCBKODORS'      
LEFT OUTER JOIN LD0V0Q01T    AS RES1  ON RES1.KOD = Q01_3.XNUM1 AND RES1.KODTYP = 'SCBKODORS'      
LEFT OUTER JOIN LD0V0Q01T    AS GAR  ON GAR.KOD = E.VARDNHAV AND GAR.KODTYP = 'VARDHAVARE'
GO
GRANT ALL ON LD0ApiDecision TO LD0ROANV
Go
