Search Results interface_perstmt_id

Overview

IGS.IGS_AD_PERSTMT_INT is an Oracle E-Business Suite interface (staging) table that belongs to the IGS (Student Systems / Admissions) product. It stores personal statement details for each person who is to be imported into the application personal statement table IGS_AD_APPL_PERSTAT through the Admission Import process. In other words, it is the inbound landing area where external or legacy admissions data is staged before the Admissions import concurrent program validates, transforms, and promotes it into the production base tables.

The table resides in the APPS_TS_INTERFACE tablespace with a PCT Free of 10, is classified as a BUSINESS_ENTITY within the IGS_ADM_APPLICATION category, and is marked as active with public scope. Heuristically, the documented relationship structure classifies this object as standalone. Under a Data Vault modeling suggestion this would be treated as an interface/staging artifact rather than a core hub, link, or satellite; the surrogate key INTERFACE_PERSTMT_ID functions as a row-level identifier rather than a durable business hub key.

Key Information Stored

The table contains 19 documented columns. The most significant are:

• INTERFACE_PERSTMT_ID (NUMBER 15) — the unique identifier for each interface row. It is the documented surrogate primary key, enforced by unique index IGS_AD_PERSTMT_INT_PK, and is the column the user searched for.
• INTERFACE_APPL_ID (NUMBER 15) — the interface application ID linking the personal statement to its parent application record. It is a foreign key to IGS_AD_APL_INT and is indexed by IGS_AD_PERSTMT_INT_N1.
• PERSL_STAT_TYPE (VARCHAR2 30) — the personal statement type attached to the application personal statement.
• DATE_RECEIVED (DATE) — the date the statement was received; it must not be greater than the system date.
• STATUS (VARCHAR2) — the processing status of the interface record, indexed by IGS_AD_PERSTMT_INT_N2 and used by the import program to drive validation and error recovery.
• ERROR_CODE (VARCHAR2 30) and ERROR_TEXT — the code and descriptive text describing why a staged row failed import validation.
• INTERFACE_RUN_ID (NUMBER) — identifies the specific instance of the Import Process Run that created or last processed the row, indexed by IGS_AD_PERSTMT_INT_N3.
• PERSON_ID and ADMISSION_APPL_NUMBER — the person and admission application number tied to the staged statement.
• Standard Who columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, plus the concurrent program columns REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, and PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

The primary use case is monitoring and troubleshooting the Admissions personal statement import. Typical queries check for unprocessed or errored rows, and confirm which run produced them:

• List all failed rows for a run: SELECT INTERFACE_PERSTMT_ID, INTERFACE_APPL_ID, PERSL_STAT_TYPE, ERROR_CODE, ERROR_TEXT FROM IGS.IGS_AD_PERSTMT_INT WHERE INTERFACE_RUN_ID = :run_id AND STATUS = 'ERROR';
• Count rows by status to gauge import completeness: SELECT STATUS, COUNT(*) FROM IGS.IGS_AD_PERSTMT_INT GROUP BY STATUS;
• Audit which concurrent request loaded a batch: join REQUEST_ID to FND_CONCURRENT_REQUESTS to trace the originating program and timing.
• Validate data quality before import, for example flagging records where DATE_RECEIVED exceeds SYSDATE.
• Reconcile staged rows against the target IGS_AD_APPL_PERSTAT after import to confirm all eligible statements were promoted.

Related Objects

The most significant related objects, based on the documented foreign key and dependency metadata, are:

• IGS.IGS_AD_APL_INT — the parent application interface table, joined via INTERFACE_APPL_ID.
• IGS.IGS_AD_APPL_PERSTAT — the target application personal statement table populated by the import.
• IGS_AD_PERSTMT_INT_PK, _N1, _N2, _N3 — the primary key and supporting indexes on INTERFACE_PERSTMT_ID, INTERFACE_APPL_ID, STATUS, and INTERFACE_RUN_ID.
• FND_CONCURRENT_REQUESTS and the concurrent program definition tables — referenced through REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID to trace the import run.
• FND_USER — referenced through the Who columns for created and updated user attribution.