Search Results igs_ad_up_detail




Overview
Explore in AI Chat

Overview

IGS_AD_UP_DETAIL is a transactional detail table owned by the IGS schema (Student System product) in Oracle E-Business Suite 12.1.1 and 12.2.2. As the documentation states, it "holds unit placement details," meaning each row represents a single unit-level record belonging to a broader unit placement activity captured at the header level. In an academic institution implementation, unit placement typically refers to the association of students with units (subjects or modules) during a placement or enrollment process, so this table functions as the child of a parent placement header, storing the specific unit, version, and status for each placement line.

In relational terms the table is the child side of a master-detail relationship. The mined Data Vault classification is standalone, which is a heuristic modeling suggestion indicating the FK structure does not clearly support a hub, link, or satellite pattern in the strict Data Vault sense. A reasonable modeling interpretation, however, treats UP_DETAIL_ID as a hub-like surrogate for each unit placement line, or alternatively as a satellite of the header hub keyed by UP_HEADER_ID combined with the unit business key.

Key Information Stored

The documented physical schema contains ten columns, with two unique primary keys defined. The most operationally significant columns are:

  • UP_DETAIL_ID — the surrogate primary key (constraint IGS_AD_UP_DETAIL_U1). This is a system-generated identifier unique to each unit placement detail row and is the safest join key to downstream or dependent objects.
  • UP_HEADER_ID — the foreign key to IGS_AD_UP_HEADER and part of the second unique index (IGS_AD_UP_DETAIL_U2). It links the detail line back to its parent placement header, establishing the master-detail hierarchy.
  • UNIT_CD — the unit code, a business-key candidate identifying which unit (subject/module) the placement relates to. It forms the second component of the U2 unique key.
  • VERSION_NUMBER — the version component of the U2 unique key. This supports versioning of unit placement records, allowing multiple historical versions of a placement line to coexist under the same header and unit code.
  • CLOSED_IND — a status flag indicating whether the unit placement detail line has been closed or remains active.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard Oracle EBS WHO columns capturing audit and concurrency information.

The second unique key (UP_HEADER_ID, UNIT_CD, VERSION_NUMBER) is the true business key and is the natural constraint to enforce when loading or validating data. Because the table is small (ten columns) it is efficient for high-volume transactional inserts typical of batch placement or enrollment processes.

Common Use Cases and Queries

Typical use cases include reporting the units placed per student or per placement header, auditing version history of unit placements, and identifying open versus closed placement lines. A standard join pattern retrieves the detail lines for a given header:

  • SELECT d.UP_DETAIL_ID, d.UNIT_CD, d.VERSION_NUMBER, d.CLOSED_IND FROM IGS.IGS_AD_UP_DETAIL d WHERE d.UP_HEADER_ID = :header_id;
  • To enumerate active placements: WHERE d.CLOSED_IND = 'N' combined with the header join.
  • To derive the latest version per unit: group by UP_HEADER_ID, UNIT_CD and take MAX(VERSION_NUMBER).

Because the table is version-aware, reporting queries should either filter a specific VERSION_NUMBER or aggregate to the maximum version to avoid double counting. Date-bounded extracts using CREATION_DATE or LAST_UPDATE_DATE support incremental integration or reconciliation jobs.

Related Objects

The principal relationship is the foreign key from IGS_AD_UP_DETAIL.UP_HEADER_ID to IGS_AD_UP_HEADER, which is the parent/master table. Joins on UP_HEADER_ID drive virtually all meaningful reporting. The primary key constraints IGS_AD_UP_DETAIL_U1 and IGS_AD_UP_DETAIL_U2 define the uniqueness rules used by the application and by any integration interfaces. Additional related objects include the IGS_AD_UP_HEADER header table (parent), the unit definition tables referenced by UNIT_CD, and any placement or enrollment APIs and concurrent programs in the IGS module that insert or update these rows. Consumers should treat IGS_AD_UP_HEADER as the mandatory join target and use UP_DETAIL_ID as the stable surrogate key when linking to dependent tables.