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

CREATE VIEW LD0VAA0RefugeePerson AS
  	SELECT
        A700.FLYKTNR,
	A700.PNR,
	A700.ANKKOMU,
	A700.DOSSIERNR,
	A700.ANKFORL,
	A700.ANSUAT,
	A700.ANKSV,
	A700.UATDAT,
	A700.MOTDAT,
	A700.MOTKOMU,
	A700.INFDAT1,
	A700.UTFDAT1,
	A700.UTFKOMU1,
	A700.INFDAT2,
	A700.UTFDAT2,
	A700.UTFKOMU2,
	A700.AVAKTDAT,
	A700.SFIAVSLUT,
	A700.ENDSTATSBIDR,
	A700.AVAKTORSAK,
	A700.KLASSNING,
	A700.GRUND,
	A700.KATEGORI,
	A700.FORSTABESOK,
	A700.ORGID,
	A700.ORGENHNR,
	A700.HOUSINGSTATUS,
	A700.RHAPPDATE,
	A700.RHAPPSTATUS,
	A700.UPDDAT,
	A700.UPDSIGN,
    ISNULL(A.NAMN, ' ') AS NAMN,
    ISNULL(Q01.KLARTEXT,' ') AS MOTTAGNINGSKOMMUN_T,
    ISNULL(Q02.KLARTEXT,' ') AS UTFLYTTNINGSKOMMUN1_T,
    ISNULL(Q03.KLARTEXT, ' ') AS UTFLYTTNINGSKOMMUN2_T,
    ISNULL(Q04.KLARTEXT, ' ') AS AVAKTORSAK_T,
    ISNULL(Q05.KLARTEXT, ' ') AS KLASSNING_T,
    ISNULL(Q06.KLARTEXT, ' ') AS GRUND_T,
    ISNULL(Q07.KLARTEXT, ' ') AS KATEGORI_T,
    ISNULL(J01.NAMN, ' ')     AS  ORGENH_T
     FROM LD0V0A700 A700
LEFT OUTER JOIN LD0V0A A ON A700.PNR = A.PNR
LEFT OUTER JOIN LD0V0Q01 Q01 ON Q01.KOD = A700.MOTKOMU AND Q01.KODTYP = 'KOMU'
LEFT OUTER JOIN LD0V0Q01 Q02 ON Q02.KOD = A700.UTFKOMU1 AND Q02.KODTYP = 'KOMU'
LEFT OUTER JOIN LD0V0Q01 Q03 ON Q03.KOD = A700.UTFKOMU2 AND Q03.KODTYP = 'KOMU'
LEFT OUTER JOIN LD0V0Q01 Q04 ON Q04.KOD = A700.AVAKTORSAK AND Q04.KODTYP = 'FLYAVORSAK'
LEFT OUTER JOIN LD0V0Q01 Q05 ON Q05.KOD = A700.KLASSNING AND Q05.KODTYP = 'FLYKLASSN'
LEFT OUTER JOIN LD0V0Q01 Q06 ON Q06.KOD = A700.GRUND AND Q06.KODTYP = 'FLYGRUND'
LEFT OUTER JOIN LD0V0Q01 Q07 ON Q07.KOD = A700.KATEGORI AND Q07.KODTYP = 'FLYKTKAT'
LEFT OUTER JOIN LD0V0J01 AS J01 ON J01.ORGID = A700.ORGID AND J01.ORGENHNR = A700.ORGENHNR
;

