SELECT DISTINCT
    subprofile1.CMS_LE_SUB_PROFILE_ID,
    subprofile1.LSP_SHORT_NAME,   	
    rel.REL_VALUE,
    (SELECT code.ENTRY_NAME 
                  FROM common_code_category_entry code
                  WHERE code.ENTRY_CODE = rel.REL_VALUE
                 AND code.CATEGORY_CODE = 'ENT_REL' 
    ) AS RELATION_NAME 
FROM sci_le_rel rel, 
     sci_le_sub_profile subprofile,
     sci_le_sub_profile subprofile1  
WHERE rel.CMS_LE_SUB_PROFILE_ID = subprofile.CMS_LE_SUB_PROFILE_ID 
     AND subprofile1.CMS_LE_SUB_PROFILE_ID  = rel.PARENT_SUB_PROFILE_ID
     AND subprofile1.CMS_LE_SUB_PROFILE_ID  <> subprofile.CMS_LE_SUB_PROFILE_ID
     AND rel.STATUS <> 'DELETED' 
    ORDER BY RELATION_NAME ASC

SELECT 'Tuned' FROM dual;