Search Results ev_form_stat_id

Overview

IGS.IGS_PE_EV_FORM_STAT is a transactional table in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, owned by the IGS schema (Oracle iLearning / Student Systems, used here in the context of Exchange Visitor Program management). The table records the status history of an Exchange Visitor (EV) form submitted by a student. Each row represents a discrete status action taken against a student's EV form, capturing when the action occurred, what type of action it was, and supplemental program/termination information. In this respect, the table functions as an audit-trail-style repository of form lifecycle events rather than a master entity holder.

From a Data Vault modeling perspective, the heuristic classification of this object is satellite-leaning. The table's primary key, EV_FORM_STAT_ID, is a surrogate identifier, while the meaningful business dimensions of the record (EV_FORM_ID, ACTION_DATE, ACTION_TYPE) form a unique alternate key. Because the table stores descriptive, time-stamped attributes about actions taken on a parent EV form, it is best modeled as a satellite attached to a hub (the EV form) and, potentially, to a link reflecting the action relationships. It is not itself a hub or a pure link.

Key Information Stored

The fourteen documented columns capture the identity, temporal, and descriptive attributes of each form-status action:

• EV_FORM_STAT_ID (NUMBER, 15) — The surrogate primary key. Uniquely identifies each status record and is enforced by the unique index IGS_EN_EV_FORM_STAT_PK.
• EV_FORM_ID (NUMBER, 15) — The foreign key to IGS_PE_EV_FORM, identifying the student's EV form to which this status action belongs. It is a business-key candidate as the leading column of the unique index IGS_EN_EV_FORM_STAT_U1.
• ACTION_DATE (DATE) — The date on which the status action was taken. Part of the composite unique key, together with EV_FORM_ID and ACTION_TYPE.
• ACTION_TYPE (VARCHAR2) — The type of action applied to the form's status (for example, initiation, approval, termination). Completes the composite business key.
• PRGM_START_DATE and PRGM_END_DATE (DATE) — The Exchange Visitor Program's start and end dates associated with the record.
• REMARKS (VARCHAR2, 500) — Free-text remarks describing the action taken. This is the largest descriptive attribute in the table.
• TERMINATION_REASON (VARCHAR2, 30) — Reason code explaining why the student's program was terminated.
• END_PROGRAM_REASON (VARCHAR2, 30) — Reason code indicating why the program ended.
• Standard WHO columns — CREATION_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN provide audit and ownership context required for Oracle EBS concurrent/reporting behavior.

Common Use Cases and Queries

The primary use case is reporting on the current or historical status of a student's EV form, and tracking changes over time. Typical queries join to IGS_PE_EV_FORM to resolve the form's context:

SELECT fs.EV_FORM_STAT_ID,
       fs.EV_FORM_ID,
       fs.ACTION_DATE,
       fs.ACTION_TYPE,
       fs.TERMINATION_REASON,
       fs.END_PROGRAM_REASON
FROM   IGS.IGS_PE_EV_FORM_STAT fs
WHERE  fs.EV_FORM_ID = :p_ev_form_id
ORDER  BY fs.ACTION_DATE DESC;

Common practical patterns include: (1) determining the latest status for a given EV form using a correlated subquery or analytic ranking on ACTION_DATE; (2) reporting on all students whose programs were terminated between two dates using TERMINATION_REASON and ACTION_DATE; and (3) producing an audit history of REMARKS entries for compliance review of a specific student's Exchange Visitor record. Because the composite unique index (EV_FORM_ID, ACTION_DATE, ACTION_TYPE) is enforced, a duplicate-free projection can be obtained by selecting on those three columns.

Related Objects

The documented relationship graph is relatively narrow, but the following are the most significant related objects and join paths:

• IGS.IGS_PE_EV_FORM — The direct foreign-key parent. Join on IGS_PE_EV_FORM_STAT.EV_FORM_ID = IGS_PE_EV_FORM.EV_FORM_ID. This is the principal join for resolving student/form context.
• APPS.IGS_PE_EV_FORM_STAT — The APPS-kernel synonym/view layered over the IGS table, referenced by the dependency metadata and used by application code running in the APPS schema. Queries should generally resolve through the APPS synonym to respect EBS naming conventions.
• Standard WHO dependencies — The CREATED_BY, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN columns indirectly reference FND_USER, enabling user-level audit reporting.
• Lookup/reference relationships — TERMINATION_REASON and END_PROGRAM_REASON are code columns conventionally validated against Oracle EBS lookup or validation tables (e.g., FND_LOOKUPS or IGS reference code tables), allowing descriptive reporting of reason codes.

When building reports or extracts, combine the parent EV form join with lookup resolution for the reason codes to produce a complete, readable status history for each Exchange Visitor record.