Search Results sys_orig_ssn

Overview

IGF_GR_RFMS is a view in the Oracle E-Business Suite Financial Aid (IGF) module, a product line that has since been designated obsolete. The view represents a row-level, originator-facing snapshot of federal student aid origination data — most notably the Free Application for Federal Student Aid (FAFSA) record and the associated origination record used for the Federal Pell Grant program. In EBS 12.1.1 and 12.2.2, the IGF schema is present but largely dormant; the underlying tables typically exist as empty stubs, and the view is provided for backward compatibility with historical implementations that interfaced with the U.S. Department of Education's origination and disbursement systems.

Functionally, IGF_GR_RFMS exposes a wide, denormalized projection of a single origination record, combining financial eligibility data (EFC, Pell amount), academic enrollment context, disbursement scheduling fields, student identity fields, and Department of Education response codes. Because the view performs a multi-organization security filter using USERENV('CLIENT_INFO'), it behaves as an operational, org-scoped reporting surface rather than a simple passthrough.

Underlying Base Objects

The view is defined as a single-table selection over IGF_GR_RFMS_ALL. The documented ETRM metadata lists "Referenced base objects: none documented," which is consistent with the view referencing a single table through the _ALL multi-org convention rather than a join. All columns are projected with the RFMS. prefix directly from IGF_GR_RFMS_ALL; no derived or computed columns are introduced, and no joins to lookup or reference tables are performed.

The absence of documented base objects in the ETRM 12.2.2 record also indicates the view is marked "not implemented in this database" — meaning that in a stock installation, IGF_GR_RFMS_ALL may not be created, and the view is retained only for object-history and upgrade consistency. Its structure is therefore best treated as a legacy interface definition.

Key Columns

• ORIGINATION_ID — Primary identifier linking the record to the origination transaction submitted to the Department of Education.
• ORIG_ACTION_CODE — The action code on the origination record (the column referenced in the user's search). This distinguishes origination events such as initial origination, corrections, or subsequent updates in the federal Pell origination cycle.
• ORIG_STATUS_DT — Effective date of the origination status or action.
• EFC / SECONDARY_EFC — Expected Family Contribution values used to determine aid eligibility.
• PELL_AMOUNT / PENDING_AMOUNT — Awarded and pending Pell disbursement amounts.
• SYS_ORIG_SSN / CURRENT_SSN / FIRST_NAME / MIDDLE_NAME / LAST_NAME / BIRTH_DT — Student identity fields as captured at origination.
• ENROLLMENT_STATUS / ENROLLMENT_DT / ACADEMIC_CALENDAR — Enrollment and academic period context.
• CI_CAL_TYPE / CI_SEQUENCE_NUMBER — Institution calendar and sequence identifying the award year or payment period.
• ORG_ID — Operating unit, used in the view's WHERE clause for multi-org security.
• FULL_RESP_CODE / ORIG_REJECT_CODE — Federal response and rejection codes returned on the origination.

Common Use Cases and Queries

The primary reporting use case is reconciliation of Pell origination records by action code — the specific search that surfaces this view. Analysts filter on ORIG_ACTION_CODE to isolate correction or update transactions, and join the result to disbursement tables to validate amounts.

Example: list originations by action code for the current operating unit.

SELECT ORIGINATION_ID,
       ORIG_ACTION_CODE,
       ORIG_STATUS_DT,
       EFC,
       PELL_AMOUNT,
       ENROLLMENT_STATUS,
       FULL_RESP_CODE
  FROM IGF_GR_RFMS
 WHERE ORIG_ACTION_CODE = :p_action_code
 ORDER BY ORIG_STATUS_DT DESC;

Example: retrieve unmatched or rejected originations for follow-up.

SELECT ORIGINATION_ID, CURRENT_SSN, LAST_NAME, ORIG_REJECT_CODE, NOTE_MESSAGE
  FROM IGF_GR_RFMS
 WHERE ORIG_REJECT_CODE IS NOT NULL
   AND LEGACY_RECORD_FLAG = 'N';

Because the view enforces ORG_ID security through USERENV('CLIENT_INFO'), queries should be executed within a session in which the client info is correctly initialized for the target operating unit; otherwise the filter resolves to -99 and returns no rows.