View Definition:
SELECT
CC.ID AS COURT_CASE_ID,
CC.CASE_NUMBER AS CASE_NUMBER,
CASE_AGENCY.AGENCY_CODE AS AGENCY_CODE,
CASE_AGENCY.AGENCY_DESC AS AGENCY_DESC,
CC.FILED_DATE AS FILED_DATE,
STATUS.CODE AS STATUS_CODE,
STATUS.CODE_TYPE AS STATUS_CODE_TYPE,
STATUS.DESCRIPTION AS STATUS_DESC,
CC.COMMENTS AS COMMENTS,
CC.CREATOR_ID AS CREATOR_ID,
CC.CREATOR_DATE AS CREATOR_DATE,
CC.UPDATOR_ID AS UPDATOR_ID,
CC.UPDATOR_DATE AS UPDATOR_DATE,
INCS.INCIDENT_ID AS INCIDENT_ID,
INCS.INC_REPORT_NUMBER AS INC_REPORT_NUM,
ARR.ARREST_ID AS ARREST_ID,
ARR.ARREST_NUM AS ARREST_NUM,
CMP.ID AS COMPLAINT_ID,
CMP.CRIMINAL_COMPLAINT_NUM AS COMPLAINT_NUM,
NAMES.PER_PERSON_ID AS PERSON_ID,
MP.INDX_SEC_LEVEL_CODE,
NAMES.FNAME AS PERSON_FNAME,
NAMES.MNAME AS PERSON_MNAME,
NAMES.LNAME AS PERSON_LNAME,
PR.CODE AS PERSON_ROLE_CODE,
PR.CODE_TYPE AS PERSON_ROLE_CODE_TYPE,
PR.DESCRIPTION AS PERSON_ROLE_DESC,
OFFS.OFFICER_ID AS OFFICER_ID,
OFFS.FNAME AS OFFICER_FNAME,
OFFS.MNAME AS OFFICER_MNAME,
OFFS.LNAME AS OFFICER_LNAME,
OFFS.INTERNAL_ID AS OFFICER_INTERNAL_ID,
CCA.COURT_APPEARANCE_ID AS APPEARANCE_ID,
CA.APPEARANCE_DATE AS APPEARANCE_DATE,
APPTYPE.CODE AS APPEARANCE_TYPE_CODE,
APPTYPE.DESCRIPTION AS APPEARANCE_TYPE_DESC,
APPDISP.CODE AS APPEARANCE_STATUS_CODE,
APPDISP.DESCRIPTION AS APPEARANCE_STATUS_DESC,
APPREASON.CODE AS APPEARANCE_REASON_CODE,
APPREASON.DESCRIPTION AS APPEARANCE_REASON_DESC,
CAOFF.TITLE AS APPEARANCE_OFF_TITLE,
CAOFF.FNAME AS APPEARANCE_OFF_FNAME,
CAOFF.MNAME AS APPEARANCE_OFF_MNAME,
CAOFF.LNAME AS APPEARANCE_OFF_LNAME,
CAOFF.INTERNAL_ID AS APPEARANCE_OFF_INTERNAL_ID,
AC.AGENCY_DESC AS APPEARANCE_OFF_AGENCY_NAME
FROM COURT_CASES CC
INNER JOIN COURT_CASE_APPEARANCES CCA ON CCA.COURT_CASE_ID = CC.ID
LEFT OUTER JOIN AGENCY_CODES CASE_AGENCY ON CASE_AGENCY.AGENCY_CODE = CC.AGENCY_CODE
LEFT OUTER JOIN EJS_CODES STATUS ON STATUS.CODE_TYPE = CC.STATUS_CODE_TYPE AND STATUS.CODE = CC.STATUS_CODE
LEFT OUTER JOIN COURT_CASE_INCS CCI ON CCI.COURT_CASE_ID = CC.ID
LEFT OUTER JOIN INCIDENTS INCS ON INCS.INCIDENT_ID = CCI.INC_INCIDENT_ID
LEFT OUTER JOIN COURT_CASE_ARRESTS CCA ON CCA.COURT_CASE_ID = CC.ID
LEFT OUTER JOIN ARRESTS ARR ON ARR.ARREST_ID = CCA.ARR_ARREST_ID
LEFT OUTER JOIN COURT_CASE_COMPLAINTS CCC ON CCC.COURT_CASE_ID = CC.ID
LEFT OUTER JOIN CRIMINAL_COMPLAINTS CMP ON CMP.ID = CCC.COMPLAINT_ID
LEFT OUTER JOIN COURT_CASE_PEOPLE CCP ON CCP.COURT_CASE_ID = CC.ID
LEFT OUTER JOIN EJS_CODES PR ON PR.CODE_TYPE = CCP.PERSON_ROLE_CODE_TYPE AND PR.CODE = CCP.PERSON_ROLE_CODE
LEFT OUTER JOIN MASTER_NAMES NAMES ON NAMES.PER_PERSON_ID = CCP.PER_PERSON_ID AND NAMES.NAME_TYPE_CODE = 'P'
LEFT OUTER JOIN PEOPLE P ON NAMES.PER_PERSON_ID = P.PERSON_ID
LEFT OUTER JOIN MASTER_PEOPLE MP ON P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID
LEFT OUTER JOIN COURT_CASE_OFFS CCO ON CCO.COURT_CASE_ID = CC.ID
LEFT OUTER JOIN OFFICERS OFFS ON OFFS.OFFICER_ID = CCO.OFFICER_ID
LEFT OUTER JOIN COURT_APPEARANCES CA ON CA.ID = CCA.COURT_APPEARANCE_ID
LEFT OUTER JOIN EJS_CODES APPTYPE ON APPTYPE.CODE_TYPE = CA.TYPE_CODE_TYPE AND APPTYPE.CODE = CA.TYPE_CODE
LEFT OUTER JOIN EJS_CODES APPDISP ON APPDISP.CODE_TYPE = CA.DISPOSITION_CODE_TYPE AND APPDISP.CODE = CA.DISPOSITION_CODE
LEFT OUTER JOIN EJS_CODES APPREASON ON APPREASON.CODE_TYPE = CA.REASON_CODE_TYPE AND APPREASON.CODE = CA.REASON_CODE
LEFT OUTER JOIN COURT_APPEARANCE_OFFS CAO ON CAO.COURT_APPEARANCE_ID = CA.ID
LEFT OUTER JOIN OFFICERS CAOFF ON CAOFF.OFFICER_ID = CAO.OFFICER_ID
LEFT OUTER JOIN AGENCY_CODES AC ON CAOFF.AGNCY_CD_AGENCY_CODE = AC.AGENCY_CODE
