SVC_BI_AUDIT_SR
Captures SR audit data from the SR shadow table (SVC_SERVICE_REQUESTS_), adding useful attributes needed for BI reporting, for a select set of SR attributes that BI wants to report on.
Details
-
Schema: FUSION
-
Object owner: SVC
-
Object type: TABLE
-
Tablespace: Default
Primary Key
| Name | Columns |
|---|---|
|
SVC_BI_AUDIT_SR_PK |
AUDIT_SR_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| AUDIT_SR_ID | NUMBER | 18 | Yes | Auto-generated number and primary key for this table. | |
| SR_ID | NUMBER | 18 | Yes | Foreign key that references SVC_SERVICE_REQUESTS.SR_ID, the SR that the audit data belongs to. | |
| 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. | |
| AUDIT_SEQ_NO | NUMBER | 10 | Yes | Orders the audit records for a specific SR, with the first audit record having a value of 1, the next 2, etc. | |
| AUDIT_START_DATE | TIMESTAMP | Yes | The first date that the combined values of audited columns are valid from. | ||
| AUDIT_END_DATE | TIMESTAMP | The last date that the combined values of audited columns are valid up to, but not including. | |||
| AUDIT_DURATION_MS | NUMBER | 18 | The difference between Audit_end_date and Audit_start_date in milliseconds, the duration of time the combined values of audited columns are valid; excluding the audit_end_date itself. | ||
| LATEST_AUDIT_FLAG | VARCHAR2 | 1 | Yes | Identifies the latest audit record for an SR, a value of 'Y' for the record holding the current values of all audited columns, a value of 'N' for all other records. | |
| AUDIT_HISTORY_COMPLETE_FLAG | VARCHAR2 | 1 | Yes | Identifies SRs created after Auditing went live. SRs created before this date will not have a complete history of audit data (N), while those created after will (Y). Customers may want to filter out SRs with incomplete audit history. | |
| AUDIT_PURGE_DATE | TIMESTAMP | Reserved for future use, when purging of audit data is implemented. | |||
| OLD_STATUS_CD | VARCHAR2 | 30 | Previous audited value from SVC_SERVICE_REQUESTS_.STATUS_CD. | ||
| STATUS_CD | VARCHAR2 | 30 | Audited value from SVC_SERVICE_REQUESTS_.STATUS_CD. | ||
| OLD_STATUS_TYPE_CD | VARCHAR2 | 30 | Previous audited value from SVC_SERVICE_REQUESTS_.STATUS_TYPE_CD. | ||
| STATUS_TYPE_CD | VARCHAR2 | 30 | Audited value from SVC_SERVICE_REQUESTS_.STATUS_TYPE_CD. | ||
| OLD_SEVERITY_CD | VARCHAR2 | 30 | Previous audited value from SVC_SERVICE_REQUESTS_.SEVERITY_CD. | ||
| SEVERITY_CD | VARCHAR2 | 30 | Audited value from SVC_SERVICE_REQUESTS_.SEVERITY_CD. | ||
| OLD_ASSIGNEE_RESOURCE_ID | NUMBER | 18 | Previous audited value from SVC_SERVICE_REQUESTS_.ASSIGNEE_RESOURCE_ID. | ||
| ASSIGNEE_RESOURCE_ID | NUMBER | 18 | Audited value from SVC_SERVICE_REQUESTS_.ASSIGNEE_RESOURCE_ID. | ||
| OLD_QUEUE_ID | NUMBER | 18 | Previous audited value from SVC_SERVICE_REQUESTS_.QUEUE_ID. | ||
| QUEUE_ID | NUMBER | 18 | Audited value from SVC_SERVICE_REQUESTS_.QUEUE_ID. | ||
| STATUS_SEQ_NO | NUMBER | 10 | Orders the status code changes (including changes to null) for a specific SR, with the first audit record having a value of 1, the next audit record with a status code change has a value of 2, etc. | ||
| STATUS_TYPE_SEQ_NO | NUMBER | 10 | Orders the status type code changes (including changes to null) for a specific SR, with the first audit record having a value of 1, the next audit record with a status type code change has a value of 2, etc. | ||
| SEVERITY_SEQ_NO | NUMBER | 10 | Orders the severity code changes (including changes to null) for a specific SR, with the first audit record having a value of 1, the next audit record with a severity code change has a value of 2, etc. | ||
| ASSIGNEE_RESOURCE_SEQ_NO | NUMBER | 10 | Orders the assignee resource id changes (including changes to null) for a specific SR, with the first audit record having a value of 1, the next audit record with an assignee resource id change has a value of 2, etc. | ||
| QUEUE_SEQ_NO | NUMBER | 10 | Orders the queue id changes (including changes to null) for a specific SR, with the first audit record having a value of 1, the next audit record with a queue id change has a value of 2, etc. | ||
| REOPEN_EVENT_FLAG | VARCHAR2 | 1 | Identifies if the SR has been reopened in this audited SR change ('Y' if yes, Null if no). | ||
| SR_LAST_UPDATE_DATE | TIMESTAMP | Copied from SVC_SERVICE_REQUESTS_.LAST_UPDATE_DATE. For internal use only. | |||
| CAT_PROD_HISTORY_COMPLETE_FLAG | VARCHAR2 | 1 | Yes | Identifies SRs created after Auditing of Category and Product went live. SRs created before this date will not have a complete history of Category or Product audit data (N), while those created after will (Y). Customers may want to filter out SRs with incomplete audit history when reporting on Category and/or Product changes. | |
| OLD_CATEGORY_ID | NUMBER | 18 | Previous audited value from SVC_SERVICE_REQUESTS_.CATEGORY_ID. | ||
| CATEGORY_ID | NUMBER | 18 | Audited value from SVC_SERVICE_REQUESTS_.CATEGORY_ID. | ||
| CATEGORY_SEQ_NO | NUMBER | 10 | Orders the category id changes (including changes to null) for a specific SR, with the first audit record having a value of 1, the next audit record with a category id change has a value of 2, etc. | ||
| OLD_PRODUCT_ID | NUMBER | 18 | Previous audited value from SVC_SERVICE_REQUESTS_, derived as NVL(INVENTORY_ITEM_ID, PROD_GROUP_ID). | ||
| PRODUCT_ID | NUMBER | 18 | Audited value from SVC_SERVICE_REQUESTS_, derived as NVL(INVENTORY_ITEM_ID, PROD_GROUP_ID). | ||
| PRODUCT_SEQ_NO | NUMBER | 10 | Orders the product id changes (including changes to null) for a specific SR, with the first audit record having a value of 1, the next audit record with a product id change has a value of 2, etc. |
Foreign Keys
| Table | Foreign Table | Foreign Key Column |
|---|---|---|
| SVC_BI_AUDIT_SR | svc_service_requests | SR_ID |
| SVC_BI_AUDIT_SR | hz_parties | ASSIGNEE_RESOURCE_ID |
| SVC_BI_AUDIT_SR | svc_queues | QUEUE_ID |
| SVC_BI_AUDIT_SR | hz_parties | OLD_ASSIGNEE_RESOURCE_ID |
| SVC_BI_AUDIT_SR | svc_queues | OLD_QUEUE_ID |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| SVC_BI_AUDIT_SR_F2 | Non Unique | Default | ASSIGNEE_RESOURCE_ID |
| SVC_BI_AUDIT_SR_F3 | Non Unique | Default | QUEUE_ID |
| SVC_BI_AUDIT_SR_F4 | Non Unique | Default | OLD_ASSIGNEE_RESOURCE_ID |
| SVC_BI_AUDIT_SR_F5 | Non Unique | Default | OLD_QUEUE_ID |
| SVC_BI_AUDIT_SR_N1 | Non Unique | Default | SR_ID, LATEST_AUDIT_FLAG |
| SVC_BI_AUDIT_SR_N2 | Non Unique | Default | TRUNC("AUDIT_START_DATE") |
| SVC_BI_AUDIT_SR_N3 | Non Unique | Default | TRUNC("AUDIT_END_DATE") |
| SVC_BI_AUDIT_SR_N4 | Non Unique | Default | OLD_CATEGORY_ID |
| SVC_BI_AUDIT_SR_N5 | Non Unique | Default | CATEGORY_ID |
| SVC_BI_AUDIT_SR_N6 | Non Unique | Default | OLD_PRODUCT_ID |
| SVC_BI_AUDIT_SR_N7 | Non Unique | Default | PRODUCT_ID |
| SVC_BI_AUDIT_SR_PK | Unique | Default | AUDIT_SR_ID |
| SVC_BI_AUDIT_SR_U1 | Unique | Default | SR_ID, AUDIT_SEQ_NO |