Search Results pnote_status_date




Overview
Explore in AI Chat

Overview

The IGF.IGF_SL_PNOTE_STAT_H table is a critical historical data repository within the Oracle E-Business Suite (EBS) Student Loan (SL) module, specifically for Direct Loan processing. Its primary function is to maintain a complete audit trail of all status changes for promissory notes associated with loan records. This table operates under the IGF (Institutional Grants and Funds) schema and is valid for both EBS releases 12.1.1 and 12.2.2. By capturing each status transition with a timestamp and user information, it provides an immutable history essential for compliance, troubleshooting, and reporting on the lifecycle of a student loan's promissory note, from origination through to its final status.

Key Information Stored

The table's structure is designed to capture the core status event and standard EBS audit information. The most significant columns include DLPNH_ID, which serves as the unique primary key identifier for each historical record. The LOAN_ID column links the status change to its specific loan master record. The PNOTE_STATUS and PNOTE_STATUS_DATE columns are the core data points, recording the specific status code (e.g., 'SIGNED', 'DISBURSED', 'CANCELLED') and the precise date and time that status became effective. The table is completed by a full suite of Standard Who columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and Standard Who columns for concurrent programs (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE), which document exactly which user or process initiated each status change.

Common Use Cases and Queries

This table is fundamental for generating audit reports, analyzing loan processing timelines, and reconciling loan statuses. A common operational query involves retrieving the full status history for a specific loan to understand its progression or to identify when a particular status was applied. For reporting, analysts frequently join this table to the main loan master table (e.g., IGF_SL_LOANS) to generate lists of all loans that reached a certain status within a given date range, which is crucial for disbursement or compliance reporting. The historical nature of the table also allows for point-in-time analysis to reconstruct a loan's status on any past date, supporting regulatory audits.

  • Retrieve complete status history for a loan: SELECT * FROM igf.igf_sl_pnote_stat_h WHERE loan_id = &loan_id ORDER BY pnote_status_date;
  • Find loans that entered a specific status in a period: SELECT DISTINCT loan_id FROM igf.igf_sl_pnote_stat_h WHERE pnote_status = '&status' AND TRUNC(pnote_status_date) BETWEEN :start_date AND :end_date;
  • Get the most recent status for a set of loans: SELECT loan_id, pnote_status, pnote_status_date FROM (SELECT loan_id, pnote_status, pnote_status_date, RANK() OVER (PARTITION BY loan_id ORDER BY pnote_status_date DESC) rnk FROM igf.igf_sl_pnote_stat_h) WHERE rnk = 1;

Related Objects

As per the dependency metadata, the IGF_SL_PNOTE_STAT_H table does not reference other objects but is itself referenced by the APPS synonym of the same name (IGF_SL_PNOTE_STAT_H). This synonym facilitates access for applications and reports running with the APPS schema permissions. The most significant logical relationship is with the main Direct Loan transaction table, likely named IGF_SL_DL_PNOTE or a similar variant, which holds the current, active promissory note data. The LOAN_ID column in this history table is a foreign key to the primary key of the core loan master table, such as IGF_SL_LOANS, establishing the fundamental link between a loan and its status history.