SELECT IJIS.LOGIN_ID AS USER_ID, IJIS.FNAME || ' ' || IJIS.LNAME AS USER_NAME, NVL (IJIS.AGNCY_CD_AGENCY_CODE, 'UNKNOWN') AS AGENCY_CODE, NVL (AC.AGENCY_DESC, 'UNKNOWN') AS AGENCY_NAME, EU.LAST_LOGIN_DATE AS LAST_LOGIN_DATE, UPR.RECORD_ID AS USER_PINNED_RECORD, EC1.DESCRIPTION AS PINNED_RECORD_TYPE, UG.NAME AS USER_GROUP, STC.SUB_TYPE_DESC AS SUBSCRIPTION_TYPE, SA.SUBSCRIPTION_ACTION_CODE AS SUBSCRIPTION_ACTION, S.SUB_VALUE AS SUBSCRIPTION_INDEX, EXU.TYPE_CODE AS EXTERNAL_USER_TYPE, DECODE(NVL(UST.TOKEN, 'X'), 'X', 'N', 'Y') AS SSO, ISL.INC_SEC_LEVEL_DESC AS INCIDENT_SECURITY_LEVEL, MIS.DESCRIPTION AS MASTER_INDEX_SECURITY_LEVEL, IJIS.SELF_ASSIGN_ENABLED_FLAG AS SELF_ASSIGN_ENABLED, IJIS.SUBSCRIPTION_OVERRIDE AS SUBSCRIPTION_OVERRIDE, IJIS.EXT_AUTH_ENABLED_FLAG AS EXT_AUTH_ENABLED, IUPR.ROLE_NAME AS USER_ROLE FROM IJIS_USER_PROFILES IJIS LEFT OUTER JOIN AGENCY_CODES AC ON AC.AGENCY_CODE = IJIS.AGNCY_CD_AGENCY_CODE LEFT OUTER JOIN USER_PINNED_RECORDS UPR ON UPR.LOGIN_ID = IJIS.LOGIN_ID LEFT OUTER JOIN EJS_CODES EC1 ON EC1.CODE = UPR.RECORD_TYPE_CODE AND EC1.CODE_TYPE = UPR.RECORD_TYPE_CODE_TYPE LEFT OUTER JOIN USER_GROUP_USERS UGU ON UGU.LOGIN_ID = IJIS.LOGIN_ID LEFT OUTER JOIN USER_GROUPS UG ON UG.ID = UGU.GROUP_ID LEFT OUTER JOIN SUBSCRIPTIONS S ON S.LOGIN_ID = IJIS.LOGIN_ID LEFT OUTER JOIN SUBSCRIPTION_TYPE_CODES STC ON STC.SUB_TYPE_CODE = S.SUB_TYPE_CODE LEFT OUTER JOIN SUBSCRIPTION_ACTIONS SA ON SA.SUBSCRIPTION_ID = S.SUBSCRIPTION_ID LEFT OUTER JOIN EXTINT_EXTERNAL_USERS@EJSLOOKUPDBC EXU ON EXU.LOGIN_ID = IJIS.LOGIN_ID LEFT OUTER JOIN EJS_USERS@EJSLOOKUPDBC EU ON EU.LOGIN_ID = EXU.LOGIN_ID LEFT JOIN EJS_USER_SYSTEM_TOKENS@EJSLOOKUPDBC UST ON UST.LOGIN_ID = IJIS.LOGIN_ID LEFT OUTER JOIN IJIS_USER_PROFILE_ROLES IUPR ON IUPR.LOGIN_ID = IJIS.LOGIN_ID LEFT OUTER JOIN INCIDENT_SECURITY_LEVEL_CODES ISL ON ISL.INC_SEC_LEVEL_CODE = IJIS.INC_SEC_LEVEL_CODE LEFT OUTER JOIN MASTER_INDEX_SECURITY_CODES MIS ON MIS.INDX_SEC_LEVEL_CODE = IJIS.INDX_SEC_LEVEL_CODE