Oracle® Clinical

Stable Interface Technical Reference Manual

Release 5.0

E36999-01

August 2013

Oracle Clinical Stable Interface Technical Reference Manual, Release 5.0

E36999-01

Copyright © 2001, 2013, Oracle and/or its affiliates. All rights reserved.

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

Contents

List of Examples

List of Tables

Preface

- Audience

- Documentation Accessibility

- Finding Information and Patches on My Oracle Support

- Finding Oracle Documentation

- Related Documents

- Conventions

What's New

- New in Release 5.0

- New in Release 4.6.2

- New in Release 4.6

1 About the Stable Interface

- Overview

- Synonyms

- Accessing Oracle Clinical Data

- Table Queries

- Global Library and Study Objects

- Self-Journaling Tables

- Joining Tables

- About Timestamps

- Simple User Updates

- Deleting Records

- Determining Object Ownership

- Standard Attributes

- Audit Attributes

- STATUS_CODE

- RETIREMENT_REASON_TYPE_CODE

- STATUS_COMMENT_TEXT

- LAST_STATUS_CHANGE_TS

- DOMAIN

- _FLAG

- REPLICATION_IND

- Reference Codelists

- Question and Question Group Attribute Hierarchy

2 Table Definitions

- About the Stable Interface Tables

- ACTUAL_EVENTS(T)

- BATCH_DM_RUNS

- CLINICAL_PLANNED_EVENTS

- CLINICAL_STUDIES

- CLINICAL_SUBJECTS

- DATA_CLARIFICATION_FORMS

- DCF_PAGES

- DCI_BOOK_DCI_CONSTRAINTS

- DCIS

- DCI_BOOKS

- DCI_BOOK_PAGES

- DCI_BOOK_PHYSICAL_PAGES

- DCI_FORM_VERSIONS

- DCI_MODULES

- DCI_MODULE_PAGES

- DCMS

- DCM_QUESTIONS

- DCM_QUES_REPEAT_DEFAULTS

- DCM_SCHEDULES

- DISCREPANCY_ENTRIES(T)

- DISCREPANCY_ENTRY_REVIEW_HIST(T)

- DISCRETE_VALUES

- DISCRETE_VALUE_GROUPS

- FORM_LAYOUT_TEMPLATES

- LABS

- LAB_TEST_QUESTION_UNITS

- LAB_RANGE_SUBSETS

- LAB_UNITS

- LAB_UNIT_CONVERSIONS

- MV_EXECUTION_LOG

- OCL_INVESTIGATORS(T)

- OCL_ORGANIZATION_UNITS

- OCL_PROGRAMS

- OCL_PROJECTS

- OCL_SITES

- OCL_STUDIES

- OCL_STUDY_SITES

- OCL_STUDY_SITE_ROLES(T)

- ORACLE_ACCOUNTS

- PATIENT_POSITIONS(T)

- PATIENT_STATUSES

- PLANNED_STUDY_INTERVALS

- PREFERRED_LAB_UNITS

- PROCEDURES

- PROCEDURE_DETAILS

- PROCEDURE_QUESTION_GROUPS

- QUESTIONS

- QUESTION_CATEGORY_RELATIONS

- QUESTION_GROUPS

- QUESTION_GROUP_QUESTIONS

- RANGES

- RDCI_HISTORY(T)

- RECEIVED_DCIS(T)

- RECEIVED_DCMS(T)

- RECEIVED_PAGES(T)

- RECEIVED_PAGE_HISTORY(T)

- REFERENCE_CODELISTS

- REFERENCE_CODELIST_VALUES

- REGIONS

- RESPONSES(T)

- STUDY_SITE_PATIENT_POSITIONS(T)

- TREATMENT_PATTERNS

- VALIDATION_REPORTED_VALUES(T)

3 Flexible Studies Tables

- DCI_BOOK_RULES

- DCI_BOOK_RULE_TGT_INTERVALS

- DCI_BOOK_RULE_TGT_DCIS

- PP_EXPECTED_INTERVALS(T)

- PP_EXPECTED_INTERVALS$JN

- PP_EXPECTED_CPES(T)

- PP_EXPECTED_CPES$JN

- PP_EXPECTED_DCIS(T)

- PP_EXPECTED_DCIS$JN

4 View Definitions

- About the Stable Interface Views

- ACTUAL_EVENTSV(T)

- CHECK_RANDOMIZATIONS

- DISCREPANCY_MANAGEMENT(T)

- FULL_STUDIES

- PATIENTS

- PROGRAM_SUBSTANCES

- TREAT_ASSIGN_ALL_VIEW

5 Entity Relationship Diagram

A Journal and History Tables

- Self-Journaling tables

- Journal Table Columns

- Tables First Audited in Oracle Clinical 4.5

- History Tables

- CLINICAL_STUDY_HISTORY

- DCMS_FFL_XML_HIST

- FORM_LAYOUT_TEMPLATES_XML_HIST

- PATIENT_POSITIONS_HISTORY

- RECEIVED_PAGES_HISTORY

List of Examples

- 1-1 Joining Self-Journaling Tables

List of Tables

- 1-1 Exceptions to standard synonym naming convention

- A-1 New Journal and History Tables For Oracle Clinical 4.5

Preface

This book contains information about the Oracle Clinical data model.

This preface contains the following topics:

- Audience

- Documentation Accessibility

- Finding Information and Patches on My Oracle Support

- Finding Oracle Documentation

- Related Documents

- Conventions

Audience

This manual is intended for users who build applications that use Oracle Clinical data for reporting purposes.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.

Finding Information and Patches on My Oracle Support

Your source for the latest information about Oracle Clinical is Oracle Support's self-service Web site My Oracle Support (formerly MetaLink).

Before you install and use Oracle Clinical, always visit the My Oracle Support Web site for the latest information, including alerts, White Papers, installation verification (smoke) tests, bulletins, and patches.

Creating a My Oracle Support Account

You must register at My Oracle Support to obtain a user name and password account before you can enter the Web site.

To register for My Oracle Support:

1. Open a Web browser to https://support.oracle.com.

2. Click the Register link to create a My Oracle Support account. The registration page opens.

3. Follow the instructions on the registration page.

Signing In to My Oracle Support

To sign in to My Oracle Support:

1. Open a Web browser to https://support.oracle.com.

2. Click Sign In.

3. Enter your user name and password.

4. Click Go to open the My Oracle Support home page.

Finding Information on My Oracle Support

There are many ways to find information on My Oracle Support.

Searching by Article ID

The fastest way to search for information, including alerts, White Papers, installation verification (smoke) tests, and bulletins is by the article ID number, if you know it.

To search by article ID:

1. Sign in to My Oracle Support at https://support.oracle.com.

2. Locate the Search box in the upper right corner of the My Oracle Support page.

3. Click the sources icon to the left of the search box, and then select Article ID from the list.

4. Enter the article ID number in the text box.

5. Click the magnifying glass icon to the right of the search box (or press the Enter key) to execute your search.
The Knowledge page displays the results of your search. If the article is found, click the link to view the abstract, text, attachments, and related products.

Searching by Product and Topic

You can use the following My Oracle Support tools to browse and search the knowledge base:

- Product Focus — On the Knowledge page under Select Product, type part of the product name and the system immediately filters the product list by the letters you have typed. (You do not need to type "Oracle.") Select the product you want from the filtered list and then use other search or browse tools to find the information you need.

- Advanced Search — You can specify one or more search criteria, such as source, exact phrase, and related product, to find information. This option is available from the Advanced link on almost all pages.

Finding Patches on My Oracle Support

Be sure to check My Oracle Support for the latest patches, if any, for your product. You can search for patches by patch ID or number, or by product or family.

To locate and download a patch:

1. Sign in to My Oracle Support at https://support.oracle.com.

2. Click the Patches & Updates tab. The Patches & Updates page opens and displays the Patch Search region. You have the following options:

- In the Patch ID or Number field, enter the number of the patch you want. (This number is the same as the primary bug number fixed by the patch.) This option is useful if you already know the patch number.

- To find a patch by product name, release, and platform, click the Product or Family link to enter one or more search criteria.

3. Click Search to execute your query. The Patch Search Results page opens.

4. Click the patch ID number. The system displays details about the patch. In addition, you can view the Read Me file before downloading the patch.

5. Click Download. Follow the instructions on the screen to download, save, and install the patch files.

Finding Oracle Documentation

The Oracle Web site contains links to all Oracle user and reference documentation. You can view or download a single document or an entire product library.

Finding Oracle Health Sciences Documentation

To get user documentation for Oracle Health Sciences applications, go to the Oracle Health Sciences documentation page at:

http://www.oracle.com/technetwork/documentation/hsgbu-clinical-407519.html

Note:

Always check the Oracle Health Sciences Documentation page to ensure you have the latest updates to the documentation.

Finding Other Oracle Documentation

To get user documentation for other Oracle products:

1. Go to the following Web page:
http://www.oracle.com/technology/documentation/index.html

Alternatively, you can go to http://www.oracle.com, point to the Support tab, and then click Documentation.

2. Scroll to the product you need and click the link.

3. Click the link for the documentation you need.

Related Documents

This section lists the documents in the Oracle Clinical documentation set, followed by their part number. The most recent version of each guide is posted on the Oracle Web site; see "Finding Oracle Documentation".

- Oracle Clinical Installation Guide (Part E36499)

- Oracle Clinical Administrator's Guide (Part E36994)

- Oracle Clinical Getting Started (Part E36995)

- Oracle Clinical Creating a Study (Part E36996)

- Oracle Clinical Conducting a Study (Part E36997)

- Oracle Clinical Application Programming Interface Guide (Part E36998)

- Oracle Clinical Remote Data Capture Onsite Administrator's Guide (Part E37004)

- Oracle Clinical Remote Data Capture Onsite User's Guide (Part E37003)

- Oracle Clinical Remote Data Capture Classic Data Entry User's Guide (Part E37005)

- Oracle Clinical, Oracle Clinical Remote Data Capture, and Oracle Thesaurus Management System Security Configuration Guide(Part E37000)

The release notes (Article ID 1537709.1) and the release content document are available on My Oracle Support. See Finding Information and Patches on My Oracle Support for more information.

In addition, Oracle Clinical customers can request a copy of the Oracle Clinical Stable Interface Technical Reference Manual from Oracle Support.

Conventions

The following text conventions are used in this document:

Convention

Meaning

boldface

Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary.

italic

Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values.

monospace

Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter.

What's New

This section describes the changes and additions to the stable interface in recent releases.

New in Release 5.0

No changes were made to the stable interface in Release 5.0.

New in Release 4.6.2

The only change to the stable interface in Oracle Clinical 4.6.2 is that a column, INFORMED_CONSENT_DATE, was added to PATIENT_POSITIONS and PATIENT_POSITIONS_HISTORY.

New in Release 4.6

This section describes the changes to the Oracle Clinical Stable Interface for Release 4.6. It includes these topics:

- Changes to Synonym RDCI_HISTORY_V45

- New Synonyms Introduced in Release 4.6

- "New Columns Added to Existing Tables in Release 4.6"

Changes to Synonym RDCI_HISTORY_V45

