Skip Headers
Oracle® Clinical Stable Interface Technical Reference Manual
Release 5.0

E36999-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Feedback page
Contact Us

Go to previous page
Previous
PDF · Mobi · ePub

A Journal and History Tables

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:

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

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.

Journal Table Columns

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)

Tables First Audited in Oracle Clinical 4.5

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

CLINICAL_STUDY_HISTORY

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

DCMS_FFL_XML_HIST

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)

DISCREPANCY_ENTRY_REVIEW_HIST(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

FORM_LAYOUT_TEMPLATES_XML_HIST

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

PATIENT_POSITIONS_HISTORY

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


History Tables

This section describes the history tables that audit changes in Oracle Clinical base tables that are not documented in Chapter 2, "Table Definitions".

CLINICAL_STUDY_HISTORY

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

DCMS_FFL_XML_HIST

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)

FORM_LAYOUT_TEMPLATES_XML_HIST

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)

PATIENT_POSITIONS_HISTORY

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)

RECEIVED_PAGES_HISTORY

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