Search Results special_review_code

Overview

The IGW_PROP_SPECIAL_REVIEWS table is a data object within the Oracle E-Business Suite (EBS) Grants Proposal module (IGW). As indicated by the provided ETRM metadata, this module is designated as obsolete. The table's core function is to store records of special reviews conducted for a specific grant proposal. These reviews typically pertain to regulatory, ethical, or compliance assessments, such as those required for human subjects research (IRB), animal welfare (IACUC), biohazard safety, or conflict of interest disclosures. Its role is to maintain a historical and current log of these mandatory compliance checks associated with a proposal, ensuring that all necessary institutional approvals are documented and linked to the funding request.

Key Information Stored

The table's structure, as defined by its primary and foreign keys, centers on linking a review record to a specific proposal and classifying the type of review. The primary key is a composite of PROPOSAL_ID and SPECIAL_REVIEW_CODE, enforcing uniqueness for each type of review on a given proposal. The PROPOSAL_ID column is a foreign key that links directly to the IGW_PROPOSALS_ALL table, anchoring the review data to a master proposal record. The SPECIAL_REVIEW_CODE column categorizes the nature of the compliance review. While the full column list is not detailed in the excerpt, typical attributes for such a table would include status (e.g., Pending, Approved, Exempt), approval date, review committee identifier, expiration date, and any associated comments or document references.

Common Use Cases and Queries

This table supports critical compliance reporting and proposal lifecycle management. Common operational use cases include generating a checklist of required reviews for a proposal, tracking pending approvals before proposal submission, and validating that all necessary reviews are complete and current. For reporting, administrators run queries to audit institutional compliance across all active proposals or to identify proposals with expiring approvals. A fundamental query pattern involves joining to the proposals table to retrieve proposal details alongside review statuses.

• Sample Query: To list all proposals with their associated special review types and statuses:
  SELECT p.PROPOSAL_NUMBER, p.TITLE, r.SPECIAL_REVIEW_CODE, r.STATUS
FROM IGW_PROP_SPECIAL_REVIEWS r, IGW_PROPOSALS_ALL p
WHERE r.PROPOSAL_ID = p.PROPOSAL_ID
ORDER BY p.PROPOSAL_NUMBER;
• Critical Note: The provided ETRM metadata explicitly states this table is "Not implemented in this database." Therefore, while the logical design exists, the physical table may not be present, and any queries would fail in a standard deployment.

Related Objects

The table's primary relationship is defined by its foreign key dependency on the IGW_PROPOSALS_ALL table, which is the central repository for proposal header information. This relationship ensures referential integrity, meaning a special review record cannot exist without a corresponding master proposal. Given the module's obsolete status and the note that the table is not implemented, it is unlikely to have extensive downstream dependencies like public APIs or key views. Any existing custom integrations or reports referencing this object would be specific to an organization's implementation and would rely on the table being manually created and populated, as it is not part of the standard delivered database schema.