Results for “igffv_dl_pnote_status_history”

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

Overview

The IGFFV_DL_PNOTE_STATUS_HISTORY view is a full (non-incremental) reporting view owned by the APPS schema in Oracle E-Business Suite, delivered under the IGF — Financial Aid product family. It exposes the entity that holds Direct Loan Promissory Note activities, presenting a denormalized, read-only projection of promissory note status change history joined across loan, award, fund, calendar, and party data. Its principal role is to support inquiry, reporting, and integration consumers that need a single flattened row per promissory note status change event without negotiating the many-to-one relationships among the underlying slotted base tables.

The view is defined WITH READ ONLY, meaning it is intended strictly for query access. No DML is permitted against it. The view is documented as VALID in the ETRM metadata for 12.1.1 and 12.2.2.

Underlying Base Objects

The view is defined over six base tables using ANSI-style joins expressed in the classic comma-join form:

• IGF_SL_PNOTE_STAT_H (HIST) — the promissory note status history table; the driving entity supplying status and status-date.
• IGF_SL_LOR_ALL (LOR) — the loan record (loan of record) table, joined on LOAN_ID; supplies the person identifier.
• IGF_SL_LOANS_ALL (LOANS) — the loan table, joined on LOAN_ID; supplies the loan number and loan period.
• IGF_AW_AWARD_ALL (AWARDS) — the award table, joined on AWARD_ID.
• IGF_AW_FUND_MAST_ALL (FMAST) — the fund master, joined on FUND_ID; supplies fund code and description and the calendar keys.
• IGS_CA_INST_ALL (AWDY) — the calendar instance, joined on CI_CAL_TYPE/CI_SEQUENCE_NUMBER; supplies the award year alternate code.
• HZ_PARTIES (PER) — the Trading Community Architecture party table, joined outer (PER.PARTY_ID (+)) on the person identifier, supplying person number and name components.

The join chain therefore flows: status history → loan of record → loan → award → fund master → calendar instance, with an outer join to party for person demographics.

Key Columns

• "_LA:PROM_NOTE_STATUS" — flexfield-mapped promissory note status, resolved via the IGF_SL_PNOTE_STATUS lookup type and displayed using its MEANING.
• PROM_NOTE_STATUS_DATE — date the status change was recorded.
• PERSON_NUMBER, PERSON_FIRST_NAME, PERSON_LAST_NAME, PERSON_MIDDLE_NAME — student/borrower identity from HZ_PARTIES.
• FUND_CODE, FUND_DESCRIPTION — the financial aid fund associated with the loan.
• AWARD_YEAR — calendar instance alternate code identifying the award year.
• LOAN_NUMBER, LOAN_PERIOD_BEGIN_DATE, LOAN_PERIOD_END_DATE — loan identification and period.
• "_LA:LOAN_RECORD_STATUS" — loan status value (lookup type IGF_SL_LOAN_STATUS).
• LOAN_ID, PERSON_ID, STATUS_CHANGE_HISTORY_ID — primary keys for joins back to base entities.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — standard WHO audit columns.

Common Use Cases and Queries

Typical usages include auditing promissory note lifecycle events, reconciling loan status transitions for reporting, and feeding downstream extracts. A representative query returns the note status history for a borrower:

SELECT prom_note_status_date, loan_number, fund_code, award_year
FROM apps.igffv_dl_pnote_status_history
WHERE person_number = :p_person_number
ORDER BY prom_note_status_date DESC;

A status-transition count by fund and award year:

SELECT fund_code, award_year, COUNT(*) note_events
FROM apps.igffv_dl_pnote_status_history
GROUP BY fund_code, award_year;

Because the view is read-only and joins TCA and calendar tables, large extracts should be filtered by award year or fund to avoid full scans across the calendar instance and party tables.