ZCH_CLNS_LOC_RESULTS
This table stores Cleanse Results.
Details
-
Schema: FUSION
-
Object owner: ZCH
-
Object type: TABLE
-
Tablespace: FUSION_TS_TX_DATA
Primary Key
| Name | Columns |
|---|---|
|
ZCH_CLNS_LOC_RESULTS_PK |
CLNS_LOC_RESULT_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| CLNS_LOC_RESULT_ID | NUMBER | 18 | Yes | Primary key | |
| DATA_MGMT_BATCH_ID | NUMBER | 18 | Yes | Foreign key to batch table | |
| RECORD_ID | NUMBER | 18 | Yes | Record Id | |
| ADDRESS1 | VARCHAR2 | 240 | Cleansed Attribute | ||
| ADDRESS2 | VARCHAR2 | 240 | Cleansed Attribute | ||
| ADDRESS3 | VARCHAR2 | 240 | Cleansed Attribute | ||
| ADDRESS4 | VARCHAR2 | 240 | Cleansed Attribute | ||
| CITY | VARCHAR2 | 60 | Cleansed Attribute | ||
| COUNTY | VARCHAR2 | 60 | Cleansed Attribute | ||
| STATE | VARCHAR2 | 60 | Cleansed Attribute | ||
| PROVINCE | VARCHAR2 | 60 | Cleansed Attribute | ||
| COUNTRY | VARCHAR2 | 60 | Cleansed Attribute | ||
| POSTAL_CODE | VARCHAR2 | 60 | Cleansed Attribute | ||
| PROCESS_STATUS_CODE | VARCHAR2 | 60 | Cleansed status based on return value from vendor | ||
| USAGE_STATUS_CODE | VARCHAR2 | 60 | Post process status to indicate if successfully inserted into TCA or not | ||
| PROCESS_MSG_CODE | VARCHAR2 | 200 | The message returned from process. | ||
| MSG_TEXT | VARCHAR2 | 2000 | The actual message | ||
| CREATION_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the creation of the row. | ||
| CREATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who created the row. | |
| LAST_UPDATE_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the last update of the row. | ||
| LAST_UPDATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who last updated the row. | |
| LAST_UPDATE_LOGIN | VARCHAR2 | 32 | Who column: indicates the session login associated to the user who last updated the row. | ||
| 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. | |
| POSTAL_PLUS4_CODE | VARCHAR2 | 20 | Cleansed Attribute | ||
| ADDR_ELEMENT_ATTRIBUTE2 | VARCHAR2 | 150 | Cleansed Attribute | ||
| ADDR_ELEMENT_ATTRIBUTE3 | VARCHAR2 | 150 | Cleansed Attribute | ||
| FORMATTED_ADDRESS | VARCHAR2 | 2000 | Formatted cleansed address | ||
| ORIG_FORMATTED_ADDRESS | VARCHAR2 | 2000 | Formatted original address | ||
| ORIG_ADDRESS1 | VARCHAR2 | 240 | Original address1 | ||
| ORIG_ADDRESS2 | VARCHAR2 | 240 | Original address2 | ||
| ORIG_ADDRESS3 | VARCHAR2 | 240 | Original address3 | ||
| ORIG_ADDRESS4 | VARCHAR2 | 240 | Original address4 | ||
| ORIG_CITY | VARCHAR2 | 60 | Original city | ||
| ORIG_COUNTY | VARCHAR2 | 60 | Original County | ||
| ORIG_STATE | VARCHAR2 | 60 | Original state | ||
| ORIG_PROVINCE | VARCHAR2 | 60 | Original province | ||
| ORIG_COUNTRY | VARCHAR2 | 60 | Original country | ||
| ADDRESS1_LABEL | VARCHAR2 | 240 | Address1 label | ||
| ADDRESS2_LABEL | VARCHAR2 | 240 | Address2 label | ||
| ADDRESS3_LABEL | VARCHAR2 | 240 | Address3 label | ||
| ADDRESS4_LABEL | VARCHAR2 | 240 | Address4 label | ||
| CITY_LABEL | VARCHAR2 | 240 | City label | ||
| COUNTY_LABEL | VARCHAR2 | 240 | County label | ||
| STATE_LABEL | VARCHAR2 | 240 | State label | ||
| PROVINCE_LABEL | VARCHAR2 | 240 | Province label | ||
| COUNTRY_LABEL | VARCHAR2 | 240 | Country label | ||
| POSTAL_CODE_LABEL | VARCHAR2 | 240 | Postal code label | ||
| ADDR_ELEMENT_ATTRIBUTE2_LABEL | VARCHAR2 | 240 | Address element attribute2 label | ||
| ADDR_ELEMENT_ATTRIBUTE3_LABEL | VARCHAR2 | 240 | Address element attribute3 label | ||
| RECORD_VERSION | NUMBER | 18 | This should be the OBJECT_VERSION_NUMBER of the address that retrieved from HZ_LOCATIONS. Currently, we store BATCH_REQ_REC_VERSION values when inserting into ZCQ_BT_INPUT_IDS table. | ||
| ORIG_VERSION | NUMBER | 18 | This is the OBJECT_VERSION_NUMBER of the original address values that we are going to retrieve from HZ_LOCATIONS after the DQ result has been returned. | ||
| ORIG_POSTAL_CODE | VARCHAR2 | 60 | Original postal code | ||
| ORIG_POSTAL_PLUS4_CODE | VARCHAR2 | 20 | Original postal plus4 code | ||
| ORIG_ADDR_ELEMENT_ATTRIBUTE2 | VARCHAR2 | 150 | Original address element attribute2 | ||
| ORIG_ADDR_ELEMENT_ATTRIBUTE3 | VARCHAR2 | 150 | Original address element attribute3 | ||
| JOB_ID | NUMBER | 18 | A unique identifier assigned for clustering records for parallel processing | ||
| PARENT_RECORD_ID | NUMBER | 18 | The identifier of parent record for this address. |
Foreign Keys
| Table | Foreign Table | Foreign Key Column |
|---|---|---|
| ZCH_CLNS_LOC_RESULTS | zch_data_mgmt_batches_b | DATA_MGMT_BATCH_ID |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| ZCH_CLNS_LOC_RESULTS_N1 | Non Unique | Default | DATA_MGMT_BATCH_ID, USAGE_STATUS_CODE |
| ZCH_CLNS_LOC_RESULTS_N2 | Non Unique | Default | RECORD_ID |
| ZCH_CLNS_LOC_RESULTS_N3 | Non Unique | Default | DATA_MGMT_BATCH_ID, COUNTRY |
| ZCH_CLNS_LOC_RESULTS_PK | Unique | Default | CLNS_LOC_RESULT_ID |