Results for “igs_fi_p_sa_notes_u2”

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

Overview

IGS.IGS_FI_P_SA_NOTES is a transactional table in the Oracle E-Business Suite 12.1.1 and 12.2.2 IGS (Financial Aid / Student Systems) schema that stores textual notes associated with a person party and, historically, a subaccount context. Each row captures an effective-dated note reference, allowing the institution to track narrative information about a party over time while preserving version history through the effective date. The table resides in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10, which is characteristic of volatile transactional data subject to frequent inserts and updates.

From a heuristic Data Vault modeling perspective, this object most closely resembles a link table that connects a party (hub) to a note reference and a subaccount, with the effective date and Who columns lending it satellite-like descriptive attributes. Depending on modeling granularity, the combination of PARTY_ID, EFFECTIVE_DATE, and REFERENCE_NUMBER can be treated as a business key unique to each note occurrence.

Key Information Stored

The table contains ten documented columns, of which the most significant are described below.

• PARTY_SA_NOTES_ID (NUMBER 15) — Sequence-generated surrogate primary key (IGS_FI_P_SA_NOTES_PK) and the target of the user's search term. It uniquely identifies each note record.
• PARTY_ID (NUMBER 15) — Foreign key to HZ_PARTIES, identifying the person for whom the note is entered. This is the business anchor of the record.
• EFFECTIVE_DATE (DATE) — The date from which the note is considered valid; combined with PARTY_ID and REFERENCE_NUMBER it forms the second unique business key (IGS_FI_P_SA_NOTES_U2), enforcing one note reference per party per effective date.
• REFERENCE_NUMBER (NUMBER) — A foreign key to IGS_GE_NOTE; the actual note text is derived from this reference rather than stored inline, keeping the row compact.
• SUBACCOUNT_ID (NUMBER 15) — Documented as OBSOLETE, retained for backward compatibility; historically linked the note to a subaccount via IGS_FI_SUBACCTS_ALL.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard Who audit columns required in every Oracle EBS transactional table for change tracking and concurrency control.

Two unique indexes (IGS_FI_P_SA_NOTES_U1 on PARTY_SA_NOTES_ID; IGS_FI_P_SA_NOTES_U2 on PARTY_ID, EFFECTIVE_DATE, REFERENCE_NUMBER) support both the primary lookup and the notes-per-party reporting pattern. No non-unique indexes are documented.

Common Use Cases and Queries

The table is typically queried when producing a chronological audit trail of notes attached to a student or constituent, or when resolving free-text commentary linked via IGS_GE_NOTE. A standard retrieval for a single party would filter on PARTY_ID and order by EFFECTIVE_DATE:

• SELECT n.PARTY_SA_NOTES_ID, n.EFFECTIVE_DATE, t.NOTE_TEXT FROM IGS.IGS_FI_P_SA_NOTES n, IGS.IGS_GE_NOTE t WHERE n.REFERENCE_NUMBER = t.REFERENCE_NUMBER AND n.PARTY_ID = :party_id ORDER BY n.EFFECTIVE_DATE DESC;
• Reporting the most recent effective note per party using a correlated subquery or analytic ROW_NUMBER() OVER (PARTITION BY PARTY_ID ORDER BY EFFECTIVE_DATE DESC) for point-in-time views.
• Referential integrity checks confirming that every REFERENCE_NUMBER resolves to a valid IGS_GE_NOTE row.
• Migration and purge scripts keyed on PARTY_SA_NOTES_ID when archiving historical notes.

Related Objects

• HZ_PARTIES — joined on PARTY_ID; the master party registry supplying person demographics.
• IGS_GE_NOTE — joined on REFERENCE_NUMBER; holds the actual note text.
• IGS_FI_SUBACCTS_ALL — joined on SUBACCOUNT_ID; the now-obsolete subaccount dimension.
• APPS.IGS_FI_P_SA_NOTES — the APPS synonym/view layer through which users query the table.

No database objects are referenced beyond the three foreign keys noted above, and the only dependent object documented is the APPS synonym.