Search Results prop_science_code_id




Overview
Explore in AI Chat

Overview

IGW.IGW_PROP_SCIENCE_CODES is a transactional child table within the Oracle E-Business Suite Grants and Proposal Management (IGW) product family. It stores the keywords or science codes that have been attached to a proposal during proposal preparation. Science codes must first be defined in the parent IGW_SCIENCE_CODES reference table before they can be associated with a proposal; the child table only records the assignment, not the master definition.

In the documented physical schema for ETRM 12.1.1 (and similarly in 12.2.2), the table resides in the IGW schema and is stored in the APPS_TS_TX_DATA tablespace with PCTFREE 10. It carries 11 columns, a unique index named IGW_PROP_SCIENCE_CODES_U1 on (PROPOSAL_ID, SCIENCE_CODE), and a primary key constraint IGW_PROP_SCIENCE_CODES_PK on the same column pair.

From a heuristic Data Vault modeling perspective, this object is best classified as a link. It resolves a many-to-many relationship between proposals (IGW_PROPOSALS_ALL) and predefined science codes (IGW_SCIENCE_CODES), and its composite unique key consists entirely of foreign key attributes pointing to those two parent hubs. The remaining descriptive columns — notably KEYWORD_OBJECT and PROP_PROGRAM_ID — act as link-level descriptive attributes rather than a separate satellite.

Key Information Stored

The columns below are the most significant for understanding and querying the table:

  • PROP_SCIENCE_CODE_ID (NUMBER 15) — Surrogate unique identifier for the row, referenced by the user search term "prop_science_code_id".
  • PROPOSAL_ID (NUMBER 15) — Proposal identifier; first component of the primary key and unique index, and foreign key to IGW_PROPOSALS_ALL.
  • SCIENCE_CODE (VARCHAR2 15) — The keyword used in preparing the proposal; second component of the primary key/unique index and foreign key to IGW_SCIENCE_CODES.
  • PROP_PROGRAM_ID (NUMBER) — Program identifier, populated only for Program Keywords; the value −1 is used for Proposal Keywords.
  • KEYWORD_OBJECT (VARCHAR2 30) — Denotes the origin of the keyword (for example, proposal-level versus program-level keyword).
  • RECORD_VERSION_NUMBER (NUMBER 15) — Locking sequence number used for optimistic concurrency control.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns recording creation and last-update timestamps and user identifiers.

Business-key candidates are therefore (PROPOSAL_ID, SCIENCE_CODE), enforced by the unique index IGW_PROP_SCIENCE_CODES_U1 and the primary key, while PROP_SCIENCE_CODE_ID serves as the surrogate identifier.

Common Use Cases and Queries

Typical usage involves retrieving, inserting, or reporting on the keywords attached to a proposal. The canonical projection documented for the object is:

  • SELECT PROPOSAL_ID, SCIENCE_CODE, PROP_SCIENCE_CODE_ID, PROP_PROGRAM_ID, KEYWORD_OBJECT FROM IGW.IGW_PROP_SCIENCE_CODES;
  • Listing all keywords for a given proposal: filter by PROPOSAL_ID, optionally joining IGW_SCIENCE_CODES to obtain full descriptions.
  • Separating proposal-level from program-level keywords: WHERE PROP_PROGRAM_ID = -1 returns proposal keywords, while any other PROP_PROGRAM_ID value identifies program keywords.
  • Audit and change-tracking reports using CREATION_DATE, LAST_UPDATE_DATE, and RECORD_VERSION_NUMBER.

Because there is no public API in the metadata, integrations typically insert and delete child rows directly or through the corresponding OAF/forms layer, always respecting the composite unique key.

Related Objects

IGW_PROP_SCIENCE_CODES does not reference other database objects beyond its two foreign keys, and is itself referenced only by the APPS synonym IGW_PROP_SCIENCE_CODES. The most significant related objects are:

  • IGW.IGW_PROPOSALS_ALL — Parent proposal entity, joined on PROPOSAL_ID.
  • IGW.IGW_SCIENCE_CODES — Master keyword definition table, joined on SCIENCE_CODE.
  • APPS.IGW_PROP_SCIENCE_CODES — The APPS synonym through which the table is normally queried by application code.
  • Supporting proposal and keyword components that surface these rows in the Proposal Management UIs.

Together these objects form the keyword-assignment relationship used throughout proposal preparation in Oracle Grants Management and Proposal Management.