Search Results igs_ad_location_rel_u1

Overview

IGS.IGS_AD_LOCATION_REL is a transactional table in the Oracle E-Business Suite (EBS) 12.1.1 / 12.2.2 environment, owned by the IGS (Student Systems) schema. It is registered in FND Design Data as IGS.IGS_AD_LOCATION_REL and holds a VALID status. Functionally, the table describes the hierarchical relationship between locations, establishing geographical equivalence between a "owning" location and one or more "sub" locations. It is a core building block of the Oracle Student System's location model, used to derive default examination locations for students enrolled in on-campus units.

Physically, the table resides in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10. Its primary structure consists of 8 columns and is indexed by two indexes: the unique index IGS_AD_LOCATION_REL_U1 (on LOCATION_CD and SUB_LOCATION_CD) and the non-unique index IGS_AD_LOCATION_REL_N1 (on SUB_LOCATION_CD).

From a data-vault modeling perspective, the heuristic classification mined from the FK structure labels this object as a link. This is appropriate: the table stores the association between two location entities (owning and sub) rather than descriptive attributes of a single entity. The natural/business keys of the link are the two location codes; the only attribute carried is DFLT_IND, making this a classic relationship (link) table in a hub-and-link design.

Key Information Stored

The following columns are the most significant for understanding and querying this table:

• LOCATION_CD (VARCHAR2(10), mandatory) — Code for the owning location in the relationship. A location may have multiple owning locations. Part of the natural composite key and of the primary key IGS_AD_LOCATION_REL_PK.
• SUB_LOCATION_CD (VARCHAR2(10), mandatory) — Code for the sub location in the relationship. A location may have multiple sub locations. Part of the natural composite key and of the PK; also the leading column of the non-unique index N1.
• DFLT_IND (VARCHAR2) — Flag indicating whether the sub location is the default sub-location of the owning location. The system uses this to determine the default unit attempt examination location for students enrolled in on-campus units. Each campus may have at most one default exam location; only Campus locations may carry a default sub-location.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard "Who" audit columns maintained by EBS, providing row-level auditability (created/updated user and timestamp).

The surrogate primary key is IGS_AD_LOCATION_REL_PK (LOCATION_CD, SUB_LOCATION_CD). The unique index IGS_AD_LOCATION_REL_U1 mirrors the same two columns and is the business-key candidate, ensuring that no duplicate owning-to-sub-location pair is stored. Because the PK is composed entirely of the natural business key, no separate meaningless surrogate column exists in this table.

Common Use Cases and Queries

Typical usage centers on location hierarchy reporting, examination-location derivation, and data quality checks. Common patterns include:

• Resolving the default examination location for a campus: query rows where DFLT_IND = 'Y' to identify the single default sub-location per campus.
• Listing all sub-locations under a given owning location, joining to IGS_AD_LOCATION_ALL to fetch descriptive location details.
• Auditing missing or multiple defaults within a campus — a data-integrity check for the rule that only campus locations may have a default sub-location and each campus may hold only one.

A representative query follows the documented query text, for example:

SELECT LOCATION_CD, SUB_LOCATION_CD, DFLT_IND FROM IGS.IGS_AD_LOCATION_REL;

To fetch the default exam location per owning location, filter on DFLT_IND = 'Y'. To enumerate all relationships in the hierarchy, join LOCATION_CD back to SUB_LOCATION_CD via IGS_AD_LOCATION_ALL to provide names and addresses.

Related Objects

• IGS.IGS_AD_LOCATION_ALL — Referenced by both foreign keys: LOCATION_CD references IGS_AD_LOCATION_ALL, and SUB_LOCATION_CD references IGS_AD_LOCATION_ALL. This is the primary master table for location validation and description lookup.
• IGS.IGS_AD_LOCATION_REL (APPS synonym) — The APPS layer exposes this IGS object; the "IGS_AD_LOCATION_REL is referenced by APPS.IGS_AD_LOCATION_REL" dependency indicates the APPS synonym/table alias through which application code and reports access the table.
• IGS_AD_LOCATION_REL_U1 — The unique index enforcing the business key; critical for lookup performance and duplicate prevention.
• IGS_AD_LOCATION_REL_N1 — Non-unique index on SUB_LOCATION_CD supporting reverse lookups (finding owning locations of a given sub location).
• IGS_AD_LOCATION_REL_PK — The primary key constraint defining the row's identity.

No other database objects are documented as being referenced by this table. Its principal external dependency is the location master via its two foreign keys, making IGS_AD_LOCATION_ALL the most significant related object for joins and validation.