CREATE OR ALTER VIEW LD0VAA0SearchLMAssignmentFlow3
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
	,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 AS HEMTEL
	,A.ARBTEL AS ARBTEL
	,A.MOBIL AS MOBIL
	,I1.NAMN AS BESTHAND_T
	,I2.NAMN AS MOTHAND_T
	,I3.NAMN AS ANVISHAND_T
	,J01.NAMN AS BESTORGNAMN_T
	,J02.NAMN AS MOTTAGORGNAMN_T
	,U10.INLEDDAT AS INLEDDAT
	,U10.ORGID AS U10_ORGID
	,U10.ORGENHNR AS U10_ORGENHNR
	,J03.NAMN AS INSKRIVNINGSORG_T
	,U11.FROMDAT AS ANVISSTARTDAT
	,U11.TOMDAT AS ANVISAVSLUTDAT
	,Q01.KLARTEXT AS ANVISORGNAMN_T
	,Q02.KLARTEXT AS UTANARB_T
	,Q03.KLARTEXT AS ANVISORTYP_T
,CASE 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,
	CASE WHEN U90.FOMDAT <> ' '
			AND U90.TOMDAT <> ' '
			THEN SUBSTRING(U90.FOMDAT, 1, 4) + '-' + SUBSTRING(U90.FOMDAT, 5, 2) + '-' + SUBSTRING(U90.FOMDAT, 7, 2) + ' - ' + SUBSTRING(U90.TOMDAT, 1, 4) + '-' + SUBSTRING(U90.TOMDAT, 5, 2) + '-' + SUBSTRING(U90.TOMDAT, 7, 2)
		WHEN U90.FOMDAT <> ' '
			AND U90.TOMDAT = ' '
			THEN SUBSTRING(U90.FOMDAT, 1, 4) + '-' + SUBSTRING(U90.FOMDAT, 5, 2) + '-'
		ELSE ' '
		END AS BESTPERIOD
FROM LD0V2U90 U90
LEFT OUTER JOIN LD0V0U10 U10 ON U10.INSKRIVNINGSNR = U90.INSKRIVNINGNR
LEFT OUTER JOIN LD0V0U11 U11 ON U11.ANVISANDENR = U90.ANVISANDENR
LEFT OUTER JOIN LD0V0I AS I3 ON I3.HAND = U11.HANDLAGGARE
LEFT OUTER JOIN LD0V0A A ON A.PNR = U90.PNR
LEFT OUTER JOIN LD0V0I AS I1 ON I1.HAND = U90.BESTHAND
LEFT OUTER JOIN LD0V0I AS I2 ON I2.HAND = U90.MOTHAND
--LEFT OUTER JOIN LD0V0I AS I3 ON I3.HAND = U11.HANDLAGGARE
LEFT OUTER JOIN LD0V0J01 AS J01 ON J01.ORGID = U90.BESTORGID
	AND J01.ORGENHNR = U90.BESTORGENHNR
LEFT OUTER JOIN LD0V0J01 AS J02 ON J02.ORGID = U90.MOTORGID
	AND J02.ORGENHNR = U90.MOTORGENHNR
LEFT OUTER JOIN LD0V0J01 AS J03 ON J03.ORGID = U10.ORGID
	AND J03.ORGENHNR = U10.ORGENHNR
LEFT OUTER JOIN LD0V0Q01 AS Q01 ON Q01.KODTYP = 'LNANVISORG'
	AND Q01.KOD = U90.ORGEXTERN
LEFT OUTER JOIN LD0V0Q01 AS Q02 ON Q02.KODTYP = 'LNUTANARB'
	AND Q02.KOD = U10.UTANARB
LEFT OUTER JOIN LD0V0Q01 AS Q03 ON Q03.KODTYP = 'LNANVISTYP'
	AND Q03.KOD = U90.ANVISORGTYP
GO



GRANT ALL ON LD0VAA0SearchLMAssignmentFlow3 TO LD0ROANV
GO