The RDCI_HISTORY_V45 synonym changed in release 4.6. The data it queries excludes records where TRANS_TYPE = AUDIT (see Bug 10176208) or TRANS_TYPE is like SYS%, RETAIN%, REVERSE%. The synonym should include AUDIT records and should exclude SYS%, RETAIN% and REVERSE% records only if you have not applied patches 4.5.3.11 and 4.5.3.12 before upgrading to Release 4.6.

New Synonyms Introduced in Release 4.6

The Oracle Clinical Release 4.6 Stable Interface adds the new DCI and Patient Position tables that support flexible study design. This is the list of new tables and corresponding synonyms. See Chapter 3, "Flexible Studies Tables" for details.

SYNONYM_NAME

TABLE_NAME

DCI_BOOK_RULES_V46

DCI_BOOK_RULES

DCI_BOOK_RULE_TGT_DCIS_V46

DCI_BOOK_RULE_TGT_DCIS

DCI_BOOK_RULE_TGT_INTERVAL_V46

DCI_BOOK_RULE_TGT_INTERVALS

PP_EXPECTED_CPEST_V46

PP_EXPECTED_CPEST

PP_EXPECTED_CPES_V46

PP_EXPECTED_CPES

PP_EXPECTED_DCIST_V46

PP_EXPECTED_DCIST

PP_EXPECTED_DCIS_V46

PP_EXPECTED_DCIS

PP_EXPECTED_INTERVALST_V46

PP_EXPECTED_INTERVALST

PP_EXPECTED_INTERVALS_V46

PP_EXPECTED_INTERVALS

New Columns Added to Existing Tables in Release 4.6

Oracle modified the following stable interface tables and their synonyms for Release 4.6, and their synonyms for previous releases in a 4.6 database.

DCI_BOOKS

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 run.

DISCREPANCY_ENTRIES and DISCREPANCY_ENTRIEST

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').

DCM_QUESTIONS

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.

PATIENT_POSITIONST and PATIENT_POSITIONS

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.

1 About the Stable Interface

The term stable interface in this document refers to a subset of the Oracle Clinical database that appears to remain unchanged, or stable, through at least two subsequent releases of Oracle Clinical. This subset consists of a set of synonyms that point to the tables and views. (See "Synonyms".)

If you are building applications that read data from Oracle Clinical, the stable interface provides:

- easier access to data held within Oracle Clinical

- smooth transition from one version of Oracle Clinical to the next

- upgrade timing flexibility

The term stable views in this document refers to the views obtained via the stable interface; it does not refer to the stable views obtained in the Data Extract subsystem of Oracle Clinical.

The stable interface supports read-only access to data. Updates to the Oracle Clinical database should always be performed in the Oracle Clinical client.

Note:

Tables and views not documented as part of the stable interface are not part of it and are therefore subject to change without notice. External use of the tables not included in the stable interface is not supported by Oracle Corporation unless a separate agreement is reached with Oracle Consulting Services.

Overview

The stable interface allows external reports and applications to run against Oracle Clinical without change when Oracle Clinical is upgraded. By following some general rules for accessing data within Oracle Clinical, it is not necessary to simultaneously upgrade all applications that read from Oracle Clinical. Instead, you may choose when to migrate to the new layout. Each new release of Oracle Clinical includes an upgrade and instructions to upgrade the stable interface.

You do not have to upgrade to the newest version of the stable interface. Each version of the stable interface provides a set of synonyms that point to a table or view. In most cases, the synonym that reflects an earlier release, points to the same underlying table or view as the synonym in the current release. For example, in Oracle Clinical 5.0, the synonyms ACTUAL_EVENTS_V40, ACTUAL_EVENTS_V45, ACTUAL_EVENTS_V46 and ACTUAL_EVENTS_V50 all point to the same table in the database.

You can choose to continue to use the existing stable interface synonyms or upgrade to the new synonyms. We recommend that you use the new synonyms for new interfacing applications because they will be supported through one more release of Oracle Clinical than the older synonyms, but the older synonyms will work as well.

All synonyms correspond exactly between the different versions except for the RDCI_HISTORY Synonym which is explained below.

RDCI_HISTORY Synonym

RDCI_HISTORY_V46 and RDCI_HISTORY_V50 synonyms both refer to the RDCI_HISTORY table. The RDCI_HISTORY_V45 synonym changed in release 4.6.

If you applied patch 4.5.3.11 or a superseding patch before upgrading to 5.0, the RDCI_HISTORY_V45 synonym also refers to the RDCI_HISTORY table as well. If you did not apply these patches, the synonym refers to a view which excludes records where TRANS_TYPE = AUDIT or TRANS_TYPE is like SYS%, RETAIN%, REVERSE%.

While the stable interface hides changes to the database layout or business meaning in the release, Oracle cannot guarantee that exactly the same business information is provided. For example, if the length of a field is increased in a new version of Oracle Clinical, the stable interface truncates the contents of the field and provides existing users of the stable interface with a field of the original length. This truncation means that customers who want to view all the information in a new field must upgrade to the latest version of the stable interface.

Synonyms

The stable interface includes a set of synonyms that point to the underlying Oracle Clinical tables and views. All programs that access Oracle Clinical data should use these synonyms.

Synonym names typically consist of the table name concatenated with a version number. For example, the synonym for the CLINICAL_STUDIES table in Oracle Clinical is CLINICAL_STUDIES_V40 in the stable interface for version 4.0. These synonyms are supported in this release of the stable interface.

Some synonyms follow another naming convention, or have retained the naming convention of the previous release of the stable interface. Refer to Table 1-1 for the exceptions to the standard synonym naming convention.

Table 1-1 Exceptions to standard synonym naming convention

Table Name

Synonym

CLINICAL_PLANNED_EVENTS

LIVE_CLINICAL_PLANNED_EVE_V40

DISCREPANCY_ENTRY_REVIEW_HIST

DISCREPANCY_ENTRY_REVIEW_V40

DISCREPANCY_ENTRY_REVIEW_HISTT

DISCREPANCY_ENTRY_REVIEWT_V40

PATIENT_POSITIONS

LIVE_PATIENT_POSITIONS_V40

PLANNED_STUDY_INTERVALS

LIVE_PLANNED_STUDY_INTERV_V40

QUESTION_CATEGORY_RELATIONS

QUESTION_CATEGORY_RELATIO_V40

RESPONSES

RESPONSE_ATTRIBUTES_V40

RESPONSEST

RESPONSE_ATTRIBUTEST_V40

STUDY_SITE_PATIENT_POSITIONS

STUDY_SITE_PATIENT_POSIT_V40

STUDY_SITE_PATIENT_POSITIONST

STUDY_SITE_PATIENT_POSITT_V40

TREATMENT_PATTERNS

LIVE_TREATMENT_PATTERNS_V40

VALIDATION_REPORTED_VALUES

VALIDATION_REPORTED_VALU_V40

VALIDATION_REPORTED_VALUEST

VALIDATION_REPORTED_VALUT_V40

Subsequent releases of Oracle Clinical will include new synonyms that follow both naming conventions described above. Version numbers are updated to reflect the version number for the most recent release.

Accessing Oracle Clinical Data

This section describes general rules and techniques for accessing data within Oracle Clinical.

Table Queries

Use the *SELECT syntax to query a table only when you are certain of the sequence of columns within the table. If you do not know the order of the columns, it is recommended that you avoid the *SELECT syntax and instead include a specific column name in the query, for example:

SELECT Region_Code

Note:

The sequence of columns within a table is not guaranteed. As Oracle Clinical is upgraded, the column sequence may change. In addition, in the table and column definitions, some columns are for information only. Although Oracle has defined the expected meaning for these columns, the text the user will add to these columns cannot be guaranteed. For example, a column named Region could be populated with tax code information.

Global Library and Study Objects

A number of objects in Oracle Clinical are associated with two levels: the Global Library level and the Study level. Internally, both levels are held in the same tables. Objects belonging to an individual study include a clinical study ID; objects in the Global Library have a clinical study ID of 0. The objects that follow this convention are:

- DCIs

- DCMs

- Procedures

- View definitions

To select objects in the Global Library, select where clinical study ID = 0. To select objects for a specific study, use the appropriate clinical study ID.

Self-Journaling Tables

Oracle Clinical saves a copy of a self-journaling table each time you modify the table. This generated history enables you to track changes in data over time. RESPONSES, RECEIVED_DCMS, and RECEIVED_DCIS are all self-journaling tables.

This section describes joining tables and updating them, including deleting records from tables. For a list of the journal tables added to this release, see Appendix A, "Journal and History Tables".

Joining Tables

When self-journaling tables are joined to each other, they must be individually restricted to the record instance that existed at the time you want to view the data. You do not join on the entry timestamp, even though it is part of the concatenated primary key of the tables.

Example 1-1 Joining Self-Journaling Tables

The following chart illustrates how to join self-journaling tables. Tables A and B are self-journaling tables that have a foreign key relationship from B back up to A. The following state could exist after one year.

Table A

Table B

ID

Entry Timestamp

ID

Entry Timestamp

Foreign Key

123

01-JAN-1998

789

10-JAN-1998

123

123

01-APR-1998

789

15-MAR-1998

123

123

10-JUN-1998

789

20-SEP-1998

123

123

20-DEC-1998

All three records in Table B have a foreign key that points to all four records in Table A. The following SELECT statement brackets the date 01-MAR-1998, selecting where:

- Timestamps for Table A cover 01-MAR-2009.

- Timestamps for Table B cover 01-MAR-2009.

- The foreign key links B to A.

