IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME = 'LD0ApiResourceAllocation')
BEGIN
DROP VIEW LD0ApiResourceAllocation
END
GO
CREATE VIEW LD0ApiResourceAllocation    
AS     
SELECT N70.*,        
N70.STARTTIME,        
N70.ENDTIME,        
Q.KLARTEXT HEMTYP_T,         
L01.RESURSTYP, Q1.KLARTEXT RESURSTYP_T,         
L.RESURSBEN,        
(SELECT DISTINCT KUNDNUMMER FROM LD0P0A A WHERE EXISTS (SELECT * FROM LD0V0C20T C20 WHERE C20.AKTPNR=A.PNR AND C20.INSNR=N70.INSNR)) AS KUNDNUMMER,        
(SELECT DISTINCT PNR FROM LD0P0A A WHERE EXISTS (SELECT * FROM LD0V0C20T C20 WHERE C20.AKTPNR=A.PNR AND C20.INSNR=N70.INSNR)) AS PNR,        
Q01.KLARTEXT AS ResourceCategoryTypeText,        
L0.RESURSSTAT AS ResourceStatus,        
Q02.KLARTEXT AS ResourceStatusText,        
L0.INTEXT AS IsExternalResource,        
L0.AVTAL AS Agreement,  
Q04.KOD AS ResponsibleAuthorityId,  
Q04.KLARTEXT AS ResponsibleAuthority,  
L01.SCBSTAT AS TypeOfPlacementTypeId,        
Q2.KLARTEXT AS TypeOfPlacementTypeText,        
Q01_STATGROUP1.KLARTEXT AS StatGrp1,        
Q01_STATGROUP2.KLARTEXT AS StatGrp2,        
Q01_STATGROUP3.KLARTEXT AS StatGrp3,        
Q01_STATGROUP4.KLARTEXT AS StatGrp4,        
Q01_STATGROUP5.KLARTEXT AS StatGrp5        
FROM LD0P0N70 N70        
LEFT OUTER JOIN LD0V0Q01T Q ON Q.KODTYP = 'HEMTYP' AND Q.KOD = N70.HEMTYP        
LEFT JOIN LD0V0L01 L01 ON L01.RESURSSOMNR = N70.RESURSSOMNR        
LEFT JOIN LD0V0Q01T Q1 ON Q1.KODTYP = 'RESURSTYP' AND Q1.KOD = L01.RESURSTYP           
LEFT JOIN LD0VAA0RESOURCE L ON L.RESURSNR = L01.RESURSNR          
LEFT JOIN LD0V0Q01T Q2 ON Q2.KODTYP = 'PLFORM'  AND Q2.KOD = L01.SCBSTAT        
LEFT JOIN LD0V0L L0 ON L0.RESURSNR = L01.RESURSNR        
LEFT JOIN LD0V0Q01T Q01 ON Q01.KOD = L0.RESURSKAT AND Q01.KODTYP = 'RESURSKAT'  
LEFT JOIN LD0V0Q01T Q02  ON Q02.KODTYP = 'RESURSSTAT' AND Q02.KOD = L0.RESURSSTAT        
LEFT JOIN LD0V0Q01T Q03  ON Q03.KODTYP = 'PLFORM' AND Q03.KOD = L01.SCBSTAT  
LEFT JOIN LD0V0Q01T Q04 ON Q04.KOD = L0.HUVUDTYP AND Q04.KODTYP = 'RESHUVMAN'   
LEFT JOIN LD0V0Q04 Q04_INSATS on Q04_INSATS.KODTYP = 'RESURSTYP' AND Q04_INSATS.KOD = L01.RESURSTYP        
LEFT JOIN LD0V0Q01T Q01_STATGROUP1 on Q01_STATGROUP1.KODTYP = 'STATGRUPP' AND Q01_STATGROUP1.XTXT1 = 'RESURSTYP' AND Q01_STATGROUP1.KOD=Q04_INSATS.STATGRP1          
LEFT JOIN LD0V0Q01T Q01_STATGROUP2 on Q01_STATGROUP2.KODTYP = 'STATGRUPP' AND Q01_STATGROUP2.XTXT1 = 'RESURSTYP' AND Q01_STATGROUP2.KOD=Q04_INSATS.STATGRP2          
LEFT JOIN LD0V0Q01T Q01_STATGROUP3 on Q01_STATGROUP3.KODTYP = 'STATGRUPP' AND Q01_STATGROUP3.XTXT1 = 'RESURSTYP' AND Q01_STATGROUP3.KOD=Q04_INSATS.STATGRP3          
LEFT JOIN LD0V0Q01T Q01_STATGROUP4 on Q01_STATGROUP4.KODTYP = 'STATGRUPP' AND Q01_STATGROUP4.XTXT1 = 'RESURSTYP' AND Q01_STATGROUP4.KOD=Q04_INSATS.STATGRP4          
LEFT JOIN LD0V0Q01T Q01_STATGROUP5 on Q01_STATGROUP5.KODTYP = 'STATGRUPP' AND Q01_STATGROUP5.XTXT1 = 'RESURSTYP' AND Q01_STATGROUP5.KOD=Q04_INSATS.STATGRP5 
GO
GRANT ALL ON LD0ApiResourceAllocation TO LD0ROANV
GO