SELECT O.OFFICER_ID, 'O' as TYPE, O.INTERNAL_ID, O.TITLE, O.LNAME, O.FNAME, O.MNAME, O.SUFFIX, O.USER_ID, O.ACTIVE, O.AGNCY_CD_AGENCY_CODE, AC.AGENCY_DESC FROM OFFICERS O LEFT OUTER JOIN AGENCY_CODES AC ON O.AGNCY_CD_AGENCY_CODE = AC.AGENCY_CODE UNION SELECT E.EJS_EMP_ID, 'E' as TYPE, E.EMPLOYEE_ID, EC.DESCRIPTION AS TITLE, ejs_encryption.decrypt_aes256(E.ENC_LNAME), ejs_encryption.decrypt_aes256(E.ENC_FNAME), ejs_encryption.decrypt_aes256(E.ENC_MNAME), '', E.LOGIN_ID, CASE WHEN AC.CHILD_CODE='A' THEN 'Y' ELSE 'N' END AS ACTIVE, E.AGENCY_CODE, AC.AGENCY_DESC FROM EMPLOYEES E LEFT OUTER JOIN EJS_CODES EC ON E.TITLE_CODE = EC.CODE AND E.TITLE_CODE_TYPE = EC.CODE_TYPE LEFT OUTER JOIN AGENCY_CODES AC ON E.AGENCY_CODE = AC.AGENCY_CODE LEFT OUTER JOIN EJS_CODES_RELATIONS AC ON AC.CHILD_CODE_TYPE='STATUS' AND AC.PARENT_CODE_TYPE=E.ACTIVE_STATUS_CODE_TYPE AND AC.PARENT_CODE=E.ACTIVE_STATUS_CODE WHERE E.MASTER_EMP_ID IS NULL AND ( E.LOGIN_ID IS NULL OR ( E.LOGIN_ID IS NOT NULL AND NOT EXISTS (SELECT 1 FROM OFFICERS OFCR WHERE OFCR.USER_ID = E.LOGIN_ID AND OFCR.ACTIVE = 'Y') ) )