IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0SearchResourceAllocations')
BEGIN
    DROP VIEW LD0VAA0SearchResourceAllocations
END
GO
CREATE VIEW LD0VAA0SearchResourceAllocations AS   
SELECT  N70.*,  
  A.NAMN AS PERSONNAME,  
  A.PNR,  
  A.ADRESS,  
  A.FODDAT,  
  A.POSTNR,  
  A.ORT,  
  A.COADR AS PERSONCOADDRESS,  
  A.KON,  
  A.GRUPP,  
  A.KUNDNUMMER,  
  I_1.HAND AS CASEWORKERID,  
  I_1.NAMN AS CASEWORKER,  
  I_2.HAND AS COCASEWORKERID,  
  I_2.NAMN AS COCASEWORKER,  
  C20.ORGID AS INSORGID,  
  C20.ORGENHNR AS INSORGENHNR,  
  J01.NAMN AS INSORGNAMN,  
  C20.FOMDAT AS INSFOMDAT,  
  C20.TOMDAT AS INSTOMDAT,  
  L01.RESURSTYP,  
  L.RESURSNR,  
  L.RESURSKAT,  
  L.RESURSBEN,  
  L.PNR AS RESURSPNR,  
  E_1.DATUM AS STARTBESLDATE,  
  E_2.DATUM AS STOPBESLDATE,  
  Q01_1.KLARTEXT AS HEMTYP_T,  
  Q01_2.KLARTEXT AS RESURSTYP_T,  
  Q01_3.KLARTEXT AS RESURSKAT_T,  
  Q01_4.KLARTEXT AS STARTBESLTYP_T,  
  Q01_5.KLARTEXT AS STOPBESLTYP_T,  
        Q01_6.KLARTEXT AS INSTYP_T,
  L.COMPANY		
FROM LD0V0N70 N70   
 INNER JOIN LD0VYC20 C20 ON C20.INSNR = N70.INSNR   
 JOIN LD0V0A A ON A.PNR = C20.AKTPNR  
 LEFT OUTER JOIN LD0V0J01 J01 ON J01.ORGID = C20.ORGID AND J01.ORGENHNR = C20.ORGENHNR   
 LEFT OUTER JOIN LD0V0I I_1 ON C20.HAND = I_1.HAND   
 LEFT OUTER JOIN LD0V0I I_2 ON C20.MEDHAND = I_2.HAND   
 LEFT OUTER JOIN LD0V0L01 L01 ON N70.RESURSSOMNR = L01.RESURSSOMNR   
 LEFT OUTER JOIN LD0V0L L ON L.RESURSNR = L01.RESURSNR  
 LEFT OUTER JOIN LD0VYE E_1 ON E_1.BESLUTNR = N70.BESLUTNRSTART  
 LEFT OUTER JOIN LD0VYE E_2 ON E_2.BESLUTNR = N70.BESLUTNRSTOP  
 LEFT OUTER JOIN LD0V0Q01 Q01_1 ON Q01_1.KODTYP = 'HEMTYP' AND Q01_1.KOD = N70.HEMTYP  
 LEFT OUTER JOIN LD0V0Q01 Q01_2  ON Q01_2.KODTYP = 'RESURSTYP' AND Q01_2.KOD = L01.RESURSTYP  
 LEFT OUTER JOIN LD0V0Q01 Q01_3  ON Q01_3.KODTYP = 'RESURSKAT' AND Q01_3.KOD = L.RESURSKAT  
 LEFT OUTER JOIN LD0V0Q01 Q01_4  ON Q01_4.KODTYP = 'BESLUT' AND Q01_4.KOD = E_1.BESLUT  
 LEFT OUTER JOIN LD0V0Q01 Q01_5  ON Q01_5.KODTYP = 'BESLUT' AND Q01_5.KOD = E_2.BESLUT  
 LEFT OUTER JOIN LD0V0Q01 Q01_6  ON Q01_6.KODTYP = 'INSATS' AND Q01_6.KOD = C20.INSTYP  
GO
GRANT ALL ON LD0VAA0SearchResourceAllocations TO LD0ROANV
GO