Table slot9.WORLD.TSTA.CRIMINAL_COMPLAINTS
This table stores criminal complaint records.
Legend:
Primary key columns
Columns with indexes
Implied relationships
Excluded column relationships
< n > number of related tables
 
Column Type Size Nulls Auto Default Children Parents Comments
ID number 0
COURT_CASE_COMPLAINTS.COMPLAINT_ID CRTCASECMP_CRMCMP_FK R
CRIMINAL_COMP_ATTACHMENTS.CRIMINAL_COMPLAINT_ID CCATT_CRMCMP_FK R
CRIMINAL_COMP_IMAGES.CRIMINAL_COMPLAINT_ID CCIMA_CRMCMP_FK R
CRIMINAL_COMPLAINT_ARRESTS.CRIMINAL_COMPLAINT_ID CCARRS_CRMCMP_FK R
CRIMINAL_COMPLAINT_CHARGES.CRIMINAL_COMPLAINT_ID CCCRG_CRMCMP_FK R
CRIMINAL_COMPLAINT_INCIDENTS.CRIMINAL_COMPLAINT_ID CCINCS_CRMCMP_FK R
USI_CRIMINAL_COMPLAINT_FIELDS.COMPLAINT_ID USI_CCF_CRMCMP_FK R
USI_CRIMINAL_COMPLAINT_FORMS.COMPLAINT_ID USI_CC_CRMCMP_FK R
COURT_CASE_DELETE.ID Implied Constraint R
PK - Unique identifier for each criminal complaint record.
CRIMINAL_COMPLAINT_NUM varchar2 100  √  null RMS System generated number for criminal complaint number.
PER_PERSON_ID number 0  √  null
PEOPLE.PERSON_ID CRMCMP_PER_FK R
MASTER_NAMES.PER_PERSON_ID CRMCMP_MN_FK R
FK to people table. Specifies the offender on the criminal complaint.
MN_MN_ID number 0  √  null
MASTER_NAMES.MN_ID CRMCMP_MN_FK R
FK to master_names table. Specifies the offender master name on the criminal complaint.
PERSON_ADDRESS_ID number 0  √  null
ADDRESSES.ADDRESS_ID CRMCMP_ADDR_FK R
FK to addresses table. Specifies the offender address on the criminal complaint.
OBTN_NUMBER varchar2 100  √  null Offender base tracking number.
COMPLAINT_DATETIME date 7  √  null Date/time of application for criminal complaint.
STATUS_CODE varchar2 30  √  null
EJS_CODES.CODE CRMCMP_EJSCD_STATUS_FK R
FK to ejs_codes table. Code for criminal complaint RMS status.
STATUS_CODE_TYPE varchar2 30  √  null
EJS_CODES.CODE_TYPE CRMCMP_EJSCD_STATUS_FK R
FK to ejs_codes table. Code type for criminal complaint RMS status.
STATUS_DATE date 7  √  null Date of current criminal complaint RMS status.
SUBMIT_STATUS_CODE varchar2 30  √  null
EJS_CODES.CODE CRMCMP_EJSCD_SUBMIT_FK R
FK to ejs_codes table. Code for criminal complaint state submission status.
SUBMIT_STATUS_CODE_TYPE varchar2 30  √  null
EJS_CODES.CODE_TYPE CRMCMP_EJSCD_SUBMIT_FK R
FK to ejs_codes table. Code type for criminal complaint state submission status.
SUBMIT_STATUS_DATE date 7  √  null Date of current criminal complaint state submission status.
COURT_ID number 0  √  null
COURT_LOCATION_CODES.COURT_ID CRMCMP_CRTLOC_FK R
FK to court_locations table. Selected court on the criminal complaint.
COURT_DOCKET_NUMBER varchar2 100  √  null Docket number issued from the court.
COMPLAINT_TYPE_CODE varchar2 30  √  null
EJS_CODES.CODE CRMCMP_EJSCD_COMP_FK R
FK to ejs_codes table. Code for criminal complaint type.
COMPLAINT_TYPE_CODE_TYPE varchar2 30  √  null
EJS_CODES.CODE_TYPE CRMCMP_EJSCD_COMP_FK R
FK to ejs_codes table. Code type for criminal complaint type.
HEARING_CODE varchar2 30  √  null
EJS_CODES.CODE CRMCMP_EJSCD_HEAR_FK R
FK to ejs_codes table. Code for type of court hearing.
HEARING_CODE_TYPE varchar2 30  √  null
EJS_CODES.CODE_TYPE CRMCMP_EJSCD_HEAR_FK R
FK to ejs_codes table. Code type for type of court hearing.
THREAT_CODE varchar2 30  √  null
EJS_CODES.CODE CRMCMP_EJSCD_THREAT_FK R
FK to ejs_codes table. Code for criminal complaint threat.
THREAT_CODE_TYPE varchar2 30  √  null
EJS_CODES.CODE_TYPE CRMCMP_EJSCD_THREAT_FK R
FK to ejs_codes table. Code type for criminal complaint threat.
CHARGE_TYPE_CODE varchar2 30  √  null
EJS_CODES.CODE CRMCMP_EJSCD_CHARGE_FK R
FK to ejs_codes table. Code for criminal complaint charge type.
CHARGE_TYPE_CODE_TYPE varchar2 30  √  null
EJS_CODES.CODE_TYPE CRMCMP_EJSCD_CHARGE_FK R
FK to ejs_codes table. Code type for criminal complaint charge type.
COMPLAINTANT_OFF_ID number 0  √  null
OFFICERS.OFFICER_ID CRMCMP_OFF1_FK R
FK to officers table. Specifies criminal complaint complaintant officer.
COMPLAINTANT_AGENCY_CODE varchar2 30  √  null
AGENCY_CODES.AGENCY_CODE CRMCMP_AGNCY_CD_FK R
FK to agency_codes table. Agency of officer selected on the criminal complaint.
CRIMINAL_COMPLAINT_ADDR_ID number 0  √  null
ADDRESSES.ADDRESS_ID CRMCMP_ADDR_CRMCMP_FK R
FK to addresses table. Address of the criminal complaint record (offense address).
COMMENTS varchar2 255  √  null Additional comments/remarks on the criminal complaint.
JUVENILE varchar2 1 'N' Y/N flag indicating if the accused is a juvenile.
CREATOR_ID varchar2 100  √  null
CREATOR_DATE date 7  √  null
UPDATOR_ID varchar2 100  √  null
UPDATOR_DATE date 7  √  null
REC_ID number 0  √  null
SUBMIT_STATUS_REASON varchar2 255  √  null Free text reason for submission status (used when court denies a complaint).
COURT_AGENCY_CODE varchar2 30  √  null
COURT_LOCATION_CODES.AGENCY_CODE CRMCMP_CRTLOC_FK R
STATE_SUBMITTING_USER varchar2 100  √  null
IJIS_USER_PROFILES.LOGIN_ID CRMCMP_IUP_FK R
FK to IJIS_USER_PROFILES. user_id of the person that submitted the complaint to the state.

