SELECT A.HRS_PERSON_ID , B.LANGUAGE_CD , B.DESCR , C.NAME_DISPLAY FROM PS_HRS_APPLICANT A , PS_HRS_STS_APP_LVW B , PS_HRS_APP_NM_LNG C WHERE A.STATUS_CODE = B.STATUS_CODE AND A.HRS_PERSON_ID = C.HRS_PERSON_ID AND B.STATUS_AREA = '2' AND B.LANGUAGE_CD = C.LANGUAGE_CD UNION SELECT A.HRS_PERSON_ID , B.LANGUAGE_CD , B.DESCR , C.NAME_DISPLAY FROM PS_HRS_APPLICANT A , PS_HRS_STS_APP_LVW B , PS_HRS_APP_NAMES C WHERE A.STATUS_CODE = B.STATUS_CODE AND A.HRS_PERSON_ID = C.HRS_PERSON_ID AND B.STATUS_AREA = '2' AND NOT EXISTS ( SELECT 'X' FROM PS_HRS_APP_NM_LNG NM WHERE NM.HRS_PERSON_ID = A.HRS_PERSON_ID AND NM.LANGUAGE_CD = B.LANGUAGE_CD) UNION SELECT A.HRS_PERSON_ID , C.LANGUAGE_CD , B.DESCR , C.NAME_DISPLAY FROM PS_HRS_APPLICANT A , PS_HRS_STS_APP_I B , PS_HRS_APP_NM_LNG C WHERE A.STATUS_CODE = B.STATUS_CODE AND A.HRS_PERSON_ID = C.HRS_PERSON_ID AND B.STATUS_AREA = '2' AND NOT EXISTS ( SELECT 'X' FROM PS_HRS_STS_APP_LVW STS WHERE STS.STATUS_CODE = A.STATUS_CODE AND B.STATUS_AREA = '2' AND STS.LANGUAGE_CD = C.LANGUAGE_CD)
|