Search Results igs_ad_api_int_all




Overview
Explore in AI Chat

Overview

IGS_AD_API_INT_ALL is a table in the IGS (Student System) product schema of Oracle E-Business Suite, present and valid in both 12.1.1 and 12.2.2. It is an interface (staging) table that holds information about a person's alternate person identifiers — the multiple identification values the institution tracks for a learner or applicant, such as national IDs, legacy system numbers, or external agency references. As an _INT_ALL interface table, it follows the standard Oracle EBS integration pattern: records are populated by an external process or feeder program, validated and matched against the production person tables, then either applied or rejected with an error code.

The table is classified heuristically as a standalone object under Data Vault modeling conventions, meaning it exposes no dependency relationship to other tables through its primary key. For modeling purposes, it is best treated as a hub-like or transfer structure rather than a link or satellite, since the surrogate key INTERFACE_API_ID does not participate in a foreign-key relationship to other ETRM objects.

Key Information Stored

  • INTERFACE_API_ID — surrogate primary key for the row, enforced by the IGS_AD_API_INT_PK constraint. No business-key unique index is documented beyond this column, so it is the sole uniqueness guarantee for the interface record.
  • ORG_ID — operating unit / organization context, supporting multi-org data segregation.
  • INTERFACE_ID — business reference identifying the source interface record or batch item.
  • INTERFACE_RUN_ID — identifies the specific interface run that loaded or processed the row.
  • PERSON_ID_TYPE — the classification of the alternate identifier (for example national ID versus institutional ID), determining where the value is applied.
  • ALTERNATE_ID — the actual alternate identifier value to be validated and stored.
  • START_DT and END_DT — effective date range for the identifier's validity.
  • MATCH_IND — flag indicating whether the incoming identifier matched an existing person record.
  • STATUS and ERROR_CODE — processing outcome and, when applicable, the reason the row failed validation.
  • REGION_CD — region qualifier applied to the identifier.
  • REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — concurrent program execution context for traceability.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE20 — the DFF (descriptive flexfield) block for client-specific extensions.

Common Use Cases and Queries

The most frequent scenario is monitoring and troubleshooting identifier loads. Report rows that failed processing:

  • SELECT interface_api_id, alternate_id, person_id_type, error_code FROM igs.igs_ad_api_int_all WHERE status = 'ERROR';
  • Confirm all rows from a specific run were processed: ... WHERE interface_run_id = :run_id AND status IS NULL;
  • Audit which concurrent request loaded a batch: join REQUEST_ID to FND_CONCURRENT_REQUESTS to identify the originating program and its completion status.
  • Reconcile matched versus unmatched identifiers with GROUP BY person_id_type, match_ind for a load summary.
  • Identify duplicate or conflicting alternates across runs by grouping on PERSON_ID_TYPE and ALTERNATE_ID before validation is applied.

Related Objects

  • IGS_AD_API_INT_PK — the primary key constraint on INTERFACE_API_ID; the table's only documented integrity object.
  • Person identifier production tables (the IGS person/alternate-ID entities targeted by the interface) — the destination records populated once validation and matching succeed.
  • FND_CONCURRENT_REQUESTS — joined on REQUEST_ID to trace the concurrent program that loaded or processed the interface rows.
  • FND_CONCURRENT_PROGRAMS and FND_APPLICATION — joined via PROGRAM_ID and PROGRAM_APPLICATION_ID to identify the feeder process.
  • HR_OPERATING_UNITS / FND_ORGANIZATION_UNITS — joined on ORG_ID to resolve the operating unit context.

Because the table is documented as standalone, no FK relationships constrain it; all joins above are logical rather than enforced, which is typical for interface staging tables that must accept unvalidated inbound data before integrity checks are applied.