Results for “award_yr_end_effective_date”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IGSBV_STUDENT_SPNSR_RELATIONS is a read-only view owned by the APPS schema within the Oracle E-Business Suite Student System product (IGS). It consolidates sponsor-to-student relationship data, drawing together information about sponsors, students, fund awards, and the calendar instances associated with both the load period and the award year. The view presents a denormalized, reporting-friendly projection over six underlying base tables, allowing institutions to query sponsor-funded student awards without manually joining the constituent ETRM (Enterprise Territory and Resource Management / Student System) tables.

Because the view is defined WITH READ ONLY, it is intended strictly for reporting, extract, and integration purposes rather than transactional maintenance. It is commonly consumed in Oracle Discoverer workbooks, Oracle Reports, BI Publisher data models, and custom SQL extracts that report on third-party sponsorship of student fees and charges.

Underlying Base Objects

The view is defined over six base objects, all in the _ALL (multi-org/partitioned) form, joined on their primary or foreign key relationships:

• IGF_SP_STDNT_REL_ALL SP — the driving table holding sponsor student relation details (sponsor, person, fund, base, load calendar).
• HZ_PARTIES PA — joined on SP.PERSON_ID = PA.PARTY_ID to retrieve the student's party number and name.
• IGS_CA_INST_ALL CI1 — joined on load calendar type and sequence number to supply load calendar start/end dates and descriptions.
• IGF_AP_FA_BASE_REC_ALL AP — joined on SP.BASE_ID = AP.BASE_ID, linking the relationship to its award base record and its associated calendar instance.
• IGS_CA_INST_ALL CI2 — joined on AP.CI_CAL_TYPE and AP.CI_SEQUENCE_NUMBER to supply the award year calendar instance details.
• IGF_AW_FUND_MAST_ALL AW — joined on SP.FUND_ID = AW.FUND_ID to provide the fund code and description.

The joins resolve both the load calendar (CI1) and award-year calendar (CI2) instances, which is why the view exposes two distinct sets of effective date columns.

Key Columns

Columns fall into several logical groups. Sponsor-relationship measures include TOTAL_SPONSOR_AMOUNT, MINIMUM_CREDIT_POINTS, and MINIMUM_ATTENDANCE_TYPE. Identifying attributes include PERSON_NUMBER, PERSON_NAME, SPONSOR_STUDENT_IDENTIFIER, FUND_IDENTIFIER, BASE_IDENTIFIER, and PERSON_IDENTIFIER.

Calendar-related columns are split by period. The load calendar group comprises LOAD_CALENDAR_TYPE, LOAD_SEQUENCE_NUMBER, LOAD_CAL_START_EFFECTIVE_DATE, LOAD_CAL_END_EFFECTIVE_DATE, LOAD_CALENDAR_TYPE_DESCRIPTION, and LOAD_CALENDAR_ALTERNATE_CODE. The award year group comprises AWARD_YEAR_CALENDAR_TYPE, AWARD_YR_CAL_SEQUENCE_NUMBER, AWARD_YR_START_EFFECTIVE_DATE, AWARD_YR_END_EFFECTIVE_DATE, AWARD_YR_CALENDAR_DESCRIPTION, and AWARD_YR_CAL_ALTERNATE_CODE. The AWARD_YR_END_EFFECTIVE_DATE column — the term the user searched for — is supplied by the award-year instance of IGS_CA_INST_ALL (CI2.END_DT) and marks the closing date of the award year calendar, commonly used to determine award expiry or eligibility cutoff.

Audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) are also exposed for tracking and data lineage.

Common Use Cases and Queries

Typical uses include reporting active sponsorships by award year, reconciling sponsor amounts against student accounts, and extracting records for interfacing with finance. A representative query filtering by award year end date:

• SELECT person_number, person_name, fund_code, total_sponsor_amount, award_yr_start_effective_date, award_yr_end_effective_date FROM apps.igsbv_student_spnsr_relations WHERE award_yr_end_effective_date >= SYSDATE;
• Extracting all sponsorships for a given student: SELECT * FROM apps.igsbv_student_spnsr_relations WHERE person_id = :p_person_id;
• Producing award-year summaries: SELECT award_year_calendar_type, fund_code, COUNT(*), SUM(total_sponsor_amount) FROM apps.igsbv_student_spnsr_relations GROUP BY award_year_calendar_type, fund_code;

Because the view is read-only and joins six _ALL tables, queries should be tuned with appropriate predicates on the calendar and fund identifiers, and access should be granted through the APPS schema or a reporting responsibility.