DROP VIEW LD0VJU10
;
CREATE VIEW LD0VJU10
AS
SELECT

U10.INSKRIVNINGSNR,PERSONNR,INLEDDAT,

CASE WHEN AVSLUTDAT = '99999999' THEN ' ' ELSE AVSLUTDAT END AS AVSLUTDAT,

U10.UPDDAT, U10.UPDSIGN, FORLANGDINTRO,

CASE WHEN(Q07.KLARTEXT IS NULL) THEN ' ' ELSE Q07.KLARTEXT END AS TYPAVSLUT,
CASE WHEN(Q08.KLARTEXT IS NULL) THEN ' ' ELSE Q08.KLARTEXT END AS AVSLUTORS,
CASE WHEN(Q09.KLARTEXT IS NULL) THEN ' ' ELSE Q09.KLARTEXT END AS AVSLUTKOMMUN,
CASE WHEN(Q10.KLARTEXT IS NULL) THEN ' ' ELSE Q10.KLARTEXT END AS AVSLUTLAND,

KOMMUNUTLAND, U10.ORGID, U10.ORGENHNR,

CASE WHEN(J01.NAMN IS NULL) THEN ' ' ELSE J01.NAMN END AS ORGENHNRTXT,
CASE WHEN(Q01.KLARTEXT IS NULL) THEN ' ' ELSE Q01.KLARTEXT END AS ANVISTYP,
CASE WHEN(Q02.KLARTEXT IS NULL) THEN ' ' ELSE Q02.KLARTEXT END AS ANVISORG,

CASE WHEN(Q03.KLARTEXT IS NULL) THEN ' ' ELSE Q03.KLARTEXT END AS ANSVARIG1,
CASE WHEN(Q04.KLARTEXT IS NULL) THEN ' ' ELSE Q04.KLARTEXT END AS ANSVARIG2,
CASE WHEN(Q05.KLARTEXT IS NULL) THEN ' ' ELSE Q05.KLARTEXT END AS ANSVARIG3,
CASE WHEN(Q06.KLARTEXT IS NULL) THEN ' ' ELSE Q06.KLARTEXT END AS ANSVARIG4,

CASE WHEN(I1.HAND IS NULL) THEN ' ' ELSE I1.HAND END AS HAND1,
CASE WHEN(I2.HAND IS NULL) THEN ' ' ELSE I2.HAND END AS HAND2,
CASE WHEN(I3.HAND IS NULL) THEN ' ' ELSE I3.HAND END AS HAND3,
CASE WHEN(I4.HAND IS NULL) THEN ' ' ELSE I4.HAND END AS HAND4,


CASE WHEN(I1.NAMN IS NULL) THEN ' ' ELSE I1.NAMN END AS HAND1TXT,
CASE WHEN(I2.NAMN IS NULL) THEN ' ' ELSE I2.NAMN END AS HAND2TXT,
CASE WHEN(I3.NAMN IS NULL) THEN ' ' ELSE I3.NAMN END AS HAND3TXT,
CASE WHEN(I4.NAMN IS NULL) THEN ' ' ELSE I4.NAMN END AS HAND4TXT, 

YRKEAVSLUT 

FROM LD0VLU10 U10 

LEFT OUTER JOIN LD0P0I I1 ON I1.HAND = 
(select HANDLAGGARE from
(select U12.HANDLAGGARE, ROW_NUMBER() over (order by ANSVARIGNR DESC) as rn from LD0P0U12 U12 where u12.INSKRIVNINGSNR = U10.INSKRIVNINGSNR)
rU12 where rU12.rn = 1) 

LEFT OUTER JOIN LD0P0I I2 ON I2.HAND = 
(select HANDLAGGARE from
(select U12.HANDLAGGARE, ROW_NUMBER() over (order by ANSVARIGNR DESC) as rn from LD0P0U12 U12 where u12.INSKRIVNINGSNR = U10.INSKRIVNINGSNR)
rU12 where rU12.rn = 2) 

