Search Results igs_ad_relations_int_pk

Overview

IGS_AD_RELATIONS_INT_ALL is an interface (staging) table in the Oracle E-Business Suite IGS — Student System product. It resides in the IGS schema and is documented as VALID in ETRM for releases 12.1.1 and 12.2.2. The table's stated purpose is to hold information about a person's relationship details, meaning records that describe how one party (for example, a student, instructor, or applicant) is related to another party — parent, guardian, spouse, emergency contact, and similar kinship or association types.

The "_INT_ALL" suffix indicates this is an open-interface table used by the Oracle Student System's Admissions/Relationships import process. External sources, legacy systems, or third-party feeds write rows into this table, and concurrent programs then validate and move them into the production relationship model. Because the object is an interface rather than a master entity, its rows are transient by design.

The supplied relationship metadata classifies this object heuristically as satellite-leaning. Under a Data Vault reading, that suggests the table behaves primarily as a descriptive satellite attached to a party key rather than as a hub or link: its dominant foreign key points to HZ_PARTIES, supplying attribute context around an existing party identity.

Key Information Stored

The interface table carries 33 documented columns. Most significant among them:

• INTERFACE_RELATIONS_ID — surrogate primary key (IGS_AD_RELATIONS_INT_PK) and the column referenced by every dependent child table in the interface family.
• REL_PERSON_ID — foreign key to HZ_PARTIES, identifying the related party.
• INTERFACE_ID and INTERFACE_RUN_ID — identify the parent interface batch and the specific concurrent run that loaded the row.
• ORG_ID — multi-org operating unit context.
• RELATIONSHIP_TYPE, RELATIONSHIP_CODE, and OTH_RELATIONSHIP_TYPE — classify the nature of the relationship, including free-text "other" values.
• SURNAME, GIVEN_NAMES, MIDDLE_NAME, TITLE, PRE_NAME_ADJUNCT, PREFERRED_GIVEN_NAME — descriptive name attributes for the related person.
• BIRTH_DT, SEX, DECEASED, DECEASED_DATE — demographic and vital-status attributes.
• RECIPROCAL_FLAG — indicates whether an inverse relationship row should be generated.
• MATCH_IND, STATUS, and ERROR_CODE — processing state, match outcome, and error diagnostics.
• PERSON_NUMBER and ACCOUNT_NUMBER — business identifiers for the person.
• Standard audit columns such as CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, PROGRAM_ID, and REQUEST_ID.

A unique index, IGS_AD_RELATIONS_INT_ALL_U1, is defined on INTERFACE_RELATIONS_ID, confirming it as the business-key candidate.

Common Use Cases and Queries

Typical usage centers on monitoring batch loads and troubleshooting rejected interface rows. A common query inspects unresolved rows by status and error code:

SELECT interface_relations_id, rel_person_id, relationship_code, status, error_code
FROM   igs.igs_ad_relations_int_all
WHERE  interface_run_id = :run_id
AND    status NOT IN ('PROCESSED','SUCCESS');

Reconciliation reporting joins the interface row to HZ_PARTIES to confirm the target party resolved correctly:

SELECT i.interface_relations_id, p.party_number, i.surname, i.given_names
FROM   igs.igs_ad_relations_int_all i, hz_parties p
WHERE  i.rel_person_id = p.party_id
AND    i.interface_id = :interface_id;

Other scenarios include auditing reciprocal relationships flagged through RECIPROCAL_FLAG, validating birth and deceased dates prior to promotion, and packaging interface records for re-processing after correction.

Related Objects

Five interface child tables reference IGS_AD_RELATIONS_INT_ALL through INTERFACE_RELATIONS_ID. These include IGS_AD_RELADDR_INT_ALL (relationship address details), IGS_AD_RELEMP_INT_ALL (employment data), IGS_AD_RELACAD_INT_ALL (academic data), IGS_AD_REL_CON_INT_ALL (contact data), and IGS_AD_IMP_NEAR_MTCH_ALL (near-match results). The table itself references HZ_PARTIES via REL_PERSON_ID. Together these objects form the relationship interface family orchestrated by the Student System import concurrent programs.