Search Results derived_val




Overview
Explore in AI Chat

Overview

IGS_CA_DA_INST_V is an Oracle EBS 12.1.1 / 12.2.2 view owned by the APPS schema within the IGS (Student System) product family. Its purpose is to expose resolved date information for date alias instances. A date alias instance is the physical occurrence of a named date alias within a particular calendar and calendar instance, which the Student System uses extensively for academic scheduling, term-based processing, admission cycles, and fee or enrolment cut-off logic.

The view enriches the base transactional data with two derived columns. The first, DERIVED_VAL, is computed by calling IGS_CA_GEN_002.CALS_CLC_DT_FROM_DAI, which calculates the actual date for the alias instance from its calendar context. The second, ALIAS_VAL, is computed by calling IGS_CA_GEN_001.CALP_SET_ALIAS_VALUE, which takes the absolute value and the derived date to resolve any offset constraints that apply to the instance. In this way the view shields consumers from the underlying PL/SQL functions and presents a single row per date alias instance that carries absolute, derived, and effective values side by side. Because the columns are function-derived rather than stored, the view is a convenient read-only interface for reporting and integration, but it is not suitable for bulk in-place updates.

Underlying Base Objects

The view is defined over a single documented table, IGS_CA_DA_INST, which holds the raw date alias instance records. Although the ETRM metadata for the view lists no further referenced objects, the SELECT text invokes two packaged functions that form its effective dependencies:

  • IGS_CA_DA_INST — the base table supplying DT_ALIAS, SEQUENCE_NUMBER, CAL_TYPE, CI_SEQUENCE_NUMBER, ABSOLUTE_VAL, and the standard WHO audit columns.
  • IGS_CA_GEN_002.CALS_CLC_DT_FROM_DAI — function that derives the calendar date from the date alias instance.
  • IGS_CA_GEN_001.CALP_SET_ALIAS_VALUE — function that applies offset constraints, returning an effective alias value.

Because effective dates are resolved at query time rather than stored, any change to the underlying calendar, alias, or offset configuration is reflected immediately when the view is queried.

Key Columns

  • DT_ALIAS — name of the date alias to which the instance belongs.
  • SEQUENCE_NUMBER — sequence identifying the specific instance of the alias.
  • CAL_TYPE — calendar type that scopes the alias (for example, academic or reporting calendar).
  • CI_SEQUENCE_NUMBER — calendar instance sequence number, identifying the specific period or cycle.
  • ABSOLUTE_VAL — the stored absolute value for the instance, before derivation.
  • DERIVED_VAL — the date derived by CALS_CLC_DT_FROM_DAI for the given alias, calendar type, and calendar instance.
  • ALIAS_VAL — the effective value returned after CALP_SET_ALIAS_VALUE applies offset constraints to ABSOLUTE_VAL against DERIVED_VAL.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle WHO audit columns inherited from the base table.

Common Use Cases and Queries

Typical scenarios include identifying the effective date of a term-related alias for a given calendar instance, comparing stored absolute values against derived and offset-adjusted values, and feeding date-sensitive data extracts into downstream reporting or integration.

  • Retrieve all resolved dates for a specific calendar instance.
  • Locate instances where the offset constraint changes the stored value.
  • Join to calendar or alias definition tables to enrich reporting.

Sample queries:

SELECT dt_alias, sequence_number, cal_type, ci_sequence_number, absolute_val, derived_val, alias_val FROM igs_ca_da_inst_v WHERE cal_type = :p_cal_type AND ci_sequence_number = :p_ci_sequence_number;

SELECT dt_alias, sequence_number, absolute_val, derived_val, alias_val FROM igs_ca_da_inst_v WHERE absolute_val <> alias_val;

Results should be filtered by calendar type and instance to avoid full scans, since the derived columns invoke PL/SQL functions row by row.