SELECT A.*, B.*
FROM A, B  WHERE A.ID = B.FOREIGN_KEY
AND A.ENTRY_TIMESTAMP <=
TO_DATE('01-MAR-1998 12:00:00',  'DD-MON-YYYY HH24:MI:SS')
AND A.END_TS >
TO_DATE('01-MAR-1998 12:00:00',  'DD-MON-YYYY HH24:MI:SS')    AND
B.ENTRY_TIMESTAMP <=
TO_DATE('01-MAR-1998 12:00:00',  'DD-MON-YYYY HH24:MI:SS')
AND B.END_TS >
TO_DATE('01-MAR-1998 12:00:00',  'DD-MON-YYYY HH24:MI:SS)

This action retrieves one record from each table as it appeared on the requested date.

About Timestamps

Oracle Clinical timestamps are precise to the second. A record may be updated more than once during a day. To ensure you select a specific record, choose an explicit time. For example, if you want to view a record from June 20, 1997, decide if you want to view the record at the start, middle, or end of the day. Alternatively, you can select without a time and manage the possibility of multiple results.

Many Oracle Clinical processes use the timestamp of the start of a successfully completed batch validation process as a discrete point of time to view data. These time points are recorded in the table BATCH_DM_RUNS.

To access the current record:

SELECT ...   where end timestamp = to_date(3000000,J)   and ...

To access data as it existed at a given point in time:

SELECT ...   where entry timestamp <= the required date   and end_timestamp > required date    and ...

Simple User Updates

For tables containing the clinical data in Oracle Clinical, simple user updates are converted into an update and an insertion. Internally, Oracle Clinical performs the following steps:

1. Sets an end timestamp on the current record.

2. Inserts a new version of the record with the changes.

The new record has the same ID as the original record. In addition, it has an entry timestamp equivalent to the end timestamp on the previous record and an end timestamp in the year 3,000,000.

Deleting Records

There are two ways to delete records from a self-journaling table.

Soft Delete A record is deleted from a self-journaling table by means of Key Changes or another standard data entry process. The end timestamp of the current record is updated to the current timestamp; no new version of the record is created.

Hard Delete A record is deleted from a self-journaling table by means of the Study Data Deletion utility; the record is deleted from the database with no audit trail.

Determining Object Ownership

Most objects within Oracle Clinical are regarded as being owned by a particular database instance. Other instances can have read-only copies, but they will only be as up to date as the last time replication was executed.

If you require the most recent state of a particular type of data, you should access it at the instance that owns it. This rule applies to the following types of data:

- Global Library

- study designs and definitions at individual locations

- Patient Positions and their received data

- Sites

- Investigators

Only one location in a replication installation owns the Global Library. The Global Library owning location is listed in a reference codelist called OCL_INSTALLATION. For the short value GLIB_LOCATION, the long value (for example, LONDON) is the library owning location.

In the following occurrences, the owning location is held in the OWNING LOCATION column:

Study designs and definitions  Ownership is controlled by the owning location of the clinical studies record in the CLINICAL_STUDIES table.

Patient Positions and their received data  Ownership is controlled by the owning location on the patient positions record in the PATIENT_POSITIONS table. This means that although a study has only one owning location (for example, LONDON), patient records in the study may have separate owning locations (for example, NEW YORK).

Sites and Investigators  Each has a unique owning location.

Note:

The owning location for studies and patients can be changed from within Oracle Clinical up to the point when all clinical data has been collected.

To find out where you are within Oracle Clinical, select the short value LOCATION_CODE on the reference codelist called OCL_STATE. The long value indicates where you are.

Standard Attributes

Many tables have one or more of the following standard attributes.

Audit Attributes

Every record within Oracle Clinical, except for the self-journaling tables, comes with the following four standard audit attributes:

CREATION_TS: the date the record was created.

CREATED_BY: person who created the record.

MODIFICATION_TS: date the record was last modified

MODIFIED_BY: person who last modified the record.

STATUS_CODE

Many objects in Oracle Clinical include a status. The STATUS_CODE is always P (Provisional), A (Active), or R (Retired). For a given requirement you must choose an appropriate status. If the status is:

- P or Provisional, the object may be changed, deleted, or used in a limited context. In addition, provisional objects may be used for test data entry.

- A or Active, very limited changes are allowed; this object may not be deleted. The object may be used for Production data entry.

- R or Retired, no changes are allowed; this object may not be deleted nor may it be used. Retired objects are retained to support existing data.

RETIREMENT_REASON_TYPE_CODE

Records that have a STATUS_CODE of R also have a code to indicate why the status was set to retired.

STATUS_COMMENT_TEXT

Records that have a STATUS_CODE include free form text to describe why the status was changed.

LAST_STATUS_CHANGE_TS

Records that have a STATUS_CODE include a timestamp that indicates when the status was changed.

DOMAIN

The Global Library is partitioned into domains. Object names within domains are unique; across domains they need not be unique. Partitioning allows for objects with the same name to have different definitions. Because, for example, an Adverse Event DCM could have several definitions, it is necessary to specify an Adverse Event DCM as being in a domain called, for example, STANDARD; it is not sufficient to specify the Adverse Event DCM without its domain.

Users may be limited to accessing certain domains, depending upon their privileges.

In tables containing both Global Library and study information, the name of the domain for study information is the same as the study, because names of objects within a study must be unique.

_FLAG

Attributes that end in _FLAG always have a value of Y or N.

REPLICATION_IND

Most replicated tables come with an indicator to show that the record was created by replication. Tables in the Design subsystem do not have a replication indicator.

Reference Codelists

Reference codelists appear to end users as simple lookup tables. However, within Oracle Clinical they are all stored within the following two tables.

REFERENCE_CODELISTS Holds a list of codelists and some of their basic attributes.

REFERENCE_CODELIST_VALUES Holds the actual values for all lists. The value is held in a column called Short Value. The short value is guaranteed to be unique only within the context of a particular codelist.

Most reference codelists are used to validate code values and to supply descriptions of the codes for reports and screens. However, in a few reference codelists the long value has a processing significance.

Question and Question Group Attribute Hierarchy

Some attributes for Questions and Question Groups are repeated at different levels. Oracle Clinical includes business rules that control the field values that may be overwritten as well as the valid override values. For example, the maximum length of a Question could be increased at the study level. The basic principles are that:

- The highest order contains the most general values.

- Values become more restricted at the study level.

- Lower-level values override the values from the level above.

Attributes at the higher order are used as defaults when the object is used at the next level. If no lower-level value exists, the value is derived from the higher-level value.

In the following list, Questions is the highest order, so Question Group Questions default from and are constrained by Questions, while DCM Question Group Questions default from and are constrained by Question Group Questions:

- Questions

- Question Group Questions

- DCM Question Group Questions

In this list, Question Groups is the highest order, so DCM Question Groups default from and are constrained by Question Groups:

- Question Groups

- DCM Question Groups

2 Table Definitions

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."

About the Stable Interface Tables

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.

ACTUAL_EVENTS(T)

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.

Primary key ACTUAL_EVENT_ID

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.

BATCH_DM_RUNS

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

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.

Primary Key CLIN_PLAN_EVE_ID

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_STUDIES

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.

Primary Key CLINICAL_STUDY_ID

Unique Key STUDY

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

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

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

Primary Key DCF_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

PATIENT-POSITIONS

PATIENT_ID

PATIENT_POSITION_ID

OCL_INVESTIGATORS

INVESTIGATOR_ID

INVESTIGATOR_ID

CLINICAL_STUDIES

CLINICAL_STUDY_ID

CLINICAL_STUDY_ID

Column Descriptions

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.

DCF_PAGES

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.

Primary Key DCF_PAGE_ID

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

DCF_PAGES Column Descriptions

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_DCI_CONSTRAINTS

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.

DCIS

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.

Primary Key DCI_ID

Unique Keys NAME, DOMAIN

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

DCIS Column Descriptions

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.

DCI_BOOKS

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.

Primary Key DCI_BOOK_ID

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

DCI_BOOKS Column Descriptions

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

DCI_BOOK_PAGES

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.

Primary Key DCI_BOOK_PAGE_ID

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.

DCI_BOOK_PHYSICAL_PAGES

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_FORM_VERSIONS

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.

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.

DCI_MODULES

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.

Primary Key DCI_MODULE_ID

Unique Keys

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

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.

DCMS

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

DCMS Column Descriptions

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:

- DYNAMIC – Use Data Entry preference

- EUROPEAN – Use DD-MM-YYYY

- SWEDISH – Use YYYY-DD-MM

- US – Use MM-DD-YYYY

- STANDARD – Use DD-MON-YYYY

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.

DCM_QUESTIONS

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.

DCM_QUES_REPEAT_DEFAULTS

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.

DCM_SCHEDULES

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.

DISCREPANCY_ENTRIES(T)

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')

DISCREPANCY_ENTRY_REVIEW_HIST(T)

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:

- DATA CHANGE – Data change

- VALID CHANGE (Validation change)

- VALID RETIRED – Validation retired

- DVG

- DVG SUBSET – Discrete value group subset

- THESAURUS (Thesaurus DVG)

- DATA REMOVED – RDCI/RDCM/response was deleted

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

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

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.

FORM_LAYOUT_TEMPLATES

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.

Primary Key FLT_ID

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.

LABS

A lab record contains basic details about a laboratory being used for clinical studies.

Primary Key LAB_ID

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

LABS Column Descriptions

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.

LAB_TEST_QUESTION_UNITS

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.

LAB_RANGE_SUBSETS

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.

LAB_UNITS

This table holds lists of units used to measure lab results.

Primary Key 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

PREFERRED_LAB_UNITS

LAB_UNIT_CODE

LAB_UNIT_CODE

LAB_UNITS Column Descriptions

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.

LAB_UNIT_CONVERSIONS

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.

MV_EXECUTION_LOG

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.

Primary Key JOB_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

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(T)

OCL_INVESTIGATORS records basic details about an investigator. The test table for OCL_INVESTIGATORS is OCL_INVESTIGATORST.

Primary Key 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_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.

OCL_ORGANIZATION_UNITS

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.

OCL_PROGRAMS

Programs are groups of studies that are usually related to the same compound.

Primary Key PROGRAM_CODE

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.

OCL_PROJECTS

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.

Primary Key PROJECT_CODE

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.

OCL_SITES

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.

Primary Key SITE_ID

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

OCL_SITES Column Descriptions

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.

OCL_STUDIES

This table contains a list of studies that are being performed or that are planned to be performed.

Primary Key TASK_ID

Unique Keys

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.

OCL_STUDY_SITES

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.

OCL_STUDY_SITE_ROLES(T)

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.

ORACLE_ACCOUNTS

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(T)

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.

PATIENT_STATUSES

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

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.

Primary Key PLAN_STUDY_INT_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

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.

PREFERRED_LAB_UNITS

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.

PROCEDURES

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

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

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:

- VISIT ASC ASC (RECEIVED_DCMS.VISIT_NUMBER, ASC; RECEIVED_DCMS.SUBEVENT_NUMBER, ASC)

- VISIT ASC DESC (RECEIVED_DCMS_VISIT_NUMBER ASC, RECEIVED_DCMS.SUBEVENT_NUMBER DESC)

- VISIT DESC ASC (RECEIVED_DCMS.VISIT_NUMBER DESC, RECEIVED_DCMS.SUBEVENT_NUMBER ASC)

- VISIT DESC DESC (RECEIVED_DCMS.VISIT_NUMBER DESC, RECEIVED_DCMS.SUBEVENT_NUMBER DESC)

- DATE ASC ASC (RECEIVED_DCMS.DCM_DATE ASC, RECEIVED_DCMS.DCM_TIME ASC)

- DATE ASC DESC (RECEIVED_DCMS.DCM_DATE ASC, RECEIVED_DCMS.DCM_TIME DESC)

- DATE DESC ASC (RECEIVED_DCMS.DCM_DATE DESC, RECEIVED_DCMS.DCM_TIME ASC)

- DATE DESC DESC (RECEIVED_DCMS.DCM_DATE DESC, RECEIVED_DCMS.DCM_TIME DESC)

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

Questions are the foundation of data definition. They represent single pieces of information that can be captured and recorded.

Primary Key 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_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

QUESTIONS Column Descriptions

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.

QUESTION_CATEGORY_RELATIONS

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

Question groups are sets of related questions.

Primary Key QUESTION_GROUP_ID

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.

QUESTION_GROUP_QUESTIONS

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

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.

Primary Key RANGE_ID

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

RANGES Column Descriptions

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.

RDCI_HISTORY(T)

The RDCI_HISTORY table stores history records for three types of transaction:

- Approvals and Verifications of Records in RDC

- Records of the Reason for Change when Changing an RDCI or RDCM

- Populating RDCI_HISTORY from the API

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().

RECEIVED_DCIS(T)

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:

- RECEIVED – Received

- PASS 1 STARTED – 1st Pass Started

- PASS 1 COMPLETE – 1st Pass Complete

- PASS 2 STARTED – 2nd Pass Started

- PASS 2 COMPLETE – 2nd Pass Complete

- PASS 2 PENDING – Awaiting reconciliation of Pass 1 and Pass 2

- REMOVED – Removed

- BATCH – Batch Loaded

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.

RECEIVED_DCMS(T)

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:

RECEIVED – Received

PASS 1 STARTED – 1st Pass Started

PASS 1 COMPLETE – 1st Pass Complete

PASS 2 STARTED – 2nd Pass Started

PASS 2 COMPLETE – 2nd Pass Complete

PASS 2 PENDING – Awaiting reconciliation of Pass 1 and Pass 2

REMOVED – Removed

BATCH – Batch Loaded

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:

UNKNOWN – No lab specified

CRITERION – Defaulted via lab assignment criteria

LOGIN – Manually entered by data entry

SYSTEM – Not currently used

USER – Explicitly entered through RDCM screen in LAC Maintenance

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.

RECEIVED_PAGES(T)

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.

Primary Key RECEIVED_PAGE_ID

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.

RECEIVED_PAGE_HISTORY(T)

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

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:

- LOCAL – User-defined values for the instance; for example, printers

- INSTALLATION – User-defined for all instances that share the same Global Library

- SYSTEM – Oracle-defined values

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

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

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.

Primary Key REGION_ID

Unique Key REGION_CODE

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

REGIONS Column Descriptions

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(T)

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

RESPONSES Column Descriptions

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:

O – Open, status CURRENT, no resolution

I – Irresolvable, queried but no further resolution possible

K – Confirmed by query

C – Closed, status OBSOLETE — due to either a data change or validation change

N – No discrepancy

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:

- Datatype discrepancies store the value here with the value text null.

- Length discrepancies store the full value here with the value text containing null for numbers and containing the text truncated to the DCM question length for characters.

STUDY_SITE_PATIENT_POSITIONS(T)

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

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_REPORTED_VALUES(T)

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.

3 Flexible Studies Tables

Oracle Clinical Release 4.6 introduced an enhanced DCI Book form that allows you to create rules that define the required decision points in a flexible study.

The following tables hold the rule definitions for enhanced DCI Books:

"DCI_BOOK_RULES"

"DCI_BOOK_RULE_TGT_INTERVALS"

"DCI_BOOK_RULE_TGT_DCIS"

DCI_BOOK_RULES is the master table. DCI_BOOK_RULE_TGT_INTERVALS and DCI_BOOK_RULE_TGT_DCIS are child tables. However, DCI_BOOK_RULE_TGT_INTERVALS and DCI_BOOK_RULE_TGT_DCIS are mutually exclusive in their relationship to DCI_BOOK_RULES: For a given RULE_ID in DCI_BOOK_RULES, the child rows are either in DCI_BOOK_RULE_TGT_INTERVALS or DCI_BOOK_RULE_TGT_DCIS. Which one is present is determined by the value of RULE_TYPE in the DCI_BOOK_RULES table.

The following tables for Patient Positions also support flexible studies:

"PP_EXPECTED_INTERVALS(T)"

"PP_EXPECTED_INTERVALS$JN"

"PP_EXPECTED_CPES(T)"

"PP_EXPECTED_CPES$JN"

"PP_EXPECTED_DCIS(T)"

"PP_EXPECTED_DCIS$JN"

Each of these Patient Position tables has a foreign key to PATIENT_POSITIONS and to the object of the expectedness they track: PP_EXPECTED_INTERVALS to PLAN_INTERVAL_ID, PP_EXPECTED_CPES to CLIN_PLAN_EVE_ID, and PP_EXPECTED_DCIS to DCI_ID. The PP_EXPECTED_DCIS also has a foreign key to the Clinical Planned Event (CPE) because DCIs are expected at a CPE. There is a row in the tables if the object is expected and none if it is not expected — with the caveat that intervals and CPEs will have a row present even if they are not expected if the BYPASSED_FLAG field indicates that they have been skipped due to a Bypass To rule.

Note:

Tables that have test data counterparts are designated by a (T) at the end of the table's title. PP_EXPECTED_INTERVALS(T) therefore describes the production table PP_EXPECTED_INTERVALS and the test table PP_EXPECTED_INTERVALST.

DCI_BOOK_RULES

DCI Book rules are created within a DCI Book for a flexible study. Each rule has a set of trigger conditions and a set of target conditions. The target or targets of a rule are conditional and only become expected when the trigger conditions are met. The DCI_BOOK_RULES table contains the trigger conditions (the data definition and data value or values), for a rule. A trigger may be the existence of a particular received DCI at a Clinical Planned Event, or it may be as specific as a particular response to a question in a qualified DCI module at a Clinical Planned Event. A DCI Book rule applies for the selected DCI Trigger at every CPE(s) at which it exists in the DCI Book.

- Interval rules specify the conditions for the CPEs in an entire study Interval (or Intervals) to be required. There are two different types of Interval rule; the Enable rule, which sets the DCI – CPE combinations within the Interval as expected, or the Bypass To rule, which also causes all DCI – CPE combinations after the CPE of the data entry point — including DCI-CPE in the rest of the current interval — up to the target Interval to be non-expected.

- DCI rules specify the criteria for a DCI to be required There are two different types of DCI rule; the 'Enable Within CPE' type applies to the target DCI(s) only at the same CPE as defined in the Trigger, and the 'Enable Across CPEs' applies to the target DCI(s) whenever it is collected in the DCI Book.

The details of the targets for a DCI Book rule are found in either the DCI_BOOK_RULE_TGT_INTERVALS table or the DCI_BOOK_RULE_TGT_DCIS table.

Primary Key RULE_ID

DCI_BOOK_RULES 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_RULES Columns

Column

Null?

Type

Column Description

RULE_ID

Not null

NUMBER(10)

The rule ID value.

DCI_BOOK_ID

Not null

NUMBER(10)

The ID of the DCI Book.

RULE_TYPE

Not null

VARCHAR2(20)

Specifies whether the rule triggers Intervals or DCIs. Values are INTERVAL or DCI.

RULE_TARGET_ACTION

Not null

VARCHAR2(20)

Specifies the rule subtype. For Interval rules, the subtypes are Enable and Bypass To. For DCI rules, the subtypes are Across CPEs and Within CPE.

TRIGGER_DCI_ID

Not null

NUMBER(10)

ID of the DCI containing the data evaluated by the rule

TRIGGER_DCM_ID

Null

NUMBER(10)

ID of the DCM within the trigger DCI containing the data evaluated by the rule. The special value of 0 indicates that simply the presence of the DCI with a status of Pass 1 Complete or higher causes the rule to evaluate to TRUE.

TRIGGER_DCM_MODULE_SN

Null

NUMBER(3)

A serial number indicating the order of the trigger DCM module within the parent DCI

TRIGGER_DCM_QUALIFIER

Null

VARCHAR2(80)

Value of the Qualifying Question that identifies the particular DCM within the trigger DCI.

TRIGGER_DCM_QG_ID

Null

NUMBER(10)

ID of the DCM Question Group within the trigger DCI/DCM containing the data evaluated by the rule.

TRIGGER_DCM_QUE_ID

Null

NUMBER(10)

ID of the DCM Question within the trigger DCI/DCM containing the data evaluated by the rule.

TRIGGER_VALUES

Null

VARCHAR2(2000)

List of the Response values which cause the rule to evaluate to TRUE. Delimited by the non-printing character with ASCII value 31.

CLINICAL_STUDY_ID

Not null

NUMBER(10)

The ID of the clinical study.

CREATED_BY

Not null

VARCHAR2(30)

The username of the person who created the DCI Book rule.

CREATION_TS

Not null

DATE

Creation Timestamp

MODIFIED_BY

Null

VARCHAR2(30)

User who last modified this DCI book rule.

MODIFICATION_TS

Null

DATE

The Modification Timestamp.

RULE_DESCRIPTION

Null

VARCHAR2(200)

Describes the rule's function for the end user. The description appears on the Interval and the DCI rule screens.

DCI_BOOK_RULE_TGT_INTERVALS

This table contains the target Interval(s) for an Interval rule in a DCI Book. The target can be one or more Intervals in the Study schedule, or the pseudo value [Next Interval], which is used, for example, in a study design where patients continue on cycles of treatment until an end point is reached.

When the Interval rule trigger conditions are met, all CPEs in the target Interval(s) become expected. All DCIs collected at those CPEs also become expected, if they are not the target of a DCI rule. For a Bypass To Interval rule, all DCI – CPE combinations after the CPE at which the trigger DCI meets the trigger conditions, up to the expected target Interval also become Non-expected.

Primary Key RULE_ID, INTERVAL_ID

DCI_BOOK_RULE_TGT_INTERVALS Related tables and foreign keys

Name of related table

Foreign key name in this table

Foreign key name in the related table

DCI_BOOK_RULES

RULE_ID

RULE_ID

DCI_BOOK_RULE_TGT_INTERVALS Column Descriptions

Column

Null?

Type

Column Description

RULE_ID

Not null

NUMBER(10)

The rule ID.

INTERVAL_ID

Not null

NUMBER(10)

The target interval ID.

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.

CREATED_BY

Not null

VARCHAR2(30)

The username of the person who created the DCI Book target interval.

CREATION_TS

Not null

DATE

The creation timestamp.

MODIFIED_BY

Null

VARCHAR2(30)

User who last modified this DCI book rule target interval.

MODIFICATION_TS

Null

DATE

The modification timestamp.

DCI_BOOK_RULE_TGT_DCIS

This table contains the DCI(s) which are defined as the target of a DCI rule in a DCI Book. All DCIs that are not the target of a DCI rule are expected if the Interval, and therefore the CPEs, in which they are defined is, or becomes, expected. In addition, if a DCI is the target of a DCI rule, the rule trigger conditions must also be met for the DCI to become expected.

Primary Key RULE_ID, DCI_ID

DCI_BOOK_RULE_TGT_DCIS Related tables and foreign keys

Name of related table

Foreign key name in this table

Foreign key name in the related table

DCI_BOOK_RULES

RULE_ID

RULE_ID

DCI_BOOK_RULE_TGT_DCIS Column Descriptions

Column

Null?

Type

Column Description

RULE_ID

Not null

NUMBER(10)

The rule ID.

DCI_ID

Not null

NUMBER(10)

The ID of the DCI to which the DCI module belongs.

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.

CREATED_BY

Not null

VARCHAR2(30)

The username of the person who created the DCI Book rule target DCI.

CREATION_TS

Not null

DATE

The creation timestamp.

MODIFIED_BY

Null

VARCHAR2(30)

User who last modified this DCI book rule target DCI.

MODIFICATION_TS

Null

DATE

The modification timestamp.

PP_EXPECTED_INTERVALS(T)

PP_EXPECTED_INTERVALS tracks the current expectedness of Intervals for a particular Patient. If the Interval is expected for the Patient, there is an entry in this table with the BYPASSED_FLAG = N. If the Interval is not expected for the Patient, there is either no row or the entry has the BYPASSED_FLAG = Y.

Primary Key PATIENT_POSITION_ID, PLAN_INTERVAL_ID

PP_EXPECTED_INTERVALS 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

PP_EXPECTED_INTERVALS Column Descriptions

Column Name

Null?

Type

Column Description

CLINICAL_STUDY_ID

Not null

NUMBER(10)

The ID of the clinical study.

PATIENT_POSITION_ID

Not null

NUMBER(10)

Patient Position ID

PLAN_INTERVAL_ID

Not null

NUMBER(10)

Plan Interval ID

BYPASSED_FLAG

Null

VARCHAR2(1)

Indicates whether Interval has been bypassed by Bypass To Interval rule. Values are Y or N.

PP_EXPECTED_INTERVALS$JN

The PP_EXPECTED_INTERVALS table is audited by the PP_EXPECTED_INTERVALS$JN journal table. See the Column Descriptions in the table below.

Primary Key JN_SN

PP_EXPECTED_INTERVALS$JN 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

PP_EXPECTED_INTERVALS$JN Column Descriptions

Column Name

Null?

Type

Column Description

JN_SN

Not null

NUMBER(10)

Journal Sequence.

JN_OPERATION

Not null

VARCHAR2(3)

Journal Operation INS, UPD or DEL

JN_ORACLE_USER

Not null

VARCHAR2(30)

Oracle User who modified the data.

JN_TIMESTAMP

Not null

DATE

Journal Timestamp.

CLINICAL_STUDY_ID

Not null

NUMBER(10)

The ID of the clinical study.

PATIENT_POSITION_ID

Not null

NUMBER(10)

Patient Position ID.

PLAN_INTERVAL_ID

Not null

NUMBER(10)

Plan Interval ID.

BYPASSED_FLAG

Null

VARCHAR2(1)

Bypassed Flag.

PP_EXPECTED_CPES(T)

PP_EXPECTED_CPES tracks the current expectedness of clinical planned events for a particular Patient. If the clinical planned event is expected for the Patient, there is an entry in this table with the BYPASSED_FLAG = N. If the clinical planned event is not expected for the Patient, there is either no row or the entry has the BYPASSED_FLAG = Y.

Primary Key PATIENT_POSITION_ID, CLIN_PLAN_EVE_ID

PP_EXPECTED_CPES 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

PP_EXPECTED_CPES Column Descriptions

Column Name

Null?

Type

Column Description

CLINICAL_STUDY_ID

Not null

NUMBER(10)

The ID of the clinical study.

PATIENT_POSITION_ID

Not null

NUMBER(10)

Patient Position ID

CLIN_PLAN_EVE_ID

Not null

NUMBER(10)

Clinical planned event ID

BYPASSED_FLAG

Null

VARCHAR2 (1)

Indicates whether CPE has been bypassed by a Bypass To Interval rule. Values are Y or N.

LAST_EXPECTEDNESS_MOD_TS

Null

DATE

Timestamp of last modification of expectedness for a DCI assigned to this CPE in the DCI Book. It is updated whenever a DCI assigned to this CPE is added or removed from the PP_EXPECTED_DCIS table.

PP_EXPECTED_CPES$JN

The PP_EXPECTED_CPES table is audited by the PP_EXPECTED_CPES$JN journal table. See the Column Descriptions in the table below.

Primary Key JN_SN

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

PP_EXPECTED_CPES$JN Column Descriptions

Column Name

Null?

Type

Column Description

JN_SN

Not null

NUMBER(10)

The Journal Sequence number.

JN_OPERATION

Not null

VARCHAR2(3)

Journal Operation INS, UPD, or DEL

JN_ORACLE_USER

Not null

VARCHAR2(30)

Oracle User who modified the data

JN_TIMESTAMP

Not Null

DATE

Journal Timestamp

CLINICAL_STUDY_ID

Not null

NUMBER(10)

The ID of the clinical study.

PATIENT_POSITION_ID

Not null

NUMBER(10)

Patient Position ID

CLIN_PLAN_EVE_ID

Not null

NUMBER(10)

Clinical planned event ID

BYPASSED_FLAG

Null

VARCHAR2(1)

Bypassed Flag

LAST_EXPECTEDNESS_MOD_TS

Null

DATE

Timestamp of last modification of expectedness for a DCI assigned to this CPE in the DCI Book. It is updated whenever a DCI assigned to this CPE is added or removed from the PP_EXPECTED_DCIS table.

Note:

A journal row is not added if the only update is LAST_EXPECTEDNESS_MOD_TS.

PP_EXPECTED_DCIS(T)

PP_EXPECTED_DCIS tracks the current expectedness of DCIs at Clinical Planned Events for a particular Patient. If the DCI is expected at a Clinical Planned Event for the Patient, there is an entry in this table. If the DCI is not expected, there is no entry.

Primary Key PATIENT_POSITION_ID, CLIN_PLAN_EVE_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

PATIENT_POSITIONS

PATIENT_POSITION_ID

PATIENT_POSITION_ID

PP_EXPECTED_DCIS Column Descriptions

Column Name

Null?

Type

Column Description

CLINICAL_STUDY_ID

Not null

NUMBER(10)

The ID of the clinical study.

PATIENT_POSITION_ID

Not null

NUMBER(10)

Patient Position ID

CLIN_PLAN_EVE_ID

Not null

NUMBER)10)

