SELECT FI.FLDINT_ID, FI.AGENCY_CODE, AC.AGENCY_DESC AS AGENCY_DESC, FI.AGENCY_ONLY_YN AS AGENCY_ONLY, FI.FLDINT_DATE, FI.FLDINT_DATE AS SORT_DATE, FI.FLDINT_TYPE AS FLDINT_TYPE, EC.DESCRIPTION AS FLDINT_TYPE_DESC, FI.INTELL_ONLY_YN AS INTEL_ONLY, FI.NOTE_ID AS NOTE_ID, FI.SECURITY_LEVEL AS SECURITY_LEVEL, (SELECT ISLC.INC_SEC_LEVEL_DESC FROM INCIDENT_SECURITY_LEVEL_CODES ISLC WHERE ISLC.INC_SEC_LEVEL_CODE = FI.SECURITY_LEVEL) AS SECURITY_DESC, FI.SUMMARY AS SUMMARY, FP.ROLE_TYPE, RC.ROLE_DESC AS ROLE_DESC, MN.FNAME AS FNAME, MN.LNAME AS LNAME, MN.SSN AS SSN, MP.RACE_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = MP.RACE_CODE_TYPE AND EC.CODE = MP.RACE_CODE) AS RACE_DESC, MP.SEX_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = MP.SEX_CODE_TYPE AND EC.CODE = MP.SEX_CODE) AS SEX_DESC, P.MASTER_PERSON_ID AS PERSON_ID, CASE WHEN CFI.CASE_ID IS NOT NULL THEN 'Y' ELSE 'N' END AS IS_ASSOCIATED_TO_CASE FROM FIELD_INTERVIEWS FI INNER JOIN AGENCY_CODES AC ON AC.AGENCY_CODE = FI.AGENCY_CODE INNER JOIN EJS_CODES EC ON EC.CODE_TYPE = FI.FLDINT_TYPE_TYPE AND EC.CODE = FI.FLDINT_TYPE INNER JOIN FLDINT_PEOPLE FP ON FP.FLDINT_ID = FI.FLDINT_ID INNER JOIN ROLE_CODES RC ON RC.ROLE_TYPE = FP.ROLE_TYPE INNER JOIN PEOPLE P ON P.PERSON_ID = FP.PERSON_ID INNER JOIN MASTER_PEOPLE MP ON MP.MASTER_PERSON_ID = P.MASTER_PERSON_ID INNER JOIN PEOPLE PP ON PP.MASTER_PERSON_ID = MP.MASTER_PERSON_ID INNER JOIN MASTER_NAMES MN ON MN.PER_PERSON_ID = PP.PERSON_ID AND MN.NAME_TYPE_CODE = 'P' LEFT OUTER JOIN CASE_FIELD_INTS CFI ON CFI.FLDINT_ID = FI.FLDINT_ID