Search Results igw_prop_science_codes_pk

Overview

IGW_PROP_SCIENCE_CODES is a child table within the Oracle E-Business Suite Grants Proposal (IGW) module, which is documented as obsolete in Oracle EBS 12.1.1 and 12.2.2. The table stores the association between a proposal and the scientific keywords or science codes used in preparing that proposal. Functionally, it is a cross-reference table that records which science codes apply to a given proposal record.

In the ETRM documentation, the table is listed as "Not implemented in this database." Its columns and constraints remain documented for reference purposes, but it is not present in current 12.1.1 or 12.2.2 installations. Any integration or reporting built against this table must account for its obsolete status.

Based on the foreign key structure, the table can be classified as a link in a Data Vault modeling sense. It sits between two hubs — IGW_PROPOSALS_ALL and IGW_SCIENCE_CODES — and resolves the many-to-many relationship between proposals and science codes. It is not a hub (it carries no independent business key of its own beyond the composite) and not a satellite (it does not store descriptive history about a single entity).

Key Information Stored

The table comprises 11 documented columns. The most significant include:

• PROPOSAL_ID — the foreign key referencing IGW_PROPOSALS_ALL; identifies the proposal that owns the science code assignment.
• SCIENCE_CODE — the foreign key referencing IGW_SCIENCE_CODES; identifies the keyword applied to the proposal.
• PROP_SCIENCE_CODE_ID — a surrogate identifier column associated with the science code record on the proposal.
• PROP_PROGRAM_ID — links the assignment to a specific program context within the proposal structure.
• KEYWORD_OBJECT — a descriptive or object reference column for the keyword entry.
• RECORD_VERSION_NUMBER — supports optimistic locking and concurrency control.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard Oracle EBS WHO-column audit fields.

The primary key is IGW_PROP_SCIENCE_CODES_PK, defined on (PROPOSAL_ID, SCIENCE_CODE). A unique index, IGW_PROP_SCIENCE_CODES_U1, exists on the same two columns, making the composite pair the business-key candidate. The surrogate PROP_SCIENCE_CODE_ID does not appear as the primary key in the documented metadata but may serve as an alternate identifier.

Common Use Cases and Queries

Because the table is obsolete, typical uses are historical or migration-related. In legacy reporting, a common query pattern joins the link back to both parents to list science codes per proposal:

• Retrieve all science codes for a proposal: SELECT p.proposal_id, s.science_code FROM igw_prop_science_codes p, igw_science_codes s WHERE p.science_code = s.science_code AND p.proposal_id = :proposal_id;
• Count keyword assignments per proposal for analytics or audit: SELECT proposal_id, COUNT(*) FROM igw_prop_science_codes GROUP BY proposal_id;
• Identify orphaned or unused science codes by comparing against IGW_SCIENCE_CODES.
• Audit last-updated users via LAST_UPDATED_BY and LAST_UPDATE_DATE to support data-quality reviews.

For migration to a successor module, the PROP_PROGRAM_ID and KEYWORD_OBJECT columns help map legacy assignments to new structures. Note that since the table is not implemented, these queries are applicable only to historical or archived databases.

Related Objects

The following objects are directly related through the documented constraints:

• IGW_PROPOSALS_ALL — parent table; joined via IGW_PROP_SCIENCE_CODES.PROPOSAL_ID = IGW_PROPOSALS_ALL.PROPOSAL_ID.
• IGW_SCIENCE_CODES — parent/lookup table; joined via IGW_PROP_SCIENCE_CODES.SCIENCE_CODE = IGW_SCIENCE_CODES.SCIENCE_CODE.
• IGW_PROP_SCIENCE_CODES_PK — the primary key constraint on (PROPOSAL_ID, SCIENCE_CODE).
• IGW_PROP_SCIENCE_CODES_U1 — unique index reinforcing the business-key candidate.

Indirectly, this table participates in the broader IGW proposal model alongside proposal program and keyword structures referenced by PROP_PROGRAM_ID and KEYWORD_OBJECT. Given the obsolete status, no current APIs or concurrent programs depend on it in 12.1.1 or 12.2.2.