Clinical planned event ID

DCI_ID

Not null

NUMBER(10)

The ID of the DCI to which the expected DCI belongs.

CREATION_TS

Not null

DATE

The creation timestamp.

PP_EXPECTED_DCIS$JN

The PP_EXPECTED_DCIS table is audited by the PP_EXPECTED_DCIS$JN journal table. See the Column Descriptions in the table below.

Primary Key JN_SN

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

PP_EXPECTED_DCIS$JN Column Descriptions

Column Name

Null?

Type

Column Description

JN_SN

Not null

NUMBER(10)

Journal Sequence

JN_OPERATION

Not null

VARCHAR2(3)

Journal Operation INS, UPD or DEL

JN_ORACLE_USER

Not null

VARCHAR2(30)

Oracle User who modified the data

JN_TIMESTAMP

Not null

DATE

The journal timestamp.

CLINICAL_STUDY_ID

Not null

NUMBER(10)

The ID of the clinical study.

PATIENT_POSITION_ID

Not null

NUMBER(10)

Patient Position ID

CLIN_PLAN_EVE_ID

Not null

NUMBER(10)

Clinical planned event ID

DCI_ID

Not null

NUMBER(10)

The ID of the DCI to which the expected DCI belongs.

CREATION_TS

Not null

DATE

