Search Results ad_batch_id

Overview

IGS.IGS_UC_APP_ADDRESES is a transactional table in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 data model, owned by the IGS schema. It stores UCAS (Universities and Colleges Admissions Service) applicant address details, capturing both correspondence and home address information for applicants processed through the UK admissions interface. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and it carries FND Design Data registration under IGS.IGS_UC_APP_ADDRESES, confirming it as a recognized application object within the EBS data dictionary.

From a heuristic Data Vault modeling perspective, the table is classified as standalone, meaning no foreign key dependencies were mined from the documented physical structure. This classification is a suggestion rather than a formal constraint: the table behaves principally as a satellite-like store of descriptive applicant attributes, keyed by an applicant identifier. Because the same key column (APP_NO) both identifies the applicant and anchors the address records, a hub-and-satellite decomposition would place APP_NO in a hub and the address attributes in one or more satellites, with AD_BATCH_ID and AD_INTERFACE_ID potentially modeled as link references to the admissions import process. The table also contains standard WHO audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN), which support change tracking and data lineage.

Key Information Stored

The surrogate primary key is APP_NO (NUMBER(22)), the unique applicant identifier, enforced by the unique index IGS_UC_APP_ADDRESES_PK. No separate business-key candidate exists beyond this unique index; APP_NO serves as both the technical and business identifier for the row.

The most significant descriptive columns fall into two address groups:

• Correspondence address: ADDRESS1 through ADDRESS4 (VARCHAR2(50) each), POST_CODE, MAIL_SORT, COUNTRY_CODE, and ADDRESS_AREA.
• Home address: HOME_ADDRESS1 through HOME_ADDRESS4 (VARCHAR2(50) each), HOME_POSTCODE, and HOME_COUNTRY_CODE.
• Contact channels: TELEPHONE (35), HOME_PHONE (24), MOBILE (35), FAX, HOME_FAX, EMAIL (60), and HOME_EMAIL. These distinguish correspondence contact details from home-based ones.
• Interface and processing flags: SENT_TO_OSS_FLAG (Sent to OSS indicator, indexed by IGS_UC_APP_ADDRESES_N1), AD_BATCH_ID (Admissions Import Batch ID, indexed by IGS_UC_APP_ADDRESES_N2), and AD_INTERFACE_ID (Admissions Import Interface ID).

The query term "home_postcode" maps directly to the HOME_POSTCODE column, which holds the postcode of the applicant's home address. It is deliberately distinct from POST_CODE, which stores the correspondence address postcode.

Common Use Cases and Queries

Typical reporting and integration scenarios include extracting applicant address data for admissions processing, validating correspondence versus home addresses, and monitoring which records have been transmitted to the OSS system. A representative query filtering by home postcode is:

• SELECT app_no, home_address1, home_postcode, home_country_code FROM igs.igs_uc_app_addreses WHERE home_postcode = :p_postcode;
• SELECT app_no, ad_batch_id, ad_interface_id FROM igs.igs_uc_app_addreses WHERE sent_to_oss_flag = 'N' AND ad_batch_id = :p_batch;

The indexes IGS_UC_APP_ADDRESES_N1 (SENT_TO_OSS_FLAG) and IGS_UC_APP_ADDRESES_N2 (AD_BATCH_ID) support these filters efficiently, while the primary key index supports point lookups by applicant number. Address parsing and deduplication reports can join the correspondence and home columns to detect mismatched postcodes or country codes.

Related Objects

Because the documented relationship data classifies this table as standalone, no foreign key columns are formally recorded. Dependencies should therefore be inferred from shared column semantics rather than enforced constraints. The most significant associated objects include:

• IGS.IGS_UC_APPLICANTS (or equivalent UCAS applicant master), joined on APP_NO, which supplies applicant identity and status.
• Admissions import interface tables referenced by AD_INTERFACE_ID, which stage inbound UCAS data prior to loading.
• Admissions import batch tables referenced by AD_BATCH_ID, which group the records processed together.
• OSS transmission or outbound staging objects implied by SENT_TO_OSS_FLAG, used to confirm onward delivery.
• Standard EBS WHO audit objects such as FND_USER, joined via CREATED_BY and LAST_UPDATED_BY for user attribution.

Consult the IGS schema's admissions-related views and the UCAS interface concurrent programs to confirm the precise join columns used in production integrations.