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

CREATE VIEW LD0VAAYLMASSIGNMENT AS
 SELECT 
U90.LNUPPDRAGNR
,U90.PNR
,U90.INSKRIVNINGNR
,U90.BESLUTNR
,U90.BESTHAND
,U90.BESTORGID
,U90.BESTORGENHNR
,U90.BESTDAT
,U90.FOMDAT
,U90.TOMDAT
,U90.MOTHAND
,U90.MOTORGID
,U90.MOTORGENHNR
,U90.MOTDAT
,U90.ATERTAGDAT
,U90.AVBOJDAT
,U90.UPDDAT
,U90.UPDSIGN
,U90.UTANARB
,U90.UTANIFO
,U90.ANVISANDENR
,U90.ANVISORGTYP
,U90.ORGEXTERN
,U90.BESTINSKRIVNINGNR
,U90.ANVISNINGGALLRAD
,U11.FROMDAT AS ANVISSTARTDAT
,U11.TOMDAT AS ANVISAVSLUTDAT
,A.NAMN as PERSNAMN
,A.KUNDNUMMER as PERSONKEY
,I1.NAMN as BESTHAND_T
,I2.NAMN as MOTHAND_T
,J01.NAMN as BESTORGNAMN_T
,J02.NAMN as MOTORGNAMN_T
,Q01.KLARTEXT as ANVISORGTYP_T
,Q01.XNUM5 as ANVISORGTYP_XNUM5
,Q02.KLARTEXT as ANVISORGNAMN_T
,Q03.KLARTEXT as UTANARB_T
, CASE 
WHEN U90.ANVISNINGGALLRAD = 1 
 THEN 'Anvisning gallrad' 
WHEN U90.ATERTAGDAT = '' AND U90.AVBOJDAT = '' AND U90.MOTDAT = '' 
 THEN 'Nytt'
WHEN U90.ATERTAGDAT = '' AND U90.AVBOJDAT = '' AND U90.MOTDAT != '' AND(U11.FROMDAT = '99999999' OR U11.FROMDAT IS NULL OR TRY_CONVERT(date, NULLIF(U11.FROMDAT, ''), 112) > CAST(GETDATE() AS date)) 
 THEN 'Mottaget'
WHEN U90.ATERTAGDAT = '' AND U90.AVBOJDAT = '' AND U90.MOTDAT != '' AND U11.FROMDAT != '' AND (U11.TOMDAT = '' OR TRY_CONVERT(date, NULLIF(U11.TOMDAT, ''), 112) IS NULL OR TRY_CONVERT(date, NULLIF(U11.TOMDAT, ''), 112) > CAST(GETDATE() AS date)) 
 THEN 'Pågående' 
WHEN U90.ATERTAGDAT = '' AND U90.AVBOJDAT = '' AND U90.MOTDAT != '' AND TRY_CONVERT(date, NULLIF(U11.TOMDAT, ''), 112) IS NOT NULL AND TRY_CONVERT(date, NULLIF(U11.TOMDAT, ''), 112) < CAST(GETDATE() AS date) 
 THEN 'Avslutat' 
WHEN U90.AVBOJDAT != '' 
 THEN 'Avböjt' 
WHEN U90.ATERTAGDAT != '' 
 THEN 'Återtagit' 
ELSE 'Okänd status' END AS STATUS
FROM LD0P0U90 U90
LEFT OUTER JOIN LD0P0U11 U11 ON U11.ANVISANDENR = U90.ANVISANDENR AND U90.ANVISANDENR > 0
LEFT OUTER JOIN LD0P0A A ON A.PNR = U90.PNR
LEFT OUTER JOIN LD0P0I AS I1 ON I1.HAND = U90.BESTHAND
LEFT OUTER JOIN LD0P0I AS I2 ON I2.HAND = U90.MOTHAND
LEFT OUTER JOIN LD0P0J01 AS J01 ON J01.ORGID = U90.BESTORGID AND J01.ORGENHNR = U90.BESTORGENHNR
LEFT OUTER JOIN LD0P0J01 AS J02 ON J02.ORGID = U90.MOTORGID AND J02.ORGENHNR = U90.MOTORGENHNR
LEFT OUTER JOIN ld0v0q01 AS Q01 ON Q01.KODTYP = 'LNANVISTYP' AND Q01.KOD = U90.ANVISORGTYP 
LEFT OUTER JOIN ld0v0q01 AS Q02 ON Q02.KODTYP = 'LNANVISORG' AND Q02.KOD = U90.ORGEXTERN 
LEFT OUTER JOIN ld0v0q01 AS Q03 ON Q03.KODTYP = 'LNUTANARB' AND Q03.KOD = U90.UTANARB 
GO