The creation timestamp.

4 View Definitions

This chapter provides a description of each view that can be found within the stable interface. You should access these views only by their synonyms, which are described in Chapter 1, "About the Stable Interface."

About the Stable Interface Views

The following views supply packages of information about some of the major concepts in the Oracle Clinical system. Views make simple queries easier to code, but require more processing than straight table accesses and should therefore be avoided in production applications.

The columns in each stable interface view are described in terms of the tables and columns from which they are derived. In each case the column name in the view is given, followed by the table and column from which it is derived. For a definition of the field, refer to the corresponding table definition in Chapter 2, "Table Definitions".

ACTUAL_EVENTSV(T)

This is a view of actual events with internal IDs translated into user-recognizable codes.

The test view for ACTUAL_EVENTSV is ACTUAL_EVENTSVT.

Name of column in the ACTUAL_EVENTSV(T) view

Name of underlying table

Name of column in underlying table

ACTUAL_EVENT_ID

ACTUAL_EVENTS

ACTUAL_EVENT_ID

PATIENT_POSITION_ID

ACTUAL_EVENTS

PATIENT_POSITION_ID

CLINICAL_STUDY_ID

ACTUAL_EVENTS

CLINICAL_STUDY_ID

CLIN_PLAN_EVE_ID

ACTUAL_EVENTS

CLIN_PLAN_EVE_ID

CPE_NAME

CLINICAL_PLANNED_EVENTS

NAME

VISIT_NUMBER

CLINICAL_PLANNED_EVENTS

VISIT_NUMBER

SUBEVENT_NUMBER

ACTUAL_EVENTS

SUBEVENT_NUMBER

COMMENT_TEXT

ACTUAL_EVENTS

COMMENT_TEXT

START_DATE

RECEIVED_DCMS

DCM_DATE

END_DATE

RECEIVED_DCMS

DCM_DATE

CHECK_RANDOMIZATIONS

This view shows information about when a randomization was created. This view hides the seed number used, because this value is blinded information.

Column Name

Null?

Type

PERFORMED_BY

Not null

VARCHAR2(30)

TIMESTAMP_OCCURRED

Not null

DATE

RANDOMIZATION_TYPE_CODE

Not null

VARCHAR2(7)

CREATED_BY

Not null

VARCHAR2(30)

CREATION_TS

Not null

DATE

MODIFIED_BY

Null

VARCHAR2(30)

MODIFICATION_TS

Null

DATE

CLINICAL_STUDY_ID

Not null

NUMBER(10)

CLINICAL_STUDY_VERSION_ID

Not null

NUMBER(10)

DISCREPANCY_MANAGEMENT(T)

A view of discrepancies and associated information.

The test view for DISCREPANCY_MANAGEMENT is DISCREPANCY_MANAGEMENTT.

Name of Column in the DISCREPANCY_MANAGEMENT(T) view

Name of underlying table

Name of column in underlying table

DISCREPANCY_ENTRY_ID

DISCREPANCY_ENTRIES

DISCREPANCY_ENTRY_ID

CLINICAL_STUDY_ID

DISCREPANCY_ENTRIES

CLINICAL_STUDY_ID

STUDY_SITE

OCL_STUDY_SITES

STUDY_SITE

INVESTIGATOR

OCL_INVESTIGATORS

INVESTIGATOR

INV_NAME

OCL_INVESTIGATORS

LAST_NAME, FIRST_NAME

SITE

OCL_SITES

SITE

PATIENT

PATIENT_POSITIONS

PATIENT

REPORTED_INITIALS

PATIENT_POSITIONS

REPORTED_INITIALS

CREATION_TS

DISCREPANCY_ENTRIES

CREATION_TS

CREATED_BY

DISCREPANCY_ENTRIES

CREATED_BY

MODIFICATION_TS

DISCREPANCY_ENTRIES

MODIFICATION_TS

MODIFIED_BY

DISCREPANCY_ENTRIES

MODIFIED_BY

DISCREPANCY_STATUS_CODE

DISCREPANCY_ENTRIES

DISCREPANCY_STATUS_CODE

DE_SUB_TYPE_CODE

DISCREPANCY_ENTRIES

DE_SUB_TYPE_CODE

DISCREPANCY_TYPE_CODE

DISCREPANCY_ENTRIES

DISCREPANCY_TYPE_CODE

DCM_ID

DCMS

DCM_ID

NAME

DCMS

NAME

DCM_QUESTION_ID

DISCREPANCY_ENTRIES

DCM_QUESTION_ID

QUESTION_NAME

DCM_QUESTIONS

QUESTION_NAME

OCCURRENCE_SN

DCM_QUESTIONS

OCCURRENCE_SN

