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

CREATE VIEW LD0VAA0SEARCHASSIGNMENTIFO
AS SELECT 
N900.*
,A.NAMN as PERSNAMN
,A.KUNDNUMMER as PERSONKEY
,A.ADRESS as ADRESS
,A.COADR as COADR
,A.POSTNR as POSTNR
,A.ORT as ORT
,A.FODDAT as FODDAT
,A.KON as KON
,A.HEMTEL
,A.ARBTEL
,A.MOBIL
,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_3.KLARTEXT RESTYPGRP_T
,C.ORGID AS INV_ORGANIZATIONID
,C.ORGENHNR AS INV_ORGANIZATIONUNITID
,C.HAND AS INV_CASEWORKER
,C.MEDHAND AS INV_COCASEWORKER
,J01_INV.NAMN AS INV_ORGNAMN_T
,VI_INV1.NAMN AS INV_CASEWORKERNAME_T
,VI_INV2.NAMN AS INV_COCASEWORKERNAME_T
,C20.ORGID AS SERV_ORGANIZATIONID
,C20.ORGENHNR AS SERV_ORGANIZATIONUNITID
,C20.HAND AS SERV_CASEWORKER
,C20.MEDHAND AS SERV_COCASEWORKER
,J01_SERV.NAMN AS SERV_ORGNAMN_T
,VI_SERV1.NAMN AS SERV_CASEWORKERNAME_T
,VI_SERV2.NAMN AS SERV_COCASEWORKERNAME_T
FROM LD0V0N900 N900
JOIN LD0VYA A ON A.PNR = N900.PNR
INNER 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_3 ON Q01_3.KODTYP = 'RESTYPGRP' AND Q01_3.KOD = N900.RESTYPGRP
LEFT OUTER JOIN LD0VYC 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 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 LD0P0J01 AS J01_SERV ON J01_SERV.ORGENHNR = C20.ORGENHNR AND J01_SERV.ORGID = C20.ORGID
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 LD0VAA0SEARCHASSIGNMENTIFO TO LD0ROANV
GO
IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0SEARCHLMASSIGNMENT')
BEGIN
DROP VIEW LD0VAA0SEARCHLMASSIGNMENT
END
GO

CREATE VIEW LD0VAA0SEARCHLMASSIGNMENT
AS SELECT 
U90.*
,U11.FROMDAT AS ANVISSTARTDAT
,U11.TOMDAT AS ANVISAVSLUTDAT
,U11.HANDLAGGARE AS ANVISHAND
,U11.ANVISORGINTERN AS ANVISORGENHNR
,A.NAMN as PERSNAMN
,A.KUNDNUMMER as PERSONKEY
,A.ADRESS as ADRESS
,A.COADR as COADR
,A.POSTNR as POSTNR
,A.ORT as ORT
,A.FODDAT as FODDAT
,A.KON as KON
,A.HEMTEL
,A.ARBTEL
,A.MOBIL
,I1.NAMN as BESTHAND_T
,I2.NAMN as MOTHAND_T
,I3.NAMN as ANVISHAND_T
,J01.NAMN as BESTORGNAMN_T
,J02.NAMN as MOTORGNAMN_T
,J03.NAMN as ANVISORGENHNR_T
,Q01.KLARTEXT as ANVISORGTYP_T
,Q01.XNUM5 as ANVISORGTYP_XNUM5
,Q02.KLARTEXT as ANVISORGNAMN_T
,Q03.KLARTEXT as UTANARB_T
, CASE 
  WHEN U90.ATERTAGDAT = '' AND U90.AVBOJDAT = '' and U90.MOTDAT = ''  THEN 'Nytt' 
  WHEN U90.ATERTAGDAT = '' AND u90.AVBOJDAT = '' and U90.MOTDAT != '' and U90.INSKRIVNINGNR = 0 THEN 'Mottaget' 
  WHEN U90.ATERTAGDAT = '' AND u90.AVBOJDAT = '' and U90.MOTDAT != '' and U11.FROMDAT != ''  and U11.TOMDAT = '99999999' THEN 'Pågående' 
  WHEN U90.ATERTAGDAT = '' AND u90.AVBOJDAT = '' and U90.MOTDAT != '' and U11.TOMDAT != '99999999' THEN 'Avslutat' 
  WHEN U90.AVBOJDAT != ''  THEN 'Avböjt' 
  WHEN U90.ATERTAGDAT != ''  THEN 'Återtaget' 
