Search Results igs_ad_reladdr_int_all_u1

Overview

IGS.IGS_AD_RELADDR_INT_ALL is a public interface (staging) table in the Oracle E-Business Suite IGS (Student Systems / Advanced Admissions) product schema. It stores the address details of relatives associated with each person scheduled for import. During the Admission Import process, the rows held here are validated, matched, and ultimately written into the production TCA and student tables HZ_PARTY_SITES, HZ_LOCATIONS, and IGS_PE_HZ_PTY_SITES.

The table resides in the APPS_TS_INTERFACE tablespace with a PCTFREE of 10, indicating an interface workload characterized by frequent inserts and updates rather than long-lived production data. Its lifecycle status is active (not obsolete), and the FND design data reference is IGS.IGS_AD_RELADDR_INT_ALL. The object is categorized as a BUSINESS_ENTITY tied to HZ_PERSON.

From a Data Vault modeling perspective, the heuristic classification of this table is standalone (no dimensionally significant outgoing relationships beyond the operational FK). This reflects its role as a flat staging structure that carries both relationship keys and descriptive address attributes in a single denormalized row, rather than a normalized hub or link.

Key Information Stored

The table holds 36 documented columns. The most operationally significant are:

• INTERFACE_RELADDR_ID — Surrogate primary key, enforced by unique index IGS_AD_RELADDR_INT_ALL_U1 and the primary key constraint IGS_AD_RELADDR_INT_PK. It is the only documented unique-index (business-key candidate) column.
• INTERFACE_RELATIONS_ID — Foreign key to IGS_AD_RELATIONS_INT_ALL, tying this address line to its parent relation record.
• INTERFACE_RUN_ID — Groups all interface rows belonging to a single import run; indexed non-uniquely for bulk selection and purge.
• ORG_ID — Operating unit identifier, supporting multi-org security in the import.
• STATUS — Import state of the row (e.g., pending, processed, error); indexed non-uniquely.
• CORRESPONDENCE — Flag indicating whether the address is a correspondence address.
• START_DT / END_DT — Effective date range for the address.
• ADDR_LINE_1 through ADDR_LINE_4 — The four free-form address lines (240 characters each).
• CITY, STATE, PROVINCE, COUNTY, COUNTRY, POSTAL_CODE — Geographic components used to resolve or create an HZ_LOCATIONS entry.
• SITE_USE_CODE_1 / SITE_USE_CODE_2 — Site-use codes that govern how the resulting HZ_PARTY_SITES row is consumed downstream.
• MATCH_IND — Match indicator used by the import to signal whether an existing location/party site was found.
• ERROR_CODE — Populated when validation fails, driving the rejection/reprocessing logic.

Standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and concurrent-program audit columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) are also present.

Common Use Cases and Queries

The principal use case is pre-import inspection and troubleshooting of the Admission Import. A typical pattern is to enumerate all pending addresses for a given run:

SELECT INTERFACE_RELADDR_ID, INTERFACE_RELATIONS_ID, ADDR_LINE_1, CITY, POSTAL_CODE, STATUS, ERROR_CODE FROM IGS.IGS_AD_RELADDR_INT_ALL WHERE INTERFACE_RUN_ID = :run_id AND STATUS = 'PENDING';

Because only STATUS and INTERFACE_RUN_ID are indexed (plus the PK), filtered queries on ORG_ID or MATCH_IND will scan unless combined with an indexed predicate. A second scenario joins the parent relation table to resolve which person the address belongs to, using INTERFACE_RELATIONS_ID. Error reporting queries filter on ERROR_CODE IS NOT NULL to list rejected rows for correction and resubmission. A third scenario counts records per run for reconciliation: GROUP BY INTERFACE_RUN_ID against STATUS. Post-import, DBAs purge processed rows to keep the interface tablespace lean.

Related Objects

• IGS.IGS_AD_RELATIONS_INT_ALL — Parent table; joined on INTERFACE_RELATIONS_ID = IGS_AD_RELATIONS_INT_ALL.INTERFACE_RELATIONS_ID (the only FK listed).
• HZ_PARTY_SITES — Target table populated by the import from validated rows.
• HZ_LOCATIONS — Target location master receiving address components.
• IGS_PE_HZ_PTY_SITES — Student-specific party-site target.
• HZ_PERSON / HZ_PARTIES — The party context (category BUSINESS_ENTITY HZ_PERSON) that the relationships resolve to.
• Concurrent program / FND run objects — Referenced via REQUEST_ID and PROGRAM_ID for job-level traceability.