SELECT U.LOGIN_ID, U.AGNCY_CD_AGENCY_CODE, (SELECT POD2.AGENCY_CODE FROM PSEUDO_ORG_DESCRIPTORS POD INNER JOIN PSEUDO_ORG_DESCRIPTORS POD2 ON POD.STRUCTURE_ID = POD2.STRUCTURE_ID AND POD2.IS_ROOT = 'Y' WHERE POD.AGENCY_CODE = U.AGNCY_CD_AGENCY_CODE) AS ORG_AGENCY_CODE, U.LNAME AS LNAME, U.FNAME, U.MNAME, AC.AGENCY_DESC AS AGENCY_NAME, U.EXPIRE_DATE, U.TELEPHONE_NUMBER, U.TITLE, U.EMAIL_ADDR, U.SELF_ASSIGN_ENABLED_FLAG, (SELECT MAX (RL.ROLE_ACCESS_LEVEL) FROM IJIS_USER_PROFILE_ROLES IUP INNER JOIN Role_Securities RL ON IUP.ROLE_NAME = RL.ROLE_NAME WHERE IUP.LOGIN_ID = U.LOGIN_ID) AS ACCESS_LEVEL, UP.USER_TYPE_CODE AS USER_TYPE, JOIN(CURSOR(SELECT ROLE_NAME FROM IJIS_USER_PROFILE_ROLES WHERE LOGIN_ID = U.LOGIN_ID),', ') AS USER_ROLES, EUS.STATUS, UP.ACCOUNT_STATUS, EUS.SCHEMA_ID, CASE WHEN UP.ACCOUNT_STATUS = 'L' THEN 'Locked - Failed Login' WHEN UP.ACCOUNT_STATUS = 'I' OR EUS.STATUS = 'I' THEN 'Locked - Inactive' WHEN UP.ACCOUNT_STATUS = 'A' AND EUS.STATUS = 'A' THEN 'Active' WHEN UP.ACCOUNT_STATUS = 'D' THEN 'Deactivated' END AS STATUS_DESC, UP.CC_ADMIN, U.TIME_CODE, SLC.DESCRIPTION FROM IJIS_USER_PROFILES U INNER JOIN AGENCY_CODES AC ON U.AGNCY_CD_AGENCY_CODE = AC.AGENCY_CODE INNER JOIN EJS_USERS@EJSLOOKUPDBC UP ON UP.LOGIN_ID = U.LOGIN_ID INNER JOIN EJS_USERS_SCHEMAS@EJSLOOKUPDBC EUS ON EUS.LOGIN_ID = UP.LOGIN_ID LEFT OUTER JOIN EJS_CODES SLC ON SLC.CODE = U.TIME_CODE AND SLC.CODE_TYPE = U.TIME_CODE_TYPE