ELSE 'Okänd status' END as STATUS
,C.ORGID AS INV_ORGANIZATIONID
,C.ORGENHNR AS INV_ORGANIZATIONUNITID
,C.HAND AS INV_CASEWORKER
,C.MEDHAND AS INV_COCASEWORKER
,J01_INV.NAMN AS INV_ORGNAMN_T
,VI_INV1.NAMN AS INV_CASEWORKERNAME_T
,VI_INV2.NAMN AS INV_COCASEWORKERNAME_T
,C20.ORGID AS SERV_ORGANIZATIONID
,C20.ORGENHNR AS SERV_ORGANIZATIONUNITID
,C20.HAND AS SERV_CASEWORKER
,C20.MEDHAND AS SERV_COCASEWORKER
,J01_SERV.NAMN AS SERV_ORGNAMN_T
,VI_SERV1.NAMN AS SERV_CASEWORKERNAME_T
,VI_SERV2.NAMN AS SERV_COCASEWORKERNAME_T
FROM LD0V0U90 U90 WITH(NOLOCK)
INNER JOIN LD0VYA A WITH(NOLOCK) ON A.PNR = U90.PNR
LEFT OUTER JOIN LD0V0U11 U11 WITH(NOLOCK) ON U11.ANVISANDENR = U90.ANVISANDENR AND U90.ANVISANDENR > 0
LEFT OUTER JOIN LD0V0I AS I1 WITH(NOLOCK) ON I1.HAND = U90.BESTHAND
LEFT OUTER JOIN LD0V0I AS I2 WITH(NOLOCK) ON I2.HAND = U90.MOTHAND
LEFT OUTER JOIN LD0V0I AS I3 WITH(NOLOCK) ON I3.HAND = U11.HANDLAGGARE
LEFT OUTER JOIN LD0V0J01 AS J01 WITH(NOLOCK) ON J01.ORGID = U90.BESTORGID AND J01.ORGENHNR = U90.BESTORGENHNR
LEFT OUTER JOIN LD0V0J01 AS J02 WITH(NOLOCK) ON J02.ORGID = U90.MOTORGID AND J02.ORGENHNR = U90.MOTORGENHNR
LEFT OUTER JOIN LD0V0J01 AS J03 WITH(NOLOCK) ON J03.ORGID = U11.ORGID AND J03.ORGENHNR = U11.ANVISORGINTERN
LEFT OUTER JOIN LD0V0Q01 AS Q01 WITH(NOLOCK) ON Q01.KODTYP = 'LNANVISTYP' AND Q01.KOD = U90.ANVISORGTYP 
LEFT OUTER JOIN LD0V0Q01 AS Q02 WITH(NOLOCK) ON Q02.KODTYP = 'LNANVISORG' AND Q02.KOD = U90.ORGEXTERN 
LEFT OUTER JOIN LD0V0Q01 AS Q03 WITH(NOLOCK) ON Q03.KODTYP = 'LNUTANARB' AND Q03.KOD = U90.UTANARB
LEFT OUTER JOIN LD0VYE E WITH(NOLOCK) ON E.BESLUTNR = U90.BESLUTNR
LEFT OUTER JOIN LD0VYC AS C WITH(NOLOCK) ON C.AEVERKNR = E.AEVERKNR
LEFT OUTER JOIN LD0V0J01 AS J01_INV WITH(NOLOCK) ON J01_INV.ORGENHNR = C.ORGENHNR AND J01_INV.ORGID = C.ORGID
LEFT OUTER JOIN LD0V0I AS VI_INV1 WITH(NOLOCK) ON VI_INV1.HAND = C.HAND
LEFT OUTER JOIN LD0V0I AS VI_INV2 WITH(NOLOCK) ON VI_INV2.HAND = C.MEDHAND
LEFT OUTER JOIN LD0VYC20 AS C20 WITH(NOLOCK) ON C20.INSNR = E.INSNR
LEFT OUTER JOIN LD0P0J01 AS J01_SERV WITH(NOLOCK) ON J01_SERV.ORGENHNR = C20.ORGENHNR AND J01_SERV.ORGID = C20.ORGID
LEFT OUTER JOIN LD0V0I AS VI_SERV1 WITH(NOLOCK) ON VI_SERV1.HAND = C20.HAND
LEFT OUTER JOIN LD0V0I AS VI_SERV2 WITH(NOLOCK) ON VI_SERV2.HAND = C20.MEDHAND
GO

GRANT ALL ON LD0VAA0SEARCHLMASSIGNMENT TO LD0ROANV
GO

