PSC_CE_INCIDENT
This table contains incident transaction record data.
Details
-
Schema: FUSION
-
Object owner: PSC_CE
-
Object type: TABLE
-
Tablespace: FUSION_TS_TX_DATA
Primary Key
| Name | Columns |
|---|---|
|
PSC_CE_INCIDENT_PK |
INCIDENT_KEY |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| INCIDENT_KEY | NUMBER | 18 | Yes | This column is used to indicate unique id for the incident. Use Autosequencing | |
| PARCEL_ID_ENTERED | VARCHAR2 | 50 | This column is used to indicate the parcel id entered. | ||
| INCIDENT_PUID | VARCHAR2 | 480 | Yes | Incident PUID | |
| INCIDENT_ID | VARCHAR2 | 50 | Yes | This column is used to indicate a logical id for the incident transaction | |
| INTAKE_KEY | NUMBER | 18 | Yes | This column is used to indicate used as key from parent record PSC_CE_INCIDENT_MY | |
| INTAKE_PUID | VARCHAR2 | 480 | Yes | Intake's PUID | |
| INTAKE_SOURCE | VARCHAR2 | 30 | This column is used to indicate Case source transaction type | ||
| ISSUE_TYPE_ID | VARCHAR2 | 50 | Yes | This column is used to indicate logical Issue Type ID | |
| ISSUE_SUBTYPE_ID | VARCHAR2 | 50 | Yes | This column is used to indicate logical Issue Sub Type ID | |
| ISSUE_DESCRIPTION | VARCHAR2 | 1000 | Yes | This column is used to indicate complainant issue description | |
| STATUS | VARCHAR2 | 15 | Yes | This column is used to indicate incident status | |
| DISCLOSURE_FLAG | VARCHAR2 | 1 | This column is used to indicate use by agencies that do not accept anonymous incidents, but gives the option to hide private information | ||
| NOTIFY_ME_FLAG | VARCHAR2 | 1 | This column is used to indicate indicator to update the reporting register user | ||
| DUPLICATE_FLAG | VARCHAR2 | 1 | Yes | This column is used to indicate whether the incident is duplicate or not | |
| REOPENED_FLAG | VARCHAR2 | 1 | Yes | This column is used to indicate whether incident is reopened or not. | |
| REOPENED_REASON | VARCHAR2 | 30 | This column is used to indicate the reason why the incident is reopened. | ||
| REPORTED_BY_ID | VARCHAR2 | 18 | This column is used to indicate reporter | ||
| AGENCY_FLAG | VARCHAR2 | 1 | AGENCY_FLAG | ||
| PROPERTY_ADDRESS1 | VARCHAR2 | 240 | This column is used to indicate address line 1 where the complaint is taking place | ||
| PROPERTY_ADDRESS2 | VARCHAR2 | 240 | This column is used to indicate address line 2 where the complaint is taking place | ||
| PROPERTY_ADDRESS3 | VARCHAR2 | 240 | This column is used to indicate address line 3 where the complaint is taking place | ||
| PROPERTY_ADDRESS4 | VARCHAR2 | 240 | This column is used to indicate address line 4 where the complaint is taking place | ||
| PROPERTY_CITY | VARCHAR2 | 60 | This column is used to indicate city where the complaint is taking place | ||
| PROPERTY_STATE | VARCHAR2 | 60 | This column is used to indicate state where the complaint is taking place | ||
| PROPERTY_PROVINCE | VARCHAR2 | 60 | This column is used to indicate province where the complaint is taking place | ||
| PROPERTY_COUNTRY | VARCHAR2 | 3 | This column is used to indicate country where the complaint is taking place | ||
| PROPERTY_COUNTY | VARCHAR2 | 60 | This column is used to indicate county where the complaint is taking place | ||
| PROPERTY_POSTAL_CODE | VARCHAR2 | 60 | This column is used to indicate postal code where the complaint is taking place | ||
| PROPERTY_POSTAL_PLUS4CODE | VARCHAR2 | 10 | This column is used to indicate postal Code plus 4 where the complaint is taking place | ||
| PROPERTY_COORDINATE_X | NUMBER | This column is used to indicate property X coordinate where the complaint is taking place | |||
| PROPERTY_COORDINATE_Y | NUMBER | This column is used to indicate property Y coordinate where the complaint is taking place | |||
| LONGITUDE | NUMBER | This column is used to indicate longitude of property reported | |||
| LATITUDE | NUMBER | This column is used to indicate latitude of property reported | |||
| SHAPE | UDT | This column is used to store oracle spatial geometry coordinates of the parcel. | |||
| DISTRICT_TYPE | VARCHAR2 | 50 | This column is used to store district type. | ||
| PROPERTY_ADDRESS_ID | NUMBER | 18 | This column is used to indicate property address id | ||
| PROPERTY_PARCEL_ID | NUMBER | 18 | This column is used to indicate property parcel id | ||
| DISTRICT_ID | VARCHAR2 | 30 | This column is used to indicate district id | ||
| LOCATION_DESCR | VARCHAR2 | 1000 | This column is used to indicate additional address | ||
| PRIORITY | VARCHAR2 | 30 | This column is used to indicate incident Priority code (CRITICAL P1,HIGH P2,MEDIUM P3,LOW P4) dropdown value | ||
| ORIGINATOR_SR_ID | VARCHAR2 | 30 | This column is used to indicate external source unique service request id | ||
| ORIGINATOR_SOURCE | VARCHAR2 | 30 | This column is used to indicate external source system identifier (311, CRM, CX) | ||
| REVIEWER_ID | NUMBER | 18 | This column is used to indicate PERSON_ID key of the incident?s reviewer from table PER_USERS | ||
| ASSIGNED_ID | NUMBER | 18 | This column is used to indicate PERSON_ID key of the person assigned to work on incident from table PER_USERS | ||
| AGENCY_ID | NUMBER | 8 | This column is used to indicate unique agency id | ||
| PARENT_KEY | NUMBER | 18 | This column is used to indicate parent incident key | ||
| PARENT_ID | VARCHAR2 | 50 | This column is used to indicate parent incident Id | ||
| PARENT_TYPE | VARCHAR2 | 15 | This column is used to indicate logical identifier from record parent (incident, case) | ||
| PRIMARY_ISSUE_FLAG | VARCHAR2 | 1 | This column is used to indicate parent issue flag | ||
| OBSOLETE_FLAG | VARCHAR2 | 1 | This column is used to indicate obsolete flag | ||
| REFERRAL_CODE | VARCHAR2 | 100 | This column is used to indicate referral code | ||
| REVIEW_DUE_DATE | TIMESTAMP | This column is used to indicate review due date | |||
| SUBMIT_DATE | TIMESTAMP | This column is used to indicate submit date | |||
| CLOSED_DATE | TIMESTAMP | This column is used to indicate case close date | |||
| CLOSED_REASON | VARCHAR2 | 450 | This column is used to indicate case close reason | ||
| CLOSED_BY | VARCHAR2 | 64 | This column is used to indicate case closed by | ||
| REINSPECTION_REASON | VARCHAR2 | 30 | This column is used to store the reason for the reinspection | ||
| OBJECT_VERSION_NUMBER | NUMBER | 9 | Yes | Used to implement optimistic locking. This number is incremented every time that the row is updated. The number is compared at the start and end of a transaction to detect whether another session has updated the row since it was queried. | |
| LAST_UPDATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who last updated the row. | |
| LAST_UPDATE_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the last update of the row. | ||
| LAST_UPDATE_LOGIN | VARCHAR2 | 32 | Who column: indicates the session login associated to the user who last updated the row. | ||
| CREATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who created the row. | |
| CREATION_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the creation of the row. | ||
| MIGRATED_DATA_FLAG | VARCHAR2 | 3 | Indicates in-flight or historical transaction for data migration | ||
| LAST_GIS_SYNC_DATETIME | TIMESTAMP | Indicates the time stamp when data is last synced from OPAL to GIS |
Foreign Keys
| Table | Foreign Table | Foreign Key Column |
|---|---|---|
| PSC_CE_INCIDENT | psc_ce_intake | INTAKE_KEY |
| psc_ce_inc_status_hist | psc_ce_incident | TRANSACTION_KEY |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| psc_ce_incident_N1 | Non Unique | psc_ce_incident_N1 | SHAPE |
| psc_ce_incident_U1 | Unique | Default | INCIDENT_KEY |
| psc_ce_incident_U2 | Unique | Default | INCIDENT_PUID |