Results for “igs_pe_citizen_int_u1”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IGS.IGS_PE_CITIZEN_INT is a public interface table in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, owned by the IGS (Student System / People) schema. It functions as a staging area for citizenship records associated with persons before those records are imported into the production table HZ_CITIZENSHIP through the TCA (Trading Community Architecture) person import process. Its display name, "Import Citizenship," and its primary category, BUSINESS_ENTITY HZ_PERSON, confirm its role as a transient collection point for external or legacy citizenship data destined for the customer master.

The table resides in the APPS_TS_INTERFACE tablespace, which is the standard storage location for open-interface tables in EBS. It is not a permanent transaction table; rows are typically inserted by external loaders, batch processes, or concurrent programs, validated, and then consumed during import into HZ_CITIZENSHIP. The ETRM metadata classifies the object as standalone based on foreign-key analysis, meaning it has no outgoing FK relationships. In Data Vault modelling terms, the table behaves most like a staging satellite: a unique-keyed collection of descriptive attributes about a person's citizenship, with standard EBS who-columns providing audit context, and no enforced link to a hub within the interface schema itself.

Key Information Stored

The table contains 22 documented columns. The following are the most operationally significant:

• INTERFACE_CITIZENSHIP_ID (NUMBER 15) — The surrogate primary key and the column underpinning the unique index IGS_PE_CITIZEN_INT_U1. It must be populated by the loading process and uniquely identifies each interface row.
• INTERFACE_ID (NUMBER 15) — The interface identifier that groups related rows; indexed by the non-unique index IGS_PE_CITIZEN_INT_N1.
• COUNTRY_CODE — The citizenship country assigned to the person.
• DOCUMENT_TYPE (VARCHAR2 30) — Lookup code identifying the supporting document, e.g., passport or naturalization certificate.
• DOCUMENT_REFERENCE (VARCHAR2 60) — Reference number of the supporting document.
• DATE_RECOGNIZED — Start date of citizenship validity.
• END_DATE — End date of citizenship validity.
• DATE_DISOWNED — Date the person disowned the citizenship.
• MATCH_IND — Match indicator used during deduplication or matching against existing person records.
• STATUS (VARCHAR2) — Import status: 1 Completed, 2 Pending, 3 Error, 4 Warning; indexed by IGS_PE_CITIZEN_INT_N2.
• ERROR_CODE (VARCHAR2 30) — Error or warning code raised during validation.
• DUP_CITIZENSHIP_ID (NUMBER 15) — Reference to a detected duplicate citizenship record.
• INTERFACE_RUN_ID — Batch run identifier; indexed by IGS_PE_CITIZEN_INT_N3.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — Concurrent program who-columns identifying the job that loaded or processed the row.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard audit who-columns.

The business-key candidate is INTERFACE_CITIZENSHIP_ID, enforced by the unique index IGS_PE_CITIZEN_INT_U1. No other column is uniquely constrained; INTERFACE_ID, STATUS, and INTERFACE_RUN_ID are supporting access paths only.

Common Use Cases and Queries

Typical scenarios involve loading external citizenship data, monitoring import progress, and diagnosing errors before committing records to HZ_CITIZENSHIP.

• Identifying pending rows awaiting import: SELECT INTERFACE_CITIZENSHIP_ID, COUNTRY_CODE, STATUS FROM IGS_PE_CITIZEN_INT WHERE STATUS = 2;
• Isolating failed records for correction: SELECT INTERFACE_CITIZENSHIP_ID, INTERFACE_ID, ERROR_CODE FROM IGS_PE_CITIZEN_INT WHERE STATUS = 3;
• Reviewing all rows from a specific batch: SELECT * FROM IGS_PE_CITIZEN_INT WHERE INTERFACE_RUN_ID = :run_id;
• Tracking duplicates flagged during validation: SELECT INTERFACE_CITIZENSHIP_ID, DUP_CITIZENSHIP_ID, MATCH_IND FROM IGS_PE_CITIZEN_INT WHERE DUP_CITIZENSHIP_ID IS NOT NULL;
• Reporting on load performance by concurrent program: SELECT PROGRAM_ID, REQUEST_ID, COUNT(*) FROM IGS_PE_CITIZEN_INT GROUP BY PROGRAM_ID, REQUEST_ID;

Reporting is generally limited to import validation and reconciliation rather than transactional analytics, since the table is purged or archived after successful import.

Related Objects

• HZ_CITIZENSHIP — The target production table to which validated rows are imported; joins on person party relationship and country code.
• HZ_PERSON — The business entity referenced by the interface category; person records are matched during import.
• IGS_PE_PERSON_INT — The companion person interface table; INTERFACE_ID correlates records across interfaces.
• FND_CONCURRENT_REQUESTS — Joined via REQUEST_ID to identify the concurrent job that loaded or processed rows.
• FND_LOOKUP_VALUES — Resolves DOCUMENT_TYPE lookup codes.
• HZ_IMP_PERSON_TCA / TCA person import APIs — Consume rows from this interface during the import-to-TCA process.
• IGS_PE_CITIZEN_INT_U1, _N1, _N2, _N3 — Indexes supporting unique and query access paths, notably for the value searched (igs_pe_citizen_int_u1) on INTERFACE_CITIZENSHIP_ID.