Search Results igs_or_unit_hist

Overview

The view IGS_OR_UNIT_HIST belongs to the IGS – Student System product family within Oracle E-Business Suite. In the ETRM metadata published for release 12.1.1 and 12.2.2, this object is flagged as obsolete and the documentation explicitly records that it is "Not implemented in this database." That notation reflects the status of the view in the reference environment from which the ETRM extract was generated; it does not necessarily mean the view is absent from every historical or customized installation. Where it does exist, IGS_OR_UNIT_HIST presents the historical audit trail of academic organizational units (institutions, faculties, departments, and similar reporting structures) maintained by the Student System. Its columns capture the effective dating of each organizational unit (OU), who created the historical record, and the descriptive and status attributes that were in force during the recorded interval. The view is therefore a reporting and integration surface over the OU history table, intended to let consumers query the state of an organizational unit as of a particular date without directly manipulating the multi-org base table.

Underlying Base Objects

According to the documented view text, IGS_OR_UNIT_HIST is defined exclusively over a single base object: IGS_OR_UNIT_HIST_ALL. The view performs a select from that table, aliased as TAB, projecting the table's ROWID as ROW_ID alongside the business columns. No other base tables or objects are documented in the ETRM metadata, and the "Referenced base objects" section lists none beyond the _ALL table implied by the view definition.

The most significant structural characteristic is the multi-organization filter embedded in the WHERE clause. The predicate compares the current operating unit against the value carried in the session client information string:

• NVL(TAB.ORG_ID, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)

This is the standard Oracle multi-org ("_ALL") security construct. The view is the secured (operating-unit-scoped) layer, while IGS_OR_UNIT_HIST_ALL is the underlying table holding records for all operating units. Resolving the session value requires the application to have initialized client information, typically through FND_GLOBAL.APPS_INITIALIZE or an equivalent session context call.

Key Columns

The columns exposed by the view describe the identity, lifespan, and administrative state of each organizational unit history record:

• ROW_ID – the ROWID of the underlying IGS_OR_UNIT_HIST_ALL row, exposed as a unique row identifier.
• ORG_ID – the operating unit / organization identifier used by the multi-org security predicate.
• ORG_UNIT_CD – the code identifying the organizational unit.
• OU_START_DT and OU_END_DT – the overall start and end dates of the organizational unit's validity.
• HIST_START_DT and HIST_END_DT – the start and end dates of the specific history record interval, allowing point-in-time reconstruction of attribute values.
• HIST_WHO – the user or process responsible for the history record.
• DESCRIPTION, NAME – descriptive attributes of the unit.
• ORG_STATUS, ORG_TYPE, MEMBER_TYPE – classification and status attributes.
• INSTITUTION_CD – the institution to which the unit belongs.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN – standard Oracle Who columns recording row creation and the most recent modification.

Common Use Cases and Queries

The principal use case is point-in-time reporting on organizational unit structure: identifying which units existed, what their status or type was, and which institution they belonged to during a given interval. Historical reporting typically filters on HIST_START_DT and HIST_END_DT to retrieve the record whose interval contains the date of interest. A representative query is:

• SELECT org_unit_cd, name, org_status, org_type, hist_start_dt, hist_end_dt FROM igs_or_unit_hist WHERE org_unit_cd = :unit_code AND :as_of_date BETWEEN hist_start_dt AND NVL(hist_end_dt, :as_of_date);
• SELECT org_unit_cd, institution_cd, COUNT(*) FROM igs_or_unit_hist WHERE org_status = 'A' GROUP BY org_unit_cd, institution_cd;

Because the view is flagged as obsolete and not implemented in the documented environment, implementers should verify its presence with ALL_VIEWS and confirm the existence of IGS_OR_UNIT_HIST_ALL before relying on it. Where it does not exist, equivalent OU history data should be sourced from the corresponding _ALL table or from the successor objects designated by Oracle in later Student System releases. Direct queries against IGS_OR_UNIT_HIST should be executed within an initialized applications session so the multi-org predicate resolves correctly.