Results for “catalog_sequence_number”

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

Overview

IGSFV_ACADEMIC_CATALOGS is a read-only view owned by the APPS schema in the Oracle E-Business Suite Student System (IGS) product. It exposes the academic catalog and calendar attributes recorded against a person in the institution's records. The view consolidates identifying party information with effective-dated person status details, resolving four distinct calendar references held on the person status record: matriculation, initial, recent, and catalog calendars. Its primary role is to provide reporting, inquiry, and integration access to these catalog associations without requiring the consumer to join the underlying person status and calendar instance tables directly.

Because the view is defined with the WITH READ ONLY clause, it is intended exclusively for query and extract purposes. No DML operations are supported against it. The view is registered as VALID, indicating that its dependency chain is intact and that the referenced tables and columns resolve correctly in the EBS 12.1.1 and 12.2.2 environments.

Underlying Base Objects

The view is defined over four base tables using an outer-join pattern, with the person status detail record acting as the driving table:

• IGS_PE_STAT_DETAILS (aliased PSD) — the primary source of effective-dated status, tuition, and calendar reference data for the person.
• HZ_PARTIES (aliased HZP) — joined on PSD.PERSON_ID = HZP.PARTY_ID to supply the party number used as the person identifier.
• IGS_CA_INST_ALL (aliased CIA1, CIA2, CIA3, CIA4) — joined four times, each instance resolving a calendar type and sequence number pair from PSD to the calendar instance for matriculation, initial, recent, and catalog calendars respectively.

All four calendar joins are outer joins, since the corresponding calendar type and sequence number columns on the person status detail may be null. The join on the recent calendar is expressed as CIA3.CAL_TYPE(+) = PSD.RECENT_CAL_TYPE AND CIA3.SEQUENCE_NUMBER(+) = RECENT_SEQUENCE_NUMBER, matching the RECENT_SEQUENCE_NUMBER column referenced in the user's search. This pairing allows the view to return alternate codes and start and end dates for the recent calendar instance where one is recorded, while preserving the person status row if no match exists.

Key Columns

• PERSON_NUMBER — the party number of the person, sourced from HZ_PARTIES.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date range for which the person status detail record is effective.
• IN_STATE_TUITION, TUITION_START_DATE, TUITION_END_DATE — tuition residency and its validity period.
• MATRICULATION_ALTERNATE_CODE, MATRICULATION_CALENDAR_TYPE, MATRICULATION_SEQUENCE_NUMBER, MATRICULATION_START_DATE, MATRICULATION_END_DATE — the matriculation calendar reference and its resolved instance attributes.
• INITIAL_ALTERNATE_CODE, INITIAL_CALENDAR_TYPE, INITIAL_SEQUENCE_NUMBER, INITIAL_START_DATE, INITIAL_END_DATE — the initial calendar reference.
• RECENT_ALTERNATE_CODE, RECENT_SEQUENCE_NUMBER, and the corresponding calendar type and dates — the recent calendar reference, which is frequently used to identify the person's most current academic catalog association.
• CATALOG_CALENDAR_TYPE, CATALOG_SEQUENCE_NUMBER, CATALOG_START_DATE, CATALOG_END_DATE, and the catalog alternate code — the catalog calendar instance tied to the person.
• PERSON_ID and the standard audit columns CREATION_DATE, CREATED_BY, LAST_UPDATED_BY, and LAST_UPDATE_DATE.

Common Use Cases and Queries

Typical uses include identifying the catalog associated with a student for a given period, reporting tuition status, and integrating person status data into downstream extracts. A query locating the most recent catalog reference for a person can filter on the recent sequence number and alternate code:

• SELECT person_number, recent_calendar_type, recent_sequence_number, recent_alternate_code, recent_start_date, recent_end_date FROM igsfv_academic_catalogs WHERE person_number = :party_number;
• SELECT person_number, catalog_calendar_type, catalog_sequence_number, catalog_start_date, catalog_end_date FROM igsfv_academic_catalogs WHERE effective_start_date <= SYSDATE AND (effective_end_date IS NULL OR effective_end_date >= SYSDATE);

Because the calendar joins are outer joins, consumers should filter on the relevant sequence number column to exclude rows where the calendar reference is null when a specific catalog association is required.