| Oracle® Clinical Stable Interface Technical Reference Manual Release 5.0 E36999-01 |
|
|
PDF · Mobi · ePub |
| Oracle® Clinical Stable Interface Technical Reference Manual Release 5.0 E36999-01 |
|
|
PDF · Mobi · ePub |
Journal and history tables store audit records that enable you to track changes to Oracle Clinical data over time. Each type of auditing table is used in a particular situation:
Journal tables are used to track changes when the base table will have a relatively low volume of changes. For example, all NLS tables that store translations have journal tables, because translations are not frequently changed. The journal table keeps current and previous values of all columns in the base table. The journal table gets populated with a record when the operations INSERT, UPDATE or DELETE are carried out on the base table. Tables can also be self-journaling. See "Self-Journaling tables".
High volume tables will either be self-auditing or will use history tables. Each history and self-auditing table has rules particular to that table, while journal tables all follow the same rules. History tables do not capture all changes to a table, only those changes that are considered critical. Self-auditing tables also only capture changes to user-entered fields. Internal status tracking fields are not audited.
In general, tables are not journaled if they store any of the following:
Temporary, test, or derived data.
Data used to drive a data change if this data has not yet been used to do a data change.
Encrypted password data.
Data that cannot be manipulated from the Oracle Clinical front end.
Data that is used for system configuration but does not affect clinical data. For example, tables that specify directory mapping to allow users to view their job output.
Data for tracking internal status.
This appendix lists each table that has changes audited by a journal or history table.
Note:
There are no newly journaled tables in Oracle Clinical 4.6.Self-journaling tables store their own audit histories by storing the end timestamp of records. Records with an end timestamp of 3,000,000 Julian (15-AUG-3501) are considered current; records with a different END_TS value are audit records.
Any base table with an END_TS column is self-journaling. The stable interface synonym tables that are self journaling are ACTUAL_EVENTS(T), RECEIVED_DCIS(T), RECEIVED_DCMS(T), and RESPONSES(T).
Note:
Self journaling on patient data starts after the completion of Pass 1 for a study requiring Pass 1 data entry only and after completion of Pass 2 for a study requiring Pass 2 data entry.All journal table names end with the suffix $JN. Journal tables include all of the columns in their base table, as well as the following columns specific to journaling:
| Column Name | Null? | Datatype |
|---|---|---|
| JN_OPERATION The type of operation carried out on the base table. Possible values are: 'INS' for insert, 'UPD' for update and 'DEL' for delete. | Not Null | VARCHAR2(3) |
| JN_TIMESTAMP The date and time of this audit record. Populated with SYSDATE value. | Not Null | DATE |
| JN_SN The journal sequence number, which is unique for every operation. | Not Null | NUMBER(10) |
| JN_ORACLE_USER User who last modified the base table. | Not Null | VARCHAR2(30) |
| SYMMETRIC_REPLICATION_IND This column indicates whether the base table that this this journaling table audits is involved in symmetric replication. | Null | VARCHAR2(1) |
This section lists each Oracle Clinical base table that was audited for the first time in Release 4.5, and the name of the journal or history table that records its audit records. For journal tables, the journal table's name is usually the base table name concatenated with $JN; however, for some long base table names, the journal table uses a shortened version of the base table name (with the $JN suffix) to fit the table name length requirements of the Oracle database.
Table A-1 lists the journal and history tables and the base tables they audit. The five history tables (whose names end in _HIST or _HISTORY instead of $JN) that were new in Oracle Clinical 4.5 are described in more detail in "History Tables" or with the base tables in Chapter 2, "Table Definitions".
Table A-1 New Journal and History Tables For Oracle Clinical 4.5
| Base table | Journal table | First audited in Oracle Clinical 4.5? |
|---|---|---|
|
CLINICAL_PLANNED_EVENTS |
CLINICAL_PLANNED_EVENTS$JN |
Yes |
|
CLINICAL_STUDIES |
No |
|
|
CLINICAL_SUBJECTS |
CLINICAL_SUBJECTS$JN |
Yes |
|
DATA_CLARIFICATION_FORMS |
DATA_CLARIFICATION_FORMS$JN |
No |
|
DCF_PAGES |
DCF_PAGES$JN |
No |
|
DCIS |
DCIS$JN |
No |
|
DCI_BOOK_DCI_CONSTRAINTS |
DCI_BOOK_DCI_CONSTRAINTS$JN |
Yes |
|
DCI_BOOKS |
DCI_BOOKS$JN |
No |
|
DCI_BOOK_PAGES |
DCI_BOOK_PAGES$JN |
No |
|
DCI_BOOK_PHYSICAL_PAGES |
DCI_BOOK_PHYSICAL_PAGES$JN |
No |
|
DCI_FORM_VERSIONS |
DCI_FORM_VERSIONS$JN |
Yes |
|
DCI_MODULES |
DCI_MODULES$JN |
No |
|
DCI_MODULE_PAGES |
DCI_MODULE_PAGES$JN |
No |
|
DCMS |
DCMS$JN |
No |
|
DCMS |
Yes, by this history table |
|
|
DCM_QUESTIONS |
DCM_QUESTIONS$JN |
No |
|
DCM_QUESTION_GROUPS |
DCM_QUESTION_GROUPS$JN |
No |
|
DCM_QUES_REPEAT_DEFAULTS |
DCM_QUES_REPEAT_DEFAULTS$JN |
No |
|
DCM_SCHEDULES |
DCM_SCHEDULES$JN |
No |
|
DISCREPANCY_ENTRIES(T) |
No |
|
|
DISCRETE_VALUES |
DISCRETE_VALUES$JN |
No |
|
DISCRETE_VALUE_GROUPS |
DISCRETE_VALUE_GROUPS$JN |
No |
|
FORM_LAYOUT_TEMPLATES |
FORM_LAYOUT_TEMPLATES$JN |
Yes |
|
FORM_LAYOUT_TEMPLATES |
Yes |
|
|
LAB_RANGE_SUBSETS |
LAB_RANGE_SUBSETS$JN |
Yes |
|
LAB_TEST_QUESTION_UNITS |
LAB_TEST_QUESTION_UNITS$JN |
Yes |
|
LAB_UNIT_CONVERSIONS |
LAB_UNIT_CONVERSIONS$JN |
Yes |
|
LAB_UNITS |
LAB_UNITS$JN |
Yes |
|
LABS |
LABS$JN |
Yes |
|
OCL_INVESTIGATORS(T) |
OCL_INVESTIGATORS$JN |
Yes |
|
OCL_ORGANIZATION_UNITS |
OCL_ORGANIZATION_UNITS$JN |
Yes |
|
OCL_PROGRAMS |
OCL_PROGRAMS$JN |
Yes |
|
OCL_PROJECTS |
OCL_PROJECTS$JN |
Yes |
|
OCL_SITES |
OCL_SITES$JN |
Yes |
|
OCL_STUDIES |
OCL_STUDIES$JN |
Yes |
|
OCL_STUDY_SITE_ROLES(T) |
OCL_STUDY_SITE_ROLES$JN |
Yes |
|
OCL_STUDY_SITES |
OCL_STUDY_SITES$JN |
Yes |
|
ORACLE_ACCOUNTS |
ORACLE_ACCOUNTS$JN |
No |
|
PATIENT_POSITIONS |
Yes |
|
|
PLANNED_STUDY_INTERVALS |
PLANNED_STUDY_INTERVALS$JN |
Yes |
|
PREFERRED_LAB_UNITS |
PREFERRED_LAB_UNITS$JN |
Yes |
|
PROCEDURES |
PROCEDURES$JN |
No |
|
PROCEDURE_DETAILS |
PROCEDURE_DETAILS$JN |
No |
|
PROCEDURE_QUESTION_GROUPS |
PROCEDURE_QUESTION_GROUPS$JN |
No |
|
QUESTIONS |
QUESTIONS$JN |
No |
|
QUESTION_CATEGORY_RELATIONS |
QUESTION_CATEGORY_RELATIONS$JN |
No |
|
QUESTION_GROUPS |
QUESTION_GROUPS$JN |
No |
|
QUESTION_GROUP_QUESTIONS |
QUESTION_GROUP_QUESTIONS$JN |
No |
|
RANGES |
RANGES$JN |
Yes |
|
RECEIVED_PAGES(T) |
RECEIVED_PAGE_HISTORY(T) |
No |
|
REFERENCE_CODELISTS |
REFERENCE_CODELISTS$JN |
No |
|
REFERENCE_CODELIST_VALUES |
REFERENCE_CODELIST_VALUES$JN |
No |
|
REGIONS |
REGIONS$JN |
Yes |
|
STUDY_SITE_PATIENT_POSITIONS |
STUDY_SITE_PATIENT_POSITION$JN |
Yes |
|
TREATMENT_PATTERNS |
TREATMENT_PATTERNS$JN |
Yes |
|
VALIDATION_REPORTED_VALUES |
VALIDATION_REPORTED_VALUES$JN |
Yes |
This section describes the history tables that audit changes in Oracle Clinical base tables that are not documented in Chapter 2, "Table Definitions".
This table provides a record of certain major system-defined events in the course of a study as well as arbitrary user-recorded event about a clinical study.
Unless a description is provided next to the column name, the column stores the same information as its counterpart in the base table, CLINICAL_STUDIES.
| Column Name | Null? | Datatype |
|---|---|---|
| CLIN_STUDY_ID ID of the clinical study to which this history record applies. | Not Null | NUMBER(10) |
| CLIN_STUDY_HISTORY_ID Unique ID of this history record. | Not Null | NUMBER(10) |
| DATE_OCCURRED Date when this change to the study occurred. | Not Null | DATE |
| CLIN_STUDY_HISTORY_TYPE_CODE This column stores a value from the CLIN STUDY HISTORY TYPE CODE reference codelist. The special code USERDEF identifies the user-created history records, while the other codes are for history records that are automatically created when the matching event occurs for the study. | Not Null | VARCHAR2(7) |
| TEXT Description of the event. | Not Null | VARCHAR2(200) |
| CREATED_BY User who generated this history record; that is, the user who modified the clinical study record identified by the clin_study_id. | Not Null | VARCHAR2(30) |
| CREATION_TS Date and time when this history record was generated; that is, the user who modified the clinical study record identified by the clin_study_id. | Not Null | DATE |
| END_DATE Date and time when this version of the clinical study record was changed. | Null | DATE |
| PLANNED_STUDY_INTERVAL_ID ID of the study interval in the study schedule that is associated with the occurrence of the history record – specifically for the SECURIN type of CLIN_STUDY_HISTORY_TYPE_CODE. | Null | NUMBER(10) |
| RAND_ACC_STAT_TY_CODE_OLD The RAND_ACC_STAT_TYPE_CODE value before the change to this record. This code indicates who can access the study's randomization. | Null | VARCHAR2(7) |
| RAND_ACC_STAT_TY_CODE_NEW The RAND_ACC_STAT_TYPE_CODE value after the change to this record. This code indicates who can access the study's randomization. | Null | VARCHAR2(7) |
| STUDY_STAT_TY_CODE_OLD The STUDY_STATUS_TYPE_CODE value before the change to this record. This code indicates the status of the study in terms of planning, operation, or analysis. | Null | VARCHAR2(7) |
| STUDY_STAT_TY_CODE_NEW The STUDY_STATUS_TYPE_CODE value before the change to this record. This code indicates the status of the study in terms of planning, operation, or analysis. | Null | VARCHAR2(7) |
| MODIFIED_BY User who modified this clinical study record. | Null | VARCHAR2(30) |
| MODIFICATION_DATE Date and time when this clinical study record was modified. | Null | DATE |
This history table is populated with the current graphical layout whenever it is made available (its Available? column is set to Y).
| Column Name | Null? | Datatype |
|---|---|---|
| DCM_ID DCM-wide: A unique, system-generated ID for the DCM. | Not Null | NUMBER(10) |
| DCM_SUBSET_SN Subset-wide: A sequence number for a DCM subset. A DCM subset can be used to collect only some of the questions assigned to the base DCM. The base or initial DCM has a sequence number of 1. | Not null | NUMBER(3) |
| DCM_LAYOUT_SN Layout-specific: The DCM layout number. Layouts provide a way to present the same DCM subset in different formats for data entry — for instance, a DCM subset can have an English and a Japanese layout. Within a subset, the layouts are numbered starting with 1. | Not null | NUMBER(3) |
| CLINICAL_STUDY_ID DCM-wide: The ID of the clinical study to which the DCM belongs. | Not null | NUMBER(10) |
| FLT_XML A character large object that stores this DCM graphic layout. | Not null | CLOB |
| FLT_XML_MODIFICATION_TS Date and time when this DCM Form layout was last modified. Modifications include edits, updates, and generations. | Not null | DATE |
| FLT_XML_MODIFIED_BY User who last modified the DCM Form Layout. | Not null | VARCHAR2(30) |
This history table is populated with current graphical layout and related information when status is set to A.
| Column Name | Null? | Datatype |
|---|---|---|
| FLT_ID Unique ID of this form layout template. | Not Null | NUMBER(10) |
| FLT_XML A character large object that stores the XML for this form layout template. | Not Null | CLOB |
| FL_XML_MODIFICATION_TS Date and time when the XML for this FLT was last modified. | Not Null | DATE |
| FL_XML_MODIFIED_BY User who last modified the XML for this FLT. | Not Null | VARCHAR2(30) |
This history table records changes to the records in the PATIENT_POSITIONS(T) table. In release 4.5, it tracks changes to four columns that were not previously audited: DCI_BOOK_ID, FREEZE_FLAG, REPLACEMENT_POSITION_FLAG, and SCREENING_POSITION_FLAG.
Unless a description is provided next to the column name, the column stores the same information as its counterpart in the base table, PATIENT_POSITIONS(T).
| Column Name | Null? | Datatype |
|---|---|---|
| CLINICAL_STUDY_ID | Not null | NUMBER(10) |
| PATIENT_POSITION_ID | Not null | NUMBER(10) |
| PATIENT | Not null | VARCHAR2(10) |
| CLINICAL_STUDY_VERSION_ID | Not null | NUMBER(10) |
| DCI_BOOK_ID | Null | NUMBER |
| FREEZE_FLAG | Null | VARCHAR2(1) |
| INC_IN_EFF_ANALYSIS_FLAG | Null | VARCHAR2(1) |
| INC_IN_SAFETY_ANALYSIS_FLAG | Null | VARCHAR2(1) |
| REPLACEMENT_POSITION_FLAG | Null | VARCHAR2(1) |
| SCREENING_POSITION_FLAG | Null | VARCHAR2(1) |
| EARLY_TERMINATION_FLAG | Null | VARCHAR2(1) |
| PATIENT_ENROLLMENT_DATE | Null | DATE |
| CLINICAL_SUBJECT_ID | Null | NUMBER(10) |
| INCLUSION_EXCLUSION_DATE | Null | DATE |
| EXCLUDE_FROM_EFFICACY_REASON | Null | VARCHAR2(200) |
| REPORTED_FIRST_NAME | Null | VARCHAR2(15) |
| REPORTED_LAST_NAME | Null | VARCHAR2(20) |
| REPORTED_SEX | Null | VARCHAR2(1) |
| REPORTED_PATIENT_REFERENCE | Null | VARCHAR2(25) |
| REPORTED_INITIALS | Null | VARCHAR2(4) |
| REPORTED_BIRTH_DATE | Null | DATE |
| REPORTED_DEATH_DATE | Null | DATE |
| RANDOMIZATION_COMMENT | Null | VARCHAR2(200) |
| REPORTED_DATE_LAST_PREGNANCY | Null | DATE |
| FIRST_SCREENING_DATE | Null | DATE |
| INFORMED_CONSENT_DATE | Null | Date |
| TERMINATION_DATE | Null | DATE |
| MODIFICATION_TS | Null | DATE |
| AUDIT_COMMENT Identifies the audit row as an update ('UPG') or a deletion ('DEL'). | Null | VARCHAR2(10) |
This history table records changes to the records in the RECEIVED_PAGES(T) table.
Unless a description is provided next to the column name, the column stores the same information as its counterpart in the base table, RECEIVED_PAGES(T).
| Column Name | Null? | Datatype |
|---|---|---|
| RECEIVED_PAGE_ID | Not null | NUMBER(10) |
| RECEIVED_DCI_ID | Not null | NUMBER(10) |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) |
| PAGE_STATUS | Not null | VARCHAR2(15) |
| HAS_DATA_FLAG | Not null | VARCHAR2(1) |
| RELATIVE_TO_DCI_PAGE | Not null | NUMBER(4) |
| BOOK_PAGE | Not null | VARCHAR2(15) |
| COMMENT_TEXT | Null | VARCHAR2(200) |
| CREATED_BY | Not null | VARCHAR2(30) |
| CREATION_TS | Not null | DATE |
|
 Copyright © 2001, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
