Search Results citzn_status_code

Overview

IGF_GR_REP_STDNT_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, classified under the IGF (Financial Aid) product family. Its documented purpose is to report the Student tag within the outbound XML message generated for Pell Grant processing. In the context of EBS 12.1.1 and 12.2.2, the view serves as the extraction layer that shapes student-level demographic, identification, and permanent address data into the flat structure expected by the Pell outbound file layout.

The view is reported as VALID, indicating that its underlying dependencies resolve correctly and it is available for querying by concurrent programs or packaged procedures responsible for generating the XML payload. Because it is a reporting view rather than a table, it does not store data; all values are derived at runtime from its base object.

Underlying Base Objects

The ETRM metadata documents no referenced base objects explicitly, but the view text reveals the single source object: IGF_GR_COD_DTLS. The view is defined as a SELECT with a GROUP BY clause over that source, projecting and trimming character columns and converting date values to the format 'YYYY-MM-DD'. The GROUP BY is applied across the full projected column list, which effectively de-duplicates rows produced by a join or union within IGF_GR_COD_DTLS. No joins, outer joins, or sub-queries appear in the documented view text, so the entire reporting shape is inherited from IGF_GR_COD_DTLS.

Key Columns

The columns expose student identifiers, demographic attributes, and permanent address details. The column most directly associated with the user's search term — S_SSN — carries the student's Social Security Number, alongside S_CHG_SSN, which represents a changed or corrected SSN value used in Pell reporting scenarios where the identifier has been updated.

• DOCUMENT_ID_TXT — trimmed document identifier for the reporting record.
• REP_ENTITY_ID_TXT / ATD_ENTITY_ID_TXT — trimmed entity identifiers associated with the reporting and attendance entities.
• BASE_ID — internal base identifier linking back to the originating detail record.
• S_SSN — student Social Security Number, the primary identifier for Pell reporting.
• S_CHG_SSN — corrected or changed SSN value.
• S_DATE_OF_BIRTH / S_CHG_DATE_OF_BIRTH — date of birth and corrected date of birth, both formatted as 'YYYY-MM-DD'.
• S_LAST_NAME, FIRST_NAME, MIDDLE_NAME, S_CHG_LAST_NAME — name components, including the changed last name.
• DRIVER_LIC_STATE, DRIVER_LIC_NUMBER — driver's licence state code and number.
• CITZN_STATUS_CODE — citizenship status code.
• NOTE_MESSAGE — free-text note associated with the student record.
• PERMT_ADDR_* — permanent address components: foreign flag, type code, lines 1–3, city, state code, postal code, county, and country.
• PHONE_NUMBER_1 — contact telephone number.
• EMAIL_ADDRESS — student email address.

Common Use Cases and Queries

The principal use case is populating the Student tag in the outbound Pell XML. A developer verifying SSN values for a specific document can query the view directly:

SELECT document_id_txt, s_ssn, s_chg_ssn, s_last_name, first_name
FROM   apps.igf_gr_rep_stdnt_v
WHERE  document_id_txt = :p_document_id;

A second scenario validates that changed identifiers are correctly carried through, which matters when a student's SSN or date of birth has been corrected after the original submission:

SELECT base_id, s_ssn, s_chg_ssn,
       s_date_of_birth, s_chg_date_of_birth
FROM   apps.igf_gr_rep_stdnt_v
WHERE  s_ssn IS NOT NULL;

A third scenario reconciles permanent address data prior to XML generation, ensuring country, state, and postal values are populated:

SELECT document_id_txt, permt_addr_line_1, permt_addr_city,
       permt_addr_state_code, permt_addr_post_code, permt_addr_country
FROM   apps.igf_gr_rep_stdnt_v
WHERE  permt_addr_foreign_flag = 'Y';

Because the view applies no filtering predicate, all restrictions must be supplied by the calling program or the ad-hoc query. When troubleshooting discrepancies between the source detail record and the outbound file, querying IGF_GR_COD_DTLS and IGF_GR_REP_STDNT_V side by side isolates whether the difference originates in the stored data or in the view's trimming and date-formatting logic.