| Oracle® Clinical Stable Interface Technical Reference Manual Release 5.0 E36999-01 |
|
|
PDF · Mobi · ePub |
| Oracle® Clinical Stable Interface Technical Reference Manual Release 5.0 E36999-01 |
|
|
PDF · Mobi · ePub |
This chapter 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."
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".
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 |
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) |
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 |
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 |
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 |
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 |
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.
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 |
| 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 |
|
 Copyright © 2001, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