DISCREPANCY_REV_STATUS_CODE

DISCREPANCY_ENTRIES

DISCREPANCY_REV_STATUS_CODE

DISCREPANCY_RESOLU_TYPE_CODE

DISCREPANCY_ENTRIES

DISCREPANCY_RESOLU_TYPE_CODE

COMMENT_TEXT

DISCREPANCY_ENTRIES

COMMENT_TEXT

RECEIVED_DCM_ID

DISCREPANCY_ENTRIES

RECEIVED_DCM_ID

RECEIVED_DCM_ENTRY_TS

DISCREPANCY_ENTRIES

RECEIVED_DCM_ENTRY_TS

RESPONSE_ID

DISCREPANCY_ENTRIES

RESPONSE_ID

RESPONSE_ENTRY_TS

DISCREPANCY_ENTRIES

RESPONSE_ENTRY_TS

PROCEDURE_ID

DISCREPANCY_ENTRIES

PROCEDURE_ID

PROCEDURE_VER_SN

DISCREPANCY_ENTRIES

PROCEDURE_VER_SN

PROCEDURE_DETAIL_ID

DISCREPANCY_ENTRIES

PROCEDURE_DETAIL_ID

IRRESOLVABLE_FLAG

DISCREPANCY_ENTRIES

IRRESOLVABLE_FLAG

CLIN_PLAN_EVE_ID

CLINICAL_PLANNED_EVENTS

CLIN_PLAN_EVEN_ID

SUSPECT_FLAG

DISCREPANCY_ENTRIES

SUSPECT_FLAG

CLIN_PLAN_EVE_SN

CLINICAL_PLANNED_EVENTS

CLIN_PLAN_EVE_SN

EVENT_NAME

CLINICAL_PLANNED_EVENTS

NAME

SUBEVENT_NUMBER

DISCREPANCY_ENTRIES

SUBEVENT_NUMBER

DCF_ID

DISCREPANCY_ENTRIES

DCF_ID

ASSOCIATED_ID

DISCREPANCY_ENTRIES

ASSOCIATED_ID

CRF_PAGE_NUMBER

DISCREPANCY_ENTRIES

CRF_PAGE_NUMBER

DCF_COMMENT

DISCREPANCY_ENTRIES

DCF_COMMENT

PATIENT_POSITION_ID

DISCREPANCY_ENTRIES

PATIENT_POSITION_ID

SITE_ID

DISCREPANCY_ENTRIES

SITE_ID

INVESTIGATOR_ID

DISCREPANCY_ENTRIES

INVESTIGATOR_ID

FULL_STUDIES

A view of planned studies, clinical studies, and the live clinical study version attributes.

Name of column in the FULL_STUDIES view

Name of underlying table

Name of column in underlying table

STUDY

OCL_STUDIES

STUDY

CLINICAL_STUDY_ID

CLINICAL_STUDIES

CLINICAL_STUDY_ID

ORGANIZATION_CODE

OCL_ORGANIZATION_UNITS

CODE

Derived from the Organization Unit ID on OCL_STUDIES.

PROJECT_CODE

OCL_STUDIES

PROJECT_CODE

PROGRAM_CODE

OCL_STUDIES

PROGRAM_CODE

PRIMARY_COUNTRY_CODE

REGIONS

REGION_CODE

SHORT_TITLE

CLINICAL_STUDIES

SHORT_TITLE

AVAIL_FOR_REPLICATION_FLAG

CLINICAL_STUDIES

AVAIL_FOR_REPLICATION_FLAG

PHONE_RANDOMIZATION_FLAG

CLINICAL_STUDIES

PHONE_RANDOMIZATION_FLAG

OWNING_LOCATION

CLINICAL_STUDIES

OWNING_LOCATION

CLINICAL_PHASE

OCL_STUDIES

CLINICAL_PHASE

RAND_ACC_STAT_TYPE_CODE

CLINICAL_STUDIES

RAND_ACC_STAT_TYPE_CODE

STUDY_STATUS_TYPE_CODE

CLINICAL_STUDIES

STUDY_STATUS_TYPE_CODE

EXPTL_DESIGN_TYPE_CODE

OCL_STUDIES

EXPTL_DESIGN_TYPE_CODE

PAT_RPL_RULE_TYPE_CODE

CLINICAL_STUDY_VERSIONS

PAT_RPL_RULE_TYPE_CODE

TIME_UNIT_TYPE_CODE

CLINICAL_STUDY_VERSIONS

TIME_UNIT_TYPE_CODE

FDA_PACKAGE_FLAG

CLINICAL_STUDIES

FDA_PACKAGE_FLAG

PIVOTAL_STUDY_FLAG

CLINICAL_STUDIES

PIVOTAL_STUDY_FLAG

TYPE_I_ERROR

CLINICAL_STUDY_VERSIONS

TYPE_I_ERROR

POWER

CLINICAL_STUDY_VERSIONS

POWER

MAXIMUM_STUDY_DURATION

CLINICAL_STUDY_VERSIONS

MAXIMUM_STUDY_DURATION

MAXIMUM_TREATMENT_DURATION

CLINICAL_STUDY_VERSIONS

MAXIMUM_TREATMENT_DURATION

EXPECTED_ENROLLMENT_COST

CLINICAL_STUDY_VERSIONS

EXPECTED_ENROLLMENT_COST

EXPECTED_ENROLLMENT_RATE

CLINICAL_STUDY_VERSIONS

EXPECTED_ENROLLMENT_RATE

ACTUAL_PATIENTS_ENROLLED

FULL_STUDIES

ACTUAL_PATIENTS_ENROLLED

A count of patient positions for which the patient's enrollment date is not null.

ACTUAL_PATIENTS_WITH_DATA

FULL_STUDIES

ACTUAL_PATIENTS_WITH_DATA

A count of patient positions for which the patient's Has Data Flag = 'Y'.

INVESTIGATORS_PLANNED

OCL_STUDIES

INVESTIGATORS_PLANNED

NUMBER_OF_PAT_TO_ENROLL

CLINICAL_STUDY_VERSIONS

NUMBER_OF_PAT_TO_ENROLL

NUMBER_OF_PAT_TO_ANALYSE

CLINICAL_STUDY_VERSIONS

NUMBER_OF_PAT_TO_ANALYSE

TITLE

CLINICAL_STUDIES

TITLE

INTERIM_ANALYSIS_TRIGGER

CLINICAL_STUDY_VERSIONS

INTERIM_ANALYSIS_TRIGGER

DESIGN_DESCRIPTION

CLINICAL_STUDY_VERSIONS

DESIGN_DESCRIPTION

RANDOMIZATION_DESCRIPTION

CLINICAL_STUDY_VERSIONS

RANDOMIZATION_DESCRIPTION

CREATED_BY

CLINICAL_STUDIES

CREATED_BY

CREATION_TS

CLINICAL_STUDIES

CREATION_TS

MODIFIED_BY

CLINICAL_STUDIES

MODIFIED_BY

MODIFICATION_TS

CLINICAL_STUDIES

MODIFICATION_TS

PATIENTS

A view of patient positions, strata, treatment pattern, current site, current investigator, and DCI book information. This is a particularly large view and should only be used when the additional information is required. For basic patient information, the patient positions table should be used.

Name of Column in the PATIENTS view

Name of underlying table

Name of column in underlying table

STUDY

CLINICAL_STUDY_VERSIONS

STUDY

PATIENT

PATIENT_POSITIONS

PATIENT

ORDER_BY_PATIENT

PATIENT_POSITIONS

ORDER_BY_PATIENT

A derived column that sorts for numeric patient codes.

CLINICAL_STUDY_ID

PATIENT_POSITIONS

CLINICAL_STUDY_ID

PATIENT_POSITION_ID

PATIENT_POSITIONS

PATIENT_POSITION_ID

CLINICAL_SUBJECT_ID

PATIENT_POSITIONS

CLINICAL_SUBJECT_ID

DCI_BOOK

DCI_BOOKS

DCI_BOOK

HAS_DATA_FLAG

PATIENT_POSITIONS

HAS_DATA_FLAG

OWNING_LOCATION

PATIENT_POSITIONS

OWNING_LOCATION

CURRENT_SITE

PATIENT_POSITIONS

CURRENT_SITE

Derived from the current assignment of the patient to a site.

CURRENT_INVESTIGATOR

PATIENT_POSITIONS

CURRENT_INVESTIGATOR

Derived from the current assignment of the patient to an investigator.

STRATUM

PATIENT_POSITIONS

STRATUM

Derived from the strata assigned to a patient.

TREATMENT_PATTERN_CODE

PATIENT_POSITIONS

TREATMENT_PATTERN_CODE

Derived from the treatment pattern assigned to a patient.

FREEZE_FLAG

PATIENT_POSITIONS

FREEZE_FLAG

REPLACEMENT_POSITION_FLAG

PATIENT_POSITIONS

REPLACEMENT_POSITION_FLAG

SCREENING_POSITION_FLAG

PATIENT_POSITIONS

SCREENING_POSITION_FLAG

EARLY_TERMINATION_FLAG

PATIENT_POSITIONS

EARLY_TERMINATION_FLAG

FIRST_SCREENING_DATE

PATIENT_POSITIONS

FIRST_SCREENING_DATE

PATIENT_ENROLLMENT_DATE

PATIENT_POSITIONS

PATIENT_ENROLLMENT_DATE

TERMINATION_DATE

PATIENT_POSITIONS

TERMINATION_DATE

REPORTED_FIRST_NAME

PATIENT_POSITIONS

REPORTED_FIRST_NAME

REPORTED_LAST_NAME

PATIENT_POSITIONS

REPORTED_LAST_NAME

REPORTED_SEX

PATIENT_POSITIONS

REPORTED_SEX

REPORTED_PATIENT_REFERENCE

PATIENT_POSITIONS

REPORTED_PATIENT_REFERENCE

REPORTED_INITIALS

PATIENT_POSITIONS

REPORTED_INITIALS

REPORTED_BIRTH_DATE

PATIENT_POSITIONS

REPORTED_BIRTH_DATE

REPORTED_DEATH_DATE

PATIENT_POSITIONS

REPORTED_DEATH_DATE

RANDOMIZATION_COMMENT

PATIENT_POSITIONS

RANDOMIZATION_COMMENT

REPORTED_DATE_LAST_PREGNANCY

PATIENT_POSITIONS

REPORTED_DATE_LAST_PREGNANCY

INC_IN_EFF_ANALYSIS_FLAG

PATIENT_POSITIONS

INC_IN_EFF_ANALYSIS_FLAG

INC_IN_SAFETY_ANALYSIS_FLAG

PATIENT_POSITIONS

INC_IN_SAFETY_ANALYSIS_FLAG

EXCLUDE_FROM_EFFICACY_REASON

PATIENT_POSITIONS

EXCLUDE_FROM_EFFICACY_REASON

INCLUSION_EXCLUSION_DATE

PATIENT_POSITIONS

INCLUSION_EXCLUSION_DATE

EXCLUDE_FROM_SAFETY_REASON

PATIENT_POSITIONS

EXCLUDE_FROM_SAFETY_REASON

CREATION_TS

PATIENT_POSITIONS

