PSC_CE_CASE
This table contains case transaction record data.
Details
-
Schema: FUSION
-
Object owner: PSC_CE
-
Object type: TABLE
-
Tablespace: FUSION_TS_TX_DATA
Primary Key
| Name | Columns |
|---|---|
|
PSC_CE_CASE_PK |
CASE_KEY |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| CASE_KEY | NUMBER | 18 | Yes | This column is used to indicate unique id for the case. Use Autosequencing | |
| CASE_PUID | VARCHAR2 | 480 | Yes | This column is used to indicate the public unique identifier of the case. | |
| CASE_ID | VARCHAR2 | 50 | Yes | This column is used to indicate a logical id for the case transaction | |
| CASE_PIN | VARCHAR2 | 5 | This column indicates the PIN generated for a case. | ||
| INTAKE_KEY | NUMBER | 18 | Yes | This column is used to indicate used as key from parent record PSC_CE_CASE_MY | |
| INTAKE_PUID | VARCHAR2 | 480 | Yes | This column is used to indicate the public unique identifier of the intake | |
| INTAKE_SOURCE | VARCHAR2 | 30 | Yes | This column is used to indicate case source transaction type (incident, inspection, sweep, inspector, etc) | |
| SOURCE_TRX_ID | VARCHAR2 | 50 | This column is used to indicate a logical id from the source transaction | ||
| 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 | This column is used to indicate issue description | ||
| FUNC_STATUS | VARCHAR2 | 15 | Yes | This column is used to indicate user defined functional status. | |
| STATUS | VARCHAR2 | 15 | Yes | This column is used to indicate case status | |
| REPORTED_BY_ID | VARCHAR2 | 64 | This column is used to indicate reported person id | ||
| MIGRATED_DATA_FLAG | VARCHAR2 | 3 | Indicates in-flight or historical transaction for data migration. | ||
| 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. | |||
| 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 | ||
| PARCEL_ID_ENTERED | VARCHAR2 | 50 | This column is used to indicate the parcel id entered. | ||
| DISTRICT_ID | VARCHAR2 | 30 | This column is used to indicate destrict Id | ||
| DISTRICT_TYPE | VARCHAR2 | 50 | This column is used to store district type. | ||
| LOCATION_DESCR | VARCHAR2 | 1000 | Indicates the location description of the case. | ||
| ASSIGNED_ID | NUMBER | 18 | This column is used to indicate PROFILE_ID key of the person assigned to work on incident from table psc_pbl_profile | ||
| REVIEWER_ID | NUMBER | 18 | Indicates the profile id of the code technician assigned for the case. | ||
| INTERNAL_PRIORITY | VARCHAR2 | 30 | This column is used to indicate internal priority (high, medium, low) | ||
| CRITICAL_FLAG | VARCHAR2 | 1 | This column is used to indicate critical flag | ||
| PRIORITY | VARCHAR2 | 30 | This column is used to indicate incident severity code (CRITICAL S1 ,MAJOR S2, MODERATE S3 ,LOW S4) Dropdown value | ||
| OBSOLETE_FLAG | VARCHAR2 | 1 | This column is used to indicate rows with malicious or inappropriate information. | ||
| REFERRAL_CODE | VARCHAR2 | 100 | This column is used to indicate referral code | ||
| AGENCY_ID | NUMBER | 8 | This column is used to indicate unique agency id. | ||
| SUBMIT_DATE | TIMESTAMP | Yes | This column is used to indicate case 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 | ||
| COMPLIANCE_DUE_DATE | DATE | This column is used to indicate compliance due date | |||
| COMPLIED_ON | DATE | This column is used to indicate complied on date | |||
| APPEAL_DUE_DATE | DATE | This column is used to indicate case appeal due date. | |||
| 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. | ||
| 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_CASE | psc_ce_intake | INTAKE_KEY |
| psc_ce_case_comment | psc_ce_case | CASE_KEY |
| psc_ce_case_notice | psc_ce_case | CASE_KEY |
| psc_ce_fee_record | psc_ce_case | CASE_KEY |
| psc_ce_case_code_ref | psc_ce_case | SOURCE_TRANSACTION_KEY |
| psc_ce_case_history | psc_ce_case | CASE_KEY |
| psc_ce_case_contact | psc_ce_case | CASE_KEY |
| psc_ce_case_attachment | psc_ce_case | CASE_KEY |
| psc_ce_citation | psc_ce_case | CASE_KEY |
| psc_ce_case_appeal | psc_ce_case | CASE_KEY |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| psc_ce_case_N1 | Non Unique | Default | REVIEWER_ID |
| psc_ce_case_N2 | Non Unique | psc_ce_case_N2 | SHAPE |
| psc_ce_case_U1 | Unique | Default | CASE_KEY |
| psc_ce_case_U2 | Unique | Default | CASE_PUID |