Search Results igs_pe_citizen_int

Overview

IGS_PE_CITIZEN_INT is a staging interface table belonging to the IGS (Student System) product family, which is classified as obsolete in Oracle EBS 12.1.1 and 12.2.2. The table holds person citizenship records prior to their validation and migration into the Oracle Human Resources (HR) or Trading Community Architecture (TCA) person model, specifically the HZ_CITIZENSHIP table. Its purpose is to decouple inbound citizenship data — whether sourced from legacy feeds, third-party systems, batch loads, or integration programs — from the transactional citizenship table, allowing upstream matching, validation, error handling, and duplicate detection before a permanent record is created.

From a heuristic Data Vault modeling perspective, the mined FK structure classifies this object as a standalone entity. No foreign key relationships were documented, so a strict hub, link, or satellite assignment is not supported by the metadata. In a Data Vault design, the interface rows would most naturally be modeled as a staging or raw satellite structure keyed on the interface identifier, with the target HZ_CITIZENSHIP record representing the resolved hub/link relationship. Because IGS is obsolete, the object should be treated as a legacy integration artifact rather than an active business entity.

Key Information Stored

The table contains 22 documented columns. The surrogate primary key is INTERFACE_CITIZENSHIP_ID, enforced through the constraint IGS_PE_CITIZEN_INT_PK and additionally backed by the unique index IGS_PE_CITIZEN_INT_U1. This makes INTERFACE_CITIZENSHIP_ID the sole documented business-key candidate, although in practice it functions as a technical row identifier for the staging session.

• INTERFACE_ID — a batch or group identifier tying related interface rows to a single load run.
• COUNTRY_CODE — the country of citizenship to be transferred to HZ_CITIZENSHIP.
• DOCUMENT_TYPE and DOCUMENT_REFERENCE — evidence of citizenship, such as passport or naturalization documents.
• DATE_RECOGNIZED and DATE_DISOWNED — the effective recognition and disavowal dates for the citizenship claim.
• END_DATE — the termination date of the citizenship record.
• MATCH_IND — indicates whether the staged row has been successfully matched to an existing HZ_CITIZENSHIP record.
• STATUS and ERROR_CODE — the disposition of the row after interface processing and any failure reason.
• DUP_CITIZENSHIP_ID — a reference to a detected duplicate citizenship record.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, and PROGRAM_UPDATE_DATE — standard concurrent program execution context.
• INTERFACE_RUN_ID — identifies the parent import run.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN — standard WHO audit columns.

Common Use Cases and Queries

The principal use case is running the citizenship import concurrent program that reads IGS_PE_CITIZEN_INT, validates each row, and inserts corresponding records into HZ_CITIZENSHIP. Typical operational queries include reviewing pending rows before import, diagnosing failed rows after import, and reconciling counts between staging and target tables.

• Reviewing pending rows: SELECT interface_citizenship_id, country_code, status FROM igs_pe_citizen_int WHERE status IS NULL OR status = 'PENDING';
• Diagnosing errors: SELECT interface_citizenship_id, error_code FROM igs_pe_citizen_int WHERE error_code IS NOT NULL;
• Tracking a batch: SELECT COUNT(*) FROM igs_pe_citizen_int WHERE interface_id = :batch_id;
• Auditing a concurrent run: SELECT interface_run_id, request_id, program_id FROM igs_pe_citizen_int WHERE request_id = :req;

Reporting needs commonly include citizenship counts by country, match rates (MATCH_IND), and duplicate detection statistics computed from DUP_CITIZENSHIP_ID.

Related Objects

The most significant dependent object is HZ_CITIZENSHIP, the target table into which validated rows are loaded; the interface columns COUNTRY_CODE, DATE_RECOGNIZED, DATE_DISOWNED, and END_DATE map directly to its attributes. Related staging and processing objects include IGS_PE_PERSON_INT, which supplies person identity context; the HZ_PARTIES and HZ_PERSON_PROFILES tables that define the party for whom citizenship is recorded; the FND_CONCURRENT_REQUESTS view, joined on REQUEST_ID to trace the importing run; and the import concurrent program registered in FND_CONCURRENT_PROGRAMS, tied through PROGRAM_ID and PROGRAM_APPLICATION_ID. The HZ_CITIZENSHIP_PKG and related TCA party APIs typically govern insertion of the final citizenship rows.