View Definition:
SELECT CI.CITATION_ID,
CI.TICKET_NUMBER,
CI.CITATION_TYPE_CODE,
CTC.DESCRIPTION AS CITATION_TYPE_DESC,
CI.FILED_DATE,
CI.CITATION_DATE,
CI.USER_AGENCY,
CI.AGENCY_CODE,
CAC.AGENCY_DESC,
CI.PLAINTIFF,
CI.TWP_RD,
CI.STATE_DIST_CODE,
SDC.DESCRIPTION AS STATE_DIST_DESC,
CI.CITY_CODE,
CC.DESCRIPTION AS CITY_DESC,
CI.COUNTY_CODE,
COC.DESCRIPTION AS COUNTY_DESC,
CI.BOND_TYPE,
BTC.DESCRIPTION AS BOND_TYPE_DESC,
CI.APPEARANCE_DATE,
CI.MUST_APPEAR,
CI.BOND_AMOUNT,
CI.POSTED_SPEED,
CI.ACTUAL_SPEED,
CI.RACE_KNOWN,
CI.STOP_COMMENT,
CI.PERCEIVED_RACE_CODE,
PRC.DESCRIPTION AS PERCEIVED_RACE_DESC,
CI.DURATION_CODE,
DC.DESCRIPTION AS DURATION_DESC,
MN.LNAME AS LAST_NAME,
MN.FNAME AS FIRST_NAME,
MN.MNAME AS MIDDLE_NAME,
CASE WHEN MN.LNAME IS NOT NULL THEN MN.LNAME||', '||MN.FNAME||' '||MN.MNAME ELSE MN.FNAME||' '||MN.MNAME END,
MN.DOB,
MP.RACE_CODE,
(SELECT RC.DESCRIPTION FROM EJS_CODES RC WHERE RC.CODE_TYPE = MP.RACE_CODE_TYPE AND RC.CODE = MP.RACE_CODE),
MP.SEX_CODE,
(SELECT SX.DESCRIPTION FROM EJS_CODES SX WHERE SX.CODE_TYPE = MP.SEX_CODE_TYPE AND SX.CODE = MP.SEX_CODE),
MP.ETHNICITY_CODE,
(SELECT ET.DESCRIPTION FROM EJS_CODES ET WHERE ET.CODE_TYPE = MP.ETHNICITY_CODE_TYPE AND ET.CODE = MP.ETHNICITY_CODE),
CP.MISC_ID_NUMBER AS DL_NUMBER,
MID.STATE_CD_STATE_CODE AS DL_STATE,
CP.PERSON_ID,
MP.MASTER_PERSON_ID,
CASE WHEN CP.PERSON_ID IS NOT NULL THEN EJS_FORMAT_ADDRESS(EJS_GET_LATEST_PERSON_ADDRESS(CP.PERSON_ID, NULL)) ELSE NULL END,
CASE WHEN CP.PERSON_ID IS NOT NULL THEN EJS_ADDRESSES.INTERSECT_STREET_SUMMARY(EJS_GET_LATEST_PERSON_ADDRESS(CP.PERSON_ID, NULL)) ELSE NULL END,
CP.BAC,
CV.CITATION_VIOLATION_ID,
CV.CHARGE_CODE,
CC.CHA_DESC,
CC.STATUTE,
CV.CLASS_CODE,
CLS.DESCRIPTION CLASS_DESC,
CV.NATURE_OF_OFFENSE,
CV.FINE_AMOUNT,
CV.DISPOSITION_CODE,
DSP.DESCRIPTION DISPOSITION_DESC,
CASE WHEN CO.OFFICER_ID IS NOT NULL THEN OFF.FNAME||' '||OFF.MNAME||' '||OFF.LNAME||' - '||OFF.INTERNAL_ID
WHEN CO.OFFICER_NAME IS NOT NULL AND OFF.INTERNAL_ID IS NOT NULL THEN CO.OFFICER_NAME||' - '||OFF.INTERNAL_ID
WHEN CO.OFFICER_NAME IS NULL AND OFF.INTERNAL_ID IS NOT NULL THEN OFF.INTERNAL_ID ELSE CO.OFFICER_NAME END AS OFF_NAME,
CASE WHEN CO.OFFICER_ID IS NOT NULL THEN OFF.INTERNAL_ID ELSE CO.INTERNAL_ID END AS OFF_BADGE,
CASE WHEN CO.OFFICER_ID IS NOT NULL THEN OFF.AGNCY_CD_AGENCY_CODE ELSE CO.AGENCY END AS OFF_AGENCY_CODE,
CASE WHEN CO.OFFICER_ID IS NOT NULL THEN (SELECT AGENCY_DESC FROM AGENCY_CODES WHERE AGENCY_CODE = OFF.AGNCY_CD_AGENCY_CODE) ELSE CO.AGENCY END OFF_AGENCY_DESC,
CO.ROLE_CODE,
OFR.DESCRIPTION,
OFF.TITLE,
CRI.TYPE_CODE,
(SELECT TYP.DESCRIPTION FROM EJS_CODES TYP WHERE TYP.CODE_TYPE = CRI.TYPE_CODE_TYPE AND TYP.CODE = CRI.TYPE_CODE),
CRI.SEARCH_TYPE_CODE,
(SELECT SRC.DESCRIPTION FROM EJS_CODES SRC WHERE SRC.CODE_TYPE = CRI.SEARCH_TYPE_CODE_TYPE AND SRC.CODE = CRI.SEARCH_TYPE_CODE),
CRI.CONTRABAND_TYPE_CODE,
(SELECT CON.DESCRIPTION FROM EJS_CODES CON WHERE CON.CODE_TYPE = CRI.CONTRABAND_TYPE_CODE_TYPE AND CON.CODE = CRI.CONTRABAND_TYPE_CODE),
CRI.ARREST_TYPE_CODE,
(SELECT ARR.DESCRIPTION FROM EJS_CODES ARR WHERE ARR.CODE_TYPE = CRI.ARREST_TYPE_CODE_TYPE AND ARR.CODE = CRI.ARREST_TYPE_CODE),
JOIN(CURSOR(SELECT DESCRIPTION
FROM CITATION_METHODS CM
, METHOD_CODES MC
WHERE CM.CITATION_ID = CI.CITATION_ID),', ')
FROM CITATIONS CI
INNER JOIN CITATION_TYPE_CODES CTC
ON CI.CITATION_TYPE_CODE = CTC.CITATION_TYPE_CODE
INNER JOIN AGENCY_CODES CAC ON CI.AGENCY_CODE = CAC.AGENCY_CODE
INNER JOIN EJS_CODES COC ON CI.COUNTY_CODE_TYPE = COC.CODE_TYPE
AND CI.COUNTY_CODE = COC.CODE
LEFT OUTER JOIN CITATION_PEOPLE CP ON CI.CITATION_ID = CP.CITATION_ID
LEFT OUTER JOIN MASTER_NAMES MN ON CP.PERSON_ID = MN.PER_PERSON_ID AND CP.MN_ID = MN.MN_ID
LEFT OUTER JOIN PEOPLE P ON CP.PERSON_ID = P.PERSON_ID
LEFT OUTER JOIN MASTER_PEOPLE MP ON P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID
LEFT OUTER JOIN CITATION_OFFICERS CO ON CI.CITATION_ID = CO.CITATION_ID
LEFT OUTER JOIN OFFICERS OFF ON CO.OFFICER_ID = OFF.OFFICER_ID
LEFT OUTER JOIN EJS_CODES OFR ON CO.ROLE_CODE_TYPE = OFR.CODE_TYPE AND
CO.ROLE_CODE = OFR.CODE
LEFT OUTER JOIN MISC_IDS MID ON CP.MISC_ID_CNTR = MID.MISC_ID_CNTR
AND CP.MISC_ID_CODE = MID.MISC_CD_MISC_ID_CODE
AND CP.MISC_ID_NUMBER = MID.ID_NUMBER
LEFT OUTER JOIN EJS_CODES CC ON CI.CITY_CODE_TYPE = CC.CODE_TYPE AND CI.CITY_CODE = CC.CODE
LEFT OUTER JOIN STATE_DISTRICT_CODES SDC ON CI.STATE_DIST_CODE = SDC.STATE_DIST_CODE
LEFT OUTER JOIN BOND_TYPE_CODES BTC ON CI.BOND_TYPE = BTC.BOND_TYPE
LEFT OUTER JOIN EJS_CODES PRC ON CI.PERCEIVED_RACE_CODE_TYPE = PRC.CODE_TYPE AND
CI.PERCEIVED_RACE_CODE = PRC.CODE
LEFT OUTER JOIN EJS_CODES DC ON CI.DURATION_CODE_TYPE = PRC.CODE_TYPE AND
CI.DURATION_CODE = PRC.CODE
LEFT OUTER JOIN CITATION_VIOLATIONS CV ON CI.CITATION_ID = CV.CITATION_ID
LEFT OUTER JOIN CHARGE_CODES CC ON CV.CHARGE_CODE = CC.CODE
LEFT OUTER JOIN EJS_CODES CLS ON CV.CLASS_CODE_TYPE = CLS.CODE_TYPE AND
CV.CLASS_CODE = CLS.CODE
LEFT OUTER JOIN EJS_CODES DSP ON CV.DISPOSITION_CODE_TYPE = DSP.CODE_TYPE AND
CV.DISPOSITION_CODE = DSP.CODE
LEFT OUTER JOIN CITATION_RACIAL_INFO CRI ON CI.CITATION_ID = CRI.CITATION_ID
