Hosp Kind Request

   SELECT NVL(d1.HOSP_KIND,
              CASE
                WHEN v1.ID IS NOT NULL THEN D_PKG_VISIT_FIELDS.GET_VALUE(v1.ID, :LPU, 'HOSP_KIND')
                ELSE NULL
              END) AS HOSP_KIND
     FROM D_V_DIRECTION_SERVICES ds
left join D_V_VISITS v ON v.PID = ds.ID
left join D_V_DIRECTIONS d1 ON d1.REG_VISIT_ID = v1.ID
left join D_V_DIRECTION_SERVICES ds1 ON ds1.DISEASECASE = ds.DISEASECASE
                                    AND TRIM(UPPER(ds1.SERVICE)) = 'S002'
left join D_V_VISITS v1 ON v1.PID = ds1.ID;