Analyzed at Mon Sep 20 21:05 MDT 2021

Requirements (check constraints):
Constraint Constraint Name
JUVENILE IN ( 'Y' , 'N' ) CK_CRMCMP_JUVENILE_FLAG
"ID" IS NOT NULL CRMCMP_ID_NN
"JUVENILE" IS NOT NULL CRMCMP_JUVFLG_NN

Indexes:
Column(s) Type Sort Constraint Name
ID Primary key Asc CRMCMP_PK
CRIMINAL_COMPLAINT_ADDR_ID Performance Asc CRMCMP_ADDR_CRMCMP_FK_I
PERSON_ADDRESS_ID Performance Asc CRMCMP_ADDR_FK_I
COMPLAINTANT_AGENCY_CODE Performance Asc CRMCMP_AGNCY_CD_FK_I
COURT_ID + COURT_AGENCY_CODE Performance Asc/Asc CRMCMP_CRTLOC_FK_I
CHARGE_TYPE_CODE_TYPE + CHARGE_TYPE_CODE Performance Asc/Asc CRMCMP_EJSCD_CHARGE_FK_I
COMPLAINT_TYPE_CODE_TYPE + COMPLAINT_TYPE_CODE Performance Asc/Asc CRMCMP_EJSCD_COMP_FK_I
HEARING_CODE_TYPE + HEARING_CODE Performance Asc/Asc CRMCMP_EJSCD_HEAR_FK_I
STATUS_CODE_TYPE + STATUS_CODE Performance Asc/Asc CRMCMP_EJSCD_STATUS_FK_I
SUBMIT_STATUS_CODE_TYPE + SUBMIT_STATUS_CODE Performance Asc/Asc CRMCMP_EJSCD_SUBMIT_FK_I
THREAT_CODE_TYPE + THREAT_CODE Performance Asc/Asc CRMCMP_EJSCD_THREAT_FK_I
STATE_SUBMITTING_USER Performance Asc CRMCMP_IUP_FK_I
MN_MN_ID + PER_PERSON_ID Performance Asc/Asc CRMCMP_MN_FK_I
OBTN_NUMBER Performance Asc CRMCMP_OBTN_NUMBER_I
COMPLAINTANT_OFF_ID Performance Asc CRMCMP_OFF1_FK_I
PER_PERSON_ID Performance Asc CRMCMP_PER_FK_I

Close relationships  within of separation: