Search Results igs_ad_citizen_int_all

Overview

The IGS_AD_CITIZEN_INT_ALL table is a component of the Oracle E-Business Suite IGS – Student System product family, carrying the suffix _INT_ALL that identifies it as a temporary staging or interface structure rather than a transactional base table. As documented in the ETRM metadata, the table holds information about citizenship details and is flagged as Obsolete in the 12.1.1 and 12.2.2 reference models. The _ALL suffix conventionally signals that the table is partitioned by ORG_ID, permitting multi-organization visibility of the staged rows.

In practice, this table acts as the landing zone where externally sourced citizenship records — whether loaded from a legacy institution system, a national registry feed, or an inter-institution exchange — are written before validation and ultimate promotion into the operational student records. This staging pattern is characteristic of the Oracle Student System (OSS) data conversion and integration framework, where _INT tables are paired with batch programs that read them, apply matching rules, and write matched results back into the interface row for review.

The metadata records that the object is not implemented in this database, confirming that it exists only in the shipped schema definition and must be instantiated as part of an installation or upgrade that includes the Student System interfaces. Heuristically, and solely as a modeling suggestion, the table's structure — with a single surrogate interface identifier, no incoming foreign keys, and predominantly descriptive and status attributes — would be classified in Data Vault terms as a standalone satellite, suited to holding attribute history around an interface load batch rather than participating in a shared hub or link topology.

Key Information Stored

The physical schema documented for ETRM 12.1.1 lists twenty-three columns owned by the IGS schema. The primary key is the unique index IGS_AD_CITIZEN_INT_U1, defined on the surrogate key column INTERFACE_CITIZEN_ID. This is the technical row identifier assigned by the interface loader; it is not a business identifier and carries no meaning outside the staging lifecycle. Business-key candidates for reconciliation purposes include INTERFACE_ID in combination with DUP_CITIZENSHIP_ID, and, for externally sourced attributes, DOCUMENT_TYPE together with DOCUMENT_REFERENCE, which together represent a person's citizenship or identification document.

The most consequential descriptive and control columns are:

• ORG_ID — the operating unit that owns the staged row, driving multi-organization security.
• INTERFACE_ID — the batch or load run with which the row was submitted.
• BIRTH_OR_SELECTED — a flag or code distinguishing citizenship acquired by birth from citizenship selected or elected.
• COUNTRY_CODE — the country of the citizenship record.
• DATE_RECOGNIZED and DATE_DISOWNED — the effective period during which the citizenship was recognized or renounced.
• END_DATE — the termination date of the citizenship record.
• STATUS and MATCH_IND — the processing state and the indicator of whether the staged record was successfully matched to an existing person.
• ERROR_CODE — the validation failure reason when processing does not succeed.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — the concurrent program context that created or last modified the row.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns.

Common Use Cases and Queries

Reporting against an interface table is typically concerned with load health rather than with the citizenship facts themselves. A standard reconciliation query groups staged rows by interface run and reports match and error status:

• SELECT INTERFACE_ID, STATUS, MATCH_IND, COUNT(*) FROM IGS_AD_CITIZEN_INT_ALL WHERE ORG_ID = :org GROUP BY INTERFACE_ID, STATUS, MATCH_IND;
• SELECT INTERFACE_CITIZEN_ID, ERROR_CODE FROM IGS_AD_CITIZEN_INT_ALL WHERE ERROR_CODE IS NOT NULL AND REQUEST_ID = :request_id;
• SELECT COUNTRY_CODE, BIRTH_OR_SELECTED, COUNT(*) FROM IGS_AD_CITIZEN_INT_ALL WHERE STATUS = 'P' GROUP BY COUNTRY_CODE, BIRTH_OR_SELECTED;

Because the table is obsolete and not implemented, the principal use cases are conversion auditing, detection of duplicate citizenship submissions, and support for purge or archival procedures that remove successfully processed rows after promotion.

Related Objects

The metadata classifies the table as standalone, with no documented inbound or outbound foreign keys beyond its own primary key. The following related objects are the most significant in practice:

• IGS_AD_CITIZEN_ALL — the operational citizenship base table that is the typical promotion target for matched interface rows.
• IGS_AD_PERSON_ALL / HZ_PARTIES — the person or party to which a staged citizenship is ultimately attached.
• IGS_AD_DOCUMENT_INT_ALL and other IGS _INT_ALL staging tables — sibling interface structures processed by the same batch programs.
• FND_CONCURRENT_REQUESTS — joined on REQUEST_ID to attribute a staged row to its load run.
• FND_APPLICATION and FND_PROGRAM — joined on PROGRAM_APPLICATION_ID and PROGRAM_ID to identify the loader.
• HR_ALL_ORGANIZATION_UNITS — joined on ORG_ID to resolve the operating unit that owns the staged data.

No public APIs are documented against this obsolete structure; integration should be directed to the current Student System citizenship objects.