PSC_CE_CASE_CONTACT
Stores the contact details for code enforcement cases.
Details
-
Schema: FUSION
-
Object owner: PSC_CE
-
Object type: TABLE
-
Tablespace: Default
Primary Key
| Name | Columns |
|---|---|
|
PSC_CE_CASE_CONTACT_PK |
CASE_CONTACT_KEY |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| CASE_CONTACT_KEY | NUMBER | 18 | Yes | Unique key to identify a case contact record. | |
| CASE_CONTACT_PUID | VARCHAR2 | 480 | Yes | Indicates the public unique indentifier for the case contact. | |
| CASE_KEY | NUMBER | 18 | Yes | Indicates the case associated with this case contact record. | |
| CASE_PUID | VARCHAR2 | 480 | Yes | Indicates the public unique identifier of the case. | |
| CONTACT_TYPE | VARCHAR2 | 15 | Yes | Indicates the type of case contact. | |
| PRIMARY_FLAG | VARCHAR2 | 1 | Yes | Indicates that the contact is the primary contact for the case. | |
| STATUS | VARCHAR2 | 1 | Status indicating if the record is Active or Inactive | ||
| CONTACT_PARTY_ID | NUMBER | 18 | This column is to indicate the contact party id. | ||
| ACCESS_STATUS | VARCHAR2 | 30 | Indicates the access status of a case contact to a case. | ||
| ACCESS_COMMENTS | VARCHAR2 | 1000 | Indicates the comments entered by the case contact while requesting access for a case. | ||
| ACCESS_APPROVED_DATE | TIMESTAMP | Access approval date | |||
| ACCESS_APPROVED_BY | VARCHAR2 | 64 | Identifies the user approving the online access request | ||
| LEGACY_APPROVER_NAME | VARCHAR2 | 100 | Used for data migration rows. Approver name from the legacy system | ||
| AGENCY_COMMENTS | VARCHAR2 | 1000 | Indicates the comments by agency staff user | ||
| PERSON_FIRST_NAME | VARCHAR2 | 150 | Yes | This column is used to indicate the contact first name. | |
| PERSON_MIDDLE_NAME | VARCHAR2 | 60 | This column is used to indicate person middle name. | ||
| PERSON_LAST_NAME | VARCHAR2 | 150 | Yes | This column is used to indicate person last name. | |
| PERSON_NAME_SUFFIX | VARCHAR2 | 30 | This column is used to indicate person name suffix. | ||
| PERSON_TITLE | VARCHAR2 | 60 | This column is used to indicate person title. | ||
| EMAIL_ADDRESS | VARCHAR2 | 254 | Indicates the email id of the case contact. | ||
| BUSINESS_NAME | VARCHAR2 | 254 | Indicates the business name of the case contact. | ||
| PHONE_COUNTRY_CODE | VARCHAR2 | 10 | International country code for a telephone number | ||
| PHONE_AREA_CODE | VARCHAR2 | 10 | The area code within a country code. | ||
| PHONE_NUMBER | VARCHAR2 | 100 | Indicates the phone number of the case contact. | ||
| PHONE_EXTENSION | VARCHAR2 | 20 | Extension number for a telephone number | ||
| USER_FIRST_NAME | VARCHAR2 | 150 | Indicates the first name of the user who requested access to the case. | ||
| USER_LAST_NAME | VARCHAR2 | 150 | Indicates the last name of the user who requested access to the case. | ||
| USER_EMAIL | VARCHAR2 | 254 | Identifies the requester or claimant email address | ||
| USER_PHONE_COUNTRY_CODE | VARCHAR2 | 10 | Identifies the claimant user phone country code | ||
| USER_PHONE_AREA_CODE | VARCHAR2 | 10 | The area code within a country code. | ||
| USER_PHONE_NUMBER | VARCHAR2 | 100 | Identifies the claimant user phone number | ||
| USER_PHONE_EXTENSION | VARCHAR2 | 20 | Identifies the claimant user phone extension | ||
| ADDRESS1 | VARCHAR2 | 240 | This column is used to indicate address1. | ||
| ADDRESS2 | VARCHAR2 | 240 | This column is used to indicate address2 | ||
| ADDRESS3 | VARCHAR2 | 240 | This column is used to indicate address3 | ||
| ADDRESS4 | VARCHAR2 | 240 | This column is used to indicate address4 | ||
| CITY | VARCHAR2 | 60 | This column is used to indicate city | ||
| STATE | VARCHAR2 | 60 | This column is used to indicate state. | ||
| PROVINCE | VARCHAR2 | 60 | This column is used to indicate province. | ||
| COUNTY | VARCHAR2 | 60 | This column is used to indicate county. | ||
| COUNTRY | VARCHAR2 | 3 | This column is used to indicate country code. | ||
| POSTAL_CODE | VARCHAR2 | 60 | This column is used to indicate postal code. | ||
| POSTAL_PLUS4_CODE | VARCHAR2 | 10 | This column is used to indicate postal plus4 code | ||
| MIGRATED_DATA_FLAG | VARCHAR2 | 3 | Indicates in-flight or historical transaction for data migration. | ||
| 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. | |
| 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_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. |
Foreign Keys
| Table | Foreign Table | Foreign Key Column |
|---|---|---|
| PSC_CE_CASE_CONTACT | psc_ce_case | CASE_KEY |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| PSC_CE_CASE_CONTACT_N1 | Non Unique | Default | CASE_KEY |
| PSC_CE_CASE_CONTACT_PK | Unique | Default | CASE_CONTACT_KEY |
| PSC_CE_CASE_CONTACT_U1 | Unique | Default | CASE_CONTACT_PUID |