Search Results subject_information_id

Overview

IGW.IGW_SUBJECT_INFORMATION is a transactional table in the Oracle E-Business Suite IGW (Grants and Clinical Trials) schema that stores information about the subjects enrolled or planned for a clinical study. Each row describes a distinct combination of study, subject type, race, and ethnicity, along with the number of subjects falling into that cohort. The table is registered in FND Design Data as IGW.IGW_SUBJECT_INFORMATION, currently holds VALID status, and resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. Its indexes are stored separately in APPS_TS_TX_IDX.

From a dimensional modeling perspective, the mined relationship data classifies this object as satellite-leaning. The heuristic suggests that IGW_SUBJECT_INFORMATION behaves as a satellite attached to the study-title hub (IGW_STUDY_TITLES via STUDY_TITLE_ID), carrying descriptive attributes such as subject type, race, ethnicity, and subject counts rather than acting as an independent hub or a many-to-many link. This classification is advisory and should be validated against the actual reporting grain in use.

Key Information Stored

The table contains twelve documented columns. The most significant are:

• SUBJECT_INFORMATION_ID — Surrogate identifier for the subject information record, stored as NUMBER(15).
• STUDY_TITLE_ID — Foreign key to IGW_STUDY_TITLES, identifying the parent study.
• SUBJECT_TYPE_CODE — VARCHAR2(30), unique code identifying the type of subject (e.g., healthy volunteer, patient). This is the column most frequently searched in support and reporting contexts.
• SUBJECT_RACE_CODE — VARCHAR2(30), code identifying subject race.
• SUBJECT_ETHNICITY_CODE — VARCHAR2(30), code identifying subject ethnicity.
• NO_OF_SUBJECTS — NUMBER(15), the count of subjects in the given study/type/race/ethnicity combination.
• RECORD_VERSION_NUMBER — Locking sequence number used for optimistic concurrency control.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard WHO audit columns.

The documented unique index IGW_SUBJECT_INFORMATION_U1 spans STUDY_TITLE_ID, SUBJECT_RACE_CODE, SUBJECT_ETHNICITY_CODE, and SUBJECT_TYPE_CODE, and is mirrored by the primary key IGW_SUBJECT_INFORMATION_PK. These four columns therefore constitute the composite business key, while SUBJECT_INFORMATION_ID and RECORD_VERSION_NUMBER serve as surrogate and concurrency identifiers respectively.

Common Use Cases and Queries

Typical uses include reporting subject demographics per study, reconciling enrollment counts, and validating compliance with diversity requirements in clinical trials. A common pattern groups counts by subject type and race:

• SELECT STUDY_TITLE_ID, SUBJECT_TYPE_CODE, SUM(NO_OF_SUBJECTS) FROM IGW.IGW_SUBJECT_INFORMATION GROUP BY STUDY_TITLE_ID, SUBJECT_TYPE_CODE;
• SELECT * FROM IGW.IGW_SUBJECT_INFORMATION WHERE SUBJECT_TYPE_CODE = :type;
• Join to IGW_STUDY_TITLES on STUDY_TITLE_ID to retrieve the study title alongside demographic breakdowns.

Because the table has no child dependents, it is typically queried directly or joined outward to the study title table rather than being traversed downward.

Related Objects

The most significant related object is IGW.IGW_STUDY_TITLES, referenced by the STUDY_TITLE_ID foreign key. The APPS synonym APPS.IGW_SUBJECT_INFORMATION exposes the table to the application layer, and the unique index IGW_SUBJECT_INFORMATION_U1 and primary key IGW_SUBJECT_INFORMATION_PK provide enforced access paths. The ETRM documentation notes that IGW_SUBJECT_INFORMATION does not reference any database object other than IGW_STUDY_TITLES and is not referenced by any downstream object, confirming its role as a leaf-level satellite within the IGW schema.