LEFT OUTER JOIN LD0P0I I3 ON I3.HAND = 
(select HANDLAGGARE from
(select U12.HANDLAGGARE, ROW_NUMBER() over (order by ANSVARIGNR DESC) as rn from LD0P0U12 U12 where u12.INSKRIVNINGSNR = U10.INSKRIVNINGSNR)
rU12 where rU12.rn = 3) 

LEFT OUTER JOIN LD0P0I I4 ON I4.HAND = 
(select HANDLAGGARE from
(select U12.HANDLAGGARE, ROW_NUMBER() over (order by ANSVARIGNR DESC) as rn from LD0P0U12 U12 where u12.INSKRIVNINGSNR = U10.INSKRIVNINGSNR)
rU12 where rU12.rn = 4) 

LEFT OUTER JOIN LD0P0J01 J01 ON J01.ORGID = U10.ORGID AND J01.ORGENHNR = U10.ORGENHNR 
LEFT OUTER JOIN LD0P0Q01 Q01 ON Q01.KODTYP = 'LNANVISTYP' AND Q01.KOD = U10.ANVISTYP 
LEFT OUTER JOIN LD0P0Q01 Q02 ON Q02.KODTYP = 'LNANVISORG' AND Q02.KOD = U10.ANVISORG 

LEFT OUTER JOIN LD0P0Q01 Q03 ON Q03.KODTYP = 'LNANSVFOR' AND Q03.KOD = 
(select ANSVARIGFOR from
(select U12.ANSVARIGFOR, ROW_NUMBER() over (order by ANSVARIGNR DESC) as rn from LD0P0U12 U12 where u12.INSKRIVNINGSNR = U10.INSKRIVNINGSNR)
rU12 where rU12.rn = 1)

LEFT OUTER JOIN LD0P0Q01 Q04 ON Q04.KODTYP = 'LNANSVFOR' AND Q04.KOD = 
(select ANSVARIGFOR from
(select U12.ANSVARIGFOR, ROW_NUMBER() over (order by ANSVARIGNR DESC) as rn from LD0P0U12 U12 where u12.INSKRIVNINGSNR = U10.INSKRIVNINGSNR)
rU12 where rU12.rn = 2)

LEFT OUTER JOIN LD0P0Q01 Q05 ON Q05.KODTYP = 'LNANSVFOR' AND Q05.KOD = 
(select ANSVARIGFOR from
(select U12.ANSVARIGFOR, ROW_NUMBER() over (order by ANSVARIGNR DESC) as rn from LD0P0U12 U12 where u12.INSKRIVNINGSNR = U10.INSKRIVNINGSNR)
rU12 where rU12.rn = 3)

LEFT OUTER JOIN LD0P0Q01 Q06 ON Q06.KODTYP = 'LNANSVFOR' AND Q06.KOD = 
(select ANSVARIGFOR from
(select U12.ANSVARIGFOR, ROW_NUMBER() over (order by ANSVARIGNR DESC) as rn from LD0P0U12 U12 where u12.INSKRIVNINGSNR = U10.INSKRIVNINGSNR)
rU12 where rU12.rn = 4)

LEFT OUTER JOIN LD0P0Q01 Q07 ON Q07.KODTYP = 'LNTYPAVSL' AND Q07.KOD = U10.TYPAVSLUT 
LEFT OUTER JOIN LD0P0Q01 Q08 ON Q08.KODTYP = 'LNAVSLORS' AND Q08.KOD = U10.AVSLUTORS 
LEFT OUTER JOIN LD0P0Q01 Q09 ON Q09.KODTYP = 'KOMU' AND Q09.KOD = U10.AVSLUTKOMMUN 
LEFT OUTER JOIN LD0P0Q01 Q10 ON Q10.KODTYP = 'LAND' AND Q10.KOD = U10.AVSLUTLAND 
LEFT OUTER JOIN LD0P0Q01 Q11 ON Q11.KODTYP = 'LNINSKTYP' AND Q11.KOD = U10.INSKRTYP 
;