Search Results igs_pe_addr_v




Overview
Explore in AI Chat

Overview

IGS_PE_ADDR_V is a view in the APPS schema belonging to the IGS – Student System product family within Oracle E-Business Suite, available in releases such as 12.1.1 and 12.2.2. It presents person and party-site address information by joining Oracle Trading Community Architecture (TCA) party-site records with the associated location records. The view consolidates identity, address, and demographic attributes into a single relational shape, which makes it suitable for reporting, integration extracts, and downstream processing where a flattened address model is preferred over the normalized TCA or student-system tables.

The view exposes a rich set of columns, including both current TCA-style address fields and legacy student-system fields, and it is commonly consumed by concurrent programs, Oracle Reports, and custom SQL that need one row per party site per address usage. Despite its name, the view is not exclusively limited to student addresses; it surfaces the general party-site address repository, with student-specific date ranges also included.

Underlying Base Objects

The ETRM metadata documents the view as being owned by APPS with no explicitly listed base objects in the excerpt; however, the view text defines the join. The primary driver is HZ_PARTY_SITES, aliased PS, which supplies PARTY_ID, PARTY_SITE_ID, LOCATION_ID, MAILSTOP, IDENTIFYING_ADDRESS_FLAG, STATUS, PARTY_SITE_NAME, ORIG_SYSTEM_REFERENCE, and the twenty ATTRIBUTE columns. It is joined to a location table, aliased L, which provides ADDRESS1 through ADDRESS4, CITY, STATE, PROVINCE, COUNTY, POSTAL_PLUS4_CODE, ADDRESS_EFFECTIVE_DATE, ADDRESS_EXPIRATION_DATE, ADDRESS_KEY, and other geographic attributes. A format-template reference, aliased FT, supplies ADDRESS_STYLE. Finally, the student-system address relationship table, aliased IGSPS, contributes START_DATE and END_DATE, aliased as START_DT and END_DT.

Key Columns

  • PERSON_ID – Maps from PS.PARTY_ID and identifies the party (typically a person) whose address is represented.
  • PARTY_SITE_ID / LOCATION_ID – Unique identifiers for the party site and the linked location.
  • ADDR_LINE_1 … ADDR_LINE_4, CITY, STATE, PROVINCE, COUNTY – Standard formatted address components sourced from the location record.
  • POSTAL_PLUS4_CODE – The postal code extension (for example, the "+4" segment of a US ZIP code) sourced from L.POSTAL_PLUS4_CODE, often queried alongside CITY and STATE.
  • ADDRESS_EFFECTIVE_DATE / ADDRESS_EXPIRATION_DATE – Validity window for the address.
  • START_DT / END_DT – Student-system address relationship start and end dates from IGSPS.
  • PARTY_SITE_NAME, MAILSTOP, IDENTIFYING_ADDRESS_FLAG, STATUS – Party-site descriptors and status indicators.
  • ATTRIBUTE1 … ATTRIBUTE20 – Descriptive flexfield context and segment values inherited from the party site.
  • WH_UPDATE_DATE, DATE_LAST_VERIFIED – Present in the projection but returned as NULL via TO_DATE(NULL).

Common Use Cases and Queries

Typical usage includes person-address extracts for enrollment or correspondence processing, reconciliation of party-site addresses against legacy student records, and postal validation routines that must read or verify postal code extensions. The example below retrieves current address details for a party, filtering on the postal code extension column referenced by the search.

  • Retrieve a person's formatted address including postal extension:

SELECT person_id, party_site_id, addr_line_1, addr_line_2, city, state, postal_plus4_code, status FROM apps.igs_pe_addr_v WHERE person_id = :p_person_id;

The view can also be joined to other student-system or TCA views using PERSON_ID or PARTY_SITE_ID to enrich results with contact points, phone numbers, or enrollment data, making it a reliable foundation for address-centric reporting in the IGS module.