Results for “igs_pe_act_site”

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

Overview

IGS_PE_ACT_SITE is a table in the IGS (Student System) product schema of Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the Exchange Visitor Site of Activity records associated with persons tracked in the IGS Student System and, by foreign key, in the Trading Community Architecture. The table captures where an exchange visitor (for example, a J-1 scholar or student) conducts their approved program activity, together with the effective dates of that activity site assignment and a flag identifying the primary site.

Because the table resolves a many-to-many relationship between a person party and an activity location, the ETRM heuristic Data Vault classification is link. This should be treated as a modeling suggestion rather than a documented architectural statement. The linking role is evident from the primary key, which combines a person identifier, an activity site code, and a start date, and from the two foreign keys pointing to HZ_PARTIES and IGS_AD_LOCATION_ALL.

Key Information Stored

The documented physical schema for 12.1.1 lists 12 columns. The most significant are:

• PERSON_ID — Identifies the exchange visitor. It is part of the primary key and is a foreign key to HZ_PARTIES, tying the record to the person's party record.
• ACTIVITY_SITE_CD — The code identifying the site of activity. Part of the primary key and a foreign key to IGS_AD_LOCATION_ALL. This is the column most commonly searched, and it is the lookup value users reference when querying a person's activity site.
• START_DATE — The date the activity site assignment begins. It is the third component of the primary key, allowing a person to have multiple activity sites over time.
• END_DATE — The date the activity site assignment ends, defining the effective period.
• LOCATION_ID — A location reference associated with the activity site record.
• PRIMARY_FLAG — Indicates whether this activity site is the primary site for the person during the period.
• REMARKS — Free-text comments on the activity site assignment.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard audit columns recording who created and last modified the row and when.

The primary key is IGS_PE_ACT_SITE_PK, defined on (PERSON_ID, ACTIVITY_SITE_CD, START_DATE). No separate surrogate key is documented; the composite business key serves as the unique identifier.

Common Use Cases and Queries

Typical uses include verifying an exchange visitor's approved activity site, reporting on all sites a person has used over time, and joining site codes to location descriptions for compliance and immigration reporting. A common query filters by person and activity site code:

• SELECT person_id, activity_site_cd, start_date, end_date, primary_flag FROM igs.igs_pe_act_site WHERE activity_site_cd = :site;
• Join to HZ_PARTIES on person_id = party_id to resolve the visitor's name and party details.
• Join to IGS_AD_LOCATION_ALL on activity_site_cd to obtain location descriptions for reporting.
• Filter on START_DATE and END_DATE to return only currently effective site assignments.

Because ACTIVITY_SITE_CD participates in the primary key, indexed lookups on that column, alone or with PERSON_ID, are efficient and appropriate for interactive queries and concurrent-program extracts.

Related Objects

The most significant related objects, based on documented foreign keys, are:

• HZ_PARTIES — joined on IGS_PE_ACT_SITE.PERSON_ID = HZ_PARTIES.PARTY_ID; provides the person or party identity.
• IGS_AD_LOCATION_ALL — joined on IGS_PE_ACT_SITE.ACTIVITY_SITE_CD = IGS_AD_LOCATION_ALL.ACTIVITY_SITE_CD; supplies the site definition and description.
• IGS_PE_ACT_SITE_PK — the primary key constraint enforcing uniqueness on (PERSON_ID, ACTIVITY_SITE_CD, START_DATE).

These relationships make the table a bridge between person records maintained in TCA and location reference data maintained in the IGS Student System, which is the basis for its link-style classification.