CREATE OR ALTER VIEW LD0ApiPlans    
AS    
SELECT    
U30.PLANNR AS PLANID,    
U30.INSKRIVNINGSNR AS ENROLLMENTID,    
U30.PLANTYP AS PLANTYPE,    
Q01.KLARTEXT AS PLANTYPETEXT,    
U30.FROMDAT AS STARTDATE,    
U30.TOMDAT AS ENDDATE,    
U30.UPPFDAT AS FOLLOWUPDATE,       
U30.ANSVARIGNR AS RESPONSIBLEID,    
CASE    
    WHEN U30.TOMDAT IS NOT NULL AND U30.TOMDAT<>'99999999' THEN U30.ANSVARIGFOR    
    WHEN U30.TOMDAT IS NULL OR U30.TOMDAT = '99999999' THEN U12.ANSVARIGFOR    
END AS  RESPONSIBILITYTYPEID,    
CASE     
    WHEN U30.TOMDAT IS NOT NULL AND U30.TOMDAT<>'99999999' THEN Q02.KLARTEXT    
    WHEN U30.TOMDAT IS NULL OR U30.TOMDAT = '99999999' THEN Q03.KLARTEXT    
END AS RESPONSIBILITYTYPE,    
CASE     
    WHEN U30.TOMDAT IS NOT NULL AND U30.TOMDAT<>'99999999' THEN U30.ANSVARIGORG    
    WHEN U30.TOMDAT IS NULL OR U30.TOMDAT = '99999999' THEN U12.ANSVARIGORG    
END AS RESPONSIBLEORGANIZATIONID,    
CASE     
    WHEN U30.TOMDAT IS NOT NULL AND U30.TOMDAT<>'99999999' THEN J01.NAMN    
    WHEN U30.TOMDAT IS NULL OR U30.TOMDAT = '99999999' THEN J02.NAMN    
END AS RESPONSIBLEORGANIZATION,    
CASE    
    WHEN U30.TOMDAT IS NOT NULL AND U30.TOMDAT<>'99999999' THEN U30.ANSVARIGHANDL    
    WHEN U30.TOMDAT IS NULL OR U30.TOMDAT = '99999999' THEN U12.HANDLAGGARE    
END AS RESPONSIBLECASEWORKERID,    
CASE     
    WHEN U30.TOMDAT IS NOT NULL AND U30.TOMDAT<>'99999999' THEN H01.NAMN    
    WHEN U30.TOMDAT IS NULL OR U30.TOMDAT = '99999999' THEN I.NAMN    
END AS RESPONSIBLECASEWORKER    
FROM LD0P0U30 U30    
LEFT JOIN LD0P0Q01 Q01 ON  KOD = PLANTYP AND KODTYP='LNPLANTYP'    
LEFT JOIN LD0P0Q01 Q02 ON Q02.KODTYP = 'LNANSVFOR' and Q02.KOD = U30.ANSVARIGFOR    
LEFT JOIN LD0P0J01 J01 ON J01.ORGID = U30.ORGID and U30.ANSVARIGORG=J01.ORGENHNR    
LEFT JOIN LD0P0I H01 ON H01.HAND = U30.ANSVARIGHANDL    
LEFT JOIN LD0P0U12 U12 ON U30.ANSVARIGNR = U12.ANSVARIGNR    
LEFT JOIN LD0P0Q01 Q03  ON U12.ANSVARIGFOR = Q03.KOD AND Q03.KODTYP = 'LNANSVFOR'    
LEFT JOIN LD0P0J01 J02 ON U12.ORGID = J02.ORGID AND U12.ANSVARIGORG = J02.ORGENHNR    
LEFT JOIN LD0P0I I ON U12.HANDLAGGARE = I.HAND
GO
GRANT ALL ON LD0ApiPlans TO LD0ROANV
GO