CREATION_TS

CREATED_BY

PATIENT_POSITIONS

CREATED_BY

MODIFICATION_TS

PATIENT_POSITIONS

MODIFICATION_TS

MODIFIED_BY

PATIENT_POSITIONS

MODIFIED_BY

PROGRAM_SUBSTANCES

A view of programs and their primary active substance.

Name of column in the PROGRAM_SUBSTANCES view

Name of underlying table

Name of column in underlying table

PROGRAM_CODE

OCL_PROGRAMS

PROGRAM_CODE

DESCRIPTION

OCL_PROGRAMS

DESCRIPTION

ACTIVE_FLAG

OCL_PROGRAMS

PROGRAM_CODE

PRIMARY_ACTIVE_SUBSTANCE_ID

OCL_PROGRAM_PRODUCT_MASTERS

PM_ID

CREATED_BY

OCL_PROGRAMS

CREATED_BY

CREATION_TS

OCL_PROGRAMS

CREATION_TS

MODIFIED_BY

OCL_PROGRAMS

MODIFIED_BY

MODIFICATION_TS

OCL_PROGRAMS

MODIFICATION_TS

TREAT_ASSIGN_ALL_VIEW

The main reason for this view is to manage the blinding of a study. While the study is blinded, the view will provide a dummy treatment code. This code has no relation to the real treatment to which the patient has been randomized. After the randomization is released, the view will show the true treatment patterns. Because the underlying table (TREATMENT_ASSIGNMENTS) is not part of the documented interface, the columns in the view are defined here. The TREATMENT_ASSIGNMENTS table is at the clinical study version level, but this view limits the data presented to the live version of the study.

Primary key

TREAT_ASS_ID

Unique key

CLINICAL_STUDY_ID, TREATMENT

Name of related table

Foreign key name in the current table

Foreign key name in the related table

CLINICAL_STUDIES

CLINICAL_STUDY_ID

CLINICAL_STUDY_ID

TREATMENT_PATTERNS

TREAT_PATTERN_ID

TREATMENT_PATTERN_ID

PATTERNS

PATTERN_CODE

CODE

RANDOMIZATIONS

RANDOM_PERFORMED_BY

RANDOM_PERFORMED_BY

RANDOMIZATIONS

RANDOM_TIMESTAMP_OCCURRED

RANDOM_TIMESTAMP_OCCURRED

RANDOMIZATION_BLOCKS

RANDOM_BLOCK_ID

RANDOMIZATION_BLOCK_ID

OCL_SITES

SITE_ID

SITE_ID

STRATA

STRATUM_ID

STRATUM_ID

PATIENT_POSITIONS

PATIENT_POSITION_ID

PATIENT_POSITION_ID

Columns

Column name

Null?

Type

TREAT_ASS_ID A unique, system-generated ID for the treatment assignment.

Not null

NUMBER(10)

CLINICAL_STUDY_ID The ID of the clinical study to which the treatment assignment belongs.

Not null

NUMBER(10)

TREATMENT A code for the treatment assignment, unique within the clinical study.

Not null

VARCHAR2(10)

LOCKED_FLAG A flag to show if the randomization where the treatment assignment belongs has been locked.

Not null

VARCHAR2(1)

TREAT_PATTERN_ID The ID of the treatment pattern for the treatment assignment.

Not null

NUMBER(10)

NAME The name of the treatment pattern for the treatment assignment.

Not null

VARCHAR2(60)

PATTERN_CODE The pattern code of the treatment pattern that the treatment assignment is for.

Not null

VARCHAR2(15)

LATEST_FLAG A flag to show if this is the latest or current treatment assignment for the patient.

Not null

VARCHAR2(1)

REPLACEMENT_FLAG A flag to show if this is intended as a replacement treatment assignment in the study. A replacement treatment assignment is used only if a patient drops out of the study.

Not null

VARCHAR2(1)

RANDOM_PERFORMED_BY The user name of the person who ran the randomization process that created this treatment assignment.

Not null

VARCHAR2(30)

RANDOM_TIMESTAMP_OCCURRED The date and time that the randomization process that created this treatment assignment was run.

Not null

DATE

CREATED_BY

Not null

VARCHAR2(30)

CREATION_TS

Not null

DATE

RANDOM_BLOCK_ID If this treatment assignment is part of a blocked randomization, this column is the ID of the randomization block to which the treatment assignment belongs.

Null

NUMBER(10)

SITE_ID The ID of the site to which the treatment assignment has been assigned.

Null

NUMBER(10)

ASSIGNMENT_DATE The date and time that the treatment assignment was assigned to a patient.

Null

DATE

TREAT_CHG_REASON_TYPE_CODE A code that indicates why the treatment pattern of the treatment assignment was changed.

Null

VARCHAR2(15)

TREAT_CHG_REASON_DESCRIPTION A freeform text description explaining why the treatment pattern of the treatment assignment was changed.

Null

VARCHAR2(200)

STRATUM_ID The ID of the stratum to which the patient who has been assigned this treatment assignment belongs.

Null

NUMBER(10)

PATIENT_POSITION_ID The ID of the patient position that has been assigned this treatment assignment.

Null

NUMBER(10)

MODIFIED_BY

Null

VARCHAR2(30)

MODIFICATION_TS

Null

DATE

5 Entity Relationship Diagram

The diagram on the following two pages describes the relationships between tables in the Oracle Clinical Stable Interface Technical Reference Manual. In using this map as a programming guide, be sure to use the synonyms that point to the listed names, and not the underlying names themselves, to access data from Oracle Clinical. Synonym names are listed in Chapter 1, "About the Stable Interface."

An asterisk (*) indicates that a table is related to CLINICAL_STUDIES through the Clinical Study ID.

A Journal and History Tables

Journal and history tables store audit records that enable you to track changes to Oracle Clinical data over time. Each type of auditing table is used in a particular situation:

Journal tables are used to track changes when the base table will have a relatively low volume of changes. For example, all NLS tables that store translations have journal tables, because translations are not frequently changed. The journal table keeps current and previous values of all columns in the base table. The journal table gets populated with a record when the operations INSERT, UPDATE or DELETE are carried out on the base table. Tables can also be self-journaling. See "Self-Journaling tables".

High volume tables will either be self-auditing or will use history tables. Each history and self-auditing table has rules particular to that table, while journal tables all follow the same rules. History tables do not capture all changes to a table, only those changes that are considered critical. Self-auditing tables also only capture changes to user-entered fields. Internal status tracking fields are not audited.

In general, tables are not journaled if they store any of the following:

- Temporary, test, or derived data.

- Data used to drive a data change if this data has not yet been used to do a data change.

- Encrypted password data.

- Data that cannot be manipulated from the Oracle Clinical front end.

- Data that is used for system configuration but does not affect clinical data. For example, tables that specify directory mapping to allow users to view their job output.

- Data for tracking internal status.

This appendix lists each table that has changes audited by a journal or history table.

Note:

There are no newly journaled tables in Oracle Clinical 4.6.

Self-Journaling tables

Self-journaling tables store their own audit histories by storing the end timestamp of records. Records with an end timestamp of 3,000,000 Julian (15-AUG-3501) are considered current; records with a different END_TS value are audit records.

Any base table with an END_TS column is self-journaling. The stable interface synonym tables that are self journaling are ACTUAL_EVENTS(T), RECEIVED_DCIS(T), RECEIVED_DCMS(T), and RESPONSES(T).

Note:

Self journaling on patient data starts after the completion of Pass 1 for a study requiring Pass 1 data entry only and after completion of Pass 2 for a study requiring Pass 2 data entry.

Journal Table Columns

All journal table names end with the suffix $JN. Journal tables include all of the columns in their base table, as well as the following columns specific to journaling:

Column Name

Null?

Datatype

JN_OPERATION The type of operation carried out on the base table. Possible values are: 'INS' for insert, 'UPD' for update and 'DEL' for delete.

Not Null

VARCHAR2(3)

JN_TIMESTAMP The date and time of this audit record. Populated with SYSDATE value.

Not Null

DATE

JN_SN The journal sequence number, which is unique for every operation.

Not Null

NUMBER(10)

JN_ORACLE_USER User who last modified the base table.

Not Null

VARCHAR2(30)

SYMMETRIC_REPLICATION_IND This column indicates whether the base table that this this journaling table audits is involved in symmetric replication.

Null

VARCHAR2(1)

Tables First Audited in Oracle Clinical 4.5

This section lists each Oracle Clinical base table that was audited for the first time in Release 4.5, and the name of the journal or history table that records its audit records. For journal tables, the journal table's name is usually the base table name concatenated with $JN; however, for some long base table names, the journal table uses a shortened version of the base table name (with the $JN suffix) to fit the table name length requirements of the Oracle database.

Table A-1 lists the journal and history tables and the base tables they audit. The five history tables (whose names end in _HIST or _HISTORY instead of $JN) that were new in Oracle Clinical 4.5 are described in more detail in "History Tables" or with the base tables in Chapter 2, "Table Definitions".

Table A-1 New Journal and History Tables For Oracle Clinical 4.5

Base table

Journal table

First audited in Oracle Clinical 4.5?

CLINICAL_PLANNED_EVENTS

CLINICAL_PLANNED_EVENTS$JN

Yes

CLINICAL_STUDIES

CLINICAL_STUDY_HISTORY

No

CLINICAL_SUBJECTS

CLINICAL_SUBJECTS$JN

Yes

DATA_CLARIFICATION_FORMS

DATA_CLARIFICATION_FORMS$JN

No

DCF_PAGES

DCF_PAGES$JN

No

DCIS

DCIS$JN

No

DCI_BOOK_DCI_CONSTRAINTS

DCI_BOOK_DCI_CONSTRAINTS$JN

Yes

DCI_BOOKS

DCI_BOOKS$JN

No

DCI_BOOK_PAGES

DCI_BOOK_PAGES$JN

No

DCI_BOOK_PHYSICAL_PAGES

DCI_BOOK_PHYSICAL_PAGES$JN

No

DCI_FORM_VERSIONS

DCI_FORM_VERSIONS$JN

Yes

DCI_MODULES

DCI_MODULES$JN

No

DCI_MODULE_PAGES

DCI_MODULE_PAGES$JN

No

DCMS

DCMS$JN

No

DCMS

DCMS_FFL_XML_HIST

Yes, by this history table

DCM_QUESTIONS

DCM_QUESTIONS$JN

No

DCM_QUESTION_GROUPS

DCM_QUESTION_GROUPS$JN

No

DCM_QUES_REPEAT_DEFAULTS

DCM_QUES_REPEAT_DEFAULTS$JN

No

DCM_SCHEDULES

DCM_SCHEDULES$JN

No

DISCREPANCY_ENTRIES(T)

DISCREPANCY_ENTRY_REVIEW_HIST(T)

No

DISCRETE_VALUES

DISCRETE_VALUES$JN

No

DISCRETE_VALUE_GROUPS

DISCRETE_VALUE_GROUPS$JN

No

FORM_LAYOUT_TEMPLATES

FORM_LAYOUT_TEMPLATES$JN

Yes

FORM_LAYOUT_TEMPLATES

FORM_LAYOUT_TEMPLATES_XML_HIST

