Search Results citzn_status_code

Overview

IGF_GR_REP_STDNT_V is a reporting view in the Oracle E-Business Suite student systems schema (APPS), associated with the Financial Aid module family (IGF prefix). It presents student demographic, identity, and permanent address information drawn from the financial aid Common Origination and Disbursement (COD) detail store. The view is designed primarily to expose data in a flat, externally consumable format suitable for reporting extracts, interfaces with external agencies, and regulatory or downstream data feeds.

The view does not expose primary keys in the conventional sense; instead it combines identifier, identity, and contact attributes. Because it is owned by APPS and reads from the IGF schema, it is typically made available to reporting users and integration routines without granting direct access to the underlying base object.

Underlying Base Objects

The view is defined over a single underlying base object, IGF_GR_COD_DTLS. The ETRM metadata documents no other referenced base objects, indicating that all projected columns originate from this one source. The view applies two notable transformations:

• TRIM functions are applied to all character-based identifier, address, phone, and email columns, removing leading and trailing whitespace that may exist in the base table.
• TO_CHAR conversions format S_DATE_OF_BIRTH and S_CHG_DATE_OF_BIRTH as YYYY-MM-DD strings rather than native date values.

The presence of a GROUP BY clause repeating every projected expression indicates that the view is effectively performing a SELECT DISTINCT operation, eliminating duplicate rows that may exist in IGF_GR_COD_DTLS. This is significant because it means the view does not return one row per underlying record; repeated demographic combinations are collapsed.

Key Columns

• S_SSN — The student's Social Security Number, the column most commonly searched by users querying this view. It is the principal identity key exposed.
• S_CHG_SSN — A changed or corrected SSN value, typically populated when a prior SSN has been superseded.
• DOCUMENT_ID_TXT, REP_ENTITY_ID_TXT, ATD_ENTITY_ID_TXT — Trimmed text identifiers for the document, reporting entity, and attended entity, linking the record to COD reporting context.
• BASE_ID — The base identifier from the source record.
• S_LAST_NAME, FIRST_NAME, MIDDLE_NAME, S_CHG_LAST_NAME — Student name attributes, including a changed last name field.
• S_DATE_OF_BIRTH, S_CHG_DATE_OF_BIRTH — Date of birth and changed date of birth, both rendered as YYYY-MM-DD strings.
• DRIVER_LIC_STATE, DRIVER_LIC_NUMBER — Driver's license identity data.
• CITZN_STATUS_CODE — Citizenship status code for the student.
• PERMT_ADDR_* fields — Permanent address components: foreign flag, type code, lines 1–3, city, state code, postal code, county, and country.
• PHONE_NUMBER_1, EMAIL_ADDRESS — Contact information, trimmed.
• NOTE_MESSAGE — Free-text note associated with the COD detail record.

Common Use Cases and Queries

The most frequent use is identity lookup by Social Security Number, particularly by financial aid staff reconciling COD records or troubleshooting student data discrepancies. A typical query:

• SELECT s_ssn, s_last_name, first_name, s_date_of_birth, citzn_status_code FROM apps.igf_gr_rep_stdnt_v WHERE s_ssn = '123456789';

Because changed values are tracked separately, reconciliation queries commonly compare current and prior identity values:

• SELECT s_ssn, s_chg_ssn, s_last_name, s_chg_last_name FROM apps.igf_gr_rep_stdnt_v WHERE s_chg_ssn IS NOT NULL;
• SELECT document_id_txt, rep_entity_id_txt, s_ssn, permt_addr_city, permt_addr_state_code FROM apps.igf_gr_rep_stdnt_v WHERE base_id = :base_id;

Address and contact extracts for COD reporting or mailing purposes also draw on this view, trimming whitespace ensures clean output for fixed-format or delimited files.