| Oracle® Clinical Interfacing from Oracle Clinical Release 4.6 Part Number A83793-06 |
|
|
View PDF |
| Oracle® Clinical Interfacing from Oracle Clinical Release 4.6 Part Number A83793-06 |
|
|
View PDF |
Oracle Clinical interfaces with your company's applications in the areas of study planning, investigator management, and drug supply. The interface provides tables and modules that support basic requirements in these areas. You can use these Oracle Clinical tables and modules at your site, or you can replace them with tables and modules from your system. Oracle Clinical provides the means for validating responses using your company's own thesaurus system.
This chapter has the following sections:
The Oracle Clinical tables and modules that are part of the Interface Configuration interface with external applications. The table names begin with OCL_. You can use these Oracle Clinical tables and modules at your own sites; alternatively, you can choose the following methods for your interface:
Batch/parallel maintenance of the OCL_ tables from customer applications
Mapping can be limited to the tables and columns required by the core Oracle Clinical functionality and does not have to reproduce exactly the Oracle Clinical version of these tables, described in "Detailed Table Layouts".
Note:
If you customized the interface then do not maintain these tables with Oracle Clinical functions.Pay particular attention to performance when you test your modifications. This observation has special importance when views have contributed to the mapping, because they can seriously affect performance. Also, the underlying structure and indexing of the customer application may not be tuned to meet Oracle Clinical access requirements.
The following sections describe the layout and content of each Oracle Clinical table and the required components that a customized interface supports.
Study planning is the process of controlling and managing all studies within a company and across all company locations. The standard procedure is to define studies only at the company location where they are to be conducted or analyzed. Study planning also contains information about studies that are to be run by outside organizations, such as CROs, as well as details of possible future studies.
Oracle Clinical requires that study planning provide:
A two-tier hierarchy of studies to which users can be assigned as an alternative to maintaining security by individual user assignments to studies
A list of studies that are approved for definition in Oracle Clinical
Table 2-1 Study Planning Tables
| Table | Description |
|---|---|
|
OCL_STUDIES |
Master list of all studies |
|
OCL_STUDY_REGIONS |
List of regions where each study is to be conducted |
|
OCL_ORGANIZATION_UNITS |
List of company departments that can support studies |
|
OCL_PROGRAMS |
Upper tier where studies can be grouped and security managed |
|
OCL_PROJECTS |
Lower tier where studies can be grouped and security managed |
|
OCL_PROGRAM_PRODUCT_MASTERS |
List of the compounds for which each program is responsible |
A drug supply system interacts with Oracle Clinical in two ways:
At the front end of the clinical process, the drug supply system provides a list of drugs formulated for a clinical study.
At the back end of the process, the drug supply system takes the randomization generated in the design subsystem of Oracle Clinical and produces from it the packaged and labeled supplies for the study.
An investigator management system is responsible for maintaining a list of the investigators who can work on studies and the records of their assignments to studies.
Table 2-3 Investigator Management Tables
| Table | Description |
|---|---|
|
OCL_INVESTIGATORS |
List of potential and actual investigators who can work on studies |
|
OCL_SITES |
List of locations where a clinical study can be conducted |
|
OCL_STUDY_SITES |
List of the assignments of sites to a particular clinical study |
|
OCL_STUDY_SITE_ROLES |
List of which investigators have been or are responsible for each study site |
The procedures described in the following sections maintain referential integrity between Oracle Clinical and another customer application. You must maintain independence between Oracle-supplied code and customer applications, while maintaining referential integrity and complying with site support agreement rules. These requirements are provided for by a set of database procedures that can be maintained and used either by Oracle or by the customer.
The following sections list each test or update separately; in the actual implementation some are merged. Local procedures may differ, but the parameters and procedure names are fixed.
Prevents a study from being deleted by checking in related tables in non-Oracle Clinical applications before a clinical study is deleted. Only a return value of OK allows the study to be deleted; anything else means failure.
vReturnMsg out Char(60)
Deletes records from the OCL tables used by Oracle Clinical. A return value of DONE means that everything worked correctly; anything else means failure.
vReturnMsg out Char(60)
Deletes clinical study-related records from non-Oracle Clinical tables when a clinical study is deleted. A return value of DONE means that everything worked correctly; anything else means failure.
vReturnMsg out Char(60)
Checks if a region can be deleted based on local usage. A return value of DONE means that everything worked correctly; anything else means failure.
vRegionCode in char(7)
vReturnMsg out Char(60)
Applies a cascade update when a region code is changed.
vNewRegionCode in varchar2(7)
vReturnMsg out varchar2(60)
Checks if a treatment pattern can be deleted based on local usage. A return value of DONE means that everything worked correctly; anything else means failure.
vReturnMsg out char(60)
Checks if a treatment assignment can be deleted based on local usage. A return value of DONE means everything worked correctly; anything else means failure.
vStartCode in char(10)
vreturnMsg out char(60)
Supplies the value of a STUDY_SITE code.
nStudyId in number(10)
vSiteCode in varchar2(10)
nSiteId in number(10)
vTestProd in varchar2(1)
vReturnStudySite out varchar2(10)
Validates a STUDY_SITE code. A return value of OK means the value is acceptable.
nStudyId in number(10)
vSiteCode in varchar2(10)
nSiteId in number(10)
vTestProd in varchar2(1)
vStudySite in varchar2(10)
vReturnMsg out varchar2(60)
Checks whether a STUDY_SITE code can be changed.
nStudyId in number(10)
vSiteCode in varchar2(10)
nSiteId in number(10)
vTestProd in varchar2 1)
vOldStudySite in varchar2(10)
vNewStudySite in varchar2(10)
vReturnMsg out varchar2(60)
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.
There are two drug supply tables, OCL_DOSAGE_FORMS and OCL_PRODUCT_MASTERS.
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 |
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 |
There are four investigator management system tables, OCL_INVESTIGATORS, OCL_SITES, OCL_STUDY_SITES, and OCL_STUDY_SITE_ROLES.
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 |
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 |
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 |
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 |
There are six study planning tables, OCL_ORGANIZATION_UNITS, OCL_PROGRAMS, OCL_PROJECTS, OCL_PROGRAM_PRODUCT_MASTERS, OCL_STUDIES, and OCL_STUDY_REGIONS.
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. OCL_PROPOSERS is a view of the OCL_ORGANIZATIONS table.
| 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. |
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 |
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 |
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 |
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 |
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 |
|
 Copyright © 1998, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