Yes

LAB_RANGE_SUBSETS

LAB_RANGE_SUBSETS$JN

Yes

LAB_TEST_QUESTION_UNITS

LAB_TEST_QUESTION_UNITS$JN

Yes

LAB_UNIT_CONVERSIONS

LAB_UNIT_CONVERSIONS$JN

Yes

LAB_UNITS

LAB_UNITS$JN

Yes

LABS

LABS$JN

Yes

OCL_INVESTIGATORS(T)

OCL_INVESTIGATORS$JN

Yes

OCL_ORGANIZATION_UNITS

OCL_ORGANIZATION_UNITS$JN

Yes

OCL_PROGRAMS

OCL_PROGRAMS$JN

Yes

OCL_PROJECTS

OCL_PROJECTS$JN

Yes

OCL_SITES

OCL_SITES$JN

Yes

OCL_STUDIES

OCL_STUDIES$JN

Yes

OCL_STUDY_SITE_ROLES(T)

OCL_STUDY_SITE_ROLES$JN

Yes

OCL_STUDY_SITES

OCL_STUDY_SITES$JN

Yes

ORACLE_ACCOUNTS

ORACLE_ACCOUNTS$JN

No

PATIENT_POSITIONS

PATIENT_POSITIONS_HISTORY

Yes

PLANNED_STUDY_INTERVALS

PLANNED_STUDY_INTERVALS$JN

Yes

PREFERRED_LAB_UNITS

PREFERRED_LAB_UNITS$JN

Yes

PROCEDURES

PROCEDURES$JN

No

PROCEDURE_DETAILS

PROCEDURE_DETAILS$JN

No

PROCEDURE_QUESTION_GROUPS

PROCEDURE_QUESTION_GROUPS$JN

No

QUESTIONS

QUESTIONS$JN

No

QUESTION_CATEGORY_RELATIONS

QUESTION_CATEGORY_RELATIONS$JN

No

QUESTION_GROUPS

QUESTION_GROUPS$JN

No

QUESTION_GROUP_QUESTIONS

QUESTION_GROUP_QUESTIONS$JN

No

RANGES

RANGES$JN

Yes

RECEIVED_PAGES(T)

RECEIVED_PAGE_HISTORY(T)

No

REFERENCE_CODELISTS

REFERENCE_CODELISTS$JN

No

REFERENCE_CODELIST_VALUES

REFERENCE_CODELIST_VALUES$JN

No

REGIONS

REGIONS$JN

Yes

STUDY_SITE_PATIENT_POSITIONS

STUDY_SITE_PATIENT_POSITION$JN

Yes

TREATMENT_PATTERNS

TREATMENT_PATTERNS$JN

Yes

VALIDATION_REPORTED_VALUES

VALIDATION_REPORTED_VALUES$JN

Yes

History Tables

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

CLINICAL_STUDY_HISTORY

This table provides a record of certain major system-defined events in the course of a study as well as arbitrary user-recorded event about a clinical study.

Unless a description is provided next to the column name, the column stores the same information as its counterpart in the base table, CLINICAL_STUDIES.

Column Name

Null?

Datatype

CLIN_STUDY_ID ID of the clinical study to which this history record applies.

Not Null

NUMBER(10)

CLIN_STUDY_HISTORY_ID Unique ID of this history record.

Not Null

NUMBER(10)

DATE_OCCURRED Date when this change to the study occurred.

Not Null

DATE

CLIN_STUDY_HISTORY_TYPE_CODE This column stores a value from the CLIN STUDY HISTORY TYPE CODE reference codelist. The special code USERDEF identifies the user-created history records, while the other codes are for history records that are automatically created when the matching event occurs for the study.

Not Null

VARCHAR2(7)

TEXT Description of the event.

Not Null

VARCHAR2(200)

CREATED_BY User who generated this history record; that is, the user who modified the clinical study record identified by the clin_study_id.

Not Null

VARCHAR2(30)

CREATION_TS Date and time when this history record was generated; that is, the user who modified the clinical study record identified by the clin_study_id.

Not Null

DATE

END_DATE Date and time when this version of the clinical study record was changed.

Null

DATE

PLANNED_STUDY_INTERVAL_ID ID of the study interval in the study schedule that is associated with the occurrence of the history record – specifically for the SECURIN type of CLIN_STUDY_HISTORY_TYPE_CODE.

Null

NUMBER(10)

RAND_ACC_STAT_TY_CODE_OLD The RAND_ACC_STAT_TYPE_CODE value before the change to this record. This code indicates who can access the study's randomization.

Null

VARCHAR2(7)

RAND_ACC_STAT_TY_CODE_NEW The RAND_ACC_STAT_TYPE_CODE value after the change to this record. This code indicates who can access the study's randomization.

Null

VARCHAR2(7)

STUDY_STAT_TY_CODE_OLD The STUDY_STATUS_TYPE_CODE value before the change to this record. This code indicates the status of the study in terms of planning, operation, or analysis.

Null

VARCHAR2(7)

STUDY_STAT_TY_CODE_NEW The STUDY_STATUS_TYPE_CODE value before the change to this record. This code indicates the status of the study in terms of planning, operation, or analysis.

Null

VARCHAR2(7)

MODIFIED_BY User who modified this clinical study record.

Null

VARCHAR2(30)

MODIFICATION_DATE Date and time when this clinical study record was modified.

Null

DATE

DCMS_FFL_XML_HIST

This history table is populated with the current graphical layout whenever it is made available (its Available? column is set to Y).

Column Name

Null?

Datatype

DCM_ID DCM-wide: A unique, system-generated ID for the DCM.

Not Null

NUMBER(10)

DCM_SUBSET_SN Subset-wide: A sequence number for a DCM subset. A DCM subset can be used to collect only some of the questions assigned to the base DCM. The base or initial DCM has a sequence number of 1.

Not null

NUMBER(3)

DCM_LAYOUT_SN Layout-specific: The DCM layout number. Layouts provide a way to present the same DCM subset in different formats for data entry — for instance, a DCM subset can have an English and a Japanese layout. Within a subset, the layouts are numbered starting with 1.

Not null

NUMBER(3)

CLINICAL_STUDY_ID DCM-wide: The ID of the clinical study to which the DCM belongs.

Not null

NUMBER(10)

FLT_XML  A character large object that stores this DCM graphic layout.

Not null

CLOB

FLT_XML_MODIFICATION_TS Date and time when this DCM Form layout was last modified. Modifications include edits, updates, and generations.

Not null

DATE

FLT_XML_MODIFIED_BY User who last modified the DCM Form Layout.

Not null

VARCHAR2(30)

FORM_LAYOUT_TEMPLATES_XML_HIST

This history table is populated with current graphical layout and related information when status is set to A.

Column Name

Null?

Datatype

FLT_ID Unique ID of this form layout template.

Not Null

NUMBER(10)

FLT_XML A character large object that stores the XML for this form layout template.

Not Null

CLOB

FL_XML_MODIFICATION_TS Date and time when the XML for this FLT was last modified.

Not Null

DATE

FL_XML_MODIFIED_BY User who last modified the XML for this FLT.

Not Null

VARCHAR2(30)

PATIENT_POSITIONS_HISTORY

This history table records changes to the records in the PATIENT_POSITIONS(T) table. In release 4.5, it tracks changes to four columns that were not previously audited: DCI_BOOK_ID, FREEZE_FLAG, REPLACEMENT_POSITION_FLAG, and SCREENING_POSITION_FLAG.

Unless a description is provided next to the column name, the column stores the same information as its counterpart in the base table, PATIENT_POSITIONS(T).

Column Name

Null?

Datatype

CLINICAL_STUDY_ID

Not null

NUMBER(10)

PATIENT_POSITION_ID

Not null

NUMBER(10)

PATIENT

Not null

VARCHAR2(10)

CLINICAL_STUDY_VERSION_ID

Not null

NUMBER(10)

DCI_BOOK_ID

Null

NUMBER

FREEZE_FLAG

Null

VARCHAR2(1)

INC_IN_EFF_ANALYSIS_FLAG

Null

VARCHAR2(1)

INC_IN_SAFETY_ANALYSIS_FLAG

Null

VARCHAR2(1)

REPLACEMENT_POSITION_FLAG

Null

VARCHAR2(1)

SCREENING_POSITION_FLAG

Null

VARCHAR2(1)

EARLY_TERMINATION_FLAG

Null

VARCHAR2(1)

PATIENT_ENROLLMENT_DATE

Null

DATE

CLINICAL_SUBJECT_ID

Null

NUMBER(10)

INCLUSION_EXCLUSION_DATE

Null

DATE

EXCLUDE_FROM_EFFICACY_REASON

Null

VARCHAR2(200)

REPORTED_FIRST_NAME

Null

VARCHAR2(15)

REPORTED_LAST_NAME

Null

VARCHAR2(20)

REPORTED_SEX

Null

VARCHAR2(1)

REPORTED_PATIENT_REFERENCE

Null

VARCHAR2(25)

REPORTED_INITIALS

Null

VARCHAR2(4)

REPORTED_BIRTH_DATE

Null

DATE

REPORTED_DEATH_DATE

Null

DATE

RANDOMIZATION_COMMENT

Null

VARCHAR2(200)

REPORTED_DATE_LAST_PREGNANCY

Null

DATE

FIRST_SCREENING_DATE

Null

DATE

INFORMED_CONSENT_DATE

Null

Date

TERMINATION_DATE

Null

DATE

MODIFICATION_TS

Null

DATE

AUDIT_COMMENT Identifies the audit row as an update ('UPG') or a deletion ('DEL').

Null

VARCHAR2(10)

RECEIVED_PAGES_HISTORY

This history table records changes to the records in the RECEIVED_PAGES(T) table.

Unless a description is provided next to the column name, the column stores the same information as its counterpart in the base table, RECEIVED_PAGES(T).

Column Name

Null?

Datatype

RECEIVED_PAGE_ID

Not null

NUMBER(10)

RECEIVED_DCI_ID

Not null

NUMBER(10)

CLINICAL_STUDY_ID

Not null

NUMBER(10)

PAGE_STATUS

Not null

VARCHAR2(15)

HAS_DATA_FLAG

Not null

VARCHAR2(1)

RELATIVE_TO_DCI_PAGE

Not null

NUMBER(4)

BOOK_PAGE

Not null

VARCHAR2(15)

COMMENT_TEXT

Null

VARCHAR2(200)

CREATED_BY

Not null

VARCHAR2(30)

CREATION_TS

Not null

DATE

Oracle Legal Notices

Copyright Notice

Copyright © 1994-2013, Oracle and/or its affiliates. All rights reserved.

Trademark Notice

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

License Restrictions Warranty/Consequential Damages Disclaimer

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

Warranty Disclaimer

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

Restricted Rights Notice

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

Hazardous Applications Notice

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Third-Party Content, Products, and Services Disclaimer

This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

Alpha and Beta Draft Documentation Notice

If this document is in preproduction status:

This documentation is in preproduction status and is intended for demonstration and preliminary use only. It may not be specific to the hardware on which you are using the software. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to this documentation and will not be responsible for any loss, costs, or damages incurred due to the use of this documentation.