Search Results fa_citi_stat_code

Overview

The IGF_SL_PE_CITI_MAP_V view is a reporting and integration object in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, owned by the APPS schema under the IGF (Financial Aid) product family. It is classified as a VALID view and is described in ETRM as the "View for the OSS and FA data mapping for Citizenship Code." Its primary function is to reconcile the citizenship status codes used by the Oracle Student System (OSS) side of the application with the corresponding citizenship status codes defined for Financial Aid processing.

Because citizenship status governs eligibility rules for Title IV aid, residency determinations, and student categorization, a single authoritative mapping between the two code sets is essential. This view presents that mapping in a denormalized, human-readable form by joining the mapping table to the appropriate lookup definitions and calendar instances. Reporting tools, extracts, and integration interfaces that must translate between OSS citizenship codes and FA citizenship codes rely on this view rather than querying the underlying mapping table directly, ensuring that code descriptions and effective calendar context are available alongside the raw codes.

Underlying Base Objects

The view is defined over four base objects joined in a single SELECT statement:

• IGF_SL_PE_CITI_MAP (aliased CITIMAP) — the core mapping table that stores the relationship between the OSS citizenship status code and the FA citizenship status code, along with calendar type and sequence, audit columns, and row identifier.
• IGS_LOOKUPS_VIEW (aliased IGS) — the OSS lookup view, filtered to LOOKUP_TYPE = 'PE_CITI_STATUS', providing the description (MEANING) for the OSS citizenship code.
• IGF_LOOKUPS_VIEW (aliased IGF) — the Financial Aid lookup view, filtered to LOOKUP_TYPE = 'IGF_SL_PE_CITI_STATUS', providing the description for the FA citizenship code.
• IGS_CA_INST (aliased CA) — the OSS calendar instance table, joined on calendar type and sequence number to supply the instance start date, end date, and alternate code.

No additional base objects are documented for this view in the ETRM metadata. The joins are keyed on lookup codes and calendar identifiers, not on numeric surrogate keys, which is characteristic of code-mapping views in the IGF schema.

Key Columns

• CI_CAL_TYPE / CI_SEQUENCE_NUMBER — identify the calendar type and sequence number for which the mapping is effective, providing the temporal context of the mapping.
• CI_START_DT / CI_END_DT — the start and end dates of the referenced calendar instance.
• CI_ALTERNATE_CODE — the alternate code of the calendar instance, useful for display and reporting.
• PE_CITI_STAT_CODE — the OSS (PeopleSoft-derived) citizenship status code.
• PE_CITI_STAT_DESC — the descriptive meaning of the OSS citizenship code, derived from IGS_LOOKUPS_VIEW.
• FA_CITI_STAT_CODE — the Financial Aid citizenship status code, referenced in the search term fa_citi_stat_code and central to this mapping.
• FA_CITI_STAT_DESC — the descriptive meaning of the FA citizenship code, derived from IGF_LOOKUPS_VIEW.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns inherited from the mapping table.
• ROW_ID — the ROWID of the underlying mapping table row, exposed for uniqueness and debugging.

Common Use Cases and Queries

Typical uses include validation reports confirming that every OSS citizenship code has a corresponding FA code, data conversion or interface extracts that must emit FA citizenship codes, and diagnostic queries when a student's citizenship appears inconsistently between the two subsytems.

A representative query resolving the FA code for a given OSS code is:

• SELECT pe_citi_stat_code, pe_citi_stat_desc, fa_citi_stat_code, fa_citi_stat_desc FROM igf_sl_pe_citi_map_v WHERE pe_citi_stat_code = :oss_code;

To identify mappings active for a specific calendar instance:

• SELECT ci_cal_type, ci_sequence_number, ci_start_dt, fa_citi_stat_code, fa_citi_stat_desc FROM igf_sl_pe_citi_map_v WHERE ci_cal_type = :cal_type ORDER BY ci_sequence_number;

To detect unmapped OSS citizenship codes, a lookup against IGS_LOOKUPS_VIEW (LOOKUP_TYPE = 'PE_CITI_STATUS') may be compared against the view's PE_CITI_STAT_CODE values. Because the view already carries both descriptions and calendar dates, it is well suited to feed OBIEE, BI Publisher, and custom concurrent program extracts without further joins.