Results for “special_review_code”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IGW_PROP_SPECIAL_REVIEWS_V is a reporting view within the Oracle E-Business Suite Grants Proposal (IGW) module. The ETRM metadata classifies this object under the IGW product family and labels it "Obsolete," with a scope note of "10SC Only." This designation indicates that the view was originally constructed for a specific deployment context (release 10SC) and is not implemented in the current database against which the ETRM documentation was captured. The metadata explicitly states "Not implemented in this database," meaning the view text is preserved for reference purposes but the object does not physically exist in the target environment.

Functionally, the view presents special review records associated with grant proposals, enriching raw code columns with their human-readable descriptions by joining to the FND_LOOKUPS table three times. It serves as a denormalized, presentation-layer construct intended to support reporting and integration scenarios where descriptive lookup meanings are required alongside stored codes, eliminating the need for downstream consumers to resolve those codes independently.

Underlying Base Objects

The view is defined over a single transactional base table, IGW_PROP_SPECIAL_REVIEWS, aliased as S. Three lookups on the FND_LOOKUPS table (aliased L1, L2, and L3) are joined to translate stored codes into descriptive text.

• IGW_PROP_SPECIAL_REVIEWS (S) — the primary source of special review data for proposals.
• FND_LOOKUPS L1 — an inner join on LOOKUP_TYPE = 'IGW_SPECIAL_REVIEWS', resolving SPECIAL_REVIEW_CODE to its MEANING.
• FND_LOOKUPS L2 — an outer join (+) on LOOKUP_TYPE = 'IGW_SPECIAL_REVIEW_TYPES', resolving SPECIAL_REVIEW_TYPE.
• FND_LOOKUPS L3 — an outer join (+) on LOOKUP_TYPE = 'IGW_REVIEW_APPROVAL_TYPES', resolving APPROVAL_TYPE_CODE.

The asymmetric join structure is significant: because L1 is an inner join, any special review row whose SPECIAL_REVIEW_CODE lacks a matching active lookup entry in IGW_SPECIAL_REVIEWS is excluded from the result set entirely. The L2 and L3 outer joins, by contrast, permit rows to survive even when the special review type or approval type is null or unmatched, returning a null description.

Key Columns

The view exposes a ROWID pseudo-column along with the following documented columns:

• PROPOSAL_ID — the identifier linking the review to a specific proposal record.
• SPECIAL_REVIEW_CODE and SPECIAL_REVIEW_DESC — the stored code and its resolved meaning from IGW_SPECIAL_REVIEWS lookups.
• SPECIAL_REVIEW_TYPE and SPECIAL_REVIEW_TYPE_DESC — the review classification and its optional description.
• APPROVAL_TYPE_CODE and APPROVAL_TYPE_DESC — the approval type code searched by the user and its optional description, resolved via the IGW_REVIEW_APPROVAL_TYPES lookup type.
• PROTOCOL_NUMBER — the associated protocol reference for the review.
• APPLICATION_DATE and APPROVAL_DATE — the dates the review was applied for and approved.
• COMMENTS — free-text remarks attached to the review.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN, supporting standard EBS audit tracking.

Common Use Cases and Queries

The primary use case is retrieving special reviews for a proposal with resolved descriptions, particularly when filtering by approval type. A representative query follows:

• SELECT proposal_id, special_review_desc, special_review_type_desc, approval_type_code, approval_type_desc, approval_date FROM igw_prop_special_reviews_v WHERE proposal_id = :p_proposal_id;
• SELECT proposal_id, approval_type_code, approval_type_desc FROM igw_prop_special_reviews_v WHERE approval_type_code IS NOT NULL;
• SELECT proposal_id, protocol_number, application_date, approval_date FROM igw_prop_special_reviews_v WHERE approval_date IS NULL;

Because the metadata records the view as obsolete and not implemented, these queries apply only in environments where the object was originally deployed. In current releases, equivalent data and description resolution should be sourced from the underlying IGW_PROP_SPECIAL_REVIEWS table joined directly to FND_LOOKUPS.