Results for “student_inst_number”

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

Overview

The IGS_HE_EXT_RUN_INTERIM table is a transient staging object within the Oracle E-Business Suite Student System (IGS) product family. Its documented description identifies it as the HESA Extract Run Interim Table, which stores records temporarily while they are being processed by the Generate Extract Process. The Higher Education Statistics Agency (HESA) extract is a statutory reporting mechanism used by UK higher education institutions to return student, course, and unit data to the regulatory body. During that process, the Generate Extract Process writes candidate rows into this interim table, validates and transforms them, then persists the results to the final extract tables.

Because the table is populated and consumed exclusively within the extract workflow, it functions as a scratchpad rather than a system of record. Records written to IGS_HE_EXT_RUN_INTERIM may be truncated, replaced, or deleted between extract runs, and the table should never be treated as an authoritative source of student data. The primary key is IGS_HE_EXT_RUN_INTERIM_PK, defined on EXT_INTERIM_ID, and the documented schema contains fourteen columns.

Based on the foreign key relationship to HZ_PARTIES through PERSON_ID and the presence of run-scoped context columns, the heuristic Data Vault classification for this object is satellite-leaning. This is a modeling suggestion only: the table carries descriptive attributes attached to a parent key, which is characteristic of a satellite structure, though the transient nature of the data means it does not behave as a durable satellite in a Data Vault warehouse.

Key Information Stored

The documented columns support a small set of functional groupings:

• EXT_INTERIM_ID — Surrogate primary key, sourced from the IGS_HE_EXT_RUN_INTERIM_PK constraint and mirrored by the unique index IGS_HE_EXT_RUN_INTERIM_U1, which is the business-key candidate for this table.
• EXTRACT_RUN_ID — Identifier that links each interim row back to its parent extract run, allowing rows to be grouped or purged per execution.
• PERSON_ID — Foreign key to HZ_PARTIES, identifying the student or person to whom the extract row belongs.
• COURSE_CD and CRV_VERSION_NUMBER — Course code and course version, describing the programme of study.
• UNIT_CD and UV_VERSION_NUMBER — Unit (module) code and unit version, describing the individual unit of study.
• STUDENT_INST_NUMBER — Institution-assigned student identifier used by HESA returns.
• LINE_NUMBER — Ordinal position of the row within the extract, supporting deterministic ordering and sequencing of output records.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN follow Oracle EBS standard WHO column conventions and record who created and last modified each interim row.

Common Use Cases and Queries

The most frequent operational requirement is to inspect the current contents of an in-flight extract run. Because EXTRACT_RUN_ID scopes every row, a query filtering on that column returns the full working set for a single execution:

SELECT a.EXT_INTERIM_ID, a.PERSON_ID, a.COURSE_CD, a.UNIT_CD,
       a.STUDENT_INST_NUMBER, a.LINE_NUMBER
  FROM   IGS.IGS_HE_EXT_RUN_INTERIM a
 WHERE  a.EXTRACT_RUN_ID = :extract_run_id
 ORDER  BY a.LINE_NUMBER;

Diagnostic scenarios typically involve joining to HZ_PARTIES to resolve person identity when an interim row fails validation or produces an unexpected extract value. Row counts grouped by EXTRACT_RUN_ID are useful for confirming that an extract produced the expected volume before it is committed downstream, and the same query can expose partial runs that aborted mid-process. Reconciliation reporting commonly compares interim counts against the final extract output for a given run. Support and development teams also use the table to verify that COURSE_CD, CRV_VERSION_NUMBER, UNIT_CD, and UV_VERSION_NUMBER were resolved correctly before the extract was finalised.

Related Objects

The following objects are the most significant points of interaction for IGS_HE_EXT_RUN_INTERIM:

• HZ_PARTIES — Referenced through IGS_HE_EXT_RUN_INTERIM.PERSON_ID; the primary join for resolving student identity.
• IGS_HE_EXT_RUN_INTERIM_PK — Primary key constraint on EXT_INTERIM_ID.
• IGS_HE_EXT_RUN_INTERIM_U1 — Unique index on EXT_INTERIM_ID, the documented business-key candidate.
• Extract run parent table — The table supplying EXTRACT_RUN_ID, which governs the lifecycle of each interim batch.
• Final HESA extract tables — The downstream targets populated once interim rows pass processing.
• Generate Extract Process (concurrent program) — The process that inserts, reads, and clears rows in this table.

Because the object is run-scoped and transient, integration and reporting solutions should always join through EXTRACT_RUN_ID rather than assuming the table contains a single stable dataset.