Results for “igs_pe_hz_pty_sites”

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

Overview

IGS_PE_HZ_PTY_SITES is an OSS (Oracle Student System) extension table layered on top of the Oracle Trading Community Architecture (TCA) base table HZ_PARTY_SITES. Its stated purpose within the IGS - Student System product is to store the effective dates (start and end) associated with the address of a person, organization, or institution. In practice, it functions as a date-stamping satellite of the TCA party-site relationship, allowing the Student System to track when a given party-site association became valid and when it expired, independent of the core TCA record.

The ETRM metadata classifies this entity as satellite-leaning under a heuristic Data Vault model. This is a modeling suggestion rather than a physical constraint: the table carries a primary key that also serves as a foreign key to HZ_PARTY_SITES, and its remaining columns are primarily descriptive and audit attributes, which is characteristic of a satellite feeding off a hub/link structure in TCA.

Note that the ETRM documentation marks this object as obsolete in the IGS product line, and reports that it is not implemented in the referenced database. It should therefore be treated as a legacy artifact for reference, migration analysis, or historical data lineage exercises rather than as an active production object in current EBS 12.1.1 or 12.2.2 environments.

Key Information Stored

The documented physical schema defines eight columns for the table, owned by the IGS schema. The most important of these are described below.

• PARTY_SITE_ID — The surrogate primary key of the table, enforced by the unique index IGS_PE_HZ_PARTY_SITE_PK. It simultaneously serves as a foreign key to HZ_PARTY_SITES.PARTY_SITE_ID, meaning each row in IGS_PE_HZ_PTY_SITES corresponds one-to-one with a party-site record in TCA and inherits that identity.
• START_DATE — The effective start date of the address association for the party. Represents the point in time from which the party-site link is considered valid in the Student System context.
• END_DATE — The effective end date of the address association. Together with START_DATE, this forms the effective-dating window that is the core business value of the table.
• CREATED_BY, CREATION_DATE — Standard EBS audit columns recording the user and timestamp of row creation.
• LAST_UPDATED_BY, LAST_UPDATE_DATE — Standard EBS audit columns recording the user and timestamp of the most recent modification.
• LAST_UPDATE_LOGIN — The login identifier associated with the most recent update, used for audit traceability.

The only documented unique index, IGS_PE_HZ_PARTY_SITE_PK on PARTY_SITE_ID, confirms that the surrogate key is the sole business-key candidate in the documented metadata. There is no separate composite business key documented beyond this identity column.

Common Use Cases and Queries

Because the table extends HZ_PARTY_SITES with effective dating, the typical reporting and integration scenarios involve joining back to the TCA party-site and party tables to surface address validity windows.

• Effective-dated address reporting — Retrieving the address of a student, organization, or institution as of a specific date by filtering on START_DATE <= :as_of_date AND (END_DATE IS NULL OR END_DATE >= :as_of_date).
• Historical address lineage — Identifying all address associations for a party over time by joining to HZ_PARTY_SITES and HZ_PARTIES.
• Data migration and cleanup — Given the obsolete status, queries are frequently used to export or archive historical IGS address data before decommissioning.

A representative join pattern follows the documented foreign key relationship:

SELECT p.party_id, ps.party_site_id, s.start_date, s.end_date
FROM igs_pe_hz_pty_sites s, hz_party_sites ps, hz_parties p
WHERE s.party_site_id = ps.party_site_id
AND ps.party_id = p.party_id;

Related Objects

The following objects are the most significant for integration and reporting, based on the documented relationships and standard TCA dependencies.

• HZ_PARTY_SITES — The parent TCA table referenced by the documented foreign key IGS_PE_HZ_PTY_SITES.PARTY_SITE_ID → HZ_PARTY_SITES.PARTY_SITE_ID. This is the primary join target.
• HZ_PARTIES — The party (person, organization, institution) master table reached through HZ_PARTY_SITES.PARTY_ID.
• HZ_LOCATIONS — The address-location table referenced from HZ_PARTY_SITES, providing the actual address details being effectively dated.
• HZ_PARTY_SITE_USES — Party-site purpose/usage records, commonly joined alongside this table to identify address roles.
• HZ_CUST_ACCT_SITES_ALL / HZ_CUST_SITE_USES_ALL — TCA customer site tables that share the PARTY_SITE_ID lineage, relevant where party sites are also customer sites.
• HZ_ORG_CONTACTS — Organization contact relationships that use party-site addresses and may consume the effective-date information.

Together these objects form the standard TCA party-site address model into which IGS_PE_HZ_PTY_SITES injects student-specific effective dating.