| 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 |
This chapter describes each table supported by the stable interface. You should access these tables only by their synonyms, which are described in Chapter 1, "About the Stable Interface."
Many of the tables in the stable interface have test database counterparts that can be used to enter test data. Test data is stored separately from the production data, so test tables enable you to modify data in ways not allowed in the production interface. In this section, tables that have test data counterparts are designated by a (T) at the end of the table's title. ACTUAL_EVENTS(T) therefore describes the production table ACTUAL_EVENTS and the test table ACTUAL_EVENTST.
Each table description includes the following information:
A general description of the table.
The table's primary key.
The table's unique keys, where applicable.
A listing of related tables and foreign keys and other functional relations.
Descriptions of the columns in the tables.
Note:
A number of fields within Oracle Clinical are optional — for example, the clinical phase of a planned study. In this section their expected meaning is given; however, there can be no guarantee that they will be maintained or used as expected.A study design includes a set of clinical planned events (CPEs). These are defined once for a study. An actual event is created the first time data is collected about a patient at a CPE. Actual events are also identified by a sub-event number. The actual event corresponding to the CPE is referred to as Actual Event 0 (zero) and is identified by sub-event number 0. If the patient returns for additional unplanned visits and additional CRFs are completed for the patient, they are recorded as different sub-event numbers, or actual events, or visits x.1, x.2, etc., where visit x is the nearest previous planned actual event.
Actual events are used in Validation Procedures to associate DCMs that occur at the same event. Actual events can also be used to label events for future reference — for instance, to describe why an unplanned visit occurred. When the last data record that references an actual event is soft-deleted, the associated actual event is soft-deleted by setting its end timestamp to the date of deletion. If all data that references an actual event is hard-deleted, the actual event is hard-deleted as well. Refer to "Self-Journaling Tables" for more information.
The test table for ACTUAL_EVENTS is ACTUAL_EVENTST.
Unique Keys PATIENT_POSITION_ID, CLIN_PLAN_EVE_ID, SUBEVENT_NUMBER
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
| PATIENT_POSITIONS | PATIENT_POSITION_ID | PATIENT_POSITION_ID |
| CLINICAL_PLANNED_EVENTS | CLIN_PLAN_EVE_ID | CLIN_PLAN_EVE_ID |
| RECEIVED_DCIS | ACTUAL_EVENT_ID | ACTUAL_EVENT_ID |
| RECEIVED_DCMS | ACTUAL_EVENT_ID | ACTUAL_EVENT_ID |
| DISCREPANCY_ENTRIES | ACTUAL_EVENT_ID | ACTUAL_EVENT_ID |
ACTUAL_EVENTS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| ACTUAL_EVENT_ID | Not null | NUMBER(10) | A unique, system-generated ID for the actual event. |
| CREATION_TS | Not null | DATE | Date and time when this actual event record was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this actual event record. |
| PATIENT_POSITION_ID | Not null | NUMBER(10) | The ID of the patient position for the patient who attended the actual event. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which the actual event belongs. |
| END_TS | Not null | DATE | Date and time that this actual event record was changed. This column is for journaling purposes. |
| CLIN_PLAN_EVE_ID | Not null | NUMBER(10) | The ID of the clinical planned event to which the actual event is assigned. |
| SUBEVENT_NUMBER | Not null | NUMBER(2) | A sequence number for this actual event within the set of actual events linked to a particular clinical planned event. By convention, the sub-event number is equal to 0 for the planned actual event. |
| COMMENT_TEXT | Null | VARCHAR2(200) | Freeform text about the actual event. |
| MODIFICATION_TS | Null | DATE | Date and time when this actual event was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this actual event. |
A record of batch validation (or Data Management) runs. The most recent record for a particular clinical study can be identified by Current Flag = Y. The batch start timestamp of successful runs is used to define stable data extract views and data extract snapshots. The batch start timestamp of the most recent successful run is frequently referred to in other tables as LAST_BATCH_TS.
Primary Key CLINICAL_STUDY_ID, CREATION_TS
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
BATCH_DM_RUNS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study in which the batch validation process was run. |
| CREATION_TS | Not null | DATE | Date and time when this batch validation process occurred. |
| CREATED_BY | Not null | VARCHAR2(30) | User who initiated this batch validation process. |
| BATCH_END_TS | Not null | DATE | The date and time that the batch validation process successfully completed. |
| SUCCESS_FLAG | Not null | VARCHAR(1) | A flag to show whether the batch validation process ended successfully. |
| MODIFIED_PATIENT_COUNT | Not null | NUMBER(7) | The number of patients processed by the batch validation process. |
| BATCH_START_TS | Not null | DATE | The date and time the process started. Used as the reference timestamp for other parts of Oracle Clinical, such as Data Extract, which rely upon the successful completion of the batch validation to ensure consistency between entered data and derived data. |
| CURRENT_FLAG | Not null | VARCHAR2(1) | A flag to indicate if this is the most recent record for the clinical study. |
| DATA_REVISION_SN | Not null | NUMBER(10) | A study-specific sequence number used to identify the batch validation run. Used to identify the latest batch validation in which a patient was processed. |
| LAST_DCD_REPLICATION_TS | Null | DATE | At non-owning locations of distributed studies, the most recent previous replication timestamp of study definition (data collection definition) information. Used to ensure that changes made to study definitions at the owning location are applied to data at the non-owning location. |
| LOCATION_CODE | Not null | VARCHAR2(15) | A code for the instance where the batch job was run. |
Clinical planned events (CPEs) represent points in the timeline of a study when data is scheduled to be collected. They normally equate to visits. Each clinical planned event must have either the offsets from interval start or the offsets from previous event defined.
Unique Keys CLINICAL_STUDY_ID, VISIT_NUMBER
CLINICAL_STUDY_ID, NAME
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
| PLANNED_STUDY_INTERVALS | PLAN_STUDY_INTERVAL_ID | PLAN_STUDY_INT_ID |
| DISCREPANCY_ENTRIES | CLIN_PLAN_EVE_ID | CLIN_PLAN_EVE_ID |
| PROCEDURE_QUESTION_GROUPS | NAME | CPE_BEGIN_ACCESS_NAME |
| PROCEDURE_QUESTION_GROUPS | NAME | CPE_END_ACCESS_NAME |
| DCI_MODULES | CLIN_PLAN_EVE_ID | CLIN_PLAN_EVE_ID |
| RECEIVED_DCIS | CLIN_PLAN_EVE_ID | CLIN_PLAN_EVE_ID |
| RECEIVED_DCMS | CLIN_PLAN_EVE_ID | CLIN_PLAN_EVE_ID |
| ACTUAL_EVENTS | CLIN_PLAN_EVE_ID | CLIN_PLAN_EVE_ID |
| DCM_SCHEDULE | CLIN_PLAN_EVE_ID | CLIN_PLAN_EVE_ID |
| DCI_BOOK_PAGES | CLIN_PLAN_EVE_ID | CLIN_PLAN_EVE_ID |
CLINICAL_PLANNED_EVENTS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| CLIN_PLAN_EVE_ID | Not null | NUMBER(10) | A code for the instance where the batch job was run. |
| PLAN_STUDY_INTERVAL_ID | Not null | NUMBER(10) | The ID of the planned study interval to which the CPE is assigned. |
| CLIN_PLAN_PROCESSES_ID | Not null | NUMBER(10) | The ID of the clinical planned process to be performed at the CPE. |
| CLIN_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which the CPE belongs. |
| CLIN_STUDY_VERSION_ID | Not null | NUMBER(10) | The ID of the clinical study version to which the CPE belongs. |
| CLIN_PLAN_EVE_TYPE_CODE | Not null | VARCHAR2(7) | The type of CPE. From the reference codelist CLIN_PLAN_EVE_TYPE_CODE. The only valid value at the moment is VISIT. |
| OPTIONAL_FLAG | Not null | VARCHAR2(1) | A flag to show if the CPE is optional for patients in the study. If a CPE is flagged as optional, then the DCIs for that CPE are never considered missing. |
| NAME | Not null | VARCHAR2(16) | A free form text name of the clinical planned event. It is unique within the study. |
| CLIN_PLAN_EVE_SN | Not null | NUMBER(5) | Event sequence number. The system maintains the value of this column such that it is always identical to visit_number. |
| DISPENSING_VISIT_FLAG | Not null | VARCHAR2(1) | A flag to show whether medication is to be dispensed to patients in the study at the clinical planned event (visit). |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this CPE record. |
| CREATION_TS | Not null | DATE | Date and time when this CPE was created. |
| VISIT_NUMBER | Null | NUMBER(5) | Within this study, a unique integer number that is associated with the clinical planned event. |
| DESCRIPTION | Null | VARCHAR2(70) | A freeform text description, in mixed case, of the clinical planned event (visit). |
| TRIGGER_EVENT | Null | VARCHAR2(200) | A free form text description, in mixed case, of the conditions that trigger an optional clinical planned event to occur. |
| MIN_OFFSET_FROM_START | Null | NUMBER(10) | The minimum length of time before this clinical planned event (visit) can occur from the start of the interval to which it is assigned. The time unit is defined by the time unit of the interval. |
| MAX_OFFSET_FROM_START | Null | NUMBER(10) | The maximum length of time by which this clinical planned event (visit) must occur from the start of the interval to which it is assigned. The time unit is defined by the time unit of the interval. |
| MIN_OFFSET_FROM_PREV | Null | NUMBER(10) | The minimum length of time before this clinical planned event (visit) can occur from the end of the previous clinical planned event. The time unit is defined by the time unit of the interval. |
| MAX_OFFSET_FROM_PREV | Null | NUMBER(10) | The maximum length of time by which this clinical planned event (visit) must occur from the end of the previous clinical planned event. The time unit is defined by the time unit of the interval. |
| MIN_OFFSET_IN_STUDY | Null | NUMBER(10) | The minimum length of time before this clinical planned event (visit) can occur from the start of the study. This derived number is always calculated in days. |
| MAX_OFFSET_IN_STUDY | Null | NUMBER(10) | The maximum length of time by which this clinical planned event (visit) must occur from the start of the study. This derived number is always calculated in days. |
| TERMINATION_VISIT_TYPE_CODE | Null | VARCHAR2(15) | A code to indicate the normal termination visit and the visit to jump to in the case of an early termination. Other CPEs have a null value. Valid values are NORMAL TERM (which means the normal termination visit) and EARLY TERM (for the early termination visit). |
| LABEL_CODE | Null | VARCHAR2(10) | A freeform text code for labels, to identify this clinical planned event — for example, all supplies handed out at the first visit could be labeled with an A, at the second visit with a B. This is done to help prevent mistakes in dispensing supplies. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this CPE record. |
| MODIFICATION_TS | Null | DATE | Date and time when this CPE was last modified. |
Clinical study records contain basic information about the study. The primary key of a clinical study is the clinical study ID; the primary key of an OCL study is the task ID. They are normally the same but may differ in some cases. They should be joined by using the study code.
SHORT_TITLE
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| OCL_STUDIES | STUDY | STUDY |
| Many child tables | CLINICAL_STUDY_ID | STUDY |
CLINICAL_STUDIES Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | A unique, system-generated ID for the clinical study. |
| STUDY | Not null | VARCHAR2(15) | A unique code for the clinical study. |
| SHORT_TITLE | Not null | VARCHAR2(80) | A unique short title of the study. |
| RAND_ACC_STAT_TYPE_CODE | Not null | VARCHAR2(7) | A code to indicate who can access the study's randomization. Possible values come from the reference codelist RAND_ACC_STAT_TYPE_CODE.Values for RAND_ACC_STAT_TYPE_CODE are OPEN (everyone has always had access), CLOSED (only privileged users have access), ACCESS (access to named users while in this state), RELEASE (everyone has access at end of study) and MULT (access controlled at the Phase level). |
| STUDY_STATUS_TYPE_CODE | Not null | VARCHAR2(7) | A user-defined code for the status of the study in terms of planning, operation, or analysis. |
| FDA_PACKAGE_FLAG | Not null | VARCHAR2(1) | A flag to indicate if the study is intended for inclusion in the FDA filing package. |
| PIVOTAL_STUDY_FLAG | Not null | VARCHAR2(1) | A flag to indicate if the study is a pivotal part of the program. Pivotal parts of the program are those whose successful outcome to the study is required to continue work on the new compound. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this study. |
| CREATION_TS | Not null | DATE | Date and time when this study was created. |
| STUDY_SUB_TYPE_CODE | Not null | VARCHAR2(4) | A code for the type of study. Clinical studies have a type of 1. |
| AVAIL_FOR_REPLICATION_FLAG | Not null | VARCHAR2(1) | A flag to show if the study can be replicated to other locations. |
| PHONE_RANDOMIZATION_FLAG | Not null | VARCHAR2(1) | A flag to show if the randomization in the study can be made available to a phone randomization system. |
| REPLICATED_TO_OTHER_SITES_FLAG | Not null | VARCHAR2(1) | A flag to show if the study has actually been replicated to another location. |
| RAND_REPLICATED_FLAG | Not null | VARCHAR2(1) | A flag to show if the randomization in the study has actually been replicated to another location. |
| OWNING_LOCATION | Not null | VARCHAR2(15) | A code for the location that owns the study. |
| TITLE | Null | VARCHAR2(2000) | A long title for the study. |
| INTERNATIONAL_UNIQUE_CODE | Null | VARCHAR2(30) | An alternative code for the study. |
| SOURCE_STUDY | Null | VARCHAR2(15) | The code of the source study, if the study was created by copying another study. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this study. |
| MODIFICATION_TS | Null | DATE | Date and time when this study was last modified. |
| GIVING_LOCATION | Null | VARCHAR2(15) | The system uses this column during a change of owning location. When the owner of a study changes, Oracle Clinical sets the giving location to the current location (that is, the previous owner). Thus, when the study is being given away, the giving location equals the current location. After the ownership change is complete, the system sets this column to null. |
CLINICAL_SUBJECTS acts as a place holder to link patient positions together. The patients can be within the same study or across studies. This concept can be used to combine screening data recorded against a screening patient position number with randomized data recorded against a regular patient position number. It can also be used to link data from a short-term and rollover study.
Many of the column descriptions in this table refer to the "most accepted" attribute for a patient, such as "most accepted date of birth" or "most accepted last name." This phrasing is used because records of patient attributes may differ between patient positions. Using a most accepted value — a value generally recommended for this patient position — therefore enables researchers to link patient positions despite data inaccuracies.
Primary Key CLINICAL_SUBJECT_ID
Related Tables and Foreign Keys
| Related table | Foreign key Name in this table | Foreign Key Name in the Related Table |
|---|---|---|
| PATIENT_POSITIONS | CLINICAL_SUBJECT_ID | CLINICAL_SUBJECT_ID |
CLINICAL_SUBJECTS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| CLINICAL_SUBJECT_ID | Not null | NUMBER(10) | A unique, system-generated ID for the clinical subject. |
| CHILD_BEARING_POTENTIAL_FLAG | Not null | VARCHAR2(1) | A flag to show if the patient has child-bearing potential. |
| RETIRED_FLAG | Not null | VARCHAR2(1) | Not used. |
| SYSTEM_GENERATED_FLAG | Not null | VARCHAR2(1) | Not used. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this clinical subject. |
| CREATION_TS | Not null | DATE | Date and time when this clinical subject was created. |
| BIRTH_DATE | Null | DATE | The most accepted date of birth for the patient positions linked together. |
| FIRST_NAME | Null | VARCHAR2(15) | The most accepted first name for the patient positions linked together. |
| SEX | Null | VARCHAR2(1) | The most accepted gender for the patient positions linked together. Values are M for male and F for female. |
| LAST_NAME | Null | VARCHAR2(20) | The most accepted last name for the patient positions linked together. |
| INITIALS | Null | VARCHAR2(4) | The most accepted initials for the patient positions linked together. |
| DATE_LAST_PREGNANCY_STARTED | Null | DATE | The date that the patient last reported becoming pregnant. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this clinical subject. |
| MODIFICATION_TS | Null | DATE | Date and time when this clinical subject was last modified. |
Data Clarification Forms (DCFs) provide a structure for organizing discrepancies into groups based on a user defined selection criteria such as patient, DCM, investigator, or site. You specify criteria, or, select specific discrepancies and Oracle Clinical creates one DCF for each patient that has selected discrepancy records. A DCF report is based on a single DCF. DCF reports are created to facilitate communication between CRAs and investigators. The CRA and investigator can use the DCF and DCF reports to organize and track progress in resolving discrepancies.
Specifically, DCFs provides a means to:
group discrepancies, clarify the information needed, and produce DCF reports
track progress in analyzing and resolving discrepancies
more efficiently handle decisions related to the grouped discrepancies
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| OCL_SITES | SITE_ID | SITE_ID |
| PATIENT-POSITIONS | PATIENT_ID | PATIENT_POSITION_ID |
| OCL_INVESTIGATORS | INVESTIGATOR_ID | INVESTIGATOR_ID |
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| DCF_ID | Not Null | NUMBER(10) | A unique, system-generated ID for the data clarification form. |
| INVESTIGATOR_STATUS | Null | VARCHAR2(60) | The optionally specified constraint when the DCF was created to included Discrepancies only associated with a particular Discrepancy Status. |
| PASSIVE_STATUS | Null | VARCHAR2(60) | The optionally specified constraint when the DCF was created to additionally include related Discrepancies associated with a particular Discrepancy Status, but that should not be included on the DCF itself. |
| RESOLVED_STATUS | Null | VARCHAR2(60) | The optionally specified constraint when the DCF was created to additionally include related Discrepancies associated with a particular Discrepancy Status, but that should not be included on the DCF itself. |
| INVESTIGATOR_ID | Null | NUMBER(10) | The optionally specified constraint when the DCF was created to included Discrepancies only associated with a particular Investigator. |
| SITE_ID | Null | NUMBER (10) | The optionally specified constraint when the DCF was created to included Discrepancies only associated with a particular Site. |
| PATIENT_ID | Null | NUMBER(10) | The specified constraint when the DCF was created to included Discrepancies only associated with a particular Patient. |
| PATIENT_RANGE | Null | VARCHAR2(60) | Specifies the manner in which the patient constraints were selected, such as No Range Specified or Multi-Select (when the Discrepancies, and thereby the Patients, are selected in this way from the Discrepancy Management screen). |
| CLINICAL_STUDY_ID | Not Null | NUMBER(10) | The ID of the Clinical Study. |
| CURRENT_STATUS | Null | VARCHAR2(30) | The status of the DCF (Sent, Received, etc.) |
| LAST_PRINT_DATE | Null | DATE | The last date/time that the DCF was printed. |
| LAST_PRINT_BY | Null | VARCHAR2(30) | The username of the person that last printed the DCF. |
| DATABASE_MODE | Null | VARCHAR2(1) | Specifies whether or not the DCF was created in Test or Production mode. |
| INVESTIGATOR | Null | VARCHAR2(10) | The name of the Investigator if Investigator_Id is populated. |
| SITE | Null | VARCHAR2(10) | The name of the Site if Site_Id is populated. |
| PATIENT | Null | VARCHAR2(10) | The name of the Patient if Patient_Id is populated. |
| CREATION_TS | Null | DATE | Date and time when this DCF was created. |
| CREATED_BY | Null | VARCHAR2(30) | User who created the DCF. |
| MODIFICATION_TS | Null | DATE | Date and time when the DCF was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified the DCF. |
| OWNING_USER | Null | VARCHAR2(30) | The username of the person to whom ownership of the DCF has been granted. |
| DESCRIPTION | Null | VARCHAR2(200) | The detailed description of the reason for which the DCF was created. |
| HEADER_TEXT | Null | VARCHAR2(2000) | The text that appears as the header of the DCF when the DCF is printed and sent out. |
| FOOTER_TEXT | Null | VARCHAR2(2000) | The text that appears as the footer of the DCF when the DCF is printed and sent out. |
The system uses this table to track the pages of a DCF report. The system populates the table when the DCF status changes to SENT or REPRINT.
The DCF_PAGES table resides in the RXC_DISC_TSPA tablespace.
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| DATA_CLARIFICATION_FORMS | DCF_ID | DCF_ID |
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| CLINICAL_STUDY_ID | Not Null | NUMBER(10) | The ID of a the clinical study to which all DCF pages belong. |
| DCF_PAGE_ID | Not Null | NUMBER(10) | The ID of the DCF page. |
| DCF_ID | Not Null | NUMBER(10) | The ID of the DCF to which all DCF pages belong. |
| PAGE_NO | Not Null | NUMBER(10) | The physical page number of the DCF that was printed. |
| RELEASE_NO | Not Null | NUMBER(10) | The version number of the DCF that was printed. |
| STATUS | Not Null | VARCHAR2(10) | The status of the page (Sent, Received, etc.). |
| STATUS_TS | Not Null | DATE | Date and time of the latest status change. |
| CREATED_BY | Not Null | VARCHAR2(30) | User who created the DCF Page. |
| CREATION_TS | Not Null | DATE | Date and time when the DCF Page was created. |
| PAGE_REFERENCE_NUMBER | Null | VARCHAR2(20) | An optional, free-form field for specifying imaging codes associated with the page. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified the DCF Page. |
| MODIFICATION_TS | Null | DATE | Date and time when the DCF Page was last modified. |
DCI Book Constraints determine which DCIs in a DCI book you can use to create Received RDCIs for unplanned events. For the Graphic layout system, constraints settings allows you to specify the preferred version of the DCI Form. The preferred version is used to select a version of the DCI form when first logging in data in RDC PDF, and when migrating data.
This table is replicated throughout an Oracle Clinical installation.
Primary Key DCI_BOOK_ID, DCI_ID
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| DCI_BOOKS | DCI_BOOK_ID | DCI_BOOK_ID |
| DCIS | DCI_ID | DCI_ID |
DCI_BOOK_DCI_CONSTRAINTS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| DCI_BOOK_ID | Not Null | NUMBER(10) | The ID of the DCI Book. |
| CLINICAL_STUDY_ID | Not Null | NUMBER(10) | The ID of the clinical study. |
| DCI_ID | Not Null | NUMBER(10) | The ID of the DCI. |
| UNPLANNED_ALLOWED_FLAG | Not Null | VARCHAR2(1) | If Y, you can enter data for this DCI against any clinical planned event. If N and the DCI is planned, you can enter data only against the clinical planned events specified in DCI Book Pages. If N and the DCI is unplanned, you cannot enter data for that DCI. |
| PREFERRED_VERSION_SN | Not Null | NUMBER(3) | Number of the preferred DCI Form version of this DCI to associate with this DCI Book. The Preferred DCI Form version is used for RDC PDF data entry. |
| CREATION_TS | Not Null | DATE | The date and time that the DCI Book DCI Constraint was created. |
| CREATED_BY | Not Null | DATE | The username of the person who created the DCI Book DCI Constraint. |
| MODIFICATION_TS | Null | DATE | The date and time that the DCI Book DCI Constraint was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | The username of the person who last modified the DCI Book DCI Constraint. |
Data collection instruments (DCIs) are the definition of physical collections of data. A DCI could be a CRF or a block of data in a batch file. Its primary function is to manage the data collection process. It has no significance to the actual data analysis.
A DCI associates one or more data collection modules (DCMs) that are collected together via its child table, DCI_MODULES. The DCI and its child tables have two functions: to structure the order of data entry, and to define the relationship between the data defined in DCMs, and the physical pages that the data is collected upon, through the DCI_MODULE_PAGES.
SHORT_NAME, DOMAIN
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
| DCI_MODULES | DCI_ID | DCI_ID |
| RECEIVED_DCIS | DCI_ID | DCI_ID |
| DCI_BOOK_PAGES | DCI_ID | DCI_ID |
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| DCI_ID | Not null | NUMBER(10) | A unique, system-generated ID for the DCI. |
| CREATION_TS | Not null | DATE | Date and time when this DCI was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this DCI. |
| NAME | Not null | VARCHAR2(30) | The name of the DCI. The name is unique within the domain that the DCI belongs to. |
| DOMAIN | Not null | VARCHAR2(15) | A Global Library object-naming convention that facilitates the unique naming or grouping of objects. |
| DCM_DCI_QG_TYPE_CODE | Not null | VARCHAR2(15) | A user-defined code to classify the DCI according to the type of data collected by that DCI. |
| DCI_STATUS_CODE | Not null | VARCHAR2(15) | A code to control usage and attributes of the DCI. Values are P for provisional, A for active, or R for retired. |
| LAST_STATUS_CHANGE_TS | Not null | DATE | The date and time that the DCI_STATUS_CODE was last changed. |
| SHORT_NAME | Not null | VARCHAR2(10) | A shortened name for the DCI,used during Log-In. The short name is unique within the DCI's domain. |
| SAFETY_FLAG | Not null | VARCHAR2(1) | A user-defined flag to show if the DCI collects safety data. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which the DCI belongs. |
| COLLECT_TIME_FLAG | Not null | VARCHAR2(1) | A flag to indicate whether the time of a clinical planned event should be collected. If this field is Y, the user must complete the DCI date and DCI time fields during the DCI Log-In process. If this field is N, only the DCI date field appears on the DCI Log-In screen. |
| CRF_PAGE_TRACKING_ENABLED_FLAG | Not null | VARCHAR2(1) | A flag to show whether page tracking is enabled for the DCI. |
| DCI_DATE_REQUIRED_FLAG | Not Null | VARCHAR(1) | Flag to indicate if the DCI date is required. If Y, the DCI date is required. |
| FL_NEEDS_EDIT_FLAG | Not null | VARCHAR2(1) | If Y, the provisional form layout, if it exists, needs to be edited before you can use it to generate a DCI Form. If there are no provisional layouts and the box is checked, there are post-edit updates. |
| FL_NEEDS_UPDATE_FLAG | Not null | VARCHAR2(1) | Equals Y if there were changes to one of the constituent DCM graphic layouts after the last editing of a layout. If there is a provisional layout, editing and saving a provisional layout updates the layouts with the updated DCM graphic layouts, and changes clears this checkbox. If there are no provisional layouts, this indicates that none of the existing layouts reflect changes made to the constituent DCM graphic layouts after the last editing of a layout. |
| FL_NEEDS_GENERATION_FLAG | Not null | VARCHAR2(1) | If Y, the DCI Module definition has changed since the last DCI Form Layout was generated, and requires regeneration. |
| RETIREMENT_REASON_TYPE_CODE | Null | VARCHAR2(15) | A code to indicate why a retired DCI was retired. |
| STATUS_COMMENT_TEXT | Null | VARCHAR2(200) | Freeform text, in mixed case, containing supplementary information about why the DCI's status did or did not change. |
| HELP_TEXT | Null | VARCHAR2(200) | Freeform help text, in mixed case, available for display during data entry. |
| MODIFICATION_TS | Null | DATE | Date and time when this DCI was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this DCI. |
| REPLICATION_IND | Null | VARCHAR2(1) | A flag to show if the DCI has been replicated from another location. Values are Y if yes, and null if not replicated. |
| DEFAULT_PAGE_NUMBERING_SCHEME | Null | VARCHAR2(15) | A code for the default page numbering scheme used by page tracking if it is enabled. |
| EXPECTED_NUMBER_OF_PAGES | Null | NUMBER(4) | The number of pages expected to be entered for the DCI. |
| UNEXP_PAGE_NUMBERING_SCH | Null | VARCHAR2(15) | A code for the numbering scheme to use when unexpected pages have to be recorded. |
| ORIGINAL_DCI_ID | Null | NUMBER(10) | This field preserves the ID of the DCI from which this DCI originally derives. This field is NULL when a DCI is created from scratch, and is populated during DCI copy operations. Subsequent copies of DCIs that already have this field populated preserve the original ID. |
This table represents a physical collection of DCIs or CRF pages. A DCI can be defined in more than one way — for example, pages can be made to fit European and US paper sizes. The table defines the sequence in which data should be entered during data entry. It is also used by the page tracking feature to assign actual page numbers (physical pages) to a patient's data when it is entered.
Unique Key NAME, CLINICAL_STUDY_ID
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| PATIENT_POSITIONS | DCI_BOOK_ID | DCI_BOOK_ID |
| DCI_BOOK_PAGES | DCI_BOOK_ID | DCI_BOOK_ID |
| DCI_BOOK_PHYSICAL_PAGES | DCI_BOOK_ID | DCI_BOOK_ID |
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| DCI_BOOK_ID | Not Null | NUMBER(10) | Unique, system-generated ID for this DCI Book. |
| CREATED_BY | Not Null | VARCHAR2(30) | User who created this DCI Book. |
| CREATION_TS | Not Null | DATE | Date and time when this DCI Book was created. |
| CLINICAL_STUDY_ID | Not Null | NUMBER(10) | ID of the clinical study with which this DCI Book is associated. |
| NAME | Not Null | VARCHAR2(30) | Name of the DCI Book. |
| DEFAULT_FLAG | Not Null | VARCHAR2(1) | If Y, this DCI Book is the default book for the study and comes up automatically in data entry, provided Entry by DCI Book is enabled. |
| DESCRIPTION | Null | VARCHAR2(200) | Freeform text describing the DCI Book. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this DCI Book. |
| MODIFICATION_TS | Null | DATE | Date and time when this DCI Book was last modified. |
| REPLICATION_IND | Null | VARCHAR2(1) | A flag to show if the DCI book has been replicated from another location. |
| DCI_BOOK_STATUS_CODE | Not null | VARCHAR2(15) | A code to show if the DCI book is Provisional (P), Active (A), or Retired (R). |
| RETIREMENT_REASON_TYPE_CODE | Null | VARCHAR2(15) | A code to indicate why a retired DCI book was retired. |
| STATUS_COMMENT_TEXT | Not null | VARCHAR2(200) | A comment about why the DCI_BOOK_STATUS_CODE was changed. |
| UNPLANNED_ALLOWED_FLAG | Not null | VARCHAR2(1) | If Y, then for any DCI is not listed in DCI Book DCI Constraints, you can enter data for the DCI. Any planned DCIs are automatically listed in DCI Book Constraints, so this column only applies to unplanned DCIs. |
| VALIDATION_STATUS | Not Null | VARCHAR2(20) | Indicates whether the DCI Book needs validating to ensure its rules and pages are consistent with each other. The value defaults to NOT APPLICABLE. |
| VALIDATION_RESULTS | Null | CLOB | The results from the current Validate DCI Book run for this DCI Book. |
| VALIDATION_TS | Null | DATE | The timestamp of the last successful Validate DCI Book run. |
| REVISION_SN | Null | NUMBER(3) | The revision sequence number of the DCI Book at the last successful Validate DCI Book |
A DCI book page represents an individual page within a DCI book. It corresponds to an actual page of a CRF. If the DCIs are made up of more than one page, the DCI_BOOK_PAGE references the first page of the DCI — for instance, if the Demography DCI were two pages long and the pages were numbered 3 and 4, there would be a single DCI_BOOK_PAGE record with a START_PAGE_NUMBER of 3. The child table DCI_BOOK_PHYSICAL_PAGES contains the number of each page making up DCIs within the DCI_BOOK.
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| CLINICAL_PLANNED_EVENTS | CLIN_PLAN_EVE_ID | CLIN_PLAN_EVE_ID |
| DCI_BOOKS | DCI_BOOK_ID | DCI_BOOK_ID |
| DCI_BOOK_PHYSICAL_PAGES | DCI_BOOK_PAGE_ID | DCI_BOOK_PAGE_ID |
| DCIS | DCI_ID | DCI_ID |
DCI_BOOK_PAGES Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which the DCI book page belongs. |
| DCI_BOOK_PAGE_ID | Not null | NUMBER(10) | A unique, system-generated ID for the DCI book page. |
| DCI_BOOK_ID | Not null | NUMBER(10) | The ID of the DCI book to which the DCI book page belongs. |
| DCI_ID | Not null | NUMBER(10) | The ID of the DCI that the DCI book page collects. |
| START_PAGE_NUMBER | Not null | VARCHAR2(15) | Alphanumeric page number in the CRF book where the data for this instance of the DCI starts. |
| CLIN_PLAN_EVE_ID | Null | NUMBER(10) | For visit-oriented DCIs, the ID of the CPE assigned to this DCI book page is collected. |
| CREATED_BY | Null | VARCHAR2(30) | User who created this DCI Book Page. |
| CREATION_TS | Null | DATE | Date and time when this DCI Book Page was created. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this DCI Book Page. |
| MODIFICATION_TS | Null | DATE | Date and time when this DCI Book Page was last modified. |
| REPLICATION_IND | Null | VARCHAR2(1) | A flag to show if this DCI book page has been replicated from another location. |
| DISPLAY_SN | Not null | NUMBER(4) | A sequence number indicating the placement of the DCI book page within the DCI. |
A DCI book physical page records the entry of each CRF page in a DCI book. Oracle Clinical ensures the first physical page for each DCI book page has the same BOOK_PAGE label as the START_PAGE_NUMBER. The number of DCI book physical pages is determined by the number of pages defined for the DCI.
Primary Key DCI_BOOK_PHYSICAL_PAGE_ID
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| DCI_BOOKS | DCI_BOOK_ID | DCI_BOOK_ID |
| DCI_BOOK_PAGE | DCI_BOOK_PAGE_ID | DCI_BOOK_PAGE_ID |
DCI_BOOK_PHYSICAL_PAGES Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| DCI_BOOK_PHYSICAL_PAGE_ID | Not null | NUMBER(10) | A unique, system-generated ID for the DCI book physical page. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which the DCI book physical page belongs. |
| DCI_BOOK_ID | Not null | NUMBER(10) | The ID of the DCI book to which the DCI book physical page belongs. |
| DCI_BOOK_PAGE_ID | Not null | NUMBER(10) | The ID of the DCI book page that this actual page relates to. |
| BOOK_PAGE | Not null | VARCHAR2(15) | The label or page code used to identify this page within the DCI book. |
| RELATIVE_TO_DCI_PAGE | Not null | NUMBER(4) | The order of this physical page relative to all the physical pages spanned by this physical page's DCI. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this DCI Book Physical Page. |
| CREATION_TS | Not null | DATE | Date and time when this DCI Book Physical Page was created. |
| PAGE_DESCRIPTION | Null | VARCHAR2(200) | Freeform text describing the physical page. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this DCI Book Physical Page. |
| MODIFICATION_TS | Null | DATE | Date and time when this DCI Book Physical Page was last modified. |
| REPLICATION_IND | Null | VARCHAR2(1) | A flag to show if the DCI book physical page has been replicated from another location. |
DCI Forms are data entry-enabled PDF CRFs that you can create if you are implementing the RDC PDF Option. Each DCI Form is a versioned object, so this table stores information about each version of each DCI Form in this database.
Primary Key DCI_ID, VERSION_SN
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| DCIS | DCI_ID | DCI_ID |
| FORM_LAYOUT_TEMPLATES | FLT_ID | FLT_ID |
DCI_FORM_VERSIONS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| DCI_ID | Not Null | NUMBER(10) | The ID of the DCI for which this DCI Form is used. |
| VERSION_SN | Not Null | NUMBER(3) | Version number of this layout. Oracle Clinical creates version 1 from your first generation. A new version is created when you generate a provisional layout, if there are no existing provisional layouts. |
| LANGUAGE | Not Null | VARCHAR2(20) | The name of the language for this version of the DCI form. |
| CLINICAL_STUDY_ID | Not Null | NUMBER(10) | Unique ID for the clinical study for which this form version is being used. |
| FLT_ID | Not Null | VARCHAR2(30) | ID of the template for this version of the DCI Form. |
| FL_GENERATION_TS | Null | DATE | Date and time when this form layout was generated. |
| FL_GENERATED_BY | Null | VARCHAR2(30) | User who generated this form layout. |
| FL_STATUS | Null | VARCHAR2(1) | The current status of this form layout. The status can be P (Provisional), A (Active) or R (Retired). RDC uses the active layout if the preferred version for the DCI in the DCI book is CURRENT.
A DCI form can have at most: One Provisional Form Layout Version (editable and updatable) at most one Active Form Layout Versions (Can render output) zero or more Retired Form Layout Versions. |
| FL_EDITED_FLAG | Null | VARCHAR2(1) | If Y, this DCI form layout has been edited since its generation. |
| DESCRIPTION | Null | VARCHAR2(120) | Further information to identify this version. |
| FL_XML | Null | CLOB | This Character Large OBject (CLOB) stores the actual XML that describes this DCI Form Version. |
| FL_XML_MODIFICATION_TS | Null | DATE | Date and time when the generated XML was last modified. |
| FL_XML_MODIFIED_BY | Null | VARCHAR2(30) | User who last modified the generated XML. |
| FL_XML_REFRESH_TS | Null | DATE | Date and time when the XML was last refreshed. |
| FL_XML_REFRESHED_BY | Null | VARCHAR2(30) | User who last refreshed the XML. |
| PDF_GENERATION_TS | Null | DATE | Date and time when the PDF was first generated. |
| PDF_GENERATED_BY | Null | VARCHAR2(30) | User who first generated this PDF. |
| Null | BLOB | This Binary Large OBject (or BLOB) column stores the actual. | |
| CHECKBOX_SHAPE | Null | VARCHAR2(30) | Either CIRCLE or SQUARE. Circular checkboxes are radio buttons, which restrict you to choosing one item from a group. Square checkboxes are shown as actual checkboxes, and allow you to select and deselect each item in the group. |
| CHECKBOX_SIZE | Null | NUMBER(6) | The size of the checkbox, usually in points. |
| FL_PDF_PAGE_DEFINITION_CODE | Null | VARCHAR2(15) | Name of the template used for generation of the PDF. |
| FL_PDR_PAGE_DEFINITION_CODE | Null | VARCHAR2(15) | Name of the template used for generation of the PDR. |
| FL_HEIGHT | Null | NUMBER(6,1) | Overall height (in points) of the DCI Form Layout. |
| FL_WIDTH | Null | NUMBER(6,1) | Overall width (in points) of the DCI Form Layout. |
A DCI module records a DCM assignment to a DCI with additional information, such as whether the DCM should collect time, if the DCM is qualified, what the qualification value is, and whether the DCM is to be collected at a particular visit.
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| DCMS | DCM_ID | DCM_ID |
| DCMS | DCM_SUBSET_SN | DCM_SUBSET_SN |
| DCMS | DCM_LAYOUT_SN | DCM_LAYOUT_SN |
| DCIS | DCI_ID | DCI_ID |
| CLINICAL_PLANNED_EVENTS | CLIN_PLAN_EVE_ID | CLIN_PLAN_EVE_ID |
| DCI_MODULE_PAGES | DCI_MODULE_ID | DCI_MODULE_ID |
DCI_MODULES Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| DCI_MODULE_ID | Not null | NUMBER(10) | A unique, system-generated ID for the DCI module. |
| CREATION_TS | Not null | DATE | Date and time when this DCI Module was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this DCI Module. |
| DCI_ID | Not null | NUMBER(10) | The ID of the DCI to which the DCI module belongs. |
| DCM_ID | Not null | NUMBER(10) | The ID of the DCM assigned to the DCI by this DCI module. |
| DCM_SUBSET_SN | Not null | NUMBER(3) | The subset number of the DCM assigned to the DCI by this DCI module. |
| DCM_LAYOUT_SN | Not null | NUMBER(3) | The layout number of the DCM assigned to the DCI by this DCI module. |
| DCM_MODULE_SN | Not null | NUMBER(3) | A serial number indicating the order of the module within the parent DCI. |
| EVENT_DATE_IS_DCI_DATE_FLAG | Not null | VARCHAR2(1) | A flag indicating whether the clinical planned event's time and date should be collected separately for received DCMs entered for this DCI module or whether they should be regarded as those entered for the received DCI. |
| COLLECT_EVENT_TIME_FLAG | Not null | VARCHAR2(1) | A flag indicating whether the clinical planned event's time should be collected for this DCI module. |
| OPTIONAL_FLAG | Not null | VARCHAR2(1) | A flag to indicate whether the DCM can have more than one instance created for this module at entry. If the flag is Y, the module is optional. Only a single DCI module can exist in a DCI if this flag is marked as optional. At entry, the operator can manually invoke creation of additional received DCMs for optional DCI module. The DCI module must also allow entry of either the qualifying value or the clinical planned event to be marked as optional. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which the DCI belongs. |
| QUAL_QUESTION_VALUE_TEXT | Null | VARCHAR2(80) | If the DCM for this DCI module is qualified by a particular question, this column specifies the matching value for that question for this module. |
| CLIN_PLAN_EVE_ID | Null | NUMBER(10) | The ID of the clinical planned event assigned to the DCI module. |
| CLIN_PLAN_EVE_NAME | Null | VARCHAR2(20) | The name of the clinical planned event assigned to the DCI module. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this DCI module. |
| MODIFICATION_TS | Null | DATE | Date and time when this DCI Module was last modified. |
| REPLICATION_IND | Null | VARCHAR2(1) | A flag to show if the DCI book physical page has been replicated from another location. |
| START_PAGE | Null | NUMBER(4) | The first physical page number spanned by the DCM (associated with the DCI module) within the parent DCI. |
| END_PAGE | Null | NUMBER(4) | The last physical page number spanned by the DCM (associated with the DCI module) within the parent DCI. |
| SHOW_QUALIFYING_VALUE_FLAG | Null | VARCHAR2(1) | If Y, Oracle Clinical displays a Qualifying Value in the DCM header. |
| SHOW_LAB_FLAG | Null | VARCHAR2(1) | If Y, Oracle Clinical displays the lab in the DCM header. |
| SHOW_VISIT_CODE | Null | VARCHAR2(15) | The Visit Code controls how the DCI Form displays the visit name and subevent number. The available options are:
NAME ONLY – Only the Visit Name appears on the DCI Form. NAME+SUB# – The Visit Name and Subevent Number appear as one field with a period separator (for instance, CHECKUP.1). NAME/SUB# – The Visit Name and Subevent Number appear as separate fields. HIDDEN – Both the Visit Name and Subevent number are hidden in the DCM Header. If there is no defined clinical planned event, or the "Use DCI Date" setting is unchecked, you cannot select value HIDDEN for the Visit Display. |
| SHOW_DATA_COMMENT_FLAG | Null | VARCHAR2(1) | If Y, Oracle Clinical shows the Data Comment in the DCM header. |
| SHOW_DATE_FLAG | Null | VARCHAR2(1) | If Y, Oracle Clinical displays the date in the DCM header. |
| SHOW_TIME_FLAG | Null | VARCHAR2(1) | If Y, Oracle Clinical displays the time in the DCM header. |
| SHOW_BLANK_FLAG | Null | VARCHAR2(1) | If Y, Oracle Clinical displays a blank flag in the DCM header. |
| SHOW_COMMENT_FLAG | Null | VARCHAR2(1) | If Y, Oracle Clinical displays the Comment in the DCM header. |
DCI_Module_Pages contains page tracking information about DCI Modules.
Primary Key DCI_MODULE_PAGE_ID
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| DCI_MODULES | DCI_MODULE_ID | DCI_MODULE_ID |
DCI_MODULE_PAGES Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| DCI_MODULE_PAGE_ID | Not null | NUMBER(10) | A unique, system-generated ID for the DCI module page. |
| DCI_MODULE_ID | Not null | NUMBER(10) | The ID of the DCI module to which this DCI module page is assigned. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which this DCI module page is assigned. |
| RELATIVE_TO_DCI_PAGE | Not null | NUMBER(4) | The ID of the clinical study to which this DCI module page is assigned. |
| LAST_DCM_QUESTION_ID | Null | NUMBER(10) | The ID of the last DCM question on this DCI module page. |
| DCI_ID | Not null | NUMBER(10) | The ID of the DCI to which this DCI module page is assigned. |
| DCI_MODULE_SN | Not null | NUMBER(3) | The subset number of the DCI module to which this DCI Module Page is assigned. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this DCI Module Page. |
| CREATION_TS | Not null | DATE | Date and time when this DCI Module Page was created. |
| LAST_QUE_REPEAT_NUM_ON_PAGE | Null | NUMBER(4) | If this question belongs to a repeating question group, this column stores the ID of last repeat instance that appears on the page. Leave this column blank if the question is not repeating. |
| REPLICATION_IND | Null | VARCHAR2(1) | A flag to show if the DCI module page has been replicated from another location. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this DCI Module Page. |
| MODIFICATION_TS | Null | DATE | Date and time when this DCI Module page was last modified. |
A data collection module (DCM) is a logically related set of questions consisting of one or more DCM question groups that are collected at a single visit or about a particular subject (defined by qualifying value) within a visit. The primary use of the DCM is to specify the logical collections of data in data collection and analysis. Oracle Clinical enforces that one and only one instance of a DCM (irrespective of DCM subset and DCM layout) can be collected at an actual event, unless the DCM is qualified (see QUALIFYING_QUESTION), in which case one instance for each unique qualifying value is allowed.
The DCM table and its child tables all use a concatenated primary key consisting of the object ID and the DCM_SUBSET_SN and DCM_LAYOUT_SN. The DCM_SUBSET_SN defines variants of the DCM that might collect different subsets of the DCM questions specified for the DCM. The DCM_LAYOUT_SN defines variants of a DCM subset which are functionally equivalent in what they collect, but differ in the data entry properties. When working with DCMs, particular care must be taken to fully qualify upon these keys if a unique instance of a DCM and its children are required.
By convention, the base DCM subset is the DCM with DCM_SUBSET_SN = 1 and DCM_LAYOUT_SN = 1. For attributes that are not subset- and layout-specific, this base DCM subset and its children can be referenced. These DCM-wide attributes are labeled "DCM-WIDE" in the column descriptions. By convention, the base DCM layout for each DCM subset is the DCM subset with DCM_LAYOUT_SN = 1, and each DCM subset has at least one DCM layout = 1. These DCM subset-wide attributes are labeled "SUBSET-WIDE" in the column descriptions. For attributes that are not layout specific, this base DCM Layout and its children can be referenced. Attributes that can change for each DCM layout are labeled "LAYOUT-SPECIFIC" in the column descriptions.
Primary Key DCM_ID, DCM_SUBSET_SN, DCM_LAYOUT_SN
Unique Keys NAME, DCM_SUBSET_SN, DCM_LAYOUT_SN
SHORT_NAME, DCM_SUBSET_SN, DCM_LAYOUT_SN
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
| DCM_QUESTION_GROUPS | DCM_ID | DCM_ID |
| DCM_QUESTION_GROUPS | DCM_SUBSET_SN | DCM_QUE_GRP_DCM_SUBSET_SN |
| DCM_QUESTION_GROUPS | DCM_LAYOUT_SN | DCM_QUE_GRP_DCM_LAYOUT_SN |
| DCMS | ORIGINAL_DCM_ID | DCM_ID |
| DCMS | ORIGINAL_DCM_SUBSET_SN | DCM_SUBSET_SN |
| DCMS | ORIGINAL_DCM_LAYOUT_SN | DCM_LAYOUT_SN |
| QUESTIONS | QUAL_QUESTION_ID | QUESTION_ID |
| DCI_MODULES | DCM_ID | DCM_ID |
| DCI_MODULES | DCM_SUBSET_SN | DCM_SUBSET_SN |
| DCI_MODULES | DCM_LAYOUT_SN | DCM_LAYOUT_SN |
| RECEIVED_DCMS | DCM_ID | DCM_ID |
| RECEIVED_DCMS | DCM_SUBSET_SN | DCM_SUBSET_SN |
| RECEIVED_DCMS | DCM_LAYOUT_SN | DCM_LAYOUT_SN |
| DISCRETE_VALUE_GROUPS | DISCRETE_VAL_GRP_ID | DISCRETE_VALUE_GRP_ID |
| DISCRETE_VALUE_GROUPS | DISCRETE_VAL_GRP_SUBSET_NUM | DISCRETE_VAL_GRP_SUBSET_NUM |
| DISCRETE_VALUES | DISCRETE_VAL_GRP_ID | DISCRETE_VALUE_DVG_ID |
| DISCRETE_VALUES | DISCRETE_VAL_GRP_SUBSET_NUM | DISCRETE_VAL_GRP_SUBSET_NUM |
| DISCRETE_VALUES | DISCRETE_VAL_GRP_IND_VAL | DISCRETE_VALUE_VALUE |
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| DCM_ID | Not null | NUMBER(10) | DCM-wide: A unique, system-generated ID for the DCM. |
| DCM_SUBSET_SN | Not null | NUMBER(3) | 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. |
| DCM_LAYOUT_SN | Not null | NUMBER(3) | 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. |
| CREATION_TS | Not null | DATE | Layout-specific: The date and time when this layout was created for this DCM subset. |
| CREATED_BY | Not null | VARCHAR2(30) | Layout-specific: The user who created this layout for this DCM subset. |
| NAME | Not null | VARCHAR2(16) | DCM-wide: A name for the DCM. Names are unique within a domain. |
| DOMAIN | Not null | VARCHAR2(15) | DCM-wide: A Global Library object-naming convention that facilitates the unique naming or grouping of objects. Domains also provide a mechanism for limiting access to objects. |
| SUBSET_NAME | Not null | VARCHAR2(8) | Subset-wide: Subset name; unique among the subsets of this DCM. |
| DCM_STATUS_CODE | Not null | VARCHAR2(15) | Layout-specific: A code to control where the DCM can be used and which attributes can be changed. This status has one of three values: provisional (P), active (A), or retired (R). |
| LAST_STATUS_CHANGE_TS | Not null | DATE | Layout-specific: The date and time that the DCM_STATUS_CODE was last changed. |
| SHORT_NAME | Not null | VARCHAR2(4) | DCM-wide: The short name is used as the basis for constructing the names of the default Oracle and SAS views associated with the DCM. Non-repeating DCM question groups are available in a default view named by the DCM short name; repeating question groups are available in a view named by the DCM short name concatenated with the DCM question group short name. |
| DESCRIPTION | Not null | VARCHAR2(70) | Layout-specific: Freeform text, in mixed case, used to describe the DCM and its clinical and/or medical use. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | DCM-wide: The ID of the clinical study to which the DCM belongs. |
| DCM_DCI_QG_TYPE_CODE | Not null | VARCHAR2(15) | DCM-wide: A user-defined code to classify a DCM according to the type of data collected by that DCM. |
| COLLECTS_LAB_TESTS_FLAG | Not null | VARCHAR2(1) | DCM-wide: A system-derived flag to show if the DCM collects lab data. This flag is set to Y if any DCM question in the DCM is based on a Global Library question of type LAB_TEST. |
| LAYOUT_EDITED_FLAG | Not null | VARCHAR2(1) | Layout-specific: A system-derived flag to indicate if the DCM's layout has been edited with the layout editor. |
| LAYOUT_REGENERATION_REQ_FLAG | Not null | VARCHAR2(1) | Layout-specific: A system-derived flag to indicate if the DCM layout needs regeneration or edit with the layout editor. A regeneration is required if changes made to the DCM or underlying DCM questions would change the data entry screen layout — for instance, adding a new, enterable DCM question to a DCM sets the flag. |
| FFL_EDITED_FLAG | Not null | VARCHAR2(1) | If Y, this DCM graphic layout has been edited since it was last generated. The default value is N. |
| FFL_NEEDS_EDIT_FLAG | Not null | VARCHAR2(1) | If Y, you must edit this DCM graphic layout before you can add it to a DCI Form Layout. |
| FFL_NEEDS_UPDATE_FLAG | Not null | VARCHAR2(1) | This flag indicates whether there have been changes to the DCM definition since the DCM graphic layout was last reconciled with the DCM definition. If Y, you must open the DCM graphic layout in the editor, and save it, for the system to perform the required reconciliation. Since editing is necessary, the system enters Y places a check in the FFL_NEEDS_EDIT_FLAG column whenever it enters Y in the FFL_NEEDS_UPDATE_FLAG column. After you edit and save the layout, the system sets this column to N. |
| FFL_AVAILABLE_FLAG | Not null | VARCHAR2(1) | If Y, you can incorporate the DCM graphic layout into a DCI Form Layout. This column cannot be Y if the DCM graphic layout does not exist, if the Needs Edit is checked, or if the DCI Form Definition is not enabled. The system sets this column to N when the Needs Update or Needs Edit flags are Y. |
| DEFAULT_PAGE_WIDTH | Not null | NUMBER(3) | Character layout-specific: A number that defines, in characters, the width of the page created with the default layout generator. The page width will be between 80 and 240 characters and may be different from the width of the window that displays the page. |
| DEFAULT_PAGE_HEIGHT | Not null | NUMBER(3) | Character layout-specific: A number that defines, in lines, the height of the page created with the default layout generator. The page height will be between 22 and 80 lines and may be different from the height of the window that displays the page. |
| WINDOW_WIDTH | Not null | NUMBER(3) | Layout-specific: Not currently in use. |
| WINDOW_HEIGHT | Not null | NUMBER(3) | Layout-specific: Not currently in use. |
| ORIGINAL_DCM_ID | Null | NUMBER(10) | DCM-wide: Together with ORIGINAL_DCM_SUBSET_SN and ORIGINAL_DCM_LAYOUT_SN, this field preserves the ID of the DCM from which this DCM originally derives. This field is NULL when a DCM is created from scratch, and is populated during DCM copy operations. Subsequent copies of DCMs that already have this field populated preserve the original ID. |
| ORIGINAL_DCM_LAYOUT_SN | Null | NUMBER(3) | Preserves the ID for the DCM layout from which this DCM originally derives. |
| ORIGINAL_DCM_SUBSET_SN | Null | NUMBER(3) | Preserves the ID for the DCM subset from which this DCM originally derives. |
| DCM_QUAL_VALUE_TEXT | Null | VARCHAR2(80) | Subset-wide: The value to which the system constrains the qualifying question for all received DCMs created for the DCM/DCM subset. The qualifying value must be a value in the qualifying DVG. If this value is NULL for a DCM with a qualifying question, the value can be supplied either in the DCI definition or, if NULL there, when the received DCM is created. |
| QUAL_QUESTION_ID | Null | NUMBER(10) | DCM-wide: The ID of the question used to qualify the DCM. |
| DISPLAY_SN | Null | NUMBER(3) | Layout-specific: The order in which DCM subsets are displayed in the DCM schedule maintenance application. |
| LAYOUT_GENERATION_TS | Null | DATE | Layout-specific: The last time the data entry screen layout underwent generation or editing with the layout editor. If this field is blank, the DCM data entry screen layout has never been generated. |
| RETIREMENT_REASON_TYPE_CODE | Null | VARCHAR2(15) | Layout-specific: A code to indicate why a retired DCM was retired. |
| STATUS_COMMENT_TEXT | Null | VARCHAR2(200) | Layout-specific: Supplementary information about why the DCM's status did or did not change. |
| HELP_TEXT | Null | VARCHAR2(200) | Layout-specific: Freeform text, in mixed case, available for display during data entry. |
| DATE_ORDER_CODE | Null | VARCHAR2(15) |
Layout-specific: A code that controls the entry-time display of date questions for this DCM layout. If the DATE_ORDER_CODE is DYNAMIC, the behavior is governed by the data entry preference for date display. If it is a specific value, the DCM layout uses the value to override the data entry preference. Possible values come from the reference codelist DATE_ORDER_CODE. Values are:
|
| QUAL_QUESTION_DVG_ID | Null | NUMBER(10) | DCM-wide: The ID of the DVG to be used for the qualifying question. This field will be blank when a qualifying question is not specified. |
| QUAL_QUESTION_DVG_SUBSET_NUM | Null | NUMBER(3) | DCM-wide: The subset number of the DVG to be used for the qualifying question. This field will be blank when a qualifying question is not specified. |
| QUAL_QUESTION_PROMPT | Null | VARCHAR2(60) | DCM-wide: Text on the data entry DCM header screen that asks for data input to the qualifying question. The prompt, in mixed case, is a freeform text field. |
| PROD_GENERATION_TS | Null | DATE | Layout-specific: The date and time when this DCM subset layout became available for production data entry. If this field is blank or if the PROD_GENERATION_TS is earlier than the GENERATION_TS, the DCM layout is not available for production data entry. |
| MODIFICATION_TS | Null | DATE | Date and time when this DCM subset/layout combination was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | The user who last modified this DCM subset/layout combination. |
| REPLICATION_IND | Null | VARCHAR2(1) | A flag to show if the DCM has been replicated from another location. |
| LAYOUT_VER_NUM | Null | NUMBER(2) | An internal version tracking field not currently in use. |
| FFL_BLOCK_WIDTH | Null | NUMBER(6,1) | The width (in points) of all blocks of this DCM graphic layout. |
| FFL_FIRST_BLOCK_HEIGHT | Null | NUMBER(6,1) | The height (in points) of the first block of this DCM graphic layout. This value overrides the Maximum Allowed Height for the first block only, and you can adjust it in the Layout Editor. |
| FFL_MAX_ALLOWED_HEIGHT | Null | NUMBER(6,1) | The maximum height (in points) that any of the blocks within the graphic layout can attain. Each block may be shorter than this maximum; a block's size depends on its content. You can change the height of blocks in the layout editor; however, no block may be made taller than the value reflected in this field.
The maximum allowed height is determined by the height of the DCM Layout Region of the Form Layout Template that you select when generating the DCM Graphic Layout. Choose a template that is large enough for your expected needs. |
| FFL_ACTUAL_MAX_HEIGHT | Null | NUMBER(6,1) | The actual height (in points) of the tallest block in the layout. The Editor updates this value whenever you save an edited DCM graphic layout. This information can be useful in determining which Form Layout Template to use when incorporating this DCM into a DCI. |
| FFL_LAST_BLOCK_HEIGHT | Null | NUMBER(6,1) | The height (in points) of the last block in the layout. This value may be useful in deciding the height you assign to the first block of another DCM Graphic Layout that you intend to fit on a DCI page after this DCM. |
| FLT_ID | Null | NUMBER(10) | ID of the Form Layout Template used to initialize the values for the DCM graphic layout's Standard Block Width and Height. |
| FLT_XML | Null | CLOB | A Character Large OBject (CLOB) that stores this DCM graphic layout. |
| FLT_XML_GENERATION_TS | Null | DATE | Date and time when this DCM graphic layout was first generated. |
| FLT_XML_GENERATED_BY | Null | VARCHAR2(30) | User who first generated the DCM graphic layout. |
| FLT_XML_MODIFICATION_TS | Null | DATE | Date and time when this DCM Form layout was last modified. Modifications include edits, updates, and generations. |
| FLT_XML_MODIFIED_BY | Null | VARCHAR2(30) | User who last modified the DCM Form Layout. |
| CHECKBOX_SHAPE | Null | VARCHAR2(30) | Shape of the checkbox generated by default at generation time. You can change the shape of an individual checkbox in the layout editor. |
| CHECKBOX_SIZE | Null | NUMBER(6) | Size of the checkbox generated by default at generation time. You can change the size of an individual checkbox in the layout editor. |
This table records the assignment of a question in the Global Library to a DCM question group.
For the column definitions in this section, DCM-wide attributes are labeled "DCM-WIDE," DCM subset-wide attributes are labeled "SUBSET-WIDE," and attributes that can change for each DCM layout are labeled "LAYOUT-SPECIFIC." Refer to the introduction to the DCM table for the full definitions of these attributes.
Primary Key DCM_QUESTION_ID, DCM_QUE_DCM_SUBSET_SN, DCM_QUE_DCM_LAYOUT_SN
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| DCMS | DCM_ID | DCM_ID |
| DCMS | DCM_QUE_DCM_SUBSET_SN | DCM_SUBSET_SN |
| DCMS | DCM_QUE_DCM_LAYOUT_SN | DCM_LAYOUT_SN |
| DCM_QUESTION_GROUPS | DCM_QUESTION_GROUP_ID | DCM_QUESTION_GROUP_ID |
| DCM_QUESTION_GROUPS | DCM_QUE_DCM_SUBSET_SN | DCM_QUE_GRP_DCM_SUBSET_SN |
| DCM_QUESTION_GROUPS | DCM_QUE_DCM_LAYOUT_SN | DCM_QUE_GRP_DCM_LAYOUT_SN |
| QUESTIONS | QUESTION_ID | QUESTION_ID |
| QUESTION_GROUPS | QUESTION_GROUP_QUESTION_ID | QUESTION_GROUP_QUESTION_ID |
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
| DISCRETE_VALUE_GROUPS | DISCRETE_VAL_GRP_ID | DISCRETE_VALUE_GRP_ID |
| DISCRETE_VALUE_GROUPS | DISCRETE_VAL_GRP_SUBSET_NM | DISCRETE_VAL_GRP_SUBSET_NM |
| DCM_QUES_REPEAT_DEFAULTS | DCM_QUESTION_ID | DCM_QUESTION_ID |
DCM_QUESTIONS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| DCM_QUESTION_ID | Not null | NUMBER(10) | DCM-wide: A unique, system-generated ID for the DCM question. Part of the concatenated primary key. |
| DCM_QUE_DCM_SUBSET_SN | Not null | NUMBER(3) | DCM-wide: A unique, system-generated ID for the DCM question. Part of the concatenated primary key. |
| DCM_QUE_DCM_LAYOUT_SN | Not null | NUMBER(3) | Layout-specific: The layout number of the DCM to which the DCM question belongs. Part of the concatenated primary key. |
| CREATED_BY | Not null | VARCHAR2(30) | Layout-specific: The user who created this layout for this DCM subset. |
| CREATION_TS | Not null | DATE | Layout-specific: The date and time when this layout was created for this DCM subset. |
| DCM_ID | Not null | NUMBER(10) | DCM-wide: The ID of the DCM to which the DCM question belongs. |
| DCM_QUESTION_GROUP_ID | Not null | NUMBER(10) | DCM-wide: The ID of the DCM question group to which the DCM question belongs. |
| DISPLAY_SN | Not null | NUMBER(3) | Layout-specific: A sequence number to control the order of the DCM questions in the DCM maintenance application; the order in the default layout for the DCM subset layout; and, for the base DCM, the order in the defaulted data extract views. |
| DVG_MODIFIABLE_FLAG | Not null | VARCHAR2(1) | DCM-wide: A flag to control whether the discrete value group (DVG) Name field for the question can be modified. If this field is Y, the current value of the DVG name can be modified (including from null to a value). Constrained to be N in the DCM maintenance application if the value of the corresponding DCM_MODIFIABLE_FLAG is N in the Global Library question group question, or, if none, the Global Library question. |
| MANDATORY_FLAG | Not null | VARCHAR2(1) | DCM-wide: A flag to control whether a response to the question must be entered. If this field is Y, a response to the question must be entered during data entry or batch data load, or a univariate mandatory discrepancy is created. |
| QUESTION_NAME | Not null | VARCHAR2(20) | DCM-wide: Name of the question in the Global Library that was copied to the DCM when a question group was added to the DCM or when an individual Global Library question was added to the DCM. Defaulted from questions; not modifiable in the DCM. |
| OCCURRENCE_SN | Not null | NUMBER(3) | DCM-wide: A sequence number to indicate the instance number when there is more than one instance of the same Global Library question in the same DCM QUESTION_GROUP. By convention, the OCCURRENCE_SN is 0 when there is only a single instance. When there are multiple instances, they are numbered sequentially from 1. The system enforces that the numbering remains sequential when instances are added or removed. |
| QUESTION_DATA_TYPE_CODE | Not null | VARCHAR2(15) | DCM-wide: A code for the type of data expected as a response to the Global Library question. This type of data is defaulted from questions, and is not modifiable in the DCM. |
| INDICATOR_FLAG | Not null | VARCHAR2(1) | DCM-wide: A flag to control whether the question is an indicator question. If this field is Y, the question is an indicator question and the value in the DISCRETE_VAL_IND_VALUE field specifies that the other questions in the question group are expected to have responses during data entry. In a repeating DCM question group, only a single response is collected for the indicator question so that in Oracle Clinical data entry, the indicator question is treated as a non-repeating question. In the batch validation process, Oracle Clinical creates indicator-type discrepancies when the value for the indicator question is inconsistent with the presence or absence of responses to the other questions in the question group. |
| REQUIRED_FLAG | Not null | VARCHAR2(1) | DCM-wide: A flag to show if the question is required in the DCM question group. Defaulted from question group questions; not modifiable in the DCM. If this field is Y, the COLLECTED_FLAG can not be changed to N. |
| SIGHT_VERIFICATION_FLAG | Not null | VARCHAR2(1) | Layout-specific: A flag to control second pass data entry. If this field is Y, the system completes the second pass data entry response field with the response from the first pass, allowing the response to be visually verified instead of being re-keyed. If this field is N, the second pass field is left blank and requires reentry. |
| VALIDATION_CHANGE_TS | Not null | DATE | Subset-specific: A system-maintained timestamp that records the last time that an attribute affecting the discrepancy status of the DCM question was changed. These attributes include: UPPER_BOUND, LOWER_BOUND, MANDATORY_FLAG, LENGTH, DECIMAL_PLACES, DERIVED_FLAG, DISCRETE_VAL_GRP_ID, and DATE_TIME_TYPE_CODE, which are all DCM-wide, and DISCRETE_VAL_GRP_SUBSET_NM, which is subset-wide. |
| DERIVED_LOCK_FLAG | Not null | VARCHAR2(1) | Not currently used. |
| ENTERABLE_FLAG | Not null | VARCHAR2(1) | Layout-specific: A flag to control whether responses to a question can be entered during data entry. If this field is Y, a response to the question can be entered through the interactive data entry screen. Even if the field is not enterable, a value can be supplied by the DEFAULT_RESPONSE_TEXT field. |
| DISPLAYED_FLAG | Not null | VARCHAR2(1) | Layout-specific: A flag to control whether responses to the question are displayed on the data entry screen. If this field is Y, the responses are displayed on the data entry screens. This is most relevant to derived questions where the value is not to be entered but the derived value can be seen. |
| COLLECTED_FLAG | Not null | VARCHAR2(1) | DCM-wide: A flag to control whether the question is collected in the clinical study. This field must be Y if the Required In DCM Ques Group? field is Y. If this field is N, the question is not collected in the clinical study, so it does not appear either on the data entry screens or in the data extract views. |
| AUTOSKIP_FLAG | Not null | VARCHAR2(1) | DCM-wide: A flag to control whether the question is collected in the clinical study. This field must be Y if the Required In DCM Ques Group? field is Y. If this field is N, the question is not collected in the clinical study, so it does not appear either on the data entry screens or in the data extract views. |
| DERIVED_FLAG | Not null | VARCHAR2(1) | DCM-wide: A flag to control whether responses to the question are derived. If this field is Y, responses to the question are entered and modified exclusively through a Derivation Procedure. Responses cannot be entered or modified through the data entry subsystem. |
| QUESTION_ID | Not null | NUMBER(10) | DCM-wide: The ID of the question (from the Questions table) corresponding to the DCM question. |
| UPPER_CASE_FLAG | Not null | VARCHAR2(1) | Layout-specific: A flag to indicate whether the data entry screen forces all character input for the DCM question to capital letters. If this field is N, no character is forced to uppercase; mixed-case characters retain the case in which they were entered. |
| SAS_NAME | Not null | VARCHAR2(8) | DCM-wide: An identifier for the DCM question when accessed through the SAS package, after creating the data extract views. The SAS name defaults from the question's SAS_NAME, but the system modifies it by appending the OCCURRENCE_SN when multiple occurrences are created. This field is not user-modifiable. |
| SAS_LABEL | Not null | VARCHAR2(40) | DCM-wide: Text description of the question supplied to the SAS Labels field in the SAS views. Defaulted from question group questions. Freeform text field, in mixed case. |
| LENGTH | Not null | NUMBER(3) | DCM-wide: The maximum number of characters for responses to the question. Defaulted from question group questions and constrained to be at least as long as the corresponding question's length. Once increased, a DCM question's length can not be decreased. |
| COLLECTED_IN_SUBSET_FLAG | Not null | VARCHAR2(1) | Subset-wide: A flag to control whether the question is collected in this DCM subset. This flag must be Y if the Required In DCM Quest Grp? field is Y and cannot be Y if the Collect in Study? field is N. If this field is N, the question is not collected in the DCM subset, so it cannot appear on the data entry screens. The question does appear in the data extract views with a null value for data entered for this DCM subset. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | DCM-wide: The ID of the clinical study to which the DCM question belongs. |
| DISCRETE_VAL_IND_VALUE | Null | VARCHAR2(80) | DCM-wide: The value for the indicator question that indicates that other questions in the DCM question group should have responses. See INDICATOR_FLAG. |
| DISCRETE_VAL_GRP_ID | Null | NUMBER(25) | DCM-wide: The ID of the discrete value group associated with this DCM question. Modifiable only if the DVG_MODIFIABLE_FLAG is Y. |
| DISCRETE_VAL_GRP_SUBSET_NM | Null | NUMBER(3) | Subset-wide: Specifies the number of the DVG subset associated with this DCM question. |
| HELP_TEXT | Null | VARCHAR2(200) | Layout-specific: Freeform text, in mixed case, available for display during data entry. |
| UPPER_BOUND | Null | VARCHAR2(45) | DCM-wide: Specifies the highest inclusive value allowed for the DCM question without causing a univariate discrepancy. |
| VALIDATION_FAILURE_TYPE_CODE | Null | VARCHAR2(15) | DCM-wide: A code to specify the severity of a univariate discrepancy if one is created. |
| DATA_ENTRY_DISPLAY_LENGTH | Null | NUMBER(3) | Layout-specific: The width of the field, in number of characters, displayed for input on the data entry screen. Used by default screen layout generation and modified by field length changes made through the layout editor. This number cannot be greater than the DCM question length. |
| DEFAULT_PROMPT | Null | VARCHAR2(60) | Layout-specific: Freeform text, in mixed case, that is used by the default screen layout generation to create the default data entry screens. Also used to describe the DCM question in some applications. |
| DECIMAL_PLACES | Null | NUMBER(2) | DCM-wide: Specifies the expected maximum number of digits to the right of the decimal point for a response to a number question. Entry of a larger number of digits causes a univariate PRECISION discrepancy. |
| DEFAULT_RESPONSE_TEXT | Null | VARCHAR2(200) | Layout-specific: Default response displayed at entry time. Constrained by the system to be compatible with the DCM question's data type. |
| LOWER_BOUND | Null | VARCHAR2(45) | DCM-wide: Specifies the lowest inclusive value allowed for the DCM question without causing a univariate discrepancy. |
| QUESTION_GROUP_QUESTION_ID | Null | NUMBER(10) | DCM-wide: The ID of the question group question corresponding to this DCM question. Null if the DCM question was created by adding a single question directly to the DCM question group. |
| DVG_SUB_TYPE_CODE | Null | VARCHAR2(15) | DCM-wide: A code for the type of DVG assigned to the DCM question. Defaulted from the DVG associated with the DCM question; not modifiable in the DCM. |
| X_POSITION | Null | NUMBER(3) | Layout-specific: The x-coordinate on the data entry screen where the question should start. |
| Y_POSITION | Null | NUMBER(3) | Layout-specific: The y-coordinate on the data entry screen where the question should start. |
| PAGE_NUM | Null | NUMBER(3) | Layout-specific: The data entry screen number where this DCM question will appear. This field is not related to the page numbering attributes defined in DCI_BOOKS and DCIS that are used for page tracking. |
| DATE_TIME_TYPE_CODE | Null | VARCHAR2(3) | DCM-wide: A code to specify the expected precision of the data for a question with data type of DATE or TIME. Valid values are: DMY, MY, Y for dates, and HMS or HM for times. Entry of a date or time with less information than specified results in the creation of a univariate PARTIAL_DATE discrepancy. |
| MODIFICATION_TS | Null | DATE | Layout-specific. The date and time when this layout was last modified for this DCM subset. |
| MODIFIED_BY | Null | VARCHAR2(30) | Layout-specific. The user who last modified this layout for this DCM subset. |
| REPLICATION_IND | Null | VARCHAR2(1) | v |
| HAS_REPEAT_DEFAULTS_FLAG | Not null | VARCHAR2(1) | Subset-wide: A system-maintained flag to indicate if default values have been specified for the various repeats of the DCM question. These repeating defaults are in the DCM_QUES_REPEAT_DEFAULTS table. All layouts of the same DCM subset are constrained to have the same set of repeating defaults. |
| DCM_QUE_SUB_GROUP_SN | Not null | NUMBER(4) | Layout-specific: An internal, system-maintained column used to track when DCM questions from one DCM question group are moved in the DCM layout editor to appear both before and after another DCM question group. |
| HAS_CONDITIONAL_BRANCH_FLAG | Not null | VARCHAR2(1) | Layout-specific: A system-maintained flag to indicate if conditional branching has been specified. The conditional branch information is in the table DCM_CONDITIONAL_BRANCHES. |
| DATE_TIME_FORMAT_CODE | Not null | VARCHAR2(15) | Layout-specific: A code to control which portions of the date or time question to display on data entry screens. Possible values come from the reference codelist DATE_TIME_FORMAT_CODE. Values are DMY (Day Month Year), MY (Month Year), Y (Year), HMS (Second), and HM (Hour Minute). |
| SCREEN_ITEM_NUM | Not null | NUMBER(3) | Layout-specific: An internal, system-maintained column that specifies the display order of data entry fields within the data entry screen page. This number reflects the actual field number left-to-right, top-to-bottom on the screen, counting repeating instances of the fields on the data entry screen. Thus the sequence has gaps when a repeating question group that displays multiple repeats precedes the DCM question on the entry screen. |
| ALPHA_DVG_MODIFIABLE_FLAG | Not null | VARCHAR2(1) | Flag to indicate if the alpha discrete value group assigned to this question is modifiable. |
| ALPHA_DVG_ID | Not null | NUMBER(10) | The ID of the alpha discrete value group assigned to this question. |
| ALPHA_DVG_SUBSET_NUM | Not null | NUMBER(3) | The subset number of the alpha discrete value group assigned to this question. |
| HAS_GRAPHIC_BLOCKS_FLAG | Null | VARCHAR2(10) | Indicates whether target blocks are defined for the DCM question in the graphic layout. The value CB denotes a conditional block is defined for the question, and a value TB denotes that an indicator block is defined for the question. |
This table holds default responses to repeating questions in a repeating question group; for example, 1:00, 2:00, 4:00, and 6:00 could be default values for a repeating PLANNED_TIME question. The DCM maintenance application ensures that all layouts of the same DCM subset have the same repeating default values. Repeating defaults are not used by batch data load.
For the column definitions in this section, DCM-wide attributes are labeled "DCM-WIDE," DCM subset-wide attributes are labeled "SUBSET-WIDE," and attributes that can change for each DCM layout are labeled "LAYOUT-SPECIFIC." Refer to the introduction to the DCM table for the full definitions of these attributes.
Primary Key DCM_QUES_REP_DFTS_ID
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| DCM_QUESTIONS | DCM_QUESTION_ID | DCM_QUESTION_ID |
| DCM_QUESTIONS | DCM_SUBSET_SN | DCM_QUE_DCM_SUBSET_SN |
| DCM_QUESTIONS | DCM_LAYOUT_SN | DCM_QUE_DCM_LAYOUT_SN |
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDIES |
DCM_QUES_REPEAT_DEFAULTS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| DCM_QUES_REP_DFTS_ID | Not null | NUMBER(10) | Layout-specific: A unique, system-generated ID for the DCM question repeat default. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | DCM-wide: The ID of the clinical study to which the DCM question repeat default belongs. |
| DCM_QUESTION_ID | Not null | NUMBER(10) | Subset-wide: The ID of the DCM question to which the default is assigned. |
| DCM_SUBSET_SN | Not null | NUMBER(2) | Subset-wide: The subset number of the DCM to which the DCM repeat default belongs. |
| DCM_LAYOUT_SN | Not null | NUMBER(2) | Layout-specific: The layout number of the DCM to which the DCM repeat default belongs. |
| REPEAT_SN | Not null | NUMBER(4) | Subset-wide: A sequence number for the default within the repeating question. |
| CREATION_TS | Not null | DATE | Subset-wide: the date and time when this DCM question repeat default was created. |
| CREATED_BY | Not null | VARCHAR2(30) | Subset-wide: the user who created this DCM question repeat default. |
| DEFAULT_VALUE_TEXT | Null | VARCHAR2(200) | Subset-wide: The value displayed for the specified repeat of the question. If the question is associated with a DVG, only that DVG's values can be entered as the defaults. |
| REPLICATION_IND | Null | VARCHAR2(1) | A flag to show if the DCM repeating question default has been replicated from another location. |
| MODIFIED_BY | Null | VARCHAR2(30) | Subset-wide: the user who last modified this DCM question repeat default. |
| MODIFICATION_TS | Null | DATE | Subset-wide: the date and time when this DCM question repeat default was last modified. |
A DCM schedule records at which clinical planned event a DCM subset is planned to be collected. It is used to drive the Missing and Overdue DCMs report. Since the DCM schedule is specific to a DCM subset and not to a particular DCM layout, there is only a partial foreign key to DCMS.
Rows exist in this table only where the DCM subset is scheduled; there are no rows where the SCHEDULE_TYPE_CODE is null.
Primary Key DCM_SCHEDULE_DCM_ID, DCM_SCHEDULE_DCM_SUBSET_SN, CLIN_PLAN_EVE_ID
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
| CLINICAL_PLANNED_EVENTS | CLIN_PLAN_EVE_ID | CLIN_PLAN_EVE_ID |
| DCMS (partial) | DCM_SCHEDULE_DCM_ID | DCM_ID |
| DCMS (partial) | DCM_SCHEDULE_DCM_SUBSET_SN | DCM_SUBSET_SN |
DCM_SCHEDULES Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| DCM_SCHEDULE_DCM_ID | Not null | NUMBER(10) | The ID of the DCM scheduled by the DCM schedule. |
| DCM_SCHEDULE_DCM_SUBSET_SN | Not null | NUMBER(3) | The subset number of the DCM scheduled by the DCM schedule. |
| CLIN_PLAN_EVE_ID | Not null | NUMBER(10) | The ID of the clinical planned event at which the DCM is scheduled. |
| CREATION_TS | Not null | DATE | Date and time when this DCM schedule was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this DCM schedule. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which the DCM schedule belongs. |
| SCHEDULE_TYPE_CODE | Not null | VARCHAR2(15) | A code to indicate if the DCM is expected or optional. Possible values come from the reference codelist SCHEDULE_TYPE_CODE. Values are E (Expected) and O (Optional). |
| MODIFICATION_TS | Not null | DATE | Date and time when this DCM schedule was last modified. |
| MODIFIED_BY | Not null | VARCHAR2(30) | User who last modified this DCM schedule. |
| REPLICATION_IND | Not null | VARCHAR2(1) | A flag to show if the DCM schedule has been replicated from another location. |
A discrepancy entry is created each time a univariate or multivariate validation test fails. It can be cleared by either changing the received data or changing the validation test concerned.
The test table for DISCREPANCY_ENTRIES is DISCREPANCY_ENTRIEST.
Primary Key DISCREPANCY_ENTRY_ID
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
| PATIENT_POSITIONS | PATIENT_POSITION_ID | PATIENT_POSITION_ID |
| PROCEDURES | PROCEDURE_ID | PROCEDURE_ID |
| PROCEDURES | PROCEDURE_VER_SN | PROCEDURE_VER_SN |
| PROCEDURE_DETAILS | PROCEDURE_DETAIL_ID | PROCEDURE_DETAIL_ID |
| RECEIVED_DCMS | RECEIVED_DCM_ID | RECEIVED_DCM_ID |
| RECEIVED_DCMS | RECEIVED_DCM_ENTRY_TS | RECEIVED_DCM_ENTRY_TS |
| RESPONSES | RESPONSE_ID | RESPONSE_ID |
| RESPONSES | RESPONSE_ENTRY_TS | RESPONSE_ENTRY_TS |
| DCM_QUESTIONS (partial) | DCM_QUESTION_ID | DCM_QUESTION_ID |
| OCL_INVESTIGATORS | INVESTIGATOR_ID | INVESTIGATOR_ID |
| OCL_SITES | SITE_ID | SITE_ID |
| ACTUAL_EVENTS | ACTUAL_EVENT_ID | ACTUAL_EVENT_ID |
| CLINICAL_PLANNED_EVENTS | CLIN_PLAN_EVE_ID | CLIN_PLAN_EVE_ID |
| DISCREPANCY_ENTRY_REVIEW_HIST | DISCREPANCY_ENTRY_ID | DISCREPANCY_ENTRY_ID |
| VALIDATION_REPORTED_VALUES | DISCREPANCY_ENTRY_ID | DISCREPANCY_ENTRY_ID |
DISCREPANCY_ENTRIES Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| DISCREPANCY_ENTRY_ID | Not null | NUMBER(10) | A unique, system-generated ID for the discrepancy. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created the discrepancy. |
| CREATION_TS | Not null | DATE | Date and time when this discrepancy was created. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which the discrepancy belongs. |
| INVESTIGATOR_ID | Not null | NUMBER(10) | The ID of the investigator responsible for the discrepancy. |
| SITE_ID | Not null | NUMBER(10) | The ID of the investigator responsible for the discrepancy. |
| PATIENT_POSITION_ID | Not null | NUMBER(10) | The ID of the patient that the discrepancy belongs to. |
| DE_SUB_TYPE_CODE | Not null | VARCHAR2(15) | System-controlled classification of the discrepancy according to how the discrepancy was generated. Possible values come from the system reference codelist DE SUB_TYPE_CODE. Values are UNIVARIATE, MULTIVARIATE, MANUAL, and INDICATOR. |
| DISCREPANCY_STATUS_CODE | Not null | VARCHAR2(15) | System-controlled status that indicates whether the discrepancy is current or obsolete. Possible values come from the system codelist DISCREPANCY_STATUS_CODE. Values are CURRENT and OBSOLETE. |
| DISCREPANCY_REV_STATUS_CODE | Not null | VARCHAR2(15) | Current status of the discrepancy in the discrepancy management process. A new univariate discrepancy is automatically UNREVIEWED, while the initial review status for multivariate discrepancies is dictated by the originating Procedure definition (details block). These and other values used in the discrepancy life cycle come from the installation reference codelist DISCREPANCY_REV_STATUS_CODE. If a discrepancy is resolved by Oracle Clinical — for instance, due to a validation or data change — Oracle Clinical puts the value CLOSED in this field. |
| SUSPECT_FLAG | Not null | VARCHAR2(1) | A flag used internally during the batch validation process. Its value has no significance outside of this process. |
| IRRESOLVABLE_FLAG | Not null | VARCHAR2(1) | A flag to show whether the discrepancy is being regarded as irresolvable, or in need of further processing. This flag is maintained by the system. It is set to Y when the value of DISCREPANCY REV STATUS CODE (drawn from the DISCREPANCY REV STATUS CODE reference codelist) has a reference codelist long value of IRRESOLVABLE. It is set to N otherwise. |
| FIRST_PASS_ERROR_FLAG | Not null | VARCHAR2(1) | Not used. |
| DISCREPANCY_TYPE_CODE | Null | VARCHAR2(15) | A code to classify the type of discrepancy. For univariate discrepancies, possible values come from the system reference codelist DISCREPANCY_TYPE_CODE.
Values for the DISCREPANCY_TYPE_CODE are: DVG DATA TYPE LOWERBOUND SCT_MISSING DVG SUBSET LENGTH PRECISION PARTIAL DATE THESAURUS UPPERBOUND PT_MISSING TMS_EVAL For multivariate discrepancies, values come from the installation codelist PROCEDURE_TYPE_CODE. |
| RECEIVED_DCM_ID | Null | NUMBER(10) | Not used. |
| DCM_QUESTION_ID | Null | NUMBER(10) | The ID of the DCM question that caused the discrepancy. |
| RECEIVED_DCM_ENTRY_TS | Null | DATE | The entry timestamp of the received DCM that contains the discrepancy. |
| PROCEDURE_DETAIL_ID | Null | NUMBER(10) | The ID of the Procedure Detail that raised the discrepancy. |
| PROCEDURE_ID | Null | NUMBER(10) | The ID of the Procedure that raised the discrepancy. |
| PROCEDURE_VER_SN | Null | NUMBER(3) | The version number of the Procedure that raised the discrepancy. |
| RESPONSE_ID | Null | NUMBER(10) | The ID of the response that has the discrepancy against it. |
| RESPONSE_ENTRY_TS | Null | DATE | The entry timestamp of the response that has the discrepancy against it. |
| DISCREPANCY_RESOLU_TYPE_CODE | Null | VARCHAR2(15) | A code to clarify the discrepancy review status. When the Review Status field has a value of UNREVIEWED, this field is blank. When the discrepancy is closed by the system, this field is automatically completed with an appropriate value. Values come from the installation reference codelist DISCREPANCY_RESOLU_TYPE_CODE. If the long value of the value in the codelist contains the value IRRESOLVABLE, the system also sets the value of the IRRESOLVABLE_FLAG to Y. If a discrepancy is resolved by Oracle Clinical — due to a validation or data change, for example — Oracle Clinical puts the following codes in this field: DATA CHANGE – Data change VALID. CHANGE – Validation change VALID. RETIRED – Validation retired DVG – Discrete value group DVG SUBSET – Discrete value group subset THESAURUS – Thesaurus discrete value group DATA REMOVED – RDCI/RDCM/response was deleted |
| DISCREPANCY_CHG_REASON_TYPE | Null | VARCHAR2(15) | (Obsolete) |
| COMMENT_TEXT | Null | VARCHAR2(2000) | Freeform text to hold a comment about the discrepancy. It is by default populated with either the DISCREPANCY_TYPE_CODE for univariate discrepancies, or the message text as defined in the originating Procedure Detail for multivariate discrepancies. |
| CLIN_PLAN_EVE_ID | Null | NUMBER(10) | The ID of the clinical planned event to which the discrepancy is linked. |
| ACTUAL_EVENT_ID | Null | NUMBER(10) | The ID of the actual event to which the discrepancy is linked. |
| DCM_ID | Null | NUMBER(10) | The ID of the DCM that has the discrepancy against it. |
| SUBEVENT_NUMBER | Null | NUMBER(2) | Together with CLIN_PLAN_EVE_ID, the identifier of the actual event to which the discrepancy is linked. |
| MODIFICATION_TS | Null | DATE | Timestamp for the last modification of this discrepancy, if any. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this discrepancy, if any. |
| DCF_ID | Null | NUMBER(10) | The ID of the most recent DCF created to resolve the discrepancy. |
| ASSOCIATED_ID | Null | NUMBER(10) | The ID of another discrepancy entry with which the current discrepancy has been manually associated, usually to indicate that the discrepancy entry is probably caused by the same data anomaly and is being passively reviewed through a query on the associated discrepancy. |
| CRF_PAGE_NUMBER | Null | VARCHAR2(20) | The page number of the DCI book page containing the question that caused the discrepancy. |
| DCF_COMMENT | Null | VARCHAR2(200) | The comment on the most recent DCF created to resolve the discrepancy. |
| INTERNAL_COMMENT_TEXT | Null | VARCHAR2(2000) | Field allowing entry of E-mail like comments between data managers. Includes date, time, and username. |
| RESOLUTION_TEXT | Null | VARCHAR2(2000) | The text specified on the DCF to request clarification of the discrepancy. |
| OWNING_GROUP_TYPE_CODE | Null | VARCHAR2(15) | Specifies the type of owning group, such as DM or CRA that is associated with the Discrepancy. |
| CRF_PAGE_NUMBER_SORT | Null | VARCHAR2(100) | Specifies the sort order for CRF pages, regardless of the CRF Page Number value. |
| FLEX_FIELD1 | Null | VARCHAR2(15) | Field allowing entry of value for company-specified question. |
| FLEX_FIELD2 | Null | VARCHAR2(15) | Field allowing entry of value for company-specified question. |
| LAST_CHANGE_TS | Null | DATE | Indicates the last time that the multivariate discrepancy was modified. Used for inclusion in data replication. The value defaults to TO_DATE (19000101000000,'YYYYMMDDHH24MISS'). |
| LAST_VRV_CHANGE_TS | Null | DATE | Indicates the last time the Validation Reported Values associated with this multivariate discrepancy were modified. Used for inclusion in data replication. The value defaults to TO_DATE (19000101000000,'YYYYMMDDHH24MISS') |
This table holds an audit trail of discrepancy review status, discrepancy resolution status, and discrepancy comment changes over time. The table contains an entry for the current values in the discrepancy as well as all previous values.
The test table for DISCREPANCY_ENTRY_REVIEW_HIST is DISCREPANCY_ENTRY_REVIEW_HISTT.
Primary Key DISCREPANCY_ENTRY_ID, DISCREPANCY_REV_STATUS_CODE, CREATION_TS
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| DISCREPANCY_ENTRIES | DISCREPANCY_ENTRY_ID | DISCREPANCY_ENTRY_ID |
DISCREPANCY_ENTRY_REVIEW_HIST(T) Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| DISCREPANCY_ENTRY_ID | Not null | NUMBER(10) | The ID of the discrepancy to which the discrepancy history belongs. |
| DISCREPANCY_REV_STATUS_CODE | Not null | VARCHAR2(15) | Current status of the discrepancy in the discrepancy management process. A new univariate discrepancy is automatically UNREVIEWED, while the initial review status for multivariate discrepancies are dictated by the originating Procedure definition (details block). These and other values used in the discrepancy life cycle come from the installation reference codelist DISCREPANCY_REV_STATUS_CODE. If a discrepancy is resolved by Oracle Clinical — for instance, due to a validation or data change — Oracle Clinical puts the value CLOSED in this field. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this discrepancy history entry. |
| CREATION_TS | Not null | DATE | Date and time when this discrepancy history entry record was created. |
| COMMENT_TEXT | Null | VARCHAR2(2000) | Freeform text to hold a comment about the discrepancy. It is by default populated with either the DISCREPANCY_TYPE_CODE for univariate discrepancies, or the message text as defined in the originating Procedure Detail for multivariate discrepancies. |
| NEXT_DISCREPANCY_REV_STATUS | Null | VARCHAR2(15) | Null for the current record for each discrepancy, this field contains the value the DISCREPANCY_REV_STATUS_CODE changed to on the next update. This column supports summarization of status change transitions. |
| NEXT_STATUS_TS | Null | DATE | The timestamp of the superseding update, above. Facilitates duration calculations for review statuses. |
| CURRENT_FLAG | Null | VARCHAR2(1) | Shows if this history record reflects the value of the current discrepancy entry. |
| DISCREPANCY_RESOLU_TYPE_CODE | Null | VARCHAR2(15) | A code to clarify the discrepancy review status. When the review status field has a value of UNREVIEWED, this field is blank. When the discrepancy is closed by the system, this field is automatically completed with an appropriate value. Values come from the installation reference codelist DISCREPANCY_RESOLU_TYPE_CODE. If the long value of the value in the codelist contains the value IRRESOLVABLE, the system also sets the value of the IRRESOLVABLE_FLAG to Y. If a discrepancy is resolved by Oracle Clinical — due to a validation or data change, for example — Oracle Clinical puts the following codes in this field:
|
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which the discrepancy belongs. |
| DE_SUB_TYPE_CODE | Not null | VARCHAR2(15) | Denormalized from discrepancy entries. |
| INTERNAL_COMMENT_TEXT | Null | VARCHAR2(2000) | Field allowing entry of E-mail like comments between data managers. Includes date, time, and username. |
| RESOLUTION_TEXT | Null | VARCHAR2(2000) | The text specified on the DCF to request clarification of the discrepancy. |
| FLEX_FIELD1 | Null | VARCHAR2(15) | Field allowing entry of value for company-specified question. |
| FLEX_FIELD2 | Null | VARCHAR2(15) | Field allowing entry of value for company-specified question. |
Discrete values are predefined values a question can have. Discrete values are grouped into discrete value groups (DVGs). To avoid duplication of values, subsets of a discrete value group can be defined. The superset of values is referred to as the base DVG.
Primary Key DISCRETE_VALUE_DVG_ID, DISCRETE_VALUE_VALUE, DISCRETE_VALUE_DVG_SUBSET_NM
Related Tables and Foreign Keys
| Name of related table | Foreign key name in the current table | Foreign key name in the related table |
|---|---|---|
| DISCRETE_VALUE_GROUPS | DISCRETE_VALUE_DVG_ID | DISCRETE_VAL_GRP_ID |
| DISCRETE_VALUE_GROUPS | DISCRETE_VALUE_DVG_SUBSET_NM | DISCRETE_VAL_GRP_SUBSET_NUM |
| QUESTION_GROUP_QUESTIONS | DISCRETE_VALUE_VALUE | DISCRETE_VALUE_GRP_IND_VALUE |
| DCM_QUESTIONS | DISCRETE_VALUE_VALUE | DISCRETE_VALUE_GRP_IND_VALUE |
DISCRETE_VALUES Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| DISCRETE_VALUE_DVG_ID | Not null | NUMBER(10) | The ID of the discrete value group to which the discrete value belongs. |
| DISCRETE_VALUE_DVG_SUBSET_NM | Not null | NUMBER(3) | A number to identify the DVG subset. A DVG with Number 0 is called the base subset. |
| DISCRETE_VALUE_VALUE | Not null | VARCHAR2(80) | An acceptable response to the question associated with this DVG. The value must be unique within the DVG. |
| DISPLAY_SN | Not null | NUMBER(3) | A positive or negative integer that controls the order the system presents discrete values during data entry. System-enforced to be sequential positive integers starting at 1 if the attribute RESEQUENCE_FLAG = Y. |
| ACTIVE_FLAG | Not null | VARCHAR2(1) | A flag to control if the discrete value appears in the list of values for data entry. A response matching a value with N in the active field creates a univariate DVG discrepancy. |
| LONG_LABEL_DESCRIPTION | Null | VARCHAR2(200) | Freeform text, in mixed case, that describes the discrete value. |
| REPLICATION_IND | Null | VARCHAR2(1) | A flag to indicate if the discrete value has been replicated to another location. |
| CREATE_MAND_DISCREP_FLAG | Not Null | VARCHAR(1) | Flag to indicate if a mandatory discrepancy should be created. |
Discrete value groups (DVGs) are sets of possible values a question can have. To avoid duplication of values, subsets of a discrete value group can be defined. The superset of values is referred to as the base DVG.
Primary Key DISCRETE_VALUE_GRP_ID, DISCRETE_VAL_GRP_SUBSET_NUM
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| QUESTIONS | DISCRETE_VALUE_GRP_ID | DISCRETE_VALUE_GRP_ID |
| QUESTIONS | DISCRETE_VAL_GRP_SUBSET_NUM | DISCRETE_VAL_GRP_SUBSET_NUM |
| DCMS | DISCRETE_VALUE_GRP_ID | QUAL_QUESTION_DVG_ID |
| DCMS | DISCRETE_VAL_GRP_SUBSET_NUM | QUAL_QUESTION_DVG_SUBSET_NUM |
| QUESTION_GROUP_QUESTIONS | DISCRETE_VALUE_GRP_ID | DISCRETE_VAL_GRP_ID |
| QUESTION_GROUP_QUESTIONS | DISCRETE_VAL_GRP_SUBSET_NUM | DISCRETE_VAL_GRP_SUBSET_NUM |
| DCM_QUESTIONS | DISCRETE_VALUE_GRP_ID | DISCRETE_VAL_GRP_ID |
| DCM_QUESTIONS | DISCRETE_VAL_GRP_SUBSET_NUM | DISCRETE_VAL_GRP_SUBSET_NUM |
| DCM_QUESTIONS | DISCRETE_VALUES | DISCRETE_VALUE_GRP_ID |
| DISCRETE_VALUE _DVG_ID | DISCRETE_VAL_GRP_SUBSET_NUM | DISCRETE_VALUE_DVG_SUBSET_NUM |
DISCRETE_VALUE_GROUPS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| DISCRETE_VALUE_GRP_ID | Not null | NUMBER(10) | A unique, system-generated ID for the discrete value group. |
| DISCRETE_VAL_GRP_SUBSET_NUM | Not null | NUMBER(3) | A number for the DVG subsets. A DVG with Number 0 is called the base subset. The associated discrete values for DVGs with subset numbers greater than 0 are constrained to be within the set of discrete values for the DVG with the same DISCRETE_VALUE_GRP_ID and a subset number of 0. DVGs with a DVG_SUB_TYPE_CODE of THESAURUS are constrained to have only a single subset, 0. |
| CREATION_TS | Not null | DATE | Date and time when the DVG was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this DVG. |
| NAME | Not null | VARCHAR2(30) | Unique identification, within a domain, of the discrete value group (DVG), which is a grouping of acceptable responses to a question of data. |
| DOMAIN | Not null | VARCHAR2(15) | A Global Library object-naming convention that facilitates the unique naming or grouping of objects. |
| DVG_SUB_TYPE_CODE | Not null | VARCHAR2(15) | A code to specify the location of DVG values as either INTERNAL or THESAURUS. Possible values come from the reference codelist DVG_SUB_TYPE_CODE. Values are: INTERNAL (Uses an internal list of values), or THESAURUS (Uses tables and columns to access external information).
The values of INTERNAL type DVGs are contained in the DISCRETE_VALUES table. The values of THESAURUS type DVGs are in a separate table or view named in the THES_VIEW_NAME field. |
| DESCRIPTION | Not null | VARCHAR2(70) | Freeform text, in mixed case, describing the discrete value group. |
| DISCRETE_VAL_GRP_STATUS_CODE | Not null | VARCHAR2(15) | A code to show if the discrete value group is provisional (P), active (A), or retired (R). |
| DISCRETE_VAL_GRP_TYPE_CODE | Not null | VARCHAR2(15) | A user-defined code to classify the DVG. Valid values are maintained in the installation reference codelist DISCRETE_VAL_GRP_TYPE_CODE. |
| LAST_STATUS_CHANGE_TS | Not null | DATE | The date and time that the DISCRETE_VAL_GRP_STATUS_CODE was last changed. |
| SUBSETTABLE_FLAG | Not null | VARCHAR2(1) | A flag to control if you can group some of the DVG's values into subsets. |
| EXPANDABLE_FLAG | Not null | VARCHAR2(1) | A flag to control if you can add new values to an active internal DVG. |
| ALLOW_ENTRY_BY_SEQUENCE_FLAG | Not null | VARCHAR2(1) | A flag to control if data entry's response to a question associated with the internal DVG can be the DVG sequence number rather than the value itself. Usually used when the values of the DVG themselves can be numeric and entry of sequence numbers might be ambiguous. |
| UPPER_CASE_FLAG | Not null | VARCHAR2(1) | A flag to control if the values of the response to the DVG are forced to uppercase. |
| MAX_VALUE_LENGTH | Not null | NUMBER(3) | The maximum number of characters, between 1 and 80, for each value of an internal DVG. |
| RETIREMENT_REASON_TYPE_CODE | Not null | VARCHAR2(15) | A code to show why a retired DVG was retired. |
| STATUS_COMMENT_TEXT | Null | VARCHAR2(200) | Freeform text, in mixed case, about why the DVG's status did or did not change. |
| THES_VIEW_NAME | Null | VARCHAR2(30) | THESAURUS subtype only: The name of the Oracle table or view that contains the values available as responses to a question associated with this thesaurus DVG. |
| THES_TERM_COL | Null | VARCHAR2(30) | THESAURUS subtype only: The name of the column in the Oracle table or view that contains the values available as responses for this thesaurus DVG. During data entry, operators with appropriate privileges can use the List function for acceptable values. |
| THES_TERM_LEN | Null | NUMBER(3) | THESAURUS subtype only: The number of character spaces available in the column of the Oracle table or view. |
| THES_DECODE_VIEW_NAME | Null | NUMBER(3) | THESAURUS subtype only: The name of the Oracle table or view the system uses in Data Extract to translate (or decode) the text response to a question. |
| THES_DECODE_TERM_COL | Null | VARCHAR2(30) | THESAURUS subtype only: The name of the column in the Oracle table or view in which the system searches for the text response being decoded. |
| THES_DECODE_TERM_LEN | Null | NUMBER(3) | THESAURUS subtype only: The number of character spaces available in the column of the Oracle table or view for this thesaurus DVG. |
| THES_DECODE_LABEL_COL1 | Null | VARCHAR2(30) | THESAURUS subtype only: The name of the column in the table or view to use when returning the first decode value in Data Extract. |
| THES_DECODE_LABEL_COL2 | Null | VARCHAR2(30) | THESAURUS subtype only: The name of the column in the table or view to use when returning the second decode value in Data Extract. |
| THES_DECODE_LABEL_IDCHAR1 | Null | VARCHAR2(1) | THESAURUS subtype only: Identifies, with a single character, the derived question corresponding to the Label 1 column of the Oracle table or view that contains the first decoded value. The system concatenates the short label with the SAS name of the question associated with this DVG to create the SAS name of the decoded value. |
| THES_DECODE_LABEL_LEN2 | Null | NUMBER(3) | THESAURUS subtype only: The number of character spaces available in the Label 1 column of the Oracle table or view that contains the second decoded value for a derived question. |
| THES_DECODE_LABEL_IDCHAR2 | Null | VARCHAR2(1) | THESAURUS subtype only: Identifies, with a single character, the derived question corresponding to the Label 2 column of the Oracle table or view that contains the second decoded value. The system concatenates the short label with the SAS name of the question associated with this DVG to create the SAS name of the decoded value. |
| THES_DECODE_LABEL_LEN1 | Null | NUMBER(3) | THESAURUS subtype only: The number of character spaces available in the Label 1 column of the Oracle table or view that contains the first decoded value for a derived question. |
| THES_DECODE_LABEL_COL3 | Null | VARCHAR2(30) | THESAURUS subtype only: The name of the column in the table or view to use when returning the third decode value in Data Extract. |
| THES_DECODE_LABEL_LEN3 | Null | NUMBER(3) | THESAURUS subtype only: The number of character spaces available in the Label 3 column of the Oracle table or view that contains the first decoded value for a derived question. |
| THES_DECODE_LABEL_IDCHAR3 | Null | VARCHAR2(1) | THESAURUS subtype only: Identifies, with a single character, the derived question corresponding to the Label 2 column of the Oracle table or view that contains the third decoded value. The system concatenates the short label with the SAS name of the question associated with this DVG to create the SAS name of the decoded value. |
| MODIFICATION_TS | Null | DATE | Date and time when the DVG was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this DVG. |
| REPLICATION_IND | Null | VARCHAR2(1) | A flag to show if the discrete value group has been replicated to another site. |
| RESEQUENCE_FLAG | Not null | VARCHAR2(1) | A flag to indicate if the discrete values can be re-sequenced once the user provides a sequence number for each of the values. |
| DISPLAY_TYPE_CODE | Not null | VARCHAR2(15) | The Display Type of a DVG Subset determines how the Subset is laid out if the Subset is used by a Question that is incorporated in a Graphical Layout:
LOV means that the Question is laid out to be entered as a text field. The Active values of the DVG Subset will be displayed on demand, as an LOV, during data entry; CBG means that the Question will be represented as checkboxes, one for each of the Active values in the DVG Subset; FLAG means that the Question will be represented as one checkbox, which will signify one value when checked, and another when subsequently unchecked. |
| CHECKBOX_LAYOUT_CODE | Null | VARCHAR2(15) | Checkbox Layout. This field is only meaningful if DISPLAY_TYPE_CODE is CBG. The value of this field determines the orientation in which checkboxes are laid out (Horizontal or Vertical) and the position of the value labels relative to the checkboxes (Left or Right). |
| CHECKBOX_LABEL_SOURCE_CODE | Null | VARCHAR2(15) | Checkbox Label Source. If this DVG Subset is used in a Graphic Layout, the value of this field determines whether the label for each checkbox is to be drawn from the Value or Long Value corresponding to the checkbox. |
| CHECKED_FLAG_VALUE | Null | VARCHAR2(80) | If the DISPLAY_TYPE_CODE of the DVG Subset is FLAG, this column determines which of the two active values for the DVG Subset is transmitted to the database when the Flag's checkbox is checked during data entry. If the checkbox is subsequently unchecked, the form will transmit the other Active value. For instance, if the active values are Yes and No, and Display Type is FLAG, and Checked Flag Value is Yes, then checking the flag's checkbox during data entry will transmit Yes, and subsequently clearing it will transmit No. The flag's initial value is NULL. |
DCI and DCM graphic layouts inherit their shape from a DCI Form Layout Template. It defines the work area, which is the area between the header and footer, and the size of the header and footer when previewing the layout or when generating the DCI Form. This area is used for the DCM and response related fields when generating the DCM and DCI layouts. The contents of this area can be edited in the DCM or DCI layout editor.
This table is replicated throughout an Oracle Clinical installation.
FORM_LAYOUT_TEMPLATES Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| FLT_ID | Not Null | NUMBER(10) | Unique ID of this form layout template. |
| LANGUAGE | Not Null | VARCHAR2(20) | Language for this form layout template. Language values are commonly listed in "en_US" format, which uses two-letter abbreviations for language and country. |
| FLT_NAME | Not Null | VARCHAR2(30) | The name of the form layout template. |
| CREATION_TS | Not Null | DATE | Date and time when this form layout template was created. |
| CREATED_BY | Not Null | VARCHAR2(30) | User who created this form layout template. |
| FL_WIDTH | Not Null | NUMBER(6,1) | Width (in points) of the FLT. |
| HEADER_HEIGHT | Not Null | NUMBER(6,1) | Height (in points) of the FLT header. |
| DCM_REGION_HEIGHT | Not Null | NUMBER(6,1) | Height (in points) of the FLT's DCM region. |
| FOOTER_HEIGHT | Not Null | NUMBER(6,1) | Height (in points) of the FLT's footer. |
| FLT_HEIGHT | Not Null | NUMBER(6,1) | Height (in points) of the whole FLT. |
| FLT_XML | Not Null | CLOB | A character large object that stores the XML for this form layout template. |
| STATUS | Not Null | VARCHAR2(1) | Possible values: A (Active), P (Provisional), or R (Retired). Only active layouts can be selected when specifying the Form Layout Template when generating DCM Graphic Layouts or DCI Form Version Layouts. |
| UPDATE_ALLOWED_FLAG | Not Null | VARCHAR2(1) | If Y, the Form Layout Template is updatable. This field is unchecked for form layout templates that are shipped and cannot be changed. If you want to change a Form Layout Template that has been shipped, you need to copy the Form Layout Template and modify the copy. |
| DESCRIPTION | Null | VARCHAR2(120) | Further information about this FLT. The description is displayed in LOVs when defining Form Layout Template for generating DCM graphic layouts and DCI graphic layouts. |
| FL_XML_MODIFICATION_TS | Null | DATE | Date and time when the XML for this FLT was last modified. |
| FL_XML_MODIFIED_BY | Null | VARCHAR2(30) | User who last modified the XML for this FLT. |
| MODIFICATION_TS | Null | DATE | Date and time when this FLT definition was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this FLT definition. |
A lab record contains basic details about a laboratory being used for clinical studies.
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| RANGES | LAB_ID | LAB_ID |
| LAB_RANGE_SUBSETS | LAB_ID | LAB_ID |
| FORMAT_MASKS | MASK_ID | MASK_ID |
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| LAB_ID | Not null | NUMBER(10) | A unique, system-generated ID for the lab. |
| LAB | Not null | VARCHAR2(10) | Unique external identifier of the lab. |
| LAB_NAME | Not null | VARCHAR2(60) | Unique descriptive designation of the lab. |
| CREATION_TS | Not null | DATE | Date and time when this lab was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this lab. |
| ACTIVE_FLAG | Not null | VARCHAR2(1) | A flag to show if the lab is available to supply lab ranges for the lab data entered or batch loaded for a study. |
| MODIFICATION_TS | Null | DATE | Date and time when this lab was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this lab. |
| CONTACT_FIRST_NAME | Null | VARCHAR2(15) | The first name of the principal contact at the lab; in mixed case. |
| CONTACT_LAST_NAME | Null | VARCHAR2(30) | The last name of the principal contact at the lab; in mixed case. |
| CONTACT_VOICE_PHONE | Null | VARCHAR2(25) | The phone number of the principal contact at the lab; in mixed case. |
| CONTACT_FAX_PHONE | Null | VARCHAR2(25) | The fax number of the principal contact at the lab; in mixed case. |
| LAB_MAIL_NAME | Null | VARCHAR2(40) | The name of the lab for mail; in mixed case. |
| ADDRESS1 | Null | VARCHAR2(40) | Line 1 of the lab's mailing address; in mixed case. |
| ADDRESS2 | Null | VARCHAR2(40) | Line 2 of the lab's mailing address; in mixed case. |
| ADDRESS3 | Null | VARCHAR2(40) | Line 3 of the lab's mailing address; in mixed case. |
| CITY | Null | VARCHAR2(25) | The city in the lab's mailing address; in mixed case. |
| STATE_PROVINCE | Null | VARCHAR2(20) | The state or province in the lab's mailing address. |
| POSTAL_CODE | Null | VARCHAR2(10) | The postal code or US zip code in the lab's mailing address; in mixed case. |
| COUNTRY | Null | VARCHAR2(20) | The country where the lab is located; in mixed case. |
| ACCREDITATION_START_DATE | Null | DATE | Start date of the period during which the lab is accredited. |
| ACCREDITATION_END_DATE | Null | DATE | End date of the period during which the lab was accredited. If this field is blank, there is no cut-off date. |
| CERTIFYING_AGENCY_NAME | Null | VARCHAR2(60) | Name of certifying authority that accredits the lab. |
| CERTIFICATE_NUMBER | Null | VARCHAR2(20) | Identification number of certificate issued by certifying agency. |
| LAB_COMMENT | Null | VARCHAR2(240) | Freeform text, in mixed case, containing supplementary information about the lab. |
| LAST_REPLICATION_TS | Null | DATE | Date and time this lab was last replicated from the Oracle Clinical owning location. |
| OWNING_LOCATION | Not null | VARCHAR2(15) | Name of the Oracle Clinical database that owns this lab data. |
| REPLICATE_FLAG | Not null | VARCHAR2(1) | Flag set at locations that do not own the lab data to indicate that lab ranges for this lab are to be included in lab range replication. |
| MASK_ID | Not null | NUMBER(10) | ID of the batch data load format mask to use for this lab's data. |
This table holds the mapping of acceptable units for use with particular Global Library questions.
Primary Key QUESTION_ID, LAB_UNIT_CODE
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| LAB_UNITS | LAB_UNIT_CODE | LAB_UNIT_CODE |
| QUESTIONS | QUESTION_ID | QUESTION_ID |
LAB_TEST_QUESTION_UNITS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| QUESTION_ID | Not null | NUMBER(10) | ID of the Global Library question with which you are mapping an acceptable lab test question unit. |
| LAB_UNIT_CODE | Not null | VARCHAR2(40) | A code for the lab unit. |
| CREATION_TS | Not null | DATE | Date and time when this mapping between Global Library question and lab test question unit was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this mapping between Global Library question and lab test question unit. |
| MODIFICATION_TS | Null | DATE | Date and time when this mapping was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this mapping. |
| ACTIVE_FLAG | Not null | VARCHAR2(1) | A flag to show if the lab unit is still available for use. |
A lab range subset allows for different ranges for the same questions at the same lab. For example, it could accommodate an alternative set of ranges for diabetic patients.
Primary Key LAB_ID, LAB_RANGE_SUBSET_NUM
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| LABS | LAB_ID | LAB_ID |
| RANGES | LAB_RANGE_SUBSET_NUM | LAB_RANGE_SUBSET_NUM |
LAB_RANGE_SUBSETS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| LAB_ID | Not null | NUMBER(10) | The ID of the lab to which the subset belongs. |
| LAB_RANGE_SUBSET_NUM | Not null | NUMBER(10) | A sequence number for the subset within the lab. |
| LAB_RANGE_SUBSET_CODE | Not null | VARCHAR2(15) | A code to identify the lab range subset. The code will exist in the reference codelist table called LAB_RANGE_SUBSET_CODE. |
| NAME | Not null | VARCHAR2(70) | A name for the subset. |
| ACTIVE_FLAG | Not null | VARCHAR2(1) | A flag to show if the subset is still active or if it has been retired. |
| CREATION_TS | Not null | DATE | Date and time when this lab range subset was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this lab range subset. |
| MODIFICATION_TS | Null | DATE | Date and time when this lab range subset was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this lab range subset. |
| LAST_LR_MOD_TS | Not null | DATE(1) | This timestamp is updated when a change is made to the lab range data within the lab and subset. |
This table holds lists of units used to measure lab results.
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| PREFERRED_LAB_UNITS | LAB_UNIT_CODE | LAB_UNIT_CODE |
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| LAB_UNIT_CODE | Not null | VARCHAR2(40) | A code for the lab unit. |
| CREATION_TS | Not null | DATE | Date and time when this lab unit was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this lab unit. |
| REPRESENTATIVE_LAB_UNIT_CODE | Not null | VARCHAR2(40) | These codes enable you to define lab units as equivalent in the system. For example, because the Lab Units mg/ml and mg/cc are equivalent, you can define mg/ml as their common Representative Lab Unit Code.
If you are defining a lab unit for which you do not want to specify a representative lab unit code, enter the LAB_UNIT_CODE in this column. |
| ACTIVE_FLAG | Not null | VARCHAR2(1) | A flag to show if the lab unit is still available for use. |
| MODIFICATION_TS | Null | DATE | Date and time when this lab unit was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this lab unit. |
| LAB_UNIT_NAME | Null | VARCHAR2(45) | A name for the lab unit. |
| LAB_UNIT_PRONUNCIATION | Null | VARCHAR2(60) | A phonetic name for the lab unit. |
This table defines how data measured in one lab unit should be converted into a different lab unit. Conversions of the form y = ax + b (a multiplication factor combined with the addition of a constant) are supported.
Primary Key QUESTION_ID, LAB_UNIT_CODE_CONVERT_FROM, LAB_UNIT_CODE_CONVERT_TO
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| QUESTIONS | QUESTION_ID | QUESTION_ID |
LAB_UNIT_CONVERSIONS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| QUESTION_ID | Not null | NUMBER(10) | The ID of the question to which the conversion applies. |
| LAB_UNIT_CODE_CONVERT_FROM | Not null | VARCHAR2(40) | The code of the unit of measure to convert from. For example, if you are converting temperature values from Fahrenheit to Celsius, this column stores the Fahrenheit value. |
| LAB_UNIT_CODE_CONVERT_TO | Not null | VARCHAR2(40) | The code of the unit of measure to convert to. For example, if you are converting temperature values from Fahrenheit to Celsius, this column stores the Celsius value. |
| CREATION_TS | Not null | DATE | Date and time when this lab unit conversion was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this lab unit conversion. |
| MULTIPLIER | Not null | NUMBER | The factor to multiply the "from code" by to compute the "to code". |
| MODIFICATION_TS | Null | DATE | Date and time when this lab unit conversion was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this lab unit conversion. |
| LAB_UNIT_CONVERSION_COMMENT | Null | VARCHAR2(240) | A comment about the conversion. |
| COEFFICIENT0 | Not null | NUMBER | A fixed value to be added to the result of the multiplication. |
| CONVERSION_SOURCE | Not null | VARCHAR2(15) | A code for the method used to convert from one unit to another. |
This table holds execution status information for multivariate validation jobs launched via the immediate execution API. Each submitted job will create one entry, which gets subsequently updated to reflect execution status and results.
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| PATIENT_POSITIONS | PATIENT_POSITION_ID | PATIENT_POSITION_ID |
| DCMS | DCM_ID | DCM_ID |
MV_EXECUTION_LOG Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| JOB_ID | Not null | NUMBER(10) | A unique, system-generated ID for the job. |
| SESSION_ID | Not null | NUMBER(10) | A unique identifier of the user session. |
| EXECUTION_STATUS | Not null | VARCHAR2(15) | The current execution status of the job. Possible values to be included in a reference codelist are: SUBMITTED, EXECUTING, and COMPLETED. |
| OUTCOME_STATUS | Null | VARCHAR2(15) | Reflects the outcome of the execution. Possible values to be included in a reference codelist are: SUCCESS, WARNING, and FAILURE. |
| EXECUTION_SUBMITTED_TS | Not null | DATE | Date and time when the job was submitted. |
| EXECUTION_START_TS | Not null | DATE | Date and time when that the job started to run. |
| EXECUTION_END_TS | Null | DATE | Date and time when the job finished executing. |
| EXECUTION_CONTEXT | Not null | VARCHAR2(15) | A code for the mode in which the Procedure was executed. Values come from the reference codelist PROC_EXEC_CONTEXT_EXECUTION. |
| PROCEDURE_ID | Not null | NUMBER(10) | A code for the mode in which the Procedure was executed. Values come from the reference codelist PROC_EXEC_CONTEXT_EXECUTION. |
| PROCEDURE_VERSION_SN | Not null | NUMBER(3) | The sequence number of the Procedure being executed. |
| PATIENT_ID | Not null | NUMBER(10) | The ID of the patient for whom the job is executed. |
| DCM_ID | Null | NUMBER(10) | The ID of the DCM for which the job is executed. This field is only filled when execution is requested for a particular DCM. |
| TOT_NEW_DISC | Null | NUMBER(10) | Number of new discrepancies created after the job execution. |
| TOT_SAME_DISC | Null | NUMBER(10) | Number of discrepancies that remained the same after the job execution. |
| TOT_OBS_DISC | Null | NUMBER(10) | Number of discrepancies that were made obsolete as a result of the job execution. |
OCL_INVESTIGATORS records basic details about an investigator. The test table for OCL_INVESTIGATORS is OCL_INVESTIGATORST.
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| OCL_STUDY_SITE_ROLES | INVESTIGATOR_ID | INVESTIGATOR_ID |
| RECEIVED_DCIS | INVESTIGATOR_ID | INVESTIGATOR_ID |
| RECEIVED_DCMS | INVESTIGATOR_ID | INVESTIGATOR_ID |
| DISCREPANCY_ENTRIES | INVESTIGATOR_ID | INVESTIGATOR_ID |
| REGIONS | STATE | REGION_CODE |
| REGIONS | COUNTRY | REGION_CODE |
OCL_INVESTIGATORS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| INVESTIGATOR_ID | Not null | NUMBER(10) | A unique, system-generated ID for the investigator. |
| INVESTIGATOR | Not null | VARCHAR2(10) | An external unique code for the investigator. |
| FIRST_NAME | Not null | VARCHAR2(15) | The first name of the investigator. |
| LAST_NAME | Not null | VARCHAR2(20) | The last name of the investigator. |
| ACTIVE_FLAG | Not null | VARCHAR2(1) | A flag to show if the investigator is still available for new studies. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this investigator. |
| CREATION_TS | Not null | DATE | Date and time when this investigator was created. |
| PHONE_NUMBER | Not null | VARCHAR2(25) | The phone number of the investigator. |
| OWNING_LOCATION | Not null | VARCHAR2(15) | The code for the location that owns or manages the investigator. Only the owning location can modify the investigator record of the investigator. |
| COUNTRY | Null | VARCHAR2(7) | A code for the country where the investigator is located. |
| STATE | Null | VARCHAR2(7) | A code for the state or province where the investigator is located. |
| CITY | Null | VARCHAR2(25) | The city where the investigator is located. |
| ADDRESS_NAME | Null | VARCHAR2(40) | A name to start the investigator's address. |
| ADDRESS_LINE_1 | Null | VARCHAR2(40) | The first line of the investigator's address. |
| ADDRESS_LINE_2 | Null | VARCHAR2(40) | The second line of the investigator's address. |
| ADDRESS_LINE_3 | Null | VARCHAR2(40) | The third line of the investigator's address. |
| POSTAL_CODE | Null | VARCHAR2(15) | A postal or zip code of the investigator's address. |
| TITLE | Null | VARCHAR2(40) | A title for the investigator. |
| INITIALS | Null | VARCHAR2(4) | The initials of the investigator. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this investigator record. |
| MODIFICATION_TS | Null | DATE(4) | Date and time when this investigator record was last modified. |
Organization units represent parts of a company that can sponsor clinical studies.
Primary Key ORGANIZATION_UNIT_ID
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| OCL_STUDIES | ORGANIZATION_UNIT_ID | ORGANIZATION_UNIT_ID |
OCL_ORGANIZATION_UNITS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| ORGANIZATION_UNIT_ID | Not null | NUMBER(10) | A unique, system-generated ID for the organization unit. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this organization unit. |
| CREATION_TS | Not null | DATE | Date and time when this organization unit was created. |
| CODE | Not null | VARCHAR2(10) | A unique code for the organization unit. |
| DESCRIPTION | Not null | VARCHAR2(70) | A description of the organization unit. |
| END_DATE | Null | DATE | The date the organization unit ceased to be valid. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this organization unit. |
| MODIFICATION_TS | Null | DATE | Date and time when this organization unit was last modified. |
| NAME | Null | VARCHAR2(60) | A name for the organization unit. |
| START_DATE | Not null | DATE | The date that the organization unit became valid. |
Programs are groups of studies that are usually related to the same compound.
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| OCL_PROJECTS | PROGRAM_CODE | PROGRAM_CODE |
OCL_PROGRAMS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| PROGRAM_CODE | Not null | VARCHAR2(15) | A code for the program. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this program. |
| CREATION_TS | Not null | DATE | Date and time when this program was created. |
| DESCRIPTION | Not null | VARCHAR2(70) | A freeform text description of the program. |
| ACTIVE_FLAG | Not null | VARCHAR2(1) | A flag to show if the program is still active (A) or if it has been retired (R). |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this program. |
| MODIFICATION_TS | Null | DATE | Date and time when this program was last modified. |
Projects are groups of studies within a program. One example of a project is all of the studies in a particular indication. Projects are particularly important if Oracle Thesaurus Management System (TMS) is being used. Clinical studies are processed by TMS based upon the project to which they belong, so that all of the studies in a project will be processed in the same way.
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| OCL_PROGRAMS | PROGRAM_CODE | PROGRAM_CODE |
| OCL_STUDIES | PROGRAM_CODE | PROGRAM_CODE |
| OCL_STUDIES | PROJECT_CODE | PROJECT_CODE |
OCL_PROJECTS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| PROJECT_CODE | Not null | VARCHAR2(15) | A unique code for the project. |
| DESCRIPTION | Not null | VARCHAR2(70) | A freeform text description of the project. |
| START_DATE | Not null | DATE | The date that the project started. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this project. |
| CREATION_TS | Not null | DATE | Date and time when this project was created. |
| PROGRAM_CODE | Not null | VARCHAR2(15) | The code of the program to which the project belongs. |
| END_DATE | Null | DATE | The date the project finished. If null, the project is ongoing. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this project. |
| MODIFICATION_TS | Null | DATE | Date and time when this project was last modified. |
Sites are places at which patients can be seen and treated in a clinical study. Sites do not necessarily correspond to a street address; a hospital could contain a site for each ward, or even an abstract site such as a particular doctor's set of patients in a large ward.
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| OCL_STUDY_SITES | SITE_ID | SITE_ID |
| RECEIVED_DCIS | SITE_ID | SITE_ID |
| RECEIVED_DCMS | SITE_ID | SITE_ID |
| DISCREPANCY_ENTRIES | SITE_ID | SITE_ID |
| REGIONS | STATE | REGION_CODE |
| REGIONS | COUNTRY | REGION_CODE |
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| SITE_ID | Not null | NUMBER(10) | A unique, system-generated ID for the site. |
| ACTIVE_FLAG | Not null | VARCHAR2(1) | A flag to show if the site is still available to conduct studies. |
| ADDRESS_LINE_1 | Not null | VARCHAR2(40) | The first line of the site's address. |
| CITY | Not null | VARCHAR2(25) | The city where the site can be found. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this site. |
| CREATION_TS | Not null | DATE | Date and time when this site was created. |
| NAME | Not null | VARCHAR2(60) | A name for the site. |
| POSTAL_CODE | Not null | VARCHAR2(15) | A postal code or US zip code for the site. |
| STATE | Not null | VARCHAR2(7) | A code for the state or province where the site can be found. |
| SITE | Not null | VARCHAR2(10) | An external unique code for the site. |
| PHONE_NUMBER | Not null | VARCHAR2(25) | A phone number for the site. |
| COUNTRY | Not null | VARCHAR2(7) | A code for the country where the site can be found. |
| OWNING_LOCATION | Not null | VARCHAR2(15) | A code for the location that owns the site. |
| ADDRESS_LINE_2 | Null | VARCHAR2(40) | The second line of the site's address. |
| ADDRESS_LINE_3 | Null | VARCHAR2(40) | The third line of the site's address. |
| MODIFICATION_TS | Null | DATE | Date and time when this site was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this site. |
This table contains a list of studies that are being performed or that are planned to be performed.
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| CLINICAL_STUDIES | STUDY | STUDY |
| OCL_PROGRAMS | PROGRAM_CODE | PROGRAM_CODE |
| OCL_PROJECTS | PROGRAM_CODE | PROGRAM_CODE |
| OCL_PROJECTS | PROJECT_CODE | PROJECT_CODE |
| OCL_ORGANIZATION_UNITS | ORGANIZATION_UNIT_ID | ORGANIZATION_UNIT_ID |
| REGIONS | REGION_ID | REGION_ID |
OCL_STUDIES Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| TASK_ID | Not null | NUMBER(10) | A unique, system-generated ID for the OCL study. TASK_ID is normally the same as the corresponding CLINICAL_STUDY_ID, but not in all cases. The join from OCL_STUDIES to CLINICAL_STUDIES should be done with the STUDY code. |
| STUDY | Not null | VARCHAR2(15) | An external, unique code for the study. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this study. |
| CREATION_TS | Not null | DATE | Date and time when this study was created. |
| ORGANIZATION_UNIT_ID | Not null | NUMBER(10) | The ID of the organization unit responsible for the study. |
| PROJECT_CODE | Not null | VARCHAR2(15) | A code for the project to which the study belongs. |
| PROGRAM_CODE | Not null | VARCHAR2(15) | A code for the program to which the study belongs. |
| TITLE | Null | VARCHAR2(2000) | A long title for the study. This is often the title of the protocol. |
| INVESTIGATORS_PLANNED | Null | NUMBER(10) | The number of investigators expected to work on the study. |
| EXPTL_DESIGN_TYPE_CODE | Null | VARCHAR2(5) | A code for the type of design to be used for the randomization in the study. |
| REGION_ID | Null | NUMBER(10) | The ID of the primary region in which the study will be conducted. It must be a country. |
| CLINICAL_PHASE | Null | VARCHAR2(10) | A code for the clinical phase to which the study belongs. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this study. |
| MODIFICATION_TS | Null | DATE | Date and time when this study was last modified. |
A study site represents the assignment of a site to a clinical study. A site can only be assigned to a clinical study once.
Primary Key SITE_ID, CLINICAL_STUDY_ID
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| OCL_SITES | SITE_ID | SITE_ID |
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
| STUDY_SITE_PATIENT_POSITIONS | SITE_ID | SITE_ID |
| STUDY_SITE_PATIENT_POSITIONS | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
| STUDY_SITE_ROLES | SITE_ID | SITE_ID |
| STUDY_SITE_ROLES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
OCL_STUDY_SITES Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| SITE_ID | Not null | NUMBER(10) | The ID of the site corresponding to the study site. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study corresponding to the study site. |
| DATA_FREEZE_FLAG | Not null | VARCHAR2(1) | A flag to show whether the data at the study site has been frozen. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this study site. |
| CREATION_TS | Not null | DATE | Date and time when this study site was created. |
| OK_TO_SHIP_FLAG | Not null | VARCHAR2(1) | A flag to show if regulatory affairs has approved shipment of clinical supplies to this site. |
| OWNING_LOCATION | Not null | VARCHAR2(15) | A code for the location that owns the study site. |
| STUDY_SITE | Not null | VARCHAR2(10) | A code for the study site. |
| START_DATE | Null | DATE | The date that the site became active. |
| END_DATE | Null | DATE | The date that the site ceased being active. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this study site. |
| MODIFICATION_TS | Null | DATE | Date and time when this study site was last modified. |
A study site role records the assignment of an investigator to a study site.
The test table for OCL_STUDY_SITE_ROLES is OCL_STUDY_SITE_ROLEST.
Primary Key CLINICAL_STUDY_ID, SITE_ID, INVESTIGATOR_ID
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| OCL_SITES | SITE_ID | SITE_ID |
| OCL_INVESTIGATORS | INVESTIGATOR_ID | INVESTIGATOR_ID |
| OCL_STUDY_SITES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
| OCL_STUDY_SITES | SITE_ID | SITE_ID |
OCL_STUDY_SITE_ROLES Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which the study site role belongs. |
| SITE_ID | Not null | NUMBER(10) | The ID of the site assigned to the study site role. |
| INVESTIGATOR_ID | Not null | NUMBER(10) | The ID of the investigator assigned to the study site role. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this study site role. |
| CREATION_TS | Not null | DATE | Date and time when this study site role was created. |
| CURRENT_FLAG | Not null | VARCHAR2(1) | A flag to show if this is the current investigator assigned to the study site. |
| START_DATE | Not null | DATE | The date that the investigator became responsible for the study site. |
| NUMBER_OF_PATIENTS_REQUIRED | Null | NUMBER(10) | The number of patients the investigator is expected to recruit. |
| CONTRACT_DATE | Null | DATE | The date that the investigator's contract was signed. |
| TERMINATION_DATE | Null | DATE | The date that the investigator was terminated from the study. |
| DISCONTINUATION_LETTER_DATE | Null | DATE | The date that the investigator was sent a letter informing him/her that they were being discontinued from the study |
| MODIFICATION_TS | Null | DATE | Date and time when this study site role was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this study site role. |
This table contains details about users of Oracle Clinical and groups of users. Groups of users can be defined to simplify assigning access to studies. Most of the attributes apply only to individual user entries.
Primary Key ORACLE_ACCOUNT_NAME
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| DCI_BOOKS | DCI_BOOK_DFLT_IN_DE_IND | DCI_BOOK_ID |
ORACLE_ACCOUNTS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| ORACLE_ACCOUNT_NAME | Not null | VARCHAR2(30) | The name of the Oracle account. |
| CREATION_TS | Not null | DATE | Date and time when this Oracle account was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this Oracle account. |
| MODIFICATION_TS | Null | DATE | Date and time when this Oracle account was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this Oracle account. |
| ALL_STUDY_ACCESS_FLAG | Not null | VARCHAR2(1) | A flag to show if the user should be allowed access to all studies. |
| FIRST_NAME | Not null | VARCHAR2(30) | The first name of the person. |
| LAST_NAME | Not null | VARCHAR2(40) | The last name of the person. |
| OA_SUB_TYPE_CODE | Not null | VARCHAR2(6) | A code for the type of account. Possible values come from the reference codelist OA_SUB_TYPE_CODE. Values are ORACLE (for an individual Oracle Clinical user) and GROUP (a name for a group of users). |
| USER_LOG_DIR | Null | VARCHAR2(60) | The directory where the user's log files should be written to. |
| UNIVARIATE_ALERT_IND | Null | VARCHAR2(1) | A flag to show if the user should be given warnings when univariate discrepancies are being created. |
| SECOND_PASS_ALERT_IND | Null | VARCHAR2(1) | A flag to show if the user should be given warnings when univariate discrepancies are being created during second pass data entry. |
| THESAURUS_LOV_IND | Null | VARCHAR2(1) | A flag to show if a thesaurus LOV is available to the user during data entry. |
| MANUAL_DISC_IN_BROWSE_IND | Null | VARCHAR2(1) | A flag to show if the user is allowed to raise manual discrepancies while browsing data. |
| DISC_RESOLVE_IN_DE_IND | Null | VARCHAR2(1) | A flag to show if the user is allowed to resolve discrepancies during data entry. |
| DCI_BOOK_DFLT_IN_DE_IND | Null | VARCHAR2(1) | The ID of the default DCI book for the user. |
| PRIVILEGED_UPDATE_IND | Null | VARCHAR2(1) | A flag to show if the user is allowed to perform Privileged Update. |
| UNENROLLED_ALERT_IND | Null | VARCHAR2(1) | A flag to show if the system alerts users if they try to enter data for a patient who has not enrolled. |
| PASS2_NOT_BY_PASS1_IND | Null | VARCHAR2(1) | A flag to prevent the person who did first pass data entry from doing second pass entry for the same CRF. |
| OCL_THES_DISC_AT_DE_IND | Null | VARCHAR2(1) | A flag to show if the user should be alerted of a Thesaurus discrepancy at data entry time. |
| OCL_THES_LOV_IND | Null | VARCHAR2(1) | A flag to show if the user has access to a LOV of Thesaurus values at data entry time. |
| ACCESS_ONLY_IN_BROWSE_IND | Null | VARCHAR2(1) | A flag to restrict access in Browse mode to accessible received DCIs only. |
| DEFAULT_DE_PAGE_HEIGHT | Null | NUMBER(3) | The page height for the user. |
| DEFAULT_DE_PAGE_WIDTH | Null | NUMBER(3) | The page width for the user. |
| AUTO_SKIP_PREF_FLAG | Null | VARCHAR2(1) | A flag to show if control should automatically jump to the next field after the current field is filled. |
| AUTO_FILL_PREF_FLAG | Null | VARCHAR2(1) | A flag to show if, after enough characters have been entered to achieve a unique value, the rest of the field should be filled in automatically. |
| UNIV_BEEP_PREF_FLAG | Null | VARCHAR2(1) | A flag to show if the user should be given a beep each time a univariate discrepancy is raised. |
| COMP_BEEP_PREF_FLAG | Null | VARCHAR2(1) | A flag to show if the system beeps each time a comparison failure occurs in second pass entry. |
| END_FORM_BEEP_PREF_FLAG | Null | VARCHAR2(1) | A flag to show if the user should be given a beep at the end of each data entry page. |
| DATE_INPUT_FORMAT | Null | VARCHAR2(15) | The format the user wishes to use when entering dates during data entry. |
| DATE_DISPLAY_FORMAT | Null | VARCHAR2(15) | The format the user wishes to see when viewing dates. |
| RDCI_ORDER_PREF | Null | VARCHAR2(15) | The sort order the user wishes to use when querying received DCIs in data entry. |
| DCDCOM_FLAG | Null | VARCHAR2(1) | For internal system use only. |
| DCDCOM_COMMAND | Null | VARCHAR2(80) | For internal system use only. |
| DCDCOM_DCM_ID | Null | NUMBER(10) | For internal system use only. |
| DCDCOM_SUBSET_SN | Null | NUMBER(10) | For internal system use only. |
| DCDCOM_LAYOUT_SN | Null | NUMBER(10) | For internal system use only. |
| DCDCOM_SESSION_ID | Null | NUMBER(10) | For internal system use only. |
| DCI_DATE_REQUIRED_IN_DE_IND | Null | VARCHAR2(1) | A flag to show if a date must be given for a DCI during Log-In. |
| RS_RXC_LOG | Null | VARCHAR2(35) | A flag to show if a date must be given for a DCI during Log-In. |
| DEFAULT_PRINTER_QUEUE | Null | VARCHAR2(50) | Default printer for PSUB jobs for this user. |
| DEFAULT_PSUB_QUEUE | Null | VARCHAR2(50) | Default PSUB queue for this user. |
| DEFAULT_REPORT_RS | Null | VARCHAR2(50) | Default Reports Server when doing report jobs and generating a DCI Form. |
| DEFAULT_JOB_SET_RS | Null | VARCHAR2(50) | Default Reports Server when running a job set. |
| DEFAULT_PSUB_SCHEDULE_RS | Null | VARCHAR2(50) | Default Reports Server when scheduling a PSUB job. |
| DEFAULT_RS_PRINTER | Null | VARCHAR2(50) | Default printer for the Reports Server. |
| OC_CUSTOM_DOC_DIR | Null | VARCHAR2(200) | This user's custom documentation directory. If a location is specified in this column, this user will view custom help files from the location specified. If this column is null, this user will view custom help files from the location specified by the Oracle Clinical Web Server. |
| DEFAULT_PROFILE_ID | Null | NUMBER(10) | ID of the profile assigned to this user. |
| LAST_CLINICAL_STUDY_ID | Null | NUMBER(10) | ID of the last clinical study accessed by this user through RDC. |
Patient positions record details about people taking part in a clinical study.
Primary Key PATIENT_POSITION_ID
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
| DCI_BOOKS | DCI_BOOK_ID | DCI_BOOK_ID |
| TREAT_ASSGN_ALL_VIEW | PATIENT_POSITION_ID | PATIENT_POSITION_ID |
| ACTUAL_EVENTS | PATIENT_POSITION_ID | PATIENT_POSITION_ID |
| RECEIVED_DCIS | PATIENT_POSITION_ID | PATIENT_POSITION_ID |
| RECEIVED_DCMS | PATIENT_POSITION_ID | PATIENT_POSITION_ID |
| DISCREPANCY_ENTRIES | PATIENT_POSITION_ID | PATIENT_POSITION_ID |
| STUDY_SITE_PATIENT_POSITIONS | PATIENT_POSITION_ID | PATIENT_POSITION_ID |
| TREAT_ASSGN_ALL_VIEW | PATIENT_POSITION_ID | PATIENT_POSITION_ID |
| PATIENT_STATUSES | PATIENT_POSITION_ID | PATIENT_POSITION_ID |
| CLINICAL_SUBJECTS | CLINICAL_SUBJECT_ID | CLINICAL_SUBJECT_ID |
| MV_EXECUTION_LOG | PATIENT_POSITION_ID | PATIENT_POSITION_ID |
PATIENT_POSITIONS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which the patient position belongs. |
| CLINICAL_STUDY_VERSION_ID | Not null | NUMBER(10) | The version ID of the clinical study to which the patient position belongs. |
| PATIENT_POSITION_ID | Not null | NUMBER(10) | A unique, system-generated ID for the patient position. |
| PATIENT | Not null | VARCHAR2(10) | A unique code for the patient position within the clinical study. |
| DATA_MODIFIED_FLAG | Not null | VARCHAR2(1) | A flag to show if the patient has received data that has been changed since the last batch validation run. Usually modified only during the course of batch validation, it does not directly reflect the modification status of a patient between batch validations. |
| DATA_REVISION_SN | Not null | NUMBER(10) | The latest batch validation run sequence number in which the patient had modified data and was included in incremental validation. This number corresponds to the DATA_REVISION_SN in the BATCH_DM_RUNS table. |
| LAST_REPLIC_BATCH_TS | Null | DATE | The date and time of the most recent batch validation run for which changes to data were detected that would result in the patient's data being included in data replication. |
| LAST_BATCH_TS | Null | DATE | The date and time that batch validation last validated this patient due to data changes. |
| LAST_DATA_DELETION_TS | Null | DATE | The date and time that data was last batch deleted for this patient. This timestamp is only updated in the course of a batch validation session and does not reflect deletions until the batch validation session following the deletion. |
| LAST_LAB_MODIFICATION_TS | Null | DATE | The most recent date and time that a lab association to the patient's data was modified. |
| LAST_LAB_BATCH_TS | Null | DATE | The date and time of the most recent batch validation in which the lab-related Procedures were run, either alone — due to changes to lab association or lab range changes — or due to data changes. |
| DCI_BOOK_ID | Null | NUMBER | The ID of the DCI book to be used for this patient. |
| HAS_DATA_FLAG | Not null | VARCHAR2(2) | A flag to show if any received data has ever been received for this patient. Can not be used to determine whether patient currently has data, since soft data deletions (such as by patient transfer or the remove function in data entry) do not clear the flag. |
| OWNING_LOCATION | Not null | VARCHAR2(15) | The code of the database location that owns this patient position. |
| FREEZE_FLAG | Not null | VARCHAR2(1) | A flag to show if the system prevents any changes to the data for this patient. |
| INC_IN_EFF_ANALYSIS_FLAG | Not null | VARCHAR2(1) | A flag to indicate if this patient's data is included in an efficacy analysis. |
| INC_IN_SAFETY_ANALYSIS_FLAG | Not null | VARCHAR2(1) | A flag to indicate if this patient's data is included in a safety analysis. |
| REPLACEMENT_POSITION_FLAG | Not null | VARCHAR2(1) | A flag to indicate if this patient position is assigned to this study as a replacement patient position. |
| SCREENING_POSITION_FLAG | Not null | VARCHAR2(1) | A flag to indicate if this patient position is assigned to this study for screening purposes. |
| CREATION_TS | Not null | DATE | Date and time when the patient position was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created the patient position. |
| CREATED_LATE_FLAG | Not null | VARCHAR2(1) | Not used. |
| PATIENT_DROPPED_FLAG | Not null | VARCHAR2(1) | Not used. |
| EARLY_TERMINATION_FLAG | Not null | VARCHAR2(1) | A flag to show if the patient was terminated early from the study. |
| PATIENT_ENROLLMENT_DATE | Null | DATE | Date and time when this patient was enrolled in this study. |
| CLINICAL_SUBJECT_ID | Null | NUMBER(10) | The ID of the clinical subject that links this patient position to other patient positions. |
| EXCLUDE_FROM_EFFICACY_REASON | Null | VARCHAR2(200) | Freeform, mixed-case description of the reason for excluding the patient's data from an efficacy analysis. |
| INCLUSION_EXCLUSION_DATE | Null | DATE | The date when the decision was made to include or exclude a patient's data in efficacy or safety analyses. |
| EXCLUDE_FROM_SAFETY_REASON | Null | VARCHAR2(200) | Freeform, mixed-case description of the reason for excluding the patient's data from a safety analysis. Normally the only valid reason is that no study medication was taken by the patient. |
| REPORTED_FIRST_NAME | Null | VARCHAR2(15) | The patient's first name, in mixed case. |
| REPORTED_LAST_NAME | Null | VARCHAR2(20) | The patient's last name, in mixed case. |
| REPORTED_SEX | Null | VARCHAR2(1) | The patient's gender. Values are M for male and F for female. The system may use the value in this field to determine the appropriate normal range for a patient's laboratory responses. |
| REPORTED_PATIENT_REFERENCE | Null | VARCHAR2(25) | The patient's reported code identification from a previous study. |
| REPORTED_INITIALS | Null | VARCHAR2(4) | The patient's initials, in mixed case. |
| REPORTED_BIRTH_DATE | Null | DATE | The patient's date of birth. The system may use the value in this field to determine the appropriate normal range for a patient's laboratory responses. |
| REPORTED_DEATH_DATE | Null | DATE | The date that the patient was reported to have died. |
| RANDOMIZATION_COMMENT | Null | VARCHAR2(200) | A freeform comment about the particular randomization for this patient. |
| REPORTED_DATE_LAST_PREGNANCY | Null | DATE | The date the patient was last pregnant. |
| FIRST_SCREENING_DATE | Null | DATE | The date the patient passed screening. |
| INFORMED_CONSENT_DATE | Null | DATE | The date the patient signed the informed consent form. |
| TERMINATION_DATE | Null | DATE | The date the patient was terminated from this clinical study. |
| MODIFICATION_TS | Null | DATE | Date and time when this patient position was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this patient position. |
| DISC_ENTRY_DEL_LAST_BATCH_TS | Null | DATE | The timestamp of the last batch validation on the source location at which multivariate discrepancies for a procedure version were deleted. This is used in patient data replication. |
This table contains a record of user-defined situations that have occurred for patients in the study. This table is maintained during batch validation through the use of the RXC_PAT_STAT package.
Primary Key PATIENT_POSITION_ID, PATIENT_STATUS_CODE
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| PATIENT_POSITIONS | PATIENT_POSITION_ID | PATIENT_POSITION_ID |
PATIENT_STATUSES Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which the patient position belongs. |
| SITE_ID | Not null | NUMBER(10) | The ID of the site to which the patient is assigned. |
| PATIENT_POSITION_ID | Not null | NUMBER(10) | The ID of the patient position concerned. |
| PATIENT_STATUS_CODE | Not null | VARCHAR2(15) | A user-defined code for the state that is being recorded. |
| STATUS_DATE | Not null | DATE | The date that the state was achieved. |
| STATUS_COMMENT | Null | VARCHAR2(200) | Optional comment to provide more information about this patient status record. |
Planned study intervals represent the planned experience of one patient in the study. They form a tree structure. At the top is a system-generated interval called study time with a subtype code of 0. This represents the entire length of time a patient can be in the study.
The study time can be divided into phases. Phases have a subtype code of 1 and represent major segments of the study, such as screening, dosing, and follow-up. Each phase has pointers to the study time and to the next and previous phase.
A phase can, in turn, be divided into periods. Periods have subtype code of 2 and represent segments of a period — for example, the dosing phase could be divided into periods for dose A, dose B, and dose C. Each period has pointers to its parent phase and to the next and previous period within the phase.
A period can be divided into sub-periods. Sub-periods have subtype code of 3 and represent small changes within the period — for example, the dosing periods for dose A could be a one-day washout sub-period followed by six days of treatment. Each sub-period has pointers to its parent period and the next and previous sub-period within the period.
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
| CLINICAL_PLANNED_EVENTS | PLAN_STUDY_INT_ID | PLAN_STUDY_INT_ID |
PLANNED_STUDY_INTERVALS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which the patient position belongs. |
| CLINICAL_STUDY_VERSION_ID | Not null | NUMBER(10) | The ID of the clinical study version to which the patient position belongs. |
| PLAN_STUDY_INT_ID | Not null | NUMBER(10) | A unique, system-generated ID for the planned study interval. |
| NAME | Not null | VARCHAR2(60) | The name of the planned study interval. |
| PLAN_STUDY_INT_TYPE_CODE | Not null | VARCHAR2(7) | A code for the general business type of the planned study interval. |
| PLSTIN_SUB_TYPE_CODE | Not null | VARCHAR2(4) | The type of planned study interval. Valid values are PHASE, PERIOD, and SUB-PERIOD. |
| OPTIONAL_FLAG | Not null | VARCHAR2(1) | A flag to show if patients must complete the planned study interval. |
| TIME_UNIT_TYPE_CODE | Not null | VARCHAR2(7) | A code for the unit of time used to measure the duration of the planned study interval. |
| RANDOMIZATION_ENDED_FLAG | Not null | VARCHAR2(1) | A flag to show if defining and creating the randomization in the interval has been completed. This flag only applies to intervals of type PHASE. |
| SHORT_NAME | Null | VARCHAR2(20) | A short code for the planned study interval. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created the planned study interval. |
| CREATION_TS | Not null | DATE | Date and time when the planned study interval was created. |
| BLIND_TYPE_CODE | Not null | VARCHAR2(7) | A code for the type of blinding applied to the randomization in the planned study interval. Valid values come from the installation codelist called BLIND_TYPE_CODE. |
| END_DATE | Null | DATE | The date by which the last patient must complete this planned study interval. This column would only be relevant in a long-term study where the study was finally being terminated. |
| RAND_ACC_STAT_TYPE_CODE | Null | VARCHAR2(7) | A code for the current access allowed to the randomization in the planned study interval. Possible values come from the reference codelist RAND_ACC_STAT_TYPE_CODE. Values are OPEN (everyone has always had access), CLOSED (only privileged users have access), ACCESS (access to named users while in this state), RELEASE (everyone has access at the end of the study), and MULT (access controlled at the Phase level). |
| PARENT_INTERVAL_ID | Null | NUMBER(10) | The ID of the planned study interval to which this one belongs. |
| NEXT_INTERVAL_ID | Null | NUMBER(10) | The ID of the next planned study interval of the current interval. |
| PREVIOUS_INTERVAL_ID | Null | NUMBER(10) | The ID of the previous planned study interval of the current interval. |
| MINIMUM_DURATION . | Null | NUMBER(14,4) | The minimum length of time that the interval can last for a patient |
| MAXIMUM_DURATION | Null | NUMBER(14,4) | The maximum length of time that the interval can last for a patient. |
| PLSTIN_SEQ_NUM | Null | NUMBER(10) | A system-maintained sequence number for the interval within the set of all intervals in the study. |
| MIN_OFFSET_IN_STUDY | Null | NUMBER(10) | The minimum length of time from the start of the study that this interval can start. |
| MAX_OFFSET_IN_STUDY | Null | NUMBER(10) | The maximum length of time from the start of the study by which time this interval must start. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this planned study interval. |
| MODIFICATION_TS | Null | DATE | Date and time when this planned study interval was last modified. |
This table records the preferred lab unit to use for a question.
Primary Key PREF_LAB_UNIT_GROUP_ID, QUESTION_ID
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| LAB_UNITS | LAB_UNIT_CODE | LAB_UNIT_CODE |
| QUESTIONS | QUESTION_ID | QUESTION_ID |
PREFERRED_LAB_UNITS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| PREF_LAB_UNIT_GROUP_ID | Not null | NUMBER(10) | Unique, system-generated ID for this preferred conversion group. |
| QUESTION_ID | Not null | NUMBER(10) | The ID of the question for which a preferred lab unit has been defined. |
| CREATION_TS | Not null | DATE | Date and time when this preferred lab unit record was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this preferred lab unit record. |
| LAB_UNIT_CODE | Not null | VARCHAR2(40) | A code for the lab unit assigned to this question as its preferred lab unit. |
| MODIFICATION_TS | Null | DATE | Date and time when this preferred lab unit record was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this preferred lab unit record. |
| PREFERRED_LAB_UNIT_COMMENT | Null | VARCHAR2(240) | A comment with supplementary information about using this preferred lab unit for this group. |
| DECIMAL_PLACES | Not null | NUMBER(1) | The number of decimal places that you want reports displaying this value to use. |
There are two types of Procedure: Validation Procedures and Derivation Procedures. Validation Procedures are intended to validate received data, and return either a true or false value for each specified detail. If TRUE is returned a multivariate discrepancy is produced. A Derivation Procedure is used to derive a new value from received data. This value is stored as a derived response.
Both types of Procedure are defined and stored in the same way.
Primary Key PROCEDURE_ID, PROCEDURE_VER_SN
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
| PROCEDURE_QUESTION_GROUPS | PROCEDURE_ID | PROCEDURE_ID |
| PROCEDURE_QUESTION_GROUPS | PROCEDURE_VER_SN | PROC_QUE_GRP_PROC_VER_SN |
| DISCREPANCY_ENTRIES | PROCEDURE_ID | PROCEDURE_ID |
| DISCREPANCY_ENTRIES | PROCEDURE_VER_SN | PROCEDURE_VER_SN |
PROCEDURES Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| PROCEDURE_ID | Not null | NUMBER(10) | A unique, system-generated ID for the Procedure. |
| PROCEDURE_VER_SN | Not null | NUMBER(3) | A display-only number automatically assigned to each new version of a Procedure after its creation. |
| CREATION_TS | Not null | DATE | Date and time when this procedure was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this procedure. |
| NAME | Not null | VARCHAR2(30) | Name of the Procedure; unique within a domain. The name, whether of a Validation or Derivation Procedure, is listed in the discrepancy database as PROC NAME. |
| DOMAIN | Not null | VARCHAR2(15) | A Global Library object-naming convention that facilitates the unique naming or grouping of similar objects. Domains also provide a mechanism for limiting access to objects. |
| PRO_SUB_TYPE_CODE | Not null | VARCHAR2(5) | A code to show if the Procedure is for validation or derivation. Possible values come from the system reference codelist PRO_SUB_TYPE_CODE. Values are DERIVATION and VALIDATION. |
| DESCRIPTION | Not null | VARCHAR2(70) | Freeform text, in mixed case, describing the Procedure. |
| EDITED_FLAG | Not null | VARCHAR2(1) | A flag to show whether the generated Procedure text has been edited. |
| COMPILED_FLAG | Not null | VARCHAR2(1) | A flag to show whether the Procedure has been successfully compiled. |
| PROCEDURE_STATUS_CODE | Not null | VARCHAR2(15) | A code to show the state of the Procedure. Values are provisional (P), active (A), or retired (R). |
| PROCEDURE_TYPE_CODE | Not null | VARCHAR2(15) | A code to classify the Procedure according to the type of data it handles. Values come from the installation reference codelist PROCEDURE_TYPE_CODE. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which the Procedure belongs. |
| NEEDS_GENERATION_FLAG | Not null | VARCHAR2(1) | A flag to show whether the Procedure needs to be generated. |
| CANNOT_PRESERVE_DISC_FLAG | Not null | VARCHAR2(1) | A system-set flag that indicates that changes to the Procedure preclude preserving existing discrepancies. |
| PRESERVE_DISC_FLAG | Not null | VARCHAR2(1) | A user-set flag indicating whether the user wants to preserve existing discrepancies after upgrade. |
| ALL_PATS_VALIDATED_FLAG | Not null | VARCHAR2(1) | Not currently used. |
| LAST_GENERATION_TS | Null | DATE | Date and time this Procedure code was last generated. If it is blank, the Procedure code has not been successfully generated. |
| LAST_STATUS_CHANGE_TS | Null | DATE | The date and time that the Procedure status was last changed. |
| EDIT_COMMENT | Null | VARCHAR2(200) | Freeform text, in mixed case, containing supplementary information about how and why the generated Procedure code was edited. |
| STATUS_COMMENT | Null | VARCHAR2(200) | Freeform text, in mixed case, containing supplementary information about how and why the Procedure's status did or did not change. |
| RETIREMENT_REASON_TYPE_CODE | Null | VARCHAR2(15) | A code to summarize why the Procedure has been retired. Values come from the installation reference codelist RETIREMENT_REASON_TYPE_CODE. |
| MODIFICATION_TS | Null | DATE | Date and time when this procedure was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this procedure. |
| REPLICATION_IND | Null | VARCHAR2(1) | A flag to show if the Procedure has been replicated from another location. |
| SORT_ORDER | Null | NUMBER(10) | A positive integer that controls the order in which the Derivation Procedures are executed. |
| VER_31_FLAG | Not null | VARCHAR2(1) | A flag to indicate if this is a V3.1-style Procedure or a pre-3.1-style Procedure. V3.1-style Procedures are more speedily executed and include features not available in the older style, including more custom code locations and differentiated settings for every variable in every detail. |
| LAB_DEPENDENT_FLAG | Not null | VARCHAR2(1) | A user-defined flag to indicate if the system sees this Procedure as affected by changes in lab ranges. If this flag is set, Oracle Clinical will include this Procedure for patients that are re-validated due to changes to lab ranges or lab or data associations since the last batch validation. |
| EXECUTION_CONTEXT | Not null | VARCHAR2(15) | A code that indicates, for the Data Capture API immediate procedure execution feature, whether the procedure should be run immediately for a particular DCM(ON-LINE/DCM), immediately for a patient-as-a-whole (ON-LINE), or only during batch validation (OFF-LINE). Note that all procedures execute again at the higher levels as well. For instance, all ON-LINE procedures also execute during batch validation. |
Procedure Details hold the individual steps that constitute a Procedure. There are two types of Procedure, Derivation Procedures and Validation Procedures.
The steps in a Validation Procedure produce a result of TRUE or FALSE. A value of TRUE will result in the creation of a multivariate discrepancy. Some of the steps in a Derivation Procedure produce a result to be stored in a derived response. A Derivation Procedure can include steps that perform tests and create multivariate discrepancies.
Primary Key PROCEDURE_DETAIL_ID, PROCEDURE_DETAIL_PROC_VER_SN
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| PROCEDURES | PROCEDURE_ID | PROCEDURE_ID |
| PROCEDURES | PROCEDURE_DETAIL_PROC_VER_SN | PROCEDURE_VER_SN |
| PROCEDURE_QUESTIONS | PROCEDURE_QUESTION_ID | PROC_QUES_ID |
PROCEDURE_DETAILS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| PROCEDURE_DETAIL_ID | Not null | NUMBER(10) | An internally derived, unique ID for the Procedure Detail. Part of concatenated primary key. Value stays the same for all Procedure versions. |
| PROCEDURE_DETAIL_PROC_VER_SN | Not null | NUMBER(3) | This is the PROCEDURE_VER_SN of the parent Procedure. Part of concatenated primary key. |
| CREATION_TS | Not null | DATE | Date and time when this procedure detail was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this procedure detail. |
| PROCEDURE_ID | Not null | NUMBER(10) | ID of this procedure to which this procedure detail applies. |
| PD_SUB_TYPE_CODE | Not null | VARCHAR2(15) | A code for the type of Procedure Detail. Valid values come from the reference codelist PD_SUB_TYPE_CODE. Valid values are CALCULATION (where the Procedure Detail derives a value) and TEST (where the Procedure Detail returns TRUE or FALSE). |
| TEST_ORDER_SN | Not null | NUMBER(3) | A sequence number to control the order in which the Procedure Details are be executed. |
| EXPRESSION | Not null | VARCHAR2(2000) | The expression contains a syntactical expression to produce either TRUE or FALSE for TEST details or a derived value for CALCULATION details. The syntax of the expression is not guaranteed to be stable across Oracle Clinical releases. |
| TEST_NOT_NULL_ONLY_FLAG | Null | VARCHAR2(1) | A flag to indicate if the Procedure Detail should be executed even if the input value it is manipulating is NULL. Only applicable to pre-Version 3.1-style Procedures. |
| VALIDATION_FAILURE_TYPE_CODE | Null | VARCHAR2(15) | A code for the type of multivariate discrepancy to be produced if the test succeeds. Valid values come from the user-defined reference codelist VALIDATION_FAILURE_TYPE_CODE. |
| VT_CONTINUE_ON_DISCREP_FLAG | Not null | VARCHAR2(1) | A flag to indicate if a Validation Procedure should continue to execute after raising a discrepancy in this Procedure Detail. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study that the Procedure Detail belongs to. |
| VT_FAILURE_MESSAGE | Null | VARCHAR2(200) | A freeform text message to be assigned to the discrepancy if one is created. The syntax of embedding information in the messages is not guaranteed to be stable across Oracle Clinical releases. |
| PROCEDURE_QUESTION_ID | Null | NUMBER(10) | The ID of the Procedure question derived by this Procedure Detail. The only application is to CALCULATION details. |
| DESCRIPTION | Null | VARCHAR2(70) | A freeform text description of the Procedure Detail. |
| MODIFICATION_TS | Null | DATE(70) | Date and time when this procedure detail was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this procedure detail. |
| REPLICATION_IND | Null | VARCHAR2(1) | A flag to indicate if the Procedure Detail has been replicated from another location. |
| EXTERNALLY_DERIVED_FLAG | Not null | VARCHAR2(1) | Not currently used. |
| INIT_DISCR_REVIEW_STATUS_CODE | Null | VARCHAR2(15) | The code to be associated with the discrepancy in the discrepancy database according to your company's policy. |
| CONTINUE_AT_DETAIL_ID | Null | NUMBER(10) | If INIT_DISCR_REVIEW_STATUS_CODE is Y, you can specify in this column the detail at which detail you want the Procedure to continue. For example, if a discrepancy exists on the first Question in a Question Group, you might want to skip any details that process related Questions and start again at the first detail for a new Question Group. |
| DISC_MSG_VAR_COUNT | Not null | NUMBER(10) | For internal use only. |
Note:
The meaning of the expression and the failure message is covered by the Stable Interface; however, the stability of their syntax is not guaranteed.Procedure question groups record the assignment of DCM question groups to a Procedure.
Primary Key PROC_QUE_GRP_ID, PROC_QUE_GRP_PROC_VER_SN
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| PROCEDURES | PROCEDURE_ID | PROCEDURE_ID |
| PROCEDURES | PROCEDURE_DETAIL_PROC_VER_SN | PROCEDURE_VER_SN |
| DCM_QUESTION_GROUPS | DCM_QUE_GRP_ID | DCM_QUESTION_GRP_ID |
| DCM_QUESTION_GROUPS | DCM_QUE_GRP_DCM_SUBSET_SN | DCM_QUE_GRP_DCM_SUBSET_SN |
| DCM_QUESTION_GROUPS | DCM_QUE_GRP_DCM_LAYOUT_SN | DCM_QUE_GRP_DCM_LAYOUT_SN |
| CLINICAL_PLANNED_EVENTS | CPE_BEGIN_ACCESS_NAME | NAME |
| CLINICAL_PLANNED_EVENTS | CPE_END_ACCESS_NAME | NAME |
PROCEDURE_QUESTION_GROUPS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| PROC_QUE_GRP_ID | Not null | NUMBER(10) | A unique, system-generated ID for the Procedure question group. |
| PROC_QUE_GRP_PROC_VER_SN | Not null | NUMBER(3) | The version of the parent Procedure. |
| CREATION_TS | Not null | DATE | Date and time when this procedure question group was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this procedure question group. |
| ALIAS | Not null | VARCHAR2(4) | A short name for the Procedure question group that uniquely identifies the question group and its questions in Procedure Details and in correlated question groups. |
| DCM_QUE_GRP_ID | Not null | NUMBER(10) | The ID of the DCM question group that corresponds to this Procedure question group. |
| DCM_QUE_GRP_DCM_SUBSET_SN | Not null | NUMBER(3) | The subset sequence number of the DCM question group that corresponds to this Procedure question group. |
| DCM_QUE_GRP_DCM_LAYOUT_SN | Not null | NUMBER(3) | The layout sequence number of the DCM question group that corresponds to this Procedure question group. |
| PROCEDURE_ID | Not null | NUMBER(10) | The ID of the Procedure to which the Procedure question group belongs. |
| AGGREGATE_FLAG | Not null | VARCHAR2(1) | A flag to indicate if an aggregate function is applied to the responses of all questions in the DCM question group. |
| ORDER_SN | Not null | NUMBER(3) | Internal use only. Used by the front-end to display the Procedures question groups in order. |
| CREATE_PLACEHOLDER_FLAG | Not null | VARCHAR2(1) | A flag to show if the Procedure creates a null record when a clinical planned event is missing from the DCM question group. Not used in Derivation Procedures or in aggregate types of question groups. |
| PRIMARY_REFERENCE_FLAG | Not null | VARCHAR2(1) | A flag to indicate that within this Procedure this DCM question group is the primary reference; any discrepancies found are associated with this DCM in the discrepancy database, a derived responses can be created only in this DCM question group. |
| CORREL_ACTUAL_EVENT_FLAG | Null | VARCHAR2(1) | Used in pre-3.1 style Procedures only. If this DCM question group is to be correlated with another DCM question group (the Correlate W/Alias field has a value), a Y in this field indicates the Procedure will correlate responses to questions in the two DCM question groups based on the value of a particular actual event. That is, the Procedure will fetch responses from only that actual event. |
| CORREL_QUALIFYING_QUES_FLAG | Not null | VARCHAR2(1) | A flag to show if this DCM question group is to be correlated with another DCM question group (the Correlate W/Alias field has a value), a Y in this field indicates the Procedure will correlate responses to questions in the two DCM question groups based on the value of their DCM qualifying questions. That is, the Procedure fetches responses only if the value of their qualifying question is the same. |
| EVENT_ORDER | Not null | VARCHAR2(50) | The event sort order criteria used by the Procedure question group. Possible values come from the reference codelist PROC_QG_EVENT_ORDER. Values are:
|
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which this Procedure belongs. |
| CPE_BEGIN_ACCESS_NAME | Null | VARCHAR2(20) | Name of the first clinical planned event the Procedure processes (aka Event Range/First by the front-end application). If this field is blank, the Procedure processes the first clinical planned event for the specified patient. If both Event 1 and Event 2 fields are left blank, the Procedure processes the data collected at all patient visits. Not used in Derivation Procedures. |
| CPE_END_ACCESS_NAME | Null | VARCHAR2(20) | Name of the last clinical planned event the Procedure processes (aka Event Range/Last by the front-end application). If this field blank, the Procedure continues processing down to the last clinical planned event for the specified patient. If both Event 1 and Event 2 fields are left blank, the Procedure processes the data collected at all patient visits. Not used in Derivation Procedures. |
| OTHER_PQG_CORREL_PQG_ID | Null | NUMBER(10) | This field is a self-join to another Procedure question group in the same Procedure. If defined, it means that values of this Procedure question group are constrained to match values of the correlated Procedure question group (see CORREL_ACTUAL_EVENT_FLAG, CORREL_QUALIFYING_QUES_FLAG, and CORREL_WITH_EVENT_TYPE). In the front-end application, the reference to the other Procedure question group is defined via its alias. |
| FIRST_LAST_EVENT | Null | VARCHAR2(5) | A value in this field indicates that the Procedure limits processing to either the first or last actual event per patient when processing the responses to the questions in this question group. Not used in Derivation Procedures. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this procedure question group. |
| MODIFICATION_TS | Null | DATE | Date and time when this procedure question group was last modified. |
| REPLICATION_IND | Null | VARCHAR2(1) | A flag to show if the Procedure question group has been replicated from another location. |
| QUALIFYING_EXPRESSION | Null | VARCHAR2(2000) | A PL/SQL statement of up to 2000 characters of mixed-case text that limits the retrieval of records for processing by the Procedure. Not used in Derivation Procedures. |
| CORREL_WITH_EVENT_TYPE | Null | VARCHAR2(8) | If this DCM question group is to be correlated with another DCM question group (the correlate w/alias field in the front-end application, OTHER_PQG_CORREL_PQG_ID in the table, has a value), a PREVIOUS/ACTUAL/NEXT value in this field indicates the Procedure will correlate responses to questions in the two DCM question groups based on the value of the previous, actual, or next event. |
| EVENT_ORDER_EXTENSION | Not null | VARCHAR2(2000) | Freeform text that specifies the innermost Sort criteria. The default is REPEAT_SN ASC, or, where the corresponding DCM has a qualifying question, QUALIFYING_VALUE ASC, REPEAT_SN ASC. |
| SINGLE_REPEATS_ONLY_FLAG | Not null | VARCHAR2(1) | A flag to show if only the first fetched repeating instance of a repeating question group will be processed. |
| WHERE_EXTENSION | Null | VARCHAR2(200) | A PL/SQL statement with mixed-case text that limits the retrieval of records for processing by the Procedure. Not used in Derivation Procedures. |
Questions are the foundation of data definition. They represent single pieces of information that can be captured and recorded.
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| QUESTION_CATEGORY_RELATIONS | QUESTION_ID | QUESTION_ID |
| PREFERRED_LAB_UNITS | QUESTION_ID | QUESTION_ID |
| RANGES | QUESTION_ID | QUESTION_ID |
| LAB_UNIT_CONVERSIONS | QUESTION_ID | QUESTION_ID |
| DISCRETE_VALUE_GROUPS | DISCRETE_VAL_GRP_ID | DISCRETE_VALUE_GRP_ID |
| DISCRETE_VALUE_GROUPS | DISCRETE_VAL_GRP_SUBSET_NUM | DISCRETE_VAL_GRP_SUBSET_NUM |
| QUESTION_GROUP_QUESTIONS | QUESTION_ID | QUESTION_ID |
| DCM_QUESTIONS | QUESTION_ID | QUESTION_ID |
| DCMS | QUESTION_ID | QUAL_QUESTION_ID |
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| QUESTION_ID | Not null | NUMBER(10) | A unique, system-generated ID for the question. |
| CREATION_TS | Not null | DATE | Date and time when the question was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created the question. |
| NAME | Not null | VARCHAR2(20) | The unique identification, within a domain, of a question. |
| DOMAIN | Not null | VARCHAR2(15) | Global Library object-naming that facilitates the unique naming or grouping of objects. |
| QUE_SUB_TYPE_CODE | Not null | VARCHAR2(15) | A code to specify how the system applies the question and constrains the question's data type. Possible values come from the reference codelist QUE_SUB_TYPE_CODE. Values are UNIT, LAB TEST, DATE TIME, NON-LAB, COMPLEX, CHAR (for character), THES VALIDATED (Thesaurus validated), and QUESTION SET. |
| SAS_NAME | Not null | VARCHAR2(8) | A name supplied to the SAS Name field in the SAS views and SAS datasets, to describe the question. This will be unique within the domain of the question. |
| SAS_LABEL | Not null | VARCHAR2(40) | Freeform text field, in mixed case, supplied to the SAS Label field in the SAS views and SAS datasets, to describe the question. |
| LAST_STATUS_CHANGE_TS | Not null | DATE | The date and time that the question's status last changed. |
| QUESTION_DATA_TYPE_CODE | Not null | VARCHAR2(15) | A code to specify the expected data type of a response to the question. Possible values come from the reference codelist QUESTION_DATA_TYPE_CODE. Values are NUMBER, CHAR, DATE, and TIME. |
| DERIVED_FLAG | Not null | VARCHAR2(1) | A flag to indicate if this is a derived question. You can only enter or change responses to the question through a Derivation Procedure; you cannot enter or modify responses through the data entry or discrepancy management subsystems. |
| INTENT | Not null | VARCHAR2(200) | Freeform text, in mixed case, to describe the information the question will collect. |
| DERIVED_LOCK_FLAG | Not null | VARCHAR2(1) | Not used. |
| PROTOCOL_FLAG | Not null | VARCHAR2(1) | Not used. |
| QUESTION_STATUS_CODE | Not null | VARCHAR2(15) | A code to control the attributes and usage of a Question. Values are Provisional (P), Active (A), Retired (R). |
| DVG_MODIFIABLE_FLAG | Not null | VARCHAR2(1) | A flag to indicate if you can modify either the values in the discrete value group (DVG), or the name of the DVG assigned to this question. Applies only to questions with a data type of CHAR and not to safety questions. |
| SAFETY_QUESTION_FLAG | Not null | VARCHAR2(1) | A flag to indicate if the system always records the responses to this question in a corporate safety database. |
| SIGHT_VERIFICATION_FLAG | Not null | VARCHAR2(1) | A flag to indicate that the system completes the second pass data entry response field with the response from the first pass, allowing the response to be visually verified instead of being re-keyed. If this field is N, the second pass field is left blank and requires reentry. |
| UPPER_CASE_FLAG | Not null | VARCHAR2(1) | A flag to indicate if responses to the question are forced to uppercase. |
| VALIDATION_FAILURE_TYPE_CODE | Not null | VARCHAR2(15) | A code to specify the severity of a univariate discrepancy associated with a response to this question. |
| LENGTH | Not null | NUMBER(5) | The maximum number of allowable characters for a response to the question. The following values are the lengths for each type of data: 200 characters for type CHAR; 40 characters for type NUMBER; 8 characters for type DATE; 6 characters for type TIME. For a question of type NUMBER, a negative sign or a decimal point is not included as part of the length. The number in the decimal places field is part of this number. |
| STATUS_SAS_NAME | Null | VARCHAR2(8) | A name, unique within a domain, of the SAS name when you access the data validation status codes through the SAS package. |
| DISCRETE_VAL_GRP_ID | Null | NUMBER(10) | The ID of the discrete value group (DVG) associated with this question. |
| DISCRETE_VAL_GRP_SUBSET_NUM | Null | NUMBER(3) | The subset number of the discrete value group (DVG) associated with this question. |
| DECIMAL_PLACES | Null | NUMBER(2) | The expected maximum number of digits to the right of the decimal point for a response to a number question. The number in this field counts as part of the total number in the length field. It will be 0 for an integer response. |
| DEFAULT_PROMPT | Null | VARCHAR2(60) | Freeform text, up to 60 characters in mixed case, that appears on the data entry screens as the default prompt for data input. |
| LOWER_BOUND | Null | VARCHAR2(45) | The lowest inclusive value allowed as a response to the DCM question. This field is only available for questions of type NUMBER, DATE, or TIME. |
| UPPER_BOUND | Null | VARCHAR2(45) | The highest inclusive value allowed as a response to the DCM question. This field is available only for questions of type NUMBER, DATE, or TIME. |
| MEDICAL_EVAL_TYPE_CODE | Null | VARCHAR2(15) | A code to classify the question according to how the responses are medically evaluated. |
| RETIREMENT_REASON_TYPE_CODE | Null | VARCHAR2(15) | A code of a summary of why a retired question was retired. |
| STATUS_COMMENT_TEXT | Null | VARCHAR2(200) | A comment about why the status of the question was changed. |
| DATE_TIME_TYPE_CODE | Null | VARCHAR2(3) | A code to specify the expected precision of the response for a question with data type of DATE or TIME. |
| MODIFICATION_TS | Null | DATE | Date and time when this question was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this question. |
| REPLICATION_IND | Null | VARCHAR2(1) | A flag to show if the question has been replicated to another location. |
| DATE_TIME_FORMAT_CODE | Null | VARCHAR2(15) | A code to control how much to display of a response to a date or time question. |
| QUESTION_SET_ID | Null | NUMBER(10) | The ID of the question set to which the question belongs. |
| QS_PARENT_QUESTION_ID | Null | NUMBER(10) | The ID of the parent question for a question in a question set. |
| QUESTION_SET_QUESTION_ID | Null | NUMBER(10) | The ID of the question set question that corresponds to the question in a question set. |
| EXTRACT_MACRO_NAME | Null | VARCHAR2(30) | The name of the extract macro associated with this question. |
| ALPHA_DVG_MODIFIABLE_FLAG | Not Null | VARCHAR2(1) | Flag to indicate if the alpha discrete value group assigned to this question is modifiable. |
| ALPHA_DVG_ID | Null | NUMBER(10) | The ID of the alpha discrete value group assigned to this question. |
| ALPHA_DVG_SUBSET_NUM | Null | NUMBER(3) | The subset number of the alpha discrete value group assigned to this question. |
When you define a question, you can associate it with one or more categories, which are general search classifications for the question. This table stores the relationships between questions and their categories; each row entered in this table records a category to which the question belongs.
Primary Key QUESTION_ID, QUESTION_CATEGORY_TYPE_CODE
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| QUESTIONS | QUESTION_ID | QUESTION_ID |
QUESTION_CATEGORY_RELATIONS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| QUESTION_ID | Not null | NUMBER(10) | The ID of the question to which the category applies. |
| QUESTION_CATEGORY_TYPE_CODE | Not null | VARCHAR2(15) | The type of question category. Valid values come from the installation reference codelist QUESTION_CATEGORY_TYPE_CODE. |
| CREATION_TS | Not null | DATE | Date and time when this question category relation record was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this question category relation record. |
| MODIFICATION_TS | Null | DATE | Date and time when this question category relation record was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this question category relation record |
| REPLICATION_IND | Not null | VARCHAR2(1) | A flag to show if the question category has been replicated from another location. |
Question groups are sets of related questions.
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| DCM_QUESTION_GROUPS | QUESTION_GROUP_ID | QUESTION_GROUP_ID |
| QUESTION_GROUP_QUESTIONS | QUESTION_GROUP_ID | QUESTION_GROUP_ID |
QUESTION_GROUPS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| QUESTION_GROUP_ID | Not null | NUMBER(10) | A unique, system-generated ID for the question group. |
| CREATION_TS | Not null | DATE | Date and time when this question group was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this question group. |
| NAME | Not null | VARCHAR2(30) | A name for the question group. |
| DOMAIN | Not null | VARCHAR2(15) | The Global Library domain to which the question group belongs. |
| QUESTION_GROUP_STATUS_CODE | Not null | VARCHAR2(15) | The status of the question group. Values are P (Provisional), A (Active), and R (Retired). |
| DCM_DCI_QG_TYPE_CODE | Not null | VARCHAR2(15) | A code for the medical use of the question group. Valid values come from the installation reference codelist DCM_DCI_QG_TYPE_CODE. |
| DESCRIPTION | Not null | VARCHAR2(70) | A freeform text description of the question group. |
| LAST_STATUS_CHANGE_TS | Not null | DATE | The date and time that the question group's status was last changed. |
| EXPANDABLE_FLAG | Not null | VARCHAR2(1) | A flag to indicate if you can add questions to the question group. |
| RETIREMENT_REASON_TYPE_CODE | Null | VARCHAR2(15) | A code for why a retired question group was retired. |
| STATUS_COMMENT_TEXT | Null | VARCHAR2(200) | A freeform text description of why the question group's status was last changed. |
| MODIFICATION_TS | Null | DATE | Date and time when this question group was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this question group. |
| REPLICATION_IND | Null | VARCHAR2(1) | A flag to show if the question group has been replicated from another location. |
This view holds the assignment of questions to question groups.
Primary Key QUESTION_GROUP_QUESTION_ID
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| QUESTION_GROUPS | QUESTION_GROUP_ID | QUESTION_GROUP_ID |
| QUESTIONS | QUESTION_ID | QUESTION_ID |
| DISCRETE_VALUE_GROUPS | DISCRETE_VAL_GRP_ID | DISCRETE_VAL_GRP_ID |
| DISCRETE_VALUE_GROUPS | DISCRETE_VAL_GRP_SUBSET_NUM | DISCRETE_VAL_GRP_SUBSET_NUM |
| DISCRETE_VALUES | DISCRETE_VAL_GRP_ID | DISCRETE_VALUE_DVG_ID |
| DISCRETE_VALUES | DISCRETE_VAL_GRP_SUBSET_NUM | DISCRETE_VALUE_DVG_SUBSET_NUM |
| DISCRETE_VALUES | DISCRETE_VALUE_IND_VALUE | DISCRETE_VALUE_VALUE |
| DCM_QUESTIONS | QUESTION_ID | QUESTION_ID |
QUESTION_GROUP_QUESTIONS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| QUESTION_GROUP_QUESTION_ID | Not null | NUMBER(10) | A unique, system-generated ID for the question group question. |
| CREATION_TS | Not null | DATE | Date and time when this question group question was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this question group question. |
| QUESTION_NAME | Not null | VARCHAR2(20) | Denormalized from the QUESTIONS table. The unique identification, within a domain, of a question. |
| OCCURRENCE_SN | Not null | NUMBER(3) | The number of times the Question reoccurs in the Question Group. |
| DISPLAY_SN | Not null | NUMBER(3) | The order in which this question should be displayed in the Question Group during data entry. When you add a Question to the Question Group, it is assigned, by default, the next available sequence number. You can change this value only for Provisional and Active DCMs. |
| INDICATOR_FLAG | Not null | VARCHAR2(1) | Denormalized from the QUESTIONS table. The unique identification, within a domain, of a question. |
| MANDATORY_FLAG | Not null | VARCHAR2(1) | A flag to show if a response to this question is required. If no value is given a univariate discrepancy will be created. |
| UPPER_CASE_FLAG | Not null | VARCHAR2(1) | A flag to indicate if responses to the question are forced to uppercase. |
| SIGHT_VERIFICATION_FLAG | Not null | VARCHAR2(1) | Override to the value at the question level. A flag to indicate that the system completes the second pass data entry response field with the response from the first pass, allowing the response to be visually verified instead of being re-keyed. If this field is N, the second pass field is left blank and requires reentry. |
| SAS_LABEL | Not null | VARCHAR2(40) | Freeform text field, in mixed case, supplied to the SAS Labels field in the SAS views and SAS datasets, to describe the question. |
| REQUIRED_FLAG | Not null | VARCHAR2(1) | A flag to show if the question is required in the question group. |
| DVG_MODIFIABLE_FLAG | Not null | VARCHAR2(1) | Override to the value at the question level. A flag to indicate if you can modify either the values in the discrete value group (DVG), or the name of the DVG, assigned to this question. Applies only to questions with a data type of CHAR and not to safety questions. |
| QUESTION_ID | Not null | NUMBER(10) | The ID of the question assigned to this question group by the question group question. |
| QUESTION_GROUP_ID | Not null | NUMBER(10) | The ID of the question group concerned. |
| DISCRETE_VAL_GRP_ID | Null | NUMBER(10) | The ID of the discrete value group (DVG) associated with this question. Modifiable if the DVG_MODIFIABLE_FLAG = Y at the question level. |
| DISCRETE_VAL_GRP_SUBSET_NUM | Null | NUMBER(3) | The subset number of the discrete value group (DVG) associated with this question. Modifiable if the DVG_MODIFIABLE_FLAG = Y at the question level. |
| COLLECTED_FLAG | Not null | VARCHAR2(1) | A flag to indicate if the system collects responses to the question. |
| AUTOSKIP_FLAG | Not null | VARCHAR2(1) | A flag to indicate if data entry jumps the cursor to the next field after the current field has been completed. |
| LENGTH | Not null | NUMBER(3) | Override to the value from the question level. The maximum number of allowable characters for a response to the question. The following values are the maximum lengths for each type of data: 200 characters for type CHAR; 40 characters for type NUMBER; 8 characters for type DATE; 6 characters for type TIME. For a question of type NUMBER, a negative sign or a decimal point is not included as part of the length. The number in the decimal places field is part of this number. |
| VALIDATION_FAILURE_TYPE_CODE | Not null | VARCHAR2(15) | A code to specify the severity of a univariate discrepancy if one is created. |
| DATA_ENTRY_DISPLAY_LENGTH | Null | NUMBER(3) | The number of characters available for the data entry response to this question. |
| DISCRETE_VALUE_IND_VALUE | Null | VARCHAR2(40) | The value for the indicator question, which indicates that other questions in the question group should have responses. See INDICATOR_QUESTION_FLAG. |
| UPPER_BOUND | Null | VARCHAR2(45) | Specifies the highest inclusive value that is allowed for the question without causing a univariate discrepancy. |
| LOWER_BOUND | Null | VARCHAR2(45) | Specifies the lowest inclusive value allowed for the question without causing a univariate discrepancy. |
| HELP_TEXT | Null | VARCHAR2(200) | Freeform text, in mixed case, available for display during data entry. |
| DEFAULT_RESPONSE_TEXT | Null | VARCHAR2(200) | Default response displayed at entry time. Constrained by the system to be compatible with the DCM question's data type. |
| DEFAULT_PROMPT | Null | VARCHAR2(60) | Freeform text, in mixed case, for use by the default screen layout generation to create the default data entry screens. Also used to describe the question in some applications. |
| DECIMAL_PLACES | Null | NUMBER(2) | The expected maximum number of digits to the right of the decimal point for a response to a number question. The number in this field counts toward the total number in the length field. This number will be 0 for an integer response. |
| DATE_TIME_TYPE_CODE | Null | VARCHAR2(3) | A code to specify the expected precision of the response for a question with data type of DATE or TIME. |
| MODIFICATION_TS | Null | DATE(3) | Date and time when this question group question was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this question group question. |
| REPLICATION_IND | Null | VARCHAR2(1) | A flag to show if the question group question has been replicated from another location. |
| DERIVED_FLAG | Not null | VARCHAR2(1) | A flag to indicate if this is a derived question. You can only enter or change responses to the question through a Derivation Procedure; you cannot enter or modify responses through the data entry or discrepancy management subsystems. |
| DATE_TIME_FORMAT_CODE | Null | VARCHAR2(15) | A code to control how much to display of a response to a date or time question. |
| ALPHA_DVG_MODIFIABLE_FLAG | Not Null | VARCHAR2(1) | Flag to indicate if the alpha discrete value group assigned to this question is modifiable. |
| ALPHA_DVG_ID | Null | NUMBER(10) | The ID of the alpha discrete value group assigned to this question. |
| ALPHA_DVG_SUBSET_NUM | Null | NUMBER(3) | The subset number of the alpha discrete value group assigned to this question. |
Ranges store lower and upper normal bounds for the response to a particular question at a lab. The ranges can be used to compare lab results from different labs.
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| LABS | LAB_ID | LAB_ID |
| LAB_RANGE_SUBSETS | LAB_RANGE_SUBSET_NUM | LAB_RANGE_SUBSET_NUM |
| QUESTIONS | QUESTION_ID | QUESTION_ID |
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| RANGE_ID | Not null | NUMBER(10) | A unique, system-generated ID for the range. |
| LAB_ID | Not null | NUMBER(10) | The ID of the lab to which the range belongs. |
| QUESTION_ID | Not null | NUMBER(10) | The ID of the question to which the range applies. |
| RANGE_TYPE | Not null | VARCHAR2(15) | A code for the type of range. Possible values come from the reference codelist LR_RANGE_TYPE. Values are LAB and TEXTBOOK. |
| RANGE_STATUS_TYPE | Not null | VARCHAR2(15) | A code for the current status of the range. Possible values come from the reference codelist LR_RANGE_STATUS. Values are APPROVED, SUSPICIOUS, UNAPPROVED, and MISSING. MISSING is a system-maintained code used when the lab unit and the minimum and maximum values are not specified. |
| CREATION_TS | Not null | DATE | Date and time when this range was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this range. |
| MINIMUM_AGE_DAYS | Not null | NUMBER(10) | The minimum age of a patient in days to which the range applies. |
| MAXIMUM_AGE_DAYS | Not null | NUMBER(10) | The maximum age of a patient in days to which the range applies. |
| EFFECTIVE_START_DATE | Not null | DATE | The date that the range became available. |
| EFFECTIVE_END_DATE | Not null | DATE | The date the range stopped being available. |
| GENDER | Not null | VARCHAR2(15) | The sex of patients to whom this range applies. Possible values come from the system reference codelist LR_GENDER. Values are M (Male), F (Female), or B (Both). |
| MODIFICATION_TS | Null | DATE | Date and time when this range was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this range. |
| LAB_UNIT_CODE | Null | VARCHAR2(40) | Lab units of the lab test question. |
| MINIMUM_AGE | Null | NUMBER(3) | Minimum age to which the range applies. |
| MINIMUM_AGE_UNIT_TYPE | Null | VARCHAR2(15) | Unit of the minimum age. Possible values come from the system reference codelist LR_AGE_UNIT. Values are DAYS, MONTHS, and YEARS. |
| MAXIMUM_AGE | Null | NUMBER(3) | Maximum age to which the range applies. |
| MAXIMUM_AGE_UNIT_TYPE | Null | VARCHAR2(15) | Unit of the maximum age. Possible values come from the system reference codelist LR-AGE-UNIT. Values are DAYS, MONTHS, and YEARS. |
| MINIMUM_VALUE | Null | NUMBER | The lower value of the range. |
| MAXIMUM_VALUE | Null | NUMBER | The upper value of the range. |
| RANGE_COMMENT | Null | VARCHAR2(240) | Freeform text, in mixed case, containing supplementary information about the range. |
| LAB_RANGE_SUBSET_NUM | Not null | NUMBER(10) | Lab range subset associated with the range. |
| SUBSET_USAGE_INDICATOR | Not null | VARCHAR2(240) | Internal flag to keep track of the subset and the ranges association. |
The RDCI_HISTORY table stores history records for three types of transaction:
In all three of these transactions, Oracle Clinical bases the CREATION_TS and CREATED_BY values on the date that the RDCI_HISTORY record is created. The system uses the same CLINICAL_STUDY_ID as the RDCI record.
Note:
Oracle Clinical 4.6 includes the following functional enhancements related to the RDCI_HISTORY table:Users with lock privilege can specify the list of users to unlock.
The system tracks approval and verification records.
The system tracks reversals for changes in data entry after approvals and verifications.
The system tracks retaining or reversing approvals and verifications during form version migration.
Approvals and Verifications of Records in RDC
When you use Oracle Remote Data Capture to approve or verify a record (or to undo approval or verification), the system populates five records in the RDCI_HISTORY table. The RECEIVED_DCI_ID, TRANS_TS, and TRANS_BY columns are populated with the values in the current RDC environment; the user cannot set these values manually. The TRANS_TYPE reflects the RDC action the user selects: VERIFY, APPROVAL, UNDO VERIFY, or UNDO APPROVAL. The COMMENT_TEXT stores the user's comment about this change in approval or verification status.
Records of the Reason for Change when Changing an RDCI or RDCM
Oracle Clinical populates the DATA_CHANGE_REASON_TYPE_CODE column only when you make a change to an RDCI record. The system validates this value against one of two reference codelists:
RDCI_CHANGE_REASON_TYPE_CODE for log in form and API
RDCI_CHANGE_REASON2_TYPE_CODE for additional values for API
In either case, the TRANS_TYPE used is AUDIT.
Populating RDCI_HISTORY from the API
You can populate the same five columns as in approvals and verifications, but the TRANS_TYPE selection is set by the API.
Primary Key RECEIVED_DCI_ID, TRANS_TS, TRANS_BY, TRANS_TYPE
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| RECEIVED_DCIS | RECEIVED_DCI_ID | RECEIVED_DCI_ID |
RDCI_HISTORY Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| RECEIVED_DCI_ID | Not null | NUMBER(10) | The RECEIVED_DCI_ID of the RDCI record for which this audit record was created. |
| TRANS_TS | Not null | DATE | The transaction timestamp set by the front end through its call of SetExternalContext(). |
| TRANS_BY | Not null | VARCHAR2(30) | The transaction user set by the front end through its call of SetExternalContext(). |
| TRANS_TYPE | Not null | VARCHAR2(15) | The transaction type set by the front end through its call of SetExternalContext(). It contains one of the values of the reference codelist EXTERNAL_TRANS_TYPE. |
| CREATION_TS | Not null | DATE | Date and time when this audit record was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this audit record. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | ID of the clinical study to which this received DCI belongs. This column enables you to focus your queries to received DCIs from one study, or a more discrete set of studies. |
| DATA_CHANGE_REASON_TYPE_CODE | Null | VARCHAR2(15) | The reason that a change was made to this RDCI record. |
| COMMENT_TEXT | Null | VARCHAR2(200) | Freeform text set by the front end through its call of SetExternalContext(). |
This table records the receipt of an actual DCI. The table is self-journaling.
The test table for RECEIVED_DCIS is RECEIVED_DCIST.
Primary Key RECEIVED_DCI_ID, RECEIVED_DCI_ENTRY_TS
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
| OCL_SITES | SITE_ID | SITE_ID |
| OCL_INVESTIGATORS | INVESTIGATOR_ID | INVESTIGATOR_ID |
| PATIENT_POSITIONS | PATIENT_POSITION_ID | PATIENT_POSITION_ID |
| ACTUAL_EVENTS | ACTUAL_EVENT_ID | ACTUAL_EVENT_ID |
| CLINICAL_PLANNED_EVENTS | CLIN_PLAN_EVE_ID | CLIN_PLAN_EVE_ID |
| DCIS | DCI_ID | DCI_ID |
| RECEIVED_PAGES | RECEIVED_DCI_ID | RECEIVED_DCI_ID |
| RECEIVED_DCMS | RECEIVED_DCI_ID | RECEIVED_DCI_ID |
| DATA_FILES | DATA_FILE_ID | DATA_FILE_ID |
RECEIVED_DCIS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| RECEIVED_DCI_ID | Not null | NUMBER(10) | A unique, system-generated ID for the received DCI. |
| RECEIVED_DCI_ENTRY_TS | Not null | DATE | The date and time that the received DCI record version was created. |
| DCI_ID | Not null | NUMBER(10) | The ID of the DCI corresponding to the received DCI. |
| ENTERED_BY | Not null | VARCHAR2(30) | The name of the person who created the received DCI record version. |
| END_TS | Not null | DATE | The time until this version of the record is active. Defaulted to to_date (3000000, 'J') for a new document. Equal to the entry timestamp of the next version of this record (if any) if not equal to the default. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which the DCI belongs. |
| INVESTIGATOR_ID | Not null | NUMBER(10) | The ID of the investigator who completed the DCI. |
| INVESTIGATOR | Not null | VARCHAR2(10) | The code of the investigator who completed the DCI. |
| SITE_ID | Not null | NUMBER(10) | The ID of the site where the DCI was completed. |
| SITE | Not null | VARCHAR2(10) | The name of the site where the DCI was completed. |
| PATIENT_POSITION_ID | Not null | NUMBER(10) | The ID of the patient for whom the DCI was completed. |
| PATIENT | Not null | VARCHAR2(10) | The code of the patient for whom the DCI was completed. |
| ACTUAL_EVENT_ID | Not null | NUMBER(10) | The ID of the actual event at which this received DCI was collected. |
| RECEIVED_DCI_STATUS_CODE | Not null | VARCHAR2(15) | System-generated. Indicates the status of the received DCI. Possible values come from the reference codelist RECEIVED_STATUS_DCI_CODE. Values are:
|
| LOG_IN_TS | Not null | DATE | Date and time when the original record was logged in. Stays constant across journaled versions of the record. |
| DOCUMENT_NUMBER | Not null | VARCHAR2(20) | The external, unique key for the received DCI. Unique only in combination with RECEIVED_DCI_ENTRY_TS. DOCUMENT_NUMBER can change across versions of the DCI. |
| DATA_LOCK_FLAG | Not null | VARCHAR2(1) | Flag indicating if it is acceptable to change data for this received DCI without privileged update. |
| ACCESSIBLE_TS | Not null | DATE | Timestamp indicating at what time this received DCI became available to the external world for processing in Procedures and, after the subsequent batch validation, via the stable data extract views. Defaulted to to_date (3000000, 'J') and set to SYSDATE at either the completion of Pass 1 or at Pass 2, depending on whether Pass 2 is specified as required for the study. |
| BLANK_FLAG | Not null | VARCHAR2(1) | Flag indicating if there is any data for this received DCI. Constrained by the system to be consistent with the blank flag on the associated received DCMs. |
| CLIN_PLAN_EVE_ID | Not null | NUMBER(10) | The ID for the clinical planned event for this received DCI. |
| CLIN_PLAN_EVE_NAME | Not null | VARCHAR2(20) | The external identifier for the clinical planned event for this received DCI. |
| SUBEVENT_NUMBER | Not null | NUMBER(2) | A number marking unplanned events, if any, occurring after the planned event recorded on this received DCI, but before the next planned event. Defaulted to 0 for the planned event itself. |
| DCI_DATE | Null | VARCHAR2(8) | Date at which the DCI was collected. |
| DCI_TIME | Null | VARCHAR2(6) | Time at which the DCI was collected. |
| DATA_FILE_ID | Null | NUMBER(10) | Identifier for the external electronic data source (if any) for this received DCI. |
| DATA_LOCK_TS | Null | DATE | If data lock flag is set, the date and time at which it was set. Can be used in conjunction with the Last Change timestamp to report on potential changes made after locking. |
| LAST_STATUS_CHANGE_TS | Null | DATE | Time at which the received DCI had a status code change. |
| LAST_NEW_VERSION_TS | Null | DATE | Time at which the received DCI had the receipt of a new version specified in key changes. In this context, a new version means the acknowledgment that a modified version of the associated CRF has been received. Can be used to track that after the logging of the new version, a subsequent data change was made to reflect the changes on the new version. This external use of version is not to be confused with the internal use of the term new versions, which describes the creation of modified records used to implement self-journaling. |
| COMMENT_TEXT | Null | VARCHAR2(200) | Internal comment, if any, for this received DCI. Changes do not trigger audit by self-journaling. |
| IMAGE_DOCUMENT_NUMBER | Null | VARCHAR2(20) | Not currently used. |
| IMAGE_INDEX_DATE | Null | DATE | Not currently used. |
| MODIFICATION_TS | Null | DATE | Date and time when this received DCI was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this received DCI. |
| VISIT_NUMBER | Not null | NUMBER(5) | A numeric representation of the planned event for this received DCI. |
| FIRST_BOOK_PAGE | Null | VARCHAR2(15) | The book page at which this received DCI starts in the DCI book. |
| NUMBER_OF_PAGES | Null | NUMBER(4) | The number of pages spanned by the received DCI in the DCI book. Populated only if CRF page tracking is enabled. |
| DCI_BOOK_ID | Null | NUMBER(10) | ID identifying the DCI book from which the DCI was chosen for data entry. Populated if CRF page tracking is enabled and for any CRFs entered through RDC. |
| LAST_CHANGE_TS | Null | DATE | Timestamp to track the last time any data changed for the received DCI or any of its associated received DCMs or responses. Used for inclusion in data replication. |
| VERSION_SN | Null | NUMBER(3) | Version number of the DCI Form used for collecting data in PDF mode. |
| VERSION_SN_NLS | Null | NUMBER(3) | Version number of the Local Language DCI Form used for collecting data in PDF mode. |
This table records the receipt of an actual DCM. The table is self-journaling.
The test table for RECEIVED_DCMS is RECEIVED_DCMST.
Primary Key RECEIVED_DCM_ID, RECEIVED_DCM_ENTRY_TS
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
| OCL_SITES | SITE_ID | SITE_ID |
| OCL_INVESTIGATORS | INVESTIGATOR_ID | INVESTIGATOR_ID |
| PATIENT_POSITIONS | PATIENT_POSITION_ID | PATIENT_POSITION_ID |
| ACTUAL_EVENTS | ACTUAL_EVENT_ID | ACTUAL_EVENT_ID |
| CLINICAL_PLANNED_EVENTS | CLIN_PLAN_EVE_ID | CLIN_PLAN_EVE_ID |
| LABS | LAB_ID | LAB_ID |
| DCMS | DCM_ID | DCM_ID |
| DCMS | DCM_SUBSET_SN | DCM_SUBSET_SN |
| DCMS | DCM_LAYOUT_SN | DCM_LAYOUT_SN |
| DISCREPANCY_ENTRIES | RECEIVED_DCM_ID | RECEIVED_DCM_ID |
| DISCREPANCY_ENTRIES | RECEIVED_DCM_ENTRY_TS | RECEIVED_DCM_ENTRY_TS |
| RESPONSES | RECEIVED_DCM_ID | RECEIVED_DCM_ID |
RECEIVED_DCMS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| RECEIVED_DCI_ID | Not null | NUMBER(10) | A unique, system-generated ID for the received DCM. |
| RECEIVED_DCM_ENTRY_TS | Not null | DATE | The date and time that the received DCM record version was created. |
| ENTERED_BY | Not null | VARCHAR2(30) | The name of the person who created the received DCM record version. |
| END_TS | Not null | DATE | The time until this version of the record is active. Defaulted to to_date (3000000, 'J') for a new document. Equal to the entry timestamp of the next version of this record (if any) if not equal to the default. |
| RECEIVED_DCI_ID | Not null | NUMBER(10) | ID for the received DCI to which this received DCM belongs. |
| SN | Not null | NUMBER(3) | Serial number of the received DCM within other received DCMs belonging to the same parent received DCI. Constrained to match the DCM_MODULE_SN of the DCI_MODULE of the DCI that was used to enter this received DCM. |
| INVESTIGATOR_ID | Not null | NUMBER(10) | The ID of the investigator who completed the DCM. |
| INVESTIGATOR | Not null | VARCHAR2(10) | The code of the investigator who completed the DCM. |
| SITE_ID | Not null | NUMBER(10) | The ID of the site where the DCM was completed. |
| SITE | Not null | VARCHAR2(10) | The code of the site where the DCM was completed. |
| DCM_ID | Not null | NUMBER(10) | The ID of the DCM that corresponds to this received DCM. |
| DCM_SUBSET_SN | Not null | NUMBER(3) | The subset number of the DCM subset that corresponds to this received DCM. |
| DCM_LAYOUT_SN | Not null | NUMBER(3) | The layout number of the DCM layout used to enter this received DCM. |
| ACTUAL_EVENT_ID | Not null | NUMBER(10) | The ID for the actual event for this received DCM. |
| ACCESSIBLE_TS | Not null | DATE | Timestamp indicating at what time this received DCM is available to the external world for processing in Procedures and, after the subsequent batch validation, via the stable data extract views. Defaulted to to_date(3000000, 'J') and set to SYSDATE at either the completion of Pass 1 or at Pass 2, depending on whether Pass 2 is specified as required by the study. |
| DATA_LOCK_FLAG | Not null | VARCHAR2(1) | Flag indicating if it is acceptable to change data for this received DCM without privileged update. |
| RECEIVED_DCM_STATUS_CODE | Not null | VARCHAR2(15) | System-generated. Indicates the status of the received DCM. Possible values come from the reference codelist RECEIVED_DCM_STATUS_CODE.
Values for the RECEIVED_DCM_STATUS_CODE are:
|
| BLANK_FLAG | Not null | VARCHAR2(1) | Flag indicating if there is any data for this received DCM. |
| PATIENT_POSITION_ID | Not null | NUMBER(10) | The ID of the patient for whom the DCM was completed. |
| PATIENT | Not null | VARCHAR2(10) | The code of the patient for whom the DCM was completed. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which the DCM belongs. |
| CLIN_PLAN_EVE_ID | Not null | NUMBER(10) | The ID for the clinical planned event for this received DCM. |
| CLIN_PLAN_EVE_NAME | Not null | VARCHAR2(20) | The external identifier for the clinical planned event for this received DCM. |
| SUBEVENT_NUMBER | Not null | NUMBER(2) | A number marking unplanned events, if any, occurring after the planned event recorded on this received DCI, but before the next planned event. Defaulted to 0 for the planned event itself. |
| DCM_DATE | Null | VARCHAR2(8) | Date at which the DCM was collected. |
| DCM_TIME | Null | VARCHAR2(6) | Time at which the DCM was collected. |
| LAB_ID | Null | NUMBER(10) | The ID of the lab where the DCM was completed. |
| LAB | Null | VARCHAR2(10) | The code of the lab where the DCM was completed. |
| LAB_ASSIGNMENT_TYPE_CODE | Null | VARCHAR2(15) | A system-defined value that records how the lab associated with this received DCM was specified. Possible values come from the reference codelist LR_LAT. Values are:
|
| LAB_MODIFICATION_TS | Null | DATE | The date and time that the last change to the lab fields was made. |
| QUALIFYING_VALUE | Null | VARCHAR2(70) | The value for the qualifying question associated with the DCM that is used to identify a particular instance of a DCM within an actual event (or visit). Received DCMs for the same DCM must have only one instance at an actual event (irrespective of subset or layout) unless the received DCM is qualified by a qualifying value, in which case, one unique occurrence of the DCM is allowed for each unique qualifying value. |
| DATA_LOCK_TS | Null | DATE | If data_lock_flag is set, the time at which it was set. Can be used together with the LAST_DATA_CHANGE_TS to detect received DCMs that have had data modifications since the received DCM was locked. |
| LAST_STATUS_CHANGE_TS | Null | DATE | Time at which the received DCM had a status code change. |
| COMMENT_TEXT | Null | VARCHAR2(200) | Internal comment, if any, for this received DCM. Changes to the COMMENT_TEXT are not audited. They do not cause a new record version to be created. |
| LAST_DATA_CHANGE_TS | Null | DATE | Timestamp to track the last time any data changed for responses associated with the received DCM. Used for inclusion in batch validation and replication. Not modified by changes to the received DCM itself. |
| PASS_ONE_TS | Null | DATE | Date and time of first pass data entry for this received DCM. |
| PASS_ONE_BY | Null | VARCHAR2(30) | Operator who performed first pass data entry for this received DCM. |
| PASS_TWO_TS | Null | DATE | Date and time of second pass data entry for this received DCM. |
| PASS_TWO_BY | Null | VARCHAR2(30) | Operator who performed second pass data entry for this received DCM. |
| DATA_COMMENT_TEXT | Null | VARCHAR2(200) | A comment that is part of the data — for instance, text that the investigator writes in the margin of the CRF and that applies to the DCM as a whole. Changes to the DATA_COMMENT_TEXT are audited. They cause new record version creation. |
| MODIFICATION_TS | Null | DATE | Date and time when this RDCM records was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this RDCM record. |
| DOCUMENT_NUMBER | Not null | VARCHAR2(20) | The external unique key for the received DCM. |
| VISIT_NUMBER | Not null | NUMBER(5) | A numeric representation of the clinical planned event for this received DCM. |
| LOG_IN_TS | Not null | DATE | The date and time of original entry. Denormalized from the LOG_IN_TS of the associated received DCI. |
| LAB_RANGE_SUBSET_NUM | Not null | NUMBER(10) | The identifier, within a lab, of a particular lab range subset that should be used when combining data on this received DCM with lab range information. The base, or default, lab is subset 0. |
| LAST_RESPONSE_MODIFICATION_TS | Not null | DATE | Timestamp to track the last time any responses changed for the received DCM for inclusion in replication. This timestamp changes due to internal changes to responses — such as re-derivation of derived responses, re-computation of response validation status, or changes to responses due to re-execution of univariate validation during batch validation. |
| DCI_ID | Not null | NUMBER(10) | The ID of the DCI corresponding to the received DCI associated with this received DCM. |
The RECEIVED_PAGES table contains tracking or status information about the physical CRF pages associated with a received DCI. The table is populated only if CRF page tracking is enabled for a study. The system automatically populates the table when received DCIs are created in Log-In. The system automatically derives the page status and determines whether the page contains data by using a user-configurable database package. The page status can be manually modified, constrained by validation logic defined in the same database package. Only the current status of received pages is reflected in this table; the RECEIVED_PAGE_HISTORY table records changes to the received pages over time.
The test table for RECEIVED_PAGES is RECEIVED_PAGEST.
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| RECEIVED_DCIS | RECEIVED_DCI_ID | RECEIVED_DCI_ID |
| RECEIVED_PAGE_HISTORY | RECEIVED_PAGE_ID | RECEIVED_PAGE_ID |
RECEIVED_PAGES Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| RECEIVED_PAGE_ID | Not null | NUMBER(10) | A unique, system-generated ID for the received page. |
| RECEIVED_DCI_ID | Not null | NUMBER(10) | The unique identifier of the received DCI to which this received page belongs |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which the DCI book page belongs. |
| PAGE_STATUS | Not null | VARCHAR2(15) | The status of the received page. Valid values are defined in the installation reference codelist CRF_PAGE_STATUS_CODES. By default, the system uses the following modifiable statuses values: UNKNOWN, RECEIVED, PASS 1 COMPLETE, PASS 2 COMPLETE, PASS 2 PENDING, REMOVED, MISSING and BLANK. |
| HAS_DATA_FLAG | Not null | VARCHAR2(1) | A system-derived flag indicating whether the received page has any non-null response data on it. |
| RELATIVE_TO_DCI_PAGE | Not null | NUMBER(4) | The order of this received page within the group of pages allocated to the same received DCI. |
| BOOK_PAGE | Not null | VARCHAR2(15) | The unique, user-defined identifier of the DCI book page to which this received physical page corresponds. |
| COMMENT_TEXT | Null | VARCHAR2(200) | Freeform text describing the received page. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this received page. |
| CREATION_TS | Not null | DATE | Date and time when this received page was created. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this received page. |
| MODIFICATION_TS | Null | DATE | Date and time when this received page was last modified. |
The received page history tracks changes to received pages over time. This table includes records the current received page values as well as a record for each previous change to those values.
The test table for RECEIVED_PAGE_HISTORY is RECEIVED_PAGE_HISTORYT.
Primary Key RECEIVED_PAGE_ID, CREATION_TS
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| RECEIVED_PAGES | RECEIVED_PAGE_ID | RECEIVED_PAGE_ID |
RECEIVED_PAGE_HISTORY Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| RECEIVED_PAGE_ID | Not null | NUMBER(10) | A unique, system-generated ID for the received page. |
| RECEIVED_DCI_ID | Not null | NUMBER(10) | The unique identifier of the received DCI to which this received page belongs. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which the DCI book page belongs. |
| PAGE_STATUS | Not null | VARCHAR2(15) | The status of the received page. |
| HAS_DATA_FLAG | Not null | VARCHAR2(1) | A flag indicating whether the received page has any non-null response date. |
| RELATIVE_TO_DCI_PAGE | Not null | NUMBER(4) | The order of this received page within the group of pages allocated to the same DCI. |
| BOOK_PAGE | Not null | VARCHAR2(15) | The unique system identifier of the DCI book page to which this received page belongs. |
| COMMENT_TEXT | Null | VARCHAR2(200) | Freeform text describing the received page. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this received page history record. |
| CREATION_TS | Not null | DATE | Date and time when this received page history record was created. |
Reference codelists are simple sets of values a particular code can have.
Primary Key REFERENCE_CODELIST_NAME
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| REFERENCE_CODELIST_VALUES | REFERENCE_CODELIST_NAME | REFERENCE_CODELIST_NAME |
REFERENCE_CODELISTS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| REFERENCE_CODELIST_NAME | Not null | VARCHAR2(30) | A name for the reference codelist. |
| CREATION_TS | Not null | DATE | Date and time when this reference codelist was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this reference codelist. |
| ACTIVE_FLAG | Not null | VARCHAR2(1) | A flag to show if the reference codelist is active or retired. |
| MAX_LONG_VALUE_LENGTH | Not null | NUMBER(2) | The maximum allowed length of the long value for each entry. |
| MAX_SHORT_VALUE_LENGTH | Not null | NUMBER(2) | The maximum allowed length of the short value for each entry. |
| RC_TYPE_CODE | Not null | VARCHAR2(15) | The type of reference codelist. Valid values are:
|
| RC_DATA_TYPE_CODE | Not null | VARCHAR2(15) | Data type of short value. Valid values are CHAR, NUMBER, and DATE. |
| DESCRIPTION | Null | VARCHAR2(70) | A freeform text description of the reference codelist. |
| DEFAULT_SHORT_VALUE | Null | VARCHAR2(15) | The default short value for the reference codelist. |
| MODIFICATION_TS | Null | DATE | Date and time when this reference codelist was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this reference codelist. |
| REPLICATION_IND | Null | VARCHAR2(1) | A flag to show if the reference codelist has been replicated from another site. |
| APPLICATION_SYSTEM_NAME | Not null | VARCHAR2(30) | A code for the sub-system that owns the reference codelist. Oracle Clinical uses RXC, RXA_DES, and RXA_LR. |
Reference codelist values are particular values within a reference codelist that a code field can have.
Primary Key REFERENCE_CODELIST_NAME, REF_CODELIST_VALUE_SHORT_VAL
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| REFERENCE_CODELISTS | REFERENCE_CODELIST_NAME | REFERENCE_CODELIST_NAME |
REFERENCE_CODELIST_VALUES Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| REF_CODELIST_NAME | Not null | VARCHAR2(30) | A name for the reference codelist to which this individual value belongs. |
| REF_CODELIST_VALUE_SHORT_VAL | Not null | VARCHAR2(15) | A short code for the value. Unique within the reference codelist. |
| CREATION_TS | Not null | DATE(15) | Date and time when this reference codelist value was created. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this reference codelist value. |
| ACTIVE_FLAG | Not null | VARCHAR2(1) | A flag to show if the value is still active. |
| LONG_VALUE | Null | VARCHAR2(60) | A long value for the code. |
| DESCRIPTION | Null | VARCHAR2(70) | A description of the code. |
| DISPLAY_SN | Null | NUMBER(3) | A sequence number to control the display order in LOVs. |
| MODIFICATION_TS | Null | DATE | Date and time when this reference codelist value was last modified. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this reference codelist value. |
| REPLICATION_IND | Null | VARCHAR2(1) | User who last modified this reference codelist value. |
Regions are areas where studies can be conducted. They can be generally recognized political or geographic regions, but they can also be more general, user-defined names, such as Northern Europe.
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| OCL_INVESTIGATORS | REGION_CODE | STATE |
| OCL_INVESTIGATORS | REGION_CODE | COUNTRY |
| OCL_SITES | REGION_CODE | STATE |
| OCL_SITES | REGION_CODE | COUNTRY |
| OCL_STUDIES | REGION_ID | REGION_ID |
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| REGION_ID | Not null | NUMBER(10) | A unique, system-generated ID for the region. |
| REGION_CODE | Not null | VARCHAR2(7) | A unique code for the region. |
| DESCRIPTION | Not null | VARCHAR2(200) | A description for the region. This is most applicable when a user-defined region has been defined. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created this region record. |
| CREATION_TS | Not null | DATE | Date and time when this region was created. |
| START_DATE | Not null | DATE | The date that the region became active. |
| NAME | Not null | VARCHAR2(60) | A name for the region. |
| END_DATE | Null | DATE | The date that the region stopped being active. |
| REGION_TYPE_CODE | Not null | VARCHAR2(7) | The type of region. Valid values are CONT (for Continent), COUNTRY, and STATE. The valid values for Region Type come from the installation reference codelist called REGION_TYPE_CODE. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this region. |
| MODIFICATION_TS | Null | DATE | Date and time when this region was last modified. |
Responses record the data values for a particular instance of a DCM question. The table is self-journaling, and its test table is RESPONSEST.
Accessing data in the RESPONSES table following response index changes
Include CLINICAL_STUDY_ID in all access to the responses table. Since responses are in a partition on the basis of CLINICAL_STUDY_ID, the query optimizer can restrict its search to the proper partition if the query contains CLINICAL_STUDY_ID. This is called partition pruning. In order of preference, this reference to CLINICAL_STUDY_ID can be a constant, a bind variable in an aqua-join, or a join from another table.
The primary access path is the concatenated index RESPONSE_RDCM_NFK_IDX that is prefixed with CLINICAL_STUDY_ID in order to force responses from different studies in the same partition to be physically grouped together, and to optimize certain partition accesses. Even in a non-partitioned database, the index begins with CLINICAL_STUDY_ID.
Redirect all previous queries on DCM_QUESTION_ID or DCM_QUESTION_GROUP_ID to use a join through the RECEIVED_DCMS table so that they can use the concatenated index. Since there are no longer indexes with DCM_QUESTION_ID or DCM_QUESTION_GROUP_ID as leading keys, these are no longer efficient access paths. Much access involving these keys is already done in the context of a Received DCM, so the query retuning is usually minimal. In some cases, it might be necessary to add joins to DCM_QUESTIONS or DCM_QUESTION_GROUPS, and then through RECEIVED_DCMS via DCM_ID.
Primary Key RESPONSE_ID, RESPONSE_ENTRY_TS
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| RECEIVED_DCMS | RECEIVED_DCM_ID | RECEIVED_DCM_ID |
| DISCREPANCY_ENTRIES | RESPONSE_ID | RESPONSE_ID |
| DISCREPANCY_ENTRIES | RESPONSE_ENTRY_TS | RESPONSE_ENTRY_TS |
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
| VALIDATION_REPORTED_VALUES | RESPONSE_ID | RESPONSE_ID |
| VALIDATION_REPORTED_VALUES | RESPONSE_ENTRY_TS | RESPONSE_ENTRY_TS |
| DCM_QUESTION_GROUPS | DCM_QUESTION_GRP_ID | DCM_QUESTION_GRP_ID |
| DCM_QUESTIONS | DCM_QUESTION_ID | DCM_QUESTION_ID |
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| RESPONSE_ID | Not null | NUMBER(10) | A unique, system-generated ID for the response. |
| RESPONSE_ENTRY_TS | Not null | DATE | The date and time that the response was entered. |
| ENTERED_BY | Not null | VARCHAR2(30) | The name of the person who entered the response. |
| RECEIVED_DCM_ID | Not null | NUMBER(10) | ID for the received DCM to which this response belongs. |
| DCM_QUESTION_ID | Not null | NUMBER(10) | ID of the DCM question to which the response belongs. |
| DCM_QUESTION_GROUP_ID | Not null | NUMBER(10) | ID of the DCM question group to which the DCM question for the response belongs. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the clinical study to which the response belongs. |
| REPEAT_SN | Not null | NUMBER(3) | The repeat or logical row number within the DCM question group for the response. |
| END_TS | Not null | DATE | The time until this version of the record is active. Defaulted to to_date (3000000, 'J') for a new document. Equal to the entry timestamp of the next version of this record (if any) if not equal to the default. |
| VALIDATION_STATUS | Not null | VARCHAR2(3) | Three-part indicator showing whether univariate, multivariate, or manual discrepancies are present for the response and what the status of those discrepancies are. Values for resolved discrepancies are derived from the user-defined long value in the reference codelist DISCREPANCY_RESOLU_TYPE_CODE. In the case of multivariate discrepancies (which also include indicator discrepancies) multiple discrepancies can be associated with the response. The value reflects the highest status as defined by the order below. Depending on the long value of the reference codelist entry for the VAL_STATUS in the codelist OCL_OPTIONS_TYPE_CODE, the validation status can be optionally maintained with full auditing as of each batch validation (the default), maintained only for the current response value (NO_AUDIT), or not maintained at all (NONE).
The possible values of the indicator are:
|
| SECOND_PASS_INDICATOR | Null | VARCHAR2(1) | Indicates the status of the data of the response about whether first, second, reconciliation and/or update passes has been done on this response and whether data was updated in the passes. Possible values are: N – Pass 2 not performed Y – Pass 2 comparison successfully performed 1 – Pass 2 comparison failed, Pass 2 value accepted 2 – Pass 2 comparison failed, Pass 1 value accepted 3 – Response modified in update mode 4 – Response created in update mode by repeat creation 5 – Response modified in reconciliation mode 6 – Response created in reconciliation mode by repeat creation |
| VALUE_TEXT | Null | VARCHAR2(200) | The actual text for the response. |
| DISCREPANCY_INDICATOR | Null | VARCHAR2(1) | Flag indicating whether a univariate discrepancy, a manual discrepancy, or both exists for the response. Possible values are U, M, and B, respectively. |
| DATA_CHANGE_REASON_TYPE_CODE | Null | VARCHAR2(15) | Code indicating reason a new version of the response was created. Value is populated on the version prior to the new version, or, in the case of deletions, on the final version. |
| DATA_COMMENT_TEXT | Null | VARCHAR2(200) | Investigator comment, if any, for the response. |
| AUDIT_COMMENT_TEXT | Null | VARCHAR2(200) | If the record was updated after it was made accessible in the Update mode of data entry, the comment, if any, added by the operator. Value is populated on the version prior to the new version, or, in the case of deletions, on the final version. |
| EXCEPTION_VALUE_TEXT | Null | VARCHAR2(200) | The full value of the response is stored in this column if a discrepancy has been created of the type that indicates that the value is inconsistent with the database use of the DCM question. In particular:
|
Study site patient positions record the assignment of patient positions to study sites.
The test table for this table is STUDY_SITE_PATIENT_POSITIONST.
Primary Key SITE_ID, CLINICAL_STUDY_ID, PATIENT_POSITION_ID
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| PATIENT_POSITIONS | PATIENT_POSITION_ID | PATIENT_POSITION_ID |
| OCL_STUDY_SITES | SITE_ID | SITE_ID |
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
STUDY_SITE_PATIENT_POSITIONS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| SITE_ID | Not null | NUMBER(10) | The ID of the site assigned to the study. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the study concerned. |
| PATIENT_POSITION_ID | Not null | NUMBER(10) | The ID of the patient assigned to the study site. |
| START_DATE | Not null | DATE | The date that the patient assignment started. |
| CURRENT_FLAG | Not null | VARCHAR2(1) | A flag to show if this is the current or most recent assignment for the patient. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created the study site patient position record. |
| CREATION_TS | Not null | DATE | Date and time when this study site patient position record was created. |
| END_DATE | Null | DATE | The date that the patient assignment ended. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified this study site patient position record. |
| MODIFICATION_TS | Null | DATE | Date and time when this study site patient position record was last modified. |
Treatment patterns describe the medication to be given to a patient during the study.
Primary Key TREATMENT_PATTERN_ID
Unique Keys CLINICAL_STUDY_ID, NAME
CLINICAL_STUDY_ID, PATTERN_CODE
Related Tables and Foreign Keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| CLINICAL_STUDIES | CLINICAL_STUDY_ID | CLINICAL_STUDY_ID |
| PATTERNS | PATTERN_CODE | PATTERN_CODE |
| TREAT_ASSIGN_ALL_VIEW | TREATMENT_PATTERN_ID | TREATMENT_PATTERN_ID |
TREATMENT_PATTERNS Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| TREATMENT_PATTERN_ID | Not null | NUMBER(10) | A unique, system-generated ID for the treatment pattern. |
| CLINICAL_STUDY_ID | Not null | NUMBER(10) | The ID of the study to which the treatment pattern belongs. |
| CLINICAL_STUDY_VERSION_ID | Not null | NUMBER(10) | The ID of the study version to which the treatment pattern belongs. |
| PATTERN_CODE | Not null | VARCHAR2(15) | A code for the treatment. |
| NAME | Not null | VARCHAR2(60) | A name for the treatment pattern. |
| CREATED_BY | Not null | VARCHAR2(30) | User who created the treatment pattern. |
| CREATION_TS | Not null | DATE | Date and time when the treatment pattern was created. |
| NUMBER_OF_KITS_REQUIRED | Null | NUMBER(10) | The number of treatment assignments to be created for this treatment pattern. |
| NUMBER_OF_RPL_KITS_REQUIRED | Null | NUMBER(10) | The number of replacement treatment assignments to be created for this treatment pattern. |
| LABEL_CODE | Null | VARCHAR2(20) | A code to print on labels for this treatment pattern. |
| STARTING_KIT_CODE | Null | VARCHAR2(10) | A code from which to start coding the treatment assignments. |
| STARTING_RPL_KIT_CODE | Null | VARCHAR2(10) | A code from which to start coding the replacement treatment assignments. |
| MODIFIED_BY | Null | VARCHAR2(30) | User who last modified the treatment pattern. |
| MODIFICATION_TS | Null | DATE | Date and time when the treatment pattern was last modified. |
Validation Report Values (VRVs) record the values that are associated with a particular validation failure for a Validation Procedure. For Pre-3.1-style Procedures, every failure for a particular Procedure has the same set of values. For 3.1-style Procedures, each Procedure Detail specifies which values to report. The relation to a particular Procedure and Procedure Detail is determined via the relation to a particular discrepancy, which has the IDs of the Procedure and Procedure Detail.
The test table for VALIDATION_REPORTED_VALUES is VALIDATION_REPORTED_VALUEST.
Primary Key DISCREPANCY_ENTRY_ID, PROC_VARIABLE_NAME
Related tables and foreign keys
| Name of related table | Foreign key name in this table | Foreign key name in the related table |
|---|---|---|
| RESPONSES | RESPONSE_ID | RESPONSE_ID |
| RESPONSES | RESPONSE_ENTRY_TS | RESPONSE_ENTRY_TS |
| DISCREPANCY_ENTRIES | DISCREPANCY_ENTRY_ID | DISCREPANCY_ENTRY_ID |
VALIDATION_REPORTED_VALUES Column Descriptions
| Column Name | Null? | Type | Column Description |
|---|---|---|---|
| DISCREPANCY_ENTRY_ID | Not null | NUMBER(10) | The ID of the multivariate discrepancy entry with which this validation reported value is associated. |
| PROC_VARIABLE_NAME | Not null | VARCHAR2(30) | The Procedure variable name is unique within a particular Procedure and uniquely associates the validation reported value to a particular source within the Procedure definition. |
| RESPONSE_ID | Null | NUMBER(10) | If the VRV corresponds to a unique response, as contrasted to a calculated or auxiliary value, this points to the response that participated in the test. |
| RESPONSE_ENTRY_TS | Null | DATE | Part of foreign key to responses, above. Maintained by system when new versions of responses are created that do not change the value of the response, such as a change to a data comment. For 3.1-style Procedures, if the response value changes, but the value is only reported but not part of the test, the RESPONSE_ENTRY_TS continues to refer to the version of the response that existed at the time of discrepancy creation. |
| VALUE_TEXT | Null | VARCHAR2(200) | Value of validation reported value which was used in the test. This field is populated both for response-based values and other values. |
| DCF_INCLUDE | Null | VARCHAR2(1) | An indicator of whether to include the VRV in DCF. Value is Y to include, NULL if not included. |
|
 Copyright © 2001, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
