Search Results award_change_source_code

Overview

The IGF_AW_AWARD_LEVEL_HIST table is a critical audit and history table within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the Institutional Grants and Funds (IGF) module. Its primary role is to serve as a detailed ledger, storing a complete historical record of all changes made to key attributes of a financial aid award at the award level. Whenever an award's status, monetary amounts, distribution plan, or lock flag is modified, this table captures both the old and new values. This functionality is essential for compliance, audit trails, troubleshooting, and analyzing the lifecycle of an award from initial offering through acceptance, payment, and final status.

Key Information Stored

The table's structure is designed to capture the delta of any change. Each record is tied to a specific award (AWARD_ID) and a transaction (AWARD_HIST_TRAN_ID). The AWARD_ATTRIB_CODE indicates which attribute was modified. For each modified attribute, the table stores paired OLD and NEW value columns, creating a clear before-and-after snapshot. Key data points tracked include:

• Monetary Amounts: OFFERED_AMT, ACCEPTED_AMT, and PAID_AMT.
• Status and Flags: AWARD_STATUS_CODE and LOCK_AWARD_FLAG.
• Distribution Plan: The ADPLANS_ID columns, which are foreign keys to the award distribution plan table. The user's search for "new_adplans_id" directly relates to tracking changes in how an award is scheduled to be disbursed.
• Change Context: AWARD_CHANGE_SOURCE_CODE and standard "Who" columns (CREATED_BY, CREATION_DATE, etc.) provide essential metadata on why and by whom the change was made.

Common Use Cases and Queries

This table is primarily used for audit reporting, data validation, and process analysis. A common scenario is tracing the history of an award's distribution plan or paid amount to resolve discrepancies. For example, to audit all changes to the distribution plan for a specific award, one might execute:

SELECT award_id, award_hist_tran_id, old_adplans_id, new_adplans_id, creation_date, created_by
FROM igf.igf_aw_award_level_hist
WHERE award_id = :p_award_id
AND award_attrib_code = 'ADPLANS_ID'
ORDER BY creation_date;

Another critical use case is generating a comprehensive audit report for a specific transaction batch or date range, joining with lookup tables to decode the AWARD_ATTRIB_CODE and AWARD_CHANGE_SOURCE_CODE for user-friendly reporting.

Related Objects

The IGF_AW_AWARD_LEVEL_HIST table is central to the IGF award processing architecture. Its primary relationships are:

• IGF_AW_AWARD: The main award table, to which the AWARD_ID foreign key points.
• IGF_AW_AWD_DISB_PLANS: The table referenced by the OLD_ADPLANS_ID and NEW_ADPLANS_ID foreign keys, defining the disbursement schedule.
• IGF_LOOKUPS_VIEW: Used to decode the meaning of the AWARD_ATTRIB_CODE and AWARD_CHANGE_SOURCE_CODE lookup values.
• IGF_AW_AWARD_T (Transaction Table): Likely related to the AWARD_HIST_TRAN_ID, which groups individual attribute changes into a single logical transaction.

The table is secured by the unique primary key index IGF_AW_AWARD_LVL_HIST_PK on (AWARD_ID, AWARD_HIST_TRAN_ID, AWARD_ATTRIB_CODE), ensuring a single historical entry per attribute change per transaction.