Results for “offset_date_alias_inst_seq_num”

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

Overview

IGSBV_CAL_PRD_EVNT_OFSTS is a read-only database view owned by the APPS schema in Oracle E-Business Suite. It belongs to the IGS (Student System) product family and functions as the base view for the Calendar Period Event Offset entity, which describes the relationship between two date alias instances. In institutional calendar modeling, a date alias instance represents a specific dated occurrence tied to a calendar, such as the start of a term, the census date, or a withdrawal deadline. This view exposes the offset relationships and derived calendar dates that describe how one such date alias instance is positioned relative to another, using day, week, month, and year offsets.

In Oracle EBS 12.1.1 and 12.2.2, the view serves reporting and integration purposes rather than transaction processing. Because the view materializes the offset relationship together with computed absolute calendar dates for both the primary and offsetting alias instances, it allows external systems, extracts, and concurrent programs to consume pre-resolved date relationships without re-implementing the calendar resolution logic embedded in the IGS_CA_GEN_001 and IGS_CA_GEN_002 PL/SQL packages.

Underlying Base Objects

The view text is defined over three tables, joined with the WITH READ ONLY clause:

• IGS_CA_DA_INST_OFST (aliased DA) — the primary source for the offset relationship. It supplies the date alias, calendar type, and calendar instance sequence number for both the base instance and the offset instance, together with the numeric day, week, month, and year offsets and the OFST_OVERRIDE flag.
• IGS_CA_DA_INST (aliased DA1) — the base date alias instance. It resolves the ABSOLUTE_VAL of the primary alias, which is passed to IGS_CA_GEN_002.CALS_CLC_DT_FROM_DAI to yield the resolved date (DERIVED_VALUE) and to IGS_CA_GEN_001.CALP_SET_ALIAS_VALUE to yield the ALIAS_VALUE.
• IGS_CA_DA_INST (aliased DA2) — the offset date alias instance, processed in the same manner to produce OFFSET_DERIVED_VALUE and OFFSET_ALIAS_VALUE.

The join is performed on date alias, instance sequence number, calendar type, and calendar instance sequence number for both the base and offset alias pair. The ETRM 12.2.2 metadata documents no referenced base objects, although the view text clearly identifies these three tables.

Key Columns

• DATE_ALIAS, DATE_ALIAS_INST_SEQ_NUM — identify the primary date alias instance (DT_ALIAS, DAI_SEQUENCE_NUMBER).
• CALENDAR_TYPE, CAL_INST_SEQ_NUM — the calendar category and its specific calendar instance for the primary alias.
• OFFSET_DATE_ALIAS, OFFSET_DATE_ALIAS_INST_SEQ_NUM — the second date alias instance with which the offset relationship is defined.
• OFFSET_CALENDAR_TYPE, OFFSET_CAL_INST_SEQ_NUM — calendar type and instance for the offset alias.
• NUMBER_OF_DAY_OFFSET, NUMBER_OF_WEEK_OFFSET, NUMBER_OF_MONTH_OFFSET, NUMBER_OF_YEAR_OFFSET — absolute offset quantities (DAY_OFFSET, WEEK_OFFSET, MONTH_OFFSET, YEAR_OFFSET) expressing the positional difference between the two alias instances.
• OFST_OVERRIDE — a flag indicating that the offset should be overridden rather than computed.
• ABSOLUTE_VALUE / DERIVED_VALUE / ALIAS_VALUE — the stored absolute value of the primary alias, its computed calendar date via IGS_CA_GEN_002.CALS_CLC_DT_FROM_DAI, and its formatted alias value via IGS_CA_GEN_001.CALP_SET_ALIAS_VALUE.
• OFFSET_ABSOLUTE_VALUE / OFFSET_DERIVED_VALUE / OFFSET_ALIAS_VALUE — the equivalent values for the offset alias instance.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — standard EBS audit columns inherited from the offset table.

Common Use Cases and Queries

Typical uses include validating calendar configuration, publishing date relationship data to downstream systems, and troubleshooting the IGS_CA_GEN_002 date calculation referenced in the query you searched. A representative query returning offset relationships for a calendar type is:

• SELECT DATE_ALIAS, OFFSET_DATE_ALIAS, NUMBER_OF_DAY_OFFSET, DERIVED_VALUE, OFFSET_DERIVED_VALUE FROM APPS.IGSBV_CAL_PRD_EVNT_OFSTS WHERE CALENDAR_TYPE = :p_cal_type ORDER BY DATE_ALIAS;
• SELECT DATE_ALIAS, OFFSET_DATE_ALIAS, ALIAS_VALUE, OFFSET_ALIAS_VALUE FROM APPS.IGSBV_CAL_PRD_EVNT_OFSTS WHERE OFST_OVERRIDE = 'Y';
• SELECT * FROM APPS.IGSBV_CAL_PRD_EVNT_OFSTS WHERE DATE_ALIAS = :p_alias AND CAL_INST_SEQ_NUM = :p_seq;

Because the functions invoked in the view (IGS_CA_GEN_002.CALS_CLC_DT_FROM_DAI and IGS_CA_GEN_001.CALP_SET_ALIAS_VALUE) execute per row, large-scale extracts should be filtered on calendar type and instance to control execution cost.