Search Results igf_ap_css_interface_pk

Overview

IGF_AP_CSS_INTERFACE_ALL is a Financial Aid (IGF) interface table that serves as the initial staging area for CSS Profile records within Oracle E-Business Suite 12.1.1 and 12.2.2. The CSS Profile (College Scholarship Service Profile) is a financial aid application form administered by the College Board and used by many institutions to determine eligibility for institutional, non-federal student aid. Records received from the CSS Profile electronic file are first loaded into IGF_AP_CSS_INTERFACE_ALL, where they reside until downstream processing, validation, and needs-analysis evaluation convert them into the institutional financial aid system.

The table is owned by the IGF schema and is documented as VALID in the ETRM repository. It is characterized by an unusually wide structure of 716 columns, reflecting the extensive set of data elements captured on the CSS Profile form — demographics, income, assets, household composition, and needs-analysis intermediate results. From a heuristic Data Vault modeling perspective (mined from the foreign-key structure), the table is classified as standalone, meaning it does not appear to function purely as a hub, link, or satellite within a normalized Data Vault design but rather as a self-contained staging/interface structure. It should be treated as a transient landing table rather than a system of record.

Because the table is an interface object, records are typically loaded by a concurrent program or an inbound interface process and then consumed by needs-analysis (INAS/FMAR) and matching logic. After successful processing, records are generally purged or archived according to institutional retention policies.

Key Information Stored

The primary key is CSS_ID, enforced by the constraint IGF_AP_CSS_INTERFACE_PK. A unique index, IGF_AP_CSS_INTERFACE_U1, also exists on CSS_ID, making it the business-key candidate for uniquely identifying each CSS record. The most significant columns include:

• CSS_ID — Surrogate primary key uniquely identifying each CSS Profile record.
• ORG_ID — Operating unit / organization identifier, enabling multi-org partitioning of inbound records.
• RECORD_STATUS — Processing state of the interface row (for example, pending, processed, or errored).
• COLLEGE_CODE and ACADEMIC_YEAR — Identify the receiving institution and the aid year the profile applies to.
• CSS_ID_NUMBER — The College Board-assigned identifier for the student's CSS Profile submission.
• STU_RECORD_TYPE and APPLICATION_TYPE — Classify the record type (student versus parent segment) and application category.
• LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, DATE_OF_BIRTH, SOCIAL_SECURITY_NUMBER — Core student identity data used for matching to the person record.
• MATCH_CODE — Matching key used to associate the interface record with an existing person or applicant in the IGF person tables.
• FINANCIAL_AID_STATUS and YEAR_IN_COLLEGE — Aid-status and enrollment attributes influencing need calculation.
• HOUSEHOLD_SIZE and NUMBER_IN_COLLEGE — Household composition variables central to needs analysis.
• ADJUSTED_GROSS_INC, US_TAX_PAID, ITEMIZED_DEDUCTIONS — Income and tax figures feeding the FAFSA/CSS need formula.
• ASSET_* columns such as CASH_SAV_CHECK, INVEST_VALUE, HOME_VALUE, and BUS_FARM_VALUE — Asset and debt values used in the contribution calculation.
• FM_S_TOTAL_CONTRIBUTION, FM_P_TOTAL_CONTRIBUTION, and the IM_* family of columns — Intermediate and final needs-analysis results (institutional methodology) computed for student and parent segments.
• FNAR_MESSAGE_* columns — System-generated messages and rejection codes from the needs-analysis process.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY — Standard audit columns.

Common Use Cases and Queries

The most frequent operational task is identifying unprocessed or errored interface records prior to running the load/calculation program. A typical query filters on RECORD_STATUS and ORG_ID:

• SELECT css_id, college_code, academic_year, css_id_number, last_name, first_name, record_status FROM igf.igf_ap_css_interface_all WHERE record_status = 'NEW' AND org_id = :p_org_id ORDER BY creation_date;

Monitoring throughput and error volume for a given aid cycle is another common reporting use case, aggregating by academic year and status:

• SELECT academic_year, record_status, COUNT(*) FROM igf.igf_ap_css_interface_all WHERE org_id = :p_org_id GROUP BY academic_year, record_status;

Reconciliation queries join the interface table to the person-matching and setup tables to confirm that every loaded profile has been matched and consumed. Data-quality checks typically inspect required columns such as LAST_NAME, SOCIAL_SECURITY_NUMBER, and ACADEMIC_YEAR for nulls. Because the table carries 716 columns, reporting queries should always project an explicit column list rather than using SELECT *, both for performance and to avoid exposing unused fields.

Related Objects

The following objects reference or depend on IGF_AP_CSS_INTERFACE_ALL through documented relationships:

• IGF_AP_PERSON_MATCH_ALL — References this table via CSS_ID, linking each CSS interface record to a matched person.
• IGF_AP_FA_SETUP_ALL — References this table via CSS_ID, associating aid setup configuration with the CSS record.
• IGF_AP_CSS_INTERFACE_PK — The primary-key constraint enforcing uniqueness on CSS_ID.
• IGF_AP_CSS_INTERFACE_U1 — The unique index on CSS_ID, serving as the business-key candidate.

Downstream processing is typically driven by concurrent programs and the needs-analysis engine that read rows from this table, compute institutional methodology results into the IM_* and FM_* columns, and then transfer finalized data into the institutional aid tables. Because the table is an interface object, no other core application tables depend on it as a system of record; its lifecycle is bounded by the inbound financial aid load cycle.