Results for “sponsor_proposal_number”

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

Overview

The IGW_PROPOSALS_ALL table is the central transactional repository within the Oracle E-Business Suite Grants Proposal (IGW) module. It stores the core information about grant proposals submitted by an organization, serving as the primary anchor point for the entire proposal lifecycle — from creation and internal routing through sponsor submission and eventual award conversion. In Oracle EBS 12.1.1 and 12.2.2, the table is owned by the IGW schema and is classified as VALID in the data dictionary. Its documented physical schema under ETRM 12.1.1 comprises 83 columns.

From a dimensional modeling perspective, a heuristic Data Vault classification of this object is hub. This reflects its role as a business-key-bearing entity whose surrogate primary key (PROPOSAL_ID) is broadcast outward as a foreign key to a large family of dependent detail tables. Rather than storing descriptive context itself, the table anchors identity and state, with satellites representing abstracts, budgets, narratives, persons, and locations.

Key Information Stored

The primary key is the surrogate PROPOSAL_ID, enforced by the IGW_PROPOSALS_PK constraint. Two documented unique indexes identify business-key candidates: IGW_PROPOSALS_ALL_U1 (PROPOSAL_ID) and IGW_PROPOSALS_ALL_U2 (PROPOSAL_NUMBER), making PROPOSAL_NUMBER the natural business key most commonly exposed to end users.

• PROPOSAL_NUMBER / PROPOSAL_TITLE / PROPOSAL_SHORT_TITLE — human-readable identification of the proposal.
• PROPOSAL_STATUS — lifecycle state (draft, submitted, awarded, withdrawn) and the principal filter for reporting.
• LEAD_ORGANIZATION_ID and SUBMITTING_ORGANIZATION_ID — foreign keys to HR_ALL_ORGANIZATION_UNITS identifying the performing and submitting units.
• SPONSOR_ID / FUNDING_SPONSOR_UNIT / SPONSOR_PROPOSAL_NUMBER — the funding source and the sponsor's own reference identifier.
• PROPOSAL_START_DATE / PROPOSAL_END_DATE — the proposed performance period.
• DEADLINE_DATE / DEADLINE_TYPE / LETTER_OF_INTENT_DUE_DATE — submission timing controls.
• PROPOSAL_MANAGER_ID / PROGRAM_MANAGER_ID / SIGNING_OFFICIAL_ID — responsible parties.
• TOTAL_AMOUNT / FUNDED_DIRECT_COST / FUNDED_INDIRECT_COST — monetary summary values used in financial rollups.
• ORG_ID — the multi-org operating unit discriminator, essential for row-level security.
• XML_CLOB — the EDI/sponsor payload, relevant for electronic submission integration.

Standard WHO audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and the 15 ATTRIBUTE flex columns are also present.

Common Use Cases and Queries

Typical reporting retrieves active proposals by organization and deadline. For example:

• SELECT PROPOSAL_NUMBER, PROPOSAL_TITLE, PROPOSAL_STATUS, DEADLINE_DATE FROM IGW_PROPOSALS_ALL WHERE ORG_ID = :p_org AND PROPOSAL_STATUS = 'SUBMITTED' ORDER BY DEADLINE_DATE;
• Joining IGW_PROP_PERSONS on PROPOSAL_ID to list principal investigators, and IGW_BUDGETS on PROPOSAL_ID to reconcile TOTAL_AMOUNT against budget lines.
• Aggregating funded totals by SUBMITTING_ORGANIZATION_ID for institutional dashboards.
• Tracking deadline compliance via DEADLINE_DATE versus SPONSOR_ACTION_DATE.

Related Objects

The table is a hub referenced by numerous satellites, all joined on PROPOSAL_ID:

• IGW_BUDGETS — proposal budget lines (IGW_BUDGETS.PROPOSAL_ID).
• IGW_PROP_PERSONS — investigators and personnel roles.
• IGW_PROP_NARRATIVES and IGW_PROP_ABSTRACTS — narrative and abstract attachments.
• IGW_PROP_LOCATIONS and IGW_PROP_PROGRAM_ADDRESSES — performance and program addresses.
• IGW_PROP_APPROVAL_RUNS — routing/approval history.
• IGW_PROP_CHECKLIST and IGW_PROP_QUESTIONS — compliance and intake data.
• IGW_AWARDS — downstream award conversion (IGW_AWARDS.PROPOSAL_ID).
• HR_ALL_ORGANIZATION_UNITS — parent reference for both organization foreign keys.