Results for “relationship_start_date”

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

Overview

IGSBV_FAMILY_RELATIONSHIPS is a read-only view owned by the APPS schema in the Oracle E-Business Suite Student System (IGS) product family. It exposes information about the family relationships of a person, presenting a denormalized, reporting-friendly representation of relationship data that is otherwise distributed across the Oracle Trading Community Architecture (TCA) tables HZ_RELATIONSHIPS, HZ_PARTIES, and the IGS-specific extension table IGS_PE_HZ_REL. The "BV" designation indicates a business view, a naming convention Oracle uses to identify views intended primarily for reporting, inquiry, and integration consumption rather than for transactional data entry.

Because the view is defined WITH READ ONLY, it is guaranteed to be non-updatable, making it safe for ad hoc querying, OBIEE/BIPublisher reporting, and external integration extracts. In EBS 12.1.1 and 12.2.2 the underlying TCA and IGS objects are functionally equivalent; the view remains VALID in both releases and is commonly used to surface parent, guardian, spouse, sibling, and next-of-kin information held against a student or related party record.

Underlying Base Objects

The view text joins three documented base objects inside the APPS schema:

• IGS_PE_HZ_REL (alias PHR) — the IGS extension table that stores institution-specific relationship attributes such as PRIMARY, SECONDARY, JOINT_SALUTATION, NEXT_TO_KIN, and the reported-by flags (REP_FACULTY, REP_STAFF, REP_STUDENT, REP_ALUMNI), keyed by RELATIONSHIP_ID and DIRECTIONAL_FLAG.
• HZ_RELATIONSHIPS (alias HZR) — the TCA base relationship table holding SUBJECT_ID, OBJECT_ID, RELATIONSHIP_CODE, START_DATE, END_DATE, and DIRECTIONAL_FLAG. This is the authoritative source for who relates to whom and in which direction.
• HZ_PARTIES (aliases HZP1, HZP2) — TCA party master, joined twice: HZP1 resolves the subject party (SUBJECT_ID) and HZP2 resolves the object party (OBJECT_ID), supplying the PARTY_NUMBER values exposed as subject and object identifiers.

The join condition links PHR to HZR on both RELATIONSHIP_ID and DIRECTIONAL_FLAG, ensuring the IGS extension row matches the correct directional TCA relationship, and then resolves each endpoint to its party number.

Key Columns

• SUBJECT_ID_NUMBER — party number of the person from whose perspective the relationship is defined.
• OBJECT_ID_NUMBER — party number of the related person (the family member).
• _LA:RELATIONSHIP_CODE — a special lookup-display column. The literal string embeds a lookup directive against the AR_LOOKUPS lookup type PARTY_RELATIONS_TYPE, resolving RELATIONSHIP_CODE into its MEANING (for example, "Parent", "Sibling", "Spouse").
• RELATIONSHIP_START_DATE / RELATIONSHIP_END_DATE — effective dating of the relationship.
• PRIMARY, SECONDARY, JOINT_SALUTATION — flags and salutation text describing the prominence and joint-addressing conventions for the relationship.
• NEXT_TO_KIN, REPORTED_FACULTY, REPORTED_STAFF, REPORTED_STUDENT, REPORTED_ALUMNI — institutional indicators of emergency contact status and who reported the relationship.
• RELATIONSHIP_ID, DIRECTIONAL_FLAG — keys linking back to the base TCA relationship record.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY.

Common Use Cases and Queries

Typical uses include producing next-of-kin or emergency contact rosters, validating guardian relationships for enrollment or financial aid, and extracting family structures for CRM or data-warehouse loads. A simple query lists the family relationships for a given party number:

SELECT subject_id_number,
       object_id_number,
       "_LA:RELATIONSHIP_CODE"  AS relationship_meaning,
       relationship_start_date,
       relationship_end_date,
       next_to_kin,
       primary
FROM   apps.igsbv_family_relationships
WHERE  subject_id_number = :p_party_number
AND    (relationship_end_date IS NULL
        OR relationship_end_date >= SYSDATE)
ORDER BY primary DESC, relationship_start_date;

To identify all next-of-kin contacts, filter on the NEXT_TO_KIN flag and join the returned party numbers to HZ_PARTIES or HZ_CUST_ACCOUNTS for names and contact points. Because the view is read-only and defined over TCA, queries should always qualify the object with the APPS schema and respect the caller's MO/security profile, since TCA party access may be restricted by operating unit or party security in some configurations.