Search Results igs_ad_reladdr_int_pk

Overview

IGS_AD_RELADDR_INT_ALL is an Oracle E-Business Suite interface table owned by the IGS schema, which supports the Oracle Student System (formerly Oracle Student Systems / IGS) product family. As the name and the _INT_ALL suffix indicate, the table is a staging or inbound interface object rather than a transactional base table. Its documented Description states that it "contains information about person's relatives' address details," meaning it holds the mailing and location attributes associated with a student's or person's relatives — parents, guardians, sponsors, or other related parties — before or during data migration, integration, or batch loading into the operational student tables.

The table is created as IGS.IGS_AD_RELADDR_INT_ALL and is reported as VALID in the data dictionary. It is isolated from the operational relational core by design; the ETRM relationship metadata classifies it heuristically as standalone within a Data Vault-style modeling suggestion, indicating that it functions as an independent staging entity rather than as a hub, link, or satellite. It carries only a single inherited foreign key relationship and no downstream dependents in the documented model, consistent with its role as an intake buffer that is consumed by concurrent programs rather than queried directly by application logic.

Key Information Stored

The table documents 36 columns in the ETRM 12.1.1 physical schema. The most significant are:

• INTERFACE_RELADDR_ID — the surrogate primary key, enforced by constraint IGS_AD_RELADDR_INT_PK and also unique index IGS_AD_RELADDR_INT_ALL_U1. This is the row-level identity within the interface batch.
• INTERFACE_RELATIONS_ID — the foreign key to IGS_AD_RELATIONS_INT_ALL, tying each address row back to the corresponding relative record. This is the true business linkage column.
• ORG_ID — the operating unit / multi-org discriminator, essential for correct partitioning of data during posting.
• CORRESPONDENCE — a flag indicating whether the address is the preferred correspondence destination for the relative.
• START_DT / END_DT — the effective date range during which the address is considered valid for the relative.
• ADDR_LINE_1 through ADDR_LINE_4, CITY, STATE, PROVINCE, COUNTY, COUNTRY, POSTAL_CODE — the address components themselves.
• SITE_USE_CODE_1 / SITE_USE_CODE_2, LOCATION_1 / LOCATION_2 — site-use classifications and location key references used during validation against Oracle location data.
• MATCH_IND — a matching indicator used by the import process to detect duplicate or already-existing addresses.
• STATUS and ERROR_CODE — the processing state and failure reason for each staged row.
• INTERFACE_RUN_ID, REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID — the concurrent request context that produced the interface batch.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard WHO audit columns.

Only INTERFACE_RELADDR_ID is documented as a unique index (IGS_AD_RELADDR_INT_ALL_U1); no separate business-key unique index is listed, so the interface ID functions as both surrogate and practical identifier.

Common Use Cases and Queries

Typical usage involves loading relative address data from legacy systems or external agencies, validating and correcting it, then submitting the IGS import concurrent program to post correct rows into the operational tables. Common SQL patterns include:

• Checking staging rows for a specific relative before importing:

SELECT a.interface_reladdr_id, a.city, a.state, a.postal_code, a.status, a.error_code
FROM   igs.igs_ad_reladdr_int_all a
WHERE  a.interface_relations_id = :relations_id
AND    a.status = 'NEW';

• Reporting failed address rows by error code:

SELECT error_code, COUNT(*)
FROM   igs.igs_ad_reladdr_int_all
WHERE  status = 'ERROR'
GROUP  BY error_code;

• Extracting addresses from a particular concurrent run:

SELECT * FROM igs.igs_ad_reladdr_int_all
WHERE  interface_run_id = :run_id;

Reporting use cases include auditing correspondence addresses for guardians, reconciling in-flight interface loads, and producing data quality reports on postal code or country completeness before committing to the base tables.

Related Objects

• IGS_AD_RELATIONS_INT_ALL — the parent interface table; joined on IGS_AD_RELADDR_INT_ALL.INTERFACE_RELATIONS_ID = IGS_AD_RELATIONS_INT_ALL.INTERFACE_RELATIONS_ID. This is the only documented foreign key.
• IGS_AD_RELADDR (operational base table) — the target of the import process for validated rows.
• IGS_AD_RELATIONS — the operational relative record to which posted addresses ultimately attach.
• HZ_LOCATIONS — used during validation of LOCATION_1 / LOCATION_2 references.
• HZ_LOCATION_PROFILES — source of site-use code validation for SITE_USE_CODE_1 / SITE_USE_CODE_2.
• FND_CONCURRENT_REQUESTS — joined via REQUEST_ID for tracking the populating concurrent program.

These relationships confirm the table's role as a controlled staging surface feeding the IGS student and relationship address model.