Search Results old_award_status_code

Overview

The IGF.IGF_AW_AWARD_LEVEL_HIST table is an audit and history repository within the Oracle E-Business Suite (EBS) Grants Management (IGF) module. It stores all award level change details captured whenever a material attribute of an award record is modified. Each row records a before-and-after pair of values for a specific award attribute, the transaction that caused the change, and the change source. This design provides a complete, queryable audit trail of the award lifecycle across Oracle EBS 12.1.1 and 12.2.2.

Following heuristic Data Vault classification mined from the foreign key structure, this table is best modeled as a link (specifically a transactional link or change-log association). It connects award records in IGF_AW_AWARD_ALL, award distribution plans in IGF_AW_AWD_DIST_PLANS (both old and new references), and the initiating transaction. Combined with the parallel old/new attribute patterns, this makes it a change-tracking artifact rather than a pure hub or satellite.

Key Information Stored

The documented schema includes 25 columns. The surrogate primary key is defined by the unique index IGF_AW_AWARD_LVL_HIST_PK over (AWARD_ID, AWARD_HIST_TRAN_ID, AWARD_ATTRIB_CODE), which also serves as the primary business-key candidate. Key columns include:

• AWARD_ID — foreign key to IGF_AW_AWARD_ALL; the award for which the change occurred.
• AWARD_HIST_TRAN_ID — identifies the transaction causing the award update; part of the composite PK.
• AWARD_ATTRIB_CODE — lookup QuickCode identifying which award attribute was changed; part of the composite PK.
• AWARD_CHANGE_SOURCE_CODE — lookup code indicating the source of the change (e.g., user, concurrent process).
• OLD_AWARD_STATUS_CODE / NEW_AWARD_STATUS_CODE — the prior and resulting award status values, the columns most directly relevant to searches for old_award_status_code.
• OLD_LOCK_AWARD_FLAG / NEW_LOCK_AWARD_FLAG — prior and resulting award lock status.
• OLD_OFFERED_AMT / NEW_OFFERED_AMT, OLD_ACCEPTED_AMT / NEW_ACCEPTED_AMT, OLD_PAID_AMT / NEW_PAID_AMT — before-and-after monetary values for offered, accepted, and paid amounts.
• OLD_ADPLANS_ID / NEW_ADPLANS_ID — foreign keys to IGF_AW_AWD_DIST_PLANS, tracking distribution plan changes.
• Standard Who and Concurrent Who columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) — audit metadata.

Common Use Cases and Queries

Typical uses include reconstructing the status history of an award, auditing who changed monetary amounts, and reconciling distribution plan reassignments. A representative query retrieving status transitions for a given award is:

SELECT h.AWARD_ID, h.AWARD_HIST_TRAN_ID, h.AWARD_ATTRIB_CODE,
       h.OLD_AWARD_STATUS_CODE, h.NEW_AWARD_STATUS_CODE,
       h.AWARD_CHANGE_SOURCE_CODE, h.LAST_UPDATE_DATE
FROM   IGF.IGF_AW_AWARD_LEVEL_HIST h
WHERE  h.AWARD_ID = :p_award_id
AND    h.OLD_AWARD_STATUS_CODE IS NOT NULL
ORDER BY h.AWARD_HIST_TRAN_ID;

Reporting frequently joins back to IGF_AW_AWARD_ALL for the current award context, or filters by AWARD_CHANGE_SOURCE_CODE to separate user-driven from batch-driven changes. Because the change source is stored as a lookup QuickCode, reporting typically resolves it against FND_LOOKUPS or the relevant IGF lookup view.

Related Objects

The most significant related objects, derived from documented FK relationships and usage:

• IGF.IGF_AW_AWARD_ALL — parent award table; joined on AWARD_ID.
• IGF.IGF_AW_AWD_DIST_PLANS — referenced twice, via OLD_ADPLANS_ID and NEW_ADPLANS_ID.
• IGF.IGF_AW_AWARD_LEVEL_HIST indexes and PK (IGF_AW_AWARD_LVL_HIST_PK) — drive uniqueness and query performance.
• FND_LOOKUPS — resolves AWARD_ATTRIB_CODE, AWARD_CHANGE_SOURCE_CODE, and the status codes.
• Standard Who / Concurrent Who columns — integrate with FND_USER and concurrent request tracking for attribution.

The history grain (one row per attribute change per transaction) makes this table the authoritative source for award change auditing in the IGF module.