Results for “or_inst_stat_type”

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

Overview

IGS_OR_INST_STATS_V is a reporting and integration view owned by the APPS schema in Oracle E-Business Suite, part of the Student Systems / Oracle Recruiting and Admissions (OR) product family. It presents institutional statistics records held against a party, enriching each row with the decoded lookup meaning for the statistic type. The view is a denormalized read layer over the transactional table IGS_OR_INST_STATS, joined to the shared lookup table IGS_LOOKUP_VALUES to translate the stored STAT_TYPE_CD code into a human-readable description. Because it exposes the decoded STAT_TYPE_DESC alongside the raw code, the view is well suited to concurrent program output, Oracle Reports, OAF/ADF-based inquiry pages, and outbound interfaces where downstream consumers should not have to resolve lookup codes themselves.

The name surfaced in the user's search, or_inst_stat_type, is not a column of this view; it is the lookup type value used in the join predicate (STYPE.LOOKUP_TYPE = 'OR_INST_STAT_TYPE'). This is the standard Oracle lookup mechanism, and it is the reason the view can return a MEANING without the calling code knowing the lookup type.

Underlying Base Objects

The ETRM metadata documents no referenced base objects, but the view text itself defines exactly two sources:

• IGS_OR_INST_STATS IS1 — the driving table holding the institutional statistics rows keyed by INST_STAT_ID and PARTY_ID.
• IGS_LOOKUP_VALUES STYPE — the lookup values table, restricted to LOOKUP_TYPE = 'OR_INST_STAT_TYPE'.

The join is an equality between IS1.STAT_TYPE_CD and STYPE.LOOKUP_CODE. Because the predicate on LOOKUP_TYPE is applied in the WHERE clause rather than as an outer join, rows whose statistic type code has no active lookup entry are excluded from the result set. In practice this makes the view dependent on the lookup being seeded and enabled in the target instance, which is a common cause of "missing rows" when comparing the view against its base table. The view also projects IS1.ROWID, which permits updatable-view behavior in Oracle Forms and OAF where the framework needs the row identifier to perform DML against IGS_OR_INST_STATS.

Key Columns

• ROWID — the row identifier of the underlying IGS_OR_INST_STATS row; used by tools for update/delete targeting.
• INST_STAT_ID — primary key of the institutional statistics record; the primary join key for detail queries.
• PARTY_ID — the party (person or organization) to whom the statistic applies. This is the standard TCA/HZ party identifier and is the column most often used in filters and joins to person or organization views.
• STATISTICS_TYPE — the raw statistic type code, aliased from STAT_TYPE_CD.
• STAT_TYPE_DESC — the decoded lookup MEANING for the statistic type, resolved from IGS_LOOKUP_VALUES.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN — standard WHO columns carried through for audit and delta-extraction purposes.

Common Use Cases and Queries

Typical uses include reporting the statistics held for a person or organization, validating that a party carries a required statistic type, and feeding downstream systems with a friendly description rather than a code. A representative query lists all statistics for a given party, ordered by type:

SELECT inst_stat_id, party_id, statistics_type, stat_type_desc, last_update_date
FROM apps.igs_or_inst_stats_v
WHERE party_id = :p_party_id
ORDER BY stat_type_desc;

A second pattern aggregates counts by statistic type for a population, useful for institutional dashboards:

SELECT statistics_type, stat_type_desc, COUNT(*) stat_count
FROM apps.igs_or_inst_stats_v
GROUP BY statistics_type, stat_type_desc
ORDER BY stat_type_desc;

For integration or incremental extraction, the WHO columns support a delta pull: SELECT * FROM apps.igs_or_inst_stats_v WHERE last_update_date >= :p_since. When a row is expected but not returned, query IGS_OR_INST_STATS directly and check that the corresponding STAT_TYPE_CD exists in IGS_LOOKUP_VALUES with LOOKUP_TYPE = 'OR_INST_STAT_TYPE' and an enabled flag, since the inner join on the lookup table is the most frequent reason for excluded rows.