Table Details
The following tables show the structure of the interface tables for Oracle Clinical, grouped by subsystem. The columns indicated as NOT NULL are mandatory for Oracle Clinical functionality. The other columns may not be used at all, or may be used in a display-only report or screen.
For more information, see:
Parent topic: Interface Tables and Views
Drug Supply System
There are two drug supply tables:
Parent topic: Table Details
OCL_DOSAGE_FORMS
This is a list of the forms in which drugs can be manufactured. It adds a description to the dosage form code held on OCL_PRODUCT_MASTERS.
| Parameter | Datatype, Null? | Description |
|---|---|---|
|
DOFO_ID |
NUMBER(10), NOT NULL |
Unique ID for the dosage form |
|
DOSAGE_FORM_CODE |
VARCHAR2(7), NOT NULL |
User-understood code for the dosage form |
Parent topic: Drug Supply System
OCL_PRODUCT_MASTERS
This is a list of two types of material. The ACT SUB (active substances) materials are base chemicals that can be assigned to programs. The FORMULATED materials are formulated drug supplies (for example, 250 mg pill of a drug) from which treatment regimens can be defined.
| Parameter | Datatype, Null? | Description |
|---|---|---|
|
PM_ID |
NUMBER(10), NOT NULL |
Unique ID for the product master |
|
PRODUCT_NAME |
VARCHAR2(70), NOT NULL |
User-understood name for the product master |
|
PM_STATUS_CODE |
VARCHAR2(15), NOT NULL |
Set to ACTIVE |
|
PM_TYPE_CODE |
VARCHAR2(10), NOT NULL |
ACTIVE SUB—raw compounds to which programs can be linked FORMULATED—formulated drugs used in treatment regimens |
|
CREATION_TS |
DATE, NOT NULL |
Date and time the record was created |
|
CREATED_BY |
VARCHAR2(30), NOT NULL |
User ID of the person who created the record |
|
ACTIVE_SUBSTANCE_PM_ID |
NUMBER(10) |
Product Master ID of the active substance in a formulated product (Null for active substances themselves) |
|
DOFO_ID |
NUMBER(10) |
ID of the dosage form for formulated products (Null for active substances) |
|
CODE |
VARCHAR2(10) |
Code for the active substance itself or for the active substance in a formulated product |
|
SUBCODE |
VARCHAR2(10) |
Subcode for the active substance itself or for the active substance in a formulated product |
|
MODIFICATION_TS |
DATE |
Date and time the record was last modified |
|
MODIFIED_BY |
VARCHAR2(30) |
User ID of the person who last modified the record |
Parent topic: Drug Supply System
Investigator Management System
There are four investigator management system tables:
OCL_INVESTIGATORS
This is a master list of investigators who can be assigned to a study.
| Parameter | Datatype, Null? | Description |
|---|---|---|
|
INVESTIGATOR_ID |
NUMBER(10,0), NOT NULL |
ID of an investigator record |
|
INVESTIGATOR |
VARCHAR2(10),NOT NULL |
Code for the investigator as recognized by the user |
|
TITLE |
VARCHAR2(4), NULL |
Title for the investigator |
|
INITIALS |
VARCHAR2(4), NULL |
Initials of the investigator |
|
FIRST_NAME |
VARCHAR2(15),NOT NULL |
First name of the investigator |
|
LAST_NAME |
VARCHAR2(20),NOT NULL |
Last name of the investigator |
|
ACTIVE_FLAG |
VARCHAR2(1), NOT NULL |
Y = Investigator is available to work on new studies N = Investigator not assignable to new studies |
|
CREATED_BY |
VARCHAR2(30),NOT NULL |
User ID of the person who created the record |
|
CREATION_TS |
DATE, NOT NULL |
Date and time the record was created |
|
PHONE_NUMBER |
VARCHAR2(25),NOT NULL |
Phone number of the investigator |
|
COUNTRY |
VARCHAR2(7), NULL |
Region code of the country in which the investigator lives |
|
STATE |
VARCHAR2(7), NULL |
Region code of the state/province/county, etc. where the investigator lives |
|
CITY |
VARCHAR2(25),NULL |
Name of the city where the investigator lives |
|
ADDRESS_NAME |
VARCHAR2(40),NULL |
Name to start the investigator's address |
|
ADDRESS_LINE_1 |
VARCHAR2(40),NULL |
First line of the investigator's address |
|
ADDRESS_LINE_2 |
VARCHAR2(40), NULL |
Second line of the investigator's address |
|
ADDRESS_LINE_3 |
VARCHAR2(40), NULL |
Third line of the investigator's address |
|
POSTAL_CODE |
VARCHAR2(15),NULL |
Postal or ZIP code for the investigator |
|
MODIFIED_BY |
VARCHAR2(30), NULL |
User ID of the person who last modified the record |
|
MODIFICATION_TS |
DATE, NULL |
Date and time the record was last modified |
Parent topic: Investigator Management System
OCL_SITES
This is a list of sites where a study can be performed.
| Parameter | Datatype, Null? | Description |
|---|---|---|
|
SITE_ID |
NUMBER(10,0), NOT NULL |
ID for the site |
|
SITE |
VARCHAR2(10), NOT NULL |
Code for the site as recognized by the user |
|
ACTIVE_FLAG |
VARCHAR2(1), NOT NULL |
Y = Site is available for work on new studies N = Site not assignable to new studies |
|
CREATED_BY |
VARCHAR2(30), NOT NULL |
User ID of the person who created the record |
|
CREATION_TS |
DATE, NOT NULL |
Date and time the record was created |
|
PHONE_NUMBER |
VARCHAR2(25), NOT NULL |
Phone number for the site |
|
NAME |
VARCHAR2(60), NOT NULL, |
Name to start the site's address |
|
COUNTRY |
VARCHAR2(7), NOT NULL |
Region code of the country where the site is located |
|
STATE |
VARCHAR2(7), NOT NULL |
Region code of the state/province/county, etc. where the site is located |
|
CITY |
VARCHAR2(25), NOT NULL |
City where the site is located |
|
ADDRESS_LINE_1 |
VARCHAR2(40), NOT NULL |
First line of the site's address |
|
ADDRESS_LINE_2 |
VARCHAR2(40), NULL |
Second line of the site's address |
|
ADDRESS_LINE_3 |
VARCHAR2(40), NULL |
Third line of the site's address |
|
POSTAL_CODE |
VARCHAR2(15), NOT NULL |
Postal or ZIP code of the site |
|
MODIFICATION_TS |
DATE, NULL |
Date and time the record was last modified |
|
MODIFIED_BY |
VARCHAR2(30), NULL |
User ID of the person who last modified the record |
Parent topic: Investigator Management System
OCL_STUDY_SITES
This is a record of the assignments of a site to a study. The Oracle model assigns sites to a study and then patients to a site for the use of the investigator currently responsible for managing the site. The OCL_STUDY_SITES table is the linchpin around which the assignment of patients and investigators is maintained.
| Parameter | Datatype, Null? | Description |
|---|---|---|
|
SITE_ID |
NUMBER(10,0), NOT NULL |
ID of the site assigned to the study |
|
CLINICAL_STUDY_ID |
NUMBER(10,0), NOT NULL |
ID of the study to which the site has been assigned |
|
DATA_FREEZE_FLAG |
VARCHAR2(1), NOT NULL |
Y = All data for the site is frozen N = Data for site can be changed |
|
CREATED_BY |
VARCHAR2(30), NOT NULL |
User ID of the person who created the record |
|
CREATION_TS |
DATE, NOT NULL |
Date and time the record was created |
|
OK_TO_SHIP_FLAG |
VARCHAR2(1), NOT NULL |
Y = Site is approved to receive drug supplies N = Site is awaiting approval |
|
START_DATE |
DATE, NULL |
Date the site was first assigned to the study |
|
END_DATE |
DATE, NULL |
Date the site was closed |
|
MODIFIED_BY |
VARCHAR2(30), NULL |
User ID of the person who last modified the record. |
|
MODIFICATION_TS |
DATE, NULL |
Date and time the record was last modified |
Parent topic: Investigator Management System
OCL_STUDY_SITE_ROLES
This is a record of the assignment of an investigator to a site in a study. Only one investigator can be responsible for a site within a study at a time.
| Parameter | Datatype, Null? | Description |
|---|---|---|
|
CLINICAL_STUDY_ID |
NUMBER(10,0), NOT NULL |
ID of the study to which the site has been assigned |
|
SITE_ID |
NUMBER(10,0), NOT NULL |
ID of the site assigned to the study |
|
INVESTIGATOR_ID |
NUMBER(10,0), NOT NULL |
ID of the investigator assigned to the site |
|
CREATED_BY |
VARCHAR2(30), NOT NULL |
User ID of the person who created the record |
|
CREATION_TS |
DATE, NOT NULL |
Date and time the record was created |
|
CURRENT_FLAG |
VARCHAR2(1), NOT NULL |
Y = This is the investigator currently responsible for the site N = This is a historic assignment |
|
START_DATE |
DATE, NOT NULL |
Date the assignment started |
|
NUMBER_OF_PATIENTS_REQUIRED |
NUMBER(10,0), NULL |
Number of patients the investigator is targeted with recruiting at this site |
|
CONTRACT_DATE |
DATE, NULL |
Date the investigator's contract was signed |
|
EXPECTED_ENROLLMENT_RATE |
NUMBER(5,2), NULL |
Number of patients the investigator is expected to recruit each month |
|
TERMINATION_DATE |
DATE, NULL |
Date the investigator stopped being responsible for the site. |
|
DISCONTINUATION_LETTER_DATE |
DATE, NULL |
Date the investigator's termination letter was sent |
|
MODIFICATION_TS |
DATE, NULL |
Date and time the record was last modified |
|
MODIFIED_BY |
VARCHAR2(30), NULL |
User ID of the person who last modified the record |
Parent topic: Investigator Management System
Study Planning
There are six study planning tables:
- OCL_ORGANIZATION_UNITS
- OCL_PROGRAMS
- OCL_PROJECTS
- OCL_PROGRAM_PRODUCT_MASTERS
- OCL_STUDIES
- OCL_STUDY_REGIONS
Parent topic: Table Details
OCL_ORGANIZATION_UNITS
This table contains a list of organizations within the company that manage or sponsor studies. A study must be assigned to one of these organizations.
| Parameter | Datatype, Null? | Description |
|---|---|---|
|
ORGANIZATION_ UNIT_ID |
NUMBER(10), NOT NULL |
Unique ID for the organization unit |
|
CODE |
VARCHAR2(3), NOT NULL |
Code for the organization unit as recognized by the user |
|
NAME |
VARCHAR2(60), NULL |
Name for the organization unit |
|
DESCRIPTION |
VARCHAR2(70), NOT NULL |
Description of the organization unit |
|
START_DATE |
DATE, NOT NULL |
Date the organization unit became active |
|
END_DATE |
DATE, NULL |
Date the organization unit was retired |
|
CREATED_BY |
VARCHAR2(30), NOT NULL |
User ID of the person who created the record |
|
CREATION_TS |
DATE, NOT NULL |
Date and time the record was created |
|
MODIFIED_BY |
VARCHAR2(30), NULL |
User ID of the person who last modified the record. |
|
MODIFICATION_TS |
DATE, NULL |
Date and time the record was last modified. |
Parent topic: Study Planning
OCL_PROGRAMS
Programs are the upper tier in the studies hierarchy. They group the studies within a particular compound, which could include multiple indications and formulations of the compound across all locations where studies are being performed.
| Parameter | Datatype, Null? | Description |
|---|---|---|
|
PROGRAM_CODE |
VARCHAR2(15), NOT NULL |
User code for the program |
|
DESCRIPTION |
VARCHAR2(70), NOT NULL |
Description of the program |
|
ACTIVE_FLAG |
VARCHAR2(1), NOT NULL |
Y = Program is still active N = Program is for historical use only |
|
CREATED_BY |
VARCHAR2(30), NOT NULL |
User ID of the person who created the record |
|
CREATION_TS |
DATE, NOT NULL |
Date and time the record was created |
|
MODIFIED_BY |
VARCHAR2(30), NULL |
User ID of the person who last modified the record |
|
MODIFICATION_TS |
DATE, NULL |
Date and time the record was last modified |
Parent topic: Study Planning
OCL_PROJECTS
Projects are the lower tier in the studies hierarchy. They group studies in a program into some customer-determined groups. Typical groups could split studies by indications or formulations of the compound, and possibly by locations or regions.
| Parameter | Datatype, Null? | Description |
|---|---|---|
|
PROJECT_CODE |
VARCHAR2(15), NOT NULL |
Code for the project |
|
DESCRIPTION |
VARCHAR2(70), NOT NULL |
Description of the project |
|
START_DATE |
DATE, NOT NULL |
Date the project started |
|
END_DATE |
DATE, NULL |
Date the project ended |
|
PROGRAM_CODE |
VARCHAR2(15), NOT NULL |
Code of the program to which the project belongs |
|
CREATED_BY |
VARCHAR2(30), NOT NULL |
User ID of the person who created the record |
|
CREATION_TS |
DATE, NOT NULL |
Date and time the record was created |
|
MODIFIED_BY |
VARCHAR2(30), NULL |
User ID of the person who last modified the record |
|
MODIFICATION_TS |
DATE, NULL |
Date and time the record was last modified |
Parent topic: Study Planning
OCL_PROGRAM_PRODUCT_MASTERS
This lists type ACT SUB (active substance) product masters, assigned to programs.
| Parameter | Datatype, Null? | Description |
|---|---|---|
|
PROGRAM_CODE |
VARCHAR2(15), NOT NULL |
Code of the program concerned |
|
PM_ID |
NUMBER(10,0), NOT NULL |
ID of the product master assigned |
|
PRIMARY_RS_COMPOUND_FLAG |
VARCHAR2(1), NOT NULL |
Y = Main compound of the program N = Other compounds assigned to the program |
|
CREATED_BY |
VARCHAR2(30),NOT NULL |
User ID of the person who created the record |
|
CREATION_TS |
DATE, NOT NULL |
Date and time the record was created |
|
MODIFIED_BY |
VARCHAR2(30), NULL |
User ID of the person who last modified the record |
|
MODIFICATION_TS |
DATE, NULL |
Date and time the record was last modified |
Parent topic: Study Planning
OCL_STUDIES
This table lists all studies within a set of instances that share data — all studies ever performed, currently being performed, or might be performed in the future. A study must exist in this table before it can be designed in Oracle Clinical.
| Parameter | Datatype, Null? | Description |
|---|---|---|
|
TASK_ID |
NUMBER(10), NOT NULL |
Unique ID for the planned study |
|
STUDY |
VARCHAR2(15), NOT NULL |
User-recognized code of the study |
|
CREATED_BY |
VARCHAR2(30), NOT NULL |
User ID of the person who created the record |
|
CREATION_TS |
DATE, NOT NULL |
Date and time the record was created |
|
ORGANIZATION_UNIT_ID |
NUMBER(10), NOT NULL, |
Code of the organization sponsoring the study |
|
PROJECT_CODE |
VARCHAR2(15), NOT NULL |
Code of the project to which the study belongs |
|
PROGRAM_CODE |
VARCHAR2(15), NOT NULL |
Code of the program that owns the project to which the study belongs |
|
TITLE |
VARCHAR2(255), NULL |
Title for the study |
|
INVESTIGATORS_PLANNED |
NUMBER(10), NULL |
Planned number of investigators in the study |
|
EXPTL_DESIGN_TYPE_CODE |
VARCHAR2(5), NULL |
Type of design being used in the study |
|
REGION_ID |
NUMBER(10), NULL |
ID of the master region for the study |
|
CLINICAL_PHASE |
VARCHAR2(5), NULL |
Clinical phase of the study |
|
MODIFIED_BY |
VARCHAR2(30), NULL |
User ID of the person who last modified the record |
|
MODIFICATION_TS |
DATE, NULL |
Date and time the record was last modified |
Parent topic: Study Planning
OCL_STUDY_REGIONS
This table lists the regions where a study is to be performed. One region must be flagged as the master region— the one displayed in the heads of some reports.
| Parameter | Datatype, Null? | Description |
|---|---|---|
|
STUDY_REGION_ID |
NUMBER(10), NOT NULL, |
ID for the study region record |
|
TASK_ID |
NUMBER(10), NOT NULL |
ID of the study concerned |
|
REGION_ID |
NUMBER(10), NOT NULL, |
ID of the region concerned |
|
REGION_CODE |
VARCHAR2(7), NOT NULL |
Code of the region concerned |
|
REGULATORY_ FLAG |
VARCHAR2(1), NOT NULL |
Y = Region is being used for regulatory reasons N = Region is not being used for regulatory reasons |
|
MARKETING_FLAG |
VARCHAR2(1), NOT NULL |
Y = Region is being used for marketing reasons N = Region is not being used for marketing reasons |
|
REPORTING_REGION_FLAG |
VARCHAR2(1), NOT NULL |
Y = Region is the main reporting region N = Region is not the main reporting region |
|
CREATED_BY |
VARCHAR2(30),NOT NULL |
User ID of the person who created the record |
|
CREATION_TS |
DATE, NOT NULL |
Date and time the record was created |
|
MODIFIED_BY |
VARCHAR2(30), NULL |
User ID of the person who last modified the record |
|
MODIFICATION_TS |
DATE, NULL |
Date and time the record was last modified |
Parent topic: Study Planning