Search Results special_review_code




Overview
Explore in AI Chat

Overview

The IGW_PROP_SPECIAL_REVIEWS table is a core data object within the Oracle E-Business Suite Grants Accounting module (IGW), specifically for versions 12.1.1 and 12.2.2. It serves as the central repository for tracking all special reviews associated with research proposals. A special review pertains to mandatory regulatory or ethical oversight, primarily concerning human subjects or vertebrate animals, as required for research compliance. The table's role is to maintain a detailed history of each review's type, status, approval details, and associated protocol information, ensuring proposals adhere to institutional and regulatory standards before funding is awarded.

Key Information Stored

The table stores comprehensive data points for each proposal's special review record. Key columns include the mandatory PROPOSAL_ID and SPECIAL_REVIEW_CODE, which together form the table's primary key, linking the review to a specific proposal and defining the review category from a lookup (IGW_SPECIAL_REVIEWS). Critical status and detail columns are SPECIAL_REVIEW_TYPE, APPROVAL_TYPE_CODE, and APPROVAL_DATE. For approved reviews, the PROTOCOL_NUMBER is a required field. Additional descriptive data is held in TITLE (Protocol Title), SUBJECT_ENROLLMENT_CODE, EXEMPTION_NUMBER, and a COMMENTS field. The table includes standard Oracle EBS WHO columns (CREATED_BY, LAST_UPDATE_DATE, etc.) and a RECORD_VERSION_NUMBER for optimistic locking. The unique surrogate key for each row is PROP_SPECIAL_REVIEW_ID.

Common Use Cases and Queries

This table is essential for compliance reporting and proposal lifecycle management. Common use cases include generating reports for institutional review boards (IRBs), auditing proposal compliance statuses, and validating that all required reviews are complete before proposal submission or award activation. A fundamental query retrieves all special reviews for a specific proposal, often joining to lookup tables for descriptive values:

  • Basic Review Listing: SELECT special_review_code, approval_type_code, protocol_number, approval_date FROM igw.igw_prop_special_reviews WHERE proposal_id = :p_proposal_id;
  • Compliance Status Check: Queries often filter on APPROVAL_TYPE_CODE to identify pending or approved reviews.
  • Reporting on Human Subject Protocols: A typical report would join to IGW_PROPOSALS_ALL and filter on SPECIAL_REVIEW_CODE values related to human subjects to list all associated protocol numbers and approval dates.

Related Objects

The IGW_PROP_SPECIAL_REVIEWS table has defined relationships with other core Grants Accounting entities. Its primary key constraint is IGW_PROP_SPECIAL_REVIEW_PK on (PROPOSAL_ID, SPECIAL_REVIEW_CODE). The most significant documented foreign key relationship is a reference to the main proposals table, enforcing data integrity:

  • IGW_PROPOSALS_ALL: The column IGW_PROP_SPECIAL_REVIEWS.PROPOSAL_ID references IGW_PROPOSALS_ALL. This ensures every special review record is linked to a valid, existing proposal.

Furthermore, key columns such as SPECIAL_REVIEW_CODE, SPECIAL_REVIEW_TYPE, and APPROVAL_TYPE_CODE are validated against corresponding lookup tables (IGW_SPECIAL_REVIEWS, IGW_SPECIAL_REVIEW_TYPES, IGW_REVIEW_APPROVAL_TYPES), which are critical for obtaining meaningful descriptions in reports.