Search Results igf_sl_pnote_status

Overview

The view APPS.IGF_SL_PNOTE_STAT_H_V is a reporting and inquiry construct within the Oracle E-Business Suite student loan processing module (IGF — the "Financial Aid" / Student Loan schema). Its name decomposes as "IGF Student Loan Promissory Note Status History," which accurately describes its content: a denormalized, human-readable projection of promissory note status history records joined to their parent loan. In Oracle EBS 12.1.1 and 12.2.2 the object resides in the APPS schema and is owned by that schema, exposing data through the standard multi-org and AOL security model.

The view exists primarily to support forms, OAF pages, and concurrent/BI Publisher reports that must display the lifecycle of a promissory note — the legal instrument a student signs committing to repay a loan. Rather than requiring the application tier to perform a lookup on the status code, the view resolves the coded status into its descriptive meaning at query time, which makes it directly consumable by end-user reporting layers.

Underlying Base Objects

The view is defined over two physical tables using an inner equi-join:

• IGF_SL_PNOTE_STAT_H (aliased DLPNH) — the promissory note status history table. It stores one row per status transition for each note, capturing the status code, the effective status date, standard who-columns, and the concurrent request/program audit columns.
• IGF_SL_LOANS (aliased LOAN) — the parent loan master table, joined on LOAN.LOAN_ID = DLPNH.LOAN_ID. Only LOAN_NUMBER is projected from this table.

Because the join is inner, history rows whose parent loan no longer exists (or has not yet been created) are excluded. The view therefore always reflects a resolvable loan context, which is appropriate for reporting where an orphan status row would be meaningless.

Key Columns

The projected columns fall into four groups:

• Identity — ROWID (the row identifier of the underlying history row, useful for forms-based optimistic locking), DLPNH_ID (primary key of the history record), LOAN_ID and LOAN_NUMBER (the linked loan).
• Status — PNOTE_STATUS, the stored lookup code, paired with the derived column IGF_AW_GEN.LOOKUP_DESC('IGF_SL_PNOTE_STATUS', DLPNH.PNOTE_STATUS), which returns the description from the IGF_SL_PNOTE_STATUS lookup type. PNOTE_STATUS_DATE records when the transition took effect.
• Audit / WHO — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN.
• Concurrent program audit — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE, enabling traceability back to the request that inserted or updated each row.

Note that the lookup description column is unnamed in the view text; in SQL it must be referenced by position or aliased explicitly.

Common Use Cases and Queries

Typical uses include promissory note status inquiry screens, delinquency and disbursement-eligibility reporting, and audit trails showing who changed a note's status and when.

Sample query — current status history for a loan:

• SELECT pnote_status, PNOTE_STATUS_DATE, LOAN_NUMBER FROM apps.igf_sl_pnote_stat_h_v WHERE loan_number = :p_loan_number ORDER BY pnote_status_date;
• SELECT loan_number, pnote_status, PNOTE_STATUS_DATE FROM apps.igf_sl_pnote_stat_h_v WHERE TRUNC(pnote_status_date) BETWEEN :p_from AND :p_to;
• SELECT h.loan_id, h.pnote_status, h.request_id FROM apps.igf_sl_pnote_stat_h_v h WHERE h.request_id = :p_request_id;

Because the view is a simple two-table join with no aggregation, it is safe for direct querying in BI Publisher data models and Discoverer workbooks, subject to the standard APPS schema grants.