Search Results igs_ad_addr_int_all




Overview
Explore in AI Chat

Overview

IGS_AD_ADDR_INT_ALL is an interface (staging) table in the Oracle E-Business Suite Student System (IGS) product family. It holds information about a person's address details prior to validation and transfer into the Oracle Trading Community Architecture (TCA) model. The table is owned by the IGS schema and is designated VALID in the E-Business Suite Technical Reference Manual for releases 12.1.1 and 12.2.2.

The table functions as the inbound conduit for address data entering the Student System. Rows are written by interface programs, validated and cleansed, and then propagated to the TCA party site model. The presence of columns such as MATCH_IND, STATUS, ERROR_CODE, and DUP_PARTY_SITE_ID confirms that the table supports deduplication and error-handling semantics typical of an EBS inbound interface.

From a Data Vault modeling perspective, the mined foreign-key structure suggests a satellite-leaning classification. The table carries a single-column surrogate primary key, IGS_AD_ADDR_INT_PK, defined over INTERFACE_ADDR_ID, and a single outbound foreign key to HZ_PARTY_SITES, while it is itself referenced by IGS_AD_ADDRUSAGE_INT_ALL. This dependency profile is consistent with a satellite structure attached to a core address hub concept, with the usage table acting as a dependent child.

Key Information Stored

The documented physical schema comprises 37 columns. The most operationally significant are listed below.

  • INTERFACE_ADDR_ID — The surrogate primary key, enforced through IGS_AD_ADDR_INT_PK. It uniquely identifies each staged address record and is the join key to IGS_AD_ADDRUSAGE_INT_ALL.
  • INTERFACE_ID — Groups rows belonging to a single interface run or batch, allowing a batch to be isolated for validation and purge. Together with INTERFACE_RUN_ID it forms the practical batch identifier.
  • ADDR_LINE_1 through ADDR_LINE_4 — The free-form address street lines captured from the source feed.
  • CITY, STATE, COUNTY, PROVINCE, COUNTRY, POSTCODE — The geographic components of the address, used for validation against TCA geography data.
  • ORG_ID — The operating unit or business unit context for the record, supporting multi-org reporting.
  • STATUS and ERROR_CODE — The processing outcome for each row. STATUS indicates whether the row is pending, validated, or failed; ERROR_CODE captures the specific validation failure.
  • MATCH_IND — A matching indicator used during deduplication against existing party sites.
  • DUP_PARTY_SITE_ID — The foreign key to HZ_PARTY_SITES, recording the TCA party site identified as a duplicate or as the target of the address record.
  • CONTACT_PERSON_ID and CORRESPONDENCE_FLAG — Identify the person associated with the address and whether it is designated for correspondence.
  • START_DATE and END_DATE — Effective-dating attributes describing the validity window of the address.
  • DATE_LAST_VERIFIED — The date on which the address was last confirmed as accurate.
  • Audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) — Standard EBS who-columns and concurrent program context used for traceability.

No unique business key is documented beyond the surrogate primary key; candidate business keys would typically be composite combinations of the interface identifier and person reference.

Common Use Cases and Queries

The primary use case is monitoring and troubleshooting address interface runs. Standard EBS concurrent programs populate the table, validation logic sets STATUS and ERROR_CODE, and a subsequent process transfers valid rows to TCA via HZ_PARTY_SITES. The following query identifies all failed rows in a batch.

  • SELECT INTERFACE_ADDR_ID, INTERFACE_ID, ADDR_LINE_1, CITY, POSTCODE, STATUS, ERROR_CODE FROM IGS_AD_ADDR_INT_ALL WHERE STATUS = 'ERROR' AND INTERFACE_ID = :p_interface_id;
  • Detecting potential duplicates by joining to the TCA party site referenced through DUP_PARTY_SITE_ID.
  • Validating an inbound file by reconciling the row count for a given INTERFACE_RUN_ID against the expected source total.
  • Reporting addresses pending correspondence within an operating unit: SELECT * FROM IGS_AD_ADDR_INT_ALL WHERE CORRESPONDENCE_FLAG = 'Y' AND ORG_ID = :p_org_id;
  • Purging processed rows after transfer to control table growth and preserve interface performance.

Related Objects

The following objects are the most significant in relation to this table, based on the documented foreign-key relationships.

  • HZ_PARTY_SITES — The TCA party site table referenced by IGS_AD_ADDR_INT_ALL.DUP_PARTY_SITE_ID. This is the principal target of the address transfer process.
  • IGS_AD_ADDRUSAGE_INT_ALL — The companion interface table that references this table through INTERFACE_ADDR_ID, holding address usage type detail for each staged address.
  • IGS_AD_ADDR_INT_PK — The primary key constraint over INTERFACE_ADDR_ID.
  • HZ_LOCATIONS and HZ_PARTIES — The underlying TCA tables that ultimately receive validated address and person data.
  • IGS_AD_ADDRESS_API and related IGS address PL/SQL packages — The validation and transfer logic that reads from and updates STATUS, ERROR_CODE, and MATCH_IND on this table.

Together these objects form the inbound address pipeline for the Oracle Student System, linking external address feeds to the TCA party site model.